More queries more problems

I'm trying to write a query that returns a list of all employess from one table, and their hours worked from another table. If there are no entries in the hours table, I want to still show them with value of zero hours worked. I almost have this working, but I'm having some issues. Here's my query:

SELECT th.ID, tp.FirstName + ' ' + tp.LastName as Name, IsNull(Total_Hours, '0.0') as Hours1
FROM tblProfiles tp LEFT JOIN tblHours th on tp.ID = th.userID
WHERE (th.Start_Date = '" + myStartDate + "' OR th.Start_Date IS NULL)
ORDER BY tp.FirstName

For testing, in my profiles table, I have 5 users. In my hours table, I have entries for 3 of those users. Of those three users, 2 have entries for both 1/1/03 and 1/16/03, and the other has just 1 entry for 1/1/03.

When I run the query above for myStartDate = '1/1/03', I get the correct output, which shows the hours for 3 users, and shows 0 hours for the other 2. However, if I run the query on myStartDate = '1/16/03', my result set has the hours for the 2 users that have entries for that date, and also has 0 hours for the 2 users that have no entries. The user that has only 1 entry (for 1/1/03)should show up with 0 hours, but instead, it doesn't show up at all.

I'm guessing that the problem is with the way the table is joined, and that because that user has 1 valid Start_Date, it is getting excluded from the WHERE conditions, but I can't seem to make it work.

help!can you show the structure of the 2 tables ...|||tblProfiles has the following columns:

ID FirstName LastName ... (other unimportant columns)

tblHours has the following columns:

ID userID Start_Date End_Date Total_Hours

|||have you tried running this query in Query Analyzer as with the straight sql to make sure that you are getting the same results?|||yeah, I did... in Enterprise Manager, if I run the same query, I get the same results... the user with 1 entry for 1/1/03 does not show up at all if I run the query with:

WHERE Start_Date = '1/16/03' OR Start_Date IS NULL

I am assuming that "Start_Date IS NULL" is not evaluating to true because, with the table join, it is picking up the other record with the 1/1/03 Start_Date ...|||In my WHERE clause:

WHERE Start_Date = '" + myStartDate + "' OR Start_Date IS NULL

I think theStart_Date IS NULL part needs to specifically refer only the record for the given "myStartDate", but I can't make it jive...|||Yeah this is ringing a bell, I had something similiar. Maybe try wrapping everything in parens, like

WHERE ((Start_Date = '" + myStartDate + "') OR (Start_Date IS NULL))

Not sure but this might force SQL to evaluate the where clause correctly.|||I tried your suggestion, but it didn't change the results at all...|||have you thought abt using a cursor...
loop through for each record in tblprofiles...and for each record join with tblhours on id and start_date and get the hours..
this way you can get those ids tht dont have any entries...startdates/hours...etc and still show them.

HTH|||I'm not familiar with using cursors, but I'm willing to try anything... can you give me an example of how I would use it?|||heres a sample cursor tht i am using

DECLARE rs CURSOR
LOCAL
FORWARD_ONLY
OPTIMISTIC
TYPE_WARNING
FOR SELECT ID,FirstName,LastName, ... (other columns)
OPEN rs
fetch next from rs into @.id,@.fname,@.lname...( other columns)
WHILE ( @.@.FETCH_STATUS = 0 )
begin
 /* here do your stuff - join with the other table and get the stuff */
 use the @.id to join with tblhours..
 FETCH NEXT FROM rs INTO @.id,@.fname,@.lname...( other columns)
END
close rs
deallocate rs

HTH|||Ok, I'm getting lost in the syntax... here's what I have, but it's not working:

DECLARE @.id integer, @.fname varchar(50), @.lname varchar(50)
DECLARE rs CURSOR
LOCAL
FORWARD_ONLY
OPTIMISTIC
TYPE_WARNING
FOR SELECT tp.ID, tp.FirstName, tp.LastName FROM tblProfiles tp
OPEN rs
fetch next from rs into @.id,@.fname,@.lname
WHILE ( @.@.FETCH_STATUS = 0 )
begin
 SELECT @.id, @.fname, @.lname
SELECT th.Total_Hours FROM tblHours th WHERE th.userID = @.id AND th.Start_Date='1/16/03'
 fetch next from rs into @.id,@.fname,@.lname
END
close rs
deallocate rs

what am I doing wrong?|||I finally got it to work, and didn't have to use the cursor! Here's the working query:

SELECT IsNull(th.ID, '0') as ID, tp.FirstName + ' ' + tp.LastName as Name, IsNull(Total_Hours, '0.0') as Hours1
FROM tblProfiles tp LEFT JOIN tblHours th on tp.ID = th.userID AND th.Start_Date = '" + myStartDate + "'
WHERE ((th.Start_Date = '" + myStartDate + "') OR (th.Start_Date IS NULL)) ORDER BY th.Total_Hours DESC, tp.FirstName

The solution was to addStart_Date = '" + myStartDate + "' to the LEFT JOIN criteria.

Thanks for all the help along the way guys...
<sigh of relief>|||

CREATE PROCEDURE get_hrs AS
begin
DECLARE @.id as int, @.fname as varchar(50), @.lname as varchar(50)
declare @.mystartdate as datetime
set @.mystartdate='1/1/2003'
declare @.hrs as int
DECLARE rs CURSOR
LOCAL
FORWARD_ONLY
OPTIMISTIC
TYPE_WARNING
FOR SELECT [ID], FName, LName FROM tblpro
OPEN rs
fetch next from rs into @.id,@.fname,@.lname
WHILE ( @.@.FETCH_STATUS = 0 )
begin
select @.hrs=hours from tblhours where ([ID] =@.id and startdate=@.mystartdate)
print convert(varchar(5),@.id) + ' ' + @.fname + '-' + @.lname + ' ' + convert(nvarchar(10),@.hrs)
set @.hrs=0
 fetch next from rs into @.id,@.fname,@.lname
END
close rs
deallocate rs
end
GO

you might need to change the column names, table names...

HTH

More on ANSI Padding

The column attribute trimtrailingblanks is changed,
without notice, from yes to no when the length of the
column is altered via the Query Analyzer. Is there a way
to change the trimtrailingblanks property of a column on
the fly?Seems like ALTER TABLE doesn't honor the ANSI_PADDINGS setting and always set this to "no". See
script at end.
AFAIK, there's no way to change this with less that re-create the table.
I don't know if the current behavior is considered a bug or even reported, you might want to check
with MS.
SET ANSI_PADDING ON
GO
create table t(c1 varchar(1) NULL)
GO
EXEC sp_help t
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE t ALTER COLUMN c1 varchar(1) NOT NULL
GO
EXEC sp_help t
GO
DROP TABLE t
GO
SET ANSI_PADDING OFF --Doesn't help
GO
create table t(c1 varchar(1) NULL)
GO
EXEC sp_help t
GO
--SET ANSI_PADDING ON --Doesn't matter
GO
ALTER TABLE t ALTER COLUMN c1 varchar(1) NOT NULL
GO
EXEC sp_help t
GO
DROP TABLE t
GO
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Cordelia goh" <cordelia.goh@.gvrd.bc.ca> wrote in message
news:046901c3933d$71b19f20$a101280a@.phx.gbl...
> The column attribute trimtrailingblanks is changed,
> without notice, from yes to no when the length of the
> column is altered via the Query Analyzer. Is there a way
> to change the trimtrailingblanks property of a column on
> the fly?
>sql

More Efficient way to Concat Rows

Hi I have this problem with a query to process data concatenating
Units/Modules.
I have one View called Certificates thats lists Course IDs, Candidate
Names and Units. As each Certificate shows multiple Units achieved by
the Candidate I need to transform this data and combine the Units into
a single value - so I have 1 row per certificate rathan than 1 row per
unit.
I have been using this Query and several UDF's (Unit names, Unit
Grades, Unit Award dates etc) but as the database incresed in size the
query is timing out - Is there a more efficient way to do this ? Also
the function seems to create duplicates so i am using DISTINCT and
thisn seems to slow it down too. Help!
Thanks
hals_left
SELECT DISTINCT CourseID, CandidateID,dbo.GetUnits(CandidateID,
CourseID) AS Units
FROM Certificates
CREATE FUNCTION dbo.GetUnits
(@.CandidateID AS smallint, @.CourseID AS smallint )
RETURNS varchar(1000)
AS
BEGIN
DECLARE @.str AS varchar(1000)
SET @.str = ''
SELECT @.str = @.str + UnitTitle + char(13) + char(10)
FROM Certificates
WHERE CandidateID = @.CandidateID AND CourseID= @.CourseID
RETURN @.str
ENDIf this is just formatting for display then it isn't obvious why you
would need to do it in the database. The simplest and most likely
fastest method is in the client or presentation tier. See:
http://msdn.microsoft.com/library/d...
etstringmethod(recordset)ado.asp
David Portas
SQL Server MVP
--|||I agree but the client application (Word 2K Mailmerge) isnt capable of
this.|||Hello, hals_left
To make sure that the function is not called too many times, use a
query like this:
SELECT CourseID, CandidateID,
dbo.GetUnits(CandidateID, CourseID) AS Units
FROM (
SELECT DISTINCT CourseID, CandidateID,
FROM Certificates
) x
Razvan

More efficient query than this?

I have two tables:
tblA tblB
---
ColA int PK ColA int FK
... ColB int PK
DateAdded datetime
where tblB/ColA references tblA/ColA.
I need to find the most recently added row from tblB given a value of
ColA.
I wrote the following but wondered if there was a better, more
efficient method.
declare @.ColA int
select ColB, p.dateadded
from tblB b
inner join tblA a on b.ColA = a.ColA
where a.ColA = @.ColA and
b.dateadded = (select max(b.dateadded)
from tblB b
inner join tblA a on b.ColA = a.ColA
where a.ColA = @.ColA)
TIA LarsMhhm,
if there is a constraint between the two why do you need the Join ?
declare @.ColA int
select ColB, MAX(p.dateadded)
from tblB b
where b.ColA = @.ColA
Group by ColB
HTH, Jens Smeyer
http://www.sqlserver2005.de
--
"larzeb" <larzeb@.community.nospam> schrieb im Newsbeitrag
news:vtd061tnman8e1hft4f23rfos4sqa1q9a8@.
4ax.com...
>I have two tables:
> tblA tblB
> ---
> ColA int PK ColA int FK
> ... ColB int PK
> DateAdded datetime
> where tblB/ColA references tblA/ColA.
> I need to find the most recently added row from tblB given a value of
> ColA.
> I wrote the following but wondered if there was a better, more
> efficient method.
> declare @.ColA int
> select ColB, p.dateadded
> from tblB b
> inner join tblA a on b.ColA = a.ColA
> where a.ColA = @.ColA and
> b.dateadded = (select max(b.dateadded)
> from tblB b
> inner join tblA a on b.ColA = a.ColA
> where a.ColA = @.ColA)
> TIA Lars
>|||Jens,
Thanks for the reply. I don't think I explained the problem well
enough.
The table definition
tblA tblB
---
ColA int PK ColA int FK
... ColB int PK
DateAdded datetime
and some test data
ColA ColB DateAdded
---
100 32 2-10-2005
100 16 3-01-2005
100 24 3-16-2005
100 20 1-15-2005
101 ...
I would like a query which would return the single row whose ColA
value = 100
100 24 3-16-2005
since it has the latest date.
Lars
On Sat, 16 Apr 2005 00:17:02 +0200, "Jens Smeyer"
<Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote:

>Mhhm,
>if there is a constraint between the two why do you need the Join ?
>declare @.ColA int
>select ColB, MAX(b.dateadded)
>from tblB b
>where b.ColA = @.ColA
>Group by ColB
>HTH, Jens Smeyer
>--
>http://www.sqlserver2005.de
>--
>
>"larzeb" <larzeb@.community.nospam> schrieb im Newsbeitrag
> news:vtd061tnman8e1hft4f23rfos4sqa1q9a8@.
4ax.com...
>|||OK, whats wrong with that?
declare @.ColA int
Set ColA = 100
select b.ColB, MAX(b.dateadded)
from tblB b
where b.ColA = @.ColA
Group by ColB
Jens Smeyer.
"larzeb" <larzeb@.community.nospam> schrieb im Newsbeitrag
news:e9i061t1nrhddkviiksi0vf3qdk3m2g3qo@.
4ax.com...
> Jens,
> Thanks for the reply. I don't think I explained the problem well
> enough.
> The table definition
> tblA tblB
> ---
> ColA int PK ColA int FK
> ... ColB int PK
> DateAdded datetime
> and some test data
> ColA ColB DateAdded
> ---
> 100 32 2-10-2005
> 100 16 3-01-2005
> 100 24 3-16-2005
> 100 20 1-15-2005
> 101 ...
> I would like a query which would return the single row whose ColA
> value = 100
> 100 24 3-16-2005
> since it has the latest date.
> Lars
>
> On Sat, 16 Apr 2005 00:17:02 +0200, "Jens Smeyer"
> <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote:
>
>|||It returns all 4 rows when I expect only 1.
On Sat, 16 Apr 2005 01:31:54 +0200, "Jens Smeyer"
<Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote:

>OK, whats wrong with that?
>declare @.ColA int
>Set ColA = 100
>select b.ColB, MAX(b.dateadded)
>from tblB b
>where b.ColA = @.ColA
>Group by ColB
>Jens Smeyer.
>
>"larzeb" <larzeb@.community.nospam> schrieb im Newsbeitrag
> news:e9i061t1nrhddkviiksi0vf3qdk3m2g3qo@.
4ax.com...
>|||Sorry youre right, forget about that, try this.
Select * from TableB Where
COLB =
(
Select TOP 1 COLB
From TableB
WHERE COLA = 100
Group by COLB
Order by MAX(dateadded) DESC
)
HTH (now), Jens Smeyer ;-)
http://www.sqlserver2005.de
--
"larzeb" <larzeb@.community.nospam> schrieb im Newsbeitrag
news:j5k061hvmpdqbgjog7nf78qrq9ms5ki73k@.
4ax.com...
> It returns all 4 rows when I expect only 1.
> On Sat, 16 Apr 2005 01:31:54 +0200, "Jens Smeyer"
> <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote:
>
>|||Try This...
Select ColA, ColB, DateAdded
From TblB B
Where DateAdded =
(Select Max(DateAdded)
From TblB
Where ColA = B.ColA)
And ColA = @.ColA
Leave out the last row ( And ColA = @.ColA) to get the results for all ColA
values
"larzeb" wrote:

> I have two tables:
> tblA tblB
> ---
> ColA int PK ColA int FK
> .... ColB int PK
> DateAdded datetime
> where tblB/ColA references tblA/ColA.
> I need to find the most recently added row from tblB given a value of
> ColA.
> I wrote the following but wondered if there was a better, more
> efficient method.
> declare @.ColA int
> select ColB, p.dateadded
> from tblB b
> inner join tblA a on b.ColA = a.ColA
> where a.ColA = @.ColA and
> b.dateadded = (select max(b.dateadded)
> from tblB b
> inner join tblA a on b.ColA = a.ColA
> where a.ColA = @.ColA)
> TIA Lars
>
>sql

More dumb questions

Still learning so bear with me
1) Say I have a complicated select query (many joins) that I'd like users
to run. Which typically
performs better and why: creating a view with the select query or creating
a stored procedure with
the select query. Assume users don't care which mechanism is used.
2) I read where the rows column in sysindexes shows the number of rows in a
table and is a good
alternative to select count(*). The same article went on to state that this
figure can become inaccurate.
What can cause this figure to not match what a select count(*) would bring
back?
3) with select statements with many left outer joins used, do the order of
the joins matter? Do the joins
filter as they go along or does sql server figure it all out?
4) what is an IX lock? I can't figure it out from my reading. How is it
different from the X lock? Is there
a relationship?1. No telling which will be more efficient. You need to compare both
execution plans to determine that. They may be the same, and maybe not.
2. The values in sysindexes can get 'de-synchronized from the actual
table.row count as the result of bulk insert operations, deleting large
blocks of rows, etc. Re-synchronize by update statistics.
3. Order of JOINs 'can' matter. SQL will do it's best to 'figure it out',
but perhaps not the way you intended.
4. IX Lock =Intent Exclusive. Next in line to have an Exclusive (X) lock. IX
lock is marking one's position in the queue to place an exclusive lock (X)
as soon as any preceding shared locks are removed.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Dodo Lurker" <none@.noemailplease> wrote in message
news:bPadneyD79wGI3LZnZ2dnUVZ_qGdnZ2d@.co
mcast.com...
> Still learning so bear with me
> 1) Say I have a complicated select query (many joins) that I'd like users
> to run. Which typically
> performs better and why: creating a view with the select query or
> creating
> a stored procedure with
> the select query. Assume users don't care which mechanism is used.
> 2) I read where the rows column in sysindexes shows the number of rows in
> a
> table and is a good
> alternative to select count(*). The same article went on to state that
> this
> figure can become inaccurate.
> What can cause this figure to not match what a select count(*) would
> bring
> back?
> 3) with select statements with many left outer joins used, do the order
> of
> the joins matter? Do the joins
> filter as they go along or does sql server figure it all out?
> 4) what is an IX lock? I can't figure it out from my reading. How is it
> different from the X lock? Is there
> a relationship?
>|||The decision between a view and a stored procedure is usually based on how
the data will be used. A view can be used like a table, but a stored
procedure cannot. A stored procedure can take parameters, and include other
statements, and error checking. There is lots of info available about
optimizing stored procedures so search the Knowledgebase at
http://support.microsoft.com/search/?adv=1 It's impossible to say which
will perform better. It completely depends on what you're doing, what your
parameters are, how the query or proc is called, etc, etc.
The values in sysindexes can get out of date when you do certain bulk
operations. SQL Server doesn't always update the counts for all bulk inserts
and truncates. You can run DBCC UPDATEUSAGE to correct the values. UPDATE
STATISTICS will not do it.
The order of joins should not matter. The query is optimized as a whole and
may be completely rewritten internally.
An IX lock means INTENT-EXCLUSIVE, and is mainly a protection mechanism. It
does not mean a process is waiting for an X lock; if it were waiting, it
would be blocked, it doesn't get a special kind of lock to indicate waiting.
An intent lock is acquired a higher granularities when a regular lock is
acquired on a lower granularity unit. For example, if you have an exclusive
lock on a row, you will get an IX lock on the page and another IX lock on
the table, which will keep other processes from getting a lock on the whole
page or table. So yes, there is a relationship between X and IX.
Two IX locks are compatible with each other (if two processes each have X
locks on separate rows in the same table, they will each have IX locks on
the table itself), but X and IX are not compatible. You can read about "Lock
Compatibility" in the Books Online.
I suggest if you need to follow up on any of these questions, you start a
separate thread. It's a bit confusing to have so many separate topics in a
single message. They are not dumb questions, and each topic is certainly
worth of its own discussion. There is more info you can read about each of
these topics. Once you've done that, feel free to post follow-up questions.
HTH
Kalen Delaney, SQL Server MVP
"Dodo Lurker" <none@.noemailplease> wrote in message
news:bPadneyD79wGI3LZnZ2dnUVZ_qGdnZ2d@.co
mcast.com...
> Still learning so bear with me
> 1) Say I have a complicated select query (many joins) that I'd like users
> to run. Which typically
> performs better and why: creating a view with the select query or
> creating
> a stored procedure with
> the select query. Assume users don't care which mechanism is used.
> 2) I read where the rows column in sysindexes shows the number of rows in
> a
> table and is a good
> alternative to select count(*). The same article went on to state that
> this
> figure can become inaccurate.
> What can cause this figure to not match what a select count(*) would
> bring
> back?
> 3) with select statements with many left outer joins used, do the order
> of
> the joins matter? Do the joins
> filter as they go along or does sql server figure it all out?
> 4) what is an IX lock? I can't figure it out from my reading. How is it
> different from the X lock? Is there
> a relationship?
>|||Thank you. From now on, I'll start separate threads. Sorry for these
questions. I am VB developer
who's been tabbed to be the "database guy" since the company does not want
to hire a real
dba *shrug*.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OBeH6WNyGHA.2572@.TK2MSFTNGP06.phx.gbl...
> The decision between a view and a stored procedure is usually based on how
> the data will be used. A view can be used like a table, but a stored
> procedure cannot. A stored procedure can take parameters, and include
other
> statements, and error checking. There is lots of info available about
> optimizing stored procedures so search the Knowledgebase at
> http://support.microsoft.com/search/?adv=1 It's impossible to say which
> will perform better. It completely depends on what you're doing, what your
> parameters are, how the query or proc is called, etc, etc.
> The values in sysindexes can get out of date when you do certain bulk
> operations. SQL Server doesn't always update the counts for all bulk
inserts
> and truncates. You can run DBCC UPDATEUSAGE to correct the values. UPDATE
> STATISTICS will not do it.
> The order of joins should not matter. The query is optimized as a whole
and
> may be completely rewritten internally.
> An IX lock means INTENT-EXCLUSIVE, and is mainly a protection mechanism.
It
> does not mean a process is waiting for an X lock; if it were waiting, it
> would be blocked, it doesn't get a special kind of lock to indicate
waiting.
> An intent lock is acquired a higher granularities when a regular lock is
> acquired on a lower granularity unit. For example, if you have an
exclusive
> lock on a row, you will get an IX lock on the page and another IX lock on
> the table, which will keep other processes from getting a lock on the
whole
> page or table. So yes, there is a relationship between X and IX.
> Two IX locks are compatible with each other (if two processes each have X
> locks on separate rows in the same table, they will each have IX locks on
> the table itself), but X and IX are not compatible. You can read about
"Lock
> Compatibility" in the Books Online.
> I suggest if you need to follow up on any of these questions, you start a
> separate thread. It's a bit confusing to have so many separate topics in a
> single message. They are not dumb questions, and each topic is certainly
> worth of its own discussion. There is more info you can read about each of
> these topics. Once you've done that, feel free to post follow-up
questions.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Dodo Lurker" <none@.noemailplease> wrote in message
> news:bPadneyD79wGI3LZnZ2dnUVZ_qGdnZ2d@.co
mcast.com...
users[vbcol=seagreen]
in[vbcol=seagreen]
it[vbcol=seagreen]
>|||Don't worry, they're all valid questions.
Do you have access to the documentation (Books Online) that comes with SQL
Server? If you can't find it, that should be your first question.
HTH
Kalen Delaney, SQL Server MVP
"Dodo Lurker" <none@.noemailplease> wrote in message
news:J_6dnTrx3rlQ2W3ZnZ2dnUVZ_qGdnZ2d@.co
mcast.com...
> Thank you. From now on, I'll start separate threads. Sorry for these
> questions. I am VB developer
> who's been tabbed to be the "database guy" since the company does not want
> to hire a real
> dba *shrug*.
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OBeH6WNyGHA.2572@.TK2MSFTNGP06.phx.gbl...
> other
> inserts
> and
> It
> waiting.
> exclusive
> whole
> "Lock
> questions.
> users
> in
> it
>|||I do and I did read them. I just can't seem to get my arms around some of
the topics so
I look for a layman's explanation.
Thank you for your help!
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:#MVNBRSyGHA.5048@.TK2MSFTNGP05.phx.gbl...
> Don't worry, they're all valid questions.
> Do you have access to the documentation (Books Online) that comes with SQL
> Server? If you can't find it, that should be your first question.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Dodo Lurker" <none@.noemailplease> wrote in message
> news:J_6dnTrx3rlQ2W3ZnZ2dnUVZ_qGdnZ2d@.co
mcast.com...
want[vbcol=seagreen]
which[vbcol=seagreen]
UPDATE[vbcol=seagreen]
mechanism.[vbcol=seagreen]
it[vbcol=seagreen]
is[vbcol=seagreen]
on[vbcol=seagreen]
X[vbcol=seagreen]
on[vbcol=seagreen]
a[vbcol=seagreen]
in[vbcol=seagreen]
certainly[vbcol=seagreen]
rows[vbcol=seagreen]
that[vbcol=seagreen]
is[vbcol=seagreen]
>

More dumb questions

Still learning so bear with me
1) Say I have a complicated select query (many joins) that I'd like users
to run. Which typically
performs better and why: creating a view with the select query or creating
a stored procedure with
the select query. Assume users don't care which mechanism is used.
2) I read where the rows column in sysindexes shows the number of rows in a
table and is a good
alternative to select count(*). The same article went on to state that this
figure can become inaccurate.
What can cause this figure to not match what a select count(*) would bring
back?
3) with select statements with many left outer joins used, do the order of
the joins matter? Do the joins
filter as they go along or does sql server figure it all out?
4) what is an IX lock? I can't figure it out from my reading. How is it
different from the X lock? Is there
a relationship?1. No telling which will be more efficient. You need to compare both
execution plans to determine that. They may be the same, and maybe not.
2. The values in sysindexes can get 'de-synchronized from the actual
table.row count as the result of bulk insert operations, deleting large
blocks of rows, etc. Re-synchronize by update statistics.
3. Order of JOINs 'can' matter. SQL will do it's best to 'figure it out',
but perhaps not the way you intended.
4. IX Lock =Intent Exclusive. Next in line to have an Exclusive (X) lock. IX
lock is marking one's position in the queue to place an exclusive lock (X)
as soon as any preceding shared locks are removed.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Dodo Lurker" <none@.noemailplease> wrote in message
news:bPadneyD79wGI3LZnZ2dnUVZ_qGdnZ2d@.comcast.com...
> Still learning so bear with me
> 1) Say I have a complicated select query (many joins) that I'd like users
> to run. Which typically
> performs better and why: creating a view with the select query or
> creating
> a stored procedure with
> the select query. Assume users don't care which mechanism is used.
> 2) I read where the rows column in sysindexes shows the number of rows in
> a
> table and is a good
> alternative to select count(*). The same article went on to state that
> this
> figure can become inaccurate.
> What can cause this figure to not match what a select count(*) would
> bring
> back?
> 3) with select statements with many left outer joins used, do the order
> of
> the joins matter? Do the joins
> filter as they go along or does sql server figure it all out?
> 4) what is an IX lock? I can't figure it out from my reading. How is it
> different from the X lock? Is there
> a relationship?
>|||The decision between a view and a stored procedure is usually based on how
the data will be used. A view can be used like a table, but a stored
procedure cannot. A stored procedure can take parameters, and include other
statements, and error checking. There is lots of info available about
optimizing stored procedures so search the Knowledgebase at
http://support.microsoft.com/search/?adv=1 It's impossible to say which
will perform better. It completely depends on what you're doing, what your
parameters are, how the query or proc is called, etc, etc.
The values in sysindexes can get out of date when you do certain bulk
operations. SQL Server doesn't always update the counts for all bulk inserts
and truncates. You can run DBCC UPDATEUSAGE to correct the values. UPDATE
STATISTICS will not do it.
The order of joins should not matter. The query is optimized as a whole and
may be completely rewritten internally.
An IX lock means INTENT-EXCLUSIVE, and is mainly a protection mechanism. It
does not mean a process is waiting for an X lock; if it were waiting, it
would be blocked, it doesn't get a special kind of lock to indicate waiting.
An intent lock is acquired a higher granularities when a regular lock is
acquired on a lower granularity unit. For example, if you have an exclusive
lock on a row, you will get an IX lock on the page and another IX lock on
the table, which will keep other processes from getting a lock on the whole
page or table. So yes, there is a relationship between X and IX.
Two IX locks are compatible with each other (if two processes each have X
locks on separate rows in the same table, they will each have IX locks on
the table itself), but X and IX are not compatible. You can read about "Lock
Compatibility" in the Books Online.
I suggest if you need to follow up on any of these questions, you start a
separate thread. It's a bit confusing to have so many separate topics in a
single message. They are not dumb questions, and each topic is certainly
worth of its own discussion. There is more info you can read about each of
these topics. Once you've done that, feel free to post follow-up questions.
--
HTH
Kalen Delaney, SQL Server MVP
"Dodo Lurker" <none@.noemailplease> wrote in message
news:bPadneyD79wGI3LZnZ2dnUVZ_qGdnZ2d@.comcast.com...
> Still learning so bear with me
> 1) Say I have a complicated select query (many joins) that I'd like users
> to run. Which typically
> performs better and why: creating a view with the select query or
> creating
> a stored procedure with
> the select query. Assume users don't care which mechanism is used.
> 2) I read where the rows column in sysindexes shows the number of rows in
> a
> table and is a good
> alternative to select count(*). The same article went on to state that
> this
> figure can become inaccurate.
> What can cause this figure to not match what a select count(*) would
> bring
> back?
> 3) with select statements with many left outer joins used, do the order
> of
> the joins matter? Do the joins
> filter as they go along or does sql server figure it all out?
> 4) what is an IX lock? I can't figure it out from my reading. How is it
> different from the X lock? Is there
> a relationship?
>|||Thank you. From now on, I'll start separate threads. Sorry for these
questions. I am VB developer
who's been tabbed to be the "database guy" since the company does not want
to hire a real
dba *shrug*.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OBeH6WNyGHA.2572@.TK2MSFTNGP06.phx.gbl...
> The decision between a view and a stored procedure is usually based on how
> the data will be used. A view can be used like a table, but a stored
> procedure cannot. A stored procedure can take parameters, and include
other
> statements, and error checking. There is lots of info available about
> optimizing stored procedures so search the Knowledgebase at
> http://support.microsoft.com/search/?adv=1 It's impossible to say which
> will perform better. It completely depends on what you're doing, what your
> parameters are, how the query or proc is called, etc, etc.
> The values in sysindexes can get out of date when you do certain bulk
> operations. SQL Server doesn't always update the counts for all bulk
inserts
> and truncates. You can run DBCC UPDATEUSAGE to correct the values. UPDATE
> STATISTICS will not do it.
> The order of joins should not matter. The query is optimized as a whole
and
> may be completely rewritten internally.
> An IX lock means INTENT-EXCLUSIVE, and is mainly a protection mechanism.
It
> does not mean a process is waiting for an X lock; if it were waiting, it
> would be blocked, it doesn't get a special kind of lock to indicate
waiting.
> An intent lock is acquired a higher granularities when a regular lock is
> acquired on a lower granularity unit. For example, if you have an
exclusive
> lock on a row, you will get an IX lock on the page and another IX lock on
> the table, which will keep other processes from getting a lock on the
whole
> page or table. So yes, there is a relationship between X and IX.
> Two IX locks are compatible with each other (if two processes each have X
> locks on separate rows in the same table, they will each have IX locks on
> the table itself), but X and IX are not compatible. You can read about
"Lock
> Compatibility" in the Books Online.
> I suggest if you need to follow up on any of these questions, you start a
> separate thread. It's a bit confusing to have so many separate topics in a
> single message. They are not dumb questions, and each topic is certainly
> worth of its own discussion. There is more info you can read about each of
> these topics. Once you've done that, feel free to post follow-up
questions.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Dodo Lurker" <none@.noemailplease> wrote in message
> news:bPadneyD79wGI3LZnZ2dnUVZ_qGdnZ2d@.comcast.com...
> > Still learning so bear with me
> >
> > 1) Say I have a complicated select query (many joins) that I'd like
users
> > to run. Which typically
> > performs better and why: creating a view with the select query or
> > creating
> > a stored procedure with
> > the select query. Assume users don't care which mechanism is used.
> >
> > 2) I read where the rows column in sysindexes shows the number of rows
in
> > a
> > table and is a good
> > alternative to select count(*). The same article went on to state that
> > this
> > figure can become inaccurate.
> > What can cause this figure to not match what a select count(*) would
> > bring
> > back?
> >
> > 3) with select statements with many left outer joins used, do the order
> > of
> > the joins matter? Do the joins
> > filter as they go along or does sql server figure it all out?
> >
> > 4) what is an IX lock? I can't figure it out from my reading. How is
it
> > different from the X lock? Is there
> > a relationship?
> >
> >
>|||Don't worry, they're all valid questions.
Do you have access to the documentation (Books Online) that comes with SQL
Server? If you can't find it, that should be your first question.
--
HTH
Kalen Delaney, SQL Server MVP
"Dodo Lurker" <none@.noemailplease> wrote in message
news:J_6dnTrx3rlQ2W3ZnZ2dnUVZ_qGdnZ2d@.comcast.com...
> Thank you. From now on, I'll start separate threads. Sorry for these
> questions. I am VB developer
> who's been tabbed to be the "database guy" since the company does not want
> to hire a real
> dba *shrug*.
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OBeH6WNyGHA.2572@.TK2MSFTNGP06.phx.gbl...
>> The decision between a view and a stored procedure is usually based on
>> how
>> the data will be used. A view can be used like a table, but a stored
>> procedure cannot. A stored procedure can take parameters, and include
> other
>> statements, and error checking. There is lots of info available about
>> optimizing stored procedures so search the Knowledgebase at
>> http://support.microsoft.com/search/?adv=1 It's impossible to say which
>> will perform better. It completely depends on what you're doing, what
>> your
>> parameters are, how the query or proc is called, etc, etc.
>> The values in sysindexes can get out of date when you do certain bulk
>> operations. SQL Server doesn't always update the counts for all bulk
> inserts
>> and truncates. You can run DBCC UPDATEUSAGE to correct the values. UPDATE
>> STATISTICS will not do it.
>> The order of joins should not matter. The query is optimized as a whole
> and
>> may be completely rewritten internally.
>> An IX lock means INTENT-EXCLUSIVE, and is mainly a protection mechanism.
> It
>> does not mean a process is waiting for an X lock; if it were waiting, it
>> would be blocked, it doesn't get a special kind of lock to indicate
> waiting.
>> An intent lock is acquired a higher granularities when a regular lock is
>> acquired on a lower granularity unit. For example, if you have an
> exclusive
>> lock on a row, you will get an IX lock on the page and another IX lock on
>> the table, which will keep other processes from getting a lock on the
> whole
>> page or table. So yes, there is a relationship between X and IX.
>> Two IX locks are compatible with each other (if two processes each have X
>> locks on separate rows in the same table, they will each have IX locks on
>> the table itself), but X and IX are not compatible. You can read about
> "Lock
>> Compatibility" in the Books Online.
>> I suggest if you need to follow up on any of these questions, you start a
>> separate thread. It's a bit confusing to have so many separate topics in
>> a
>> single message. They are not dumb questions, and each topic is certainly
>> worth of its own discussion. There is more info you can read about each
>> of
>> these topics. Once you've done that, feel free to post follow-up
> questions.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>> "Dodo Lurker" <none@.noemailplease> wrote in message
>> news:bPadneyD79wGI3LZnZ2dnUVZ_qGdnZ2d@.comcast.com...
>> > Still learning so bear with me
>> >
>> > 1) Say I have a complicated select query (many joins) that I'd like
> users
>> > to run. Which typically
>> > performs better and why: creating a view with the select query or
>> > creating
>> > a stored procedure with
>> > the select query. Assume users don't care which mechanism is used.
>> >
>> > 2) I read where the rows column in sysindexes shows the number of rows
> in
>> > a
>> > table and is a good
>> > alternative to select count(*). The same article went on to state that
>> > this
>> > figure can become inaccurate.
>> > What can cause this figure to not match what a select count(*) would
>> > bring
>> > back?
>> >
>> > 3) with select statements with many left outer joins used, do the
>> > order
>> > of
>> > the joins matter? Do the joins
>> > filter as they go along or does sql server figure it all out?
>> >
>> > 4) what is an IX lock? I can't figure it out from my reading. How is
> it
>> > different from the X lock? Is there
>> > a relationship?
>> >
>> >
>>
>|||I do and I did read them. I just can't seem to get my arms around some of
the topics so
I look for a layman's explanation.
Thank you for your help!
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:#MVNBRSyGHA.5048@.TK2MSFTNGP05.phx.gbl...
> Don't worry, they're all valid questions.
> Do you have access to the documentation (Books Online) that comes with SQL
> Server? If you can't find it, that should be your first question.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Dodo Lurker" <none@.noemailplease> wrote in message
> news:J_6dnTrx3rlQ2W3ZnZ2dnUVZ_qGdnZ2d@.comcast.com...
> > Thank you. From now on, I'll start separate threads. Sorry for these
> > questions. I am VB developer
> > who's been tabbed to be the "database guy" since the company does not
want
> > to hire a real
> > dba *shrug*.
> >
> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > news:OBeH6WNyGHA.2572@.TK2MSFTNGP06.phx.gbl...
> >> The decision between a view and a stored procedure is usually based on
> >> how
> >> the data will be used. A view can be used like a table, but a stored
> >> procedure cannot. A stored procedure can take parameters, and include
> > other
> >> statements, and error checking. There is lots of info available about
> >> optimizing stored procedures so search the Knowledgebase at
> >> http://support.microsoft.com/search/?adv=1 It's impossible to say
which
> >> will perform better. It completely depends on what you're doing, what
> >> your
> >> parameters are, how the query or proc is called, etc, etc.
> >>
> >> The values in sysindexes can get out of date when you do certain bulk
> >> operations. SQL Server doesn't always update the counts for all bulk
> > inserts
> >> and truncates. You can run DBCC UPDATEUSAGE to correct the values.
UPDATE
> >> STATISTICS will not do it.
> >>
> >> The order of joins should not matter. The query is optimized as a whole
> > and
> >> may be completely rewritten internally.
> >>
> >> An IX lock means INTENT-EXCLUSIVE, and is mainly a protection
mechanism.
> > It
> >> does not mean a process is waiting for an X lock; if it were waiting,
it
> >> would be blocked, it doesn't get a special kind of lock to indicate
> > waiting.
> >> An intent lock is acquired a higher granularities when a regular lock
is
> >> acquired on a lower granularity unit. For example, if you have an
> > exclusive
> >> lock on a row, you will get an IX lock on the page and another IX lock
on
> >> the table, which will keep other processes from getting a lock on the
> > whole
> >> page or table. So yes, there is a relationship between X and IX.
> >>
> >> Two IX locks are compatible with each other (if two processes each have
X
> >> locks on separate rows in the same table, they will each have IX locks
on
> >> the table itself), but X and IX are not compatible. You can read about
> > "Lock
> >> Compatibility" in the Books Online.
> >>
> >> I suggest if you need to follow up on any of these questions, you start
a
> >> separate thread. It's a bit confusing to have so many separate topics
in
> >> a
> >> single message. They are not dumb questions, and each topic is
certainly
> >> worth of its own discussion. There is more info you can read about each
> >> of
> >> these topics. Once you've done that, feel free to post follow-up
> > questions.
> >>
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >>
> >>
> >> "Dodo Lurker" <none@.noemailplease> wrote in message
> >> news:bPadneyD79wGI3LZnZ2dnUVZ_qGdnZ2d@.comcast.com...
> >> > Still learning so bear with me
> >> >
> >> > 1) Say I have a complicated select query (many joins) that I'd like
> > users
> >> > to run. Which typically
> >> > performs better and why: creating a view with the select query or
> >> > creating
> >> > a stored procedure with
> >> > the select query. Assume users don't care which mechanism is used.
> >> >
> >> > 2) I read where the rows column in sysindexes shows the number of
rows
> > in
> >> > a
> >> > table and is a good
> >> > alternative to select count(*). The same article went on to state
that
> >> > this
> >> > figure can become inaccurate.
> >> > What can cause this figure to not match what a select count(*) would
> >> > bring
> >> > back?
> >> >
> >> > 3) with select statements with many left outer joins used, do the
> >> > order
> >> > of
> >> > the joins matter? Do the joins
> >> > filter as they go along or does sql server figure it all out?
> >> >
> >> > 4) what is an IX lock? I can't figure it out from my reading. How
is
> > it
> >> > different from the X lock? Is there
> >> > a relationship?
> >> >
> >> >
> >>
> >>
> >
> >
>

more database mail

Is there a limit to the complexity of a query that can be handled by
database mail?
thanks
Adam
Hi
"Adam Clark" wrote:

> Is there a limit to the complexity of a query that can be handled by
> database mail?
> thanks
> Adam
>
Complexity should not be an issue although if it is a particularly complex
statement I would create a stored procedure. The query parameter is
nvarchar(max) which is 2^31 bytes and probably also be constrained by the
batch size which is 65,536 * Network Packet Size. If you produce large
amounts of output you will be constrained by the MaxFileSize, also you SMTP
relay/server may have limits for total size of the message or attachments.
Is there any particular instance where you have had problems with this?
John

more database mail

Is there a limit to the complexity of a query that can be handled by
database mail?
thanks
AdamHi
"Adam Clark" wrote:

> Is there a limit to the complexity of a query that can be handled by
> database mail?
> thanks
> Adam
>
Complexity should not be an issue although if it is a particularly complex
statement I would create a stored procedure. The query parameter is
nvarchar(max) which is 2^31 bytes and probably also be constrained by the
batch size which is 65,536 * Network Packet Size. If you produce large
amounts of output you will be constrained by the MaxFileSize, also you SMTP
relay/server may have limits for total size of the message or attachments.
Is there any particular instance where you have had problems with this?
John

more database mail

Is there a limit to the complexity of a query that can be handled by
database mail?
thanks
AdamHi
"Adam Clark" wrote:
> Is there a limit to the complexity of a query that can be handled by
> database mail?
> thanks
> Adam
>
Complexity should not be an issue although if it is a particularly complex
statement I would create a stored procedure. The query parameter is
nvarchar(max) which is 2^31 bytes and probably also be constrained by the
batch size which is 65,536 * Network Packet Size. If you produce large
amounts of output you will be constrained by the MaxFileSize, also you SMTP
relay/server may have limits for total size of the message or attachments.
Is there any particular instance where you have had problems with this?
John

more Command inline

Hi.

"isqlw" is for Query Analizer.

Which is for Enterprise Manager?sql enterprise manager is an graphical user interface application which use to manager the sql server as well as MSDE, you can google it and download it
but i think the command tool osql is better than GUI application|||I was talking about "shortcuts", for example with 'isqlw' {in the run}, you open "Query Analizer". there are an shortcuts for open 'Enterprise Manager'?|||but you don't have enterprise manager in you box, how can you create an shortcuts?first you have to install enterprise manager, then you can create an shortcut to open enterprise manager|||I installed enterprise manager, you can help me in the create the shortcut.?|||you can either drag the enterprise manager executeable file to somewhere you want the shortcut be in,
or go to where you want to shortcut in and right click then choose new to create an shortcuts, when the shortcut wizzard come up, you have to point the path to enterprise manager executeable|||Hi, vito huang.

I understanded, the problem that I have.

the command para abrir el Enterprise manager is:

mmc /s "G:\Archivos de programa\Microsoft SQL Server\80\Tools\BINN\SQL Server Enterprise Manager.MSC"

mmc: is the program standar for IDE de "enterprise manager", you have that to use it command for open "Enterprise Manager.MSC"sql

More About SQL Mail

Dear Surajits,
Thanks for teach me the method of sending query result via SQL mail. But I
want to ask that if I want today date on the Subject, how can I perform this
?
i.e. @.Subject = 'Summary for the date of ' + GetDate() <= Can I write like
this ?
Thanks for concern.
You can't call the GETDATE() function in the proc execution. Bus you can declare a variable and
construct the subject into that variable (including today's date) and then pass that variable as a
parameter for subject in the xp_sendmail call.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Devil Garfield" <DevilGarfield@.discussions.microsoft.com> wrote in message
news:6F8F593F-C69B-467E-9BFF-276096853050@.microsoft.com...
> Dear Surajits,
> Thanks for teach me the method of sending query result via SQL mail. But I
> want to ask that if I want today date on the Subject, how can I perform this
> ?
> i.e. @.Subject = 'Summary for the date of ' + GetDate() <= Can I write like
> this ?
> Thanks for concern.
sql

More About SQL Mail

Dear Surajits,
Thanks for teach me the method of sending query result via SQL mail. But I
want to ask that if I want today date on the Subject, how can I perform this
?
i.e. @.Subject = 'Summary for the date of ' + GetDate() <= Can I write like
this '
Thanks for concern.You can't call the GETDATE() function in the proc execution. Bus you can dec
lare a variable and
construct the subject into that variable (including today's date) and then p
XXX that variable as a
parameter for subject in the xp_sendmail call.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Devil Garfield" <DevilGarfield@.discussions.microsoft.com> wrote in message
news:6F8F593F-C69B-467E-9BFF-276096853050@.microsoft.com...
> Dear Surajits,
> Thanks for teach me the method of sending query result via SQL mail. But I
> want to ask that if I want today date on the Subject, how can I perform th
is
> ?
> i.e. @.Subject = 'Summary for the date of ' + GetDate() <= Can I write like
> this '
> Thanks for concern.

More About SQL Mail

Dear Surajits,
Thanks for teach me the method of sending query result via SQL mail. But I
want to ask that if I want today date on the Subject, how can I perform this
?
i.e. @.Subject = 'Summary for the date of ' + GetDate() <= Can I write like
this '
Thanks for concern.You can't call the GETDATE() function in the proc execution. Bus you can declare a variable and
construct the subject into that variable (including today's date) and then pass that variable as a
parameter for subject in the xp_sendmail call.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Devil Garfield" <DevilGarfield@.discussions.microsoft.com> wrote in message
news:6F8F593F-C69B-467E-9BFF-276096853050@.microsoft.com...
> Dear Surajits,
> Thanks for teach me the method of sending query result via SQL mail. But I
> want to ask that if I want today date on the Subject, how can I perform this
> ?
> i.e. @.Subject = 'Summary for the date of ' + GetDate() <= Can I write like
> this '
> Thanks for concern.

Monthly report query

Someone please shed some light on how to write a select statement that will only pull out a bunch of records belongs only to a certain month. The field(sys_date) that keeps track of each record is a datatime field. Let's say that I need to select all the records starting from 03/01/2007 to 03/31/2007 at the end of March. I can't hardcode the dates because this report is scheduled to run at the end of every month via a DTS job in Sql 2000. Please help out. Thanks.

blumonde

Found the solution. Just in case anyone needs it:

Where (DATEPART(Month, sys_date) =
DATEPART(Month, GETDATE())) And (DATEPART(Year, sys_date) = DATEPART(Year,
GETDATE()))

Hope that helps.

month name from the sql query

Hi all

i want result as month name from my query

i tried with the following query but it give result as month number
like (8)

select (month(getdate())) as expr

i want result as month name (Augest)..

give me the proper query...

from Sachinsachin shah (sachin28880@.gmail.com) writes:

Quote:

Originally Posted by

i want result as month name from my query
>
i tried with the following query but it give result as month number
like (8)
>
select (month(getdate())) as expr
>
i want result as month name (Augest)..
>
give me the proper query...

Look at the datename() function in Books Online.

--
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|||On Aug 16, 7:48 am, sachin shah <sachin28...@.gmail.comwrote:

Quote:

Originally Posted by

Hi all
>
i want result as month name from my query
>
i tried with the following query but it give result as month number
like (8)
>
select (month(getdate())) as expr
>
i want result as month name (Augest)..
>
give me the proper query...
>
from Sachin

Try SELECT DATENAME(MONTH,GETDATE())sql

Month and Day Date Selection

Hi Folks:

I'm running a query whereby my users will select between "FromCloseDate" and "ToCloseDate". The easy part is when they're searching for month, day and year between the close dates however, they have a boolean report parameter that allow them to select month and day between the close dates. Has anyone done a between date selection for month and day?

Thanks in advance

Couldn't you just use the MONTH() and DAY() functions to extract the month and day for use in your query? Or you could use SUBSTRING().

Month and date wrong way around

Hi,

I am querying a report that was written with reporting services, via a webpage in vs2005. However when I input a date field to query from i.e 14/01/2007, this produces an error because the report is seeing this as 01/14/2007, even though in the database the record shows 14/01/2007? Please can someone offer any advice what to check?

thanks,

Harry.

Is that report runs client side?

|||

It seems to happen when report is tested in 'preview mode', and when the report is deployed it happens on the client. An error results because it can't handle the DD/MM the wrong way around?

There must be something I'm missing here...?

Many thanks,

Harry

|||

Hi, Harry:

You should try to format your date before you using them.

You can check out this article about how to format the date in SSRS.

http://msdn2.microsoft.com/en-us/library/ms157328.aspx

I hope the above information will be helpful. If you have any issues or concerns, please let me know. It's my pleasure to be of assistance

|||

Hi,

I'm a little confused how I can use the conversions,

I'm passing StartDate AND EndDate from 2 txtboxes,, my data is SELECT par1, par2, par2 from tbldatabase1 WHERE par1 >@.StartDate and par1 <@.EndDate.

But the parameters from the date boxes are taken in the wrong wat around. Do you know how I can implement some code to change this?. My main visual studio pages are written in c#?.

Or would you do this in the query itself?

Any help would be greatly appreciated.

Thanks Harry.

|||

HI,camper :

If your referring toReport Manager displaying the DateTime in theparameter input box, then no, you cannotformat this. You can onlyformat the date within thereport itself, or like the example I posted as following, give the user a dropdownparameter list of dates.

Apparently you can set theparameter to a string and then it won't enter the time, though you'll have to convert it into a date before it runs against your dataset. This way though,report users can enter a non-date as aparameter.

For some of myreports I create aparameter dataset based on the table holding the dates.

SELECT DATENAME(day, MyDateField) + ' ' + DATENAME(month, MyDateField) + ' ' + DATENAME(year, MyDateField) AS Label, MyDateField AS Value
FROM MyTable
GROUP BY DATENAME(day, MyDateField) + ' ' + DATENAME(month, MyDateField) + ' ' + DATENAME(year, MyDateField), MyDateField
ORDER BY MyDateField

This creates 2 fields, 'Label' which is what the user selects from and 'Value' which the dataset uses to query on.
Create aparameter (@.MyParameterDate) and reference it against your main dataset e.g.

SELECT * FROM MyTable WHERE MyDateField = @.MyParameterDate

In yourReportparameters set the values to be from a query and select yourparameter dataset. Set the Value and Label datafields to the ones created above and ensure theparameter datatype is DateTime. You can change the aboveformat if you don't want dates displaying as '7 November 2005'

I hope the above information will be helpful. If you have any issues or concerns, please let

me know. It's my pleasure to be of assistance

|||

Hi,

I know it's a few months since the last entry but I was having the same problems and fixed it by setting the Report properties: Language Property to my locale (English (United Kingdom) in this case). You can get at these properties from the report designer (in local mode) and clicking on the grey area outside the design grid. This stopped the report from swapping the months and days around :-)

Good luck.

|||

Change this line in your model's smdl file

<Culture>de-DE</Culture>

In this case German yyyy.mm.dd

|||

I am having the same problem. I saw your solution and tried that. However, it appears that those settings do not get passed to the report server. The report now correctly allows entry of the date in the Visual Studio designer, and when selected shows the date correctly, however, when the report is delpyed, the server, does not change behavior. It still thinks it is using mm/dd/yyy format.

Any ideas how to change the server side format to allow dd/mm/yy parameter format entry?

|||I also had to change regional settings to YYYY/MM/DD format on server. In layout view click properties on righthand side and select report from available fields, Click on language and choose English South Africa for YYYY/MM/DD format.sql

Monster Query Assistance

I realize this query is inherently incorrect, but my issue is mainly

syntax. The line, "WHEN a.order_id <> b.order_id THEN" is wrong.

I want to ensure that a.order_id is not in the settlement table. So I

was thinking something along the lines of "WHEN a.order_id not in

(select order_id from settlement)" which I know will cause a slower

response time, but I'm willing to deal with it. In any case, that

syntax doesn't appear to work.
sum(
CASE
WHEN a.ready_to_pay_flag = 'Y' and a.deduction_type = 'E' and (
CASE
WHEN a.order_id <> b.order_id THEN
a.transaction_date
ELSE
b.delivery_date
END) used_date datediff(d,used_date, ".$cutOffDate.") < 30) THEN
a.amount
END) earn_amount_rtp_curr,
Any help here would be hotness!
Thanks!

Keep in mind that the effect of the CASE structure is to return a value, in this case a date value. That value is then place inside these parentheses.

Can you spot the syntax problem simply by replacing the entire CASE (inside the parens) with a date value?

and ( CASE
WHEN a.order_id <> b.order_id THEN a.transaction_date
ELSE b.delivery_date
END
) used_date

|||Erm. I'm sure I don't follow you. used_date will be a.transaction_date if a.order_id is not b.order_id. Correct? Are you saying that my syntax is incorrect inside of the case statement? If it is, I just don't see where.

Also, can I simply use

and ( CASE
WHEN a.order_id not in (select order_id from settlement) THEN a.transaction_date
ELSE b.delivery_date
END
) used_date

?|||

This statement makes no sense: (the substituted date value is immaterial)

and ( '01/01/2007' ) used_date datediff(d,used_date, ".$cutOffDate.") < 30)

|||Hrm, so you're saying that I need some sort of separator. I tried parentheses, but those did not work. To quote your example:

and (( CASE ... END) used_date) datediff(d,used_date, ".$cutOffDate.") < 30)

I also tried adding a comma after "used_date)" and just after used_date and removed the parentheses. Can you simply not nest cases in this way or am I completely destroying the query?|||

You can next CASE structures, BUT each nested level MUST be a syntactically complete CASE structure.

I have not attempted to work out your structure, you have not provided any sample data, etc. (See: http://www.aspfaq.com/5006)

I am attempting to point out that (( CASE ... END) used_date) seems meaningless. There is no evaluation, assignment, or other indication about how these values are to be used. And then somehow there is this magical continuation of the line into the datediff function. I don't get what you are attempting with this line.

I suggest that you separately TEST each CASE structure, determining the return value, and then add that value to the outer CASE structure. ONLY when the entire CASE structure works with those substituted values, then you can carefully replace the substituted value with the CASE structure that derived that value.

|||Awesome. Yeah, all I needed to do was remove the "used_date" from that. I had assumed that I needed to alias out the result of the case statement and use the alias in the query later on. I guess I just have to constantly use the same case statement. Oh well, still works so I'm happy.

Thanks much for your help!

monitoring tables access

I have a sql server 2000 in windows 2003 domain.
A third part software connects to this sql and make query and it writes
record.
How can I keep track of the activity of this software on sql tables ?
Can I see which query the software make on the sql?
Many Thanks
> Can I see which query the software make on the sql?
SQL Profiler is your friend. Do please check it in Books OnLine.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message

monitoring tables access

I have a sql server 2000 in windows 2003 domain.
A third part software connects to this sql and make query and it writes
record.
How can I keep track of the activity of this software on sql tables ?
Can I see which query the software make on the sql?
Many Thanks> Can I see which query the software make on the sql?
SQL Profiler is your friend. Do please check it in Books OnLine.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message