Comparing a pair

Hi
I would like to have a subquery where I'm comparing 2 values from the
subquery to the main query and unsure on the syntax. I know in Oracle sql
it's something like
WHERE (manager_id , dept_id) IN (SELECT manager_id, dept_id FROM table ...
etc)
Thanks in advance for your help
G
TSQL doesn't have row-value constructors. But EXISTS is often a better way
than using IN anyway:
SELECT *
FROM Foo
WHERE EXISTS
(SELECT *
FROM Bar
WHERE bar.manager_id = foo.manager_id
AND bar.dept_id = foo.dept_id)
David Portas
SQL Server MVP
|||ok, will try. thanks
"David Portas" wrote:

> TSQL doesn't have row-value constructors. But EXISTS is often a better way
> than using IN anyway:
> SELECT *
> FROM Foo
> WHERE EXISTS
> (SELECT *
> FROM Bar
> WHERE bar.manager_id = foo.manager_id
> AND bar.dept_id = foo.dept_id)
> --
> David Portas
> SQL Server MVP
> --
>
>

Compare records of two tables?

Hi

I am trying for compare two tables records and if which records are not match insert in to both table and at end both table records will be same.

using stored procedure & I need also pass server name database name.

thanks

I did not know how to use store procedures but i did use TableDiff.exe to compare both tables.

Here is the link:

http://www.replicationanswers.com/TableDiff2005.asp

Have a nice day

|||

Check out SQL Data Compare from RedGate.

Martin

Compare given period in current year / previous year

Hi
I want to write a function that can return a sum for a given date
range. The same function should be able to return the sum for the same
period year before.

Let me give an example:
The Table LedgerTrans consist among other of the follwing fields
AccountNum (Varchar)
Transdate
AmountMST (Real)

The sample data could be
1111, 01-01-2005, 100 USD
1111, 18-01-2005, 125 USD
1111, 15-03-2005, 50 USD
1111,27-06-2005, 500 USD
1111,02-01-2006, 250 USD
1111,23-02-2006,12 USD

If the current day is 16. march 2006 I would like to have a function
which called twice could retrive the values.
Previus period (for TransDate >= 01-01-2005 AND TransDate <=
16-03-2005) = 275 USD
Current period (for TransDate >= 01-01-2006 AND TransDate <=
16-03-2006) = 262 USD
The function should be called with the AccountNum and current date
(GetDate() ?) and f.ex. 0 or 1 for this year / previous year.
How can I create a function that dynamically can do this ?

I have tried f.ex. calling the function with
@.ThisYear as GetDate()
SET @.DateStart = datepart(d,0) + '-' + datepart(m,0) +
'-'+datepart(y,@.ThisYear)
But the value for @.dateStart is something like 12-07-1905 so this
don't work.

I Would appreciate any help on this.
BR / Jan(jannoergaard@.hotmail.com) writes:
> Let me give an example:
> The Table LedgerTrans consist among other of the follwing fields
> AccountNum (Varchar)
> Transdate
> AmountMST (Real)
> The sample data could be
> 1111, 01-01-2005, 100 USD
> 1111, 18-01-2005, 125 USD
> 1111, 15-03-2005, 50 USD
> 1111,27-06-2005, 500 USD
> 1111,02-01-2006, 250 USD
> 1111,23-02-2006,12 USD
> If the current day is 16. march 2006 I would like to have a function
> which called twice could retrive the values.
> Previus period (for TransDate >= 01-01-2005 AND TransDate <=
> 16-03-2005) = 275 USD
> Current period (for TransDate >= 01-01-2006 AND TransDate <=
> 16-03-2006) = 262 USD
> The function should be called with the AccountNum and current date
> (GetDate() ?) and f.ex. 0 or 1 for this year / previous year.
> How can I create a function that dynamically can do this ?

I'm uncertain on want interface you want on your function (and I am
not sure that you should use a function anyway), but here is a
query for the task:

SELECT AccountNum, LastYearTroubles =
SUM(CASE WHEN Transdate BETWEEN
dateadd(YEAR, -1,
convert(char(4), @.date, 112) + '0101')) AND
dateadd(YEAR, -1, @.date)
THEN AmountMST
ELSE 0
END),
ThisYear =
SUM(CASE WHEN Transdate BETWEEN
convert(char(4), @.date, 112) + '0101')) AND
@.date)
THEN AmountMST
ELSE 0
END)
FROM Ledger
WHERE TransDate BETWEEN dateadd(YEAR, -1,
convert(char(4), @.date, 112) + '0101')) AND
@.date
GROUP BY AccountNum

As for the date conversion, format 112 is essentail for playing with
dates. This format is YYYYMMDD, and this is one of the formats that
always converts back to date in the same way.

--
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|||Hi There

Thank you very much. I modified your query a lillte bit, and it worked
as wanted in the function.

CREATE FUNCTION GuruInvoicedPeriodNew (@.AccountNum as
VarChar(10),@.Period AS Int, @.ThisY as DateTime)
RETURNS Float AS
BEGIN

DECLARE @.LedgerTrans AS Float
SET @.LedgerTrans = 0

IF @.Period = 0 /*this year*/
BEGIN
SELECT @.LedgerTrans =
SUM(AmountMST) FROM dbo.LedgerTrans
WHERE (DATAAREAID = dbo.GuruDataArea())
AND (TransDate >= (convert(char(4), @.ThisY, 112) + '0101')
AND TransDate <= @.ThisY)
AND AccountNum = @.AccountNum
END

IF @.Period = 1 /*previous year*/
BEGIN
SELECT @.LedgerTrans = SUM(AMOUNTMST) FROM dbo.LEDGERTRANS
WHERE (DATAAREAID = dbo.GuruDataArea())
AND Transdate >= (dateadd(YEAR, -1, convert(char(4), @.ThisY,
112) + '0101'))
AND TransDate <= dateadd(YEAR, -1, @.ThisY)
AND AccountNum = @.AccountNum
END

RETURN @.LedgerTrans
END

BR/Jan|||(jannoergaard@.hotmail.com) writes:
> Thank you very much. I modified your query a lillte bit, and it worked
> as wanted in the function.
> CREATE FUNCTION GuruInvoicedPeriodNew (@.AccountNum as
> VarChar(10),@.Period AS Int, @.ThisY as DateTime)
> RETURNS Float AS
> BEGIN

Yellow alert! How are you going to use this function? If you are going
to say something like:

SELECT AccountNum, dbo.InvoicedPeriod(AccountNum, 1, getdate()),
dbo.InvoicedPeriod(AccountNum, 0, getdate())
FROM accounts

It's not going to perform well. Scalar UDFs is something you should use
with care, and not the least scalar UDFs that perform table access.
There is quite an overhead for calling a UDF once per row, and when you
do table access, you have essentially created a disguised cursor.

--
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

Compare datetimes

Hi

I need to retrieve data from a table comparing datetimes. I mean I've a
table with a datetime data and I'll need to retrieve rows with date time
> now and date time < now + 5 minutes...

Is this posible ? Which is the best / easy way ?

Thanks in advance

J"Javier" <jleyba@.manresa.net> wrote in message
news:cu8frg$d8m$1@.news.ya.com...
> Hi
> I need to retrieve data from a table comparing datetimes. I mean I've a
> table with a datetime data and I'll need to retrieve rows with date time
> > now and date time < now + 5 minutes...
> Is this posible ? Which is the best / easy way ?
> Thanks in advance
> J

Check out DATEADD() in Books Online.

select col1, col2, ...
from dbo.MyTable
where dtcol > getdate()
and dtcol < dateadd(mi, 5, getdate())

Simon|||On Mon, 07 Feb 2005 20:34:05 +0100, Javier wrote:

>Hi
>I need to retrieve data from a table comparing datetimes. I mean I've a
>table with a datetime data and I'll need to retrieve rows with date time
> > now and date time < now + 5 minutes...
>Is this posible ? Which is the best / easy way ?

Hi Javier,

SELECT Column1, Column2, ...
FROM MyTable
WHERE MyDatetimeColumn > CURRENT_TIMESTAMP
AND MyDatetimeColumn < DATEADD(minute, 5, CURRENT_TIMESTAMP)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Compare dates

Hi!
I'm just attempting to write a SQL statement that will see if a date matches
.
Using asp.net, I place the date into a field in the database. It's formatted
like this:
08/12/2005
I'd like to create a query that counts how many rows a date (yesterday) is i
n.
This is the query I created:
select count(*) from FSRTurnover where theDate = Convert(Char(12),
DateAdd("d", -1, getdate()), 101)
but it doesn't seem to work. It doesn't return any rows...
If I use a static date, it does work:
select count(*) from FSRTurnover where theDate = '08/18/2005'
Any ideas why this might be?A couple of issues here.
First of all, 8/12/2005 is an ambiguous date. Depending on locale it might
mean August 12 or December 8. To be safe, always use the ISO date format,
YYYYMMDD.
Second, your query is only good for dates that happen to have a timestamp of
exactly midnight. But I'm guessing that's not what you really want... You
probably want ALL times from yesterday?
Try:
select count(*)
from FSRTurnover
where theDate >= DATEADD(dd, DATEDIFF(dd, 1, GETDATE()), 0)
AND theDate < DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
That will give you all dates >= midnight yesterday, and < midnight today.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Casey" <Casey@.discussions.microsoft.com> wrote in message
news:D31A0999-5F58-4882-A0B5-C910A7F813C0@.microsoft.com...
> Hi!
> I'm just attempting to write a SQL statement that will see if a date
matches.
> Using asp.net, I place the date into a field in the database. It's
formatted
> like this:
> 08/12/2005
> I'd like to create a query that counts how many rows a date (yesterday) is
in.
> This is the query I created:
> select count(*) from FSRTurnover where theDate = Convert(Char(12),
> DateAdd("d", -1, getdate()), 101)
> but it doesn't seem to work. It doesn't return any rows...
> If I use a static date, it does work:
> select count(*) from FSRTurnover where theDate = '08/18/2005'
> Any ideas why this might be?|||> I'm just attempting to write a SQL statement that will see if a date
> matches.
> Using asp.net, I place the date into a field in the database. It's
> formatted
> like this:
> 08/12/2005
No, it's not, if it is a DATETIME or SMALLDATETIME column. That is just how
*your* client tool shows it to you. Behind the scenes, it is actually
stored as two numeric values and does not have any ridiculously ambiguous
and confusing format like mm/dd/yyyy.

> I'd like to create a query that counts how many rows a date (yesterday) is
> in.
> This is the query I created:
> select count(*) from FSRTurnover where theDate = Convert(Char(12),
> DateAdd("d", -1, getdate()), 101)
Why are you converting to a character format? And why on earth would you
use CHAR(12)? You're comparing dates, not strings!
SELECT COUNT(*)
FROM FSRTurnover
WHERE theDate -- awful column name!
>= DATEADD(DAY,-1,DATEDIFF(DAY,0,GETDATE()))
AND theDate
< DATEADD(DAY,0,DATEDIFF(DAY,0,GETDATE()))
or broken down:
DECLARE @.yesterday SMALLDATETIME, @.today SMALLDATETIME
SET @.yesterday = DATEDIFF(DAY, 0, GETDATE())-1
SET @.today = @.yesterday + 1
SELECT COUNT(*)
FROM FSRTurnover
WHERE theDate >= @.yesterday
AND theDate < @.today
A|||Yeah. That seemed to work. I keep the ISO date format thing in mind for the
future.
Thanks.
"Adam Machanic" wrote:

> A couple of issues here.
> First of all, 8/12/2005 is an ambiguous date. Depending on locale it migh
t
> mean August 12 or December 8. To be safe, always use the ISO date format,
> YYYYMMDD.
> Second, your query is only good for dates that happen to have a timestamp
of
> exactly midnight. But I'm guessing that's not what you really want... You
> probably want ALL times from yesterday?
> Try:
> select count(*)
> from FSRTurnover
> where theDate >= DATEADD(dd, DATEDIFF(dd, 1, GETDATE()), 0)
> AND theDate < DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
> That will give you all dates >= midnight yesterday, and < midnight today.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Casey" <Casey@.discussions.microsoft.com> wrote in message
> news:D31A0999-5F58-4882-A0B5-C910A7F813C0@.microsoft.com...
> matches.
> formatted
> in.
>
>

Communication between SQL6.5 and SQL200

Hi!
I'm in a fix, we're changing domains and I need to communicate between SQL6.5 and SQL200 for a short while.
Both servers reside in the same domain (at the moment) so all I want to do is communicate between 6.5 and 2000.
There seems to be a problem with that...
Is this a common problem?
WCS is to install 2000 on all machines, but time is agains me. I will get to that evenually.

Any ideas?

The communication I want is to access data in a table in SQL2000 using a SP on the sql2000 server. The call is invoced by a SP on SQL6.5.

Anu ideas?

Thanks!
/BixProblem solved!
And as I can see it, there was no problem...
It worked just as it's supposed to do, but I had other errors to handle first.

/Bix

Commit, select or update duration time vary from short to long

Hi
I have a statement that normally takes a short time but sometimes
takes long. I think I have isolated the problem to being variations in
the time it takes to commit.
I have constructed a setup that somehow show my problem.
First execute the following script (45 lines):
use pubs
/****** Object: Stored Procedure dbo.spTestCommit Script Date:
16-05-2005 08:20:27 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[spTestCommit]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spTestCommit]
GO
/****** Object: StoredProcedure [dbo].[spTestCommit] Script Date
:
05/16/2007 08:32:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spTestCommit]
@.deltaValue int,
@.titleId varchar(50)
AS
declare @.tranCount int
select @.tranCount = @.@.trancount
if (@.tranCount = 0) begin
tran spTran
else begin
save tran spTran
end
UPDATE titles
SET ytd_sales = ytd_sales + @.deltaValue
WHERE title_id = @.titleId
if (@.tranCount = 0)
begin
commit tran
end
return 0
errorHandler:
rollback tran spTran
return 1
GO
Then start a trace enabling Execution plan and execute the statement.
I use the following:
exec spTestCommit 1, 'BU1032'
exec spTestCommit -1, 'BU1032'
(This will decrease and then increase some int value in the
pubs..titles table for a specific record)
Now my point is that when I execute those 2 lines, then they have a
duration of 10 or 20 ms most of the time. But all of the sudden I see
an entry with a duration of 210 ms.
Why does it take so much longer when that is the only thing I execute
on that entire database?!?
The output from my trace for the 210 ms run is:
Execution Plan Execution Tree
--
Clustered Index Update(OBJECT[pubs].[dbo].[titles].
[UPKCL_titleidind]), SET[titles].[ytd_sales]=[Expr1004]),
DEFINE:
([Expr1004]=[titles].[ytd_sales]+[@.deltaValue]), WHERE[
;titles].
[title_id]=[@.titleId]))
SQL Query Analyzer usr PC\usr 2756 55 2007-05-16 13:11:23.553
Execution Plan Execution Tree
--
Clustered Index Update(OBJECT[pubs].[dbo].[titles].
[UPKCL_titleidind]), SET[titles].[ytd_sales]=[Expr1004]),
DEFINE:
([Expr1004]=[titles].[ytd_sales]+[@.deltaValue]), WHERE[
;titles].
[title_id]=[@.titleId]))
SQL Query Analyzer usr PC\usr 2756 55 2007-05-16 13:11:23.703
SQL:BatchCompleted exec spTestCommit 1, 'BU1032'
exec spTestCommit -1, 'BU1032'
SQL Query Analyzer usr PC\usr 0 18 0 210 2756 55 2
007-05-16
13:11:23.553 2007-05-16 13:11:23.763
In my real world setup I have a stored procedure that normally takes
0-20 ms but have been seen taking 30000 ms or even more... I would
very much apreciate some hints as to why it vary so much...
Thanks
Resist> Now my point is that when I execute those 2 lines, then they have a
> duration of 10 or 20 ms most of the time. But all of the sudden I see
> an entry with a duration of 210 ms.
> Why does it take so much longer when that is the only thing I execute
> on that entire database?!?
SQL Server must wait until the COMMIT log write is physically complete
before completing the statement. If a CHECKPOINT is writing lots of data
and data and log files are on the same physical disk, this can prolong the
COMMIT statement duration. This is one reason why it's a Best Practice to
place data and log on different disks. You can monitor checkpoints in
perfmon to see the correlation.

> In my real world setup I have a stored procedure that normally takes
> 0-20 ms but have been seen taking 30000 ms or even more... I would
> very much apreciate some hints as to why it vary so much...
30+ seconds is excessive and probably due to a different reason, assuming
your I/O subsystem is adequately sized for your workload. A more likely
cause is blocking. Run sp_who at the time of the problem to see if that's
the case. An occasional long-running query or transaction may be the
culprit.
Hope this helps.
Dan Guzman
SQL Server MVP
"resist" <dba@.godhund.dk> wrote in message
news:1179315894.009631.298820@.y80g2000hsf.googlegroups.com...
> Hi
> I have a statement that normally takes a short time but sometimes
> takes long. I think I have isolated the problem to being variations in
> the time it takes to commit.
> I have constructed a setup that somehow show my problem.
> First execute the following script (45 lines):
> use pubs
> /****** Object: Stored Procedure dbo.spTestCommit Script Date:
> 16-05-2005 08:20:27 ******/
> if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
> [spTestCommit]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
> drop procedure [dbo].[spTestCommit]
> GO
> /****** Object: StoredProcedure [dbo].[spTestCommit] Script Da
te:
> 05/16/2007 08:32:43 ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE PROCEDURE [dbo].[spTestCommit]
> @.deltaValue int,
> @.titleId varchar(50)
>
> AS
> declare @.tranCount int
> select @.tranCount = @.@.trancount
> if (@.tranCount = 0) begin
> tran spTran
> else begin
> save tran spTran
> end
> UPDATE titles
> SET ytd_sales = ytd_sales + @.deltaValue
> WHERE title_id = @.titleId
>
> if (@.tranCount = 0)
> begin
> commit tran
> end
> return 0
> errorHandler:
> rollback tran spTran
> return 1
> GO
>
>
> Then start a trace enabling Execution plan and execute the statement.
> I use the following:
> exec spTestCommit 1, 'BU1032'
> exec spTestCommit -1, 'BU1032'
> (This will decrease and then increase some int value in the
> pubs..titles table for a specific record)
>
> Now my point is that when I execute those 2 lines, then they have a
> duration of 10 or 20 ms most of the time. But all of the sudden I see
> an entry with a duration of 210 ms.
> Why does it take so much longer when that is the only thing I execute
> on that entire database?!?
> The output from my trace for the 210 ms run is:
> Execution Plan Execution Tree
> --
> Clustered Index Update(OBJECT[pubs].[dbo].[titles].
> [UPKCL_titleidind]), SET[titles].[ytd_sales]=[Expr1004])
, DEFINE:
> ([Expr1004]=[titles].[ytd_sales]+[@.deltaValue]), WHERE&#
91;titles].
> [title_id]=[@.titleId]))
> SQL Query Analyzer usr PC\usr 2756 55 2007-05-16 13:11:23.553
> Execution Plan Execution Tree
> --
> Clustered Index Update(OBJECT[pubs].[dbo].[titles].
> [UPKCL_titleidind]), SET[titles].[ytd_sales]=[Expr1004])
, DEFINE:
> ([Expr1004]=[titles].[ytd_sales]+[@.deltaValue]), WHERE&#
91;titles].
> [title_id]=[@.titleId]))
> SQL Query Analyzer usr PC\usr 2756 55 2007-05-16 13:11:23.703
> SQL:BatchCompleted exec spTestCommit 1, 'BU1032'
> exec spTestCommit -1, 'BU1032'
> SQL Query Analyzer usr PC\usr 0 18 0 210 2756 55 2007-05-16
> 13:11:23.553 2007-05-16 13:11:23.763
>
> In my real world setup I have a stored procedure that normally takes
> 0-20 ms but have been seen taking 30000 ms or even more... I would
> very much apreciate some hints as to why it vary so much...
> Thanks
> Resist
>

Commit, select or update duration time vary from short to long

Hi
I have a statement that normally takes a short time but sometimes
takes long. I think I have isolated the problem to being variations in
the time it takes to commit.
I have constructed a setup that somehow show my problem.
First execute the following script (45 lines):
use pubs
/****** Object: Stored Procedure dbo.spTestCommit Script Date:
16-05-2005 08:20:27 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[spTestCommit]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spTestCommit]
GO
/****** Object: StoredProcedure [dbo].[spTestCommit] Script Date:
05/16/2007 08:32:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spTestCommit]
@.deltaValue int,
@.titleId varchar(50)
AS
declare @.tranCount int
select @.tranCount = @.@.trancount
if (@.tranCount = 0) begin
tran spTran
else begin
save tran spTran
end
UPDATE titles
SET ytd_sales = ytd_sales + @.deltaValue
WHERE title_id = @.titleId
if (@.tranCount = 0)
begin
commit tran
end
return 0
errorHandler:
rollback tran spTran
return 1
GO
Then start a trace enabling Execution plan and execute the statement.
I use the following:
exec spTestCommit 1, 'BU1032'
exec spTestCommit -1, 'BU1032'
(This will decrease and then increase some int value in the
pubs..titles table for a specific record)
Now my point is that when I execute those 2 lines, then they have a
duration of 10 or 20 ms most of the time. But all of the sudden I see
an entry with a duration of 210 ms.
Why does it take so much longer when that is the only thing I execute
on that entire database?!?
The output from my trace for the 210 ms run is:
Execution PlanExecution Tree
Clustered Index Update(OBJECT[pubs].[dbo].[titles].
[UPKCL_titleidind]), SET[titles].[ytd_sales]=[Expr1004]), DEFINE:
([Expr1004]=[titles].[ytd_sales]+[@.deltaValue]), WHERE[titles].
[title_id]=[@.titleId]))
SQL Query AnalyzerusrPC\usr2756552007-05-16 13:11:23.553
Execution PlanExecution Tree
Clustered Index Update(OBJECT[pubs].[dbo].[titles].
[UPKCL_titleidind]), SET[titles].[ytd_sales]=[Expr1004]), DEFINE:
([Expr1004]=[titles].[ytd_sales]+[@.deltaValue]), WHERE[titles].
[title_id]=[@.titleId]))
SQL Query AnalyzerusrPC\usr2756552007-05-16 13:11:23.703
SQL:BatchCompletedexec spTestCommit 1, 'BU1032'
exec spTestCommit -1, 'BU1032'
SQL Query AnalyzerusrPC\usr01802102756552007-05-16
13:11:23.5532007-05-16 13:11:23.763
In my real world setup I have a stored procedure that normally takes
0-20 ms but have been seen taking 30000 ms or even more... I would
very much apreciate some hints as to why it vary so much...
Thanks
Resist
> Now my point is that when I execute those 2 lines, then they have a
> duration of 10 or 20 ms most of the time. But all of the sudden I see
> an entry with a duration of 210 ms.
> Why does it take so much longer when that is the only thing I execute
> on that entire database?!?
SQL Server must wait until the COMMIT log write is physically complete
before completing the statement. If a CHECKPOINT is writing lots of data
and data and log files are on the same physical disk, this can prolong the
COMMIT statement duration. This is one reason why it's a Best Practice to
place data and log on different disks. You can monitor checkpoints in
perfmon to see the correlation.

> In my real world setup I have a stored procedure that normally takes
> 0-20 ms but have been seen taking 30000 ms or even more... I would
> very much apreciate some hints as to why it vary so much...
30+ seconds is excessive and probably due to a different reason, assuming
your I/O subsystem is adequately sized for your workload. A more likely
cause is blocking. Run sp_who at the time of the problem to see if that's
the case. An occasional long-running query or transaction may be the
culprit.
Hope this helps.
Dan Guzman
SQL Server MVP
"resist" <dba@.godhund.dk> wrote in message
news:1179315894.009631.298820@.y80g2000hsf.googlegr oups.com...
> Hi
> I have a statement that normally takes a short time but sometimes
> takes long. I think I have isolated the problem to being variations in
> the time it takes to commit.
> I have constructed a setup that somehow show my problem.
> First execute the following script (45 lines):
> use pubs
> /****** Object: Stored Procedure dbo.spTestCommit Script Date:
> 16-05-2005 08:20:27 ******/
> if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
> [spTestCommit]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
> drop procedure [dbo].[spTestCommit]
> GO
> /****** Object: StoredProcedure [dbo].[spTestCommit] Script Date:
> 05/16/2007 08:32:43 ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE PROCEDURE [dbo].[spTestCommit]
> @.deltaValue int,
> @.titleId varchar(50)
>
> AS
> declare @.tranCount int
> select @.tranCount = @.@.trancount
> if (@.tranCount = 0) begin
> tran spTran
> else begin
> save tran spTran
> end
> UPDATE titles
> SET ytd_sales = ytd_sales + @.deltaValue
> WHERE title_id = @.titleId
>
> if (@.tranCount = 0)
> begin
> commit tran
> end
> return 0
> errorHandler:
> rollback tran spTran
> return 1
> GO
>
>
> Then start a trace enabling Execution plan and execute the statement.
> I use the following:
> exec spTestCommit 1, 'BU1032'
> exec spTestCommit -1, 'BU1032'
> (This will decrease and then increase some int value in the
> pubs..titles table for a specific record)
>
> Now my point is that when I execute those 2 lines, then they have a
> duration of 10 or 20 ms most of the time. But all of the sudden I see
> an entry with a duration of 210 ms.
> Why does it take so much longer when that is the only thing I execute
> on that entire database?!?
> The output from my trace for the 210 ms run is:
> Execution Plan Execution Tree
> --
> Clustered Index Update(OBJECT[pubs].[dbo].[titles].
> [UPKCL_titleidind]), SET[titles].[ytd_sales]=[Expr1004]), DEFINE:
> ([Expr1004]=[titles].[ytd_sales]+[@.deltaValue]), WHERE[titles].
> [title_id]=[@.titleId]))
> SQL Query Analyzer usr PC\usr 2756 55 2007-05-16 13:11:23.553
> Execution Plan Execution Tree
> --
> Clustered Index Update(OBJECT[pubs].[dbo].[titles].
> [UPKCL_titleidind]), SET[titles].[ytd_sales]=[Expr1004]), DEFINE:
> ([Expr1004]=[titles].[ytd_sales]+[@.deltaValue]), WHERE[titles].
> [title_id]=[@.titleId]))
> SQL Query Analyzer usr PC\usr 2756 55 2007-05-16 13:11:23.703
> SQL:BatchCompleted exec spTestCommit 1, 'BU1032'
> exec spTestCommit -1, 'BU1032'
> SQL Query Analyzer usr PC\usr 0 18 0 210 2756 55 2007-05-16
> 13:11:23.553 2007-05-16 13:11:23.763
>
> In my real world setup I have a stored procedure that normally takes
> 0-20 ms but have been seen taking 30000 ms or even more... I would
> very much apreciate some hints as to why it vary so much...
> Thanks
> Resist
>

Commit and Rollback on a batch

Hi!

I am using VB.NET 2005 and/or SQL Server 2005 studio manager.

I have a string of about 20 or 30 inserts updates and deletes. I want to process all or nothing. If there is an error that prevents a single transaction from completeing, i want to roll back the entire batch.

One solution i read is to test fot @.@.error after each statement. This is not desirable as i receive the batch as a single string already made. I would have to separate all the statements and insert the error testing myself.

I would prefere to simply execute the batch as an all or nothing batch.

Certainly this is a common request. But the only solutions i can find involve extensive re-working of the source batch of transactions. I may have up to 100 statements in my batch.

Any Ideas?

Thank you

Jerry Cicierega

using System.Transactions;

...

using (TransactionScope ts = new TransactionScope())
{
using (SqlConnection con = new SqlConnection())
{
using (SqlCommand cmd = new SqlCommand())
{
// Do stuff with your cmd object here such as running the aforementioned batch statements
}
}
ts.Complete()
}

If an error occurs during the processing, the whole thing will be rolled back. Of course, fill in the stuff you need for the connection and command objects in the using statements.

commit after 10000 rows

Hi
I need to update a vary large amount of records (3 million). I don't want to wait till the end of the update to commit.
do you know how can I commit the transaction after, lets say 10000 rows, and then continue to the next 10000... and so on ?
I think it's one of the SET commands but I can't remember it.set rowcount 10000

while (still have records to be updated)
begin
update records
end

set rowcount 0

Originally posted by aig
Hi
I need to update a vary large amount of records (3 million). I don't want to wait till the end of the update to commit.
do you know how can I commit the transaction after, lets say 10000 rows, and then continue to the next 10000... and so on ?
I think it's one of the SET commands but I can't remember it.

Combo box to exclude blank input

Hi
I have a combo box that displays a list of company names. Some entries do
not have a company name on the form so the textbox remains blank. Therefore
in the combo box there are alot of blanks in the list. How do I get the
combo box to just list out the company names without the blanks?
At the moment I have this:
SELECT Contacts.ID, Contacts.CompanyName
FROM Contacts;
Thanks in advance
SeanThe following will exclude NULL or blank CompanyName rows:
SELECT Contacts.ID, Contacts.CompanyName
FROM Contacts
WHERE
Contacts.CompanyName IS NOT NULL AND
Contacts.CompanyName <> '';
Hope this helps.
Dan Guzman
SQL Server MVP
"Sean" <Sean@.discussions.microsoft.com> wrote in message
news:9930ACDB-8E70-45B8-94B1-89A8DF57B60F@.microsoft.com...
> Hi
> I have a combo box that displays a list of company names. Some entries do
> not have a company name on the form so the textbox remains blank.
> Therefore
> in the combo box there are alot of blanks in the list. How do I get the
> combo box to just list out the company names without the blanks?
> At the moment I have this:
> SELECT Contacts.ID, Contacts.CompanyName
> FROM Contacts;
> Thanks in advance
> Sean|||Isn't there a Company table that, by definition, wouldn't have empty
company names?
If not, then one should be added - basic normalization.
Sean wrote:
> Hi
> I have a combo box that displays a list of company names. Some entries do
> not have a company name on the form so the textbox remains blank. Therefor
e
> in the combo box there are alot of blanks in the list. How do I get the
> combo box to just list out the company names without the blanks?
> At the moment I have this:
> SELECT Contacts.ID, Contacts.CompanyName
> FROM Contacts;
> Thanks in advance
> Sean