Calling A Function From A Function?

Apr 11, 2008

Hi All

Yesterday Peso was gracious enough to help me with creating function/views/sp's

I took those examples and extended what had from excel into function in SQL

however I see myself repeating certain parts of the query and i'm wondering if there is a way to call a function (in part or in whole) from another function?

Here are excerpts two functions I have:

We'll call this function UserUsage()
------------------------------------
RETURN(
SELECT ut.LastName, ut.FirstName,
CEILING(Sum(hu.session_time)/ 60000) AS [Time Spent(MIN)],
Max(hu.time_stamp) AS [Last Log Date],
pct.Title, cat.topic_name
FROM ZSRIVENDEL.dbo.UserTable ut,
ZSRIVENDEL.dbo.history_usage hu,
ZSRIVENDEL.dbo.pc_CourseTitles pct,
ZSRIVENDEL.dbo.cam_topics cat
WHERE ut.student_id = hu.student_id
AND hu.course_id = pct.CourseID
AND hu.topic_id = cat.topic_id
AND ((ut.ClientID=@ClientID)
AND (pct.ClientID=@ClientID)
AND (ut.GroupID=3400)
AND (hu.time_stamp>= @StartDate
And hu.time_stamp< @EndDate)
AND (hu.session_time<21600000))
GROUP BY ut.LastName, ut.FirstName, pct.Title, cat.topic_name
)

and will call this function UserSummary():
-----------------------------------------
RETURN (
SELECTut.LastName, ut.FirstName,
CEILING(SUM(hu.Session_Time) / 60000.0) AS [Time Spent(MIN)]
FROM ZSRIVENDEL.dbo.UserTable AS ut
INNER JOIN ZSRIVENDEL.dbo.History_Usage AS hu
ON hu.Student_ID = ut.Student_ID
WHERE ut.ClientID = @ClientID
AND ut.GroupID = 3400
AND hu.Time_Stamp >= @StartDate
AND hu.Time_Stamp < @EndDate
AND hu.Session_Time < 21600000
GROUP BY ut.LastName, ut.FirstName
)

As you can see the first part of the both query are simlar. In particular the:

SELECTut.LastName, ut.FirstName,
CEILING(SUM(hu.Session_Time) / 60000.0) AS [Time Spent(MIN)]

and also the variables @StartDate and @EndDate.

In C# it would create a method and just call that method as well as the variables. However i'm not sure how to do that with sql functions. Could someone shed some light please?

Thank you!

View 2 Replies


ADVERTISEMENT

Calling SQL Function In C#

Feb 22, 2007

Hello,
I am trying to call a SQL Function in C#, as I want to get the value binded back to the column in a datagrid.
 
Any answers will be appreciated ...............Urgent.
 
:)

View 4 Replies View Related

Calling A Function.

Feb 26, 2008

I'm calling a function with its full qualifiers.
That means servername.dbname.schemaname.functionname

I'm getting an error like this
Invalid column name 'servername'.

Is it possible to call a function name with its server name?


Thanks
Somu

View 4 Replies View Related

Calling A SP In A Function...?

Aug 30, 2006

Can I call a Stored Procedure in a function?



I created a SP:

CREATE PROCEDURE dbo.test_sp AS
BEGIN
select * from STOCK

END
GO


and I tried to create a function like that:

CREATE FUNCTION dbo.test_fn ()
RETURNS int AS
BEGIN
declare @a int
select @a = sto_id from dbo.test_sp
return @a
END

as seen the function uses the test_sp.but that gives syntax error. How can I do this?

thanks.

View 4 Replies View Related

Calling A VB Function From An SQL Statement

Feb 19, 2003

I need to know if there's any way to call a VB function from within an SQl statement. We have text in Rich Text format in a database and need it converted to regular text before we are able to perform searches on the data. Maybe I can use a stored procedure to accomplish this conversion or to call a function that would do this? Any help would be appreciated.

View 2 Replies View Related

Calling MS Access Function From DTS

Mar 10, 2004

Does anyone know if you can call an Access function from DTS?
I'm trying to delete data from an Access database, Compact the database, and load new data. My snag is calling a function in Access to compact the database.

Suggestions?

View 2 Replies View Related

Calling User Function Without Dbo.

Jun 23, 2008

Hi
I have created a user function
the only way i can use this function is by specifying dbo. as prefix of the function

example

SELECT column1, dbo.MyFunc(column2)
FROM mytable

what i want is something like this

SELECT column1, MyFunc(column2)
FROM mytable

is this possible?

View 4 Replies View Related

Problem Calling A Function

Mar 11, 2008

I have the next Function:

CREATE FUNCTION F_StoreName
(
@strTienda as varchar(3)--Numero de Tienda
)
RETURNS VARCHAR(200)
AS

BEGIN

declare
@strStoreName as varchar(30),
@strSQLString nvarchar(500),
@strParmDefinition nvarchar(500)

--set @strTienda='003'

SET @strSQLString = N'select @StoreName = max(nombre) ' +
' from ' + master.dbo.fGetServerName('bdSupport') + 'bdSupport..tbTiendas with (noLock) ' +
' where notienda= ' + @strTienda

SET @strParmDefinition = N'@StoreName varchar(30) OUTPUT';
--print @strSQL
EXECUTE sp_executesql @strSQLString, @strParmDefinition, @StoreName=@strStoreName OUTPUT;

RETURN @strStoreName

END

When I call this function:

select dbo.F_StoreName('002') as x

It sent me next error:
Only functions and extended stored procedures can be executed from within a function.

View 1 Replies View Related

Calling Sp_oa* In Function

Jul 23, 2005

I'm faced with a situation where I will need to calculate a column fora resultset by calling a component written as a VB6 DLL, passingparameters from the resultset to the component and setting (orupdating) a column with the result. I thought that perhaps the bestway out would be to create a UDF that passes the parameters to the VBcomponent using the sp_oa* OLE stored procs.For a test, I created an ActiveX DLL in VB6 (TestDLL) with someproperties and methods. I then created a function that creates theobject, sets the required properties and returns a result. I usesp_oaDestroy at the end of the function to remove the objectreference. The function seems to work surprisingly well except for asmall problem; when I use the function to calculate a column for aresultset with more that one row, the DLL appears to stay locked up("the file is being used by another person or program"). This leavesme with the impression that the object reference is not beingdestroyed. I have to stop/restart the SQL Server in order to free theDLL.Question:Is the UDF approach the best way? I don't like the idea of creatingand destroying the object at every pass which is what the UDF does.As an alternative, I suppose that I could have a single SP where Icreate the OLE object once, loop through the result set with a cursorand do my processing/updating, then close the OLE object. I must saythat I'm not too fond of that approach either.Thanks for your help,Bill E.Hollywood, FL(code is below)___________________________--Test the functionCreate Table #TestTable(Field1 int)INSERT INTO #TestTable VALUES (1)INSERT INTO #TestTable VALUES (2)SELECT Field1, dbo.fnTest(Field1,4) AS CalcColFROM #TestTableDrop Table #TestTable___________________________CREATE FUNCTION dbo.fnTest/*This function calls a VB DLL*/(--input variables@intValue1 smallint,@intValue2 smallint)RETURNS integerASBEGIN--Define the return variable and the counterDeclare @intReturnValue smallintSet @intReturnValue=0--Define other variablesDeclare @intObject intDeclare @intResult intDeclare @intError intSet @intError=0If @intError = 0exec @intError=sp_oaCreate 'TestDLL.Convert', @intObject OUTPUTIf @intError = 0exec @intError = sp_OASetProperty @intObject,'Input1', @intValue1If @intError = 0exec @intError = sp_OASetProperty @intObject,'Input2', @intValue2If @intError = 0exec @intError = sp_oamethod @intObject, 'Multiply'If @intError = 0exec @intError = sp_oagetproperty @intObject,'Output',@intReturnValue OutputIf @intError = 0exec @intError = sp_oadestroy @intObjectRETURN @intReturnValueEND

View 8 Replies View Related

Calling A Function On A Different Server?

Oct 17, 2006

I was wondering if anyone knows the best way to call a function that is located in database on a different server then the database that is calling the function. Is it possible to do this with out linked servers? I am running SQL Server 2000. Any help in this matter would be greatly appreciated.

View 1 Replies View Related

Calling VB.NET Function From SQL Trigger

Nov 27, 2007


I have VS 2003 & SQL Server 2005.I have created VB.NET console application which calls various function. Based on data insertion/ updatation in SQL 2005 I need to call function from my VB.NET application. That is from SQL insert/update trigger I need to call function from my console application which is continuouly running.


I need help on how can I capture insert trigger event VS 2003 console application?

View 2 Replies View Related

Calling The User Defined Function!!!

Aug 28, 2007

hai,
the problem is - I have created a userdefined function using SQL 2000
create function  getfulldate (@date varchar(10))returns datetimeasbegindeclare @getfulldate datetime set @getfulldate  = dateadd (mi,55,@date)
return @getfulldateend
and normally we call this in the SQL statements as
select *, dbo.getfulldate('2006-05-03')  from emp
This works fine and what I need was, I need to invoke the user-defined function like
select *, getfulldate('2006-05-03')  from emp    that is, without using "dbo".
If I call in that manner, it gives error as - 'getfulldate' is not a recognized function name.
So, here what is the purpose of dbo and can I call in my desired manner as mentioned above.
anyone guide me, thanks!

View 3 Replies View Related

Calling A Function From A Stored Procedure

Oct 26, 2007

Hello all,
I'm trying to construct a select statement in a stored procedure that filters based on the returned values of a number of functions.  My function works fine, but when I try to call the function from the stored procedure I get an error.
I'm going to try explain the thought process behind what I'm doing.  Hope I make enough sense.The purpose of the stored procedure is to perform a wildcard search on a tool.  The tool contains a number of FK that link to different tables (e.g., manufacturer, vendor).  So I'm creating functions that also search the manufacturer and vendor and return the matching IDs.
Example of tool SELECT statement:SELECT tool_number, tool_description
FROM tool
WHERE tool_manufacturer IN (UDFmanufacturer_SearchName(@search_string)
This gives me an error:'UDFmanufacturer_SearchName' is not a recognized built-in function name.
Function code (removed some wrapping code for simplicity):SELECT manufacturer_id
FROM manufacturer
WHERE manufacturer_name LIKE '%' + @search_string + '%'These statements both work if I run a independent query: SELECT *
FROM UDFmanufacturer_SearchName('mol')  SELECT *
FROM tool
WHERE tool_manufacturer IN (SELECT *FROM UDFmanufacturer_SearchName('mol')) This code fails:SELECT *
FROM ato_tool
WHERE ato_tool_manufacturer IN (UDFmanufacturer_SearchName('mol'))
 I'm stuck.  I haven't been able to find anything that shows me where I'm going wrong.  Any thoughts or suggestions are appreciated. Thanks,Jay

View 4 Replies View Related

An Issue With Calling A Function From Storeprocedure.

May 9, 2008

This is an issue with calling a stored procedure, which calls a function.this input parameter of the function is a list nvarchar.where i am giving the input like : 1,2,3,4,8 here for the corresponding id's 1,2,3,4,8i wanna take all the details and displaying it ina crystal report........  CREATE FUNCTION iter$simple_intlist_to_tbl (@list nvarchar(MAX)) RETURNS @tbl TABLE (number int NOT NULL) ASBEGIN DECLARE @pos int, @nextpos int, @valuelen int SELECT @pos = 0, @nextpos = 1 WHILE @nextpos > 0 BEGIN SELECT @nextpos = charindex(',', @list, @pos + 1) SELECT @valuelen = CASE WHEN @nextpos > 0 THEN @nextpos ELSE len(@list) + 1 END - @pos - 1 INSERT @tbl (number) VALUES (convert(int, substring(@list, @pos + 1, @valuelen))) SELECT @pos = @nextpos END RETURNEND create proc [dbo].[Comparison](@ProductVersionID VarChar(50))asbeginselect PV.Productversionname, F.FeatureID, F.Title, F.description, F.Modifieddate,PVF.IsPresent, FG.Title from features F,ProductVersionFeatures PVF, productversion PV, Featuregroup FG where F.FeatureID = PVF.FeatureID and PVF.productversionid = PV.ProductVersionID and iter$simple_intlist_to_tbl(@ProductVersionID) i ON PVF.productversionid = i.numberendThis is my Storeprocedure, where i am calling a function in this stored procedure but when i am trying to execute the Sp, i am facing an error liek this :Msg 195, Level 15, State 10, Procedure Comparison, Line 4'iter$simple_intlist_to_tbl' is not a recognized built-in function name. can any body please help me why this is hapenig, how can i solve this issue  

View 11 Replies View Related

SSIS Function Calling Problem

Nov 7, 2007

I need to write SSIS package with 5 script task. I have one function which need to be called from each SSIS one by one. I wrote the that function at first SSIS task. For example:

Public Function Add() As Integer
Dim i, j As Integer
i = 10
j = 20
Return (i + j)
End Function

and I can call this function inside 1st SSIS task but how can I call this function on rest of 4 script task?
Thanks
Sanjeev

View 2 Replies View Related

Calling User-defined Function Without 'dbo.' -- Possible?

Jul 20, 2005

Is it possible to call a user-defined function without prefixing itwith 'dbo.' within a SELECT clause somehow? Just curious; it's not abig issue but just a stylistic one for me.Thanks!Joel Thornton ~ <groups@joelpt.eml.cc>

View 1 Replies View Related

Howto: Calling Sp_executesql From A Function ?

Jul 20, 2005

I want to execute a dynamically generated sql-statementfrom inside an user-defined-function. Calling functions andextended stored-procs is allowed so I tried sp_executesqlas well as sp_prepare/sp_execute ....but both fail with an error 'only functions and extended stored-procsmay be called from inside a function.'any idea where I might be wrong ?thx in advance,Joerg--************************************************** ***********Joerg ClausmeyerMedizinische Informatik und DatenmanagementCHARITE - Universitätsmedizin Berlin************************************************** ***********

View 3 Replies View Related

Calling User Defined Function From Other Server

May 25, 2006

I have UDF in a database on SQL2000 server. Is it possible to call this UDF from other server (SQL2005)? I did setup a linked server to SQL2000

Call to the following function returns an error:

Msg 207, Level 16, State 1, Line 1

Invalid column name 'srv2000'.

select [srv2000].db_test.dbo.F_TEST()

View 4 Replies View Related

Dynamic Query Calling User Defined Function

Apr 19, 2006

I have the following procedure, that calls a Padding function to pad characters to a field.

Here is what the procedure looks like

Code:

CREATE PROCEDURE [dbo].[Pad_Left]

@Table VARCHAR(255),
@Column VARCHAR(255),
@PadChar CHAR(1),
@PadToLen INT

AS

DECLARE @Query Varchar(5000)

SET @Query = 'UPDATE ' + @Table + '

SET ' + @Column + ' = dbo.Function_PadLeft(' + @Column + ', ''' + @PadChar + ''', ' + @PadToLen + ')'

EXECUTE(@Query)
GO



When I run this I get the error

Server: Msg 245, Level 16, State 1, Procedure Pad_Left, Line 13
Syntax error converting the varchar value 'UPDATE Lincoln

SET baths = dbo.Function_PadLeft(baths, '0', ' to a column of data type int.

But when I just run this query, it works


Code:

CREATE PROCEDURE [dbo].[Pad_Left]

@Table VARCHAR(255),
@Column VARCHAR(255),
@PadChar CHAR(1),
@PadToLen INT

AS

UPDATE Lincoln

SET Baths = dbo.Function_PadLeft(Baths, '0', 4)

GO



Why would one work but not the other? I don't understand, as they are the same thing, just one calls the function dynamically?

I must be missing something very obvious

Thanks for any help!

View 2 Replies View Related

Stored Procedure And Calling User Defined Function

Sep 29, 2007

I seem to be getting tasks that I am not familiar with these days. I am a
guy that has coded it all in the asp page or in the code behind in .NET.
This problem is outlined below and I need a help / advice on doing this. I
had the flow of the 3 parts to it expanded below. A call is made to a Stored
Procedure, The SP then calls a user defined function that runs SQL, this
returns a 1 or 0 to the SP which then returns the value back to the call on
the asp page. This is a lot I know but it is the way the lead guy wants it
done. Any help so I can keep most of the hair I have left is appreciated :-)

Short list of process flow:

1. Form.asp calls to rx_sp_HasAccessToClient in SQL SERVER

2. rx_sp_HasAccessToClient then calls ab_HasAccessToClient

3. ab_HasAccessToClient runs SQL command on db and sends return bit back to
rx_sp_HasAccessToClient

4. rx_sp_HasAccessToClient then sends this back to the call in the Form.asp
page

5. Form.asp then checks the Boolean and if 1 then show or if 0 then deny.

<FLOW WITH CODE AND FUNCTIONS :>

This is not the correct syntax but is showing what I understand sort of how
this is to be done so far.

This panel loads up the Vendors and id's when the user clicks on the link
"view detailed list of vendors associated with this client". This is the
beginning of the process.

This is code in Form.asp

'PANEL ONE
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXX----
>

If ValidateInput(Request.Querystring("Postback"))="FormDetails" then 'Check
Postback Type

'We need to load up vendors associated with the current client.

'--------- CHECK ACCESS HERE via function ab_HasAccessToClient
--------

'If the call returns 1, then the employee has access.

'Otherwise, just write out "Access to this client is denied."

'CALL SP - Not sure what parameters need to go with it or its syntax

Execute_SP("rx_sp_HasAccessToClient '" & ClientSSN & "', 1)

'When it returns can check it here........

if ab_HasAccessToClient result is a 1 then

'boolean would be 1 so show panel

Else

'boolean would be 0 so show access denied

'allow them to go back to the original page.

end if

'PANEL ONE
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXX----
>

ON SQL SERVER: Stored Procedure

----------------------------------------------------------
--------------------------------

rx_sp_HasAccessToClient

CREATE PROCEDURE [dbo].[ rx_sp_HasAccessToClient]

@EmployeeID INT,

@ClientSSN varchar(50),

@ReturnBitValue = OUTPUT

/*

' Parameters here passed via call from Form.asp - not sure what is passed
yet.

*/

AS

set nocount on

/*

Written by Mike Belcher 9/27/2007 for Form.asp

'Calls ab_HasAccessToClient function - not sure of the syntax as of yet,
just making flow.

'Gets return bit and passes that back to the call from Form.asp

*/

GO

----------------------------------------------------------
--------------------------------

ON SQL SERVER: User-Defined Function

----------------------------------------------------------
--------------------------------

ab_HasAccessToClient

CREATE FUNCTION ab_HasAccessToClient (@employeeID INT, @ClientSSN
VARCHAR(50))

@ClientSSN varchar(50),

@EmployeeID,

@ReturnBitValue = OUTPUT

AS

SELECT 1

FROM tblEmployeesClients ec

INNER JOIN tblClients c ON ec.ClientID = c.ClientSSN

INNER JOIN tblEmployees e ON ec.Employee = e.EmployeeLogInName

WHERE e.EmployeeID= @EmployeeID

AND c.InActiveClient=0

AND c.ClientSSN = @ClientSSN

'Some Code here to save result bit ..

RETURN @ReturnBitValue 'Back to rx_sp_HasAccessToClient

----------------------------------------------------------
--------------------------------

</FLOW WITH CODE AND FUNCTIONS :>

View 5 Replies View Related

Stored Procedure And Calling User Defined Function

Sep 29, 2007

I seem to be getting tasks that I am not familiar with these days. I am a
guy that has coded it all in the asp page or in the code behind in .NET.
This problem is outlined below and I need a help / advice on doing this. I
had the flow of the 3 parts to it expanded below. A call is made to a Stored
Procedure, The SP then calls a user defined function that runs SQL, this
returns a 1 or 0 to the SP which then returns the value back to the call on
the asp page. This is a lot I know but it is the way the lead guy wants it
done. Any help so I can keep most of the hair I have left is appreciated :-)

Short list of process flow:

1. Form.asp calls to rx_sp_HasAccessToClient in SQL SERVER

2. rx_sp_HasAccessToClient then calls ab_HasAccessToClient

3. ab_HasAccessToClient runs SQL command on db and sends return bit back to
rx_sp_HasAccessToClient

4. rx_sp_HasAccessToClient then sends this back to the call in the Form.asp
page

5. Form.asp then checks the Boolean and if 1 then show or if 0 then deny.

<FLOW WITH CODE AND FUNCTIONS :>

This is not the correct syntax but is showing what I understand sort of how
this is to be done so far.

This panel loads up the Vendors and id's when the user clicks on the link
"view detailed list of vendors associated with this client". This is the
beginning of the process.

This is code in Form.asp

'PANEL ONE
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXX----
>

If ValidateInput(Request.Querystring("Postback"))="Fo rmDetails" then 'Check
Postback Type

'We need to load up vendors associated with the current client.

'--------- CHECK ACCESS HERE via function ab_HasAccessToClient
--------

'If the call returns 1, then the employee has access.

'Otherwise, just write out "Access to this client is denied."

'CALL SP - Not sure what parameters need to go with it or its syntax

Execute_SP("rx_sp_HasAccessToClient '" & ClientSSN & "', 1)

'When it returns can check it here........

if ab_HasAccessToClient result is a 1 then

'boolean would be 1 so show panel

Else

'boolean would be 0 so show access denied

'allow them to go back to the original page.

end if

'PANEL ONE
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXX----
>

ON SQL SERVER: Stored Procedure

----------------------------------------------------------
--------------------------------

rx_sp_HasAccessToClient

CREATE PROCEDURE [dbo].[ rx_sp_HasAccessToClient]

@EmployeeID INT,

@ClientSSN varchar(50),

@ReturnBitValue = OUTPUT

/*

' Parameters here passed via call from Form.asp - not sure what is passed
yet.

*/

AS

set nocount on

/*

Written by Mike Belcher 9/27/2007 for Form.asp

'Calls ab_HasAccessToClient function - not sure of the syntax as of yet,
just making flow.

'Gets return bit and passes that back to the call from Form.asp

*/

GO

----------------------------------------------------------
--------------------------------

ON SQL SERVER: User-Defined Function

----------------------------------------------------------
--------------------------------

ab_HasAccessToClient

CREATE FUNCTION ab_HasAccessToClient (@employeeID INT, @ClientSSN
VARCHAR(50))

@ClientSSN varchar(50),

@EmployeeID,

@ReturnBitValue = OUTPUT

AS

SELECT 1

FROM tblEmployeesClients ec

INNER JOIN tblClients c ON ec.ClientID = c.ClientSSN

INNER JOIN tblEmployees e ON ec.Employee = e.EmployeeLogInName

WHERE e.EmployeeID= @EmployeeID

AND c.InActiveClient=0

AND c.ClientSSN = @ClientSSN

'Some Code here to save result bit ..

RETURN @ReturnBitValue 'Back to rx_sp_HasAccessToClient

----------------------------------------------------------
--------------------------------

</FLOW WITH CODE AND FUNCTIONS :>

View 1 Replies View Related

Calling Encrypt2 Function From Sqlserver Select Query

Jul 23, 2005

I use a database that has user names stored in Encrypted format usingthe following API.Declare Sub Encrypt2 Lib "QPRO32.DLL" (ByVal Work As String, ByValPASSWORD As String)Every time i require the user name i have to again decrypt the nameusing the same function.My problem is that when i fetch a large number of records i have toloop through every record and call the encrypt function for eachrecord.Instead of binding the recordset to my control i need to loopthrough and fill my controlA MSHFlexGrid in Vb6.0.Is there a way out to this problem that will make my record populatiogfaster withoutout changing the current Encrypted users.Thanx in Advance

View 2 Replies View Related

Newbe Question: Calling Function Inside Select

Jul 20, 2005

Hi!I have a scalar function that returns integer:xview (int)Now, I'm trying to build a procedure that has the following selectinside:select atr1, xview(atr2)from tablenameBut, I get the 'Invalid name' error when I try to execute thatprocedure.If I got it right, I must use user.fn_name() syntax, but I cannot usedbo.xview() inside my procedure since it means xview will always beexecuted as dbo, which is unaccaptable.I'm a bit confused, so any hint is very welcomed.Thanks!Mario.

View 4 Replies View Related

Calling User Defined Function From Stored Procedure

Jul 30, 2007

When i call user defined function from stored procedure, i am getting an error.

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.MyFunction'.


why this error is happening?.

i am calling this way... dbo.MyFunction(param1,param2)

View 6 Replies View Related

Calling A Function From A Remote (linked Server) Problem

May 21, 2008



Hi guys,

I am trying to call a function from a remote sql server (linked server) using the following syntax:

select [ServerName].[dbName].dbo.CLRHelloWorld('SomeMessage'). I am getting the following error:

The object name '[ServerName].[dbName].dbo.' contains more than the maximum number of prefixes. The maximum is 2.

I need to call this function remotely and using this syntax; I don't want to use OPENQUERY...

Any hints?

Yousef

View 4 Replies View Related

Using EXECUTE Statements Calling An Extended Stored Procedures From Function..

Apr 29, 2004

Hi, all
I'm using Sql server 2000
I want to make select statement dynamically and return table using function.
in sp, I've done this but, in function I don't know how to do so.
(I have to create as function since our existing API..)

Following is my tials...
1.
alter Function fnTest
( @fromTime datetime, @toTime datetime)
RETURNS Table
AS

RETURN Exec spTest @from, @to
GO

Yes, it give syntax error..

2. So, I found the following


From Sql Server Books Online, Remark section of CREATE FUNCTION page of Transact-SQL Reference , it says following..

"The following statements are allowed in the body of a multi-statement function. Statements not in this list are not allowed in the body of a function: "
.....
* EXECUTE statements calling an extended stored procedures.

So, I tried.

alter Function fnTest
( @fromTime datetime, @toTime datetime)
RETURNS Table
AS

RETURN Exec master..xp_msver
GO

It doesn't work... syntax err...

Here I have quick question.. How to execute statements calling an extended stored procedures. any examples?


Now, I'm stuck.. how can I create dynamic select statement using function?

I want to know if it's possible or not..

View 13 Replies View Related

Calling Managed CLR Procedure From Inside User Defined Function -- How To ?

May 15, 2008

I have several UDFs created. Inside one of the UDFs I need to execute a dynamic SQL statement and then take that result and do something else with it, before returning the final value.

I know you can not execute a stored proce from inside a function. I also know you can not use the EXEC statement.

I did read that you could use an external stored procedure and/or managed CLR procedures inside a function.

I have created a managed procedure CLR (C#) that simply executes a passed statemetn and returns the value to the calling routine. I have this all coded and is working fine.

However, I am struggling with knowing how to call this CLR procedure from inside my function, seeing how I can not use EXEC statement.

Any advice on how to do this?

Thanks,
Bruce

View 1 Replies View Related

Trouble Calling Function In View &&amp; Multi-Table Select...

Aug 13, 2007

How Do I fix View(below) or Multi-Table select(below) to use this Function to return distinct rows via qcParent_ID?

Following Function populates a field (with concat list of related titles) with other required fields:

Create Function [dbo].openItemsIntoList(@Delimeter varchar(15),@qcparent_ID varchar(1000))

Returns Varchar(8000) as

Begin

Declare @Lists as varchar(8000);



Select @Lists = '';

Select @Lists = @Lists + itemTitle + @Delimeter From z_QClocate_openAll_Qualifier

Where @qcParent_ID = qcParent_ID;

Return Substring(@Lists,1,len(@Lists)-len(@Delimeter));

End

works perfect against single table select (returning 54 distinct rows by qcParent_ID):

Select a.qcParent_ID, a.Facility, a.Modality, openItemListToFix

From dbo.a2_qcEntryForm a

JOIN (Select DISTINCT qcParent_ID, dbo.openItemsIntoList(' / AND ',qcParent_ID) as openItemListToFix FROM dbo.a3_qcItems2Fix) i

on a.qcParent_ID = i.qcParent_ID

But data is needed from 3 tables...
- Created a VIEW that returns all (82) rows (negating distinct of the function on qcParent_ID)
- Failed Miserably Integrating Function call into a multi-table select (inexperienced with complex joins)


This VIEW returns ALL (82) rows in table:


CREATE VIEW z_QClocate_openAll AS

SELECT dbo.a1_qcParent.qcStatus, dbo.a1_qcParent.qcAlert, dbo.a3_qcItems2Fix.qcParent_ID, dbo.a3_qcItems2Fix.qcEntryForm_ID,

dbo.a3_qcItems2Fix.itemComplete, dbo.a3_qcItems2Fix.itemTitle, dbo.a2_qcEntryForm.Facility, dbo.a2_qcEntryForm.Modality

FROM dbo.a1_qcParent INNER JOIN

dbo.a2_qcEntryForm ON dbo.a1_qcParent.qcParent_ID = dbo.a2_qcEntryForm.qcParent_ID INNER JOIN

dbo.a3_qcItems2Fix ON dbo.a2_qcEntryForm.qcEntryForm_ID = dbo.a3_qcItems2Fix.qcEntryForm_ID AND

dbo.a1_qcParent.qcParent_ID = dbo.a3_qcItems2Fix.qcParent_ID

WHERE (dbo.a1_qcParent.qcStatus = 'Awaiting Attn') AND (dbo.a3_qcItems2Fix.itemComplete = 0) OR

(dbo.a1_qcParent.qcStatus = 'In Process') OR

(dbo.a1_qcParent.qcStatus = 'Re-Opened')

Calling like this returns ALL 82 rows (negating the functions distinct):

Select a.qcParent_ID, a.qcStatus, a.qcAlert, a.itemComplete, a.Facility, a.Modality, openItemListToFix

From z_QClocate_openAll a

JOIN (Select DISTINCT qcParent_ID, dbo.openItemsIntoList(' / AND ',qcParent_ID) as openItemListToFix FROM dbo.a3_qcItems2Fix) i

on a.qcParent_ID = i.qcParent_ID

AND THEN THERES...
Failing miserably on Integrating the Function call into This SELECT ON MULTI-TABLES:

How to integrate the Function call:
JOIN (Select DISTINCT qcParent_ID, dbo.openItemsIntoList(' / AND ',qcParent_ID) as openItemListToFix FROM dbo.a3_qcItems2Fix) i

on a.qcParent_ID = i.qcParent_ID

into the multi-table Select relationships (while maintaining Where & Order By):

SELECT dbo.a1_qcParent.qcStatus, dbo.a1_qcParent.qcAlert, dbo.a3_qcItems2Fix.qcParent_ID, dbo.a3_qcItems2Fix.qcEntryForm_ID,

dbo.a3_qcItems2Fix.itemComplete, dbo.a3_qcItems2Fix.itemTitle, dbo.a2_qcEntryForm.Facility, dbo.a2_qcEntryForm.Modality

FROM dbo.a1_qcParent INNER JOIN

dbo.a2_qcEntryForm ON dbo.a1_qcParent.qcParent_ID = dbo.a2_qcEntryForm.qcParent_ID INNER JOIN

dbo.a3_qcItems2Fix ON dbo.a2_qcEntryForm.qcEntryForm_ID = dbo.a3_qcItems2Fix.qcEntryForm_ID AND

dbo.a1_qcParent.qcParent_ID = dbo.a3_qcItems2Fix.qcParent_ID

WHERE (dbo.a1_qcParent.qcStatus = 'Awaiting Attn') AND (dbo.a3_qcItems2Fix.itemComplete = 0) OR

(dbo.a1_qcParent.qcStatus = 'In Process') OR

(dbo.a1_qcParent.qcStatus = 'Re-Opened')


View 3 Replies View Related

Calling CLR Stored Procedure From Within A CLR Table-valued Function Giving Errors

Apr 6, 2007

We are trying to create a TVF that executes a CLR Stored Procedure we wrote to use the results from the SP and transform them for the purposes of returning to the user as a table.






Code Snippet

[SqlFunction ( FillRowMethodName = "FillRow",

TableDefinition = "CustomerID nvarchar(MAX)",

SystemDataAccess = SystemDataAccessKind.Read,

DataAccess = DataAccessKind.Read,

IsDeterministic=false)]

public static IEnumerable GetWishlist () {

using (SqlConnection conn = new SqlConnection ( "Context Connection=true" )) {

List<string> myList = new List<string> ();

conn.Open ();

SqlCommand command = conn.CreateCommand ();

command.CommandText = "GetObject";

command.Parameters.AddWithValue ( "@map", "Item" );

command.CommandType = System.Data.CommandType.StoredProcedure;

using ( SqlDataReader reader = command.ExecuteReader ( System.Data.CommandBehavior.SingleRow )) {

if (reader.Read ()) {

myList.Add ( reader[0] as string );

}

}



return (IEnumerable)myList;

}

}



When command.ExecuteReader is called, I am getting an "Object not defined" error. However, the stored procedure can be used in SQL Management Studio just fine.






Code SnippetEXEC GetObject 'Item'



Is there some sorf of trick I am missing?



Thank you!

View 3 Replies View Related

Calling Scalar Valued Function From SSIS OleDB Command Transformation

Mar 2, 2007

Hi There,

I need to call a function to calculate a value. This function accepts a varchar parameter and returns a boolean value. I need to call this function for each row in the dataflow task. I thought I would use an oledb command transformation and for some reason if I say..

'select functioname(?)' as the sqlcommand, it gives me an error message at the design time. In the input/output properties, I have mapped Param_0(external column) to an input column.

I get this erro.."syntax error, ermission violation or other non specific error". Can somebiody please suggest me what's wrong with this and how should I deal this.

Thanks a lot!!

View 8 Replies View Related

Calling VB Based SQLCLR Function Failed With Error Can't Load System.Web Assembly

Apr 20, 2007

I created a CLR function based on following VB code:



Imports Microsoft.SqlServer.Server

Public Partial Class SqlClrVB

<Microsoft.SqlServer.Server.SqlFunction()> _

Public Shared Function GetTotalPhysicalMemory() As Integer

GetTotalPhysicalMemory = My.Computer.Info.TotalPhysicalMemory

End Function

End Class



The VB code was complied into a DLL called totalmem.dll and call following TSQL to map it into a SQL function:



create assembly totalmem from '!WORKINGDIR! otalmem.dll'

WITH PERMISSION_SET=UNSAFE

go

create function fnGetTotalMem()

returns int

as external name totalmem.SqlClrVB.GetTotalPhysicalMemory

go



When I call this function, it returned following error:

select dbo.fnGetTotalMem()



Msg 6522, Level 16, State 2, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'fnGetTotalMem':

System.IO.FileNotFoundException: Could not load file or assembly 'System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.

System.IO.FileNotFoundException:

at Microsoft.VisualBasic.MyServices.Internal.ContextValue`1.get_Value()

at My.MyProject.ThreadSafeObjectProvider`1.get_GetInstance()

at SqlClrVB.GetTotalPhysicalMemory()

.



Anyone knows why I'm hitting this error? I didn't reference any System.Web interface why it needs to load System.Web assembly? The same code runs OK if I compile it as a separate VB application out side of SQL Server 2005.



Thanks much,



Zhiqiang

View 2 Replies View Related

Help Convert MS Access Function To MS SQL User Defined Function

Aug 1, 2005

I have this function in access I need to be able to use in ms sql.  Having problems trying to get it to work.  The function gets rid of the leading zeros if the field being past dosn't have any non number characters.For example:TrimZero("000000001023") > "1023"TrimZero("E1025") > "E1025"TrimZero("000000021021") > "21021"TrimZero("R5545") > "R5545"Here is the function that works in access:Public Function TrimZero(strField As Variant) As String   Dim strReturn As String   If IsNull(strField) = True Then      strReturn = ""   Else      strReturn = strField      Do While Left(strReturn, 1) = "0"         strReturn = Mid(strReturn, 2)      Loop   End If  TrimZero = strReturnEnd Function

View 3 Replies View Related

In-Line Table-Valued Function: How To Get The Result Out From The Function?

Dec 9, 2007

Hi all,

I executed the following sql script successfuuly:

shcInLineTableFN.sql:

USE pubs

GO

CREATE FUNCTION dbo.AuthorsForState(@cState char(2))

RETURNS TABLE

AS

RETURN (SELECT * FROM Authors WHERE state = @cState)

GO

And the "dbo.AuthorsForState" is in the Table-valued Functions, Programmabilty, pubs Database.

I tried to get the result out of the "dbo.AuthorsForState" by executing the following sql script:

shcInlineTableFNresult.sql:

USE pubs

GO

SELECT * FROM shcInLineTableFN

GO


I got the following error message:

Msg 208, Level 16, State 1, Line 1

Invalid object name 'shcInLineTableFN'.


Please help and advise me how to fix the syntax

"SELECT * FROM shcInLineTableFN"
and get the right table shown in the output.

Thanks in advance,
Scott Chang

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved