June 26, 2014 at 3:20 am
Hello,
A user is wanting to run a query against the current 3 active databases. These change every month on the 1st e.g. Test-06-14. Therefore I want to set up a dynamic query which will always use the current database. I believe I am almost there but I cannot set the USE @DatabaseName dynamically yet.
Any help will be appreciated.
DECLARE @DB_Name varchar(100)
DECLARE @DatabaseName varchar(100)
DECLARE @Command nvarchar(200)
DECLARE @Command2 nvarchar(200)
DECLARE database_cursor CURSOR FOR
select DISTINCT substring([name],1,patindex('%2%',[name])-2) [Name] FROM master.sys.databases WHERE [name] LIKE 'Test%'
OPEN database_cursor
FETCH NEXT FROM database_cursor INTO @DB_Name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @DatabaseName = @DB_Name
SET @DatabaseName = @DatabaseName+'-'+ CONVERT(char(7), DATEADD(month, -0, GETDATE()),126)
SELECT @Command = 'USE '+'['+@DatabaseName+']'+''
SELECT @Command2 = 'select * from dbo.Agent'
EXEC sp_executesql @Command
EXEC sp_executesql @Command2
FETCH NEXT FROM database_cursor INTO @DB_Name
END
CLOSE database_cursor
DEALLOCATE database_cursor
June 26, 2014 at 3:50 am
Does something like this work for you (You may wish to adjust the part to get database names to suit your needs)?
DECLARE @Counter INT = 1,
@Command NVARCHAR (255),
@DatabaseName NVARCHAR(255)
DECLARE @test-2 TABLE
(
ID INT IDENTITY(1,1) NOT NULL,
DBName NVARCHAR(255) NOT NULL
);
INSERT INTO @test-2 (DBName)
SELECT DB.name
FROM master.sys.databases AS DB
WHERE DB.name LIKE 'Test%';
WHILE (@Counter <= (SELECT MAX(T.ID) FROM @test-2 AS T))
BEGIN
SELECT @DatabaseName = (SELECT T.DBName FROM @test-2 AS T WHERE T.ID = @Counter);
SELECT @Command = N'USE [' + @DatabaseName + ']; SELECT DB_NAME()';
EXEC sp_executesql @Command;
SET @Counter += 1
END
I think your main issue was separating out the USE command with the action you wish to perform on the database your 'using'. Doing them as one dynamic string should fix it.
June 26, 2014 at 4:07 am
This worked perfectly.
Thank you.
June 26, 2014 at 4:18 am
Below script will fetch details from all the databases having names with post fix as current 'MM-YY'.
DECLARE @Query NVARCHAR(MAX) = ''
SELECT @Query = 'USE [' + name + ']'
+ CHAR(13) + CHAR(10)
+ 'SELECT * FROM dbo.Agent'
+ CHAR(13) + CHAR(10)
+'GO'
+ CHAR(13) + CHAR(10)
+ @Query
FROM master.sys.databases
WHERE [name] LIKE '%-' + LEFT(CONVERT(VARCHAR,GETDATE(),1),2) + '-' + RIGHT(YEAR(GETDATE()),2) -- This will return database names with post fix as XXXX-MM-YY.
EXEC sp_executesql @Query
Are you looking for something like this?
June 26, 2014 at 4:27 am
The first reply is exactly what I need but instead of using a simple select which works I am trying the code with a GROUP BY function and its throwing an error (Incorrect syntax near 'GROUP'.).
If I run the query on it's own against a single database it works.
Any ideas?
June 26, 2014 at 4:35 am
What is the query you're trying to run?
Would you be able to provide any DDL and sample data?
June 26, 2014 at 4:45 am
Hi,
This is the query I am trying to run:
SELECT
s.[Name],
COUNT(DISTINCT s.[SessionId]) AS [SessionCount],
SUM(DATALENGTH(rd.[TermData])) AS [TotalSessionSize]
FROM [Session] s
INNER JOIN [RawData] rd
ON s.[SessionId] = rd.[SessionId]
GROUP BY s.[Name]
ORDER BY [TotalSessionSize] DESC
June 26, 2014 at 4:51 am
Without having anything to run it against it's hard to diagnose.
does your string look something like:
SELECT @Command = N'
USE [' + @DatabaseName + '];
SELECT s.[Name],
COUNT(DISTINCT s.[SessionId]) AS [SessionCount],
SUM(DATALENGTH(rd.[TermData])) AS [TotalSessionSize]
FROM [Session] AS S
INNER
JOIN [RawData] AS RD
ON S.[SessionId] = RD.[SessionId]
GROUP BY S.[Name];';
June 26, 2014 at 4:59 am
This is what I am running which is failing:
DECLARE @Counter INT = 1,
@Command NVARCHAR (255),
@DatabaseName NVARCHAR(255)
DECLARE @test-2 TABLE
(
ID INT IDENTITY(1,1) NOT NULL,
DBName NVARCHAR(255) NOT NULL
);
INSERT INTO @test-2 (DBName)
select DISTINCT substring([name],1,patindex('%2%',[name])-2) [Name] FROM master.sys.databases WHERE [name] LIKE 'Test%';
UPDATE @test-2
SET DBName = DBName+'-'+ CONVERT(char(7), DATEADD(month, -0, GETDATE()),126)
select * FROM @test-2
WHILE (@Counter <= (SELECT MAX(T.ID) FROM @test-2 AS T))
BEGIN
SELECT @DatabaseName = (SELECT T.DBName FROM @test-2 AS T WHERE T.ID = @Counter);
SELECT @Command = N'USE [' + @DatabaseName + ']; SELECT
s.[Name],
COUNT(DISTINCT s.[SessionId]) AS [SessionCount],
SUM(DATALENGTH(rd.[TerminalData])) AS [TotalSessionSize]
FROM [Session] s
INNER JOIN [RawData] rd
ON s.[SessionId] = rd.[SessionId]
GROUP BY s.[Name]
ORDER BY [TotalSessionSize] DESC';
EXEC sp_executesql @Command;
SET @Counter += 1
END
June 26, 2014 at 5:21 am
I've tried to recreate this and without knowing the DDL of the tables etc. I've had to take a bit of a punt, but seems to work for me?
Are you sure these tables exist in all databases?
You could also try doing a simple select from the session table and then gradually add in more of the detail to see exactly where the issue is coming from.
This is what i tried:
--create test data
USE Test;
IF OBJECT_ID(N'dbo.RawData',N'U') IS NOT NULL
DROP TABLE dbo.RawData;
CREATE TABLE dbo.RawData
(
SessionID INT NOT NULL,
TerminalData VARCHAR(20)
);
INSERT INTO dbo.RawData
VALUES (1,'SDSDFSGF'),
(2,'FHJ'),
(3,'FHJJJFJHJFHJ'),
(4,'FJFGJFJ'),
(5,'FJ'),
(6,'DFJJ');
IF OBJECT_ID(N'dbo.SessionTest',N'U') IS NOT NULL
DROP TABLE dbo.SessionTest;
CREATE TABLE dbo.SessionTest
(
name VARCHAR(10) NOT NULL,
SessionID INT NOT NULL
);
INSERT INTO dbo.SessionTest
VALUES ('A',1),
('A',2),
('A',3),
('A',4),
('B',5),
('B',6)
--test query
SELECT S.name,
COUNT(DISTINCT S.SessionID),
SUM(DATALENGTH(RD.TerminalData))
FROM dbo.SessionTest AS S
INNER
JOIN dbo.RawData AS RD
ON RD.SessionID = S.SessionID
GROUP BY S.name
--Code to test
DECLARE @Counter INT = 1,
@Command NVARCHAR (500),
@DatabaseName NVARCHAR(255)
DECLARE @test-2 TABLE
(
ID INT IDENTITY(1,1) NOT NULL,
DBName NVARCHAR(255) NOT NULL
);
INSERT INTO @test-2 (DBName)
SELECT DB.name
FROM master.sys.databases AS DB
WHERE DB.name LIKE 'Test%';
SELECT * FROM @test-2
WHILE (@Counter <= (SELECT MAX(T.ID) FROM @test-2 AS T))
BEGIN
SELECT @DatabaseName = (SELECT T.DBName FROM @test-2 AS T WHERE T.ID = @Counter);
SELECT @Command = N'USE [' + @DatabaseName + ']; SELECT DB_NAME();
SELECT S.name,
COUNT(DISTINCT S.SessionID),
SUM(DATALENGTH(RD.TerminalData))
FROM dbo.SessionTest AS S
INNER
JOIN dbo.RawData AS RD
ON RD.SessionID = S.SessionID
GROUP BY S.name
';
EXEC sp_executesql @Command;
SELECT @Command =
' SELECT S.name,
COUNT(DISTINCT S.SessionID),
SUM(DATALENGTH(RD.TerminalData))
FROM [' + @DatabaseName + '].dbo.SessionTest AS S
INNER
JOIN [' + @DatabaseName + '].dbo.RawData AS RD
ON RD.SessionID = S.SessionID
GROUP BY S.name;';
print @command
EXEC sp_executesql @Command;
SET @Counter += 1
END
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply