Member Avatar for culebrin

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.

Thanks..

Omar

  • 2 Contributors
  • 2 Replies
  • 321 Views
  • 5 Months Discussion Span
  • Latest Post Latest Post by culebrin

Dani AI

Generated

Short answer: the connection that actually runs the heterogeneous query must have ANSI_NULLS and ANSI_WARNINGS turned ON. pointed this out and confirmed it fixed the problem — the SETs have to be applied on the session your PHP code uses (or on whatever connection the CLR stored proc opens), not only at object-creation time.

Apply the two SETs immediately after connecting from PHP, then call the procedure. Example with the old mssql extension:

$link = mssql_connect($server, $user, $pass);
mssql_select_db($db, $link);
mssql_query('SET ANSI_NULLS ON', $link);
mssql_query('SET ANSI_WARNINGS ON', $link);
mssql_query("EXEC sp_test_procedure @p1, @p2", $link);

Or, if your driver accepts batches, run them in one call:

mssql_query('SET ANSI_NULLS ON; SET ANSI_WARNINGS ON; EXEC sp_test_procedure @p1, @p2', $link);

Troubleshooting notes: setting the flags when the SP was created won’t help the caller’s session; you must set them on the runtime connection. If you’re using connection pooling or persistent connections, make those SETs part of your connection initialization so every pooled session has the correct options. If the stored procedure is a CLR routine that opens its own SqlConnection, that internal connection must also enable those options before issuing the heterogeneous query. Finally, test carefully — ANSI_WARNINGS ON can change behavior (some conditions that were warnings may become errors), so confirm the proc still behaves as expected after enabling these options.

Recommended Answers

All 2 Replies

Member Avatar for R0bb0b

This should help

Member Avatar for culebrin

This should help

Yes, that is what I needed... Thanks!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.