| Author |
Topic |
|
Michael71
Posting Yak Master
126 Posts |
 Posted - 2007-01-16 : 14:48:58
|
| I want BCP to transfer a file from a different server at the time a file hits that server. What avenues can I go about this. And if know how? Please help..... |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-16 : 15:29:05
|
| Bcp doesn't transfer files. It imports/exports data. So is that what you want to do?Tara Kizer |
 |
|
|
Michael71
Posting Yak Master
126 Posts |
Posted - 2007-01-16 : 15:35:31
|
| yes, |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-16 : 15:37:42
|
| How does the file get to the server? Do you have control of that process?Tara Kizer |
|
|
|
Michael71
Posting Yak Master
126 Posts |
Posted - 2007-01-16 : 15:39:11
|
| No, the companies send a flat file to the server. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-16 : 15:43:54
|
| Then you'll want to just schedule the stored procedure to run whenever you know that the file will be there. I typically just run my imports about 15 minutes after the file should have been received. Here's what a sample bcp command would look like:bcp.exe SomeDatabase.dbo.SomeTable in F:\SomeFolder\SomeFile.csv -t, -SsomeServer -T -c -r\r\nYou can wrap it into a stored procedure if you use xp_cmdshell.CREATE PROC ...AS...EXEC xp_cmdshell 'bcp ...'Tara Kizer |
|
|
|
Michael71
Posting Yak Master
126 Posts |
Posted - 2007-01-16 : 15:48:56
|
| Can I implement the BCP to run an import exactly when the Flat file hits. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-16 : 15:53:02
|
| Not really. You'd have to continuously run the stored procedure to check if the file exists. That probably would create an unnecessary load on your database server. To check if a file exists, you can use xp_fileexists, but I wouldn't recommend checking every second for a file.The only way to run bcp after the file hits the server is to modify the process that transfers the file to your server.Tara Kizer |
|
|
|
Michael71
Posting Yak Master
126 Posts |
Posted - 2007-01-16 : 15:58:34
|
| Can I check for the file lets say every 30 mins or hour than tranfer it.Will that cause a strin on my database server. Can you point me to or give me an example of xp_fileexists. Also, how do I modify the process that transfers the file to my server? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-16 : 16:12:52
|
| Checking every half hour would probably be okay, but it would be much better if you knew approximately what time the file should arrive and just have your process run shortly after that.Here's an example of xp_fileexists: EXEC xp_fileexists 'C:\SomeFile.txt'You'd have to talk to the person that wrote the process that transfers the file to your server to figure out how to modify it.Tara Kizer |
|
|
|
Michael71
Posting Yak Master
126 Posts |
Posted - 2007-01-16 : 16:21:02
|
| The person who wrote it is gone. I have their code here. Also, they tranfered it to a MySql after it hit the database base, then FrameMaker database. They want it to hit a different server and then the flat file to be transfered to a SQL Server table. |
|
|
|
Michael71
Posting Yak Master
126 Posts |
Posted - 2007-01-16 : 16:22:14
|
| Are you going to be available in the next few days, I might ask for your help..a little. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-16 : 16:23:54
|
| Yep I'm usually around.Tara Kizer |
|
|
|
Michael71
Posting Yak Master
126 Posts |
Posted - 2007-01-16 : 16:25:10
|
| Thanks. So do you think this will be difficult? |
|
|
|
|
Writing a SP to Start BCP when a flat file hits