MS SQL Server :: Why ALTER QUEUE WITH STATUS=OFF Execute The Activation SP?

Queue Activation Context

Oct 6, 2006

Is there any way to identify the context by which a stored proc has been activated. i.e.

I want to use the same sp to end conversations on receipt of the end mesage from the target.

However I don't know how to find out which queue activated the SP.

View 1 Replies View Related

CLR Stored Proc Queue Activation Help

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

SQL 2012 :: Queue Activation Is Enabled But No Receive Executed

Sep 14, 2015

Our Sql server is not responding, So we restarted the server and modified one of the sp code. After that we are getting frequently every 2 min giving the below error

The queue 855365233 in database 9 has activation enabled and contains unlocked messages but no RECEIVE has been executed for 453199 seconds

View 0 Replies View Related

Monitoring Queue Status

Nov 16, 2006

Hi,

I'm new to the service broker service. All I want to do is to monitor the queue status. If the queue is disabled, send me an email alert.

Can you let me know what's the best way to accomplish it?

Thanks,

Jia

View 4 Replies View Related

An Error Occurred In The Service Broker Internal Activator While Trying To Scan The User Queue '(null)' For Its Status. Error: 2

Apr 25, 2007

Hello - can't find any info on this error - can anyone shed some light?

There was a deadlock - which preceeded this message by 2 seconds.

04/25/2007 08:20:12,spid108,Unknown,An error occurred in the Service Broker internal activator while trying to scan the user queue '(null)' for its status. Error: 2905<c/> State: 1.
04/25/2007 08:20:12,spid108,Unknown,External dump process return code 0x20000001.<nl/>External dump process returned no errors.
04/25/2007 08:20:12,spid108,Unknown,Stack Signature for the dump is 0x1229B5AA
04/25/2007 08:20:12,spid108,Unknown,78132A36 Module(MSVCR80+00002A36)
04/25/2007 08:20:12,spid108,Unknown,781329AA Module(MSVCR80+000029AA)
04/25/2007 08:20:12,spid108,Unknown,01447720 Module(sqlservr+00447720)
04/25/2007 08:20:12,spid108,Unknown,0144789A Module(sqlservr+0044789A)
04/25/2007 08:20:12,spid108,Unknown,0144859B Module(sqlservr+0044859B)
04/25/2007 08:20:12,spid108,Unknown,01447562 Module(sqlservr+00447562)
04/25/2007 08:20:12,spid108,Unknown,01006DAB Module(sqlservr+00006DAB)
04/25/2007 08:20:12,spid108,Unknown,01006BBC Module(sqlservr+00006BBC)
04/25/2007 08:20:12,spid108,Unknown,01006A96 Module(sqlservr+00006A96)
04/25/2007 08:20:12,spid108,Unknown,0112F65C Module(sqlservr+0012F65C)
04/25/2007 08:20:12,spid108,Unknown,0112F70F Module(sqlservr+0012F70F)
04/25/2007 08:20:12,spid108,Unknown,0112CB04 Module(sqlservr+0012CB04)
04/25/2007 08:20:12,spid108,Unknown,0112D1D1 Module(sqlservr+0012D1D1)
04/25/2007 08:20:12,spid108,Unknown,0112EA61 Module(sqlservr+0012EA61)
04/25/2007 08:20:12,spid108,Unknown,0108A5CB Module(sqlservr+0008A5CB)
04/25/2007 08:20:12,spid108,Unknown,0108A56B Module(sqlservr+0008A56B)
04/25/2007 08:20:12,spid108,Unknown,01060A5A Module(sqlservr+00060A5A)
04/25/2007 08:20:12,spid108,Unknown,01476CB2 Module(sqlservr+00476CB2)
04/25/2007 08:20:12,spid108,Unknown,01476FD3 Module(sqlservr+00476FD3)
04/25/2007 08:20:12,spid108,Unknown,017FA720 Module(sqlservr+007FA720)
04/25/2007 08:20:12,spid108,Unknown,017FAADC Module(sqlservr+007FAADC)
04/25/2007 08:20:12,spid108,Unknown,01796B1B Module(sqlservr+00796B1B)
04/25/2007 08:20:12,spid108,Unknown,01793310 Module(sqlservr+00793310)
04/25/2007 08:20:12,spid108,Unknown,0179438B Module(sqlservr+0079438B)
04/25/2007 08:20:12,spid108,Unknown,* Short Stack Dump
04/25/2007 08:20:12,spid108,Unknown,* -------------------------------------------------------------------------------
04/25/2007 08:20:12,spid108,Unknown,* *******************************************************************************
04/25/2007 08:20:12,spid108,Unknown,* SegSs: 00000023:
04/25/2007 08:20:12,spid108,Unknown,* Esp: 1302E584: 13025387 80C87378 80C872F0 00000000 78140001 0009D9C2
04/25/2007 08:20:12,spid108,Unknown,* EFlags: 00010202: 00610067 00650074 0049003B 0076006E 006C0061 00640069
04/25/2007 08:20:12,spid108,Unknown,* SegCs: 0000001B:
04/25/2007 08:20:12,spid108,Unknown,* Ebp: 1302F67C: 1302F788 01793310 13025373 80C872F0 7093AB12 801E8B38
04/25/2007 08:20:12,spid108,Unknown,* Eip: 0179438B: 828B118B 000000E0 C085D0FF 00B9850F 9D890000 FFFFEF8C
04/25/2007 08:20:12,spid108,Unknown,* Edx: 00000E38:
04/25/2007 08:20:12,spid108,Unknown,* Ecx: 00000000:
04/25/2007 08:20:12,spid108,Unknown,* Ebx: 00000000:
04/25/2007 08:20:12,spid108,Unknown,* Eax: A99EE598: 00000000 00000000 A99EE5B0 00000000 00000000 00000000
04/25/2007 08:20:12,spid108,Unknown,* Esi: 00000000:
04/25/2007 08:20:12,spid108,Unknown,* Edi: 80C872F0: 7093AB12 80C87FF0 00000007 B8A20008 7093AB15 00000000
04/25/2007 08:20:12,spid108,Unknown,*
04/25/2007 08:20:12,spid108,Unknown,* dbghelp 14200000 14312FFF 00113000
04/25/2007 08:20:12,spid108,Unknown,* sqlevn70 76780000 76913FFF 00194000
04/25/2007 08:20:12,spid108,Unknown,* sqlevn70 764A0000 76633FFF 00194000
04/25/2007 08:20:12,spid108,Unknown,* sqlevn70 76300000 76493FFF 00194000
04/25/2007 08:20:12,spid108,Unknown,* sqlevn70 341D0000 343AFFFF 001e0000
04/25/2007 08:20:12,spid108,Unknown,* sqlevn70 33590000 33764FFF 001d5000
04/25/2007 08:20:12,spid108,Unknown,* sqlevn70 333F0000 33583FFF 00194000
04/25/2007 08:20:12,spid108,Unknown,* sqlevn70 14490000 1467AFFF 001eb000
04/25/2007 08:20:12,spid108,Unknown,* sqlevn70 13D10000 13EFBFFF 001ec000
04/25/2007 08:20:12,spid108,Unknown,* OLEDB32R 13770000 13780FFF 00011000
04/25/2007 08:20:12,spid108,Unknown,* MSDART 133D0000 133E9FFF 0001a000
04/25/2007 08:20:12,spid108,Unknown,* oledb32 13350000 133C8FFF 00079000
04/25/2007 08:20:12,spid108,Unknown,* msxml3 11BD0000 11CE1FFF 00112000
04/25/2007 08:20:12,spid108,Unknown,* msxmlsql 11AF0000 11BC5FFF 000d6000
04/25/2007 08:20:12,spid108,Unknown,* System.Xml 10FA0000 11193FFF 001f4000
04/25/2007 08:20:12,spid108,Unknown,* System.Security 10F20000 10F61FFF 00042000
04/25/2007 08:20:12,spid108,Unknown,* System 10C10000 10EF3FFF 002e4000
04/25/2007 08:20:12,spid108,Unknown,* System.Security.ni 10A90000 10B45FFF 000b6000
04/25/2007 08:20:12,spid108,Unknown,* System.Transactions 0FAD0000 0FB12FFF 00043000
04/25/2007 08:20:12,spid108,Unknown,* System.ni 7A440000 7ABFDFFF 007be000
04/25/2007 08:20:12,spid108,Unknown,* System.Data 102D0000 10596FFF 002c7000
04/25/2007 08:20:12,spid108,Unknown,* mscorjit 0F910000 0F962FFF 00053000
04/25/2007 08:20:12,spid108,Unknown,* SqlAccess 0F8A0000 0F8F5FFF 00056000
04/25/2007 08:20:12,spid108,Unknown,* SensApi 0F880000 0F884FFF 00005000
04/25/2007 08:20:12,spid108,Unknown,* cryptnet 0F680000 0F691FFF 00012000
04/25/2007 08:20:12,spid108,Unknown,* SOFTPUB 0F670000 0F674FFF 00005000
04/25/2007 08:20:12,spid108,Unknown,* mscorsec 0F640000 0F652FFF 00013000
04/25/2007 08:20:12,spid108,Unknown,* mscorlib.ni 0EAC0000 0F5A7FFF 00ae8000
04/25/2007 08:20:12,spid108,Unknown,* mscorwks 080E0000 08640FFF 00561000
04/25/2007 08:20:12,spid108,Unknown,* xplog70 07CA0000 07CA2FFF 00003000
04/25/2007 08:20:12,spid108,Unknown,* xplog70 07C80000 07C8BFFF 0000c000
04/25/2007 08:20:12,spid108,Unknown,* xpstar90 07C50000 07C75FFF 00026000
04/25/2007 08:20:12,spid108,Unknown,* odbcint 07C30000 07C46FFF 00017000
04/25/2007 08:20:12,spid108,Unknown,* ATL80 7C630000 7C64AFFF 0001b000
04/25/2007 08:20:12,spid108,Unknown,* BatchParser90 07A40000 07A5EFFF 0001f000
04/25/2007 08:20:12,spid108,Unknown,* ODBC32 07A00000 07A3CFFF 0003d000
04/25/2007 08:20:12,spid108,Unknown,* SQLSCM90 079E0000 079E8FFF 00009000
04/25/2007 08:20:12,spid108,Unknown,* xpstar90 07980000 079C7FFF 00048000
04/25/2007 08:20:12,spid108,Unknown,* xpsqlbot 07960000 07965FFF 00006000
04/25/2007 08:20:12,spid108,Unknown,* msftepxy 07490000 074A4FFF 00015000
04/25/2007 08:20:12,spid108,Unknown,* SQLNCLIR 007A0000 007D2FFF 00033000
04/25/2007 08:20:12,spid108,Unknown,* comdlg32 762B0000 762F9FFF 0004a000
04/25/2007 08:20:12,spid108,Unknown,* COMCTL32 77530000 775C6FFF 00097000
04/25/2007 08:20:12,spid108,Unknown,* sqlncli 337A0000 339C1FFF 00222000
04/25/2007 08:20:12,spid108,Unknown,* CLBCatQ 777B0000 77832FFF 00083000
04/25/2007 08:20:12,spid108,Unknown,* xpsp2res 10000000 102C4FFF 002c5000
04/25/2007 08:20:12,spid108,Unknown,* ntdsapi 766F0000 76704FFF 00015000
04/25/2007 08:20:12,spid108,Unknown,* wshtcpip 070B0000 070B7FFF 00008000
04/25/2007 08:20:12,spid108,Unknown,* hnetcfg 071F0000 07248FFF 00059000
04/25/2007 08:20:12,spid108,Unknown,* dssenh 070C0000 070E3FFF 00024000
04/25/2007 08:20:12,spid108,Unknown,* imagehlp 76C10000 76C38FFF 00029000
04/25/2007 08:20:12,spid108,Unknown,* WINTRUST 76BB0000 76BDAFFF 0002b000
04/25/2007 08:20:12,spid108,Unknown,* dbghelp 06C10000 06D22FFF 00113000
04/25/2007 08:20:12,spid108,Unknown,* msfte 069B0000 06C08FFF 00259000
04/25/2007 08:20:12,spid108,Unknown,* security 06190000 06193FFF 00004000
04/25/2007 08:20:12,spid108,Unknown,* rasadhlp 76F80000 76F84FFF 00005000
04/25/2007 08:20:12,spid108,Unknown,* winrnr 76F70000 76F76FFF 00007000
04/25/2007 08:20:12,spid108,Unknown,* DNSAPI 76ED0000 76EF8FFF 00029000
04/25/2007 08:20:12,spid108,Unknown,* RESUTILS 05D50000 05D62FFF 00013000
04/25/2007 08:20:12,spid108,Unknown,* CLUSAPI 05D30000 05D41FFF 00012000
04/25/2007 08:20:12,spid108,Unknown,* OLEAUT32 77D00000 77D8BFFF 0008c000
04/25/2007 08:20:12,spid108,Unknown,* WSOCK32 71BB0000 71BB8FFF 00009000
04/25/2007 08:20:12,spid108,Unknown,* VERSION 77B90000 77B97FFF 00008000
04/25/2007 08:20:12,spid108,Unknown,* MTXCLU 05D10000 05D28FFF 00019000
04/25/2007 08:20:12,spid108,Unknown,* msvcp60 780C0000 78120FFF 00061000
04/25/2007 08:20:12,spid108,Unknown,* MSDTCPRX 05C90000 05D07FFF 00078000
04/25/2007 08:20:12,spid108,Unknown,* XOLEHLP 05C80000 05C85FFF 00006000
04/25/2007 08:20:12,spid108,Unknown,* COMRES 77010000 770D5FFF 000c6000
04/25/2007 08:20:12,spid108,Unknown,* schannel 76750000 76776FFF 00027000
04/25/2007 08:20:12,spid108,Unknown,* cryptdll 766E0000 766EBFFF 0000c000
04/25/2007 08:20:12,spid108,Unknown,* kerberos 05BC0000 05C17FFF 00058000
04/25/2007 08:20:12,spid108,Unknown,* iphlpapi 76CF0000 76D09FFF 0001a000
04/25/2007 08:20:12,spid108,Unknown,* msv1_0 76C90000 76CB6FFF 00027000
04/25/2007 08:20:12,spid108,Unknown,* MSCOREE 05950000 05994FFF 00045000
04/25/2007 08:20:12,spid108,Unknown,* AUTHZ 76C40000 76C53FFF 00014000
04/25/2007 08:20:12,spid108,Unknown,* rsaenh 04E90000 04EBEFFF 0002f000
04/25/2007 08:20:12,spid108,Unknown,* WLDAP32 76F10000 76F3DFFF 0002e000
04/25/2007 08:20:12,spid108,Unknown,* SAMLIB 5CCF0000 5CCFEFFF 0000f000
04/25/2007 08:20:12,spid108,Unknown,* ole32 77670000 777A3FFF 00134000
04/25/2007 08:20:12,spid108,Unknown,* NTMARTA 77E00000 77E21FFF 00022000
04/25/2007 08:20:12,spid108,Unknown,* SQLOS 344D0000 344D4FFF 00005000
04/25/2007 08:20:12,spid108,Unknown,* sqlevn70 4F610000 4F7A3FFF 00194000
04/25/2007 08:20:12,spid108,Unknown,* instapi 48060000 48069FFF 0000a000
04/25/2007 08:20:12,spid108,Unknown,* psapi 76B70000 76B7AFFF 0000b000
04/25/2007 08:20:12,spid108,Unknown,* comctl32 77420000 77522FFF 00103000
04/25/2007 08:20:12,spid108,Unknown,* SHLWAPI 77DA0000 77DF1FFF 00052000
04/25/2007 08:20:12,spid108,Unknown,* SHELL32 7C8D0000 7D0D2FFF 00803000
04/25/2007 08:20:12,spid108,Unknown,* NETAPI32 71C40000 71C97FFF 00058000
04/25/2007 08:20:12,spid108,Unknown,* opends60 333E0000 333E6FFF 00007000
04/25/2007 08:20:12,spid108,Unknown,* USERENV 76920000 769E3FFF 000c4000
04/25/2007 08:20:12,spid108,Unknown,* WS2HELP 71BF0000 71BF7FFF 00008000
04/25/2007 08:20:12,spid108,Unknown,* WS2_32 71C00000 71C16FFF 00017000
04/25/2007 08:20:12,spid108,Unknown,* MSWSOCK 71B20000 71B60FFF 00041000
04/25/2007 08:20:12,spid108,Unknown,* Secur32 76F50000 76F62FFF 00013000
04/25/2007 08:20:12,spid108,Unknown,* MSASN1 76190000 761A1FFF 00012000
04/25/2007 08:20:12,spid108,Unknown,* CRYPT32 761B0000 76242FFF 00093000
04/25/2007 08:20:12,spid108,Unknown,* GDI32 77C00000 77C47FFF 00048000
04/25/2007 08:20:12,spid108,Unknown,* USER32 77380000 77411FFF 00092000
04/25/2007 08:20:12,spid108,Unknown,* RPCRT4 77C50000 77CEEFFF 0009f000
04/25/2007 08:20:12,spid108,Unknown,* ADVAPI32 77F50000 77FEBFFF 0009c000
04/25/2007 08:20:12,spid108,Unknown,* MSVCP80 7C420000 7C4A6FFF 00087000
04/25/2007 08:20:12,spid108,Unknown,* msvcrt 77BA0000 77BF9FFF 0005a000
04/25/2007 08:20:12,spid108,Unknown,* MSVCR80 78130000 781CAFFF 0009b000
04/25/2007 08:20:12,spid108,Unknown,* kernel32 77E40000 77F41FFF 00102000
04/25/2007 08:20:12,spid108,Unknown,* ntdll 7C800000 7C8BFFFF 000c0000
04/25/2007 08:20:12,spid108,Unknown,* sqlservr 01000000 02BCEFFF 01bcf000
04/25/2007 08:20:12,spid108,Unknown,* MODULE BASE END SIZE
04/25/2007 08:20:12,spid108,Unknown,*
04/25/2007 08:20:12,spid108,Unknown,*
04/25/2007 08:20:12,spid108,Unknown,* Access Violation occurred reading address 00000000
04/25/2007 08:20:12,spid108,Unknown,* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
04/25/2007 08:20:12,spid108,Unknown,* Exception Address = 0179438B Module(sqlservr+0079438B)
04/25/2007 08:20:12,spid108,Unknown,*
04/25/2007 08:20:12,spid108,Unknown,*
04/25/2007 08:20:12,spid108,Unknown,* 04/25/07 08:20:12 spid 108
04/25/2007 08:20:12,spid108,Unknown,* BEGIN STACK DUMP:
04/25/2007 08:20:12,spid108,Unknown,*
04/25/2007 08:20:12,spid108,Unknown,* *******************************************************************************
04/25/2007 08:20:12,spid108,Unknown,SqlDumpExceptionHandler: Process 108 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
04/25/2007 08:20:12,spid108,Unknown,***Stack Dump being sent to D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGSQLDump0007.txt
04/25/2007 08:20:12,spid108,Unknown,Using 'dbghelp.dll' version '4.0.5'
04/25/2007 08:20:12,spid60,Unknown,An error occurred in the Service Broker internal activator while trying to scan the user queue '(null)' for its status. Error: 2905<c/> State: 1.
04/25/2007 08:20:12,spid60,Unknown,External dump process return code 0x20000001.<nl/>External dump process returned no errors.
04/25/2007 08:20:10,spid60,Unknown,Stack Signature for the dump is 0x1229B5AA
04/25/2007 08:20:10,spid60,Unknown,78132A36 Module(MSVCR80+00002A36)
04/25/2007 08:20:10,spid60,Unknown,781329AA Module(MSVCR80+000029AA)
04/25/2007 08:20:10,spid60,Unknown,01447720 Module(sqlservr+00447720)
04/25/2007 08:20:10,spid60,Unknown,0144789A Module(sqlservr+0044789A)
04/25/2007 08:20:10,spid60,Unknown,0144859B Module(sqlservr+0044859B)
04/25/2007 08:20:10,spid60,Unknown,01447562 Module(sqlservr+00447562)
04/25/2007 08:20:10,spid60,Unknown,01006DAB Module(sqlservr+00006DAB)
04/25/2007 08:20:10,spid60,Unknown,01006BBC Module(sqlservr+00006BBC)
04/25/2007 08:20:10,spid60,Unknown,01006A96 Module(sqlservr+00006A96)
04/25/2007 08:20:10,spid60,Unknown,0112F65C Module(sqlservr+0012F65C)
04/25/2007 08:20:10,spid60,Unknown,0112F70F Module(sqlservr+0012F70F)
04/25/2007 08:20:10,spid60,Unknown,0112CB04 Module(sqlservr+0012CB04)
04/25/2007 08:20:10,spid60,Unknown,0112D1D1 Module(sqlservr+0012D1D1)
04/25/2007 08:20:10,spid60,Unknown,0112EA61 Module(sqlservr+0012EA61)
04/25/2007 08:20:10,spid60,Unknown,0108A5CB Module(sqlservr+0008A5CB)
04/25/2007 08:20:10,spid60,Unknown,0108A56B Module(sqlservr+0008A56B)
04/25/2007 08:20:10,spid60,Unknown,01060A5A Module(sqlservr+00060A5A)
04/25/2007 08:20:10,spid60,Unknown,01476CB2 Module(sqlservr+00476CB2)
04/25/2007 08:20:10,spid60,Unknown,01476FD3 Module(sqlservr+00476FD3)
04/25/2007 08:20:10,spid60,Unknown,017FA720 Module(sqlservr+007FA720)
04/25/2007 08:20:10,spid60,Unknown,017FAADC Module(sqlservr+007FAADC)
04/25/2007 08:20:10,spid60,Unknown,01796B1B Module(sqlservr+00796B1B)
04/25/2007 08:20:10,spid60,Unknown,01793310 Module(sqlservr+00793310)
04/25/2007 08:20:10,spid60,Unknown,0179438B Module(sqlservr+0079438B)
04/25/2007 08:20:10,spid60,Unknown,* Short Stack Dump
04/25/2007 08:20:10,spid60,Unknown,* -------------------------------------------------------------------------------
04/25/2007 08:20:10,spid60,Unknown,* *******************************************************************************
04/25/2007 08:20:10,spid60,Unknown,* SegSs: 00000023:
04/25/2007 08:20:10,spid60,Unknown,* Esp: 1189E584: 11895387 80C87378 80C872F0 00000000 03E90001 00087C2D
04/25/2007 08:20:10,spid60,Unknown,* EFlags: 00010202: 00610067 00650074 0049003B 0076006E 006C0061 00640069
04/25/2007 08:20:10,spid60,Unknown,* SegCs: 0000001B:
04/25/2007 08:20:10,spid60,Unknown,* Ebp: 1189F67C: 1189F788 01793310 11895373 80C872F0 7093AB12 801E8B38
04/25/2007 08:20:10,spid60,Unknown,* Eip: 0179438B: 828B118B 000000E0 C085D0FF 00B9850F 9D890000 FFFFEF8C
04/25/2007 08:20:10,spid60,Unknown,* Edx: 00000E38:
04/25/2007 08:20:10,spid60,Unknown,* Ecx: 00000000:
04/25/2007 08:20:10,spid60,Unknown,* Ebx: 00000000:
04/25/2007 08:20:10,spid60,Unknown,* Eax: 5ADE8598: 00000000 00000000 5ADE85B0 00000000 00000000 00000000
04/25/2007 08:20:10,spid60,Unknown,* Esi: 00000000:
04/25/2007 08:20:10,spid60,Unknown,* Edi: 80C872F0: 7093AB12 80C87FF0 00000007 B8A20008 7093AB15 00000000
04/25/2007 08:20:10,spid60,Unknown,*
04/25/2007 08:20:10,spid60,Unknown,* dbghelp 14200000 14312FFF 00113000
04/25/2007 08:20:10,spid60,Unknown,* sqlevn70 76780000 76913FFF 00194000
04/25/2007 08:20:10,spid60,Unknown,* sqlevn70 764A0000 76633FFF 00194000
04/25/2007 08:20:10,spid60,Unknown,* sqlevn70 76300000 76493FFF 00194000
04/25/2007 08:20:10,spid60,Unknown,* sqlevn70 341D0000 343AFFFF 001e0000
04/25/2007 08:20:10,spid60,Unknown,* sqlevn70 33590000 33764FFF 001d5000
04/25/2007 08:20:10,spid60,Unknown,* sqlevn70 333F0000 33583FFF 00194000
04/25/2007 08:20:10,spid60,Unknown,* sqlevn70 14490000 1467AFFF 001eb000
04/25/2007 08:20:10,spid60,Unknown,* sqlevn70 13D10000 13EFBFFF 001ec000
04/25/2007 08:20:10,spid60,Unknown,* OLEDB32R 13770000 13780FFF 00011000
04/25/2007 08:20:10,spid60,Unknown,* MSDART 133D0000 133E9FFF 0001a000
04/25/2007 08:20:10,spid60,Unknown,* oledb32 13350000 133C8FFF 00079000
04/25/2007 08:20:10,spid60,Unknown,* msxml3 11BD0000 11CE1FFF 00112000
04/25/2007 08:20:10,spid60,Unknown,* msxmlsql 11AF0000 11BC5FFF 000d6000
04/25/2007 08:20:10,spid60,Unknown,* System.Xml 10FA0000 11193FFF 001f4000
04/25/2007 08:20:10,spid60,Unknown,* System.Security 10F20000 10F61FFF 00042000
04/25/2007 08:20:10,spid60,Unknown,* System 10C10000 10EF3FFF 002e4000
04/25/2007 08:20:10,spid60,Unknown,* System.Security.ni 10A90000 10B45FFF 000b6000
04/25/2007 08:20:10,spid60,Unknown,* System.Transactions 0FAD0000 0FB12FFF 00043000
04/25/2007 08:20:10,spid60,Unknown,* System.ni 7A440000 7ABFDFFF 007be000
04/25/2007 08:20:10,spid60,Unknown,* System.Data 102D0000 10596FFF 002c7000
04/25/2007 08:20:10,spid60,Unknown,* mscorjit 0F910000 0F962FFF 00053000
04/25/2007 08:20:10,spid60,Unknown,* SqlAccess 0F8A0000 0F8F5FFF 00056000
04/25/2007 08:20:10,spid60,Unknown,* SensApi 0F880000 0F884FFF 00005000
04/25/2007 08:20:10,spid60,Unknown,* cryptnet 0F680000 0F691FFF 00012000
04/25/2007 08:20:10,spid60,Unknown,* SOFTPUB 0F670000 0F674FFF 00005000
04/25/2007 08:20:10,spid60,Unknown,* mscorsec 0F640000 0F652FFF 00013000
04/25/2007 08:20:10,spid60,Unknown,* mscorlib.ni 0EAC0000 0F5A7FFF 00ae8000
04/25/2007 08:20:10,spid60,Unknown,* mscorwks 080E0000 08640FFF 00561000
04/25/2007 08:20:10,spid60,Unknown,* xplog70 07CA0000 07CA2FFF 00003000
04/25/2007 08:20:10,spid60,Unknown,* xplog70 07C80000 07C8BFFF 0000c000
04/25/2007 08:20:10,spid60,Unknown,* xpstar90 07C50000 07C75FFF 00026000
04/25/2007 08:20:10,spid60,Unknown,* odbcint 07C30000 07C46FFF 00017000
04/25/2007 08:20:10,spid60,Unknown,* ATL80 7C630000 7C64AFFF 0001b000
04/25/2007 08:20:10,spid60,Unknown,* BatchParser90 07A40000 07A5EFFF 0001f000
04/25/2007 08:20:10,spid60,Unknown,* ODBC32 07A00000 07A3CFFF 0003d000
04/25/2007 08:20:10,spid60,Unknown,* SQLSCM90 079E0000 079E8FFF 00009000
04/25/2007 08:20:10,spid60,Unknown,* xpstar90 07980000 079C7FFF 00048000
04/25/2007 08:20:10,spid60,Unknown,* xpsqlbot 07960000 07965FFF 00006000
04/25/2007 08:20:10,spid60,Unknown,* msftepxy 07490000 074A4FFF 00015000
04/25/2007 08:20:10,spid60,Unknown,* SQLNCLIR 007A0000 007D2FFF 00033000
04/25/2007 08:20:10,spid60,Unknown,* comdlg32 762B0000 762F9FFF 0004a000
04/25/2007 08:20:10,spid60,Unknown,* COMCTL32 77530000 775C6FFF 00097000
04/25/2007 08:20:10,spid60,Unknown,* sqlncli 337A0000 339C1FFF 00222000
04/25/2007 08:20:10,spid60,Unknown,* CLBCatQ 777B0000 77832FFF 00083000
04/25/2007 08:20:10,spid60,Unknown,* xpsp2res 10000000 102C4FFF 002c5000
04/25/2007 08:20:10,spid60,Unknown,* ntdsapi 766F0000 76704FFF 00015000
04/25/2007 08:20:10,spid60,Unknown,* wshtcpip 070B0000 070B7FFF 00008000
04/25/2007 08:20:10,spid60,Unknown,* hnetcfg 071F0000 07248FFF 00059000
04/25/2007 08:20:10,spid60,Unknown,* dssenh 070C0000 070E3FFF 00024000
04/25/2007 08:20:10,spid60,Unknown,* imagehlp 76C10000 76C38FFF 00029000
04/25/2007 08:20:10,spid60,Unknown,* WINTRUST 76BB0000 76BDAFFF 0002b000
04/25/2007 08:20:10,spid60,Unknown,* dbghelp 06C10000 06D22FFF 00113000
04/25/2007 08:20:10,spid60,Unknown,* msfte 069B0000 06C08FFF 00259000
04/25/2007 08:20:10,spid60,Unknown,* security 06190000 06193FFF 00004000
04/25/2007 08:20:10,spid60,Unknown,* rasadhlp 76F80000 76F84FFF 00005000
04/25/2007 08:20:10,spid60,Unknown,* winrnr 76F70000 76F76FFF 00007000
04/25/2007 08:20:10,spid60,Unknown,* DNSAPI 76ED0000 76EF8FFF 00029000
04/25/2007 08:20:10,spid60,Unknown,* RESUTILS 05D50000 05D62FFF 00013000
04/25/2007 08:20:10,spid60,Unknown,* CLUSAPI 05D30000 05D41FFF 00012000
04/25/2007 08:20:10,spid60,Unknown,* OLEAUT32 77D00000 77D8BFFF 0008c000
04/25/2007 08:20:10,spid60,Unknown,* WSOCK32 71BB0000 71BB8FFF 00009000
04/25/2007 08:20:10,spid60,Unknown,* VERSION 77B90000 77B97FFF 00008000
04/25/2007 08:20:10,spid60,Unknown,* MTXCLU 05D10000 05D28FFF 00019000
04/25/2007 08:20:10,spid60,Unknown,* msvcp60 780C0000 78120FFF 00061000
04/25/2007 08:20:10,spid60,Unknown,* MSDTCPRX 05C90000 05D07FFF 00078000
04/25/2007 08:20:10,spid60,Unknown,* XOLEHLP 05C80000 05C85FFF 00006000
04/25/2007 08:20:10,spid60,Unknown,* COMRES 77010000 770D5FFF 000c6000
04/25/2007 08:20:10,spid60,Unknown,* schannel 76750000 76776FFF 00027000
04/25/2007 08:20:10,spid60,Unknown,* cryptdll 766E0000 766EBFFF 0000c000
04/25/2007 08:20:10,spid60,Unknown,* kerberos 05BC0000 05C17FFF 00058000
04/25/2007 08:20:10,spid60,Unknown,* iphlpapi 76CF0000 76D09FFF 0001a000
04/25/2007 08:20:10,spid60,Unknown,* msv1_0 76C90000 76CB6FFF 00027000
04/25/2007 08:20:10,spid60,Unknown,* MSCOREE 05950000 05994FFF 00045000
04/25/2007 08:20:10,spid60,Unknown,* AUTHZ 76C40000 76C53FFF 00014000
04/25/2007 08:20:10,spid60,Unknown,* rsaenh 04E90000 04EBEFFF 0002f000
04/25/2007 08:20:10,spid60,Unknown,* WLDAP32 76F10000 76F3DFFF 0002e000
04/25/2007 08:20:10,spid60,Unknown,* SAMLIB 5CCF0000 5CCFEFFF 0000f000
04/25/2007 08:20:10,spid60,Unknown,* ole32 77670000 777A3FFF 00134000
04/25/2007 08:20:10,spid60,Unknown,* NTMARTA 77E00000 77E21FFF 00022000
04/25/2007 08:20:10,spid60,Unknown,* SQLOS 344D0000 344D4FFF 00005000
04/25/2007 08:20:10,spid60,Unknown,* sqlevn70 4F610000 4F7A3FFF 00194000
04/25/2007 08:20:10,spid60,Unknown,* instapi 48060000 48069FFF 0000a000
04/25/2007 08:20:10,spid60,Unknown,* psapi 76B70000 76B7AFFF 0000b000
04/25/2007 08:20:10,spid60,Unknown,* comctl32 77420000 77522FFF 00103000
04/25/2007 08:20:10,spid60,Unknown,* SHLWAPI 77DA0000 77DF1FFF 00052000
04/25/2007 08:20:10,spid60,Unknown,* SHELL32 7C8D0000 7D0D2FFF 00803000
04/25/2007 08:20:10,spid60,Unknown,* NETAPI32 71C40000 71C97FFF 00058000
04/25/2007 08:20:10,spid60,Unknown,* opends60 333E0000 333E6FFF 00007000
04/25/2007 08:20:10,spid60,Unknown,* USERENV 76920000 769E3FFF 000c4000
04/25/2007 08:20:10,spid60,Unknown,* WS2HELP 71BF0000 71BF7FFF 00008000
04/25/2007 08:20:10,spid60,Unknown,* WS2_32 71C00000 71C16FFF 00017000
04/25/2007 08:20:10,spid60,Unknown,* MSWSOCK 71B20000 71B60FFF 00041000
04/25/2007 08:20:10,spid60,Unknown,* Secur32 76F50000 76F62FFF 00013000
04/25/2007 08:20:10,spid60,Unknown,* MSASN1 76190000 761A1FFF 00012000
04/25/2007 08:20:10,spid60,Unknown,* CRYPT32 761B0000 76242FFF 00093000
04/25/2007 08:20:10,spid60,Unknown,* GDI32 77C00000 77C47FFF 00048000
04/25/2007 08:20:10,spid60,Unknown,* USER32 77380000 77411FFF 00092000
04/25/2007 08:20:10,spid60,Unknown,* RPCRT4 77C50000 77CEEFFF 0009f000
04/25/2007 08:20:10,spid60,Unknown,* ADVAPI32 77F50000 77FEBFFF 0009c000
04/25/2007 08:20:10,spid60,Unknown,* MSVCP80 7C420000 7C4A6FFF 00087000
04/25/2007 08:20:10,spid60,Unknown,* msvcrt 77BA0000 77BF9FFF 0005a000
04/25/2007 08:20:10,spid60,Unknown,* MSVCR80 78130000 781CAFFF 0009b000
04/25/2007 08:20:10,spid60,Unknown,* kernel32 77E40000 77F41FFF 00102000
04/25/2007 08:20:10,spid60,Unknown,* ntdll 7C800000 7C8BFFFF 000c0000
04/25/2007 08:20:10,spid60,Unknown,* sqlservr 01000000 02BCEFFF 01bcf000
04/25/2007 08:20:10,spid60,Unknown,* MODULE BASE END SIZE
04/25/2007 08:20:10,spid60,Unknown,*
04/25/2007 08:20:10,spid60,Unknown,*
04/25/2007 08:20:10,spid60,Unknown,* Access Violation occurred reading address 00000000
04/25/2007 08:20:10,spid60,Unknown,* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
04/25/2007 08:20:10,spid60,Unknown,* Exception Address = 0179438B Module(sqlservr+0079438B)
04/25/2007 08:20:10,spid60,Unknown,*
04/25/2007 08:20:10,spid60,Unknown,*
04/25/2007 08:20:10,spid60,Unknown,* 04/25/07 08:20:10 spid 60
04/25/2007 08:20:10,spid60,Unknown,* BEGIN STACK DUMP:
04/25/2007 08:20:10,spid60,Unknown,*
04/25/2007 08:20:10,spid60,Unknown,* *******************************************************************************
04/25/2007 08:20:10,spid60,Unknown,SqlDumpExceptionHandler: Process 60 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
04/25/2007 08:20:10,spid60,Unknown,***Stack Dump being sent to D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGSQLDump0006.txt
04/25/2007 08:20:10,spid60,Unknown,Using 'dbghelp.dll' version '4.0.5'

View 5 Replies View Related

Execute Remote Stored Procedure From The Receiver's Queue

Sep 27, 2006

How can my receiver's queue procedure execute a procedure on another server?
Everytime it gets to that remote procedure statement, I get DISCONNECTED_INBOUND status.
The procedure I want to call is on SQL 2000.

View 9 Replies View Related

SQL Security :: Give Access To Create Alter And Execute SPs In Server 2008R2

May 12, 2015

Is there a way i can grant a windows authenticated user say abcadam privilege:

-> to create, execute and alter the stored procedures that will be created by him only:

Point is we do not want the user to execute alter or modify other stored procedures out there for that DB

View 13 Replies View Related

Clarifications On Queue Service And Queue Readers

Jan 11, 2006

Hello,
This is info that I am still not certain about and I just need to make sure, my gut feeling is correct:

A.
When a procedure is triggered upon reception of a message in a queue, what happens when the procedure fails and rolls back?
1. Message is left on the Queue.
2. is the worker procedure triggered again for the same message by the queue?
3. I am hoping the Queue keeps on triggering workers until it is empty.

My scenario is that my queue reader procedure only reads one message at a time, thus I do not loop to receive many messages.

B.
For my scenario messages are independent and ordering does not matter.
Thus I want to ensure my Queue reader procedures execute simultaneously. Is reading the Top message in one reader somehow blocking the queue for any other reader procedures? I.e. if I have BEGIN TRANSACTION when reading messages of the Queue, is that effectively going prevent many reader procedures working simultaneously. Again, I want to ensure that Service broker is effectively spawning procedures that work simultaneously.

Thank you very much for the time,

Lubomir

View 5 Replies View Related

Alter Table Alter Column In MSACCESS. How Can I Do It For A Decimal Field?

Jul 23, 2005

Hi people,I?m trying to alter a integer field to a decimal(12,4) field in MSACCESS 2K.Example:table : item_nota_fiscal_forn_setor_publicofield : qtd_mercadoria integer NOT NULLALTER TABLE item_nota_fiscal_forn_setor_publicoALTER COLUMN qtd_mercadoria decimal(12,4) NOT NULLBut, It doesn't work. A sintax error rises.I need to change that field in a Visual Basic aplication, dinamically.How can I do it? How can I create a decimal(12,4) field via script in MSACCESS?Thanks,Euler Almeida--Message posted via http://www.sqlmonster.com

View 1 Replies View Related

Alter Table Alter Column

Jul 20, 2005

I would like to add an Identity to an existing column in a table using astored procedure then add records to the table and then remove the identityafter the records have been added or something similar.here is a rough idea of what the stored procedure should do. (I do not knowthe syntax to accomplish this can anyone help or explain this?Thanks much,CBLCREATE proc dbo.pts_ImportJobsas/* add identity to [BarCode Part#] */alter table dbo.ItemTestalter column [BarCode Part#] [int] IDENTITY(1, 1) NOT NULL/* add records from text file here *//* remove identity from BarCode Part#] */alter table dbo.ItemTestalter column [BarCode Part#] [int] NOT NULLreturnGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGOhere is the original tableCREATE TABLE [ItemTest] ([BarCode Part#] [int] NOT NULL ,[File Number] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_File Number] DEFAULT (''),[Item Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Item Number] DEFAULT (''),[Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Description] DEFAULT (''),[Room Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Room Number] DEFAULT (''),[Quantity] [int] NULL CONSTRAINT [DF_ItemTest_Quantity] DEFAULT (0),[Label Printed Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Label Printed Cnt]DEFAULT (0),[Rework] [bit] NULL CONSTRAINT [DF_ItemTest_Rework] DEFAULT (0),[Rework Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Rework Cnt] DEFAULT (0),[Assembly Scan Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Assembly Scan Cnt]DEFAULT (0),[BarCode Crate#] [int] NULL CONSTRAINT [DF_ItemTest_BarCode Crate#] DEFAULT(0),[Assembly Group#] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Assembly Group#] DEFAULT (''),[Assembly Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Assembly Name] DEFAULT (''),[Import Date] [datetime] NULL CONSTRAINT [DF_ItemTest_Import Date] DEFAULT(getdate()),CONSTRAINT [IX_ItemTest] UNIQUE NONCLUSTERED([BarCode Part#]) ON [PRIMARY]) ON [PRIMARY]GO

View 2 Replies View Related

Alter Table Alter Column...

Oct 8, 2007

I am using sql server ce.I am changing my tables sometimes.how to use 'alter table alter column...'.for example:I have table 'customers', I delete column 'name' and add column 'age'.Now I drop Table 'customers' and create again.but I read something about 'alter table alter column...'.I use thi command but not work.I thing syntax not true,that I use..plaese help me?

my code:
Alter table customers alter column age

View 7 Replies View Related

Activation

May 1, 2007

I was testing around with a sample service broker app using activation, and came across an interesting question. The little app sends a series of four messages to a queue, either on the same conversation or on seperate ones. Each message invokes one stored procedure in my activation procedure. All the procedure does is enter a record into a test table and then wait for an allotted amount of time. In my example, the first message called a proc that waited 20 sec, the 2nd one that waited 10 seconds, the third 5 seconds, and the 4th 1 second. I am using internal activation on the queue. It seemed that in both scenarios (sending on 4 separate conversations and on one conversation) the procedures executed "almost" sequentially. "Almost" meaning that the first procedure was done before the last one started executing. It makes sense to me that this would happen where I sent them on the same conversation, but not really when I sent them on 4 seperate ones. Is it because when I call a procedure from my activation procedure it locks the queue so that another message cannot be processed (I'm processing a message at a time)? How could I make it so that the 4th procedure (the one that only waits 1 second) returns before the 1st procedure (the one that waits 20 seconds)?

View 5 Replies View Related

Xp_cmdshell And Activation

Oct 11, 2006

We are trying to use xp_cmdshell commands in service broker. If I kick off the process without activation, the process succeeds. If I enable activation, the tasks with xp_cmdshell fail.

The xp_cmdshell task is either a echo command so that users know that we are processing a database, or a file copy.

I used the TechNet article by Roger Wolter to create the queues and stored procedures.

We will be working across domains that have one-way trusts.

Wayne

View 2 Replies View Related

Activation Not Working

Sep 11, 2006

Hi There

Everything works 100% until i activate my sp.

I specify to execute as 'dbo' in the alter queue statement. I also define the activated sp to execute as 'dbo'.

But i keep getting permission errors from my activated sp. i have tried to excute as 'sa' , i have even tried to write a job that excutes to stored procedure but it also get weird errors. Bottom line if i exceute the sp in management studio logged in as sa it works , but thorugh activation or a job nothing works, as 'dbo' or 'sa'.

???

Thanx

View 11 Replies View Related

Changing Activation Key

Mar 7, 2007

I need to change the Activation Key for a copy of SQL Server 2000 that I already have installed and up and running. I have the new key but can't figure out how to change from the old, no longer valid, key.

How can I change keys w/o dumping the DB, uninstalling, and reinstalling all of my instances?

View 4 Replies View Related

How To Find How Activation Has Failed

May 1, 2006

I have seen the posting on determining how activation has failed and looking through the system logs is very helpful in determining why activation is not occuring, however, short of looking through the SQL Server logs is there another way to get the same information? Access to the SQL Server logs is fairly restricted. Does anyone know another way that this can be done? I have used the execute as technique described in the article on "Troubleshooting Activation Stored Procedures" and found it helpful in some cases.

Gary

View 5 Replies View Related

How Can Internal Activation Use More Resources?

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

Cluster Failovers And Activation

Nov 28, 2006

So is there something I'm supposed to configure to allow activation to fire after a cluster failover. We have had three where I have noticed that activation does not automatically fire back up. i have to alter the queue to get it going. None of the values on sys.service_queues imply that is disabled, but messages just keep stacking up into the queue and are not being dealt with. Once I alter the queue to turn activation on; everything takes off and starts working normaly.

Obviously, this is less than desireable for an environment that requires High availability.

Regards

View 1 Replies View Related

Activation Procedure Design

Apr 24, 2007

I've been experimenting with Service Broker and was surprised at one aspect of the design: the interface to Activation stored procedures.

I would have expected the queue to be a parameted passed to the procedure rather than having to hard code the queue query into the SP.

In a system with lots of queues it seems plausible that the same activation procedure might want to be used with several queues.

Any comments?

David.

View 1 Replies View Related

Can Activation SP Wake Up A Thread?

Aug 15, 2007

Newbie question, but is there a way for me to set up a thread in my c# code so as it sleeps until it gets woken up by an event fired by an activation SP?

As in queue sits idle, thread sleeps.
Queue receives a message, activation SP gets fired, activation SP raises an event which gets caught by event handler in code, which wakes up thread to do processing?

View 1 Replies View Related

Device Activation Error

Apr 5, 2007

Hi All,

Currently I have a PC that has MSDE installed on it and is attached to database (MyData.MDF and a log file MYLog.LDF) located on its hard drive at c:data. When I detach from the database, place a copy of the two files noted above on my network drive @ u:data and try to attach I get the following error��s:

SQL[1] exec error = -1: Changed database context to 'master'.
��Device activation error. The physical file name u:dataMyData.MDF may be incorrect.�?

I have done some testing a have found that I can attach to a copy of my database if I move it anywhere on the c: drive, and or even to a 1Gb USB key attached to the system(e:). So far it seems to only be an issue if I move it to a mapped network drive. If anyone could please provide me with any info it would be greatly appreciated.

Thanks.

James D.

View 4 Replies View Related

Increase Activation Time

Oct 23, 2006

Is there any way to increase the activation time, i.e. the time between service broker creating a new instance of the activation SP.

View 3 Replies View Related

Service Broker Activation

Jan 12, 2006

Hi

I'm having problems with activation. I have a CLR stored procedure that runs fine when run directly (and consumes messages from the queue). When I try to enable activation nothing happens. I've tried different execute as options and looked in the sql server logs and I don't see anything indicating why.

I've checked sys.service_queues and all options look correct including activation options. The sys.dm_broker_activated_tasks view is empty.

Without any errors I'm having difficulty tracking down the problem. I've seen references to service broker shutting down the procedure if it doesn't consume any messages but I haven't seen where this is indicated.

Any help appreciated.

Duncan Godwin

View 4 Replies View Related

Activation Proc Failed

Aug 17, 2006

Good day,

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)?

thank you in advance.

View 6 Replies View Related

Internal Activation Best Practices?

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

External Activation And Receive Timeout

Oct 31, 2006

I have a set of service broker services setup that rely on external activation to process messages. I'm using the GotDotNet ExternalActivator, and it launches console applications that do the actual retrieval from the queues. The console applications are written to run continuously to avoid the cost of starting up .NET based console apps over and over again.

I am observing very odd timing behavior. With the receive queues empty and the external activator configured to run a minimum and maximum of 5 instances, I observe in SQL Profiler that most of the receive operations finish in about the same amount of time as my WAITFOR command in my receive stored procedure. However, there is usually one receive command that consistently takes upwards of 30 seconds and often causes sql timeout exceptions to be thrown. I know that I could code around this, but I wasn't really expecting this behavior.

Does anyone have any thoughts on why it might be occurring? I would have expected to routinely see my receive operations taking 15 seconds, give or take, especially when the queue is empty. Also, I have observed this behaviour on both SQL 2k5 Express and Dev Editions, so I don't think it's a version thing.

The stored procedure I am using to do the receive is:

Create PROCEDURE [dbo].[P_RTD_MessageBase_Receive]

@receiving_queue_name varchar(255),

@receiving_time_out int

AS

Declare @receiveQuery nvarchar(300)

Set @receiveQuery = 'WAITFOR(RECEIVE * FROM [dbo].['+ @receiving_queue_name +']),

TIMEOUT ' + cast (@receiving_time_out as varchar)

Execute sp_executeSql @receiveQuery

View 6 Replies View Related

Any simple External Activation Examples Available?

Feb 13, 2007

BOL only seems to say that you can do it w/o really showing how, and the ExternalActivator sample at gotdotnet.com contains so much functionality I'm not sure what's required just for the external activation. Are there any docs or samples out there that focus on how to do it w/o obscuring the matter with a bunch of other functionality? (I prefer docs to project samples, b/c the samples tend to have hacks like hardcoded paths and connection strings so that they rarely work correctly right out of the box.)

TIA

View 1 Replies View Related

Activation/Security/Dynamic SQL Question

May 12, 2006

I will have a variety of different types of work that will come into my Service Broker queue and I'll likely have a stored procedure or two for each of the different types of work (ie. move order header, move items, move payment, etc.) What is required to be done in each of these steps may vary by the subsidiary and type of order coming in. My plan is to use exclusively stored procedures but to execute them dynamically using sp_executesql. I think I should use sp_executesql because that way I can have a config file (in xml) that I can store what stored procedures need to be called for which unit of work/order type/subsidiary. If I do this I should be able to easily configure each type of work to be done in a config file and let Service Broker handle the execution dynamically. As long as I keep the parameters the same for each of the stored procedures (I'm thinking maybe 4 or 5 parameters) and passing them to each of the stored procedures, this approach will allow me to dynamically configure Service Broker to do what it is supposed to do. I can pull what needs to be done out of the message that comes in with an XQuery expression on the config file. I know that I will have to configure my user (activation user) to be able to run sp_executesql and the security may be complex (especially since I'm using certificates). I can not use trusted databases. Are there any other considerations I should think about?

Gary

View 1 Replies View Related

Boilerplate Activation SQL For High-throughput

Apr 9, 2007

OK, so assume I am recycling dialogs in my client code, and assume I am doing something similar to get a dialog handle in my TSQL. What should the activated stored procedure that is processing my queue look like if I am expecting thousands of messages per second? Assume also that there is a small bit of logic need to process each individual message? I am building for a high-throughput scenario and would like to get as much as possible out of each second-tier service broker server as possible before the aggregated data is moved up the chain to a master. The first tier is Express on a web server and exists primarily only as a forwarding mechanism.

View 1 Replies View Related

Problem With Event-based Activation

Aug 29, 2006

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.

Any ideas on what I'm doing wrong?

Thanks,

Michael

View 1 Replies View Related

Cross Database Activation Security

May 25, 2006

I have two databases Basket_ODS and Intelligence_ODS.

I created a user in the Basket_ODS and Intelligence_ODS databases as follows:

USE Basket_ods

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '*******'

CREATE USER BasketServiceUser WITHOUT LOGIN

ALTER AUTHORIZATION ON SERVICE::[Order Send] TO BasketServiceUser

GRANT CONTROL ON SERVICE::[Order Send]

TO BasketServiceUser

CREATE CERTIFICATE BasketServiceCertPriv

AUTHORIZATION BasketServiceUser

WITH SUBJECT = 'ForBasketService'

BACKUP CERTIFICATE BasketServiceCertPriv

TO FILE = 'BasketServiceCertPub'

In the other database...

I created the following:

USE Intelligence_ODS

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '************

USE Intelligence_ODS

GO

CREATE USER BasketServiceUser WITHOUT LOGIN

CREATE CERTIFICATE BasketServiceCertPub

AUTHORIZATION BasketServiceUser

FROM FILE = 'BasketServiceCertPub'

My Queue is in BASKET_ODS and is set up as:

ALTER QUEUE ODS.[Order Process Queue] WITH

ACTIVATION (

STATUS = ON,

PROCEDURE_NAME = ODS.ProcessOrderQueue,

MAX_QUEUE_READERS = 4,

EXECUTE AS 'BasketServiceUser'

)

I have performed the following grants in Basket_ODS

grant execute on ODS.ProcessOrderQueue to BasketServiceUser

ProcessOrderQueue calls [ODS].[MoveOrderTotals_Core] in the Intelligence_ODS database.

grant execute on [ODS].[MoveOrderTotals_Core] to BasketServiceUser

ProcessOrderQueue proc is set as follows:

ALTER procedure [ODS].[ProcessOrderQueue]

WITH EXECUTE AS 'BasketServiceUser'

[ODS].[MoveOrderTotals_Core] is set up as follows:

when I run ProcessOrderQueue I get an error message:

ALTER procedure [ODS].[MoveOrderTotals_Core](@Orderid uniqueidentifier)

with execute as 'BasketServiceUser'

I just don't understand when I run ProcessOrderQueue I get the following error message (when database trust is turned off)

The server principal "sa" is not able to access the database "Intelligence_ODS" under the current security context.

Can you help me figure out what I'm doing wrong. I've spent so much time on this security stuff. Is there another way to do this that is more straight forward without using database trust?

View 3 Replies View Related