August 16, 2008 at 9:50 pm
Hope this is the right forum!
When I run this:
DECLARE @returncode int
EXEC @returncode = xp_cmdshell 'dtexec /dts "msdb/mypackage"'
print @returncode
Returncode always returns NULL whether the underlying SSIS mypackage succeeds or fails.
What am I missing here?
TIA,
barkingdog
August 18, 2008 at 1:34 am
If you do a DIR X:\ in cmdshell, what does it return? (Assuming x is an invalid drive)
_just_ incase, you are aware of SQL returning a NULL as the last row for cmdshell? You not confusing that null with your print?
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
August 18, 2008 at 10:08 am
The result of running cmdshell "Dir X:\" from Query Editor on my system gives this:
>>>>
The system cannot find the path specified.
NULL
>>>>
The line "print @returncode" in
DECLARE @returncode int
EXEC @returncode = xp_cmdshell 'dir m:\'
print @returncode
apparently printed nothing.
In fact I was NOT aware that SQL returns a NULL as the last row for cmdshell. (The above verifies this).
So it looks like "dtexec" swallows the error.
The basic problem we faced (and tried to solve as above) was to run an SSIS package, upon request,
from a client box without SSIS installed. Microsoft has an article on how to do this but their approaches
seem problematic at best.
TIA,
barkingdog
August 18, 2008 at 1:00 pm
I do not believe cmd or ssis swallow any errors. There is something else wrong.
Changing your print to a select?
Also, how are you trying to run SSIS without having ssis installed? I'm confused.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
August 18, 2008 at 8:21 pm
Crispin,
We have a database server with sql 2000 and a DTS package that runs fine their. The DTS package is stored in the msdb database and when we run a command like
xp_cmdshell " dtsrun /S msdb/myPackage"
on a client withtout sql, it runs fine.
We now want to upgrade that sevrer to sql 2005. I have updated the DTS package to an SSIS version, which too, runs fine locally. We wanted to try the same approach, enabling a non-sql client to execute a package on the sql server -- just like we did under sql 2000.
TIA,
barkingdog
BTW: I spoke to a developer and he complained that the sql 2000 "xp_cmdsshell" never returned an error code, just like I saw for sql 2005.
We have another machine (no sql installed on this one) that, upon user request, need to run the SSIS package on the sql server. We found that if the SSIS package is stored in the MSDB database then others have been able to successfully run a commwe like that
client demand,
wiWe are trying to execute th
have a server the uses ColdFusion to store a
August 19, 2008 at 1:33 am
The end of the post looks like keyboard corruption 🙂
Ok, so the client is not running the package - they are simply asking for it to be run. There is a difference.
Off the top of my head, I cannot confirm or deny the 2000 CMD thing but I really doubt it. It's been around for donkey's years. Pretty sure it would be working.
Anyhow, if your CMD way is not working, why not setup a job with the package and run sp_start_job. That does run it "out of process" though.
Have you tried running the package as a file and not from MSDB? (File packages are preferable to msdb store but that's a different topic)
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply