Can't start SQLEXPRESS so that I can't install latest SQLEXPRESS security updates.

I can't start SQLEXPRESS.

The SQL ERRORLOG shows: Error is 3414, Severity 21, State 2 and says: "An error occurred during recovery, preventing the database 'model' (database ID 3) from restarting." Just prior to this, I get a warning: "did not see LP_CKPT_END".

Any thoughts why this might be and how I can fix this?

Seems like something happened to your master database, check out the BOL topic on restoring master for starters.

Mike

|||

Thank you for your thoughts.

Unfortunately, I am not able to restore the database because I can't start SQLEXPRESS. I have even tried the command line sqlservr -s SQLEXPRESS command line command without success.

I continue to get this warning "did not see LP_CKPT_END" Error:3414, Severity:21, State: 1.

It might help to know that I have never used the SQLEXPRESS database on my box before. (I also have an instance of MSSQLSERVER and that is what I have been using. MSSQLSERVER starts up without issue.)

I'm not sure what it is that I would be restoring as I haven't created any databases in SQLEXPRESS.

The issue began when I update MSSQLSERVER with SP2 (KB 933508) install package. It seemed to take fine. I also did the install after March 5, 2007 -- a critical date for this update.

When the SQL 2005 Express SP2 (KB 921896) came out, it would load because it couldn't start an instance of SQLEXPRESS.

Does this help explain the background better? Your further suggestions would be helpful.

|||

This one is beyond me. Moving to the setup forum to ask the experts.

Mike

Can't start SQL 2000 after MS Update

Hi,
I recently installed several Windows Updates and security
patches. Now the SQL Server on my XP Pro development
machine won't start. The only message box that I get
when trying to restart is a message box:
SQL Server does not exist or access denied.
Any clues on how to fix this? I can't seem to find any
other references to this problem.
Regards,
DougSQL Server does not exist or access denied is a generic error message for a
failed connection.
If the MSSQLServer service is not started, then this makes sense.
Try running : Net start MSSQLServer
Check the account you're using to start MSSQLServer.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||I do have the SQLServer icon in the tray, stopped of
course.
I am logged in to the box as Doug with administrator
privledges, SQLServer is set to logon using Local System
account.
The exact message I get when trying to start from
Computer Management console is:
--
A connection could not be established to (local).
Reason: SQL Server does not exist or access denied.
ConnectionOpen (Connect())...
Please verify SQL Server is running and check your SQL
Server registration properties (by right-clicking on the
(local) node) and try again.
--
This had been working fine for months. Nothing that I
know of changed, other than these Windows Updates two or
three days ago when the problem started.
This is my development machine and I'm right in the
middle of a project, so I REALLY appreciate your help
here.
Doug

>--Original Message--
>SQL Server does not exist or access denied is a generic
error message for a
>failed connection.
>If the MSSQLServer service is not started, then this
makes sense.
>Try running : Net start MSSQLServer
>Check the account you're using to start MSSQLServer.
>Thanks,
>Kevin McDonnell
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and
confers no rights.
>
>.
>|||Doug,
The MSSQLServer service needs to be running in order to connect.
Verify that the MSSQLServer service is running / started.
If you continue to have problems connecting, you should open a support case
with SQL Server.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Kevin,
Please don't keep telling me that I need to start before
I can connect. I know that. As I mentioned before, I
CAN'T start or connect. THAT IS the problem.
Something happened, probably during an Update. I don't
know what else could have happened.
HELP. Please! Anyone!!!!
Doug

>--Original Message--
>Doug,
> The MSSQLServer service needs to be running in order
to connect.
>Verify that the MSSQLServer service is running / started.
>If you continue to have problems connecting, you should
open a support case
>with SQL Server.
>Thanks,
>Kevin McDonnell
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and
confers no rights.
>
>.
>|||Doug,
Go into Control Panel to services and try to start the service from
there. If it doesn't start, check the event logs and your SQL Server
error log (the errorlog file in the SQL Server installation path \Log
folder).
-Sue
On Fri, 21 May 2004 13:42:27 -0700,
<anonymous@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Kevin,
>Please don't keep telling me that I need to start before
>I can connect. I know that. As I mentioned before, I
>CAN'T start or connect. THAT IS the problem.
>Something happened, probably during an Update. I don't
>know what else could have happened.
>HELP. Please! Anyone!!!!
>Doug
>
>to connect.
>open a support case
>confers no rights.|||Hi! If you haven't solved your problem yet, try:
restoring your Windows XP to a previous state prior to the installation of
Windows patches. You can do this using System Restore of Windows XP.
Or, you can uninstall the patch(es) you had installed.
Or, try running sqlservr.exe from the command line and see if it generates a
more detailed error message.
HTH. =)
Ricky
<anonymous@.discussions.microsoft.com> wrote in message
news:105bb01c43f74$20bb2b70$a301280a@.phx
.gbl...[vbcol=seagreen]
> Kevin,
> Please don't keep telling me that I need to start before
> I can connect. I know that. As I mentioned before, I
> CAN'T start or connect. THAT IS the problem.
> Something happened, probably during an Update. I don't
> know what else could have happened.
> HELP. Please! Anyone!!!!
> Doug
>
> to connect.
> open a support case
> confers no rights.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.688 / Virus Database: 449 - Release Date: 5/18/2004

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