Named Pipes vs TCP/IP

Why would one choose Named Pipes over TCP/IP for the client net lib? We have
noticed for some of our customers that changing to named pipes speeds up the
response times of our application. But it is my understanding that a
properly configured LAN should work fine with TCP/IP.
Thanks,
Bob Castleman
SuccessWare Software
It is my understanding that named pipes have slightly less overhead than
TCP/IP but there it is usually not enough to warrant using named pipes. TCP
is much more versatile and should be encouraged in general over named pipes.
One thing to note is that WIN2003 has a 25% higher throuput in TCP over
Win2000 and DNS lookups are up to 120% faster as well. If your clients are
looking for speed improvements and are not on Win2003 they should try it
out. Another thing to note is that usually when clients tell me one
protocol is faster than another I tend to find there is too much chatter
from the client to the server in the first place. The more packets sent
back and forth the more likely you will have performance issues under heavy
load.
Andrew J. Kelly SQL MVP
"Bob Castleman" <nomail@.here> wrote in message
news:OAh7NLOlEHA.324@.TK2MSFTNGP11.phx.gbl...
> Why would one choose Named Pipes over TCP/IP for the client net lib? We
have
> noticed for some of our customers that changing to named pipes speeds up
the
> response times of our application. But it is my understanding that a
> properly configured LAN should work fine with TCP/IP.
> Thanks,
> Bob Castleman
> SuccessWare Software
>
|||A specific example of what we are running into happened this morning. A
customer called complaining of speed problems. Everything was "fine" on
Friday and today it was slow. CPU load on the database server was around 2%.
Disks weren't thrasing, etc. I had him change a workstation from TCP/IP to
Named Pipes and the time it took to load our application went from 20
seconds to 2 seconds. He reported a similar effect on the other
workstations. Nothing I have been able to find about TCP/IP and Named Pipes
would explain this behavior. This seems like some problem in network
communication, especially since the server was basically sitting idle.
I personally don't like "quick fixes". We run into this with just enough
regularity that it would be good to know what's really happening so we can
address the underlying problem.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ed0R4WOlEHA.2892@.tk2msftngp13.phx.gbl...
> It is my understanding that named pipes have slightly less overhead than
> TCP/IP but there it is usually not enough to warrant using named pipes.
> TCP
> is much more versatile and should be encouraged in general over named
> pipes.
> One thing to note is that WIN2003 has a 25% higher throuput in TCP over
> Win2000 and DNS lookups are up to 120% faster as well. If your clients
> are
> looking for speed improvements and are not on Win2003 they should try it
> out. Another thing to note is that usually when clients tell me one
> protocol is faster than another I tend to find there is too much chatter
> from the client to the server in the first place. The more packets sent
> back and forth the more likely you will have performance issues under
> heavy
> load.
> --
> Andrew J. Kelly SQL MVP
>
> "Bob Castleman" <nomail@.here> wrote in message
> news:OAh7NLOlEHA.324@.TK2MSFTNGP11.phx.gbl...
> have
> the
>
|||Simply some facts top start with: Named Pipes is a OSI level 7 (Application)
protocol, WinSock is OSI level 5 (Session), therefore in a TCP only based
network, Named Pipes actually goes over WinSock, so it does not make any
sense that TCP Winsock is slower in that case, which is also not the
experience, and this is why we use TCP to perform client server benchmarks
for SQL Server. When running on the same machine, Named Pipes is faster then
TCP, because it becomes a Local Pipe call (this is where the magic . (dot)
notation is coming from).
Also since Named Pipes are file system objects at kernel level the
scalability compared to WinSock is much less, which is why if you need to
handle large number of connections you always have to use TCP sockets
instead of named pipes. Which each OS release thresholds move, like Andrew
said in Windows Server 2003, TCP sockets and DNS performance increase
dramatically. For example in NT 4.0 you would run out of file handles when
you would have more then 100-150 named pipes connections, this is not longer
a problem with Windows 2000 and Windows Server 2004, but this is to indicate
that each protocol have there own characteristics and scalability
thresholds.
In general the default choice is and should be TCP sockets for all remote
connections, if you are running on the same server like batch process you
might consider named pipes using a local pipe (using the . (dot) notation).
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ed0R4WOlEHA.2892@.tk2msftngp13.phx.gbl...
> It is my understanding that named pipes have slightly less overhead than
> TCP/IP but there it is usually not enough to warrant using named pipes.
> TCP
> is much more versatile and should be encouraged in general over named
> pipes.
> One thing to note is that WIN2003 has a 25% higher throuput in TCP over
> Win2000 and DNS lookups are up to 120% faster as well. If your clients
> are
> looking for speed improvements and are not on Win2003 they should try it
> out. Another thing to note is that usually when clients tell me one
> protocol is faster than another I tend to find there is too much chatter
> from the client to the server in the first place. The more packets sent
> back and forth the more likely you will have performance issues under
> heavy
> load.
> --
> Andrew J. Kelly SQL MVP
>
> "Bob Castleman" <nomail@.here> wrote in message
> news:OAh7NLOlEHA.324@.TK2MSFTNGP11.phx.gbl...
> have
> the
>
|||Anti.virus program trying to AV check the named pipes stuff?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bob Castleman" <nomail@.here> wrote in message news:uZ2a5kOlEHA.3392@.TK2MSFTNGP14.phx.gbl...
>A specific example of what we are running into happened this morning. A customer called complaining
>of speed problems. Everything was "fine" on Friday and today it was slow. CPU load on the database
>server was around 2%. Disks weren't thrasing, etc. I had him change a workstation from TCP/IP to
>Named Pipes and the time it took to load our application went from 20 seconds to 2 seconds. He
>reported a similar effect on the other workstations. Nothing I have been able to find about TCP/IP
>and Named Pipes would explain this behavior. This seems like some problem in network communication,
>especially since the server was basically sitting idle.
> I personally don't like "quick fixes". We run into this with just enough regularity that it would
> be good to know what's really happening so we can address the underlying problem.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ed0R4WOlEHA.2892@.tk2msftngp13.phx.gbl...
>
|||An other option we see often are a DNS name resolution problems, use tracert
to determine the route taken.
An other thought are these notebooks, that connect wireless as well, or at
home? In which case it most of the times helps to dump the DNS resolver
cache of the workstation. See IPCONFIG /flushdns
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e6n8opOlEHA.3372@.TK2MSFTNGP09.phx.gbl...
> Anti.virus program trying to AV check the named pipes stuff?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Bob Castleman" <nomail@.here> wrote in message
> news:uZ2a5kOlEHA.3392@.TK2MSFTNGP14.phx.gbl...
>
|||"Bob Castleman" <nomail@.here> wrote in message
news:OAh7NLOlEHA.324@.TK2MSFTNGP11.phx.gbl...
> Why would one choose Named Pipes over TCP/IP for the client net lib? We
have
> noticed for some of our customers that changing to named pipes speeds up
the
> response times of our application. But it is my understanding that a
> properly configured LAN should work fine with TCP/IP.
Don't know if this is your problem, but there was a bug a while ago in SQL
Server 2000 to do with the introduction of Kerberos aware authentication
using DBLibrary connections over TCP/IP.
IIRC, the SQL Client and SQL Server would not correctly negotiate packet
sizes. This could be circumvented by explicitly overriding the default
packet size in the client app. This was resolved in SQL Server SP2.
Kind Regards, Howard
|||Yes I have seen where issues with DNS have caused situations such as this as
well.
Andrew J. Kelly SQL MVP
"Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
news:e4O$dsOlEHA.3712@.TK2MSFTNGP15.phx.gbl...
> An other option we see often are a DNS name resolution problems, use
tracert
> to determine the route taken.
> An other thought are these notebooks, that connect wireless as well, or at
> home? In which case it most of the times helps to dump the DNS resolver
> cache of the workstation. See IPCONFIG /flushdns
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> You assume all risk for your use.
> Copyright SQLDev.Net 1991-2004 All rights reserved.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in[vbcol=seagreen]
> message news:e6n8opOlEHA.3372@.TK2MSFTNGP09.phx.gbl...
network[vbcol=seagreen]
enough[vbcol=seagreen]
than[vbcol=seagreen]
pipes.[vbcol=seagreen]
over[vbcol=seagreen]
clients[vbcol=seagreen]
it[vbcol=seagreen]
chatter[vbcol=seagreen]
sent[vbcol=seagreen]
We
>
|||Same here.
Basically if the DNS server is unavailable the client will wait for 20-30
seconds, and then resolve the servername in a different way.
You can do a quick check if this is the issue by connecting to the server
via both the IP-address and the servername. IP-address should behave
normally and the servername will have the 30 second delay.
Jacco Schalkwijk
SQL Server MVP
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uD8FsIPlEHA.3356@.TK2MSFTNGP14.phx.gbl...
> Yes I have seen where issues with DNS have caused situations such as this
> as
> well.
> --
> Andrew J. Kelly SQL MVP
>
> "Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
> news:e4O$dsOlEHA.3712@.TK2MSFTNGP15.phx.gbl...
> tracert
> rights.
> in
> network
> enough
> than
> pipes.
> over
> clients
> it
> chatter
> sent
> We
>
|||Thanks,
I'll try that.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:uXQM9FQlEHA.3876@.TK2MSFTNGP15.phx.gbl...
> Same here.
> Basically if the DNS server is unavailable the client will wait for 20-30
> seconds, and then resolve the servername in a different way.
> You can do a quick check if this is the issue by connecting to the server
> via both the IP-address and the servername. IP-address should behave
> normally and the servername will have the 30 second delay.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uD8FsIPlEHA.3356@.TK2MSFTNGP14.phx.gbl...
>

Named Pipes vs TCP/IP

Why would one choose Named Pipes over TCP/IP for the client net lib? We have
noticed for some of our customers that changing to named pipes speeds up the
response times of our application. But it is my understanding that a
properly configured LAN should work fine with TCP/IP.
Thanks,
Bob Castleman
SuccessWare SoftwareIt is my understanding that named pipes have slightly less overhead than
TCP/IP but there it is usually not enough to warrant using named pipes. TCP
is much more versatile and should be encouraged in general over named pipes.
One thing to note is that WIN2003 has a 25% higher throuput in TCP over
Win2000 and DNS lookups are up to 120% faster as well. If your clients are
looking for speed improvements and are not on Win2003 they should try it
out. Another thing to note is that usually when clients tell me one
protocol is faster than another I tend to find there is too much chatter
from the client to the server in the first place. The more packets sent
back and forth the more likely you will have performance issues under heavy
load.
--
Andrew J. Kelly SQL MVP
"Bob Castleman" <nomail@.here> wrote in message
news:OAh7NLOlEHA.324@.TK2MSFTNGP11.phx.gbl...
> Why would one choose Named Pipes over TCP/IP for the client net lib? We
have
> noticed for some of our customers that changing to named pipes speeds up
the
> response times of our application. But it is my understanding that a
> properly configured LAN should work fine with TCP/IP.
> Thanks,
> Bob Castleman
> SuccessWare Software
>|||http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/optimsql/odp_tun_1a_7fcj.asp
Peter
"Status quo, you know, that is Latin for "the mess we're
in."
Ronald Reagan
>--Original Message--
>Why would one choose Named Pipes over TCP/IP for the
client net lib? We have
>noticed for some of our customers that changing to named
pipes speeds up the
>response times of our application. But it is my
understanding that a
>properly configured LAN should work fine with TCP/IP.
>Thanks,
>Bob Castleman
>SuccessWare Software
>
>.
>|||A specific example of what we are running into happened this morning. A
customer called complaining of speed problems. Everything was "fine" on
Friday and today it was slow. CPU load on the database server was around 2%.
Disks weren't thrasing, etc. I had him change a workstation from TCP/IP to
Named Pipes and the time it took to load our application went from 20
seconds to 2 seconds. He reported a similar effect on the other
workstations. Nothing I have been able to find about TCP/IP and Named Pipes
would explain this behavior. This seems like some problem in network
communication, especially since the server was basically sitting idle.
I personally don't like "quick fixes". We run into this with just enough
regularity that it would be good to know what's really happening so we can
address the underlying problem.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ed0R4WOlEHA.2892@.tk2msftngp13.phx.gbl...
> It is my understanding that named pipes have slightly less overhead than
> TCP/IP but there it is usually not enough to warrant using named pipes.
> TCP
> is much more versatile and should be encouraged in general over named
> pipes.
> One thing to note is that WIN2003 has a 25% higher throuput in TCP over
> Win2000 and DNS lookups are up to 120% faster as well. If your clients
> are
> looking for speed improvements and are not on Win2003 they should try it
> out. Another thing to note is that usually when clients tell me one
> protocol is faster than another I tend to find there is too much chatter
> from the client to the server in the first place. The more packets sent
> back and forth the more likely you will have performance issues under
> heavy
> load.
> --
> Andrew J. Kelly SQL MVP
>
> "Bob Castleman" <nomail@.here> wrote in message
> news:OAh7NLOlEHA.324@.TK2MSFTNGP11.phx.gbl...
>> Why would one choose Named Pipes over TCP/IP for the client net lib? We
> have
>> noticed for some of our customers that changing to named pipes speeds up
> the
>> response times of our application. But it is my understanding that a
>> properly configured LAN should work fine with TCP/IP.
>> Thanks,
>> Bob Castleman
>> SuccessWare Software
>>
>|||Simply some facts top start with: Named Pipes is a OSI level 7 (Application)
protocol, WinSock is OSI level 5 (Session), therefore in a TCP only based
network, Named Pipes actually goes over WinSock, so it does not make any
sense that TCP Winsock is slower in that case, which is also not the
experience, and this is why we use TCP to perform client server benchmarks
for SQL Server. When running on the same machine, Named Pipes is faster then
TCP, because it becomes a Local Pipe call (this is where the magic . (dot)
notation is coming from).
Also since Named Pipes are file system objects at kernel level the
scalability compared to WinSock is much less, which is why if you need to
handle large number of connections you always have to use TCP sockets
instead of named pipes. Which each OS release thresholds move, like Andrew
said in Windows Server 2003, TCP sockets and DNS performance increase
dramatically. For example in NT 4.0 you would run out of file handles when
you would have more then 100-150 named pipes connections, this is not longer
a problem with Windows 2000 and Windows Server 2004, but this is to indicate
that each protocol have there own characteristics and scalability
thresholds.
In general the default choice is and should be TCP sockets for all remote
connections, if you are running on the same server like batch process you
might consider named pipes using a local pipe (using the . (dot) notation).
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2004 All rights reserved.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ed0R4WOlEHA.2892@.tk2msftngp13.phx.gbl...
> It is my understanding that named pipes have slightly less overhead than
> TCP/IP but there it is usually not enough to warrant using named pipes.
> TCP
> is much more versatile and should be encouraged in general over named
> pipes.
> One thing to note is that WIN2003 has a 25% higher throuput in TCP over
> Win2000 and DNS lookups are up to 120% faster as well. If your clients
> are
> looking for speed improvements and are not on Win2003 they should try it
> out. Another thing to note is that usually when clients tell me one
> protocol is faster than another I tend to find there is too much chatter
> from the client to the server in the first place. The more packets sent
> back and forth the more likely you will have performance issues under
> heavy
> load.
> --
> Andrew J. Kelly SQL MVP
>
> "Bob Castleman" <nomail@.here> wrote in message
> news:OAh7NLOlEHA.324@.TK2MSFTNGP11.phx.gbl...
>> Why would one choose Named Pipes over TCP/IP for the client net lib? We
> have
>> noticed for some of our customers that changing to named pipes speeds up
> the
>> response times of our application. But it is my understanding that a
>> properly configured LAN should work fine with TCP/IP.
>> Thanks,
>> Bob Castleman
>> SuccessWare Software
>>
>|||Anti.virus program trying to AV check the named pipes stuff?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bob Castleman" <nomail@.here> wrote in message news:uZ2a5kOlEHA.3392@.TK2MSFTNGP14.phx.gbl...
>A specific example of what we are running into happened this morning. A customer called complaining
>of speed problems. Everything was "fine" on Friday and today it was slow. CPU load on the database
>server was around 2%. Disks weren't thrasing, etc. I had him change a workstation from TCP/IP to
>Named Pipes and the time it took to load our application went from 20 seconds to 2 seconds. He
>reported a similar effect on the other workstations. Nothing I have been able to find about TCP/IP
>and Named Pipes would explain this behavior. This seems like some problem in network communication,
>especially since the server was basically sitting idle.
> I personally don't like "quick fixes". We run into this with just enough regularity that it would
> be good to know what's really happening so we can address the underlying problem.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ed0R4WOlEHA.2892@.tk2msftngp13.phx.gbl...
>> It is my understanding that named pipes have slightly less overhead than
>> TCP/IP but there it is usually not enough to warrant using named pipes. TCP
>> is much more versatile and should be encouraged in general over named pipes.
>> One thing to note is that WIN2003 has a 25% higher throuput in TCP over
>> Win2000 and DNS lookups are up to 120% faster as well. If your clients are
>> looking for speed improvements and are not on Win2003 they should try it
>> out. Another thing to note is that usually when clients tell me one
>> protocol is faster than another I tend to find there is too much chatter
>> from the client to the server in the first place. The more packets sent
>> back and forth the more likely you will have performance issues under heavy
>> load.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Bob Castleman" <nomail@.here> wrote in message
>> news:OAh7NLOlEHA.324@.TK2MSFTNGP11.phx.gbl...
>> Why would one choose Named Pipes over TCP/IP for the client net lib? We
>> have
>> noticed for some of our customers that changing to named pipes speeds up
>> the
>> response times of our application. But it is my understanding that a
>> properly configured LAN should work fine with TCP/IP.
>> Thanks,
>> Bob Castleman
>> SuccessWare Software
>>
>>
>|||An other option we see often are a DNS name resolution problems, use tracert
to determine the route taken.
An other thought are these notebooks, that connect wireless as well, or at
home? In which case it most of the times helps to dump the DNS resolver
cache of the workstation. See IPCONFIG /flushdns
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2004 All rights reserved.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e6n8opOlEHA.3372@.TK2MSFTNGP09.phx.gbl...
> Anti.virus program trying to AV check the named pipes stuff?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Bob Castleman" <nomail@.here> wrote in message
> news:uZ2a5kOlEHA.3392@.TK2MSFTNGP14.phx.gbl...
>>A specific example of what we are running into happened this morning. A
>>customer called complaining of speed problems. Everything was "fine" on
>>Friday and today it was slow. CPU load on the database server was around
>>2%. Disks weren't thrasing, etc. I had him change a workstation from
>>TCP/IP to Named Pipes and the time it took to load our application went
>>from 20 seconds to 2 seconds. He reported a similar effect on the other
>>workstations. Nothing I have been able to find about TCP/IP and Named
>>Pipes would explain this behavior. This seems like some problem in network
>>communication, especially since the server was basically sitting idle.
>> I personally don't like "quick fixes". We run into this with just enough
>> regularity that it would be good to know what's really happening so we
>> can address the underlying problem.
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:ed0R4WOlEHA.2892@.tk2msftngp13.phx.gbl...
>> It is my understanding that named pipes have slightly less overhead than
>> TCP/IP but there it is usually not enough to warrant using named pipes.
>> TCP
>> is much more versatile and should be encouraged in general over named
>> pipes.
>> One thing to note is that WIN2003 has a 25% higher throuput in TCP over
>> Win2000 and DNS lookups are up to 120% faster as well. If your clients
>> are
>> looking for speed improvements and are not on Win2003 they should try it
>> out. Another thing to note is that usually when clients tell me one
>> protocol is faster than another I tend to find there is too much chatter
>> from the client to the server in the first place. The more packets sent
>> back and forth the more likely you will have performance issues under
>> heavy
>> load.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Bob Castleman" <nomail@.here> wrote in message
>> news:OAh7NLOlEHA.324@.TK2MSFTNGP11.phx.gbl...
>> Why would one choose Named Pipes over TCP/IP for the client net lib? We
>> have
>> noticed for some of our customers that changing to named pipes speeds
>> up
>> the
>> response times of our application. But it is my understanding that a
>> properly configured LAN should work fine with TCP/IP.
>> Thanks,
>> Bob Castleman
>> SuccessWare Software
>>
>>
>>
>|||"Bob Castleman" <nomail@.here> wrote in message
news:OAh7NLOlEHA.324@.TK2MSFTNGP11.phx.gbl...
> Why would one choose Named Pipes over TCP/IP for the client net lib? We
have
> noticed for some of our customers that changing to named pipes speeds up
the
> response times of our application. But it is my understanding that a
> properly configured LAN should work fine with TCP/IP.
Don't know if this is your problem, but there was a bug a while ago in SQL
Server 2000 to do with the introduction of Kerberos aware authentication
using DBLibrary connections over TCP/IP.
IIRC, the SQL Client and SQL Server would not correctly negotiate packet
sizes. This could be circumvented by explicitly overriding the default
packet size in the client app. This was resolved in SQL Server SP2.
Kind Regards, Howard|||Yes I have seen where issues with DNS have caused situations such as this as
well.
--
Andrew J. Kelly SQL MVP
"Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
news:e4O$dsOlEHA.3712@.TK2MSFTNGP15.phx.gbl...
> An other option we see often are a DNS name resolution problems, use
tracert
> to determine the route taken.
> An other thought are these notebooks, that connect wireless as well, or at
> home? In which case it most of the times helps to dump the DNS resolver
> cache of the workstation. See IPCONFIG /flushdns
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> You assume all risk for your use.
> Copyright © SQLDev.Net 1991-2004 All rights reserved.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:e6n8opOlEHA.3372@.TK2MSFTNGP09.phx.gbl...
> > Anti.virus program trying to AV check the named pipes stuff?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Bob Castleman" <nomail@.here> wrote in message
> > news:uZ2a5kOlEHA.3392@.TK2MSFTNGP14.phx.gbl...
> >>A specific example of what we are running into happened this morning. A
> >>customer called complaining of speed problems. Everything was "fine" on
> >>Friday and today it was slow. CPU load on the database server was around
> >>2%. Disks weren't thrasing, etc. I had him change a workstation from
> >>TCP/IP to Named Pipes and the time it took to load our application went
> >>from 20 seconds to 2 seconds. He reported a similar effect on the other
> >>workstations. Nothing I have been able to find about TCP/IP and Named
> >>Pipes would explain this behavior. This seems like some problem in
network
> >>communication, especially since the server was basically sitting idle.
> >>
> >> I personally don't like "quick fixes". We run into this with just
enough
> >> regularity that it would be good to know what's really happening so we
> >> can address the underlying problem.
> >>
> >> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> >> news:ed0R4WOlEHA.2892@.tk2msftngp13.phx.gbl...
> >> It is my understanding that named pipes have slightly less overhead
than
> >> TCP/IP but there it is usually not enough to warrant using named
pipes.
> >> TCP
> >> is much more versatile and should be encouraged in general over named
> >> pipes.
> >> One thing to note is that WIN2003 has a 25% higher throuput in TCP
over
> >> Win2000 and DNS lookups are up to 120% faster as well. If your
clients
> >> are
> >> looking for speed improvements and are not on Win2003 they should try
it
> >> out. Another thing to note is that usually when clients tell me one
> >> protocol is faster than another I tend to find there is too much
chatter
> >> from the client to the server in the first place. The more packets
sent
> >> back and forth the more likely you will have performance issues under
> >> heavy
> >> load.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Bob Castleman" <nomail@.here> wrote in message
> >> news:OAh7NLOlEHA.324@.TK2MSFTNGP11.phx.gbl...
> >> Why would one choose Named Pipes over TCP/IP for the client net lib?
We
> >> have
> >> noticed for some of our customers that changing to named pipes speeds
> >> up
> >> the
> >> response times of our application. But it is my understanding that a
> >> properly configured LAN should work fine with TCP/IP.
> >>
> >> Thanks,
> >>
> >> Bob Castleman
> >> SuccessWare Software
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
>|||Same here.
Basically if the DNS server is unavailable the client will wait for 20-30
seconds, and then resolve the servername in a different way.
You can do a quick check if this is the issue by connecting to the server
via both the IP-address and the servername. IP-address should behave
normally and the servername will have the 30 second delay.
--
Jacco Schalkwijk
SQL Server MVP
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uD8FsIPlEHA.3356@.TK2MSFTNGP14.phx.gbl...
> Yes I have seen where issues with DNS have caused situations such as this
> as
> well.
> --
> Andrew J. Kelly SQL MVP
>
> "Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
> news:e4O$dsOlEHA.3712@.TK2MSFTNGP15.phx.gbl...
>> An other option we see often are a DNS name resolution problems, use
> tracert
>> to determine the route taken.
>> An other thought are these notebooks, that connect wireless as well, or
>> at
>> home? In which case it most of the times helps to dump the DNS resolver
>> cache of the workstation. See IPCONFIG /flushdns
>> GertD@.SQLDev.Net
>> Please reply only to the newsgroups.
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> You assume all risk for your use.
>> Copyright © SQLDev.Net 1991-2004 All rights reserved.
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
>> message news:e6n8opOlEHA.3372@.TK2MSFTNGP09.phx.gbl...
>> > Anti.virus program trying to AV check the named pipes stuff?
>> >
>> > --
>> > Tibor Karaszi, SQL Server MVP
>> > http://www.karaszi.com/sqlserver/default.asp
>> > http://www.solidqualitylearning.com/
>> >
>> >
>> > "Bob Castleman" <nomail@.here> wrote in message
>> > news:uZ2a5kOlEHA.3392@.TK2MSFTNGP14.phx.gbl...
>> >>A specific example of what we are running into happened this morning. A
>> >>customer called complaining of speed problems. Everything was "fine" on
>> >>Friday and today it was slow. CPU load on the database server was
>> >>around
>> >>2%. Disks weren't thrasing, etc. I had him change a workstation from
>> >>TCP/IP to Named Pipes and the time it took to load our application went
>> >>from 20 seconds to 2 seconds. He reported a similar effect on the other
>> >>workstations. Nothing I have been able to find about TCP/IP and Named
>> >>Pipes would explain this behavior. This seems like some problem in
> network
>> >>communication, especially since the server was basically sitting idle.
>> >>
>> >> I personally don't like "quick fixes". We run into this with just
> enough
>> >> regularity that it would be good to know what's really happening so we
>> >> can address the underlying problem.
>> >>
>> >> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> >> news:ed0R4WOlEHA.2892@.tk2msftngp13.phx.gbl...
>> >> It is my understanding that named pipes have slightly less overhead
> than
>> >> TCP/IP but there it is usually not enough to warrant using named
> pipes.
>> >> TCP
>> >> is much more versatile and should be encouraged in general over named
>> >> pipes.
>> >> One thing to note is that WIN2003 has a 25% higher throuput in TCP
> over
>> >> Win2000 and DNS lookups are up to 120% faster as well. If your
> clients
>> >> are
>> >> looking for speed improvements and are not on Win2003 they should try
> it
>> >> out. Another thing to note is that usually when clients tell me one
>> >> protocol is faster than another I tend to find there is too much
> chatter
>> >> from the client to the server in the first place. The more packets
> sent
>> >> back and forth the more likely you will have performance issues under
>> >> heavy
>> >> load.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "Bob Castleman" <nomail@.here> wrote in message
>> >> news:OAh7NLOlEHA.324@.TK2MSFTNGP11.phx.gbl...
>> >> Why would one choose Named Pipes over TCP/IP for the client net lib?
> We
>> >> have
>> >> noticed for some of our customers that changing to named pipes
>> >> speeds
>> >> up
>> >> the
>> >> response times of our application. But it is my understanding that a
>> >> properly configured LAN should work fine with TCP/IP.
>> >>
>> >> Thanks,
>> >>
>> >> Bob Castleman
>> >> SuccessWare Software
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>>
>|||Thanks,
I'll try that.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:uXQM9FQlEHA.3876@.TK2MSFTNGP15.phx.gbl...
> Same here.
> Basically if the DNS server is unavailable the client will wait for 20-30
> seconds, and then resolve the servername in a different way.
> You can do a quick check if this is the issue by connecting to the server
> via both the IP-address and the servername. IP-address should behave
> normally and the servername will have the 30 second delay.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uD8FsIPlEHA.3356@.TK2MSFTNGP14.phx.gbl...
>> Yes I have seen where issues with DNS have caused situations such as this
>> as
>> well.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
>> news:e4O$dsOlEHA.3712@.TK2MSFTNGP15.phx.gbl...
>> An other option we see often are a DNS name resolution problems, use
>> tracert
>> to determine the route taken.
>> An other thought are these notebooks, that connect wireless as well, or
>> at
>> home? In which case it most of the times helps to dump the DNS resolver
>> cache of the workstation. See IPCONFIG /flushdns
>> GertD@.SQLDev.Net
>> Please reply only to the newsgroups.
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> You assume all risk for your use.
>> Copyright © SQLDev.Net 1991-2004 All rights reserved.
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in
>> message news:e6n8opOlEHA.3372@.TK2MSFTNGP09.phx.gbl...
>> > Anti.virus program trying to AV check the named pipes stuff?
>> >
>> > --
>> > Tibor Karaszi, SQL Server MVP
>> > http://www.karaszi.com/sqlserver/default.asp
>> > http://www.solidqualitylearning.com/
>> >
>> >
>> > "Bob Castleman" <nomail@.here> wrote in message
>> > news:uZ2a5kOlEHA.3392@.TK2MSFTNGP14.phx.gbl...
>> >>A specific example of what we are running into happened this morning.
>> >>A
>> >>customer called complaining of speed problems. Everything was "fine"
>> >>on
>> >>Friday and today it was slow. CPU load on the database server was
>> >>around
>> >>2%. Disks weren't thrasing, etc. I had him change a workstation from
>> >>TCP/IP to Named Pipes and the time it took to load our application
>> >>went
>> >>from 20 seconds to 2 seconds. He reported a similar effect on the
>> >>other
>> >>workstations. Nothing I have been able to find about TCP/IP and Named
>> >>Pipes would explain this behavior. This seems like some problem in
>> network
>> >>communication, especially since the server was basically sitting idle.
>> >>
>> >> I personally don't like "quick fixes". We run into this with just
>> enough
>> >> regularity that it would be good to know what's really happening so
>> >> we
>> >> can address the underlying problem.
>> >>
>> >> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> >> news:ed0R4WOlEHA.2892@.tk2msftngp13.phx.gbl...
>> >> It is my understanding that named pipes have slightly less overhead
>> than
>> >> TCP/IP but there it is usually not enough to warrant using named
>> pipes.
>> >> TCP
>> >> is much more versatile and should be encouraged in general over
>> >> named
>> >> pipes.
>> >> One thing to note is that WIN2003 has a 25% higher throuput in TCP
>> over
>> >> Win2000 and DNS lookups are up to 120% faster as well. If your
>> clients
>> >> are
>> >> looking for speed improvements and are not on Win2003 they should
>> >> try
>> it
>> >> out. Another thing to note is that usually when clients tell me one
>> >> protocol is faster than another I tend to find there is too much
>> chatter
>> >> from the client to the server in the first place. The more packets
>> sent
>> >> back and forth the more likely you will have performance issues
>> >> under
>> >> heavy
>> >> load.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "Bob Castleman" <nomail@.here> wrote in message
>> >> news:OAh7NLOlEHA.324@.TK2MSFTNGP11.phx.gbl...
>> >> Why would one choose Named Pipes over TCP/IP for the client net
>> >> lib?
>> We
>> >> have
>> >> noticed for some of our customers that changing to named pipes
>> >> speeds
>> >> up
>> >> the
>> >> response times of our application. But it is my understanding that
>> >> a
>> >> properly configured LAN should work fine with TCP/IP.
>> >>
>> >> Thanks,
>> >>
>> >> Bob Castleman
>> >> SuccessWare Software
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>>
>>
>|||Bob,
Named-pipes is a "netbios" thing so it uses WINS (and LMHOSTS) to resolve
names.
TCP/IP Sockets uses DNS (and HOSTS).
Hence the reason DNS issues affect tcp-ip net-lib and not named-pipes net-lib.
(Even though both fundamentally run over tcp-ip. You can run named-pipes over
other protocols, but that's extremely rare these days)
Neil Pike MVP/MCSE. Protech Computing Ltd
Reply here - no email
SQL FAQ (484 entries) see
http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
(faqxxx.zip in lib 7)
or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
or www.sqlserverfaq.com
or www.mssqlserver.com/faqsql

Named Pipes Provider, error: 40 - Could not open a connection

Ok,
I've spent a bunch of hours trying to figure out how to connect to SQL
Server 2005. I have MSDE 2005, IIS 6.0 and ASP.NET configured on my laptop.
I can run ASP.net code with no problem. I can access SQL Server from the
command line using osql with no problem.
But trying to access sql server 2005 from asp.net yields the following
message:
System.Data.SqlClient.SqlException: An error has occurred while establishing
a connection to the server. When connecting to SQL Server 2005, this failure
may be caused by the fact that under the default settings SQL Server does not
allow remote connections. (provider: Named Pipes Provider, error: 40 - Could
not open a connection to SQL Server)
Can someone help me with this? I have both tcp/ip and named pipes enabled.
--
ThanksCan you post your connection string?
specifying
Network=DBMSSOCN
in your connection string will cause it to use tcp instead of named pipes,
which is less error prone.
See here for more details:
http://www.aspfaq.com/show.asp?id=2126
Also, check out the post regarding connections to sql express.
http://www.aspfaq.com/sql2005/show.asp?id=3
HTH
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Greg" wrote:
> Ok,
> I've spent a bunch of hours trying to figure out how to connect to SQL
> Server 2005. I have MSDE 2005, IIS 6.0 and ASP.NET configured on my laptop.
> I can run ASP.net code with no problem. I can access SQL Server from the
> command line using osql with no problem.
> But trying to access sql server 2005 from asp.net yields the following
> message:
> System.Data.SqlClient.SqlException: An error has occurred while establishing
> a connection to the server. When connecting to SQL Server 2005, this failure
> may be caused by the fact that under the default settings SQL Server does not
> allow remote connections. (provider: Named Pipes Provider, error: 40 - Could
> not open a connection to SQL Server)
> Can someone help me with this? I have both tcp/ip and named pipes enabled.
> --
> Thanks|||The connections string is:
Cn = new SqlConnection("Network=DBMSSOCN;Data Source=192.168.1.103;Initial
Catalog=sqlexpress;Integrated Security=SSPI");
The new error message I get is:
(provider: TCP Provider, error: 0 - No connection could be made because the
target machine actively refused it.)
The detail is as follows:
System.Data.SqlClient.SqlException: An error has occurred while establishing
a connection to the server. When connecting to SQL Server 2005, this failure
may be caused by the fact that under the default settings SQL Server does not
allow remote connections. (provider: TCP Provider, error: 0 - No connection
could be made because the target machine actively refused it.)
It appears that the connection is blocked but I'm not sure where. I've
checked Windows Firewall and made sure that the appropriate ports are open...
--
Thanks
"Ryan Powers" wrote:
> Can you post your connection string?
> specifying
> Network=DBMSSOCN
> in your connection string will cause it to use tcp instead of named pipes,
> which is less error prone.
> See here for more details:
> http://www.aspfaq.com/show.asp?id=2126
> Also, check out the post regarding connections to sql express.
> http://www.aspfaq.com/sql2005/show.asp?id=3
> HTH
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "Greg" wrote:
> > Ok,
> >
> > I've spent a bunch of hours trying to figure out how to connect to SQL
> > Server 2005. I have MSDE 2005, IIS 6.0 and ASP.NET configured on my laptop.
> > I can run ASP.net code with no problem. I can access SQL Server from the
> > command line using osql with no problem.
> >
> > But trying to access sql server 2005 from asp.net yields the following
> > message:
> >
> > System.Data.SqlClient.SqlException: An error has occurred while establishing
> > a connection to the server. When connecting to SQL Server 2005, this failure
> > may be caused by the fact that under the default settings SQL Server does not
> > allow remote connections. (provider: Named Pipes Provider, error: 40 - Could
> > not open a connection to SQL Server)
> >
> > Can someone help me with this? I have both tcp/ip and named pipes enabled.
> >
> > --
> > Thanks|||Check out this link.
http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx
This is a post that explains exactly how to enable remote connections for
express.
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Greg" wrote:
> The connections string is:
> Cn = new SqlConnection("Network=DBMSSOCN;Data Source=192.168.1.103;Initial
> Catalog=sqlexpress;Integrated Security=SSPI");
> The new error message I get is:
> (provider: TCP Provider, error: 0 - No connection could be made because the
> target machine actively refused it.)
> The detail is as follows:
> System.Data.SqlClient.SqlException: An error has occurred while establishing
> a connection to the server. When connecting to SQL Server 2005, this failure
> may be caused by the fact that under the default settings SQL Server does not
> allow remote connections. (provider: TCP Provider, error: 0 - No connection
> could be made because the target machine actively refused it.)
> It appears that the connection is blocked but I'm not sure where. I've
> checked Windows Firewall and made sure that the appropriate ports are open...
> --
> Thanks
>
> "Ryan Powers" wrote:
> > Can you post your connection string?
> >
> > specifying
> > Network=DBMSSOCN
> > in your connection string will cause it to use tcp instead of named pipes,
> > which is less error prone.
> >
> > See here for more details:
> > http://www.aspfaq.com/show.asp?id=2126
> >
> > Also, check out the post regarding connections to sql express.
> > http://www.aspfaq.com/sql2005/show.asp?id=3
> >
> > HTH
> >
> > --
> > Ryan Powers
> > Clarity Consulting
> > http://www.claritycon.com
> >
> >
> > "Greg" wrote:
> >
> > > Ok,
> > >
> > > I've spent a bunch of hours trying to figure out how to connect to SQL
> > > Server 2005. I have MSDE 2005, IIS 6.0 and ASP.NET configured on my laptop.
> > > I can run ASP.net code with no problem. I can access SQL Server from the
> > > command line using osql with no problem.
> > >
> > > But trying to access sql server 2005 from asp.net yields the following
> > > message:
> > >
> > > System.Data.SqlClient.SqlException: An error has occurred while establishing
> > > a connection to the server. When connecting to SQL Server 2005, this failure
> > > may be caused by the fact that under the default settings SQL Server does not
> > > allow remote connections. (provider: Named Pipes Provider, error: 40 - Could
> > > not open a connection to SQL Server)
> > >
> > > Can someone help me with this? I have both tcp/ip and named pipes enabled.
> > >
> > > --
> > > Thanks|||Excellent! I had to change from dynamic to static TCP port number. It works
great now.
Thanks for all your help
Greg
"Ryan Powers" wrote:
> Check out this link.
> http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx
> This is a post that explains exactly how to enable remote connections for
> express.
>
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "Greg" wrote:
> > The connections string is:
> > Cn = new SqlConnection("Network=DBMSSOCN;Data Source=192.168.1.103;Initial
> > Catalog=sqlexpress;Integrated Security=SSPI");
> >
> > The new error message I get is:
> > (provider: TCP Provider, error: 0 - No connection could be made because the
> > target machine actively refused it.)
> >
> > The detail is as follows:
> > System.Data.SqlClient.SqlException: An error has occurred while establishing
> > a connection to the server. When connecting to SQL Server 2005, this failure
> > may be caused by the fact that under the default settings SQL Server does not
> > allow remote connections. (provider: TCP Provider, error: 0 - No connection
> > could be made because the target machine actively refused it.)
> >
> > It appears that the connection is blocked but I'm not sure where. I've
> > checked Windows Firewall and made sure that the appropriate ports are open...
> >
> > --
> > Thanks
> >
> >
> > "Ryan Powers" wrote:
> >
> > > Can you post your connection string?
> > >
> > > specifying
> > > Network=DBMSSOCN
> > > in your connection string will cause it to use tcp instead of named pipes,
> > > which is less error prone.
> > >
> > > See here for more details:
> > > http://www.aspfaq.com/show.asp?id=2126
> > >
> > > Also, check out the post regarding connections to sql express.
> > > http://www.aspfaq.com/sql2005/show.asp?id=3
> > >
> > > HTH
> > >
> > > --
> > > Ryan Powers
> > > Clarity Consulting
> > > http://www.claritycon.com
> > >
> > >
> > > "Greg" wrote:
> > >
> > > > Ok,
> > > >
> > > > I've spent a bunch of hours trying to figure out how to connect to SQL
> > > > Server 2005. I have MSDE 2005, IIS 6.0 and ASP.NET configured on my laptop.
> > > > I can run ASP.net code with no problem. I can access SQL Server from the
> > > > command line using osql with no problem.
> > > >
> > > > But trying to access sql server 2005 from asp.net yields the following
> > > > message:
> > > >
> > > > System.Data.SqlClient.SqlException: An error has occurred while establishing
> > > > a connection to the server. When connecting to SQL Server 2005, this failure
> > > > may be caused by the fact that under the default settings SQL Server does not
> > > > allow remote connections. (provider: Named Pipes Provider, error: 40 - Could
> > > > not open a connection to SQL Server)
> > > >
> > > > Can someone help me with this? I have both tcp/ip and named pipes enabled.
> > > >
> > > > --
> > > > Thanks

Name property of SqlUserDefinedType

Hi,
MSDN describes that Name property of
Microsoft.SqlServer.Server.SqlUserDefinedType is not used by SQL Server, but
by VS.NET IDE. I have set a name for my UDT, how can I see its effect it
VS.NET IDE?
Thanks in advance,
Leila"Leila" <Leilas@.hotpop.com> wrote in
news:#maMbvsHGHA.2300@.TK2MSFTNGP15.phx.gbl:

> MSDN describes that Name property of
> Microsoft.SqlServer.Server.SqlUserDefinedType is not used by SQL
> Server, but by VS.NET IDE. I have set a name for my UDT, how can I see
> its effect it VS.NET IDE?
>
That statement is a bit misleading. The name property defines what name
the UDT will be created as, in SQL Server, if created through the auto
deployment features in VS.
So, if you have set the Name property and subsequently deployed the type
from within VS, you should see a type in the database with the same name
as the property.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||> MSDN describes that Name property of
> Microsoft.SqlServer.Server.SqlUserDefinedType is not used by SQL Server,
> but
> by VS.NET IDE. I have set a name for my UDT, how can I see its effect it
> VS.NET IDE?
Have you deployed the UDT from IDE? The Name property defines SQL Server
name of the type, which is used by IDE when deploying the type (i.e. when
IDE prepares the CREATE TYPE statement).
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message

n tier ??

Hello all,

Q1:In all asp.net books the authors would tell you to seperate your presentation , business, and data layer but why does the wizards for the sql data source combine the presentation layer with the data layer?

Q2:Is there a way to retreive a dataset after you execut the sqldatasource.insert() command? ......I have a store procedure(sp) that i am using for inserting records. The sp is where i 'm validating all my data (shop standard) for example, ssn length, last name <> 0 etc. If there are any errors i grab all the error code from another table and return it. I can't seem to figure out how to get the dataset...

Q1: Separating the 3 tiers is just the basic rule to keep things simple. In an Enterprise application, each tier will be split in sub tiers, ending in douzans of tiers. In the same way, in a small web app (which is what the wizards are for, not large scale enterprise stuff, not even medium), you still separate everything, but in a much smaller scale:

Dataset: Data layer. Datasource and code behind -> logic. ASPX file -> presentation.

Thats only valid for very small scale things though. A better way is to put your logic and data layer elsewhere, and wrap them in an object datasource. Then you can still use the wizards, while separating stuff for real.

Q2: Yeah its possible, but I'd suggest looking at the Object Datasource. For more complicated logic, it is much better to make a class with your 4 methods that handle all the ADO.NET in code, and then use an object datasource to bind it to your grids, dataviews, etc.

|||thanks for the info|||

cheetoz:

Q1:In all asp.net books the authors would tell you to seperate your presentation , business, and data layer but why does the wizards for the sql data source combine the presentation layer with the data layer?

I had the same experience, but then I found this:

http://www.asp.net/learn/dataaccess/default.aspx?tabid=63

Mysterious Date Problem

Is there a way to either set Sql Server 2000 or ASP.net datetime fields to a standard format. The problem is that I am passing correct datetime fields using stored procedures and keep getting "Cannot convert datetime into string". It seems to me that many other developers are having that same problem. I tried alot of different methods and still have the same problem. I'm using c# and I never had a problem with datetime fields when I was using vb.net. The problem is that SQl Server is returning datetime formats that are not compatible with c#. I have code that works in other projects but when I try to use that same code I get that conversion error. How do I set the datetime in SQl Server and ASP.Net when I run queries so that the datetime output is in mm/dd/yyyy?What is the statement where you get the error "Cannot convert datetime into string"|||well anytime I return a dataset and bind it to a datagrid I get a conversion error. Sometimes I even got 'The conversion of a char data type to a datetime data type resulted in an out-of-range'. When I ran a simple query in Query Analyzer like "select convert (datetime,'12/20/2003',101)" the output returns 2003-12-20 00:00:00.000
What I need is to be mm/dd/yyyy. Is there a way to change the default setting in Sql Server to that particular format instead of yyyy-mm-dd. Because I think C# is using mm-dd-yyyy as a default. In the asp net datagrid all I'm doing it just binding the dataset to the grid and I get that error? Thanks for your reply...|||you should do the formatting at the front end...for instance when you bind the result set to the datagrid...you can use the dataformatstring property of the datagrid columns to format it to mm/dd/yyyy format which is much simpler rather than enforcing the sql server to some other format..

hth|||Yah, I used that format and that is not the problem. The problem is that I can't even bind the dataset to the datagrid. Its in the binding that is causing the error. But thanks anyway.|||What is the code that you are using??|||basically I just call pass parameters to a method and it returns a dataset

datagrid.DataSource = ds;
datagrid.DataBind();

public DataSet GetOriginationReport( DateTime dtStartDate, DateTime dtEndDate)
{
// instantiate connection and command objects
SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["connString2"]);
SqlDataAdapter myCommand = new SqlDataAdapter("rptSelectReport", myConnection);
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;

myCommand.SelectCommand.Parameters.Add("@.pStartDate", SqlDbType.DateTime );
myCommand.SelectCommand.Parameters.Add("@.pEndDate", SqlDbType.DateTime );

myCommand.SelectCommand.Parameters["@.pStartDate"].Value = dtStartDate;
myCommand.SelectCommand.Parameters["@.pEndDate"].Value = dtEndDate;

DataSet myDS = new DataSet();
myCommand.Fill(myDS,"viewReportPipe");
return myDS;
}

===============================
Stored Procedure
===============================
CREATE PROCEDURE rptSelectReport
(
@.pStartDate DateTime,
@.pEndDate DateTime,
)
As

Select * from viewReportPipe Where AppDate Between @.pStartDate And @.pEndDate|||What result do u get when u run this query in Sql analyzer??
Select * from viewReportPipe Where AppDate Between @.pStartDate And @.pEndDate|||well I get a list of results. The problem is occuring in the binding. When I run the query and bind it to a datagrid it works. But I have sorting enable in the datagrid and when I run that same query and bind it to the grid it fails and gives me that "Cannot convert datetime to string" error. The datefield in the table is "AppDate" and is a datetime datatype and is not null. I know Sequel stores dates internally and is different then what is displayed. I have dates 01/01/1900 as default dates. I just discover that sequel uses that same date as a default date. To me its seems like Sequel is switching datetime formats and C# is not recognizing it as a valid datetime. Thanks for you quick response...|||Also should I changed my select statement and convert the date in the stored procedure
like "select AppDate = convert(datetime,AppDate) from ...|||What value does dtStartDate have??|||dtStartDate has 12/01/2003 12:00:00 AM

and the dtEndDate has 12/31/2003 12:00:00 AM|||I am not sure why you get error??
Cannot convert datetime to string

It must be probably your sorting procedure handling some parameter in wrong manner|||its funny because that what I originally though. I was able to sort by a few columns and it works. But some columns would throw that conversion error. I know I am sorting by the right field name but for some reason and I don't why but Sequel is switching datetime formats. That leads me to my original question that Sequel is returning the recordset as yyyy-mm-dd and and is there a way to change it to mm-dd-yyyy. I notice that other developers are experience the same problem and the answer seems to be that SQl Server and asp.net application have to have the same regional setting(datetime). But I haven't found an answer to change the setting in SQL Server or asp.net application. I know how to change the settings on the server but that doesn't help.|||Post your Sorting code...

MySQLAdmin for mssql

Hey,

I am looking for a asp.net web application witch has the same functionality as MySQLAdmin.
Does somebody know where I can get that (for free)?

ThxHi,

Yes, ASP EM to access SQl Server is available in 2 flavours VB.NET/C#

click here http://www.aspenterprisemanager.com/

Find Demo http://www.aspenterprisemanager.com/appcsharp/Connect.aspx

FREE Download http://sourceforge.net/projects/asp-ent-man/

Hope that helps
Murali Kumar|||Are there alternatives for this one?|||hi,

No, if i come across one other such application i ll let u know.

Murali Kumar

MySQL vs MSSQL

Hi, I've been coding in C#/ASP.NET for a little while now, but I've only ever used MS SQL.
I'm starting a new project at work soon, and our host provides free MySQL databases but charges for MS SQL databases.

What are the pros/cons for each of these databases, and how much conversion would be required in an existing C#/ASP.NET application if I wanted to change from MS SQL to MySQL? I assume the syntax/commands to operate a C# app with MS SQL are different to the ones required to communicate with a MySQL database?

I'll probably stick with MS SQL because it's what I know, but I'd like to get familiar with MySQL as it might come in handy one day.
Thanks, any information is greatly appreciated.

http://dev.mysql.com/tech-resources/articles/migrating-from-microsoft.html|||

awesome. thanks mike

MySQL OLEDB Driver

Hi!

I have a small question, there are OLEDB drivers availible for .NET on the MYSQL download site. But when I install them I'm not able to see them inside SQLIS, I've tried them inside Visual Studio and they work fine.

Is there a way to get them "registred" or add them as a reference inside SQLIS so that I may choose them when setting up an OLEDB Source / Destination?For performance reasons, the Integration Services Service caches (as DTS used to do) the list of available components and, among other things, OLE DB Providers, so that the designer doesn't need to waste time gathering those lists from the registry and the file system. I'm just speculating here, but I'm guessing that you might need to do something to refresh that cached list, such as stop and re-start the Service (or reboot). A little-known dialog box in DTS times has a "Refresh Cache" button, but I haven't seen an equivalent button in SSIS.

-Doug
|||Don't think that's the problem, I think the problem is that the driver doesn't get registered in the windows registery. Is there a way to add some keys to the windows registery to manually register the .dll files as OLEDB drivers?|||Hi, have you figured out that one?|||You say OLEDB drivers for .NET but that doesn't make sense to me. OLEDB is not a .NET provider instead it would be ADO.NET. Are the drivers OLEDB or are they .NET. If they are .NET then they wouldn't be available from our OLEDB source but from our DataReaderSource, which uses .NET connections. If they are indeed OLEDB then if they are not showing up then they are not registering themselves correctly. You could try regsvr32 to reregister them but you might have to contact MySQL support and ask them for help.

Thanks,
Matt

MySQL OLEDB Driver

Hi!

I have a small question, there are OLEDB drivers availible for .NET on the MYSQL download site. But when I install them I'm not able to see them inside SQLIS, I've tried them inside Visual Studio and they work fine.

Is there a way to get them "registred" or add them as a reference inside SQLIS so that I may choose them when setting up an OLEDB Source / Destination?For performance reasons, the Integration Services Service caches (as DTS used to do) the list of available components and, among other things, OLE DB Providers, so that the designer doesn't need to waste time gathering those lists from the registry and the file system. I'm just speculating here, but I'm guessing that you might need to do something to refresh that cached list, such as stop and re-start the Service (or reboot). A little-known dialog box in DTS times has a "Refresh Cache" button, but I haven't seen an equivalent button in SSIS.

-Doug
|||Don't think that's the problem, I think the problem is that the driver doesn't get registered in the windows registery. Is there a way to add some keys to the windows registery to manually register the .dll files as OLEDB drivers?|||Hi, have you figured out that one?|||You say OLEDB drivers for .NET but that doesn't make sense to me. OLEDB is not a .NET provider instead it would be ADO.NET. Are the drivers OLEDB or are they .NET. If they are .NET then they wouldn't be available from our OLEDB source but from our DataReaderSource, which uses .NET connections. If they are indeed OLEDB then if they are not showing up then they are not registering themselves correctly. You could try regsvr32 to reregister them but you might have to contact MySQL support and ask them for help.

Thanks,
Matt

My sqlcache doesn't work consistently under ASP.NET 2.0, any suggestion?

I have some problem with ASP.NET cache, I found other people has similar problem, but I didn't find real solution.

The one bother me most is the SQLCacheDependency doesn't work stable. I insert object in cache and has SQLCacheDependency linked. After a period of time, it stopped working. That means the the object is still in cache, but change on db side doesn't remove the cache entry. I am not sure if it is ASP side or SQL side, I feel it is ASP side.

I am using 2.0 + SQL 2005.

Once the db command notification stop working, you have to restart IIS or clear all items in cache since you don't kno which one is changed.

The following is the code I use to handle the cache :

string cacheKey = LinkSites.GetMappedKey(virtualPath, fileid.ToString()); // this will return a key from virtualPath
if (!String.IsNullOrEmpty(cacheKey)) frd = (FileRecordData)HttpContext.Current.Cache[cacheKey];
if (frd == null)
{
int siteid = 0;
SqlCacheDependency scd = null;
lock (_connection)
{
try
{
SqlCommand sqlcmd = new SqlCommand("select ownerid,id,uniqueid,parentid,category,name,content,dated=isnull(updated,created),created,updated,isdirectory from dbo.link_sourcestore where id=@.id", Connection);
sqlcmd.CommandType = CommandType.Text;
SqlParameter sqlparam;
sqlparam = sqlcmd.Parameters.Add("@.id", SqlDbType.Int);
sqlparam.Value = fileid;

scd = new SqlCacheDependency(sqlcmd);
using (SqlDataReader reader = sqlcmd.ExecuteReader())
{
if (!reader.HasRows) return null;
reader.Read();
siteid = LinkRoutine.Convert(reader["ownerid"], 0);
frd = GetRecordData(reader);
}
}
catch (Exception e)
{
ErrorHandler.Report("GetCachedFileRecord 2 [" + realVirtualPath + "," + virtualPath + "]", e);
return null;
}
}

if (scd != null)

{
frd.CacheKey = cacheKey;
frd.CacheDependency = scd;
HttpRuntime.Cache.Insert(cacheKey, frd, scd, Cache.NoAbsoluteExpiration, new TimeSpan(24, 0, 0), CacheItemPriority.NotRemovable, new CacheItemRemovedCallback(LinkCacheHandler.RemovedCallback));
}
}

It just read the record and add into cache, when cache item removed, call the static method RemovedCallback in LinkCacheHandler, LinkCacheHandler is posted below. After I restart IIS, it will work for a while, 5, 10 or more minutes, but after a while, even I set breakpoint in RemovedCallback, I don't get anything when I change the record. (when I call my clear cache method, which will remove all records from cache, it runs to the breakpoint. So the callback is fine)

public class LinkCacheHandler
{
public static void RemovedCallback(string k, object v, CacheItemRemovedReason r)
{
if (!k.Contains("system/cache.ascx"))
{
LinkSites._cacheLog += "RemovedCallback[" + DateTime.Now.ToString() + "]<br/> " + k + ((v is FileRecordData)?(" : " + ((FileRecordData)v).CacheKey) : "") + " " + r.ToString() + "\n<br/>";
LinkSites.NotifyCacheObject(k);
}
}
}

It is me again.

I found a hotfix for this : http://support.microsoft.com/kb/913364/en-us#appliesto

I asked this question if not for a year, I will say at least more than half year. Noone from microsoft response. And today I finally find the hotfix. You bet it is not easy to find it, they seems try to hide it.

It is a big problem! it happens to many users!

My sqlcache doesn't work consistently under ASP.NET 2.0, any suggestion?

I have some problem with ASP.NET cache, I found other people has similar problem, but I didn't find real solution.

The one bother me most is the SQLCacheDependency doesn't work stable. I insert object in cache and has SQLCacheDependency linked. After a period of time, it stopped working. That means the the object is still in cache, but change on db side doesn't remove the cache entry. I am not sure if it is ASP side or SQL side, I feel it is ASP side.

I am using 2.0 + SQL 2005.

Once the db command notification stop working, you have to restart IIS or clear all items in cache since you don't kno which one is changed.

The following is the code I use to handle the cache :

string cacheKey = LinkSites.GetMappedKey(virtualPath, fileid.ToString()); // this will return a key from virtualPath
if (!String.IsNullOrEmpty(cacheKey)) frd = (FileRecordData)HttpContext.Current.Cache[cacheKey];
if (frd == null)
{
int siteid = 0;
SqlCacheDependency scd = null;
lock (_connection)
{
try
{
SqlCommand sqlcmd = new SqlCommand("select ownerid,id,uniqueid,parentid,category,name,content,dated=isnull(updated,created),created,updated,isdirectory from dbo.link_sourcestore where id=@.id", Connection);
sqlcmd.CommandType = CommandType.Text;
SqlParameter sqlparam;
sqlparam = sqlcmd.Parameters.Add("@.id", SqlDbType.Int);
sqlparam.Value = fileid;

scd = new SqlCacheDependency(sqlcmd);
using (SqlDataReader reader = sqlcmd.ExecuteReader())
{
if (!reader.HasRows) return null;
reader.Read();
siteid = LinkRoutine.Convert(reader["ownerid"], 0);
frd = GetRecordData(reader);
}
}
catch (Exception e)
{
ErrorHandler.Report("GetCachedFileRecord 2 [" + realVirtualPath + "," + virtualPath + "]", e);
return null;
}
}

if (scd != null)

{
frd.CacheKey = cacheKey;
frd.CacheDependency = scd;
HttpRuntime.Cache.Insert(cacheKey, frd, scd, Cache.NoAbsoluteExpiration, new TimeSpan(24, 0, 0), CacheItemPriority.NotRemovable, new CacheItemRemovedCallback(LinkCacheHandler.RemovedCallback));
}
}

It just read the record and add into cache, when cache item removed, call the static method RemovedCallback in LinkCacheHandler, LinkCacheHandler is posted below. After I restart IIS, it will work for a while, 5, 10 or more minutes, but after a while, even I set breakpoint in RemovedCallback, I don't get anything when I change the record. (when I call my clear cache method, which will remove all records from cache, it runs to the breakpoint. So the callback is fine)

public class LinkCacheHandler
{
public static void RemovedCallback(string k, object v, CacheItemRemovedReason r)
{
if (!k.Contains("system/cache.ascx"))
{
LinkSites._cacheLog += "RemovedCallback[" + DateTime.Now.ToString() + "]<br/> " + k + ((v is FileRecordData)?(" : " + ((FileRecordData)v).CacheKey) : "") + " " + r.ToString() + "\n<br/>";
LinkSites.NotifyCacheObject(k);
}
}
}

It is me again.

I found a hotfix for this : http://support.microsoft.com/kb/913364/en-us#appliesto

I asked this question if not for a year, I will say at least more than half year. Noone from microsoft response. And today I finally find the hotfix. You bet it is not easy to find it, they seems try to hide it.

It is a big problem! it happens to many users!

My question would have been...

Hi,

I am developing an application using C#.NET that accesses a remote database that that contains japanese characters.

My sample querry is strCommand = "SELECT * FROM table WHERE prodname = 'ＣＲ新海物語スペシャルＭ８'";

The result of querry displayed in the binded datagrid also contains other rows that has this prodname:

ＣＲＦ湯けむり紀行ＳＦ－Ｔ
ＣＲぱちんこハイサイＶＲ１
ＣＲホワイトエンジェルＦＳ

etc.

Thanks in advance for your help.

You forgot to ask a question.
|||

Hi,

I am developing an application using C#.NET that accesses a remote database that that contains japanese characters.

My sample querry is strCommand = "SELECT * FROM table WHERE prodname = 'ＣＲ新海物語スペシャルＭ８'";

The result of the querry above that is displayed in the binded datagrid also contains other rows that has this prodname:

ＣＲＦ湯けむり紀行ＳＦ－Ｔ
ＣＲぱちんこハイサイＶＲ１
ＣＲホワイトエンジェルＦＳ

etc.

Why does the command yields other records where the prodname field contains the 'CR' thing?

Sample querry result in a datagrid:

field1 field2 field3 prodname

a as er ＣＲ新海物語スペシャルＭ８

w aa ww ＣＲＦ湯けむり紀行ＳＦ－Ｔ

ss dd ee ＣＲぱちんこハイサイＶＲ1

sdds ddd ee ＣＲ新海物語スペシャルＭ８

rf d5r wwe ＣＲぱちんこハイサイＶＲ１

Does this strange and redundant output has something to do with the Japanese characters contained in some fields

of the table?

Thanks in advance for your help.

|||Try with the correct nvarchar syntax:

SELECT * FROM table WHERE prodname = N'ＣＲ新海物語スペシャルＭ８'

My Own - Multi Select Parameter Passing Implementation

Hi,
I would like to share how I implemented in my application by passing multi
select parameters into the report.
1. In my .net file, I have created a list box which I load all the multi
values that the user wants to report on(for Ex: Equipment Id = 100,200,300,400 and so on).
2. When the user submits the page, I load the above list into a table.
3. In my stored procedure, I go after this table and build the SELECT
statement.
4. Since the report has to refreshed everytime they add another value into
the list, the report has to know that it has to refresh. For this what I have
done is on the table that i load all the values, I extract the SEQUENCE # and
pass it to the report(it is a unique # everytime when you refresh the page).
U can also pass a Random # but the uniqueness is not guaranteed and the
report may not refresh.
In the Report Designer:
1. I create a parameter called RefreshReport. This value that gets passed
into the parameter is the SEQUENCE value.
I poured my head into 3 books for some clue and couldn't find one hint.
I will be more than happy to answer any questions regarding this
implementation..hi, can you be more detailing? I've been following a guide from a book,
there's this query:
="SELECT * FROM vProductProfitability WHERE (Year = @.Year) AND (Mon
thNumberOfYear = @.Month) AND (Category IN (â'+Parameters!CategoryStrin
g.Value+â'))â'
yes, like what you said, you created a new parameter, in here, the the param
is category string, I dn't know where to put this query since i'm using a
stored procedure.. the only way we can use this query is by using text, so
about your stored procedure that you changed.. what is it?
what i can still select is 1 item, although i'm using checkboxlist, i can
select all of them, but w/ an error of course.
can you please discuss how you manage to enable multi select, since you've
posted a helping memo in here already.. advance thanks!
--
~SiMPLe~
"Suresh" wrote:
> Hi,
> I would like to share how I implemented in my application by passing multi
> select parameters into the report.
> 1. In my .net file, I have created a list box which I load all the multi
> values that the user wants to report on(for Ex: Equipment Id => 100,200,300,400 and so on).
> 2. When the user submits the page, I load the above list into a table.
> 3. In my stored procedure, I go after this table and build the SELECT
> statement.
> 4. Since the report has to refreshed everytime they add another value into
> the list, the report has to know that it has to refresh. For this what I have
> done is on the table that i load all the values, I extract the SEQUENCE # and
> pass it to the report(it is a unique # everytime when you refresh the page).
> U can also pass a Random # but the uniqueness is not guaranteed and the
> report may not refresh.
> In the Report Designer:
> 1. I create a parameter called RefreshReport. This value that gets passed
> into the parameter is the SEQUENCE value.
> I poured my head into 3 books for some clue and couldn't find one hint.
> I will be more than happy to answer any questions regarding this
> implementation..