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'
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
I have an application that is set up using Service Broker to pass messages between services asynchronously. I am using event-based external activation and have successfully set up my event notification in SQL Server so if a message appears on any of my Service Broker queues, I'm getting the activation event from SQL Server sent to my activation service.
The problem that I am seeing is that every time I am posting a message onto a Service Broker queue, I am losing the event notification entry in the sys.event_notifications view and I'm not receiving my activation event notifications. When I execute the CREATE EVENT NOTIFICATION T-SQL statement to recreate the event notification, I'm getting the event notification immediately (since there are messages on the queues being monitored). The event notification appears to be registered until the next message is posted on the queue.
I have send a msg successful,but the proc failed. I altered the proc to correct the problem in the proc. The msg are still in the queue. Is there a manual way to excute the proc again to process the msg in the q? or do I wait for service broker to do it after a retry time (self recover)?
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
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?
I am having trouble executing a stored procedure on a remote server. On my local server, I have a linked server setup as follows: Server1.abcd.myserver.comSQLServer2005,1563
This works fine on my local server:
Select * From [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.dbo.TableName
This does not work (Attempting to execute a remote stored proc named 'Data_Add':
When I attempt to run the above, I get the following error: Could not locate entry in sysdatabases for database 'Server1.abcd.myserver.comSQLServer2005,1563'. No entry found with that name. Make sure that the name is entered correctly.
Could anyone shed some light on what I need to do to get this to work?
Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK
"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".
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 ?
I have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.
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?
I have a stored procedure "uspX" that calls another stored procedure "uspY" and I need to retrieve the return value from uspY and use it within uspX. Does anyone know the syntax for this?
I have about 5 stored procedures that, among other things, execute exactly the same SELECT statement
Instead of copying the SELECT statement 5 times, I'd like each stored proc to call a single stored proc that executes the SELECT statement and returns the resultset to the calling stored proc
The SELECT statement in question retrieves a single row from a table containing 10 columns.
Is there a way for a stored proc to call another stored proc and gain access to the resultset of the called stored proc?
I know about stored proc return values and about output parameters, but I think I am looking for something different.
I would like to know if the following is possible/permissible:
myCLRstoredproc (or some C# stored proc) { //call some T SQL stored procedure spSQL and get the result set here to work with
INSERT INTO #tmpCLR EXECUTE spSQL }
spSQL (
INSERT INTO #tmpABC EXECUTE spSQL2 )
spSQL2 ( // some other t-sql stored proc )
Can we do that? I know that doing this in SQL server would throw (nested EXECUTE not allowed). I dont want to go re-writing the spSQL in C# again, I just want to get whatever spSQL returns and then work with the result set to do row-level computations, thereby avoiding to use cursors in spSQL.
Hi! Let's say users of my app can build some reports based on a current database schema and my custom-database adapter will generate some SQL to get the right data. User can save his report template ( basically data needed to create SQL ) in some storage. Will it be better solution to generate stored procedure for such report and save stored proc to a database and their name with raport data so next time user needs this report it will use stored proc. Jarod
I wrote a stored procedure with three variables to be passed as input parameters. The stored procedure runs a select into statement into a temp table. The resulting temp table with another table was queried using right outer join to produce the desired results. The stored procedure compiles error free.However when I ran the stored procedure with the parameters(3) in ISQL/W (SQL Server 6.5) the headers(column-names) were displayed but no records. When runned as a query with the same parameter values, records were produced. Help please urgent.
How Can I Generating wrapper code for SQL Server Stored Procedure ??
If You will Go To The Following link you will see an example for Generating wrapper code for Oracle Database .. And Also the author say there is built in tool for Generating wrapper code for SQL Server http://www.codeproject.com/vb/net/OracleSPWrapper.asp my question .. where is this tools ???
Hi all,What I am trying to do is generate a stored procedure that is desiredto output XML in this type of format<Parent Device><Device><Device ID>1</DeviceID><ChildRegister><ChildRegisterID>22</ChildRegisterID></ChildRegister></Device><Device><Device ID>2</DeviceID><ChildRegister><ChildRegisterID>23</ChildRegisterID></ChildRegister></Device></Parent Device>The area of concern is the child register, the XML being generateddisregards the Device the ChildRegister belongs to and always places itas elements of the last device.<Parent Device><Device><Device ID>1</DeviceID></Device><Device><Device ID>2</DeviceID><ChildRegister><ChildRegisterID>23</ChildRegisterID></ChildRegister><ChildRegister><ChildRegisterID>22</ChildRegisterID></ChildRegister></Device></Parent Device>I am trying to produce XML like the first one I described and have yetto discover a way of associating the ChildRegister with the parentDevice in XML. I am not sure if it is a limitation of SQL Server, or ifmy implementation is incorrect. If anyone could post hints orsolutions, I would greatly appreciate it.A shortened version of the stored procedure is belowCheers :)AlvinSELECT1AS TAG,NULL AS PARENT,NULL AS [Device!2!DeviceID!element],NULL AS [ChildRegister!3!RegisterID!element]FROM udetails INNER JOINDetail ON udetails.ID = Detail.IDWHERE (uDetails.JobID = @ID)UNION ALLSELECT2 AS TAG,1 AS PARENT,TempTable.DeviceIDAS [Device!2!DeviceID!element],NULL AS [ChildRegister!3!RegisterID!element][color=blue]>From #Temp as TempTable INNER JOIN[/color]device ON TempTable.DeviceID = device.DeviceIDUNION ALLSELECT3 AS TAG,2 AS PARENT,NULL AS [Device!2!DeviceID!element],RegisterID AS [ChildRegister!3!RegisterID!element]FROM #Temp t INNER JOINregister ON t.DeviceID =register.DeviceIDFOR XML EXPLICIT
I have a stored procedure that is pulling 3 parameters: @user_id, @, begin_date and @end_date. The parameters are setup in the 'parameters' tab of the data set, and also the 'report parameters', however, when I go to run report, I get textbox for user_id, instead of a drop down with pick list.
I tried creating a separate dataset to bring in user_id's only and manually create a parameter for it in 'report parameters'. I then get a drop down box with repeating data, and when I run the report, I get back all user_id's instead of the one I chose.
I'm finding the parameters are the most difficult concept within RS. Does anyone know I can make this work?
I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.
I was wondering if it was possible to call reporting server web service directly from my sql server stored procedure. The call that I need to make to reporting web service needs to generate the report in a PDF format.
Is it possible to create an event handler in a VB.net application to run whenever a Stored Procedure is run.
My application has a scheduled task which is created and scheduled by users of the application, whenever this scheduled task is run I would like it to contact the application to kick off a sequence of tasks. I would appreciate if anybody could point me in the right direction.
I am using C# to insert the form details and passing event id (numeric) to the same stored procedure in my eror handler and need to retrieve the description from event_db to display in MessageBox..
Hi Peeps I have a SP that returns xml I have writen another stored proc in which I want to do something like this:Select FieldOne, FieldTwo, ( exec sp_that_returns_xml ( @a, @b) ), FieldThree from TableName But it seems that I cant call the proc from within a select. I have also tried declare @v xml set @v = exec sp_that_returns_xml ( @a, @b) But this again doesn't work I have tried changing the statements syntax i.e. brackets and no brackets etc..., The only way Ive got it to work is to create a temp table, insert the result from the xml proc into it and then set @v as a select from the temp table - Which to be frank is god awful way to do it. Any and all help appreciated. Kal
How can i confirm that the data entered within the standard controls and after the click event for the particular sql query the data is been stored in the database tables (depending insert,update,delete).I want to display the different messages on the click event and that after the changes made into the tables.How can i do it ?? I m using ASP.Net with C#,VB and the sql server to store data.I also want to know where are the database tables exactly get stored which are made in the Microsoft SQL Server Management Studio Express.I want to use as the existing item in .Net but could not find.. Thanxs...
I have a problem where a certain stored procedure disappears occasionally and I need to find out which script deletes it. I found this piece of code which gives the events related to the deletion of this stored procedure.
DECLARE @path NVARCHAR(260); SELECT @path = REVERSE(SUBSTRING(REVERSE([path]), CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc' FROM sys.traces WHERE is_default = 1;
[code]...
Is there a way that I can find which stored procedure or event dropped this stored procedure?
My SQL Server 2005 SP4 on Windows 2008 R2 is flooded with the below errors:-
Date 10/25/2011 10:55:46 AM Log SQL Server (Current - 10/25/2011 10:55:00 AM) Source spid Message Event Tracing for Windows failed to send an event. Send failures with the same error code may not be reported in the future. Error ID: 0, Event class ID: 54, Cause: (null).
Is there a way I can trace it how it is coming? When I check input buffer for these ids, it looks like it is tracing everything. All the general application DMLs are coming in these spids.
I have been testing with the WMI Event Watcher Task, so that I can identify a change to a file. The WQL is thus:
SELECT * FROM __InstanceModificationEvent within 30 WHERE targetinstance isa 'CIM_DataFile' AND targetinstance.name = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\AdventureWorks.bak'
This polls every 30 secs and in the SSIS Event (ActionAtEvent in the WMI Task is set to fire the SSIS Event) I have a simple script task that runs a message box).
My understanding is that the event polls every 30 s and if there is a change on the AdventureWorks.bak file then the event is triggered and the script task will run producing the message. However, when I run the package the message is occurring every 30s, meaning the event is continually firing even though there has been NO change to the AdventureWorks.bak file.
Am I correct in my understanding of how this should work and if so why is the event firing when it should not ?