Home » forums » Public forums » Developing for iMIS

How to Use asi_SplitString in SQL Compatibility 80 on SQL 2005

Posted August 14th, 2008 by nfedin
|

I have a SQL 2005 server that has my iMIS database. Unfortunately, iMIS requires that the compatibility mode of SQL be set to 80 (SQL 2000). I'm trying to use the function asi_SplitString in my code, but I get an error when I call it telling me to change the compatibility level to 90 (not supported by iMIS). I want to use the function so that I can easily search the multi-instance field setup in an iMIS demographics table.

Here is my SQL....

SELECT N.ID, N.Company, N.Work_Phone, N.Fax, N.Toll_Free, N.Website, NA.Address_1, NA.City,
NA.State_Province, NA.Zip, NA.Full_Address, ND.Branch_Offices
FROM Name N
INNER JOIN Name_Address NA ON NA.ID = N.ID AND NA.Purpose = 'Address'
INNER JOIN Name_Demo ND ON ND.ID = N.ID
WHERE ( (
(
SELECT Count(*) FROM dbo.asi_SplitString(Branch_Offices,',')
WHERE ELEMENT = 'PED') > 0 )
)

If I change the compatibility to 90, the query works fine. My question is, how do I use this function in compatiblitity 80? How do ASI use this function?

Thanks,

Neil

‹ Membership Provider & Setting Expiration DateDevelop Custom Application Using iMIS 15 Public View Authentication ›
login or register to post comments | printer friendly version

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

What version of iMIS are you

On August 14th, 2008 Paul Bradshaw says:

What version of iMIS are you using? We use compatibility level 90 in our internal development and testing for the iMIS 15.x versions of iMIS.

login or register to post comments

We're using 15.0.3.2005 I

On August 15th, 2008 nfedin says:

We're using 15.0.3.2005 I was looking through the documentation, but couldn't find anywhere that stated the supported compatiblity level. Anyway, one of my co-workers had to drop down the level to 80 because there were several iMIS reports that would only work at level 80.

Neil Fedin
Ascension Technology Solutions

login or register to post comments

If you know the specific

On August 18th, 2008 Paul Bradshaw says:

If you know the specific report that was a problem, that might help. It's likely the obsolete "*=" join syntax being used that is the culprit.

I'd adjust the compatibility level to 90, and fix the one or two reports that were giving trouble (if they're not custom reports, then perhaps technical support could give you assistence if you need any, in updating the report).

SQL Server 2000 (compatibility mode 80) simply doesn't like to do much with table-valued functions and also doesn't support CTEs (Common Table Expressions) that would make using function even more powerful.

Is this an option for you?

login or register to post comments