Internal Activation - Calls Stored Procs In Other DBs
Mar 7, 2006
Hi all
I am using internal activation on a queue to process the messages, should an error be encountered I call stored procedure A in the same database to log the error. Part of the processing in stored procedure A is a call to stored procedure B in another database (on the same server), however I have not been able to get this call to B to work. Currently I get the error "The server principal XXXXXX is not able to access the database YYYYYYY under the current security context".
I have tried various combinations (too many to remember) of database owners, roles and permissions as well as EXECUTE AS on both A and B and the Queue but none seem to work. Can anyone give me simple example of a setup which would allow this cross database call to work?
Thanks
Ian
View 6 Replies
ADVERTISEMENT
Jun 25, 2006
CLR function has the following few lines which is invoked from Internal Activation Stored Procedure:
SqlCommand command = Connection.CreateCommand();
command.CommandText = "CREATE ASSEMBLY " + """ + AsmName + """ +" AUTHORIZATION [dbo]"+ " FROM " + "'" + regasm.UncPath + "'" + " WITH PERMISSION_SET=SAFE";
command.ExecuteNonQuery();
I am getting the following error:
"Could not impersonate the client during assembly file operation."
The CLR function is invoked from Service Broker internal activation stored procedure.
"SELECT user_name()" returns dbo just before CREATE ASSEMBLY execution.
SqlContext.WindowsIdentity.Name is "NT AUTHORITYSYSTEM" as the Data Engine runs with the LocalSystem account.
How do I create a the necessary security context for "CREATE ASSEMBLY" to succeed ?
Service Broker Queue activation with EXECUTE AS = "SELF", "OWNER", domain account or dbo, all result in the above error. The Service Broker assembly having the internal activation stored procedure is registered "unsafe".
Many Thanks.
View 13 Replies
View Related
May 17, 2006
I use Try ... catch blok in my activation stored procedure. When SQL Server raise error (e.g. Primary key violation) in Try blok, XACT_STATE in Catch blok has value 1 = commitable transaction and I can use rollback transaction to savepoint. But when I use Raiserror() in Try blok, XACT_STATE in Catch blok has value -1 = uncommitable transaction and I can't use rollback transaction to savepoint. When I drop automatic activation for given queue and I run this stored procedure with Raiserror(), XACT_STATE has value 1 = commitable transaction.
What a problem may cause this different behavior ?
Best Regards,
Pavel
View 4 Replies
View Related
May 5, 2007
Hi,
I'm using service broker queue with internal activation to run a stored procedure.
The DB server is windows 2003 R2, 4 cpu, with SQL server 2005 SP2.
When I'm runing the stored procedure directly from the sql management studio it takes about 75% of the cpu and running for about a minute, but when the stored procedure is activated by the queue internal activation (as a background process) it uses only 25% of the machine cpu (my guess it uses only 1 cpu insted of all 4 cpu) and running for much longer time (sometimes even more than one hour).
How can I change this behavior? I want it to run as fast as possible.
The queue decleration is:
CREATE QUEUE [TaskQueue]
WITH ACTIVATION (
STATUS = ON,
PROCEDURE_NAME = ProcessTasksProc,
MAX_QUEUE_READERS = 1,
EXECUTE AS SELF);
Thanks in advance,
Shai Brumer
View 9 Replies
View Related
Jun 1, 2006
I am looking for an example of a SP that shows the best practices for internal activation? In BOL this topic describes the typical patter for reading messages from a queue. What is the typical pattern for reading messages from a queue using an internally activated SP? Do we still need to loop (considering the message arrival actually fired the sp)?
Any advice provided would be helpful.
Thanks!
View 4 Replies
View Related
Nov 1, 2006
Hi, there,
I'm a Chinese fan of Microsoft SQL Server 2005. Through studying for MS SQL Server 2005, now I have a question about €˜internal activation of service broker€™.
At the moment I have done a test. The description of test is as following:
Firstly I set the status of the activation to be off (ALTER QUEUE [dbo].[TargetQueue] WITH STATUS = ON , RETENTION = OFF , ACTIVATION ( STATUS = OFF , PROCEDURE_NAME = [dbo].[Usp_HelloWorld] , MAX_QUEUE_READERS = 5 , EXECUTE AS N'dbo' )). And filled the queue with100000 Messages. Then that running another application caused the usage of the CPU achieve 100%. Secondly reset the status of the activation to be on and monitored the €˜sys.dm_broker_activated_tasks€™. The instance of the stored procedure got the maximum number 5 very quickly. The server was much slower at current.
The SQL Server 2005 Books Online says: €™an activated stored procedure typically processes one or more messages and returns a response to the service that originated the message or messages. When messages arrive faster than the stored procedure processes messages, Service Broker starts another instance of the stored procedure, up to the maximum number defined by the queue.€? But it doesn€™t mention the performance counter of the server, for example: If the usage of the CPU is very high, the queue readers should be as few as possible, even don't process the message of queue until the system is idle. Because a client choice service broker means he don€™t care immediately process message, contrarily he care the throughput of the system and don€™t bother the natural application.
So my question is whether the strategy of internal activation of service broker doesn€™t care the performance counter of the server, just care the speed of process message. And the priority of queue monitor in SQL Server€˜s internal processes. Unfortunately I can€™t find any information about these from books online and Internet.
View 3 Replies
View Related
May 23, 2007
I am kinda curious how the rest of you are doing your error handling inside your activation stored procedures...best practices says you should not rollback the part of your transaction that receives the record off of the queue...but using a try...catch block will only allow you to rollback the entire transaction. I tried using savepoints and starting the try...catch after the savepoint and the proc still gives me the error: "
The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction." when the error scenario is encountered.
I tried using @@Error and checking for errors at the statement level which would allow me to do a partial rollback, but the type of errors I receive (i.e. invalid data types etc) are aborting the entire batch instead of passing the error and continuing.
Can other people on this forum using service broker give me an idea of how you are getting around this issue?
View 3 Replies
View Related
Aug 14, 2007
New to service broker, and my books haven't come in yet. I'm trying to figure out how Internal Activation works.
The following code seems to "work", in terms of the inline code, but it doesn't seem to be going through the activation PROCs. Is there something else I need to set or do?
TIA, Bob
USE master;
GO
-- Ensure Service Broker functionality is enabled.
ALTER DATABASE ICEBOX SET ENABLE_BROKER;
GO
USE ICEBOX;
GO
-- Drop all sample objects if present.
IF EXISTS (SELECT * FROM sys.services WHERE name = N'BobsReceiveService') DROP SERVICE BobsReceiveService;
IF EXISTS (SELECT * FROM sys.service_queues WHERE name = N'BobsReceiveQueue') DROP QUEUE BobsReceiveQueue;
IF EXISTS (SELECT * FROM sys.services WHERE name = N'BobsSendService') DROP SERVICE BobsSendService;
IF EXISTS (SELECT * FROM sys.service_queues WHERE name = N'BobsSendQueue') DROP QUEUE BobsSendQueue;
IF EXISTS (SELECT * FROM sys.service_contracts WHERE name = N'BobsContract') DROP CONTRACT BobsContract;
IF EXISTS (SELECT * FROM sys.service_message_types WHERE name = N'BobsSendMessageType') DROP MESSAGE TYPE BobsSendMessageType;
IF EXISTS (SELECT * FROM sys.service_message_types WHERE name = N'BobsReceiveMessageType') DROP MESSAGE TYPE BobsReceiveMessageType;
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='PROCEDURE' AND ROUTINE_NAME = 'USP_BobsReceiveQueueMonitor') DROP PROCEDURE dbo.USP_BobsReceiveQueueMonitor;
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='PROCEDURE' AND ROUTINE_NAME = 'USP_BobsSendQueueMonitor') DROP PROCEDURE dbo.USP_BobsSendQueueMonitor;
GO
CREATE PROCEDURE dbo.USP_BobsSendQueueMonitor
AS
DECLARE @QueueTable TABLE
(status tinyint --Status of the message. For messages returned by the RECEIVE command, the status is always 1. Messages in the queue may contain one of the following values: 0=Received message1=Ready2=Not yet complete3=Retained sent message
,queuing_order bigint --Message order number within the queue.
,conversation_group_id uniqueidentifier --Identifier for the conversation group that this message belongs to.
,conversation_handle uniqueidentifier --Handle for the conversation that this message is part of.
,message_sequence_number bigint --Sequence number of the message within the conversation.
,service_name nvarchar(512) --Name of the service that the conversation is to.
,service_id int --SQL Server object identifier of the service that the conversation is to.
,service_contract_name nvarchar(256) --Name of the contract that the conversation follows.
,service_contract_id int --SQL Server object identifier of the contract that the conversation follows.
,message_type_name nvarchar(256) --Name of the message type that describes the message.
,message_type_id int --SQL Server object identifier of the message type that describes the message.
,validation nchar(2) --Validation used for the message. E=Empty N=None X=XML
,message_body varbinary(MAX) --Content of the message.
,message_id uniqueidentifier --Unique identifier for the message.
);
-- FROM BoL
-- The WHERE clause of the RECEIVE statement may only contain search conditions that use conversation_handle or conversation_group_id.
-- The search condition may not contain any of the other columns in the queue.
-- The conversation_handle or conversation_group_id may not be an expression.
DECLARE @SendDialog_Handle uniqueidentifier;
DECLARE @SendMsg varbinary(MAX);
DECLARE @SendMsgName nvarchar(256);
BEGIN
PRINT 'SEND' + CONVERT(VARCHAR,GETDATE(),109);
SELECT TOP (1)
@SendDialog_Handle = conversation_handle
,@SendMsg = message_body
,@SendMsgName = message_type_name
FROM
BobsSendQueue;
SELECT 'SEND',@SendDialog_Handle AS conversation_handle,CAST(@SendMsg AS VARCHAR(MAX)) AS message_body,@SendMsgName AS message_type_name;
END;
GO
CREATE PROCEDURE dbo.USP_BobsReceiveQueueMonitor
AS
DECLARE @QueueTable TABLE
(status tinyint --Status of the message. For messages returned by the RECEIVE command, the status is always 1. Messages in the queue may contain one of the following values: 0=Received message1=Ready2=Not yet complete3=Retained sent message
,queuing_order bigint --Message order number within the queue.
,conversation_group_id uniqueidentifier --Identifier for the conversation group that this message belongs to.
,conversation_handle uniqueidentifier --Handle for the conversation that this message is part of.
,message_sequence_number bigint --Sequence number of the message within the conversation.
,service_name nvarchar(512) --Name of the service that the conversation is to.
,service_id int --SQL Server object identifier of the service that the conversation is to.
,service_contract_name nvarchar(256) --Name of the contract that the conversation follows.
,service_contract_id int --SQL Server object identifier of the contract that the conversation follows.
,message_type_name nvarchar(256) --Name of the message type that describes the message.
,message_type_id int --SQL Server object identifier of the message type that describes the message.
,validation nchar(2) --Validation used for the message. E=Empty N=None X=XML
,message_body varbinary(MAX) --Content of the message.
,message_id uniqueidentifier --Unique identifier for the message.
);
-- FROM BoL
-- The WHERE clause of the RECEIVE statement may only contain search conditions that use conversation_handle or conversation_group_id.
-- The search condition may not contain any of the other columns in the queue.
-- The conversation_handle or conversation_group_id may not be an expression.
DECLARE @ReceiveDialog_Handle uniqueidentifier;
DECLARE @ReceiveMsg varbinary(MAX);
DECLARE @ReceiveMsgName nvarchar(256);
BEGIN
PRINT 'RECEIVE' + CONVERT(VARCHAR,GETDATE(),109);
SELECT TOP (1)
@ReceiveDialog_Handle = conversation_handle
,@ReceiveMsg = message_body
,@ReceiveMsgName = message_type_name
FROM
BobsReceiveQueue;
SELECT 'RECEIVE',@ReceiveDialog_Handle AS conversation_handle,CAST(@ReceiveMsg AS VARCHAR(MAX)) AS message_body,@ReceiveMsgName AS message_type_name;
END;
GO
CREATE MESSAGE TYPE BobsSendMessageType VALIDATION = NONE;
CREATE MESSAGE TYPE BobsReceiveMessageType VALIDATION = NONE;
CREATE CONTRACT BobsContract
(BobsSendMessageType SENT BY INITIATOR
,BobsReceiveMessageType SENT BY TARGET
);
CREATE QUEUE BobsSendQueue
WITH ACTIVATION
(STATUS = ON
,PROCEDURE_NAME = dbo.USP_BobsSendQueueMonitor
,MAX_QUEUE_READERS = 1
,EXECUTE AS OWNER
)
;
CREATE SERVICE BobsSendService ON QUEUE BobsSendQueue;
CREATE QUEUE BobsReceiveQueue
WITH ACTIVATION
(STATUS = ON
,PROCEDURE_NAME = dbo.USP_BobsReceiveQueueMonitor
,MAX_QUEUE_READERS = 1
,EXECUTE AS OWNER
)
;
CREATE SERVICE BobsReceiveService ON QUEUE BobsReceiveQueue (BobsContract);
GO
DECLARE @BobsDialog_Handle UNIQUEIDENTIFIER;
DECLARE @SendMsg NVARCHAR(100);
SET @SendMsg = N'Hello Bob';
BEGIN TRANSACTION;
BEGIN DIALOG CONVERSATION @BobsDialog_Handle FROM SERVICE BobsSendService TO SERVICE N'BobsReceiveService' ON CONTRACT BobsContract WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @BobsDialog_Handle MESSAGE TYPE BobsSendMessageType (@SendMsg);
COMMIT TRANSACTION;
SELECT 'Conversation started on Handle ' + CAST(@BobsDialog_Handle AS VARCHAR(36));
GO
DECLARE @BobsDialog_Handle UNIQUEIDENTIFIER;
DECLARE @ReceiveMsg NVARCHAR(100);
DECLARE @ResponseMsg NVARCHAR(100);
DECLARE @ReceiveMsgName SYSNAME;
SET @ResponseMsg = N'Back at ya';
BEGIN TRANSACTION;
RECEIVE TOP(1)
@BobsDialog_Handle = conversation_handle
,@ReceiveMsg = message_body
,@ReceiveMsgName = message_type_name
FROM
BobsReceiveQueue;
COMMIT TRANSACTION;
SELECT @ReceiveMsgName AS ReceiveMsgName,@ReceiveMsg AS ReceiveMsg;
SEND ON CONVERSATION @BobsDialog_Handle MESSAGE TYPE BobsReceiveMessageType (@ResponseMsg);
END CONVERSATION @BobsDialog_Handle;
GO
DECLARE @ResponseMsg NVARCHAR(100);
DECLARE @ResponseMsgName SYSNAME;
DECLARE @BobsDialog_Handle UNIQUEIDENTIFIER;
BEGIN TRANSACTION;
RECEIVE TOP(1)
@BobsDialog_Handle = conversation_handle
,@ResponseMsg = message_body
,@ResponseMsgName = message_type_name
FROM BobsSendQueue;
END CONVERSATION @BobsDialog_Handle;
COMMIT TRANSACTION;
SELECT @ResponseMsgName AS ResponseMsgName, @ResponseMsg AS ResponseMsg;
GO
IF EXISTS (SELECT * FROM sys.services WHERE name = N'BobsReceiveService') DROP SERVICE BobsReceiveService;
IF EXISTS (SELECT * FROM sys.service_queues WHERE name = N'BobsReceiveQueue') DROP QUEUE BobsReceiveQueue;
IF EXISTS (SELECT * FROM sys.services WHERE name = N'BobsSendService') DROP SERVICE BobsSendService;
IF EXISTS (SELECT * FROM sys.service_queues WHERE name = N'BobsSendQueue') DROP QUEUE BobsSendQueue;
IF EXISTS (SELECT * FROM sys.service_contracts WHERE name = N'BobsContract') DROP CONTRACT BobsContract;
IF EXISTS (SELECT * FROM sys.service_message_types WHERE name = N'BobsSendMessageType') DROP MESSAGE TYPE BobsSendMessageType;
IF EXISTS (SELECT * FROM sys.service_message_types WHERE name = N'BobsReceiveMessageType') DROP MESSAGE TYPE BobsReceiveMessageType;
GO
View 3 Replies
View Related
Mar 10, 2007
I am trying to find a solution to my woes creating CLR activation stored procedure that references Enterprise library data access dlls. Does anyone know best practices to deploy .NET 2.0 assemblies as SQLCLR. I am using VS2005 SqlClrProject to deploy my .NET 2.0 assemblies to Sql Server and it fails with error such as "Assembly was not found in the sql catalog". If I manually try to load a referenced assembly within SqlServer by browsing to the target location, like for example, System.Management.dll it fails with similar error, trying to resolve its dependencies. Even if I manage to walk through the dependency tree and resolve it to the final dependent dll, I am only able to load it in "UNSAFE" mode. Any recommendations, suggestions and feedback are welcome.
Thanks in advance.
View 4 Replies
View Related
Jan 15, 2008
Is there a way to namespace groups of stored procs to reduce confusion when using them?
For C# you can have ProjectName.ProjectSection.Classname when naming a class. I am just wondering if there is a way to do the same with SQL stored procs. I know Oracle has packages and the name of the package provides a namespace for all of the stored procs inside it.
View 1 Replies
View Related
May 7, 2004
HI,
CREATE PROCEDURE PROC1
AS
BEGIN
SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
FROM CUSTOMER A
INNER JOIN CUSTOMERPREFERENCE B
ON A.INTCUSTOMERID = B.INTCUSTOMERID
INNER JOIN TMPREFERENCE C
ON B.INTPREFERENCEID = C.INTPREFERENCEID
WHERE B.INTPREFERENCEID IN (6,7,2,3,12,10)
ORDER BY B.INTCUSTOMERID
END
IF I AM USING THIS PROC AS I/P TO ANOTHER PROC THEN IT GIVES NO PROBLEM AS I CAN USE ?
CREATE PROCEDURE PROC2
AS
BEGIN
CREATE TABLE #SAATHI(INTCUSTOMERID INT,CHREMAIL NVARCHAR(60),INTPREFERENCEID INT,CHRPREFERENCEDESC NVARCHAR(50))
INSERT INTO #SAATHI
EXEC PROC1
ST......1,
ST......2,
END.
BUT IF , I USE ANOTHER PROC SIMILAR TO THE FIRST ONE WHICH GIVES SLIGHTLY DIFFERENT RESULTS AND GIVES TWO SETS OF RESULTS,THEN WE HAVE A PROBLEM,HO TO SOLVE THIS :-
CREATE PROCEDURE MY_PROC
AS
BEGIN
SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
FROM CUSTOMER A
INNER JOIN CUSTOMERPREFERENCE B
ON A.INTCUSTOMERID = B.INTCUSTOMERID
INNER JOIN TMPREFERENCE C
ON B.INTPREFERENCEID = C.INTPREFERENCEID
WHERE B.INTPREFERENCEID IN (23,12,10)
ORDER BY B.INTCUSTOMERID
END
SELECT A.INTCUSTOMERID,MAX(case when A.intpreferenceid = 23 then '1'
else '0' end) +
MAX(case when A.intpreferenceid = 12 then '1'
else '0' end) +
MAX(case when A.intpreferenceid = 10 then '1'
else '0' end) AS PREFER
FROM CUSTOMER
GROUP BY A.INTCUSTOMERID
ORDER BY A.INTCUSTOMERID
END
WHICH NOW GIVES ME TWO SETS OF DATA , BOTH REQUIRED THEN HOW TO USE ONE SET OF RESULTS AS I/P TO ANOTHER PROC AND OTHER SET OF RESULTS AS I/P TO YET ANOTHER PROC .
CREATE PROCEDURE PROC2
AS
BEGIN
CREATE TABLE #SAATHI(INTCUSTOMERID INT,CHREMAIL NVARCHAR(60),INTPREFERENCEID INT,CHRPREFERENCEDESC NVARCHAR(50))
INSERT INTO #SAATHI
EXEC MY_PROC
ST......1,
ST......2,
END.
BUT, HERE I WANT TO USE FIRST DATASET ONLY , HOW TO USE IT ?
THANKS.
View 4 Replies
View Related
Aug 24, 2007
I need to develope a stored procedure (eventually called by a trigger) that creates a record in an event table for all the descendants of a drawing. There are 3 tables involved as example tables shown below:
DwgTable
DwgID (integer)--drawing record identifier
PrntDwgID (integer)--parent drawing record identifier (a previously defined DwgID from this table)
DwgEventTable
DwgEventID (integer)--record identifier
DwgID (integer)--value from DwgTable
EventID (integer)--value from EventTable
EventTable
EventID
There are other fields in two of the tables and only the fields shown in the DwgEventTable, but only the fields shown are required for adding a record in the DwgEventTable for a new event in the EventTable. The problem is identifying all the DwgID's of the descendant of the DwgID where the EventID occurred. There may be 0 to N descendants in 0 to N generations. I need to add a record for the original DwgID and all the descendant DwgID's in the DwgEventTable for the event identified by EventID.
I could do this from the client side, but a better place would be from the server side. I need some clue(s) on how to start coding a recursive stored procedure in SQL Server 2005. From what I have read, you cannot create a managed code procedure that appends or updates records--if managed code can add/modify records then I can do the above with managed code procedure.
Any Suggestions?
View 4 Replies
View Related
Oct 9, 2006
Hi.
How can I get the messages sent by the server while I'm executing a stored procedure via the JDBC driver?
I need to get my own debug messages (done through the print() function)
and also standard server messages (such as "x row(s) affected" or
results from SET STATISTICS TIME ON). Is this possible?
Many thanks.
Carlos
View 4 Replies
View Related
Aug 2, 2006
I set up a queue to activate a clr stored procedure upon receiving a message and nothing is happening. I have tried everything listed in this topic:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=200684&SiteID=1
except for saying "Recieve Top(0)" which didn't make any sense to me. I have set the database to trustworthy (all of this is taking place within a single database on a local server). There are messages on the queue, and I have the queue activation set to max_queue_readers=2, procedure_name=StoredProcedure1, execute as owner. I tried execute as self and that didn't work either. I signed the assembly that contains StoredProcedure1 and the assembly that it references. The only thing that appears in the sql error log is this (I trimmed off the timestamp):
AppDomain 15 (TriggerTest.dbo[runtime].14) is marked for unload due to common language runtime (CLR) or security data definition language (DDL) operations.
AppDomain 15 (TriggerTest.dbo[runtime].14) unloaded.
AppDomain 18 (TriggerTest.dbo[runtime].17) created.
If I call the stored procedure manually it works just fine
View 6 Replies
View Related
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
Mar 3, 2007
I am trying to raise an event using sp_trace_generateevent in my activation stored proc. (dbo.ActivationSP)
EXEC sp_trace_generateevent @event_class = 82, @userinfo = N'Test Event'
There is a Service broker service listening to this event.
The problem is the event is getting fired whenever the activation SP is executed (could see in profiler) but the secondtargetqueue doesnt receive any messaages.
But if manually do a "EXEC sp_trace_generateevent", the secondtargetqueue receives a messaage.
Both the queues and sevices are in the same database.
The following are the code snippets
-- Code for queue on which the activation is working
CREATE QUEUE TargetQueue WITH STATUS=ON, ACTIVATION (PROCEDURE_NAME = dbo.ActivationSP,MAX_QUEUE_READERS = 5,Execute AS 'dbo') ;
Create Service ReceiverService ON QUEUE TargetQueue (SampleContract)
-- Code for Queue listening to event
Create Queue SecondTargetQueue WITH status= ON
Create Service SecondReceiverService ON QUEUE SecondTargetQueue ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
CREATE EVENT NOTIFICATION TestNotification
ON SERVER FOR UserConfigurable_0 TO SERVICE 'SecondReceiverService', 'current database'
View 3 Replies
View Related
Mar 17, 2015
How can I find calls which do not exist in stored procedures and functions?We have many stored procedures, sometimes a stored procedure or function which is called does not exist. Is there a query/script or something that I can identify which stored procedures do not 'work' and which procedure/ function they are calling?I am searching for stored procedures and functions which are still called, but do not exist in the current database.
View 7 Replies
View Related
Apr 29, 2008
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
View 1 Replies
View Related
Sep 18, 2007
Hi all,
I have a problem while i create a proxy account.The situation is like this...There is a user who has an login in to the server.He has a stored procedure which calls some on the SSIS packages and XP_cmdshell...so this stored procedure basically load some data in to the tables .So for the login in order to execute the stored procedure as he is not a Sys admin I have created a proxy account in my account as Iam an SA and then in the proxies and in principals I selected his login name and this way I have created a credential and a proxy account.
Now the problem is if he logins with his id and password and try to execute the stored procedure it gives an error message
Server: 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.
....so this mean the login is not able to see the proxy account.So what I did is I created a job and then in the job owner tab I have selected his login and then created a step with the type operating system (CmdExec) as I need to just execute the stored procedure and used the proxy account that I have created.
so I gave the command -- exec <stored procedure> --.
But this job fails and gives the error message as
[298] SQLServer Error: 536, Invalid length parameter passed to the SUBSTRING function. [SQLSTATE 42000]....
So now ....first My question is am I doing in a right way....if its right then why Iam not able to execute the stored procedure.
If there is any other way through which I can execute the stored procedure using a proxy account for the logins who are not sys admins....please do let me know.....
Thanks
Raja.V
View 2 Replies
View Related
Nov 9, 2007
We are looking for some guidance with an issue we have picked up with our implementation of Service Broker here on the ABSA Capital project and I am hoping you can help or point us in the direction of someone.
The architecture we have implemented for service broker is to make use of an Activation stored procedure on two queues (1 SP per queue) to process the messages received. What we have found is that the activation stored procedure runs on a background session and its CPU time and memory just grows to the point where it brought one of our UAT servers to a grinding halt.
Is there anyway we can reduce the memory consumption of the activation stored procedure or is this one of those things that still need to be ironed out in Service Broker?
View 6 Replies
View Related
Feb 5, 2005
almost choked when i read the following recent post on The Daily WTF (http://thedailywtf.com/) the other day --
Logical Tiers? That Makes No Sense! (http://thedailywtf.com/ShowPost.aspx?PostID=28959)
i don't think i can do justice to how utterly stupid that stored procedure is
read the comments and have a laugh
one of the points made was that stored procedures are bad
this twigged something in my memory, so i dug around in my bookmarks, and sure enough, here's another decent discussion about stored procs --
Stored procedures are bad, m'kay? (http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx)
enjoy!
View 6 Replies
View Related
Jun 28, 2007
Hey guys.
I am wondering if it is possible to execute an .exe from a stored proc. I have an application that calls a stored proc and I am wanting for that stored proc to call up an app to show certain results. I have no way of doing this on the application itself as I dont have the source code. So, since I do have access to the SQL I am wondering if it could be done there.
Thanks
tibor
View 14 Replies
View Related
Apr 8, 2004
First off, this is a cross post which is also located here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=34073
Now, with the following stored procs and UDF, the Result is NULL and I cannot figure out why.
What I am doing is calling a stored procedure pass in an OUTPUT variable of type float. This stored proceudre then calls a different stored procedure passing the same OUTPUT variable of type float. The second stored procedure then calls a UDF passing in two variables to be multiplied ans this should set the OUTPUT variable to the result.
The UDF does return the correct result to the interior stored procedure, but the interior stored procedure does not pass the value back to the original stored procudure resultint in a Result value = NULL.
The code is below, just copy / past, execute and you will see exactly what I mean.
Any thoughts?
USE NORTHWIND
GO
CREATE FUNCTION RECTANGULAR_XSECTION
(@fWidth float, @fHeight float)
RETURNS float
AS
BEGIN
RETURN (@fWidth * @fHeight)
END
GO
CREATE PROCEDURE usp_shapes_GetRectangularXSection
@fResult float OUTPUT
AS
declare @fWidth float, @fHeight float
SELECT @fWidth = 108, @fHeight = 10
SELECT @fResult = [dbo].[RECTANGULAR_XSECTION](@fWidth, @fHeight)
SELECT @fResult AS CalledProcedureOkHere
GO
CREATE PROCEDURE usp_shapes_GetXSection
@fResult float OUTPUT
AS
EXECUTE usp_shapes_GetRectangularXSection @fResult
SELECT @fResult AS CallingProcedure_NULL?
GO
declare @fResult float
EXECUTE usp_shapes_GetXSection @fResult
SELECT @fResult as OutsideCallingProcedure_NULL?
GO
DROP FUNCTION RECTANGULAR_XSECTION
GO
DROP PROCEDURE usp_shapes_GetRectangularXSection
GO
DROP PROCEDURE usp_shapes_GetXSection
GO
Mike B
View 3 Replies
View Related
Nov 13, 2007
I am trying to improve my SQL and reduce the number of connections my website is making to the server.
Currently I have a stored procedure that gets all the games (and their details) for a single user which works fine which uses a WHERE userid = @userid which is an Int value.
I now need to create a new procedure which brings back the to all their "friends" (again by their userid). How is it best to do this? I originally tried to do a WHERE userID IN (@userid) but was unable to work out what variable type to use for @userid.
Is it best to do a single query in this way or is there a way to use the existing SP, loop through the collection of userids and join the result together into a single set to return? If an IN is the best route, what is the correct datatype for the variable?
View 5 Replies
View Related
Aug 8, 2006
can anyone help me out.... i need to compare a date that was retrieved from the database and the system date then it must be coded in the stored procs of the database.. help!!!!
View 10 Replies
View Related
Mar 8, 2004
All,
I'm relatively new to stored procs (not to SQL or SQL Server) and I am trying to get transactions to work within a stored proc. Here is the code:
(
--define the parameters that are needed
@userID char(32),
@userName varchar(50),
@status char(10),
@type char(10),
@password varchar(50),
@firstName varchar(100),
@lastName varchar(100),
@email varchar(200),
@domain varchar(50),
@pwdExpiry int,
@badLogin int,
@lastLogin datetime,
@full varchar(8000),
@read varchar(8000),
@noaccess varchar(8000)
)
AS
BEGIN TRAN tmp1
--First, insert the user into the system
INSERT INTO ptsUsers(UserID, UserName, Status, Type, Password, FirstName, LastName, Email, DomainName, PasswordExpiry, BadLoginAttempts, LastLoginDate)
values(@userID, @userName, @status, @type, @password, @firstName, @lastName, @email, @domain, @pwdExpiry, @badLogin, @lastLogin);
--Now, we need to add the function access edges for the user
declare @arrayValue char(32)
declare @rightID char(32)
declare @sepPos int
while patindex('%,%',@full)<>0
BEGIN
select @sepPos = patindex('%,%' , @full)
select @arrayValue = left(@full, @sepPos - 1)
-- replace the value with an empty string
select @full = stuff(@full, 1, @sepPos, '')
--create and parse the new id
declare @strID char(32),@tmpStr varchar(40)
set @tmpStr = newid();
set @strID = REPLACE(@tmpStr,'-','')
--get the access right id for full control
select @rightID = (Select AccessRightID from ptsAccessRights where Name = 'Write')
if(@rightID IS NOT NULL)
--insert the records that are full access
INSERT INTO ptsFunctionAccessEdges(FunctionAccessEdgeID, FunctionID, AccessRightID, UserID)
VALUES(@strID, @arrayValue, @rightID, @userID)
else
RAISERROR(50100,15,1)
END
COMMIT TRAN tmp1
IF @@TRANCOUNT > 0
RAISERROR(50101,15,1)
ROLLBACK TRAN tmp1
The transaction does not rollback when any errors occur at all. Any advice/help?
View 3 Replies
View Related
Apr 12, 2004
Hi all!
I am not an expert in Stored Procs. I would like to build one for a product list that would return a default value without using output parameters, if possible. Ultimately, I wouldn't be opposed to it.
It currently looks like this:
CREATE PROCEDURE ProductsByVendorNo
(
@VendorNum nvarchar(24)
)
AS
SELECT
P.ProductID AS ProductID,
P.ProductShortName AS ProductName,
P.ProductDesc AS ProductDesc,
U.UnitDesc AS Unit,
P.VendorProductNumber AS VendorNumber,
V.VendorName AS VendorName,
P.Price AS Price,
P.ImageThumb AS ImageThumb
FROM
tblProducts AS P
INNER JOIN tblUnitCodes AS U ON P.UnitCode=U.UnitCode
INNER JOIN tblVendors AS V ON P.VendorID=V.VendorID
WHERE
V.VendorsVendorNo = @VendorNum
AND P.Inactive = 0
ORDER BY
ProductName,
VendorNumber
I would like for it to return a default, constant value for the URL in the ImageThumb field, if this one is empty. I could not find good documentation of how to use IF statements for this case, i.e. to alter the return of just one field.
Suggestions?
Any input is highly appreciated.
Thanks in advance,
Mili Skikic
View 8 Replies
View Related
Mar 25, 2002
I want to "deny" create, update,and delete access on the dbo stored procs that are in the database, but do not want take away dbo owner access. is this possible?
can i create a role and deny access on a particular table in msdb? or a system table in the user table. Thus preventing the developers on the box access to update any of the dbo owned sp's and have them create their own user-owned stored procs?
this is sql7, sp3, development box.
thanks,
View 1 Replies
View Related
Apr 27, 2001
I am trying to set up a DTS package that selects data from one table on server A into another table on server B. I want to do a select statement that will do the following:
select store_name
from store (server A)
where date_created >= (select max(date_created) from store (server B)
I use EM 7.0 to manage all of my extracts, however, the data is moving from a Syabase (adaptive Server)onto another Syabase(adaptive server) machine.
Unfortunately, there is no functionality for a linked server connection.
I tried the following, (which doesn't error out), but is not displaying the source columns in the destination tab of the DTS package when setting up the transformations.
declare @maxdate datetime
exec serverB.dbo.sp_max_date_from_store @maxdate
select store_name
from store --(server A)
where date_created >= @store
Any help or suggestions would be greatly appreciated!
trevorb
View 1 Replies
View Related
Jun 19, 2000
I will be taking over a database that has almost no pk's or relations(this is not my choice, but a vendors)
Management is looking at stored procs to improve performance, but I am wondering
if the db is in this state will there really be a gain. I am pushing for normalization
first, but if anybody has any ideas or opinions I would appreciate
View 2 Replies
View Related
Sep 10, 2001
I have a stored proc that is run periodically which I execute with a DTS package. The problem is that I have to update a field for each record selected by this stored proc with a specific date. I need to prompt the operator for this date so I can use this value.
View 6 Replies
View Related
Jul 28, 2004
Ok, I've read somewhere(which I'm looking for again : ) that said that there are errors like DeadLock that kills the execution of a stored proc and there are other errors that do not necessarily kill the rest of the execution of the stored proc. Is that true? If so does anyone have any links I can read. What I'm seeing is a bad id in the foreign key and I think what is happening is that there was a unique constraint error on the first insert but the stored proc continued executing and used the bad id later on in the stored proc.
I do know I can use the @@error and will start using it but I need more proof to agree or not agree with my theory.
Thanks ahead of time for any information you can give me either way.
DMW
View 2 Replies
View Related
Aug 31, 2004
Is there any performance loss by setting up a view and creating a stored procedure to return a recordset from a view versus setting up the stored procedure to return the recordset from the tables directly?
What way should I do this?
Mike B
View 2 Replies
View Related
