Systax help quotation marks driving me nuts

Question

Post reply

Systax help quotation marks driving me nuts

Jeff-132889

Ten Centuries

Points: 1112
More actions
July 27, 2004 at 11:28 am

#112096

Someone please tell me what I'm doing wrong here:
When I run this it almost works but the path to the device I'm creating is wrong. The device path should be:
'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Diffs\traffic_Diff.BAK'
but it's executing as:
'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Diffs\' traffic_Diff.BAK

declare @IDENT INT, @sql varchar(1000), @DBNAME VARCHAR(200)
select @IDENT=min(DBID) from SYSDATABASES WHERE [DBID] > 0 AND NAME NOT IN ('PUBS', 'NORTHWIND', 'TEMPDB', 'master', 'model', 'msdb', 'tempdb')
while @IDENT is not null
begin
SELECT @DBNAME = NAME FROM SYSDATABASES WHERE DBID = @IDENT
SELECT @sql = 'EXEC sp_addumpdevice ''disk'', ' +@DBNAME+
', ''C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Diffs\''' +@DBNAME+ '_Diff.BAK'
PRINT @sql
EXEC (@SQL)
select @IDENT=min(DBID) from SYSDATABASES WHERE [DBID] > 0 and DBID>@IDENT AND
NAME NOT IN ('PUBS', 'NORTHWIND', 'TEMPDB', 'master', 'model', 'msdb', 'tempdb')
end

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

Change this:

''C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Diffs\'''

to:

''C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Diffs\'

I've removed two single quotes at the end. That tells SQL Server to add a single quote into the string.

K. Brian Kelley
@kbriankelley

Jeff-132889 Ten Centuries Points: 1112 More actions · Answer 2

Thanks. I tried that and got:

Server: Msg 170, Level 15, State 1, Line 20

Line 20: Incorrect syntax near ''.

Server: Msg 105, Level 15, State 1, Line 20

Unclosed quotation mark before the character string '

'.

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 3

Oops. Try and replace this whole piece:

SELECT @sql = 'EXEC sp_addumpdevice ''disk'', ' +@DBNAME+

', ''C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Diffs\''' +@DBNAME+ '_Diff.BAK'

with this:

SELECT @sql = 'EXEC sp_addumpdevice ''disk'', ' +@DBNAME+

', '''C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Diffs\' +@DBNAME+ '_Diff.BAK'''

Make sure it looks right in your PRINT(@SQL).

K. Brian Kelley
@kbriankelley

Jeff-132889 Ten Centuries Points: 1112 More actions · Answer 4

I appreciate the help. This is alot more of pain than it should be. Same unclosed quote error:

Here is the current code:

declare @IDENT INT, @sql varchar(1000), @DBNAME VARCHAR(200)

select @IDENT=min(DBID) from SYSDATABASES WHERE [DBID] > 0 AND NAME NOT IN ('PUBS',

'NORTHWIND', 'TEMPDB', 'EStar', 'master', 'MAW_FE', 'MAWData', 'MAWOfficeSupply',

'model', 'msdb', 'tempdb', '_MAWDataDev', 'reports')

while @IDENT is not null

begin

SELECT @DBNAME = NAME FROM SYSDATABASES WHERE DBID = @IDENT

SELECT @sql = 'EXEC sp_addumpdevice ''disk'', ' +@DBNAME+

', '''C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Diffs\' +@DBNAME+ '_Diff.BAK'''

PRINT @sql

EXEC (@SQL)

select @IDENT=min(DBID) from SYSDATABASES WHERE [DBID] > 0 and DBID>@IDENT AND

NAME NOT IN ('PUBS', 'NORTHWIND', 'TEMPDB', 'EStar', 'master', 'MAW_FE', 'MAWData',

'MAWOfficeSupply', 'model', 'msdb', 'tempdb', '_MAWDataDev', 'reports')

end

gljjr SSChampion Points: 10080 More actions · Answer 5

Try

declare @IDENT INT, @sql varchar(1000), @DBNAME VARCHAR(200)

select @IDENT=min(dbid) from sysdatabases WHERE [dbid] > 0 AND name NOT IN ('PUBS',

'NORTHWIND', 'TEMPDB', 'EStar', 'master', 'MAW_FE', 'MAWData', 'MAWOfficeSupply',

'model', 'msdb', 'tempdb', '_MAWDataDev', 'reports')

--while @IDENT is not null

begin

SELECT @DBNAME = name FROM sysdatabases WHERE dbid = @IDENT

SELECT @sql = 'EXEC sp_addumpdevice ''disk'', ' +@DBNAME+ ', ''C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Diffs\' +@DBNAME+ '_Diff.BAK'''

PRINT @sql

EXEC (@SQL)

select @IDENT=min(DBID) from SYSDATABASES WHERE [DBID] > 0 and DBID>@IDENT AND

NAME NOT IN ('PUBS', 'NORTHWIND', 'TEMPDB', 'EStar', 'master', 'MAW_FE', 'MAWData',

'MAWOfficeSupply', 'model', 'msdb', 'tempdb', '_MAWDataDev', 'reports')

end

BTW: a good thing to do is to change the color of your "String" in QA to something like Red so that it shows up what is going on. This can be done by going to the Tools/Options menu and clicking on the Fonts tab. Then in the "Colors" listbox select String and change the foreground color to what you want.

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

Jeff-132889 Ten Centuries Points: 1112 More actions · Answer 6

Thanks Gary and Brian, it's getting closer. That works to create the device. I just have to find out now why the looping thru the sysdatabases is busted.

Jeff-132889 Ten Centuries Points: 1112 More actions · Answer 7

Thanks again guys. Everything is working perfectly. I have over 100 databases running on a cluster that I wanted to do a differential backup on everynight and wanted to catch new databases as they are added so this was a huge timesaver.

Jeff