August 30, 2005 at 8:27 am
All,
This may be a stupid question, but does anyone know if there is a way to perform distibuted queries of system functions? I am trying to find a way to perform the following queries (either via the DATABASEPROPERTYEX function or via other means) for a linked SQL server. Obviously the 4 part naming comvention is of no relevance when it comes to system functions.
DECLARE @dbName sysname
SET @dbName = 'master'
SELECT DATABASEPROPERTYEX (@dbName, 'Collation')
SELECT DATABASEPROPERTYEX (@dbName, 'IsAutoShrink')
SELECT DATABASEPROPERTYEX (@dbName, 'IsAutoUpdateStatistics')
SELECT DATABASEPROPERTYEX (@dbName, 'IsInStandBy')
SELECT DATABASEPROPERTYEX (@dbName, 'Recovery')
SELECT DATABASEPROPERTYEX (@dbName, 'Status')
SELECT DATABASEPROPERTYEX (@dbName, 'Updateability')
SELECT DATABASEPROPERTYEX (@dbName, 'UserAccess')
I have investigated using sp_helpdb, which I can execute in the following manor:
EXEC [remoter_server].master.dbo.sp_helpdb
This will return all the database properties above within the returned 'status' column (pulling these out using various string functions is not an issue). The only problem is that if a database is OFFLINE or in a RESTRICTED ACCESS state 'sp_helpdb' does not return any information about it, which is something I am trying to achieve (obviously I could deduce that the database is OFFLINE but would not be able to interrogate anyother property values). For a local server I simply run the DATABASEPROPERTYEX statements (exlcluding Collation) against the database if it is OFFLINE or in a RESTRICTED ACCESS state giving me the appropriate property values.
I have also thought about deploying a stored procedure locally to every server which would simply contain the DATABASEPROPERTYEX statements above, I could then query this remotely to return the results, but again this is not really a workable solution.
Any help in identifying how I could achieve this would be greatly appreciated as this could seriously reduce the maintenance/deployment of some administrative scripts i am working on.
Thanks in advance,
ll
August 31, 2005 at 4:26 am
I have also thought about deploying a stored procedure locally to every server which would simply contain the DATABASEPROPERTYEX statements above, I could then query this remotely to return the results, but again this is not really a workable solution.
It's pretty much the only way except using possibly (untested)
SELECT * FROM OPENQUERY(LinkedServer, '
DECLARE @dbName sysname
SET @dbName = ''master''
SELECT DATABASEPROPERTYEX (@dbName, ''Collation'')
SELECT DATABASEPROPERTYEX (@dbName, ''IsAutoShrink'')
SELECT DATABASEPROPERTYEX (@dbName, ''IsAutoUpdateStatistics'')
SELECT DATABASEPROPERTYEX (@dbName, ''IsInStandBy'')
SELECT DATABASEPROPERTYEX (@dbName, ''Recovery'')
SELECT DATABASEPROPERTYEX (@dbName, ''Status'')
SELECT DATABASEPROPERTYEX (@dbName, ''Updateability'')
SELECT DATABASEPROPERTYEX (@dbName, ''UserAccess'')
')
You may need to UNIONs between the selects
SELECT * FROM OPENQUERY(LinkedServer, '
DECLARE @dbName sysname
SET @dbName = ''master''
SELECT DATABASEPROPERTYEX (@dbName, ''Collation'')
UNION
...
')
Or a single select
SELECT * FROM OPENQUERY(LinkedServer, '
DECLARE @dbName sysname
SET @dbName = ''master''
SELECT DATABASEPROPERTYEX (@dbName, ''Collation'') AS [Collation], DATABASEPROPERTYEX (@dbName, ''IsAutoShrink'') AS [sss] ...
')
But of you want to change the database name, then you need dynamic SQL because openquery only takes constants... (untested)
DECLARE @remotesql varchar(1000)
SET @remotesql = 'SELECT DATABASEPROPERTYEX (''' + pubs + ''', ''Collation'') AS [Collation], DATABASEPROPERTYEX (''' + pubs + ''', ''IsAutoShrink'') AS [sss] ...'
EXEC (@remotesql)
So, stick to a remote sp
EXEC linkedserver.db.dbo.usp_Stuff 'pubs'
Far easier...
August 31, 2005 at 8:50 am
Shawn,
Many many thanks for your response. I have spent a good few hours thinking about this solution and investigating OPENQUERY. I had never really used this function before. However I immediately came across it's limitations as you stated above, i.e no support for the use of variables for the query portion. I had a think about this and have finally come up with a solution to this, see below:
DECLARE @status varchar(256),
@dbName sysname,
@linkedServer varchar(128)
SET @dbName = 'Northwind'
SET @linkedServer = 'server-name'
SET @status = 'SELECT * FROM OPENQUERY(' + quotename(@linkedServer) + ', ''
SELECT DATABASEPROPERTYEX ('''''+ @dbName + ''''', ''''Status'''')'')'
--SELECT @status
EXECUTE(@status)
The above query allows me to specify dynamically the database name and the linked server name (@dbName and @linkedServer respectively), the values of these variables are then substituted in to the pass-through query statement which is held as another varchar variable (@status). I then simply fire the EXECUTE statement passing in the @status variable.
Hey pronto "distributed system function querying"
I realise this isn't the clearest or possibly the best way of doing this? It does however allow me to dynamically query DATABASEPROPERTYEX properties/values remotely and seems to run without noticeable performance degredation, the Execution plan for this query explains this better than I can.
I was planning on resorting to the legacy 'sp_dboption' system sproc (with a lot of manipulation and logic wrapped around to interpret it's output) to achieve this remote querying. However I believe the method above to be the best solution by far, being that it uses the preferred method of querying database options (DATABASEPROPERTYEX) and the preferred method to run distributed queries (Linked Servers), heye you never know, it may even work with SQL Server 2005????
Many thanks again for pointing me in the right direction Shawn,
lloyd
August 31, 2005 at 8:54 am
Glad to help.
probably not much performance degradation because no query plans with table access, also execution is split across 2 servers...
It's always a pain getting the number of ''''''' corect in nesting string though...
August 31, 2005 at 9:40 am
Tell me about it, that's what took the majority of the time, you can see my commented out SELECT statement to make sure the resultant command was formatted correctly. quotename is a little gem i've only recently found as well.
ll
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply