We help IT Professionals succeed at work.

Two insert procedure revisions

dbaSQL
dbaSQL asked
on
362 Views
Last Modified: 2008-03-06
I need some pretty decent help w/the insert proc below.  in short, we trade electronically, sometimes there is a need to manually insert trades into the database, and we have three datasets that are to be kept in synch at all times.  right now, if/when it is needed, the proc below is fired three times to hit the three different servers.  (very problematic)  i have several other procedures similar in this nature, that need to impact all three instances --- i fire them off with a 'driver'.  This is a small example of one of my driver procs:

IF EXISTS(SELECT 1 FROM Database.dbo.Current WHERE tradetime >=@tradetime and  ordernumber=@ordernumber)
BEGIN

INSERT Database.dbo.AuditTrail (ActionDate,ActionType,ActionSource,OrderNumber,Old,New,UserID,Hostname)

SELECT CURRENT_TIMESTAMP,'Quantity',CONVERT(char(20), SERVERPROPERTY('servername')),@ordernumber,quantity,@newqty,SYSTEM_USER,HOST_NAME()
FROM Database.dbo.Current
WHERE tradeTime >=@tradetime
AND ordernumber = @ordernumber
      
      EXEC SERVER1.Database.dbo.usp_procName @newqty, @ordernumber, @tradetime
      EXEC SERVER2.Database.dbo.usp_procName @newqty, @ordernumber, @tradetime
      EXEC SERVER3.Database.dbo.usp_procName @newqty, @ordernumber, @tradetime

END

You see, the drivers are coded such that they invoke the editing procedure(s) on the three instances.  my people invoke the driver once, it hits all three boxes.  i need to do that w/this insert procedure, but i am struggling around all of the defaults.  Is anyone able to help me put a driver around this?

That's problem #1.  The second issue is a little harder to describe.  It may simply not be an option, but I need to check.  As you can see, @source defaults to 'FImporter'.  Of course, this can be passed in, if needed.  I was wondering, IF @source = 'ROLLTRADE', can I go just a step further?  If @source=ROLLTRADE, the user also needs to submit a settledate for the trade they just inserted. (@settledate, @ordernumber)   I want the gui to do this with as little intervention from the trading desk, as possible.  From their point of view, they enter all the information at once, and hit one button to submit.   But, IF @source = ROLLTRADE, is there anyway I can then proceed to retrieve the ordernumber (just created) and pass it with @SettleDate to another proc, that will write to another table, without any intervention on the part of the desk?


both are very important, though #2 is a tiny bit more pressing than #1.  any suggestions?


CREATE PROCEDURE dbo.insertProc
(
   @MsgID varchar(175),
   @UserId varchar(16),
   @AcctId varchar(16),  
   @PrevOrderNo varchar(32)='?',
   @EndPoint varchar(8),
   @Symbol varchar(25),
   @BuySell char(1),
   @Quantity int,
   @Price decimal(18,8),
   @TradeTime datetime=NULL,
   @ClearingFee int = 0,
   @LiquidityFlag char(4)='?',
   @Client varchar(32)='MET',
   @OrderNumber varchar(100),
   @OrigOrderNo varchar(32)='?',
   @BasketId char(12)='?',
   @BasketNumber varchar(15)='0',
   @Quantity bigint=NULL,
   @ExecutedQuantity bigint=NULL,
   @LeavesQuantity bigint=0,
   @OrderType char(1)='L',
   @LimitPrice decimal(18,8)=NULL,
   @AvgFillPrice decimal(18,8)=NULL,
   @OrderStatus varchar(4)='FILL',
   @StopPrice decimal(18,8)=0.0,
   @TimeInForce varchar(5)='DAY',
   @cntrpartyID varchar(15)='?',
   @HostName varchar(50)=NULL,
   @OrderMarking char(1)=NULL,
   @Source varchar(15)='FImporter'
)
AS
SET NOCOUNT ON
BEGIN

  --if @tradetime isn't passed, default to today
  IF (@TradeTime IS NULL)
  SET @TradeTime = GETDATE()
  --set these all for defaults
  SET @quantity = COALESCE(@quantity, @quantity)
  SET @executedquantity = COALESCE(@quantity, @executedquantity)
  SET @limitprice = COALESCE(@price,@limitprice)
  SET @avgfillprice = COALESCE(@price,@avgfillprice)
  SET @hostname = HOST_NAME()

BEGIN TRANSACTION

  IF DATEDIFF(day,@TradeTime,getdate())=0
  /* if date is current day, insert only into Database.dbo.Current */
  BEGIN
INSERT Database.dbo.Current (
[MsgID], [UserId],[AcctId],[PrevOrderNo],[EndPoint],[Symbol],[BuySell],[Quantity],[Price],[TradeTime],[ClearingFee],
[LiquidityFlag],[Client],[OrderNumber],[OrigOrderNo],[BasketId],[BasketNumber],[Quantity],[ExecutedQuantity],[LeavesQuantity],[OrderType],
[LimitPrice],[AvgFillPrice],[OrderStatus],[StopPrice],[TimeInForce],[CntrPartyId],[HostName],[OrderMarking],[Source]  )
      VALUES (
@MsgId,@UserId,@AcctId,@PrevOrderNo,@EndPoint,@Symbol,@BuySell,@Quantity,@Price,@TradeTime,@ClearingFee,
@LiquidityFlag,@Client,@OrderNumber,@OrigOrderNo,@BasketId,@BasketNumber,@Quantity,@ExecutedQuantity,@LeavesQuantity,@OrderType,
@LimitPrice,@AvgFillPrice,@OrderStatus,@StopPrice,@TimeInForce,@cntrpartyID,@HostName,@OrderMarking,@Source   )

        IF @@ERROR <> 0
          BEGIN
            ROLLBACK TRAN
            RAISERROR('Failed to insert into Database.dbo.Current.',16,-1)
            RETURN
        END
  END
  ELSE
  /* If date within current business week, insert into Database.dbo.Current AND Database.dbo.Historical */
  IF DATEDIFF(day,@TradeTime,getdate()) BETWEEN 1 AND 7
  BEGIN
INSERT Database.dbo.Current (
[MsgID], [UserId],[AcctId],[PrevOrderNo],[EndPoint],[Symbol],[BuySell],[Quantity],[Price],[TradeTime],[ClearingFee],
[LiquidityFlag],[Client],[OrderNumber],[OrigOrderNo],[BasketId],[BasketNumber],[Quantity],[ExecutedQuantity],[LeavesQuantity],[OrderType],
[LimitPrice],[AvgFillPrice],[OrderStatus],[StopPrice],[TimeInForce],[CntrPartyId],[HostName],[OrderMarking],[Source]  )
      VALUES (
@MsgId,@UserId,@AcctId,@PrevOrderNo,@EndPoint,@Symbol,@BuySell,@Quantity,@Price,@TradeTime,@ClearingFee,
@LiquidityFlag,@Client,@OrderNumber,@OrigOrderNo,@BasketId,@BasketNumber,@Quantity,@ExecutedQuantity,@LeavesQuantity,@OrderType,@LimitPrice,
@AvgFillPrice,@OrderStatus,@StopPrice,@TimeInForce,@cntrpartyID,@HostName,@OrderMarking,@Source   )

        IF @@ERROR <> 0
          BEGIN
            ROLLBACK TRAN
            RAISERROR('Failed to insert into Database.dbo.Current.',16,-1)
            RETURN
        END

INSERT Database.dbo.Historical (
[MsgID], [UserId],[AcctId],[PrevOrderNo],[EndPoint],[Symbol],[BuySell],[Quantity],[Price],[TradeTime],[ClearingFee],
[LiquidityFlag],[Client],[OrderNumber],[OrigOrderNo],[BasketId],[BasketNumber],[Quantity],[ExecutedQuantity],[LeavesQuantity],[OrderType],
[LimitPrice],[AvgFillPrice],[OrderStatus],[StopPrice],[TimeInForce],[CntrPartyId],[HostName],[OrderMarking],[Source]  )
      VALUES (
@MsgId,@UserId,@AcctId,@PrevOrderNo,@EndPoint,@Symbol,@BuySell,@Quantity,@Price,@TradeTime,@ClearingFee,
@LiquidityFlag,@Client,@OrderNumber,@OrigOrderNo,@BasketId,@BasketNumber,@Quantity,@ExecutedQuantity,@LeavesQuantity,@OrderType,@LimitPrice,
@AvgFillPrice,@OrderStatus,@StopPrice,@TimeInForce,@cntrpartyID,@HostName,@OrderMarking,@Source   )

        IF @@ERROR <> 0
        BEGIN
            ROLLBACK TRAN
            RAISERROR('Failed to insert into Database.dbo.Historical.',16,-1)
            RETURN
        END
  END
  ELSE
  /* If date older than current business week, only insert into Database.dbo.Historical.*/
  IF DATEDIFF(day,@TradeTime,getdate())>7
  BEGIN
INSERT Database.dbo.Historical (
[MsgID], [UserId],[AcctId],[PrevOrderNo],[EndPoint],[Symbol],[BuySell],[Quantity],[Price],[TradeTime],[ClearingFee],
[LiquidityFlag],[Client],[OrderNumber],[OrigOrderNo],[BasketId],[BasketNumber],[Quantity],[ExecutedQuantity],[LeavesQuantity],[OrderType],
[LimitPrice],[AvgFillPrice],[OrderStatus],[StopPrice],[TimeInForce],[CntrPartyId],[HostName],[OrderMarking],[Source]  )
      VALUES (
@MsgId,@UserId,@AcctId,@PrevOrderNo,@EndPoint,@Symbol,@BuySell,@Quantity,@Price,@TradeTime,@ClearingFee,
@LiquidityFlag,@Client,@OrderNumber,@OrigOrderNo,@BasketId,@BasketNumber,@Quantity,@ExecutedQuantity,@LeavesQuantity,@OrderType,
@LimitPrice,@AvgFillPrice,@OrderStatus,@StopPrice,@TimeInForce,@cntrpartyID,@HostName,@OrderMarking,@Source   )
  END
        IF @@ERROR <> 0
        BEGIN
            ROLLBACK TRAN
            RAISERROR('Failed to insert into Database.dbo.Historical.',16,-1)
            RETURN
        END

        IF @@ERROR = 0
        BEGIN
            COMMIT TRANSACTION      
        END
END
GO
Comment
Watch Question

CERTIFIED EXPERT

Commented:
Why don't you just open 2 sepparate questions?
CERTIFIED EXPERT

Commented:
I am not sure I understand the question with the drivers? You need a sp that fires the inserts on all 3 servers?
CERTIFIED EXPERT

Commented:
Why this:

SET @quantity = COALESCE(@quantity, @quantity)
? id of no good.
CERTIFIED EXPERT

Commented:
For 2:

Why not just add the settledate as a param defaulted with NULL  and then test for the source if ROLLTRADE and use the settledate.

CREATE PROCEDURE dbo.insertProc
(
   @MsgID varchar(175),
   @UserId varchar(16),
   @AcctId varchar(16),  
   @PrevOrderNo varchar(32)='?',
   @EndPoint varchar(8),
   @Symbol varchar(25),
   @BuySell char(1),
   @Quantity int,
   @Price decimal(18,8),
   @TradeTime datetime=NULL,
   @ClearingFee int = 0,
   @LiquidityFlag char(4)='?',
   @Client varchar(32)='MET',
   @OrderNumber varchar(100),
   @OrigOrderNo varchar(32)='?',
   @BasketId char(12)='?',
   @BasketNumber varchar(15)='0',
   @Quantity bigint=NULL,
   @ExecutedQuantity bigint=NULL,
   @LeavesQuantity bigint=0,
   @OrderType char(1)='L',
   @LimitPrice decimal(18,8)=NULL,
   @AvgFillPrice decimal(18,8)=NULL,
   @OrderStatus varchar(4)='FILL',
   @StopPrice decimal(18,8)=0.0,
   @TimeInForce varchar(5)='DAY',
   @cntrpartyID varchar(15)='?',
   @HostName varchar(50)=NULL,
   @OrderMarking char(1)=NULL,
   @Source varchar(15)='FImporter',      
   @settledate datetime=NULL
)

IF @Source='ROLLTRADE' ...

or

CASE @Source
      WHEN 'ROLLTRADE' THEN @settledate
      ELSE <otherdate_here>
END

Author

Commented:
hi zberteoc.  i figured since they were both pertaining to the same proc, i would just open one inquiry.  i do hope this wasn't a bad call.  

>>You need a sp that fires the inserts on all 3 servers?
Yes, precisely.

I'm not sure I follow you re settledate.  See, the need to submit settledate is for the order just inserted.  if @source = ROLLTRADE, i need to write the order, then get the ordernumber just inserted, and pass it into another procedure along with the given @settledate

you know what i mean?

CERTIFIED EXPERT

Commented:
but you know the @OrderNumber because you passed it into the sp. If the settledate is also passed in the case of ROLLTRADE I don't see what the problem is, ayou already know both values.

Author

Commented:
oh gosh, i'm sorry.  no, @ordernumber is autogenerated upon insertion
in this particular case, (the need to manually enter trades) it's not know until the insert occurs
CERTIFIED EXPERT

Commented:
how can that be if you have it in the VALUES list in th insert statemens? That means is passed into the sp and it is inserted.



Author

Commented:
sorry, zberteoc, i didn't see your reply until just now.  
yes, the proc is written that way, but the @ordernumber itself is autogenerated at the gui -- i believe it actually calls another resource to retrieve the number (an orderid generator)
but this doesn't happen until the insert is executed
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
ok, i'm going to give this a shot, zberteoc.  i see what you're saying...

at the end of the proc, should I just

IF @Source='ROLLTRADE' ...
EXEC new proc?

syntactically, how would i do this?
CERTIFIED EXPERT

Commented:
exactly like that

IF @Source='ROLLTRADE'
  EXEC spNameHere @ordernumber, @sattledate


nothing else. When the IF condition is true the next sentence is executed. You need BEGIN ... END block only if you have more than one sentences to execute.

Author

Commented:
ok, i've done it, zberteoc.  exactly as you've suggested
seemingly quite simple, now that i have it in front of me
unfortunately, the testing isn't going to be done until the am.  but, i will let you know asap

Author

Commented:
still unable to get some testing done, zberteoc.  i apologize for the delay, but it will happen soon.

Author

Commented:
testing is complete, zberteoc.  it went perfectly.  
do you have any ideas on the driver?

>>You need a sp that fires the inserts on all 3 servers?
CERTIFIED EXPERT

Commented:
If you need this on 3 different servers at the same time what you'll have to do is to set up linked servers from the main server to the other 2 and then you can simply qualify a table like:

linkedserver.Database.dbo.Current,

where linkedserver is the name you set for the link, may be the same name with the server it links.

After that you need to add an insert statement fora each of these tables:

INSERT INTO database.owner.tablename ... (the one you have_ +

INSERT INTO linkedserver1.database.owner.tablename ...
and
INSERT INTO linkedserver2.database.owner.tablename ...


Hpw to set up linked servers you can find here:

http://www.databasejournal.com/features/mssql/article.php/3691721

Author

Commented:
oh yes, i know how to set up the linked servers, and they are already linked.  as i said before, i have a number of drivers that exec the procs across all three instances, when called. (see sample below)

i just don't know how to do this insertion across all three..given the number of parms and defaults within

IF EXISTS(SELECT 1 FROM Database.dbo.Current WHERE tradetime >=@tradetime and  ordernumber=@ordernumber)
BEGIN

INSERT Database.dbo.AuditTrail (ActionDate,ActionType,ActionSource,OrderNumber,Old,New,UserID,Hostname)

SELECT CURRENT_TIMESTAMP,'Quantity',CONVERT(char(20), SERVERPROPERTY('servername')),@ordernumber,quantity,@newqty,SYSTEM_USER,HOST_NAME()
FROM Database.dbo.Current
WHERE tradeTime >=@tradetime
AND ordernumber = @ordernumber
     
      EXEC SERVER1.Database.dbo.usp_procName @newqty, @ordernumber, @tradetime
      EXEC SERVER2.Database.dbo.usp_procName @newqty, @ordernumber, @tradetime
      EXEC SERVER3.Database.dbo.usp_procName @newqty, @ordernumber, @tradetime

END
CERTIFIED EXPERT

Commented:
Yes, if you created the sp on all 3 servers than it should work. I thaught that you needed to do the inserts on 3 different servers.

What I understand from your code is that if you have 1 "main" server where you execute the insert stored procedure from where you call the second sp on other 3 servers. Is that right?

Author

Commented:
not really.  i've got three instances, they are to be identical at all times.  there isn't really a primary or secondary, they're supposed to be the same.  they're all written to in parallel, but when data revisions are required, i have to push them to all three boxes.  the same proc would exist on all three instances, and they are executed by drivers, which also exist on all three instances.  thus, it doesn't matter where they're connected.  if/when the driver is invoked, it will impact all three machines.  
CERTIFIED EXPERT

Commented:
I'm not sure I understand what you mean by driver.

You'll have a problem with this setup because I don't think you can create a linked server to itself (this is what I understood, no matter where you are you execute the 3 calls: EXEC SERVERn) , that means if you executed the calling proc on SERVER1, the EXEC SERVER1.Database.dbo.usp_procName @newqty, @ordernumber, @tradetime
 will give you error.

If it doesn't work you'll have to use a dynamic query like this:

DECLARE
      @sql nvarchar(1000)
SELECT
      @sql=
      CASE @@SERVERNAME
            WHEN 'SERVER1' THEN
'
EXEC Database.dbo.usp_procName @newqty, @ordernumber, @tradetime
EXEC SERVER2.Database.dbo.usp_procName @newqty, @ordernumber, @tradetime
EXEC SERVER3.Database.dbo.usp_procName @newqty, @ordernumber, @tradetime
'
            WHEN 'SERVER2' THEN
'
EXEC SERVER1.Database.dbo.usp_procName @newqty, @ordernumber, @tradetime
EXEC Database.dbo.usp_procName @newqty, @ordernumber, @tradetime
EXEC SERVER3.Database.dbo.usp_procName @newqty, @ordernumber, @tradetime
'
            WHEN 'SERVER2' THEN
'
EXEC SERVER1.Database.dbo.usp_procName @newqty, @ordernumber, @tradetime
EXEC SERVER2.Database.dbo.usp_procName @newqty, @ordernumber, @tradetime
EXEC Database.dbo.usp_procName @newqty, @ordernumber, @tradetime
'
      END

IF @sql IS NOT NULL
      EXEC sp_sqlexec @sql



CERTIFIED EXPERT

Commented:
Sorry, the third one should have been:

WHEN 'SERVER3' THEN
CERTIFIED EXPERT

Commented:
actually I forgot about params, the correct code is this:

DECLARE
      @sql nvarchar(1000)
      @params nvarchar(1000)
SELECT
      @sql=
      CASE @@SERVERNAME
            WHEN 'SERVER1' THEN
'
EXEC Database.dbo.usp_procName @newqty, @ordernumber, @tradetime
EXEC SERVER2.Database.dbo.usp_procName @newqty, @ordernumber, @tradetime
EXEC SERVER3.Database.dbo.usp_procName @newqty, @ordernumber, @tradetime
'
            WHEN 'SERVER2' THEN
'
EXEC SERVER1.Database.dbo.usp_procName @newqty, @ordernumber, @tradetime
EXEC Database.dbo.usp_procName @newqty, @ordernumber, @tradetime
EXEC SERVER3.Database.dbo.usp_procName @newqty, @ordernumber, @tradetime
'
            WHEN 'SERVER3' THEN
'
EXEC SERVER1.Database.dbo.usp_procName @newqty, @ordernumber, @tradetime
EXEC SERVER2.Database.dbo.usp_procName @newqty, @ordernumber, @tradetime
EXEC Database.dbo.usp_procName @newqty, @ordernumber, @tradetime
'
      END
      @params='@newqty typehere, @ordernumber typehere, @tradetime datetime'

IF @sql IS NOT NULL
      EXEC sp_sqlexec @sql, @params, @newqty, @ordernumber, @tradetime

Author

Commented:
oh, you know, i hadn't thought about that.  i guess because there are some 30 odd parms in the insert proc, i wasn't thinking i could/should do that dynamically.

>> that means if you executed the calling proc on SERVER1, the EXEC SERVER1.Database.dbo.usp_procName @newqty, @ordernumber, @tradetime
 will give you error.


and this puzzles me.  how is it that you expect that to error?  the drivers are called once w/all the parms.  then, within the driver, the proc(s) are executed on each of the machines, just like this:

exec server1 database.dbo.insertproc @parm1, @parm2, @parm3.....
exec server2 database.dbo.insertproc @parm1, @parm2, @parm3.....
exec server3 database.dbo.insertproc @parm1, @parm2, @parm3.....


that's done within the drivers.   at least the ones that are working already.  i wasn't sure that was the right way to do the one with so many parms

Author

Commented:
if the insert proc exists on all three instances, could i not just do the driver like the others?  kinda like this:

exec server1 database.dbo.insertproc @parm1, @parm2, @parm3..... @parm30
exec server2 database.dbo.insertproc @parm1, @parm2, @parm3..... @parm30
exec server3 database.dbo.insertproc @parm1, @parm2, @parm3..... @parm30

that would be preferred, over the dynamic, as i don't want the enduser to have to pass in a servername, nor do i really feel good about the risks of the dynamic tSQL.  (though i am using it in some places, i just don't want to do more, if i can avoid it)
CERTIFIED EXPERT

Commented:
Sorry, my bad, you actually can qualify a table starting with the server name.I learned something today. :)  So it will be fine with the condition that any given server will be linked with the other 2.

CERTIFIED EXPERT

Commented:
Ooops, aparently works with tables but not with stored procedures.
CERTIFIED EXPERT

Commented:
Sorry, again, I'm confused now, it should work with sps as well. Just try it ands tell me if it worked.

Author

Commented:
i'm trying it now, i will let you know as soon as i know something more

Author

Commented:
Yep, it worked just fine.  Just like all of my other drivers.  I don't know if it was just all the parms, or something else entirely that tripped me up... but, it works perfectly.

Thanks very much for your assitance, Zdberteoc.  I do appreciate the help w/ROLLTRADE, and the push to figure out my driver.  

Author

Commented:
sorry for the misspelling.  of course, i meant 'Zberteoc'
again, thanks very much
CERTIFIED EXPERT

Commented:
Glad I could be of help, cheers!

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.