Can someone help me with some basic SQL code?

Question

I have a table in MS SQL server. I would like to write it to text file. I would like to do so without using the import/export tool. Is there some code that will accomplish this?

Answer 1

There are a million and one scripts to do what you want. Here's a simple one in VB.NET:



Import Namespace="System.Data.Sql"

Import Namespace="System.Data.SqlClient"

Import Namespace="System.IO"



Sub OutputCSV()

Dim strPath As String = "physical path to where you want the text file saved"

Dim strOut As String



Dim objConn As New SqlConnection( "Your connection string" )

Dim objCmd As New SqlCommand( "name of stored procedure that gets records you want to output to text file")

objCmd.CommandType = CommandType.StoredProcedure



Dim objFile As New TextWriter(strPath)



objCmd.Open()

Dim objReader As SqlDataReader = objCmd.ExecuteReader()

While objReader.Read()

strOut = objReader( "column1" ) + "," + objReader( " column2" ) + "," + objReader( "column3" )

objFile.WriteLine( strOut )

End While



objFile.Close()

objConn.Close()

objCmd.Dispose()

objConn.Dispose()

End Sub

Answer 2

' You can use import/export tool. to generate the VB code:

' The following example is used "employee" table in pubs database

'

'****************************************************************

'Microsoft SQL Server 2000

'Visual Basic file generated for DTS Package

'File Name: C:\\VBScriptTest.vbs

'Package Name: VBScriptTest

'Package Description: DTS package description

'Generated Date: 3/15/2007

'Generated Time: 10:32:19 PM

'****************************************************************



Option Explicit

Public goPackageOld As New DTS.Package

Public goPackage As DTS.Package2

Private Sub Main()

set goPackage = goPackageOld



goPackage.Name = "VBScriptTest"

goPackage.Description = "DTS package description"

goPackage.WriteCompletionStatusToNTEventLog = False

goPackage.FailOnError = False

goPackage.PackagePriorityClass = 2

goPackage.MaxConcurrentSteps = 4

goPackage.LineageOptions = 0

goPackage.UseTransaction = True

goPackage.TransactionIsolationLevel = 4096

goPackage.AutoCommitTransaction = True

goPackage.RepositoryMetadataOptions = 0

goPackage.UseOLEDBServiceComponents = True

goPackage.LogToSQLServer = False

goPackage.LogServerFlags = 0

goPackage.FailPackageOnLogFailure = False

goPackage.ExplicitGlobalVariables = False

goPackage.PackageType = 0





Dim oConnProperty As DTS.OleDBProperty



'---------------------------------------------------------------------------

' create package connection information

'---------------------------------------------------------------------------



Dim oConnection as DTS.Connection2



'------------- a new connection defined below.

'For security purposes, the password is never scripted



Set oConnection = goPackage.Connections.New("SQLOLEDB")



oConnection.ConnectionProperties("Integrated Security") = "SSPI"

oConnection.ConnectionProperties("Persist Security Info") = True

oConnection.ConnectionProperties("Initial Catalog") = "pubs"

oConnection.ConnectionProperties("Data Source") = "(local)"

oConnection.ConnectionProperties("Application Name") = "DTS Import/Export Wizard"



oConnection.Name = "Connection 1"

oConnection.ID = 1

oConnection.Reusable = True

oConnection.ConnectImmediate = False

oConnection.DataSource = "(local)"

oConnection.ConnectionTimeout = 60

oConnection.Catalog = "pubs"

oConnection.UseTrustedConnection = True

oConnection.UseDSL = False



'If you have a password for this connection, please uncomment and add your password below.

'oConnection.Password = ""



goPackage.Connections.Add oConnection

Set oConnection = Nothing



'------------- a new connection defined below.

'For security purposes, the password is never scripted



Set oConnection = goPackage.Connections.New("DTSFlatFile")



oConnection.ConnectionProperties("Data Source") = "c:\test.txt"

oConnection.ConnectionProperties("Mode") = 3

oConnection.ConnectionProperties("Row Delimiter") = vbCrLf

oConnection.ConnectionProperties("File Format") = 1

oConnection.ConnectionProperties("Column Delimiter") = ","

oConnection.ConnectionProperties("File Type") = 1

oConnection.ConnectionProperties("Skip Rows") = 0

