Problem with linking servers

Hi.

I have two sql servers and have ran exec sp_addlinkedserver 'ACSPSM', N'SQL
Server' to link one to the other and also vise versa.

Each server has two users permissioned.

My problem is when ever I try to do something that does a remote write I get
the follow error message
Microsoft OLE DB Provider for SQL Server error '80040e14'

[OLE/DB provider returned message: Cannot start more transactions on this
session.]

Also

when I try and manually run a stored procedure, I get:

Remote tables are not updatable. Updatable keyset-driven cursors on remote
tables require a transaction with the REPEATABLE_READ or SERIALIZABLE
isolation level spanning the cursor.

(1 row(s) affected)

(1 row(s) affected)

(50 row(s) affected)

Server: Msg 7395, Level 16, State 2, Procedure ams_Move_Stock_To_PSM, Line
65
Unable to start a nested transaction for OLE DB provider 'SQLOLEDB'. A
nested transaction was required because the XACT_ABORT option was set to
OFF.
[OLE/DB provider returned message: Cannot start more transactions on this
session.]

Can anyone suggest how I resolve this.

FYI

Database Creation Script: (both database are the same scripts but db names
are changed)

CREATE DATABASE [msmprim] ON (NAME = N'msmprim_Data', FILENAME =
N'D:\ACS_DB\data\msmprim_Data.MDF' , SIZE = 2000, FILEGROWTH = 10%) LOG ON
(NAME = N'msmprim_Log', FILENAME = N'D:\ACS_DB\logs\msmprim_Log.LDF' , SIZE
= 2000, FILEGROWTH = 10%)

COLLATE Latin1_General_CI_AS

GO

exec sp_dboption N'msmprim', N'autoclose', N'false'

GO

exec sp_dboption N'msmprim', N'bulkcopy', N'false'

GO

exec sp_dboption N'msmprim', N'trunc. log', N'false'

GO

exec sp_dboption N'msmprim', N'torn page detection', N'true'

GO

exec sp_dboption N'msmprim', N'read only', N'false'

GO

exec sp_dboption N'msmprim', N'dbo use', N'false'

GO

exec sp_dboption N'msmprim', N'single', N'false'

GO

exec sp_dboption N'msmprim', N'autoshrink', N'false'

GO

exec sp_dboption N'msmprim', N'ANSI null default', N'false'

GO

exec sp_dboption N'msmprim', N'recursive triggers', N'false'

GO

exec sp_dboption N'msmprim', N'ANSI nulls', N'false'

GO

exec sp_dboption N'msmprim', N'concat null yields null', N'false'

GO

exec sp_dboption N'msmprim', N'cursor close on commit', N'false'

GO

exec sp_dboption N'msmprim', N'default to local cursor', N'false'

GO

exec sp_dboption N'msmprim', N'quoted identifier', N'false'

GO

exec sp_dboption N'msmprim', N'ANSI warnings', N'false'

GO

exec sp_dboption N'msmprim', N'auto create statistics', N'true'

GO

exec sp_dboption N'msmprim', N'auto update statistics', N'true'

GO

User Creation Script:

/****** Object: Login MSM Script Date: 31/10/2002 10:41:26 ******/

use [msmprim]

GO

declare @.sqlLoginName nvarchar(32) select @.sqlLoginName = N'msm'

declare @.UserPassword nvarchar(32) select @.UserPassword = N'wibble'

declare @.logindb nvarchar(132) select @.logindb = N'msmprim'

declare @.loginlang nvarchar(132) select @.loginlang = N'British English'

if not exists (select * from master.dbo.syslogins where loginname =
@.sqlLoginName)

BEGIN

if @.logindb is null or not exists (select * from master.dbo.sysdatabases
where name = @.logindb)

select @.logindb = N'master'

if @.loginlang is null or (not exists (select * from master.dbo.syslanguages
where name = @.loginlang) and @.loginlang <> N'British English')

select @.loginlang = @.@.language

exec sp_addlogin @.sqlLoginName, @.UserPassword, @.logindb, @.loginlang

END

/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/

if not exists (select * from dbo.sysusers where name = @.sqlLoginName and uid
< 16382)

EXEC sp_grantdbaccess @.sqlLoginName, @.sqlLoginName

/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/

exec sp_addrolemember N'db_datareader', @.sqlLoginName

/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/

exec sp_addrolemember N'db_datawriter', @.sqlLoginName

/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/

exec sp_addrolemember N'db_owner', @.sqlLoginName

GO

use [msmprim]

GO

declare @.sqlLoginName nvarchar(32) select @.sqlLoginName = N'psm'

declare @.UserPassword nvarchar(32) select @.UserPassword = N'wibble'

declare @.logindb nvarchar(132) select @.logindb = N'msmprim'

declare @.loginlang nvarchar(132) select @.loginlang = N'British English'

if not exists (select * from master.dbo.syslogins where loginname =
@.sqlLoginName)

BEGIN

if @.logindb is null or not exists (select * from master.dbo.sysdatabases
where name = @.logindb)

select @.logindb = N'master'

if @.loginlang is null or (not exists (select * from master.dbo.syslanguages
where name = @.loginlang) and @.loginlang <> N'British English')

select @.loginlang = @.@.language

exec sp_addlogin @.sqlLoginName, @.UserPassword, @.logindb, @.loginlang

END

/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/

if not exists (select * from dbo.sysusers where name = @.sqlLoginName and uid
< 16382)

EXEC sp_grantdbaccess @.sqlLoginName, @.sqlLoginName

/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/

exec sp_addrolemember N'db_datareader', @.sqlLoginName

/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/

exec sp_addrolemember N'db_datawriter', @.sqlLoginName

/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/

exec sp_addrolemember N'db_owner', @.sqlLoginName

GOI have got a little further, by adding 'SET XACT_ABORT ON' I have mangaged
to get the remote write to work.

Can someone tell me what the implication of setting 'SET XACT_ABORT ON' is
and what effect will it have on my SPs. Will they still roll back as
before?

Sample SP

procedure [msm].ams_Insert_Audit
@.strResult varchar(8) = 'Failure' output,
@.strErrorDesc varchar(512) = 'SP Not Executed' output,
@.strSCRIPT varchar(128),
@.strLOGINID varchar(32),
@.strVFEID varchar(16),
@.strBILLORGID varchar(16),
@.strRETAILERID varchar(16),
@.strLOCATIONID varchar(16),
@.strPOSTID varchar(16),
@.strXACTIONID varchar(16),
@.strCODE varchar(64),
@.strDATA varchar(1024)
as
declare @.strStep varchar(32)
declare @.trancount int

set @.trancount = @.@.trancount
set @.strStep = 'Start of Stored Proc'

if (@.trancount = 0)
begin tran ams_Insert_Audit
else
save tran ams_Insert_Audit

SET XACT_ABORT ON

set @.strStep = 'Writing MSM Audit'

insert into
HAN.msmprim.msm.AMSAUDIT (TIMESTAMP, SCRIPT, LOGINID, VFEID, BILLORGID,
RETAILERID, LOCATIONID, POSTID, XACTIONID, CODE, DATA)
values
(GetDate(), @.strSCRIPT, @.strLOGINID, @.strVFEID, @.strBILLORGID,
@.strRETAILERID, @.strLOCATIONID, @.strPOSTID, @.strXACTIONID, @.strCODE,
@.strDATA)

set @.strStep = 'Writing PSM Audit'

insert into
ACSPSM.psmprim.psm.AMSAUDIT (TIMESTAMP, SCRIPT, LOGINID, VFEID, BILLORGID,
RETAILERID, LOCATIONID, POSTID, XACTIONID, CODE, DATA)
values
(GetDate(), @.strSCRIPT, @.strLOGINID, @.strVFEID, @.strBILLORGID,
@.strRETAILERID, @.strLOCATIONID, @.strPOSTID, @.strXACTIONID, @.strCODE,
@.strDATA)

if (@.@.error <> 0)
begin
rollback tran ams_Insert_Audit
set @.strResult = 'Failure'
set @.strErrorDesc = 'Fail @. Step :' + @.strStep + ' Error : Error Occured'
return -1969
end
else
begin
set @.strResult = 'Success'
set @.strErrorDesc = ''
end
-- commit tran if we started it

if (@.trancount = 0)
commit tran ams_Insert_Audit

return 0

Regards

Steve

"Steve Thorpe" <stephenthorpe@.nospam.hotmail.com> wrote in message
news:bkeqcl$h14$1@.titan.btinternet.com...
> Hi.
> I have two sql servers and have ran exec sp_addlinkedserver 'ACSPSM',
N'SQL
> Server' to link one to the other and also vise versa.
> Each server has two users permissioned.
> My problem is when ever I try to do something that does a remote write I
get
> the follow error message
> Microsoft OLE DB Provider for SQL Server error '80040e14'
> [OLE/DB provider returned message: Cannot start more transactions on this
> session.]
> Also
> when I try and manually run a stored procedure, I get:
> Remote tables are not updatable. Updatable keyset-driven cursors on remote
> tables require a transaction with the REPEATABLE_READ or SERIALIZABLE
> isolation level spanning the cursor.
> (1 row(s) affected)
>
> (1 row(s) affected)
>
> (50 row(s) affected)
> Server: Msg 7395, Level 16, State 2, Procedure ams_Move_Stock_To_PSM, Line
> 65
> Unable to start a nested transaction for OLE DB provider 'SQLOLEDB'. A
> nested transaction was required because the XACT_ABORT option was set to
> OFF.
> [OLE/DB provider returned message: Cannot start more transactions on this
> session.]
>
> Can anyone suggest how I resolve this.
>
>
> FYI
> Database Creation Script: (both database are the same scripts but db names
> are changed)
> CREATE DATABASE [msmprim] ON (NAME = N'msmprim_Data', FILENAME =
> N'D:\ACS_DB\data\msmprim_Data.MDF' , SIZE = 2000, FILEGROWTH = 10%) LOG ON
> (NAME = N'msmprim_Log', FILENAME = N'D:\ACS_DB\logs\msmprim_Log.LDF' ,
SIZE
> = 2000, FILEGROWTH = 10%)
> COLLATE Latin1_General_CI_AS
> GO
> exec sp_dboption N'msmprim', N'autoclose', N'false'
> GO
> exec sp_dboption N'msmprim', N'bulkcopy', N'false'
> GO
> exec sp_dboption N'msmprim', N'trunc. log', N'false'
> GO
> exec sp_dboption N'msmprim', N'torn page detection', N'true'
> GO
> exec sp_dboption N'msmprim', N'read only', N'false'
> GO
> exec sp_dboption N'msmprim', N'dbo use', N'false'
> GO
> exec sp_dboption N'msmprim', N'single', N'false'
> GO
> exec sp_dboption N'msmprim', N'autoshrink', N'false'
> GO
> exec sp_dboption N'msmprim', N'ANSI null default', N'false'
> GO
> exec sp_dboption N'msmprim', N'recursive triggers', N'false'
> GO
> exec sp_dboption N'msmprim', N'ANSI nulls', N'false'
> GO
> exec sp_dboption N'msmprim', N'concat null yields null', N'false'
> GO
> exec sp_dboption N'msmprim', N'cursor close on commit', N'false'
> GO
> exec sp_dboption N'msmprim', N'default to local cursor', N'false'
> GO
> exec sp_dboption N'msmprim', N'quoted identifier', N'false'
> GO
> exec sp_dboption N'msmprim', N'ANSI warnings', N'false'
> GO
> exec sp_dboption N'msmprim', N'auto create statistics', N'true'
> GO
> exec sp_dboption N'msmprim', N'auto update statistics', N'true'
> GO
>
>
> User Creation Script:
> /****** Object: Login MSM Script Date: 31/10/2002 10:41:26 ******/
> use [msmprim]
> GO
> declare @.sqlLoginName nvarchar(32) select @.sqlLoginName = N'msm'
> declare @.UserPassword nvarchar(32) select @.UserPassword = N'wibble'
> declare @.logindb nvarchar(132) select @.logindb = N'msmprim'
> declare @.loginlang nvarchar(132) select @.loginlang = N'British English'
>
> if not exists (select * from master.dbo.syslogins where loginname =
> @.sqlLoginName)
> BEGIN
>
> if @.logindb is null or not exists (select * from master.dbo.sysdatabases
> where name = @.logindb)
> select @.logindb = N'master'
> if @.loginlang is null or (not exists (select * from
master.dbo.syslanguages
> where name = @.loginlang) and @.loginlang <> N'British English')
> select @.loginlang = @.@.language
> exec sp_addlogin @.sqlLoginName, @.UserPassword, @.logindb, @.loginlang
> END
>
>
> /****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
> if not exists (select * from dbo.sysusers where name = @.sqlLoginName and
uid
> < 16382)
> EXEC sp_grantdbaccess @.sqlLoginName, @.sqlLoginName
> /****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
> exec sp_addrolemember N'db_datareader', @.sqlLoginName
> /****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
> exec sp_addrolemember N'db_datawriter', @.sqlLoginName
> /****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
> exec sp_addrolemember N'db_owner', @.sqlLoginName
> GO
> use [msmprim]
> GO
> declare @.sqlLoginName nvarchar(32) select @.sqlLoginName = N'psm'
> declare @.UserPassword nvarchar(32) select @.UserPassword = N'wibble'
> declare @.logindb nvarchar(132) select @.logindb = N'msmprim'
> declare @.loginlang nvarchar(132) select @.loginlang = N'British English'
>
> if not exists (select * from master.dbo.syslogins where loginname =
> @.sqlLoginName)
> BEGIN
>
> if @.logindb is null or not exists (select * from master.dbo.sysdatabases
> where name = @.logindb)
> select @.logindb = N'master'
> if @.loginlang is null or (not exists (select * from
master.dbo.syslanguages
> where name = @.loginlang) and @.loginlang <> N'British English')
> select @.loginlang = @.@.language
> exec sp_addlogin @.sqlLoginName, @.UserPassword, @.logindb, @.loginlang
> END
>
>
>
> /****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
> if not exists (select * from dbo.sysusers where name = @.sqlLoginName and
uid
> < 16382)
> EXEC sp_grantdbaccess @.sqlLoginName, @.sqlLoginName
> /****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
> exec sp_addrolemember N'db_datareader', @.sqlLoginName
> /****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
> exec sp_addrolemember N'db_datawriter', @.sqlLoginName
> /****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
> exec sp_addrolemember N'db_owner', @.sqlLoginName
> GO

Problem with linked server to Oracle

I run linked server from SQL Server 2000 to Oracle 9.i. In SQL Server oracle client is xa73.dll.
EXEC sp_addlinkedserver @.server='ORDB',
@.provider='MSDAORA',
@.srvproduct = 'Oracle',
@.datasrc='ORDB'
EXEC sp_addlinkedsrvlogin
@.rmtsrvname ='ORDB',
@.useself ='false',
@.locallogin =NULL,
@.rmtuser = 'ivr',
@.rmtpassword = 'jjcom'
SELECT * FROM OPENQUERY(ORDB,'SELECT * FROM SUBSCRIBER')
but get error like for the following :
Server: Msg 7399, Level 16, State 1, Line 2
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: Oracle error occurred, but error message could not be retrieved from Oracle.]
Please help me to get a solution ?

Hi,

did you check those threads on the groups ?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=56755&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=19365&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=408756&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=417751&SiteID=1

The latter one including links for troubleshotting linked servers in Oracle.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||Yes, I already checked those site and still get error.
|||

Please try to install MDAC 2.8.

Thanks

|||Hi Zoya,
I already install mdac 2.8 sp1 and try to execute query, the error message nos is :
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: Error while trying to retrieve text for error ORA-01019
]

Problem with Linked Server

I am running a linked server "SGSC" from SQL 2k to Oracle 9i

EXEC sp_addlinkedserver @.server='SGSC',
@.provider='MSDAORA',
@.srvproduct = 'Oracle',
@.datasrc='SGSC'

EXEC sp_addlinkedsrvlogin
@.rmtsrvname ='SGSC',
@.useself ='false',
@.locallogin =NULL,
@.rmtuser = 'bisadmin',
@.rmtpassword = 'bisadmin'

When I am trying to execute the following query

SELECT * INTO GL_BALANCES FROM OPENQUERY(SGSC,'SELECT * FROM GL_BALANCES')

I get this error

<eb1>OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].

OLE DB provider 'MSDAORA' reported an error. The provider did not give any information about the error.

State:01000,Native:7300,Origin:[Microsoft][ODBC SQL Server Driver][SQL Server]

State:37000,Native:7399,Origin:[Microsoft][ODBC SQL Server Driver][SQL Server]</eb1>

Can somebody help me out?

Assuming that your Oracle client is installed correctly on your SQL Server instance, I think you have a problem with the OPENQUERY statement that being passed to the Oracle server. The table name should be fully qualified with the catalog and schema names, like:

SELECT * INTO GL_BALANCES FROM OPENQUERY(SGSC,'SELECT * FROM THECATALOGNAME.THESCHEMANAME.GL_BALANCES')

or

SELECT * INTO GL_BALANCES FROM SGSC.THECATALOGNAME.THESCHEMANAME.GL_BALANCES

The catalog and schema names should be visible from within Enterprise Manager when you browse the linked server's database tables.

Mark

|||Hi Mark,

Thanks for replying.
I tried executing the query using catalog and schema names but still it gives the same error|||I assume that the query is valid? (Run SQL Plus on the box where SQL Server resides and login using bisadmin/bisadmin@.SGSC.)
Have you gotten any linked queries to run on this server using OPENQUERY? Can you run 'SELECT * FROM dual'? If you get errors doing this, are the error codes the same?
When logging into SQL Server, are you using Windows Authentication or a SQL Server login?|||Make sure that SQL Server server appears before oracle in the server path statement. The following article helped me tremendousely http://windowsitpro.com/articles/print.cfm?articleid=22264|||Hi All,

Here is my system:

Windows 2003 Server
Oracle 9i client client (9.2.0.1.0)
SQL Server 2000 (SP 3)

Here is my story:

I've setup my Linked Server according to this article http://support.microsoft.com/kb/280106/
and built the test environment according to this article : http://objectsharp.com/Blogs/matt/archive/2005/06/13/2221.aspx

When I issued this command

begin tran

exec CallOracleProcTest

rollback tran

i get this error :

Error 7391: The operation could not be performed because the OLE DB provider 'MSDAORA' does not support distributed transactions. OLE DB error trace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b]

Error 7391: The operation could not be performed because the OLE DB provider 'MSDAORA' does not support distributed transactions. OLE DB error trace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b]

Can anybody help me ?|||Im getting the same message as you do. I've checked that the DTC is running and i still get the error. Any suggestions?|||Hi, Can anyone tell me if there was ever a sucessful resolution/conclusion to this?

We've been going round in circles trying to link a Sybase db into SQLServer hosted on w W2K3 platform.

Just looking at the other linked articles now but I'd like to hear if anyone found out what was going on or if indeed why?

Thanks

Paul.sql

Problem with Linked Server

I am running a linked server "SGSC" from SQL 2k to Oracle 9i

EXEC sp_addlinkedserver @.server='SGSC',
@.provider='MSDAORA',
@.srvproduct = 'Oracle',
@.datasrc='SGSC'

EXEC sp_addlinkedsrvlogin
@.rmtsrvname ='SGSC',
@.useself ='false',
@.locallogin =NULL,
@.rmtuser = 'bisadmin',
@.rmtpassword = 'bisadmin'

When I am trying to execute the following query

SELECT * INTO GL_BALANCES FROM OPENQUERY(SGSC,'SELECT * FROM GL_BALANCES')

I get this error

<eb1>OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].

OLE DB provider 'MSDAORA' reported an error. The provider did not give any information about the error.

State:01000,Native:7300,Origin:[Microsoft][ODBC SQL Server Driver][SQL Server]

State:37000,Native:7399,Origin:[Microsoft][ODBC SQL Server Driver][SQL Server]</eb1>

Can somebody help me out?

Assuming that your Oracle client is installed correctly on your SQL Server instance, I think you have a problem with the OPENQUERY statement that being passed to the Oracle server. The table name should be fully qualified with the catalog and schema names, like:

SELECT * INTO GL_BALANCES FROM OPENQUERY(SGSC,'SELECT * FROM THECATALOGNAME.THESCHEMANAME.GL_BALANCES')

or

SELECT * INTO GL_BALANCES FROM SGSC.THECATALOGNAME.THESCHEMANAME.GL_BALANCES

The catalog and schema names should be visible from within Enterprise Manager when you browse the linked server's database tables.

Mark

|||Hi Mark,

Thanks for replying.
I tried executing the query using catalog and schema names but still it gives the same error|||I assume that the query is valid? (Run SQL Plus on the box where SQL Server resides and login using bisadmin/bisadmin@.SGSC.)
Have you gotten any linked queries to run on this server using OPENQUERY? Can you run 'SELECT * FROM dual'? If you get errors doing this, are the error codes the same?
When logging into SQL Server, are you using Windows Authentication or a SQL Server login?|||Make sure that SQL Server server appears before oracle in the server path statement. The following article helped me tremendousely http://windowsitpro.com/articles/print.cfm?articleid=22264|||Hi All,

Here is my system:

Windows 2003 Server
Oracle 9i client client (9.2.0.1.0)
SQL Server 2000 (SP 3)

Here is my story:

I've setup my Linked Server according to this article http://support.microsoft.com/kb/280106/
and built the test environment according to this article : http://objectsharp.com/Blogs/matt/archive/2005/06/13/2221.aspx

When I issued this command

begin tran

exec CallOracleProcTest

rollback tran

i get this error :

Error 7391: The operation could not be performed because the OLE DB provider 'MSDAORA' does not support distributed transactions. OLE DB error trace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b]

Error 7391: The operation could not be performed because the OLE DB provider 'MSDAORA' does not support distributed transactions. OLE DB error trace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b]

Can anybody help me ?|||Im getting the same message as you do. I've checked that the DTC is running and i still get the error. Any suggestions?|||Hi, Can anyone tell me if there was ever a sucessful resolution/conclusion to this?

We've been going round in circles trying to link a Sybase db into SQLServer hosted on w W2K3 platform.

Just looking at the other linked articles now but I'd like to hear if anyone found out what was going on or if indeed why?

Thanks

Paul.

Problem with liinked server

Hi,
I have two sql server. One is sql 2000, and another is sql 2005
On sql 2000 I try to add linked to sql 2005.
(like this)
exec sp_addlinkedserver @.server = N'ZAGREB_NEW',
@.srvproduct = N' ',
@.provider = N'SQLNCLI',
@.datasrc = N'',
@.catalog = N'komitenti',
@.provstr = N'SERVER=ZAGREB_NEW;uid=radovan;pwd=DELTA555'
GO
I try
SELECT top 1 adresa from zagreb_new.komitenti.dbo.komitenti
and have followig error:
OLE DB error trace [Non-interface error: Column 'MB' (compile-time ordinal 4) of object '"komitenti"."dbo"."komitenti"' was reported to have changed. The exact nature of the change is unknown].
Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'SQLNCLI' supplied inconsistent metadata for a column. Metadata information was changed at execution time.
When I try:
SELECT * FROM OPENQUERY(ZAGREB_NEW, 'SELECT TOP 1 * FROM komitenti.dbo.komitenti')
I got result.
How can I exec stored procedure on linked server, because
exec Zagreb_new.database.dbo.Mystored doesn't work
error is:
Msg 7212, Level 17, State 1, Line 1
Could not execute procedure 'spG_ProvjeriMB' on remote server 'zagreb_new'.
Hi
"Radovan Dobri?" wrote:

> Hi,
> I have two sql server. One is sql 2000, and another is sql 2005
> On sql 2000 I try to add linked to sql 2005.
> (like this)
> exec sp_addlinkedserver @.server = N'ZAGREB_NEW',
> @.srvproduct = N' ',
> @.provider = N'SQLNCLI',
> @.datasrc = N'',
> @.catalog = N'komitenti',
> @.provstr = N'SERVER=ZAGREB_NEW;uid=radovan;pwd=DELTA555'
> GO
>
> I try
> SELECT top 1 adresa from zagreb_new.komitenti.dbo.komitenti
> and have followig error:
>
> OLE DB error trace [Non-interface error: Column 'MB' (compile-time ordinal 4) of object '"komitenti"."dbo"."komitenti"' was reported to have changed. The exact nature of the change is unknown].
> Msg 7356, Level 16, State 1, Line 1
> OLE DB provider 'SQLNCLI' supplied inconsistent metadata for a column. Metadata information was changed at execution time.
>
> When I try:
> SELECT * FROM OPENQUERY(ZAGREB_NEW, 'SELECT TOP 1 * FROM komitenti.dbo.komitenti')
> I got result.
>
> How can I exec stored procedure on linked server, because
> exec Zagreb_new.database.dbo.Mystored doesn't work
> error is:
>
> Msg 7212, Level 17, State 1, Line 1
> Could not execute procedure 'spG_ProvjeriMB' on remote server 'zagreb_new'
The only thing I have found about this is that "it could be fixed in SP2"
which versions are the servers running? Is the client one of the servers?
Is a login defined for the linked server? See
http://msdn2.microsoft.com/en-us/library/ms189811.aspx or are you using the
default mapping, in which case will the login have permissions on the remote
server? Your error message when running the remote procedure would indicate
that they don't have permissions to run this.
John
|||Hi,
I have sp2 on server (I try it on another server with sp2 in another firm
and have exactly same result).
I try it with sql server account (and did mapping on that account). That
account have admin privil.
radovan
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:BE5DF476-CF21-4F8C-8634-D4461EA9132F@.microsoft.com...
> Hi
> "Radovan Dobri" wrote:
>
> The only thing I have found about this is that "it could be fixed in SP2"
> which versions are the servers running? Is the client one of the servers?
> Is a login defined for the linked server? See
> http://msdn2.microsoft.com/en-us/library/ms189811.aspx or are you using
> the
> default mapping, in which case will the login have permissions on the
> remote
> server? Your error message when running the remote procedure would
> indicate
> that they don't have permissions to run this.
> John
>
|||Hi
Have you tried creating the linked server as
EXEC sp_addlinkedserver @.server = 'ZAGREB_NEW',
@.srvproduct = N'',
@.provider = N'SQLNCLI',
@.datasrc = N'ZAGREB_NEW'
or
EXEC sp_addlinkedserver 'ZAGREB_NEW', N'SQL Server'
John
"Radovan Dobri?" wrote:

> Hi,
> I have sp2 on server (I try it on another server with sp2 in another firm
> and have exactly same result).
> I try it with sql server account (and did mapping on that account). That
> account have admin privil.
> radovan
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:BE5DF476-CF21-4F8C-8634-D4461EA9132F@.microsoft.com...
>
>
|||That was my first try.
Linked server works fine with
SELECT * FROM OPENQUERY(ZAGREB_NEW, 'SELECT TOP 1 * FROM
komitenti.dbo.komitenti')
but not with
SELECT TOP 1 * FROM zagreb_new.komitenti.dbo.komitenti'
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:03897BB2-0508-4F7C-A923-090DB6AE439B@.microsoft.com...[vbcol=seagreen]
> Hi
> Have you tried creating the linked server as
> EXEC sp_addlinkedserver @.server = 'ZAGREB_NEW',
> @.srvproduct = N'',
> @.provider = N'SQLNCLI',
> @.datasrc = N'ZAGREB_NEW'
> or
> EXEC sp_addlinkedserver 'ZAGREB_NEW', N'SQL Server'
>
> John
> "Radovan Dobria" wrote:
sql

Problem with liinked server

Hi,
I have two sql server. One is sql 2000, and another is sql 2005
On sql 2000 I try to add linked to sql 2005.
(like this)
exec sp_addlinkedserver @.server = N'ZAGREB_NEW',
@.srvproduct = N' ',
@.provider = N'SQLNCLI',
@.datasrc = N'',
@.catalog = N'komitenti',
@.provstr = N'SERVER=ZAGREB_NEW;uid=radovan;pwd=DELT
A555'
GO
I try
SELECT top 1 adresa from zagreb_new.komitenti.dbo.komitenti
and have followig error:
OLE DB error trace [Non-interface error: Column 'MB' (compile-time ordin
al 4) of object '"komitenti"."dbo"."komitenti"' was reported to have changed
. The exact nature of the change is unknown].
Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'SQLNCLI' supplied inconsistent metadata for a column. Metad
ata information was changed at execution time.
When I try:
SELECT * FROM OPENQUERY(ZAGREB_NEW, 'SELECT TOP 1 * FROM komitenti.dbo.komit
enti')
I got result.
How can I exec stored procedure on linked server, because
exec Zagreb_new.database.dbo.Mystored doesn't work
error is:
Msg 7212, Level 17, State 1, Line 1
Could not execute procedure 'spG_ProvjeriMB' on remote server 'zagreb_new'.Hi
"Radovan Dobri?" wrote:

> Hi,
> I have two sql server. One is sql 2000, and another is sql 2005
> On sql 2000 I try to add linked to sql 2005.
> (like this)
> exec sp_addlinkedserver @.server = N'ZAGREB_NEW',
> @.srvproduct = N' ',
> @.provider = N'SQLNCLI',
> @.datasrc = N'',
> @.catalog = N'komitenti',
> @.provstr = N'SERVER=ZAGREB_NEW;uid=radovan;pwd=DELT
A555'
> GO
>
> I try
> SELECT top 1 adresa from zagreb_new.komitenti.dbo.komitenti
> and have followig error:
>
> OLE DB error trace [Non-interface error: Column 'MB' (compile-time ord
inal 4) of object '"komitenti"."dbo"."komitenti"' was reported to have chang
ed. The exact nature of the change is unknown].
> Msg 7356, Level 16, State 1, Line 1
> OLE DB provider 'SQLNCLI' supplied inconsistent metadata for a column. Met
adata information was changed at execution time.
>
> When I try:
> SELECT * FROM OPENQUERY(ZAGREB_NEW, 'SELECT TOP 1 * FROM komitenti.dbo.kom
itenti')
> I got result.
>
> How can I exec stored procedure on linked server, because
> exec Zagreb_new.database.dbo.Mystored doesn't work
> error is:
>
> Msg 7212, Level 17, State 1, Line 1
> Could not execute procedure 'spG_ProvjeriMB' on remote server 'zagreb_new'
The only thing I have found about this is that "it could be fixed in SP2"
which versions are the servers running? Is the client one of the servers?
Is a login defined for the linked server? See
http://msdn2.microsoft.com/en-us/library/ms189811.aspx or are you using the
default mapping, in which case will the login have permissions on the remote
server? Your error message when running the remote procedure would indicate
that they don't have permissions to run this.
John|||Hi,
I have sp2 on server (I try it on another server with sp2 in another firm
and have exactly same result).
I try it with sql server account (and did mapping on that account). That
account have admin privil.
radovan
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:BE5DF476-CF21-4F8C-8634-D4461EA9132F@.microsoft.com...
> Hi
> "Radovan Dobri" wrote:
>
> The only thing I have found about this is that "it could be fixed in SP2"
> which versions are the servers running? Is the client one of the servers?
> Is a login defined for the linked server? See
> http://msdn2.microsoft.com/en-us/library/ms189811.aspx or are you using
> the
> default mapping, in which case will the login have permissions on the
> remote
> server? Your error message when running the remote procedure would
> indicate
> that they don't have permissions to run this.
> John
>|||Hi
Have you tried creating the linked server as
EXEC sp_addlinkedserver @.server = 'ZAGREB_NEW',
@.srvproduct = N'',
@.provider = N'SQLNCLI',
@.datasrc = N'ZAGREB_NEW'
or
EXEC sp_addlinkedserver 'ZAGREB_NEW', N'SQL Server'
John
"Radovan Dobri?" wrote:

> Hi,
> I have sp2 on server (I try it on another server with sp2 in another firm
> and have exactly same result).
> I try it with sql server account (and did mapping on that account). That
> account have admin privil.
> radovan
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:BE5DF476-CF21-4F8C-8634-D4461EA9132F@.microsoft.com...
>
>|||That was my first try.
Linked server works fine with
SELECT * FROM OPENQUERY(ZAGREB_NEW, 'SELECT TOP 1 * FROM
komitenti.dbo.komitenti')
but not with
SELECT TOP 1 * FROM zagreb_new.komitenti.dbo.komitenti'
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:03897BB2-0508-4F7C-A923-090DB6AE439B@.microsoft.com...[vbcol=seagreen]
> Hi
> Have you tried creating the linked server as
> EXEC sp_addlinkedserver @.server = 'ZAGREB_NEW',
> @.srvproduct = N'',
> @.provider = N'SQLNCLI',
> @.datasrc = N'ZAGREB_NEW'
> or
> EXEC sp_addlinkedserver 'ZAGREB_NEW', N'SQL Server'
>
> John
> "Radovan Dobria" wrote:
>|||Hi
On May 23, 3:40 pm, "Radovan Dobri=E6" <rado...@.servis24.hr> wrote:
> That was my first try.
> Linked server works fine with
> SELECT * FROM OPENQUERY(ZAGREB_NEW, 'SELECT TOP 1 * FROM
> komitenti.dbo.komitenti')
> but not with
> SELECT TOP 1 * FROM zagreb_new.komitenti.dbo.komitenti'
>
The error you are getting would indicate that the user context that
each one is running under is different in each case.
Try mapping the local login to a different remote login using
sp_addlinkedsrvlogin
e=2Eg.
EXEC sp_addlinkedsrvlogin 'Accounts', 'false', 'Domain\Mary', 'MaryP',
'd89q3w4u'
and/or make sure that you can connect as the same user on the remote
system and execute the procedure.
John