Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kgundava
Yak Posting Veteran
66 Posts |
 Posted - 2011-07-23 : 11:06:43
|
| HI All, I am trying to write the output of a query to a file using either OSQL or SQLCMD.The Table has 120+ columns and some columns may have special characters. SO i use double quotations for the column names.The issue OSQL works fine for queries which are little bit smaller but throws an error for lengthy queries.(I.e. Input query too long).Query syntax:declare @testcmd sysname,@VARIABLEVALUE sysnameSELECT @VARIABLEVALUE = 'OSQL -E -S -Q "Select "SourceTable" from PROGRAMDELTA1 " -o"E:\ReportNew1\test.txt"'EXEC master..XP_cmdshell @VARIABLEVALUE To avoid length issue i have tried to switch to SQLcmd, but SQLCMD does not understand column names in double quotations and the query does not executed.But if we remove double for column names the query gets executed.Sample query:declare @testcmd sysname,@VARIABLEVALUE sysnameSELECT @VARIABLEVALUE = 'sqlcmd -E -Q "Select "SourceTable" from PROGRAMDELTA1 " -o"E:\ReportNew1\test.txt"'EXEC master..XP_cmdshell @VARIABLEVALUE Please let me know if i can increase the OSQL query length or is there any other way to write output to a file.Thanks..... |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-07-23 : 12:55:05
|
Would using square brackets instead of double-quotes to escape special characters work for you? For example:SELECT @VARIABLEVALUE = 'sqlcmd -E -Q Select [SourceTable] from PROGRAMDELTA1 " -o"E:\ReportNew1\test.txt"' |
 |
|
|
kgundava
Yak Posting Veteran
66 Posts |
Posted - 2011-07-24 : 00:38:04
|
| Thanks to both of u for the quick response.@sunita\Vishal: The square brackets solution actually worked for me.But i still have issues with huge queries(expected to run for three -- four hours).I am running these queries against a oracle linked and for some reason i am getting the oracle connection lost error.This happens only when i try to execute the query from the sqlcmd or OSQL and not from regular sql. In order to avoid this error is there any other way to write to a file i.e. from the data management studio itself???? As per vishal's suggestion i would look into the BCP too.... but any other suggestion's would definitely help me...Thanks.... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-07-24 : 00:57:22
|
| if you want to import data from oracle server you can even use export / import wizard. use connection manager for oracle source and oledb connection for sql destination table.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
kgundava
Yak Posting Veteran
66 Posts |
Posted - 2011-07-24 : 01:36:05
|
| Thanks Vishak.....I am doing a data validation for the data in sql server and oracle server. For this i am using except qery(linked server to access the data in oracle). OIf except query returns any differences i write these differences to a file. This is working for small qeries and not for queries which return huge datasets(i.e oracle oledb is loosing the connection). please let me know if BCP solves this issue or not.Thanks.... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-07-24 : 01:43:42
|
| using except query over linked server can be slow.i think you would better of dropping records from linked server onto a temporary table in your server and then use except between temporary table and your table which will execute much faster.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Help in writing query output to a file