I just upgraded from SQL2000 to SQL 2005. When I have a query opened for a database already and I want to drag and drop a script in, I get the login box. Is there anyway to get this to not happen? I would like it that when I drag and drop the script it is just loaded in a new query with the database connection that I was using already. (This is what it did in SQL2000) Thanks.
View 4 RepliesI am having problem to find the right syntax to DROP a column with contrainst and recrate it
I get an error
if exists ( select * from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='myTable'
and COLUMN_NAME='myDate' )
ALTER TABLE [dbo].[myTable] DROP COLUMN myDate
GO
ALTER TABLE [dbo].[myTable] WITH NOCHECK ADD
myDate datetime CONSTRAINT [DF_myDate] DEFAULT (GetDate())
GO
Query Analyser says :
Server: Msg 5074, Level 16, State 1, Line 5
The object 'DF_myDate' is dependent on column 'myDate'.
Server: Msg 4922, Level 16, State 1, Line 5
ALTER TABLE DROP COLUMN myDate failed because one or more objects access this column.
Server: Msg 2705, Level 16, State 4, Line 2
Column names in each table must be unique. Column name 'myDate' in table 'dbo.myTable' is specified more than once.
thank you for helping
I have two transaction log files, one on C drive and other on D drive. I want to drop the file from that database that is on C drive. Can anyone help me out on this with complete syntax.
I have taken a down time of one and half hour to accomplish this task
Here is what think
Take a full database backup
Take transaction log backup
Shrinkfile using DBCC with truncateonly option
emptyfile using dbcc or drop file using alter database remove
thanks for your input guys
Rome
Hi All,I am having a serious problem of removing and adding again an user in adatabase.Microsoft SQL Server 2000 - 8.00.760 (Intel X86)Dec 17 2002 14:22:05Copyright (c) 1988-2003 Microsoft CorporationEnterprise Edition on Windows NT 5.2 (Build 3790: Service PackSomehow, the user was created earlier but was not able to run queryassigned to him. As a result I wanted to drop and recreate the user.I have done many possible things to create the users again with thesame user id but failed.1. I tried to delete this user from the enterprsie manager security-->logins-->user1It deletes but when I try to add again, it gives me error message(Error 15023: user or role u'user1' already exist).2. Then I tried in the db:delete sysusers where name='user1'it deletes the user1.3. Again tried adding, got the message in 1.4. Then I trieduse db1EXEC sp_change_users_login 'Update_One', 'user1', 'user1'Server: Msg 15291, Level 16, State 1, Procedure sp_change_users_login,Line 88Terminating this procedure. The User name 'user1' is absent or invalid.I also tried master database and ran the following.EXEC sp_droplogin 'user1'The login 'user1' does not exist.But If I try to add the login user1, get the error message.Error 15023: user or role u'user1' already existI also ran the following when I got Ad hoc error messageexecute sp_configure "allow updates",1goreconfigure with overridegoCould you please tell me how I can solve this problem.I do highly appreciate your help.Thanks a million in advance.best regards,mamun
View 1 Replies View RelatedHi,
I get the following error message while droping unique index.
Server: Msg 3723, Level 16, State 5, Line 1
An explicit DROP INDEX is not allowed on index 'dbo.ALEG.IX_ALEG'. It is being used for UNIQUE KEY constraint enforcement.
Could you please how the index can be droped.
Thanks
Anybody had experience dropping the system generated index?
I tried to drop some auto-generated index which usually have name like _WA_Sys_[column name}_07F6335A. Then I follow the table.index name rule. It always show no such index exists in the database. But I can query these indexes in sysindexes and verify they are there. What went wrong?
Help appreciated very much.
wilson
I'm familiar with how to check for the existence of a table before dropping it using the following command:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[xxx]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[xxx]
How does one check for the existence of a temp table (using # syntax) before dropping it? I've tried various flavors of this command and none work. One flavor is
use tempdb
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[#xxx]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[#xxx]
CREATE TABLE #xxx (
NumID INTEGER IDENTITY(1,1),
Exhibitor_Id INTEGER NOT NULL,
Company_Id INTEGER NOT NULL
)
Thanks.
Nick
I have a database that is about 300 gig. I am setting up replication to a reporting server. We are doing a series or mock loads and I will need drop the tables and reload the main database a few times before we go live. To do that I plan to stop replication and drop all the articles, drop the subscription, then load the new data, then reinitialize and restart replication.
The first time I tried to do this, when I drop the articles, it seems to be trying to "clean up" the distribution database on the reporting server and that is taking a couple of hours to do. The disruption database is about 40 gig.
Is this correct behavior in SQL2005 replication? Is there a way to avoid this? I have all the replication pieces scripted out and would like to just drop replication, reload, and then run my scripts to recreate replication. But this "clean up" is going to cause me a lot of headache if I don't figure out what is going on.
Am I going down the wrong road here? Is there an easier way to do this? Any comments would be great!!!!
Thanks in advance for any help.
Jim Youmans
St. Louis Missouri