March 19, 2011 at 8:20 am
Is I run this in a query window it's fine"
DECLARE @sqlCommand NVARCHAR(1000)
DECLARE @dateTime NVARCHAR(20)
SELECT @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),111),'/','') + REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')
SET @sqlCommand = 'BACKUP DATABASE ' + 'databasename' + ' TO DISK = ''D:\Program Files\Microsoft SQL Server\MSSQL.1\Backup\AF_Filesurf\' + 'databasename' + '_Full_' + @dateTime + '.BAK'''
But if I run it as @query in sp_send_dbmail, it keeps failing because of
the + OR the / after the 111. It says invalid syntax. Help
March 19, 2011 at 11:16 am
Why would u run that as sp_send_DBmail ? You should use EXEC or sp_ExecuteSQL for this..
March 19, 2011 at 2:26 pm
Coldcoffee popped up the correct question.
Keep in mind when you put things into a variable to be executed, you should double the quotes !
Schedule your backup job(steps) in a sqlagent job, it gives you nice feedback and the occasion to send appropriate messages.
Also keep in mind you may need to use quoted names for your databases !
Another remark.
Whats the length of "varchar" ? by default 15 !
How about replacing that mess with
SELECT @dateTime = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(23), GETDATE(), 120), '-', '') ,' ',''),':','')
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 19, 2011 at 2:42 pm
ALZDBA (3/19/2011)
Whats the length of "varchar" ? by default 15 !
1 in a declare, 30 in a cast.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 20, 2011 at 6:58 am
Because sp_send_dbmail, allow me to type what the Subject Line should say instead of having "SQL Server Message" And I am using sp_executeSQL.
March 20, 2011 at 7:03 am
GilaMonster (3/19/2011)
ALZDBA (3/19/2011)
Whats the length of "varchar" ? by default 15 !1 in a declare, 30 in a cast.
Thank you for correcting that.
Of course the message should have been "never trust a default".
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply