Error message when saving 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 message when saving sp

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2006-05-19 : 09:59:23

I get the following error message when I run the sp below. How do I set these connections please ?

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.

Here's the sp

CREATE Procedure [dbo].[spRMU_GetUserPCCCore]

@strSurname nvarchar (100),
@strFirstname numeric
as
Select *, EmpFirstname + ' ' + EmpSurname as FName
from OPENDATASOURCE('SQLOLEDB','User ID=PCCCOREUser;Password=pccc0re').PCCCore.[dbo].vweEmpDetails
where EmpSurname Like '%'+ @strSurname +'%' and
EmpFirstname like '%'+@strFirstname+'%'

GO

Nitu
Yak Posting Veteran

81 Posts

Posted - 2006-05-19 : 10:03:59

set ansi_nulls on
set ansi_warnings on

Pinto
Aged Yak Warrior

590 Posts

Posted - 2006-05-19 : 10:05:22

How do I do that ? In the sp or in Query Analyser or in some settings somewhere ?

Nitu
Yak Posting Veteran

81 Posts

Posted - 2006-05-19 : 10:15:52

You can just add these lines to your sp (after the begin).

Pinto
Aged Yak Warrior

590 Posts

Posted - 2006-05-19 : 10:18:40

I now have this and I get same error

CREATE Procedure [dbo].[spRMU_GetUserPCCCore]

@strSurname nvarchar (100),
@strFirstname numeric
as

set ansi_nulls on
set ansi_warnings on

Select *, EmpFirstname + ' ' + EmpSurname as FName
from OPENDATASOURCE('SQLOLEDB','User ID=PCCCOREUser;Password=pccc0re').PCCCore.[dbo].vweEmpDetails
where EmpSurname Like '%'+ @strSurname +'%' and
EmpFirstname like '%'+@strFirstname+'%'

GO

Nitu
Yak Posting Veteran

81 Posts

Posted - 2006-05-19 : 10:35:46

I am sorry, i told you what i knew. Anyways here is a link that i found, hope this helps.

http://www.thescripts.com/forum/thread144266.html

Pinto
Aged Yak Warrior

590 Posts

Posted - 2006-05-19 : 10:38:13

Thank you for your help - you knew a lot more than I did ! Nobody else answered so it may not be any easy answer !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources