linked server problems

I do the following to link a server:
EXEC sp_addlinkedserver 'MainLinkedServer', 'SQLServer OLEDB Provider',
'SQLOLEDB', 'MATT', NULL, NULL, 'WEEK'
It says it links fine (no errors), but when I do a select on a table I know
exists
select distinct T1."Institution" c1 from 'MainLinkedServer'...myTable T1
I get the following error:
OLE DB provider 'MainLinkedServer'does not contain table 'myTable'. The
table either does not exist or the current user does not have permissions on
that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain
the table: ProviderName='MainLinkedServer', TableName='myTable'].
any ideas?
I figured it out:
SELECT * FROM linked_server.database.dbo.table_name tn
"Matt Tapia" <mtapia@.inbizservices.com> wrote in message
news:ebXRQoASEHA.1312@.TK2MSFTNGP12.phx.gbl...
> I do the following to link a server:
> EXEC sp_addlinkedserver 'MainLinkedServer', 'SQLServer OLEDB Provider',
> 'SQLOLEDB', 'MATT', NULL, NULL, 'WEEK'
> It says it links fine (no errors), but when I do a select on a table I
know
> exists
> select distinct T1."Institution" c1 from 'MainLinkedServer'...myTable T1
> I get the following error:
> OLE DB provider 'MainLinkedServer'does not contain table 'myTable'. The
> table either does not exist or the current user does not have permissions
on
> that table.
> OLE DB error trace [Non-interface error: OLE DB provider does not contain
> the table: ProviderName='MainLinkedServer', TableName='myTable'].
> any ideas?
>
>
>

Linked Server Openquery returns -1 on 2000, works on 2005

We have a query that works fine on SQLServer 2005, but when we run the exact
same query on SQLServer 2000 it fails with a -1. We're not able to get any
other error info at the moment. We're using Windows 2003 Server for both
databases and connecting to Oracle.
KevinAnyone? Buehler?

> We have a query that works fine on SQLServer 2005, but when we run the
> exact same query on SQLServer 2000 it fails with a -1. We're not able to
> get any other error info at the moment. We're using Windows 2003 Server
> for both databases and connecting to Oracle.
> Kevin
>

Linked Server Openquery returns -1 on 2000, works on 2005

We have a query that works fine on SQLServer 2005, but when we run the exact
same query on SQLServer 2000 it fails with a -1. We're not able to get any
other error info at the moment. We're using Windows 2003 Server for both
databases and connecting to Oracle.
KevinAnyone? Buehler?
> We have a query that works fine on SQLServer 2005, but when we run the
> exact same query on SQLServer 2000 it fails with a -1. We're not able to
> get any other error info at the moment. We're using Windows 2003 Server
> for both databases and connecting to Oracle.
> Kevin
>sql

Linked Server Openquery returns -1 on 2000, works on 2005

We have a query that works fine on SQLServer 2005, but when we run the exact
same query on SQLServer 2000 it fails with a -1. We're not able to get any
other error info at the moment. We're using Windows 2003 Server for both
databases and connecting to Oracle.
Kevin
Anyone? Buehler?

> We have a query that works fine on SQLServer 2005, but when we run the
> exact same query on SQLServer 2000 it fails with a -1. We're not able to
> get any other error info at the moment. We're using Windows 2003 Server
> for both databases and connecting to Oracle.
> Kevin
>

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

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

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

Linked server issue

I have a SQL Server linked to a non-SQL server using ODBC on our
internal network. Every query I run on the linked server from SQL
Server takes exactly 1 minute longer than to run the query via ASP
page and ODBC. I'm suspecting some strange handshake is going on when
the server is linked, and was wondering if there was a way around it?
I'd rather not tap into the remote server via ASP pages, but rather
have the ASP pages connect to the SQL Server.
Thanks
Cate
Do you run this query using 4 part naming or OPENQUERY?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Linked Server Issue

I am trying to set up a link between 2 SQLServer databases. One is on
server named ACT-S91 and the other is on server named ACT-S92. I ran the
following statement on ACT-S91 - which said it worked.
sp_addlinkedserver N'ACT-S92', ' ', N'SQLOLEDB', N'ACT-S92'
So, at this point I should have ACT-S91 and ACT-S92 linked. But when I run
a query from ACT-S91 trying to access the data on ACT-S92 I get the
following error:
Server: Msg 7302, Level 16, State 1, Line 1
Could not create an instance of OLE DB provider 'SQLOLEDB'.
OLE DB error trace [Non-interface error: CoCreate of DSO for SQLOLEDB
returned 0x8007007f].
I assume I am missing something in setting up the link. But from the
documentation I can't determine what that something might be. Any help?
Edward Fultz
Sr. Software Engineer
Pamet Systems
Acton, Ma
www.pametsystems.com
If you look at the properties of the linked Server in Enterprise Manager,
under Security, what context is the connection to the linked server being
made.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||I used the one that says Be made using the login's current security context.
Since I can get into both servers I assumed I would have no problems. I
have no specific local logins defined. We use windows authentication on
both machines. So, if I can get into both machines shouldn't this have been
right?
Edward Fultz
Sr. Software Engineer
Pamet Systems
Acton, Ma
www.pametsystems.com
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:hXKEeD%23UEHA.2616@.cpmsftngxa10.phx.gbl...
> If you look at the properties of the linked Server in Enterprise Manager,
> under Security, what context is the connection to the linked server being
> made.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>
|||I tried setting a specific security user id and password and that, also,
provided the same error.
Edward Fultz
Sr. Software Engineer
Pamet Systems
Acton, Ma
www.pametsystems.com
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:hXKEeD%23UEHA.2616@.cpmsftngxa10.phx.gbl...
> If you look at the properties of the linked Server in Enterprise Manager,
> under Security, what context is the connection to the linked server being
> made.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>
|||Figured out the problem appears to be that you can't use the SQLServer
option. But on our server there are no providers loaded so we are
researching why this is and how to fix it. Anyone have any ideas how to get
providers loaded? We have 2 other servers and the providers show up there
fine. The server on which they don't show up is a Server2003 server running
SQLServer2000 (was recently upgraded to Server2003).
Edward Fultz
Sr. Software Engineer
Pamet Systems
Acton, Ma
www.pametsystems.com
"Ed Fultz" <efultz@.yahoo.com> wrote in message
news:%235FnB88UEHA.4092@.TK2MSFTNGP11.phx.gbl...
> I am trying to set up a link between 2 SQLServer databases. One is on
> server named ACT-S91 and the other is on server named ACT-S92. I ran the
> following statement on ACT-S91 - which said it worked.
> sp_addlinkedserver N'ACT-S92', ' ', N'SQLOLEDB', N'ACT-S92'
> So, at this point I should have ACT-S91 and ACT-S92 linked. But when I
run
> a query from ACT-S91 trying to access the data on ACT-S92 I get the
> following error:
> Server: Msg 7302, Level 16, State 1, Line 1
> Could not create an instance of OLE DB provider 'SQLOLEDB'.
> OLE DB error trace [Non-interface error: CoCreate of DSO for SQLOLEDB
> returned 0x8007007f].
>
> I assume I am missing something in setting up the link. But from the
> documentation I can't determine what that something might be. Any help?
>
> --
> Edward Fultz
> Sr. Software Engineer
> Pamet Systems
> Acton, Ma
> www.pametsystems.com
>
sql

Linked Server Issue

I am trying to set up a link between 2 SQLServer databases. One is on
server named ACT-S91 and the other is on server named ACT-S92. I ran the
following statement on ACT-S91 - which said it worked.
sp_addlinkedserver N'ACT-S92', ' ', N'SQLOLEDB', N'ACT-S92'
So, at this point I should have ACT-S91 and ACT-S92 linked. But when I run
a query from ACT-S91 trying to access the data on ACT-S92 I get the
following error:
Server: Msg 7302, Level 16, State 1, Line 1
Could not create an instance of OLE DB provider 'SQLOLEDB'.
OLE DB error trace [Non-interface error: CoCreate of DSO for SQLOLEDB
returned 0x8007007f].
I assume I am missing something in setting up the link. But from the
documentation I can't determine what that something might be. Any help?
Edward Fultz
Sr. Software Engineer
Pamet Systems
Acton, Ma
www.pametsystems.comIf you look at the properties of the linked Server in Enterprise Manager,
under Security, what context is the connection to the linked server being
made.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||I used the one that says Be made using the login's current security context.
Since I can get into both servers I assumed I would have no problems. I
have no specific local logins defined. We use windows authentication on
both machines. So, if I can get into both machines shouldn't this have been
right?
Edward Fultz
Sr. Software Engineer
Pamet Systems
Acton, Ma
www.pametsystems.com
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:hXKEeD%23UEHA.2616@.cpmsftngxa10.phx.gbl...
> If you look at the properties of the linked Server in Enterprise Manager,
> under Security, what context is the connection to the linked server being
> made.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>|||I tried setting a specific security user id and password and that, also,
provided the same error.
Edward Fultz
Sr. Software Engineer
Pamet Systems
Acton, Ma
www.pametsystems.com
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:hXKEeD%23UEHA.2616@.cpmsftngxa10.phx.gbl...
> If you look at the properties of the linked Server in Enterprise Manager,
> under Security, what context is the connection to the linked server being
> made.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>|||Figured out the problem appears to be that you can't use the SQLServer
option. But on our server there are no providers loaded so we are
researching why this is and how to fix it. Anyone have any ideas how to get
providers loaded? We have 2 other servers and the providers show up there
fine. The server on which they don't show up is a Server2003 server running
SQLServer2000 (was recently upgraded to Server2003).
Edward Fultz
Sr. Software Engineer
Pamet Systems
Acton, Ma
www.pametsystems.com
"Ed Fultz" <efultz@.yahoo.com> wrote in message
news:%235FnB88UEHA.4092@.TK2MSFTNGP11.phx.gbl...
> I am trying to set up a link between 2 SQLServer databases. One is on
> server named ACT-S91 and the other is on server named ACT-S92. I ran the
> following statement on ACT-S91 - which said it worked.
> sp_addlinkedserver N'ACT-S92', ' ', N'SQLOLEDB', N'ACT-S92'
> So, at this point I should have ACT-S91 and ACT-S92 linked. But when I
run
> a query from ACT-S91 trying to access the data on ACT-S92 I get the
> following error:
> Server: Msg 7302, Level 16, State 1, Line 1
> Could not create an instance of OLE DB provider 'SQLOLEDB'.
> OLE DB error trace [Non-interface error: CoCreate of DSO for SQLOLEDB
> returned 0x8007007f].
>
> I assume I am missing something in setting up the link. But from the
> documentation I can't determine what that something might be. Any help?
>
> --
> Edward Fultz
> Sr. Software Engineer
> Pamet Systems
> Acton, Ma
> www.pametsystems.com
>

Linked server issue

I have a SQL Server linked to a non-SQL server using ODBC on our
internal network. Every query I run on the linked server from SQL
Server takes exactly 1 minute longer than to run the query via ASP
page and ODBC. I'm suspecting some strange handshake is going on when
the server is linked, and was wondering if there was a way around it?
I'd rather not tap into the remote server via ASP pages, but rather
have the ASP pages connect to the SQL Server.
Thanks
CateDo you run this query using 4 part naming or OPENQUERY?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Linked Server Issue

I am trying to set up a link between 2 SQLServer databases. One is on
server named ACT-S91 and the other is on server named ACT-S92. I ran the
following statement on ACT-S91 - which said it worked.
sp_addlinkedserver N'ACT-S92', ' ', N'SQLOLEDB', N'ACT-S92'
So, at this point I should have ACT-S91 and ACT-S92 linked. But when I run
a query from ACT-S91 trying to access the data on ACT-S92 I get the
following error:
Server: Msg 7302, Level 16, State 1, Line 1
Could not create an instance of OLE DB provider 'SQLOLEDB'.
OLE DB error trace [Non-interface error: CoCreate of DSO for SQLOLEDB
returned 0x8007007f].
I assume I am missing something in setting up the link. But from the
documentation I can't determine what that something might be. Any help?
--
Edward Fultz
Sr. Software Engineer
Pamet Systems
Acton, Ma
www.pametsystems.comIf you look at the properties of the linked Server in Enterprise Manager,
under Security, what context is the connection to the linked server being
made.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||I used the one that says Be made using the login's current security context.
Since I can get into both servers I assumed I would have no problems. I
have no specific local logins defined. We use windows authentication on
both machines. So, if I can get into both machines shouldn't this have been
right?
--
Edward Fultz
Sr. Software Engineer
Pamet Systems
Acton, Ma
www.pametsystems.com
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:hXKEeD%23UEHA.2616@.cpmsftngxa10.phx.gbl...
> If you look at the properties of the linked Server in Enterprise Manager,
> under Security, what context is the connection to the linked server being
> made.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>|||I tried setting a specific security user id and password and that, also,
provided the same error.
--
Edward Fultz
Sr. Software Engineer
Pamet Systems
Acton, Ma
www.pametsystems.com
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:hXKEeD%23UEHA.2616@.cpmsftngxa10.phx.gbl...
> If you look at the properties of the linked Server in Enterprise Manager,
> under Security, what context is the connection to the linked server being
> made.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>|||Figured out the problem appears to be that you can't use the SQLServer
option. But on our server there are no providers loaded so we are
researching why this is and how to fix it. Anyone have any ideas how to get
providers loaded? We have 2 other servers and the providers show up there
fine. The server on which they don't show up is a Server2003 server running
SQLServer2000 (was recently upgraded to Server2003).
--
Edward Fultz
Sr. Software Engineer
Pamet Systems
Acton, Ma
www.pametsystems.com
"Ed Fultz" <efultz@.yahoo.com> wrote in message
news:%235FnB88UEHA.4092@.TK2MSFTNGP11.phx.gbl...
> I am trying to set up a link between 2 SQLServer databases. One is on
> server named ACT-S91 and the other is on server named ACT-S92. I ran the
> following statement on ACT-S91 - which said it worked.
> sp_addlinkedserver N'ACT-S92', ' ', N'SQLOLEDB', N'ACT-S92'
> So, at this point I should have ACT-S91 and ACT-S92 linked. But when I
run
> a query from ACT-S91 trying to access the data on ACT-S92 I get the
> following error:
> Server: Msg 7302, Level 16, State 1, Line 1
> Could not create an instance of OLE DB provider 'SQLOLEDB'.
> OLE DB error trace [Non-interface error: CoCreate of DSO for SQLOLEDB
> returned 0x8007007f].
>
> I assume I am missing something in setting up the link. But from the
> documentation I can't determine what that something might be. Any help?
>
> --
> Edward Fultz
> Sr. Software Engineer
> Pamet Systems
> Acton, Ma
> www.pametsystems.com
>

Linked Server in SQL Server 2000 for Active Directory.

I am trying to create linked servers for Active Directory from SQL
Server 2000.
Can somebody tell me how to actually create it.
I have no idea what are those parameters.
1. Product
2. Provider
3. Provider String
4. Data Source
5. Catalog
6. Location
Also on the security tab what is the security level it requires for
proper authentication.
Thanks
Hi
Check previous postings:
http://tinyurl.com/6l76n
http://tinyurl.com/5d474
You may also want to check out the newsgroup
microsoft.public.active.directXory.interfaces
John
"tension" <kumar877@.gmail.com> wrote in message
news:1113118999.884752.319920@.z14g2000cwz.googlegr oups.com...
>I am trying to create linked servers for Active Directory from SQL
> Server 2000.
> Can somebody tell me how to actually create it.
> I have no idea what are those parameters.
> 1. Product
> 2. Provider
> 3. Provider String
> 4. Data Source
> 5. Catalog
> 6. Location
> Also on the security tab what is the security level it requires for
> proper authentication.
> Thanks
>
|||tension wrote:
> Dear John,
> I have used the following to add the linked server.
> sp_addlinkedserver 'ADSI', 'Active Directory Service
> Interfaces', 'ADSDSOObject', 'adsdatasource'
>
> And I am querying using the following.
> SELECT * INTO #adtmptbl FROM OpenQuery(ADSI,
> 'SELECT * FROM ''LDAP://OU=users,OU=laptop,DC=192.168.0.8,DC=local''
> where objectClass = ''User''')
> and getting the error
> Server: Msg 7321, Level 16, State 2, Line 1
> An error occurred while preparing a query for execution against OLE
DB
> provider 'ADSDSOObject'.
> OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
> ICommandPrepare::Prepare returned 0x80040e14].
> Any idea's? may be my OU and DC names are wrong.. how to find the
right
> one's.
> I am using windows 2003 server and sql server 2000 SP3.
> Thanks, your reply to this message is greately appreciated.
> Kumar

Linked Server in SQL Server 2000 for Active Directory.

I am trying to create linked servers for Active Directory from SQL
Server 2000.
Can somebody tell me how to actually create it.
I have no idea what are those parameters.
1. Product
2. Provider
3. Provider String
4. Data Source
5. Catalog
6. Location
Also on the security tab what is the security level it requires for
proper authentication.
ThanksHi
Check previous postings:
http://tinyurl.com/6l76n
http://tinyurl.com/5d474
You may also want to check out the newsgroup
microsoft.public.active.direct_ory.interfaces
John
"tension" <kumar877@.gmail.com> wrote in message
news:1113118999.884752.319920@.z14g2000cwz.googlegroups.com...
>I am trying to create linked servers for Active Directory from SQL
> Server 2000.
> Can somebody tell me how to actually create it.
> I have no idea what are those parameters.
> 1. Product
> 2. Provider
> 3. Provider String
> 4. Data Source
> 5. Catalog
> 6. Location
> Also on the security tab what is the security level it requires for
> proper authentication.
> Thanks
>|||tension wrote:
> Dear John,
> I have used the following to add the linked server.
> sp_addlinkedserver 'ADSI', 'Active Directory Service
> Interfaces', 'ADSDSOObject', 'adsdatasource'
>
> And I am querying using the following.
> SELECT * INTO #adtmptbl FROM OpenQuery(ADSI,
> 'SELECT * FROM ''LDAP://OU=users,OU=laptop,DC=192.168.0.8,DC=local''
> where objectClass = ''User''')
> and getting the error
> Server: Msg 7321, Level 16, State 2, Line 1
> An error occurred while preparing a query for execution against OLE
DB
> provider 'ADSDSOObject'.
> OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
> ICommandPrepare::Prepare returned 0x80040e14].
> Any idea's? may be my OU and DC names are wrong.. how to find the
right
> one's.
> I am using windows 2003 server and sql server 2000 SP3.
> Thanks, your reply to this message is greately appreciated.
> Kumar

Linked server from SQL Server to Access db on network share?

I have an Access database that exists on a network share
that I'm trying to link to as a linked server from SQL
Server. Sitting at the SQL server machine, I can create
the linked server (both by EM and sql), connect to the
access db, query data, see tables, etc with no problems.
However, if I go to another machine and try to access the
linked database, either through EM or through sql, I get
this error message:
Server: Msg 7399, Level 16, State 1, Procedure
sp_tables_ex, Line 20
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an
error.
[OLE/DB provider returned message: The Microsoft Jet
database engine cannot open the
file '\\server\path\database.mdb'. It is already opened
exclusively by another user, or you need permission to
view its data.]
OLE DB error trace [OLE/DB
Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
The SQL server service is running as a domain user with
full access to the network share.
Any help?Hi, I believe I answered this same question that was cross posted to a few
newsgroups...
Steve
"Hobbes" <anonymous@.discussions.microsoft.com> wrote in message
news:1372b01c3f726$aff2aac0$a501280a@.phx
.gbl...
> I have an Access database that exists on a network share
> that I'm trying to link to as a linked server from SQL
> Server. Sitting at the SQL server machine, I can create
> the linked server (both by EM and sql), connect to the
> access db, query data, see tables, etc with no problems.
> However, if I go to another machine and try to access the
> linked database, either through EM or through sql, I get
> this error message:
> Server: Msg 7399, Level 16, State 1, Procedure
> sp_tables_ex, Line 20
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an
> error.
> [OLE/DB provider returned message: The Microsoft Jet
> database engine cannot open the
> file '\\server\path\database.mdb'. It is already opened
> exclusively by another user, or you need permission to
> view its data.]
> OLE DB error trace [OLE/DB
> Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBInitialize::Initialize returned 0x80004005: ].
> The SQL server service is running as a domain user with
> full access to the network share.
> Any help?
>

Linked server from SQL Server to Access db on network share?

I have an Access database that exists on a network share
that I'm trying to link to as a linked server from SQL
Server. Sitting at the SQL server machine, I can create
the linked server (both by EM and sql), connect to the
access db, query data, see tables, etc with no problems.
However, if I go to another machine and try to access the
linked database, either through EM or through sql, I get
this error message:
Server: Msg 7399, Level 16, State 1, Procedure
sp_tables_ex, Line 20
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an
error.
[OLE/DB provider returned message: The Microsoft Jet
database engine cannot open the
file '\\server\path\database.mdb'. It is already opened
exclusively by another user, or you need permission to
view its data.]
OLE DB error trace [OLE/DB
Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
The SQL server service is running as a domain user with
full access to the network share.
Any help?"Hobbes159" <anonymous@.discussions.microsoft.com> wrote in message
news:eQDAz289DHA.3436@.tk2msftngp13.phx.gbl...
> I have an Access database that exists on a network share
> that I'm trying to link to as a linked server from SQL
> Server. Sitting at the SQL server machine, I can create
> the linked server (both by EM and sql), connect to the
> access db, query data, see tables, etc with no problems.
> However, if I go to another machine and try to access the
> linked database, either through EM or through sql, I get
> this error message:
> Server: Msg 7399, Level 16, State 1, Procedure
> sp_tables_ex, Line 20
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an
> error.
> [OLE/DB provider returned message: The Microsoft Jet
> database engine cannot open the
> file '\\server\path\database.mdb'. It is already opened
> exclusively by another user, or you need permission to
> view its data.]
> OLE DB error trace [OLE/DB
> Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBInitialize::Initialize returned 0x80004005: ].
> The SQL server service is running as a domain user with
> full access to the network share.
Does the domain user account have MODIFY permissions at the NTFS file layer?
The sql server service account must have the ability to create files, such
as the associated *.ldb file.
Steve|||Yes, the domain account has full access to the share (read, write, delete).
The linked database works fine from the SQL server machine, it just won't
work from any other machine.
"Steve Thompson" <SteveThompson@.nomail.please> wrote in message
news:OWnvfl99DHA.1428@.TK2MSFTNGP12.phx.gbl...
> "Hobbes159" <anonymous@.discussions.microsoft.com> wrote in message
> news:eQDAz289DHA.3436@.tk2msftngp13.phx.gbl...
> Does the domain user account have MODIFY permissions at the NTFS file
layer?
> The sql server service account must have the ability to create files, such
> as the associated *.ldb file.
> Steve
>|||Have you installed the latest Jet service pack and MDAC release on your SQL
Server?
Steve
"Hobbes159" <anonymous@.discussions.microsoft.com> wrote in message
news:e3$m6p#9DHA.3176@.TK2MSFTNGP11.phx.gbl...
> Yes, the domain account has full access to the share (read, write,
delete).
> The linked database works fine from the SQL server machine, it just won't
> work from any other machine.
> "Steve Thompson" <SteveThompson@.nomail.please> wrote in message
> news:OWnvfl99DHA.1428@.TK2MSFTNGP12.phx.gbl...
> layer?
such
>|||Yup, everything is up to date on both machines...
"Steve Thompson" <SteveThompson@.nomail.please> wrote in message
news:uNwFIN$9DHA.2932@.TK2MSFTNGP10.phx.gbl...
> Have you installed the latest Jet service pack and MDAC release on your
SQL
> Server?
> Steve
> "Hobbes159" <anonymous@.discussions.microsoft.com> wrote in message
> news:e3$m6p#9DHA.3176@.TK2MSFTNGP11.phx.gbl...
> delete).
won't
> such
>|||Under the Linked Server properties, security tab -- which security context
did you select?
Steve
"Hobbes159" <anonymous@.discussions.microsoft.com> wrote in message
news:e3$m6p#9DHA.3176@.TK2MSFTNGP11.phx.gbl...
> Yes, the domain account has full access to the share (read, write,
delete).
> The linked database works fine from the SQL server machine, it just won't
> work from any other machine.
> "Steve Thompson" <SteveThompson@.nomail.please> wrote in message
> news:OWnvfl99DHA.1428@.TK2MSFTNGP12.phx.gbl...
> layer?
such
>

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

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

Linked Server Error Between SQL Server and Oracle

I have used Linked Servers to hook up to an Oracle 9i database in SQL
Server 2000. I can see all the Tables and Views when I look in the
linked server section. I can also run the following SQL statement :

Select *
from Orcldb..SYSTEM.CrossTabSaveSites

and get a return result. The problem I realize is that some of the
tables are going to be quite larger and I can't wait for SQL Server to
download all the data and parse it itself, so I tried to use a
passthrough query like so :

SELECT * FROM OPENQUERY(OrclDB, 'SELECT * FROM
SYSTEM.CrossTabSaveSites')

Unfortunately this gives me the error message :

Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'MSDAORA'.
[OLE/DB provider returned message: ORA-00942: table or view does not
exist
]

There is also the issue of speed. It takes about 15 seconds to come
back to me to tell me that the table/view doesn't exist, but sometimes
it comes back immediately. Is this something with the user
permissions in Oracle? (Don't really know much about Oracle) It just
seems like a very long time to wait just to let me know that the item
doesn't exist.

Any help would be greatly appreciated,

-Jevon Thurlow"Jevon" <jevonthurlow@.hotmail.com> wrote in message
news:34e7f100.0404080933.2e40e19e@.posting.google.c om...
> I have used Linked Servers to hook up to an Oracle 9i database in SQL
> Server 2000. I can see all the Tables and Views when I look in the
> linked server section. I can also run the following SQL statement :
> Select *
> from Orcldb..SYSTEM.CrossTabSaveSites
> and get a return result. The problem I realize is that some of the
> tables are going to be quite larger and I can't wait for SQL Server to
> download all the data and parse it itself, so I tried to use a
> passthrough query like so :
> SELECT * FROM OPENQUERY(OrclDB, 'SELECT * FROM
> SYSTEM.CrossTabSaveSites')
> Unfortunately this gives me the error message :
> Server: Msg 7321, Level 16, State 2, Line 1
> An error occurred while preparing a query for execution against OLE DB
> provider 'MSDAORA'.
> [OLE/DB provider returned message: ORA-00942: table or view does not
> exist
> ]
> There is also the issue of speed. It takes about 15 seconds to come
> back to me to tell me that the table/view doesn't exist, but sometimes
> it comes back immediately. Is this something with the user
> permissions in Oracle? (Don't really know much about Oracle) It just
> seems like a very long time to wait just to let me know that the item
> doesn't exist.
> Any help would be greatly appreciated,
> -Jevon Thurlow

I can't tell you anything about the performance issue, but the ORA-00942
error is probably because Oracle is case-sensitive, and object names are
usually in upper case:

http://support.microsoft.com/defaul...kb;EN-US;240340
http://support.microsoft.com/defaul...kb;EN-US;280106

Simon

Linked server - Calling AS400 SP

Hi,
I have created a linked server to AS400 DB2 database on my sqlserver
2000 server.
I am using IBM iSeries Client Access drivers on the server.
The connection has been made properly and to confirm that i ran a query
to select a table from AS400 using openquery syntax.
I am trying to call a stored procedure in AS400 that either returns 0 or
1. The stored procedre executes from vb through a dsn without any problem.
However calling the stored proc using the linked server is a nightmare.
If I use the openquery syntax which is ...
select * from openquery(as400serv1,'CALL QGPL.CCVALX ( ''1'' ,
''123343453454'' , ''0205'' , ''CC'') ' )
I get error ...
Server: Msg 7357, Level 16, State 2, Line 2
Could not process object 'CALL QGPL.CCVALX ( '1' , '123343453454' , '0205' ,
'CC') '. The OLE DB provider 'IBMDA400' indicates that the object has no
columns.
If I use the execute method as in ...
exec as400serv1.wima400.QGPL.CCVALX '1','123343453454','0205','CC'
I get err..
Server: Msg 7212, Level 17, State 1, Line 1
Could not execute procedure 'CCVALX' on remote server 'as400serv1'.
[OLE/DB provider returned message: SQL0104: Token { was not valid. Valid
tokens: ( END SET CALL DROP FREE HOLD LOCK OPEN WITH ALTER BEGIN CLOSE.
Cause . . . . . : A syntax error was detected at token {. Token { is not
a valid token. A partial list of valid tokens is ( END SET CALL DROP FREE
HOLD LOCK OPEN WITH ALTER BEGIN CLOSE. This list assumes that the statement
is correct up to the token. The error may be earlier in the statement, but
the syntax of the statement appears to be valid up to this point. Recovery
. . . : Do one or more of the following and try the request again: --
Verify the SQL statement in the area of the token {. Correct the statement.
The error could be a missing comma or quotation mark, it could be a
misspelled word, or it could be related to the order of clauses. -- If the
error token is <END-OF-STATEMENT>, correct the SQL statement because it does
not end with a valid clause.]
Please, can anyone help me understanding what is going wrong... it very
crucial i get this working from the linked server.
Thanks in advance
Remy De Almeida> Hi,
> I have created a linked server to AS400 DB2 database on my sqlserver
> 2000 server.
> I am using IBM iSeries Client Access drivers on the server.
> The connection has been made properly and to confirm that i ran a
query
> to select a table from AS400 using openquery syntax.
> I am trying to call a stored procedure in AS400 that either returns 0
or
> 1. The stored procedre executes from vb through a dsn without any problem.
> However calling the stored proc using the linked server is a nightmare.
> If I use the openquery syntax which is ...
> select * from openquery(as400serv1,'CALL QGPL.CCVALX ( ''1'' ,
> ''123343453454'' , ''0205'' , ''CC'') ' )
> I get error ...
> Server: Msg 7357, Level 16, State 2, Line 2
> Could not process object 'CALL QGPL.CCVALX ( '1' , '123343453454' ,
'0205' ,
> 'CC') '. The OLE DB provider 'IBMDA400' indicates that the object has no
> columns.
>
> If I use the execute method as in ...
> exec as400serv1.wima400.QGPL.CCVALX '1','123343453454','0205','CC'
> I get err..
> Server: Msg 7212, Level 17, State 1, Line 1
> Could not execute procedure 'CCVALX' on remote server 'as400serv1'.
> [OLE/DB provider returned message: SQL0104: Token { was not valid. Valid
> tokens: ( END SET CALL DROP FREE HOLD LOCK OPEN WITH ALTER BEGIN CLOSE.
> Cause . . . . . : A syntax error was detected at token {. Token { is
not
> a valid token. A partial list of valid tokens is ( END SET CALL DROP FREE
> HOLD LOCK OPEN WITH ALTER BEGIN CLOSE. This list assumes that the
statement
> is correct up to the token. The error may be earlier in the statement,
but
> the syntax of the statement appears to be valid up to this point. Recovery
> . . . : Do one or more of the following and try the request again: --
> Verify the SQL statement in the area of the token {. Correct the
statement.
> The error could be a missing comma or quotation mark, it could be a
> misspelled word, or it could be related to the order of clauses. -- If the
> error token is <END-OF-STATEMENT>, correct the SQL statement because it
does
> not end with a valid clause.]
> Please, can anyone help me understanding what is going wrong... it very
> crucial i get this working from the linked server.
> Thanks in advance
> Remy De Almeida
--
You need to determine if the third-party OLE-DB provider supports the
calling of stored procedures from the remote server. Speak to your OLE-DB
provider vendor about this.
Hope this helps,
--
Eric Cárdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.