Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 7,2000 » Administration » BCP
Add to briefcase

BCP Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
river1
Posted Tuesday, August 11, 2009 8:40 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:56 AM
Points: 679, Visits: 953
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
Saravanan_tvr
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: Thursday, April 25, 2013 7:04 AM
Points: 269, Visits: 1,110
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
er.rajesh89
Posted Wednesday, May 02, 2012 9:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 28, 2012 5:37 AM
Points: 13, Visits: 14
hi if u can send code for import using " bcp in "

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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:11 PM
Points: 11,605, Visits: 27,645
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
er.rajesh89
Posted Thursday, May 03, 2012 5:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 28, 2012 5:37 AM
Points: 13, Visits: 14
Thanks Lowell ,

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


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 09, 2013 10:43 AM
Points: 170, Visits: 530
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
Lowell
Posted Friday, March 01, 2013 8:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:11 PM
Points: 11,605, Visits: 27,645
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
Sigerson
Posted Friday, March 01, 2013 9:00 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 09, 2013 10:43 AM
Points: 170, Visits: 530
@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
Lowell
Posted Friday, March 01, 2013 9:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:11 PM
Points: 11,605, Visits: 27,645
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
				
		
							
		
				

								

									        
									
								

							

							
		
				
						

							
		
		
			
			
	    		      	
		

							
		
				 
				
				
				

								

									
Jeff Moden

									
								

							

							
			
				
							
			
				

								

									  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: Yesterday @ 8:21 PM
				

				Points: 32,893, 
				Visits: 26,765
						
								

							

							
					
				
							
					
				

								

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

For better, quicker answers on T-SQL questions, click on the following... 
 http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following... 
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

				 
					
				
						
								

							

							
					
				
						

						
		

							
		
				Post #1425852
				
		
							
		
				

								

									        
									
								

							

							
		
				
						

							
		
		
			
		
			
			

							
		


					

					

						

		

		
« Prev Topic | Next Topic » 
		



					

				

						
			

		

				
	

	

		
		
	












	

		
      
      
      
      Add to briefcase
    
		
      
    
	



	








	

		

			

				

					

						Permissions
						Expand / Collapse
					

				

						
			

			
		

				
	

	

		
		
	











 






    
 





					
			 

		

	

	

		

			 
			
				

					Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.