Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

continued dynamic nightmare

Posted on 2005-05-03
52
Medium Priority
?
273 Views
Last Modified: 2010-03-19
hi.  i had opened this question some time back (http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21383458.html).  but to date, i've not been able to get any further with it.  any assistance at all really truly is appreciated as this has become horribly urgent.

long story short - i've got four perfectly functional procedures, all just like the one below - the only difference is the column and tsql which is used to 'make' almstUniq, the uniqueID.  i simply want to combine them into one.  ideally, the source server, source database, destination server, destination database and destionation are the only parameters, but i believe that mandates dynamic sql.  i tried, it failed.  for simplicities sake, i've tried to hard code in source/destination server and database, and only use the destination as parameter, such that i could avoid dynamic, but this fails too.  again, each procedure works flawlessly by itself.  how can i combine them?  i've copied 3 of them below:

----here's one:
CREATE proc usp_test
  @destination varchar(5)
as

---------------Identifiers from drop and prod-------------
CREATE TABLE #Ident (almstUniq varchar(50))
insert INTO #Ident
select  rtrim(cast(origorderno as varchar)) +'-'+ rtrim(cast(lastfillquantity as varchar))+'-'+ rtrim(cast(lastfillprice as varchar)) as almstUniq
from Server1.database1.dbo.table1
where timeofexecution >= left(getdate()-0, 11)
and  Destination = @Destination
union
select  rtrim(cast(origorderno as varchar)) + '-' + rtrim(cast(lastfillquantity as varchar)) +'-'+ rtrim(cast(lastfillprice as varchar)) as almstUniq
from Server2.database2.dbo.table2
where timeofexecution >= left(getdate()-0, 11)
and  Destination = @Destination


---------------# of occurrences in Drop -------------
CREATE TABLE #DropCounts (almstUniq varchar(50), countDrop int, FillQtyDrop int, lastfillprice smallmoney, origorderno varchar(50),sequencenumber int)
--CREATE TABLE #DropCounts (almstUniq varchar(50), countDrop int, FillQtyDrop int, lastfillprice smallmoney, origorderno varchar(50))
insert INTO #DropCounts
select distinct rtrim(cast(origorderno as varchar)) + '-' + rtrim(cast(lastfillquantity as varchar)) +'-'+ rtrim(cast(lastfillprice as varchar))  as almstUniq,
count(rtrim(cast(origorderno as varchar)))  as countDrop, lastfillquantity as FillQtyDrop,lastfillprice,origorderno,sequencenumber
from Server1.database1.dbo.table1
where timeofexecution >= left(getdate()-0, 11)
and  Destination = @Destination
group by origorderno,lastfillquantity,lastfillprice,sequencenumber


---------------# of occurrences in Production -------------
CREATE TABLE #ProdCounts (almstUniq varchar(50), countProd int)
insert INTO #ProdCounts
select distinct rtrim(cast(origorderno as varchar)) + '-' + rtrim(cast(lastfillquantity as varchar)) +'-'+ rtrim(cast(lastfillprice as varchar)) as almstUniq,
count(rtrim(cast(origorderno as varchar)))  as countProd
from Server2.database2.dbo.table2
where timeofexecution >= left(getdate()-0, 11)
and  Destination = @Destination
group by origorderno,lastfillquantity,lastfillprice


---------------anything that occurs more in the drop than in prod ----
select #dropcounts.sequencenumber,#dropcounts.origorderno,#dropcounts.FillQtyDrop,#dropcounts.lastfillprice  from #DropCounts
left outer join #Ident on #Ident.almstUniq = #DropCounts.almstUniq
left outer join #ProdCounts on #ProdCounts.almstUniq = #DropCounts.almstUniq
where countDrop-isnull(#ProdCounts.countProd,0) > 0
order by #DropCounts.origorderno


INSERT server1.database1.dbo.SEQUENCE SELECT sequencenumber FROM #DropCounts
left outer join #ProdCounts ON #ProdCounts.almstUniq = #DropCounts.almstUniq
WHERE #DropCounts.countDrop-isnull(#ProdCounts.countProd,0) > 0

drop table #Ident
drop table #DropCounts
drop table #ProdCounts

INSERT INTO server1.database1.dbo.Working
(traderid, accountid, prevorderno, destination, symbol, buysell, lastfillquantity, lastfillprice, timeofexecution, clearingfee,liquidityflag, client,
ordernumber, origorderno, basketid, basketnumber, quantity, executedquantity, leavesquantity, ordertype, limitprice, avgfillprice,orderstatus, stopprice,
timeinforce, cntrpartyID,sequencenumber)
      SELECT traderid, accountid, prevorderno, destination, symbol, buysell, lastfillquantity, lastfillprice, timeofexecution, clearingfee,
      liquidityflag, client, ordernumber, origorderno, basketid, basketnumber, quantity, executedquantity, leavesquantity, ordertype, limitprice, avgfillprice,
      orderstatus, stopprice, timeinforce,cntrpartyID,t.sequencenumber
      FROM server1.database1.dbo.table1 T
      INNER JOIN server1.database1.dbo.Sequence X on X.sequencenumber = T.sequencenumber
      WHERE destination = @destination


--------------------------  look at them  --------------------------------------------
select * from server1.database1.dbo.table1 s1
where exists                    
(select 1 from server1.database1.dbo.Sequence d1 WHERE s1.sequencenumber = d1.sequencenumber)
AND destination = @destination

GO

---here's #2:
CREATE proc usp_test2
 @destination varchar(5)
as

---------------Identifiers from drop and prod-------------
CREATE TABLE #Ident (almstUniq varchar(50))
insert INTO #Ident
select rtrim(ordernumber) as almstUniq
from server1.database1.dbo.table1
where timeofexecution >= left(getdate()-0, 11)
and  Destination = @Destination
union
select rtrim(ordernumber) as almstUniq
from server2.database2.dbo.table2
where timeofexecution >= left(getdate()-0, 11)
and  Destination = @Destination


---------------# of occurrences in Drop -------------
CREATE TABLE #DropCounts (almstUniq varchar(50), countDrop int, FillQtyDrop int, lastfillprice smallmoney, ordernumber varchar(50),sequencenumber int)
insert INTO #DropCounts
select rtrim(ordernumber) as almstUniq, count(rtrim(cast(ordernumber as varchar)))  as countDrop,
lastfillquantity as FillQtyDrop,lastfillprice,ordernumber,sequencenumber
from server1.database1.dbo.table1
where timeofexecution >= left(getdate()-0, 11)
and  Destination = @Destination
group by ordernumber,lastfillquantity,lastfillprice,sequencenumber


---------------# of occurrences in Production  -------------
CREATE TABLE #ProdCounts (almstUniq varchar(50), countProd int)
insert INTO #ProdCounts
select rtrim(ordernumber) as almstUniq, count(rtrim(cast(ordernumber as varchar)))  as countProd
from server2.database2.dbo.table2
where timeofexecution >= left(getdate()-0, 11)
and  Destination = @Destination
group by ordernumber,lastfillquantity,lastfillprice


---------------anything that occurs more in the drop than prod -------------
select #dropcounts.sequencenumber,#dropcounts.ordernumber,#dropcounts.FillQtyDrop,#dropcounts.lastfillprice  from #DropCounts
left outer join #Ident on #Ident.almstUniq = #DropCounts.almstUniq
left outer join #ProdCounts on #ProdCounts.almstUniq = #DropCounts.almstUniq
where countDrop-isnull(#ProdCounts.countProd,0) > 0
order by #DropCounts.ordernumber


INSERT server1.database1.dbo.SEQUENCE SELECT sequencenumber FROM #DropCounts
left outer join #ProdCounts ON #ProdCounts.almstUniq = #DropCounts.almstUniq
WHERE #DropCounts.countDrop-isnull(#ProdCounts.countProd,0) > 0

drop table #Ident
drop table #DropCounts
drop table #ProdCounts

--DELETE FROM server1.database1.dbo.sequence
DELETE FROM server1.database1.dbo.working where timeofexecution >= left(getdate()-0, 11)

INSERT INTO server1.database1.dbo.Working
(traderid, accountid, prevorderno, destination, symbol, buysell, lastfillquantity, lastfillprice, timeofexecution, clearingfee,liquidityflag, client,
ordernumber, origorderno, basketid, basketnumber, quantity, executedquantity, leavesquantity, ordertype, limitprice, avgfillprice,orderstatus, stopprice,
timeinforce, cntrpartyID,sequencenumber)
      SELECT traderid, accountid, prevorderno, destination, symbol, buysell, lastfillquantity, lastfillprice, timeofexecution, clearingfee,
      liquidityflag, client, ordernumber, origorderno, basketid, basketnumber, quantity, executedquantity, leavesquantity, ordertype, limitprice, avgfillprice,
      orderstatus, stopprice, timeinforce,cntrpartyID,t.sequencenumber
      FROM server1.database1.dbo.table1 T
      INNER JOIN server1.database1.dbo.Sequence X on X.sequencenumber = T.sequencenumber
      WHERE destination = @destination
      AND timeofexecution >= left(getdate()-0, 11)


--------------------------  look at them  --------------------------------------------
select * from server1.database1.dbo.table1 s1
where exists                    
(select 1 from server1.database1.dbo.sequence d1 WHERE s1.sequencenumber = d1.sequencenumber)
AND destination = @destination
AND timeofexecution >= left(getdate()-0, 11)

GO


----and here's #3:

CREATE proc usp_test3
 CREATE proc usp_test
  @destination varchar(5)
as

---------------Identifiers from drop and prod-------------
CREATE TABLE #Ident (almstUniq varchar(50))
insert INTO #Ident
select  case WHEN charindex('_',ordernumber)>1 THEN left(ordernumber,charindex('_',ordernumber) -1)ELSE ordernumber
END as almstUniq
from server1.database1.dbo.table1
where timeofexecution >= left(getdate()-0, 11)
and  Destination = @Destination
union
select  case WHEN charindex('_',ordernumber)>1 THEN left(ordernumber,charindex('_',ordernumber) -1)ELSE ordernumber
END as almstUniq
from server2.database2.dbo.table2
where timeofexecution >= left(getdate()-0, 11)
and  Destination = @Destination


---------------# of occurrences in Drop -------------
CREATE TABLE #DropCounts (almstUniq varchar(50),countDrop int,FillQtyDrop int,lAStfillprice smallmoney,ordernumber varchar(50),sequencenumber int)
INSERT INTO #DropCounts  (almstUniq, countDrop, FillQtyDrop, LastFillPrice, ordernumber, sequencenumber)
select case WHEN charindex('_',ordernumber)>1 THEN left(ordernumber,charindex('_',ordernumber) -1)ELSE ordernumber
END as almstUniq, count(rtrim(cast(ordernumber as varchar)))  as countDrop, lastfillquantity as FillQtyDrop,lastfillprice,ordernumber,sequencenumber
from server1.database1.dbo.table1
where timeofexecution >= left(getdate()-0, 11)
and  Destination = @Destination
group by ordernumber,lastfillquantity,lastfillprice,sequencenumber


---------------# of occurrences in Production -------------
CREATE TABLE #ProdCounts (almstUniq varchar(50), countProd int)
insert INTO #ProdCounts
select case WHEN charindex('_',ordernumber)>1 THEN left(ordernumber,charindex('_',ordernumber) -1)ELSE ordernumber
END as almstUniq, count(rtrim(cast(ordernumber as varchar))) as countProd
from server2.database2.dbo.table2
where timeofexecution >= left(getdate()-0, 11)
and  Destination = @Destination
group by ordernumber,lastfillquantity,lastfillprice


---------------anything that occurs more in the drop than prod -------------
select #dropcounts.ordernumber,#dropcounts.FillQtyDrop,#dropcounts.lastfillprice  from #DropCounts
left outer join #Ident on #Ident.almstUniq = #DropCounts.almstUniq
left outer join #ProdCounts on #ProdCounts.almstUniq = #DropCounts.almstUniq
where countDrop-isnull(#ProdCounts.countProd,0) > 0
order by #DropCounts.ordernumber

DELETE FROM server1.database1.dbo.working where timeofexecution >= left(getdate()-0, 11)

INSERT server1.database1.dbo.SEQUENCE SELECT sequencenumber FROM #DropCounts
left outer join #ProdCounts ON #ProdCounts.almstUniq = #DropCounts.almstUniq
WHERE #DropCounts.countDrop-isnull(#ProdCounts.countProd,0) > 0

drop table #Ident
drop table #DropCounts
drop table #ProdCounts

INSERT INTO server1.database1.dbo.Working
(traderid, accountid, prevorderno, destination, symbol, buysell, lastfillquantity, lastfillprice, timeofexecution, clearingfee,liquidityflag, client,
ordernumber, origorderno, basketid, basketnumber, quantity, executedquantity, leavesquantity, ordertype, limitprice, avgfillprice,orderstatus, stopprice,
timeinforce, cntrpartyID,sequencenumber)
      SELECT traderid, accountid, prevorderno, destination, symbol, buysell, lastfillquantity, lastfillprice, timeofexecution, clearingfee,
      liquidityflag, client, ordernumber, origorderno, basketid, basketnumber, quantity, executedquantity, leavesquantity, ordertype, limitprice, avgfillprice,
      orderstatus, stopprice, timeinforce,cntrpartyID,t.sequencenumber
      FROM server1.database1.dbo.table1 T
      INNER JOIN server1.database1.dbo.Sequence X on X.sequencenumber = T.sequencenumber
      WHERE destination = @destination


--------------------------  look at them  --------------------------------------------
select * from server1.database1.dbo.table1 s1
where exists                    
(select 1 from server1.database1.dbo.sequence d1 WHERE s1.sequencenumber = d1.sequencenumber)
AND destination = @destination
GO

0
Comment
Question by:RLLewis
  • 30
  • 22
52 Comments
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13920581
Try this for your first stored procedure if it will return the same result as your original:

CREATE proc usp_test
  @destination varchar(5)
as

declare @SQLStmt VARCHAR(8000)

SET @SQLStmt = '
---------------Identifiers from drop and prod-------------
CREATE TABLE #Ident (almstUniq varchar(50))
insert INTO #Ident
select  rtrim(cast(origorderno as varchar)) +''-''+ rtrim(cast(lastfillquantity as varchar))+''-''+ rtrim(cast(lastfillprice as varchar)) as almstUniq
from Server1.database1.dbo.table1
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''
union
select  rtrim(cast(origorderno as varchar)) + ''-'' + rtrim(cast(lastfillquantity as varchar)) +''-''+ rtrim(cast(lastfillprice as varchar)) as almstUniq
from Server2.database2.dbo.table2
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''


---------------# of occurrences in Drop -------------
CREATE TABLE #DropCounts (almstUniq varchar(50), countDrop int, FillQtyDrop int, lastfillprice smallmoney, origorderno varchar(50),sequencenumber int)
insert INTO #DropCounts
select distinct rtrim(cast(origorderno as varchar)) + ''-'' + rtrim(cast(lastfillquantity as varchar)) +''-''+ rtrim(cast(lastfillprice as varchar))  as almstUniq,
count(rtrim(cast(origorderno as varchar)))  as countDrop, lastfillquantity as FillQtyDrop,lastfillprice,origorderno,sequencenumber
from Server1.database1.dbo.table1
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''
group by origorderno,lastfillquantity,lastfillprice,sequencenumber


---------------# of occurrences in Production -------------
CREATE TABLE #ProdCounts (almstUniq varchar(50), countProd int)
insert INTO #ProdCounts
select distinct rtrim(cast(origorderno as varchar)) + ''-'' + rtrim(cast(lastfillquantity as varchar)) +''-''+ rtrim(cast(lastfillprice as varchar)) as almstUniq,
count(rtrim(cast(origorderno as varchar)))  as countProd
from Server2.database2.dbo.table2
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''
group by origorderno,lastfillquantity,lastfillprice


---------------anything that occurs more in the drop than in prod ----
select #dropcounts.sequencenumber,#dropcounts.origorderno,#dropcounts.FillQtyDrop,#dropcounts.lastfillprice  from #DropCounts
left outer join #Ident on #Ident.almstUniq = #DropCounts.almstUniq
left outer join #ProdCounts on #ProdCounts.almstUniq = #DropCounts.almstUniq
where countDrop-isnull(#ProdCounts.countProd,0) > 0
order by #DropCounts.origorderno


INSERT server1.database1.dbo.SEQUENCE SELECT sequencenumber FROM #DropCounts
left outer join #ProdCounts ON #ProdCounts.almstUniq = #DropCounts.almstUniq
WHERE #DropCounts.countDrop-isnull(#ProdCounts.countProd,0) > 0

drop table #Ident
drop table #DropCounts
drop table #ProdCounts

INSERT INTO server1.database1.dbo.Working
(traderid, accountid, prevorderno, destination, symbol, buysell, lastfillquantity, lastfillprice, timeofexecution, clearingfee,liquidityflag, client,
ordernumber, origorderno, basketid, basketnumber, quantity, executedquantity, leavesquantity, ordertype, limitprice, avgfillprice,orderstatus, stopprice,
timeinforce, cntrpartyID,sequencenumber)
     SELECT traderid, accountid, prevorderno, destination, symbol, buysell, lastfillquantity, lastfillprice, timeofexecution, clearingfee,
     liquidityflag, client, ordernumber, origorderno, basketid, basketnumber, quantity, executedquantity, leavesquantity, ordertype, limitprice, avgfillprice,
     orderstatus, stopprice, timeinforce,cntrpartyID,t.sequencenumber
     FROM server1.database1.dbo.table1 T
     INNER JOIN server1.database1.dbo.Sequence X on X.sequencenumber = T.sequencenumber
     WHERE destination = ''' + @Destination + '''


--------------------------  look at them  --------------------------------------------
select * from server1.database1.dbo.table1 s1
where exists                    
(select 1 from server1.database1.dbo.Sequence d1 WHERE s1.sequencenumber = d1.sequencenumber)
AND destination = ''' + @Destination + ''''

EXEC (@SQLStmt)

GO

0
 
LVL 1

Author Comment

by:RLLewis
ID: 13920691
that totally worked.  i'm pretty floored right about now.  a., what did you do?  b., any way i can do servername/database name as parameters, too?  c., what about the other proc, easily comingled?
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13920803
Ok, try this one this time, with the source/destination server name and database name as parameters.

What are the cases when the other processes in your other stored procedures will be called?  Is it dependent on the value of @destination?

In your sp, the @sourceserver here is server1, @sourcedb is database1, @destinationserver is server2 and @destinationdb is database2.

CREATE proc usp_test
  @sourceserver varchar(100),
  @sourcedb varchar(100),
  @destinationserver varchar(100),
  @destinationdb varchar(100),
  @destination varchar(5)
as

declare @SQLStmt VARCHAR(8000)

SET @SQLStmt = '
---------------Identifiers from drop and prod-------------
CREATE TABLE #Ident (almstUniq varchar(50))
insert INTO #Ident
select  rtrim(cast(origorderno as varchar)) +''-''+ rtrim(cast(lastfillquantity as varchar))+''-''+ rtrim(cast(lastfillprice as varchar)) as almstUniq
from ' + @sourceserver + '.' + @sourcedb + '.dbo.table1
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''
union
select  rtrim(cast(origorderno as varchar)) + ''-'' + rtrim(cast(lastfillquantity as varchar)) +''-''+ rtrim(cast(lastfillprice as varchar)) as almstUniq
from ' + @destinationserver + '.' + @destinationdb + '.dbo.table2
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''


---------------# of occurrences in Drop -------------
CREATE TABLE #DropCounts (almstUniq varchar(50), countDrop int, FillQtyDrop int, lastfillprice smallmoney, origorderno varchar(50),sequencenumber int)
insert INTO #DropCounts
select distinct rtrim(cast(origorderno as varchar)) + ''-'' + rtrim(cast(lastfillquantity as varchar)) +''-''+ rtrim(cast(lastfillprice as varchar))  as almstUniq,
count(rtrim(cast(origorderno as varchar)))  as countDrop, lastfillquantity as FillQtyDrop,lastfillprice,origorderno,sequencenumber
from ' + @sourceserver + '.' + @sourcedb + '.dbo.table1
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''
group by origorderno,lastfillquantity,lastfillprice,sequencenumber


---------------# of occurrences in Production -------------
CREATE TABLE #ProdCounts (almstUniq varchar(50), countProd int)
insert INTO #ProdCounts
select distinct rtrim(cast(origorderno as varchar)) + ''-'' + rtrim(cast(lastfillquantity as varchar)) +''-''+ rtrim(cast(lastfillprice as varchar)) as almstUniq,
count(rtrim(cast(origorderno as varchar)))  as countProd
from ' + @destinationserver + '.' + @destinationdb + '.dbo.table2
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''
group by origorderno,lastfillquantity,lastfillprice


---------------anything that occurs more in the drop than in prod ----
select #dropcounts.sequencenumber,#dropcounts.origorderno,#dropcounts.FillQtyDrop,#dropcounts.lastfillprice  from #DropCounts
left outer join #Ident on #Ident.almstUniq = #DropCounts.almstUniq
left outer join #ProdCounts on #ProdCounts.almstUniq = #DropCounts.almstUniq
where countDrop-isnull(#ProdCounts.countProd,0) > 0
order by #DropCounts.origorderno


INSERT ' + @sourceserver + '.' + @sourcedb + '.dbo.SEQUENCE SELECT sequencenumber FROM #DropCounts
left outer join #ProdCounts ON #ProdCounts.almstUniq = #DropCounts.almstUniq
WHERE #DropCounts.countDrop-isnull(#ProdCounts.countProd,0) > 0

drop table #Ident
drop table #DropCounts
drop table #ProdCounts

INSERT INTO ' + @sourceserver + '.' + @sourcedb + '.dbo.Working
(traderid, accountid, prevorderno, destination, symbol, buysell, lastfillquantity, lastfillprice, timeofexecution, clearingfee,liquidityflag, client,
ordernumber, origorderno, basketid, basketnumber, quantity, executedquantity, leavesquantity, ordertype, limitprice, avgfillprice,orderstatus, stopprice,
timeinforce, cntrpartyID,sequencenumber)
     SELECT traderid, accountid, prevorderno, destination, symbol, buysell, lastfillquantity, lastfillprice, timeofexecution, clearingfee,
     liquidityflag, client, ordernumber, origorderno, basketid, basketnumber, quantity, executedquantity, leavesquantity, ordertype, limitprice, avgfillprice,
     orderstatus, stopprice, timeinforce,cntrpartyID,t.sequencenumber
     FROM ' + @sourceserver + '.' + @sourcedb + '.dbo.table1 T
     INNER JOIN ' + @sourceserver + '.' + @sourcedb + '.dbo.Sequence X on X.sequencenumber = T.sequencenumber
     WHERE destination = ''' + @Destination + '''


--------------------------  look at them  --------------------------------------------
select * from ' + @sourceserver + '.' + @sourcedb + '.dbo.table1 s1
where exists                    
(select 1 from ' + @sourceserver + '.' + @sourcedb + '.dbo.Sequence d1 WHERE s1.sequencenumber = d1.sequencenumber)
AND destination = ''' + @Destination + ''''

EXEC (@SQLStmt)

GO

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:RLLewis
ID: 13920889
almost there, maybe

Server: Msg 7314, Level 16, State 1, Line 67
OLE DB provider 'server1' does not contain table '"database1"."dbo"."table1table1"'.  The table either does not exist or the current user does not have permissions on that table.
OLE DB error trace [Non-interface error:  OLE DB provider does not contain the table: ProviderName='server1', TableName='"database1"."dbo"."table1table1"'].

do you see where this is?
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13920909
Please post the command that you run, including the parameters.
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13920920
oh i found it, one sec
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13920961
it was at the bottom in my 'look at them' section --- i fixed it.  and it works.  i am really so surprised. i've been struggling on this one for far too long.  do you know that we can combine the other two procedures into this?
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13921041
Here's the complete stored procedure, all three of them.  I suggest you create them on separate stored procedures and just create a master sp that will call each one of them so that your sp will not be very long.

CREATE proc usp_test
  @sourceserver varchar(100),
  @sourcedb varchar(100),
  @destinationserver varchar(100),
  @destinationdb varchar(100),
  @destination varchar(5)
as

declare @SQLStmt VARCHAR(8000)

SET @SQLStmt = '
---------------Identifiers from drop and prod-------------
CREATE TABLE #Ident (almstUniq varchar(50))
insert INTO #Ident
select  rtrim(cast(origorderno as varchar)) +''-''+ rtrim(cast(lastfillquantity as varchar))+''-''+ rtrim(cast(lastfillprice as varchar)) as almstUniq
from ' + @sourceserver + '.' + @sourcedb + '.dbo.table1
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''
union
select  rtrim(cast(origorderno as varchar)) + ''-'' + rtrim(cast(lastfillquantity as varchar)) +''-''+ rtrim(cast(lastfillprice as varchar)) as almstUniq
from ' + @destinationserver + '.' + @destinationdb + '.dbo.table2
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''


---------------# of occurrences in Drop -------------
CREATE TABLE #DropCounts (almstUniq varchar(50), countDrop int, FillQtyDrop int, lastfillprice smallmoney, origorderno varchar(50),sequencenumber int)
insert INTO #DropCounts
select distinct rtrim(cast(origorderno as varchar)) + ''-'' + rtrim(cast(lastfillquantity as varchar)) +''-''+ rtrim(cast(lastfillprice as varchar))  as almstUniq,
count(rtrim(cast(origorderno as varchar)))  as countDrop, lastfillquantity as FillQtyDrop,lastfillprice,origorderno,sequencenumber
from ' + @sourceserver + '.' + @sourcedb + '.dbo.table1
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''
group by origorderno,lastfillquantity,lastfillprice,sequencenumber


---------------# of occurrences in Production -------------
CREATE TABLE #ProdCounts (almstUniq varchar(50), countProd int)
insert INTO #ProdCounts
select distinct rtrim(cast(origorderno as varchar)) + ''-'' + rtrim(cast(lastfillquantity as varchar)) +''-''+ rtrim(cast(lastfillprice as varchar)) as almstUniq,
count(rtrim(cast(origorderno as varchar)))  as countProd
from ' + @destinationserver + '.' + @destinationdb + '.dbo.table2
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''
group by origorderno,lastfillquantity,lastfillprice


---------------anything that occurs more in the drop than in prod ----
select #dropcounts.sequencenumber,#dropcounts.origorderno,#dropcounts.FillQtyDrop,#dropcounts.lastfillprice  from #DropCounts
left outer join #Ident on #Ident.almstUniq = #DropCounts.almstUniq
left outer join #ProdCounts on #ProdCounts.almstUniq = #DropCounts.almstUniq
where countDrop-isnull(#ProdCounts.countProd,0) > 0
order by #DropCounts.origorderno


INSERT ' + @sourceserver + '.' + @sourcedb + '.dbo.SEQUENCE SELECT sequencenumber FROM #DropCounts
left outer join #ProdCounts ON #ProdCounts.almstUniq = #DropCounts.almstUniq
WHERE #DropCounts.countDrop-isnull(#ProdCounts.countProd,0) > 0

drop table #Ident
drop table #DropCounts
drop table #ProdCounts

INSERT INTO ' + @sourceserver + '.' + @sourcedb + '.dbo.Working
(traderid, accountid, prevorderno, destination, symbol, buysell, lastfillquantity, lastfillprice, timeofexecution, clearingfee,liquidityflag, client,
ordernumber, origorderno, basketid, basketnumber, quantity, executedquantity, leavesquantity, ordertype, limitprice, avgfillprice,orderstatus, stopprice,
timeinforce, cntrpartyID,sequencenumber)
     SELECT traderid, accountid, prevorderno, destination, symbol, buysell, lastfillquantity, lastfillprice, timeofexecution, clearingfee,
     liquidityflag, client, ordernumber, origorderno, basketid, basketnumber, quantity, executedquantity, leavesquantity, ordertype, limitprice, avgfillprice,
     orderstatus, stopprice, timeinforce,cntrpartyID,t.sequencenumber
     FROM ' + @sourceserver + '.' + @sourcedb + '.dbo.table1 T
     INNER JOIN ' + @sourceserver + '.' + @sourcedb + '.dbo.Sequence X on X.sequencenumber = T.sequencenumber
     WHERE destination = ''' + @Destination + '''


--------------------------  look at them  --------------------------------------------
select * from ' + @sourceserver + '.' + @sourcedb + '.dbo.table1 s1
where exists                    
(select 1 from ' + @sourceserver + '.' + @sourcedb + '.dbo.Sequence d1 WHERE s1.sequencenumber = d1.sequencenumber)
AND destination = ''' + @Destination + ''''

EXEC (@SQLStmt)

SET @SQLStmt = '
---------------Identifiers from drop and prod-------------
CREATE TABLE #Ident (almstUniq varchar(50))
insert INTO #Ident
select rtrim(ordernumber) as almstUniq
from ' + @sourceserver + '.' + @sourcedb + '.dbo.table1
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''
union
select rtrim(ordernumber) as almstUniq
from ' + @destinationserver + '.' + @destinationdb + '.dbo.table2
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''


---------------# of occurrences in Drop -------------
CREATE TABLE #DropCounts (almstUniq varchar(50), countDrop int, FillQtyDrop int, lastfillprice smallmoney, ordernumber varchar(50),sequencenumber int)
insert INTO #DropCounts
select rtrim(ordernumber) as almstUniq, count(rtrim(cast(ordernumber as varchar)))  as countDrop,
lastfillquantity as FillQtyDrop,lastfillprice,ordernumber,sequencenumber
from ' + @sourceserver + '.' + @sourcedb + '.dbo.table1
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''
group by ordernumber,lastfillquantity,lastfillprice,sequencenumber


---------------# of occurrences in Production  -------------
CREATE TABLE #ProdCounts (almstUniq varchar(50), countProd int)
insert INTO #ProdCounts
select rtrim(ordernumber) as almstUniq, count(rtrim(cast(ordernumber as varchar)))  as countProd
from ' + @destinationserver + '.' + @destinationdb + '.dbo.table2
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''
group by ordernumber,lastfillquantity,lastfillprice


---------------anything that occurs more in the drop than prod -------------
select #dropcounts.sequencenumber,#dropcounts.ordernumber,#dropcounts.FillQtyDrop,#dropcounts.lastfillprice  from #DropCounts
left outer join #Ident on #Ident.almstUniq = #DropCounts.almstUniq
left outer join #ProdCounts on #ProdCounts.almstUniq = #DropCounts.almstUniq
where countDrop-isnull(#ProdCounts.countProd,0) > 0
order by #DropCounts.ordernumber


INSERT ' + @sourceserver + '.' + @sourcedb + '.dbo.SEQUENCE SELECT sequencenumber FROM #DropCounts
left outer join #ProdCounts ON #ProdCounts.almstUniq = #DropCounts.almstUniq
WHERE #DropCounts.countDrop-isnull(#ProdCounts.countProd,0) > 0

drop table #Ident
drop table #DropCounts
drop table #ProdCounts

--DELETE FROM ' + @sourceserver + '.' + @sourcedb + '.dbo.sequence
DELETE FROM ' + @sourceserver + '.' + @sourcedb + '.dbo.working where timeofexecution >= left(getdate()-0, 11)

INSERT INTO ' + @sourceserver + '.' + @sourcedb + '.dbo.Working
(traderid, accountid, prevorderno, destination, symbol, buysell, lastfillquantity, lastfillprice, timeofexecution, clearingfee,liquidityflag, client,
ordernumber, origorderno, basketid, basketnumber, quantity, executedquantity, leavesquantity, ordertype, limitprice, avgfillprice,orderstatus, stopprice,
timeinforce, cntrpartyID,sequencenumber)
     SELECT traderid, accountid, prevorderno, destination, symbol, buysell, lastfillquantity, lastfillprice, timeofexecution, clearingfee,
     liquidityflag, client, ordernumber, origorderno, basketid, basketnumber, quantity, executedquantity, leavesquantity, ordertype, limitprice, avgfillprice,
     orderstatus, stopprice, timeinforce,cntrpartyID,t.sequencenumber
     FROM ' + @sourceserver + '.' + @sourcedb + '.dbo.table1 T
     INNER JOIN ' + @sourceserver + '.' + @sourcedb + '.dbo.Sequence X on X.sequencenumber = T.sequencenumber
     WHERE destination = ''' + @Destination + '''
     AND timeofexecution >= left(getdate()-0, 11)


--------------------------  look at them  --------------------------------------------
select * from ' + @sourceserver + '.' + @sourcedb + '.dbo.table1 s1
where exists                    
(select 1 from ' + @sourceserver + '.' + @sourcedb + '.dbo.sequence d1 WHERE s1.sequencenumber = d1.sequencenumber)
AND destination = ''' + @Destination + '''
AND timeofexecution >= left(getdate()-0, 11)'

EXEC (@SQLStmt)

SET @SQLStmt = '
---------------Identifiers from drop and prod-------------
CREATE TABLE #Ident (almstUniq varchar(50))
insert INTO #Ident
select  case WHEN charindex(''_'',ordernumber)>1 THEN left(ordernumber,charindex(''_'',ordernumber) -1)ELSE ordernumber
END as almstUniq
from ' + @sourceserver + '.' + @sourcedb + '.dbo.table1
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''
union
select  case WHEN charindex(''_'',ordernumber)>1 THEN left(ordernumber,charindex(''_'',ordernumber) -1)ELSE ordernumber
END as almstUniq
from ' + @destinationserver + '.' + @destinationdb + '.dbo.table2
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''


---------------# of occurrences in Drop -------------
CREATE TABLE #DropCounts (almstUniq varchar(50),countDrop int,FillQtyDrop int,lAStfillprice smallmoney,ordernumber varchar(50),sequencenumber int)
INSERT INTO #DropCounts  (almstUniq, countDrop, FillQtyDrop, LastFillPrice, ordernumber, sequencenumber)
select case WHEN charindex(''_'',ordernumber)>1 THEN left(ordernumber,charindex(''_'',ordernumber) -1)ELSE ordernumber
END as almstUniq, count(rtrim(cast(ordernumber as varchar)))  as countDrop, lastfillquantity as FillQtyDrop,lastfillprice,ordernumber,sequencenumber
from ' + @sourceserver + '.' + @sourcedb + '.dbo.table1
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''
group by ordernumber,lastfillquantity,lastfillprice,sequencenumber


---------------# of occurrences in Production -------------
CREATE TABLE #ProdCounts (almstUniq varchar(50), countProd int)
insert INTO #ProdCounts
select case WHEN charindex(''_'',ordernumber)>1 THEN left(ordernumber,charindex(''_'',ordernumber) -1)ELSE ordernumber
END as almstUniq, count(rtrim(cast(ordernumber as varchar))) as countProd
from ' + @destinationserver + '.' + @destinationdb + '.dbo.table2
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''
group by ordernumber,lastfillquantity,lastfillprice


---------------anything that occurs more in the drop than prod -------------
select #dropcounts.ordernumber,#dropcounts.FillQtyDrop,#dropcounts.lastfillprice  from #DropCounts
left outer join #Ident on #Ident.almstUniq = #DropCounts.almstUniq
left outer join #ProdCounts on #ProdCounts.almstUniq = #DropCounts.almstUniq
where countDrop-isnull(#ProdCounts.countProd,0) > 0
order by #DropCounts.ordernumber

DELETE FROM ' + @sourceserver + '.' + @sourcedb + '.dbo.working where timeofexecution >= left(getdate()-0, 11)

INSERT ' + @sourceserver + '.' + @sourcedb + '.dbo.SEQUENCE SELECT sequencenumber FROM #DropCounts
left outer join #ProdCounts ON #ProdCounts.almstUniq = #DropCounts.almstUniq
WHERE #DropCounts.countDrop-isnull(#ProdCounts.countProd,0) > 0

drop table #Ident
drop table #DropCounts
drop table #ProdCounts

INSERT INTO ' + @sourceserver + '.' + @sourcedb + '.dbo.Working
(traderid, accountid, prevorderno, destination, symbol, buysell, lastfillquantity, lastfillprice, timeofexecution, clearingfee,liquidityflag, client,
ordernumber, origorderno, basketid, basketnumber, quantity, executedquantity, leavesquantity, ordertype, limitprice, avgfillprice,orderstatus, stopprice,
timeinforce, cntrpartyID,sequencenumber)
     SELECT traderid, accountid, prevorderno, destination, symbol, buysell, lastfillquantity, lastfillprice, timeofexecution, clearingfee,
     liquidityflag, client, ordernumber, origorderno, basketid, basketnumber, quantity, executedquantity, leavesquantity, ordertype, limitprice, avgfillprice,
     orderstatus, stopprice, timeinforce,cntrpartyID,t.sequencenumber
     FROM ' + @sourceserver + '.' + @sourcedb + '.dbo.table1 T
     INNER JOIN ' + @sourceserver + '.' + @sourcedb + '.dbo.Sequence X on X.sequencenumber = T.sequencenumber
     WHERE destination = ''' + @Destination + '''


--------------------------  look at them  --------------------------------------------
select * from ' + @sourceserver + '.' + @sourcedb + '.dbo.table1 s1
where exists                    
(select 1 from ' + @sourceserver + '.' + @sourcedb + '.dbo.sequence d1 WHERE s1.sequencenumber = d1.sequencenumber)
AND destination = ''' + @Destination + ''''

EXEC (@SQLStmt)
GO

0
 
LVL 1

Author Comment

by:RLLewis
ID: 13921070
i'm not sure i know what you mean by that, rafran?
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13921160

Server: Msg 2627, Level 14, State 1, Line 56
Violation of PRIMARY KEY constraint 'PK_working_sn'. Cannot insert duplicate key in object 'Working'.
The statement has been terminated.

i had deleted records from working tbl before running it.  can we be destination-specific w/the insertion/delete into sequence?  or, since it's just a sequencenumber, shall we delete from there just as we're deleting from the working table?
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13921177
well, i was expecting to go in there and clean out working/sequence tables and start over.  but, there is nothing in the working table.  so, interesting that the error suggests it attempted to insert a dupe?
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13921183
First, did it work?

What I meant was, create separate sps for each sp you currently have passing the same set of parameters.

CREATE PROCEDUE usp_Test1 (@sourceserver varchar(100), @sourcedb varchar(100), @destinationserver varchar(100), @destinationdb varchar(100), @destination)

CREATE PROCEDUE usp_Test2 (@sourceserver varchar(100), @sourcedb varchar(100), @destinationserver varchar(100), @destinationdb varchar(100), @destination)

CREATE PROCEDUE usp_Test3 (@sourceserver varchar(100), @sourcedb varchar(100), @destinationserver varchar(100), @destinationdb varchar(100), @destination)

Then create a master sp that will call each one:

CREATE PROCEDUE usp_Test (@sourceserver varchar(100), @sourcedb varchar(100), @destinationserver varchar(100), @destinationdb varchar(100), @destination)
AS
EXEC usp_Test1 @sourceserver, @sourcedb, @destinationserver, @destinationdb, @destination
EXEC usp_Test2 @sourceserver, @sourcedb, @destinationserver, @destinationdb, @destination
EXEC usp_Test3 @sourceserver, @sourcedb, @destinationserver, @destinationdb, @destination
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13921192
oh, i didn't realize that's what you meant to do.  let me try, give me a moment
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13921344
ok, well, i've got the 3 sp's done.  i'm unsure about this master proc, though.  let's assume i add a new server --- source and/or destination --- how would i account for it with this?
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13921419
and do we have to do it w/this master driver?  see, i'm trying to hand over a reconciliation process. ideally, it's 2 steps.  usp_Recon, usp_Insert  i've got to train them on how to utilize and it would be much more appropriate if i was doing just that -- two steps.  Or, possibly I misunderstand your approach.  Please clarify?
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13921479
well, here you'll see the procs -- each creates  and runs by itself just fine.  but when i try to fire w/the master, i get this:

Server: Msg 137, Level 15, State 2, Line 53
Must declare the variable '@destination'.
Server: Msg 137, Level 15, State 2, Line 54
Must declare the variable '@destination'.
Server: Msg 137, Level 15, State 2, Line 47
Must declare the variable '@destination'.
Server: Msg 137, Level 15, State 2, Line 47
Must declare the variable '@destination'.




CREATE proc usp_testB
  @sourceserver varchar(100),
  @sourcedb varchar(100),
  @destinationserver varchar(100),
  @destinationdb varchar(100),
  @destination varchar(5)
as

declare @SQLStmt VARCHAR(8000)

SET @SQLStmt = '
---------------Identifiers from drop and prod-------------
CREATE TABLE #Ident (almstUniq varchar(50))
insert INTO #Ident
select  rtrim(cast(origorderno as varchar)) +''-''+ rtrim(cast(lastfillquantity as varchar))+''-''+ rtrim(cast(lastfillprice as varchar)) as almstUniq
from ' + @sourceserver + '.' + @sourcedb + '.dbo.trade
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''
union
select  rtrim(cast(origorderno as varchar)) + ''-'' + rtrim(cast(lastfillquantity as varchar)) +''-''+ rtrim(cast(lastfillprice as varchar)) as almstUniq
from ' + @destinationserver + '.' + @destinationdb + '.dbo.trade
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''


---------------# of occurrences in Drop -------------
CREATE TABLE #DropCounts (almstUniq varchar(50), countDrop int, FillQtyDrop int, lastfillprice smallmoney, origorderno varchar(50),sequencenumber int)
insert INTO #DropCounts
select distinct rtrim(cast(origorderno as varchar)) + ''-'' + rtrim(cast(lastfillquantity as varchar)) +''-''+ rtrim(cast(lastfillprice as varchar))  as almstUniq,
count(rtrim(cast(origorderno as varchar)))  as countDrop, lastfillquantity as FillQtyDrop,lastfillprice,origorderno,sequencenumber
from ' + @sourceserver + '.' + @sourcedb + '.dbo.trade
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''
group by origorderno,lastfillquantity,lastfillprice,sequencenumber


---------------# of occurrences in Production -------------
CREATE TABLE #ProdCounts (almstUniq varchar(50), countProd int)
insert INTO #ProdCounts
select distinct rtrim(cast(origorderno as varchar)) + ''-'' + rtrim(cast(lastfillquantity as varchar)) +''-''+ rtrim(cast(lastfillprice as varchar)) as almstUniq,
count(rtrim(cast(origorderno as varchar)))  as countProd
from ' + @destinationserver + '.' + @destinationdb + '.dbo.trade
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''
group by origorderno,lastfillquantity,lastfillprice


---------------anything that occurs more in the drop than in prod ----
select #dropcounts.sequencenumber,#dropcounts.origorderno,#dropcounts.FillQtyDrop,#dropcounts.lastfillprice  from #DropCounts
left outer join #Ident on #Ident.almstUniq = #DropCounts.almstUniq
left outer join #ProdCounts on #ProdCounts.almstUniq = #DropCounts.almstUniq
where countDrop-isnull(#ProdCounts.countProd,0) > 0
order by #DropCounts.origorderno


INSERT ' + @sourceserver + '.' + @sourcedb + '.dbo.SEQUENCE SELECT sequencenumber FROM #DropCounts
left outer join #ProdCounts ON #ProdCounts.almstUniq = #DropCounts.almstUniq
WHERE #DropCounts.countDrop-isnull(#ProdCounts.countProd,0) > 0

drop table #Ident
drop table #DropCounts
drop table #ProdCounts

DELETE FROM ' + @sourceserver + '.' + @sourcedb + '.dbo.working where timeofexecution >= left(getdate()-0, 11) AND destination = @destination

INSERT INTO ' + @sourceserver + '.' + @sourcedb + '.dbo.Working
(traderid, accountid, prevorderno, destination, symbol, buysell, lastfillquantity, lastfillprice, timeofexecution, clearingfee,liquidityflag, client,
ordernumber, origorderno, basketid, basketnumber, quantity, executedquantity, leavesquantity, ordertype, limitprice, avgfillprice,orderstatus, stopprice,
timeinforce, cntrpartyID,sequencenumber)
     SELECT traderid, accountid, prevorderno, destination, symbol, buysell, lastfillquantity, lastfillprice, timeofexecution, clearingfee,
     liquidityflag, client, ordernumber, origorderno, basketid, basketnumber, quantity, executedquantity, leavesquantity, ordertype, limitprice, avgfillprice,
     orderstatus, stopprice, timeinforce,cntrpartyID,t.sequencenumber
     FROM ' + @sourceserver + '.' + @sourcedb + '.dbo.trade T
     INNER JOIN ' + @sourceserver + '.' + @sourcedb + '.dbo.Sequence X on X.sequencenumber = T.sequencenumber
     WHERE destination = ''' + @Destination + '''


--------------------------  look at them  --------------------------------------------
select * from ' + @sourceserver + '.' + @sourcedb + '.dbo.trade s1
where exists                    
(select 1 from ' + @sourceserver + '.' + @sourcedb + '.dbo.Sequence d1 WHERE s1.sequencenumber = d1.sequencenumber)
AND destination = ''' + @Destination + ''''

EXEC (@SQLStmt)

GO




CREATE proc usp_testA
  @sourceserver varchar(100),
  @sourcedb varchar(100),
  @destinationserver varchar(100),
  @destinationdb varchar(100),
  @destination varchar(5)
as

declare @SQLStmt VARCHAR(8000)


SET @SQLStmt = '
---------------Identifiers from drop and prod-------------
CREATE TABLE #Ident (almstUniq varchar(50))
insert INTO #Ident
select rtrim(ordernumber) as almstUniq
from ' + @sourceserver + '.' + @sourcedb + '.dbo.trade
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''
union
select rtrim(ordernumber) as almstUniq
from ' + @destinationserver + '.' + @destinationdb + '.dbo.trade
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''


---------------# of occurrences in Drop -------------
CREATE TABLE #DropCounts (almstUniq varchar(50), countDrop int, FillQtyDrop int, lastfillprice smallmoney, ordernumber varchar(50),sequencenumber int)
insert INTO #DropCounts
select rtrim(ordernumber) as almstUniq, count(rtrim(cast(ordernumber as varchar)))  as countDrop,
lastfillquantity as FillQtyDrop,lastfillprice,ordernumber,sequencenumber
from ' + @sourceserver + '.' + @sourcedb + '.dbo.trade
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''
group by ordernumber,lastfillquantity,lastfillprice,sequencenumber


---------------# of occurrences in Production  -------------
CREATE TABLE #ProdCounts (almstUniq varchar(50), countProd int)
insert INTO #ProdCounts
select rtrim(ordernumber) as almstUniq, count(rtrim(cast(ordernumber as varchar)))  as countProd
from ' + @destinationserver + '.' + @destinationdb + '.dbo.trade
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''
group by ordernumber,lastfillquantity,lastfillprice


---------------anything that occurs more in the drop than prod -------------
select #dropcounts.sequencenumber,#dropcounts.ordernumber,#dropcounts.FillQtyDrop,#dropcounts.lastfillprice  from #DropCounts
left outer join #Ident on #Ident.almstUniq = #DropCounts.almstUniq
left outer join #ProdCounts on #ProdCounts.almstUniq = #DropCounts.almstUniq
where countDrop-isnull(#ProdCounts.countProd,0) > 0
order by #DropCounts.ordernumber


INSERT ' + @sourceserver + '.' + @sourcedb + '.dbo.SEQUENCE SELECT sequencenumber FROM #DropCounts
left outer join #ProdCounts ON #ProdCounts.almstUniq = #DropCounts.almstUniq
WHERE #DropCounts.countDrop-isnull(#ProdCounts.countProd,0) > 0

drop table #Ident
drop table #DropCounts
drop table #ProdCounts

DELETE FROM ' + @sourceserver + '.' + @sourcedb + '.dbo.working where timeofexecution >= left(getdate()-0, 11) AND destination = @destination

INSERT INTO ' + @sourceserver + '.' + @sourcedb + '.dbo.Working
(traderid, accountid, prevorderno, destination, symbol, buysell, lastfillquantity, lastfillprice, timeofexecution, clearingfee,liquidityflag, client,
ordernumber, origorderno, basketid, basketnumber, quantity, executedquantity, leavesquantity, ordertype, limitprice, avgfillprice,orderstatus, stopprice,
timeinforce, cntrpartyID,sequencenumber)
     SELECT traderid, accountid, prevorderno, destination, symbol, buysell, lastfillquantity, lastfillprice, timeofexecution, clearingfee,
     liquidityflag, client, ordernumber, origorderno, basketid, basketnumber, quantity, executedquantity, leavesquantity, ordertype, limitprice, avgfillprice,
     orderstatus, stopprice, timeinforce,cntrpartyID,t.sequencenumber
     FROM ' + @sourceserver + '.' + @sourcedb + '.dbo.trade T
     INNER JOIN ' + @sourceserver + '.' + @sourcedb + '.dbo.Sequence X on X.sequencenumber = T.sequencenumber
     WHERE destination = ''' + @Destination + '''
     AND timeofexecution >= left(getdate()-0, 11)


--------------------------  look at them  --------------------------------------------
select * from ' + @sourceserver + '.' + @sourcedb + '.dbo.trade s1
where exists                    
(select 1 from ' + @sourceserver + '.' + @sourcedb + '.dbo.sequence d1 WHERE s1.sequencenumber = d1.sequencenumber)
AND destination = ''' + @Destination + '''
AND timeofexecution >= left(getdate()-0, 11)'

EXEC (@SQLStmt)
GO


CREATE proc usp_testC
  @sourceserver varchar(100),
  @sourcedb varchar(100),
  @destinationserver varchar(100),
  @destinationdb varchar(100),
  @destination varchar(5)
as

declare @SQLStmt VARCHAR(8000)


SET @SQLStmt = '
---------------Identifiers from drop and prod-------------
CREATE TABLE #Ident (almstUniq varchar(50))
insert INTO #Ident
select  case WHEN charindex(''_'',ordernumber)>1 THEN left(ordernumber,charindex(''_'',ordernumber) -1)ELSE ordernumber
END as almstUniq
from ' + @sourceserver + '.' + @sourcedb + '.dbo.trade
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''
union
select  case WHEN charindex(''_'',ordernumber)>1 THEN left(ordernumber,charindex(''_'',ordernumber) -1)ELSE ordernumber
END as almstUniq
from ' + @destinationserver + '.' + @destinationdb + '.dbo.trade
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''


---------------# of occurrences in Drop -------------
CREATE TABLE #DropCounts (almstUniq varchar(50),countDrop int,FillQtyDrop int,lAStfillprice smallmoney,ordernumber varchar(50),sequencenumber int)
INSERT INTO #DropCounts  (almstUniq, countDrop, FillQtyDrop, LastFillPrice, ordernumber, sequencenumber)
select case WHEN charindex(''_'',ordernumber)>1 THEN left(ordernumber,charindex(''_'',ordernumber) -1)ELSE ordernumber
END as almstUniq, count(rtrim(cast(ordernumber as varchar)))  as countDrop, lastfillquantity as FillQtyDrop,lastfillprice,ordernumber,sequencenumber
from ' + @sourceserver + '.' + @sourcedb + '.dbo.trade
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''
group by ordernumber,lastfillquantity,lastfillprice,sequencenumber


---------------# of occurrences in Production -------------
CREATE TABLE #ProdCounts (almstUniq varchar(50), countProd int)
insert INTO #ProdCounts
select case WHEN charindex(''_'',ordernumber)>1 THEN left(ordernumber,charindex(''_'',ordernumber) -1)ELSE ordernumber
END as almstUniq, count(rtrim(cast(ordernumber as varchar))) as countProd
from ' + @destinationserver + '.' + @destinationdb + '.dbo.trade
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''
group by ordernumber,lastfillquantity,lastfillprice


---------------anything that occurs more in the drop than prod -------------
select #dropcounts.ordernumber,#dropcounts.FillQtyDrop,#dropcounts.lastfillprice  from #DropCounts
left outer join #Ident on #Ident.almstUniq = #DropCounts.almstUniq
left outer join #ProdCounts on #ProdCounts.almstUniq = #DropCounts.almstUniq
where countDrop-isnull(#ProdCounts.countProd,0) > 0
order by #DropCounts.ordernumber

DELETE FROM ' + @sourceserver + '.' + @sourcedb + '.dbo.working where timeofexecution >= left(getdate()-0, 11) AND destination = @destination

INSERT ' + @sourceserver + '.' + @sourcedb + '.dbo.SEQUENCE SELECT sequencenumber FROM #DropCounts
left outer join #ProdCounts ON #ProdCounts.almstUniq = #DropCounts.almstUniq
WHERE #DropCounts.countDrop-isnull(#ProdCounts.countProd,0) > 0

drop table #Ident
drop table #DropCounts
drop table #ProdCounts

INSERT INTO ' + @sourceserver + '.' + @sourcedb + '.dbo.Working
(traderid, accountid, prevorderno, destination, symbol, buysell, lastfillquantity, lastfillprice, timeofexecution, clearingfee,liquidityflag, client,
ordernumber, origorderno, basketid, basketnumber, quantity, executedquantity, leavesquantity, ordertype, limitprice, avgfillprice,orderstatus, stopprice,
timeinforce, cntrpartyID,sequencenumber)
     SELECT traderid, accountid, prevorderno, destination, symbol, buysell, lastfillquantity, lastfillprice, timeofexecution, clearingfee,
     liquidityflag, client, ordernumber, origorderno, basketid, basketnumber, quantity, executedquantity, leavesquantity, ordertype, limitprice, avgfillprice,
     orderstatus, stopprice, timeinforce,cntrpartyID,t.sequencenumber
     FROM ' + @sourceserver + '.' + @sourcedb + '.dbo.trade T
     INNER JOIN ' + @sourceserver + '.' + @sourcedb + '.dbo.Sequence X on X.sequencenumber = T.sequencenumber
     WHERE destination = ''' + @Destination + '''


--------------------------  look at them  --------------------------------------------
select * from ' + @sourceserver + '.' + @sourcedb + '.dbo.trade s1
where exists                    
(select 1 from ' + @sourceserver + '.' + @sourcedb + '.dbo.sequence d1 WHERE s1.sequencenumber = d1.sequencenumber)
AND destination = ''' + @Destination + ''''

EXEC (@SQLStmt)
GO



CREATE proc usp_testD
  @sourceserver varchar(100),
  @sourcedb varchar(100),
  @destinationserver varchar(100),
  @destinationdb varchar(100),
  @destination varchar(5)
as

declare @SQLStmt VARCHAR(8000)


SET @SQLStmt = '
---------------Identifiers from drop and prod-------------
CREATE TABLE #Ident (almstUniq varchar(50))
insert INTO #Ident

SELECT SUBSTRING(case when charindex(''_'',ordernumber)>1 then left(ordernumber,charindex(''_'',ordernumber) -1) else ordernumber end, PATINDEX(''%[1-9]%'', ordernumber), LEN(ordernumber)) as almstUniq
from ' + @sourceserver + '.' + @sourcedb + '.dbo.trade
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''
union
select  SUBSTRING(case when charindex(''_'',ordernumber)>1 then left(ordernumber,charindex(''_'',ordernumber) -1) else ordernumber end, PATINDEX(''%[1-9]%'', ordernumber), LEN(ordernumber)) as almstUniq
from ' + @destinationserver + '.' + @destinationdb + '.dbo.trade
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''


---------------# of occurrences in Drop -------------
CREATE TABLE #DropCounts (almstUniq varchar(50),countDrop int,FillQtyDrop int,lAStfillprice smallmoney,ordernumber varchar(50),sequencenumber int)
INSERT INTO #DropCounts  (almstUniq, countDrop, FillQtyDrop, LastFillPrice, ordernumber, sequencenumber)
select SUBSTRING(case when charindex(''_'',ordernumber)>1 then left(ordernumber,charindex(''_'',ordernumber) -1) else ordernumber end, PATINDEX(''%[1-9]%'', ordernumber), LEN(ordernumber)) as almstUniq,
count(rtrim(cast(ordernumber as varchar)))  as countDrop, lastfillquantity as FillQtyDrop,lastfillprice,ordernumber,sequencenumber
from ' + @sourceserver + '.' + @sourcedb + '.dbo.trade
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''
group by ordernumber,lastfillquantity,lastfillprice,sequencenumber


---------------# of occurrences in Production [10013-RLEWIS] -------------
CREATE TABLE #ProdCounts (almstUniq varchar(50), countProd int)
insert INTO #ProdCounts
select SUBSTRING(case when charindex(''_'',ordernumber)>1 then left(ordernumber,charindex(''_'',ordernumber) -1) else ordernumber end, PATINDEX(''%[1-9]%'', ordernumber), LEN(ordernumber)) as almstUniq,
count(rtrim(cast(ordernumber as varchar))) as countProd
from ' + @destinationserver + '.' + @destinationdb + '.dbo.trade
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''
group by ordernumber,lastfillquantity,lastfillprice


---------------anything that occurs more in the drop than in prod -------------
select #dropcounts.ordernumber,#dropcounts.FillQtyDrop,#dropcounts.lastfillprice  from #DropCounts
left outer join #Ident on #Ident.almstUniq = #DropCounts.almstUniq
left outer join #ProdCounts on #ProdCounts.almstUniq = #DropCounts.almstUniq
where countDrop-isnull(#ProdCounts.countProd,0) > 0
order by #DropCounts.ordernumber


DELETE FROM ' + @sourceserver + '.' + @sourcedb + '.dbo.working where timeofexecution >= left(getdate()-0, 11) AND destination = @destination

INSERT ' + @sourceserver + '.' + @sourcedb + '.dbo.SEQUENCE SELECT sequencenumber FROM #DropCounts
left outer join #ProdCounts ON #ProdCounts.almstUniq = #DropCounts.almstUniq
WHERE #DropCounts.countDrop-isnull(#ProdCounts.countProd,0) > 0

drop table #Ident
drop table #DropCounts
drop table #ProdCounts

INSERT INTO ' + @sourceserver + '.' + @sourcedb + '.dbo.Working
(traderid, accountid, prevorderno, destination, symbol, buysell, lastfillquantity, lastfillprice, timeofexecution, clearingfee,liquidityflag, client,
ordernumber, origorderno, basketid, basketnumber, quantity, executedquantity, leavesquantity, ordertype, limitprice, avgfillprice,orderstatus, stopprice,
timeinforce, cntrpartyID,sequencenumber)
      SELECT traderid, accountid, prevorderno, destination, symbol, buysell, lastfillquantity, lastfillprice, timeofexecution, clearingfee,
      liquidityflag, client, ordernumber, origorderno, basketid, basketnumber, quantity, executedquantity, leavesquantity, ordertype, limitprice, avgfillprice,
      orderstatus, stopprice, timeinforce,cntrpartyID,t.sequencenumber
        FROM ' + @sourceserver + '.' + @sourcedb + '.dbo.trade T
        INNER JOIN ' + @sourceserver + '.' + @sourcedb + '.dbo.Sequence X on X.sequencenumber = T.sequencenumber
        WHERE destination = ''' + @Destination + '''

--------------------------  look at them  --------------------------------------------
select * from ' + @sourceserver + '.' + @sourcedb + '.dbo.trade s1
where exists                    
(select 1 from ' + @sourceserver + '.' + @sourcedb + '.dbo.sequence d1 WHERE s1.sequencenumber = d1.sequencenumber)
AND destination = ''' + @Destination + ''''

EXEC (@SQLStmt)
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13921535
Can you post the master sp.
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13923440
sure, here it is:
but can you tell me whether or not i'd be able to do the 2-step recon i mentioned ?  you know, one proc to find, one to transfer.  how would that work w/the master proc?

CREATE PROCEDURE usp_Master (
 @sourceserver varchar(100),
 @sourcedb varchar(100),
 @destinationserver varchar(100),
 @destinationdb varchar(100),
 @destination varchar(5)
)
AS
EXEC usp_TestA @sourceserver, @sourcedb, @destinationserver, @destinationdb, @destination
EXEC usp_TestB @sourceserver, @sourcedb, @destinationserver, @destinationdb, @destination
EXEC usp_TestC @sourceserver, @sourcedb, @destinationserver, @destinationdb, @destination
EXEC usp_TestD @sourceserver, @sourcedb, @destinationserver, @destinationdb, @destination


GO
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13927006
Ok, let's continue.  Here are just some questions:

1.  Does the error "Must declare the variable '@destination'." still happen?
2.  Please explain further on the 2-step recon, I am just a little bit confused.
3.  Does all your SPs now work?

That's it for now.
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13927161
1. Yes, it still happens.  apparently once for each exec line of my master proc

Server: Msg 137, Level 15, State 2, Line 53
Must declare the variable '@destination'.
Server: Msg 137, Level 15, State 2, Line 54
Must declare the variable '@destination'.
Server: Msg 137, Level 15, State 2, Line 47
Must declare the variable '@destination'.
Server: Msg 137, Level 15, State 2, Line 47
Must declare the variable '@destination'.

2. ok, see, i need to be able to hand over this as a reconciliation 'routine'.  in short, if any discrepancies are determined to be present, the first proc is run which 'finds' the discrepancies.  then, after review/approval, the second proc is run which transfers them from sourceserver/db to destinationserver/db.  when i said i've 'got four perfectly functional procedures', these were the ones i was talking about -- but because i had different destinations and different almstUniq logic for each, i was unable to do it with one procedure --- i had to have four.  you're helping me combine these into one.  so, when/if we're able to do this, i will hand over a reconciliation routine which includes 2 steps --- 1) Find - 2) Transfer  
And I'm just not sure how the master proc impacts this.

does that make more sense?

and no, none of them work anymore.  which is odd as heck cuz yesterday prior to creating the separate sp's and the master, they worked just fine.  you know - while you and i were doing the earlier testing, they worked wonderfully.  now i'm getting the same output for each:

Server: Msg 137, Level 15, State 2, Line 47
Must declare the variable '@destination'.
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13927216
This should work:

CREATE proc usp_testB
  @sourceserver varchar(100),
  @sourcedb varchar(100),
  @destinationserver varchar(100),
  @destinationdb varchar(100),
  @destination varchar(5)
as

declare @SQLStmt VARCHAR(8000)

SET @SQLStmt = '
---------------Identifiers from drop and prod-------------
CREATE TABLE #Ident (almstUniq varchar(50))
insert INTO #Ident
select  rtrim(cast(origorderno as varchar)) +''-''+ rtrim(cast(lastfillquantity as varchar))+''-''+ rtrim(cast(lastfillprice as varchar)) as almstUniq
from ' + @sourceserver + '.' + @sourcedb + '.dbo.trade
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''
union
select  rtrim(cast(origorderno as varchar)) + ''-'' + rtrim(cast(lastfillquantity as varchar)) +''-''+ rtrim(cast(lastfillprice as varchar)) as almstUniq
from ' + @destinationserver + '.' + @destinationdb + '.dbo.trade
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''


---------------# of occurrences in Drop -------------
CREATE TABLE #DropCounts (almstUniq varchar(50), countDrop int, FillQtyDrop int, lastfillprice smallmoney, origorderno varchar(50),sequencenumber int)
insert INTO #DropCounts
select distinct rtrim(cast(origorderno as varchar)) + ''-'' + rtrim(cast(lastfillquantity as varchar)) +''-''+ rtrim(cast(lastfillprice as varchar))  as almstUniq,
count(rtrim(cast(origorderno as varchar)))  as countDrop, lastfillquantity as FillQtyDrop,lastfillprice,origorderno,sequencenumber
from ' + @sourceserver + '.' + @sourcedb + '.dbo.trade
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''
group by origorderno,lastfillquantity,lastfillprice,sequencenumber


---------------# of occurrences in Production -------------
CREATE TABLE #ProdCounts (almstUniq varchar(50), countProd int)
insert INTO #ProdCounts
select distinct rtrim(cast(origorderno as varchar)) + ''-'' + rtrim(cast(lastfillquantity as varchar)) +''-''+ rtrim(cast(lastfillprice as varchar)) as almstUniq,
count(rtrim(cast(origorderno as varchar)))  as countProd
from ' + @destinationserver + '.' + @destinationdb + '.dbo.trade
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''
group by origorderno,lastfillquantity,lastfillprice


---------------anything that occurs more in the drop than in prod ----
select #dropcounts.sequencenumber,#dropcounts.origorderno,#dropcounts.FillQtyDrop,#dropcounts.lastfillprice  from #DropCounts
left outer join #Ident on #Ident.almstUniq = #DropCounts.almstUniq
left outer join #ProdCounts on #ProdCounts.almstUniq = #DropCounts.almstUniq
where countDrop-isnull(#ProdCounts.countProd,0) > 0
order by #DropCounts.origorderno


INSERT ' + @sourceserver + '.' + @sourcedb + '.dbo.SEQUENCE SELECT sequencenumber FROM #DropCounts
left outer join #ProdCounts ON #ProdCounts.almstUniq = #DropCounts.almstUniq
WHERE #DropCounts.countDrop-isnull(#ProdCounts.countProd,0) > 0

drop table #Ident
drop table #DropCounts
drop table #ProdCounts

DELETE FROM ' + @sourceserver + '.' + @sourcedb + '.dbo.working where timeofexecution >= left(getdate()-0, 11) AND destination = ''' + @Destination + '''

INSERT INTO ' + @sourceserver + '.' + @sourcedb + '.dbo.Working
(traderid, accountid, prevorderno, destination, symbol, buysell, lastfillquantity, lastfillprice, timeofexecution, clearingfee,liquidityflag, client,
ordernumber, origorderno, basketid, basketnumber, quantity, executedquantity, leavesquantity, ordertype, limitprice, avgfillprice,orderstatus, stopprice,
timeinforce, cntrpartyID,sequencenumber)
     SELECT traderid, accountid, prevorderno, destination, symbol, buysell, lastfillquantity, lastfillprice, timeofexecution, clearingfee,
     liquidityflag, client, ordernumber, origorderno, basketid, basketnumber, quantity, executedquantity, leavesquantity, ordertype, limitprice, avgfillprice,
     orderstatus, stopprice, timeinforce,cntrpartyID,t.sequencenumber
     FROM ' + @sourceserver + '.' + @sourcedb + '.dbo.trade T
     INNER JOIN ' + @sourceserver + '.' + @sourcedb + '.dbo.Sequence X on X.sequencenumber = T.sequencenumber
     WHERE destination = ''' + @Destination + '''


--------------------------  look at them  --------------------------------------------
select * from ' + @sourceserver + '.' + @sourcedb + '.dbo.trade s1
where exists                    
(select 1 from ' + @sourceserver + '.' + @sourcedb + '.dbo.Sequence d1 WHERE s1.sequencenumber = d1.sequencenumber)
AND destination = ''' + @Destination + ''''

EXEC (@SQLStmt)

GO




CREATE proc usp_testA
  @sourceserver varchar(100),
  @sourcedb varchar(100),
  @destinationserver varchar(100),
  @destinationdb varchar(100),
  @destination varchar(5)
as

declare @SQLStmt VARCHAR(8000)


SET @SQLStmt = '
---------------Identifiers from drop and prod-------------
CREATE TABLE #Ident (almstUniq varchar(50))
insert INTO #Ident
select rtrim(ordernumber) as almstUniq
from ' + @sourceserver + '.' + @sourcedb + '.dbo.trade
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''
union
select rtrim(ordernumber) as almstUniq
from ' + @destinationserver + '.' + @destinationdb + '.dbo.trade
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''


---------------# of occurrences in Drop -------------
CREATE TABLE #DropCounts (almstUniq varchar(50), countDrop int, FillQtyDrop int, lastfillprice smallmoney, ordernumber varchar(50),sequencenumber int)
insert INTO #DropCounts
select rtrim(ordernumber) as almstUniq, count(rtrim(cast(ordernumber as varchar)))  as countDrop,
lastfillquantity as FillQtyDrop,lastfillprice,ordernumber,sequencenumber
from ' + @sourceserver + '.' + @sourcedb + '.dbo.trade
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''
group by ordernumber,lastfillquantity,lastfillprice,sequencenumber


---------------# of occurrences in Production  -------------
CREATE TABLE #ProdCounts (almstUniq varchar(50), countProd int)
insert INTO #ProdCounts
select rtrim(ordernumber) as almstUniq, count(rtrim(cast(ordernumber as varchar)))  as countProd
from ' + @destinationserver + '.' + @destinationdb + '.dbo.trade
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''
group by ordernumber,lastfillquantity,lastfillprice


---------------anything that occurs more in the drop than prod -------------
select #dropcounts.sequencenumber,#dropcounts.ordernumber,#dropcounts.FillQtyDrop,#dropcounts.lastfillprice  from #DropCounts
left outer join #Ident on #Ident.almstUniq = #DropCounts.almstUniq
left outer join #ProdCounts on #ProdCounts.almstUniq = #DropCounts.almstUniq
where countDrop-isnull(#ProdCounts.countProd,0) > 0
order by #DropCounts.ordernumber


INSERT ' + @sourceserver + '.' + @sourcedb + '.dbo.SEQUENCE SELECT sequencenumber FROM #DropCounts
left outer join #ProdCounts ON #ProdCounts.almstUniq = #DropCounts.almstUniq
WHERE #DropCounts.countDrop-isnull(#ProdCounts.countProd,0) > 0

drop table #Ident
drop table #DropCounts
drop table #ProdCounts

DELETE FROM ' + @sourceserver + '.' + @sourcedb + '.dbo.working where timeofexecution >= left(getdate()-0, 11) AND destination = ''' + @Destination + '''

INSERT INTO ' + @sourceserver + '.' + @sourcedb + '.dbo.Working
(traderid, accountid, prevorderno, destination, symbol, buysell, lastfillquantity, lastfillprice, timeofexecution, clearingfee,liquidityflag, client,
ordernumber, origorderno, basketid, basketnumber, quantity, executedquantity, leavesquantity, ordertype, limitprice, avgfillprice,orderstatus, stopprice,
timeinforce, cntrpartyID,sequencenumber)
     SELECT traderid, accountid, prevorderno, destination, symbol, buysell, lastfillquantity, lastfillprice, timeofexecution, clearingfee,
     liquidityflag, client, ordernumber, origorderno, basketid, basketnumber, quantity, executedquantity, leavesquantity, ordertype, limitprice, avgfillprice,
     orderstatus, stopprice, timeinforce,cntrpartyID,t.sequencenumber
     FROM ' + @sourceserver + '.' + @sourcedb + '.dbo.trade T
     INNER JOIN ' + @sourceserver + '.' + @sourcedb + '.dbo.Sequence X on X.sequencenumber = T.sequencenumber
     WHERE destination = ''' + @Destination + '''
     AND timeofexecution >= left(getdate()-0, 11)


--------------------------  look at them  --------------------------------------------
select * from ' + @sourceserver + '.' + @sourcedb + '.dbo.trade s1
where exists                    
(select 1 from ' + @sourceserver + '.' + @sourcedb + '.dbo.sequence d1 WHERE s1.sequencenumber = d1.sequencenumber)
AND destination = ''' + @Destination + '''
AND timeofexecution >= left(getdate()-0, 11)'

EXEC (@SQLStmt)
GO


CREATE proc usp_testC
  @sourceserver varchar(100),
  @sourcedb varchar(100),
  @destinationserver varchar(100),
  @destinationdb varchar(100),
  @destination varchar(5)
as

declare @SQLStmt VARCHAR(8000)


SET @SQLStmt = '
---------------Identifiers from drop and prod-------------
CREATE TABLE #Ident (almstUniq varchar(50))
insert INTO #Ident
select  case WHEN charindex(''_'',ordernumber)>1 THEN left(ordernumber,charindex(''_'',ordernumber) -1)ELSE ordernumber
END as almstUniq
from ' + @sourceserver + '.' + @sourcedb + '.dbo.trade
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''
union
select  case WHEN charindex(''_'',ordernumber)>1 THEN left(ordernumber,charindex(''_'',ordernumber) -1)ELSE ordernumber
END as almstUniq
from ' + @destinationserver + '.' + @destinationdb + '.dbo.trade
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''


---------------# of occurrences in Drop -------------
CREATE TABLE #DropCounts (almstUniq varchar(50),countDrop int,FillQtyDrop int,lAStfillprice smallmoney,ordernumber varchar(50),sequencenumber int)
INSERT INTO #DropCounts  (almstUniq, countDrop, FillQtyDrop, LastFillPrice, ordernumber, sequencenumber)
select case WHEN charindex(''_'',ordernumber)>1 THEN left(ordernumber,charindex(''_'',ordernumber) -1)ELSE ordernumber
END as almstUniq, count(rtrim(cast(ordernumber as varchar)))  as countDrop, lastfillquantity as FillQtyDrop,lastfillprice,ordernumber,sequencenumber
from ' + @sourceserver + '.' + @sourcedb + '.dbo.trade
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''
group by ordernumber,lastfillquantity,lastfillprice,sequencenumber


---------------# of occurrences in Production -------------
CREATE TABLE #ProdCounts (almstUniq varchar(50), countProd int)
insert INTO #ProdCounts
select case WHEN charindex(''_'',ordernumber)>1 THEN left(ordernumber,charindex(''_'',ordernumber) -1)ELSE ordernumber
END as almstUniq, count(rtrim(cast(ordernumber as varchar))) as countProd
from ' + @destinationserver + '.' + @destinationdb + '.dbo.trade
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''
group by ordernumber,lastfillquantity,lastfillprice


---------------anything that occurs more in the drop than prod -------------
select #dropcounts.ordernumber,#dropcounts.FillQtyDrop,#dropcounts.lastfillprice  from #DropCounts
left outer join #Ident on #Ident.almstUniq = #DropCounts.almstUniq
left outer join #ProdCounts on #ProdCounts.almstUniq = #DropCounts.almstUniq
where countDrop-isnull(#ProdCounts.countProd,0) > 0
order by #DropCounts.ordernumber

DELETE FROM ' + @sourceserver + '.' + @sourcedb + '.dbo.working where timeofexecution >= left(getdate()-0, 11) AND destination = ''' + @Destination + '''

INSERT ' + @sourceserver + '.' + @sourcedb + '.dbo.SEQUENCE SELECT sequencenumber FROM #DropCounts
left outer join #ProdCounts ON #ProdCounts.almstUniq = #DropCounts.almstUniq
WHERE #DropCounts.countDrop-isnull(#ProdCounts.countProd,0) > 0

drop table #Ident
drop table #DropCounts
drop table #ProdCounts

INSERT INTO ' + @sourceserver + '.' + @sourcedb + '.dbo.Working
(traderid, accountid, prevorderno, destination, symbol, buysell, lastfillquantity, lastfillprice, timeofexecution, clearingfee,liquidityflag, client,
ordernumber, origorderno, basketid, basketnumber, quantity, executedquantity, leavesquantity, ordertype, limitprice, avgfillprice,orderstatus, stopprice,
timeinforce, cntrpartyID,sequencenumber)
     SELECT traderid, accountid, prevorderno, destination, symbol, buysell, lastfillquantity, lastfillprice, timeofexecution, clearingfee,
     liquidityflag, client, ordernumber, origorderno, basketid, basketnumber, quantity, executedquantity, leavesquantity, ordertype, limitprice, avgfillprice,
     orderstatus, stopprice, timeinforce,cntrpartyID,t.sequencenumber
     FROM ' + @sourceserver + '.' + @sourcedb + '.dbo.trade T
     INNER JOIN ' + @sourceserver + '.' + @sourcedb + '.dbo.Sequence X on X.sequencenumber = T.sequencenumber
     WHERE destination = ''' + @Destination + '''


--------------------------  look at them  --------------------------------------------
select * from ' + @sourceserver + '.' + @sourcedb + '.dbo.trade s1
where exists                    
(select 1 from ' + @sourceserver + '.' + @sourcedb + '.dbo.sequence d1 WHERE s1.sequencenumber = d1.sequencenumber)
AND destination = ''' + @Destination + ''''

EXEC (@SQLStmt)
GO



CREATE proc usp_testD
  @sourceserver varchar(100),
  @sourcedb varchar(100),
  @destinationserver varchar(100),
  @destinationdb varchar(100),
  @destination varchar(5)
as

declare @SQLStmt VARCHAR(8000)


SET @SQLStmt = '
---------------Identifiers from drop and prod-------------
CREATE TABLE #Ident (almstUniq varchar(50))
insert INTO #Ident

SELECT SUBSTRING(case when charindex(''_'',ordernumber)>1 then left(ordernumber,charindex(''_'',ordernumber) -1) else ordernumber end, PATINDEX(''%[1-9]%'', ordernumber), LEN(ordernumber)) as almstUniq
from ' + @sourceserver + '.' + @sourcedb + '.dbo.trade
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''
union
select  SUBSTRING(case when charindex(''_'',ordernumber)>1 then left(ordernumber,charindex(''_'',ordernumber) -1) else ordernumber end, PATINDEX(''%[1-9]%'', ordernumber), LEN(ordernumber)) as almstUniq
from ' + @destinationserver + '.' + @destinationdb + '.dbo.trade
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''


---------------# of occurrences in Drop -------------
CREATE TABLE #DropCounts (almstUniq varchar(50),countDrop int,FillQtyDrop int,lAStfillprice smallmoney,ordernumber varchar(50),sequencenumber int)
INSERT INTO #DropCounts  (almstUniq, countDrop, FillQtyDrop, LastFillPrice, ordernumber, sequencenumber)
select SUBSTRING(case when charindex(''_'',ordernumber)>1 then left(ordernumber,charindex(''_'',ordernumber) -1) else ordernumber end, PATINDEX(''%[1-9]%'', ordernumber), LEN(ordernumber)) as almstUniq,
count(rtrim(cast(ordernumber as varchar)))  as countDrop, lastfillquantity as FillQtyDrop,lastfillprice,ordernumber,sequencenumber
from ' + @sourceserver + '.' + @sourcedb + '.dbo.trade
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''
group by ordernumber,lastfillquantity,lastfillprice,sequencenumber


---------------# of occurrences in Production [10013-RLEWIS] -------------
CREATE TABLE #ProdCounts (almstUniq varchar(50), countProd int)
insert INTO #ProdCounts
select SUBSTRING(case when charindex(''_'',ordernumber)>1 then left(ordernumber,charindex(''_'',ordernumber) -1) else ordernumber end, PATINDEX(''%[1-9]%'', ordernumber), LEN(ordernumber)) as almstUniq,
count(rtrim(cast(ordernumber as varchar))) as countProd
from ' + @destinationserver + '.' + @destinationdb + '.dbo.trade
where timeofexecution >= left(getdate()-0, 11)
and  Destination = ''' + @Destination + '''
group by ordernumber,lastfillquantity,lastfillprice


---------------anything that occurs more in the drop than in prod -------------
select #dropcounts.ordernumber,#dropcounts.FillQtyDrop,#dropcounts.lastfillprice  from #DropCounts
left outer join #Ident on #Ident.almstUniq = #DropCounts.almstUniq
left outer join #ProdCounts on #ProdCounts.almstUniq = #DropCounts.almstUniq
where countDrop-isnull(#ProdCounts.countProd,0) > 0
order by #DropCounts.ordernumber


DELETE FROM ' + @sourceserver + '.' + @sourcedb + '.dbo.working where timeofexecution >= left(getdate()-0, 11) AND destination = ''' + @Destination + '''

INSERT ' + @sourceserver + '.' + @sourcedb + '.dbo.SEQUENCE SELECT sequencenumber FROM #DropCounts
left outer join #ProdCounts ON #ProdCounts.almstUniq = #DropCounts.almstUniq
WHERE #DropCounts.countDrop-isnull(#ProdCounts.countProd,0) > 0

drop table #Ident
drop table #DropCounts
drop table #ProdCounts

INSERT INTO ' + @sourceserver + '.' + @sourcedb + '.dbo.Working
(traderid, accountid, prevorderno, destination, symbol, buysell, lastfillquantity, lastfillprice, timeofexecution, clearingfee,liquidityflag, client,
ordernumber, origorderno, basketid, basketnumber, quantity, executedquantity, leavesquantity, ordertype, limitprice, avgfillprice,orderstatus, stopprice,
timeinforce, cntrpartyID,sequencenumber)
     SELECT traderid, accountid, prevorderno, destination, symbol, buysell, lastfillquantity, lastfillprice, timeofexecution, clearingfee,
     liquidityflag, client, ordernumber, origorderno, basketid, basketnumber, quantity, executedquantity, leavesquantity, ordertype, limitprice, avgfillprice,
     orderstatus, stopprice, timeinforce,cntrpartyID,t.sequencenumber
        FROM ' + @sourceserver + '.' + @sourcedb + '.dbo.trade T
        INNER JOIN ' + @sourceserver + '.' + @sourcedb + '.dbo.Sequence X on X.sequencenumber = T.sequencenumber
        WHERE destination = ''' + @Destination + '''

--------------------------  look at them  --------------------------------------------
select * from ' + @sourceserver + '.' + @sourcedb + '.dbo.trade s1
where exists                    
(select 1 from ' + @sourceserver + '.' + @sourcedb + '.dbo.sequence d1 WHERE s1.sequencenumber = d1.sequencenumber)
AND destination = ''' + @Destination + ''''

EXEC (@SQLStmt)
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13927264
>> the first proc is run which 'finds' the discrepancies.  then, after review/approval, the second proc is run which transfers them from sourceserver/db to destinationserver/db. <<

From what I understand, there is a manual process in your Find/Transfer routine.  Given this, combining your sps in one sp will not work because you have to stop at the first sp, review/approve then run the succeeding sps accordingly.

The only advantage of a master sp is that it is easier to maintain the 4 individual and smaller sps instead of one BIIIIIIGGGG sp combining all 4 sps.
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13927359
alright, you lost me on that one.  if i've got four procs which all work just fine by themselves to do nothing more than find the discrepancies.  why can they not be combined into one proc that will walk through the different destinations, using the different tsql that is associated with each?
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13927390
Ok, I misunderstood you there before.  If the 4 procs you have is to find the discrepancies, then yes you can combine them using the master sp.  What I thought was the first proc you have was just to find the discrepancy and the others were to transfer them.

Go ahead and combine them using the master sp.
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13927515
ok, that's what i've done, but now everything fails w/this error:

Server: Msg 137, Level 15, State 2, Line 47
Must declare the variable '@destination'.

i still don't understand the master proc, though, but if i can find the flaw and fix it, possibly running it successfully will help make sense.  
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13927612
>> ok, that's what i've done, but now everything fails w/this error: <<

Did you use the latest code that I sent you?
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13927623
yep.  i re-did it after you sent that one
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13927631
Look for any @destination in your code and if it is inside the @SQL variable, replace it with ''' + @destination + '''
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13927633
lemme just try again real quick
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13927649
The problem before was with this part:

DELETE FROM ' + @sourceserver + '.' + @sourcedb + '.dbo.working where timeofexecution >= left(getdate()-0, 11) AND destination = ''' + @Destination + '''

Originally, it was like this:

DELETE FROM ' + @sourceserver + '.' + @sourcedb + '.dbo.working where timeofexecution >= left(getdate()-0, 11) AND destination = @Destination
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13927690
yep, i found one.  just a missing single tick.
and how am i to use the master proc?
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13927712
Given that this is your master proc:

CREATE PROCEDURE usp_Master (
 @sourceserver varchar(100),
 @sourcedb varchar(100),
 @destinationserver varchar(100),
 @destinationdb varchar(100),
 @destination varchar(5)
)
AS
EXEC usp_TestA @sourceserver, @sourcedb, @destinationserver, @destinationdb, @destination
EXEC usp_TestB @sourceserver, @sourcedb, @destinationserver, @destinationdb, @destination
EXEC usp_TestC @sourceserver, @sourcedb, @destinationserver, @destinationdb, @destination
EXEC usp_TestD @sourceserver, @sourcedb, @destinationserver, @destinationdb, @destination
GO

Then simply do an EXEC on it:

EXEC usp_Master 'server1', 'database1', 'server2', 'database2', 'destination'
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13927804
nope, this gest us back where we were yesterday --- i truncated the working table first, it begins to find a good deal of the discrepancies, but then it fails w/this error:

.....
.......
.........
(13 row(s) affected)


(992 row(s) affected)

Server: Msg 2627, Level 14, State 1, Line 56
Violation of PRIMARY KEY constraint 'PK_working_sn'. Cannot insert duplicate key in object 'Working'.
The statement has been terminated.
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13928271
This error is now caused by your INSERT and has nothing to do with the sps running inside a master sp.  Even if you run the sps together in a single sp, this will still happen.
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13928287
I may have to argue that.  I deleted the records from the working table first, then it failed.  And the violation is specific to the sequencenumber, which is an identity column.  It cannot be retrieving duplicated sequencenumbers.
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13928339
Instead of the INSERT, try just executing the SELECT and see if there are duplicates.  Even if your SequenceNumber is an identity column, you are joining it with other tables which may cause the duplicates.
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13928357
what join?  if you're referring to sequence and/or working table, both of them were deleted before invoking the proc.  i'm not sure i follow what you're saying
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13928418
When inserting into the Working table, you are joining the Sequence table with the Trade table.  There might be records in the Trade table that has the same sequence number, thus producing the duplicates.

It is also possible that the Sequence table already has duplicates because the sequence number is being produced by joining 2 tables, namely #DropCounts and #ProdCounts.

Regardless of where the duplicate is coming from, I believe that that is a separate question altogether from the original question that you had.  The original question was how to join your sps together and making the server name and database name as parameters.
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13928460
The identity colum is on the Trade table - the sequencenumbers are all distinct.  The sequence table and the working table were truncated before I ran the proc.  Yes, my original question was how to join them, but I don't understand how i've done that, as I've got four procs, and have to run them each separately in order to retrieve the discrepancies per destination.  Yes, the servername/dbname parameters are very cool.  I like that.  But the ability to perform the recon without having to run four separate procs is really my objective.  Possibly I wasn't clear, maybe?
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13928527
When you run your sps, comment out the part that insert into the Working table on the 3rd sp where the error is encountered and see the data if there are duplicates.  You have to work from there.
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13928536
will do, gimme one sec
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13928718
ok, maybe i figured out what's up.  maybe.  i just commented out the insert like you said, but still it failed w/the pk violation.  so, out of curiosity, i then cut out the insert into working table entirely -- from all procs.  it runs w/out the pk violation, BUT, it runs each proc. see, in my proc there are two sections that will print 'em out.  first, this one:

--------------anything that occurs more in the drop than prod -------------
select #dropcounts.sequencenumber,#dropcounts.ordernumber,#dropcounts.FillQtyDrop,#dropcounts.lastfillprice  from #DropCounts

and then the one at the bottom of each proc that is prefaced w/this:
--------------------------  look at them  --------------------------------------------

So, under normal circumstances, i'd have two chunks of data returned to me.  I don't.  I've got 8.  this is why it's attempting to insert dupe sequencenumbers into the working table.  see, some of the almstUniq logic will work for multiple destinations, but some of it won't.  that's why there are 4 distinctly different approaches.  what do you think?  how can I fix this?
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13928917
>> how can I fix this? <<

Before inserting to the Working table, check if the sequence number being inserted already exists in the same table.
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13928938
why is it running all three procs?  if it's destination A, it should run this proc, if it's destination B, it should run this proc.....
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13928967
sorry, i meant all four procs.  why is it running each of them, as opposed to the one it is supposed to run given the destination i pass in?  like if i run this:

exec trading.dbo.usp_master 'sourcesrv1','sourcedb1','destsrv1','destdb1','aaaa'

i expect it to run usp_TestA.

0
 
LVL 28

Accepted Solution

by:
rafrancisco earned 2000 total points
ID: 13929086
>> why is it running each of them, as opposed to the one it is supposed to run given the destination i pass in? <<

This was one of my questions before, what is the criteria when the sp will run.  If it depends on the value of @destination, then simply put an IF statement there:

CREATE PROCEDURE usp_Master (
 @sourceserver varchar(100),
 @sourcedb varchar(100),
 @destinationserver varchar(100),
 @destinationdb varchar(100),
 @destination varchar(5)
)
AS

IF @destination = 'aaaaa'
    EXEC usp_TestA @sourceserver, @sourcedb, @destinationserver, @destinationdb, @destination
ELSE IF @destinatino = 'bbbbb'
    EXEC usp_TestB @sourceserver, @sourcedb, @destinationserver, @destinationdb, @destination
ELSE IF @destination = 'ccccc'
    EXEC usp_TestC @sourceserver, @sourcedb, @destinationserver, @destinationdb, @destination
ELSE IF @destinatino = 'ddddd'
    EXEC usp_TestD @sourceserver, @sourcedb, @destinationserver, @destinationdb, @destination
GO
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13929272
how bout this --- i've got three that are just singular --- as in, if destination = 'aaaa' do this, if destination = 'bbbb', do this
but i've got one that is if destination in ('cccc','dddd','eeee'), do this

is that alright?
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13929307
CREATE PROCEDURE usp_Master (
 @sourceserver varchar(100),
 @sourcedb varchar(100),
 @destinationserver varchar(100),
 @destinationdb varchar(100),
 @destination varchar(5)
)
AS

IF @destination = 'aaaaa'
    EXEC usp_TestA @sourceserver, @sourcedb, @destinationserver, @destinationdb, @destination

IF @destinatino = 'bbbbb'
    EXEC usp_TestB @sourceserver, @sourcedb, @destinationserver, @destinationdb, @destination

IF @destination IN ( 'ccccc', 'ddddd', 'eeeee')
    EXEC usp_TestC @sourceserver, @sourcedb, @destinationserver, @destinationdb, @destination

IF @destinatino IN ( 'ddddd', 'eeeee')
    EXEC usp_TestD @sourceserver, @sourcedb, @destinationserver, @destinationdb, @destination
GO
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13929319
yep, i just tried it, it worked fine.  BUT, i'd not put the insert into working back in yet.  it did, however, only run one proc.  which is very cool.  so, i'm going to put the insert into working back in and try again.  gimme a few....
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13929401
that is it.  i've just walked through every destination w/out error.  rafran, thank you so much.  i've got a bit of clean up work to do, but i can't tell you how good it feels to have finally been able to get to this point.  your effort was excellent, really.  again, thank you very much.  
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13929411
Glad to be able to help.
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13969757
hey rfran, one more question for you -- i've no problem opening another question, i just wanted to start it this way, so you'd get an email w/notification first....my insert -- with your help, i am finding all the discrepancies just fine.  i want to proceed w/the insert, but it's failing w/this error:  

Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].

just like the other item, i need to be able to specify sourcesrv, sourcedb, destinationsrv, etc.  here is my proc.  can you advise?

CREATE proc usp_Insert
 @sourcesrv varchar(35),
 @sourcedb varchar(25),
 @destinationsrv varchar(35),
 @destinationdb varchar(25),
 @destination varchar(5)
as

set nocount on

declare @SQLStmt varchar(5000)
SET @SQLStmt = 'INSERT INTO '+@destinationsrv+'.'+@destinationdb+'.dbo.Trade
(traderid, accountid, prevorderno, destination, symbol, buysell, lastfillquantity, lastfillprice, timeofexecution,
clearingfee,liquidityflag, client, ordernumber, origorderno, basketid, basketnumber, quantity, executedquantity,
leavesquantity, ordertype, limitprice, avgfillprice,orderstatus, stopprice,timeinforce, cntrpartyID)
   SELECT DISTINCT traderid, accountid, prevorderno, destination, symbol, buysell, lastfillquantity, lastfillprice,
   timeofexecution, clearingfee,liquidityflag, client, ordernumber, origorderno, basketid, basketnumber,quantity,
   executedquantity, leavesquantity, ordertype, limitprice, avgfillprice,orderstatus,stopprice,timeinforce,cntrpartyID
   FROM '+@sourcesrv+'.'+@sourcedb+'.dbo.Working
   WHERE sent = 0 and timeofexecution >= left(getdate()-0,11)
   AND destination = ''' + @destination +'''

DELETE FROM '+@sourcesrv+'.'+@sourcedb+'.dbo.sequence
DELETE FROM '+@sourcesrv+'.'+@sourcedb+'.dbo.working WHERE destination = ''' + @destination + '''

UPDATE '+@sourcesrv+'.'+@sourcedb+'.dbo.working
SET sent = 1 WHERE sent = 0 and timeofexecution >= left(getdate()-0,11)'

EXEC (@SQLStmt)

GO
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question