February 27, 2008 at 5:06 am
I'm getting some strange results when running a query that combines data from local and a single remote source. It would seem to return twice the local data rather than picking up the single remote source.
Further tests reveal that this does seem to be the case. For example, the following script returns only data from the local server, whichever it is.
Select count(*) from [ ]
UNION ALL
Select count(*) from [ ]
The two tables (with the same name) have different numbers of records, so I would expect to see:
--------------
Instead I see:
--------------
(or duplicated ) depending on which server it is run.
This was working correctly recently. The only thing that has changed in the past few days (although I'm not convinced that this is connected), is that we have rebuilt the application on ONE of the servers. The central SQL Server wasn't touched (i.e. master, msdb, etc), but the application databases where completely re-created on a newly built set of disk arrays.
The Linked Server is set to run through the "Microsoft OLE DB PRovider for SQL Server" and use a particular login to the remote database as "Be made using this security context".
Another knock-on would seem to be that the only visible files (in Enterprise Manager > Security > Linked Servers > Tables) are those that are in the default database for this user LOCALLY. Using the sp_ _ex tools in Query Analyser also only shows these tables unless a database or table is specified. Then it returns the data for that linked information.
February 27, 2008 at 6:44 am
It would be helpful if you would post the Query in its entirety and more of the results set. I don't see anything in the post that you have here.
Regards, Irish
February 27, 2008 at 7:15 am
Oops, for some reason half the important bits of the email disappeared! I'll try reposting without some items that might get misread. Assume square brackets round everything and correct names used.
------------------------------------
I'm getting some strange results when running a query that combines data from local and a single remote source. It would seem to return twice the local data rather than picking up the single remote source.
Further tests reveal that this does seem to be the case. For example, the following script returns only data from the local server, whichever it is.
Select count(*) from databasename.dbo.tablename
UNION ALL
Select count(*) from servername.databasename.dbo.tablename
The two tables (with the same name) have different numbers of records, so I would expect to see:
--------------
count1
count2
Instead I see:
--------------
count1
count1
(or duplicated count2) depending on which server it is run.
This was working correctly recently. The only thing that has changed in the past few days (although I'm not convinced that this is connected), is that we have rebuilt the application on ONE of the servers. The central SQL Server wasn't touched (i.e. master, msdb, etc), but the application databases where completely re-created on a newly built set of disk arrays.
The Linked Server is set to run through the "Microsoft OLE DB PRovider for SQL Server" and use a particular login to the remote database as "Be made using this security context".
Another knock-on would seem to be that the only visible files (in Enterprise Manager > Security > Linked Servers > Tables) are those that are in the default database for this user LOCALLY. Using the sp_table_ex tool (and the rest!) in Query Analyser also only shows these tables unless a database or table is specified. Then it returns the data for that linked information.
February 27, 2008 at 8:57 am
When using linked server are you using the four part naming convention. If so did you try running the query to see if the other sql server fetches results from the linked server. Also let us know in what versions the sql servers are running.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 27, 2008 at 9:16 am
Yes, I'm using servername.databasename.dbo.tablename for both queries.
Both servers just return data from their local table, whatever the servername is in the above 4-part name.
Both still using SQL Server 2000.
Regards
Richard
February 27, 2008 at 9:21 am
Can you explain in detail please. can't get what the problem is.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 27, 2008 at 9:47 am
You should not have to qualify the Local Server with the four parts. If you run the query within the Database that the table resides in the most you would need is schema.object.
Assuming you are in the DB on the Local side I would run the query like this:
select count(1) from table
UNION ALL
Select count(1) from server.database.schema.table
I ran this in the context of the Local Database without issue, and received the counts as expected.
Regards, Irish
February 27, 2008 at 12:27 pm
Okay, sorry with context already I my mind, I can't be explaining very well.
Simply put when I run a query like
select * from servername.databasename.dbo.tablename
I do NOT get the data from the remote server I get results from the local server. So I get the equivalent of
select * from databasename.dbo.tablename
instead.
February 27, 2008 at 12:40 pm
So when you run the query with the linked server do you get any errors. or it returns o rows available.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 27, 2008 at 3:00 pm
No, I get the data fom the local server.
To clarify, when running this
select * from remoteservername.databasename.dbo.tablename
I do NOT get the data from the remote server I get results from the local server. So I get the equivalent of
select * from locaalservername.databasename.dbo.tablename
For example, there are 2 servers called "remoteserver" and "localserver". In "remoteserver" there is a database called "places". Within "places" there is a table called "locations". This table has 30 records all beginning "London, ..". On "localserver" there is also a database called "places" and a table called "locations". However within that table there is 50 records all beginning "Chicago, ..".
When I run the query to pull back data from the remoteserver as follows
select * from remoteserver.places.dbo.locations
I would expect to see 30 records all beginning "London, ..". This does not happen, instead I get the 50 records all beginning "Chicago, ..". This is the equivalent of returning
select * from localserver.places.dbo.locations
BTW, I'm sure I started this discussion in SQL Server 2000, which is the server level. Somehow this has ended up in SQL Server 2005, which is incorrect.
February 27, 2008 at 3:04 pm
Okie can we do this for our testing. create a stored procedure in the remote server database with the same select query. then call the stored procedure in the linked server local database. See what the results are. i hope there is some impersonation problem here. Do let me know the results of this.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
March 4, 2008 at 10:47 am
Okay, finally managed to get back onto this.
Within Stored Procedures was where we first noticed the issue. I've done some extensive testing looking at the different strings used to connect to the other database and I can find nothing that works either in or out of Stored Procedures.
I tried using double quotes to surround the remote servername, and square brackets, and removing all limiters.
Again, I repeat that this is with SQL Server 2000 not 2005.
Now, the solution!
First, looking at the details in Enterprise Manager can be misleading. Several items had gone missing (not sure how, but have a chance to test this again in a couple of weeks), which is not entirely unusual with connections like this. I should have taken it's literal meaning, but not knowing a combination of missing data about the systems I couldn't experiment much.
Setting up a completely new connection with the correct servername for the datasource and including the destination database as the Catalog managed to get me the correct results when using the 4-part naming convention. Then, picking my way through the scripted setup in Query Analyser took another hour of trying different options with quotes and none until it would recreate exactly what was the correct connection. Essentially, if you don't need quotes around the servername, don't use them!
-- first delete the login and server
-- I've put quotes round my remote-server because it has a dash
-- This may not be necessary, you'll need to check
if exists ( select * from master..sysservers where srvname = 'remote-server' ) begin
EXEC master..sp_droplinkedsrvlogin 'remote-server', NULL
exec master..sp_dropserver 'remote-server'
end
-- add the server back in
EXEC master..sp_addlinkedserver
@server = 'remote-server',
@srvproduct = '',
--@location = NULL,
--@provstr = '' ,
@provider = 'SQLOLEDB',
@catalog = 'remote-database',
@datasrc = 'remote-server'
GO
EXEC master..sp_addlinkedsrvlogin 'remote-server', TRUE
GO
EXEC sp_linkedservers
GO
So,
Thanks for help!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply