MS SQL Server :: Calling VB.NET Function From Trigger In SQL 2005

SQL Server 2005 Query/trigger/function (whatever It Is That I Need)
Hey guys maybe you can help me out, been trying to figure this one out all day at work. I know how to use columns in a table to calculate another column in that same table. But I need to do some math on columns from a totally seperate table. Here is my scenario
table 1 = stock table
table 2 = Purchase order table
in table 2 there are line items that have ordered quantities for parts that we have ordered
in table 1 under each part number is a field for "quantity on order"
I need to compute the "quantity on order" in table 1 by summing all of the quantities in table 2 where the partnumber = the partnumber from table 1

quantity on order (table 1) = sum of all quantities (table 2) where the part numbers match
so for part number 516 i have this

table 2
poNumber             partNumber                 quantity
1                             516                             1
2                             516                             12
3                             516                             4

table 1
partNumber            inStock              onOrder
516                          0                        17(this is what i am trying to figure out how to compute)

any help on this qould be appreciated. I would like the database to automatically do this itself if at all possible.

View Replies !

Trigger Calling An Sp
I have a trigger that, when changes are made to the table (wo_chng_flds), will insert the
changes into a table called CHANGE_LOGS. The problem is that within Change_logs, there
is a field that is required -- PK. I'm trying to get my sp to run within the trigger so that my
INSERT CHANGE_LOGS statements will work. The way the trigger is right now, I get an
error of 'Incorrect syntax near EXECUTE'. Any suggestions? I've tried putting EXECUTE
proc_change_logs everywhere!!
Thanks! Your help is greatly appreciated.
Toni

if exists (select * from sysobjects where id = object_id('dbo.insupdtr_WO_CHNG_FLDS') and sysstat & 0xf = 8)
drop trigger dbo.insupdtr_WO_CHNG_FLDS
GO

CREATE TRIGGER insupdtr_WO_CHNG_FLDS ON dbo.WO_CHNG_FLDS
FOR INSERT,UPDATE
AS

DECLARE @WCF_ID INT, @OldData varchar(80), @NewData varchar(80),
@FieldName varchar(20)
SELECT @WCF_ID=(select WCF_ID from inserted)
SELECT @Chng=(execute proc_change_logs)

IF UPDATE (WCF_NAME)
BEGIN
SELECT @OldData=(select WCF_NAME from deleted)
SELECT @NewData=(select WCF_NAME from inserted)
SELECT @FieldName="WCF_NAME"
EXECUTE proc_change_logs
INSERT CHANGE_LOGS
(CL_ID, CL_TABLE, CL_FIELD, CL_FID, CL_DATE, CL_EMPL, CL_NEW_DATA,
CL_OLD_DATA)
VALUES(@chng, "WO_CHNG_FLDS", @FieldName, @WCF_ID, GETDATE(), user, @OldData,
@NewData)
END

IF UPDATE (WCF_DESC)
BEGIN
SELECT @OldData=(select WCF_DESC from deleted)
SELECT @NewData=(select WCF_DESC from inserted)
SELECT @FieldName="WCF_DESC"
EXECUTE proc_change_logs
INSERT CHANGE_LOGS
(CL_ID, CL_TABLE, CL_FIELD, CL_FID, CL_DATE, CL_EMPL, CL_NEW_DATA,
CL_OLD_DATA)
VALUES(@chng, "WO_CHNG_FLDS", @FieldName, @WCF_ID, GETDATE(), user, @OldData,
@NewData)
END
GO

View Replies !

Calling SQL Function In C#
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 Replies !

Calling A SP In A Function...?
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 Replies !

Calling A Function.
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 Replies !

Calling Webservice From A Trigger
Is it possible to call/fire a method in a webservice (.asmx) from a trigger in MS SQL 2005? I would like to send out a notification to all the admins whenever a new row is inserted into a table in our db. If possible, can someone show me an example of how to?

View Replies !

Calling Sp_oa* In Function
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 Replies !

Calling MS Access Function From DTS
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 Replies !

Calling A VB Function From An SQL Statement
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 Replies !

Calling A Function On A Different Server?
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 Replies !

Problem Calling A Function
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 Replies !

Calling User Function Without Dbo.
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 Replies !

Calling Stored Procedure In Trigger
Hi

I have a problem calling stored procedure in trigger..

When no exception occures stored procedure returns the value but if any exception occures executing that stored procedure then stored procedure will not return any value..

I have handled exception by returning values in case if any..

Here is the stored procedure

CREATE PROCEDURE BidAllDestinations
(
@ITSPID int,
@DestinationID int,
@BidAmount decimal (18,4),
@BidTime datetime,
@intErrorCode int out
)

AS
DECLARE @GatewayID int
DECLARE @GatewayExist int
SET @GatewayID = 0
SET @GatewayExist = 0
SET @intErrorCode = 0

UPDATE BID FOR CORRESPONDING GATEWAY
DECLARE GatewayList CURSOR FOR

SELECT Gateways.GatewayID
FROM Gateways INNER JOIN
GatewayDestinations ON Gateways.GatewayID = GatewayDestinations.GatewayID INNER JOIN
ITSPs ON Gateways.ITSPID = ITSPs.ITSPID
Where Gateways.ITSPID = @ITSPID AND DestinationID = @DestinationID

OPEN GatewayList

FETCH NEXT FROM GatewayList INTO @GatewayID

IF (@GatewayID = 0)

SET @intErrorCode = 1
ELSE
BEGIN
-- CHECK @@FETCH_STATUS TO SEE IF THERE ARE ANY MORE ROWS TO FETCH
WHILE @@FETCH_STATUS = 0

BEGIN

SELECT@GatewayExist = Gatewayid
FROMTerminationBids
WHEREGatewayid = @Gatewayid AND DestinationID = @DestinationID

IF @GatewayExist > 0

UPDATE TerminationBids
SET BidAmount = @BidAmount,
BidTime = getdate()

WHERE GatewayID = @Gatewayid AND DestinationID = @DestinationID

ELSE

INSERT INTO TerminationBids (GatewayID, DestinationID, BidAmount)
VALUES (@GatewayID,@DestinationID,@BidAmount)

IF @@ERROR <> 0
BEGIN
GOTO PROBLEM
CLOSE GatewayList
DEALLOCATE GatewayList
END

FETCH NEXT FROM GatewayList INTO @GatewayID

END
CLOSE GatewayList
DEALLOCATE GatewayList

END
PROBLEM:
BEGIN

SET @intErrorCode = 100

END
RETURN @intErrorCode
GO

TRIGGER CODE:::

CREATE TRIGGER TR_TerminationBid
ON dbo.TerminatorBidHistory FOR INSERT

AS

DECLARE @ITSPID int
DECLARE @DestinationID int
DECLARE @BidAmount decimal (18,4)
DECLARE @BidTime datetime
DECLARE @intErrorCode INT
DECLARE @DistinationList varchar (8000)
DECLARE @DestinationLevel varchar (100)
SET @intErrorCode = 0
SET @ITSPID = 0
SET @DistinationList = ''
-- CHECK ITPSID' S VALIDITY

SELECT@ITSPID = i.ITSPID, @DestinationID= i.DestinationID,
@BidAmount = i.BidAmount, @BidTime = i.BidTime
FROM Inserted i
INNER JOIN ITSPS ON ITSPS.ITSPID = i.ITSPID
INNER JOIN Destinations ON Destinations.DestinationID = i.DestinationID

EXEC BidAllDestinations @ITSPID,@DestinationID,@BidAmount,@BidTime, @intErrorCode = @intErrorCode output
SELECT @intErrorCode
Following should return value for @intErrorCode if any exception occures

Any one can help what is wrong with it?

Thanks

View Replies !

Trigger Calling Stored Procedure???
can a trigger firing cause a stored procedure to execute!! if this can be done then I will have more questions to follow! thanks, Scott

View Replies !

Calling An SSIS Package From A Trigger Causes It To Run For Ever
hi,

when calling an SSIS package from a trigger causes it to run for ever. Know why??

In Detail;

Consider that there are two tables with the same schema in a db. Lets name that Test1 and Test2.
I develope a package whihc will transform data from Test1 to Test2. I initiate this package from a Trigger for Insert on Test1. For eg.

CREATE TRIGGER Trigger_Test1

ON Test1

AFTER INSERT

AS

BEGIN

EXEC xp_cmdshell 'dtexec /FILE "C:TestTestPackage1.dtsx"'

END

This runs for ever when a record is inserted into the Test1 table.

But, when the trigger is on someother table , everything works fine.For eg, if the trigger is on the table TT1 & this trigger initiates the same package while inserting a record into TT1, everything is fine.

Can anyone help me on this.

Thanks
Man

View Replies !

Calling Native Code From A Trigger.
I've been searching for a bit but I can't seem to find a difinitive answer.

Can an SQL Server trigger call native C/C++ functions? If so, what is the mechanism?

Thanks in advance.

View Replies !

Calling A Stored Procedure In A Trigger
Hello,

I am trying to test a simple trigger on insert and it does not work when I call EXEC sp_send_cdosysmail.
However, the stored procedures does work if I right-click on it and select Execute Stored Procedure.

Below is a simple version of the trigger I am trying to implement. I know it works in SQL Server 2000 and 2005 but can't seem to get it to work in SQL Server 2005 Express. Any help is greatly appreciated!

ALTER TRIGGER [dbo].[trig_Tableinsert]
ON [dbo].[Table]
FOR INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

Print 'Hello'

-- Insert statements for trigger here
EXEC sp_send_cdosysmail some@one.com', 'notify@me.com','New Insert', 'test'

END

Thanks!

View Replies !

An Issue With Calling A Function From Storeprocedure.
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 Replies !

Calling A Function From A Stored Procedure
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 Replies !

Calling The User Defined Function!!!
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 Replies !

Calling User-defined Function Without 'dbo.' -- Possible?
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 Replies !

Howto: Calling Sp_executesql From A Function ?
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 Replies !

Calling A Serviced Component From A SQL CLR Function
Hi
Trying to call a component hosted in COM+ from my SQL CLR function. At first it seams System.EnterpriseServices is not deployed in sql so I used the CREATE ASSEMBLY command to add it, and redeployd my function.

Then it complains it can€™t load System.EnterpriseServices.Wrapper.dll, so I tried creating this one as well, the same way. I then get an exception that sais:

€œFailed to open assembly €˜SystemEnterpriseServiceWrapper€™€¦€?

Does any one have a clue, to what I should do to call a Serviced Component?

View Replies !

SSIS Function Calling Problem
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 Replies !

Trigger Calling Extended Stored Procedure
Hello,

I've installed a "For Update" trigger for some table that calls a extended stored procedure (ESP). In some cases, the ESP needs to modify the same table, maybe causing a recursive call of the trigger.

Is there a way to "disable" the trigger inside the code just before the operation that updates the table and "re-enable" it just after? I know I can disable recursive triggering at database level, but I'm developing a database add-on so I don't want to interfere with the existing triggers behavior (if any) for the rest of the tables.

10x.

View Replies !

Calling Asp.net Web Service From A Trigger Or Stored Procedure

Hi,
I need to call a webservice directly from a tigger or stored procedrue instead of creating a window service to read from a table then call the webservice .Is it possible ? if yes, please i need your support.
Thanks,

View Replies !

Calling A Function From A Function?
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 Replies !

Calling User Defined Function From Other Server
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 Replies !

Executing Store Procedre By Calling It From An Insert Trigger?
hi, I have a trigger on a table for insert, once there is new data into that table I want to run a nother store procedure by passing all input from inserted to the store procedure as input parameter. can I do that.

Thanks

Ali

View Replies !

Calling Encrypt2 Function From Sqlserver Select Query
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 Replies !

Newbe Question: Calling Function Inside Select
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 Replies !

Stored Procedure And Calling User Defined Function
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 Replies !

Dynamic Query Calling User Defined Function
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 Replies !

Calling A Function From A Remote (linked Server) Problem

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 Replies !

Calling User Defined Function From SSIS Packages
When I call call a user defined function from SSIS packages that returns non numeric value it does not work.

I am calling function as exec ? = Function_name ?,? using OLEDB connection type.

Thanks

Smriti

View Replies !

Calling User Defined Function From Stored Procedure
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 Replies !

Stored Procedure And Calling User Defined Function
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 Replies !

Instead Of Insert, Update Trigger Calling A Stored Procedure Question
I have to control my business rules in a Instead of Insert, Update Trigger.

Since the Control Flow is quite complicated I wanted to break it into stored procedures that get called from within the trigger.

I know that Insert Statements embedded in a Instead of Trigger do not execute the Insert of the trigger you are calling.

But... If I embed stored procedures that handle my inserts in the Instead of Insert trigger call the trigger and put in a endless loop or are the stored procedure inserts treated the same as trigger embedded inserts.

View Replies !

Using EXECUTE Statements Calling An Extended Stored Procedures From Function..
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 Replies !

Calling Managed CLR Procedure From Inside User Defined Function -- How To ?
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 Replies !

Performance Issues When Calling A .net Dll Function Thousands Of Times In 1 Report
The actual work that happens inside the functions is minimal. They're either returning a constant string, or doing some simple parsing on a string that was passed. I had 2 dataset fields made off of a function, and several report item properties set via a function. The purpose of all of this was to centralize the logic so several of my reports can use the same logic.

Taking out the dll calls, I saw a drop of 30s of DataRetrievalTime, but I will say that I also elminated some report logic as well. I would say that there were 5000 different calls to the dll function (I'm using a matrix and for each column and row cell, the dll function is getting called)

Are there any ideas as to why there's so much of an increase in time? It's a long shot, but is there anything a person can to do to maybe "cache" the .net function defintion/logic for ReportingServices to use?

View Replies !

Trouble Calling Function In View && Multi-Table Select...
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 Replies !

Calling CLR Stored Procedure From Within A CLR Table-valued Function Giving Errors
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 Replies !

Calling Scalar Valued Function From SSIS OleDB Command Transformation
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 Replies !

Calling VB Based SQLCLR Function Failed With Error Can't Load System.Web Assembly
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 Replies !

Trigger And Update() Function!
In the SQL 7.0 Documentation is indicated that the UPDATE(<fieldname>), if
used in a isert/update trigger, it return TRUE or FALSE based on presence of <field> in the SET List, if the trigger was fired by an UPDATE operation, or in the list of field to fill, if the trigger was fired by an INSERT operation.

Below there is a piece of code that demostrate the false documentation
assertion in the case of a trigger fired by an INSERT operation, infact even
if i have specified only the FIELD1 in INSERT Statement the trigger Print in the screen that i have touched FIELD2 too.

Now the SP1 don't solve this BUG, even if i had read some month ago (before
that SP1 was out) that in SP1 this will be fixed!

Anyone Can Help me to solve this problem, indicating me where i can post the
problem to sensitize the Microsoft's Service Pack Factory ?

Thank in Advance

PS:Sorry for my Bad English .... correct me please if some part are not
readable!

------[Cut Here]-------------------

CREATE TABLE TEST (FIELD1 INTEGER , FIELD2 INTEGER )
GO

CREATE TRIGGER TriggerIU_Test
ON TEST
FOR INSERT, UPDATE
AS

IF UPDATE(FIELD1) PRINT 'Field 1 Touched'

IF UPDATE(FIELD2) PRINT 'Field 2 Touched'
GO

print 'InsertIng '
INSERT INTO TEST (FIELD1) VALUES (2)

print 'Updating '
UPDATE TEST set FIELD1 = 1

GO
DROP TABLE TEST
------[Cut Here]-------------------

View Replies !