We are looking at installing a 2 Node ACTIVE/ACTIVE SQL
Server configuration utilizing two DELL PE6600 servers,
both connected via FIBRE to a DELL CX200(CLARION). The
OS will be clustered in a ACTIVE/ACTIVE configuration as
well.
The main purpose for attempting this is more for
spreading processing across systems more than for
failover capabilities.
I have a few questions, the first would be has anyone
accomplished this? Secondly, in a SQL server
ACTIVE/ACTIVE configuration, can both instances utilize
one shared disk configuration, so that database merging
does not need to happen?
Any help/advice would be greatly appreciated as i'm
drawing up a plan.
Failover clustering is designed for high availability. Failover clustering
is NOT a scale-out technology. There are some scenarios where data
partitioning and merge replication can be used in conjunction with
multi-instance clustering to achieve scale-out, but they require intensive
design and management. You are probably better off buying a bigger box for
your server than trying to scale out.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:81e201c431f4$708e9430$a501280a@.phx.gbl...
> We are looking at installing a 2 Node ACTIVE/ACTIVE SQL
> Server configuration utilizing two DELL PE6600 servers,
> both connected via FIBRE to a DELL CX200(CLARION). The
> OS will be clustered in a ACTIVE/ACTIVE configuration as
> well.
> The main purpose for attempting this is more for
> spreading processing across systems more than for
> failover capabilities.
>
> I have a few questions, the first would be has anyone
> accomplished this? Secondly, in a SQL server
> ACTIVE/ACTIVE configuration, can both instances utilize
> one shared disk configuration, so that database merging
> does not need to happen?
>
No.
> Any help/advice would be greatly appreciated as i'm
> drawing up a plan.
|||I guess with that being said, would you have any ideas in
regards to distributed processing.
High availiability is what i'm looking for, just in an
active/active configuration. Can I not have a sql server
running on two different systems and look at the same
logical data source via SAN solution?
>--Original Message--
>Failover clustering is designed for high availability.
Failover clustering
>is NOT a scale-out technology. There are some scenarios
where data
>partitioning and merge replication can be used in
conjunction with
>multi-instance clustering to achieve scale-out, but they
require intensive
>design and management. You are probably better off
buying a bigger box for
>your server than trying to scale out.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
>"Jeff" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:81e201c431f4$708e9430$a501280a@.phx.gbl...
as
>No.
>
>.
>
|||SQL Failover clustering works on a shared-nothing setup. Each instance has
exclusive control over the underlying resources, including its portion of
the shared storage system. The cluster service arbitrates ownership of the
underlying resources so that only one host node at a time controls each
resource.
Distributed database computing is not quite what is advertised. The TPC-C
benchmark is particularly well suited to a distributed model (coincidence
maybe?), but not all applications work as well. Buying a larger box is
probably where you want to go if you need more processing power.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:830d01c43207$a14ec370$a601280a@.phx.gbl...[vbcol=seagreen]
> I guess with that being said, would you have any ideas in
> regards to distributed processing.
> High availiability is what i'm looking for, just in an
> active/active configuration. Can I not have a sql server
> running on two different systems and look at the same
> logical data source via SAN solution?
>
> Failover clustering
> where data
> conjunction with
> require intensive
> buying a bigger box for
> message
> as
|||That makes since. I have been doing some reading on
active/active cluster configurations. Just want to
understand that in that configuration each instance
serves it's own needs until the time that one fails and
then the other instance picks up the additional workload
of the failed instance. Is that correct?
>--Original Message--
>SQL Failover clustering works on a shared-nothing
setup. Each instance has
>exclusive control over the underlying resources,
including its portion of
>the shared storage system. The cluster service
arbitrates ownership of the
>underlying resources so that only one host node at a
time controls each
>resource.
>Distributed database computing is not quite what is
advertised. The TPC-C
>benchmark is particularly well suited to a distributed
model (coincidence
>maybe?), but not all applications work as well. Buying
a larger box is
>probably where you want to go if you need more
processing power.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
>"Jeff" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:830d01c43207$a14ec370$a601280a@.phx.gbl...
in[vbcol=seagreen]
server[vbcol=seagreen]
scenarios[vbcol=seagreen]
they[vbcol=seagreen]
SQL[vbcol=seagreen]
servers,[vbcol=seagreen]
The[vbcol=seagreen]
configuration[vbcol=seagreen]
anyone[vbcol=seagreen]
utilize[vbcol=seagreen]
merging
>
>.
>
|||Nope. You are confusing instances and hosts. Not surprising since
active-active is a holdover from the SQL 7.0 days and is misleading for SQL
2000.
Lets start with a single-instance cluster. Two nodes (host computers), one
SQL instance. The instance runs on one host at a time. The cluster
services on all nodes monitor the running instance and if it fails a series
of tests (looks-alive and is-alive) the instance is deemed failed and is
restarted. The restart may be on the original node or on the other node.
You can add a second, independant instance that normally runs on the second
node but can run on either node. The two instances have nothing in common
except the set of host nodes that they can run on. There are some extra
concerns when using multiple instances on a cluster to make sure they can
'stack' on a single host node if necessary, but otherwise they do not
interact in any way. People incorrectly refer to this as 'Active-Active',
meaning both nodes have active instances. However, under SQL 2000 you can
have up to 16 instances on a cluster, so the naming quickly can get out of
hand.
Under SQL 7.0 the nodes were not completely interchangable so the
active-passive and active-active names were important. Each node owned a
single instance and the other node could run it but not make certain changes
to the setup. Now, single-instance and multiple-instance naming is more
meaningful since the nodes are truly interchangable. This is especially
true now that Windows 2003 allows 8 node clusters using Enterprise Edition.
(Active-Active-Passive-Passive-Active.. Blah, Blah, Blah).
Sounds like you need a single-instance installation running on fairly beefy
hardware. FYI, the CX-200 has been replaced with the CX-300. If you go
with a system larger than a 6600/6650 I would strongly look at the CX-500 or
CX-700 for the performance benefits.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:837001c43213$70e59cc0$a501280a@.phx.gbl...[vbcol=seagreen]
> That makes since. I have been doing some reading on
> active/active cluster configurations. Just want to
> understand that in that configuration each instance
> serves it's own needs until the time that one fails and
> then the other instance picks up the additional workload
> of the failed instance. Is that correct?
> setup. Each instance has
> including its portion of
> arbitrates ownership of the
> time controls each
> advertised. The TPC-C
> model (coincidence
> a larger box is
> processing power.
> message
> in
> server
> scenarios
> they
> SQL
> servers,
> The
> configuration
> anyone
> utilize
> merging
|||No. SQL Server does not allow shared access to a database. A single SQL
Server can open the database files and access them and that prevents
anything else for getting at the data. This is a data protection mechanism
and exists to prevent possible corruption of the data when two independent
resources attempt to write the same data.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||You may deploy it on SAN
|||I was going to have my two nodes on a SAN solution. Is
there differences from what the other gentleman was
stating in regards to having a SAN solution?
>--Original Message--
>You may deploy it on SAN
>.
>
|||You can store SQL data on a SAN. It is generally the best way to build a
cluster.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:acbc01c4369c$fd6a3290$a601280a@.phx.gbl...[vbcol=seagreen]
> I was going to have my two nodes on a SAN solution. Is
> there differences from what the other gentleman was
> stating in regards to having a SAN solution?
Showing posts with label via. Show all posts
Showing posts with label via. Show all posts
Thursday, March 22, 2012
Thursday, March 8, 2012
acronym (sql server backup problem)
i try to backup database (e.g., myDataBase) via the sql server 2000
wizard. (i use default value when process to do the backup, except
change to save database to disk)
and in anther machine i try to restore it. however, it occurrs that
the user would lose its login name that associates with sql server
2000 in another machine.(even though i create one the same as that in
backup machine) therefore, i have to use command like 'select * from
owner.table_name' to access tables in database. how to prevent this
happened when performing databasebackup procedure? or any ways to
correct this after the database restored in another machine?
i appreciate any suggestions, sincerely.
arsene
Maybe this can help.
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default.aspx?kbid=314546
Ana
"jason" <freeonfair@.yahoo.co.uk> wrote in message
news:ce06a859.0405130128.51c8f272@.posting.google.c om...
> i try to backup database (e.g., myDataBase) via the sql server 2000
> wizard. (i use default value when process to do the backup, except
> change to save database to disk)
> and in anther machine i try to restore it. however, it occurrs that
> the user would lose its login name that associates with sql server
> 2000 in another machine.(even though i create one the same as that in
> backup machine) therefore, i have to use command like 'select * from
> owner.table_name' to access tables in database. how to prevent this
> happened when performing databasebackup procedure? or any ways to
> correct this after the database restored in another machine?
> i appreciate any suggestions, sincerely.
> arsene
wizard. (i use default value when process to do the backup, except
change to save database to disk)
and in anther machine i try to restore it. however, it occurrs that
the user would lose its login name that associates with sql server
2000 in another machine.(even though i create one the same as that in
backup machine) therefore, i have to use command like 'select * from
owner.table_name' to access tables in database. how to prevent this
happened when performing databasebackup procedure? or any ways to
correct this after the database restored in another machine?
i appreciate any suggestions, sincerely.
arsene
Maybe this can help.
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default.aspx?kbid=314546
Ana
"jason" <freeonfair@.yahoo.co.uk> wrote in message
news:ce06a859.0405130128.51c8f272@.posting.google.c om...
> i try to backup database (e.g., myDataBase) via the sql server 2000
> wizard. (i use default value when process to do the backup, except
> change to save database to disk)
> and in anther machine i try to restore it. however, it occurrs that
> the user would lose its login name that associates with sql server
> 2000 in another machine.(even though i create one the same as that in
> backup machine) therefore, i have to use command like 'select * from
> owner.table_name' to access tables in database. how to prevent this
> happened when performing databasebackup procedure? or any ways to
> correct this after the database restored in another machine?
> i appreciate any suggestions, sincerely.
> arsene
acronym (sql server backup problem)
i try to backup database (e.g., myDataBase) via the sql server 2000
wizard. (i use default value when process to do the backup, except
change to save database to disk)
and in anther machine i try to restore it. however, it occurrs that
the user would lose its login name that associates with sql server
2000 in another machine.(even though i create one the same as that in
backup machine) therefore, i have to use command like 'select * from
owner.table_name' to access tables in database. how to prevent this
happened when performing databasebackup procedure? or any ways to
correct this after the database restored in another machine?
i appreciate any suggestions, sincerely.
arseneMaybe this can help.
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default.aspx?kbid=314546
Ana
"jason" <freeonfair@.yahoo.co.uk> wrote in message
news:ce06a859.0405130128.51c8f272@.posting.google.com...
> i try to backup database (e.g., myDataBase) via the sql server 2000
> wizard. (i use default value when process to do the backup, except
> change to save database to disk)
> and in anther machine i try to restore it. however, it occurrs that
> the user would lose its login name that associates with sql server
> 2000 in another machine.(even though i create one the same as that in
> backup machine) therefore, i have to use command like 'select * from
> owner.table_name' to access tables in database. how to prevent this
> happened when performing databasebackup procedure? or any ways to
> correct this after the database restored in another machine?
> i appreciate any suggestions, sincerely.
> arsene
wizard. (i use default value when process to do the backup, except
change to save database to disk)
and in anther machine i try to restore it. however, it occurrs that
the user would lose its login name that associates with sql server
2000 in another machine.(even though i create one the same as that in
backup machine) therefore, i have to use command like 'select * from
owner.table_name' to access tables in database. how to prevent this
happened when performing databasebackup procedure? or any ways to
correct this after the database restored in another machine?
i appreciate any suggestions, sincerely.
arseneMaybe this can help.
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default.aspx?kbid=314546
Ana
"jason" <freeonfair@.yahoo.co.uk> wrote in message
news:ce06a859.0405130128.51c8f272@.posting.google.com...
> i try to backup database (e.g., myDataBase) via the sql server 2000
> wizard. (i use default value when process to do the backup, except
> change to save database to disk)
> and in anther machine i try to restore it. however, it occurrs that
> the user would lose its login name that associates with sql server
> 2000 in another machine.(even though i create one the same as that in
> backup machine) therefore, i have to use command like 'select * from
> owner.table_name' to access tables in database. how to prevent this
> happened when performing databasebackup procedure? or any ways to
> correct this after the database restored in another machine?
> i appreciate any suggestions, sincerely.
> arsene
acronym (sql server backup problem)
i try to backup database (e.g., myDataBase) via the sql server 2000
wizard. (i use default value when process to do the backup, except
change to save database to disk)
and in anther machine i try to restore it. however, it occurrs that
the user would lose its login name that associates with sql server
2000 in another machine.(even though i create one the same as that in
backup machine) therefore, i have to use command like 'select * from
owner.table_name' to access tables in database. how to prevent this
happened when performing databasebackup procedure? or any ways to
correct this after the database restored in another machine?
i appreciate any suggestions, sincerely.
arseneMaybe this can help.
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default.aspx?kbid=314546
Ana
"jason" <freeonfair@.yahoo.co.uk> wrote in message
news:ce06a859.0405130128.51c8f272@.posting.google.com...
> i try to backup database (e.g., myDataBase) via the sql server 2000
> wizard. (i use default value when process to do the backup, except
> change to save database to disk)
> and in anther machine i try to restore it. however, it occurrs that
> the user would lose its login name that associates with sql server
> 2000 in another machine.(even though i create one the same as that in
> backup machine) therefore, i have to use command like 'select * from
> owner.table_name' to access tables in database. how to prevent this
> happened when performing databasebackup procedure? or any ways to
> correct this after the database restored in another machine?
> i appreciate any suggestions, sincerely.
> arsene
wizard. (i use default value when process to do the backup, except
change to save database to disk)
and in anther machine i try to restore it. however, it occurrs that
the user would lose its login name that associates with sql server
2000 in another machine.(even though i create one the same as that in
backup machine) therefore, i have to use command like 'select * from
owner.table_name' to access tables in database. how to prevent this
happened when performing databasebackup procedure? or any ways to
correct this after the database restored in another machine?
i appreciate any suggestions, sincerely.
arseneMaybe this can help.
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default.aspx?kbid=314546
Ana
"jason" <freeonfair@.yahoo.co.uk> wrote in message
news:ce06a859.0405130128.51c8f272@.posting.google.com...
> i try to backup database (e.g., myDataBase) via the sql server 2000
> wizard. (i use default value when process to do the backup, except
> change to save database to disk)
> and in anther machine i try to restore it. however, it occurrs that
> the user would lose its login name that associates with sql server
> 2000 in another machine.(even though i create one the same as that in
> backup machine) therefore, i have to use command like 'select * from
> owner.table_name' to access tables in database. how to prevent this
> happened when performing databasebackup procedure? or any ways to
> correct this after the database restored in another machine?
> i appreciate any suggestions, sincerely.
> arsene
Saturday, February 25, 2012
Account lock outs
I have 50 users connecting to SQL server via ODBC using
TCP/IP to connect. But 2 of the users accounts keep on
having their windows accounts locked out.
Any suggestions?Turn on Windows auditing, maybe even SQL auditing as well. Have your users
log off and back on their computers and watch both security logs. NT
authentication for SQL doesn't ask for a password when connecting, it just
checks your Windows credentials. Are they mapping network drives before
connecting to SQL? Maybe the password credentials there are bad. You might
want to tell them to disconnect any network drives and remap them. Do a net
use as well and check for persistent connections.
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"Marcus" <anonymous@.discussions.microsoft.com> wrote in message
news:26ee01c3e18d$626c7820$a101280a@.phx.gbl...
TCP/IP to connect. But 2 of the users accounts keep on
having their windows accounts locked out.
Any suggestions?Turn on Windows auditing, maybe even SQL auditing as well. Have your users
log off and back on their computers and watch both security logs. NT
authentication for SQL doesn't ask for a password when connecting, it just
checks your Windows credentials. Are they mapping network drives before
connecting to SQL? Maybe the password credentials there are bad. You might
want to tell them to disconnect any network drives and remap them. Do a net
use as well and check for persistent connections.
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"Marcus" <anonymous@.discussions.microsoft.com> wrote in message
news:26ee01c3e18d$626c7820$a101280a@.phx.gbl...
quote:
> I have 50 users connecting to SQL server via ODBC using
> TCP/IP to connect. But 2 of the users accounts keep on
> having their windows accounts locked out.
> Any suggestions?
Friday, February 24, 2012
accesssing mirrored databases via SQLNCLI linked server?
Does anyone know how to configure a mirrored pair as a linked server on a 3rd instance? It is quit possible that the connection to the primary take long to fail before making second connection to the secondary. The default timeout value is 15 seconds for a connection. You can try extend the timeout value or you can upgrade to SP1 which has better retry logic that can deal with timeout better.
Say I have a mirrored database on two servers: PRIMARY and SECONDARY.
I want to create a linked server on a 3rd machine that allows me to access the database on the mirrored pair.
This is what I'm using:
EXEC master.dbo.sp_addlinkedserver
@.server = N'MIRROR',
@.srvproduct=N'',
@.provider=N'SQLNCLI',
@.provstr=N'Server=PRIMARY;FailoverPartner=SECONDARY;'
select count (*) from mirror.pubs.dbo.authors
and it works fine if the database on PRIMARY is alive. however when the mirror has failed over to SECONDARY and PRIMARY is no longer available, I get the following when I try to query the database via the linked server:
OLE DB provider "SQLNCLI" for linked server "MIRROR" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "MIRROR" returned message "An error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
Msg 10061, Level 16, State 1, Line 0
TCP Provider: No connection could be made because the target machine actively refused it.
As far as I can tell, it doesn't try to contact SECONDARY at all. It seems like SQL Server is ignoring the FailoverPartner attribute.
If I switch PRIMARY and SECONDARY in the connection string (ie @.provstr=N'Server=SECONDARY;FailoverPartner=PRIMARY;') then it works when SECONDARY is online, but not when the mirror has failed back to PRIMARY.
Any ideas?
Piers.
I was able to reproduce this problem and we're investigating further. Will let you know the results.
|||great! I'd love to know if there's a workaround or patch available.|||
From the documentation at: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dataacc9/html/71b15712-7972-4465-9274-e0ddc271eedc.htm
You must explicitly specify the database to be used by a connection if you want to use this feature in a DSN, connection string, or connection property/attribute. SQL Native Client will not attempt to failover to the partner database if this is not done.
Mirroring is a feature of the database. Applications that use multiple databases might not be able to exploit this feature.
In addition, server names are case insensitive, but database names are case sensitive. You should therefore make sure that you use the same casing in DSNs and connection strings.
So basically you should specify the Database name to which to connect to either in the connection string or through @.catalog=<Database Name> parameter to sp_addlinkedserver. I tried doing that and the repro started working.
Thanks
Waseem
accesssing mirrored databases via SQLNCLI linked server?
Does anyone know how to configure a mirrored pair as a linked server on a 3rd instance?
Say I have a mirrored database on two servers: PRIMARY and SECONDARY.
I want to create a linked server on a 3rd machine that allows me to access the database on the mirrored pair.
This is what I'm using:
EXEC master.dbo.sp_addlinkedserver
@.server = N'MIRROR',
@.srvproduct=N'',
@.provider=N'SQLNCLI',
@.provstr=N'Server=PRIMARY;FailoverPartner=SECONDARY;'
select count (*) from mirror.pubs.dbo.authors
and it works fine if the database on PRIMARY is alive. however when the mirror has failed over to SECONDARY and PRIMARY is no longer available, I get the following when I try to query the database via the linked server:
OLE DB provider "SQLNCLI" for linked server "MIRROR" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "MIRROR" returned message "An error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
Msg 10061, Level 16, State 1, Line 0
TCP Provider: No connection could be made because the target machine actively refused it.
As far as I can tell, it doesn't try to contact SECONDARY at all. If I switch PRIMARY and SECONDARY in the connection string (ie @.provstr=N'Server=SECONDARY;FailoverPartner=PRIMARY;') then it works when SECONDARY is online, but not when the mirror has failed back to PRIMARY.
Any ideas?
Piers.
It is quit possible that the connection to the primary take long to fail before making second connection to the secondary. The default timeout value is 15 seconds for a connection. You can try extend the timeout value or you can upgrade to SP1 which has better retry logic that can deal with timeout better.|||All 3 servers are running 9.0.2047sp1/x64 on WS2k3sp1/x64.
I've also tried extending the connection timeout with
EXEC master.dbo.sp_serveroption @.server=N'MIRROR', @.optname=N'connect timeout', @.optvalue=N'120'
but I still get the same error after ~20 seconds (regardless of the value I specify). I've looked at the network traffic and it never seems to attempt to establish a connection to the secondary.
here's the entire script I'm using:
EXEC master.dbo.sp_dropserver @.server= N'MIRROR'
EXEC master.dbo.sp_addlinkedserver
@.server = N'MIRROR',
@.srvproduct=N'',
@.provider=N'SQLOLEDB',
@.provstr=N'Server=PRIMARY;FailoverPartner=SECONDARY;'
GOEXEC master.dbo.sp_serveroption @.server=N'MIRROR', @.optname=N'collation compatible', @.optvalue=N'true'
EXEC master.dbo.sp_serveroption @.server=N'MIRROR', @.optname=N'data access', @.optvalue=N'true'
EXEC master.dbo.sp_serveroption @.server=N'MIRROR', @.optname=N'dist', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'MIRROR', @.optname=N'pub', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'MIRROR', @.optname=N'rpc', @.optvalue=N'true'
EXEC master.dbo.sp_serveroption @.server=N'MIRROR', @.optname=N'rpc out', @.optvalue=N'true'
EXEC master.dbo.sp_serveroption @.server=N'MIRROR', @.optname=N'sub', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'MIRROR', @.optname=N'connect timeout', @.optvalue=N'1000'
EXEC master.dbo.sp_serveroption @.server=N'MIRROR', @.optname=N'collation name', @.optvalue=null
EXEC master.dbo.sp_serveroption @.server=N'MIRROR', @.optname=N'lazy schema validation', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'MIRROR', @.optname=N'query timeout', @.optvalue=N'0'
EXEC master.dbo.sp_serveroption @.server=N'MIRROR', @.optname=N'use remote collation', @.optvalue=N'true'select top 10 * from MIRROR.pubs.dbo.authors
What am I missing?
Piers.
Accessing Views via ODBC from MSAccess
Hello,
This started as a simple thing, allow somebody to access to a database on a SQL 2005 server so they could get to it from MSAccess. I have created a login (Windows Authentication) so they can see the database and gave them "public" and "datareader" abilities.
The user then informed me that while they could see the views they could not see the data. Not being a DBA (sorry) I added "public" to the view's permissions (under properties) and granted it "Select" access and nothing else. After I completed this the user could not even see the views to pick them (via the ODBC link from MSAccess) and when I try to remove "public" from the permissions it dissapears, but when I reopen the permission "public" is back.
I then find out there is no data in the views and so the user would not have been able view anything anyway.
So what I want to do is remove "public" from the views permissions, is this possible?
Thanks for any help,
Tyrone
Hi,naviagte in SSMS to the database open > Security > Roles > Database Roles > public > Right click Properties , Remove the permission from the public group to view the schema / Object.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de|||
Hi,
Thanks for the reply... now the strnage big... when I go to the public properties I cannot really find anything to remove. Nothing is ticked, any ideas?
Tyrone
Accessing Users & Members in Active Directory via Linked Server
Hi,
I'm trying to extract all the users and their membership to groups, and the membership of groups to groups from active directory though a link to server.
I can get the users. I can get the groups.... individually.
I can't get the info of what user is a member of or who are members of a group.
Anyone know how to do this or am I going to have to right a vb app? (Anyone already got the code...)
I want to load this data into tables for reporting in my Data Warehouse.
Cheers
Chris
Take a look at the following API:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/netmgmt/netmgmt/netgroupgetusers.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/netmgmt/netmgmt/netlocalgroupgetmembers.asp
Follow the links from the bottom of the articles for other related API.
Hope this helps.
Laurentiu
Sunday, February 19, 2012
Accessing the ReportService.asmx via an ASP.NET page
Hi all, I have an ASP.NET page which I want to use to be able to upload
*.rdl documents to the report server "on the fly".
When I call the webservice via some code in my ASP.NET webpage I receive the
following error
"The request failed with HTTP status 401: Unauthorized."
Any ideas more than welcome..
TIA MarkHi all, a bit of research produced the following:
http://support.microsoft.com/default.aspx?scid=kb;en-us;811318
All fixed and working now :)
Cheers
Mark
"Mark" <dont@.spam.me> wrote in message
news:ud3zhsenFHA.3408@.tk2msftngp13.phx.gbl...
> Hi all, I have an ASP.NET page which I want to use to be able to upload
> *.rdl documents to the report server "on the fly".
> When I call the webservice via some code in my ASP.NET webpage I receive
the
> following error
> "The request failed with HTTP status 401: Unauthorized."
> Any ideas more than welcome..
> TIA Mark
>
*.rdl documents to the report server "on the fly".
When I call the webservice via some code in my ASP.NET webpage I receive the
following error
"The request failed with HTTP status 401: Unauthorized."
Any ideas more than welcome..
TIA MarkHi all, a bit of research produced the following:
http://support.microsoft.com/default.aspx?scid=kb;en-us;811318
All fixed and working now :)
Cheers
Mark
"Mark" <dont@.spam.me> wrote in message
news:ud3zhsenFHA.3408@.tk2msftngp13.phx.gbl...
> Hi all, I have an ASP.NET page which I want to use to be able to upload
> *.rdl documents to the report server "on the fly".
> When I call the webservice via some code in my ASP.NET webpage I receive
the
> following error
> "The request failed with HTTP status 401: Unauthorized."
> Any ideas more than welcome..
> TIA Mark
>
Accessing the FileSystem
Is there a way to access the File System via T-SQL. There is a field in a
table of mine. In this table there is a filename to a file on my server. I
want to make sure that the filename actually points to a file on the server.
In other words I want to make sure the file exists!
Thanks in advancetry this
exec master..xp_fileexist 'C:\Program Files\Microsoft SQL
Server\MSSQL\readme.txt'
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Also try OLE Automation stored procedures.
Search for "OLE automation, FolderExists, copy, etc from stored
procedure" in this newsgroup. Date Posted: 4/26/2006 11:08:00 PM.
*** Sent via Developersdex http://www.examnotes.net ***
table of mine. In this table there is a filename to a file on my server. I
want to make sure that the filename actually points to a file on the server.
In other words I want to make sure the file exists!
Thanks in advancetry this
exec master..xp_fileexist 'C:\Program Files\Microsoft SQL
Server\MSSQL\readme.txt'
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Also try OLE Automation stored procedures.
Search for "OLE automation, FolderExists, copy, etc from stored
procedure" in this newsgroup. Date Posted: 4/26/2006 11:08:00 PM.
*** Sent via Developersdex http://www.examnotes.net ***
Accessing System Views via linked servers
Is there anyway to access system views on/from a linked server?
I have unsuccessfully tried various permutations of
select *
from [MDEDATAWTD\ss2005].master.[information_schema.colums]
ThanksYou are making the table and column name appear as one object. This works fine for me:
SELECT *
FROM [MyServer].master.information_schema.columns
2005 also. High priveledge linked account.
HTH|||You are making the table and column name appear as one object.Not very helpful huh?
I mean with this:
...[information_schema.colums]|||Thanks.
It is not working for me even though I can access the data bases directly. I am going to try a different tack. This approach is chewing too much time.|||Ok - but if you do decide to stick with it check that your linked server maps to an account with the correct privledges. Remember - your domain account could be locally mapped to a remote SQL account that has insufficient rights.
I suppose we'd need to know the error too.
Anyway - just a thought :)|||Thanks for the additional insight.
I also discovered that I really didn't want the system tables from a "linked server" but the system views from a database on the "linked server". Either way I could not get it to work. As I said the benefit gained by procedurally scripting the change looks to be longer than just manually doing the change.
FYI: The error was of the "object does not exist" variety.|||Awww, c'mon Bartron...don't give up now! :) You'll need the linked server experience somewhere down the line!
It's very common for the "object does not exist" error to be due to permissions issues as previously pointed out.
I have unsuccessfully tried various permutations of
select *
from [MDEDATAWTD\ss2005].master.[information_schema.colums]
ThanksYou are making the table and column name appear as one object. This works fine for me:
SELECT *
FROM [MyServer].master.information_schema.columns
2005 also. High priveledge linked account.
HTH|||You are making the table and column name appear as one object.Not very helpful huh?
I mean with this:
...[information_schema.colums]|||Thanks.
It is not working for me even though I can access the data bases directly. I am going to try a different tack. This approach is chewing too much time.|||Ok - but if you do decide to stick with it check that your linked server maps to an account with the correct privledges. Remember - your domain account could be locally mapped to a remote SQL account that has insufficient rights.
I suppose we'd need to know the error too.
Anyway - just a thought :)|||Thanks for the additional insight.
I also discovered that I really didn't want the system tables from a "linked server" but the system views from a database on the "linked server". Either way I could not get it to work. As I said the benefit gained by procedurally scripting the change looks to be longer than just manually doing the change.
FYI: The error was of the "object does not exist" variety.|||Awww, c'mon Bartron...don't give up now! :) You'll need the linked server experience somewhere down the line!
It's very common for the "object does not exist" error to be due to permissions issues as previously pointed out.
Accessing SWebmObjectSet Objects
I'm working in an environment where domain structure and firewall rules only allow access to a SQL Server - including OS - via a SQL Server client connection. I'm attempting to collect various pieces of information for an inventory database that are not normally accessible through standard t-sql calls.
A specific example is collecting network adapter and IP information via WMI and sp_OAxxx procedures. I've been able to interface WMI and retrieve the SWebmObjectSet collection with the information I want, but I can't seem to get to the individual objects in the collection for two reasons. First, t-sql doesn't have any sort of "for each" construct that allows me to iterate through the objects. Second, the Item() method of SWebmObjectSet requires an object path that I haven't been able to enumerate.
I'm avoiding enabling xp_cmdshell in SQL 2005 so calls such as "ipconfig /all" are not at option at this point.
How can I access the individual objects in the collection via t-sql? Is there another technology I might use? Remember - I can only access via a standard SQL client.
Here is some code I've come up with so far.
-- INITIALIZE SCRIPT
DECLARE@.wmiLocatorINT,
@.wmiServicesINT,
@.wmiObjectSetINT,
@.wmiObjectCountINT,
@.wmiObjectINT,
@.wmiNetAdapterNameNVARCHAR(200),
@.wmiNetAdapterIPNVARCHAR(200),
@.loopIdxINT,
@.oleSourceNVARCHAR(500),
@.oldDescNVARCHAR(500),
@.rcBIGINT,
@.msgNVARCHAR(400)
-- INITIALIZE WMI COM OBJECTS
EXEC @.rc = master.dbo.sp_OACreate 'WbemScripting.SWbemLocator', @.wmiLocator OUTPUT
IF @.rc <> 0 BEGIN
PRINT 'Create WMI object failed'
RETURN
END ELSE BEGIN
EXEC @.rc = master.dbo.sp_OAMethod @.wmiLocator, 'ConnectServer', @.wmiServices OUTPUT, '.'
IF @.rc <> 0 BEGIN
EXEC master.dbo.sp_OADestroy @.wmiLocator
RETURN
END
END
-- COLLECT DESIRED DATA
EXEC@.rc = master.dbo.sp_OAMethod @.wmiServices, 'InstancesOf', @.wmiObjectSet OUTPUT, 'Win32_NetworkAdapterConfiguration'
EXEC@.wmiObjectCount= master.dbo.sp_OAGetProperty @.wmiObjectSet, 'Count', @.wmiObjectCount OUTPUT
SELECT@.loopIdx= 0
WHILE @.loopIdx < @.wmiObjectCount - 1 BEGIN
EXEC@.rc= master.dbo.sp_OAMethod @.wmiObjectSet, 'Item', @.wmiObject OUTPUT, @.loopIdx
IF @.rc <> 0 BEGIN
EXEC@.rc= master.dbo.sp_OAGetErrorInfo @.wmiObjectSet, @.oleSource OUTPUT, @.oldDesc OUTPUT
END ELSE BEGIN
EXEC@.rc= master.dbo.sp_OAGetProperty @.wmiObject, 'Caption', @.wmiNetAdapterName OUTPUT
EXEC@.rc= master.dbo.sp_OAGetProperty @.wmiObject, 'IPAddress', @.wmiNetAdapterIP OUTPUT
END
SELECT@.loopIdx= @.loopIdx + 1
END
-- CLEANUP
EXEC master.dbo.sp_OADestroy @.wmiServices
EXEC master.dbo.sp_OADestroy @.wmiLocator
Is it fair to assume that you have completely explored and discarded the various system metadata, security, stastical and configuration functions, as well as the ODBC functions?
Would it be possible to have a Windows Scheduler task that would freqently run, using SQLCmd.exe to populate a table in the server with the desired information?
|||If you mean the SQL Server metadata, security, statistical and configuration functions, then yes I have. But it is entirely possible I missed something which is why I posted the question.
It is possible to use the Windows Scheduler as you noted. I'm not a fan of installing DBA utilities on every server I manage if I can get away with a centralized solution. Additionally, there are many devices in scope that have firewall rules preventing console access and/or file transfer mechanisms. In short, the only mechanism I have is a standard SQL client. That is not to say that I couldn't petition the security team for relaxed access, but security is king here and it would be a battle I would likely lose.
One solution I've considered is use of the xp_cmdshell. Enabling it in SQL 2005 is possible, but I would prefer to leave it alone if I can.
Bruce.
|||This really sounds like a task for an administrative WMI script -controlled and executed by the system administrators, retrieving the data and storing it in a central server for you to access. I would think that such an approach would molify the net administrator's security concerns. Easily done with MOM/SMS or whatever monitoring/management software is being used.
Accessing Stored Procedure from IIS
I have a stored procedure that is supposed to
1. Increment a counter in Table A via a transaction
2. Use this value as the primary key to add in an address to customers
Table B
(Referenced as a "DECLARE @.CustomerID INT" just after the AS
clause)
3. Return the primary key.
This works perfectly when being called from Query Analyzer supplying values
in an EXEC line, however, accessing it from .ASP (IIS 5.0 on Win2K), the
execution falls right through without adding the customer or incrementing
the counter or giving an error. All conditional routines are executed, but
no work is being done.
Is there anything I can do to raise some sort of error to let me know what
is or isn't happening?
adovbs.inc is linked and the "conditional code" I refer to swaps the stored
procedure name (for add/edit) to add in one more parameter needed for
editing records. The parameters are referenced in exactly the same order as
they are in the procedures, with the return value being mentioned first.
The append parameters lines have been rewritten in short form, long form,
and in a "with" block as shown.
for example:
(Blocked within conditional code)
adocmd.CommandType = adCmdStoredProc
adocmd.CommandText = "spr_AddCustomer"
adocmd.ActiveConnection = conn.ConnectionObject
set param = adocmd.createparameter("@.RETURN_VALUE", adInteger,
adParamReturnValue, 0)
adocmd.parameters.append param
(Conditional code end)
With adocmd
set param = .createparameter("@.Company", adVarChar, adParamInput, 40,
company)
.parameters.append param
set param = .createparameter("@.FirstName", adVarChar, adParamInput, 15,
firstname)
.parameters.append param
set param = .createparameter("@.MiddleInitial", adVarChar, adParamInput,
1, middleinitial)
.parameters.append param
set param = .createparameter("@.LastName", adVarChar, adParamInput, 20,
lastname)
.parameters.append param
... (continuing to add parameters in the same order as SP)
.execute lngRecs,,adexecutenorecords
CustomerId = .Parameters("@.RETURN_VALUE").Value
End WithMake sure you disable "on error resume next" in your ASP page.
Make sure the stored procedure has SET NOCOUNT ON at the beginning.
Have a look at http://www.aspfaq.com/2201
http://www.aspfaq.com/
(Reverse address to reply.)
"stjulian" <anonymous@.discussions.microsoft.com> wrote in message
news:#l$GjY2GFHA.2736@.TK2MSFTNGP09.phx.gbl...
> I have a stored procedure that is supposed to
> 1. Increment a counter in Table A via a transaction
> 2. Use this value as the primary key to add in an address to customers
> Table B
> (Referenced as a "DECLARE @.CustomerID INT" just after the AS
> clause)
> 3. Return the primary key.
> This works perfectly when being called from Query Analyzer supplying
values
> in an EXEC line, however, accessing it from .ASP (IIS 5.0 on Win2K), the
> execution falls right through without adding the customer or incrementing
> the counter or giving an error. All conditional routines are executed, but
> no work is being done.
> Is there anything I can do to raise some sort of error to let me know what
> is or isn't happening?
> adovbs.inc is linked and the "conditional code" I refer to swaps the
stored
> procedure name (for add/edit) to add in one more parameter needed for
> editing records. The parameters are referenced in exactly the same order
as
> they are in the procedures, with the return value being mentioned first.
> The append parameters lines have been rewritten in short form, long form,
> and in a "with" block as shown.
> for example:
> (Blocked within conditional code)
> adocmd.CommandType = adCmdStoredProc
> adocmd.CommandText = "spr_AddCustomer"
> adocmd.ActiveConnection = conn.ConnectionObject
> set param = adocmd.createparameter("@.RETURN_VALUE", adInteger,
> adParamReturnValue, 0)
> adocmd.parameters.append param
> (Conditional code end)
> With adocmd
> set param = .createparameter("@.Company", adVarChar, adParamInput, 40,
> company)
> .parameters.append param
> set param = .createparameter("@.FirstName", adVarChar, adParamInput, 15,
> firstname)
> .parameters.append param
> set param = .createparameter("@.MiddleInitial", adVarChar, adParamInput,
> 1, middleinitial)
> .parameters.append param
> set param = .createparameter("@.LastName", adVarChar, adParamInput, 20,
> lastname)
> .parameters.append param
> ... (continuing to add parameters in the same order as SP)
> .execute lngRecs,,adexecutenorecords
> CustomerId = .Parameters("@.RETURN_VALUE").Value
> End With
>|||Hi
You don't provide DDL for the procedure.
http://www.aspfaq.com/etiquette.asp?id=5006
You may want to check what is happening using profiler, and make sure that
NOCOUNT is ON.
John
"stjulian" wrote:
> I have a stored procedure that is supposed to
> 1. Increment a counter in Table A via a transaction
> 2. Use this value as the primary key to add in an address to customers
> Table B
> (Referenced as a "DECLARE @.CustomerID INT" just after the AS
> clause)
> 3. Return the primary key.
> This works perfectly when being called from Query Analyzer supplying value
s
> in an EXEC line, however, accessing it from .ASP (IIS 5.0 on Win2K), the
> execution falls right through without adding the customer or incrementing
> the counter or giving an error. All conditional routines are executed, but
> no work is being done.
> Is there anything I can do to raise some sort of error to let me know what
> is or isn't happening?
> adovbs.inc is linked and the "conditional code" I refer to swaps the store
d
> procedure name (for add/edit) to add in one more parameter needed for
> editing records. The parameters are referenced in exactly the same order a
s
> they are in the procedures, with the return value being mentioned first.
> The append parameters lines have been rewritten in short form, long form,
> and in a "with" block as shown.
> for example:
> (Blocked within conditional code)
> adocmd.CommandType = adCmdStoredProc
> adocmd.CommandText = "spr_AddCustomer"
> adocmd.ActiveConnection = conn.ConnectionObject
> set param = adocmd.createparameter("@.RETURN_VALUE", adInteger,
> adParamReturnValue, 0)
> adocmd.parameters.append param
> (Conditional code end)
> With adocmd
> set param = .createparameter("@.Company", adVarChar, adParamInput, 40,
> company)
> .parameters.append param
> set param = .createparameter("@.FirstName", adVarChar, adParamInput, 15,
> firstname)
> .parameters.append param
> set param = .createparameter("@.MiddleInitial", adVarChar, adParamInput,
> 1, middleinitial)
> .parameters.append param
> set param = .createparameter("@.LastName", adVarChar, adParamInput, 20,
> lastname)
> .parameters.append param
> ... (continuing to add parameters in the same order as SP)
> .execute lngRecs,,adexecutenorecords
> CustomerId = .Parameters("@.RETURN_VALUE").Value
> End With
>
>|||Thank you both for your attention...
DDL follows
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/****** Object: Stored Procedure dbo.spr_WriteCustomers Created: 2/24/05
JS ******/
CREATE PROCEDURE spr_WriteCustomers
@.Company varchar(40) = NULL,
@.FirstName varchar(15) = NULL,
@.MiddleInitial varchar(1) = NULL,
@.LastName varchar(20) = NULL,
@.Title varchar(30) = NULL,
@.BillingAttnLine varchar(40) = NULL,
@.BillingAddress1 varchar(40) = NULL,
@.BillingAddress2 varchar(40) = NULL,
@.BillingCity varchar(20) = NULL,
@.BillingState varchar(3) = NULL,
@.BillingZip varchar(10) = NULL,
@.FK_CountryCode varchar(3) = NULL,
@.BillingCountry varchar(25) = NULL,
@.BillingPhone varchar(25) = NULL,
@.BillingFax varchar(15) = NULL,
@.ShippingFirstName varchar(15) = NULL,
@.ShippingLastName varchar(20) = NULL,
@.ShippingCompany varchar(40) = NULL,
@.ShippingTitle varchar(40) = NULL,
@.ShippingAttnLine varchar(40) = NULL,
@.ShippingAddress1 varchar(40) = NULL,
@.ShippingAddress2 varchar(40) = NULL,
@.ShippingCity varchar(20) = NULL,
@.ShippingState varchar(3) = NULL,
@.ShippingZip varchar(10) = NULL,
@.FK_SCountryCode varchar(3) = NULL,
@.ShippingCountry varchar(25) = NULL,
@.ShippingPhone varchar(25) = NULL,
@.ShippingFax varchar(15) = NULL,
@.FK_CustomerTierID int = 0,
@.UserName varchar(45) = NULL,
@.Password varchar(20) = NULL,
@.EMail varchar(45) = NULL,
@.TaxExempt bit = 0,
@.NoEmail bit= 0,
@.GREETING1 varchar(35) = NULL,
@.GREETING2 varchar(35) = NULL,
@.BelongsTo int = 0
AS
BEGIN
SET NOCOUNT ON
DECLARE @.custid INT
DECLARE @.CREATEDATE DATETIME
-- Begin process
--Get New CustomerID
BEGIN TRAN
SELECT @.custid = nextid
FROM tblAutoNumber
WHERE TableName = 'tblCustomers'
UPDATE tblAutoNumber
SET nextid = @.custid + 1
WHERE TableName = 'tblCustomers'
COMMIT TRAN
SELECT @.CREATEDATE = getdate()
BEGIN
INSERT INTO tblCustomers
(PK_ID,
Company,
FirstName,
MiddleInitial,
LastName,
Title,
BillingAttnLine,
BillingAddress1,
BillingAddress2,
BillingCity,
BillingState,
BillingZip,
FK_CountryCode,
BillingCountry,
ShippingFirstName,
ShippingLastName,
ShippingCompany,
ShippingTitle,
ShippingAttnLine,
ShippingAddress1,
ShippingAddress2,
ShippingCity,
ShippingState,
ShippingZip,
FK_SCountryCode,
ShippingCountry,
FK_CustomerTierID,
UserName,
Password,
Email,
BillingPhone,
ShippingPhone,
BillingFax,
ShippingFax,
LeaseStatus,
LeaseCreditLimit,
FK_CurrencyId,
DisableLogin,
LastModified,
Created,
TaxExempt,
NoEmail,
GREETING1,
GREETING2,
TaxExemptVerified,
AutoCancel,
LastLogin,
BelongsTo)
VALUES(
@.custid,
@.Company,
@.FirstName,
@.MiddleInitial,
@.LastName,
@.Title,
@.BillingAttnLine,
@.BillingAddress1,
@.BillingAddress2,
@.BillingCity,
@.BillingState,
@.BillingZip,
@.FK_CountryCode,
@.BillingCountry,
@.ShippingFirstName,
@.ShippingLastName,
@.ShippingCompany,
@.ShippingTitle,
@.ShippingAttnLine,
@.ShippingAddress1,
@.ShippingAddress2,
@.ShippingCity,
@.ShippingState,
@.ShippingZip,
@.FK_SCountryCode,
@.ShippingCountry,
@.FK_CustomerTierID,
@.UserName,
@.Password,
@.Email,
@.BillingPhone,
@.ShippingPhone,
@.BillingFax,
@.ShippingFax,
'',
0,
0,
0,
@.CREATEDATE,
@.CREATEDATE,
@.TaxExempt,
@.NoEmail,
@.GREETING1,
@.GREETING2,
0,
0,
@.CREATEDATE,
@.BelongsTo)
END
RETURN @.custid
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:A7EC9405-C84D-4ABC-B9D7-34ED6A4A1BC6@.microsoft.com...
> Hi
> You don't provide DDL for the procedure.
> http://www.aspfaq.com/etiquette.asp?id=5006
> You may want to check what is happening using profiler, and make sure that
> NOCOUNT is ON.
> John
>
> "stjulian" wrote:
>|||Wait, I think I got it ... The On Error was in an include file.
Thank you all for your help.
Julian
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:A7EC9405-C84D-4ABC-B9D7-34ED6A4A1BC6@.microsoft.com...
> Hi
> You don't provide DDL for the procedure.
> http://www.aspfaq.com/etiquette.asp?id=5006
> You may want to check what is happening using profiler, and make sure that
> NOCOUNT is ON.
> John
>
> "stjulian" wrote:
>
1. Increment a counter in Table A via a transaction
2. Use this value as the primary key to add in an address to customers
Table B
(Referenced as a "DECLARE @.CustomerID INT" just after the AS
clause)
3. Return the primary key.
This works perfectly when being called from Query Analyzer supplying values
in an EXEC line, however, accessing it from .ASP (IIS 5.0 on Win2K), the
execution falls right through without adding the customer or incrementing
the counter or giving an error. All conditional routines are executed, but
no work is being done.
Is there anything I can do to raise some sort of error to let me know what
is or isn't happening?
adovbs.inc is linked and the "conditional code" I refer to swaps the stored
procedure name (for add/edit) to add in one more parameter needed for
editing records. The parameters are referenced in exactly the same order as
they are in the procedures, with the return value being mentioned first.
The append parameters lines have been rewritten in short form, long form,
and in a "with" block as shown.
for example:
(Blocked within conditional code)
adocmd.CommandType = adCmdStoredProc
adocmd.CommandText = "spr_AddCustomer"
adocmd.ActiveConnection = conn.ConnectionObject
set param = adocmd.createparameter("@.RETURN_VALUE", adInteger,
adParamReturnValue, 0)
adocmd.parameters.append param
(Conditional code end)
With adocmd
set param = .createparameter("@.Company", adVarChar, adParamInput, 40,
company)
.parameters.append param
set param = .createparameter("@.FirstName", adVarChar, adParamInput, 15,
firstname)
.parameters.append param
set param = .createparameter("@.MiddleInitial", adVarChar, adParamInput,
1, middleinitial)
.parameters.append param
set param = .createparameter("@.LastName", adVarChar, adParamInput, 20,
lastname)
.parameters.append param
... (continuing to add parameters in the same order as SP)
.execute lngRecs,,adexecutenorecords
CustomerId = .Parameters("@.RETURN_VALUE").Value
End WithMake sure you disable "on error resume next" in your ASP page.
Make sure the stored procedure has SET NOCOUNT ON at the beginning.
Have a look at http://www.aspfaq.com/2201
http://www.aspfaq.com/
(Reverse address to reply.)
"stjulian" <anonymous@.discussions.microsoft.com> wrote in message
news:#l$GjY2GFHA.2736@.TK2MSFTNGP09.phx.gbl...
> I have a stored procedure that is supposed to
> 1. Increment a counter in Table A via a transaction
> 2. Use this value as the primary key to add in an address to customers
> Table B
> (Referenced as a "DECLARE @.CustomerID INT" just after the AS
> clause)
> 3. Return the primary key.
> This works perfectly when being called from Query Analyzer supplying
values
> in an EXEC line, however, accessing it from .ASP (IIS 5.0 on Win2K), the
> execution falls right through without adding the customer or incrementing
> the counter or giving an error. All conditional routines are executed, but
> no work is being done.
> Is there anything I can do to raise some sort of error to let me know what
> is or isn't happening?
> adovbs.inc is linked and the "conditional code" I refer to swaps the
stored
> procedure name (for add/edit) to add in one more parameter needed for
> editing records. The parameters are referenced in exactly the same order
as
> they are in the procedures, with the return value being mentioned first.
> The append parameters lines have been rewritten in short form, long form,
> and in a "with" block as shown.
> for example:
> (Blocked within conditional code)
> adocmd.CommandType = adCmdStoredProc
> adocmd.CommandText = "spr_AddCustomer"
> adocmd.ActiveConnection = conn.ConnectionObject
> set param = adocmd.createparameter("@.RETURN_VALUE", adInteger,
> adParamReturnValue, 0)
> adocmd.parameters.append param
> (Conditional code end)
> With adocmd
> set param = .createparameter("@.Company", adVarChar, adParamInput, 40,
> company)
> .parameters.append param
> set param = .createparameter("@.FirstName", adVarChar, adParamInput, 15,
> firstname)
> .parameters.append param
> set param = .createparameter("@.MiddleInitial", adVarChar, adParamInput,
> 1, middleinitial)
> .parameters.append param
> set param = .createparameter("@.LastName", adVarChar, adParamInput, 20,
> lastname)
> .parameters.append param
> ... (continuing to add parameters in the same order as SP)
> .execute lngRecs,,adexecutenorecords
> CustomerId = .Parameters("@.RETURN_VALUE").Value
> End With
>|||Hi
You don't provide DDL for the procedure.
http://www.aspfaq.com/etiquette.asp?id=5006
You may want to check what is happening using profiler, and make sure that
NOCOUNT is ON.
John
"stjulian" wrote:
> I have a stored procedure that is supposed to
> 1. Increment a counter in Table A via a transaction
> 2. Use this value as the primary key to add in an address to customers
> Table B
> (Referenced as a "DECLARE @.CustomerID INT" just after the AS
> clause)
> 3. Return the primary key.
> This works perfectly when being called from Query Analyzer supplying value
s
> in an EXEC line, however, accessing it from .ASP (IIS 5.0 on Win2K), the
> execution falls right through without adding the customer or incrementing
> the counter or giving an error. All conditional routines are executed, but
> no work is being done.
> Is there anything I can do to raise some sort of error to let me know what
> is or isn't happening?
> adovbs.inc is linked and the "conditional code" I refer to swaps the store
d
> procedure name (for add/edit) to add in one more parameter needed for
> editing records. The parameters are referenced in exactly the same order a
s
> they are in the procedures, with the return value being mentioned first.
> The append parameters lines have been rewritten in short form, long form,
> and in a "with" block as shown.
> for example:
> (Blocked within conditional code)
> adocmd.CommandType = adCmdStoredProc
> adocmd.CommandText = "spr_AddCustomer"
> adocmd.ActiveConnection = conn.ConnectionObject
> set param = adocmd.createparameter("@.RETURN_VALUE", adInteger,
> adParamReturnValue, 0)
> adocmd.parameters.append param
> (Conditional code end)
> With adocmd
> set param = .createparameter("@.Company", adVarChar, adParamInput, 40,
> company)
> .parameters.append param
> set param = .createparameter("@.FirstName", adVarChar, adParamInput, 15,
> firstname)
> .parameters.append param
> set param = .createparameter("@.MiddleInitial", adVarChar, adParamInput,
> 1, middleinitial)
> .parameters.append param
> set param = .createparameter("@.LastName", adVarChar, adParamInput, 20,
> lastname)
> .parameters.append param
> ... (continuing to add parameters in the same order as SP)
> .execute lngRecs,,adexecutenorecords
> CustomerId = .Parameters("@.RETURN_VALUE").Value
> End With
>
>|||Thank you both for your attention...
DDL follows
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/****** Object: Stored Procedure dbo.spr_WriteCustomers Created: 2/24/05
JS ******/
CREATE PROCEDURE spr_WriteCustomers
@.Company varchar(40) = NULL,
@.FirstName varchar(15) = NULL,
@.MiddleInitial varchar(1) = NULL,
@.LastName varchar(20) = NULL,
@.Title varchar(30) = NULL,
@.BillingAttnLine varchar(40) = NULL,
@.BillingAddress1 varchar(40) = NULL,
@.BillingAddress2 varchar(40) = NULL,
@.BillingCity varchar(20) = NULL,
@.BillingState varchar(3) = NULL,
@.BillingZip varchar(10) = NULL,
@.FK_CountryCode varchar(3) = NULL,
@.BillingCountry varchar(25) = NULL,
@.BillingPhone varchar(25) = NULL,
@.BillingFax varchar(15) = NULL,
@.ShippingFirstName varchar(15) = NULL,
@.ShippingLastName varchar(20) = NULL,
@.ShippingCompany varchar(40) = NULL,
@.ShippingTitle varchar(40) = NULL,
@.ShippingAttnLine varchar(40) = NULL,
@.ShippingAddress1 varchar(40) = NULL,
@.ShippingAddress2 varchar(40) = NULL,
@.ShippingCity varchar(20) = NULL,
@.ShippingState varchar(3) = NULL,
@.ShippingZip varchar(10) = NULL,
@.FK_SCountryCode varchar(3) = NULL,
@.ShippingCountry varchar(25) = NULL,
@.ShippingPhone varchar(25) = NULL,
@.ShippingFax varchar(15) = NULL,
@.FK_CustomerTierID int = 0,
@.UserName varchar(45) = NULL,
@.Password varchar(20) = NULL,
@.EMail varchar(45) = NULL,
@.TaxExempt bit = 0,
@.NoEmail bit= 0,
@.GREETING1 varchar(35) = NULL,
@.GREETING2 varchar(35) = NULL,
@.BelongsTo int = 0
AS
BEGIN
SET NOCOUNT ON
DECLARE @.custid INT
DECLARE @.CREATEDATE DATETIME
-- Begin process
--Get New CustomerID
BEGIN TRAN
SELECT @.custid = nextid
FROM tblAutoNumber
WHERE TableName = 'tblCustomers'
UPDATE tblAutoNumber
SET nextid = @.custid + 1
WHERE TableName = 'tblCustomers'
COMMIT TRAN
SELECT @.CREATEDATE = getdate()
BEGIN
INSERT INTO tblCustomers
(PK_ID,
Company,
FirstName,
MiddleInitial,
LastName,
Title,
BillingAttnLine,
BillingAddress1,
BillingAddress2,
BillingCity,
BillingState,
BillingZip,
FK_CountryCode,
BillingCountry,
ShippingFirstName,
ShippingLastName,
ShippingCompany,
ShippingTitle,
ShippingAttnLine,
ShippingAddress1,
ShippingAddress2,
ShippingCity,
ShippingState,
ShippingZip,
FK_SCountryCode,
ShippingCountry,
FK_CustomerTierID,
UserName,
Password,
Email,
BillingPhone,
ShippingPhone,
BillingFax,
ShippingFax,
LeaseStatus,
LeaseCreditLimit,
FK_CurrencyId,
DisableLogin,
LastModified,
Created,
TaxExempt,
NoEmail,
GREETING1,
GREETING2,
TaxExemptVerified,
AutoCancel,
LastLogin,
BelongsTo)
VALUES(
@.custid,
@.Company,
@.FirstName,
@.MiddleInitial,
@.LastName,
@.Title,
@.BillingAttnLine,
@.BillingAddress1,
@.BillingAddress2,
@.BillingCity,
@.BillingState,
@.BillingZip,
@.FK_CountryCode,
@.BillingCountry,
@.ShippingFirstName,
@.ShippingLastName,
@.ShippingCompany,
@.ShippingTitle,
@.ShippingAttnLine,
@.ShippingAddress1,
@.ShippingAddress2,
@.ShippingCity,
@.ShippingState,
@.ShippingZip,
@.FK_SCountryCode,
@.ShippingCountry,
@.FK_CustomerTierID,
@.UserName,
@.Password,
@.Email,
@.BillingPhone,
@.ShippingPhone,
@.BillingFax,
@.ShippingFax,
'',
0,
0,
0,
@.CREATEDATE,
@.CREATEDATE,
@.TaxExempt,
@.NoEmail,
@.GREETING1,
@.GREETING2,
0,
0,
@.CREATEDATE,
@.BelongsTo)
END
RETURN @.custid
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:A7EC9405-C84D-4ABC-B9D7-34ED6A4A1BC6@.microsoft.com...
> Hi
> You don't provide DDL for the procedure.
> http://www.aspfaq.com/etiquette.asp?id=5006
> You may want to check what is happening using profiler, and make sure that
> NOCOUNT is ON.
> John
>
> "stjulian" wrote:
>|||Wait, I think I got it ... The On Error was in an include file.
Thank you all for your help.
Julian
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:A7EC9405-C84D-4ABC-B9D7-34ED6A4A1BC6@.microsoft.com...
> Hi
> You don't provide DDL for the procedure.
> http://www.aspfaq.com/etiquette.asp?id=5006
> You may want to check what is happening using profiler, and make sure that
> NOCOUNT is ON.
> John
>
> "stjulian" wrote:
>
Thursday, February 16, 2012
Accessing SQL via http
Hello,
Apologies if this is in the wrong newsgroup...
I have followed the tutorial in Books Online that is called "Creating the
nwind Virtual Directory". Everything looks ok but I get an error that
says: -
"HTTP Error 404 - File or directory not found. Internet Information Services
(IIS) when I try to test it using http://<IISServer>/nwind?sql=SELECT * FROM
Employees FOR XML AUTO&root=root
I have looked all over trying to find out what I have done wrong :(
Is there anything else that needs to be set up?
Thanks in advance
Brian.Hi
You did change <IISServer> for the actual server name?
If all the properties look ok, then you may want to try restarting the web
server to see if that helps.
Make sure that you can ping the database server from the web server.
John
"Lost and Confused" <lostand
@.nowhere.com> wrote in message
news:431f2057$0$30311$da0feed9@.news.zen.co.uk...
> Hello,
> Apologies if this is in the wrong newsgroup...
> I have followed the tutorial in Books Online that is called "Creating the
> nwind Virtual Directory". Everything looks ok but I get an error that
> says: -
> "HTTP Error 404 - File or directory not found. Internet Information
> Services
> (IIS) when I try to test it using http://<IISServer>/nwind?sql=SELECT *
> FROM
> Employees FOR XML AUTO&root=root
> I have looked all over trying to find out what I have done wrong :(
> Is there anything else that needs to be set up?
> Thanks in advance
> Brian.
>|||Hi John,
Yes, I amended "<IISServer>" to "myserv"
Excuse my ignorance but I'm not sure about restarting the web server. Do you
mean the 'World Wide Web Publishing Service' in 'Services'?
Thanks for replying
Brian.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:umSswh$sFHA.4076@.TK2MSFTNGP11.phx.gbl...
> Hi
> You did change <IISServer> for the actual server name?
> If all the properties look ok, then you may want to try restarting the web
> server to see if that helps.
> Make sure that you can ping the database server from the web server.
> John
> "Lost and Confused" <lostand@.nowhere.com> wrote in message
> news:431f2057$0$30311$da0feed9@.news.zen.co.uk...
>|||Sorry, I forgot to mention that I'm trying to set this up on our server.
The database is on 1 drive, the virtual directory has been set up and I'm
trying to retrieve the data using the same server.
So, no web hosting/external parties as everything is internal.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:umSswh$sFHA.4076@.TK2MSFTNGP11.phx.gbl...
> Hi
> You did change <IISServer> for the actual server name?
> If all the properties look ok, then you may want to try restarting the web
> server to see if that helps.
> Make sure that you can ping the database server from the web server.
> John
> "Lost and Confused" <lostand@.nowhere.com> wrote in message
> news:431f2057$0$30311$da0feed9@.news.zen.co.uk...
>|||Hi
That was the reboot/restart option! Could do stop/start IIS from Internet
Information Services on the Administrators menu.
If all directories/permissions exist and are correct, you may want to try
removing the directory and re-creating it. Check the IIS logs to see if
there is more information.
John
"Lost and Confused" <lostand@.nowhere.com> wrote in message
news:431f6810$0$3556$da0feed9@.news.zen.co.uk...
> Hi John,
> Yes, I amended "<IISServer>" to "myserv"
> Excuse my ignorance but I'm not sure about restarting the web server. Do
> you mean the 'World Wide Web Publishing Service' in 'Services'?
> Thanks for replying
> Brian.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:umSswh$sFHA.4076@.TK2MSFTNGP11.phx.gbl...
>|||Hi John,
I had also posted the problem in the sqlserver.xml newsgroup. One suggestion
was to add a web service extension called SQLXML.
http://support.microsoft.com/defaul...4&Product=sql2k
This worked perfectly :
Thanks for your help
Brian.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:uZNNK2EtFHA.3236@.TK2MSFTNGP14.phx.gbl...
> Hi
> That was the reboot/restart option! Could do stop/start IIS from Internet
> Information Services on the Administrators menu.
> If all directories/permissions exist and are correct, you may want to try
> removing the directory and re-creating it. Check the IIS logs to see if
> there is more information.
> John
> "Lost and Confused" <lostand@.nowhere.com> wrote in message
> news:431f6810$0$3556$da0feed9@.news.zen.co.uk...
>|||Hi
Yes that is a pre-requisite!
John
"Lost and Confused" <lostand@.nowhere.com> wrote in message
news:431ffdb2$0$306$da0feed9@.news.zen.co.uk...
> Hi John,
> I had also posted the problem in the sqlserver.xml newsgroup. One
> suggestion was to add a web service extension called SQLXML.
> [url]http://support.microsoft.com/default.aspx?scid=kb;en-us;810784&Product=sql2k[/ur
l]
> This worked perfectly :
> Thanks for your help
> Brian.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:uZNNK2EtFHA.3236@.TK2MSFTNGP14.phx.gbl...
>
Apologies if this is in the wrong newsgroup...
I have followed the tutorial in Books Online that is called "Creating the
nwind Virtual Directory". Everything looks ok but I get an error that
says: -
"HTTP Error 404 - File or directory not found. Internet Information Services
(IIS) when I try to test it using http://<IISServer>/nwind?sql=SELECT * FROM
Employees FOR XML AUTO&root=root
I have looked all over trying to find out what I have done wrong :(
Is there anything else that needs to be set up?
Thanks in advance
Brian.Hi
You did change <IISServer> for the actual server name?
If all the properties look ok, then you may want to try restarting the web
server to see if that helps.
Make sure that you can ping the database server from the web server.
John
"Lost and Confused" <lostand
@.nowhere.com> wrote in messagenews:431f2057$0$30311$da0feed9@.news.zen.co.uk...
> Hello,
> Apologies if this is in the wrong newsgroup...
> I have followed the tutorial in Books Online that is called "Creating the
> nwind Virtual Directory". Everything looks ok but I get an error that
> says: -
> "HTTP Error 404 - File or directory not found. Internet Information
> Services
> (IIS) when I try to test it using http://<IISServer>/nwind?sql=SELECT *
> FROM
> Employees FOR XML AUTO&root=root
> I have looked all over trying to find out what I have done wrong :(
> Is there anything else that needs to be set up?
> Thanks in advance
> Brian.
>|||Hi John,
Yes, I amended "<IISServer>" to "myserv"
Excuse my ignorance but I'm not sure about restarting the web server. Do you
mean the 'World Wide Web Publishing Service' in 'Services'?
Thanks for replying
Brian.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:umSswh$sFHA.4076@.TK2MSFTNGP11.phx.gbl...
> Hi
> You did change <IISServer> for the actual server name?
> If all the properties look ok, then you may want to try restarting the web
> server to see if that helps.
> Make sure that you can ping the database server from the web server.
> John
> "Lost and Confused" <lostand
@.nowhere.com> wrote in message> news:431f2057$0$30311$da0feed9@.news.zen.co.uk...
>|||Sorry, I forgot to mention that I'm trying to set this up on our server.
The database is on 1 drive, the virtual directory has been set up and I'm
trying to retrieve the data using the same server.
So, no web hosting/external parties as everything is internal.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:umSswh$sFHA.4076@.TK2MSFTNGP11.phx.gbl...
> Hi
> You did change <IISServer> for the actual server name?
> If all the properties look ok, then you may want to try restarting the web
> server to see if that helps.
> Make sure that you can ping the database server from the web server.
> John
> "Lost and Confused" <lostand
@.nowhere.com> wrote in message> news:431f2057$0$30311$da0feed9@.news.zen.co.uk...
>|||Hi
That was the reboot/restart option! Could do stop/start IIS from Internet
Information Services on the Administrators menu.
If all directories/permissions exist and are correct, you may want to try
removing the directory and re-creating it. Check the IIS logs to see if
there is more information.
John
"Lost and Confused" <lostand
@.nowhere.com> wrote in messagenews:431f6810$0$3556$da0feed9@.news.zen.co.uk...
> Hi John,
> Yes, I amended "<IISServer>" to "myserv"
> Excuse my ignorance but I'm not sure about restarting the web server. Do
> you mean the 'World Wide Web Publishing Service' in 'Services'?
> Thanks for replying
> Brian.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:umSswh$sFHA.4076@.TK2MSFTNGP11.phx.gbl...
>|||Hi John,
I had also posted the problem in the sqlserver.xml newsgroup. One suggestion
was to add a web service extension called SQLXML.
http://support.microsoft.com/defaul...4&Product=sql2k
This worked perfectly :
Thanks for your help
Brian.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:uZNNK2EtFHA.3236@.TK2MSFTNGP14.phx.gbl...
> Hi
> That was the reboot/restart option! Could do stop/start IIS from Internet
> Information Services on the Administrators menu.
> If all directories/permissions exist and are correct, you may want to try
> removing the directory and re-creating it. Check the IIS logs to see if
> there is more information.
> John
> "Lost and Confused" <lostand
@.nowhere.com> wrote in message> news:431f6810$0$3556$da0feed9@.news.zen.co.uk...
>|||Hi
Yes that is a pre-requisite!
John
"Lost and Confused" <lostand
@.nowhere.com> wrote in messagenews:431ffdb2$0$306$da0feed9@.news.zen.co.uk...
> Hi John,
> I had also posted the problem in the sqlserver.xml newsgroup. One
> suggestion was to add a web service extension called SQLXML.
> [url]http://support.microsoft.com/default.aspx?scid=kb;en-us;810784&Product=sql2k[/ur
l]
> This worked perfectly :
> Thanks for your help
> Brian.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:uZNNK2EtFHA.3236@.TK2MSFTNGP14.phx.gbl...
>
Accessing SQL via http
Hello,
Apologies if this is in the wrong newsgroup...
I have followed the tutorial in Books Online that is called "Creating the
nwind Virtual Directory". Everything looks ok but I get an error that
says: -
"HTTP Error 404 - File or directory not found. Internet Information Services
(IIS) when I try to test it using http://<IISServer>/nwind?sql=SELECT * FROM
Employees FOR XML AUTO&root=root
I have looked all over trying to find out what I have done wrong :(
Is there anything else that needs to be set up?
Thanks in advance
Brian.Could you please disable "Show friendly HTTP error messages" option in
Advanced tab of IE Options? This should give you more precise error
messages.
In the mean time, please ensure that you enable sql queries on the virtual
directory settings.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lost and Confused" <lostand@.nowhere.com> wrote in message
news:431ef381$0$21190$db0fefd9@.news.zen.co.uk...
> Hello,
> Apologies if this is in the wrong newsgroup...
> I have followed the tutorial in Books Online that is called "Creating the
> nwind Virtual Directory". Everything looks ok but I get an error that
> says: -
> "HTTP Error 404 - File or directory not found. Internet Information
> Services (IIS) when I try to test it using
> http://<IISServer>/nwind?sql=SELECT * FROM Employees FOR XML
> AUTO&root=root
> I have looked all over trying to find out what I have done wrong :(
> Is there anything else that needs to be set up?
> Thanks in advance
> Brian.
>|||Hi,
Thanks for replying, I was beginning to despair ;)
I have disabled the friendly errors as you suggested but the 404 page still
doesn't show any more detail than before.
The sql queries were already enabled on the 'nwind
Properties/Settings/Options' tab
Do you have any other suggestions?
Thanks
Brian.
"Bertan ARI [MSFT]" <bertan@.online.microsoft.com> wrote in message
news:eTzZVB$sFHA.3604@.tk2msftngp13.phx.gbl...
> Could you please disable "Show friendly HTTP error messages" option in
> Advanced tab of IE Options? This should give you more precise error
> messages.
> In the mean time, please ensure that you enable sql queries on the virtual
> directory settings.
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Lost and Confused" <lostand@.nowhere.com> wrote in message
> news:431ef381$0$21190$db0fefd9@.news.zen.co.uk...
>|||I am sorry but that's all I could say. It is strange that you cannot see
more detailed error message instead of 404.
Can you access the web server for different directories and pages?
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lost and Confused" <lostand@.nowhere.com> wrote in message
news:431f5d05$0$17950$db0fefd9@.news.zen.co.uk...
> Hi,
> Thanks for replying, I was beginning to despair ;)
> I have disabled the friendly errors as you suggested but the 404 page
> still doesn't show any more detail than before.
> The sql queries were already enabled on the 'nwind
> Properties/Settings/Options' tab
> Do you have any other suggestions?
> Thanks
> Brian.
>
> "Bertan ARI [MSFT]" <bertan@.online.microsoft.com> wrote in message
> news:eTzZVB$sFHA.3604@.tk2msftngp13.phx.gbl...
>|||I just remember.
If you are using IIS6, you should enable some options on iis configuration
for security reasons:
http://support.microsoft.com/defaul...4&Product=sql2k
If this doesn't work, follow the steps given in this article:
http://sqlxml.org/faqs.aspx?79
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lost and Confused" <lostand@.nowhere.com> wrote in message
news:431f5d05$0$17950$db0fefd9@.news.zen.co.uk...
> Hi,
> Thanks for replying, I was beginning to despair ;)
> I have disabled the friendly errors as you suggested but the 404 page
> still doesn't show any more detail than before.
> The sql queries were already enabled on the 'nwind
> Properties/Settings/Options' tab
> Do you have any other suggestions?
> Thanks
> Brian.
>
> "Bertan ARI [MSFT]" <bertan@.online.microsoft.com> wrote in message
> news:eTzZVB$sFHA.3604@.tk2msftngp13.phx.gbl...
>|||Fantastic :)
I followed the first link and added the SQLXML web service extension, which
cured the problem.
I have now unchecked the 'Allow URL queries'
Thanks for your help
Brian.
"Bertan ARI [MSFT]" <bertan@.online.microsoft.com> wrote in message
news:uQxZ5AFtFHA.2792@.tk2msftngp13.phx.gbl...
>I just remember.
> If you are using IIS6, you should enable some options on iis configuration
> for security reasons:
> http://support.microsoft.com/defaul...rg/faqs.aspx?79
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Lost and Confused" <lostand@.nowhere.com> wrote in message
> news:431f5d05$0$17950$db0fefd9@.news.zen.co.uk...
>
Apologies if this is in the wrong newsgroup...
I have followed the tutorial in Books Online that is called "Creating the
nwind Virtual Directory". Everything looks ok but I get an error that
says: -
"HTTP Error 404 - File or directory not found. Internet Information Services
(IIS) when I try to test it using http://<IISServer>/nwind?sql=SELECT * FROM
Employees FOR XML AUTO&root=root
I have looked all over trying to find out what I have done wrong :(
Is there anything else that needs to be set up?
Thanks in advance
Brian.Could you please disable "Show friendly HTTP error messages" option in
Advanced tab of IE Options? This should give you more precise error
messages.
In the mean time, please ensure that you enable sql queries on the virtual
directory settings.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lost and Confused" <lostand
@.nowhere.com> wrote in messagenews:431ef381$0$21190$db0fefd9@.news.zen.co.uk...
> Hello,
> Apologies if this is in the wrong newsgroup...
> I have followed the tutorial in Books Online that is called "Creating the
> nwind Virtual Directory". Everything looks ok but I get an error that
> says: -
> "HTTP Error 404 - File or directory not found. Internet Information
> Services (IIS) when I try to test it using
> http://<IISServer>/nwind?sql=SELECT * FROM Employees FOR XML
> AUTO&root=root
> I have looked all over trying to find out what I have done wrong :(
> Is there anything else that needs to be set up?
> Thanks in advance
> Brian.
>|||Hi,
Thanks for replying, I was beginning to despair ;)
I have disabled the friendly errors as you suggested but the 404 page still
doesn't show any more detail than before.
The sql queries were already enabled on the 'nwind
Properties/Settings/Options' tab
Do you have any other suggestions?
Thanks
Brian.
"Bertan ARI [MSFT]" <bertan@.online.microsoft.com> wrote in message
news:eTzZVB$sFHA.3604@.tk2msftngp13.phx.gbl...
> Could you please disable "Show friendly HTTP error messages" option in
> Advanced tab of IE Options? This should give you more precise error
> messages.
> In the mean time, please ensure that you enable sql queries on the virtual
> directory settings.
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Lost and Confused" <lostand
@.nowhere.com> wrote in message> news:431ef381$0$21190$db0fefd9@.news.zen.co.uk...
>|||I am sorry but that's all I could say. It is strange that you cannot see
more detailed error message instead of 404.
Can you access the web server for different directories and pages?
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lost and Confused" <lostand
@.nowhere.com> wrote in messagenews:431f5d05$0$17950$db0fefd9@.news.zen.co.uk...
> Hi,
> Thanks for replying, I was beginning to despair ;)
> I have disabled the friendly errors as you suggested but the 404 page
> still doesn't show any more detail than before.
> The sql queries were already enabled on the 'nwind
> Properties/Settings/Options' tab
> Do you have any other suggestions?
> Thanks
> Brian.
>
> "Bertan ARI [MSFT]" <bertan@.online.microsoft.com> wrote in message
> news:eTzZVB$sFHA.3604@.tk2msftngp13.phx.gbl...
>|||I just remember.
If you are using IIS6, you should enable some options on iis configuration
for security reasons:
http://support.microsoft.com/defaul...4&Product=sql2k
If this doesn't work, follow the steps given in this article:
http://sqlxml.org/faqs.aspx?79
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lost and Confused" <lostand
@.nowhere.com> wrote in messagenews:431f5d05$0$17950$db0fefd9@.news.zen.co.uk...
> Hi,
> Thanks for replying, I was beginning to despair ;)
> I have disabled the friendly errors as you suggested but the 404 page
> still doesn't show any more detail than before.
> The sql queries were already enabled on the 'nwind
> Properties/Settings/Options' tab
> Do you have any other suggestions?
> Thanks
> Brian.
>
> "Bertan ARI [MSFT]" <bertan@.online.microsoft.com> wrote in message
> news:eTzZVB$sFHA.3604@.tk2msftngp13.phx.gbl...
>|||Fantastic :)
I followed the first link and added the SQLXML web service extension, which
cured the problem.
I have now unchecked the 'Allow URL queries'
Thanks for your help
Brian.
"Bertan ARI [MSFT]" <bertan@.online.microsoft.com> wrote in message
news:uQxZ5AFtFHA.2792@.tk2msftngp13.phx.gbl...
>I just remember.
> If you are using IIS6, you should enable some options on iis configuration
> for security reasons:
> http://support.microsoft.com/defaul...rg/faqs.aspx?79
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Lost and Confused" <lostand
@.nowhere.com> wrote in message> news:431f5d05$0$17950$db0fefd9@.news.zen.co.uk...
>
Accessing SQL via http
Hello,
Apologies if this is in the wrong newsgroup...
I have followed the tutorial in Books Online that is called "Creating the
nwind Virtual Directory". Everything looks ok but I get an error that
says: -
"HTTP Error 404 - File or directory not found. Internet Information Services
(IIS) when I try to test it using http://<IISServer>/nwind?sql=SELECT * FROM
Employees FOR XML AUTO&root=root
I have looked all over trying to find out what I have done wrong
Is there anything else that needs to be set up?
Thanks in advance
Brian.
Could you please disable "Show friendly HTTP error messages" option in
Advanced tab of IE Options? This should give you more precise error
messages.
In the mean time, please ensure that you enable sql queries on the virtual
directory settings.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lost and Confused" <lostandconfused@.nowhere.com> wrote in message
news:431ef381$0$21190$db0fefd9@.news.zen.co.uk...
> Hello,
> Apologies if this is in the wrong newsgroup...
> I have followed the tutorial in Books Online that is called "Creating the
> nwind Virtual Directory". Everything looks ok but I get an error that
> says: -
> "HTTP Error 404 - File or directory not found. Internet Information
> Services (IIS) when I try to test it using
> http://<IISServer>/nwind?sql=SELECT * FROM Employees FOR XML
> AUTO&root=root
> I have looked all over trying to find out what I have done wrong
> Is there anything else that needs to be set up?
> Thanks in advance
> Brian.
>
|||Hi,
Thanks for replying, I was beginning to despair ;)
I have disabled the friendly errors as you suggested but the 404 page still
doesn't show any more detail than before.
The sql queries were already enabled on the 'nwind
Properties/Settings/Options' tab
Do you have any other suggestions?
Thanks
Brian.
"Bertan ARI [MSFT]" <bertan@.online.microsoft.com> wrote in message
news:eTzZVB$sFHA.3604@.tk2msftngp13.phx.gbl...
> Could you please disable "Show friendly HTTP error messages" option in
> Advanced tab of IE Options? This should give you more precise error
> messages.
> In the mean time, please ensure that you enable sql queries on the virtual
> directory settings.
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Lost and Confused" <lostandconfused@.nowhere.com> wrote in message
> news:431ef381$0$21190$db0fefd9@.news.zen.co.uk...
>
|||I am sorry but that's all I could say. It is strange that you cannot see
more detailed error message instead of 404.
Can you access the web server for different directories and pages?
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lost and Confused" <lostandconfused@.nowhere.com> wrote in message
news:431f5d05$0$17950$db0fefd9@.news.zen.co.uk...
> Hi,
> Thanks for replying, I was beginning to despair ;)
> I have disabled the friendly errors as you suggested but the 404 page
> still doesn't show any more detail than before.
> The sql queries were already enabled on the 'nwind
> Properties/Settings/Options' tab
> Do you have any other suggestions?
> Thanks
> Brian.
>
> "Bertan ARI [MSFT]" <bertan@.online.microsoft.com> wrote in message
> news:eTzZVB$sFHA.3604@.tk2msftngp13.phx.gbl...
>
|||I just remember.
If you are using IIS6, you should enable some options on iis configuration
for security reasons:
http://support.microsoft.com/default...&Product=sql2k
If this doesn't work, follow the steps given in this article:
http://sqlxml.org/faqs.aspx?79
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lost and Confused" <lostandconfused@.nowhere.com> wrote in message
news:431f5d05$0$17950$db0fefd9@.news.zen.co.uk...
> Hi,
> Thanks for replying, I was beginning to despair ;)
> I have disabled the friendly errors as you suggested but the 404 page
> still doesn't show any more detail than before.
> The sql queries were already enabled on the 'nwind
> Properties/Settings/Options' tab
> Do you have any other suggestions?
> Thanks
> Brian.
>
> "Bertan ARI [MSFT]" <bertan@.online.microsoft.com> wrote in message
> news:eTzZVB$sFHA.3604@.tk2msftngp13.phx.gbl...
>
|||Fantastic
I followed the first link and added the SQLXML web service extension, which
cured the problem.
I have now unchecked the 'Allow URL queries'
Thanks for your help
Brian.
"Bertan ARI [MSFT]" <bertan@.online.microsoft.com> wrote in message
news:uQxZ5AFtFHA.2792@.tk2msftngp13.phx.gbl...
>I just remember.
> If you are using IIS6, you should enable some options on iis configuration
> for security reasons:
> http://support.microsoft.com/default...&Product=sql2k
> If this doesn't work, follow the steps given in this article:
> http://sqlxml.org/faqs.aspx?79
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Lost and Confused" <lostandconfused@.nowhere.com> wrote in message
> news:431f5d05$0$17950$db0fefd9@.news.zen.co.uk...
>
Apologies if this is in the wrong newsgroup...
I have followed the tutorial in Books Online that is called "Creating the
nwind Virtual Directory". Everything looks ok but I get an error that
says: -
"HTTP Error 404 - File or directory not found. Internet Information Services
(IIS) when I try to test it using http://<IISServer>/nwind?sql=SELECT * FROM
Employees FOR XML AUTO&root=root
I have looked all over trying to find out what I have done wrong
Is there anything else that needs to be set up?
Thanks in advance
Brian.
Could you please disable "Show friendly HTTP error messages" option in
Advanced tab of IE Options? This should give you more precise error
messages.
In the mean time, please ensure that you enable sql queries on the virtual
directory settings.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lost and Confused" <lostandconfused@.nowhere.com> wrote in message
news:431ef381$0$21190$db0fefd9@.news.zen.co.uk...
> Hello,
> Apologies if this is in the wrong newsgroup...
> I have followed the tutorial in Books Online that is called "Creating the
> nwind Virtual Directory". Everything looks ok but I get an error that
> says: -
> "HTTP Error 404 - File or directory not found. Internet Information
> Services (IIS) when I try to test it using
> http://<IISServer>/nwind?sql=SELECT * FROM Employees FOR XML
> AUTO&root=root
> I have looked all over trying to find out what I have done wrong
> Is there anything else that needs to be set up?
> Thanks in advance
> Brian.
>
|||Hi,
Thanks for replying, I was beginning to despair ;)
I have disabled the friendly errors as you suggested but the 404 page still
doesn't show any more detail than before.
The sql queries were already enabled on the 'nwind
Properties/Settings/Options' tab
Do you have any other suggestions?
Thanks
Brian.
"Bertan ARI [MSFT]" <bertan@.online.microsoft.com> wrote in message
news:eTzZVB$sFHA.3604@.tk2msftngp13.phx.gbl...
> Could you please disable "Show friendly HTTP error messages" option in
> Advanced tab of IE Options? This should give you more precise error
> messages.
> In the mean time, please ensure that you enable sql queries on the virtual
> directory settings.
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Lost and Confused" <lostandconfused@.nowhere.com> wrote in message
> news:431ef381$0$21190$db0fefd9@.news.zen.co.uk...
>
|||I am sorry but that's all I could say. It is strange that you cannot see
more detailed error message instead of 404.
Can you access the web server for different directories and pages?
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lost and Confused" <lostandconfused@.nowhere.com> wrote in message
news:431f5d05$0$17950$db0fefd9@.news.zen.co.uk...
> Hi,
> Thanks for replying, I was beginning to despair ;)
> I have disabled the friendly errors as you suggested but the 404 page
> still doesn't show any more detail than before.
> The sql queries were already enabled on the 'nwind
> Properties/Settings/Options' tab
> Do you have any other suggestions?
> Thanks
> Brian.
>
> "Bertan ARI [MSFT]" <bertan@.online.microsoft.com> wrote in message
> news:eTzZVB$sFHA.3604@.tk2msftngp13.phx.gbl...
>
|||I just remember.
If you are using IIS6, you should enable some options on iis configuration
for security reasons:
http://support.microsoft.com/default...&Product=sql2k
If this doesn't work, follow the steps given in this article:
http://sqlxml.org/faqs.aspx?79
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lost and Confused" <lostandconfused@.nowhere.com> wrote in message
news:431f5d05$0$17950$db0fefd9@.news.zen.co.uk...
> Hi,
> Thanks for replying, I was beginning to despair ;)
> I have disabled the friendly errors as you suggested but the 404 page
> still doesn't show any more detail than before.
> The sql queries were already enabled on the 'nwind
> Properties/Settings/Options' tab
> Do you have any other suggestions?
> Thanks
> Brian.
>
> "Bertan ARI [MSFT]" <bertan@.online.microsoft.com> wrote in message
> news:eTzZVB$sFHA.3604@.tk2msftngp13.phx.gbl...
>
|||Fantastic
I followed the first link and added the SQLXML web service extension, which
cured the problem.
I have now unchecked the 'Allow URL queries'
Thanks for your help
Brian.
"Bertan ARI [MSFT]" <bertan@.online.microsoft.com> wrote in message
news:uQxZ5AFtFHA.2792@.tk2msftngp13.phx.gbl...
>I just remember.
> If you are using IIS6, you should enable some options on iis configuration
> for security reasons:
> http://support.microsoft.com/default...&Product=sql2k
> If this doesn't work, follow the steps given in this article:
> http://sqlxml.org/faqs.aspx?79
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Lost and Confused" <lostandconfused@.nowhere.com> wrote in message
> news:431f5d05$0$17950$db0fefd9@.news.zen.co.uk...
>
Accessing SQL Server via TELNET?
Is the protocol for SQL Server available anywhere? In other words, if I
TELNET to my server on port 1433, can I issue raw commands right there like
I could with, say, SMTP or POP3?Hi
No. SQL Server uses to Tabular Data Stream (TDS) to talk. This involves
binary data transmission of data.
99% of TDS is not publicly documented.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jeff Johnson [MVP: VB]" <i.get@.enough.spam> wrote in message
news:OTL2Bx3cFHA.2760@.tk2msftngp13.phx.gbl...
> Is the protocol for SQL Server available anywhere? In other words, if I
> TELNET to my server on port 1433, can I issue raw commands right there
> like I could with, say, SMTP or POP3?
>
TELNET to my server on port 1433, can I issue raw commands right there like
I could with, say, SMTP or POP3?Hi
No. SQL Server uses to Tabular Data Stream (TDS) to talk. This involves
binary data transmission of data.
99% of TDS is not publicly documented.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jeff Johnson [MVP: VB]" <i.get@.enough.spam> wrote in message
news:OTL2Bx3cFHA.2760@.tk2msftngp13.phx.gbl...
> Is the protocol for SQL Server available anywhere? In other words, if I
> TELNET to my server on port 1433, can I issue raw commands right there
> like I could with, say, SMTP or POP3?
>
Accessing SQL Server via TELNET?
Is the protocol for SQL Server available anywhere? In other words, if I
TELNET to my server on port 1433, can I issue raw commands right there like
I could with, say, SMTP or POP3?
Hi
No. SQL Server uses to Tabular Data Stream (TDS) to talk. This involves
binary data transmission of data.
99% of TDS is not publicly documented.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jeff Johnson [MVP: VB]" <i.get@.enough.spam> wrote in message
news:OTL2Bx3cFHA.2760@.tk2msftngp13.phx.gbl...
> Is the protocol for SQL Server available anywhere? In other words, if I
> TELNET to my server on port 1433, can I issue raw commands right there
> like I could with, say, SMTP or POP3?
>
TELNET to my server on port 1433, can I issue raw commands right there like
I could with, say, SMTP or POP3?
Hi
No. SQL Server uses to Tabular Data Stream (TDS) to talk. This involves
binary data transmission of data.
99% of TDS is not publicly documented.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jeff Johnson [MVP: VB]" <i.get@.enough.spam> wrote in message
news:OTL2Bx3cFHA.2760@.tk2msftngp13.phx.gbl...
> Is the protocol for SQL Server available anywhere? In other words, if I
> TELNET to my server on port 1433, can I issue raw commands right there
> like I could with, say, SMTP or POP3?
>
accessing sql server via IP is very slow
Hello,
we got a situation with sql 2005 server that configured to be access with
both name pipes and tcp/ip.
while accessing the server via name pipes is very fast other method ip is
very slow
someone could put a light on this issue
ThanksCan you give some information about your network config?
"Tal Bar-Or" <TalBarOr@.discussions.microsoft.com> wrote in message
news:4584EDEB-61AA-4439-9A5D-D03CBAE4BD33@.microsoft.com...
> Hello,
> we got a situation with sql 2005 server that configured to be access with
> both name pipes and tcp/ip.
> while accessing the server via name pipes is very fast other method ip is
> very slow
> someone could put a light on this issue
> Thanks|||we have standard active directory with dns and wins all connected to switch
with one vlan without any restriction.
"Aaron Bertrand [SQL Server MVP]" wrote:
> Can you give some information about your network config?
>
> "Tal Bar-Or" <TalBarOr@.discussions.microsoft.com> wrote in message
> news:4584EDEB-61AA-4439-9A5D-D03CBAE4BD33@.microsoft.com...
> > Hello,
> >
> > we got a situation with sql 2005 server that configured to be access with
> > both name pipes and tcp/ip.
> > while accessing the server via name pipes is very fast other method ip is
> > very slow
> > someone could put a light on this issue
> > Thanks
>
>|||Have you talked to your network administrator? This could be how the LAN is
configured (e.g. it could be that IPs are routed differently...)
"Tal Bar-Or" <TalBarOr@.discussions.microsoft.com> wrote in message
news:45F5967B-B303-4E44-9F63-8096A9C22006@.microsoft.com...
> we have standard active directory with dns and wins all connected to
> switch
> with one vlan without any restriction.
> "Aaron Bertrand [SQL Server MVP]" wrote:
>> Can you give some information about your network config?
>>
>> "Tal Bar-Or" <TalBarOr@.discussions.microsoft.com> wrote in message
>> news:4584EDEB-61AA-4439-9A5D-D03CBAE4BD33@.microsoft.com...
>> > Hello,
>> >
>> > we got a situation with sql 2005 server that configured to be access
>> > with
>> > both name pipes and tcp/ip.
>> > while accessing the server via name pipes is very fast other method ip
>> > is
>> > very slow
>> > someone could put a light on this issue
>> > Thanks
>>|||Also, is it just SQL traffic? What do ping, tracert, file copy etc. look
like using both connection methods?
And if IP is slow, then why not just use named pipes? I gather that you
want to understand the problem, but personally I would rather spend my time
on software performance issues and leave the hardware stuff to the hardware
folks. :-)|||This is definitely a question for your network admin to address.
You could try using the tracert dos command to see if there is a single
point in the path that is causing the majority of the slowdown.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Tal Bar-Or" <TalBarOr@.discussions.microsoft.com> wrote in message
news:4584EDEB-61AA-4439-9A5D-D03CBAE4BD33@.microsoft.com...
> Hello,
> we got a situation with sql 2005 server that configured to be access with
> both name pipes and tcp/ip.
> while accessing the server via name pipes is very fast other method ip is
> very slow
> someone could put a light on this issue
> Thanks|||Thanks for the answer
could you please point me to an article how difference made by connecting
via name pipes and tcp/ip
thanks
"Aaron Bertrand [SQL Server MVP]" wrote:
> Also, is it just SQL traffic? What do ping, tracert, file copy etc. look
> like using both connection methods?
> And if IP is slow, then why not just use named pipes? I gather that you
> want to understand the problem, but personally I would rather spend my time
> on software performance issues and leave the hardware stuff to the hardware
> folks. :-)
>
>|||Just to be clear, the clients in question are not on the same server?
Quoting the Books On Line:
"It is also important to clarify if you are talking about local pipes
or network pipes. If the server application is running locally on the
computer that is running an instance of SQL Server, the local Named
Pipes protocol is an option. Local named pipes runs in kernel mode and
is very fast."
Roy Harvey
Beacon Falls, CT
On Mon, 28 Apr 2008 04:59:01 -0700, Tal Bar-Or
<TalBarOr@.discussions.microsoft.com> wrote:
>Hello,
>we got a situation with sql 2005 server that configured to be access with
>both name pipes and tcp/ip.
>while accessing the server via name pipes is very fast other method ip is
>very slow
>someone could put a light on this issue
>Thanks|||> could you please point me to an article how difference made by connecting
> via name pipes and tcp/ip
I don't know of one specifically off-hand. Did you try Google?
we got a situation with sql 2005 server that configured to be access with
both name pipes and tcp/ip.
while accessing the server via name pipes is very fast other method ip is
very slow
someone could put a light on this issue
ThanksCan you give some information about your network config?
"Tal Bar-Or" <TalBarOr@.discussions.microsoft.com> wrote in message
news:4584EDEB-61AA-4439-9A5D-D03CBAE4BD33@.microsoft.com...
> Hello,
> we got a situation with sql 2005 server that configured to be access with
> both name pipes and tcp/ip.
> while accessing the server via name pipes is very fast other method ip is
> very slow
> someone could put a light on this issue
> Thanks|||we have standard active directory with dns and wins all connected to switch
with one vlan without any restriction.
"Aaron Bertrand [SQL Server MVP]" wrote:
> Can you give some information about your network config?
>
> "Tal Bar-Or" <TalBarOr@.discussions.microsoft.com> wrote in message
> news:4584EDEB-61AA-4439-9A5D-D03CBAE4BD33@.microsoft.com...
> > Hello,
> >
> > we got a situation with sql 2005 server that configured to be access with
> > both name pipes and tcp/ip.
> > while accessing the server via name pipes is very fast other method ip is
> > very slow
> > someone could put a light on this issue
> > Thanks
>
>|||Have you talked to your network administrator? This could be how the LAN is
configured (e.g. it could be that IPs are routed differently...)
"Tal Bar-Or" <TalBarOr@.discussions.microsoft.com> wrote in message
news:45F5967B-B303-4E44-9F63-8096A9C22006@.microsoft.com...
> we have standard active directory with dns and wins all connected to
> switch
> with one vlan without any restriction.
> "Aaron Bertrand [SQL Server MVP]" wrote:
>> Can you give some information about your network config?
>>
>> "Tal Bar-Or" <TalBarOr@.discussions.microsoft.com> wrote in message
>> news:4584EDEB-61AA-4439-9A5D-D03CBAE4BD33@.microsoft.com...
>> > Hello,
>> >
>> > we got a situation with sql 2005 server that configured to be access
>> > with
>> > both name pipes and tcp/ip.
>> > while accessing the server via name pipes is very fast other method ip
>> > is
>> > very slow
>> > someone could put a light on this issue
>> > Thanks
>>|||Also, is it just SQL traffic? What do ping, tracert, file copy etc. look
like using both connection methods?
And if IP is slow, then why not just use named pipes? I gather that you
want to understand the problem, but personally I would rather spend my time
on software performance issues and leave the hardware stuff to the hardware
folks. :-)|||This is definitely a question for your network admin to address.
You could try using the tracert dos command to see if there is a single
point in the path that is causing the majority of the slowdown.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Tal Bar-Or" <TalBarOr@.discussions.microsoft.com> wrote in message
news:4584EDEB-61AA-4439-9A5D-D03CBAE4BD33@.microsoft.com...
> Hello,
> we got a situation with sql 2005 server that configured to be access with
> both name pipes and tcp/ip.
> while accessing the server via name pipes is very fast other method ip is
> very slow
> someone could put a light on this issue
> Thanks|||Thanks for the answer
could you please point me to an article how difference made by connecting
via name pipes and tcp/ip
thanks
"Aaron Bertrand [SQL Server MVP]" wrote:
> Also, is it just SQL traffic? What do ping, tracert, file copy etc. look
> like using both connection methods?
> And if IP is slow, then why not just use named pipes? I gather that you
> want to understand the problem, but personally I would rather spend my time
> on software performance issues and leave the hardware stuff to the hardware
> folks. :-)
>
>|||Just to be clear, the clients in question are not on the same server?
Quoting the Books On Line:
"It is also important to clarify if you are talking about local pipes
or network pipes. If the server application is running locally on the
computer that is running an instance of SQL Server, the local Named
Pipes protocol is an option. Local named pipes runs in kernel mode and
is very fast."
Roy Harvey
Beacon Falls, CT
On Mon, 28 Apr 2008 04:59:01 -0700, Tal Bar-Or
<TalBarOr@.discussions.microsoft.com> wrote:
>Hello,
>we got a situation with sql 2005 server that configured to be access with
>both name pipes and tcp/ip.
>while accessing the server via name pipes is very fast other method ip is
>very slow
>someone could put a light on this issue
>Thanks|||> could you please point me to an article how difference made by connecting
> via name pipes and tcp/ip
I don't know of one specifically off-hand. Did you try Google?
Subscribe to:
Posts (Atom)