Invoking a SQL Server user-defined function via SQL passed to Informix

==========
Background
==========
My colleague and I are using MS Reporting Services to create reports based upon data extracted from an Informix database. Inseveral reports, we have the need to parse the contents of a variable length field in order to extract a variable lengthforeign key value.

Note that the key value is of variable length and embedded within a string that's also of variable length.

==========
Question
==========
Given the fact that we aren't allowed to add anything within the back-end Informix environment, we wrote a SQL Server userdefined function that can extract a variable length "key" value from a variable length string. My question is as follows:

Is it even possible to invoke a SQL Server user defined function in our SQL statement (passed to Informix) within the context of Reporting Services?

==========
Environment Notes
==========
- We are NOT allowed to add anything within the back-end Informix db environment.

- We are connecting via an Informix ODBC driver with a linked Informix server defined in our SQL Server environment.

- We've tried defining the user-defined function within the "master", "ReportServer" and "ReportServerTempDB" databases with the appropriate permissions set.

==========
Error Messages
==========
We've received the following error message after trying to invoke the SQL Server user defined function:

SELECT field1, field2, field3, dbo.udf_GetStringElement(field3,'/',1,2) AS extrctd

[Informix][Informix ODBC Driver][Informix]Identifier length exceeds the maximum allowed by this version of the server.

We receive the following error message after creating a new SQL Server user defined function using a shorter name:

SELECT field1, field2, field3, dbo.gse(field3,'/',1,2) AS extrctd

[Informix][Informix ODBC Driver][Informix]Routine (dbo.gse) can not be resolved.

Any feedback would be appreciated,

ndm

have you tried using a custom code function for it ?|||

Hey, thanks for the reply.

I'm not sure I understand what you mean by "custom code function". The user defined functions I mentioned, "udf_GetStringElement", "gse", are user defined functions that I wrote. It's not one of the "system" functions (e.g. "fn_isreplmergeagent") found in master.

I merely created the function WITHIN the context of the "master" db and eventually within "Report Server" and "ReportServerTempDB" in an attempt to invoke the function within a Report Services SQL statement.

|||By Custom Code function I mean the custom code vb.net functions you canwrite under Report -> Properties -> Code tab. You canreplicate the logic of your user defined function you have in sql intovb.net.
what xactly foes your user defined function do ?
|||

Ah, I understand what you're saying about simply replicating/writing the string parsing/extraction logic within the Report Services environment, but our issue isn't really a (post-data aggregation) formatting problem.

Our SQL Server user defined function parses a variable length string in order to extract a variable length "foreign key" value that we'll need to use for joining data from several Informix tables.

Unless there's another approach of which I'm unaware, I'm assuming that we need to parse this string DURING the SQL execution in order to perform the requisite join(s).

Appreciate the feedback,

ndm

|||I didnt understand the situation completely..you have the SELECT stmt from Informix db but the UDF is in SQL Server right ? does the udf do anything other than string parsing ? looks like its a little complicated and we could be hitting the limitations ( one of the many) of RS.|||

Correct; the SELECT statement (defined within shared Report Services dataset) is passed to Informix and our custom UDF is in SQL Server.

More specifically, I've tried placing our custom SQL Server UDF within several of the SQL Server databases ("master", "ReportServer" and "ReportServerTempDB"). When I attempt to invoke the UDF within the SQL statement passed to Informix, I receive the aforementioned error messages.

The key point is that we need to be able to parse DURING the SQL execution in order to perform some requisite join(s).

Within a pure SQL Server environment, I can access the UDF from master (or, for that matter, any other SQL Server db so long as the appropriate permissions are set). I'm not even sure if what we're trying to accomplish is possible since the SQL statement is being passed to Informix. I was wondering if there was some "Reporting Services" method for invoking our custom UDF defined WITHIN the SQL Server realm.

Again, I appreciate the reply.

ndm

|||

The SQL statement you have is executed on Informix DB engine and it wouldnt understand the SQL UDF..unless you make a specific connection to SQL for which it may not be possible in 1 SELECT stmt..you prbly might need to use a set of stmts..If the UDF is only doing some string parsing I would recommend moving it into RS custom code and rewriting it in vb.net. Cant think of anything else..

|||

I would create a DTS package that is scheduled to get the data from Informix and populate the Reporting Service database. Try the urls below for more info. Hope this helps.

http://www.sqldts.com

http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/infmxsql.mspx#EDAA

Kind regards,

Gift Peddie

|||

Yes, I'm thinking that we may have to take a less directextract+dump+cleanse route (Informix -> MS SQL Server -> MS Reporting Services) for this scenario. We were trying to avoid taking snapshots of the data and having to maintain a secondary (albeit temporary) data store.

Your recommendation merits further consideration.

Thanks for the feedback,

ndm

Invert values

How do I go about inverting values. That is:
-change all positive to negative and change all negative to positive
I have tried using abd function but that only changes negative to positive??
SET amt = Abs(amt)
What the best way of performing this in sql?Set Amt = Amt * -1

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 use of INSERT within a function.

Is it possible to insert into a table from a function. I have a function that searches for a unique group id, and if the group does not exist, it inserts and returns the id. I created a procedure with the same code (except for the returns) and it operated properly, so it shouldn't be a logic issue. I cannot find any resources that say inserting from within a function is disallowed. Microsoft even does it on one of their sql server 2000 examples in msdn.

Any help would be greatly appreciated.You cannot INSERT, UPDATE, or DELETE from tables in a FUNCTION. The examples you probably saw did so to locally defined variables of type TABLE.

You can return a value from a stored procedure to get the new ID. You can do this with an output parameter or with a RETURN in the stored procedure. To get the value of the RETURN, execute the stored procedure like this:

DECLARE @.ResultCode int
EXEC @.ResultCode = uspGetMyGroup

Originally posted by saderax
Is it possible to insert into a table from a function. I have a function that searches for a unique group id, and if the group does not exist, it inserts and returns the id. I created a procedure with the same code (except for the returns) and it operated properly, so it shouldn't be a logic issue. I cannot find any resources that say inserting from within a function is disallowed. Microsoft even does it on one of their sql server 2000 examples in msdn.

Any help would be greatly appreciated.

Invalid object name dbo.getname ?

someone please help me?

i've made this used-defined function and sql won't let me use it,

CREATE FUNCTION dbo.getname (@.sname varchar(255))
RETURNS TABLE
AS

RETURN
SELECT ISNULL((select (c.firstname + ' ' + c.surname) from contacts.dbo.c_contact as c where c.employ_ref LIKE @.sname), @.sname) as FullName

using it,

select dbo.getname(c.EMPLOY_REF)
from c_contact as c

but everytime i try to use it i get,

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.getname'.

what's wrong with it?, i should have rights, i am set as sysadmingot it to work,

CREATE FUNCTION dbo.getname (@.sname nvarchar(255))
RETURNS nvarchar(255)
AS

BEGIN
DECLARE @.ss as nvarchar(255)
SET @.ss = (
SELECT ISNULL((select (c.firstname + ' ' + c.surname) from contacts.dbo.c_contact as c where c.employ_ref LIKE @.sname), @.sname) as FullName
)
RETURN(@.ss)
END

invalid object name (sql server

I am attempting to call a user defined function from a stored procedure in the same database, and I get the following error:

Invalid object name 'dbo.fn_NewSplit'.

I've tried calling it as dbo.fn_NewSplit, fn_NewSplit etc. I also setup another that had my DB login IE ihomesm_maindb.fn_NewSplit and get the same results.

Interestingly there is another previously built stored procedure calling the same function with apparantly no problems.

Also, I have tried to execute this stored procedure with both my .net application and within the query analyzer with the same error.

Can you post the function code as well as your TSQL calling the function?

|||

jmhooten:

I am attempting to call a user defined function from a stored procedure in the same database, and I get the following error:

Invalid object name 'dbo.fn_NewSplit'.

I've tried calling it as dbo.fn_NewSplit, fn_NewSplit etc. I also setup another that had my DB login IE ihomesm_maindb.fn_NewSplit and get the same results.

Interestingly there is another previously built stored procedure calling the same function with apparantly no problems.

Also, I have tried to execute this stored procedure with both my .net application and within the query analyzer with the same error.

Well, thank you Sql Server developers, my issue wasn't that the object name wasn't valid, it was that I was not calling it correctly (I should have called the function within a SELECT statement in my case) the last question lead me to the answer.

Invalid object name

Hi All
I am calling a function from a stored procedure (the function is created
before the stored procedure). Both the stored procedure and the function are
created successfully (no syntax errors in the enterprise manager).
When I try to execute the stored procedure I get the message "Invalid object
name 'twuser.Proc_SelectTwinEntries'".
I can't find anything wrong, can anyone help?
Thanks in advance
Elie Grouchko
CREATE FUNCTION twuser.Proc_SelectTwinEntries(
@.twc_comment_subject int
) RETURNS TABLE
AS
RETURN (
SELECT
[twc_comment_twin]
FROM [twt_comment]
WHERE ([twc_comment_subject] = @.twc_comment_subject) AND ([twc_comment_twin]
IS NOT NULL)
)
CREATE PROCEDURE twuser.Proc_SelectTopForumCommentsOrderByDate
@.twc_comment_subject int
AS
SELECT TOP 5 * FROM [twt_comment]
WHERE ((([twc_comment_subject] = @.twc_comment_subject) AND ([twc_comment_id]
!= @.twc_comment_subject)) OR
([twc_comment_firstparent] IN
(twuser. Proc_SelectTwinEntries(@.twc_comment_subj
ect))))
AND ([twc_comment_state] = 2) AND [twc_comment_twin] IS NULL
ORDER BY [twc_comment_date] DESC
GOYou can only reference a table-valued UDF in the FROM clause. You could
change your IN subquery:
SELECT [twc_comment_twin]
FROM twuser. Proc_SelectTwinEntries(@.twc_comment_subj
ect)
but I don't see much point here. Why not just combine the logic of the
two queries?
David Portas
SQL Server MVP
--|||> ([twc_comment_firstparent] IN
> (twuser. Proc_SelectTwinEntries(@.twc_comment_subj
ect))))
([twc_comment_firstparent] IN
(select [twc_comment_twin] from
twuser. Proc_SelectTwinEntries(@.twc_comment_subj
ect))...
"Elie Grouchko" wrote:

> Hi All
> I am calling a function from a stored procedure (the function is created
> before the stored procedure). Both the stored procedure and the function a
re
> created successfully (no syntax errors in the enterprise manager).
> When I try to execute the stored procedure I get the message "Invalid obje
ct
> name 'twuser.Proc_SelectTwinEntries'".
> I can't find anything wrong, can anyone help?
> Thanks in advance
> Elie Grouchko
> CREATE FUNCTION twuser.Proc_SelectTwinEntries(
> @.twc_comment_subject int
> ) RETURNS TABLE
> AS
> RETURN (
> SELECT
> [twc_comment_twin]
> FROM [twt_comment]
> WHERE ([twc_comment_subject] = @.twc_comment_subject) AND ([twc_comment_twi
n]
> IS NOT NULL)
> )
> CREATE PROCEDURE twuser.Proc_SelectTopForumCommentsOrderByDate
> @.twc_comment_subject int
> AS
> SELECT TOP 5 * FROM [twt_comment]
> WHERE ((([twc_comment_subject] = @.twc_comment_subject) AND ([twc_comment_i
d]
> != @.twc_comment_subject)) OR
> ([twc_comment_firstparent] IN
> (twuser. Proc_SelectTwinEntries(@.twc_comment_subj
ect))))
> AND ([twc_comment_state] = 2) AND [twc_comment_twin] IS NULL
> ORDER BY [twc_comment_date] DESC
> GO
>
>

invalid object like view, function etc.

Hi is there a way to know if object (view, function, etc) are invalid
?
let say a have a table t1 (field col1, col2)
and a view v1 (field t1.col1, t1.col2)

if I drop t1.col2, the view v1 is not working anymore. I want to know
that information.

In Oracle (8.1.7), i can query the all_objects, user_object table,
where status = 'INVALID'. So i can recompile invalid objects (or
correct it).

In sql Server, the table sysobjects give me some status info, but
they are not documented enough.
Do you know if i can user one of those fields : status, userstat,
sysstat ?

Same question for function , procedure.
TKS.SQL Server doesn't expose this information. One method to identify invalid
objects is to reference them with SET FMPONLY ON:

SET FMTONLY ON
SELECT * FROM MyView
SET FMTONLY OFF
GO
SET FMTONLY ON
EXEC MyProcedure NULL
SET FMTONLY OFF
GO

However, this is not as thorough as actually exercising the objects. For
example, it won't detect invalid dynamic SQL or triggers. Below is a proc
that will generate and execute such a script for all views, functions and
procedures in the database. Note that it is still under development and
hasn't been tested thoroughly.

CREATE PROC #ValidateObjects
AS
SET NOCOUNT ON
--procedures and functions
SELECT
CASE r.ROUTINE_TYPE
WHEN 'PROCEDURE' THEN 'Procedure'
WHEN 'FUNCTION' THEN
CASE
WHEN
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)), 'IsTableFunction') = 1
THEN 'TableFunction'
WHEN
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)), 'IsScalarFunction') = 1
THEN 'ScalarFunction'
WHEN
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)), 'IsInlineFunction') = 1
THEN 'InlineFunction'
END
END AS ObjectType,
QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME) AS ObjectName,
REPLICATE(N'NULL,',
ISNULL((SELECT COUNT(*) AS Parameters
FROM INFORMATION_SCHEMA.PARAMETERS p
WHERE
p.IS_RESULT = 'NO' AND
p.SPECIFIC_SCHEMA = r.ROUTINE_SCHEMA AND
p.SPECIFIC_NAME = r.ROUTINE_NAME), 0)) AS Parameters
INTO #Objects
FROM INFORMATION_SCHEMA.ROUTINES r
WHERE
ROUTINE_TYPE IN ('PROCEDURE', 'FUNCTION') AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)), 'IsMSShipped') = 0
UNION ALL
--views
SELECT
'View' AS ObjectType,
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) AS ObjectName,
'' AS Parameters
FROM INFORMATION_SCHEMA.TABLES t
WHERE
TABLE_TYPE = 'VIEW' AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0

--remove trailing comma from parameter list
UPDATE #Objects
SET Parameters = LEFT(Parameters, LEN(Parameters) - 1)
WHERE RIGHT(Parameters, 1) = N','

--generate invocation scripts
SELECT
CASE ObjectType
WHEN 'View' THEN 'SELECT * FROM '
WHEN 'Procedure' THEN 'EXEC '
WHEN 'ScalarFunction' THEN 'SELECT '
WHEN 'InlineFunction' THEN 'SELECT * FROM '
WHEN 'TableFunction' THEN 'SELECT * FROM '
END +
RTRIM(ObjectName) +
CASE ObjectType
WHEN 'View' THEN ''
WHEN 'Procedure' THEN ' '
WHEN 'ScalarFunction' THEN '('
WHEN 'InlineFunction' THEN '('
WHEN 'TableFunction' THEN '('
END +
Parameters +
CASE ObjectType
WHEN 'View' THEN ''
WHEN 'Procedure' THEN ''
WHEN 'ScalarFunction' THEN ')'
WHEN 'InlineFunction' THEN ')'
WHEN 'TableFunction' THEN ')'
END
AS InvocationScript
INTO #InvocationScripts
FROM #Objects
ORDER BY ObjectName

DECLARE InvocationScripts CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT InvocationScript
FROM #InvocationScripts
DECLARE @.InvocationScript nvarchar(4000)
OPEN InvocationScripts
WHILE 1 = 1
BEGIN
FETCH NEXT FROM InvocationScripts INTO @.InvocationScript
IF @.@.FETCH_STATUS = -1 BREAK
SET @.InvocationScript = 'PRINT ''' +
@.InvocationScript +
''' SET FMTONLY ON ' + @.InvocationScript + ' SET FMTONLY OFF'
EXEC sp_executesql @.InvocationScript
END
CLOSE InvocationScripts
DEALLOCATE InvocationScripts

DROP TABLE #Objects, #InvocationScripts
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Fran?ois Bourdages" <francois.bourdages@.harfan.com> wrote in message
news:92e05f1e.0410010722.7a57f90a@.posting.google.c om...
> Hi is there a way to know if object (view, function, etc) are invalid
> ?
> let say a have a table t1 (field col1, col2)
> and a view v1 (field t1.col1, t1.col2)
> if I drop t1.col2, the view v1 is not working anymore. I want to know
> that information.
> In Oracle (8.1.7), i can query the all_objects, user_object table,
> where status = 'INVALID'. So i can recompile invalid objects (or
> correct it).
> In sql Server, the table sysobjects give me some status info, but
> they are not documented enough.
> Do you know if i can user one of those fields : status, userstat,
> sysstat ?
> Same question for function , procedure.
> TKS.sql

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 character value on 7000th record

Our developers are trying to pinpoint why a function keeps bombing out
(email below). The database was created using the same setup as other
dbs, none of which have had this problem. I ran a trace, which showed
several Sort Warnings before the process stopped, but no error
messages. The process seems to be a complex query for data, which is
then loaded into a table.
Any suggestions?
I am trying to debug a problem with some data conversion (from a dbf
file into a SQL table) For some reason we get this once we have loaded
our 7000th record. It is not a problem with the record, not always the
same one, has something to do with the limit. Not sure why 7000, but
always crashes there. I have tried everything on the code side.
Is there any setting in SQL that my enforce some limits on data loading
or on the store call, maybe something odd with this table?
"Underlying DBMS error[Microsoft OLE DB Provider for SQL Server:
Invalid character value for cast specification. (.dbo.a109)]"hi,
If you try to move that table using an ETL tool as DTS, inside the pump you
can define how many error as maximum you want to pass.
"naomi" wrote:

> Our developers are trying to pinpoint why a function keeps bombing out
> (email below). The database was created using the same setup as other
> dbs, none of which have had this problem. I ran a trace, which showed
> several Sort Warnings before the process stopped, but no error
> messages. The process seems to be a complex query for data, which is
> then loaded into a table.
> Any suggestions?
>
> I am trying to debug a problem with some data conversion (from a dbf
> file into a SQL table) For some reason we get this once we have loaded
> our 7000th record. It is not a problem with the record, not always the
> same one, has something to do with the limit. Not sure why 7000, but
> always crashes there. I have tried everything on the code side.
> Is there any setting in SQL that my enforce some limits on data loading
> or on the store call, maybe something odd with this table?
> "Underlying DBMS error[Microsoft OLE DB Provider for SQL Server:
> Invalid character value for cast specification. (.dbo.a109)]"
>

interval dates

i want to create a user-defined function that accepts 2 dates and returns
the time difference in days, hours, minutes, and seconds-
for example, a possible result would be something like:
2 days, 17 hours, 46 minutes, 12 seconds
has anyone out there done this before in sql server? any help would be much
appreciated.
thanks,
jtjtl
CREATE FUNCTION dbo.dates_range
(
@.date1 DATETIME,
@.date2 DATETIME
)
RETURNS VARCHAR(32)
AS
BEGIN
DECLARE @.sD INT, @.sR INT, @.mD INT, @.mR INT, @.hR INT
SET @.sD = DATEDIFF(SECOND, @.date1, @.date2)
SET @.sR = @.sD % 60
SET @.mD = (@.sD - @.sR) / 60
SET @.mR = @.mD % 60
SET @.hR = (@.mD - @.mR) / 60
RETURN CONVERT(VARCHAR, @.hR)
+':'+RIGHT('00'+CONVERT(VARCHAR, @.mR), 2)
+':'+RIGHT('00'+CONVERT(VARCHAR, @.sR), 2)
END
"JTL" <jliautaud@.hotmail.com> wrote in message
news:uo90dQFDGHA.2908@.TK2MSFTNGP09.phx.gbl...
>i want to create a user-defined function that accepts 2 dates and returns
>the time difference in days, hours, minutes, and seconds-
> for example, a possible result would be something like:
> 2 days, 17 hours, 46 minutes, 12 seconds
> has anyone out there done this before in sql server? any help would be
> much appreciated.
> thanks,
> jt
>|||thanks!
do you know how i can return days as well?
jtl
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23rZKdUFDGHA.1312@.TK2MSFTNGP09.phx.gbl...
> jtl
> CREATE FUNCTION dbo.dates_range
> (
> @.date1 DATETIME,
> @.date2 DATETIME
> )
> RETURNS VARCHAR(32)
> AS
> BEGIN
> DECLARE @.sD INT, @.sR INT, @.mD INT, @.mR INT, @.hR INT
> SET @.sD = DATEDIFF(SECOND, @.date1, @.date2)
> SET @.sR = @.sD % 60
> SET @.mD = (@.sD - @.sR) / 60
> SET @.mR = @.mD % 60
> SET @.hR = (@.mD - @.mR) / 60
> RETURN CONVERT(VARCHAR, @.hR)
> +':'+RIGHT('00'+CONVERT(VARCHAR, @.mR), 2)
> +':'+RIGHT('00'+CONVERT(VARCHAR, @.sR), 2)
> END
>
>
> "JTL" <jliautaud@.hotmail.com> wrote in message
> news:uo90dQFDGHA.2908@.TK2MSFTNGP09.phx.gbl...
>|||You could do something like this with my TTimeSpan UDT at [0]:
declare @.ts TTimeSpan
set @.ts = TTimeSpan::FromDates('1/1/2005 2:00', '1/12/2005 14:6')
select @.ts.ToString(), @.ts.ToLongString()
Output:
-- ---
11.12:6:0 11 Days 12 Hours 6 Minutes 0 Seconds 0 Milliseconds
[0] - http://channel9.msdn.com/ShowPost.aspx?PostID=147390
William Stacey [MVP]
"JTL" <jliautaud@.hotmail.com> wrote in message
news:%23APwXiFDGHA.2700@.TK2MSFTNGP14.phx.gbl...
> thanks!
> do you know how i can return days as well?
> jtl
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23rZKdUFDGHA.1312@.TK2MSFTNGP09.phx.gbl...
>|||i didn't see where to get the source for TTimeSpan- can you help?
jt
"William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
news:uctoCXQDGHA.2436@.TK2MSFTNGP15.phx.gbl...
> You could do something like this with my TTimeSpan UDT at [0]:
> declare @.ts TTimeSpan
> set @.ts = TTimeSpan::FromDates('1/1/2005 2:00', '1/12/2005 14:6')
> select @.ts.ToString(), @.ts.ToLongString()
> Output:
> -- ---
--
> 11.12:6:0 11 Days 12 Hours 6 Minutes 0 Seconds 0 Milliseconds
> [0] - http://channel9.msdn.com/ShowPost.aspx?PostID=147390
> --
> William Stacey [MVP]
> "JTL" <jliautaud@.hotmail.com> wrote in message
> news:%23APwXiFDGHA.2700@.TK2MSFTNGP14.phx.gbl...
>|||JTL (jliautaud@.hotmail.com) writes:
> i didn't see where to get the source for TTimeSpan- can you help?
What about reading William's post in full?
> "William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
> news:uctoCXQDGHA.2436@.TK2MSFTNGP15.phx.gbl...
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

Intersect?

Dose SQL 2000 support the intersect function? If so...could someone show me an example. If not...how do you work around the problem.

Thanks,
TreyThis is what I am trying to do:

SELECT DISTINCT dbo.[table].*
FROM dbo.x INNER JOIN
dbo.y ON dbo.x.id = dbo.y.id
WHERE (dbo.y.reason_id = '4744')

Intersect

SELECT DISTINCT dbo.[table].*
FROM dbo.x INNER JOIN
dbo.y ON dbo.x.id = dbo.y.id
WHERE (dbo.y.reason_id <> '4786')

INTERSECT MDX Query - Reg

Hi Everyone,

We are facing some problem in the cube particularly in INTERSECT function.

Here are the details.

Dimension Tables:

DimTime 200601

200602

DimProduct 01

02

DimUser 101

102

103

FactTables:

FactPlayer

Time Product User

200601 01 101

200601 02 101

200601 01 102

Transact SQL Query:

Select Count(*) from

(

Select userid from FactPlayer where productId = 01

INTERSECT

Select userid from FactPlayer where productId= 02

)

PlayerCount

We want the same result from MDX query. Can you please guide us how to do it by using INTERSECT.

Expecting your valuable reply.

Regards

Vijay

Hi Vijay,

You could solve this using the Intersect function, but you don't need to. Here's an example from Adventure Works showing all the Customers who bought products from two different subcategories (mountain bikes and caps):

select {[Measures].[Internet Sales Amount]} on 0,
nonempty(
nonempty(
[Customer].[Customer].[Customer].members,
([Measures].[Internet Sales Amount], [Product].[Subcategory].&[1])
)
, ([Measures].[Internet Sales Amount],[Product].[Subcategory].&[19])
)
on 1
from [Adventure Works]

What it's doing is using the nonempty function to return a list of Customers who bought products in subcategory 1, and then using another nonempty function to filter that list by those who bought products from subcategory 19. This, I think, will be more efficient than using the Intersect function although for the record here's the same query rewritten to use Intersect:

select {[Measures].[Internet Sales Amount]} on 0,
intersect(
nonempty(
[Customer].[Customer].[Customer].members,
([Measures].[Internet Sales Amount], [Product].[Subcategory].&[1])
)
,nonempty(
[Customer].[Customer].[Customer].members,
([Measures].[Internet Sales Amount],[Product].[Subcategory].&[19])
)
)
on 1
from [Adventure Works]

HTH,

Chris

|||

Hi Chris,

Thank you very much. It is working perfectly.

Vijay

|||

Hi Everyone,

We are facing some problem in the cube particularly in INTERSECT function.

Here are the details.

Dimension Tables:

DimTime 200601

200602

DimProduct 01

02

03

DimUser 101

102

FactTables:

FactPlayer

Time Product User

200601 01 101

200601 02 101

200601 01 102

200601 03 101

Transact SQL Query:

Select Count(*) from

(

Select user from FactPlayer where productId = 01

INTERSECT

Select user from FactPlayer where productId= 02

INTERSECT

Select user from FactPlayer where productId= 03

)

PlayerCount

RESULT: 1 (UserId: 101)

We want the same result from MDX query. Can you please guide us how to do it by using INTERSECT.

Expecting your valuable reply.

Regards

Vijay

|||

I found the solution below.

SELECT NON EMPTY{[Measures].[User ID Distinct Count]} ON COLUMNS,

INTERSECT

(

NONEMPTY

(

INTERSECT

(

NONEMPTY

(

[DIM USER].[DIM USER].CHILDREN,

([Dim Time].[TimeKey].&[200602],

[Measures].[User ID Distinct Count],

[DIM PRODUCT].[DIM PRODUCT].&[3])

),

NONEMPTY

(

[DIM USER].[DIM USER].CHILDREN,

([Dim Time].[TimeKey].&[200602],

[Measures].[User ID Distinct Count],

[DIM PRODUCT].[DIM PRODUCT].&[11])

)

)

),

NONEMPTY

(

[DIM USER].[DIM USER].CHILDREN,

([Dim Time].[TimeKey].&[200602],

[Measures].[User ID Distinct Count],

[DIM PRODUCT].[DIM PRODUCT].&[12])

)

)

ON ROWS

FROM [DSV KPI]

Please reply me if there any changes in the query.

Thank You

Vijay