RE: Job issue almost working

RE: Job issue almost working

 

  

Its actually syntax error near xp_cmdshell

declare @BackupName varchar(200)
Set @BackupName = '''copy' + ' "e:\Microsoft SQL Server\MSSQL\BACKUP\'
+ 'my_db_' + CONVERT(varchar(8), GETDATE(), 112) +
'2100' + '.BAK" '
+ '"\\10.10.10.10\backup\"'', ''no_output'''
xp_cmdshell @BackupName



Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'xp_cmdshell'.





-----Original Message-----
From: Jay Butler
[mailto:mssqldba-ezmlmshield-x15674470.[Email address protected]
Sent: Tuesday, February 28, 2006 12:10 PM
To: LazyDBA Discussion
Subject: Re: Job issue almost working

The error is caused by the double-quotes. From BOL: "Enclosing a
character constant in single quotation marks is recommended. Enclosing a

character constant in double quotation marks is sometimes not allowed
when the QUOTED IDENTIFIER option is set to ON."

This gets rid of that error:

declare @BackupName varchar(200)
Set @BackupName = ''''+'copy' + ' "e:\Microsoft SQL
Server\MSSQL\BACKUP\'+'my_db_'
+ CONVERT(varchar(8), GETDATE(), 112) + '2100' + '.BAK" '+
'"\\10.10.10.10\backup\"'+ ''''
+ ',no_output'
PRINT @BackupName



Jumma Salim wrote:
> This show syntax error
>
> declare @BackupName varchar(200)
> Set @BackupName = "'"+'copy' + ' "e:\Microsoft SQL
Server\MSSQL\BACKUP\'
> +'my_db_' + CONVERT(varchar(8), GETDATE(), 112) + '2100' + '.BAK" '+
> '"\\10.10.10.10\backup\"'+ "'"+ ',no_output'
>
> xp_cmdshell @Backupname
>
> -----Original Message-----
> From: Paul Ho
> [mailto:mssqldba-ezmlmshield-x29373364.[Email address protected]
> Sent: Tuesday, February 28, 2006 11:47 AM
> To: LazyDBA Discussion
> Subject: RE: Job issue almost working
>
> Do it in restartable mode switch /z over network especially if file is
> large
>
> -----Original Message-----
> From: Jumma Salim
> [mailto:mssqldba-ezmlmshield-x81028368.[Email address protected]
> Sent: February 28, 2006 11:40 AM
> To: LazyDBA Discussion
> Subject: FW: Job issue almost working
>
>
>
>
> Sorry I wrote in the email wrong
>
> declare @BackupName varchar(200)
> Set @BackupName = "'"+'copy' + ' "e:\Microsoft SQL
Server\MSSQL\BACKUP\'
> +'my_db_' + CONVERT(varchar(8), GETDATE(), 112) + '2100' + '.BAK" '+
> '"\\10.10.10.10\backup\"'+ "'"+ ',no_output'
>
> xp_cmdshell @Backupname
>
> -----Original Message-----
> From: Bellizzi Christopher - IBG [mailto:Christopher.[Email address
> protected]
> Sent: Tuesday, February 28, 2006 11:33 AM
> To: Jumma, Salim
> Subject: RE: Job issue almost working
>
> **A LazyDBA.com subscriber has responded to your lazydba.com post**
> **LazyDBA.com mail shield has forwarded you this email,
> **and removed any attachments, and kept your email address secret
> **from this person, and any viruses/trojans.
> **If you reply to this email, the person will see your email address
as
> normal
> **Anything below this line is the original email text
>
>
> Your variable is named different at the end?
>
>
> Christopher Bellizzi
> SQL DBA/Programmer
> 240 Falls Street
> Seneca Falls, NY 13148
> Phone: (315) 568-7886
> Email: [Email address protected]
> -----Original Message-----
> From: Jumma Salim
> [Email address protected]
> Sent: Tuesday, February 28, 2006 11:25 AM
> To: LazyDBA Discussion
> Subject: Job issue almost working
>
> I have set up a job to transfer file
>
>
>
>
>
> declare [Email address protected] varchar(200)
>
> Set [Email address protected] = 'xp_cmdshell'+"'"+'copy' + '
> "e:\Microsoft sql
> server\MSSQL\Backup\' +'Mydb_' + CONVERT(varchar(8), GETDATE(), 112) +
> '2100' + '.BAK"'+
>
> '"\\10.10.10.10\backup\"'+ "'"+ ',no_output'
>
> print [Email address protected]
>
>
>
>
>
> IT prints the command as I want; but when I do
>
>
>
> declare [Email address protected] varchar(200)
>
> Set [Email address protected] = 'xp_cmdshell'+"'"+'copy' + '
> "e:\Microsoft sql
> server\MSSQL\Backup\' +'Mydb_' + CONVERT(varchar(8), GETDATE(), 112) +
> '2100' + '.BAK"'+
>
> '"\\10.10.10.10\backup\"'+ "'"+ ',no_output'
>
>
>
> [Email address protected]
>
>
>
>
>
> Give me syntax error what I am doing wrong
>
>
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Website : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
> ************************************
> This e-mail and any files transmitted with it are proprietary and
> intended solely for the use of the individual or entity to whom they
are
> addressed. If you have received this e-mail in error please notify the
> sender. Please note that any views or opinions presented in this
e-mail
> are solely those of the author and do not necessarily represent those
of
> ITT Industries, Inc. The recipient should check this e-mail and any
> attachments for the presence of viruses. ITT Industries accepts no
> liability for any damage caused by any virus transmitted by this
e-mail.
> ************************************
>
>
>
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Website : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Website : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Website : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>


---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


MS Sql Server LazyDBA home page