September 28, 2012 at 8:27 am
Hi,
When I run the below query as dataset in my report it shows me data for multiple values of @order but when I run the report it says
FnSplitString has to many arguments specified. I checked the function , it does have varchar (max). what could be wrong? it runs perfectly fine whern run in dataset query window , it is the report that throws error. thanks.
Declare @1 table
(cal varchar(max))
;
WITH CTE AS
(
SELECT dates
FROM table AS t
inner join dbo.FnSplitString(@order,',') ss on ltrim(rtrim(ss.Item)) = ltrim(rtrim(t.value))
and t.itemid=20
WHERE
dates between '1/1/2012' and '1/31/2012'--@StartDate AND @EndDate
group by dates
)
INSERT INTO @1
SELECT t.value AS cal FROM table AS t INNER JOIN
CTE AS CT ON t.dates = ct.dates WHERE (t.item_Id = 200)
GROUP BY t.value
SELECT cal from @1
September 28, 2012 at 8:32 am
Not enough details to provide much of an answer. What is in @orders? What does the split function look like?
I would recommend using the splitter found in the article referenced in my signature about splitting strings. Given that yours takes varchar(max) I am guessing it is loop or xml based. If your value is <= 8,000 characters the one I reference will be way faster for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 28, 2012 at 8:44 am
SQL_path (9/28/2012)
Hi,When I run the below query ...
Can you post the actual query you are attempting to run? There are several errors in the query you posted.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 28, 2012 at 8:47 am
Sean Lange (9/28/2012)
Not enough details to provide much of an answer. What is in @orders? What does the split function look like?I would recommend using the splitter found in the article referenced in my signature about splitting strings. Given that yours takes varchar(max) I am guessing it is loop or xml based. If your value is <= 8,000 characters the one I reference will be way faster for you.
Thanks for replying !! the query workd perfectly fine in dataset window of report but when running actual report, the dataset throws error.
@orders like 'ABCD1002, EFGH2120, IJKLMN4567'
ALTER FUNCTION [dbo].[fnSplitString](@Text varchar(max),@Delimiter varchar(2)= ' ')
RETURNS @Strings TABLE
(
Position int IDENTITY PRIMARY KEY,
Item varchar(max)
)
AS
BEGIN
DECLARE @index int
SET @index = -1
WHILE (LEN(@Text) > 0)
BEGIN
SET @index = CHARINDEX(@Delimiter,@Text)
IF (@Index = 0) And (LEN(@Text) > 0)
BEGIN
INSERT INTO @Strings VALUES (@Text)
BREAK
END
IF (@Index > 1)
BEGIN
INSERT INTO @Strings VALUES (LEFT(@Text,@Index - 1))
SET @Text = RIGHT(@Text, (LEN(@Text) - @index))
END
ELSE
SET @Text = RIGHT(@Text, (LEN(@Text) - @index))
END
RETURN
END
September 28, 2012 at 8:53 am
SQL_path (9/28/2012)
Sean Lange (9/28/2012)
Not enough details to provide much of an answer. What is in @orders? What does the split function look like?I would recommend using the splitter found in the article referenced in my signature about splitting strings. Given that yours takes varchar(max) I am guessing it is loop or xml based. If your value is <= 8,000 characters the one I reference will be way faster for you.
Thanks for replying !! the query workd perfectly fine in dataset window of report but when running actual report, the dataset throws error.
@orders like 'ABCD1002, EFGH2120, IJKLMN4567'
ALTER FUNCTION [dbo].[fnSplitString](@Text varchar(max),@Delimiter varchar(2)= ' ')RETURNS @Strings TABLE
(
Position int IDENTITY PRIMARY KEY,
Item varchar(max)
)
AS
BEGIN
DECLARE @index int
SET @index = -1
WHILE (LEN(@Text) > 0)
BEGIN
SET @index = CHARINDEX(@Delimiter,@Text)
IF (@Index = 0) And (LEN(@Text) > 0)
BEGIN
INSERT INTO @Strings VALUES (@Text)
BREAK
END
IF (@Index > 1)
BEGIN
INSERT INTO @Strings VALUES (LEFT(@Text,@Index - 1))
SET @Text = RIGHT(@Text, (LEN(@Text) - @index))
END
ELSE
SET @Text = RIGHT(@Text, (LEN(@Text) - @index))
END
RETURN
END
What about the code which calls this? Can you post it?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 28, 2012 at 8:53 am
Can you post the actual values for your string and the EXACT error message. I don't see anything that would cause an error but I don't really know what the error is either.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 28, 2012 at 9:00 am
Sean Lange (9/28/2012)
Can you post the actual values for your string and the EXACT error message. I don't see anything that would cause an error but I don't really know what the error is either.
Here is error message:
An error occurred during local report processing,
An error has occurred during report processing,
Query execution failed for dataset ORDERTYPE
Procedure or function dbo.fnsplitstring has too many arguments specified
values are: N'TSMITH81512,TSMITH081612,TSMITH081712,TSMITH082012,TSMITH082112,TSMITH082212,TSMITH082312,TSMITH082712'
thanks !!
September 28, 2012 at 9:12 am
SQL_path (9/28/2012)
Sean Lange (9/28/2012)
Can you post the actual values for your string and the EXACT error message. I don't see anything that would cause an error but I don't really know what the error is either.Here is error message:
An error occurred during local report processing,
An error has occurred during report processing,
Query execution failed for dataset ORDERTYPE
Procedure or function dbo.fnsplitstring has too many arguments specified
values are: N'TSMITH81512,TSMITH081612,TSMITH081712,TSMITH082012,TSMITH082112,TSMITH082212,TSMITH082312,TSMITH082712'
thanks !!
I think at this point you need to provide all the details. ddl and sample data for all the tables. What you are saying doesn't seem to match up with what I see but I can't even think about testing anything without a lot of ddl and data that I don't have.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 28, 2012 at 9:15 am
[/quote]
I think at this point you need to provide all the details. ddl and sample data for all the tables. What you are saying doesn't seem to match up with what I see but I can't even think about testing anything without a lot of ddl and data that I don't have.[/quote]
I am trying to capture as much as I can, it is actually 4-5 procs and a report with exhaustive list of parameters. this is crazy :crying:
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply