Hi,
I was trying to drop a table (or alter it in any way) and the Managment Studio Express gave me an error of timeout. So I say something like DROP TABLE myTable, and it takes a while and gives me the error. Does anybody know how to unlock a table?
Thanks
I have a DDL Trigger that writes a record into a table when any DDL event happens.
It works great except when I try dropping any object. I get an error at that point and the object is not dropped. The trigger and error are listed below.
Thanks.
J
Trigger:
/****** Object: DdlTrigger [MonitorDBChanges] Script Date: 07/24/2007 11:02:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [MonitorDBChanges]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
set nocount on
declare @EventType varchar(100)
declare @SchemaName varchar(100)
declare @ObjectName varchar(100)
declare @ObjectType varchar(100)
declare @Script varchar(2000)
SELECT
@EventType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)')
,@SchemaName = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(max)')
,@ObjectName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)')
,@ObjectType = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(max)')
,@Script = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
-- Is the default schema used
if @SchemaName = ' ' select @SchemaName = default_schema_name from sys.sysusers u join sys.database_principals p
on u.uid = p.principal_id where u.name = CURRENT_USER
insert into MonitorDBChanges
select @EventType, @SchemaName, @ObjectName, @ObjectType, getdate(), SUSER_SNAME(), CURRENT_USER, ORIGINAL_LOGIN(),@Script
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [MonitorDBChanges] ON DATABASE
*******************************************************************************
Error Msg:
SELECT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods. (Microsoft SQL Server, Error: 1934)
1. We have user defined type (Sql type = bit ) with rule setting it to 0
2. New column(isVIP) was created using this user defined type
3. sp_unbindrule was used to unbind rule from new column (isVIP)
4. Alter table Client drop Column isVIP !!!! (invalid syntax )
Any ideas ?
Thanks
The error message is "cannot drop the table because it is being used for replication"
my sql version is in sql server 2012 and Im ussing merge replication.
I want to drop only one table.
This thread is no longer active. I moved issue to a new, cleaner thread named: How can I get Transactional Republishing to work?
--------------------------------------------------------------------------------------------------------------------
I've tried for more than a week to figure this one out, but have not found a solution. I'm missing some critical information that perhaps someone can share. I'm desperate and embarassed that this has kept me stuck for so long. Thanks for any assistance.
In my simplist scenario, I'm transactionally replicating a single table from database A to database B and then from database B to database C. All databases are on the same XP server (in this simplist version of the problem). This is SQL 2005 RTM. Publication A to B replicates fine. Publication B to C can't even get the snapshot to work because I get the error:
Command attempted:
drop Table "dbo"."MyReplTable"
(Transaction sequence number: 0x00004174000000E100A300000000, Command ID: 41)
Error messages:
Cannot drop the table 'dbo.MyReplTable' because it is being used for replication. (Source: MSSQLServer, Error number: 3724) Get help: http://help/3724 (fyi - there is no help here)
I have a simple example script where I've renamed real names to fake names, that is exactly what I run to simulate the problem. Obviously the script is wrong, but where? I used the wizard to create the whole script (I know that is my first mistake ;-) ). After I start the snapshot jobs, I get the error above.
I presume that the sp_addarticle option @pre_creation_cmd = N'drop' is part of the problem. I don't have row filters. Also, I want to be able to replicate schema modifications when necessary (like add a column or drop a column)
Perhaps the sp_addpublication @repl_freq = N'continuous' option in the AtoB publication prevents the BtoC publication from ever getting a chance to do anything, if it never lets go of the table.
If it helps, here is my script with hopefully helpfully renamed objects. My real world scenario has many articles, but this is a simple version - 2 publications each having 1 article (the same table).
/****** Scripting replication configuration for server MyInstance. Script Date: 3/14/2006 11:16:58 AM ******/
/****** Please Note: For security reasons, all password parameters were scripted with either NULL or an empty string. ******/
/****** Installing the server MyInstance as a Distributor. Script Date: 3/14/2006 11:16:58 AM ******/
use master
exec sp_adddistributor @distributor = N'MyInstance', @password = N''
GO
exec sp_adddistributiondb @database = N'distribution', @data_folder = N'C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLData', @data_file_size = 4, @log_folder = N'C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLData', @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @security_mode = 1
GO
use [distribution]
if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U '))
create table UIProperties(id int)
if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null)))
EXEC sp_updateextendedproperty N'SnapshotFolder', N'C:ThisFolderMY2005DBWorking
epldata', 'user', dbo, 'table', 'UIProperties'
else
EXEC sp_addextendedproperty N'SnapshotFolder', 'C:ThisFolderMY2005DBWorking
epldata', 'user', dbo, 'table', 'UIProperties'
GO
exec sp_adddistpublisher @publisher = N'MyInstance', @distribution_db = N'distribution', @security_mode = 1, @working_directory = N'C:ThisFolderMY2005DBWorking
epldata', @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'
GO
use [DatabaseA]
exec sp_replicationdboption @dbname = N'DatabaseA', @optname = N'publish', @value = N'true'
GO
-- Adding the transactional publication
use [DatabaseA]
exec sp_addpublication @publication = N'PubAtoB', @description = N'Transactional publication of database ''DatabaseA'' from Publisher ''MyInstance''.', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
GO
exec sp_addpublication_snapshot @publication = N'PubAtoB', @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 1
use [DatabaseA]
exec sp_addarticle @publication = N'PubAtoB', @article = N'MyReplTable', @source_owner = N'dbo', @source_object = N'MyReplTable', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'MyReplTable', @destination_owner = N'dbo', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_dboMyReplTable', @del_cmd = N'CALL sp_MSdel_dboMyReplTable', @upd_cmd = N'SCALL sp_MSupd_dboMyReplTable'
GO
-----------------BEGIN: Script to be run at Publisher 'MyInstance'-----------------
use [DatabaseA]
exec sp_addsubscription @publication = N'PubAtoB', @subscriber = N'MyInstance', @destination_db = N'DatabaseB', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'PubAtoB', @subscriber = N'MyInstance', @subscriber_db = N'DatabaseB', @job_login = null, @job_password = null, @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20060314, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'
GO
-----------------END: Script to be run at Publisher 'MyInstance'-----------------
use [DatabaseB]
exec sp_replicationdboption @dbname = N'DatabaseB', @optname = N'publish', @value = N'true'
GO
-- Adding the transactional publication
use [DatabaseB]
exec sp_addpublication @publication = N'PubBtoC', @description = N'Transactional publication of database ''DatabaseB'' from Publisher ''MyInstance''.', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
GO
exec sp_addpublication_snapshot @publication = N'PubBtoC', @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 1
use [DatabaseB]
exec sp_addarticle @publication = N'PubBtoC', @article = N'MyReplTable', @source_owner = N'dbo', @source_object = N'MyReplTable', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'MyReplTable', @destination_owner = N'dbo', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_dboMyReplTable', @del_cmd = N'CALL sp_MSdel_dboMyReplTable', @upd_cmd = N'SCALL sp_MSupd_dboMyReplTable'
GO
-----------------BEGIN: Script to be run at Publisher 'MyInstance'-----------------
use [DatabaseB]
exec sp_addsubscription @publication = N'PubBtoC', @subscriber = N'MyInstance', @destination_db = N'DatabaseC', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'PubBtoC', @subscriber = N'MyInstance', @subscriber_db = N'DatabaseC', @job_login = null, @job_password = null, @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20060314, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'
GO
-----------------END: Script to be run at Publisher 'MyInstance'-----------------
I have a procedure where an id is passed into the procedure. It will either be a numeric value or it will be the word "ALL." All that is taking place is a select statement for the specified user id. However, if All is passed in then I 1st need to get a list of all possible user id's and produce the results for all of the associated user ids. This is my syntax, but it keeps producing a compile error of:
Msg 3701, Level 11, State 5, Line 1
Cannot drop the table '#tbl_temp', because it does not exist or you do not have permission.And I placed a comment above the line that is throwing this error.
CREATE procedure [dbo].[TEST]
(
@id varchar(100),
@startDate datetime,
@endDate datetime
[code]...
Hi,I found this SQL in the news group to drop indexs in a table. I need ascript that will drop all indexes in all user tables of a givendatabase:DECLARE @indexName NVARCHAR(128)DECLARE @dropIndexSql NVARCHAR(4000)DECLARE tableIndexes CURSOR FORSELECT name FROM sysindexesWHERE id = OBJECT_ID(N'F_BI_Registration_Tracking_Summary')AND indid 0AND indid < 255AND INDEXPROPERTY(id, name, 'IsStatistics') = 0OPEN tableIndexesFETCH NEXT FROM tableIndexes INTO @indexNameWHILE @@fetch_status = 0BEGINSET @dropIndexSql = N' DROP INDEXF_BI_Registration_Tracking_Summary.' + @indexNameEXEC sp_executesql @dropIndexSqlFETCH NEXT FROM tableIndexes INTO @indexNameENDCLOSE tableIndexesDEALLOCATE tableIndexesTIARob
View 2 Replies View RelatedFor reasons that are not relevant (though I explain them below *), Iwant, for all my users whatever privelige level, an SP which createsand inserts into a temporary table and then another SP which reads anddrops the same temporary table.My users are not able to create dbo tables (eg dbo.tblTest), but arepermitted to create tables under their own user (eg MyUser.tblTest). Ihave found that I can achieve my aim by using code like this . . .SET @SQL = 'CREATE TABLE ' + @MyUserName + '.' + 'tblTest(tstIDDATETIME)'EXEC (@SQL)SET @SQL = 'INSERT INTO ' + @MyUserName + '.' + 'tblTest(tstID) VALUES(GETDATE())'EXEC (@SQL)This becomes exceptionally cumbersome for the complex INSERT & SELECTcode. I'm looking for a simpler way.Simplified down, I am looking for something like this . . .CREATE PROCEDURE dbo.TestInsert ASCREATE TABLE tblTest(tstID DATETIME)INSERT INTO tblTest(tstID) VALUES(GETDATE())GOCREATE PROCEDURE dbo.TestSelect ASSELECT * FROM tblTestDROP TABLE tblTestIn the above example, if the SPs are owned by dbo (as above), CREATETABLE & DROP TABLE use MyUser.tblTest while INSERT & SELECT usedbo.tblTest.If the SPs are owned by the user (eg MyUser.TestInsert), it workscorrectly (MyUser.tblTest is used throughout) but I would have to havea pair of SPs for each user.* I have MS Access ADP front end linked to a SQL Server database. Forreports with complex datasets, it times out. Therefore it suit mypurposes to create a temporary table first and then to open the reportbased on that temporary table.
View 6 Replies View Relatedwhich one is smarter, where there is no indexing on the table which is really simple table delete everything or recreate table. I got an argument with one of my coworker. He says it doesnt matter i say do delete. Any opinions.
View 7 Replies View RelatedHello !
I try to drop a table, but I get this message :
Could not drop object 'dbo.cs_Users' because it is referenced by a FOREIGN KEY constraint
This message does not tell me which is the Foreign key constraint so that I may disable it.
Any solution to force the dropping?
Thank you very much!
Regards,
Fabian
my favorit hoster is ASPnix : www.aspnix.com !
hi, is it possible to recover the dropped table?
if means please tell me how.
hi everyone,
I have a couple questions. I'm very new to SQL and I have this problem:
I need to be able to drop the contents of Existingtable_B into Newtable_A - I found this command (below) that will make a 'copy' but I don't want to keep the contents of Existingtable_B. Is it possible to drop them into Newtable_A instead of copy? Also, I want to do this for 5 tables on Sundays at midnight.. how could I schedule that? Finally, what happens if there is not enough space or some other critical error happens during this procedure? I don't want to lose the data.
Any help is greatly appreciated.
Hi,When I drop a table in Sqlserver 2000 database, The following error occurs:Server: Msg 1204, Level 19, State 1, Line 1The SQL Server cannot obtain a LOCK resource at this time. Rerun yourstatement when there are fewer active users or ask the system administratorto check the SQL Server lock and memory configuration.What's wrong? Any help is greatly appreciated, thanks.
View 4 Replies View RelatedHi everybody,I need some help in SQL Server. I am looking for a command that will "Dropall user table" in auser database.Can anyone help me?Thank you very muchSabrina
View 1 Replies View RelatedI am unable to get the table in the following VB code to actually DROP. A straight SQL version with literals (no variables) runs "successfully" as a query but the table also fails to DROP. Can anybody explain what I'm doing wrong?
sSQL = ""
sSQLExists = ""
sSQLExists = "IF OBJECT_ID (N'ImEx.dbo." & TableName & "', N'U') IS NOT NULL"
sSQLExists = sSQLExists & " DROP TABLE ImEx.dbo." & TableName & ";"
sSQL = "CREATE TABLE [" & TableName & "] (" & sFieldInfo & ");"
ConnectSQLExpress "ImEx.mdf"
DbConn.Execute sSQLExists
DbConn.Execute sSQL
DbConn.Close
Hi
View 4 Replies View Related
What kind of problems may I see if a process drop and re-create a set of tables every night?
i am using vb.net and ms sql server 2005 express.....what is the syntax for dropping a table if existsi have used this but it says incorrect syntax near if Dim cmda As New SqlCommand("drop table " + test + " if exists", New SqlConnection(strdb)) cmda.Connection.Open() cmda.ExecuteNonQuery() cmda.Connection.Close()any solutions???? plz only answer in vb.net and sql server express
View 8 Replies View RelatedI'm trying to drop a temporary table. I keep getting this error:
Cannot drop the table '#temp_table', because it does not exist in the system catalog.
I tried the following but it did not work, so help would be appreciated.
if object_id('tempdb..#temp_table') is not nulldrop table #temp_table
I need some help with Merge Replication. After successfully defining a publisher, distributor and subscriber to perform merge replication, we decided to test Merge Replication to see what we can and can't do.
What we found is that you can't add new fields or change the nullable/null attributes of tables whilst the replication settings are still defined to the databases. ie you get the following error
'tblProducts' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the table 'dbo.tblProducts' because it is published for replication.
So we thought, fair enough. We decided to uninstall replication using the wizards provided to see whether we can make schema changes. What we found is that we can make schema changes to the publisher database(on Server1), BUT not to the subscriber (Server2).
Does anyone know why? We are running SQL7 SP2.
Email me on ftowicz@icontact.com.au
We have a table created by an application, and a view that joins the table with other tables.
For some reason we are now unable to drop the table or the view. In Enterprise Manager the drop table dialog comes up, we click 'Drop All' and then the hour glass comes up and never goes away. No errors are returned, the process just never returns control to the client, the same when trying to remove the view. Using Query Analyzer is no different.
However stopping and starting the server resolves the problem for a while, but eventually the same problem starts happening. The table is created, populated and dropped using stored procedures called from a web page via asp script. This process may occur numerous times and hasn't been a problem until the last day or so when the developer added a couple of smallint columns to the table.
Anyone know what could be the problem here?
Hi all,
how can I drop a column in a table.
Hello,
What's the command for dropping an existing Foreign key on a table and reacreating it in SQL Server 6.5
Thanks
Sri
We have a publication from database a to database b. Database a containts table1 to be published to databasae b. Database b contains a publication of table1 to database c. When we go to rerun the publication from database a it errors saying cannot drop table1 since it is part of database b's publication. how do you do this.
Thanks in Advance,
Phillip M. Triocli
Hi,
I had a question and did not obtain an answer. So I am trying to rephrase and ask again, in case I was not clear the first time.
When we drop and recreate a table, do we always have to recompile the stored procedures that reference the table? Or is only under certain scenarios that we need to do so- like if an index on the table is changed
Please let me know
Thanks in advance
Kiran
I have a group that has select, insert, update, delete permissions and I have a user in that group that
needs permissions also to drop tables.
does that login need to be aliased to dbo - which is in public -- do I then to give puboic all the other permissions.
Is there a way to just give one login all permissions including dropping tables??
Help!!
I want to get rid of a huge table. DELETE FROM would take for-freaking-ever, so I was thinking of doing DROP TABLE. Would this be any faster?
If not, would doing a TRUNCATE TABLE and then DROP TABLE be fast? What's the best way to nuke a big table?
Thanks.
in mysql i can drop a table or db if it currently exists using
drop table if exists [table1] or
drop database if exists [db1]
is there an equalivant in ms sql
thanks
I created the #Temporary table in MS SQL. Now i want to drop this #Temporary table, but i want to check first before i drop the this table. How to check the Temporary table exist or not in MS SQL?
View 4 Replies View RelatedHi all,
I have 2 questions:
1. How can I drop /remove a complete db from MSDE desktop engine?
2. After exporting my db onto MSDE server and get connected those db tables in FE (adp), how can I create a new table in the same back end db?
I know these are basic questions but since I am new to MSDE I hope the forum would bear me.
With kind regards,
Ashfaque
is there a way to drop all the indexes on a table other than listing them out separately in a drop index statement?
View 2 Replies View RelatedHi,
Here is what I have done:
1. I created two tables using:
CREATE TABLE CursorTest
(
RowID INT,
RowText CHAR(4)
)
GO
CREATE TABLE CursorTestOdd
(
RowID INT,
RowText CHAR(4)
)
GO
I then populate the tables using:
SET NOCOUNT ON
DECLARE @intCounter INT
DECLARE @chrTextOdd CHAR(4)
DECLARE @chrTextEven CHAR(4)
SELECT @intCounter = 1
SELECT @chrTextOdd = 'Odd'
SELECT @chrTextEven = 'Even'
WHILE (@intCounter <= 200000)
BEGIN
IF (@intCounter % 2) = 0
BEGIN
INSERT INTO CursorTest VALUES (@intCounter,
@chrTextEven)
END ELSE
BEGIN
INSERT INTO CursorTest VALUES (@intCounter,
@chrTextOdd)
END
SELECT @intCounter = @intCounter + 1
END
GO
3. I then tried to use cursor to insert and delete rows from the tables
DECLARE @intRowID INT
DECLARE curOddRows CURSOR FOR
SELECT RowID
FROM CursorTest
WHERE RowID % 2 = 1
OPEN curOddRows
FETCH NEXT FROM curOddRows INTO @intRowID
WHILE (@@FETCH_STATUS <> -1)
BEGIN
BEGIN TRANSACTION
INSERT INTO CursorTestOdd
SELECT *
FROM CursorTest
WHERE RowID = @intRowID
DELETE CursorTest
WHERE RowID = @intRowID
COMMIT
FETCH NEXT FROM curOddRows INTO @intRowID
END
CLOSE curOddRows
DEALLOCATE curOddRows
GO
Here comes the mess:
I have no problem with codes in step 1&2,but when I tried to execute the codes in step3, I got no error, however, when I checked the two tables, nothing has been changed which means that my insert and delete are not working on the two tables. SO I tried to debug the script and found out that the code:
WHERE RowID % 2 = 1
is not working correctly, it seems that the code returns nothing, if I changed it to
WHERE RowID % 2 = 0
it returns the correct answer.
I then tried to execute the modified codes in step3, I got a message saying that
"A cursor with the name 'curOddRows' already exists."
If I changed the name to 'curOddRows1' and execute, it will say:
"The cursor is already open."
But I execute the code again whatever, and it takes a long time for it to finish such that I cannot wait. So I clicked the 'stop' button to cancel the execution.
Now I want to drop the tables and got following error:
**************************
TITLE: Microsoft SQL Server Management Studio
------------------------------
Drop failed for Table 'dbo.CursorTest'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.Exceptio nTemplates.FailedOperationExceptionText&EvtID=Drop+Table&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=1222&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
===================================
Drop failed for Table 'dbo.CursorTest'. (Microsoft.SqlServer.Smo)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.Exceptio nTemplates.FailedOperationExceptionText&EvtID=Drop+Table&LinkId=20476
------------------------------
Program Location:
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.Dr opImpl()
at Microsoft.SqlServer.Management.Smo.Table.Drop()
at Microsoft.SqlServer.Management.SqlManagerUI.DropOb jects.DoDropObject(Int32 objectRowIndex)
at Microsoft.SqlServer.Management.SqlManagerUI.DropOb jects.DropAllObjects(Boolean stopOnError)
===================================
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Program Location:
at Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Smo.ExecutionManage r.ExecuteNonQuery(StringCollection queries)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.Ex ecuteNonQuery(StringCollection queries, Boolean includeDbContext)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.Dr opImplWorker(Urn& urn)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.Dr opImpl()
===================================
Lock request time out period exceeded. (.Net SqlClient Data Provider)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=1222&LinkId=20476
------------------------------
Server Name:
Error Number: 1222
Severity: 16
State: 56
Line Number: 2
------------------------------
Program Location:
at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQuer yTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNo nQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
**************************
So how can I drop the table, right now I can not even
view the tables' contents, cannot use 'SELECT' etc.. commands, whenever I tried to access the two tables, the program will be keeping doing the execution without stop.
Your comments and suggestions are greatly appreciated!
Thanks,
Using SQL Server 2005. I have a stored prod that uses a temp table. I need to test at the start of the prod to see if the temp table is there. Using the following code at the start of my prod, but does not run.
IF exists(select * from ##TO_STATUS_TBL)
DROP TABLE ##TO_STATUS_TBL
What is the best way to check and/or drop the temp table.
Thank you, David