Invalid Object Name when trying to reference a database

Hi There

Let me start by explaining what I am trying to achieve as there may be a better way to perform this. I have two databases and I want to refer to four tables - one in one database and three in the other in a sql select statement.

When I use the query builder in VWD I can only see tables for the database that is defined in the connection string - so therefore how do I add tables from another database? After reading a few posts I realized that all I have to do is use the syntax: database.owner.table (or [database].[owner].[table]). Unfortunately this didn't work and came up with the error "Invalid Object Name".

Assuming that I must have got the owner incorrect I used the Query Tool in SQL Server Express Edition and it worked perfectly! So my sql must be correct, but why will it not work inside VWD?

The two databases are namedassessmentandaspnetdb - the assessment database contains the tablereviews and the aspnetdb database contains the tablesTutorDetails,EmployerDetails andStudentDetails.

Here is the connection string and sql:

Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Assessment.mdf;Integrated Security=True;User Instance=True

SELECT Reviews.Programme, aspnetdb.dbo.TutorDetails.Tutor_DisplayNameAS [Tutor Name],

aspnetdb.dbo.EmployerDetails.DisplayNameAS [Employer Name], aspnetdb.dbo.StudentDetails.Student_DisplayNameAS [Student Name]

FROM ReviewsINNERJOIN

aspnetdb.dbo.StudentDetailsON Reviews.StudentID= aspnetdb.dbo.StudentDetails.UsernameINNERJOIN

aspnetdb.dbo.EmployerDetailsON Reviews.EmployerID= aspnetdb.dbo.EmployerDetails.UserNameINNERJOIN

aspnetdb.dbo.TutorDetailsON Reviews.TutorID= aspnetdb.dbo.TutorDetails.UserName

WHERE(Reviews.ReviewID= 15)

Disregard the value of '15' in the WHERE statement as this was just put in to ensure I got some valid data back, this will be replaced with a variable eventually.

I know the sql statement works properly, I think the problem may be something to do with either the connection string - any ideas would be much appreciated.

Regards

Stuart

If this is Sql Server 2000 I'm pretty sure you need to add a link to the other servers and you may have to do so with 2005 too. Have a look atsp_addlinkedserver.|||

Hi Brian

Thank you for your response. I am using SQL Server Express, but the two databases reside on the same server. I am struggling to find anymore information on this point which surprises me as I cannot be the only person who wants to get data from more than one database!

sp_addlinkedserver seems to be used for adding additional servers into the query but not actually for adding additional databases. It seems that the connection string I used may be limiting me to only using that database - in my case "assessment", but surely if that is the case then how would someone reference other databases on the same server?

Regards

Stuart

|||Your connection string seems to reference the file instead of using the actual sql server. Try changing it to:-

Data Source=.\SQLEXPRESS;Initial Catalog=Assessment;Integrated Security=True;User Instance=True

and make sure Assessment is registered in sql server.|||

Brian

Thank you once again for your reply. Unfortunately this method isn't working - it throws up an error about connecting. The connection string that I originally had is the default one create by VWD and seems to be the one quoted in many online articles, but I cannot believe that it restricts acess to just the database it quotes in the connection.

The error was Error 26: Error locating server/instance. It mentions about checking whether SQL Server accepts remote connections, I have checked this and it does.

Any more thoughts?

Regards

Stuart

|||You need to make sure the db appears in Management studio by right-clicking databases and selecting attach. I suspect you added this db in VWD but this uses a file method not disimilar to using an access db. It's allows for xcopy of 2005 dbs but I suspect that if you want to reference other dbs then they all need to be in the sql server environment.
Also you may need to change your connection string to reference the server i.e. machinename\sqlexpress. I have successfully tested this on my machine so I think it may be the problem. If you haven't got Management Studio Express it's the new enterprise manager.Download it here.|||

Brian

Thank you once again for your comments. I have been thinking for a while that it must be related to the fact that sql server express when attached using "attachdb=" appears as a server with just one database when you connect to it using the standard connection string generated in VWD. As such it doesn't matter how many times you try and refer to other databases it will claim they do not exist.

I already had the Management Studio Express and used that to attach the databases, however it threw up all sorts of issues with the application when it came to executing it. Typically one part of the application which used the original connection string would get hold of the database and prevent the others from accessing it and so on...

Anyway, cut a long story short. I wiped the original connection strings and replaced them with connection strings that would be used to connect to SQL Server running on a remote machine - removing the "attachdb=" element that causes so many problems. I also had to change the provider setup in web.config otherwise my membership wouldn't work.

But it now works! I cannot believe other people haven't come across this before? In the future I am going to setup the connection strings in this way from the beginning as it seems to provides a lot more flexibility.

Once again, thank you for your support.

Regards

Stuart

|||Glad you got it sorted. I suppose it's early days with Sql Server and I'm sure people will happen accross this problem in the future.sql