Inserting 2 tables with Pk/Fk

Hello all... I'm working on a C++ Windows service that writes to a SQL Server database. I consider myself quite a novice at SQL Server, but I have played around with it over the years... Performance is going to be a concern with this project.

Let's say...
Table A has columns PkA(identity), Stuff(text), FkB (Table B's Pk)
Table B has columns PkB(identity), MoreStuff(text)

I'll be executing SQL statements from my service - INSERTs, etc...

What's the most efficient way to write to these two tables? The immediate challenge I have is getting that PkB value after inserting Table B and using it for Table A's FkB.

Is there a way I can insert into both tables with one SQL statement?

Thanks!! Curt.First, I recommend that your service call a stored procedure to make this happen, and not issue an ad-hoc query. the sproc would do both inserts, you'd just call it with the values you need to put in Stuff and MoreStuff. So as far as your service is concerned, both inserts happen in "one statement". Within the sproc it's still two inserts though.

Second, in your sproc after your insert into tableB, you can call SCOPE_IDENTITY() to get the identity value that was just inserted. use this value as the fk in tableA when you do the insert there.

take a look at SCOPE_IDENTITY() in BOL. @.@.IDENTITY is a related beast, but SCOPE_IDENTITY() is preferred since it's scoped, as the name implies.

Edit: since I have my roots in C++ as well, thought I would add this: leaving your tables open to ad-hoc queries from client apps is like designing a class in C++ where all the fields are public. If your table structure changes, you have to recompile and redeploy your service. You wouldn't want to do that would you? :)

I think of sprocs as analogous to the public member functions on a class. use them to control how clients are allowed to manipulate the private fields (your tables), and make all fields (tables) private.|||jezemine, yeah I guess I should have qualfied that a bit more... We are in fact planning to put that into a sproc a little later on. As I mentioned, I'm not really a sql server pro and sprocs are on my list of items to conquer... Right now we just need to get something up and running to help prove concept. Thanks for the tips, though! Perhaps I'll conquer that beast sooner than I thought! :)|||ok, but remember that prototype code sometimes has a way of "sticking" :)

Insert to a Linked Server possible via Service Broker?

I have configured a non-SQL linked server (via an OLE DB provider) and I wish to insert data into it via Service Broker but I am getting the following error in the SQL Server log:

The activated proc [dbo].[sp_ mytableServiceProgram] running on queue TestDB.dbo.mytableQueue output the following:'Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction.'

As you see below, my strored proc. is not issuing any 'save trans' statements, so why is it not allowing me to wrap my code in a transaction? How else can I use a transaction (in order to not lose anything from the queue) and yet still be able to insert to the linked server?

CREATE PROC sp_mytableServiceProgram

AS

SET NOCOUNT ON;

DECLARE

@.XML XML,

@.MessageBody VARBINARY(MAX),

@.MessageTypeName nvarchar(256),

@.Dialog UNIQUEIDENTIFIER;

-- This procedure continues to process messages in the queue until the

-- queue is empty.

WHILE (1 = 1)

BEGIN

BEGIN TRANSACTION;

--BEGIN DISTRIBUTED TRANSACTION; --Tried this but didn't help.

-- Receive the next available message

WAITFOR (

RECEIVE TOP(1) -- just handle one message at a time

@.MessageTypeName = message_type_name,

@.MessageBody = message_body,

@.Dialog = conversation_handle

FROM mytableQueue

), TIMEOUT 2000 ;

-- If RECEIVE did not return a message, roll back the transaction

-- and break out of the while loop, exiting the procedure.

IF (@.@.ROWCOUNT = 0)

BEGIN

ROLLBACK TRANSACTION;

BREAK;

END ;

SET @.XML = CAST(@.MessageBody AS XML);

INSERT INTO LINKEDSERVER.dbname.user.mytable

SELECT tbl.rows.value('@.doc_no', 'INT') AS doc_no,

tbl.rows.value('@.queryid', 'NVARCHAR(50)') AS queryid,

tbl.rows.value('@.ar_num', 'NVARCHAR(50)') AS ar_num,

tbl.rows.value('@.status', 'NVARCHAR(20)') AS status,

tbl.rows.value('@.creationtime', 'DATETIME') AS creationtime,

tbl.rows.value('@.note', 'NVARCHAR(250)') AS note,

tbl.rows.value('@.posted', 'NCHAR(1)') AS posted,

tbl.rows.value('@.kms', 'INT') AS kms,

tbl.rows.value('@.schresid', 'NVARCHAR(50)') AS schresid,

tbl.rows.value('@.resolution_code', 'NCHAR(8)') AS resolution_code,

tbl.rows.value('@.page_count', 'INT') AS page_count,

tbl.rows.value('@.new_serial_number', 'NVARCHAR(20)') AS new_serial_number,

tbl.rows.value('@.taskresolution', 'NVARCHAR(250)') AS taskresolution

FROM @.XML.nodes('/inserted') tbl(rows);

-- If the INSERT did not insert any rows, rollback.

IF @.@.ROWCOUNT = 0

BEGIN

ROLLBACK TRANSACTION;

BREAK;

END

COMMIT TRANSACTION;

END

GO

Is the procedure runnning fine if is invoked from a user connection (e.g. using exec sp_mytableServiceProgram from a Management Studio query) but it fails when called from activation context?

Also, what OLDDB provider are you using for the linked server?

BTW, your procedure should handle the [http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog] and [http://schemas.microsoft.com/SQL/ServiceBroker/Error] message types.

HTH,
~ Remus

|||

No, I see the same error when run as myself (but with more detail):

Msg 3933, Level 16, State 1, Procedure sp_mytableServiceProgram, Line 38

Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction.

Line #38 is the insert to the linked server and the OLEDB provider is set to 'Ifxoledbc' which is for an IBM Informix database server.

And yes, you are correct, I do handle the other two message types, (just removed for posting simplicity).

Is the WAITFOR...RECEIVE statement perhaps doing an implicit SAVE TRANS for each and every message that it receives?

Regards,

Ron

|||

Is the WAITFOR that creates an internal savepoint. Remove the WAITFOR and it should work, using a plain RECEIVE. It does work for me, using an SQLNCLI linked server.

HTH,
~ Remus

|||

Right, I was able to see the same thing, removing WAITFOR allowed the insert to the linked server to go through.

But I wonder what are the repercussions to not using WAITFOR are?

Thanks,

Ron

|||

WAITFOR in activated procedures has the purpose of lingering the proedure around for a few seconds when he queue is empty, in hope another message comes anew. This avoids the situation when the rate of incoming messages is just slow enough to keep activating the procedure immedeatly after it just finished.

You should be fine, the benefits of WAITFOR show up only at one particular rate of incomming messages and the gains offered by it are not earth shatering in the first place anyway...

HTH,
~ Remus

|||Excellent, thank you.sql