I'm not sure if this is the right ng for this question, but here goes...
I just installed a product that uses SQL Express. When I run queries
against the installed instance I get different behavior depending on if the
query is run from Management Studio locally or from SSMS running on a remote
machine. In both cases the user is a domain admin and the query is
identical. The local query returns 1000+ rows, the remote query returns
ZERO. If the query returns less than say 100 rows, there is no difference
between remote and local.
Has anyone else seen this or use use/implemented it themselves?
I'm curious; how this behavior is defined/implemented? Is it database or
instance specific? It seems to be instance-wide in my case but I'm not sure
yet. I've been digging around in SSMS but haven't found any settings or
properties that seem to apply to this, so any sugestions on where to look
would be appreciated.
Thanks!
KeithMy guess would be that there is a problem on the remote machine. Have you
tried a 2nd remote query?
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Keith" <keith@.alh.com> wrote in message
news:%23Ej27KC4HHA.1824@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I'm not sure if this is the right ng for this question, but here goes...
> I just installed a product that uses SQL Express. When I run queries
> against the installed instance I get different behavior depending on if
> the query is run from Management Studio locally or from SSMS running on a
> remote machine. In both cases the user is a domain admin and the query is
> identical. The local query returns 1000+ rows, the remote query returns
> ZERO. If the query returns less than say 100 rows, there is no difference
> between remote and local.
> Has anyone else seen this or use use/implemented it themselves?
> I'm curious; how this behavior is defined/implemented? Is it database or
> instance specific? It seems to be instance-wide in my case but I'm not
> sure yet. I've been digging around in SSMS but haven't found any
> settings or properties that seem to apply to this, so any sugestions on
> where to look would be appreciated.
> Thanks!
> Keith
>|||Yes...a dozen or so actually. And I've tried them on different remote
machines as well with SSMS and SSMSe, in addition to using queries from the
query window and "Open Table/View" from the Object Explorer.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OTMOdYC4HHA.2312@.TK2MSFTNGP06.phx.gbl...
> My guess would be that there is a problem on the remote machine. Have you
> tried a 2nd remote query?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Keith" <keith@.alh.com> wrote in message
> news:%23Ej27KC4HHA.1824@.TK2MSFTNGP04.phx.gbl...
>|||Sounds like larger network packets are getting dropped. Some bridges that
do not do packet splitting can cause this.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Keith" <keith@.alh.com> wrote in message
news:%23Ej27KC4HHA.1824@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I'm not sure if this is the right ng for this question, but here goes...
> I just installed a product that uses SQL Express. When I run queries
> against the installed instance I get different behavior depending on if
> the query is run from Management Studio locally or from SSMS running on a
> remote machine. In both cases the user is a domain admin and the query is
> identical. The local query returns 1000+ rows, the remote query returns
> ZERO. If the query returns less than say 100 rows, there is no difference
> between remote and local.
> Has anyone else seen this or use use/implemented it themselves?
> I'm curious; how this behavior is defined/implemented? Is it database or
> instance specific? It seems to be instance-wide in my case but I'm not
> sure yet. I've been digging around in SSMS but haven't found any
> settings or properties that seem to apply to this, so any sugestions on
> where to look would be appreciated.
> Thanks!
> Keith
>|||OK then I would have to agree with Geoff in that it sounds like an issue
with the servers communications. If you can run this fine locally and all
other machines have issues it kind of narrows it down to that machine
.Check the event logs and see if there are errors on that server.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Keith" <keith@.alh.com> wrote in message
news:e8xPg6C4HHA.5724@.TK2MSFTNGP05.phx.gbl...
> Yes...a dozen or so actually. And I've tried them on different remote
> machines as well with SSMS and SSMSe, in addition to using queries from
> the query window and "Open Table/View" from the Object Explorer.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OTMOdYC4HHA.2312@.TK2MSFTNGP06.phx.gbl...
>|||Geoff N. Hiten (SQLCraftsman@.gmail.com) writes:
> Sounds like larger network packets are getting dropped. Some bridges that
> do not do packet splitting can cause this.
Keith's description reminds me of a very weird error that a formed DSL
provider of mine had. I was mainly reading my after a holiday in SSH2
connection to a Unix account. And that worked fine. But then I got the
idea to look at some web site, but I could not access it. Tried another.
Did not work. A third one. Eventually I tried running lynx from the Unix
account. And that hung too! But I could open a new connection and read my
mail.
Finally I came around to put a very short text file on my web site, and
sure enough, this page did turn up in the browser. So I concluded they
had an error where split packets got lost, but as long as the packets
were small, things worked.
(The reason this provider is a former provider, is simply because I moved
to a new flat, and they could not deliver DSL there.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:erXXwEE4HHA.5980@.TK2MSFTNGP04.phx.gbl...
> Sounds like larger network packets are getting dropped. Some bridges that
> do not do packet splitting can cause this.
I should add that this behavior does not happen running queries against any
other SQL Express, SQL 2005, SQL 2000, or MSDE instance - named or
otherwise, physical or virtual machine - anywhere on my network.
Only this particular instance behaves this way.|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OeY8weE4HHA.3916@.TK2MSFTNGP02.phx.gbl...
> OK then I would have to agree with Geoff in that it sounds like an issue
> with the servers communications. If you can run this fine locally and all
> other machines have issues it kind of narrows it down to that machine
.Unless you install the product on another machine and get the same behavior,
which I have.
> Check the event logs and see if there are errors on that server.
The Event Viewer does not show any errors, nor does the error log in the
instance's data directory.
Considering that this behavior is present along with the installation of
this particular product, and that other named SQL and SQL Express instances
on the same machine do not demonstrate this behavior - even when returning
result sets orders of magnitude larger and on the same physical network -
I'm inclined to believe that this behavior is not machine specific but
rather localized to the named instance installed by this particular product.
Meaning, some configurable property of SQL Server.
I'm totally willing to believe that this is a bug in the product - a
misconfiguration of some kind - but it sounds like most folks don't think
that this type of (mis)configuration is even possible.
Oh, did I mention that this is a Microsoft product's install of SQL Express
;-)
k|||> Unless you install the product on another machine and get the same
> behavior, which I have.
> Considering that this behavior is present along with the installation of
> this particular product, and that other named SQL and SQL Express
> instances on the same machine do not demonstrate this behavior - even when
> returning result sets orders of magnitude larger and on the same physical
> network - I'm inclined to believe that this behavior is not machine
> specific but rather localized to the named instance installed by this
> particular product. Meaning, some configurable property of SQL Server.
Tidbits like these are always good to know. The answers we gave were based
on the info given. I would ping the makers of the product then and see if
they can provide a clue. Other than some ANSI settings at the connection or
database object level that may return different results I know of no
settings or configuration that would produce this behavior. Things like ANSI
PADDING, ANSI NULLS etc can affect how SQL Server treats the data for joins
and the Where clause which can produce different result sets. But none of
that is based on the size of the result set. I would check to see if the
connection settings are the same for both the local and remote SSMS. But
like I said that doesn't explain all the symptoms you mention.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Keith" <keith@.alh.com> wrote in message
news:O%23h0HtF4HHA.484@.TK2MSFTNGP06.phx.gbl...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OeY8weE4HHA.3916@.TK2MSFTNGP02.phx.gbl...
> Unless you install the product on another machine and get the same
> behavior, which I have.
>
> The Event Viewer does not show any errors, nor does the error log in the
> instance's data directory.
> Considering that this behavior is present along with the installation of
> this particular product, and that other named SQL and SQL Express
> instances on the same machine do not demonstrate this behavior - even when
> returning result sets orders of magnitude larger and on the same physical
> network - I'm inclined to believe that this behavior is not machine
> specific but rather localized to the named instance installed by this
> particular product. Meaning, some configurable property of SQL Server.
> I'm totally willing to believe that this is a bug in the product - a
> misconfiguration of some kind - but it sounds like most folks don't think
> that this type of (mis)configuration is even possible.
> Oh, did I mention that this is a Microsoft product's install of SQL
> Express ;-)
> k
>
>|||I discovered this when I was a network admin/dba many years ago. We had a
business network and a process control network that had to be splittable for
political reasons. We had an ethernet bridge set up that we could unplug to
isolate the systems. I was setting up Replication (SQL 6.0/6.5, I think)
between two servers. The both had FDDI interfaces , but there was the
ethernet bridge between them. They negotiated large frame sizes since they
were both on FDDI, not considering that the equipment in the middle couldn't
pass that big a packet. Whatever genius designed the netotiation protocol
didn't bother to test and see if an actual large packet could make it
through. Drove me buggy troubleshooting it. Test queries worked. Ping
never failed. Drives mapped. Then replication would fail to sync. I
eventually found the issue and limited the packet size on the
publisher/distributor and never had another problem.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns998F55155F70Yazorman@.127.0.0.1...
> Geoff N. Hiten (SQLCraftsman@.gmail.com) writes:
> Keith's description reminds me of a very weird error that a formed DSL
> provider of mine had. I was mainly reading my after a holiday in SSH2
> connection to a Unix account. And that worked fine. But then I got the
> idea to look at some web site, but I could not access it. Tried another.
> Did not work. A third one. Eventually I tried running lynx from the Unix
> account. And that hung too! But I could open a new connection and read my
> mail.
> Finally I came around to put a very short text file on my web site, and
> sure enough, this page did turn up in the browser. So I concluded they
> had an error where split packets got lost, but as long as the packets
> were small, things worked.
>
> (The reason this provider is a former provider, is simply because I moved
> to a new flat, and they could not deliver DSL there.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
No comments:
Post a Comment