October 8, 2010 at 5:38 am
I need to get and integration solution to run on both SQL Server 2005 and Oracle instances of the same database. The Oracle DB exists and I created the SQL version, all works well except for the calling of a few stored procedures in SQL Server.
Two of the stored procedures have a couple of OUTPUT parameters defined. The problem is, in Oracle an OUTPUT parameter is exactly that, OUTPUT. In SQL Server, apparently, an OUTPUT parameter is actually INPUT/OUTPUT. The integration layer seems to inspect the procs' metadata to validate matching with the correct procedure and this is causing problems - it does not recognise the SQL procedures since the parameter signatures do not tie up with the stored proc declarations in the integration code. The procs are declared with OUT parameters in the code while the DB metadata probably says the parameters are INPUT/OUTPUT.
I've tested changing the Oracle procs and integration code to use INPUT/OUTPUT procs and then all works fine. Problem is the Oracle DB is that of another company and chances of changing it are zero.
So my question is simple: Is there no way in SQL Server to restrict a stored proc parameter to be strictly OUTPUT instead of the (hidden) default INPUT/OUTPUT?
Thank you
Dawie
October 8, 2010 at 6:42 am
dwmllr (10/8/2010)
I need to get and integration solution to run on both SQL Server 2005 and Oracle instances of the same database. The Oracle DB exists and I created the SQL version, all works well except for the calling of a few stored procedures in SQL Server.Two of the stored procedures have a couple of OUTPUT parameters defined. The problem is, in Oracle an OUTPUT parameter is exactly that, OUTPUT. In SQL Server, apparently, an OUTPUT parameter is actually INPUT/OUTPUT. The integration layer seems to inspect the procs' metadata to validate matching with the correct procedure and this is causing problems - it does not recognise the SQL procedures since the parameter signatures do not tie up with the stored proc declarations in the integration code. The procs are declared with OUT parameters in the code while the DB metadata probably says the parameters are INPUT/OUTPUT.
I've tested changing the Oracle procs and integration code to use INPUT/OUTPUT procs and then all works fine. Problem is the Oracle DB is that of another company and chances of changing it are zero.
So my question is simple: Is there no way in SQL Server to restrict a stored proc parameter to be strictly OUTPUT instead of the (hidden) default INPUT/OUTPUT?
Can you make changes to "integration layer"?
I remember working in a project like that. Solution was to add a "target" parameter to "integration layer" so to identify if target database was Oracle, SQL Server or Sybase.
Depending on "target", integration layer translated metadata information to an "unified" value.
An abstration would be...
On rdbms "A" metadata says greeting="hello"
On rdbms "B" metadata says greeting='hi"
We added our own metadata a.k.a. a translation table, like...
A.greeting="hello","hello"
B.greeting="hi","hello"
So, integration layer was able to translate the meaning of specific values depending on "target" rdbms.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 10, 2010 at 10:35 pm
Thanks for the response.
Your suggestion is an option. I was hoping to find a way to get around the SQL Server issue without cluttering the integration code. If not I will probably have to include a parameter to specify the DB type.
Cheers
October 11, 2010 at 7:53 am
dwmllr (10/10/2010)
Thanks for the response.Your suggestion is an option. I was hoping to find a way to get around the SQL Server issue without cluttering the integration code. If not I will probably have to include a parameter to specify the DB type.
You are most welcome.
I know, the search for the philosopher's stone of rdbms integration is full of setbacks. Please let us know if you find a way to do it other than telling the integration layer the identity of the target database.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply