bibi92
asked on
error in SQL Statement Incorrect syntax near '%'.
Hello,
I try to execute :
DECLARE @SrvAbonne varchar (50)
DECLARE @DbCible varchar (50)
DECLARE @Dossier varchar (50)
exec ['+@SrvAbonne + '].['+ @Dbcible + '].[dbo].sp_executesql N'
INSERT INTO sage_maj_replsp
SELECT m.definition + CHAR(13) + ''GO'' + CHAR(13), o.name
FROM sys.objects o INNER JOIN sys.sql_modules m
ON o.object_id = m.object_id INNER JOIN sys.schemas s
ON s.schema_id = o.schema_id
WHERE o.type_desc IN ( ''SQL_STORED_PROCEDURE'' )
AND o.name LIKE '%sp_MSdel_' + @Dossier + '%'
OR o.name LIKE '%sp_MSdel_' + @Dossier + '%'
ORDER BY o.name DESC
'
Incorrect syntax near '%'.
Thanks
bibi
I try to execute :
DECLARE @SrvAbonne varchar (50)
DECLARE @DbCible varchar (50)
DECLARE @Dossier varchar (50)
exec ['+@SrvAbonne + '].['+ @Dbcible + '].[dbo].sp_executesql N'
INSERT INTO sage_maj_replsp
SELECT m.definition + CHAR(13) + ''GO'' + CHAR(13), o.name
FROM sys.objects o INNER JOIN sys.sql_modules m
ON o.object_id = m.object_id INNER JOIN sys.schemas s
ON s.schema_id = o.schema_id
WHERE o.type_desc IN ( ''SQL_STORED_PROCEDURE'' )
AND o.name LIKE '%sp_MSdel_' + @Dossier + '%'
OR o.name LIKE '%sp_MSdel_' + @Dossier + '%'
ORDER BY o.name DESC
'
Incorrect syntax near '%'.
Thanks
bibi
ASKER
what is the stuff regarding CHAR(13) and ''GO'' is for execute the ''SQL_STORED_PROCEDURE''
I have quote after sp_executesql N'
Thanks
I have quote after sp_executesql N'
Thanks
Sorry, have been wrong about the N' part. I assume the following should work:
DECLARE @SrvAbonne varchar (50)
DECLARE @DbCible varchar (50)
DECLARE @Dossier varchar (50)
exec @SrvAbonne.@Dbcible.dbo.sp_executesql N'
INSERT INTO sage_maj_replsp
SELECT m.definition + CHAR(13) + ''GO'' + CHAR(13), o.name
FROM sys.objects o INNER JOIN sys.sql_modules m
ON o.object_id = m.object_id INNER JOIN sys.schemas s
ON s.schema_id = o.schema_id
WHERE o.type_desc IN ( ''SQL_STORED_PROCEDURE'' )
AND o.name LIKE ''%sp_MSdel_' + @Dossier + '%''
OR o.name LIKE ''%sp_MSdel_' + @Dossier + '%''
ORDER BY o.name DESC
'
Ah, you retrieve the definition of the stored procedure, and want to have the "GO" appended. That is ok. But that code does not execute that procedure, of course.
Here your syntax error removed. But still I could not understand your SELECT clause.
DECLARE @SrvAbonne varchar (50)
DECLARE @DbCible varchar (50)
DECLARE @Dossier varchar (50)
declare @sql nvarchar(MAX)
set @sql = N'INSERT INTO sage_maj_replsp
SELECT m.definition + CHAR(13) + ''GO'' + CHAR(13), o.name
FROM sys.objects o INNER JOIN sys.sql_modules m
ON o.object_id = m.object_id INNER JOIN sys.schemas s
ON s.schema_id = o.schema_id
WHERE o.type_desc IN ( ''SQL_STORED_PROCEDURE'' )
AND o.name LIKE %sp_MSdel_@Dossier% OR o.name LIKE %sp_MSdel_@Dossier% ORDER BY o.name DESC'
exec [@SrvAbonne].[@Dbcible].[d bo].sp_exe cutesql @sql
DECLARE @SrvAbonne varchar (50)
DECLARE @DbCible varchar (50)
DECLARE @Dossier varchar (50)
declare @sql nvarchar(MAX)
set @sql = N'INSERT INTO sage_maj_replsp
SELECT m.definition + CHAR(13) + ''GO'' + CHAR(13), o.name
FROM sys.objects o INNER JOIN sys.sql_modules m
ON o.object_id = m.object_id INNER JOIN sys.schemas s
ON s.schema_id = o.schema_id
WHERE o.type_desc IN ( ''SQL_STORED_PROCEDURE'' )
AND o.name LIKE %sp_MSdel_@Dossier% OR o.name LIKE %sp_MSdel_@Dossier% ORDER BY o.name DESC'
exec [@SrvAbonne].[@Dbcible].[d
ASKER
exec @SrvAbonne.@Dbcible doesn't work because this is an named instances SRV\TEST
Msg 102, Level 15, State 1, Procedure sage_maj_proc_X3, Line 93
Incorrect syntax near '+'.
But that code does not execute that procedure, of course exactly I have to do this after with execute statement
Thanks
Msg 102, Level 15, State 1, Procedure sage_maj_proc_X3, Line 93
Incorrect syntax near '+'.
But that code does not execute that procedure, of course exactly I have to do this after with execute statement
Thanks
dont forget to pass @Dossier as parameter. You can pass parameter as
exec sp_executesql @sql, N'@Dossier varchar'
exec sp_executesql @sql, N'@Dossier varchar'
malikirfan,
That still needs some correction - quotes for the the like arguments, and constructing a string from the expression.
bibi92,
That doesn't make sense to me. Maybe you should phrase what you really want to achieve, so we can present a real solution.
That still needs some correction - quotes for the the like arguments, and constructing a string from the expression.
bibi92,
That doesn't make sense to me. Maybe you should phrase what you really want to achieve, so we can present a real solution.
ASKER
Thanks
but this is not execute like a parameter AND o.name LIKE %sp_MSdel_@Dossier% OR o.name LIKE %sp_MSdel_@Dossier%
but this is not execute like a parameter AND o.name LIKE %sp_MSdel_@Dossier% OR o.name LIKE %sp_MSdel_@Dossier%
Because it needs to be
AND o.name LIKE ''%sp_MSdel_' + @Dossier + '%'' OR o.name LIKE ''%sp_MSdel_' + @Dossier + '%''
AND o.name LIKE ''%sp_MSdel_' + @Dossier + '%'' OR o.name LIKE ''%sp_MSdel_' + @Dossier + '%''
ASKER
Thanks Qlemo, but I have this error :
declare @sql3 varchar(max)
set @sql3 = 'exec ['+@SrvAbonne + '].['+ @Dbcible + '].[dbo].sp_executesql
N''INSERT INTO maj_replsp
SELECT m.definition + CHAR(13) + ''GO'' + CHAR(13), o.name
FROM sys.objects o INNER JOIN sys.sql_modules m
ON o.object_id = m.object_id INNER JOIN sys.schemas s
ON s.schema_id = o.schema_id
WHERE o.type_desc IN ( ''SQL_STORED_PROCEDURE'' )
AND o.name LIKE ''%sp_MSupd_'+@Dossier+'%' ' OR o.name LIKE ''%sp_MSdel_' + @Dossier+'%'' ORDER BY o.name DESC''
'
print(@sql3)
execute (@sql3)
Msg 50000, Level 15, State 1, Procedure maj_proc_Dossier, Line 126
Incorrect syntax near 'GO'. #102
declare @sql3 varchar(max)
set @sql3 = 'exec ['+@SrvAbonne + '].['+ @Dbcible + '].[dbo].sp_executesql
N''INSERT INTO maj_replsp
SELECT m.definition + CHAR(13) + ''GO'' + CHAR(13), o.name
FROM sys.objects o INNER JOIN sys.sql_modules m
ON o.object_id = m.object_id INNER JOIN sys.schemas s
ON s.schema_id = o.schema_id
WHERE o.type_desc IN ( ''SQL_STORED_PROCEDURE'' )
AND o.name LIKE ''%sp_MSupd_'+@Dossier+'%'
'
print(@sql3)
execute (@sql3)
Msg 50000, Level 15, State 1, Procedure maj_proc_Dossier, Line 126
Incorrect syntax near 'GO'. #102
What you do not seem to understand is that GO is not a T-SQL command. You cannot use it that way.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
And for those who think otherwise regarding the GO command, here is a direct quote from SQL Server BOL:
GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.
SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO.
A Transact-SQL statement cannot occupy the same line as a GO command. However, the line can contain comments.
GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.
SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO.
A Transact-SQL statement cannot occupy the same line as a GO command. However, the line can contain comments.
ASKER
Thanks bibi
sp_executesql N'