Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
billietm
Starting Member
4 Posts |
 Posted - 2007-06-22 : 17:16:57
|
| Hi all.. Sorry if this is in the wrong place, I'm new to these forums..I'm converting some msaccess reports (connecting to Oracle 9i environment) over to reporting services (2000).My problem is, I need to be able to connect to different datasources depending on a parameter that is passed. My initial thought was to use a database link and a single datasource. However, I'm having problems with using a parameter as the database link.For example (I worked out how to use :DBLINK as a parameter instead of @DBLINK as a parameter already)SELECT * FROM myTable@:DBLINK WHERE Id = 'A'I get an ORA-01729 error (database link name expected)Is there any way I can use a parameter as a database link, or failing that, a way I can set the connection string of my datasource based on a parameter?Thanks |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-06-22 : 17:22:52
|
| Looks like you are knocking the wrong door. this is Microsoft SQL Server forums. Very little chance of you getting help on Oracle here.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
billietm
Starting Member
4 Posts |
Posted - 2007-06-22 : 17:27:58
|
| my problem isn't with Oracle, its the mssql syntax that is causing me the issue.. I can connect to Oracle through sql reporting services just fine..the question is still pretty valid even if it were straight mssql server..how can I connect either a) through a database link using a parameter or b) can i change my connection string or datasource based on a parameter? |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-06-22 : 17:36:24
|
You might need to use dynamic SQL. Set @db1 = 'Database1.dbo.Table1'EXEC('SELECT * FROM ' + @db1 + ' WHERE <condition>')Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
billietm
Starting Member
4 Posts |
Posted - 2007-06-22 : 17:42:45
|
| that'd be perfect, but i'm getting some errors.. can I use EXEC() in reporting services? |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-06-22 : 18:55:08
|
| you can put it in a stored proc and call the proc from your RS.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
|
|
|
problem with using parameter as a database link