How to get this relation(Need Sql Query)?

Hi friends

I have one Table called tblCategory.

I have three Column CatID, CatName, ParentID

I have many records in this Table

CatID CatName ParentID

1 Cat1 0

2 Cat2 1

3 Cat3 1

4 Cat4 2

5 Cat5 2

6 Cat6 0

7 Cat7 6

8 Cat8 6

9 Cat9 7

10 Cat10 7

11 Cat11 8

Here I have Main Category which has ParentID 0 [ Cat1 and Cat6 ]

I Have Sub categories of Cat1 Which has ParentID 1(CatID 1 of Cat1) [ Cat2 and Cat 3 ]

Cat 2 has also sub category with ParentID 2 (CatID 2 of Cat2) [ Cat4 and Cat5]

I want result looks like as

Cat1 Cat6 ->>>>>ParentID 0
- Cat2 -Cat7 ->>>>>ParentID 1(CatID of Root Cat1) and ParentID 6(CatID of Root Cat6)
- - Cat4 - -Cat9 ->>>>>PaerntID 2(CatID of Root Cat2) and ParentID 7(CatID of Root Cat7)
- - Cat5 - -Cat10 ->>>>>PaerntID 2(CatID of Root Cat2) and ParentID 7(CatID of Root Cat7)
- Cat3 -Cat8 -->>>>>ParentID 1(CatID of Root Cat1) and ParentID 6(CatID of Root Cat6)
--No record --Cat11

Can anybody give me solution?

Thanks

See if this article on working out JOIN syntax will help:http://www.mikesdotnetting.com/Article.aspx?ArticleID=72

|||

There are two tables but in my case I have only one table.

|||

impathan:

There are two tables but in my case I have only one table.

So you have. Sorry, I saw the word Relation in the title of your post and thought you were after somerthing else.

Is what you are after something like the threaded view of a discussion board or similar? If so, one solution might be a recursive function. If not, could you explain the logic beind the diagram you have supplied? I can't, for example see the basis on which Cat3 and Cat8 on the penultimate line are related.

|||

impathan:

There are two tables but in my case I have only one table.

You can query a table twice in the same statement and join it to itself. :) You just need to give each usage of the table a different table alias.

How to get the total time of the records in a table

Hello,

I have one table that has a column called CallDuration. This columns has always the format "1/01/2000 12:01:38 AM". The date part "1/01/2000" I want to discard, and sum the time part to get a total time in my query. How can I do that?

Thxselect datediff(hour,'1/01/2000 12:01:38 AM', '1/01/2000 11:10:38 AM') Hours,
datediff(minute,'1/01/2000 12:01:38 AM', '1/01/2000 11:10:38 AM') % 60 Minutes,
datediff(minute,'1/01/2000 12:01:38 AM', '1/01/2000 11:10:38 AM') TotalMinutes|||select datediff(hour,min(callduration), max(callduration)) Hours,
datediff(minute,min(callduration), max(callduration)) % 60 Minutes,
datediff(minute,min(callduration), max(callduration)) TotalMinutes

how to get the sum of a group and not the whole column?

?can you explain further? any sample data with desired output will help|||

example:

Account Sales

New

John Doe 1,000,000

George Bush 2,000,000

Juan Luna 3,000,000

6,000,000

Old

Michael Tell 5,000,000

Billy Banks 2,000,000

7,000,000

where Account and Sales are table columns, New and Old are group names.

i want to display the total of the group.

|||anyone? something like a GroupSum() function.|||i have no Reporting services installed on my system but in Crystal Reports, you can do this by creating a Running Total field...|||

You can do this by using a matrix, and put two row groups on it: the first grouping by account age (new/old), the second by name.

Or you can put them in a list control and apply a group to the list, and sum the sales inside the list.

sluggy

|||there's a runningValue function but I'm getting exceptions when I use it. I just want to display the subtotal not the Total of the sales column.|||

CryptoKnight wrote:

i have no Reporting services installed on my system but in Crystal Reports, you can do this by creating a Running Total field...

Account Sales

New

John Doe 1,000,000

George Bush 2,000,000

Juan Luna 3,000,000

6,000,000

Old

Michael Tell 5,000,000

Billy Banks 2,000,000

13,000,000

this is the output when I use the running value.

|||

icemart525 wrote:

there's a runningValue function but I'm getting exceptions when I use it. I just want to display the subtotal not the Total of the sales column.

is there a reset on change of group property? try setting it to reset after every change of group if there's one...|||nope I can't find one.|||I've seen a lot of topics about subtotals in the web but they did not mention how to do it. should I add a new dataset for this?|||

icemart525 wrote:

I've seen a lot of topics about subtotals in the web but they did not mention how to do it. should I add a new dataset for this?

You are confusing me.... i gave you the answer how to do this several replies ago, did you not see it?

And to answer the other question: if you apply a group expression to a container, then any aggregation type functions (sum, avg, etc.) are done within the scope of only that group.

sluggy

|||I'm using a table, my total returns the Total of the column and not the group. I have not tried a list or a matrix though.|||

icemart525 wrote:

I'm using a table, my total returns the Total of the column and not the group. I have not tried a list or a matrix though.

Okay, for this scenario just put your table inside a list, and apply the group expression to the list. The list (with its table) then gets (automatically) repeated for every group there is, and the table will only "see" data that is in that group. Hope that helps

sluggy

|||okay i'll try your solution later, i'll let you know the results as soon as i get it.

How to get the record created date?

How do I delete rows that are older than 180 days if the table does not
contain a column of timestamp? The following statement works fine if
parameter startdate is known:
DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
Thanks in advance.
-tcYou don't. Unless you can join with another table that has a datetime
column. (Every table 'should' have such a column: RecordCreated datetime)
And 'timestamp' as the timestamp datatype has nothing to do with TIME.
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"tcw" <tcwangs@.msn.com> wrote in message
news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> How do I delete rows that are older than 180 days if the table does not
> contain a column of timestamp? The following statement works fine if
> parameter startdate is known:
> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> Thanks in advance.
> -tc
>|||SQL Server does not track this information, so you're going to have to add a
column like:
ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
CURRENT_TIMESTAMP;
Of course, you won't be able to take advantage of the values for this
specifc task until 6 more months have passed.
A
"tcw" <tcwangs@.msn.com> wrote in message
news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> How do I delete rows that are older than 180 days if the table does not
> contain a column of timestamp? The following statement works fine if
> parameter startdate is known:
> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> Thanks in advance.
> -tc
>|||> (Every table 'should' have such a column: RecordCreated datetime)
Whoa, that's a pretty broad generalization. Such a column is useful, but in
most applications it isn't necessary on every single table.
A|||Very True, but I prefer to drive home the point for the generalization, and
then once the idea has taken hold, start dealing with the exceptions. And of
course, not needed at all in some databases.
But for general business data ...
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
> Whoa, that's a pretty broad generalization. Such a column is useful, but
> in most applications it isn't necessary on every single table.
> A
>|||It would be better to drive home the fact that each row should take up as
little space as possible and then deal with the exceptions. That way you
encourage efficiency.
Best regards
Mark Baldwin
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:eiBkZz4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> Very True, but I prefer to drive home the point for the generalization,
> and then once the idea has taken hold, start dealing with the exceptions.
> And of course, not needed at all in some databases.
> But for general business data ...
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>|||I'm less concerned with saving 4 or 8 bytes than with having a 'robust' data
system -I keep hearing storage is inexpensive. Perhaps it's just the segment
of large corporation and government agencies that I work with, but for the
last several years, all projects I've worked with have either required or
benefited from having not only a date column (entered/changed) -but also a
column capturing 'who' was responsible. And updates/deletes from selected
tables may be shadowed to an archive table/server. I tend to work with
VLDB's that must meet requirements set out by Aegis (law enforcement),
SarBox (financial), and/or HIPPA (medical).
Personally, I think that business systems 'should' have date and person
columns on almost every table. They solve so many of the 'problems' that
come up over a project's lifecycle. -Such as the one posted by the OP.
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Mark" <swozz_@.hotmail.com> wrote in message
news:OT$O7e$pGHA.5064@.TK2MSFTNGP05.phx.gbl...
> It would be better to drive home the fact that each row should take up as
> little space as possible and then deal with the exceptions. That way you
> encourage efficiency.
> --
> Best regards
> Mark Baldwin
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:eiBkZz4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
>|||> benefited from having not only a date column (entered/changed) -but also
> a column capturing 'who' was responsible.
That kind of data, imho, belongs in a separate auditing table.
Otherwise, you are forced to either have really wide tables or only track
the LAST person who changed it.

> Personally, I think that business systems 'should' have date and person
> columns on almost every table.
I think for higher level entities that's not a bad idea. For complex OLTP
systems with hundreds of tables, you're going to drown yourself in
information overload.
A|||Thank you very much, guys. I think I will add a timestamp column to my table
next time.
-tc
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:eo6cup4pGHA.148@.TK2MSFTNGP04.phx.gbl...
> SQL Server does not track this information, so you're going to have to add
> a column like:
> ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
> CURRENT_TIMESTAMP;
> Of course, you won't be able to take advantage of the values for this
> specifc task until 6 more months have passed.
> A
>
> "tcw" <tcwangs@.msn.com> wrote in message
> news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
>|||"tcw" <tcwangs@.msn.com> wrote in message
news:egpsl9GqGHA.4932@.TK2MSFTNGP05.phx.gbl...
> Thank you very much, guys. I think I will add a timestamp column to my
table
> next time.
Note: To be perfectly clear, you wanta datetime (or smalldatetime) column.
Timestamp is a separate datatype which actually doesn't map to date or time.

> -tc
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
message
> news:eo6cup4pGHA.148@.TK2MSFTNGP04.phx.gbl...
add[vbcol=seagreen]
>

How to get the record created date?

How do I delete rows that are older than 180 days if the table does not
contain a column of timestamp? The following statement works fine if
parameter startdate is known:
DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
Thanks in advance.
-tcYou don't. Unless you can join with another table that has a datetime
column. (Every table 'should' have such a column: RecordCreated datetime)
And 'timestamp' as the timestamp datatype has nothing to do with TIME.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"tcw" <tcwangs@.msn.com> wrote in message
news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> How do I delete rows that are older than 180 days if the table does not
> contain a column of timestamp? The following statement works fine if
> parameter startdate is known:
> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> Thanks in advance.
> -tc
>|||SQL Server does not track this information, so you're going to have to add a
column like:
ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
CURRENT_TIMESTAMP;
Of course, you won't be able to take advantage of the values for this
specifc task until 6 more months have passed.
A
"tcw" <tcwangs@.msn.com> wrote in message
news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> How do I delete rows that are older than 180 days if the table does not
> contain a column of timestamp? The following statement works fine if
> parameter startdate is known:
> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> Thanks in advance.
> -tc
>|||> (Every table 'should' have such a column: RecordCreated datetime)
Whoa, that's a pretty broad generalization. Such a column is useful, but in
most applications it isn't necessary on every single table.
A|||Very True, but I prefer to drive home the point for the generalization, and
then once the idea has taken hold, start dealing with the exceptions. And of
course, not needed at all in some databases.
But for general business data ...
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>> (Every table 'should' have such a column: RecordCreated datetime)
> Whoa, that's a pretty broad generalization. Such a column is useful, but
> in most applications it isn't necessary on every single table.
> A
>|||It would be better to drive home the fact that each row should take up as
little space as possible and then deal with the exceptions. That way you
encourage efficiency.
--
Best regards
Mark Baldwin
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:eiBkZz4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> Very True, but I prefer to drive home the point for the generalization,
> and then once the idea has taken hold, start dealing with the exceptions.
> And of course, not needed at all in some databases.
> But for general business data ...
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>> (Every table 'should' have such a column: RecordCreated datetime)
>> Whoa, that's a pretty broad generalization. Such a column is useful, but
>> in most applications it isn't necessary on every single table.
>> A
>|||I'm less concerned with saving 4 or 8 bytes than with having a 'robust' data
system -I keep hearing storage is inexpensive. Perhaps it's just the segment
of large corporation and government agencies that I work with, but for the
last several years, all projects I've worked with have either required or
benefited from having not only a date column (entered/changed) -but also a
column capturing 'who' was responsible. And updates/deletes from selected
tables may be shadowed to an archive table/server. I tend to work with
VLDB's that must meet requirements set out by Aegis (law enforcement),
SarBox (financial), and/or HIPPA (medical).
Personally, I think that business systems 'should' have date and person
columns on almost every table. They solve so many of the 'problems' that
come up over a project's lifecycle. -Such as the one posted by the OP.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Mark" <swozz_@.hotmail.com> wrote in message
news:OT$O7e$pGHA.5064@.TK2MSFTNGP05.phx.gbl...
> It would be better to drive home the fact that each row should take up as
> little space as possible and then deal with the exceptions. That way you
> encourage efficiency.
> --
> Best regards
> Mark Baldwin
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:eiBkZz4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
>> Very True, but I prefer to drive home the point for the generalization,
>> and then once the idea has taken hold, start dealing with the exceptions.
>> And of course, not needed at all in some databases.
>> But for general business data ...
>> --
>> Arnie Rowland
>> "To be successful, your heart must accompany your knowledge."
>>
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> message news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>> (Every table 'should' have such a column: RecordCreated datetime)
>> Whoa, that's a pretty broad generalization. Such a column is useful,
>> but in most applications it isn't necessary on every single table.
>> A
>>
>|||> benefited from having not only a date column (entered/changed) -but also
> a column capturing 'who' was responsible.
That kind of data, imho, belongs in a separate auditing table.
Otherwise, you are forced to either have really wide tables or only track
the LAST person who changed it.
> Personally, I think that business systems 'should' have date and person
> columns on almost every table.
I think for higher level entities that's not a bad idea. For complex OLTP
systems with hundreds of tables, you're going to drown yourself in
information overload.
A|||Thank you very much, guys. I think I will add a timestamp column to my table
next time.
-tc
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eo6cup4pGHA.148@.TK2MSFTNGP04.phx.gbl...
> SQL Server does not track this information, so you're going to have to add
> a column like:
> ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
> CURRENT_TIMESTAMP;
> Of course, you won't be able to take advantage of the values for this
> specifc task until 6 more months have passed.
> A
>
> "tcw" <tcwangs@.msn.com> wrote in message
> news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
>> How do I delete rows that are older than 180 days if the table does not
>> contain a column of timestamp? The following statement works fine if
>> parameter startdate is known:
>> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
>> Thanks in advance.
>> -tc
>|||"tcw" <tcwangs@.msn.com> wrote in message
news:egpsl9GqGHA.4932@.TK2MSFTNGP05.phx.gbl...
> Thank you very much, guys. I think I will add a timestamp column to my
table
> next time.
Note: To be perfectly clear, you wanta datetime (or smalldatetime) column.
Timestamp is a separate datatype which actually doesn't map to date or time.
> -tc
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
message
> news:eo6cup4pGHA.148@.TK2MSFTNGP04.phx.gbl...
> > SQL Server does not track this information, so you're going to have to
add
> > a column like:
> >
> > ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
> > CURRENT_TIMESTAMP;
> >
> > Of course, you won't be able to take advantage of the values for this
> > specifc task until 6 more months have passed.
> >
> > A
> >
> >
> > "tcw" <tcwangs@.msn.com> wrote in message
> > news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> >> How do I delete rows that are older than 180 days if the table does not
> >> contain a column of timestamp? The following statement works fine if
> >> parameter startdate is known:
> >>
> >> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> >>
> >> Thanks in advance.
> >>
> >> -tc
> >>
> >
> >
>sql

How to get the record created date?

How do I delete rows that are older than 180 days if the table does not
contain a column of timestamp? The following statement works fine if
parameter startdate is known:
DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
Thanks in advance.
-tcYou don't. Unless you can join with another table that has a datetime
column. (Every table 'should' have such a column: RecordCreated datetime)
And 'timestamp' as the timestamp datatype has nothing to do with TIME.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"tcw" <tcwangs@.msn.com> wrote in message
news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> How do I delete rows that are older than 180 days if the table does not
> contain a column of timestamp? The following statement works fine if
> parameter startdate is known:
> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> Thanks in advance.
> -tc
>|||SQL Server does not track this information, so you're going to have to add a
column like:
ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
CURRENT_TIMESTAMP;
Of course, you won't be able to take advantage of the values for this
specifc task until 6 more months have passed.
A
"tcw" <tcwangs@.msn.com> wrote in message
news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> How do I delete rows that are older than 180 days if the table does not
> contain a column of timestamp? The following statement works fine if
> parameter startdate is known:
> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> Thanks in advance.
> -tc
>|||> (Every table 'should' have such a column: RecordCreated datetime)
Whoa, that's a pretty broad generalization. Such a column is useful, but in
most applications it isn't necessary on every single table.
A|||Very True, but I prefer to drive home the point for the generalization, and
then once the idea has taken hold, start dealing with the exceptions. And of
course, not needed at all in some databases.
But for general business data ...
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>> (Every table 'should' have such a column: RecordCreated datetime)
> Whoa, that's a pretty broad generalization. Such a column is useful, but
> in most applications it isn't necessary on every single table.
> A
>|||It would be better to drive home the fact that each row should take up as
little space as possible and then deal with the exceptions. That way you
encourage efficiency.
--
Best regards
Mark Baldwin
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:eiBkZz4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> Very True, but I prefer to drive home the point for the generalization,
> and then once the idea has taken hold, start dealing with the exceptions.
> And of course, not needed at all in some databases.
> But for general business data ...
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>> (Every table 'should' have such a column: RecordCreated datetime)
>> Whoa, that's a pretty broad generalization. Such a column is useful, but
>> in most applications it isn't necessary on every single table.
>> A
>|||I'm less concerned with saving 4 or 8 bytes than with having a 'robust' data
system -I keep hearing storage is inexpensive. Perhaps it's just the segment
of large corporation and government agencies that I work with, but for the
last several years, all projects I've worked with have either required or
benefited from having not only a date column (entered/changed) -but also a
column capturing 'who' was responsible. And updates/deletes from selected
tables may be shadowed to an archive table/server. I tend to work with
VLDB's that must meet requirements set out by Aegis (law enforcement),
SarBox (financial), and/or HIPPA (medical).
Personally, I think that business systems 'should' have date and person
columns on almost every table. They solve so many of the 'problems' that
come up over a project's lifecycle. -Such as the one posted by the OP.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Mark" <swozz_@.hotmail.com> wrote in message
news:OT$O7e$pGHA.5064@.TK2MSFTNGP05.phx.gbl...
> It would be better to drive home the fact that each row should take up as
> little space as possible and then deal with the exceptions. That way you
> encourage efficiency.
> --
> Best regards
> Mark Baldwin
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:eiBkZz4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
>> Very True, but I prefer to drive home the point for the generalization,
>> and then once the idea has taken hold, start dealing with the exceptions.
>> And of course, not needed at all in some databases.
>> But for general business data ...
>> --
>> Arnie Rowland
>> "To be successful, your heart must accompany your knowledge."
>>
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> message news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>> (Every table 'should' have such a column: RecordCreated datetime)
>> Whoa, that's a pretty broad generalization. Such a column is useful,
>> but in most applications it isn't necessary on every single table.
>> A
>>
>|||> benefited from having not only a date column (entered/changed) -but also
> a column capturing 'who' was responsible.
That kind of data, imho, belongs in a separate auditing table.
Otherwise, you are forced to either have really wide tables or only track
the LAST person who changed it.
> Personally, I think that business systems 'should' have date and person
> columns on almost every table.
I think for higher level entities that's not a bad idea. For complex OLTP
systems with hundreds of tables, you're going to drown yourself in
information overload.
A|||Thank you very much, guys. I think I will add a timestamp column to my table
next time.
-tc
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eo6cup4pGHA.148@.TK2MSFTNGP04.phx.gbl...
> SQL Server does not track this information, so you're going to have to add
> a column like:
> ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
> CURRENT_TIMESTAMP;
> Of course, you won't be able to take advantage of the values for this
> specifc task until 6 more months have passed.
> A
>
> "tcw" <tcwangs@.msn.com> wrote in message
> news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
>> How do I delete rows that are older than 180 days if the table does not
>> contain a column of timestamp? The following statement works fine if
>> parameter startdate is known:
>> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
>> Thanks in advance.
>> -tc
>|||"tcw" <tcwangs@.msn.com> wrote in message
news:egpsl9GqGHA.4932@.TK2MSFTNGP05.phx.gbl...
> Thank you very much, guys. I think I will add a timestamp column to my
table
> next time.
Note: To be perfectly clear, you wanta datetime (or smalldatetime) column.
Timestamp is a separate datatype which actually doesn't map to date or time.
> -tc
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
message
> news:eo6cup4pGHA.148@.TK2MSFTNGP04.phx.gbl...
> > SQL Server does not track this information, so you're going to have to
add
> > a column like:
> >
> > ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
> > CURRENT_TIMESTAMP;
> >
> > Of course, you won't be able to take advantage of the values for this
> > specifc task until 6 more months have passed.
> >
> > A
> >
> >
> > "tcw" <tcwangs@.msn.com> wrote in message
> > news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> >> How do I delete rows that are older than 180 days if the table does not
> >> contain a column of timestamp? The following statement works fine if
> >> parameter startdate is known:
> >>
> >> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> >>
> >> Thanks in advance.
> >>
> >> -tc
> >>
> >
> >
>

How to get the position of a character in a column/String

Hi, I want to substring a part from a column, and for that I need the position of a defined character within the string, for example, I want to know the position of '#':

String: TEst#test2

So I want to know the position from # in the String

Is that possible?

Quote:

Originally Posted by JPete

Hi, I want to substring a part from a column, and for that I need the position of a defined character within the string, for example, I want to know the position of '#':

String: TEst#test2

So I want to know the position from # in the String

Is that possible?

Yes, it it CHARINDEX: SELECT CHARINDEX('#',' TEst#test2')

How to get the password change date in SQL Server

