May 14, 2010 at 3:05 pm
I am having a problem where an Access application executes an adhoc SQL string which generates an error, the problem is that SQL Server 2005 (SP2 CU15) returns a result set with no records as well as the error. In this case Access ignores the error and just presents the empty result set.
Here is some sample code to see what I mean:
CREATE TABLE #Test (D DATETIME NOT NULL);
INSERT INTO #Test VALUES ('01/01/2001');
GO
SELECT * FROM #Test WHERE D > '20/31/2009';
As an example this query only returns an error, without a result set:
SELECT * FROM #Test WHERE D > 20,20;
Can anyone confirm that it does it for them, and it isn't just me?
Would upgrading to SP3 solve the problem? (I doubt it, I just tested that SQL Server 2008 does the same thing.)
Is there anything I can do to prevent SQL Server from returning the invalid results?
May 14, 2010 at 4:36 pm
UMG Developer (5/14/2010)
I am having a problem where an Access application executes an adhoc SQL string which generates an error, the problem is that SQL Server 2005 (SP2 CU15) returns a result set with no records as well as the error. In this case Access ignores the error and just presents the empty result set.Here is some sample code to see what I mean:
CREATE TABLE #Test (D DATETIME NOT NULL);INSERT INTO #Test VALUES ('01/01/2001');
GO
SELECT * FROM #Test WHERE D > '20/31/2009';
As an example this query only returns an error, without a result set:
SELECT * FROM #Test WHERE D > 20,20;
Can anyone confirm that it does it for them, and it isn't just me?
Would upgrading to SP3 solve the problem? (I doubt it, I just tested that SQL Server 2008 does the same thing.)
Is there anything I can do to prevent SQL Server from returning the invalid results?
You last example causes the compliation error, that why the resultset is not return. The conversion error doesn't stop SELECT to produce empty resultset. You better find how to catch the error in Access.
However, you can try something like:
DECLARE @a DATETIME; SET @a=CAST('20/31/2009' AS DATETIME);IF @@ERROR=0 SELECT * FROM #Test WHERE D > '20/31/2009';
I am not sure if Access will allow to execute the above, but this code will not return resultset when there is a conversion problem.
May 14, 2010 at 4:53 pm
What your locales set to in SQL Server.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 14, 2010 at 5:20 pm
I could use your method of casting all of the values, but it would make for some pretty ugly code, both in Access and SQL.
If Access runs the query directly as a pass-through it gets the error, but I am running the code from an SP that logs the query, and error, if any, and re-raises the error if there is one, but access ignores the error in cases where a result set is returned, and I can't come up with any way to make it notice the error.
I have put a work-around in place to report a general error, as I know there should always be at least one record, but I haven't found any way to get the error message, which would be very helpful.
May 14, 2010 at 5:24 pm
CirquedeSQLeil (5/14/2010)
What your locales set to in SQL Server.
Do you mean the collation? If so that is: SQL_Latin1_General_CP1_CI_AS
May 15, 2010 at 5:03 pm
How you call your SP from Access?
From SQL Server prospective, it is expected behaviour. The error is raised, query/sp execution stopped (conversion error will terminate your query or sp), empty recordset (from the last select) returned. It is upto the caller to handle returned errors properly.
Actually, when you reraise the error what severity are you using? May be it is very low?
May 17, 2010 at 10:30 am
elutin (5/15/2010)
How you call your SP from Access?From SQL Server prospective, it is expected behaviour. The error is raised, query/sp execution stopped (conversion error will terminate your query or sp), empty recordset (from the last select) returned. It is upto the caller to handle returned errors properly.
Actually, when you reraise the error what severity are you using? May be it is very low?
I'm calling the SP via a pass-through query.
The severity is used from the original error, so in this case it is a 16.
May 18, 2010 at 11:26 am
Since I was already logging the errors to a table I created a second SP that retrieves the last error for the user, and then modified the Access application to call that SP if the first SP returns invalid results so that it can properly inform the user of the error. Seems like a bit of a kludge, but it works for now.
If anybody has any better suggestions I am all ears.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply