Forgive me as I'm approaching a problem as someone familliar with
Oracle, so may be trying to solve it using techniques not applicable
for SQL Server.
I wonder if SQL Server allows me to partition tables. There's lots of
talk about distributed partitioning, but none about locally
partitioned tables. I wonder if SQL Server's query engine works in a
way that it isn't needed.
The reasons I'm looking for local partitioning are thus:
I can load the new fact data into one partition. It may not already
be empty, for example if I'm doing monthly loads into an annual
partition, but its going to be somewhat smaller than the whole fact
table. The index generation hit should be smaller with the partition.
I wonder if DTS/SQL Server supports bulk insert for extra speed.
My queries should take advantage of the partition. If I create
parallel partitions in Analysis Services, things should become faster
to maintain. For example - I only reprocess the cube for the partition
I've just loaded.
When data becomes too old to be interesting, I can just extract the
partition to a backup medium somewhere and drop it - easy as that. Say
goodbye to the partition in both SQL Server and in the Analysis
Services cube without any reprocessing hit on the rest of the data.
(Rolling sums become interesting on the boundary condition, or do
they, as I'm not reprocessing back there? hmmm. At my last employer I
made sure I kept data around long enough to satisfy the rolling sums,
so we queried to 3 years ago, and I kept 4 years online to satisfy
that)
Also, what editions of SQL Server support this kind of technology. Is
this an Enterprise Server type thing? At what point of fine graindness
do partitions become a heavy hit? Assuming I can automate all this
well, does it become sensible to use monthly partitions, or does the
cross partition query performance (12 of them for a Rolling 12 Month
or a Year To Date) drag things down?
Thanks
- Richard
In the current release of SQL Server, you can use partitioned views to
accomplish what you need. You'll need a partitioning column - enforced with
a check constraint - as part of the primary key in each member table. In
your case, it would be the relevant date. You then construct a view that
includes all members, hooked together with UNION ALL's. As you need to
add/drop a member table, simply alter the view to eliminate the old and add
the new from the definition. Then drop the un-needed table.
I don't believe you'll need the enterprise edition, since you're looking at
local partitioned views.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Richard Corfield" <richard.corfield@.gmail.com> wrote in message
news:c58fd37c.0410190647.cd2e11c@.posting.google.co m...
Forgive me as I'm approaching a problem as someone familliar with
Oracle, so may be trying to solve it using techniques not applicable
for SQL Server.
I wonder if SQL Server allows me to partition tables. There's lots of
talk about distributed partitioning, but none about locally
partitioned tables. I wonder if SQL Server's query engine works in a
way that it isn't needed.
The reasons I'm looking for local partitioning are thus:
I can load the new fact data into one partition. It may not already
be empty, for example if I'm doing monthly loads into an annual
partition, but its going to be somewhat smaller than the whole fact
table. The index generation hit should be smaller with the partition.
I wonder if DTS/SQL Server supports bulk insert for extra speed.
My queries should take advantage of the partition. If I create
parallel partitions in Analysis Services, things should become faster
to maintain. For example - I only reprocess the cube for the partition
I've just loaded.
When data becomes too old to be interesting, I can just extract the
partition to a backup medium somewhere and drop it - easy as that. Say
goodbye to the partition in both SQL Server and in the Analysis
Services cube without any reprocessing hit on the rest of the data.
(Rolling sums become interesting on the boundary condition, or do
they, as I'm not reprocessing back there? hmmm. At my last employer I
made sure I kept data around long enough to satisfy the rolling sums,
so we queried to 3 years ago, and I kept 4 years online to satisfy
that)
Also, what editions of SQL Server support this kind of technology. Is
this an Enterprise Server type thing? At what point of fine graindness
do partitions become a heavy hit? Assuming I can automate all this
well, does it become sensible to use monthly partitions, or does the
cross partition query performance (12 of them for a Rolling 12 Month
or a Year To Date) drag things down?
Thanks
- Richard
Showing posts with label locally. Show all posts
Showing posts with label locally. Show all posts
Monday, March 19, 2012
Monday, March 12, 2012
Locally Partitioned Tables for OLAP
Forgive me as I'm approaching a problem as someone familliar with
Oracle, so may be trying to solve it using techniques not applicable
for SQL Server.
I wonder if SQL Server allows me to partition tables. There's lots of
talk about distributed partitioning, but none about locally
partitioned tables. I wonder if SQL Server's query engine works in a
way that it isn't needed.
The reasons I'm looking for local partitioning are thus:
I can load the new fact data into one partition. It may not already
be empty, for example if I'm doing monthly loads into an annual
partition, but its going to be somewhat smaller than the whole fact
table. The index generation hit should be smaller with the partition.
I wonder if DTS/SQL Server supports bulk insert for extra speed.
My queries should take advantage of the partition. If I create
parallel partitions in Analysis Services, things should become faster
to maintain. For example - I only reprocess the cube for the partition
I've just loaded.
When data becomes too old to be interesting, I can just extract the
partition to a backup medium somewhere and drop it - easy as that. Say
goodbye to the partition in both SQL Server and in the Analysis
Services cube without any reprocessing hit on the rest of the data.
(Rolling sums become interesting on the boundary condition, or do
they, as I'm not reprocessing back there? hmmm. At my last employer I
made sure I kept data around long enough to satisfy the rolling sums,
so we queried to 3 years ago, and I kept 4 years online to satisfy
that)
Also, what editions of SQL Server support this kind of technology. Is
this an Enterprise Server type thing? At what point of fine graindness
do partitions become a heavy hit? Assuming I can automate all this
well, does it become sensible to use monthly partitions, or does the
cross partition query performance (12 of them for a Rolling 12 Month
or a Year To Date) drag things down?
Thanks
- RichardIn the current release of SQL Server, you can use partitioned views to
accomplish what you need. You'll need a partitioning column - enforced with
a check constraint - as part of the primary key in each member table. In
your case, it would be the relevant date. You then construct a view that
includes all members, hooked together with UNION ALL's. As you need to
add/drop a member table, simply alter the view to eliminate the old and add
the new from the definition. Then drop the un-needed table.
I don't believe you'll need the enterprise edition, since you're looking at
local partitioned views.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Richard Corfield" <richard.corfield@.gmail.com> wrote in message
news:c58fd37c.0410190647.cd2e11c@.posting.google.com...
Forgive me as I'm approaching a problem as someone familliar with
Oracle, so may be trying to solve it using techniques not applicable
for SQL Server.
I wonder if SQL Server allows me to partition tables. There's lots of
talk about distributed partitioning, but none about locally
partitioned tables. I wonder if SQL Server's query engine works in a
way that it isn't needed.
The reasons I'm looking for local partitioning are thus:
I can load the new fact data into one partition. It may not already
be empty, for example if I'm doing monthly loads into an annual
partition, but its going to be somewhat smaller than the whole fact
table. The index generation hit should be smaller with the partition.
I wonder if DTS/SQL Server supports bulk insert for extra speed.
My queries should take advantage of the partition. If I create
parallel partitions in Analysis Services, things should become faster
to maintain. For example - I only reprocess the cube for the partition
I've just loaded.
When data becomes too old to be interesting, I can just extract the
partition to a backup medium somewhere and drop it - easy as that. Say
goodbye to the partition in both SQL Server and in the Analysis
Services cube without any reprocessing hit on the rest of the data.
(Rolling sums become interesting on the boundary condition, or do
they, as I'm not reprocessing back there? hmmm. At my last employer I
made sure I kept data around long enough to satisfy the rolling sums,
so we queried to 3 years ago, and I kept 4 years online to satisfy
that)
Also, what editions of SQL Server support this kind of technology. Is
this an Enterprise Server type thing? At what point of fine graindness
do partitions become a heavy hit? Assuming I can automate all this
well, does it become sensible to use monthly partitions, or does the
cross partition query performance (12 of them for a Rolling 12 Month
or a Year To Date) drag things down?
Thanks
- Richard
Oracle, so may be trying to solve it using techniques not applicable
for SQL Server.
I wonder if SQL Server allows me to partition tables. There's lots of
talk about distributed partitioning, but none about locally
partitioned tables. I wonder if SQL Server's query engine works in a
way that it isn't needed.
The reasons I'm looking for local partitioning are thus:
I can load the new fact data into one partition. It may not already
be empty, for example if I'm doing monthly loads into an annual
partition, but its going to be somewhat smaller than the whole fact
table. The index generation hit should be smaller with the partition.
I wonder if DTS/SQL Server supports bulk insert for extra speed.
My queries should take advantage of the partition. If I create
parallel partitions in Analysis Services, things should become faster
to maintain. For example - I only reprocess the cube for the partition
I've just loaded.
When data becomes too old to be interesting, I can just extract the
partition to a backup medium somewhere and drop it - easy as that. Say
goodbye to the partition in both SQL Server and in the Analysis
Services cube without any reprocessing hit on the rest of the data.
(Rolling sums become interesting on the boundary condition, or do
they, as I'm not reprocessing back there? hmmm. At my last employer I
made sure I kept data around long enough to satisfy the rolling sums,
so we queried to 3 years ago, and I kept 4 years online to satisfy
that)
Also, what editions of SQL Server support this kind of technology. Is
this an Enterprise Server type thing? At what point of fine graindness
do partitions become a heavy hit? Assuming I can automate all this
well, does it become sensible to use monthly partitions, or does the
cross partition query performance (12 of them for a Rolling 12 Month
or a Year To Date) drag things down?
Thanks
- RichardIn the current release of SQL Server, you can use partitioned views to
accomplish what you need. You'll need a partitioning column - enforced with
a check constraint - as part of the primary key in each member table. In
your case, it would be the relevant date. You then construct a view that
includes all members, hooked together with UNION ALL's. As you need to
add/drop a member table, simply alter the view to eliminate the old and add
the new from the definition. Then drop the un-needed table.
I don't believe you'll need the enterprise edition, since you're looking at
local partitioned views.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Richard Corfield" <richard.corfield@.gmail.com> wrote in message
news:c58fd37c.0410190647.cd2e11c@.posting.google.com...
Forgive me as I'm approaching a problem as someone familliar with
Oracle, so may be trying to solve it using techniques not applicable
for SQL Server.
I wonder if SQL Server allows me to partition tables. There's lots of
talk about distributed partitioning, but none about locally
partitioned tables. I wonder if SQL Server's query engine works in a
way that it isn't needed.
The reasons I'm looking for local partitioning are thus:
I can load the new fact data into one partition. It may not already
be empty, for example if I'm doing monthly loads into an annual
partition, but its going to be somewhat smaller than the whole fact
table. The index generation hit should be smaller with the partition.
I wonder if DTS/SQL Server supports bulk insert for extra speed.
My queries should take advantage of the partition. If I create
parallel partitions in Analysis Services, things should become faster
to maintain. For example - I only reprocess the cube for the partition
I've just loaded.
When data becomes too old to be interesting, I can just extract the
partition to a backup medium somewhere and drop it - easy as that. Say
goodbye to the partition in both SQL Server and in the Analysis
Services cube without any reprocessing hit on the rest of the data.
(Rolling sums become interesting on the boundary condition, or do
they, as I'm not reprocessing back there? hmmm. At my last employer I
made sure I kept data around long enough to satisfy the rolling sums,
so we queried to 3 years ago, and I kept 4 years online to satisfy
that)
Also, what editions of SQL Server support this kind of technology. Is
this an Enterprise Server type thing? At what point of fine graindness
do partitions become a heavy hit? Assuming I can automate all this
well, does it become sensible to use monthly partitions, or does the
cross partition query performance (12 of them for a Rolling 12 Month
or a Year To Date) drag things down?
Thanks
- Richard
Locally Partitioned Tables for OLAP
Forgive me as I'm approaching a problem as someone familliar with
Oracle, so may be trying to solve it using techniques not applicable
for SQL Server.
I wonder if SQL Server allows me to partition tables. There's lots of
talk about distributed partitioning, but none about locally
partitioned tables. I wonder if SQL Server's query engine works in a
way that it isn't needed.
The reasons I'm looking for local partitioning are thus:
I can load the new fact data into one partition. It may not already
be empty, for example if I'm doing monthly loads into an annual
partition, but its going to be somewhat smaller than the whole fact
table. The index generation hit should be smaller with the partition.
I wonder if DTS/SQL Server supports bulk insert for extra speed.
My queries should take advantage of the partition. If I create
parallel partitions in Analysis Services, things should become faster
to maintain. For example - I only reprocess the cube for the partition
I've just loaded.
When data becomes too old to be interesting, I can just extract the
partition to a backup medium somewhere and drop it - easy as that. Say
goodbye to the partition in both SQL Server and in the Analysis
Services cube without any reprocessing hit on the rest of the data.
(Rolling sums become interesting on the boundary condition, or do
they, as I'm not reprocessing back there? hmmm. At my last employer I
made sure I kept data around long enough to satisfy the rolling sums,
so we queried to 3 years ago, and I kept 4 years online to satisfy
that)
Also, what editions of SQL Server support this kind of technology. Is
this an Enterprise Server type thing? At what point of fine graindness
do partitions become a heavy hit? Assuming I can automate all this
well, does it become sensible to use monthly partitions, or does the
cross partition query performance (12 of them for a Rolling 12 Month
or a Year To Date) drag things down?
Thanks
- RichardIn the current release of SQL Server, you can use partitioned views to
accomplish what you need. You'll need a partitioning column - enforced with
a check constraint - as part of the primary key in each member table. In
your case, it would be the relevant date. You then construct a view that
includes all members, hooked together with UNION ALL's. As you need to
add/drop a member table, simply alter the view to eliminate the old and add
the new from the definition. Then drop the un-needed table.
I don't believe you'll need the enterprise edition, since you're looking at
local partitioned views.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Richard Corfield" <richard.corfield@.gmail.com> wrote in message
news:c58fd37c.0410190647.cd2e11c@.posting.google.com...
Forgive me as I'm approaching a problem as someone familliar with
Oracle, so may be trying to solve it using techniques not applicable
for SQL Server.
I wonder if SQL Server allows me to partition tables. There's lots of
talk about distributed partitioning, but none about locally
partitioned tables. I wonder if SQL Server's query engine works in a
way that it isn't needed.
The reasons I'm looking for local partitioning are thus:
I can load the new fact data into one partition. It may not already
be empty, for example if I'm doing monthly loads into an annual
partition, but its going to be somewhat smaller than the whole fact
table. The index generation hit should be smaller with the partition.
I wonder if DTS/SQL Server supports bulk insert for extra speed.
My queries should take advantage of the partition. If I create
parallel partitions in Analysis Services, things should become faster
to maintain. For example - I only reprocess the cube for the partition
I've just loaded.
When data becomes too old to be interesting, I can just extract the
partition to a backup medium somewhere and drop it - easy as that. Say
goodbye to the partition in both SQL Server and in the Analysis
Services cube without any reprocessing hit on the rest of the data.
(Rolling sums become interesting on the boundary condition, or do
they, as I'm not reprocessing back there? hmmm. At my last employer I
made sure I kept data around long enough to satisfy the rolling sums,
so we queried to 3 years ago, and I kept 4 years online to satisfy
that)
Also, what editions of SQL Server support this kind of technology. Is
this an Enterprise Server type thing? At what point of fine graindness
do partitions become a heavy hit? Assuming I can automate all this
well, does it become sensible to use monthly partitions, or does the
cross partition query performance (12 of them for a Rolling 12 Month
or a Year To Date) drag things down?
Thanks
- Richard
Oracle, so may be trying to solve it using techniques not applicable
for SQL Server.
I wonder if SQL Server allows me to partition tables. There's lots of
talk about distributed partitioning, but none about locally
partitioned tables. I wonder if SQL Server's query engine works in a
way that it isn't needed.
The reasons I'm looking for local partitioning are thus:
I can load the new fact data into one partition. It may not already
be empty, for example if I'm doing monthly loads into an annual
partition, but its going to be somewhat smaller than the whole fact
table. The index generation hit should be smaller with the partition.
I wonder if DTS/SQL Server supports bulk insert for extra speed.
My queries should take advantage of the partition. If I create
parallel partitions in Analysis Services, things should become faster
to maintain. For example - I only reprocess the cube for the partition
I've just loaded.
When data becomes too old to be interesting, I can just extract the
partition to a backup medium somewhere and drop it - easy as that. Say
goodbye to the partition in both SQL Server and in the Analysis
Services cube without any reprocessing hit on the rest of the data.
(Rolling sums become interesting on the boundary condition, or do
they, as I'm not reprocessing back there? hmmm. At my last employer I
made sure I kept data around long enough to satisfy the rolling sums,
so we queried to 3 years ago, and I kept 4 years online to satisfy
that)
Also, what editions of SQL Server support this kind of technology. Is
this an Enterprise Server type thing? At what point of fine graindness
do partitions become a heavy hit? Assuming I can automate all this
well, does it become sensible to use monthly partitions, or does the
cross partition query performance (12 of them for a Rolling 12 Month
or a Year To Date) drag things down?
Thanks
- RichardIn the current release of SQL Server, you can use partitioned views to
accomplish what you need. You'll need a partitioning column - enforced with
a check constraint - as part of the primary key in each member table. In
your case, it would be the relevant date. You then construct a view that
includes all members, hooked together with UNION ALL's. As you need to
add/drop a member table, simply alter the view to eliminate the old and add
the new from the definition. Then drop the un-needed table.
I don't believe you'll need the enterprise edition, since you're looking at
local partitioned views.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Richard Corfield" <richard.corfield@.gmail.com> wrote in message
news:c58fd37c.0410190647.cd2e11c@.posting.google.com...
Forgive me as I'm approaching a problem as someone familliar with
Oracle, so may be trying to solve it using techniques not applicable
for SQL Server.
I wonder if SQL Server allows me to partition tables. There's lots of
talk about distributed partitioning, but none about locally
partitioned tables. I wonder if SQL Server's query engine works in a
way that it isn't needed.
The reasons I'm looking for local partitioning are thus:
I can load the new fact data into one partition. It may not already
be empty, for example if I'm doing monthly loads into an annual
partition, but its going to be somewhat smaller than the whole fact
table. The index generation hit should be smaller with the partition.
I wonder if DTS/SQL Server supports bulk insert for extra speed.
My queries should take advantage of the partition. If I create
parallel partitions in Analysis Services, things should become faster
to maintain. For example - I only reprocess the cube for the partition
I've just loaded.
When data becomes too old to be interesting, I can just extract the
partition to a backup medium somewhere and drop it - easy as that. Say
goodbye to the partition in both SQL Server and in the Analysis
Services cube without any reprocessing hit on the rest of the data.
(Rolling sums become interesting on the boundary condition, or do
they, as I'm not reprocessing back there? hmmm. At my last employer I
made sure I kept data around long enough to satisfy the rolling sums,
so we queried to 3 years ago, and I kept 4 years online to satisfy
that)
Also, what editions of SQL Server support this kind of technology. Is
this an Enterprise Server type thing? At what point of fine graindness
do partitions become a heavy hit? Assuming I can automate all this
well, does it become sensible to use monthly partitions, or does the
cross partition query performance (12 of them for a Rolling 12 Month
or a Year To Date) drag things down?
Thanks
- Richard
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.
Local Testing Using SQL Server
I have a SQL server db set up locally.
I have a local DSN set up.
I have DW connected to the SQL server db and can create Bindings and
use Server Behaviors without any problem.
When I try to test the site locally in the browser, I get the
following error:
"Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database
"xxxxxxx" requested by the login. The login failed."
What am I doing wrong?
Thanks.
Most likely, you've set the default database in the connection string to a
database that the login doesn't have access to. Note this would be the
connection string used by the web server to connect to the SQL instance.
Linchi
"Brett_A" wrote:
> I have a SQL server db set up locally.
> I have a local DSN set up.
> I have DW connected to the SQL server db and can create Bindings and
> use Server Behaviors without any problem.
> When I try to test the site locally in the browser, I get the
> following error:
> "Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database
> "xxxxxxx" requested by the login. The login failed."
> What am I doing wrong?
> Thanks.
>
I have a local DSN set up.
I have DW connected to the SQL server db and can create Bindings and
use Server Behaviors without any problem.
When I try to test the site locally in the browser, I get the
following error:
"Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database
"xxxxxxx" requested by the login. The login failed."
What am I doing wrong?
Thanks.
Most likely, you've set the default database in the connection string to a
database that the login doesn't have access to. Note this would be the
connection string used by the web server to connect to the SQL instance.
Linchi
"Brett_A" wrote:
> I have a SQL server db set up locally.
> I have a local DSN set up.
> I have DW connected to the SQL server db and can create Bindings and
> use Server Behaviors without any problem.
> When I try to test the site locally in the browser, I get the
> following error:
> "Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database
> "xxxxxxx" requested by the login. The login failed."
> What am I doing wrong?
> Thanks.
>
Local Testing Using SQL Server
I have a SQL server db set up locally.
I have a local DSN set up.
I have DW connected to the SQL server db and can create Bindings and
use Server Behaviors without any problem.
When I try to test the site locally in the browser, I get the
following error:
"Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open datab
ase
"xxxxxxx" requested by the login. The login failed."
What am I doing wrong?
Thanks.Most likely, you've set the default database in the connection string to a
database that the login doesn't have access to. Note this would be the
connection string used by the web server to connect to the SQL instance.
Linchi
"Brett_A" wrote:
> I have a SQL server db set up locally.
> I have a local DSN set up.
> I have DW connected to the SQL server db and can create Bindings and
> use Server Behaviors without any problem.
> When I try to test the site locally in the browser, I get the
> following error:
> "Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open dat
abase
> "xxxxxxx" requested by the login. The login failed."
> What am I doing wrong?
> Thanks.
>
I have a local DSN set up.
I have DW connected to the SQL server db and can create Bindings and
use Server Behaviors without any problem.
When I try to test the site locally in the browser, I get the
following error:
"Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open datab
ase
"xxxxxxx" requested by the login. The login failed."
What am I doing wrong?
Thanks.Most likely, you've set the default database in the connection string to a
database that the login doesn't have access to. Note this would be the
connection string used by the web server to connect to the SQL instance.
Linchi
"Brett_A" wrote:
> I have a SQL server db set up locally.
> I have a local DSN set up.
> I have DW connected to the SQL server db and can create Bindings and
> use Server Behaviors without any problem.
> When I try to test the site locally in the browser, I get the
> following error:
> "Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open dat
abase
> "xxxxxxx" requested by the login. The login failed."
> What am I doing wrong?
> Thanks.
>
Local Testing Using SQL Server
I have a SQL server db set up locally.
I have a local DSN set up.
I have DW connected to the SQL server db and can create Bindings and
use Server Behaviors without any problem.
When I try to test the site locally in the browser, I get the
following error:
"Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database
"xxxxxxx" requested by the login. The login failed."
What am I doing wrong?
Thanks.Most likely, you've set the default database in the connection string to a
database that the login doesn't have access to. Note this would be the
connection string used by the web server to connect to the SQL instance.
Linchi
"Brett_A" wrote:
> I have a SQL server db set up locally.
> I have a local DSN set up.
> I have DW connected to the SQL server db and can create Bindings and
> use Server Behaviors without any problem.
> When I try to test the site locally in the browser, I get the
> following error:
> "Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database
> "xxxxxxx" requested by the login. The login failed."
> What am I doing wrong?
> Thanks.
>
I have a local DSN set up.
I have DW connected to the SQL server db and can create Bindings and
use Server Behaviors without any problem.
When I try to test the site locally in the browser, I get the
following error:
"Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database
"xxxxxxx" requested by the login. The login failed."
What am I doing wrong?
Thanks.Most likely, you've set the default database in the connection string to a
database that the login doesn't have access to. Note this would be the
connection string used by the web server to connect to the SQL instance.
Linchi
"Brett_A" wrote:
> I have a SQL server db set up locally.
> I have a local DSN set up.
> I have DW connected to the SQL server db and can create Bindings and
> use Server Behaviors without any problem.
> When I try to test the site locally in the browser, I get the
> following error:
> "Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database
> "xxxxxxx" requested by the login. The login failed."
> What am I doing wrong?
> Thanks.
>
Wednesday, March 7, 2012
Local SQL Queries slow on Dual CPU/hyperthreading hardware
We've discovered that under some circumstances running SQL queries locally
are significantly slower than when running the same payload over the
network, but only when hyperthreading is switched on using the same physical
hardware. Please also not that in these tests, I've set SQL server to on run
on 1 CPU only, and set with a parallelism of 1 (which should be irrelevant
when running on only one CPU).
A pretty simple payload can be used to reproduce the problem - as long as
there are enough lines! In fact you don't need to run the query at all -
simply parsing it in SQL query analyzer is enough.
For example, I've used:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[A]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[A]
GO
create table [dbo].[A] ([A] [int], [B] [int], [C] [int])
GO
and then I've repeated it 1000 times (one way to create this is to use
Excel...), to produce a 4000 line payload.
Note that I've chosen this just as something that passes the syntax check -
not SELECTing, UPDATEing,
DELETEing or INSERTing anything at all - the table is empty!
Pasting this into SQL Query analyzer, and then hitting the parse button
takes varying amounts of time, as shown below:
When running queries locally:
With hyperthreading on it always runs slow (17s)
With hyperthreading off it always runs fast (<1s).
Networking protocol makes no discernable difference - run this with
named pipes, TCP/IP and Shared memory protocol make no difference.
When running queries remotely (i.e running Query analyzer on a different
machine connected via the network):
Hyperthreading on or off makes no discernable difference to the
performance time.
When connected with named pipes it always runs fast (~2s).
When connected with TCP/IP it takes ~25s.
We've tried this test on both XP and Server 2003 OS's on different
hyperthreading hardware and get very similar results. Initial tests suggest
that something similar may happen on Dual CPU hardware, but we haven't fully
investigated this yet.
The difference between named pipes and TCP/IP over the network I understand,
and am not too worried about. But the fact that I can make LOCAL queries run
much faster by disabling hyperthreading is a real issue for us - we have
services running on the local machine that query and update the database.
Initally we thought that this was a Windows 2003 server issue, but we can
reproduce it on XP. We haven't tried on Win2k, but would have expected to
see it before now if it was so obvious - we have several Win2k dual CPU
machines that have been running the same software on customer sites for the
last 3-4 years.
Thanks a lot!
Stephen Brown
(P.S. I apologise for posting this before, but I wanted to change the
emphasis from my earlier post.)Have you tried simplifying the script? For example, what if you do something
like:
" decalre @.a int
set @.a = 0
set @.a = @.a+1
... repeat 1000 times
print @.a" ?
This will tell if the problem is related to the SQL script you send or not.
If not related to the SQL script, then the problem is likely to be in the
network layer.
Is your windows 2003 server and windows XP clean installed? Do you have
anti-virus, network monitoring, etc running? Any special network driver?
Which version of SQL Server?
You can also contact microsoft support to get this problem resolved.
http://www.microsoft.com/sql/support/default.asp
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chillipepper" <chillipepper@.nospam.nospam> wrote in message
news:%23NyYY3YHFHA.3072@.TK2MSFTNGP10.phx.gbl...
> We've discovered that under some circumstances running SQL queries locally
> are significantly slower than when running the same payload over the
> network, but only when hyperthreading is switched on using the same
> physical hardware. Please also not that in these tests, I've set SQL
> server to on run on 1 CPU only, and set with a parallelism of 1 (which
> should be irrelevant when running on only one CPU).
> A pretty simple payload can be used to reproduce the problem - as long as
> there are enough lines! In fact you don't need to run the query at all -
> simply parsing it in SQL query analyzer is enough.
> For example, I've used:
> if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[A]')
> and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[A]
> GO
> create table [dbo].[A] ([A] [int], [B] [int], [C] [int])
> GO
> and then I've repeated it 1000 times (one way to create this is to use
> Excel...), to produce a 4000 line payload.
> Note that I've chosen this just as something that passes the syntax
> check - not SELECTing, UPDATEing,
> DELETEing or INSERTing anything at all - the table is empty!
> Pasting this into SQL Query analyzer, and then hitting the parse button
> takes varying amounts of time, as shown below:
> When running queries locally:
> With hyperthreading on it always runs slow (17s)
> With hyperthreading off it always runs fast (<1s).
> Networking protocol makes no discernable difference - run this with
> named pipes, TCP/IP and Shared memory protocol make no difference.
> When running queries remotely (i.e running Query analyzer on a different
> machine connected via the network):
> Hyperthreading on or off makes no discernable difference to the
> performance time.
> When connected with named pipes it always runs fast (~2s).
> When connected with TCP/IP it takes ~25s.
> We've tried this test on both XP and Server 2003 OS's on different
> hyperthreading hardware and get very similar results. Initial tests
> suggest that something similar may happen on Dual CPU hardware, but we
> haven't fully investigated this yet.
> The difference between named pipes and TCP/IP over the network I
> understand, and am not too worried about. But the fact that I can make
> LOCAL queries run much faster by disabling hyperthreading is a real issue
> for us - we have services running on the local machine that query and
> update the database. Initally we thought that this was a Windows 2003
> server issue, but we can reproduce it on XP. We haven't tried on Win2k,
> but would have expected to see it before now if it was so obvious - we
> have several Win2k dual CPU machines that have been running the same
> software on customer sites for the last 3-4 years.
> Thanks a lot!
> Stephen Brown
> (P.S. I apologise for posting this before, but I wanted to change the
> emphasis from my earlier post.)
>|||Hi Wei Xiao ,
Thanks for your response. In answer to your questions:
I have tried your suggestion and it DOES make a significant difference -
i.e. the simple set-a-local-variable script runs almost instantaneously both
remotely and locally, irrespective of hyperthreading or not.
The OS's that I have used are both patched and up to date. The XP OS is on a
development machine (i.e. heavily used!), and running e-Trust antivirus. The
2003 OS is on a test machine - the OS has been recently rebuilt, and has no
anti-virus installed. Both machines have intel chipsets - one is using the
onboard intel network, the other using a netgear card. I have run the test
with networking disabled, and it made no difference.
I'm using SQL server SP3a - and see the same behaviour on MSDE, developement
and standard editions.
I have tried all of the usual monitoring tools - perfmon, the network
monitor, SQL profiler etc. Whether running these tools or not makes no
difference. I have looked at disk performance, CPU performance, memory
allocation, network queues etc etc, and nothing seems to be busy at all. All
I have discovered is that when the query runs quickly (i.e. hyperthreading
off, single CPU) the CPU is used heavily (70-80%) for the few seconds that
the query runs - i.e. it is CPU limited (as expected!). When the query runs
slowly, there is virtually no CPU activity at all.
As you have probably gathered, the problem I posted is a bit abstracted from
our real problem - we have encountered unexpected performance issues when
running our existing applications on a new hardware/software platform. After
weeks of head scratching discovered that SQL server was responding slowly
when accessed locally, but normally when accessed remotely.
Stephen
P.S. We have also come across the issue mentioned elsewhere on this
newsgroup - where SQL server slows down dramatically (this may be restricted
to Server 2003), and recovers after a SQL server service restart. It is
beginning to look to us as if there are two separate issues affecting us -
one possibly Server 2003 related, the other dual processor/hyperthreading
related - and unfortunately we have encountered both at the same time,
making it very difficult to determine what is going on!
"wei xiao" <weix@.online.microsoft.com> wrote in message
news:OWz4HkjHFHA.2744@.tk2msftngp13.phx.gbl...
> Have you tried simplifying the script? For example, what if you do
> something like:
> " decalre @.a int
> set @.a = 0
> set @.a = @.a+1
> ... repeat 1000 times
> print @.a" ?
> This will tell if the problem is related to the SQL script you send or
> not. If not related to the SQL script, then the problem is likely to be in
> the network layer.
> Is your windows 2003 server and windows XP clean installed? Do you have
> anti-virus, network monitoring, etc running? Any special network driver?
> Which version of SQL Server?
> You can also contact microsoft support to get this problem resolved.
> http://www.microsoft.com/sql/support/default.asp
>
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Chillipepper,
Generally, this kind of performance issues can be caused by various
factors, and it is difficult to locate the root cause in a newsgroup
thread. If the issue still exists after you have used the troubleshooting
steps above, to efficiently troubleshoot a performance issue, we recommend
that you contact Microsoft Customer Service and Support and open a support
incident and work with a dedicated Support Professional.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
A very good white paper that talks about the SQL Server and HTT technology:
Based on my knowledge, the issue might be caused that he enabled
hyperthread but set parallelism to 1. Would you please set parallelism to 5
to see whether it will improve the performance.
Here is two very good white paper that talks about the SQL Server and HTT
technology, hope it helps
Microsoft SQL Server and Intel Hyper-Threading Technology
http://www.microsoft.com/sql/howtobuy/SQLonHTT.doc
Microsoft Windows-Based Servers and Intel Hyper-Threading Technology
http://download.microsoft.com/download/e/2/3/e239dd00-9ae1-41a9-bb3f-c01f658
d16b1/hyperthreading.doc
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
are significantly slower than when running the same payload over the
network, but only when hyperthreading is switched on using the same physical
hardware. Please also not that in these tests, I've set SQL server to on run
on 1 CPU only, and set with a parallelism of 1 (which should be irrelevant
when running on only one CPU).
A pretty simple payload can be used to reproduce the problem - as long as
there are enough lines! In fact you don't need to run the query at all -
simply parsing it in SQL query analyzer is enough.
For example, I've used:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[A]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[A]
GO
create table [dbo].[A] ([A] [int], [B] [int], [C] [int])
GO
and then I've repeated it 1000 times (one way to create this is to use
Excel...), to produce a 4000 line payload.
Note that I've chosen this just as something that passes the syntax check -
not SELECTing, UPDATEing,
DELETEing or INSERTing anything at all - the table is empty!
Pasting this into SQL Query analyzer, and then hitting the parse button
takes varying amounts of time, as shown below:
When running queries locally:
With hyperthreading on it always runs slow (17s)
With hyperthreading off it always runs fast (<1s).
Networking protocol makes no discernable difference - run this with
named pipes, TCP/IP and Shared memory protocol make no difference.
When running queries remotely (i.e running Query analyzer on a different
machine connected via the network):
Hyperthreading on or off makes no discernable difference to the
performance time.
When connected with named pipes it always runs fast (~2s).
When connected with TCP/IP it takes ~25s.
We've tried this test on both XP and Server 2003 OS's on different
hyperthreading hardware and get very similar results. Initial tests suggest
that something similar may happen on Dual CPU hardware, but we haven't fully
investigated this yet.
The difference between named pipes and TCP/IP over the network I understand,
and am not too worried about. But the fact that I can make LOCAL queries run
much faster by disabling hyperthreading is a real issue for us - we have
services running on the local machine that query and update the database.
Initally we thought that this was a Windows 2003 server issue, but we can
reproduce it on XP. We haven't tried on Win2k, but would have expected to
see it before now if it was so obvious - we have several Win2k dual CPU
machines that have been running the same software on customer sites for the
last 3-4 years.
Thanks a lot!
Stephen Brown
(P.S. I apologise for posting this before, but I wanted to change the
emphasis from my earlier post.)Have you tried simplifying the script? For example, what if you do something
like:
" decalre @.a int
set @.a = 0
set @.a = @.a+1
... repeat 1000 times
print @.a" ?
This will tell if the problem is related to the SQL script you send or not.
If not related to the SQL script, then the problem is likely to be in the
network layer.
Is your windows 2003 server and windows XP clean installed? Do you have
anti-virus, network monitoring, etc running? Any special network driver?
Which version of SQL Server?
You can also contact microsoft support to get this problem resolved.
http://www.microsoft.com/sql/support/default.asp
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chillipepper" <chillipepper@.nospam.nospam> wrote in message
news:%23NyYY3YHFHA.3072@.TK2MSFTNGP10.phx.gbl...
> We've discovered that under some circumstances running SQL queries locally
> are significantly slower than when running the same payload over the
> network, but only when hyperthreading is switched on using the same
> physical hardware. Please also not that in these tests, I've set SQL
> server to on run on 1 CPU only, and set with a parallelism of 1 (which
> should be irrelevant when running on only one CPU).
> A pretty simple payload can be used to reproduce the problem - as long as
> there are enough lines! In fact you don't need to run the query at all -
> simply parsing it in SQL query analyzer is enough.
> For example, I've used:
> if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[A]')
> and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[A]
> GO
> create table [dbo].[A] ([A] [int], [B] [int], [C] [int])
> GO
> and then I've repeated it 1000 times (one way to create this is to use
> Excel...), to produce a 4000 line payload.
> Note that I've chosen this just as something that passes the syntax
> check - not SELECTing, UPDATEing,
> DELETEing or INSERTing anything at all - the table is empty!
> Pasting this into SQL Query analyzer, and then hitting the parse button
> takes varying amounts of time, as shown below:
> When running queries locally:
> With hyperthreading on it always runs slow (17s)
> With hyperthreading off it always runs fast (<1s).
> Networking protocol makes no discernable difference - run this with
> named pipes, TCP/IP and Shared memory protocol make no difference.
> When running queries remotely (i.e running Query analyzer on a different
> machine connected via the network):
> Hyperthreading on or off makes no discernable difference to the
> performance time.
> When connected with named pipes it always runs fast (~2s).
> When connected with TCP/IP it takes ~25s.
> We've tried this test on both XP and Server 2003 OS's on different
> hyperthreading hardware and get very similar results. Initial tests
> suggest that something similar may happen on Dual CPU hardware, but we
> haven't fully investigated this yet.
> The difference between named pipes and TCP/IP over the network I
> understand, and am not too worried about. But the fact that I can make
> LOCAL queries run much faster by disabling hyperthreading is a real issue
> for us - we have services running on the local machine that query and
> update the database. Initally we thought that this was a Windows 2003
> server issue, but we can reproduce it on XP. We haven't tried on Win2k,
> but would have expected to see it before now if it was so obvious - we
> have several Win2k dual CPU machines that have been running the same
> software on customer sites for the last 3-4 years.
> Thanks a lot!
> Stephen Brown
> (P.S. I apologise for posting this before, but I wanted to change the
> emphasis from my earlier post.)
>|||Hi Wei Xiao ,
Thanks for your response. In answer to your questions:
I have tried your suggestion and it DOES make a significant difference -
i.e. the simple set-a-local-variable script runs almost instantaneously both
remotely and locally, irrespective of hyperthreading or not.
The OS's that I have used are both patched and up to date. The XP OS is on a
development machine (i.e. heavily used!), and running e-Trust antivirus. The
2003 OS is on a test machine - the OS has been recently rebuilt, and has no
anti-virus installed. Both machines have intel chipsets - one is using the
onboard intel network, the other using a netgear card. I have run the test
with networking disabled, and it made no difference.
I'm using SQL server SP3a - and see the same behaviour on MSDE, developement
and standard editions.
I have tried all of the usual monitoring tools - perfmon, the network
monitor, SQL profiler etc. Whether running these tools or not makes no
difference. I have looked at disk performance, CPU performance, memory
allocation, network queues etc etc, and nothing seems to be busy at all. All
I have discovered is that when the query runs quickly (i.e. hyperthreading
off, single CPU) the CPU is used heavily (70-80%) for the few seconds that
the query runs - i.e. it is CPU limited (as expected!). When the query runs
slowly, there is virtually no CPU activity at all.
As you have probably gathered, the problem I posted is a bit abstracted from
our real problem - we have encountered unexpected performance issues when
running our existing applications on a new hardware/software platform. After
weeks of head scratching discovered that SQL server was responding slowly
when accessed locally, but normally when accessed remotely.
Stephen
P.S. We have also come across the issue mentioned elsewhere on this
newsgroup - where SQL server slows down dramatically (this may be restricted
to Server 2003), and recovers after a SQL server service restart. It is
beginning to look to us as if there are two separate issues affecting us -
one possibly Server 2003 related, the other dual processor/hyperthreading
related - and unfortunately we have encountered both at the same time,
making it very difficult to determine what is going on!
"wei xiao" <weix@.online.microsoft.com> wrote in message
news:OWz4HkjHFHA.2744@.tk2msftngp13.phx.gbl...
> Have you tried simplifying the script? For example, what if you do
> something like:
> " decalre @.a int
> set @.a = 0
> set @.a = @.a+1
> ... repeat 1000 times
> print @.a" ?
> This will tell if the problem is related to the SQL script you send or
> not. If not related to the SQL script, then the problem is likely to be in
> the network layer.
> Is your windows 2003 server and windows XP clean installed? Do you have
> anti-virus, network monitoring, etc running? Any special network driver?
> Which version of SQL Server?
> You can also contact microsoft support to get this problem resolved.
> http://www.microsoft.com/sql/support/default.asp
>
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Chillipepper,
Generally, this kind of performance issues can be caused by various
factors, and it is difficult to locate the root cause in a newsgroup
thread. If the issue still exists after you have used the troubleshooting
steps above, to efficiently troubleshoot a performance issue, we recommend
that you contact Microsoft Customer Service and Support and open a support
incident and work with a dedicated Support Professional.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
A very good white paper that talks about the SQL Server and HTT technology:
Based on my knowledge, the issue might be caused that he enabled
hyperthread but set parallelism to 1. Would you please set parallelism to 5
to see whether it will improve the performance.
Here is two very good white paper that talks about the SQL Server and HTT
technology, hope it helps
Microsoft SQL Server and Intel Hyper-Threading Technology
http://www.microsoft.com/sql/howtobuy/SQLonHTT.doc
Microsoft Windows-Based Servers and Intel Hyper-Threading Technology
http://download.microsoft.com/download/e/2/3/e239dd00-9ae1-41a9-bb3f-c01f658
d16b1/hyperthreading.doc
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
Monday, February 20, 2012
local and distributed transaction
Hi all,
Depending on a parameter in a sp, I have to do work either locally or
remotely.
The work to be done is the same in both case.
I wrote:
if @.param = 1
begin transaction
else
begin DISTRIBUTED transaction
/* do the work */
commit transaction
Does anyone knows if Microsoft have stated any recommandation on not writing
code like that (that is not having both type of transaction in the same
place) ?
ThanksHi
though logically what you have given is right, I feel you can use a
distributed transaction for both cases and live with out the if condition.
distributed transactions can also work locally.|||Hi,
Thanks for the answer.
Do you know if there might be any performance penlaty using distributed
transaction locally ?
"Omnibuzz" wrote:
> Hi
> though logically what you have given is right, I feel you can use a
> distributed transaction for both cases and live with out the if condition.
> distributed transactions can also work locally.|||Technically there shouldn't be any difference since if there are no other
resource managers that are participating in the distirbuted transaction
except the local server. Infact SQL Server uses a distirbuted transaction
spanning across databases within the same server. I feel it should be fine t
o
use it.
But if you are uncomfortable about this, u can use two SPs one having the
local transaction and one for the distirbuted transaction. It will look
better than the approach you have suggested. Hope this helps.|||A still better solution. You will have to check it though.
Add this line to the beginning of the proc
SET REMOTE_PROC_TRANSACTIONS ON
and use "begin transaction".
On a remote procedure call, it will promote the local transaction to
distirbuted trasanction, else will keep it as a local transaction.
Hope this helps.
Depending on a parameter in a sp, I have to do work either locally or
remotely.
The work to be done is the same in both case.
I wrote:
if @.param = 1
begin transaction
else
begin DISTRIBUTED transaction
/* do the work */
commit transaction
Does anyone knows if Microsoft have stated any recommandation on not writing
code like that (that is not having both type of transaction in the same
place) ?
ThanksHi
though logically what you have given is right, I feel you can use a
distributed transaction for both cases and live with out the if condition.
distributed transactions can also work locally.|||Hi,
Thanks for the answer.
Do you know if there might be any performance penlaty using distributed
transaction locally ?
"Omnibuzz" wrote:
> Hi
> though logically what you have given is right, I feel you can use a
> distributed transaction for both cases and live with out the if condition.
> distributed transactions can also work locally.|||Technically there shouldn't be any difference since if there are no other
resource managers that are participating in the distirbuted transaction
except the local server. Infact SQL Server uses a distirbuted transaction
spanning across databases within the same server. I feel it should be fine t
o
use it.
But if you are uncomfortable about this, u can use two SPs one having the
local transaction and one for the distirbuted transaction. It will look
better than the approach you have suggested. Hope this helps.|||A still better solution. You will have to check it though.
Add this line to the beginning of the proc
SET REMOTE_PROC_TRANSACTIONS ON
and use "begin transaction".
On a remote procedure call, it will promote the local transaction to
distirbuted trasanction, else will keep it as a local transaction.
Hope this helps.
Subscribe to:
Posts (Atom)