SQL Server Performance Forum – Threads Archive

SQL 2000 recordset vs SQL 2005 recorset

I have some problems using a program developed in Visual Basic that was designed for
SQL 2000. I got a new server and I installed SQL 2005 x64. The problems are related to the different recordsets that return from several queries. For instance, when using the "isnull" statement, in SQl 2000 the field in the recordset is updatable, while using the SQL 2005 it is not.
Is there a way to fix this problem in any configuration setting?
Thanks
Leonardo

Check this script:
it runs same way in 2000/2005 set nocount on
declare @t1 table(col1 varchar(20))
insert into @t1 values(‘A’)
insert into @t1 values(‘B’)
select col1 as ‘after insert’ from @t1
declare @upvar varchar(20)
update @t1 set col1=isnull(@upvar,’B’)
where col1=’A’
select col1 as ‘after Update’ from @t1 resultset:
after insert
——————–
A
B after Update
——————–
B
B

This is about the recordset being updateable in the VB application, or not – right? Are you sure the query is being passed on to SQL Server, and not executed by a local database engine (like Jet)?

quote:Originally posted by Adriaan This is about the recordset being updateable in the VB application, or not – right? You are right. Are you sure the query is being passed on to SQL Server, and not executed by a local database engine (like Jet)?

All I changed in the VB application was the server name. If I get the xml representation of the ADODB.recordset on the SQL 2005, this is different from the one I get from the SQL 2000.
In particular in the tag s:AttributeType representing the column generated by the function "isnull" the attribute rs:writeunknown="true" is missing. While in SQL 2000 this attribute exists, and the DBGrid works properly.

You might have mentioned the XML bit a bit earlier … Perhaps the MDAC version was also upgraded?

Hi Adriaan,
the MDAC version on the client is 2.81
I even tried to change the connection string. Now I am using the SQL Native Client Provider (SQLNCLI).
The one I used before was SQLOLEDB.1
But even so, it does not work.

I tried the following test. I created a new VB application that reads the datas from a newly created table on the server. These are the different recordsets I obtain depending on the code I use to read from the table 1) simple stored with select (SELECT id, testo, numero from tabella where id = @id) it works fine and the XML is the following: <s:AttributeType name="testo" rs:number="2" rs:nullable="true" rs:writeunknown="true"> <s<img src=’/community/emoticons/emotion-2.gif’ alt=’:d’ />atatype dt:type="string" dt:maxLength="10" rs:fixedlength="true"/> </s:AttributeType> 2) stored with select and isnull command (SELECT id, isnull(testo,’zzzzz’) as testo, numero from tabella where id = @id) it works fine and the XML is the following: <s:AttributeType name="testo" rs:number="2" rs:nullable="true" rs:writeunknown="true"> <s<img src=’/community/emoticons/emotion-2.gif’ alt=’:d’ />atatype dt:type="string" dt:maxLength="10" rs:fixedlength="true"/> </s:AttributeType> 3) stored with simple select that reads from a view with isnull command stored: (SELECT id, testo, numero from vtabella where id = @id) view: (SELECT id, isnull(testo,’zzzzz’) as testo, numero from tabella) it does not work and the XML is the following: <s:AttributeType name="testo" rs:number="2"> <s<img src=’/community/emoticons/emotion-2.gif’ alt=’:d’ />atatype dt:type="string" dt:maxLength="10" rs:fixedlength="true" rs:maybenull="false"/> </s:AttributeType> Note: in SQL2000 the third sample of code works fine…

Not sure that I fully understand the XML tags and stuff, but anyway … Do you have a middle-tier app that is generating this XML? SQL 2000 can hardly do a thing in XML, whereas SQL 2005 has much more XML functionality – so perhaps the middle-tier is leaving things to SQL 2005, which it was doing on its own against SQL 2000?

