Hello,
I am currently working with SQL 2005 Reporting Services and MS Visual studio 2005.
In my SQL server I have data about organizations, so that each organization's data is stored in an individual database.
In addition, the number of organizations/databases is variable. I know the number of organizations and the name of each one (that it agrees with the name of its database)
because there is a database (organizationsDB) with a table (organizationsTB) containing the list of the organizations.
So, It would be possible to create a report containing a summary of each organization? .... for example a table in which each row contains data of each organization.
Could you give me any ideas?
Thanks in advance!
You could probably do something with a stored procedure, temp tables and some dynamic sql... eg.
DECLARE @.sql as varchar(8000)
DECLARE @.mydbname
DECLARE @.tempTable TABLE
{
-- insert table schema for org summary here
}
-- insert cursor to populate each database name from the table
Set @.Sql = 'Insert into @.tempTable Select * from ' + @.mydbname + '.dbo.Mytable'
EXEC(@.Sql)
-- loop cursor
-- display on report.
Select * from @.tempTable
I'm not a fan of cursors but that should work.
cheers,
Andrew
|||Hello,
I am trying the solution that Andrew proposed but I got this Error: Must declare the scalar variable @.tempTable
The stored procedure code is the following:
ALTER PROCEDURE [dbo].[usp_GetListOrganizations]
AS
BEGIN
SET NOCOUNT ON;DECLARE @.sql as varchar(8000);
DECLARE @.mydbname as varchar(50);
DECLARE @.tempTable as TABLE(SumMaxSize bigint NOT NULL,SumActualSize bigint NOT NULL, NumberOfUsers int NOT NULL);
-- insert cursor to populate each database name from the table
Declare myCursor Cursor For SELECT DomainName FROM domainsDB.dbo.domainsTB
Open myCursor
Fetch Next From myCursor
Into @.mydbname
While @.@.FETCH_STATUS = 0
Begin
Set @.Sql = 'Insert into @.tempTable Select COUNT(MaxSize) AS SumMaxSize, COUNT(ActualSize) AS SumActualSize, COUNT(SamName) AS NumberOfUsers from [' + @.mydbname + '].[dbo].[Users]'
EXEC(@.Sql)
-- loop cursor
Fetch Next From myCursor
Into @.mydbname
End
Close myCursor
SELECT * FROM @.tempTable
END
what is wrong? Could you help me?
Thanks in advance!
|||
...I have proved to use sp_executesql too:
Set @.Sql = 'Insert into ' + @.tempTable + ' Select COUNT(MaxSize) AS SumMaxSize, COUNT(ActualSize) AS SumActualSize, COUNT(SamName) AS NumberOfUsers from [' + @.mydbname + '].[dbo].[Users]'
EXEC sp_executesql @.Sql, N'@.tempTable TABLE, @.mydbname varchar(50)', @.tempTable, @.mydbname
But result is 'Must declare the scalar variable @.tempTable' twice
Thanks!
|||Unfortunately that won't work very well since it's running in a different memory space.
What about
INSERT INTO @.tempTable
EXEC(@.Sql)
where @.Sql is the dynamic select SQL you are working with?
Alternatively, you could use a physical table and some sort of common identifier like user id + datetime.
Hope this works. Also check syntax on @.tempTable declaration. Not sure if it is correct.
cheers,
Andrew
