SQL 2008 code running on SQL 2000

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL 2008 code running on SQL 2000

Author

Topic

TomTom
Starting Member

4 Posts

Posted - 2012-08-09 : 10:59:03

Hello,

Are there any issues with running code on an install using SQL 2000 that was developed using SQL 2008?

I am running some code, developed by outside consultants, and it keeps breaking. The break points are from not dropping or modifying temporary tables correctly.

Thanks for your help!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-08-09 : 11:15:18

There are a number of new features introduced with SQL Server 2005 (and some more with 2008) but we can't tell where it would break without seeing the code itself.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-09 : 11:21:10

There might be issues around object resolution - if a temp table is created in one sp and modified then accessed later or frrom a different sp. There was a change in the way the code was compiled. It can cause issues if an sp calls another and they both have a temp table of the same name.

Another issue you might find is that the group by clause doesn't order the resultset any more.

Are you sure it's not table variables?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-09 : 11:28:45

quote:
Originally posted by TomTom

Hello,

Are there any issues with running code on an install using SQL 2000 that was developed using SQL 2008?

I am running some code, developed by outside consultants, and it keeps breaking. The break points are from not dropping or modifying temporary tables correctly.

Thanks for your help!

it may be any of new features like ROW_NUMBER,PIVOT,CTE etc
as suggested by Rob, analyse the code and see if you can spot any of new features present in code
or alternatively provide us with error message to at least guess whats going wrong

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

TomTom
Starting Member

4 Posts

Posted - 2012-08-09 : 11:50:53

[code][/code]
-- Diagnostics
IF OBJECT_ID('dbo.TMP02', 'U') IS NOT NULL DROP TABLE dbo.TMP02
SELECT '# with NDC Codes' AS Note,GRP,ELIG_GRP,DEMO_CAT
,CAST(COUNT(*) AS FLOAT) AS Statistic
,SUM(ELIG) AS ELIG
INTO dbo.TMP02
FROM (SELECT MBR_ID FROM dbo.INP_RX GROUP BY MBR_ID) A INNER JOIN dbo.INP_ELIG B ON (A.MBR_ID=B.MBR_ID)
GROUP BY GRP,ELIG_GRP,DEMO_CAT
UNION ALL
SELECT 'Unique MbrID + RX' AS Note,GRP,ELIG_GRP,DEMO_CAT
,CAST(COUNT(*) AS FLOAT) AS Statistic
,SUM(ELIG) AS ELIG
FROM (SELECT MBR_ID,NDC,COUNT(*) AS COUNT
FROM dbo.INP_RX WHERE NDC IS NOT NULL GROUP BY MBR_ID,NDC) A INNER JOIN dbo.INP_ELIG B ON (A.MBR_ID=B.MBR_ID)
GROUP BY GRP,ELIG_GRP,DEMO_CAT

-- Insert Additional Diagnostics
INSERT INTO dbo.TMP02
VALUES('Pct Unmapped Rx','All','All','All',@UnmappedRxPct,0)

INSERT INTO dbo.DIAGNOSTICS
SELECT * FROM dbo.TMP02
[code][/code]

TomTom
Starting Member

4 Posts

Posted - 2012-08-09 : 11:56:22

sorry. hit the button to early.

The code gets stuck at the 'Insert Additional diagnostic' insert statements because don't match the table definitions.

TMP02 was created earlier and used for something prior to this. It is supossed to be dropped and recreated below but the drop of table TMP02 does not occur.

I can highlight the code and run it by itself...but executed together it fails.

Hope this clarifies.

quote:
Originally posted by TomTom

