SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - SQL Server 2005/Yukon
 Cannot update posts in MS SQL 2005
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

julian77
Starting Member

8 Posts

Posted - 05/06/2006 :  10:51:51  Show Profile
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:

"UPDATE tblsomething SET column_name = 'something' "


But in all my pages I have been using:

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


and then I'll list the columns

rs.Fields("column_name") = Request.Form("form_name")
-
-
rs.Update


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
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 05/07/2006 :  02:06:10  Show Profile  Send jen a Yahoo! Message
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 - 05/07/2006 :  18:11:25  Show Profile
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

Edited by - julian77 on 05/07/2006 18:12:26
Go to Top of Page

jen
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 05/08/2006 :  22:08:01  Show Profile  Send jen a Yahoo! Message
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 - 05/09/2006 :  06:24:23  Show Profile
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
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 05/09/2006 :  08:27:46  Show Profile  Send jen a Yahoo! Message
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 - 05/09/2006 :  10:59:13  Show Profile
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
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 05/09/2006 :  11:08:18  Show Profile  Send jen a Yahoo! Message
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 - 05/11/2006 :  16:33:18  Show Profile
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
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 05/12/2006 :  11:15:18  Show Profile  Send jen a Yahoo! Message
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

United Kingdom
22403 Posts

Posted - 05/12/2006 :  12:29:47  Show Profile
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 - 05/12/2006 :  14:32:55  Show Profile
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

United Kingdom
22403 Posts

Posted - 05/12/2006 :  14:43:39  Show Profile
"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 - 05/12/2006 :  15:08:47  Show Profile
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

USA
36565 Posts

Posted - 05/12/2006 :  15:23:52  Show Profile  Visit tkizer's Homepage
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 - 05/12/2006 :  16:19:48  Show Profile
ok, I will forward this message to them.

Thanks for your interest,

Jesper
Go to Top of Page

kin2189
Starting Member

2 Posts

Posted - 01/07/2007 :  23:20:22  Show Profile
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 - 01/08/2007 :  05:54:45  Show Profile
here is the solution

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=274270&SiteID=1
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000