Hi,

I'm using SQLCMD in PDW for extracting data into a flat file. The command line syntax is given below:

sqlcmd -S "10.20.30.40,19001" -d MyPDW_DB -U PDW_User -P Password1 -Q "SET QUOTED_IDENTIFIER ON; SELECT * FROM MyPDW_DB.dbo.SampleFact" -o "FactOut.txt" -s"|"

When I try to execute the batch file, I get the following error:

Msg 104409, Level 16, State 1, Server PdwTdsServer, Line 1
Setting QuotedIdentifier to 'OFF' is not supported.

I am assuming this is due to the fact that there is a "comma" in the server name (IP address,Port Number). I can use this command for extracting data from SQL tables. Any idea on how I can make this working for PDW?

Thanks in advance

Hi Triumph,

Based on the error message, we can infer the issue is caused by the SET QUOTED_IDENTIFIER connection option sets to OFF.

To fix this issue, we should add the -I command switches to set the SET QUOTED_IDENTIFIER connection option to ON in the command script. The following command is for your reference:

sqlcmd -S "10.20.30.40,19001" -d MyPDW_DB -U PDW_User -P Password1 -I -Q "SET QUOTED_IDENTIFIER ON; SELECT * FROM MyPDW_DB.dbo.SampleFact" -o "FactOut.txt" -s"|"

Reference:
sqlcmd Utility

If there are any other questions, please feel free to ask.

Thanks,
Katherine Xiong

If you have any feedback on our support, please click here.

Thanks a ton Katherine. It's working now. :)

However, there is one small issue that I could use help with. Since "SET NOCOUNT ON" is not a recognized function in PDW, in the output file, it is printing the number of rows affected. I'm wondering if you can help me with that as well. 

Thanks in advance

Hi Triumph,

There is a addtional command -I used to set the SET QUOTED_IDENTIFIER connection option, so the "SET QUOTED_IDENTIFIER ON" doesn't work in sqlcmd. But it seems that the option "SET NOCOUNT ON" is supported in the sqlcmd, so when we add "SET NOCOUNT ON" in the cmd, the count is not returned.

Besides, if my reply answer your question, please mark it as answer which will help other forum members who have the similar issues.

Thanks,
Katherine Xiong

If you have any feedback on our support, please click here.

Thanks for your reply. Yes, I understand "SET NOCOUNT ON" is supported in SQLCMD. However, when used against a PDW DB, this is the error that I get:

Msg 104003, Level 16, State 1, Server PdwTdsServer, Line 1
'NoCount' is not a recognized option.

Are there any other alternatives for NOCOUNT?

Hi,

I still haven't figured a workaround for this. My SQLCMD is given below:

sqlcmd -S "10.20.30.40,19001" -d MyPDW_DB -U PDW_User -P Password1 -I -Q "SELECT * FROM MyPDW_DB.dbo.SampleFact" -o "Output_Temp.txt" -s"|" -W

findstr /B /V /C:"----" Output_Temp.txt > SampleFact.txt

del Output_Temp.txt

I'm deleting the '-----' under column names in the output file using the above. Similarly, is there a way to delete (xxxx rows affected) in the file? Please advice.

you can add set nocount as below 

sqlcmd -S "10.20.30.40,19001" -d MyPDW_DB -U PDW_User -P Password1 -I -Q "set nocount on;SELECT * FROM MyPDW_DB.dbo.SampleFact" -o "Output_Temp.txt" -s"|" -W

It wont display (xxx row affected)

To remove ----- you can use below

sqlcmd -S "10.20.30.40,19001" -d MyPDW_DB -U PDW_User -P Password1 -I -Q "set nocount on;SELECT * FROM MyPDW_DB.dbo.SampleFact" -o "Output_Temp.txt" -s"|" -W

type Output_Temp.txt | findstr /v \-\- > SampleFact.txt
del Output_Temp.txt

Hi Triumph,

Here is the complete solution you have asked.

sqlcmd -S "10.20.30.40,19001" -d MyPDW_DB -U PDW_User -P Password1 -I -Q "set nocount on;SELECT * FROM MyPDW_DB.dbo.SampleFact" -o "Output_Temp.txt" -s"|" -W
type Output_Temp.txt | findstr /v \-\- > SampleFact.txt
del Output_Temp.txt

As Katherine suggested please mark it as answer which will help other forum members who have the similar issues.

Hi,

I cannot use NOCOUNT as I'm working in Parallel Data Warehouse (PDW) where it is not a recognized option. Please see the initial posts in this thread. Hence I need a way to delete the "rows affected" in the file. Can you help?

Thanks in advance.

type e:\backup\Output_Temp.txt | findstr /v /c:"rows affected" > e:\backup\SampleFact.txt

Thank you for your answer.

You have stated that I can use "type Output_Temp.txt | findstr /v \-\- > SampleFact.txt' to remove hyphens under the column name. But this is actually deleting all rows with a "-". Hence this is what I'm using:

type Output_Temp.txt | findstr /B /V /C:"----" Output_Temp.txt > Output_Temp1.txt 
type Output_Temp1.txt | findstr /V /C:"rows affected" > SampleFact.txt
del Output_Temp.txt
del Output_Temp1.txt

One more question, can this be achieved using one temp file? The number of rows in my fact table is over 30 million rows. Hence creating 2 temp files and deleting them in the end is not ideal. Any ideas?

type Output_Temp.txt | findstr /B /V /C:"----" | findstr /V /C:"rows affected" > SampleFact.txt

• Edited by gona_david 2 hours 22 minutes ago

Thanks for this, really appreciate your quick response. Let me test this and I will "Mark as Answer".

I see you have referred this link but still not marked as answered.