Hi all,



I'm working on a project of web enabling a vb application that runs off a 

SQL Server database. Currently the application data is all stored in one 

database, with a large number of stored procedures being used to access 

the data. However, my client have decided to split the database up into 3 

seperate databases. I haved been asked to assess how long it will take to 

make the necessary changes to the asp front end I'm developing. I am quite 

new to SQL Server so I'm wondering what changes are necessary. I am using 

vb dlls to access the data in the database. A large number of the current 

stored procedures access data that will soon exist on different databases. 

Can I rewrite these stored procedures to access data of different 

databases or what is the best way to tackle this. I'm on a tight deadline 

here so any help would be greatly appreciated..



Col

Preface all tables with the owner and db name:



SELECT othertable.dbo.mytableName.afieldname,

someothertable.dbo.anothertable.anotherfieldname FROM

othertable.dbo.mytableName JOIN etc.



Of course the sql user must have select privs (etc.) on each table, but you

don't have to maintain a seperate connection object.



HTHs.



-- Tomm



> -----Original Message-----

> From: Colm Diver [mailto:colm.diver@b...]

> Sent: Friday, October 26, 2001 1:50 AM

> To: ASP Databases

> Subject: [asp_databases] Accessing data on different databases

>

>

> Hi all,

>

> I'm working on a project of web enabling a vb application that runs off a

> SQL Server database. Currently the application data is all stored in one

> database, with a large number of stored procedures being used to access

> the data. However, my client have decided to split the database up into 3

> seperate databases. I haved been asked to assess how long it will take to

> make the necessary changes to the asp front end I'm developing. I am quite

> new to SQL Server so I'm wondering what changes are necessary. I am using

> vb dlls to access the data in the database. A large number of the current

> stored procedures access data that will soon exist on different databases.

> Can I rewrite these stored procedures to access data of different

> databases or what is the best way to tackle this. I'm on a tight deadline

> here so any help would be greatly appreciated..

>

> Col

The best way to tackle this is to use a distributed database.  You can

create links in one database to the other databases and then actually write

all of your data access code against the one database.



=================================

Kyle M. Burns, MCSD, MCT

ECommerce Technology Manager

Centra Credit Union

kburns@c...



 



-----Original Message-----

From: Colm Diver [mailto:colm.diver@b...]

Sent: Thursday, October 25, 2001 8:50 PM

To: ASP Databases

Subject: [asp_databases] Accessing data on different databases





Hi all,



I'm working on a project of web enabling a vb application that runs off a 

SQL Server database. Currently the application data is all stored in one 

database, with a large number of stored procedures being used to access 

the data. However, my client have decided to split the database up into 3 

seperate databases. I haved been asked to assess how long it will take to 

make the necessary changes to the asp front end I'm developing. I am quite 

new to SQL Server so I'm wondering what changes are necessary. I am using 

vb dlls to access the data in the database. A large number of the current 

stored procedures access data that will soon exist on different databases. 

Can I rewrite these stored procedures to access data of different 

databases or what is the best way to tackle this. I'm on a tight deadline 

here so any help would be greatly appreciated..



Col

It can be pretty simple really.  So long as the user account has permissions 

on all of the databases all you need to do is execute the SQL command "USE 

[dbname]" to select another database.  Make sure that you execute this 

command and any following statements by passing the same connection object 

(rather than just a connection string)



Hope this helps



Cheers

Buzzrick





----Original Message Follows----

From: "Colm Diver" <colm.diver@b...>

Reply-To: "ASP Databases" <asp_databases@p...>

To: "ASP Databases" <asp_databases@p...>

Subject: [asp_databases] Accessing data on different databases

Date: Fri, 26 Oct 2001 01:49:44



Hi all,



I'm working on a project of web enabling a vb application that runs off a

SQL Server database. Currently the application data is all stored in one

database, with a large number of stored procedures being used to access

the data. However, my client have decided to split the database up into 3

seperate databases. I haved been asked to assess how long it will take to

make the necessary changes to the asp front end I'm developing. I am quite

new to SQL Server so I'm wondering what changes are necessary. I am using

vb dlls to access the data in the database. A large number of the current

stored procedures access data that will soon exist on different databases.

Can I rewrite these stored procedures to access data of different

databases or what is the best way to tackle this. I'm on a tight deadline

here so any help would be greatly appreciated..



Col