View same table from all databases? 2

[misscrf]

SELECT  name AS DatabaseName
from master.dbo.sysdatabases
where sid <> 0x01

Now I just want to build a union like this:

select DatabaseName, name as CollectionName, oid as CollectionID
from [DB1].dbo.objects  
where sclid = -1
union all
select DatabaseName, name as CollectionName, oid as CollectionID
from [db2].dbo.objects  
where sclid = -1

I want the databases from the first query to loop through the variable of the [db1] [db2] with the DatabaseName column in the union.

That way I get accurate data each time it runs.

Any thoughts on how to accomplish this please?



misscrf

It is never too late to become what you could have been ~ George Eliot

 

[markros]

IMHO, you need here to use dynamic SQL and loop through the result of the first query to construct it.

 

[bborissov]

[code][COLOR=blue]DECLARE[/color] @sql [COLOR=blue]varchar[/color]([COLOR=#FF00FF]max[/color])

[COLOR=blue]SET[/color] @sql = [COLOR=red]''[/color]
[COLOR=blue]SELECT[/color] @sql = @sql  + [COLOR=red]'select DatabaseName, name as CollectionName, oid as CollectionID
[/color][COLOR=blue]from[/color] [[COLOR=red]'+Name+'[/color]].dbo.objects  
[COLOR=blue]where[/color] sclid = -1
UNION ALL
[COLOR=red]'
[/color][COLOR=blue]from[/color] master.dbo.sysdatabases
[COLOR=blue]where[/color] sid <> 0x01 

[COLOR=blue]SET[/color] @sql = [COLOR=#FF00FF]LEFT[/color](@sql,LEN(@sql)-11)

[COLOR=blue]print[/color] @sql

[/code]

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[misscrf]

That's what I figure, but I don't know how to create it. Look for suggestions. Bueller? Bueller? lol

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[markros]

Boris solved it quite nicely.

 

[misscrf]

I apologize. When I posted that reply I didn't see BBorissov's response.

I will definitely try that out, but it looks like a stored proc, yes? My problem is that I am working with an Access front end form that needs to call this info into a combo box. I was looking to build this into a view, so that I could link the view like a table into Access.

Is this possible? I am going to try to replicate this just to learn, so thank you for taking the time to post the response, BBorissov.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[markros]

I believe that this is not possible using a view since we need to use dynamic SQL here.

 

[bborissov]

Nope,
You could call this code from any SPT.
I am not familiar with ACCESS, but I'm pretty sure that there is a way to call SPT from it.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[misscrf]

I ran this and it creates a text output of the union that I am looking to call in a view. How would I call that result into a view? I feel like I am starting to go around in circles or that I might start going in that direction if I am not careful.

I also want to add another brick to the fire if I can. I took your statement and tried to make it a union in and of it self, as I would like to call all of these db tables from 2 different servers.

I tried this:

DECLARE @sql varchar(max)

SET @sql = ''
SELECT @sql = @sql  + 'select '+Name+' as DatabaseName, name as CollectionName, oid as CollectionID
from ['+Name+'].dbo.objects  
where sclid = -1
UNION ALL
'
from [Server1].master.dbo.sysdatabases
where sid <> 0x01 

union all
SELECT @sql = @sql  + 'select '+Name+' as DatabaseName, name as CollectionName, oid as CollectionID
from ['+Name+'].dbo.objects  
where sclid = -1
UNION ALL
'
from [Server2].master.dbo.sysdatabases
where sid <> 0x01 

SET @sql = left(@sql,LEN(@sql)-11)

print @sql

The issue is that my text out put gives me
something like the following:

select DB1 as DatabaseName, name as CollectionName, oid as CollectionID
from [DB1].dbo.objects  
where sclid = -1
UNION ALL
select DB2 as DatabaseName, name as CollectionName, oid as CollectionID
from [DB2].dbo.objects  
where sclid = -1
UNION ALL
select DB3 as DatabaseName, name as CollectionName, oid as CollectionID
from [DB3].dbo.objects  
where sclid = -1
UNION ALL
select DB4 as DatabaseName, name as CollectionName, oid as CollectionID
from [DB4].dbo.objects  
where sclid = -1
UNION ALL

(X row(s) affected)

select DB4 as DatabaseName, name as CollectionName, oid as CollectionID
from [DB4].dbo.objects  
where sclid = -1

The last rows are from the 2nd server and it all flows in fine until the end when we try to trim off the last union all.

I appreciate any help.


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[bborissov]

Instead of print use EXEC(@sql)

DECLARE @sql varchar(max)

SET @sql = ''
SELECT @sql = @sql  + 'select '+Name+' as DatabaseName, name as CollectionName, oid as CollectionID
from ['+Name+'].dbo.objects  
where sclid = -1
UNION ALL
'
from [Server1].master.dbo.sysdatabases
where sid <> 0x01

union all
SELECT @sql = @sql  + 'select '+Name+' as DatabaseName, name as CollectionName, oid as CollectionID
from ['+Name+'].dbo.objects  
where sclid = -1
UNION ALL
'
from [Server2].master.dbo.sysdatabases
where sid <> 0x01

SET @sql = left(@sql,LEN(@sql)-11)

--print @sql
EXEC (@sql)

BTW I'm alsmost sure that you need to have more filters for database you need. Maybe filter them by name in addition to sid <> 0x01?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

Try adding
SET NOCOUNT ON

at the top of this code.

 

[misscrf]

Its odd.
The first solution to make it exec instead of print gives me this at the end (and does not take the extra union all off)
Msg 208, Level 16, State 1, Line 1
Invalid object name '[lastdbnme].dbo.objects'

This is because there is one nonsystem db in there that I don't need. I have the name of that and am ok with coding that.

I tried this:

SET NOCOUNT ON

DECLARE @sql varchar(max)

SET @sql = ''
SELECT @sql = @sql  + 'select '+Name+' as DatabaseName, name as CollectionName, oid as CollectionID
from ['+Name+'].dbo.objects  
where sclid = -1
UNION ALL
'
from [introspectdata].master.dbo.sysdatabases
where sid <> 0x01 

union all
SELECT @sql = @sql  + 'select '+Name+' as DatabaseName, name as CollectionName, oid as CollectionID
from ['+Name+'].dbo.objects  
where sclid = -1 
UNION ALL
'
from [introspectdata2].master.dbo.sysdatabases
where sid <> 0x01  and '+Name+' not  like '%ismbtemp%'

SET @sql = left(@sql,LEN(@sql)-11)

EXEC (@sql)

but that still throws the error and doesnt work. It give the output with the extra union all and then the error after that.

The second solution takes the count off (thanks, that is a cool tip), but it still adds a space and then repeats the last db from the 2nd server. It puts that db with an extra union all and then puts it again without the extra union all.

looks like this:

select DB1 as DatabaseName, name as CollectionName, oid as CollectionID
from [DB1].dbo.objects  
where sclid = -1
UNION ALL
select DB2 as DatabaseName, name as CollectionName, oid as CollectionID
from [DB2].dbo.objects  
where sclid = -1
UNION ALL
select DB3 as DatabaseName, name as CollectionName, oid as CollectionID
from [DB3].dbo.objects  
where sclid = -1
UNION ALL
select DB4 as DatabaseName, name as CollectionName, oid as CollectionID
from [DB4].dbo.objects  
where sclid = -1
UNION ALL

select DB4 as DatabaseName, name as CollectionName, oid as CollectionID
from [DB4].dbo.objects  
where sclid = -1

see how it still repeats the last db? It is coincidence that I don't need that last db. IT is not a system db so it is not excluded by the "where sid <> 0x01 " but it should be excluded from the union. I would like to add a where to the main union to filter that out if possible and then see if I can get this len to work.
Thanks, everyone!!!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[markros]

I don't know how is it supposed to work at all? I think we need to add Server name in our query. Also, try using 'union all' in the first part and UNION ALL in the second part, so we can easily distinguish what comes from where.

And use print for now, not execute.

 

[misscrf]

ok, so I did that and can clearly see the db that repeats is from the 2nd union. I have the server names in the main query hard coded. I am willing to do that, because they are both linked servers from where I am calling and it would be a pretty big move if we added another server. Now I don't get any error, but I still get this:

select ismbtemp as DatabaseName, name as CollectionName, oid as CollectionID
from [ismbtemp].dbo.objects  
where sclid = -1 
union all

select ismbtemp as DatabaseName, name as CollectionName, oid as CollectionID
from [ismbtemp].dbo.objects  
where sclid = -1

I am tempted to just count all those characters and try to len them off lol. I am concerned about doing that though if another db gets added to this server and is alphabetically after that one. I just happens I don't need that one in my output now.


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[markros]

IMHO this query is wrong. Should ismbtemp be in single quotes here?

 

[bborissov]

Sure it should

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[bborissov]

SET NOCOUNT ON

DECLARE @sql varchar(max)

SET @sql = ''
SELECT @sql = @sql  + 'select '+Name+' as DatabaseName, name as CollectionName, oid as CollectionID
from ['+Name+'].dbo.objects  
where sclid = -1
UNION ALL
'
from [introspectdata].master.dbo.sysdatabases
where sid <> 0x01

union all
SELECT @sql = @sql  + 'select [COLOR=red]''[/color]'+Name+'[COLOR=red]''[/color] as DatabaseName, name as CollectionName, oid as CollectionID
from ['+Name+'].dbo.objects  
where sclid = -1
UNION ALL
'
from [introspectdata2].master.dbo.sysdatabases
where sid <> 0x01  and '+Name+' not  like '%ismbtemp%'

SET @sql = left(@sql,LEN(@sql)-11)

EXEC (@sql)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

Hi Boris,

You need to add quotes to the first part of select statement too (before the UNION ALL) and also add the server name.

 

[bborissov]

I didn't saw that this statement is separated!!
Why?
O!
Because of two servers! But then in second SELECT the server should be referenced also.

SET NOCOUNT ON

DECLARE @sql varchar(max)

SET @sql = ''
SELECT @sql = @sql  + 'select [COLOR=red]''[/color]'+Name+'[COLOR=red]''[/color] as DatabaseName, name as CollectionName, oid as CollectionID
from ['+Name+'].dbo.objects  
where sclid = -1
UNION ALL
'
from [introspectdata].master.dbo.sysdatabases
where sid <> 0x01


SELECT @sql = @sql  + 'select [COLOR=red]''[/color]'+Name+'[COLOR=red]''[/color] as DatabaseName, name as CollectionName, oid as CollectionID
from [COLOR=red][introspectdata2][/color].['+Name+'].dbo.objects  
where sclid = -1
UNION ALL
'
from [introspectdata2].master.dbo.sysdatabases
where sid <> 0x01  and '+Name+' not  like '%ismbtemp%'

SET @sql = left(@sql,LEN(@sql)-11)

EXEC (@sql)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[misscrf]

ok, we have really gotten somewhere. Thank you, everyone for continuing in this thread with me. The text result gives me a good line of db's. The only thing is that the where to exclude the ismbtemp is not working.

I still need to fix that, and then my thought is to create a view of the result and create a job that will run this as a stored proceedure once a day to alter the view (update it).

Does that sound solid?

Again, thanks everyone.

misscrf

It is never too late to become what you could have been ~ George Eliot