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

Invalid Semantic Model

Hello

While following the instructions on the tutorial "Creating a Report Model Based on an Analysis Services Cube", I got this error when trying to create the report model:

The semantic model is not valid. Details: The element 'Entity' in namespace 'http://schemas.microsoft.com/sqlserver/2004/10/semanticmodeling' has incomplete content. List of possible elements expected: 'IdentifyingAttributes' in namespace 'http://schemas.microsoft.com/sqlserver/2004/10/semanticmodeling'. (InvalidSemanticModel)

I have tried to use a SSAS cube of my own, which is working fine and tried to create a model based on it so I can create ad-hoc reports.

Parameters chosen on the data source creation:

Connection Type: Microsoft SSAS

Connection String: Data Source=localhost\SQL2005;Initial Catalog="TAP Analysis"

Connect Using: Windows Integrated Security

To SSAS using the Microsoft SQL Server Management Studio, I use Windows Authentication.

In the model creation, I have tried to switch to other credentials, but the error still remains... Can you help me with this one?

Thanks

Andr

I have the solution.

After the opening of the case in microsoft, this problem was analysed by the support teams and I got an answer today.

The problem seems to be one relationship i've built between a fact table and a dimension table.

In the current release of SSRS and SSAS, a relationship from one entity to another is implicitly to the key of the destination entity.

I was using a Time dimension (the dimension key was "day") and I was building a relationship based on the attribute "month".

So, the relationship was ignored and my fact table had no relationships and therefore, no identifyers, and that was the cause of the problem.

The solution for my case was relating the month to the first day of the month in the Time table.

I was told Microsoft plans to lift such limitation on the next releases.

Hope this helps for anyone with similar problems

Andr

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 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