locked
VB2005 Parameter Queries with SQL 2008 RRS feed

  • Question

  • We have recently updated our SQL Server to 2008 (from 2000).  I moved all the data over to the SQL 2008 server by exporting from the 2000 server.  All the tables open just fine, and all the data appears to be present.  Within the VB.NET app, however, any table reference that uses a parameter generates an error:  The data types text and text are incompatible in the equal to operators.  The table adapter works just fine with full table previews, but not with either parameters or filter values (i.e., Field1 <> '0').  I should confess that I am new to SQL 2008.  What do I need to do to get these parameter queries operational?

    BTW, I have downloaded and installed the VB2005 plugin that enables working with SQL 2008 (VS80sp1-KB954961-X86-INTL.exe).
    LDC
    Monday, December 7, 2009 4:10 PM

Answers

  • The problem has been solved.  I had to detach the database in SQL 2000, copy the data and log files over to the SQL 2008 location, and then attach the database in 2008.  Once I mapped the access rights to the new database location, the VB.NET queries worked like a charm.  I chose the detach/attach option because the import to 2008 from SQL 2000 converted all the views I had developed to tables, and this was not acceptable to me.
    LDC
    • Marked as answer by LyleD Tuesday, December 8, 2009 4:22 PM
    Tuesday, December 8, 2009 4:22 PM

All replies

  • Try rebuilding the DataSets. 

    I'm probably not the best source on this as I don't use those Designer thingies to generate my DataAccess code for me, but I think changes made to the db after your components generated from the designers are already built, will break/corrupt them.  Not sure -- just something to check.
    jfc
    Monday, December 7, 2009 4:40 PM
  • I would change your text data type columns to varchar. The text data type has been deprecated and will be removed from a future version of SQL Server.

    http://msdn.microsoft.com/en-us/library/ms187993.aspx


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Monday, December 7, 2009 6:15 PM
  • The problem has been solved.  I had to detach the database in SQL 2000, copy the data and log files over to the SQL 2008 location, and then attach the database in 2008.  Once I mapped the access rights to the new database location, the VB.NET queries worked like a charm.  I chose the detach/attach option because the import to 2008 from SQL 2000 converted all the views I had developed to tables, and this was not acceptable to me.
    LDC
    • Marked as answer by LyleD Tuesday, December 8, 2009 4:22 PM
    Tuesday, December 8, 2009 4:22 PM