Query longer in SP: select

Showing posts with label select. Show all posts

Friday, March 30, 2012

query question

I saw a query in which there was a expression
SELECT
case when credit_flag&1 = 1 then 1 else 0 end as credit_default_flag1,
case when credit_flag&2 = 2 then 1 else 0 end as credit_default_flag2,
case when credit_flag&4 = 4 then 1 else 0 end as credit_default_flag3
FROM ...
What happens when we do column & 1, column & 2
THanks
Sanjay& is the bit-wise AND operator in T-SQL. So credit_flag &
1 will return 1 on the first bit only when the first bit
from the right (of credit_flag) is set to 1. And
credit_flag & 4 will return 1 on the third bit (thus
decimal 4) only when the third bit from the right (of
credit_flag) is set to 1.
Linchi
>--Original Message--
>I saw a query in which there was a expression
>SELECT
>case when credit_flag&1 = 1 then 1 else 0 end as
credit_default_flag1,
>case when credit_flag&2 = 2 then 1 else 0 end as
credit_default_flag2,
>case when credit_flag&4 = 4 then 1 else 0 end as
credit_default_flag3
>FROM ...
>
>What happens when we do column & 1, column & 2
>THanks
>Sanjay
>.
>|||HI Linch
Do you have some white paper or document which would explain more about these bit-wise operation
I think you explained what the result would be but i am not clear how bit-wise operations work at first plac
Thanks|||The bit-wise AND operator (also OR and XOR) is supported
in most, if not all, programming languages. You can pick
up any programming tutorial book and find information on
the bit-wise operations. You can also find the info in the
SQL Server Books Online.
Linchi
>--Original Message--
>HI Linchi
>Do you have some white paper or document which would
explain more about these bit-wise operations
>I think you explained what the result would be but i am
not clear how bit-wise operations work at first place
>Thanks
>.
>

Query Question

How can I get similar results from a query like this >
SELECT au_lname, state
FROM authors
WHERE state IN ('CA', 'IN', 'MD')
Except to replace ('CA', 'IN', 'MD') With a field that will have a similar
format'
The Field is Misc it's contents are (100, 101, 102) and so on. I need to
display a row for each occurrence of 3 digits in that field and replacing
('CA', 'IN', 'MD') with something like (SELECT au_id FROM titleauthor
WHERE royaltyper < 50) is not the same thing..
I'm thinking I need some type of variable length array, but I am out of
practice and not sure, can some one please help me.."WANNABE" <breichenbach AT istate DOT com> wrote in message
news:e34iWyJwGHA.1216@.TK2MSFTNGP03.phx.gbl...
> How can I get similar results from a query like this >
> SELECT au_lname, state
> FROM authors
> WHERE state IN ('CA', 'IN', 'MD')
> Except to replace ('CA', 'IN', 'MD') With a field that will have a similar
> format'
> The Field is Misc it's contents are (100, 101, 102) and so on. I need to
> display a row for each occurrence of 3 digits in that field and replacing
> ('CA', 'IN', 'MD') with something like (SELECT au_id FROM titleauthor
> WHERE royaltyper < 50) is not the same thing..
> I'm thinking I need some type of variable length array, but I am out of
> practice and not sure, can some one please help me..
>
I'm not completely following here, but you can use either an IN clause or a
WHERE EXISTS clause.
Perhaps something like:
SELECT au_id
FROM titleauthor
WHERE SomeValue IN (SELECT myLookupValues FROM sometable WHERE
somecondition)
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks for you response Rick, but I think what you have described below is
what I have been trying to get to work. When I run this >>
SELECT au_lname, state
FROM authors
WHERE state IN ('CA', 'IN', 'MD')
I get a long list of records. I would like to get the same long list of
records by running something like the following query, AFTER I HAVE MODIFIED
THE stores TABLE TO INCLUDE THE stid FIELD and ENTERED THE VALUE (CA, IN,
MD) into that field for the record where stor_id is equal to 7067.
When I run this next query AFTER I have made the modifications described
above, I get only column headers>>
SELECT au_lname, state
FROM authors
WHERE state IN
(SELECT stid
FROM stores
WHERE stor_id = '7067')
This is all done in testing using the PUBS database, and here are the
queries used to modify that db
alter table pubs.dbo.stores add stid char(50)
UPDATE stores
SET [stid] = '(CA, IN, MD)'
where stor_id = '7067'
======================================="Rick Sawtell" <Quickening@.msn.com> wrote in message
news:%23OwzMOKwGHA.1288@.TK2MSFTNGP02.phx.gbl...
> "WANNABE" <breichenbach AT istate DOT com> wrote in message
> news:e34iWyJwGHA.1216@.TK2MSFTNGP03.phx.gbl...
>> How can I get similar results from a query like this >
>> SELECT au_lname, state
>> FROM authors
>> WHERE state IN ('CA', 'IN', 'MD')
>> Except to replace ('CA', 'IN', 'MD') With a field that will have a
>> similar format'
>> The Field is Misc it's contents are (100, 101, 102) and so on. I need to
>> display a row for each occurrence of 3 digits in that field and replacing
>> ('CA', 'IN', 'MD') with something like (SELECT au_id FROM titleauthor
>> WHERE royaltyper < 50) is not the same thing..
>> I'm thinking I need some type of variable length array, but I am out of
>> practice and not sure, can some one please help me..
> I'm not completely following here, but you can use either an IN clause or
> a WHERE EXISTS clause.
> Perhaps something like:
> SELECT au_id
> FROM titleauthor
> WHERE SomeValue IN (SELECT myLookupValues FROM sometable WHERE
> somecondition)
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||WANNABE wrote:
> Thanks for you response Rick, but I think what you have described below is
> what I have been trying to get to work. When I run this >>
> SELECT au_lname, state
> FROM authors
> WHERE state IN ('CA', 'IN', 'MD')
> I get a long list of records. I would like to get the same long list of
> records by running something like the following query, AFTER I HAVE MODIFIED
> THE stores TABLE TO INCLUDE THE stid FIELD and ENTERED THE VALUE (CA, IN,
> MD) into that field for the record where stor_id is equal to 7067.
> When I run this next query AFTER I have made the modifications described
> above, I get only column headers>>
> SELECT au_lname, state
> FROM authors
> WHERE state IN
> (SELECT stid
> FROM stores
> WHERE stor_id = '7067')
> This is all done in testing using the PUBS database, and here are the
> queries used to modify that db
> alter table pubs.dbo.stores add stid char(50)
> UPDATE stores
> SET [stid] = '(CA, IN, MD)'
> where stor_id = '7067'
You're looking for a way to parse a comma-delimited string and use its
elements in a query. Start by reading this:
http://www.realsqlguy.com/serendipity/archives/4-Parse-A-Delimited-String-Into-A-Table.html
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks Tracy, but that is the opposite of what I am trying to do, which is
to parse a delimited string from a table. Can someone tell me how'
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44E31A57.9030809@.realsqlguy.com...
> WANNABE wrote:
>> Thanks for you response Rick, but I think what you have described below
>> is what I have been trying to get to work. When I run this >>
>> SELECT au_lname, state
>> FROM authors
>> WHERE state IN ('CA', 'IN', 'MD')
>> I get a long list of records. I would like to get the same long list of
>> records by running something like the following query, AFTER I HAVE
>> MODIFIED THE stores TABLE TO INCLUDE THE stid FIELD and ENTERED THE VALUE
>> (CA, IN, MD) into that field for the record where stor_id is equal to
>> 7067.
>> When I run this next query AFTER I have made the modifications described
>> above, I get only column headers>>
>> SELECT au_lname, state
>> FROM authors
>> WHERE state IN
>> (SELECT stid
>> FROM stores
>> WHERE stor_id = '7067')
>> This is all done in testing using the PUBS database, and here are the
>> queries used to modify that db
>> alter table pubs.dbo.stores add stid char(50)
>> UPDATE stores
>> SET [stid] = '(CA, IN, MD)'
>> where stor_id = '7067'
> You're looking for a way to parse a comma-delimited string and use its
> elements in a query. Start by reading this:
> http://www.realsqlguy.com/serendipity/archives/4-Parse-A-Delimited-String-Into-A-Table.html
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

query question

This is my current query that I have in an App I wrote:
MySQL = "select DateEntered,Shipper,PickupDate,PUTime,City, State, Zip,
Consignee, Destination, DState, DZip, PickupNumber, ShippersNumber,
PONumber, Consignee_Ref_Number, Weight, Number_Packages, Carrier,
Carrier_Number, Trailer_Number, ApptDate, ApptTime, IDFProNumber,
DeliveredDate, DeliveredTime, FreightCharges, TransitTime, Comments,
LastUpdate, lastcomment from IntermodalTracingMasterFile where " &
tmpMyShippers & " Order by [" & strSort & "] desc "
This works fine.
I need to modify it a little I need to have one query that will return when
the DeliveredDate is empty
and anohter query to return the ones that have somethign in the
DeliveredDate field.
ThanksOn Wed, 17 Nov 2004 15:58:44 -0800, johnfli wrote:
>This is my current query that I have in an App I wrote:
(snip)
>I need to modify it a little I need to have one query that will return when
>the DeliveredDate is empty
>and anohter query to return the ones that have somethign in the
>DeliveredDate field.
Hi johnfli,
Add "WHERE DeliveredDate IS NULL" or "WHERE DeliveredDate IS NOT NULL"
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

query question

This is my current query that I have in an App I wrote:
MySQL = "select DateEntered,Shipper,PickupDate,PUTime,Ci
ty, State, Zip,
Consignee, Destination, DState, DZip, PickupNumber, ShippersNumber,
PONumber, Consignee_Ref_Number, Weight, Number_Packages, Carrier,
Carrier_Number, Trailer_Number, ApptDate, ApptTime, IDFProNumber,
DeliveredDate, DeliveredTime, FreightCharges, TransitTime, Comments,
LastUpdate, lastcomment from IntermodalTracingMasterFile where " &
tmpMyShippers & " Order by [" & strSort & "] desc "
This works fine.
I need to modify it a little I need to have one query that will return when
the DeliveredDate is empty
and anohter query to return the ones that have somethign in the
DeliveredDate field.
ThanksOn Wed, 17 Nov 2004 15:58:44 -0800, johnfli wrote:

>This is my current query that I have in an App I wrote:
(snip)
>I need to modify it a little I need to have one query that will return when
>the DeliveredDate is empty
>and anohter query to return the ones that have somethign in the
>DeliveredDate field.
Hi johnfli,
Add "WHERE DeliveredDate IS NULL" or "WHERE DeliveredDate IS NOT NULL"
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Query Problems - Server Configuration?

Hi All,
I was running the following Query
SELECT s.r_object_id, s.object_name, s.i_chronicle_id, c.r_object_id as content_id, c.i_full_format, c.page, c.i_rendition, c.page_modifier, sr.r_version_label, f.r_folder_path FROM dm_sysobject_s s, dmr_content_r c, dm_sysobject_r sr, dm_folder_r f, dm_sysobject_r sr2 WHERE (f.r_folder_path = '/accelera.com/contact' OR f.r_folder_path like '/accelera.com/contact/%') AND sr2.r_object_id = s.r_object_id AND sr2.i_folder_id = f.r_object_id AND sr.r_object_id = s.r_object_id AND c.parent_id = s.r_object_id AND sr.r_version_label = 'Active' AND (s.r_modify_date >= DATE('2004-02-02 11.57.34','yyyy-mm-dd hh.mi.ss') OR NOT EXISTS (SELECT r_object_id FROM dm_dbo.dm_webc_80000555_s w WHERE w.i_chronicle_id = s.i_chronicle_id AND w.r_object_id = s.r_object_id AND w.object_name = s.object_name AND w.r_folder_path = f.r_folder_path AND (w.i_full_format = c.i_full_format OR (c.i_full_format = 'html' AND w.i_full_format = 'pub_html' ) OR (c.i_full_format = 'zip_html' AND w.i_full_format = 'zip_pub_html' ) ) )) and s.a_is_hidden = false and s.a_archive = false and ( language_code='en_US' or ( language_code in ('en_US',' ') and s.r_object_id not in ( select r.parent_id from dm_relation r where r.parent_id = s.r_object_id and r.relation_name = 'DM_TRANSLATION_OF' and r.child_id in (select sys1.i_chronicle_id from dm_sysobject_s sys1, dm_sysobject_r sys2 where sys1.language_code in ('en_US',' ') and sys1.r_object_id = sys2.r_object_id and sys2.r_version_label = 'Active' ) ))) ORDER BY s.i_chronicle_id,s.r_object_id
And the Error: "A database error has occurred during the creation of a cursor (' STATE=01000, CODE=1945, MSG=[Microsoft][ODBC SQL Server Driver][SQL Server]Warning! The maximum key length is 900 bytes. The index 'RowsetWorkTableSS' has maximum length of 1020 bytes. For some combination of large values, the insert/update operation will fail.
Can Any one Help to fix this SQL Enterprise Manager?You could try adding the ROBUST PLAN hint to your query. Read more about it in Books Online.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Raj" <anonymous@.discussions.microsoft.com> wrote in message
news:65B68D28-7D07-422A-9938-5E4C5E504FA3@.microsoft.com...
> Hi All,
> I was running the following Query
> SELECT s.r_object_id, s.object_name, s.i_chronicle_id, c.r_object_id as content_id,
c.i_full_format, c.page, c.i_rendition, c.page_modifier, sr.r_version_label, f.r_folder_path FROM
dm_sysobject_s s, dmr_content_r c, dm_sysobject_r sr, dm_folder_r f, dm_sysobject_r sr2 WHERE
(f.r_folder_path = '/accelera.com/contact' OR f.r_folder_path like '/accelera.com/contact/%') AND
sr2.r_object_id = s.r_object_id AND sr2.i_folder_id = f.r_object_id AND sr.r_object_id =s.r_object_id AND c.parent_id = s.r_object_id AND sr.r_version_label = 'Active' AND
(s.r_modify_date >= DATE('2004-02-02 11.57.34','yyyy-mm-dd hh.mi.ss') OR NOT EXISTS (SELECT
r_object_id FROM dm_dbo.dm_webc_80000555_s w WHERE w.i_chronicle_id = s.i_chronicle_id AND
w.r_object_id = s.r_object_id AND w.object_name = s.object_name AND w.r_folder_path =f.r_folder_path AND (w.i_full_format = c.i_full_format OR (c.i_full_format = 'html' AND
w.i_full_format = 'pub_html' ) OR (c.i_full_format = 'zip_html' AND w.i_full_format ='zip_pub_html' ) ) )) and s.a_is_hidden = false and s.a_archive = false and ( language_code='en_US'
or ( language_code in ('en_US',' ') and s.r_object_id not in ( select r.parent_id from dm_relation r
where r.parent_id = s.r_object_id and r.relation_name = 'DM_TRANSLATION_OF' and r.child_id in
(select sys1.i_chronicle_id from dm_sysobject_s sys1, dm_sysobject_r sys2 where sys1.language_code
in ('en_US',' ') and sys1.r_object_id = sys2.r_object_id and sys2.r_version_label = 'Active' ) )))
ORDER BY s.i_chronicle_id,s.r_object_id
> And the Error: "A database error has occurred during the creation of a cursor (' STATE=01000,
CODE=1945, MSG=[Microsoft][ODBC SQL Server Driver][SQL Server]Warning! The maximum key length is 900
bytes. The index 'RowsetWorkTableSS' has maximum length of 1020 bytes. For some combination of large
values, the insert/update operation will fail.
> Can Any one Help to fix this SQL Enterprise Manager?
>|||Perhaps the index indeed is larger that 900 bytes for some rows. This is not configurable. Work with
the vendor of the app and see what can be done to handle this. One possible thing is, of course to
remove the index...
I still suggest you read about what the ROBUST PLAN does. It is likely that the intermediate steps
of the query processing creates some work table for which the row length exceeds the max for SQL
Server.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Raj" <anonymous@.discussions.microsoft.com> wrote in message
news:C6E27085-F620-4B28-8F43-0AD14BD9714B@.microsoft.com...
> Hi,
> The Query is generated by Documentum. As you know Documentum is a middleware that generates a
DQL Query (SQL like Query) on the underlying database. As per the error description, it looks like,
I have some Server configuration problem like
> 1) Maximum length of the index RowSetWorkTableSS = 1020 bytes
> 2) Maximum key length = 900 bytes
> what do these signify? How to increase these from EM?
> -- Tibor Karaszi wrote: --
> You could try adding the ROBUST PLAN hint to your query. Read more about it in Books Online.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Raj" <anonymous@.discussions.microsoft.com> wrote in message
> news:65B68D28-7D07-422A-9938-5E4C5E504FA3@.microsoft.com...
> > Hi All,
> >> I was running the following Query
> >> SELECT s.r_object_id, s.object_name, s.i_chronicle_id, c.r_object_id as content_id,
> c.i_full_format, c.page, c.i_rendition, c.page_modifier, sr.r_version_label, f.r_folder_path
FROM
> dm_sysobject_s s, dmr_content_r c, dm_sysobject_r sr, dm_folder_r f, dm_sysobject_r sr2 WHERE
> (f.r_folder_path = '/accelera.com/contact' OR f.r_folder_path like '/accelera.com/contact/%')
AND
> sr2.r_object_id = s.r_object_id AND sr2.i_folder_id = f.r_object_id AND sr.r_object_id => s.r_object_id AND c.parent_id = s.r_object_id AND sr.r_version_label = 'Active' AND
> (s.r_modify_date >= DATE('2004-02-02 11.57.34','yyyy-mm-dd hh.mi.ss') OR NOT EXISTS (SELECT
> r_object_id FROM dm_dbo.dm_webc_80000555_s w WHERE w.i_chronicle_id = s.i_chronicle_id AND
> w.r_object_id = s.r_object_id AND w.object_name = s.object_name AND w.r_folder_path => f.r_folder_path AND (w.i_full_format = c.i_full_format OR (c.i_full_format = 'html' AND
> w.i_full_format = 'pub_html' ) OR (c.i_full_format = 'zip_html' AND w.i_full_format => 'zip_pub_html' ) ) )) and s.a_is_hidden = false and s.a_archive = false and (
language_code='en_US'
> or ( language_code in ('en_US',' ') and s.r_object_id not in ( select r.parent_id from
dm_relation r
> where r.parent_id = s.r_object_id and r.relation_name = 'DM_TRANSLATION_OF' and r.child_id in
> (select sys1.i_chronicle_id from dm_sysobject_s sys1, dm_sysobject_r sys2 where
sys1.language_code
> in ('en_US',' ') and sys1.r_object_id = sys2.r_object_id and sys2.r_version_label ='Active' ) )))
> ORDER BY s.i_chronicle_id,s.r_object_id
> >> And the Error: "A database error has occurred during the creation of a cursor ('
STATE=01000,
> CODE=1945, MSG=[Microsoft][ODBC SQL Server Driver][SQL Server]Warning! The maximum key length
is 900
> bytes. The index 'RowsetWorkTableSS' has maximum length of 1020 bytes. For some combination
of large
> values, the insert/update operation will fail.
> >> Can Any one Help to fix this SQL Enterprise Manager?
> >

Wednesday, March 28, 2012

Query problem with subtraction

SELECT rm.rmsacctnum AS [Rms Acct Num],
SUM(rf.rmstranamt) - (select rf.rmstranamt FROM RMASTER rm
INNER JOIN RFINANL rff ON rff.RMSFILENUM = rm.RMSFILENUM
where rff.RMSTRANCDE = '10') AS [Current Balance],
FROM RMASTER rm
INNER JOIN RFINANL rf ON rf.RMSFILENUM = rm.RMSFILENUM
GROUP BY rm.rmsacctnum

something wrong with how I'm trying to calculate the subtraction here. I'm not sure the syntaxt to include the divisor in this particular situation. I need to subtract from the rmstranamt where rmstrancde is 10 and there should only be one results for the divisor

This is what I need in my results:
Current Balance and Account Number should be retrieved where
Current Balance = Sum of rmstranamt - (sum of rmstranamt where rmstrancde is 10)

If you need to find Sum of rmstranamt - (sum of rmstranamt where

rmstrancde is 10), then you probably need SUM in your subquery.

If that doesn't help, you'll need to be more specific about what you

mean by "something wrong". Do you get an error message? Do

you get wrong answers? Does your computer explode?

Steve Kass

Drew University|||There were syntax problems throwing errors but I corrected them. Thanks for your reply though.

query problem

i have a msde 2000 database, and one of the field are filled by datetime with the format "yyyy-mm-dd hh:mm:ss".
I make a query like this:
"select * from mytable where myfield like '%2006-06-15%'"
but i didn't get any record for the result and i have make it sure that there are lot of records with that value in myfield. The main problem is the char "-" that separate the date value, becouse if i make a query :
"select * from mytable" then i get all of my records.
Is there any suggest to solf my problem?why u want a like clause on a date time field.

Instead try myfield >= '2006-06-15' and myfield < '2006-06-16'|||Is your date field a datetime datatype, or is it a string?|||Your date is actually formatted more like 0x000097E2008BE35B even though SQL Server displays it as 2006-06-16 13:29 when it returns a value to the client machine. You can use string patterns on it, and SQL Server will happily convert for you, but it isn't efficient and worse yet it isn't always predictable.

The suggested syntax that Ronin gave is both efficient and predictable.

-PatP|||0x000097E2008BE35B?

Is is that late already? I missed my 0x000097E200A4CB80 meeting!

Query problem

CREATE TABLE [Table1] (
[abc] [int] ,
[xyz] [char] (10)
)

insert into abc (1,'z')
insert into abc (2,'y')
insert into abc (3,'z')
select * from table1 where abc in (3,2)
i want the output as follows
3 z
2 y
not

2 y
3 z
please help me out
You should use an ORDER BY:
SELECT * FROM table1 WHERE abc IN (3,2) ORDER BY abc DESC
|||it seems i haven't posted the question properly,
it's not the case of 3 and 2
it may be
SELECT * FROM table1 WHERE abc IN (3,2,8,4,1,7)
then it won't work
i hope i made more clear the Q
thanks

|||No, I'm sorry it's not clear. I really have no idea what you arelooking for. Maybe you can explain with more examples.
|||Do you just want to order it in descending rather than ascending order?

Query problem

I have a count code that works just fine...to get the total..

I need to modify it to select the total where zip = Session("zip")

can anyone help me?

Dim cmd3AsNew Data.SqlClient.SqlCommand("Select count (*) from CustomerInfo", MyConn)

cmd.Connection.Open()

Dim count3AsInteger = cmd3.ExecuteScalar()'this contains the number of records

Label_registeredusers.Text = count3

cmd.Connection.Close()

you can use a parameter in your query to limit the results The parameterized query will be much safer than simply concatenating in the zip code as text in your sql statement as that would expose you to the possibility of a sql injection attack.

Dim paramZipAs New System.Data.SqlClient.SqlParameterparamZip.ParameterName ="@.zip"param.Value = Session("zip")Dim cmd3As New Data.SqlClient.SqlCommand("Select count (*) from CustomerInfo WHERE zip=@.zip", MyConn) cmd3.Parameters.Add(paramZip)cmd.Connection.Open()Dim count3As Integer = cmd3.ExecuteScalar()'this contains the number of recordsLabel_registeredusers.Text = count3cmd.Connection.Close()

|||

You could do this:

Dim cmd3AsNew Data.SqlClient.SqlCommand("Select count (*) from CustomerInfo WHERE zip = '" & Session("zip") &"'", MyConn)

But it would be better to use command Parameters like :|||

A lot easier than I thought... Thank you very much!

sql

query problem

hi,

I need this as a filter criteria in my where clause for my select statement,

I need to set

Year1 = current year and

Year2 = currentyear-1

when current date's [month-day] > july30th

current year is in the form of 07 or 06 or 05

i.e

Year1 = 07

Year2 = 06

when current date's[ month-day] < july01

Year1 = current year - 1 and

Year2 = currentyear-2

Year1 = 06

Year2 = 05

I know I can use datepart, but current date from getdate() function gives

2007-10-07-hh.min.sec

and I need to compare this with

july 10th or june30th at any point of a year

also

set Year1

Year2 in the form 07 or 06 or 05

thnx

I suggest that this, and many date related queries you now have or will have in the future will be so much easier if you were to explore using a Calendar table. See this source.

Seems like you need to compare a date against Jul 01, so DATEDIFF() 'could' help.

Here is one simple use of DATEDIFF():

DECLARE
@.DateToVerify datetime,
@.FYear datetime

SELECT
@.DateToVerify = getdate(),
@.FYear = dateadd( day, 181, dateadd( year, datediff( year, 0, getdate() ), 0 ))

SELECT CASE
WHEN datediff( day, @.FYear, @.DateToVerify ) >= 0 THEN '07'
WHEN datediff( day, @.FYear, @.DateToVerify ) >= (-365) THEN '06'
WHEN datediff( day, @.FYear, @.DateToVerify ) >= (-730) THEN '05'
ELSE '04'
END

Most likely there are many ways to 'skin this cat', as the saying goes. But a Calendar table is often the most robust method of handling date span/range issues.

Query problem

Need answer pls to this problem:
Select charge_id,name,amount from tblcharge t inner join
tblpayments p on p.id = t.id where p.charge_id not in
(select t.charge_id from from tblcharge t inner join
tblpayments p on p.id = t.id where t.amount - p.amount = 0)
This works if data is:
charge_id name amount
1 Johnson -50.00 (from payment table)
1 Johnson 50.00 (from charge table)
but not if
charge_id name amount
1 Johnson -38.60 (from payment table)
1 Johnson -11.40 (from payment table)
1 Johnson 50.00 (from charge table)
Essentially not working if two or more payments were
received.
Pls. help ASAP. Thanks.Select charge_id,name,amount from tblcharge t inner join
tblpayments p on p.id = t.id where p.charge_id not in
(select t.charge_id from from tblcharge t inner join
tblpayments p on p.id = t.id where t.amount - sum(p.amount) = 0
group by t.charge_id )
see if this helps you, don't have the complete DDL so don't know if it is
100% correct
"Merwin12" <anonymous@.discussions.microsoft.com> wrote in message
news:152b01c530b0$8cba8ae0$a601280a@.phx.gbl...
> Need answer pls to this problem:
> Select charge_id,name,amount from tblcharge t inner join
> tblpayments p on p.id = t.id where p.charge_id not in
> (select t.charge_id from from tblcharge t inner join
> tblpayments p on p.id = t.id where t.amount - p.amount = 0)
> This works if data is:
> charge_id name amount
> 1 Johnson -50.00 (from payment table)
> 1 Johnson 50.00 (from charge table)
> but not if
> charge_id name amount
> 1 Johnson -38.60 (from payment table)
> 1 Johnson -11.40 (from payment table)
> 1 Johnson 50.00 (from charge table)
> Essentially not working if two or more payments were
> received.
> Pls. help ASAP. Thanks.|||Try,
Select
t.charge_id,
max(t.[name]),
t.amount
from
tblcharge t
left join
tblpayments p
on p.id = t.id
group by
t.charge_id
having
sum(t.amount) != isnull(sum(p.amount), 0)
go
AMB
"Merwin12" wrote:

> Need answer pls to this problem:
> Select charge_id,name,amount from tblcharge t inner join
> tblpayments p on p.id = t.id where p.charge_id not in
> (select t.charge_id from from tblcharge t inner join
> tblpayments p on p.id = t.id where t.amount - p.amount = 0)
> This works if data is:
> charge_id name amount
> 1 Johnson -50.00 (from payment table)
> 1 Johnson 50.00 (from charge table)
> but not if
> charge_id name amount
> 1 Johnson -38.60 (from payment table)
> 1 Johnson -11.40 (from payment table)
> 1 Johnson 50.00 (from charge table)
> Essentially not working if two or more payments were
> received.
> Pls. help ASAP. Thanks.
>|||Correction,
Select
t.charge_id,
max(t.[name]),
max(t.amount)
from
tblcharge t
left join
tblpayments p
on p.id = t.id
group by
t.charge_id
having
sum(t.amount) != isnull(sum(p.amount), 0)
go
AMB
"Alejandro Mesa" wrote:
> Try,
> Select
> t.charge_id,
> max(t.[name]),
> t.amount
> from
> tblcharge t
> left join
> tblpayments p
> on p.id = t.id
> group by
> t.charge_id
> having
> sum(t.amount) != isnull(sum(p.amount), 0)
> go
>
> AMB
> "Merwin12" wrote:
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications. And stop using that sillly redundant "tbl-" -- this is
SQL, not 1960's BASIC; read ISO-11179 for the rules on data element
names.
You also neve said what you wanted to do, but only posted code that was
not working right. I will further guess that you want the people who
have paid off their charges:
SELECT X.charge_id, X.name
FROM ( SELECT charge_id, name, amt
FROM Charges
UNION ALL
SELECT charge_id, name, amt
FROM Payments) AS X(charge_id, name, amt)
GROUP BY X.charge_id, X.name
HAVING SUM(amt) = 0.00;

query problem

This don't work. I don't know why? Do you?
Thanks Mike

strSQL ="SELECT * FROM annons where lan=" & "'" &_
request.form("lanSelect") & "'" & " AND Rubrik Like" &_
"'%" & request.form("searchStr") & "%'" &_
" AND (registrerad BETWEEN " & FormatDateTime(DateAdd("d", -strTime,
date),bvShortDate) & _
" AND " & FormatDateTime(date,bvShortDate) & ")"Did you get any errors? How about,

strSQL = "SELECT * FROM annons WHERE lan = '" & Request.Form("lanselect") &
"' AND Rubrik LIKE '%' + " & Request.Form("searchstr") & " + '%' AND
registrerad BETWEEN '" & FormatDateTime(DateAdd("d", -strTime, date),
bvShortDate) & "' AND '" & FormatDateTime(date,bvShortDate) & "'"

Watch out for wrapping, single quotes etc.

--
- Anith
( Please reply to newsgroups only )

Monday, March 26, 2012

query problem

not sure whats up with this stored procedure, i basically do my queries in
access and port them over
CREATE PROCEDURE factfindnotsourced AS
SELECT dbo.Personal.ID, dbo.Personal.Surname1, dbo.Lead.FactfindCompleted,
dbo.Lead.FactfindCompletedBy
FROM (dbo.Personal LEFT JOIN dbo.Lead ON dbo.Personal.ID = dbo.Lead.ID) LEFT
JOIN dbo.Mortgage ON dbo.Personal.ID = dbo.Mortgage.ID
WHERE (((dbo.Lead.FactfindCompleted) > 01/01/2004) AND
((dbo.Lead.DateToSourcing)<>01/01/1900) AND
((dbo.Lead.LeadClosed)=01/01/1900) AND
((dbo.Mortgage.MortgageApplicationClosed) Is Null))
ORDER BY dbo.Lead.FactfindCompleted;
GO
dbo.Lead.FactfindCompleted > 01/01/2004 is just being ignored - its
displaying all the records for some reason!> not sure whats up with this stored procedure, i basically do my queries in
> access and port them over
> CREATE PROCEDURE factfindnotsourced AS
> SELECT dbo.Personal.ID, dbo.Personal.Surname1, dbo.Lead.FactfindCompleted,
> dbo.Lead.FactfindCompletedBy
> FROM (dbo.Personal LEFT JOIN dbo.Lead ON dbo.Personal.ID = dbo.Lead.ID)
LEFT
> JOIN dbo.Mortgage ON dbo.Personal.ID = dbo.Mortgage.ID
> WHERE (((dbo.Lead.FactfindCompleted) > 01/01/2004) AND
> ((dbo.Lead.DateToSourcing)<>01/01/1900) AND
> ((dbo.Lead.LeadClosed)=01/01/1900) AND
> ((dbo.Mortgage.MortgageApplicationClosed) Is Null))
> ORDER BY dbo.Lead.FactfindCompleted;
> GO
> dbo.Lead.FactfindCompleted > 01/01/2004 is just being ignored - its
> displaying all the records for some reason!
--
How about changing it to:
dbo.LeadFactfindCompleted Between 01/01/2004 and 31/12/9999
Does it change the result? Is there an index on the LeadFactfindCompleted?
If so, does reindexing change the result?
Hope this helps,
Eric Cárdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.|||You need to enclose date in single quotes. If you don't, you are doing integer arethmics, leading up to an
integer, then converting that integer to datetime and do the comparison (>, <, or whatever you do). Also,
please is a language neutral datetime format. See:
http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mark" <mark@.remove.com> wrote in message news:1kjrc.17$_07.4@.newsfe4-win...
> not sure whats up with this stored procedure, i basically do my queries in
> access and port them over
> CREATE PROCEDURE factfindnotsourced AS
> SELECT dbo.Personal.ID, dbo.Personal.Surname1, dbo.Lead.FactfindCompleted,
> dbo.Lead.FactfindCompletedBy
> FROM (dbo.Personal LEFT JOIN dbo.Lead ON dbo.Personal.ID = dbo.Lead.ID) LEFT
> JOIN dbo.Mortgage ON dbo.Personal.ID = dbo.Mortgage.ID
> WHERE (((dbo.Lead.FactfindCompleted) > 01/01/2004) AND
> ((dbo.Lead.DateToSourcing)<>01/01/1900) AND
> ((dbo.Lead.LeadClosed)=01/01/1900) AND
> ((dbo.Mortgage.MortgageApplicationClosed) Is Null))
> ORDER BY dbo.Lead.FactfindCompleted;
> GO
> dbo.Lead.FactfindCompleted > 01/01/2004 is just being ignored - its
> displaying all the records for some reason!
>
>sql

Query Problem

Can you tell me what is wrong with this line of SQL statement?
if not exists(select @.InitialPasswordInd = InitialPasswordInd
where signonname = @.signonname
and userpassword = @.userpassword)
goto passwordinvalid
Assuming that @.InitialPasswordInd is declare and is the same type as
"InitialPasswordInd".
It does not like @.InitialPasswordInd = InitialPasswordInd
Is this not the proper way to get values form the database?
The query should only return one record. I tried Select Distinct... but I
get the same error (Something wrong near the equals sign.
Thanks in advance for your assistance!!!!!!!!!!!!!!It may be a typo, but I didn't see an FROM clause. Let us know...
James|||You were correct, I did not have a "from" - But I still get same error.
if not exists(select @.InitialPasswordInd = InitialPasswordInd
from dbo.Signon
where signonname = @.signonname
and userpassword = @.userpassword)
goto passwordinvalid|||You can't assign to a variable in a subquery. If you want to get the value,
just
select @.InitialPasswordInd ...
If you want to use the existence in a query,
if not exists (
select InitialPasswordInd
from ...
)
You can't do both in the subquery.
SK
"CJ Silin" <cjssilin@.nospam.com> wrote in message
news:Xns9479867BCDF3Acsilinhotmailcom@.207.46.248.16...
> Can you tell me what is wrong with this line of SQL statement?
> if not exists(select @.InitialPasswordInd = InitialPasswordInd
> where signonname = @.signonname
> and userpassword = @.userpassword)
> goto passwordinvalid
> Assuming that @.InitialPasswordInd is declare and is the same type as
> "InitialPasswordInd".
> It does not like @.InitialPasswordInd = InitialPasswordInd
> Is this not the proper way to get values form the database?
> The query should only return one record. I tried Select Distinct... but I
> get the same error (Something wrong near the equals sign.
> Thanks in advance for your assistance!!!!!!!!!!!!!!|||An EXISTS test is not a data retrieval operation so you can't specify the
variable assignment in the WHERE clause. Also, you have no FROM clause.
If you need to check for data existence and retrieve data, you can check for
a NULL variable value (of a non-NULL column) following the select. For
example:
SELECT @.InitialPasswordInd = InitialPasswordInd
FROM MyTable
WHERE signonname = @.signonname
AND userpassword = @.userpassword
IF @.InitialPasswordInd IS NULL GOTO passwordinvalid
Hope this helps.
Dan Guzman
SQL Server MVP
"CJ Silin" <cjssilin@.nospam.com> wrote in message
news:Xns9479867BCDF3Acsilinhotmailcom@.207.46.248.16...
> Can you tell me what is wrong with this line of SQL statement?
> if not exists(select @.InitialPasswordInd = InitialPasswordInd
> where signonname = @.signonname
> and userpassword = @.userpassword)
> goto passwordinvalid
> Assuming that @.InitialPasswordInd is declare and is the same type as
> "InitialPasswordInd".
> It does not like @.InitialPasswordInd = InitialPasswordInd
> Is this not the proper way to get values form the database?
> The query should only return one record. I tried Select Distinct... but I
> get the same error (Something wrong near the equals sign.
> Thanks in advance for your assistance!!!!!!!!!!!!!!

Query Problem

Hi, I have a problem with the WHERE IN statement
The following statement works ok.
SELECT Id
FROM table1
WHERE (Id IN
('{23ABFD83-0A00-40D2-8E1F-333055062862}','{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}','{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'))
But this statement throws a error : Conversion failed when converting
from a character string to uniqueidentifier.
declare @.Id varchar(4000)
set @.Id = '''{23ABFD83-0A00-40D2-8E1F-333055062862}''' + ',' +
'''{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}'''+ ','
+'''{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'''
SELECT Id
FROM table1
WHERE (Id IN (@.Id))
Anyone have any Ideas?
Thanks
Toby> SELECT Id
> FROM table1
> WHERE (Id IN (@.Id))
The IN clause will treat @.Id as a single value. If the list size is fixed,
you can specify multiple parameters:
SELECT Id
FROM table1
WHERE (Id IN (@.Id1, @.Id2, @.Id3))
See http://www.sommarskog.se/arrays-in-sql.html for a discussion of various
solutions. You also have additional XML options in SQL 2005.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Tobi" <toby.riley@.gmail.com> wrote in message
news:1154771947.162803.55670@.p79g2000cwp.googlegroups.com...
> Hi, I have a problem with the WHERE IN statement
> The following statement works ok.
> SELECT Id
> FROM table1
> WHERE (Id IN
> ('{23ABFD83-0A00-40D2-8E1F-333055062862}','{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}','{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'))
> But this statement throws a error : Conversion failed when converting
> from a character string to uniqueidentifier.
>
> declare @.Id varchar(4000)
> set @.Id = '''{23ABFD83-0A00-40D2-8E1F-333055062862}''' + ',' +
> '''{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}'''+ ','
> +'''{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'''
> SELECT Id
> FROM table1
> WHERE (Id IN (@.Id))
> Anyone have any Ideas?
> Thanks
> Toby
>|||Tobi
Another alternative to dan's suggestion would be to run the query using
sp_executesql which might be easier on your code if you need more than 3
parameters.
e.g.
declare @.Id varchar(4000)
set @.Id = '''{23ABFD83-0A00-40D2-8E1F-333055062862}''' + ',' +
'''{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}'''+ ','
+'''{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'''
declare @.QueryStr as nVarchar(3000)
Select @.QueryStr = 'Select * from table1 where id in (' + @.Id + ')'
exec sp_executesql @.querystr
"Dan Guzman" wrote:
> > SELECT Id
> > FROM table1
> > WHERE (Id IN (@.Id))
> The IN clause will treat @.Id as a single value. If the list size is fixed,
> you can specify multiple parameters:
> SELECT Id
> FROM table1
> WHERE (Id IN (@.Id1, @.Id2, @.Id3))
> See http://www.sommarskog.se/arrays-in-sql.html for a discussion of various
> solutions. You also have additional XML options in SQL 2005.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Tobi" <toby.riley@.gmail.com> wrote in message
> news:1154771947.162803.55670@.p79g2000cwp.googlegroups.com...
> > Hi, I have a problem with the WHERE IN statement
> >
> > The following statement works ok.
> >
> > SELECT Id
> > FROM table1
> > WHERE (Id IN
> > ('{23ABFD83-0A00-40D2-8E1F-333055062862}','{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}','{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'))
> >
> > But this statement throws a error : Conversion failed when converting
> > from a character string to uniqueidentifier.
> >
> >
> > declare @.Id varchar(4000)
> > set @.Id = '''{23ABFD83-0A00-40D2-8E1F-333055062862}''' + ',' +
> > '''{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}'''+ ','
> > +'''{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'''
> > SELECT Id
> > FROM table1
> > WHERE (Id IN (@.Id))
> >
> > Anyone have any Ideas?
> >
> > Thanks
> >
> > Toby
> >
>
>|||Excellent that'll work, thankyou.
t
Chris Hoare wrote:
> Tobi
> Another alternative to dan's suggestion would be to run the query using
> sp_executesql which might be easier on your code if you need more than 3
> parameters.
> e.g.
> declare @.Id varchar(4000)
> set @.Id = '''{23ABFD83-0A00-40D2-8E1F-333055062862}''' + ',' +
> '''{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}'''+ ','
> +'''{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'''
>
> declare @.QueryStr as nVarchar(3000)
> Select @.QueryStr = 'Select * from table1 where id in (' + @.Id + ')'
> exec sp_executesql @.querystr
> "Dan Guzman" wrote:
> > > SELECT Id
> > > FROM table1
> > > WHERE (Id IN (@.Id))
> >
> > The IN clause will treat @.Id as a single value. If the list size is fixed,
> > you can specify multiple parameters:
> >
> > SELECT Id
> > FROM table1
> > WHERE (Id IN (@.Id1, @.Id2, @.Id3))
> >
> > See http://www.sommarskog.se/arrays-in-sql.html for a discussion of various
> > solutions. You also have additional XML options in SQL 2005.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Tobi" <toby.riley@.gmail.com> wrote in message
> > news:1154771947.162803.55670@.p79g2000cwp.googlegroups.com...
> > > Hi, I have a problem with the WHERE IN statement
> > >
> > > The following statement works ok.
> > >
> > > SELECT Id
> > > FROM table1
> > > WHERE (Id IN
> > > ('{23ABFD83-0A00-40D2-8E1F-333055062862}','{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}','{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'))
> > >
> > > But this statement throws a error : Conversion failed when converting
> > > from a character string to uniqueidentifier.
> > >
> > >
> > > declare @.Id varchar(4000)
> > > set @.Id = '''{23ABFD83-0A00-40D2-8E1F-333055062862}''' + ',' +
> > > '''{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}'''+ ','
> > > +'''{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'''
> > > SELECT Id
> > > FROM table1
> > > WHERE (Id IN (@.Id))
> > >
> > > Anyone have any Ideas?
> > >
> > > Thanks
> > >
> > > Toby
> > >
> >
> >
> >

Query Problem

quote:

> Can you tell me what is wrong with this line of SQL statement?
> if not exists(select @.InitialPasswordInd = InitialPasswordInd
> where signonname = @.signonname
> and userpassword = @.userpassword)
> goto passwordinvalid
> Assuming that @.InitialPasswordInd is declare and is the same type as
> "InitialPasswordInd".
> It does not like @.InitialPasswordInd = InitialPasswordInd
> Is this not the proper way to get values form the database?
> The query should only return one record. I tried Select Distinct... but I
> get the same error (Something wrong near the equals sign.
> Thanks in advance for your assistance!!!!!!!!!!!!!!

|||An EXISTS test is not a data retrieval operation so you can't specify the
variable assignment in the WHERE clause. Also, you have no FROM clause.
If you need to check for data existence and retrieve data, you can check for
a NULL variable value (of a non-NULL column) following the select. For
example:
SELECT @.InitialPasswordInd = InitialPasswordInd
FROM MyTable
WHERE signonname = @.signonname
AND userpassword = @.userpassword
IF @.InitialPasswordInd IS NULL GOTO passwordinvalid
Hope this helps.
Dan Guzman
SQL Server MVP
"CJ Silin" <cjssilin@.nospam.com> wrote in message
news:Xns9479867BCDF3Acsilinhotmailcom@.20
7.46.248.16...

quote:

> Can you tell me what is wrong with this line of SQL statement?
> if not exists(select @.InitialPasswordInd = InitialPasswordInd
> where signonname = @.signonname
> and userpassword = @.userpassword)
> goto passwordinvalid
> Assuming that @.InitialPasswordInd is declare and is the same type as
> "InitialPasswordInd".
> It does not like @.InitialPasswordInd = InitialPasswordInd
> Is this not the proper way to get values form the database?
> The query should only return one record. I tried Select Distinct... but I
> get the same error (Something wrong near the equals sign.
> Thanks in advance for your assistance!!!!!!!!!!!!!!

sql

Query Privileges

Hi NG,
How do I select the privileges for a particular user in SQL Server?
sp_helpuser doesn't help. I need to get the list of tables, views etc. that
a particular user have access.
************************************************
This is how I do it in Oracle.
select * from DBA_TAB_PRIVS where grantee = username
************************************************
Thank you in advance.
Hi
I think Aaron wrote this script
CREATE FUNCTION dbo.RoleCheckUser
(
@.UserName sysname,
@.RoleName sysname
)
RETURNS BIT
AS
BEGIN
DECLARE @.RetVal BIT
SET @.RetVal = 0
SELECT @.RetVal = 1
WHERE EXISTS
(
SELECT *
FROM sysmembers membs
JOIN sysusers users on membs.memberuid = users.uid
JOIN sysusers groups on membs.groupuid = groups.uid
WHERE
users.name = @.UserName
AND groups.name = @.RoleName
)
RETURN @.RetVal
END
GO
-- Syntax to use the created function
SELECT dbo.RoleCheckUser('dbo', 'db_owner')
GO
"Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
news:OcAvzDeJIHA.3400@.TK2MSFTNGP03.phx.gbl...
> Hi NG,
> How do I select the privileges for a particular user in SQL Server?
> sp_helpuser doesn't help. I need to get the list of tables, views etc.
> that a particular user have access.
> ************************************************
> This is how I do it in Oracle.
> select * from DBA_TAB_PRIVS where grantee = username
> ************************************************
> Thank you in advance.
>
|||Try executing EXEC sp_helprotect under the required database.
Manu
"Praetorian Guard" wrote:

> Hi NG,
> How do I select the privileges for a particular user in SQL Server?
> sp_helpuser doesn't help. I need to get the list of tables, views etc. that
> a particular user have access.
> ************************************************
> This is how I do it in Oracle.
> select * from DBA_TAB_PRIVS where grantee = username
> ************************************************
> Thank you in advance.
>
>
|||Praetorian Guard (praetorian@.gatekeeper.com) writes:
> How do I select the privileges for a particular user in SQL Server?
> sp_helpuser doesn't help. I need to get the list of tables, views etc.
> that a particular user have access.
> ************************************************
> This is how I do it in Oracle.
> select * from DBA_TAB_PRIVS where grantee = username
> ************************************************
If you are on SQL 2005, have a look on fn_my_permissions and
Has_Perms_By_Name. fn_my_permissons would have been really useful,
had it only accepted a column for the first parameter, but it appears
to only accept strings and variables, so you would have to run a cursor
over it. Has_Perms_By_Name takes a column so it can be used a query,
but you can only check one permission at time.
Both presume that you impersonate the user in question with EXECUTE AS.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Query Privileges

Hi NG,
How do I select the privileges for a particular user in SQL Server?
sp_helpuser doesn't help. I need to get the list of tables, views etc. that
a particular user have access.
****************************************
********
This is how I do it in Oracle.
select * from DBA_TAB_PRIVS where grantee = username
****************************************
********
Thank you in advance.Hi
I think Aaron wrote this script
CREATE FUNCTION dbo.RoleCheckUser
(
@.UserName sysname,
@.RoleName sysname
)
RETURNS BIT
AS
BEGIN
DECLARE @.RetVal BIT
SET @.RetVal = 0
SELECT @.RetVal = 1
WHERE EXISTS
(
SELECT *
FROM sysmembers membs
JOIN sysusers users on membs.memberuid = users.uid
JOIN sysusers groups on membs.groupuid = groups.uid
WHERE
users.name = @.UserName
AND groups.name = @.RoleName
)
RETURN @.RetVal
END
GO
-- Syntax to use the created function
SELECT dbo.RoleCheckUser('dbo', 'db_owner')
GO
"Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
news:OcAvzDeJIHA.3400@.TK2MSFTNGP03.phx.gbl...
> Hi NG,
> How do I select the privileges for a particular user in SQL Server?
> sp_helpuser doesn't help. I need to get the list of tables, views etc.
> that a particular user have access.
> ****************************************
********
> This is how I do it in Oracle.
> select * from DBA_TAB_PRIVS where grantee = username
> ****************************************
********
> Thank you in advance.
>|||Try executing EXEC sp_helprotect under the required database.
Manu
"Praetorian Guard" wrote:

> Hi NG,
> How do I select the privileges for a particular user in SQL Server?
> sp_helpuser doesn't help. I need to get the list of tables, views etc. tha
t
> a particular user have access.
> ****************************************
********
> This is how I do it in Oracle.
> select * from DBA_TAB_PRIVS where grantee = username
> ****************************************
********
> Thank you in advance.
>
>|||Praetorian Guard (praetorian@.gatekeeper.com) writes:
> How do I select the privileges for a particular user in SQL Server?
> sp_helpuser doesn't help. I need to get the list of tables, views etc.
> that a particular user have access.
> ****************************************
********
> This is how I do it in Oracle.
> select * from DBA_TAB_PRIVS where grantee = username
> ****************************************
********
If you are on SQL 2005, have a look on fn_my_permissions and
Has_Perms_By_Name. fn_my_permissons would have been really useful,
had it only accepted a column for the first parameter, but it appears
to only accept strings and variables, so you would have to run a cursor
over it. Has_Perms_By_Name takes a column so it can be used a query,
but you can only check one permission at time.
Both presume that you impersonate the user in question with EXECUTE AS.
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

Friday, March 30, 2012

query question

Query Question

query question

query question

Query Problems - Server Configuration?

Wednesday, March 28, 2012

Query problem with subtraction

query problem

Query problem

Query problem

query problem

Query problem

query problem

Monday, March 26, 2012

query problem

query problem

Query Problem

Query Problem

Query Problem

Query Problem

Query Privileges

Query Privileges

Query longer in SP

Blog Archive

About Me