ExecuteNonQuery Hangs In Timer Event Notification
Apr 13, 2007
If I call ExecuteNonQuery() in a timer event callback in a console application, it hangs. Why is that?
.B ekiM
class Program
{
static SqlConnection conn = new SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=NONEOFYOURBISUINESS;Data Source=localhost");
static void Main(string[] args)
{
NativeMethods.MEMORYSTATUSEX mem = new NativeMethods.MEMORYSTATUSEX();
NativeMethods.GlobalMemoryStatusEx(mem);
Console.WriteLine("{0} bytes", mem.ullAvailPhys);
System.Timers.Timer aTimer = new System.Timers.Timer();
// Set the Interval to 2 seconds (2000 milliseconds).
aTimer.Interval = 1000;
aTimer.Enabled = true;
// Hook up the Elapsed event for the timer.
aTimer.Elapsed += new ElapsedEventHandler(OnTimedEvent);
Console.ReadLine();
}
private static void OnTimedEvent(object source, ElapsedEventArgs e)
{
NativeMethods.MEMORYSTATUSEX mem = new NativeMethods.MEMORYSTATUSEX();
NativeMethods.GlobalMemoryStatusEx(mem);
SqlCommand cmd = new SqlCommand(
"INSERT INTO Observation (StatisticType, StatisticObserved, StatisticValue) VALUES (1, @When, @AvailPhys);
" +
"INSERT INTO Observation (StatisticType, StatisticObserved, StatisticValue) VALUES (2, @When, @AvailPageFile);
" +
"INSERT INTO Observation (StatisticType, StatisticObserved, StatisticValue) VALUES (3, @When, @AvailVirtual);
" +
"INSERT INTO Observation (StatisticType, StatisticObserved, StatisticValue) VALUES (4, @When, @AvailExtendedVirtual);
");
DateTime dt = DateTime.Now;
cmd.Parameters.AddWithValue("AvailPhys", mem.ullAvailPhys);
cmd.Parameters.AddWithValue("AvailPageFile", mem.ullAvailPageFile);
cmd.Parameters.AddWithValue("AvailVirtual", mem.ullAvailVirtual);
cmd.Parameters.AddWithValue("AvailExtendedVirtual", mem.ullAvailExtendedVirtual);
cmd.Parameters.AddWithValue("When", dt);
cmd.ExecuteNonQuery();
Console.WriteLine("Inserted {0}", dt);
}
}
View 3 Replies
ADVERTISEMENT
Aug 10, 2004
Does anyone know if there is any software available that notifies specified people when an error above a certian level occurs. Im thinking along the lines of email and text message.
Im running sql server 2k at sp3a level, and the software will have to be compatible with it so as to get the event notification to pass the information on.
If there isn't any software, would anyone know of any scripts that will do this?
View 4 Replies
View Related
Aug 10, 2004
I am trying to set up the email notification system in SQL server 2000, but im having some problems. Mainly, im having problems with setting up an email account in the first place (there is a dedicated mail box, but the one im working with is a different box altogether).
If anyone could help fathom this out from start to finish i would be really grateful.
View 1 Replies
View Related
May 21, 2006
Hi
I have a simple question regarding event notification in sql 2005. I keep up getting a message indicating a syntax error when trying to execute the following script. I also tried with [ instead of '.
CREATE EVENT NOTIFICATION NotifyCT
ON DATABASE
FOR CREATE_TABLE
TO SERVICE 'NotifyService'
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'NotifyService'.
I cannot find any error in my syntax. Please help
Thanks
View 1 Replies
View Related
Jan 30, 2006
Hello Remus,
I have implemented Event Notification sample you provided in my logic, but I am perplexed that even though my watched queue does go down, I do not get message in the Event_Collector queue all the time. The Event_Collector queue does stay enabled though. Any thoughts? I guess any suggestions on debugging the event notification portion would be helpful.
Lubomir
View 4 Replies
View Related
Jul 23, 2005
I would like to propagate an event signal to an external applicationwhen a table in my MSSql2000 server is updated.Prog A; I have an external application adding records to a table.Prog B; I have another external application using this table as well.When Prog A creates a new record in the Table, how can I have Prog B benotified of the event without polling the table or creating a linkbetween Prog A and Prog B.Thanks.
View 2 Replies
View Related
Jun 19, 2007
Hello,
I have configured an Event Notification for BROKER_QUEUE_DISABLED.
I created a Queue different from the one the Receive my normal messages and on this queue I define an activation.
The Activated SP takes the [EventNotification] message and execute a
RAISEERROR('Message',0,1) WITH LOG.
The error is properly logged in the SQL server event log but I gave a look the Initiator and target sys.conversation_endpoints catalog view and I have see the the conversations are in the status CONVERSING or SI ...
Do I have to call the END CONVERSATION after the commit or these statuses are caused by some mistakes on the message process workflow?
Thank you
Marina B.
View 4 Replies
View Related
Feb 21, 2007
Hello,
Can anyone spot what i am missing here ? The problem is that i am getting a null object for e.TextData in the t_OnEvent(object sender, TraceEventArgs e) function below. I am trying to get event- notifications while processing the data mining structure.
thanks
anil
//-------------code below-------------------------------------
using Microsoft.AnalysisServices;
private void ProcessMiningStructure(MiningStructure mStruct)
{
Trace t;
TraceEvent e;
t = server.Traces.Add();
e = t.Events.Add(TraceEventClass.ProgressReportCurrent);
e.Columns.Add(TraceColumn.TextData);
t.OnEvent += new TraceEventHandler(t_OnEvent);
t.Update();
try
{
t.Start();
mStruct.Process(ProcessType.ProcessFull);
t.Stop();
}
catch (Exception ex)
{
}
t.Drop();
}
//------------------------------------------------------
void t_OnEvent(object sender, TraceEventArgs e)
{
SetText(e.TextData);
}
//------------------------------------------------------
View 4 Replies
View Related
Aug 20, 2007
Hi
I am unable to drop the above login. The error is Error:15141 The server principal owns an event notification and cannot be dropped.
I have looked in the table sys.server_event_notifications and there are rows returned that have a server_principal_id of the user I am trying to drop. No events have been created on this server, so I am assuming these notifications are either created by default or are somehow related to Database Mail?
All the event notifications belong to service name "SQL/Notifications/ProcessWMIEventProviderNotification/v1.0" and begin with SQLWEP (i.e. SQLWEP_RECHECK_SUBSCRIPTIONS, SQLWEP_B415ADB8_A604_4057_976F_600002FA5AF6). How can I find out what these are for and how they were created?
What purpose do these event notifications have? Is there some syntax to change the owner of these event notifications so I can successfully drop the login? If the only way is to directly update the system view, is this safe and what repercussions could this have?
Thanks
Martin
View 4 Replies
View Related
Oct 17, 2007
Hi there,
Version : Framework 2,0 (assemly: C:WINDOWSMicrosoft.NETFrameworkv2.0.50727System.Windows.Forms.dll)
The debuger hangs at the line e.Cancel = true in RowValidation event with 100% cpu ,when the focus go out of
SplitContainer .
i am using visual studio 2005 professional , with service pack1..
Appriciate your help.
thanks
oz
--
private void INVOICEDETAILDataGridView_RowValidating(object sender, DataGridViewCellCancelEventArgs e)
{
if (!INVOICEDETAILDataGridView.IsCurrentRowDirty) return;
DataGridViewRow CurRow = this.INVOICEDETAILDataGridView.Rows[e.RowIndex];
if (CurRow.Cells["ACCOUNT"].value.ToString().Trim().Length < 1)
{
MessageBox.Show(ex.Message, "Application Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
e.Cancel = true;
}
View 1 Replies
View Related
Dec 8, 2003
I am trying to write my first app using SQL Server. I am not able to even open a connection. I get the following error message:
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
I am not sure first of all why the user is listed as null. I hard coded a user name in for test purposes. Second, how DO I establish a trusted connection with a SQL Server. The SQL Server is located on an internal domain controller.
Also, is it necessary to have anything special installed on my remote machine? As I said, I am VERY new to Microsoft SQL Server
View 3 Replies
View Related
Jun 2, 2015
Recently we migrated our environment to 2012.
We are planning to implement Xevents in all the servers in place of Trace files and everything is working fine.
Is it possible to configure Extended event to trigger a mail whenever any event (example dead lock) occurs.
I have gone through so many websites but i never find.
View 13 Replies
View Related
Oct 25, 2011
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.
View 2 Replies
View Related
Apr 8, 2008
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 ?
View 2 Replies
View Related
May 31, 2007
Server 2003 SE SP1 5.2.3790 Sql Server 2000, SP 4, 8.00.2187 (latest hotfix rollup)
We fixed one issue, but it brought up another. the fix we applied stopped the ServicesActive access failure, but now we have a failure on MSSEARCH. The users this is affecting do NOT have admin rights on the machine, they are SQL developers.
We were having
Event Type: Failure Audit
Event Source: Security
Event Category: Object AccessEvent ID: 560
Date: 5/23/2007
Time: 6:27:15 AM
User: domainuser
Computer: MACHINENAME
Description:
Object Open:
Object Server: SC Manager
Object Type: SC_MANAGER OBJECT
Object Name: ServicesActive
Handle ID: -
Operation ID: {0,1623975729}
Process ID: 840
Image File Name: C:WINDOWSsystem32services.exe
Primary User Name: MACHINE$
Primary Domain: Domain
Primary Logon ID: (0x0,0x3E7)
Client User Name: User
Client Domain: Domain
Client Logon ID: (0x0,0x6097C608)
Accesses: READ_CONTROL
Connect to service controller
Enumerate services
Query service database lock state
Privileges: -
Restricted Sid Count: 0
Access Mask: 0x20015
Applied the following fix
http://support.microsoft.com/kb/907460/
Now we are getting
Event Type: Failure Audit
Event Source: Security
Event Category: Object Access
Event ID: 560
Date: 5/23/2007
Time: 10:51:23 AM
User: domainuser
Computer: MACHINE
Description:
Object Open:
Object Server: SC Manager
Object Type: SERVICE OBJECT
Object Name: MSSEARCH
Handle ID: -
Operation ID: {0,1627659603}
Process ID: 840
Image File Name: C:WINDOWSsystem32services.exe
Primary User Name: MACHINE$
Primary Domain: domain
Primary Logon ID: (0x0,0x3E7)
Client User Name: user
Client Domain: domain
Client Logon ID: (0x0,0x60D37C1A)
Accesses: READ_CONTROL
Query service configuration information
Query status of service
Enumerate dependencies of service
Query information from service
Privileges: - Restricted Sid Count: 0 Access Mask: 0x2008D
View 4 Replies
View Related
Nov 2, 2007
Hi all,
Can we get the event properties by using a query?
Are there any extended stored procuder to get the above?
Scenario:
>Desktop>Right Click on My Computer
>Go to Manage and click
>Expand System Tools
>Expand Event Viewer
>Application
click on one event.We can get the log info which is the manual procudure.
But now i want to get the event properties through the Query analyzer...
Any help would be great?
Thanks,
View 4 Replies
View Related
Sep 17, 2007
I'm not sure this is the place for this question, but not sure where else to go. I've written asp.net code to read from a sql server 2005 db and send out customized emails based on user info.Currently the process gets rolling by clicking a button in a web page.The client doesn't want to click a button, they want to run the email sender on a timer.How can I set up my function to run on a timer either in asp.net or more likely called from sql server?
View 6 Replies
View Related
Jul 20, 2005
HI,I need to run same kind of transactions (basically deleting records)in a loop but I have only 1 hour in a day to run my procedure. So Ineed to set a timer in a SP so that SP terminates after one hour andthen rest of the transactions will be done next day.Can anybody suggest as how to check execution time in a storedprocedure? The execution of the SP will be scheduled every night.If u need any further info pls ask.Thanks,Subodh
View 2 Replies
View Related
Jun 6, 2007
Hi,
Im trying to build a solution that Selects a couple of rows, checks their timestamp and sends them as a file with FTP if they´re up to date.
However, those old rows that haven´t been updated yet I need to check every hour to see if the they have been updated and send them with FTP when thei´re up to date.
Is is possable to solve this by using SSIS?
Many thanks.
View 1 Replies
View Related
Apr 27, 2006
I'm using conversation timers successfully to fire events at a predetermined time in the future, but I'm running into issues when using an interval of considerable size. I set the conversation timeout like so:
set @Timeout = DATEDIFF(SECOND, GETDATE(), DATEADD(MINUTE, -(@TimeOffset), @FollowUpDateTime));
if (@Timeout < 0)
set @Timeout = 1;
// begin dialog
begin conversation timer (@FollowUpConversation)
timeout = @Timeout;
In this case @Timeout was 94693494.
In the SQL error log I see the following error: "Invalid subcommand value 94693494. Legal range from 1 to 2147483."
I thought I may check the @Timeout value and simply set it to 2147483 if it is larger than 2147483, but I was wondering if there was a reason the upper limit was so small.
Thanks,
Chris
View 10 Replies
View Related
Apr 24, 2006
I'd like to add code to a trigger to calculate the time to fire a message into a queue based on a field changing, and conversation timers seem like the way to go. My first question refers to this line from the BOL:
"Calling BEGIN CONVERSATION TIMER on a conversation before the timer has expired sets the timeout to the new value."
I think that in this trigger, I can simply begin a new conversation if the given field has changed to reset the timer. But intuition tells me that in order to change the timer to a new value, I need to retrieve the existing conversation, correct?
Also, I've read that conversation timers are persistent in that they survive database restarts and shutdowns. But I'm not sure to what extent. After a database restart/shutdown, does the conversation timer "reset" itself to the time interval specified when the conversation was begun or is it able to account for the time the database was down/offline?
Thanks,
Chris
View 5 Replies
View Related
Nov 17, 2006
I need to follow up on a message and check on its status. I am planning on using Conversation Timers (self addressed). I've tried it and they do work well. I am wondering if the LIFETIME parameter can be used for the same purpose. If the dialog has not been closed and the LIFETIME expires, will a message be queued into the service's queue? It does not seem that this is the case, but it is worth checking, as it could be a much desired feature.
Thanks,
View 5 Replies
View Related
Apr 30, 2007
Hi,
I am using conversation Timer for delaying a message for a few seconds but I can see the message immediately in the queue.
Here is the code i am using. This is a part of a stored procedure I have used.
BEGIN CONVERSATION TIMER ( @h ) TIMEOUT = @DelayBySeconds;
SEND ON CONVERSATION @h
MESSAGE TYPE [sendmsg]
(@msg);
I am executing this stored procedure with following statements.
exec set_ssb_msg 'test3', 25;
exec set_ssb_msg 'test1', 1;
select * from q1
I was hoping to see just the 'Test1' and see test3 after 25 seconds. But I could see both the messages in a queue as soon as i run the stored proc.
If I execute a receive command on the queue, I am receiving 'test3' first and then 'test1'. This is exactly opposit of what i expected.
Can you please let me know if I am doing anything wrong or missing a step.
Any help is greatly appreciated.
Thanks,
Don.
View 1 Replies
View Related
Aug 14, 2007
New to service broker, and my books haven't come in yet. I'm trying to figure out how Internal Activation works.
The following code seems to "work", in terms of the inline code, but it doesn't seem to be going through the activation PROCs. Is there something else I need to set or do?
TIA, Bob
USE master;
GO
-- Ensure Service Broker functionality is enabled.
ALTER DATABASE ICEBOX SET ENABLE_BROKER;
GO
USE ICEBOX;
GO
-- Drop all sample objects if present.
IF EXISTS (SELECT * FROM sys.services WHERE name = N'BobsReceiveService') DROP SERVICE BobsReceiveService;
IF EXISTS (SELECT * FROM sys.service_queues WHERE name = N'BobsReceiveQueue') DROP QUEUE BobsReceiveQueue;
IF EXISTS (SELECT * FROM sys.services WHERE name = N'BobsSendService') DROP SERVICE BobsSendService;
IF EXISTS (SELECT * FROM sys.service_queues WHERE name = N'BobsSendQueue') DROP QUEUE BobsSendQueue;
IF EXISTS (SELECT * FROM sys.service_contracts WHERE name = N'BobsContract') DROP CONTRACT BobsContract;
IF EXISTS (SELECT * FROM sys.service_message_types WHERE name = N'BobsSendMessageType') DROP MESSAGE TYPE BobsSendMessageType;
IF EXISTS (SELECT * FROM sys.service_message_types WHERE name = N'BobsReceiveMessageType') DROP MESSAGE TYPE BobsReceiveMessageType;
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='PROCEDURE' AND ROUTINE_NAME = 'USP_BobsReceiveQueueMonitor') DROP PROCEDURE dbo.USP_BobsReceiveQueueMonitor;
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='PROCEDURE' AND ROUTINE_NAME = 'USP_BobsSendQueueMonitor') DROP PROCEDURE dbo.USP_BobsSendQueueMonitor;
GO
CREATE PROCEDURE dbo.USP_BobsSendQueueMonitor
AS
DECLARE @QueueTable TABLE
(status tinyint --Status of the message. For messages returned by the RECEIVE command, the status is always 1. Messages in the queue may contain one of the following values: 0=Received message1=Ready2=Not yet complete3=Retained sent message
,queuing_order bigint --Message order number within the queue.
,conversation_group_id uniqueidentifier --Identifier for the conversation group that this message belongs to.
,conversation_handle uniqueidentifier --Handle for the conversation that this message is part of.
,message_sequence_number bigint --Sequence number of the message within the conversation.
,service_name nvarchar(512) --Name of the service that the conversation is to.
,service_id int --SQL Server object identifier of the service that the conversation is to.
,service_contract_name nvarchar(256) --Name of the contract that the conversation follows.
,service_contract_id int --SQL Server object identifier of the contract that the conversation follows.
,message_type_name nvarchar(256) --Name of the message type that describes the message.
,message_type_id int --SQL Server object identifier of the message type that describes the message.
,validation nchar(2) --Validation used for the message. E=Empty N=None X=XML
,message_body varbinary(MAX) --Content of the message.
,message_id uniqueidentifier --Unique identifier for the message.
);
-- FROM BoL
-- The WHERE clause of the RECEIVE statement may only contain search conditions that use conversation_handle or conversation_group_id.
-- The search condition may not contain any of the other columns in the queue.
-- The conversation_handle or conversation_group_id may not be an expression.
DECLARE @SendDialog_Handle uniqueidentifier;
DECLARE @SendMsg varbinary(MAX);
DECLARE @SendMsgName nvarchar(256);
BEGIN
PRINT 'SEND' + CONVERT(VARCHAR,GETDATE(),109);
SELECT TOP (1)
@SendDialog_Handle = conversation_handle
,@SendMsg = message_body
,@SendMsgName = message_type_name
FROM
BobsSendQueue;
SELECT 'SEND',@SendDialog_Handle AS conversation_handle,CAST(@SendMsg AS VARCHAR(MAX)) AS message_body,@SendMsgName AS message_type_name;
END;
GO
CREATE PROCEDURE dbo.USP_BobsReceiveQueueMonitor
AS
DECLARE @QueueTable TABLE
(status tinyint --Status of the message. For messages returned by the RECEIVE command, the status is always 1. Messages in the queue may contain one of the following values: 0=Received message1=Ready2=Not yet complete3=Retained sent message
,queuing_order bigint --Message order number within the queue.
,conversation_group_id uniqueidentifier --Identifier for the conversation group that this message belongs to.
,conversation_handle uniqueidentifier --Handle for the conversation that this message is part of.
,message_sequence_number bigint --Sequence number of the message within the conversation.
,service_name nvarchar(512) --Name of the service that the conversation is to.
,service_id int --SQL Server object identifier of the service that the conversation is to.
,service_contract_name nvarchar(256) --Name of the contract that the conversation follows.
,service_contract_id int --SQL Server object identifier of the contract that the conversation follows.
,message_type_name nvarchar(256) --Name of the message type that describes the message.
,message_type_id int --SQL Server object identifier of the message type that describes the message.
,validation nchar(2) --Validation used for the message. E=Empty N=None X=XML
,message_body varbinary(MAX) --Content of the message.
,message_id uniqueidentifier --Unique identifier for the message.
);
-- FROM BoL
-- The WHERE clause of the RECEIVE statement may only contain search conditions that use conversation_handle or conversation_group_id.
-- The search condition may not contain any of the other columns in the queue.
-- The conversation_handle or conversation_group_id may not be an expression.
DECLARE @ReceiveDialog_Handle uniqueidentifier;
DECLARE @ReceiveMsg varbinary(MAX);
DECLARE @ReceiveMsgName nvarchar(256);
BEGIN
PRINT 'RECEIVE' + CONVERT(VARCHAR,GETDATE(),109);
SELECT TOP (1)
@ReceiveDialog_Handle = conversation_handle
,@ReceiveMsg = message_body
,@ReceiveMsgName = message_type_name
FROM
BobsReceiveQueue;
SELECT 'RECEIVE',@ReceiveDialog_Handle AS conversation_handle,CAST(@ReceiveMsg AS VARCHAR(MAX)) AS message_body,@ReceiveMsgName AS message_type_name;
END;
GO
CREATE MESSAGE TYPE BobsSendMessageType VALIDATION = NONE;
CREATE MESSAGE TYPE BobsReceiveMessageType VALIDATION = NONE;
CREATE CONTRACT BobsContract
(BobsSendMessageType SENT BY INITIATOR
,BobsReceiveMessageType SENT BY TARGET
);
CREATE QUEUE BobsSendQueue
WITH ACTIVATION
(STATUS = ON
,PROCEDURE_NAME = dbo.USP_BobsSendQueueMonitor
,MAX_QUEUE_READERS = 1
,EXECUTE AS OWNER
)
;
CREATE SERVICE BobsSendService ON QUEUE BobsSendQueue;
CREATE QUEUE BobsReceiveQueue
WITH ACTIVATION
(STATUS = ON
,PROCEDURE_NAME = dbo.USP_BobsReceiveQueueMonitor
,MAX_QUEUE_READERS = 1
,EXECUTE AS OWNER
)
;
CREATE SERVICE BobsReceiveService ON QUEUE BobsReceiveQueue (BobsContract);
GO
DECLARE @BobsDialog_Handle UNIQUEIDENTIFIER;
DECLARE @SendMsg NVARCHAR(100);
SET @SendMsg = N'Hello Bob';
BEGIN TRANSACTION;
BEGIN DIALOG CONVERSATION @BobsDialog_Handle FROM SERVICE BobsSendService TO SERVICE N'BobsReceiveService' ON CONTRACT BobsContract WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @BobsDialog_Handle MESSAGE TYPE BobsSendMessageType (@SendMsg);
COMMIT TRANSACTION;
SELECT 'Conversation started on Handle ' + CAST(@BobsDialog_Handle AS VARCHAR(36));
GO
DECLARE @BobsDialog_Handle UNIQUEIDENTIFIER;
DECLARE @ReceiveMsg NVARCHAR(100);
DECLARE @ResponseMsg NVARCHAR(100);
DECLARE @ReceiveMsgName SYSNAME;
SET @ResponseMsg = N'Back at ya';
BEGIN TRANSACTION;
RECEIVE TOP(1)
@BobsDialog_Handle = conversation_handle
,@ReceiveMsg = message_body
,@ReceiveMsgName = message_type_name
FROM
BobsReceiveQueue;
COMMIT TRANSACTION;
SELECT @ReceiveMsgName AS ReceiveMsgName,@ReceiveMsg AS ReceiveMsg;
SEND ON CONVERSATION @BobsDialog_Handle MESSAGE TYPE BobsReceiveMessageType (@ResponseMsg);
END CONVERSATION @BobsDialog_Handle;
GO
DECLARE @ResponseMsg NVARCHAR(100);
DECLARE @ResponseMsgName SYSNAME;
DECLARE @BobsDialog_Handle UNIQUEIDENTIFIER;
BEGIN TRANSACTION;
RECEIVE TOP(1)
@BobsDialog_Handle = conversation_handle
,@ResponseMsg = message_body
,@ResponseMsgName = message_type_name
FROM BobsSendQueue;
END CONVERSATION @BobsDialog_Handle;
COMMIT TRANSACTION;
SELECT @ResponseMsgName AS ResponseMsgName, @ResponseMsg AS ResponseMsg;
GO
IF EXISTS (SELECT * FROM sys.services WHERE name = N'BobsReceiveService') DROP SERVICE BobsReceiveService;
IF EXISTS (SELECT * FROM sys.service_queues WHERE name = N'BobsReceiveQueue') DROP QUEUE BobsReceiveQueue;
IF EXISTS (SELECT * FROM sys.services WHERE name = N'BobsSendService') DROP SERVICE BobsSendService;
IF EXISTS (SELECT * FROM sys.service_queues WHERE name = N'BobsSendQueue') DROP QUEUE BobsSendQueue;
IF EXISTS (SELECT * FROM sys.service_contracts WHERE name = N'BobsContract') DROP CONTRACT BobsContract;
IF EXISTS (SELECT * FROM sys.service_message_types WHERE name = N'BobsSendMessageType') DROP MESSAGE TYPE BobsSendMessageType;
IF EXISTS (SELECT * FROM sys.service_message_types WHERE name = N'BobsReceiveMessageType') DROP MESSAGE TYPE BobsReceiveMessageType;
GO
View 3 Replies
View Related
Feb 7, 2007
Hi Remus,
I just started looking into SB about a week ago, so my question is likely to be pretty lame. However, that's not going to stop me from asking it :-)
We're trying to do something similar to what you're describing here. How do you "reset" a dialog timer? Why would you need to deal with resetting timers (or with timers at all for that matter) in the event of a success? Couldn't you rewrite the logic to be as follows?
begin transaction
receive message
if message is web request
save state of request (http address, caller etc)
else if message is retry timer message
load state of request
endif
commit
do the web request (no transaction open)
if success
begin transaction
send back response
end conversation
commit
else
set a retry timer on the dialog (say 1 minute) using BEGIN DIALOG TIMER
endif
Also, when you set the retry timer you have to associate it with the saved request state, right? Otherwise, how will the service know which request to load on receipt of the timer message?
TIA.
View 6 Replies
View Related
Oct 25, 2006
I want to test some times it takes for a proc to run. Since I work for a large company, I rarely have access to the SQL server in a capacity where I can use profiler and such. Are there any quick and easy ways to just surround blocks of code in a T-SQL statement to get an accurate reading on how long it takes?I.e., if I surround with GETDATE() before and after, does that also measure the round trips to the server, or just the execution time.I want to just compare some different methods and see what is quicker. THX.
View 4 Replies
View Related
Dec 3, 2006
I need to check the databes on the server side every 3 days and delete old data.
I am using SQL Express.
View 5 Replies
View Related
May 21, 2008
I have an application that automatically reads a lot of data from a third-party application into my database, via XML. For example, I might read a couple thousand rows-worth of XML data, one row at a time in a foreach loop.
To reduce the load on their server and database, I thought about putting a 2 second delay in between each of my automatic requests. Would this really help much, or is there enough overhead (setting up/tearing down connections, etc) with each request that it wouldn't reduce server load much anyway?
Is 2 seconds enough? Too little or too much?
View 3 Replies
View Related
Mar 8, 2007
Hello,
I'm using SQL service Broker 2005 with ASP.NET 2.0 in order to use the sql cache dependency.
everything works fine...
I have only a doubt regarding a query that is constantly executed on mu db ( i can see it be means of the SQL Profiler)
The query is:
exec sp_executesql N'BEGIN CONVERSATION TIMER ("'') TIMEOUT = 120; WAITFOR(RECEIVE TOP (1) message_type_name, conversation_handle,
cast(message_body AS XML) as message_body from [SqlQueryNotificationService-GUID]), TIMEOUT @p2;',N'@p2 int',@p2=60000
The web application is not running from a browser ...
It this ok or I forget to clean/reset something from my web application and/or sql server?
Thank you
Marina B.
View 3 Replies
View Related
Oct 22, 2007
We recently upgraded to SQL 2005 from SQL 2000. We have most of our issues ironed out however about every 1 minute there is a message in the Application Event log and the SQL log that states:
EVENT ID 18456 Login Failed for the users DOMAIN/ACCOUNT [CLIENT: <local machine>]
This is a state 16 message which I thought meant that the account does not have access to the default database. The account is actually the account that the SQL services run under.
Any ideas? We can't seem to figure this one out. We actually upgraded to 2005 from 2000 and had an error appear after every reboot that prevented the SQL Agent from running(This application has failed to start because GAPI32.dll was not found. Re-installing the application may fix this problem.) We did a full uninstall of SQL and reinstalled fresh and restored the databases from .bak files and that is when the EVENT ID 18546 started occuring every minute.
We don't have any SQL heavy hitters here so please be detailed with any possible solutions. That you very much for any help you can provide!
David
View 5 Replies
View Related
Jan 20, 2008
Dear;
I got a problem executenonquery in asp.net 2.0. Below as my Code:
1 Dim conn As New SqlConnection(tmpconn)2 Dim cmd1 As New SqlCommand("SP_RPTFABTRANSFER_DYEING_PREV", conn)3 4 cmd1.CommandType = CommandType.StoredProcedure5 cmd1.CommandTimeout = 9006 cmd1.Parameters.Add(New SqlParameter("@aSTDATE", SqlDbType.VarChar, 10))7 cmd1.Parameters.Add(New SqlParameter("@aEDDATE", SqlDbType.VarChar, 10))8 cmd1.Parameters.Add(New SqlParameter("@aBUYERID", SqlDbType.VarChar, 10)) 9 cmd1.Parameters.Add(New SqlParameter("@aFACTORYID", SqlDbType.VarChar, 10))10 cmd1.Parameters.Add(New SqlParameter("@aFabGrpId", SqlDbType.VarChar, 10))11 cmd1.Parameters.Add(New SqlParameter("@aFABSUPPIDFROM", SqlDbType.VarChar, 10))12 cmd1.Parameters.Add(New SqlParameter("@aFABSUPPIDTO", SqlDbType.VarChar, 10))13 cmd1.Parameters.Add(New SqlParameter("@aUSERID", SqlDbType.VarChar, 20))14 cmd1.Parameters.Add(New SqlParameter("@aDelType", SqlDbType.VarChar, 20))15 16 cmd1.Parameters("@aSTDATE").Value = lstartdt17 cmd1.Parameters("@aEDDATE").Value = lenddt18 cmd1.Parameters("@aBUYERID").Value = Trim(dropBuyer.SelectedValue)19 cmd1.Parameters("@aFACTORYID").Value = Trim(dropFactory.SelectedValue)20 cmd1.Parameters("@aFabGrpId").Value = lFabGrp21 cmd1.Parameters("@aFABSUPPIDFROM").Value = fabFrom22 cmd1.Parameters("@aFABSUPPIDTO").Value = fabTo23 cmd1.Parameters("@aUSERID").Value = Session("UID").ToString24 cmd1.Parameters("@aDelType").Value = lDelType25 Try26 conn.Open()27 cmd1.ExecuteNonQuery()28 conn.Close()29 Catch ex As Exception30 lblerr.Visible = True31 lblerr.Text = ex.Message32 Finally33 conn.Close()34 End Try
Web.Config<add name="oldtextileConnectionString" connectionString="Data Source=xx.xx.xx.xx;Initial Catalog=ERP;Integrated Security=TRUE;Connection Lifetime=0;Min Pool Size =0;Max Pool Size=1000;Pooling=true;" providerName="System.Data.SqlClient"/>
This Code running on asp only 2min Execute time. But i try it on Asp.net 2.0 take a long time about 15min or request time out error. Could any one can give me some tips or hits? Help much appreciated. Thanks
View 5 Replies
View Related
Sep 7, 2006
I hope you would help me in this problem. I use the code below for executenonquery command for mdb DB.But I do not know the changes I should made when Using SQL2005.-------------Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; " & _ "Data Source=C:ASPNET20dataNorthwind.mdb" Dim dbConnection As New OleDbConnection(connectionString) dbConnection.Open() Dim commandString As String = "INSERT INTO Employees(FirstName, LastName) " & _ "Values(@FirstName, @LastName)" Dim dbCommand As New OleDbCommand(commandString, dbConnection) Dim firstNameParam As New OleDbParameter("@FirstName", OleDbType.VarChar, 10) firstNameParam.Value = txtFirstName.Text dbCommand.Parameters.Add(firstNameParam) Dim lastNameParam As New OleDbParameter("@LastName", OleDbType.VarChar, 20) LastNameParam.Value = txtLastName.Text dbCommand.Parameters.Add(LastNameParam) dbCommand.ExecuteNonQuery() dbConnection.Close()--------
View 2 Replies
View Related
Sep 11, 2006
Hi, I am trying to execute a nonquery as follows (look for bold):Dim connStringSQL As New SqlConnection("Data Source=...***...Trusted_Connection=False")'// Create the new OLEDB connection to Indexing ServiceDim connInd As New System.Data.OleDb.OleDbConnection(connStringInd)Dim commandInd As New System.Data.OleDb.OleDbDataAdapter(strQueryCombined, connInd)Dim commandSQL As New SqlCommand("GetAssetList2", connStringSQL)commandSQL.CommandType = Data.CommandType.StoredProcedureDim resultDS As New Data.DataSet()Dim resultDA As New SqlDataAdapter()'// Fill the dataset with valuescommandInd.Fill(resultDS)'// Get the XML values of the dataset to send to SQL server and run a new query...'// Return the number of resultsresultCount.Text = source.Count.ToStringresults.DataSource = sourceresults.DataBind()'// Record the searchcommandSQL = New SqlCommand("RecordSearch", connStringSQL)commandSQL.Parameters.Clear()commandSQL.Parameters.Add("@userName", Data.SqlDbType.VarChar, 50).Value = authUser.Text()commandSQL.Parameters.Add("@createdDateTime", Data.SqlDbType.DateTime).Value = DateTime.Now()commandSQL.Parameters.Add("@numRows", Data.SqlDbType.Int, 1000).Value = resultCount.TextcommandSQL.Parameters.Add("@searchString", Data.SqlDbType.VarChar, 1000).Value = searchText.TextconnStringSQL.Open()commandSQL.ExecuteNonQuery()connStringSQL.Close() The stored procedure looks like this:Use GTGAssetsDROP PROC dbo.RecordSearch;--New ProcedureGOCREATE PROC dbo.RecordSearch(@userName varchar(50),@createdDateTime DateTime,@numRows varchar(1000),@searchString varchar(1000))ASBEGINSET NOCOUNT ONINSERT INTO SearchLog (SearchString, CreatedByUser, CreatedDTTM, RowsReturned) VALUES (@searchString, @userName, @createdDateTime, @numRows)ENDGOAny ideas as to why this error is appearing? Incorrect syntax near 'RecordSearch'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'RecordSearch'.Source Error: Line 169: commandSQL.Parameters.Add("@searchString", Data.SqlDbType.VarChar, 1000).Value = searchText.Text
Line 170: connStringSQL.Open()
Line 171: commandSQL.ExecuteNonQuery()
Line 172: connStringSQL.Close()
Line 173: End IfMany thanks!James
View 5 Replies
View Related