Heterogeneous queries

[Aziz V.]

Hi All,
Can someone please tell me what does this means. and How to solw this problem.
This is my procedure that I created.

CREATE PROCEDURE stp_Test @TableName varchar(50), @Id int out AS
DECLARE @String varchar(500)
SELECT @String = 'SELECT * INTO #TempTable FROM ServerName.Database.dbo.' + @TableName
EXEC sp_sqlexec @String
SELECT @id = max(ID) FROM #TempTable
GO

WHEN I run this Procedure.

EXEC stp_Test 'TableName', @Id out

I get this out put.

-------------------------------------------------------
SELECT * INTO #TempTable FROM ServerName.Database.dbo.TableName
(1 rows affected)

Server: Msg 7405, Level 16, State 1, Procedure stp_MaxNoReturn, Line 2
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.

What is the solution to this problem.
Thanks in advance
Aziz

You may want to turn on ANSI NULLS for that database with sp_dboption.


------------
Aziz V. at 9/3/99 1:26:21 PM

Hi All,
Can someone please tell me what does this means. and How to solw this problem.
This is my procedure that I created.

CREATE PROCEDURE stp_Test @TableName varchar(50), @Id int out AS
DECLARE @String varchar(500)
SELECT @String = 'SELECT * INTO #TempTable FROM ServerName.Database.dbo.' + @TableName
EXEC sp_sqlexec @String
SELECT @id = max(ID) FROM #TempTable
GO

WHEN I run this Procedure.

EXEC stp_Test 'TableName', @Id out

I get this out put.

-------------------------------------------------------
SELECT * INTO #TempTable FROM ServerName.Database.dbo.TableName
(1 rows affected)

Server: Msg 7405, Level 16, State 1, Procedure stp_MaxNoReturn, Line 2
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.

What is the solution to this problem.
Thanks in advance
Aziz

[Aziz V.]

Thank you all for your time.
I have found the answer to it. And, the answer I found was really different then what ecpected. I had nothing to do with problem.
Here is the Answer.
CREATE your procedure as following.

CREATE PROCEDURE stp_Test @TableName varchar(50), @Id int out AS
DECLARE @String Nvarchar(500)
SELECT @String = N'SELECT * INTO #TempTable FROM ServerName.Database.dbo.' + @TableName
EXEC sp_EXECUTESQL @String
SELECT @id = max(ID) FROM #TempTable
GO

CREATING procedure as above works like a charm.
Thank you again.
Aziz



------------
at 9/3/99 2:23:23 PM

You may want to turn on ANSI NULLS for that database with sp_dboption.


------------
Aziz V. at 9/3/99 1:26:21 PM

Hi All,
Can someone please tell me what does this means. and How to solw this problem.
This is my procedure that I created.

CREATE PROCEDURE stp_Test @TableName varchar(50), @Id int out AS
DECLARE @String varchar(500)
SELECT @String = 'SELECT * INTO #TempTable FROM ServerName.Database.dbo.' + @TableName
EXEC sp_sqlexec @String
SELECT @id = max(ID) FROM #TempTable
GO

WHEN I run this Procedure.

EXEC stp_Test 'TableName', @Id out

I get this out put.

-------------------------------------------------------
SELECT * INTO #TempTable FROM ServerName.Database.dbo.TableName
(1 rows affected)

Server: Msg 7405, Level 16, State 1, Procedure stp_MaxNoReturn, Line 2
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.

What is the solution to this problem.
Thanks in advance
Aziz