heterogenous query

[jackvull]

I am getting this error in my script.
I compiled the SQL server stored procedure with these settings so not sure what I have to set now:
Warning: mssql_query() [function.mssql-query]: message: 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. (severity 16) in C:\Inetpub\wwwroot\Finance\kpiImport.php on line 136

Warning: mssql_query() [function.mssql-query]: Query failed in C:\Inetpub\wwwroot\Finance\kpiImport.php on line 136
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.

[ashish21cool]

try to use this
-----------------
Set ansi_nulls on
Set ansi_warnings on
go
Create Procedure YourProcedure ....
----------------

[jackvull]

I did that and even created the procedure from query analyzer so seems fine.
But when I run the EXEC command from PHOP I still get this.
It's only this 1 stored procedure that connects to an Excel spreadsheet and none of the other stored procedures I call.
?

[jackvull]

So, as a workaround I set it in the code and now I get this:
Warning: mssql_query() [function.mssql-query]: message: Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server. (severity 16) in C:\Inetpub\wwwroot\Finance\kpiImport.php on line 143

Warning: mssql_query() [function.mssql-query]: Query failed in C:\Inetpub\wwwroot\Finance\kpiImport.php on line 143
Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.

[ashish21cool]

Go to this url
[support.microsoft.com...]

Let me know if you get the solution or not

[jackvull]

What I did was set it in code:
if (isset($_GET['upload'])) {
$execrtn = mssql_query("SET ANSI_NULLS ON") ;//or die(mssql_get_last_message());
$execrtn = mssql_query("SET ANSI_WARNINGS ON") ;//or die(mssql_get_last_message());
$execrtn = mssql_query("EXEC kpi_UploadData") or die(mssql_get_last_message());
if (file_exists("C:\KPIUpload\Data.xls")) {
unlink("C:/KPIUpload/Data.xls");
}
//header('Location: [10.4.1.47...]
}

This was working but now I seem to get this error instead on one of the set lines:
Warning: mssql_query() [function.mssql-query]: Query failed in C:\Inetpub\wwwroot\Finance\kpiImport.php on line 173
The statement has been terminated.