Linked Server - Error

Hi all-

I created a another SQL Server as linked and I use them in some of my UDFs and SPs. Earlier these functions worked. But this morning, when I try to alter the definitions of these objects or query them, I get the following error message,
"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."

Iadded SET ANSI_WARNINGS ON , but I still get this error. Any ideas?

- CBhttp://www.dbforums.com/t492719.html

http://support.microsoft.com/default.aspx?scid=%2Fservicedesks%2Fbin%2Fkbsearch .asp%3FArticle%3D296769|||Thanks Snail. It worked.

- CB
Originally posted by snail
http://www.dbforums.com/t492719.html

http://support.microsoft.com/default.aspx?scid=%2Fservicedesks%2Fbin%2Fkbsearch .asp%3FArticle%3D296769

Linked Server - DB2 - iseries

I am trying to set up a linked server to our iseries through Enterprise
Manager and am getting the message;
Error 7304; Could not create new session on OLE DB Provider 'IBMDA400'
OLE DB error trace [OLD/DB provider 'IBMDA400' IDBCreatesession:: returned
0x80040e44].
I am using the following in the Linked Server Properties;
Provider name: IBM DB2 UDB for iseries OLE DB Provider
PRoduct name: Microsoft OLE DB Provider for DB2
Data Source: AS400.domainname.com (the domain
name of our as400 server)
Catalog: libname
(the Library name on the AS400)
I am using remote login using my AS400 userid in CAPS and password (using
Security context)
Server options has Data Access and User Remote Collation checked.
The error came from the DB2 OLEDB provider. You may contact IBM for
assitance.
- billyy
"Denny" <denny@.news.com> д?
news:eEVD1RKYFHA.3132@.TK2MSFTNGP09.phx.gbl...
>
> I am trying to set up a linked server to our iseries through Enterprise
> Manager and am getting the message;
> Error 7304; Could not create new session on OLE DB Provider 'IBMDA400'
> OLE DB error trace [OLD/DB provider 'IBMDA400' IDBCreatesession:: returned
> 0x80040e44].
> I am using the following in the Linked Server Properties;
> Provider name: IBM DB2 UDB for iseries OLE DB Provider
> PRoduct name: Microsoft OLE DB Provider for DB2
> Data Source: AS400.domainname.com (the
domain
> name of our as400 server)
> Catalog: libname
> (the Library name on the AS400)
>
> I am using remote login using my AS400 userid in CAPS and password (using
> Security context)
> Server options has Data Access and User Remote Collation checked.
>
>

Linked Server - DB2 - iseries

I am trying to set up a linked server to our iseries through Enterprise
Manager and am getting the message;
Error 7304; Could not create new session on OLE DB Provider 'IBMDA400'
OLE DB error trace [OLD/DB provider 'IBMDA400' IDBCreatesession:: return
ed
0x80040e44].
I am using the following in the Linked Server Properties;
Provider name: IBM DB2 UDB for iseries OLE DB Provider
PRoduct name: Microsoft OLE DB Provider for DB2
Data Source: AS400.domainname.com (the domain
name of our as400 server)
Catalog: libname
(the Library name on the AS400)
I am using remote login using my AS400 userid in CAPS and password (using
Security context)
Server options has Data Access and User Remote Collation checked.The error came from the DB2 OLEDB provider. You may contact IBM for
assitance.
- billyy
"Denny" <denny@.news.com> д?
news:eEVD1RKYFHA.3132@.TK2MSFTNGP09.phx.gbl...
>
> I am trying to set up a linked server to our iseries through Enterprise
> Manager and am getting the message;
> Error 7304; Could not create new session on OLE DB Provider 'IBMDA400'
> OLE DB error trace [OLD/DB provider 'IBMDA400' IDBCreatesession:: retu
rned
> 0x80040e44].
> I am using the following in the Linked Server Properties;
> Provider name: IBM DB2 UDB for iseries OLE DB Provider
> PRoduct name: Microsoft OLE DB Provider for DB2
> Data Source: AS400.domainname.com (the
domain
> name of our as400 server)
> Catalog: libname
> (the Library name on the AS400)
>
> I am using remote login using my AS400 userid in CAPS and password (using
> Security context)
> Server options has Data Access and User Remote Collation checked.
>
>

Linked Server - Change of Password

Hi,
If the password changes on a remote source, is there a way of changing the password for the linked server to the remote source via a stored procedure (outside of the gui)? Or does the linked server login need to be dropped and recreated? How does EM do
it in the gui?
Thanks
Jerry
You can do it with the sp_addlinkedsrvlogin store procedure like so:
exec sp_addlinkedsrvlogin@.rmtsrvname = 'yourrmtserver',
@.locallogin = 'your local login',
@.rmtuser = 'remote login',
@.rmtpassword = 'new password'
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Jerry" <anonymous@.discussions.microsoft.com> wrote in message
news:15134043-AF75-4440-96DD-A3A39747C44D@.microsoft.com...
> Hi,
> If the password changes on a remote source, is there a way of changing the
password for the linked server to the remote source via a stored procedure
(outside of the gui)? Or does the linked server login need to be dropped
and recreated? How does EM do it in the gui?
> Thanks
> Jerry

Linked Server - Change of Password

Hi,
If the password changes on a remote source, is there a way of changing the p
assword for the linked server to the remote source via a stored procedure (o
utside of the gui)? Or does the linked server login need to be dropped and
recreated? How does EM do
it in the gui?
Thanks
JerryYou can do it with the sp_addlinkedsrvlogin store procedure like so:
exec sp_addlinkedsrvlogin@.rmtsrvname = 'yourrmtserver',
@.locallogin = 'your local login',
@.rmtuser = 'remote login',
@.rmtpassword = 'new password'
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Jerry" <anonymous@.discussions.microsoft.com> wrote in message
news:15134043-AF75-4440-96DD-A3A39747C44D@.microsoft.com...
> Hi,
> If the password changes on a remote source, is there a way of changing the
password for the linked server to the remote source via a stored procedure
(outside of the gui)? Or does the linked server login need to be dropped
and recreated? How does EM do it in the gui?
> Thanks
> Jerry

Linked server - Calling AS400 SP

Hi,
I have created a linked server to AS400 DB2 database on my sqlserver
2000 server.
I am using IBM iSeries Client Access drivers on the server.
The connection has been made properly and to confirm that i ran a query
to select a table from AS400 using openquery syntax.
I am trying to call a stored procedure in AS400 that either returns 0 or
1. The stored procedre executes from vb through a dsn without any problem.
However calling the stored proc using the linked server is a nightmare.
If I use the openquery syntax which is ...
select * from openquery(as400serv1,'CALL QGPL.CCVALX ( ''1'' ,
''123343453454'' , ''0205'' , ''CC'') ' )
I get error ...
Server: Msg 7357, Level 16, State 2, Line 2
Could not process object 'CALL QGPL.CCVALX ( '1' , '123343453454' , '0205' ,
'CC') '. The OLE DB provider 'IBMDA400' indicates that the object has no
columns.
If I use the execute method as in ...
exec as400serv1.wima400.QGPL.CCVALX '1','123343453454','0205','CC'
I get err..
Server: Msg 7212, Level 17, State 1, Line 1
Could not execute procedure 'CCVALX' on remote server 'as400serv1'.
[OLE/DB provider returned message: SQL0104: Token { was not valid. Valid
tokens: ( END SET CALL DROP FREE HOLD LOCK OPEN WITH ALTER BEGIN CLOSE.
Cause . . . . . : A syntax error was detected at token {. Token { is not
a valid token. A partial list of valid tokens is ( END SET CALL DROP FREE
HOLD LOCK OPEN WITH ALTER BEGIN CLOSE. This list assumes that the statement
is correct up to the token. The error may be earlier in the statement, but
the syntax of the statement appears to be valid up to this point. Recovery
.. . . : Do one or more of the following and try the request again: --
Verify the SQL statement in the area of the token {. Correct the statement.
The error could be a missing comma or quotation mark, it could be a
misspelled word, or it could be related to the order of clauses. -- If the
error token is <END-OF-STATEMENT>, correct the SQL statement because it does
not end with a valid clause.]
Please, can anyone help me understanding what is going wrong... it very
crucial i get this working from the linked server.
Thanks in advance
Remy De Almeida
> Hi,
> I have created a linked server to AS400 DB2 database on my sqlserver
> 2000 server.
> I am using IBM iSeries Client Access drivers on the server.
> The connection has been made properly and to confirm that i ran a
query
> to select a table from AS400 using openquery syntax.
> I am trying to call a stored procedure in AS400 that either returns 0
or
> 1. The stored procedre executes from vb through a dsn without any problem.
> However calling the stored proc using the linked server is a nightmare.
> If I use the openquery syntax which is ...
> select * from openquery(as400serv1,'CALL QGPL.CCVALX ( ''1'' ,
> ''123343453454'' , ''0205'' , ''CC'') ' )
> I get error ...
> Server: Msg 7357, Level 16, State 2, Line 2
> Could not process object 'CALL QGPL.CCVALX ( '1' , '123343453454' ,
'0205' ,
> 'CC') '. The OLE DB provider 'IBMDA400' indicates that the object has no
> columns.
>
> If I use the execute method as in ...
> exec as400serv1.wima400.QGPL.CCVALX '1','123343453454','0205','CC'
> I get err..
> Server: Msg 7212, Level 17, State 1, Line 1
> Could not execute procedure 'CCVALX' on remote server 'as400serv1'.
> [OLE/DB provider returned message: SQL0104: Token { was not valid. Valid
> tokens: ( END SET CALL DROP FREE HOLD LOCK OPEN WITH ALTER BEGIN CLOSE.
> Cause . . . . . : A syntax error was detected at token {. Token { is
not
> a valid token. A partial list of valid tokens is ( END SET CALL DROP FREE
> HOLD LOCK OPEN WITH ALTER BEGIN CLOSE. This list assumes that the
statement
> is correct up to the token. The error may be earlier in the statement,
but
> the syntax of the statement appears to be valid up to this point. Recovery
> . . . : Do one or more of the following and try the request again: --
> Verify the SQL statement in the area of the token {. Correct the
statement.
> The error could be a missing comma or quotation mark, it could be a
> misspelled word, or it could be related to the order of clauses. -- If the
> error token is <END-OF-STATEMENT>, correct the SQL statement because it
does
> not end with a valid clause.]
> Please, can anyone help me understanding what is going wrong... it very
> crucial i get this working from the linked server.
> Thanks in advance
> Remy De Almeida
You need to determine if the third-party OLE-DB provider supports the
calling of stored procedures from the remote server. Speak to your OLE-DB
provider vendor about this.
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

Linked server - Calling AS400 SP

Hi,
I have created a linked server to AS400 DB2 database on my sqlserver
2000 server.
I am using IBM iSeries Client Access drivers on the server.
The connection has been made properly and to confirm that i ran a query
to select a table from AS400 using openquery syntax.
I am trying to call a stored procedure in AS400 that either returns 0 or
1. The stored procedre executes from vb through a dsn without any problem.
However calling the stored proc using the linked server is a nightmare.
If I use the openquery syntax which is ...
select * from openquery(as400serv1,'CALL QGPL.CCVALX ( ''1'' ,
''123343453454'' , ''0205'' , ''CC'') ' )
I get error ...
Server: Msg 7357, Level 16, State 2, Line 2
Could not process object 'CALL QGPL.CCVALX ( '1' , '123343453454' , '0205' ,
'CC') '. The OLE DB provider 'IBMDA400' indicates that the object has no
columns.
If I use the execute method as in ...
exec as400serv1.wima400.QGPL.CCVALX '1','123343453454','0205','CC'
I get err..
Server: Msg 7212, Level 17, State 1, Line 1
Could not execute procedure 'CCVALX' on remote server 'as400serv1'.
[OLE/DB provider returned message: SQL0104: Token { was not valid. Valid
tokens: ( END SET CALL DROP FREE HOLD LOCK OPEN WITH ALTER BEGIN CLOSE.
Cause . . . . . : A syntax error was detected at token {. Token { is not
a valid token. A partial list of valid tokens is ( END SET CALL DROP FREE
HOLD LOCK OPEN WITH ALTER BEGIN CLOSE. This list assumes that the statement
is correct up to the token. The error may be earlier in the statement, but
the syntax of the statement appears to be valid up to this point. Recovery
. . . : Do one or more of the following and try the request again: --
Verify the SQL statement in the area of the token {. Correct the statement.
The error could be a missing comma or quotation mark, it could be a
misspelled word, or it could be related to the order of clauses. -- If the
error token is <END-OF-STATEMENT>, correct the SQL statement because it does
not end with a valid clause.]
Please, can anyone help me understanding what is going wrong... it very
crucial i get this working from the linked server.
Thanks in advance
Remy De Almeida> Hi,
> I have created a linked server to AS400 DB2 database on my sqlserver
> 2000 server.
> I am using IBM iSeries Client Access drivers on the server.
> The connection has been made properly and to confirm that i ran a
query
> to select a table from AS400 using openquery syntax.
> I am trying to call a stored procedure in AS400 that either returns 0
or
> 1. The stored procedre executes from vb through a dsn without any problem.
> However calling the stored proc using the linked server is a nightmare.
> If I use the openquery syntax which is ...
> select * from openquery(as400serv1,'CALL QGPL.CCVALX ( ''1'' ,
> ''123343453454'' , ''0205'' , ''CC'') ' )
> I get error ...
> Server: Msg 7357, Level 16, State 2, Line 2
> Could not process object 'CALL QGPL.CCVALX ( '1' , '123343453454' ,
'0205' ,
> 'CC') '. The OLE DB provider 'IBMDA400' indicates that the object has no
> columns.
>
> If I use the execute method as in ...
> exec as400serv1.wima400.QGPL.CCVALX '1','123343453454','0205','CC'
> I get err..
> Server: Msg 7212, Level 17, State 1, Line 1
> Could not execute procedure 'CCVALX' on remote server 'as400serv1'.
> [OLE/DB provider returned message: SQL0104: Token { was not valid. Valid
> tokens: ( END SET CALL DROP FREE HOLD LOCK OPEN WITH ALTER BEGIN CLOSE.
> Cause . . . . . : A syntax error was detected at token {. Token { is
not
> a valid token. A partial list of valid tokens is ( END SET CALL DROP FREE
> HOLD LOCK OPEN WITH ALTER BEGIN CLOSE. This list assumes that the
statement
> is correct up to the token. The error may be earlier in the statement,
but
> the syntax of the statement appears to be valid up to this point. Recovery
> . . . : Do one or more of the following and try the request again: --
> Verify the SQL statement in the area of the token {. Correct the
statement.
> The error could be a missing comma or quotation mark, it could be a
> misspelled word, or it could be related to the order of clauses. -- If the
> error token is <END-OF-STATEMENT>, correct the SQL statement because it
does
> not end with a valid clause.]
> Please, can anyone help me understanding what is going wrong... it very
> crucial i get this working from the linked server.
> Thanks in advance
> Remy De Almeida
--
You need to determine if the third-party OLE-DB provider supports the
calling of stored procedures from the remote server. Speak to your OLE-DB
provider vendor about this.
Hope this helps,
--
Eric Cárdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.