Dynamic lCONVERT([SYSNAME],DATABASEPROPERTYEX([NAME],''RECOVERY'')

I am running into an issue trying to accuire information using a linked
server and dynamic sql.
-- this is the query for local
SELECT @.@.SERVERNAME AS [SERVER],
[NAME],
[DBID],
[SID],
[MODE],
[STATUS],
[STATUS2],
[CRDATE],
[RESERVED],
[CATEGORY],
[CMPTLEVEL],
[FILENAME],
[VERSION],
GETDATE() AS [STATDATE],
CONVERT([SYSNAME],DATABASEPROPERTYEX([NA
ME],'RECOVERY')) AS
[RECOVERYMODE]
FROM [MASTER].[DBO].[SYSDATABASES]
WHERE [DBID] > 4
ORDER BY 3
-- this is the dynamic code
declare @.v_sql nvarchar(4000),
@.p_SourceServer sysname
SET @.p_SourceServer='LINKED SERVER NAME'
select @.v_sql ='SELECT '''+@.p_SourceServer+''' AS [SERVER], ' + char(13) +
' [NAME], ' + char(13) +
' [DBID], ' + char(13) +
' [SID], ' + char(13) +
' [MODE], ' + char(13) +
' [STATUS], ' + char(13) +
' [STATUS2], ' + char(13) +
' [CRDATE], ' + char(13) +
' [RESERVED], ' + char(13) +
' [CATEGORY], ' + char(13) +
' [CMPTLEVEL], ' + char(13) +
' [FILENAME], ' + char(13) +
' [VERSION], ' + char(13) +
' GETDATE() AS [STATDATE], ' + char(13) +
'
CONVERT([SYSNAME],DATABASEPROPERTYEX([NA
ME],''RECOVERY'')) AS [RECOVERYMODE]
' + char(13) +
' FROM ['+@.p_SourceServer+'].[MASTER].[DBO].[SYSDATABASES]
' + char(13) +
' WHERE [DBID] > 4 '
+ char(13) +
'ORDER BY 3 '
select @.V_sql -- display code
EXEC SP_EXECUTESQL @.V_SQL
Now when ran locally the recovermode field is filled in with SIMPLE, FULL
etc but when ran using linked server I get mostly null returns. The reason
behind changing this over to dynamic sql is the current process is a dts
package that runs the simple query above and then gets inserted into dba
database under databaselistarchive table. The problem with that is for each
server you want to get this information on you have to add into dts package
to get simple informatoin from each one. All information being pulled to
centeral database that gets reported off of using alot of web apps. But dts
package fails because some servers are down, offline, etc because of virtual
servers.
Need to know how to use system functions via dynamic sql and linked servers.
Thanks for the help in advance..JosephPruiett (JosephPruiett@.discussions.microsoft.com) writes:
> CONVERT([SYSNAME],DATABASEPROPERTYEX([NA
ME],'RECOVERY')) AS
> [RECOVERYMODE]
>...
> Now when ran locally the recovermode field is filled in with SIMPLE,
> FULL etc but when ran using linked server I get mostly null returns.
This is because databasepropertyex() executes on your local server.

> The reason behind changing this over to dynamic sql is the current
> process is a dts package that runs the simple query above and then gets
> inserted into dba database under databaselistarchive table. The problem
> with that is for each server you want to get this information on you
> have to add into dts package to get simple informatoin from each one.
> All information being pulled to centeral database that gets reported off
> of using alot of web apps. But dts package fails because some servers
> are down, offline, etc because of virtual servers.
> Need to know how to use system functions via dynamic sql and linked
> servers.
I think that should be able to run this through OPENQUERY. With OPENQUERY()
you run a pass-through query.
While you would have to run dynamic SQL to have the server name dynamic
with OPENQUERY, you could instead run sp_addlinkedserver to retarget
the server name for each server. The server name is really just an
alias.
If you are on SQL 2005, you could use EXEC() AT linkedserver insteead
of OPENQUERY.
By the way, it's "sysdatabases", "sp_executesql" etc in all lowercase.
On a case-insensitive server it does not matter, but it does on a
case-sensitive server.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx