Technical: Microsoft – SQL Server – Linked Server (LoopBack) – Errors – “Transaction context in use by another session” in InsteadofTrigger

Technical: Microsoft – SQL Server – Linked Server (LoopBack) – Errors – “Transaction context in use by another session” in InsteadofTrigger

Introduction

So basically we have a SQL Server Database Application and our topology involves a few DB SQL Server Instances.  During development, we are trying to constrain each Development\Developer environment to a single (lone) server.

Processing

Processing – Linked Server – Loopback (Create)



use master
go

/*
	select * from sys.servers
*/

declare @serverNameLocal sysname
declare @lsRemote        sysname
declare @tab		 sysname

set @lsRemote = 'LSREMOTE'
set @serverNameLocal = cast(serverproperty('servername') as sysname)
set @tab = char(9)

if exists
	(
	   select *
	   from   sys.servers tblServer
	   where  tblServer.name = @lsRemote
	)
begin

	print @tab + 'Dropping server ' + @lsRemote + ' ....'

	EXEC master.dbo.sp_dropserver @server=@lsRemote, @droplogins='droplogins'

end

print  @tab + 'Adding Self Server as Linked Server ... ' + @lsRemote

EXEC master.dbo.sp_addlinkedserver 
		  @server = @lsRemote
		, @srvproduct=@serverNameLocal
		, @provider=N'SQLOLEDB'
		, @datasrc=@serverNameLocal

EXEC master.dbo.sp_serveroption @server=@lsRemote
           , @optname=N'collation compatible', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'data access', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'dist', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'pub', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'rpc', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'rpc out', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'sub', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'connect timeout', @optvalue=N'0'

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'collation name', @optvalue=null

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'lazy schema validation', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'query timeout', @optvalue=N'0'

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'use remote collation', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'remote proc transaction promotion'
       , @optvalue=N'false'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = @lsRemote
       , @locallogin = NULL 
       , @useself = N'True'

print  @tab + 'Added Self Server as Linked Server ' + @lsRemote

Processing – Create Database (DBLab)


use [master]
go

if db_id('DBLab') is null
begin

	print 'Creating DB - DBLab .... '

	exec('create database [DBLab]')

	exec('ALTER DATABASE [DBLab] set recovery simple')

	print 'Creating DB - DBLab'

end
go

Processing – Table – dbo.whatisonyourmind (Create)

set noexec off
go

use [DBLab]
go

if object_id('dbo.whatisonyourmind') is not null
begin

	set noexec on
end
go

create table dbo.whatisonyourmind
(
	  [post] nvarchar(600)

	, [addedBy] sysname not null 
		constraint defaultWhatIsOnYourMindAddedBy default SYSTEM_USER

	, [addedOn] datetime not null 
		constraint defaultWhatIsOnYourMindAddedOn default getutcdate()

)

go

set noexec off
go

Processing – Table – dbo.whatisonyourmindArchive (Create)


set noexec off
go
use [DBLab]
go

if object_id('dbo.whatisonyourmindArchive') is not null
begin
	set noexec on
end
go

/*
	drop table dbo.whatisonyourmindArchive
*/
create table dbo.whatisonyourmindArchive
(

	  [id] bigint not null identity(1,1)

	, [post] nvarchar(600)

	, [addedBy] sysname not null 

	, [addedOn] datetime not null 

	, [modificationType] char (1) not null

	, [archivedBy] sysname not null 
		constraint defaultWhatIsOnYourMindArchivedBy default SYSTEM_USER

	, [dateArchived]  datetime not null 
		constraint defaultWhatIsOnYourMindArchivedOn default getutcdate()

)

go

set noexec off
go

Trigger

Here is a trigger the captures changes occurring on dbo.whatisonyourmind and propagates them unto dbo.whatisonyourmindArchive.


set noexec off
go

use [DBLab]
go

if object_id('dbo.TR_InsteadOf_Whatisonyourmind', 'TR') is not null
begin
	drop trigger dbo.TR_InsteadOf_Whatisonyourmind
end
go

if object_id('dbo.TR_InsteadOf_WhatisonyourmindLS', 'TR') is not null
begin
	drop trigger dbo.TR_InsteadOf_WhatisonyourmindLS
end
go

CREATE TRIGGER dbo.TR_InsteadOf_Whatisonyourmind
ON dbo.WhatIsOnYourMind
INSTEAD OF INSERT, UPDATE, DELETE
AS
begin

        --post changes unto dbo.whatisonyourmindArchive
	insert into dbo.whatisonyourmindArchive
	(
		  [post]

		, [addedBy]

		, [addedOn]

		, [modificationType]

	)
	select 
		  isNull(tblInsert.[post], tblDeleted.[post])

		, isNull(tblInsert.[addedBy], tblDeleted.[addedBy])

		, isNull(tblInsert.[addedOn], tblDeleted.[addedOn])

		, 
			case

				when ( 

				           (tblInsert.[post] is not null)
				        and (tblDeleted.[post] is null)

				      ) then  'I'

				when ( 

				               (tblInsert.[post] is null)
					   and (tblDeleted.[post] is not null)

				     ) then  'D'

				else 'U'

			end

	from   inserted tblInsert

		  full join deleted tblDeleted

		    on tblInsert.[post] = tblDeleted.[post]

        /* process insert/updates as 1 */
	MERGE INTO dbo.whatisonyourmind AS Target
	USING (
			select *
			from   inserted tblInsert
          )
			AS Source ([post], [addedBy], [addedOn] )
				ON Target.[addedBy] = Source.[addedBy]

	WHEN MATCHED THEN

		UPDATE 
		SET 
				  [post] = Source.[post]
				, [addedOn] = SOurce.[addedOn]

	WHEN NOT MATCHED BY TARGET THEN
		INSERT (post)
		VALUES (post)

	;

        /* remove records that are being deleted */
	delete tblTarget

	from   dbo.whatisonyourmind tblTarget

	where  tblTarget.[post] = 
			(

			   select  tblDeleted.[post]

			   from    deleted tblDeleted
											                           left outer join inserted tblInsert
													              on tblDeleted.[post] = tblInsert.[post]
										        			              and tblDeleted.[addedBy] = tblInsert.[addedBy]

			    where tblInsert.[post] is null

			)

end
go

Data Changes

Let us instigate data changes



use [DBLab]
go

/*

	truncate table [dbo].[whatisonyourmindArchive];
	truncate table [dbo].[whatisonyourmind];

*/

insert into [dbo].[whatisonyourmind]
([post])
values ('Dev1819')

insert into [dbo].[whatisonyourmind]
([post])
values ('DEV1819.2')

update [dbo].[whatisonyourmind]
set    [post] = 'DEV1819.3'
where  [addedBy] = SYSTEM_USER

delete
from   [dbo].[whatisonyourmind]
where  [post] = 'DEV1819.3'

select *
from   [dbo].[whatisonyourmind]

select *
from   [dbo].[whatisonyourmindArchive]

everything is good!

Trigger – Link Server

A few months later we get a request that we should decouple our OLTP (dbo.whatisonyourmind) and OLAP System (dbo.whatisonyourmindArchive).

And, we created the Linked Server mentioned earlier and change the trigger code a bit.

Here is our new trigger:



set noexec off
go

use [DBLab]
go

if object_id('dbo.TR_InsteadOf_Whatisonyourmind', 'TR') is not null
begin
	drop trigger dbo.TR_InsteadOf_Whatisonyourmind
end
go

if object_id('dbo.TR_InsteadOf_WhatisonyourmindLS', 'TR') is not null
begin
	drop trigger dbo.TR_InsteadOf_WhatisonyourmindLS
end
go

CREATE TRIGGER dbo.TR_InsteadOf_WhatisonyourmindLS
ON dbo.WhatIsOnYourMind
INSTEAD OF INSERT, UPDATE, DELETE
AS
begin

        /* insert into dbo.whatisonyourmind */
	insert into [LSREMOTE].[DBLab].dbo.whatisonyourmindArchive
	(
		  [post]

		, [addedBy]

		, [addedOn]

		, [modificationType]

	)
	select 
		  isNull(tblInsert.[post], tblDeleted.[post])

		, isNull(tblInsert.[addedBy], tblDeleted.[addedBy])

		, isNull(tblInsert.[addedOn], tblDeleted.[addedOn])

		, 
			case

				when ( 

				           (tblInsert.[post] is not null)
				        and (tblDeleted.[post] is null)

				      ) then  'I'

				when ( 

				               (tblInsert.[post] is null)
					   and (tblDeleted.[post] is not null)

				     ) then  'D'

				else 'U'

			end

	from   inserted tblInsert

		  full join deleted tblDeleted

		    on tblInsert.[post] = tblDeleted.[post]

	MERGE INTO dbo.whatisonyourmind AS Target
	USING (
			select *
			from   inserted tblInsert
          )
			AS Source ([post], [addedBy], [addedOn] )
				ON Target.[addedBy] = Source.[addedBy]

	WHEN MATCHED THEN

		UPDATE 
		SET 
				  [post] = Source.[post]
				, [addedOn] = SOurce.[addedOn]

	WHEN NOT MATCHED BY TARGET THEN
		INSERT (post)
		VALUES (post)

	;

	delete tblTarget

	from   dbo.whatisonyourmind tblTarget

	where  tblTarget.[post] = 
			(

			   select  tblDeleted.[post]

			   from    deleted tblDeleted
											                           left outer join inserted tblInsert
													              on tblDeleted.[post] = tblInsert.[post]
										        			              and tblDeleted.[addedBy] = tblInsert.[addedBy]

			    where tblInsert.[post] is null

			)

end
go

The basic changes between this new trigger and our earlier one is:

  • Changed “insert into dbo.whatisonyourmind” to “insert into [LSREMOTE].[DBLab].dbo.whatisonyourmindArchive”

Data Changes (Failed)

Let us instigate data changes



use [DBLab]
go

/*

	truncate table [dbo].[whatisonyourmindArchive];
	truncate table [dbo].[whatisonyourmind];

*/

insert into [dbo].[whatisonyourmind]
([post])
values ('DEV6834')

Upon, a quick test, nothing is good, as we get the error pasted below:


Msg 3910, Level 16, State 2, Line 1
Transaction context in use by another session.

Code Repository

To facilitate quick and broad participation and review,  I have shared this code in github. And, it is available in https://github.com/DanielAdeniji/MSSQLServerLinkedServerLBTrigger

 

Lab Environment

This problem has been tested out in the following environments:

  • Microsoft SQL Server 2012 – SP1 {11.0.2100}
  • Microsoft SQL Server 2008/R2 – SP2 {10.50.4000}
  • Microsoft SQL Server 2000 – SP4 {8.0.2039}

 

Summary

This seems to be a problem and I have been able to reproduce on a couple of MS SQL Server Instances; both of them running MS SQL Server 2012.

And, thus I opened a Connect Item – “Transaction context in use by another session (when LoopBack LinkedServer is used in trigger” {https://connect.microsoft.com/SQLServer/feedback/details/814524/transaction-context-in-use-by-another-session-when-loopback-linkedserver-is-used-in-trigger} to track.

Addendum

2014-04-09 – Connect Feedback – Alexey Stepanov

Alexey commented on the Connect Item and pointed out two Technet Articles that discusses the ‘handicap’:

References

References – Linked Server – Loopback

Microsoft – SQL Server – Transact SQL – Linked Server – Error – “Transaction context in use by another session (Msg 3910, Level 16, State 2, Line 1)”

Introduction

Playing around with Transact SQL today and found a limitation that was not obvious till today.

It concerns Linked Servers and explicit transactions.

Background

Background – Linked Server

So if you create a linked Server Connection; i.e.


USE [master]
GO

EXEC master.dbo.sp_addlinkedserver @server = N'dbremote'
          , @srvproduct=N'DEVDB\MSSQL2012'
          , @provider=N'SQLOLEDB'
          , @datasrc=N'DEVDB\MSSQL2012'
GO
EXEC master.dbo.sp_serveroption
               @server=N'dbremote'
             , @optname=N'collation compatible'
             , @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption
              @server=N'dbremote'
            , @optname=N'data access'
            , @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption
             @server=N'dbremote'
           , @optname=N'rpc'
           , @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption
             @server=N'dbremote'
           , @optname=N'rpc out'
           , @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption
             @server=N'dbremote'
           , @optname=N'connect timeout'
           , @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption
              @server=N'dbremote'
            , @optname=N'collation name'
            , @optvalue=null
GO
EXEC master.dbo.sp_serveroption
               @server=N'dbremote'
             , @optname=N'lazy schema validation'
             , @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption
                @server=N'dbremote'
              , @optname=N'query timeout'
              , @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption
                @server=N'dbremote'
              , @optname=N'use remote collation'
              , @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption
                @server=N'dbremote'
              , @optname=N'remote proc transaction promotion'
              , @optvalue=N'true'
GO

Background – Linked Server – Queries

And, you issue queries against it:

Query – Select

   select *
   from   dbRemote.hr.dbo.employee

Query – Delete

   delete
   from   dbRemote.hr.dbo.employee
   where  employeeID = 10101

everything is good!

Background – Linked Server – Queries – Fail

… until you decide to follow best practices and loop your SQL into an explicit transaction. Additionally, you reference a local resource via 4 part names:

Query – Delete

    begin tran tranCancellation

      --reference a local resource (dbRemote) via 4 part name
      delete
      from   dbRemote.hr.dbo.employee
      where  employeeID = 10101

   commit tran tranCancellation

Result


Msg 3910, Level 16, State 2, Line 1
Transaction context in use by another session.

Please keep in mind that you ‘re OK as long as SQL code that references local resources do not refer to them via 4 part names.

Query – Delete


    begin tran tranCancellation

      --notice that we are using 3-part and not 4-part names
      delete
      from   hr.dbo.employee
      where  employeeID = 10101

      --OK to use 4 part names for remote resource
      insert into dbConsolidated.hr.dbo.employee
      (employeeID, employeeName)
      values (10191, 'Sallie');

   commit tran tranCancellation

Reason

The reason for the failure is that “Distributed Transactions”, the underlying infrastructure that co-ordinates transaction management only works when objects referenced via 4 part names are actually remote.

Crediting

Crediting Microsoft’s Piort Rodak for his well written blog posting:

Piotr Rodak – DATA ACCESS setting on local server

http://sqlblogcasts.com/blogs/piotr_rodak/archive/2009/11/22/data-access-setting-on-local-server.aspx

The limitation of a loopback linked server is that it cannot take part in an explicit or implicit transaction because this would require loopback distributed transaction, something that MSDTC apparently does not support.

In such scenario, you will get error 3910.

Implications

I reached the same conclusion has Piort Rodak, but habitually I lean towards pushing ahead .. that is, until Goggle lands me where I can actually follow a nicely published and creditable source.

I normally would not face this problem, but did so this time while provisioning a LAB environment and I was trying to use the same machine for multiple SQL Server Instances.

And, also I was trying to use the same code base as the original SQL.  That is, use 4 part names.

 

References

References – Linked Server – Use-case scenario

References – Distributed Transactions on Local Server

References – Transact SQL – Conventions

SQL Server – Linked Server /ODBC – Error – Datetime field overflow. Fractional second precision exceeds the scale specified in the parameter binding (Msg 7320, Level 16, State 2, Line 39)

Error Message


OLE DB provider "MSDASQL" for linked server "DBLABJ_NC_10_ODBC" returned message
"[Microsoft][SQL Server Native Client 10.0]Datetime field overflow. Fractional second
precision exceeds the scale specified in the parameter binding.".

Msg 7320, Level 16, State 2, Line 39
Cannot execute the query "SELECT FROM "dbHR"."dbo"."employee" "Tbl1005" WHERE "Tbl1005"."hiredate">?" against OLE DB provider "MSDASQL" for linked server
"DBLABJ_NC_10_ODBC".

Solution

Original Code


declare @hireDataDestMax datetime
declare @hireDateDefaultMax datetime

set @hireDateDefaultMax = '1/1/1900'

set @hirDateDestMax =
			(
			   select max(hiredate)
			   from   dbo.employee tblSrc
			)

if @hireDataDestMax is null
begin
	set @hireDataDestMax = @hireDateDefaultMax
end

insert into [nxtaccess].dbo.employee
(
   [employeeID]
  ,[employeeName]
  ,[hireData]
)
select
   [employeeID]
  ,[employeeName]
  ,[hireData]
from  [DBLABJ_NC_10_ODBC].[dbHR].dbo.employee tblSrc
where [hiredate] > @hireDataDestMax

Revised Code


declare @hireDataDestMax smalldatetime
declare @hireDateDefaultMax smalldatetime

set @hireDateDefaultMax = '1/1/1900'

set @hirDateDestMax =
			(
			   select max(hiredate)
			   from   dbo.employee tblSrc
			)

if @hireDataDestMax is null
begin
	set @hireDataDestMax = @hireDateDefaultMax
end

insert into [nxtaccess].dbo.employee
(
   [employeeID]
  ,[employeeName]
  ,[hireData]
)
select
   [employeeID]
  ,[employeeName]
  ,[hireData]
from  [DBLABJ_NC_10_ODBC].[dbHR].dbo.employee tblSrc
where [hiredate] > @hireDataDestMax

Explanation

Changed the definition of our local datetime variable from datetime to smalldatetime.

It seems that the Linked Server ODBC layer is not able to handle the more expansive datetime variable; and that we will have to use smalldatetime.

Microsoft – SQL Server – Linked Server – SQL Server v2012 -to- SQL Server v2000 (SP3)

Introduction

Got in a bit of trouble trying to prepare\use a Linked Server connection from MS SQL Server 2012 SP1 to MS SQL Server v2000 SP3.

Background

BTW, same Linked Server Connection works from MS SQL Server 2008/R2 (SP1) to MS SQL Server v2000 (SP3).

Scenario

Here is how our Linked Server Connections were attempted.

Scenario – Microsoft OLE DB Provider for SQL Server

When we tried using “Microsoft OLE DB Provider for SQL Server”:

SQL Server Linked Connection – GUI

NewLinkedServer

SQL Server Linked Connection – SQL Script


USE [master]
GO

EXEC master.dbo.sp_addlinkedserver 
		  @server = N'DBLABJ_OLEDBMS'
		, @srvproduct=N'DBLABJ'
		, @provider=N'SQLOLEDB'
		, @datasrc=N'DBLABJ'

GO
EXEC master.dbo.sp_serveroption 
           @server=N'DBLABJ_OLEDBMS'
         , @optname=N'collation compatible'
         , @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption 
            @server=N'DBLABJ_OLEDBMS'
          , @optname=N'data access'
          , @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption 
             @server=N'DBLABJ_OLEDBMS'
           , @optname=N'rpc'
           , @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption 
              @server=N'DBLABJ_OLEDBMS'
            , @optname=N'rpc out'
            , @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption 
                @server=N'DBLABJ_OLEDBMS'
              , @optname=N'lazy schema validation'
              , @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption 
                 @server=N'DBLABJ_OLEDBMS'
               , @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption 
                 @server=N'DBLABJ_OLEDBMS'
               , @optname=N'use remote collation'
               , @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption 
                  @server=N'DBLABJ_OLEDBMS'
                , @optname=N'remote proc transaction promotion'
                , @optvalue=N'false'
GO

EXEC master.dbo.sp_addlinkedsrvlogin 
                   @rmtsrvname = N'DBLABJ_OLEDBMS'
                 , @locallogin = NULL 
                 , @useself = N'True'
GO

Error

Error Message Text:

SQL Server Native Client 11.0 does not support connections to SQL Server 2000 or 
earlier versions.

OLE DB provider "SQLNCLI11" for linked server "DBLABJ_OLEDBMS" returned message 
"Client unable to establish connection". (Microsoft SQL Server, Error: 22)</code>
<code>Error Message Screen:

GUI:
SQLServerNativeClientDoesNotSupport

Scenario – SQL Server Native Client 11.0

When we tried using “SQL Server Native Client 11.0”:

SQL Server Linked Connection – GUI

SQLServerNativeClient-v11


USE [master]
GO
EXEC master.dbo.sp_addlinkedserver 
		  @server = N'DBLABJ_NC_11'
		, @srvproduct=N'DBLABJ'
		, @provider=N'SQLNCLI11'
		, @datasrc=N'DBLABJ'
GO

EXEC master.dbo.sp_serveroption @server=N'DBLABJ_NC_11'
         , @optname=N'collation compatible'
	 , @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'DBLABJ_NC_11'
         , @optname=N'data access'
	 , @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'DBLABJ_NC_11'
        , @optname=N'rpc'
	, @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption 
                        @server=N'DBLABJ_NC_11'
                       , @optname=N'rpc out'
			, @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption 
                       @server=N'DBLABJ_NC_11'
                     , @optname=N'collation name'
		     , @optvalue=null
GO

EXEC master.dbo.sp_serveroption 
                       @server=N'DBLABJ_NC_11'
                     , @optname=N'lazy schema validation'
		     , @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption 
                @server=N'DBLABJ_NC_11'
              , @optname=N'use remote collation'
	      , @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption 
          @server=N'DBLABJ_NC_11'
         , @optname=N'remote proc transaction promotion'
	 , @optvalue=N'false'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin 
			  @rmtsrvname = N'DBLABJ_NC_11'
			, @locallogin = NULL 
			, @useself = N'True'
GO

Image:

SQLServerNativeClient-v11-error

Textual:

SQL Server Native Client 11.0 does not support connections to SQL Server 2000 or earlier versions.

OLE DB provider “SQLNCLI11” for linked server “DBLABJ_NC_11” returned message “Client unable to establish connection”. (Microsoft SQL Server, Error: 22)

Scenario – SQL Server Native Client 10.0

When we tried using “SQL Server Native Client 10.0”:

SQL Server Linked Connection – GUI

SQLServerNativeClient_v10


USE [master]
GO
EXEC master.dbo.sp_addlinkedserver 
		  @server = N'DBLABJ_NC_10'
		, @srvproduct=N'DBLABJ'
		, @provider=N'SQLNCLI10'
		, @datasrc=N'DBLABJ'

GO
EXEC master.dbo.sp_serveroption @server=N'DBLABJ_NC_10'
          , @optname=N'collation compatible'
          , @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'DBLABJ_NC_10'
          , @optname=N'data access'
          , @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'DBLABJ_NC_10'
         , @optname=N'rpc'
         , @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'DBLABJ_NC_10'
        , @optname=N'rpc out'
        , @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'DBLABJ_NC_10'
         , @optname=N'collation name'
         , @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'DBLABJ_NC_10'
         , @optname=N'lazy schema validation'
         , @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DBLABJ_NC_10'
        , @optname=N'query timeout'
        , @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'DBLABJ_NC_10'
       , @optname=N'use remote collation'
       , @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'DBLABJ_NC_10'
			, @optname=N'remote proc transaction promotion'
			, @optvalue=N'false'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin 
			  @rmtsrvname = N'DBLABJ_NC_10'
			, @locallogin = NULL 
			, @useself = N'True'
GO

Image:

CannotObtainTheRequiredInterface
Textual:


The OLE DB provider "SQLNCLI10" for linked server "DBLABJ_NC_10" reported an error. Access denied.

Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB provider "SQLNCLI10" for linked server "DBLABJ_NC_10". (Microsoft SQL Server, Error: 7399)

Textual – 4 part name query – Error 7339/ Error 7330

Query


select *
from DBLABJ_NC_10.tempdb.dbo.sysobjects

 


Error Message

Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "SQLNCLI10" for linked server "DBLABJ_NC_10" reported an error. 
The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 2
Cannot fetch a row from OLE DB provider "SQLNCLI10" for linked server "DBLABJ_NC_10".

Textual - Openquery - Error 7339/ Error 7330

Query


select * from openquery(DBLABJ_NC_10, 'select * from tempdb.dbo.sysobjects')  

Message
Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "SQLNCLI10" for linked server"DBLABJ_NC_10" reported an error. The provider reported an unexpected catastrophic failure. Msg 7330, Level 16, State 2, Line 1 Cannot fetch a row from OLE DB provider "SQLNCLI10" for linked server "DBLABJ_NC_10".

Fixes

Fixes - ODBC

  • Using ODBC Administrator, create an ODBC Connection
  • Using SQL Server Manager Studio, create Linked Server (LS) Connection

ODBC Administrator

Create a New Data Source

CreateANewDataSource

How should SQL Server Authenticate

CreateANewDataSource_Authentication

Change Database Name & Connection ANSI' settings

CreateANewDataSource_ChangeDefaultDatabase

Language

CreateANewDataSource_Language

Confirm Configuration Settings

CreateANewDataSource_Confirmation

Validation

CreateANewDataSource_Validation

Linked Server

Here is the Linked Server

DBLABJ_NC_10_ODBC

Using ODBC work!

Fixes - SQL Server Patches

This is a possible fix; but I have yet to try it -- Apply Microsoft SQL Server 2000 /Service Pack 4 to upgrade SQL Server 20003 from SP3 to SP4.

References

Technical: Microsoft – SQLServer – Error – “The conversion of the varchar value ‘100000’ overflowed an INT2 column. Use a larger integer column” (Msg 244, Level 16, State 2, Line 355)

Technical: Microsoft – SQLServer – Error – “The conversion of the varchar value ‘100000’ overflowed an INT2 column. Use a larger integer column” (Msg 244, Level 16, State 2, Line 355)



Msg 244, Level 16, State 2, Line 355

The conversion of the varchar value '100000' overflowed an INT2 column. 
Use a larger integer column  

Failing Code



    SELECT *
    FROM [dbo].[sales] tblSource

	inner join dbo.Worker tblWorker
	    on  (tblSource.salesPersonID = tblWorker.EmployeeID)

Corrected Code



    SELECT *
    FROM [dbo].[sales] tblSource

	inner join dbo.Worker tblWorker

           on   (isNumeric(tblWorker.EmployeeID) = 1)
	   and  (cast(tblSource.salesPersonID as int) = tblWorker.EmployeeID)

Explanation

  • Added isNumeric check to make sure employeeID is numeric
  • Cast salesPersonID as an int