-
May 19th, 2004, 10:57 AM
#1
SQL Syntax problem, please help
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
-
May 20th, 2004, 02:48 AM
#2
you forgot the ')'
'IF EXISTS (select * from ' + @dbObject + '..sysObjects where name = ''spBobsTest%'')'
-
May 20th, 2004, 11:14 AM
#3
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
-
May 20th, 2004, 12:45 PM
#4
i used this code in query analyzer and clicked run and i got no errors :
Code:
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
-
May 20th, 2004, 01:21 PM
#5
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|