I have been going over the samples from PDC 2005 and trying to run the External Activator program in section 2 'CLR Services'. After sending a message to the Inventory Queue, I see that 'dm_broker_queue_monitors' table has a row in the table, with the state column shown as 'NOTIFIED', but the ExternalActivator program never gets notified of the event and therefore never launches the 'InventoryServer' program.
Querying the [ExternalActivatorQueue] shows no rows.
Querying the [Inventory Queue] shows messages are waiting to be received.
Executing the 'activator' command in the ExternalActviator program shows
Notification service 'ExternalActivator' on SQL Server 'DBSERVER' and Database 'In
ventory' is connected to the database and working.
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:
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.)
I am attempting to use Service Broker to distribute message processing across multiple machines. I have mulitple queues setup to all notify a single service with the QUEUE_ACTIVATION event. My external application then sits in a "WAITFOR RECEIVE" on that event queue and when it receives a message for a particular queue, will begin processing the messages in that queue if that particular server is configured to process messages of that type.
My problem is that once one server starts receiving messages, the others don't always start processing and I end up with one server attempting to process all the messages by itself.
What is the best way to notify multiple servers that there are messages in a particular queue which need processing? I really like the WAITFOR clause since it eliminates the need to poll the server intermittently, however I also don't want to have each server sit in a WAITFOR RECEVE on every queue to avoid polling, I could easily end up with around a thousand connections sitting in that state. I also don't know how many servers there will be processing messages, so I'd like to avoid having to setup an event queue for each server.
(Remus, if you read this, I was at the SQL Performance Lab in building 35 about a month ago and we spoke about using Service Broker in our application.)
I will have many client databases that will be updated. When they are updated I need to transfer some of that data to a central Server database somewhere on the Internet. Note, schemas do not necessarily match.
Transfering the data (Web Services, Remoting) is not a problem.
What I am looking for is a cool, correct, advisable way, for the client database to notify me of an Insert, Update or Delete. I can then initiate the connection to the Server and 'push' the data (maybe pull some back from the Server too).
Obviously Triggers may be a place to start... But I need to know 'external to the database' that an update has happened or can I send the data from within the SQL Server assembly?
Anyone have any ideas or a technique, maybe something new in SQL Server 2005 (all databases will be 2005), for acheiving this? Just so I don't go down the 'wrong' path...
There are some BLOB's involved (1-2 page PDF's), if that makes a difference.
I envisage that the process transfering the data will be a Windows Service running on each client. The connection may not always be available, so some kind of 'to do' list of outstanding data to be transfered is required.
I'm just starting on this, so any pointers would be great, I'm sure it's all been done before ;)
When running the following:C:Program FilesMicrosoft SQL Server80ToolsBinn>rsactivate -c"C:Program FilesMicrosoft SQLServerMSSQLReportingServicesReportServerRSRepo rtServer.config" -tI get the following error:Failure starting the web service:System.Exception: The local NT Service is disabled 0x80090005 --->System.Exception: 0x80090005--- End of inner exception stack trace ---atMicrosoft.ReportingServices.RSActivate.RSActivate. RpcActivateService(Int32clientType)at Microsoft.ReportingServices.RSActivate.RSActivate. InstanceMain()at Microsoft.ReportingServices.BaseCmdLine.CommandLin eMain(String[]args, BaseCmdLine instance)Any insight into this. What am I doing wrong.
We are using Notification services of SQL Server 2005 in our application. The scenario is that mails will be sent to customers based on different criteria like New Invoices, Overdue Invoices, Invoices due in certain number of days, Invoices for which Amount Exceeds certain value etc. We are using two stored procedures and two tables to achieve this. One stored proc inserts/updates data related to different notifications that are selected by customers. Another stored proc inserts or updates data related to customers who created the notification.
We are now planning to upgrade from SQL 2005 to SQL server 2012. Since notification services are no longer supported in SQL 2012, we are looking for other alternatives. Which among the below would be the best alternative to replace notification services for our scenario. Also, anyny documentation or useful links which would support us in replacing notification services.
i've installed Sql Server 2005 Standard edition (32bit)with Notification services but after installation this options of Notification service is not appear on sql server management studio 2005 . how can correct this?please give me a hand.
i've installed Sql Server 2005 Standard edition (32bit)with Notification services but after installation this options of Notification service is not appear on sql server management studio 2005 . how can correct this?please give me a hand.
Hi,I am a newbee to sql server. I know there is a tool calledNotification Services. I want to set up notification for specificentries in sql server table.Thanks a lot for your guidance.AJ
We are upgrading a Windows 2003 system with SQL Server 2000 and we've been seeing the notification services .net dll fail to install. The error that is displayed has a internet lookup to Microsoft. When we lookup the error it mentions that MS DTC needs to be started for the notification services to be installed. We re-imaged the box and verified it was running prior to the install of SQL 2005. Anyone have any ideas why this error is occurring?
Is there any way around among SQL Server 2005 enhancements that provide something
similar to the event notifications effect? Notification Service is not supported on SQL Server 2005 Express; common problem with what I am trying to do is, for example: I want to produce event each time that row has been inserted into the table that would reach each of multiple users
using multiple instances of application that all connect to the same Express database;
one way to do this would be to use triggers on insert on the table, that could perhaps
insert appropriate row into some temporary table, and then read on timer that temporary table and fire event for each active user...
However, I was wondering if there was some other, perhaps lighter and built-in mechanism on Express that I could use?
Does SQLExpress support notification services? I read all kind of opposite statements. Some websites say they are supproted, other websites say it's not included.
Hello,I'm following the tutorial for setting up notification services, available here: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqltut9/html/44aa0556-b9c2-48f5-8775-134bbe0b90aa.htm. Where it says:Update the Instance of Notification Services After you modify the ADF, you update the instance of Notification Services to add event class objects to the application database:
In Object Explorer, expand Notification Services. Right-click Tutorial, point to Tasks, and then select Update. In the Update Instance dialog, click Browse, browse to the TutorialICF.xml file, and then click Open. In the Parameters grid, enter values for the _ServerName_, _DBEngineInstance_, and _InstancePath_ parameters. Use the same values you used to create the instance. Click OK. Review the Notification Services - Update Summary dialog box, and then click Update. When the update actions complete, click Close. I'm getting the error: The instance must be disabled before it can be updated or upgraded. How do I do that?Thanks.
I just wondered that now SQL Mail is classed as a legacy solution for email, does anyone know how to set up Notification Services to send email once a job has completed e.g. Backup or reindex?
I have tried to find a tutorial but with no joy so far!
I am trying to do custom log provider that will post events to Notification Services...The idea is that if I could post the "SSIS events" to Notification Services, with a Custom Delivery Channel in NS, I would be able to do NS Subscriptions with real time monitoring of SSIS packages..
I have tried to use the sample custom log provider code in MSDN. The problem is after I added the custom log provider dll to the C:Program FilesMicrosoft SQL Server90DTSLogProviders folder and install to global assembly using gacutil...
The custom log provider is not showing up in the Provider Type under Configure SSIS logs...
I'm trying to reinstall SQL Server 2005 STD but I cannot get Setup to install Integration Services or Notification Services. I looked in the log and this is what I found. Needless to say, it makes no sense to me. It seems that Setup is trying to find a missing source list for a certain product. I have no clue what that product is. I've spent endless hours trying to figure it out and I'm still drawing a blank. This is a printout of the log.
Integration services - ---------------------- MSI (s) (F0:B0) [02:33:01:437]: Machine policy value 'DisableUserInstalls' is 0 MSI (s) (F0:B0) [02:33:02:188]: End dialog not enabled MSI (s) (F0:B0) [02:33:02:188]: Original package ==> G:ServersSetupSqlRun_DTS.msi MSI (s) (F0:B0) [02:33:02:188]: Package we're running from ==> C:WINDOWSInstaller188966.msi MSI (s) (F0:B0) [02:33:02:339]: APPCOMPAT: looking for appcompat database entry with ProductCode '{EE8CFFD9-6E29-4DC3-A967-7348D5F41F44}'. MSI (s) (F0:B0) [02:33:02:339]: APPCOMPAT: no matching ProductCode found in database. MSI (s) (F0:B0) [02:33:02:339]: MSCOREE already loaded, using loaded copy MSI (s) (F0:B0) [02:33:02:379]: Couldn't find local patch ''. Looking for it at its source. MSI (s) (F0:B0) [02:33:02:379]: Resolving Patch source. MSI (s) (F0:B0) [02:33:02:379]: User policy value 'SearchOrder' is 'nmu' MSI (s) (F0:B0) [02:33:02:379]: User policy value 'DisableMedia' is 0 MSI (s) (F0:B0) [02:33:02:379]: Machine policy value 'AllowLockdownMedia' is 0 MSI (s) (F0:B0) [02:33:02:379]: SOURCEMGMT: Media enabled only if package is safe. MSI (s) (F0:B0) [02:33:02:379]: SOURCEMGMT: Looking for sourcelist for product {8B7E2749-624E-4C61-92FC-2BC18307FF24} MSI (s) (F0:B0) [02:33:02:379]: Note: 1: 1706 2: {8B7E2749-624E-4C61-92FC-2BC18307FF24} 3: MSI (s) (F0:B0) [02:33:02:379]: SOURCEMGMT: Failed to resolve source MSI (s) (F0:B0) [02:33:02:399]: Note: 1: 1708 MSI (s) (F0:B0) [02:33:02:399]: Note: 1: 2729 MSI (s) (F0:B0) [02:33:02:409]: Note: 1: 2729 MSI (s) (F0:B0) [02:33:02:409]: Product: Microsoft SQL Server 2005 Integration Services -- Installation failed.
MSI (s) (F0:B0) [02:33:02:439]: MainEngineThread is returning 1612 The installation source for this product is not available. Verify that the source exists and that you can access it. G:ServersSetupSqlRun_DTS.msi MSI (c) (B8:88) [02:33:02:439]: Decrementing counter to disable shutdown. If counter >= 0, shutdown will be denied. Counter after decrement: -1 MSI (c) (B8:88) [02:33:02:439]: MainEngineThread is returning 1612 === Verbose logging stopped: 5/19/2006 2:33:02 ===
----------------------- Notification services - ----------------------- MSI (s) (F0:30) [02:33:07:566]: Machine policy value 'DisableUserInstalls' is 0 MSI (s) (F0:30) [02:33:08:087]: End dialog not enabled MSI (s) (F0:30) [02:33:08:087]: Original package ==> G:ServersSetupSqlRun_NS.msi MSI (s) (F0:30) [02:33:08:087]: Package we're running from ==> C:WINDOWSInstaller18898f.msi MSI (s) (F0:30) [02:33:08:167]: APPCOMPAT: looking for appcompat database entry with ProductCode '{37E9AD9F-3217-4229-B5A5-7A0C82364C6C}'. MSI (s) (F0:30) [02:33:08:167]: APPCOMPAT: no matching ProductCode found in database. MSI (s) (F0:30) [02:33:08:167]: MSCOREE already loaded, using loaded copy MSI (s) (F0:30) [02:33:08:187]: Couldn't find local patch ''. Looking for it at its source. MSI (s) (F0:30) [02:33:08:187]: Resolving Patch source. MSI (s) (F0:30) [02:33:08:187]: User policy value 'SearchOrder' is 'nmu' MSI (s) (F0:30) [02:33:08:187]: User policy value 'DisableMedia' is 0 MSI (s) (F0:30) [02:33:08:187]: Machine policy value 'AllowLockdownMedia' is 0 MSI (s) (F0:30) [02:33:08:187]: SOURCEMGMT: Media enabled only if package is safe. MSI (s) (F0:30) [02:33:08:187]: SOURCEMGMT: Looking for sourcelist for product {65A498A6-83B9-4B91-99EA-1BA88D73F0AA} MSI (s) (F0:30) [02:33:08:187]: Note: 1: 1706 2: {65A498A6-83B9-4B91-99EA-1BA88D73F0AA} 3: MSI (s) (F0:30) [02:33:08:187]: SOURCEMGMT: Failed to resolve source MSI (s) (F0:30) [02:33:08:187]: Note: 1: 1708 MSI (s) (F0:30) [02:33:08:187]: Note: 1: 2729 MSI (s) (F0:30) [02:33:08:187]: Note: 1: 2729 MSI (s) (F0:30) [02:33:08:187]: Product: Microsoft SQL Server 2005 Notification Services -- Installation failed.
MSI (s) (F0:30) [02:33:08:197]: MainEngineThread is returning 1612 The installation source for this product is not available. Verify that the source exists and that you can access it. G:ServersSetupSqlRun_NS.msi MSI (c) (B8:DC) [02:33:08:207]: Decrementing counter to disable shutdown. If counter >= 0, shutdown will be denied. Counter after decrement: -1 MSI (c) (B8:DC) [02:33:08:207]: MainEngineThread is returning 1612
Hi I have report subscription to send mail to external emails. It works fine for Internal email id's, external email id's doesn't work. Any body knows what configuration changes we need to do. Please let me know if any body knows. Thx vijji
We are installing 2 instances of sql server in cluster env.
When intalling "Default" instance, I m getting Notification services, Integration, reporting services for default instance.
My question is : When we intall second named instance on cluster, will named instance share Notification services, Integration, reporting services with default instace.
So if above is yes, do I have to install Notification services, Integration, reporting services on C: drive of cluster nodes, so that it is shared by all the instances on the cluster nodes.
In SSIS 2008R2, I have a dataflow with an xlsx source and the destination is a SQL Server 2008R2 table. The files are delivered from a location where staff members 'work with' the source files. The files are produced monthly.
The dataflow that contains the file breaks upon the attempt to process subsequent monthly xlsx files with a message similar to the following:
--************* [TNUQQ [16]] Warning: The external columns for component "TNUQQ" (16) are out of synchronization with the data source columns. The column "F12" needs to be added to the external columns. The external column "county_taxable_sale_amount" needs to be updated. The external column "city_taxable_sale_amount" needs to be updated. The external column "district_taxable_sale_amount" needs to be updated. The external column "QTY" (62) needs to be removed from the external columns. --*************
I've noticed that some columns in the file ship with no data. A column with no data can be typed as datetime one month, and then float another month. I've tried to load xlsx to raw to table, but that does not work around this issue.
I've tried to set 'ValidateExternalMetadata' to 'False' on the Excel source, but that does not work either. Aside from going back to the folks who ship the file to us, is there anything that can be done in SSIS to work around this issue, and still wind up with valid data?
I have a SSRS 2012 report which references a custom c# assembly. This report exists in multiple environments (alpha, beta and production ) which are each associated with different data sources.
Is there a way for the assembly to determine the datasource used by the calling report so it can also connect to it?
Can anyone help me with a problem I have with SSRS 2005? How do I configure SQL Server 2005 Reporting Services to deliver subscription e-mails to addresses external to my windows domain?
I've been having trouble configuring the Report Server e-mail delivery component to send subscription e-mails to addresses outside the domain on which the report server is hosted.
For e.g. since my local windows domain is named "smartsystems.com", I have found that the Report Manager does not have any problems sending subscription mails to "eawagu@smartsystems.com" but throws up an error whenever I include "eawagu@yahoo.com" in the recipients' list.
I am getting the above error when I try to create Notificatioin services new Instance.
Additional Information is
Failed to connect to server ABC(Microsoft.SqlServer.ConnectionInfo)
An error occured while establishing connection to the server.When connecting to SQL Server 2005, this failure may be caused by the fact that under default settings SQL Server deoes not allow remote connections.(Provider : Named Pipes Provider, error 40 -could not open connection to SQL Server)(Microsoft SQL Server error: 53)
Please help me I am new to SQL Server 2005.I can't able to proceed.
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)?
One is Basket_ODS and the other is Intelligence_ODS. I am using service broker activation on a queue to move data from the Basket_ODS table to the Intelligence_ODS database. Previously I was able to move from table to table in Basket_ODS, however now that I am moving it to another database on the same instance it is no longer working.
If I set my active connection in SQL Management Studio to this user(BrokerUser) and execute the "move" procedure it works. When activated by Service Broker however, it does not. Here is the error message:
2006-05-09 14:47:52.940 spid86s The activated proc [ODS].[ProcessOrderQueue] running on queue Basket_ODS.ODS.Order Process Queue output the following: 'The server principal "BrokerUser" is not able to access the database "Intelligence_ODS" under the current security context.'
I'm sure I missed something becasue it works fine in the same database. BrokerUser has datareader and datawriter in both databases.
