I have a linked server from SQL 2005 to SQL 2000. Yesterday I tried to update a table on SQL 2000 from SQL 2005. The UPDATE statement looks like this:
update sql2000.dbname.dbo.tablename set db = getdate() where processid = 3
SQL errored out with: Could not create an acceptable cursor
The table I was trying to modified has 2 triggers, none of which include any type of cursor. Search online suggest that I upgrade to the latest service pack. Seeing how this is a production server, I can’t just upgrade w/o getting a lot of people involved!
I was pretty much stumped and was ready to go down a lengthy process to get this resolved since a report running Reporting Services requires that this table be updated every hour. Before going down the road often travelled, I tried one other thing:
declare @sqlcmd datetime
select @sqlcmd = getdate()
update sql2000.dbname.dbo.tablename set db = @sqlcmd where processid = 3
Believe it or not, this bloody workaround worked.