BCP QUERYOUT HELP - ANY BCP GURUS? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

BCP QUERYOUT HELP - ANY BCP GURUS?

Author

Topic

toddmcdaniel
Starting Member

5 Posts

Posted - 2009-05-26 : 10:07:53

-- I need help creating an acceptable select statement for BCP that will allow
-- me to produce a Comma-delimited, quoted-identifier text file WITHOUT using
-- a format file. I've been told it can be done, but I am pulling my hair out
-- over here!!! See code below... YOU SHOULD BE ABLE TO COPY AND PASTE THIS
-- ENTIRE MESSAGE TO A QUERY WINDOW FOR RUNNING/TESTING.

-- First set up the temporary test table and add a couple test rows.
USE tempdb
go
IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'TestTable')
DROP TABLE TestTable
go

CREATE TABLE TestTable
(
TestCol1 VARCHAR(20)
,TestCol2 VARCHAR(20)
,TestCol3 NUMERIC(12,2)
)
go

INSERT TestTable (TestCol1, TestCol2, TestCol3) VALUES ('123', 'abc,def', 100)
INSERT TestTable (TestCol1, TestCol2, TestCol3) VALUES ('654' , 'zyx,wvu', 200)
go

-- the following select statement retrieves the data EXACTLY the way I want
-- it to appear in the bcp out file (quoted-identifier and comma-delimited).
SELECT '"' + TestCol1 + '", "' + TestCol2 + '", "' +
CONVERT(VARCHAR(30), TestCol3) + '"' FROM TestTable

-- Now I want to use this same select statement in a BCP QUERYOUT command,
-- but it seems BCP gets confused.
--**************************************************************************
--* IS THERE A WAY TO DO THIS - CREATE A COMMA-SEPARATED, QUOTED-IDENTFIER *
--* TEXT FILE USING THE BCP COMMAND **WITHOUT USING A FORMAT FILE **?????? *
--**************************************************************************
DECLARE
@sql VARCHAR(400),
@cmd VARCHAR(400)

SET @sql = 'SELECT '
SET @sql = @sql + '''"'' + TestCol1 + ''", '
SET @sql = @sql + '"'' + TestCol2 + ''"'''
SET @sql = @sql + ' FROM tempdb..TestTable'
print @sql
EXEC (@sql) -- this execute command of the select statement works just fine!

-- But the following BCP command does NOT work!!!!!
SET @cmd = 'BCP "' + @sql + '" QUERYOUT "C:\test.csv" -c -t, -q -S'
SET @cmd = @cmd + CONVERT(VARCHAR(255), SERVERPROPERTY('servername')) + ' -T'
print @cmd
EXEC master..xp_Cmdshell @cmd

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-26 : 10:15:23

Please post the PRINT result.

E 12°55'05.63"
N 56°04'39.26"

nr
SQLTeam MVY

12543 Posts

Posted - 2009-05-26 : 10:23:17

see
http://www.simple-talk.com/sql/database-administration/creating-csv-files-using-bcp-and-stored-procedures/

==========================================
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.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-26 : 10:28:34

it's due to the double quote " in your query affecting the BCP. In BCP command line the query need to be enclosed in the double quote.
try . .


SET @sql = 'SELECT '
SET @sql = @sql + 'char(34) + TestCol1 + char(34), '
SET @sql = @sql + 'char(34) + TestCol2 + char(34)'
SET @sql = @sql + ' FROM tempdb..TestTable'

KH
[spoiler]Time is always against us[/spoiler]

toddmcdaniel
Starting Member

5 Posts

Posted - 2009-05-26 : 13:45:26

quote:
Originally posted by khtan

it's due to the double quote " in your query affecting the BCP. In BCP command line the query need to be enclosed in the double quote.
try . .

SET @sql = 'SELECT '
SET @sql = @sql + 'char(34) + TestCol1 + char(34), '
SET @sql = @sql + 'char(34) + TestCol2 + char(34)'
SET @sql = @sql + ' FROM tempdb..TestTable'

KH
[spoiler]Time is always against us[/spoiler]

khtan,

That did it! You are the GURU! ;)

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources