ADODB.Recordset Delete method causes ''Could not find server'' error

• Kuba J

  SSC Veteran

  Points: 208

  More actions

  October 10, 2006 at 5:39 am

  #174852

  Hi

  I get a strange error while calling Delete method on ADODB.Recordset.

  Microsoft OLE DB Provider for SQL Server error '80004005'

  Could not find server 'MyServerName' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

  I reproduced the behaviour in a very simple test program. It connects to fresh local test database, creates and fills a table and then tries to delete rows using ADODB.Recordset.

  I suppose the problem emerged after promoting the server to domain controller.

  Thanks in advance

  Kuba

  -------------------------------------------------------------

  Test VBScript program

  Dim connectionString, conn, rs

  connectionString = "Provider=SQLOLEDB;Data Source=(local);Initial Catalog=testb"

  Set conn = Server.CreateObject("ADODB.Connection")

  conn.Open connectionString, dbUser, dbPassword

  conn.Execute "if exists(select * from sysobjects where name = 'qqq') drop table qqq"

  conn.Execute "create table qqq (aaaaa int identity, bbbbb int)"

  conn.Execute "insert into qqq (bbbbb) values(3)"

  Set rs = Server.CreateObject("ADODB.Recordset")

  rs.LockType = adLockPessimistic

  rs.Open "select * from qqq", conn, adOpenKeyset

  While Not rs.EOF

     rs.Delete  '!!!!!  Here

     rs.MoveNext

  Wend

  rs.Close

• colin mcallister

  Valued Member

  Points: 54

  More actions

  October 11, 2006 at 3:01 am

  #665162

  This worked fine for me once I dropped the "Server." from the "Server.CreateObject".  Your vbs file should only have

  Set conn = CreateObject("ADODB.Connection")

  Set rs = CreateObject("ADODB.Recordset")

  I also changed your lock enumerator from adLockPessimistic to 2 (ie the numeric constant for that lock type)

  worked a treat after that.  The full code that worked is:

  Dim connectionString, conn, rs

  connectionString = "Provider=SQLOLEDB;Data Source=(local);Initial Catalog=testb"

  Set conn = CreateObject("ADODB.Connection")

  conn.Open connectionString, dbUser, dbPassword

  conn.Execute "if exists(select * from sysobjects where name = 'qqq') drop table qqq"

  conn.Execute "create table qqq (aaaaa int identity, bbbbb int)"

  conn.Execute "insert into qqq (bbbbb) values(3)"

  Set rs = CreateObject("ADODB.Recordset")

  rs.LockType = 3

  rs.Open "select * from qqq", conn, adOpenKeyset

  While Not rs.EOF

     rs.Delete  '!!!!!  Here

     rs.MoveNext

  Wend

  rs.Close

• Kuba J

  SSC Veteran

  Points: 208

  More actions

  October 16, 2006 at 1:49 am

  #665937

  Unfortunately it did not help. I think the program is correct. It works on most computers, except for the domain controller. It also worked on that computer before configuration changes.

• satishmega

  Grasshopper

  Points: 11

  More actions

  February 15, 2011 at 1:16 am

  #1286892

  I am also faced same problem, and i fixed in the following way:

  This problem arises if you had changed computer's name.

  execute this query in sql server:

  select @@servername as 'server name'

  This should return the currect name of your server (<server or computername>\<instance name>)

  If it returns value other than your server then execute this command:

  sp_dropserver '<OldServerName>\<InstanceName>'

  -- eg: sp_dropserver 'MyComputersOldName_OrAnyOtherServerName\SQLExpress'

  sp_addserver '<NewServerName>\<InstanceName>' , 'local'

  -- eg: sp_addserver 'MyComputersCurrentName\SQLExpress' , 'local'

  Then restart sql server, and check again ther servernames by executing

  select @@servername as 'server name'

  If it returns 'NULL' value then repeat the above commands as:

  sp_dropserver '<CurrentServerName>\<InstanceName>'

  sp_addserver '<CurrentServerName>\<InstanceName>', 'local'

  Then again restart sql server.

• Kuba J

  SSC Veteran

  Points: 208

  More actions

  February 15, 2011 at 3:58 am

  #1286946

  Thank you very much. It sounds good.

  Regards

  Kuba