I have just been given a new SQL Server 2000 box to look after in production. I just tried to run a standard t-sql script I use for setting up backup jobs and so on. However, it failed with a long list of errors - quite a surprise at first since I have run the same script on many other servers wihtout a hitch. On close examination, the problem appears to be that the new serer is setup with a server default collation... Latin1_General_BIN (I think a binary based collation makes this a case sensitive server).
This is quite an urgenet one since I have to get this wrapped up today. I don't think I can change the server's default collation without a lot of red-tape. Is there a quick way to run my scripts in a 'case insenstive' context within Query Analyzer? If so, how?
Thanks in advance,
CliveWe have that problem all of the time with the PeopleSoft servers. They can only run with binary sort orders.
If you find a way to work around the case sensitivity, please let me know!
-PatP|||Ok, well thanks anyway. It seems a bit over the top for a server to be set to case senstive by default. Even the master database is case sensitive as a result. I can't see why the s/w vendor couldn't have left the default server collation alone and just been a bit more selective about what tables/columns actually needed a _BIN collation. Laziness I suppose.
Clive|||Binary sort order os a bit faster than case sensitive, as it bypasses all of the checks for 'A' = 'a'. That aside, you should probably go over the script and change the identifiers (table names, column names) to be the proper case (fortunately this is all lowercase for system tables), and change all of your variables to be the same case throughout. The variables can be doe with find/replace very easily. After that, you would have a new script that would work on Latin1_General_CI_AS, _BIN, and _CS_AS servers.
Since i have a couple of case sensitive servers around, I have had to write all of my own scripts to be able to handle case sensitivity. Even the ones that "should" only run on the case insensitive ones. It is a good habit to get into.|||All of our main production servers are Latin1_General_BIN_437 which is actually what yours probably is. This was recommended back in the old days when it was based on 6.5. Since they reworked the architecture, It doesn't really make any difference on speed. Since it's not the industry standard to use this, it's just a big pain. Anyway, I've learned to use all upper-case for my commands and lower-case for my objects. We have a program at work that will automagically convert all your code for you. I'll see if I can "share" it with you. Saves soooooooo much time.
Showing posts with label script. Show all posts
Showing posts with label script. Show all posts
Sunday, March 25, 2012
Monday, March 19, 2012
CASE and INSERT statements
Hi,
I need to write a simple script which i am getting rather
on how to
write. Basically, what i need to do is:
If no records exist for a particular condition i.e. TableID=3 and
TableTypeID=4, then insert a row into Tabel1.
I have tried all sorts but I think i have got the order mixed up and that’
s
why it is not working.
This is what I have tried so far:
select * ,
case
when not exists (select * from Table1 where TableID=3 and TableTypeID=14)
then insert into Table1 (TableID, TableTypeID) values (3,14)
end
from Table1
I have tried the above in various different formats but with no success.
Any help/advice much appreciated.
Thanks,
JJens
> If no records exist for a particular condition i.e. TableID=3 and
> TableTypeID=4, then insert a row into Tabel1.
IF NOT EXISTS (SELECT * FROM Table WHERE TableID=3 and TableTypeID=4)
INSERT INTO Table balblabala alaba
ELSE
Do it something else here
"JenC" <JenC@.discussions.microsoft.com> wrote in message
news:57ED03B3-B064-4793-B1AC-C5F4E891EA5E@.microsoft.com...
> Hi,
> I need to write a simple script which i am getting rather on how
> to
> write. Basically, what i need to do is:
> If no records exist for a particular condition i.e. TableID=3 and
> TableTypeID=4, then insert a row into Tabel1.
> I have tried all sorts but I think i have got the order mixed up and that
s
> why it is not working.
> This is what I have tried so far:
> select * ,
> case
> when not exists (select * from Table1 where TableID=3 and TableTypeID=14)
> then insert into Table1 (TableID, TableTypeID) values (3,14)
> end
> from Table1
> I have tried the above in various different formats but with no success.
> Any help/advice much appreciated.
> Thanks,
> J
>|||
This will never evaluate to true
-TableId can=B4t be 3 AND 14 at the same time
select * from Table1 where TableID=3D3 and TableTypeID=3D14
For an inline Query (with an OR rather than the AND which is causing
the above issue)
insert into Table1
(
TableID,
TableTypeID
)
SELECT 3,14
WHERE NOT EXISTS
(
select * from Table1 where TableID=3D3
OR TableTypeID=3D14
)=20
HTH, Jens SUessmeyer.
I need to write a simple script which i am getting rather
on how towrite. Basically, what i need to do is:
If no records exist for a particular condition i.e. TableID=3 and
TableTypeID=4, then insert a row into Tabel1.
I have tried all sorts but I think i have got the order mixed up and that’
s
why it is not working.
This is what I have tried so far:
select * ,
case
when not exists (select * from Table1 where TableID=3 and TableTypeID=14)
then insert into Table1 (TableID, TableTypeID) values (3,14)
end
from Table1
I have tried the above in various different formats but with no success.
Any help/advice much appreciated.
Thanks,
JJens
> If no records exist for a particular condition i.e. TableID=3 and
> TableTypeID=4, then insert a row into Tabel1.
IF NOT EXISTS (SELECT * FROM Table WHERE TableID=3 and TableTypeID=4)
INSERT INTO Table balblabala alaba
ELSE
Do it something else here
"JenC" <JenC@.discussions.microsoft.com> wrote in message
news:57ED03B3-B064-4793-B1AC-C5F4E891EA5E@.microsoft.com...
> Hi,
> I need to write a simple script which i am getting rather
on how> to
> write. Basically, what i need to do is:
> If no records exist for a particular condition i.e. TableID=3 and
> TableTypeID=4, then insert a row into Tabel1.
> I have tried all sorts but I think i have got the order mixed up and that
s
> why it is not working.
> This is what I have tried so far:
> select * ,
> case
> when not exists (select * from Table1 where TableID=3 and TableTypeID=14)
> then insert into Table1 (TableID, TableTypeID) values (3,14)
> end
> from Table1
> I have tried the above in various different formats but with no success.
> Any help/advice much appreciated.
> Thanks,
> J
>|||
This will never evaluate to true
-TableId can=B4t be 3 AND 14 at the same time
select * from Table1 where TableID=3D3 and TableTypeID=3D14
For an inline Query (with an OR rather than the AND which is causing
the above issue)
insert into Table1
(
TableID,
TableTypeID
)
SELECT 3,14
WHERE NOT EXISTS
(
select * from Table1 where TableID=3D3
OR TableTypeID=3D14
)=20
HTH, Jens SUessmeyer.
Sunday, February 12, 2012
Can''t use a Dataset in my script component...reference required...whats going on?
Hi
Finding this forum really useful...I wonder if you could help me with this.
I've got a very simple script component and I just want to use a Dataset in it.
When I declare the Dataset i get a warning and then consequently an error. herers an image grab of whats happening:
http://www5.webng.com/hopelist/error.jpg
If you can't see the image please let me know.
Essentially VS gives me a hint to add a required assembly which it needs....but when I click to add I get a 'Visaul Basic Compiler has encountered a problem and needs to close' type error.
Anyone got any idea whats going on and why I'm having such a hard time just accessing a Dataset?
thanks
Andy
Well, you do need to add a reference to the System.XML assembly, because datasets have a reference to it. Not sure why the environment is crashing, though. If you are using the add option on the error dialog, you might try adding the reference by using the project explorer, and going to the references folder, or Project..Add Reference from the menu bar.
Can't update linked server table
Dear Sir,
I have a script which update a linked server table.
e.g.
Update <LinkedServer>.DB1.Tbl1
Set col1 = B.col1
From <LinkedServer>.DB1.Tbl1 A
inner join <local>.DB0.Tbl1 B
On A.id = B.id
It function for several month and suddently I started to recevive error:
Server: Msg 7306, Level 16, State 2, Line 1
Could not open table '"ABC_Sale_Rpt"."dbo"."Sale_Data"' 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.
[OLE/DB provider returned message: Multiple-step OLE DB operation genera
ted
errors. Check each OLE DB status value, if available. No work was done.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset
returned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True
STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT VALUE=60
0
STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID VALUE=True
STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate VALUE=True
STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_IRowsetChange VA...
Any advise to make it function again?
HenryMost times If saw this error it was based on the absence of a unique
key (e.g. primary key). Examine the table if such a key exsist. if not
create on, that SQL Server is able to look up the row that currently
should be updated.
HTH, Jens Suessmeyer.|||Did you tryed to use OPENQUERY():
http://msdn.microsoft.com/library/d...br />
5xix.asp
"Henry" wrote:
> Dear Sir,
> I have a script which update a linked server table.
> e.g.
> Update <LinkedServer>.DB1.Tbl1
> Set col1 = B.col1
> From <LinkedServer>.DB1.Tbl1 A
> inner join <local>.DB0.Tbl1 B
> On A.id = B.id
> It function for several month and suddently I started to recevive error:
> Server: Msg 7306, Level 16, State 2, Line 1
> Could not open table '"ABC_Sale_Rpt"."dbo"."Sale_Data"' from OLE DB provid
er
> 'SQLOLEDB'. The provider could not support a row lookup position. The
> provider indicates that conflicts occurred with other properties or
> requirements.
> [OLE/DB provider returned message: Multiple-step OLE DB operation gene
rated
> errors. Check each OLE DB status value, if available. No work was done.]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset
> returned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True
> STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT VALUE=
600
> STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID VALUE=True
> STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate VALUE=True
> STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_IRowsetChange VA...
> Any advise to make it function again?
> Henry
>
I have a script which update a linked server table.
e.g.
Update <LinkedServer>.DB1.Tbl1
Set col1 = B.col1
From <LinkedServer>.DB1.Tbl1 A
inner join <local>.DB0.Tbl1 B
On A.id = B.id
It function for several month and suddently I started to recevive error:
Server: Msg 7306, Level 16, State 2, Line 1
Could not open table '"ABC_Sale_Rpt"."dbo"."Sale_Data"' 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.
[OLE/DB provider returned message: Multiple-step OLE DB operation genera
ted
errors. Check each OLE DB status value, if available. No work was done.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset
returned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True
STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT VALUE=60
0
STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID VALUE=True
STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate VALUE=True
STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_IRowsetChange VA...
Any advise to make it function again?
HenryMost times If saw this error it was based on the absence of a unique
key (e.g. primary key). Examine the table if such a key exsist. if not
create on, that SQL Server is able to look up the row that currently
should be updated.
HTH, Jens Suessmeyer.|||Did you tryed to use OPENQUERY():
http://msdn.microsoft.com/library/d...br />
5xix.asp
"Henry" wrote:
> Dear Sir,
> I have a script which update a linked server table.
> e.g.
> Update <LinkedServer>.DB1.Tbl1
> Set col1 = B.col1
> From <LinkedServer>.DB1.Tbl1 A
> inner join <local>.DB0.Tbl1 B
> On A.id = B.id
> It function for several month and suddently I started to recevive error:
> Server: Msg 7306, Level 16, State 2, Line 1
> Could not open table '"ABC_Sale_Rpt"."dbo"."Sale_Data"' from OLE DB provid
er
> 'SQLOLEDB'. The provider could not support a row lookup position. The
> provider indicates that conflicts occurred with other properties or
> requirements.
> [OLE/DB provider returned message: Multiple-step OLE DB operation gene
rated
> errors. Check each OLE DB status value, if available. No work was done.]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset
> returned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True
> STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT VALUE=
600
> STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID VALUE=True
> STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate VALUE=True
> STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_IRowsetChange VA...
> Any advise to make it function again?
> Henry
>
Can't update linked server table
Dear Sir,
I have a script which update a linked server table.
e.g.
Update <LinkedServer>.DB1.Tbl1
Set col1 = B.col1
From <LinkedServer>.DB1.Tbl1 A
inner join <local>.DB0.Tbl1 B
On A.id = B.id
It function for several month and suddently I started to recevive error:
Server: Msg 7306, Level 16, State 2, Line 1
Could not open table '"ABC_Sale_Rpt"."dbo"."Sale_Data"' 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.
[OLE/DB provider returned message: Multiple-step OLE DB operation generated
errors. Check each OLE DB status value, if available. No work was done.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset
returned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True
STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT VALUE=600
STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID VALUE=True
STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate VALUE=True
STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_IRowsetChange VA...
Any advise to make it function again?
HenryMost times If saw this error it was based on the absence of a unique
key (e.g. primary key). Examine the table if such a key exsist. if not
create on, that SQL Server is able to look up the row that currently
should be updated.
HTH, Jens Suessmeyer.|||Did you tryed to use OPENQUERY():
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_oa-oz_5xix.asp
"Henry" wrote:
> Dear Sir,
> I have a script which update a linked server table.
> e.g.
> Update <LinkedServer>.DB1.Tbl1
> Set col1 = B.col1
> From <LinkedServer>.DB1.Tbl1 A
> inner join <local>.DB0.Tbl1 B
> On A.id = B.id
> It function for several month and suddently I started to recevive error:
> Server: Msg 7306, Level 16, State 2, Line 1
> Could not open table '"ABC_Sale_Rpt"."dbo"."Sale_Data"' 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.
> [OLE/DB provider returned message: Multiple-step OLE DB operation generated
> errors. Check each OLE DB status value, if available. No work was done.]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset
> returned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True
> STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT VALUE=600
> STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID VALUE=True
> STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate VALUE=True
> STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_IRowsetChange VA...
> Any advise to make it function again?
> Henry
>
I have a script which update a linked server table.
e.g.
Update <LinkedServer>.DB1.Tbl1
Set col1 = B.col1
From <LinkedServer>.DB1.Tbl1 A
inner join <local>.DB0.Tbl1 B
On A.id = B.id
It function for several month and suddently I started to recevive error:
Server: Msg 7306, Level 16, State 2, Line 1
Could not open table '"ABC_Sale_Rpt"."dbo"."Sale_Data"' 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.
[OLE/DB provider returned message: Multiple-step OLE DB operation generated
errors. Check each OLE DB status value, if available. No work was done.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset
returned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True
STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT VALUE=600
STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID VALUE=True
STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate VALUE=True
STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_IRowsetChange VA...
Any advise to make it function again?
HenryMost times If saw this error it was based on the absence of a unique
key (e.g. primary key). Examine the table if such a key exsist. if not
create on, that SQL Server is able to look up the row that currently
should be updated.
HTH, Jens Suessmeyer.|||Did you tryed to use OPENQUERY():
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_oa-oz_5xix.asp
"Henry" wrote:
> Dear Sir,
> I have a script which update a linked server table.
> e.g.
> Update <LinkedServer>.DB1.Tbl1
> Set col1 = B.col1
> From <LinkedServer>.DB1.Tbl1 A
> inner join <local>.DB0.Tbl1 B
> On A.id = B.id
> It function for several month and suddently I started to recevive error:
> Server: Msg 7306, Level 16, State 2, Line 1
> Could not open table '"ABC_Sale_Rpt"."dbo"."Sale_Data"' 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.
> [OLE/DB provider returned message: Multiple-step OLE DB operation generated
> errors. Check each OLE DB status value, if available. No work was done.]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset
> returned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True
> STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT VALUE=600
> STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID VALUE=True
> STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate VALUE=True
> STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_IRowsetChange VA...
> Any advise to make it function again?
> Henry
>
Can't update linked server table
Dear Sir,
I have a script which update a linked server table.
e.g.
Update <LinkedServer>.DB1.Tbl1
Set col1 = B.col1
From <LinkedServer>.DB1.Tbl1 A
inner join <local>.DB0.Tbl1 B
On A.id = B.id
It function for several month and suddently I started to recevive error:
Server: Msg 7306, Level 16, State 2, Line 1
Could not open table '"ABC_Sale_Rpt"."dbo"."Sale_Data"' 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.
[OLE/DB provider returned message: Multiple-step OLE DB operation generated
errors. Check each OLE DB status value, if available. No work was done.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset
returned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True
STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT VALUE=600
STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID VALUE=True
STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate VALUE=True
STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_IRowsetChange VA...
Any advise to make it function again?
Henry
Most times If saw this error it was based on the absence of a unique
key (e.g. primary key). Examine the table if such a key exsist. if not
create on, that SQL Server is able to look up the row that currently
should be updated.
HTH, Jens Suessmeyer.
|||Did you tryed to use OPENQUERY():
http://msdn.microsoft.com/library/de...oa-oz_5xix.asp
"Henry" wrote:
> Dear Sir,
> I have a script which update a linked server table.
> e.g.
> Update <LinkedServer>.DB1.Tbl1
> Set col1 = B.col1
> From <LinkedServer>.DB1.Tbl1 A
> inner join <local>.DB0.Tbl1 B
> On A.id = B.id
> It function for several month and suddently I started to recevive error:
> Server: Msg 7306, Level 16, State 2, Line 1
> Could not open table '"ABC_Sale_Rpt"."dbo"."Sale_Data"' 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.
> [OLE/DB provider returned message: Multiple-step OLE DB operation generated
> errors. Check each OLE DB status value, if available. No work was done.]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset
> returned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True
> STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT VALUE=600
> STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID VALUE=True
> STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate VALUE=True
> STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_IRowsetChange VA...
> Any advise to make it function again?
> Henry
>
I have a script which update a linked server table.
e.g.
Update <LinkedServer>.DB1.Tbl1
Set col1 = B.col1
From <LinkedServer>.DB1.Tbl1 A
inner join <local>.DB0.Tbl1 B
On A.id = B.id
It function for several month and suddently I started to recevive error:
Server: Msg 7306, Level 16, State 2, Line 1
Could not open table '"ABC_Sale_Rpt"."dbo"."Sale_Data"' 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.
[OLE/DB provider returned message: Multiple-step OLE DB operation generated
errors. Check each OLE DB status value, if available. No work was done.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset
returned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True
STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT VALUE=600
STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID VALUE=True
STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate VALUE=True
STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_IRowsetChange VA...
Any advise to make it function again?
Henry
Most times If saw this error it was based on the absence of a unique
key (e.g. primary key). Examine the table if such a key exsist. if not
create on, that SQL Server is able to look up the row that currently
should be updated.
HTH, Jens Suessmeyer.
|||Did you tryed to use OPENQUERY():
http://msdn.microsoft.com/library/de...oa-oz_5xix.asp
"Henry" wrote:
> Dear Sir,
> I have a script which update a linked server table.
> e.g.
> Update <LinkedServer>.DB1.Tbl1
> Set col1 = B.col1
> From <LinkedServer>.DB1.Tbl1 A
> inner join <local>.DB0.Tbl1 B
> On A.id = B.id
> It function for several month and suddently I started to recevive error:
> Server: Msg 7306, Level 16, State 2, Line 1
> Could not open table '"ABC_Sale_Rpt"."dbo"."Sale_Data"' 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.
> [OLE/DB provider returned message: Multiple-step OLE DB operation generated
> errors. Check each OLE DB status value, if available. No work was done.]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset
> returned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True
> STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT VALUE=600
> STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID VALUE=True
> STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate VALUE=True
> STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_IRowsetChange VA...
> Any advise to make it function again?
> Henry
>
Subscribe to:
Posts (Atom)