August 4, 2011 at 9:56 am
Hi,
I am migrating from SQL 2000 to SQL 2008. I have come across a stored procedure that compiles on 2000 but does not on 2008. Here is the code
create procEDURE spExportStock(@CompanyCode AS CHAR(4),
@Factory AS CHAR(2),
@StartTime AS CHAR(5),
@EndTime AS CHAR(5),
@MovementType AS SMALLINT) AS
-- Local Variables
DECLARE @SystemDate varchar(10),
@FromDate varchar(15),
@ToDate varchar(15)
-- Set System Date Variable
SET @SystemDate = CONVERT(varchar,GetDate(),112)
SET @FromDate = @SystemDate + ' ' + @StartTime
SET @ToDate = @SystemDate + ' ' + @EndTime
-- Create Temporary Table
CREATE TABLE #Data
(
Product_ID varchar(20),
Product_Desc varchar(30),
Qty real
)
-- Populate temporary table with data
INSERT INTO #Data
SELECT fg.productnumber,pm.description, SUM(quantity) FROM fgstockmovements fg INNER JOIN productmaster pm ON fg.productnumber = pm.productnumber
WHERE movementtype = @MovementType AND fg.dateupdated > @FromDate AND fg.dateupdated < @ToDate
GROUP BY fg.productnumber,pm.description
ORDER BY 1
-- Delete data from AP_PRODUCED_QTY where production date
-- matched with the system date
-- Drop temporary table
DROP TABLE #Data
In 2008 I get the error Column name or number of supplied values does not match table definition.
Has anyone come across this before - can anyone tell me what has changed between 2000 and 2008?
August 4, 2011 at 10:00 am
I can't see any reason why that won't run, it compiles fine on my SQL 2008 instance.
You haven't perhaps got a temp table called #data created already in the session you're using for that?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 4, 2011 at 10:03 am
bingo - I am an idiot:hehe:
have been doing this for hundreds of sps - had opened a 2000 session which worked but of course that's because it was a fresh session.
Thank you
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply