Link to Server via Windows Authentication

Well, then everyone who used the linked server will be
using the new account created. I want people to only use
what thier domain account has permissions to do.
Any way to do this?
>--Original Message--
>You'll need to create a domain account that has login
>permissions to SQL Server "A" and the appropriate
database
>permissions as well. Use this account for the user login
>credentials when you create the link.
>Steve
>.
>You can use delegation to accomplish this. You can find
information on this in books online under the topic:
Security Account Delegation
-Sue
On Mon, 1 Mar 2004 06:57:34 -0800, "Linda"
<anonymous@.discussions.microsoft.com> wrote:
>Well, then everyone who used the linked server will be
>using the new account created. I want people to only use
>what thier domain account has permissions to do.
>Any way to do this?
>database

Link to Exchange

I have a SQL 2000 server I want to link to an Exchange Server. Can I used
linked servers to access the Exchange database? I do not want to have to
import my email addresses into a SQL table for updates.
Thank you,
JLFlemingYou can use SQL queries via ADSI to query the AD to get the Exchange
information. This will take additional programming and there is nothing out
of the box that I know of that will facilitate this. If you don't need RT
access to it, you could build that into a nightly job.
--
Ryan Hanisco
MCSE, MCDBA
Flagship Integration Services
"JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
news:43FDE5A4-EF8C-4923-B6D6-BC07D25774F7@.microsoft.com...
> I have a SQL 2000 server I want to link to an Exchange Server. Can I used
> linked servers to access the Exchange database? I do not want to have to
> import my email addresses into a SQL table for updates.
> Thank you,
> JLFleming|||I'm trying to link to Exchange as well. But I'm having difficult query the AD
after I created the link server. When I run the following query in Query
Analyzer from the SQL Server, I received the following error:
Either queries:
select * from openquery (ADSI, 'Select name, adsPath FROM
''LDAP://DC=HQMAIL1, DC=com''
WHERE objectCategory =''Person'' AND objectClass= ''user''')
SELECT * FROM Openquery(ADSI, 'SELECT givenName FROM
''LDAP://192.168.90.13/OU=AE,DC=HomeQuest,DC=Internal,DC=com'' WHERE
objectCategory
= ''person'' AND objectClass = ''user''')
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'ADSDSOObject'.
When I try to access the Linked Server from EM, I received this error:
Error 7301: Could not obtain a required interface from OLE DB provider
'ADSDSOObject'
Not sure what to do, any ideas?
"Ryan Hanisco" wrote:
> You can use SQL queries via ADSI to query the AD to get the Exchange
> information. This will take additional programming and there is nothing out
> of the box that I know of that will facilitate this. If you don't need RT
> access to it, you could build that into a nightly job.
> --
> Ryan Hanisco
> MCSE, MCDBA
> Flagship Integration Services
> "JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
> news:43FDE5A4-EF8C-4923-B6D6-BC07D25774F7@.microsoft.com...
> > I have a SQL 2000 server I want to link to an Exchange Server. Can I used
> > linked servers to access the Exchange database? I do not want to have to
> > import my email addresses into a SQL table for updates.
> >
> > Thank you,
> > JLFleming
>
>

Link Table

Ronia,
this could be achieved using replication (and triggers,
linked servers, DTS...). It largely depends on latency -
the delay between the change made on A and it appearing
on B, and whether the data in B is updatable. If you can
give me a bit more info on these matters, then we'll be
in a better position to advise.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Hi Paul,
Data won't be update by table B. Update will only done via Table A.
so it is only a read only .
There's a replication mode which fit with this scen.
But i am not familiar with replication. and i will ask me not to use system
account with it, I don't know how to do it.
Thanks.
Ronia.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> ?
news:8aae01c4d17a$1828d140$a601280a@.phx.gbl ?...
> Ronia,
> this could be achieved using replication (and triggers,
> linked servers, DTS...). It largely depends on latency -
> the delay between the change made on A and it appearing
> on B, and whether the data in B is updatable. If you can
> give me a bit more info on these matters, then we'll be
> in a better position to advise.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Transactional replication would be best for your
scenario. Replication uses agents which are essentially
jobs run by the sql server agent. For the security
requirements have a look in books on line
for 'replication, security', 'Role Requirements'.
To run replication you won't need the sysadmin account
but to set it up, configure it and monitor it, you will.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Ronia,
Why dont you create a view in your database "B"?
Use B
Go
Create view T
As
Select * From A.dbo.T
Go
This will let you read the data from the database A, table T.
Sriram
"Ronia" <Ronia@.mail.com> wrote in message news:<OaGb5tj0EHA.480@.TK2MSFTNGP10.phx.gbl>...
> Hi Paul,
> Data won't be update by table B. Update will only done via Table A.
> so it is only a read only .
> There's a replication mode which fit with this scen.
> But i am not familiar with replication. and i will ask me not to use system
> account with it, I don't know how to do it.
> Thanks.
> Ronia.
|||hi Sriram,
Will there be any preformance issue.. when doing this? However is they
are located within the same server is that performance will not be an
issue?...
compare with Transactional replication which will be better?
Thanks
Ronia
"Sriram" <srivish@.gmail.com> ?
news:25274ca3.0411241354.5e914fc2@.posting.google.c om ?...
> Hi Ronia,
> Why dont you create a view in your database "B"?
> Use B
> Go
> Create view T
> As
> Select * From A.dbo.T
> Go
> This will let you read the data from the database A, table T.
> Sriram
>
> "Ronia" <Ronia@.mail.com> wrote in message
news:<OaGb5tj0EHA.480@.TK2MSFTNGP10.phx.gbl>...[vbcol=seagreen]
system[vbcol=seagreen]
|||It depends...
If you require a copy of the data, so eg you can do
reports on it, while not affecting - blocking - users who
are making updates on the main table, then replication
would be suitable. It can also be used to effectively
load balance database access if the databases exist on
different drives. Also, so people use snapshot
replication to have a copy of a table that users can play
around with and modify the data indiscriminantly, knowing
that all the changes will later be overwritten.
So, what is your requirement for a copy of the data? If
it doesn't fit into one of these categories, then a cross-
database view may indeed be the solution.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

link sql servers using enterprise manage

Hi
Can anyone explain me the options for linked servers in the enterprise
manager.
I've tried to link another sql server but i'm gettng errors.> Can anyone explain me the options for linked servers in the enterprise
> manager.
Check the "Configuring Linked Servers" topic in Books OnLine
(mk:@.MSITStore:C:\Program%20Files\Micros
oft%20SQL%20Server\80\Tools\Books\ad
minsql.chm::/ad_1_server_4uuq.htm).

> I've tried to link another sql server but i'm gettng errors.
Any specific errors?
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||do u know the ip address of your second server if yes then enter the name or
the ipaddress then give the username as u use for your local server and admi
n
as username for remote server and give the password as your local server
password
and in the server option use select the above five options
and set ok
it will work for u but keep one thing in mind that u can not see the tables
or views u can only execute the hetrogenious queries.
from
sufian
"Dejan Sarka" wrote:

> Check the "Configuring Linked Servers" topic in Books OnLine
> (mk:@.MSITStore:C:\Program%20Files\Micros
oft%20SQL%20Server\80\Tools\Books\
ad
> minsql.chm::/ad_1_server_4uuq.htm).
>
> Any specific errors?
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
>

Link SQL Server 2005 Tables to external database through ODBC

Hi

I'm a newbie at SQL 2005 and I'm trying to create linked tables to our ERP system through ODBC. I can do this in MS Access or vb.net by using the ERP system's ODBC driver, but I am lost when it comes to SQL Server 2005.

Thanks for any help

Using a linked server, you will need to have either a OLEDB driver or a support for the OLEDB for ODBC provider. Using OPENDATASOURCE you will need to have the init string (connectionstring) for the database. You will find more information in the BOL under the topic OPENDATASOURCE.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Link SQL Server 2005 Tables to external database through ODBC

Hi

I'm a newbie at SQL 2005 and I'm trying to create linked tables to our ERP system through ODBC. I can do this in MS Access or vb.net by using the ERP system's ODBC driver, but I am lost when it comes to SQL Server 2005.

Thanks for any help

Using a linked server, you will need to have either a OLEDB driver or a support for the OLEDB for ODBC provider. Using OPENDATASOURCE you will need to have the init string (connectionstring) for the database. You will find more information in the BOL under the topic OPENDATASOURCE.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Link Servers with Delegation

I am trying to implement a linked server that uses integrated authentication on a 64 bit Wndows 2003 SP1 server. I have both Sql Server 2005 and Sql Server installed, and have successfully created database link that is able to use double hop authentiction on the Sql Server 2005 instance. I am unable to do the same usign the Sql Server 2000 instance. Does anyone know if double hop uathentication using Kerberos is supported on Sql Server 2000. The linked server on Sql Server 2005 is created using this syntax

"EXEC sp_addlinkedserver @.server=’LinkedServer’,

@.srvproduct=''",

@.provider='SQLNCLI',

@.datasrc=’SQLB’,--the data source

@.provstr="Integrated Security=SSPI; "

"exec sp_addlinkedsrvlogin ‘LinkedServer’, 'true'"

SPN's and domain accounts have been created as documented and those same accounts are used in both the Sql Server 2005 and Sql Server 2000 instances.

The error message going from a Sql Server 2000 or 2005 client, to the Sql Server 2000 instance that has the linked server using the SQLNCLI provider is

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLNCLI' reported an error. Authentication failed.
[OLE/DB provider returned message: Communication link failure]
[OLE/DB provider returned message: Named Pipes Provider: No process is on the other end of the pipe.
]
[OLE/DB provider returned message: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.]
OLE DB error trace [OLE/DB Provider 'SQLNCLI' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].

If I use the Sql Server 2000 OLEDB provider when creating the link I get this error

Server: Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

The same link using the SQLNCLI provider in Sql Server 2005

works and I am able to use double hop authentication.

My question is , does anyone know if double hop authentication is supported using a Sql Server 2000 linked server?

Using Kerberos as Authentication protocol and the appropiate configuration this should work even for SQL Server 2000.

http://msdn2.microsoft.com/en-us/library/aa905162(sql.80).aspx
http://msdn2.microsoft.com/en-us/library/ms189580.aspx

Jens K. Suessmeyer.

http://www.sqlserver2005.de

link server with case expression

anybody know how many case expression can be in linked server query?
when i have more then 10 "case" expression witjh linked server, sql query
give this message :
Server: Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Server: Msg 170, Level 15, State 1, Line 1
Line 8: Incorrect syntax near 'Qry1068'.
Server: Msg 125, Level 15, State 1, Line 1
Case expressions may only be nested to level 10.
any microsoft document or page show the detail about this?
Thanks in advance
SoonyuPlease post your query
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"soonyu" wrote:

> anybody know how many case expression can be in linked server query?
> when i have more then 10 "case" expression witjh linked server, sql query
> give this message :
> Server: Msg 8180, Level 16, State 1, Line 1
> Statement(s) could not be prepared.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 8: Incorrect syntax near 'Qry1068'.
> Server: Msg 125, Level 15, State 1, Line 1
> Case expressions may only be nested to level 10.
> any microsoft document or page show the detail about this?
> Thanks in advance
> Soonyu|||l 2000 sp3 sit in win2003
when we run the quere in server A.. it look okay with case expression more
then 10
but when we run query in server B with linkserver to server A
sql server give this message
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Qry1068'.
Msg 125, Level 15, State 4, Line 1
Case expressions may only be nested to level 10.
select distinct rp.invoice_id,
CASE
WHEN (rp.product_group_code = 2 and c.catelog_id = 1) then 'PG'
WHEN (rp.product_group_code = 2 and c.catelog_id = 3) then 'JB'
WHEN (rp.product_group_code = 9 and c.catelog_id = 1) then 'KL'
WHEN (rp.product_group_code = 9 and c.catelog_id = 2) then 'SG'
WHEN (rp.product_group_code = 9 and c.catelog_id = 3) then 'JB'
WHEN (rp.product_group_code = 8 and c.catelog_id = 1) then 'GEORGETOWN'
WHEN (rp.product_group_code = 3 and c.catelog_id = 2) then 'GENTING'
WHEN (rp.product_group_code not in (2, 3, 8, 9) and c.catelog_id = 1) then
'PERAK'
WHEN (rp.product_group_code not in (2, 3, 8, 9) and c.catelog_id = 2) then
'N9'
WHEN (I.product_code not in (2, 3, 8, 9) and c.catelog_id = 3) then 'KEDAH'
WHEN (I.product_code not in (2, 3, 8, 9) and c.catelog_id = 3) then 'PERIS'
ELSE ''
END
from a.db1.dbo.invoice rp, a.db1.dbo.contract c
WHERE rp.id=c.id
if i take out of one of the case condition, no error and result come out
I just wonder whether this is sql2000 sp3 bug or limitation using case
expression over sql linked server
Regards
"Wayne Snyder" wrote:
> Please post your query
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "soonyu" wrote:
>

Link Server to MySQL Database

Hi

I ma trying to create a linked server to a MySQL database via ODBC. I am running SQL 2005 on Windows 2003 R2 (64-bit). Can any one point me in the right direction .

Any ideas|||MSDASQL (provider shipped with SS2005 which serves as OLEDB-ODBC bridge) is not supported in 64-bit. Check if there is an 64-bit OLEDB provider for MySQL that you can use directly.|||

Thanks for that i will look into it.

|||

Hi James,

Were you able to do that? I am having the trouble doing the same. If you have done, could you please post the procedure.

Thanks,

Neelesh:)

|||Unfortunatly I have not had much time to look at it. I have not managed to find a free / cheap OLE MYSQL driver. Sorry I was not much help|||Can you guys post your email? I can send you a documentation for creating linked servers with MySQL|||

My email address is jamesholmes@.greggs.co.uk

Thanks

|||

Have you tried email me ?

Thanks

Link Server to MySQL Database

Hi

I ma trying to create a linked server to a MySQL database via ODBC. I am running SQL 2005 on Windows 2003 R2 (64-bit). Can any one point me in the right direction .

Any ideas|||MSDASQL (provider shipped with SS2005 which serves as OLEDB-ODBC bridge) is not supported in 64-bit. Check if there is an 64-bit OLEDB provider for MySQL that you can use directly.|||

Thanks for that i will look into it.

|||

Hi James,

Were you able to do that? I am having the trouble doing the same. If you have done, could you please post the procedure.

Thanks,

Neelesh:)

|||Unfortunatly I have not had much time to look at it. I have not managed to find a free / cheap OLE MYSQL driver. Sorry I was not much help|||Can you guys post your email? I can send you a documentation for creating linked servers with MySQL|||

My email address is jamesholmes@.greggs.co.uk

Thanks

|||

Have you tried email me ?

Thanks

Link Server settings

Hello:
I am trying to create linked server ( another SQL Server) to my SQL Server,
using Enterprise Manager, but new alias pointed me only to master database.
How can I change settings of the link server to get access to pubs database?
Thanks,
GBYou could change the default database for the user the linked server is
configured to use, using sp_defaultdb on the remote server.
Or, use the USE statement or proper 4-part naming to qualify the database
name you are accessing from the local server. e.g. you should not be saying
this:
SELECT <col_list> FROM LinkedServer...TableName;
You should be saying:
SELECT <col_list> FROM LinkedServer.DatabaseName.dbo.TableName;
"GB" <v7v1k3@.hotmail.com> wrote in message
news:y_zvf.57264$OU5.43252@.clgrps13...
> Hello:
> I am trying to create linked server ( another SQL Server) to my SQL
> Server,
> using Enterprise Manager, but new alias pointed me only to master
> database.
> How can I change settings of the link server to get access to pubs
> database?
> Thanks,
> GB
>|||You could change the default database for the user the linked server is
configured to use, using sp_defaultdb on the remote server.
Or, use the USE statement or proper 4-part naming to qualify the database
name you are accessing from the local server. e.g. you should not be saying
this:
SELECT <col_list> FROM LinkedServer...TableName;
You should be saying:
SELECT <col_list> FROM LinkedServer.DatabaseName.dbo.TableName;
"GB" <v7v1k3@.hotmail.com> wrote in message
news:y_zvf.57264$OU5.43252@.clgrps13...
> Hello:
> I am trying to create linked server ( another SQL Server) to my SQL
> Server,
> using Enterprise Manager, but new alias pointed me only to master
> database.
> How can I change settings of the link server to get access to pubs
> database?
> Thanks,
> GB
>|||What you see in Enterprise Manager is simply the default database for the
account that you are using to authenticate to the linked server. Changed th
e
default database for that user, and refresh and you will see another
databases tables.
You will still be able to access all databases to which that user has access
.
kevin
"GB" wrote:

> Hello:
> I am trying to create linked server ( another SQL Server) to my SQL Server
,
> using Enterprise Manager, but new alias pointed me only to master database
.
> How can I change settings of the link server to get access to pubs databas
e?
> Thanks,
> GB
>
>

link server reset connection

I have recently begun getting a sp_reset_connection on one linked
server. Both are SQL 2000 build 818 (SP3).
The login time between each reset is about 10 minutes. In the past the
login time was at least 24 to 48 hours.
I have rebooted both servers and it's still happening. Any ideas?>> On Sun, Nov 19, 2006 at 6:49 PM, in message
<1163987375.312919.163700@.e3g2000cwe.googlegroups.com>,
<bic1ster@.gmail.com> wrote:
> I have recently begun getting a sp_reset_connection on one linked
> server. Both are SQL 2000 build 818 (SP3).
> The login time between each reset is about 10 minutes. In the past
> the
> login time was at least 24 to 48 hours.
> I have rebooted both servers and it's still happening. Any ideas?
Did anything change on the servers or the network (such as a new
firewall between the hosts)?|||I will check. I did look at the Compaq Insight NIC application and
they don't have any packet receive errors etc.. I also looked at event
logs and nothing pecular was in them. Maybe it's time for some
perfmon.
We did have a switch blade failure last month. I don't remember if
these were on that blade.|||It's an undocumented stored procedure used internally by SQL
Server. It shows up when using connection pooling
and it is called to reset the connection options,settings,
etc before reusing the connection in the connection pool so
that the settings don't persist to another client
connection. In and of itself, just seeing it execute every
10 minutes doesn't necessarily indicate any problems. Why do
you have concerns? If the issue is that it's every 10 mins
now instead of 24 to 48 hours, you can run a trace and see
what the connection is executing to see what's going on with
whatever application, process. It may be nothing though.
-Sue
On 19 Nov 2006 17:49:35 -0800, bic1ster@.gmail.com wrote:
>I have recently begun getting a sp_reset_connection on one linked
>server. Both are SQL 2000 build 818 (SP3).
>The login time between each reset is about 10 minutes. In the past the
>login time was at least 24 to 48 hours.
>I have rebooted both servers and it's still happening. Any ideas?|||> connection. In and of itself, just seeing it execute every
> 10 minutes doesn't necessarily indicate any problems. Why do
> you have concerns? If the issue is that it's every 10 mins
It wasn't happening two weeks ago as I monitor who is connected to the
server. ok, I will look at profiler for a while and see what happens.
I think the app was updated recently too. Maybe something else to
check with the developer.

Link Server problem

