Hi All,I have a report ASP.NET page that allow users to run a report by clicking a buttion to call a store procedure to generate the report, however, the store procedure is taking a few minutes to return the data, thus I got the 'timeout' error message on my page. How do I extend the time on my page?Thanks
View 3 RepliesHi, I have been searching for an answer to allow me to execute xp_cmdshell from withing store prcedure by calling the store procedure from an aspx via click on a button. This is what I found from my reserch but was not able to know where to set the rights and what each of them means:
"To run xp_cmdshell for a non-system administrator user, you must grant the following rights.
MSSQLServer and SQLServerAgent Services
Act as part of the Operating System.
Increase Quotas.
Replace a process level token.
Log on as a batch job."
The above quote was from the following link:
http://support.microsoft.com/default.aspx?scid=kb;en-us;264155
The bottom line is to be able to call a store procedure from an aspx page to execute the code which contain xp_cmdshell command, an example of such command is like:
------------------------------------------------------------
EXEC master..xp_cmdshell 'dir d:BT_importDatasales_option_price_report.csv'
Thanks for your help
ehx5
I am encountering an issue which is effecting our production environment, none of our sql jobs are now saving any kind of job history, nor are the status of the jobs being saved.
I have run a profiler trace and it seems as if procedure : sp_sqlagent_log_jobhistory is not being called during the execution of any of our jobs
Has anyone else encountered anything similar?
Im using a SqlDataSource control. Ive got my "selectcommand" set to the procedure name, the "selectcommandtype" set to "storedprocedure"What am i doing wrong ? Ive got a Sql 2005 trace window open and NO sql statements are coming through "ds" runat="server" ConnectionString="<%$ ConnectionStrings:myConnectionString %>" SelectCommand="my_proc_name" SelectCommandType="StoredProcedure">
"txtF1" Name="param1" Type="String" />
"txtF2" Name="param2" Type="String" />
"" FormField="txtF3" Name="param3" Type="String" />
"" FormField="txtF4" Name="param4" Type="String" />
Hi,
I have implemented the code from the 'Recycling Conversations' post that Remus Resanu has posted. For some reason my sender activation procedure never gets called. I thought it was working at one point but now can not get it to work. Messages are being sent correctly from sender to receiver but the c_audit_send_queue_activation procedure never gets called.
According to my code that calls 'begin conversation timer(@dlg) timeout=30;' I would think that after 30 seconds that my activated procedure would get called with a DialogTimer message but it does not. Nor does the activation procedure on the sender get called when I manually end conversations on the receiver side.
Thanks in advance for any help.
Here is my send procedure:
Code Snippet
create procedure [dbo].[c_audit_p_send_message]
@msg nvarchar(max)
as
begin
if @msg is not null
begin
begin try
set nocount on;
declare @dlg uniqueidentifier
declare @counter int;
declare @error int;
declare @errid bigint, @dbname nvarchar(128)
set @counter = 1;
begin transaction;
while(1=1)
begin
select @dlg = dialog_id from dbo.audit_dialog with(holdlock) where audit_dialog_id_X = @@spid
if @dlg is null
begin
begin dialog conversation @dlg
from service [tcp://SFT3DEVSQL01:4022/TyMetrix360Audit/DataSender]
to service '//TyMetrix360Audit/DataWriter','7A9690F7-11A5-4ABB-ACBA-EECC1A58ACB7'
on contract [//TyMetrix360Audit/Contract] with encryption = off;
begin conversation timer(@dlg) timeout=30;
insert into dbo.audit_dialog(audit_dialog_id_X, dialog_id) values(@@spid, @dlg)
end;
send on conversation @dlg message type [//TyMetrix360Audit/Message] (@msg)
set @error = @@ERROR;
if @error = 0
begin
break;
end
set @counter = @counter + 1;
if @counter > 10
begin
raiserror(N'Failed to SEND on a converstation for more than 10 times.',16,1) with log;
insert into audit_error (error_procedure, error_line, error_number, error_message, error_severity, error_state, audited_data)
select error_procedure(), error_line(), error_number(), error_message(), error_severity(), error_state(), @msg
break;
end
delete from dbo.audit_dialog where dialog_id = @dlg;
set @dlg = null;
end
commit transaction;
end try
begin catch
insert into audit_error (error_procedure, error_line, error_number, error_message, error_severity, error_state, audited_data)
select error_procedure(), error_line(), error_number(), error_message(), error_severity(), error_state(), @msg
select @errid = scope_identity(), @dbname = db_name()
raiserror (N'Error while sending Service Broker message to TyMetrix360Audit. Error info can be found in ''%s.dbo.AUDIT_ERROR'' table with id: %I64d', 16, 1, @dbname, @errid) with log;
end catch
end
end
GO
here is the activatation procedure:
Code Snippet
create procedure [dbo].[c_audit_p_send_queue_activation]
as
begin
declare @dlg uniqueidentifier
declare @msgtype sysname
declare @msg varbinary(max)
begin transaction;
receive top(1) @dlg = conversation_handle, @msgtype = message_type_name, @msg = message_body from dbo.TyMetrix360AuditQueue
if @dlg is not null
begin
delete from audit_dialog where dialog_id = @dlg
if @msgtype = N'http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer'
begin
send on conversation @dlg
message type [//TyMetrix360Audit/Message/EndConversation] ('');
end
else if @msgtype= N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
begin
end conversation @dlg
end
else if @msgtype = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
begin
end conversation @dlg
declare @error int;
declare @description nvarchar(4000);
with xmlnamespaces ('http://schemas.microsoft.com/SQL/ServiceBroker/Error' as ssb)
select @error = cast(@msg as xml).value('(//ssb:Error/ssb:Code)[1]', 'int'), @description = cast(@msg as xml).value('(//ssb:Error/ssb:Description)[1]', 'nvarchar(4000)')
raiserror(N'Received error Code:%i Description:''%s''', 16, 1, @error, @description) with log;
insert into audit_error (error_procedure, error_line, error_number, error_message, error_severity, error_state, audited_data)
select error_procedure(), error_line(), error_number(), error_message(), error_severity(), error_state(), @msg
end
end
commit transaction;
end
and here is the code that creates the service broker on the sender:
Code Snippet
if exists (select * from sys.routes where name = 'TyMetrix360Route') drop route TyMetrix360Route
if exists (select * from sys.services where name = N'tcp://SFT3DEVSQL01:4022/TyMetrix360Audit/DataSender') drop service [tcp://SFT3DEVSQL01:4022/TyMetrix360Audit/DataSender]
if exists (select * from sys.service_queues where name = N'TyMetrix360AuditQueue') drop queue TyMetrix360AuditQueue
if exists (select * from sys.service_contracts where name = N'//TyMetrix360Audit/Contract') drop contract [//TyMetrix360Audit/Contract]
if exists (select * from sys.service_message_types where name = N'//TyMetrix360Audit/Message') drop message type [//TyMetrix360Audit/Message]
if exists (select * from sys.service_message_types where name = N'//TyMetrix360Audit/Message/Blob') drop message type [//TyMetrix360Audit/Message/Blob]
if exists (select * from sys.service_message_types where name = N'//TyMetrix360Audit/Message/EndConversation') drop message type [//TyMetrix360Audit/Message/EndConversation]
GO
create route TyMetrix360Route authorization dbo with
service_name='//TyMetrix360Audit/DataWriter',
broker_instance='7A9690F7-11A5-4ABB-ACBA-EECC1A58ACB7',
address='TCP://SFT3DEVSQL01:4022'
create message type [//TyMetrix360Audit/Message] validation=none;
create message type [//TyMetrix360Audit/Message/Blob] validation=none;
create message type [//TyMetrix360Audit/Message/EndConversation] validation=none;
create contract [//TyMetrix360Audit/Contract]([//TyMetrix360Audit/Message] sent by initiator, [//TyMetrix360Audit/Message/Blob] sent by initiator, [//TyMetrix360Audit/Message/EndConversation] sent by initiator);
create queue dbo.TyMetrix360AuditQueue
alter queue dbo.TyMetrix360AuditQueue with activation(status=on,max_queue_readers=1,procedure_name=[c_audit_p_send_queue_activation],execute as owner);
create service [tcp://SFT3DEVSQL01:4022/TyMetrix360Audit/DataSender] authorization dbo on queue dbo.TyMetrix360AuditQueue
grant send on service::[tcp://SFT3DEVSQL01:4022/TyMetrix360Audit/DataSender] to public
GO
OK, I have been raking my brains with this and no solution yet. I simply want to update a field in a table given the record Id. When I try the SQL in standalone (no sp) it works and the field gets updated. When I do it by executing the stored procedure from a query window in the Express 2005 manager it works well too. When I use the stored procedure from C# then it does not work:
1. ExecuteNonQuery() always returns -1 2. When retrieving the @RETURN_VALUE parameter I get -2, meaning that the SP did not find a matching record.
So, with #1 there is definitely something wrong as I would expect ExecuteNonQuery to return something meaningful and with #2 definitely strange as I am able to execute the same SQL code with those parameters from the manager and get the expected results.
Here is my code (some parts left out for brevity):1 int result = 0;
2 if (!String.IsNullOrEmpty(icaoCode))
3 {
4 icaoCode = icaoCode.Trim().ToUpper();
5 try
6 {
7 SqlCommand cmd = new SqlCommand(storedProcedureName);(StoredProcedure.ChangeAirportName);
8 cmd.Parameters.Add("@Icao", SqlDbType.Char, 4).Value = newName;
9 cmd.Parameters.Add("@AirportName", SqlDbType.NVarChar, 50).Value = (String.IsNullOrEmpty(newName) ? null : newName);
10 cmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
11 cmd.Connection = mConnection; // connection has been opened already, not shown here
12 cmd.CommandType = CommandType.StoredProcedure;
13 int retval = cmd.ExecuteNonQuery(); // returns -1 somehow even when RETURN n is != -1
14 result = (int)cmd.Parameters["@RETURN_VALUE"].Value;
15
16 }
17 catch (Exception ex)
18 {
19 result = -1;
20 }
21 }
And this is the stored procedure invoked by the code above:1 ALTER PROCEDURE [dbo].[ChangeAirfieldName]
2 -- Add the parameters for the stored procedure here
3 @Id bigint = null,-- Airport Id, OR
4 @Icao char(4) = null,-- ICAO code
5 @AirportName nvarchar(50)
6 AS
7 BEGIN
8 -- SET NOCOUNT ON added to prevent extra result sets from
9 -- interfering with SELECT statements.
10 SET NOCOUNT ON;
11
12 -- Parameter checking
13 IF @Id IS NULL AND @Icao IS NULL
14 BEGIN
15 RETURN -1;-- Did not specify which record to change
16 END
17 -- Get Id if not known given the ICAO code
18 IF @Id IS NULL
19 BEGIN
20 SET @Id = (SELECT [Id] FROM [dbo].[Airports] WHERE [Icao] = @Icao);
21 --PRINT @id
22 IF @Id IS NULL
23 BEGIN
24 RETURN -2;-- No airport found with that ICAO Id
25 END
26 END
27 -- Update record
28 UPDATE [dbo].[Airfields] SET [Name] = @AirportName WHERE [Id] = @Id;
29 RETURN @@ROWCOUNT
30 END
As I said when I execute standalone UPDATE works fine, but when approaching it via C# it returns -2 (did not find Id).
HiOur SQL server has a lot of stored procedures and we want to get somecleaning up to be done. We want to delete the ones that have been notrun for like 2-3 months. How exactly will i find out which ones todelete. Enterprise manager only seesm to give the "Create Date"How exactly can I find the last called date ! I guess you could write aquery for that ! but how ???P.S I dont want to run a trace for 1 months and see what storedprocedures are not being used.
View 7 Replies View RelatedI have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete]
@QQ_YYYY char(7),
@YYYYQQ char(8)
AS
begin
SET NOCOUNT ON;
select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
[Code] ....
Hi all,i have a problem and couldnt find anything even close to it. please help me, here is the description of what i m trying to accomplish:I have a trigger that is generating a column value and calling a stored procedure after the value is generated. And this stored procedure is setting this generated value as an output parameter. But my problem is:my asp.net page is only sending an insert parameter to the table with the trigger, trigger is running some code depending on the insert parameter and calling this other stored procedure internally. So basically i m not calling this last stored procedure that sets the output parameter within my web form. How can i get the output parameter in my webform? Everthing is working now, whenever an insert hits the table trigger runs and generates this value and called stored procedure sets it as an output parameter. I can get the output parameter with no problem in query analyzer, so the logic has no problem but i have no idea how this generated output parameter can be passed in my webform since its not initiated there.any help will greately be appreciated, i m sure asp.net and sql server 2000 is powerful and flexible enough to accomplish this but how??-shane
View 8 Replies View RelatedWhen running a stored procedure, how can i retrieve the warnings that are issued within the stored procedure?
the code used is below,
the jdbc is connecting fine, it is running the stored procedure, but when an error is raised in the stored procedure, it is not coming back into s.getwarnings()
warning raised in stored proc with
Code Block
RAISERROR ('Error', 16, 1)with nowait;
there are no results for the stored procedure, I am just wanting to get the warnings
Code Block
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection("jdbc:sqlserver://localh...........);
CallableStatement s = con.prepareCall("{call procedure_name}");
s.execute();
//running in seprate thread
SQLWarning warn = s.getWarnings();
while (m.running) {
if(warn == null) {
warn = s.getWarnings();
}
safeOut(warn);
if(warn != null)
warn = warn.getNextWarning();
m.wait(100);
}the code is not complete, but should show what is happening
it is continually outputting null for the warning, though the strored proc is definately raising errors, which is proven by running it in a query window in sql server.
it is retreiving warning if the statement is a raiseerror instead of a call to the proc
Code Block
CallableStatement s = con.prepareCall("RAISERROR ('Error', 1, 1)with nowait;");
this thread is quite related, but doesnt offer a working solution
Getting messages sent while JDBC Driver calls stored procedure
any help much appreciated,
thankyou
Simon
I need help debugging a CLR stored procedure that is being called from an SSIS package. I can debug the procedure itself from within Visual Studio by using "Step into stored procedure" from Server Explorer, but really need to debug it as it is being called from SSIS.
View 4 Replies View RelatedI have implemented an SSAS stored procedure for dynamic security and I call this stored procedure to obtain the allowed set filter. To my supprise, the stored procedure is being called repeatedly many times (more than 10) upon establishing the user session. Why is this happening?
View 20 Replies View RelatedI seem to be able to see where a procedure is being recompiled, but not the actual statement that was executing the procedure.
Note, with 2008 there is a DMV called dm_exec_procedure_stats , which is not present in 2005
USE YourDb;
SELECT qt.[text] AS [SP Name],
qs.last_execution_time,
qs.execution_count AS [Execution Count]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = DB_ID()
AND objectid = OBJECT_ID('YourProc')
The above shows results that include the CREATE PROCEDURE statements for the procedure in question, but this only indicates that the procedure was being recompiled, not necessarily that it was being executed?
I need help debugging a CLR stored procedure that is being called from an SSIS package. I can debug the procedure itself from within Visual Studio by using "Step into stored procedure" from Server Explorer, but really need to debug it as it is being called from SSIS.
I have a sub that passes values from my form to my stored procedure. The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page. Here's where I'm stuck: Public Sub InsertOrder() Conn.Open() cmd = New SqlCommand("Add_NewOrder", Conn) cmd.CommandType = CommandType.StoredProcedure ' pass customer info to stored proc cmd.Parameters.Add("@FirstName", txtFName.Text) cmd.Parameters.Add("@LastName", txtLName.Text) cmd.Parameters.Add("@AddressLine1", txtStreet.Text) cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue) cmd.Parameters.Add("@Zip", intZip.Text) cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text) cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text) cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text) cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text) cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text) ' pass order info to stored proc cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue) cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue) cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue) 'Session.Add("FirstName", txtFName.Text) cmd.ExecuteNonQuery() cmd = New SqlCommand("Add_EntreeItems", Conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc) <------------------------- Dim li As ListItem Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar) For Each li In chbxl_entrees.Items If li.Selected Then p.Value = li.Value cmd.ExecuteNonQuery() End If Next Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder) and pass that to my second stored procedure (Add_EntreeItems)
View 9 Replies View RelatedHi,
I'm trying to call a Stored Procedure from a Inline Table-Valued Function. Is it possible? If so can someone please tell me how? And also I would like to call this function from a view. Can it be possible? Any help is highly appreciated. Thanks
I have a report based on our product names that consists of two parts.Both insert data into a temporary table.1. A single grouped set of results based on all products2. Multiple tables based on individual product names.I am getting data by calling the same stored procedure multipletimes... for the single set of data I use "product like '%'"To get the data for individual products, I am using a cursor to parsethe product list.It's working great except that I have no idea how to identify theresults short of including a column with the product name. While thatis fine, I'm wondering if there is something that is like a header ortitle that I could insert prior to generating the data that would looka little tighter.Thanks in advance-DanielleJoin Bytes!
View 3 Replies View Related I know I can call store procedure from store procedure but i want to take the value that the store procedure returned and to use it:
I want to create a table and to insert the result of the store procedure to it.
This is the code: Pay attention to the underlined sentence!
ALTER PROCEDURE [dbo].[test]
AS
BEGIN
SET NOCOUNT ON;
DROP TABLE tbl1
CREATE TABLE tbl1 (first_name int ,last_name nvarchar(10) )
INSERT INTO tbl1 (first_name,last_name)
VALUES (exec total_cash '8/12/2006 12:00:00 AM' '8/12/2006 12:00:00 AM' 'gilad' ,'cohen')
END
PLEASE HELP!!!! and God will repay you in kind!
Thanks!
I have a stored procedure 'ChangeUser' in which there is a call to another stored procedure 'LogChange'. The transaction is started in 'ChangeUser'. and the last statement in the transaction is 'EXEC LogChange @p1, @p2'. My questions is if it would be correct to check in 'LogChange' the following about this transaction: 'IF @@trancount >0 BEGIN Rollback tran' END Else BEGIN Commit END.
Any help on this would be appreciated.
My store Procedure is not save in Strore Procedure folder at the time of saving it give me option to save in Project folder and file name default is SQLQuery6.sql when i save it after saving when i run my store procedure
exec [dbo].[SP_GetOrdersForCustomer] 'ALFKI'
I am getting below error :
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'dbo.SP_GetOrdersForCustomer'.
Hello,
I have to transfer lets say once a day arround 30 000 records from one table into another. The query uses cursor and should check if the record exists to make update, otherwise to make insert.
On this ammount of data, in visual studio I get Connection Timeout. I even unselected "Cancel long running query" in Tools/Options/Database tools, and I still get timeout. When using Sql Server management studio it works and it takes long time. I know that this query executes long time, but it will run in the middle of the night and nobody will bother.
I could have transfered this data in the application and then return it to the database one by one, but why unnecesary transport?
How do I get the stored procedure running without this timeout?
Thanks
I am having a problem with a long running stored proceudure timing out in a web page.
Details:
In ASP page - run stored procedure
After approx 30 seconds, I get the error msg:
Microsoft OLE DB Provider for ODBC Drivers error '80040e31'
[Microsoft][ODBC SQL Server Driver]Timeout expired
xxx.asp, line 284
(this is where the exec statement is for the stored procedure)
The procuedure takes approx 2:20 to run directly in Query Analyzer. Any ideas on how to ensure that the page waits for the entire query to finish and return results? I suspect that some fine tuning can be done on the procedure but it will still take longer than the 30 seconds that the browser is giving it to run...
Notes - query works.
takes 2:20 to complete.
ASP - Server Timeout set to 600 seconds for the ASP page.
SQL Server timeout set to 0 (unlimited)
SQL 7 SP1 applied
Thanks,
Dave
I have a stored procedure, one varchar(20) input parameter and 6 varchar(100) output parameters....
I have set some prints in it to see where it is getting to before the timeout, but it doesn't get to the first print right after the variables....
Seems like if I change the 20 character input string it runs through just fine....
Very weird, happens both in a VB6 Program and in SQL Query Analyzer...
Since RDMBS and its language SQL is set-based would it make more senseto call a given stored process "Stored Sets" instead of currenttheorically misleading Stored Procedure, as a measure to prodprogrammers to think along the line of sets instead of procedure?
View 4 Replies View RelatedI am running a large insert in a stored procedure, and it is timing out after 30 seconds (which I take to be the default). Can anyone tell me how to change the timeout from inside the stored procedure?
Thanks.
im testing an application change that should handle a timeout on a stored procedure being called from the application. thing is, the timeout that we experience in production that led to this fix is random. so is there some way for me to setup a test stored procedure or some way to call the SP so that i can test a timeout scenario?
im using MFC and the CDatabase::ExecuteSQL method to call this SP if you were wondering at all.
this app is running locally on the server that has an instance of SQL Server Express 2k5 on it. server is running win 2k3.
Guys,
In simple terms, our system is as such: We have a website. As someone clicks a button on the website, a stored procedure is executed against our database.
Every single day, between 12:15AM and 12:45AM we have a few stored procedures timing out, with the following message, for example:
2007-04-10 00:37:03,268 [3632] ERROR Service - caught exception Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception, Boolean breakConnection)
I checked and saw that although there are jobs running at that time, all of these jobs are running periodically (e.g. every 30 minutes) and would cause timeouts at other times as well, if they were to blame. Other jobs are running at far away times and checking their history I know that their duraion in no way intersects the time-out times.
I also ran profiler during peak hours and know that no stored procedure of ours has a duration anywhere near 30 seconds (which is the currently set timeout period, although all of our sps run within milliseconds).
I am really puzzled as to what exactly is causing these timeouts. Would anyone suggest any approach to identify the problem. For example, I thought about running profiler (server side tracing) between 12AM and 1AM, but am not sure which counters are best to capture. Any suggestion on this?
Thanks a lot!
I Have a problem When I execute a stored procedure from query analyzer
(Exec storedname @parameter1='', @Parameter2='') it take 7 min. and I stop running
If I copy stored procedure , past it in Query analyzer and declare parameters
it take 3 sec.
Is there a way to force a stored procedure timeout?
I need this to happen to test an error handling method.
Hi,
I have written a stored procedure that i then execute in a loop within a wrapper procedure:
WHILE somecondition
BEGIN
EXEC dbo.PROC1
END
If i run the procedure manually i can run it time after time with no error. However if i execute the procedure above i get a timeout error after about 2 or 3 loops.
How can i avoid this?
I have been reading everywhere and i dont seem to be able to find a solution...
Hi,
I'm running a CLR stored procedure through my web using table adapters as follows:
res = BLL.contractRateAdviceAdapter.AutoGenCRA() 'with BLL being the business logic layer that hooks into the DAL containing the table adapters.
The AutoGen stored procedure runs fine when executed directly from within Management Studio, but times out after 30 seconds when run from my application. It's quite a complex stored procedure and will often take longer than 30 seconds to complete.
The stored procedure contains a number of queries and updates which all run as a single transaction. The transaction is defined as follows:
----------------------------------------------------------------------------------------------------------------------
options.IsolationLevel = Transactions.IsolationLevel.ReadUncommittedoptions.Timeout = New TimeSpan(1, 0, 0)
Using scope As New TransactionScope(TransactionScopeOption.Required, options)
'Once we've opened this connection, we need to pass it through to just about every
'function so it can be used throughout. Opening and closing the same connection doesn't seem to work
'within a single transactionUsing conn As New SqlConnection("Context Connection=true")
conn.Open()
ProcessEffectedCRAs(dtTableInfo, arDateList, conn)
scope.Complete()
End Using
End Using
----------------------------------------------------------------------------------------------------------------------
As I said, the code encompassed within this transaction performs a number of database table operations, using the one connection. Each of these operations uses it's own instance of SQLCommand. For example:
----------------------------------------------------------------------------------------------------------------------Dim dt As DataTable
Dim strSQL As StringDim cmd As New SqlCommand
cmd.Connection = conn
cmd.CommandType = CommandType.Text
cmd.CommandTimeout = 0Dim rdr As SqlDataReaderstrSQL = "SELECT * FROM " & Table
cmd.CommandText = strSQL
rdr = cmd.ExecuteReader
SqlContext.Pipe.Send(rdr)
rdr.Close()
----------------------------------------------------------------------------------------------------------------------
Each instance of SQLCommand throughout the stored procedure specifies cmd.CommandTimeout = 0, which is supposed to be endless. And the fact that the stored procedure is successful when run directly from Management studio indicates to me that the stored procedure itself is fine. I also know from output messages that there is no issues with the database connection.
I've set the ASP.Net configuration properties in IIS accordingly.
Are there any other settings that I need to change?
Can I set a timeout property when I'm calling the stored procedure in the first place?
Any advice would be appreciated.
Thanks
I have a stored procedure when query a big table about 500,000 records. When I run the stored procedure in the query analyzer, it is very fast and it only takes 2~3 seconds. However, when my aspx page try to call this stored-procedure with a Command's ExecuteReader method like bellow:
SqlDataReader myReader = myCommand.ExecuteReader();
I always get timeout expired exception. I try to set the connection timeout and command timeout to 100 seconds. The exception is gone but the average execution time is about 25 seconds! While the stored-procedure only takes about 2~3 seconds in query analyzer with the same parameter.
What could be the problem?? I tried to figure this out for a couple days but still no clue.
Thanks.
When I execute a long running procedure, I get timeout errors when other users try to execute other procedures with UPDATE or INSERT statements.
I suspect that the other procedures are trying to execute DML statements on tables that are locked by the long running procedure.
I have a sharred trigger on all my tables that creates and updates records in tables AuditLogDetails and AuditLogParent for keeping a log of modifications. I suspect that tables AuditoLogDetails and AuditLogParent are locked by the long running procedure.
How can I change the LOCKING behavior of the long running procedure to fix the time out errors that I get?
The long running procedure is displayed below.
ALTER PROCEDURE [dbo].[spPostPresenceToHistory2]
@PostDate DateTime,
@Department Int,
@Division Int,
@Testing Bit = 0,
@XDoc xml OUTPUT,
@XDoc2 xml OUTPUT,
@ModifierID varchar(20),
@Comment varchar(200)
AS
BEGIN
BEGIN TRANSACTION
DECLARE @PostCount Int,@PreCount Int,@DiffCount Int
IF @Testing=1
BEGIN
PRINT 'DELETE FROM History2_Presence'
EXEC sp_SetPostingProperties 'History2_Presence',@ModifierID,@Comment
SELECT @PreCount=COUNT(*) FROM History2_Presence
IF EXISTS(SELECT E.ID FROM History2_Personel E WHERE E.PostDate=@PostDate)
BEGIN
DELETE FROM History2_Presence FROM History2_Presence H
INNER JOIN History2_Personel Ps ON H.Personel_ID=Ps.ID AND Ps.PostDate=@PostDate
WHERE Ps.Category_Department_ID=@Department AND Ps.Category_Division_ID=@Division AND H.Date_de_Presence=@PostDate
AND EXISTS (SELECT P.ID FROM Presence P
WHERE (P.Date_de_Presence=@PostDate AND P.Personel_ID=H.Personel_ID AND P.Travaille_de_Jour=H.Travaille_de_Jour) OR (P.ID=H.ID))
END
ELSE
BEGIN
DELETE FROM History2_Presence FROM History2_Presence H
INNER JOIN Personel As Ps ON H.Personel_ID=Ps.ID
WHERE Ps.Category_Department_ID=@Department AND Ps.Category_Division_ID=@Division AND H.Date_de_Presence=@PostDate
AND EXISTS (SELECT P.ID FROM Presence P
WHERE (P.Date_de_Presence=@PostDate AND P.Personel_ID=H.Personel_ID AND P.Travaille_de_Jour=H.Travaille_de_Jour) OR (P.ID=H.ID))
END
SELECT @PostCount=COUNT(*) FROM History2_Presence
IF @PreCount<>@PostCount
BEGIN
SET @DiffCount = @PreCount-@PostCount
SET @XDoc2.modify('
insert <Table Name="History2_Presence" RecordDeleted="{ sql:variable("@DiffCount") }"/> as last into /Deleted_Records[1]
')
END
END
PRINT 'INSERT INTO History2_Presence'
EXEC sp_SetPostingProperties 'History2_Presence',@ModifierID,@Comment
SELECT @PreCount=COUNT(*) FROM History2_Presence
INSERT INTO [dbo].[History2_Presence]
([ID]
,[User_ID]
,[Personel_ID]
,[Date_de_Presence]
,[Category_Motif_ID]
,[DateEntre]
,[Category_TypeDePresence_ID]
,[Travaille_de_Jour]
,[Heur_Supplementaire_Travaille]
,[prime_transport]
,[Tarif]
,[Jour_Travaille]
,[Montant_Supplementaire_Par_Heur]
,[Salair_par_Jour]
,[Salair_Minimum]
,[IsAutomaticRec])
SELECT [P].[ID]
,[P].[User_ID]
,[P].[Personel_ID]
,[P].[Date_de_Presence]
,[P].[Category_Motif_ID]
,[P].[DateEntre]
,[P].[Category_TypeDePresence_ID]
,[P].[Travaille_de_Jour]
,[P].[Heur_Supplementaire_Travaille]
,[P].[prime_transport]
,[P].[Tarif]
,[P].[Jour_Travaille]
,[P].[Montant_Supplementaire_Par_Heur]
,[P].[Salair_par_Jour]
,[P].[Salair_Minimum]
,[P].[IsAutomaticRec]
FROM [dbo].[Presence] AS P
INNER JOIN Personel As Ps ON P.Personel_ID=Ps.ID
WHERE P.Date_de_Presence=@PostDate AND Ps.Category_Department_ID=@Department AND Ps.Category_Division_ID=@Division
AND NOT EXISTS
(SELECT HP.ID FROM History2_Presence HP
WHERE (HP.Date_de_Presence=@PostDate AND HP.Personel_ID=P.Personel_ID AND HP.Travaille_de_Jour=P.Travaille_de_Jour) OR (HP.ID=P.ID))
SELECT @PostCount=COUNT(*) FROM History2_Presence
IF @PreCount<>@PostCount
BEGIN
SET @DiffCount = @PostCount-@PreCount
SET @xdoc.modify('
insert <Table Name="History2_Presence" RecordAdded="{ sql:variable("@DiffCount") }"/> as last into /Inserted_Records[1]
')
END
IF @Testing=0
BEGIN
PRINT 'DELETE FROM Presence'
EXEC sp_SetPostingProperties 'Presence',@ModifierID,@Comment
SELECT @PreCount=COUNT(*) FROM Presence
DELETE FROM Presence FROM Presence P
INNER JOIN Personel As Ps ON P.Personel_ID=Ps.ID
WHERE P.Date_de_Presence=@PostDate AND Ps.Category_Department_ID=@Department AND Ps.Category_Division_ID=@Division
AND EXISTS
(SELECT HP.ID FROM History2_Presence HP
WHERE (HP.Date_de_Presence=@PostDate AND HP.Personel_ID=P.Personel_ID AND HP.Travaille_de_Jour=P.Travaille_de_Jour) OR (HP.ID=P.ID))
SELECT @PostCount=COUNT(*) FROM Presence
IF @PreCount<>@PostCount
BEGIN
SET @DiffCount = @PreCount-@PostCount
SET @XDoc2.modify('
insert <Table Name="Presence" RecordDeleted="{ sql:variable("@DiffCount") }"/> as last into /Deleted_Records[1]
')
END
END
COMMIT TRANSACTION
END