Automate DTS whe DB name changes

[wciccadmin]

Hello,

I am using Microsoft ISA Server as our proxy authentication server. It records our user's web usage to an MSDE database.

I have loaded enterprise manager on the server and would like to use DTS or something to copy the contents a table from the MSDE server to a remote SQL server.

The problem I have is that ISA creates a new database every day in MSDE. The table name with the data I want is called Webproxylog in every database but every day I have a new database named like

ISALOG_20060718_WEB_000

the next day

ISALOG_20060719_WEB_000

and so on.

I would like to do a daily copy of the contents of the webproxylog table from the daily databases to a single db on another SQL server.

I am stumped how to do this considering the dynamic naming of the databases.

Any help is greatly appreciated

 

[gradley]

Assuming only 1 database is created each time you run this job, you can add an Execute SQL task in DTS that performs the following:

Declare @varDBName as varchar(256)
Declare @strSQL as varchar(5000)

-- Determine Most Recent created database
SET ROWCOUNT  1 -- Retrieves only one record from the select statement

select @varDBName =  [name]
from master.dbo.sysdatabases
order by crdate desc

SET ROWCOUNT  0

-- Create dynamic SQL statement to insert rows from new database table
SET @strSQL = ' INSERT INTO MyDatabase.dbo.Webproxylog  SELECT * from ' + @varDBName + '.dbo.Webproxylog '
Exec @strSQL

 

[wciccadmin]

great idea... thanks

 

[gradley]

Glad to help -- hope it works for you!!!

 

[wciccadmin]

Thakns again for the assistance. I am running in to one more problem in my dts.

It looks like the dbname variable is getting correctly fee in to the sql statement but when I try to parse the query I recieve the error...

-----------
The name 'INSERT INTO ISA_LOGS_TEMP.dbo.WebProxyLog SELECT * from ISALOG_20060722_WEB_000.dbo.WebProxyLog' is not a valid identifier.
-----------

ISA_LOGS_TEMP is the destination database name and contains a table WebProxyLog and ISALOG_20060722 is the correct source db.

I am unfamiliar with this error, just wondering if you can give my a little more help.

Thanks!

 

[gradley]

Is it possible the "dbo" is not the owner of either the source or destination tables?

If so, you will want to replace .dbo. with the actual table owner.

 

[wciccadmin]

dbo was the owner of the 2 tables.

I found it works if I put enclose the exec statemnt in parenthesis

exec (@strSQL)

Thanks again.

 

[gradley]

Ooops!! Sorry about that...

Anytime!!

Glad to hear you got it working...