Does SQL Server check for constraint violation when COMMIT is called?

If there are two different transactions, both of which update the username column to 'xyz' for userid = 234 in 'Users' table. This is a unique value for username. Ater this update each transaction adds a row to 'AppLog' table. The transaction is only committed after second operation.

The 'username' column has a unique constraint on it.

If transaction isolation level is 'read committed', and both transaction execute the first operation when neither of the transactions have been committed, then the transaction that calls COMMIT later will error out or not? If COMMIT does not check constraints then it will NOT error out. As a result we will have a violation of unique constraint happening without any error being thrown by SQL Server.

sun21170:

If COMMIT does not check constraints then it will NOT error out.

The statements that COMMIT causes to execute will be checked for contraint violations. If SQL Server allowed constraint violations, it wouldn't be around very long.

sql

Does SQL 2005 purchase come with access to SQL 2000 media?

My company is planning to purchase a SQL2005 standard Processor license (unlimited users - about $5000)

Does anyone know if we can obtain SQL2000 media along with the purchase. Because of the app we are running and its stage of development, we have to install and run SQL2000 for about 6 months before we can run Sql2005. We dont want to purchase SQL2000 for such a short term use.

I asked a DELL rep to help me with this over a month ago and he still has no answers for me.

Any help with this would be greatly appreciated.

Already answered this question ton the setup & upgrade forum:

My previous response:

SQL Server 2000 is not provided with SQL Server 2005. Some components that ship with SQL Server 2005 will update existing SQL Server 2000 components to address any compatibility issues.

You have a couple of options:

* Use the SQL Server 2000 eval for the time being. Not sure if it is still available

* Use SQL Server 2000 MSDE, as it is a free download. Not sure if it is still available

* Use SQL Server 2005 but run the database(s) in SQL Server 2000 (version 8) compatibility mode.

Thanks,

Peter Saddow

|||

I believe that the SQL 2005 license covers using a downlevel version. However, for definitive answers to licensing questions, call the licensing folks at:

Licensing –VL Contact
(800) 426-9400

You will have to contact Microsoft Fullfillment to obtain the media, or if you have access to your MSDN subscription...

Does Shrinkfile Emptyfile block users?

I read BOL says this can be done online but will it generate blocks.
Is it done in small transactions?
Thanks
Paul
Can you point us to exactly where it stats that? A shrinkfile with or
without Emptyfile will lock the page or extent as it moves it from the back
of the file to the front or from file to file in the case of the empty file.
It should not hold that lock unless you wrapped it in your own transaction.
Andrew J. Kelly SQL MVP
"Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
news:eLgLMZXiFHA.2644@.TK2MSFTNGP09.phx.gbl...
>I read BOL says this can be done online but will it generate blocks.
> Is it done in small transactions?
> Thanks
> Paul
>
|||Hi Andrew
As follows. If it does it page at a time I should be OK.
Thanks
Paul
DBCC SHRINKFILE
Shrinks the size of the specified data file or log file for the related
database
........
To remove any data that may be in a file, execute DBCC
SHRINKFILE('file_name', EMPTYFILE) before executing ALTER DATABASE.
The database being shrunk does not have to be in single-user mode; other
users can be working in the database when the file is shrunk. You do not
have to run SQL Server in single-user mode to shrink the system databases.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OeBukJYiFHA.500@.TK2MSFTNGP09.phx.gbl...
> Can you point us to exactly where it stats that? A shrinkfile with or
> without Emptyfile will lock the page or extent as it moves it from the
> back of the file to the front or from file to file in the case of the
> empty file. It should not hold that lock unless you wrapped it in your own
> transaction.
> --
> Andrew J. Kelly SQL MVP
>
> "Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
> news:eLgLMZXiFHA.2644@.TK2MSFTNGP09.phx.gbl...
>

Does Shrinkfile Emptyfile block users?

I read BOL says this can be done online but will it generate blocks.
Is it done in small transactions?
Thanks
PaulCan you point us to exactly where it stats that? A shrinkfile with or
without Emptyfile will lock the page or extent as it moves it from the back
of the file to the front or from file to file in the case of the empty file.
It should not hold that lock unless you wrapped it in your own transaction.
Andrew J. Kelly SQL MVP
"Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
news:eLgLMZXiFHA.2644@.TK2MSFTNGP09.phx.gbl...
>I read BOL says this can be done online but will it generate blocks.
> Is it done in small transactions?
> Thanks
> Paul
>|||Hi Andrew
As follows. If it does it page at a time I should be OK.
Thanks
Paul
DBCC SHRINKFILE
Shrinks the size of the specified data file or log file for the related
database
.......
To remove any data that may be in a file, execute DBCC
SHRINKFILE('file_name', EMPTYFILE) before executing ALTER DATABASE.
The database being shrunk does not have to be in single-user mode; other
users can be working in the database when the file is shrunk. You do not
have to run SQL Server in single-user mode to shrink the system databases.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OeBukJYiFHA.500@.TK2MSFTNGP09.phx.gbl...
> Can you point us to exactly where it stats that? A shrinkfile with or
> without Emptyfile will lock the page or extent as it moves it from the
> back of the file to the front or from file to file in the case of the
> empty file. It should not hold that lock unless you wrapped it in your own
> transaction.
> --
> Andrew J. Kelly SQL MVP
>
> "Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
> news:eLgLMZXiFHA.2644@.TK2MSFTNGP09.phx.gbl...
>

Does Shrinkfile Emptyfile block users?

I read BOL says this can be done online but will it generate blocks.
Is it done in small transactions?
Thanks
PaulCan you point us to exactly where it stats that? A shrinkfile with or
without Emptyfile will lock the page or extent as it moves it from the back
of the file to the front or from file to file in the case of the empty file.
It should not hold that lock unless you wrapped it in your own transaction.
--
Andrew J. Kelly SQL MVP
"Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
news:eLgLMZXiFHA.2644@.TK2MSFTNGP09.phx.gbl...
>I read BOL says this can be done online but will it generate blocks.
> Is it done in small transactions?
> Thanks
> Paul
>|||Hi Andrew
As follows. If it does it page at a time I should be OK.
Thanks
Paul
DBCC SHRINKFILE
Shrinks the size of the specified data file or log file for the related
database
.......
To remove any data that may be in a file, execute DBCC
SHRINKFILE('file_name', EMPTYFILE) before executing ALTER DATABASE.
The database being shrunk does not have to be in single-user mode; other
users can be working in the database when the file is shrunk. You do not
have to run SQL Server in single-user mode to shrink the system databases.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OeBukJYiFHA.500@.TK2MSFTNGP09.phx.gbl...
> Can you point us to exactly where it stats that? A shrinkfile with or
> without Emptyfile will lock the page or extent as it moves it from the
> back of the file to the front or from file to file in the case of the
> empty file. It should not hold that lock unless you wrapped it in your own
> transaction.
> --
> Andrew J. Kelly SQL MVP
>
> "Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
> news:eLgLMZXiFHA.2644@.TK2MSFTNGP09.phx.gbl...
>>I read BOL says this can be done online but will it generate blocks.
>> Is it done in small transactions?
>> Thanks
>> Paul
>>
>sql

Does RS process sleep?

I tried both SQL2000 & SQL2005.
But both servers RS processes sleep if no users access to them over an hour.
Once it sleeps, the user has to wait 20 seconds to start responding the
request.
Is this normal? Is there anyway to prevent this waiting time?
Please help!!What I do is have a very simple report that auto-refreshes every 5 minutes.
That keeps it alive. There is also an IIS configuration that can be set but
I haven't used it and I don't remember it off the top of my head. The
auto-refresh is pretty easy hack though.
Instead of 5 minutes you could set it longer than than (30 minutes?).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Aki Nomura" <anomura@.jtb.com> wrote in message
news:%23TPmKK1%23FHA.3104@.TK2MSFTNGP15.phx.gbl...
>I tried both SQL2000 & SQL2005.
> But both servers RS processes sleep if no users access to them over an
> hour.
> Once it sleeps, the user has to wait 20 seconds to start responding the
> request.
> Is this normal? Is there anyway to prevent this waiting time?
> Please help!!
>
>|||Auto-refreshes means scheduling report?
Actually I tried an every 3 minutes schedule report.
But it didn't work for my case.
Anyway, I will try the IIS configuration.
Thanks.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OXDxAt6%23FHA.328@.TK2MSFTNGP14.phx.gbl...
> What I do is have a very simple report that auto-refreshes every 5
> minutes. That keeps it alive. There is also an IIS configuration that can
> be set but I haven't used it and I don't remember it off the top of my
> head. The auto-refresh is pretty easy hack though.
> Instead of 5 minutes you could set it longer than than (30 minutes?).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Aki Nomura" <anomura@.jtb.com> wrote in message
> news:%23TPmKK1%23FHA.3104@.TK2MSFTNGP15.phx.gbl...
>>I tried both SQL2000 & SQL2005.
>> But both servers RS processes sleep if no users access to them over an
>> hour.
>> Once it sleeps, the user has to wait 20 seconds to start responding the
>> request.
>> Is this normal? Is there anyway to prevent this waiting time?
>> Please help!!
>>
>|||Layout, report tab, autorefreshes checkbox. Once you have a report that
autorefreshes then open it and leave it up. This has worked for me both with
RS 2000 and RS 2005.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Aki Nomura" <anomura@.jtb.com> wrote in message
news:OzI$fDD$FHA.504@.TK2MSFTNGP09.phx.gbl...
> Auto-refreshes means scheduling report?
> Actually I tried an every 3 minutes schedule report.
> But it didn't work for my case.
> Anyway, I will try the IIS configuration.
> Thanks.
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OXDxAt6%23FHA.328@.TK2MSFTNGP14.phx.gbl...
>> What I do is have a very simple report that auto-refreshes every 5
>> minutes. That keeps it alive. There is also an IIS configuration that can
>> be set but I haven't used it and I don't remember it off the top of my
>> head. The auto-refresh is pretty easy hack though.
>> Instead of 5 minutes you could set it longer than than (30 minutes?).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Aki Nomura" <anomura@.jtb.com> wrote in message
>> news:%23TPmKK1%23FHA.3104@.TK2MSFTNGP15.phx.gbl...
>>I tried both SQL2000 & SQL2005.
>> But both servers RS processes sleep if no users access to them over an
>> hour.
>> Once it sleeps, the user has to wait 20 seconds to start responding the
>> request.
>> Is this normal? Is there anyway to prevent this waiting time?
>> Please help!!
>>
>>
>|||Yes, it works.
Thanks again.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:egpp%23KE$FHA.2036@.TK2MSFTNGP14.phx.gbl...
> Layout, report tab, autorefreshes checkbox. Once you have a report that
> autorefreshes then open it and leave it up. This has worked for me both
> with RS 2000 and RS 2005.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Aki Nomura" <anomura@.jtb.com> wrote in message
> news:OzI$fDD$FHA.504@.TK2MSFTNGP09.phx.gbl...
>> Auto-refreshes means scheduling report?
>> Actually I tried an every 3 minutes schedule report.
>> But it didn't work for my case.
>> Anyway, I will try the IIS configuration.
>> Thanks.
>>
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:OXDxAt6%23FHA.328@.TK2MSFTNGP14.phx.gbl...
>> What I do is have a very simple report that auto-refreshes every 5
>> minutes. That keeps it alive. There is also an IIS configuration that
>> can be set but I haven't used it and I don't remember it off the top of
>> my head. The auto-refresh is pretty easy hack though.
>> Instead of 5 minutes you could set it longer than than (30 minutes?).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Aki Nomura" <anomura@.jtb.com> wrote in message
>> news:%23TPmKK1%23FHA.3104@.TK2MSFTNGP15.phx.gbl...
>>I tried both SQL2000 & SQL2005.
>> But both servers RS processes sleep if no users access to them over an
>> hour.
>> Once it sleeps, the user has to wait 20 seconds to start responding the
>> request.
>> Is this normal? Is there anyway to prevent this waiting time?
>> Please help!!
>>
>>
>>
>|||That is called Application Pool in IIS. Go to IIS Manager, under Applocation
Pools node, right click "DefaultAppPool" in which the Reporting Server work
process is running, select properties. On "Performace" tag, you will see, by
default, the app pool will shut down if being idle for 20 min. You can
extend this time to 8x60min 480min, so that the app pool will not shut down
for a regular working day. However, the first report reader of the day, will
hit the delay. You may schedule a dummy report at beginning of a work day
for this.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OXDxAt6%23FHA.328@.TK2MSFTNGP14.phx.gbl...
> What I do is have a very simple report that auto-refreshes every 5
> minutes. That keeps it alive. There is also an IIS configuration that can
> be set but I haven't used it and I don't remember it off the top of my
> head. The auto-refresh is pretty easy hack though.
> Instead of 5 minutes you could set it longer than than (30 minutes?).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Aki Nomura" <anomura@.jtb.com> wrote in message
> news:%23TPmKK1%23FHA.3104@.TK2MSFTNGP15.phx.gbl...
>>I tried both SQL2000 & SQL2005.
>> But both servers RS processes sleep if no users access to them over an
>> hour.
>> Once it sleeps, the user has to wait 20 seconds to start responding the
>> request.
>> Is this normal? Is there anyway to prevent this waiting time?
>> Please help!!
>>
>|||Wow, This is what I was looking for.
Thank you.
"Norman Yuan" <NotReal@.NotReal.not> wrote in message
news:uSWtdkQ$FHA.532@.TK2MSFTNGP15.phx.gbl...
> That is called Application Pool in IIS. Go to IIS Manager, under
> Applocation Pools node, right click "DefaultAppPool" in which the
> Reporting Server work process is running, select properties. On
> "Performace" tag, you will see, by default, the app pool will shut down if
> being idle for 20 min. You can extend this time to 8x60min 480min, so that
> the app pool will not shut down for a regular working day. However, the
> first report reader of the day, will hit the delay. You may schedule a
> dummy report at beginning of a work day for this.
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OXDxAt6%23FHA.328@.TK2MSFTNGP14.phx.gbl...
>> What I do is have a very simple report that auto-refreshes every 5
>> minutes. That keeps it alive. There is also an IIS configuration that can
>> be set but I haven't used it and I don't remember it off the top of my
>> head. The auto-refresh is pretty easy hack though.
>> Instead of 5 minutes you could set it longer than than (30 minutes?).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Aki Nomura" <anomura@.jtb.com> wrote in message
>> news:%23TPmKK1%23FHA.3104@.TK2MSFTNGP15.phx.gbl...
>>I tried both SQL2000 & SQL2005.
>> But both servers RS processes sleep if no users access to them over an
>> hour.
>> Once it sleeps, the user has to wait 20 seconds to start responding the
>> request.
>> Is this normal? Is there anyway to prevent this waiting time?
>> Please help!!
>>
>>
>

Does offline database come back online when server is restarted?

If you take a database offline to prevent users from accessing it, and then restart the server, will the database be back online when the server starts up?

If so, is there an option to prevent this from happening so that a database taken offline will remain offline until it is manually put online again by an administrator?

The database state is kept in the master database, and is persistent across shutdowns as long as the database is not detached.

A database which is set OFFLINE will not come ONLINE until an administrator explicitly sets it back ONLINE.

Does anyone know of a web based SQL Query tool?

Hi

I want to be able t give my users access to our SQL server database via a eb application published o our web server. I have found one freeware tool but it only supports SQL Server 2000 and the developer doesn't returns any emails.

Has anyone come across a web ui SQL query tool either freeware or one that can be purhcase

thanks for your help

Marcus

Do you just want to have a tool sending out a query from a textbox and getting the results back in a grid or something ? Or do you want tome graphical designer for your users ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

search for

SQL Server Web Data Administrator

on microsoft website

|||

Here is the link to SQL Server Web Data Administrator

http://www.microsoft.com/downloads/details.aspx?FamilyID=C039A798-C57A-419E-ACBC-2A332CB7F959&displaylang=en

Sethu Srinivasan, Software Design Engineer, SQL Server Manageability
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.

|||ASP Enterprise Manager http://sourceforge.net/projects/asp-ent-man/

Does anonymous access have to be disabled in IIS?

I want anonymous users to be able to see my reports. But it says here it
has to be disabled.
http://www.microsoft.com/products/ee/transform.aspx?EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings.resources.Strings&EvtID=rsAccessDenied&ProdName=Microsoft+SQL+Server+Reporting+Services&ProdVer=8.00
I'm getting an rsAccessDenied if I give it anon access.Ok, I got a solution, but is this ok?
I got anonymous access to work on the report server. I hope there's no
security issues with it. Reporting services won't allow anonymous access,
so I set in IIS ReportServices Properties->Directory Security-> anonymous
access->edit-> anonymous access->edit
And Used my username Domain/CLee and my password. Also Integrated Windows
Security needs to be checked too.
Then, anyone can access the report server.
"Cindy Lee" <cindylee@.hotmail.com> wrote in message
news:e3PvN3vfEHA.644@.tk2msftngp13.phx.gbl...
> I want anonymous users to be able to see my reports. But it says here it
> has to be disabled.
>
http://www.microsoft.com/products/ee/transform.aspx?EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings.resources.Strings&EvtID=rsAccessDenied&ProdName=Microsoft+SQL+Server+Reporting+Services&ProdVer=8.00
> I'm getting an rsAccessDenied if I give it anon access.
>|||If you wanted to set up a totally open site, running as your domain account,
then you have succeeded.
A better way of allowing anonymous access is to
1. give the IUSR_computername the required permissions (for ex. browser, or
content manager) in the report server - use Home | Properties | Security and
Site Settings | Security
2. enable anonymous access, and leave the account as IUSR_machine... or some
equally unpermissioned account.
--
Tudor Trufinescu
Dev Lead
Sql Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cindy Lee" <cindylee@.hotmail.com> wrote in message
news:Osp9VLwfEHA.636@.TK2MSFTNGP12.phx.gbl...
> Ok, I got a solution, but is this ok?
>
> I got anonymous access to work on the report server. I hope there's no
> security issues with it. Reporting services won't allow anonymous access,
> so I set in IIS ReportServices Properties->Directory Security-> anonymous
> access->edit-> anonymous access->edit
> And Used my username Domain/CLee and my password. Also Integrated Windows
> Security needs to be checked too.
> Then, anyone can access the report server.
>
>
> "Cindy Lee" <cindylee@.hotmail.com> wrote in message
> news:e3PvN3vfEHA.644@.tk2msftngp13.phx.gbl...
> > I want anonymous users to be able to see my reports. But it says here
it
> > has to be disabled.
> >
>
http://www.microsoft.com/products/ee/transform.aspx?EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings.resources.Strings&EvtID=rsAccessDenied&ProdName=Microsoft+SQL+Server+Reporting+Services&ProdVer=8.00
> >
> > I'm getting an rsAccessDenied if I give it anon access.
> >
> >
>

Document Map not displaying for some users.

I have noticed that with some users the document map pane is empty,
whereas on my machine and others it displays as it should. Has anyone
seen this behavior before and how do you think I could resolve this?Ok, I figured it out.
With reporting services 2000, the document maps do not appear
correctly with users running IE7. There is a hotfix you can download
from Microsoft's site to resolve this.
On Apr 10, 10:08=A0am, Just Another Reporter <Crystal.War...@.gmail.com>
wrote:
> I have noticed that with some users the document map pane is empty,
> whereas on my machine and others it displays as it should. =A0Has anyone
> seen this behavior before and how do you think I could resolve this?

Document map icon disappears!

Has anyone experienced this? One of our users cannot see the Document Map
icon on any report he views.
Clearly this is an issue with his browser or O/S or how his system is
configured, as everyone else can see the icon (and the map!).
Any pointers to what might be causing this greatly appreciated.
ThanksIt would appear to be the browser version. Upgraded to v6 of IE and problem
solved! :)
"Hyper" wrote:
> Has anyone experienced this? One of our users cannot see the Document Map
> icon on any report he views.
> Clearly this is an issue with his browser or O/S or how his system is
> configured, as everyone else can see the icon (and the map!).
> Any pointers to what might be causing this greatly appreciated.
> Thanks

Document Map doesnt display in IE7

Can anyone help,

I have created a report which works great when the users run from IE6, but when anyone runs report from IE7, the report displays ok, but the document appears to build but displays absolutely nothing.

Has anyone seen this problem with IE7 using Reporting Services 2000?

Thanks people

See http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=971203&SiteID=1.