Row lookup problem

Question

Row lookup problem

FreeHansje

SSChampion

Points: 11751
More actions
March 26, 2004 at 5:54 am

#152089

Hi there,
I'm trying to run the following script on a linked server:
SET XACT_ABORT on
INSERT INTO report_server_a.master.dbo.tblSpaceChecks(Drive, DriveSpace)
EXEC report_server_a.master.dbo.xp_fixeddrives
UPDATE report_server_a.master.dbo.tblSpaceChecks
SET Srvname = @@Servername
The INSERT part goes well, unless I leave the XACT_ABORT OFF; the update part generates:
Could not open table '"master"."dbo"."tblSpaceChecks"' from OLE DB provider 'SQLOLEDB'. The provider could not support a row lookup position. The provider indicates that conflicts occurred with other properties or requirements.
There is more, but not important.
If I run the script on the 'master' server there is no problem. I am not sure what is happening here. If I execute a qualified DELETE operation on the linkedserver, it goes well. I have tried adding a Identity keyfield to the table, but nodice.
I found a similar question via GOOGLE, the answer there was that the targettable should be qualified as SERVER.DB.OWNER.TABLE. Since I am already doing this it is no help to me.
Any hints? Am I missing something here?
BTW, the table I use is:
CREATE TABLE [dbo].[tblSpaceChecks] (
[Drive] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DriveSpace] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Srvname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Checkdate] [datetime] NULL
) ON [PRIMARY]
GO
Greetz,
Hans Brouwer

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

FreeHansje SSChampion Points: 11751 More actions · Answer 1

Well, I found out WHAT caused the problem and how to work around it. The systemvariable @@SERVERNAME is not accepted as input for the update. I now declare a VARCHAR variable, set it to @@SERVERNAME, and now the update is accepted. Same for the GETDATE() function, had to select this function into my own varaible, and then the update is accepted.

I am still wondering why this is. If anyone can answer this, please let me know. I really hate not understanding..

Greetz,
Hans Brouwer

gljjr SSCrazy Eights Points: 9970 More actions · Answer 2

I would guess that it is due to @@servername probably being declared as sysname (I think that relates to nvarchar(128)) behind the scenes and your field is declared as a varchar(20). I'll have to try something like this when I get some free time to see what it really does.

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.