Cursor syntax error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Cursor syntax error

Author

Topic

fredong
Yak Posting Veteran

80 Posts

Posted - 2006-03-08 : 16:13:05

I have a cursor below that I have created when I executed this cursor I got return error "Server: Msg 170, Level 15, State 1, Line 16Line 16: Incorrect syntax near '.'." Can somebody help. Thanks

declare @dbname varchar(200)
declare @mSql1 varchar(8000)

DECLARE DBName_Cursor CURSOR FOR
select name
from master.dbo.sysdatabases
where name not in ('mssecurity','tempdb')
Order by name

OPEN DBName_Cursor

FETCH NEXT FROM DBName_Cursor INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN
Set @mSQL1 = 'Insert into TESTAudit(ID,[TABLE OWNER],[TABLE VIEW NAME],GRANTOR,GRANTEE,PRIVILEDGE,[IS GRANTABLE])

select O.ID,USER_NAME(O.UID),OBJECT_NAME(O.ID),USER_NAME(P.GRANTOR),USER_NAME(P.UID)
,CASE P.ACTION
WHEN 26 THEN ''REFERENCES''
WHEN 178 THEN ''CREATE FUNCTION''
WHEN 193 THEN ''INSERT''
WHEN 196 THEN ''DELETE''
WHEN 197 THEN ''CREATE TABLE''
WHEN 203 THEN ''CREATE DATABASE''
WHEN 207 THEN ''CREATE VIEW''
WHEN 222 THEN ''CREATE PROCEDURE''
WHEN 224 THEN ''EXECUTE''
WHEN 228 THEN ''BACKUP DATABASE''
WHEN 233 THEN ''CREATE DEFAULT''
WHEN 235 THEN ''BACKUP LOG''
WHEN 236 THEN ''CREATE RULE''
END
,CASE P.PROTECTTYPE
WHEN 204 THEN ''YES''
WHEN 205 THEN ''NO''
WHEN 206 THEN ''NO''
END
from' +@dbname+'.dbo.sysprotects P,'+@dbname +'.dbo.sysobjects O
where P.id =*O.id'

Execute (@mSql1)

FETCH NEXT FROM DBName_Cursor INTO @dbname
END

CLOSE DBName_Cursor
DEALLOCATE DBName_Cursor
Go

k

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-03-08 : 17:24:21

You're missing a space in the FROM clause:

 END
from ' +@dbname+'.dbo.sysprotects P,'+@dbname +'.dbo.sysobjects O
where P.id =*O.id'

HTH,

Tim

fredong
Yak Posting Veteran

80 Posts

Posted - 2006-03-08 : 17:33:35

Thanks.

k

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources