• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • Last Modified:

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
0
dbaSQL
Asked:
dbaSQL
  • 17
  • 14
1 Solution
 
ZberteocCommented:
Why don't you just open 2 sepparate questions?
0
 
ZberteocCommented:
I am not sure I understand the question with the drivers? You need a sp that fires the inserts on all 3 servers?
0
 
ZberteocCommented:
Why this:

SET @quantity = COALESCE(@quantity, @quantity)
? id of no good.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
ZberteocCommented:
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
0
 
dbaSQLAuthor 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?

0
 
ZberteocCommented:
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.
0
 
dbaSQLAuthor 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
0
 
ZberteocCommented:
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.



0
 
dbaSQLAuthor 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
0
 
ZberteocCommented:
i see, but the point is that at the insert moment YOU KNOW the order nuber. Process as I understood is like this:

UI - aquire ordernumber
UI - invoke sp with the aquired number
--> SQL exec sp with the passed in ordernumber

so inside the 2 stored proc you know the order number AND the sattledate if ROLLTRADE then inside you could call another sp
 EXEC spNameHere @ordernumber, @sattledate
0
 
dbaSQLAuthor 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?
0
 
ZberteocCommented:
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.

0
 
dbaSQLAuthor 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
0
 
dbaSQLAuthor Commented:
still unable to get some testing done, zberteoc.  i apologize for the delay, but it will happen soon.
0
 
dbaSQLAuthor 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?
0
 
ZberteocCommented:
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
0
 
dbaSQLAuthor 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
0
 
ZberteocCommented:
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?
0
 
dbaSQLAuthor 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.  
0
 
ZberteocCommented:
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



0
 
ZberteocCommented:
Sorry, the third one should have been:

WHEN 'SERVER3' THEN
0
 
ZberteocCommented:
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

0
 
dbaSQLAuthor 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
0
 
dbaSQLAuthor 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)
0
 
ZberteocCommented:
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.

0
 
ZberteocCommented:
Ooops, aparently works with tables but not with stored procedures.
0
 
ZberteocCommented:
Sorry, again, I'm confused now, it should work with sps as well. Just try it ands tell me if it worked.
0
 
dbaSQLAuthor Commented:
i'm trying it now, i will let you know as soon as i know something more
0
 
dbaSQLAuthor 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.  
0
 
dbaSQLAuthor Commented:
sorry for the misspelling.  of course, i meant 'Zberteoc'
again, thanks very much
0
 
ZberteocCommented:
Glad I could be of help, cheers!
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 17
  • 14
Tackle projects and never again get stuck behind a technical roadblock.
Join Now