How to break the 4000 character barrier in SQLCOMMAND.

Question

How to break the 4000 character barrier in SQLCOMMAND.

mbmalm

SSC-Addicted

Points: 487
More actions
November 23, 2011 at 9:46 am

#251005

I have a query that exceeds the 4000 character limit. It is executed in datasource reader. I have created the command using this expression @[User::SQL_CODE1] + @[User::SQL_CODE2] + @[User::SQL_CODE3] + @[User::Cust_List] + @[User::SQL_CODE4] in the Data Flow Task expression Builder. None of the variables including the cust_list exceed 3000 characters. It is quite a complicated query with many tables accessed. Is there a method I could use to get past the 4000 character boundary?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Create a stored procedure and then execute the stored procedure from there.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

mbmalm SSC-Addicted Points: 487 More actions · Answer 2

I cannot create a Stored Procedure. Sql is going against corporate TERADATA database, not allowed to create function on the system.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

The only other thing is to try and break up the query. Sometimes you can break the query out to pull from a subset of tables and then do that a few times for the remaining subsets and join that data within the flow.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

mbmalm SSC-Addicted Points: 487 More actions · Answer 4

Thanks for the reply. Unfortunately the tables are vary large and in breaking up the query I exceed the spool space limits in Teradata for the userid executing the query (currently set at 90 gig).

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Wow, I really think the best solution is to get help from the teradata team to help create this query on their end. Alternatively, you could try chunking the query into a .Net script object and then have that return all of your data to the flow. Like I said though, I would prefer to work with the Teradata people and see if I can get a query created on their side or a job scheduled that pre-populates this data into a separate workspace for you to consume in SSIS.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

mbmalm SSC-Addicted Points: 487 More actions · Answer 6

Thanks for the assistance. I have spent a couple of days redoing the query. Not able to exclude the customer # before without exceeding the spool space limit. Am able to do that now by changing serveral join statements, so I did a merge join with the customer #'s and the thing works fine. I do wish I could insert a variable into the Datasource reader. It does not have the 4000 character limitation when the code is entered directly into sqlcommand instead of using my 5 variables. Would like to just pass the customer # list into the "IN" statement.

COZYROC One Orange Chip Points: 28499 More actions · Answer 7

If you have this option available, start using SQL 2012 RC0. The 4000 character limitation has been resolved in it.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/