SQL Server Performance

SQL 2000 recordset vs SQL 2005 recorset

Discussion in 'SQL Server 2005 General DBA Questions' started by leonardo, Aug 22, 2006.

  1. leonardo New Member

    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
  2. ranjitjain New Member

    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
  3. Adriaan New Member

    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)?
  4. leonardo New Member

    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.

  5. Adriaan New Member

    You might have mentioned the XML bit a bit earlier ...

    Perhaps the MDAC version was also upgraded?
  6. leonardo New Member

    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.
  7. leonardo New Member

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

    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?
  9. leonardo New Member

    The XML code is generated from ADODB.Recordset, the VB code I use to get the XML is:<br /><br /><pre id="code"><font face="courier" size="2" id="code"> <br />Dim sConnect As String<br />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 <br />Catalog=test2005;Data Source=192.168.1.90;Connect Timeout=30"<br /><br />Dim con As New Connection<br />con.CursorLocation = adUseClient<br />con.Open sConnect<br /><br />Dim cmd As New Command<br />cmd.CommandType = adCmdStoredProc<br />cmd.CommandText = "testselect"<br />cmd.Parameters.Append cmd.CreateParameter("@id", adInteger, adParamInput, , 4)<br /><br />cmd.ActiveConnection = con<br /><br />Dim rst As Recordset<br />Set rst = cmd.Execute<br /><br />Dim objxml As New DOMDocument<br />rst.Save objxml, adPersistXML<br /><br />Debug.Print objxml.xml<br /></font id="code"></pre id="code"> <br /><br /><b>and the XML output is:</b><br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />&lt;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-<br />11d1-A29F-00AA00C14882" xmlns:rs="urn<img src='/community/emoticons/emotion-7.gif' alt=':s' />chemas-microsoft-com:rowset" <br />xmlns:z="#RowsetSchema"&gt;<br /> &lt;s<img src='/community/emoticons/emotion-7.gif' alt=':S' />chema id="RowsetSchema"&gt;<br /> &lt;s:ElementType name="row" content="eltOnly"&gt;<br /> &lt;s:AttributeType name="id" rs:number="1"&gt;<br /> &lt;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=':p' />recision="10" rs:fixedlength="true" rs:maybenull="false"/&gt;<br /> &lt;/s:AttributeType&gt;<br /> &lt;s:AttributeType name="testo" rs:number="2"&gt;<br /> &lt;s<img src='/community/emoticons/emotion-2.gif' alt=':d' />atatype dt:type="string" dt:maxLength="10" rs:fixedlength="true" <br />rs:maybenull="false"/&gt;<br /> &lt;/s:AttributeType&gt;<br /> &lt;s:AttributeType name="numero" rs:number="3" rs:nullable="true" rs:writeunknown="true"&gt;<br /> &lt;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=':p' />recision="10" rs:fixedlength="true"/&gt;<br /> &lt;/s:AttributeType&gt;<br /> &lt;s:extends type="rs:rowbase"/&gt;<br /> &lt;/s:ElementType&gt;<br /> &lt;/s<img src='/community/emoticons/emotion-7.gif' alt=':S' />chema&gt;<br /> &lt;rs<img src='/community/emoticons/emotion-2.gif' alt=':d' />ata&gt;<br /> &lt;z:row id="4" testo="zzzzz " numero="4"/&gt;<br /> &lt;/rs<img src='/community/emoticons/emotion-2.gif' alt=':d' />ata&gt;<br />&lt;/xml&gt;<br /><br /></font id="code"></pre id="code"><br /><br />There is no middle-tier between my application and database.<br /><br />Thanks for helping<br /><br />Leonardo
  10. Adriaan New Member

    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?
  11. leonardo New Member

    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!
  12. Adriaan New Member

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

    ...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.
  14. Adriaan New Member

    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.
  15. Adriaan New Member

    ... but perhaps in the old situation, you had an INSTEAD OF UPDATE trigger on the view?
  16. leonardo New Member

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

Share This Page