Tech Off Thread

8 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

Trying to use SQL studio 2005 to script tables for SQL 2000

Back to Forum: Tech Off
  • User profile image
    qwert231

    You can check out the SQL that is generated by SQL studio 2005 here: SQL 2005 table scripted for 2000, but doesn't work (Not Cart Select)

    I get an error when I run it that says:
    Server: Msg 170, Level 15, State 1, Line 8
    Line 8: Incorrect syntax near '('.

    I can script other, more simple tables, without CONSTRAINTs. What's up? How can I tweak it?

  • User profile image
    hodo

    WITH (IGNORE_DUP_KEY = OFF)  is the problem.

    Looking at the CREATE TABLE syntax for SQL2000 I do not think it's legal, although it is in SQL2005.

    You will need to modify the system generated scripts by hand to tweak the differences or just use SQL2000 to auto-generate the scripts.

  • User profile image
    qwert231

    Ok... so I installed SQL 2005 for developers, since that's what our server is using, ran the scripts to get the database, and even imported the data through my VPN last night.

    Now when my software goes to connect, it says:
    System.Data.SqlClient.SqlException: SQL Server does not exist or access denied.


    WHAT THE .... 2 days I've been simply trying to get a local copy of the database so I can play around with it!!!

    Is it something different with 2005? I can connect to (local) on another computer (running SQL 2005), why not this one!!?!

    Here's my connection string:
    "user id=***;password=***;data source=(local);persist security info=False;initial catalog=OrderProcessing"

  • User profile image
    Blue Ink

    Maybe I'm stating the obvious... did you enable SQL security on your SQLServer?

  • User profile image
    qwert231

    Hmm.. and where would I do that? (Currently I'm running my app and this install of 2005 on my development machine.)

  • User profile image
    blowdart

    qwert231 wrote:
    Hmm.. and where would I do that? (Currently I'm running my app and this install of 2005 on my development machine.)


    Remember developer edition, and SQL Express disable remote connections by default. That includes connections using localhost.

    Run the Surface Area Configuration tool and choose to set the SAC for services and connections. Look at the Remote Connections property for the Database Engine of your instance

  • User profile image
    Blue Ink

    Open SQL Server 2005 Management Studio,
    In the object explorer pane, right click on the server (it's the root),
    Select Properties. The Server Properties Dialog appears.
    Under "Select a page", select "Security".
    Under "Server authentication", in the right portion of the dialog, make sure the item "SQL Server and Windows authentication mode"is selected.

    SQL Server 2005 Express appears to be the same, when using Management Studio.

    --m

  • User profile image
    qwert231

    You were right blowdart, I set it to allow local and remote... which doesn't make much sense, but it worked. It was set to local only... so I don't know why my vb .net code was using remote... but it will be when deployed, so I might as well develop that way.

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.