Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kmistic
Starting Member
40 Posts |
 Posted - 2005-03-02 : 16:11:10
|
| I need to make a stored procedure that will execute a dts package. I was testing my xp_cmdshell command in query analyzer and couldn't get it to work correctly. I generated the command by making the package a scheduled job and looking at the command there. I also used dtsrunui to generate a command, but it to fails. I will mention that all the command WORK correctly at a command prompt in cmd.exe command:EXEC master..xp_cmdshell 'DTSRun /~Z0xD4C427BCCAF6D25BB97434970F938AC69747995105517C686BDBA307A28DD6D602F47408369666A30B8B80E40FCD5B82A46E79B7A9C63F74B6F835436F4AA9BDF7CD2D0CD7BDD4B094771E3A77C79506C9479B'OUTPUT:DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 0 (0) Error string: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. NULLThanks |
|
|
PW
Yak Posting Veteran
95 Posts |
Posted - 2005-03-02 : 16:15:03
|
| Is the ODBC datasource a user datasource ? |
 |
|
|
kmistic
Starting Member
40 Posts |
Posted - 2005-03-02 : 16:23:02
|
| System dsn (.txt file) |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-02 : 21:44:05
|
| When you test it from the command prompt, is it from your desktop computer, or on the SQL Server itself? The DSN must be installed on the SQL Server in order for it to run from a job or by using xp_cmdshell. If you've been running it from your client machine then it will always succeed.A word to (and from) the wise: don't use ODBC or DSNs unless they are ABSOLUTELY necessary. Using OLE DB providers completely eliminates the problem you're having. |
|
|
|
kmistic
Starting Member
40 Posts |
Posted - 2005-03-02 : 22:11:06
|
| ahhh, so easy. I can't believe i overlooked that. btw, The dts package is inserting data i have extracted from a UniVerse Database. I have yet to find a better way of doing this. thanks |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-02 : 22:16:11
|
Sorry, forgot to mention that if there are no OLE DB providers available then yeah, ODBC is the only choice.  |
|
|
|
|
|
|
dtsrun package not working with xp_cmdshell