Execute BCP xp_cmdshell from VB

Question

Post reply

Execute BCP xp_cmdshell from VB

weldy

SSC-Addicted

Points: 410
More actions
November 3, 2002 at 9:33 pm

#79040

Hello everyone,
I have the following procedure to export to a csv file:
---------------------------------------------
CREATE PROCEDURE XExportBCP AS
exec master..xp_cmdshell 'bcp "select rtrim(refnbr), Today=convert(varchar, GETDATE(), 112), rtrim(curyid), rtrim(curyorigdocamt), rtrim(cpnyname), rtrim(bankacct), rtrim(vendbank), rtrim(vendacct), rtrim(name), snotetext, rtrim(citiacct) from acsvapp..XExportTable" queryout "c:\testing123.txt" -t, -c'
---------------------------------------------
**This works when I simply run it directly through the query analyzer by typing Exec XExportBCP.
How do I execute this from VB? I have tried the following (normally this would work):
---------------------------------------------
Public Sub WriteCSV()
Dim sqlstr As String
sqlstr = "XExportBCP"
Call sql(csr_ExecBCP, sqlstr)
End Sub
---------------------------------------------
Any Suggestions? Thanks!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Klaas-Jan SSCrazy Points: 2297 More actions · Answer 1

Hi,

When using ado you could do something like this:



dim con as adodb.connection

dim strSql as string

strSql = "XExportBCP" 

con.execute(strSql)

set con = nothing

strSql=""





Make sure that the vb application has sufficients rights in sql server to execute xp_cmdshell

Edited by - klaas-jan on 11/04/2002 02:53:16 AM

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 2

What doe syour sql function do? Are you using ado, dmo ?

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

Antares686 SSC Guru Points: 125444 More actions · Answer 3

Also, to save some round trips to the SQL server you may want to add using the ADODB.Command object so you can specify Stored Procedure with not return.

' Our SQL SOnnection object, defined globally.

Public SQLConn As New ADODB.Connection

----------This goes in your function or sub

' Verify our SQL connection, if closed then open else continue.

If SQLConn.State = adStateClosed Then SQLConn.Open "Provider=SQLOLEDB.1;Initial Catalog=DatabaseName;APP=ApplicationName;Data Source=ServerNameOrIP", "useraccount", "password"

' Create out command object

Dim SQLCmd As New ADODB.Command

With SQLCmd

' Set our command objects base information.

.ActiveConnection = SQLConn

.CommandText = "XExportBCP"

.CommandType = adCmdStoredProc

' Execute our Query but I do not expect a return value.

.Execute , , adExecuteNoRecords

End With

-------------Then when closing your app or thru with SQL and not planning reuse in a relative short time

SQLConn.Close()

Set SQLConn = nothing

------------

Do not close until you do not need to reconnect to take maximum advantage of the connection.

And do make sure the account you connect has sufficient rights to run the Proc.

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 4

Using something similar.

Created a user for the database and supply username/password in bcp command string.

weldy SSC-Addicted Points: 410 More actions · Answer 5

Simonsabin, I am using neither ado or dmo. The procedure is stored within SQL Server and the purpose of the procedure is to produce a text file named testing 123 (comma delimited)

I'm currently using VB Tools for Solomon, so in this case, do I still need a cursor? or could I use ADO? I tried using it but it keeps on asking me to define a UDT. Thanks again.

Antares686 SSC Guru Points: 125444 More actions · Answer 6

Sorry, I have never used Solomon. Would have to see their documentation to possibly offer any further help.