?
Solved

error in SQL Statement Incorrect syntax near '%'.

Posted on 2011-04-24
15
Medium Priority
?
632 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:bibi92
  • 5
  • 5
  • 3
  • +1
15 Comments
 
LVL 71

Expert Comment

by:Qlemo
ID: 35456181
I understand what you want to achieve with the first part, but what is the stuff regarding CHAR(13) and ''GO''? Anyway, you are missing several doubled single quotes, the first should be after the N of
  sp_executesql N'
0
 

Author Comment

by:bibi92
ID: 35456188
what is the stuff regarding CHAR(13) and ''GO'' is for execute the ''SQL_STORED_PROCEDURE''

I have quote after sp_executesql N'

Thanks
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 35456189
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 
'

Open in new window

0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 71

Expert Comment

by:Qlemo
ID: 35456196
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.
0
 
LVL 4

Expert Comment

by:malikirfan28
ID: 35456202
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].[dbo].sp_executesql @sql
0
 

Author Comment

by:bibi92
ID: 35456205
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
0
 
LVL 4

Expert Comment

by:malikirfan28
ID: 35456207
dont forget to pass @Dossier as parameter. You can pass parameter as
exec sp_executesql @sql, N'@Dossier varchar'
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 35456239
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.
0
 

Author Comment

by:bibi92
ID: 35456240
Thanks
but this is not execute like a parameter AND o.name LIKE %sp_MSdel_@Dossier% OR  o.name LIKE %sp_MSdel_@Dossier%
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 35456245
Because it needs to be
AND o.name LIKE ''%sp_MSdel_' + @Dossier + '%'' OR  o.name LIKE ''%sp_MSdel_' + @Dossier + '%''
0
 

Author Comment

by:bibi92
ID: 35456267
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
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35456353
What you do not seem to understand is that GO is not a T-SQL command.  You cannot use it that way.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 35456374
It should be as simple as the code below (although it would be far more efficient and maintainable using sp_executesql).
set @sql3 = 'exec ['+@SrvAbonne + '].['+ @Dbcible + '].[dbo].sp_executesql 
 N''INSERT INTO maj_replsp  
SELECT m.definition, 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)

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35456377
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.
0
 

Author Closing Comment

by:bibi92
ID: 35456568
Thanks bibi
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question