October 29, 2008 at 6:53 pm
I am attempting to drop a table (if it exists) to replace it with a fresh version as part of a t-sql script. I'm using two versions of the test - see below
VersionA
if object_id('[dbo].[SEEKER_DATA_PLUS_OPTOUT]') is not null
DROP TABLE [DBO].[SEEKER_DATA_PLUS_OPTOUT]
go
VersionB
IF EXISTS
(
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[SEEKER_DATA_PLUS_OPTOUT]')
AND type in (N'U')
)
DROP TABLE [dbo].[SEEKER_DATA_PLUS_OPTOUT]
go
Both versions report success in the messages tab when run. Nonetheless, the table survives.
When I attempt to run just the 'drop table... ' line, I get Msg3701, Level11, State5.
This is a permanent table, not temporary.
Both commands work on other tables in the database.
I can drop the table using the script generated from the DropTo script built from the SSMS gui. It is, of course, Version B above.
BTW, I am unable to define an owner for this database. It appears to accept the command or the GUI action but the owner textbox in the DatabaseProperties-Files page remains blank when revisited.
October 29, 2008 at 8:54 pm
I am confused. Does version B work or not?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 30, 2008 at 2:06 am
Both versions report success in the messages tab when run. Nonetheless, the table survives.
Both versions reported report success does not mean they executed the DROP TABLE part. IF condition returned FALSE, so they reported success without running the DROP TABLE command. You can confirm this by running the Profiler.
It looks like you have permissions issue.
October 30, 2008 at 7:31 am
Suresh B. (10/30/2008)
Both versions report success in the messages tab when run. Nonetheless, the table survives.
Both versions reported report success does not mean they executed the DROP TABLE part. IF condition returned FALSE, so they reported success without running the DROP TABLE command. You can confirm this by running the Profiler.
However, that is different from this later sentence:
I can drop the table using the script generated from the DropTo script built from the SSMS gui. It is, of course, Version B above.
which is why I am asking for resolution on Version B.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 30, 2008 at 7:41 am
what's the result of this code?
is it NULL or NOT NULL?
select object_id('[dbo].[SEEKER_DATA_PLUS_OPTOUT]')
does manually dropping the table (without the if condition) works?
DROP TABLE [DBO].[SEEKER_DATA_PLUS_OPTOUT]
If it's permission-related, I am certain you'll receive an error message
October 30, 2008 at 11:02 am
To clarify:
Version B is the script you get from the 'script to' command in the SSMS GUI.
When embedded in the t-sql script, it fails.
When run fresh from the 'script to...', it works.
My login is effectively 'sa'. I am a sys admin on the server which makes me a member of the BUILTIN/Admin user. I'm also a member of our DBA group in AD which has sysadmin role membership in the instance.
The target table is one of about two dozen which are created on the fly by scripts which pull the data from a linked server. They table drops are embedded in these scripts so they may be recreated without error. Both versions of this function have been tested successfully on other table scripts.
October 30, 2008 at 1:09 pm
Scott MacCready (10/30/2008)
To clarify:Version B is the script you get from the 'script to' command in the SSMS GUI.
When embedded in the t-sql script, it fails.
When run fresh from the 'script to...', it works.
Ah, then I think that we need to see that larger t-sql script then. Also, we will obviously need to know more about it's execution context: SQLCMD?, stored procedure?, SSMS query window?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 3, 2008 at 10:20 am
It is being run from the SSMS Query window.
The only command that precedes it is 'use database'.
I intend to eventually put the entire script into an sproc.
This works in other scripts which vary only in the target table name.
November 3, 2008 at 10:36 am
Please show us the version that works and the version that does not work. This is usually a matter of detail and we need to see the details.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 3, 2008 at 10:43 am
Though frankly, what this suggests is that the database that you are switching to with "USE [dbname]" is different from the database where you generated the script from and where you are looking to see if the table still exists.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply