SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Prevent sql job failure.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

funketekun
Constraint Violating Yak Guru

Australia
491 Posts

Posted - 05/21/2008 :  14:34:46  Show Profile  Visit funketekun's Homepage  Reply with Quote
Have a job that calls a DTS package, DTS is an Export & Import wizard to copy tables. Someone deleted a table from source and my job failed last night. Inputs appreaciated.

tkizer
Almighty SQL Goddess

USA
35953 Posts

Posted - 05/21/2008 :  15:04:33  Show Profile  Visit tkizer's Homepage  Reply with Quote
You forgot the most important part of posting a question: the question!

What is your question?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx
Go to Top of Page

funketekun
Constraint Violating Yak Guru

Australia
491 Posts

Posted - 05/21/2008 :  16:20:36  Show Profile  Visit funketekun's Homepage  Reply with Quote
how to prevent this?...they told me the tables should be drop at anytime.

Edited by - funketekun on 05/21/2008 16:22:16
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35953 Posts

Posted - 05/21/2008 :  16:24:31  Show Profile  Visit tkizer's Homepage  Reply with Quote
Modify the DTS package to check if it exists or modify the job to do the check. It's probably easier to just modify the job. Before the call to dtsrun add an IF statement that checks for the table(s). Only run the package if they exist.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx
Go to Top of Page

funketekun
Constraint Violating Yak Guru

Australia
491 Posts

Posted - 05/21/2008 :  16:32:01  Show Profile  Visit funketekun's Homepage  Reply with Quote
but there are 100 tables.....do i need to write 100 checks...oh my good...
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35953 Posts

Posted - 05/21/2008 :  16:35:04  Show Profile  Visit tkizer's Homepage  Reply with Quote
Are you just copying a database?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx
Go to Top of Page

funketekun
Constraint Violating Yak Guru

Australia
491 Posts

Posted - 05/21/2008 :  16:37:06  Show Profile  Visit funketekun's Homepage  Reply with Quote
yes But from 2005 to 2000
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35953 Posts

Posted - 05/21/2008 :  16:45:12  Show Profile  Visit tkizer's Homepage  Reply with Quote
You're going to need to write a bunch of code. I think I'd find out what tables were there via INFORMATION_SCHEMA.TABLES and then bcp those out to files. Once the data is in the files, I'd have another process that looped through a folder and processed each found file. Perhaps you could name your files the same as the table names to make the import dynamic and easy.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx
Go to Top of Page

funketekun
Constraint Violating Yak Guru

Australia
491 Posts

Posted - 05/21/2008 :  16:48:29  Show Profile  Visit funketekun's Homepage  Reply with Quote
oh my good.
Go to Top of Page

funketekun
Constraint Violating Yak Guru

Australia
491 Posts

Posted - 05/21/2008 :  18:18:37  Show Profile  Visit funketekun's Homepage  Reply with Quote
anyway to use bcp without using xp_cmdshell?
xp_cmdshell is disable in the damn server.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35953 Posts

Posted - 05/21/2008 :  18:51:14  Show Profile  Visit tkizer's Homepage  Reply with Quote
It's disabled by default in SQL Server 2005. Can't you just enable it?

If you can't enable xp_cmdshell, then you'll need to do this from the command line through a batch file.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 05/21/2008 :  19:39:21  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Why not just use replication?

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 05/22/2008 :  10:30:02  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Also asked and answered here
http://www.sqlservercentral.com/Forums/Topic504746-5-1.aspx



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000