Comparing fields within a select

Hi there,

Is it possible for a query to return all the records in a table which have the same value in a given column?

SELECT * FROM table1 WHERE field1 = 2

..would return all the records from table1 which have a field1 value of 2 but I don't want to specify the value - just that all the records have the same value.

Cheers,

WT.I'm not sure I understand what you're trying to do. Are you trying to return all records where the values in field1 are repeated? Can you provide an example to clarify?|||I think I understand what you are asking for. You're looking for all records where there are duplicates in field1.
Kind of the opposite of a DISTINCT query.
If you do a self join you can get what you want.
Here's the generic form...
SELECT * FROM table1 WHERE field1 IN (SELECT DISTINCT T1.field1 FROM table1.T1, table1.T2 WHERE T1.field1=T2.field1 AND T1.Field2<>T2.field2)

Here's an example using the Customers table from NorthWind that will return all the customers in cities where there is more than one customer in that city...
SELECT DISTINCT C1.CITY FROM Customers C1, Customers C2 WHERE C1.City = C2.City and C1.CustomerID <> C2.CustomerID|||An easier way to do this (if I understand your question):

SELECT CompanyID, COUNT(*)
Companies
GROUP BY CompanyID
HAVING COUNT(*) > 1

Comparing Dates in Subqueries

Hi,

I am trying to write a basic select query with a subquery.

select x
from y
where DateTime > '27 January 2003'
and DateTime < '02 February 2003'
and a = 'ttt'
and x IN (SELECT x
from y
WHERE a = 'vvv'
and rcd.DateTime > '27 January 2003'
and rcd.DateTime < '02 February 2003')

I only want to see the data for the records where ttt happened before vvv.

Any ideas?select x
from y
where DateTime > '27 January 2003'
and DateTime < '02 February 2003'
and a = 'ttt'
and exists
(SELECT *
from y y2
WHERE y2.a = 'vvv'
and y2.DateTime > '27 January 2003'
and y2.DateTime < '02 February 2003'
and y2,DateTime > y.DateTime
)|||select y.x
from y
left join
(
select x,MinDateTime=min(DateTime)
from y
where a='vvv' and DateTime > '27 January 2003' and DateTime < '02 February 2003'
group by x
) sy on sy.x=y.x and a='ttt' and ( (DateTime<MinDateTime) or MinDateTime is null )sqlsql

Comparing Dates in SQL

I am wondering how I would create a SELECT that will select the most recent date from one of two tables. For example, table1 has a field called LastUpdate and table 2 has a field called LastUpdate. I need to grab only the most recent date. I tried this using an inner join...but that didn't work because it only picks the lastupdate form one table only. talbe1 and table2 are tied by table2.table1id.

Can anyone help?

you could do a MAX(lastupdatedate) and then do the INNER JOIN.
SELECT
table1.column
FROM
table1
INNER JOIN table2
on table1.table1id = able2.table1id
WHERE
MAX(table1.lastupdatedate) = MAX(table2.lastupdatedate)|||I'm sorry I don't understand how this works. If the last update date is today in table2, I want to return that date. Otherwise I want to return the last update date in table1. Does this mak sense?|||SELECTCASE WHEN (table1.LastUpdate > table2.LastUpdate) THEN table1.LastUpdate ELSE table2.LastUpdate END AS MaxLastUpdate
FROM ... usual join statement|||

This is almost perfect! Thanks!

Followup: what if there is no instance of table2.table1id? Then I get nothing returned but in reality I still want table1.lastupdate returned.

Thanks again!

|||Ok I'm an idiot. I just switched the when statement and the then and else so the else displays the main from table1 and walaa. Thank you so much for this. I will get better at these case statements yet!|||

You have to use OUTER JOIN to make sure all rows are included. For example (using my own dummy master/detail tables):

SELECT A.ID_MASTER, CASE WHEN (A.DateEntered > ISNULL(B.DateEntered, '01-01-1900'))
THEN A.DateEntered ELSE b.DateEntered END AS MaxLastUpdate
FROM TestDate AS A LEFT OUTER JOIN
TestDate2 AS B ON A.ID_MASTER = B.ID_MASTER

ISNULL() function is used to make sure NULL date value (for the missing row in Detail) defaults to "01-01-1900", assuming that date will be small enough. Also, this query will return multiple rows if there are more than one Detail rows for a given Master row. Depending on your situation, you may or may not have to change this.

|||

SELECT keyid,MAX(DateEntered)
FROM (
SELECT keyid,DateEntered FROM table1
UNION
SELECT keyid,DateEntered FROM table2
) z
GROUP BY keyid

Would work as well, and only return a single result for each keyid.

Comparing date only?

This is probably a really simple one:
Given a table with a DATETIME column, how can I do an = or <> comparison
with just the date part?
ie.
SELECT * FROM MyTable WHERE SOMEFUNC(MyTable.theDateTime) <>
SOMEFUNC(@._In_theDataTime)
, where SOMEFUNC will just return the date part (ignoring the time part) of
theDateTime.
Thanks.....You don't want to apply expressions to both sides of the WHERE clause,
otherwise you will negate any indexes. How about (assuming your oddly named
variable is a DATETIME or, better yet, SMALLDATETIME):
WHERE MyTable.TheDateTIme >= (@._In_theDataTime)
AND MyTable.TheDateTIme < (@._In_theDataTime + 1)
"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:dbo636$hgi$1$8302bc10@.news.demon.co.uk...
> This is probably a really simple one:
> Given a table with a DATETIME column, how can I do an = or <> comparison
> with just the date part?
> ie.
> SELECT * FROM MyTable WHERE SOMEFUNC(MyTable.theDateTime) <>
> SOMEFUNC(@._In_theDataTime)
> , where SOMEFUNC will just return the date part (ignoring the time part)
> of theDateTime.
>
> Thanks.....
>|||Okay, thanks (and yes, my stored proc names are odd, as I use:
@._In_somevar, @._Out_somevar, @._InOut_somevar). I find it easier to
distinguish between local vars and parameters just by prefixing parameters
with `_'. It's a personal thing (no guidelines here) - also, quite often I
write Data when I mean to write Date and visa versa - apologies as I'm
slightly dyslexic. Anyway:
WHERE MyTable.TheDateTime >= (@._In_theDateTime)
AND MyTable.TheDateTime < (@._In_theDateTime + 1)
This won't work though, because @._In_theDateTime might be less than
MyTable.TheDateTime but still on the same date (ie. the former is 3pm, the
latter 1pm). I was thinking I would be able to say "DATEPART(x) =
DATEPART(y)" or something. I don't have millions of rows (average around
20 - 40k rows in this table), so I don't mind too much about not using the
indexes (at least for now).
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eyawDVfjFHA.3336@.tk2msftngp13.phx.gbl...
> You don't want to apply expressions to both sides of the WHERE clause,
> otherwise you will negate any indexes. How about (assuming your oddly
> named variable is a DATETIME or, better yet, SMALLDATETIME):
> WHERE MyTable.TheDateTIme >= (@._In_theDataTime)
> AND MyTable.TheDateTIme < (@._In_theDataTime + 1)
>
>
> "Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
> message news:dbo636$hgi$1$8302bc10@.news.demon.co.uk...
>|||> This won't work though, because @._In_theDateTime might be less than
> MyTable.TheDateTime but still on the same date (ie. the former is 3pm, the
> latter 1pm).
So why not pass in JUST THE DATE?
Or, in the stored procedure, say:
SET @._in_theDateTime = 0 + DATEDIFF(DAY, 0, @._in_TheDateTime)
-- this will set the time portion to midnight without any messy
casts/conversions.|||See CONVERT function in SQL Server Books Online. It offers you various
styles for date formatting. One of them (112) gets rid of time portion.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:dbo636$hgi$1$8302bc10@.news.demon.co.uk...
This is probably a really simple one:
Given a table with a DATETIME column, how can I do an = or <> comparison
with just the date part?
ie.
SELECT * FROM MyTable WHERE SOMEFUNC(MyTable.theDateTime) <>
SOMEFUNC(@._In_theDataTime)
, where SOMEFUNC will just return the date part (ignoring the time part) of
theDateTime.
Thanks.....|||Okay I get it now - yes this will work. Thanks.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23vvTFhfjFHA.3608@.TK2MSFTNGP12.phx.gbl...
> So why not pass in JUST THE DATE?
> Or, in the stored procedure, say:
> SET @._in_theDateTime = 0 + DATEDIFF(DAY, 0, @._in_TheDateTime)
> -- this will set the time portion to midnight without any messy
> casts/conversions.
>sqlsql

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

Compare values of databases between SQL Server 6.5 and 2000

Hi,
I am trying to compare values of two tables which are on different servers
and versions of SQL Server.
All i am looking for is
select * from a where not exists (select * from b where a.docid = b.docid)
where a is a table in Sql server 6.5
where b is a table in sql server 2000
I could not link the databases because of the difference in version. What is
best and easy approach to get the final result.
--
VijayaHi,
You can simply DTS the table from one server to the other and run your
comparison query.
--
- - - - - - - - -
Thanks
Yogish
"Vijaya" wrote:
> Hi,
> I am trying to compare values of two tables which are on different servers
> and versions of SQL Server.
> All i am looking for is
> select * from a where not exists (select * from b where a.docid = b.docid)
> where a is a table in Sql server 6.5
> where b is a table in sql server 2000
>
> I could not link the databases because of the difference in version. What is
> best and easy approach to get the final result.
> --
> Vijaya|||Vijaya,
Can you run the query on the SQL 2000 box? That would work. Or maybe
transfer the table and data from 2000 to 6.5 using something simple like
bcp and run the query locally on the 6.5 box? There's not going to be an
elegant solution to this one.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Vijaya wrote:
> Hi,
> I am trying to compare values of two tables which are on different servers
> and versions of SQL Server.
> All i am looking for is
> select * from a where not exists (select * from b where a.docid = b.docid)
> where a is a table in Sql server 6.5
> where b is a table in sql server 2000
>
> I could not link the databases because of the difference in version. What is
> best and easy approach to get the final result.

compare two string in SQL Server

Hi,
I am writing a Store Procedure for Login, as following:
@.p_sUsername, @.p_sPassword are parameters
...
SELECT @.BName = B.username, @.BPW = Password FROM BENUTZER as B WHERE
username = @.p_sUsername AND Passwort = @.p_sPassword
...
but the SELECT Statement can not differ Uppercase and Lowercase, that means,
"Martin" = "martin"
Then I check explicitly:
if @.BName != @.p_sUsername
Login = 0
but this comparing works the same.
What can I do?
Thanks
Martinif you SQL Server database is not case sensitive you have to convert to to a
comparable format ,e.g. varbinary or specifiy a CASE Sensitive (CS) Collatio
n
for it:
Select 1 where 'test' = 'TEST'
GO
Select 1 where 'Test' COLLATE SQL_Latin1_General_CP1_CS_AS = 'TEST' COLLATE
SQL_Latin1_General_CP1_CS_AS
GO
Select 1 Where CAST('Test' as varbinary) = CAST('TEST' as varbinary)
Select 1 where 'test' = 'TEST'
GO
Select 1 where 'Test' COLLATE SQL_Latin1_General_CP1_CS_AS = 'TEST' COLLATE
SQL_Latin1_General_CP1_CS_AS
GO
Select 1 Where CAST('Test' as varbinary) = CAST('TEST' as varbinary)
--
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Martin" wrote:

> Hi,
> I am writing a Store Procedure for Login, as following:
> @.p_sUsername, @.p_sPassword are parameters
> ...
> SELECT @.BName = B.username, @.BPW = Password FROM BENUTZER as B WHERE
> username = @.p_sUsername AND Passwort = @.p_sPassword
> ...
> but the SELECT Statement can not differ Uppercase and Lowercase, that mean
s,
> "Martin" = "martin"
> Then I check explicitly:
> if @.BName != @.p_sUsername
> Login = 0
> but this comparing works the same.
> What can I do?
> Thanks
> Martin
>
>|||Case-sensitivity is determined by the column collation. So choose a
case-sensitive collation. For example:
ALTER TABLE benutzer
ALTER COLUMN username VARCHAR(128) COLLATE Latin1_General_CS_AS ;
David Portas
SQL Server MVP
--|||Thanks
Martin
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> schrieb im
Newsbeitrag news:1125571037.540712.199690@.g43g2000cwa.googlegroups.com...
> Case-sensitivity is determined by the column collation. So choose a
> case-sensitive collation. For example:
> ALTER TABLE benutzer
> ALTER COLUMN username VARCHAR(128) COLLATE Latin1_General_CS_AS ;
> --
> David Portas
> SQL Server MVP
> --
>|||thanks
Martin
"Jens Smeyer" <Jens@.[Remove_that][for contacting me]sqlserver2005.de>
schrieb im Newsbeitrag
news:877CE479-F0DF-4CDA-8720-A14908BFB207@.microsoft.com...
> if you SQL Server database is not case sensitive you have to convert to to
a
> comparable format ,e.g. varbinary or specifiy a CASE Sensitive (CS)
Collation
> for it:
> Select 1 where 'test' = 'TEST'
> GO
>
> Select 1 where 'Test' COLLATE SQL_Latin1_General_CP1_CS_AS = 'TEST'
COLLATE
> SQL_Latin1_General_CP1_CS_AS
> GO
> Select 1 Where CAST('Test' as varbinary) = CAST('TEST' as varbinary)
> Select 1 where 'test' = 'TEST'
> GO
>
> Select 1 where 'Test' COLLATE SQL_Latin1_General_CP1_CS_AS = 'TEST'
COLLATE
> SQL_Latin1_General_CP1_CS_AS
> GO
> Select 1 Where CAST('Test' as varbinary) = CAST('TEST' as varbinary)
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Martin" wrote:
>
means,|||To add to the other responses, if you force a case-sensitive compare in your
SQL statement, consider also including the normal compare in your WHERE
clause. This will allow SQL Server to efficiently use indexes on those
columns and thereby improve performance.
SELECT
@.BName = B.username,
@.BPW = Password
FROM BENUTZER as B
WHERE
username COLLATE SQL_Latin1_General_CP1_CS_AS = @.p_sUsername AND
username = @.p_sUsername AND
password COLLATE SQL_Latin1_General_CP1_CS_AS = @.p_sPassword AND
Password = @.p_sPassword
Hope this helps.
Dan Guzman
SQL Server MVP
"Martin" <martinsm@.freenet.de> wrote in message
news:Okeml6trFHA.304@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I am writing a Store Procedure for Login, as following:
> @.p_sUsername, @.p_sPassword are parameters
> ...
> SELECT @.BName = B.username, @.BPW = Password FROM BENUTZER as B WHERE
> username = @.p_sUsername AND Passwort = @.p_sPassword
> ...
> but the SELECT Statement can not differ Uppercase and Lowercase, that
> means,
> "Martin" = "martin"
> Then I check explicitly:
> if @.BName != @.p_sUsername
> Login = 0
> but this comparing works the same.
> What can I do?
> Thanks
> Martin
>

Compare Two Queries - Help

Hi All,

I have two database that are duplicates of each other - but are on
different servers.

I need to write a script that will do a select from one table and then
compare it to another select of that table - but on the db on the other
server.

Is it possible to do that in a script? If so, how?

Thanks in advance.Brian Schultz (bdschultz@.gmail.com) writes:

Quote:

Originally Posted by

I have two database that are duplicates of each other - but are on
different servers.
>
I need to write a script that will do a select from one table and then
compare it to another select of that table - but on the db on the other
server.
>
Is it possible to do that in a script? If so, how?

SELECT ...
FROM localtbl l
FULL JOIN SERVER.db.dbo.remotetbl r ON l.keycol = r.keycol
WHERE l.keycol IS NULL
OR r.keycol IS NULL
OR a.col <b.col
OR a.col IS NULL AND b.col IS NOT NULL
OR a.col IS NOT NULL AND b.col IS NULL

SERVER is here a linked server that you have set up with sp_addlinkedserver.

If you need to do this on a large-scale basis, you should probably
consider a third-party product. I believe Red Gate has something called
DataCompare.

--
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|||you could try firefly. it's a free tool that i wrote:
http://www.getfirefly.net/
let me know if you have any problems.
thanks,
James

Compare two column values with leading zeros

Hey,

This is what I would like to do:
===========
Declare @.chvBOLNumber
Set @.chvBOLNumber='0001234'
Select * from BOL where BOLNumber=@.chvBOLNumber
I want to return the row/rows when BOLNumber=1234
============

The problem is the leading zeros. @.chvBOLNumber can be 01234 or 001234 or ...

Hope the above makes sense. How can I do this ? (probably using wildcards)

Thanks, JohnPerhaps try an integer comparison instead by converting the number to
integer:

SELECT * from BOL where CONVERT(INTEGER, BOLNumber) = 1234

(no idea if this syntax is correct!)

"Girish" <kattukuyil@.hotmail.com> wrote in message
news:b2bb38a.0410060557.3479f77e@.posting.google.co m...
> Hey,
> This is what I would like to do:
> ===========
> Declare @.chvBOLNumber
> Set @.chvBOLNumber='0001234'
> Select * from BOL where BOLNumber=@.chvBOLNumber
> I want to return the row/rows when BOLNumber=1234
> ============
> The problem is the leading zeros. @.chvBOLNumber can be 01234 or 001234 or
> ...
> Hope the above makes sense. How can I do this ? (probably using wildcards)
> Thanks, John|||"Girish" <kattukuyil@.hotmail.com> wrote in message
news:b2bb38a.0410060557.3479f77e@.posting.google.co m...
> Hey,
> This is what I would like to do:
> ===========
> Declare @.chvBOLNumber
> Set @.chvBOLNumber='0001234'
> Select * from BOL where BOLNumber=@.chvBOLNumber
> I want to return the row/rows when BOLNumber=1234
> ============
> The problem is the leading zeros. @.chvBOLNumber can be 01234 or 001234 or
> ...
> Hope the above makes sense. How can I do this ? (probably using wildcards)
> Thanks, John

For questions like this, it's important to know the data types involved, but
assuming that BOLNumber is an integer, then you can try this:

Select *
from BOL
where BOLNumber = cast(@.chvBOLNumber as int)

If this doesn't work as expected, please post the data types of BOLNumber
and @.chvBOLNumber. In general, you should always try to post CREATE TABLE
and INSERT statements to provide some sample data - that way there's no
confusion over exactly what you need.

Simon

Compare join condition definited in from clause or where clause

such as:

1.select * from table1 join table2 on table1.column1=table2.column1

2.select * from table1,table2 where table1.column1=table2.column1

which one is better? why ?

thanks

As per the perfromance both or same.

The advantage on 1st qurey is ANSI standard, if you use/learn the ANSI it will be common across any databases.

|||

From: http://www.sql-server-performance.com/faq/sqlviewfaq.aspx?faqid=85

Which of the following joins will produce better performance?

ANSI JOIN Syntax

SELECT fname, lname, department
FROM names
INNER JOIN departments
ON names.employeeid = departments.employeeid

Former Microsoft JOIN Syntax

SELECT fname, lname, department
FROM names, departments
WHERE names.employeeid = departments.employeeid

Answer

SQL Server supports two variations of performing JOINs: the ANSI JOIN syntax and the former Microsoft JOIN syntax. Both produce identical results and identical performance. There is no performance reasons to use one form of the JOIN over the other.

On the other hand, there are two good reasons why you should use the ANSI JOIN syntax over the former Microsoft JOIN syntax. First, it is more portable because it is the ANSI standard, and second, because eventually Microsoft may eliminate support of the former JOIN syntax.

Compare csv file layout to SQL Server table

I am creating a facility whereby you can select a source file (.csv), and a target (SQL table).

Then I call a DTS to copy the csv file to the SQL table.

How can I validate that the two files have the same number of columns ?

Moved to SQL Server Tools forum|||In order to check the number of rows you need to work on DTS with workflow, refer to books online for more information and you can schedule the DTS package a SQLagent job by right-click on selected package.|||I need to validate that the correct file has been selected before scheduling the DTS package. This is why I need to do it in my VB.Net Windows application.|||

You can write a custom Script Task inside the SSIS package that will open the file and check how many columns it has. You can utilize Excel object model for that or just go with regular .NET IO libraries.

Hope that helps.

Maciek Sarnowicz

Compare csv file layout to SQL Server table

I am creating a facility whereby you can select a source file (.csv), and a target (SQL table).

Then I call a DTS to copy the csv file to the SQL table.

How can I validate that the two files have the same number of columns ?

Moved to SQL Server Tools forum|||In order to check the number of rows you need to work on DTS with workflow, refer to books online for more information and you can schedule the DTS package a SQLagent job by right-click on selected package.|||I need to validate that the correct file has been selected before scheduling the DTS package. This is why I need to do it in my VB.Net Windows application.|||

You can write a custom Script Task inside the SSIS package that will open the file and check how many columns it has. You can utilize Excel object model for that or just go with regular .NET IO libraries.

Hope that helps.

Maciek Sarnowicz

Common Table Expression?

What is the SQL Server equivalent of DB2 common table expressions? For
example,

with gry(year,count) as(
select floor(sem/10),count(distinct ssn)
from grades
group by floor(sem/10)
)
select year,sum(count) Head_Count from gry
group by year
having year >= 1980;

N. Shamsundar
University of Houston"N. Shamsundar" <shamsundar_AT_uh.edu@.nospam.xyz> wrote in message
news:c4npes$3ecc$1@.masala.cc.uh.edu...
> What is the SQL Server equivalent of DB2 common table expressions? For
> example,
> with gry(year,count) as(
> select floor(sem/10),count(distinct ssn)
> from grades
> group by floor(sem/10)
> )
> select year,sum(count) Head_Count from gry
> group by year
> having year >= 1980;
> N. Shamsundar
> University of Houston

If you have a lot of queries which will reference the CTE, you could create
a view or table-valued function. If you only have a few queries, or if you
can't create a view or function for some reason, then a derived table is
probably the only other alternative. Your example seems to be relatively
simple, so I guess any of these options will work, but in more complex cases
a function might give you the most flexibility. CTEs will be in Yukon, by
the way.

Simon|||something like this:

*/ Untested! */
select
a.year,
a.sum(amount)

from
(select floor(sem/10) as year ,count(distinct ssn) as amount
from grades
group by floor(sem/10)
) as a

where
year >=1980

group by a.year

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
>

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 USE with variable

Why I can′t use the command USE with variable?

declare @.banco varchar(20)
select @.banco='xpto'
USE @.banco

I have try in other way without success

DECLARE @.banco varchar(50)
declare @.SQL varchar(50)
select @.banco='xpto'
print @.banco
select @.SQL='USE '+@.banco
print @.SQL
exec(@.SQL)

Help me, please.

Rodrigo

It is executed at both compile time and run time, so I presume that is the reason. Not all commands can use variables in place of constants. the TOP command, for instance, only takes constants until it was altered in SQL 2005. For USE sysntax and description:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_tsqlcon_6lyk.asp

|||

I am developing a routine to desfragment
the indexes of all tables of the server... I′ve found one in the books online (DBCC SHOWCONTIG session), but I ′m trying to convert it to defrag all objetcs in all databases of my instance. So I have think in a "Cursor" that get the name of the databases in the "sysdatabases" system table. Than set each database to current and run the script. Fallow the script:

For this, I have think in set the database to run the script on the database context.

Can you help me?

/*Perform a 'USE <database name>' to select the database in which to run the script.*/ -- Declare variables SET NOCOUNT ON DECLARE @.tablename VARCHAR (128) DECLARE @.execstr VARCHAR (255) DECLARE @.objectid INT DECLARE @.indexid INT DECLARE @.frag DECIMAL DECLARE @.maxfrag DECIMAL -- Decide on the maximum fragmentation to allow SELECT @.maxfrag = 30.0 -- Declare cursor DECLARE tables CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' -- Create the table CREATE TABLE #fraglist ( ObjectName CHAR (255), ObjectId INT, IndexName CHAR (255), IndexId INT, Lvl INT, CountPages INT, CountRows INT, MinRecSize INT, MaxRecSize INT, AvgRecSize INT, ForRecCount INT, Extents INT, ExtentSwitches INT, AvgFreeBytes INT, AvgPageDensity INT, ScanDensity DECIMAL, BestCount INT, ActualCount INT, LogicalFrag DECIMAL, ExtentFrag DECIMAL) -- Open the cursor OPEN tables -- Loop through all the tables in the database FETCH NEXT FROM tables INTO @.tablename WHILE @.@.FETCH_STATUS = 0 BEGIN -- Do the showcontig of all indexes of the table INSERT INTO #fraglist EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS') FETCH NEXT FROM tables INTO @.tablename END -- Close and deallocate the cursor CLOSE tables DEALLOCATE tables -- Declare cursor for list of indexes to be defragged DECLARE indexes CURSOR FOR SELECT ObjectName, ObjectId, IndexId, LogicalFrag FROM #fraglist WHERE LogicalFrag >= @.maxfrag AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0 -- Open the cursor OPEN indexes -- loop through the indexes FETCH NEXT FROM indexes INTO @.tablename, @.objectid, @.indexid, @.frag WHILE @.@.FETCH_STATUS = 0 BEGIN PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@.tablename) + ', ' + RTRIM(@.indexid) + ') - fragmentation currently ' + RTRIM(CONVERT(varchar(15),@.frag)) + '%' SELECT @.execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@.objectid) + ', ' + RTRIM(@.indexid) + ')' EXEC (@.execstr) FETCH NEXT FROM indexes INTO @.tablename, @.objectid, @.indexid, @.frag END -- Close and deallocate the cursor CLOSE indexes DEALLOCATE indexes -- Delete the temporary table DROP TABLE #fraglist GO |||

Here is one way with using sp_MSforeachdb and DBCC DBREINDEXALL

Both of these things are undocumented and you might not want to run it on a production server

EXEC sp_MSForEachDB 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')
DBCC DBREINDEXALL (''?'') '

-- nobody can be connected to the db for the above statement to work

also you could do something like this

DECLARE @.SQL NVarchar(4000)
SET @.SQL = ''
SELECT @.SQL = @.SQL + 'EXEC ' + NAME + '..sp_MSforeachtable @.command1=''DBCC DBREINDEX (''''*'''')'', @.replacechar=''*''' + Char(13)
FROM MASTER..Sysdatabases
where dbid > 6
PRINT @.SQL

EXEC (@.SQL)

However if you exceed 4000 characters this will fail

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

For EXECUTE you can use varchar(8000), or in 2005 you can use varchar(max).

|||Please don't use undocumented system stored procedures. They are not guaranteed to remain in every version of SQL Server. In fact we removed several system stored procedures or modified their behavior in SQL Server 2005.|||

Set options or use statement executed within dynamic SQL is only valid within that scope. So in your case, you have to perform the USE and the DBCC statement within the same dynamic SQL execution like:

exec('use ' + @.somedb + '; dbcc index...')