SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Trigger or Store Procedure?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ostinoh
Yak Posting Veteran

66 Posts

Posted - 03/07/2013 :  12:35:11  Show Profile  Reply with Quote
Hello -

Little quick background. My endusers access a table via excel to run reports. I'll call them Table-A and Table-B alternate the data that is inserted with data from Production every 2 hours.

I use Synonyms so they can use and access Table-A that has the last data pulled while the newer data in Table-B is updated and then it switches over behind the scenes so the all the enduser has to do is refresh in excel and they have the current data just pulled.

Hope that makes sense without writing a novel.

In this SSIS package that the 1st Step that runs is

DELETE FROM dbo.BackLogLaborOld
WHERE (datename(dw,PIT)<>'Friday' or datepart(hh,PIT) <> 16)


I want to keep all Friday at 4:00PM data and not have them deleted from either Table-A or Table-B. The issue I'm having is the package seems to be alternating the weeks it keeps the Friday at 4:00. For example in February I only have the Friday at 4:00 data kept for the weeks of the 8th and 22nd. The 1st and 15th do not show any Friday at 4:00 data.

Is there a way I can either create a Trigger or Store Procedure to check Table-A and Table-B for that Friday at 4:00 data and update either table that does not have it in it?

Regards,
David

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 03/07/2013 :  12:40:15  Show Profile  Reply with Quote
Yeah, you should use a stored procedure for this and, depending, on how you run things, you could add it to a SQl Agent Job or include it in your SSIS package.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 03/07/2013 :  13:00:07  Show Profile  Reply with Quote
Apart from the question you asked asto how to create a trigger or stored proc: Do you already know what the root cause of data being saved only on some Friday's is? If you don't, it could very well be that the data is inserted with a timestamp that happens to be a few seconds before 4:00 PM (in which case, datepart(hh,PIT) would return 15.
Go to Top of Page

ostinoh
Yak Posting Veteran

66 Posts

Posted - 03/07/2013 :  13:52:39  Show Profile  Reply with Quote
James and Lamprey -

I mispoked when I said
quote:
The 1st and 15th do not show any Friday at 4:00 data.



It does show up in Table-B but never shows up in Table A. So I would have for Febuary in Table-A Friday's 8th and 22nd and in Table-B 1st and 15th. I thought the way I had it setup the Friday data would always be in both tables after they did the switching in the background.

Any suggestions or thoughts??

Thanks in advance,
David



Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 03/07/2013 :  15:22:49  Show Profile  Reply with Quote
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000