how to diff the data of same table of two sql servers

as subject.
any freeware or done at sql level?
thanks!What?
"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
> as subject.
> any freeware or done at sql level?
> thanks!
>|||say, i have a table Order at server A and B,
i want to diff the sql data at two servers.
"ChrisR" <chris@.noemail.com> wrote in message
news:OgXnH3FzEHA.1188@.tk2msftngp13.phx.gbl...
> What?
>
> "Mullin Yu" <mullin_yu@.ctil.com> wrote in message
> news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
> > as subject.
> >
> > any freeware or done at sql level?
> >
> > thanks!
> >
> >
>|||It's not free but it's real cheap. www.red-gate.com has a product called
data compare that will do what you ask.
--
Andrew J. Kelly SQL MVP
"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:%23tl6F6FzEHA.1596@.TK2MSFTNGP10.phx.gbl...
> say, i have a table Order at server A and B,
> i want to diff the sql data at two servers.
> "ChrisR" <chris@.noemail.com> wrote in message
> news:OgXnH3FzEHA.1188@.tk2msftngp13.phx.gbl...
>> What?
>>
>> "Mullin Yu" <mullin_yu@.ctil.com> wrote in message
>> news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
>> > as subject.
>> >
>> > any freeware or done at sql level?
>> >
>> > thanks!
>> >
>> >
>>
>|||I'm with Andrew, I vote Red Gate
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
> as subject.
> any freeware or done at sql level?
> thanks!
>|||"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
> as subject.
> any freeware or done at sql level?
> thanks!
>
What about using a FULL OUTER JOIN and then pulling the values with NULL.
Those would be the difference records. The matches would be the matching
records.
Rick Sawtell
MCT, MCSD, MCDBA|||Have a look at www.dbghost.com - why bother with a product that doesn't
always work?
DB Ghostâ?¢ provides you with a fully automated BUILD, COMPARISON and
SYNCHRONIZATION capability for your SQL Server databases and is the only
product on the market that ensures database integrity as DB Ghostâ?¢ will build
your database directly from your source control system. No other product in
the world does this. No other product can build, compare and synchronize a
target database making it match the source scripts precisely, every single
time, not just sometimes, but every single time. Try and prove us wrong.
Something else that might grab your interest is that an incredible 94% of
our clients (94%!!!) previously purchased our competitors products and soon
found that in the real world, these products let them down time after time.
Don't make the same mistake - why would you buy from our competitors who, for
similar money, can only offer you tools that don't build, and only compare
and sometimes synchronize...food for thought?
"ChrisR" wrote:
> What?
>
> "Mullin Yu" <mullin_yu@.ctil.com> wrote in message
> news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
> > as subject.
> >
> > any freeware or done at sql level?
> >
> > thanks!
> >
> >
>
>

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 SQL version from a command line

Hi, I am trying to run a command line script against 50
SQL servers to determine the SQL ver and SP level
installed but when I run srvinfo -ns this returns way more
info then I require and does not include SP level. I have
run registry searches but this will only give the
installed version of SQL, it will not give me the latest
ver, i.e. if it has had an SP installed or not. Any help
would be great. ThanksYou can use OSQL from the command line to connect to SQL Server. Once
connected issue SELECT @.@.VERSION or use SERVERPROPERTY
Without using SQL you could always do a DIR and seach for sqlservr.exe, from
it's size and file data you should be able to work out which version.
--
HTH
Ryan Waight, MCDBA, MCSE
"Jonathon" <Jonathon_Taaffe@.hotmail.com> wrote in message
news:296d601c3919f$d3ea6f90$a601280a@.phx.gbl...
> Hi, I am trying to run a command line script against 50
> SQL servers to determine the SQL ver and SP level
> installed but when I run srvinfo -ns this returns way more
> info then I require and does not include SP level. I have
> run registry searches but this will only give the
> installed version of SQL, it will not give me the latest
> ver, i.e. if it has had an SP installed or not. Any help
> would be great. Thanks|||Jonathan,
Refer to following url:
http://support.microsoft.com/default.aspx?scid=kb;en-us;q321185
You can run these queries from command prompt, using osql utility by passing quries to -Q
parameter.
--
- Vishal|||You can run 'select @.@.version' with osql in dos.
>--Original Message--
>Hi, I am trying to run a command line script against 50
>SQL servers to determine the SQL ver and SP level
>installed but when I run srvinfo -ns this returns way
more
>info then I require and does not include SP level. I have
>run registry searches but this will only give the
>installed version of SQL, it will not give me the latest
>ver, i.e. if it has had an SP installed or not. Any help
>would be great. Thanks
>.
>|||In article <296d601c3919f$d3ea6f90$a601280a@.phx.gbl>, Jonathon
<Jonathon_Taaffe@.hotmail.com> writes
>Hi, I am trying to run a command line script against 50
>SQL servers to determine the SQL ver and SP level
>installed but when I run srvinfo -ns this returns way more
>info then I require and does not include SP level. I have
>run registry searches but this will only give the
>installed version of SQL, it will not give me the latest
>ver, i.e. if it has had an SP installed or not. Any help
>would be great. Thanks
If you are looking for any SQL Servers then you could try SQL Scan as
well-
http://www.microsoft.com/sql/downloads/securitytools.asp
I use a combination of methods to monitor what servers appear on the
network and in what state.
The registry will tell you which SP you are running, but it will not
tell you if there are any patches on top as well.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVers
ion]
The CurrentVersion value gives you the base version, e.g 8.00.194 = SQL
Server 2000 RTM.
The CSDVersion key will then tell you the service pack level, e.g.
8.00.761 = SP3a. Note this is actually quite useful because the TSQL
@.@.VERSION and similar will only give you 8.00.760, which means SP3 or
SP3a. However since I also have the latest security patch installed
@.@.VERSION says 8.00.818, so a combination is often better.
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com
PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org|||I've just tried, it worked without problem.
From a cmd line :-
OSQL -Sservername -Q"select @.@.Version" -E
--
HTH
Ryan Waight, MCDBA, MCSE
"Ray Miao" <rmiao@.bloomberg.com> wrote in message
news:03d801c391b4$f2bded60$a401280a@.phx.gbl...
> You can run 'select @.@.version' with osql in dos.
> >--Original Message--
> >Hi, I am trying to run a command line script against 50
> >SQL servers to determine the SQL ver and SP level
> >installed but when I run srvinfo -ns this returns way
> more
> >info then I require and does not include SP level. I have
> >run registry searches but this will only give the
> >installed version of SQL, it will not give me the latest
> >ver, i.e. if it has had an SP installed or not. Any help
> >would be great. Thanks
> >.
> >

How to determine SQL Server service pack level

I can't remember what command to issue in query analyzer
to see what build the SQL server is at. Can someone help
me? Thanks... (SQL2K)SELECT @.@.Version
If using 2000
select serverproperty('Productversion') --major minor build level
select serverproperty('Productlevel')-- SP
select serverproperty('edition') --edition of SQL Server
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org|||http://www.aspfaq.com/2160
"Brandon" <nospam@.noway.com> wrote in message
news:09ca01c3632b$cfb5d650$a401280a@.phx.gbl...
> I can't remember what command to issue in query analyzer
> to see what build the SQL server is at. Can someone help
> me? Thanks... (SQL2K)|||Fantastic! Thank you!
>--Original Message--
>SELECT @.@.Version
>If using 2000
>select serverproperty('Productversion') --major minor
build level
>select serverproperty('Productlevel')-- SP
>select serverproperty('edition') --edition of SQL Server
>--
>
>Allan Mitchell (Microsoft SQL Server MVP)
>MCSE,MCDBA
>www.SQLDTS.com
>I support PASS - the definitive, global community
>for SQL Server professionals - http://www.sqlpass.org
>.
>

how to determine sp level at app install time?

Hi,
I have an application that uses an instance of MSDE we create. What should
our installer check to find out if we need to install msde sp3, when our
users upgrade our software?
thanks,
David
Hi,
Installation of ur application require a valid login to sql server right,
then you could verify the service pack by executing the below query.
SELECT SERVERPROPERTY('PRODUCTLEVEL')
This returns SP3 if the service pack installed is SP3. Based on this u could
continue the installation.
Thanks
Hari
SQL Server Mvp
"David Burson" <DavidBurson@.discussions.microsoft.com> wrote in message
news:C52717FF-A27E-4686-89ED-01AA4FDAF179@.microsoft.com...
> Hi,
> I have an application that uses an instance of MSDE we create. What
> should
> our installer check to find out if we need to install msde sp3, when our
> users upgrade our software?
> thanks,
> David
|||Thanks Hari,
We have a problem with that solution though. I guess we need to use osql to
run the serverproperty query, but we don't know how to run it so that the
user doesn't see it run, and also we don't know how to get the response from
osql back into our installer code.
Do you have any suggestions on how to handle those issues?
Thanks very much!
David
"Hari Prasad" wrote:

> Hi,
> Installation of ur application require a valid login to sql server right,
> then you could verify the service pack by executing the below query.
> SELECT SERVERPROPERTY('PRODUCTLEVEL')
> This returns SP3 if the service pack installed is SP3. Based on this u could
> continue the installation.
> Thanks
> Hari
> SQL Server Mvp
> "David Burson" <DavidBurson@.discussions.microsoft.com> wrote in message
> news:C52717FF-A27E-4686-89ED-01AA4FDAF179@.microsoft.com...
>
>

How to determine MSDE version and Service Pack level

How can I determine what versioin and service pack of MSDE i have?You might try to look at @.@.VERSION for this information.

Assuming you don't have Query Analyzer, so use the command-line utility osql.

Shell out to a command prompt and type:

osql -E -q "PRINT @.@.VERSION"

Terri|||I tried this and the response was:

[Shared Memory]SQL Server does not exist or access denied.
[Shared Memory]ConnectionOpen (Connect()).

Is there a next step?

Thanks,
Bob|||Those are the messages I get when the SQL Server is not started.

You need to start SQL Server. You do this through the SQL Server Service Manager in your systray.

Also, this is a good link to keep handy:HOW TO: Manage the SQL Server Desktop Engine (MSDE 2000) by Using the Osql Utility

Terri|||Thank you Terri,

After a reboot and restart it worked and brought me to my next problem(s).

I think what I really need to do is uninstall MSDE and start over. I don't seem to able to get into my DB, the path where MSSQL ended up is too long to work with in a command shell, etc. Is there an easy way to get uninstall all of it? Without a ADD/REMOVE option or Uninstall I'm lost.

Thanks for the first anser and the link. I bookmarked it and will use it on the next go around.

Thanks,
Bob|||Thanks to tmorton for the answer that worked. It was really simple.|||Codfish -- glad to be of help.

Disker -- the path were MSSQL was installed is going to be long -- and it shouldn't matter. You should never need to type it in. The install should add it to your PATH so that you will be able to go to the command shell and type OSQL from anywhere and have it work.

Please don't uninstall/reinstall unless absolutely necessary.

You say that you "don't seem to be able to get into" your DB. What tool(s) are you trying to use for this? What error(s) are you receiving?

Terri|||Thanks for the help Terri,

Some hardware/software specs -

Running XP PRO Sp1, IIS on AMD 900, 512MB. IIS is running as is SQLSERVR, SQLMANGR, and sqlagent. These are listed in Task Manager [processes] as running and also in Services as Started. I have the little server icon on the task bar with a little blue or green arrow blinking every so often. I installed MSDE from a command line after entering SAPWD="mypassword" The password I chose is 9 alpha-numeric.

I did follow your suggestion yesterday and read through the OSQL commands. This did allow me to create another DB. This command returned a message saying that it had created the DB giving the name and the space allocated. I checked on this and did find the two files ending with .mdf and .ldf in C:\Program Files\Microsoft SQL Server\MSSQL\Data.

For SQL management I've been trying a few things. First, I tried SQL Server Web Data Administrator. When I use this the IIS option only gives me a page of HTML. The Cassini model gives me a login screen which asks for username, password, server and the two options of windows authentication or SQL. I have tried both and cannot gain access being given a incorrect password or username message.

My newest attempt and what I would like to use is Enterprise Manager and have downloaded SQLEntMan_0.5.1.zip to use. I unzipped this to a folder named EntMan but cannot get that to start up. I tried browsing to the folder with IE but it just shows me the files.

So it seems that I have installed MSDE semi-correctly in that I can find it and create databases through OSQL. I can see the DB but cannot access it through any interface I have tried so far.

Thanks so much for your time and help. I really appreciate it.|||Instead of this moving target -- let's try to concentrate on getting SQL Server Web Data Administrator working for you. (I prefer this over ASP Enterprise Manager strictly because I have installed and used it myself and can more easily help you troubleshoot it.)

With the hardware/software specs you listed, I am assuming that all of the elements of your environment are local to your workstation (IIS, SQL).

Is your local IIS able to serve up any ASP.NET pages at all? Get back to us on this and we can move forward from there.

Terri|||HI,

If I type http://localhost/or http://desktop/ it takes me to a web page which displays the localstart.asp page which states the following:

Your Web service is now running.
You do not currently have a default Web page established for your users. Any users attempting to connect to your Web site from another machine are currently receiving an Under Construction page. Your Web server lists the following files as possible default Web pages: default.htm,default.asp,index.htm,iisstart.asp. Currently, only iisstart.asp exists.

To add documents to your default Web site, save files in c:\inetpub\wwwroot\.

I assume this means IIS is capable of serving up pages.

I have the SQL Server Web Adm. Version 2.0 installed to c: as is everything else.

I won't do anything else until I hear back.

Thanks,

Bob|||Yes, IIS is serving up pages, but specifically can it serve up ASP.NET pages (.aspx extension)?

Create test.aspx and put it into your wwwroot directory and try to access it from your browser (http://localhost/test.aspx):

<%@. Page Language="VB" %>
<html>
 <head>
 <title>ASP.NET Test</title>
 </head
 <body>
 <center>
 <form action="test.aspx" method="post">
 <% Dim I As Integer
 For I = 0 to 7 %>
 <font size="<%=I%>">Welcome to ASP.NET</font><br>
 <% Next %>
 </form>
 </center>
 </body>
</html>

What you should see is "Welcome to ASP.NET" 7 times in increasingly bigger font sizes. If you don't, then IIS is not serving up your .ASPX pages correctly and you will need to repair te IIS mappings:

To repair IIS mappings for ASP.NET with Aspnet_regiis.exe

1. ClickStart, and then clickRun.
2. In theRun dialog box, typecmd, and then clickOK.
3. At the command prompt, type:
"systemroot\Microsoft.NET\Framework\version\aspnet_regiis.exe"-i

In this path,systemroot is the folder containing your operating system files (C:\Windows on Windows XP systems, C:\WinNT on Windows 2000 Server systems, and so on), andversion is the version number of the .NET Framework installed on your server.

Note You must include the quotation marks in the command.

For more information about Aspnet_regiis.exe, use step 3 with the -? option or see the ASP.NET IIS Registration Tool topic in the .NET Framework version 1.1 documentation.|||Ok I created the page but it didn't do what it was supposed to do so I ran the repair. This started and completed installing ASP.NET. The path I used was

C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\aspnet_regiis.exe" -i

Then I checked to see if the ASP.NET State Service was running and it is. Then I rebooted just to be safe, double-checked ASP.NET and tried the page again. What I get is the text for the test page you've given above. I used Frontpage to create the page and did a copy and paste of your text and entered it into the html tab in Frontpage. I then saved the page to wwwroot as a .aspx page. What I get when I use IE to go to the page is the text that was pasted in and not the page as it should appear.

IE works for any other page I go to so I don't see that as a problem. I don't have a firewall running on this box so there is problem there either. I also checked the page from another PC on my mini network using http://desktop/test/aspx and get the same text page as I do locally.

Sorry for all the work. This gets really frustrating.

Bob|||At this point I am going to have to ask you to post this problem to theSetup forum. :-( Something isn't right with your setup. Each time I've seen this, aspnet_regiis.exe -i has solved it. The people over in that forum have seen it all and should be able to get you on the right track.

To be clear -- by "this problem" I mean the inability to view the test page.

Once you are able to view that test page correctly, then give Web Data Administrator another try. (And post back if you are having trouble with it.)

Terri
PS - Don't give up!|||OK, will do.

Thanks Terri. Hope to get back to you with a note saying everything works fine.

Bob|||Back again!

I now have IIS serving .asp pages to my local PC as well as another located on the network. Both seem to be working fine so I take this to mean that my IIS problem is resolved.

Back to the SQL Web Data Administrator - I've tried this again and it's still not working the way it should. When I activate the administrator it pops with the little window saying Web Server Configuration. It lists the following options:

Desired Port:
IIS
Cassini Personal Web Server
Restore Faults

and Start and Stop buttons.

If I leave the setting on IIS and click Start it opens a webpage entitled Web Data Administrator. Moving further down I have a welcome message and then the line -

Please enter a SQL Server name:

Username: [Desktop\bob shadowed entry]
Password
Server (local)

Authentication Method:

Windows Intergrated
SQl Login

and then Login

If I select SQL Login and enter "sa" as the username and then my password leaving the (local) as the server I get the following error:

Invalid username and/or password, you are using a windows login that is not your own, or server does not exist.

This is where I'm stuck at this point. I appreciate your help and patience.

Thanks,
Bob

p.s. Should we move this to another Subject heading or start a new thread?

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 Cross DB Ownership Chaining

During the SQL Server install the Cross DB Ownership Chaining option can be
turned on at the server level. How can I determine if the server has it
turned on?
Thanks.
EXEC sp_dboption 'database_name','db chaining'
David Portas
SQL Server MVP
|||That should be the sp_configure setting. Please read about this setting in Books Online so you
understand the relationship with the database setting as well...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:2504E3E6-5757-476B-BA57-E3AC890D9FBF@.microsoft.com...
> During the SQL Server install the Cross DB Ownership Chaining option can be
> turned on at the server level. How can I determine if the server has it
> turned on?
> Thanks.
|||Sorry, you asked for the server setting. Use sp_configure as Tibor says.
David Portas
SQL Server MVP
|||Thanks Tibor and David!!!

how to determine if Cross DB Ownership Chaining

During the SQL Server install the Cross DB Ownership Chaining option can be
turned on at the server level. How can I determine if the server has it
turned on?
Thanks.EXEC sp_dboption 'database_name','db chaining'
--
David Portas
SQL Server MVP
--|||That should be the sp_configure setting. Please read about this setting in Books Online so you
understand the relationship with the database setting as well...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:2504E3E6-5757-476B-BA57-E3AC890D9FBF@.microsoft.com...
> During the SQL Server install the Cross DB Ownership Chaining option can be
> turned on at the server level. How can I determine if the server has it
> turned on?
> Thanks.|||Sorry, you asked for the server setting. Use sp_configure as Tibor says.
--
David Portas
SQL Server MVP
--|||Thanks Tibor and David!!!

how to determine if Cross DB Ownership Chaining

During the SQL Server install the Cross DB Ownership Chaining option can be
turned on at the server level. How can I determine if the server has it
turned on?
Thanks.EXEC sp_dboption 'database_name','db chaining'
David Portas
SQL Server MVP
--|||That should be the sp_configure setting. Please read about this setting in B
ooks Online so you
understand the relationship with the database setting as well...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:2504E3E6-5757-476B-BA57-E3AC890D9FBF@.microsoft.com...
> During the SQL Server install the Cross DB Ownership Chaining option can b
e
> turned on at the server level. How can I determine if the server has it
> turned on?
> Thanks.|||Sorry, you asked for the server setting. Use sp_configure as Tibor says.
David Portas
SQL Server MVP
--|||Thanks Tibor and David!!!