Subscription Persists After Application Ends

Dec 19, 2007


I have a question about how Query Notification works and the objects and data created when a new subscription is created.

Here is what we are doing:

- When the application starts, we call the SqlDependency start method:

SqlDependency.Start( <connection string> )

- For each query we want to monitor, we run the following:

SqlConnection oConnection = new SqlConnection( <connection string> );
oConnection.Open();
SqlCommand oCommand = new SqlCommand( <sql statement> , oConnection);
SqlDependency oDependency = new SqlDependency(oCommand);
oDependency.OnChange += new OnChangeEventHandler( <handler delegate> );
SqlDataReader objReader = oCommand.ExecuteReader();
objReader.Close();

- When the application shuts down, we call the SqlDependency stop method:

SqlDependency.Stop( <connection string> )


We have observed that when we create a new subscription, the following things happen:
1. A row is added to sys.DM_QN_Subscriptions.
2. query_notification_% internal table is created.
3. SQLQueryNotificationStoredProcedure% stored procedure is created.
4. SQLQueryNotificationService% Service Queue is created.
5. queue_messages% internal table is created.

When the application ends, the stored procedure, service queue and queue messages internal table are dropped. The row in sys.DM_QN_Subscriptions and the query_notification_% persist.

When the application is restarted, a new row is added to sys.DM_QN_Subscriptions, but the existing query_notification_% internal table is used.

Is this normal behavior? Or, is there something we should be doing to clean up?

What I am wondering is if there is a threshold where these persistent items become a problem with locking, performance, other?

View 1 Replies


ADVERTISEMENT

How Do I Find Out When A Data Driven Subscription Ends?

Oct 10, 2007



Hello everybody,

I have a problem concerning the execution status of a data driven subscription. The problem is that
I“m creating a datamart and rendering several thousands of pdf based reports after that. I“ve found out how
to start the subscription using stored procedures with DTS.

Now I want to take further actions after the report building is done. But how do I find that out?

tfr
LG

View 3 Replies View Related

Creating A Subscription Via An Application

Mar 9, 2007

Hello,

I am wondering if there is some sample code out there that shows how to create a subscription for a report on reporting services via a win app or if anyone has a better suggestion. We are wanting to have a report that resides on reporting services server be sent to a client via email subscription, but do not want the client to goto the actual website that host reporting services. Thanks in advance.

John

View 3 Replies View Related

SQL 2012 :: Create A View Persists In Timing Out In About 30 Seconds

Feb 25, 2014

I am running some new queries in SQL 2012 (in SSMS) and, while slow, they do run. If I try to use the same query to create a view it persists in timing out in about 30 seconds. I see very little on this subject via google.

View 9 Replies View Related

Transact SQL :: Create Temp Table That Persists While A Front End App Is Open

Sep 30, 2015

I have an Access app. that I am migrating the DB portion (queries, tables) to SQL server. I need to create a temp table that lasts as long as the user has the Access FE app. open. Idea is that the temp table stores the user's parameters (used for filtering data entry forms and report). The parameters allow the app. to only show the user his data (cannot view other users data). The SP shown below works OK, it creates a ##Temp table and updates it with the parameters sent by Access FE app. The issue I am having is that as soon as the SP finishes the ##Temp table is removed. I thought of using a regular table, but, I am currently testing this migration in my local SQL server instance, as soon as I move the database to production environment, then users will not be able to create tables as permissions are only read/write.

USE [Work_Allocation]
GO
/****** Object: StoredProcedure [dbo].[spUser_Parameters_update] Script Date: 9/30/2015 12:27:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spUser_Parameters_update]

[code]...

View 10 Replies View Related

WHILE Statement Never Ends

Sep 25, 2006

Why does this query never ends??

Seems that parameter @Sucursales is always the same number. When the query loops in the while statement does it set @Sucursales to the result in the select statement (in red)? Or continue looping from the while statement?

DECLARE @Sucursales INT

SET @Sucursales = (SELECT COUNT(*) FROM CatSucursales)

BEGIN

WHILE @Sucursales > 0

INSERT INTO Compras(Fecha,NumOC,CveProveedor,Importe,CveDepartamento)

SELECT

Fecha,

NumOC,

CveProveedor,

CONVERT(money,SUM(Cantidad * Costo)) AS Importe,

CD.CveDepartamento

FROM

ComprasDetalle CD

INNER JOIN

( SELECT

CveProveedor,

Nombre,

CveDepartamento

FROM

CatProveedores CP

INNER JOIN CatDepartamentos CD ON

CP.CveSucursal = CD.CveSucursal

WHERE

CP.CveSucursal = @Sucursales

)VirCP ON

CD.Proveedor = VirCP.Nombre

AND CD.CveDepartamento = VirCP.CveDepartamento

WHERE

Fecha = DATEADD(dd, DATEDIFF(dd, 0, GETDATE())+0, -1)

GROUP BY Fecha, NumOC, CveProveedor, Nombre, CD.CveDepartamento

SET @Sucursales = @Sucursales - 1

END

View 6 Replies View Related

Query Never Ends Execution

Apr 16, 2007

Hello

I have a .Net application that calls an stored procedure. When it does, the execution goes and never ends (I have to kill the windows process). When I call the sp from within the Management Studio, it also never ends executing and I have to cancel the query. But, when I call it immediately after, it takes 45 seconds to complete.

Now, the sp has several parts and I have made that it prints a message at the end of each part so that I can read where it stops. Strange enough, it completes all parts except the last one, which has the form INSERT INTO myLocalTable SELECT * FROM MyRemoteTable. But if I execute the Select independetly, I discover that it brings no rows! Now, many of the @@rowcount printed after the execution of the other parts shows zero rows involved or just a few. I am not using cursors, each part is an UPDATE statement or an INSERT.

TestMachine1 runs SQL2005 SP2 and has as linked server myRemoteServer (SQL2000) server. The stored procedure in TestMachine1 inserts rows to a table in myRemoteServer and brings back some rows.

What could be wrong?

View 3 Replies View Related

Job Ends Successfully Even When It's Not True

Apr 23, 2008

Hi,
we've got this problem with some particular jobs: they look as they
ran correctly, but actually they didn't made it all through their
duties.
The problem is that this job is calling a sequence of DTS, where there
is a DTS with an ActiveX control which modifies another DTS before
launching and some other tasks: the error happens there, .
Launched from the DTS we get the error, from the job no...any idea how
we could get the correct job information?
Thank you
Daniele

View 5 Replies View Related

Stored Procedure Never Ends

Feb 28, 2008

Hi,


I have strange situation with one stored procedure on MS SQL Server 2000, sp3:
- It's some old procedure that worked well for several years
- It still works at other locations (even on MS SQL Server 7)
- Now sometimes this procedure just stops working over ODBC and OLE-DB connection. From Query Analyzer it works always ok! Over ODBC looks like client doesn't receive information, that procedure has completed.


I put some insert at the end of the procedure (for debbuging): procedure executes insert (it's the last statement in procedure), I can select inserted values from table, but client just doesn't get answer from server. I looked all processes at SQL Server and none of them is blocked, there are no locks that prevents procedure execution,...


I found out that recreating procedure makes it working again (for all clients). Well, at least for few days or even weeks.


Any ideas?


Thx!
Borut

View 12 Replies View Related

Linked Server Query Never Ends

Apr 25, 2001

I have a delete query on a linked server that never ends. I can do a select where ID = x and it returns lickity split but when I do a delete where id = x it never comes back? Any Clues???

View 1 Replies View Related

Cube Back Up Ends In Error

Aug 27, 2007

Hi all,

I have back up job that runs to back up Cube daily but lately it is failing and gives error bellow:

The operation has been cancelled. at Microsoft.AnalysisServices.Xmla.XmlaClient.CheckForSoapFault(XmlReader reader, XmlaResult xmlaResult, Boolean throwIfError) at Microsoft.AnalysisServices.Xmla.XmlaClient.CheckForError(XmlReader reader, XmlaResult xmlaResult, Boolean throwIfError) at Microsoft.AnalysisServices.Xmla.XmlaClient.SendMessage(Boolean endReceivalIfException, Boolean readSession, Boolean readNamespaceCompatibility) at Microsoft.AnalysisServices.Xmla.XmlaClient.SendMessageAndReturnResult(String& result, Boolean skipResult) at Microsoft.AnalysisServices.Xmla.XmlaClient.Execute(String command, String properties, String& result, Boolean skipResult, Boolean propertiesXmlIsComplete) at Microsoft.SqlServer.Management.Smo.Olap.SoapClient.ExecuteStatement(String stmt, StatementType stmtType, Boolean withResults, String properties, String parameters, Boolean restrictionListElement, String discoverType, String catalog) at Mi. The step failed.

But if i run that job manually it runs fine(successfully)

What could be the problem!!

thanks

View 1 Replies View Related

DTS SQL Server 2005 Package Never Ends

May 22, 2008

I now support a SQL Server "master" wrapper package that runs several other packages that simply never ends. When I run it in Visual Studio all the tasks complete, but the status stays running. I'm am trying to get this package to run via TWS/OPC and it doesn't end there either.

Does anyone know what the problem is? Has anyone heard of this?

Thanks,
Jim

View 5 Replies View Related

Loading Data At Both Ends Of A Merge Replication?

Feb 9, 2007

Hi gang,

We have a database that's using merge replication between two servers, and we need to insert a lot (about 1GB) of data into it.

The servers, however, are separated by a 192k WAN connection, so it's impractical to rely on the merge replication to send the data across to the subscriber.

Is there a way to insert the data at both ends? I can get the data out there on a DVD or a laptop easily enough. Can I load the data into both copies of the database and tell the merge agent that it's not to be replicated?

Thanks,
Matt

View 3 Replies View Related

Full Text Search - Ends With Option

Aug 14, 2007

I am implementing full text serach option in my project.
Its working fine for Begins with ("Text*")
Contains("Text")

But I am not able get the results for ends with ("*Text")


SELECT * FROM CATALOGUE_INDEX WHERE CONTAINS(SHORT_DESCRIPTION,'*OCK')


Now I am expecting the rows which are having "Stock" as value in short_desciption column.

But I am not getting the result.

Please advise me on how to do the same

Regards

Muralimohan


View 1 Replies View Related

Package Fails But Single Task Ends With Success

Nov 8, 2006

Hi

I've created a simple package that contains only one task that is an execute sql task. When I run only this single task from Business Intelligence development studio it runs successfully. But when I run the whole package (also from Business intlligence studio), the package fails.

The data source I access is ODBC. I'm sure the real reason for the error is the bad ODBC driver of the data source but this can't be changed. So I need to know what is different from running only a task in a package to running the whole package. If I knew that I might be able to adjust some setting and make it work.

Any help welcome.

View 4 Replies View Related

Accessing ConnectionString From ConnectionManager In A Script Task Ends With Login Failed! WHY!!

Feb 8, 2007

So in a script task for one of my packages I have a connection manager to an dtsConfig OLE DB.

This is the code

Dim ConnectionString As String = Dts.Connections("db_stage").ConnectionString
Dim sqlConnection As SqlConnection = New SqlConnection(ConnectionString )

I get a login failed for
user...But if I hardcode the connectionString, including the password this works.

1) Why is it that the ConnectionString from the connection manager omits this password?
2) Since this is an OLE DB, is there anyway to set the Data Source Designer to omit the "Provider=ABCDED.1" section?

Thanks!
Tony

View 5 Replies View Related

SSRS - Resetting A Subscription After A Subscription Run Failure

Feb 11, 2008



It seems to me that if a scheduled SSRS report subscription fails ( Status Message - An error has occurred during report processing) , that I actually need to delete the subscription and reconfigure it from scratch. The scheduled job doesn't try to run again automatically (say the next Monday on a weekly Monday schedule).

Is there a way to "reset" a failed subscription without have to recreate the entire subscription?

Thank you
Devon Kyle

View 5 Replies View Related

Existing Subscription Already Contains Table Included In The New Subscription

Sep 26, 2007







28638


SSCE_M_DUPLICATETABLE


Existing subscription already contains table included in the new subscription.


What are the possible causes of this merge replication error?
Could it be caused by a SQL Server Compact Edition User trying to sync their .sdf file after their subscription has already expired on the SQL Server?

Would you expect to see a different message if a SQL Server Compact Edition user tried to sync a subscriber database (.sdf file) with merge replication if it's been longer than the subscription retention period since their last sync?

View 6 Replies View Related

SQL 2012 :: AlwaysOn Availability Group Replica Ends Up With User Account As Owner

Sep 5, 2014

Setting up a test AlwaysOn Availability Group for one database.

However, whenever I restore the database to the replica server and join it, it ends up with my user account as the owner of the database.

Obviously I do not want a user account as the database owner, but since it is read-only I cannot modify it directly. If I were able to fail the AG over to the replica, I could change the owner then, but I cannot due to business requirements. this AG is to essentially serve as a replacement to log shipping.

I tried doing the backups and restores using EXECUTE AS login = 'sa', and yet it still shows up as my user account.

View 2 Replies View Related

Transact SQL :: Sending Auto Email With Queried Data (On INSERT And On Month Ends)

Oct 22, 2015

I have to send updated Employee list from employee master table to a particular email ID on every last date of Month and when a new employee is added / deleted / edited.Ā Also need to send this as an Excel fileĀ 

I tried the following but "Invalid Object name dbo.tbl_EmployeeMaster" error coming while inserting a new employee.

USE [eXact]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trg_Email]

[Code] ....

View 8 Replies View Related

Distrib.exe Application Error , Application Failed To Initialize Properly(0xx0000142)

Apr 13, 2008

have SQL Server 2005 std edition SP1 installed on Windows 2003 Std edition .Configured Transactional (single Publisher and no clustered environment.)
Replication past two months working fine, Now
1.Distrib.exe application err is coming.

Due to which my job is failing (Distributor to Subscriber).
Iam attaching thw file.
Thanks
Sandeep

View 1 Replies View Related

What I Need To Run Compiled Application On Another Application?

Sep 11, 2007



Hey,
don't know if it's the right place for this question but i hope you help me.
I made an application with VS 2005 that connecting to sql server file db.mdf
Now i want this application work on another user computer, and of course i don't want to install vs 2005 there.
I did install .net framework, but what i need to do to make the database work? do i have to install sql server on his computer? or is there something more simple?
I know that if i was using access file than i need to install nothing else.
please help!
thanx.
max

View 4 Replies View Related

Subscription

Jul 2, 2007



Hi,


We are migrating our reporting services database from one server to another.


What needs to be done to subscriptions to ensure they work properly. Will they automatically update as part of the migration, or is there a manual process.


We are using Reporting Services 2000 and will be using Reporting services 2000 as well on the new server.



Thanks

View 1 Replies View Related

DD Subscription

Nov 7, 2007

Hi all,
I'm creating data driven subscription for parameterized reports. I'm supposed to deliver one report to six different customers. But each customer can see only his own data in the report. This is not a problem. But the problem is I need another report in which all the data can be seen. When I create DD subsription, I chosed Auto increment option for the write mode under "





Specify delivery extension settings for Report Server FileShare" .
But then it creates seperate report for each parameter value. But it does not create the report which contains all the data for all parameter values. How can I make it without creating another report.
Also when I use overwrite option instead of Auto Increment, It creates only one reports. Does any one know why it happens?

Thanks

View 1 Replies View Related

Subscription

Dec 7, 2006

Hello,

is it possible to make a new subscription remote over a website and NOT in the Reportmanager?

Has anybody done something like this and are there any examples?



Thx

Jo-Jo

View 10 Replies View Related

Replication - Subscription

May 23, 2001

Hi,
I am doing a repl.in SQL7/NT.
At time of 'Push Subscription wizard' - I used the option "Yes,Initialize the schema and data......" whereas I already had data and schema at the subscriber.
Now my question is what will happen now and how long will this process take(for approx5GB of DAta).
OR should I stop the process - it's already an hour when I started the process.
Any suggestion appreciated.

View 1 Replies View Related

Subscription Pulls

Aug 24, 2001

I have created a subscription pull from one SQL server to another so that the database from one SQL server is replicated to another. But when the subscription runs, no data appears in the subscriber SQL server. When I check the subscription, I see that the database is connecting to the publisher but that is it. I have the login correct but what am I missing? Do the tables in the subscriber database have to be already created and matching the ones in the publisher database before the subscription runs or are they automaticaly created after the subscription runs?

Thank you in advance

View 1 Replies View Related

Generation Of Subscription

Feb 7, 2005

Hello all,

I have a data driven subscription with the information about the file name/file extension/ path etc coming in from a database. The problem is that the subscription status after running tells me that things are done and there is no error but the file is not being generated in the specified directory and for that reason the file is not generated at all anywhere on the hard disk. can anybody please help.

The information in the database for the report is as follows

FileName : Test1
FileExtn : True
Path = \ram\C$Reports
Render_Format = PDF
Username = Administrator
Password = password
Writemode = Overwrite

All the fields are varchar(50) in size.

View 1 Replies View Related

Subscription Reports

Mar 20, 2006

Hi, wonder if someone can help.

I've created a subscription and scheduled it to run but I get the following message:

Failure sending mail: The transport failed to connect to the server.

Anyone any ideas why? or what I can do to fix it?

Thanks

View 3 Replies View Related

Is DB Publication/subscription?

Jul 26, 2004

Because of problems trying to alter databases used
for replication my software would need to find out
if a database is a publisher or was repliated (using
T-SQL). Is this possible?

View 1 Replies View Related

Pull Subscription

Sep 27, 2006

My name is Tech guy,

I want to use window script program to execute a pull subscription installed on SQL 2005 express edition. Because it free for downloand.
is there a script that can call an existing pull subscription execution.

View 6 Replies View Related

Drop Subscription

Dec 28, 2006

is there anyone know how to drop subscription from the publisher database without a connection to the subscriber(because the subscriber server is formated)

View 3 Replies View Related

Pull Subscription

Sep 14, 2006

Are there any procedural differences between Express and MSDE considering pull subscription?

Thanks

abdul

View 2 Replies View Related







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