How to get the time since a sql server is running

Do you know any easy way for getting the time that the SQL Server was
running?
Thanks
JavierHi Javier
Look at the creation date for the tempdb database.
In SQL 2005:
select create_date from sys.databases
where name = 'tempdb'
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Javier" <ji_villegas@.hotmail.com> wrote in message
news:ACBD90A1-D7B3-4D81-868F-EB2528A10741@.microsoft.com...
> Do you know any easy way for getting the time that the SQL Server was
> running?
> Thanks
> Javier|||You can look at your SQL Server logs as they are created when SQL Server
restarts (although it may be a spill-over of the previous log which may be
too big to fit into one file). Just check the very first entry in the
latest log
"Javier" <ji_villegas@.hotmail.com> wrote in message
news:ACBD90A1-D7B3-4D81-868F-EB2528A10741@.microsoft.com...
> Do you know any easy way for getting the time that the SQL Server was
> running?
> Thanks
> Javiersql

How to get the SQLEXPRESS GUID ?

Hi, please can someone be so kind and tell me the fastet way to get the GUID of the running SQLExpress instance ?

Sincerely,gonzo883.

hi,

what do you mean by "the GUID of the running SQLExpress instance"?

regards

|||

I mean the GUID of my SQLExpress server, I need the GUID because I want to use SSL encryption and to register SSL via httpcfg so I need the GUID.

Sincerely,gonzo883.

|||

hi,

GUID string identifying the entity registering the certificate. As a best practice, create one GUID for each instance of SQL Server 2005 and use that same GUID for all certificate registrations made by that instance.

so just "create" your own guid (as no one is directly present nor available from the instance) and save that value for your references..

regards

|||

Thank you for your reply, but I don't know how to create a own guid for my instance od SQLExpress. Can you please explain this.

Sincerely,gonzo883.

|||

OK, I've registert the certificate, but I have some more problems to get the whole think working :

I've created a certificate with selfssl for my server (cn=www.example.de) and have registered it via '/T' for the default web site.

After that I've controled via httpcfg query ssl if the certificate is really registered to the server.

Now I try to set the SQLExpress server to use the encryption in the configuration manager, but the certificate was not shown in the dialogue field. I check 'force encryption' and try to reach the server from a client.

With the web browser if can connect via https after accepting the certificate, I also add the certificate to the 'trusted' ones on the client.

When I try to connect to the server using the SQL management studio with the option 'encrypt' I get an error message that server could be reached but the certificate was a trusted one.

What I have to do on the client that the certificate would be seen as a trusted one.

I also have exported the certificate from the server as a *.pfx file and have imported it to the client, but I still get the error message .

Sorry for my bad english,gonzo883.

How to get the SQLEXPRESS GUID ?

Hi, please can someone be so kind and tell me the fastet way to get the GUID of the running SQLExpress instance ?

Sincerely,gonzo883.

hi,

what do you mean by "the GUID of the running SQLExpress instance"?

regards

|||

I mean the GUID of my SQLExpress server, I need the GUID because I want to use SSL encryption and to register SSL via httpcfg so I need the GUID.

Sincerely,gonzo883.

|||

hi,

GUID string identifying the entity registering the certificate. As a best practice, create one GUID for each instance of SQL Server 2005 and use that same GUID for all certificate registrations made by that instance.

so just "create" your own guid (as no one is directly present nor available from the instance) and save that value for your references..

regards

|||

Thank you for your reply, but I don't know how to create a own guid for my instance od SQLExpress. Can you please explain this.

Sincerely,gonzo883.

|||

OK, I've registert the certificate, but I have some more problems to get the whole think working :

I've created a certificate with selfssl for my server (cn=www.example.de) and have registered it via '/T' for the default web site.

After that I've controled via httpcfg query ssl if the certificate is really registered to the server.

Now I try to set the SQLExpress server to use the encryption in the configuration manager, but the certificate was not shown in the dialogue field. I check 'force encryption' and try to reach the server from a client.

With the web browser if can connect via https after accepting the certificate, I also add the certificate to the 'trusted' ones on the client.

When I try to connect to the server using the SQL management studio with the option 'encrypt' I get an error message that server could be reached but the certificate was a trusted one.

What I have to do on the client that the certificate would be seen as a trusted one.

I also have exported the certificate from the server as a *.pfx file and have imported it to the client, but I still get the error message .

Sorry for my bad english,gonzo883.

sql

How to get the REAL host name?

Hello:
I need to be able to determine the name of the client machine running the
session. The value in sysprocesses.hostname is client-supplied, so it may be
not the real name of the client machine. The client can explicitly specify
any host name in the ADO connection string. Is there a way to find out the
real host name? Perhaps from the network adapter address, which is also in
sysprocesses?
This problem is especially apparent for MS Access projects (adp's); they
always connect with the host name being the name of the developer's machine.
thanks,
VadimYou tocuhed on the answer... I'm sure there's a way to get it from the MAC
address. I just don't know TCP enough to tell you how...
but there's nothing else that could definitiely tell you. As you point
out... the client can put anything it wants int he string...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Vadim Rapp" <vr@.myrealbox.nospam.com> wrote in message
news:u5zReG7rDHA.1760@.TK2MSFTNGP10.phx.gbl...
> Hello:
> I need to be able to determine the name of the client machine running the
> session. The value in sysprocesses.hostname is client-supplied, so it may
be
> not the real name of the client machine. The client can explicitly specify
> any host name in the ADO connection string. Is there a way to find out the
> real host name? Perhaps from the network adapter address, which is also in
> sysprocesses?
> This problem is especially apparent for MS Access projects (adp's); they
> always connect with the host name being the name of the developer's
machine.
> thanks,
> Vadim
>|||Depending on the architecture of TDS, which I'm not sure of, the MAC address
may not be the client's host NIC anyway, usually MAC address is the address
of the client NIC or the nearest router NIC whichever is logically closer on
the network.
But, it you want to assume MAC=MAC of Client NIC, you could write a routine
to parse the output of ARP -a
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:uC#7f47rDHA.3552@.TK2MSFTNGP11.phx.gbl...
> You tocuhed on the answer... I'm sure there's a way to get it from the MAC
> address. I just don't know TCP enough to tell you how...
> but there's nothing else that could definitiely tell you. As you point
> out... the client can put anything it wants int he string...
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Vadim Rapp" <vr@.myrealbox.nospam.com> wrote in message
> news:u5zReG7rDHA.1760@.TK2MSFTNGP10.phx.gbl...
> > Hello:
> >
> > I need to be able to determine the name of the client machine running
the
> > session. The value in sysprocesses.hostname is client-supplied, so it
may
> be
> > not the real name of the client machine. The client can explicitly
specify
> > any host name in the ADO connection string. Is there a way to find out
the
> > real host name? Perhaps from the network adapter address, which is also
in
> > sysprocesses?
> >
> > This problem is especially apparent for MS Access projects (adp's); they
> > always connect with the host name being the name of the developer's
> machine.
> >
> > thanks,
> >
> > Vadim
> >
>

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 number of queries fired to the database by a single ssis package

Hi,

Is there any way that I can know the count of queries and queries fired to the database after running a ssis package?

can any one help me out?

Thanks in advance.

Programatically yes, otherwise I haven't any idea.|||SQL Profiler could help you.

How to get the list of sql servers

Hi,
Am on a LAN and there are several sql servers running. I wanted know the list of servers with the version. I know we can get the list of servers using cmd line prompt "OSQL -L", but i need to know the version of each server also. Is there someway to get this information for all the sql servers in the LAN
Thanks very muc
YogishYou could use SQLDMO for this.Check out
http://www32.brinkster.com/srisamp/sqlArticles/article_15.htm. You can
easily extend the procedure using more properties of SQLDMO to return the
information that you want.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"GYK" <anonymous@.discussions.microsoft.com> wrote in message
news:F03C14A9-47C6-4010-BD6B-1D7B4CB3A417@.microsoft.com...
> Hi,
> Am on a LAN and there are several sql servers running. I wanted know the
list of servers with the version. I know we can get the list of servers
using cmd line prompt "OSQL -L", but i need to know the version of each
server also. Is there someway to get this information for all the sql
servers in the LAN?
> Thanks very much
> Yogish

How to get the exact installed Version

OK, this is a simple question for you, but i am a newbie to SQL2000, and i need to find out which Service Pack we are currently running. Could anybody please give me a short reply :)
Thanks
Chrisselect @.@.version|||Originally posted by blindman
select @.@.version

great thank you. This helped a lot.

Chris

How to get started?

I am looking to learn about reporting with SQL Server for my company

Currently we have an applicaiton running on SQL Server 2000 and have SQL Server 2000 Reporting Services installed. However, I have VB Express and Web Development Express 2005.

How do I get started here? What versions "work" with what? Do I need to either upgrade the DB to SQL 2005 or find VB.net 2003, or can I use what I have to get started?

jibip,

you will need "Reporting Add-In for Microsoft Visual Web Developer 2005 Express" if you want SSRS with your Web Development express. However, you will still need a BIDS environment (VS studio) for SSRS 2000.

In short, you will be a whole lot better just upgrading to SQL 2005 and installing Reporting Service, VB.NET is not a requirement, but hey if you are just starting out I would suggest going to VS 2005 and be done with it.

Ham

How to get started

I am currently running SQL 2000 SP3 as the backend for one of my production
systems. I would like to start doing some kind of repication for quick
diaster recovery efforts. Where would I find instructions for doing this. I
have plenty of hardware available.
Hi
You generally can't implment replication without affecting the application
in some way due to the requiements to have certain types of fileds in place
for replication to work.
Why don't you look at log shipping if you need a DR type scenario?
It is not intrusive at all for the application.
Regards
Mike
"Ronald_L" wrote:

> I am currently running SQL 2000 SP3 as the backend for one of my production
> systems. I would like to start doing some kind of repication for quick
> diaster recovery efforts. Where would I find instructions for doing this. I
> have plenty of hardware available.
|||Thanks Mike, I think that would be best for me as well from what I have been
looking at. How easy is it to put the original primary, back to the primary
once the repair to that device has been completed.
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> You generally can't implment replication without affecting the application
> in some way due to the requiements to have certain types of fileds in place
> for replication to work.
> Why don't you look at log shipping if you need a DR type scenario?
> It is not intrusive at all for the application.
> Regards
> Mike
> "Ronald_L" wrote:
|||I respectfully disagree. Only merge and updateable subscribers require new
"fields"/columns to be added to the underlying tables.
Transactional replication requires a primary key to exist, but does not
require the addition of any columns.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:2870741C-B8B7-4976-9319-5BA193FE796C@.microsoft.com...[vbcol=seagreen]
> Hi
> You generally can't implment replication without affecting the application
> in some way due to the requiements to have certain types of fileds in
> place
> for replication to work.
> Why don't you look at log shipping if you need a DR type scenario?
> It is not intrusive at all for the application.
> Regards
> Mike
> "Ronald_L" wrote:

How to get SQL Server Service Pack information

Hi,
The below result i get from running xp_msver, but is this information
showing any SQL service pack information?
How can i get it?
1,ProductName,NULL,Microsoft SQL Server
2,ProductVersion,524288,8.00.760
3,Language,1033,English (United States)
4,Platform,NULL,NT INTEL X86
5,Comments,NULL,NT INTEL X86
6,CompanyName,NULL,Microsoft Corporation
7,FileDescription,NULL,SQL Server Windows NT
8,FileVersion,NULL,2000.080.0760.00
9,InternalName,NULL,SQLSERVR
10,LegalCopyright,NULL,© 1988-2003 Microsoft Corp. All rights reserved.
11,LegalTrademarks,NULL,Microsoft® is a registered trademark of Microsoft
Corporation. Windows(TM) is a trademark of Microsoft Corporation
12,OriginalFilename,NULL,SQLSERVR.EXE
13,PrivateBuild,NULL,NULL
14,SpecialBuild,49807360,NULL
15,WindowsVersion,143851525,5.0 (2195)
16,ProcessorCount,1,1
17,ProcessorActiveMask,1,00000001
18,ProcessorType,586,PROCESSOR_INTEL_PENTIUM
19,PhysicalMemory,511,511 (536264704)
20,Product ID,NULL,NULL
--
Kzeto NgHi,
From the output the service pack level for your SQL Server is SP3 (
2,ProductVersion,524288,8.00.760 )
Thanks
Hari
MCDBA
"Kzeto ng" <kzetong@.yahoo.com> wrote in message
news:ev9prZIuDHA.3220@.tk2msftngp13.phx.gbl...
> Hi,
> The below result i get from running xp_msver, but is this information
> showing any SQL service pack information?
> How can i get it?
>
> 1,ProductName,NULL,Microsoft SQL Server
> 2,ProductVersion,524288,8.00.760
> 3,Language,1033,English (United States)
> 4,Platform,NULL,NT INTEL X86
> 5,Comments,NULL,NT INTEL X86
> 6,CompanyName,NULL,Microsoft Corporation
> 7,FileDescription,NULL,SQL Server Windows NT
> 8,FileVersion,NULL,2000.080.0760.00
> 9,InternalName,NULL,SQLSERVR
> 10,LegalCopyright,NULL,© 1988-2003 Microsoft Corp. All rights reserved.
> 11,LegalTrademarks,NULL,Microsoft® is a registered trademark of Microsoft
> Corporation. Windows(TM) is a trademark of Microsoft Corporation
> 12,OriginalFilename,NULL,SQLSERVR.EXE
> 13,PrivateBuild,NULL,NULL
> 14,SpecialBuild,49807360,NULL
> 15,WindowsVersion,143851525,5.0 (2195)
> 16,ProcessorCount,1,1
> 17,ProcessorActiveMask,1,00000001
> 18,ProcessorType,586,PROCESSOR_INTEL_PENTIUM
> 19,PhysicalMemory,511,511 (536264704)
> 20,Product ID,NULL,NULL
>
>
> --
> Kzeto Ng
>|||http://www.aspfaq.com/2160
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Kzeto ng" <kzetong@.yahoo.com> wrote in message
news:ev9prZIuDHA.3220@.tk2msftngp13.phx.gbl...
> Hi,
> The below result i get from running xp_msver, but is this information
> showing any SQL service pack information?
> How can i get it?
>
> 1,ProductName,NULL,Microsoft SQL Server
> 2,ProductVersion,524288,8.00.760
> 3,Language,1033,English (United States)
> 4,Platform,NULL,NT INTEL X86
> 5,Comments,NULL,NT INTEL X86
> 6,CompanyName,NULL,Microsoft Corporation
> 7,FileDescription,NULL,SQL Server Windows NT
> 8,FileVersion,NULL,2000.080.0760.00
> 9,InternalName,NULL,SQLSERVR
> 10,LegalCopyright,NULL,© 1988-2003 Microsoft Corp. All rights reserved.
> 11,LegalTrademarks,NULL,Microsoft® is a registered trademark of Microsoft
> Corporation. Windows(TM) is a trademark of Microsoft Corporation
> 12,OriginalFilename,NULL,SQLSERVR.EXE
> 13,PrivateBuild,NULL,NULL
> 14,SpecialBuild,49807360,NULL
> 15,WindowsVersion,143851525,5.0 (2195)
> 16,ProcessorCount,1,1
> 17,ProcessorActiveMask,1,00000001
> 18,ProcessorType,586,PROCESSOR_INTEL_PENTIUM
> 19,PhysicalMemory,511,511 (536264704)
> 20,Product ID,NULL,NULL
>
>
> --
> Kzeto Ng
>

How to get SQL Server Info(Version/SP Version/Server Statistics) from a network.

Does anyone know how to get SQL Server versions, Service pack versions
and the Server that instance of SQL is running on a network. I have
seen some threads on how to list SQL servers in a network but I need to
find more info about these SQL servers.

Any help appreciated..!
Thx...SELECT @.@.VERSION

--
Regards Bagieta
~~~~~~~~~~~~~~~~~~~~~~~~~~~
dbDeveloper - Multiple databases editor
http://www.prominentus.com
~~~~~~~~~~~~~~~~~~~~~~~~~~~

How to get running stored procs

Is there a script I can run that identifies the stored procedures that
are running NOW?
Regards
On Jun 7, 4:24 am, Frank Rizzo <n...@.none.com> wrote:
> Is there a script I can run that identifies the stored procedures that
> are running NOW?
> Regards
sql profiler show you the current proceses.
|||Hello Frank,
You may need to use the SQL Profiler to track the running stored procedure.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||On Jun 7, 3:24 am, Frank Rizzo <n...@.none.com> wrote:
> Is there a script I can run that identifies the stored procedures that
> are running NOW?
> Regards
Check Erland's website
http://www.sommarskog.se/sqlutil/aba_lockinfo.html
you can use this
|||Execute the profiler and in event selection select "Stored procedures and
select the check box "RPC Completed". After specific time save the contents
into a table and filter it out.
Thanks
Hari
"Frank Rizzo" <none@.none.com> wrote in message
news:OpVP9lIqHHA.3888@.TK2MSFTNGP05.phx.gbl...
> Is there a script I can run that identifies the stored procedures that are
> running NOW?
> Regards
|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

How to get running stored procs

Is there a script I can run that identifies the stored procedures that
are running NOW?
RegardsOn Jun 7, 4:24 am, Frank Rizzo <n...@.none.com> wrote:
> Is there a script I can run that identifies the stored procedures that
> are running NOW?
> Regards
sql profiler show you the current proceses.|||Hello Frank,
You may need to use the SQL Profiler to track the running stored procedure.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||On Jun 7, 3:24 am, Frank Rizzo <n...@.none.com> wrote:
> Is there a script I can run that identifies the stored procedures that
> are running NOW?
> Regards
Check Erland's website
http://www.sommarskog.se/sqlutil/aba_lockinfo.html
you can use this|||Execute the profiler and in event selection select "Stored procedures and
select the check box "RPC Completed". After specific time save the contents
into a table and filter it out.
Thanks
Hari
"Frank Rizzo" <none@.none.com> wrote in message
news:OpVP9lIqHHA.3888@.TK2MSFTNGP05.phx.gbl...
> Is there a script I can run that identifies the stored procedures that are
> running NOW?
> Regards|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

How to get running stored procs

Is there a script I can run that identifies the stored procedures that
are running NOW?
RegardsOn Jun 7, 4:24 am, Frank Rizzo <n...@.none.com> wrote:
> Is there a script I can run that identifies the stored procedures that
> are running NOW?
> Regards
sql profiler show you the current proceses.|||Hello Frank,
You may need to use the SQL Profiler to track the running stored procedure.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||On Jun 7, 3:24 am, Frank Rizzo <n...@.none.com> wrote:
> Is there a script I can run that identifies the stored procedures that
> are running NOW?
> Regards
Check Erland's website
http://www.sommarskog.se/sqlutil/aba_lockinfo.html
you can use this|||Execute the profiler and in event selection select "Stored procedures and
select the check box "RPC Completed". After specific time save the contents
into a table and filter it out.
Thanks
Hari
"Frank Rizzo" <none@.none.com> wrote in message
news:OpVP9lIqHHA.3888@.TK2MSFTNGP05.phx.gbl...
> Is there a script I can run that identifies the stored procedures that are
> running NOW?
> Regards|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.

How to get running difference with SQL?

Hi, Experts:
I have a question about getting running difference. The difference is
compared with previous week, if no data for previous week, the difference is
null. Any suggestions? Thanks much!
id week weight diff
1 1 100 0
1 2 102 -2
1 3 99 3
2 1 103 0
2 3 102 nullI think this will do what you describe. The one difference is that
the value for week 1 follows the rule specified "if no data for
previous week, the difference is null", while the data shown does not
reflect that. A CASE expression could be used to enforce that week 1
would get zero, but I chose not to complicate the query that much.
SELECT d, week, weight,
(SELECT A.weight - B.weight
FROM Whatever as B
WHERE A.d = B.d
AND A.week = B.week + 1)
FROM Whatever as A
Roy Harvey
Beacon Falls, CT
On Mon, 18 Jun 2007 08:20:00 -0700, luvgreen
<luvgreen@.discussions.microsoft.com> wrote:

>Hi, Experts:
>I have a question about getting running difference. The difference is
>compared with previous week, if no data for previous week, the difference i
s
>null. Any suggestions? Thanks much!
>id week weight diff
>1 1 100 0
>1 2 102 -2
>1 3 99 3
>2 1 103 0
>2 3 102 null

How to get running difference with SQL?

Hi, Experts:
I have a question about getting running difference. The difference is
compared with previous week, if no data for previous week, the difference is
null. Any suggestions? Thanks much!
id week weight diff
1 1 100 0
1 2 102 -2
1 3 99 3
2 1 103 0
2 3 102 nullI think this will do what you describe. The one difference is that
the value for week 1 follows the rule specified "if no data for
previous week, the difference is null", while the data shown does not
reflect that. A CASE expression could be used to enforce that week 1
would get zero, but I chose not to complicate the query that much.
SELECT d, week, weight,
(SELECT A.weight - B.weight
FROM Whatever as B
WHERE A.d = B.d
AND A.week = B.week + 1)
FROM Whatever as A
Roy Harvey
Beacon Falls, CT
On Mon, 18 Jun 2007 08:20:00 -0700, luvgreen
<luvgreen@.discussions.microsoft.com> wrote:
>Hi, Experts:
>I have a question about getting running difference. The difference is
>compared with previous week, if no data for previous week, the difference is
>null. Any suggestions? Thanks much!
>id week weight diff
>1 1 100 0
>1 2 102 -2
>1 3 99 3
>2 1 103 0
>2 3 102 null|||Thank you so very much. It is really helpful!!
"Roy Harvey" wrote:
> I think this will do what you describe. The one difference is that
> the value for week 1 follows the rule specified "if no data for
> previous week, the difference is null", while the data shown does not
> reflect that. A CASE expression could be used to enforce that week 1
> would get zero, but I chose not to complicate the query that much.
> SELECT d, week, weight,
> (SELECT A.weight - B.weight
> FROM Whatever as B
> WHERE A.d = B.d
> AND A.week = B.week + 1)
> FROM Whatever as A
> Roy Harvey
> Beacon Falls, CT
> On Mon, 18 Jun 2007 08:20:00 -0700, luvgreen
> <luvgreen@.discussions.microsoft.com> wrote:
> >Hi, Experts:
> >
> >I have a question about getting running difference. The difference is
> >compared with previous week, if no data for previous week, the difference is
> >null. Any suggestions? Thanks much!
> >
> >id week weight diff
> >1 1 100 0
> >1 2 102 -2
> >1 3 99 3
> >2 1 103 0
> >2 3 102 null
>

How to get running difference with SQL?

Hi, Experts:
I have a question about getting running difference. The difference is
compared with previous week, if no data for previous week, the difference is
null. Any suggestions? Thanks much!
id week weight diff
1 1 100 0
1 2 102 -2
1 3 99 3
2 1 103 0
2 3 102 null
I think this will do what you describe. The one difference is that
the value for week 1 follows the rule specified "if no data for
previous week, the difference is null", while the data shown does not
reflect that. A CASE expression could be used to enforce that week 1
would get zero, but I chose not to complicate the query that much.
SELECT d, week, weight,
(SELECT A.weight - B.weight
FROM Whatever as B
WHERE A.d = B.d
AND A.week = B.week + 1)
FROM Whatever as A
Roy Harvey
Beacon Falls, CT
On Mon, 18 Jun 2007 08:20:00 -0700, luvgreen
<luvgreen@.discussions.microsoft.com> wrote:

>Hi, Experts:
>I have a question about getting running difference. The difference is
>compared with previous week, if no data for previous week, the difference is
>null. Any suggestions? Thanks much!
>id week weight diff
>1 1 100 0
>1 2 102 -2
>1 3 99 3
>2 1 103 0
>2 3 102 null
|||Thank you so very much. It is really helpful!!
"Roy Harvey" wrote:

> I think this will do what you describe. The one difference is that
> the value for week 1 follows the rule specified "if no data for
> previous week, the difference is null", while the data shown does not
> reflect that. A CASE expression could be used to enforce that week 1
> would get zero, but I chose not to complicate the query that much.
> SELECT d, week, weight,
> (SELECT A.weight - B.weight
> FROM Whatever as B
> WHERE A.d = B.d
> AND A.week = B.week + 1)
> FROM Whatever as A
> Roy Harvey
> Beacon Falls, CT
> On Mon, 18 Jun 2007 08:20:00 -0700, luvgreen
> <luvgreen@.discussions.microsoft.com> wrote:
>

How to get rid of a corrupt record

I have a corrupt record in my database that I can't query on. Nor can I
delete or update. Running any of the Select, Update or Delete statements in
QA using the WHERE clause with ID = <the corrupt record ID> causes the
system to hang until it eventually times out.
How can I get rid of this record ?Are you getting any errors? Did you try running DBCC CHECKTABLE on that
table?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"RB" <rakhi.bhatia@.verizon.net> wrote in message
news:%23O8P%23jWnDHA.2512@.TK2MSFTNGP09.phx.gbl...
I have a corrupt record in my database that I can't query on. Nor can I
delete or update. Running any of the Select, Update or Delete statements in
QA using the WHERE clause with ID = <the corrupt record ID> causes the
system to hang until it eventually times out.
How can I get rid of this record ?|||Rakhi
You could try running DBCC Checktable. You can run this
with parameters that can try to repair errors. See BOL for
details.
Hope this helps
John|||Have you tried:
DBCC CHECKTABLE ('tablename', REPAIR_ALLOW_DATA_LOSS)
...Most likely the problem is NOT a corrupt record, but rather a hanging
connection that has the record locked.
Run your query and check the "management" section to see if your connection
is blocked.
Bruce
"RB" <rakhi.bhatia@.verizon.net> wrote in message
news:%23O8P%23jWnDHA.2512@.TK2MSFTNGP09.phx.gbl...
> I have a corrupt record in my database that I can't query on. Nor can I
> delete or update. Running any of the Select, Update or Delete statements
in
> QA using the WHERE clause with ID = <the corrupt record ID> causes the
> system to hang until it eventually times out.
> How can I get rid of this record ?
>|||Hi,
Try running dbcc checktable on that table with Repair options after taking a
database backup or table backup (Select * into bckuptable from table). If it
fails again u can try the below steps:
1. Create a new table same as the corrupted table
2. Write a script to copy the data in batches excluding the corrupted data.
3. Rename the corrupted table to table_corrupt
4. Rename the new table to original name
5. Create all indexes for this new table
Thanks
Hari
MCDBA
"RB" <rakhi.bhatia@.verizon.net> wrote in message
news:#O8P#jWnDHA.2512@.TK2MSFTNGP09.phx.gbl...
> I have a corrupt record in my database that I can't query on. Nor can I
> delete or update. Running any of the Select, Update or Delete statements
in
> QA using the WHERE clause with ID = <the corrupt record ID> causes the
> system to hang until it eventually times out.
> How can I get rid of this record ?
>|||Thanks everyone. Will try out all these steps and report the results.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eq#M#3WnDHA.644@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Try running dbcc checktable on that table with Repair options after taking
a
> database backup or table backup (Select * into bckuptable from table). If
it
> fails again u can try the below steps:
> 1. Create a new table same as the corrupted table
> 2. Write a script to copy the data in batches excluding the corrupted
data.
> 3. Rename the corrupted table to table_corrupt
> 4. Rename the new table to original name
> 5. Create all indexes for this new table
> Thanks
> Hari
> MCDBA
>
> "RB" <rakhi.bhatia@.verizon.net> wrote in message
> news:#O8P#jWnDHA.2512@.TK2MSFTNGP09.phx.gbl...
> > I have a corrupt record in my database that I can't query on. Nor can I
> > delete or update. Running any of the Select, Update or Delete statements
> in
> > QA using the WHERE clause with ID = <the corrupt record ID> causes the
> > system to hang until it eventually times out.
> >
> > How can I get rid of this record ?
> >
> >
>|||To add to the other posts:
If this indeed is a corrupted row, you want to find out why it became corrupted in the first place.
If you have a hw problem, you want to fix that!
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"RB" <rakhi.bhatia@.verizon.net> wrote in message news:%23O8P%23jWnDHA.2512@.TK2MSFTNGP09.phx.gbl...
> I have a corrupt record in my database that I can't query on. Nor can I
> delete or update. Running any of the Select, Update or Delete statements in
> QA using the WHERE clause with ID = <the corrupt record ID> causes the
> system to hang until it eventually times out.
> How can I get rid of this record ?
>

How to get Physical Drives associated with SQL Instance

I need to get a list of physical drives (ie: drive
letters) of all the drives associated with an instance of
SQL 2000 running on a cluster.
The ideal solution would be a stored procedure that given
the instance name you would get back the drives.
Can anyone make a useful suggestion? Is this possible with
a stored procedure?
Thanks,
kenKen,
You can use master..xp_fixeddrives to get the fixed drives name as well as
free space in MB.Since its a undocumented call, please refrain from using it
in production code.An alternative would be to use master..xp_cmdshell
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Ken McClain" <ken@.softbreeze.net> wrote in message
news:0b8e01c35062$4e3466e0$a501280a@.phx.gbl...
> I need to get a list of physical drives (ie: drive
> letters) of all the drives associated with an instance of
> SQL 2000 running on a cluster.
> The ideal solution would be a stored procedure that given
> the instance name you would get back the drives.
> Can anyone make a useful suggestion? Is this possible with
> a stored procedure?
> Thanks,
> ken|||In addition to Dinesh's suggestion which I can't recall if it works on a
cluster there is also the system function fn_servershareddrives() that will
give you the shared cluster drives, possibly combining them will give you
the answer, I haven't got a cluster to test on at the moment. See BOL for
details of this function
SELECT *
FROM ::fn_servershareddrives()
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Ken McClain" <ken@.softbreeze.net> wrote in message
news:0b8e01c35062$4e3466e0$a501280a@.phx.gbl...
I need to get a list of physical drives (ie: drive
letters) of all the drives associated with an instance of
SQL 2000 running on a cluster.
The ideal solution would be a stored procedure that given
the instance name you would get back the drives.
Can anyone make a useful suggestion? Is this possible with
a stored procedure?
Thanks,
ken