Getting an error in Visual Studios while extracting the tables from Vertica

JatinV

While creating the SSAS Multidimensional Cube, I made a Vertica data source connection in Microsoft Visual Studio through a Vertica OLEDB provider. The connection is working, but during the data source view step, while extracting the tables, I'm getting this error message:

"===================================

ERROR 4856: Syntax error at or near "[" at character 15 (Microsoft Visual Studio)

---

Program Location:

at System.Data.OleDb.OleDbCommand.ProcessResults(OleDbHResult hr)
at System.Data.OleDb.OleDbCommand.PrepareCommandText(Int32 expectedExecutionCount)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.DataWarehouse.Design.DataSourceConnection.FillDataSet(DataSet dataSet, String schemaName, String tableName, String tableType)
at Microsoft.AnalysisServices.Design.DSVUtilities.AddTableItemsInDataSet(DataSourceView dsv, DataSourceConnection conn, ArrayList tableList, String tableType, Hashtable usedFriendlyNames, ProgressBar progressBar, Int32 progressTotalCount, Int32& progressCounter)
at Microsoft.AnalysisServices.Design.DSVUtilities.ExportDataSet(DataSourceView dsv, DataSourceConnection conn, ArrayList tableList, ArrayList viewList, ArrayList sysTableList, ArrayList sysViewList, IServiceProvider serviceProvider, ProgressBar progressBar)
at Microsoft.AnalysisServices.Wizards.DSVWizardForm.OnFinish(CancelEventArgs e)"

SruthiA

Are you using C# ? could you please share your sample code?

JatinV

I'm creating an Analysis Service Multidimensional Project, and I was able to create a Vertica OLEDB Data Source connection. In the Data Source view, I am able to see the available tables and select the required tables, but after clicking Finish, I'm getting this error.

"ERROR 4856: Syntax error at or near "[" at character 15 (Microsoft Visual Studio)"

marcothesane

You probably had a SQL statement written for SQL Server not abiding by the ANSI standard.
Like:

SELECT [firstname],[lastname]  FROM [customer]

Testing it with the vanilla client to Vertica:

marco ~/1/Vertica/supp $ vsql -c "SELECT [firstname],[lastname]  FROM [customer]" 
ERROR 4856:  Syntax error at or near "[" at character 8

LINE 1: SELECT [firstname],[lastname] FROM [customer]
. ^

In ODBC / JDBC , and probably in OLEDB, too, you can add this setting:

ConvertSquareBracketIdentifiers = TRUE
```
(its default is false, and it does what the keyword says)

VivJain

Hi,

Pls set the property **ConvertSquareBracketIdentifiers = TRUE **
By default its set to FALSE. Set it to TRUE which will remove the mentioned error.

Regards/Vivek