Question to linked server, wrong result

First the question and correct result in Oracle
SQL> select distinct anv_uppg, profilkod from ekop2.bav001
where anv_uppg like 'san%' ;
ANV_UPPG PRO
-- --
sanderss SAN
Linked server question from query analyzer
SELECT distinct ANV_UPPG, REG_NAMN
FROM HACTAR..EKOP2.BAV001
where anv_uppg like 'san%'
sanderssGNO
sanderssSAN
I have never seen this before and it usually works fine.
Any idea what's wrong?
Are you sure you don't want
SELECT distinct ANV_UPPG, REG_NAMN
FROM HACTAR..EKOP2.BAV001
where pro like 'san%'
becuase the query you showed return 2 records and that is correct for
the field anv_uppg (sanderss)
http://sqlservercode.blogspot.com/
|||Problem solved: no longer an issue
:::::::::::::::::::::::::::::::::::::::::::::::::: ::::::::::::
"ln54" wrote:

> First the question and correct result in Oracle
> SQL> select distinct anv_uppg, profilkod from ekop2.bav001
> where anv_uppg like 'san%' ;
> ANV_UPPG PRO
> -- --
> sanderss SAN
>
> ----
> Linked server question from query analyzer
> SELECT distinct ANV_UPPG, REG_NAMN
> FROM HACTAR..EKOP2.BAV001
> where anv_uppg like 'san%'
> --
> sanderssGNO
> sanderssSAN
> ----
> I have never seen this before and it usually works fine.
> Any idea what's wrong?
>
>
>

Question to linked server, wrong result

First the question and correct result in Oracle
SQL> select distinct anv_uppg, profilkod from ekop2.bav001
where anv_uppg like 'san%' ;
ANV_UPPG PRO
-- --
sanderss SAN
----
Linked server question from query analyzer
SELECT distinct ANV_UPPG, REG_NAMN
FROM HACTAR..EKOP2.BAV001
where anv_uppg like 'san%'
--
sanderss GNO
sanderss SAN
----
I have never seen this before and it usually works fine.
Any idea what's wrong'Are you sure you don't want
SELECT distinct ANV_UPPG, REG_NAMN
FROM HACTAR..EKOP2.BAV001
where pro like 'san%'
becuase the query you showed return 2 records and that is correct for
the field anv_uppg (sanderss)
http://sqlservercode.blogspot.com/|||Problem solved: no longer an issue
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
"ln54" wrote:
> First the question and correct result in Oracle
> SQL> select distinct anv_uppg, profilkod from ekop2.bav001
> where anv_uppg like 'san%' ;
> ANV_UPPG PRO
> -- --
> sanderss SAN
>
> ----
> Linked server question from query analyzer
> SELECT distinct ANV_UPPG, REG_NAMN
> FROM HACTAR..EKOP2.BAV001
> where anv_uppg like 'san%'
> --
> sanderss GNO
> sanderss SAN
> ----
> I have never seen this before and it usually works fine.
> Any idea what's wrong'
>
>
>sql

Question to linked server, wrong result

First the question and correct result in Oracle
SQL> select distinct anv_uppg, profilkod from ekop2.bav001
where anv_uppg like 'san%' ;
ANV_UPPG PRO
-- --
sanderss SAN
----
Linked server question from query analyzer
SELECT distinct ANV_UPPG, REG_NAMN
FROM HACTAR..EKOP2.BAV001
where anv_uppg like 'san%'
sanderss GNO
sanderss SAN
----
I have never seen this before and it usually works fine.
Any idea what's wrong'Are you sure you don't want
SELECT distinct ANV_UPPG, REG_NAMN
FROM HACTAR..EKOP2.BAV001
where pro like 'san%'
becuase the query you showed return 2 records and that is correct for
the field anv_uppg (sanderss)
http://sqlservercode.blogspot.com/|||Problem solved: no longer an issue
::::::::::::::::::::::::::::::::::::::::
::::::::::::::::::::::
"ln54" wrote:

> First the question and correct result in Oracle
> SQL> select distinct anv_uppg, profilkod from ekop2.bav001
> where anv_uppg like 'san%' ;
> ANV_UPPG PRO
> -- --
> sanderss SAN
>
> ----
> Linked server question from query analyzer
> SELECT distinct ANV_UPPG, REG_NAMN
> FROM HACTAR..EKOP2.BAV001
> where anv_uppg like 'san%'
> --
> sanderss GNO
> sanderss SAN
> ----
> I have never seen this before and it usually works fine.
> Any idea what's wrong'
>
>
>

Question Regarding Execution of Stored Procedure on linked server using RPC

When I execute a stored procedure it always works fine using the syntax
EXEC server.db.catalog.stored_proc_name
However whenever I change the stored procedure to have parameters and
attempt to pass the parameters to the stored procedure I receive the
error:
Server: Msg 7212, Level 17, State 1, Line 1
Could not execute procedure 'stored_proc_name' on remote server
'server'.
Any ideas or suggestions about why this is occurring?
Thanks,
Edtry using the OPENQUERY Function

Question Regarding Execution of Stored Procedure on linked server using RPC

When I execute a stored procedure it always works fine using the syntax
EXEC server.db.catalog.stored_proc_name
However whenever I change the stored procedure to have parameters and
attempt to pass the parameters to the stored procedure I receive the
error:
Server: Msg 7212, Level 17, State 1, Line 1
Could not execute procedure 'stored_proc_name' on remote server
'server'.
Any ideas or suggestions about why this is occurring?
Thanks,
Edtry using the OPENQUERY Functionsql

Question Regarding Execution of Stored Procedure on linked server using RPC

When I execute a stored procedure it always works fine using the syntax
EXEC server.db.catalog.stored_proc_name
However whenever I change the stored procedure to have parameters and
attempt to pass the parameters to the stored procedure I receive the
error:
Server: Msg 7212, Level 17, State 1, Line 1
Could not execute procedure 'stored_proc_name' on remote server
'server'.
Any ideas or suggestions about why this is occurring?
Thanks,
Ed
try using the OPENQUERY Function

Question on Settings in Connection

I've programmed a user defined function (SQL2000), which in a specific query
references a linked server (another SQL instance, BTW contained in same
physical server). The sintaxis is ok, but i couldn't apply the definition
because of following error:
"Error 7405: Heteogeneous queries require the ANSI_NULLS and ANSI_WARNINGS
options to be set for the connection. This ensures consistente
query semantics. Enable these options and then reissue your query."
I set the corresponding settings in both servers, section Connections of
Server's properties, but to no avail.
Which is the trick here? How is resolved the 'connection' issue referred in
the error message?
Thanks in advanceMiguel Castanuela (MiguelCastanuela@.discussions.microsoft.com) writes:
> I've programmed a user defined function (SQL2000), which in a specific
> query references a linked server (another SQL instance, BTW contained in
> same physical server). The sintaxis is ok, but i couldn't apply the
> definition because of following error:
> "Error 7405: Heteogeneous queries require the ANSI_NULLS and ANSI_WARNINGS
> options to be set for the connection. This ensures consistente
> query semantics. Enable these options and then reissue your query."
> I set the corresponding settings in both servers, section Connections of
> Server's properties, but to no avail.
> Which is the trick here? How is resolved the 'connection' issue referred
> in the error message?
The trick is to stop using Enterprise Manager for editing functions and
stored procedures. Use Query Analyzer instead, this is a far better tool
for the task.
The particular problem here, is that Enterprise Manager creates functions
and procedures with ANSI_NULLS and QUOTED_IDENTIFIER OFF, and these
settings are saved with the procedure/function. Thus you need to recreate
the function with ANSI_NULLS ON. (In Query Analyzer all needed options
are ON by default.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Many thanks, it resolves the problem.
In ahead I will take this great tip in account.
"Erland Sommarskog" wrote:

> Miguel Castanuela (MiguelCastanuela@.discussions.microsoft.com) writes:
> The trick is to stop using Enterprise Manager for editing functions and
> stored procedures. Use Query Analyzer instead, this is a far better tool
> for the task.
> The particular problem here, is that Enterprise Manager creates functions
> and procedures with ANSI_NULLS and QUOTED_IDENTIFIER OFF, and these
> settings are saved with the procedure/function. Thus you need to recreate
> the function with ANSI_NULLS ON. (In Query Analyzer all needed options
> are ON by default.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>