Distributed query against loopback linked server is not supported

I receive the following error message when I run a distributed query against
a loopback linked server in SQL Server 2005:
"The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled
the distributed transaction."
To resolve this problem, I was told that running a distributed query against
a loopback linked server is not supported in SQL Server 2005. And I am
suggested to use a remote server definition (sp_addserver) instead of a
linked server definition to resolve this problem. (Although this is only a
temporary resolution, which will deprecate in Katmai)
However, I run into another problem when I use the remote server definition.
I receive the following error message:
"Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'ServerName\SQL2005' because '' is not defined
as a remote login at the server. Verify that you have specified the correct
login name."
Could anyone please help me out?
(I include the reproduce steps for the first error message, followed by my
resolution that generates the second error message)
====== Reproduce steps for the first error message
======
1. On the ComputerA\InstanceA instance, run the following statement to
create a database and a table:
CREATE DATABASE DatabaseA
GO
USE DatabaseA
GO
CREATE TABLE TestTable(Col1 int, Col2 varchar(50))
GO
INSERT INTO TestTable VALUES (1, 'Hello World')
GO
2. On the ComputerB\InstanceB instance, run the following statement to
create a database and a table:
CREATE DATABASE DatabaseB
GO
USE DatabaseB
GO
CREATE TABLE TestTable (Col1 int, Col2 varchar(50))
GO
3. On the ComputerA\InstanceA instance, create a linked server that links to
the ComputerB\InstanceB instance. Assume the name of the linked server is
LNK_ServerB.
4. On the ComputerB\InstanceB instance, create a linked server that links to
the ComputerA\InstanceA instance. Assume the name of the linked server is
LNK_ServerA.
5. On the ComputerB\InstanceB instance, run the following statement:
USE DatabaseB
GO
CREATE PROCEDURE InsertA AS
BEGIN
SELECT * from LNK_ServerA.DatabaseA.dbo.TestTable
END
GO
6. On the ComputerA\InstanceA instance, run the following statement:
USE DatabaseA
GO
INSERT INTO TestTable
EXEC LNK_ServerB.DatabaseB.dbo.InsertA
GO
Then I receive the first error message.
======= My resolution that generates the second error message
======= 1. On the ComputerB\InstanceB instance, run the following statement:
sp_addserver 'ComputerA\InstanceA'
GO
sp_serveroption 'ComputerA\InstanceA', 'Data Access', 'TRUE'
GO
USE DatabaseB
GO
CREATE PROCEDURE InsertA AS
BEGIN
SELECT * FROM [ComputerA\InstanceA].DatabaseA.dbo.TestTable
END
GO
2. On the ComputerA\InstanceA instance, run the following statement:
USE DatabaseA
GO
INSERT INTO TestTable
EXECUTE [ComputerB\InstanceB].[DatabaseB].[dbo].[InsertA]
GO
Then I receive the second error message.Hi
Have you checked the security tab on the linked server properties in SSMS?
You may be using "be made without using a security context"
John
"Felix_Jiang" wrote:
> I receive the following error message when I run a distributed query against
> a loopback linked server in SQL Server 2005:
> "The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled
> the distributed transaction."
> To resolve this problem, I was told that running a distributed query against
> a loopback linked server is not supported in SQL Server 2005. And I am
> suggested to use a remote server definition (sp_addserver) instead of a
> linked server definition to resolve this problem. (Although this is only a
> temporary resolution, which will deprecate in Katmai)
> However, I run into another problem when I use the remote server definition.
> I receive the following error message:
> "Msg 18483, Level 14, State 1, Line 1
> Could not connect to server 'ServerName\SQL2005' because '' is not defined
> as a remote login at the server. Verify that you have specified the correct
> login name."
> Could anyone please help me out?
> (I include the reproduce steps for the first error message, followed by my
> resolution that generates the second error message)
> ======> Reproduce steps for the first error message
> ======> 1. On the ComputerA\InstanceA instance, run the following statement to
> create a database and a table:
> CREATE DATABASE DatabaseA
> GO
> USE DatabaseA
> GO
> CREATE TABLE TestTable(Col1 int, Col2 varchar(50))
> GO
> INSERT INTO TestTable VALUES (1, 'Hello World')
> GO
> 2. On the ComputerB\InstanceB instance, run the following statement to
> create a database and a table:
> CREATE DATABASE DatabaseB
> GO
> USE DatabaseB
> GO
> CREATE TABLE TestTable (Col1 int, Col2 varchar(50))
> GO
> 3. On the ComputerA\InstanceA instance, create a linked server that links to
> the ComputerB\InstanceB instance. Assume the name of the linked server is
> LNK_ServerB.
> 4. On the ComputerB\InstanceB instance, create a linked server that links to
> the ComputerA\InstanceA instance. Assume the name of the linked server is
> LNK_ServerA.
> 5. On the ComputerB\InstanceB instance, run the following statement:
> USE DatabaseB
> GO
> CREATE PROCEDURE InsertA AS
> BEGIN
> SELECT * from LNK_ServerA.DatabaseA.dbo.TestTable
> END
> GO
> 6. On the ComputerA\InstanceA instance, run the following statement:
> USE DatabaseA
> GO
> INSERT INTO TestTable
> EXEC LNK_ServerB.DatabaseB.dbo.InsertA
> GO
> Then I receive the first error message.
> =======> My resolution that generates the second error message
> =======> 1. On the ComputerB\InstanceB instance, run the following statement:
> sp_addserver 'ComputerA\InstanceA'
> GO
> sp_serveroption 'ComputerA\InstanceA', 'Data Access', 'TRUE'
> GO
> USE DatabaseB
> GO
> CREATE PROCEDURE InsertA AS
> BEGIN
> SELECT * FROM [ComputerA\InstanceA].DatabaseA.dbo.TestTable
> END
> GO
> 2. On the ComputerA\InstanceA instance, run the following statement:
> USE DatabaseA
> GO
> INSERT INTO TestTable
> EXECUTE [ComputerB\InstanceB].[DatabaseB].[dbo].[InsertA]
> GO
> Then I receive the second error message.|||John, thanks so much for your reply.
Yes, I use my Windows user logon in the security context. Additionally, both
servers are logged on using the Administrator account.
If I would like to use the remote server definition, could you please shed
any light on the second error message? Thanks!
"John Bell" wrote:
> Hi
> Have you checked the security tab on the linked server properties in SSMS?
> You may be using "be made without using a security context"
> John
> "Felix_Jiang" wrote:
> > I receive the following error message when I run a distributed query against
> > a loopback linked server in SQL Server 2005:
> > "The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled
> > the distributed transaction."
> >
> > To resolve this problem, I was told that running a distributed query against
> > a loopback linked server is not supported in SQL Server 2005. And I am
> > suggested to use a remote server definition (sp_addserver) instead of a
> > linked server definition to resolve this problem. (Although this is only a
> > temporary resolution, which will deprecate in Katmai)
> >
> > However, I run into another problem when I use the remote server definition.
> > I receive the following error message:
> > "Msg 18483, Level 14, State 1, Line 1
> > Could not connect to server 'ServerName\SQL2005' because '' is not defined
> > as a remote login at the server. Verify that you have specified the correct
> > login name."
> >
> > Could anyone please help me out?
> > (I include the reproduce steps for the first error message, followed by my
> > resolution that generates the second error message)
> >
> > ======> > Reproduce steps for the first error message
> > ======> >
> > 1. On the ComputerA\InstanceA instance, run the following statement to
> > create a database and a table:
> > CREATE DATABASE DatabaseA
> > GO
> > USE DatabaseA
> > GO
> > CREATE TABLE TestTable(Col1 int, Col2 varchar(50))
> > GO
> > INSERT INTO TestTable VALUES (1, 'Hello World')
> > GO
> >
> > 2. On the ComputerB\InstanceB instance, run the following statement to
> > create a database and a table:
> > CREATE DATABASE DatabaseB
> > GO
> > USE DatabaseB
> > GO
> > CREATE TABLE TestTable (Col1 int, Col2 varchar(50))
> > GO
> >
> > 3. On the ComputerA\InstanceA instance, create a linked server that links to
> > the ComputerB\InstanceB instance. Assume the name of the linked server is
> > LNK_ServerB.
> >
> > 4. On the ComputerB\InstanceB instance, create a linked server that links to
> > the ComputerA\InstanceA instance. Assume the name of the linked server is
> > LNK_ServerA.
> >
> > 5. On the ComputerB\InstanceB instance, run the following statement:
> > USE DatabaseB
> > GO
> > CREATE PROCEDURE InsertA AS
> > BEGIN
> > SELECT * from LNK_ServerA.DatabaseA.dbo.TestTable
> > END
> > GO
> >
> > 6. On the ComputerA\InstanceA instance, run the following statement:
> > USE DatabaseA
> > GO
> > INSERT INTO TestTable
> > EXEC LNK_ServerB.DatabaseB.dbo.InsertA
> > GO
> >
> > Then I receive the first error message.
> >
> > =======> > My resolution that generates the second error message
> > =======> > 1. On the ComputerB\InstanceB instance, run the following statement:
> > sp_addserver 'ComputerA\InstanceA'
> > GO
> > sp_serveroption 'ComputerA\InstanceA', 'Data Access', 'TRUE'
> > GO
> > USE DatabaseB
> > GO
> > CREATE PROCEDURE InsertA AS
> > BEGIN
> > SELECT * FROM [ComputerA\InstanceA].DatabaseA.dbo.TestTable
> > END
> > GO
> >
> > 2. On the ComputerA\InstanceA instance, run the following statement:
> > USE DatabaseA
> > GO
> > INSERT INTO TestTable
> > EXECUTE [ComputerB\InstanceB].[DatabaseB].[dbo].[InsertA]
> > GO
> >
> > Then I receive the second error message.|||Hi
For the first try I would not expect the error you have, from BOL:
Loopback linked servers cannot be used in a distributed transaction.
Attempting a distributed query against a loopback linked server from within a
distributed transaction causes an error:
Msg: 3910 Level: 16 State: 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction context in use by
another session.
Therefore the error message you are getting for the loopback server is not
the one I would expect.
For the second error http://support.microsoft.com/kb/818334 indicates that
there is possible wrong with the server names. I assume that both servers are
in the same domains?
Try using
INSERT INTO TestTable
SELECT * FROM OPENQUERY(LNK_ServerB,'EXEC DatabaseB.dbo.InsertA')
GO
John
"Felix_Jiang" wrote:
> John, thanks so much for your reply.
> Yes, I use my Windows user logon in the security context. Additionally, both
> servers are logged on using the Administrator account.
> If I would like to use the remote server definition, could you please shed
> any light on the second error message? Thanks!
> "John Bell" wrote:
> > Hi
> >
> > Have you checked the security tab on the linked server properties in SSMS?
> >
> > You may be using "be made without using a security context"
> >
> > John
> >
> > "Felix_Jiang" wrote:
> >
> > > I receive the following error message when I run a distributed query against
> > > a loopback linked server in SQL Server 2005:
> > > "The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled
> > > the distributed transaction."
> > >
> > > To resolve this problem, I was told that running a distributed query against
> > > a loopback linked server is not supported in SQL Server 2005. And I am
> > > suggested to use a remote server definition (sp_addserver) instead of a
> > > linked server definition to resolve this problem. (Although this is only a
> > > temporary resolution, which will deprecate in Katmai)
> > >
> > > However, I run into another problem when I use the remote server definition.
> > > I receive the following error message:
> > > "Msg 18483, Level 14, State 1, Line 1
> > > Could not connect to server 'ServerName\SQL2005' because '' is not defined
> > > as a remote login at the server. Verify that you have specified the correct
> > > login name."
> > >
> > > Could anyone please help me out?
> > > (I include the reproduce steps for the first error message, followed by my
> > > resolution that generates the second error message)
> > >
> > > ======> > > Reproduce steps for the first error message
> > > ======> > >
> > > 1. On the ComputerA\InstanceA instance, run the following statement to
> > > create a database and a table:
> > > CREATE DATABASE DatabaseA
> > > GO
> > > USE DatabaseA
> > > GO
> > > CREATE TABLE TestTable(Col1 int, Col2 varchar(50))
> > > GO
> > > INSERT INTO TestTable VALUES (1, 'Hello World')
> > > GO
> > >
> > > 2. On the ComputerB\InstanceB instance, run the following statement to
> > > create a database and a table:
> > > CREATE DATABASE DatabaseB
> > > GO
> > > USE DatabaseB
> > > GO
> > > CREATE TABLE TestTable (Col1 int, Col2 varchar(50))
> > > GO
> > >
> > > 3. On the ComputerA\InstanceA instance, create a linked server that links to
> > > the ComputerB\InstanceB instance. Assume the name of the linked server is
> > > LNK_ServerB.
> > >
> > > 4. On the ComputerB\InstanceB instance, create a linked server that links to
> > > the ComputerA\InstanceA instance. Assume the name of the linked server is
> > > LNK_ServerA.
> > >
> > > 5. On the ComputerB\InstanceB instance, run the following statement:
> > > USE DatabaseB
> > > GO
> > > CREATE PROCEDURE InsertA AS
> > > BEGIN
> > > SELECT * from LNK_ServerA.DatabaseA.dbo.TestTable
> > > END
> > > GO
> > >
> > > 6. On the ComputerA\InstanceA instance, run the following statement:
> > > USE DatabaseA
> > > GO
> > > INSERT INTO TestTable
> > > EXEC LNK_ServerB.DatabaseB.dbo.InsertA
> > > GO
> > >
> > > Then I receive the first error message.
> > >
> > > =======> > > My resolution that generates the second error message
> > > =======> > > 1. On the ComputerB\InstanceB instance, run the following statement:
> > > sp_addserver 'ComputerA\InstanceA'
> > > GO
> > > sp_serveroption 'ComputerA\InstanceA', 'Data Access', 'TRUE'
> > > GO
> > > USE DatabaseB
> > > GO
> > > CREATE PROCEDURE InsertA AS
> > > BEGIN
> > > SELECT * FROM [ComputerA\InstanceA].DatabaseA.dbo.TestTable
> > > END
> > > GO
> > >
> > > 2. On the ComputerA\InstanceA instance, run the following statement:
> > > USE DatabaseA
> > > GO
> > > INSERT INTO TestTable
> > > EXECUTE [ComputerB\InstanceB].[DatabaseB].[dbo].[InsertA]
> > > GO
> > >
> > > Then I receive the second error message.|||Hi John
Thank you for your reply. Your suggestion really works. I notice that the
reason for the second error message is that ComputerA and ComputerB are in
the same domain. When I use SQL Server Login instead to run the statement, I
didn't receive the second error message.
Many many thanks!
One remaining question, do you think the "reproduce" section qualifies a
loopback linked server?
Chen
"John Bell" wrote:
> Hi
> For the first try I would not expect the error you have, from BOL:
> Loopback linked servers cannot be used in a distributed transaction.
> Attempting a distributed query against a loopback linked server from within a
> distributed transaction causes an error:
> Msg: 3910 Level: 16 State: 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction context in use by
> another session.
> Therefore the error message you are getting for the loopback server is not
> the one I would expect.
> For the second error http://support.microsoft.com/kb/818334 indicates that
> there is possible wrong with the server names. I assume that both servers are
> in the same domains?
> Try using
> INSERT INTO TestTable
> SELECT * FROM OPENQUERY(LNK_ServerB,'EXEC DatabaseB.dbo.InsertA')
> GO
> John
> "Felix_Jiang" wrote:
> > John, thanks so much for your reply.
> >
> > Yes, I use my Windows user logon in the security context. Additionally, both
> > servers are logged on using the Administrator account.
> >
> > If I would like to use the remote server definition, could you please shed
> > any light on the second error message? Thanks!
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > Have you checked the security tab on the linked server properties in SSMS?
> > >
> > > You may be using "be made without using a security context"
> > >
> > > John
> > >
> > > "Felix_Jiang" wrote:
> > >
> > > > I receive the following error message when I run a distributed query against
> > > > a loopback linked server in SQL Server 2005:
> > > > "The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled
> > > > the distributed transaction."
> > > >
> > > > To resolve this problem, I was told that running a distributed query against
> > > > a loopback linked server is not supported in SQL Server 2005. And I am
> > > > suggested to use a remote server definition (sp_addserver) instead of a
> > > > linked server definition to resolve this problem. (Although this is only a
> > > > temporary resolution, which will deprecate in Katmai)
> > > >
> > > > However, I run into another problem when I use the remote server definition.
> > > > I receive the following error message:
> > > > "Msg 18483, Level 14, State 1, Line 1
> > > > Could not connect to server 'ServerName\SQL2005' because '' is not defined
> > > > as a remote login at the server. Verify that you have specified the correct
> > > > login name."
> > > >
> > > > Could anyone please help me out?
> > > > (I include the reproduce steps for the first error message, followed by my
> > > > resolution that generates the second error message)
> > > >
> > > > ======> > > > Reproduce steps for the first error message
> > > > ======> > > >
> > > > 1. On the ComputerA\InstanceA instance, run the following statement to
> > > > create a database and a table:
> > > > CREATE DATABASE DatabaseA
> > > > GO
> > > > USE DatabaseA
> > > > GO
> > > > CREATE TABLE TestTable(Col1 int, Col2 varchar(50))
> > > > GO
> > > > INSERT INTO TestTable VALUES (1, 'Hello World')
> > > > GO
> > > >
> > > > 2. On the ComputerB\InstanceB instance, run the following statement to
> > > > create a database and a table:
> > > > CREATE DATABASE DatabaseB
> > > > GO
> > > > USE DatabaseB
> > > > GO
> > > > CREATE TABLE TestTable (Col1 int, Col2 varchar(50))
> > > > GO
> > > >
> > > > 3. On the ComputerA\InstanceA instance, create a linked server that links to
> > > > the ComputerB\InstanceB instance. Assume the name of the linked server is
> > > > LNK_ServerB.
> > > >
> > > > 4. On the ComputerB\InstanceB instance, create a linked server that links to
> > > > the ComputerA\InstanceA instance. Assume the name of the linked server is
> > > > LNK_ServerA.
> > > >
> > > > 5. On the ComputerB\InstanceB instance, run the following statement:
> > > > USE DatabaseB
> > > > GO
> > > > CREATE PROCEDURE InsertA AS
> > > > BEGIN
> > > > SELECT * from LNK_ServerA.DatabaseA.dbo.TestTable
> > > > END
> > > > GO
> > > >
> > > > 6. On the ComputerA\InstanceA instance, run the following statement:
> > > > USE DatabaseA
> > > > GO
> > > > INSERT INTO TestTable
> > > > EXEC LNK_ServerB.DatabaseB.dbo.InsertA
> > > > GO
> > > >
> > > > Then I receive the first error message.
> > > >
> > > > =======> > > > My resolution that generates the second error message
> > > > =======> > > > 1. On the ComputerB\InstanceB instance, run the following statement:
> > > > sp_addserver 'ComputerA\InstanceA'
> > > > GO
> > > > sp_serveroption 'ComputerA\InstanceA', 'Data Access', 'TRUE'
> > > > GO
> > > > USE DatabaseB
> > > > GO
> > > > CREATE PROCEDURE InsertA AS
> > > > BEGIN
> > > > SELECT * FROM [ComputerA\InstanceA].DatabaseA.dbo.TestTable
> > > > END
> > > > GO
> > > >
> > > > 2. On the ComputerA\InstanceA instance, run the following statement:
> > > > USE DatabaseA
> > > > GO
> > > > INSERT INTO TestTable
> > > > EXECUTE [ComputerB\InstanceB].[DatabaseB].[dbo].[InsertA]
> > > > GO
> > > >
> > > > Then I receive the second error message.|||Hi
It would be useful to have the linked server scripted e.g.
EXEC sp_addlinkedserver
@.server='LoopBack',
@.srvproduct='',
@.provider='SQLNCLI',
@.datasrc='(LOCAL)'
GO
EXEC master.dbo.sp_addlinkedsrvlogin
@.rmtsrvname=N'LoopBack',@.useself=N'True',@.locallogin=NULL,@.rmtuser=NULL,@.rmtpassword=NULL
GO
EXEC master.dbo.sp_serveroption @.server=N'LoopBack', @.optname=N'rpc out',
@.optvalue=N'true'
GO
CREATE DATABASE DatabaseA
GO
USE DatabaseA
GO
CREATE TABLE TestTable(Col1 int, Col2 varchar(50))
GO
INSERT INTO TestTable VALUES (1, 'Hello World')
GO
CREATE DATABASE DatabaseB
GO
USE DatabaseB
GO
CREATE TABLE TestTable (Col1 int, Col2 varchar(50))
GO
CREATE PROCEDURE InsertA AS
BEGIN
SELECT * from LoopBack.DatabaseA.dbo.TestTable
END
GO
EXEC InsertA
GO
USE DatabaseA
GO
INSERT INTO TestTable
EXEC LoopBack.DatabaseB.dbo.InsertA
GO
/*
Msg 3910, Level 16, State 2, Line 1
Transaction context in use by another session.
*/
INSERT INTO TestTable
SELECT * FROM OPENQUERY(LoopBack,'EXEC DatabaseB.dbo.InsertA')
GO
John
"Felix_Jiang" wrote:
> Hi John
> Thank you for your reply. Your suggestion really works. I notice that the
> reason for the second error message is that ComputerA and ComputerB are in
> the same domain. When I use SQL Server Login instead to run the statement, I
> didn't receive the second error message.
> Many many thanks!
> One remaining question, do you think the "reproduce" section qualifies a
> loopback linked server?
> Chen
>
> "John Bell" wrote:
> > Hi
> >
> > For the first try I would not expect the error you have, from BOL:
> >
> > Loopback linked servers cannot be used in a distributed transaction.
> > Attempting a distributed query against a loopback linked server from within a
> > distributed transaction causes an error:
> >
> > Msg: 3910 Level: 16 State: 1
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction context in use by
> > another session.
> >
> > Therefore the error message you are getting for the loopback server is not
> > the one I would expect.
> >
> > For the second error http://support.microsoft.com/kb/818334 indicates that
> > there is possible wrong with the server names. I assume that both servers are
> > in the same domains?
> >
> > Try using
> >
> > INSERT INTO TestTable
> > SELECT * FROM OPENQUERY(LNK_ServerB,'EXEC DatabaseB.dbo.InsertA')
> > GO
> >
> > John
> >
> > "Felix_Jiang" wrote:
> >
> > > John, thanks so much for your reply.
> > >
> > > Yes, I use my Windows user logon in the security context. Additionally, both
> > > servers are logged on using the Administrator account.
> > >
> > > If I would like to use the remote server definition, could you please shed
> > > any light on the second error message? Thanks!
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > Have you checked the security tab on the linked server properties in SSMS?
> > > >
> > > > You may be using "be made without using a security context"
> > > >
> > > > John
> > > >
> > > > "Felix_Jiang" wrote:
> > > >
> > > > > I receive the following error message when I run a distributed query against
> > > > > a loopback linked server in SQL Server 2005:
> > > > > "The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled
> > > > > the distributed transaction."
> > > > >
> > > > > To resolve this problem, I was told that running a distributed query against
> > > > > a loopback linked server is not supported in SQL Server 2005. And I am
> > > > > suggested to use a remote server definition (sp_addserver) instead of a
> > > > > linked server definition to resolve this problem. (Although this is only a
> > > > > temporary resolution, which will deprecate in Katmai)
> > > > >
> > > > > However, I run into another problem when I use the remote server definition.
> > > > > I receive the following error message:
> > > > > "Msg 18483, Level 14, State 1, Line 1
> > > > > Could not connect to server 'ServerName\SQL2005' because '' is not defined
> > > > > as a remote login at the server. Verify that you have specified the correct
> > > > > login name."
> > > > >
> > > > > Could anyone please help me out?
> > > > > (I include the reproduce steps for the first error message, followed by my
> > > > > resolution that generates the second error message)
> > > > >
> > > > > ======> > > > > Reproduce steps for the first error message
> > > > > ======> > > > >
> > > > > 1. On the ComputerA\InstanceA instance, run the following statement to
> > > > > create a database and a table:
> > > > > CREATE DATABASE DatabaseA
> > > > > GO
> > > > > USE DatabaseA
> > > > > GO
> > > > > CREATE TABLE TestTable(Col1 int, Col2 varchar(50))
> > > > > GO
> > > > > INSERT INTO TestTable VALUES (1, 'Hello World')
> > > > > GO
> > > > >
> > > > > 2. On the ComputerB\InstanceB instance, run the following statement to
> > > > > create a database and a table:
> > > > > CREATE DATABASE DatabaseB
> > > > > GO
> > > > > USE DatabaseB
> > > > > GO
> > > > > CREATE TABLE TestTable (Col1 int, Col2 varchar(50))
> > > > > GO
> > > > >
> > > > > 3. On the ComputerA\InstanceA instance, create a linked server that links to
> > > > > the ComputerB\InstanceB instance. Assume the name of the linked server is
> > > > > LNK_ServerB.
> > > > >
> > > > > 4. On the ComputerB\InstanceB instance, create a linked server that links to
> > > > > the ComputerA\InstanceA instance. Assume the name of the linked server is
> > > > > LNK_ServerA.
> > > > >
> > > > > 5. On the ComputerB\InstanceB instance, run the following statement:
> > > > > USE DatabaseB
> > > > > GO
> > > > > CREATE PROCEDURE InsertA AS
> > > > > BEGIN
> > > > > SELECT * from LNK_ServerA.DatabaseA.dbo.TestTable
> > > > > END
> > > > > GO
> > > > >
> > > > > 6. On the ComputerA\InstanceA instance, run the following statement:
> > > > > USE DatabaseA
> > > > > GO
> > > > > INSERT INTO TestTable
> > > > > EXEC LNK_ServerB.DatabaseB.dbo.InsertA
> > > > > GO
> > > > >
> > > > > Then I receive the first error message.
> > > > >
> > > > > =======> > > > > My resolution that generates the second error message
> > > > > =======> > > > > 1. On the ComputerB\InstanceB instance, run the following statement:
> > > > > sp_addserver 'ComputerA\InstanceA'
> > > > > GO
> > > > > sp_serveroption 'ComputerA\InstanceA', 'Data Access', 'TRUE'
> > > > > GO
> > > > > USE DatabaseB
> > > > > GO
> > > > > CREATE PROCEDURE InsertA AS
> > > > > BEGIN
> > > > > SELECT * FROM [ComputerA\InstanceA].DatabaseA.dbo.TestTable
> > > > > END
> > > > > GO
> > > > >
> > > > > 2. On the ComputerA\InstanceA instance, run the following statement:
> > > > > USE DatabaseA
> > > > > GO
> > > > > INSERT INTO TestTable
> > > > > EXECUTE [ComputerB\InstanceB].[DatabaseB].[dbo].[InsertA]
> > > > > GO
> > > > >
> > > > > Then I receive the second error message.sql

Display tiff in Reporting Services

I have a file directory of tiff images that I need to display dynamically with reporting services. I receive the following error message: "The value of the MIMEType property for the image ‘image1’ is “image/tiff”, which is not a valid MIMEType." After looking it up sure enough tiff is not a valide type. Does anyone know of a way to display tiff images in reporting services.

Thanks,

Ross

Well I see plenty of threads on tiffs in the forums so it must be possible. I'm not sure why you're getting the error though.|||Not sure what your situation is, but one way to display a *.tif file in reporting services is to drag the image tool from the report items onto the canvas and select "Web" from the radio boxes options. Reference your url http://localhost/images/yourTIFname.tif. As far as doing this dynamically, I'm unclear as to what it is that your actually trying to do and why you want to do this in reporting services in the first place as apposed to some other means. There are a lot of easier ways to display images, instead of using reporting services to do this.|||I currently have it set to web however the images are not on a web server. I have to access them via a file path file:\\servername\image\1234.tif. The image tool in reporting services does not support the *.tif format. Is there a way to work around this limitation?|||

I wasn't able to find a solution. I'll have to force the users to publish their CADs in another format. If someone finds a solution in the future to display tiff images in ssrs please post it here for others to see.

Thanks,

Ross

|||

Pardon me. The image control in a report does support TIF.

Could these be multi-page TIFFs by any chance? I guess those might not be supported.

>L<

Display tiff in Reporting Services

I have a file directory of tiff images that I need to display dynamically with reporting services. I receive the following error message: "The value of the MIMEType property for the image ‘image1’ is “image/tiff”, which is not a valid MIMEType." After looking it up sure enough tiff is not a valide type. Does anyone know of a way to display tiff images in reporting services.

Thanks,

Ross

Well I see plenty of threads on tiffs in the forums so it must be possible. I'm not sure why you're getting the error though.|||Not sure what your situation is, but one way to display a *.tif file in reporting services is to drag the image tool from the report items onto the canvas and select "Web" from the radio boxes options. Reference your url http://localhost/images/yourTIFname.tif. As far as doing this dynamically, I'm unclear as to what it is that your actually trying to do and why you want to do this in reporting services in the first place as apposed to some other means. There are a lot of easier ways to display images, instead of using reporting services to do this.|||I currently have it set to web however the images are not on a web server. I have to access them via a file path file:\\servername\image\1234.tif. The image tool in reporting services does not support the *.tif format. Is there a way to work around this limitation?|||

I wasn't able to find a solution. I'll have to force the users to publish their CADs in another format. If someone finds a solution in the future to display tiff images in ssrs please post it here for others to see.

Thanks,

Ross

|||

Pardon me. The image control in a report does support TIF.

Could these be multi-page TIFFs by any chance? I guess those might not be supported.

>L<