OSQL QUERY TOO LONG ERROR - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

OSQL QUERY TOO LONG ERROR

Author

Topic

kgundava
Yak Posting Veteran

66 Posts

Posted - 2011-07-21 : 19:53:19

Hi All,
I have a query which i am trying to run and it has more than 2500 characters in it.When i try to execute this query i am getting the Query too long error.Please let me how to fix this. I have windows 2008 R2 installed
Plz see the sample query below.....

DECLARE @CMD88 varchar(8000)
Select @CMD88 = 'osql -E -S -Q"select "MANDT","BUKRS","KUNNR","UMSKS","UMSKZ","AUGDT","AUGBL","ZUONR","GJAHR","BELNR","BUZEI","BUDAT","BLDAT","CPUDT","WAERS","XBLNR","BLART","MONAT","BSCHL","ZUMSK","SHKZG","GSBER","MWSKZ","DMBTR","WRBTR","MWSTS","WMWST","BDIFF","BDIF2","SGTXT","PROJN","AUFNR","ANLN1","ANLN2","SAKNR","HKONT","FKONT","FILKD","ZFBDT","ZTERM","ZBD1T","ZBD2T","ZBD3T","ZBD1P","ZBD2P","SKFBT","SKNTO","WSKTO","ZLSCH","ZLSPR","ZBFIX","HBKID","BVTYP","REBZG","REBZJ","REBZZ","SAMNR","ANFBN","ANFBJ","ANFBU","ANFAE","MANSP","MSCHL","MADAT","MANST","MABER","XNETB","XANET","XCPDD","XINVE","XZAHL","MWSK1","DMBT1","WRBT1","MWSK2","DMBT2","WRBT2","MWSK3","DMBT3","WRBT3","BSTAT","VBUND","VBELN","REBZT","INFAE","STCEG","EGBLD","EGLLD","RSTGR","XNOZA","VERTT","VERTN","VBEWA","WVERW","PROJK","FIPOS","NPLNR","AUFPL","APLZL","XEGDR","DMBE2","DMBE3","DMB21","DMB22","DMB23","DMB31","DMB32","DMB33","BDIF3","XRAGL","UZAWE","XSTOV","MWST2","MWST3","SKNT2","SKNT3","XREF1","XREF2","XARCH","PSWSL","PSWBT","LZBKZ","LANDL","IMKEY","VBEL2","VPOS2","POSN2","ETEN2","FISTL","GEBER","DABRZ","XNEGP","KOSTL","RFZEI","KKBER","EMPFB","PRCTR","XREF3","QSSKZ","ZINKZ","DTWS1","DTWS2","DTWS3","DTWS4","XPYPR","KIDNO","ABSBT","CCBTC","PYCUR","PYAMT","BUPLA","SECCO","CESSION_KZ","PPDIFF","PPDIF2","PPDIF3","FKBER" from DB..Ganta.[Test] Except select "MANDT","BUKRS","KUNNR","UMSKS","UMSKZ","AUGDT","AUGBL","ZUONR","GJAHR","BELNR","BUZEI","BUDAT","BLDAT","CPUDT","WAERS","XBLNR","BLART","MONAT","BSCHL","ZUMSK","SHKZG","GSBER","MWSKZ","DMBTR","WRBTR","MWSTS","WMWST","BDIFF","BDIF2","SGTXT","PROJN","AUFNR","ANLN1","ANLN2","SAKNR","HKONT","FKONT","FILKD","ZFBDT","ZTERM","ZBD1T","ZBD2T","ZBD3T","ZBD1P","ZBD2P","SKFBT","SKNTO","WSKTO","ZLSCH","ZLSPR","ZBFIX","HBKID","BVTYP","REBZG","REBZJ","REBZZ","SAMNR","ANFBN","ANFBJ","ANFBU","ANFAE","MANSP","MSCHL","MADAT","MANST","MABER","XNETB","XANET","XCPDD","XINVE","XZAHL","MWSK1","DMBT1","WRBT1","MWSK2","DMBT2","WRBT2","MWSK3","DMBT3","WRBT3","BSTAT","VBUND","VBELN","REBZT","INFAE","STCEG","EGBLD","EGLLD","RSTGR","XNOZA","VERTT","VERTN","VBEWA","WVERW","PROJK","FIPOS","NPLNR","AUFPL","APLZL","XEGDR","DMBE2","DMBE3","DMB21","DMB22","DMB23","DMB31","DMB32","DMB33","BDIF3","XRAGL","UZAWE","XSTOV","MWST2","MWST3","SKNT2","SKNT3","XREF1","XREF2","XARCH","PSWSL","PSWBT","LZBKZ","LANDL","IMKEY","VBEL2","VPOS2","POSN2","ETEN2","FISTL","GEBER","DABRZ","XNEGP","KOSTL","RFZEI","KKBER","EMPFB","PRCTR","XREF3","QSSKZ","ZINKZ","DTWS1","DTWS2","DTWS3","DTWS4","XPYPR","KIDNO","ABSBT","CCBTC","PYCUR","PYAMT","BUPLA","SECCO","CESSION_KZ","PPDIFF","PPDIF2","PPDIF3","FKBER" from DB1..Ganta.[Test]"-o"E:\R\1.txt"'
EXEC master..xp_cmdshell @cmd88

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-07-21 : 19:55:12

Put your query into a view, and then query the view from osql. By the way, you should be using sqlcmd if you are on SQL 2005 or greater.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

kgundava
Yak Posting Veteran

66 Posts

Posted - 2011-07-21 : 19:59:43

I am really new to SQL server. I did not understand the solution :( can you give me a example( i.e. How can i put the query into a view)
Also is it not possible to make this one work at OSQL level???
Thanks a ton!!!!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-07-21 : 20:09:28

create view SomeViewName
as
your query goes here
go

Hit F5 to create the view.

Now use SomeViewName in your osql command: select * from SomeViewName

It should now work.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

kgundava
Yak Posting Veteran

66 Posts

Posted - 2011-07-22 : 15:53:38

Thanks for the reply.When i started executing the select * from the view
using osql i got a Oracle oledb connection lost error (Linked server to Oracle from sql server). Please let me know if it is possible to run huge queries which would take 3-4 hours from OSQL?? or does it have any limitations.
Thanks.....

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-07-22 : 16:17:07

Osql doesn't have that limitation as far as I know. You may want to try sqlcmd though as osql has been deprecated and isn't as good.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources