Select * from otherdatabase 1

[mflancour]

Used the following to link a database...appeared to work...not sure how to check:

USE master;
GO
EXEC sp_addlinkedserver
'SEATTLESales',
N'SQL Server'
GO

Used the following to query a table from above linked database and received "Invalid object name 'Otherdatabase.dbo.oeeh'.":

select * from Otherdatabase.dbo.oeeh

Permissions are fine...never linked anything before, so thanks for any help you can give.

 

[gmmastros]

When you want to use another database in a query, the structure is something like...

Select * From OtherDatabase.dbo.TableName

When you want to use a database on a different server, you need to use the 4 part naming convention, like so...

Select * From OtherServer.OtherDatabase.dbo.TableName


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Catadmin]

You need to use Server.Database.Owner.Table (my mnemonic is "Should Document Over-Time" to remember it) in order to query.

So, with a linked server, it should be:

Select *
from LinkedServer.OtherDatabase.dbo.oeeh

If that doesn't help, let us know.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[mflancour]

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

guess I was wrong about the security...both databases are using mixed mode, so not sure why it wont let me.

 

[Catadmin]

Does the login in question exist on the linked server already? If not, you probably have to create it.

Also, is SeattleSales the Server name or the instance name? You have to specify both in a format like [ServerName\InstanceName].



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Catadmin]

Okay, not good with the typing today. Apologies for having to reiterate.

If SeattleSales is the instance name, make sure you specify the server name also, is what I meant to say. Again, apologies for my brain running ahead of my fingers today.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[mflancour]

Select * From server.OtherDatabase.dbo.oeelh

In the above code, .dbo. is the user? dbo does exist in both databases as the "sa"

Think I am missing somethign here...can one of you spell out the sql from linking to querying using this example?

server= server1
linked server= server2
currentdb= db1
db on linked server = db2
Table on linked server = oeeh
authentication mode = mixed
login id = jdoe
user exist on db1 as the "sa"= dbo
user exist on db2 as the "sa"= dbo

Thanks for helping

 

[Catadmin]

When we say "dbo" as the owner, DBO is the default schema on SQL 2000. All tables, databases, etc. created by anyone with the SysAdmin role should be owned by "dbo". Anyone not a part of the SysAdmin server role (or the dbowner database role) will own the table. So, if my login is Snoopy, and I have Create Table rights, but not DBowner or Sysadmin, my table will end up being "Snoopy.MyTable" when I create it.

In SQL 2005, it could be owned by any schema, depending on how your schemas and defaults are set up.

Regardless, check the objects in EM or SSMS and see who owns them to verify if it really is DBO as the owner or someone else. After that...

Does the SA login map to a local account on the server or a domain user account? Usually, it defaults to local unless specified otherwise. That being the case, I would find a domain user account to connect to your linked server or you will be unable to use linked servers properly. (Local accounts don't like talking to other boxes).

Once all this is straightened out, you should be able to run your query as "Select * from Server2.db2.<owner>.oeeh".



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[mflancour]

Thanks....I didn't quite follow: "find a domain user account to connect to your linked server"

how am I connecting? in the select statement? Does my login id have to be specified on the table even if I am an admin on the network?

 

[Catadmin]

You are not connecting via the SQL Statement. What you need is to get with your network administrator to get a dedicated (and plain vanilla) Domain user account for SQL Server. Only SQL Server Services should use this account (if any). Then, on each server, create a mapped login to the windows account name the network admin gives you.

Once this is created, use this login to link your servers together (with the sp_addlinkedserver).

If none of this makes sense, you will need to do some research in Books Online about SQL Server Logins and mapping windows logins. If you are not running in a domain and each SQL Server is a standalone machine, you can't use linked servers.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[mflancour]

Good morning. I really appologize, but this is REALLY giving me a hard time. I could just copy the table over, but I NEED to know how to do this. Here is what I have now;

srv1 has user: testuser with owner permissions
srv2 has user: testuser with owner permissions
domain controller has user: testuser with domain admin permissions

I droped the link to the table and added it agian using:
EXEC sp_addlinkedserver @server='tspro', @srvproduct='',
@provider='SQLOLEDB', @datasrc='tspro\sql_nxt'
* Newbi questeion, but the instance you refered to is a database name within the server? What is an instance?

Tried querying the table I need again with:
select * from tspro.[sql_nxt].testuser.oeeh
and received the following error:
Server: Msg 17, Level 16, State 1, Line 1
SQL Server does not exist or access denied.

It acutally attempts to query now for about 30 seconds Feels like I'm soooooo close then.......grrrrrrrrrr

 

[Catadmin]

Look up "instance names" in Books Online.

FYI: This is a basic need-to-know bit of information for doing DBA work in Microsoft's SQL Server and, unfortunately, there is no good way to explain it via posting. You'll need to do some research and playing with your database so you can get that knowledge ingrained so reflexively that when someone says "instance" you know exactly what they're talking about.

Sorry I can't help you further with this.


Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[SQLBill]

Just out of curiosity....

1. What edition/version of SQL Server are you using? Run this:
SELECT SERVERPROPERTY('Edition'),
SERVERPROPERTY('ProductVersion'),
SERVERPROPERTY('ProductLevel')

2. Is there a firewall between the two servers? If so, check with the firewall people to make sure the proper port is open.

-SQLBill

Posting advice: FAQ481-4875

 

[mflancour]

Holy carp! It is actually working!! VERY slow, but working. Now I just need to try to understand why. Here is what I did. Instead of using sql, I went to the enterprize manager under security and linked the server with the gui.

in teh security tab,under "for a login not defined in teh list above, connections will", when I set it to "be made using this security context" with the userid i made earlier "testuser" nothing worked. I changed that to "Be made using the login's current security context". suddenly it worked!! The only issue I have now is that it only works from the server. when I try to run a query using my pc it wont work....any ideas?

By the way thanks for all the help.

 

[Catadmin]

If the query is running really slowly, it could be a timeout issue from the PC. In order to verify that, though, we need the exact error from Query Analyzer that you get when trying to run it from your PC.

Can you post it?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[mflancour]

Sorry. Guess I put too much emphasys on the slowness. That's really only due to the number of records. At this point I really just want to know why it works from the server but not from my pc. I am pretty sure it has to do with the how the security is set up on the linked table, but not sure how to fix it yet.