How to use imported tables from multiple users

[tdudley]

We have an OLTP application with multiple one to many tables relationships that are edited client side. For Example the primary table has descriptive components. One of the multiple table have things like parts and quantity ordered linked by a record number back to the primary table.

When our client transmits their transaction, they actally running a batch job which sends all the client side tables to the SQL Server and then we are trying to issue a stored procedure server side to push this data into our 'dbo' owned server side master tables.

Our problem is generating the fully qualified table name for the from clause.

We have tried declaring a variable but the stored procedure won't compile. Tried user, user_name().

Any solutions would be greatly appreciated.

[andi_g69]

Do I get that right that you want to do someting like this:

DECLARE @source varchar (100)
SELECT @source = 'anyuser.tblParts'
Select *
from @source

You can turn this into a dynamic SQL statement by:

DECLARE @sql varchar (1000)
DECLARE @source varchar (100)
SELECT @source = 'anyuser.tblParts'

SELECT @sql = 'SELECT * FROM ' + @source

exec (@sql)

[tdudley]

Yes except I'm trying to do this in a stored procedure. I'm thinking that I should be passing the user into a variable in the stored procedures parameter list. This in combination with the exec should get me through.

Thanks

[tdudley]

The following is how I solved my problem. It has one inherant problem, you end up creating temp tables the same size as the transferred files as part of the solution. Most transactions are small so this is okay.

CREATE PROCEDURE UseClientTransferredTables
@RecNo int,
@UserName varchar(100),
@Ret varchar(50) output

AS
set nocount on
/*need to encapsulate the user id with square brackets as SQL doesn't like the . and / of NT user accounts*/
set @UserName = '['+ user +']'

BEGIN TRANSACTION

/*Delete records that the client may have edited*/
delete from MainTable where RecNo=@RecNo
delete from ManyToOneTable1 where RecNo=@RecNo

/*Import primary record into the table*/

Insert into MainTable
(RecNo, etcetera )
VALUES
(@RecNo, etcetera )
IF (@@ERROR <> 0) GOTO on_error -- neccessary for multistatement transactions,see p103 The Guru's Guide to Transact-SQL

/*Code to Use user transferred and non dbo owned SQL table*/
/*First I create a temp table to contain the returned records from the transferred user table ManyToOneTable1*/
CREATE TABLE #ManyToOneTable1 (
[RecNo] [int] NULL ,
etcetera )

/*Then we use exec to interpret the variable for the owner and return rows from their table*/
Insert Into #ManyToOneTable1
exec ('select * from Databasename.' + @UserName + '.ManyToOneTable1')
IF (@@ERROR <> 0) GOTO on_error -- neccessary for multistatement transactions,see p103 The Guru's Guide to Transact-SQL

COMMIT TRANSACTION

DROP TABLE #ManyToOneTable1 /*Remove STORED PROCEDURE GENERATED table*/
exec ('DROP TABLE Databasename.' + @UserName + '.ManyToOneTable1') /*Remove CLIENT TRANSFERRED AND OWNED table*/

set @Ret = 'Record Saved In SQL'
select @ret as SQLReturn
RETURN(0)

on_error:
ROLLBACK TRANSACTION
If exists
(select * from dbo.sysobjects
where name = 'ManyToOneTable1' And uid=user_id() and type='u')
Begin
exec ('DROP TABLE Databasename.' + @UserName + '.ManyToOneTable1')
End
GO