ASCII 28 and 29 (Hex 0x28 and 0x29 ) used as Special Delimiters




--The ASCII and Unicode character sets were designed to solve this problem by the provision of non-printing characters that can be used as delimiters. 
--These are the range from ASCII 28 to 31.
--0x28
DECLARE @cmd0x28 NVARCHAR(4000) 
set @cmd0x28 = 'BCP  [test1].[dbo].[sales] OUT  "C:\temp\sales9.txt"  -c -t 0x28  -T  -S'+ @@servername
exec master..xp_cmdshell @cmd0x28
set @cmd0x28 = 'BCP [test1].[dbo].sales2  IN  "C:\temp\sales9.txt" -c -t 0x28  -T -S'+ @@servername
exec master..xp_cmdshell @cmd0x28

select * from  [test1].[dbo].sales2 
truncate table [test1].[dbo].sales2 


--0x29
DECLARE @cmd0x29 NVARCHAR(4000) 
set @cmd0x29 = 'BCP  [test1].[dbo].[sales] OUT  "C:\temp\sales9.txt"  -c -t 0x29  -T  -S'+ @@servername
exec master..xp_cmdshell @cmd0x29
set @cmd0x29 = 'BCP [test1].[dbo].sales2  IN  "C:\temp\sales9.txt" -c -t 0x29  -T -S'+ @@servername
exec master..xp_cmdshell @cmd0x29


select * from  [test1].[dbo].sales2 
truncate table [test1].[dbo].sales2 


--Not working for 30 and 31
--0x30
--0x31
 http://en.wikipedia.org/wiki/Delimiter


 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s