Post reply

smtp logging into sql server using ODBC connections

  • February 10, 2016 at 11:25 pm

    #324172

    Hi,

    I have a system where we are using sql server 2012 developer edition on a windows 2008 r2 box. The same box also hosts the smtp server. We are using IIS manager 6.0.

    I followed this link

    https://support.microsoft.com/en-us/kb/303738

    to create the InternetLog table, created an ODBC DSN and enabled ODBC logging for the database table.

    However, nothing is logging into the database when I send out emails and I do not know where to start troubleshooting or where I can get information on what could have gone wrong.

    So, it would be nice if someone could shade some light into what could go wrong and what sort of logs I should be looking into.

    thanks

  • February 12, 2016 at 6:11 am

    #1858316

    What type of ODBC DSN did you actually create? File, System or User?

    EDIT: And what type of OS do you have on your server? It seems to me that this link (https://support.microsoft.com/en-us/kb/303738) is for Windows 2000 and Windows 2003, which I don't think SQL 2012 runs on. So you might be working off outdated information.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • February 12, 2016 at 9:05 am

    #1858406

    Hi Brandie,

    I created a system DSN and the OS is running on Windows 2008r2. The problem is that I couldn't find a lot of information online regarding how to set up the logging using ODBC connection and now I am struggling to make it work.

  • February 12, 2016 at 11:16 am

    #1858461

    So what smtp stuff are you logging? IIS stuff? Or database mail?

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • February 12, 2016 at 11:32 am

    #1858469

    Is your server using a case sensitive or case insensitive collation?

  • February 12, 2016 at 11:36 am

    #1858470

    Also, you may need to use the ODBC logging to determine what is going on. I had to do this many years ago at a previous employer where I was setting up logging of FTP transfers to a Windows FTP site. Our server was using a case sensitive collation and the column names in the MS documentation didn't match the actual column names expected. Using the ODBC logging I was able to discover the actual column names that needed to be defined in the table receiving the FTP logging information.

  • February 17, 2016 at 12:23 pm

    #1859358

    Thanks for you help,

    I was finally able to find out the issue. It is very strange but the ODBC source was trying to login using the service account for SQL Server agent. As soon as I gave write permissions to the particular login on the database, it started working.

    For your second question, I am trying to setup ODBC logging for an smtp server that customers are supposed to be using for sending emails and messages to each other. I need to be able to parse the log somehow and let the customers know if the emails do not go through.

    Thanks,

    Sanjaya

  • February 17, 2016 at 12:27 pm

    #1859359

    Hi Lynn,

    I have a question that you might know the answer to. I have the ODBC logging set up and working for an smtp server. It seems every successful email delivery will have 15 rows inserted into the database and an unsuccessful ones will insert about 5 rows. However, it seems quite impossible to figure out which email created which logs. My end goal is to alert the end users that the email they tried sending didn't go through by looking at the email state. So, my question is, how do you separate the logs created by multiple emails?

    Thanks

  • February 17, 2016 at 3:47 pm

    #1859400

    Srl832 (2/17/2016)

    Hi Lynn,

    I have a question that you might know the answer to. I have the ODBC logging set up and working for an smtp server. It seems every successful email delivery will have 15 rows inserted into the database and an unsuccessful ones will insert about 5 rows. However, it seems quite impossible to figure out which email created which logs. My end goal is to alert the end users that the email they tried sending didn't go through by looking at the email state. So, my question is, how do you separate the logs created by multiple emails?

    Thanks

    I will admit ignorance. My ODBC logging experience is old and was specific to FTP transfers. If I get a chance I can do some research but not sure when that will be. I do know a second set of eyes looking will help, so hope I can do it soon.

  • February 17, 2016 at 4:07 pm

    #1859403

    I understand. There is no pressure 😀

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

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