SQL Syntax problem, please help

[Delamater]

Hello all,

I was wondering if anyone knew why I was receiving the following error:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'spBobsTest%'.




Here is my SQL code:




/*
*****************************************************************************************
Author: Bob ********* *
Date Created: 05-17-04 *
Description: This procedure is designed to loop through all the databases on a server*
to look for any object. Specifically, at this point, you can modify the *
"Select Name" block to search for the object you desire *
*****************************************************************************************
*/

SET NoCount On

Declare @dbObject varChar(30)

Declare scroll_cursor Cursor For

Select Name
From master..sysDatabases
Where name like '%_app' OR
Name like '%_sys'
Order By name

Open scroll_cursor

FETCH NEXT FROM scroll_cursor INTO @dbObject

While (@@FETCH_STATUS = 0)
Begin

Exec ('IF EXISTS (select * from ' + @dbObject + '..sysObjects where name = ''spBobsTest%''')
Begin
SELECT 'Database Name: ' + @dbObject
End

FETCH NEXT FROM scroll_cursor INTO @dbobject

End

Close scroll_cursor
DEALLOCATE scroll_cursor









Thanks for any help you can offer!
Bob

[hspc]

you forgot the ')'

'IF EXISTS (select * from ' + @dbObject + '..sysObjects where name = ''spBobsTest%'')'

[Delamater]

Good catch, but I am still missing something here. I changed the procedure to read:

While (@@FETCH_STATUS = 0)
Begin

Exec ('IF EXISTS (select * from ' + @dbObject + '..sysObjects where name = ''spBobsTest%'')')
Begin
SELECT 'Database Name: ' + @dbObject
End

FETCH NEXT FROM scroll_cursor INTO @dbobject

End


Now I receive the following:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.



Any ideas?

Thanks again!
Bob

[hspc]

i used this code in query analyzer and clicked run and i got no errors :SET NoCount On

Declare @dbObject varChar(30)

Declare scroll_cursor Cursor For

Select Name
From master..sysDatabases
Where name like '%_app' OR
Name like '%_sys'
Order By name

Open scroll_cursor

FETCH NEXT FROM scroll_cursor INTO @dbObject

While (@@FETCH_STATUS = 0)
Begin

Exec ('IF EXISTS (select * from ' + @dbObject + '..sysObjects where name = ''spBobsTest%'')')
Begin
SELECT 'Database Name: ' + @dbObject
End

FETCH NEXT FROM scroll_cursor INTO @dbobject

End


Close scroll_cursor
DEALLOCATE scroll_cursor

[Delamater]

Did you merely check the syntax or actually run the query? Of course you would have to change the stored procedure to some other than spBobstest (maybe sp%).

The reason I ask is that is syntatically checks ok for me, but when I run it, and it loops through the databases on the SQL Server, it is at that point when I receive the errors. Actually, I receive one error for each database.

Thanks again for the assistance!
Bob