DENY permission,SQL server: default

Showing posts with label default. Show all posts

Wednesday, March 21, 2012

deleting an instance of SQL Server

We have a server with with four instances of SQL Server 2000 running on a
server with Win2003. On of the instances (not the default) was a Biztalk
development server. Well, I want to delete that old SQL Server instance and
reinstall a new instance ( I want to have a new name for the installed
instance of SLQ Server)
Question - will I have to stop and start the server to get this done or can
I delete that old database instance and reinstall the new database instance?
richHi Rich
"Rich" wrote:

> We have a server with with four instances of SQL Server 2000 running on a
> server with Win2003. On of the instances (not the default) was a Biztalk
> development server. Well, I want to delete that old SQL Server instance a
nd
> reinstall a new instance ( I want to have a new name for the installed
> instance of SLQ Server)
> Question - will I have to stop and start the server to get this done or ca
n
> I delete that old database instance and reinstall the new database instanc
e?
> rich
Have you seen http://msdn2.microsoft.com/en-us/library/ms144255.aspx. I
don't think you will need to reboot, but you can install the new instance an
d
just stop the service for the old instance. Then remove that instance when
you have a maintenance window.
John

deleting an instance of SQL Server

We have a server with with four instances of SQL Server 2000 running on a
server with Win2003. On of the instances (not the default) was a Biztalk
development server. Well, I want to delete that old SQL Server instance and
reinstall a new instance ( I want to have a new name for the installed
instance of SLQ Server)
Question - will I have to stop and start the server to get this done or can
I delete that old database instance and reinstall the new database instance?
richHi Rich
"Rich" wrote:
> We have a server with with four instances of SQL Server 2000 running on a
> server with Win2003. On of the instances (not the default) was a Biztalk
> development server. Well, I want to delete that old SQL Server instance and
> reinstall a new instance ( I want to have a new name for the installed
> instance of SLQ Server)
> Question - will I have to stop and start the server to get this done or can
> I delete that old database instance and reinstall the new database instance?
> rich
Have you seen http://msdn2.microsoft.com/en-us/library/ms144255.aspx. I
don't think you will need to reboot, but you can install the new instance and
just stop the service for the old instance. Then remove that instance when
you have a maintenance window.
John

Monday, March 19, 2012

Deleting a column DEFAULT....

Hi,
I've the following problem. I must delete a column DEFAULT from a table,
but I must do it with a script, independently from the server where it'll
be executed.
Locally I've tried with:

ALTER TABLE [dbo].[PlanningDettaglio]
DROP CONSTRAINT [DF__PlanningD__OreSt__688C6DAC]
GO

ALTER TABLE [dbo].[PlanningDettaglio]
ALTER COLUMN [OreStraordinario] varchar(5)
GO

ALTER TABLE [dbo].[PlanningDettaglio]
ADD DEFAULT ('00.00') FOR [OreStraordinario]
GO

it works, but only locally.
I've tried with:

ALTER TABLE PlanningDettaglio ALTER COLUMN OreStraordinario DROP DEFAULT

Err.: Incorrect syntax near the keyword 'DEFAULT'.

Can someone help me please?

Thanks in advance,
Giacomo

P.S.
We're using SQL Sever 2000 (version 8.00.194)Giacomo (no_spam@.grazie.it) writes:

Quote:

Originally Posted by

P.S.
We're using SQL Sever 2000 (version 8.00.194)

Before you do anything else, install SP4. What you have is the RTM version
which is five years old, and there are many bugs that have been fixed.
Not the least the hold that made the Slammer worm possible.

No, it will not address your problem at hand, and being at work now I don't
have the time to post the exact code. You will need to read the system
tables to get the name of the default and then use dynamic SQL to run
ALTER TABLE DROP CONSTRAINT. Best practice is to always name your defaults,
so that you don't run into this problem.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Giacomo,

You must use ALTER TABLE ... DROP CONSTRAINT to drop the column default.

Another option (than the one posted by Erland) is to recreate the
column. Note that this will only work if there is no DRI on the column,
and no indexes. For example:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION

EXEC sp_rename 'PlanningDettaglio.OreStraordinario', '__old', 'COLUMN'

ALTER TABLE PlanningDettaglio
ADD OreStraordinario DECIMAL(18,2) NULL -- or whatever the original
definition

UPDATE PlanningDettaglio
SET OreStraordinario = __old

ALTER TABLE PlanningDettaglio DROP COLUMN __old

COMMIT TRANSACTION

HTH,
Gert-Jan

Giacomo wrote:

Quote:

Originally Posted by

>
Hi,
I've the following problem. I must delete a column DEFAULT from a table,
but I must do it with a script, independently from the server where it'll
be executed.
Locally I've tried with:
>
ALTER TABLE [dbo].[PlanningDettaglio]
DROP CONSTRAINT [DF__PlanningD__OreSt__688C6DAC]
GO
>
ALTER TABLE [dbo].[PlanningDettaglio]
ALTER COLUMN [OreStraordinario] varchar(5)
GO
>
ALTER TABLE [dbo].[PlanningDettaglio]
ADD DEFAULT ('00.00') FOR [OreStraordinario]
GO
>
it works, but only locally.
I've tried with:
>
ALTER TABLE PlanningDettaglio ALTER COLUMN OreStraordinario DROP DEFAULT
>
Err.: Incorrect syntax near the keyword 'DEFAULT'.
>
Can someone help me please?
>
Thanks in advance,
Giacomo
>
P.S.
We're using SQL Sever 2000 (version 8.00.194)

|||Erland Sommarskog (esquel@.sommarskog.se) writes:

Quote:

Originally Posted by

Giacomo (no_spam@.grazie.it) writes:

Quote:

Originally Posted by

>P.S.
>We're using SQL Sever 2000 (version 8.00.194)

>
Before you do anything else, install SP4. What you have is the RTM version
which is five years old, and there are many bugs that have been fixed.
Not the least the hold that made the Slammer worm possible.
>
No, it will not address your problem at hand, and being at work now I
don't have the time to post the exact code. You will need to read the
system tables to get the name of the default and then use dynamic SQL to
run ALTER TABLE DROP CONSTRAINT. Best practice is to always name your
defaults, so that you don't run into this problem.

So this is how you do it:

DECLARE @.default sysname
SELECT @.default = object_name(cdefault)
FROM syscolumns
WHERE id = object_id('yourtable')
AND name = 'yourcolumn'
EXEC ('ALTER TABLE yourtable DROP CONSTRAINT ' + @.default)

Giacomo|||Actually I m also facing the same problem. I had need of fetching data
from two tables for which we used Diagram for inner joining. Later I
found that in VB.Net when we update or save any new data in one of
those tables which were inner joined it was showing error, so we
deleted that diagram and updated the data without joining.
Now when we try to delete some data from one of those tables it shows
message Delete statement conflicted with COLUMN REFERENCE constraint
<constraint name >The conflict occured in database <databasename>,table
tablename,column <columnname>

Kindly give solution of this problem.
sonika.

Gert-Jan Strik wrote:

Quote:

Originally Posted by

Giacomo,
>
You must use ALTER TABLE ... DROP CONSTRAINT to drop the column default.
>
Another option (than the one posted by Erland) is to recreate the
column. Note that this will only work if there is no DRI on the column,
and no indexes. For example:
>
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
>
EXEC sp_rename 'PlanningDettaglio.OreStraordinario', '__old', 'COLUMN'
>
ALTER TABLE PlanningDettaglio
ADD OreStraordinario DECIMAL(18,2) NULL -- or whatever the original
definition
>
UPDATE PlanningDettaglio
SET OreStraordinario = __old
>
ALTER TABLE PlanningDettaglio DROP COLUMN __old
>
COMMIT TRANSACTION
>
HTH,
Gert-Jan
>
>
Giacomo wrote:

Quote:

Originally Posted by

Hi,
I've the following problem. I must delete a column DEFAULT from a table,
but I must do it with a script, independently from the server where it'll
be executed.
Locally I've tried with:

ALTER TABLE [dbo].[PlanningDettaglio]
DROP CONSTRAINT [DF__PlanningD__OreSt__688C6DAC]
GO

ALTER TABLE [dbo].[PlanningDettaglio]
ALTER COLUMN [OreStraordinario] varchar(5)
GO

ALTER TABLE [dbo].[PlanningDettaglio]
ADD DEFAULT ('00.00') FOR [OreStraordinario]
GO

it works, but only locally.
I've tried with:

ALTER TABLE PlanningDettaglio ALTER COLUMN OreStraordinario DROP DEFAULT

Err.: Incorrect syntax near the keyword 'DEFAULT'.

Can someone help me please?

Thanks in advance,
Giacomo

P.S.
We're using SQL Sever 2000 (version 8.00.194)

|||sonu (sonika4oct@.gmail.com) writes:

Quote:

Originally Posted by

Actually I m also facing the same problem. I had need of fetching data
from two tables for which we used Diagram for inner joining. Later I
found that in VB.Net when we update or save any new data in one of
those tables which were inner joined it was showing error, so we
deleted that diagram and updated the data without joining.
Now when we try to delete some data from one of those tables it shows
message Delete statement conflicted with COLUMN REFERENCE constraint
><constraint name >The conflict occured in database <databasename>,table
tablename,column <columnname>
>
Kindly give solution of this problem.

I'm sorry, but you will have to try again. To start with, your problem
appears to be completely different from what Giacomo had, so please start
a new thread.

As for your problem, you run a DELETE statement and a constraint fires.
I assume that this is a foreign key constraint. Usually foreign keys
are a good thing, as it prevents from you from doing bad things like
deleting a product which are listed on open order items. Then you talk
about diagrams, but it's impossible to understand what you are talking
about. Are you talking about database diagrams in an SQL tool, or is one
of your tables called Diagrams? Please post again, and make an effor to
explain your problem clearly. It helps if you includ the definition of
the involved tables, and the SQL you are having problem with.

Friday, March 9, 2012

Deleted Default DB, Need Hlp Fast!

I have stoopidly enough deleted default Db. That causes Enterprise Manager to be unable to work with my DB's. The default DB I deleted had no other functions other then being default DB, I mean it was outdated, and I had other DB's that contained all my importent work. They are still running, and I can view DB driven site hosted at localhost, even though default DB no longer excist. I am even able to upload new content, or add new users, so this means all my other DB's are fine. I can even see SQL server icon in my bottom right corner of my desktop, and it shows server running.

Now I am in the need of adding tables and rework some of my excisting tables and stored procedures, but I am not able to do that with Enterprise Manager, due to the lack of default Database.

How do I correct this problem? I have gotten one tip of doing the following:EXEC sp_defaultdb 'User', 'DB' but I am not sure what to do with this....tried to run it from command line, and put my username and the DB I would set to default but nothing happend.

So I need more details,step-by-step guiding will work, as I don't know a hole lot about Enterprise Manager and SQL.

Btw, this is my error in Enterpr.Managr:

A connection could not be established to MyComputer\VSDOTNET2003

Reason: Cannot open default database. Login failed..

Please verify SQL server is running and check your SQL server registration prpoerties and try again

Pls tell me there is a way to fix this problem.This is because youruser account is set to use a specific default db which is missing.

Try using the SA account or other administrative account to login and change your personal user account to have a different default db.|||Thx, but need also step-by-step guiding on howto do this.

Pls help me here.

s|||Pls tell me in small steps at a time howto fix this.
My account is actually put up as an admin account in winxp. There is an ADMIN account i can log in with, but as I know, just in safemode.

What is the SA account in SQL server? How can I gain access to that account?
Do I use Enterprise Manager, or another tool or command line?
I am able to view the "startup" picture in Enter.Mangr. like I always could, however when trying to connect to MyComputer/VSDotNet2003 I ca't because of the deleted account.
How can I set new default account using Enterprise Manager? Is there any script I can use? What buttons do I use if it is possible to use E.M.?

As you all see, I am not really familiar with E.M., and therefor need very detailed information on howto fix this.

Pls in small steps.

s|||Logon to your computer as Administrator
Open Enterprise Manager
Expand until you can see the MyComputer/VSDotNet2003 node.
Expand that so you can see the "Security" folder.
Expand the folder so you can see the "Logins" node
Click "Logins", on the right you should see a list of permitted logins.
Right-click the login in question, and choose 'Properties'
On the "General" tab, choose a new default database from the list.

Cheers
Ken|||You mean like when I set a security setting for a newly created DB?
When I try to expand the MyComputer/VSDotNet2003 node, I run into the same problem. This action can't be performed due to lack of default DB, and I get the same error as mentioned before:

A connection could not be established to MyComputer\VSDOTNET2003

Reason: Cannot open default database. Login failed..

Please verify SQL server is running and check your SQL server registration prpoerties and try again

So how then can I set new default DB?

What does people think of this? Actually as I write this, I get a note that this works:

From MS Support (http://support.microsoft.com/default.aspx?scid=kb;en-us;307864&Product=sql):

If the user's default database no longer exists, or has been marked suspect, use the ISQL command line utility to change the user's default database to a database that is currently available for a connection.

1. At a command line prompt, type the following and then press ENTER: C:\>isql -E

2. At the isql prompt, type the following and then press ENTER: 1>sp_defaultdb 'user's_login', 'master'

3. At the second prompt, type the following and then press ENTER:2>go

Thx a lot to DarrelNorton for the respond, and all of you who took the time to helping a newbie.

Thx

slopee|||I'm glad you got the problem solved.

In the case of using Enteprise Manager, what I was suggesting is that you logon with a different user account (i.e. an another account that can access SQL Server), whose default database has *not* been deleted.

So, if you deleted the default database for User1, then logon as User2, and go into EM, and change the default database for User1. Then User1 should be able to login to SQL Server again.

Cheers
Ken

Deleted default database, now can't get in

Excuse my cross post but I wasn't sure which group was best.
I was cleaning up my SQL 2005 instance and deleted my default database
(obviously I forgot that i changed it to my default database), now I cannot
use the Studio Manager (it uses Windows Authentication), and I tried using
sqlcmd, but it says it won't accept remote connections, even though I set it
to accept them using the configuration manager.
Any one have any ideas of how I can rectify this situation? The only thing i
don't want to do, is lose the work i have already done on this, set up
replication and jobs, but if I have to I guess I'll have to.
Thanks,
NancyI'm not sure if the SQLCMD error about enabling remote connections is
related to the default database problem. Have you tried connecting with an
explicit database specification? For example:
sqlcmd -d master -E
Hope this helps.
Dan Guzman
SQL Server MVP
"Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
news:egQn3bHfHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Excuse my cross post but I wasn't sure which group was best.
> I was cleaning up my SQL 2005 instance and deleted my default database
> (obviously I forgot that i changed it to my default database), now I
> cannot use the Studio Manager (it uses Windows Authentication), and I
> tried using sqlcmd, but it says it won't accept remote connections, even
> though I set it to accept them using the configuration manager.
> Any one have any ideas of how I can rectify this situation? The only thing
> i don't want to do, is lose the work i have already done on this, set up
> replication and jobs, but if I have to I guess I'll have to.
> Thanks,
> Nancy
>|||"Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
news:egQn3bHfHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Excuse my cross post but I wasn't sure which group was best.
> I was cleaning up my SQL 2005 instance and deleted my default database
> (obviously I forgot that i changed it to my default database), now I
> cannot use the Studio Manager (it uses Windows Authentication), and I
> tried using sqlcmd, but it says it won't accept remote connections, even
> though I set it to accept them using the configuration manager.
>
Can you get in as a different login (say as an adminstrator)?
Problem is, with a default database, the login has no place to map to, so
for security purposes can't let you in.
> Any one have any ideas of how I can rectify this situation? The only thing
> i don't want to do, is lose the work i have already done on this, set up
> replication and jobs, but if I have to I guess I'll have to.
Well if you dropped the default database, about the only thing you can do is
restore from your backup.
Dropping the database deletes it and all the objects.
> Thanks,
> Nancy
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Thanks, but I tried that it doesn't work either, I should have been more
specific in my information, but thanks for the quick reply.
Nancy
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:47814D35-9C31-457F-A5BD-690CB4122E57@.microsoft.com...
> I'm not sure if the SQLCMD error about enabling remote connections is
> related to the default database problem. Have you tried connecting with
> an explicit database specification? For example:
> sqlcmd -d master -E
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
> news:egQn3bHfHHA.1388@.TK2MSFTNGP05.phx.gbl...
>> Excuse my cross post but I wasn't sure which group was best.
>> I was cleaning up my SQL 2005 instance and deleted my default database
>> (obviously I forgot that i changed it to my default database), now I
>> cannot use the Studio Manager (it uses Windows Authentication), and I
>> tried using sqlcmd, but it says it won't accept remote connections, even
>> though I set it to accept them using the configuration manager.
>> Any one have any ideas of how I can rectify this situation? The only
>> thing i don't want to do, is lose the work i have already done on this,
>> set up replication and jobs, but if I have to I guess I'll have to.
>> Thanks,
>> Nancy
>|||How to I restore if I can't get in, even using the Administrative connection
gets a remote connection not allowed error using sqlcmd?
Thanks, though,
Nancy
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:%23I5M%23hHfHHA.3956@.TK2MSFTNGP03.phx.gbl...
> "Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
> news:egQn3bHfHHA.1388@.TK2MSFTNGP05.phx.gbl...
>> Excuse my cross post but I wasn't sure which group was best.
>> I was cleaning up my SQL 2005 instance and deleted my default database
>> (obviously I forgot that i changed it to my default database), now I
>> cannot use the Studio Manager (it uses Windows Authentication), and I
>> tried using sqlcmd, but it says it won't accept remote connections, even
>> though I set it to accept them using the configuration manager.
> Can you get in as a different login (say as an adminstrator)?
> Problem is, with a default database, the login has no place to map to, so
> for security purposes can't let you in.
>> Any one have any ideas of how I can rectify this situation? The only
>> thing i don't want to do, is lose the work i have already done on this,
>> set up replication and jobs, but if I have to I guess I'll have to.
> Well if you dropped the default database, about the only thing you can do
> is restore from your backup.
> Dropping the database deletes it and all the objects.
>
>> Thanks,
>> Nancy
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>|||You seem to be working from your own workstation. Can you log directly onto
the SQL Server machine and run SQLCMD from there?
RLF
"Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
news:%23In76iHfHHA.2308@.TK2MSFTNGP06.phx.gbl...
> Thanks, but I tried that it doesn't work either, I should have been more
> specific in my information, but thanks for the quick reply.
> Nancy
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:47814D35-9C31-457F-A5BD-690CB4122E57@.microsoft.com...
>> I'm not sure if the SQLCMD error about enabling remote connections is
>> related to the default database problem. Have you tried connecting with
>> an explicit database specification? For example:
>> sqlcmd -d master -E
>>
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
>> news:egQn3bHfHHA.1388@.TK2MSFTNGP05.phx.gbl...
>> Excuse my cross post but I wasn't sure which group was best.
>> I was cleaning up my SQL 2005 instance and deleted my default database
>> (obviously I forgot that i changed it to my default database), now I
>> cannot use the Studio Manager (it uses Windows Authentication), and I
>> tried using sqlcmd, but it says it won't accept remote connections, even
>> though I set it to accept them using the configuration manager.
>> Any one have any ideas of how I can rectify this situation? The only
>> thing i don't want to do, is lose the work i have already done on this,
>> set up replication and jobs, but if I have to I guess I'll have to.
>> Thanks,
>> Nancy
>>
>|||Nancy Lytle (nancy_lytle@.payformance.com) writes:
> Thanks, but I tried that it doesn't work either, I should have been more
> specific in my information, but thanks for the quick reply.
What does "does not work" mean? Do you get an error message? In such case
what?
I just tried setting the default database for a login, and then I dropped
that database. When I logged in with
sqlcmd -U frits -P xxxxx -S .\NELJÄ
this failed with "Cannot open user default database." as expected.
However, when I tried:
sqlcmd -U frits -P xxxxx -S .\NELJÄ -d tempdb
I was able to get in.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||This instance is on my workstation.
Thanks,
Nancy
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:OBvqxnHfHHA.3648@.TK2MSFTNGP05.phx.gbl...
> You seem to be working from your own workstation. Can you log directly
> onto the SQL Server machine and run SQLCMD from there?
> RLF
> "Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
> news:%23In76iHfHHA.2308@.TK2MSFTNGP06.phx.gbl...
>> Thanks, but I tried that it doesn't work either, I should have been more
>> specific in my information, but thanks for the quick reply.
>> Nancy
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:47814D35-9C31-457F-A5BD-690CB4122E57@.microsoft.com...
>> I'm not sure if the SQLCMD error about enabling remote connections is
>> related to the default database problem. Have you tried connecting with
>> an explicit database specification? For example:
>> sqlcmd -d master -E
>>
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
>> news:egQn3bHfHHA.1388@.TK2MSFTNGP05.phx.gbl...
>> Excuse my cross post but I wasn't sure which group was best.
>> I was cleaning up my SQL 2005 instance and deleted my default database
>> (obviously I forgot that i changed it to my default database), now I
>> cannot use the Studio Manager (it uses Windows Authentication), and I
>> tried using sqlcmd, but it says it won't accept remote connections,
>> even though I set it to accept them using the configuration manager.
>> Any one have any ideas of how I can rectify this situation? The only
>> thing i don't want to do, is lose the work i have already done on this,
>> set up replication and jobs, but if I have to I guess I'll have to.
>> Thanks,
>> Nancy
>>
>>
>|||The server (my workstation is actively refusing the connection...may be
caused by the fact that by default SQL Server refuses remote connections - I
have set it up to accept remote connections.
This is the error I get when I tried your sqlcmd as well as the ones I tried
originally to get to the master db.
Nancy
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99108DEBF1DDYazorman@.127.0.0.1...
> Nancy Lytle (nancy_lytle@.payformance.com) writes:
>> Thanks, but I tried that it doesn't work either, I should have been more
>> specific in my information, but thanks for the quick reply.
> What does "does not work" mean? Do you get an error message? In such case
> what?
> I just tried setting the default database for a login, and then I dropped
> that database. When I logged in with
> sqlcmd -U frits -P xxxxx -S .\NELJÄ
> this failed with "Cannot open user default database." as expected.
> However, when I tried:
> sqlcmd -U frits -P xxxxx -S .\NELJÄ -d tempdb
> I was able to get in.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Just to let everyone know the outcome, after trying everything i could think
of , and that you all could suggest I almost gave up.
But first thing this morning I downloaded a trial of EMS SQL Manager for
SQL Server and installed it, and with it I could get into the instance and
change my default database back to master.
Thank goodness, that saved me a reinstall and hours of work,
Again, thank you all for your suggestions,
Nancy
"Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
news:egQn3bHfHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Excuse my cross post but I wasn't sure which group was best.
> I was cleaning up my SQL 2005 instance and deleted my default database
> (obviously I forgot that i changed it to my default database), now I
> cannot use the Studio Manager (it uses Windows Authentication), and I
> tried using sqlcmd, but it says it won't accept remote connections, even
> though I set it to accept them using the configuration manager.
> Any one have any ideas of how I can rectify this situation? The only thing
> i don't want to do, is lose the work i have already done on this, set up
> replication and jobs, but if I have to I guess I'll have to.
> Thanks,
> Nancy
>|||Nancy Lytle (nancy_lytle@.payformance.com) writes:
> The server (my workstation is actively refusing the connection...may be
> caused by the fact that by default SQL Server refuses remote connections
> - I have set it up to accept remote connections. This is the error I get
> when I tried your sqlcmd as well as the ones I tried originally to get
> to the master db.
Obviously the problem is something else than the missing default database.
If I understand your post correctly, the SQL Server is on your local
machine. If you then get the error "actively refused the connection",
I would recommend that you restart the SQL Server service, to see if
this helps.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Deleted default database, now can't get in

Deleted Default Database

I had two databases on my server, and I just deleted the one which SQL Server
has identified as the default database. Now I can't connect to the server
via SQL Enterprise Manager. How can I rectify this situation? The server
itself is not having a problem, I'm just not able to connect remotely.
Actually, EM is the source of the problem. Login via another program (e.g.,
query analyzer) and reset the default db to one that exists (using
sp_defaultdb).
"KitCaz" <KitCaz@.discussions.microsoft.com> wrote in message
news:8F218C95-680F-4656-8AF9-354EA23E0694@.microsoft.com...
> I had two databases on my server, and I just deleted the one which SQL
Server
> has identified as the default database. Now I can't connect to the server
> via SQL Enterprise Manager. How can I rectify this situation? The server
> itself is not having a problem, I'm just not able to connect remotely.
|||Great, thanks! I see now it was a user ID thing.
"Scott Morris" wrote:

> Actually, EM is the source of the problem. Login via another program (e.g.,
> query analyzer) and reset the default db to one that exists (using
> sp_defaultdb).
> "KitCaz" <KitCaz@.discussions.microsoft.com> wrote in message
> news:8F218C95-680F-4656-8AF9-354EA23E0694@.microsoft.com...
> Server
>
>

Deleted Default Database

I had two databases on my server, and I just deleted the one which SQL Serve
r
has identified as the default database. Now I can't connect to the server
via SQL Enterprise Manager. How can I rectify this situation? The server
itself is not having a problem, I'm just not able to connect remotely.Actually, EM is the source of the problem. Login via another program (e.g.,
query analyzer) and reset the default db to one that exists (using
sp_defaultdb).
"KitCaz" <KitCaz@.discussions.microsoft.com> wrote in message
news:8F218C95-680F-4656-8AF9-354EA23E0694@.microsoft.com...
> I had two databases on my server, and I just deleted the one which SQL
Server
> has identified as the default database. Now I can't connect to the server
> via SQL Enterprise Manager. How can I rectify this situation? The server
> itself is not having a problem, I'm just not able to connect remotely.|||Great, thanks! I see now it was a user ID thing.
"Scott Morris" wrote:

> Actually, EM is the source of the problem. Login via another program (e.g
.,
> query analyzer) and reset the default db to one that exists (using
> sp_defaultdb).
> "KitCaz" <KitCaz@.discussions.microsoft.com> wrote in message
> news:8F218C95-680F-4656-8AF9-354EA23E0694@.microsoft.com...
> Server
>
>

Wednesday, March 7, 2012

Delete/truncate table ignoring contraints

Is there any easy way to truncate a table which has a foreign key restraint? I want to override the default behavior which is to not allow truncate of parent tables. I want to be able to temperarily remove the contraint so I can truncate the temple, how do you do this?

I should add that the systables keep track of the contraints. There should be a query that I could run that would just disable the checking of the contraint? Any help?|||No. There is no way to control/alter the behavior of truncate table. If you need to use it you have to drop the FK constraints even if the table is empty.|||

O.K. So... that isn't easily done.

Is there any easy way to simply copy the schema, contraints and everything but simply no data? Perhaps using DTS?

Or perhaps, is there another way to reset the identity information of a table? The delete operator does not reset identity.

|||If you want to reset identity value you can use DBCC CHECKIDENT. See Books Online for more details. Your original question was different.||| your original question was different

:) Thanks.

Wednesday, March 21, 2012

deleting an instance of SQL Server

deleting an instance of SQL Server

Monday, March 19, 2012

Deleting a column DEFAULT....

Friday, March 9, 2012

Deleted Default DB, Need Hlp Fast!

Deleted default database, now can't get in

Deleted default database, now can't get in

Deleted default database, now can't get in

Deleted Default Database

Deleted Default Database

Wednesday, March 7, 2012

Delete/truncate table ignoring contraints

DENY permission,SQL server

Blog Archive

About Me