How to differentiate SQL 2000 and MSDE installs

Hello!
I am trying to determine how to differentiate a SQL 2000 and MSDE install
without running a query on the database.
In mmc's post and Andrea's answer on 1/18/05, a clear solution using a query
is noted.
I would like to be able to read a registy value to make the determination if
possible.
Thanks very much for the assistance.
Carl
Hi
Gave you installed SQL Server on one machine, MSDE on another one and looked
at the differences?
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Carl Langren" <test@.test> wrote in message
news:eNlceBWBFHA.3588@.TK2MSFTNGP11.phx.gbl...
> Hello!
> I am trying to determine how to differentiate a SQL 2000 and MSDE install
> without running a query on the database.
> In mmc's post and Andrea's answer on 1/18/05, a clear solution using a
query
> is noted.
> I would like to be able to read a registy value to make the determination
if
> possible.
> Thanks very much for the assistance.
> Carl
>
|||Hi Mike,
Yes, three servers and six workstations. Mixed MSDE, Developer and Full SQL.
I was really looking more for an answer rather than a question......
I found this post:
http://www.masterado.net/home/ng/for...t14419113.aspx
=================================================
11/16/2004 4:27:03 PM SQL Server 2000 registry values by edition?
Is it possible to determine through the registry which edition of SQL Server
2000 has been installed - Enterprise, Standard, Personal, Developer,
Evalulation, Windows CE, Desktop (MSDE)?
I know it's possible using 'SELECT SERVERPROPERTY('edition')' in
QueryAnalyzer but what about the registry?
Thanks!
==================================================
11/28/2004 4:20:00 AM RE: SQL Server 2000 registry values by edition?
No, but you can use registry key below to differentiate between MSDE and
other sql editions:
For a named instance:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<Instance
Name>\Setup
For a default instance:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Setup
If one of these keys exist but there is no ProductCode, the instance is one
of the SQL Server 2000 Editions (such as Enterprise, Enterprise Evaluation,
Standard, Developer, or Personal).
If a ProductCode entry is present, the instance is MSDE.
You can also parse the sql errorlog to determine what edition, see KB: INF:
How to Determine the Download Files You Need for Upgrading to SQL Server
2000 Service Pack 3 - http://support.microsoft.com/?id=814618
Fany Vargas
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their
Microsoft software to better protect against viruses and security
vulnerabilities. The easiest way to do this is to visit the following
websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||hi Carl,
Carl Langren wrote:
> Hello!
> I am trying to determine how to differentiate a SQL 2000 and MSDE
> install without running a query on the database.
> In mmc's post and Andrea's answer on 1/18/05, a clear solution using
> a query is noted.
> I would like to be able to read a registy value to make the
> determination if possible.
> Thanks very much for the assistance.
> Carl
AFAIK, apart from ProductCode key in the Setup hive you already mentioned,
unfortunately there are no registry difference among SQL Server editions,
and MSDE is one of them, and only directly querying the engine determins a
real answer to this question...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

How to diagnose memory bloating?

Good morning/afternoon,
We have two instances of sql-server 2k running. They start out fine
running about 50-100 meg of storage, then gradually increase to over 750mg
each, then one of the other stops responding and needs a restart.
Though I'm an old hand at database systems, I'm new to SQL-server and would
like to find a way to resolve this. I've run a couple of traces, but not
know what parameters to set, haven't really found a problem. (Though I did
add a couple of indexes which sped the system up.)
Where do we go from here?eg1,
By storage, do you mean memoy as in your subject line? Check the SQL Server
logand Windows event log to see if there are any messages that might offer a
hint. Check to see if your paging file is filling up (use Performance
Monitor or Task Manager).
The two instances might be fighting each other for memory resources. If all
else fails, try limiting the memory for each instance to 700 MB (or maybe a
little less). SQL Server is an aggressive user of system memory.
-- Bill
"eg1" <Bor@.kurzikstan.com> wrote in message
news:Xns98BA9DAEAED0BBorkurzikstancom@.20
7.46.248.16...
> Good morning/afternoon,
> We have two instances of sql-server 2k running. They start out fine
> running about 50-100 meg of storage, then gradually increase to over 750mg
> each, then one of the other stops responding and needs a restart.
> Though I'm an old hand at database systems, I'm new to SQL-server and
> would
> like to find a way to resolve this. I've run a couple of traces, but not
> know what parameters to set, haven't really found a problem. (Though I
> did
> add a couple of indexes which sped the system up.)
> Where do we go from here?

How to diagnose memory bloating?

Good morning/afternoon,
We have two instances of sql-server 2k running. They start out fine
running about 50-100 meg of storage, then gradually increase to over 750mg
each, then one of the other stops responding and needs a restart.
Though I'm an old hand at database systems, I'm new to SQL-server and would
like to find a way to resolve this. I've run a couple of traces, but not
know what parameters to set, haven't really found a problem. (Though I did
add a couple of indexes which sped the system up.)
Where do we go from here?eg1,
By storage, do you mean memoy as in your subject line? Check the SQL Server
logand Windows event log to see if there are any messages that might offer a
hint. Check to see if your paging file is filling up (use Performance
Monitor or Task Manager).
The two instances might be fighting each other for memory resources. If all
else fails, try limiting the memory for each instance to 700 MB (or maybe a
little less). SQL Server is an aggressive user of system memory.
-- Bill
"eg1" <Bor@.kurzikstan.com> wrote in message
news:Xns98BA9DAEAED0BBorkurzikstancom@.207.46.248.16...
> Good morning/afternoon,
> We have two instances of sql-server 2k running. They start out fine
> running about 50-100 meg of storage, then gradually increase to over 750mg
> each, then one of the other stops responding and needs a restart.
> Though I'm an old hand at database systems, I'm new to SQL-server and
> would
> like to find a way to resolve this. I've run a couple of traces, but not
> know what parameters to set, haven't really found a problem. (Though I
> did
> add a couple of indexes which sped the system up.)
> Where do we go from here?

How to diagnose memory bloating?

Good morning/afternoon,
We have two instances of sql-server 2k running. They start out fine
running about 50-100 meg of storage, then gradually increase to over 750mg
each, then one of the other stops responding and needs a restart.
Though I'm an old hand at database systems, I'm new to SQL-server and would
like to find a way to resolve this. I've run a couple of traces, but not
know what parameters to set, haven't really found a problem. (Though I did
add a couple of indexes which sped the system up.)
Where do we go from here?
eg1,
By storage, do you mean memoy as in your subject line? Check the SQL Server
logand Windows event log to see if there are any messages that might offer a
hint. Check to see if your paging file is filling up (use Performance
Monitor or Task Manager).
The two instances might be fighting each other for memory resources. If all
else fails, try limiting the memory for each instance to 700 MB (or maybe a
little less). SQL Server is an aggressive user of system memory.
-- Bill
"eg1" <Bor@.kurzikstan.com> wrote in message
news:Xns98BA9DAEAED0BBorkurzikstancom@.207.46.248.1 6...
> Good morning/afternoon,
> We have two instances of sql-server 2k running. They start out fine
> running about 50-100 meg of storage, then gradually increase to over 750mg
> each, then one of the other stops responding and needs a restart.
> Though I'm an old hand at database systems, I'm new to SQL-server and
> would
> like to find a way to resolve this. I've run a couple of traces, but not
> know what parameters to set, haven't really found a problem. (Though I
> did
> add a couple of indexes which sped the system up.)
> Where do we go from here?
sql

How to diagnose a deadly embrace

I have an application which is running on 40 active workstations. I am
encountering lock outs many times a day. When I run the stored procedure
"sp_who2" I can see the spids that are blocked and by which other spid.
If I "kill xx" where xx is the head of the blocking chain the system frees
up. When doing the "sp_who2" I see a command column which only shows the
start of a command like "insert", 'update", "select" or "AWAITING COMMAND".
Is there a way to see the whole command to isolate which table(s) are
causing the problem?
Are there other tools to help me out?
Desparately in need of help...
Mark
Mark Butler wrote:
> I have an application which is running on 40 active workstations. I
> am encountering lock outs many times a day. When I run the stored
> procedure "sp_who2" I can see the spids that are blocked and by which
> other spid.
> If I "kill xx" where xx is the head of the blocking chain the system
> frees up. When doing the "sp_who2" I see a command column which only
> shows the start of a command like "insert", 'update", "select" or
> "AWAITING COMMAND". Is there a way to see the whole command to
> isolate which table(s) are causing the problem?
> Are there other tools to help me out?
> Desparately in need of help...
> Mark
You can use Profiler to see what command are starting and not completing
or committing as the case may be. If you look at the starting and
completed events, you can see if something did not complete (missing
completed event). That might mean that data was not rolled back. It
sounds like you might have open transactions. Make sure no one is using
SQL Enterprise Manager (or other tools that do not fetch all data at
once) to edit data in tables as they will leave open locks on data.
If the same user is responsibl, you can filter the Profiler data. The
output may generate a lot of information. I would start looking at
SQL:BatchStarting/Completed and RPC:Starting/Completed. If yo uneed more
detail, you can add SQL:StmtStarting/Completed and
SP:StmtStarting/Completed.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||You could also use DBCC INPUTBUFFER or fn_get_sql on specific spids to find
out what they are doing at a given point in time. See SQL Server Books
Online for more information. You may find my code useful in this scenario:
http://vyaskn.tripod.com/fn_get_sql.htm
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mark Butler" <mredhat_nospam@.yahoo.com> wrote in message
news:eYpyqXLfFHA.1284@.TK2MSFTNGP14.phx.gbl...
> I have an application which is running on 40 active workstations. I am
> encountering lock outs many times a day. When I run the stored procedure
> "sp_who2" I can see the spids that are blocked and by which other spid.
> If I "kill xx" where xx is the head of the blocking chain the system frees
> up. When doing the "sp_who2" I see a command column which only shows the
> start of a command like "insert", 'update", "select" or "AWAITING
COMMAND".
> Is there a way to see the whole command to isolate which table(s) are
> causing the problem?
> Are there other tools to help me out?
> Desparately in need of help...
> Mark
>
|||You can start SQL Server Profiler to trace which table is causing the lock.
John King
http://www.agileinfollc.com
"Mark Butler" <mredhat_nospam@.yahoo.com> wrote in message
news:eYpyqXLfFHA.1284@.TK2MSFTNGP14.phx.gbl...
>I have an application which is running on 40 active workstations. I am
>encountering lock outs many times a day. When I run the stored procedure
>"sp_who2" I can see the spids that are blocked and by which other spid.
> If I "kill xx" where xx is the head of the blocking chain the system frees
> up. When doing the "sp_who2" I see a command column which only shows the
> start of a command like "insert", 'update", "select" or "AWAITING
> COMMAND". Is there a way to see the whole command to isolate which
> table(s) are causing the problem?
> Are there other tools to help me out?
> Desparately in need of help...
> Mark
>

How to determine which Service Pack level your SQL Server is running and column name spaci

I have visual studio 2005 with XP home. I'm trying to find out which service pack my SQL server is running as I've been told that Service pack 2 has a few bugs.For example ADO Applications Report a Syntax Error When Column Names Contain Spaces. This appears to be an error i am recieving. I just wanted to confirm that it is a microsoft bug rather then something Im doing wrong.Microsoft meantions the bug here:http://support.microsoft.com/kb/264687I've been looking everywhere. I do not have the "query analyzer window" where you can find this out as I have VS 2005. How would I find this out.Thank you

Query Analyzer is an application that came with SQL 2000.

You can get the same results in SQL Management Studio, by right clicking on a table, choose 'Script Table As', then 'Select To' and lastly, 'New Query Window'

It gives you a select statement based on that table, but you can adjust the query any way you'd like, and then run it to see the results

|||

universaltruth:

I'm trying to find out which service pack my SQL server is running ....

Here is an example that will get you the answer, (Copy & Paste it in your SQL Server)... then Run... we are Done!

My Input:

1SELECTCONVERT(char(20), SERVERPROPERTY('productlevel'));2GO

My Output:

SP2

Done!

----------------

Now we are done for the Service Pack, here is a bonus "Tip"to get know more about the server.

Try this:

execxp_msver

Output:

1 ProductName NULL Microsoft SQL Server
2 ProductVersion 589824 9.00.3054.00
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 2005.090.3054.00
9 InternalName NULL SQLSERVR
10 LegalCopyright NULL ? 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 200146944 NULL
15 WindowsVersion 393216006 6.0 (6000)
16 ProcessorCount 2 2
17 ProcessorActiveMask 3 00000003
18 ProcessorType 586 PROCESSOR_INTEL_PENTIUM
19 PhysicalMemory 2046 2046 (2145202176)
20 Product ID NULL NULL

|||

CS4ever,

This issue of determining service pack level has been bugging me for ages - thanks for that reply. I've worked with early releases of SQL Server, and I had wondered why "select @.@.version" was no longer giving me the proper release info. Once again, thanks!

|||Thank you very much for your answers. Unfortunatley I Know very little about SQL.You said "Here is an example that will get you the answer, (Copy & Paste it in your SQL Server)... then Run... we are Done!"Im not sure where to copy that code. Do i just copy it into the regular aspx.vb page or somewhere else|||

Copy it into a SQL Editor that runs queries against your database. You're using Visual Studio or Visual Web Developer Express? Open the Database Explorer, right-click on the database and choose "new query". You'll be able to copy the SQL into the window provided and hit the "execute" button.

If you have Enterprise Manager of SQL Server Management Studio - which you probably don't - these have better SQL Editors, but the Database Explorer should do fine.

|||

One more thing - if you're using Visual Web Developer Express then the Database Explorer query window doesn't let you run stored procedures. That means the CS4ever's second example that starts with "exec" will throw an error - you'll need Management Studio instead.

But you'll be able to run his first example "Select.." fine. Good luck!

How to determine isolation level

Running SQLServer2000 on Win2k. How can I determine the isolation level of
a running transaction?
Thanx.Vince wrote:
> Running SQLServer2000 on Win2k. How can I determine the isolation
> level of a running transaction?
> Thanx.
READ COMMITTED is the default unless you specifically change it using
SET TRANSACTION ISOLATION LEVEL. It can also be overridden by the SQL
statement itself. If the isolation level is overriden for the session,
you can see this using DBCC USEROPTIONS (look for the item called
"isolation level"). If the "isolation level" option is not there, it
means you're using the default value.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thank you for the response. Is there any way to determine the isolation
level of another transaction (not mine); thru Enterprise Manager, for
example?
Thanx again.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:umXsW3l3FHA.2432@.TK2MSFTNGP10.phx.gbl...
> Vince wrote:
> READ COMMITTED is the default unless you specifically change it using
> SET TRANSACTION ISOLATION LEVEL. It can also be overridden by the SQL
> statement itself. If the isolation level is overriden for the session,
> you can see this using DBCC USEROPTIONS (look for the item called
> "isolation level"). If the "isolation level" option is not there, it
> means you're using the default value.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||Vince wrote:
> Thank you for the response. Is there any way to determine the
> isolation level of another transaction (not mine); thru Enterprise
> Manager, for example?
There may be a way to pull that information from sysprocesses, but
nothing I can see directly. In any case, that won't tell you what
isolation level the user is currently using anyway for a given
transaction. Each DML statement could use a an isolation level different
that the user's default. What is the reason you are trying to determine
this information?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||I've been asked by one of our developers if this can be done. He's working
with a third party front end that doesn't afford a lot of opportunity to
deal with isolation levels, but he was able to modify some of the Selects to
include a hint (read uncommited I think) and wanted to make sure that SQL
Server was actually taking the hint.
It's an interesting question and I don't work with SQL Server very much, so
I thought I'd open it up to the experts.
Thanx again for all your help.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:OKfXA6v3FHA.3976@.TK2MSFTNGP15.phx.gbl...
> Vince wrote:
> There may be a way to pull that information from sysprocesses, but
> nothing I can see directly. In any case, that won't tell you what
> isolation level the user is currently using anyway for a given
> transaction. Each DML statement could use a an isolation level different
> that the user's default. What is the reason you are trying to determine
> this information?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||Vince wrote:
> I've been asked by one of our developers if this can be done. He's
> working with a third party front end that doesn't afford a lot of
> opportunity to deal with isolation levels, but he was able to modify
> some of the Selects to include a hint (read uncommited I think) and
> wanted to make sure that SQL Server was actually taking the hint.
>
If the hint is in the query, then I think you can just assume SQL Server
is using it. However, READ UNCOMMITTED (which is the same as a NOLOCK
hint in the query and allows dirty reads) is only applicable for
SELECTs.
David Gugick
Quest Software
www.imceda.com
www.quest.com

How to determine isolation level

Running SQLServer2000 on Win2k. How can I determine the isolation level of
a running transaction?
Thanx.Vince wrote:
> Running SQLServer2000 on Win2k. How can I determine the isolation
> level of a running transaction?
> Thanx.
READ COMMITTED is the default unless you specifically change it using
SET TRANSACTION ISOLATION LEVEL. It can also be overridden by the SQL
statement itself. If the isolation level is overriden for the session,
you can see this using DBCC USEROPTIONS (look for the item called
"isolation level"). If the "isolation level" option is not there, it
means you're using the default value.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thank you for the response. Is there any way to determine the isolation
level of another transaction (not mine); thru Enterprise Manager, for
example?
Thanx again.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:umXsW3l3FHA.2432@.TK2MSFTNGP10.phx.gbl...
> Vince wrote:
> > Running SQLServer2000 on Win2k. How can I determine the isolation
> > level of a running transaction?
> >
> > Thanx.
> READ COMMITTED is the default unless you specifically change it using
> SET TRANSACTION ISOLATION LEVEL. It can also be overridden by the SQL
> statement itself. If the isolation level is overriden for the session,
> you can see this using DBCC USEROPTIONS (look for the item called
> "isolation level"). If the "isolation level" option is not there, it
> means you're using the default value.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||Vince wrote:
> Thank you for the response. Is there any way to determine the
> isolation level of another transaction (not mine); thru Enterprise
> Manager, for example?
There may be a way to pull that information from sysprocesses, but
nothing I can see directly. In any case, that won't tell you what
isolation level the user is currently using anyway for a given
transaction. Each DML statement could use a an isolation level different
that the user's default. What is the reason you are trying to determine
this information?
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||I've been asked by one of our developers if this can be done. He's working
with a third party front end that doesn't afford a lot of opportunity to
deal with isolation levels, but he was able to modify some of the Selects to
include a hint (read uncommited I think) and wanted to make sure that SQL
Server was actually taking the hint.
It's an interesting question and I don't work with SQL Server very much, so
I thought I'd open it up to the experts.
Thanx again for all your help.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:OKfXA6v3FHA.3976@.TK2MSFTNGP15.phx.gbl...
> Vince wrote:
> > Thank you for the response. Is there any way to determine the
> > isolation level of another transaction (not mine); thru Enterprise
> > Manager, for example?
> There may be a way to pull that information from sysprocesses, but
> nothing I can see directly. In any case, that won't tell you what
> isolation level the user is currently using anyway for a given
> transaction. Each DML statement could use a an isolation level different
> that the user's default. What is the reason you are trying to determine
> this information?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||Vince wrote:
> I've been asked by one of our developers if this can be done. He's
> working with a third party front end that doesn't afford a lot of
> opportunity to deal with isolation levels, but he was able to modify
> some of the Selects to include a hint (read uncommited I think) and
> wanted to make sure that SQL Server was actually taking the hint.
>
If the hint is in the query, then I think you can just assume SQL Server
is using it. However, READ UNCOMMITTED (which is the same as a NOLOCK
hint in the query and allows dirty reads) is only applicable for
SELECTs.
--
David Gugick
Quest Software
www.imceda.com
www.quest.comsql

How to determine isolation level

Running SQLServer2000 on Win2k. How can I determine the isolation level of
a running transaction?
Thanx.
Vince wrote:
> Running SQLServer2000 on Win2k. How can I determine the isolation
> level of a running transaction?
> Thanx.
READ COMMITTED is the default unless you specifically change it using
SET TRANSACTION ISOLATION LEVEL. It can also be overridden by the SQL
statement itself. If the isolation level is overriden for the session,
you can see this using DBCC USEROPTIONS (look for the item called
"isolation level"). If the "isolation level" option is not there, it
means you're using the default value.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Thank you for the response. Is there any way to determine the isolation
level of another transaction (not mine); thru Enterprise Manager, for
example?
Thanx again.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:umXsW3l3FHA.2432@.TK2MSFTNGP10.phx.gbl...
> Vince wrote:
> READ COMMITTED is the default unless you specifically change it using
> SET TRANSACTION ISOLATION LEVEL. It can also be overridden by the SQL
> statement itself. If the isolation level is overriden for the session,
> you can see this using DBCC USEROPTIONS (look for the item called
> "isolation level"). If the "isolation level" option is not there, it
> means you're using the default value.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
|||Vince wrote:
> Thank you for the response. Is there any way to determine the
> isolation level of another transaction (not mine); thru Enterprise
> Manager, for example?
There may be a way to pull that information from sysprocesses, but
nothing I can see directly. In any case, that won't tell you what
isolation level the user is currently using anyway for a given
transaction. Each DML statement could use a an isolation level different
that the user's default. What is the reason you are trying to determine
this information?
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||I've been asked by one of our developers if this can be done. He's working
with a third party front end that doesn't afford a lot of opportunity to
deal with isolation levels, but he was able to modify some of the Selects to
include a hint (read uncommited I think) and wanted to make sure that SQL
Server was actually taking the hint.
It's an interesting question and I don't work with SQL Server very much, so
I thought I'd open it up to the experts.
Thanx again for all your help.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:OKfXA6v3FHA.3976@.TK2MSFTNGP15.phx.gbl...
> Vince wrote:
> There may be a way to pull that information from sysprocesses, but
> nothing I can see directly. In any case, that won't tell you what
> isolation level the user is currently using anyway for a given
> transaction. Each DML statement could use a an isolation level different
> that the user's default. What is the reason you are trying to determine
> this information?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
|||Vince wrote:
> I've been asked by one of our developers if this can be done. He's
> working with a third party front end that doesn't afford a lot of
> opportunity to deal with isolation levels, but he was able to modify
> some of the Selects to include a hint (read uncommited I think) and
> wanted to make sure that SQL Server was actually taking the hint.
>
If the hint is in the query, then I think you can just assume SQL Server
is using it. However, READ UNCOMMITTED (which is the same as a NOLOCK
hint in the query and allows dirty reads) is only applicable for
SELECTs.
David Gugick
Quest Software
www.imceda.com
www.quest.com

How to Determine if MSDE 2K running

Hi All
I need to check if MSDE2K service is running on a stand-alone computer using
VB6
I have tried using SQLDMO.ListAvailableServers but find it very
unreliable...
Dim oApp As SQLDMO.Application
Dim oNames As SQLDMO.NameList
Set oApp = CreateObject("SQLDMO.Application")
Set oNames = oApp.ListAvailableSQLServers()
MsgBox oNames.count
If I first run this code it detects my MSDE service oNames.count = 1
(correct)
If I stop MSDE, this code returns oNames.count = 0 (correct)
If I restart MSDE (icon indicates running) oNames.count still returns 0
(incorrect)
Any ideas
Regards
Steve
I know it's not the best way, but how about just writing an ADO application
that executes a test query in one of the databases? If your app fails to
connect to MSDE, then you know you have a problem.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"steve" <sfrancis@.bigpond.net.au> wrote in message
news:etCwYZcjFHA.320@.TK2MSFTNGP09.phx.gbl...
Hi All
I need to check if MSDE2K service is running on a stand-alone computer using
VB6
I have tried using SQLDMO.ListAvailableServers but find it very
unreliable...
Dim oApp As SQLDMO.Application
Dim oNames As SQLDMO.NameList
Set oApp = CreateObject("SQLDMO.Application")
Set oNames = oApp.ListAvailableSQLServers()
MsgBox oNames.count
If I first run this code it detects my MSDE service oNames.count = 1
(correct)
If I stop MSDE, this code returns oNames.count = 0 (correct)
If I restart MSDE (icon indicates running) oNames.count still returns 0
(incorrect)
Any ideas
Regards
Steve
|||hi Steve,
steve wrote:
> Hi All
> I need to check if MSDE2K service is running on a stand-alone
> computer using VB6
> I have tried using SQLDMO.ListAvailableServers but find it very
> unreliable...
> Dim oApp As SQLDMO.Application
> Dim oNames As SQLDMO.NameList
> Set oApp = CreateObject("SQLDMO.Application")
> Set oNames = oApp.ListAvailableSQLServers()
> MsgBox oNames.count
> If I first run this code it detects my MSDE service oNames.count = 1
> (correct)
> If I stop MSDE, this code returns oNames.count = 0 (correct)
> If I restart MSDE (icon indicates running) oNames.count still returns
> 0 (incorrect)
you could use the SQLDMOSQLServer Status property,
http://msdn.microsoft.com/library/de..._p_s_769l.asp,
but this requires you to be already connected ot the SQL Server instance..
and, as you already saw, the ListAvailableServers is not reliable, because
of the nature of the broadcast call of the ODBC SQLBrowseConnect api used by
the DMO method, where the timeframe window is involved as well...
ListAvailableServer uses ODBC function SQLBrowseConnect() provided by ODBC
libraries installed by MDAC;
this is a mechanism working in broadcast calls, which result never are
conclusive and consistent, becouse results are influenced of various
servers's answer states, answer time, etc.
Until Mdac 2.5, SQLBrowseConnect function works based on a NetBIOS
broadcast, on which SQL Servers respond (Default protocol for SQL Server
7.0), while in SQL Server 2000 the rules changed, because the default client
protocol changed to TCP/IP and now a UDP broadcast is used, beside a NetBIOS
broadcast, listening on port 1434:
which is using a UDP broadcast on port 1434, if instance do not listen or
not respond on time they will not be part of the enumeration.
Some basic rules for 7.0 are:
- SQL Servers have to be running on Windows NT or Windows 2000 and have to
listen on Named Pipes, that is why in 7.0 Windows 9x SQL Servers will never
show up, because they do not listen on Named Pipes.
- The SQL Server has to be running in order to respond on the broadcast.
There is a gray window of 15 minutes after shutdown, where a browse master
in the domain may respond on the broadcast and answer.
- If you have routers in your network, that do not pass on NetBIOS
broadcasts, this might limit your scope of the broadcast.
- Only servers within the same NT domain (or trust) will get enumerated.
In SQL Server 2000 using MDAC 2.6 this changes a little, because now the
default protocol has been changed to be TCP/IP sockets and instead of a
NetBIOS broadcast, they use a TCP UDP to detect the servers. The same logic
still applies roughly.
- SQL Server that are running
- SQL Server that listening on TCP/IP
- Running on Windows NT or Windows 2000 or Windows 9x
- If you use routers and these are configured not to pass UDP broadcasts,
only machines within the same subnet show up.
Upgrading to Service Pack 2 of SQL Server 2000 is required in order to have
..ListAvailableServer method to work properly, becouse precding release of
Sql-DMO Components of Sql Server 2000 present a bug in this area.
Courtesy of Mr. Gert E.R. Drapers
further Information at
http://sqldev.net/misc.htm
to the besto of my knowledge, as you can see from
http://msdn.microsoft.com/library/de...ob_s_7igk.asp,
SQLServer object does not directly exposes a disconnected property to get
it's state, so you have to connect (and eventually use the Status method,,,
but youll''be already connected)..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
sql

how to determine if a stored proc is running

I need to determine whether a stored procedure is executiing and perform
various steps dependent on the state of the procedure in question (i.e.,
determined from sysprocesses.status). I know I can get the listing of
active commands sysprocesses.cmd (from the master table, sysprocesses),
but this doesn't give me the _stored procedure_ name, as it appears in
Enterprise Manager when you click on the ProcessID and it gives the
"Last TSQL Batch..." Where exactly is this information is this stored?
Somewhere, I assume, in MDDB?
TIA!
gms--Greg M. Silverman wrote:
> I need to determine whether a stored procedure is executiing and
> perform various steps dependent on the state of the procedure in
> question (i.e., determined from sysprocesses.status). I know I can get
> the listing of active commands sysprocesses.cmd (from the master
> table, sysprocesses), but this doesn't give me the _stored procedure_
> name, as it appears in Enterprise Manager when you click on the
> ProcessID and it gives the "Last TSQL Batch..." Where exactly is this
> information is this stored? Somewhere, I assume, in MDDB?
> TIA!
> gms--
>
okay, looks like DBCC INPUTBUFFER (pid) gives me what I need.
gms--

How to determine if a SP is running?

I need to to modify a stored procedure so that it can
determine if it is already running. I could create a
table to add/opdate a status record, but I would prefer
to read a system table looking for the proc running on
a connection if possible.
If anyone has done something like this, I would really
like to hear about it.
tia,
BillYou can't do that directly, but you can use an application lock inside your
stored procedure. See sp_getapplock in Books Online for the details.
Jacco Schalkwijk
SQL Server MVP
<bill_sheets@.hotmail.com> wrote in message
news:1107442939.517642.53580@.g14g2000cwa.googlegroups.com...
>I need to to modify a stored procedure so that it can
> determine if it is already running. I could create a
> table to add/opdate a status record, but I would prefer
> to read a system table looking for the proc running on
> a connection if possible.
> If anyone has done something like this, I would really
> like to hear about it.
> tia,
> Bill
>

How to determine if a linked server is available

Hello all,
I need to write data from one SQL server in to a linked server running on an
other machine. How can i check with T-SQL commands if this server is
available?
If it is not available the stored procedure directly quits with an error.
But this procedure should continue in differt ways, depending on the result
of the linked server transaction.
Both servers are MS SQL 2000, SP3a
Thanks a lot.
Try
if exists(select * from master.dbo.sysservers where srvname =
'OtherServer')
BEGIN
-- my code
END
sql

How to determine if a linked server is available

Hello all,
I need to write data from one SQL server in to a linked server running on an
other machine. How can i check with T-SQL commands if this server is
available?
If it is not available the stored procedure directly quits with an error.
But this procedure should continue in differt ways, depending on the result
of the linked server transaction.
Both servers are MS SQL 2000, SP3a
Thanks a lot.Try
if exists(select * from master.dbo.sysservers where srvname =
'OtherServer')
BEGIN
-- my code
END

How to determine a physical node name with a sql cluster?

I am trying to determine what physical node my sql server cluster is running
on from within SQL Server. How can I do that?For example. In query analyzer
select @.@.servername
- returns the virtual name.
Select CONVERT(Varchar(25), SERVERPROPERTY('machinename'))
- returns the virtual name.
"Charles" wrote:
> I am trying to determine what physical node my sql server cluster is running
> on from within SQL Server. How can I do that?

How to detect a stored proc is running from TSQL?

I launch a stored proc from a job that is scheduled several times a day. I want the proc to detect if it is still running (from a previous process) and exit out if it is. I want to be absolutely sure it is or is not running.

Thanks!

I don't know of a straight forward way to check if a stored prcocedure is running but I have a couple of workarounds. Before that I just want to highlight if the only source that is running the stored procedure is the job you're talking about, then if it is still running and it is time for the next job, the next instance won't run so you wouldn't have the case that 2 job instances are running in the same time. Regardless here are the workarounds:

Include in your database a table that you can use to check the status of the stored procedure. For example a table called SystemStatus that includes a Key column and a Status column. Add a row for this procedure with initial value in the status false (for not running). Make the first statement in the procedure a check on the value of the status field for this row. If it true then another instance is running so end the procedure. If it is false then update it to true and let the final statement in the procedure update it back to false|||

I can't able to understand "I want the proc to detect if it is still running (from a previous process) and exit out if it is"

You mean, to find the current SP is already running on your Server (or job) before executing it.

If yes, you need not worry about it. Bcs if the current job is executing then the SQL server never initate the next scheduled job, it will wait to complete the current job, then it will find the next possible schedule to execute.

if you 2 or more parllel jobs with same SP (not really required) then you need to follow some log based events.

|||

Sami,

Thank you for your quick and detailed response. Actually I have used this method before. Unfortunately, this transaction is what I wanted to avoid here because it is a long-running proc that runs many other procs so I wanted to avoid holding blocking other users just to prevent my proc from running simultaneously. Thanks again!

Mr. P.

|||

You could use application locks in SQL Server. Something like below should work. This can be simplified a bit in SQL Server 2005.

Code Snippet

exec @.rc = sp_getapplock 'Only_this_SP', 'Exclusive', default, 0

if @.rc = -1

begin

-- SP is already running:

end

-- Release lock at end of SP:

exec @.rc = sp_releaseapplock 'Only_this_SP'

How to design the history table to be more efficient?

I am running a website of crossword puzzle and Sudoku games. The website is designed to be:

There are 20-30 games onlines each day.
Every registered user could play and submit the game to win scores.
For each game, every registered user could get the score for ONLY one time. i.e., No score will be calculated if the user had finished the game before.
To avoid wasting time on a game finished before, user will be notified with hint message in the page when enter a already finished game.

The current solution is:
3 tables are designed for the functions mentioned above.

Table A: UserTable --storing usering information, userid
Table B: GameList --storing all the game information.
Related fields:
GameID primary key
FinshiedTimes recording how many times the game has been finished
Table C: FinishHistory --storing who and when finished the game
Related fields:
GameID ID of the game
UserID ID of the user
FinishedDate the time when the game was finshied

PS: Fields listed above are only related ones, not the complete structure.

Each time when user enters the game, the program will read Table B(GameList), listing all the available game and the times games have been finished. User could then choose a desired game to play.

When user clicks the link and enter a page showing the detail content of the game, the program will read Table C(FinishHistory) to check whether user has finished this game before. If yes, hint message will be shown in the page.

When user finishes the game and submit, the program will again read Table C(FinishHistory) to check whether user has finished this game before. If yes, hint message will be shown in the page. If no, user will get the score.

Existing Problems:
With the increase of game and users, the capacity of Table C(FinishHistory) grows rapidly. And each time when a game is loaded, the Table C will be loaded to check, and when a game is submitted, the Table C will be loaded to check again. So it is only a time question to find out Table C to become a bottleneck.

Does any one here have any good suggestions to change / re-invent a new structure or design to avoid this bottleneck?what the? "loading a table" won't you just be searching the table?

What size do you expect this table to grow to?|||sorry, what i said "loading the table" means to do the query via a sql statement.

what i am worry about is the table will become bigger and bigger with more and more games online. Although i have added index to fields GameID and UserId in Table C, but i still think the efficiency will decrease with more lines inserted into the table.

Currently i have 2000 around games, each game is played 40 times for average. this is a big number compared with the total number of games.

I am wondering if there is another way to design the structure to avoid this.

my friend suggest me to add an extra field in Table B, holding all the finished userID, which likes: "User001|User005|User007"

And when a game is loaded by a user, the program could match the user's id with this field to find whether the user has played this game before.

But I am afraid this will need a big field to hold all the possible userIDs. say I have 10000 users, and the length of a unique user ID is 6 chars, so this field should be designed to be able to hold (6+1)*10000=60000, which is quite huge, right?|||Huj

you seem to have chosen the correct approach - a classic instancing table - I would strongly recommend you don't use the flat earth approach suggested by your friend.

It does'nt look like you've reached any performance problems as yet but if you do I would primarily be looking at :-

Finish History table with Clustered composite primary Key.
Potential archiving of old data in this table
Using Ints for ID's (if your not already)
ensure instancing table (FinishHistory) only holds primary keys (ie smallest overall record length)

Should run like a rocket

GW|||my friend suggest me to add an extra field in Table B, holding all the finished userID, which likes: "User001|User005|User007"

Some freind...what a nigghtmare that would be...if you want, add a child table that stores that data...in rows|||hi all, thanks all for your reply.

Brett Kaiser, if i add a child table store that data in rows, it is actual an alternative way of TableC, which is my headache: the increase speed is much higher than TableB's....

How to Delete Data on the Subscriber but Maintain It on the Publisher?

Hi,
We're using merge replication between a Sql Server 2000 Publisher and
Subscribers on PocketPCs running Sql Server Ce.
One part of our data sync scheme deals only with newly created data on
the PocketPC. For this we have an upload-only replication that sends
the new data from the device to the Publisher.
Due to storage constraints on the PcketPC, we want to delete the data
from these SQL CE tables after it has been sent to the Publisher, but I
am assuming that if we do so, upon the next replication this data will
then be deleted from the Publisher also.
We want to avoid that from happening - we want to delete the data on
the subscriber after it has been merged, but we also want to maintain
the data on the publisher.
Is there any way that we can delete the data from the subscriber but
still maintain it on the publisher.
Thanks,
JJ
create a sql account which has rights to insert and update, but not delete
on your publisher. Add this to the pal, and use this account in your SQL CE
program in the PublisherLogin parameter.
Now, for the articles which you wish the deletes not to make it to the
publisher, right click on the publication, select publication properties,
and then click on the articles tab. Click on the browse button to the right
of your table names, and select the merging changes tab. Ensure the delete
command is selected.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JJ" <jjabour@.gmail.com> wrote in message
news:1127612785.479827.68040@.o13g2000cwo.googlegro ups.com...
> Hi,
> We're using merge replication between a Sql Server 2000 Publisher and
> Subscribers on PocketPCs running Sql Server Ce.
> One part of our data sync scheme deals only with newly created data on
> the PocketPC. For this we have an upload-only replication that sends
> the new data from the device to the Publisher.
> Due to storage constraints on the PcketPC, we want to delete the data
> from these SQL CE tables after it has been sent to the Publisher, but I
> am assuming that if we do so, upon the next replication this data will
> then be deleted from the Publisher also.
> We want to avoid that from happening - we want to delete the data on
> the subscriber after it has been merged, but we also want to maintain
> the data on the publisher.
> Is there any way that we can delete the data from the subscriber but
> still maintain it on the publisher.
> Thanks,
> JJ
>

How to delete data older than X days, without considering time

When running the following SQL statements, I get the same results.
Though I need to count only -30 days. Both statements below also
consider the time of the day as well, which is not desired

DELETE FROM MNT_R
WHERE MNT_R.TIMESTAMP < GETDATE()- 30

DELETE FROM MNT_R
WHERE MNT_R.TIMESTAMP < DATEADD(d, -30, GETDATE())

Here is the format of the values in column
MNT_R.TIMESTAMP
2005-08-09 06:06:44.577
2005-08-09 06:06:46.810
2005-08-09 06:06:49.060

So, since data are inserted into the MNT_R table every few seconds, my
delete statement will delete different number of rows, according to the
time of the day it runs.

Can you please post a SQL query that will not give me this headache?

thanx a lot allHi there,

You have to convert the source column to a non-using time format like
ISO:

DELETE FROM MNT_R
WHERE VARCHAR(10),MNT_R.TIMESTAMP < CONVERT(VARCHAR(10),GETDATE()-
30,112)

HTH, Jens Suessmeyer.|||nai (nioannides@.laiki.com) writes:
> When running the following SQL statements, I get the same results.
> Though I need to count only -30 days. Both statements below also
> consider the time of the day as well, which is not desired
>
> DELETE FROM MNT_R
> WHERE MNT_R.TIMESTAMP < GETDATE()- 30
> DELETE FROM MNT_R
> WHERE MNT_R.TIMESTAMP < DATEADD(d, -30, GETDATE())
>
> Here is the format of the values in column
> MNT_R.TIMESTAMP
> 2005-08-09 06:06:44.577
> 2005-08-09 06:06:46.810
> 2005-08-09 06:06:49.060
> So, since data are inserted into the MNT_R table every few seconds, my
> delete statement will delete different number of rows, according to the
> time of the day it runs.
> Can you please post a SQL query that will not give me this headache?

Instead of getdate() used convert(char(8), getdate(), 112) to strip
of the time portion.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

How to delete all rows in a table

I am running SQL Server 2000.
I need to delete all of the rows in a table within Enterprize management.
How can I do this. All I see is how to delete the table itself.
I only want to delete the data.
Ron
Mark the first line go to the last line with STRG+End, mark the last line
(that marks all) and press DEL.
Other option would be to swtich to SQL mode (in the SQL designer) and
manipulate the SQl Statement as Follows: Delete From SomeTable --OR Truncate
table SomeTable (which is not logged). You can also issue these commands in
the QA.
HTH, jens Suessmeyer.
"Ron" wrote:

> I am running SQL Server 2000.
> I need to delete all of the rows in a table within Enterprize management.
> How can I do this. All I see is how to delete the table itself.
> I only want to delete the data.
> Ron
|||Thanks
"Jens Sü?meyer" wrote:
[vbcol=seagreen]
> Mark the first line go to the last line with STRG+End, mark the last line
> (that marks all) and press DEL.
> Other option would be to swtich to SQL mode (in the SQL designer) and
> manipulate the SQl Statement as Follows: Delete From SomeTable --OR Truncate
> table SomeTable (which is not logged). You can also issue these commands in
> the QA.
> HTH, jens Suessmeyer.
> "Ron" wrote:
|||Ron wrote:
> I am running SQL Server 2000.
> I need to delete all of the rows in a table within Enterprize
> management. How can I do this. All I see is how to delete the table
> itself.
> I only want to delete the data.
> Ron
The fastest way to do this as an administrator/db owner is to truncate
the table to avoid excessive logging, unless you want the operation
logged. You can do this from Query Analyzer or any query tool by issuing
a TRUNCATE TABLE <table_name>
David Gugick
Quest Software
www.imceda.com
www.quest.com