SP not working the same SQL 2000 vs SQL 7.0 when exec from Access
-
We have changed from SQL Server 7.0 to SQL Server 2000 as our back end to an Access application.
We execute a stored procedure from the Access front end that inserts a record with an identity field into a batch control table. The identity field is the batch control number.
We have @BatchID as an output variable set to 0 at the start of the sp. After the Insert command we set @BatchID = @@IDENTITY. The sp returns a NULL value which I believe means that the record was not written as yet.
This worked fine in SQL 7.0.
The record is created when I look at the table.
Is this a timing issue? Is there something I should be looking out for in SQL 2000 with the execution of SPs?
Please help.
Thank you in advance for any help.
Somewhat new to SQL sp.
the-developer. Fred
-
Do you have any triggers set on that table?
You may want to change
@BatchID = @@IDENTITY
for
@BatchID = SCOPE_IDENTITY( )
HTH
* Noel -
Thanks for your quick response.
Tried Scope_Identity and the result was still null.
I will check into the triggers.
Thank you.
the-developer
-
can you post the contents of the sp ?
* Noel -
Here is the sp:
/****** Object: Stored Procedure dbo.INTERFACE_BATCH_CREATE Script Date: 3/28/2002 12:57:08 PM ******/
/******** Altered 4/23/02 For RV & BK Invoices *********/
/******** Altered 4/26/02 For Warnings *********/
CREATE PROCEDURE INTERFACE_BATCH_CREATE
(
@BatchID int = 0 OUTPUT,
@UserID varchar(20),
@InterfaceFileID int,
@Description varchar(255)
)
AS
DECLARE
@SysName varchar(50),
@CreateDate char(23),
@TransType varchar(4),
@NumInBatch int
SET @CreateDate = CURRENT_TIMESTAMP
-- Check if any batches are in process for this export type. If so then don't allow this new batch to be created.
SELECT @NumInBatch = Count(ID) FROM BATCH_CONTROL WHERE InterfaceFileID = @InterfaceFileID AND BatchStatus = 'BPIB'
IF @NumInBatch > 0
BEGIN
SET @BatchID = 2
RETURN (@BatchID)
END
BEGIN TRANSACTION
-- Create Batch Record
INSERT INTO BATCH_CONTROL (InterfaceFileID,Description,BatchStatus,CreateBy,CreateDate,UpdateBy,UpdateDate)
VALUES (@InterfaceFileID,@Description,'BPIB',@UserID,@CreateDate,@UserID,@CreateDate)
-- check for errors
IF @@Error <> 0
BEGIN
GOTO ERR_HANDLER
END
-- get ID of batch_control record just inserted
SET @BatchID = @@IDENTITY
-- get the systemname based on the interfacefileID
SELECT @SysName = SystemName FROM INTERFACE_FILE WHERE ID = @InterfaceFileID
SELECT @TransType =
CASE @SysName
WHEN 'CASHRECEIPTEXPORT' THEN 'CR'
WHEN 'ARINVOICEEXPORT' THEN 'IN'
WHEN 'GENERALJOURNALEXPORT' THEN 'GJ'
WHEN 'WIREEXPORT' THEN 'WI'
WHEN 'RECAPAPVOUCHEREXPORT' THEN 'AP'
ELSE '??'
END
-- update tables with batch information based on what type of export is being done
IF @SysName = 'VENDOREXPORT'
BEGIN
-- this will flag all vendor records that will be exported to MAS90 with the BatchID and BatchStatus
-- only vendor records with a NULL BatchControlID will be affected
UPDATE VENDORTABLE SET VendorTable.BatchControlID = @BatchID, VendorTable.BatchStatus = 'BPIB'
WHERE VendorTable.BatchControlID IS NULL OR VendorTable.BatchControlID = 0
IF @@ROWCOUNT = 0
BEGIN
ROLLBACK TRANSACTION
SET @BatchID = 4
RETURN (@BatchID)
END
else commit transaction
END
ELSE -- will handle all other financial data exports from RECAP
BEGIN
/*
Edited out 5/3/01 ckindt And Then Again on 4/23/02
-- update the batchsent field in the table: BatchNumberTable
UPDATE BatchNumberTable SET BatchSent = 1
FROM BatchNumberTable INNER JOIN ExpenseEntryTable ON ExpenseEntryTable.BatchNo = BatchNumberTable.BatchNo
WHERE BatchNumberTable.BatchComplete = 1
AND (ExpenseEntryTable.BatchControlID IS NULL OR ExpenseEntryTable.BatchControlID = 0)
*/
-- update the "voucher" table with the batchID and status for "vouchers" that are in posted batches that have not been sent to MAS90
if @TransType = 'IN'
begin
--Update for IN RV and BK's
UPDATE ExpenseEntryTable SET ExpenseEntryTable.BatchControlID = @BatchID,BatchStatus = 'BPIB',SentToGL = 1
FROM ExpenseEntryTable
WHERE (ExpenseEntryTable.BatchControlID IS NULL OR ExpenseEntryTable.BatchControlID = 0)
AND ExpenseEntryTable.TransactionType in (@TransType,'RV','BK')
end
else
begin
UPDATE ExpenseEntryTable SET ExpenseEntryTable.BatchControlID = @BatchID,BatchStatus = 'BPIB',SentToGL = 1
FROM ExpenseEntryTable
WHERE (ExpenseEntryTable.BatchControlID IS NULL OR ExpenseEntryTable.BatchControlID = 0)
AND ExpenseEntryTable.TransactionType = @TransType
end
-- update the individual expenses for the voucher/invoice with the batchID and status of its parent
UPDATE PropertyExpenseDetailTable SET BatchControlID = @BatchID,BatchStatus = 'BPIB',SentToGL = 1,
UpdateBy = @user-id,UpdateDate = @CreateDate
WHERE ExpenseEntryID IN (SELECT ExpenseEntryID FROM ExpenseEntryTable WHERE ExpenseEntryTable.BatchControlID = @BatchID)
-- If no expenses updated then none are in the batch so don't create the batch
IF @@ROWCOUNT = 0
BEGIN
ROLLBACK TRANSACTION
SET @BatchID = 3
RETURN (@BatchID)
END
END
IF @@Error <> 0
BEGIN
GOTO ERR_HANDLER
END
--COMMIT TRANSACTION
RETURN (@BatchID)
ERR_HANDLER:
ROLLBACK TRANSACTION
SET @BatchID = 1
RETURN (@BatchID)
GO
****************************************************************************
Thanks
the-developer
-
Are you sure that the table BATCH_CONTROL has an IDENTITY Field?
BTW when you check for errors after the INSERT The Statement should be
SELECT @Er=@@ERROR, @BATCHID= @@IDENTITY -- Just one line !!
IF @Er <> 0 ....
* Noel -
try to debug this SP by doing this:
1- open query Analyzer ..
2- right click on your stored procedure .. and choose debug ...
(if a message appears tells you that you use a local account .. then you will not be able to debug your SP)
3- write the default values for each variable you pass in to your SP
4- then trace line by line in your SP .. and you can see all local variables changed values in the left pane of sceen.
I hope this help u
Alamir Mohamed
Alamir_mohamed@yahoo.com -
Noeld,
I checked the table and it does have an identity field and there ate no triggers associated with this table.
**********************************
Alamir,
Thanks for the suggestion. I will be trying it out in the next hour or so.
***********************************
I also ran into an interesting thing in some of the Access queries:
With SQL 2000 any query testing a boolean field with a -1 is comming up with an empty record set as a result. When I use "True" or "Yes" I get the resulting record set. I also noticed that SQL server itself uses 1 for true. Is -1 an Access thing?
Thanks for your help so far.
the-developer
-
I also ran into an interesting thing in some of the Access queries:
With SQL 2000 any query testing a boolean field with a -1 is comming up with an empty record set as a result. When I use "True" or "Yes" I get the resulting record set. I also noticed that SQL server itself uses 1 for true. Is -1 an Access thing?
The -1 is a VBA thing is the representation for "true" in vb. When you use True in your queries (at the client side) access actually translates that value to 1 and sql Server (on the sever side, of course
) sees only the 1.on the Client side In access you can also use 1 to Identify True
Access is "smart" enough not to translate it
* Noel -
Noled and Alamir:
Noeld,
Thanks Noeld for the info. I have been changing the queries based on using the x-Ref in Access Analyzer from FMS to find the queries that contain the Yes/No fields. What I can't understand is why the -1 worked in SQL 7.0 but in SQL 2000 it is not.
Alamir,
I ran the debug and the sp returned the identity field in debug mode. Could it be a timing or connection issue since users are sometimes getting timeout errors in Access screens? Are there any standards for connection timeouts?
Thanks again. I appreciate you assistance.
the-developer
-
there are many another posible things you have to check :
1- you must set the bit datafiled to NOT Null .. and so give it a default value .. it is a bug in SQL Server with MS Access that YOU HAVE TO FIX IT, because there are 3 values for bit field : YES, No and NULL (get rid of it).. and then don't forget to refresh the table in Access again.
2- restart the server ... sometimes SQL Server gets in stravge modes and then you have to restart it
3- search for any option you may set that disable the @identity work
I hope this help u.
Alamir Mohamed
Alamir_mohamed@yahoo.com
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply