I have a procedure created in Amazon redshift, and I can call it from SQL Workbench with no issue. Now I need to call that procedure from SQL Server. Even I can connect with Redshift with ODBC to do the data query, I am not able to call the procedure through ODBC connection. Is there a way to call the procedure programmatically?
Driver Support
We recommend that you upgrade your Java Database Connectivity (JDBC) and Open Database Connectivity (ODBC) drivers to the latest version that has support for Amazon Redshift stored procedures.
You might be able to use your existing driver if your client tool uses driver API operations that pass through the CALL statement to the server. Output parameters, if any, are returned as a result set of one row.
The latest versions of Amazon Redshift JDBC and ODBC drivers have metadata support for stored procedure discovery. They also have CallableStatement support for custom Java applications. For more information on drivers, see Connecting to an Amazon Redshift Cluster Using SQL Client Tools in the Amazon Redshift Cluster Management Guide.
This is what Amazon said. Confusion part is, I just downloaded the latest version of ODBC driver, but it seems still not working. When I call it from SQL Server, I got the following error:
The OLE DB provider "MSDASQL" for linked server "xxxx" indicates that either the object has no columns or the current user does not have permissions on that object.
