Using multiple databases

[snufse]

We are using MS sequel server and have a production data base provided by a third party. We need to create additional views, stored procedures and even some customized tables.

My question is: Could we put those in a "customized" data base and still be able to access tables in the prodiction data base? If so, how would a sp or view located in the "customized" db know to look for tables in the "production" db. In my vb program do I need to connect to both data bases and call the sp in the customized data base and it will find the table(s) in the production data base? Also If I have a table in both customized and production data base that need to be part of same sp will that be a problem?

[RobDog888]

Thread Moved

[GaryMazzone]

Are the custumized db and the orginal on the same database server? If you you can use use the fully qualified path for the object

dbname.shcemaname.objectname

If on different servers setup a linked db and then do the same

[snufse]

Yes, they are located on the same db server. If I understand you correctly I should be able to use tableA from data base A and and tableB from data base B as long as I use a fully qualified path in my stored procedure, correct? Would I then in my vb program have to open 2 connections like:

Dim ConnectionString1 = "Data Source=VGIWPW03-SQL3;Initial Catalog=Prod;User Id=xx;Password=yy;"
and
Dim ConnectionString2 = "Data Source=VGIWPW03-SQL3;Initial Catalog=Custom;User Id=xx;Password=yy;"

[GaryMazzone]

Yes that is correct

[kevchadders]

You will only need two connection strings if your planning to run stored procedures from both databases.

Otherwise just use the one connection, as your stored procedure will be doing the join across the databases.

Make sure the username you use has access rights to both databases.

[alex_read]

Just as an additional note, now this thread has already been answered! The complete, expanded syntax is SQLServerInstanceName.CatalogName.OwnerName.Tablename.

If this were to be expanded to a database upon a second SQL Server instance;upon a different server, you must first link in the secondary SQL server into initial SQL Server instance which will be performing the accessing. This can be done via the management studio UI or by calling the sp_addlinkedserver system stored procedure.