Re: System sproc to channel to database(s)

From: Eric Sabine (mopar41_at____ho_y_tmail.ScPoAmM)
Date: 05/07/04 

Date: Fri, 7 May 2004 14:12:14 -0400

See if something like this will help you. Keep in mind it requires execute
permissions in master.
hth,
Eric

use northwind
create table tester123 (col1 int identity not null, col2 char(1) not null)
use pubs
create table tester123 (col1 int identity not null, col2 char(1) not null)
use master
go
create proc sp_insertSomething (@value char(1))
as
begin
 insert tester123 values(@value)
 return @@identity
end
exec dbo.sp_MS_marksystemobject 'dbo.sp_insertSomething'
use northwind
exec sp_insertSomething 'a'
exec sp_insertSomething 'b'
exec sp_insertSomething 'c'
use pubs
exec sp_insertSomething 'd'
exec sp_insertSomething 'e'
exec sp_insertSomething 'f'
use master
select * from northwind.dbo.tester123
select * from pubs.dbo.tester123

drop proc sp_insertSomething
use pubs
drop table tester123
use northwind
drop table tester123

"JT" <JTnospam@verizon.net> wrote in message
news:A465C1D6-DC22-43AD-8832-713EED31647C@microsoft.com...
> HI,
> I am wondering if it is possible to set-up a stored procedure in Master
that I can then use as a "channel" to insert values into identically named
tables in different databases (ie, sproc A resides in DB1, DB2, DB3....
DB1, 2, and 3 all have identical structure, but are repositories for data
from different clients).
> i was trying to use sp_databases to get the database name, assign it to a
declared variable, and then concatenate this into a fully qualified table
name. For example:
>
> DECLARE @dbName sysname
> INSERT [@dbName].dbo.myTable(......
>
> This led to the error unrecognized object name. I saw examples of ways to
concatenate this type of thing in an execute script, like N' + @dbName +
'].[' ......, but I dont think this will work for me. I say that because my
sproc has to deliver table metadata as the basis for a BizTalk Server SQL
Adapter. Therefore, it has to end with
>
> FOR XML RAW, XMLDATA
>
> What could also solve my dilemma here would be if the sql adapter's conn
string could be dynamically assigned based on the incoming message, but I
don't think this is the case.
>
> Anyway, can anyone tell me if this use of a system sproc as a
'channelizer' to direct inserts to a particular database is possible,
keeping in mind the XML requirement. Thanks
>
> John

Relevant Pages

  • Re: Creating System SPROC
    ... use northwind ... use pubs ... exec sp_test --returns 'master' ... exec sp_MS_marksystemobject sp_test ...
    (microsoft.public.sqlserver.programming)
  • Re: Two Way Bidirectional Rep
    ... Let me post my entire script with what I did. ... use master ... exec sp_replicationdboption N'WEB_TSK_MGMT', N'publish', true ... "The process could not connect to server 'GRSSQL'. ...
    (microsoft.public.sqlserver.replication)
  • Re: Granting EXEC to all my user sprocs in one hit
    ... You can run the procedure below (after you have created it in master) in the ... Grant the EXEC privilege to these user-sprocs only (excluding the ... > I'm detaching the live copy and copying the files across to dev machine, ... > set the permissions. ...
    (microsoft.public.sqlserver.security)
  • Re: Creating a view that uses a sp to retrive data
    ... Use Northwind ... And I still should qualify the database name. ... > db other than the stored procedure. ... exec tempdb..usp')x ...
    (microsoft.public.sqlserver.programming)
  • File does not exist error
    ... use Northwind ... DECLARE @SQL_STRING AS ... The query will execute successfully. ... EXEC sp_ExecuteSql @SQL_STRING ...
    (microsoft.public.sqlserver.programming)