Link to home
Start Free TrialLog in
Avatar of dbaSQL
dbaSQLFlag for United States of America

asked on

Two insert procedure revisions

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
Avatar of Zberteoc
Zberteoc
Flag of Canada image
Why don't you just open 2 sepparate questions?
Avatar of Zberteoc
Zberteoc
Flag of Canada image
I am not sure I understand the question with the drivers? You need a sp that fires the inserts on all 3 servers?
Avatar of Zberteoc
Zberteoc
Flag of Canada image
Why this:

SET @quantity = COALESCE(@quantity, @quantity)
? id of no good.
Avatar of Zberteoc
Zberteoc
Flag of Canada image
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
Avatar of dbaSQL
dbaSQL
Flag of United States of America image

ASKER

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?

Avatar of Zberteoc
Zberteoc
Flag of Canada image
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.
Avatar of dbaSQL
dbaSQL
Flag of United States of America image

ASKER

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
Avatar of Zberteoc
Zberteoc
Flag of Canada image
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.



Avatar of dbaSQL
dbaSQL
Flag of United States of America image

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of Zberteoc
Zberteoc
Flag of Canada image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dbaSQL
dbaSQL
Flag of United States of America image

ASKER

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?
Avatar of Zberteoc
Zberteoc
Flag of Canada image
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.

Avatar of dbaSQL
dbaSQL
Flag of United States of America image

ASKER

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
Avatar of dbaSQL
dbaSQL
Flag of United States of America image

ASKER

still unable to get some testing done, zberteoc.  i apologize for the delay, but it will happen soon.
Avatar of dbaSQL
dbaSQL
Flag of United States of America image

ASKER

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?
Avatar of Zberteoc
Zberteoc
Flag of Canada image
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
Avatar of dbaSQL
dbaSQL
Flag of United States of America image

ASKER

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
Avatar of Zberteoc
Zberteoc
Flag of Canada image
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?
Avatar of dbaSQL
dbaSQL
Flag of United States of America image

ASKER

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.  
Avatar of Zberteoc
Zberteoc
Flag of Canada image
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



Avatar of Zberteoc
Zberteoc
Flag of Canada image
Sorry, the third one should have been:

WHEN 'SERVER3' THEN
Avatar of Zberteoc
Zberteoc
Flag of Canada image
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

Avatar of dbaSQL
dbaSQL
Flag of United States of America image

ASKER

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
Avatar of dbaSQL
dbaSQL
Flag of United States of America image

ASKER

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)
Avatar of Zberteoc
Zberteoc
Flag of Canada image
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.

Avatar of Zberteoc
Zberteoc
Flag of Canada image
Ooops, aparently works with tables but not with stored procedures.
Avatar of Zberteoc
Zberteoc
Flag of Canada image
Sorry, again, I'm confused now, it should work with sps as well. Just try it ands tell me if it worked.
Avatar of dbaSQL
dbaSQL
Flag of United States of America image

ASKER

i'm trying it now, i will let you know as soon as i know something more
Avatar of dbaSQL
dbaSQL
Flag of United States of America image

ASKER

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.  
Avatar of dbaSQL
dbaSQL
Flag of United States of America image

ASKER

sorry for the misspelling.  of course, i meant 'Zberteoc'
again, thanks very much
Avatar of Zberteoc
Zberteoc
Flag of Canada image
Glad I could be of help, cheers!