July 22, 2007 at 11:22 am
I have a ASP.NET that is using a SQL Server 2000 database. Occasionally, I will receive an error from the application saying that it cannot find a column from the results returned from a stored procedure. The thing is is that when I run the stored procedure in the query analyzer or even refresh the page everything seems to be working fine. When I go to enterprise manager and look at current activity, after refreshing of course, I noticed that at any one time there could be three or four database locks. These queries should not be locking the database at all. Enterprise manager says that it is a database lock type, S mode, GRANT status, and Sess for the Owner. Occasionally, I will see a particular process ID show up a hundred times with a lock on a particular tables index. Any ideas?
Jim
July 22, 2007 at 11:34 am
July 22, 2007 at 11:49 am
CREATE PROCEDURE [dbo].[GetWFSAInfo]
@WFSAID int
AS
SELECT WFSA.*, FirstName, LastName, EmailAddress, LoginName FROM WFSA, WFSAUsers
WHERE WFSAID=@WFSAID AND WFSA.CreatorID = WFSAUsers.UserID
GO
I personally do not like the FROM clause. I was always taught to use standard join syntax (INNER JOIN, OUTER JOIN, etc.), but this database is riddled with this kind of syntax. Also, the previous developer used a lot of CASE... END CASE statements within the SELECT clause but I also find to be very difficult. I was always taught that the database should do what the database was designed for selecting, inserting, updating, and deleting of records all other computations and reformatting of data for display purposes should be done within the application.
Lastly, my application tends to also produce an error saying that the connection needs to be closed before it can be opened. In my finally statements I am explicitly closing all of my SQL connections, so if the method bombs the connection will always be closed. Consequently, this tends to be a little frustrating to say the least.
Thanks for your help,
Jim
July 22, 2007 at 12:02 pm
you can try to use table hints in your select statement,i.e.
CREATE PROCEDURE [dbo].[GetWFSAInfo]
@WFSAID int
AS
SELECT WFSA.*, FirstName, LastName, EmailAddress, LoginName FROM WFSA, WFSAUsers WITH (NOLOCK)
WHERE WFSAID=@WFSAID AND WFSA.CreatorID = WFSAUsers.UserID
>Lastly, my application tends to also produce an error saying that the connection needs to be closed before it can be opened
At which stage in your application code do you get this error?
July 22, 2007 at 12:15 pm
This usually happens when the user first comes to the application, so it is when the application needs to get data. When I went through and audited the code, I found that the previous developer does a lot of computations and data formatting in the database. Is this common, or a best practice? We have numerous occasions where we are displaying data to the user in a table format that the user makes their edits in, then when the user wants to commit these changes the application loops through, and in some cases has nested loops, that fire off a method that updates a table row for each individual value no matter whether it has changed or not. Is there a way that I can do a batch like update so we are only opening the connection once, updating the data, then closing the connection?
July 22, 2007 at 12:50 pm
July 22, 2007 at 2:17 pm
I would get rid of the SELECT *.
I've had problems when the underlying table structure changed but the stored proc did not pick up the changes.
I would also switch to using ANSI standard joins rather than the old method of putting everything in the WHERE clause.
July 23, 2007 at 11:58 am
Jim,
There may be nothing wrong with your database or any of its objects. Instead, it may be that the application code is real buggy.
Your comment that the app throws an error saying that "the connection needs to be closed before it can be opened" indicates poor exception handling at the least. You may want to do a general code review of this.
Secondly, it's possible that under certain circumstances, the application code takes a path that contains some outdated code, hence the errors about not finding certain columns. For example, at one time the stored procedure and/or underlying tables may have contained additional columns. When the columns were removed, for whatever reason, all of the application code was not adjusted accordingly.
Regards,
Rubes
July 23, 2007 at 6:36 pm
Because object names are not qualified with owner names you procedure is recompiled every time it's been called.
Because you've got WFSA.* optimiser needs to read database schema to retrieve list of columns for that table and build execution plan.
That's why for the time of SP recompilation system tables are locked and you can see it as database lock.
If you've got many users trying to run it simultaneously some of them can get error in SP compilation and see "missing columns".
If performance of the system is important then lazy coding is not acceptable approach.
_____________
Code for TallyGenerator
July 25, 2007 at 8:03 pm
Thank you so much for confirming what I already had thought. This thing is riddled with lazy coding, no definite user workflow or progression, documentation, and the list goes on and on...
Thanks again,
Jim
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply