April 8, 2004 at 11:46 am
I'm in the middle of migrating all database objects to a new server including the scheduled jobs. I used the "Copy Database Wizard" to move the jobs.
This worked for all jobs EXCEPT those that contained T-SQL job steps.
Any ideas why CDW would not copy these jobs to the new server???
April 9, 2004 at 4:34 am
I have had difficulty getting DTS jobs to copy over as well. My work around was to go into design mode on the DTS package. On the menu bar go to Package and click Save As - then change the server to save it to when it prompts you.
April 9, 2004 at 8:00 am
Actually, I didn't have any problems with transferring the DTS packages over. Just the Jobs which execute the DTSs. If the Job had T-SQL steps, it was not moved. Can't figure that one out -- maybe it failed because the database did not exist on the server yet, because I told CDW to move "all Jobs" and I had not moved all of the databases yet.
Concerning the DTS packages, I used the following SQL in a data pump (per http://www.sqldts.com/?204) and it worked beautifully with no problems.
SELECT T1.*
FROM msdb.dbo.sysdtspackages AS T1
INNER JOIN
(SELECT [name], [id], MAX([createdate]) AS [createdate]
FROM msdb.dbo.sysdtspackages GROUP BY [name], [id]) AS T2
ON T1.[id] = T2.[id] AND T1.[createdate] = T2.[createdate]
April 9, 2004 at 8:18 am
I must've misread your post. Sorry about that. I don't have an answer for your problem with transferring the actual jobs.
April 9, 2004 at 8:50 am
My best guess is that since the database had not been moved yet, SQL Server could not parse the T-SQL for a database that does not exist on the server yet? If you go to create a Job and you choose T-SQL in as the "Type", you're asked to specify the database in the combo box. Since it's not there yet, it probably choked.
April 9, 2004 at 8:55 am
Another possibility -
You said that the jobs which contain T-SQL were the ones that failed. If the T-SQL contains any literal quotes (ie. 'dbname') that could throw script generation off. Try going to the job in Enterprise Manager, right click, all tasks, generate script, preview. Then cut and paste the resultant script into query analyzer to see if quotes are throwing you off.
Steve
April 9, 2004 at 9:15 am
I'm not sure that I understand. The jobs ran fine under the old server. When I migrated the jobs with CDW, the jobs containing t-sql were not copied to the new server by the wizard.
I do appreciate the tip on the "Generate SQL Script" though.
April 9, 2004 at 2:33 pm
The copy database wizard (along with most enterprise manager functions) use sql scripts behind the scenes. If those jobs didn't get migrated, while others did, it is probably due to an error in the sql script. I suggested scripting the jobs and checking syntax because you would be creating the same script that the wizard does.
Steve
April 9, 2004 at 2:58 pm
I see what you're saying, but if the job was executing successfully on the old server, wouldn't that mean that the scripting should come out ok?
April 9, 2004 at 3:56 pm
When you generate the script, the tsql commands that are in the job BECOME a literal which is enclosed in single quotes. If the commands CONTAIN literals enclosed in single quotes they will throw the whole thing off.
like this....
I have a tsql command in a job -
SET @some_var = 'some text'
execute some_proc
Now I script the job. Within the script will be
@command = 'SET @some_var = 'some text' execute some_proc'
The quotes around 'some text' end the literal and sql tries to EXECUTE [some text].
disclaimer - I know I have seen this, but I just tried scripting a job that I knew contained quotes, and it scripted ok, so maybe it was fixed in a service pack????
Steve
April 9, 2004 at 4:33 pm
Yes, I can see where quotes in the T-SQL would cause SQL Server to think that the first quote it encountered due to the 'literal' was ending the SQL statement and cause problems.
August 29, 2006 at 8:27 pm
I in the middle of migration from SQL 2000 to 2005. When i first tried to use the Copy Database Wizard (CDW) - the last step of transfering agent jobs always fails. So - i used other option - Restoring the database object from a Bakup file, and it works fine.
To transfer the Packages - i used the Data Transformation Services under 'Management \ Legacy'
HOW DO I transfer the Jobs ? which wizard to use (where is it)? need any configuration? where do you specify the "All jobs" to move?
Ron (C#\SQL Dev)
August 30, 2006 at 9:21 am
Ron,
The easiest way to move jobs is to script them out and execute the scripts on the destination server. Here's a similar thread: http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=110&messageid=304226#bm304671
Greg
Greg
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply