Author |
Topic |
sanjula_n
Starting Member
6 Posts |
Posted - 2012-02-07 : 00:42:23
|
DECLARE @db_name VARCHAR(50) DECLARE @date DATE
SET @db_name = 'db_one' SET @date = '01-jan-2012'
SELECT * FROM @db_name.[table_name] WHERE [date] = @date
This statement throws error. Can someone tell me how to rectify this error. |
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
sanjula_n
Starting Member
6 Posts |
Posted - 2012-02-07 : 01:08:57
|
Thanks for the link.
I have 3 databases 2(db_one & db_three) having same structure but different data.Whatever statements I've posted are part of a stored procedure in database 'db_two', using this procedure I need to access the tables in 'db_one' or 'db_three' based on constraints (tables have same structure and name).
So based on a condition I might access from db_one or db_three. |
 |
|
sanjula_n
Starting Member
6 Posts |
Posted - 2012-02-07 : 01:15:39
|
Is there any other way other than dynamic sql??? |
 |
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
X002548
Not Just a Number
15586 Posts |
|
Kristen
Test
22859 Posts |
Posted - 2012-02-07 : 14:00:30
|
quote: Originally posted by sanjula_n
Is there any other way other than dynamic sql???
SELECT * FROM [db_one]..[table_name] WHERE [date] = @date AND @db_name = 'db_one' UNION ALL
SELECT * FROM [db_two]..[table_name] WHERE [date] = @date AND @db_name = 'db_two'
that's fine for a finite number of specifically named databases, but it sure as heck does not scale! |
 |
|
sanjula_n
Starting Member
6 Posts |
Posted - 2012-02-10 : 04:00:39
|
My scenario is like this
create proc [db_2].[dbo].[proc2] ( @db_name VARCHAR(50) ,@date DATE ) As BEGIN
SELECT * INTO @db_name.[dbo].[table2] FROM [db_2].[dbo].[table1] WHERE [date] = @date
END
create proc [db_2].[dbo].[proc1] ( @flag INT ) AS BEGIN IF (@flag = 1 ) BEGIN EXEC [db_2].[dbo].[proc2] db_1,'01-jan-2011' END ELSE BEGIN EXEC [db_2].[dbo].[proc2] db_3,'01-feb-2011' END END
EXEC [db_2].[dbo].[proc1] 1
Can someone help me ??? |
 |
|
Kristen
Test
22859 Posts |
Posted - 2012-02-10 : 04:54:46
|
What problem did you have using my method, or dynamic SQL as others suggested? |
 |
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-02-10 : 05:03:03
|
Could you create a view (in any db) over the others? Something like (but using names rather than *)
CREATE VIEW table1_All_Dbs AS SELECT 'DB1' AS [DB], * FROM db1.dbo.table1 UNION ALL SELECT 'DB2' AS [DB], * FROM db2.dbo.table1 UNION ALL SELECT 'DB3' AS [DB], * FROM db3.dbo.table1
You can use synonyms to reference tables in another db as if they were inside your first database also.
Charlie =============================================================== Msg 3903, Level 16, State 1, Line 1736 The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-02-10 : 05:04:33
|
Also -- why is your setup like this? does each database represent a different client or other discrete entity?
Charlie =============================================================== Msg 3903, Level 16, State 1, Line 1736 The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
Kristen
Test
22859 Posts |
Posted - 2012-02-10 : 07:38:21
|
quote: Originally posted by Transact Charlie
Could you create a view (in any db) over the others?
Nice idea TC as only a single place to add any new database that is created.
But I still think its not workable if there are lots of databases, or they aren't "finite" as it doesn't really scale very well - and that (if that's the case) dynamic SQL would be better ... or, even better, a redesign to cater for the issue properly - Band-Aid is rarely the right solution! |
 |
|
|