Fully Qualifying system functions for Stored Proc.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Fully Qualifying system functions for Stored Proc.

Author

Topic

craigwg
Posting Yak Master

154 Posts

Posted - 2009-07-30 : 10:38:29

I have a stored procedure I am writing. It will exist on one server and analyze database information for other linked servers. I have one peice of it that looks like this:


USE [master]
GO

/****** Object:  StoredProcedure [dbo].[GetDatabaseInfo]    Script Date: 07/30/2009 07:49:30 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Alter PROC [dbo].[GetOfflineDBInfo]
@Servername sysname
AS
declare @sql varchar (400)
set @sql  = 
'
select
	convert(varchar(40),databasepropertyex(name, ''Status'')) as DB_Status,
	name,
	dbid,
	crdate,
	cmptlevel
from
	[' + @Servername + '].master.dbo.sysdatabases
where
	databasepropertyex(name, ''Status'') <> ''ONLINE''
'
print (@sql)


--GetOfflineDBInfo 'rtsioltp\data'

This does run successfully when I run it on the server called rtsioltp\data and use static names. When I take it to the server and run as it exists above with dynamic variables it runs successfully but only pulls a blank table.

My theory is that I have to fully qualify the function databasepropertyex. But I can't find where this function is to even test the theory. Is it possible to fully qualify functions so they can be ran from other servers?

Thanks

Craig Greenwood

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-30 : 10:54:53

that's not the problem. either permissions issue or linked server issue.

how many linked servers do u have?

select * from sys.servers where is_linked = 1 and is_data_access_enabled = 1

are u using sql2000? or newer?

craigwg
Posting Yak Master

154 Posts

Posted - 2009-07-30 : 11:05:20

I am using 2000, 2005 and 2008. The name of the server I am basing all my Stored Procedures in is 2008. 85% of the servers I am administering are 2005 and the other 15% are 2000. So I am writing my queries to be as user friendly as possible. Mostly just building a dynamic table of all databases in the company on all servers. I have all I need for online databases, but we want to keep track of offline databases as well. sp_helpdb is what I use to pull information for online databases. I know I won't be able to get EVERYTHING for offline databases, but I'd like to get what I can anyway. So when I write my queries I need to fully qualify everything and then make it use a variable for the server name so it can loop through a cursor and get everything. The concept is beautiful and should be straight forward. But dang, it's hard for me. First time I've done a lot of this stuff.

Another theory I got from this forum yesterday was to use the status column and derive from that the status. Right now that is plan B. I'd much rather dynamically look at the offline databases using the query above and then pull what I need from dynamicserver.master.dbo.sysdatabases which works across all three versions of servers.

FYI there are 120+ servers I am looking at, hence the need for all the dynamicity (is that a word?)

If you can add more I'd be happy for the help!

Craig Greenwood

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-30 : 23:29:26

change this:

WHERE databasepropertyex(name, ''Status'') <>

to this:

WHERE isNull(databasepropertyex(name, ''Status''), '''') <>

but you're still better off going with the status column in sysdatabase/sys.databases

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources