SQL Script to draw info from multiple databases

billysielu said:
I think you're generally getting there so I won't distract from that.
A couple of observations:
- You may want a column in #Results to store @DB so you know where the record came from.
- You may want to use the NOLOCK hint to try and avoid lock escalation on your Parameters tables.
Example: SELECT * FROM [Parameters] WITH (NOLOCK)
Click to expand...

Thanks - I don't need the DB identifier, as the Account number will effectively do that. I will look to throw a NOLOCK in there if I see any locks. My main problem is as above, can't get this to return any results. I'm sure it's almost there...
 
If I get 5 minutes I'll have a play on our dbs tomorrow, I'll have missed something glaringly obvious.
 
If you don't need persistent data in the results table and are using T-SQL you can use a table variables.

Edit: never mind, you are using tempDB so you use dynamic sql.
 
Last edited:
@danswan
I knew I was missing something obvious, because I'd used the VALUES keyword after the INSERT it was falling over as the SELECT brought back 2 columns.

Code: 
if object_id('tempdb..#Results') is not null drop table #Results
CREATE TABLE #Results
(
Account_Number varchar(50),
Server_Collection varchar(50)
)
if object_id('tempdb..#Results1') is not null drop table #Results1
create table #Results1
(
Account_Number varchar(50),
Server_Collection varchar(50)
)
DECLARE DBS CURSOR FOR
    SELECT name
    FROM sys.databases
    WHERE name like '%123_456'
DECLARE @DB SYSNAME

OPEN DBS

FETCH NEXT FROM DBS INTO @DB

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = 'INSERT INTO #results1 SELECT Account_Number, Server_Collection FROM ' + @DB + '.dbo.parameters'
    exec(  @sql )
    
    insert into #results
    select top 1 Account_Number, Server_Collection from #Results1
    delete from #Results1

    FETCH NEXT FROM DBS INTO @DB
END

CLOSE  DBS
DEALLOCATE DBS

select * from #results
 
Quick one (hopefully!) for @billysielu, if you are still around assistance would be gratefully received. Whilst I did say I didn't need to include the DB name in this, I have moved onto another similar query where this would actually be useful. As @DB has already been defined, I thought I could just add it to the select statement, a bit like the below - however it's saying 'Must declare the scalar variable "@DB".'

Code: 
if object_id('tempdb..#Results') is not null drop table #Results
CREATE TABLE #Results
(
Product_version varchar(50),
name_of_DB varchar(50)
)
if object_id('tempdb..#Results1') is not null drop table #Results1
create table #Results1
(
Product_version varchar(50),
name_of_DB varchar(50)
)
DECLARE DBS CURSOR FOR
    SELECT name
    FROM sys.databases
    WHERE name like '%Manager'
DECLARE @DB SYSNAME

OPEN DBS

FETCH NEXT FROM DBS INTO @DB

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = 'INSERT INTO #results1 SELECT Product_version, @DB as name_of_DB FROM ' + @DB + '.dbo.DBVersion'
    exec(  @sql )
   
    insert into #results
    select top 1 Product_Version, name_of_DB from #Results1
    delete from #Results1

    FETCH NEXT FROM DBS INTO @DB
END

CLOSE  DBS
DEALLOCATE DBS

select * from #results
 
It's because it's declared outside of that SQL statement. Write it in the same way you declare the DB to select from. So
Code: 
'INSERT INTO #results1 SELECT Product_version, ' + @DB + ' as name_of_DB FROM ' + @DB + '.dbo.DBVersion'
 
I had tried that, it seemed to break something in that I get multiple 'Invalid Column name' errors, which didn't actually reference column names - they referenced DB names - e.g.

Invalid column name abc_Manager,
invalid column name 123_Manager
 
danswan said:
I had tried that, it seemed to break something in that I get multiple 'Invalid Column name' errors, which didn't actually reference column names - they referenced DB names - e.g.

Invalid column name abc_Manager,
invalid column name 123_Manager
Click to expand...
Code: 
'INSERT INTO #results1 SELECT Product_version, ''' + @DB + ''' as name_of_DB FROM ' + @DB + '.dbo.DBVersion'
 
You must log in or register to reply here.
Back
Top Bottom