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


ADVERTISEMENT

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 ONset QUOTED_IDENTIFIER ONgo
-- =============================================-- Author:  C.R.P. RAJAN-- Create date: March 26, 2008-- Description: PROCEDURE FOR IMPLEMENTING SOFT PURGE-- =============================================ALTER PROCEDURE [dbo].[SP_SOFTPURGE] @POID INTASBEGINUPDATE POMASTER SET FLAG=1 WHERE POID=@POIDSELECT * FROM POMASTER WHERE FLAG=0END
Procedure 2-----------set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo
-- =============================================-- Author: C.R.P. RAJAN-- Create date: March 26, 2008-- Description: Stored Procedure for Hard Purge-- =============================================ALTER PROCEDURE [dbo].[SP_HARDPURGE]  @PoId INTAS
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   ENDCOMMIT TRANSELECT * FROM POMASTER

View 1 Replies View Related

SQL 2012 :: Nested Procedures And Try Catch

Mar 11, 2014

I have 2 Procedures. P1 and P2. Even if there is error in P1 , I want changes in the P2 to be committed. How do I do it?

Following is the code sample I have used for testing. With this code , update in both the procedures are rolled back.

create procedure P2
as
begin try

BEGIN tran
update table1
set Description = '*P2*' + Description
where Code = 'b'

[Code] ....

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

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

.NET 2005 TRY..CATCH With SQL RAISERROR Not Catching

May 28, 2008

Is there a reason why the following code does not raise an error in my .NET 2005 application? Basically I have a try..catch in my stored procedure. Then I have a try...catch in my .NET application that I want to display the error message.
But, when the stored proc raisses the error, the .net code doesn't raise it's error. The .NET code DOES raise an error if I remove the try..catch from the SQL proc and let it error (no error handling), but not if I catch the error and then use RAISERROR to bubble-up the error to .NET app. (I really need to catch the error in my SQL proc and rollback the transaction before I raise the error up to my .NET app...)
SQL
BEGIN TRYBEGIN TRANSACTION trnName
DO MY STUFF....  <---- Error raisedCOMMIT TRANSACTION trnName
END TRY
BEGIN CATCHROLLBACK TRANSACTION trnName
RAISERROR('There was an error',10,1)
 
END CATCH
ASP.NET CODE (No error raised)
Try
daAdapter.SelectCommand.ExecuteNonQuery()Catch ex As SqlException
Err.Raise(50001, "ProcName", "Error:" + ex.Message)
End Try
 

View 4 Replies View Related

Compile Error While Using Try Catch In SQL Server 2005

Mar 18, 2008



I am trying to use a simple BEGIN TRY and END TRY in my SP. It is giving a compile time error such as
Line 13: Incorrect syntax near 'try'.

Why is this, can somebody help me out. Yes i am sure it is SQL Server 2005 on my machine.

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

Problem With Nested Install Of SQL 2005 Express...

Jan 13, 2006

I create Installer for my company, with using SQL 2005 Express and its files database. I used MS Build bootstrapper before to install MS SQL 2005 Express in my installer and it was installed before my product installed, and there is no problems...
But now I need to install Express during setup, because it must be installed NOT everytime (if user choose, in bootstrapper it is impossible).
I know that during execute sequence it is not possible: two installer are not lived simultaneously in memory and I move SQL 2005 Express installation to UISequence. 

 But everytime error occured during install (list from sql server install log):
 MSI (s) (00:6C) [18:54:38:015]: Machine policy value 'DisableUserInstalls'
 is 0 MSI (s) (00:6C) [18:54:38:015]: Note: 1: 1309 2: 5 3:
 c:3cae475d5363b997aa6d2d8setupsqlncli.msi
 MSI (s) (00:6C) [18:54:38:015]: MainEngineThread is returning 110
 The system cannot open the device or file specified.

So there is no error, "Another installer process in memory", but another
 very strange error and I dont know what to do :(
 I try quiet and usual SQL express install - but that doesnt matter.

It seems, that package sqlexpr32.exe after unpacking cant see its files... What must I do to install SQL 2005 Express during my UI of Installer?

Or may some other ways to install SQL 2005 Express, with some secret keys?



 

View 1 Replies View Related

How To Invoke Nested Stored Procedures In SQL Server Projects In VS.NET 2005?

Jul 9, 2006

Hi,


I would like to use a stroed procedure within another stored procedure ( nested sp )

in a SQL project in VS.NET 2005. Since I have to use "context connection = true" as

connection string, I wont be able to use this connection for another sqlconnection

object. Because its already open. and If i try to use a regular connection string

("server=localhost;...") it will raise a security permission error. Having this

problem, Im not able to use nested stored procedures. Would anyone please give me a

hint how to resolve this issue?

View 4 Replies View Related

SQLNCLI --Linked Server Provider -- Nested Query Problem (SQL 2005)

Nov 30, 2007

Ok here is a run down of the situation:

I'm running Server 'A' and Server 'B' which are both on SQL 2005 SP2. Server B connects to Server A using the linked server functionality via the SQLNCLI provider. I am issuing an update statement from a web api in a nested transaction that uses a distrubted transaction.


However, I am receiving the following error :

Unable to start a nested transaction for OLE DB provider "SQLNCLI" for linked server "A". A nested transaction was required because the XACT_ABORT option was set to OFF.
OLE DB provider "SQLNCLI" for linked server "A" returned message "Cannot start more transactions on this session.".


I've researched the issue and understand how XACT_ABORT works.

Also, I looked at the Linked Server provider options for SQLNCLI and came accross the Nested Queries option being unchecked. I checked the option and applied it to the Linked Server.

Okay, so after my long post my questions are: Do i need to restart SQL in order for this to take effect? If not, what do i need to do? I 've restarted IIS to no avail .

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

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

SQL Server 2005 Reporting Svcs - How To Use Dataset 1 Info To Drive Nested Table Select

May 20, 2008

I've only been doing sql 2005 for a couple of months with minimal training so there's a lot I don't know.
What I'm trying to do is load a nested table (industry & customer totals) based on a value from the table it's nested in.
This is the relationship of the data.
I have at the highest group, an industry code, then a customer, then the part and then the fiscal year.
What I'm trying to accomplish is to get a group total (footer) for the (1) industry code and (2) the customer code. This footer would contain the fiscal years (ascending) and their monthly totals. I would like to take the industry code from table one and pass it to the select statement in the dataset that feeds the nested table. I've read this is not possible to load a dataset field into a parm but I've seen where some people know how to work around this. If you reply, please explain in simple terms. Thanks!


industry Customer Year OCT NOV DEC
001 - Signposts
M12345
Part 1
2006 5 6 2
2007 0 3 1


Part 2
2006 4 3 0
2007 1 0 7

Customer M12345 totals
2006 9 9 2
2007 1 3 8


M45678
Part 3
2007 8 4 7
2008 3 4 8


Part 4
2006 3 8 7
2007 5 6 6


Customer M45678 totals
2006 3 8 7
2007 13 10 13
2008 3 4 8
Industry 001 totals




2006 12 17 9
2007 14 13 21
2008 3 4 8

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

Format Database Disk With 64K Blocks NTFS

Jul 14, 2004

Hi:

Somebody tellme that with a format of my database disk with 64K blocks NTFS, i can have better performance, is that true ? there is any problem in SQL with this block size ?

View 14 Replies View Related

Splitting Script Into Small Blocks And Using UNION ALL

Jun 9, 2014

We are working for a long time on optimizing this script, and latest modification I did was splitting script into small blocks and using UNION ALL. This supported a lot but still it takes some 45 mins on prod environment. I'm not able to find anything more in this script to optimize.

View 9 Replies View Related

SQL Server 2008 :: Notify Only If There Are Certain Blocks By Certain User

Jun 12, 2015

I'm trying to set the query to send email ONLY when it returns records of blocks and i cant seem to get this going.

declare @blocks varchar(max)
set @blocks = (SELECT spid,
sp.[status],
loginame [Login],
hostname,
blocked BlkBy,
sd.name DBName,

[Code] ....

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

Blocks Of Time - Log Number Of Visits From Clients

Oct 30, 2014

I have the following SQL that I want to log the number of visits from clients. a session is a block of time, for example, any time between 9-12 is a morning sessions, 12-5 is an afternoon session etc. I need to count the number of sessions in a month not the appointments within the session, make sense?

select sr.resourceid, st.TimeStart, datename(m,sr.scheduledate) as sesmonth, datename(yy,sr.scheduledate) as year,
(CASE
WHEN DATEPART(hour, st.TimeStart) BETWEEN 0 AND 12 THEN 'MORNING SESSION'
WHEN DATEPART(hour, st.TimeStart) BETWEEN 12 AND 17 THEN 'AFTERNOON SESSION'
WHEN DATEPART(hour, st.TimeStart) BETWEEN 17 AND 24 THEN 'EVENING SESSION'

[Code] ....

View 2 Replies View Related

Convert Repeating Blocks Of Columns Into Rows?

Feb 17, 2015

writing SQL code to convert blocks of columns into rows.

Example,

Id A1 A2 B1 B2
1 1 1 1 1
2 2 2 2 2
3 3 3 3 3

into

Id Group Value
1 A 1
1 A 1
1 B 1
1 B 1
2 A 2
2 A 2
2 B 2
2 B 2

View 2 Replies View Related

Delete Statement Times Out And Blocks Reads

Aug 21, 2007

Hi. Periodically I need to run a delete statement that deletes old data. The problem is that this can timeout using ODBC (via the CDatabase and CRecordSet classes in legacy code). Also, while its running the delete, the table its operating on is locked and my application can't continue to run and operate on rows not affected by the delete.

Are there any workarounds for this? Can the timeout be set in the connect string?

Thanks,
Brian

View 1 Replies View Related

Data Application Blocks For Windows Mobile

Feb 5, 2006

I've read that microsoft.applicationblocks.data for .net v2 can't be deployed to a mobile app, and my experience bears that out. So I 'm wondering if there are application blocks for windows mobile 5 that would know how to both talk to sql server mobile and sql server 2005. I see OpenNetCF has a port but as far as I can tell, they only address sql server mobile and not talking to a sql server 2005 remote database. I can use the OpenNetCF version for my sql server mobile requirements, but I'm hoping there is an encapsulation of sqlclient calls for communication with my server db.

thanks

braden

View 3 Replies View Related







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