July 22, 2008 at 4:06 pm
We are in the process of moving our webserver IIS/ASP from a 32 to 64 bit platform. I have aleady come across some issues which I didn't expect and I am wondering if there are any more to look out for.
We had to change our connection strings from MDAC to SQLOLEDB as MDAC isn't supported on 64 bit.
After doing this we found a number of issues with SQL that returns multiple recordsets. On the client any dynamic SQL that was like
strSQL = "DECLARE @vals varchar(1000);SELECT @Vals = COALESCE(@vals+',','')+CAST(somePK as varchar) FROM SOMETABLE WHERE ID=@SiteID;SELECT @Vals"
set objRS = objConnection.execute(strSQL)
If isOpenRecordset(objRS,False) Then
If not(objRS.BOF AND objRS.EOF) Then
Set objRS = objRS.NextRecordet
would break as with MDAC the initial recordset will be the rowcount of the SELECT @Vals statement as SET NOCOUNT was not used. Then the NextRecordset would get the SELECT @Vals recordset. However using SQLOLEDB the objRS.NextRecordset is not needed as the first recordset returned in the SELECT @Vals so trying to call NextRecordset returns an error as there is no recordset to go to.
I know the way to get similar behaviour from both providers is to add SET NOCOUNT ON to the top of all dynamic SQL like this but am I right in presuming that with SQLOLEDB its automatically adding this command to the top of the temporary stored procedures its creating to run any dynamic SQL and this is why the behaviour is different?
Also in stored procs that return multiple recordsets that DON'T currently have SET NOCOUNT ON at the top of them which currently work fine with MDAC they break with SQLOLEDB. I have added SET NOCOUNT ON to the top of any procs which had it missing (which should have been done anyway)and it has fixed this issue.
Am I right in presuming that the issues mentioned are related to the provider and are there any more issues like this that I need to look out for?
July 25, 2008 at 4:37 am
Has no-one had to move a system from 32 to 64 bit? System is windows 2003, IIS 6, ASP, SQL 20005
Just an update on some more problems I have found which are mainly concered with switching from using an MDAC connection string to SQLOLED.
-Client side SQL that uses CTE and starts with
strSQL="WITH JOBS (Row, Category,CategoryFK, LiveJobs) " &_
is fine in MDAC but breaks in SQLOLEDB you need to put the ; in front of the WITH
strSQL=";WITH JOBS (Row, Category,CategoryFK, LiveJobs) " &_
I think most problems must be down to the TSQL that the providers create in any temporary stored procs used to run client side SQL. It would be interesting to be able to compare the two different types as that would give a heads up on what else to expect.
Also I had a stored proc used for creating de-normalised recordsets for 3rd party text indexers which had a local variable declared within called @DEBUG that when turned on will print out messages.
With MDAC this didn't seem to cause a problem but with SQLOLEDB it did. The 3rd party indexer is DTSearch and they have some functions that pass a recordset to create the index so I don't know whether the issue was related to their code but turning off the PRINT messages fixed the problem.
Other issues found so far includee
-Not being able to run scripts from the normal command prompt directory if they access 64 bit COM components they need to run from C:\Windows\SysWOW64
-ISAPI rewrite files have changed their name from httpd.ini to .htaccess.
-We have had to purchase new licences for all our 3rd party Components in 64 bit mode.
-We currently have a problem where IIS is now running in 64 bit mode but it cannot instanstiate the DTsearch COM component which has also been upgraded to 64bit. I know that both the 32 and 64 bit versions are installed on the server in different directories so something is pointing to the wrong one either IIS as 64 bit trying to use the 32 bit COM or the other way round.
If anyone else has any other advice or experience gained from going through this process please can they post it. There really should be some sort of troubleshooting guide that lists all these problems but as of yet I have not found one. Maybe I will have enough problems to write to my own at the end.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply