Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-11-06 : 08:22:17
|
| Tim writes "How do I accomplish the following?I am running SQL Server 2000 SP3 on Windows 2000 Server SP4. We have a program (written in VB.NET) that generates SQL for us on the fly from user selected text boxes and entered data. Once the user has selected everything they want, we generate the SQL statement, pass it to SQL server, run it, and bring back the data either on the screen or in a text file.Our next step is to be able to save this SQL statement and have it run whenever the person wants it to run (maybe put it in a batch). We want to be able to export this data that is created by the SQL statement to a text file (with or without column headings as well) but we don't want to use bcp (some of the SQL statements are huge!). How can we pass SQL statements to DTS in order to make this happen? We have tried to make several programs and have failed at this several times. Are we just missing something? Thank you in advance." |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-11-06 : 08:26:41
|
| For one thing, it's generally insecure to pass constructed SQL statements to a SQL Server from an application. If someone hacks your app, they can send commands to do all kinds of things that you didn't intend or want. This is called SQL Injection. It's better to rewrite this as a stored procedure that accepts ONLY values as parameters.In addition to making it more secure, it would then become much easier to use bcp to perform the text file output. DTS could do it but bcp will be A LOT easier to manage and customize. You can also use regular ADO or ADO.Net to transform the results of the stored procedure into a string and then write it out to a file. In ADO, look up GetRows and GetString, and I know ADO.Net has a number of methods to transform a dataset into text, XML, or an array. |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-11-06 : 08:27:15
|
| You can set a global variable in the dts package then use that in an activex step to set the source of an export.See http://www.nigelrivett.net/SetDTSRunTimeValues.htmlFor setting the connection - you can do similar things for the source of an export.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Exporting to text files using DTS and user generated SQL Statements