asked
Simple query returns no results. Works in SSMS
I have the following table: CREATE TABLE "T_Customer" ( "IdCustomer" INT NOT NULL DEFAULT NULL, "NDG" NVARCHAR(50) NOT NULL DEFAULT NULL ) and this (simple!) query (executed from an
ASP.NET, C# application): SELECT [IdCustomer], [NDG] FROM T_Customer WHERE NDG=@myNdg Now, the customer complains that some records (i.e. some "NDG") are **not** found; note that some other values are correctly found on the same table (it seems that the last NDG in the table are the culprits). Looking at the SQL Profiler, I see the following query executed: exec sp_executesql N'SELECT [IdCustomer], [NDG] FROM T_Customer WHERE NDG=@myNdg',N'@myNdg nvarchar(4000)',@myNdg=N'468472' go and trying to execute that query in SSMS (on the customer side), the query returns the expected result. So, my questions: * how can I find out what's wrong in here? * the connection string is using a specific user name; could the customer have used another user in SSMS, and that's the reason why the query returns different results? * should I use dbo.[T_Customer] in my query, instead of T_Customer alone? Connection string: add connectionString="Server=SERVER;Database=DATABASE;User ID=USERID;Password=PASSWORD" name="MyDbConnection" ---------- **Edit** [2015.01.22] The options (SELECT @@OPTIONS) from the database returns 5496, and from the application is returning 5432. The only different flag is ARITHABORT > Terminates a query when an overflow or divide-by-zero error occurs during query execution As far as I can read from this SO answer (SQL Server ARITHABORT), this is the normal behavior of .NET applications > NET applications connect with the option disabled by default, but it's enabled by default in Management Studio So, this should have at least a (negative) impact on performance, but nothing more. Is this right? Then, I tried to query the DB with different options (i.e. with and without the dbo.), with no success. The last point I have in mind to check is the connection, since the application is currently using a SQL server user ID in the connection string, and the tests on SSMS were run with the Windows authentication. I honestly don't know what are the "real" differences in between these...
Taking your questions in reverse order: If your customer is using a user with the default schema set to 'dbo', then using the fully qualified name will make little difference (although it may affect performance a little). However if they have a different schema, and there is a T_Customer table / view in it, then they would be drawing from that other object. It is possible to restrict data to specific users, but this is normally done via a view. I'm not aware of a good way to do this at the table level - other contributors may be able to help you here. The more likely scenario would be if the other user has a different default schema, in which case there may be a filtered view or another table getting in the way. Can you verify the results in the application? Can you check the rowcount received back (both via Extended events / profiler (with the usual caveat on the performance implications on a production server..) ? How many does the application see? could the two be related, if they're not the same (e.g. half the number, which might indicate a double-retrieve bug in the appication)? Can it be demonstrated for other 'NDG's? It rather depends how much access you have to the application code. Good luck with tracking down the problem.
Thank you. Can't understand the "rowcount" point: my query is asking for a *single* NDG, and the application returns an empty result set; the same query, in SSMS, returns the expected record. I already asked the customer to run the query I copied from the profiler on my PC, and the query returns the expected record (in SSMS, again). Other 'NDG's works, up to the last rows in the table... Last: I think I don't have Extended events, in SQLServer 2005
Rowcount - ok, hadn't realised you were looking at a single row, but the same point applies - i.e. a different / changed result on each could result. Extended events - yes, you'll have to work with Profiler on SQL 2005.
15 People are following this question.
