January 18, 2007 at 1:27 pm
Hi,
I have been trying to write an SP that runs a DTS using xp_cmdshell, and am finding that this simply won't work (there are spacing differences that for some reason will not resolve, no matter what I try), so I was going to try the approach of scheduling the DTS as a job and using the SP to kick off the job, but my dilemma now is how do I fill in the variables? There are two variables that need to have answers before the job should proceed, and using xp_cmdshell I could put them as parameters on the SP and feed them into the command string that way, but if I'm going to have to schedule a job to do it, how do I accomplish this? As far as I can see the job won't prompt for input.
Thanks,
Kerstin
January 18, 2007 at 2:47 pm
What kind of spacing issues are you referring to? You should be able to execute a DTS package by using master..xp_cmdshell 'DTSRUN /~Z0x39210848502ADD5...etc'. If you need to be able to pass a parameter into your DTS package, have your package reference global variables and pass the varaibles into your package in the DTSRUN call. See this article for an example:
http://www.sqlservercentral.com/columnists/aKersha/dtsandglobalvariables.asp
January 18, 2007 at 4:59 pm
It's very obscure, but MS does have a notation of it on their website as a known problem. I SHOULD have bookmarked the address, but I actually found it out here, on this forum.
I looked at that article, and it seems to be using CONSTANTS rather than VARIABLES, in that the values passed into the variables are always the same, but I need a way that actually prompts the user and then passes the answers to those prompts into the job. I am actually writing a VB app that does the prompting, but the second half of that is where I am stumped.
It just came to me that I probably need to pout some sort of Activex script in the job, too, to prompt for input.
If I could get the xp_cmdshell thing to work, I'd be very happy, but it is really being very uncooperative.
January 18, 2007 at 5:09 pm
I guess you could say they are Constants in the way that the article uses them as you would need to change the job to change the values. I meant that only to be an example of how to pass a value into a DTSRUN statement. My intention was for you to be able to use the variable values to build a DTSRUN command and then pass that into xp_cmdshell within a stored procedure. I am able to run DTS packages using xp_cmdshell without problem. If you could elaborate as to the problems you are having with DTSRUN and xp_cmdshell, someone may be able to help with a resolution.
January 18, 2007 at 5:47 pm
Currently my string is this:
select @vcCmdString = 'DTSrun /E /NMAILING /G{EDE27100-CC1B-4FEB-A9E3-6E4D23E0EB5B} /V{DA7673E7-0080-406B-BC9B-DOEODA31D752} /F C:\MAILING.dts/AgvInputFile:8=" + @InputDSK + '"/AgvKey:8="' + @key + '"'
exec master..xp_cmdshell @vcCmdString
If I put spaces in front of the /A parts the DTS job just hangs (which is what I mean about the spacing issue)
(I have declared my @variables as constants for the purpose of getting this right)
And it keeps giving me an error:
Error: -2147024773 (8007007B) Provider error: O (0)
Error string: The filename, directory name, or volume label syntax is incorrect.
(My cut and paste is not working right now, so I copied this from the error message.
It may not be EXACTLY right, but it's probably a matter of O vs 0)
I have tried many iterations of this, but it usually comes down to the same error, ultimately.
January 21, 2007 at 10:43 am
I found the KB article where this was mentioned, by the way:
http://support.microsoft.com/kb/308801
I am still trying to make this work. I have put the string into a .bat file and am running that, also with no luck.
January 22, 2007 at 10:01 am
It looks like you've got some redundant references to your package in your DTSRUN command string. I can't say if this is your problem or not, but consider this example:
Set up a simple DTS Package called gvTest. Create a global variable gvTest of type string. Create an ActiveX task using the following code:
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main ()
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("C:\temp\")
Set FileList = objFolder.files
'Create Working File
TextFile = objFolder & "\gvTest.txt"
Set objOutputFile = objFSO.CreateTextFile(TextFile)
objOutputFile.WriteLine DTSGlobalVariables("gvTest").Value
objOutputFile.Close
Main = DTSTaskExecResult_Success
End Function
Then execute the following code in QA and see what you get. This runs without error for me.
declare @vcCmdString varchar(8000)
select @vcCmdString = 'DTSrun /E /S <your Server here> /NgvTest /AgvTest:8=GlobalVariableTest'
exec master..xp_cmdshell @vcCmdString
January 22, 2007 at 10:21 am
I copied the string from dtsrunui, and put the 'answers' to my parameters into it, so any redundancy is what it expected, apparently.
I can try your suggestion, but I think I may have figured out why it's hanging. I created the .bat file, and ran it directly from the cmd prompt, and it finally (after changing the order of some switches per the KB article) didn't give me a "file not found" error, but even though the GVs have values in them, it still stopped and prompted me for input. I imagine that is what it's doing when I run it from xp_cmdshell. I am not sure the client that I am doing this for is all up to date with service packs and such on SQL Server 2000, either (and neither are they. It's a very small shop with one IT guy).
January 22, 2007 at 10:36 am
The KB article that you posted earlier states that the fix for that problem is in SP2. If you client has not applied SP2 as of yet, you should strongly urge them to do so. This was the SP that had all of the security fixes in it for the SQL Slammer worm. Also, if you need them to apply a service pack so that you can give them functionality that they require, they should be fairl open to patching their system.
January 22, 2007 at 10:43 am
They have a go live date of 2/3 for this project, and they don't want to rock the boat beforehand, so the guy said that he would probably do any SP application after that date, and run this stuff manually if need be (which works perfectly, by the way), in the meantime.
January 24, 2007 at 10:05 am
I had them check, and it has been determined that they are on SP4, so THAT'S not the issue. I have tried just running the package as 'dtsrun /F c:\mailing.dts' and it still prompts for input, so then I made a file called dtsRun,txt, that contains just the /F c:\mailing.dts on the first line, and the answers to the prompts on the next two lines, and now we are back to the filename, or volume label is bad error that I was getting before.
January 24, 2007 at 11:49 am
January 24, 2007 at 1:51 pm
Not yet. I've been really busy this morning, but I have a window open with your example in it, so I just need to DO it. I'll post something when I do that.
January 24, 2007 at 5:20 pm
OK, I tried the sample, and here's the error message I get:
Error string: The specified DTS Package ('Name = 'gvTest'; ID.VersionID = {[not specified]}.{[not specified]}') does not exist.
I checked and it's there (I saved it as a Structured Storage File), I opened it up and verified the name. One thing it doesn't do is prompt me for any kind of input. I put the Activex script in there, and defined a global variable (gvTest, as a string). In my DTS package I have a SQL command to select top 10 * from OFFERS where OFFERNO = ?. I defined the global variable in a Dynamic Property task. The workflow goes from the Activex to the dynamic property to the SQL command. When I run it, it just completes without asking me anything, and a file gets generated in the c:\temp folder that says in it.
January 25, 2007 at 9:34 am
I'm a bit confused. So you did or did not get my example to work? Also, isn't the point of this so that you can pass in your global variable values in the DTSRUN string instead of having it prompt you?
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply