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);
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?
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.
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 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.
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.
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?
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.
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?
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
My SQL Server 2005 SP4 on Windows 2008 R2 is flooded with the below errors:-
Date  10/25/2011 10:55:46 AM Log  SQL Server (Current - 10/25/2011 10:55:00 AM) Source  spid Message Event Tracing for Windows failed to send an event. Send failures with the same error code may not be reported in the future. Error ID: 0, Event class ID: 54, Cause: (null).  Is there a way I can trace it how it is coming? When I check input buffer for these ids, it looks like it is tracing everything. All the general application DMLs are coming in these spids.
I have been testing with the WMI Event Watcher Task, so that I can identify a change to a file. The WQL is thus:
SELECT * FROM __InstanceModificationEvent within 30 WHERE targetinstance isa 'CIM_DataFile' AND targetinstance.name = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\AdventureWorks.bak'
This polls every 30 secs and in the SSIS Event (ActionAtEvent in the WMI Task is set to fire the SSIS Event) I have a simple script task that runs a message box).
My understanding is that the event polls every 30 s and if there is a change on the AdventureWorks.bak file then the event is triggered and the script task will run producing the message. However, when I run the package the message is occurring every 30s, meaning the event is continually firing even though there has been NO change to the AdventureWorks.bak file.
Am I correct in my understanding of how this should work and if so why is the event firing when it should not ?
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
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?
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
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.
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.
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?
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.
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;
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?
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.
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?
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?
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!
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
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()--------
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