Originally posted by: rcmsf

Hi,

We have been using database interface designer for generating type trees from procedures in oracle/sybase and microsoft sql server.

However, recently we noticed the following behavioue when attempting to connect to the sybase or sql server:

1. The Stored Procedures or Tables are not visible and when trying to view procedures the error is:
The database does not have any procedures so type tree(s) cannot be generated.

The database has procedures and userid accessing these has create and execute privileges on the database.
The procedures in oracle database can be viewd and used for generating type trees.

2. The type tree for queries can still be generated with the query set as :
exec sp_proc_to_execute

3. The query in tables can be generated using insert table and adding the table name.

There has been no visible change in the environment however any advise will be very helpful.

Thanks.

Niraj
#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender

Originally posted by: rcmsf

To correct myself from below the sybase procs are visible and we have the issue only with SQL Server 2005. Most of our previous development has been on Sybase and Oracle and we are in the initial days of working on WTX with SQL Server and trying to find our way through it.
Thanks again.
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange

Originally posted by: rcmsf

The issue is resolved. We tested 2 different approach:

1. Using ODBC for connecting to the SQL Server 2005 database.
2. Using MS SQL Server adapater from WTX.

For both the approach tested with Access/User Tables with procedures only. The values Yes/No did not help.

Enabled ODBC tracing. This returned the error of Cursor having null values but was not very helpful as database had sufficient free space available for data as well as logs.

Further used SQL Server profiler for finding the actual queries going into the database:
1. When using ODBC the query hitting the database was:
exec sp_stored_procedures NULL,N'USERNAME',NULL
Further testing the query returned with the user 'USERNAME' returned no data in SQL Server. The user USERNAME had dbo privs and could query the schema tables, etc. On further analysis it appears that based on the option selected (for Access/User Tables with procedures only) it was searching for all procedures owned by the user. In my case the owner was not the user and probably the cause of nothing being returned.
2. When using MS SQL server adapter the query hitting the database was:
exec db_name..sp_procedures_rowset;2 NULL
Although the procedure was returning the dataset but frontend Database Interface Designer was not displaying the output. Further changing the options for Access/User tables with procedures only to No resulted in the DID returning all the procedures including system procedures.

Also, I have observed that after creating the new connection we do not immendiately see the procedures when trying to build type tress from procedures or tables. However, after saving and closing the mdq and reopening it the procedures are displayed. I am not sure of the quirkiness. Probably my system or application..I don't know. Please do let me know in case you have any inputs. Thanks.
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender