Hi All
I am trying to create a linked server to MYSQL and it is not listed in the
Linked Server providers in SQL Server 2005. How do I go about adding a
provider that is not in the existing provider list?
Any ideas on how to either add the provider or create the linked server
would be appreciated.
Thanks
- DavidYou need to obtain the needed drivers or providers for MySQL
and install it on the SQL Server box. There are third party
drivers but you can download free drivers from:
http://dev.mysql.com/downloads/
-Sue
On Wed, 30 Aug 2006 06:11:02 -0700, David
<David@.discussions.microsoft.com> wrote:
>Hi All
>I am trying to create a linked server to MYSQL and it is not listed in the
>Linked Server providers in SQL Server 2005. How do I go about adding a
>provider that is not in the existing provider list?
>Any ideas on how to either add the provider or create the linked server
>would be appreciated.
>Thanks
>- Davidsql
Showing posts with label create. Show all posts
Showing posts with label create. Show all posts
Friday, March 30, 2012
Linked Server Provider
Hi All
I am trying to create a linked server to mysql and it is not listed in the
Linked Server providers in SQL Server 2005. How do I go about adding a
provider that is not in the existing provider list?
Any ideas on how to either add the provider or create the linked server
would be appreciated.
Thanks
- DavidYou need to obtain the needed drivers or providers for MySQL
and install it on the SQL Server box. There are third party
drivers but you can download free drivers from:
http://dev.mysql.com/downloads/
-Sue
On Wed, 30 Aug 2006 06:11:02 -0700, David
<David@.discussions.microsoft.com> wrote:
>Hi All
>I am trying to create a linked server to mysql and it is not listed in the
>Linked Server providers in SQL Server 2005. How do I go about adding a
>provider that is not in the existing provider list?
>Any ideas on how to either add the provider or create the linked server
>would be appreciated.
>Thanks
>- David
I am trying to create a linked server to mysql and it is not listed in the
Linked Server providers in SQL Server 2005. How do I go about adding a
provider that is not in the existing provider list?
Any ideas on how to either add the provider or create the linked server
would be appreciated.
Thanks
- DavidYou need to obtain the needed drivers or providers for MySQL
and install it on the SQL Server box. There are third party
drivers but you can download free drivers from:
http://dev.mysql.com/downloads/
-Sue
On Wed, 30 Aug 2006 06:11:02 -0700, David
<David@.discussions.microsoft.com> wrote:
>Hi All
>I am trying to create a linked server to mysql and it is not listed in the
>Linked Server providers in SQL Server 2005. How do I go about adding a
>provider that is not in the existing provider list?
>Any ideas on how to either add the provider or create the linked server
>would be appreciated.
>Thanks
>- David
Linked Server Problems with all providers
Im having problems with linked servers on one particular computer and am out
of ideas. The box is an XP SP2, SQL server 2000 SP3. When i create a linke
d
server of any type, clicking on "tables" or "views" in the enterprise manage
r
list, gives the following error:
Error 7399: OLE DB provider 'SQLOLEDB' reported an error.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBinitialize::Initialize
returned 0x80004005: ].
This particular LSN is pointing to another SQL server machine. Regardless
of what provider i choose, i get the same error (the error message changes
the provider based on the provider of the LSN).
I am an administrator on both boxes, have disabled virus protection, the
windows firewall on the XP box is disabled (by a group policy on the domain)
,
and i have enabled inbound and outbound traffic in the dcomcnfg.exe.
LSN's created on other computers work fine. Any ideas?You can get this error with timeout issues. Refer to the
following:
Error message when you execute a linked server query in SQL
Server: "Timeout Expired"
http://support.microsoft.com/?id=314530
You can also double check the security mappings for the
linked servers.
You will also want to check the connectivity - try pinging
the other servers from the box you are having problems with.
Ping by server name and IP.
-Sue
On Wed, 15 Mar 2006 06:56:28 -0800, John Kurtis <John
Kurtis@.discussions.microsoft.com> wrote:
>Im having problems with linked servers on one particular computer and am ou
t
>of ideas. The box is an XP SP2, SQL server 2000 SP3. When i create a link
ed
>server of any type, clicking on "tables" or "views" in the enterprise manag
er
>list, gives the following error:
>Error 7399: OLE DB provider 'SQLOLEDB' reported an error.
>OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBinitialize::Initializ
e
>returned 0x80004005: ].
>This particular LSN is pointing to another SQL server machine. Regardless
>of what provider i choose, i get the same error (the error message changes
>the provider based on the provider of the LSN).
>I am an administrator on both boxes, have disabled virus protection, the
>windows firewall on the XP box is disabled (by a group policy on the domain
),
>and i have enabled inbound and outbound traffic in the dcomcnfg.exe.
>LSN's created on other computers work fine. Any ideas?|||Thanks for the reply. However im 99% sure that its not a timeout issue. I
get the error immediately after i click on "tables" or "views" in enterprise
manager.
Pings from the box that is having problems come back fine, both with IP as
well as name.
of ideas. The box is an XP SP2, SQL server 2000 SP3. When i create a linke
d
server of any type, clicking on "tables" or "views" in the enterprise manage
r
list, gives the following error:
Error 7399: OLE DB provider 'SQLOLEDB' reported an error.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBinitialize::Initialize
returned 0x80004005: ].
This particular LSN is pointing to another SQL server machine. Regardless
of what provider i choose, i get the same error (the error message changes
the provider based on the provider of the LSN).
I am an administrator on both boxes, have disabled virus protection, the
windows firewall on the XP box is disabled (by a group policy on the domain)
,
and i have enabled inbound and outbound traffic in the dcomcnfg.exe.
LSN's created on other computers work fine. Any ideas?You can get this error with timeout issues. Refer to the
following:
Error message when you execute a linked server query in SQL
Server: "Timeout Expired"
http://support.microsoft.com/?id=314530
You can also double check the security mappings for the
linked servers.
You will also want to check the connectivity - try pinging
the other servers from the box you are having problems with.
Ping by server name and IP.
-Sue
On Wed, 15 Mar 2006 06:56:28 -0800, John Kurtis <John
Kurtis@.discussions.microsoft.com> wrote:
>Im having problems with linked servers on one particular computer and am ou
t
>of ideas. The box is an XP SP2, SQL server 2000 SP3. When i create a link
ed
>server of any type, clicking on "tables" or "views" in the enterprise manag
er
>list, gives the following error:
>Error 7399: OLE DB provider 'SQLOLEDB' reported an error.
>OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBinitialize::Initializ
e
>returned 0x80004005: ].
>This particular LSN is pointing to another SQL server machine. Regardless
>of what provider i choose, i get the same error (the error message changes
>the provider based on the provider of the LSN).
>I am an administrator on both boxes, have disabled virus protection, the
>windows firewall on the XP box is disabled (by a group policy on the domain
),
>and i have enabled inbound and outbound traffic in the dcomcnfg.exe.
>LSN's created on other computers work fine. Any ideas?|||Thanks for the reply. However im 99% sure that its not a timeout issue. I
get the error immediately after i click on "tables" or "views" in enterprise
manager.
Pings from the box that is having problems come back fine, both with IP as
well as name.
Monday, March 26, 2012
Linked Server Oracle 10g problem
I have followed the examples from MS. to create the link between my MSSQL200
5
and Oracle 10g
I have all of the Oracle client installed on the server.
I have a net service defined.
Here is what I ran to create link:
EXEC sp_addlinkedserver 'OraLinkServer2', 'Oracle', 'MSDAORA', 'HPRD89'
EXEC sp_addlinkedsrvlogin 'OraLinkServer2', 'False' ,Null, 'spwr07', 'spwr07
'
Here is the query:
select count(*) from OraLinkServer2..sysadm.SUN_PUBT
Here is the error:
OLE DB provider "MSDAORA" for linked server "OraLinkServer2" returned
message "ORA-12154: TNS:could not resolve the connect identifier specified
".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDAORA" for
linked server "OraLinkServer2".
What am I doing wrong all the oracle tools connect just fine. I need SQL2005
to connect?
Thanks
GregHob,
I've had the same problem. I used the openquery syntax to work around
the problem. This also has the advantage of pushing the work to the
Oracle Server.
So.. try:
select * from
openquery(OraLinkServer2,
' select count(*) from SUN_PUBT
'
)
No -- you don't need the formating and returns -- I just use them to
make it easy for me to read the Oracle Commands separate from the
MS-SQL commands.
If you find a better solution, please post it.
You also might want to read:
http://www.sqlservercentral.com/col...br />
art3.asp
It has the best set of instructions for getting the Instant Client to
work.
Good luck,
Peter Ryan
Hob_Naggers wrote:
> I have followed the examples from MS. to create the link between my MSSQL2
005
> and Oracle 10g
> I have all of the Oracle client installed on the server.
> I have a net service defined.
> Here is what I ran to create link:
> EXEC sp_addlinkedserver 'OraLinkServer2', 'Oracle', 'MSDAORA', 'HPRD8
9'
> EXEC sp_addlinkedsrvlogin 'OraLinkServer2', 'False' ,Null, 'spwr07', 'spwr
07'
>
> Here is the query:
> select count(*) from OraLinkServer2..sysadm.SUN_PUBT
> Here is the error:
> OLE DB provider "MSDAORA" for linked server "OraLinkServer2" returned
> message "ORA-12154: TNS:could not resolve the connect identifier specified
> ".
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider "MSDAORA" for
> linked server "OraLinkServer2".
> What am I doing wrong all the oracle tools connect just fine. I need SQL20
05
> to connect?
> Thanks
> Greg|||Hob,
I've had the same problem. I used the openquery syntax to work around
the problem. This also has the advantage of pushing the work to the
Oracle Server.
So.. try:
select * from
openquery(OraLinkServer2,
' select count(*) from SUN_PUBT
'
)
No -- you don't need the formating and returns -- I just use them to
make it easy for me to read the Oracle Commands separate from the
MS-SQL commands.
If you find a better solution, please post it.
You also might want to read:
http://www.sqlservercentral.com/col...br />
art3.asp
It has the best set of instructions for getting the Instant Client to
work.
Good luck,
Peter Ryan
Hob_Naggers wrote:
> I have followed the examples from MS. to create the link between my MSSQL2
005
> and Oracle 10g
> I have all of the Oracle client installed on the server.
> I have a net service defined.
> Here is what I ran to create link:
> EXEC sp_addlinkedserver 'OraLinkServer2', 'Oracle', 'MSDAORA', 'HPRD8
9'
> EXEC sp_addlinkedsrvlogin 'OraLinkServer2', 'False' ,Null, 'spwr07', 'spwr
07'
>
> Here is the query:
> select count(*) from OraLinkServer2..sysadm.SUN_PUBT
> Here is the error:
> OLE DB provider "MSDAORA" for linked server "OraLinkServer2" returned
> message "ORA-12154: TNS:could not resolve the connect identifier specified
> ".
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider "MSDAORA" for
> linked server "OraLinkServer2".
> What am I doing wrong all the oracle tools connect just fine. I need SQL20
05
> to connect?
> Thanks
> Greg|||Hi Peter,
Thanks for the response, but I finally found the problem yesterday. The real
problem was not with SQL server, but rather it was a problem with the Oracle
config.
Though i had the TNSNames.ora file configured correctly the SQLNET.ora file
was where the problem lay. It was configured for ezconnect instead of
tnsnames. After this was ironed out the linking of the oracle db into sql wa
s
easy. One weird not though I found after getting the config right. When
querying the linked db. It is case sensitive with regards to column and tabl
e
names, even though on the Oracle side it is not case sensitive. I'm guessing
it has something to do with the translation being provided by the providers.
Thanks
Greg
"pryan64" wrote:
> Hob,
> I've had the same problem. I used the openquery syntax to work around
> the problem. This also has the advantage of pushing the work to the
> Oracle Server.
> So.. try:
> select * from
> openquery(OraLinkServer2,
> ' select count(*) from SUN_PUBT
> '
> )
> No -- you don't need the formating and returns -- I just use them to
> make it easy for me to read the Oracle Commands separate from the
> MS-SQL commands.
> If you find a better solution, please post it.
> You also might want to read:
> http://www.sqlservercentral.com/col... />
ypart3.asp
> It has the best set of instructions for getting the Instant Client to
> work.
> Good luck,
> Peter Ryan
> Hob_Naggers wrote:
>|||Hi Peter,
Thanks for the response, but I finally found the problem yesterday. The real
problem was not with SQL server, but rather it was a problem with the Oracle
config.
Though i had the TNSNames.ora file configured correctly the SQLNET.ora file
was where the problem lay. It was configured for ezconnect instead of
tnsnames. After this was ironed out the linking of the oracle db into sql wa
s
easy. One weird not though I found after getting the config right. When
querying the linked db. It is case sensitive with regards to column and tabl
e
names, even though on the Oracle side it is not case sensitive. I'm guessing
it has something to do with the translation being provided by the providers.
Thanks
Greg
"pryan64" wrote:
> Hob,
> I've had the same problem. I used the openquery syntax to work around
> the problem. This also has the advantage of pushing the work to the
> Oracle Server.
> So.. try:
> select * from
> openquery(OraLinkServer2,
> ' select count(*) from SUN_PUBT
> '
> )
> No -- you don't need the formating and returns -- I just use them to
> make it easy for me to read the Oracle Commands separate from the
> MS-SQL commands.
> If you find a better solution, please post it.
> You also might want to read:
> http://www.sqlservercentral.com/col... />
ypart3.asp
> It has the best set of instructions for getting the Instant Client to
> work.
> Good luck,
> Peter Ryan
> Hob_Naggers wrote:
>
5
and Oracle 10g
I have all of the Oracle client installed on the server.
I have a net service defined.
Here is what I ran to create link:
EXEC sp_addlinkedserver 'OraLinkServer2', 'Oracle', 'MSDAORA', 'HPRD89'
EXEC sp_addlinkedsrvlogin 'OraLinkServer2', 'False' ,Null, 'spwr07', 'spwr07
'
Here is the query:
select count(*) from OraLinkServer2..sysadm.SUN_PUBT
Here is the error:
OLE DB provider "MSDAORA" for linked server "OraLinkServer2" returned
message "ORA-12154: TNS:could not resolve the connect identifier specified
".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDAORA" for
linked server "OraLinkServer2".
What am I doing wrong all the oracle tools connect just fine. I need SQL2005
to connect?
Thanks
GregHob,
I've had the same problem. I used the openquery syntax to work around
the problem. This also has the advantage of pushing the work to the
Oracle Server.
So.. try:
select * from
openquery(OraLinkServer2,
' select count(*) from SUN_PUBT
'
)
No -- you don't need the formating and returns -- I just use them to
make it easy for me to read the Oracle Commands separate from the
MS-SQL commands.
If you find a better solution, please post it.
You also might want to read:
http://www.sqlservercentral.com/col...br />
art3.asp
It has the best set of instructions for getting the Instant Client to
work.
Good luck,
Peter Ryan
Hob_Naggers wrote:
> I have followed the examples from MS. to create the link between my MSSQL2
005
> and Oracle 10g
> I have all of the Oracle client installed on the server.
> I have a net service defined.
> Here is what I ran to create link:
> EXEC sp_addlinkedserver 'OraLinkServer2', 'Oracle', 'MSDAORA', 'HPRD8
9'
> EXEC sp_addlinkedsrvlogin 'OraLinkServer2', 'False' ,Null, 'spwr07', 'spwr
07'
>
> Here is the query:
> select count(*) from OraLinkServer2..sysadm.SUN_PUBT
> Here is the error:
> OLE DB provider "MSDAORA" for linked server "OraLinkServer2" returned
> message "ORA-12154: TNS:could not resolve the connect identifier specified
> ".
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider "MSDAORA" for
> linked server "OraLinkServer2".
> What am I doing wrong all the oracle tools connect just fine. I need SQL20
05
> to connect?
> Thanks
> Greg|||Hob,
I've had the same problem. I used the openquery syntax to work around
the problem. This also has the advantage of pushing the work to the
Oracle Server.
So.. try:
select * from
openquery(OraLinkServer2,
' select count(*) from SUN_PUBT
'
)
No -- you don't need the formating and returns -- I just use them to
make it easy for me to read the Oracle Commands separate from the
MS-SQL commands.
If you find a better solution, please post it.
You also might want to read:
http://www.sqlservercentral.com/col...br />
art3.asp
It has the best set of instructions for getting the Instant Client to
work.
Good luck,
Peter Ryan
Hob_Naggers wrote:
> I have followed the examples from MS. to create the link between my MSSQL2
005
> and Oracle 10g
> I have all of the Oracle client installed on the server.
> I have a net service defined.
> Here is what I ran to create link:
> EXEC sp_addlinkedserver 'OraLinkServer2', 'Oracle', 'MSDAORA', 'HPRD8
9'
> EXEC sp_addlinkedsrvlogin 'OraLinkServer2', 'False' ,Null, 'spwr07', 'spwr
07'
>
> Here is the query:
> select count(*) from OraLinkServer2..sysadm.SUN_PUBT
> Here is the error:
> OLE DB provider "MSDAORA" for linked server "OraLinkServer2" returned
> message "ORA-12154: TNS:could not resolve the connect identifier specified
> ".
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider "MSDAORA" for
> linked server "OraLinkServer2".
> What am I doing wrong all the oracle tools connect just fine. I need SQL20
05
> to connect?
> Thanks
> Greg|||Hi Peter,
Thanks for the response, but I finally found the problem yesterday. The real
problem was not with SQL server, but rather it was a problem with the Oracle
config.
Though i had the TNSNames.ora file configured correctly the SQLNET.ora file
was where the problem lay. It was configured for ezconnect instead of
tnsnames. After this was ironed out the linking of the oracle db into sql wa
s
easy. One weird not though I found after getting the config right. When
querying the linked db. It is case sensitive with regards to column and tabl
e
names, even though on the Oracle side it is not case sensitive. I'm guessing
it has something to do with the translation being provided by the providers.
Thanks
Greg
"pryan64" wrote:
> Hob,
> I've had the same problem. I used the openquery syntax to work around
> the problem. This also has the advantage of pushing the work to the
> Oracle Server.
> So.. try:
> select * from
> openquery(OraLinkServer2,
> ' select count(*) from SUN_PUBT
> '
> )
> No -- you don't need the formating and returns -- I just use them to
> make it easy for me to read the Oracle Commands separate from the
> MS-SQL commands.
> If you find a better solution, please post it.
> You also might want to read:
> http://www.sqlservercentral.com/col... />
ypart3.asp
> It has the best set of instructions for getting the Instant Client to
> work.
> Good luck,
> Peter Ryan
> Hob_Naggers wrote:
>|||Hi Peter,
Thanks for the response, but I finally found the problem yesterday. The real
problem was not with SQL server, but rather it was a problem with the Oracle
config.
Though i had the TNSNames.ora file configured correctly the SQLNET.ora file
was where the problem lay. It was configured for ezconnect instead of
tnsnames. After this was ironed out the linking of the oracle db into sql wa
s
easy. One weird not though I found after getting the config right. When
querying the linked db. It is case sensitive with regards to column and tabl
e
names, even though on the Oracle side it is not case sensitive. I'm guessing
it has something to do with the translation being provided by the providers.
Thanks
Greg
"pryan64" wrote:
> Hob,
> I've had the same problem. I used the openquery syntax to work around
> the problem. This also has the advantage of pushing the work to the
> Oracle Server.
> So.. try:
> select * from
> openquery(OraLinkServer2,
> ' select count(*) from SUN_PUBT
> '
> )
> No -- you don't need the formating and returns -- I just use them to
> make it easy for me to read the Oracle Commands separate from the
> MS-SQL commands.
> If you find a better solution, please post it.
> You also might want to read:
> http://www.sqlservercentral.com/col... />
ypart3.asp
> It has the best set of instructions for getting the Instant Client to
> work.
> Good luck,
> Peter Ryan
> Hob_Naggers wrote:
>
Friday, March 23, 2012
Linked Server Limit in SQL Server 2000
I am about to start working on a project where I would be required to
dynamically create linked servers during the execution of an
application. One of the requirements is for the Link Servers to be
created and dropped before and after the retrieval of the data. My
question is about any type of cap on the number of linked servers SQL
Server 2000 can have registered at any single time. If I find out that
there is some type of cap, I would need to look into another way to
deal with my linked server needs. Thanks for any help/information you
might be able to provide.TBlair (tblair23221@.yahoo.com) writes:
> I am about to start working on a project where I would be required to
> dynamically create linked servers during the execution of an
> application. One of the requirements is for the Link Servers to be
> created and dropped before and after the retrieval of the data. My
> question is about any type of cap on the number of linked servers SQL
> Server 2000 can have registered at any single time. If I find out that
> there is some type of cap, I would need to look into another way to
> deal with my linked server needs. Thanks for any help/information you
> might be able to provide.
dynamically create linked servers during the execution of an
application. One of the requirements is for the Link Servers to be
created and dropped before and after the retrieval of the data. My
question is about any type of cap on the number of linked servers SQL
Server 2000 can have registered at any single time. If I find out that
there is some type of cap, I would need to look into another way to
deal with my linked server needs. Thanks for any help/information you
might be able to provide.TBlair (tblair23221@.yahoo.com) writes:
> I am about to start working on a project where I would be required to
> dynamically create linked servers during the execution of an
> application. One of the requirements is for the Link Servers to be
> created and dropped before and after the retrieval of the data. My
> question is about any type of cap on the number of linked servers SQL
> Server 2000 can have registered at any single time. If I find out that
> there is some type of cap, I would need to look into another way to
> deal with my linked server needs. Thanks for any help/information you
> might be able to provide.
I have no idea whether there are any restriction, but since a linked
server is really just an alias which is being looked up in sysservers
at run time, it is not likely that there should be an issue.
But, hm, what is there is a numeric id in sysservers? Ah, there is,
and it's smallint. So that gives you an upper limit of 32767 linked
servers. Hm, if you add and remove servers constantly, could you still
run out of ids? I tested by dropping a server with id = 5 (I have 19
linked servers), and adding a new. The new server got id = 5, so gaps
are being filled.
Looks like you will be able to sleep well at night.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Wednesday, March 21, 2012
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
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.directory.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
>|||Dear John,
I have actually created linked server using following
sp_addlinkedserver
sp_addlinkedserver 'ADSI', 'Active Directory Service
Interfaces', 'ADSDSOObject', 'adsdatasource'
SELECT * INTO #adtmptbl FROM OpenQuery(ADSI,
'SELECT * FROM ''LDAP://OU=users,OU=laptop,DC=192.168.0.8,DC=local''
where objectClass = ''User''')
I have used the above to query.. but it is giving the following 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?
Thanks|||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|||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
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.directory.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
>|||Dear John,
I have actually created linked server using following
sp_addlinkedserver
sp_addlinkedserver 'ADSI', 'Active Directory Service
Interfaces', 'ADSDSOObject', 'adsdatasource'
SELECT * INTO #adtmptbl FROM OpenQuery(ADSI,
'SELECT * FROM ''LDAP://OU=users,OU=laptop,DC=192.168.0.8,DC=local''
where objectClass = ''User''')
I have used the above to query.. but it is giving the following 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?
Thanks|||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|||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
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 SQL2005 to SQL2000
Hi
I use the following SQL statements to create a link server. RemoteServerName is an SQL2000 and I am executing this in another machine which is SQL2005.
Though the link server is created successfully, I am not able to se the tables under it so could not query anything.
-
USE [master]
EXEC master.dbo.sp_addlinkedserver @.server = N'RemoteServerName\InstanceName', @.srvproduct=N'SQL Server'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'collation compatible', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'data access', @.optvalue=N'true'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'rpc',@.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'rpc out', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'connect timeout', @.optvalue=N'0'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'collation name', @.optvalue=null
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'query timeout', @.optvalue=N'0'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'use remote collation', @.optvalue=N'true'
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'RemoteServerName\InstanceName', @.locallogin = NULL , @.useself = N'False', @.rmtuser = N'remoteUser', @.rmtpassword = N'remotePassword'
Did you check permissions of remoteUser on the remote server instance? You can do this by just using the remote login to connect to the server using ISQLW and see what tables you can access or check your permissions.|||Yes, remoteUser is able to access RemoteServerName through Query Analizer.|||I didn't mean just accessing the server. That works since you didn't get any errors. What tables can you access? What happens if you try to select from a known table?|||You have to use the Servername.Database.UserObject.Table on the query..
example,
Select * from [RemoteServer\MSSS2000].NorthWind.dbo.Orders
Linked server from SQL2005 to SQL2000
Linked server from SQL2005 to SQL2000
Hi
I use the following SQL statements to create a link server. RemoteServerName is an SQL2000 and I am executing this in another machine which is SQL2005.
Though the link server is created successfully, I am not able to se the tables under it so could not query anything.
--
USE [master]
EXEC master.dbo.sp_addlinkedserver @.server = N'RemoteServerName\InstanceName', @.srvproduct=N'SQL Server'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'collation compatible', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'data access', @.optvalue=N'true'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'rpc',@.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'rpc out', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'connect timeout', @.optvalue=N'0'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'collation name', @.optvalue=null
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'query timeout', @.optvalue=N'0'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'use remote collation', @.optvalue=N'true'
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'RemoteServerName\InstanceName', @.locallogin = NULL , @.useself = N'False', @.rmtuser = N'remoteUser', @.rmtpassword = N'remotePassword'
What do you mean by "I am not able to see the tables under it"... have you tried:
SELECT * FROM [targetServer].[master].dbo.SysObjects to see if you are actually connected (and can query) ?
|||No, this does not work, I am getting no object found|||Can you post your query to access the linked server, as well as the error message you got? Maybe you got right object name, but incorrect schema name?|||Hey u can't see the link server objects in 2005 as u used to see in sql2000.
But u can query an object as u wish.
if u know the name of any table.
Try to query the link server like
Select * from LinkServerName...tablename.
You can also use openquery statement.
Good Luck
|||Do you mean you can't find Linked Server objects in Management Studio? Actually?Linked Server objects are moved to the "Server Objects" catalog in Management Studio, no longer under the Security branch as in Enterprise Manager
You can also get information about linker servers usingsp_linkedservers system procedure.
Linked Server for Oracle XE Database
Hello,
I have been able to create a linked server using the MSDAORA provider to an
Oracle XE Server. I have done so on the same machine as the Oracle XE
Server (also has a SQL server on it), and on another machine (with SQL
server and Oracle XE Client installed). The problem happens when I have do
anything to the Oracle database within a transaction. You see in order to
get this to work you need to point the MSDTC (Microsoft Distributed
Transaction Coordinator) to the correct Oracle DLLs. You can do this by
modifying the registry entries at the following location:
HKLM\Software\Microsoft\MSDTC\MTxOCI\
I could do so on the server with Oracle XE Server installed. I entered the
following values:
OracleOciLib = oci.dll
OracleSqlLib = orasql10.dll
OracleXaLib = oraclient10.dll
And everything worked fine. But on the server with the Oracle XE Client
installed, these files are not present, so what am I supposed to do? I
tried copying the files from the Oracle XE Server PC but it didn't fix
things. Has anyone had any success in configuring MSDTC for Oracle XE
Client?
Any help would be greatly appreciated.
Thanks.
-adamOkay, I have gotten around it by installing the Oracle 8i Client on the
server instead of the Oracle XE Client. This is hardly ideal, but it will
have to do until I can find a solution.
-adam
"Adam Byrne" <misterbyrne.nospam@.allforme.gmail.com> wrote in message
news:e3u5JVwjGHA.3440@.TK2MSFTNGP02.phx.gbl...
> Hello,
> I have been able to create a linked server using the MSDAORA provider to
> an Oracle XE Server. I have done so on the same machine as the Oracle XE
> Server (also has a SQL server on it), and on another machine (with SQL
> server and Oracle XE Client installed). The problem happens when I have
> do anything to the Oracle database within a transaction. You see in order
> to get this to work you need to point the MSDTC (Microsoft Distributed
> Transaction Coordinator) to the correct Oracle DLLs. You can do this by
> modifying the registry entries at the following location:
> HKLM\Software\Microsoft\MSDTC\MTxOCI\
> I could do so on the server with Oracle XE Server installed. I entered
> the following values:
> OracleOciLib = oci.dll
> OracleSqlLib = orasql10.dll
> OracleXaLib = oraclient10.dll
> And everything worked fine. But on the server with the Oracle XE Client
> installed, these files are not present, so what am I supposed to do? I
> tried copying the files from the Oracle XE Server PC but it didn't fix
> things. Has anyone had any success in configuring MSDTC for Oracle XE
> Client?
> Any help would be greatly appreciated.
> Thanks.
> -adam
>
I have been able to create a linked server using the MSDAORA provider to an
Oracle XE Server. I have done so on the same machine as the Oracle XE
Server (also has a SQL server on it), and on another machine (with SQL
server and Oracle XE Client installed). The problem happens when I have do
anything to the Oracle database within a transaction. You see in order to
get this to work you need to point the MSDTC (Microsoft Distributed
Transaction Coordinator) to the correct Oracle DLLs. You can do this by
modifying the registry entries at the following location:
HKLM\Software\Microsoft\MSDTC\MTxOCI\
I could do so on the server with Oracle XE Server installed. I entered the
following values:
OracleOciLib = oci.dll
OracleSqlLib = orasql10.dll
OracleXaLib = oraclient10.dll
And everything worked fine. But on the server with the Oracle XE Client
installed, these files are not present, so what am I supposed to do? I
tried copying the files from the Oracle XE Server PC but it didn't fix
things. Has anyone had any success in configuring MSDTC for Oracle XE
Client?
Any help would be greatly appreciated.
Thanks.
-adamOkay, I have gotten around it by installing the Oracle 8i Client on the
server instead of the Oracle XE Client. This is hardly ideal, but it will
have to do until I can find a solution.
-adam
"Adam Byrne" <misterbyrne.nospam@.allforme.gmail.com> wrote in message
news:e3u5JVwjGHA.3440@.TK2MSFTNGP02.phx.gbl...
> Hello,
> I have been able to create a linked server using the MSDAORA provider to
> an Oracle XE Server. I have done so on the same machine as the Oracle XE
> Server (also has a SQL server on it), and on another machine (with SQL
> server and Oracle XE Client installed). The problem happens when I have
> do anything to the Oracle database within a transaction. You see in order
> to get this to work you need to point the MSDTC (Microsoft Distributed
> Transaction Coordinator) to the correct Oracle DLLs. You can do this by
> modifying the registry entries at the following location:
> HKLM\Software\Microsoft\MSDTC\MTxOCI\
> I could do so on the server with Oracle XE Server installed. I entered
> the following values:
> OracleOciLib = oci.dll
> OracleSqlLib = orasql10.dll
> OracleXaLib = oraclient10.dll
> And everything worked fine. But on the server with the Oracle XE Client
> installed, these files are not present, so what am I supposed to do? I
> tried copying the files from the Oracle XE Server PC but it didn't fix
> things. Has anyone had any success in configuring MSDTC for Oracle XE
> Client?
> Any help would be greatly appreciated.
> Thanks.
> -adam
>
linked server for clipper files
Hi i have d/l some ODBC drivers from datadirect.com , made a tested dsn
that points to my clipper files and i m trying to create an Link server
using that dsn but can't
.
Could someone please point out the correct way of doing it
thnks
*** Sent via Developersdex http://www.codecomments.com ***Hi
Have you tried:
http://msdn.microsoft.com/library/d...r />
_61yb.asp
John
"tolisss" <nospam@.devdex.com> wrote in message
news:OjKYvUeQFHA.3076@.TK2MSFTNGP14.phx.gbl...
> Hi i have d/l some ODBC drivers from datadirect.com , made a tested dsn
> that points to my clipper files and i m trying to create an Link server
> using that dsn but can't.
> Could someone please point out the correct way of doing it
> thnks
> *** Sent via Developersdex http://www.codecomments.com ***
that points to my clipper files and i m trying to create an Link server
using that dsn but can't
.Could someone please point out the correct way of doing it
thnks
*** Sent via Developersdex http://www.codecomments.com ***Hi
Have you tried:
http://msdn.microsoft.com/library/d...r />
_61yb.asp
John
"tolisss" <nospam@.devdex.com> wrote in message
news:OjKYvUeQFHA.3076@.TK2MSFTNGP14.phx.gbl...
> Hi i have d/l some ODBC drivers from datadirect.com , made a tested dsn
> that points to my clipper files and i m trying to create an Link server
> using that dsn but can't
.> Could someone please point out the correct way of doing it
> thnks
> *** Sent via Developersdex http://www.codecomments.com ***
Monday, March 19, 2012
Linked server error in SSMS
Darius
Hmm, I tried succefully create a linked server throu SSMS to SQL Server
2000. Pehaps something got corrupted during the installation of SSMS...
"Darius" <nospam@.mail.com> wrote in message
news:uU8j%23TlgHHA.4064@.TK2MSFTNGP02.phx.gbl...
> Hi all
> I have installed Sql Server 2005 + Sp2 developer edition. Once trying to
> add a linked server in SSMS Server Objects => Linked Servers => New Linked
> Server I'm getting the error immediately saying " Cannot show requested
> dialog. Cannot find table 0. (System.Data)". Additionally Providers tree
> under Linked servers is also empty. However I could add a linked server
> successfully using t-sql sp_addlinkedserver. I got the feeling that some
> files are corrupt on my PC, but I don't know which. I tried to uninstall
> and put the server back again and it did not help either. I installed Sql
> Server from the same disk onto the other machine and it works fine. Has
> anybody got an idea how to resolve my problem?
> Many thanks
> Darius
>
>
Hi
Have you tried to search on "Google" for this error message?
"Darius" <nospam@.mail.com> wrote in message
news:eK2yKolgHHA.3852@.TK2MSFTNGP04.phx.gbl...
> Hi Uri
> I believe something is corrupted on my PC (drivers, etc.), I successfully
> can create linked server on the other machines. The error appears straight
> after I click on "New Linked Server", it does not even show any dialog
> box.. I reinstalled the Sql Server from the scratch but there were no
> difference. Just don't know where to start looking at...
> Darius
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uHjRvelgHHA.4260@.TK2MSFTNGP03.phx.gbl...
>
Hmm, I tried succefully create a linked server throu SSMS to SQL Server
2000. Pehaps something got corrupted during the installation of SSMS...
"Darius" <nospam@.mail.com> wrote in message
news:uU8j%23TlgHHA.4064@.TK2MSFTNGP02.phx.gbl...
> Hi all
> I have installed Sql Server 2005 + Sp2 developer edition. Once trying to
> add a linked server in SSMS Server Objects => Linked Servers => New Linked
> Server I'm getting the error immediately saying " Cannot show requested
> dialog. Cannot find table 0. (System.Data)". Additionally Providers tree
> under Linked servers is also empty. However I could add a linked server
> successfully using t-sql sp_addlinkedserver. I got the feeling that some
> files are corrupt on my PC, but I don't know which. I tried to uninstall
> and put the server back again and it did not help either. I installed Sql
> Server from the same disk onto the other machine and it works fine. Has
> anybody got an idea how to resolve my problem?
> Many thanks
> Darius
>
>
Hi
Have you tried to search on "Google" for this error message?
"Darius" <nospam@.mail.com> wrote in message
news:eK2yKolgHHA.3852@.TK2MSFTNGP04.phx.gbl...
> Hi Uri
> I believe something is corrupted on my PC (drivers, etc.), I successfully
> can create linked server on the other machines. The error appears straight
> after I click on "New Linked Server", it does not even show any dialog
> box.. I reinstalled the Sql Server from the scratch but there were no
> difference. Just don't know where to start looking at...
> Darius
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uHjRvelgHHA.4260@.TK2MSFTNGP03.phx.gbl...
>
Monday, March 12, 2012
Linked Server error
I have created a linked server (LAB) to an Access 2K DB on my local drive.
From within a DB on the SQL server I started to create a view, and using the
syntax:
SELECT * FROM LAB...tblName tblName_1
It displays all the required data.
When I attempt to save the view though, then I get problems. I receive an
error message:
ODBC error:[Microsoft][ODBC SQL Server Driver][SQL Server]Could not start a
transaction OLE DB provider 'Microsoft.Jet.OLEDB.4.0'.
[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB returned message:
Neither the isolation level nor a strengthening of it is supported.]
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace[OLE/DB
Provider 'Microsoft.Jet.4.0' ITransactionLoca StartTransaction returned
0x8004d008: ISOLEVEL=1048576].
I then placed the SELECT statement in a SProc, and received the following
error:
Error 7392: Could Not start a transaction for OLE DB provider
'Microsoft.Jet.OLEDB.4.0'.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.4.0'
ITransactionLocal::StartTransaction returned 0x8004d008: ISOLEVEL=1048576].
Setting the Isolation level as the first statement in the SProc made no
difference.
Anyone have any idea where I have gone wrong? Either I didn't set the Linked
Server up correctly, or I have missed something very fundamental.
Thanks in advanced for any help with this one,
TonyWhy don't you create Access from inside Access and create SQL Proc from SQL
side?
GX
"Logicalman" <tony9scott45us@.com7cast.net34> wrote in message
news:16C30F75-3662-49DD-BA57-28A07A39DC19@.microsoft.com...
>I have created a linked server (LAB) to an Access 2K DB on my local drive.
> From within a DB on the SQL server I started to create a view, and using
> the
> syntax:
> SELECT * FROM LAB...tblName tblName_1
> It displays all the required data.
> When I attempt to save the view though, then I get problems. I receive an
> error message:
> ODBC error:[Microsoft][ODBC SQL Server Driver][SQL Server]Could not start
> a
> transaction OLE DB provider 'Microsoft.Jet.OLEDB.4.0'.
> [Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB returned message:
> Neither the isolation level nor a strengthening of it is supported.]
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace[OLE/DB
> Provider 'Microsoft.Jet.4.0' ITransactionLoca StartTransaction returned
> 0x8004d008: ISOLEVEL=1048576].
> I then placed the SELECT statement in a SProc, and received the following
> error:
> Error 7392: Could Not start a transaction for OLE DB provider
> 'Microsoft.Jet.OLEDB.4.0'.
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.4.0'
> ITransactionLocal::StartTransaction returned 0x8004d008:
> ISOLEVEL=1048576].
> Setting the Isolation level as the first statement in the SProc made no
> difference.
> Anyone have any idea where I have gone wrong? Either I didn't set the
> Linked
> Server up correctly, or I have missed something very fundamental.
> Thanks in advanced for any help with this one,
> Tony|||The access DB is a replicant, and, unfortunately, has some data errors in it
.
I originally started importing the data into SQL to complete some web work,
but due to the errors, felt thaat 'linked' tables would suffice for my needs
,
thus, a linked server method raised up and so I tried this. Once the errors
started it then intrigued me to find out why, and thus, I wrote the question
.
".Net Guy" wrote:
> Why don't you create Access from inside Access and create SQL Proc from SQ
L
> side?
> GX
>
> "Logicalman" <tony9scott45us@.com7cast.net34> wrote in message
> news:16C30F75-3662-49DD-BA57-28A07A39DC19@.microsoft.com...
>
>
From within a DB on the SQL server I started to create a view, and using the
syntax:
SELECT * FROM LAB...tblName tblName_1
It displays all the required data.
When I attempt to save the view though, then I get problems. I receive an
error message:
ODBC error:[Microsoft][ODBC SQL Server Driver][SQL Server]Could not start a
transaction OLE DB provider 'Microsoft.Jet.OLEDB.4.0'.
[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB returned message:
Neither the isolation level nor a strengthening of it is supported.]
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace[OLE/DB
Provider 'Microsoft.Jet.4.0' ITransactionLoca StartTransaction returned
0x8004d008: ISOLEVEL=1048576].
I then placed the SELECT statement in a SProc, and received the following
error:
Error 7392: Could Not start a transaction for OLE DB provider
'Microsoft.Jet.OLEDB.4.0'.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.4.0'
ITransactionLocal::StartTransaction returned 0x8004d008: ISOLEVEL=1048576].
Setting the Isolation level as the first statement in the SProc made no
difference.
Anyone have any idea where I have gone wrong? Either I didn't set the Linked
Server up correctly, or I have missed something very fundamental.
Thanks in advanced for any help with this one,
TonyWhy don't you create Access from inside Access and create SQL Proc from SQL
side?
GX
"Logicalman" <tony9scott45us@.com7cast.net34> wrote in message
news:16C30F75-3662-49DD-BA57-28A07A39DC19@.microsoft.com...
>I have created a linked server (LAB) to an Access 2K DB on my local drive.
> From within a DB on the SQL server I started to create a view, and using
> the
> syntax:
> SELECT * FROM LAB...tblName tblName_1
> It displays all the required data.
> When I attempt to save the view though, then I get problems. I receive an
> error message:
> ODBC error:[Microsoft][ODBC SQL Server Driver][SQL Server]Could not start
> a
> transaction OLE DB provider 'Microsoft.Jet.OLEDB.4.0'.
> [Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB returned message:
> Neither the isolation level nor a strengthening of it is supported.]
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace[OLE/DB
> Provider 'Microsoft.Jet.4.0' ITransactionLoca StartTransaction returned
> 0x8004d008: ISOLEVEL=1048576].
> I then placed the SELECT statement in a SProc, and received the following
> error:
> Error 7392: Could Not start a transaction for OLE DB provider
> 'Microsoft.Jet.OLEDB.4.0'.
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.4.0'
> ITransactionLocal::StartTransaction returned 0x8004d008:
> ISOLEVEL=1048576].
> Setting the Isolation level as the first statement in the SProc made no
> difference.
> Anyone have any idea where I have gone wrong? Either I didn't set the
> Linked
> Server up correctly, or I have missed something very fundamental.
> Thanks in advanced for any help with this one,
> Tony|||The access DB is a replicant, and, unfortunately, has some data errors in it
.
I originally started importing the data into SQL to complete some web work,
but due to the errors, felt thaat 'linked' tables would suffice for my needs
,
thus, a linked server method raised up and so I tried this. Once the errors
started it then intrigued me to find out why, and thus, I wrote the question
.
".Net Guy" wrote:
> Why don't you create Access from inside Access and create SQL Proc from SQ
L
> side?
> GX
>
> "Logicalman" <tony9scott45us@.com7cast.net34> wrote in message
> news:16C30F75-3662-49DD-BA57-28A07A39DC19@.microsoft.com...
>
>
Linked server connection to MS Access db across the network failed
I have attempted to create a linked server on Server1 to an Access db on
Server2.
These servers are located on the same network.
The account running the SQL Server services on Sever 1 is a domain admin
account
I can open the file via explorer across the network using the same services
account.
The provider name I used is Microsoft.Jet.OLEDB.4.0.
The data source I used is \\(ip address)\c$\…\accessdb.mdb
I have tried with and without a security context of admin with no password.
I received the following error…
Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 13
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)\c$\…\ accessdb.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: ].
I realize this an issue of permissions. I am just not sure where the failure
is taking place, what the cause is, or even how to analyze it.
Also I am able to create the linked server if the access database is located
on Server1 with SQL Server. However this is not the scenario I am trying to
solution.
Please advise.
Thanks.
As I've referred in other posts ensure you've using correct version of Jet,
refer to KBA http://support.microsoft.com/kb/818182 and
http://support.microsoft.com/default.aspx/kb/241267 link.
--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on
Performance topic.
"IT4CABLETV" wrote:
> I have attempted to create a linked server on Server1 to an Access db on
> Server2.
> These servers are located on the same network.
> The account running the SQL Server services on Sever 1 is a domain admin
> account
> I can open the file via explorer across the network using the same services
> account.
> The provider name I used is Microsoft.Jet.OLEDB.4.0.
> The data source I used is \\(ip address)\c$\…\accessdb.mdb
> I have tried with and without a security context of admin with no password.
> I received the following error…
> Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 13
> 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)\c$\…\ accessdb.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: ].
> I realize this an issue of permissions. I am just not sure where the failure
> is taking place, what the cause is, or even how to analyze it.
> Also I am able to create the linked server if the access database is located
> on Server1 with SQL Server. However this is not the scenario I am trying to
> solution.
> Please advise.
> Thanks.
Server2.
These servers are located on the same network.
The account running the SQL Server services on Sever 1 is a domain admin
account
I can open the file via explorer across the network using the same services
account.
The provider name I used is Microsoft.Jet.OLEDB.4.0.
The data source I used is \\(ip address)\c$\…\accessdb.mdb
I have tried with and without a security context of admin with no password.
I received the following error…
Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 13
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)\c$\…\ accessdb.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: ].
I realize this an issue of permissions. I am just not sure where the failure
is taking place, what the cause is, or even how to analyze it.
Also I am able to create the linked server if the access database is located
on Server1 with SQL Server. However this is not the scenario I am trying to
solution.
Please advise.
Thanks.
As I've referred in other posts ensure you've using correct version of Jet,
refer to KBA http://support.microsoft.com/kb/818182 and
http://support.microsoft.com/default.aspx/kb/241267 link.
--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on
Performance topic.
"IT4CABLETV" wrote:
> I have attempted to create a linked server on Server1 to an Access db on
> Server2.
> These servers are located on the same network.
> The account running the SQL Server services on Sever 1 is a domain admin
> account
> I can open the file via explorer across the network using the same services
> account.
> The provider name I used is Microsoft.Jet.OLEDB.4.0.
> The data source I used is \\(ip address)\c$\…\accessdb.mdb
> I have tried with and without a security context of admin with no password.
> I received the following error…
> Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 13
> 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)\c$\…\ accessdb.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: ].
> I realize this an issue of permissions. I am just not sure where the failure
> is taking place, what the cause is, or even how to analyze it.
> Also I am able to create the linked server if the access database is located
> on Server1 with SQL Server. However this is not the scenario I am trying to
> solution.
> Please advise.
> Thanks.
Linked server connection to MS Access db across the network failed
I have attempted to create a linked server on Server1 to an Access db on
Server2.
These servers are located on the same network.
The account running the SQL Server services on Sever 1 is a domain admin
account
I can open the file via explorer across the network using the same services
account.
The provider name I used is Microsoft.Jet.OLEDB.4.0.
The data source I used is \\(ip address)\c$\…\accessdb.mdb
I have tried with and without a security context of admin with no password.
I received the following error…
Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 13
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine can
not
open the file '\\(server)\c$\…\ accessdb.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: ].
I realize this an issue of permissions. I am just not sure where the failure
is taking place, what the cause is, or even how to analyze it.
Also I am able to create the linked server if the access database is located
on Server1 with SQL Server. However this is not the scenario I am trying to
solution.
Please advise.
Thanks.As I've referred in other posts ensure you've using correct version of Jet,
refer to KBA http://support.microsoft.com/kb/818182 and
http://support.microsoft.com/default.aspx/kb/241267 link.
--
--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on
Performance topic.
"IT4CABLETV" wrote:
> I have attempted to create a linked server on Server1 to an Access db on
> Server2.
> These servers are located on the same network.
> The account running the SQL Server services on Sever 1 is a domain admin
> account
> I can open the file via explorer across the network using the same service
s
> account.
> The provider name I used is Microsoft.Jet.OLEDB.4.0.
> The data source I used is \\(ip address)\c$\…\accessdb.mdb
> I have tried with and without a security context of admin with no password
.
> I received the following error…
> Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 13
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
> [OLE/DB provider returned message: The Microsoft Jet database engine c
annot
> open the file '\\(server)\c$\…\ accessdb.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: ].
> I realize this an issue of permissions. I am just not sure where the failu
re
> is taking place, what the cause is, or even how to analyze it.
> Also I am able to create the linked server if the access database is locat
ed
> on Server1 with SQL Server. However this is not the scenario I am trying t
o
> solution.
> Please advise.
> Thanks.
Server2.
These servers are located on the same network.
The account running the SQL Server services on Sever 1 is a domain admin
account
I can open the file via explorer across the network using the same services
account.
The provider name I used is Microsoft.Jet.OLEDB.4.0.
The data source I used is \\(ip address)\c$\…\accessdb.mdb
I have tried with and without a security context of admin with no password.
I received the following error…
Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 13
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine can
not
open the file '\\(server)\c$\…\ accessdb.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: ].
I realize this an issue of permissions. I am just not sure where the failure
is taking place, what the cause is, or even how to analyze it.
Also I am able to create the linked server if the access database is located
on Server1 with SQL Server. However this is not the scenario I am trying to
solution.
Please advise.
Thanks.As I've referred in other posts ensure you've using correct version of Jet,
refer to KBA http://support.microsoft.com/kb/818182 and
http://support.microsoft.com/default.aspx/kb/241267 link.
--
--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on
Performance topic.
"IT4CABLETV" wrote:
> I have attempted to create a linked server on Server1 to an Access db on
> Server2.
> These servers are located on the same network.
> The account running the SQL Server services on Sever 1 is a domain admin
> account
> I can open the file via explorer across the network using the same service
s
> account.
> The provider name I used is Microsoft.Jet.OLEDB.4.0.
> The data source I used is \\(ip address)\c$\…\accessdb.mdb
> I have tried with and without a security context of admin with no password
.
> I received the following error…
> Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 13
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
> [OLE/DB provider returned message: The Microsoft Jet database engine c
annot
> open the file '\\(server)\c$\…\ accessdb.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: ].
> I realize this an issue of permissions. I am just not sure where the failu
re
> is taking place, what the cause is, or even how to analyze it.
> Also I am able to create the linked server if the access database is locat
ed
> on Server1 with SQL Server. However this is not the scenario I am trying t
o
> solution.
> Please advise.
> Thanks.
Linked server config prob
SQL 7.0
Trying to create a linked server, but I always get the
error message below. I've created a successful ODBC
connection to the remote server and I use that name in
the config process.
"Error 6: Specified SQL Server not found."
Any ideas?
Thanks,
Don"Don" <ddachner@.hotmail.com> wrote in message
news:a85701c3eb7e$01fc1450$a601280a@.phx.gbl...
Are you creating a linked server from SQL Server v7 to SQL Server v7? If so,
what security mode is in place and does the account have proper access?
Another possibility is name resolution -- try using the IP address instead
of the NetBIOS name.
Steve|||Yes, trying to link two v7 servers.
The security mode is SQL/Windows on both.
I'm using the sa account which is the same on both.
I've tried using the IP address, which didn't give an
error message, but when I tried a query using the 4 part
name, I get a syntax error on the dots of the servername,
i.e, 123.111.333.555.DBNAME.dbo.TABLENAME there's a
syntax error "near" where the "." is of the 123.111 part
of the name so i gave up on that approach.
Don
SQL Server v7? If so,
proper access?
IP address instead
Trying to create a linked server, but I always get the
error message below. I've created a successful ODBC
connection to the remote server and I use that name in
the config process.
"Error 6: Specified SQL Server not found."
Any ideas?
Thanks,
Don"Don" <ddachner@.hotmail.com> wrote in message
news:a85701c3eb7e$01fc1450$a601280a@.phx.gbl...
quote:
> SQL 7.0
> Trying to create a linked server, but I always get the
> error message below. I've created a successful ODBC
> connection to the remote server and I use that name in
> the config process.
> "Error 6: Specified SQL Server not found."
Are you creating a linked server from SQL Server v7 to SQL Server v7? If so,
what security mode is in place and does the account have proper access?
Another possibility is name resolution -- try using the IP address instead
of the NetBIOS name.
Steve|||Yes, trying to link two v7 servers.
The security mode is SQL/Windows on both.
I'm using the sa account which is the same on both.
I've tried using the IP address, which didn't give an
error message, but when I tried a query using the 4 part
name, I get a syntax error on the dots of the servername,
i.e, 123.111.333.555.DBNAME.dbo.TABLENAME there's a
syntax error "near" where the "." is of the 123.111 part
of the name so i gave up on that approach.
Don
quote:
>--Original Message--
>"Don" <ddachner@.hotmail.com> wrote in message
>news:a85701c3eb7e$01fc1450$a601280a@.phx.gbl...
>Are you creating a linked server from SQL Server v7 to
SQL Server v7? If so,
quote:
>what security mode is in place and does the account have
proper access?
quote:
>Another possibility is name resolution -- try using the
IP address instead
quote:
>of the NetBIOS name.
>Steve
>
>.
>
Friday, March 9, 2012
Linked Server Between two machines in different domains with different SQL versions
hi everybody
well im trying to create a linked server were the source is a database
on SQL server 2000 and the destination is an SQL server 2005 and the
two servers on different domains
now when i run the openquery statment i get an error says
OLE DB provider "SQLNCLI" for linked server "HR" returned message
"Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "HR" returned message "An
error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact
that under the default settings SQL Server does not allow remote
connections.".
Msg 10061, Level 16, State 1, Line 0
TCP Provider: No connection could be made because the target machine
actively refused it.
and im not sure if i created the linked server correctly, so can anyone
help me in that, and if it was correct and the error was generated
because of another reason can anyone to tell me whats wrong because the
message not that clear for me
One of the messages states that you may have an issue with the 2005 server
not being configured to allow remote access. Have a look at these:
https://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx
Configuring Remote Access
http://www.kbalertz.com/Feedback_914277.aspx Configuring SQL2005 for
remote access
Andrew J. Kelly SQL MVP
"alcatraz" <ala.zareini@.gmail.com> wrote in message
news:1168179390.779562.66530@.i15g2000cwa.googlegro ups.com...
> hi everybody
> well im trying to create a linked server were the source is a database
> on SQL server 2000 and the destination is an SQL server 2005 and the
> two servers on different domains
> now when i run the openquery statment i get an error says
> OLE DB provider "SQLNCLI" for linked server "HR" returned message
> "Login timeout expired".
> OLE DB provider "SQLNCLI" for linked server "HR" returned message "An
> error has occurred while establishing a connection to the server. When
> connecting to SQL Server 2005, this failure may be caused by the fact
> that under the default settings SQL Server does not allow remote
> connections.".
> Msg 10061, Level 16, State 1, Line 0
> TCP Provider: No connection could be made because the target machine
> actively refused it.
>
> and im not sure if i created the linked server correctly, so can anyone
> help me in that, and if it was correct and the error was generated
> because of another reason can anyone to tell me whats wrong because the
> message not that clear for me
>
well im trying to create a linked server were the source is a database
on SQL server 2000 and the destination is an SQL server 2005 and the
two servers on different domains
now when i run the openquery statment i get an error says
OLE DB provider "SQLNCLI" for linked server "HR" returned message
"Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "HR" returned message "An
error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact
that under the default settings SQL Server does not allow remote
connections.".
Msg 10061, Level 16, State 1, Line 0
TCP Provider: No connection could be made because the target machine
actively refused it.
and im not sure if i created the linked server correctly, so can anyone
help me in that, and if it was correct and the error was generated
because of another reason can anyone to tell me whats wrong because the
message not that clear for me
One of the messages states that you may have an issue with the 2005 server
not being configured to allow remote access. Have a look at these:
https://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx
Configuring Remote Access
http://www.kbalertz.com/Feedback_914277.aspx Configuring SQL2005 for
remote access
Andrew J. Kelly SQL MVP
"alcatraz" <ala.zareini@.gmail.com> wrote in message
news:1168179390.779562.66530@.i15g2000cwa.googlegro ups.com...
> hi everybody
> well im trying to create a linked server were the source is a database
> on SQL server 2000 and the destination is an SQL server 2005 and the
> two servers on different domains
> now when i run the openquery statment i get an error says
> OLE DB provider "SQLNCLI" for linked server "HR" returned message
> "Login timeout expired".
> OLE DB provider "SQLNCLI" for linked server "HR" returned message "An
> error has occurred while establishing a connection to the server. When
> connecting to SQL Server 2005, this failure may be caused by the fact
> that under the default settings SQL Server does not allow remote
> connections.".
> Msg 10061, Level 16, State 1, Line 0
> TCP Provider: No connection could be made because the target machine
> actively refused it.
>
> and im not sure if i created the linked server correctly, so can anyone
> help me in that, and if it was correct and the error was generated
> because of another reason can anyone to tell me whats wrong because the
> message not that clear for me
>
Linked server AS400/DB2 trouble again
Hi !
I'm trying to create Linked Server to AS400 DB2 on SQL Server 2000.
Using IBMDA400 (IBM AS400 OLEDB Provider installed from Client Access).
Got an error 7399: OLEDB Provider returned UNKNOWN error.
exec SP_AddLinkedServer
@.Server=AS_SERVER,
@.SrvProduct='IBM AS400 OLEDB Provider',
@.Catalog='QGPL',
@.Location='AS_SERVER',
@.Provider=IBMDA400,
@.Datasrc=AS_SERVER,
@.Provstr='Provider=IBMDA400.1;Password="";User ID=AS_USER;Data Source=AS_SERVER;Transport Product=Client Access;SSL=DEFAULT;Libraries=QGPL;Connect Timeout=90;Host Code Page=037'
Attempted different combinations of theese params...
After that created remote login on AS400.
Please help or correct my LinkedServerProperties...
Regards,
PVHave you looked at this old posting:
dbForums - BD2OLEDB (http://dbforums.com/t477702.html)
I'm trying to create Linked Server to AS400 DB2 on SQL Server 2000.
Using IBMDA400 (IBM AS400 OLEDB Provider installed from Client Access).
Got an error 7399: OLEDB Provider returned UNKNOWN error.
exec SP_AddLinkedServer
@.Server=AS_SERVER,
@.SrvProduct='IBM AS400 OLEDB Provider',
@.Catalog='QGPL',
@.Location='AS_SERVER',
@.Provider=IBMDA400,
@.Datasrc=AS_SERVER,
@.Provstr='Provider=IBMDA400.1;Password="";User ID=AS_USER;Data Source=AS_SERVER;Transport Product=Client Access;SSL=DEFAULT;Libraries=QGPL;Connect Timeout=90;Host Code Page=037'
Attempted different combinations of theese params...
After that created remote login on AS400.
Please help or correct my LinkedServerProperties...
Regards,
PVHave you looked at this old posting:
dbForums - BD2OLEDB (http://dbforums.com/t477702.html)
Linked Server and ADSI
Does anyone one know how to create a linked server to active directory
on a sql server that is not a domain controller?
I have tried the following to add the linked server
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
'ADSDSOObject',
'adsdatasource'
but I can not issue any querys without getting the following 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].
This is running on a windows 2000 sql 2000 box.
Thanks in advance.
That's fine but you'll need some remote login credentials with which to
query AD (otherwise what security context is it going to use?). I've set up
links to AD from my SQL servers by adding the linked server (as you've done)
and then adding a remote login mapping so that all local logins use a
specific set of credentials to connect via ADSI (I'm not overly concerned
about oppressive security in this case because any user that connects to my
SQL box can also query AD anyway - this just makes multi-source queries a
little more transparent/seamless). Specifically I've set up the mapping so
that all local logins use the service account under which SQL is running
(ie. Windows account). That is,
exec sp_addlinkedsrvlogin 'ADSI', false, NULL,
'MyDomain\MySQLServiceAccount', 'MyServiceAccountPassword'
So any SQL client session that issues an ADSI query through that linked
server will query AD using the service account that SQL server is running
under (this assumes you're running your SQL server under a domain account,
not as System or a local SAM account). Works pretty well for me - the only
thing you have to bear in mind is you can only query the bits of AD that the
SQL service account has been granted permissions to query.
Cheers,
Mike
"sfibich" <sfibich@.pfgc.com> wrote in message
news:u2w7zDOzEHA.1264@.TK2MSFTNGP12.phx.gbl...
> Does anyone one know how to create a linked server to active directory on
> a sql server that is not a domain controller?
> I have tried the following to add the linked server
> EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
> 'ADSDSOObject',
> 'adsdatasource'
>
> but I can not issue any querys without getting the following 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].
>
> This is running on a windows 2000 sql 2000 box.
> Thanks in advance.
|||Mike Hodgson wrote:
> That's fine but you'll need some remote login credentials with which to
> query AD (otherwise what security context is it going to use?). I've set up
> links to AD from my SQL servers by adding the linked server (as you've done)
> and then adding a remote login mapping so that all local logins use a
> specific set of credentials to connect via ADSI (I'm not overly concerned
> about oppressive security in this case because any user that connects to my
> SQL box can also query AD anyway - this just makes multi-source queries a
> little more transparent/seamless). Specifically I've set up the mapping so
> that all local logins use the service account under which SQL is running
> (ie. Windows account). That is,
> exec sp_addlinkedsrvlogin 'ADSI', false, NULL,
> 'MyDomain\MySQLServiceAccount', 'MyServiceAccountPassword'
> So any SQL client session that issues an ADSI query through that linked
> server will query AD using the service account that SQL server is running
> under (this assumes you're running your SQL server under a domain account,
> not as System or a local SAM account). Works pretty well for me - the only
> thing you have to bear in mind is you can only query the bits of AD that the
> SQL service account has been granted permissions to query.
>
Thanks that does it.
|||I'm hoping you are still checking this newsgroup.
I'm experiencing the same problem and I am using a domain account to start
the services as well as in the security context of the linked server but I
still am having problems executing a query.
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'ADSDSOObject'.
It is a Windows 2000 Server OS, SQL Server 2000. The AD is 2003 Server. Any
ideas?
"sfibich" wrote:
> Mike Hodgson wrote:
> Thanks that does it.
>
on a sql server that is not a domain controller?
I have tried the following to add the linked server
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
'ADSDSOObject',
'adsdatasource'
but I can not issue any querys without getting the following 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].
This is running on a windows 2000 sql 2000 box.
Thanks in advance.
That's fine but you'll need some remote login credentials with which to
query AD (otherwise what security context is it going to use?). I've set up
links to AD from my SQL servers by adding the linked server (as you've done)
and then adding a remote login mapping so that all local logins use a
specific set of credentials to connect via ADSI (I'm not overly concerned
about oppressive security in this case because any user that connects to my
SQL box can also query AD anyway - this just makes multi-source queries a
little more transparent/seamless). Specifically I've set up the mapping so
that all local logins use the service account under which SQL is running
(ie. Windows account). That is,
exec sp_addlinkedsrvlogin 'ADSI', false, NULL,
'MyDomain\MySQLServiceAccount', 'MyServiceAccountPassword'
So any SQL client session that issues an ADSI query through that linked
server will query AD using the service account that SQL server is running
under (this assumes you're running your SQL server under a domain account,
not as System or a local SAM account). Works pretty well for me - the only
thing you have to bear in mind is you can only query the bits of AD that the
SQL service account has been granted permissions to query.
Cheers,
Mike
"sfibich" <sfibich@.pfgc.com> wrote in message
news:u2w7zDOzEHA.1264@.TK2MSFTNGP12.phx.gbl...
> Does anyone one know how to create a linked server to active directory on
> a sql server that is not a domain controller?
> I have tried the following to add the linked server
> EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
> 'ADSDSOObject',
> 'adsdatasource'
>
> but I can not issue any querys without getting the following 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].
>
> This is running on a windows 2000 sql 2000 box.
> Thanks in advance.
|||Mike Hodgson wrote:
> That's fine but you'll need some remote login credentials with which to
> query AD (otherwise what security context is it going to use?). I've set up
> links to AD from my SQL servers by adding the linked server (as you've done)
> and then adding a remote login mapping so that all local logins use a
> specific set of credentials to connect via ADSI (I'm not overly concerned
> about oppressive security in this case because any user that connects to my
> SQL box can also query AD anyway - this just makes multi-source queries a
> little more transparent/seamless). Specifically I've set up the mapping so
> that all local logins use the service account under which SQL is running
> (ie. Windows account). That is,
> exec sp_addlinkedsrvlogin 'ADSI', false, NULL,
> 'MyDomain\MySQLServiceAccount', 'MyServiceAccountPassword'
> So any SQL client session that issues an ADSI query through that linked
> server will query AD using the service account that SQL server is running
> under (this assumes you're running your SQL server under a domain account,
> not as System or a local SAM account). Works pretty well for me - the only
> thing you have to bear in mind is you can only query the bits of AD that the
> SQL service account has been granted permissions to query.
>
Thanks that does it.
|||I'm hoping you are still checking this newsgroup.
I'm experiencing the same problem and I am using a domain account to start
the services as well as in the security context of the linked server but I
still am having problems executing a query.
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'ADSDSOObject'.
It is a Windows 2000 Server OS, SQL Server 2000. The AD is 2003 Server. Any
ideas?
"sfibich" wrote:
> Mike Hodgson wrote:
> Thanks that does it.
>
Subscribe to:
Posts (Atom)