November 8, 2011 at 8:33 am
Proddb03 is the name of another server on our network. I want to be able to query it from Proddb04, without using a linked server setup, so I'm trying...
select top 100 * from proddb03.mfr.dbo.mfr
select top 100 * from proddb03.[mfr]..mfr
... but keep getting this error...
"Could not find server 'proddb03' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers."
I even tried referencing the machine by the inside IP address thusly...
select top 100 * from [192.168.1.234].mfr.dbo.mfr
I DON'T WANT TO USE LINKED SERVERS... Why isn't SQL finding the other server on the network by the name I'm prefixing?
Please help, this is just killing me right now. I KNOW I've done this before. Difficult to search the issue as well, sorry if this is a double post.
November 8, 2011 at 8:44 am
BTW, all SQL Servers in the domain are running in Mixed Mode authentication, and I'm logged in as a domain administrator. Thanks!
November 8, 2011 at 8:47 am
Why do you not want to use with Linked Servers? This is what they are designed to do.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 8, 2011 at 8:54 am
It just seems like a workaround to me. I'd rather trim the fat and get 4 part naming to work as it should.
Is there a reason why referencing another server in my domain won't work using "FROM [servername].[dbname].[owner].[tablename]"?
November 8, 2011 at 8:56 am
It's the way it's supposed to be used by DESIGN.
No way around it except openrowset / openquery. I forget which one doesn't required the linked server.
November 8, 2011 at 8:59 am
Thanks for setting me straight. Not sure why I was convinced otherwise.
November 8, 2011 at 9:06 am
Using openrowset will allow you to perform selects between servers that are not using linked servers. We do this amongst many servers on our network. We do this because if someone gains access to one of our servers it will not allow them to be able to acces data on other servers.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
November 8, 2011 at 9:07 am
sjimmo (11/8/2011)
Using openrowset will allow you to perform selects between servers that are not using linked servers. We do this amongst many servers on our network. We do this because if someone gains access to one of our servers it will not allow them to be able to acces data on other servers.
...
untill he reads all the query definitions on the server and picks up all the openrowset commands ;-).
November 8, 2011 at 10:57 am
Ninja's_RGR'us (11/8/2011)
sjimmo (11/8/2011)
Using openrowset will allow you to perform selects between servers that are not using linked servers. We do this amongst many servers on our network. We do this because if someone gains access to one of our servers it will not allow them to be able to acces data on other servers....
untill he reads all the query definitions on the server and picks up all the openrowset commands ;-).
yep...at least if you specify a remote username/password for a linked server, the password is stored in a CREDENTIAL object, which i've never seen as hackable (to date)...i've tripped over it a lot of times when i wanted to script objects out like mail server settings nd linked servers.
the openrowset would show the password in clear text...that's much less secure than a linked server.
Lowell
November 8, 2011 at 10:09 pm
Is there a reason why referencing another server in my domain won't work using "FROM [servername].[dbname].[owner].[tablename]"?
Even when referencing a server with the 4-part requires a linked server. Whether you use the "FROM [servername].[dbname].[owner].[tablename]" or FROM OPENQUERY([servername], '') You still need to set it up.
To the best of my knowledge, there is no other way. Setting up the linked server is what actually adds it to sys.servers.
The only other way I can think this would be possible would be using an ADO connection (or something similar) and then creating a system/user-level ODBC connection to that server. You could then reference it using the method you're attempting. However, in SSMS, I don't believe there is any other way.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
November 8, 2011 at 11:26 pm
Is there a reason why referencing another server in my domain won't work using "FROM [servername].[dbname].[owner].[tablename]"?
Two reasons:
1) Because you are not connected to another Server. Even if you have the same account which has all permissions on both the servers, SQL Server can't authenticate you this way. The Login Access is limited to Current Server Connection ONLY.
2) The authenticated way is Linked Server / openrowset / openquery. This way you pass credentials to connect to another Server and it satisfy #1.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply