Call Webservice From Trigger/stored Proc

Sep 17, 2004

Is it possible to call an external web service from a SQL Server trigger or stored procedure?

View 6 Replies


ADVERTISEMENT

How Can I Call One Or More Stored Procedures Into Perticular One Stored Proc ?

Apr 23, 2008

Hello friends......How are you ? I want to ask you all that how can I do the following ?
I want to now that how many ways are there to do this ?



How can I call one or more stored procedures into perticular one Stored Proc ? in MS SQL Server 2000/05.

View 1 Replies View Related

Need To Call Sp_replicationdboption From Another Stored Proc

Feb 10, 2006

i am trying to call sp_replicationdboption from another stored procedure that i wrote myself but it is giving me the following error
"sp_replicationdboption cannot be executed within a transaction"
note that i'm not using begin or commit transaction in my stored procedure
is there a way to do this

View 1 Replies View Related

Remote Stored Proc Call

Aug 29, 2007

I'm calling this from another sql server....
I created a linked server... and want to restore database backups on the other box....
The restore script runs fine when ran locally but fails with the message below when calling it remotely

Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Server: Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.

CREATE PROCEDURE usp_restore_database_backups AS

RESTORE DATABASE BesMgmt
FROM DISK = 'D:MSSQLBACKUPBesMgmtBesMgmt_backup_device.bak '
WITH
--DBO_ONLY,
REPLACE,
--STANDBY = 'D:MSSQLDataBesMgmtundo_BesMgmt.ldf',
MOVE 'BesMgmt_data' TO 'D:MSSQLDataBesMgmt.mdf',
MOVE 'BesMgmt_log' TO 'D:MSSQLDataBesMgmt.ldf'

WAITFOR DELAY '00:00:05'


EXEC sp_dboption 'BesMgmt', 'single user', true
GO


I have set it to read only dbo only .... single user.... still get the same message....
does anyone have any suggestions....

View 6 Replies View Related

HELP!! HOW TO CALL A DTS PACKAGE FROM A STORED PROC

Jul 20, 2005

I'm a rookie with MSSQL. I need to run a DTS package to export a result setto an MX Excel spread sheet. I need to call the DTS from a stored procedureand pass it three values, depending on the input parameters to the storedproc.DTS package is no problem. Pretty easy with the DTS wizard. My problem isthat I can't figure out how to instansiate the DTS package object from astored proc and pass the three values as parameters to the DTS package sothey can populate the parameters I created in it.I found an article related to it, but I'm too much of a rookie to grasp it.It showed how to do this from a stored procedure:EXEC @hr = sp_OASetProperty @oPKG, 'GlobalVariables("MyGVName").Value,'MyGVValue'IF @hr <> 0BEGINPRINT '*** GlobalVariable Assignment Failed'EXEC sp_displayoaerrorinfo @oPKG, @hrENDI have three values and tree global variables to populate. Do I need to dothe above 3 times?How do I instantiate the package object? I've read up some on sp_OACreate,but I don't get it, yet.How do I initiate the variable @oPKG?It contains the name of the sp_OACreate string, right? How do I address aDTS package in the sp_OACreate string?I would really appreciate just writing out the sp_OACreate string and how Ipass values for three existing global variables to a DTS package named"DTS_1".I'm under some real pressure to get this done.Thanks for any help I can get.Gunny

View 1 Replies View Related

Recursive Call In Stored Proc

Jul 20, 2005

Hi allI am implementing a stored procedure which needs to recursively callitself until specific condition is reached, Could anyone give someadvice about that?Thanks a lotRobert Song

View 3 Replies View Related

Retreive @@Identity From Stored Proc Call

May 3, 2007

Hi all, I've been struggling with this one for a while, but am still doing something wrong: I have three tables which need to be updated. I have a stored proc which accomplishes the first writes the data in for the first 2 tables. The last table is a one to many, so needs a seperate stored proc which will be called multiple times depending on the number of items in the order.  My only question I am trying to get to here is: How do I get the first stored proc to return me the primary key value from the 1st insert (NOT the second)? I've tried a few different methods: the current one shown below returns me  "2", as in the number of inserts performed.   Dim InsertCmd As New SqlCommand("WriteOrder", oSQLConn)
InsertCmd.CommandType = CommandType.StoredProcedure
InsertCmd.Parameters.AddWithValue("@CartTotal", Session("CartTotal"))
InsertCmd.Parameters.AddWithValue("@CARDFNAME", BillingInfo("CARDFNAME"))
InsertCmd.Parameters.AddWithValue("@CARDLNAME", BillingInfo("CARDLNAME"))
...
InsertCmd.Parameters.AddWithValue("@CONTACTEMAIL", BillingInfo("CONTACTEMAIL"))
InsertCmd.Parameters.AddWithValue("@COMPANYMATCH", 0)
InsertCmd.Parameters.AddWithValue("@RECNUM", 0)
Response.Write("---" & InsertCmd.ExecuteNonQuery().ToString() & "---")


---------------------STORED PROC---------------------

ALTER PROCEDURE [dbo].[WriteOrder]
@CartTotal float,
@CARDFNAME varchar(30),
...
@CONTACTEMAIL varchar(100),
@COMPANYMATCH bit,
@RecNum int = 0 OUTPUT
AS

INSERT INTO [dbo].[ORDER]
([OrderDate], [OrderTotal], [CARDFIRSTNAME], [CARDLASTNAME], [BILLINGADDR1], [BILLINGADDR2], [BILLINGADDR3], [BILLINGCITY], [BILLINGSTATE], [BILLINGPOSTALCODE], [BILLINGCOUNTRY], [BILLINGPHONE], [BILLINGEMAIL], [CCTYPE], [ACCOUNTNUMBER], [CARDEXPIREMONTH], [CARDEXPIREYEAR], [CVV2], [TransactionID], [TransDateStamp], [ProcessorAuthCode])

VALUES
(GetDate() ,@CartTotal , @CARDFNAME, @CARDLNAME, @BILLINGADDRESS1, @BILLINGADDRESS2, @BILLINGADDRESS3, @BILLINGCITY, @BILLINGSTATE, @BILLINGPOSTALCODE, @BILLINGCOUNTRY, @BILLINGPHONE, @BILLINGEMAIL, @CCTYPE, @ACCOUNTNUMBER, @EXPIRATIONMONTH, @EXPIRATIONYEAR, @CVV2,@TransactionID, @TransDateStamp,@ProcessorAuthCode)

SET @RecNum = @@IDENTITY

INSERT INTO [dbo].[CONTACT]
([ORDERID], [CONTACTFNAME], [CONTACTLNAME], [CONTACTADDRESS1], [CONTACTADDRESS2], [CONTACTADDRESS3],
[CONTACTCITY], [CONTACTSTATE], [CONTACTPOSTALCODE], [CONTACTCOUNTRY], [CONTACTPHONE], [CONTACTEMAIL], [COMPANYMATCH])
VALUES
(@@IDENTITY , @CONTACTFNAME, @CONTACTLNAME, @CONTACTADDRESS1, @CONTACTADDRESS2, @CONTACTADDRESS3, @CONTACTCITY, @CONTACTSTATE, @CONTACTPOSTALCODE, @CONTACTCOUNTRY, @CONTACTPHONE, @CONTACTEMAIL, @COMPANYMATCH)
RETURN @RecNum
GO

 I've also tried returning parameters like this, with no luck:        InsertCmd.Parameters(32).SqlDbType = SqlDbType.Int        InsertCmd.Parameters(32).Direction = ParameterDirection.ReturnValue        Response.Write("---" & InsertCmd.Parameters(32).Value() & "---")Any help is greatly appreciated! 

View 4 Replies View Related

Call A DTS Export Into Excel From A Stored Proc ??

Jul 8, 1999

I'm new to 7.0 and to DTS, and I find it all very confusing and need some help. :)

1) I need to automatically import data from a text file on a daily basis. Can someone tell me in short, simple steps how to set this up?

2) I need to export certain data into a new Excel sheet. Can this be triggered from a stored procedure, by calling some function? If yes, is it possible to send parameters to this function?

thanks bunches.

-M

View 1 Replies View Related

Call A Oracle Stored Proc In SQL SERVER

Jul 20, 2005

We have set up Oracle database as a linked server in SQL Server.We are able to access Oracle tables fine.I am trying to call a Oracle stored procedure in SQL Server as follows:declare @p1 varchar(1000)set @p1 = 'HHH'exec GENRET..OPS$GENRET.BOB_TEST_PROC @p1This is the message:Server 'GENRET' is not configured for RPC.Please help.Thanks in advancev

View 1 Replies View Related

Call To Stored Proc Returning Null Datatable

Jun 6, 2007

I have a stored proc which should be returning a datatable.  When I execute it manually it returns all requested results. However, when I call it via code (C#) it is returning a null table which leads me to believe the problem is in my code.  I'm not getting any errors during runtime.  Any help at all would be a BIG help!
private void PopulateControls()    {        DataTable table = CartAccess.getCart();    }
public static DataTable getCart() {        DbCommand comm = GenericDataAccess.CreateCommand();        comm.CommandText = "sp_cartGetCart";
        DbParameter param = comm.CreateParameter();        param.ParameterName = "@CartID";        param.Value = cartID;        param.DbType = DbType.String;        param.Size = 36;        comm.Parameters.Add(param);
        DataTable table = (GenericDataAccess.ExecuteSelectCommand(comm));        return table; }
public static DataTable ExecuteSelectCommand(DbCommand command)    {        // The DataTable to be returned         DataTable table;        // Execute the command making sure the connection gets closed in the end        try        {            // Open the data connection             command.Connection.Open();            // Execute the command and save the results in a DataTable            DbDataReader reader = command.ExecuteReader();            table = new DataTable();            table.Load(reader);            // Close the reader             reader.Close();        }        catch (Exception ex)        {            Utilities.SendErrorLogEmail(ex);            throw ex;        }        finally        {            // Close the connection            command.Connection.Close();        }        return table;    }

View 1 Replies View Related

Can You Call A Stored Proc That Returns A Table Variable Using ADO?

Jan 8, 2004

I have a stored proc that inserts into a table variable (@ReturnTable) and then ends with "select * from @ReturnTable."

It executes as expected in Query Analyzer but when I call it from an ADO connection the recordset returned is closed. All the documentation that I have found suggests that table variables can be used this way. Am I doing somthing wrong?

View 1 Replies View Related

Call Cfusion Web Page/webserice From Stored Proc

Jun 12, 2008

I am trying to call a CF web page/web service from a SQL 2005 stored proc and getting proxy info cannot be created. I cannot use stored proc 2005 CLR assembly because it will help us in creating only .asmx proxy not CFC proxy , any help would be appreciated.

exec master..xp_cmdshell 'http://wifi.abctest.com/Test/lartnerCall.cfm

Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1
The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.

View 1 Replies View Related

Transact SQL :: Stored Proc Call By Adding Right Login

May 14, 2015

I have dw schema in the database, owned by user dw.The login name is dw. The login had db_owner right in the database. The default schema for the login on the database is dw.Now Once I assign 'sysadmin' serverrole to dw login, I started seeing stored proc not found error, if try to execute stored proc without mentioning dw.spname;Also I am seeing table not found error while quering tables under dw schema, after the change.

View 20 Replies View Related

Stored Proc Call, 'table Name' As String, And T-sql Statement

Jun 16, 2006

I need to write a storedproc that receives the name of a table (as a string) and inside the stored proc uses select count(*) from <tablename>. The problem is the passed in tablename is a string so it can't be used in the select statement. Any ideas how I can do what I want?



TIA,

barkingdog



View 1 Replies View Related

NH: Best Practices Approach - Call Stored Proc - Or Run It Via Linked Server?

Mar 27, 2007

what pro's cons would there be to having a linked server run a local stored proc against another sql server or create that stored proc on that other sql server and call it from there in the c# code.
i would think that calling the stored proc would be more efficient that running a linked server - but please let me know your thoughts. I'm not sure i can have permission to add a stored proc on that server, so possibly the linked server is the only solution - but if i can put a stored proc on that server should i?
thanks.
Jeff 

View 4 Replies View Related

SQL Server 2012 :: Call Stored Proc Once Per Each Row Of A Table Without Using CURSOR

Jul 10, 2014

I have a situation where I need to call a stored procedure once per each row of table (with some of the columns of each row was its parameters). I was wondering how I can do this without having to use cursors.

Here are my simulated procs...

Main Stored Procedure: This will be called once per each row of some table.

-- All this proc does is, prints out the list of parameters that are passed to it.

CREATE PROCEDURE dbo.MyMainStoredProc (
@IDINT,
@NameVARCHAR (200),
@SessionIDINT
)
AS
BEGIN

[Code] ....

Here is a sample call to the out proc...

EXEC dbo.MyOuterStoredProc @SessionID = 123

In my code above for "MyOuterStoredProc", I managed to avoid using cursors and was able to frame a string that contains myltiple EXEC statements. At the end of the proc, I am using sp_executesql to run this string (of multipl sp calls). However, it has a limitation in terms of string length for NVARCHAR. Besides, I am not very sure if this is an efficient way...just managed to hack something to make it work.

View 9 Replies View Related

How To Call AS400 Stored Proc And Evaluate The Return Code?

May 30, 2007

I am trying to use SSIS to update an AS400 DB2 database by calling a stored procedure on the AS400 using an OLE DB command object. I have a select statement running against the SQL Server 2005 that brings back 20 values, all of which are character strings, and the output of this select is piped into the OLE DB command object. The call from SSIS works just fine to pass parameters into the AS400 as long as the stored procedure being called does not have an output parameter defined in its signature. There is no way that I can find to tell the OLE DB command object that one of the parameters is an output (or even an input / output) parameter. As soon as one of the parameters is changed to an output type, I get an error like this:






Code Snippet


Error: 0xC0202009 at SendDataToAs400 1, OLE DB Command [2362]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8000FFFF.

Error: 0xC0047022 at SendDataToAs400 1, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Command" (2362) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

Error: 0xC0047021 at SendDataToAs400 1, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0202009. There may be error messages posted before this with more information on why the thread has exited.

Information: 0x40043008 at SendDataToAs400 1, DTS.Pipeline: Post Execute phase is beginning.

Information: 0x40043009 at SendDataToAs400 1, DTS.Pipeline: Cleanup phase is beginning.

Task failed: SendDataToAs400 1

Warning: 0x80019002 at RetrieveDataForSchoolInitiatedLoans: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

Warning: 0x80019002 at Load_ELEP: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "Load_ELEP.dtsx" finished: Failure.





I really need to know if the call to the AS400 stored procedure succeeded or not, so I need a way to obtain and evaluate the output parameter. Is there a better way to accomplish what I am trying to do? Any help is appreciated.

View 3 Replies View Related

How To Call Webservice From Tsql?

May 24, 2007

Hello!

From an stored proc I want to call an existing web-service.

How to do that??

Must it be done using CLR-procs or is there some way in TSQL?

Greetings

Bjorn

View 11 Replies View Related

FoxPro Triggers Call FoxPro Stored Proc Calls SQL Server Stored Procedure

Mar 10, 2005

I didn't want to maintain similar/identical tables in a legacy FoxPro system and another system with SQL Server back end. Both systems are active, but some tables are shared.

Initially I was going to use a Linked Server to the FoxPro to pull the FP data when needed. This works. But, I've come up with what I believe is a better solution. Keep in mind that these tables are largely static - occassional changes, edits.

I will do a 1 time DTS from FP into SQL Server tables.

I then create INSERT and UPDATE triggers within FoxPro.

These triggers fire a stored procedure in FoxPro that establishes a connection to the SQL Server and fire the appropriate stored procedure on SQL Server to CREATE and/or UPDATE the corresponding table there.

In the end - the tables are local to both apps.

If the UPDATES or TRIGGERS fail I write to an error log - and in that rare case - I can manually fix. I could set it up to email me from within FoxPro as well if needed.

Here's the FoxPro and SQL Server code for reference for the Record Insert:

FOXPRO employee.dbf InsertTrigger:
employee_insert_trigger(VAL(Employee.ep_pk),Employ ee.fname,Employee.lname,Employee.email,Employee.us er_login,Employee.phone)

FOXPRO corresponding Stored Procedure:
FUNCTION EMPLOYEE_INSERT_TRIGGER
PARAMETERS wepk,wefname,welname,weemail,WEUSERID,WEPHONE

nhandle=SQLCONNECT('SS_PDITHP3','userid','password ')

IF nhandle<0
m.errclose=.f.
IF !USED("errorlog")
USE tisdata!errorlog IN SELECT(1)
m.errclose=.t.
ENDIF

SELECT errorlog
INSERT INTO errorlog (date, time, program,source,user) ;
values (DATE(), TIME(), 'EMPLOYEE_INSERT_TRIGGER','nhandle<0 PARAMS: '+STR(wepk)+wefname+welname+weemail+WEUSERID+WEPHO NE,GETENV("username"))

IF m.errclose
USE IN errorlog
ENDIF
RETURN

ENDIF
nquery="exec ewo_sp_insertNewEmployee @WEPK ="+STR(wepk)+",@WEFNAME ='"+wefname+"',@WELNAME ='"+welname+"',@WEEMAIL ='"+weemail+"',@WEUSERID ='"+weuserid+"',@WEPHONE='"+wephone+"',@RETCODE =0"
nsucc=SQLEXEC(nhandle,nquery)

SQLDISCONNECT(nhandle)

IF nSucc<0
m.errclose=.f.
IF !USED("errorlog")
USE tisdata!errorlog IN SELECT(1)
m.errclose=.t.
ENDIF

SELECT errorlog
INSERT INTO errorlog (date, time, program,source,user) ;
values (DATE(), TIME(), 'EMPLOYEE_INSERT_TRIGGER','nSucc<0 PARAMS: '+STR(wepk)+wefname+welname+weemail+WEUSERID+WEPHO NE,GETENV("username"))

IF m.errclose
USE IN errorlog
ENDIF
ENDIF

RETURN

SQL SERVER Stored Procedure called from FOXPRO Stored Procedure
CREATE procedure ewo_sp_insertNewEmployee (
@WEPK int,
@WEFNAME char(20),
@WELNAME char(20),
@WEEMAIL char(50),
@WEUSERID char(15),
@WEPHONE char(25),
@RETCODE int OUTPUT
)

AS

insert into WO_EMP (
WE_PK,
WE_FNAME,
WE_LNAME,
WE_EMAIL,
WE_USERID,
WE_PHONE
)

VALUES (
@WEPK,
@WEFNAME,
@WELNAME,
@WEEMAIL,
@WEUSERID,
@WEPHONE
)


IF @@ERROR <> 0
BEGIN
SET @RETCODE=@@ERROR
END
ELSE
BEGIN
-- SUCCESS!!
SET @RETCODE=0
END

return @RETCODE
GO

View 2 Replies View Related

Trigger Job From Stored Proc

Mar 28, 2008

s it poaaible? as if so how can i trigger a job from a stored procedure?

View 2 Replies View Related

Trigger/Stored Proc Help Needed

Nov 29, 2007

I am fairly new to using triggers and stored procs in SQL Server and could use some help.

Scenario:

I have a table on SQL Server database running on a Windows server (of course) that contains information about articles. A second server in the shop is set up as a LAMP (Linux, Apache, MySQL, PHP) box. What I would like to do is any time the SQL server table is updated or a record is added, tables in MySQL would then be updated to reflect the changes on the first server. My thought is to try and do this with triggers and a stored proc.

Questions:
1) Assuming MySQL can be accessed through ODBC, can an ODBC connection be set up inside a stored proc or trigger, or would this have to be done through the Windows ODBC Data Sources tool in the control panel?

2) Can a Stored Proc be used in a trigger?

3) Can a Stored Proc call an outside function, such as an API call for third party software?

Thanks in advance.

View 1 Replies View Related

DDL Create User In T-SQL Stored Proc/trigger

Dec 7, 2004

Hello SQL Server programming gurus:

I am trying to create a trigger that fires after a user logon and logoff and does the following:

creates a new user
deletes data from old temp table

Then I need to create a stored procedure that executes dynamically to
drop old users
remove the old user account access

We are running SQL Server 2000.
Since I am new to T-SQL programming could anyone help point me in the right direction? Can I write dynamic SQL in a trigger to do these things?

View 1 Replies View Related

Raising Error From Trigger To Stored Proc

Jul 23, 2005

I feel like I'm missing something obvious here, but I'm stumped...I have a stored procedure with code that looks like:INSERT INTO MyTableA ( ...fields... ) VALUES (...values...)IF (@@ERROR <> 0)BEGINROLLBACK TRANSACTION;RAISERROR('An error occurred in the stored proc.', 16, 1);RETURN(1);END--FORCING AN ERROR AT THE END FOR TESTING PURPOSESRAISERROR('Proc Successful',16,1)On MyTableA, there is a trigger that loops through the inserted data andstops the insert in certain circumstances, returning an error:IF (some criteria)BEGINROLLBACKRAISERROR('An error occurred in the trigger.',16,1)RETURNENDWhen I call the stored procedure from VB (connecting via RDO) witherror-causing data, the trigger successfully stops the insert, and adds thetrigger-error-msg to the errors collection, but it does NOT seem to createan error situation back in the stored procedure. The procedure finishes upwith the "Proc Successful" message, so that when I iterate through theerrors collection back in VB, I have "Proc Successful" followed by "An erroroccurred in the trigger."Is there some way I'm not finding to have the calling procedure recognizethat a raiserror occurred in the trigger and behave appropriately for anerror situation?Jen

View 1 Replies View Related

Trigger Not Executing CLR Code/stored Proc

May 7, 2008

I have a database trigger that is set to call a CLR trigger/stored proc when a certain field in a table is updated. The issue is that if i execute the stored proc manually in enterprise studio, it works perfectly but the same call made through the trigger does not go through. A few more details -


I have CLR integration enabled on the sql server.

The dbo has UNSAFE ASSEMBLY rights

I have the both the assembly and the serialized dll imported in the database.


Here's the definition of the stored proc -


CREATE PROCEDURE [dbo].[WriteXMLNotification]

@TaskID [nvarchar](20)

WITH EXECUTE AS CALLER

AS

EXTERNAL NAME [DataInterfaceWebServices].[TaskUpdateXMLWriter.WriteXMLNotification].[run]



and the trigger -



CREATE TRIGGER [dbo].[tr_Task_U]

ON [dbo].[_Task]

FOR UPDATE, INSERT AS

IF UPDATE (TaskType_Status) OR UPDATE (TaskType) OR UPDATE (TaskType_SubType1)

BEGIN

SET NOCOUNT ON;

DECLARE @status AS INT

DECLARE @taskType AS INT

DECLARE @taskSubType AS INT

DECLARE @taskID as sysname

DECLARE @cmd as sysname

DECLARE @parentTask as sysname

DECLARE @NotificationXMLTaskID as sysname



SELECT @status = [TaskType_Status] FROM inserted

SELECT @taskType = [TaskType] FROM inserted

SELECT @taskSubType = [TaskType_SubType1] FROM inserted

SELECT @taskID = [TaskID] FROM inserted

SELECT @parentTask = [Parent_TaskID] FROM inserted

SELECT @NotificationXMLTaskID = [MCCTaskID] FROM _TaskNotificationXML WHERE [MCCTaskID] = @parentTask



IF (@status = 2602) AND (@taskType = 2282) AND (@taskSubType = 19500)

BEGIN

exec WriteXMLNotification @taskID;

END

ELSE IF (@taskType = 2285) AND (@parentTask IS NOT NULL) AND (@NotificationXMLTaskID IS NOT NULL)

BEGIN

exec WriteXMLNotification @parentTask;

END



END


I stepped into the trigger and it seems to execute the line " exec WriteXMLNotification @taskID;" but nothing happens, but if I run that same line manually, it works. Could it be that the impersonation by the EXECUTE AS clause is causing it to fail?

Please advise!

Thanks in Advance,
-Mihir Sonalkar.

View 1 Replies View Related

Trigger Error When Inserting Stored Proc Output Into Temp Table.

Feb 18, 2008





I writing a unit test which has one stored proc calling data from another stored proc. Each time I run dbo.ut_wbTestxxxxReturns_EntityTest I get a severe uncatchable error...most common cause is a trigger error. I have checked and rechecked the columns in both of the temp tables created. Any ideas as to why the error is occurring?

--Table being called.


ALTER PROCEDURE dbo.wbGetxxxxxUserReturns

@nxxxxtyId smallint,

@sxxxxxxxxUser varchar(32),

@sxxxxName varchar(32)

AS

SET NOCOUNT ON




CREATE TABLE #Scorecard_Returns

(
NAME_COL varchar(64),
ACCT_ID int,

ACCT_NUMBER varchar(10),

ENTITY_ID smallint,

NAME varchar(100),

ID int,

NUM_ACCOUNT int,

A_OFFICER varchar(30),

I_OFFICER varchar(30),

B_CODE varchar(30),

I_OBJ varchar(03),

LAST_MONTH real,

LAST_3MONTHS real,

IS int

)




ALTER PROCEDURE dbo.ut_wbTestxxxxReturns_EntityTest



AS

SET NOCOUNT ON




CREATE TABLE #Scorecard_Returns

(
NAME_COL varchar(64),
ACCT_ID int,

ACCT_NUMBER varchar(10),

ENTITY_ID smallint,

NAME varchar(100),

ID int,

NUM_ACCOUNT int,

A_OFFICER varchar(30),

I_OFFICER varchar(30),

B_CODE varchar(30),

I_OBJ varchar(03),

LAST_MONTH real,

LAST_3MONTHS real,

IS int

)

INSERT #Scorecard_Returns(

NAME_COL ,

ACCT_ID

ACCT_NUMBER ,

ENTITY_ID,

NAME,

ID,

NUM_ACCOUNT,

A_OFFICER,

I_OFFICER,

B_CODE,

I_OBJ ,

LAST_MONTH

LAST_3MONTHS,

IS

)

EXEC ISI_WEB_DATA.dbo.wbGetxxxxxcardUserReturns

@nId = 1,

@sSUser = 'SELECTED USER',

@sUName = 'VALID USER'

View 4 Replies View Related

Column Level Constraints... I Need A Constraint, Not A Rule, Or A Trigger Or A Stored Proc... Thnx.

Jul 17, 2001

Hello all.

First of all, I've been a reader of swynk.com for quite sometime now, and I'd like to say 'thank you' to everyone who contributes.

Today, I'm the town moron.. haha I'm having issues with column level constraints. I have a varchar(50) where I want to keep *,=,#,/, .. etc, OUT OF the value input. I don't want to strip them. I simply want for sql to throw an error if the insert contains those (and other characters). The only characters that I want in the column are A-Z and 0-9. However, it's not a set number of characters per insert. It always varies... There has to be an easier way to do this than creating a constraint for every possibilty... Any help would be greatly appreciated.

tia,

Jeremy

View 1 Replies View Related

Calling Webservice From A Trigger

Jun 26, 2007

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 1 Replies View Related

Can You Trace Into A Stored Proc? Also Does RAISERROR Terminate The Stored Proc Execution.

Feb 13, 2008

I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code.
So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message:
Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages.
(1 row(s) affected)
(1 row(s) affected)
I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution?
Also, Is there a way to trace into a stored procedure through Query Analyzer?
-------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised:
SELECT  @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND  Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId
IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END 
 

View 3 Replies View Related

Maximum Characters In A Proc/SQL Statement Call

Jan 8, 2002

How many characters can be used in a query?

Say I have a query that inserts into a varchar(4000). I need to do ~100 of them. Can all this data be passed at once. Or would I need to break it up?

Thoughts?

Dano

View 1 Replies View Related

How To Call Proc On Linked Oracle Server

Sep 12, 2006



Sorry if this is very stupid question, but i've spent too long searching for the answer:

I have a linked Oracle server set up for RPC in SQL server 9 db. How do I call it? I've tried this, but gives Unspecified error:

"

OLE DB provider "OraOLEDB.Oracle" for linked server "SANSORA1" returned message "Unspecified error".

"

From this:



declare @UserName char(20) -- Current Username

declare @Password varchar(20) -- Current Password

declare @PasswordNew varchar(20) -- New Password

declare @PasswordCfm varchar(20) -- Confirm New Password

set @UserName = '900878'

set @Password = '900878'

set @PasswordNew = '777'

set @PasswordCfm = '777'

declare @return int

exec SANSORA1..PCG.USR_CHANGEPASSWORD @UserName ,@Password ,@PasswordNew ,@PasswordCfm;

--Exec ( 'SANSORA1..PCG.USR_CHANGEPASSWORD (' + @UserName + ' ,' + @Password + ' ,' + @PasswordNew + ' ,' + @PasswordCfm + ')')

View 1 Replies View Related

Access Webservice From A Stored Procedure.

Apr 1, 2008

Hi everybody,
How can I access a webservice from inside a stored procedure? any help is greatly appriciated.

View 3 Replies View Related

Calling Webservice From A T-SQl Stored Procedure

Oct 6, 2006

Hi,



I need to call a web service (consume a webservice)from a T-SQL stored procedure. Is there a way to do this. If not is there a way to make a simple http request, something like a utl_http in oracle.



At the moment iam using a MSSOAP30.SOAPCLIENT object created using sp_OACreate to make this call. However this means that the soap toolkit be installed on the pc on which SQL server is installed. I was hoping to find a completely independent way.



Also when i call sp_OACreate where does sqlserver 2005 look to find that object. Iam thinking of putting the MSSOAP30.dll on that machine, if all else fails.





Ahmad

View 1 Replies View Related

CLR Stored Procedure Consume Webservice

Nov 8, 2006

Can you have a CRL stored procedure call a webservice that returns a dataset?

View 7 Replies View Related







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