none
SET NOEXEC ON has different results in SQL Server 2005 and 2014 RRS feed

  • Pertanyaan

  • I executed a select SQL statement with an unknown table name both in SQL Server 2005 and 2014, but got different results. SQL Server 2005 gave compilation errors while 2014 didn't. Which one is correct?

    My sql statements are something as below.

    set noexec on

    select tablenotexisted.f1 from tablenotexisted

    set noexec off

    I am using SQL Server native client driver.

    Test code is as below:

    #include "stdafx.h"

    #include <oledb.h>  
    #include <oledberr.h>  
    #include "SQLNCLI.h"
    #include <msdasc.h>

    IMalloc*        g_pIMalloc = NULL;

    // DumpErrorInfo queries SQLOLEDB error interfaces, retrieving available  
    // status or error information.  
    void DumpErrorInfo  
        (  
        IUnknown* pObjectWithError,  
        REFIID IID_InterfaceWithError  
        )  
        {  

        // Interfaces used in the example.  
        IErrorInfo*             pIErrorInfoAll          = NULL;  
        IErrorInfo*             pIErrorInfoRecord       = NULL;  
        IErrorRecords*          pIErrorRecords          = NULL;  
        ISupportErrorInfo*      pISupportErrorInfo      = NULL;  
        ISQLErrorInfo*          pISQLErrorInfo          = NULL;  
        ISQLServerErrorInfo*    pISQLServerErrorInfo    = NULL;  

        // Number of error records.  
        ULONG                   nRecs;  
        ULONG                   nRec;  

        // Basic error information from GetBasicErrorInfo.  
        ERRORINFO               errorinfo;  

        // IErrorInfo values.  
        BSTR                    bstrDescription;  
        BSTR                    bstrSource;  

        // ISQLErrorInfo parameters.  
        BSTR                    bstrSQLSTATE;  
        LONG                    lNativeError;  

        // ISQLServerErrorInfo parameter pointers.  
        SSERRORINFO*            pSSErrorInfo = NULL;  
        OLECHAR*                pSSErrorStrings = NULL;  

        // Hard-code an American English locale for the example.  
        DWORD                   MYLOCALEID = 0x0409;  

        // Only ask for error information if the interface supports  
        // it.  
       if (FAILED(pObjectWithError->QueryInterface(IID_ISupportErrorInfo,  
            (void**) &pISupportErrorInfo)))  
           {  
            wprintf_s(L"SupportErrorErrorInfo interface not supported");  
            return;  
            }  
       if (FAILED(pISupportErrorInfo->  
            InterfaceSupportsErrorInfo(IID_InterfaceWithError)))  
            {  
            wprintf_s(L"InterfaceWithError interface not supported");  
            return;  
            }  

        // Do not test the return of GetErrorInfo. It can succeed and return  
        // a NULL pointer in pIErrorInfoAll. Simply test the pointer.  
        GetErrorInfo(0, &pIErrorInfoAll);  

        if (pIErrorInfoAll != NULL)  
            {  
            // Test to see if it's a valid OLE DB IErrorInfo interface   
            // exposing a list of records.  
            if (SUCCEEDED(pIErrorInfoAll->QueryInterface(IID_IErrorRecords,  
                (void**) &pIErrorRecords)))  
                {  
                pIErrorRecords->GetRecordCount(&nRecs);  

                // Within each record, retrieve information from each  
                // of the defined interfaces.  
                for (nRec = 0; nRec < nRecs; nRec++)  
                    {  
                    // From IErrorRecords, get the HRESULT and a reference  
                    // to the ISQLErrorInfo interface.  
                    pIErrorRecords->GetBasicErrorInfo(nRec, &errorinfo);  
                    pIErrorRecords->GetCustomErrorObject(nRec,  
                        IID_ISQLErrorInfo, (IUnknown**) &pISQLErrorInfo);  

                    // Display the HRESULT, then use the ISQLErrorInfo.  
                    wprintf_s(L"HRESULT:\t%#X\n", errorinfo.hrError);  

                    if (pISQLErrorInfo != NULL)  
                        {  
                        pISQLErrorInfo->GetSQLInfo(&bstrSQLSTATE,   
                            &lNativeError);  

                        // Display the SQLSTATE and native error values.  
                        wprintf_s(L"SQLSTATE:\t%s\nNative Error:\t%ld\n",  
                            bstrSQLSTATE, lNativeError);  

                        // SysFree BSTR references.  
                        SysFreeString(bstrSQLSTATE);  

                        // Get the ISQLServerErrorInfo interface from  
                        // ISQLErrorInfo before releasing the reference.  
                        pISQLErrorInfo->QueryInterface(  
                            IID_ISQLServerErrorInfo,  
                            (void**) &pISQLServerErrorInfo);  

                        pISQLErrorInfo->Release();  
                        }  

                    // Test to ensure the reference is valid, then  
                    // get error information from ISQLServerErrorInfo.  
                    if (pISQLServerErrorInfo != NULL)  
                        {  
                        pISQLServerErrorInfo->GetErrorInfo(&pSSErrorInfo,  
                            &pSSErrorStrings);  

                        // ISQLServerErrorInfo::GetErrorInfo succeeds  
                        // even when it has nothing to return. Test the  
                        // pointers before using.  
                        if (pSSErrorInfo)  
                            {  
                            // Display the state and severity from the  
                            // returned information. The error message comes  
                            // from IErrorInfo::GetDescription.  
                            wprintf_s(L"Error state:\t%d\nSeverity:\t%d\n",  
                                    pSSErrorInfo->bState,  
                                    pSSErrorInfo->bClass);  

                            // IMalloc::Free needed to release references  
                            // on returned values. For the example, assume  
                            // the g_pIMalloc pointer is valid.  
                            g_pIMalloc->Free(pSSErrorStrings);  
                            g_pIMalloc->Free(pSSErrorInfo);  
                            }  

                        pISQLServerErrorInfo->Release();  
                        }  

                    if (SUCCEEDED(pIErrorRecords->GetErrorInfo(nRec,  
                        MYLOCALEID, &pIErrorInfoRecord)))  
                        {  
                        // Get the source and description (error message)  
                        // from the record's IErrorInfo.  
                        pIErrorInfoRecord->GetSource(&bstrSource);  
                        pIErrorInfoRecord->GetDescription(&bstrDescription);  

                        if (bstrSource != NULL)  
                            {  
                            wprintf_s(L"Source:\t\t%s\n", bstrSource);  
                            SysFreeString(bstrSource);  
                            }  
                        if (bstrDescription != NULL)  
                            {  
                            wprintf_s(L"Error message:\t%s\n",  
                                bstrDescription);  
                            SysFreeString(bstrDescription);  
                            }  

                        pIErrorInfoRecord->Release();  
                        }  
                    }  

                pIErrorRecords->Release();  
                }  
            else  
                {  
                // IErrorInfo is valid; get the source and  
                // description to see what it is.  
                pIErrorInfoAll->GetSource(&bstrSource);  
                pIErrorInfoAll->GetDescription(&bstrDescription);  

                if (bstrSource != NULL)  
                    {  
                    wprintf_s(L"Source:\t\t%s\n", bstrSource);  
                    SysFreeString(bstrSource);  
                    }  
                if (bstrDescription != NULL)  
                    {  
                    wprintf_s(L"Error message:\t%s\n", bstrDescription);  
                    SysFreeString(bstrDescription);  
                    }  
                }  

            pIErrorInfoAll->Release();  
            }  
        else  
            {  
            wprintf_s(L"GetErrorInfo failed.");  
            }  

        pISupportErrorInfo->Release();  

        return;  
        }  

    int _tmain(int argc, _TCHAR* argv[])
    {
    HRESULT                hr                = S_OK;
    IDBInitialize *        pIDBInitialize    = NULL;
    IDataInitialize *      pIDataInitialize  = NULL;
    IDBCreateSession*   pIDBCreateSession = NULL;
    IDBCreateCommand*   pIDBCreateCommand = NULL;
     ICommandText*       pICommandText = NULL;
     long m_lRowsAffected = 0;

     // Init OLE and set up the DLLs.
        CoInitialize(NULL);

    // Get the task memory allocator.
        if (FAILED(CoGetMalloc(MEMCTX_TASK, &g_pIMalloc)))
            return -1;
    hr = CoCreateInstance(
       CLSID_MSDAINITIALIZE,
       NULL,
       CLSCTX_INPROC_SERVER,
       IID_IDataInitialize,
       reinterpret_cast<LPVOID *>(&pIDataInitialize));
       if (!SUCCEEDED(hr))
          return hr;

    hr = pIDataInitialize->GetDataSource(
       NULL,
       CLSCTX_INPROC_SERVER,
       L"Provider=SQLNCLI11;Data Source=server3;User ID=sa;Password=password;MARS Connection=true",
       
       
       IID_IDBInitialize,
       reinterpret_cast<IUnknown **>(&pIDBInitialize));

     pIDBInitialize->Initialize();
    pIDBInitialize->QueryInterface(IID_IDBCreateSession, (void **) &pIDBCreateSession);

    hr = pIDBCreateSession->CreateSession(NULL, IID_IDBCreateCommand,
            (IUnknown**) &pIDBCreateCommand);

    pIDBCreateCommand->CreateCommand(NULL, IID_ICommandText, (IUnknown**)&pICommandText);

    pICommandText->SetCommandText(DBGUID_DBSQL, L"SET NOEXEC ON");
    hr = pICommandText->Execute(NULL, IID_NULL, NULL, &m_lRowsAffected, NULL);

    pICommandText->SetCommandText(DBGUID_DBSQL, L"select x123.c1 from x123 where x123.c3 ='N'");
    hr = pICommandText->Execute(NULL, IID_NULL, NULL, &m_lRowsAffected, NULL);

    if (FAILED(hr)){
    DumpErrorInfo  
        (  
        pICommandText,  
        IID_ICommandText  
        ); 
    }

    pICommandText->SetCommandText(DBGUID_DBSQL, L"SET NOEXEC OFF");
    hr = pICommandText->Execute(NULL, IID_NULL, NULL, &m_lRowsAffected, NULL);


    return 0;
    }

    Kamis, 01 Juni 2017 06.16

Semua Balasan

  • Hi hong_sen,

    SET NOEXEC是从SQL Server 2008开始才可以使用。所以,由于你是使用的SQL Server 2005,它才报compilation errors。在2014里面得到的结果是正确的。

    具体的信息可以参考MS官方文档:SET NOEXEC (Transact-SQL) 

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    • Diedit oleh Xi Jin Jumat, 02 Juni 2017 05.44
    Jumat, 02 Juni 2017 05.43
  • Hi Xi Jin,

       Thanks a lot for your answer. So If I want to get the compilation error without actually executing the SQL, how to do it?

    Thanks

    Jumat, 02 Juni 2017 07.56
  • Hi hong_sen,

    As I know, there are two ways:

    1. Use SET NOEXEC in SQL Server 2008 or Later.

    2. Try the methods in the following article:

    How to: Validate a Query without Executing

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Jumat, 02 Juni 2017 08.21