Solved

Problems with single quote character iwhen attempting to export data

Posted on 2010-09-15
10
198 Views
Last Modified: 2012-05-10
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
Comment
Question by:sib_user
  • 5
  • 3
  • 2
10 Comments
 
LVL 15

Expert Comment

by:AmmarR
Comment Utility
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
 
LVL 8

Expert Comment

by:khun
Comment Utility
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
 

Author Comment

by:sib_user
Comment Utility
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
 
LVL 15

Expert Comment

by:AmmarR
Comment Utility
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
 
LVL 8

Expert Comment

by:khun
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:sib_user
Comment Utility
AmmarR;
Same error.

khun:
Have a look at the attached image.
errors.bmp
0
 
LVL 8

Expert Comment

by:khun
Comment Utility
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
 

Author Comment

by:sib_user
Comment Utility
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
 

Accepted Solution

by:
sib_user earned 0 total points
Comment Utility
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
 

Author Closing Comment

by:sib_user
Comment Utility
I got the answer on another forum.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now