Hi, there,
I have two SQL Server 2000, which are SQLA -- with SAN connected; SQLB --
just a bald SQL Server.
I want to set up linked server from SQLA , so I can run query like "select *
from SQLB.dbname.dbo.tablename". The command I use is
sp_addlinkedserver @.server = 'SQLB'
After I ran this code, I span the "Linked servers" node of Enterprise
Manager, I saw an image of a linked server named "SQLB". Then I run the
query "select * from SQLB.dbname.dbo.tablename" from Query Analyzer of
SQLA, I got the following errors
Server: Msg 17, Level 16, State 1, Line 1
SQL Server does not exist or access denied.
I'm the system administrator of the two servers, and the account I ran the
query is sys admin account. I don't know why the command failed. I drop the
linked server and readd the linked server again. I got the same result.
Who can help a bit?
Thanks,
JamieHi
If you don't call sp_addlinkedsvrlogin, then you will have a default match
between logins from ServerA to ServerB created by sp_addlinkedserver. Have
you checked that you have access with the current login to serverA on serverB?
You may want to try calling
EXEC sp_addlinkedsrvlogin 'SQLB', 'true'
John
"Jamie" wrote:
> Hi, there,
> I have two SQL Server 2000, which are SQLA -- with SAN connected; SQLB --
> just a bald SQL Server.
> I want to set up linked server from SQLA , so I can run query like "select *
> from SQLB.dbname.dbo.tablename". The command I use is
> sp_addlinkedserver @.server = 'SQLB'
> After I ran this code, I span the "Linked servers" node of Enterprise
> Manager, I saw an image of a linked server named "SQLB". Then I run the
> query "select * from SQLB.dbname.dbo.tablename" from Query Analyzer of
> SQLA, I got the following errors
> Server: Msg 17, Level 16, State 1, Line 1
> SQL Server does not exist or access denied.
> I'm the system administrator of the two servers, and the account I ran the
> query is sys admin account. I don't know why the command failed. I drop the
> linked server and readd the linked server again. I got the same result.
> Who can help a bit?
> Thanks,
> Jamie
>
>|||Thank you John! I just found out the real problem. We have IP Address
problem of SQLB. After it is fixed, I can ran query across servers.
Jamie
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:2BD42FE7-07D4-4C46-9D93-2E01CBC87E8A@.microsoft.com...
> Hi
> If you don't call sp_addlinkedsvrlogin, then you will have a default match
> between logins from ServerA to ServerB created by sp_addlinkedserver. Have
> you checked that you have access with the current login to serverA on
> serverB?
> You may want to try calling
> EXEC sp_addlinkedsrvlogin 'SQLB', 'true'
> John
> "Jamie" wrote:
>> Hi, there,
>> I have two SQL Server 2000, which are SQLA -- with SAN connected; SQLB --
>> just a bald SQL Server.
>> I want to set up linked server from SQLA , so I can run query like
>> "select *
>> from SQLB.dbname.dbo.tablename". The command I use is
>> sp_addlinkedserver @.server = 'SQLB'
>> After I ran this code, I span the "Linked servers" node of Enterprise
>> Manager, I saw an image of a linked server named "SQLB". Then I run the
>> query "select * from SQLB.dbname.dbo.tablename" from Query Analyzer of
>> SQLA, I got the following errors
>> Server: Msg 17, Level 16, State 1, Line 1
>> SQL Server does not exist or access denied.
>> I'm the system administrator of the two servers, and the account I ran
>> the
>> query is sys admin account. I don't know why the command failed. I drop
>> the
>> linked server and readd the linked server again. I got the same result.
>> Who can help a bit?
>> Thanks,
>> Jamie
>>
>>

Link Server problem

Hi, there,
I have two SQL Server 2000, which are SQLA -- with SAN connected; SQLB --
just a bald SQL Server.
I want to set up linked server from SQLA , so I can run query like "select *
from SQLB.dbname.dbo.tablename". The command I use is
sp_addlinkedserver @.server = 'SQLB'
After I ran this code, I span the "Linked servers" node of Enterprise
Manager, I saw an image of a linked server named "SQLB". Then I run the
query "select * from SQLB.dbname.dbo.tablename" from Query Analyzer of
SQLA, I got the following errors
Server: Msg 17, Level 16, State 1, Line 1
SQL Server does not exist or access denied.
I'm the system administrator of the two servers, and the account I ran the
query is sys admin account. I don't know why the command failed. I drop the
linked server and readd the linked server again. I got the same result.
Who can help a bit?
Thanks,
Jamie
Hi
If you don't call sp_addlinkedsvrlogin, then you will have a default match
between logins from ServerA to ServerB created by sp_addlinkedserver. Have
you checked that you have access with the current login to serverA on serverB?
You may want to try calling
EXEC sp_addlinkedsrvlogin 'SQLB', 'true'
John
"Jamie" wrote:

> Hi, there,
> I have two SQL Server 2000, which are SQLA -- with SAN connected; SQLB --
> just a bald SQL Server.
> I want to set up linked server from SQLA , so I can run query like "select *
> from SQLB.dbname.dbo.tablename". The command I use is
> sp_addlinkedserver @.server = 'SQLB'
> After I ran this code, I span the "Linked servers" node of Enterprise
> Manager, I saw an image of a linked server named "SQLB". Then I run the
> query "select * from SQLB.dbname.dbo.tablename" from Query Analyzer of
> SQLA, I got the following errors
> Server: Msg 17, Level 16, State 1, Line 1
> SQL Server does not exist or access denied.
> I'm the system administrator of the two servers, and the account I ran the
> query is sys admin account. I don't know why the command failed. I drop the
> linked server and readd the linked server again. I got the same result.
> Who can help a bit?
> Thanks,
> Jamie
>
>
|||Thank you John! I just found out the real problem. We have IP Address
problem of SQLB. After it is fixed, I can ran query across servers.
Jamie
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:2BD42FE7-07D4-4C46-9D93-2E01CBC87E8A@.microsoft.com...[vbcol=seagreen]
> Hi
> If you don't call sp_addlinkedsvrlogin, then you will have a default match
> between logins from ServerA to ServerB created by sp_addlinkedserver. Have
> you checked that you have access with the current login to serverA on
> serverB?
> You may want to try calling
> EXEC sp_addlinkedsrvlogin 'SQLB', 'true'
> John
> "Jamie" wrote:

Link Server problem

Hi, there,
I have two SQL Server 2000, which are SQLA -- with SAN connected; SQLB --
just a bald SQL Server.
I want to set up linked server from SQLA , so I can run query like "select *
from SQLB.dbname.dbo.tablename". The command I use is
sp_addlinkedserver @.server = 'SQLB'
After I ran this code, I span the "Linked servers" node of Enterprise
Manager, I saw an image of a linked server named "SQLB". Then I run the
query "select * from SQLB.dbname.dbo.tablename" from Query Analyzer of
SQLA, I got the following errors
Server: Msg 17, Level 16, State 1, Line 1
SQL Server does not exist or access denied.
I'm the system administrator of the two servers, and the account I ran the
query is sys admin account. I don't know why the command failed. I drop the
linked server and readd the linked server again. I got the same result.
Who can help a bit?
Thanks,
JamieHi
If you don't call sp_addlinkedsvrlogin, then you will have a default match
between logins from ServerA to ServerB created by sp_addlinkedserver. Have
you checked that you have access with the current login to serverA on server
B?
You may want to try calling
EXEC sp_addlinkedsrvlogin 'SQLB', 'true'
John
"Jamie" wrote:

> Hi, there,
> I have two SQL Server 2000, which are SQLA -- with SAN connected; SQLB --
> just a bald SQL Server.
> I want to set up linked server from SQLA , so I can run query like "select
*
> from SQLB.dbname.dbo.tablename". The command I use is
> sp_addlinkedserver @.server = 'SQLB'
> After I ran this code, I span the "Linked servers" node of Enterprise
> Manager, I saw an image of a linked server named "SQLB". Then I run the
> query "select * from SQLB.dbname.dbo.tablename" from Query Analyzer of
> SQLA, I got the following errors
> Server: Msg 17, Level 16, State 1, Line 1
> SQL Server does not exist or access denied.
> I'm the system administrator of the two servers, and the account I ran the
> query is sys admin account. I don't know why the command failed. I drop th
e
> linked server and readd the linked server again. I got the same result.
> Who can help a bit?
> Thanks,
> Jamie
>
>|||Thank you John! I just found out the real problem. We have IP Address
problem of SQLB. After it is fixed, I can ran query across servers.
Jamie
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:2BD42FE7-07D4-4C46-9D93-2E01CBC87E8A@.microsoft.com...[vbcol=seagreen]
> Hi
> If you don't call sp_addlinkedsvrlogin, then you will have a default match
> between logins from ServerA to ServerB created by sp_addlinkedserver. Have
> you checked that you have access with the current login to serverA on
> serverB?
> You may want to try calling
> EXEC sp_addlinkedsrvlogin 'SQLB', 'true'
> John
> "Jamie" wrote:
>

Link Server Connection failure

I have a linked server defined in my SQL 2000 server that connects to an Ora
cle databse that stops working for no apparent reason. Restarting SQL Serve
r fixes the problem. The linked server works fine for months at a time and
then stops working and give
s a login failure message. Nothing gets written to the server event log or
the SQL Server error log.Where do you see the login failed message? What is the exact text of the
login failed message? Once you start getting this message can you use the
Oracle client tools on the SQL Server box to connect to the Oracle instance
using the same login SQL Server is configured to use?
280106 HOW TO: Set Up and Troubleshoot a Linked Server to Oracle in SQL
Server
http://support.microsoft.com/?id=280106
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

LINK SERVER BAD PERFORMANTCE

A SP with join's between tables of 2 SQL 2000 linked server used to work
relatively quick.
Since a few weeks the same SP that runs on +/- the same amount of data takes
much more time (43 sec).
When i copied the DB so that the SP should run on the same server it takes
only 13 seconds.
Probably the customer installed on the server but obviously they deny that
:)Hi
Run sp_updatestats (on both servers) and see what is going on
"Chedva" <chedvag@.matrix-it.co.il> wrote in message
news:eoueGxomGHA.1912@.TK2MSFTNGP02.phx.gbl...
>A SP with join's between tables of 2 SQL 2000 linked server used to work
> relatively quick.
> Since a few weeks the same SP that runs on +/- the same amount of data
> takes
> much more time (43 sec).
> When i copied the DB so that the SP should run on the same server it takes
> only 13 seconds.
> Probably the customer installed on the server but obviously they deny that
> :)
>sql

LINK SERVER BAD PERFORMANTCE

A SP with join's between tables of 2 SQL 2000 linked server used to work
relatively quick.
Since a few ws the same SP that runs on +/- the same amount of data takes
much more time (43 sec).
When i copied the DB so that the SP should run on the same server it takes
only 13 seconds.
Probably the customer installed on the server but obviously they deny that
:)Hi
Run sp_updatestats (on both servers) and see what is going on
"Chedva" <chedvag@.matrix-it.co.il> wrote in message
news:eoueGxomGHA.1912@.TK2MSFTNGP02.phx.gbl...
>A SP with join's between tables of 2 SQL 2000 linked server used to work
> relatively quick.
> Since a few ws the same SP that runs on +/- the same amount of data
> takes
> much more time (43 sec).
> When i copied the DB so that the SP should run on the same server it takes
> only 13 seconds.
> Probably the customer installed on the server but obviously they deny that
> :)
>

Link Report Problems

I have established a set of base reports which are then linked to a variety of department specific folders. Moreover, many of the reports have the capability to "Drill Through". The problem occurs when clicking on a Drill Through field in one of the department specfic folder -- it directs the drill through to a report in the Base folder instead of the department specific folder. Pleaseeeeee help!The behavior you are seeing is the expected behavior for linked reports. Relative paths are resolved based on the location of the source report, and not the linked report.|||Hopefully in a future release there could be an option to select either base or linked folder(s).

link from sql2005 (64-bit) to sql 7 (32-bit).

Hi: I'm trying to link from sql2005 (64-bit) SP1 to sql 7 (32-bit) SP4.
I get the following error:
OLE DB provider "SQLNCLI" for linked server "sql7" returned message
"Unspecified error".
OLE DB provider "SQLNCLI" for linked server "sql7" returned message "The
stored procedure required to complete this operation could not be found on
the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider
"SQLNCLI" for linked server "sql7". The provider supports the interface, but
returns a failure code when it is used.
How can I get these servers linked?
Regards,I just ran into a similar problem, but from 64 bit SQL 2005 trying to link
to 32 bit SQL 2000. What I did on the 2000 server was run the instcat.sql
from the latest service pack installation I did on the 2000 server. In my
case it was SP3a. There are instructions to change the last insert statement
to the table to reflect the version of SQL Server (select @.@.version) that
you are running. In my case it was .977. That fixed my problems.
Jackie
"José" <josedc@.starmedia.com> wrote in message
news:OE2iT1GlGHA.3720@.TK2MSFTNGP03.phx.gbl...
> Hi: I'm trying to link from sql2005 (64-bit) SP1 to sql 7 (32-bit) SP4.
> I get the following error:
> OLE DB provider "SQLNCLI" for linked server "sql7" returned message
> "Unspecified error".
> OLE DB provider "SQLNCLI" for linked server "sql7" returned message "The
> stored procedure required to complete this operation could not be found on
> the server. Please contact your system administrator.".
> Msg 7311, Level 16, State 2, Line 1
> Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider
> "SQLNCLI" for linked server "sql7". The provider supports the interface,
> but
> returns a failure code when it is used.
> How can I get these servers linked?
> Regards,
>sql