How to get the OS Version

Hey guys,
My team supports databases on about 75 different servers. I would like to know what OS Version is running on those server. I have done some research and I know I can use the following three methods:
1. master..xp_msver
or
2. master..xp_cmdshell 'netsh diag SHOW os /p'
or
3. select right(@.@.version, 44)

are there any other options out there? Option 2 gives me the output I would like, but takes a long time to return the result:
i.e.
Microsoft(R) Windows(R) Server 2003, Standard Edition
5.2.3790

xp_msver and @.@.version gives me the info, but not quite in the format I would like:
5.2 (3790)
and
Windows NT 5.2 (Build 3790: Service Pack 1)

Are there any other options out there?

Thanks,
ReghardtIs this a one time gathering of statistics, or an ongoing thing/ If you have SMS on your network, you can query some of their views much more effectively.|||It will be an ongoing thing, and yes we do have SMS. Thanks for the advice I have to remember to sometimes think outside the box.

How to get the day and day number for a specified month

Hi Guys,

I'm trying to set up a report where the user can select a month, and the report will list the days and day numbers for that month. For example, if the user chooses August then the report will show:-

Wednesday 1st

Thursday 2nd

Friday 3rd

... etc

Can anybody help?

TIA.

Create a calendar table with the necessary data (as many years as you want. it will not be a big table even if you use the entire smalldatetime or datetime date range). You can then write a simple query that retrieves the days based on the month. You can also store other attributes like holidays, different calendars (fiscal, yearly, iso), weekends etc.

|||

If you let them choose the month and year, you can build the start date variable and use this script.

Code Snippet

DECLARE @.dateStart datetime

SET @.dateStart = '08/01/2007'

WHILE MONTH(@.dateStart) = 8

BEGIN

print(DATENAME(dw, @.dateStart) + ' ' + CAST(DAY(@.dateStart) AS VARCHAR(2)))

SET @.dateStart = @.dateStart + 1

END

This gives the output

Code Snippet

Wednesday 1

Thursday 2

Friday 3

Saturday 4

Sunday 5

Monday 6

Tuesday 7

Wednesday 8

Thursday 9

Friday 10

Saturday 11

Sunday 12

Monday 13

Tuesday 14

Wednesday 15

Thursday 16

Friday 17

Saturday 18

Sunday 19

Monday 20

Tuesday 21

Wednesday 22

Thursday 23

Friday 24

Saturday 25

Sunday 26

Monday 27

Tuesday 28

Wednesday 29

Thursday 30

Friday 31

sql

How to get the amount of bytes exchanged during a Merge replication between 2 MSSQL server

Hi, guys.
A very simple question for all of you: how can I get the amount of
bytes exchanged during a Merge replication between two Microsoft SQL
2000 servers?

Thank you.
Bye,
Angelo.-"Angelo Mariani" <pzgrgr@.hotmail.com> wrote in message
news:d03f5ff7.0407190523.3284dd8a@.posting.google.c om...
> Hi, guys.
> A very simple question for all of you: how can I get the amount of
> bytes exchanged during a Merge replication between two Microsoft SQL
> 2000 servers?
> Thank you.
> Bye,
> Angelo.-

I don't believe there's any API to get this information - you could use
Perfmon to trace the merge agent changes per second, and make an estimate
based on average row size, but that's probably not going to be very
accurate. You may want to post in microsoft.public.sqlserver.replication to
see if you get a better answer.

Simon

how to get rowcount from table

Hi guys, can anybody help to solve this problem.

set @.count=0
Insert into User_t (userid, counter) select userid, count+1 from resultset is not working

0/p: bhasker 1
bhanu 1
kishore 1

but o/p must be

bhasker 1
bhanu 2
kishore 3

You can't do it with a query like that. Here are two alternatives

1. Declare the counter field as an identity integer (it'll increment by one each time)

or

2. cursor around the resultset, inserting one record at a time and incrementing by one within the loop.

|||

Here's a neat trick.

Create a table variable with an additional column to store the counter value. And insert into the table from your SELECT. do an UPDATE on the Table variable as follows.

Declare @.TTable (useridvarchar(10), Counterint)Insert into @.TSelect'bhasker',0unionallSelect'bhanu',0unionallSelect'kishore',0select *from @.tDeclare @.iintSet @.i = 0Update @.TSet @.i = Counter = @.i + 1Select *from @.t

|||

Have a look at this. But I think you can do somthing like this.

http://support.microsoft.com/kb/186133

select rank=count(*), a1.name from addresses a1, addresses a2
where a1.name >= a2.name
group by a1.name
order by rank

|||

My exact question is:

INSERTINTO UserChargeDetail(UserID, Offering, Counter,OfferingDetail,

Comment, ValueInput, CostCenterInput,Month)

SELECT UserID, tierOrder,rowsequence,NULL,NULL, 1,'',GetDate()FROM CostCenter

how do I get rowsequence

rowsequence: 1,2,3,4,5

|||

You were shown at least 2 or more working solutions on getting the sequence number. Please put some effort in trying out those solutions. If you are still unable to resolve your issue, please post what you have tried so we can guide you into solving the issue.

How to get month from a datetime type?

Hello guys,

I have datetime value as "mm/dd/yyyy h:m:s", my question is as title. help pls!

Cheers,

Elton

SUBSTRING(<your-value>, 1, 2)

-Jamie

|||use DATEPART function in expressions|||

If the source column is a date type, such as DT_DBTIMESTAMP, then you can use the date related functions, DATEPART, or there is also a more direct MONTH function.

Integration Services Expression Reference
(http://msdn2.microsoft.com/en-us/library/8b80403f-6d45-4001-8b12-25a933c663a2.aspx)

how to get latest record from a table?

hi guys.
i have a table that store all transactions. In this table, a member can has multiple records, there is a field to store the date.
1. the problem i'm facing is, i need to retrieve only the latest record. for example, the transaction table contains of 20 records for 5 members, i need to retrieve the latest record for 5 members.
2. another problem that i face is, how can i make comparison with the date. let say i want to retrieve the record that more than 90 days?

thanks for all advises.

Engloon:

For #1 you need to make sure that you have an index on "member id" so that you can efficiently retrieve records based on the "member id."

For #2 you need to have an index baded on the date and you need to make sure that your field is has a "datetime" datatype.

declare @.member table
( memberId integer,
tranDT datetime
)
insert into @.member
select 5, '1/9/6' union all
select 5, '5/8/6' union all
select 5, '12/15/6' union all
select 5, '3/12/7'
--select * from @.member

/*
memberId tranDT
-- --
5 2006-01-09 00:00:00.000
5 2006-05-08 00:00:00.000
5 2006-12-15 00:00:00.000
5 2006-03-12 00:00:00.000
*/

select top 1
memberId,
tranDt
from @.member
order by tranDt desc

/*
memberId tranDt
-- --
5 2006-12-15 00:00:00.000
*/

select memberId,
tranDt
from @.member
where tranDt < getdate() - 90
order by tranDt desc

/*
memberId tranDt
--
5 2006-12-15 00:00:00.000
5 2006-05-08 00:00:00.000
5 2006-01-09 00:00:00.000
*/

|||

1. This should give you an idea about how to get the latest entry for each member. (I assume you have a MemberID column.)

Code Snippet

SELECT
MemberID,
max( TransactionDate )
FROM MyTable
GROUP BY MemberID
ORDER BY MemberID

2. Add a WHERE clause, something like this:

Code Snippet

WHERE TransactionDate < ( dateadd( day, -90, getdate() ))

|||many thanks to Arnie Rowland and Kent Waldrop. I appreciate your help.
but the @.member table contains not only 1 member. I'm thinking to use distinct function to get only 1 record for each member.
Will let you know if I can solve the problem.

|||here is the result of my sql query
MembershipID Date
3 03-Jan-2007
8 05-Aug-2006
8 18-Sep-2005
8 18-Sep-2005
187 16-May-2006
187 14-May-2006
187 06-Jun-2006
187 29-Jul-2005
187 05-Jan-2007
195 14-Mar-2006
239 29-Aug-2005
275 07-Aug-2005
303 28-Dec-2005
303 19-Dec-2006
306 03-Oct-2005
306 16-Dec-2005

the result that i desire would be like this
MembershipID Date
3 03-Jan-2007
8 05-Aug-2006
187 16-May-2006
195 14-Mar-2006
239 29-Aug-2005
275 07-Aug-2005
303 28-Dec-2005
306 03-Oct-2005
|||

Use the following query...

Code Snippet

Select Identity(Int,1,1) as UID,Id,Date Into #Temp from Members;

Select Mem.Id,Mem.Date From #Temp Mem
Join (Select Min(UId) UID,Id from #Temp Group By Id) as LastRec On LastRec.UID = Mem.UID;

Drop Table #Temp;

|||

Strange, it 'sounds' like you are 'blowing off' the solution that I provided. Too bad, because using the data you posted, and the query I provided, the exact resultset you asked for is produced.

Code Snippet

DECLARE @.MyTable table
( MembershipID int,
TransactionDate datetime
)

SET NOCOUNT ON

INSERT INTO @.MyTable VALUES ( 3, '03-Jan-2007' )
INSERT INTO @.MyTable VALUES ( 8, '05-Aug-2006' )
INSERT INTO @.MyTable VALUES ( 8, '18-Sep-2005' )
INSERT INTO @.MyTable VALUES ( 8, '18-Sep-2005' )
INSERT INTO @.MyTable VALUES ( 187, '16-May-2006' )
INSERT INTO @.MyTable VALUES ( 187, '14-May-2006' )
INSERT INTO @.MyTable VALUES ( 187, '06-Jun-2006' )
INSERT INTO @.MyTable VALUES ( 187, '29-Jul-2005' )
INSERT INTO @.MyTable VALUES ( 187, '05-Jan-2007' )
INSERT INTO @.MyTable VALUES ( 195, '14-Mar-2006' )
INSERT INTO @.MyTable VALUES ( 239, '29-Aug-2005' )
INSERT INTO @.MyTable VALUES ( 275, '07-Aug-2005' )
INSERT INTO @.MyTable VALUES ( 303, '28-Dec-2005' )
INSERT INTO @.MyTable VALUES ( 303, '19-Dec-2006' )
INSERT INTO @.MyTable VALUES ( 306, '03-Oct-2005' )
INSERT INTO @.MyTable VALUES ( 306, '16-Dec-2005' )

SELECT
MembershipID,
TransactionDate = max( TransactionDate )
FROM @.MyTable
GROUP BY MembershipID
ORDER BY MembershipID

MembershipID TransactionDate
3 2007-01-03 00:00:00.000
8 2006-08-05 00:00:00.000
187 2007-01-05 00:00:00.000
195 2006-03-14 00:00:00.000
239 2005-08-29 00:00:00.000
275 2005-08-07 00:00:00.000
303 2006-12-19 00:00:00.000
306 2005-12-16 00:00:00.000

|||

But your query doesn't give the last row, it gives the max tran date. He wants the row with the max tran date:

Code Snippet

DECLARE @.MyTable table
( MembershipID int,
Date datetime,
someOtherColumn int default (100),
yetAnotherColumn int default (100)
)

SET NOCOUNT ON

INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 3, '03-Jan-2007' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 8, '05-Aug-2006' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 8, '18-Sep-2005' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 8, '18-Sep-2005' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 187, '16-May-2006' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 187, '14-May-2006' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 187, '06-Jun-2006' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 187, '29-Jul-2005' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 187, '05-Jan-2007' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 195, '14-Mar-2006' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 239, '29-Aug-2005' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 275, '07-Aug-2005' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 303, '28-Dec-2005' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 303, '19-Dec-2006' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 306, '03-Oct-2005' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 306, '16-Dec-2005' )

select membershipId, date, someOtherColumn, yetAnotherColumn
from (select membershipId, date, someOtherColumn, yetAnotherColumn,
row_number() over (partition by membershipId order by date desc) as rowNum
from @.MyTable) as myTable
where rowNum = 1

|||

You are right Louis -but in defense, I was working with the data he provided, assuming that we were working with the 'first step'. (It's so much easier when folks provide us with an adequate explanition and sample data...)

The query that I provided should be used as a derived table and then would provide the requested data -and it works in both SQL 2000 and SQL 2005.

(However, there is one significant issue -how to deal with duplicate MembershipID/Date records -and of course, your proposed solution has that same problem).

Code Snippet

DECLARE @.MyTable table
( MembershipID int,
[Date] datetime,
MyOtherColumn int
)

SET NOCOUNT ON

INSERT INTO @.MyTable VALUES ( 3, '03-Jan-2007', 1 )
INSERT INTO @.MyTable VALUES ( 8, '05-Aug-2006', 2 )
INSERT INTO @.MyTable VALUES ( 8, '18-Sep-2005', 3 )
INSERT INTO @.MyTable VALUES ( 8, '18-Sep-2005', 4 )
INSERT INTO @.MyTable VALUES ( 187, '16-May-2006', 5 )
INSERT INTO @.MyTable VALUES ( 187, '14-May-2006', 6 )
INSERT INTO @.MyTable VALUES ( 187, '06-Jun-2006', 7 )
INSERT INTO @.MyTable VALUES ( 187, '29-Jul-2005', 8 )
INSERT INTO @.MyTable VALUES ( 187, '05-Jan-2007', 9 )
INSERT INTO @.MyTable VALUES ( 195, '14-Mar-2006', 10 )
INSERT INTO @.MyTable VALUES ( 239, '29-Aug-2005', 11 )
INSERT INTO @.MyTable VALUES ( 275, '07-Aug-2005', 12 )
INSERT INTO @.MyTable VALUES ( 303, '28-Dec-2005', 13 )
INSERT INTO @.MyTable VALUES ( 303, '19-Dec-2006', 14 )
INSERT INTO @.MyTable VALUES ( 306, '03-Oct-2005', 15 )
INSERT INTO @.MyTable VALUES ( 306, '16-Dec-2005', 17 )

SELECT
t.MembershipID,
t.[Date],
t.MyOtherColumn
FROM @.MyTable t
JOIN ( SELECT
MembershipID,
[Date] = max( [Date] )
FROM @.MyTable
GROUP BY MembershipID
) dt
ON ( t.MembershipID = dt.MembershipID
AND t.[Date] = dt.[Date]
)
ORDER BY t.MembershipID

MembershipID Date MyOtherColumn
-
3 2007-01-03 00:00:00.000 1
8 2006-08-05 00:00:00.000 2
187 2007-01-05 00:00:00.000 9
195 2006-03-14 00:00:00.000 10
239 2005-08-29 00:00:00.000 11
275 2005-08-07 00:00:00.000 12
303 2006-12-19 00:00:00.000 14
306 2005-12-16 00:00:00.000 17

|||i'm so sorry, i should give an example of the result i need in the begining.
and many thanks to Arnie and all programmers who tried to help me.
i really appreciate it.

How to get distinct columns using COALESCE

Hi guys, can you please help me to solve this problem. I have to get distinct row from offering column of xyz table.

I have to get offering1, offering2 from xyz table. But I am getting only offering1. I should not get duplicate rows from XYZ table.

SELECTDISTINCT @.Staging_Off=COALESCE(@.Staging_Off+',','')+ Offering

FROM xyz

WHERE xyz.OfferingNOTIN

(SELECTDISTINCT Offering.Offering

FROM OfferingJoin xyz

ON Offering.Offering= xyz.Offering

AND Offering.SourceSystem= @.SourceSystem

)

That's probably because your nested query (SELECT DISTINCT) is not working on the same row as your outer query. You must make sure your inner query join with a value of the outer query, otherwise they won't be related. Something like this:

SELECT DISTINCT @.Staging_Off=COALESCE(@.Staging_Off +',','')+ OfferingFROM xyzAS X1WHERE X1.OfferingNOT IN(SELECT DISTINCT O.OfferingFROM OfferingAS O, xyzAS X2WHERE O.Offering = X2.OfferingAND X2.SomeId = X1.SomeIdAND O.SourceSystem= @.SourceSystem)

Here I assume that there are some kind of unique id in xyz table that can be used

I think this query could be rewritten in a more clean manner, but I can't do it from the tip of my head. Would need source data and do some trial and erroring ;-) Good luck!