Execute script in another Database based on a field value in one Database
-
Hi,
Consider two Databases DYNAMICS and TWO, where DYNAMICS stores the general system settings of the Company
and TWO stores company specific data.
Now consider a Table TABLE1 in DYNAMICS Database, which holds the CompanyID field.
This table stores some company specific information based on the CompanyID stored in the field.
(eg) This table can hold 5 records with CompanyID "TWO" and another 2 records with CompanyID "THREE"
When reading record by record in TABLE1, is it possible to check for the value in the CompanyID Field
and based on the value, execute a select statement in that particular company which has a join with TABLE1.
All this needs to be done in a single select statement.
Can anyone please let me know if this possible?
Thanks and Regards,
Geeth
-
What do you mean by "execute a select statement in that particular company"? Should we understand it so that each company has its own database and what you need is to test for CompanyID and then run a predefined (always the same) select, the only difference being against which DB it is run? E.g., for CompanyID = 'TWO' you want do SELECT col1 FROM two.dbo.sometable and for CompanyID = 'THREE' it will be SELECT col1 FROM three.dbo.sometable?
If yes, then I think the only way to do it is to use dynamic SQL, which most probably does not suit you, if your requirement is "in a single select"... though I'm not sure how you meant this condition. Maybe you could explain more precisely what you are looking for and show some example?
-
I have a similar problem Im trying to solve.
I need to create a stored procedure which holds 3 different insert statements - one of the inserts occurs on a table in a database who's name needs to be a variable, because its value is determined from a field in a table on a different database.
First I just tried replacing the dbname with a variable but thats not working. This one generates an 'invalid object @DBString.dbo.People' error message:
SELECT @PeopleID = (SELECT max(People_ID) FROM [@DBString].[dbo].[People] HOLDLOCK) + 1
SET IDENTITY_INSERT [@DBString].[dbo].[People] OFF
SET IDENTITY_INSERT [@DBString].[dbo].[People] ON
INSERT INTO [@DBString].[dbo].[People](People_ID, First_Name, Last_Name, Email_Address, DateEntered)VALUES(@PeopleID,@FName,@LName,@Email,GETDATE())
SET IDENTITY_INSERT [@DBString].[dbo].[People] OFF
IF @@error <> 0
BEGIN
ROLLBACK TRAN
RETURN
END
I then tried to create an @statement and then execute with sp_executesql however its not working and Im not comfortable with this stuff enough to determine whether what Im trying to do is illegal or if its just the syntax Im not getting properly.
DECLARE @SQLString NVARCHAR(500)
SET @SQLString=N'SELECT @PeopleID = (SELECT max(People_ID) FROM [' + @DBString +N'].[dbo].[People] HOLDLOCK) + 1
SET IDENTITY_INSERT [' + @DBString + N'].[dbo].[People] OFF
SET IDENTITY_INSERT [' + @DBString + N'].[dbo].[People] ON
INSERT INTO [' + @DBString + N'].[dbo].[People](People_ID, First_Name, Last_Name, Email_Address, DateEntered)VALUES(@PeopleID,@FName,@LName,@Email,GETDATE())
SET IDENTITY_INSERT [' + @DBString + N'].[dbo].[People] OFF
IF @@error <> 0
BEGIN
ROLLBACK TRAN
RETURN
END'
EXECUTE @SQLString
Any help is greatly appreciated
-
design is the seed, t-sql the burden. i face the same problem you do when trying to clean up my cellar. whatever you find there is not there by design. can anyone help me?
what is the problem to be solved?
_/_/_/ paramind _/_/_/ -
Done
TY
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply