October 14, 2009 at 9:12 am
i'm customizing a backup reporting system that was an article here a few months back. works on most servers, but having a problem on others.
i'm dumping the data from all the clients to a central server where i have a database to keep this type of data. on some servers i created a linked server to the central server and used an account that has permissions only to database i'm using to hold the SQL data. problem is on servers where a linked server already exists to the central server and it uses an account i don't want to have access to the SQL data database.
i'm running 2 transactions
delete sqlreport.performance.dbo.database_lookup where server_name = 'sqlclust'
insert sqlreport.performance.dbo.database_lookup (server_name, database_name)
select @@servername, name from sys.databases
where name not in ('model', 'tempdb', 'northwind', 'pubs')
insert sqlreport.performance.dbo.backupset_dim
SELECT @@servername, backup_set_id, database_name, backup_start_date, backup_finish_date, user_name,type
FROM sqlclust.msdb.dbo.backupset
WHERE type IN ('D','I')
AND backup_start_date > '2008-01-01'
AND backup_set_id > (select max_backup_set_id from sqlreport.performance.dbo.server_dim where server_name = 'sqlclust')
i tried SSIS and the big problem is the @@servername. can't seem to get it to work when i use the data transfer method and it doesn't work on the Execute SQL task due to the linked server authentication issue.
my other solution i'm trying to avoid is to dump the data into a staging table for each server, update the servername column and then transfer the data to the right table.
played with openrowset, but didn't get it working
October 14, 2009 at 11:58 am
I'm willing to bet that the SSIS problem with @@SERVERNAME is that it is complaining about the column name, which there isn't one of.. Replace @@SERVERNAME with ServerName = @@SERVERNAME
I think that will solve it.
CEWII
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply