Re: Use of "USE databasename" Within a Stored Procedure

On Wed, 7 Dec 2005 07:39:02 -0800, RitaG wrote:

>Hi Hugo,
>
>Thanks for your response.
>
>We do data loads for different companies and each company has it's own SQL
>database within the same SQL server.The logic and stored procedures for each
>company's data load is different so it has not been an issue before. We now
>have taken on the task of 30 more data loads but they all use the same logic
>and stored procedures. Rather than duplicate the logic 30 times in each of
>the databases I wanted 1 common place where all the logic and stored
>procedures for these 30 data loads reside and all 30 move through this common
>place but load the data into their separate databases. We have a whole system
>designed around these separate databases and so I cannot put everything into
>just 1 database for these 30.

Hi Rita,

In this case, using seperate databases might be the better option.
Companies that happen to use the same table structure and procedures now
will probably start to diverge at a later time.

I would therefor also recommend copying the stored proc in all 30
databases. It's a bit more work now, but that will pay itself back later
when some of these companies want to tweak their DB and others don't.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
.

Relevant Pages

  • Re: Creating a cross-database command
    ... I'm not using stored procedures, I'm setting the CommandText property of the command in code. ... Given two databases A and B, both of which have an identical file File1, the SQL is... ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: SQL 2005 - audit SELECT statements?
    ... Any ideas on how to audit *select* statements with SQL 2005? ... This is a pretty common requirement with databases where one needs to ... You can audit access to those stored procedures you need audit simply by logging access to the procedure in an audit table. ...
    (microsoft.public.sqlserver.security)
  • RE: How to access a local text file from SP on remote database.
    ... In SQL 2000 BULK INSERT will take a UNC name as the data file. ... > update these to multiple remote SQL databases. ... Each of these stored procedures build a work file from these text ... > How can I point the @updates_dir at my server? ...
    (microsoft.public.sqlserver.programming)
  • Re: Portable stored procedures
    ... > JDatastore lets you store binary streams outside the table scema. ... Other databases usually do not store BLOB's ... > you will have to write the function in the client in Java (or whatever ... >> stored procedures written in some proprietary language. ...
    (comp.lang.java.databases)
  • Re: BizTalk eating up SQL Server (Virii Behaviour)
    ... Yes, one gig is the least to run sql & bts in the same box, ... uninstall procedure, i manually deleted all databases, and run the ... and it was leaving like 20 mbs free memory, ... BizTalk Solution Developer ...
    (microsoft.public.biztalk.general)