use sql function from asp - 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

Development Tools

Other Development Tools

use sql function from asp

Author

Topic

uberman
Posting Yak Master

159 Posts

Posted - 2003-11-03 : 07:08:35

I have a sql server function(!) and what I would like to do is call it directly from ASP, rather than write a wrapper Stored Proc,

so I have some asp code

set objCommand = server.createObject("adodb.command")
with objCommand
.commandType = adCmdUnknown
.commandText = "dbo.fn_CAMPAIGNS"
.parameters.append .createParameter("@startpoint", adInteger, adParamInput, , null)
.parameters.append .createParameter("@includechildren", adBoolean, adParamInput, , 1)
.activeConnection = objConnection
end with

set objRS = server.createObject("adodb.recordset")
with objRS
.lockType = adLockReadOnly
.cursorType = adOpenForwardOnly
.cursorLocation = adUseClient
.open( objCommand )
end with

which tries to call the function (top only)
CREATE FUNCTION fn_CAMPAIGNS(
@startpoint INTEGER = null,
@includechildren BIT = 1
)
RETURNS @retCAMPAIGNS TABLE (
lots of table stuff
)

(this works fine from QA)

but I always get an error (The request for procedure 'fn_CAMPAIGNS' failed because 'fn_CAMPAIGNS' is a function object. )and I can't seem to google down the correct format/commandtypes etc...

Any help appreciated

uberman
Posting Yak Master

159 Posts

Posted - 2003-11-03 : 07:22:49

PAH, who needs google...

Experimented with lots of options and found that this works (although you need to make sure the user connecting has select permissions on the function)

set objCommand = server.createObject("adodb.command")
with objCommand
.commandType = adCmdTable
.commandText = "dbo.fn_CAMPAIGNS(null,0)"
.activeConnection = objConnection
end with

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources