I have a SP (sp_test_procedure) created from a SQL Server Project in my SQL Server 2005 database, inside this SP I have a call to another SP which inside calls to a SP in another Database Server (SQL 2000) through a cross-linked connection.
When I execute it from the Management Studio it works just fine. But when I call from my PHP app It gives me an error:
A .NET Framework error occurred during execution of user defined routine or aggregate 'sp_test_procedure': System.Data.SqlClient.SqlException: Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query. System.Data.SqlClient.SqlException: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.DispatchMessages(Boolean ignoreNonFatalMessages) at Microsoft.SqlServer.Server.SmiEventSink_Default.DispatchMessages(Boolean ignoreNonFatalMessages) at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.InternalEx in /home/xxxxxxxxxxx/xxx/xxxxxxx.php on line XX Obviously, I put the SET ANSI_NULLS and ANSI_WARNINGS flags inside and outside (and both at the same time xD, just in case) the creation of the all SPs mentioned...
I've tested it with all the parameters (integers, decimals and strings) in there original types and string only type, I had no luck.
So, I need to know how to resolve this, I need that SP to work on my PHP app. So any thoughts are welcome.