Using Try Catch Blocks In Stored Procedure

Mar 26, 2008

The following two stored procedures works fine. I need to add try catch blocks in the stored procedures. How to do it? Pls modify my stored procedure with try and catch blocks and in the catch block i need to call the Procedure called ErrorMsg which contains error severity.

Procedure 1
------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:  C.R.P. RAJAN
-- Create date: March 26, 2008
-- Description: PROCEDURE FOR IMPLEMENTING SOFT PURGE
-- =============================================
ALTER PROCEDURE [dbo].[SP_SOFTPURGE]
@POID INT
AS
BEGIN
UPDATE POMASTER SET FLAG=1 WHERE POID=@POID
SELECT * FROM POMASTER WHERE FLAG=0
END

Procedure 2
-----------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author: C.R.P. RAJAN
-- Create date: March 26, 2008
-- Description: Stored Procedure for Hard Purge
-- =============================================
ALTER PROCEDURE [dbo].[SP_HARDPURGE]
 @PoId INT
AS

BEGIN TRAN
 INSERT INTO ARCHIVE SELECT * FROM POMASTER WHERE POID = @POID
   IF @@ERROR != 0
   BEGIN
      ROLLBACK TRAN
      RETURN
   END

   DELETE FROM POMASTER WHERE POID = @POID
   IF @@ERROR != 0
   BEGIN
      ROLLBACK TRAN
      RETURN
   END
COMMIT TRAN
SELECT * FROM POMASTER

View 1 Replies


ADVERTISEMENT

Problem With Nested Try Catch Blocks In SQL 2005

May 23, 2006

hi All,

Code:

begin try

begin transaction trans1

//some transaction

begin try

begin transaction trans2

//some transaction

//Raise Error

//commit transaction trans2

end try

begin catch

//Raise error

end catch
//some transaction

//commit transaction trans1

end try

begin catch

//Rollback trans1

end catch]



In the above code when an error is raised in the transaction trans2 the immediate catch is not invoked where as the outer catch is being invoked. I dont know y?. Can anybody help. Thanx











View 5 Replies View Related

How To Catch Stored Procedure Execution Time?

Dec 6, 2007

Hello, everyone:

For performance issue, I need to catch the stored procedure execution time. Any suggestion will be appreciated. Thanks.

ZYT

View 14 Replies View Related

SQL Server 2014 :: Using (Try-Catch) And (Rollback) On Read Only Stored Procedure?

Apr 30, 2015

In general as understand if we have a stored procedure that does operations like inserts or updates, it makes perfect sense to use a rollback operation within a transaction.

So, if something goes wrong and the transaction does not complete, all changes will be reverted and an error description will be thrown for example.

Nevertheless, does using a rollback within a try catch statement, make sense in a read only stored procedure, that practically executes some dynamic sql just to select data from some tables?

I have around 100 Stored procedures, all of them read only. Today a colleague suggested adding try-catch blocks with rollback to all of them. But since they are just selecting data, I don't see a clear benefit of doing so, compared to the hassle of changing such a big number of SP's.

View 9 Replies View Related

How To Stop Execution Of Stored Procedure If Error Occurs In Try/catch Block

Mar 3, 2008



Hello, I have stored procedure that when executed it will check to see if a given name is found in the database, if the name is found, I would like to have it continue on to do its work, however if the name is not found, I would like it to raise an error and then stop execution at that point, however, the way it is currently working is, if the name is not found, it catches the error, raises it and then continues on and tries to do its work, which then it bombs out because it can't. I wasn't sure if there was a way to stop the execution of the procedure in the catch statement. I don't think I want to raise the error level to 20-25 because I don't want to drop the connection to the database per say, I just want to halt execution.

Here is a simple example of what I have:




Code Snippet
begin try

if not exists (select * from sys.database_principals where [name] = 'flea')

raiserror('flea not found', 16, 1)
end try
begin catch

declare @ErrorMessage nvarchar(4000);
declare @ErrorSeverity int;
select

@ErrorMessage = error_message(),
@ErrorSeverity = error_severity();
raiserror(@ErrorMessage, @ErrorSeverity, 1);
end catch
go

begin

print 'hello world'
end






At this point, if the user name (flea) is not found, I don't want it ever to get to the point of 'Hello World', I would like the error raised and the procedure to exit at this point. Any advice would be appreciated on how to best handle my situation!

Thanks,
Flea

View 5 Replies View Related

Reusable Blocks Of Code In Stored Procedures

Apr 15, 2008

I have several stored procedures with a similar query. The SELECT clauses are identical, but the FROM and WHERE clauses are different.

Since the SELECT clause is long and complicated (and often changes), I want to place the SELECT clause in a separate file and then have the stored procedures include the block of text for the SELECT clause when they compile.

Is this possible? Looking through the docs and searching online, I don't see any way to do this.

View 1 Replies View Related

Try Catch Doesn't Catch Errors Inside A Data Flow Transformation Script Component

Feb 15, 2007

Hi,

I'm having trouble with a Script Component in a data flow task. I have code that does a SqlCommand.ExecuteReader() call that throws an 'Object reference not set to an instance of an object' error. Thing is, the SqlCommand.ExecuteReader() call is already inside a Try..Catch block. Essentially I have two questions regarding this error:

a) Why doesn't my Catch block catch the exception?
b) I've made sure that my SqlCommand object and the SqlConnection property that it uses are properly instantiated, and the query is correct. Any ideas on why it is throwing that exception?

Hope someone could help.

View 3 Replies View Related

T-sql Try Catch - Using Triggers And Nested Stored Procedures

Jan 7, 2008

For every trigger and stored procedure I have a try-catch that writes to an error_log table.
The problem is the inner error is not preserved, always get:
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

As seen below - though commented out:
I tried commiting any transactions - though I didn't create one.
I played with the
XACT_STATE though that was 0
My test case was last procedure has 1/0

Thanks,
Russ
-----------------------------------------------------------

Below is what I have

Step 1)

ALTER Trigger [trg_ActivityLogEntryReportsError] ON [dbo].[ActivityLog]
FOR INSERT AS

DECLARE @ActivityLogID int
,@AlertMessageTypeID int
,@comment nvarchar(max)
,@Error_Source nvarchar(max)
--- etc.
SELECT
@ActivityLogID = ActivityLogID
,@AlertMessageTypeID = AlertMessageTypeID
,@Comment = Comment
FROM INSERTED
BEGIN TRY

if @AlertMessageTypeID = 2 -- activity reported an error
begin
exec proc_CreateAlertLogEntry_forError
@ActivityLogID
,@Comment

update ActivityLog
set flgActivityChecked = 1
where @activityLogId = activityLogID
end
END TRY


BEGIN CATCH
select
@Error_Source = 'trg_ActivityLogEntryReportsError '
,@Error_Procedure = ERROR_Procedure()
--- etc.
INSERT INTO ERROR_LOG
( Error_Source
,Error_Procedure
,Error_Message
--- etc.
)
VALUES
(
@Error_Source
,@Error_Procedure
,@Error_Message
---etc.
,@Error_Comment )
-- if @@TRANCOUNT > 0
--begin
--commit
--end
END CATCH

Step 2)

/*
This will be called by a Trigger
*/
ALTER Procedure [dbo].[proc_CreateAlertLogEntry_forError]
(@ActivityLogID int
,@Comment nvarchar(max))
AS

Declare
@ProcessScheduleID int
,@ProcessID int
--,@comment nvarchar(max)
,@Error_Source nvarchar(max)
---etc
BEGIN TRY
insert into AlertLog
(
AlertMessageTypeID
,comment
,ActivityLogID
)
values
(
2 -- error
,@comment
,@ActivityLogID
)

end

END TRY


BEGIN CATCH
PRINT 'ERROR OCCURED'
PRINT ERROR_Procedure() + ' ' + ERROR_MESSAGE()
select
@Error_Source = 'proc_CreateAlertLogEntry_forError '
---etc.
INSERT INTO ERROR_LOG
( Error_Source
,Error_Procedure
,Error_Message
---etc.
)
VALUES
(
@Error_Source
,@Error_Procedure
,@Error_Message
--- etc.)

-- if @@TRANCOUNT > 0
--begin
--commit
--end
END CATCH

update ActivityLog
set
flgActivityChecked = 1
,UpdatedDate = getdate()
,UpdatedBy = suser_sname()
where
ActivityLogID = @ActivityLogID


STEP 3

ALTER Trigger [trg_AlertLogEntry_SendsOnInsert] ON [dbo].[AlertLog]
FOR INSERT AS

declare
@AlertLogID Int
,@AlertMessageTypeID int
,@Comment nvarchar(max)
,@Error_Source nvarchar(max)
,@Error_Procedure nvarchar(max)
,@Error_Message nvarchar(max)
--- etc.
SELECT
@AlertLogID = AlertLogID
,@AlertMessageTypeID = AlertMessageTypeID
,@Comment = isnull(Comment,'')
,@ActivityLogID = isnull(ActivityLogID,-1)
FROM INSERTED

BEGIN TRY

PRINT 'trg_AlertLogEntry_SendsOnInsert'
PRINT @COMMENT
exec proc_SendEmail
@AlertLogID
,@AlertMessageTypeID
,@comment
,@ActivityLogID


END TRY


BEGIN CATCH

select
@Error_Source = 'trg_AlertLogEntry_SendsOnInsert '
,@Error_Procedure = ERROR_Procedure()
,@Error_Message = ERROR_MESSAGE()
--- etc.
INSERT INTO ERROR_LOG
( Error_Source
,Error_Procedure
-- etc.)
VALUES
(
@Error_Source
,@Error_Procedure
,@Error_Message
---etc.)
-- if @@TRANCOUNT > 0
--begin
--commit
--end
END CATCH



STEP 4

ALTER Procedure [dbo].[proc_SendEmail]
(
@AlertLogID Int
,@AlertMessageTypeID int
,@Comment nvarchar(max) = ''
,@ActivityLogID int = -1
)


AS

declare @AlertSubject nvarchar(512)
,@AlertBody nvarchar(max)
,@myQuery nvarchar(512)
,@profile_name1 nvarchar(128)
,@return_value int
,@mymailitem int
,@Error_Source nvarchar(max)
---etc.
,@Error_Comment nvarchar(max)
,@Test int
/*
@return_value int -- not using at this point but 0 is OK 1 is failure
@mymailitem int -- not using now could store mailitem_id which is on msdb.dbo.sysmail_mailitems
sysmail_mailitems.sent_status could be either 0 new, not sent, 1 sent, 2 failure or 3 retry.
*/

select top 1 @profile_name1 = [name] from msdb.dbo.sysmail_profile
order by profile_id

set @profile_name1 = rtrim(ltrim(@profile_name1))
print '@profile_name1: ' + @profile_name1
print '@comment: ' + @comment
Declare @CrsrRecipient Cursor

BEGIN TRY
PRINT 'proc_SendEmail'
--set @test = 1/0 'test crashing
select
@AlertSubject = 'AlertSubject'
,@AlertBody = 'AlertBody'

,@recipients = 'russ@test.com'


print 'sending email ' + CAST(getdate() as nvarchar(100))
EXEC @return_value = msdb.dbo.sp_send_dbmail
@profile_name = @profile_name1
,@recipients = @EMAILID
,@body = @AlertBody
,@subject = @AlertSubject
,@mailitem_id = @mymailitem OUTPUT

print 'Done ' + CAST(getdate() as nvarchar(100))
print cast(@return_value as nvarchar(100))



update alertlog
set AlertSendStatusID = 1 --sent
where
@AlertLogID = AlertLogID

END TRY

BEGIN CATCH
PRINT 'ERROR OCCURED'
PRINT ERROR_Procedure() + ' ' + ERROR_MESSAGE()
select
@Error_Source = ' proc_SendEmail '
,@Error_Procedure = ERROR_Procedure()
--- etc.
INSERT INTO ERROR_LOG
( Error_Source
,Error_Procedure
---etc.)
VALUES
(
@Error_Source
,@Error_Procedure
,@Error_Message
--- etc.)


update alertlog
set AlertSendStatusID = 2 --error
where
@AlertLogID = AlertLogID

--if @@TRANCOUNT > 0
--begin
--commit
--end
END CATCH

View 5 Replies View Related

Throwing Exception In Stored Proc To Catch In C# And Rollback The Transaction

Sep 27, 2007




Hi,

I am using sql2k5. I just wanted to throw an error from stored procedure with some message to C# to rollback my transaction.
Here is how i wnated to do ( in sequence )

C#
=====
Open a connection
Begin the transaction
Execute the command

In the Stored Proc
===========
do multiple operations one by one
if error
stop processing further
Throw the error
C#
========
if exception
rollback the transaction
else
commit the transaction

I have tried using raise error in stored proc but never thrown exception

Can any one let me know how to achieve this scenario??

~Mohan Babu

View 5 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

Try Catch Does Not Catch Exception

May 15, 2007

hi all,



All of a sudden my application started crashing when trying execute dml statements on sql server mobile database (sdf file). Frustating thing is that whole application crashes without any error message. this happens for all kinds for DML statement but not all the time. Sometimes it would fail for delete statement such as delete from table; or for insert into statement



my problem catch does not catch the error. There is no way to find out teh what is causing this error



SqlCeConnection sqlcon = new SqlCeConnection("

Data Source = '\Program Files\HISSymbol\HISSymboldb.sdf';"

);

SqlCeCommand sqlcmd = new SqlCeCommand();

sqlcmd.CommandText = Insert into company('AA', 'Lower Plenty Hotel');

sqlcmd.Connection = sqlcon;

SqlCeDataReader sqldr = null;

try

{

sqlcon.Open();

//use nonquery if result is not needed

sqlcmd.ExecuteNonQuery(); // application crashes here



}

catch (Exception e)

{

base.myErrorMsg = e.ToString();



}

finally

{

if (sqlcon != null)

{

if (sqlcon.State != System.Data.ConnectionState.Closed)

sqlcon.Close();

sqlcon.Dispose();

}

}//end of finlally

View 4 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

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

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

System Stored Procedure Call From Within My Database Stored Procedure

Mar 28, 2007

I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.

How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?

Thanks in advance

View 9 Replies View Related

Ad Hoc Query Vs Stored Procedure Performance Vs DTS Execution Of Stored Procedure

Jan 23, 2008



Has anyone encountered cases in which a proc executed by DTS has the following behavior:
1) underperforms the same proc when executed in DTS as opposed to SQL Server Managemet Studio
2) underperforms an ad-hoc version of the same query (UPDATE) executed in SQL Server Managemet Studio

What could explain this?

Obviously,

All three scenarios are executed against the same database and hit the exact same tables and indices.

Query plans show that one step, a Clustered Index Seek, consumes most of the resources (57%) and for that the estimated rows = 1 and actual rows is 10 of 1000's time higher. (~ 23000).

The DTS execution effectively never finishes even after many hours (10+)
The Stored procedure execution will finish in 6 minutes (executed after the update ad-hoc query)
The Update ad-hoc query will finish in 2 minutes

View 1 Replies View Related

Blocks And Deadlocks

Jul 25, 2002

I know blocks and Deadlocks are different but how related are they? Seems like when I get reports of deadlocks I always have blocks and the blocks grow as time passes.

View 2 Replies View Related

Lock Blocks

May 24, 2001

Hi, Everyone

Can anyone tell me how to find who blocks the table or record, because the Lock Blocks is extremly high in the performance monitor.

Regards,

View 3 Replies View Related

Blocks On SQL Server

Nov 26, 2001

I need to test a block-alert and would like to test this by causing a block on our test server. Anyone have a script handy that will do this?

View 5 Replies View Related

Logging Blocks

Apr 23, 2008

I would like to log blocks to see what causes it.

One hack of a way to do it is to run code similar to that which populates the blk column in sp_who; on a job every 5 seconds. It would store the results of code similar to the sp_who output whenever any of the rows has a value greater than zero in the blk column.

This way, I can see what was going on at the time there was a problem.
Does anyone have a better strategy for this?

View 17 Replies View Related

History Blocks?

Mar 21, 2007

Is it possiable to go through the history blocks and change field names?...ie

I want to change "PS001_Pump1.RT_Daily" to "PS001_Pump1.Runtime_Daily"

so i can access the historical data thru the new tagname.

If so , is this using an udpate query?

View 1 Replies View Related

User 'Unknown User' Could Not Execute Stored Procedure - Debugging Stored Procedure Using Visual Studio .net

Sep 13, 2007

Hi all,



I am trying to debug stored procedure using visual studio. I right click on connection and checked 'Allow SQL/CLR debugging' .. the store procedure is not local and is on sql server.



Whenever I tried to right click stored procedure and select step into store procedure> i get following error



"User 'Unknown user' could not execute stored procedure 'master.dbo.sp_enable_sql_debug' on SQL server XXXXX. Click Help for more information"



I am not sure what needs to be done on sql server side



We tried to search for sp_enable_sql_debug but I could not find this stored procedure under master.

Some web page I came accross says that "I must have an administratorial rights to debug" but I am not sure what does that mean?



Please advise..

Thank You

View 3 Replies View Related

Sending Mail In Blocks

Jun 12, 2007

I need to find a way to send mail in blocks of 100 and 500 at intervals of 120 seconds. Does anyone know of any code for this?

Thanks!

Etymon

View 4 Replies View Related

Xp_sendmail Blocks Trigger ?

Jul 20, 2005

Dear All,we are running SQL2000 Sever and make use of the xp_sendmail.For any reason the mail service can run into problems and it lookslike that the statemnt below gets not finished.EXEC @Status = master..xp_sendmail @recipients=@TOList,@copy_recipients=@CCList,@subject='the subject goeshere',@message=@MailText,@no_output=TRUEUnfortunately the statement is in an update trigger and hence itblocks the table for any further updates.My questions are:Can I achieve a kind of timeout check in my trigger in order to bypassthexp_sendmail call ?In general, sending mail in a trigger may not be a good idea.How can this be solved better ?Any hint is highly welcomeRegardsRolf

View 6 Replies View Related

End Conversation Causing Blocks

Apr 9, 2007

Hi



I was trying to clean up some conversation in Service Broker and caused alot of blocking that I seem to unable to kill. there was 1 conversation that I was not able to end, so I wanted to restart sql service, But I can't even restart the SQL service. I get the following in Event Viewer



Timeout occurred while waiting for latch: class 'SERVICE_BROKER_TRANSMISSION_INIT', id 00000001A2B03540, type 2, Task 0x0000000000C2EDA8 : 0, waittime 5400, flags 0xa, owning task 0x00000002DEBCA5C8. Continuing to wait.



Has anyone come across this



thanks



Paul

View 1 Replies View Related

Tempdb Is Locked And Blocks Other Users

Oct 18, 1999

When I am running reports on SQL Server 6.5 database which involves creation of temp tables, whole tempdb is locked. All other users are blocked,

The reports involve queries which fetch data into temporary tables, as,

SELECT c1 , c2, c3 ...
INTO #tmp1
FROM T1
WHERE C1 = xxx
AND .........

When any of these queries are run, it blocks all other operations using TEMPDB, like creation of temporary tables or queries using sorts etc.

It appears that the query locks the system tables (sysobjects, syscolumns) in TEMPDB.

However, the above query works fine if it is constructed as,

CREATE TABLE #tmp1 (a1 ..., a2 ...., a3 ..... )

INSERT INTO #tmp1
SELECT (c1, c2, c3)
FROM T1
WHERE c1 = xxx
AND .........

Could someone please tell me what actually is happening in the first scenario. Is there a way avoid the blocking of TEMPDB usage other than the workaround mentioned above (which means I have to change all my queries) ?

Thanks
Nishant

View 1 Replies View Related

Record Groups In Blocks Via A Query

Jun 11, 2007

I'm trying to place records into groups of 100, so I need a query that will return records but not based upon an autonumber type field. For instance, if I had a set of records that had been sequentially numbered (by autonumber) with IDs to greater than 3,600 but there were actually only 300 IDs remaining ranging from 1 to 3600 (say the others were deleted), then I would want to use a query to make three groups of 100 of the remaining IDs.

I can use SELECT TOP 100 for the first 100 records. How do I get subsequent groups of 100?

It's probably obvious and I am thinking too hard.

View 3 Replies View Related

One Ms Sql Server 2000 Instance Blocks The Other (thru A Dts-job)

Jul 23, 2005

Hello,we use two instances of the ms sql server 2000 (Version: 8.00.760) on a4 processor windows 2000 (sp 4) machine.A dts-packet on one instance completly blocks the work on the otherinstance. Both instances have 2 dedicated processors. The twoprocessors of the blocked instance are in idle states.How is this possible? Has someone had the same behavior of the sqlserver and found a solution for this problem?Thanks in advance for answersJürgen Simonsen

View 1 Replies View Related

Best Method For Moving Blocks Of Records From One Db To Another?

Feb 6, 2008

I wanted to set up a mechanism that would transfer blocks of records (a few dozen to in rare cases a few thousand), with slight modification, from one database to another. It's a sort of custom partial archiving process that would be triggered from a web-based admin application. Records in the target db would be identical except:

-- the primary key in the source table, an identity field, would be just an integer in the target table
-- the target table has an extra field, an integer batch ID supplied by the web application that triggers the process

It's a simple, if not efficient matter to do it within the web application: query the source table, suck the records into memory, and insert them one by one into the target db. This will be an infrequent process which can be done at off-hours, so a bit of inefficiency is not the end of the world. But I wondered if there is a more sensible, orthodox approach:

-- Could this process be done, and done efficiently, as a stored procedure with the batch ID passed as a parameter?
-- Is there any way to do a bulk insert from a recordset or array in memory using ADO and SQL? And if so, is that better than inserting records one by one?

Advice on the best general approach would be appreciated, and I will try to figure out the details.

View 2 Replies View Related

SSIS Package BLOCKS At Opening.

Mar 21, 2006

Hallo,

I'am relatively new to SSIS-programming and experiencing a serious problem with a package (6618 KB large) containing 5 dataflows. These dataflows all start from a datareader-origin and flow trough multiple transformations. While editing the fifth dataflow the developmentenvironment suddenly became unworkably slow. After any modification made to the dataflow it takes several minutes before I can continue editing. Meanwhile in the task manager the process devenv takes a very large portion (50 to 100%) of CPU.

Building the package,shutting down SSIS and even rebooting the PC don't make any difference. When you close the package ands reopen it, this takes at least 10 minutes.

We work with Microsoft SQL Server Integration Services Designer
Version 9.00.1399.00.

Does anyone know if there is a limit to the number of transformations in a flow or the size of a package? Could that be the problem?

Before this problem occured I was multiplying transformations by copying ,renaming and altering them within a dataflow. Could SSIS have some problem with that?

Any suggestion for solving this problem, other than chucking it all away an restarting, will be gratefully accepted.



With kind regards,





Paul Baudouin



View 3 Replies View Related







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