MS SQL Views

Is there any kind of lock on a view in ms sql database?
what happens if one of my users is looking at the view and another one is adding something to the database?
Thanks.The view itself doesn't create any locks, although using the view has the potential to create locks. A view in SQL Server is simply a way to "can" a SELECT statement (with a few restrictions), so the view itself doesn't do any locking. Using the view is subject to exactly the same rules that using the SELECT statements that the view represents, and those rules are applied in exactly the same way regardless of whether you use the view or the SELECT statement.

-PatP|||Well-well-well, the usage of the view via SELECT/INSERT/UPDATE/DELETE as well as when referenced in a JOIN, DOES cause additional TAB lock to be created in syslocks. And if everything was "exactly" the same while comparing SELECT and a view, - then why did "they" come up with such a silly concept? We could just do SELECT ... FROM (SELECT ... FROM (SELECT ... FROM (SELECT ... FROM tbl) a) b) c

Instead, the usage of views (while dangerous depending who's in control) provides a mechanism of abstracting data and data access settings that equally affect the view participants.

To answer the question you need to know what isolation level is being applied while SELECTing/INSERTing from/to the view. SELECT usually produces a TAB and a PAG lock, but may escalate to EXT, while INSERT results in a PAG lock that is not yet visible to the previously invoked SELECT.|||If you want to prevent the view from escalating the lock on the table, you could always try creating another incompatible lock on the table with another spid so that it is never escalated into a table lock. It will run slower, but your select should still run. You can always watch lock escalations with the query profiler, filter the object ID and know exactly when and what is causing the escalation. When you know that, then you know what you need to tune.

Cheers,
-Kilka

Ms Sql Server Giving Timeout Expired

We are using MS SQL 2000 it is giving timeout expired proble Intermittently and the users getting slow response in the Transactions.
Need Help to solve the same.The ConnectionTimeout property of the Connection object, should be increased as much as is needed (applicable to VB6, .Net). I think that any languagae has a similar property.|||Take also in account this method .|||Hi,

In some servers, if the Select statement is run with out specifying WITH (NOLOCK) then it will throw timeout expired.

Suppose you run the following query from your application

Select * from emp;

then it will throw time out expired

Please use

Select * from emp WITH (NOLOCK);

then it wont expire. I am not sure of which server setting that causes this problem.
The application will be slow if some one is trying to run a select statement without using "WITH (NOLOCK)"

Try to avoid users from executing the Select statement, which poses this problem.

Also if many Users are accessing the DB, then we should give some time for the DB to process it. If many processes queues up, then finally some will expire.This is purely due to the load on the DB.

Also monitor the performance of DB, some stored procedures that we write will eat up all DB resources because of which the application will hang up until that is executed.

It may also be due to increase in number of users using the application.

Hope this will be useful.|||Ye, good observation, but I think that it's not always recommended to use NOLOCK, and for any Select. Eventually for special queries where it's expected to take a long time.

MS SQL Server 2005, performance; vs InterBase

Hi,
1/
I would like to ask if SQL Server 2005 Express could be used as a web
database as far as performance (higher number of simultaneous users) is
concerned.
2/
I would like to see the comparison between InterBase from Borland and SQL
Server 2005 Express. Does exist such comparison somewhere on Internet?
3/
Can be MS SQL Server 2005 Express installed on MS Windows Server 2003 Web
Edition? (MS SQL Server 2005 cannot be installed).
Thank you,
Lubomir
Hi
1. Yes, once it is released. The Beta EULA agreement does not allow for use
in production. Look at the scalability limitations on
http://www.microsoft.com/sql/express/
2. As SQL Server 2005 is in Beta, doing benchmarking will not show the true
performance of the released product. It is also a violation of EULA.
3. No. It is a Windows 2003 Web Edition licensing limitation where no
applications may be installed on the machine unless they are used inside IIS.
(http://www.microsoft.com/windowsserv...rview/web.mspx)
Regards
Mike
"Lubomir" wrote:

> Hi,
> 1/
> I would like to ask if SQL Server 2005 Express could be used as a web
> database as far as performance (higher number of simultaneous users) is
> concerned.
> 2/
> I would like to see the comparison between InterBase from Borland and SQL
> Server 2005 Express. Does exist such comparison somewhere on Internet?
> 3/
> Can be MS SQL Server 2005 Express installed on MS Windows Server 2003 Web
> Edition? (MS SQL Server 2005 cannot be installed).
>
> Thank you,
> Lubomir
>

MS SQL Server 2005 - How to add databases/users/etc.??

I've installed the MS VB Express and WebDev Express and subsequently have MS
SQL Server 2005 installed. I'm at a complete loss as to how to access or
configure this server. In the SQL Server configuration I can only see how to
configure the SQL Express protocols.
How can I add users to the Server? How do I create databases?
At the moment I'm trying to configure Macromedia Dreamweaver to access MSSQL
server.
Any help is appreciated!Get: SQL Server Express Management Studio
Search for this file on the internet: SQLServer2005_SSMSEE.msi
Milan
P.S. Still not the release version.
"Noozer" <dont.spam@.me.here> wrote in message
news:_psUf.174516$H%4.135595@.pd7tw2no...
> I've installed the MS VB Express and WebDev Express and subsequently have
> MS SQL Server 2005 installed. I'm at a complete loss as to how to access
> or configure this server. In the SQL Server configuration I can only see
> how to configure the SQL Express protocols.
> How can I add users to the Server? How do I create databases?
> At the moment I'm trying to configure Macromedia Dreamweaver to access
> MSSQL server.
> Any help is appreciated!
>|||Hi
As I understood you have installed SQL Server Express Edition.
It is supplied without SSMS (Former EM). You will to download SSMS as msi
file if I remeber and install it at your workstation
"Noozer" <dont.spam@.me.here> wrote in message
news:_psUf.174516$H%4.135595@.pd7tw2no...
> I've installed the MS VB Express and WebDev Express and subsequently have
> MS SQL Server 2005 installed. I'm at a complete loss as to how to access
> or configure this server. In the SQL Server configuration I can only see
> how to configure the SQL Express protocols.
> How can I add users to the Server? How do I create databases?
> At the moment I'm trying to configure Macromedia Dreamweaver to access
> MSSQL server.
> Any help is appreciated!
>|||Thanks guys!
Just what I was looking for.
I was hoping that something like this would be built into Dreamweaver, but I
guess not.
"Milan Kosanovic" <milan@.REMOVETHISsoftlakecity.com> wrote in message
news:u8iYQFlTGHA.1672@.tk2msftngp13.phx.gbl...
> Get: SQL Server Express Management Studio
> Search for this file on the internet: SQLServer2005_SSMSEE.msi
> Milan
> P.S. Still not the release version.
> "Noozer" <dont.spam@.me.here> wrote in message
> news:_psUf.174516$H%4.135595@.pd7tw2no...
>sql

MS SQL Server 2005 - "Copy Database" from v8 to v9

Hi dear devs, admins or users!

Two weeks ago I had a problem with transfering databases from v8 to v9, I placed the question here (original message attached), but I received not the answer which I need. I try to transfer databases with the CDW ("Copy Database Wizard"), which should run through 5 action steps. The process stops after the 'action 2', the step "Create Package" failed allways. The error message which I receive is "Library not registered". I was very surprised, because two weeks ago the process stoped after the action 4 (4/5) while the step "Execute SQL Server Agent Job". I changed nothing on my system or databases. The only thing I can remember is the "Microsoft Update" downloaded and installed a new patch, but it wasn't a SQL Server 2005 update or something with databases.

I have now two questions. What means the error "Library not registered" while the CDW process and what could be the reason for the error in 'action 5' "Execute SQL Server Agent Job" while the CDW process? **(CDW = "Copy Database Wizard")

[original message from 2006-10-17]

I have following problem with the "SQL Server 2005 Developer Edition". I am in a small network and try to copy a database from a version 8.0 to a version 9.0 "SQL Server" (2 computers). I use the "Copy Database Wizard" and the process did 4 from 5 actions correctly, the last action with the name "Execute SQL Server Agent Job" fails allways. My "SQL Server Agent" process is started and runs well. In real I don't need the "SQL Server Agent" in any way in my database, so I don't understand why this process generates this error. I try to copy this old "Portal" "ASP.NET 1.1" database to my new "SQL Server 2005". Can someone help me?

Thanks

Arzu Bazman · ?US DEV United Systems?

Here is the thread we know SQL Server so we gave you two ways that works, Copy database wizard is one of those wizards it is there but most experienced people don't use it. I think it is failing because it cannot convert TDS(tabular data stream) 80 to 90 2000 to 2005, the problem is you don't need TDS to move a database. Detach and Attach and Backup and Restore wizard don't have the problem.

http://forums.asp.net/thread/1436904.aspx

|||

Hi!

Thank you very much for the answer(s). I remember very well that you answered my first answer yet, but I am someone who wants know things very exactly and when something doesn't work, I want allways know the reason. I thought my SQL Server 2005 is not configured well or something is missing, and thats why I asked a second time about the "Copy Database Wizard". Now that you told me that some problems exist with the CDW, I will try the Backup solution for the transfer from v8 to v9 of course. I am sorry, that I didn't that immediately after your first answer (2 weeks ago), but like I said I thought I configured something wrong and wanted know it very exactly.

Thanks

Arzu Bazman · ?US DEV United Systems?