Delete Database

A database disappeared from one of our qa servers last night yet when we looked at the logs no record of the drop datbase command was to be found like wise no record of create database.
Can any one tell me where we can look in sqlserver or on the windows 2000 server fro a record of these eventsYikes! I'd never really looked at my SQL Error log files when I dropped a database, but it appears that there is no entry for this event in there. I dunno where to tell you to go from here...

I would suggest that you audit your security settings, remove anyone from the sysadmin group that doesn't absolutely positively have to be there and for sure set up to audit failed logins.

Regards,

hmscott|||Might try the event logs on the server. At least you'll have an idea who was doing what and when.|||Thanks for the advice fortunately it was a dev server so no real damange and we could restore it.
Lokks like there may be a case to go to microsoft and see if it could be added .

DELETE certain rows

I'm running SQL 2000, is there a way to delete rows 150 through 210 in a
table? I'm familiar with the DELETE command but as far as I know it can
only delete one row at a time. How does one delete multiple rows?
Thanks.
RussI found my answer, you can delete multiple rows with the DELETE command.
Russ
"Russ Hromyko" <rhromyko@.verizon.net> wrote in message
news:TWPXi.1645$It.97@.trndny06...
> I'm running SQL 2000, is there a way to delete rows 150 through 210 in a
> table? I'm familiar with the DELETE command but as far as I know it can
> only delete one row at a time. How does one delete multiple rows?
> Thanks.
> Russ
>|||Yes, as you found you can delete rows between 150 and 210 using DELETE.
For example = DELETE FROM Users where [value] >= 150 and [value] <= 210
--
Ekrem Önsoy
"Russ Hromyko" <rhromyko@.verizon.net> wrote in message
news:DGTXi.1664$It.1389@.trndny06...
>I found my answer, you can delete multiple rows with the DELETE command.
> Russ
>
> "Russ Hromyko" <rhromyko@.verizon.net> wrote in message
> news:TWPXi.1645$It.97@.trndny06...
>> I'm running SQL 2000, is there a way to delete rows 150 through 210 in a
>> table? I'm familiar with the DELETE command but as far as I know it can
>> only delete one row at a time. How does one delete multiple rows?
>> Thanks.
>> Russ
>

Delete all data from single column question

Does anyone know the correct syntax for using the DELETE command to completly
delete/remove all data from a single column within a given table only? I do
not want to save the data from column A. I would like to have the column
left in place with no data. Thanks for any info.
"brettr78" <brettr78@.discussions.microsoft.com> wrote in message
news:DDF0D6CB-CABF-49E8-A48A-DF6D0C4C62FE@.microsoft.com...
> Does anyone know the correct syntax for using the DELETE command to
completly
> delete/remove all data from a single column within a given table only? I
do
> not want to save the data from column A. I would like to have the column
> left in place with no data. Thanks for any info.
Depending on the datatype, something like:
UPDATE Foo
SET column_x = NULL
Rick

Delete all data from single column question

Does anyone know the correct syntax for using the DELETE command to completly
delete/remove all data from a single column within a given table only? I do
not want to save the data from column A. I would like to have the column
left in place with no data. Thanks for any info."brettr78" <brettr78@.discussions.microsoft.com> wrote in message
news:DDF0D6CB-CABF-49E8-A48A-DF6D0C4C62FE@.microsoft.com...
> Does anyone know the correct syntax for using the DELETE command to
completly
> delete/remove all data from a single column within a given table only? I
do
> not want to save the data from column A. I would like to have the column
> left in place with no data. Thanks for any info.
Depending on the datatype, something like:
UPDATE Foo
SET column_x = NULL
Rick

Delete all data from single column question

Does anyone know the correct syntax for using the DELETE command to completl
y
delete/remove all data from a single column within a given table only? I do
not want to save the data from column A. I would like to have the column
left in place with no data. Thanks for any info."brettr78" <brettr78@.discussions.microsoft.com> wrote in message
news:DDF0D6CB-CABF-49E8-A48A-DF6D0C4C62FE@.microsoft.com...
> Does anyone know the correct syntax for using the DELETE command to
completly
> delete/remove all data from a single column within a given table only? I
do
> not want to save the data from column A. I would like to have the column
> left in place with no data. Thanks for any info.
Depending on the datatype, something like:
UPDATE Foo
SET column_x = NULL
Rick

Delay command execution

Hi
Here is the setting: Clients connect to SQL Server 2000 via ADO.
Here is the problem: After a client's successful login, the SQL Server 2000
should delay the processing of the client-commands by, e.g., 1 sec. How do I
tell it to the SQL Server 2000?
Thanks in advance.
Adrianat the sql server side
WAITFOR DELAY '00:00:01'
But why would you need it. You can as well have the delay at the client side
.
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Thank you.
Well, I know this command. But where precisely do I tell the SQL Server to
delay an incoming command?
A delay at the client-side would be fine as well. But now, how do I tell it
to the client (3rd-party, no source code)?
Basically, I need to delay only the first command of a client immediately
after the login, so that I can run a "login-script" for the client on the
server.
Adrian
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:669DA5C9-52D0-4AA4-B489-2E041F14F7DE@.microsoft.com...
> at the sql server side
> WAITFOR DELAY '00:00:01'
> But why would you need it. You can as well have the delay at the client
> side.
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>|||> Well, I know this command. But where precisely do I tell the SQL Server to delay an incom
ing
> command?
There's no such setting in SQL Server.

> A delay at the client-side would be fine as well. But now, how do I tell i
t to the client
> (3rd-party, no source code)?
You would have to talk to the 3:rd party vendor about this...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Adrian" <adrian@.iai.uni-bonn.de> wrote in message news:eYlmvehjGHA.4344@.TK2MSFTNGP05.phx.g
bl...
> Thank you.
> Well, I know this command. But where precisely do I tell the SQL Server to
delay an incoming
> command?
> A delay at the client-side would be fine as well. But now, how do I tell i
t to the client
> (3rd-party, no source code)?
> Basically, I need to delay only the first command of a client immediately
after the login, so that
> I can run a "login-script" for the client on the server.
> Adrian
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:669DA5C9-52D0-4AA4-B489-2E041F14F7DE@.microsoft.com...
>|||That's bad news.
But...
I monitor login-events with a trace and process the events in a trigger,
which is attached to the trace-table. I need to keep the client waiting
until the trigger finishes. Is there any other way to do this?
Adrian
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ez$y2shjGHA.4884@.TK2MSFTNGP03.phx.gbl...
> There's no such setting in SQL Server.
>
> You would have to talk to the 3:rd party vendor about this...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Adrian" <adrian@.iai.uni-bonn.de> wrote in message
> news:eYlmvehjGHA.4344@.TK2MSFTNGP05.phx.gbl...
>|||Adrian (adrian@.iai.uni-bonn.de) writes:
> That's bad news.
> But...
> I monitor login-events with a trace and process the events in a trigger,
> which is attached to the trace-table. I need to keep the client waiting
> until the trigger finishes. Is there any other way to do this?
Wait, this sounds dangerous. OK, I don't know the architecture of
this particular 3rd party tool. But most modern applications these
days opens a connection, submits a query or two and then close the
connection. Or rather, that is how the application code looks like.
Under the hood, the client API maintains a connection pool, so that
if the application reconnects soon enough, a connection will be
reused.
Nevertheless, a one-second delay on each login sounds like a bad idea
to me.
Maybe if you explain in more detail what you are trying on achieve and
why, we may come with suggestions.
Don't forget to tell which version of SQL Server you are using.
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.mspxsql

Defrag Indexes

Sorry for the newbie question but can't seem to find a clear anwser...
Is there a command in SQL 2000 to Defrag all Indexes?
ThanksYes! - Look at the DBCC INDEXDEFRAG command in BOL.
Immy
"M Swancott" <swancott@.metrocast.net> wrote in message
news:OSP6VeHtFHA.2912@.TK2MSFTNGP09.phx.gbl...
> Sorry for the newbie question but can't seem to find a clear anwser...
> Is there a command in SQL 2000 to Defrag all Indexes?
> Thanks
>|||You probably don't need to defrag all your indexes. Read the MS whitepaper
below for more details about fragmentation and when/how to remove it.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Thanks
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Imtiaz Ullah" <imtiaz_ullah@.hotmail.com> wrote in message
news:uf5Z7gHtFHA.3604@.tk2msftngp13.phx.gbl...
> Yes! - Look at the DBCC INDEXDEFRAG command in BOL.
> Immy
> "M Swancott" <swancott@.metrocast.net> wrote in message
> news:OSP6VeHtFHA.2912@.TK2MSFTNGP09.phx.gbl...
>> Sorry for the newbie question but can't seem to find a clear anwser...
>> Is there a command in SQL 2000 to Defrag all Indexes?
>> Thanks
>>
>

Defrag Indexes

Sorry for the newbie question but can't seem to find a clear anwser...
Is there a command in SQL 2000 to Defrag all Indexes?
Thanks
Yes! - Look at the DBCC INDEXDEFRAG command in BOL.
Immy
"M Swancott" <swancott@.metrocast.net> wrote in message
news:OSP6VeHtFHA.2912@.TK2MSFTNGP09.phx.gbl...
> Sorry for the newbie question but can't seem to find a clear anwser...
> Is there a command in SQL 2000 to Defrag all Indexes?
> Thanks
>
|||You probably don't need to defrag all your indexes. Read the MS whitepaper
below for more details about fragmentation and when/how to remove it.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Thanks
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Imtiaz Ullah" <imtiaz_ullah@.hotmail.com> wrote in message
news:uf5Z7gHtFHA.3604@.tk2msftngp13.phx.gbl...
> Yes! - Look at the DBCC INDEXDEFRAG command in BOL.
> Immy
> "M Swancott" <swancott@.metrocast.net> wrote in message
> news:OSP6VeHtFHA.2912@.TK2MSFTNGP09.phx.gbl...
>

Defrag Indexes

Sorry for the newbie question but can't seem to find a clear anwser...
Is there a command in SQL 2000 to Defrag all Indexes?
ThanksYes! - Look at the DBCC INDEXDEFRAG command in BOL.
Immy
"M Swancott" <swancott@.metrocast.net> wrote in message
news:OSP6VeHtFHA.2912@.TK2MSFTNGP09.phx.gbl...
> Sorry for the newbie question but can't seem to find a clear anwser...
> Is there a command in SQL 2000 to Defrag all Indexes?
> Thanks
>|||You probably don't need to defrag all your indexes. Read the MS whitepaper
below for more details about fragmentation and when/how to remove it.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Thanks
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Imtiaz Ullah" <imtiaz_ullah@.hotmail.com> wrote in message
news:uf5Z7gHtFHA.3604@.tk2msftngp13.phx.gbl...
> Yes! - Look at the DBCC INDEXDEFRAG command in BOL.
> Immy
> "M Swancott" <swancott@.metrocast.net> wrote in message
> news:OSP6VeHtFHA.2912@.TK2MSFTNGP09.phx.gbl...
>

defining command,commandtype and connectionstring for SELECT command is not similar to INS

i am using visual web developer 2005 and SQL 2005 with VB as the code behind

i am usingINSERT command like this

Dim test As New SqlDataSource()
test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString()
test.InsertCommandType = SqlDataSourceCommandType.Text
test.InsertCommand = "INSERT INTO try (roll,name, age, email) VALUES (@.roll,@.name, @.age, @.email) "
test.InsertParameters.Add("roll", TextBox1.Text)
test.InsertParameters.Add("name", TextBox2.Text)
test.InsertParameters.Add("age", TextBox3.Text)
test.InsertParameters.Add("email", TextBox4.Text)
test.Insert()

i am usingUPDATE command like this

Dim test As New SqlDataSource()
test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString()
test.UpdateCommandType = SqlDataSourceCommandType.Text
test.UpdateCommand = "UPDATE try SET name = '" + myname + "' , age = '" + myage + "' , email = '" + myemail + "' WHERE roll 123 "
test.Update()

but i have to use theSELECT command like this which is completely different from INSERT and UPDATE commands

Dim tblData As New Data.DataTable()
Dim conn As New Data.SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True")
Dim Command As New Data.SqlClient.SqlCommand("SELECT * FROM try WHERE age = '100' ", conn)
Dim da As New Data.SqlClient.SqlDataAdapter(Command)
da.Fill(tblData)
conn.Close()

TextBox4.Text = tblData.Rows(1).Item("name").ToString()
TextBox5.Text = tblData.Rows(1).Item("age").ToString()
TextBox6.Text = tblData.Rows(1).Item("email").ToString()

for INSERT and UPDATE commands defining the command,commandtype and connectionstring is same

but for the SELECT command it is completely different. why ?

can i define the command,commandtype and connectionstring for SELECT command similar to INSERT and UPDATE ?

if its possible how to do ?

please help me

Hi you can use:

Insert:

Dim

conAsNew SqlConnection("server=.;database=test;uid=sa")Dim cmdAsNew SqlCommand

con.Open()

cmd.Connection =con

cmd.CommandType =Data.CommandType.Text

cmd.CommandText ="insert into t1(name,birth) values (@.n,@.b)"

cmd.Parameters.Add(

New SqlParameter("@.n", Data.SqlDbType.NVarChar)).Value =TextBox1.Text

cmd.Parameters.Add(

New SqlParameter("@.b", Data.SqlDbType.DateTime)).Value =TextBox2.Text

cmd.ExecuteNonQuery()

con.Close()

Update:

Dim conAsNew SqlConnection("server=.;database=test;uid=sa")

Dim cmdAsNew SqlCommand

con.Open()

cmd.Connection =con

cmd.CommandType =Data.CommandType.Text

cmd.CommandText ="updae t1 setname= @.n , birth= @.b)"

cmd.Parameters.Add(

New SqlParameter("@.n", Data.SqlDbType.NVarChar)).Value =TextBox1.Text

cmd.Parameters.Add(

New SqlParameter("@.b", Data.SqlDbType.DateTime)).Value =TextBox2.Text

cmd.ExecuteNonQuery()

con.Close()

Select:

Dim conAsNew SqlConnection("server=.;database=test;uid=sa")

Dim cmdAsNew SqlCommand

con.Open()

cmd.Connection =con

cmd.CommandType =Data.CommandType.Text

cmd.CommandText ="updae t1 setname= @.n , birth= @.b)"

Dim da As New Data.SqlClient.SqlDataAdapter(Command)
da.Fill(tblData)

con.Close()

|||Sorry the third query is "select .........." not "update ...."

Defining a Select Command

I'm trying to populate a DropDownList from my SQL database. I'm using C# 2005 and when I compile my code I get an error.Compiler Error Message:CS0103: The name 'myConnection' does not exist in the current context

using

System;

using

System.Data;

using

System.Data.SqlClient;

using

System.Configuration;

using

System.Collections;

using

System.Web;

using

System.Web.Security;

using

System.Web.UI;

using

System.Web.UI.WebControls;

using

System.Web.UI.WebControls.WebParts;

using

System.Web.UI.HtmlControls;

public

partialclassDefault3 : System.Web.UI.Page

{

privatestring connectionString = WebConfigurationManager.ConnectionStrings["mewconsultingConnectionString"].ConnectionString;protectedvoid Page_Load(object sender,EventArgs e)

{

SqlCommand myCommand =newSqlCommand();

myCommand.Connection = myConnection;

myCommand.CommandText =

"SELECT * FROM tblPau";

myConnection.Open();

SqlDataReader myReader;

myReader = myCommand.ExecuteReader();

myReader.Read();

// The first row in the result set is now available.

lstPau.Items.Add(myReader[

"PauSiteName"]);

myReader.Close();

myConnection.Close();

}

}

Exactly as the error stated: in your code, you did not delcare myConnection.

You need something like this before you reference it:

SqlConnection

myConnection =newSqlConnection(connectionString);

default result set semantics

When an ADO command does not exhaust the default result set and a second
command is executed, the sqloledb provider automatically spawns a new session
to execute the second command (using the default result set). My question is
since command2 was definied on the connection (and prepared = true), should
one be allowed to rebind a paramter on command2 and execute again? Doing so
results in the following error -
"Multiple-step OLD DB operator generated errors. Check each OLE DB status
value, if available. No work was done."
pseudo code example:
cmd1.execute
while not rs1.eof
obtain row value, bind into cmd2
cmd2.execute
First execute works, second iteration fails.
The solution is to shutdown cmd2 within the loop and recreate the command
for each execution.
Is this the expected behavior because of the inconsistent state for the
command (having been spawned to a new session)?
I know 2005 solves this issues w/ MARS.
Thanks.
Correct. MARS in yukon is designed to solve this.
http://msdn.microsoft.com/library/en...asp?frame=true
-oj
"Thomas Brown" <ThomasBrown@.discussions.microsoft.com> wrote in message
news:B495E631-3D88-4880-B678-6E7F6DF9749C@.microsoft.com...
> When an ADO command does not exhaust the default result set and a second
> command is executed, the sqloledb provider automatically spawns a new
> session
> to execute the second command (using the default result set). My question
> is
> since command2 was definied on the connection (and prepared = true),
> should
> one be allowed to rebind a paramter on command2 and execute again? Doing
> so
> results in the following error -
> "Multiple-step OLD DB operator generated errors. Check each OLE DB status
> value, if available. No work was done."
> pseudo code example:
> cmd1.execute
> while not rs1.eof
> obtain row value, bind into cmd2
> cmd2.execute
> First execute works, second iteration fails.
> The solution is to shutdown cmd2 within the loop and recreate the command
> for each execution.
> Is this the expected behavior because of the inconsistent state for the
> command (having been spawned to a new session)?
> I know 2005 solves this issues w/ MARS.
> Thanks.
>

default result set semantics

When an ADO command does not exhaust the default result set and a second
command is executed, the sqloledb provider automatically spawns a new session
to execute the second command (using the default result set). My question is
since command2 was definied on the connection (and prepared = true), should
one be allowed to rebind a paramter on command2 and execute again? Doing so
results in the following error -
"Multiple-step OLD DB operator generated errors. Check each OLE DB status
value, if available. No work was done."
pseudo code example:
cmd1.execute
while not rs1.eof
obtain row value, bind into cmd2
cmd2.execute
First execute works, second iteration fails.
The solution is to shutdown cmd2 within the loop and recreate the command
for each execution.
Is this the expected behavior because of the inconsistent state for the
command (having been spawned to a new session)?
I know 2005 solves this issues w/ MARS.
Thanks.Correct. MARS in yukon is designed to solve this.
http://msdn.microsoft.com/library/en-us/dnsql90/html/MARSinSQL05.asp?frame=true
--
-oj
"Thomas Brown" <ThomasBrown@.discussions.microsoft.com> wrote in message
news:B495E631-3D88-4880-B678-6E7F6DF9749C@.microsoft.com...
> When an ADO command does not exhaust the default result set and a second
> command is executed, the sqloledb provider automatically spawns a new
> session
> to execute the second command (using the default result set). My question
> is
> since command2 was definied on the connection (and prepared = true),
> should
> one be allowed to rebind a paramter on command2 and execute again? Doing
> so
> results in the following error -
> "Multiple-step OLD DB operator generated errors. Check each OLE DB status
> value, if available. No work was done."
> pseudo code example:
> cmd1.execute
> while not rs1.eof
> obtain row value, bind into cmd2
> cmd2.execute
> First execute works, second iteration fails.
> The solution is to shutdown cmd2 within the loop and recreate the command
> for each execution.
> Is this the expected behavior because of the inconsistent state for the
> command (having been spawned to a new session)?
> I know 2005 solves this issues w/ MARS.
> Thanks.
>

default result set semantics

When an ADO command does not exhaust the default result set and a second
command is executed, the sqloledb provider automatically spawns a new sessio
n
to execute the second command (using the default result set). My question is
since command2 was definied on the connection (and prepared = true), should
one be allowed to rebind a paramter on command2 and execute again? Doing so
results in the following error -
"Multiple-step OLD DB operator generated errors. Check each OLE DB status
value, if available. No work was done."
pseudo code example:
cmd1.execute
while not rs1.eof
obtain row value, bind into cmd2
cmd2.execute
First execute works, second iteration fails.
The solution is to shutdown cmd2 within the loop and recreate the command
for each execution.
Is this the expected behavior because of the inconsistent state for the
command (having been spawned to a new session)?
I know 2005 solves this issues w/ MARS.
Thanks.Correct. MARS in yukon is designed to solve this.
[url]http://msdn.microsoft.com/library/en-us/dnsql90/html/MARSinSQL05.asp?frame=true[/u
rl]
-oj
"Thomas Brown" <ThomasBrown@.discussions.microsoft.com> wrote in message
news:B495E631-3D88-4880-B678-6E7F6DF9749C@.microsoft.com...
> When an ADO command does not exhaust the default result set and a second
> command is executed, the sqloledb provider automatically spawns a new
> session
> to execute the second command (using the default result set). My question
> is
> since command2 was definied on the connection (and prepared = true),
> should
> one be allowed to rebind a paramter on command2 and execute again? Doing
> so
> results in the following error -
> "Multiple-step OLD DB operator generated errors. Check each OLE DB status
> value, if available. No work was done."
> pseudo code example:
> cmd1.execute
> while not rs1.eof
> obtain row value, bind into cmd2
> cmd2.execute
> First execute works, second iteration fails.
> The solution is to shutdown cmd2 within the loop and recreate the command
> for each execution.
> Is this the expected behavior because of the inconsistent state for the
> command (having been spawned to a new session)?
> I know 2005 solves this issues w/ MARS.
> Thanks.
>