 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / General / Security / August 2007Changing Query behavior based on local vs. remote context?
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 My guess would be that there is a problem on the remote machine. Have you tried a 2nd remote query?  SignatureAndrew J. Kelly SQL MVP Solid Quality Mentors > Hello, > [quoted text clipped - 19 lines] > > 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. > My guess would be that there is a problem on the remote machine. Have you > tried a 2nd remote query? [quoted text clipped - 22 lines] >> >> 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. SignatureAndrew J. Kelly SQL MVP Solid Quality Mentors > 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 [quoted text clipped - 26 lines] >>> >>> 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:). 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 [quoted text clipped - 3 lines] > 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. SignatureAndrew J. Kelly SQL MVP Solid Quality Mentors >> 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 [quoted text clipped - 24 lines] > > k > Considering that this behavior is present along with the installation of > this particular product, and that other named SQL and SQL Express [quoted text clipped - 4 lines] > this particular product. Meaning, some configurable property of SQL > Server. Does this happens with queries in any database? Do you get zero rows for "SELECT * FROM sys.sysdatabases"? You do connect with the same login locally as you do remotely, don't you? The only configurable property that would matter is the network options. That is, this instance have a poor choice of port, or uses named pipes only for communication (and something goes wrong there). SignatureErland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Sounds like larger network packets are getting dropped. Some bridges that do not do packet splitting can cause this. SignatureGeoff N. Hiten Senior SQL Infrastructure Consultant Microsoft SQL Server MVP > Hello, > [quoted text clipped - 19 lines] > > Keith > 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.) SignatureErland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx 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. SignatureGeoff N. Hiten Senior SQL Infrastructure Consultant Microsoft SQL Server MVP >> Sounds like larger network packets are getting dropped. Some bridges >> that [quoted text clipped - 15 lines] > (The reason this provider is a former provider, is simply because I moved > to a new flat, and they could not deliver DSL there.) > 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. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.