The XML code is generated from ADODB.Recordset, the VB code I use to get the XML is: <pre id="code"> Dim sConnect As String sConnect = "Provider=SQLOLEDB.1<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ersist Security Info=True;User ID=sa<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />wd=hehe;Initial Catalog=test2005;Data Source=192.168.1.90;Connect Timeout=30" Dim con As New Connection con.CursorLocation = adUseClient con.Open sConnect Dim cmd As New Command cmd.CommandType = adCmdStoredProc cmd.CommandText = "testselect" cmd.Parameters.Append cmd.CreateParameter("@id", adInteger, adParamInput, , 4) cmd.ActiveConnection = con Dim rst As Recordset Set rst = cmd.Execute Dim objxml As New DOMDocument rst.Save objxml, adPersistXML Debug.Print objxml.xml </pre id="code"> and the XML output is: <pre id="code"> <xml xmlns<img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns<img src=’/community/emoticons/emotion-2.gif’ alt=’:d’ />t="uuid:C2F41010-65B3- 11d1-A29F-00AA00C14882" xmlns:rs="urn<img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />chemas-microsoft-com:rowset" xmlns:z="#RowsetSchema"> <s<img src=’/community/emoticons/emotion-7.gif’ alt=’:S’ />chema id="RowsetSchema"> <s:ElementType name="row" content="eltOnly"> <s:AttributeType name="id" rs:number="1"> <s<img src=’/community/emoticons/emotion-2.gif’ alt=’:d’ />atatype dt:type="int" dt:maxLength="4" rs<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />recision="10" rs:fixedlength="true" rs:maybenull="false"/> </s:AttributeType> <s:AttributeType name="testo" rs:number="2"> <s<img src=’/community/emoticons/emotion-2.gif’ alt=’:d’ />atatype dt:type="string" dt:maxLength="10" rs:fixedlength="true" rs:maybenull="false"/> </s:AttributeType> <s:AttributeType name="numero" rs:number="3" rs:nullable="true" rs:writeunknown="true"> <s<img src=’/community/emoticons/emotion-2.gif’ alt=’:d’ />atatype dt:type="int" dt:maxLength="4" rs<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />recision="10" rs:fixedlength="true"/> </s:AttributeType> <s:extends type="rs:rowbase"/> </s:ElementType> </s<img src=’/community/emoticons/emotion-7.gif’ alt=’:S’ />chema> <rs<img src=’/community/emoticons/emotion-2.gif’ alt=’:d’ />ata> <z:row id="4" testo="zzzzz " numero="4"/> </rs<img src=’/community/emoticons/emotion-2.gif’ alt=’:d’ />ata> </xml> </pre id="code"> There is no middle-tier between my application and database. Thanks for helping Leonardo

ADO could be considered a middle tier here (stretching the point – sorry) … Anyway that’s why I was wondering about the MDAC version – perhaps there has been a change to the default behaviour with the rst.Save objxml, adPersistXML call. Where does the VB application run – is it running locally, is it a network service, is it running on ASP.NET? This will tell you where to check if there’s a newer MDAC version involved. If you’ve changed from SQL 2000 to SQL 2005, did other parts involved in the application get changed as well – particularly OS and software-wise? Do you still have the SQL 2000 server running for testing? And does that still return an updatable rowset?

Ok, I’m back My server is running with SQL 2000 and I have made some tests that leave me speechless…. I’ve created a new table in SQL 2000 with some fields and a view that reads from the new table with the isnull function.
Then I created an application in VB 6.0 that reads from the view and outputs the xml representing the recordset. Incredible! the field is not writeable!
The problem is not only in SQL2005…. Now the real problem is that I have a view that reads from a different table (using the IsNull function too) and in this case the field returned is updatable!
I don’t understand why in a table everything works fine and in another one it does not, using the same application created for the purpose. Any idea? Thanks a lot!

Could you create a view without the ISNULL function, to see if in that case the column does become updatable?

…already tried. The column becomes updatable.
I have the same problem when using the UNION command in a view. I have been told by everybody that all I had to do, once the backup was restored
on the new server with SQL2005, was to set the level of compatibility to 80.
I should not have had any problem… I am afraid this is not the case.

Is the ISNULL in the column list of the view? In that case, you have an expression and not a column – and it should not be updatable in the first place. Not sure why it was not identified as such in the old situation.

… but perhaps in the old situation, you had an INSTEAD OF UPDATE trigger on the view?

….. no there is not triggers on the view ….

]]>