Lock_Timeout - best practice?

Hi,
I'm occosianally having my main shop floor users 'hang'
for 30+ seconds and I can see that my app is timing out
somewhere SQL Server when it fires a new record insert
transaction that, in turn, fires a trigger which calls a
number of procs. This problem only happens two or three
times per day, for no particular reason and some days no
problems at all. All the the sql code in the chain of
events set Lock_Timeout to 200ms.
I would like to ask three questions:
1) Is it generally better to shorten a Lock_Timeout so as
to not hang around too long and restart the trans quickly
or extend it in the hope the trans will eventually fire
and therefore not suffer the workload caused by a Rollback.
This seems like a classic 'it depends' question - but are
there any general rules here?
2) I've always presumed that Lock_Timeouts are used each
time SQLServer wants to aquire a lock, ie if a proc needs
to aquire 10 locks then each attempt has its own
Lock_Timeout - is this correct?
3) Lock_Timeouts in no way set the overall time limit for
executing a complete transaction - is this correct?
TIA - PeterOn Wed, 23 Jul 2003 16:33:43 -0700, "Peter Jones"
<jonespm@.ozemail.com.au> wrote:
>I'm occosianally having my main shop floor users 'hang'
>for 30+ seconds and I can see that my app is timing out
>somewhere SQL Server when it fires a new record insert
>transaction that, in turn, fires a trigger which calls a
>number of procs. This problem only happens two or three
>times per day, for no particular reason and some days no
>problems at all. All the the sql code in the chain of
>events set Lock_Timeout to 200ms.
>I would like to ask three questions:
>1) Is it generally better to shorten a Lock_Timeout so as
>to not hang around too long and restart the trans quickly
>or extend it in the hope the trans will eventually fire
>and therefore not suffer the workload caused by a Rollback.
Shorten it if you want to pop up a message to the user, otherwise you
might even want to lengthen it, if you know that you get these
infrequent 30+ second hangs which are correct functioning.
>This seems like a classic 'it depends' question - but are
>there any general rules here?
>2) I've always presumed that Lock_Timeouts are used each
>time SQLServer wants to aquire a lock, ie if a proc needs
>to aquire 10 locks then each attempt has its own
>Lock_Timeout - is this correct?
AFAIK.
>3) Lock_Timeouts in no way set the overall time limit for
>executing a complete transaction - is this correct?
Yes that is correct. The only limit I know on transaction times is
connection timeout, and I'm not even certain how those interact.
Joshua Stern|||<snip>
> 2) I've always presumed that Lock_Timeouts are used each
> time SQLServer wants to aquire a lock, ie if a proc needs
> to aquire 10 locks then each attempt has its own
> Lock_Timeout - is this correct?
Yes, but the lock_timeout of the 10th lock is not relevant, because the
1st lock is always the first to time out.
> 3) Lock_Timeouts in no way set the overall time limit for
> executing a complete transaction - is this correct?
It depends on the lock type. In default isolation transaction level,
Shared locks can be released immediately after the Select statement is
finished. However, exclusive locks are essential for the transaction,
and will be held until the end of the transaction.
For example, if you have the following transaction:
BEGIN TRANSACTION
UPDATE MyTable1 SET Col1 = 1
UPDATE MyTable2 SET Col2 = 2
COMMIT TRANSACTION
Then the exclusive locks on MyTable1 will be held until the transaction
is committed. If the lock_timeout is set to 10 seconds, then the
transaction will fail if the total time of the two Updates exceeds these
10 seconds.
Hope this helps,
Gert-Jan|||Hi Gert-Jan,
Yes - this and Joshua's reply are very helpful. But they
raise a couple of issues I would like to clarrify:
2) Using your example in point 3 I would have expected the
Lock_Timeout value to be used when updating T1 then
another, independent Lock_Timeout, to be used it tries to
update T2. Your response to point 2 indicates that this is
not true - am I understanding you correctly?
3) Regarding point 3, this relates to point 2 I guess in
that it is completely contary to what I understood about
Lock_Timeouts. Your response seems to say that a
Lock_Timeout is the time SQL Server will 'hold' a lock
whereas it was my understanding it is how long it
will 'wait' for a blocked resourse to become unblocked.
Please clarrify that I have understood your response
correctly.
Cheers, Peter
>--Original Message--
><snip>
>> 2) I've always presumed that Lock_Timeouts are used each
>> time SQLServer wants to aquire a lock, ie if a proc
needs
>> to aquire 10 locks then each attempt has its own
>> Lock_Timeout - is this correct?
>Yes, but the lock_timeout of the 10th lock is not
relevant, because the
>1st lock is always the first to time out.
>> 3) Lock_Timeouts in no way set the overall time limit
for
>> executing a complete transaction - is this correct?
>It depends on the lock type. In default isolation
transaction level,
>Shared locks can be released immediately after the Select
statement is
>finished. However, exclusive locks are essential for the
transaction,
>and will be held until the end of the transaction.
>For example, if you have the following transaction:
>BEGIN TRANSACTION
>UPDATE MyTable1 SET Col1 = 1
>UPDATE MyTable2 SET Col2 = 2
>COMMIT TRANSACTION
>Then the exclusive locks on MyTable1 will be held until
the transaction
>is committed. If the lock_timeout is set to 10 seconds,
then the
>transaction will fail if the total time of the two
Updates exceeds these
>10 seconds.
>Hope this helps,
>Gert-Jan
>.
>|||Peter,
I have to appologize. It seems you are correct. The lock_timeout value
is only used when acquiring locks, and not for holding the locks. I
verified this with a simple test.
So if we go back to your original question 2:
>> 2) I've always presumed that Lock_Timeouts are used each
>> time SQLServer wants to aquire a lock, ie if a proc needs
>> to aquire 10 locks then each attempt has its own
>> Lock_Timeout - is this correct?
I tested this. I made a transaction that runs for approximately 25
seconds if there are no lock waits. Then - with another connection - I
locked a relevant row for 50 seconds. When I ran the transaction again
with a lock_timeout ot 51000 it completed successfully in 56 seconds.
IMO this proves that the lock_timeout is set for each individual lock
acquisition. (Otherwise, the transaction could not have finished in > 51
seconds).
Gert-Jan

lock timeout

Hello All-
I've been troubleshooting deadlocks in a third-party app. I ran a trace with the Lock:Deadlock, Lock:Timeout, RPC:Starting, SQL Batch:Starting, and all Error and Warning events. I then imported the output files into a table for analysis. My results show 90,000+ Lock:Timeout events and 20 Lock:Deadlock events for a 5 hour period! The vast majority of the Lock:Timeout events are for index or table resources. The server is running SQL2K, sp3a.
Here's my questions:
-Shouldn't I be seeing corresponding Attention or Exception events for the Lock:Timeouts? I thought SQL Server returned an error 1229 to the client and terminated the statement on a lock timeout. Would I not see this in the trace? Do I really have 90,000+ statements being terminated?
-a small number of the database ids in the Lock:Timeout events are large ids not included in sysdatabases. Where are these coming from? A bug?
-My developers swear that they are not setting a lock_timeout on their connections. Nor can I find a SET LOCK_TIMEOUT statement in the trace output. So why am I getting all these timeouts? I thought SQL Server by default waited indefinitely on a lock.
I confused! Any insight would be greatly appreciated.
thanks-
MargaretUnfortunately that counter is pretty much useless since it includes internal
lightweight timeouts that are normal and always present. You pretty much
have to forget about the majority of them as normal.
--
Andrew J. Kelly SQL MVP
"Margaret" <anonymous@.discussions.microsoft.com> wrote in message
news:57AEA811-A059-4426-B023-5FD611C7D330@.microsoft.com...
> Hello All-
> I've been troubleshooting deadlocks in a third-party app. I ran a trace
with the Lock:Deadlock, Lock:Timeout, RPC:Starting, SQL Batch:Starting, and
all Error and Warning events. I then imported the output files into a table
for analysis. My results show 90,000+ Lock:Timeout events and 20
Lock:Deadlock events for a 5 hour period! The vast majority of the
Lock:Timeout events are for index or table resources. The server is running
SQL2K, sp3a.
> Here's my questions:
> -Shouldn't I be seeing corresponding Attention or Exception events for the
Lock:Timeouts? I thought SQL Server returned an error 1229 to the client
and terminated the statement on a lock timeout. Would I not see this in the
trace? Do I really have 90,000+ statements being terminated?
> -a small number of the database ids in the Lock:Timeout events are large
ids not included in sysdatabases. Where are these coming from? A bug?
> -My developers swear that they are not setting a lock_timeout on their
connections. Nor can I find a SET LOCK_TIMEOUT statement in the trace
output. So why am I getting all these timeouts? I thought SQL Server by
default waited indefinitely on a lock.
> I confused! Any insight would be greatly appreciated.
> thanks-
> Margaret
>

Local VB.NET app connection to remote SQL server

I know this is strickly not a website question, but dunno where else to post...

To remotely admin and monitor some functions of the website, I wish to use a local application to connect to the MSSQL DB which is held on the remote webhosting server

I have the following code:

Dim StrSQLUNAs String ="[UN]"Dim StrSQLPWAs String ="[PW]"Dim StrServerAs String ="[IP]\[INSTANCE]"Dim StrDBAs String ="[DB]"Dim strTimeOutAs String ="Connection Timeout=0;"Dim pStrSQLConnAs String ="Server=" & StrServer &";Database=" & StrDB &";User Id=" & StrSQLUN &";Password=" & StrSQLPW &";" & strTimeOutDim sqlConnAs New SqlClient.SqlConnection(pStrSQLConn)If sqlConn.State = ConnectionState.ClosedThen sqlConn.Open()

This has basically been take from the existing code on the website, but changing to the server details. I had just started dev'ing this app when the admins decided to move the SQL server over to a different server. It was working on the old one, but the new one doesn't. It just times out after whatever time you put in the timeout variable. 0=unlimited, and so just sits there.

I am also using the MSSQL Server Management Studio locally to connect to the same database, and although slow, does connect after about a minute or so. I thought they would be using the same type of underlying connection to access the server and database? Is this correct?

Can they put restrictions in place for this specific sort of data access?

Does anyone have any suggestions on how to resolve this issue??

Thanks for any help

Adam.

does the new server allow external connections? A lot of hosts I've used in the past only allow you to connect to the SQL database from an Asp.Net app on their webservers.

|||

Yeah it does allow external access as I am using MSSQL Mgmt Studio.

Anyway I think I have found the cause of the problem - my Cisco router. I needed to put in IP Inspect rules in for the MSSQL. Thing that confuses me still is that I could use the Mgnt Studio and I thought it would be accessing it in the same way. Anyway - it still seems to work - albeit with random connection times (eg last night it was connecting in a second or to and now taking about 5mins!)

Anyway have experience in this area??

Thanks

Adam.

local SQL Server 2005 Express and Host with SQL Server 2000

I'm creating an app and have SQL Server 2005 Express installed. Most of the hosts now offering SQL Server 2005 are a bit pricey compared to those only offering 2000. Will I have any difficulty uploading my site and running the db on SQL 2000?
Thanks in advance.I've been searching these forums but all people having about the same question, all have no replies in the thread.

I'd love some feedback on this issue.
I'm quite new to asp.net.

I've got vs installed with sql 2005 express. but I also installed sql 2000 since my host is still using this one.
how can I port the db ? can I use the advanced controls (like login, membership, roles) if using a sql 2000 db ?

thank you|||ok I found some more replies in other threads.

basically I think my best approach should/could be:

-develop the website directly with ms sql 2000 as my database installed locally.
I guess the advanced features related to security, users, roles aren't configurable through the new admin panel of asp.net (related to the aspnetdb.* )
but If I understand correctly I could use sql 2005 for that and then run the tool inside the .NET 2.0 folder to port them into a mssql 2000 database. am I correct ?

any recommendations are welcome ;)

thanks|||

Because it depends on what you are doing. Yes, you can use sql 2000. Yes, you can move the data. No, sql 2000 can't do everything sql 2005 can, so if you write custom stored procedures, views, etc then you'll have to change them if they don't work on sql 2000.

The aspnetdb works fine on sql 2000, as does the sql providers that shipped with vs 2005. The problem is that it varies from hosting provider to hosting provider on how you get access to the sql box, and therefore you can't really give any instructions on "This is how you move your tables, views, stored procedures, indexes, triggers, and data easily".

Local mode in asp.net web app

Dear all,
Can i use local mode of reporting service in asp.net web application?yes, if you use VS2005 you can create and use RDLC reports based on your own
datasets.
no server required.
abd there is a webcontrol in the toolbox to render the report
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:2EBBBA15-EC80-417E-9755-071685810970@.microsoft.com...
> Dear all,
> Can i use local mode of reporting service in asp.net web application?

Local MDF Files--can I make this work?

Ok, so I'm upgrading a single user vb 6-Access app to a multi-user VS2005-Sq
l
Server app. The client would like to move to SQL Server yet we are not read
y
to do so yet. The problem is that I can not install anything (sql Server or
Sql Express) on these servers for now. Later on we will be able to. For no
w
I must use a local database. I do have access to use a file share.
We do have a copy of SQL Server 2005 to work with it just isn't on the
server. I have created a project and upgraded the database to Sql Server.
I'm wondering is there a way to use a disconnected mdf and access it on the
fileshare? Can I just place the .mdf file on a shared drive and create a
connection to that mdf? Or can it be done some other way?
The reason I am pushing towards this is I want to develop using the
System.data.sqlserver objects instead of the system.data.oledb object now so
I don't need to upgrade the code in the future? I am going to give it a try
but I would love some advise on this topic.
Thanks,Greg,
If you're asking whether you can move the Database then yes. You could
create the Database on your local machine. Then when SQL is installed
on your Server you could detach the DB, move the mdf & ldf files to the
Server and then attach them to the new SQL Installation on the Server.
You would just have to point to the new locations for the mdf & ldf
files.
Was that the question or have I missed the point completely? :-)
Barry|||Barry,
Unforutuanltly that wasn't the question. I do not have SQL Server intalled
on the Server. I want to create the database and just move the .mdf file to
the file share and access it with out having sql server insalled. I'm
guessing I can't do this and I must use an .mdb but I'm hoping...
http://msdn2.microsoft.com/en-us/library/ms233817.aspx
"Barry" wrote:

> Greg,
> If you're asking whether you can move the Database then yes. You could
> create the Database on your local machine. Then when SQL is installed
> on your Server you could detach the DB, move the mdf & ldf files to the
> Server and then attach them to the new SQL Installation on the Server.
> You would just have to point to the new locations for the mdf & ldf
> files.
> Was that the question or have I missed the point completely? :-)
> Barry
>|||What about installing SQL Express wiht my program and having it attach the
data base? Could I have multiple instances of SQL express attached to one
.mdf file (i'm looking at about 10 users)?
"Greg P" wrote:
> Barry,
> Unforutuanltly that wasn't the question. I do not have SQL Server intalle
d
> on the Server. I want to create the database and just move the .mdf file
to
> the file share and access it with out having sql server insalled. I'm
> guessing I can't do this and I must use an .mdb but I'm hoping...
> http://msdn2.microsoft.com/en-us/library/ms233817.aspx
>
> "Barry" wrote:
>|||I would not use fileshares for db access - it is not recommended. You can,
however, install sql express on local pc. That will attach to DB and other
clients can network into the instance. With local DB mode, a sql express
instance is created dynamically and locks the db file, so only that instance
can "use" the db.
William Stacey [MVP]
"Greg P" <gsp@.newsgroups.nospam> wrote in message
news:6734747C-FCA3-484E-B4A0-105BC2774272@.microsoft.com...
| What about installing SQL Express wiht my program and having it attach the
| data base? Could I have multiple instances of SQL express attached to one
| .mdf file (i'm looking at about 10 users)?
|
|
|
| "Greg P" wrote:
|
| > Barry,
| >
| > Unforutuanltly that wasn't the question. I do not have SQL Server
intalled
| > on the Server. I want to create the database and just move the .mdf
file to
| > the file share and access it with out having sql server insalled. I'm
| > guessing I can't do this and I must use an .mdb but I'm hoping...
| >
| > http://msdn2.microsoft.com/en-us/library/ms233817.aspx
| >
| >
| > "Barry" wrote:
| >
| > > Greg,
| > >
| > > If you're asking whether you can move the Database then yes. You
could
| > > create the Database on your local machine. Then when SQL is installed
| > > on your Server you could detach the DB, move the mdf & ldf files to
the
| > > Server and then attach them to the new SQL Installation on the Server.
| > > You would just have to point to the new locations for the mdf & ldf
| > > files.
| > >
| > > Was that the question or have I missed the point completely? :-)
| > >
| > > Barry
| > >
| > >|||you don't need multiple instance. 10 users can access the same database mdf
file on one instance of SQL Exp.
and if each user needs his own version, then
let them copy the mdf file to their machine, install their own sql express
and attach the db.
-Omnibuzz
--
Please post ddls and sample data for your queries and close the thread if
you got the answer for your question.
"Greg P" wrote:
> What about installing SQL Express wiht my program and having it attach the
> data base? Could I have multiple instances of SQL express attached to one
> .mdf file (i'm looking at about 10 users)?
>
> "Greg P" wrote:
>|||I think this answers my question, but I want to repeat what I'm understandin
g
to make sure, there are 3 points below.
1) If I were to install sql express on every client machine and attach the
database after install only one user could be attached to the database at on
e
time.
2) The only way to have multiple people use the program would be to copy the
database to the local computer, which would mean that updates would have to
be handled and the program wouldn't be real time. There would need to be a
good amount of code added that would be useless once SQL Server is up and
running.
3) I can have an access database that can be on the network and have
multiple users. This will lock certain tables but not the whole instance.
I think thats it. I need to develop using the system.data.oledb for now and
later on I can upgrade the code to the sql server objects if there a demand
for the better objects. The only thing I could throw into this scenario to
make it more complicated was I thought maybe I could create a XML object fro
m
the database and use that as my datasource? I still wouldn't be using the
sql server objects though I thought it was an intersting idea.
Let me know if this all seems right to yall.
Thanks again,
"William Stacey [MVP]" wrote:

> I would not use fileshares for db access - it is not recommended. You can
,
> however, install sql express on local pc. That will attach to DB and othe
r
> clients can network into the instance. With local DB mode, a sql express
> instance is created dynamically and locks the db file, so only that instan
ce
> can "use" the db.
> --
> William Stacey [MVP]
> "Greg P" <gsp@.newsgroups.nospam> wrote in message
> news:6734747C-FCA3-484E-B4A0-105BC2774272@.microsoft.com...
> | What about installing SQL Express wiht my program and having it attach t
he
> | data base? Could I have multiple instances of SQL express attached to o
ne
> | .mdf file (i'm looking at about 10 users)?
> |
> |
> |
> | "Greg P" wrote:
> |
> | > Barry,
> | >
> | > Unforutuanltly that wasn't the question. I do not have SQL Server
> intalled
> | > on the Server. I want to create the database and just move the .mdf
> file to
> | > the file share and access it with out having sql server insalled. I'm
> | > guessing I can't do this and I must use an .mdb but I'm hoping...
> | >
> | > http://msdn2.microsoft.com/en-us/library/ms233817.aspx
> | >
> | >
> | > "Barry" wrote:
> | >
> | > > Greg,
> | > >
> | > > If you're asking whether you can move the Database then yes. You
> could
> | > > create the Database on your local machine. Then when SQL is installe
d
> | > > on your Server you could detach the DB, move the mdf & ldf files to
> the
> | > > Server and then attach them to the new SQL Installation on the Serve
r.
> | > > You would just have to point to the new locations for the mdf & ldf
> | > > files.
> | > >
> | > > Was that the question or have I missed the point completely? :-)
> | > >
> | > > Barry
> | > >
> | > >
>
>|||You can have multiple users on SQL Express, all on the same database. If I
understand correctly you are limited to 10 users.
If you install SQL Server (or SQL Express) on one machine on the network
(server or PC) and have all users access the database on that machine, then
they will all be using the same data at the same time. I think this is what
you want?
If you install a database on every PC and have users access it locally, then
they will each have their own database and will not affect each other. I
don't think this is what you are looking for.
"Greg P" <gsp@.newsgroups.nospam> wrote in message
news:29BA7A55-C4B5-4DEF-B75E-84681E5028F7@.microsoft.com...
> I think this answers my question, but I want to repeat what I'm
understanding
> to make sure, there are 3 points below.
> 1) If I were to install sql express on every client machine and attach the
> database after install only one user could be attached to the database at
one
> time.
> 2) The only way to have multiple people use the program would be to copy
the
> database to the local computer, which would mean that updates would have
to
> be handled and the program wouldn't be real time. There would need to be
a
> good amount of code added that would be useless once SQL Server is up and
> running.
> 3) I can have an access database that can be on the network and have
> multiple users. This will lock certain tables but not the whole instance.
> I think thats it. I need to develop using the system.data.oledb for now
and
> later on I can upgrade the code to the sql server objects if there a
demand
> for the better objects. The only thing I could throw into this scenario
to
> make it more complicated was I thought maybe I could create a XML object
from
> the database and use that as my datasource? I still wouldn't be using the
> sql server objects though I thought it was an intersting idea.
> Let me know if this all seems right to yall.
> Thanks again,
> "William Stacey [MVP]" wrote:
>
can,
other
express
instance
the
one
I'm
installed
to
Server.
ldf|||I can't install sql server or sql express on the computer that has the file
share on it. I do not have permisions now. I will in the future, but I am
developing now. All i have to work with is a file share.
I can use access by placing it on the file share and have multiple users
access it as long as they have the access runtime on their computer, is ther
e
anyway to use sql express to do this same sort of configuration? Or is ther
e
another configuration using an mdf with outh having the software installed
that can support multiple users?
From what I understand the answer is no... but I thought it was a good
question to confirm on.
Thanx,
"Omnibuzz" wrote:
> you don't need multiple instance. 10 users can access the same database md
f
> file on one instance of SQL Exp.
> and if each user needs his own version, then
> let them copy the mdf file to their machine, install their own sql express
> and attach the db.
>
> -Omnibuzz
> --
> Please post ddls and sample data for your queries and close the thread if
> you got the answer for your question.
>
> "Greg P" wrote:
>|||In short no. You can't use the SQL data files directly as you are
trying to do.

Local connection to SQL Server 2005 Express fails

I just installed SQL Server 2005 Express edition, and created an app in
Visual Studio using C#. I get the following error when trying to connect:
"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: Named Pipes Provider, error: 40 - Could not open a connection to
SQL Server)"
I enabled Named Pipes but left TCP/IP disabled because this is a localhost
connection on the same computer.
Here is the portion of the code I am using to connect:
string dbCString = "Persist Security Info=False;Integrated
Security=true;Trusted_Connection=true;Initial
Catalog=MxData;server=(local)";
SqlConnection ThisConnection = new SqlConnection(dbCString);
ThisConnection.Open();
Thanks in advance for any help.
Perhaps these will help:
Configuration -Configure SQL Server 2005 to allow remote connections
http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277
Configuration -Connect to SQL Express from "downlevel clients"
http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"TAC" <tac@.noemail.com> wrote in message
news:GsCdnUMH0OMdb_DYnZ2dnUVZ_qOdnZ2d@.giganews.com ...
>I just installed SQL Server 2005 Express edition, and created an app in
>Visual Studio using C#. I get the following error when trying to connect:
> "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: Named Pipes Provider, error: 40 - Could not open a
> connection to SQL Server)"
> I enabled Named Pipes but left TCP/IP disabled because this is a localhost
> connection on the same computer.
> Here is the portion of the code I am using to connect:
> string dbCString = "Persist Security Info=False;Integrated
> Security=true;Trusted_Connection=true;Initial
> Catalog=MxData;server=(local)";
> SqlConnection ThisConnection = new SqlConnection(dbCString);
> ThisConnection.Open();
>
> Thanks in advance for any help.
>
>
>
>
>
|||Thanks for the info, but I did everything suggested in those two articles
and I still get the same connection error. I also tried various alterations
of my connection string with no success. Further research indicates that
this is a common problem, so I wonder why Microsoft ships the Express
edition along with Visual Studio 2005, but makes it so difficult to connect.
If you or anyone else has any other ideas, I would be most grateful.
Thanks.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:OOy2vm$EHHA.1784@.TK2MSFTNGP06.phx.gbl...
> Perhaps these will help:
> Configuration -Configure SQL Server 2005 to allow remote connections
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277
> Configuration -Connect to SQL Express from "downlevel clients"
> http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to
> the top yourself.
> - H. Norman Schwarzkopf
>
> "TAC" <tac@.noemail.com> wrote in message
> news:GsCdnUMH0OMdb_DYnZ2dnUVZ_qOdnZ2d@.giganews.com ...
>
|||This is a WAG:
You only need one of these two settings, perhaps having both is causing the
connection string to cause failure.
Integrated Security=true;Trusted_Connection=true;
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"TAC" <tac@.noemail.com> wrote in message
news:ONCdne2ocahst_LYnZ2dnUVZ_u6dnZ2d@.giganews.com ...
> Thanks for the info, but I did everything suggested in those two articles
> and I still get the same connection error. I also tried various
> alterations of my connection string with no success. Further research
> indicates that this is a common problem, so I wonder why Microsoft ships
> the Express edition along with Visual Studio 2005, but makes it so
> difficult to connect.
> If you or anyone else has any other ideas, I would be most grateful.
> Thanks.
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:OOy2vm$EHHA.1784@.TK2MSFTNGP06.phx.gbl...
>
|||Thanks for the suggestion, but using only one or the other didn't work,
either.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:OD744%23LFHHA.1912@.TK2MSFTNGP03.phx.gbl...
> This is a WAG:
> You only need one of these two settings, perhaps having both is causing
> the connection string to cause failure.
> Integrated Security=true;Trusted_Connection=true;
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to
> the top yourself.
> - H. Norman Schwarzkopf
>
> "TAC" <tac@.noemail.com> wrote in message
> news:ONCdne2ocahst_LYnZ2dnUVZ_u6dnZ2d@.giganews.com ...
>
|||After a lot of research, here's the answer. The connection string should
read as follows:
"Server=.\\SQLEXPRESS;Initial Catalog=MxData;Integrated Security=SSPI";
Note that you need a dot followed by TWO left slashes in your server name.
BTW, Initial Catalog is whatever database you want to use.
"TAC" <tac@.noemail.com> wrote in message
news:bpadnbRK_s7Mz_LYnZ2dnUVZ_oadnZ2d@.giganews.com ...
> Thanks for the suggestion, but using only one or the other didn't work,
> either.
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:OD744%23LFHHA.1912@.TK2MSFTNGP03.phx.gbl...
>
|||Thanks for following up your own post. It prevents others from wasting their
time trying to help you after you have solved the problem, and it helps
others when you share your solution.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"TAC" <tac@.noemail.com> wrote in message
news:BKidnUwKtpML-PLYnZ2dnUVZ_qmdnZ2d@.giganews.com...
> After a lot of research, here's the answer. The connection string should
> read as follows:
> "Server=.\\SQLEXPRESS;Initial Catalog=MxData;Integrated Security=SSPI";
> Note that you need a dot followed by TWO left slashes in your server name.
> BTW, Initial Catalog is whatever database you want to use.
>
> "TAC" <tac@.noemail.com> wrote in message
> news:bpadnbRK_s7Mz_LYnZ2dnUVZ_oadnZ2d@.giganews.com ...
>

Local connection to SQL Server 2005 Express fails

I just installed SQL Server 2005 Express edition, and created an app in
Visual Studio using C#. I get the following error when trying to connect:
"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: Named Pipes Provider, error: 40 - Could not open a connection to
SQL Server)"
I enabled Named Pipes but left TCP/IP disabled because this is a localhost
connection on the same computer.
Here is the portion of the code I am using to connect:
string dbCString = "Persist Security Info=False;Integrated
Security=true;Trusted_Connection=true;In
itial
Catalog=MxData;server=(local)";
SqlConnection ThisConnection = new SqlConnection(dbCString);
ThisConnection.Open();
Thanks in advance for any help.Perhaps these will help:
Configuration -Configure SQL Server 2005 to allow remote connections
http://support.microsoft.com/defaul...kb;EN-US;914277
Configuration -Connect to SQL Express from "downlevel clients"
http://blogs.msdn.com/sqlexpress/ar.../23/192044.aspx
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"TAC" <tac@.noemail.com> wrote in message
news:GsCdnUMH0OMdb_DYnZ2dnUVZ_qOdnZ2d@.gi
ganews.com...
>I just installed SQL Server 2005 Express edition, and created an app in
>Visual Studio using C#. I get the following error when trying to connect:
> "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: Named Pipes Provider, error: 40 - Could not open a
> connection to SQL Server)"
> I enabled Named Pipes but left TCP/IP disabled because this is a localhost
> connection on the same computer.
> Here is the portion of the code I am using to connect:
> string dbCString = "Persist Security Info=False;Integrated
> Security=true;Trusted_Connection=true;In
itial
> Catalog=MxData;server=(local)";
> SqlConnection ThisConnection = new SqlConnection(dbCString);
> ThisConnection.Open();
>
> Thanks in advance for any help.
>
>
>
>
>|||Thanks for the info, but I did everything suggested in those two articles
and I still get the same connection error. I also tried various alterations
of my connection string with no success. Further research indicates that
this is a common problem, so I wonder why Microsoft ships the Express
edition along with Visual Studio 2005, but makes it so difficult to connect.
If you or anyone else has any other ideas, I would be most grateful.
Thanks.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:OOy2vm$EHHA.1784@.TK2MSFTNGP06.phx.gbl...
> Perhaps these will help:
> Configuration -Configure SQL Server 2005 to allow remote connections
> http://support.microsoft.com/defaul...kb;EN-US;914277
> Configuration -Connect to SQL Express from "downlevel clients"
> http://blogs.msdn.com/sqlexpress/ar.../23/192044.aspx
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to
> the top yourself.
> - H. Norman Schwarzkopf
>
> "TAC" <tac@.noemail.com> wrote in message
> news:GsCdnUMH0OMdb_DYnZ2dnUVZ_qOdnZ2d@.gi
ganews.com...
>|||This is a WAG:
You only need one of these two settings, perhaps having both is causing the
connection string to cause failure.
Integrated Security=true;Trusted_Connection=true;
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"TAC" <tac@.noemail.com> wrote in message
news:ONCdne2ocahst_LYnZ2dnUVZ_u6dnZ2d@.gi
ganews.com...
> Thanks for the info, but I did everything suggested in those two articles
> and I still get the same connection error. I also tried various
> alterations of my connection string with no success. Further research
> indicates that this is a common problem, so I wonder why Microsoft ships
> the Express edition along with Visual Studio 2005, but makes it so
> difficult to connect.
> If you or anyone else has any other ideas, I would be most grateful.
> Thanks.
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:OOy2vm$EHHA.1784@.TK2MSFTNGP06.phx.gbl...
>|||After a lot of research, here's the answer. The connection string should
read as follows:
"Server=.\\SQLEXPRESS;Initial Catalog=MxData;Integrated Security=SSPI";
Note that you need a dot followed by TWO left slashes in your server name.
BTW, Initial Catalog is whatever database you want to use.
"TAC" <tac@.noemail.com> wrote in message
news:bpadnbRK_s7Mz_LYnZ2dnUVZ_oadnZ2d@.gi
ganews.com...
> Thanks for the suggestion, but using only one or the other didn't work,
> either.
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:OD744%23LFHHA.1912@.TK2MSFTNGP03.phx.gbl...
>|||Thanks for following up your own post. It prevents others from wasting their
time trying to help you after you have solved the problem, and it helps
others when you share your solution.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"TAC" <tac@.noemail.com> wrote in message
news:BKidnUwKtpML-PLYnZ2dnUVZ_qmdnZ2d@.giganews.com...
> After a lot of research, here's the answer. The connection string should
> read as follows:
> "Server=.\\SQLEXPRESS;Initial Catalog=MxData;Integrated Security=SSPI";
> Note that you need a dot followed by TWO left slashes in your server name.
> BTW, Initial Catalog is whatever database you want to use.
>
> "TAC" <tac@.noemail.com> wrote in message
> news:bpadnbRK_s7Mz_LYnZ2dnUVZ_oadnZ2d@.gi
ganews.com...
>

local app to access sql server box in a different office

Hello,
What different methods exist when trying to set up a sql server box to
be accessible to an app that needs to connect to it through DSN.
The app is running in a different city than the sql server box.
Would I need to do this through a VPN, IIS?
Any suggestions much appreciated.
You'll need to establish some form of network connectivity between your
offices. This could be anything from a VPN connection over the internet, to
dedicated lines (an expensive but potentially more reliable option). You
may want to post this question to microsoft.public.windows.server.networking
group for other ideas and advice.
--Brian
(Please reply to the newsgroups only.)
"Developer in Need of Information"
<DeveloperinNeedofInformation@.discussions.microsof t.com> wrote in message
news:9CAD55C5-2D7F-4CDF-8E46-255D1ACEC8DC@.microsoft.com...
> Hello,
> What different methods exist when trying to set up a sql server box to
> be accessible to an app that needs to connect to it through DSN.
> The app is running in a different city than the sql server box.
> Would I need to do this through a VPN, IIS?
> Any suggestions much appreciated.

local app to access sql server box in a different office

Hello,
What different methods exist when trying to set up a sql server box to
be accessible to an app that needs to connect to it through DSN.
The app is running in a different city than the sql server box.
Would I need to do this through a VPN, IIS?
Any suggestions much appreciated.You'll need to establish some form of network connectivity between your
offices. This could be anything from a VPN connection over the internet, to
dedicated lines (an expensive but potentially more reliable option). You
may want to post this question to microsoft.public.windows.server.networking
group for other ideas and advice.
--Brian
(Please reply to the newsgroups only.)
"Developer in Need of Information"
< DeveloperinNeedofInformation@.discussions
.microsoft.com> wrote in message
news:9CAD55C5-2D7F-4CDF-8E46-255D1ACEC8DC@.microsoft.com...
> Hello,
> What different methods exist when trying to set up a sql server box to
> be accessible to an app that needs to connect to it through DSN.
> The app is running in a different city than the sql server box.
> Would I need to do this through a VPN, IIS?
> Any suggestions much appreciated.