pgnp cannot determine output columns for procedure

Using PGNP with MS SQL Server, Data Transformation Services (DTS), SQL Server Integration Services (SSIS), Linked Servers, Snapshot and Transactional Replication and more.

pgnp cannot determine output columns for procedure

Postby qqq123890 » Fri Feb 15, 2013 7:22 am

In postgres i've function with returned declared type.
When I try to call the function using linked server or in dtsx package:
Code: Select all
SELECT * FROM OPENQUERY(LINKEDSERVER_NAME,'SELECT * FROM function()')

I get the error:
pgnp cannot determine output columns for procedure schema_name.function_name

I try
Code: Select all
EXEC('pgnp_refreshmetadata('''',''schema_name.function_name'')') AT LINKEDSERVER_NAME

It did not help.

This function works fine in pgadmin

I've pgnp 1.3.0.2252
qqq123890
 
Posts: 2
Joined: Fri Feb 15, 2013 7:11 am

Re: pgnp cannot determine output columns for procedure

Postby Moderator » Mon Feb 18, 2013 8:47 am

This was a defect in the older versions of the provider. Please upgrade the product to the latest version.
User avatar
Moderator
Site Admin
 
Posts: 302
Joined: Wed Oct 29, 2008 11:27 pm

Re: pgnp cannot determine output columns for procedure

Postby qqq123890 » Mon Feb 18, 2013 8:58 am

This error is only on one database. I have test db in the same version postgres and provider and this dataflow works fine.
qqq123890
 
Posts: 2
Joined: Fri Feb 15, 2013 7:11 am

Re: pgnp cannot determine output columns for procedure

Postby Moderator » Tue Feb 19, 2013 9:10 am

There should be a reason. Most likely it could be metadata cache. If that is the case, then restarting the client OS, or executing following command from SSIS could help:
Code: Select all
EXEC('pgnp_refreshmetadata(''schema_name'',''function_name'')') AT LINKEDSERVER_NAME
User avatar
Moderator
Site Admin
 
Posts: 302
Joined: Wed Oct 29, 2008 11:27 pm


Return to SQL Server/DTS/SSIS/Linked servers/Replication

Who is online

Users browsing this forum: No registered users and 3 guests

cron