March 16, 2011 at 9:45 am
Local Server: SQL Server 2008 Web Edition SP2
Linked Server: SQL Server 2005 Standard Edition SP2
Provider: SQL Server Native Client 10
SELECT * FROM linkedserver.db.dbo.table
Above query does not return all records and miss few. But on running this query again and again it returns whole records.
Any help that why sometimes linked server does not return all records?
March 16, 2011 at 11:33 am
1) How do you know it's not returning all records? (I'll explain why I asked as soon as you answer.)
2) Production database or dev database?
3) What security settings do you have on the linked server? If using a specific login, what are the database security perms on that login?
March 17, 2011 at 12:40 am
1) How do you know it's not returning all records? (I'll explain why I asked as soon as you answer.)
in fact I have a stored procedure which fetch data from LINKED SERVER and put in my local server (I am referring (calling) here local server the server on which I have created LINKED SERVER).
The query goes like following:
DECLARE @maxCustId INT
SELECT @maxCustId = MAX(CustomerId) FROM Customers
SELECT * INTO #temp FROM linkserver.DB.dbo.Customers where CustomerId > @maxCustId
-- Copy records to local db Customer table
DROP TABLE #temp
How do I know that records misses is:
Like I had Max CustomerId = 1138 in my local db and when I run above script my temp table misses CustoemrIds 1140, 1141. Linked server has customers upto 1160. `#temp` table has records upto 1160 but misses two records i.e. 1140, 1141
I run that script again and again and on 4th attempt the record `1141` added in #temp table but record 1140 was still missing.
I then put following query on local server to check record in linked server
SELECT * FROM linkserver.DB.dbo.Customers where CustomerId = 1140
Above query return no records.
To verify this I went to linked server and wrote same query.
-- This is the server which I am using as linked server in my local server
SELECT * FROM Customers where CustomerId = 1140
Custoemr 1140 was in db, which I was sure that it would be there but I run above script to verify it.
I come back to my local server and run this query
SELECT * FROM linkserver.DB.dbo.Customers where CustomerId = 1140
This time my linked server returns customer 1140 which it was not returning earlier.
I run the whole query again and now my #temp table has all records.
2) Production database or dev database?
Production databases both LINKED SERVER DB AND THE ONE WHERE I HAVE CREATED LINKED SERVER.
3) What security settings do you have on the linked server? If using a specific login, what are the database security perms on that login?
Security privileges are sysadmin for that user but is there any this to do with security because it misses few records not all?
March 17, 2011 at 5:23 am
kashif.nadeem (3/17/2011)
2) Production database or dev database?Production databases both LINKED SERVER DB AND THE ONE WHERE I HAVE CREATED LINKED SERVER.
Okay. I can understand the Linked Server DB being a production box (sort of), but the database / instance you're testing in is production also? I actually cringed when I read that. Is there any way you can move your testing off of Production so you don't break anything?
kashif.nadeem (3/17/2011)
3) What security settings do you have on the linked server? If using a specific login, what are the database security perms on that login?
Security privileges are sysadmin for that user but is there any this to do with security because it misses few records not all?
You didn't really answer the first part of this question. Open up the Linked Server. Go to the security page. There are several ways to set up security here. What options have you chosen? Mapped logins? Logins Not Be Made? That's the information I'm looking for.
The second part of the question refers to the "Be made using this security context" part of this page. Though, I suppose it could count for the Login Mapping part of this page.
-------------Q1 discussion below-------------
You know your data better than I do, so don't take this the wrong way, but I need to point out something (before I respond to the rest of the post).
kashif.nadeem (3/17/2011)
How do I know that records misses is:Like I had Max CustomerId = 1138 in my local db and when I run above script my temp table misses CustoemrIds 1140, 1141. Linked server has customers upto 1160. `#temp` table has records upto 1160 but misses two records i.e. 1140, 1141
Remember that just because you find a Max(ID) of something, does not mean that every single record between 0 and that Max actually exist in your database. Deletes can destroy "middle" records. So having a MAX(ID) of 1160 does not mean you have record 1140 and 1141 in your database.
Since you did verify 1140 exists in the source database, that makes this particular point a bit moot, but my concern is that your code doesn't actually account for this behavior in SQL Server. If it were me, not only would I get the MAX(ID), but I'd do a record count to verify whether the count = the MAX(ID). In my systems, they usually don't. (And that's the main reason I asked how you knew you were missing records).
Back to more questions.
1) You pull records from the source db that are greater than the MAX(ID) into the secondary database. Was your secondary db was created from an older backup and that the linked server points to a production (or more frequently updated) db?
2) Are you using the NOLOCK hint in any part of your query from the secondary database?
3a) Please review your linked server and tell me what security options are set on it. Then, if mapping to a specific login, go to the source database and tell me all permissions, denies, database mappings, and roles that particular login uses. Is it a SQL Server only login or a Windows / AD login.
3b) I know you said sysadmin, but I want to know if there's anything else set up. If this login is a Windows login, have you checked denies set up on the group this login belongs to? Do you have DDL triggers that might be prevented the user from getting all the data?
March 17, 2011 at 7:05 am
2) Production database or dev database?
Yes I can use test database for testing.
1-2) You pull records from the source db that are greater than the MAX(ID) into the secondary database. Was your secondary db was created from an older backup and that the linked server points to a production (or more frequently updated) db?
Secondary db was not created from older backup.
Yes linked server points to db whcih updated frequently, average 3 records each minute.
2-2) Are you using the NOLOCK hint in any part of your query from the secondary database?
I am not uinsg NOLOCK at any point in my query but using SET NOCOUNT ON;
3a) Please review your linked server and tell me what security options are set on it. Then, if mapping to a specific login, go to the source database and tell me all permissions, denies, database mappings, and roles that particular login uses. Is it a SQL Server only login or a Windows / AD login.
Linked server security options:
Be made using the securing context
Remote Login
With Passowrd
sa is the user
Server Roles: public and sysadmin
User Mappings: sa is not mapped with db I query.
Please tell me if you need more details or if I could not make it clear. I will try to elaborate it.
Thanks for your help
March 17, 2011 at 11:54 am
kashif.nadeem (3/17/2011)
3a) Please review your linked server and tell me what security options are set on it. Then, if mapping to a specific login, go to the source database and tell me all permissions, denies, database mappings, and roles that particular login uses. Is it a SQL Server only login or a Windows / AD login.
Linked server security options:
Be made using the securing context
Remote Login
With Passowrd
sa is the user
Server Roles: public and sysadmin
User Mappings: sa is not mapped with db I query.
Try this. Change out the SA account for a plain vanilla, no server role user account. Give that plain user account db_datareader and, if necessary, db_datawriter access to the database in question. Then try your queries again. Let me know how that works.
Avoid using SA for linked servers whenever possible. That account gives the linked server far too many permissions and can kill your server if the wrong person gets access to the connection.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply