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