LITTLEBLACKDOG.COM

Posted: Thu Feb 15, 2007 7:01 am Post subject: Calling anyone that knows SQL

I am trying to edit a backup job that runs on one of our SQL servers and it's giving me an error. At first I thought it was the stuff I was changing that was giving me errors, and then I realized I can open it up to edit, then hit ok, and I still get the error. So although it works, and is backing up the DB like it should, there's a syntax error in here somewhere. I know pretty much zero about SQL, so can anybody check out this code:

Code: Select all

DECLARE @DateString varchar(100) select @DateString = Convert(varchar, GetDate(),121) select @DateString = SubString(Replace(Replace(Replace (Replace (@DateString,'-',''),' ',''),':',''),'.',''),1,14) Set @DateString = '\\Exchg2\vision backups\Vision20060127071519.bakVision' + @DateString + '.bak' BACKUP DATABASE [Vision] TO DISK = @DateString WITH NOINIT, NOUNLOAD, NAME = N'Vision Backup', NOSKIP, STATS = 10, DESCRIPTION = N'Vision20060127071519', NOFORMAT

And tell me what this error means:

Run-time error'-2147221334 (800400aa)':
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ','),'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark before the character string".

I get that I need to close a quotation somewhere, but I can't find that particular sequence of characters in line 1 anywhere... maybe I'm just blind.

Posted: Thu Feb 15, 2007 7:35 am Post subject: Re: Calling anyone that knows SQL

I don't get the error. I seperated everything onto its own lines; does that make a difference?

Code: Select all

DECLARE @DateString varchar(100) 





select @DateString = Convert(varchar, GetDate(),121) 





select @DateString = SubString(Replace(Replace(Replace (Replace (@DateString,'-',''),' ',''),':',''),'.',''),1,14) 





Set @DateString = '\\Exchg2\vision backups\Vision20060127071519.bakVision' + @DateString + '.bak' 





BACKUP DATABASE [Vision] TO DISK = @DateString WITH NOINIT, NOUNLOAD, NAME = N'Vision Backup', NOSKIP, STATS = 10, DESCRIPTION = N'Vision20060127071519', NOFORMAT

Posted: Thu Feb 15, 2007 7:50 am Post subject:

No, it just changed the error message to say line 3 instead of line 1. I'm trying to edit this in an MSDE console if that makes any difference.

Guide Dog Joined: 08 Nov 2003 Posts: 8419 Location: MN

I'm no expert, but we had issues with long queries once at a former job... something had to get changed server side to allow for anything longer so many charactors... might be something to look into...

Otherwise I slapped it into scite (horray for colored syntax) and it looked ok...

Posted: Thu Feb 15, 2007 11:03 am Post subject:

I'm just wondering how the hell the previous admin got it in there in the first place with it giving errors, because it won't save any changes that I make, it throw that error and when I hit ok the MSDE console exits and I have to open it up and login again to get back to it, so annoying.

Posted: Thu Feb 15, 2007 12:19 pm Post subject:

Is there any chance of a hidden character?

Posted: Thu Feb 15, 2007 12:26 pm Post subject:

Webster wrote:
Is there any chance of a hidden character?

I don't think so... I cut and pasted that into the query analyzer and it ran with no errors or problems whatsoever, backed up the DB just like it's supposed to.

Posted: Thu Feb 15, 2007 1:29 pm Post subject:

Ok, so after looking at this a different way... I'm thinking maybe instead of editing the current job I need to just create a new one and delete the old one. There's a delete button in the console, but no create button. So I'm guessing I need to run some kind of SQL script to do this.

Being the noob to SQL that I am, I'm starting to gather that MSDE lacks alot of the stuff that a full fledged SQL server has, ie, no real management console.

So what I'm thinking I need to do is get together some kind of script I can run that will create a job with this modified code (which works like a champ when I run it thru the query analizer):

Code: Select all

DECLARE @DateString varchar(100)





select @DateString = Convert(varchar, GetDate(),121)





select @DateString = SubString(Replace(Replace(Replace (Replace (@DateString,'-',''),' ',''),':',''),'.',''),1,14)





Set @DateString = '\\Fileserver\share\Net_Data\Software\Vision Backups\Vision' + @DateString + '.bak'





BACKUP DATABASE [Vision] TO DISK = @DateString WITH NOINIT, NOUNLOAD, NAME = N'Vision Backup', NOSKIP, STATS = 10, DESCRIPTION = N'Vision20060127071519', NOFORMAT

And schedule it to run everyday at 6am.

Anybody have any idea on how I would go about doing that?

Posted: Tue Feb 20, 2007 9:58 am Post subject:

I figured this out... had to create an sql script containing the following:

Code: Select all




USE msdb


EXEC sp_add_job @job_name = 'VisionBackup',


    @enabled = 1,


    @description = 'VisionBackup',


    @owner_login_name = 'sa',


    @notify_level_eventlog = 2,


    @notify_level_email = 2,


    @notify_level_netsend =2,


    @notify_level_page = 2


--  @notify_email_operator_name = 'email name'


go





-- Add job step (backup).


USE msdb


EXEC sp_add_jobstep @job_name = 'VisionBackup',


    @step_name = 'Backup Database',


    @subsystem = 'TSQL',


    @command = 'DECLARE @DateString varchar(100)





select @DateString = Convert(varchar, GetDate(),121) 





select @DateString = SubString(Replace(Replace(Replace (Replace (@DateString,''-'',''''),'' '',''''),'':'',''''),''.'',''''),1,14)





Set @DateString = ''\\Fileserver\share\Net_Data\software\vision backups\Vision'' + @DateString + ''.bak''





BACKUP DATABASE [Vision] TO DISK = @DateString WITH NOINIT, NOUNLOAD, NAME = N''Vision Backup'', NOSKIP, STATS = 10, DESCRIPTION = N''Vision20060127071519'', NOFORMAT',





    @on_success_action = 1,


    @retry_attempts = 5,


    @retry_interval = 5


go





-- Add the target servers.


USE msdb


EXEC sp_add_jobserver @job_name = 'VisionBackup', @server_name = N'(local)'





-- Schedule job.


USE msdb


EXEC sp_add_jobschedule @job_name = 'Visionbackup', 


    @name = 'ScheduledBackup_msdb',


    @freq_type = 4, --daily


    @freq_interval = 1, --once


    @active_start_time = '060000' --(6:00 am) 24hr HHMMSS.


go

And run it from a cmd prompt with the following:

Code: Select all

C:> osql -Usa -Pmypassword -i myscript.sql -n

and now it's in there working like a champ.