ALTER DATABASE WITH ROLLBACK times out

If I execute the command ALTER DATASE SET MULTI_USER WITH ROLLBACK IMMEDIATE and there are any connections to the database, the command fails with a "Lock request time out period exceeded." message. If I use SET RESTRICTED_USER, the command succeeds with the following message: "Nonqualified transactions are being rolled back. Estimated rollback completion: 100%." This seems to be a bug.

What's even more annoying is that in SQL 2005 I could set MULTI_USER (using sp_dboption) even if there were active connections.I meant to say that in SQL 2000, the SET MULTI_USER option worked even if there were connections.|||I am experiencing the same issue and this causes major problems/delays with our batch cycle. Is thete any way to take a DB out of RESTRICTED mode without terminating connections like SQL 2000 did?|||

Dug around our bug database and found this is a known issue with SQL 2005. According to bug report the workaround is:

ALTER DATABASE pubs SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE pubs SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO

Have not tested this.

|||

Yes, the workaround works. It creates a slight window where people who should be able to connect to the database can't, but on the scale of incompatibilities introduced by SQL 2005, it's pretty minor.

A better question is why I can't go from restricted to multi_user without disconnecting people. OK, I know the answer - it was easier to code - but it really is a loss of functionality from earlier versions. Another quibble is that it really should be called WITH DISCONNECTION_IMMEDIATE, since that's what it really does.

ALTER DATABASE WITH ROLLBACK times out

If I execute the command ALTER DATASE SET MULTI_USER WITH ROLLBACK IMMEDIATE and there are any connections to the database, the command fails with a "Lock request time out period exceeded." message. If I use SET RESTRICTED_USER, the command succeeds with the following message: "Nonqualified transactions are being rolled back. Estimated rollback completion: 100%." This seems to be a bug.

What's even more annoying is that in SQL 2005 I could set MULTI_USER (using sp_dboption) even if there were active connections.I meant to say that in SQL 2000, the SET MULTI_USER option worked even if there were connections.|||I am experiencing the same issue and this causes major problems/delays with our batch cycle. Is thete any way to take a DB out of RESTRICTED mode without terminating connections like SQL 2000 did?|||

Dug around our bug database and found this is a known issue with SQL 2005. According to bug report the workaround is:

ALTER DATABASE pubs SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE pubs SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO

Have not tested this.

|||

Yes, the workaround works. It creates a slight window where people who should be able to connect to the database can't, but on the scale of incompatibilities introduced by SQL 2005, it's pretty minor.

A better question is why I can't go from restricted to multi_user without disconnecting people. OK, I know the answer - it was easier to code - but it really is a loss of functionality from earlier versions. Another quibble is that it really should be called WITH DISCONNECTION_IMMEDIATE, since that's what it really does.

ALTER DATABASE WITH ROLLBACK times out

If I execute the command ALTER DATASE SET MULTI_USER WITH ROLLBACK IMMEDIATE and there are any connections to the database, the command fails with a "Lock request time out period exceeded." message. If I use SET RESTRICTED_USER, the command succeeds with the following message: "Nonqualified transactions are being rolled back. Estimated rollback completion: 100%." This seems to be a bug.

What's even more annoying is that in SQL 2005 I could set MULTI_USER (using sp_dboption) even if there were active connections.I meant to say that in SQL 2000, the SET MULTI_USER option worked even if there were connections.|||I am experiencing the same issue and this causes major problems/delays with our batch cycle. Is thete any way to take a DB out of RESTRICTED mode without terminating connections like SQL 2000 did?|||

Dug around our bug database and found this is a known issue with SQL 2005. According to bug report the workaround is:

ALTER DATABASE pubs SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE pubs SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO

Have not tested this.

|||

Yes, the workaround works. It creates a slight window where people who should be able to connect to the database can't, but on the scale of incompatibilities introduced by SQL 2005, it's pretty minor.

A better question is why I can't go from restricted to multi_user without disconnecting people. OK, I know the answer - it was easier to code - but it really is a loss of functionality from earlier versions. Another quibble is that it really should be called WITH DISCONNECTION_IMMEDIATE, since that's what it really does.

Allowing secure connections to SQL Server 2000 through a firewall

Hello,

My question is about allowing and securing connections to SQL Server 2000 over the internet. The company that I work for has an application server that several of our clients connect to via the internet using secure .NET remoting. Basically, the clients have a desktop application that they run that creates a remoting connection to our server software and we handle the server/database part. Anyway, one of our clients now wants to use Crystal Reports to run ad hoc queries on their data that is hosted on our SQL 2000 database server behind our firewall. Obviously, opening up a port in our firewall and allowing someone to run ad hoc queries on the database makes us all more than a little nervous about security.

Has anyone else here had to deal with this sort of situation before? We'd like to set up a secure, encrypted connection for this one client, but still keep it locked down for everyone else. Is it as simple as enabling encryption and generating SSL certificates for the client machine and our server? I've only been able to find a few resources that help with bits and pieces of the problem, never anything tackling the issue as a whole. If anyone has any thoughts, experiences, links, etc. to share it would be greatly appreciated. We are a small company and no one here has experience with this sort of thing.

Cheers!
Justin

Hi Lovero,

You could setup your SQL Server to doesn't use default port (1433). You will use custom port (10030) for example or another.

Good coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

|||Yes, we certainly plan to change the default port on SQL Server in addition to any other steps we take. Actually, since there will be port forwarding from the firewall, I'm not sure that it's a required step, but probably a good idea. I'm much less sure about how to set up the whole security/encryption framework.|||

Yes, It is good idea :)

Good Coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

Allowing remote connections to Sql Server from command line

Hi,

Sql Server doesn't allow remote connections by default. I'm looking for a way to enable remote connections without using the UI. Ideally, I would like a command line tool or a script, though writing a C# tool would be better than nothing.

The way to do this though the UI is to go to the Surface Area Configuration > Services and Connections > MSSQLSERVER > Database Engine > Remote Connections and select "Local and remote connections" and "Using TCP/IP only".

Does anyone know how to do this programmatically?

Thanks,

Ann

Yes, check out the SAC utility:

http://msdn2.microsoft.com/en-us/library/ms162800(SQL.90).aspx

Paul A. Mestemaker II

Program Manager

Microsoft SQL Server

http://blogs.msdn.com/sqlrem/

Allowing remote connections during setup?

Is there a way to automatically setup SQL Server Express to allow remote connections during the installation process?

We are deploying SQL Server Express with our application and I really can't ask SMB customers to go through a series of rather complicated steps after our "turn key" setup installs everything in order for any of the other computers in their office to connect.

What's the reasoning behind that, anyway? Why create a database server setup which by default doesn't allow anyone except the server to access it? I guess that makes sense for ASP.NET but the web fad isn't the only platform developers use these days.

Any assistance would be greatly appreciated.

Cheers,

Evan

Evan, have you played around with the DISABLENETWORKPROTOCOLS switch? The default for Express is TCP=Off (1). This is the snippet from BOL.

;--
; The DISABLENETWORKPROTOCOLS switch is used to disable network protocol for SQL Server instance.
; Set DISABLENETWORKPROTOCOLS = 0; for Shared Memory= On, Named Pipe= On, TCP= On
; Set DISABLENETWORKPROTOCOLS = 1; for Shared Memory= On, Named Pipe= Off (Local Only), TCP= Off
; Set DISABLENETWORKPROTOCOLS = 2; for Shared Memory= On, Named Pipe= Off (Local Only), TCP= On

; Note: DISABLENETWORKPROTOCOLS if not specified has the following defaults.
; Default value for SQL Server Express/Evaluation/Developer: DISABLENETWORKPROTOCOLS =1
; Default value for Enterprise/Standard /Workgroup: DISABLENETWORKPROTOCOLS =2

Thanks,
Samuel Lester (MSFT)

Allowing Remote Connections

When I attempt to connect to our new SQLServer 2005 from my workstation running the latest XP Pro and SQL Server Mgmt Studio 2005, I get the following error. I checked the setup on the server, and TCP/IP connections are allowed. I connect to all our older SQL Server 2000 servers/databases just fine.

TITLE: Connect to Server

Cannot connect to <server>\<instance> (deleted).

ADDITIONAL INFORMATION:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476

BUTTONS:

OK

Terry,

I'm going to move this thread to the "Data Access" forum where I think you'll get a quicker response.

-Jeffrey

|||

Terry,

The error message shows that the sql browser is not able to be contacted by the client. So you need to (1) enble the sql browser service and start it. (2) make exception on your firewall setting to allow 1434 udp port.

Otherwise, you can specify port number of your sqlserver in your connection string. For example. tcp:<servername>,<serverport>

SQL Browswer is used to resolve connection paramenter for named instance. It is a seperate service from SQL Server.

|||

Terry,

I didn't do what the one reply did, but it gave me a clue on where to look.

SQL 2005 has a new SQL Server Configuration Manager under Configuration Tools. Click on that, after it opens go to SQL Server 2005 Services and verity that SQL Server Browser is running.

Now this is the change I had to make. Open SQL Server 2005 Network Configuation, and click on Protocols for <server>. You should just need to enable TCP/IP then restart SQL Server 2005 Service, if you like to you can enable the others if you use them here.

Hope this helps.

Randy

Allowing Remote Connections

When I attempt to connect to our new SQLServer 2005 from my workstation running the latest XP Pro and SQL Server Mgmt Studio 2005, I get the following error. I checked the setup on the server, and TCP/IP connections are allowed. I connect to all our older SQL Server 2000 servers/databases just fine.

TITLE: Connect to Server

Cannot connect to <server>\<instance> (deleted).

ADDITIONAL INFORMATION:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476

BUTTONS:

OK

Terry,

I'm going to move this thread to the "Data Access" forum where I think you'll get a quicker response.

-Jeffrey

|||

Terry,

The error message shows that the sql browser is not able to be contacted by the client. So you need to (1) enble the sql browser service and start it. (2) make exception on your firewall setting to allow 1434 udp port.

Otherwise, you can specify port number of your sqlserver in your connection string. For example. tcp:<servername>,<serverport>

SQL Browswer is used to resolve connection paramenter for named instance. It is a seperate service from SQL Server.

|||

Terry,

I didn't do what the one reply did, but it gave me a clue on where to look.

SQL 2005 has a new SQL Server Configuration Manager under Configuration Tools. Click on that, after it opens go to SQL Server 2005 Services and verity that SQL Server Browser is running.

Now this is the change I had to make. Open SQL Server 2005 Network Configuation, and click on Protocols for <server>. You should just need to enable TCP/IP then restart SQL Server 2005 Service, if you like to you can enable the others if you use them here.

Hope this helps.

Randy

Allowing Local and Remote connections breaks SQL queries

hello
I have a remote server in a datacentre that hosts our website
(www.sheffcare.co.uk) and we have an online backup service that tries
to backup out \inetpub\www and SQL files.
The thing is, that everytime the the backup runs, the website SQL
queries break, with the following errors:
Microsoft JET Database Engine error '800004005'
Operation must use an updateable query
/web_managers/calendarAmend.asp, line 88
Also logged in the System Even Log are:
1. Login failed for user 'administrator'
2. SSPI handshake failed with error code 0x8009030c while establishing
a connection with integrated security; the
connection has been closed
3. Login failed for user ''. The user is not associated with a trusted
SQL Server connection.
It seems the only way to get the website working again is to go into
SQL Server Suface Area Configuration application and change Remote
Connections from Local and Remote to Local only and restart the
SQLEXPRESS service.
But we need to allow Local and Remote Connections for the online
backup to work.
Could this be a problem with the code?
Thanks for any pointers.What backup program are you using?
T-SQL 'BACKUP DATABASE' won't do this. Idera LiteSpeed won't either.
Because you said "online backup service that tries to backup out
\inetpub\www and SQL files" I get the feeling you're doing some filesystem
based backup and not a database backup. I've never done that.
If I'm correct, then a simple and fast solution is to not backup the .mdf,
.ndf & .ldf files directly and create a maintenance plan - except that
maintenance plans are 2000 and I think you have 2005.
"cw1972" <cw1972@.gmail.com> wrote in message
news:1192439902.757094.12590@.e9g2000prf.googlegroups.com...
> hello
> I have a remote server in a datacentre that hosts our website
> (www.sheffcare.co.uk) and we have an online backup service that tries
> to backup out \inetpub\www and SQL files.
> The thing is, that everytime the the backup runs, the website SQL
> queries break, with the following errors:
> Microsoft JET Database Engine error '800004005'
> Operation must use an updateable query
> /web_managers/calendarAmend.asp, line 88
>
> Also logged in the System Even Log are:
> 1. Login failed for user 'administrator'
> 2. SSPI handshake failed with error code 0x8009030c while establishing
> a connection with integrated security; the
> connection has been closed
> 3. Login failed for user ''. The user is not associated with a trusted
> SQL Server connection.
> It seems the only way to get the website working again is to go into
> SQL Server Suface Area Configuration application and change Remote
> Connections from Local and Remote to Local only and restart the
> SQLEXPRESS service.
> But we need to allow Local and Remote Connections for the online
> backup to work.
> Could this be a problem with the code?
> Thanks for any pointers.
>|||On 15 Oct, 14:31, "Jay" <s...@.nospam.org> wrote:
> What backup program are you using?
it's an online backup prodcedure provided by these people -
http://www.databarracks.com/OnlineBackup/Technology/Software/SupportedApplications/
- they say SQL server is fully supported
> T-SQL 'BACKUP DATABASE' won't do this. Idera LiteSpeed won't either.
> Because you said "online backup service that tries to backup out
> \inetpub\www and SQL files" I get the feeling you're doing some filesystem
> based backup and not a database backup. I've never done that.
> If I'm correct, then a simple and fast solution is to not backup the .mdf,
> .ndf & .ldf files directly and create a maintenance plan - except that
> maintenance plans are 2000 and I think you have 2005.
>
I'm unsure what a maintenance plan is, I'll have to look into it - we
are using SQL Express 2005|||I am not familar with that backup program, however, I would suggest asking
their support.
As to the maintenance plans, SQL Server Express does not support maintenance
plans, or SQL agent. So, to automate anything, you have to use the Windows
scheduler and SQLCMD (see BOL).
What is it you are trying to backup? Just the database, or the database and
the website? I still have the feeling that you're trying to use one
application to back everything up. While I suppose that's possible, I don't
think it's a good idea.
Just to get yourself covered, in a query window, for each database you care
about (including master) run:
BACKUP DATABASE master TO DISK='X:\Backups\master.BAK' WITH INIT
(replacing the dbname and the path, making sure everything exists)
"cw1972" <cw1972@.gmail.com> wrote in message
news:1192458718.529399.35120@.e9g2000prf.googlegroups.com...
> On 15 Oct, 14:31, "Jay" <s...@.nospam.org> wrote:
>> What backup program are you using?
> it's an online backup prodcedure provided by these people -
> http://www.databarracks.com/OnlineBackup/Technology/Software/SupportedApplications/
> - they say SQL server is fully supported
>> T-SQL 'BACKUP DATABASE' won't do this. Idera LiteSpeed won't either.
>> Because you said "online backup service that tries to backup out
>> \inetpub\www and SQL files" I get the feeling you're doing some
>> filesystem
>> based backup and not a database backup. I've never done that.
>> If I'm correct, then a simple and fast solution is to not backup the
>> .mdf,
>> .ndf & .ldf files directly and create a maintenance plan - except that
>> maintenance plans are 2000 and I think you have 2005.
> I'm unsure what a maintenance plan is, I'll have to look into it - we
> are using SQL Express 2005
>

Allow remote connection (using TCP/IP) from SQL Ce

Hi all,

Need to know if its possible to allow remote connections and access a Sql Ce database using TCP/IP.

Thanks for your help

Hach

No, this is not possible out of the box. What are you trying to acheive? Sounds like SQL Express can help you - it allows remote connections over TCP/IP.|||Your app is the host, so there is no seperate process like bigger sql brothers that listen on an endpoint. You can provide your own listeners using various techs such as wcf, sockets, remoting, etc.

Allow access through network

Hi,

How can i make Query analyzer access SQL Server in a network, I've already allowed netowrk connections in enterprise manager(but locally) and none of the users could access what can it be ?

Thanks

Hi,

didi you enable a listerner for TCP/IP ? Enable this protocol and you will get access to the services of SQL Server.

-Jens Suessmeyer.

http://www.sqlserver2005.de

|||

It worked thanks

Thanks

all pooled connections were in use

I am getting an error like this

'Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool.

This may have occurred because all pooled connections were in use and max pool size was reached.'

What shuld i do

:( :(

you can try setting a new max number of connections for the pool through the connection string.

here is an example connection string:conn.ConnectionString = "integrated security=SSPI;SERVER=YOUR_SERVER;DATABASE=YOUR_DB_NAME;Min Pool Size=5;Max Pool Size=60;Connect Timeout=2;";

see how max pool size is increased? also, here is a link to an article that may help->

http://www.15seconds.com/issue/040830.htm

hope this is helpful -- jp

|||

Try to optimize your query to work faster but if it will not help just change command timeout property to bigger number default is 30 s if I remember correctly. How long takes your query to execute in Management studio?

Thanks

|||

suji,

you'll notice in the connection string that i wrote before that there is also a connection timeout value. you could also try increasing that rather than increasing the pool size if you wish...--jp

|||

Where should I add this? is it in web.config ?

Now i have the following line related to connectionStrings in web config

<connectionStrings>
<add name="myConnectionString" connectionString="Data Source=mssql2005...............,48645\sqlexpress;Initial Catalog=sp;uid=spdmin; pwd=1234567;Trusted_Connection=no" providerName="System.Data.SqlClient"/>
</connectionStrings>

hope u can help me

|||

suji,

you can use the connection string in your web.config oreven your code is you wish. The connection string that i wrotebefore is simply the string itself. at minimum you need thatstring for the code that calls the timeout that you had before. --jp