I have a question about transaction when lock_timeout error occurs
the procedure as follows,
CREATE PROCEDURE dbo.usp_glock
AS
BEGIN
SET NOCOUNT ON
SET LOCK_TIMEOUT 2000
UPDATE glocktbl SET name='john'
WHERE id=1
IF @.@.Error<>0
BEGIN
GOTO Err_Handle
END
Return 0
Err_Handle:
DECLARE @.intID INT
--/*
DECLARE cursor_Sql CURSOR
LOCAL
FORWARD_ONLY
STATIC
FOR
SELECT TOP 1 id FROM gcurtbl
OPEN cursor_Sql
FETCH NEXT FROM cursor_Sql
INTO @.intID
WHILE @.@.FETCH_STATUS = 0
BEGIN
print @.intID
End
Close cursor_Sql
--*/
insert into gerrtbl (errdesc)
values('Lock time out error.')
END
When lock happens, then error message 1222, "Lock request time-out period
exceeded" was catched by error handle. In normal case, the transaction will
not be rolled back, and this store procedure can continue to next statement
,until execute 'insert into gerrtbl (errdesc) values('Lock time out
error.')'.but actually,this procedure terminated when execute 'OPEN
cursor_Sql FETCH NEXT FROM cursor_Sql'.
who can help me explain such phenomenon?
thanks a lot.Try setting the lock-timeout to 0 (indefinite) or increase it as appropriate
.
SET LOCK_TIMEOUT 0;
http://msdn.microsoft.com/library/d... />
a_5n78.asp
http://msdn.microsoft.com/library/d... />
t_1yr8.asp
ML
http://milambda.blogspot.com/|||use 'Exec dbo.usp_glock'
the transaction will not be rolled back when lock happens.
thanks a lot.
--
wq352
"ML" wrote:
> Try setting the lock-timeout to 0 (indefinite) or increase it as appropria
te.
> SET LOCK_TIMEOUT 0;
> http://msdn.microsoft.com/library/d...>
_7a_5n78.asp
> http://msdn.microsoft.com/library/d...>
set_1yr8.asp
> ML
> --
> http://milambda.blogspot.com/|||So, what measures have you taken to solve the problem? Have you increased th
e
timeout or turned it off?
ML
http://milambda.blogspot.com/|||On Tue, 16 May 2006 09:04:02 -0700, wq352 wrote:
(snip)
>this procedure terminated when execute 'OPEN
>cursor_Sql FETCH NEXT FROM cursor_Sql'.
Hi wq352,
Since you didn't post an error message, I tried to reproduce it. I had
to change some table names to make it run. After that, I didn't get any
error message - instead, I got into an endless loop here:
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> print @.intID
> End
Generally, a loop that starts wiith WHILE @.@.FETCH_STATUS = 0 should
include at least one FETCH statement. This loop holds only a PRINT
statement, which will never change the value of @.@.FETCH_STATUS.
However, I also fail to see why you use a looop at all - considering
that you include a TOP 1 clause, you'll get just one row annyway and
there's no need to use a cursor at all.
Err_Handle:
DECLARE @.intID INT
--/*
SET @.intID = (SELECT TOP 1 id FROM gcurtbl)
PRINT @.intID
--*/
insert into gerrtbl (errdesc)
values('Lock time out error.')
Another important note - using TOP without ORDER BY means that you're
getting just one row, but it's unpredictable what row it will be. Are
you sure that that's what yoou want?
Hugo Kornelis, SQL Server MVP
Showing posts with label follows. Show all posts
Showing posts with label follows. Show all posts
Friday, March 30, 2012
Friday, March 9, 2012
localhost giving problem
WIN XP
Windows 2000
we have a standard build script as follows to build a database locally.
osql -d Master -i script name -o script log -E -S localhost
The script fails with the following error message
[TCP/IP Sockets] SQL Server does not exist or access denied
[TCP/IP Sockets]ConnectionOpen (Connect())
If -S option is removed, the script runs fine.
I Googled on this problem and did the following.
In the client library I disabled Named Piped protocol and enabled only
TCP/IP protocols.
Stopped and restarted SQL Server.
I also added an alias localhost with network libary set to TCP/IP. Unchecked
Dynically check port
number and set the port to 1433.
Still the problem persists.
BTW this is a new build machine and Sql server is installed for the first
time on that machine.
TIA
Did you try -S 127.0.0.1 or -S (local) or -S <the actual machine name>?
Or, why can't you just leave the -S parameter off?
http://www.aspfaq.com/
(Reverse address to reply.)
"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
news:1109697584.61f025d955437a9ee32b16bfa728bcfd@.t eranews...
> WIN XP
> Windows 2000
> we have a standard build script as follows to build a database locally.
> osql -d Master -i script name -o script log -E -S localhost
> The script fails with the following error message
> [TCP/IP Sockets] SQL Server does not exist or access denied
> [TCP/IP Sockets]ConnectionOpen (Connect())
> If -S option is removed, the script runs fine.
> I Googled on this problem and did the following.
> In the client library I disabled Named Piped protocol and enabled only
> TCP/IP protocols.
> Stopped and restarted SQL Server.
> I also added an alias localhost with network libary set to TCP/IP.
Unchecked
> Dynically check port
> number and set the port to 1433.
> Still the problem persists.
> BTW this is a new build machine and Sql server is installed for the first
> time on that machine.
> TIA
>
|||I'm not following why you need to specify the name of the server...
according to BOL:
The -S parameter, needs:
[-S server_name[\instance_name]]
Further:
"If you do not specify a server, the name of the workstation is used."
I'd recommend leaving the -S off.
Steve
"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
news:1109697584.61f025d955437a9ee32b16bfa728bcfd@.t eranews...
> WIN XP
> Windows 2000
> we have a standard build script as follows to build a database locally.
> osql -d Master -i script name -o script log -E -S localhost
> The script fails with the following error message
> [TCP/IP Sockets] SQL Server does not exist or access denied
> [TCP/IP Sockets]ConnectionOpen (Connect())
> If -S option is removed, the script runs fine.
> I Googled on this problem and did the following.
> In the client library I disabled Named Piped protocol and enabled only
> TCP/IP protocols.
> Stopped and restarted SQL Server.
> I also added an alias localhost with network libary set to TCP/IP.
Unchecked
> Dynically check port
> number and set the port to 1433.
> Still the problem persists.
> BTW this is a new build machine and Sql server is installed for the first
> time on that machine.
> TIA
>
|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23GiDLPoHFHA.1528@.TK2MSFTNGP09.phx.gbl...
> Did you try -S 127.0.0.1 or -S (local) or -S <the actual machine name>?
> Or, why can't you just leave the -S parameter off?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
I will have to do a big justification on why -S should be removed. I just
joined this project.
These scripts were running fine on older machines. The problem is with this
new machine only.
changing the script (removing -S) will be my last option.
thanks all.
|||"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
news:1109698545.da57de61dec89a895ce5ac06b889c11e@.t eranews...
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:%23GiDLPoHFHA.1528@.TK2MSFTNGP09.phx.gbl...
> I will have to do a big justification on why -S should be removed. I just
> joined this project.
> These scripts were running fine on older machines. The problem is with
> this new machine only.
> changing the script (removing -S) will be my last option.
I have another machine where this script works. However that machine is Win
2000 [ version 5.00.2195].
The one which is giving problem is WIn XP [ version 5.1.2600 ].
Is this a useful information?
|||> I will have to do a big justification on why -S should be removed.
Other than "it breaks, and is not necessary anyway"?
|||> I have another machine where this script works. However that machine is
Win
> 2000 [ version 5.00.2195].
> The one which is giving problem is WIn XP [ version 5.1.2600 ].
> Is this a useful information?
That depends. Did you try any of my other suggestions?
1. .
2. (local)
3. 127.0.0.1
4. <the actual IP address>
5. <the actual workstation name>
6. "do a big justification" on leaving off the unnecessary parameter
|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:etYDHeoHFHA.2356@.TK2MSFTNGP12.phx.gbl...
> Win
> That depends. Did you try any of my other suggestions?
> 1. .
> 2. (local)
success
> 3. 127.0.0.1
(failed)
> 4. <the actual IP address>
> 5. <the actual workstation name>
> 6. "do a big justification" on leaving off the unnecessary parameter
did not try.
|||"SQL Server DBA" <sqlsdba@.gmail.com> wrote[vbcol=seagreen]
> success
sorry. this one too works.?
so we have following two commands working
osql -d Master -i file.sql -o file.out -E -S (local)
osql -d Master -i file.sql -o file.out -E -S 127.0.0.1
but this one fails
osql -d Master -i file.sql -o file.out -E -S localhost
[TCP/IP Sockets] SQL Server does not exist or access denied
[TCP/IP Sockets]ConnectionOpen (Connect())
I double checked C:\WINDOWS\SYSTEM32\DRIVERS\etc\hosts file and saw an entry
for 127.0.0.1 localhost.
so why only localhost is failing.
|||"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
news:1109707082.5fa894f609537b1ba2e3d37d057d3898@.t eranews...
> "SQL Server DBA" <sqlsdba@.gmail.com> wrote
> sorry. this one too works.?
>
> so we have following two commands working
> osql -d Master -i file.sql -o file.out -E -S (local)
> osql -d Master -i file.sql -o file.out -E -S 127.0.0.1
> but this one fails
> osql -d Master -i file.sql -o file.out -E -S localhost
even IP address works
osql -d Master -i file.sql -o file.out -E -S 170.x.x.x
runs OK.
Windows 2000
we have a standard build script as follows to build a database locally.
osql -d Master -i script name -o script log -E -S localhost
The script fails with the following error message
[TCP/IP Sockets] SQL Server does not exist or access denied
[TCP/IP Sockets]ConnectionOpen (Connect())
If -S option is removed, the script runs fine.
I Googled on this problem and did the following.
In the client library I disabled Named Piped protocol and enabled only
TCP/IP protocols.
Stopped and restarted SQL Server.
I also added an alias localhost with network libary set to TCP/IP. Unchecked
Dynically check port
number and set the port to 1433.
Still the problem persists.
BTW this is a new build machine and Sql server is installed for the first
time on that machine.
TIA
Did you try -S 127.0.0.1 or -S (local) or -S <the actual machine name>?
Or, why can't you just leave the -S parameter off?
http://www.aspfaq.com/
(Reverse address to reply.)
"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
news:1109697584.61f025d955437a9ee32b16bfa728bcfd@.t eranews...
> WIN XP
> Windows 2000
> we have a standard build script as follows to build a database locally.
> osql -d Master -i script name -o script log -E -S localhost
> The script fails with the following error message
> [TCP/IP Sockets] SQL Server does not exist or access denied
> [TCP/IP Sockets]ConnectionOpen (Connect())
> If -S option is removed, the script runs fine.
> I Googled on this problem and did the following.
> In the client library I disabled Named Piped protocol and enabled only
> TCP/IP protocols.
> Stopped and restarted SQL Server.
> I also added an alias localhost with network libary set to TCP/IP.
Unchecked
> Dynically check port
> number and set the port to 1433.
> Still the problem persists.
> BTW this is a new build machine and Sql server is installed for the first
> time on that machine.
> TIA
>
|||I'm not following why you need to specify the name of the server...
according to BOL:
The -S parameter, needs:
[-S server_name[\instance_name]]
Further:
"If you do not specify a server, the name of the workstation is used."
I'd recommend leaving the -S off.
Steve
"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
news:1109697584.61f025d955437a9ee32b16bfa728bcfd@.t eranews...
> WIN XP
> Windows 2000
> we have a standard build script as follows to build a database locally.
> osql -d Master -i script name -o script log -E -S localhost
> The script fails with the following error message
> [TCP/IP Sockets] SQL Server does not exist or access denied
> [TCP/IP Sockets]ConnectionOpen (Connect())
> If -S option is removed, the script runs fine.
> I Googled on this problem and did the following.
> In the client library I disabled Named Piped protocol and enabled only
> TCP/IP protocols.
> Stopped and restarted SQL Server.
> I also added an alias localhost with network libary set to TCP/IP.
Unchecked
> Dynically check port
> number and set the port to 1433.
> Still the problem persists.
> BTW this is a new build machine and Sql server is installed for the first
> time on that machine.
> TIA
>
|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23GiDLPoHFHA.1528@.TK2MSFTNGP09.phx.gbl...
> Did you try -S 127.0.0.1 or -S (local) or -S <the actual machine name>?
> Or, why can't you just leave the -S parameter off?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
I will have to do a big justification on why -S should be removed. I just
joined this project.
These scripts were running fine on older machines. The problem is with this
new machine only.
changing the script (removing -S) will be my last option.
thanks all.
|||"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
news:1109698545.da57de61dec89a895ce5ac06b889c11e@.t eranews...
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:%23GiDLPoHFHA.1528@.TK2MSFTNGP09.phx.gbl...
> I will have to do a big justification on why -S should be removed. I just
> joined this project.
> These scripts were running fine on older machines. The problem is with
> this new machine only.
> changing the script (removing -S) will be my last option.
I have another machine where this script works. However that machine is Win
2000 [ version 5.00.2195].
The one which is giving problem is WIn XP [ version 5.1.2600 ].
Is this a useful information?
|||> I will have to do a big justification on why -S should be removed.
Other than "it breaks, and is not necessary anyway"?
|||> I have another machine where this script works. However that machine is
Win
> 2000 [ version 5.00.2195].
> The one which is giving problem is WIn XP [ version 5.1.2600 ].
> Is this a useful information?
That depends. Did you try any of my other suggestions?
1. .
2. (local)
3. 127.0.0.1
4. <the actual IP address>
5. <the actual workstation name>
6. "do a big justification" on leaving off the unnecessary parameter
|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:etYDHeoHFHA.2356@.TK2MSFTNGP12.phx.gbl...
> Win
> That depends. Did you try any of my other suggestions?
> 1. .
> 2. (local)
success
> 3. 127.0.0.1
(failed)
> 4. <the actual IP address>
> 5. <the actual workstation name>
> 6. "do a big justification" on leaving off the unnecessary parameter
did not try.
|||"SQL Server DBA" <sqlsdba@.gmail.com> wrote[vbcol=seagreen]
> success
sorry. this one too works.?
so we have following two commands working
osql -d Master -i file.sql -o file.out -E -S (local)
osql -d Master -i file.sql -o file.out -E -S 127.0.0.1
but this one fails
osql -d Master -i file.sql -o file.out -E -S localhost
[TCP/IP Sockets] SQL Server does not exist or access denied
[TCP/IP Sockets]ConnectionOpen (Connect())
I double checked C:\WINDOWS\SYSTEM32\DRIVERS\etc\hosts file and saw an entry
for 127.0.0.1 localhost.
so why only localhost is failing.
|||"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
news:1109707082.5fa894f609537b1ba2e3d37d057d3898@.t eranews...
> "SQL Server DBA" <sqlsdba@.gmail.com> wrote
> sorry. this one too works.?
>
> so we have following two commands working
> osql -d Master -i file.sql -o file.out -E -S (local)
> osql -d Master -i file.sql -o file.out -E -S 127.0.0.1
> but this one fails
> osql -d Master -i file.sql -o file.out -E -S localhost
even IP address works
osql -d Master -i file.sql -o file.out -E -S 170.x.x.x
runs OK.
localhost giving problem
WIN XP
Windows 2000
we have a standard build script as follows to build a database locally.
osql -d Master -i script name -o script log -E -S localhost
The script fails with the following error message
[TCP/IP Sockets] SQL Server does not exist or access denied
[TCP/IP Sockets]ConnectionOpen (Connect())
If -S option is removed, the script runs fine.
I Googled on this problem and did the following.
In the client library I disabled Named Piped protocol and enabled only
TCP/IP protocols.
Stopped and restarted SQL Server.
I also added an alias localhost with network libary set to TCP/IP. Unchecked
Dynically check port
number and set the port to 1433.
Still the problem persists.
BTW this is a new build machine and Sql server is installed for the first
time on that machine.
TIADid you try -S 127.0.0.1 or -S (local) or -S <the actual machine name>?
Or, why can't you just leave the -S parameter off?
http://www.aspfaq.com/
(Reverse address to reply.)
"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
news:1109697584. 61f025d955437a9ee32b16bfa728bcfd@.teranew
s...
> WIN XP
> Windows 2000
> we have a standard build script as follows to build a database locally.
> osql -d Master -i script name -o script log -E -S localhost
> The script fails with the following error message
> [TCP/IP Sockets] SQL Server does not exist or access denied
> [TCP/IP Sockets]ConnectionOpen (Connect())
> If -S option is removed, the script runs fine.
> I Googled on this problem and did the following.
> In the client library I disabled Named Piped protocol and enabled only
> TCP/IP protocols.
> Stopped and restarted SQL Server.
> I also added an alias localhost with network libary set to TCP/IP.
Unchecked
> Dynically check port
> number and set the port to 1433.
> Still the problem persists.
> BTW this is a new build machine and Sql server is installed for the first
> time on that machine.
> TIA
>|||I'm not following why you need to specify the name of the server...
according to BOL:
The -S parameter, needs:
[-S server_name[\instance_name]]
Further:
"If you do not specify a server, the name of the workstation is used."
I'd recommend leaving the -S off.
Steve
"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
news:1109697584. 61f025d955437a9ee32b16bfa728bcfd@.teranew
s...
> WIN XP
> Windows 2000
> we have a standard build script as follows to build a database locally.
> osql -d Master -i script name -o script log -E -S localhost
> The script fails with the following error message
> [TCP/IP Sockets] SQL Server does not exist or access denied
> [TCP/IP Sockets]ConnectionOpen (Connect())
> If -S option is removed, the script runs fine.
> I Googled on this problem and did the following.
> In the client library I disabled Named Piped protocol and enabled only
> TCP/IP protocols.
> Stopped and restarted SQL Server.
> I also added an alias localhost with network libary set to TCP/IP.
Unchecked
> Dynically check port
> number and set the port to 1433.
> Still the problem persists.
> BTW this is a new build machine and Sql server is installed for the first
> time on that machine.
> TIA
>|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23GiDLPoHFHA.1528@.TK2MSFTNGP09.phx.gbl...
> Did you try -S 127.0.0.1 or -S (local) or -S <the actual machine name>?
> Or, why can't you just leave the -S parameter off?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
I will have to do a big justification on why -S should be removed. I just
joined this project.
These scripts were running fine on older machines. The problem is with this
new machine only.
changing the script (removing -S) will be my last option.
thanks all.|||"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
news:1109698545. da57de61dec89a895ce5ac06b889c11e@.teranew
s...
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:%23GiDLPoHFHA.1528@.TK2MSFTNGP09.phx.gbl...
> I will have to do a big justification on why -S should be removed. I just
> joined this project.
> These scripts were running fine on older machines. The problem is with
> this new machine only.
> changing the script (removing -S) will be my last option.
I have another machine where this script works. However that machine is Win
2000 [ version 5.00.2195].
The one which is giving problem is WIn XP [ version 5.1.2600 ].
Is this a useful information?|||> I will have to do a big justification on why -S should be removed.
Other than "it breaks, and is not necessary anyway"?|||> I have another machine where this script works. However that machine is
Win
> 2000 [ version 5.00.2195].
> The one which is giving problem is WIn XP [ version 5.1.2600 ].
> Is this a useful information?
That depends. Did you try any of my other suggestions?
1. .
2. (local)
3. 127.0.0.1
4. <the actual IP address>
5. <the actual workstation name>
6. "do a big justification" on leaving off the unnecessary parameter|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:etYDHeoHFHA.2356@.TK2MSFTNGP12.phx.gbl...
> Win
> That depends. Did you try any of my other suggestions?
> 1. .
> 2. (local)
success
> 3. 127.0.0.1
(failed)
> 4. <the actual IP address>
> 5. <the actual workstation name>
> 6. "do a big justification" on leaving off the unnecessary parameter
did not try.|||"SQL Server DBA" <sqlsdba@.gmail.com> wrote[vbcol=seagreen]
> success
>
sorry. this one too works.?
so we have following two commands working
osql -d Master -i file.sql -o file.out -E -S (local)
osql -d Master -i file.sql -o file.out -E -S 127.0.0.1
but this one fails
osql -d Master -i file.sql -o file.out -E -S localhost
[TCP/IP Sockets] SQL Server does not exist or access denied
[TCP/IP Sockets]ConnectionOpen (Connect())
I double checked C:\WINDOWS\SYSTEM32\DRIVERS\etc\hosts file and saw an entry
for 127.0.0.1 localhost.
so why only localhost is failing.|||"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
news:1109707082. 5fa894f609537b1ba2e3d37d057d3898@.teranew
s...
> "SQL Server DBA" <sqlsdba@.gmail.com> wrote
> sorry. this one too works.?
>
> so we have following two commands working
> osql -d Master -i file.sql -o file.out -E -S (local)
> osql -d Master -i file.sql -o file.out -E -S 127.0.0.1
> but this one fails
> osql -d Master -i file.sql -o file.out -E -S localhost
even IP address works
osql -d Master -i file.sql -o file.out -E -S 170.x.x.x
runs OK.
Windows 2000
we have a standard build script as follows to build a database locally.
osql -d Master -i script name -o script log -E -S localhost
The script fails with the following error message
[TCP/IP Sockets] SQL Server does not exist or access denied
[TCP/IP Sockets]ConnectionOpen (Connect())
If -S option is removed, the script runs fine.
I Googled on this problem and did the following.
In the client library I disabled Named Piped protocol and enabled only
TCP/IP protocols.
Stopped and restarted SQL Server.
I also added an alias localhost with network libary set to TCP/IP. Unchecked
Dynically check port
number and set the port to 1433.
Still the problem persists.
BTW this is a new build machine and Sql server is installed for the first
time on that machine.
TIADid you try -S 127.0.0.1 or -S (local) or -S <the actual machine name>?
Or, why can't you just leave the -S parameter off?
http://www.aspfaq.com/
(Reverse address to reply.)
"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
news:1109697584. 61f025d955437a9ee32b16bfa728bcfd@.teranew
s...
> WIN XP
> Windows 2000
> we have a standard build script as follows to build a database locally.
> osql -d Master -i script name -o script log -E -S localhost
> The script fails with the following error message
> [TCP/IP Sockets] SQL Server does not exist or access denied
> [TCP/IP Sockets]ConnectionOpen (Connect())
> If -S option is removed, the script runs fine.
> I Googled on this problem and did the following.
> In the client library I disabled Named Piped protocol and enabled only
> TCP/IP protocols.
> Stopped and restarted SQL Server.
> I also added an alias localhost with network libary set to TCP/IP.
Unchecked
> Dynically check port
> number and set the port to 1433.
> Still the problem persists.
> BTW this is a new build machine and Sql server is installed for the first
> time on that machine.
> TIA
>|||I'm not following why you need to specify the name of the server...
according to BOL:
The -S parameter, needs:
[-S server_name[\instance_name]]
Further:
"If you do not specify a server, the name of the workstation is used."
I'd recommend leaving the -S off.
Steve
"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
news:1109697584. 61f025d955437a9ee32b16bfa728bcfd@.teranew
s...
> WIN XP
> Windows 2000
> we have a standard build script as follows to build a database locally.
> osql -d Master -i script name -o script log -E -S localhost
> The script fails with the following error message
> [TCP/IP Sockets] SQL Server does not exist or access denied
> [TCP/IP Sockets]ConnectionOpen (Connect())
> If -S option is removed, the script runs fine.
> I Googled on this problem and did the following.
> In the client library I disabled Named Piped protocol and enabled only
> TCP/IP protocols.
> Stopped and restarted SQL Server.
> I also added an alias localhost with network libary set to TCP/IP.
Unchecked
> Dynically check port
> number and set the port to 1433.
> Still the problem persists.
> BTW this is a new build machine and Sql server is installed for the first
> time on that machine.
> TIA
>|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23GiDLPoHFHA.1528@.TK2MSFTNGP09.phx.gbl...
> Did you try -S 127.0.0.1 or -S (local) or -S <the actual machine name>?
> Or, why can't you just leave the -S parameter off?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
I will have to do a big justification on why -S should be removed. I just
joined this project.
These scripts were running fine on older machines. The problem is with this
new machine only.
changing the script (removing -S) will be my last option.
thanks all.|||"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
news:1109698545. da57de61dec89a895ce5ac06b889c11e@.teranew
s...
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:%23GiDLPoHFHA.1528@.TK2MSFTNGP09.phx.gbl...
> I will have to do a big justification on why -S should be removed. I just
> joined this project.
> These scripts were running fine on older machines. The problem is with
> this new machine only.
> changing the script (removing -S) will be my last option.
I have another machine where this script works. However that machine is Win
2000 [ version 5.00.2195].
The one which is giving problem is WIn XP [ version 5.1.2600 ].
Is this a useful information?|||> I will have to do a big justification on why -S should be removed.
Other than "it breaks, and is not necessary anyway"?|||> I have another machine where this script works. However that machine is
Win
> 2000 [ version 5.00.2195].
> The one which is giving problem is WIn XP [ version 5.1.2600 ].
> Is this a useful information?
That depends. Did you try any of my other suggestions?
1. .
2. (local)
3. 127.0.0.1
4. <the actual IP address>
5. <the actual workstation name>
6. "do a big justification" on leaving off the unnecessary parameter|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:etYDHeoHFHA.2356@.TK2MSFTNGP12.phx.gbl...
> Win
> That depends. Did you try any of my other suggestions?
> 1. .
> 2. (local)
success
> 3. 127.0.0.1
(failed)
> 4. <the actual IP address>
> 5. <the actual workstation name>
> 6. "do a big justification" on leaving off the unnecessary parameter
did not try.|||"SQL Server DBA" <sqlsdba@.gmail.com> wrote[vbcol=seagreen]
> success
>
sorry. this one too works.?
so we have following two commands working
osql -d Master -i file.sql -o file.out -E -S (local)
osql -d Master -i file.sql -o file.out -E -S 127.0.0.1
but this one fails
osql -d Master -i file.sql -o file.out -E -S localhost
[TCP/IP Sockets] SQL Server does not exist or access denied
[TCP/IP Sockets]ConnectionOpen (Connect())
I double checked C:\WINDOWS\SYSTEM32\DRIVERS\etc\hosts file and saw an entry
for 127.0.0.1 localhost.
so why only localhost is failing.|||"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
news:1109707082. 5fa894f609537b1ba2e3d37d057d3898@.teranew
s...
> "SQL Server DBA" <sqlsdba@.gmail.com> wrote
> sorry. this one too works.?
>
> so we have following two commands working
> osql -d Master -i file.sql -o file.out -E -S (local)
> osql -d Master -i file.sql -o file.out -E -S 127.0.0.1
> but this one fails
> osql -d Master -i file.sql -o file.out -E -S localhost
even IP address works
osql -d Master -i file.sql -o file.out -E -S 170.x.x.x
runs OK.
localhost giving problem
WIN XP
Windows 2000
we have a standard build script as follows to build a database locally.
osql -d Master -i script name -o script log -E -S localhost
The script fails with the following error message
[TCP/IP Sockets] SQL Server does not exist or access denied
[TCP/IP Sockets]ConnectionOpen (Connect())
If -S option is removed, the script runs fine.
I Googled on this problem and did the following.
In the client library I disabled Named Piped protocol and enabled only
TCP/IP protocols.
Stopped and restarted SQL Server.
I also added an alias localhost with network libary set to TCP/IP. Unchecked
Dynically check port
number and set the port to 1433.
Still the problem persists.
BTW this is a new build machine and Sql server is installed for the first
time on that machine.
TIADid you try -S 127.0.0.1 or -S (local) or -S <the actual machine name>?
Or, why can't you just leave the -S parameter off?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
news:1109697584.61f025d955437a9ee32b16bfa728bcfd@.teranews...
> WIN XP
> Windows 2000
> we have a standard build script as follows to build a database locally.
> osql -d Master -i script name -o script log -E -S localhost
> The script fails with the following error message
> [TCP/IP Sockets] SQL Server does not exist or access denied
> [TCP/IP Sockets]ConnectionOpen (Connect())
> If -S option is removed, the script runs fine.
> I Googled on this problem and did the following.
> In the client library I disabled Named Piped protocol and enabled only
> TCP/IP protocols.
> Stopped and restarted SQL Server.
> I also added an alias localhost with network libary set to TCP/IP.
Unchecked
> Dynically check port
> number and set the port to 1433.
> Still the problem persists.
> BTW this is a new build machine and Sql server is installed for the first
> time on that machine.
> TIA
>|||I'm not following why you need to specify the name of the server...
according to BOL:
The -S parameter, needs:
[-S server_name[\instance_name]]
Further:
"If you do not specify a server, the name of the workstation is used."
I'd recommend leaving the -S off.
Steve
"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
news:1109697584.61f025d955437a9ee32b16bfa728bcfd@.teranews...
> WIN XP
> Windows 2000
> we have a standard build script as follows to build a database locally.
> osql -d Master -i script name -o script log -E -S localhost
> The script fails with the following error message
> [TCP/IP Sockets] SQL Server does not exist or access denied
> [TCP/IP Sockets]ConnectionOpen (Connect())
> If -S option is removed, the script runs fine.
> I Googled on this problem and did the following.
> In the client library I disabled Named Piped protocol and enabled only
> TCP/IP protocols.
> Stopped and restarted SQL Server.
> I also added an alias localhost with network libary set to TCP/IP.
Unchecked
> Dynically check port
> number and set the port to 1433.
> Still the problem persists.
> BTW this is a new build machine and Sql server is installed for the first
> time on that machine.
> TIA
>|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23GiDLPoHFHA.1528@.TK2MSFTNGP09.phx.gbl...
> Did you try -S 127.0.0.1 or -S (local) or -S <the actual machine name>?
> Or, why can't you just leave the -S parameter off?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
I will have to do a big justification on why -S should be removed. I just
joined this project.
These scripts were running fine on older machines. The problem is with this
new machine only.
changing the script (removing -S) will be my last option.
thanks all.|||"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
news:1109698545.da57de61dec89a895ce5ac06b889c11e@.teranews...
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:%23GiDLPoHFHA.1528@.TK2MSFTNGP09.phx.gbl...
>> Did you try -S 127.0.0.1 or -S (local) or -S <the actual machine name>?
>> Or, why can't you just leave the -S parameter off?
>> --
>> http://www.aspfaq.com/
>> (Reverse address to reply.)
> I will have to do a big justification on why -S should be removed. I just
> joined this project.
> These scripts were running fine on older machines. The problem is with
> this new machine only.
> changing the script (removing -S) will be my last option.
I have another machine where this script works. However that machine is Win
2000 [ version 5.00.2195].
The one which is giving problem is WIn XP [ version 5.1.2600 ].
Is this a useful information?|||> I will have to do a big justification on why -S should be removed.
Other than "it breaks, and is not necessary anyway"?|||> I have another machine where this script works. However that machine is
Win
> 2000 [ version 5.00.2195].
> The one which is giving problem is WIn XP [ version 5.1.2600 ].
> Is this a useful information?
That depends. Did you try any of my other suggestions?
1. .
2. (local)
3. 127.0.0.1
4. <the actual IP address>
5. <the actual workstation name>
6. "do a big justification" on leaving off the unnecessary parameter|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:etYDHeoHFHA.2356@.TK2MSFTNGP12.phx.gbl...
>> I have another machine where this script works. However that machine is
> Win
>> 2000 [ version 5.00.2195].
>> The one which is giving problem is WIn XP [ version 5.1.2600 ].
>> Is this a useful information?
> That depends. Did you try any of my other suggestions?
> 1. .
> 2. (local)
success
> 3. 127.0.0.1
(failed)
> 4. <the actual IP address>
> 5. <the actual workstation name>
> 6. "do a big justification" on leaving off the unnecessary parameter
did not try.|||"SQL Server DBA" <sqlsdba@.gmail.com> wrote
>> 1. .
>> 2. (local)
> success
>> 3. 127.0.0.1
sorry. this one too works.?
so we have following two commands working
osql -d Master -i file.sql -o file.out -E -S (local)
osql -d Master -i file.sql -o file.out -E -S 127.0.0.1
but this one fails
osql -d Master -i file.sql -o file.out -E -S localhost
[TCP/IP Sockets] SQL Server does not exist or access denied
[TCP/IP Sockets]ConnectionOpen (Connect())
I double checked C:\WINDOWS\SYSTEM32\DRIVERS\etc\hosts file and saw an entry
for 127.0.0.1 localhost.
so why only localhost is failing.|||"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
news:1109707082.5fa894f609537b1ba2e3d37d057d3898@.teranews...
> "SQL Server DBA" <sqlsdba@.gmail.com> wrote
>> 1. .
>> 2. (local)
>> success
>> 3. 127.0.0.1
> sorry. this one too works.?
>
> so we have following two commands working
> osql -d Master -i file.sql -o file.out -E -S (local)
> osql -d Master -i file.sql -o file.out -E -S 127.0.0.1
> but this one fails
> osql -d Master -i file.sql -o file.out -E -S localhost
even IP address works
osql -d Master -i file.sql -o file.out -E -S 170.x.x.x
runs OK.|||> for 127.0.0.1 localhost.
Is there really a period at the end?
What happens when you ping localhost from a command line?
Did you check the SQL Server Client Network Utility, to see if perhaps you
have a different alias for localhost?
Maybe you have certain things disabled on XP by default (e.g. I remember
something a whileback about a loopback connector causing problems for
accessing http://localhost/ in certain network configurations.)
Anyway, when I throw a baseball with my right hand, it goes pretty far.
When I use my left hand, it doesn't go quite so far, and people laugh at me.
In other words, USE WHAT WORKS!|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23nffD2pHFHA.3588@.TK2MSFTNGP14.phx.gbl...
>> for 127.0.0.1 localhost.
> Is there really a period at the end?
nope.
> What happens when you ping localhost from a command line?
ping 127.0.0.1 and ping localhost work fine, as they should.
> Did you check the SQL Server Client Network Utility, to see if perhaps you
> have a different alias for localhost?
I did and it at last it worked. Hooray.
I added an alias localhost using TCP/IP which talks to local server. I had
earlier
tried it and it failed. However this time I entered the IP address in the
SERVER
NAME. Bingo. It works.
> Anyway, when I throw a baseball with my right hand, it goes pretty far.
> When I use my left hand, it doesn't go quite so far, and people laugh at
> me.
> In other words, USE WHAT WORKS!
LOL.
However in this case, my right hand is tied to a rope controlled by others.
So I am forced to use
left hand. At least I don't have to depend on others.
Thanks for your input.
Windows 2000
we have a standard build script as follows to build a database locally.
osql -d Master -i script name -o script log -E -S localhost
The script fails with the following error message
[TCP/IP Sockets] SQL Server does not exist or access denied
[TCP/IP Sockets]ConnectionOpen (Connect())
If -S option is removed, the script runs fine.
I Googled on this problem and did the following.
In the client library I disabled Named Piped protocol and enabled only
TCP/IP protocols.
Stopped and restarted SQL Server.
I also added an alias localhost with network libary set to TCP/IP. Unchecked
Dynically check port
number and set the port to 1433.
Still the problem persists.
BTW this is a new build machine and Sql server is installed for the first
time on that machine.
TIADid you try -S 127.0.0.1 or -S (local) or -S <the actual machine name>?
Or, why can't you just leave the -S parameter off?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
news:1109697584.61f025d955437a9ee32b16bfa728bcfd@.teranews...
> WIN XP
> Windows 2000
> we have a standard build script as follows to build a database locally.
> osql -d Master -i script name -o script log -E -S localhost
> The script fails with the following error message
> [TCP/IP Sockets] SQL Server does not exist or access denied
> [TCP/IP Sockets]ConnectionOpen (Connect())
> If -S option is removed, the script runs fine.
> I Googled on this problem and did the following.
> In the client library I disabled Named Piped protocol and enabled only
> TCP/IP protocols.
> Stopped and restarted SQL Server.
> I also added an alias localhost with network libary set to TCP/IP.
Unchecked
> Dynically check port
> number and set the port to 1433.
> Still the problem persists.
> BTW this is a new build machine and Sql server is installed for the first
> time on that machine.
> TIA
>|||I'm not following why you need to specify the name of the server...
according to BOL:
The -S parameter, needs:
[-S server_name[\instance_name]]
Further:
"If you do not specify a server, the name of the workstation is used."
I'd recommend leaving the -S off.
Steve
"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
news:1109697584.61f025d955437a9ee32b16bfa728bcfd@.teranews...
> WIN XP
> Windows 2000
> we have a standard build script as follows to build a database locally.
> osql -d Master -i script name -o script log -E -S localhost
> The script fails with the following error message
> [TCP/IP Sockets] SQL Server does not exist or access denied
> [TCP/IP Sockets]ConnectionOpen (Connect())
> If -S option is removed, the script runs fine.
> I Googled on this problem and did the following.
> In the client library I disabled Named Piped protocol and enabled only
> TCP/IP protocols.
> Stopped and restarted SQL Server.
> I also added an alias localhost with network libary set to TCP/IP.
Unchecked
> Dynically check port
> number and set the port to 1433.
> Still the problem persists.
> BTW this is a new build machine and Sql server is installed for the first
> time on that machine.
> TIA
>|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23GiDLPoHFHA.1528@.TK2MSFTNGP09.phx.gbl...
> Did you try -S 127.0.0.1 or -S (local) or -S <the actual machine name>?
> Or, why can't you just leave the -S parameter off?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
I will have to do a big justification on why -S should be removed. I just
joined this project.
These scripts were running fine on older machines. The problem is with this
new machine only.
changing the script (removing -S) will be my last option.
thanks all.|||"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
news:1109698545.da57de61dec89a895ce5ac06b889c11e@.teranews...
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:%23GiDLPoHFHA.1528@.TK2MSFTNGP09.phx.gbl...
>> Did you try -S 127.0.0.1 or -S (local) or -S <the actual machine name>?
>> Or, why can't you just leave the -S parameter off?
>> --
>> http://www.aspfaq.com/
>> (Reverse address to reply.)
> I will have to do a big justification on why -S should be removed. I just
> joined this project.
> These scripts were running fine on older machines. The problem is with
> this new machine only.
> changing the script (removing -S) will be my last option.
I have another machine where this script works. However that machine is Win
2000 [ version 5.00.2195].
The one which is giving problem is WIn XP [ version 5.1.2600 ].
Is this a useful information?|||> I will have to do a big justification on why -S should be removed.
Other than "it breaks, and is not necessary anyway"?|||> I have another machine where this script works. However that machine is
Win
> 2000 [ version 5.00.2195].
> The one which is giving problem is WIn XP [ version 5.1.2600 ].
> Is this a useful information?
That depends. Did you try any of my other suggestions?
1. .
2. (local)
3. 127.0.0.1
4. <the actual IP address>
5. <the actual workstation name>
6. "do a big justification" on leaving off the unnecessary parameter|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:etYDHeoHFHA.2356@.TK2MSFTNGP12.phx.gbl...
>> I have another machine where this script works. However that machine is
> Win
>> 2000 [ version 5.00.2195].
>> The one which is giving problem is WIn XP [ version 5.1.2600 ].
>> Is this a useful information?
> That depends. Did you try any of my other suggestions?
> 1. .
> 2. (local)
success
> 3. 127.0.0.1
(failed)
> 4. <the actual IP address>
> 5. <the actual workstation name>
> 6. "do a big justification" on leaving off the unnecessary parameter
did not try.|||"SQL Server DBA" <sqlsdba@.gmail.com> wrote
>> 1. .
>> 2. (local)
> success
>> 3. 127.0.0.1
sorry. this one too works.?
so we have following two commands working
osql -d Master -i file.sql -o file.out -E -S (local)
osql -d Master -i file.sql -o file.out -E -S 127.0.0.1
but this one fails
osql -d Master -i file.sql -o file.out -E -S localhost
[TCP/IP Sockets] SQL Server does not exist or access denied
[TCP/IP Sockets]ConnectionOpen (Connect())
I double checked C:\WINDOWS\SYSTEM32\DRIVERS\etc\hosts file and saw an entry
for 127.0.0.1 localhost.
so why only localhost is failing.|||"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
news:1109707082.5fa894f609537b1ba2e3d37d057d3898@.teranews...
> "SQL Server DBA" <sqlsdba@.gmail.com> wrote
>> 1. .
>> 2. (local)
>> success
>> 3. 127.0.0.1
> sorry. this one too works.?
>
> so we have following two commands working
> osql -d Master -i file.sql -o file.out -E -S (local)
> osql -d Master -i file.sql -o file.out -E -S 127.0.0.1
> but this one fails
> osql -d Master -i file.sql -o file.out -E -S localhost
even IP address works
osql -d Master -i file.sql -o file.out -E -S 170.x.x.x
runs OK.|||> for 127.0.0.1 localhost.
Is there really a period at the end?
What happens when you ping localhost from a command line?
Did you check the SQL Server Client Network Utility, to see if perhaps you
have a different alias for localhost?
Maybe you have certain things disabled on XP by default (e.g. I remember
something a whileback about a loopback connector causing problems for
accessing http://localhost/ in certain network configurations.)
Anyway, when I throw a baseball with my right hand, it goes pretty far.
When I use my left hand, it doesn't go quite so far, and people laugh at me.
In other words, USE WHAT WORKS!|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23nffD2pHFHA.3588@.TK2MSFTNGP14.phx.gbl...
>> for 127.0.0.1 localhost.
> Is there really a period at the end?
nope.
> What happens when you ping localhost from a command line?
ping 127.0.0.1 and ping localhost work fine, as they should.
> Did you check the SQL Server Client Network Utility, to see if perhaps you
> have a different alias for localhost?
I did and it at last it worked. Hooray.
I added an alias localhost using TCP/IP which talks to local server. I had
earlier
tried it and it failed. However this time I entered the IP address in the
SERVER
NAME. Bingo. It works.
> Anyway, when I throw a baseball with my right hand, it goes pretty far.
> When I use my left hand, it doesn't go quite so far, and people laugh at
> me.
> In other words, USE WHAT WORKS!
LOL.
However in this case, my right hand is tied to a rope controlled by others.
So I am forced to use
left hand. At least I don't have to depend on others.
Thanks for your input.
Subscribe to:
Posts (Atom)