none
SqlClient issue with SQL Server 2000 asp.net 3.5 RRS feed

  • Question

  •  

    Hi,

     

    I developed a web application in C# (ASP.NET 3.5) with SQL Express 2005 database. However, later on I found the production server only has SQL Server 2000 SP4. I have hosted my application on an XP machine.

     

    I am getting SQLException saying 'invalid object name fp_escape_quote'. Is this because I am trying to connect to SQL Server 2000 now?

     

    Here is what I do with my connection strings.

     

    Dev (SqlExpress 2005)

    <add name="C1" connectionString="Data Source=ch1\SQLEXPRESS;Initial Catalog=DBName;Persist Security Info=True;User ID=sa;Password=pwd" providerName="System.Data.SqlClient" />

     

    In production (SQL Server 2000), I change the configuration file after deploying the website to:

    <add name="C1" connectionString="Data Source=OMEGA2;Initial Catalog=DBName;User ID=sa;Password=pwd" providerName="System.Data.SqlClient" />

     

    How should I manage this transition? Please help me out.

     

    Appreciate it.

     

    Malay

    Wednesday, March 26, 2008 4:50 PM

Answers

All replies

  • You need more than connection string to convert your database from SQL Server 2005 to SQL Server 2000, the best tool at the moment is the wizard created by the SQL Server team to help you publish your database.

     

    http://www.microsoft.com/downloads/details.aspx?FamilyID=56e5b1c5-bf17-42e0-a410-371a838e570a&DisplayLang=en

    Wednesday, March 26, 2008 7:15 PM
  • I am sorry but why do I need that tool?

     

    My initial database was in SQL Server 2000. I have SQL Express 2000 on my dev computer. If I am able to restore them on each other (2000 <---> 2005) then why should the provider have any issues? Production is also going to have only SQL 2000. There are two constraints though. I can't access SQL 2000 from my dev computer and production can't have 2005.

     

    Thank you for your help.

    Wednesday, March 26, 2008 8:57 PM
  • There is no SQL Server Express 2000 so are you saying you are running MSDE which is the 2000 version of Express.  You need that tool because you cannot move SQL Server 2005 database to 2000 without copying all your files to 2000.  what I am saying is you cannot restore SQL Server 2005 database in SQL Server 2000.

     

    Wednesday, March 26, 2008 9:12 PM
  • That is ok. I don't want to do that either.

     

    Situation:

    Dev computer - VS 2008 with SQL Express 2005. I took backup of SQL2K DB and restored on SQL Express 2005. Then developed a web site using ASP.NET 3.5.

     

    Now I need to deploy the website to a computer in production LAN.

     

    I haven't changed anything in the database structure. I just added 3 new stored procedures which I created on SQL2K using query analyzer.

     

    Thank you.

     

    Wednesday, March 26, 2008 9:25 PM
  • (Production is also going to have only SQL 2000.)

     

    If your production is running SQL Server 2000 you cannot restore the database in SQL Server 2000 because for backward compatibility you can restore 2000 in Express but you cannot restore that database in Express back to 2000.  It is confusing but it is related to structural changes in the product versions. If the changes in your database is limited then why not connect to 2000 for all your development.

     

    Wednesday, March 26, 2008 9:40 PM
  • My friend! I have a physical contraint. I am remote and I can't connect my VS 2008 to production server.

     

    Let me be clear again. I do not want to restore the DB. I added a few SPs and that's it.

     

    What I am asking is this. Did VS 2008 wrote SQL Express 2005 specific code internally in the website (data binding and all), so when it actually interfaced with SQL2K, it threw this error?

    Wednesday, March 26, 2008 9:48 PM
  • Here is connection strings and you can change connection string at the top of Visual studio with the connection property.

     

    http://www.connectionstrings.com/

     

    Wednesday, March 26, 2008 9:59 PM
  • I tried to change it to sqloledb and it broke my datasources. Application continues to run at runtime though. When I try to do 'Configure Data Source' from VS, it says 'Provider' is not a valid keyword in the connection string.

     

    Thank you.

     

    Malay

     

    Wednesday, March 26, 2008 10:23 PM
  • I really don't know what to tell you I have told you what you need to do you said no and I have given you the alternative and you cannot do it so I don't know what to tell you.  If you change your mind here is that wizard it is in VS2008 see if you can use it.

     

    http://blogs.msdn.com/webdevtools/archive/2007/10/15/sql-database-publishing-wizard-is-now-in-visual-studio-orcas.aspx

     

    Wednesday, March 26, 2008 10:41 PM
  • Well.. I think I am facing a genuine issue.

     

    Now I am connecting to my SQL2K db from VS.

    Add an SQLDataSource (use SQLClient as provider)

    Assign an SP that takes text parameters as select command.

    You will get dbo.fp_escape_quote not found error at design as well as runtime.

    Here is relevant code.

    Connectionstring:

    <add name="MedOAConnectionString1" connectionString="Data Source=dhanno;Initial Catalog=MedOA;User ID=sa;Password=;"

    providerName="System.Data.SqlClient" />

     

    SQLDataSource in ASPX:

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" CancelSelectOnNullParameter="False"

    ConnectionString="<%$ ConnectionStrings:MedOAConnectionString1 %>" SelectCommand="Usp_Search_PhoneMessages"

    SelectCommandType="StoredProcedure">

    <SelectParameters>

    <asp:ControlParameter ControlID="cmbPhysician" DefaultValue="-2" Name="aDoctorNo"

    PropertyName="SelectedValue" Type="Int32" />

    <asp:ControlParameter ControlID="lblHidMsgStatus" DefaultValue="" Name="aStatusIDs"

    PropertyName="Text" Type="String" />

    <asp:ControlParameter ControlID="txtMQPatName" Name="aPatName" PropertyName="Text"

    Type="String" DefaultValue="~BLABLABLA~" />

    </SelectParameters>

    </asp:SqlDataSource>

     

    When I pass all blank parameters, it works fine. But when I pass some value, I think it is trying to escape single quote using this fp_escape_quote thing that it is not able to find.

     

    If this is the issue then it means you can't connect to SQL2K from SqlClient (pass parameters to stored procedures)?

     

    Thank you.

    Malay

    Thursday, March 27, 2008 1:30 AM
  • Extremely sorry for all the confusion.

     

    This issue has been resolved.

     

    fp_escape_quote was a user-defined function that I didn't copy to production server.

     

    Typical programmer blunder.

     

    Thank you for help.

     

    Thursday, March 27, 2008 3:24 PM
  • I am here to help glad to see your problem was resolved and moving the whole database would have solved it because everything moves with my original solution.

     

    Thursday, March 27, 2008 3:47 PM