sib_user
asked on
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].[MainQu estionnair e$]
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].[MainQu estionnair e$]" 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.
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].[MainQu
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].[MainQu
I know that the problem is with the single quote characters but don't know how to fix it.
Please help.
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].[MainQu estionnair e$]" 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!
---
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].[MainQu
Please note this suggestion is just a guess and untested :-) But it should give you a clue where to look...
Good luck!
ASKER
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.
I get this error when I attempt your solution:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '),
isnull(left(replace(CTV,''
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.
there were 2 commas at the end of the line
try this now
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"'
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].[MainQu estionnair e$]" 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].[MainQu estionnair e$]" queryout C:\bcptest.txt -T -c -t -S MYSERVER
Should help you detect what the problem really is...
bcp "SELECT
'1',
isnull(left(DISTRICT,1),' '),
isnull(left(CTV,2),' '),
isnull(left(CTV2,5),' ')
FROM [CensusData].[dbo].[MainQu
to check that works. Then:
bcp "SELECT
'1',
isnull(left(DISTRICT,1),' '),
isnull(left(CTV,2),' '),
isnull(left(CTV2,5),' ')
FROM [CensusData].[dbo].[MainQu
Should help you detect what the problem really is...
ASKER
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].[MainQu estionnair e$]" 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].[MainQu estionnair e$]" queryout C:\bcptest.txt -T -c -t -S MYSERVER
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].[MainQu
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].[MainQu
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I got the answer on another forum.
check the code below
replace(column name,'''','')
Open in new window