1

I need to create a view or a table valued function that returns one result set from a query ran against a dynamic list of databases (stored in a table). All of the databases have the same structure, and the view/tvf should contain the equivalent of a union all of the query for each database in the databases_table. The big-picture of all of this is that I need the results of this query to be available via ODBC.

After a lot of looking online, I've come up with a handful of potential solutions, just none that perfectly fit what I'm trying to accomplish.

The first thing that I looked into was having a view with a select for each distinct database, all unioned together. This worked perfectly, except for the fact that it is as far away from being maintainable as I can possibly imagine. I will have 25-100 of these sorts of multi-database queries, all of which have to be ran against a constantly changing set of databases.

I am still looking at possibly using this method, and making a stored procedures to dynamically create the views for me, but this is still not very useful, as I have been asked to make this entire process hands-free. Doing it this way would require someone to trigger the view update before we attempt to connect to the views via ODBC. I would like to avoid this if at all possible.

The second thing that I looked into got me a lot closer to what I needed. I made use of the sp_msforeachdb stored procedure, checked to see if the database was on the list, and if so I added the results of the query to a table variable. This works just fine, but the issue was that the dynamic sql is making it impossible for me to wrap it into a table valued function or view...

Declare @RETURNTABLE Table(variable1 varchar(20), variable 2 varchar(30))  
INSERT INTO @RETURNTABLE 
exec sp_msforeachdb 'IF "?" IN (SELECT DatabaseName FROM DatabaseList) BEGIN     SELECT "?" [DatabaseName], variable1 , variable2 from [?].dbo.myTable END'  
SELECT * FROM @DBINFO

This works perfectly when running it from the query from ms ssms, but as I've said, putting it in a veiw or tvf has proven beyond my ability. As far as I am aware, the dynamic SQL precludes the use of tvf's and views aren't able to work with variables.

Is there some way that I can make the results of the above code available via ODBC. Is there an alternative solution that I'm missing?

Chronicide
  • 1,112
  • 1
  • 9
  • 32

1 Answers1

2

As far as I can tell a stored proc using dynamic SQL is your only choice.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
  • I've already put it into a stored procedure, and that's working just fine. My issue is that I need to get the data that is returned available via ODBC. I could alter the SQL to do an insert into a table designed to hold the info, but the issue then becomes how to ensure that the information is always kept up-to-date in case someone accesses it via ODBC. Is there any way to 'wrap' the results of a sproc into a view? – Chronicide Oct 31 '11 at 17:35
  • No there isn't. why can;t they just call the stored proc via ODBC? – HLGEM Oct 31 '11 at 17:46
  • The reason for this is so that a third-party application can import the data into it's own database. The third-party application is only configured to connect to tables or views, not sprocs. I think that I might have to make an empty version of the database, then design a service to manage the information that is held inside. It's still not perfect, but it doesn't look like there's a better way that's available to me. Still open to suggestions, though. – Chronicide Oct 31 '11 at 18:09
  • It's been a long while, but I decided to accept this answer. If it weren't for a set of rather specific circumstances, this would have worked for me. In the end, I stopped trying to get the import on the third party software to work, and I designed a service that monitored the source databases for changes, then updated the third party database when a change was detected. A lot more work to implement, but it was the cleanest and most maintainable solution available to me. – Chronicide Aug 02 '12 at 15:22