2

I am having problems extracting data from Sybase using Advantage 11 OLE DB Provider in SSIS (2017). I can connect to the database, see the list of tables, and, when selecting a table as a data source I can see the columns.
However, when I click Preview (or run the data flow task), I get this error:

The provider cannot derive parameter information from the command. Use SetParameterInfo. (Advantage OLE DB Provider)

I am using no parameters, my Data Access mode is set to Table or View (see attached screenshot)

enter image description here

In project properties, Run64bitRuntime is set to False.

What am I missing? Thanks!!

Hadi
  • 36,233
  • 13
  • 65
  • 124
Tamila
  • 177
  • 1
  • 3
  • 17
  • Have you seen this thread? [How to resolve SQL query parameters mapping issues while using Oracle OLE DB provider?](https://stackoverflow.com/questions/58540/how-to-resolve-sql-query-parameters-mapping-issues-while-using-oracle-ole-db-pro) – Jacob H Feb 05 '19 at 15:03
  • @JacobH, yes, thank you, but, like I said, I am not using any parameters in my query, it's a straight pull from a table.. – Tamila Feb 05 '19 at 16:21
  • Sounds like you've got it resolved then! – Jacob H Feb 05 '19 at 16:23
  • @JacobH, not at all, I am still getting the error! – Tamila Feb 05 '19 at 16:24
  • @Tamila try using `SQL Command` as source and write a `SELECT * FROM Billing` query. what is the result – Hadi Feb 05 '19 at 18:15
  • @Hadi, thank you, already tried it - same error.. – Tamila Feb 05 '19 at 18:22
  • @Tamila i am searching for this issue, and i think i found something interesting, i will provide an answer in a while. Even if i didn't worked with advantage oledb provider. – Hadi Feb 05 '19 at 18:25
  • @Tamila i am really curious on how to solve this problem, since there is no enough references that you can search for. anything new?? – Hadi Feb 05 '19 at 19:39
  • 1
    @Hadi, nope. Just replied below your answer (couldn't tag you there for some reason). – Tamila Feb 05 '19 at 19:47

2 Answers2

1

While searching on this issue, i found an old official reference that is related to advantage ole db provider release 1.0 (since 2000), they mentioned that:

Title:

"The Provider Cannot Derive Parameter Information From the Command. Use SetParameterInfo". Occurs When Using a Client Side Cursor with OLE DB

Problem Description:

"The provider cannot derive parameter information from the command. Use SetParameterInfo" occurs when setting the CursorLocation to use a client-side with the Advantage OLE DB Provider.

Solution:

This is a known issue in the 1.0 release of the Advantage OLE DB Provider, and is being looked into by the Advantage R&D team. The 1.1 release of the Advantage OLE DB Provider should have this problem fixed. Meanwhile, set the CursorLocation to use a server-side cursor as a work around. Server-side cursors work with the Advantage Database Server and the Advantage Local Server.

I searched on how to set CursorLocation to server side, maybe you have to set Advantage Server Type property in the connection string. Really i didn't worked with this type of connections but you can refer to the following articles to see some examples:

Also i found some official Microsoft articles concerning the CursorLocation property, but really i don't know where to change this property:

Update 1 - OLE DB Services

Try working around with the OLEDB Services values in the OLEDB connection manager:

enter image description here

Also try to change the OLEDB Services value manually in the connectionstring referring to the following article:

Update 2 - Other Providers

Try using other providers to connect to Sybase such as Sybase Adaptive Server Enterprise Data Provider or ODBC, there are many links that describe the whole process:

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • 1
    thank you for your help. I added Advantage Server Type property to the connection string, and it didn't help anything. I can't find where to set Cursor Location, unfortunately, and Google has been no help.. – Tamila Feb 05 '19 at 19:46
  • @Tamila i added an update, check it out. I think this is all what i can do. Good luck – Hadi Feb 05 '19 at 20:10
  • @Tamila you can import data from sybase using other providers check the latest update (2) – Hadi Feb 05 '19 at 20:21
  • if I don't succeed with OLEDB, I will try ODBC next. The crazy thing is I can access data from the same database using the same credentials through OLE DB provider in Excel. It's just SSIS that's giving me grief. – Tamila Feb 05 '19 at 20:28
  • @tamila why not using `Sybase Adaptive Server Enterprise Data Provider` since its the official provider? – Hadi Feb 05 '19 at 20:30
  • 1
    this is what we were told to use by our vendors.. I am still playing with cursors, but no luck so far. – Tamila Feb 05 '19 at 20:38
  • @Tamila Hope that the issue will be solved. Good luck and hope that this answer helps even if a bit. – Hadi Feb 05 '19 at 20:40
  • Nope, still the same issue, unfortunately. – Tamila Feb 06 '19 at 14:18
  • 1
    @Tamila i think you should change the provider since there is a lack of documentation for advatnage oledb. Check update 2 section – Hadi Feb 06 '19 at 14:47
0

So I finally solved this, but not with OLE DB. Instead, I used Advantage .NET Data Provider Release 11.1. Then in Visual Studio, I used ADO.NET connection manager, where I changed provider to Advantage Data Provider and that did the trick.

Tamila
  • 177
  • 1
  • 3
  • 17