Comparing dates with today dates

I want to know if there is a way to compare dates in the sql statement with dates that I input into a database and todays date. the datatype that I'm using is smalldatetime.
The statement I used is:
Select Date from Table where Date > 'Today.now'
I get an error
Could this be done or is there another approach?Select Date from Table where Date > getdate()
If you just want to match dates and not times:
Select Date from Table where convert(varchar(10), Date, 101) > convert(varchar(10), getDate(), 101)

Nick

Comparing dates in Case statement

hi

I am having problem in converting dates either to varchar or integer

I need to compare (just month and date part , not the year)

If

mm/dd (of current date) i.e 07/12 > 06/30

i.e if today's month and date is greater than june 30th then perform task A

if today's month and date is less than july 1st perform task B

i.e

07/12 < 07/01

please help

Thanks

Code Snippet

select case when (month(getdate())* 100 + day(getdate())) > 0630

then ... -- task a

else ... --task b

end

from ....

|||

Thank you very much , that helped

But I have a new problem

I am trying to execute task A

as below

SELECT
case
when (month(getdate())* 100 + day(getdate())) > 0630
then
(select * from dbo.V_FUN_SCOPES_LEVELS0506)
end

It gives error

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

|||

You need to use IF syntax instead of CASE syntax; give a look to the related article in books online. That should look something like:

Code Snippet

if (month(getdate())* 100 + day(getdate())) > 0630
select * from dbo.V_FUN_SCOPES_LEVELS0506

Also, beware of using "SELECT *" syntax from within a stored procedure or function.

|||

yea, it won't work in that manner.

Can you elaborate a little more on what you're trying to do and how it is going to be used.

From this is looks like you'll need a stored procedure or maybe a function.

|||

I think in you're probably just looking at some standard IF...ELSE control of flow.

IF (SELECT (month(getdate())* 100 + day(getdate()))) > 0630

PRINT 'do this'

ELSE

PRINT 'do that'

|||

Hi

the IF syntax works independently but If i try to create a store proc as below it gives error

CREATE PROCEDURE [dbo].[P_Latest]

AS

IF (month(getdate())* 100 + day(getdate())) > 0630
BEGIN
select * from dbo.V_FUN_SCOPES_LEVELS0506

END

ELSE
IF (month(getdate())* 100 + day(getdate())) < 0701
BEGIN
select * from dbo.V_FUN_SCOPES_LEVELS0506
END

G0

I need to display select * from PROCEDURE [dbo].[P_Latest]

i.e Exec PROCEDURE [dbo].[P_Latest] from SQL reporting services

P.S. Here the view dbo.V_FUN_SCOPES_LEVELS0506 is as below

I need the subquery for Display purpose

(select
a.student_id,
a.at_sss_read_score as Score_06,
b.at_sss_read_score as Score_07,
a.at_test_month + '/' + '20' + a.fcat_test_year as Date_06,
b.at_test_month + '/' + '20' + b.fcat_test_year as Date_07,
a.at_test_month as Month_06,
b.at_test_month as Month_07,
a.at_test_year as Year_06,
b.at_test_year as Year_07,
a.at_sss_read_level as Level_06,
b.at_sss_read_level as Level_07

from
(
(select student_id, at_sss_read_score ,at_test_month,at_test_year,at_sss_read_level
from
DW_STUDENT.DBO.AT_TEST
where AT_TEST_YEAR = right (year (getdate())-1 , 2)) a
--AND STUDENT_ID IN ('0011307')) a

full join

(select student_id, at_sss_read_score ,at_test_month,at_test_year,at_sss_read_level
from
DW_STUDENT.DBO.AT_TEST
where AT_TEST_YEAR = right (year (getdate()),2 )) b
--AND STUDENT_ID IN ('0011307')) b

on a.student_id = b.student_id
)

where
a.at_test_year is not null
and b.at_test_year is not null

)

|||

This might work:

Code Snippet

CREATE PROCEDURE [dbo].[P_Latest]
AS

IF (month(getdate())* 100 + day(getdate())) > 0630
select * from dbo.V_FUN_SCOPES_LEVELS0506
ELSE
select * from dbo.V_FUN_SCOPES_LEVELS0506

G0

But I feel like I am missing something major. I see no point to the IF statement nor the ELSE Statement. Also, the SELECT * from inside a procedure is a bad idea because the MEANING of the SELECT * statement is determined at compile time and not at run time. You should explicitly list the columns that you return.

I feel like I am going wrong with this. Anyone? Help?

|||

In your Reporting Services dataset, just set the command type to StoredProcedure and the QueryString to [dbo].[P_Latest].

That is the equivalent of select * from ...

|||

I chose to believe that that was just test code

|||

Kent Waldrop Jl07 wrote:

This might work:

Code Snippet

CREATE PROCEDURE [dbo].[P_Latest]
AS

IF (month(getdate())* 100 + day(getdate())) > 0630
select * from dbo.V_FUN_SCOPES_LEVELS0506
ELSE
select * from dbo.V_FUN_SCOPES_LEVELS0506

G0

But I feel like I am missing something major. I see no point to the IF statement nor the ELSE Statement. Also, the SELECT * from inside a procedure is a bad idea because the MEANING of the SELECT * statement is determined at compile time and not at run time. You should explicitly list the columns that you return.

I feel like I am going wrong with this. Anyone? Help?

I've always heard this... "...SELECT * from inside a procedure is a bad idea.." without really understanding why. I apologize if i'm deviating from the point but...

when you say compile time and not run time, do you mean if a store procedure is compiled with select * then subsequently, the table changes (ie. column added) the sp will return everything without the extra column?

|||Yes, that is exactly what I mean; it is for that reason that I will sometimes say that use of "SELECT *" leaves "land mines" that blow up sometime later.

comparing data in two different databases

I am trying to learn how that I can write a select statement extracting data
from two different databases. Is this possible? I imagine that I can do it
with SSRS but I was looking for something very quick. For example, my client
wants to see a query of prices from 2006 prices versus 2007 prices which are
stored in different databases.
Thank you!
You can query across databases on the same server by using
Database.dbo.tablename
If the databases reside on different SQL Servers you can use
Servername.Database.dbo.tablename (Linked Servers will be required first)
Ryan
"Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
news:%23L9xvRcWHHA.392@.TK2MSFTNGP06.phx.gbl...
>I am trying to learn how that I can write a select statement extracting
>data from two different databases. Is this possible? I imagine that I can
>do it with SSRS but I was looking for something very quick. For example, my
>client wants to see a query of prices from 2006 prices versus 2007 prices
>which are stored in different databases.
> Thank you!
>
|||Your pool won't run out of connections but it's possible that if you're
blowing out of the program without closing the connection you could have so
many connections open that SQL Express runs low on memory and slows down
enough so connection time out. It could also be that you're single stepping
through logic so it's taking an unnaturally long time for operations to
complete so they time out. I sometime bump up the timeouts in the
connections string if I'm going to be debugging.
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
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:%235yfjVcWHHA.5108@.TK2MSFTNGP06.phx.gbl...
> You can query across databases on the same server by using
> Database.dbo.tablename
> If the databases reside on different SQL Servers you can use
> Servername.Database.dbo.tablename (Linked Servers will be required first)
>
> --
> Ryan
> "Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
> news:%23L9xvRcWHHA.392@.TK2MSFTNGP06.phx.gbl...
>
|||Sorry, replied to the wrong post - off by one error.
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
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:OTDDNtcWHHA.1200@.TK2MSFTNGP02.phx.gbl...
> Your pool won't run out of connections but it's possible that if you're
> blowing out of the program without closing the connection you could have
> so many connections open that SQL Express runs low on memory and slows
> down enough so connection time out. It could also be that you're single
> stepping through logic so it's taking an unnaturally long time for
> operations to complete so they time out. I sometime bump up the timeouts
> in the connections string if I'm going to be debugging.
> --
> 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
> "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:%235yfjVcWHHA.5108@.TK2MSFTNGP06.phx.gbl...
>

comparing data in two different databases

I am trying to learn how that I can write a select statement extracting data
from two different databases. Is this possible? I imagine that I can do it
with SSRS but I was looking for something very quick. For example, my client
wants to see a query of prices from 2006 prices versus 2007 prices which are
stored in different databases.
Thank you!You can query across databases on the same server by using
Database.dbo.tablename
If the databases reside on different SQL Servers you can use
Servername.Database.dbo.tablename (Linked Servers will be required first)
Ryan
"Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
news:%23L9xvRcWHHA.392@.TK2MSFTNGP06.phx.gbl...
>I am trying to learn how that I can write a select statement extracting
>data from two different databases. Is this possible? I imagine that I can
>do it with SSRS but I was looking for something very quick. For example, my
>client wants to see a query of prices from 2006 prices versus 2007 prices
>which are stored in different databases.
> Thank you!
>|||Your pool won't run out of connections but it's possible that if you're
blowing out of the program without closing the connection you could have so
many connections open that SQL Express runs low on memory and slows down
enough so connection time out. It could also be that you're single stepping
through logic so it's taking an unnaturally long time for operations to
complete so they time out. I sometime bump up the timeouts in the
connections string if I'm going to be debugging.
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
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:%235yfjVcWHHA.5108@.TK2MSFTNGP06.phx.gbl...
> You can query across databases on the same server by using
> Database.dbo.tablename
> If the databases reside on different SQL Servers you can use
> Servername.Database.dbo.tablename (Linked Servers will be required first)
>
> --
> Ryan
> "Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
> news:%23L9xvRcWHHA.392@.TK2MSFTNGP06.phx.gbl...
>|||Sorry, replied to the wrong post - off by one error.
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
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:OTDDNtcWHHA.1200@.TK2MSFTNGP02.phx.gbl...
> Your pool won't run out of connections but it's possible that if you're
> blowing out of the program without closing the connection you could have
> so many connections open that SQL Express runs low on memory and slows
> down enough so connection time out. It could also be that you're single
> stepping through logic so it's taking an unnaturally long time for
> operations to complete so they time out. I sometime bump up the timeouts
> in the connections string if I'm going to be debugging.
> --
> 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
> "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:%235yfjVcWHHA.5108@.TK2MSFTNGP06.phx.gbl...
>|||THANK YOU! Just wasn't getting my syntax right.
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:%235yfjVcWHHA.5108@.TK2MSFTNGP06.phx.gbl...
> You can query across databases on the same server by using
> Database.dbo.tablename
> If the databases reside on different SQL Servers you can use
> Servername.Database.dbo.tablename (Linked Servers will be required first)
>
> --
> Ryan
> "Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
> news:%23L9xvRcWHHA.392@.TK2MSFTNGP06.phx.gbl...
>

comparing data in two different databases

I am trying to learn how that I can write a select statement extracting data
from two different databases. Is this possible? I imagine that I can do it
with SSRS but I was looking for something very quick. For example, my client
wants to see a query of prices from 2006 prices versus 2007 prices which are
stored in different databases.
Thank you!You can query across databases on the same server by using
Database.dbo.tablename
If the databases reside on different SQL Servers you can use
Servername.Database.dbo.tablename (Linked Servers will be required first)
Ryan
"Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
news:%23L9xvRcWHHA.392@.TK2MSFTNGP06.phx.gbl...
>I am trying to learn how that I can write a select statement extracting
>data from two different databases. Is this possible? I imagine that I can
>do it with SSRS but I was looking for something very quick. For example, my
>client wants to see a query of prices from 2006 prices versus 2007 prices
>which are stored in different databases.
> Thank you!
>|||Your pool won't run out of connections but it's possible that if you're
blowing out of the program without closing the connection you could have so
many connections open that SQL Express runs low on memory and slows down
enough so connection time out. It could also be that you're single stepping
through logic so it's taking an unnaturally long time for operations to
complete so they time out. I sometime bump up the timeouts in the
connections string if I'm going to be debugging.
--
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
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:%235yfjVcWHHA.5108@.TK2MSFTNGP06.phx.gbl...
> You can query across databases on the same server by using
> Database.dbo.tablename
> If the databases reside on different SQL Servers you can use
> Servername.Database.dbo.tablename (Linked Servers will be required first)
>
> --
> Ryan
> "Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
> news:%23L9xvRcWHHA.392@.TK2MSFTNGP06.phx.gbl...
>>I am trying to learn how that I can write a select statement extracting
>>data from two different databases. Is this possible? I imagine that I can
>>do it with SSRS but I was looking for something very quick. For example,
>>my client wants to see a query of prices from 2006 prices versus 2007
>>prices which are stored in different databases.
>> Thank you!
>|||Sorry, replied to the wrong post - off by one error.
--
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
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:OTDDNtcWHHA.1200@.TK2MSFTNGP02.phx.gbl...
> Your pool won't run out of connections but it's possible that if you're
> blowing out of the program without closing the connection you could have
> so many connections open that SQL Express runs low on memory and slows
> down enough so connection time out. It could also be that you're single
> stepping through logic so it's taking an unnaturally long time for
> operations to complete so they time out. I sometime bump up the timeouts
> in the connections string if I'm going to be debugging.
> --
> 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
> "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:%235yfjVcWHHA.5108@.TK2MSFTNGP06.phx.gbl...
>> You can query across databases on the same server by using
>> Database.dbo.tablename
>> If the databases reside on different SQL Servers you can use
>> Servername.Database.dbo.tablename (Linked Servers will be required first)
>>
>> --
>> Ryan
>> "Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
>> news:%23L9xvRcWHHA.392@.TK2MSFTNGP06.phx.gbl...
>>I am trying to learn how that I can write a select statement extracting
>>data from two different databases. Is this possible? I imagine that I can
>>do it with SSRS but I was looking for something very quick. For example,
>>my client wants to see a query of prices from 2006 prices versus 2007
>>prices which are stored in different databases.
>> Thank you!
>>
>|||THANK YOU! Just wasn't getting my syntax right.
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:%235yfjVcWHHA.5108@.TK2MSFTNGP06.phx.gbl...
> You can query across databases on the same server by using
> Database.dbo.tablename
> If the databases reside on different SQL Servers you can use
> Servername.Database.dbo.tablename (Linked Servers will be required first)
>
> --
> Ryan
> "Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
> news:%23L9xvRcWHHA.392@.TK2MSFTNGP06.phx.gbl...
>>I am trying to learn how that I can write a select statement extracting
>>data from two different databases. Is this possible? I imagine that I can
>>do it with SSRS but I was looking for something very quick. For example,
>>my client wants to see a query of prices from 2006 prices versus 2007
>>prices which are stored in different databases.
>> Thank you!
>

Comparing CASE statement products

The query I have written below works fine. However, I now want to uncomment the WHERE clause below to find entries where the PatientAge does not fall between the PAGBeginningAge and PAGEndingAge. However, when I uncomment the WHERE clause line I receive the following error message:

Msg 156, Level 15, State 1, Line 28

Incorrect syntax near the keyword 'FROM'.

SELECT ampfm.rpt_AdtVisit.PatientFullName, ampfm.rpt_AdtVisit.AdmitPriorityCode, ampfm.dct_AdmitPriorityType.AdmitPriorityTypeName,

ampfm.rpt_AdtVisit.AccountNumber, ampfm.rpt_PatientDemographics.PatientAge, ampfm.rpt_PatientDemographics.PatientAgeGroup,

ampfm.rpt_PatientDemographics.PatientSex,

CASE WHEN PatientAgeGroup = '0 - 14' THEN '0' ELSE

CASE WHEN PatientAgeGroup = '15 - 24' THEN '15' ELSE

CASE WHEN PatientAgeGroup = '25 - 34' THEN '25' ELSE

CASE WHEN PatientAgeGroup = '35 - 44' THEN '35' ELSE

CASE WHEN PatientAgeGroup = '45 - 54' THEN '45' ELSE

CASE WHEN PatientAgeGroup = '55 - 64' THEN '55' ELSE

CASE WHEN PatientAgeGroup = '65 - 74' THEN '65' ELSE

CASE WHEN PatientAgeGroup = '75 - 79' THEN '75' ELSE

CASE WHEN PatientAgeGroup = '80 - OVER' THEN '80'

END END END END END END END END END AS PAGBeginningAge,

CASE WHEN PatientAgeGroup = '0 - 14' THEN '14' ELSE

CASE WHEN PatientAgeGroup = '15 - 24' THEN '24' ELSE

CASE WHEN PatientAgeGroup = '25 - 34' THEN '34' ELSE

CASE WHEN PatientAgeGroup = '35 - 44' THEN '44' ELSE

CASE WHEN PatientAgeGroup = '45 - 54' THEN '54' ELSE

CASE WHEN PatientAgeGroup = '55 - 64' THEN '64' ELSE

CASE WHEN PatientAgeGroup = '65 - 74' THEN '74' ELSE

CASE WHEN PatientAgeGroup = '75 - 79' THEN '79' ELSE

CASE WHEN PatientAgeGroup = '80 - OVER' THEN '200'

END END END END END END END END END AS PAGEndingAge

--WHERE PatientAge NOT BETWEEN PAGBeginningAge AND PAGEndingAge

FROM ampfm.dct_AdmitPriorityType INNER JOIN

ampfm.rpt_AdtVisit ON ampfm.dct_AdmitPriorityType.AdmitPriorityTypeCode = ampfm.rpt_AdtVisit.AdmitPriorityCode INNER JOIN

ampfm.rpt_PatientDemographics ON ampfm.rpt_AdtVisit.RegNum = ampfm.rpt_PatientDemographics.RegNum

Two things:

First, your WHERE statment needs to come after the last ON condition.

second, you are not allowed to alias the "column/expression list" of the SELECT clause like this in MS SQL Server as you can do from DB2; you will need to include the entire case statement in your WHERE clause.

You might be able to create an inline TVF to make this complicated CASE statement a little easier to read.

Maybe something like:

Code Snippet

alter function dbo.ageRange
( @.arg_ageGroup varchar(12)
)
returns table
as
return
( select cast(parsename(replace(@.arg_ageGroup,' - ','.'),2)
as int)
as lowPart,
cast( case when parsename(replace(@.arg_ageGroup,' - ','.'),1)
= 'over'
then '200'
else parsename(replace(@.arg_ageGroup,' - ','.'),1)
end as int)
as highPart
)

go

declare @.ageGroup table ( ageGroup varchar(9))
insert into @.ageGroup
select '0 - 14' union all
select '15 - 24' union all
select '25 - 34' union all
select '35 - 44' union all
select '45 - 54' union all
select '55 - 64' union all
select '65 - 74' union all
select '75 - OVER'

select ageGroup,
lowPart,
highPart
from @.ageGroup
cross apply ageRange(ageGroup)

Another idea would be to put your ranges in a table that included (1) the range, (2) the low and (3) the high of the range. Then you could use a CROSS APPLY to fetch the desired numeric LOW and HIGH of the range.

|||

I can't believe I had the WHERE statement before the FROM statement; boy this has been a long day.

I placed the entire case statement in the WHERE clause and everything is working fine now. Thanks!

BTW, I am not familiar with inline TVF. What is it or where could I read up on it?

|||

( I edited my previous response to include it; please take a look. )

|||

Thanks! I may try something like that on my next project.

sqlsql

Comparing 2 records

I'm trying to write an SQL statement that would allow me to compare 2 records from the same table.

Basically, I have a table of events for an object with a column that contains when the event started but not when the event ended. An object in the table will have at least 2 events so I could take the start time of the second event to determine the end time of the first event. Can this be done using SQL? I know I can do it by writing a stored procedure and storing the results in a table, but was hoping not to have to do this.

Any help will be appreciated.select t1.id
, t1.starttime as starttime
, t2.starttime as endtime
from events t1
inner
join events t2
on t1.id = t2.id
and t1.starttime < t2.starttime use left outer join instead of inner if you want to pick up events that have started but not finished

rudy
http://r937.com/|||Thanks Rudy!|||Actually, the version of Oracle I am using did not support "inner join", so what ended up working was:

select t1.id, t1.date, t2.date
from event t1, event t2
where t1.id = t2.id
and t1.date < t2.date|||good one, and thanks for the update

i keep forgetting that there are still lots of companies and individuals out there who are in no hurry to install the latest and greatest software

e.g. i'm still on access 97 on my desktop, and have no plans to upgrade ever

by the way, what side does the plus sign go on if you wanted to join event t1 left outer event t2?

i could look it up, i guess...|||The (+) goes on the "outer" side of the join, i.e.

t1.id = t2.id (+)
and t1.starttime < t2.starttime (+)

Comparing 2 Columns Containing Null Values

Hi All.

I'm having some issues with what seems to be a simple update statement but is giving me grief when one or both of the columns I'm comparing are null. My statement (simplified) is as follows:-

UPDATE
TAB_A
SET
TAB_A.TRADCODE = TAB_B.TRADCODE
FROM
TADS_STAGE.DBO.UNCLBRAMDEPT TAB_B
JOIN
TADS.DBO.UNCLBRAMDEPT TAB_A
ON
TAB_B.BRANCODE = TAB_A.BRANCODE
AND
TAB_B.MERDCODE = TAB_A.MERDCODE
AND
(
TAB_B.TRADCODE <> TAB_A.TRADCODE
)

If either of the TRADCODE fields (or both) are null then the comparison fails to return the row to update. I've tried setting the ANSI_NULLS setting to off, this has no effect, presumably because it will only work when comparing a column to a variable or evaluating if the column is null itself.

I've considered using ISNULL, but if one of the columns happens to contain the value that I specify as the replacement value then the comparison will result true and not include the row.

I'd be grateful for any pointers!

Thanks in advance

Sorted it :)

I've used the ISNULL(exp,'') function, as I'm not bothered about updating nothing ('') to null and vice versa.

Just got to go back and alter all 60 scripts now

Nick

|||

Nick Colebourn wrote:

I've considered using ISNULL, but if one of the columns happens to contain the value that I specify as the replacement value then the comparison will result true and not include the row.

In this type of situation you would replace your ISNULL with some impossible calue that would never come up. Here are some other ideas:

1. You only want to update Tab_A with Tab_B's value if they're different. Why not just update it all the time?

Cons: Slower Update, More Locks, More overhead if set to full recovery mode.

UPDATE
TAB_A
SET
TAB_A.TRADCODE = TAB_B.TRADCODE
FROM
TADS_STAGE.DBO.UNCLBRAMDEPT TAB_B
JOIN
TADS.DBO.UNCLBRAMDEPT TAB_A
ON
TAB_B.BRANCODE = TAB_A.BRANCODE
AND
TAB_B.MERDCODE = TAB_A.MERDCODE

2. Make your JOIN even more complicated

UPDATE
TAB_A
SET
TAB_A.TRADCODE = TAB_B.TRADCODE
FROM
TADS_STAGE.DBO.UNCLBRAMDEPT TAB_B
JOIN
TADS.DBO.UNCLBRAMDEPT TAB_A
ON
TAB_B.BRANCODE = TAB_A.BRANCODE
AND
TAB_B.MERDCODE = TAB_A.MERDCODE
AND
(
(TAB_B.TRADCODE IS NULL AND TAB_A.TRADCODE IS NOT NULL)

OR (TAB_B.TRADCODE IS NOT NULL AND TAB_A.TRADCODE IS NULL)

OR TAB_B.TRADCODE <> TAB_A.TRADCODE
)

|||

Hi Jared,

Thanks for your reply. I think I'm going to take a multi faceted approach to this, some of my tables are only a few thousand rows, so I'll probably just do the full update, and some are 20 million rows or more, so I'll spend the time writing the intricate where clauses or experimenting with the ISNULL function.

Cheers for taking the time to respond.

Nick

compare two fields from same dataset in iif

hi,
This statement returns an "#error" on my report.
=sum(iif(Fields!current_fiscal_period.Value=Fields!fiscal_period.Value,Fields!shrink_dollars_positive.Value,0))
There is only one datatset for this report.
any clues?
thanks,
JustinGot it! I needed to Cint function
=sum(iif(Fields!current_fiscal_period.Value=Fields!fiscal_period.Value,
CInt(Fields!shrink_dollars_positive.Value),0))

Compare Strings

Thanks Julie

>--Original Message--
>Have you checked out the PATINDEX statement ?
>J
>fields,
>have
=[vbcol=seagreen]
>@.ContentID
always[vbcol=seagreen]
>.
>Here is an procedure/operator that returns string differences in each
direction between 2 strings. It can easily be used with sql server.
[url]http://beyondsql.blogspot.com/2007/06/dataphor-string-differences-operator.html[/u
rl]
Of course it would be nice if it entices you to explore Dataphor -

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

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, 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 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> 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 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 Update to SQL Database Table

The following code will not update and commit the update to a SQL Database Table. Now my where statement is looking for a Date field. Could this be the problem?

Dim DBConnAs SqlConnection

Dim DBAddAsNew SqlCommand

Dim strConnectAsString = ConfigurationManager.ConnectionStrings("ProtoCostConnectionString").ConnectionString

DBConn =New SqlConnection(strConnect)

'Update a existing row in the table

DBAdd.CommandText ="UPDATE [D12_MIS] SET [CSJ] = @.CSJ, [EST_DATE] = @.EST_DATE, [RECORD_LOCK_FLAG] = @.RECORD_LOCK_FLAG, [EST_CREATE_BY_NAME] = @.EST_CREATE_BY_NAME, [EST_REVIEW_BY_NAME] = @.EST_REVIEW_BY_NAME, [m2_1] = @.m2_1, [m2_2_date] = @.m2_2_date, [m2_3_date] = @.m2_3_date, [m2_4_date] = @.m2_4_date, [m2_5] = @.m2_5, [m3_1a] = @.m3_1a, [m3_1b] = @.m3_1b, [m3_2a] = @.m3_2a, [m3_2b] = @.m3_2b, [m3_3a] = @.m3_3a, [m3_3b] = @.m3_3b WHERE [EST_DATE] = " & EstDateText

With DBAdd.Parameters

.AddWithValue("@.CSJ", pvCSJ.Text)

.AddWithValue("@.EST_DATE", tmp1Date)

.AddWithValue("@.RECORD_LOCK_FLAG", tmpRecordLock)

.AddWithValue("@.EST_CREATE_BY_NAME", CheckedCreator)

.AddWithValue("@.EST_REVIEW_BY_NAME", CheckedReviewer)

.AddWithValue("@.m2_1", vb2_1)

.AddWithValue("@.m2_2_date", tmp2Date)

.AddWithValue("@.m2_3_date", tmp3Date)

.AddWithValue("@.m2_4_date", tmp4Date)

.AddWithValue("@.m2_5", vb2_5)

.AddWithValue("@.m3_1a", vb3_1a)

.AddWithValue("@.m3_1b", vb3_1b)

.AddWithValue("@.m3_2a", vb3_2a)

.AddWithValue("@.m3_2b", vb3_2b)

.AddWithValue("@.m3_3a", vb3_3a)

.AddWithValue("@.m3_3b", vb3_3b)

EndWith

DBAdd.Connection = DBConn

DBAdd.Connection.Open()

Dim rowsAffectedAsInteger = 0

Try

rowsAffected = DBAdd.ExecuteNonQuery

Catch exAs Exception

tb2_2.Text = ex.ToString()

Finally

DBAdd.Connection.Close()

EndTry

tb2_1.Text = rowsAffected

Yes. Since a date column (datetime) consists of both date and time portion, you WHERE clause will attempt to match both the date and the time values. Chances are, you don't have the time portion specified (or a different one), so nothing will be matched. Instead, try to use the format:

WHERE [EST_DATE] >= '02/18/2006 12:00:00 AM' AND [EST_DATE] <= '02/18/2006 11:59:59 PM'

or

WHERE [EST_DATE] >= '02/18/2006' AND [EST_DATE]< '02/19/2006'

Notice that when yo specify only the date portion, the time portion defaults to 12:00:00 AM, so the second method uses less-than (<) the following date of the date you're trying to filter.|||

WHERE [EST_DATE] = " & EstDateText .

I think the problem is that you have not enclosed the date with single quotes. Try the following one

WHERE [EST_DATE] = '" & EstDateText & "'"

|||That was the problem of missing quotes, thanks!|||Change the date to a parameter, and you wouldn't have had that problem.

Comments in SQL

Hi,

I am new to MS SQL Server 2005. I used MySQL.

Now my doubt is how to put a comment in SQL statement, where i am using query editor in SQL Management Studio.

Rgds.,

Aazad

Hi,

Use -- for single line comments and

/*
For multiline comments
*/

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Comments

Does anyone know how to add a comment in a sql statement in reporting
services, all the normal types e.g
/*comment*/
and
-- comment
Dont work, it just removes then as soon as you move away from the sql
window.
could maybe be a menu choice but i cant find it!
Help please
Steve DActually, it seems that they don't go away properly. If you add a comment,
it will be visible in the code view. It might be visible if you open the
data set box where you change the data source etc.
Still, it's quite silly. It's not very usable.
Kaisa M. Lindahl Lervik
"Steve Dearman" <steve.dearman@.grant.co.uk> wrote in message
news:uL4pLQttGHA.4852@.TK2MSFTNGP02.phx.gbl...
> Does anyone know how to add a comment in a sql statement in reporting
> services, all the normal types e.g
> /*comment*/
> and
> -- comment
> Dont work, it just removes then as soon as you move away from the sql
> window.
> could maybe be a menu choice but i cant find it!
> Help please
> Steve D
>

Commandtext problem

I am trying to get the return value from the select statement and store it into a variable
does anyone know whats wrong with my code?

Private Function check_login(ByVal login As String) As String

dim dbCon As SqlConnection = New SqlConnection
Dim dbCmd As SqlCommand = New SqlCommand

Dim returnuser As Integer

dbCon.ConnectionString = _
"Data Source=localhost;" + _
"Initial Catalog=registeruser;" + _
"User ID=int422;" + _
"Password=int422"

dbCon.Open()

dbCmd.Connection = dbCon
dbCmd.CommandText = "SELECT count(login_id) from users where login_id=@.login"
dbCmd.CommandType = CommandType.Text

returnuser = dbCmd.ExecuteScalar

Return returnuser

dbCon.Close()

SELECT count(login_id) from users where login_id=@.login

I don't see where you are setting the value for the @.login parameter. Also the dbCon.Close() should be immediately after the ExecuteScalar() call and, in particular, before the return statement.

Command to display SQL Statements

Does anyone know the command to display the whole SQL statement for a
connection after apply sp3?
Thanks,
LijunTake a look at the fn_get_sql topic in books online, assuming you updated...
"Lijun Zhang" <nospam@.nospam.nospam> wrote in message
news:#UPomeMnDHA.2592@.TK2MSFTNGP10.phx.gbl...
> Does anyone know the command to display the whole SQL statement for a
> connection after apply sp3?
> Thanks,
> Lijun
>|||Hi Lijun,
I agree with Aaron, you can use the fn_get_sql function to retrieve the SQL handle (sql_handle
column of the sysprocesses) and help you diagnose problematic processes.
You can also use "DBCC INPUTBUFFER" to easily display the LAST statement sent from a
client. The system process ID (SPID) for the user connection can be displayed in the output of
the sp_who system stored procedure.
Does that answer your question? Please feel free to post in the group if this solves your
problem or if you would like further assistance.
Best regards,
Billy Yao
Microsoft Online Partner Support

command text question?

i have one question in my select statement i'm selecting two things username, password will the dbCmd.ExecuteScalar return username,password in one row or together in the returnuser variable. i wanna return them both in two separate variables or do i need to different select statements?

thanks

Private Function check_login(ByVal login As String) As String

Dim dbCon As SqlConnection = New SqlConnection
Dim dbCmd As SqlCommand = New SqlCommand

Dim returnuser As Integer

dbCon.ConnectionString = _
"Data Source=localhost;" + _
"Initial Catalog=registeruser;" + _
"User ID=int422;" + _
"Password=int422"

dbCon.Open()

dbCmd.Connection = dbCon
dbCmd.CommandText = "SELECT login,password from users where login_id= @.login"
dbCmd.CommandType = CommandType.Text

With (dbCmd.Parameters)
.Add("@.login", SqlDbType.VarChar, 64).Value = login

End With

returnuser = dbCmd.ExecuteScalar

dbCon.Close()

Return returnuser

End FunctionExecuteScalar will only return the first row of the first column. You will either need separate ExecuteScalar calls with separate queries (not very efficient), use a datareader, fill a datatable, or use a stored procedure and have the username and password returned as output parameters. The easiest is probably a datareader:

// Sorry C# but you should get the idea for VB.Net
string returnuser = "";
string password = "";
SqlDataReader dr = dbCmd.ExecuteReader();
while( dr.Read() ){
returnuser = dr.GetString(0);
password = dr.GetString(1);
break; // just to make sure you only get one record
}

The stored procedure with output parameters is probably the most efficient (fastest) but it probably makes no practical difference.