BCP output without comma delimiter

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

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

BCP output without comma delimiter

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-12-16 : 07:59:43

Clyde writes "Hi There

I used BCP to output a table to a text file using this code in a stored procedure:

DECLARE @FileName varchar(50),
@bcpCommand varchar(2000)

SET @FileName = REPLACE('c:\info\cool\Detail','/','-')

SET @bcpCommand = 'bcp "SELECT * FROM Info..format_Detail " queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -U sa -P cey542 -c -t'

EXEC master..xp_cmdshell @bcpCommand

The problem was that the file was created on the SQL server directory. I got the code below(VB) from the net but the problem is that it exports it with a comma delimiter. In the above code the -t eliminates the delimiter. How can edit the code below have no delimiter?

Thanks
Clyde

Public Function BCP(ByVal Server As String, ByVal UserName As _
String, ByVal Password As String, ByVal Database As String, _
ByVal Table As String, ByVal FileName As String, _
Optional Import As Boolean) As Boolean

'**************************************************************
'PURPOSE: Do a SQL Server BulkCopy
'ASSUMPTIONS: You understand what a bulk copy is; otherwise
' consult SQLServer books online
'PARAMETERS: Server: ServerName
' UserName: UserName for Server
' Password: Password for Server
' Database: Name of Database
' Table: Table Name or SQL Statement for BCP
' FileName: File Name to Import from or Export To
' Import: If set to true, works as an import
' ("in") Otherwise, defaults to export
' ("out")
'RETURNS: True if successful, false otherwise
'EXAMPLE:
'BCP "MySQLServer", "username", "pwd", "MyDataBase", "MyTable", _
"C:\MyTable.out"
'
'Bulk Copies all rows of MyTable in MyDatabase on MySQLServer
'to a file named C:\MyTable.out
'REQUIRES: A reference to Microsoft SQLDMO Object Library
'LIMITATIONS: -- Assumes SQL Server Authentication as opposed
' to NT integrated authentication.
'
' -- Only allows full tables to be
' imported/exported i.e., you cannot specify
' where criteria
'
' -- Uses mostly default options of bcp.
' If you want to set more options, the BulkCopy
' object permits this. Refer to documentation or
' object browser
'***********************************************************

Dim objServer As New SQLDMO.SQLServer
Dim objBCP As New SQLDMO.BulkCopy
Dim objDB As SQLDMO.Database

'if file doesn't exist and it's an import,
'don't waste time
If Import = True And Dir(FileName) = "" Then Exit Function

On Error GoTo ErrorHandler
objServer.Connect Server, UserName, Password
objServer.EnableBcp = True

Set objDB = objServer.Databases(Database)
With objBCP
.DataFilePath = FileName

'Below speeds things up
'but does not log the bulk copy operation
'comment out if this is not what you
'desire
.UseBulkCopyOption = True
End With

If Import Then
objDB.Tables(Table).ImportData objBCP
Else
objDB.Tables(Table).ExportData objBCP
End If

BCP = True

ErrorHandler:
Debug.Print Err.Number & " " & Err.Description
Set objBCP = Nothing
Set objServer = Nothing
End Function"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2002-12-16 : 13:37:56

Why don't you just stick with the code that you have in the stored procedure? All that you will have to do to get the file created on another machine is to use UNC.

So modify SET @FileName = REPLACE('c:\info\cool\Detail','/','-') to SET @FileName = REPLACE('\\yourmachine\c$\info\cool\Detail','/','-')

Of course this assumes that the stored procedure can access c$ on yourmachine. But you get the idea. You don't have to use a local drive on the server to get this to work. You could also map a drive on the server to another machine and place the file there, but mapping drives isn't recommended.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources