Run this code in sp - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Run this code in sp

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-12-07 : 05:14:41

Is it possible to run this code in a stored procedure ? if so how ? TIA

Dim sw As StreamWriter = New StreamWriter("Transactions.txt")
Dim line As String

Try
cn.Open()
cnCmd.CommandText = "Select description, document, part_num, cur_qty, cond_code, generic1, generic2 from tblinventory"
cnCmd.Connection = cn
drDB = cnCmd.ExecuteReader

Dim description As String, document As String
Dim part_num As String, cur_qty As String
Dim cond_code As String, generic1 As String
Dim generic2 As String

While drDB.Read
description = drDB.Item("description")
document = drDB.Item("document")
part_num = drDB.Item("part_num")
cur_qty = drDB.Item("cur_qty")
cond_code = drDB.Item("cond_code")
generic1 = drDB.Item("generic1")
generic2 = drDB.Item("generic2")

line = description.PadRight(30) & document.PadRight(25) & "LINE," _
& part_num.PadLeft(10, "0") & ",RIC,UI," & cur_qty.padleft(5, "0") _
& "," & cond_code.PadRight(3) & "," & document.PadRight(25) _
& "," & generic1.PadRight(20) & "," & generic2.PadRight(20)

sw.WriteLine(line) ' change padding parameter accordingly!
End While
drDB.Close()

cn.Close()

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-12-07 : 05:16:59

No as it is not T-SQL.

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-12-07 : 06:03:09

What I really meant was do the same thing in an sp - end up with the same results.

cas_o
Posting Yak Master

154 Posts

Posted - 2007-12-07 : 09:55:44

in Query analyser or SQL Management Studio type the following TSQL and press f5 to create a stored procedure (make sure you have the right Database selected, don't create it in master! :)):


CREATE PROCEDURE usp_GetInventory 
AS
Select description, document, part_num, cur_qty, cond_code, generic1, generic2 from tblinventory
GO
[code]
And ammend your code thus (lines 2 and 3):
[code]
 Try
cn.Open()
cnCmd.CommandType = CommandType.StoredProcedure
cnCmd.CommandText = "usp_GetInventory "
cnCmd.Connection = cn
drDB = cnCmd.ExecuteReader

Dim description As String, document As String
Dim part_num As String, cur_qty As String
Dim cond_code As String, generic1 As String
Dim generic2 As String

While drDB.Read
description = drDB.Item("description")
document = drDB.Item("document")
part_num = drDB.Item("part_num")
cur_qty = drDB.Item("cur_qty")
cond_code = drDB.Item("cond_code")
generic1 = drDB.Item("generic1")
generic2 = drDB.Item("generic2")

line = description.PadRight(30) & document.PadRight(25) & "LINE," _
& part_num.PadLeft(10, "0") & ",RIC,UI," & cur_qty.padleft(5, "0") _
& "," & cond_code.PadRight(3) & "," & document.PadRight(25) _
& "," & generic1.PadRight(20) & "," & generic2.PadRight(20)

sw.WriteLine(line) ' change padding parameter accordingly!
End While
drDB.Close()

cn.Close()

;-]... Quack Waddle

cas_o
Posting Yak Master

154 Posts

Posted - 2007-12-07 : 10:20:18

or did you want to output the table to a file completely from within Sql Server ?

;-]... Quack Waddle

cas_o
Posting Yak Master

154 Posts

Posted - 2007-12-07 : 10:22:48

Use the bcp utility from either the command line or from within a stored proc using exec xp_cmdshell 'bcp command ...'.

;-]... Quack Waddle

cas_o
Posting Yak Master

154 Posts

Posted - 2007-12-07 : 10:40:39

You need to replace dbname with the name of your database in which tblinventory exists.


CREATE PROCEDURE usp_exportInventory AS

exec master.dbo.xp_cmdshell 'bcp "select description, document, part_num, cur_qty, cond_code, generic1, generic2 from dbname.dbo.tblinventory" queryout "C:\tblinventory.csv" -S(local) -T -c'
GO

;-]... Quack Waddle

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-12-07 : 11:08:46

Just got back to this post - I'll have to try it Monday now - thanks

cas_o
Posting Yak Master

154 Posts

Posted - 2007-12-07 : 11:15:28

It's only quarter past four !! :)

;-]... Quack Waddle

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-12-10 : 09:10:32