dtsrun

[rstum2005]

Ive created a stored procedure to do some joins and create a table in which I will use to produce a spreadsheet, on top of this I created a job to do this and for the second step ive added a xp_cmdshell 'dtsrun ect. script to run the DTS in which "should" create an excel spreadsheet onto my desktop for now. The problem lies in creating running this script. I can run it in my cmd prompt and it works fine, it runs the dts which then creates the spreadsheet and it appears on my desktop, But when I run the second step in the job it does not appear? any clues?

Thanks.

 

[rstum2005]

come to think about it the funny thing is the step in the Job does complete sucessfully but the spreadsheet does not appear on the desktop. (and yes I did hit refresh).

 

[mrdenny]

The excel sheel will be getting created on the servers hard drive in the same path as your profile on your workstation. To have it save to your local desktop you would need to give the server the full UNC path to your workstations desktop (as well as rights to the network share that it's using).

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[rstum2005]

I see what you mean so in other words i need to change the path from somthing like "c:\documents and settings\ron\desktop" to "\\computername\share" ??

 

[rstum2005]

Ok I got it to work by doing the procedure above...Now is there a way to exec the xp_cmdshell from a stored procdure?

Right now with the line below it gives an error that says I cannot use the "use master" in the stored proc.

use master
exec xp_cmdshell 'dtsrun /Ssql1000d /Uidweb /Pacc /NExportElead_SpreadSheet'

 

[rstum2005]

well geez nevermind I just put a stored proc on master to exec the xp_cmdshell command and then added that sp to the other one. It works great thanks anyways....

 

[rstum2005]

it took me a whole 5 minutes to figure it out

 

[mrdenny]

You can call xp_cmdshell from another database's procedure by calling it via the 3 part name.

create procedure dbo.usp_Something as
exec master.dbo.xp_cmdshell ...

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com