Hi there,
I am running on my machine the following SQL statements on my (local) SQL
Server:
SET NOCOUNT ON
SET ROWCOUNT 1500
delete_more:
delete table1 where field1 = '1' and field2 = '2'
IF @.@.ROWCOUNT > 0 GOTO delete_more
SET ROWCOUNT 0
The table has arround 5 million records and the DELETE statement would
delete arround 100000 records. I can see by running sp_lock that SQL is
escalating the locks to a table lock.
I am running in paralel the following:
SET ROWCOUNT 100
delete table1 where field1 = '3' and field2 = '2'
SET ROWCOUNT 0
This query is running fine, however the DELETE statement fails with the
error 1204:
"The SQL Server cannot obtain a LOCK resource at this time". Can somebody
shed some light for me here? Am I not able to run two DELETE statements is
paralel or am I doing something wrong?
I am running with the run_value for locks set to 0.
Thank you in advance.You mentioned there is already a table lock on that particular table, as it
is deleting millions of records. You probably need to wait for this
transaction to complete before you execute another delete statement on the
same table.
I suspect changing isolation level would solve your purpose.
"Andrei" wrote:
> Hi there,
> I am running on my machine the following SQL statements on my (local) SQL
> Server:
> SET NOCOUNT ON
> SET ROWCOUNT 1500
> delete_more:
> delete table1 where field1 = '1' and field2 = '2'
> IF @.@.ROWCOUNT > 0 GOTO delete_more
> SET ROWCOUNT 0
> The table has arround 5 million records and the DELETE statement would
> delete arround 100000 records. I can see by running sp_lock that SQL is
> escalating the locks to a table lock.
> I am running in paralel the following:
> SET ROWCOUNT 100
> delete table1 where field1 = '3' and field2 = '2'
> SET ROWCOUNT 0
> This query is running fine, however the DELETE statement fails with the
> error 1204:
> "The SQL Server cannot obtain a LOCK resource at this time". Can somebody
> shed some light for me here? Am I not able to run two DELETE statements is
> paralel or am I doing something wrong?
> I am running with the run_value for locks set to 0.
> Thank you in advance.|||What I want is to run several jobs concurrently, I do not want to wait for
one to finish in order to start the second one.
However I do not understand why I get the error, since the first statement
is suppose to delete 1500 records (so maximum 1500 key locks applied, if
there is no lock escalation) and the second statement is deleting 100
records. Am I missing something here?
"GYK" wrote:
> You mentioned there is already a table lock on that particular table, as it
> is deleting millions of records. You probably need to wait for this
> transaction to complete before you execute another delete statement on the
> same table.
> I suspect changing isolation level would solve your purpose.
> "Andrei" wrote:
> > Hi there,
> >
> > I am running on my machine the following SQL statements on my (local) SQL
> > Server:
> >
> > SET NOCOUNT ON
> >
> > SET ROWCOUNT 1500
> > delete_more:
> > delete table1 where field1 = '1' and field2 = '2'
> > IF @.@.ROWCOUNT > 0 GOTO delete_more
> > SET ROWCOUNT 0
> >
> > The table has arround 5 million records and the DELETE statement would
> > delete arround 100000 records. I can see by running sp_lock that SQL is
> > escalating the locks to a table lock.
> >
> > I am running in paralel the following:
> > SET ROWCOUNT 100
> > delete table1 where field1 = '3' and field2 = '2'
> > SET ROWCOUNT 0
> >
> > This query is running fine, however the DELETE statement fails with the
> > error 1204:
> > "The SQL Server cannot obtain a LOCK resource at this time". Can somebody
> > shed some light for me here? Am I not able to run two DELETE statements is
> > paralel or am I doing something wrong?
> >
> > I am running with the run_value for locks set to 0.
> >
> > Thank you in advance.
Showing posts with label local. Show all posts
Showing posts with label local. Show all posts
Friday, March 16, 2012
Sunday, March 11, 2012
"SQL Server does not exist or access denied", again...
I'm have that darn problem...
Here is our setup:
Remote clustered SQL Server 2000 running on a Windows Server 2003 Enterprise Edition box.
Local IIS 5.1 web server (my development box) running on Windows XP Tablet Edition.
I'm using Visual Studio.Net 2003 and using ASP.Net 1.1.4322.
I have an ASP.Net application that hits the remote SQL Server. When I'm on my development box and viewing my ASP.Net project locally via IE, the application retrieves values from the remote SQL Server with no problems. If I get onto another machine, launch IE, and then hit my development box's web server (which is running the asp.net app), I get the dreaded "SQL Server does not exist or access is denied" msg.
In my ASP.Net app, I have impersonate set to true and the website is set only to windows authentication. The remote SQL Server is set to SQL and Windows authentication.
I'm at a loss on what to do, any help would be greatly appreciated.
Thanks,
ExitusLSU
Hi,This seems to be firewall issue.
Check it after disabling firewall.
http://bhatiaworld.blogspot.com/2005/12/dot-net-error-aspnet-website-or-web.html|||
I also forgot to mention my connection string. It is:
"Data Source=<server name>;Integrated Security=SSPI;Initial Catalog=<database name>"
I've tried using the IP address, but that didn't work.
Thanks,
ExitusLSU
Saturday, February 25, 2012
"Local" Server
All:
I was using a 120 day eval of SQL Server, and then purchased the developers
edition. During the install....and don't ask me exactly how I did it, I
ended up naming my "new" server TVB\TVB - the old one was just TVB, and I
managed to remove the TVB and now have no "local" server, so to speak. I
can't find the setting to rectify this, although the database works
OK...but when using connection strings, I can't use localhost in the code,
I have to use the actual name, fortunately I can still use it in the URL.
This is probably easy to rectify if one knows where to look or what to do,
and I fear this may cause a problem bigger than the minor annoyance it is
now down the line. (Plus I only have a few apps set up , many more to come)
Advice greatly appreciated, thanks in advance.
Tom BThe only way to rectify this is to uninstall the named instance and
reinstall as a default instance. You cannot rename a SQL Server named
instance. If you have databases you want to keep then you can back them up
or detach them before uninstalling and then restore or attach them to your
default instance (that will have the same name as the server)
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Tom Bernsen" <t.bernsen@.worldnet.att.net> wrote in message
news:%23VGj0hN9DHA.2064@.TK2MSFTNGP11.phx.gbl...
> All:
> I was using a 120 day eval of SQL Server, and then purchased the
developers
> edition. During the install....and don't ask me exactly how I did it, I
> ended up naming my "new" server TVB\TVB - the old one was just TVB, and I
> managed to remove the TVB and now have no "local" server, so to speak. I
> can't find the setting to rectify this, although the database works
> OK...but when using connection strings, I can't use localhost in the
code,
> I have to use the actual name, fortunately I can still use it in the URL.
> This is probably easy to rectify if one knows where to look or what to do,
> and I fear this may cause a problem bigger than the minor annoyance it is
> now down the line. (Plus I only have a few apps set up , many more to
come)
> Advice greatly appreciated, thanks in advance.
> Tom B
>
I was using a 120 day eval of SQL Server, and then purchased the developers
edition. During the install....and don't ask me exactly how I did it, I
ended up naming my "new" server TVB\TVB - the old one was just TVB, and I
managed to remove the TVB and now have no "local" server, so to speak. I
can't find the setting to rectify this, although the database works
OK...but when using connection strings, I can't use localhost in the code,
I have to use the actual name, fortunately I can still use it in the URL.
This is probably easy to rectify if one knows where to look or what to do,
and I fear this may cause a problem bigger than the minor annoyance it is
now down the line. (Plus I only have a few apps set up , many more to come)
Advice greatly appreciated, thanks in advance.
Tom BThe only way to rectify this is to uninstall the named instance and
reinstall as a default instance. You cannot rename a SQL Server named
instance. If you have databases you want to keep then you can back them up
or detach them before uninstalling and then restore or attach them to your
default instance (that will have the same name as the server)
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Tom Bernsen" <t.bernsen@.worldnet.att.net> wrote in message
news:%23VGj0hN9DHA.2064@.TK2MSFTNGP11.phx.gbl...
> All:
> I was using a 120 day eval of SQL Server, and then purchased the
developers
> edition. During the install....and don't ask me exactly how I did it, I
> ended up naming my "new" server TVB\TVB - the old one was just TVB, and I
> managed to remove the TVB and now have no "local" server, so to speak. I
> can't find the setting to rectify this, although the database works
> OK...but when using connection strings, I can't use localhost in the
code,
> I have to use the actual name, fortunately I can still use it in the URL.
> This is probably easy to rectify if one knows where to look or what to do,
> and I fear this may cause a problem bigger than the minor annoyance it is
> now down the line. (Plus I only have a few apps set up , many more to
come)
> Advice greatly appreciated, thanks in advance.
> Tom B
>
"Local" Server
All:
I was using a 120 day eval of SQL Server, and then purchased the developers
edition. During the install....and don't ask me exactly how I did it, I
ended up naming my "new" server TVB\TVB - the old one was just TVB, and I
managed to remove the TVB and now have no "local" server, so to speak. I
can't find the setting to rectify this, although the database works
OK...but when using connection strings, I can't use localhost in the code,
I have to use the actual name, fortunately I can still use it in the URL.
This is probably easy to rectify if one knows where to look or what to do,
and I fear this may cause a problem bigger than the minor annoyance it is
now down the line. (Plus I only have a few apps set up , many more to come)
Advice greatly appreciated, thanks in advance.
Tom BThe only way to rectify this is to uninstall the named instance and
reinstall as a default instance. You cannot rename a SQL Server named
instance. If you have databases you want to keep then you can back them up
or detach them before uninstalling and then restore or attach them to your
default instance (that will have the same name as the server)
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Tom Bernsen" <t.bernsen@.worldnet.att.net> wrote in message
news:%23VGj0hN9DHA.2064@.TK2MSFTNGP11.phx.gbl...
> All:
> I was using a 120 day eval of SQL Server, and then purchased the
developers
> edition. During the install....and don't ask me exactly how I did it, I
> ended up naming my "new" server TVB\TVB - the old one was just TVB, and I
> managed to remove the TVB and now have no "local" server, so to speak. I
> can't find the setting to rectify this, although the database works
> OK...but when using connection strings, I can't use localhost in the
code,
> I have to use the actual name, fortunately I can still use it in the URL.
> This is probably easy to rectify if one knows where to look or what to do,
> and I fear this may cause a problem bigger than the minor annoyance it is
> now down the line. (Plus I only have a few apps set up , many more to
come)
> Advice greatly appreciated, thanks in advance.
> Tom B
>
I was using a 120 day eval of SQL Server, and then purchased the developers
edition. During the install....and don't ask me exactly how I did it, I
ended up naming my "new" server TVB\TVB - the old one was just TVB, and I
managed to remove the TVB and now have no "local" server, so to speak. I
can't find the setting to rectify this, although the database works
OK...but when using connection strings, I can't use localhost in the code,
I have to use the actual name, fortunately I can still use it in the URL.
This is probably easy to rectify if one knows where to look or what to do,
and I fear this may cause a problem bigger than the minor annoyance it is
now down the line. (Plus I only have a few apps set up , many more to come)
Advice greatly appreciated, thanks in advance.
Tom BThe only way to rectify this is to uninstall the named instance and
reinstall as a default instance. You cannot rename a SQL Server named
instance. If you have databases you want to keep then you can back them up
or detach them before uninstalling and then restore or attach them to your
default instance (that will have the same name as the server)
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Tom Bernsen" <t.bernsen@.worldnet.att.net> wrote in message
news:%23VGj0hN9DHA.2064@.TK2MSFTNGP11.phx.gbl...
> All:
> I was using a 120 day eval of SQL Server, and then purchased the
developers
> edition. During the install....and don't ask me exactly how I did it, I
> ended up naming my "new" server TVB\TVB - the old one was just TVB, and I
> managed to remove the TVB and now have no "local" server, so to speak. I
> can't find the setting to rectify this, although the database works
> OK...but when using connection strings, I can't use localhost in the
code,
> I have to use the actual name, fortunately I can still use it in the URL.
> This is probably easy to rectify if one knows where to look or what to do,
> and I fear this may cause a problem bigger than the minor annoyance it is
> now down the line. (Plus I only have a few apps set up , many more to
come)
> Advice greatly appreciated, thanks in advance.
> Tom B
>
Thursday, February 16, 2012
"Full-Text Index Table" is disabled?
I'm trying to do some local development so I installed Full-Text Indexing as
part of the SQL 2000 Developer Edition on my Windows XP machine.
The MS Search Service is running but the option is still disabled in EM when
I right-click on a table.
Thanks, Dave.
http://www.indexserverfaq.com/tablenotenabled.htm
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:BE6EABAB-0F45-40B2-9C9C-1A29A1A8746B@.microsoft.com...
> I'm trying to do some local development so I installed Full-Text Indexing
> as
> part of the SQL 2000 Developer Edition on my Windows XP machine.
> The MS Search Service is running but the option is still disabled in EM
> when
> I right-click on a table.
> Thanks, Dave.
part of the SQL 2000 Developer Edition on my Windows XP machine.
The MS Search Service is running but the option is still disabled in EM when
I right-click on a table.
Thanks, Dave.
http://www.indexserverfaq.com/tablenotenabled.htm
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:BE6EABAB-0F45-40B2-9C9C-1A29A1A8746B@.microsoft.com...
> I'm trying to do some local development so I installed Full-Text Indexing
> as
> part of the SQL 2000 Developer Edition on my Windows XP machine.
> The MS Search Service is running but the option is still disabled in EM
> when
> I right-click on a table.
> Thanks, Dave.
Saturday, February 11, 2012
"Data Source=localhost;..." fails with : General network error.
I replaced SQL 2000 with SQL 2005 Developer edition and my application could
not connect
to the local SQL server anymore. When connecting from a .Net 1.1 application
using the
following connection string:
"Data Source=localhost;Database=MM;Integrated Security=SSPI"
I always received the following exception message:
"General network error. Check your network documentation."
I had no problem to connect when using the host name instead of "localhost".
The problem
got resolved when I started "SQL Server Surface Area Configuration" and conf
igured it as
follow:
Surface Area Configuration for Services and Connections -> Database Engine -
> Remote
Connections -> Local and remote connections -> Using TCP/IP only.
The SQL 2005 Developer Edition is configured out of the box with "Local conn
ections only"
and "localhost" for some reason is not recognized as a local connection !!!!Try . or (local) or servername or IP address.
"Gaetan" <someone@.somewhere.com> wrote in message
news:14fhn1dr367kni9qt8vp7j5rb70imtelhl@.
4ax.com...
>I replaced SQL 2000 with SQL 2005 Developer edition and my application
>could not connect
> to the local SQL server anymore. When connecting from a .Net 1.1
> application using the
> following connection string:
> "Data Source=localhost;Database=MM;Integrated Security=SSPI"
> I always received the following exception message:
> "General network error. Check your network documentation."
> I had no problem to connect when using the host name instead of
> "localhost". The problem
> got resolved when I started "SQL Server Surface Area Configuration" and
> configured it as
> follow:
> Surface Area Configuration for Services and Connections -> Database
> Engine -> Remote
> Connections -> Local and remote connections -> Using TCP/IP only.
> The SQL 2005 Developer Edition is configured out of the box with "Local
> connections only"
> and "localhost" for some reason is not recognized as a local connection
> !!!!
>
not connect
to the local SQL server anymore. When connecting from a .Net 1.1 application
using the
following connection string:
"Data Source=localhost;Database=MM;Integrated Security=SSPI"
I always received the following exception message:
"General network error. Check your network documentation."
I had no problem to connect when using the host name instead of "localhost".
The problem
got resolved when I started "SQL Server Surface Area Configuration" and conf
igured it as
follow:
Surface Area Configuration for Services and Connections -> Database Engine -
> Remote
Connections -> Local and remote connections -> Using TCP/IP only.
The SQL 2005 Developer Edition is configured out of the box with "Local conn
ections only"
and "localhost" for some reason is not recognized as a local connection !!!!Try . or (local) or servername or IP address.
"Gaetan" <someone@.somewhere.com> wrote in message
news:14fhn1dr367kni9qt8vp7j5rb70imtelhl@.
4ax.com...
>I replaced SQL 2000 with SQL 2005 Developer edition and my application
>could not connect
> to the local SQL server anymore. When connecting from a .Net 1.1
> application using the
> following connection string:
> "Data Source=localhost;Database=MM;Integrated Security=SSPI"
> I always received the following exception message:
> "General network error. Check your network documentation."
> I had no problem to connect when using the host name instead of
> "localhost". The problem
> got resolved when I started "SQL Server Surface Area Configuration" and
> configured it as
> follow:
> Surface Area Configuration for Services and Connections -> Database
> Engine -> Remote
> Connections -> Local and remote connections -> Using TCP/IP only.
> The SQL 2005 Developer Edition is configured out of the box with "Local
> connections only"
> and "localhost" for some reason is not recognized as a local connection
> !!!!
>
Thursday, February 9, 2012
"cannot open user default database. Login failed"
When attempting to connect to my local instance of SQL server I am getting t
his error message: "cannot open user default database. Login failed".
My OS is WinXp v2002 SP2. This issue started after I deleted a database on
my local server, prior to that it was working fine.
ThanksIt looks like you deleted the database that was the default database for
your login. One method to fix the problem is to connect using the OSQL
command-line utility, overriding the default database. You can then change
the default DB to one that exists.
The example below connects to the local server with SQL authentication and
changes the default database to 'master':
OSQL -d master -U MyLogin -P MyPassword -Q"EXEC sp_defaultdb 'MyLogin'
,'master'"
Hope this helps.
Dan Guzman
SQL Server MVP
"Brent Stevenson" <essexbs@.insightbb.com> wrote in message
news:u6aIYXRwFHA.3864@.TK2MSFTNGP12.phx.gbl...
When attempting to connect to my local instance of SQL server I am getting
this error message: "cannot open user default database. Login failed".
My OS is WinXp v2002 SP2. This issue started after I deleted a database on
my local server, prior to that it was working fine.
Thanks|||Dan,
Thanks for the reply! But infortunaley that fails with a "login failed for
'user' error."
According to OSQL help: "If neither the -U or -P options are used, SQL
Server 2000 attempts to connect using Windows Authentication Mode." Well
that would be my network account & password and if I just enter OSQL -d
master and then my network account password at the prompt, it fails (login
failed for user). I know I'm correctly entering my current network account
password.
Any ideas?
Thanks
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:Oex4GfRwFHA.464@.TK2MSFTNGP15.phx.gbl...
> It looks like you deleted the database that was the default database for
> your login. One method to fix the problem is to connect using the OSQL
> command-line utility, overriding the default database. You can then
> change the default DB to one that exists.
> The example below connects to the local server with SQL authentication and
> changes the default database to 'master':
> OSQL -d master -U MyLogin -P MyPassword -Q"EXEC sp_defaultdb 'MyLogin'
> ,'master'"
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Brent Stevenson" <essexbs@.insightbb.com> wrote in message
> news:u6aIYXRwFHA.3864@.TK2MSFTNGP12.phx.gbl...
> When attempting to connect to my local instance of SQL server I am getting
> this error message: "cannot open user default database. Login failed".
> My OS is WinXp v2002 SP2. This issue started after I deleted a database
> on my local server, prior to that it was working fine.
> Thanks
>|||My machines gives an error if I don't use -U or -E. Did you try using the -E
switch to login using
your Windows account?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Brent Stevenson" <essexbs@.insightbb.com> wrote in message
news:OCxkkudwFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Dan,
> Thanks for the reply! But infortunaley that fails with a "login failed for
'user' error."
> According to OSQL help: "If neither the -U or -P options are used, SQL Ser
ver 2000 attempts to
> connect using Windows Authentication Mode." Well that would be my network
account & password and
> if I just enter OSQL -d master and then my network account password at the
prompt, it fails (login
> failed for user). I know I'm correctly entering my current network account
password.
> Any ideas?
> Thanks
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:Oex4GfRwFHA.464@.TK2MSFTNGP15.phx.gbl...
>|||Using the -E go me in & allowed me to change my default DB & resolve the
issue.
Thanks a million!! Have a good day.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23vKLtxdwFHA.3556@.TK2MSFTNGP12.phx.gbl...
> My machines gives an error if I don't use -U or -E. Did you try using
> the -E switch to login using your Windows account?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Brent Stevenson" <essexbs@.insightbb.com> wrote in message
> news:OCxkkudwFHA.1028@.TK2MSFTNGP12.phx.gbl...
>|||The example I posted assumed a SQL login. Like Tibor mentioned, you can
specify '-E' to establish a trusted connection using your current Windows
account. You never need to specify a Windows account with the '-E'
parameter but you still need to specify the name of the account to
sp_defaultdb. For a local Windows account:
OSQL -d master -E -Q"EXEC sp_defaultdb 'MyMachine\MyAccount','master'"
or for a domain account:
OSQL -d master -E -Q"EXEC sp_defaultdb 'MyDomain\MyAccount','master'"
If your SQL Server access is via membership of the local Administrators
group, you'll need to change the default database for that group instead:
OSQL -d master -E -Q"EXEC sp_defaultdb 'BUILTIN\Administrators','master'"
Hope this helps.
Dan Guzman
SQL Server MVP
"Brent Stevenson" <essexbs@.insightbb.com> wrote in message
news:OCxkkudwFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Dan,
> Thanks for the reply! But infortunaley that fails with a "login failed for
> 'user' error."
> According to OSQL help: "If neither the -U or -P options are used, SQL
> Server 2000 attempts to connect using Windows Authentication Mode." Well
> that would be my network account & password and if I just enter OSQL -d
> master and then my network account password at the prompt, it fails (login
> failed for user). I know I'm correctly entering my current network account
> password.
> Any ideas?
> Thanks
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:Oex4GfRwFHA.464@.TK2MSFTNGP15.phx.gbl...
>|||I just had the same problem, and used this same solution. Deleted a
database, didn't realise it was my default DB (or that it mattered), and
couldn't figure out the account credentials problem I was having using the
command line suggestions found elsewhere. Was using a domain admin account.
Thanks very much!
Bryan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:u9z7NBewFHA.2540@.TK2MSFTNGP09.phx.gbl...
> The example I posted assumed a SQL login. Like Tibor mentioned, you can
> specify '-E' to establish a trusted connection using your current Windows
> account. You never need to specify a Windows account with the '-E'
> parameter but you still need to specify the name of the account to
> sp_defaultdb. For a local Windows account:
> OSQL -d master -E -Q"EXEC sp_defaultdb 'MyMachine\MyAccount','master'"
> or for a domain account:
> OSQL -d master -E -Q"EXEC sp_defaultdb 'MyDomain\MyAccount','master'"
> If your SQL Server access is via membership of the local Administrators
> group, you'll need to change the default database for that group instead:
> OSQL -d master -E -Q"EXEC sp_defaultdb 'BUILTIN\Administrators','master'"
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Brent Stevenson" <essexbs@.insightbb.com> wrote in message
> news:OCxkkudwFHA.1028@.TK2MSFTNGP12.phx.gbl...
>
his error message: "cannot open user default database. Login failed".
My OS is WinXp v2002 SP2. This issue started after I deleted a database on
my local server, prior to that it was working fine.
ThanksIt looks like you deleted the database that was the default database for
your login. One method to fix the problem is to connect using the OSQL
command-line utility, overriding the default database. You can then change
the default DB to one that exists.
The example below connects to the local server with SQL authentication and
changes the default database to 'master':
OSQL -d master -U MyLogin -P MyPassword -Q"EXEC sp_defaultdb 'MyLogin'
,'master'"
Hope this helps.
Dan Guzman
SQL Server MVP
"Brent Stevenson" <essexbs@.insightbb.com> wrote in message
news:u6aIYXRwFHA.3864@.TK2MSFTNGP12.phx.gbl...
When attempting to connect to my local instance of SQL server I am getting
this error message: "cannot open user default database. Login failed".
My OS is WinXp v2002 SP2. This issue started after I deleted a database on
my local server, prior to that it was working fine.
Thanks|||Dan,
Thanks for the reply! But infortunaley that fails with a "login failed for
'user' error."
According to OSQL help: "If neither the -U or -P options are used, SQL
Server 2000 attempts to connect using Windows Authentication Mode." Well
that would be my network account & password and if I just enter OSQL -d
master and then my network account password at the prompt, it fails (login
failed for user). I know I'm correctly entering my current network account
password.
Any ideas?
Thanks
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:Oex4GfRwFHA.464@.TK2MSFTNGP15.phx.gbl...
> It looks like you deleted the database that was the default database for
> your login. One method to fix the problem is to connect using the OSQL
> command-line utility, overriding the default database. You can then
> change the default DB to one that exists.
> The example below connects to the local server with SQL authentication and
> changes the default database to 'master':
> OSQL -d master -U MyLogin -P MyPassword -Q"EXEC sp_defaultdb 'MyLogin'
> ,'master'"
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Brent Stevenson" <essexbs@.insightbb.com> wrote in message
> news:u6aIYXRwFHA.3864@.TK2MSFTNGP12.phx.gbl...
> When attempting to connect to my local instance of SQL server I am getting
> this error message: "cannot open user default database. Login failed".
> My OS is WinXp v2002 SP2. This issue started after I deleted a database
> on my local server, prior to that it was working fine.
> Thanks
>|||My machines gives an error if I don't use -U or -E. Did you try using the -E
switch to login using
your Windows account?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Brent Stevenson" <essexbs@.insightbb.com> wrote in message
news:OCxkkudwFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Dan,
> Thanks for the reply! But infortunaley that fails with a "login failed for
'user' error."
> According to OSQL help: "If neither the -U or -P options are used, SQL Ser
ver 2000 attempts to
> connect using Windows Authentication Mode." Well that would be my network
account & password and
> if I just enter OSQL -d master and then my network account password at the
prompt, it fails (login
> failed for user). I know I'm correctly entering my current network account
password.
> Any ideas?
> Thanks
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:Oex4GfRwFHA.464@.TK2MSFTNGP15.phx.gbl...
>|||Using the -E go me in & allowed me to change my default DB & resolve the
issue.
Thanks a million!! Have a good day.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23vKLtxdwFHA.3556@.TK2MSFTNGP12.phx.gbl...
> My machines gives an error if I don't use -U or -E. Did you try using
> the -E switch to login using your Windows account?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Brent Stevenson" <essexbs@.insightbb.com> wrote in message
> news:OCxkkudwFHA.1028@.TK2MSFTNGP12.phx.gbl...
>|||The example I posted assumed a SQL login. Like Tibor mentioned, you can
specify '-E' to establish a trusted connection using your current Windows
account. You never need to specify a Windows account with the '-E'
parameter but you still need to specify the name of the account to
sp_defaultdb. For a local Windows account:
OSQL -d master -E -Q"EXEC sp_defaultdb 'MyMachine\MyAccount','master'"
or for a domain account:
OSQL -d master -E -Q"EXEC sp_defaultdb 'MyDomain\MyAccount','master'"
If your SQL Server access is via membership of the local Administrators
group, you'll need to change the default database for that group instead:
OSQL -d master -E -Q"EXEC sp_defaultdb 'BUILTIN\Administrators','master'"
Hope this helps.
Dan Guzman
SQL Server MVP
"Brent Stevenson" <essexbs@.insightbb.com> wrote in message
news:OCxkkudwFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Dan,
> Thanks for the reply! But infortunaley that fails with a "login failed for
> 'user' error."
> According to OSQL help: "If neither the -U or -P options are used, SQL
> Server 2000 attempts to connect using Windows Authentication Mode." Well
> that would be my network account & password and if I just enter OSQL -d
> master and then my network account password at the prompt, it fails (login
> failed for user). I know I'm correctly entering my current network account
> password.
> Any ideas?
> Thanks
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:Oex4GfRwFHA.464@.TK2MSFTNGP15.phx.gbl...
>|||I just had the same problem, and used this same solution. Deleted a
database, didn't realise it was my default DB (or that it mattered), and
couldn't figure out the account credentials problem I was having using the
command line suggestions found elsewhere. Was using a domain admin account.
Thanks very much!
Bryan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:u9z7NBewFHA.2540@.TK2MSFTNGP09.phx.gbl...
> The example I posted assumed a SQL login. Like Tibor mentioned, you can
> specify '-E' to establish a trusted connection using your current Windows
> account. You never need to specify a Windows account with the '-E'
> parameter but you still need to specify the name of the account to
> sp_defaultdb. For a local Windows account:
> OSQL -d master -E -Q"EXEC sp_defaultdb 'MyMachine\MyAccount','master'"
> or for a domain account:
> OSQL -d master -E -Q"EXEC sp_defaultdb 'MyDomain\MyAccount','master'"
> If your SQL Server access is via membership of the local Administrators
> group, you'll need to change the default database for that group instead:
> OSQL -d master -E -Q"EXEC sp_defaultdb 'BUILTIN\Administrators','master'"
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Brent Stevenson" <essexbs@.insightbb.com> wrote in message
> news:OCxkkudwFHA.1028@.TK2MSFTNGP12.phx.gbl...
>
"cannot open user default database. Login failed"
When attempting to connect to my local instance of SQL server I am getting this error message: "cannot open user default database. Login failed".
My OS is WinXp v2002 SP2. This issue started after I deleted a database on my local server, prior to that it was working fine.
Thanks
It looks like you deleted the database that was the default database for
your login. One method to fix the problem is to connect using the OSQL
command-line utility, overriding the default database. You can then change
the default DB to one that exists.
The example below connects to the local server with SQL authentication and
changes the default database to 'master':
OSQL -d master -U MyLogin -P MyPassword -Q"EXEC sp_defaultdb 'MyLogin'
,'master'"
Hope this helps.
Dan Guzman
SQL Server MVP
"Brent Stevenson" <essexbs@.insightbb.com> wrote in message
news:u6aIYXRwFHA.3864@.TK2MSFTNGP12.phx.gbl...
When attempting to connect to my local instance of SQL server I am getting
this error message: "cannot open user default database. Login failed".
My OS is WinXp v2002 SP2. This issue started after I deleted a database on
my local server, prior to that it was working fine.
Thanks
|||Dan,
Thanks for the reply! But infortunaley that fails with a "login failed for
'user' error."
According to OSQL help: "If neither the -U or -P options are used, SQL
Server 2000 attempts to connect using Windows Authentication Mode." Well
that would be my network account & password and if I just enter OSQL -d
master and then my network account password at the prompt, it fails (login
failed for user). I know I'm correctly entering my current network account
password.
Any ideas?
Thanks
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:Oex4GfRwFHA.464@.TK2MSFTNGP15.phx.gbl...
> It looks like you deleted the database that was the default database for
> your login. One method to fix the problem is to connect using the OSQL
> command-line utility, overriding the default database. You can then
> change the default DB to one that exists.
> The example below connects to the local server with SQL authentication and
> changes the default database to 'master':
> OSQL -d master -U MyLogin -P MyPassword -Q"EXEC sp_defaultdb 'MyLogin'
> ,'master'"
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Brent Stevenson" <essexbs@.insightbb.com> wrote in message
> news:u6aIYXRwFHA.3864@.TK2MSFTNGP12.phx.gbl...
> When attempting to connect to my local instance of SQL server I am getting
> this error message: "cannot open user default database. Login failed".
> My OS is WinXp v2002 SP2. This issue started after I deleted a database
> on my local server, prior to that it was working fine.
> Thanks
>
|||My machines gives an error if I don't use -U or -E. Did you try using the -E switch to login using
your Windows account?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Brent Stevenson" <essexbs@.insightbb.com> wrote in message
news:OCxkkudwFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Dan,
> Thanks for the reply! But infortunaley that fails with a "login failed for 'user' error."
> According to OSQL help: "If neither the -U or -P options are used, SQL Server 2000 attempts to
> connect using Windows Authentication Mode." Well that would be my network account & password and
> if I just enter OSQL -d master and then my network account password at the prompt, it fails (login
> failed for user). I know I'm correctly entering my current network account password.
> Any ideas?
> Thanks
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:Oex4GfRwFHA.464@.TK2MSFTNGP15.phx.gbl...
>
|||Using the -E go me in & allowed me to change my default DB & resolve the
issue.
Thanks a million!! Have a good day.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23vKLtxdwFHA.3556@.TK2MSFTNGP12.phx.gbl...
> My machines gives an error if I don't use -U or -E. Did you try using
> the -E switch to login using your Windows account?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Brent Stevenson" <essexbs@.insightbb.com> wrote in message
> news:OCxkkudwFHA.1028@.TK2MSFTNGP12.phx.gbl...
>
|||The example I posted assumed a SQL login. Like Tibor mentioned, you can
specify '-E' to establish a trusted connection using your current Windows
account. You never need to specify a Windows account with the '-E'
parameter but you still need to specify the name of the account to
sp_defaultdb. For a local Windows account:
OSQL -d master -E -Q"EXEC sp_defaultdb 'MyMachine\MyAccount','master'"
or for a domain account:
OSQL -d master -E -Q"EXEC sp_defaultdb 'MyDomain\MyAccount','master'"
If your SQL Server access is via membership of the local Administrators
group, you'll need to change the default database for that group instead:
OSQL -d master -E -Q"EXEC sp_defaultdb 'BUILTIN\Administrators','master'"
Hope this helps.
Dan Guzman
SQL Server MVP
"Brent Stevenson" <essexbs@.insightbb.com> wrote in message
news:OCxkkudwFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Dan,
> Thanks for the reply! But infortunaley that fails with a "login failed for
> 'user' error."
> According to OSQL help: "If neither the -U or -P options are used, SQL
> Server 2000 attempts to connect using Windows Authentication Mode." Well
> that would be my network account & password and if I just enter OSQL -d
> master and then my network account password at the prompt, it fails (login
> failed for user). I know I'm correctly entering my current network account
> password.
> Any ideas?
> Thanks
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:Oex4GfRwFHA.464@.TK2MSFTNGP15.phx.gbl...
>
|||I just had the same problem, and used this same solution. Deleted a
database, didn't realise it was my default DB (or that it mattered), and
couldn't figure out the account credentials problem I was having using the
command line suggestions found elsewhere. Was using a domain admin account.
Thanks very much!
Bryan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:u9z7NBewFHA.2540@.TK2MSFTNGP09.phx.gbl...
> The example I posted assumed a SQL login. Like Tibor mentioned, you can
> specify '-E' to establish a trusted connection using your current Windows
> account. You never need to specify a Windows account with the '-E'
> parameter but you still need to specify the name of the account to
> sp_defaultdb. For a local Windows account:
> OSQL -d master -E -Q"EXEC sp_defaultdb 'MyMachine\MyAccount','master'"
> or for a domain account:
> OSQL -d master -E -Q"EXEC sp_defaultdb 'MyDomain\MyAccount','master'"
> If your SQL Server access is via membership of the local Administrators
> group, you'll need to change the default database for that group instead:
> OSQL -d master -E -Q"EXEC sp_defaultdb 'BUILTIN\Administrators','master'"
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Brent Stevenson" <essexbs@.insightbb.com> wrote in message
> news:OCxkkudwFHA.1028@.TK2MSFTNGP12.phx.gbl...
>
My OS is WinXp v2002 SP2. This issue started after I deleted a database on my local server, prior to that it was working fine.
Thanks
It looks like you deleted the database that was the default database for
your login. One method to fix the problem is to connect using the OSQL
command-line utility, overriding the default database. You can then change
the default DB to one that exists.
The example below connects to the local server with SQL authentication and
changes the default database to 'master':
OSQL -d master -U MyLogin -P MyPassword -Q"EXEC sp_defaultdb 'MyLogin'
,'master'"
Hope this helps.
Dan Guzman
SQL Server MVP
"Brent Stevenson" <essexbs@.insightbb.com> wrote in message
news:u6aIYXRwFHA.3864@.TK2MSFTNGP12.phx.gbl...
When attempting to connect to my local instance of SQL server I am getting
this error message: "cannot open user default database. Login failed".
My OS is WinXp v2002 SP2. This issue started after I deleted a database on
my local server, prior to that it was working fine.
Thanks
|||Dan,
Thanks for the reply! But infortunaley that fails with a "login failed for
'user' error."
According to OSQL help: "If neither the -U or -P options are used, SQL
Server 2000 attempts to connect using Windows Authentication Mode." Well
that would be my network account & password and if I just enter OSQL -d
master and then my network account password at the prompt, it fails (login
failed for user). I know I'm correctly entering my current network account
password.
Any ideas?
Thanks
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:Oex4GfRwFHA.464@.TK2MSFTNGP15.phx.gbl...
> It looks like you deleted the database that was the default database for
> your login. One method to fix the problem is to connect using the OSQL
> command-line utility, overriding the default database. You can then
> change the default DB to one that exists.
> The example below connects to the local server with SQL authentication and
> changes the default database to 'master':
> OSQL -d master -U MyLogin -P MyPassword -Q"EXEC sp_defaultdb 'MyLogin'
> ,'master'"
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Brent Stevenson" <essexbs@.insightbb.com> wrote in message
> news:u6aIYXRwFHA.3864@.TK2MSFTNGP12.phx.gbl...
> When attempting to connect to my local instance of SQL server I am getting
> this error message: "cannot open user default database. Login failed".
> My OS is WinXp v2002 SP2. This issue started after I deleted a database
> on my local server, prior to that it was working fine.
> Thanks
>
|||My machines gives an error if I don't use -U or -E. Did you try using the -E switch to login using
your Windows account?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Brent Stevenson" <essexbs@.insightbb.com> wrote in message
news:OCxkkudwFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Dan,
> Thanks for the reply! But infortunaley that fails with a "login failed for 'user' error."
> According to OSQL help: "If neither the -U or -P options are used, SQL Server 2000 attempts to
> connect using Windows Authentication Mode." Well that would be my network account & password and
> if I just enter OSQL -d master and then my network account password at the prompt, it fails (login
> failed for user). I know I'm correctly entering my current network account password.
> Any ideas?
> Thanks
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:Oex4GfRwFHA.464@.TK2MSFTNGP15.phx.gbl...
>
|||Using the -E go me in & allowed me to change my default DB & resolve the
issue.
Thanks a million!! Have a good day.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23vKLtxdwFHA.3556@.TK2MSFTNGP12.phx.gbl...
> My machines gives an error if I don't use -U or -E. Did you try using
> the -E switch to login using your Windows account?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Brent Stevenson" <essexbs@.insightbb.com> wrote in message
> news:OCxkkudwFHA.1028@.TK2MSFTNGP12.phx.gbl...
>
|||The example I posted assumed a SQL login. Like Tibor mentioned, you can
specify '-E' to establish a trusted connection using your current Windows
account. You never need to specify a Windows account with the '-E'
parameter but you still need to specify the name of the account to
sp_defaultdb. For a local Windows account:
OSQL -d master -E -Q"EXEC sp_defaultdb 'MyMachine\MyAccount','master'"
or for a domain account:
OSQL -d master -E -Q"EXEC sp_defaultdb 'MyDomain\MyAccount','master'"
If your SQL Server access is via membership of the local Administrators
group, you'll need to change the default database for that group instead:
OSQL -d master -E -Q"EXEC sp_defaultdb 'BUILTIN\Administrators','master'"
Hope this helps.
Dan Guzman
SQL Server MVP
"Brent Stevenson" <essexbs@.insightbb.com> wrote in message
news:OCxkkudwFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Dan,
> Thanks for the reply! But infortunaley that fails with a "login failed for
> 'user' error."
> According to OSQL help: "If neither the -U or -P options are used, SQL
> Server 2000 attempts to connect using Windows Authentication Mode." Well
> that would be my network account & password and if I just enter OSQL -d
> master and then my network account password at the prompt, it fails (login
> failed for user). I know I'm correctly entering my current network account
> password.
> Any ideas?
> Thanks
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:Oex4GfRwFHA.464@.TK2MSFTNGP15.phx.gbl...
>
|||I just had the same problem, and used this same solution. Deleted a
database, didn't realise it was my default DB (or that it mattered), and
couldn't figure out the account credentials problem I was having using the
command line suggestions found elsewhere. Was using a domain admin account.
Thanks very much!
Bryan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:u9z7NBewFHA.2540@.TK2MSFTNGP09.phx.gbl...
> The example I posted assumed a SQL login. Like Tibor mentioned, you can
> specify '-E' to establish a trusted connection using your current Windows
> account. You never need to specify a Windows account with the '-E'
> parameter but you still need to specify the name of the account to
> sp_defaultdb. For a local Windows account:
> OSQL -d master -E -Q"EXEC sp_defaultdb 'MyMachine\MyAccount','master'"
> or for a domain account:
> OSQL -d master -E -Q"EXEC sp_defaultdb 'MyDomain\MyAccount','master'"
> If your SQL Server access is via membership of the local Administrators
> group, you'll need to change the default database for that group instead:
> OSQL -d master -E -Q"EXEC sp_defaultdb 'BUILTIN\Administrators','master'"
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Brent Stevenson" <essexbs@.insightbb.com> wrote in message
> news:OCxkkudwFHA.1028@.TK2MSFTNGP12.phx.gbl...
>
Subscribe to:
Posts (Atom)
