Problem Description:
Query statement is "SELECT *" [Remote Service Name]. [Database Name]. [DBO]. [Table Name],
The result is performed normally in the SQL query analyzer, but put this statement in the stored procedure.
The following error "The heterogeneous query requires the ANSI_NULLS and ANSI_WARNINGS options for the connection.
This will ensure consistent query semantics. Please enable these options and re-issue the query.
[SQLSTATE 42000] (Error 7405). Step failed. "
Even if the "SET ANSI_NULLS ON" statement is executed, it will not work.
Solution:
1. You can use OpenDataSource, there is a specific description in OnlineHelp
OpenDataSource
SELECT *
From OpenDataSource
'Sqloledb',
'Data Source = ServerName; User ID = SA; Password ='
.Awfi.dbo.temp
2. But the above method cannot solve the prompt information when creating a stored procedure in the enterprise manager.
SET ANSI_WARNINGS ON
SET ANSI_NULLS ON
Go
You can add as mentioned before the stored procedure CREATE statement.
