Problem creating function to be schema independent

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

SQL Server 2005 Forums

Transact-SQL (2005)

Problem creating function to be schema independent

Author

Topic

dxdavidson
Starting Member

3 Posts

Posted - 2008-04-01 : 12:50:22

I am writing a SQLServer script that I want to be schema name independent. I mean I know that all users of the script will have the same tables, but not necessarily the same schema name.

When I hard code the script to use the name of my schema, wcadmin, it works OK.

CREATE FUNCTION wcadmin.dectohex(@a numeric)
RETURNS varchar(8)
BEGIN
DECLARE @x varchar(8)
DECLARE @y varchar(1)
DECLARE @z numeric
DECLARE @w numeric

SET @w=@a
SET @x=''

WHILE @w > 0
BEGIN
SET @z = @w % 16;

SET @y= CASE @z
WHEN 10 THEN 'A'
WHEN 11 THEN 'B'
WHEN 12 THEN 'C'
WHEN 13 THEN 'D'
WHEN 14 THEN 'E'
WHEN 15 THEN 'F'
ELSE CAST(@z AS varchar)
END

SET @w = ROUND(@w/16,0,1)
SET @x = @y + @x
END

-- Pads the number with 0s on the left
SET @x = RIGHT(REPLICATE('0',8) + @x ,8)
RETURN @x
END;
GO

select 'WTDOCUMENT' HOLDER,
dm.WTDocumentNumber ITEMNUMBER,
dm.name ITEMNAME,
ad.fileName ContentFilename,
fh.hostName VaultHost,
wcadmin.dectohex(fi.uniqueSequenceNumber) VaultFile,
fm.path VaultPath
from
WTDocument di,
WTDocumentMaster dm,
HolderToContent hc,
ApplicationData ad,
FvItem fi,
FvFolder ff,
FvMount fm,
FvHost fh
where di.idA3masterReference = dm.idA2A2
and fm.idA3A5 = ff.idA2A2
and fm.idA3B5 = fh.idA2A2
and fi.idA3A4 = ff.idA2A2
and ad.idA3A5 = fi.idA2A2
and hc.idA3B5 = ad.idA2A2
and hc.idA3A5 = di.idA2A2

DROP FUNCTION wcadmin.dectohex;
GO

But when I remove my schema name I get the error

'dectohex' is not a recognized built-in function name.

In this case I'm just using :-

CREATE FUNCTION dectohex(@a numeric)
.
.
.
.
select 'WTDOCUMENT' HOLDER,
dm.WTDocumentNumber ITEMNUMBER,
dm.name ITEMNAME,
ad.fileName ContentFilename,
fh.hostName VaultHost,
dectohex(fi.uniqueSequenceNumber) VaultFile,
.
.
.
.
DROP FUNCTION dectohex;

Creating and dropping the function seems to work OK when I drop the schema name, I just can't call it.

I've been trying various permutations of dbo and [dbo] prefixes unsuccessfully.

Any suggestions?

Thanks

David

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-04-01 : 13:02:33

What has been unsuccessfull about use dbo as your schema?
That is what we do. You will need to GRANT SELECT or EXEC to non-dbos.

Be One with the Optimizer
TG

dxdavidson
Starting Member

3 Posts

Posted - 2008-04-01 : 13:58:04

Well I get a message like

The specified schema name "dbo" either does not exist or you do not have permission to use it.

But I'm not sure of the syntax, is it :-

CREATE FUNCTION dbo.dectohex(@a numeric)

Or should I be using square brackets?

Perhaps I don't have the necessary permissions, but this script is intended to be used by a SQLServer user with standardised permissions that I don't have control over.

An alternative approach could be for me to pass the schema name into the script as a parameter but I don't know how to do this either :-(

I should say that I am porting this script from an Oracle version that currently works.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-04-01 : 14:44:39

Your syntax is fine (with or without the square brackets).
whatever schema the function belongs to is how the function needs to be called. If the user(s) don't have implicit permission to the schema.function then they (or a role they belong to) will need to be granted permission to call this function.

If you are not a dbo, I suggest you talk to your dba about your objective. They can advise and still conform to your security model.

Be One with the Optimizer
TG

dxdavidson
Starting Member

3 Posts

Posted - 2008-04-01 : 18:32:46

Thanks, in actual fact the database is part of a standard application and is created by the installer. The database is really hidden behind the application and when it's installed there usually is no DBA. My script is a utility to give the application administrator (who probably isn't a DBA) some more information. Since I need this all automated it's not really viable to depend on granting additional permissions.

I think an alternative workaround would be to parameterise the schema name and somehow pass it into my SQL script. I will be calling the SQL script from an ANT script, and I will actually have the schema name as an ANT property.

Is it possible to receive an argument (schema name) into a SQLServer SQL script that I could pass from ANT?

dlwiii
Starting Member

1 Post

Posted - 2008-07-17 : 20:26:13

DX,

I have a similar need. I want to have sql which is not only schema independent, but also database independent. So, for example, I want to create a TRIM() function in Sql Server 2005, so that the statement:

SELECT TRIM(foo) from BAR

Will work in Sql Server and in Sybase. In Sybase "dbo" will be meaningless. So I want to create a "built-in" function. Does anyone know how to do this?

Cheers,
Daniel Williams

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources