SP To UDF - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

SP To UDF

Author

Topic

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-03-21 : 10:57:36

I've been trying to wrap a udf around the sp_helpuser stored procedure so that I can later use the udf and join it to another table.

I was under the impression that the following would work
however I'm faced with a syntax error.

Can someone see my error or tell me why I can't wrap a udf around an sp.

Thanks

CREATE FUNCTION [dbo].[fnSPHelpUser]
(
@Name NVARCHAR(128)
)

RETURNS

@SPHelpUser TABLE
(
UserName SYSNAME,
GroupName SYSNAME,
LoginName SYSNAME,
DefDBName SYSNAME,
UID SMALLINT,
SID VARBINARY(85)
)

AS

BEGIN

INSERT INTO @SPHelpUser
SELECT *
FROM OPENROWSET('Provider=SQLOLEDB;Data Source=MYSERVER;Initial Catalog=MYDB;Integrated Security=SSPI', 'EXEC SP_HELPUSER ' + @Name )

RETURN

END

Also tried the following with no success

DECLARE @SQL NVARCHAR(128)

SET @SQL = 'EXEC SP_HELPUSER ' + @Name

INSERT INTO @SPHelpUser
SELECT *
FROM OPENROWSET('Provider=SQLOLEDB;Data Source=MYSERVER;Initial Catalog=MYDB;Integrated Security=SSPI', @SQL)

Edited by - ValterBorges on 03/21/2003 11:06:12

Edited by - ValterBorges on 03/21/2003 11:09:06

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-03-21 : 11:17:34

Found the syntax problem

INSERT INTO @SPHelpUser
SELECT *
FROM OPENROWSET('SQLOLEDB','Provider=SQLOLEDB;Data Source=MYSERVER;Initial Catalog=MYDB;Integrated Security=SSPI', @SQL)

However now I'm getting the following

Server: Msg 18456, Level 14, State 1, Line 1
Login failed for user 'SYSTEM'.
[OLE/DB provider returned message: Invalid connection string attribute]

Any help would be appreciated

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-03-21 : 11:24:03

Sounds from the error message as if its using the localsystem account... Probably what's specified for the mssql service... Have you tried letting the service use another user?

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-03-21 : 11:49:56

Andraxx,

I've tried it with the domain admin account still didn't get it to work.

Strangely the following works

SELECT a.*
FROM OPENROWSET('SQLOLEDB','MYSERVER';'user';'password','exec sp_who') AS a

But

SELECT a.*
FROM OPENROWSET('SQLOLEDB','MYSERVER';'user';'password',
'exec sp_helpuser') AS a

returns

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '#tb1_uga'.

Edited by - ValterBorges on 03/21/2003 11:50:27

Edited by - ValterBorges on 03/21/2003 11:53:15

nr
SQLTeam MVY

12543 Posts

Posted - 2003-03-22 : 21:58:38

udf's are meant to be deterministic.
The complier will do rudimentary checks to ensure this.
I am pretty sure that anything in an openquery will not be checked so you can get invalid results.

#tb1_uga is a temp table that is created at the beginning of sp_helpuser.

Try this
use test
go
create proc sphu
as
create table #a (i int)
insert #a select 1
go
exec openquery(self,'exec master..sphu') as a
also
exec openquery(self,'exec master..sp_executesql N''master..sphu''') as a

(self is a self linked database.

You should find that you get an error for invalid object #a.

Suspect this is because it tries to find the structure of the resultset before running the query and fails to find the temp table.

I feel that trying to run system SPs like this is going to cause problems and you are better off creatring your own queries. If you are querying remote servers then get the system table data the run the queries locally.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-03-26 : 12:31:32

Thanks NR,

I was trying to get user information for a client interface to log usage. In the end I ended up using the WIN32API to get Domain,UserLogin,IP,MAC Address and SQL Role information using a recordset call to sp_helpuser and a custom stored proc to log the info.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources