SQL 2008 to SQL2005
-
When i get the script from sql 2008 to sql 2005 without any error it finish. when i wanna to execute the script in sql2005 i get this error :
Msg 139, Level 15, State 1, Procedure Language_Update, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Procedure Language_Update, Line 9
Must declare the scalar variable "@param".this is the part of script :
/****** Object: StoredProcedure [dbo].[Language_Update] Script Date: 04/23/2012 11:39:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Language_Update](@LanguageId SmallInt , @Language NVarChar(50))
AS
BEGIN
declare @param nvarchar(1000)= N'update [Common].[Language] set '
if @Language is not NULL
BEGIN
set @param = @param + '[Language]=''' + @Language + ''','
End
set @param= substring(@param,0,len(@param))
set @param = @param + ' Where LanguageId=''' + cast( @LanguageId as nvarchar(5)) + ''' '
exec sp_executesql @param
END
GOtake attention that the script is about 77000 line and i have about 200 of this type error. Please Help ! -
When i get the script from sql 2008 to sql 2005 without any error it finish. when i wanna to execute the script in sql2005 i get this error :
Msg 139, Level 15, State 1, Procedure Language_Update, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Procedure Language_Update, Line 9
Must declare the scalar variable "@param".this is the part of script :
/****** Object: StoredProcedure [dbo].[Language_Update] Script Date: 04/23/2012 11:39:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Language_Update](@LanguageId SmallInt , @Language NVarChar(50))
AS
BEGIN
declare @param nvarchar(1000)= N'update [Common].[Language] set '
if @Language is not NULL
BEGIN
set @param = @param + '[Language]=''' + @Language + ''','
End
set @param= substring(@param,0,len(@param))
set @param = @param + ' Where LanguageId=''' + cast( @LanguageId as nvarchar(5)) + ''' '
exec sp_executesql @param
END
GOtake attention that the script is about 77000 line and i have about 200 of this type error. Please Help !When you declare variable @param, set default value after declared. Do this replace: Old part of query
declare @param nvarchar(1000)= N'update [Common].[Language] set '
New part of query
declare @param nvarchar(1000)
set @param ='update [Common].[Language] set '
I Love T-SQL "VB.NET is developed with C#.NET" If my post helps you kindly save my time by voting my post.
-
When i get the script from sql 2008 to sql 2005 without any error it finish. when i wanna to execute the script in sql2005 i get this error :
Msg 139, Level 15, State 1, Procedure Language_Update, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Procedure Language_Update, Line 9
Must declare the scalar variable "@param".this is the part of script :
/****** Object: StoredProcedure [dbo].[Language_Update] Script Date: 04/23/2012 11:39:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Language_Update](@LanguageId SmallInt , @Language NVarChar(50))
AS
BEGIN
declare @param nvarchar(1000)= N'update [Common].[Language] set '
if @Language is not NULL
BEGIN
set @param = @param + '[Language]=''' + @Language + ''','
End
set @param= substring(@param,0,len(@param))
set @param = @param + ' Where LanguageId=''' + cast( @LanguageId as nvarchar(5)) + ''' '
exec sp_executesql @param
END
GOtake attention that the script is about 77000 line and i have about 200 of this type error. Please Help !Thanks for Help!
But its about 200 to 500 of this items!How can i do that ? and why doesn't the SQL create correct scripts. -
Thanks for Help!
But its about 200 to 500 of this items!How can i do that ? and why doesn't the SQL create correct scripts.How/where is SQL Server generating the script from? Try doing a search and replace ') = N' with '): Set @Update =N'
Never underestimate the power of human stupidity RAH
-
When i get the script from sql 2008 to sql 2005 without any error it finish. when i wanna to execute the script in sql2005 i get this error :
Msg 139, Level 15, State 1, Procedure Language_Update, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Procedure Language_Update, Line 9
Must declare the scalar variable "@param".this is the part of script :
/****** Object: StoredProcedure [dbo].[Language_Update] Script Date: 04/23/2012 11:39:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Language_Update](@LanguageId SmallInt , @Language NVarChar(50))
AS
BEGIN
declare @param nvarchar(1000)= N'update [Common].[Language] set '
if @Language is not NULL
BEGIN
set @param = @param + '[Language]=''' + @Language + ''','
End
set @param= substring(@param,0,len(@param))
set @param = @param + ' Where LanguageId=''' + cast( @LanguageId as nvarchar(5)) + ''' '
exec sp_executesql @param
END
GOtake attention that the script is about 77000 line and i have about 200 of this type error. Please Help !Thanks! but this is not good trick! and no use in here cause i have different parameters; this was example @Param. -
Thanks for Help!
But its about 200 to 500 of this items!How can i do that ? and why doesn't the SQL create correct scripts.jojoba2011 wrote:
and why doesn't the SQL create correct scripts
It did. You generated them on Sql2008, and thus it will use all the language-features available for 2008. If you wanted to be compatible with Sql2005, you'd generate them from there. Put your database in compatibility mode (Google knows how) and try to script again.
Bastard Programmer from Hell :suss: