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

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

• Ring0

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

• Justin F.

  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.

  
  

• billb59

  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
  ---

  
  

• shanmugal

  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

  
  

• Gilch

  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.

  
  

• Dan Marin

  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

  
  
  

• msibm

  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

  
  

• Nick Kramer - MSFT

  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

  
  

• Pivot35

  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

  
  

• domfos

  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 to correctly work with two (or more) databases?