December 28, 2011 at 7:43 am
Hi all,
Since i'm not that good in programming and thus programming terminology i tought i just post my question here with some example.
I'm using this script to gather backup stats via linked servers. Linked servers are in the @servername variable.
Now i would also like to pass the content of the variable at that time as a 'value' to the table for the column 'servename' in stead of using
''ServerName'' = b.server_name -- coming from [' + @ServerName + '].msdb.dbo.backupset b
Could anyone please shed a light, Ty in advance!
Select @SqlCmd =
'Insert backupstats
(Databasename,
LatestBackupDate,
Days,
hours,
Servername)
SELECT DatabaseName = d.name
,LatetstBackUpDate = ISNULL(CONVERT(VARCHAR(30),MAX(b.backup_finish_date),120),''No backup'')
,''Days'' = MIN(DATEDIFF(hh,b.backup_finish_date,getdate())) / 24
,''Hours'' = MIN(DATEDIFF(hh,b.backup_finish_date,getdate())) % 24
,''ServerName'' = b.server_name
FROM [' + @ServerName + '].master.sys.databases d
LEFT OUTER JOIN [' + @ServerName + '].msdb.dbo.backupset b ON (d.name = b.database_name)
Group By d.name, b.server_name'
EXEC (@SQLCmd)
FETCH NEXT FROM ServersCursor INTO @ServerName
December 28, 2011 at 8:40 am
Not sure what difference it makes but...
Just replace the whole line "''ServerName'' = b.server_name " with ' + @ServerName + '. You don't need to name your columns in the select portion of an insert so that part is just redundant.
Your whole section would look like this:
Select @SqlCmd =
'Insert backupstats
(Databasename,
LatestBackupDate,
Days,
hours,
Servername)
SELECT DatabaseName = d.name
,LatetstBackUpDate = ISNULL(CONVERT(VARCHAR(30),MAX(b.backup_finish_date),120),''No backup'')
,''Days'' = MIN(DATEDIFF(hh,b.backup_finish_date,getdate())) / 24
,''Hours'' = MIN(DATEDIFF(hh,b.backup_finish_date,getdate())) % 24
,' + @ServerName + '
FROM [' + @ServerName + '].master.sys.databases d
LEFT OUTER JOIN [' + @ServerName + '].msdb.dbo.backupset b ON (d.name = b.database_name)
Group By d.name, b.server_name'
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 29, 2011 at 12:03 am
Wel the difference is that if there is no backup for a db there is no servername in that column ...
I got it working now by removing the col name and adding some extra ' s
Select @SqlCmd =
'Insert backupstats
(Databasename,
LatestBackupDate,
Days,
hours,
Servername)
SELECT DatabaseName = d.name
,LatetstBackUpDate = ISNULL(CONVERT(VARCHAR(30),MAX(b.backup_finish_date),120),''No backup'')
,''Days'' = MIN(DATEDIFF(hh,b.backup_finish_date,getdate())) / 24
,''Hours'' = MIN(DATEDIFF(hh,b.backup_finish_date,getdate())) % 24
,''' + @ServerName + '''
FROM [' + @ServerName + '].master.sys.databases d
LEFT OUTER JOIN [' + @ServerName + '].msdb.dbo.backupset b ON (d.name = b.database_name)
Group By d.name, b.server_name'
EXEC (@SQLCmd)
FETCH NEXT FROM ServersCursor INTO @ServerName
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply