January 28, 2009 at 1:43 pm
I am using SQL server 2005. I have a Stored Procedure that dynamically creates a table with a table name also generated dynamically at run time. When I am executing the stored procedure it hangs at this point where I am checking the existence of this dynamically generated table name already in the database.
If object_id(@lv_tablename) IS NOT NULL
Begin
Execute ('Drop table' + @lv_tablename)
END
What could be the reason for this, also any solutions. If its a lock on this table may be already created then How should I write the code to handle this lock.
January 28, 2009 at 1:50 pm
Execute doesn't work that way.
What you'd need to do would be more like this:
declare @SQL varchar(1000)
If object_id(@lv_tablename, 'U') IS NOT NULL
Begin
select @SQL = 'Drop table ' + @lv_tablename
Execute (@SQL)
END
I added the object type to make sure that the drop command won't be issued against something that isn't a table. What if the object_id isn't null because there's a view by that name? (Might want to add some error handling based on object_id(@lv_tablename,'U') is null but object_id(@lv_tablename) is not null.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 28, 2009 at 1:51 pm
I just noticed what forum this appears to be in, and I'm curious about why it's posted here?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 28, 2009 at 1:52 pm
Why don't you use temp tables? Then there's no worries about locking (only your process has access) and you don't need to worry about dynamic names.
In the future, please post in an appropriate forum, not one of the article discussion forums. (I have requested the moderator to move it)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 28, 2009 at 2:04 pm
Ok, Accepted but my main problem was not related to the table name or view name. I just wrote an example over here.
My problem is that the stored procedure containing the similar functionality is getting hanged at this check. There may be times when a process is killed in between and the table created is locked.
I want to handle that situation through code in SP itself. So that even if there is a lock then my code should be able to find it and report that the table already exists and is locked by some other user.
Is it possible?
January 28, 2009 at 2:13 pm
Sorry for the wrong forum. I am using forums for helps for the first time. Will keep this in mind from future.
Thanks for bearing with me.
January 28, 2009 at 9:33 pm
Not sure if I'm splitting hairs here, but you don't have a space after the DROP TABLE.
If object_id(@lv_tablename) IS NOT NULL
Begin
Execute ('Drop table' + @lv_tablename)
END
That satement will render as DROP TABLEsomething and will cause a syntax error (unless you're padding the table name, in which case the object_id lookup would have failed and you may be trying to create a table that already exists)).
This is why you always want to print your sql statements first.
You may want to wrap the tablename in brackets as well.
January 29, 2009 at 8:32 am
The reason for it to lock and hang is if some other process is accessing that table. It can't drop it while something else is accessing it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply