Hi,
we're getting the same error as well, are you using version 1320?

Managed to find a solution for this?

I too am having this issue, I'm hoping someone's resolved this. I created a SQL Smart Object in Central Admin by selecting a table and bringing along the related table. I've provided SQL authentication in the setup. I then have an InfoPath form that I've integrated with that SmartObject's List method. I use the form to set a field in the smartobject's InputProperties then query the connection. 

When I query I get the following error in InfoPath preview. I've captured the SOAP call using Fiddler and put the request and response below

The query cannot be run for the following DataObject: SQL_Vista_Voucher_Lookup_dbo_tblRedeemed_List

InfoPath cannot run the specified query.

The SOAP response indicates that an error occurred on the server:

Server was unable to process request. ---> Message: Invalid object name 'VISTAVM.dbo.tblRedeemed'.; ServiceName: Vista Voucher Lookup; ServiceGuid: 7e26513b-c195-4c9f-8581-46ff7e64dbdf; InnerExceptionMessage: ; 

SOAP Request

<?xml version="1.0" encoding="UTF-8" standalone="no"?><SOAP-ENV:Envelope xmlns:SOAPSDK1="http://www.w3.org/2001/XMLSchema" xmlns:SOAPSDK2="http://www.w3.org/2001/XMLSchema-instance" xmlns:SOAPSDK3="http://schemas.xmlsoap.org/soap/encoding/" xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"><SOAP-ENV:Body><tns:ExecuteSmartObjectMethod xmlns:tns="http://www.sourcecode.co.za/webservices/RuntimeServices">

<tns:ExecuteSmartObjectMethodInput>

<SmartObjectInput>

<InputProperties>

<lVoucherTypeID/>

<lVoucherNumber>58974324</lVoucherNumber>

<nDuplicateNo/>

<lRedeemedLocationID/>

<dRedeemedDate/>

<mIssuePrice/>

<mAlternatePrice/>

<lProcessGroupID/>

<lUpdateControl/>

<TransC_lgnNumber/>

<User_intUserNo/>

<Workstation_strCode/>

<VoucherOR_strCodeList/>

<User_intUserNoOR/>

<Redeemed_strScanOrManual/>

<Redeemed_strExportDetail/>

<Redeemed_dtmLocalTime/>

<Redeemed_dtmUpdated/>

<Redeemed_dtmRefunded/>

<Redeemed_intRefundLocationID/>

<Redeemed_strGiftCardStatus/>

<Redeemed_strSalesChannel/>

<Redeemed_dtmExtExportDetail/>

<CinType_strShortCode/>

</InputProperties>

<Locals>

<_soServer>Integrated=True;IsPrimaryLogin=True;Authenticate=True;EncryptedPassword=False;Host=UATMULLIGAN;Port=5555</_soServer>

<_soPort>5555</_soPort>

<_soGuid>4566637e-33ba-42f3-ba26-5fabd1dcc961</_soGuid>

<_soName>SQL_Vista_Voucher_Lookup_dbo_tblRedeemed</_soName>

<_soMethodName>List</_soMethodName>

<_soIsListMethod>True</_soIsListMethod>

<_soServerFieldDescription>SmartObject Server</_soServerFieldDescription>

</Locals>

</SmartObjectInput>

</tns:ExecuteSmartObjectMethodInput>

</tns:ExecuteSmartObjectMethod></SOAP-ENV:Body></SOAP-ENV:Envelope>

SOAP Response

<?xml version="1.0" encoding="utf-8"?><soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><soap:Body><soap:Fault><faultcode>soap:Server</faultcode><faultstring>Server was unable to process request. ---&gt; Message: Invalid object name 'VISTAVM.dbo.tblRedeemed'.; ServiceName: Vista Voucher Lookup; ServiceGuid: 7e26513b-c195-4c9f-8581-46ff7e64dbdf; InnerExceptionMessage: ; 

</faultstring><detail /></soap:Fault></soap:Body></soap:Envelope>

BUMP;

I've the same problem here. I can execute all the methods except the List in my production server. It works fine in my staging server and development server.

anyone have the answer?

I've seen something similar when the account that executes the call doesn't have sufficient permissions on the database it's querying. What resolved it in my case is to create a Linked Server between the databases/instances (sp_addlinkedserver).

The Linked Server will be created automatically if the account has sufficient permissions, but most of the time, production environments will be locked down and it wont be created when the instance is registered. Manual creation resolved the issue in my case.

I had to file a ticket to find out the answer here.  For me, and maybe you, the problem was due to the way I created the SQL service instance.

"If the database you are creating the SQL Service Instance from is on a different SQL Server Instance than the K2 databases, you will need to set 'on differerent sql server' true. And you need to set 'Use native sql execution' to false if you are using the 'ServiceAccount' authentication mode; 'use native sql execution' should be set to true only when using impersonation."

Hope that helps you.

@Aeberhar,

Your suggestion worked for me, i'm glad i didn't pull all of my hair, thanks for saving me lots of time.

Ronnie

@Aeberhar,

THANK YOU!  Years after this post, your information saved me a massive headache!