Running a long SQL query via ADODB

[peejay]

I am trying to run an SQL query via VBA and seem to be running into an issue with the length of my query.
The query is connecting to a SQL Server table, and is to select particular fields from the table.
The length of the string with the code is ~2100 chars (the field list is built via a loop adding dynamic field names from a row).

I can run the code successfully from within MS SQL Management Studio, so the code is OK. I passed a short query to the macro running the code and it works fine too so it's the length of the SQL string the macro builds which is the problem.

I've researched this problem and it sounds like ~2000 chars isn't outside the character limit so I'm a bit stumped.
I list the SQL code via Debug.Print so I can copy/run it in MSSQL Studio and noticed the code appears to have a line break in it after around 1023 chars so am wondering if the building of the String field is somehow adding a line feed char to the code after some character limit point, which is preventing the SQL from run properly (running it via objRecordSet = str, objConnection but the Record Set never opens).

Any suggestions gratefully accepted.

 

[peejay]

Update...

I found a tip on adding a linefeed character to the code to break up the row length; I've tried this but still getting the same problem - the RecordSet doesn't get opened ?‍

 

[RoryA]

Are you getting an error of some sort?

 

[peejay]

Hi RoryA
No, no error message from running the .OPEN command; it just doesn't give me a recordset as a result, so when I check for recordset.BOF then I get an error saying the record set is closed.

 

[RoryA]

Then you'd need to post the query. For whatever reason, it is just returning no records - the fact that you can run it in SQL studio does not mean it will necessarily work as-is with ADO.

 

[peejay]

Hi RoryA
I managed to solve this.
It seems it wasn’t the length of the query after all (I found a colleagues code which was nearly 4000 chars running ok) - my code was using the USE command (USE databasename) and once I removed this it was fine.

I’ve used this in other (much shorter) queries in the past so I’ve no idea why it didn’t like it but at least it’s going now.

Thanks for your time to consider my problem.