CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2002
    Posts
    756

    Track database changes

    Hi, ALL,
    I'm trying to implement the solution explained in this.
    However trying to call "SQLExecDirect();" on the very first query "IF NOT EXISTS(SELECT..." returns SQL_DATA_AT_EXEC.

    I don't understand this return code at all. I'm not asking for any data - I'm changing database parameter in order to proceed.
    My DSN is set to connect to the database I'm trying to modify - maybe this is the culprit and I should be connected to "master"?

    Or maybe there is an easier way to track schema changes for MS SQL Server?

    Can someone please advise?

    Thank you.

  2. #2
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    20,396

    Re: Track database changes

    Well, there is some info about SQLExecDirect and SQL_DATA_AT_EXEC here: https://docs.microsoft.com/en-us/sql...ql-server-2017
    Victor Nijegorodov

  3. #3
    Join Date
    Aug 2002
    Posts
    756

    Re: Track database changes

    VictorN,
    I'm passing SQL_NTS to the call. Here is the code:

    Code:
    std::wstring query8 = L"IF EXISTS(SELECT * FROM sys.databases WHERE name = \'" +  pimpl->m_dbName + L"\' AND is_broker_enabled = 0) ALTER DATABASE " + pimpl->m_dbName + L" SET ENABLE_BROKER";
    std::wstring query9 = L"CREATE QUEUE dbo.EventNotificationQueue";
    SQLWCHAR *query;
    query = new SQLWCHAR[query8.size() + 2];
    memset( query, '\0', query8.size() + 2 );
    uc_to_str_cpy( query, query8 );
    ret = SQLExecDirect( m_hstmt, query, SQL_NTS );
    delete query;
    query = NULL;
    if( ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO )
    {
    }
    The ret value becomes "-2".
    As you can see there is no binding, just direct query execution.
    I can try to use parameter for the database name , but it looks like the link I'm following suggests that this query should be run from the master database.

    So I can try both ways.

    Thank you.

  4. #4
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,490

    Re: Track database changes

    I'd first start out by running the scripts in your sample with SSMS. There is quite a bit to get the service broker up and running in sql, so you'll want to make sure it's set up on your sql server instance before you attempt to programmatically attempt to use it.

    Any reason you are using sqlexecdirect rather something object oriented and more modern?

  5. #5
    Join Date
    Aug 2002
    Posts
    756

    Re: Track database changes

    Hi,
    Quote Originally Posted by Arjay View Post
    I'd first start out by running the scripts in your sample with SSMS. There is quite a bit to get the service broker up and running in sql, so you'll want to make sure it's set up on your sql server instance before you attempt to programmatically attempt to use it.
    OK, is there any other way track the schema changes from the C++ program?
    I heard about DDL triggers, but the link I quoted was the first one I found.

    Quote Originally Posted by Arjay View Post
    Any reason you are using sqlexecdirect rather something object oriented and more modern?
    AFAIK, ODBC is the only portable interface between all 3 major platforms - Windows, *nix and OSX.
    In Windows its native, on *nix/Mac you choose between unixODBC, iODBC and some commercial ODBC driver managers.

    All those stuff like ADO are completely not portable.

    Thank you.

  6. #6
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,490

    Re: Track database changes

    OneEyeMan, the article you linked to is tracking data changes, not schema changes. At any rate, I suggested that you run the scripts inside SQLServer Management Studio (SSMS) before trying to execute them in c++ code. You should resolve any issues in the scripts in SSMS. That way it will be easier when you run the script in c ++ if you know it works to begin with. Be aware that certain database permissions are required to manipulate the service broker. The user that you connect to SSMS or ODBC with will need these permissions.

  7. #7
    Join Date
    Aug 2002
    Posts
    756

    Re: Track database changes

    Hi,
    Quote Originally Posted by Arjay View Post
    OneEyeMan, the article you linked to is tracking data changes, not schema changes.
    I beg to differ.
    It is tracking CREATE/ALTER TABLE, which is schema changes, and not the INSERT/UPDATE which is data.


    Quote Originally Posted by Arjay View Post
    At any rate, I suggested that you run the scripts inside SQLServer Management Studio (SSMS) before trying to execute them in c++ code. You should resolve any issues in the scripts in SSMS. That way it will be easier when you run the script in c ++ if you know it works to begin with. Be aware that certain database permissions are required to manipulate the service broker. The user that you connect to SSMS or ODBC with will need these permissions.
    Yes, I understand that.
    I did execute that in the SSMS and it looked like everything was working fine.

    Now out of curiosity - is there a better way to notify about the table creation/deletion or working with this script is the only way?

    If you don't know - that's fine. I will just stick with this solution.

    Thank you.

  8. #8
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,490

    Re: Track database changes

    You are correct about the sample tracking schema changes.

    Just want to mention that I found that I needed to reregister for events after receiving an event. In other words once I got notified, I wouldn't retrieve more events until I reregistered the table. It seemed odd that I would have needed to do that. YMMV.

    As far as other options, the service broker sits on top of message queing (msmq). So at least on Windows, you have an alternative for listening for changes - you can connect to the queue directly through c++ rather than going through sql. Of course, that approach isn't going to portable, and it is doubtful it will be simpler.

  9. #9
    Join Date
    Aug 2002
    Posts
    756

    Re: Track database changes

    Hi,
    Quote Originally Posted by Arjay View Post
    You are correct about the sample tracking schema changes.

    Just want to mention that I found that I needed to reregister for events after receiving an event. In other words once I got notified, I wouldn't retrieve more events until I reregistered the table. It seemed odd that I would have needed to do that. YMMV.
    Which version of SQL Server do you have?

    Quote Originally Posted by Arjay View Post
    As far as other options, the service broker sits on top of message queing (msmq). So at least on Windows, you have an alternative for listening for changes - you can connect to the queue directly through c++ rather than going through sql. Of course, that approach isn't going to portable, and it is doubtful it will be simpler.
    I'm actually curious if its possible to do that with ct-lib call. That way I can work with freeTDS and then make it portable. Have any idea how to do that?

    Thank you.

  10. #10
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,490

    Re: Track database changes

    Quote Originally Posted by OneEyeMan View Post
    Hi,

    Which version of SQL Server do you have?



    I'm actually curious if its possible to do that with ct-lib call. That way I can work with freeTDS and then make it portable. Have any idea how to do that?

    Thank you.
    I've used all versions of sql server including 2016. The broker stuff I did was on sql 2008. Don't know anything about ct-lib or freeTDS.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured