varchar overloaded 1

[joshuaguilty]

I have a varchar variable (@INVOICE) which contains a field from user's input. Users would choose Invoice # to retrieve information. As long as @INVOICE is within 8000 characters (together with the dynamic SQL) the SP is running smoothly. BUT when it exceeds its limit then I got an error "... ntext to.... varchar not allowed". I have tried some ways to test and reSET the @INVOICE to a shorter length to avoid crash, but failed. Guys do have the same experience !?

 

[mrdenny]

Dynamic SQL commands can not exceed the length of the VARCHAR datatype or NVARCHAR datatype depending on the method you use to run the dynamic SQL. This is one of the reasons why it's recommended not to use dynamic SQL.

Is the only part of the query which is dynamic the list of invoices which is being passed in?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[joshuaguilty]

Thank you for the response. The dynamic SQL query actually is declared as @SQL1 varchar(8000)/@INVOICE varchar(8000), is needed 1st because of the unknown number of invoices being requested, second is the number of companies (databases) involved. Therefore, users could ask for CompanyA/CompanyB/CompanyC with invoices #001/055/100/199/888. My problem is when user requests for invoices I have to add decoration for '001,055,100,199,888' to become ('001','055','100','199','888'). 8000 the maximum that I could not change under SQL 2000, my idea is that is there a way to stop the process from being crashed or before showing the error by reSET the @INVOICE to a shorter length like from '001,...........' to ('001') and do something on the SQL query by removing the filter and have 100% invoices outcome ?

 

[mrdenny]

The better solution is use is to pass in the invoice numbers as an XML document so that you don't need to use dynamic SQL. You can simply use the XML document as a join partner in the query. This will increase the amount of data you can pass in as the XML document can now take up the entire 8000 characters.

If you need an example I can provide.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[joshuaguilty]

Yes, awaiting for your next instructions. Thanks.

 

[xlbo]

Please post in a relevant forum

Your question has nothing to do with Reporting Services

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[joshuaguilty]

I apologize for NOT being clear. The data collected to run the dynamic SQL is from the Reporting Services (2000). User check the list to request data. If this is not the right place, could someone please direct/guide me to the right place and get the right answer? Thank you!

 

[xlbo]

The issue you have is not with reporting services, it is with writing SQL

forum183

should be more relevant

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[joshuaguilty]

Thank you for the information.