Re: Configuration error?

Using your latest ODBC driver from Microsoft SQL Server 2017 SSMS to query a PostgreSQL database, it appears that your driver wants to limit a PostgreSQL “text” field to a length of only 8000.  Queries that don’t include a text field return expected results.  SQL Server allows up to 2,147,483,647 bytes in a TEXT field.

I’m not sure what to do so I appreciate your help in resolving this problem.  Following is information about the queries and errors I am encountering.

My query is:

Select

       *

From

       [kwokdb].[kwokdb].[public].issue

The SQL error message is:

Msg 7347, Level 16, State 1, Line 1

OLE DB provider 'MSDASQL' for linked server 'kwokdb' returned data that does not match expected data length for column '[kwokdb].[kwokdb].[public].[issue].issue_description'. The (maximum) expected data length is 8000, while the returned data length is 41372.

The “issue_description” field is defined in PostgreSQL as TEXT.

And, if I right click the linked database table, in SSMS, and select Script Table as > Select To > New Query Editor Window, I get the following error and below it is the error’s detailed information:

===================================

Enumerate columns failed for LinkedServer 'KWOKDB'.  (Microsoft.SqlServer.Smo)

------------------------------

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=14.0.17285.0+((SSMS_Rel_17_4).180821-0238)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Enumerate+columns+LinkedServer&LinkId=20476

------------------------------

Program Location:

   at Microsoft.SqlServer.Management.Smo.LinkedServer.EnumColumns(String tableName, String schemaName, String databaseName, String columnName)

   at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ScriptGenerator.ScriptTableOrViewAsSelect(SqlTextWriter sqlwriter, Server server, Urn urn, ScriptingOptions options)

   at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ScriptGenerator.ScriptSelect(SqlTextWriter sqlWriter, Server server, Urn[] urns, ScriptingOptions options)

   at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ScriptNodeActionContext.Script(SqlTextWriter writer)

   at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.SqlScriptMenu.OnScriptItemClick(Object sender, EventArgs e)

===================================

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Program Location:

   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand, Boolean retry)

   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(StringCollection sqlCommands)

   at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteWithResults(StringCollection query)

   at Microsoft.SqlServer.Management.Smo.LinkedServer.EnumColumns(String tableName, String schemaName, String databaseName, String columnName)

===================================

Cannot get the data of the row from the OLE DB provider "SQL Server" for linked server "(null)". Conversion failed because the data value overflowed the data type used by the provider. (.Net SqlClient Data Provider)

------------------------------

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=14.00.1000&EvtSrc=MSSQLServer&EvtID=7346&LinkId=20476

------------------------------

Server Name: TDCJ4AVOIGSRS

Error Number: 7346

Severity: 16

State: 2

Procedure: master.dbo.sp_columns_ex

Line Number: 177

------------------------------

Program Location:

   at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)

   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand, Boolean retry)

Thank you for your assistance.

Paul T. Rebman

Information Technology

Office of the Inspector General

Texas Department of Criminal Justice

4616 W. Howard Ln.  Suite 250, Austin, TX 78728

Office 512.671.2471 | Cell 512.924.2259 | Fax 512.671.2135

NOTICE:  This email including any and all attachments (collectively, the "Transmission"), may contain proprietary and/or confidential information protected from disclosure and intended only for the exclusive use of the named recipient(s).   If you are not a named recipient you are prohibited from interception, review, copying, disclosure, distribution and use of this Transmission  And, if you are not a named recipient, please contact the sender immediately and destroy all copies of the Transmission.