November 17, 2013 at 1:39 am
I have a string variable which stores a big set of sql statements, ie insert statements. The insert statement intends to inserts about 200K rows with 10 columns (Lots !)
I think that my package always fails when the SQL string is this big. So, I was wondering if there is any limit on the size of the sql string ? It shows no errors or warnings.
November 17, 2013 at 2:30 am
I have a string variable which stores a big set of sql statements, ie insert statements. The insert statement intends to inserts about 200K rows with 10 columns (Lots !)
I think that my package always fails when the SQL string is this big. So, I was wondering if there is any limit on the size of the sql string ? It shows no errors or warnings.
Yes there's a limit. I searched Google and found it in two minutes. Have you even tried?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 17, 2013 at 12:49 pm
blasto_max (11/17/2013)
I have a string variable which stores a big set of sql statements, ie insert statements. The insert statement intends to inserts about 200K rows with 10 columns (Lots !)I think that my package always fails when the SQL string is this big. So, I was wondering if there is any limit on the size of the sql string ? It shows no errors or warnings.
How does your package fail? How do you know it fails if there are no errors or warnings.
Regarding the limit: try using a stored procedure instead.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 17, 2013 at 4:14 pm
Phil Parkin (11/17/2013)
I have a string variable which stores a big set of sql statements, ie insert statements. The insert statement intends to inserts about 200K rows with 10 columns (Lots !)
I think that my package always fails when the SQL string is this big. So, I was wondering if there is any limit on the size of the sql string ? It shows no errors or warnings.
Yes there's a limit. I searched Google and found it in two minutes. Have you even tried?
Yes, but may be my words were not correct. What words did you use ?
November 17, 2013 at 11:08 pm
Have a look at this page. All sorts of useful info on limits there, including max. batch size.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 18, 2013 at 12:16 am
Phil Parkin (11/17/2013)
Have a look at this page. All sorts of useful info on limits there, including max. batch size.
Maximum Capacity Specifications for SQL Server....I'll keep that in mind and bookmarks. Thanks.
Which part of the link tells me how to find the max size of SQL string ?
November 18, 2013 at 12:32 am
Batch size.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 18, 2013 at 12:43 am
Phil Parkin (11/18/2013)
Batch size.
I think the limit in SSIS is smaller.
I've had issues with very large SQL statements in Execute SQL Tasks, but they were less than 64K characters if I remember correctly.
If the string is stored in an SSIS variable configured with an expression, prior to SQL Server 2012 the limit was 4000 characters.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 18, 2013 at 1:00 am
Koen Verbeeck (11/18/2013)
Phil Parkin (11/18/2013)
Batch size.I think the limit in SSIS is smaller.
I've had issues with very large SQL statements in Execute SQL Tasks, but they were less than 64K characters if I remember correctly.
If the string is stored in an SSIS variable configured with an expression, prior to SQL Server 2012 the limit was 4000 characters.
Interesting. My ExecuteSQL statements are usually very short, so I have not come up against any limit.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 18, 2013 at 1:10 am
Phil Parkin (11/18/2013)
Koen Verbeeck (11/18/2013)
Phil Parkin (11/18/2013)
Batch size.I think the limit in SSIS is smaller.
I've had issues with very large SQL statements in Execute SQL Tasks, but they were less than 64K characters if I remember correctly.
If the string is stored in an SSIS variable configured with an expression, prior to SQL Server 2012 the limit was 4000 characters.
Interesting. My ExecuteSQL statements are usually very short, so I have not come up against any limit.
I have. 😀
A lot of views with a lot of columns strung together with UNION ALL.
When using expressions to create dynamic SQL, you can hit the limit of 4k characters pretty fast.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 18, 2013 at 1:21 am
4K chars ? Thats like a full stop for me. How about 500K chars plus ? 🙂
November 18, 2013 at 1:26 am
blasto_max (11/18/2013)
4K chars ? Thats like a full stop for me. How about 500K chars plus ? 🙂
Use a stored procedure, like I mentioned earlier.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply