error after SQL 2003 to 2005 upgrade

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

error after SQL 2003 to 2005 upgrade

Author

Topic

123456
Starting Member

1 Post

Posted - 2009-08-10 : 17:42:15

This stored procedure worked perfectly in SQL Server 2003. We upgraded to Server 2005 and now I get the following errors:

Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@Command".
Msg 137, Level 15, State 2, Line 5
Must declare the scalar variable "@Command".
Msg 137, Level 15, State 2, Line 7
Must declare the scalar variable "@Command".
Msg 137, Level 15, State 2, Line 9
Must declare the scalar variable "@Command".
Msg 137, Level 15, State 2, Line 11
Must declare the scalar variable "@Command".

The stored procedure:

CREATE PROCEDURE [dbo].[GetInSQLData]

(@Tagname varchar(2000), @RetrievalMode varchar(50),

@Resolution varchar(10), @wwVersion varchar(20), @StartDate varchar(50) ,

@EndDate varchar(50)

)

AS

BEGIN

SET ANSI_NULLS ON

SET ANSI_WARNINGS ON

SET @StartDate = CAST(CONVERT(datetime, @StartDate, 21) as varchar(50))

SET @EndDate = CAST(CONVERT(datetime, @EndDate, 21) as varchar(50))

DECLARE @Command nvarchar(4000)

SET @Command = 'SET QUOTED_IDENTIFIER OFF SELECT * FROM OPENQUERY(INSQL, "SELECT '

SET @Command = @Command + 'DateTime, [' + @Tagname + ']'

SET @Command = @Command + ' FROM WideHistory WHERE wwRetrievalMode = '''

SET @Command = @Command + @RetrievalMode + ''' AND wwResolution = '

SET @Command = @Command + @Resolution + ' AND wwVersion = '''

SET @Command = @Command + @wwVersion

SET @Command = @Command + ''' AND DateTime >= '''+ @StartDate + ''''

SET @Command = @Command + ' AND DateTime <= '''+ @EndDate + '''")'

exec sp_executesql @Command

END
GO

Any help would be greatly appreciated.
Thanks,
Lisa

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-10 : 19:31:09

change this: exec sp_executesql @Command

to this: Print @Command

Then can debug your query.

That said, I don't get the errors you're getting. seems like perhaps a syntax error. Sure you declared @Command properly?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources