Determine sequence in log

In an AUDIT_LOG table, with approximately 1.6 million rows, would there be
any way to extract a pattern of rows? Specifically, I am trying to select al
l
records that have an audit_log_id of '13' which would be immediately followe
d
by a record with an audit_log_id of '16', which in turn would be immediately
followed by a record with an audit_log_id of '2', and display the results in
these groups of three records?
Thanks for any help.
SteveCan you post some ddl, sample data and expected result?
Please provide DDL and sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"Steve B" wrote:

> In an AUDIT_LOG table, with approximately 1.6 million rows, would there be
> any way to extract a pattern of rows? Specifically, I am trying to select
all
> records that have an audit_log_id of '13' which would be immediately follo
wed
> by a record with an audit_log_id of '16', which in turn would be immediate
ly
> followed by a record with an audit_log_id of '2', and display the results
in
> these groups of three records?
> Thanks for any help.
> Steve|||Only if there is some field, IN THE TABLE, that allows you to "determine" ro
w
sequence. I mean if there exists a ccolun, with unique values, which when
sorted, will sequence the rows in the order in which "immediately following
"
has the meaning you want it to have. If that's so, let's say that column i
s
named <LogDate>.
You have to join the table to itself, where, for each record in the first
instance of the table is "Joined" to it's Immediate Follower, by "Joining"
based on the value of LogDate being equal to the Minimum value of all the
records with LogDate > Than this records LogDate...
Select Prev.*, Next.*, Last.*
From AUDIT_LOG First
Join AUDIT_LOG Mid On
Mid.LogDate = (Select Min(LogDate) From AUDIT_LOG
Where LogDate > First.LogDate)
Join AUDIT_LOG Last On
Last .LogDate = (Select Min(LogDate) From AUDIT_LOG
Where LogDate > Mid.LogDate)
Where First.audit_log_id = 13
And Mid.audit_log_id = 16
And Last.audit_log_id = 2
"Steve B" wrote:

> In an AUDIT_LOG table, with approximately 1.6 million rows, would there be
> any way to extract a pattern of rows? Specifically, I am trying to select
all
> records that have an audit_log_id of '13' which would be immediately follo
wed
> by a record with an audit_log_id of '16', which in turn would be immediate
ly
> followed by a record with an audit_log_id of '2', and display the results
in
> these groups of three records?
> Thanks for any help.
> Steve|||Thanks Alejandro.
Below is an example. I would like to return the sets of rows that are 13,
16, 2. THere are three examples in this table.
AL_EVENT_ID AL_DATETIME
2 9/24/04 9:16 AM
13 9/24/04 9:51 AM
2 9/24/04 10:21 AM
13 9/24/04 10:39 AM
13 9/24/04 1:17 PM
2 9/24/04 1:23 PM
13 9/24/04 2:05 PM
2 9/24/04 2:40 PM
2 9/24/04 2:43 PM
13 9/24/04 2:56 PM
2 9/29/04 8:13 AM
2 9/29/04 4:11 PM
2 9/30/04 8:32 AM
16 10/1/04 11:38 AM
13 10/1/04 12:28 PM
16 10/1/04 12:29 PM
2 10/1/04 12:29 PM
2 10/1/04 2:30 PM
2 10/1/04 2:46 PM
2 10/1/04 3:04 PM
13 10/1/04 4:40 PM
2 10/1/04 4:44 PM
13 10/1/04 4:58 PM
2 10/4/04 9:42 AM
13 10/4/04 3:23 PM
16 10/4/04 3:23 PM
2 10/4/04 3:23 PM
2 10/5/04 4:56 PM
2 10/6/04 9:35 AM
13 10/6/04 11:52 AM
13 10/6/04 12:47 PM
16 10/6/04 12:49 PM
2 10/6/04 12:49 PM
2 10/6/04 1:32 PM
13 10/6/04 4:06 PM
2 10/7/04 8:51 AM
2 10/7/04 11:39 AM
13 10/7/04 11:49 AM
"Alejandro Mesa" wrote:
> Can you post some ddl, sample data and expected result?
> Please provide DDL and sample data.
> http://www.aspfaq.com/etiquette.asp?id=5006
>
> AMB
>
> "Steve B" wrote:
>|||Thanks very much. I'm sure this is what I need. Not quite clear though on th
e
Prev.*, Next.*, and Last.*. It returns Msg 107 - column prefix doesn't match
.
I have added some example data to Alejandro's post.
Steve
"CBretana" wrote:
> Only if there is some field, IN THE TABLE, that allows you to "determine"
row
> sequence. I mean if there exists a ccolun, with unique values, which when
> sorted, will sequence the rows in the order in which "immediately followi
ng"
> has the meaning you want it to have. If that's so, let's say that column
is
> named <LogDate>.
> You have to join the table to itself, where, for each record in the first
> instance of the table is "Joined" to it's Immediate Follower, by "Joining"
> based on the value of LogDate being equal to the Minimum value of all the
> records with LogDate > Than this records LogDate...
> Select Prev.*, Next.*, Last.*
> From AUDIT_LOG First
> Join AUDIT_LOG Mid On
> Mid.LogDate = (Select Min(LogDate) From AUDIT_LOG
> Where LogDate > First.LogDate)
> Join AUDIT_LOG Last On
> Last .LogDate = (Select Min(LogDate) From AUDIT_LOG
> Where LogDate > Mid.LogDate)
> Where First.audit_log_id = 13
> And Mid.audit_log_id = 16
> And Last.audit_log_id = 2
>
>
> "Steve B" wrote:
>|||Sorry they were table aliases in my first incantation, and neglected to
change them... They should be First, Mid, and Last
The SQL should be
Select First.*, Mid.*, Last.*
From AUDIT_LOG First
Join AUDIT_LOG Mid On
Mid.LogDate = (Select Min(LogDate) From AUDIT_LOG
Where LogDate > First.LogDate)
Join AUDIT_LOG Last On
Last .LogDate = (Select Min(LogDate) From AUDIT_LOG
Where LogDate > Mid.LogDate)
Where First.audit_log_id = 13
And Mid.audit_log_id = 16
And Last.audit_log_id = 2
"Steve B" wrote:
> Thanks very much. I'm sure this is what I need. Not quite clear though on
the
> Prev.*, Next.*, and Last.*. It returns Msg 107 - column prefix doesn't mat
ch.
> I have added some example data to Alejandro's post.
> Steve
> "CBretana" wrote:
>|||Using your actual column name,
Select First.*, Mid.*, Last.*
From AUDIT_LOG First
Join AUDIT_LOG Mid On
Mid.AL_DATETIME =
(Select Min(AL_DATETIME) From AUDIT_LOG
Where AL_DATETIME > First.AL_DATETIME)
Join AUDIT_LOG Last On
Last.AL_DATETIME =
(Select Min(AL_DATETIME) From AUDIT_LOG
Where AL_DATETIME > Mid.AL_DATETIME)
Where First.audit_log_id = 13
And Mid.audit_log_id = 16
And Last.audit_log_id = 2
"Steve B" wrote:
> Thanks very much. I'm sure this is what I need. Not quite clear though on
the
> Prev.*, Next.*, and Last.*. It returns Msg 107 - column prefix doesn't mat
ch.
> I have added some example data to Alejandro's post.
> Steve
> "CBretana" wrote:
>|||Thank once again for your help. It works like a charm
Steve
"CBretana" wrote:
> Using your actual column name,
> Select First.*, Mid.*, Last.*
> From AUDIT_LOG First
> Join AUDIT_LOG Mid On
> Mid.AL_DATETIME =
> (Select Min(AL_DATETIME) From AUDIT_LOG
> Where AL_DATETIME > First.AL_DATETIME)
> Join AUDIT_LOG Last On
> Last.AL_DATETIME =
> (Select Min(AL_DATETIME) From AUDIT_LOG
> Where AL_DATETIME > Mid.AL_DATETIME)
> Where First.audit_log_id = 13
> And Mid.audit_log_id = 16
> And Last.audit_log_id = 2
> "Steve B" wrote:
>|||Yr very welcome!
"Steve B" wrote:
> Thank once again for your help. It works like a charm
> Steve
> "CBretana" wrote:
>

determine if value in field is number

I have a varchar field in a table that could contain values such as 123,
345, ABC, or CDF
I want to do a select statement on the table to return records where this
field could be number (123, 345) and exclude those that are not (ABC, CDF).
Is there an IsNum function or something like that?
hi matt,
make use of isnumeric function, which returns 1 for valid numeric value.
ex:
select *
from
<table>
where isnumeric(col) = 1
Vishal Parkar
vgparkar@.yahoo.co.in

Determine fastest query in Query Analyzer

I am trying to determine which of three stored procedure designs are
fastest in the Query Analyzer:

One query is a straight SELECT query with all desired rows and a dozen
(tblName.RowName = @.param or @.param = Null) filters in the WHERE
statement.

One query populates a #Temp table with the UniqueIDs from the results
of the SELECT query in the above example, then joins that #Temp table
to get the desired rows.

One query users EXEC sp_executesql @.sql, @.paramlist, @.param
in which the @.param has the dozen filters.

What I'm trying to determine is which is the fastest.

Each time I run the query in Query Analyzer it returns the same
recordset (duh!) but with much different Time Statistics.

Are the Time Statisticts THE HOLY QRAIL as far as determining which is
fastest, and what so I want to look at, the Vale or the Average? I
notice there are different numbers of bytse sen and bytes received for
each of the three queries.

Any illumination on this is appreciated.
lqHi

You are looking at the client statistic! The topic "Query Window Statistics
Pane" in books online explains their values.

Time is a good indicator of performance, for instance if there are more
network round trips this should be noticed in the time taken. You may also
want to consider the number of reads/writes which may give some indication
of how well it will perform when the system in under a load. These can be
viewed using SQL profiler.

Expect the first time you run a query to take longer than subsequent times,
if your query is cached subsequent executions may be significantly faster.
Use DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS to clear the procedure
cache and buffer pool.

John

"laurenq uantrell" <laurenquantrell@.hotmail.com> wrote in message
news:1126976566.998344.13050@.g44g2000cwa.googlegro ups.com...
>I am trying to determine which of three stored procedure designs are
> fastest in the Query Analyzer:
> One query is a straight SELECT query with all desired rows and a dozen
> (tblName.RowName = @.param or @.param = Null) filters in the WHERE
> statement.
> One query populates a #Temp table with the UniqueIDs from the results
> of the SELECT query in the above example, then joins that #Temp table
> to get the desired rows.
> One query users EXEC sp_executesql @.sql, @.paramlist, @.param
> in which the @.param has the dozen filters.
> What I'm trying to determine is which is the fastest.
> Each time I run the query in Query Analyzer it returns the same
> recordset (duh!) but with much different Time Statistics.
> Are the Time Statisticts THE HOLY QRAIL as far as determining which is
> fastest, and what so I want to look at, the Vale or the Average? I
> notice there are different numbers of bytse sen and bytes received for
> each of the three queries.
> Any illumination on this is appreciated.
> lq|||Have a look at showplan whick give you an idea what the database is
doing to resolve your queries. Determining the fastest method can be
difficult especially with changing volumes of data, add or remove an
index will effect the results (faster or slower) so experiment a bit.

Sorry I cannot be more help

duncan|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> I am trying to determine which of three stored procedure designs are
> fastest in the Query Analyzer:
> One query is a straight SELECT query with all desired rows and a dozen
> (tblName.RowName = @.param or @.param = Null) filters in the WHERE
> statement.

@.param = Null?

Remember that NULL is never equal to anything, not an even another NULL.
NULL is an unknown value, and two nulls may be two different values.
Use "@.param IS NULL" instead.

> Are the Time Statisticts THE HOLY QRAIL as far as determining which is
> fastest, and what so I want to look at, the Vale or the Average? I
> notice there are different numbers of bytse sen and bytes received for
> each of the three queries.

When I need to benchmark queries I usually do:

DECLARE @.d datetime, @.tookms int
SELECT @.d = getdate()
-- Run query
SELECT @.tookms = datediff(ms, @.d, getdate())
PRINT 'It took ' + ltrim(str(@.tookms)) + ' ms.'

As John mentioned it is important to have the cache in mind. You can
do DBCC DROPCLEANBUFFERS to flush the cache, but don't this on a
production box! Often I'm lazy and run the queries several times, and
forget the first run, since that may include time reading from disk.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,
Thanks. Yes, I use "Is Null". I wrote the question on the fly. I will
insert your @.tookms into my sprocs to see how they perform. That's a
great hint.
LQ

detecting & deleting duplicates in batch vs proc

I know how to detect & delete dups/or >dups in test with a select clause, this works fine in a small table, but if the table has a million rows say, it sounds like a proc would be faster: my question is: How do I display those rows in a proc for detecting what the problem is. The print stmt. doesn't seem to work and I wondered if I had to go through the process of building an output stream. The proc creates okay but I'm stuck after that part.

thx

Kat -- very rough code below

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create proc dupcount
@.count int
as
set nocount on
select categoryID,
CategoryName,
Count(*) As Dups
from Categories
group by Categoryid, CategoryName
having count(*) >1

set @.count = @.@.Rowcount
print convert(varchar(30),CategoryID) + ' ' + convert(varchar(30),@.count) + CategoryName

/*set @.count = @.@.RowCount
IF @.count > 1
print convert CategoryID, CategoryName, convert(varchar(30),@.count)*/

hello kat,

I recommend you save the records to be deleted into a table. Thats for reporting purpose and

just incase yo might have deleted something important, for backup purposes

print statement would not be ideal for a very large operations, besides its too slow

regards,

joey

|||

Hi Joey,

Thx for all your help... should I do this in a batch or in a proc?

Kind Regards,

Kat

|||

hi kat,

batch operation will do since this is just a one time operation.

you dont want to have so many SP's in your db.

anyway. Are you done with the delete statement?

I mean the delete query which will delete the duplicates?

joey

|||

Yes, thank you very much! I'm trying to find test situations and how to resolve them. This was one that I've seen happen before.

Kat

|||

joeydj wrote:

hello kat,

I recommend you save the records to be deleted into a table. Thats for reporting purpose and

just incase yo might have deleted something important, for backup purposes

print statement would not be ideal for a very large operations, besides its too slow

regards,

joey

Never save deleted records. Transactions and error handling ensure wanted records are not deleted.

If you want to delete them, delete them and be done with it. It's going to save a lot of purging time later on.

...and PRINT is for Query Analyzer and Management Studio not for application display.

Adamus

|||

If you want to display them in an application form, use SELECT not PRINT.

Research "Views" in SQL Server.

Adamus

|||

hello adam,

lets help the girl.

how do we delete duplicates

and of course leave one of them in the table

lets say i have a table name tablex

tablex

id , lastname, first,

1, cruz, juan

1, cruz, juan carlos

2, penduko, pedro

2, penduko, peter

2, penduko, pedro

3, falaypay,pacifico

3, falaypay, pacifico

3, falapay, pacifica

4, khan, cynthia

4, luster, cynthia - got married

4, khan, agnes cynthia - the encoder missed the second name

In this example i have a very dirty employees table in the data warehouse and i have 8 million of them. and it happens in real life. hahaha. if this is a warehouse there should be a timestamp but sad to say the architect has failed to remember of placing one

how can i remove the duplicates and maintain the integrity. Ok never mind integrity how do we delete the duplicate first just using the id as qualifier

regards,

joey

|||I was just wondering if anyone had an answer to Joey's post. It appears that I would have to manually look at the data to resolve the issue, any ideas?|||

You can detect duplicates using query below:

select id

from tbl

group by id

having count(*) > 1

For the delete logic to work efficiently, you need some other key column(s) in the table to determine which row to keep. This can also be some columns that uniquely identifies a row within each group.

delete tbl

where timestamp < (select max(t1.timestamp) from tbl as t1 where t1.id = tbl.id)

go

For these type of tables in a data warehouse that doesn't have any constraints you can add an identity column and then do below:

alter table tbl add uqid int not null identity

go

delete tbl

where uqid > (select min(t1.uqid) from tbl as t1 where t1.id = tbl.id)

go

If you have large number of rows to delete then you can perform the delete in batches like:

-- Works only on SQL Server 2005. Uses TOP clause support in DML

declare @.n int

set @.n = 1000

while(1=1)

begin

-- delete N rows at a time

delete top(@.n) tbl

where uqid > (select min(t1.uqid) from tbl as t1 where t1.id = tbl.id)

if @.@.rowcount = 0 break

end

go

-- Older versions of SQL Server

declare @.n int

set @.n = 1000

set rowcount @.n

while(1=1)

begin

-- delete N rows at a time

delete tbl

where uqid > (select min(t1.uqid) from tbl as t1 where t1.id = tbl.id)

if @.@.rowcount = 0 break

end

set rowcount 0

go

If you cannot add identity column or have other columns that identifies a duplicate within each group you can use approach below. This will however be slower due to use of cursor.

declare @.id int, @.cnt int

declare @.dupes cursor

set @.dupes =

cursor fast_forward for

select id, count(*) - 1

from tbl

group by id

having count(*) > 1

open @.dupes

while(1=1)

begin

fetch @.dupes into @.id, @.cnt

if @.@.fetch_status < 0 break

/* Older versions of SQL Server

set rowcount @.cnt -- don't care which N-1 rows are deleted

delete tbl

where id = @.id

*/

/* SQL2005

delete top(@.cnt) tbl

where id = @.id

*/

end

/* Older versions of SQL Server

set rowcount 0

*/

go

However, this is a bad way to maintain a data warehouse. You need to build rules into the data loading and transformation process to track changes easily. You need to model your dimensions so that they can accomodate changes to attribute values. There are many standard technique available to do this.

|||

DECLARE c1 CURSOR FAST_FORWARD FOR

AS

DECLARE @.FirstName varchar(25)

Select firstname, count(firstname)

from Employees

Group by firstname

having count(firstname) > 1

open c1

fetch next from c1

into @.Firstname

While @.@.fetch_status = 0

BEGIN

insert into DeletedItems(firstname) Values(@.FirstName)

Delete from Employees

Where firstname = @.Firstname

END

fetch next from c1

into @.Firstname

CLOSE c1

DEALLOCATE c1

|||

Hi Adamus,

I thought 'cursors' were the bane of SQL, if I had a table with millions of rows, wouldn't this take forever? Or is this something new in 2005?

thx

|||

katgreen777 wrote:

Hi Adamus,

I thought 'cursors' were the bane of SQL, if I had a table with millions of rows, wouldn't this take forever? Or is this something new in 2005?

thx

CTE is the replacement in 2005 and I'm learning to code it, but yes cursors do carry a lot of overhead. Notice I use a "fast_forward" It makes a big difference.

My argument with cursors and millions of rows is, if you're dealing with millions of rows, there is no lightning fix other than hardware enhancements. You could argue until you're blue in the face with seconds, execution plans, alternative methods, and the list goes on, only to discover a well written cursor and ample thought will give you the result you need.

Adamus

|||

katgreen777 wrote:

Hi Adamus,I thought 'cursors' were the bane of SQL, if I had a table with millions of rows, wouldn't this take forever? Or is this something new in 2005?

Please take a look at the code that I posted before. You don't need cursors unless your table(s) doesn't have any key information or unique identifiers or other required columns. To delete large number of rows efficiently, you can use a batching mechanism using SET ROWCOUNT or TOP. See the examples I posted. And CTEs as mentioned in the other post doesn't really help in this case. CTE is just a query expression similar to derived tables or views and provides some additional capabilities like writing recursive queries.

|||

Hi,

Looking at the 'dirty' warehouse table above, wouldn't we just use the id number to delete the rows because that is unique and the firstname isnt?

thx

sql

Detect and Passing measure name in Action

Is there a way when launching action to call a URL, I can pass the name of the measure select by users?

We have a cube with multiple measures (included calculated member measures), when a user select a cell under measure A, I need to pass the name A to the URL to ASP.NET application. Similarly, when a user click measure B cell to fire up action, I need to pass measuer name B on URL the the web applicatoin.

Currently, I can pass dimension CurrentMember w/o problem, is there a similar measure CurrentMember function I can use?

Thanks!

CurrentMember should work on the Measures dimension in exactly the same way as on other dimensions, so by using something like Measures.CurrentMember.Name you should be able to get hold of the name of the current measure. Using the UrlEscapeFragment function (see http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!517.entry) will get rid of any url-unfriendly characters.

Chris

Detach/Attach Via Management Studio

I really can't believe how difficult this is.
Open SQL Server Management Studio, browse to the databases, right-click the
one to detach, select Tasks>Detach... Check the Drop Connections checkbox,
click OK... Bang! Failed.
"The Database is not accessable..." pops up during the process. Click on the
OK.
"Cannot detach the database 'test' because it is currently in use.
(Microsoft SQL Server, Error: 3703)"
Hold on... Didn't I ask it to drop the connections? So why is it still in
use?
Look at the database, it's in Single User mode. OK. Let's get it back to
normal... Right-click, select properties... Bang! Error.
"Cannot show requested dialog."
"Database 'test' is already open and can only have one user at a time.
(Microsoft SQL Server, Error: 924)"
Who the heck has it open? Check the Activity Monitor:
A suspended delete command through the web application db user...
"(@.p2 int)BEGIN CONVERSATION TIMER ('37238b35-6439-db11-934c-00137260bfc2')
TIMEOUT = 120; WAITFOR(RECEIVE TOP (1) message_type_name,
conversation_handle, cast(message_body AS XML) as message_body from
[SqlQueryNotificationService-5710e78f-2bab-4e58-8567-edb949981446]), TIMEOUT
@.p2;"
Unfortunately, I can't seem to kill the damn process. It just refuses to go
away. And the developers have no idea what it's for, so it must be some .NET
2.0 assembly thing.
Anyone know how to deal with this apart from shutting down the web
application server?Hi,
Open the query window and try this script...
ALTER DATABASE <DBNAME> SET SINGLE_USER WITH Rollback Immediate
GO
SP_Detach_db <dbname>
Thanks
Hari
SQL Server MVP
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:OGNd4KXzGHA.3464@.TK2MSFTNGP03.phx.gbl...
>I really can't believe how difficult this is.
> Open SQL Server Management Studio, browse to the databases, right-click
> the one to detach, select Tasks>Detach... Check the Drop Connections
> checkbox, click OK... Bang! Failed.
> "The Database is not accessable..." pops up during the process. Click on
> the OK.
> "Cannot detach the database 'test' because it is currently in use.
> (Microsoft SQL Server, Error: 3703)"
> Hold on... Didn't I ask it to drop the connections? So why is it still in
> use?
> Look at the database, it's in Single User mode. OK. Let's get it back to
> normal... Right-click, select properties... Bang! Error.
> "Cannot show requested dialog."
> "Database 'test' is already open and can only have one user at a time.
> (Microsoft SQL Server, Error: 924)"
> Who the heck has it open? Check the Activity Monitor:
> A suspended delete command through the web application db user...
> "(@.p2 int)BEGIN CONVERSATION TIMER
> ('37238b35-6439-db11-934c-00137260bfc2') TIMEOUT = 120; WAITFOR(RECEIVE
> TOP (1) message_type_name, conversation_handle, cast(message_body AS XML)
> as message_body from
> [SqlQueryNotificationService-5710e78f-2bab-4e58-8567-edb949981446]),
> TIMEOUT @.p2;"
> Unfortunately, I can't seem to kill the damn process. It just refuses to
> go away. And the developers have no idea what it's for, so it must be some
> .NET 2.0 assembly thing.
> Anyone know how to deal with this apart from shutting down the web
> application server?
>|||The process is the server side of the Dependency client event that they use
to get a notification when something changes the data for a specified query.
It should be killable unless the client is restarting it. You may need to
stop the client app to get it to go away.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:OGNd4KXzGHA.3464@.TK2MSFTNGP03.phx.gbl...
>I really can't believe how difficult this is.
> Open SQL Server Management Studio, browse to the databases, right-click
> the one to detach, select Tasks>Detach... Check the Drop Connections
> checkbox, click OK... Bang! Failed.
> "The Database is not accessable..." pops up during the process. Click on
> the OK.
> "Cannot detach the database 'test' because it is currently in use.
> (Microsoft SQL Server, Error: 3703)"
> Hold on... Didn't I ask it to drop the connections? So why is it still in
> use?
> Look at the database, it's in Single User mode. OK. Let's get it back to
> normal... Right-click, select properties... Bang! Error.
> "Cannot show requested dialog."
> "Database 'test' is already open and can only have one user at a time.
> (Microsoft SQL Server, Error: 924)"
> Who the heck has it open? Check the Activity Monitor:
> A suspended delete command through the web application db user...
> "(@.p2 int)BEGIN CONVERSATION TIMER
> ('37238b35-6439-db11-934c-00137260bfc2') TIMEOUT = 120; WAITFOR(RECEIVE
> TOP (1) message_type_name, conversation_handle, cast(message_body AS XML)
> as message_body from
> [SqlQueryNotificationService-5710e78f-2bab-4e58-8567-edb949981446]),
> TIMEOUT @.p2;"
> Unfortunately, I can't seem to kill the damn process. It just refuses to
> go away. And the developers have no idea what it's for, so it must be some
> .NET 2.0 assembly thing.
> Anyone know how to deal with this apart from shutting down the web
> application server?
>|||There was a Service Broker for the client running on the database server
that wouldn't let the process finish. Rebooting the web application server
allowed me to detach/attach, but I was also able to do the same by removing
and adding the broker.
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:OL%23bT2XzGHA.1536@.TK2MSFTNGP02.phx.gbl...
> The process is the server side of the Dependency client event that they
> use to get a notification when something changes the data for a specified
> query. It should be killable unless the client is restarting it. You may
> need to stop the client app to get it to go away.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
> news:OGNd4KXzGHA.3464@.TK2MSFTNGP03.phx.gbl...
>>I really can't believe how difficult this is.
>> Open SQL Server Management Studio, browse to the databases, right-click
>> the one to detach, select Tasks>Detach... Check the Drop Connections
>> checkbox, click OK... Bang! Failed.
>> "The Database is not accessable..." pops up during the process. Click on
>> the OK.
>> "Cannot detach the database 'test' because it is currently in use.
>> (Microsoft SQL Server, Error: 3703)"
>> Hold on... Didn't I ask it to drop the connections? So why is it still in
>> use?
>> Look at the database, it's in Single User mode. OK. Let's get it back to
>> normal... Right-click, select properties... Bang! Error.
>> "Cannot show requested dialog."
>> "Database 'test' is already open and can only have one user at a time.
>> (Microsoft SQL Server, Error: 924)"
>> Who the heck has it open? Check the Activity Monitor:
>> A suspended delete command through the web application db user...
>> "(@.p2 int)BEGIN CONVERSATION TIMER
>> ('37238b35-6439-db11-934c-00137260bfc2') TIMEOUT = 120; WAITFOR(RECEIVE
>> TOP (1) message_type_name, conversation_handle, cast(message_body AS XML)
>> as message_body from
>> [SqlQueryNotificationService-5710e78f-2bab-4e58-8567-edb949981446]),
>> TIMEOUT @.p2;"
>> Unfortunately, I can't seem to kill the damn process. It just refuses to
>> go away. And the developers have no idea what it's for, so it must be
>> some .NET 2.0 assembly thing.
>> Anyone know how to deal with this apart from shutting down the web
>> application server?
>>
>

Detach/Attach Via Management Studio

I really can't believe how difficult this is.
Open SQL Server Management Studio, browse to the databases, right-click the
one to detach, select Tasks>Detach... Check the Drop Connections checkbox,
click OK... Bang! Failed.
"The Database is not accessable..." pops up during the process. Click on the
OK.
"Cannot detach the database 'test' because it is currently in use.
(Microsoft SQL Server, Error: 3703)"
Hold on... Didn't I ask it to drop the connections? So why is it still in
use?
Look at the database, it's in Single User mode. OK. Let's get it back to
normal... Right-click, select properties... Bang! Error.
"Cannot show requested dialog."
"Database 'test' is already open and can only have one user at a time.
(Microsoft SQL Server, Error: 924)"
Who the heck has it open? Check the Activity Monitor:
A suspended delete command through the web application db user...
"(@.p2 int)BEGIN CONVERSATION TIMER ('37238b35-6439-db11-934c-00137260bfc2')
TIMEOUT = 120; WAITFOR(RECEIVE TOP (1) message_type_name,
conversation_handle, cast(message_body AS XML) as message_body from
[SqlQueryNotificationService-5710e78f-2bab-4e58-8567-edb949981446]), TIM
EOUT
@.p2;"
Unfortunately, I can't seem to kill the damn process. It just refuses to go
away. And the developers have no idea what it's for, so it must be some .NET
2.0 assembly thing.
Anyone know how to deal with this apart from shutting down the web
application server?Hi,
Open the query window and try this script...
ALTER DATABASE <DBNAME> SET SINGLE_USER WITH Rollback Immediate
GO
SP_Detach_db <dbname>
Thanks
Hari
SQL Server MVP
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:OGNd4KXzGHA.3464@.TK2MSFTNGP03.phx.gbl...
>I really can't believe how difficult this is.
> Open SQL Server Management Studio, browse to the databases, right-click
> the one to detach, select Tasks>Detach... Check the Drop Connections
> checkbox, click OK... Bang! Failed.
> "The Database is not accessable..." pops up during the process. Click on
> the OK.
> "Cannot detach the database 'test' because it is currently in use.
> (Microsoft SQL Server, Error: 3703)"
> Hold on... Didn't I ask it to drop the connections? So why is it still in
> use?
> Look at the database, it's in Single User mode. OK. Let's get it back to
> normal... Right-click, select properties... Bang! Error.
> "Cannot show requested dialog."
> "Database 'test' is already open and can only have one user at a time.
> (Microsoft SQL Server, Error: 924)"
> Who the heck has it open? Check the Activity Monitor:
> A suspended delete command through the web application db user...
> "(@.p2 int)BEGIN CONVERSATION TIMER
> ('37238b35-6439-db11-934c-00137260bfc2') TIMEOUT = 120; WAITFOR(RECEIVE
> TOP (1) message_type_name, conversation_handle, cast(message_body AS XML)
> as message_body from
> [SqlQueryNotificationService-5710e78f-2bab-4e58-8567-edb949981446]),
> TIMEOUT @.p2;"
> Unfortunately, I can't seem to kill the damn process. It just refuses to
> go away. And the developers have no idea what it's for, so it must be some
> .NET 2.0 assembly thing.
> Anyone know how to deal with this apart from shutting down the web
> application server?
>|||The process is the server side of the Dependency client event that they use
to get a notification when something changes the data for a specified query.
It should be killable unless the client is restarting it. You may need to
stop the client app to get it to go away.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:OGNd4KXzGHA.3464@.TK2MSFTNGP03.phx.gbl...
>I really can't believe how difficult this is.
> Open SQL Server Management Studio, browse to the databases, right-click
> the one to detach, select Tasks>Detach... Check the Drop Connections
> checkbox, click OK... Bang! Failed.
> "The Database is not accessable..." pops up during the process. Click on
> the OK.
> "Cannot detach the database 'test' because it is currently in use.
> (Microsoft SQL Server, Error: 3703)"
> Hold on... Didn't I ask it to drop the connections? So why is it still in
> use?
> Look at the database, it's in Single User mode. OK. Let's get it back to
> normal... Right-click, select properties... Bang! Error.
> "Cannot show requested dialog."
> "Database 'test' is already open and can only have one user at a time.
> (Microsoft SQL Server, Error: 924)"
> Who the heck has it open? Check the Activity Monitor:
> A suspended delete command through the web application db user...
> "(@.p2 int)BEGIN CONVERSATION TIMER
> ('37238b35-6439-db11-934c-00137260bfc2') TIMEOUT = 120; WAITFOR(RECEIVE
> TOP (1) message_type_name, conversation_handle, cast(message_body AS XML)
> as message_body from
> [SqlQueryNotificationService-5710e78f-2bab-4e58-8567-edb949981446]),
> TIMEOUT @.p2;"
> Unfortunately, I can't seem to kill the damn process. It just refuses to
> go away. And the developers have no idea what it's for, so it must be some
> .NET 2.0 assembly thing.
> Anyone know how to deal with this apart from shutting down the web
> application server?
>|||There was a Service Broker for the client running on the database server
that wouldn't let the process finish. Rebooting the web application server
allowed me to detach/attach, but I was also able to do the same by removing
and adding the broker.
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:OL%23bT2XzGHA.1536@.TK2MSFTNGP02.phx.gbl...
> The process is the server side of the Dependency client event that they
> use to get a notification when something changes the data for a specified
> query. It should be killable unless the client is restarting it. You may
> need to stop the client app to get it to go away.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
> news:OGNd4KXzGHA.3464@.TK2MSFTNGP03.phx.gbl...
>

Destination resets when changing selected databases

I discovered that everytime I need to add a database to my backup Maintenance Plan, after I select the new database from the drop-down of databases, the Destination automatically resets back to a default location.

I'm assuming this is a bug that will be resolved at some point, but in the meantime, I need to see if there is a way I can deal with this permanently. I am not the only one adding databases to the backup routine so I can't verify that this setting is properly changed every time we have a new database (which is about once a week).

Thanks in advance.

This is my last effort to get an answer on this. At this point using the maintenance plans is causing more problems than it solves because we add new databases most weeks. Everytime a database is added we have to change the backup maintenance plan and remember to change the backup path after it resets to a default path.

If there is even a place that I can change the default backup location, I will take that as a good workaround. I just can't continue this way since I am not always adding the databases to the backup and therefore can't validate that the path is reset every time. If there isn't a solution, I'll have to build a front-end to manage the backup scripts which I really don't want to do.

I am asking that a Microsoft representative PLEASE give me an anwer on this so I can move on.

Thanks.

|||

If you go into RegEdit (with ALL of the caveats and cautions that always accompany manually modifying the registry!),

Navigate to:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer

and modify the BackupDirectory value, you'll have this as the new default.

Note that this permanently modifies the default location for ALL TSQL backups!

|||But is Microsoft going to fix this bug? I submitted it via the feedback site and was told they couldn't reproduce it. We don't add databases as often but it is still a pain to have to remember to look at the path and make sure it is right. I can't use the registry fix as each database goes to it's own location so every maint. plan is different.|||

Thanks for the idea Kevin. Unfortunately this is a client-side solution and I need a server-side solution for the default. Otherwise I would have to go make this change to every PC, laptop, and home PC via VPN for all administrators who can add backups. That just can't happen.

Thanks again for the info though.

Heather, so they said they can't reproduce it? I can't find an installation where it doesn't work this way, and we are on SP1 too.

I guess we'll just have to wait and see if it is ever acknowledged.

|||

Actually, this is a server-side setting.

If you make the registry setting on the server, and then connect to it from another node using Management Studio, that instance of Management studio will see the new default location for the backups. While we can't customize it for each database (I'm not sure how we'd do that since there's nothing to remember), we can at least get the proper parent directory, and keep backups off the C: drive!

|||

Thank you Kevin!!! That is great. I will go make the setting (carefully of course) now and we should be good to go.

For what it's worth, I would recommend that Microsoft add a backup path setup option to the server properties dialogue, maybe along with default mdb and ldb paths. Since custom backup paths to other media are common, even encouraged, I just think it makes sense to turn it into an accessible option. Obviously it is not necessary to accomplish even the most complex backup plans because the flexibility in SQL 2005 rocks, but it would make it easier to accuratly configure multiple maintenance plans using the Managment Studio tools without error.

Anyway, thanks again for the big tip! I'm sure it will help others as well.

P.S. I've made the change now and it works like a charm. I didn't even have to disconnect/reconnect my local Management Studio for it to take effect locally when configuring a maintenance plan.

|||

It worked beautifully for me as well. But I also agree this setting should be something simple in the Management Studio, not just a registry hack.

Thank you for the great assistance!

|||

The problem is not trying to set a different default destination for each database, the problem is that anytime you open the backup step properties, the destination folder is reset to the server default instead of remembering the value that you set it to manually.

It had to look at the saved properties to get the database list, it could just as easily load the saved value of the destination folder instead of going to the registry.

Destination resets when changing selected databases

I discovered that everytime I need to add a database to my backup Maintenance Plan, after I select the new database from the drop-down of databases, the Destination automatically resets back to a default location.

I'm assuming this is a bug that will be resolved at some point, but in the meantime, I need to see if there is a way I can deal with this permanently. I am not the only one adding databases to the backup routine so I can't verify that this setting is properly changed every time we have a new database (which is about once a week).

Thanks in advance.

This is my last effort to get an answer on this. At this point using the maintenance plans is causing more problems than it solves because we add new databases most weeks. Everytime a database is added we have to change the backup maintenance plan and remember to change the backup path after it resets to a default path.

If there is even a place that I can change the default backup location, I will take that as a good workaround. I just can't continue this way since I am not always adding the databases to the backup and therefore can't validate that the path is reset every time. If there isn't a solution, I'll have to build a front-end to manage the backup scripts which I really don't want to do.

I am asking that a Microsoft representative PLEASE give me an anwer on this so I can move on.

Thanks.

|||

If you go into RegEdit (with ALL of the caveats and cautions that always accompany manually modifying the registry!),

Navigate to:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer

and modify the BackupDirectory value, you'll have this as the new default.

Note that this permanently modifies the default location for ALL TSQL backups!

|||But is Microsoft going to fix this bug? I submitted it via the feedback site and was told they couldn't reproduce it. We don't add databases as often but it is still a pain to have to remember to look at the path and make sure it is right. I can't use the registry fix as each database goes to it's own location so every maint. plan is different.|||

Thanks for the idea Kevin. Unfortunately this is a client-side solution and I need a server-side solution for the default. Otherwise I would have to go make this change to every PC, laptop, and home PC via VPN for all administrators who can add backups. That just can't happen.

Thanks again for the info though.

Heather, so they said they can't reproduce it? I can't find an installation where it doesn't work this way, and we are on SP1 too.

I guess we'll just have to wait and see if it is ever acknowledged.

|||

Actually, this is a server-side setting.

If you make the registry setting on the server, and then connect to it from another node using Management Studio, that instance of Management studio will see the new default location for the backups. While we can't customize it for each database (I'm not sure how we'd do that since there's nothing to remember), we can at least get the proper parent directory, and keep backups off the C: drive!

|||

Thank you Kevin!!! That is great. I will go make the setting (carefully of course) now and we should be good to go.

For what it's worth, I would recommend that Microsoft add a backup path setup option to the server properties dialogue, maybe along with default mdb and ldb paths. Since custom backup paths to other media are common, even encouraged, I just think it makes sense to turn it into an accessible option. Obviously it is not necessary to accomplish even the most complex backup plans because the flexibility in SQL 2005 rocks, but it would make it easier to accuratly configure multiple maintenance plans using the Managment Studio tools without error.

Anyway, thanks again for the big tip! I'm sure it will help others as well.

P.S. I've made the change now and it works like a charm. I didn't even have to disconnect/reconnect my local Management Studio for it to take effect locally when configuring a maintenance plan.

|||

It worked beautifully for me as well. But I also agree this setting should be something simple in the Management Studio, not just a registry hack.

Thank you for the great assistance!

|||

The problem is not trying to set a different default destination for each database, the problem is that anytime you open the backup step properties, the destination folder is reset to the server default instead of remembering the value that you set it to manually.

It had to look at the saved properties to get the database list, it could just as easily load the saved value of the destination folder instead of going to the registry.

Destination resets when changing selected databases

I discovered that everytime I need to add a database to my backup Maintenance Plan, after I select the new database from the drop-down of databases, the Destination automatically resets back to a default location.

I'm assuming this is a bug that will be resolved at some point, but in the meantime, I need to see if there is a way I can deal with this permanently. I am not the only one adding databases to the backup routine so I can't verify that this setting is properly changed every time we have a new database (which is about once a week).

Thanks in advance.

This is my last effort to get an answer on this. At this point using the maintenance plans is causing more problems than it solves because we add new databases most weeks. Everytime a database is added we have to change the backup maintenance plan and remember to change the backup path after it resets to a default path.

If there is even a place that I can change the default backup location, I will take that as a good workaround. I just can't continue this way since I am not always adding the databases to the backup and therefore can't validate that the path is reset every time. If there isn't a solution, I'll have to build a front-end to manage the backup scripts which I really don't want to do.

I am asking that a Microsoft representative PLEASE give me an anwer on this so I can move on.

Thanks.

|||

If you go into RegEdit (with ALL of the caveats and cautions that always accompany manually modifying the registry!),

Navigate to:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer

and modify the BackupDirectory value, you'll have this as the new default.

Note that this permanently modifies the default location for ALL TSQL backups!

|||But is Microsoft going to fix this bug? I submitted it via the feedback site and was told they couldn't reproduce it. We don't add databases as often but it is still a pain to have to remember to look at the path and make sure it is right. I can't use the registry fix as each database goes to it's own location so every maint. plan is different.|||

Thanks for the idea Kevin. Unfortunately this is a client-side solution and I need a server-side solution for the default. Otherwise I would have to go make this change to every PC, laptop, and home PC via VPN for all administrators who can add backups. That just can't happen.

Thanks again for the info though.

Heather, so they said they can't reproduce it? I can't find an installation where it doesn't work this way, and we are on SP1 too.

I guess we'll just have to wait and see if it is ever acknowledged.

|||

Actually, this is a server-side setting.

If you make the registry setting on the server, and then connect to it from another node using Management Studio, that instance of Management studio will see the new default location for the backups. While we can't customize it for each database (I'm not sure how we'd do that since there's nothing to remember), we can at least get the proper parent directory, and keep backups off the C: drive!

|||

Thank you Kevin!!! That is great. I will go make the setting (carefully of course) now and we should be good to go.

For what it's worth, I would recommend that Microsoft add a backup path setup option to the server properties dialogue, maybe along with default mdb and ldb paths. Since custom backup paths to other media are common, even encouraged, I just think it makes sense to turn it into an accessible option. Obviously it is not necessary to accomplish even the most complex backup plans because the flexibility in SQL 2005 rocks, but it would make it easier to accuratly configure multiple maintenance plans using the Managment Studio tools without error.

Anyway, thanks again for the big tip! I'm sure it will help others as well.

P.S. I've made the change now and it works like a charm. I didn't even have to disconnect/reconnect my local Management Studio for it to take effect locally when configuring a maintenance plan.

|||

It worked beautifully for me as well. But I also agree this setting should be something simple in the Management Studio, not just a registry hack.

Thank you for the great assistance!

|||

The problem is not trying to set a different default destination for each database, the problem is that anytime you open the backup step properties, the destination folder is reset to the server default instead of remembering the value that you set it to manually.

It had to look at the saved properties to get the database list, it could just as easily load the saved value of the destination folder instead of going to the registry.

Desktop Server

1. Today I've installed webmatrix andMSDE200Aand followed this instruction

"Select 'SQL2kdesksp3.exe' and save it to your computer.

Double-click on theSQL2kdesksp3.exe you downloaded.

Once you have run the self extracting exe (SQL2kdesksp3.exe), go to a command prompt.

Start > Run > cmd

Navigate to the directory you expanded the self extracting exe to and change to the MSDE subdirectory.

The default is C:\sql2ksp3

Type Setup SAPWD=(Some password) SecurityMode=SQL

Example:
c:\sql2ksp3> Setup SAPWD=password SecurityMode=SQL

After that gets done running, MSDE is installed.

Start services

To get the SQL Server running and the SQL Agent we need start the services.

right click My Computer > manage > services

Double click the following and set to Automatic.

MSSQLSERVER
SQLSERVERAGENT

Make sure both are on automatic."

then I restarted my comp. I've tried to connect to database but it did not work.

I need the reply ASAP!!!!

2.What are the different among MSDE200A.exe, sql2ksp3.exe, and sql2kdesksp3.exe ?

Fahrisal:

then I restarted my comp. I've tried to connect to database but it did not work.

Please explain "did not work".

Fahrisal:

What are the different among MSDE200A.exe, sql2ksp3.exe, and sql2kdesksp3.exe ?

FromMicrosoft SQL Server 2000 Desktop Engine (MSDE 2000) Release A andMicrosoft SQL Server 2000 Service Pack 3a I have learned:

MSDE2000A.exe is the desktop engine for SQL Server (MSDE) and includes the Service Pack 3a patch - this is all you need

Design T-SQL WHERE

Hi ,

I have following statement :

SELECT * FROM Table WHERE Col1>=@.Col1L AND Col1<=@.Col1H AND Col2>=@.Col2L AND Col2<=@.Col2LH AND ... AND ColN>=@.ColNL AND ColN<=@.ColNH

But sometimes variables e.g. @.Col1L and @.Col1H may cover whole range of available values so they will be there for nothing
E.g. It may happen my query will be sufficient if I will have

SELECT * FROM Table WHERE Col1>=@.Col1L AND Col1<=@.Col1H -- and no other columns, because @.Col2L will be lowest possible assignable value and @.Col2H highest possible value, etc.

How should I design this type of query ?

Should I dynamically create WHERE clause e.g.:

IF @.Col2L<>@.MinPossibleValue OR @.Col2H<>@.MaxPossibleValue
SET @.WHERE=@.WHERE + 'Col2>=' + @.Col2L + ' AND Col2<=' + @.Col2LH
...
EXEC(@.Query)

Got any other suggestion for designing query ? Or improving performance ...

Thank you for your opinion.You could use dynamic SQL but it is not worth the effort and complexity to do that. It also depends on the indexes and your search conditions. Below are some links that you should check out:

Dynamic Search Conditions in T-SQL
http://www.sommarskog.se/dyn-search.html

The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html