RE: Job issue almost working

RE: Job issue almost working

 

  

Put "exec" before calling the procedure.

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'''
exec xp_cmdshell @BackupName


-----Original Message-----
From: Jumma Salim
[mailto:mssqldba-ezmlmshield-x74596522.[Email address protected]
Sent: Tuesday, February 28, 2006 12:22 PM
To: LazyDBA Discussion
Subject: 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



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



The contents of this e-mail are intended for the named addressee only. It
contains information that may be confidential. Unless you are the named
addressee or an authorized designee, you may not copy or use it, or disclose
it to anyone else. If you received it in error please notify us immediately
and then destroy it.


MS Sql Server LazyDBA home page