I have two computers both running Windows Server 2003 and SQL Server 2000. I
have a trigger on a table located on the first computer which includes a line
of code that inserts into a table on the second computer. The two computers
are set up as linked servers (without loopback).
The code used to work so that when you insert into the table of the first
computer, the trigger ran properly and inserted into the second computer.
However, after I installed SP3 for SQL Server 2000 to fix another bug, the
trigger stopped working properly. Every time i try to run the code, it
generates this error message:
Server: Msg 7391, Level 16, State 1, Procedure UserSongAddition, Line 20
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].
I have not touched any of the code, so something in terms of the setup must
have changed so that it doesn't work anymore, how ever I have no idea what is
wrong.
After reading the many threads about this I have checked the following items:
1) DTC is running properly on both computers
2) Setup within the component services with Network DTC Access and
NetworkService for log on Account are all correct
3) I call SET XACT_ABORT ON before the insert statement
4) The servers are not on a cluster
5) The server names are correct based on checking with @.@.servername
6) The servers can talk to each other by name and not just IP Addresses.
7) The line of code that fails in the trigger works properly when it is run
on the second computer
8) The line of code that fails in the trigger also works properly when
copied and pasted into a stored procedure of the first computer and run over
the linked server
9) Lastly, the line of code is an insert call, but when I change it to a
select call within the trigger for testing, it also produces the same error.
So essentially I can run the code as a stored procedure but not as a trigger.
If you need any other information, please let me know. Please help me fix
this problem. Thank you.
See if any of these links help. It may be a problem with MS DTC being
disabled in WIN 2003 by default.
http://support.microsoft.com/default...b;en-us;817064
http://support.microsoft.com/?id=827805
http://support.microsoft.com/default.aspx?kbid=329332
"Zwyatt" wrote:
> I have two computers both running Windows Server 2003 and SQL Server 2000. I
> have a trigger on a table located on the first computer which includes a line
> of code that inserts into a table on the second computer. The two computers
> are set up as linked servers (without loopback).
> The code used to work so that when you insert into the table of the first
> computer, the trigger ran properly and inserted into the second computer.
> However, after I installed SP3 for SQL Server 2000 to fix another bug, the
> trigger stopped working properly. Every time i try to run the code, it
> generates this error message:
> Server: Msg 7391, Level 16, State 1, Procedure UserSongAddition, Line 20
> 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].
> I have not touched any of the code, so something in terms of the setup must
> have changed so that it doesn't work anymore, how ever I have no idea what is
> wrong.
> After reading the many threads about this I have checked the following items:
> 1) DTC is running properly on both computers
> 2) Setup within the component services with Network DTC Access and
> NetworkService for log on Account are all correct
> 3) I call SET XACT_ABORT ON before the insert statement
> 4) The servers are not on a cluster
> 5) The server names are correct based on checking with @.@.servername
> 6) The servers can talk to each other by name and not just IP Addresses.
> 7) The line of code that fails in the trigger works properly when it is run
> on the second computer
> 8) The line of code that fails in the trigger also works properly when
> copied and pasted into a stored procedure of the first computer and run over
> the linked server
> 9) Lastly, the line of code is an insert call, but when I change it to a
> select call within the trigger for testing, it also produces the same error.
> So essentially I can run the code as a stored procedure but not as a trigger.
> If you need any other information, please let me know. Please help me fix
> this problem. Thank you.
Showing posts with label error. Show all posts
Showing posts with label error. Show all posts
Friday, March 30, 2012
linked server problem with error message 7391
I have two computers both running Windows Server 2003 and SQL Server 2000.
I
have a trigger on a table located on the first computer which includes a lin
e
of code that inserts into a table on the second computer. The two computers
are set up as linked servers (without loopback).
The code used to work so that when you insert into the table of the first
computer, the trigger ran properly and inserted into the second computer.
However, after I installed SP3 for SQL Server 2000 to fix another bug, the
trigger stopped working properly. Every time i try to run the code, it
generates this error message:
Server: Msg 7391, Level 16, State 1, Procedure UserSongAddition, Line 20
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].
I have not touched any of the code, so something in terms of the setup must
have changed so that it doesn't work anymore, how ever I have no idea what i
s
wrong.
After reading the many threads about this I have checked the following items
:
1) DTC is running properly on both computers
2) Setup within the component services with Network DTC Access and
NetworkService for log on Account are all correct
3) I call SET XACT_ABORT ON before the insert statement
4) The servers are not on a cluster
5) The server names are correct based on checking with @.@.servername
6) The servers can talk to each other by name and not just IP Addresses.
7) The line of code that fails in the trigger works properly when it is run
on the second computer
8) The line of code that fails in the trigger also works properly when
copied and pasted into a stored procedure of the first computer and run over
the linked server
9) Lastly, the line of code is an insert call, but when I change it to a
select call within the trigger for testing, it also produces the same error.
So essentially I can run the code as a stored procedure but not as a trigger
.
If you need any other information, please let me know. Please help me fix
this problem. Thank you.See if any of these links help. It may be a problem with MS DTC being
disabled in WIN 2003 by default.
http://support.microsoft.com/defaul...kb;en-us;817064
http://support.microsoft.com/?id=827805
http://support.microsoft.com/default.aspx?kbid=329332
"Zwyatt" wrote:
> I have two computers both running Windows Server 2003 and SQL Server 2000.
I
> have a trigger on a table located on the first computer which includes a l
ine
> of code that inserts into a table on the second computer. The two compute
rs
> are set up as linked servers (without loopback).
> The code used to work so that when you insert into the table of the first
> computer, the trigger ran properly and inserted into the second computer.
> However, after I installed SP3 for SQL Server 2000 to fix another bug, the
> trigger stopped working properly. Every time i try to run the code, it
> generates this error message:
> Server: Msg 7391, Level 16, State 1, Procedure UserSongAddition, Line 20
> 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 th
e
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> I have not touched any of the code, so something in terms of the setup mus
t
> have changed so that it doesn't work anymore, how ever I have no idea what
is
> wrong.
> After reading the many threads about this I have checked the following ite
ms:
> 1) DTC is running properly on both computers
> 2) Setup within the component services with Network DTC Access and
> NetworkService for log on Account are all correct
> 3) I call SET XACT_ABORT ON before the insert statement
> 4) The servers are not on a cluster
> 5) The server names are correct based on checking with @.@.servername
> 6) The servers can talk to each other by name and not just IP Addresses.
> 7) The line of code that fails in the trigger works properly when it is r
un
> on the second computer
> 8) The line of code that fails in the trigger also works properly when
> copied and pasted into a stored procedure of the first computer and run ov
er
> the linked server
> 9) Lastly, the line of code is an insert call, but when I change it to a
> select call within the trigger for testing, it also produces the same erro
r.
> So essentially I can run the code as a stored procedure but not as a trigg
er.
> If you need any other information, please let me know. Please help me fix
> this problem. Thank you.
I
have a trigger on a table located on the first computer which includes a lin
e
of code that inserts into a table on the second computer. The two computers
are set up as linked servers (without loopback).
The code used to work so that when you insert into the table of the first
computer, the trigger ran properly and inserted into the second computer.
However, after I installed SP3 for SQL Server 2000 to fix another bug, the
trigger stopped working properly. Every time i try to run the code, it
generates this error message:
Server: Msg 7391, Level 16, State 1, Procedure UserSongAddition, Line 20
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].
I have not touched any of the code, so something in terms of the setup must
have changed so that it doesn't work anymore, how ever I have no idea what i
s
wrong.
After reading the many threads about this I have checked the following items
:
1) DTC is running properly on both computers
2) Setup within the component services with Network DTC Access and
NetworkService for log on Account are all correct
3) I call SET XACT_ABORT ON before the insert statement
4) The servers are not on a cluster
5) The server names are correct based on checking with @.@.servername
6) The servers can talk to each other by name and not just IP Addresses.
7) The line of code that fails in the trigger works properly when it is run
on the second computer
8) The line of code that fails in the trigger also works properly when
copied and pasted into a stored procedure of the first computer and run over
the linked server
9) Lastly, the line of code is an insert call, but when I change it to a
select call within the trigger for testing, it also produces the same error.
So essentially I can run the code as a stored procedure but not as a trigger
.
If you need any other information, please let me know. Please help me fix
this problem. Thank you.See if any of these links help. It may be a problem with MS DTC being
disabled in WIN 2003 by default.
http://support.microsoft.com/defaul...kb;en-us;817064
http://support.microsoft.com/?id=827805
http://support.microsoft.com/default.aspx?kbid=329332
"Zwyatt" wrote:
> I have two computers both running Windows Server 2003 and SQL Server 2000.
I
> have a trigger on a table located on the first computer which includes a l
ine
> of code that inserts into a table on the second computer. The two compute
rs
> are set up as linked servers (without loopback).
> The code used to work so that when you insert into the table of the first
> computer, the trigger ran properly and inserted into the second computer.
> However, after I installed SP3 for SQL Server 2000 to fix another bug, the
> trigger stopped working properly. Every time i try to run the code, it
> generates this error message:
> Server: Msg 7391, Level 16, State 1, Procedure UserSongAddition, Line 20
> 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 th
e
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> I have not touched any of the code, so something in terms of the setup mus
t
> have changed so that it doesn't work anymore, how ever I have no idea what
is
> wrong.
> After reading the many threads about this I have checked the following ite
ms:
> 1) DTC is running properly on both computers
> 2) Setup within the component services with Network DTC Access and
> NetworkService for log on Account are all correct
> 3) I call SET XACT_ABORT ON before the insert statement
> 4) The servers are not on a cluster
> 5) The server names are correct based on checking with @.@.servername
> 6) The servers can talk to each other by name and not just IP Addresses.
> 7) The line of code that fails in the trigger works properly when it is r
un
> on the second computer
> 8) The line of code that fails in the trigger also works properly when
> copied and pasted into a stored procedure of the first computer and run ov
er
> the linked server
> 9) Lastly, the line of code is an insert call, but when I change it to a
> select call within the trigger for testing, it also produces the same erro
r.
> So essentially I can run the code as a stored procedure but not as a trigg
er.
> If you need any other information, please let me know. Please help me fix
> this problem. Thank you.
Monday, March 26, 2012
linked server permissions
I have a query on a web site that I'm trying to run. I get this error: Access
to the remote server is denied because no login-mapping exists.
I can run the query as myself (sysadmin). The user that has problems only
has db_datareader and db_datawriter permissions. Does this user need
different permissions?
Thanks,
Dan D.
If you are using linked server, check this out....
http://databasejournal.com/features/...le.php/3116011
Thanks
GYK
"Dan D." wrote:
> I have a query on a web site that I'm trying to run. I get this error: Access
> to the remote server is denied because no login-mapping exists.
> I can run the query as myself (sysadmin). The user that has problems only
> has db_datareader and db_datawriter permissions. Does this user need
> different permissions?
> Thanks,
> --
> Dan D.
|||Thanks.
"GYK" wrote:
[vbcol=seagreen]
> If you are using linked server, check this out....
> http://databasejournal.com/features/...le.php/3116011
> Thanks
> GYK
> "Dan D." wrote:
to the remote server is denied because no login-mapping exists.
I can run the query as myself (sysadmin). The user that has problems only
has db_datareader and db_datawriter permissions. Does this user need
different permissions?
Thanks,
Dan D.
If you are using linked server, check this out....
http://databasejournal.com/features/...le.php/3116011
Thanks
GYK
"Dan D." wrote:
> I have a query on a web site that I'm trying to run. I get this error: Access
> to the remote server is denied because no login-mapping exists.
> I can run the query as myself (sysadmin). The user that has problems only
> has db_datareader and db_datawriter permissions. Does this user need
> different permissions?
> Thanks,
> --
> Dan D.
|||Thanks.
"GYK" wrote:
[vbcol=seagreen]
> If you are using linked server, check this out....
> http://databasejournal.com/features/...le.php/3116011
> Thanks
> GYK
> "Dan D." wrote:
linked server permissions
I have a query on a web site that I'm trying to run. I get this error: Acces
s
to the remote server is denied because no login-mapping exists.
I can run the query as myself (sysadmin). The user that has problems only
has db_datareader and db_datawriter permissions. Does this user need
different permissions?
Thanks,
--
Dan D.If you are using linked server, check this out....
http://databasejournal.com/features...cle.php/3116011
Thanks
GYK
"Dan D." wrote:
> I have a query on a web site that I'm trying to run. I get this error: Acc
ess
> to the remote server is denied because no login-mapping exists.
> I can run the query as myself (sysadmin). The user that has problems only
> has db_datareader and db_datawriter permissions. Does this user need
> different permissions?
> Thanks,
> --
> Dan D.|||Thanks.
"GYK" wrote:
[vbcol=seagreen]
> If you are using linked server, check this out....
> http://databasejournal.com/features...cle.php/3116011
> Thanks
> GYK
> "Dan D." wrote:
>
s
to the remote server is denied because no login-mapping exists.
I can run the query as myself (sysadmin). The user that has problems only
has db_datareader and db_datawriter permissions. Does this user need
different permissions?
Thanks,
--
Dan D.If you are using linked server, check this out....
http://databasejournal.com/features...cle.php/3116011
Thanks
GYK
"Dan D." wrote:
> I have a query on a web site that I'm trying to run. I get this error: Acc
ess
> to the remote server is denied because no login-mapping exists.
> I can run the query as myself (sysadmin). The user that has problems only
> has db_datareader and db_datawriter permissions. Does this user need
> different permissions?
> Thanks,
> --
> Dan D.|||Thanks.
"GYK" wrote:
[vbcol=seagreen]
> If you are using linked server, check this out....
> http://databasejournal.com/features...cle.php/3116011
> Thanks
> GYK
> "Dan D." wrote:
>
linked server permissions
I have a query on a web site that I'm trying to run. I get this error: Access
to the remote server is denied because no login-mapping exists.
I can run the query as myself (sysadmin). The user that has problems only
has db_datareader and db_datawriter permissions. Does this user need
different permissions?
Thanks,
--
Dan D.If you are using linked server, check this out....
http://databasejournal.com/features/mssql/article.php/3116011
Thanks
GYK
"Dan D." wrote:
> I have a query on a web site that I'm trying to run. I get this error: Access
> to the remote server is denied because no login-mapping exists.
> I can run the query as myself (sysadmin). The user that has problems only
> has db_datareader and db_datawriter permissions. Does this user need
> different permissions?
> Thanks,
> --
> Dan D.|||Thanks.
"GYK" wrote:
> If you are using linked server, check this out....
> http://databasejournal.com/features/mssql/article.php/3116011
> Thanks
> GYK
> "Dan D." wrote:
> > I have a query on a web site that I'm trying to run. I get this error: Access
> > to the remote server is denied because no login-mapping exists.
> >
> > I can run the query as myself (sysadmin). The user that has problems only
> > has db_datareader and db_datawriter permissions. Does this user need
> > different permissions?
> >
> > Thanks,
> > --
> > Dan D.
to the remote server is denied because no login-mapping exists.
I can run the query as myself (sysadmin). The user that has problems only
has db_datareader and db_datawriter permissions. Does this user need
different permissions?
Thanks,
--
Dan D.If you are using linked server, check this out....
http://databasejournal.com/features/mssql/article.php/3116011
Thanks
GYK
"Dan D." wrote:
> I have a query on a web site that I'm trying to run. I get this error: Access
> to the remote server is denied because no login-mapping exists.
> I can run the query as myself (sysadmin). The user that has problems only
> has db_datareader and db_datawriter permissions. Does this user need
> different permissions?
> Thanks,
> --
> Dan D.|||Thanks.
"GYK" wrote:
> If you are using linked server, check this out....
> http://databasejournal.com/features/mssql/article.php/3116011
> Thanks
> GYK
> "Dan D." wrote:
> > I have a query on a web site that I'm trying to run. I get this error: Access
> > to the remote server is denied because no login-mapping exists.
> >
> > I can run the query as myself (sysadmin). The user that has problems only
> > has db_datareader and db_datawriter permissions. Does this user need
> > different permissions?
> >
> > Thanks,
> > --
> > Dan D.
Linked Server oracle 0x80004005 error
Linked Server oracle 0x80004005 error
Has anyone gotten around the .....
OLE DB provider 'OraOLEDB.Oracle' reported an error.
[OLE/DB provider returned message: Unspecified error]
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
returned 0x80004005: ]. ...
error when creating a linked server to oracle using the oracle drivers ?
Have no problem with the linked server ms oracle driver other than the fact
that it is SLOW !!!! A query returns in 1min30sec that is instant in sql
plus.
Any ideas greatly appreciated ! Thanks, Steve.Hi
0x80004005 = Access Denied. Check that your credentials are correct that you
are supplying. I have also seen this when there are not enough connections
available on the Oracle side to connect.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"saustin99" <saustin99@.discussions.microsoft.com> wrote in message
news:D119F7DF-ACC9-4019-8087-6672A956F633@.microsoft.com...
> Linked Server oracle 0x80004005 error
> Has anyone gotten around the .....
> OLE DB provider 'OraOLEDB.Oracle' reported an error.
> [OLE/DB provider returned message: Unspecified error]
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> returned 0x80004005: ]. ...
> error when creating a linked server to oracle using the oracle drivers ?
> Have no problem with the linked server ms oracle driver other than the
fact
> that it is SLOW !!!! A query returns in 1min30sec that is instant in sql
> plus.
> Any ideas greatly appreciated ! Thanks, Steve.
>|||Note that linked servers are dangerous if you are using the four part naming
method. It is very very very easy to end up pulling the whole table over and
the where clause being processed locally rather than on Oracle. If you are
going to use linked server then use the OpenQuery method instead of 4 part
naming.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
.
"saustin99" <saustin99@.discussions.microsoft.com> wrote in message
news:D119F7DF-ACC9-4019-8087-6672A956F633@.microsoft.com...
> Linked Server oracle 0x80004005 error
> Has anyone gotten around the .....
> OLE DB provider 'OraOLEDB.Oracle' reported an error.
> [OLE/DB provider returned message: Unspecified error]
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> returned 0x80004005: ]. ...
> error when creating a linked server to oracle using the oracle drivers ?
> Have no problem with the linked server ms oracle driver other than the
> fact
> that it is SLOW !!!! A query returns in 1min30sec that is instant in sql
> plus.
> Any ideas greatly appreciated ! Thanks, Steve.
>|||Bruce,
That's an interesting point you raise. I use linked servers with 4 part
names heavily. They are all MS SQL 2000 servers and I have no problems
at all with this. Are you suggesting this is a problem with all linked
servers or specifically Oracle?
Having said that I want the report server doing the processing rather
than loading the linked servers.
Chris
Bruce L-C [MVP] wrote:
> Note that linked servers are dangerous if you are using the four part
> naming method. It is very very very easy to end up pulling the whole
> table over and the where clause being processed locally rather than
> on Oracle. If you are going to use linked server then use the
> OpenQuery method instead of 4 part naming.|||I am using linked servers against Sybase but it makes no difference. It
could be that your tables are small enough that you haven't seen it. Another
SQL MVP did some testing for me. This gets better in Yukon. But he saw this
a whole lot. I suggest you take some of your queries and do get the query
plan for them and see what is happening. I would be surprised if some of
them aren't bringing all the data over.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Chris McGuigan" <chris.mcguigan@.zycko.com> wrote in message
news:uHCpg%23JIFHA.1172@.TK2MSFTNGP12.phx.gbl...
> Bruce,
> That's an interesting point you raise. I use linked servers with 4 part
> names heavily. They are all MS SQL 2000 servers and I have no problems
> at all with this. Are you suggesting this is a problem with all linked
> servers or specifically Oracle?
> Having said that I want the report server doing the processing rather
> than loading the linked servers.
>
> Chris
> Bruce L-C [MVP] wrote:
>> Note that linked servers are dangerous if you are using the four part
>> naming method. It is very very very easy to end up pulling the whole
>> table over and the where clause being processed locally rather than
>> on Oracle. If you are going to use linked server then use the
>> OpenQuery method instead of 4 part naming.
>|||Grutzi Mike,
Can connect to the oracle server with the dsn from ms
access on the server with no problems. Can also see all the tables and views
for this linked server from the enterprise manager console.
Used these instructions to set it up.How to use Microsoft SQL Analyzer with
the Oracle Provider for OLE DB
---
Configuration: ============== Microsoft SQL Server 2000 SP 1 or 2 Microsoft
Windows 2000 Server SP 2 Oracle Provider for OLE DB 8.1.7.3.0 Oracle Client
8.1.7.3.0 Oracle Database 8.1.7.3.0 Instructions to Setup the Linked Server
Connection: =================================================== 1. Open SQL
Server Enterprise Manager 2. Click on the + to expand the tree for the
database server. You will need to expand it three times. Note: If the
SQL Server does not appear here, you will need to right click and go
through the Wizard for SQL Server Registration. - The first level of
the tree is called Microsoft Sql Server, The second level of the tree is
called SQL Server Group, and the third level of the tree is your SQL
Server Database. For these instructions we will call our SQL Server
Database, 'Scott'. 3. Under the Scott Database, click on the + to expand
the tree control for 'Security' 4. Under Security, right click on Linked
Servers and select "New Linked Server" 5. In the Linked Server Properties
do the following: a. Type in a Linked Server Name, for this example we
will call it 'Tiger' b. Under Server Type select Other data source
- For the provider name select Oracle Provider for OLE DB. c. In the
Data Source field, type in the Oracle Service name (SQL Net Alias)
d. Leave the Product Name and Provider String blank. e. Click on the
Provider Options button. - Check the check boxes for "Dynamic
Parameters" and "Allow InProcess" f. Click Apply & then click OK. - The
Provider Options dialog closes. 6. Go to the "Security" tab in the Linked
Server Properties. a. Select the radio button "Be made using this security
context" at the bottom of the box. b. Type in the User ID (your
Oracle login), ie. Scott in the "Remote login" field and your
Password, ie. Tiger in the "With Password" field. 7. Go to the "Server
Options" tab in the Linked Server Properties. a. While leaving the
default selections selected, also check the checkbox for "Collation
Compatible". b. Click OK This will create your Linked Server named Tiger.
Test the Linked Connection ================================== To test the
Linked Server, open up Microsoft Query Analyzer (It is found under the
Microsoft SQL Server menu option). 1. Select your SQL Server in the Connect
to SQL Server box. a. Since we have the SQL Server installed on the same
machine, we select (local). b. Select the appropriate Connect using
option. This is determined when you Installed/Created your SQL Server
database. For our example here we use Windows Authentication. And click
on the Ok button. 2. In the Query screen you can type a query. The
query will follow this format: SELECT * FROM <Linked Server Name>..<Oracle
user name in all CAPS>.<Oracle Table Name in all CAPS> IE. SELECT * FROM
TIGER..SCOTT.EMP 3. If you have the default scott/tiger schema installed in
your Oracle database you can use the following query: SELECT * FROM
TIGER..SCOTT.EMP 4. Type it into the Query box. 5. Click on the Green Arrow
to run the query. 6. You should see the contents of the Emp table in the
Grid.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> 0x80004005 = Access Denied. Check that your credentials are correct that you
> are supplying. I have also seen this when there are not enough connections
> available on the Oracle side to connect.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> news:D119F7DF-ACC9-4019-8087-6672A956F633@.microsoft.com...
> > Linked Server oracle 0x80004005 error
> >
> > Has anyone gotten around the .....
> > OLE DB provider 'OraOLEDB.Oracle' reported an error.
> > [OLE/DB provider returned message: Unspecified error]
> > OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> > returned 0x80004005: ]. ...
> > error when creating a linked server to oracle using the oracle drivers ?
> > Have no problem with the linked server ms oracle driver other than the
> fact
> > that it is SLOW !!!! A query returns in 1min30sec that is instant in sql
> > plus.
> >
> > Any ideas greatly appreciated ! Thanks, Steve.
> >
>
>|||Hi Bruce,
Thank you so much for the reply. Created the VMFO linked server
again using the Oracle driver and tried the openquery method and... still
got the ...
SELECT * FROM openquery(VMFO, 'SELECT * FROM SYSADM.REQUEST_FOR_QUOTE')
OLE DB provider 'OraOLEDB.Oracle' reported an error.
[OLE/DB provider returned message: ]
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
returned 0x80004005: ].
"Bruce L-C [MVP]" wrote:
> Note that linked servers are dangerous if you are using the four part naming
> method. It is very very very easy to end up pulling the whole table over and
> the where clause being processed locally rather than on Oracle. If you are
> going to use linked server then use the OpenQuery method instead of 4 part
> naming.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> ..
> "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> news:D119F7DF-ACC9-4019-8087-6672A956F633@.microsoft.com...
> > Linked Server oracle 0x80004005 error
> >
> > Has anyone gotten around the .....
> > OLE DB provider 'OraOLEDB.Oracle' reported an error.
> > [OLE/DB provider returned message: Unspecified error]
> > OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> > returned 0x80004005: ]. ...
> > error when creating a linked server to oracle using the oracle drivers ?
> > Have no problem with the linked server ms oracle driver other than the
> > fact
> > that it is SLOW !!!! A query returns in 1min30sec that is instant in sql
> > plus.
> >
> > Any ideas greatly appreciated ! Thanks, Steve.
> >
>
>|||Go to your linked server in Enterprise Manager. Right mouse click,
properties. Go to the Security tab. Do you have a username and password set
for it?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"saustin99" <saustin99@.discussions.microsoft.com> wrote in message
news:B0507C65-F50B-431D-AD66-E01381CB9112@.microsoft.com...
> Hi Bruce,
> Thank you so much for the reply. Created the VMFO linked
server
> again using the Oracle driver and tried the openquery method and... still
> got the ...
> SELECT * FROM openquery(VMFO, 'SELECT * FROM SYSADM.REQUEST_FOR_QUOTE')
> OLE DB provider 'OraOLEDB.Oracle' reported an error.
> [OLE/DB provider returned message: ]
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> returned 0x80004005: ].
> "Bruce L-C [MVP]" wrote:
> > Note that linked servers are dangerous if you are using the four part
naming
> > method. It is very very very easy to end up pulling the whole table over
and
> > the where clause being processed locally rather than on Oracle. If you
are
> > going to use linked server then use the OpenQuery method instead of 4
part
> > naming.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> > ..
> > "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> > news:D119F7DF-ACC9-4019-8087-6672A956F633@.microsoft.com...
> > > Linked Server oracle 0x80004005 error
> > >
> > > Has anyone gotten around the .....
> > > OLE DB provider 'OraOLEDB.Oracle' reported an error.
> > > [OLE/DB provider returned message: Unspecified error]
> > > OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> > > returned 0x80004005: ]. ...
> > > error when creating a linked server to oracle using the oracle drivers
?
> > > Have no problem with the linked server ms oracle driver other than the
> > > fact
> > > that it is SLOW !!!! A query returns in 1min30sec that is instant in
sql
> > > plus.
> > >
> > > Any ideas greatly appreciated ! Thanks, Steve.
> > >
> >
> >
> >|||Hi Bruce,
Thanks for your reply. Yes, there is a user/pw set. Also, all
the tables and views for the linked server appear perfectly. Do not think
this would be possible without that. Steve.
"Bruce L-C [MVP]" wrote:
> Go to your linked server in Enterprise Manager. Right mouse click,
> properties. Go to the Security tab. Do you have a username and password set
> for it?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> news:B0507C65-F50B-431D-AD66-E01381CB9112@.microsoft.com...
> > Hi Bruce,
> > Thank you so much for the reply. Created the VMFO linked
> server
> > again using the Oracle driver and tried the openquery method and... still
> > got the ...
> >
> > SELECT * FROM openquery(VMFO, 'SELECT * FROM SYSADM.REQUEST_FOR_QUOTE')
> >
> > OLE DB provider 'OraOLEDB.Oracle' reported an error.
> > [OLE/DB provider returned message: ]
> > OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> > returned 0x80004005: ].
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > Note that linked servers are dangerous if you are using the four part
> naming
> > > method. It is very very very easy to end up pulling the whole table over
> and
> > > the where clause being processed locally rather than on Oracle. If you
> are
> > > going to use linked server then use the OpenQuery method instead of 4
> part
> > > naming.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > > ..
> > > "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> > > news:D119F7DF-ACC9-4019-8087-6672A956F633@.microsoft.com...
> > > > Linked Server oracle 0x80004005 error
> > > >
> > > > Has anyone gotten around the .....
> > > > OLE DB provider 'OraOLEDB.Oracle' reported an error.
> > > > [OLE/DB provider returned message: Unspecified error]
> > > > OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> > > > returned 0x80004005: ]. ...
> > > > error when creating a linked server to oracle using the oracle drivers
> ?
> > > > Have no problem with the linked server ms oracle driver other than the
> > > > fact
> > > > that it is SLOW !!!! A query returns in 1min30sec that is instant in
> sql
> > > > plus.
> > > >
> > > > Any ideas greatly appreciated ! Thanks, Steve.
> > > >
> > >
> > >
> > >
>
>sql
Has anyone gotten around the .....
OLE DB provider 'OraOLEDB.Oracle' reported an error.
[OLE/DB provider returned message: Unspecified error]
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
returned 0x80004005: ]. ...
error when creating a linked server to oracle using the oracle drivers ?
Have no problem with the linked server ms oracle driver other than the fact
that it is SLOW !!!! A query returns in 1min30sec that is instant in sql
plus.
Any ideas greatly appreciated ! Thanks, Steve.Hi
0x80004005 = Access Denied. Check that your credentials are correct that you
are supplying. I have also seen this when there are not enough connections
available on the Oracle side to connect.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"saustin99" <saustin99@.discussions.microsoft.com> wrote in message
news:D119F7DF-ACC9-4019-8087-6672A956F633@.microsoft.com...
> Linked Server oracle 0x80004005 error
> Has anyone gotten around the .....
> OLE DB provider 'OraOLEDB.Oracle' reported an error.
> [OLE/DB provider returned message: Unspecified error]
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> returned 0x80004005: ]. ...
> error when creating a linked server to oracle using the oracle drivers ?
> Have no problem with the linked server ms oracle driver other than the
fact
> that it is SLOW !!!! A query returns in 1min30sec that is instant in sql
> plus.
> Any ideas greatly appreciated ! Thanks, Steve.
>|||Note that linked servers are dangerous if you are using the four part naming
method. It is very very very easy to end up pulling the whole table over and
the where clause being processed locally rather than on Oracle. If you are
going to use linked server then use the OpenQuery method instead of 4 part
naming.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
.
"saustin99" <saustin99@.discussions.microsoft.com> wrote in message
news:D119F7DF-ACC9-4019-8087-6672A956F633@.microsoft.com...
> Linked Server oracle 0x80004005 error
> Has anyone gotten around the .....
> OLE DB provider 'OraOLEDB.Oracle' reported an error.
> [OLE/DB provider returned message: Unspecified error]
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> returned 0x80004005: ]. ...
> error when creating a linked server to oracle using the oracle drivers ?
> Have no problem with the linked server ms oracle driver other than the
> fact
> that it is SLOW !!!! A query returns in 1min30sec that is instant in sql
> plus.
> Any ideas greatly appreciated ! Thanks, Steve.
>|||Bruce,
That's an interesting point you raise. I use linked servers with 4 part
names heavily. They are all MS SQL 2000 servers and I have no problems
at all with this. Are you suggesting this is a problem with all linked
servers or specifically Oracle?
Having said that I want the report server doing the processing rather
than loading the linked servers.
Chris
Bruce L-C [MVP] wrote:
> Note that linked servers are dangerous if you are using the four part
> naming method. It is very very very easy to end up pulling the whole
> table over and the where clause being processed locally rather than
> on Oracle. If you are going to use linked server then use the
> OpenQuery method instead of 4 part naming.|||I am using linked servers against Sybase but it makes no difference. It
could be that your tables are small enough that you haven't seen it. Another
SQL MVP did some testing for me. This gets better in Yukon. But he saw this
a whole lot. I suggest you take some of your queries and do get the query
plan for them and see what is happening. I would be surprised if some of
them aren't bringing all the data over.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Chris McGuigan" <chris.mcguigan@.zycko.com> wrote in message
news:uHCpg%23JIFHA.1172@.TK2MSFTNGP12.phx.gbl...
> Bruce,
> That's an interesting point you raise. I use linked servers with 4 part
> names heavily. They are all MS SQL 2000 servers and I have no problems
> at all with this. Are you suggesting this is a problem with all linked
> servers or specifically Oracle?
> Having said that I want the report server doing the processing rather
> than loading the linked servers.
>
> Chris
> Bruce L-C [MVP] wrote:
>> Note that linked servers are dangerous if you are using the four part
>> naming method. It is very very very easy to end up pulling the whole
>> table over and the where clause being processed locally rather than
>> on Oracle. If you are going to use linked server then use the
>> OpenQuery method instead of 4 part naming.
>|||Grutzi Mike,
Can connect to the oracle server with the dsn from ms
access on the server with no problems. Can also see all the tables and views
for this linked server from the enterprise manager console.
Used these instructions to set it up.How to use Microsoft SQL Analyzer with
the Oracle Provider for OLE DB
---
Configuration: ============== Microsoft SQL Server 2000 SP 1 or 2 Microsoft
Windows 2000 Server SP 2 Oracle Provider for OLE DB 8.1.7.3.0 Oracle Client
8.1.7.3.0 Oracle Database 8.1.7.3.0 Instructions to Setup the Linked Server
Connection: =================================================== 1. Open SQL
Server Enterprise Manager 2. Click on the + to expand the tree for the
database server. You will need to expand it three times. Note: If the
SQL Server does not appear here, you will need to right click and go
through the Wizard for SQL Server Registration. - The first level of
the tree is called Microsoft Sql Server, The second level of the tree is
called SQL Server Group, and the third level of the tree is your SQL
Server Database. For these instructions we will call our SQL Server
Database, 'Scott'. 3. Under the Scott Database, click on the + to expand
the tree control for 'Security' 4. Under Security, right click on Linked
Servers and select "New Linked Server" 5. In the Linked Server Properties
do the following: a. Type in a Linked Server Name, for this example we
will call it 'Tiger' b. Under Server Type select Other data source
- For the provider name select Oracle Provider for OLE DB. c. In the
Data Source field, type in the Oracle Service name (SQL Net Alias)
d. Leave the Product Name and Provider String blank. e. Click on the
Provider Options button. - Check the check boxes for "Dynamic
Parameters" and "Allow InProcess" f. Click Apply & then click OK. - The
Provider Options dialog closes. 6. Go to the "Security" tab in the Linked
Server Properties. a. Select the radio button "Be made using this security
context" at the bottom of the box. b. Type in the User ID (your
Oracle login), ie. Scott in the "Remote login" field and your
Password, ie. Tiger in the "With Password" field. 7. Go to the "Server
Options" tab in the Linked Server Properties. a. While leaving the
default selections selected, also check the checkbox for "Collation
Compatible". b. Click OK This will create your Linked Server named Tiger.
Test the Linked Connection ================================== To test the
Linked Server, open up Microsoft Query Analyzer (It is found under the
Microsoft SQL Server menu option). 1. Select your SQL Server in the Connect
to SQL Server box. a. Since we have the SQL Server installed on the same
machine, we select (local). b. Select the appropriate Connect using
option. This is determined when you Installed/Created your SQL Server
database. For our example here we use Windows Authentication. And click
on the Ok button. 2. In the Query screen you can type a query. The
query will follow this format: SELECT * FROM <Linked Server Name>..<Oracle
user name in all CAPS>.<Oracle Table Name in all CAPS> IE. SELECT * FROM
TIGER..SCOTT.EMP 3. If you have the default scott/tiger schema installed in
your Oracle database you can use the following query: SELECT * FROM
TIGER..SCOTT.EMP 4. Type it into the Query box. 5. Click on the Green Arrow
to run the query. 6. You should see the contents of the Emp table in the
Grid.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> 0x80004005 = Access Denied. Check that your credentials are correct that you
> are supplying. I have also seen this when there are not enough connections
> available on the Oracle side to connect.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> news:D119F7DF-ACC9-4019-8087-6672A956F633@.microsoft.com...
> > Linked Server oracle 0x80004005 error
> >
> > Has anyone gotten around the .....
> > OLE DB provider 'OraOLEDB.Oracle' reported an error.
> > [OLE/DB provider returned message: Unspecified error]
> > OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> > returned 0x80004005: ]. ...
> > error when creating a linked server to oracle using the oracle drivers ?
> > Have no problem with the linked server ms oracle driver other than the
> fact
> > that it is SLOW !!!! A query returns in 1min30sec that is instant in sql
> > plus.
> >
> > Any ideas greatly appreciated ! Thanks, Steve.
> >
>
>|||Hi Bruce,
Thank you so much for the reply. Created the VMFO linked server
again using the Oracle driver and tried the openquery method and... still
got the ...
SELECT * FROM openquery(VMFO, 'SELECT * FROM SYSADM.REQUEST_FOR_QUOTE')
OLE DB provider 'OraOLEDB.Oracle' reported an error.
[OLE/DB provider returned message: ]
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
returned 0x80004005: ].
"Bruce L-C [MVP]" wrote:
> Note that linked servers are dangerous if you are using the four part naming
> method. It is very very very easy to end up pulling the whole table over and
> the where clause being processed locally rather than on Oracle. If you are
> going to use linked server then use the OpenQuery method instead of 4 part
> naming.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> ..
> "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> news:D119F7DF-ACC9-4019-8087-6672A956F633@.microsoft.com...
> > Linked Server oracle 0x80004005 error
> >
> > Has anyone gotten around the .....
> > OLE DB provider 'OraOLEDB.Oracle' reported an error.
> > [OLE/DB provider returned message: Unspecified error]
> > OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> > returned 0x80004005: ]. ...
> > error when creating a linked server to oracle using the oracle drivers ?
> > Have no problem with the linked server ms oracle driver other than the
> > fact
> > that it is SLOW !!!! A query returns in 1min30sec that is instant in sql
> > plus.
> >
> > Any ideas greatly appreciated ! Thanks, Steve.
> >
>
>|||Go to your linked server in Enterprise Manager. Right mouse click,
properties. Go to the Security tab. Do you have a username and password set
for it?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"saustin99" <saustin99@.discussions.microsoft.com> wrote in message
news:B0507C65-F50B-431D-AD66-E01381CB9112@.microsoft.com...
> Hi Bruce,
> Thank you so much for the reply. Created the VMFO linked
server
> again using the Oracle driver and tried the openquery method and... still
> got the ...
> SELECT * FROM openquery(VMFO, 'SELECT * FROM SYSADM.REQUEST_FOR_QUOTE')
> OLE DB provider 'OraOLEDB.Oracle' reported an error.
> [OLE/DB provider returned message: ]
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> returned 0x80004005: ].
> "Bruce L-C [MVP]" wrote:
> > Note that linked servers are dangerous if you are using the four part
naming
> > method. It is very very very easy to end up pulling the whole table over
and
> > the where clause being processed locally rather than on Oracle. If you
are
> > going to use linked server then use the OpenQuery method instead of 4
part
> > naming.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> > ..
> > "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> > news:D119F7DF-ACC9-4019-8087-6672A956F633@.microsoft.com...
> > > Linked Server oracle 0x80004005 error
> > >
> > > Has anyone gotten around the .....
> > > OLE DB provider 'OraOLEDB.Oracle' reported an error.
> > > [OLE/DB provider returned message: Unspecified error]
> > > OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> > > returned 0x80004005: ]. ...
> > > error when creating a linked server to oracle using the oracle drivers
?
> > > Have no problem with the linked server ms oracle driver other than the
> > > fact
> > > that it is SLOW !!!! A query returns in 1min30sec that is instant in
sql
> > > plus.
> > >
> > > Any ideas greatly appreciated ! Thanks, Steve.
> > >
> >
> >
> >|||Hi Bruce,
Thanks for your reply. Yes, there is a user/pw set. Also, all
the tables and views for the linked server appear perfectly. Do not think
this would be possible without that. Steve.
"Bruce L-C [MVP]" wrote:
> Go to your linked server in Enterprise Manager. Right mouse click,
> properties. Go to the Security tab. Do you have a username and password set
> for it?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> news:B0507C65-F50B-431D-AD66-E01381CB9112@.microsoft.com...
> > Hi Bruce,
> > Thank you so much for the reply. Created the VMFO linked
> server
> > again using the Oracle driver and tried the openquery method and... still
> > got the ...
> >
> > SELECT * FROM openquery(VMFO, 'SELECT * FROM SYSADM.REQUEST_FOR_QUOTE')
> >
> > OLE DB provider 'OraOLEDB.Oracle' reported an error.
> > [OLE/DB provider returned message: ]
> > OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> > returned 0x80004005: ].
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > Note that linked servers are dangerous if you are using the four part
> naming
> > > method. It is very very very easy to end up pulling the whole table over
> and
> > > the where clause being processed locally rather than on Oracle. If you
> are
> > > going to use linked server then use the OpenQuery method instead of 4
> part
> > > naming.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > > ..
> > > "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> > > news:D119F7DF-ACC9-4019-8087-6672A956F633@.microsoft.com...
> > > > Linked Server oracle 0x80004005 error
> > > >
> > > > Has anyone gotten around the .....
> > > > OLE DB provider 'OraOLEDB.Oracle' reported an error.
> > > > [OLE/DB provider returned message: Unspecified error]
> > > > OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> > > > returned 0x80004005: ]. ...
> > > > error when creating a linked server to oracle using the oracle drivers
> ?
> > > > Have no problem with the linked server ms oracle driver other than the
> > > > fact
> > > > that it is SLOW !!!! A query returns in 1min30sec that is instant in
> sql
> > > > plus.
> > > >
> > > > Any ideas greatly appreciated ! Thanks, Steve.
> > > >
> > >
> > >
> > >
>
>sql
Linked server OPENQUERY, error 'The name <> is not a valid identif
Hi,
I am working on exporting data from a remote MSSQL db using la inked server.
Both SQL server 2000.
So far I have a SELECT against a linked server with a hard coded value in
the WHERE clause and it works fine:
SELECT * FROM OPENQUERY(SERVER_NAME,'SELECT id,[date] FROM
DB_NAME.dbo.TABLE_NAME WHERE id= 111204892204688')
I try to put this exact query into a variable and run it:
declare @.tsql varchar(8000)
SELECT @.tsql='SELECT * FROM OPENQUERY(SERVER_NAME,''SELECT id,[date]FROM
DB_NAME.dbo.TABLE_NAME WHERE id= 111204892204688'')'
EXEC @.tsql
I get an error:
The name 'SELECT * FROM OPENQUERY(SERVER_NAME,'SELECT id,[date]FROM
DB_NAME.dbo.TABLE_NAME WHERE id= 111204892204688')' is not a valid identifie
r.
The query specified in the error runs just fine on its own (see my first
step).
I searched the web and tech groups for answers but can't find any
information on this error. Please help.
Thank you, A"Alexis" <Alexis@.discussions.microsoft.com> wrote in message
news:539D21E3-6961-49BD-9892-279744A3A5C9@.microsoft.com...
> EXEC @.tsql
Add some parens:
EXEC (@.tsql)
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||Can you try using:
...
exec (@.tsql)
AMB
"Alexis" wrote:
> Hi,
> I am working on exporting data from a remote MSSQL db using la inked serve
r.
> Both SQL server 2000.
> So far I have a SELECT against a linked server with a hard coded value in
> the WHERE clause and it works fine:
> SELECT * FROM OPENQUERY(SERVER_NAME,'SELECT id,[date] FROM
> DB_NAME.dbo.TABLE_NAME WHERE id= 111204892204688')
> I try to put this exact query into a variable and run it:
> declare @.tsql varchar(8000)
> SELECT @.tsql='SELECT * FROM OPENQUERY(SERVER_NAME,''SELECT id,[date]FROM
> DB_NAME.dbo.TABLE_NAME WHERE id= 111204892204688'')'
> EXEC @.tsql
> I get an error:
> The name 'SELECT * FROM OPENQUERY(SERVER_NAME,'SELECT id,[date]FROM
> DB_NAME.dbo.TABLE_NAME WHERE id= 111204892204688')' is not a valid identif
ier.
> The query specified in the error runs just fine on its own (see my first
> step).
> I searched the web and tech groups for answers but can't find any
> information on this error. Please help.
> Thank you, A
>
I am working on exporting data from a remote MSSQL db using la inked server.
Both SQL server 2000.
So far I have a SELECT against a linked server with a hard coded value in
the WHERE clause and it works fine:
SELECT * FROM OPENQUERY(SERVER_NAME,'SELECT id,[date] FROM
DB_NAME.dbo.TABLE_NAME WHERE id= 111204892204688')
I try to put this exact query into a variable and run it:
declare @.tsql varchar(8000)
SELECT @.tsql='SELECT * FROM OPENQUERY(SERVER_NAME,''SELECT id,[date]FROM
DB_NAME.dbo.TABLE_NAME WHERE id= 111204892204688'')'
EXEC @.tsql
I get an error:
The name 'SELECT * FROM OPENQUERY(SERVER_NAME,'SELECT id,[date]FROM
DB_NAME.dbo.TABLE_NAME WHERE id= 111204892204688')' is not a valid identifie
r.
The query specified in the error runs just fine on its own (see my first
step).
I searched the web and tech groups for answers but can't find any
information on this error. Please help.
Thank you, A"Alexis" <Alexis@.discussions.microsoft.com> wrote in message
news:539D21E3-6961-49BD-9892-279744A3A5C9@.microsoft.com...
> EXEC @.tsql
Add some parens:
EXEC (@.tsql)
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||Can you try using:
...
exec (@.tsql)
AMB
"Alexis" wrote:
> Hi,
> I am working on exporting data from a remote MSSQL db using la inked serve
r.
> Both SQL server 2000.
> So far I have a SELECT against a linked server with a hard coded value in
> the WHERE clause and it works fine:
> SELECT * FROM OPENQUERY(SERVER_NAME,'SELECT id,[date] FROM
> DB_NAME.dbo.TABLE_NAME WHERE id= 111204892204688')
> I try to put this exact query into a variable and run it:
> declare @.tsql varchar(8000)
> SELECT @.tsql='SELECT * FROM OPENQUERY(SERVER_NAME,''SELECT id,[date]FROM
> DB_NAME.dbo.TABLE_NAME WHERE id= 111204892204688'')'
> EXEC @.tsql
> I get an error:
> The name 'SELECT * FROM OPENQUERY(SERVER_NAME,'SELECT id,[date]FROM
> DB_NAME.dbo.TABLE_NAME WHERE id= 111204892204688')' is not a valid identif
ier.
> The query specified in the error runs just fine on its own (see my first
> step).
> I searched the web and tech groups for answers but can't find any
> information on this error. Please help.
> Thank you, A
>
Linked server OPENQUERY, error 'The name <> is not a valid ide
Adam,
Thank you for replying to my first message.
Now, can I pass a variable of type integer into that dynamic SELECT
statement? I know it works with character variables, but I really need to
pass in a numeric field. Or do I have to resort to conversions?
declare @.id int
select @.id = 1
SELECT * FROM OPENQUERY(SERVER_NAME,'SELECT id,[date] FROM
DB_NAME.dbo.TABLE_NAME WHERE server=' + @.id + ')'
I get Incorrect syntax near '+'.
Thank you.
"Adam Machanic" wrote:
> "Alexis" <Alexis@.discussions.microsoft.com> wrote in message
> news:539D21E3-6961-49BD-9892-279744A3A5C9@.microsoft.com...
> Add some parens:
> EXEC (@.tsql)
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
>You'll have to build the string dynamically -- you can't pass any kind of
variable into OPENQUERY.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Alexis" <Alexis@.discussions.microsoft.com> wrote in message
news:9B3439B9-7AF2-4B81-A7A3-64BF5A5CAD8A@.microsoft.com...
> Adam,
> Thank you for replying to my first message.
> Now, can I pass a variable of type integer into that dynamic SELECT
> statement? I know it works with character variables, but I really need to
> pass in a numeric field. Or do I have to resort to conversions?
> declare @.id int
> select @.id = 1
> SELECT * FROM OPENQUERY(SERVER_NAME,'SELECT id,[date] FROM
> DB_NAME.dbo.TABLE_NAME WHERE server=' + @.id + ')'
> I get Incorrect syntax near '+'.
> Thank you.
>
Thank you for replying to my first message.
Now, can I pass a variable of type integer into that dynamic SELECT
statement? I know it works with character variables, but I really need to
pass in a numeric field. Or do I have to resort to conversions?
declare @.id int
select @.id = 1
SELECT * FROM OPENQUERY(SERVER_NAME,'SELECT id,[date] FROM
DB_NAME.dbo.TABLE_NAME WHERE server=' + @.id + ')'
I get Incorrect syntax near '+'.
Thank you.
"Adam Machanic" wrote:
> "Alexis" <Alexis@.discussions.microsoft.com> wrote in message
> news:539D21E3-6961-49BD-9892-279744A3A5C9@.microsoft.com...
> Add some parens:
> EXEC (@.tsql)
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
>You'll have to build the string dynamically -- you can't pass any kind of
variable into OPENQUERY.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Alexis" <Alexis@.discussions.microsoft.com> wrote in message
news:9B3439B9-7AF2-4B81-A7A3-64BF5A5CAD8A@.microsoft.com...
> Adam,
> Thank you for replying to my first message.
> Now, can I pass a variable of type integer into that dynamic SELECT
> statement? I know it works with character variables, but I really need to
> pass in a numeric field. Or do I have to resort to conversions?
> declare @.id int
> select @.id = 1
> SELECT * FROM OPENQUERY(SERVER_NAME,'SELECT id,[date] FROM
> DB_NAME.dbo.TABLE_NAME WHERE server=' + @.id + ')'
> I get Incorrect syntax near '+'.
> Thank you.
>
Linked Server OLE DB provider does not contain the table: Provide
Getting a linked server error. The way SQLServer 7.0 linked to 2000 allowed
us to have both the ability to login under a given security context as well
as specify individual logins. The linked server also contains the provision
for impersonation in 2005. If possible, I would like to allow permission to
be propagated from one server to the other. Example: SQL 2005 UserA has
login for table1 on db2 and table3 on db5 on the 2005 server. UserA is
logged into the production server (SQL 2000 and executes a query joining
table1 on db2 (Server2005.db2.dbo.table1) and table2 from db5
(Server2005.db5.dbo.table2).
As it is understood from the reading, impersonation would use a login (sa
for example) which would always give this permission which is what I do not
want. Instead, can I make the access work such that if UserA tries to access
table3 on db2 which is not a table that usera has permission to that UserA
would be denied from SQL 2000 as well as from SQL 2005 server?
For some reason, I cannot get this part to work. UserA is in the list under
security for the linked server 2005 on the server 2000 but still cannot
access the information on Server 2005.
Regards,
Jamie
Forgot to list the error:
SCHEMA LOCK permission denied on object
Regards,
Jamie
"thejamie" wrote:
> Getting a linked server error. The way SQLServer 7.0 linked to 2000 allowed
> us to have both the ability to login under a given security context as well
> as specify individual logins. The linked server also contains the provision
> for impersonation in 2005. If possible, I would like to allow permission to
> be propagated from one server to the other. Example: SQL 2005 UserA has
> login for table1 on db2 and table3 on db5 on the 2005 server. UserA is
> logged into the production server (SQL 2000 and executes a query joining
> table1 on db2 (Server2005.db2.dbo.table1) and table2 from db5
> (Server2005.db5.dbo.table2).
> As it is understood from the reading, impersonation would use a login (sa
> for example) which would always give this permission which is what I do not
> want. Instead, can I make the access work such that if UserA tries to access
> table3 on db2 which is not a table that usera has permission to that UserA
> would be denied from SQL 2000 as well as from SQL 2005 server?
> For some reason, I cannot get this part to work. UserA is in the list under
> security for the linked server 2005 on the server 2000 but still cannot
> access the information on Server 2005.
> --
> Regards,
> Jamie
us to have both the ability to login under a given security context as well
as specify individual logins. The linked server also contains the provision
for impersonation in 2005. If possible, I would like to allow permission to
be propagated from one server to the other. Example: SQL 2005 UserA has
login for table1 on db2 and table3 on db5 on the 2005 server. UserA is
logged into the production server (SQL 2000 and executes a query joining
table1 on db2 (Server2005.db2.dbo.table1) and table2 from db5
(Server2005.db5.dbo.table2).
As it is understood from the reading, impersonation would use a login (sa
for example) which would always give this permission which is what I do not
want. Instead, can I make the access work such that if UserA tries to access
table3 on db2 which is not a table that usera has permission to that UserA
would be denied from SQL 2000 as well as from SQL 2005 server?
For some reason, I cannot get this part to work. UserA is in the list under
security for the linked server 2005 on the server 2000 but still cannot
access the information on Server 2005.
Regards,
Jamie
Forgot to list the error:
SCHEMA LOCK permission denied on object
Regards,
Jamie
"thejamie" wrote:
> Getting a linked server error. The way SQLServer 7.0 linked to 2000 allowed
> us to have both the ability to login under a given security context as well
> as specify individual logins. The linked server also contains the provision
> for impersonation in 2005. If possible, I would like to allow permission to
> be propagated from one server to the other. Example: SQL 2005 UserA has
> login for table1 on db2 and table3 on db5 on the 2005 server. UserA is
> logged into the production server (SQL 2000 and executes a query joining
> table1 on db2 (Server2005.db2.dbo.table1) and table2 from db5
> (Server2005.db5.dbo.table2).
> As it is understood from the reading, impersonation would use a login (sa
> for example) which would always give this permission which is what I do not
> want. Instead, can I make the access work such that if UserA tries to access
> table3 on db2 which is not a table that usera has permission to that UserA
> would be denied from SQL 2000 as well as from SQL 2005 server?
> For some reason, I cannot get this part to work. UserA is in the list under
> security for the linked server 2005 on the server 2000 but still cannot
> access the information on Server 2005.
> --
> Regards,
> Jamie
Linked Server OLE DB provider does not contain the table: Provide
Getting a linked server error. The way SQLServer 7.0 linked to 2000 allowed
us to have both the ability to login under a given security context as well
as specify individual logins. The linked server also contains the provision
for impersonation in 2005. If possible, I would like to allow permission to
be propagated from one server to the other. Example: SQL 2005 UserA has
login for table1 on db2 and table3 on db5 on the 2005 server. UserA is
logged into the production server (SQL 2000 and executes a query joining
table1 on db2 (Server2005.db2.dbo.table1) and table2 from db5
(Server2005.db5.dbo.table2).
As it is understood from the reading, impersonation would use a login (sa
for example) which would always give this permission which is what I do not
want. Instead, can I make the access work such that if UserA tries to access
table3 on db2 which is not a table that usera has permission to that UserA
would be denied from SQL 2000 as well as from SQL 2005 server?
For some reason, I cannot get this part to work. UserA is in the list under
security for the linked server 2005 on the server 2000 but still cannot
access the information on Server 2005.
--
Regards,
JamieForgot to list the error:
SCHEMA LOCK permission denied on object
--
Regards,
Jamie
"thejamie" wrote:
> Getting a linked server error. The way SQLServer 7.0 linked to 2000 allowed
> us to have both the ability to login under a given security context as well
> as specify individual logins. The linked server also contains the provision
> for impersonation in 2005. If possible, I would like to allow permission to
> be propagated from one server to the other. Example: SQL 2005 UserA has
> login for table1 on db2 and table3 on db5 on the 2005 server. UserA is
> logged into the production server (SQL 2000 and executes a query joining
> table1 on db2 (Server2005.db2.dbo.table1) and table2 from db5
> (Server2005.db5.dbo.table2).
> As it is understood from the reading, impersonation would use a login (sa
> for example) which would always give this permission which is what I do not
> want. Instead, can I make the access work such that if UserA tries to access
> table3 on db2 which is not a table that usera has permission to that UserA
> would be denied from SQL 2000 as well as from SQL 2005 server?
> For some reason, I cannot get this part to work. UserA is in the list under
> security for the linked server 2005 on the server 2000 but still cannot
> access the information on Server 2005.
> --
> Regards,
> Jamie
us to have both the ability to login under a given security context as well
as specify individual logins. The linked server also contains the provision
for impersonation in 2005. If possible, I would like to allow permission to
be propagated from one server to the other. Example: SQL 2005 UserA has
login for table1 on db2 and table3 on db5 on the 2005 server. UserA is
logged into the production server (SQL 2000 and executes a query joining
table1 on db2 (Server2005.db2.dbo.table1) and table2 from db5
(Server2005.db5.dbo.table2).
As it is understood from the reading, impersonation would use a login (sa
for example) which would always give this permission which is what I do not
want. Instead, can I make the access work such that if UserA tries to access
table3 on db2 which is not a table that usera has permission to that UserA
would be denied from SQL 2000 as well as from SQL 2005 server?
For some reason, I cannot get this part to work. UserA is in the list under
security for the linked server 2005 on the server 2000 but still cannot
access the information on Server 2005.
--
Regards,
JamieForgot to list the error:
SCHEMA LOCK permission denied on object
--
Regards,
Jamie
"thejamie" wrote:
> Getting a linked server error. The way SQLServer 7.0 linked to 2000 allowed
> us to have both the ability to login under a given security context as well
> as specify individual logins. The linked server also contains the provision
> for impersonation in 2005. If possible, I would like to allow permission to
> be propagated from one server to the other. Example: SQL 2005 UserA has
> login for table1 on db2 and table3 on db5 on the 2005 server. UserA is
> logged into the production server (SQL 2000 and executes a query joining
> table1 on db2 (Server2005.db2.dbo.table1) and table2 from db5
> (Server2005.db5.dbo.table2).
> As it is understood from the reading, impersonation would use a login (sa
> for example) which would always give this permission which is what I do not
> want. Instead, can I make the access work such that if UserA tries to access
> table3 on db2 which is not a table that usera has permission to that UserA
> would be denied from SQL 2000 as well as from SQL 2005 server?
> For some reason, I cannot get this part to work. UserA is in the list under
> security for the linked server 2005 on the server 2000 but still cannot
> access the information on Server 2005.
> --
> Regards,
> Jamie
Linked Server OLE DB provider does not contain the table: Provide
Getting a linked server error. The way SQLServer 7.0 linked to 2000 allowed
us to have both the ability to login under a given security context as well
as specify individual logins. The linked server also contains the provision
for impersonation in 2005. If possible, I would like to allow permission t
o
be propagated from one server to the other. Example: SQL 2005 UserA has
login for table1 on DB2 and table3 on db5 on the 2005 server. UserA is
logged into the production server (SQL 2000 and executes a query joining
table1 on DB2 (Server2005.db2.dbo.table1) and table2 from db5
(Server2005.db5.dbo.table2).
As it is understood from the reading, impersonation would use a login (sa
for example) which would always give this permission which is what I do not
want. Instead, can I make the access work such that if UserA tries to acces
s
table3 on DB2 which is not a table that usera has permission to that UserA
would be denied from SQL 2000 as well as from SQL 2005 server?
For some reason, I cannot get this part to work. UserA is in the list under
security for the linked server 2005 on the server 2000 but still cannot
access the information on Server 2005.
--
Regards,
JamieForgot to list the error:
SCHEMA LOCK permission denied on object
Regards,
Jamie
"thejamie" wrote:
> Getting a linked server error. The way SQLServer 7.0 linked to 2000 allow
ed
> us to have both the ability to login under a given security context as wel
l
> as specify individual logins. The linked server also contains the provisi
on
> for impersonation in 2005. If possible, I would like to allow permission
to
> be propagated from one server to the other. Example: SQL 2005 UserA has
> login for table1 on DB2 and table3 on db5 on the 2005 server. UserA is
> logged into the production server (SQL 2000 and executes a query joining
> table1 on DB2 (Server2005.db2.dbo.table1) and table2 from db5
> (Server2005.db5.dbo.table2).
> As it is understood from the reading, impersonation would use a login (sa
> for example) which would always give this permission which is what I do no
t
> want. Instead, can I make the access work such that if UserA tries to acc
ess
> table3 on DB2 which is not a table that usera has permission to that UserA
> would be denied from SQL 2000 as well as from SQL 2005 server?
> For some reason, I cannot get this part to work. UserA is in the list und
er
> security for the linked server 2005 on the server 2000 but still cannot
> access the information on Server 2005.
> --
> Regards,
> Jamie
us to have both the ability to login under a given security context as well
as specify individual logins. The linked server also contains the provision
for impersonation in 2005. If possible, I would like to allow permission t
o
be propagated from one server to the other. Example: SQL 2005 UserA has
login for table1 on DB2 and table3 on db5 on the 2005 server. UserA is
logged into the production server (SQL 2000 and executes a query joining
table1 on DB2 (Server2005.db2.dbo.table1) and table2 from db5
(Server2005.db5.dbo.table2).
As it is understood from the reading, impersonation would use a login (sa
for example) which would always give this permission which is what I do not
want. Instead, can I make the access work such that if UserA tries to acces
s
table3 on DB2 which is not a table that usera has permission to that UserA
would be denied from SQL 2000 as well as from SQL 2005 server?
For some reason, I cannot get this part to work. UserA is in the list under
security for the linked server 2005 on the server 2000 but still cannot
access the information on Server 2005.
--
Regards,
JamieForgot to list the error:
SCHEMA LOCK permission denied on object
Regards,
Jamie
"thejamie" wrote:
> Getting a linked server error. The way SQLServer 7.0 linked to 2000 allow
ed
> us to have both the ability to login under a given security context as wel
l
> as specify individual logins. The linked server also contains the provisi
on
> for impersonation in 2005. If possible, I would like to allow permission
to
> be propagated from one server to the other. Example: SQL 2005 UserA has
> login for table1 on DB2 and table3 on db5 on the 2005 server. UserA is
> logged into the production server (SQL 2000 and executes a query joining
> table1 on DB2 (Server2005.db2.dbo.table1) and table2 from db5
> (Server2005.db5.dbo.table2).
> As it is understood from the reading, impersonation would use a login (sa
> for example) which would always give this permission which is what I do no
t
> want. Instead, can I make the access work such that if UserA tries to acc
ess
> table3 on DB2 which is not a table that usera has permission to that UserA
> would be denied from SQL 2000 as well as from SQL 2005 server?
> For some reason, I cannot get this part to work. UserA is in the list und
er
> security for the linked server 2005 on the server 2000 but still cannot
> access the information on Server 2005.
> --
> Regards,
> Jamie
LINKED SERVER NESTED OPENQUERY SAMPLE FROM TIPS AND TRICKS DOESN'T APPEARS TO FAIL WITH MSO
Is it that I have a syntax error in the nested OPENQUERY or is there another issue? Do I need to specify a different provider in the Server Link such as OLEDB? Non-nested OPENQUERYs work fine.
I'm generally following theTips and Tricks article.
"Executing predictions from the SQL Server relational engine". One problem is the sample doesn't actually complete the example query after the second nested OPENQUERY call.
e.g.
SELECT * FROM OPENQUERY(DMServer,
'select … FROM Modell PREDICTION JOIN OPENQUERY…')
The SQL Server server link's provider is configured to allow adhoc access. I appears that the inner OPENQUERY cannot be prepared by Analysis Server or the Server link provider? but I need to return a key value t.[CardTransactionID] for joining to SQL Server data elements.
OLE DB provider "MSOLAP" for linked server "DMServer" returned message "Errors in the back-end database access module. The data provider does not support preparing queries.".
Msg 7321, Level 16, State 2, Line 2 An error occurred while preparing the query
SELECT * FROM OPENQUERY(DMServer,
'SELECT
t.[CardTransactionID],
t.[PostingDate],
[Misuse Abuse Profile].[Even Dollar Purchase],
PredictProbability([Misuse Abuse Profile].[Even Dollar Purchase]) AS Score,
PredictSupport([Misuse Abuse Profile].[Even Dollar Purchase]) AS Suppt,
t.[BillingAmount]
FROM
[Misuse Abuse Profile]
PREDICTION JOIN
OPENQUERY([Athena Dev],
''SELECT
[CardTransactionID],
[PostingDate],
[BillingAmount],
[AccountNumber],
[SupplierStateProvinceCode],
[MerchantCategoryCode],
[PurchaseIDFormat],
[TransactionTime],
[TaxAmountIncludedCode],
[Tax2AmountIncludedCode],
[OrderTypeCode],
[MemoPostFlag],
[EvenDollarPurchase]
FROM
[dbo].[vMisuseAbuseProfile]
'') AS t
ON
[Misuse Abuse Profile].[Account Number] = t.[AccountNumber] AND
[Misuse Abuse Profile].[Supplier State Province Code] = t.[SupplierStateProvinceCode] AND
[Misuse Abuse Profile].[Merchant Category Code] = t.[MerchantCategoryCode] AND
[Misuse Abuse Profile].[Purchase ID Format] = t.[PurchaseIDFormat] AND
[Misuse Abuse Profile].[Transaction Time] = t.[TransactionTime] AND
[Misuse Abuse Profile].[Tax Amount Included Code] = t.[TaxAmountIncludedCode] AND
[Misuse Abuse Profile].[Tax2 Amount Included Code] = t.[Tax2AmountIncludedCode] AND
[Misuse Abuse Profile].[Order Type Code] = t.[OrderTypeCode] AND
[Misuse Abuse Profile].[Memo Post Flag] = t.[MemoPostFlag] AND
[Misuse Abuse Profile].[Even Dollar Purchase] = t.[EvenDollarPurchase]
')
In desparation I tried returning the case key (CardTransactionID) and the predictive column elements but I get an error when I try that. I assume this is a no-no?
OLE DB provider "MSOLAP" for linked server "DMServer" returned message "Error (Data mining): Only a predictable column (or a column that is related to a predictable column) can be referenced from the mining model in the context at line 2, column 15.".
It appears the default Analysis Services Data Source wizard selection of SQL Native Client Data Source provider cannot prepare an OPENQUERY if passed from a linked server? I changed the Datasource to Provider=SQLOLEDB.1 and the query now works.
|||this is very odd - we will look into it - thanks for reporting.|||Hi Bill,
Could you give more details about the setup. Is the SQL server where you defined the linked server DMTest on the same machine with the Analysis Server it refers too? And also is the SQL server refered on the inner openquery datasource [Athena Dev] on a different machine than the machines with AS and SQL server with added linked server?
Thanks,
Dana Cristofor
Friday, March 23, 2012
linked server Login timeout expired error
Hello All,
My application is dependent on two databases one is on sql 2005 machine and other is on sql 2000 machine. A linked server is establish between these two using sql account which has appropriate rights on both server's databases.
application works fine but after some time it crashes and if i see error from sql profiler i get the error
OLE DB provider "SQLNCLI" for linked server "sql2000machine" returned message "Login timeout expired".
Few days back applciation was working absolutly fine, but i was trying to make linked sever through windows account, i dnt know wat has i changed during achiveing that setup that now application (linked server) is not working even with sql account.
please help its urgent.
Check network setting. Can you ping from sql2k5 to sql2k. Also, how is the linked server login setup? Is it "self" or the remote credential? Remember that domain authentication requires a domain controller. Network latency might be your enemy here.
sqlLinked Server Issues
Good Morning All,
I am receiving the following error message when attempting to update tables
with a trigger that call a stored procedure via a linked server. If I copy
and paste the execute statement from the triggerinto query analyzer it
completes without error.
Server: Msg 7391, Level 16, State 1, Procedure UpdateUnicenterTextFiles,
Line 5
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].
Any suggestions about what is causing this and/or how I can correct it would
be greatly appreciated.
Thanks in advance,
Elecia
Are You using any distributed transations
go to administrative tools\component
services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
Check if you have Enable XA transactions and allow remote clients and
Noauthntication Required
Options
Thanks,
Saradhi
|||Thanks I will check that. I am also looking at the possibility of it being a
firewall issue and that maybe some of the necesary ports may be blocked.
Elecia
"saradhi" wrote:
> Are You using any distributed transations
>
> go to administrative tools\component
> services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
> Check if you have Enable XA transactions and allow remote clients and
> Noauthntication Required
> Options
> Thanks,
> Saradhi
>
I am receiving the following error message when attempting to update tables
with a trigger that call a stored procedure via a linked server. If I copy
and paste the execute statement from the triggerinto query analyzer it
completes without error.
Server: Msg 7391, Level 16, State 1, Procedure UpdateUnicenterTextFiles,
Line 5
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].
Any suggestions about what is causing this and/or how I can correct it would
be greatly appreciated.
Thanks in advance,
Elecia
Are You using any distributed transations
go to administrative tools\component
services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
Check if you have Enable XA transactions and allow remote clients and
Noauthntication Required
Options
Thanks,
Saradhi
|||Thanks I will check that. I am also looking at the possibility of it being a
firewall issue and that maybe some of the necesary ports may be blocked.
Elecia
"saradhi" wrote:
> Are You using any distributed transations
>
> go to administrative tools\component
> services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
> Check if you have Enable XA transactions and allow remote clients and
> Noauthntication Required
> Options
> Thanks,
> Saradhi
>
Linked Server Issues
Good Morning All,
I am receiving the following error message when attempting to update tables
with a trigger that call a stored procedure via a linked server. If I copy
and paste the execute statement from the triggerinto query analyzer it
completes without error.
Server: Msg 7391, Level 16, State 1, Procedure UpdateUnicenterTextFiles,
Line 5
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].
Any suggestions about what is causing this and/or how I can correct it would
be greatly appreciated.
Thanks in advance,
--
EleciaAre You using any distributed transations
go to administrative tools\component
services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
Check if you have Enable XA transactions and allow remote clients and
Noauthntication Required
Options
Thanks,
Saradhi|||Thanks I will check that. I am also looking at the possibility of it being a
firewall issue and that maybe some of the necesary ports may be blocked.
--
Elecia
"saradhi" wrote:
> Are You using any distributed transations
>
> go to administrative tools\component
> services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
> Check if you have Enable XA transactions and allow remote clients and
> Noauthntication Required
> Options
> Thanks,
> Saradhi
>
I am receiving the following error message when attempting to update tables
with a trigger that call a stored procedure via a linked server. If I copy
and paste the execute statement from the triggerinto query analyzer it
completes without error.
Server: Msg 7391, Level 16, State 1, Procedure UpdateUnicenterTextFiles,
Line 5
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].
Any suggestions about what is causing this and/or how I can correct it would
be greatly appreciated.
Thanks in advance,
--
EleciaAre You using any distributed transations
go to administrative tools\component
services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
Check if you have Enable XA transactions and allow remote clients and
Noauthntication Required
Options
Thanks,
Saradhi|||Thanks I will check that. I am also looking at the possibility of it being a
firewall issue and that maybe some of the necesary ports may be blocked.
--
Elecia
"saradhi" wrote:
> Are You using any distributed transations
>
> go to administrative tools\component
> services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
> Check if you have Enable XA transactions and allow remote clients and
> Noauthntication Required
> Options
> Thanks,
> Saradhi
>
Linked Server Issues
Good Morning All,
I am receiving the following error message when attempting to update tables
with a trigger that call a stored procedure via a linked server. If I copy
and paste the execute statement from the triggerinto query analyzer it
completes without error.
Server: Msg 7391, Level 16, State 1, Procedure UpdateUnicenterTextFiles,
Line 5
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].
Any suggestions about what is causing this and/or how I can correct it would
be greatly appreciated.
Thanks in advance,
--
EleciaAre You using any distributed transations
go to administrative tools\component
services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
Check if you have Enable XA transactions and allow remote clients and
Noauthntication Required
Options
Thanks,
Saradhi|||Thanks I will check that. I am also looking at the possibility of it being a
firewall issue and that maybe some of the necesary ports may be blocked.
--
Elecia
"saradhi" wrote:
> Are You using any distributed transations
>
> go to administrative tools\component
> services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
> Check if you have Enable XA transactions and allow remote clients and
> Noauthntication Required
> Options
> Thanks,
> Saradhi
>sql
I am receiving the following error message when attempting to update tables
with a trigger that call a stored procedure via a linked server. If I copy
and paste the execute statement from the triggerinto query analyzer it
completes without error.
Server: Msg 7391, Level 16, State 1, Procedure UpdateUnicenterTextFiles,
Line 5
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].
Any suggestions about what is causing this and/or how I can correct it would
be greatly appreciated.
Thanks in advance,
--
EleciaAre You using any distributed transations
go to administrative tools\component
services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
Check if you have Enable XA transactions and allow remote clients and
Noauthntication Required
Options
Thanks,
Saradhi|||Thanks I will check that. I am also looking at the possibility of it being a
firewall issue and that maybe some of the necesary ports may be blocked.
--
Elecia
"saradhi" wrote:
> Are You using any distributed transations
>
> go to administrative tools\component
> services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
> Check if you have Enable XA transactions and allow remote clients and
> Noauthntication Required
> Options
> Thanks,
> Saradhi
>sql
Wednesday, March 21, 2012
Linked Server gives Internal SQL Server error
Hi All,
I am accessing a linked server (also a SQL Server) from a stored
procedure. There is an insert statement that I run on a table in the
linked server. This statement causes the Internal SQL Server error. But
if I run the insert statement separately from Query Analyzer, it works
fine! Also, I put the stored procedure directly into the database on
the linked SQL Server and it ran fine on it. Has anybody come across
something of this sort?
Thanks,
Sanjeevmahajan.sanjeev@.gmail.com napisa(a):
> Hi All,
> I am accessing a linked server (also a SQL Server) from a stored
> procedure. There is an insert statement that I run on a table in the
> linked server. This statement causes the Internal SQL Server error. But
> if I run the insert statement separately from Query Analyzer, it works
> fine! Also, I put the stored procedure directly into the database on
> the linked SQL Server and it ran fine on it. Has anybody come across
> something of this sort?
> Thanks,
> Sanjeev
When you start procedure from query analyzer you do it as some user
but when this procedure is started by sql agent as job it is another
user which doesn't have an access to the database linked by linked
server, and this is the problem.
You can try to run this procedure from vbs for example with ado
connection the same as in query analyzer
Linked server from 2005 to 2000 failed
Hi,
I made the linked server from SQL Server 2005 Windows 2003 (64 bit) to SQL
Server 2000 (32 bit). I got error message as following: Cannot obtain the
schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked
server "SQL2000_SQM1BSI". The provider supports the interface, but returns a
failure code when it is used.
I read the article 906954 and try to solve this problem but the osql didn't
run successfully in sql server 2000 (with SP3) box. In SQL Server 2000 box,
it installed named instance, can I use sa to run osql with instcat.sql? Can
someone give an example?
Regards!
Chen
Hi
Yes you can use osql to run instcat.sql and this is described in the second
set of examples that uses the -U parameter. Make sure that you have the
correct version of instcat.sql e.g. instcat.sql for service pack 4 will
insert a version number of 8.00.2039 for SYS_SPROC_VERSION into
spt_server_info
John
"Chen" wrote:
> Hi,
> I made the linked server from SQL Server 2005 Windows 2003 (64 bit) to SQL
> Server 2000 (32 bit). I got error message as following: Cannot obtain the
> schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked
> server "SQL2000_SQM1BSI". The provider supports the interface, but returns a
> failure code when it is used.
> I read the article 906954 and try to solve this problem but the osql didn't
> run successfully in sql server 2000 (with SP3) box. In SQL Server 2000 box,
> it installed named instance, can I use sa to run osql with instcat.sql? Can
> someone give an example?
> Regards!
> Chen
I made the linked server from SQL Server 2005 Windows 2003 (64 bit) to SQL
Server 2000 (32 bit). I got error message as following: Cannot obtain the
schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked
server "SQL2000_SQM1BSI". The provider supports the interface, but returns a
failure code when it is used.
I read the article 906954 and try to solve this problem but the osql didn't
run successfully in sql server 2000 (with SP3) box. In SQL Server 2000 box,
it installed named instance, can I use sa to run osql with instcat.sql? Can
someone give an example?
Regards!
Chen
Hi
Yes you can use osql to run instcat.sql and this is described in the second
set of examples that uses the -U parameter. Make sure that you have the
correct version of instcat.sql e.g. instcat.sql for service pack 4 will
insert a version number of 8.00.2039 for SYS_SPROC_VERSION into
spt_server_info
John
"Chen" wrote:
> Hi,
> I made the linked server from SQL Server 2005 Windows 2003 (64 bit) to SQL
> Server 2000 (32 bit). I got error message as following: Cannot obtain the
> schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked
> server "SQL2000_SQM1BSI". The provider supports the interface, but returns a
> failure code when it is used.
> I read the article 906954 and try to solve this problem but the osql didn't
> run successfully in sql server 2000 (with SP3) box. In SQL Server 2000 box,
> it installed named instance, can I use sa to run osql with instcat.sql? Can
> someone give an example?
> Regards!
> Chen
Linked server from 2005 to 2000 failed
Hi,
I made the linked server from SQL Server 2005 Windows 2003 (64 bit) to SQL
Server 2000 (32 bit). I got error message as following: Cannot obtain the
schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked
server "SQL2000_SQM1BSI". The provider supports the interface, but returns a
failure code when it is used.
I read the article 906954 and try to solve this problem but the osql didn't
run successfully in sql server 2000 (with SP3) box. In SQL Server 2000 box,
it installed named instance, can I use sa to run osql with instcat.sql? Can
someone give an example?
Regards!
ChenHi
Yes you can use osql to run instcat.sql and this is described in the second
set of examples that uses the -U parameter. Make sure that you have the
correct version of instcat.sql e.g. instcat.sql for service pack 4 will
insert a version number of 8.00.2039 for SYS_SPROC_VERSION into
spt_server_info
John
"Chen" wrote:
> Hi,
> I made the linked server from SQL Server 2005 Windows 2003 (64 bit) to SQL
> Server 2000 (32 bit). I got error message as following: Cannot obtain the
> schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked
> server "SQL2000_SQM1BSI". The provider supports the interface, but returns a
> failure code when it is used.
> I read the article 906954 and try to solve this problem but the osql didn't
> run successfully in sql server 2000 (with SP3) box. In SQL Server 2000 box,
> it installed named instance, can I use sa to run osql with instcat.sql? Can
> someone give an example?
> Regards!
> Chen
I made the linked server from SQL Server 2005 Windows 2003 (64 bit) to SQL
Server 2000 (32 bit). I got error message as following: Cannot obtain the
schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked
server "SQL2000_SQM1BSI". The provider supports the interface, but returns a
failure code when it is used.
I read the article 906954 and try to solve this problem but the osql didn't
run successfully in sql server 2000 (with SP3) box. In SQL Server 2000 box,
it installed named instance, can I use sa to run osql with instcat.sql? Can
someone give an example?
Regards!
ChenHi
Yes you can use osql to run instcat.sql and this is described in the second
set of examples that uses the -U parameter. Make sure that you have the
correct version of instcat.sql e.g. instcat.sql for service pack 4 will
insert a version number of 8.00.2039 for SYS_SPROC_VERSION into
spt_server_info
John
"Chen" wrote:
> Hi,
> I made the linked server from SQL Server 2005 Windows 2003 (64 bit) to SQL
> Server 2000 (32 bit). I got error message as following: Cannot obtain the
> schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked
> server "SQL2000_SQM1BSI". The provider supports the interface, but returns a
> failure code when it is used.
> I read the article 906954 and try to solve this problem but the osql didn't
> run successfully in sql server 2000 (with SP3) box. In SQL Server 2000 box,
> it installed named instance, can I use sa to run osql with instcat.sql? Can
> someone give an example?
> Regards!
> Chen
Linked server from 2005 to 2000 failed
Hi,
I made the linked server from SQL Server 2005 Windows 2003 (64 bit) to SQL
Server 2000 (32 bit). I got error message as following: Cannot obtain the
schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linke
d
server "SQL2000_SQM1BSI". The provider supports the interface, but returns a
failure code when it is used.
I read the article 906954 and try to solve this problem but the osql didn't
run successfully in sql server 2000 (with SP3) box. In SQL Server 2000 box,
it installed named instance, can I use sa to run osql with instcat.sql? Can
someone give an example?
Regards!
ChenHi
Yes you can use osql to run instcat.sql and this is described in the second
set of examples that uses the -U parameter. Make sure that you have the
correct version of instcat.sql e.g. instcat.sql for service pack 4 will
insert a version number of 8.00.2039 for SYS_SPROC_VERSION into
spt_server_info
John
"Chen" wrote:
> Hi,
> I made the linked server from SQL Server 2005 Windows 2003 (64 bit) to SQL
> Server 2000 (32 bit). I got error message as following: Cannot obtain the
> schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for lin
ked
> server "SQL2000_SQM1BSI". The provider supports the interface, but returns
a
> failure code when it is used.
> I read the article 906954 and try to solve this problem but the osql didn'
t
> run successfully in sql server 2000 (with SP3) box. In SQL Server 2000 box
,
> it installed named instance, can I use sa to run osql with instcat.sql? Ca
n
> someone give an example?
> Regards!
> Chen
I made the linked server from SQL Server 2005 Windows 2003 (64 bit) to SQL
Server 2000 (32 bit). I got error message as following: Cannot obtain the
schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linke
d
server "SQL2000_SQM1BSI". The provider supports the interface, but returns a
failure code when it is used.
I read the article 906954 and try to solve this problem but the osql didn't
run successfully in sql server 2000 (with SP3) box. In SQL Server 2000 box,
it installed named instance, can I use sa to run osql with instcat.sql? Can
someone give an example?
Regards!
ChenHi
Yes you can use osql to run instcat.sql and this is described in the second
set of examples that uses the -U parameter. Make sure that you have the
correct version of instcat.sql e.g. instcat.sql for service pack 4 will
insert a version number of 8.00.2039 for SYS_SPROC_VERSION into
spt_server_info
John
"Chen" wrote:
> Hi,
> I made the linked server from SQL Server 2005 Windows 2003 (64 bit) to SQL
> Server 2000 (32 bit). I got error message as following: Cannot obtain the
> schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for lin
ked
> server "SQL2000_SQM1BSI". The provider supports the interface, but returns
a
> failure code when it is used.
> I read the article 906954 and try to solve this problem but the osql didn'
t
> run successfully in sql server 2000 (with SP3) box. In SQL Server 2000 box
,
> it installed named instance, can I use sa to run osql with instcat.sql? Ca
n
> someone give an example?
> Regards!
> Chen
Subscribe to:
Posts (Atom)