May 14, 2008 at 10:00 am
etxeb (5/14/2008)
Matt Miller (5/14/2008)
If the CSV show up once per day/gets modified just once in a while, running something continuously in the hopes of catching the mod time is, well - overkill. Something firing every second to maybe catch something once a day is a god-awful waste of resources. That's worse that the old memory resident programs that would take over your DOS-based machines.....
I'd consider taking it on from the other side. Set up a .NET application, which uses a FileSystemWatcher event (which watches that particular CSV or the folder it's in). When the file is copied in/modified, etc..., it fires the FSW event which triggers your bulk insert/whatever. Damn near NO resource utlization except when the file is actually triggered.
It does not catch an only line per day, it can catch lots of lines each second, but sometimes, they are minutes that there is no change on the csv file. I know that it can be a waste of resources.
And I think that your idea is better than mine. Tell me more about those triggers. It is interesting!!
I have understood that I have to control when is the file modificated and at this moment execute the stored procedure. Can be the best solution for my case:w00t: thanks
Well - the core of what you need to start with is the .Net FileSystemWatcher class, which you will need to get familiar with.
http://msdn.microsoft.com/en-us/library/system.io.filesystemwatcher.aspx
Combining that into a Windows service is likely to be your best bet. Once you wire up the FSW event to fire as you like, triggering the SQL load becomes fairly easy (if nothing else - calling a console process to BCP would likely do the trick).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 14, 2008 at 2:45 pm
May 15, 2008 at 1:30 am
MANU (5/14/2008)
Have you gone thru this link:http://sqldev.net/sqlagent/SQLAgentRecuringJobsInSecs.htm
Manu
Yes, I have. Take a look about the last update date: 2003.
I have to say that the interface that we can see on that web site has nothing to do with the JOB interface of MSSQL server 2005. If I change the code in sql server 2005, I get an error when I open the interface to program the schedule. It is normal because the JOB interface is not programed to treat seconds.
May 15, 2008 at 9:43 am
Matt Miller (5/14/2008)
Combining that into a Windows service is likely to be your best bet. Once you wire up the FSW event to fire as you like, triggering the SQL load becomes fairly easy (if nothing else - calling a console process to BCP would likely do the trick).
I don't know how to explain it:
The FSW application can be "inserted or programmed" in the data base as an stored procedure? It would have any problem with the windows service, as the FSW is listening to the changes made in the scv file?
May 15, 2008 at 9:48 am
etxeb (5/15/2008)
Matt Miller (5/14/2008)
Combining that into a Windows service is likely to be your best bet. Once you wire up the FSW event to fire as you like, triggering the SQL load becomes fairly easy (if nothing else - calling a console process to BCP would likely do the trick).I don't know how to explain it:
The FSW application can be "inserted or programmed" in the data base as an stored procedure? It would have any problem with the windows service, as the FSW is listening to the changes made in the scv file?
No - I'm talking about an external process. Trying to stay solely within the SQL server process is what is going to make this highly inefficient (due to all of the lost cycle just to keep firing this for no reason).
- create the procedure/SSIS package/whatever mechanism to import the updates within SQL server.
- create a separate, free-standing process which starts the FSW, and wires up the event delegate. when the event is triggered, then this free standing process fires up the import or update process you have built.
Building this to operate outside of SQL Server (especially as a Windows Service), should be very "low-resource", and shouldn't interfere with the proper running of the server.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply