Ошибка при выполнении запроса openquery sqlexecdirect

Symptoms

Distributed queries that use the OpenQuery function to update, delete, or insert data in the following way

exec sp_dropserver 'linked1', 'droplogins'
exec sp_addlinkedserver 'linked1', 'SQL Server'
exec sp_setnetname  'linked1', '<servername>'
exec sp_addlinkedsrvlogin 'linked1', 'false', null, '<login name>', '<password>'
SET ANSI_NULLS ON
go
SET ANSI_WARNINGS ON
go
select * from openquery (linked1, 'update testlinked set ssn=ssn+1')
select * from openquery (linked1, 'insert into  testlinked  (ssn) values (1000)')
select * from openquery (linked1, 'delete from  testlinked  where ssn=1')

may generate the following error messages:

Server: Msg 7357, Level 16, State 2, Line 1 Could not process object ‘update testlinked set ssn=ssn’. The OLE DB provider ‘SQLOLEDB’ indicates that the object has no columns.

Server: Msg 7357, Level 16, State 2, Line 1 [Microsoft][ODBC SQL Server Driver][SQL Server]Could not process object ‘update testlinked set ssn=ssn’. The OLE DB provider ‘MSDASQL’ indicates that the object has no columns.

The actual text message of the error may vary depending on the OLE DB provider and the operation (UPDATE, INSERT, or DELETE) being performed, but the error number is always 7357.

If you are using Microsoft SQL Server 2005, you receive the following error message:

Server: Msg 7357, Level 16, State 2, Line 1 Cannot process the object «update testlinked set ssn=ssn». The OLE DB provider «SQLOLEDB» for linked server «ServerName» indicates that either the object has no columns or the current user does not have permissions on that object.

Cause

OpenQuery requires a result set to be returned, but UPDATE, DELETE, and INSERT statements that are used with OpenQuery do not return a result set.

Workaround

You can work around this problem in the following ways:

  1. Use four-part names (linked_server_name.catalog.schema.object_name) to do insert, update, or delete operations.

  2. As documented in SQL Server Books Online, reference the OpenQuery function as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. The following queries demonstrate proper usage with the SQL Server OLE DB provider:

    update openquery(linked1, 'select ssn from testlinked where ssn=2')
    set ssn=ssn + 1
    insert openquery(linked1, 'select ssn from testlinked where 1=0') values (1000)
    delete openquery(linked1, 'select ssn from testlinked where ssn>100')

    Note In the INSERT statement, a where 1=0 predicate is used to avoid retrieving data from the remote server, which can result in slower performance. Also, UPDATE and DELETE operations have special index requirements; see the «More Information» section for details.

More Information

Unique Index Requirement

The SQL Server OLE DB provider requires that a unique index exist on the underlying table for UPDATE or DELETE operations. If no unique index exists on a remote table, the following error occurs when an UPDATE or DELETE is attempted:

Server: Msg 7320, Level 16, State 2, Line 1 Could not execute query against OLE DB provider ‘SQLOLEDB’. The provider could not support a required row lookup interface. The provider indicates that conflicts occurred with other properties or requirements. [OLE/DB provider returned message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

This applies both to OpenQuery and four-part named UPDATE and DELETE operations. The problem is resolved by adding a unique index on the remote table.

Dynamic Execution with OpenQuery

It may sometimes be desirable to use a dynamic query to achieve the same effect using OpenQuery, as shown in the following example:

begin tran
SET QUOTED_IDENTIFIER OFF
SET XACT_ABORT ON
declare @cmd varchar(2500) 
declare @cmd1 varchar(2500) 
declare @var varchar(20) 
set @var = 'White' 
declare @var1 varchar(20) 
set @var1 = 'White1' 
declare @var2 varchar(20) 
set @var2 = 'Johnson1'
select @cmd = "Update openquery(linked1,'select au_lname, au_fname from pubs.dbo.authors
where au_lname = ''" + @var + "''' )
set au_lname = '" + @var1 + "',
au_fname = '" + @var2 + "'"
exec ( @cmd )
commit tran
select * from <servername>.pubs.dbo.authors


Need more help?

I am testing SQL Server on C++ and got this error:

  • When I execute «SELECT» query alone, it works fine
    SELECT
  • When I execute «UPDATE» query alone, it also works fine
    UPDATE
  • But when I execute any queries after my «SELECT» query, I got SQL_ERROR
    SELECT THEN UPDATE

This is my code:

#include "stdafx.h"
#include <iostream>
#include <windows.h>
#include <sqlext.h>
#include <sqltypes.h>
#include <sql.h>
#include <string>
#include <codecvt>
using namespace std;

#define SQL_RESULT_LEN 240
#define SQL_RETURN_CODE_LEN 1000

int main() {
    string username, password;
    int mode;

    string query;
    wstring_convert<std::codecvt_utf8_utf16<wchar_t>> converter;
    wstring wstr;

    SQLHANDLE sqlConnHandle;
    SQLHANDLE sqlStmtHandle;
    SQLHANDLE sqlEnvHandle;
    SQLWCHAR retconstring[SQL_RETURN_CODE_LEN];

    sqlConnHandle = NULL;
    sqlStmtHandle = NULL;

    if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &sqlEnvHandle))
        goto COMPLETED;
    if (SQL_SUCCESS != SQLSetEnvAttr(sqlEnvHandle, SQL_ATTR_ODBC_VERSION, 
    (SQLPOINTER)SQL_OV_ODBC3, 0))
        goto COMPLETED;
    if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_DBC, sqlEnvHandle, &sqlConnHandle))
        goto COMPLETED;

    cout << "Attempting connection to SQL Server...";
    cout << "n";

    switch (SQLDriverConnect(sqlConnHandle,
        NULL,
        (SQLWCHAR*)L"DRIVER={SQL Server};SERVER=localhost, 
        1433;DATABASE=login;UID=sa;PWD=beth12po;",
        SQL_NTS,
        retconstring,
        1024,
        NULL,
        SQL_DRIVER_NOPROMPT))
    {
    case SQL_SUCCESS:
        cout << "Successfully connected to SQL Server";
        cout << "n";
        break;
    case SQL_SUCCESS_WITH_INFO:
        cout << "Successfully connected to SQL Server";
        cout << "n";
        break;
    case SQL_INVALID_HANDLE:
        cout << "Could not connect to SQL Server";
        cout << "n";
        goto COMPLETED;
    case SQL_ERROR:
        cout << "Could not connect to SQL Server";
        cout << "n";
        goto COMPLETED;
    default:
        break;
    }

    if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_STMT, sqlConnHandle, &sqlStmtHandle))
        goto COMPLETED;

    cout << "Enter username: ";
    getline(cin, username);
    cout << "Enter password: ";
    getline(cin, password);

    cout << "n";
    cout << "Logging in...";
    cout << "n";

    query = "SELECT * FROM Account WHERE username='" + username + "';";
    wstr = converter.from_bytes(query);
    wcout << wstr << endl;

    if (SQL_SUCCESS != SQLExecDirect(sqlStmtHandle, (SQLWCHAR*)wstr.c_str(), SQL_NTS)) {
        cout << "ERROR" << endl;
    }
    else {
        cout << "SUCCESS" << endl;
    }

    query = "UPDATE Account SET status = 1 WHERE username='" + username + "';";
    wstr = converter.from_bytes(query);
    wcout << wstr << endl;

    if (SQL_SUCCESS != SQLExecDirect(sqlStmtHandle, (SQLWCHAR*)wstr.c_str(), SQL_NTS)) {
        cout << "ERROR" << endl;
    }
    else {
        cout << "SUCCESS" << endl;
    }

COMPLETED:
    SQLFreeHandle(SQL_HANDLE_STMT, sqlStmtHandle);
    SQLDisconnect(sqlConnHandle);
    SQLFreeHandle(SQL_HANDLE_DBC, sqlConnHandle);
    SQLFreeHandle(SQL_HANDLE_ENV, sqlEnvHandle);

    cout << "nPress any key to exit...";
    getchar();
}

This is my Database:
Design DB
Data DB

Anyone knows how SQLEXECDIRECT works and why it returns error after «SELECT» query? I’m new to SQL Server

I am testing SQL Server on C++ and got this error:

  • When I execute «SELECT» query alone, it works fine
    SELECT
  • When I execute «UPDATE» query alone, it also works fine
    UPDATE
  • But when I execute any queries after my «SELECT» query, I got SQL_ERROR
    SELECT THEN UPDATE

This is my code:

#include "stdafx.h"
#include <iostream>
#include <windows.h>
#include <sqlext.h>
#include <sqltypes.h>
#include <sql.h>
#include <string>
#include <codecvt>
using namespace std;

#define SQL_RESULT_LEN 240
#define SQL_RETURN_CODE_LEN 1000

int main() {
    string username, password;
    int mode;

    string query;
    wstring_convert<std::codecvt_utf8_utf16<wchar_t>> converter;
    wstring wstr;

    SQLHANDLE sqlConnHandle;
    SQLHANDLE sqlStmtHandle;
    SQLHANDLE sqlEnvHandle;
    SQLWCHAR retconstring[SQL_RETURN_CODE_LEN];

    sqlConnHandle = NULL;
    sqlStmtHandle = NULL;

    if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &sqlEnvHandle))
        goto COMPLETED;
    if (SQL_SUCCESS != SQLSetEnvAttr(sqlEnvHandle, SQL_ATTR_ODBC_VERSION, 
    (SQLPOINTER)SQL_OV_ODBC3, 0))
        goto COMPLETED;
    if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_DBC, sqlEnvHandle, &sqlConnHandle))
        goto COMPLETED;

    cout << "Attempting connection to SQL Server...";
    cout << "n";

    switch (SQLDriverConnect(sqlConnHandle,
        NULL,
        (SQLWCHAR*)L"DRIVER={SQL Server};SERVER=localhost, 
        1433;DATABASE=login;UID=sa;PWD=beth12po;",
        SQL_NTS,
        retconstring,
        1024,
        NULL,
        SQL_DRIVER_NOPROMPT))
    {
    case SQL_SUCCESS:
        cout << "Successfully connected to SQL Server";
        cout << "n";
        break;
    case SQL_SUCCESS_WITH_INFO:
        cout << "Successfully connected to SQL Server";
        cout << "n";
        break;
    case SQL_INVALID_HANDLE:
        cout << "Could not connect to SQL Server";
        cout << "n";
        goto COMPLETED;
    case SQL_ERROR:
        cout << "Could not connect to SQL Server";
        cout << "n";
        goto COMPLETED;
    default:
        break;
    }

    if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_STMT, sqlConnHandle, &sqlStmtHandle))
        goto COMPLETED;

    cout << "Enter username: ";
    getline(cin, username);
    cout << "Enter password: ";
    getline(cin, password);

    cout << "n";
    cout << "Logging in...";
    cout << "n";

    query = "SELECT * FROM Account WHERE username='" + username + "';";
    wstr = converter.from_bytes(query);
    wcout << wstr << endl;

    if (SQL_SUCCESS != SQLExecDirect(sqlStmtHandle, (SQLWCHAR*)wstr.c_str(), SQL_NTS)) {
        cout << "ERROR" << endl;
    }
    else {
        cout << "SUCCESS" << endl;
    }

    query = "UPDATE Account SET status = 1 WHERE username='" + username + "';";
    wstr = converter.from_bytes(query);
    wcout << wstr << endl;

    if (SQL_SUCCESS != SQLExecDirect(sqlStmtHandle, (SQLWCHAR*)wstr.c_str(), SQL_NTS)) {
        cout << "ERROR" << endl;
    }
    else {
        cout << "SUCCESS" << endl;
    }

COMPLETED:
    SQLFreeHandle(SQL_HANDLE_STMT, sqlStmtHandle);
    SQLDisconnect(sqlConnHandle);
    SQLFreeHandle(SQL_HANDLE_DBC, sqlConnHandle);
    SQLFreeHandle(SQL_HANDLE_ENV, sqlEnvHandle);

    cout << "nPress any key to exit...";
    getchar();
}

This is my Database:
Design DB
Data DB

Anyone knows how SQLEXECDIRECT works and why it returns error after «SELECT» query? I’m new to SQL Server

I have been trying to write an R script to query Impala database. Here is the query to the database:

select columnA, max(columnB) from databaseA.tableA where columnC in (select distinct(columnC) from databaseB.tableB ) group by columnA order by columnA

When I run this query manually (read: outside the Rscript via impala-shell), I am able to get the table contents. However, when the same is tried via the R script, I get the following error:

[1] "HY000 140 [Cloudera][ImpalaODBC] (140) Unsupported query."       
[2] "[RODBC] ERROR: Could not SQLExecDirect 'select columnA, max(columnB) from databaseA.tableA where columnC in (select distinct(columnC) from databaseB.tableB ) group by columnA order by columnA'
closing unused RODBC handle 1

Why does the query fail when tried via R? and how do I fix this? Thanks in advance :)

Edit 1:

The connection script looks as below:

library("RODBC");
connection <- odbcConnect("Impala");
query <- "select columnA, max(columnB) from databaseA.tableA where columnC in (select distinct(columnC) from databaseB.tableB ) group by columnA order by columnA";
data <- sqlQuery(connection,query);
  • Remove From My Forums

  • Вопрос

  • I am attempting to use ODBC in c++ to connect to a sql database, submit a query, and retrieve the results.  Currently I have successfully connected to the database, but I’m stuck on my query.  My call to SQLExecDirect returns an error.  I’ve
    tried a lot of different iterations, but can’t seem to either get SQLExecDirect to execute successfully or figure out what exactly the error is.  My code is as follows:

    // initialize handles
	SQLHENV handleEnvironment;
	SQLHDBC handleConnection;
	SQLHSTMT handleStatement;

	// allocate environment handle
	SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &handleEnvironment);

	// set odbc driver version for environment
	SQLSetEnvAttr(handleEnvironment,SQL_ATTR_ODBC_VERSION,(void *)SQL_OV_ODBC3, 0);

	// allocate connection handle
	SQLAllocHandle(SQL_HANDLE_DBC, handleEnvironment, &handleConnection);

	// connect to database
	SQLRETURN ret;
	ret = SQLDriverConnect(handleConnection, NULL, (SQLWCHAR*)TEXT("DRIVER={SQL Native Client};SERVER=MyComputerName;DATABASE=MyDatabaseName;Trusted_Connection=yes;"), SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT);
 
	// report status
	if (SQL_SUCCEEDED(ret)) cout << "Connected" << endl; 
	else cout << "Connection failed" << endl;

	// allocate statement handle
	ret = SQLAllocHandle(SQL_HANDLE_STMT, handleConnection, &handleStatement);
	if (SQL_SUCCEEDED(ret)) cout << "Statement handle allocated" << endl;
	else cout << "Handle allocation failed" << endl;

	// initialize query
	SQLWCHAR SQLStmt[255] = {0};
	strcpy((char*)SQLStmt, "SELECT * FROM TableName");

	// execute query
	ret = SQLExecDirect(handleStatement, SQLStmt, SQL_NTS);
	if (ret == SQL_SUCCESS) cout << "SQL_SUCCESS" << endl;
	else if (ret == SQL_SUCCESS_WITH_INFO) cout << "SQL_SUCCESS_WITH_INFO" << endl;
	else if (ret == SQL_NEED_DATA) cout << "SQL_NEED_DATA" << endl;
	else if (ret == SQL_STILL_EXECUTING) cout << "SQL_STILL_EXECUTING" << endl;
	else if (ret == SQL_ERROR) cout << "SQL_ERROR" << endl;
	else if (ret == SQL_NO_DATA) cout << "SQL_NO_DATA" << endl;
	else if (ret == SQL_INVALID_HANDLE) cout << "SQL_INVALID_HANDLE" << endl;
	else cout << "return value not found" << endl;

	// initialize error message variables
	SQLWCHAR sqlState[256],
       errorMsg[256];
	SDWORD    nativeError = 0L;
  SWORD    pcbErrorMsg = 0;
  SQLSMALLINT  recordNumber = 1;

	// get error
	ret = SQLGetDiagRec(SQL_HANDLE_STMT, handleStatement, 
			recordNumber, sqlState, &nativeError,
      errorMsg, 255, &pcbErrorMsg);

	// report error
	if (SQL_SUCCEEDED(ret)) 
	{
		cout << sqlState << "t" << recordNumber << "t" << nativeError << "t" << errorMsg << endl;
		printf("%s:%ld:%ld:%sn", sqlState, recordNumber, nativeError, errorMsg);
	}

    When I run this code, I get the following output:

    Connected
    Statement handle allocated
    SQL_ERROR
    00000000001DF9B0 1
    102 00000000001DFBB0
    4:1:102:[

    What do I need to change to get this SQLExecDirect to execute successfully?  How do I determine exactly what the problem is?  I am using sql server 2005.

Ответы

  • Try again

       
    SQLExecDirect(handleStatement, L»SELECT * FROM TableName», SQL_NTS);

    and when you receive the error details display them with
    wcout:

       
    wcout << sqlState << endl << errorMsg << endl;

    What error do you see?

    • Помечено в качестве ответа

      19 ноября 2010 г. 15:31

  • Try this syntax:
    SELECT * FROM [2010].

    • Помечено в качестве ответа
      digiplant
      19 ноября 2010 г. 15:31

привет там я изучаю C и ODBC
я получил ошибку при вызове функции SQLExecDirect.
Помогите мне, пожалуйста
вот код

#include <Windows.h>
#include <sql.h>
#include <sqlext.h>

int main( void )
{
//DB connect example
SQLRETURN   nResult     = 0;
SQLHANDLE   handleEnv   = 0;
nResult = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, ( SQLHANDLE* )&handleEnv );
//nResult = SQLSetEnvAttr( handleEnv, SQL_ATTR_ODBC_VERSION, ( SQLPOINTER )SQL_OV_ODBC2, SQL_IS_INTEGER );
nResult = SQLSetEnvAttr( handleEnv, SQL_ATTR_ODBC_VERSION, ( SQLPOINTER )SQL_OV_ODBC3, SQL_IS_INTEGER );
nResult = SQLSetEnvAttr( handleEnv, SQL_ATTR_CONNECTION_POOLING, (SQLPOINTER)SQL_CP_ONE_PER_HENV, SQL_IS_INTEGER);

SQLHANDLE   handleDBC   = 0;
nResult = SQLAllocHandle( SQL_HANDLE_DBC, handleEnv, ( SQLHANDLE* )&handleDBC );

SQLCHAR     strConnect[ 256 ]       = "DSN=MY TEST DB; UID=TESTsa; PWD=TESTpw";
SQLCHAR     strConnectOut[ 1024 ]   = {0};
SQLSMALLINT nNumOut                 = 0;
nResult = SQLDriverConnect( handleDBC, NULL, ( SQLCHAR* )strConnect, SQL_NTS, ( SQLCHAR* )strConnectOut, sizeof(strConnectOut),
&nNumOut, SQL_DRIVER_NOPROMPT );

SQLHSTMT    handleStatement = 0;
//nResult = ::SQLAllocStmt( handleDBC, &handleStatement );
nResult = SQLAllocHandle( SQL_HANDLE_STMT, handleDBC, ( SQLHANDLE* )&handleStatement );

SQLHSTMT    hstmt       = handleStatement;
int         sqlResultOutput = 0;

SQLINTEGER  cbValue     = 0;
nResult = SQLBindParameter( hstmt, 1, SQL_PARAM_OUTPUT, SQL_C_SLONG,    SQL_INTEGER,    0, 0, &sqlResultOutput,  0, &cbValue );

//here is the problem
nResult = SQLExecDirect( hstmt, (SQLTCHAR*) "{CALL my_TEST_SP(?)}", SQL_NTS );
//nResult Value is -1 and SQL State is 24000

return 0;
}

каждый результат звонка в порядке. вернуть SQL_SUCCESS все, кроме одного
Функция SQLExecDirect возвращает -1, а SQLState — 24000

хранимая процедура проста, как это

CREATE PROCEDURE my_TEST_SP
@retValue int = 0 output
AS
BEGIN
SET NOCOUNT ON

SET @retValue = 0

SELECT myTestCol
FROM tTestTable

IF @@ERROR <> 0
BEGIN
SET @retValue = -1
END
END

моя ОС windows7.
Использование Visual Studio 2010 SP1, SQL Server 2012

что я скучаю? пожалуйста, скажите мне и хорошего дня 🙂

2

Решение

Основным отличием является версия драйвера odbc. Моя версия 2014.120.2000.08, Я не думаю, что это связано с различными версиями Visual Studio — я думаю, что они все используют одинаково odbc32.lib под капотом.

Следующий код работает для меня: я изменил хранимую процедуру, чтобы она возвращала что-то, что не равно 0 (чтобы убедиться, что я действительно могу прочитать возвращенные данные):

 @retValue int = 0 output
AS
BEGIN
SET NOCOUNT ON

SET @retValue = 3499

SELECT myTestCol
FROM tTestTable

IF @@ERROR <> 0
BEGIN
SET @retValue = -1
END
END

И я использовал следующий код в Visual Studio 2013:

void printErrDbc(SQLHDBC hDbc)
{
SQLSMALLINT recNr = 1;
SQLRETURN ret = SQL_SUCCESS;
while (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
{
SQLWCHAR errMsg[SQL_MAX_MESSAGE_LENGTH + 1];
SQLWCHAR sqlState[5 + 1];
errMsg[0] = 0;
SQLINTEGER nativeError;
SQLSMALLINT cb = 0;
ret = SQLGetDiagRec(SQL_HANDLE_DBC, hDbc, recNr, sqlState, &nativeError, errMsg, SQL_MAX_MESSAGE_LENGTH + 1, &cb);
if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
{
std::wcerr << L"ERROR; native: " << nativeError << L"; state: " << sqlState << L"; msg: " << errMsg << std::endl;
}
++recNr;
}
}

void printErrStmt(SQLHSTMT hStmt)
{
SQLSMALLINT recNr = 1;
SQLRETURN ret = SQL_SUCCESS;
while (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
{
SQLWCHAR errMsg[SQL_MAX_MESSAGE_LENGTH + 1];
SQLWCHAR sqlState[5 + 1];
errMsg[0] = 0;
SQLINTEGER nativeError;
SQLSMALLINT cb = 0;
ret = SQLGetDiagRec(SQL_HANDLE_STMT, hStmt, recNr, sqlState, &nativeError, errMsg, SQL_MAX_MESSAGE_LENGTH + 1, &cb);
if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
{
std::wcerr << L"ERROR; native: " << nativeError << L"; state: " << sqlState << L"; msg: " << errMsg << std::endl;
}
++recNr;
}
}

int _tmain(int argc, _TCHAR* argv[])
{
//DB connect example
SQLRETURN   nResult = 0;
SQLHANDLE   handleEnv = 0;
nResult = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, (SQLHANDLE*)&handleEnv);
//nResult = SQLSetEnvAttr( handleEnv, SQL_ATTR_ODBC_VERSION, ( SQLPOINTER )SQL_OV_ODBC2, SQL_IS_INTEGER );
nResult = SQLSetEnvAttr(handleEnv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, SQL_IS_INTEGER);
nResult = SQLSetEnvAttr(handleEnv, SQL_ATTR_CONNECTION_POOLING, (SQLPOINTER)SQL_CP_ONE_PER_HENV, SQL_IS_INTEGER);

SQLHANDLE   handleDBC = 0;
nResult = SQLAllocHandle(SQL_HANDLE_DBC, handleEnv, (SQLHANDLE*)&handleDBC);

SQLWCHAR     strConnect[256] = L"DSN=test;UID=sa;PWD=MySecretPassword";
SQLWCHAR     strConnectOut[1024] = { 0 };
SQLSMALLINT nNumOut = 0;
nResult = SQLDriverConnect(handleDBC, NULL, (SQLWCHAR*)strConnect, SQL_NTS, (SQLWCHAR*)strConnectOut, sizeof(strConnectOut),
&nNumOut, SQL_DRIVER_NOPROMPT);
if (!SQL_SUCCEEDED(nResult))
{
printErrDbc(handleDBC);
}

SQLHSTMT    handleStatement = 0;
//nResult = ::SQLAllocStmt( handleDBC, &handleStatement );
nResult = SQLAllocHandle(SQL_HANDLE_STMT, handleDBC, (SQLHANDLE*)&handleStatement);

SQLHSTMT    hstmt = handleStatement;
int         sqlResultOutput = 0;

SQLINTEGER  cbValue = 0;
nResult = SQLBindParameter(hstmt, 1, SQL_PARAM_OUTPUT, SQL_C_SLONG, SQL_INTEGER, 0, 0, &sqlResultOutput, 0, &cbValue);

//here is the problem
nResult = SQLExecDirect(hstmt, (SQLWCHAR*)L"{CALL my_TEST_SP(?)}", SQL_NTS);
if (!SQL_SUCCEEDED(nResult))
{
printErrStmt(hstmt);
}
nResult = SQLMoreResults(hstmt);
if (!(SQL_SUCCEEDED(nResult) || nResult == SQL_NO_DATA))
{
printErrStmt(hstmt);
}
std::wcout << L"Value of sqlResultOutput is: " << sqlResultOutput << std::endl;

return 0;
}

В ODBC Data Source Administrator я создал соответствующий DSN с именем test с помощью ODBC Driver 11 for SQL Server с версией 2014.120.2000.08. В конце записи DSN отображаются следующие свойства (в диалоговом окне, где вы можете нажать Test Data Source...):

Microsoft ODBC Driver for SQL Server Version 12.00.2000

Data Source Name: test
Data Source Description:
Server: .SQLSERVERINSTANCE
Use Integrated Security: Yes
Database: Test
Language: (Default)
Data Encryption: No
Trust Server Certificate: No
Multiple Active Result Sets(MARS): No
Mirror Server:
Translate Character Data: Yes
Log Long Running Queries: No
Log Driver Statistics: No
Use Regional Settings: No
Use ANSI Quoted Identifiers: Yes
Use ANSI Null, Paddings and Warnings: Yes

Если я запускаю этот пример приложения, я получаю ожидаемый результат:

Value of sqlResultOutput is: 3499

Поэтому я бы посоветовал вам попробовать последнюю версию драйвера ODBC для SQL Server. Я думаю, что это скачать здесь: http://www.microsoft.com/de-ch/download/details.aspx?id=36434

0

Другие решения

Других решений пока нет …

Источник

Помогаю со студенческими работами здесь

Ошибка с функцией
Дан текст. Найти сумму имеющихся в нем чисел.
Функция отдельно работает(в качестве отдельной…

Ошибка с функцией
Прочитал, понял, спасибо. А подскажите что тут за ошибка:
&lt;!DOCTYPE html&gt;
&lt;html&gt;
&lt;head&gt;
&lt;script…

Ошибка с функцией
Function GetSum(a, b : Integer) : Integer;
begin
if slabel1.caption=’1′ then
begin…

Ошибка с функцией
#include &lt;iostream&gt;

using namespace std;

int main()
{
int sum,n;
cin&gt;&gt;n;…

Искать еще темы с ответами

Или воспользуйтесь поиском по форуму:

Источник

Symptoms

Distributed queries that use the OpenQuery function to update, delete, or insert data in the following way

exec sp_dropserver 'linked1', 'droplogins'
exec sp_addlinkedserver 'linked1', 'SQL Server'
exec sp_setnetname  'linked1', '<servername>'
exec sp_addlinkedsrvlogin 'linked1', 'false', null, '<login name>', '<password>'
SET ANSI_NULLS ON
go
SET ANSI_WARNINGS ON
go
select * from openquery (linked1, 'update testlinked set ssn=ssn+1')
select * from openquery (linked1, 'insert into  testlinked  (ssn) values (1000)')
select * from openquery (linked1, 'delete from  testlinked  where ssn=1')

may generate the following error messages:

Server: Msg 7357, Level 16, State 2, Line 1 Could not process object ‘update testlinked set ssn=ssn’. The OLE DB provider ‘SQLOLEDB’ indicates that the object has no columns.

Server: Msg 7357, Level 16, State 2, Line 1 [Microsoft][ODBC SQL Server Driver][SQL Server]Could not process object ‘update testlinked set ssn=ssn’. The OLE DB provider ‘MSDASQL’ indicates that the object has no columns.

The actual text message of the error may vary depending on the OLE DB provider and the operation (UPDATE, INSERT, or DELETE) being performed, but the error number is always 7357.

If you are using Microsoft SQL Server 2005, you receive the following error message:

Server: Msg 7357, Level 16, State 2, Line 1 Cannot process the object «update testlinked set ssn=ssn». The OLE DB provider «SQLOLEDB» for linked server «ServerName» indicates that either the object has no columns or the current user does not have permissions on that object.

Cause

OpenQuery requires a result set to be returned, but UPDATE, DELETE, and INSERT statements that are used with OpenQuery do not return a result set.

Workaround

You can work around this problem in the following ways:

  1. Use four-part names (linked_server_name.catalog.schema.object_name) to do insert, update, or delete operations.

  2. As documented in SQL Server Books Online, reference the OpenQuery function as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. The following queries demonstrate proper usage with the SQL Server OLE DB provider:

    update openquery(linked1, 'select ssn from testlinked where ssn=2')
    set ssn=ssn + 1
    insert openquery(linked1, 'select ssn from testlinked where 1=0') values (1000)
    delete openquery(linked1, 'select ssn from testlinked where ssn>100')

    Note In the INSERT statement, a where 1=0 predicate is used to avoid retrieving data from the remote server, which can result in slower performance. Also, UPDATE and DELETE operations have special index requirements; see the «More Information» section for details.

More Information

Unique Index Requirement

The SQL Server OLE DB provider requires that a unique index exist on the underlying table for UPDATE or DELETE operations. If no unique index exists on a remote table, the following error occurs when an UPDATE or DELETE is attempted:

Server: Msg 7320, Level 16, State 2, Line 1 Could not execute query against OLE DB provider ‘SQLOLEDB’. The provider could not support a required row lookup interface. The provider indicates that conflicts occurred with other properties or requirements. [OLE/DB provider returned message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

This applies both to OpenQuery and four-part named UPDATE and DELETE operations. The problem is resolved by adding a unique index on the remote table.

Dynamic Execution with OpenQuery

It may sometimes be desirable to use a dynamic query to achieve the same effect using OpenQuery, as shown in the following example:

begin tran
SET QUOTED_IDENTIFIER OFF
SET XACT_ABORT ON
declare @cmd varchar(2500) 
declare @cmd1 varchar(2500) 
declare @var varchar(20) 
set @var = 'White' 
declare @var1 varchar(20) 
set @var1 = 'White1' 
declare @var2 varchar(20) 
set @var2 = 'Johnson1'
select @cmd = "Update openquery(linked1,'select au_lname, au_fname from pubs.dbo.authors
where au_lname = ''" + @var + "''' )
set au_lname = '" + @var1 + "',
au_fname = '" + @var2 + "'"
exec ( @cmd )
commit tran
select * from <servername>.pubs.dbo.authors

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Источник

I have been trying to write an R script to query Impala database. Here is the query to the database:

select columnA, max(columnB) from databaseA.tableA where columnC in (select distinct(columnC) from databaseB.tableB ) group by columnA order by columnA

When I run this query manually (read: outside the Rscript via impala-shell), I am able to get the table contents. However, when the same is tried via the R script, I get the following error:

[1] "HY000 140 [Cloudera][ImpalaODBC] (140) Unsupported query."       
[2] "[RODBC] ERROR: Could not SQLExecDirect 'select columnA, max(columnB) from databaseA.tableA where columnC in (select distinct(columnC) from databaseB.tableB ) group by columnA order by columnA'
closing unused RODBC handle 1

Why does the query fail when tried via R? and how do I fix this? Thanks in advance :)

Edit 1:

The connection script looks as below:

library("RODBC");
connection <- odbcConnect("Impala");
query <- "select columnA, max(columnB) from databaseA.tableA where columnC in (select distinct(columnC) from databaseB.tableB ) group by columnA order by columnA";
data <- sqlQuery(connection,query);

Источник
  • Remove From My Forums

  • Question

  • Cross post from Windows Server 2008 Forums (MSFT Suggestion).  Original Post:

    I am trying to set up Perfmon to log directly to a SQL 2008 Server
    (trying to avoid binary file + relog).  The SQL server is running
    locally on the same server I am trying to record perf counters from. 
    Note that I am NOT using SQL 2008’s data collection features, and I am
    trying to log non-sql related counters.

    I set up a SQL 2008
    Native Client System DSN with Windows Authentication, but when I try
    and start my data collecter set I get an error message  that «Call to
    SQLExecDirect failed with %1», and the in event viewer I get «Call to
    SQLExecDirect failed with .».  The set is running as System, which has
    rights to the database.  SQL does not log any errors or a failed logon
    attempt.

    The ODBC DNS ‘Test’ button says it connected successfully, and I am
    able to connect to the same database via Management Studio and ASP.Net.  I don’t think its a SQL problem, but its hard to say when the error message is blank.

    Any help fixing this, or ruling out SQL as the problem, would be appreciated.
    Joe

  • Proposed as answer by

    Thursday, September 23, 2010 11:09 PM

  • FYI, I had this same issue and switching to SQL Server driver instead of SQL Server Native solved the problem.

    • Proposed as answer by
      Finy
      Monday, October 11, 2010 7:38 AM

  • SQL Server Native Client has a very detailed tracing built-in. Please enable it using the approach described here http://msdn.microsoft.com/en-us/library/aa964124.aspx. If you won’t be able to tell what’s wrong please share the trace with us.

  • FYI, I had this same issue and switching to SQL Server driver instead of SQL Server Native solved the problem.

    Not knowing why, but using «SQL Server» driver instead of «SQL Server Native Client» driver is really a good solution!

  • I have switched to sql server ODBC driver 6.01.7600.16385, SQLSRV32.DLL and still have the issue.

  • [Resolved]

    error msg : Call to SQLExecDirect failed with %1

    I was having the issue with NT AUTHORITYSYSTEM permission.

    Please try using the below steps.

    step1: Go to the SQL server security tab -> NT AUTHORITYSYSTEM

    Step2 : Double click on NT AUTHORITYSYSTEM

    Step3: Click on server Roles and check sysadmin and click ok.

    After completing the above steps please try to start the Perf counter.

    —Pramodsingh

    • Источник

  • Ошибка при выполнении запроса get к ресурсу mainform html
  • Ошибка при выполнении запроса get к ресурсу index html
  • Ошибка при выполнении запроса get к ресурсу e1cib usersettings
  • Ошибка при выполнении запроса get к ресурсу e1cib modules defs
  • Ошибка при выполнении запроса get к ресурсу e1cib login