One Way Interface

  • I am mainly a programmer work as the IT dept. I am not that adept at SQL Server (and database admin) but am learning. My task at hand it to build a one way interface where another company will send records to our database and we process them. I have a question:

    How will I make them able to send the records (they send 1 at a time @ random intervals)? My guess is to assign them a low level password to a table that I have set up to recieve their data.

  • How will they be sending the data to you? Emailing in an Excel spreadsheet, FTP'ing a zipped text file, or something else? Without knowing too many more details, I would not reccommend allowing them direct acces to your tables. If they are sending you files, you could create a DTS package that picks them up and processes them into your table.

    Sean

  • I'm really not sure about that either 🙂 I spoke with them on the phone a little and they mentioned UUCP, LPR, FTP, & smtp. I don't know what the first two are and am not sure what format they would ftp it in. I just don't know what the limitations would be of each or what would be best.

    Thanks in advance,

    Michael

  • Michael,

    I'd recommend thinking of it as two parts. One is the file transport (email, FTP, burn to CD, whatever). The second is the processing. You need to think about how the data will be formatted in comparison to your tables and how you will load it (DTS as Sean suggested, BCP maybe). DTS definitely an easy to work with option.

    Andy

  • The second part is dependant on the first part but What I believe I need to do with the information is to make a few calculations, send a few emails (using the data I receive), and store it in different tables. I'm still just not understanding how I will get the data.

    Thanks in advance,

    Michael

  • You could setup a table, give a login/user rights to just this table and let the other company BCP in.

    If you do, I'd recommend setting up a field that has a timestamp that you fill in with a default. That way you can keep track of what is sent when.

    Steve Jones

    steve@dkranch.net

  • >>"give a login/user rights to just this table and let the other company BCP in."

    That is what I was thinking (what does BCP stand for?). I called the company and they said the transmisions they send to other co's now are via SMTP. But I am still lost :)(They also said that they will have better documentation on their transmissions in a few weeks) so hopefully I will be able to postpone the project until then.

    Thanks,

    Michael

  • SMTP is Internet email.

    BCP is a utility that ships with SQL Server. It can be distributed to your partner and can run on any windows workstation. The workstation will need the MDAC data components and port 1433 open to your server.

    If this is not a fairly trusted company, then I would recommend they file transfer to you. there are any number of ways to do this.

    Then you can schedule something to import the file. Once you have more definite information on how this can flow, start a new thread and we will help.

    One note, if you want to automate this, then the file you recieve should be in the same format all the time.

    Steve Jones

    steve@dkranch.net

  • BCP = Bulk Copy Program. It's been a while since I've actually said the words, I'm only 99.44% sure that the "P" is for "program". Could be "process", but I doubt it.

  • Thanks for all the help given 🙂 I'll definantly be posting once I get the documentation outlining their data transfer procedures.

    Thanks again,

    Michael

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply