master..xp_cmdshell 'DTSRUN %' where % represents the commands you would send to DTSRUN. IIRC, this needs to be executed with dbo authority...must check.
http://www.sqldts.com/ has great info on this subject. David L. Penton, Microsoft MVP JCPenney Application Specialist / Lead "Mathematics is music for the mind, and Music is Mathematics for the Soul. - J.S. Bach" [EMAIL PROTECTED] Do you have the VBScript Docs or SQL BOL installed? If not, why not? VBScript Docs: http://www.davidpenton.com/vbscript SQL BOL: http://www.davidpenton.com/sqlbol -----Original Message----- From: S.Isaac Dealey > OK, let me lay my problem on the table...it might help you > help me > (scenes from Jerry Maguire): You had me from OK. :) > This sounds very much like a job for XML, but I'm stuck at > the step of > getting the data from server A to the SQL Server. The > client can save > the delimited text file onto a shared network drive, but: > a) I don't know how to get SQL Server to pick it up and > import the data *on command* (not regularly scheduled) Not something that gets done frequently in the places where I work, but my understanding is that it's done by calling <cfexecute> to run isql.exe ( Query Analyzer minus the mmc shell i believe... the shell is isqlw.exe ) from the command line ... > b) it would be nice if these two entities could > communicate more > directly (this may be a problem because the source > database is > proprietary, not ODBC) Hrm... Is the source db not accessible to ColdFusion using any kind of drivers? I mean... Is it so proprietary that you couldn't connect to it through ColdFusion? My initial response was going to be: use <cfhttp method="post"> to send a wddx serialized copy of the report query to the 2nd server ... but then if the 1st server where the report is generated is inaccessible to CF I'm not sure how to make that happen... :-/ > I am open to any ideas. The only one I could come up with > was to have > the client save the file on the network drive and execute > on command the > DTS to import the data. But as you can tell from my first > post on this > topic, I don't know how (or even if) this can be done. You probably want to take a look at the Books Online documentation for SQL Server specifically regarding "Bulk Insert" ... that's the feature that allows you to read data into a table from a delimited file on the server... I'm not certain off the top of my head if you can use it from within <cfquery> or a stored procedure, which would make things easier since you wouldn't have to resort to using isql.exe from the "command line" through <cfexecute> Isaac Certified Advanced ColdFusion 5 Developer www.turnkey.to 954-776-0046 ------------------------------------------------------------------------- This email server is running an evaluation copy of the MailShield anti- spam software. Please contact your email administrator if you have any questions about this message. MailShield product info: www.mailshield.com ----------------------------------------------- To post, send email to [EMAIL PROTECTED] To subscribe / unsubscribe: http://www.dfwcfug.org
