• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 215
  • Last Modified:

Problems with single quote character iwhen attempting to export data

Hi,

I am attempting to use the following query, which works fine when run in sql server management studio, to export data:

SELECT
            '1',
            isnull(left(DISTRICT,1),' '),
            isnull(left(CTV,2),'  '),
            isnull(left(CTV2,5),'     ')          
FROM [CensusData].[dbo].[MainQuestionnaire$]

The export command follows:

EXEC xp_cmdshell 'bcp "SELECT
            '1',
            isnull(left(DISTRICT,1),' '),
            isnull(left(CTV,2),'  '),
            isnull(left(CTV2,5),'     ')            
FROM [CensusData].[dbo].[MainQuestionnaire$]" queryout "C:\bcptest.txt" -T -c -t -S "MYSERVER"'

I know that the problem is with the single quote characters but don't know how to fix it.

Please help.
0
sib_user
Asked:
sib_user
  • 5
  • 3
  • 2
1 Solution
 
AmmarRCommented:
Dear you can use the replace command

check the code below

replace(column name,'''','')
EXEC xp_cmdshell 'bcp "SELECT 
            '1', 
            isnull(left(replace(DISTRICT,'''',''),1),' '),
            isnull(left(replace(CTV,'''',''),2),'  '),,
            isnull(left(replace(CTV2,'''','')),'     ')            
FROM [CensusData].[dbo].[MainQuestionnaire$]" queryout "C:\bcptest.txt" -T -c -t -S "MYSERVER"' 

Open in new window

0
 
Knut HunstadCommented:
From the documentation:

---
command_string cannot contain more than one set of double quotation marks. A single pair of quotation marks is necessary if any spaces are present in the file paths or program names referenced by command_string. If you have trouble with embedded spaces, consider using FAT 8.3 file names as a workaround
---

So the problem seems to be your double quotation marks, not the single ones!

Try this:

EXEC xp_cmdshell 'bcp "SELECT
            '1',
            isnull(left(DISTRICT,1),' '),
            isnull(left(CTV,2),'  '),
            isnull(left(CTV2,5),'     ')            
FROM [CensusData].[dbo].[MainQuestionnaire$]" queryout C:\bcptest.txt -T -c -t -S MYSERVER'


Please note this suggestion is just a guess and untested :-) But it should give you a clue where to look...

Good luck!
0
 
sib_userAuthor Commented:
AmmarR:

I get this error when I attempt your solution:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '),
            isnull(left(replace(CTV,'','),2),'.

Khun:

Your give the error:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '1'.
Which seems to refute your assertion that the double quotes is the problem.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
AmmarRCommented:
there were 2 commas at the end of the line

try this now
EXEC xp_cmdshell 'bcp "SELECT 
            '1', 
            isnull(left(replace(DISTRICT,'''',''),1),' '),
            isnull(left(replace(CTV,'''',''),2),'  '),
            isnull(left(replace(CTV2,'''','')),'     ')            
FROM [CensusData].[dbo].[MainQuestionnaire$]" queryout "C:\bcptest.txt" -T -c -t -S "MYSERVER"' 

Open in new window

0
 
Knut HunstadCommented:
I would start with going to a cmd-shell and try typing (or rather put it into a bat-file so you don't have to retype if it doesn't work):

bcp "SELECT
            '1',
            isnull(left(DISTRICT,1),' '),
            isnull(left(CTV,2),'  '),
            isnull(left(CTV2,5),'     ')            
FROM [CensusData].[dbo].[MainQuestionnaire$]" queryout "C:\bcptest.txt" -T -c -t -S "MYSERVER"

to check that works. Then:

bcp "SELECT
            '1',
            isnull(left(DISTRICT,1),' '),
            isnull(left(CTV,2),'  '),
            isnull(left(CTV2,5),'     ')            
FROM [CensusData].[dbo].[MainQuestionnaire$]" queryout C:\bcptest.txt -T -c -t -S MYSERVER

Should help you detect what the problem really is...
0
 
sib_userAuthor Commented:
AmmarR;
Same error.

khun:
Have a look at the attached image.
errors.bmp
0
 
Knut HunstadCommented:
You need to have it all on on line. I should have mentioned that, of course, sorry. Right now you sent one command:

bcp "SELECT

which triggered the explanation of the bcp command. Then you sent another command:

'1',

which was sort of difficult to understand :-) Etc. etc.

Put this into a text file you call TestSQL.bat:

bcp "SELECT '1', isnull(left(DISTRICT,1),' '), isnull(left(CTV,2),'  '), isnull(left(CTV2,5),'     ') FROM [CensusData].[dbo].[MainQuestionnaire$]" queryout "C:\bcptest.txt" -T -c -t -S "MYSERVER"

Then just write TestSQL.bat at the command prompt. Then try the same with:

bcp "SELECT '1', isnull(left(DISTRICT,1),' '), isnull(left(CTV,2),'  '), isnull(left(CTV2,5),'     ') FROM [CensusData].[dbo].[MainQuestionnaire$]" queryout C:\bcptest.txt -T -c -t -S MYSERVER
0
 
sib_userAuthor Commented:
khun,

Tried that and for some strange reason the server seems to be inaccessible when I run the batch file.

I can't figure it out because it runs well from sql server management studio.
0
 
sib_userAuthor Commented:
Got the answer here: ttp://social.msdn.microsoft.com/Forums/en/transactsql/thread/ff79934d-36c6-4078-8081-5e1f14df82c2

All I had to do was put two single quotes everywhere on quote appeared and make sure that everything is on thesame line.

Thanks very much for your attempts.
0
 
sib_userAuthor Commented:
I got the answer on another forum.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now