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?
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)
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.
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"
Maybe I'm stating the obvious... did you enable SQL security on your SQLServer?
Hmm.. and where would I do that? (Currently I'm running my app and this install of 2005 on my development machine.)
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
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.
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.