-- Diagnostics
IF OBJECT_ID('dbo.TMP02', 'U') IS NOT NULL DROP TABLE dbo.TMP02
SELECT '# with NDC Codes' AS Note,GRP,ELIG_GRP,DEMO_CAT
,CAST(COUNT(*) AS FLOAT) AS Statistic
,SUM(ELIG) AS ELIG
INTO dbo.TMP02
FROM (SELECT MBR_ID FROM dbo.INP_RX GROUP BY MBR_ID) A INNER JOIN dbo.INP_ELIG B ON (A.MBR_ID=B.MBR_ID)
GROUP BY GRP,ELIG_GRP,DEMO_CAT
UNION ALL
SELECT 'Unique MbrID + RX' AS Note,GRP,ELIG_GRP,DEMO_CAT
,CAST(COUNT(*) AS FLOAT) AS Statistic
,SUM(ELIG) AS ELIG
FROM (SELECT MBR_ID,NDC,COUNT(*) AS COUNT
FROM dbo.INP_RX WHERE NDC IS NOT NULL GROUP BY MBR_ID,NDC) A INNER JOIN dbo.INP_ELIG B ON (A.MBR_ID=B.MBR_ID)
GROUP BY GRP,ELIG_GRP,DEMO_CAT

-- Insert Additional Diagnostics
INSERT INTO dbo.TMP02
VALUES('Pct Unmapped Rx','All','All','All',@UnmappedRxPct,0)

INSERT INTO dbo.DIAGNOSTICS
SELECT * FROM dbo.TMP02

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-09 : 12:45:43

are you sure Tmp02 was created on dbo schema itself? if not that may be reason why the DROP is not working

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

TomTom
Starting Member

4 Posts

Posted - 2012-08-09 : 13:15:53

The table is created as a dbo schema. Below is the code. The code previously posted is supposed to drop and recreate...but that does not happen. I can run the code by itself and it works fine.

There is also 5 more temp tables that are supposed to be created but they don't get created either...

I am not sure what is going on because it looks as if it should work but it does not. The version of SQL the code was created and tested on was 2008. And now I running this on a server with SQL 2000. i don't know if its a version issue or not.

-- Summarize by WRA Grouping
IF OBJECT_ID('dbo.TMP02', 'U') IS NOT NULL DROP TABLE dbo.TMP02
SELECT MBR_ID,DIAGNOSTIC,WRACat,COUNT(*) AS COUNT INTO dbo.TMP02
FROM TMP01 GROUP BY MBR_ID,DIAGNOSTIC,WRACat ORDER BY MBR_ID,WRACat

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-09 : 15:12:30

can you try putting a GO and see if this helps


IF OBJECT_ID('dbo.TMP02', 'U') IS NOT NULL DROP TABLE dbo.TMP02
GO
SELECT '# with NDC Codes' AS Note,GRP,ELIG_GRP,DEMO_CAT
,CAST(COUNT(*) AS FLOAT) AS Statistic
,SUM(ELIG) AS ELIG
INTO dbo.TMP02
FROM (SELECT MBR_ID FROM dbo.INP_RX GROUP BY MBR_ID) A INNER JOIN dbo.INP_ELIG B ON (A.MBR_ID=B.MBR_ID)
GROUP BY GRP,ELIG_GRP,DEMO_CAT
UNION ALL
SELECT 'Unique MbrID + RX' AS Note,GRP,ELIG_GRP,DEMO_CAT
,CAST(COUNT(*) AS FLOAT) AS Statistic
,SUM(ELIG) AS ELIG
FROM (SELECT MBR_ID,NDC,COUNT(*) AS COUNT 
FROM dbo.INP_RX WHERE NDC IS NOT NULL GROUP BY MBR_ID,NDC) A INNER JOIN dbo.INP_ELIG B ON (A.MBR_ID=B.MBR_ID)
GROUP BY GRP,ELIG_GRP,DEMO_CAT

-- Insert Additional Diagnostics
INSERT INTO dbo.TMP02
VALUES('Pct Unmapped Rx','All','All','All',@UnmappedRxPct,0)

INSERT INTO dbo.DIAGNOSTICS
SELECT * FROM dbo.TMP02

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources