Stored Procedure Timeout Mystery ...

Apr 11, 2007

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!

View 4 Replies


ADVERTISEMENT

Stored Procedure Performance Mystery

Jul 23, 2005

My application fetches a batch of data through a web service and writes 1000entities per batch to a SQL Server 2000 database. There are 4 tables inevery batch. There are the following number of SQL commands executed peraverage of every batch;Table #1: always 1Table #2: 5Table #3: 5Table #4: 3The problem is that the performance slows down for every batch. Below is anexcerpt from my log file;2004-12-15 12:00:01 Starting job... (RAM usage: 6,38 mb)2004-12-15 12:00:39 data fetch time: 00:00:28 (RAM usage: 23,04 mb)2004-12-15 12:00:39 Total data fetch time: 00:00:37 (RAM usage: 23,04 mb)2004-12-15 12:00:39 Inserting/updating 1000 entities...2004-12-15 12:01:20 Write SQL time: 00:00:402004-12-15 12:01:49 data fetch time: 00:00:24 (RAM usage: 26,87 mb)2004-12-15 12:01:49 Total data fetch time: 00:00:29 (RAM usage: 26,87 mb)2004-12-15 12:01:49 Inserting/updating 1000 entities...2004-12-15 12:02:59 Write SQL time: 00:01:102004-12-15 12:04:06 data fetch time: 00:00:29 (RAM usage: 27,48 mb)2004-12-15 12:04:06 Total data fetch time: 00:01:06 (RAM usage: 27,48 mb)2004-12-15 12:04:06 Inserting/updating 1000 entities...2004-12-15 12:05:30 Write SQL time: 00:01:232004-12-15 12:06:05 data fetch time: 00:00:31 (RAM usage: 27,03 mb)2004-12-15 12:06:05 Total data fetch time: 00:00:35 (RAM usage: 27,03 mb)2004-12-15 12:06:05 Inserting/updating 1000 entities...2004-12-15 12:07:37 Write SQL time: 00:01:32As one can see, the Write SQL time increases per every batch.I would like this time to stay around one minute per batch.There are one trigger per table. There is one parent table which has aprimary-foreign key relationship to the three sub tables.I have 2% automatic file size growth set on both the data and the log file.Thank you in advance to the guru which helps me out with this!

View 5 Replies View Related

Stored Procedure Timeout

Feb 18, 2008

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

View 5 Replies View Related

Stored Procedure Timeout In ASP

Jun 12, 2001

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

View 3 Replies View Related

Stored Procedure TimeOut

Aug 23, 2006

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...

View 5 Replies View Related

Setting Timeout In A Stored Procedure

Oct 17, 1999

I 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.

View 1 Replies View Related

Forcing A Timeout On A Stored Procedure

May 2, 2008

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.

View 4 Replies View Related

Timeout While Exec Stored Procedure

Mar 12, 2008

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.

View 3 Replies View Related

Force Stored Procedure To Timeout

Sep 29, 2010

Is there a way to force a stored procedure timeout?

I need this to happen to test an error handling method.

View 4 Replies View Related

Timeout Exception When Running Stored Procedure

Feb 4, 2008

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

View 2 Replies View Related

Timeout Expired When Run A Stored Procedure From Aspx Page.

Mar 2, 2004

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.

View 8 Replies View Related

Timeout Expired Occurs In The Middle Of A Stored Procedure

Sep 11, 2006

greets all,

i have a express set up and running a table filled with a few batches of records. Each batch has approx. 20k records. I have a program that inserts a new batch into the table. afterwards, it makes a call to a stored procedure. this SP compares the last batch with the latest batch and marks the records as additions, updated, or deleted records (which will be used by the program). the problem im experiencing is that the comparison algorithm is getting the "Timeout expired" error returning back to my program. the SP seems to take roughly 1-3 minutes to make the comparisons. i tried setting the execution timeout to 0 (no timeout) in Options->Query Execution->SQL Server->General but i still encounter the problem. am i changing the wrong option for this or is there something else i can try? im going to try to reproduce this database in SQL Server 2000 so i can use the profiler, but im running into problems exporting the database to an older version (im not familiar with bcp, which im looking into). any advice until then?

View 7 Replies View Related

Stored Procedure Timeout Expired Exception On Windows Forms (C#)

Oct 26, 2007

Hello. I've read some similar topics (most of them on this forum), but none of them helped.

I have a stored procedure which runs fine on SQL Server (in 0 ms). The problem occurs when i try to run it under a WindowsForm application (C#).

The following code



Code Block
SqlCommand cmdUpdate;
SqlConnection sqlCon = GetConnection();
cmdUpdate = new SqlCommand("StoredProcedure_Name", sqlCon);
cmdUpdate.CommandType = CommandType.StoredProcedure;
sqlCon.Open();
cmdUpdate.CommandTimeout = 300;
cmdUpdate.ExecuteNonQuery();



produces the exception: "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." on the ExecuteNonQuery() line.

It's not a problem with the CommandTimeout or the ConnectionTimeout (from within the connection string), they are both set to 300 (and even when set to 0, the problem is the same).

It's very strange, because only two stored procedures don't work, the others work without any problems and under SQL Server (using EXEC stored_procedure) all of them work fine. It seems like the stored procedure is executed, but no response is returned to the client (the WinForm app), thus the timeout exception.

Any ideas? Thanks.

View 10 Replies View Related

Stored Procedure Query Optimization - Query TimeOut Error

Nov 23, 2004

How to optimize the following Stored procedure running on MSSQL server 2000 sp4 :

CREATE PROCEDURE proc1
@Franchise ObjectId
, @dtmStart DATETIME
, @dtmEnd DATETIME
AS
BEGIN


SET NOCOUNT ON

SELECT p.Product
, c.Currency
, c.Minor
, a.ACDef
, e.Event
, t.Dec
, count(1) "Count"
, sum(Amount) "Total"
FROM tb_Event t
JOIN tb_Prod p
ON ( t.ProdId = p.ProdId )
JOIN tb_ACDef a
ON ( t.ACDefId = a.ACDefId )
JOIN tb_Curr c
ON ( t.CurrId = c.CurrId )
JOIN tb_Event e
ON ( t.EventId = e.EventId )
JOIN tb_Setl s
ON ( s.BUId = t.BUId
and s.SetlD = t.SetlD )
WHERE Fran = @Franchise
AND t.CDate >= @dtmStart
AND t.CDate <= @dtmEnd
AND s.Status = 1
GROUP BY p.Product
, c.Currency
, c.Minor
, a.ACDef
, e.Event
, t.Dec

RETURN 1
END



GO

View 8 Replies View Related

Calling A Stored Procedure Inside Another Stored Procedure (or Nested Stored Procedures)

Nov 1, 2007

Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly.  For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') 
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). 
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
 

View 1 Replies View Related

Calling A Stored Procedure From ADO.NET 2.0-VB 2005 Express: Working With SELECT Statements In The Stored Procedure-4 Errors?

Mar 3, 2008

Hi all,

I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):

(1) /////--spTopSixAnalytes.sql--///

USE ssmsExpressDB

GO

CREATE Procedure [dbo].[spTopSixAnalytes]

AS

SET ROWCOUNT 6

SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName

FROM LabTests

ORDER BY LabTests.Result DESC

GO


(2) /////--spTopSixAnalytesEXEC.sql--//////////////


USE ssmsExpressDB

GO
EXEC spTopSixAnalytes
GO

I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")

Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)

sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure

'Pass the name of the DataSet through the overloaded contructor

'of the DataSet class.

Dim dataSet As DataSet ("ssmsExpressDB")

sqlConnection.Open()

sqlDataAdapter.Fill(DataSet)

sqlConnection.Close()

End Sub

End Class
///////////////////////////////////////////////////////////////////////////////////////////

I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)

Please help and advise.

Thanks in advance,
Scott Chang

More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.




View 11 Replies View Related

Store Procedure Called - Timeout

Sep 12, 2005

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 Replies View Related

Timeout Error While Executing Procedure

Aug 17, 2006

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...

View 1 Replies View Related

T-SQL (SS2K8) :: One Stored Procedure Return Data (select Statement) Into Another Stored Procedure

Nov 14, 2014

I am new to work on Sql server,

I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.

Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.

View 1 Replies View Related

SQL Server 2014 :: Embed Parameter In Name Of Stored Procedure Called From Within Another Stored Procedure?

Jan 29, 2015

I 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] ....

View 9 Replies View Related

Connect To Oracle Stored Procedure From SQL Server Stored Procedure...and Vice Versa.

Sep 19, 2006

I have a requirement to execute an Oracle procedure from within an SQL Server procedure and vice versa.

How do I do that? Articles, code samples, etc???

View 1 Replies View Related

Set Timeout For Stored Procedures

Dec 12, 2007

Hi all,

is there a way to set timeout in a stored procedure?? I'm running a stored procedure but 10% of the time it will time out and when I say retry it works just fine. I'm wondering if I set the timeout large enough it won't run into that error.
I'd like to know what would be the best way to avoid it AND how to set timeout in stored procedures..

thanks guys!!

View 7 Replies View Related

Timeout Errors When Execution Long Running Procedure

Apr 21, 2007

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







View 1 Replies View Related

Grab IDENTITY From Called Stored Procedure For Use In Second Stored Procedure In ASP.NET Page

Dec 28, 2005

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 Related

SQL Server 2012 :: Executing Dynamic Stored Procedure From A Stored Procedure?

Sep 26, 2014

I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure

at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT

I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT

View 3 Replies View Related

Bcp Mystery

Mar 4, 1999

I have a table Catalog_Item with 365000 rows. I wish to move the contents of the table to an identical database on a different server using bcp. The table has no primary keys, foreign keys, or indexes. I am able to successfully bcp out the data in character format and native format.

I attempt to bcp the data in to the identical table on the other server, but
the bcp ALWAYS fails at 19000 or 20000 rows; it does not complete, just hangs there in the command window, with no more info. When I use a small data set of 5000 rows, the same happens at the end of those 5000. I have tried to redo the bcp in native mode, and character mode. I have tried to specify the first line starts at 125000. I have tried to bcp the data into a different database on a different server; that also quits at 19000 or 20000 rows.

BCP of other large data sets into the same database occurs smoothly. Only this combination of table and data set is giving this problem.

any ideas?

View 2 Replies View Related

Strange Timeout Problem When Executing Stored Proc

Jul 27, 2004

We have a stored procedure that will return at most about 600 records. In almost all cases it works fine, but in a few odd cases the command times out when used from a webform. We changed the CommandTimeout property to 120 seconds... no difference. If we run a query that times out in Query Analyzer, it runs in under 3 seconds (on the SQL Server machine). We also did a lot of profiling, and for any page that runs fine, the query takes about the same time when called from a webform and run in the Query Analyzer.
We ran our tests on the pre-production environment with absolutely no one else on the machines. We also created a test page that just fills a DataSet. Straightforward... get connection object, create command, set params, create data adapter, fill dataset. It suffers the same problem.

The web server and SQL server are two separate machines. Oh, and before I forget... This is SQL 2000. It used to run SQL 7, and all the queries there were (slightly) slower, but none timed out, not even the ones that do now.

Maybe the first question should be:
How can it be that a query that runs fine in Query Analyzer times out from a webform?

View 8 Replies View Related

SQL Express Query Timeout Error On Stored Procedures

Dec 12, 2007

I've been researching this problem for weeks and I haven't gotten very far with it so I was hoping to get some help here.

Here's the error information we get:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

This particular error comes from a .net application, but we've seen similar "query timeout errors" from a vb6 application roughly 6 months ago.

Here are the facts we've narrowed down:

1) The timeout happens at seemingly random times (very sporadic).
2) It's currently only happening on a few stored procedures (if requested, I'll provide one of the stored procedures, but it's basically a complex search procedure).
3) To resolve the timeout error, we've found 2 temporary solutions:

A) have all clients exit the program thus closing all active connections (less than 10 connection in a 4-5 user setup)
B) I run the following script when the timeout occurs and then the stored procedure runs smoothly:




Code Block

sp_configure 'remote query timeout', 0
reconfigure with override
sp_configure 'remote query timeout', 600
reconfigure with override
4) Running the stored procedure from our application and from SQL Management studio express, is the same, except management studio doesn't time out and actually runs as long as it takes (roughly 1 minute 20 seconds or under 1 second after the script above runs).

We're pretty stumpted and it's happened at 5 different client sites with little in common. One of our sites is even running SQL express off the workstation with nothing else running and it still occurs.

I'm open to trying practically anything at this point, but unfortuntenly we have not been able to reproduce this behavior in our testing enviroment so I can't give much information for others to reproduce.

Thanks in advance!

View 1 Replies View Related

Weird Timeout In CLR Stored Procedures That Use FtpWebRequest / Response....

Jan 14, 2007

Hi all,

This is a problem for that I lost a weekend to reach to this conclusions:

Here is the scenario:
I need a file downloaded from a FTP server on my SQL 2005 DB Server to process it in several ways. This file is tens of MBs (let's say 50MB). I set up a CLR stored procedure to bring it locally on the machine.

Inside this procedure, call it getFtpFile, I use the FtpWebRequest to issue the commands I need to the FTP server (get the file size and download the file). For reading and writing I use a StreamReader sr and a StreamWriter. sw

I read the remote file in block with:
sr.ReadBlock(buf, 0, blockSizeBytes);

Everything work file until the last block that, most probably will not be of size blockSizeBytes, but smaller. So, what happens is that when I read this last block there is an exception raised from the call saying:
The underlying connection was closed: An unexpected error occurred on a receive.

This happens only if:
-> the code runs inside SQL (calling the SP) (it runs perfect on normal tet environment where I have another executable and call the getFtpFile method in the CLR dll);
-> 100 seconds (100000 miliseconds) (exactly) have passed (small files do not experiencing this problem)

I thought the reader writer scheme is not perfect and implemented again with a simple FileStream.WriteByte(response.GetResponseStream().Read());
This approach, even if very slow, still has the problem after 100 seconds, in a CLR SQL environment, when it reads the at the last character.

I have used all the combinations of KeepAlive and response.Closose(). Problems still there.

Conclusion: There must be a 100 seconds timeout that immediately after my last byte has been received, closes the connection and the stream of the response crashes during this last call.



Thank you all for the help!

View 2 Replies View Related

Connection Mystery

Apr 13, 2005

I am having trouble connecting to an SQL Server from an ASP.NET page
written in C#.  I have reduced the code in my page to this: public void Page_Load( object s, EventArgs e ) {

SqlConnection conn = new SqlConnection( "Server=server;User ID=user;Password=pass;" );
 conn.Open();

} and I get the following error:
System.Data.SqlClient.SqlException: SQL Server does not exist or access denied.

This
works for other server/user/pass except the one I am having trouble
with.  The SQL Server is using SQL & Windows Authentication.  I am
able to connect from the machine that is running the webserver via
osql, ODBC, and MS Access using the same arguments (server, user, and
pass), so I know that the SQL server does exist, and the User/Password
should give me access. Does anyone have any ideas why this
isn't working or what ASP.NET is does differently from the other access
methods?  Any ideas are greatly appreciated. Thanks, Mark Dane

View 6 Replies View Related

Backup Mystery

Jan 31, 2000

I have created a maintenance plan which backups to a device which I have created. However the device does not show in explorer and maintenance plan cannot find it when it trys to open the device. Consequently I'm not getting a backup.
Even if I delete the device and re create it things don't change.

Running SQL 7 SP1 on NT SP5 with SMS 2 SP1.

Any help gratefully received....


Dave Turner

View 1 Replies View Related

Mystery Backup?

Jan 28, 2000

I was working at a customer site and wanted to verify that backups were indeed being run successfully before I began to make changes.

I noticed a dump device for the production database with the nightly backup in it but I cannot find the task that produces this dump. I did find the task that dumps the log each hour.

Is there another area that some of you DBAs used to schedule dumps other than the task manager that I might be missing? Maybe an AT job?

Thanks.

-Darin.

View 1 Replies View Related

Mystery Login?

Feb 3, 2000

I am trying to track down an account in SQL 6.5 that keeps showing up in my failed login log. We are using Standard Security.

The log doesn't provide much detail other than the attempted login name and the time/date.

Any ideas on how to track down more information on this mystery account would be appreciated.

Thanks.

Darin Drewrey

View 2 Replies View Related







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