error in execution of SP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

error in execution of SP

Author

Topic

msrnivas
Starting Member

4 Posts

Posted - 2004-12-07 : 23:46:08

Hi
I am using Linkedservers in my SP.When i execute my SP giving below error.Pl advice how to set these options.
thanks

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.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-12-08 : 00:09:09

guess you need to follow what sql tells you

--------------------
keeping it simple...

msrnivas
Starting Member

4 Posts

Posted - 2004-12-08 : 02:33:32

Hi Jen
Sorry i am not getting what you are saying. my SP like below
When i execute my qry with local table its fine ,if i specify
linked server tablename(test.sales_DB.dbo.sales) its giving specified error.So where i have to set the values in my SP.

CREATE PROCEDURE x AS
DECLARE @TSQL varchar(8000), @VAR char(2)
SELECT @TSQL = 'SELECT * FROM test.sales_DB.dbo.sales'
EXEC (@TSQL)
GO

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-12-08 : 02:36:47

you need to issue inside your sp
create proc x
as
set ansi_nulls on
set ansi_warnings on
.
.
.
.
.
set ansi_warnings off
set ansi_nulls off

also, don't use dynamic sql, just execute your query as is

--edit

i think you just need this

create proc x
as

SELECT field1,field2,field3... FROM [test].[sales_DB].[dbo].sales

--don't use * to retrieve data, enumerate the fields.

--------------------
keeping it simple...

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-12-08 : 02:43:08

this is from BOL, how to query using linked servers.

quote:

OPENQUERY
Executes the specified pass-through query on the given linked server, which is an OLE DB data source. The OPENQUERY function can be referenced in the FROM clause of a query as though it is a table name. The OPENQUERY function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENQUERY returns only the first one.

Syntax
OPENQUERY ( linked_server , 'query' )

Arguments
linked_server

Is an identifier representing the name of the linked server.

'query'

Is the query string executed in the linked server.

Remarks
OPENQUERY does not accept variables for its arguments.

Examples
This example creates a linked server named OracleSvr against an Oracle database using the Microsoft OLE DB Provider for Oracle. Then this example uses a pass-through query against this linked server.

Note This example assumes that an Oracle database alias called ORCLDB has been created.

EXEC sp_addlinkedserver 'OracleSvr',
'Oracle 7.3',
'MSDAORA',
'ORCLDB'
GO
SELECT *
FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')
GO

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources