Quick Transaction Question.

If in .NET I open a connection to my database, then use some sql text to start a transaction, then reuse that same open connection to call several stored procedures (using SqlCommand with CommandType.StoredProcedure), before ending the transaction. Will that run as a single transaction that can be rolled back? or are the stored procedure calls unable to roll-back after each one completes?

the stored procedures are themselves atomic and would need their own rollbacks. your use of transaction would work if all your statements were sql strings executing one after the other. -- jp

|||

Did a bit of research, and it appears there is a BeginTransaction method that can be used as part of the SqlConnection object. Which allowed me to start a transaction on a connection, then call serveral stored procedures using a SqlCommand object set to CommandType.StoredProcedure, and it all works as a single transaction, rolling everything back if any of then calls fail (internally or enternally). Here is a quick code snippet:

1protected void btnSave_Click(object sender, EventArgs e) {2//Create connection string for SQL query3 String strConnect;4 strConnect = WebConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString;56//Generate call to stored procedure7 SqlConnection con =new SqlConnection(strConnect);8 SqlTransaction trans =null;910//Make Calls11string myNull =null;12try {13 con.Open();14 trans = con.BeginTransaction();15int ret1 = spCall(con, trans,"two");16int ret3 = spCall(con, trans, myNull);17int ret2 = spCall(con, trans,"three");18 trans.Commit();19 Master.Message.CssClass ="Text_Message";20 Master.Message.Text = ret1.ToString();21 Master.Message.Visible =true;22 }23catch (Exception sql) {24 Master.Message.CssClass ="Text_Error";25 Master.Message.Text = sql.Message;26 Master.Message.Visible =true;27if (null != trans) {28 trans.Rollback();29 }30 }31finally {32 con.Close();33 con.Dispose();34 }35 }3637protected int spCall(SqlConnection myConn, SqlTransaction myTrans,string myParam) {3839//Generate call to stored procedure40 SqlCommand storedProcCommand =new SqlCommand("spTest", myConn);41 storedProcCommand.CommandType = CommandType.StoredProcedure;4243//Build SQL parameter list44 storedProcCommand.Parameters.AddWithValue("@.value", myParam);4546//Return code47 SqlParameter retParam = storedProcCommand.Parameters.Add("@.ReturnValue", SqlDbType.Int);48 retParam.Direction = ParameterDirection.ReturnValue;4950//Bind to transaction51 storedProcCommand.Transaction = myTrans;5253//Run stored procedure54int retCode = 0;55 SqlDataReader Reader = storedProcCommand.ExecuteReader();56 retCode = (int)storedProcCommand.Parameters["@.ReturnValue"].Value;57 Reader.Close();5859return retCode;60 }

 
1CREATE PROCEDURE [dbo].[spTest]2--Parameters3 @.valuevarchar(50) =null4AS56BEGIN78SET NOCOUNT ON;910INSERT INTO tbTest11 (12 [value]13 )14VALUES15 (16 @.value17 )1819RETURN@.@.Identity2021END
My first call inserts okay, the second call fails because nulls are not accepted by [value] in the table definition., and the third call never happens due to the exception thrown by the second call, which forced a rollback of the entire transaction, of which each stored procedure is a call of. Did a fair amount of testing and everything appears to be in order, if anyone notices anything I overlooked and or that could be problematic, please let me know.

Quick question to check NULL values in input parameters in a stored procedure

Hi:

I have a stored procedure that calls 3 stored procedures. If some of my input parameters are NULL, I would like to skip the call to another stored procedure. Can you someone please help me with this? I would like to find out what is NULL, before I execute the other stored procedures. Thanks so much.

MA

check with is not null

example

If @.Var1 is not null
begin
exec proc1 @.Var1
end

If @.Var2 is not null
begin
exec proc2 @.Var2
end

If @.Var3 is not null
begin
exec proc3 @.Var3
end

Denis the SQL Menace
http://sqlservercode.blogspot.com/

|||Is there a way loop thru the parameters in one go, because in some instances I am dealing with a set of 50 or more parameters. Thanks.|||

something like this perhaps

declare @.v int
declare @.v2 int
declare @.v3 int

select @.v =1,@.v2 =3

if exists (select * from (select @.v as a union all
select @.v2 union all
select @.v3) z where a is null)
begin
print 'at least one parameter has a null value'
end

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Going back to your initial response, which I think I will respond as an Answer to my question, because it is my best bet at this moment. I do have a quick question in reference to your first response, here it is:

If I have more than parameters, that I need to check for NULL, and if its NULL then dont execute the SP, and vice versa, how would i do that? Can i do something like this, my goal is to check/validate that if all values passed in are NULL, then dont call the sp:

IF @.CitizenshipStatusCode is null and
@.GovtIDTypeCode is null and
@.AlienID is null and
@.EmploymentStatusCode is null and
@.EmployerName is null and
@.EmployerAddress1 is null and
@.EmployerAddress2 is null and
@.EmployerAddress3 is null and
@.EmployerCity is null and
@.EmployerStateCode is null and
@.EmployerZipCode is null and
@.EmployerCountryCode is null and
@.Position is null and
@.WorkForeignPhoneExchange is null and
@.WorkAreaCode is null and
@.WorkPhoneNumber is null and
@.WorkExtension is null and
@.WorkEmail is null and
@.EmploymentYears is null and
@.EmploymentMonths is null and
@.MonthlySalaryAmount is null and
@.MonthlyRentAmount is null and
@.OtherMonthlyIncome is null and
@.ResidenceTypeCode is null and
@.CreatedPersonID is null and
@.UpdatedOn is null and
@.CreatedPersonID is null and
@.UpdatedOn is null
BEGIN
Set @.IsNull = 1
END
ELSE
Set @.IsNull = 0

|||

you could use coalesce since coalesce returns the first non null value

examples

declare @.v varchar(40)
declare @.v2 int
declare @.v3 int

select @.v ='1',@.v2 =3
if coalesce(@.v,@.v2,@.v2,null) is null
begin
select 'is null'
end
else
begin
select 'is NOT null'
end
go

declare @.v varchar(40)
declare @.v2 int
declare @.v3 int

--will be null
if coalesce(@.v,@.v2,@.v2,null) is null
begin
select 'is null'
end
else
begin
select 'is NOT null'
end

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||thanks I think this is what I can use. Also, why do you have the word null at the end, inside the parantheses. Is that necessary? Whats the purpose of that?|||It is not necessary to have NULL at the end. If all of the inputs to COALESCE is NULL then it will return NULL anyway.

Queue Reader - remote procedure call failed

I am running transactional repl with an updateable subscription between two servers running SQL Server 2000 SP3, all agents running on the publisher. Every now and then, the Queue reader fails. I enable logging and attempt restart. The output file looks n
ormal to me; several queries for queued data, but then it seems to timeout. It just sits there for 3 minutes, then fails and retries. I can successfully query the other server, so I know it's not a communications problem. The event viewer simply says "the
remote procedure call failed and did not execute". I can't find any other error messages.
Does anyone have any advice? Thank you.
Microsoft SQL Server Replication Queue Reader Agent 8.00.760
Copyright (c) 2000 Microsoft Corporation
Microsoft SQL Server Replication Agent: [MIALDCS-1].9
Trying to Connect to Local Distributor
Connecting to QueueReader 'MIALDCS-1.distribution'
Server: MIALDCS-1
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[7/30/2004 12:17:19 PM]MIALDCS-1.distribution: select count(*) from master.dbo.sysprocesses where [program_name] = 'Queue Reader Main (distribution)'
[7/30/2004 12:17:19 PM]MIALDCS-1.distribution: select top 1 id, name from MSqreader_agents
[7/30/2004 12:17:19 PM]MIALDCS-1.distribution: select SERVERPROPERTY('IsClustered')
Queue Reader Agent [MIALDCS-1].9 (Id = 3) started
Repl Agent Status: 1
[7/30/2004 12:17:19 PM]MIALDCS-1.distribution: execute dbo.sp_MShelp_profile 3, 9, N''
Opening SQL based queues
[7/30/2004 12:17:19 PM]MIALDCS-1.distribution: exec master.dbo.sp_MSenum_replsqlqueues N'distribution'
Worker Thread 608 : Starting
The Message Queuing service does not exist[7/30/2004 12:17:19 PM]MIALDCS-1.distribution: exec dbo.sp_MShelp_subscriber_info N'MIALDCS-1', N'MIALDCS-2'
The Message Queuing service is not available
Connecting to MIALDCS-2 'MIALDCS-2.Island'
Server: MIALDCS-2
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
MIALDCS-2.Island: {? = call dbo.sp_getsqlqueueversion (?, ?, ?, ?)}
MIALDCS-2.Island: {? = call dbo.sp_replsqlqgetrows (N'MIALDCS-1', N'Island', N'Island')}
[7/30/2004 12:17:31 PM]MIALDCS-1.distribution: exec dbo.sp_helpdistpublisher @.publisher = N'MIALDCS-1'
Connecting to MIALDCS-1 'MIALDCS-1.Island'
Worker Thread 608 : processing Transaction [2LhOShgh_agC2<T]?LH.h-5--09M--] of [SQL Queue]
Worker Thread 608 : Started Queue Transaction
Worker Thread 608 : Started SQL Tran
MIALDCS-1.Island: {? = call dbo.sp_getqueuedarticlesynctraninfo (N'Island', 44)}
SQL Command : <exec [dbo].[sp_MSsync_del_Leg_Seat_Map_1] N'MIALDCS-2', N'Island', 'F2', '101', '2004-07-24 00:00:00.000', '1', 0, 0, ' ', ' ', 0, 12632256, ' ', 'Y', ' ', 'N', 'N', '', '', '', '', '', '', '', '', ' ', ' ', ' ', 'E8613112-29DC-4563-B3F0-58
665C4967B9', 1>
(hundreds more records follow)
what command is it failing on?
The problem is probably related to the execution of a single proc, which is
locking on the publisher.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"LeeH" <LeeH@.discussions.microsoft.com> wrote in message
news:055E41A9-DA76-403B-8D93-3F4C32BA6DDE@.microsoft.com...
> I am running transactional repl with an updateable subscription between
two servers running SQL Server 2000 SP3, all agents running on the
publisher. Every now and then, the Queue reader fails. I enable logging and
attempt restart. The output file looks normal to me; several queries for
queued data, but then it seems to timeout. It just sits there for 3 minutes,
then fails and retries. I can successfully query the other server, so I know
it's not a communications problem. The event viewer simply says "the remote
procedure call failed and did not execute". I can't find any other error
messages.
> Does anyone have any advice? Thank you.
> Microsoft SQL Server Replication Queue Reader Agent 8.00.760
> Copyright (c) 2000 Microsoft Corporation
> Microsoft SQL Server Replication Agent: [MIALDCS-1].9
> Trying to Connect to Local Distributor
> Connecting to QueueReader 'MIALDCS-1.distribution'
> Server: MIALDCS-1
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: dbo
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [7/30/2004 12:17:19 PM]MIALDCS-1.distribution: select count(*) from
master.dbo.sysprocesses where [program_name] = 'Queue Reader Main
(distribution)'
> [7/30/2004 12:17:19 PM]MIALDCS-1.distribution: select top 1 id, name from
MSqreader_agents
> [7/30/2004 12:17:19 PM]MIALDCS-1.distribution: select
SERVERPROPERTY('IsClustered')
> Queue Reader Agent [MIALDCS-1].9 (Id = 3) started
> Repl Agent Status: 1
> [7/30/2004 12:17:19 PM]MIALDCS-1.distribution: execute
dbo.sp_MShelp_profile 3, 9, N''
> Opening SQL based queues
> [7/30/2004 12:17:19 PM]MIALDCS-1.distribution: exec
master.dbo.sp_MSenum_replsqlqueues N'distribution'
> Worker Thread 608 : Starting
> The Message Queuing service does not exist[7/30/2004 12:17:19
PM]MIALDCS-1.distribution: exec dbo.sp_MShelp_subscriber_info N'MIALDCS-1',
N'MIALDCS-2'
> The Message Queuing service is not available
> Connecting to MIALDCS-2 'MIALDCS-2.Island'
> Server: MIALDCS-2
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: dbo
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> MIALDCS-2.Island: {? = call dbo.sp_getsqlqueueversion (?, ?, ?, ?)}
> MIALDCS-2.Island: {? = call dbo.sp_replsqlqgetrows (N'MIALDCS-1',
N'Island', N'Island')}
> [7/30/2004 12:17:31 PM]MIALDCS-1.distribution: exec
dbo.sp_helpdistpublisher @.publisher = N'MIALDCS-1'
> Connecting to MIALDCS-1 'MIALDCS-1.Island'
> Worker Thread 608 : processing Transaction
[2LhOShgh_agC2<T]?LH.h-5--09M--] of [SQL Queue]
> Worker Thread 608 : Started Queue Transaction
> Worker Thread 608 : Started SQL Tran
> MIALDCS-1.Island: {? = call dbo.sp_getqueuedarticlesynctraninfo
(N'Island', 44)}
> SQL Command : <exec [dbo].[sp_MSsync_del_Leg_Seat_Map_1] N'MIALDCS-2',
N'Island', 'F2', '101', '2004-07-24 00:00:00.000', '1', 0, 0, ' ', ' ', 0,
12632256, ' ', 'Y', ' ', 'N', 'N', '', '', '', '', '', '', '', '', ' ', ' ',
' ', 'E8613112-29DC-4563-B3F0-58665C4967B9', 1>
> (hundreds more records follow)
>
|||I don't see anything in the log that indicates failure. It seems that the
agent just restarts. The event viewer simply says "the remote procedure call
failed and did not execute".
"Hilary Cotter" wrote:

> what command is it failing on?
> The problem is probably related to the execution of a single proc, which is
> locking on the publisher.
>
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "LeeH" <LeeH@.discussions.microsoft.com> wrote in message
> news:055E41A9-DA76-403B-8D93-3F4C32BA6DDE@.microsoft.com...
> two servers running SQL Server 2000 SP3, all agents running on the
> publisher. Every now and then, the Queue reader fails. I enable logging and
> attempt restart. The output file looks normal to me; several queries for
> queued data, but then it seems to timeout. It just sits there for 3 minutes,
> then fails and retries. I can successfully query the other server, so I know
> it's not a communications problem. The event viewer simply says "the remote
> procedure call failed and did not execute". I can't find any other error
> messages.
> master.dbo.sysprocesses where [program_name] = 'Queue Reader Main
> (distribution)'
> MSqreader_agents
> SERVERPROPERTY('IsClustered')
> dbo.sp_MShelp_profile 3, 9, N''
> master.dbo.sp_MSenum_replsqlqueues N'distribution'
> PM]MIALDCS-1.distribution: exec dbo.sp_MShelp_subscriber_info N'MIALDCS-1',
> N'MIALDCS-2'
> N'Island', N'Island')}
> dbo.sp_helpdistpublisher @.publisher = N'MIALDCS-1'
> [2LhOShgh_agC2<T]?LH.h-5--09M--] of [SQL Queue]
> (N'Island', 44)}
> N'Island', 'F2', '101', '2004-07-24 00:00:00.000', '1', 0, 0, ' ', ' ', 0,
> 12632256, ' ', 'Y', ' ', 'N', 'N', '', '', '', '', '', '', '', '', ' ', ' ',
> ' ', 'E8613112-29DC-4563-B3F0-58665C4967B9', 1>
>
>