Msg 7320

Hi,
I've successfully created a connection (linked server) in
SQL Server 2000 to an Oracle database (9i). I'm running
SQL Server on a WIN2K machine.
Now, I know that the connection is made because I can see
the list of tables and views under my Linked Server.
With that said, if I try ANY kind of query on these tables
I get a Msg 7320 error. The name of my Linked Server is
OracleSource. Here are some examples of queries that fail:
select * from openquery(OracleSource, 'select * from
ETA_USER.ABBR_DEVICE')
Select * From OracleSource..ETA_USER.ABBR_DEVICE
ETA_USER is the Schema and ABBR_DEVICE is the table name.
They are the same except one is using OpenQuery and not
the other. I know that this query works because I tried
it successfully in SQL Plus.
I have no idea what to try next and there doesn't seem to
be any help on the net (I've already consulted KB articles
270119, 251238, 248156 and 280106)
Any ideas, please!
Thanks, Skip.You might be encountering a limitation as described in :
244661 INFO: Limitations of Microsoft Oracle ODBC Driver and OLEDB Provider
http://support.microsoft.com/?id=244661
You can also try dbcc trace (7300,3604) to see if you can get the provider
to return more information on why it is failing.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Msg 7320

Hi,

I've successfully created a connection (linked server) in SQL Server 2000 to an Oracle database (9i). I'm running SQL Server on a WIN2K machine.

Now, I know that the connection is made because I can see the list of tables and views under my Linked Server.

With that said, if I try ANY kind of query on these tables I get a Msg 7320 error. The name of my Linked Server is OracleSource. Here are some examples of queries that fail:

select * from openquery(OracleSource, 'select * from ETA_USER.ABBR_DEVICE')
Select * From OracleSource..ETA_USER.ABBR_DEVICE

ETA_USER is the Schema and ABBR_DEVICE is the table name.

They are the same except one is using OpenQuery and not the other. I know that this query works because I tried it successfully in SQL Plus.

I have no idea what to try next and there doesn't seem to be any help on the net (I've already consulted KB articles 270119, 251238, 248156 and 280106)

Any ideas, please!

Thanks, Skip.Have you tried it with the 4 part name?|||Originally posted by Brett Kaiser
Have you tried it with the 4 part name?

The second select is using four-part naming, no? I know I'm missing the catalog (between the server name and the schema) but I don't think there's one because sp_table_ex returned NULL for this table's catalog.

Is there a way in SQL Plus to view a table's catalog if there's one?

Thanks,

Skip.|||What do you have selected under Security Tab int the linked server option in EM. I have had a problem where the Linked server to oracle did not work until i had the radio button

Be made with this security context selected.

Another Miracle .... brett|||Thanks Brett but I do need a security context because authentication is required to login to the remote server (a special user has been created for this purpose).

Plus, the EXACT same query works under SQL Plus!

Anyway, any other ideas?

Skip|||Yup ..

Thats what I am talking about ... The problem i faced a long time ago was ...
I was able to run the query from SQL plus ... also from QA but not from a stored procedure ... and using the security context helped me out ... am not sure in your case what is the problem until you elaborate what all you have tried out

Msg 7314 while querying across linked servers

Hello
I am beginning to think that linked server are more trouble than they are wo
rth and MS will need to work harder to get these to work properly. Here is t
he latest problem.
From srv1, when I do
select * from srv2.db1.information_schema.tables
I get
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'srv2' does not contain table '"db1"."information_schema"."t
ables"'. The table either does not exist or the current user does not have
permissions on that table.
OLE DB error trace [Non-interface error: OLE DB provider does not conta
in the table: ProviderName='srv2', TableName='"db1"."information_schema"."ta
bles"'].
It works if I connect to srv2 with the login being used to link the 2 server
s and do
use db1
go
select * from information_schema.tables
go
Also if I do <<<select * from srv2.db1.dbo.someothertable>>> it works.
Trying this on SQL Server 2000 enterprise hotfix 837.
Any clues?
Thanks for reading and appreciate your help
ArunArun,
Try this:
exec srv2.db1.dbo.sp_executesql N'select * from information_schema.tables'
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Arun wrote:
> Hello
> I am beginning to think that linked server are more trouble than they are
worth and MS will need to work harder to get these to work properly. Here is
the latest problem.
>
> From srv1, when I do
> select * from srv2.db1.information_schema.tables
> I get
> Server: Msg 7314, Level 16, State 1, Line 1
> OLE DB provider 'srv2' does not contain table '"db1"."information_schema".
"tables"'. The table either does not exist or the current user does not hav
e permissions on that table.
> OLE DB error trace [Non-interface error: OLE DB provider does not con
tain the table: ProviderName='srv2', TableName='"db1"."information_schema"."
tables"'].
> It works if I connect to srv2 with the login being used to link the 2 serv
ers and do
> use db1
> go
> select * from information_schema.tables
> go
> Also if I do <<<select * from srv2.db1.dbo.someothertable>>> it works.
> Trying this on SQL Server 2000 enterprise hotfix 837.
> Any clues?
> Thanks for reading and appreciate your help
> Arun
>|||It works. Thanks Mark
Arun
"Mark Allison" wrote:

> Arun,
> Try this:
> exec srv2.db1.dbo.sp_executesql N'select * from information_schema.tables'
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Arun wrote:
>

Msg 7314 while querying across linked servers

Hello
I am beginning to think that linked server are more trouble than they are worth and MS will need to work harder to get these to work properly. Here is the latest problem.
From srv1, when I do
select * from srv2.db1.information_schema.tables
I get
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'srv2' does not contain table '"db1"."information_schema"."tables"'. The table either does not exist or the current user does not have permissions on that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='srv2', TableName='"db1"."information_schema"."tables"'].
It works if I connect to srv2 with the login being used to link the 2 servers and do
use db1
go
select * from information_schema.tables
go
Also if I do <<<select * from srv2.db1.dbo.someothertable>>> it works.
Trying this on SQL Server 2000 enterprise hotfix 837.
Any clues?
Thanks for reading and appreciate your help
Arun
Arun,
Try this:
exec srv2.db1.dbo.sp_executesql N'select * from information_schema.tables'
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Arun wrote:
> Hello
> I am beginning to think that linked server are more trouble than they are worth and MS will need to work harder to get these to work properly. Here is the latest problem.
>
> From srv1, when I do
> select * from srv2.db1.information_schema.tables
> I get
> Server: Msg 7314, Level 16, State 1, Line 1
> OLE DB provider 'srv2' does not contain table '"db1"."information_schema"."tables"'. The table either does not exist or the current user does not have permissions on that table.
> OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='srv2', TableName='"db1"."information_schema"."tables"'].
> It works if I connect to srv2 with the login being used to link the 2 servers and do
> use db1
> go
> select * from information_schema.tables
> go
> Also if I do <<<select * from srv2.db1.dbo.someothertable>>> it works.
> Trying this on SQL Server 2000 enterprise hotfix 837.
> Any clues?
> Thanks for reading and appreciate your help
> Arun
>
|||It works. Thanks Mark
Arun
"Mark Allison" wrote:

> Arun,
> Try this:
> exec srv2.db1.dbo.sp_executesql N'select * from information_schema.tables'
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Arun wrote:
>

Msg 7314 while querying across linked servers

Hello
I am beginning to think that linked server are more trouble than they are worth and MS will need to work harder to get these to work properly. Here is the latest problem.
From srv1, when I do
select * from srv2.db1.information_schema.tables
I get
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'srv2' does not contain table '"db1"."information_schema"."tables"'. The table either does not exist or the current user does not have permissions on that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='srv2', TableName='"db1"."information_schema"."tables"'].
It works if I connect to srv2 with the login being used to link the 2 servers and do
use db1
go
select * from information_schema.tables
go
Also if I do <<<select * from srv2.db1.dbo.someothertable>> it works.
Trying this on SQL Server 2000 enterprise hotfix 837.
Any clues?
Thanks for reading and appreciate your help
ArunArun,
Try this:
exec srv2.db1.dbo.sp_executesql N'select * from information_schema.tables'
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Arun wrote:
> Hello
> I am beginning to think that linked server are more trouble than they are worth and MS will need to work harder to get these to work properly. Here is the latest problem.
>
> From srv1, when I do
> select * from srv2.db1.information_schema.tables
> I get
> Server: Msg 7314, Level 16, State 1, Line 1
> OLE DB provider 'srv2' does not contain table '"db1"."information_schema"."tables"'. The table either does not exist or the current user does not have permissions on that table.
> OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='srv2', TableName='"db1"."information_schema"."tables"'].
> It works if I connect to srv2 with the login being used to link the 2 servers and do
> use db1
> go
> select * from information_schema.tables
> go
> Also if I do <<<select * from srv2.db1.dbo.someothertable>> it works.
> Trying this on SQL Server 2000 enterprise hotfix 837.
> Any clues?
> Thanks for reading and appreciate your help
> Arun
>

Msg 2540

Msg 2540, Level 16, State 1
Allocation Discrepancy: Page is allocated but not linked;
check the following pages and ids: allocation pg#=614144
extent id=614392 logical pg#=614395 object id on extent=8
(object name = syslogs) indid on extent=0
I forgot to ask what the ramifications are if I migrate
to SQL 2000 with this message still occurring.Jim,
Always fix such problems if you can.
If all of these pages are for syslogs (ie, the transaction log) then life
might not be too bad if you migrate. But I cannot say for sure.
What I would do.
1 - Backup the database
2 - Restore the database backup to a different database, e.g.
mydatabaseRestore
3 - Run the fixes on the second database and observe the results.
Russell Fields
"Jim" <anonymous@.discussions.microsoft.com> wrote in message
news:06e601c3b399$e5d9cbd0$a101280a@.phx.gbl...
> Msg 2540, Level 16, State 1
> Allocation Discrepancy: Page is allocated but not linked;
> check the following pages and ids: allocation pg#=614144
> extent id=614392 logical pg#=614395 object id on extent=8
> (object name = syslogs) indid on extent=0
> I forgot to ask what the ramifications are if I migrate
> to SQL 2000 with this message still occurring.

MSDTC Problem

Hi,

I recently moved & upgraded a database from 7.0 to 2000 that makes use of linked servers. After upgrading, a number of queries started failing with the error below. These linked servers with this problem either had a stopped MSDTC or were behind a firewall. I've read the articles on how to configure MSDTC to work through a firewall, but before I do this I'm wondering why this is even happening. There were no problems when the database was at SQL 7.0. Does anyone know why sql 2000 would need to use MSDTC, but SQL7.0 would not? I don't need to use a distributed transaction, so ideally I'd like to somehow tell SQL not to use MSDTC at all. Any ideas?

Thanks

Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].Thanks in advance for any help you can offer.

I have same problem as cpyne.

I recently upgraded a SQL server 7.0 box to SQL server 2000.
(Standard edition)

I had several stored procs selecting data from linked servers and inserting into local tables.

They all ran fine for the last 2 years but after the upgrade they all fail.
Linked servers include SQL Server, ORACLE and INFORMIX databases.

Error report indicates that MSDTC is now unable to begin distributed transaction.

Anybody know why MSDTC is suddenly failing after I switched from version 7 to 2000?|||On most of our servers that had this problem, MSDTC was stopped on the remote server. Starting it and setting it to automatic fixed the issue. There were a few queries that didn't even appear to be distributed transactions. They were just local tables with joins to linked servers. I spoke with MS about this and they said that SQL 2000 makes more use for MSDTC in places where SQL 7.0 didn't. The only servers where we had difficulty getting this working were ones where there was a firewall between the master server and the linked ones. In the end we dicided to chang the logic of our application, but MS suggested some articles that listed which ports needed to be opened. I don't have them handy, but if you have this problem, search on Firewall and MSDTC.

Charlie

MSDTC on server 'SERVER_NAME' is unavailable

Hi
I have two stored procedures that use a linked server to access an Access
database from SQL Server 2000.
The first takes no input:
CREATE PROCEDURE GetModules
AS
INSERT INTO datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime) SELECT * FROM
OPENQUERY(ProjAForcBCase0,'select * from datCase')
GO
This procedure runs fine and does exactly as expected.
The second parameterises the linked server name (the idea being that these
will be created dynamically in code) so that it can be passed in to the
stored procedure.
CREATE PROCEDURE GetModulesDynamic
@.LinkedServer nvarchar(4000)
AS
DECLARE @.TSQL nvarchar(4000)
DECLARE @.OPENQUERY nvarchar(4000)
SET @.OPENQUERY = 'SELECT * FROM OPENQUERY('+ @.LinkedServer + ','''
SET @.TSQL = 'select * from datCase'')'
Insert into datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime)
EXEC(@.OPENQUERY+@.TSQL)
GO
However if I run this second stored procedure I am met with the error
message "MSDTC on server 'SERVER_NAME' is unavailable".
Both these stored procedures were executed on my PC (win 2K) using a local
SQL Server 2000 instance and a localo access database. As such I do not
understand why I should require MSDTC for the second procedure and not for
the first.
Any explanation would be extremely useful.
Also can MSDTC be installed on Win 2K and if so where can I get it from?
Thanks
TomHave just dicovered that I didn't have DTC running (had been looking for a
service called MSDTC as defined in the error message)
As such this now works as expected for both procedures, but if anyone knows
why it worked without DTC for the first procedure and not the second I would
be interested in an explanation.
"TomPearson" wrote:

> Hi
> I have two stored procedures that use a linked server to access an Access
> database from SQL Server 2000.
> The first takes no input:
> CREATE PROCEDURE GetModules
> AS
> INSERT INTO datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
> UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime) SELECT * FROM
> OPENQUERY(ProjAForcBCase0,'select * from datCase')
> GO
> This procedure runs fine and does exactly as expected.
> The second parameterises the linked server name (the idea being that these
> will be created dynamically in code) so that it can be passed in to the
> stored procedure.
> CREATE PROCEDURE GetModulesDynamic
> @.LinkedServer nvarchar(4000)
> AS
> DECLARE @.TSQL nvarchar(4000)
> DECLARE @.OPENQUERY nvarchar(4000)
> SET @.OPENQUERY = 'SELECT * FROM OPENQUERY('+ @.LinkedServer + ','''
> SET @.TSQL = 'select * from datCase'')'
> Insert into datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
> UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime)
> EXEC(@.OPENQUERY+@.TSQL)
> GO
> However if I run this second stored procedure I am met with the error
> message "MSDTC on server 'SERVER_NAME' is unavailable".
> Both these stored procedures were executed on my PC (win 2K) using a local
> SQL Server 2000 instance and a localo access database. As such I do not
> understand why I should require MSDTC for the second procedure and not for
> the first.
> Any explanation would be extremely useful.
> Also can MSDTC be installed on Win 2K and if so where can I get it from?
> Thanks
> Tom|||Most likely because in the second procedure, you used an
insert exec which would promote the transaction to a
distributed transaction. The first procedure used an insert
select so was a local transaction.
-Sue
On Mon, 31 Jan 2005 07:45:03 -0800, "TomPearson"
<TomPearson@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Have just dicovered that I didn't have DTC running (had been looking for a
>service called MSDTC as defined in the error message)
>As such this now works as expected for both procedures, but if anyone knows
>why it worked without DTC for the first procedure and not the second I woul
d
>be interested in an explanation.
>"TomPearson" wrote:
>

MSDTC on server 'SERVER_NAME' is unavailable

Hi
I have two stored procedures that use a linked server to access an Access
database from SQL Server 2000.
The first takes no input:
CREATE PROCEDURE GetModules
AS
INSERT INTO datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime) SELECT * FROM
OPENQUERY(ProjAForcBCase0,'select * from datCase')
GO
This procedure runs fine and does exactly as expected.
The second parameterises the linked server name (the idea being that these
will be created dynamically in code) so that it can be passed in to the
stored procedure.
CREATE PROCEDURE GetModulesDynamic
@.LinkedServer nvarchar(4000)
AS
DECLARE @.TSQL nvarchar(4000)
DECLARE @.OPENQUERY nvarchar(4000)
SET @.OPENQUERY = 'SELECT * FROM OPENQUERY('+ @.LinkedServer + ','''
SET @.TSQL = 'select * from datCase'')'
Insert into datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime)
EXEC(@.OPENQUERY+@.TSQL)
GO
However if I run this second stored procedure I am met with the error
message "MSDTC on server 'SERVER_NAME' is unavailable".
Both these stored procedures were executed on my PC (win 2K) using a local
SQL Server 2000 instance and a localo access database. As such I do not
understand why I should require MSDTC for the second procedure and not for
the first.
Any explanation would be extremely useful.
Also can MSDTC be installed on Win 2K and if so where can I get it from?
Thanks
Tom
Have just dicovered that I didn't have DTC running (had been looking for a
service called MSDTC as defined in the error message)
As such this now works as expected for both procedures, but if anyone knows
why it worked without DTC for the first procedure and not the second I would
be interested in an explanation.
"TomPearson" wrote:

> Hi
> I have two stored procedures that use a linked server to access an Access
> database from SQL Server 2000.
> The first takes no input:
> CREATE PROCEDURE GetModules
> AS
> INSERT INTO datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
> UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime) SELECT * FROM
> OPENQUERY(ProjAForcBCase0,'select * from datCase')
> GO
> This procedure runs fine and does exactly as expected.
> The second parameterises the linked server name (the idea being that these
> will be created dynamically in code) so that it can be passed in to the
> stored procedure.
> CREATE PROCEDURE GetModulesDynamic
> @.LinkedServer nvarchar(4000)
> AS
> DECLARE @.TSQL nvarchar(4000)
> DECLARE @.OPENQUERY nvarchar(4000)
> SET @.OPENQUERY = 'SELECT * FROM OPENQUERY('+ @.LinkedServer + ','''
> SET @.TSQL = 'select * from datCase'')'
> Insert into datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
> UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime)
> EXEC(@.OPENQUERY+@.TSQL)
> GO
> However if I run this second stored procedure I am met with the error
> message "MSDTC on server 'SERVER_NAME' is unavailable".
> Both these stored procedures were executed on my PC (win 2K) using a local
> SQL Server 2000 instance and a localo access database. As such I do not
> understand why I should require MSDTC for the second procedure and not for
> the first.
> Any explanation would be extremely useful.
> Also can MSDTC be installed on Win 2K and if so where can I get it from?
> Thanks
> Tom
|||Most likely because in the second procedure, you used an
insert exec which would promote the transaction to a
distributed transaction. The first procedure used an insert
select so was a local transaction.
-Sue
On Mon, 31 Jan 2005 07:45:03 -0800, "TomPearson"
<TomPearson@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Have just dicovered that I didn't have DTC running (had been looking for a
>service called MSDTC as defined in the error message)
>As such this now works as expected for both procedures, but if anyone knows
>why it worked without DTC for the first procedure and not the second I would
>be interested in an explanation.
>"TomPearson" wrote:

MSDTC on server 'SERVER_NAME' is unavailable

Hi
I have two stored procedures that use a linked server to access an Access
database from SQL Server 2000.
The first takes no input:
CREATE PROCEDURE GetModules
AS
INSERT INTO datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime) SELECT * FROM
OPENQUERY(ProjAForcBCase0,'select * from datCase')
GO
This procedure runs fine and does exactly as expected.
The second parameterises the linked server name (the idea being that these
will be created dynamically in code) so that it can be passed in to the
stored procedure.
CREATE PROCEDURE GetModulesDynamic
@.LinkedServer nvarchar(4000)
AS
DECLARE @.TSQL nvarchar(4000)
DECLARE @.OPENQUERY nvarchar(4000)
SET @.OPENQUERY = 'SELECT * FROM OPENQUERY('+ @.LinkedServer + ','''
SET @.TSQL = 'select * from datCase'')'
Insert into datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime)
EXEC(@.OPENQUERY+@.TSQL)
GO
However if I run this second stored procedure I am met with the error
message "MSDTC on server 'SERVER_NAME' is unavailable".
Both these stored procedures were executed on my PC (win 2K) using a local
SQL Server 2000 instance and a localo access database. As such I do not
understand why I should require MSDTC for the second procedure and not for
the first.
Any explanation would be extremely useful.
Also can MSDTC be installed on Win 2K and if so where can I get it from?
Thanks
TomHave just dicovered that I didn't have DTC running (had been looking for a
service called MSDTC as defined in the error message)
As such this now works as expected for both procedures, but if anyone knows
why it worked without DTC for the first procedure and not the second I would
be interested in an explanation.
"TomPearson" wrote:
> Hi
> I have two stored procedures that use a linked server to access an Access
> database from SQL Server 2000.
> The first takes no input:
> CREATE PROCEDURE GetModules
> AS
> INSERT INTO datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
> UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime) SELECT * FROM
> OPENQUERY(ProjAForcBCase0,'select * from datCase')
> GO
> This procedure runs fine and does exactly as expected.
> The second parameterises the linked server name (the idea being that these
> will be created dynamically in code) so that it can be passed in to the
> stored procedure.
> CREATE PROCEDURE GetModulesDynamic
> @.LinkedServer nvarchar(4000)
> AS
> DECLARE @.TSQL nvarchar(4000)
> DECLARE @.OPENQUERY nvarchar(4000)
> SET @.OPENQUERY = 'SELECT * FROM OPENQUERY('+ @.LinkedServer + ','''
> SET @.TSQL = 'select * from datCase'')'
> Insert into datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
> UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime)
> EXEC(@.OPENQUERY+@.TSQL)
> GO
> However if I run this second stored procedure I am met with the error
> message "MSDTC on server 'SERVER_NAME' is unavailable".
> Both these stored procedures were executed on my PC (win 2K) using a local
> SQL Server 2000 instance and a localo access database. As such I do not
> understand why I should require MSDTC for the second procedure and not for
> the first.
> Any explanation would be extremely useful.
> Also can MSDTC be installed on Win 2K and if so where can I get it from?
> Thanks
> Tom|||Most likely because in the second procedure, you used an
insert exec which would promote the transaction to a
distributed transaction. The first procedure used an insert
select so was a local transaction.
-Sue
On Mon, 31 Jan 2005 07:45:03 -0800, "TomPearson"
<TomPearson@.discussions.microsoft.com> wrote:
>Have just dicovered that I didn't have DTC running (had been looking for a
>service called MSDTC as defined in the error message)
>As such this now works as expected for both procedures, but if anyone knows
>why it worked without DTC for the first procedure and not the second I would
>be interested in an explanation.
>"TomPearson" wrote:
>> Hi
>> I have two stored procedures that use a linked server to access an Access
>> database from SQL Server 2000.
>> The first takes no input:
>> CREATE PROCEDURE GetModules
>> AS
>> INSERT INTO datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
>> UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime) SELECT * FROM
>> OPENQUERY(ProjAForcBCase0,'select * from datCase')
>> GO
>> This procedure runs fine and does exactly as expected.
>> The second parameterises the linked server name (the idea being that these
>> will be created dynamically in code) so that it can be passed in to the
>> stored procedure.
>> CREATE PROCEDURE GetModulesDynamic
>> @.LinkedServer nvarchar(4000)
>> AS
>> DECLARE @.TSQL nvarchar(4000)
>> DECLARE @.OPENQUERY nvarchar(4000)
>> SET @.OPENQUERY = 'SELECT * FROM OPENQUERY('+ @.LinkedServer + ','''
>> SET @.TSQL = 'select * from datCase'')'
>> Insert into datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
>> UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime)
>> EXEC(@.OPENQUERY+@.TSQL)
>> GO
>> However if I run this second stored procedure I am met with the error
>> message "MSDTC on server 'SERVER_NAME' is unavailable".
>> Both these stored procedures were executed on my PC (win 2K) using a local
>> SQL Server 2000 instance and a localo access database. As such I do not
>> understand why I should require MSDTC for the second procedure and not for
>> the first.
>> Any explanation would be extremely useful.
>> Also can MSDTC be installed on Win 2K and if so where can I get it from?
>> Thanks
>> Tom