July 9, 2001 at 12:00 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/lsmith/usingcomtooutputqueriestoatextfile.asp
July 31, 2001 at 9:24 am
Good article but the reader should be aware that sp_ExportData calls sp_displayoaerrorinfo which calls sp_hexadecimal. These are not supplied with the downloads but can be found in SQL Server Books On Line.
August 6, 2001 at 10:14 pm
If you really want to use DTS, you can set anything you'd like in code. For the SQL, check the SourceSQLStatement of the DataPumpTask object. For the text file name and location, set the datasource property of the connection object. With the exception of file format translations, there is little you can do with DTS that cannot be done with TSQL. If anyone knows how to take an excel file and import it using only TSQL, I'd like to hear about it!
Now for some stupid questions...
Why not use bcp and xp_cmdshell?
If you're going to involve VB, why write all those property let statements instead of passing those values to your procedure? i.e.
sqltoText(ByVal server as string, ByVal database as string, ByVal sql as string, ByVal filename as string, ByVal ConString as string)
Finally, why not;
txt.Write rs.GetString
Please advise.
January 16, 2002 at 3:21 pm
If you really want to import data from an excle file with T-SQL only, you can do this:
1. Open a passthrough query. This can be done by first creating a linked server, and then querying against it or using OPENROWSET() (there are more wyas to do it, but these two are good enough as an example)
2. remove linked server (if you used the linked server method.
All in all, you're looking at 2-3 lines of SQL code.
As to the statement that you can't do with DTS anything that you can't do with regular SQl, I don't agree with it...but it's just MHO.
regards, Mark.
quote:
If you really want to use DTS, you can set anything you'd like in code. For the SQL, check the SourceSQLStatement of the DataPumpTask object. For the text file name and location, set the datasource property of the connection object. With the exception of file format translations, there is little you can do with DTS that cannot be done with TSQL. If anyone knows how to take an excel file and import it using only TSQL, I'd like to hear about it!Now for some stupid questions...
Why not use bcp and xp_cmdshell?
If you're going to involve VB, why write all those property let statements instead of passing those values to your procedure? i.e.
sqltoText(ByVal server as string, ByVal database as string, ByVal sql as string, ByVal filename as string, ByVal ConString as string)
Finally, why not;
txt.Write rs.GetString
Please advise.
January 16, 2002 at 4:20 pm
Thanks for the reply. I've never created a linked server in code before. I'll look into that. Sounds like a good option.
With regards to the DTS vs TSQL comment, you lost me somewhere in the double negative. I was trying to say, if it can be done with a dts package, it can be done in tsql script.
January 16, 2002 at 6:22 pm
You're right about my latter statement. On the second reading, I realized that I was unclear. My English teacher had always told me not to use double negation in speech :))
What I was really trying to say was that some things that you can do with DTS, are almost impossible to do in SQL code. DTS, in fact, exposes some of the interesting functionality in SQL server that pure T_SQL can't always afford. As a example, think about ActiveX transformations--these are very powerful. I'm doing all kinds of actions and transformations using VBscript and ActiveX, and combined with parallel execution of tasks, as well as flexible logic and execution precedence (i.e. generating DTS packages and tasks within DTS packages on the fly at run-time), DTS does win the race here, I think.
And the least argument....As with every other Micro$oft product, you never get the full scripting capabilities of something that is otherwise wriiten as an application. That is to say that SQL server is written as whole bunch of pre-made objects, and traditionally, Microsoft doesn't provide you with a full scripting access to all of its functions. Therefore, T-SQL can't provide as much access to some areas of SQL server that DTS can. I know this last statement is pretty vague and weak as an argument, but it's more of a philosophical nature, rather than of technical.....
regards,
Mark.
quote:
Thanks for the reply. I've never created a linked server in code before. I'll look into that. Sounds like a good option.With regards to the DTS vs TSQL comment, you lost me somewhere in the double negative. I was trying to say, if it can be done with a dts package, it can be done in tsql script.
January 16, 2002 at 11:09 pm
I don't know what kind of vbscript transformations you're doing. Most I've seen are string manipulations combined with if then logic. This can all be done in SQL.
I'd be interested to see how and why you're building dts packages from within dts packages... I have written stored procedures that build and execute sql, but never packages that build and execute packages. Got an example to share?
January 16, 2002 at 11:48 pm
I'd be glad to do so. Just e-mail me at the address in my profile, so that I have your e-mail address and I will try to find you some relevant code snippets.....
regards,
Mark.
quote:
I don't know what kind of vbscript transformations you're doing. Most I've seen are string manipulations combined with if then logic. This can all be done in SQL.I'd be interested to see how and why you're building dts packages from within dts packages... I have written stored procedures that build and execute sql, but never packages that build and execute packages. Got an example to share?
February 11, 2002 at 10:25 am
The article assumes too much. I read the 'article', downloaded the code, and hadn't a clue what to do next.
1.) Where should the .dll and .cls be saved?
2.) Is the .sql to be run against Master or Northwinds.
3.) Then what?
I'm not a verteran coder, but I'm not a rookie either; if I were grading this 'article' I would give give it an 'Incomplete'.
February 11, 2002 at 12:13 pm
Im sorry if the article didnt provide you with all the info you needed. The dll needs to be registered on the server using regsvr32. The location doesn't matter but I like to put mine under the MSSQL folder in a DLL folder so that when I upgrade or move I remember they are there! As far as I can tell the script should be run against whatever db you're going to be doing the export from.
Andy
February 12, 2002 at 12:36 pm
Thanks Andy, I greatly apprecitae the prompt and informative reply.
Best Regards,
Dunc
March 26, 2002 at 9:14 am
I am trying to call the stored proc (which inturn call the DLL) from an update trigger. this is not able to write to the file.
bu if the SP is called directly in query analyser, it's working fine.
can anybody guide me to a solution ?
thanks
-hari
March 26, 2002 at 11:08 am
Confirmed permissions on the proc? Are you executing it conditionally?
Andy
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply