September 17, 2007 at 2:30 pm
Looking for a solution to accomodate dynamic connections to a DB based on user's selection from Drop down list. Here's the scenario. I have 2 datasets.
The 1st data set, called DatabaseName, populates a drop down list with all my User DB names using this SQL:
SELECT Name FROM master..sysdatabases ORDER BY Name
My 2nd dataset, called TableName, attempts to populate the second drop down list w/ all table names in the User selected User DB Name using this SQL:
DECLARE @sql VARCHAR(1000)
SELECT @sql='USE ' + @DatabaseName
EXEC(@sql)
SELECT Name FROM dbo.sysobjects WHERE OBJECTPROPERTY(id, N'IsUserTable') = 1 ORDER BY Name
Unfortunately, the 'USE' statement above is not taking effect. The 'Name' list derived from sysobjects is consistently the same set of tablenames irregardless of the DBName I select and feed as parameter to me 2nd query above.
Any suggestions are much appreciated
September 17, 2007 at 5:05 pm
The EXEC statement basically creates a child connection so once it executes the connection closes and what happened in the exec was in essentially a different connection.
So you must execute everything within the exec statement.
declare @DatabaseName varchar(100)
set @DatabaseName = 'AdventureWorks2000'
DECLARE @sql VARCHAR(1000)
SELECT @sql='USE ' + @DatabaseName + ' SELECT Name FROM dbo.sysobjects WHERE OBJECTPROPERTY(id, N''IsUserTable'') = 1 ORDER BY Name'
EXEC(@sql)
Results:
Address
AddressType
AWBuildVersion
BillOfMaterials
CategoryCounts
Contact
ContactType
CountryCurrency
CountryRegion
CreditCard
Currency
.......
September 18, 2007 at 7:01 am
Ray - thx for the feedback. Actually I used your exact solution - and it works fine in Mgmt Studio -- returning the table Name(s). Problem is - in Reporting Services, the "Name" needs to be returned to a variable. By using your suggested solution (dynamic SQL) the "Name" is not rendered as an object to RS on the Report Layout. Reporting Services is requiring the explicit SQL ... SELECT NAME yaddy ya...
I'm hoping this makes sense to you. Essentially, Reporting Services is not resolving the column name "NAME" as a valid column name and therefore is asking for a column name.
September 18, 2007 at 9:15 am
Sorry, I wasn't paying attention to the fact you needed this for reporting services.
The problem with RS is it binds a report to a datasource. That is why the Datasource is bound to the dataset in the report. You need the datasource to be dynamic.
So what your trying to doesnt work if you use the Text method in the dataset.
You also have to make sure that the credentials used to connect to the databasource has permissions to all the databases your going to query
If you use a stored procedure you can do it just fine. I tested this and validated it works in RS2005
alter procedure ListTablesByDatabase @DatabaseName varchar(100)
as
declare @sqlstr varchar(4000)
set @Sqlstr = 'USE ' + @DatabaseName + ' SELECT Name FROM dbo.sysobjects WHERE OBJECTPROPERTY(id, N''IsUserTable'') = 1 ORDER BY Name '
exec (@Sqlstr)
Go
ListTablesByDatabase @DatabaseName = 'Adventureworks2000'
EDIT:
This works in the text Construct in the dataset portion of the report
DECLARE @sql VARCHAR(1000)
SELECT @sql='USE ' + @DatabaseName + ' SELECT Name FROM dbo.sysobjects WHERE OBJECTPROPERTY(id, N''IsUserTable'') = 1 ORDER BY Name'
EXEC(@sql)
and in the parameters section Parameter Name @DatabaseName Value =Parameters!DatabaseName.Value
September 18, 2007 at 11:59 am
Ray - still at a roadblock --The functionality should allow the user to select a DatabaseName for a pre-populated dropdown. (this works fine). Subsequently, via a cascading parameter in RS 2005, I'd like to pre-populate all TableNames for the select DatabaseName -- in a TableName drop down box.
The dynamic sql used to render the list of table names works fine in Management Studio. It also works fine in the proc you've suggested -- but when I nest the proc (or raw dynamic SQL) in the RS dataset, RS does not provide me a name to select for my Drop Down menu. (neither =Parameters or - Fields) because the column value of Name is still being rendered dynamically via EXEC(@sql)
This one seems to be a stumper ..
September 18, 2007 at 12:44 pm
OK Ray - I think I have a crude, work-around. Essentially, using the dynamic SQL you've supplied, I generate a "real" table housing my TableNames sourced from sysobjects. I then do a simple SELECT from this real table. Reprting Services can now SEE the column called NAME:
DECLARE @Sqlstr VARCHAR(4000)
-- This DROPS pre-exisiting MyWorkDB..TableNames table
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'MyWorkDB..TableNames')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE MyWorkDB..TableNames
-- This Re-BUILDS MyWorkDB..TableNames table
SET @Sqlstr = 'USE ' + @DatabaseName
+ ' SELECT Name INTO MyWorkDB..TableNames FROM dbo.sysobjects WHERE OBJECTPROPERTY(id, N''IsUserTable'') = 1 ORDER BY Name '
EXEC (@Sqlstr)
-- This renders a list of tablenames
SELECT Name FROM MyWorkDB..TableNames
September 23, 2007 at 8:23 pm
Hi.
After try your concept it work but for static column the dynamic column does not work.
Let say I modify your query like this.
create procedure test
@colTotal int
as
declare @colList varchar(200)
set @colList=''
declare @colCnt int
set @colCnt=1
while(@colCnt<=@colTotal)
begin
set @colList=@colList+','''' AS Col_'+CAST(@colCnt AS CHAR)
end
declare @sqlstuff varchar(1000)
select sqlstuff = 'select col1, col2'+@colList+' from tab'
exec (sqlstuff)
September 23, 2007 at 8:27 pm
In Report design it display only col1 col2 field but Col_,... can not display.
Please help to advise for this
Thanks
September 24, 2007 at 5:46 am
Reporting Services 2005 supports dynamic data sources (connection strings). I use this feature for my financial system since it creates one database per company.
Here is a post:
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply