MSSQL 2000 -> 2005 - Schema stuff?

Hi All,

I imported a database backup with no problems.
I can view the data using the Studio.
However, I noticed that all the tables are now pre-appended with a Schema name.
This also applies to Views.

How do I get 'rid' of this as my application doesn't know about the Schema and claims that it can't find the Tables and Objects I'm referenceing in the web.config.

Thanks,

-Alon

Hi,

schemas were introduced in SQL Server 2005. if the object is no in the default schema of the user, SQL Server will search for it in the dbo namespace. So, there are several options for you to make it work:

-Change default schema of the user which accesses the database to the schema the tables were imported.
-Directly import the tables to the dbo schema / or move them to the appropiate schema.
-Prefix your object in the queries with the schema name
-Schema are quite useful, you should get familiar with them, a lot to read is in the BOL. You should also consider using prefixes in your queries per se.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

While schemas are new in SQL 2005, SQL 2000 had the same basic idea (at least as far as calling the objects). In SQL 2000 objects had owners, and these were prefixed infront of the table name. In most cases dbo was the owner.

Take the application account that is logging into the database. Change it's default schema to the schema that the objects are in (probably the dbo schema) this will fix the problem. By default when you upgrade a database all users are setup with there own schema as there default schema.

You may want to remove any unneeded schemas from the database so that objects don't get created in the wrong schema.

MsSql , VPS , plsek software .... PROBLEM ! [:(]

Hi ,

like i said i have VPS plan but on this plan the plsek sofware does not support
MsSql , the stuff that host my website told me that they will not support anything that invovled to MsSql

so ... what i was doing till now ?

i download SqlExpress 2005 on the remote machine . and i can login to the Database engine using Windows Authentication ,

also , i have 'webs develeoper express' (asp.net) on my PC and also have SQLExpress 2005 - when i run my website on my PC i successfuly
insert some data into the DataTables .

but when i run the same file on the VPS (after i upload them using FTP)
that does not work and i see this message :

Cannot open database "horse" requested by the login. The login failed. Login failed for user 'VE326\IWPD_4(main)'.

my connectin string on my "DBLayer" class looks like this :

connString = @."Server=(local)\sqlexpress;Integrated Security=True;Database=horse";

ANY IDEA ?

thanks .

Hi greekhand,

The error messaged indicates that it's a login failure. So, just make sure the user account VE326\IWPD_4(main) has sufficient permission to the "horse" database. Open your Management Studio and connect to your db, select the "horse" database and expand it. In the "security" section, choose "Users" and verify if account "ve326\iwpd_4(main)" has been included. And if not, right click and slect "new users", you can assign permissions to that account here.

Hope my suggestion helps

Msg 4104 Level 16 The multi-part identifier X could not be bound.

this is so stupid and simple and I am annoyed over having to spend so much on this silly simple stuff.

I am sure I am just making a silly mistake.

I am trying to remove records from one table. The table holds 19000 something records.
To determine WHICh records to delete, I have another table that contains the 45 I want to delete.

So I wrote this very simple query
Delete from tbl_X
where tbl_X.FieldA = tbl_Y.FieldA;

The message I get is:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "tblY.FieldA" could not be bound.

Please tell me I am stupid!

Thanks!

tbl_Y needs to be mentioned as a table in a FROM clause or a JOIN clause. Perhaps you want something like

DELETE FROM tbl_X

WHERE tbl_X.FieldA IN (SELECT FieldA FROM tbl_Y)

HTH,

Don

|||

I've always coded a delete like this as a join:

DELETE tbl_X

FROM tbl_X INNER JOIN tbl_Y

ON tbl_X.FieldA = tbl_Y.FieldA

|||

THANKS!

And thanks for the quick reply!

Richard

|||

THANKS!

And thanks for the quick reply!

Richard