Hi,

I have multiple client databases sharing the same structure that
contain a view (dbo.Act1) that needs to be refreshed on a constant
basis.

The results of the view are passed into Access as a linked table, but
Access does not update the contents of the view automatically. This
requires us to open each client database in SQL Server, select the
View and Open the view - which is a little tedious. We are stuck
with the Access route since we have a few clients that are using
external db's and sending exported files that need to be linked to as
well for reporting purposes.

I am trying to create a script that would cycle through all the
databases and update each view. The number of databases is not
constant as we have clients added or dropped on a monthly basis so I
have used rowcount on sysdatabases to get the total number of clients
- the low end counter (db_row_counter) is initiated at 7 since the
first 7 databases are system and not client databases (not sure if
this is a constant, but it was a starting place).

I'm a new user and I'm stuck on the syntax for 1) Selecting the
database using my @db_name parameter and 2) Updating (opening/
refreshing?) the actual view "@db_name.dbo.Act1"

Any help would be appreciated.

Chris.

Current syntax generates db_name and then increments to next db_name
until all db's have been "listed":

DECLARE @db_row_counter AS INT
DECLARE @db_name AS CHAR(50)
DECLARE @db_row_max AS INT
SET @db_row_counter = 7
SELECT * FROM master.dbo.sysdatabases
SET @db_row_max = @@rowcount
SELECT @db_row_counter
SELECT @db_row_max
start_point:
IF @db_row_counter <= @db_row_max
BEGIN
SET @db_name = (SELECT name
FROM (SELECT name,
ROW_NUMBER()OVER(ORDER BY dbid) AS db_row
FROM master.dbo.sysdatabases) AS names_from_master
WHERE db_row = @db_row_counter)
SET @db_name = rtrim(@db_name)
SELECT @db_name /* to show current value of db_name while performing
the loop*/
/*USE @db_name ??
Looking for proper syntax that would "refresh" (open?)
the view " @db_name+'dbo.Act1' "
*/
SET @db_row_counter = @db_row_counter + 1
GOTO start_point
END

FYI - I was able to get the query to do what I wanted with the
following syntax - not optimized I'm sure, but it works...

DECLARE @db_row_counter AS INT
DECLARE @daba_name AS CHAR(50)
DECLARE @db_row_max AS INT
DECLARE @sql AS CHAR(100)
SET @db_row_counter = 7
SELECT * FROM master.dbo.sysdatabases
SET @db_row_max = @@rowcount
start_point:
IF @db_row_counter <= @db_row_max
BEGIN
SET @daba_name = (SELECT name
FROM (SELECT name,
ROW_NUMBER()OVER(ORDER BY dbid) AS db_row
FROM master.dbo.sysdatabases) AS names_from_master
WHERE db_row = @db_row_counter)
SET @daba_name = rtrim(@daba_name)
SET @SQL = 'USE ' + '['+rtrim(@daba_name)+']'+' SELECT * FROM Act1'
EXEC (@SQL)
SET @db_row_counter = @db_row_counter + 1
GOTO start_point
END

Chris H (chollstein@broadreachpartnersinc.com) writes:
> FYI - I was able to get the query to do what I wanted with the
> following syntax - not optimized I'm sure, but it works...
>
> DECLARE @db_row_counter AS INT
> DECLARE @daba_name AS CHAR(50)
> DECLARE @db_row_max AS INT
> DECLARE @sql AS CHAR(100)
> SET @db_row_counter = 7
> SELECT * FROM master.dbo.sysdatabases
> SET @db_row_max = @@rowcount
> start_point:
> IF @db_row_counter <= @db_row_max
> BEGIN
> SET @daba_name = (SELECT name
> FROM (SELECT name,
> ROW_NUMBER()OVER(ORDER BY dbid) AS db_row
> FROM master.dbo.sysdatabases) AS names_from_master
> WHERE db_row = @db_row_counter)
> SET @daba_name = rtrim(@daba_name)
> SET @SQL = 'USE ' + '['+rtrim(@daba_name)+']'+' SELECT * FROM Act1'
> EXEC (@SQL)
> SET @db_row_counter = @db_row_counter + 1
> GOTO start_point
> END

Here is a solution that is somewhat cleaner. Beside using a cursor, I'm
using quotename to add the brackets, and there is a check that the
view actually exists in this database.

Then how this can help to refresh your Access application, I don't
know, but then again I don't know Access, so that's maybe why.


DECLARE @sql nvarchar(MAX)
DECLARE @db sysname
DECLARE dbcur CURSOR STATIC LOCAL FOR
SELECT quotename(name)
FROM sys.databases
WHERE user_access_desc = 'MULTI_USER'
OPEN dbcur

WHILE 1 = 1
BEGIN
FETCH dbcur INTO @db
IF @@fetch_status <> 0
BREAK

SELECT @sql = 'USE ' + @db +
' IF object_id('dbo.Acct1') IS NOT NULL SELECT * FROM Acct1'
EXEC (@sql)
END

DEALLOCATE dbcur





--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx