how to correctly work with two (or more) databases?

Does anybody know how to work with two (or more) databases in SQL Server 2005; or where that information can be obtained? I searched online, in BOL and asked in this forum but with no success.

information in this posting does not work; results in invalid object name (source database) and/or database does not exist (destination database) errors:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=292125&SiteID=1

this post about the database does not exist has received no replies:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=295742&SiteID=1

Of course, both databases exist and their names are valid. Both can be accessed individually thru SSMS and a VB app I am coding. The problem is when trying to work with both of them.

Any information on the subject of working with multiple datatabases and/or links to said subject would be appreciated.

Hi,

ok noone answered because this *has* to be a typo or a logical error. Try to make sure that you specified the *right* schema and that you specified the right database and make sure that you are connecting to the right server and the right instance.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

I don't understand. How I can successfully run queries on each database individually; yet, fail when trying to run a query that refers to both of them?

Here are two version of a query I tried. The first reports error: database 'tf_1' does not exist. The second reports invalid object name 'rawtf_1.dbo.TstTable_1'.

These queries are being run in SSMS. BTW, the [brackets] are used because the fieldnames are keywords in other programs that I am using.

SELECT
[DateTime],
[Open],
[High],
[Low],
[Close],
[Volume]
INTO tf_1.dbo.TstTable_1
FROM TstTable_1

SELECT
[DateTime],
[Open],
[High],
[Low],
[Close],
[Volume]
INTO tf_1.dbo.TstTable_1
FROM rawtf_1.dbo.TstTable_1

When I run sp_databases, both databases are listed; a query such as:

SELECT * FROM TstTable_1

runs successfully.

How can I discover what I am doing wrong?

|||

Are both the objects in the dbo schema, or are they in a different schema? Do you have permission to access each of the databases in question?

When you run the query that is successful, which database are you running the query in? Also, what is your default schema? To get some of this information, simply run the following queries and post the output:

select db_name()

select schema_name()

select schema_name(schema_id), name from tf_1.sys.tables where name = 'TstTable_1'

select schema_name(schema_id), name from rawtf_1.sys.tables where name = 'TstTable_1'

select name from master.sys.databases where database_id > 4

|||

First, thanks for taking the time to help me with this.

Both are in the dbo schema. I have successfully run queries on both databases individually so I assume that means I have the necessary permissions. I'm the admin on the computer and I created both databases.

In SSMS, running your queries:

select db_name() // correct dbname when run on each database

select schema_name() // dbo when run on each database

select schema_name(schema_id), name from tf_1.sys.tables where name = 'TstTable_1' // could not run this because have yet to successfully copy the table from rawtf_1.mdf

select schema_name(schema_id), name from rawtf_1.sys.tables where name = 'TstTable_1' // ran this and got the dreaded Invalid object name 'rawtf_1.sys.tables'. error message.

select name from master.sys.databases where database_id > 4 // ran this on each database and both times successfully listed both databases

|||

hmmm...well, are you by chance on a case-sensitive server? Is the database name all lower-case, or mixed-case?

Also, what compatibility level is each of these databases running under?

Something must be going on somewhere, this is definately something you should be able to (and can normally) do...hopefully we'll get it figured out...

|||

How can I tell if I'm on a case-sensitive server? I tried running the SELECT INTO query using upper case; same result database 'TF_1' does not exist. Both databases were created with all lowercase names.

Both databases have compatibility set to SQL Server 2005 (90).

I have discovered something interesting tho. In SSMS, I opened each databases properties dialog box and lined them up side by side. On the Options tab, in State options: for rawtf_1, Database State is Normal; for tf_1, there is no value. In the General tab, rawtf_1 Status is Normal; for tf_1, it is Shutdown, Normal.

|||

For the heck of it, I again tried deleting and recreating the tf_1 database. This time, fortunately, the SELECT INTO query worked. Unfortunately, I am now getting the following error message when I tried open TstTable_1 in either database in SSMS:

Class does not support aggregation (or class object is remote) (Exception from HRESULT: 0x80040110 (CLASS_E_NOGGREGATION)) (Microsoft.SqlServer.SqlTools.VSIntegration)

Other posts on the subject suggested uninstalling and reinstalling. Not a happy prospect but perhaps that is my only option at this point.

|||

Trying to uninstall and reinstall sql server only made things worse. I've moved this discussion to Setup & Upgrade; tho I'll probably be offline a day or two while I do a complete reinstallation of windows xp, programs and data. what a bummer.

http://forums.microsoft.com/MSDN/AddPost.aspx?ForumID=95&SiteID=1

|||BTW, did a complete reinstall of winxp and sql server 2005 dev edition. That just generated new problems.|||

After more than a week of aggravation and complete reinstalls of winxp and sql server 2005 dev edition, I'm back to the same situation: I cannot run a query involving two databases. Does anybody know what the problem could be? Or does anyone know of any other dbms that can easily work with two databases? Is the sql server 2000 any less aggravating than 2005?