oConnection.ConnectionProperties("Text Qualifier") = """"

oConnection.ConnectionProperties("First Row Column Name") = True

oConnection.ConnectionProperties("Column Names") = "au_id,au_lname,au_fname,phone,address,city,state,zip,contract"

oConnection.ConnectionProperties("Number of Column") = 9

oConnection.ConnectionProperties("Text Qualifier Col Mask: 0=no, 1=yes, e.g. 0101") = "111111110"

oConnection.ConnectionProperties("Blob Col Mask: 0=no, 1=yes, e.g. 0101") = "000000000"



oConnection.Name = "Connection 2"

oConnection.ID = 2

oConnection.Reusable = True

oConnection.ConnectImmediate = False

oConnection.DataSource = "c:\test.txt"

oConnection.ConnectionTimeout = 60

oConnection.UseTrustedConnection = False

oConnection.UseDSL = False



'If you have a password for this connection, please uncomment and add your password below.

'oConnection.Password = ""



goPackage.Connections.Add oConnection

Set oConnection = Nothing



'---------------------------------------------------------------------------

' create package steps information

'---------------------------------------------------------------------------



Dim oStep as DTS.Step2

Dim oPrecConstraint as DTS.PrecedenceConstraint



'------------- a new step defined below



Set oStep = goPackage.Steps.New



oStep.Name = "Copy Data from authors to c:\test.txt Step"

oStep.Description = "Copy Data from authors to c:\test.txt Step"

oStep.ExecutionStatus = 1

oStep.TaskName = "Copied data in table c:\test.txt"

oStep.CommitSuccess = False

oStep.RollbackFailure = False

oStep.ScriptLanguage = "VBScript"

oStep.AddGlobalVariables = True

oStep.RelativePriority = 3

oStep.CloseConnection = False

oStep.ExecuteInMainThread = False

oStep.IsPackageDSORowset = False

oStep.JoinTransactionIfPresent = False

oStep.DisableStep = False

oStep.FailPackageOnError = False



goPackage.Steps.Add oStep

Set oStep = Nothing



'---------------------------------------------------------------------------

' create package tasks information

'---------------------------------------------------------------------------



'------------- call Task_Sub1 for task Copied data in table c:\test.txt (Copied data in table c:\test.txt)

Call Task_Sub1( goPackage )



'---------------------------------------------------------------------------

' Save or execute package

'---------------------------------------------------------------------------



'goPackage.SaveToSQLServer "(local)", "sa", ""

goPackage.Execute

goPackage.Uninitialize

'to save a package instead of executing it, comment out the executing package line above and uncomment the saving package line

set goPackage = Nothing



set goPackageOld = Nothing



End Sub





'------------- define Task_Sub1 for task Copied data in table c:\test.txt (Copied data in table c:\test.txt)

Public Sub Task_Sub1(ByVal goPackage As Object)



Dim oTask As DTS.Task

Dim oLookup As DTS.Lookup



Dim oCustomTask1 As DTS.DataPumpTask2

Set oTask = goPackage.Tasks.New("DTSDataPumpTask")

Set oCustomTask1 = oTask.CustomTask



oCustomTask1.Name = "Copied data in table c:\test.txt"

oCustomTask1.Description = "Copied data in table c:\test.txt"

oCustomTask1.SourceConnectionID = 1

oCustomTask1.SourceSQLStatement = "select [au_id],[au_lname],[au_fname],[phone],[address],[city],[state],[zip],[contract] from [pubs].[dbo].[authors]"

oCustomTask1.DestinationConnectionID = 2

oCustomTask1.DestinationObjectName = "c:\test.txt"

oCustomTask1.ProgressRowCount = 1000

oCustomTask1.MaximumErrorCount = 0

oCustomTask1.FetchBufferSize = 1

oCustomTask1.UseFastLoad = True

oCustomTask1.InsertCommitSize = 0

oCustomTask1.ExceptionFileColumnDelimiter = "|"

oCustomTask1.ExceptionFileRowDelimiter = vbCrLf

oCustomTask1.AllowIdentityInserts = False

oCustomTask1.FirstRow = 0

oCustomTask1.LastRow = 0

oCustomTask1.FastLoadOptions = 2

oCustomTask1.ExceptionFileOptions = 1

oCustomTask1.DataPumpOptions = 0



Call oCustomTask1_Trans_Sub1( oCustomTask1 )





goPackage.Tasks.Add oTask

Set oCustomTask1 = Nothing

Set oTask = Nothing



End Sub



Public Sub oCustomTask1_Trans_Sub1(ByVal oCustomTask1 As Object)



Dim oTransformation As DTS.Transformation2

Dim oTransProps as DTS.Properties

Dim oColumn As DTS.Column

Set oTransformation = oCustomTask1.Transformations.New("DTS.DataPumpTransformCopy")

oTransformation.Name = "DirectCopyXform"

oTransformation.TransformFlags = 63

oTransformation.ForceSourceBlobsBuffered = 0

oTransformation.ForceBlobsInMemory = False

oTransformation.InMemoryBlobSize = 1048576

oTransformation.TransformPhases = 4



Set oColumn = oTransformation.SourceColumns.New("au_id" , 1)

oColumn.Name = "au_id"

oColumn.Ordinal = 1

oColumn.Flags = 8

oColumn.Size = 11

oColumn.DataType = 129

oColumn.Precision = 0

oColumn.NumericScale = 0

oColumn.Nullable = False



oTransformation.SourceColumns.Add oColumn

Set oColumn = Nothing



Set oColumn = oTransformation.SourceColumns.New("au_lname" , 2)

oColumn.Name = "au_lname"

oColumn.Ordinal = 2

oColumn.Flags = 8

oColumn.Size = 40

oColumn.DataType = 129

oColumn.Precision = 0

oColumn.NumericScale = 0

oColumn.Nullable = False



oTransformation.SourceColumns.Add oColumn

Set oColumn = Nothing



Set oColumn = oTransformation.SourceColumns.New("au_fname" , 3)

oColumn.Name = "au_fname"

oColumn.Ordinal = 3

oColumn.Flags = 8

oColumn.Size = 20

oColumn.DataType = 129

oColumn.Precision = 0

oColumn.NumericScale = 0

oColumn.Nullable = False



oTransformation.SourceColumns.Add oColumn

Set oColumn = Nothing



Set oColumn = oTransformation.SourceColumns.New("phone" , 4)

oColumn.Name = "phone"

oColumn.Ordinal = 4

oColumn.Flags = 24

oColumn.Size = 12

oColumn.DataType = 129

oColumn.Precision = 0

oColumn.NumericScale = 0

oColumn.Nullable = False



oTransformation.SourceColumns.Add oColumn

Set oColumn = Nothing



Set oColumn = oTransformation.SourceColumns.New("address" , 5)

oColumn.Name = "address"

oColumn.Ordinal = 5

oColumn.Flags = 104

oColumn.Size = 40

oColumn.DataType = 129

oColumn.Precision = 0

oColumn.NumericScale = 0

oColumn.Nullable = True



oTransformation.SourceColumns.Add oColumn

Set oColumn = Nothing



Set oColumn = oTransformation.SourceColumns.New("city" , 6)

oColumn.Name = "city"

oColumn.Ordinal = 6

oColumn.Flags = 104

oColumn.Size = 20

oColumn.DataType = 129

oColumn.Precision = 0

oColumn.NumericScale = 0

oColumn.Nullable = True



oTransformation.SourceColumns.Add oColumn

Set oColumn = Nothing



Set oColumn = oTransformation.SourceColumns.New("state" , 7)

oColumn.Name = "state"

oColumn.Ordinal = 7

oColumn.Flags = 120

oColumn.Size = 2

oColumn.DataType = 129

oColumn.Precision = 0

oColumn.NumericScale = 0

oColumn.Nullable = True



oTransformation.SourceColumns.Add oColumn

Set oColumn = Nothing



Set oColumn = oTransformation.SourceColumns.New("zip" , 8)

oColumn.Name = "zip"

oColumn.Ordinal = 8

oColumn.Flags = 120

oColumn.Size = 5

oColumn.DataType = 129

oColumn.Precision = 0

oColumn.NumericScale = 0

oColumn.Nullable = True



oTransformation.SourceColumns.Add oColumn

Set oColumn = Nothing



Set oColumn = oTransformation.SourceColumns.New("contract" , 9)

oColumn.Name = "contract"

oColumn.Ordinal = 9

oColumn.Flags = 24

oColumn.Size = 0

oColumn.DataType = 11

oColumn.Precision = 0

oColumn.NumericScale = 0

oColumn.Nullable = False



oTransformation.SourceColumns.Add oColumn

Set oColumn = Nothing



Set oColumn = oTransformation.DestinationColumns.New("au_id" , 1)

oColumn.Name = "au_id"

oColumn.Ordinal = 1

oColumn.Flags = 8

oColumn.Size = 11

oColumn.DataType = 129

oColumn.Precision = 0

oColumn.NumericScale = 0

oColumn.Nullable = False



oTransformation.DestinationColumns.Add oColumn

Set oColumn = Nothing



Set oColumn = oTransformation.DestinationColumns.New("au_lname" , 2)

oColumn.Name = "au_lname"

oColumn.Ordinal = 2

oColumn.Flags = 8

oColumn.Size = 40

oColumn.DataType = 129

oColumn.Precision = 0

oColumn.NumericScale = 0

oColumn.Nullable = False



oTransformation.DestinationColumns.Add oColumn

Set oColumn = Nothing



Set oColumn = oTransformation.DestinationColumns.New("au_fname" , 3)

oColumn.Name = "au_fname"

oColumn.Ordinal = 3

oColumn.Flags = 8

oColumn.Size = 20

oColumn.DataType = 129

oColumn.Precision = 0

oColumn.NumericScale = 0

oColumn.Nullable = False



oTransformation.DestinationColumns.Add oColumn

Set oColumn = Nothing



Set oColumn = oTransformation.DestinationColumns.New("phone" , 4)

oColumn.Name = "phone"

oColumn.Ordinal = 4

oColumn.Flags = 24

oColumn.Size = 12

oColumn.DataType = 129

oColumn.Precision = 0

oColumn.NumericScale = 0

oColumn.Nullable = False



oTransformation.DestinationColumns.Add oColumn

Set oColumn = Nothing



Set oColumn = oTransformation.DestinationColumns.New("address" , 5)

oColumn.Name = "address"

oColumn.Ordinal = 5

oColumn.Flags = 104

oColumn.Size = 40

oColumn.DataType = 129

oColumn.Precision = 0

oColumn.NumericScale = 0

oColumn.Nullable = True



oTransformation.DestinationColumns.Add oColumn

Set oColumn = Nothing



Set oColumn = oTransformation.DestinationColumns.New("city" , 6)

oColumn.Name = "city"

oColumn.Ordinal = 6

oColumn.Flags = 104

oColumn.Size = 20

oColumn.DataType = 129

oColumn.Precision = 0

oColumn.NumericScale = 0

oColumn.Nullable = True



oTransformation.DestinationColumns.Add oColumn

Set oColumn = Nothing



Set oColumn = oTransformation.DestinationColumns.New("state" , 7)

oColumn.Name = "state"

oColumn.Ordinal = 7

oColumn.Flags = 120

oColumn.Size = 2

oColumn.DataType = 129

oColumn.Precision = 0

oColumn.NumericScale = 0

oColumn.Nullable = True



oTransformation.DestinationColumns.Add oColumn

Set oColumn = Nothing



Set oColumn = oTransformation.DestinationColumns.New("zip" , 8)

oColumn.Name = "zip"

oColumn.Ordinal = 8

oColumn.Flags = 120

oColumn.Size = 5

oColumn.DataType = 129

oColumn.Precision = 0

oColumn.NumericScale = 0

oColumn.Nullable = True



oTransformation.DestinationColumns.Add oColumn

Set oColumn = Nothing



Set oColumn = oTransformation.DestinationColumns.New("contract" , 9)

oColumn.Name = "contract"

oColumn.Ordinal = 9

oColumn.Flags = 24

oColumn.Size = 5

oColumn.DataType = 129

oColumn.Precision = 0

oColumn.NumericScale = 0

oColumn.Nullable = False



oTransformation.DestinationColumns.Add oColumn

Set oColumn = Nothing



Set oTransProps = oTransformation.TransformServerProperties





Set oTransProps = Nothing



oCustomTask1.Transformations.Add oTransformation

Set oTransformation = Nothing



End Sub

Answer 3

Why do you want to do this without using the import/export tool? Is it not working?? If so, and you are using SQL 2005, upgrade to SP1 to fix it.



Else I'd recommend using the tool to create a DTS package (save it instead of running it). Then you can call dtsrun from a command prompt, stored procedure or query. I recently wrote a stored procedure using DTS packages and passing variables to it such as file name and server name...works great!