query help

I have a table with multiple records for an identical person. It is a rolling history of the applications a user has submitted. How can I grab the most current application by the date most closest to todays date. Obviously one of my fields is an applied date.
I need help in creating a query.select * from table t where datefield = (select max(datefield) from table where userid = t.userid)

Nick

query help

I need a query to give me dbname, tablename,rowcount,size for all user dbs
and tables on a server
I know i can use sp_spaceused.. But want a query that I can then run on any
server
ThanksIf you are using SQL Server 2005, select your database, then Summary,
Report, Disk Usage and expand Disk Space Used by Tables.
Ben Nevarez, MCDBA, OCP
"Hassan" <hassan@.hotmail.com> wrote in message
news:%23yeA8Dl8FHA.740@.TK2MSFTNGP11.phx.gbl...
>I need a query to give me dbname, tablename,rowcount,size for all user dbs
>and tables on a server
> I know i can use sp_spaceused.. But want a query that I can then run on
> any server
> Thanks
>|||I was looking for the TSQL and I am using SQL 2000
I want the result to look like
DBName TableName Size(KB)
DB1 T1 1200
DB1 T2 100
DB2 T1 50
and so on...
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:%236fY%23Km8FHA.1188@.TK2MSFTNGP12.phx.gbl...
> If you are using SQL Server 2005, select your database, then Summary,
> Report, Disk Usage and expand Disk Space Used by Tables.
> Ben Nevarez, MCDBA, OCP
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:%23yeA8Dl8FHA.740@.TK2MSFTNGP11.phx.gbl...
>|||Hassan (hassan@.hotmail.com) writes:
> I need a query to give me dbname, tablename,rowcount,size for all user dbs
> and tables on a server
> I know i can use sp_spaceused.. But want a query that I can then run on
> any server
sp_MSforeachdb 'SELECT db = ''?'', "table" = object_name(id), rows, size =
reserved * 8192 / 1000000 from sysindexes WHERE indid IN (0,1) ORDER BY 2'
You will get one result set per database.
Note that the procedure sp_MSforeachdb is undocumented and not supported.
(But it stilll works in SQL 2005; I just tested the above in SQL 2005.)
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|||Erland,
It lists the same tablenames for all databases :-(
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns971BB51D75D5Yazorman@.127.0.0.1...
> Hassan (hassan@.hotmail.com) writes:
> sp_MSforeachdb 'SELECT db = ''?'', "table" = object_name(id), rows, size =
> reserved * 8192 / 1000000 from sysindexes WHERE indid IN (0,1) ORDER BY 2'
> You will get one result set per database.
> Note that the procedure sp_MSforeachdb is undocumented and not supported.
> (But it stilll works in SQL 2005; I just tested the above in SQL 2005.)
> --
> 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|||Hassan (hassan@.hotmail.com) writes:
> It lists the same tablenames for all databases :-(
Oops! Change "sysindexes" to "?.dbo.sysindexes", and it should work
better.
The ? is a placeholder for the DB name in sp_MSforeachdb.
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

Query Governer

Hi,
Can I define limitation on consuming CPU/IO for specific database user ?
We have an application that it's database resource consumption should be
restricted.
I can't use query governor because it puts global restriction for all users
(am i correct?)
Thanks,
AllanNo, sorry.
"A.M" <IHateSpam@.sapm123.com> wrote in message
news:OczCnokIEHA.3664@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Can I define limitation on consuming CPU/IO for specific database user ?
> We have an application that it's database resource consumption should be
> restricted.
> I can't use query governor because it puts global restriction for all
users
> (am i correct?)
> Thanks,
> Allan
>

Query Governer

Hi,
Can I define limitation on consuming CPU/IO for specific database user ?
We have an application that it's database resource consumption should be
restricted.
I can't use query governor because it puts global restriction for all users
(am i correct?)
Thanks,
AllanNo, sorry.
"A.M" <IHateSpam@.sapm123.com> wrote in message
news:OczCnokIEHA.3664@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Can I define limitation on consuming CPU/IO for specific database user ?
> We have an application that it's database resource consumption should be
> restricted.
> I can't use query governor because it puts global restriction for all
users
> (am i correct?)
> Thanks,
> Allan
>

Query Governer

Hi,
Can I define limitation on consuming CPU/IO for specific database user ?
We have an application that it's database resource consumption should be
restricted.
I can't use query governor because it puts global restriction for all users
(am i correct?)
Thanks,
Allan
No, sorry.
"A.M" <IHateSpam@.sapm123.com> wrote in message
news:OczCnokIEHA.3664@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Can I define limitation on consuming CPU/IO for specific database user ?
> We have an application that it's database resource consumption should be
> restricted.
> I can't use query governor because it puts global restriction for all
users
> (am i correct?)
> Thanks,
> Allan
>
sql

query for stored procedures?

I'm working with SQL Server and I'd like to run a query to find out a
list of the user stored procedures in a given database, and what the
parameters for each stored procedure are.http://www.aspfaq.com/2123
http://www.aspfaq.com/search.asp?q=schema%3A
<apandapion@.gmail.com> wrote in message
news:1129665163.315912.19510@.g14g2000cwa.googlegroups.com...
> I'm working with SQL Server and I'd like to run a query to find out a
> list of the user stored procedures in a given database, and what the
> parameters for each stored procedure are.
>

Query for server user rights, databases and roles.

I'm trying to write a query that will list the server login name, the
databases they have access too, and what roles they have. I can do this
through Enterprise Manager no problem. I simply go to the server, security,
logins, right click on a user and it gives me the databases they have access
to plus their roles. All I want to do is write this in a query in query
analyzer. Thanks in advance for the help.
Message posted via http://www.droptable.com
Hi,
Execute the below system stored proc from query analyzer:-
sp_helplogins <Login_name>
Thanks
Hari
SQL Server MVP
"Frank Nadal via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:4FCC7EC4933C0@.droptable.com...
> I'm trying to write a query that will list the server login name, the
> databases they have access too, and what roles they have. I can do this
> through Enterprise Manager no problem. I simply go to the server,
> security,
> logins, right click on a user and it gives me the databases they have
> access
> to plus their roles. All I want to do is write this in a query in query
> analyzer. Thanks in advance for the help.
> --
> Message posted via http://www.droptable.com

Query for server user rights, databases and roles.

I'm trying to write a query that will list the server login name, the
databases they have access too, and what roles they have. I can do this
through Enterprise Manager no problem. I simply go to the server, security,
logins, right click on a user and it gives me the databases they have access
to plus their roles. All I want to do is write this in a query in query
analyzer. Thanks in advance for the help.
Message posted via http://www.droptable.comHi,
Execute the below system stored proc from query analyzer:-
sp_helplogins <Login_name>
Thanks
Hari
SQL Server MVP
"Frank Nadal via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:4FCC7EC4933C0@.droptable.com...
> I'm trying to write a query that will list the server login name, the
> databases they have access too, and what roles they have. I can do this
> through Enterprise Manager no problem. I simply go to the server,
> security,
> logins, right click on a user and it gives me the databases they have
> access
> to plus their roles. All I want to do is write this in a query in query
> analyzer. Thanks in advance for the help.
> --
> Message posted via http://www.droptable.com

Query for server user rights, databases and roles.

I'm trying to write a query that will list the server login name, the
databases they have access too, and what roles they have. I can do this
through Enterprise Manager no problem. I simply go to the server, security,
logins, right click on a user and it gives me the databases they have access
to plus their roles. All I want to do is write this in a query in query
analyzer. Thanks in advance for the help.
--
Message posted via http://www.sqlmonster.comHi,
Execute the below system stored proc from query analyzer:-
sp_helplogins <Login_name>
Thanks
Hari
SQL Server MVP
"Frank Nadal via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in
message news:4FCC7EC4933C0@.SQLMonster.com...
> I'm trying to write a query that will list the server login name, the
> databases they have access too, and what roles they have. I can do this
> through Enterprise Manager no problem. I simply go to the server,
> security,
> logins, right click on a user and it gives me the databases they have
> access
> to plus their roles. All I want to do is write this in a query in query
> analyzer. Thanks in advance for the help.
> --
> Message posted via http://www.sqlmonster.com

Query for Ranking

The problem is :
I have table :
----------
...User ... Speed(minute)...
Maria 0.8
John 0.5
Alan 0.8
Anne 2.0
Smith 1.0
Kate 1.5
Evan 1.5
----------

--> I wanna set ranking for them such as:

Rank....User....Speed(minutes)
1 John 0.5
2 Maria 0.8
2 Alan 0.8
4 Smith 1.0
5 Kate 1.5
5 Evan 1.5
7 Anne 2.0
----------

Anyone can tell me how?
Thanks muchselect *,
(select count(1) + 1 from mytable t1 where t1.speed < t.speed) Rank
from mytable t
order by speed|||I run that query very well.
It's really excellent :]
Thanks so much, ehorn

query filling tempdb

Hello,
I've got a user who has been trying to run a query for the last few
days. Every time he tries to run it, the tempdb grows to be about 5G and
ends up filling up the hard drive. Anyone have any insight into why
this particular query is filling up the tempdb like this?
SELECT top 50
SUBSTRING(dbo.CIMSDetail.AccountCode, 1, 1) AS Corp,
SUBSTRING(dbo.CIMSDetail.AccountCode, 2, 2) AS BillGroup,
SUBSTRING(dbo.CIMSDetail.AccountCode, 4, 7) As BillEntity,
SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 2) AS LOB,
SUBSTRING(dbo.CIMSDetail.AccountCode, 13, 3) AS Portfolio,
SUBSTRING(dbo.CIMSDetail.AccountCode, 16, 1) AS WorkType.
SUBSTRING(dbo.CIMSDetail.AccountCode, 17, 3) AS Client,
SUBSTRING(dbo.CIMSDetail.AccountCode, 20, 2) AS Service,
SUBSTRING(dbo.CIMSDetail.AccountCode, 22, 2) AS Service Type,
SUBSTRING(dbo.CIMSDetail.AccountCode, 24, 2) AS Product,
SUBSTRING(dbo.CIMSDetail.AccountCode, 26, 3) AS Department,
SUBSTRING(dbo.CIMSDetail.AccountCode, 29, 3) AS LOC,
SUBSTRING(dbo.CIMSDetail.AccountCode, 32, 4) AS LPAR,
SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 18) AS ORIGSUM,
dbo.CIMSDetail.AccountCode As Acct_Code,
dbo.CIMSDetail.StartDate AS StartDay,
dbo.CIMSDetail.EndDate AS EndDay,
dbo.CIMSDetail.RateCode as RateCode,
dbo.CIMSDetail.ResourceUnits AS Volume,
dbo.CIMSDetailIdent.IdentValue AS Ident_Value,
dbo.CIMSIdent.IdentDescription AS IDENT_Desc,
DATEPART(yyyy, dbo.CIMSDetail.EndDate) AS Year,
DATEPART(mm, dbo.CIMSDetail.EndDate) AS Month,
DATEPART(dd, dbo.CIMSDetail.EndDate) AS Day
FROM dbo.CIMSDetail INNER JOIN
dbo.CIMSDetailIdent ON dbo.CIMSDetail.DetailUID = dbo.CIMSDetailIdent.DetailUID AND
dbo.CIMSDetail.DetailLine = dbo.CIMSDetailIdent.DetailLine INNER JOIN
dbo.CIMSIdent ON dbo.CIMSDetailIdent.IdentNumber = dbo.CIMSIdent.IdentNumber
WHERE DATEPART(DD,GETDATE()) - DATEPART(DD, dbo.CIMSDetail.EndDate) = 1
AND dbo.CIMSDetail.RateCode IN ('Z003', 'Z020', 'ZZ05')
AND dbo.CIMSIdent.IdentDescription = 'JOBNAME'
OR dbo.CIMSIdent.IdentDescription = 'WORK_ID'
GROUP BY
DATEPART(yyyy, dbo.CIMSDetail.EndDate),
DATEPART(mm, dbo.CIMSDetail.EndDate),
DATEPART(dd, dbo.CIMSDetail.EndDate),
SUBSTRING(dbo.CIMSDetail.AccountCode, 32, 4),
SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 2),
SUBSTRING(dbo.CIMSDetail.AccountCode, 29, 3),
dbo.CIMSDetail.RateCode,
dbo.CIMSDetail.RateCode,
dbo.CIMSDetail.StartDate,
dbo.CIMSDetail.EndDate,
dbo.CIMSIdent.IdentDescription,
dbo.CIMSDetailIdent.IdentValue,
dbo.CIMSDetail.AccountCode,
SUBSTRING(dbo.CIMSDETAIL.AccountCode,11,18)
ORDER BY
DATEPART(yyyy, dbo.CIMSDetail.EndDate),
DATEPART(mm, dbo.CIMSDetail.EndDate),
DATEPART(dd, dbo.CIMSDetail.EndDate),
SUBSTRING(dbo.CIMSDetail.AccountCode, 32, 4),
SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 2),
SUBSTRING(dbo.CIMSDetail.AccountCode, 29, 3),
dbo.CIMSDetail.RateCode,
dbo.CIMSDetail.StartDate,
dbo.CIMSDetail.EndDate,
dbo.CIMSIdent.IdentDescription,
dbo.CIMSDetailIdent.IdentValue,
dbo.CIMSDetail.AccountCode,
SUBSTRING(dbo.CIMSDETAIL.AccountCode,11,18)
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!You might consider normalization. :-) Why is all that discrete data in one
column? The database has to do a lot of work separating it out with
SUBSTRING like that.
You might also consider putting tempdb on a drive with more than 5GB free.
:-)
"Rachael Faber" <rfaber@.alldata.net> wrote in message
news:eJh59sOjDHA.3192@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I've got a user who has been trying to run a query for the last few
> days. Every time he tries to run it, the tempdb grows to be about 5G and
> ends up filling up the hard drive. Anyone have any insight into why
> this particular query is filling up the tempdb like this?
> SELECT top 50
> SUBSTRING(dbo.CIMSDetail.AccountCode, 1, 1) AS Corp,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 2, 2) AS BillGroup,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 4, 7) As BillEntity,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 2) AS LOB,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 13, 3) AS Portfolio,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 16, 1) AS WorkType.
> SUBSTRING(dbo.CIMSDetail.AccountCode, 17, 3) AS Client,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 20, 2) AS Service,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 22, 2) AS Service Type,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 24, 2) AS Product,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 26, 3) AS Department,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 29, 3) AS LOC,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 32, 4) AS LPAR,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 18) AS ORIGSUM,
> dbo.CIMSDetail.AccountCode As Acct_Code,
> dbo.CIMSDetail.StartDate AS StartDay,
> dbo.CIMSDetail.EndDate AS EndDay,
> dbo.CIMSDetail.RateCode as RateCode,
> dbo.CIMSDetail.ResourceUnits AS Volume,
> dbo.CIMSDetailIdent.IdentValue AS Ident_Value,
> dbo.CIMSIdent.IdentDescription AS IDENT_Desc,
> DATEPART(yyyy, dbo.CIMSDetail.EndDate) AS Year,
> DATEPART(mm, dbo.CIMSDetail.EndDate) AS Month,
> DATEPART(dd, dbo.CIMSDetail.EndDate) AS Day
>
> FROM dbo.CIMSDetail INNER JOIN
> dbo.CIMSDetailIdent ON dbo.CIMSDetail.DetailUID => dbo.CIMSDetailIdent.DetailUID AND
> dbo.CIMSDetail.DetailLine = dbo.CIMSDetailIdent.DetailLine INNER JOIN
> dbo.CIMSIdent ON dbo.CIMSDetailIdent.IdentNumber => dbo.CIMSIdent.IdentNumber
> WHERE DATEPART(DD,GETDATE()) - DATEPART(DD, dbo.CIMSDetail.EndDate) => 1
> AND dbo.CIMSDetail.RateCode IN ('Z003', 'Z020', 'ZZ05')
> AND dbo.CIMSIdent.IdentDescription = 'JOBNAME'
> OR dbo.CIMSIdent.IdentDescription = 'WORK_ID'
> GROUP BY
> DATEPART(yyyy, dbo.CIMSDetail.EndDate),
> DATEPART(mm, dbo.CIMSDetail.EndDate),
> DATEPART(dd, dbo.CIMSDetail.EndDate),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 32, 4),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 2),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 29, 3),
> dbo.CIMSDetail.RateCode,
> dbo.CIMSDetail.RateCode,
> dbo.CIMSDetail.StartDate,
> dbo.CIMSDetail.EndDate,
> dbo.CIMSIdent.IdentDescription,
> dbo.CIMSDetailIdent.IdentValue,
> dbo.CIMSDetail.AccountCode,
> SUBSTRING(dbo.CIMSDETAIL.AccountCode,11,18)
> ORDER BY
> DATEPART(yyyy, dbo.CIMSDetail.EndDate),
> DATEPART(mm, dbo.CIMSDetail.EndDate),
> DATEPART(dd, dbo.CIMSDetail.EndDate),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 32, 4),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 2),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 29, 3),
> dbo.CIMSDetail.RateCode,
> dbo.CIMSDetail.StartDate,
> dbo.CIMSDetail.EndDate,
> dbo.CIMSIdent.IdentDescription,
> dbo.CIMSDetailIdent.IdentValue,
> dbo.CIMSDetail.AccountCode,
> SUBSTRING(dbo.CIMSDETAIL.AccountCode,11,18)
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Typically coputed values in your select statement (like
all of the substring statements) require use of tempdb
space, and the group by and order by portions of your
query will also require a large amount of tempdb space.
In addition to Aaron's comments, you may want to consider
using a summary table that stores some of these
calculated/summary values so that you don't have to
calculate them within the query itself. While this will
use space within your actual database, this will be
easier to monitor and control then the space allocated on
the fly within tempdb.
Just an idea. I hope that this helps somehow.
Matthew Bando
BandoM@.CSCTechnologies.com
>--Original Message--
>Hello,
>I've got a user who has been trying to run a query for
the last few
>days. Every time he tries to run it, the tempdb grows to
be about 5G and
>ends up filling up the hard drive. Anyone have any
insight into why
>this particular query is filling up the tempdb like this?
>SELECT top 50
> SUBSTRING(dbo.CIMSDetail.AccountCode, 1, 1) AS
Corp,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 2, 2) AS
BillGroup,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 4, 7) As
BillEntity,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 2) AS
LOB,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 13, 3) AS
Portfolio,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 16, 1) AS
WorkType.
> SUBSTRING(dbo.CIMSDetail.AccountCode, 17, 3) AS
Client,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 20, 2) AS
Service,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 22, 2) AS
Service Type,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 24, 2) AS
Product,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 26,
3) AS Department,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 29, 3) AS
LOC,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 32, 4) AS
LPAR,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 18) AS
ORIGSUM,
> dbo.CIMSDetail.AccountCode As Acct_Code,
> dbo.CIMSDetail.StartDate AS StartDay,
> dbo.CIMSDetail.EndDate AS EndDay,
> dbo.CIMSDetail.RateCode as RateCode,
> dbo.CIMSDetail.ResourceUnits AS Volume,
> dbo.CIMSDetailIdent.IdentValue AS Ident_Value,
> dbo.CIMSIdent.IdentDescription AS IDENT_Desc,
> DATEPART(yyyy, dbo.CIMSDetail.EndDate) AS Year,
> DATEPART(mm, dbo.CIMSDetail.EndDate) AS Month,
> DATEPART(dd, dbo.CIMSDetail.EndDate) AS Day
>
>FROM dbo.CIMSDetail INNER JOIN
> dbo.CIMSDetailIdent ON dbo.CIMSDetail.DetailUID =>dbo.CIMSDetailIdent.DetailUID AND
> dbo.CIMSDetail.DetailLine =dbo.CIMSDetailIdent.DetailLine INNER JOIN
> dbo.CIMSIdent ON dbo.CIMSDetailIdent.IdentNumber =>dbo.CIMSIdent.IdentNumber
>WHERE DATEPART(DD,GETDATE()) - DATEPART(DD,
dbo.CIMSDetail.EndDate) =>1
> AND dbo.CIMSDetail.RateCode IN
('Z003', 'Z020', 'ZZ05')
> AND dbo.CIMSIdent.IdentDescription = 'JOBNAME'
> OR dbo.CIMSIdent.IdentDescription
= 'WORK_ID'
>GROUP BY
> DATEPART(yyyy, dbo.CIMSDetail.EndDate),
> DATEPART(mm, dbo.CIMSDetail.EndDate),
> DATEPART(dd, dbo.CIMSDetail.EndDate),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 32, 4),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 2),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 29, 3),
> dbo.CIMSDetail.RateCode,
> dbo.CIMSDetail.RateCode,
> dbo.CIMSDetail.StartDate,
> dbo.CIMSDetail.EndDate,
> dbo.CIMSIdent.IdentDescription,
> dbo.CIMSDetailIdent.IdentValue,
> dbo.CIMSDetail.AccountCode,
> SUBSTRING(dbo.CIMSDETAIL.AccountCode,11,18)
>ORDER BY
> DATEPART(yyyy, dbo.CIMSDetail.EndDate),
> DATEPART(mm, dbo.CIMSDetail.EndDate),
> DATEPART(dd, dbo.CIMSDetail.EndDate),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 32,
4),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 2),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 29, 3),
> dbo.CIMSDetail.RateCode,
> dbo.CIMSDetail.StartDate,
> dbo.CIMSDetail.EndDate,
> dbo.CIMSIdent.IdentDescription,
> dbo.CIMSDetailIdent.IdentValue,
> dbo.CIMSDetail.AccountCode,
> SUBSTRING(dbo.CIMSDETAIL.AccountCode,11,18)
>
>*** Sent via Developersdex http://www.developersdex.com
***
>Don't just participate in USENET...get rewarded for it!
>.
>

Query failis

HAI Friends
I need some info.
I have written a query which was working fine with DB user id and password deticated to this DB which i am using.
Then we change the login mode to Windows Login with Trusted connection. After this change my query is failing.
My query is which working in SQL login mode is
SELECT DATE_TIME,CARDNO, NATIONALID, DOB, STATUS FROM TBL_INFO where DATE_TIME between '29/12/2003 00:00:01' and '30/12/2003 23:59:59' Order by DATE_TIME ASC

Where DATE_TIME is date time Data type.

i change this query to check with Windows login with trusted connection.
SELECT DATE_TIME,CARDNO, NATIONALID, DOB, STATUS FROM TBL_INFO where DATE_TIME between convert(datetime, '29/12/2003', 103 ) and convert(datetime, '30/12/2003', 103 ) Order by DATE_TIME ASC
No Error is mention for this query but no records are fetched. But data is there in DB.
Please help i am new to SQL server.
Expecting your replies ASAP
Signing Off
Venkatesh.VHas the windows login been given access to the database/tables ? Where are you running these queries ? Do you have access to query analyzer ?|||Originally posted by rnealejr
Has the windows login been given access to the database/tables ? Where are you running these queries ? Do you have access to query analyzer ?

Yes i have access to Tables. i have been given DB owner rights.

i have to run these queries form Visual basic. i have to populate these data as reports using Crystal reports. Query is not working even in Query Analyser.
i ma trying it out with 2 diff login id with query analyser|||In query analyzer - what about select * from tbl_info/select count(*) from tbl_info. Also, what message is returned in query analyzer ?|||You connected to the server?

Then you're in...

And I bet you're trying to execute the code in master...|||But he would get invalid object name if that were the case ...|||Originally posted by rnealejr
In query analyzer - what about select * from tbl_info/select count(*) from tbl_info. Also, what message is returned in query analyzer ?
Dear Friend,
I ma able to see data if i put a Select * Statement and i ma able to get count etc..
Only thing this query fails ..|||It shoulds like your where clause is hosed - leave off the where clause and try again. If that returns rows, what values are stored in the date_time field ?|||First:

You need to post the error message. We're just guess right now as to what the problem is.

Second:

You need to post the Query and The DDL of the Tables

Third:

Sample Data would be a BIG help|||Originally posted by Brett Kaiser
First:

You need to post the error message. We're just guess right now as to what the problem is.

Second:

You need to post the Query and The DDL of the Tables

Third:

Sample Data would be a BIG help

Dear Frnd,
I have ran the query with out any condtion. it works. Even with one condtion by checking only first date value it works. when we put the between caluse in the query it returns nothing.

i have attched the table strucure,index,constraints and sample data. please have look into this and help me out.|||You seem to be focusing on the fact that you changed your login method, but your post indicates that you also changed your query, and this is likely the problem. For one thing,

between '29/12/2003 00:00:01' and '30/12/2003 23:59:59'

is NOT the same as

between convert(datetime, '29/12/2003', 103 ) and convert(datetime, '30/12/2003', 103 )

At the very least, the second statement covers 24 fewer hours than the first statement, so any data on 30/12/2003 would not show up.

Why did you switch to using a convert function?

blindman|||Originally posted by blindman
You seem to be focusing on the fact that you changed your login method, but your post indicates that you also changed your query, and this is likely the problem. For one thing,

between '29/12/2003 00:00:01' and '30/12/2003 23:59:59'

is NOT the same as

between convert(datetime, '29/12/2003', 103 ) and convert(datetime, '30/12/2003', 103 )

At the very least, the second statement covers 24 fewer hours than the first statement, so any data on 30/12/2003 would not show up.

Why did you switch to using a convert function?

blindman

As my Previous Query was not working under Trusted conenction it throws me error stating that "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."
So to solve this i tired with Convert fucntion. Still after that no records are fetched. But data is there in Table.|||If you show what is returned in the date_time field, I think we can solve this. Also, what is the datatype for the date_time field - datetime or smalldatetime ?|||Originally posted by rnealejr
If you show what is returned in the date_time field, I think we can solve this. Also, what is the datatype for the date_time field - datetime or smalldatetime ?

Datatype for DATE_TME is datetime.
when we select date_time alone we get data as
30/12/2003 11:01:10
30/12/2003 10:34:18
30/12/2003 10:34:01|||I looked at your attachment...and it does look like the column is defined as datetime...

Your data however doesn't...

Do this and tell us what you get

[grasping at straws now]

SELECT COUNT(*) FROM TBL_INFO WHERE ISDATE(DATE_TIME) = 0

If you get anything other than 0, you'll have non date data...

Which is what your error message is telling you...but not what your table def shows

[/grasping at straws now]

Also try DBCC CHECKTABLE ('TBL_INFO')

And let use know what that says...|||Maybe I missed it earlier in the thread but it seems to me as though your date format is wrong.

Try changing your between clause to the following:

between '2003-12-29 00:00:01' and '2003-12-30 23:59:59'

That should work.

Dan|||Not sure if I smell a discrepancy in regional settings here or not, but...

If you run sp_helpdb on your database, what is the collation (under status)? (example: sp_helpdb pubs)

Can you re-write the query without the between, and have it work? As in

where date_time > '29/12/2003 00:00:01'
and date_time < '30/12/2003 23:59:59'

If it works this way, great, but if you get no error and no records, try lowering the bottom constraint ('29/12/2003 00:00:01'), until something gets in range.

If you get no errors, then the query parses and compiles correctly. Maybe you just have a condition like where 1 = 2...|||Oh, and what does the date format look like on the client computer?|||Originally posted by Brett Kaiser
I looked at your attachment...and it does look like the column is defined as datetime...

Your data however doesn't...

Do this and tell us what you get

[grasping at straws now]

SELECT COUNT(*) FROM TBL_INFO WHERE ISDATE(DATE_TIME) = 0

If you get anything other than 0, you'll have non date data...

Which is what your error message is telling you...but not what your table def shows

[/grasping at straws now]

Also try DBCC CHECKTABLE ('TBL_INFO')

And let use know what that says...

When i ran the query
SELECT COUNT(*) FROM TBL_INFO WHERE ISDATE(DATE_TIME) = 0
It returns 0
i rean this query in two different logins such as DB login id and win user
the results are same.
After this i ran the DBCC CHECKTABLE ('TBL_INFO')
it returns
/*----------
DBCC CHECKTABLE ('TBL_INFO')
----------*/
DBCC results for 'TBL_INFO'.
There are 11 rows in 2 pages for object 'TBL_INFO'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

i ran these queries from Query Analyser.

Can you please tel me wat these commands will do
"DBCC check table".|||Originally posted by danielacroft
Maybe I missed it earlier in the thread but it seems to me as though your date format is wrong.

Try changing your between clause to the following:

between '2003-12-29 00:00:01' and '2003-12-30 23:59:59'

That should work.

Dan
Date Format is same dd-mm-yyyy.|||Originally posted by MCrowley
Not sure if I smell a discrepancy in regional settings here or not, but...

If you run sp_helpdb on your database, what is the collation (under status)? (example: sp_helpdb pubs)

Can you re-write the query without the between, and have it work? As in

where date_time > '29/12/2003 00:00:01'
and date_time < '30/12/2003 23:59:59'

If it works this way, great, but if you get no error and no records, try lowering the bottom constraint ('29/12/2003 00:00:01'), until something gets in range.

If you get no errors, then the query parses and compiles correctly. Maybe you just have a condition like where 1 = 2...

Status is
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=539, Collation=SQL_Latin1_General_CP1253_CI_AS, SQLSortOrder=114, IsTornPageDetectionEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics

I even tried that it returns 0 rows. Where data was there in DB.|||What is the result if you do the between using (no convert/no time):

'mm/dd/yyyy' and 'mm/dd/yyyy'
OR this method
'dd/mm/yyyy' and 'dd/mm/yyyy'|||Originally posted by rnealejr
What is the result if you do the between using (no convert/no time):

'mm/dd/yyyy' and 'mm/dd/yyyy'
OR this method
'dd/mm/yyyy' and 'dd/mm/yyyy'

Result is "No rows are returned."|||What the heck is ""???

Those aren't even letter "i's. There a different ASCII character altogether. Could that be some weird regional time format for am/pm? Not that I can find.

Please run this query IN QUERY ANALYZER and post the results:

SELECT distinct top 20
getdate() SystemDate,
DATE_TIME as RawFormat,
convert(varchar(19), DATE_TIME, 120) as Format120,
cast(convert(varchar(19), DATE_TIME, 120) as datetime) as Reconverted
FROM TBL_INFO

blindman|||...and please run this query as well, through QUERY ANALYZER (Crystal frequently mucks things up, especially field formats).

SELECT DATE_TIME
FROM TBL_INFO
where DATE_TIME between '12/29/2003 00:00:01' and '12/30/2003 23:59:59'

blindman|||Originally posted by blindman
What the heck is ""???

Those aren't even letter "i's. There a different ASCII character altogether. Could that be some weird regional time format for am/pm? Not that I can find.

Please run this query IN QUERY ANALYZER and post the results:

SELECT distinct top 20
getdate() SystemDate,
DATE_TIME as RawFormat,
convert(varchar(19), DATE_TIME, 120) as Format120,
cast(convert(varchar(19), DATE_TIME, 120) as datetime) as Reconverted
FROM TBL_INFO

blindman

Sir,
I ran the query it returns
2/1/2004 10:16:10 1/1/2004 7:42:52 2004-01-01 19:42:52 1/1/2004 7:42:52
2/1/2004 10:16:10 1/1/2004 7:46:22 2004-01-01 19:46:22 1/1/2004 7:46:22
2/1/2004 10:16:10 1/1/2004 7:51:06 2004-01-01 19:51:06 1/1/2004 7:51:06
2/1/2004 10:16:10 1/1/2004 7:51:33 2004-01-01 19:51:33 1/1/2004 7:51:33
2/1/2004 10:16:10 1/1/2004 7:55:14 2004-01-01 19:55:14 1/1/2004 7:55:14
2/1/2004 10:16:10 1/1/2004 7:56:40 2004-01-01 19:56:40 1/1/2004 7:56:40
2/1/2004 10:16:10 1/1/2004 7:59:07 2004-01-01 19:59:07 1/1/2004 7:59:07
2/1/2004 10:16:10 1/1/2004 8:19:16 2004-01-01 20:19:16 1/1/2004 8:19:16
2/1/2004 10:16:10 1/1/2004 8:28:54 2004-01-01 20:28:54 1/1/2004 8:28:54
2/1/2004 10:16:10 1/1/2004 8:31:33 2004-01-01 20:31:33 1/1/2004 8:31:33
2/1/2004 10:16:10 1/1/2004 8:41:35 2004-01-01 20:41:35 1/1/2004 8:41:35
2/1/2004 10:16:10 1/1/2004 10:16:58 2004-01-01 22:16:58 1/1/2004 10:16:58
2/1/2004 10:16:10 1/1/2004 10:27:03 2004-01-01 22:27:03 1/1/2004 10:27:03
2/1/2004 10:16:10 1/1/2004 11:14:09 2004-01-01 23:14:09 1/1/2004 11:14:09
2/1/2004 10:16:10 1/1/2004 11:14:14 2004-01-01 23:14:14 1/1/2004 11:14:14
2/1/2004 10:16:10 1/1/2004 11:22:42 2004-01-01 23:22:42 1/1/2004 11:22:42
2/1/2004 10:16:10 2/1/2004 3:11:32 2004-01-02 15:11:32 2/1/2004 3:11:32
2/1/2004 10:16:10 2/1/2004 3:11:47 2004-01-02 15:11:47 2/1/2004 3:11:47
2/1/2004 10:16:10 2/1/2004 3:14:30 2004-01-02 15:14:30 2/1/2004 3:14:30
2/1/2004 10:16:10 2/1/2004 3:22:05 2004-01-02 15:22:05 2/1/2004 3:22:05

"" means PM in Greek. As regional settings is set to Greece,
"" means AM in Greek.|||Originally posted by blindman
...and please run this query as well, through QUERY ANALYZER (Crystal frequently mucks things up, especially field formats).

SELECT DATE_TIME
FROM TBL_INFO
where DATE_TIME between '12/29/2003 00:00:01' and '12/30/2003 23:59:59'

blindman
I ran the query ,
I got the Date time filed value.
Since data has been purged,as it was 30 days old. i have data from 1st alone.

Query is working in both login .. i mean in with DB login and as well as Nt login.
Still i am not clear how come same query was failing last week and now it working in 2 login id woth out nay change?

Last week it was thorwing errro stating that can't convert the char to datetime with NT login id , when i use the convert func.. it was not returning any rows.

but Today query without any convert function is working in both login id .. with DB user id and NT user login

Do u get anything froM this ??/|||There was a change.

The old code was:
between '29/12/2003 00:00:01' and '30/12/2003 23:59:59'

...and the new code is:
between '12/29/2003 00:00:01' and '12/30/2003 23:59:59'

Notice the change in dateformat from dd/mm/yyyy to mm/dd/yyyy.

There is some confusion going on, either within SQL Server or through Crystal reports, when exchanging time formats. Crystal is notoriously unreliable about such things.

If you can use the 'mm/dd/yyyy' format, then that should solve your problem. Better is to use format 120 "yyyy-mm-dd hh:mi:ss(24h)" which is supposedly interpreted correctly across all instances of SQL Server.

blindman|||Originally posted by blindman
There was a change.

The old code was:
between '29/12/2003 00:00:01' and '30/12/2003 23:59:59'

...and the new code is:
between '12/29/2003 00:00:01' and '12/30/2003 23:59:59'

Notice the change in dateformat from dd/mm/yyyy to mm/dd/yyyy.

There is some confusion going on, either within SQL Server or through Crystal reports, when exchanging time formats. Crystal is notoriously unreliable about such things.

If you can use the 'mm/dd/yyyy' format, then that should solve your problem. Better is to use format 120 "yyyy-mm-dd hh:mi:ss(24h)" which is supposedly interpreted correctly across all instances of SQL Server.

blindman
So How does this Query to be changed to format 120

SELECT DATE_TIME, CARDNO, MFLAG, XFLAG, INFO FROM TBL_TRANS where Menuid = 3 and DATE_TIME between '02/01/2004 00:00:01' and '02/01/2004 23:59:59' Order by DATE_TIME ASC

can u pls help me out on this|||SELECT convert(varchar(20),DATE_TIME, 120),CARDNO, MFLAG, XFLAG, INFO FROM TBL_TRANS where Menuid = 3 and DATE_TIME between '02/01/2004 00:00:01' and '02/01/2004 23:59:59' Order by DATE_TIME ASC|||Originally posted by rnealejr
SELECT convert(varchar(20),DATE_TIME, 120),CARDNO, MFLAG, XFLAG, INFO FROM TBL_TRANS where Menuid = 3 and DATE_TIME between '02/01/2004 00:00:01' and '02/01/2004 23:59:59' Order by DATE_TIME ASC

Query is not returning any rows.

But when i run this query with date range from 1 to 2
SELECT convert(varchar(20),DATE_TIME, 120)as DATE_TIME,CARDNO, MFLAG, XFLAG, INFO FROM TBL_TRANS where Menuid = 3 and
DATE_TIME between '01/01/2004 00:00:01' and '02/01/2004 23:59:59' Order by DATE_TIME ASC

it returns all rows of date 1 and 2 ..

And Also when i run it as
SELECT DATE_TIME, CARDNO, MFLAG, XFLAG, INFO FROM TBL_TRANS where Menuid = 3 and
DATE_TIME between '01/01/2004' and '03/01/2004' Order by DATE_TIME ASC
it returns rows which have date 2/1/2004

Any idea wat cud be the problem
Thanking You|||Originally posted by rnealejr
SELECT convert(varchar(20),DATE_TIME, 120),CARDNO, MFLAG, XFLAG, INFO FROM TBL_TRANS where Menuid = 3 and DATE_TIME between '02/01/2004 00:00:01' and '02/01/2004 23:59:59' Order by DATE_TIME ASC

The above query returns all rows of Date 1st alone with
DB login and not returing any rows with NT trusted connection.

Also this query
SELECT DATE_TIME, CARDNO, MFLAG, XFLAG, INFO FROM TBL_TRANS where Menuid = 3 and DATE_TIME between '02/01/2004 00:00:01' and '02/01/2004 23:59:59' Order by DATE_TIME ASC

Is working with DB login id
and not with NT Login id with trusted connection ..
This is my problem ..
This query returns all rows of 2nd alone|||Originally posted by rnealejr
SELECT convert(varchar(20),DATE_TIME, 120),CARDNO, MFLAG, XFLAG, INFO FROM TBL_TRANS where Menuid = 3 and DATE_TIME between '02/01/2004 00:00:01' and '02/01/2004 23:59:59' Order by DATE_TIME ASC

I think i got it

i change the date format to yyyy-mm-dd for the above query..
it is working with nt trusted conenction this wat i need ..

let me check all of my queris and i shall get back to u ASAP..
Thanking you all for ur efforts|||SELECT DATE_TIME, CARDNO, MFLAG, XFLAG, INFO
FROM TBL_TRANS
where Menuid = 3
and
DATE_TIME between '2004-02-01 00:00:01' and '2004-02-01 23:59:59'
Order by DATE_TIME ASC

...should be interpreted by the sql server unambiguously. I don't think you need to do an explicit convert for your situation (maybe you do), but the datestrings in the above example are already in format 120.

The question is, can you get Crystal to submit them in this format?

You haven't talked much about how your process works, but I hope that you put this code into a stored procedure, and then just have crystal call the stored procedure and submit the datetime values as datetime values (not as strings).

blindman|||Can't you call a sproc from Crystal?|||Yes, Crystal will reference stored procedures. They don't appear on the list of data resources by default. You have to go into options on the dialog box and specify that you want the listed. Just one more minor way the crystal sucks.

Have I mentioned before that Crystal sucks?

It does.

blindmansql

Query Engine Error?

I have a report which displays some customer information and their transactions. The data is coming from an MS Access Database. The user must specify a customer either by customer Number or by their Last Name. I have C#.NET code which does this. Unfortunatley the data doesn't display on the report if the customer doesn't have any transactions. I asked another developer and he said to change the joins to right joins. The closest option i have is right outer join so i selected that. When i do this and run the report i get "Query Engine Error". It also displays the filepath to the rpt file. The database has been built up over a number of years and is not well normalized. Therefore i have more tables in the report then I would like. The database is quite large and would take alot of time to normalize that database and the program using the database would have to be altered as well. As you may have guessed, that code has also been developed over a number of years and again would take alot of time to change that. Is there an easy solution to my problem. I am not too experienced with Crystal Reports and any help would be greatly appreciated.Run the right join query in Access and see if it works

query does oposite of what i want

I am trying to find all the user tables that are not mentioned in a view or
stored procedure. Instead, this lists each table once for each stored
procedure it is not appearing.
select so2.name,so.name
from AdminDB.dbo.sysobjects so inner join AdminDB.dbo.syscomments sc on
(so.id=sc.id)
inner join AdminDB.dbo.sysobjects so2 on
(patindex('%so2.name%',sc.text)=0)
where so.xtype in ('P','V')
and so2.xtype ='U'
order by so2.name
GOT IT!
SELECT a.name
FROM AdminDB.dbo.sysobjects a LEFT JOIN (
SELECT so2.name,so.name AS 'usedin'
FROM AdminDB.dbo.sysobjects so INNER JOIN AdminDB.dbo.syscomments sc
ON (so.id=sc.id)
INNER JOIN AdminDB.dbo.sysobjects so2 ON (patindex('%' + so2.name+
'%',sc.text)>0)
WHERE so.xtype IN ('P','V','FN')
AND so2.xtype ='U'
) x ON a.name = x.name
WHERE x.name IS NULL
AND a.xtype IN ('U')
"DBA72" wrote:

> I am trying to find all the user tables that are not mentioned in a view or
> stored procedure. Instead, this lists each table once for each stored
> procedure it is not appearing.
> select so2.name,so.name
> from AdminDB.dbo.sysobjects so inner join AdminDB.dbo.syscomments sc on
> (so.id=sc.id)
> inner join AdminDB.dbo.sysobjects so2 on
> (patindex('%so2.name%',sc.text)=0)
> where so.xtype in ('P','V')
> and so2.xtype ='U'
> order by so2.name
|||I would use the ANSI schema views, as opposed to directly accessing system
tables. Does this yield the same result as your query?
SELECT t.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES t
LEFT OUTER JOIN
(
SELECT
oname = ROUTINE_NAME,
odef = ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
UNION ALL
SELECT
oname = TABLE_NAME,
odef = VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
) o
ON o.odef LIKE '%'+t.TABLE_NAME+'%'
WHERE o.oname IS NULL
Note, of course, that pattern matching isn't perfect, for example there are
these (and probably many other) limitations:
(a) a table name could be mentioned in a comment (false positive)
(b) a table name could be spread across multiple rows for a proc/view>8000
characters (missing)
(c) the view/proc name could be the same as or contain the table name, but
not actually depend on it
http://www.aspfaq.com/
(Reverse address to reply.)
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:BB5D2A28-C9A7-4606-82A6-3E7960E72065@.microsoft.com...[vbcol=seagreen]
> GOT IT!
> SELECT a.name
> FROM AdminDB.dbo.sysobjects a LEFT JOIN (
> SELECT so2.name,so.name AS 'usedin'
> FROM AdminDB.dbo.sysobjects so INNER JOIN AdminDB.dbo.syscomments sc
> ON (so.id=sc.id)
> INNER JOIN AdminDB.dbo.sysobjects so2 ON (patindex('%' + so2.name+
> '%',sc.text)>0)
> WHERE so.xtype IN ('P','V','FN')
> AND so2.xtype ='U'
> ) x ON a.name = x.name
> WHERE x.name IS NULL
> AND a.xtype IN ('U')
> "DBA72" wrote:
or[vbcol=seagreen]

query does oposite of what i want

I am trying to find all the user tables that are not mentioned in a view or
stored procedure. Instead, this lists each table once for each stored
procedure it is not appearing.
select so2.name,so.name
from AdminDB.dbo.sysobjects so inner join AdminDB.dbo.syscomments sc on
(so.id=sc.id)
inner join AdminDB.dbo.sysobjects so2 on
(patindex('%so2.name%',sc.text)=0)
where so.xtype in ('P','V')
and so2.xtype ='U'
order by so2.nameGOT IT!
SELECT a.name
FROM AdminDB.dbo.sysobjects a LEFT JOIN (
SELECT so2.name,so.name AS 'usedin'
FROM AdminDB.dbo.sysobjects so INNER JOIN AdminDB.dbo.syscomments sc
ON (so.id=sc.id)
INNER JOIN AdminDB.dbo.sysobjects so2 ON (patindex('%' + so2.name+
'%',sc.text)>0)
WHERE so.xtype IN ('P','V','FN')
AND so2.xtype ='U'
) x ON a.name = x.name
WHERE x.name IS NULL
AND a.xtype IN ('U')
"DBA72" wrote:

> I am trying to find all the user tables that are not mentioned in a view o
r
> stored procedure. Instead, this lists each table once for each stored
> procedure it is not appearing.
> select so2.name,so.name
> from AdminDB.dbo.sysobjects so inner join AdminDB.dbo.syscomments sc on
> (so.id=sc.id)
> inner join AdminDB.dbo.sysobjects so2 on
> (patindex('%so2.name%',sc.text)=0)
> where so.xtype in ('P','V')
> and so2.xtype ='U'
> order by so2.name|||I would use the ANSI schema views, as opposed to directly accessing system
tables. Does this yield the same result as your query?
SELECT t.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES t
LEFT OUTER JOIN
(
SELECT
oname = ROUTINE_NAME,
odef = ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
UNION ALL
SELECT
oname = TABLE_NAME,
odef = VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
) o
ON o.odef LIKE '%'+t.TABLE_NAME+'%'
WHERE o.oname IS NULL
Note, of course, that pattern matching isn't perfect, for example there are
these (and probably many other) limitations:
(a) a table name could be mentioned in a comment (false positive)
(b) a table name could be spread across multiple rows for a proc/view>8000
characters (missing)
(c) the view/proc name could be the same as or contain the table name, but
not actually depend on it
http://www.aspfaq.com/
(Reverse address to reply.)
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:BB5D2A28-C9A7-4606-82A6-3E7960E72065@.microsoft.com...[vbcol=seagreen]
> GOT IT!
> SELECT a.name
> FROM AdminDB.dbo.sysobjects a LEFT JOIN (
> SELECT so2.name,so.name AS 'usedin'
> FROM AdminDB.dbo.sysobjects so INNER JOIN AdminDB.dbo.syscomments sc
> ON (so.id=sc.id)
> INNER JOIN AdminDB.dbo.sysobjects so2 ON (patindex('%' + so2.name+
> '%',sc.text)>0)
> WHERE so.xtype IN ('P','V','FN')
> AND so2.xtype ='U'
> ) x ON a.name = x.name
> WHERE x.name IS NULL
> AND a.xtype IN ('U')
> "DBA72" wrote:
>
or[vbcol=seagreen]

query database user input from ListBox

I have a problem selecting fields from a table where fields are equal to user input from a listbox. example

listbox of zip codes:

33023
[red]22300[/red]
39844
[red]29339[/red]
23883
[red]38228[/red]

user wants to retreive highlight zip codes from database.
connection working perfect.
Thank you for your help.

you could use IN. check out books on line for more info.

SELECT

<columnlist>

FROM

yourtable

WHERE

zipcode IN ( ....)

|||

Hi There:

You need to retrieve the selected item from yous listbox first. Like this:

<code>

Dim sYourZipString As String = nothing
Dim sItem As ListItem
For Each sItem In zipListBox.Items
If sItem.Selected Then
sYourZipString = sYourZipString & "'" & sItem.Text & "', "
End If
Next

sYourZipString = sYourZipString.Remove(sYourZipString.Length-2, 2) ' remove the tail

</code>

You can use a select IN clause with a parameter which use the value(s) user.

Assume zipcode field is a text field (Char(5), or varchar(5) something not a number).

<code>
Dim conn As SqlConnection
Dim yourcmd As SqlCommand
Dim strConn as string = "yourconnection"

Dim yourSQLString As String
yourSQLString ="Select * FROM youTableIncludeZip WHERE zipcode IN (@.YourZipString)"

conn = New SqlConnection(strConn)

yourcmd = New SqlCommand(strUpdateStmt, conn)
yourcmd.Parameters.Add(New SQLParameter("@.Fname", txtFirstName.text))

Dim objDR as SQLDataReader
dim yourItem1fromDB as string =nothing
dim yourItem2fromDB as string =nothing
dim yourItem3fromDB as string =nothing

Try
conn.Open()
objDR=yourCmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)

While objDR.Read()

yourItem1fromDB=objDR("yourItem1")
yourItem2fromDB=objDR("yourItem2")
yourItem3fromDB=objDR("yourItem3")
' ... get what you want from you Db table
End While

Finally
conn.close

End Try

</code>

Hope this help.

Limno

QUERY BY USER

I have a datagrid with rows that pertain to multiple users. I want to
write a query statement so that whomever the CURRENT logged-in
user is, he can go to the datagrid page and it will show ONLY rows
that pertain to him (programmatic security). To accomplish this,
I have a column in the datatable that references each user's
username. So I need a query statement that will compare the table's
'username' column to the current logged-in user. Something like...

SELECT... FROM [VendorEquipment]
WHERE [UserName] = Context.Current.User.Name

...or something like this. Question is, can someone help on how to
syntax this. Also, do I need anything special in the login form or in
web.config to 'pave the way' for this query to work.

Thx much.
Reid C.after a successfull login,you can store the username in sessions...|||Thanks for the reply...but I am a novice and will probably need more to clear up
the picture...can you suggest how I can syntax my SELECT command to accomplish
the above-stated objective and is there something I need to do via sessions to
enable this--and where would I do that?

Thx much.
Reid C.|||i believe you are checking for user authorization when the user logs in right ?

just put the username into session after the validation is successfull

session("username")=username

and frm any page
you can get it back as

dim uname as string
uname=session("username")

so when you query the db for the records you can do something like

strsql=select * From table where username='" & uname & "'"

and bind the datagrid.

HTH|||Thanks again...this sounds like what I'm looking for...just two
more questions due to my relative inexperience:

1) the statement: session("username")=username
you suggested...does that need to be in the login form
like in the "sub loginBtn_click" subroutine? and if so where in
the subroutine does it need to go...is the placement of the
statement critical?

2) is "username" interchangeable in this operation with "userid" ?
because I have written my registration form and login with a
"userid" instead of a "username"...but I will change this if it is
important to have "username" for forms authentication.

Thx.
Reid C.|||yep you can have any variable in session you want..username,userid...doesnt matter.
right after you check the userid with the password and if the query returns as a valid user, store the userid in sessions...and you should be able to access it frm any page...

HTH|||Thank you ...do I place the session stmt in global.asax?

like this, for instance:

Sub Session_OnStart()
 Session("userId")=userId
End Sub

?

Thx.
Reid C.|||no...you put it in ur login.aspx where ever you validate the authenticity of the user...|||o.k., thanks again, ...but I am a complete novice and have been building pages
using WebMatrix, so sorry to be dense...but do I place the "session..." statement
in the "Function GetUser..." or in my "Sub LoginBtn_click..." subroutine, or after
that and right before the </script> tag ? I know placement of this statement is
important, I just don't know where?

Thx again.
Reid C.|||if you post your code for the login page..i can tell you where to place it...|||Thanks again for all your help...here is the code for the login (minus the html) and
I've got the statement placed right before the end </script> tag...tell me if you see
a glaring case of needing to move it to a more appropriate space.

<script runat="server">
Function GetUser(ByVal userId As String, ByVal userPassword As String) As System.Data.DataSet
 Dim connectionString As String = "server='(local)'; trusted_connection=true; database='VSdatastore'"
 Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
 Dim queryString As String = "SELECT [Users].* FROM [Users] WHERE (([Users].[UserId] = @.UserId) AND ([Users].[U"& _
 "serPassword] = @.UserPassword))"
 Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
 dbCommand.CommandText = queryString
 dbCommand.Connection = dbConnection
 Dim dbParam_userId As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_userId.ParameterName = "@.UserId"
 dbParam_userId.Value = userId
 dbParam_userId.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_userId)
 Dim dbParam_userPassword As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_userPassword.ParameterName = "@.UserPassword"
 dbParam_userPassword.Value = userPassword
 dbParam_userPassword.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_userPassword)
 Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter
 dataAdapter.SelectCommand = dbCommand
 Dim dataSet As System.Data.DataSet = New System.Data.DataSet
 dataAdapter.Fill(dataSet)
 Return dataSet
End Function
Sub LoginBtn_Click(Sender As Object, E As EventArgs)
 If Page.IsValid Then
 Dim userDS As New System.Data.DataSet
 userDS = GetUser(UserId.Text, UserPassword.Text)
 If userDS.Tables(0).Rows.Count = 1 Then
 FormsAuthentication.RedirectFromLoginPage(UserId.Text, false)
 Else
 Msg.Text = "Invalid UserId or Password: Please try again"
 End If
 End If
End Sub
session("UserId")=UserId
</script

Thx again.
Reid C.|||i think this should work...

If Page.IsValid Then
 Dim userDS As New System.Data.DataSet
 userDS = GetUser(UserId.Text, UserPassword.Text)
 If userDS.Tables(0).Rows.Count = 1 Then
 'valid user...
 session("UserId")=UserId
 FormsAuthentication.RedirectFromLoginPage(UserId.Text, false)
 Else
 Msg.Text = "Invalid UserId or Password: Please try again"
 End If
 End If
End Sub
session("UserId")=UserId

|||In your revision you have the "session..." statement right after
the "If userDS.Tables(0).Rows.Count = 1 Then" statement which
made sense as soon as I saw it. But you also have the same
"session..." statement again after the "End Sub" statement. Is
this just a typo on your part from copying and pasting what I
sent you, or does it need to be there?

I am assuming it is a copy&paste oversight...unless you answer
back that it needs to be repeated...

Thx much again for your help.
Reid C.|||See if you can review the code for my datagrid page and see where I'm going wrong.
One thing, the "UserId" I need to compare is in the datatable as "PropUserId" because
I also have a column in the table "VendorUserId" and need to reference both. So I
need to compare the session "UserId" with the table's "PropUserId" column and I've
added a statement in the "Function GetVendorEquipment..." that says the "PropUserId"
column is equivalent to "UserId"...but I am getting a blank table on the page when I
run it.

<script runat="server"
 Function GetVendorEquipment() As System.Data.DataSet
 Dim connectionString As String = "server='(local)'; trusted_connection=true; database='VSdatastore'"
 Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
 dim UserId as String
 dim PropUserId as String
 PropUserId=session("UserId")
 Dim queryString As String = "SELECT [VendorEquipment].* FROM [VendorEquipment] WHERE [PropUserId]='" & UserId & "'"
 Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
 dbCommand.CommandText = queryString
 dbCommand.Connection = dbConnection
 Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter
 dataAdapter.SelectCommand = dbCommand
 Dim dataSet As System.Data.DataSet = New System.Data.DataSet
 dataAdapter.Fill(dataSet)
 Return dataSet
 End Function
 Sub Page_Load(sender As Object, e As EventArgs)
 DataGrid1.DataBind()
 End Sub
</script>

Thx again for your help.
Reid C.|||ur userid seems to be a number...
so you have to use

Dim queryString As String = "SELECT [VendorEquipment].* FROM [VendorEquipment] WHERE [PropUserId]=" & PropUserId

also one other suggestion...bind your datagrid within if not ispostback...loop

Sub Page_Load(sender As Object, e As EventArgs)
 if not ispostback then
 DataGrid1.DataBind()
 end if
End Sub

HTH

Query building based on user selections

I currently have a form that has different options in a list box such as:

Status Codes

Division Codes

Project Managers

Report Types

The user can select a value from any one of these and hit a search button to run a query. Currently I am running through some IF statements to evaluate which combination of values were selected to run the right SQL code. Example

A user wants to view reports that have a status code of "Active" and a division code of "001". So my if statement would be something like (not in perfect syntax, just trying to get the point across):

if strStats <>"" and strDivision <>"" and strProjMgr ="" and strRptType ="" thenselect ...from ...where status_cd = strStats and division_cd = strDivision

end if

So my question is this: Is there a way to build the query dynamically (1 time) without having to run through all the senarios? 

Yes. you could write a query as:

select ...
from ...
where status_cd = ISNULL(@.strStats,status_cd)

and division_cd = ISNULL(@.strDivision,division_cd )

and ...

pass the values for @.strStats and @.strDivision. You can pass the parameters in any combination.

|||So this basically allows a condition to be null or a value can be passed?|||Yes.

Query assistance

I am trying to pull some "notes" from a sql database....the notes that
are put into the database come via the web and the user is entering it
for a certain task. they are stored in their own table and field and
get assigned and incremental ID #.

I want to be able to pull up the latest entry to the task, not all of
the notes just the latest one.. The entry does get a timestamp in the
field so I am thinking I might be able to look at that field
somehow... Right now my query shows all notes / entries for the task.

I am an intermediate sql query guy so I hopefully expained enough to
get assistance.

Let me know if you need to know more.Here is the sample data...
iwmsjn_Note iwmsjn_Timestamp
working on SQL queries for reports2/17/2006 4:20:34 PM
Researching a report for Sheri 2/28/2006 4:35:21 PM
Working on Reports / Queries 3/3/2006 3:34:04 PM
Test Delete 3/8/2006 1:45:30 PM

I only want the one that says "Test Delete" stamped for TODAY...not
the others.|||This should do it...

selectiwmsjn_Note
from[tablename] a
whereiwmsjn_Timestamp = (select max(iwmsjn_Timestamp)
from [tablename] b
where a.[ID] = b.[ID])

Jody