INvariant part inside SELECT

All,
Can we do this trick and if yes then how? Just schematically: the SP should
return the number of records if the parameter @.Count=1, if not, then the
records themselves. The problem is that there is some complicated JOIN and
the whole set of WHERE clauses that I wouldn't like to repeat in two
different queries looking almost identically excluding the main SELECT part.
The idea described below doesn't work.
--Parameter
Declare @.Count bit
SET @.Count = 1
SELECT
CASE
WHEN @.Count = 1
THEN pe.*, pn.*
ELSE COUNT(*)
END
...
FROM ...
INNER JOIN ... ON ...
WHERE ...
Any ideas?
Just D.Just D (no@.spam.please) writes:
> Can we do this trick and if yes then how? Just schematically: the SP
> should return the number of records if the parameter @.Count=1, if not,
> then the records themselves. The problem is that there is some
> complicated JOIN and the whole set of WHERE clauses that I wouldn't like
> to repeat in two different queries looking almost identically excluding
> the main SELECT part. The idea described below doesn't work.
The best is probably to put the whole JOIN-WHERE business in an
inline table-valued function. Then the procedure can read:
IF @.count = 1
SELECT COUNT(*) FROM tblfunc(@.par1, @.par2, ...)
ELSE
SELECT col1, col2, ...
FROM tblfunc (@.par1, @.par2, ...)
You could also bounce the data over a temp tble, but that would be more
expensive in terms of performance, not the least for the COUNT. (Since for
the COUNT(*) SQL Server may find a quicker query plan when it does not have
to read all data pages.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi
Using SELECT * in production code is not a good idea. The only way to do
what you are doing without writing the query twice would be to use dynamic
SQL.
John
"Just D" wrote:

> All,
> Can we do this trick and if yes then how? Just schematically: the SP shoul
d
> return the number of records if the parameter @.Count=1, if not, then the
> records themselves. The problem is that there is some complicated JOIN and
> the whole set of WHERE clauses that I wouldn't like to repeat in two
> different queries looking almost identically excluding the main SELECT par
t.
> The idea described below doesn't work.
> --Parameter
> Declare @.Count bit
> SET @.Count = 1
>
> SELECT
> CASE
> WHEN @.Count = 1
> THEN pe.*, pn.*
> ELSE COUNT(*)
> END
> ...
> FROM ...
> INNER JOIN ... ON ...
> WHERE ...
> Any ideas?
> Just D.
>
>|||Erland,
Correct me if I am wrong. I don't see any performance benifit by using the
table valued function over using the actual query, except for the fact that
the stored procedure looks better :)
The execution plan is not stored for the TVF but is stored in the calling
SP. And the plan will be recomplied everytime the condition changes. I would
say it would be better performance wise, if we have two stored procedures on
e
for returning the row count and one for returning the result set and call
these two SPs from the main SP based on the condition.So that only the main
SP will get recompiled and will not be much of an overhead.
If its a query with a simple execution plan, then what you suggest will be
fine.
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Erland Sommarskog" wrote:

> Just D (no@.spam.please) writes:
> The best is probably to put the whole JOIN-WHERE business in an
> inline table-valued function. Then the procedure can read:
> IF @.count = 1
> SELECT COUNT(*) FROM tblfunc(@.par1, @.par2, ...)
> ELSE
> SELECT col1, col2, ...
> FROM tblfunc (@.par1, @.par2, ...)
> You could also bounce the data over a temp tble, but that would be more
> expensive in terms of performance, not the least for the COUNT. (Since for
> the COUNT(*) SQL Server may find a quicker query plan when it does not hav
e
> to read all data pages.)
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||>> Just schematically: the SP should return the number of records [sic] if the parame
ter @.Count=1, if not, then the records [sic] themselves. <<
Did you ever have a software engineering course? Remember cohesion?
The idea that a properly designed code module will perform one
well-defined task. Good programmers do not write things that return
the square root of a number or translate Flemish depending on a
parameter.
Why did you make your low-level BIT flag a reserved word? Why are you
thinking in terms of assembly language style flags and variant records
instead of rows?
DECLARE @.Count bit
SET @.Count = 1
SELECT
CASE
WHEN @.Count = 1
THEN pe.*, pn.*
ELSE COUNT(*)
END
..
FROM ...
INNER JOIN ... ON ...
WHERE ... ; <<
CASE is an expression and not a control flow device. You can use an
IF-THEN-ELSE construct in T-SQL to mimic procedural coding with variant
records instead of using declarative coding.
Did you also notice that you want to return one column and then want to
return two columns? Arow in a relational table always has a fixed
number of columns, unlike records in a file. Basically, you are still
writing COBOL or some other procedural file-oriented language, but you
are doing it in SQL.
This is a simple matter of cut & paste, not the end of the world.
However, if you are just looking for a newsgroup kludge instead of a
real answer in one query, try:
SELECT
CASE WHEN @.assembly_language_flag = 1
THEN 'violated cohesion'
ELSE COUNT(*) END AS foobar,
CASE WHEN @.assembly_language_flag = 1
THEN PA.x
ELSE 'violated cohesion' END AS x,
etc.
FROM ..
Boy that is awful, isn't it?|||You're so kind as usual writing that in this style. :) Let me guess, you're
from the Western Ukraine, aren't you?
"--CELKO--" <jcelko212@.earthlink.net> wrote in message

> Did you ever have a software engineering course? Remember cohesion?
> The idea that a properly designed code module will perform one
> well-defined task. Good programmers do not write things that return
Tell that to the MS coders (mostly contractors from India:)) who were
usually adding 20 and more parameters like NULL (reserved) to the method
parameter list.overriding one method tons of times. That was always MS
style.

> the square root of a number or translate Flemish depending on a
> parameter.
Yea-yea, pretty close.
The flame is closed.|||Omnibuzz (Omnibuzz@.discussions.microsoft.com) writes:
> Correct me if I am wrong. I don't see any performance benifit by using
> the table valued function over using the actual query, except for the
> fact that the stored procedure looks better :)
Correct, but the presumption was that Just D wanted to the procedure
to look better. That is, he did not want repeat the conditions. And I can
think of four ways to achieve this aim:
1) view/inlined table function.
2) bounce over temp table.
3) dynamic SQL.
4) pre-processor.
In my post I only discussed the first two options, and of these the
TVF gives better performance than the temp table.
In my opinion, using dynamic SQL introduces another level of complexity
which is not worth the pain in this case.
And preprocessor? Well, we have one in our environment, but most
people doesn't.

> The execution plan is not stored for the TVF but is stored in the calling
> SP. And the plan will be recomplied everytime the condition changes.
As I understood it, the JOIN and WHERE conditions of the query are
stable. As for the condition on whether to return COUNT or result set,
that should lead to any recompilation.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland, thanks for your answer.
Yes, the main idea was to make the SP more flexible and maintainable. The
conditions are complex enough to repeat them more than one time, and that's
especially bad if we need to improve/modify them in future, we can easily
make a simple mistake doing that in two places, OR we will have to
copy/paste each time we need to change something. That's why this idea
appeared. But from another side any change like that should not seriously
affect the speed of the code or the whole complexity because having this
divantage we're getting into another trouble which is not acceptable.
That's why I asked this newsgroup for a new, better idea. To implement the
function - then we'll need to maintain this function and provide the
required set of tables and parameters that should be cached in a different
way I guess if we call the function inside our SP. Temporary table - it's
even the worst scenario. Many different ways are able to change the whole
idea and to do one thing crashing all around.
Just D.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97E0F409CDF8DYazorman@.127.0.0.1...
> Omnibuzz (Omnibuzz@.discussions.microsoft.com) writes:
> Correct, but the presumption was that Just D wanted to the procedure
> to look better. That is, he did not want repeat the conditions. And I can
> think of four ways to achieve this aim:
> 1) view/inlined table function.
> 2) bounce over temp table.
> 3) dynamic SQL.
> 4) pre-processor.
> In my post I only discussed the first two options, and of these the
> TVF gives better performance than the temp table.
> In my opinion, using dynamic SQL introduces another level of complexity
> which is not worth the pain in this case.
> And preprocessor? Well, we have one in our environment, but most
> people doesn't.
>
> As I understood it, the JOIN and WHERE conditions of the query are
> stable. As for the condition on whether to return COUNT or result set,
> that should lead to any recompilation.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Just D (no@.spam.please) writes:
> That's why I asked this newsgroup for a new, better idea. To implement the
> function - then we'll need to maintain this function and provide the
> required set of tables and parameters that should be cached in a different
> way I guess if we call the function inside our SP.
Not really sure what you mean here. An inline-table function does not have
any query plan of its own. An inline table function is really a macro that
the optimizer pastes in before building the query plan. (Note that this
does not apply to multi-statement functions nor to scalar functions.)
As for the maintenance, you would move that to the function. The procedure
would just be a wrapper on the function.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

invalied length parameter passed the substring function

Does anyone know what the above error message means?What length did you pass to substring?|||What do you mean, what length...|||From the SQL Server Books Online (a.k.a. BOL):

SUBSTRING
Returns part of a character, binary, text, or image expression. For more information about the valid Microsoft SQL Server data types that can be used with this function, see Data Types.

Syntax
SUBSTRING ( expression , start , length )

Arguments
....|||DECLARE @.x int, @.y int, @.z varchar(8000)

-- This works
SELECT @.x = 47, @.y = 22, @.z = 'Even if God Almighty, points his finger at us, It just Doesnt matter'
SELECT SUBSTRING(@.z,@.x,@.y)

-- This works in SQL Server...DB2's head would explode though since it's out of range
SELECT @.x = 47, @.y = 8000
SELECT SUBSTRING(@.z,@.x,@.y)

-- Same as the above...really doesn't make a whole lot of sense though
SELECT @.x = 47, @.y = 0
SELECT SUBSTRING(@.z,@.x,@.y)

-- So the Only thing that doesn't work is a negative...but the way sql works...probably could have allowed it
-- Would have been neat to go to the left in the string for negative nymbers
SELECT @.x = 47, @.y = -1
SELECT SUBSTRING(@.z,@.x,@.y)

EDIT: Oh, most likely you have code that's deriving the length..post the code and we can figure it out...

invalid reconciler parameter

First of all... thanks for a great and interesting product... this is really nice...

I'm working with a winform app and pushing it out via click-once deployment (using the bootstrapper for SQLCompact as well)... the SQL install is fine, however when my app attempts to do anything with the db, i get an error "invalid reconciler parameter" error... some searching seems to indicate that this could be coming from the path being longer than 128chars... howeve, using click once, i'm not certain I can control the deployment location... I'm certain that there is a work around, but not sure what it is... any guidance?

As a note, running the app on the same machine using the output from VS (not installed via ClickOnce but simply running hte exe) works fine...

Thanks,

Rob

Just to close the loop on this thread... SSCE RTM'd today and this problem does not repro in the RTM bits.

thanks.

|||

I am having exactly the same problem but don't understand your response ? I have installed the latest versions of SQL Compact Edition including SDK, Server Tools, VS Tools and Runtime and I'm still getting the "invalid reconciler parameter" using ClickOnce.

Please help.

|||Rob how did you resolve this issue ?

sql

invalid reconciler parameter

First of all... thanks for a great and interesting product... this is really nice...

I'm working with a winform app and pushing it out via click-once deployment (using the bootstrapper for SQLCompact as well)... the SQL install is fine, however when my app attempts to do anything with the db, i get an error "invalid reconciler parameter" error... some searching seems to indicate that this could be coming from the path being longer than 128chars... howeve, using click once, i'm not certain I can control the deployment location... I'm certain that there is a work around, but not sure what it is... any guidance?

As a note, running the app on the same machine using the output from VS (not installed via ClickOnce but simply running hte exe) works fine...

Thanks,

Rob

Just to close the loop on this thread... SSCE RTM'd today and this problem does not repro in the RTM bits.

thanks.

|||

I am having exactly the same problem but don't understand your response ? I have installed the latest versions of SQL Compact Edition including SDK, Server Tools, VS Tools and Runtime and I'm still getting the "invalid reconciler parameter" using ClickOnce.

Please help.

|||Rob how did you resolve this issue ?

INVALID LENGTH PARAMETER PASSED....

I have the follwoing stored procedure:

ALTER procedure [dbo].[up_GetExecutionContext](
@.ExecutionGUID int = null
) as
begin
set nocount on

declare@.s varchar(500)
declare @.i int

set @.s = ''
select @.s = @.s + EventType + ','-- Dynamically build the list of
events
from(
select distinct top 100 percent [event] as EventType
from dbo.PackageStep
where (@.ExecutionGUID is null or PackageStep.packagerunid =
@.ExecutionGUID)
order by 1
) as x

set @.i = len(@.s)
select case @.i
when 500 then left(@.s, @.i - 3) + '...'-- If string is too long then
terminate with '...'
else left(@.s, @.i - 1) -- else just remove the final comma
end as 'Context'

set nocount off
end --procedure
GO

When I run this and pass in a value of NULL, things work fine. When I
pass in an actual value (i.e. 15198), I get the following message:

Invalid length parameter passed to the SUBSTRING function.

There is no SUBSTRING being used anywhere in the query and the
datatypes look okay to me.

Any suggestions would be greatly appreciated.

Thanks!!On Jun 4, 12:43 pm, ansonee <anso...@.yahoo.comwrote:

Quote:

Originally Posted by

I have the follwoing stored procedure:
>
ALTER procedure [dbo].[up_GetExecutionContext](
@.ExecutionGUID int = null
) as
begin
set nocount on
>
declare @.s varchar(500)
declare @.i int
>
set @.s = ''
select @.s = @.s + EventType + ',' -- Dynamically build the list of
events
from(
select distinct top 100 percent [event] as EventType
from dbo.PackageStep
where (@.ExecutionGUID is null or PackageStep.packagerunid =
@.ExecutionGUID)
order by 1
) as x
>
set @.i = len(@.s)
select case @.i
when 500 then left(@.s, @.i - 3) + '...' -- If string is too long then
terminate with '...'
else left(@.s, @.i - 1) -- else just remove the final comma
end as 'Context'
>
set nocount off
end --procedure
GO
>
When I run this and pass in a value of NULL, things work fine. When I
pass in an actual value (i.e. 15198), I get the following message:
>
Invalid length parameter passed to the SUBSTRING function.
>
There is no SUBSTRING being used anywhere in the query and the
datatypes look okay to me.
>
Any suggestions would be greatly appreciated.
>
Thanks!!

increase the value of @.s from 500 to 5000 maybe and test it ?|||ansonee (ansonee@.yahoo.com) writes:

Quote:

Originally Posted by

set @.s = ''
select @.s = @.s + EventType + ',' -- Dynamically build the list of
events
from(
select distinct top 100 percent [event] as EventType
from dbo.PackageStep
where (@.ExecutionGUID is null or PackageStep.packagerunid =
@.ExecutionGUID)
order by 1
) as x

I'm afraid that this relies on undefined behaviour. It may produce what
you want today. It might not tomorrow. If you are on SQL 2000, you
will need to run a cursor. On SQL 2005 there exists an option with
XML. See SQL Server MVP Antith Sen's article on
http://www.projectdmx.com/tsql/rowconcatenate.aspx for more information.

Quote:

Originally Posted by

set @.i = len(@.s)
select case @.i
when 500 then left(@.s, @.i - 3) + '...' -- If string is too
long then
terminate with '...'
else left(@.s, @.i - 1) -- else just remove the final comma
end as 'Context'
>
set nocount off
end --procedure
GO
>
When I run this and pass in a value of NULL, things work fine. When I
pass in an actual value (i.e. 15198), I get the following message:
>
Invalid length parameter passed to the SUBSTRING function.
>
There is no SUBSTRING being used anywhere in the query

No, but there is LEFT, which is just a shortcut for SUBSTRING.

More to the point, you have failed to handle the case that the query
does not find any events, and @.i is the empty string.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Invalid length parameter passed to the substring function.

This is driving me absolutley crazy. This function returns ProductIDs stripped out from a memo field based on a check on Product Type (which is also stripped for the checking) and returns a list of ProductIDs based on the type I'm checking for, in other words the @.FileFormat

The error: Invalid length parameter passed to the substring function.

I am passing 2 types of ProductDescriptions to my function below. This function returns ProductIDs by Type, stripped out of a large varchar field. The problem I'm having is that I have 2 checks that check whether the ProductID is MP3 or WAV. The second check is failing.

The 2 types of possible incoming ProductDescriptions for example are:

'These fully-orchestrated royalty free music tracks invoke the spirit of some of the great themes from 1970"s and 1980"s television and film productions and offer majestic brass, string and guitar melodies that will make a memorable addition to projects as background music and production music.<br><br><span class="product-name-no-link">You can also purchase the individual tracks:</span><br><br>01. American Plains <a href="ProductInfo.aspx?ProductID=105234">MP3</a> | <a href="ProductInfo.aspx?ProductID=105235">WAV</a><br>02. Sultry Summer Night <a href="ProductInfo.aspx?ProductID=105236">MP3</a> | <a href="ProductInfo.aspx?ProductID=105237">WAV</a><br>03. Ocean Skyline <a href="ProductInfo.aspx?ProductID=105238">MP3</a> | <a href="ProductInfo.aspx?ProductID=105239">WAV</a><br>04. Wistful Lover <a href="ProductInfo.aspx?ProductID=105240">MP3</a> | <a href="ProductInfo.aspx?ProductID=105241">WAV</a><br>05. Final Choice <a href="ProductInfo.aspx?ProductID=105242">MP3</a> | <a href="ProductInfo.aspx?ProductID=105243">WAV</a><br>06. Fun and Free <a href="ProductInfo.aspx?ProductID=105244">MP3</a> | <a href="ProductInfo.aspx?ProductID=105245">WAV</a><br>07. Wayward Strangers <a href="ProductInfo.aspx?ProductID=105246">MP3</a> | <a href="ProductInfo.aspx?ProductID=105247">WAV</a><br>08. Savored Moments <a href="ProductInfo.aspx?ProductID=105248">MP3</a> | <a href="ProductInfo.aspx?ProductID=105249">WAV</a><br>09. Endless Searcher <a href="ProductInfo.aspx?ProductID=105250">MP3</a> | <a href="ProductInfo.aspx?ProductID=105251">WAV</a><br>10. Bach Piano <a href="ProductInfo.aspx?ProductID=105252">MP3</a> | <a href="ProductInfo.aspx?ProductID=105253">WAV</a><br>11. Fog Bound Mornings <a href="ProductInfo.aspx?ProductID=105254">MP3</a> | <a href="ProductInfo.aspx?ProductID=105255">WAV</a><br>'

OR

'Clapping percussion effects characterize this Hip Hop/Urban piece with train sound effects and strings.<br><br><b>Styles:</b> Dramatic,Reflective,Somber/dark<br><br><b>If you like this track, you can save more than <span style='color=#ff0000'>70%</span> by purchasing it on the following albums:</b><br><a href="ProductInfo.aspx?ProductID=106758">Hip-Hop / Urban</a><br><a href="ProductInfo.aspx?ProductID=106763">Documentary, Film, Television, General Production - Volume 2</a><br><br>Click <a href="ProductInfo.aspx?ProductID=105747">here</a> for the WAV version of this track.'

The current function I'm trying to fix:

ALTER FUNCTION [dbo].[GetProductChildIDs]

(

@.ProductDescription varchar(5500),

@.FileFormatvarchar(3)

)

RETURNS varchar(1000)

AS

BEGIN

Declare @.Keyword varchar(30),

@.KeywordLen tinyint,

@.ProductID int,

@.Pos smallint,

@.StartPos smallint,

@.EndPos smallint,

@.Result varchar(1000),

@.valid int

SET @.valid = 0

SET @.Keyword = 'ProductInfo.aspx?ProductID='

SET @.KeywordLen = LEN(@.Keyword)

SET @.Result = ''

SET @.Pos = 1

WHILE @.Pos > 0

BEGIN

SET @.Pos = CHARINDEX(@.Keyword, @.ProductDescription, @.Pos)

If @.Pos > 0

BEGIN

SET @.StartPos = @.Pos + @.KeywordLen

SET @.EndPos = CHARINDEX('"', @.ProductDescription, @.StartPos + 1)

IF SUBSTRING(@.ProductDescription, @.EndPos + 2, 3) = @.FileFormat

SET @.valid = 1

IF (len(@.ProductDescription) - @.StartPos) > 19

IF SUBSTRING(@.ProductDescription, @.EndPos + 19, 3) = @.FileFormat

SET @.valid = 1

IF @.valid = 1

BEGIN

SELECT @.Result = @.Result + SUBSTRING(@.ProductDescription, @.StartPos, @.EndPos - @.StartPos) + ','

END

SET @.Pos = @.EndPos + 1

END

END

RETURN SUBSTRING(@.Result,1,len(@.Result)-1)

END

The following checks to see whether the ProductID is WAV or MP3 by checking certain chars after each sequence 'ProductInfo.aspx?ProductID='

IF SUBSTRING(@.ProductDescription, @.EndPos + 2, 3) = @.FileFormat - checks if it's a 'WAV' or 'MP3' based on the first type of incoming ProductDescription passed to this function at any time

IF SUBSTRING(@.ProductDescription, @.EndPos + 19, 3) = @.FileFormat - checks to see if it's a 'WAV' or 'MP3' ProductID based on the second type of possible incoming ProductDescription.

I found out that the second check fails if there isn't 19 or more chars to check so I tried putting this in:

So I'm not sure how to handle the Nulls in the second IF statement if there are not enough chars to check in the loop for each SUBSTRING(@.ProductDescription, @.EndPos + 19, 3)

ALTER FUNCTION [dbo].[GetProductChildIDs]

(

@.ProductDescription varchar(5500),

@.FileFormatvarchar(3)

)

RETURNS varchar(1000)

AS

BEGIN

Declare @.Keyword varchar(30),

@.KeywordLen tinyint,

@.ProductID int,

@.Pos smallint,

@.StartPos smallint,

@.EndPos smallint,

@.Result varchar(1000),

@.valid int

SET @.valid = 0

SET @.Keyword = 'ProductInfo.aspx?ProductID='

SET @.KeywordLen = LEN(@.Keyword)

SET @.Result = ''

SET @.Pos = 1

WHILE @.Pos > 0

BEGIN

SET @.Pos = CHARINDEX(@.Keyword, @.ProductDescription, @.Pos)

If @.Pos > 0

BEGIN

SET @.StartPos = @.Pos + @.KeywordLen

SET @.EndPos = CHARINDEX('"', @.ProductDescription, @.StartPos + 1)

IF SUBSTRING(@.ProductDescription, @.EndPos + 2, 3) = @.FileFormat

SET @.valid = 1

IF (len(@.ProductDescription) - @.StartPos) > 19

IF SUBSTRING(@.ProductDescription, @.EndPos + 19, 3) = @.FileFormat

SET @.valid = 1

--IF @.valid = 1

BEGIN

SELECT @.Result = @.Result + SUBSTRING(@.ProductDescription, @.StartPos, @.EndPos - @.StartPos) + ','

END

SET @.Pos = @.EndPos + 1

END

END

RETURN SUBSTRING(@.Result,1,len(@.Result)-1)

END

|||

I since then have figured it out by extensive testing and added some stuff so that it will behave...but let me take a look at what you had to suggest also. Thanks for the suggestion though!

ALTER FUNCTION [dbo].[GetProductChildIDs]

(

@.ProductDescription varchar(5500),

@.FileFormat varchar(3)

)

RETURNS varchar(1000)

AS

BEGIN

Declare @.Keyword varchar(30),

@.KeywordLen tinyint,

@.ProductID int,

@.Pos smallint,

@.StartPos smallint,

@.EndPos smallint,

@.Result varchar(1000),

@.ResultToReturn varchar(1000),

@.valid int,

@.MP3Pos int,

@.WavPos int

SET @.valid = 0

SET @.Keyword = 'ProductInfo.aspx?ProductID='

SET @.KeywordLen = LEN(@.Keyword)

SET @.Result = ''

SET @.ResultToReturn = ''

SET @.Pos = 1

WHILE @.Pos > 0

BEGIN

SET @.Pos = CHARINDEX(@.Keyword, @.ProductDescription, @.Pos)

If @.Pos > 0

BEGIN

SET @.StartPos = @.Pos + @.KeywordLen

SET @.EndPos = CHARINDEX('"', @.ProductDescription, @.StartPos + 1)

IF SUBSTRING(@.ProductDescription, @.EndPos + 2, 3) = @.FileFormat

BEGIN

SET @.valid = 1

END

IF SUBSTRING(@.ProductDescription, @.EndPos + 19, 3) = @.FileFormat

BEGIN

SET @.valid = 1

END

IF @.valid = 1

BEGIN

SELECT @.Result = @.Result + SUBSTRING(@.ProductDescription, @.StartPos, @.EndPos - @.StartPos) + ','

END

SET @.Pos = @.EndPos + 1

END

END

IF len(@.Result) > 6

SET @.ResultToReturn = @.ResultToReturn + SUBSTRING(@.Result,1,len(@.Result)-1)

RETURN @.ResultToReturn

END

|||why did you comment out the If @.valid = 1?

Invalid length parameter passed to the SUBSTRING function

Hi all,

I am having a weird issue after we upgraded our DB server to SQL 2005.

I have a SP used to extract exchange rate, and a job calls this SP daily. This job worked fine on SQL 2000, and works very well in Management studio if I call this SP seperately, but failed in sql job in 2005.

The error statement pointed to:

select left(@.row, charindex(',', @.row)-1), REVERSE(left(@.reversedrow, charindex(',', @.reversedrow)-1))

The error message is:

Invalid length parameter passed to the SUBSTRING function.

Anyone knows what's the difference for LEFT function between sql 2000 and 2005?

Thanks

Bill

I would imagine CHARINDEX is either returning a NULL or a 0 (see below). It may be better storing the resulting of the CHARINDEX in a variable before running the query (if it is the same for all values), or testing the value returned before doing the left. Or use ISNULL if it is returning NULL. I could not find any documentation suggesting differences between the function in 2000 vs 2005. Has your database compatibility level changed?

Clarity Consulting (www.claritycon.com)

http://blogs.claritycon.com/blogs/the_englishman/default.aspx

Clarity Consulting (www.claritycon.com)

CHARINDEX link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_left_7910.asp

CHARINDEX ( expression1 , expression2 [ , start_location ] )

Arguments

expression1

Is an expression containing the sequence of characters to be found. expression1 is an expression of the short character data type category.

expression2

Is an expression, usually a column searched for the specified sequence. expression2 is of the character string data type category.

start_location

Is the character position to start searching for expression1 in expression2. If start_location is not given, is a negative number, or is zero, the search starts at the beginning of expression2.

Return Types

int

Remarks

If either expression1 or expression2 is of a Unicode data type (nvarchar or nchar) and the other is not, the other is converted to a Unicode data type.

If either expression1 or expression2 is NULL, CHARINDEX returns NULL when the database compatibility level is 70 or later. If the database compatibility level is 65 or earlier, CHARINDEX returns NULL only when both expression1 and expression2 are NULL.

If expression1 is not found within expression2, CHARINDEX returns 0.

|||

Hi Shughes,

Thanks for your response.

The problem was not caused by NULL or 0. I set up trace and found that it actually caused by another statement.

-- select @.pos = charindex('United States Dollar', @.sourcedoc)

-- select @.len = len(@.sourcedoc) - @.pos

select @.doc = substring(@.sourcedoc, @.pos, @.len)

-- exec spReportSQLError 'Tracing', @.doc

It seems this statement will generate different result running in SQL job or in management studio.

Here is the trace results:

Error Details:(from SQL Job: wrong data)

-

Error Date: Feb 6 2006 12:43PM

Error Number: 0

Error Severity: 0

Error State: 0

Error Procedure: None

Error Line: 0

Error Message: No details info.

Other Info: # The daily noon exchange rates for major foreign currencies are published every business day at about 1 # p.m. EST. They are obtained from market or official sources around noon, and show the rates for the # various currencies in Canadian dollars converted from US dollars. The rates are nominal quotations - # neither buying nor selling rates - and are intended for statistical or analytical purposes. Rates # available from financial institutions will differ.

#

Date (<m>/<d>/<year>),01/27/2006,01/30/

Error Details(Run in management studio(Correct data)):

Error Date: Feb 6 2006 12:43PM

Error Number: 0

Error Severity: 0

Error State: 0

Error Procedure: None

Error Line: 0

Error Message: No details info.

Other Info: United States Dollar,1.1474,1.1443,1.1439,1.1402,1.1432,1.1471,1.1457

Argentine Peso (Floating Rate),0.3733,0.3729,0.3725,0.3712,0.3716,0.3729,0.3727

Australian Dollar,0.8624,0.8577,0.8660,0.8598,0.8628,0.8591,0.8507

Bahamian Dollar,1.1474,1.1443,1.1439,1.1402,1.1432,1.1471,1.1457

Brazilian Real,0.5201,0.5181,0.5174,0.5138,0.5132,0.5171,0.5242

Chilean Peso,0.002178,0.002183,0.002173,0.002158,0.002156,0.002171,0.002183

Chinese Renminbi,0.1423,0.1419,0.1419,0.1414,0.1418,0.1423,0.1422

Colombian Peso,0.000506,0.000505,0.000504,0.000503,0.000505,0.000507,0.000507

Croatian Kuna,0.1893,0.1882,0.1895,0.1880,0.1886,0.1883,0.1870

Czech. Republic Koruna,0.04920,0.04875,0.04907,0.04843,0.04849,0.04848,0.04844

Danish Krone,0.1865,0.1854,0.1863,0.1847,0.1853,0.1847,0.1836

East Caribbean Dollar,0.4265,0.4253,0.4252,0.4238,0.4249,0.4264,0.4259

European EURO,1.3919,1.3836,1.3906,1.3786,1.3833,1.3786,1.3713

Fiji Dollar,0.6689,0.6676,0.6640,0.6647,0.6643,0.6688,0.6687

African Financial Community Franc (CFA),0.002122,0.002109,0.002120,0.002102,0.002109,0.002102,0.002091

Pacific Financial Community Franc (CFP),0.01166,0.01159,0.01165,0.01155,0.01159,0.01155,0.01149

Ghanaian Cedi,0.000126,0.000126,0.000126,0.000125,0.000125,0.000126,0.000126

Guatemala Quetzal,0.15058,0.15017,0.15036,0.14988,0.15027,0.15079,0.15060

Honduran Lempira,0.06073,0.06056,0.06054,0.06034,0.06050,0.06071,0.06064

Hong Kong Dollar,0.147927,0.147512,0.147461,0.146989,0.147382,0.147865,0.147674

Hungarian Forint,0.005534,0.005495,0.005526,0.005479,0.005512,0.005502,0.005479

Icelandic Krona,0.01851,0.01840,0.01833,0.01806,0.01812,0.01816,0.01816

Indian Rupee,0.02607,0.02598,0.02602,0.02583,0.02588,0.02598,0.02595

Indonesian Rupiah,0.000122,0.000122,0.000122,0.000122,0.000122,0.000123,0.000124

Israeli New Shekel,0.2476,0.2463,0.2450,0.2443,0.2439,0.2441,0.2435

Jamaican dollar,0.01784,0.01780,0.01781,0.01869,0.01874,0.01783,0.01780

Japanese Yen,0.009793,0.009735,0.009785,0.009674,0.009665,0.009643,0.009632

Malaysian Ringgit,0.3059,0.3051,0.3050,0.3040,0.3046,0.3063,0.3064

Mexican Peso,0.1097,0.1096,0.1095,0.1093,0.1090,0.1093,0.1095

Moroccan Dirham,0.1273,0.1267,0.1271,0.1259,0.1265,0.1265,0.1259

Myanmar (Burma) Kyat,0.1954,0.1942,0.1943,0.1937,0.1937,0.1944,0.1934

Neth. Antilles Guilder,0.6446,0.6429,0.6426,0.6406,0.6422,0.6444,0.6437

New Zealand Dollar,0.7837,0.7802,0.7840,0.7816,0.7879,0.7878,0.7805

Norwegian Krona,0.1722,0.1700,0.1719,0.1707,0.1721,0.1714,0.1704

Pakistan Rupee,0.01917,0.01907,0.01911,0.01904,0.01909,0.01917,0.01914

Panamanian Balboa,1.1474,1.1443,1.1439,1.1402,1.1432,1.1471,1.1457

Peruvian New Sol,0.3460,0.3458,0.3450,0.3449,0.3452,0.3479,0.3484

Philippine Peso,0.02187,0.02181,0.02193,0.02190,0.02196,0.02208,0.02214

Polish Zloty,0.3641,0.3623,0.3641,0.3609,0.3615,0.3605,0.3590

Russian Rouble,0.04098,0.04066,0.04068,0.04051,0.04063,0.04062,0.04056

Singapore Dollar,0.7060,0.7019,0.7050,0.6998,0.7001,0.7015,0.7038

Slovak Koruna,0.03725,0.03706,0.03721,0.03696,0.03702,0.03693,0.03671

Slovenian Tolar,0.005809,0.005778,0.005805,0.005754,0.005775,0.005760,0.005727

South African Rand,0.1867,0.1862,0.1879,0.1864,0.1878,0.1880,0.1874

South Korean Won,0.001182,0.001179,0.001190,0.001185,0.001176,0.001182,0.001190

Sri Lanka Rupee,0.01123,0.01120,0.01120,0.01117,0.01119,0.01123,0.01123

Swedish Krona,0.1507,0.1498,0.1504,0.1491,0.1489,0.1487,0.1474

Swiss Franc,0.8967,0.8894,0.8945,0.8878,0.8900,0.8861,0.8806

Taiwanese New Dollar,0.03588,0.03578,0.03577,0.03565,0.03575,0.03574,0.03570

Thai Baht,0.02941,0.02926,0.02940,0.02904,0.02903,0.02910,0.02906

Trinidad & Tobago Dollar,0.1833,0.1831,0.1845,0.1823,0.1823,0.1840,0.1835

Tunisian Dinar,0.8578,0.8524,0.8570,0.8488,0.8510,0.8500,0.8468

New Turkish Lira,0.8647,0.8643,0.8651,0.8612,0.8634,0.8664,0.8624

Pound Sterling,2.0342,2.0240,2.0377,2.0269,2.0357,2.0213,2.0006

Venezuelan Bolivar,0.000534,0.000533,0.000533,0.000531,0.000532,0.000534,0.000534

I am still trying to figure out how this was caused.

Thanks

Bill

|||? Hi Bill, It's hard to know without knowing the complete code and the data it operates on. But I suspect that this was a potential bug in your code all along, and you just were lucky until now. The SQL Server query optimizer is free to reorganize your query and evaulate it in any order it wants. That can give you great performance benefits - but it may also cause unexpected bugs. I'll illustrate this with the example you posted in your first post (yes, I did see the follow-up, but this code takes a lot less typing <g>). Have a look at this query: SELECT LEFT(MyColumn, CHARINDEX(',', MyColumn) - 1) FROM MyTable WHERE CHARINDEX(',', MyColumn) > 0 You might be inclined to say that this is safe - after all, the WHERE will exclude all rows without a comma, and the LEFT function will evaluate fine for the remaining rows. Right? WRONG!!!! The optimizer is free to evaluate the query in any order it sees fit. So it might decide to do the SELECT first, then use the WHERE to filter the results. And BOOM!! you get an error for the first row with no comma in MyColumn. What might have happened is that in your real query, which is probably more complex than the example above, a new optimizing technique (that was not available to the SQL Server 2000 query optimizer) was chosen to evaluate your query, leading to this result. -- Hugo Kornelis, SQL Server MVP <Bill YU@.discussions.microsoft.com> schreef in bericht news:7d07a949-c4d2-4fa9-af97-bad706de680d@.discussions.microsoft.com... Hi all, I am having a weird issue after we upgraded our DB server to SQL 2005. I have a SP used to extract exchange rate, and a job calls this SP daily. This job worked fine on SQL 2000, and works very well in Management studio if I call this SP seperately, but failed in sql job in 2005. The error statement pointed to: select left(@.row, charindex(',', @.row)-1), REVERSE(left(@.reversedrow, charindex(',', @.reversedrow)-1)) The error message is: Invalid length parameter passed to the SUBSTRING function. Anyone knows what's the difference for LEFT function between sql 2000 and 2005? Thanks Bill|||

Hi Hugo,

Here are my 3 SPs:

CREATE PROCEDURE uspGetXMLFromHTTP (@.URL varchar(255), @.Method varchar(20)='GET')
AS
BEGIN
set nocount on
declare @.objRef int,@.resultcode int
exec @.resultcode = sp_OACreate 'Msxml2.XMLHTTP.4.0', @.objRef OUT
if @.resultcode = 0
begin
exec @.resultcode = sp_OAMethod @.objRef, 'Open', NULL,@.Method, @.URL, False
exec @.resultcode = sp_OAMethod @.objRef, 'Send',null
execute sp_OAGetProperty @.objRef, 'responseText'
end
exec sp_OADestroy @.objRef
END
GO

CREATE PROCEDURE uspReportSQLError(@.Location Varchar(250) = null, @.TraceInfo varchar(MAX) = null)
AS
BEGIN
set nocount on
declare @.cc varchar(250), @.bcc varchar(250)
declare @.errormsg varchar(Max), @.subject varchar(250)
select @.cc = '',
@.bcc = '',
@.subject = 'SQL Error: Server > [' + @.@.servername + '] > Database > [' + DB_NAME() + ']'
+ isnull((' > Location > [' + isnull(@.Location, ERROR_PROCEDURE()) + ']'), '')

select @.errormsg = 'Error Details:' + char(13) + char(10)
+ '-' + char(13) + char(10)
+ ' Error Date: ' + cast(getdate() as varchar) + char(13) + char(10)
+ ' Error Number: ' + cast(isnull(ERROR_NUMBER(), 0) as varchar) + char(13) + char(10)
+ ' Error Severity: ' + cast(isnull(ERROR_SEVERITY(), 0) as varchar) + char(13) + char(10)
+ ' Error State: ' + cast(isnull(ERROR_STATE(), 0) as varchar) + char(13) + char(10)
+ 'Error Procedure: ' + isnull(ERROR_PROCEDURE(), 'None') + char(13) + char(10)
+ ' Error Line: ' + cast(isnull(ERROR_LINE(), 0) as varchar) + char(13) + char(10)
+ ' Error Message: ' + isnull(ERROR_MESSAGE(), 'No details info.') + char(13) + char(10) + char(13) + char(10)
+ isnull((' Other Info: ' + @.TraceInfo), '')
-- Insert central log table

exec msdb.dbo.sp_send_dbmail @.profile_name = 'SQLError',
@.recipients = 'dba@.builddirect.com',
@.copy_recipients = @.cc,
@.blind_copy_recipients = @.bcc,
@.body = @.errormsg,
@.subject = @.subject,
@.importance = 'High',
@.body_format = 'Text'
set nocount off
END
GO

CREATE PROCEDURE uspExtractExchangeRate
AS
BEGIN
set nocount on
declare @.currency varchar(100), @.rate decimal(12,6), @.pos int, @.len int
declare @.doc varchar(Max), @.row varchar(255), @.reversedrow varchar(255), @.i int, @.j int
declare @.table table(Currency varchar(100), Rate decimal(12,6))
create table #TodayExchangeRate(response varchar(MAX))
begin try
insert #TodayExchangeRate
exec uspGetXMLFromHTTP 'http://www.bankofcanada.ca/en/financial_markets/csv/exchange_eng.csv'
select @.doc = response from #TodayExchangeRate
drop table #TodayExchangeRate

select @.pos = charindex('United States Dollar', @.doc)
select @.len = len(@.doc) - @.pos
select @.doc = substring(@.doc, @.pos, @.len)

--exec uspReportSQLError 'Tracing', @.doc

select @.i = 1, @.j = -1
while (1=1)
begin
select @.j = charindex((char(13) + char(10)), @.doc, @.j + 2)
if @.j = 0 break
select @.row = substring(@.doc, @.i, @.j - @.i)
select @.reversedrow = REVERSE(@.row)
select @.currency = left(@.row, charindex(',', @.row)-1)
select @.rate = REVERSE(left(@.reversedrow, charindex(',', @.reversedrow)-1))
insert @.table(Currency, Rate)
select @.currency, @.rate
select @.i = @.j + 2
end
end try
begin catch
--exec uspReportSQLError
end catch

-- save into exchangeRates table
set nocount off
END
GO

Except resetting @.profile_name in [uspExtractExchangeRate], these SPs are functional.

Hope this help.

Thanks

Bill

|||? Hi Bill, This'll be hard to troubleshoot, since I have no idea what xp_OACreate 'Msxml2.XMLHTTP.4.0' does. And I don't have SQL Server 2005, so I can't run this. However, I do have a trouble-shooting suggestion: add some PRINT (or SELECT) statements to your main procedure to see what happens, what exact data is being returned from the calls to sp_OAMethod and what steps are taken during the string parsing process. Something like this: CREATE PROCEDURE uspExtractExchangeRateASBEGINset nocount ondeclare @.currency varchar(100), @.rate decimal(12,6), @.pos int, @.len intdeclare @.doc varchar(8000), @.row varchar(255), @.reversedrow varchar(255), @.i int, @.j intdeclare @.table table(Currency varchar(100), Rate decimal(12,6))create table #TodayExchangeRate(response varchar(8000))begin tryinsert #TodayExchangeRateexec uspGetXMLFromHTTP 'http://www.bankofcanada.ca/en/financial_markets/csv/exchange_eng.csv' select @.doc = response from #TodayExchangeRate SELECT @.doc AS 'After uspGetXMLFromHTTP'drop table #TodayExchangeRate select @.pos = charindex('United States Dollar', @.doc)select @.len = len(@.doc) - @.posselect @.doc = substring(@.doc, @.pos, @.len)SELECT @.doc AS 'After stripping'--exec uspReportSQLError 'Tracing', @.doc select @.i = 1, @.j = -1while (1=1)beginselect @.j = charindex((char(13) + char(10)), @.doc, @.j + 2)if @.j = 0 breakselect @.row = substring(@.doc, @.i, @.j - @.i)select @.reversedrow = REVERSE(@.row) SELECT @.i, @.j, @.row, @.reversedrow select @.currency = left(@.row, charindex(',', @.row)-1)select @.rate = REVERSE(left(@.reversedrow, charindex(',', @.reversedrow)-1)) SELECT @.currency, @.rateinsert @.table(Currency, Rate)select @.currency, @.rateselect @.i = @.j + 2endSELECT @.i, @.j, 'Completely done' end trybegin catch--exec uspReportSQLErrorend catch -- save into exchangeRates tableset nocount offENDGO Checking the output of this debug-enabled version of the proc might reveal what's going on. -- Hugo Kornelis, SQL Server MVP -- Original Message -- From: Bill YU@.discussions.microsoft.com Newsgroups: microsoft.private.forums.msdn.sqlserver.tsql Sent: Monday, February 06, 2006 11:59 PM Subject: Re: Invalid length parameter passed to the SUBSTRING function Hi Hugo, Here are my 3 SPs: CREATE PROCEDURE uspGetXMLFromHTTP (@.URL varchar(255), @.Method varchar(20)='GET')ASBEGINset nocount ondeclare @.objRef int,@.resultcode intexec @.resultcode = sp_OACreate 'Msxml2.XMLHTTP.4.0', @.objRef OUT if @.resultcode = 0beginexec @.resultcode = sp_OAMethod @.objRef, 'Open', NULL,@.Method, @.URL, False exec @.resultcode = sp_OAMethod @.objRef, 'Send',nullexecute sp_OAGetProperty @.objRef, 'responseText'endexec sp_OADestroy @.objRefENDGO CREATE PROCEDURE uspReportSQLError(@.Location Varchar(250) = null, @.TraceInfo varchar(MAX) = null)ASBEGINset nocount ondeclare @.cc varchar(250), @.bcc varchar(250)declare @.errormsg varchar(Max), @.subject varchar(250)select @.cc = '',@.bcc = '',@.subject = 'SQL Error: Server > [' + @.@.servername + '] > Database > [' + DB_NAME() + ']'+ isnull((' > Location > [' + isnull(@.Location, ERROR_PROCEDURE()) + ']'), '')select @.errormsg = 'Error Details:' + char(13) + char(10)+ '-' + char(13) + char(10)+ ' Error Date: ' + cast(getdate() as varchar) + char(13) + char(10) + ' Error Number: ' + cast(isnull(ERROR_NUMBER(), 0) as varchar) + char(13) + char(10)+ ' Error Severity: ' + cast(isnull(ERROR_SEVERITY(), 0) as varchar) + char(13) + char(10)+ ' Error State: ' + cast(isnull(ERROR_STATE(), 0) as varchar) + char(13) + char(10)+ 'Error Procedure: ' + isnull(ERROR_PROCEDURE(), 'None') + char(13) + char(10)+ ' Error Line: ' + cast(isnull(ERROR_LINE(), 0) as varchar) + char(13) + char(10)+ ' Error Message: ' + isnull(ERROR_MESSAGE(), 'No details info.') + char(13) + char(10) + char(13) + char(10)+ isnull((' Other Info: ' + @.TraceInfo), '')-- Insert central log table exec msdb.dbo.sp_send_dbmail @.profile_name = 'SQLError',@.recipients = 'dba@.builddirect.com',@.copy_recipients = @.cc,@.blind_copy_recipients = @.bcc,@.body = @.errormsg,@.subject = @.subject,@.importance = 'High',@.body_format = 'Text' set nocount offENDGO CREATE PROCEDURE uspExtractExchangeRateASBEGINset nocount ondeclare @.currency varchar(100), @.rate decimal(12,6), @.pos int, @.len intdeclare @.doc varchar(Max), @.row varchar(255), @.reversedrow varchar(255), @.i int, @.j intdeclare @.table table(Currency varchar(100), Rate decimal(12,6))create table #TodayExchangeRate(response varchar(MAX))begin tryinsert #TodayExchangeRateexec uspGetXMLFromHTTP 'http://www.bankofcanada.ca/en/financial_markets/csv/exchange_eng.csv' select @.doc = response from #TodayExchangeRatedrop table #TodayExchangeRateselect @.pos = charindex('United States Dollar', @.doc)select @.len = len(@.doc) - @.posselect @.doc = substring(@.doc, @.pos, @.len) --exec uspReportSQLError 'Tracing', @.doc select @.i = 1, @.j = -1while (1=1)beginselect @.j = charindex((char(13) + char(10)), @.doc, @.j + 2)if @.j = 0 breakselect @.row = substring(@.doc, @.i, @.j - @.i)select @.reversedrow = REVERSE(@.row) select @.currency = left(@.row, charindex(',', @.row)-1)select @.rate = REVERSE(left(@.reversedrow, charindex(',', @.reversedrow)-1))insert @.table(Currency, Rate)select @.currency, @.rateselect @.i = @.j + 2endend trybegin catch--exec uspReportSQLErrorend catch -- save into exchangeRates tableset nocount offENDGO Except resetting @.profile_name in [uspExtractExchangeRate], these SPs are functional. Hope this help. Thanks Bill <Bill YU@.discussions.microsoft.com> schreef in bericht news:3285cccb-7a6a-4451-b618-a8d90cdf19c1@.discussions.microsoft.com... Hi Hugo, Here are my 3 SPs: CREATE PROCEDURE uspGetXMLFromHTTP (@.URL varchar(255), @.Method varchar(20)='GET')ASBEGINset nocount ondeclare @.objRef int,@.resultcode intexec @.resultcode = sp_OACreate 'Msxml2.XMLHTTP.4.0', @.objRef OUT if @.resultcode = 0beginexec @.resultcode = sp_OAMethod @.objRef, 'Open', NULL,@.Method, @.URL, False exec @.resultcode = sp_OAMethod @.objRef, 'Send',nullexecute sp_OAGetProperty @.objRef, 'responseText'endexec sp_OADestroy @.objRefENDGO CREATE PROCEDURE uspReportSQLError(@.Location Varchar(250) = null, @.TraceInfo varchar(MAX) = null)ASBEGINset nocount ondeclare @.cc varchar(250), @.bcc varchar(250)declare @.errormsg varchar(Max), @.subject varchar(250)select @.cc = '',@.bcc = '',@.subject = 'SQL Error: Server > [' + @.@.servername + '] > Database > [' + DB_NAME() + ']'+ isnull((' > Location > [' + isnull(@.Location, ERROR_PROCEDURE()) + ']'), '')select @.errormsg = 'Error Details:' + char(13) + char(10)+ '-' + char(13) + char(10)+ ' Error Date: ' + cast(getdate() as varchar) + char(13) + char(10) + ' Error Number: ' + cast(isnull(ERROR_NUMBER(), 0) as varchar) + char(13) + char(10)+ ' Error Severity: ' + cast(isnull(ERROR_SEVERITY(), 0) as varchar) + char(13) + char(10)+ ' Error State: ' + cast(isnull(ERROR_STATE(), 0) as varchar) + char(13) + char(10)+ 'Error Procedure: ' + isnull(ERROR_PROCEDURE(), 'None') + char(13) + char(10)+ ' Error Line: ' + cast(isnull(ERROR_LINE(), 0) as varchar) + char(13) + char(10)+ ' Error Message: ' + isnull(ERROR_MESSAGE(), 'No details info.') + char(13) + char(10) + char(13) + char(10)+ isnull((' Other Info: ' + @.TraceInfo), '')-- Insert central log table exec msdb.dbo.sp_send_dbmail @.profile_name = 'SQLError',@.recipients = 'dba@.builddirect.com',@.copy_recipients = @.cc,@.blind_copy_recipients = @.bcc,@.body = @.errormsg,@.subject = @.subject,@.importance = 'High',@.body_format = 'Text' set nocount offENDGO CREATE PROCEDURE uspExtractExchangeRateASBEGINset nocount ondeclare @.currency varchar(100), @.rate decimal(12,6), @.pos int, @.len intdeclare @.doc varchar(Max), @.row varchar(255), @.reversedrow varchar(255), @.i int, @.j intdeclare @.table table(Currency varchar(100), Rate decimal(12,6))create table #TodayExchangeRate(response varchar(MAX))begin tryinsert #TodayExchangeRateexec uspGetXMLFromHTTP 'http://www.bankofcanada.ca/en/financial_markets/csv/exchange_eng.csv' select @.doc = response from #TodayExchangeRatedrop table #TodayExchangeRateselect @.pos = charindex('United States Dollar', @.doc)select @.len = len(@.doc) - @.posselect @.doc = substring(@.doc, @.pos, @.len) --exec uspReportSQLError 'Tracing', @.doc select @.i = 1, @.j = -1while (1=1)beginselect @.j = charindex((char(13) + char(10)), @.doc, @.j + 2)if @.j = 0 breakselect @.row = substring(@.doc, @.i, @.j - @.i)select @.reversedrow = REVERSE(@.row) select @.currency = left(@.row, charindex(',', @.row)-1)select @.rate = REVERSE(left(@.reversedrow, charindex(',', @.reversedrow)-1))insert @.table(Currency, Rate)select @.currency, @.rateselect @.i = @.j + 2endend trybegin catch--exec uspReportSQLErrorend catch -- save into exchangeRates tableset nocount offENDGO Except resetting @.profile_name in [uspExtractExchangeRate], these SPs are functional. Hope this help. Thanks Bill|||

Why are you using SQL to parse the comma-separated string? This is much more easier to do on the client. You can do any of the following:

1. You can replace the SP with a DTS or SSIS package that imports the CSV information to a table

2. Or you can take the CSV file and import using BCP

3. Or use an ActiveX task from SQLAgent

Any of these approaches will be much more robust and simpler. OLE automation SPs should generally be avoided due to their overhead in using on the server and since you are running this from a job anyway it is better to isolate the process from server.

Btw, the error is probably due to bad data or incorrectly formed row.

|||

Thanks Hugo.

Bill

|||

Hi Umachandar,

This is a legacy task, I run this job from Internal DB server not production, and I did not want to develop and maintain any codes other than sql scripts.

I just happened to have this problem, seems SQL 2005 has a different behavior here.

Thanks

Bill

|||You will have to post a repro script to determine if this is a bug in SQL Server 2005 or not. Otherwise it is hard to tell by just looking at the code since this can be due to bad input. Does the same code run fine in SQL Server 2000 with the same input?|||

Yes,

I run this job for more than two years, never had a problem.

The issue happened after we upgraded to SQL 2005.

Interestingly, if I assign the CSV text to a variable from TEMP table, it works fine.

Now, I have to manually execute the same SP from Management Studio daily.

Bill

Invalid Dataset Referenced Field?

here's my mdx code for my parameter (CrashVehicleVehicleType)

WITH MEMBER [Measures].[ParameterCaption] AS '[Crash Vehicle].[Vehicle Type].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Crash Vehicle].[Vehicle Type].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Crash Vehicle].[Vehicle Type].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Crash Vehicle].[Vehicle Type].ALLMEMBERS ON ROWS FROM [SDMTest]

why do i keep getting this error if i have a parameter value and caption

[rsInvalidDataSetReferenceField] The report parameter ‘CrashVehicleVehicleType’ uses the field ‘ParameterValue’ in a data set reference, but the data set ‘CrashVehicleVehicleType’ does not contain that field.

Check the XML Code behind page of each report.

http://jhermiz.googlepages.com|||

this is the xml behind the report im working on.....It says the value and caption is in these datasets?! i just dont get it.

</DataSet>

<DataSet Name="FromDateCalendarDate">

<Query>

<rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate>

<CommandText>WITH MEMBER [Measures].[ParameterCaption] AS '[Date].[Calendar Date].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Date].[Calendar Date].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Date].[Calendar Date].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Date].[Calendar Date].ALLMEMBERS ON ROWS FROM [SDMTest]</CommandText>

<DataSourceName>NewCrashTest</DataSourceName>

<rd:AutoGenerated>true</rd:AutoGenerated>

<rd:MdxQuery><QueryDefinition xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/AnalysisServices/QueryDefinition"><CommandType>MDX</CommandType><Type>Query</Type><QuerySpecification xsi:type="MDXQuerySpecification"><Select><Items /></Select><From>SDMTest</From><Filter><FilterItems /></Filter><Calculations /><Aggregates /><QueryProperties /></QuerySpecification><Query><Statement>WITH MEMBER [Measures].[ParameterCaption] AS '[Date].[Calendar Date].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Date].[Calendar Date].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Date].[Calendar Date].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Date].[Calendar Date].ALLMEMBERS ON ROWS FROM [SDMTest]</Statement><ParameterDefinitions /></Query></QueryDefinition></rd:MdxQuery>

<rd:Hidden>true</rd:Hidden>

</Query>

</DataSet>

<DataSet Name="ToDateCalendarDate">

<Query>

<CommandText>WITH MEMBER [Measures].[ParameterCaption] AS '[Date].[Calendar Date].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Date].[Calendar Date].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Date].[Calendar Date].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Date].[Calendar Date].ALLMEMBERS ON ROWS FROM [SDMTest]</CommandText>

<DataSourceName>NewCrashTest</DataSourceName>

<rd:AutoGenerated>true</rd:AutoGenerated>

<rd:MdxQuery><QueryDefinition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/AnalysisServices/QueryDefinition"><CommandType>MDX</CommandType><Type>Query</Type><QuerySpecification xsi:type="MDXQuerySpecification"><Select><Items /></Select><From>SDMTest</From></QuerySpecification><Query><Statement>WITH MEMBER [Measures].[ParameterCaption] AS '[Date].[Calendar Date].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Date].[Calendar Date].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Date].[Calendar Date].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Date].[Calendar Date].ALLMEMBERS ON ROWS FROM [SDMTest]</Statement></Query></QueryDefinition></rd:MdxQuery>

<rd:Hidden>true</rd:Hidden>

</Query>

<Fields>

Invalid Data Type error with Subreports

I have a report with two subreports. The parameter connecting the report with the subreports is based on a field with a datatype of UniqueIdentifier.

If I run each of the subreports by themselves, entering in value for the parameter, they run fine.

When I try to run the master report, it runs, but the subreport sections contain the following text instead of the correct output: "Error: Subreport could not be shown." The Output window shows 2 warning, one for each subreport:

The expression use in subreport 'subreport1' returned a data type that is not valid.

I don't know for a fact that it is the parameter causing the problem, because the error message has a blank for the expression that is having the problem.

Each subreport is a simple table with one column containing string fields from a simple query.

Anyone have any ideas?It sounds like you are trying to pass GUIDs as parameters to the subreport.
On the subreport parameters dialog, use the CStr(...) function to convert the Guids to strings. E.g. =CStr(Fields!GuidColumn.Value)

-- Robert|||Guys:

I am having the same problem. I tried the CStr() function on the parameters to the SubReport, but had the same result. Any more ideas?

Thanks,
Mike|||Whoops! I am getting a different error when using the CStr() function:

Cast from type 'Guid' to type 'String' is not valid.

|||

I think I originally tried Robert's suggestion and was still giving an error like you found.

What I ended up doing was going into the queries for both the master and sub report and using "(CAST ScriptID AS varchar(255))" in the queries themselves. Then, these string fields linked up without a problem.

Gregg

|||Sorry, I meant using =Fields!GuidColum.Value.ToString() to convert it into a string. Anyway, the CAST in the query is also fine.

-- Robert

Invalid Data Type error with Subreports

I have a report with two subreports. The parameter connecting the report with the subreports is based on a field with a datatype of UniqueIdentifier.

If I run each of the subreports by themselves, entering in value for the parameter, they run fine.

When I try to run the master report, it runs, but the subreport sections contain the following text instead of the correct output: "Error: Subreport could not be shown." The Output window shows 2 warning, one for each subreport:

The expression use in subreport 'subreport1' returned a data type that is not valid.

I don't know for a fact that it is the parameter causing the problem, because the error message has a blank for the expression that is having the problem.

Each subreport is a simple table with one column containing string fields from a simple query.

Anyone have any ideas?It sounds like you are trying to pass GUIDs as parameters to the subreport.
On the subreport parameters dialog, use the CStr(...) function to convert the Guids to strings. E.g. =CStr(Fields!GuidColumn.Value)

-- Robert|||Guys:

I am having the same problem. I tried the CStr() function on the parameters to the SubReport, but had the same result. Any more ideas?

Thanks,
Mike|||Whoops! I am getting a different error when using the CStr() function:

Cast from type 'Guid' to type 'String' is not valid.

|||

I think I originally tried Robert's suggestion and was still giving an error like you found.

What I ended up doing was going into the queries for both the master and sub report and using "(CAST ScriptID AS varchar(255))" in the queries themselves. Then, these string fields linked up without a problem.

Gregg

|||Sorry, I meant using =Fields!GuidColum.Value.ToString() to convert it into a string. Anyway, the CAST in the query is also fine.

-- Robert

Invalid Data Type error with Subreports

I have a report with two subreports. The parameter connecting the report with the subreports is based on a field with a datatype of UniqueIdentifier.

If I run each of the subreports by themselves, entering in value for the parameter, they run fine.

When I try to run the master report, it runs, but the subreport sections contain the following text instead of the correct output: "Error: Subreport could not be shown." The Output window shows 2 warning, one for each subreport:

The expression use in subreport 'subreport1' returned a data type that is not valid.

I don't know for a fact that it is the parameter causing the problem, because the error message has a blank for the expression that is having the problem.

Each subreport is a simple table with one column containing string fields from a simple query.

Anyone have any ideas?It sounds like you are trying to pass GUIDs as parameters to the subreport.
On the subreport parameters dialog, use the CStr(...) function to convert the Guids to strings. E.g. =CStr(Fields!GuidColumn.Value)

-- Robert|||Guys:

I am having the same problem. I tried the CStr() function on the parameters to the SubReport, but had the same result. Any more ideas?

Thanks,
Mike|||Whoops! I am getting a different error when using the CStr() function:

Cast from type 'Guid' to type 'String' is not valid.

|||

I think I originally tried Robert's suggestion and was still giving an error like you found.

What I ended up doing was going into the queries for both the master and sub report and using "(CAST ScriptID AS varchar(255))" in the queries themselves. Then, these string fields linked up without a problem.

Gregg

|||Sorry, I meant using =Fields!GuidColum.Value.ToString() to convert it into a string. Anyway, the CAST in the query is also fine.

-- Robert

Invalid Characters when setting up a subscription

I have a report with some of the parameter values in French. When I
run the report from the view tab, selecting parameters works fine.
When I attempt to create a subscription, I get "There is an error in
XML document (1, 6319)".
When I view the log, I see mention of an invalid character.
Why would it work on the view tab and not in the subscription setup?
Any help is greatly appreciated.
Craigdid you ever figure this out i have the same issue
"Craig" wrote:
> I have a report with some of the parameter values in French. When I
> run the report from the view tab, selecting parameters works fine.
> When I attempt to create a subscription, I get "There is an error in
> XML document (1, 6319)".
> When I view the log, I see mention of an invalid character.
> Why would it work on the view tab and not in the subscription setup?
> Any help is greatly appreciated.
> Craig
>

interrelated report parameters

Is it possible to have parameter available values (coming from different
queries) BUT related to each other?
for example, we have two report parameters, ProductArea and ProductType,
with their available values coming from the queries:
select distinct ProductArea from SalesTable
select distinct ProductType from SalesTable
but not all product types are sold to all areas, so we need to use ONLY the
valid combinations of area and type.
Therefore, when a user selects an area from the drop-down box of available
areas, we need the second parameter to present only the types of products
actually sold in the selected area as available values, so the second query
should change to something like:
select distinct ProductType from SalesTable where ProductArea = <Selected
Value>
Is it possible to reference the selected value of a parameter at runtime in
the query?Yes, this is called Cascading Parameters in Reporting Services. All you need
to do is to set up a query parameter in your main query with the area
identifier. For example:
select Area, ProductType, Product, ListPrice FROM MyTable WHERE Area = @.area
AND ProductType = @.producttype
Your picklist for the area parameter will come from the query you have
provided below:
select distinct ProductArea from SalesTable
Your second parameter for ProductType will have a picklist defined by the
following query:
select distinct ProductType from SalesTable where Area = @.area
Because this query uses a parameter which will not be available until a
selection for the first parameter is made, Reporting Services will have the
listbox greyed out until a selection of Area has been made. And when the
selection for Area has been made, the picklist for the ProductType will be
populated with values which are only relevant to that area.
HTH
Charles Kangai, MCT, MCDBA
"vsiat" wrote:
> Is it possible to have parameter available values (coming from different
> queries) BUT related to each other?
> for example, we have two report parameters, ProductArea and ProductType,
> with their available values coming from the queries:
> select distinct ProductArea from SalesTable
> select distinct ProductType from SalesTable
> but not all product types are sold to all areas, so we need to use ONLY the
> valid combinations of area and type.
> Therefore, when a user selects an area from the drop-down box of available
> areas, we need the second parameter to present only the types of products
> actually sold in the selected area as available values, so the second query
> should change to something like:
> select distinct ProductType from SalesTable where ProductArea = <Selected
> Value>
> Is it possible to reference the selected value of a parameter at runtime in
> the query?
>