How to execute an extrememly long Script

Question

User248267340 posted

I'd like to know if there's a way to execute a script using Dynamic-SQL that is a total of 64,000 bytes long. Its way too long, but I'm trying to buy some time.

I have the script in a string defined as varchar(max), but it still won't run.

EXEC(@wstr)   is how I execute it, but all I get is errors for output. (I know the code is all good, no bugs).

Answer 1

User475983607 posted

I'd like to know if there's a way to execute a script using Dynamic-SQL that is a total of 64,000 bytes long. Its way too long, but I'm trying to buy some time.

64,000 bytes is only ~64k and, IMHO, not long at all.

EXEC(@wstr)   is how I execute it, but all I get is errors for output. (I know the code is all good, no bugs).

You have to realize that we can only see what you share.  What are the output errors?  Have you investigated the error?  What troubleshooting steps have you performed?

Answer 2

User753101303 posted

Hi,

Maybe an EXEC limitation. What if you try sp_executesql instead? According to https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-2017 tells explicity the statement could be up to a 2 Gb.

If it doesn't help, as pointed already always tell what is the first error you have to start with. It is easier to help if knowing which error you have !!!

Answer 3

User248267340 posted

Thanks for responding PatriceSc. I didn't think it would help reporting the error, because it doesn't produce a runtime error message or code.

It just says the script has a bad line, and that's because it's truncating it.

I never thought it would truncate my script, but that's what's happening. That's why I asked about varchar(max).

In any event, I'll read through the link you sent me - hopefully that will help.

Thank you VERY much for reading and responding!

Coreysan