Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Updating Views with several underlying tables (Read 4687 times)
bonzie
YaBB Newbies
*


I Love SQL*XL

Posts: 5
Updating Views with several underlying tables
20.02.08 at 18:40:57
 
I'm getting  the following message when updating a view with 2 tables - one being an alias of the original table.  
 
Row cannot be located for updating.  Some of the values may have changed since it was last read.
 
Please requery and try the updates again.
 
We are using this view to compare columns of records in the same table that have matching id's then update the values of one to the other (the primary key is not the column we are matching on).  We are only updating one underlying table at a time and the view updates work when i use SQL2005 query window to edit the view.
 
We currently are using the folllowing DB connection string
 
SQLXL.Database.Connect UserName:=loginid, Password:=Password, DBAlias:="ForecastDB", ConnectionString:="Provider=SQLOLEDB.1;Password=" & Password & ";User ID=" & loginid & ";Initial Catalog=ForecastDB;Data Source=vscore\sql2005;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=CHRIS-X2FDWUQT2;Use Encryption for Data=False;Tag with column collation when possible=False", AllowTransactions:=True
 
Refreshing with this does not have any effect it says the same error on update every time.
 
HOWEVER...................................
 
When I use the following connection string below the updates work only after I load the table a first time then reload the table again.  Then updates work.  Only problem is when we use this connection string the reload takes forever about 5X to 10X slower load time than the first time loading the table and all other table loads after that take forever not matter what I'm loading.
 
The SQL server is registering thousands of  
 
exec sp_cursorfetch 180150003,2,0,1 calls during the time excel is waiting for data.
 
 
SQLXL.Database.Connect UserName:=loginid, Password:=Password, DBAlias:="ForecastDB", ConnectionString:="Provider=MSDASQL.1;Password=" & Password & ";User ID=" & loginid & ";Extended Properties=""DRIVER=SQL Server;SERVER=VSCORE\SQL2005;UID=" & loginid & ";PWD=" & Password & ";APP=2007 Microsoft Office system;WSID=CHRIS-X2FDWUQT2;DATABASE=ForecastDB;Network=DBMSSOCN""", AllowTransactions:=True
 
Any idea how I can get this to work correctly?  We use the first connection string currently because of the load time problems we are experiencing with the other connection string.  Load times are not a problem at any time with the first connection string and most of my view updates work, but not this one and the only real difference is that this view is two of the same table linked by a id column that is not the primary key.
Back to top
 
 
Email   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Updating Views with several underlying tables
Reply #1 - 21.02.08 at 07:51:01
 
Hi,
 
Thank you for posting your question with soo much detail.  
 
Are you updating col1 or col2 in a view like this?
select t1.col1, t2.col2 from mytable t1, mytable t2  
where t1.id = t2.id
 
You say that you see thousands of calls to exec sp_cursorfetch when you do a refresh. How do you see / monitor these calls? Would be interesting to see.
 
In general I can say that there are differences in drivers when doing work with databases. For the most basic stuff there is not a great deal of difference however when you move to more complicated scenarios there are certainly great differences between the drivers. Updating views is one of these things.
 
In my experience "row cannot be located for updating" means that it found more than one row to update. In the multiple rows update feature of SQL*XL I let the driver go back to the record to update. It clearly didn't manage to do that. I must say I tend to not have this  problem with Oracle databases as I think Oracle's rowid is stored within the datasets returned by the driver and on the rowid the driver obtains a link back to the original row. With SQL Server and Access I have seen the error you report and usually adding a primary key solves the problem. I know that this is not a solution you can always use and possibly you can't either.
 
So switching driver can sometimes be a good idea. That leaves the question why reloading the updated data is so slow. I have no explanation for that. From the perspective of SQL*XL there is no difference between running a query a second time after an update or without having made any updates. Perhaps it is something that has to do with read consistency in the database or in the driver. When you applied your changes, did you commit the changes? When others query the same tables they will see the old, unchanged data until you commit. Perhaps in this - keeping your changes separate from what others can see - is where the problem occurs. Committing your changes will clear this as everybody will see again the same version of the table.
 
Can you try to press the commit button (the green commit button on the toolbar) after you have made your updates?
 
What happens when you query your view, not make any updates and then query the view again. Is that second query slower as well?
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
bonzie
YaBB Newbies
*


I Love SQL*XL

Posts: 5
Re: Updating Views with several underlying tables
Reply #2 - 21.02.08 at 19:49:26
 
I'm updating col1 in your example.  The values in col2 are for reference.  Other views setup like this work, I think it has someting to do with the fact in this case the two tables are the same table.  My other views that do updates work well.
 
I monitor the SQL server using SQL Server Profiler and can see what is going on realtime.
 
I'm not sure what you mean by adding a primary key?  do you mean to the view?  All of the underlying tables that comprise the view have primary keys.
 
I think the slowness has to do with all the sp_cursorfetch calls the DB is making.  Probably someting in that driver.  It does not happen if I use ODBC or SQL Native, but both these modes do not let me make updates.
 
Comitting changes does not seem to do anything.  Our updates are defaulted to commit immediatly anyway.
 
"What happens when you query your view, not make any updates and then query the view again. Is that second query slower as well?"
 
Any time after the first query run it is drastically slower even if i call a differnet query.  Again it is only with the MSDASQL provider that is does this.
Back to top
 
 
Email   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Updating Views with several underlying tables
Reply #3 - 21.02.08 at 20:54:52
 
Thank you for providing yet more valuable feedback. At the end you are saying something that makes me think whether it has to do anything with updates at all.
 
Clearly the SQLOLEDB provider (OLE DB driver) cannot handle the updates you talk about. The MSDASQL provider (ODBC driver) can handle the updates. This is a good thing to clearly conclude.  
 
You say at the end of your message: Quote:
"Any time after the first query run it is drastically slower even if i call a differnet query. Again it is only with the MSDASQL provider that is does this."

 
So with the ODBC driver (MSDASQL provider) any second query is slower than the first you perform. Perhaps we should time the execution of a query like this:
select top 1000 from mytable
 
You claim that the first time it runs faster than the second time. It would be interesting to confirm that this is indeed the case.
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1