I have searched this topic on google first, and I understood that "xdate2
column of sysxlogins" may store this info, but might not exclusively store
this info... this value changes as extra permissions is granted.
Is there a way to get the password change date? Either within a column of a
table, or programatically?
Thank you in advance.
/SMAC
Actually if you are using SQL Server 2005 you can use
select name,modify_date from sys.server_principals
However ,there is no guarantee that only password has been changed , could
be name as well
"SMAC" <info@.smactool.com> wrote in message
news:rVsQg.340$gE7.320@.newsfe05.lga...
>I have searched this topic on google first, and I understood that "xdate2
>column of sysxlogins" may store this info, but might not exclusively store
>this info... this value changes as extra permissions is granted.
> Is there a way to get the password change date? Either within a column of
> a table, or programatically?
> Thank you in advance.
> /
>|||Thank you Uri,
Any sure way to do this for all SQL 7, 2000, 2005?
Thanks,
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uj94vFW3GHA.4648@.TK2MSFTNGP04.phx.gbl...
> SMAC
> Actually if you are using SQL Server 2005 you can use
> select name,modify_date from sys.server_principals
>
> However ,there is no guarantee that only password has been changed , could
> be name as well
>
>
>
> "SMAC" <info@.smactool.com> wrote in message
> news:rVsQg.340$gE7.320@.newsfe05.lga...
>>I have searched this topic on google first, and I understood that "xdate2
>>column of sysxlogins" may store this info, but might not exclusively store
>>this info... this value changes as extra permissions is granted.
>> Is there a way to get the password change date? Either within a column
>> of a table, or programatically?
>> Thank you in advance.
>> /
>|||Check out the syslogins table (view on 2005).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SMAC" <info@.smactool.com> wrote in message news:j2wQg.5$v14.4@.newsfe02.lga...
> Thank you Uri,
> Any sure way to do this for all SQL 7, 2000, 2005?
> Thanks,
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uj94vFW3GHA.4648@.TK2MSFTNGP04.phx.gbl...
>> SMAC
>> Actually if you are using SQL Server 2005 you can use
>> select name,modify_date from sys.server_principals
>>
>> However ,there is no guarantee that only password has been changed , could
>> be name as well
>>
>>
>>
>> "SMAC" <info@.smactool.com> wrote in message
>> news:rVsQg.340$gE7.320@.newsfe05.lga...
>>I have searched this topic on google first, and I understood that "xdate2
>>column of sysxlogins" may store this info, but might not exclusively store
>>this info... this value changes as extra permissions is granted.
>> Is there a way to get the password change date? Either within a column
>> of a table, or programatically?
>> Thank you in advance.
>> /
>>
>|||In 2005 there is loginproperties I can use, how about SQL 7 and 2000? Any
idea?
Thank you.
/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u9WqBDb3GHA.4560@.TK2MSFTNGP05.phx.gbl...
> Check out the syslogins table (view on 2005).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "SMAC" <info@.smactool.com> wrote in message
> news:j2wQg.5$v14.4@.newsfe02.lga...
>> Thank you Uri,
>> Any sure way to do this for all SQL 7, 2000, 2005?
>> Thanks,
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:uj94vFW3GHA.4648@.TK2MSFTNGP04.phx.gbl...
>> SMAC
>> Actually if you are using SQL Server 2005 you can use
>> select name,modify_date from sys.server_principals
>>
>> However ,there is no guarantee that only password has been changed ,
>> could be name as well
>>
>>
>>
>> "SMAC" <info@.smactool.com> wrote in message
>> news:rVsQg.340$gE7.320@.newsfe05.lga...
>>I have searched this topic on google first, and I understood that
>>"xdate2 column of sysxlogins" may store this info, but might not
>>exclusively store this info... this value changes as extra permissions
>>is granted.
>> Is there a way to get the password change date? Either within a column
>> of a table, or programatically?
>> Thank you in advance.
>> /
>>
>>|||No, that function doesn't exist in earlier versions. I'd go for syslogins on pre-2005 and the
LOGINPROPERTY function on 2005 (version check in your code).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SMAC" <info@.smactool.com> wrote in message news:rSIQg.73$c86.46@.newsfe07.lga...
> In 2005 there is loginproperties I can use, how about SQL 7 and 2000? Any idea?
> Thank you.
> /
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:u9WqBDb3GHA.4560@.TK2MSFTNGP05.phx.gbl...
>> Check out the syslogins table (view on 2005).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "SMAC" <info@.smactool.com> wrote in message news:j2wQg.5$v14.4@.newsfe02.lga...
>> Thank you Uri,
>> Any sure way to do this for all SQL 7, 2000, 2005?
>> Thanks,
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:uj94vFW3GHA.4648@.TK2MSFTNGP04.phx.gbl...
>> SMAC
>> Actually if you are using SQL Server 2005 you can use
>> select name,modify_date from sys.server_principals
>>
>> However ,there is no guarantee that only password has been changed , could be name as well
>>
>>
>>
>> "SMAC" <info@.smactool.com> wrote in message news:rVsQg.340$gE7.320@.newsfe05.lga...
>>I have searched this topic on google first, and I understood that "xdate2 column of sysxlogins"
>>may store this info, but might not exclusively store this info... this value changes as extra
>>permissions is granted.
>> Is there a way to get the password change date? Either within a column of a table, or
>> programatically?
>> Thank you in advance.
>> /
>>
>>
>|||Thanks Tibor,
syslogins doesn't give the reliable password modified date because any
changes to the logins will change the date within syslogins / sysxlogins...
Maybe I'll ask this question in a different way...
when CHECK_EXPIRATION property of the login is set to ON, how does SQL
Server check the password expiration? I believe there is a place where it
stores password creation date right?
Please share your insight.
Thanks!
/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:elCVDFh3GHA.3492@.TK2MSFTNGP06.phx.gbl...
> No, that function doesn't exist in earlier versions. I'd go for syslogins
> on pre-2005 and the LOGINPROPERTY function on 2005 (version check in your
> code).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "SMAC" <info@.smactool.com> wrote in message
> news:rSIQg.73$c86.46@.newsfe07.lga...
>> In 2005 there is loginproperties I can use, how about SQL 7 and 2000?
>> Any idea?
>> Thank you.
>> /
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:u9WqBDb3GHA.4560@.TK2MSFTNGP05.phx.gbl...
>> Check out the syslogins table (view on 2005).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "SMAC" <info@.smactool.com> wrote in message
>> news:j2wQg.5$v14.4@.newsfe02.lga...
>> Thank you Uri,
>> Any sure way to do this for all SQL 7, 2000, 2005?
>> Thanks,
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:uj94vFW3GHA.4648@.TK2MSFTNGP04.phx.gbl...
>> SMAC
>> Actually if you are using SQL Server 2005 you can use
>> select name,modify_date from sys.server_principals
>>
>> However ,there is no guarantee that only password has been changed ,
>> could be name as well
>>
>>
>>
>> "SMAC" <info@.smactool.com> wrote in message
>> news:rVsQg.340$gE7.320@.newsfe05.lga...
>>I have searched this topic on google first, and I understood that
>>"xdate2 column of sysxlogins" may store this info, but might not
>>exclusively store this info... this value changes as extra permissions
>>is granted.
>> Is there a way to get the password change date? Either within a
>> column of a table, or programatically?
>> Thank you in advance.
>> /
>>
>>
>|||Most probably you cannot differentiate between password change and other login modifications in 2000
and earlier. So for those versions, syslogins is what you have.
As for 2005, where is the problem? Why not use the LOGINPROPERTY() function, which you already found
and according to the documentation can answer when password was last changed? Btw, it seems like the
information from that function comes from the same place as the modified_date in sys.sql_logins (as
this doesn't change when I alter the default database for a login...).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SMAC" <info@.smactool.com> wrote in message news:6dQQg.310$aF4.164@.newsfe06.lga...
> Thanks Tibor,
> syslogins doesn't give the reliable password modified date because any changes to the logins will
> change the date within syslogins / sysxlogins...
> Maybe I'll ask this question in a different way...
> when CHECK_EXPIRATION property of the login is set to ON, how does SQL Server check the password
> expiration? I believe there is a place where it stores password creation date right?
> Please share your insight.
> Thanks!
> /
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:elCVDFh3GHA.3492@.TK2MSFTNGP06.phx.gbl...
>> No, that function doesn't exist in earlier versions. I'd go for syslogins on pre-2005 and the
>> LOGINPROPERTY function on 2005 (version check in your code).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "SMAC" <info@.smactool.com> wrote in message news:rSIQg.73$c86.46@.newsfe07.lga...
>> In 2005 there is loginproperties I can use, how about SQL 7 and 2000? Any idea?
>> Thank you.
>> /
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:u9WqBDb3GHA.4560@.TK2MSFTNGP05.phx.gbl...
>> Check out the syslogins table (view on 2005).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "SMAC" <info@.smactool.com> wrote in message news:j2wQg.5$v14.4@.newsfe02.lga...
>> Thank you Uri,
>> Any sure way to do this for all SQL 7, 2000, 2005?
>> Thanks,
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:uj94vFW3GHA.4648@.TK2MSFTNGP04.phx.gbl...
>> SMAC
>> Actually if you are using SQL Server 2005 you can use
>> select name,modify_date from sys.server_principals
>>
>> However ,there is no guarantee that only password has been changed , could be name as well
>>
>>
>>
>> "SMAC" <info@.smactool.com> wrote in message news:rVsQg.340$gE7.320@.newsfe05.lga...
>>>I have searched this topic on google first, and I understood that "xdate2 column of
>>>sysxlogins" may store this info, but might not exclusively store this info... this value
>>>changes as extra permissions is granted.
>>>
>>> Is there a way to get the password change date? Either within a column of a table, or
>>> programatically?
>>>
>>> Thank you in advance.
>>> /
>>>
>>
>>
>|||I believe this is the field that you are looking for:
select xdate2 AS password_change_date,
DATEDIFF(dd, xdate2, GETDATE()) AS days_since_change
FROM master.dbo.sysxlogins
Tibor Karaszi wrote:
> Most probably you cannot differentiate between password change and other login modifications in 2000
> and earlier. So for those versions, syslogins is what you have.
> As for 2005, where is the problem? Why not use the LOGINPROPERTY() function, which you already found
> and according to the documentation can answer when password was last changed? Btw, it seems like the
> information from that function comes from the same place as the modified_date in sys.sql_logins (as
> this doesn't change when I alter the default database for a login...).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "SMAC" <info@.smactool.com> wrote in message news:6dQQg.310$aF4.164@.newsfe06.lga...
> > Thanks Tibor,
> >
> > syslogins doesn't give the reliable password modified date because any changes to the logins will
> > change the date within syslogins / sysxlogins...
> >
> > Maybe I'll ask this question in a different way...
> > when CHECK_EXPIRATION property of the login is set to ON, how does SQL Server check the password
> > expiration? I believe there is a place where it stores password creation date right?
> >
> > Please share your insight.
> >
> > Thanks!
> > /
> >
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> > news:elCVDFh3GHA.3492@.TK2MSFTNGP06.phx.gbl...
> >> No, that function doesn't exist in earlier versions. I'd go for syslogins on pre-2005 and the
> >> LOGINPROPERTY function on 2005 (version check in your code).
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "SMAC" <info@.smactool.com> wrote in message news:rSIQg.73$c86.46@.newsfe07.lga...
> >> In 2005 there is loginproperties I can use, how about SQL 7 and 2000? Any idea?
> >>
> >> Thank you.
> >> /
> >>
> >> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> >> news:u9WqBDb3GHA.4560@.TK2MSFTNGP05.phx.gbl...
> >> Check out the syslogins table (view on 2005).
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "SMAC" <info@.smactool.com> wrote in message news:j2wQg.5$v14.4@.newsfe02.lga...
> >> Thank you Uri,
> >>
> >> Any sure way to do this for all SQL 7, 2000, 2005?
> >>
> >> Thanks,
> >>
> >>
> >> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:uj94vFW3GHA.4648@.TK2MSFTNGP04.phx.gbl...
> >> SMAC
> >> Actually if you are using SQL Server 2005 you can use
> >> select name,modify_date from sys.server_principals
> >>
> >>
> >>
> >> However ,there is no guarantee that only password has been changed , could be name as well
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >> "SMAC" <info@.smactool.com> wrote in message news:rVsQg.340$gE7.320@.newsfe05.lga...
> >>>I have searched this topic on google first, and I understood that "xdate2 column of
> >>>sysxlogins" may store this info, but might not exclusively store this info... this value
> >>>changes as extra permissions is granted.
> >>>
> >>> Is there a way to get the password change date? Either within a column of a table, or
> >>> programatically?
> >>>
> >>> Thank you in advance.
> >>> /
> >>>
> >>
> >>
> >>
> >>
> >>
> >
> >

How to Get the Output Column in OLE DB Command Transformation

Hi,

I am writing a Dataflow task which will take a Particular column from the source table and i am passing the column value in the SQL command property. My SQL Command will look like this,

Select SerialNumber From SerialNumbers Where OrderID = @.OrderID

If i go and check the output column in the Input and output properties tab, I am not able to see this serial number column in the output column tree,So i cant able to access this column in the next transformation component.

Please help me.

Thanks in advance.

Hi,

I am writing a Dataflow task which will take a Particular column from the source table and i am passing the column value in the SQL command property. My SQL Command will look like this,

Select SerialNumber From SerialNumbers Where OrderID = ?

If i go and check the output column in the Input and output properties tab, I am not able to see this serial number column in the output column tree,So i cant able to access this column in the next transformation component.

Please help me.

Thanks in advance.

|||

It sounds as tho you are using the wrong component. To source stuff use the OLE DB Source Adapter, not the OLE DB Command.

-Jamie

|||

Dear Jamie,

Thanks for such a quick reply.

U Mean OLEDB Source From DataFlow Sources.

Actually the My dataflow task contains one OLEDB source component which is having connection to one table, from that table i am getting the orderID column, Then i am passing this OrderID column values to the query Which will get the serialnumber in the SerialNumbers table based on this OrderID. And my problem is i cant able to get this selected serialnumber column in the output column tree view,so i that column is not accessable for futher transformations.

Please give me some solution.

Thanks in advance.

- Dhivya

|||

You need the LOOKUP transform. That s exactly what it does.

-Jamie

|||

Dear Jamie,

That also i tried,the table contains multiple values(for same OrderID multiple serial numbers) and the lookup transform will take only the first value and map the same to the others.

-Dhivya

|||

So its a many-to-many?

Then you should use the MERGE JOIN component!

-Jamie

|||

Good advice, Jamie.

Dhivya, remember that the Merge Join needs a sorted input, so you'll also need to use sort components. Alternatively, use ORDER BY in the source queries, and set the IsSorted property of the source adapter output to True.

Donald

|||

I tried merge join, the problem is order ID is not unique in my source table and in transaction table. so if i put some inner or left joins i am not getting the values what i want.

-Dhivya

|||

Merge Join worked for me. I did right outer join.

Thank u Jamie and donald.

But still my question is, we cant get the output columns in OLE DB Command Component if we use select command?

|||

No. That's not what its for!

-Jamie

|||

OK. Thanks a lot

-Dhivya

How to get the name of the error column from the errorcolumn output

Hi,

Iam redirecting the error output of a OLEDB destination component to a script component. My aim is to create a HTML report having the information about the bad records, the error occuring in the rows and the column name that fails. The error output provided two new columns i.e the errorcode and errorcolumn , the errorcolumn value for a bad record gives the linage id for the column, is there a way to derieve the name of the column by using the lineage id?

Regard,

pritesh

Possible, but not trivial. MS seem to consider this a design-time lookup, so you could use something that queries the package structure to get the info. Simon has written a transform that does this as well, note it needs both outputs to be able to get all the metadata. http://sqlblogcasts.com/files/3/transforms/entry2.aspx|||Thanks for the info , i will try to implement it. It sounds useful !|||

Anonymous wrote:

Thanks for the info , i will try to implement it. It sounds useful !

And if you want to continue posting here, please change your display name from Anonymous to something else. Microsoft staff will be cleaning up those accounts with display names of Anonymous.

How to get the name of the column that is an Identity Column

I would like to know the best method of getting the name of the column if it
is an IDENTITY column. I need to remove the IDENTITY property on all tables
.
Thankshi,
Using syscolumns system table allocated in every db.
"CSHARPITPRO" wrote:

> I would like to know the best method of getting the name of the column if
it
> is an IDENTITY column. I need to remove the IDENTITY property on all tabl
es.
> Thanks|||There is no way to just remove the IDENTITY property using T-SQL, unless you
are just going to drop the column altogether.
If you just want a list so you can go deal with this manually:
SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMNPROPERTY
(
OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsIdentity'
) = 1
ORDER BY TABLE_NAME;
"CSHARPITPRO" <CSHARPITPRO@.discussions.microsoft.com> wrote in message
news:2603575C-72FA-4FB5-A74D-AEA9B32C6EC4@.microsoft.com...
>I would like to know the best method of getting the name of the column if
>it
> is an IDENTITY column. I need to remove the IDENTITY property on all
> tables.
> Thanks|||this will get you a list of all identity columns for user defined tables:
select o.name, col_name(i.object_id, column_id)
from sys.objects o join sys.identity_columns i on o.object_id = i.object_id
where o.type = 'U'
order by o.name|||Thanks Aaron,
This information is very helpful. I am just going to handle it
manually. Thank you very much!
"Aaron Bertrand [SQL Server MVP]" wrote:

> There is no way to just remove the IDENTITY property using T-SQL, unless y
ou
> are just going to drop the column altogether.
> If you just want a list so you can go deal with this manually:
> SELECT TABLE_NAME, COLUMN_NAME
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE COLUMNPROPERTY
> (
> OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsIdentity'
> ) = 1
> ORDER BY TABLE_NAME;
>
>
> "CSHARPITPRO" <CSHARPITPRO@.discussions.microsoft.com> wrote in message
> news:2603575C-72FA-4FB5-A74D-AEA9B32C6EC4@.microsoft.com...
>
>|||> this will get you a list of all identity columns for user defined tables:
> select o.name, col_name(i.object_id, column_id)
> from sys.objects o join sys.identity_columns i on o.object_id =
> i.object_id
> where o.type = 'U'
> order by o.name
Sure, in SQL Server 2005. The user didn't post their target version.
The query I posted works in 2000 as well.|||Thanks Enric
"Enric" wrote:
> hi,
> Using syscolumns system table allocated in every db.
> "CSHARPITPRO" wrote:
>|||Thanks!
"PhxSam" wrote:

> this will get you a list of all identity columns for user defined tables:
> select o.name, col_name(i.object_id, column_id)
> from sys.objects o join sys.identity_columns i on o.object_id = i.object_i
d
> where o.type = 'U'
> order by o.namesql

How to get the metadata of a table

As title, such as the PK, data type of each column, etc.

Thanks,

Ricky.

The "standard" way is to use the information_schema views. It is the best way in 2000:

use tempdb

go

create table viewMetadata

(

column1 varchar(10) primary key,

column2 int unique,

check (column2 > column1)

)

go

select *

from information_schema.tables

where table_name = 'viewMetadata'

select *

from information_schema.columns

where table_name = 'viewMetadata'

select *

from information_schema.table_constraints

where table_name = 'viewMetadata'

etc

And there are more. Look up information_schema in BOL. In 2005, you can also use the system catalog views. They are not as convienient as the information_schema views, but they are a lot more powerful. For example:

select *

from sys.tables

where name = 'viewMetadata'

select *

from sys.columns

where object_id = object_id('viewMetadata')

select *

from sys.check_constraints

where parent_object_id = object_id('viewMetadata')

select *

from sys.key_constraints

where parent_object_id = object_id('viewMetadata')

It is really a big topic, I would suggest you look in BOL for Object Catalog Views.

|||

Hi, Louis. Thanks for your reply first.

However, I got empty results following your instruction like:

select *

from sys.tables

where name = 'AdventureWorks.Person.Address'

By the way, if I want to detect the primary key of 'AdventureWorks.Person.Address', what SQL statement should I issue to SQL 2005?

Regards,

Ricky.

|||

The name of the table is just Address, the schema is Person, and the database is AdventureWorks:

use AdventureWorks

go

select *

from sys.tables

where object_id = object_id('Person.Address')

/* alternative

where name = 'Address'

and schema_id = schema_id('Person')

*/

select *

from sys.key_constraints

where parent_object_id = object_id('Person.Address')

and type = 'PK'

|||

Thanks a lot, Louis.

Ricky.

how to get the last record from the database?

hi friends,

i tried to get the last record in a specified column from a database. but it fails.

for example, i have a column name as "empid" in the table name "detail". in that, the records are in the form of "Emp1, Emp2, Emp3, .........Emp12."

i used this query --> select top 1 empid from detail order by empid desc

it is showing only Emp9.

it should show Emp12.

but the id's are showing in ascending order(Emp1,2,3,.....12) when i used this query --> select empid from detail

i've a gridview to show all Employee id's with paging(1-10 per page). So Emp12 would be showed in the second page. how to get that value "Emp12" from that gridview?

but i need a SQL query rather than this method (getting from gridview).

its urgent. Plz...................

since you are sorting a text column, you will not get a proper numerical sort. the correct text sort sequence would be

Emp1
Emp11
Emp2
Emp21
...
Emp9

in order to approach a numerical sort with text data, you need to store leading zeros with the numeric portion of your text

Emp000001
Emp000002
Emp000009
Emp000011
Emp000021

I would recommend that your seperate out the numerical data though and store it in a column with a numeric datatype.

|||

Uuh!.......... i stored Zeroes........no use............still it is showing "Emp09".............

suggest me another method............

|||

Although frankly speaking I didn't like this approach of storing string int mixed values and then trying it to sort it on the int part only, I thought to create at least something to get out of this kind of problem. I eventually came up with creating a UDF. The UDF code and sample code to test the function is as below. I hope you'll like it, but my advice is to separate out the string and int part, this will help you in days to come. Anyways, below is the code.

--- My functioncreate function ParseInt(@.valuevarchar(8000))returnsbigintasbegindeclare @.char varchar(1)declare @.lengthintdeclare @.counterintdeclare @.lastIndexintdeclare @.varIntvarchar(100)set @.varInt =''set @.counter = 1set @.length =len ( @.value )set @.lastIndex = 0while @.counter <= @.lengthbeginif ( ( @.lastIndex = 0or @.lastIndex + 1 = @.counter )and ( ascii (substring ( @.value , @.counter , 1 ) )between 48and 57 ) )beginset @.varInt = @.varInt +substring ( @.value , @.counter , 1 )set @.lastIndex = @.counterendset @.counter = @.counter + 1endreturncast ( @.varIntas bigint )endGO--- Sample Data to test the function

create table tbl(empvarchar(50))declare @.iintset @.i = 1while @.i <= 30begininsert tbl ( emp )select'empid' +cast ( @.ias varchar)set @.i = @.i + 1endGOselect * , dbo.ParseInt ( emp )from tblorder by dbo.ParseInt ( emp )desc

Hope this will help.

|||

Hi,

To make things simple, just add an identity column (eg: ID) to your table Detail and then fire the query

select top 1 * from Detail order by ID desc

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

|||

Hi,

In case you are want to know how to add an identity column to an existing table :

alter table Detail
add ID int IDENTITY(1,1) not null

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

|||

Hey friends,

no one works which is provided by you.................anyway i found a solution (not for sorting)........... thank u friends, for ur all replies............

How to get the last occurence of rows containing disticnt value in one column

I have a table that tracks GPS records broadly speaking as follows

tsDATETIME
usernameVARCHAR(16)
...
GPS data
...

I want to select the most recent GPS data for each distinct user.
That is, the table will have may records for a given username, but I
only want the most recent for each one.

For a single user I know I can do

SELECT TOP 1 * from <table> order by ts desc

But I want a set of results that effectively does this for
all users, and I can't work out the necessary query/subquery I
should be using.

I'm sure I'm missing something fairly obvious, so usual newbie
disclaimers apply.

--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/What is the primary key? I'll assume the key consists of (ts,
username), in which case the folllowing should do what you want:

SELECT ts, username, ... /* other columns */
FROM YourTable AS T
WHERE ts =
(SELECT MAX(ts)
FROM YourTable
WHERE username = T.username)

It really helps if you include DDL with questions like this (basically
a CREATE TABLE statement, including keys and constraints). The exact
table structure may make a big difference to the possible solutions.
The usual recommendation that you shouldn't use SELECT * in production
code also applies.

--
David Portas
SQL Server MVP
--|||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. Even pseudo-DDL is better than narratives. Is this
what you meant?

CREATE TABLE Foobar
(event_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
user_name VARCHAR(16) NOT NULL,
stuff_1 INTEGER NOT NULL,
stuff_2 INTEGER NOT NULL,
..
stuff_n INTEGER NOT NULL,
PRIMARY KEY (user_name, event_time));

SELECT F1.*
FROM Foobar AS F1
WHERE F1.event_time
= (SELECT MAX(f2.event_time)
FROM Foobar AS F2
WHERE F1.user_name = F2.user_name);
-- use column names in production code, not SELECT *.|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote:

>What is the primary key? I'll assume the key consists of (ts,
>username), in which case the folllowing should do what you want:
>SELECT ts, username, ... /* other columns */
> FROM YourTable AS T
> WHERE ts =
> (SELECT MAX(ts)
> FROM YourTable
> WHERE username = T.username)

Thanks

>It really helps if you include DDL with questions like this (basically
>a CREATE TABLE statement, including keys and constraints). The exact
>table structure may make a big difference to the possible solutions.

Point noted.

>The usual recommendation that you shouldn't use SELECT * in production
>code also applies.

I realise this. That was just me being lazy (although for some of my
selects I do want the entire row).

--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/|||"--CELKO--" <jcelko212@.earthlink.net> 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. Even pseudo-DDL is better than narratives. Is this
>what you meant?

Sorry. Will do in future.

Thanks for the solution.
--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/sql

How to get the full contents of the text column in syscomments

HI All ,

I need to extract the full text of a procedure from the syscomments table. When I run the query in query Analyzer and do a 'save as' to a textfile, I get only part of the text.

Appreciate any response on this.

Thanks

Two things, the first one is that the definition, column [text], is nvarchar(4000), so the output in QA should be a least 4000 characters. You can set it changing "Tools - Options... - Results - Maximum characters per column:" to 8192 (8k).

Second, if the definition, for example of the stored procedure, is greater than 4000, then you will have to select from multiple rows. The value of the column [colid] give you the sequence.

AMB

|||Thanks a lot ,it worked.sql

How to get the Column Name of primary key of a table?

How to get the Column Name of primary key of a table?
Thanks.
--Using SQL 2005 devFrank Lee wrote:
> How to get the Column Name of primary key of a table?
> Thanks.
> --Using SQL 2005 dev
>
>
This example will return the ordered columns that make up the primary
key of the ContactCreditCard table in the AdventureWorks database:
use AdventureWorks
go
select b.TABLE_NAME, COLUMN_NAME, a.ORDINAL_POSITION
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE a join
INFORMATION_SCHEMA.TABLE_CONSTRAINTS b on a.constraint_name =
b.constraint_name
where b.CONSTRAINT_TYPE = 'PRIMARY KEY' and b.TABLE_NAME =
'ContactCreditCard'
order by b.TABLE_NAME, a.ORDINAL_POSITION
The results will return all of the fields that make up the primary key:
TABLE_NAME COLUMN_NAME ORDINAL_POSITION
ContactCreditCard ContactID 1
ContactCreditCard CreditCardID 2|||You might find this procedure as a useful starting point, sorry about the
formatting. One thing I know it doesn't take into account is compound
*foreign* keys. This was developed for a system that doesn't have any of
those...
Basically, this will show every *user* table in your database, whether the
column is in the primary key or not, the name of the column, the type (as
well as length or precision/scale where appropriate), whether the column
allows nulls, and the foreign key reference if it exists (again, I only
dealt with single column references).
This exclusively uses the new sys. catalog views and so you do not need to
qualify objects with built-ins like objectproperty('isMsShipped') etc.
There might be a way to eliminate some of the joins, especially if you don't
need all of the information. Just providing you with what I developed in a
pinch for my requirements.
CREATE PROCEDURE dbo.ShowColumnList
AS
BEGIN
SET NOCOUNT ON;
SELECT
Table_Name = object_name(c.Object_id),
In_Key = CASE WHEN keys.Column_Name IS NOT NULL THEN 'Yes' ELSE 'No' END,
Column_Name = c.name,
Type_Name = UPPER(t.name + CASE
WHEN t.name IN ('VARCHAR','NVARCHAR') AND c.Max_Length = -1 THEN '(MAX)'
WHEN t.name IN ('NCHAR','NVARCHAR') THEN '('+RTRIM(c.Max_Length/2)+')'
WHEN t.name IN ('CHAR', 'VARCHAR') THEN '('+RTRIM(c.Max_Length)+')'
WHEN t.name IN ('NUMERIC','DECIMAL') THEN
'('+RTRIM(c.precision)+','+RTRIM(c.scale)+')'
ELSE '' END
+ CASE c.is_identity WHEN 1 THEN ' -- IDENTITY' ELSE '' END),
Allows_Nulls = CASE c.is_nullable WHEN 1 THEN 'Yes' ELSE 'No' END,
Foreign_Key = COALESCE(fkeys.Ref_Table_Name+'.'+fkeys.Ref_Column_Name, '')
FROM
sys.columns c
INNER JOIN
sys.types t
ON
c.system_type_id = t.system_type_id
AND t.name != 'SYSNAME'
INNER JOIN
sys.tables tb
ON
c.object_id = tb.object_id
LEFT OUTER JOIN
(
SELECT
Table_Name = OBJECT_NAME(t.object_id),
Column_Name = c.name
FROM
sys.index_columns ic WITH (NOLOCK)
INNER JOIN
sys.indexes i WITH (NOLOCK)
ON ic.index_id = i.index_id
AND i.object_id = ic.object_id
INNER JOIN
sys.tables t WITH (NOLOCK)
ON i.object_id = t.object_id
INNER JOIN
sys.key_constraints k WITH (NOLOCK)
ON k.name = i.name
AND k.type='PK'
INNER JOIN
sys.columns c WITH (NOLOCK)
ON c.Object_id = t.object_id
AND c.column_id = ic.column_id
) keys
ON
keys.Column_Name = c.Name
AND keys.Table_Name = tb.Name
LEFT OUTER JOIN
(
SELECT
Table_Name = OBJECT_NAME(k.Parent_Object_ID),
Column_Name = c1.name,
Ref_Table_Name = OBJECT_NAME(k.Referenced_Object_ID),
Ref_Column_Name = c2.name
FROM
sys.foreign_keys k WITH (NOLOCK)
INNER JOIN
sys.foreign_key_columns kc WITH (NOLOCK)
ON
k.object_id = kc.constraint_object_id
INNER JOIN
sys.columns c1 WITH (NOLOCK)
ON
c1.object_id = kc.parent_object_id
AND kc.parent_column_id = c1.column_id
INNER JOIN
sys.columns c2 WITH (NOLOCK)
ON
c2.object_id = kc.referenced_object_id
AND kc.referenced_column_id = c2.column_id
) fkeys
ON
fkeys.Column_Name = c.Name
AND fkeys.Table_Name = tb.Name
ORDER BY
OBJECT_NAME(c.object_id),
c.column_id;
END
GO
"Frank Lee" <Reply@.to.newsgroup> wrote in message
news:%23kwAsBQDGHA.916@.TK2MSFTNGP10.phx.gbl...
> How to get the Column Name of primary key of a table?
> Thanks.
> --Using SQL 2005 dev
>
>

how to get the all the column names of a table?

Can't figure out...

Given a database table name, how do I get all the column names of the table? Actually my task is more than that, what I want is the percentage of null values for each column. But there too many columns in the table and I don't want to type the column name one by one. How to achieve it?

For example, suppose I have a table TB which contains 50 columns C1 to C50. What I want to produce a stored-procedure that table the table name as input parameter and output the percentage of null values for each column C1 to C50, something like:

ColumnName NullPercentage
C1 14.28%

....
C50 6.89%

Thank you!

You can get the column names from system table SYSCOLUMNS.

You can use them to construct a dynamic SQL statement, and then use EXEC(@.string_variable) to run the query.

If you need further assistance, I can try to hunt up some code where I did this sort of thing to estimate the space consumed by a table. (Now with SQL Server 2005 you can find such space information, both for the table and its indexes, with "Properties" after right-clicking on the table name in SSMS.)

HTH.

Dan

|||I am fairy new to sql. If you could have some sample script that would be great! Thanks.
|||

I'll see if I can post the code tomorrow, Friday. Perhaps others here may beat me to it.

Dan

|||

Yes.. Here you go...

Hey Dan I never thought to beat you Buddy ..

Code Snippet

Declare @.Cols as Varchar(8000);

Declare @.PreparedCols as Varchar(8000);

Declare @.TableName as NVarchar(1000);

Select @.TableName = 'Orders'

Select

@.PreparedCols = ',Cast(Sum(Case When ? is null Then 1 Else 0 End)/Sum(1.0) * 100 as Numeric(5,2)) as [?]',

@.Cols = ''

Select

@.Cols = @.Cols + replace(@.PreparedCols, '?', name)

From

syscolumns

Where

id=Object_Id(@.TableName)

Exec ('Select ''NULL Value %'' as Comment' + @.Cols + ' From ' + @.TableName)

|||

Manivannen,

Not only did you beat me, but you provided a much-more compact solution!

I have never before built a dynamic SQL statement using a SELECT to append to an ever-growing SQL statement.

I like it!

Thanks for the education!

Where I tried it, though, I would like to note that I had to change @.COL to VARCHAR(MAX); it must have changed @.COL to NVARCHAR, since I could only print 4000 characters of it. It worked fine when I changed to VARCHAR(MAX). (Without the change the limit is around 40 columns, depending on the lengths of the column names.)

Dan

P.S. Is that enough exclamation points?

|||Beautiful!! Save me tons of time to check nulls.

I had to change to NVarchar(MAX) too, otherwise some of the tables will not work.

I marked both posts as answer.

Thanks!
|||

Thanks for your question, too!

I learn a lot by reading answers in this Forum.

Dan

how to get table structure?

Hi,
There is a command Describe in Oracle to get the table structure (column
names, types, etc. ).
Is there any similar command in SQL server?
Thanks,
Guangmingsp_help tablename
Word 2003 memory Leakage wrote:
> Hi,
> There is a command Describe in Oracle to get the table structure (column
> names, types, etc. ).
> Is there any similar command in SQL server?
> Thanks,
> Guangming|||Following will give you list of columns, data type, size etc.
sp_columns tablename
For more information, please have a look at:
http://www.aspfaq.com/show.asp?id=2177
"Word 2003 memory Leakage" wrote:
> Hi,
> There is a command Describe in Oracle to get the table structure (column
> names, types, etc. ).
> Is there any similar command in SQL server?
> Thanks,
> Guangming|||Both are working. sp_help returns more infor than sp_columns.
It seems they are much slower than describ in Oracle.
but it works.
Thanks,
"Absar Ahmad" wrote:
> Following will give you list of columns, data type, size etc.
> sp_columns tablename
> For more information, please have a look at:
> http://www.aspfaq.com/show.asp?id=2177
> "Word 2003 memory Leakage" wrote:
> > Hi,
> >
> > There is a command Describe in Oracle to get the table structure (column
> > names, types, etc. ).
> >
> > Is there any similar command in SQL server?
> >
> > Thanks,
> >
> > Guangming

How to get String representation of a Hex number?

Hi,
I want to get the string representation of a hex number from a
varBinary column of a table.
For example I want to get the output : 'The Hex value is 0xFF'
but
select 'The Hex value is ' + convert(varchar(10), 0xFF)
retruns the ascii charecter for 0xFF

Any idea how do I get the hex form as it is?
thanks.
Supratim[posted and mailed, please reply in news]

Supratim (supratim@.sagemetrics.com) writes:
> I want to get the string representation of a hex number from a
> varBinary column of a table.
> For example I want to get the output : 'The Hex value is 0xFF'
> but
> select 'The Hex value is ' + convert(varchar(10), 0xFF)
> retruns the ascii charecter for 0xFF
> Any idea how do I get the hex form as it is?

You can use

select master.dbo.fn_varbintohexstr(@.binvalue)

Note, however, that fn_varbintohexstr is un documented function, and thus
unsupported.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||That works exactly the way I want ..
Thanks a lot
-Supratim

Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns9558844747DDEYazorman@.127.0.0.1>...
> [posted and mailed, please reply in news]
> Supratim (supratim@.sagemetrics.com) writes:
> > I want to get the string representation of a hex number from a
> > varBinary column of a table.
> > For example I want to get the output : 'The Hex value is 0xFF'
> > but
> > select 'The Hex value is ' + convert(varchar(10), 0xFF)
> > retruns the ascii charecter for 0xFF
> > Any idea how do I get the hex form as it is?
> You can use
> select master.dbo.fn_varbintohexstr(@.binvalue)
> Note, however, that fn_varbintohexstr is un documented function, and thus
> unsupported.

How to get Stored Procedure output ?

I have a variable @.NetPay as type money, and a stored proc spGetNetPay.
The output of spGetNetPay has one column NetPay, also with type of money, and always has one row.

Now I need assgin output from spGetNetPay to user variable @.NetPay. How can I do That?

Set @.NetPay = (Exec spGetNetPay) Sorry this does not work. Is it possible to create a user defined function?

I have little knowledge about User defided function. Is is the way I should go?

Thanks.

David J.

Create Procedure dbo.spGetNetPay (
 @.NetPay Money OUTPUT
) AS
SET @.NetPay = (Select Top 1 NetPay From SomeTable)
GO

|||Post your code for spGetNetPay to enable determine the best solution for you.|||I use Kay Lee's solution. My spGetNetPay is huge, having more than 200 factors to determine the net pay. However, adding an output parameter is an easy job for me:)

I am still interested in if it is possible to code "user defined function". It has its beauty such that you can add it in select statment column list. But it is totally new for me. I even never seen sample code...