Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Using COM to Output Queries to a Text File Expand / Collapse
Author
Message
Posted Monday, July 9, 2001 12:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 13, 2003 12:00 AM
Points: 6, Visits: 1
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/lsmith/usingcomtooutputqueriestoatextfile.asp


Post #544
Posted Tuesday, July 31, 2001 9:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 29, 2008 7:49 AM
Points: 40, Visits: 24
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.


Post #19883
Posted Monday, August 6, 2001 10:14 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 4:30 PM
Points: 154, Visits: 10
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.






Post #19884
Posted Wednesday, January 16, 2002 3:21 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 9, 2012 1:29 PM
Points: 4, Visits: 3
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.








Post #19885
Posted Wednesday, January 16, 2002 4:20 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 4:30 PM
Points: 154, Visits: 10
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.




Post #19886
Posted Wednesday, January 16, 2002 6:22 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 9, 2012 1:29 PM
Points: 4, Visits: 3
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.








Post #19887
Posted Wednesday, January 16, 2002 11:09 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 4:30 PM
Points: 154, Visits: 10
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?





Post #19888
Posted Wednesday, January 16, 2002 11:48 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 9, 2012 1:29 PM
Points: 4, Visits: 3
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?









Post #19889
Posted Monday, February 11, 2002 10:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 17, 2008 5:55 PM
Points: 28, Visits: 34
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'.





Post #19890
Posted Monday, February 11, 2002 12:13 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 11:48 AM
Points: 6,783, Visits: 1,879
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


Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #19891
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse