Can't shrink a Transaction Log File

I backed up my log file and attempted to shrink it with DBCC
SHRINKFILE. The command executes but leaves the transaction log at
it's current size. I know that an uncommited tansaction can cause this
to occur.
a) How do I test for this?
b)How do I get rid of it?
Regards
John L
Hi Gemel,
Try to backup your transaction log again and then try to shrink the file.
If its possible to loose youre trans log info(i guess you dont) change the
recovery model to simple(you'll loose your log info), shrink your tlog to the
desired size, then change recovery model to full and finally backup youre
database.
Hope that this can help you.
Regards
"gemel" wrote:

> I backed up my log file and attempted to shrink it with DBCC
> SHRINKFILE. The command executes but leaves the transaction log at
> it's current size. I know that an uncommited tansaction can cause this
> to occur.
> a) How do I test for this?
> b)How do I get rid of it?
> Regards
> John L
>
|||If you have a long running open transaction that could prevent the log from
shrinking..
DBCC opentran will show you the oldest transaction still in transaction
state.
You might also try
DBCC SQLPERF(logspace) to see how much space in the log is used
also
DBCC loginfo ( google on this to find more details.)
hope this helps
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"gemel" <jonel@.glenavon1.demon.co.uk> wrote in message
news:gdhm31tl8rs5t2804ibhc9onv36lee5evb@.4ax.com...
>I backed up my log file and attempted to shrink it with DBCC
> SHRINKFILE. The command executes but leaves the transaction log at
> it's current size. I know that an uncommited tansaction can cause this
> to occur.
> a) How do I test for this?
> b)How do I get rid of it?
> Regards
> John L

Can't shrink a Transaction Log File

I backed up my log file and attempted to shrink it with DBCC
SHRINKFILE. The command executes but leaves the transaction log at
it's current size. I know that an uncommited tansaction can cause this
to occur.
a) How do I test for this?
b)How do I get rid of it?
Regards
John LHi Gemel,
Try to backup your transaction log again and then try to shrink the file.
If its possible to loose youre trans log info(i guess you dont) change the
recovery model to simple(you'll loose your log info), shrink your tlog to the
desired size, then change recovery model to full and finally backup youre
database.
Hope that this can help you.
Regards
"gemel" wrote:
> I backed up my log file and attempted to shrink it with DBCC
> SHRINKFILE. The command executes but leaves the transaction log at
> it's current size. I know that an uncommited tansaction can cause this
> to occur.
> a) How do I test for this?
> b)How do I get rid of it?
> Regards
> John L
>|||If you have a long running open transaction that could prevent the log from
shrinking..
DBCC opentran will show you the oldest transaction still in transaction
state.
You might also try
DBCC SQLPERF(logspace) to see how much space in the log is used
also
DBCC loginfo ( google on this to find more details.)
hope this helps
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"gemel" <jonel@.glenavon1.demon.co.uk> wrote in message
news:gdhm31tl8rs5t2804ibhc9onv36lee5evb@.4ax.com...
>I backed up my log file and attempted to shrink it with DBCC
> SHRINKFILE. The command executes but leaves the transaction log at
> it's current size. I know that an uncommited tansaction can cause this
> to occur.
> a) How do I test for this?
> b)How do I get rid of it?
> Regards
> John L

Can't shrink a Transaction Log File

I backed up my log file and attempted to shrink it with DBCC
SHRINKFILE. The command executes but leaves the transaction log at
it's current size. I know that an uncommited tansaction can cause this
to occur.
a) How do I test for this?
b)How do I get rid of it?
Regards
John LHi Gemel,
Try to backup your transaction log again and then try to shrink the file.
If its possible to loose youre trans log info(i guess you dont) change the
recovery model to simple(you'll loose your log info), shrink your tlog to th
e
desired size, then change recovery model to full and finally backup youre
database.
Hope that this can help you.
Regards
"gemel" wrote:

> I backed up my log file and attempted to shrink it with DBCC
> SHRINKFILE. The command executes but leaves the transaction log at
> it's current size. I know that an uncommited tansaction can cause this
> to occur.
> a) How do I test for this?
> b)How do I get rid of it?
> Regards
> John L
>|||If you have a long running open transaction that could prevent the log from
shrinking..
DBCC opentran will show you the oldest transaction still in transaction
state.
You might also try
DBCC SQLPERF(logspace) to see how much space in the log is used
also
DBCC loginfo ( google on this to find more details.)
hope this helps
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"gemel" <jonel@.glenavon1.demon.co.uk> wrote in message
news:gdhm31tl8rs5t2804ibhc9onv36lee5evb@.
4ax.com...
>I backed up my log file and attempted to shrink it with DBCC
> SHRINKFILE. The command executes but leaves the transaction log at
> it's current size. I know that an uncommited tansaction can cause this
> to occur.
> a) How do I test for this?
> b)How do I get rid of it?
> Regards
> John Lsql

Can't seem to get the newly created entry's identity field :(

Hi there,

I have a stored procedure that executes a transaction which updates/inserts into a table on a remote server and should then use that new entry's autogenerated primary key to update another table.
I try to store that key into a variable called @.NewlyCreatedPastelDClinkNumber but to no avail

I have used the @.@.Identity approach and now try to manually get that key to from other information but still no go..My thinking is: since i am using transactions, maybe the entry cant be found until the transaction has been committed...but surely it should find it after the commit statement? Here is my code: I have marked the interesting bit with blue for simplicity.

ALTER procedure [dbo].[inv_SynchronizeClientsWithPastel]
as
BEGIN
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
SET XACT_ABORT ON

DECLARE @.PastelDCLinkNumber int
DECLARE @.ClientID int
DECLARE @.Name varchar(25)
DECLARE @.Surname nvarchar(25)
DECLARE @.POBoxAddr1 varchar(40)
DECLARE @.POBoxAddr2 varchar(40)
DECLARE @.PostalCode varchar(5)
DECLARE @.ClientCreditCardNumber nvarchar(50)
DECLARE @.NewlyCreatedPastelDClinkNumber int
DECLARE PastelClientPortCursor CURSOR FOR

SELECT id, [Name], Surname, POBoxAddr1, POBoxAddr2, PostalCode, PastelDCLinkNumber ,CreditCardNumber
FROM inc_Client
WHERE HasSynchronizedWithPastel = 0

OPEN PastelClientPortCursor;
FETCH NEXT FROM PastelClientPortCursor
INTO @.ClientID,@.Name,@.Surname,@.POBoxAddr1,@.POBoxAddr2,@.PostalCode,@.PastelDCLinkNumber,@.ClientCreditCardNumber
WHILE @.@.FETCH_STATUS = 0
BEGIN

--Step2 insert/update the client information into the Synergy invoice and client table
BEGIN TRANSACTION T1
IF Exists(SELECT DCLink from [server].[Satra Corporate (Pty) Ltd].dbo.Client WHERE DCLink = @.PastelDCLinkNumber)

--Update the Pastel Client table

Begin
Update [server].[Satra Corporate (Pty) Ltd].dbo.Client SET
Name = (@.Name + ' ' + @.Surname),
Account = (@.Name + ' ' + @.Surname),
Post1 = @.POBoxAddr1,
Post2 = @.POBoxAddr2,
PostPC = @.PostalCode,
cAccDescription = 'Credit Card Number: ' + @.ClientCreditCardNumber
WHERE DCLink = @.PastelDCLinkNumber
End

ELSE
Begin
--Insert into the Pastel Client table
INSERT INTO [server].[Satra Corporate (Pty) Ltd].dbo.Client
(Account, Name, Post1, Post2, PostPC, AccountTerms, CT, Credit_Limit,
RepID, Interest_Rate, Discount, On_Hold, BFOpenType, BankLink,
AutoDisc,
DiscMtrxRow, CashDebtor, DCBalance, CheckTerms, UseEmail,
iCountryID, cAccDescription, iCurrencyID, bStatPrint,
bStatEmail, bForCurAcc,
fForeignBalance, bTaxPrompt, iARPriceListNameID)
VALUES
(
SUBSTRING(@.Name, 1,19), --Account
(@.Name + ' ' + @.Surname), --Name
@.POBoxAddr1, --Post1
@.POBoxAddr2, --Post2
@.PostalCode, --PostPC
0, --Account_Terms
1, --CT
0,--Credit_Limit
0, --Rep_ID
0, --interest_Rate
0, --Discount
0, --On_Hold
0, --BFOpenType
0, --BankLink
0, --AutoDisc
0, --DiscMatrix
0, --CashDebtor
0, --DCBalance
1, --CheckTerms
0, --UseEmail
0, --iCountryID
'Credit Card Number: ' + @.ClientCreditCardNumber,
0, --iCurrencyID
1, --StatPrint
0, --StatEmail
0, --bForCurAcc
0, --ForeignBal
1, --bTaxPrompt
1 --iARPriceListNameID
)
End

--If the rowcount is 0, an error has occured
IF @.@.ROWCOUNT <> 0
Begin
COMMIT TRANSACTION T1
execute sec_LogSQLTransaction 'inv_SynchronizeClientsWithPastel' ,'inc_Client_id',@.ClientID,'System'
-- Step 3Update the HasSynchronizedWithPastel Flag

Begin
SET @.NewlyCreatedPastelDClinkNumber = (SELECT DCLink From [server].[Satra Corporate (Pty) Ltd].dbo.Client
WHERE Name = (@.Name + ' ' + @.Surname)AND
Account = (@.Name + ' ' + @.Surname)AND
Post1 = @.POBoxAddr1 AND
Post2 = @.POBoxAddr2 AND
PostPC = @.PostalCode AND
cAccDescription = 'Credit Card Number: ' + @.ClientCreditCardNumber)
End

Begin
Update inc_Client SET
HasSynchronizedWithPastel = 1 ,
PastelDCLinkNumber = @.NewlyCreatedPastelDClinkNumber
WHERE id = @.ClientID
End
End

--Rollback transaction
IF @.@.TRANCOUNT > 0
Begin
ROLLBACK TRANSACTION T1
execute sec_LogSQLTransactionError 'inv_SynchronizeClientsWithPastel','inc_Client_id',@.ClientID,'System'
End

FETCH NEXT FROM PastelClientPortCursor
INTO @.ClientID,@.Name,@.Surname,@.POBoxAddr1,@.POBoxAddr2,@.PostalCode,@.PastelDCLinkNumber, @.ClientCreditCardNumber
END
CLOSE PastelClientPortCursor;
DEALLOCATE PastelClientPortCursor;

END

SET XACT_ABORT OFF
Go

Instead of:

COMMIT TRANSACTION T1
execute sec_LogSQLTransaction 'inv_SynchronizeClientsWithPastel' ,'inc_Client_id',@.ClientID,'System'
-- Step 3Update the HasSynchronizedWithPastel Flag

Begin
SET @.NewlyCreatedPastelDClinkNumber = (SELECT DCLink From [server].[Satra Corporate (Pty) Ltd].dbo.Client
WHERE Name = (@.Name + ' ' + @.Surname)AND
Account = (@.Name + ' ' + @.Surname)AND
Post1 = @.POBoxAddr1 AND
Post2 = @.POBoxAddr2 AND
PostPC = @.PostalCode AND
cAccDescription = 'Credit Card Number: ' + @.ClientCreditCardNumber)
End

Try:

COMMIT TRANSACTION T1

SET @.NewlyCreatedPastelDClinkNumber = scope_identity()

execute sec_LogSQLTransaction 'inv_SynchronizeClientsWithPastel' ,'inc_Client_id',@.ClientID,'System'
-- Step 3Update the HasSynchronizedWithPastel Flag

|||

Problem 1)

IF Exists(SELECT DCLink from [server].[Satra Corporate (Pty) Ltd].dbo.Client WHERE DCLink = @.PastelDCLinkNumber)

--Update the Pastel Client table

Begin
Update [server].[Satra Corporate (Pty) Ltd].dbo.Client SET

Don't check for the data and then do the update. Simply DO the update first. If you update 0 rows, then you do the insert. This cuts exection in half when there is an update to be performed.

Problem 2)

IMMEDIATELY after the INSERT, put the @.@.ERROR and @.@.ROWCOUNT AND scope_identity() values into a variable. Then act on those three in appropriate order.

Problem 3)

You should use a remote sproc to do the update/insert stuff on the remote server. MUCH more efficient. Pass parameters in and then local stuff does work with a cached query plan and only one network trip.

|||Thanks for all your support. I'm going to give this a try|||Thank you for all your help. It worked.

I looked at your suggestions in your steps(problems) and immediately saw the advantages of your suggestions.

I will now adapt future stored procedure's templates to include those good practices. Thanks once more.

Regards
Mike

Can't seem to get the newly created entry's identity field :(

Hi there,

I have a stored procedure that executes a transaction which updates/inserts into a table on a remote server and should then use that new entry's autogenerated primary key to update another table.
I try to store that key into a variable called @.NewlyCreatedPastelDClinkNumber but to no avail

I have used the @.@.Identity approach and now try to manually get that key to from other information but still no go..My thinking is: since i am using transactions, maybe the entry cant be found until the transaction has been committed...but surely it should find it after the commit statement? Here is my code: I have marked the interesting bit with blue for simplicity.

ALTER procedure [dbo].[inv_SynchronizeClientsWithPastel]
as
BEGIN
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
SET XACT_ABORT ON

DECLARE @.PastelDCLinkNumber int
DECLARE @.ClientID int
DECLARE @.Name varchar(25)
DECLARE @.Surname nvarchar(25)
DECLARE @.POBoxAddr1 varchar(40)
DECLARE @.POBoxAddr2 varchar(40)
DECLARE @.PostalCode varchar(5)
DECLARE @.ClientCreditCardNumber nvarchar(50)
DECLARE @.NewlyCreatedPastelDClinkNumber int
DECLARE PastelClientPortCursor CURSOR FOR

SELECT id, [Name], Surname, POBoxAddr1, POBoxAddr2, PostalCode, PastelDCLinkNumber ,CreditCardNumber
FROM inc_Client
WHERE HasSynchronizedWithPastel = 0

OPEN PastelClientPortCursor;
FETCH NEXT FROM PastelClientPortCursor
INTO @.ClientID,@.Name,@.Surname,@.POBoxAddr1,@.POBoxAddr2,@.PostalCode,@.PastelDCLinkNumber,@.ClientCreditCardNumber
WHILE @.@.FETCH_STATUS = 0
BEGIN

--Step2 insert/update the client information into the Synergy invoice and client table
BEGIN TRANSACTION T1
IF Exists(SELECT DCLink from [server].[Satra Corporate (Pty) Ltd].dbo.Client WHERE DCLink = @.PastelDCLinkNumber)

--Update the Pastel Client table

Begin
Update [server].[Satra Corporate (Pty) Ltd].dbo.Client SET
Name = (@.Name + ' ' + @.Surname),
Account = (@.Name + ' ' + @.Surname),
Post1 = @.POBoxAddr1,
Post2 = @.POBoxAddr2,
PostPC = @.PostalCode,
cAccDescription = 'Credit Card Number: ' + @.ClientCreditCardNumber
WHERE DCLink = @.PastelDCLinkNumber
End

ELSE
Begin
--Insert into the Pastel Client table
INSERT INTO [server].[Satra Corporate (Pty) Ltd].dbo.Client
(Account, Name, Post1, Post2, PostPC, AccountTerms, CT, Credit_Limit,
RepID, Interest_Rate, Discount, On_Hold, BFOpenType, BankLink,
AutoDisc,
DiscMtrxRow, CashDebtor, DCBalance, CheckTerms, UseEmail,
iCountryID, cAccDescription, iCurrencyID, bStatPrint,
bStatEmail, bForCurAcc,
fForeignBalance, bTaxPrompt, iARPriceListNameID)
VALUES
(
SUBSTRING(@.Name, 1,19), --Account
(@.Name + ' ' + @.Surname), --Name
@.POBoxAddr1, --Post1
@.POBoxAddr2, --Post2
@.PostalCode, --PostPC
0, --Account_Terms
1, --CT
0,--Credit_Limit
0, --Rep_ID
0, --interest_Rate
0, --Discount
0, --On_Hold
0, --BFOpenType
0, --BankLink
0, --AutoDisc
0, --DiscMatrix
0, --CashDebtor
0, --DCBalance
1, --CheckTerms
0, --UseEmail
0, --iCountryID
'Credit Card Number: ' + @.ClientCreditCardNumber,
0, --iCurrencyID
1, --StatPrint
0, --StatEmail
0, --bForCurAcc
0, --ForeignBal
1, --bTaxPrompt
1 --iARPriceListNameID
)
End

--If the rowcount is 0, an error has occured
IF @.@.ROWCOUNT <> 0
Begin
COMMIT TRANSACTION T1
execute sec_LogSQLTransaction 'inv_SynchronizeClientsWithPastel' ,'inc_Client_id',@.ClientID,'System'
-- Step 3Update the HasSynchronizedWithPastel Flag

Begin
SET @.NewlyCreatedPastelDClinkNumber = (SELECT DCLink From [server].[Satra Corporate (Pty) Ltd].dbo.Client
WHERE Name = (@.Name + ' ' + @.Surname)AND
Account = (@.Name + ' ' + @.Surname)AND
Post1 = @.POBoxAddr1 AND
Post2 = @.POBoxAddr2 AND
PostPC = @.PostalCode AND
cAccDescription = 'Credit Card Number: ' + @.ClientCreditCardNumber)
End

Begin
Update inc_Client SET
HasSynchronizedWithPastel = 1 ,
PastelDCLinkNumber = @.NewlyCreatedPastelDClinkNumber
WHERE id = @.ClientID
End
End

--Rollback transaction
IF @.@.TRANCOUNT > 0
Begin
ROLLBACK TRANSACTION T1
execute sec_LogSQLTransactionError 'inv_SynchronizeClientsWithPastel','inc_Client_id',@.ClientID,'System'
End

FETCH NEXT FROM PastelClientPortCursor
INTO @.ClientID,@.Name,@.Surname,@.POBoxAddr1,@.POBoxAddr2,@.PostalCode,@.PastelDCLinkNumber, @.ClientCreditCardNumber
END
CLOSE PastelClientPortCursor;
DEALLOCATE PastelClientPortCursor;

END

SET XACT_ABORT OFF
Go

Instead of:

COMMIT TRANSACTION T1
execute sec_LogSQLTransaction 'inv_SynchronizeClientsWithPastel' ,'inc_Client_id',@.ClientID,'System'
-- Step 3Update the HasSynchronizedWithPastel Flag

Begin
SET @.NewlyCreatedPastelDClinkNumber = (SELECT DCLink From [server].[Satra Corporate (Pty) Ltd].dbo.Client
WHERE Name = (@.Name + ' ' + @.Surname)AND
Account = (@.Name + ' ' + @.Surname)AND
Post1 = @.POBoxAddr1 AND
Post2 = @.POBoxAddr2 AND
PostPC = @.PostalCode AND
cAccDescription = 'Credit Card Number: ' + @.ClientCreditCardNumber)
End

Try:

COMMIT TRANSACTION T1

SET @.NewlyCreatedPastelDClinkNumber = scope_identity()

execute sec_LogSQLTransaction 'inv_SynchronizeClientsWithPastel' ,'inc_Client_id',@.ClientID,'System'
-- Step 3Update the HasSynchronizedWithPastel Flag

|||

Problem 1)

IF Exists(SELECT DCLink from [server].[Satra Corporate (Pty) Ltd].dbo.Client WHERE DCLink = @.PastelDCLinkNumber)

--Update the Pastel Client table

Begin
Update [server].[Satra Corporate (Pty) Ltd].dbo.Client SET

Don't check for the data and then do the update. Simply DO the update first. If you update 0 rows, then you do the insert. This cuts exection in half when there is an update to be performed.

Problem 2)

IMMEDIATELY after the INSERT, put the @.@.ERROR and @.@.ROWCOUNT AND scope_identity() values into a variable. Then act on those three in appropriate order.

Problem 3)

You should use a remote sproc to do the update/insert stuff on the remote server. MUCH more efficient. Pass parameters in and then local stuff does work with a cached query plan and only one network trip.

|||Thanks for all your support. I'm going to give this a try|||Thank you for all your help. It worked.

I looked at your suggestions in your steps(problems) and immediately saw the advantages of your suggestions.

I will now adapt future stored procedure's templates to include those good practices. Thanks once more.

Regards
Mikesql

Can''t seem to get the newly created entry''s identity field :(

Hi there,

I have a stored procedure that executes a transaction which updates/inserts into a table on a remote server and should then use that new entry's autogenerated primary key to update another table.
I try to store that key into a variable called @.NewlyCreatedPastelDClinkNumber but to no avail

I have used the @.@.Identity approach and now try to manually get that key to from other information but still no go..My thinking is: since i am using transactions, maybe the entry cant be found until the transaction has been committed...but surely it should find it after the commit statement? Here is my code: I have marked the interesting bit with blue for simplicity.

ALTER procedure [dbo].[inv_SynchronizeClientsWithPastel]
as
BEGIN
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
SET XACT_ABORT ON

DECLARE @.PastelDCLinkNumber int
DECLARE @.ClientID int
DECLARE @.Name varchar(25)
DECLARE @.Surname nvarchar(25)
DECLARE @.POBoxAddr1 varchar(40)
DECLARE @.POBoxAddr2 varchar(40)
DECLARE @.PostalCode varchar(5)
DECLARE @.ClientCreditCardNumber nvarchar(50)
DECLARE @.NewlyCreatedPastelDClinkNumber int
DECLARE PastelClientPortCursor CURSOR FOR

SELECT id, [Name], Surname, POBoxAddr1, POBoxAddr2, PostalCode, PastelDCLinkNumber ,CreditCardNumber
FROM inc_Client
WHERE HasSynchronizedWithPastel = 0

OPEN PastelClientPortCursor;
FETCH NEXT FROM PastelClientPortCursor
INTO @.ClientID,@.Name,@.Surname,@.POBoxAddr1,@.POBoxAddr2,@.PostalCode,@.PastelDCLinkNumber,@.ClientCreditCardNumber
WHILE @.@.FETCH_STATUS = 0
BEGIN

--Step2 insert/update the client information into the Synergy invoice and client table
BEGIN TRANSACTION T1
IF Exists(SELECT DCLink from [server].[Satra Corporate (Pty) Ltd].dbo.Client WHERE DCLink = @.PastelDCLinkNumber)

--Update the Pastel Client table

Begin
Update [server].[Satra Corporate (Pty) Ltd].dbo.Client SET
Name = (@.Name + ' ' + @.Surname),
Account = (@.Name + ' ' + @.Surname),
Post1 = @.POBoxAddr1,
Post2 = @.POBoxAddr2,
PostPC = @.PostalCode,
cAccDescription = 'Credit Card Number: ' + @.ClientCreditCardNumber
WHERE DCLink = @.PastelDCLinkNumber
End

ELSE
Begin
--Insert into the Pastel Client table
INSERT INTO [server].[Satra Corporate (Pty) Ltd].dbo.Client
(Account, Name, Post1, Post2, PostPC, AccountTerms, CT, Credit_Limit,
RepID, Interest_Rate, Discount, On_Hold, BFOpenType, BankLink,
AutoDisc,
DiscMtrxRow, CashDebtor, DCBalance, CheckTerms, UseEmail,
iCountryID, cAccDescription, iCurrencyID, bStatPrint,
bStatEmail, bForCurAcc,
fForeignBalance, bTaxPrompt, iARPriceListNameID)
VALUES
(
SUBSTRING(@.Name, 1,19), --Account
(@.Name + ' ' + @.Surname), --Name
@.POBoxAddr1, --Post1
@.POBoxAddr2, --Post2
@.PostalCode, --PostPC
0, --Account_Terms
1, --CT
0,--Credit_Limit
0, --Rep_ID
0, --interest_Rate
0, --Discount
0, --On_Hold
0, --BFOpenType
0, --BankLink
0, --AutoDisc
0, --DiscMatrix
0, --CashDebtor
0, --DCBalance
1, --CheckTerms
0, --UseEmail
0, --iCountryID
'Credit Card Number: ' + @.ClientCreditCardNumber,
0, --iCurrencyID
1, --StatPrint
0, --StatEmail
0, --bForCurAcc
0, --ForeignBal
1, --bTaxPrompt
1 --iARPriceListNameID
)
End

--If the rowcount is 0, an error has occured
IF @.@.ROWCOUNT <> 0
Begin
COMMIT TRANSACTION T1
execute sec_LogSQLTransaction 'inv_SynchronizeClientsWithPastel' ,'inc_Client_id',@.ClientID,'System'
-- Step 3Update the HasSynchronizedWithPastel Flag

Begin
SET @.NewlyCreatedPastelDClinkNumber = (SELECT DCLink From [server].[Satra Corporate (Pty) Ltd].dbo.Client
WHERE Name = (@.Name + ' ' + @.Surname)AND
Account = (@.Name + ' ' + @.Surname)AND
Post1 = @.POBoxAddr1 AND
Post2 = @.POBoxAddr2 AND
PostPC = @.PostalCode AND
cAccDescription = 'Credit Card Number: ' + @.ClientCreditCardNumber)
End

Begin
Update inc_Client SET
HasSynchronizedWithPastel = 1 ,
PastelDCLinkNumber = @.NewlyCreatedPastelDClinkNumber
WHERE id = @.ClientID
End
End

--Rollback transaction
IF @.@.TRANCOUNT > 0
Begin
ROLLBACK TRANSACTION T1
execute sec_LogSQLTransactionError 'inv_SynchronizeClientsWithPastel','inc_Client_id',@.ClientID,'System'
End

FETCH NEXT FROM PastelClientPortCursor
INTO @.ClientID,@.Name,@.Surname,@.POBoxAddr1,@.POBoxAddr2,@.PostalCode,@.PastelDCLinkNumber, @.ClientCreditCardNumber
END
CLOSE PastelClientPortCursor;
DEALLOCATE PastelClientPortCursor;

END

SET XACT_ABORT OFF
Go

Instead of:

COMMIT TRANSACTION T1
execute sec_LogSQLTransaction 'inv_SynchronizeClientsWithPastel' ,'inc_Client_id',@.ClientID,'System'
-- Step 3Update the HasSynchronizedWithPastel Flag

Begin
SET @.NewlyCreatedPastelDClinkNumber = (SELECT DCLink From [server].[Satra Corporate (Pty) Ltd].dbo.Client
WHERE Name = (@.Name + ' ' + @.Surname)AND
Account = (@.Name + ' ' + @.Surname)AND
Post1 = @.POBoxAddr1 AND
Post2 = @.POBoxAddr2 AND
PostPC = @.PostalCode AND
cAccDescription = 'Credit Card Number: ' + @.ClientCreditCardNumber)
End

Try:

COMMIT TRANSACTION T1

SET @.NewlyCreatedPastelDClinkNumber = scope_identity()

execute sec_LogSQLTransaction 'inv_SynchronizeClientsWithPastel' ,'inc_Client_id',@.ClientID,'System'
-- Step 3Update the HasSynchronizedWithPastel Flag

|||

Problem 1)

IF Exists(SELECT DCLink from [server].[Satra Corporate (Pty) Ltd].dbo.Client WHERE DCLink = @.PastelDCLinkNumber)

--Update the Pastel Client table

Begin
Update [server].[Satra Corporate (Pty) Ltd].dbo.Client SET

Don't check for the data and then do the update. Simply DO the update first. If you update 0 rows, then you do the insert. This cuts exection in half when there is an update to be performed.

Problem 2)

IMMEDIATELY after the INSERT, put the @.@.ERROR and @.@.ROWCOUNT AND scope_identity() values into a variable. Then act on those three in appropriate order.

Problem 3)

You should use a remote sproc to do the update/insert stuff on the remote server. MUCH more efficient. Pass parameters in and then local stuff does work with a cached query plan and only one network trip.

|||Thanks for all your support. I'm going to give this a try|||Thank you for all your help. It worked.

I looked at your suggestions in your steps(problems) and immediately saw the advantages of your suggestions.

I will now adapt future stored procedure's templates to include those good practices. Thanks once more.

Regards
Mike

Can't retrieve second resultset with jdbc sql server 2005 driver

Hi All,

My java application executes one stored procedure that returns two resultsets and then deal with data. The code actually is very simple

Statement stmt = con.createStatement(); con is database connection

ResultSet rs = stmt.getResultSet();

//Do something

rs.close();

rs = stmt.getResultSet();

//Do something

Then I got the error: The result set is closed.

The code worked well with jdbc sql server 2000 driver.

Thanks for your help.

Eric

Hello lxiao,

The syntax for getting the second result set is

if(stmt.getMoreResults())

stmt.getResultSet();

Keep in mind that the getMoreResults() methods takes multiple arguments which controls how visited result sets should persist. Those flags are:

CLOSE_CURRENT_RESULT

CLOSE_ALL_RESULTS

You can find more information about using multiple result sets objects at the following site http://msdn2.microsoft.com/en-us/library/ms378758.aspx

Regards,

Jaaved Mohammed

Can't retrieve second resultset with jdbc sql server 2005 driver

Hi All,

My java application executes one stored procedure that returns two resultsets and then deal with data. The code actually is very simple

Statement stmt = con.createStatement(); con is database connection

ResultSet rs = stmt.getResultSet();

//Do something

rs.close();

rs = stmt.getResultSet();

//Do something

Then I got the error: The result set is closed.

The code worked well with jdbc sql server 2000 driver.

Thanks for your help.

Eric

Hello lxiao,

The syntax for getting the second result set is

if(stmt.getMoreResults())

stmt.getResultSet();

Keep in mind that the getMoreResults() methods takes multiple arguments which controls how visited result sets should persist. Those flags are:

CLOSE_CURRENT_RESULT

CLOSE_ALL_RESULTS

You can find more information about using multiple result sets objects at the following site http://msdn2.microsoft.com/en-us/library/ms378758.aspx

Regards,

Jaaved Mohammed