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... |
|
|
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 |
|
|
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... |
|
|
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 |
|
|
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 instanceHTH--------------------keeping it simple... |
|
|
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 justrs.update and the exact same file works if i connect to a SQL2000-server insteadthis 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 |
|
|
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... |
|
|
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... |
|
|
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 tablescan you verify if the connection string you used before issuing the rs.update is still active?--------------------keeping it simple... |
|
|
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 |
|
|
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 |
|
|
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=4564598Kristen |
|
|
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 usingrs.addnew...listing fieldsrs.updateI 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 |
|
|
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 Kizeraka tduggan |
|
|
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 |
|
|
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
|
|
|
kin2189
Starting Member
2 Posts |
Posted - 2007-01-08 : 05:54:45
|
here is the solutionhttp://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=274270&SiteID=1 |
|
|
|