 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
VB Forum / Database Access / August 2006ADO and very large queries
Hello all, I have to execute a script to generate a very specific access report on a very large list of servers. The size of the script is over 80 kB, and runs perfectly fine on Query Analyzer, since the database is on a MSSQL server. However, if I put that script into a variable, and try to run it using a recordset (since the result comes in a single-row single-column grid) it complains of a very generic error. I checked that the script being sent was the correct one, since I wrote it to a file before opening the recordset. I copied the content of the file and compared to the original script and there was no difference at all. So, my theory is the recordset object does not take a query that has that size (which is 80 kilobytes). Am I correct? If so, how can I overcome that limitation? If not, what other mean can I use to fetch the results? I already tried using OSQL from the command prompt, but not only the output gets scrambled but also the file size grows to several times the expected one. The usual size is about 60 kB, and using OSQL it gets near 7 MB. Thanks, Anthuan > Hello all, > [quoted text clipped - 20 lines] > Thanks, > Anthuan I don't think there is practical size limitation - I used to run TSQL statements via ADO with multimeg size without problems. You, probably, should show some code and tell what kind of error do you get. Dmitriy. Dmitriy, The error message is somewhat useless: ---------------------------- Run-time error '-2177217900 (80040e14)' Invalid column name ' '. ---------------------------- Since the script is quite large, and it works when using the Query Analyzer, I'm not sure how to even start debugging this. If, however, you have any idea, I'd appreciate it. Anthuan. > I don't think there is practical size limitation - I used to run TSQL > statements via ADO with multimeg size without problems. You, probably, > should show some code and tell what kind of error do you get. > > Dmitriy. > Dmitriy, > [quoted text clipped - 14 lines] >> >> Dmitriy. You don't even tell, when you have this error, what method do you use to open the recordset (Connection object, Command object, recordset's open method or else). If you don't show the code it is difficult to give any advise - there is just no required information. The only general advise I can give - try to minimize you TSQL statement until it works without an error. Then - add another portion. Continue until you encounter an error again. This way you can localize the problem. Additionally - try to inspect content of your variable (the one, which holds entire TSQL script just before you submit it to server) in debugging mode - you may find some discrepancy there, maybe you read file in some way, which changes final script compared to what you run in Query Analyzer (QA). You can even save this variable in another file and then try to run content of that new file in QA - is result the same?. Dmitriy. >> Dmitriy, >> [quoted text clipped - 32 lines] > > Dmitriy. I don't think you would get the error you report if the query string were cut off by some limit. More likely, the adoRecordset.Source or adoCommand.CommandText property value has an error. I also suggest you save the contents to a text file for inspection, or run in QA. It can be difficult to convert a query that runs in QA to a string for assignment to an ADO object.  SignatureRichard Microsoft MVP Scripting and ADSI Hilltop Lab - http://www.rlmueller.net Dmitriy, I'm sorry I did not show any code. I chose not to do so because I though the code was too standard to be the source of the problem. Either way, the sql script was not developed by me, thus chopping it to smaller working pieces did not seem like an option. Richard, I did as you said, outputting the script variable to a file, and then running that file on QA, and it worked fine. That was my first debugging option, and unfortunately it only led me to believe the script was not the source of the problems. So: Since the scripts does work on QA, and I do need to save the output to a file, I decided to use QA to generate the report, using the shell sub. So, in the end, it looks like this: Call Shell("isqlw -E -S " & serverName & " -d " & databaseName & " -i c:\file.sql -o report.html") Not a coding masterpiece, I know, but solved the problem nicely. Regards, Anthuan > >> Dmitriy, > >> [quoted text clipped - 44 lines] > Microsoft MVP Scripting and ADSI > Hilltop Lab - http://www.rlmueller.net |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.