db_cursor 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

SQL Server Development (2000)

db_cursor error

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-04-28 : 08:22:14

Wendy writes "I am running following script:

"/* if temp table exists, drop it */
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE [name] LIKE '#zip_dbs%')
BEGIN
DROP TABLE #zip_dbs
END

/* Create temp table used to hold version and supervisor info grabbed by the cursor for all institution databases */
CREATE TABLE #zip_dbs (bak_files_name varchar(100))

INSERT INTO #zip_dbs
exec master.dbo.xp_cmdshell 'dir "D:\SAM2003DB1\BACKUP\Restores\"'

/* select * from #zip_dbs */

delete #zip_dbs
where rtrim(bak_files_name) not like '%.bak' or bak_files_name not like '%INST%.bak' or bak_files_name is NUll

declare @filename varchar(100),
@zip_file_path varchar(100),
@bak_file_path varchar(100)

declare db_cursor scroll cursor for
select replace(right(rtrim(bak_files_name),33), '.bak','') as filename
from #zip_dbs

open db_cursor
fetch next from db_cursor into @filename
while @@fetch_status = 0
begin

set @zip_file_path ='D:\SAM2003DB1\BACKUP\restores\'+ @filename + '.zip'
set @bak_file_path ='D:\SAM2003DB1\BACKUP\restores\'+ @filename + '.bak'

Exec master.dbo.xp_cmdshell ''D:\SAM2003DB1\backup\restores\wzzip 'D:\SAM2003DB1\BACKUP\restores\'+ @filename + '.zip' 'D:\SAM2003DB1\BACKUP\restores\'+ @filename + '.bak'''

fetch next from db_cursor into @filename
end
close db_cursor
deallocate db_cursor "

I got the error "Server: Msg 170, Level 15, State 1, Line 37
Line 37: Incorrect syntax near '\'."

I can't see what's going wrong, can any one help?

Thanks

Wendy

"

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-28 : 09:08:46

Wendy,

not necessary but loose the double quotes here:
>>exec master.dbo.xp_cmdshell 'dir "D:\SAM2003DB1\BACKUP\Restores\"'
exec master.dbo.xp_cmdshell 'dir D:\SAM2003DB1\BACKUP\Restores\'

and change this:
Exec master.dbo.xp_cmdshell ''D:\SAM2003DB1\backup\restores\wzzip 'D:\SAM2003DB1\BACKUP\restores\'+ @filename + '.zip' 'D:\SAM2003DB1\BACKUP\restores\'+ @filename + '.bak'''

to this:
declare @cmd varchar(2000)
set @cmd = 'D:\SAM2003DB1\backup\restores\wzzip D:\SAM2003DB1\BACKUP\restores\' + @filename + '.zip D:\SAM2003DB1\BACKUP\restores\'+ @filename + '.bak'
Exec master.dbo.xp_cmdshell @cmd

Be One with the Optimizer
TG

wendy
Starting Member

19 Posts

Posted - 2005-04-28 : 09:48:08

TG,

It works greate.

Thanks!

Wendy

quote:
Originally posted by TG

Wendy,

not necessary but loose the double quotes here:
>>exec master.dbo.xp_cmdshell 'dir "D:\SAM2003DB1\BACKUP\Restores\"'
exec master.dbo.xp_cmdshell 'dir D:\SAM2003DB1\BACKUP\Restores\'

and change this:
Exec master.dbo.xp_cmdshell ''D:\SAM2003DB1\backup\restores\wzzip 'D:\SAM2003DB1\BACKUP\restores\'+ @filename + '.zip' 'D:\SAM2003DB1\BACKUP\restores\'+ @filename + '.bak'''

to this:
declare @cmd varchar(2000)
set @cmd = 'D:\SAM2003DB1\backup\restores\wzzip D:\SAM2003DB1\BACKUP\restores\' + @filename + '.zip D:\SAM2003DB1\BACKUP\restores\'+ @filename + '.bak'
Exec master.dbo.xp_cmdshell @cmd

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources