K2 blackpearl Dynamic SQL Stored Procedure Servi

A very nice feature of SQL 2005 is defining different schemas within your database, this way you can partition your data, and assign different security access depending on whether the schema is concerned with Reference Data or Business Data.

Unfortunately, in the current version of this service object, custom schemas are not taken into consideration. The stored procedures will be mapped by the Describe Schema method, but when attempting to execute you will receive a "Stored Procedure not found" error. This is because the describe schema stores [SP name] rather than [schema].[SP Name].

I think that could be done, the code probably has to be modified to take care of retrieving the schema value from the database.  I'll try to see if that can be done when I get the time to relook at this.

I am working on the project using SQL server 2005 with Windows 2003 with eventual migration to Windows 2008/SQL server 2008 platform within a few months after K2 Blackpearl releases its Win2k8 compatible version.

Will my smart objects built with this service broker work when I do my migration to the win2k8 environment seamlessly?

I think that the official dynamic SQL Server SmartObject service could be in the next major release (i.e. 0904).

If this is correct, then you should modify your SmartObject to use the new (supported) service rather than the unsupported one on the blackmarket.

However, I have been known to be wrong at times so I think the best thing is to wait for the official release notes for the next version.

Jonny,

We have downlaoded your project and we thank you for this valuable asset. Currenlty the solution you provided does not handle stored procedures to get data, in other words, it only executes SPs that transactional such as Insert , Update and Delete.

We have extyended your code to also handle stored procedures that uses select to get the data. Please let me know what you need for us to send you the code and get uploaded to the blackMarket.

Haytham Sammar

Para Solutions (K2 Partner)

haytham.sammar post a note on 2/5/2009 stating that they have implemented changes to the code to allow READ stored procs to be used in this service object.  Do you have this code, and if so can you please send it to me.  Thank you.

I am trying to use the latest dynamic service against SQL 2000.

It seems to work fine on stored procedures, except for the ones that accept Numeric parameters.

Here is an example of the Stor. Proc. that crashes the Dynamic SQL Stor. Proc. service:

 ************

Any help would be appreciated.

Thanks,

Alex

Been testing this out at a customer site and apparently there is a slight bug which prevents the service from handling stored procedures that have input parameters with the same name as the output column name.  This is due to the code that adds the parameters (i.e. it tried to add the same param key with both an input and output directions.  This has to be modified to add only one time with the both direction).  The other workaround is to make sure your stored procs use different names for the parameters and selected column names.

A easy way to tell is to first register service instance.  If the Service Instance Meta dialog has blank fields (Service GUID, System Name, Display Name, Description fields are blank), then it means that there was a failure in the DescribeSchema function.

You can also debug by:

1. Run the ServiceBroker tool (C:Program FilesK2 blackpearlServiceBrokerBrokerManagement.exe)


2. Opening the Visual Studio solution for the v1.03 file and attach the debugger to the BrokerManagement.exe.


3. Set a breakpoint at the DescribeSchema() exception handler in the ServiceBroker.cs file.


4. Register the database that you are having problems and the error should be visible at your breakpoint.

Currently the service broker only supports stored procedures that return a single table. If you run it against a SP that returns multiple tables, you will get an error saying "Service returned a property in the resultset that is not on the ServiceObject".

Dear,

We are receiving the following exception that is raised at the following line in the MSSqlSPDataConnector.cs file:

DataTable procedureParameters = connection.GetSchema("ProcedureParameters", restrictions);

 the error is as follows:

 System.Data.SqlClient.SqlException: Cannot resolve collation conflict for column 9 in SELECT statement.

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()

   at System.Data.SqlClient.SqlDataReader.get_MetaData()

   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)

   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)

   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)

   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)

   at System.Data.Common.DbCommand.ExecuteReader()

   at System.Data.ProviderBase.DbMetaDataFactory.ExecuteCommand(DataRow requestedCollectionRow, String[] restrictions, DbConnection connection)

the error is related to a collation deference between the DB from which stored procedures are imported and the BlackPearl DB server collation. What is the best way to fix or workaround this issue.

Your quick response is highly appreciated ...

Aboodii 

Dear,

We are receiving the following exception that is raised at the following line in the MSSqlSPDataConnector.cs file:

DataTable procedureParameters = connection.GetSchema("ProcedureParameters", restrictions);

 the error is as follows:

 System.Data.SqlClient.SqlException: Cannot resolve collation conflict for column 9 in SELECT statement.

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()

   at System.Data.SqlClient.SqlDataReader.get_MetaData()

   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)

   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)

   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)

   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)

   at System.Data.Common.DbCommand.ExecuteReader()

   at System.Data.ProviderBase.DbMetaDataFactory.ExecuteCommand(DataRow requestedCollectionRow, String[] restrictions, DbConnection connection)

the error is related to a collation deference between the DB from which stored procedures are imported and the BlackPearl DB server collation. What is the best way to fix or workaround this issue.

Your quick response is highly appreciated ...

Aboodii 

I've just installed the dynamic sql stored procedures service (latest from Blackmarket) on my Blackpearl 0807 server and created a new instance to a custom SQL server database.  This database has eleven custom sprocs in it all using the same schema and user rights.  When I created the new service instance pointing to this database only 5 of the 11 sprocs were registered/imported and available in the instance's generated XML and in the Context Browser in the VS designer.  The 5 sproc references that the service instance recognizes are all SELECT and DELETE sprocs.  No INSERT or UPDATE sprocs are recognized or imported.  Have anyone run across this behavior?  Do you have a fix?  Thanks.

Andy Fritsch

Hi Srikanth,

 You can still stick with the older version if you want to.  The only change contributed in v1.06 was the code to support tables in another schema other than dbo.

I'm getting pretty much the same error. Are you saying this is not am issue in earlier versions?

Thanks,

Eugen