Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Old Forums
 CLOSED - SQL Server 2005/Yukon
 Cannot update posts in MS SQL 2005

Author  Topic 

julian77
Starting Member

8 Posts

Posted - 2006-05-06 : 10:51:51
Hi,

I have recently migrated from SQL 2000 to SQL 2005 and certain .asp-code that was working before, is no longer working.

Retrieving, inserting, deleting recordsets are no problem, but when I try to update I run into problems.

The following code works:

[CODE]"UPDATE tblsomething SET column_name = 'something' "[/CODE]

But in all my pages I have been using:

[CODE]rs.Open "SELECT * FROM tblSomething WHERE column_name=" & RequestSomething, connection, 1,3[/CODE]

and then I'll list the columns

[CODE]rs.Fields("column_name") = Request.Form("form_name")
-
-
rs.Update[/CODE]

And this approach does not work. Since I am primarily a designer with programming knowledge on a need-to-know-basis, I don't know if something changed between the MS SQL versions? And I am not so keen on changing my code to the working solution in all those pages...

Since the error message is in Swedish I can't just post it but is says something about not finding the sql server in sysservers and tells me to run sp_addlinkedserver to add servers into sysservers?

Does this make any sence to anyone?

Any help appreciated.

//Jesper

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-05-07 : 02:06:10
is the sql server name you specified in your connection string the same with the new server name?



--------------------
keeping it simple...
Go to Top of Page

julian77
Starting Member

8 Posts

Posted - 2006-05-07 : 18:11:25
Jen,

thanks for your reply.

the connection is not the problem. i can view, edit and insert recordsets from the database on MS SQL 2005. But when it comes to updating the code used before, which is currently working on SQL 2000, I can't unless i rewrite the code as in my previous post.

I wonder if anyone else has this problem, if there are any known changes between the SQL versions, or if there might be a problem with my hosting company?

This is the first time I use SQL 2005.

/jesper
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-05-08 : 22:08:01
Hi Jesper,

where are you doing these...
quote:
Originally posted by julian77


i can view, edit and insert recordsets from the database on MS SQL 2005.



are you able to establish connection from your application? can you verify?

--------------------
keeping it simple...
Go to Top of Page

julian77
Starting Member

8 Posts

Posted - 2006-05-09 : 06:24:23
Hi,

I have my own "content management system" and there are a lot of different pages connecting to the database. When I changed hosting company they offered me SQL 2005-server. I changed the connection string, checked that it was working, and it wasnt until later when some post needed update, that I ran into this problem.

So 95% of the pages are working flawlessly, just the updating that I wish to find a cure for.

/jesper
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-05-09 : 08:27:46
are you using a global connection, same conn for the other transactions including update?
can you verify if update is all you are doing that would have invoked the message you just posted?

the message implies that you are trying to access another server from where you are connected, like from local to named instance

HTH




--------------------
keeping it simple...
Go to Top of Page

julian77
Starting Member

8 Posts

Posted - 2006-05-09 : 10:59:13
yes, i connect globally and the only database work I do in this specific file is to (try and) update. The error message referrs to the line with just

rs.update


and the exact same file works if i connect to a SQL2000-server instead

this is so strange. i posted this question in a number of forums but no solutions (hardly even no replies) This makes me wonder -

doesn't anybody use SQL 2005?
can others successfully use the update code I showed with sql 2005?

/jesper
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-05-09 : 11:08:18
any update trigger on the said table that may be connecting to another server?

have you tried on other tables if this is the case?



--------------------
keeping it simple...
Go to Top of Page

julian77
Starting Member

8 Posts

Posted - 2006-05-11 : 16:33:18
hi,

no there are no other triggers, and use only this server.
i have also tried to update other tables, but anywhere I use this type of way to update recordsets, i receive an error.

the error, probably not flawlessly translated from Swedish says...

Microsoft OLE DB Provider for ODBC Drivers error '80004005' 

[Microsoft][ODBC SQL Server Driver][SQL Server]The server S19-AXENTUS (whis is the server I use) could not be found in sysservers. Run sp_addlinkedserver to add servers to sysservers.


I guess sp_addlinkedserver is a stored procedure, which I dont use.
And I use just one server.

Weird...
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-05-12 : 11:15:18
in sql, an update is a combination of delete and insert, so it doesn't make sense if you are unable to do an update unless you don't have permissions on the underlying tables

can you verify if the connection string you used before issuing the rs.update is still active?

--------------------
keeping it simple...
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-05-12 : 12:29:47
Is S19-AXENTUS the old sever you used, or the new SQL 2005 one?

If the old one does "S19-AXENTUS" exist anywhere in your code?

Could it be that there is a permissions error on a table, and you are just seeing a duff error message (implying that the SERVER is not visible, rather than, say, some TABLE)??

Kristen
Go to Top of Page

julian77
Starting Member

8 Posts

Posted - 2006-05-12 : 14:32:55
Jen, Kristen, thanks for your replies.

I have a global connection string, which works perfectly to insert and view recordsets. It´s just the update thing that doesn´t work. As I said before, if I change it to "UPDATE tblsomething SET column_name = 'something' " it works fine, but listing the fields I want to change by writing rs.Fields("column_name") and then rs.Update, it just doesn´t work.

s19-Axentus i am not sure what it refers to. I connect to the server by ip-number. But the details I received from the hosting company gave me both an ip-number and a server name s19.myaxentus.se (this is my new server, sql 2005), which I also tried to connect to with the same unsatisfactoy result.

The basic question remains: Can anybody else connect to MS SQL 2005 and update recordsets with "my code", i.e. listing rs.fields and the rs.update?

since I can update with the code written above, there should not be a permission problem, right?

thanks,

jesper
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-05-12 : 14:43:39
"since I can update with the code written above, there should not be a permission problem, right?"

Apart from the fact that when things go wrong I never assume anything!, I do agree that the permissions seem to be OK.

The following turned up in a quick Google search, and might be relevant:

http://www.developersdex.com/sql/message.asp?p=580&r=4564598

Kristen
Go to Top of Page

julian77
Starting Member

8 Posts

Posted - 2006-05-12 : 15:08:47
True, never assume anything is working. I learned that many times...

Thanks for the link. That guy had the same problem almost, unfortunately no solution.

He was using

rs.addnew
...listing fields
rs.update

I have also used this approach historically, partly because of old habit (copy & paste), and also because I find this approach easy to edit; if I dont want to add a new post and just update, I remove the rs.addnew-line. This works perfectly with Access and SQL 2000. But not SQL 2005.

The guy from the link you posted, also "found a solution" by changing his code to the other approach, which I listed before.

Thing is, I would like to know if my way of doing it, for some stupid reason, is no more compatible with SQL 2005.
I owuld also prefer not to having to change my previous coding, since its not just one page for one application, but one I have used in numerous places for numerous clients (that might also want to upgrade to sql 2005 at one point)..

/jesper
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-12 : 15:23:52
Jesper,

Your hosting company needs to run sp_addlinkedserver to fix this issue. The error message that you posted indicates that they recently changed the server name and did not complete the process. They can read up on this in SQL Server Books Online.

Tara Kizer
aka tduggan
Go to Top of Page

julian77
Starting Member

8 Posts

Posted - 2006-05-12 : 16:19:48
ok, I will forward this message to them.

Thanks for your interest,

Jesper
Go to Top of Page

kin2189
Starting Member

2 Posts

Posted - 2007-01-07 : 23:20:22
I have the same problem as yours. Do you have any solution. Thanks!!
quote:
Originally posted by julian77

ok, I will forward this message to them.

Thanks for your interest,

Jesper

Go to Top of Page

kin2189
Starting Member

2 Posts

Posted - 2007-01-08 : 05:54:45
here is the solution

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=274270&SiteID=1
Go to Top of Page
   

- Advertisement -