I'm trying to drop a table onto the design view in Web Matrix and the following message appears: "dropped table does not have a primary key and cannot be used".
I'm using a SQL 2000 database that was previously an MSDE 2000 database. Is there anyway that I can define a column as a primary key?
Thanks.
I am helping another division with a problem of a dropped table. They have no backup and after dropping the table, the server has been re-booted 3 times. Is there a way to recover the table with no backup?
Help anyone,
Thank you.
Somebody has dropped my SQL table in (multiuser environment),now I want
to know who has done that. Is there any way by which I can retrieve
that(user name or machine name)?
I am using SQL 2005.
Hi All,
Accidentally I have dropped a table and that is the main table for my application.
I dont have a backup of it. Is there any way to recover it.Please help me guys.
Thanks,
Kumar.
Unfortunately I dropped my table in sqlserver. How to I recover that table?
View 3 Replies View RelatedAfter installing a Zen Cart plugin I was getting errors that there was a duplicate entry for the mysql table so I went in and dropped the table assuming that it would come back when I re - installed everything, anyways I have the PHP code that created the table but not sure how to recreate it or if that's even possible. Also my main way to get to change anything is phpMyAdmin, I tried importing the code there but that didn't work.
View 2 Replies View RelatedHello,
I'm in trouble, I have just dropped a table from a sql dbase and now need to recreate it. I have no backup and I am panicking!!! I need to know if I can recrreate them .
Any and all help appreciated
DVNC
Hello,
I'm in trouble, I have just dropped a table from a sql dbase and now need to recreate it. I have no backup and I am panicking!!! I need to know if I can recrreate them .
If this can't be done let me know I have to bit the bullet
thanx
I hv dropped the useless columns for a table but the table size is remain unchanged by executing sp_spaceused. pls advise how to release table spaces.
thx
I'm getting a replication error that an object (Table) was not found. Any script that can capture this information?
View 3 Replies View RelatedHello all,
View 12 Replies View RelatedHow can I easily identify who dropped a table?
View 8 Replies View RelatedHi all,
Can anyone suggest me on Adding primary key to a table which has already a primary key.
Thanks,
Jeyam
I need to add a child table that will tell us who the participants counselor is, what I did was I did a Make Table query based off the primary key of the Parent table and made that the link (foreign key) for the People_tbl and the Counselor_tbl, so if the counselor changes then the user adds the record to the counselor tbl and then puts in the Effective date. The problem is that when I run a report it doesn't show the present counselor always shows the old counselor?
Code:
SELECT Student_ind.StudentFirstName, Student_ind.StudentLastName, Student_ind.[Student ID], People_tbl.[Family ID], People_tbl.FirstName,
People_tbl.LastName, People_tbl.[Parent ID]
FROM People_tbl RIGHT OUTER JOIN
Student_ind ON People_tbl.[Family ID] = Student_ind.[Family ID]
WHERE (People_tbl.LastName = @Enter_LastName) AND (People_tbl.FirstName = @Enter_FirstName)
Previously same records exists in table having primary key and table having foreign key . we have faced 7 records were lost from primary key table but same record exists in foreign key table.
View 3 Replies View Related
Hi all,
my stored procedure have one table variable (@t_Replenishment_Rpt).I want to create an Index on this table variable.please advise any of them in this loop...
below is my table variable and I need to create 3 indexes on this...
DECLARE @t_Replenishment_Rpt TABLE
(
Item_Nbr varchar(25) NULL,
Item_Desc varchar(255) NULL,
Trx_Date datetime NULL,
Balance int NULL,
Trx_Type char(10) NULL,
Issue_Type char(10) NULL,
Location char(25) NULL,
Min_Stock int NULL,
Order_Qty int NULL,
Unit char(10) NULL,
Issue_Qty int NULL,
Vendor varchar(10) NULL,
WO_Nbr varchar(10) NULL,
Lead_Time int NULL,
PO_Nbr char(10) NULL,
PO_Status char(10) NULL,
Currency char(10) NULL,
Last_Cost money NULL,
Dept_No varchar(20) NULL,
MSDSNbr varchar(10) NULL,
VendorName varchar(50) NULL,
Reviewed varchar(20) NULL
)
I tryed all below senarios...it is giving error...
--Indexing the @t_Replenishment_Rpt table on the column Names Item Number, Vender , Department Number
--EXEC sp_executesql(CREATE UNIQUE CLUSTERED INDEX Replenishment_index ON @t_Replenishment_Rpt (Item_Nbr))
--CREATE UNIQUE CLUSTERED INDEX Idx1 ON @t_Replenishment_Rpt.Item_Nbr
INDEX_COL ( '@t_Replenishment_Rpt' , ind_Replenishment_id , Item_Nbr )
--EXEC sp_executesql('SELECT INDEXPROPERTY('+ '@t_Replenishment_Rpt' + ', ' + 'Item_Nbr' + ',' + 'IsPadIndex' + ')')
--EXEC sp_executesql(SELECT INDEXPROPERTY('@t_Replenishment_Rpt', 'Vendor','IsPadIndex'))
--EXEC sp_executesql(SELECT INDEXPROPERTY('@t_Replenishment_Rpt', 'Dept_No','IsPadIndex'))
Hi i have set up two very simple tables, I want a user to be able to create a basic account ( data stored in User_Profile table with Id set as the Primery Key as Identity) I
want the user to be able to be able to return to their account at a later date
and then post multiple reviews of different bands they have seen at a later date.
I kept the tables in my example very simple so I could get my head
around the concept, but generally, I want to connect the Id (PK) value in
User_Profile table to the User_Id filed in the User_Review table,
so every review that user writes, will be connected directly to their Id.
Any help you could give would be fantastic a i have no idea where to start!!!
User_Profile
Id int, ( as primary Identity Key)
Name
City
Country
I have a second table called User Reviews
User_Revews
Revew_Id int , ( as primary Identity Key)
User_Id int, ( I want this to contain the Id value in
the User profile Table)
Review_Details
Thanks
Odxsigma
mahesh writes "HI,
I am new to sql server.
can anybody help me
I have a table named tblqualificationmaster.
can i know the foreignkeys and the table related to this
tblqualificationmaster having foeign keys using stored procedure."
Hi,
I have a Users table that I use for membership. I am using username varchar(30) as the primary key for this table since username will always be unique.
The question I have is regarding how SQL Server actually stores data:
I see that when I add users, they are always stored alphabetically sorted on username.
I was expecting that all users will appear on the users table in the order they were added.
Example: I have 3 users (john, jonah, wilson). Now I added 4 user with username='bob'
If I execute select * from users, it returns me (bob, john, jonah, wilson). Look bob has become the first row of the table.
My question: Is Sql server moving 3 older rows to make room for 'bob' and it is also rebuilding part of the index due this new username 'bob'?
If this is the case, then it will have big impact if I have 100K users and I add one user that becomes firstrow. In that case 99,999 rows will have to move.
Bottom line, insert, delete will be very expensive.
I know sql server keeps data physically sorted on PK. But I am concerned here since rows are losing the order in which they were inserted.
Thanks
Is it possible at all to remove the SA login or deny "System Administrator" from it by loggin as another user who has sa previledges
thanks
Here's a poser.
I run a VB application to import data from a text file into a SQL 6.5 database. Typically, there'll be 300,000 records, all of which have to be processed. Time taken will be in excess of 2 hours.
When the application finishes, I try to go into Enterprise Manager and run a Select Count(*) to verify that the data's been imported. Invariably, I get an error reading:
"A connection could not be established to <server> - [SQL Server] Could not allocate a new object descriptor for required system catalog in database '1'. Another database must be closed or objects in another database dropped in order to open this database."
I get the same error whether I try to view Current Activity, open the Query Tool or simply expand the server in the Server Manager area.
Usually, it means I have to re-boot the server, which is not popular with other users. Does anybody know what's causing this and what might be the solution?
Thanks.
I believe I deleted a maintenance plan that was attached to a job and therefore have received the following message:
Drop failed for Job 'Shrink DSS Database.Subplan_1'. (Microsoft.SqlServer.Smo)
An execption occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlSserver.connectionInfo)
The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'job_id'.
The statement has been terminated. (Microsoft SQL Server, Error: 547)
Is there a T-SQL or other means I can use to remove this constraint and delete the job show it does not show up in SQL Server Management Studio any longer?
There is a restriction that we must have only one primary key per table. We can have composite primary key by defining primary key over 2 or more columns. Actually,, a primary key uniquely defines the table and if we have two or more columns which follow unique and not null features why can't we have two primary keys?? I want to know what is the main reason behind having only one primary key in a table??
View 3 Replies View RelatedI ran into a table that is used a lot. Well less than100,000 records. Maybe not a lot of records but i believethis table is used often. The table has 26 fields, 9 indexesbut no Primary Key at all!There are no table relationships defined in this database, noNatural keys, only Surrogate keys in the database.1- Maybe an odd question but is it normal to have 1/3 of thetable's fields as indexes? Is this a valid question or it reallydoesn't matter if you have 9 indexes if they are appropriate to beindexes?2- Below is the DDL of the indexes (Is DDL the appropriate termto describe the indexes?) Without going into too technical aboutwhat the table is, what relationships it has with other tables,would you be able to tell if the indexes are good, bad, too many,etc?3- If i open the table in DESIGN view in SQL EM, i don't seethe Primary key icon. Yet here i see the words "PRIMARY KEYNONCLUSTERED". Does this mean UNIQUENO is actually some typeof primary key? If it was CLUSTERED then SQL EM would showUNIQUEID with a key to the left it identifying it as a PK?If that is the case, then what is the difference betweenPRIMARY KEY NONCLUSTEREDandPRIMARY KEY CLUSTERED?CREATE UNIQUE CLUSTERED INDEX [TBLTEST_PK] ON[dbo].[TBLTEST]([UNIQUENO]) WITH FILLFACTOR = 80 ON [PRIMARY]GOCONSTRAINT [PK_TBLTEST] PRIMARY KEY NONCLUSTERED([UNIQUENO]) ON [PRIMARY]GOCREATE UNIQUE INDEX [ASSIGNUNIQUENAME] ON[dbo].[TBLTEST]([USERNO], [STARTDATE], [NAME]) WITHFILLFACTOR = 80 ON [PRIMARY]GOCREATE INDEX [ENDDATE] ON [dbo].[TBLTEST]([ENDDATE]) WITH FILLFACTOR = 80 ON [PRIMARY]GOCREATE UNIQUE INDEX [IUSERASSIGNACT] ON[dbo].[TBLTEST]([USERNO], [TASKNO], [PROCESSENTRYNO])WITH FILLFACTOR = 80 ON [PRIMARY]GOCREATE INDEX [STARTDATE] ON [dbo].[TBLTEST]([STARTDATE]) WITH FILLFACTOR = 80 ON [PRIMARY]GOCREATE INDEX [PROCESSENTRYNOTBLTEST] ON[dbo].[TBLTEST]([PROCESSENTRYNO]) WITHFILLFACTOR = 80 ON [PRIMARY]GO/****** The index created by the following statementis for internal use only. ******//****** It is not a real index but exists asstatistics only. ******/if (@@microsoftversion > 0x07000000 )EXEC ('CREATE STATISTICS [Statistic_NAME] ON[dbo].[TBLTEST] ([NAME]) ')GOCREATE INDEX [TASKNO_IDX] ON [dbo].[TBLTEST]([TASKNO]) WITH FILLFACTOR = 80 ON [PRIMARY]GOCREATE INDEX [TBLTEST_ORGANIZATIONNO_IDX] ON[dbo].[TBLTEST]([ORGANIZATIONNO]) WITHFILLFACTOR = 80 ON [PRIMARY]GOCREATE INDEX [TASKNOUSERNO] ON [dbo].[TBLTEST]([USERNO], [TASKNO]) WITH FILLFACTOR = 80 ON [PRIMARY]GOThank you
View 4 Replies View RelatedHi to all.
I have a table with about 8 columns as a primary key. I would like to delete one column but not destroy the other 7 primary keys and their relationships between the other tables. Is it possible to do this?
Thanks.
Hi
ONLY ONE of our many stored procedure is getting dropped in SQL 7.0. We were not able to find thru SQL Profiler. Let me know any alternate methods to find it or the possible reasons why/how the sp is getting dropped.
Thanks
Hello,
I have the following problem that I need to find a solution. I have created a generic user id and login in the model datbase on SQL 7. However the genericuser does not show in the explorer window for some databases, does not work on some databases. I have to go into TSQL and use dropuser to get rid off them and re-link them again.
Does any1 have a clue about why this happening? How it can be solved?
TIA Gurmi
I was doing a DTS import and accidently imported into the wrong database, dropping very critical tables. Please let me know how I can restore these transaction back. My last backup was 24 hours ago, and I just need to restore a few hours back.
Thanks,
KJ
Hi..I just restored a databae from backup. After restoring, as usual, from the Enterprises manager, I want to delete the database user and readd again from the DB login name. But when I tried to delete the DB user, it prompts me the following.
"the selected user cannot be dropped because the user owns objects"
why? I never encountered this b4 ? I restored DB again but problem still the same. Any ideas?
I am in a big trouble ,
accidently i have issued a DROP DATABASE XXX command in the sql client, thinking its a local server....... The whole 4 months of database database is now dropped.
Please help me , how can i recover the database ....
thanks in advance
Hi,
In my local instance of SQL Server at work (which I use for testing), somehow all of the logins except 2 were dropped. The administrators group no longer exists as a login (nor do any other windows users or groups)-the only 2 remaining are sa (which is disabled) and a SQL login I had created earlier which has no permissions except read permission on master. I can login as this SQL user, but I do not have permissions to create logins or enable the sa account. Do I have any options other than uninstalling and reinstalling my local instance?
Thanks,
-Dave
I am testing sql server 2005 in different disaster situations happened under 2000. I was reading a lot about no update on system tables, so:
sp_detach_db 'tempdb'
In sql 2000 i could issue something like the following command to recover:
use master
sp_configure 'allow updates', 1
reconfigure with override
go
insert sys.sysdatabases (name, dbid, sid, mode, status, status2, crdate, reserved, category, cmptlevel, filename, version)
values ('tempdb', 2, 0x01, 0, 8, 1090520064, '2007-01-27 13:03:10.873', '1900-01-01 00:00:00.000', 0, '90', 'D:eepmssql emp empdb.mdf', 611)
go
sp_configure 'allow updates', 0
reconfigure with override
go
As tempdb is hardcoded db id 2, I do not have a chance to recover without a master backup.
Of course, this situation can be recovered using a master backup, but it is not always available.
Thank you for your comments.
I'm trying to find the primary key on a given table in SQL Server 2000 using SQL. I'm querying the sysobjects table to find a given table, and then querying the sysindexes table. I've ALMOST found what I'm looking for. I see the indexes and columns etc. on the tables in the database, I just don't see the field that indicates that the index is the primary key.
Can anyone help?
Thanks, Alex