Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

BCP Expand / Collapse
Author
Message
Posted Tuesday, August 11, 2009 8:40 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, April 07, 2014 10:25 AM
Points: 715, Visits: 1,018
Hi,

i need to export data from a view to a PSV file.
I'm traing to do this t-sql:

exec master..xp_cmdshell 'bcp "select * from [database]..test1" queryout "c:\Import.psv" -T -S "server\instancename"'

I receive an error:

Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot resolve collation conflict for replace operation.


I want to export this data to a unicode PSV so that people from Oracle can import it.
Can someone help please?


thank you
Post #768668
Posted Monday, February 15, 2010 12:46 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, March 30, 2014 9:34 PM
Points: 277, Visits: 1,179
Use this format...

declare @sql varchar(8000)
declare @path varchar(8000)
set @path ='d:\import.psv'
select @sql = 'bcp "set fmtonly off select * from db_name.DBO.table_name" queryout '+@path+' -c -t^ -T -S' + @@servername
exec master..xp_cmdshell @sql

this will give u the Psv format with tab delimiter..




Many Thanks!
S.saravanan
“I am a slow walker, but I never walk backwards-
Abraham Lincoln”
Post #865397
Posted Wednesday, May 02, 2012 9:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 10:19 PM
Points: 13, Visits: 34
hi if u can send code for import using " bcp in "

By Rajesh
Post #1293979
Posted Wednesday, May 02, 2012 9:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:00 AM
Points: 12,744, Visits: 31,072
er.rajesh89 (5/2/2012)
hi if u can send code for import using " bcp in "

By Rajesh


Booksonline and google are great resources, but if you need an example, here's both an out and in in:
--using a super special 4 char row delimiter to be sure it doesn't exist in the data
--flags explanation:
-- -c = charcater data
-- -t"[||]" = field terminator
-- -r"[~~]" = row terminator
-- -T' = Trusted connection
--out
EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT EMAILTEMPLATESID, EMAILBODY FROM Sandbox.dbo.EMAILTEMPLATES ORDER BY EMAILTEMPLATESID" queryout c:\Data\bcpExample.txt -c -t"[||]" -r"[~~]" -T'
--in
EXECUTE master.dbo.xp_cmdshell 'bcp Sandbox.dbo.EMAILTEMPLATES in c:\Data\bcpExample.txt -c -t"[||]" -r"[~~]" -T'
--in via bulk insert
BULK INSERT EMAILTEMPLATES FROM 'c:\Data\bcpExample.txt'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = '[||]',
ROWTERMINATOR = '[~~]',
FIRSTROW = 1
)



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1293987
Posted Thursday, May 03, 2012 5:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 10:19 PM
Points: 13, Visits: 34
Thanks Lowell ,

this code is very used ....
Post #1294487
Posted Friday, March 01, 2013 8:14 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 8:17 AM
Points: 243, Visits: 787
This is a related question, hoping someone on this thread can answer it.

I've had a working bcp process for months that creates a CSV file on a network drive. Here's the command:


'bcp "exec [dbname].[dbo].ExtractData" queryout ' + @destFolder + @fileName + ' -t \, -T -c'

When @destFolder = "\\[server_name]\UploadFiles" it works

If I change @destFolder to: "\\[server_name]\9 Auto Generated Upload Files\" with spaces in the folder name, it doesn't work.

I can work around this, but does anyone know of a way to force bcp to accept spaces in the destination folder?

Thanks in advance,


Sigerson

"No pressure, no diamonds." - Thomas Carlyle
Post #1425535
Posted Friday, March 01, 2013 8:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:00 AM
Points: 12,744, Visits: 31,072
Sigerson (3/1/2013)
This is a related question, hoping someone on this thread can answer it.

I've had a working bcp process for months that creates a CSV file on a network drive. Here's the command:


'bcp "exec [dbname].[dbo].ExtractData" queryout ' + @destFolder + @fileName + ' -t \, -T -c'

When @destFolder = "\\[server_name]\UploadFiles" it works

If I change @destFolder to: "\\[server_name]\9 Auto Generated Upload Files\" with spaces in the folder name, it doesn't work.

I can work around this, but does anyone know of a way to force bcp to accept spaces in the destination folder?

Thanks in advance,


it's the usual command line thing: files or paths that contain spaces must be wrapped with double quotes

your code modified:note the dbl quotes that make up the path+file, and also for the t delimiter
'bcp "exec [dbname].[dbo].ExtractData" queryout "' + @destFolder + @fileName + '" -t "\", -T -c'



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1425548
Posted Friday, March 01, 2013 9:00 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 8:17 AM
Points: 243, Visits: 787
@Lowell,

Thanks for the quick answer. Yes, a rookie mistake. (pause for hanging head in shame)

Is there a word for having to learn so many fine points that one begins to forget the basics?

Thanks again,


Sigerson

"No pressure, no diamonds." - Thomas Carlyle
Post #1425564
Posted Friday, March 01, 2013 9:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:00 AM
Points: 12,744, Visits: 31,072
Sigerson (3/1/2013)
@Lowell,

Is there a word for having to learn so many fine points that one begins to forget the basics?


Oh yea,the only guys that remember it all, i think I hear it described as "no personal life" the most



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1425569
Posted Saturday, March 02, 2013 7:04 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:25 AM
Points: 35,959, Visits: 30,253
Lowell (3/1/2013)
Sigerson (3/1/2013)
@Lowell,

Is there a word for having to learn so many fine points that one begins to forget the basics?


Oh yea,the only guys that remember it all, i think I hear it described as "no personal life" the most



Heh... we can coin a new phrase, Lowell. "NPL" for "No Personal Life". I won't hazard the pronunciation of the acronym, though.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1425852
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse