hi guys,
i have a code which has views and tables. i am trying to drop the table but i am geting an error. the code is:
DROP TABLE dbo.sp
and the error that i am getting is:
Cannot drop the table 'sp', because it does not exist or you do not have permission.
I have a drop down list populated using sqldatasource1 it is populated with [item]
However, I need it to be poplulated with the three fields below AND, I don't want duplicates.
SELECT [Item], [Alias1], [Alias2] FROM [Authors]
I have a query which joins multiple tables. This query has suddenly begun to take up to 2 minutes to run (vs. 5-10 seconds previously).
No major change in number of records in the tables (currently about 220,000). When I remove the PK from one of the tables which then forces a tablescan, the query returns to running in 5-10 seconds. If I add the PK back, performance is back to 2 minutes.
Any suggestions short and/or long term?
Thanks for any help.
What's query dropping Default value constraint from a column?
View 1 Replies View RelatedHi,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 RelatedWe are running MS RS and SQL Server 2000 SP3.
We have one LEDGER, where all the daily activities are stored. The LEDGER table has 4 indexes (1 clustered and 3 non-clustered). To get AR we use this table.
Well problem is some times in 1-2 months, any simple AR query takes a long time and every other client gets slow response (queries are very slow or sometimes block).
If we DROP any index on LEDGER table and again put it back (RECREATE), all our queries work fine and faster. This goes on till 1-2 months, till we see the same issue again.
This is a classic case happened today. Queries were running fine till morning 8 AM. We upload some 50 thousand records to Ledger table (Data Conversion). Well after 30 mins, all simple AR queries started taking a long time. We DROPPED an index in LEDGER table and everything was faster....Just to be same we added back the same index again.......everything is Faster.....
What is this. ....is it our QUERY, index or huge Transactions or no free space ???
We are scheduled to run SP4, next week. But is there any solution in the mean time on what is this?
Also is they any way to KILL all SQL server processes that take more than a mins. We just don't want ALL our client to Slow down because of one query????
Thanks,
Hello. I currently have a website that has a table on one webpage. When a record is clicked, the primary key of that record is transfered in the query string to another page and fed into an sql statement. In this case its selecting a project on the first page, and displaying all the scripts for that project on another page. I also have an additional dropdownlist on the second page that i use to filter the scripts by an attribute called 'testdomain'. At present this works to an extent. When i click a project, i am navigated to the scripts page which is empty except for the dropdownlist. i then select a 'testdomain' from the dropdownlist and the page populates with scripts (formview) for the particular test domain. what i would like is for all the scripts to be displayed using the formview in the first instance when the user arrives at the second page. from there, they can then filter the scripts using the dropdownlist.
My current SQL statement is as follows.
SelectCommand="SELECT * FROM [TestScript] WHERE (([ProjectID] = @ProjectID) AND ([TestDomain] = @TestDomain))"
So what is happening is when testdomain = a null value, it does not select any scripts. Is there a way i can achieve the behaivour of the page as i outlined above? Any help would be appreciated.
Thanks,
James.
Hi folks.
Here i have small problem in transactions.I don't know how it is happaning.
Up to my knowldge if you start a transaction in side the transaction if you have DML statements
Those statements only will be effected by rollback or commit but in MS SQL SERVER 7.0 and 6.5
It is rolling back all the commands including DDL witch it shouldn't please let me know on that
If any one can help this please tell me ...........Please............
For Example
begin transaction t1
create table t1
drop table t2
then execute bellow statements
select * from t1
this query gives you table with out data
select * from t2
you will recieve an error that there is no object
but if you rollback
T1 willn't be there in the database
droped table t2 will come back please explain how it can happand.....................
Email Address:
myself@ramkistuff.8m.com
How can I drop Primary Key? If I do/don't have data in the table.Thanks!
Ravi.
Why can't I drop the database? Is this query correct Drop database databasename?
View 1 Replies View RelatedDear Friends,
i want to drop UDD.which i have used in 1000 of sps and tables. it is impossible to drop all objects and create.so please give any sugessions.
Hi,
I have an sql script, which updates some values by using BIT operations..
ex:
UPDATE table1
SET myValue = myValue & ~mask | (availability - mask)...
Problem is, this returns a value, when written to binary, it's one bit too long!
ex:
0
1
1
1
1
1
(= 62)
which should be:
0
1
1
1
1
0
(=31)
How can I "drop" this last BIT?
(Keep in mind that I store these values as LONGINT's)...
Been fighting with this one all day..
Hi folks,
Wonder if I'm onto something....everything I've read says that to drop foreign key constraints, you have to drop/recreate the table.
I've been able to dynamically create the ALTER TABLE statements for all FK constraints in a database. The way I did was a little convoluted (Access 97 linked tables and pass-through queries, cut/paste from the debug window to SQL7 QA), but it does work. More importantly, it saves the hassle of dropping/recreating tables....Someone with more expertise than I in using nested cursors could probably convert it to pure T-SQL pretty easily.
Just curious...opinions? Be glad to post the code here if that's appropriate.
Dan
Does anyone know why this does not work?
ALTER TABLE deal DROP COLUMN TestField
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'TestField'.
Thanks,
Paul
I am trying to drop some tables, but keep getting the message:
Msg 3702, Level 16, State 1
Cannot drop the table 'TABLE' because it is currently in use. But it is NOT IN USE. I tried to do an sp_who, and I'm the only one. Please help. Thank you.
I am trying to delete a database that is labeled suspect. Is there a way to force a drop of a database? It is shows it is marked inaccessible when trying to delete.
Thanks, Steve
I am managing 120 gb SQL7 database. The 120 GB is in a RAID5 (Hardware RAID). At begining, I think I should create a user defined filegroup besides Primary filegroup, as BOL suggested.
After I read some discussions here, It appears I cannot gain any performance by adding second filegroups. Now I want to drop the filegroup I created. The Question is How?. If I do alter database, move all files from userdefined filegroup, does this mean my userdefined filegroup has gone?
Someone can help me on this!
Thanks a lot.
I am trying to drop the database through the application designed using vb.net. Through login form i am logging into the database from the application. There is an option for the user to take the backup and restore the database using the application. I am able to take the backup of the database. To restore the database, i am dropping the existing database. I am unable to drop the database as an error message is being displayed saying that "Cannot drop the database, as it is currently in use". I tried to drop the database even by changing the database names in the connection string.
But i am unable to drop the database. If any one finds the solution for this please help me. Its very urgent.
Thanks and Regards,
sashirekha
Hi:
'Unfortunately', there is still a SQL6.5 sp4 server that I need to handle.
I need to drop a coulmn xyz from an important table tblABC.
But neither Enterprise manager could do it nor
'alter table tblABC drop column xyz' could run.
Without the hastle of makin a tblABCcopy and copy all data from tblABC except column xyz and re-do all fks; is there another way to drop the column?
thanks
-D
How do you create a php or html page with a drop down menu from a table a database? Please show any code or helpful links,
Thanks!
I have a database with many PK and FK constraints. Is there any script I can use to drop all indexes and rebuild them taking care of PK and dependencies? I am unable to drop them by tablename order. This is on MSSQLServer-2000 SP3. Any help is appreciated.
View 4 Replies View Relateddoes anyone know how to drop the last part of an ip address? I've been searching various sites for string functions and havn't come up with anything that works. What I want is something that will "find the third occurance of a "." and then return everything to the left of it"
I've tried various types of InStr and even found a SUBSTRING_INDEX function, but apparently it's only for mysql.
i'm using sql server and linking tables thru access
fisk
Hello, I have hit the wall here...can't make sense of this one.
I have a script that creates a PRIMARY KEY constraint called PK_tblDRG
CODE:
ALTER TABLE [dbo].[tblDRG]
ALTER COLUMN [record_id] Uniqueidentifier NOT NULL
Go
ALTER TABLE [dbo].[tblDRG]
WITH NOCHECK ADD PK_tblDRG PRIMARY KEY CLUSTERED
(
[record_id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
All is fine with that. I run this to verify:
EXEC sp_pkeys @table_name = 'tblDRG'
,@table_owner = 'dbo' ,@table_qualifier = 'Relational_05Q3'
which returns this:
TABLE_QUALIFIERTABLE_OWNERTABLE_NAMECOLUMN_NAMEKEY_SEQPK_NAME
Relational_05Q3dbotblDRGrecord_id1PK_tblDRG
Now I want to drop the constriant if it exists:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PK__tblDRG]') and OBJECTPROPERTY(id, N'IsPrimaryKey') = 1)
ALTER TABLE [dbo].[tblDRG] DROP CONSTRAINT PK__tblDRG
AND I get this in return:
The command(s) completed successfully.
So, lets double check:
EXEC sp_pkeys @table_name = 'tblDRG'
,@table_owner = 'dbo' ,@table_qualifier = 'Relational_05Q3'
AND I STILL GET THIS:
TABLE_QUALIFIERTABLE_OWNERTABLE_NAMECOLUMN_NAMEKEY_SEQPK_NAME
Relational_05Q3dbotblDRGrecord_id1PK_tblDRG
Hmmmmm. Looks like the IF statement didn't do it's job. Ok fine. I'll just kill it myself:
ALTER TABLE [dbo].[tblDRG] DROP CONSTRAINT PK__tblDRG
AND I GET THIS?!?!?!?!
Server: Msg 3728, Level 16, State 1, Line 1
'PK__tblDRG' is not a constraint.
What am I not getting here? Is it me...I can take If I am a bone head here.
Any help would be appreciated. Thanks!
is there anyone know how to drop subscription from the publisher database without a connection to the subscriber(because the subscriber server is formated)
View 3 Replies View RelatedI'm want to be able to choose(including multiple selections) names from a drop down list that are fetched from the database(from table "Employees") and add them to table "Biotechnology".
Employees(ID,FirstName,LastName)
Biotechnology(ID,EmployeeID)
**assuming that EmployeeID in "Biotechnology" is the ID from "Employees" table
Example: We add Employees.ID(23, 33) to Biotechnology.EmployeeID
would be
Biotechnology:
[ID] [EmployeeID]
01 - 23
02 - 33
Hi,
I am using SQL Server 2005, trying to drag and drop a sql-query but it is not possible. This was possible before, what might be the cause?
Now, I have to open file, browse to the query and then it works but that is very time consuming. Would appreciate any suggestion.
Here is my code,
1. create table t1 (id int )
2. create unique index u on t1(id)
3. create table t2 (id int constraint f foreign key references t1(id))
4. ALTER TABLE t2 NOCHECK CONSTRAINT ALL
5. ALTER TABLE t1 NOCHECK CONSTRAINT ALL
6. drop index t1.u
I get a FK voilation exception. on line 6.
I was not expecting this, because I am doing 4 & 5
Is dropping the FK is only option?
Actually I am trying to move the unique index to a different file group.
------------------------
I think, therefore I am - Rene Descartes
I need to use T-SQL to drop all instances of a particular object, no matter who the owner is. For example I might have two instances of a view named "test_view". One is owned by "dbo" and one is owned by "randomly_named_user". I do not know what the name of the owner of the second instance of the view, nor do I know how man instances of the view may exist. I need to drop all the views with the name "test_view".
Is there a way with T-SQL to drop all the instances of an object no matter who owns it? I understand I will need proper permissions to drop an object owned by someone else.
Hi
I am wana to know why we every time drop the procedure and again create it as below...will that impact on performance or compilation of SP???
Use Northwind
GO
IF OBJECT_ID('dbo.ListCustomersByCity') IS NOT NULL
DROP PROC dbo.ListCustomersByCity
GO
CREATE PROCEDURE dbo.ListCustomersByCity @Country nvarchar(30)='%'
AS
SELECT City, COUNT(*) AS NumberOfCustomers
FROM Customers
WHERE Country LIKE @Country
GROUP BY City
GO
T.I.A
I want to delete a table.This table is created with foreign key.How to remove foreign key.
View 3 Replies View RelatedI have a question, Every month I get a request from cutomer service to go into a SQL VIEW and DELETE a column to reflect the past Month. How to write a query to create a SQL job to performed this task automatically. For example today I deleted the column [2015/03] which represents the month of March. The columns left are [2015/04] representing the current month, [2015/05], [2015/06],[2015/07]....[2016/08].Next month I will have to delete column [2015/04]and the current month will be [2015/05]....etc.
So I'm thinking I will have to Drop the column and also Maybe using a date funtion to look at the current date and format it to reflect [YYYY/MM] and IF the Column Month is less than current date month then drop the column.
Hello !
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.