September 18, 2017 at 4:26 am
HI All,
I have a classic asp site that is connecting to SQL Server 2012 via ODBC and am login in with a specific user. The user has been granted Execute permissions on the Stored Proc. On the DB the user has been given execute, read and write permissions. Using sql server profiler I can see the DB being hit with the stored proc call. If I login to SQL Server Management Studio with the user credentials I can execute the sp from the Profiler (copy and paste) and data is returned in the query results window proving that the user has correct permissions. From the application side if I pass something like 'Select * from mytable' data is returned and I can see it when debugging my application, this also proves that the ODBC settings are correct. I don't understand why SQL Server will not pass the results from the SP back to my calling app. It must be a permissions setting somewhere, but where I don't know. Any help would be greatly appreciated.
September 18, 2017 at 4:42 am
If you are using the EXACT same SQL that is being run by the application (captured in profiler) under the same credentials, and SSMS is returning results and your application isn't, I'd hazard a guess thatt he way that your application is interpreting the results is not as you expect, and thus thinks it is returning none. Otherwise, you're not running the statement in exactly the same environment (I.e. Different User/Login, different T-SQL).
I'm not an expert (at all) on ASP.net, however, when working with PHP, I know that it can have some odd interpretations. For example, in PHP 7, using Microsoft's own PHP dll, it doesn't work well with Multi-part Statement SPs. The dll will interpret every SELECT statement as a return, if it that statement itself doesn't return results.
For example:DECLARE @CurrDate date;
SELECT @CurrDate = GETDATE();
SELECT @CurrDate AS CurrentDate;
GO
In PHP 7, simply returning the first resultset would yield nothing, as the the driver interprets SELECT @CurrDate = GETDATE(); as a result set. You therefore have to cycle to the second resultset in the code. This is an "intended" feature, according to Microsoft.
Not sure if that logic is applicable to PHP.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 18, 2017 at 6:03 am
Welcome to SSC.
Does your procedure return more than one result set? Meaning, if you execute your procedure from SSMS, do you see just one results set in the results pane? It's been years since I worked in classic ASP (not .NET), but I remember something along these lines. I don't remember what Thom described above ever being a problem, with an variable assignment returning a result set, but rather with multiple result sets being a problem.
Also, try firing a SET NOCOUNT ON in your procedure first thing. I know the "N row(s) affected" can interfere with ODBC in some cases by providing output from the procedure that one driver or another may pick up.
Other than that, I'd say the next step is to look at how your ASP code is treating the results of the procedure call.
September 18, 2017 at 6:29 am
I would also suggest the SET NOCOUNT ON, that used to be a staple of my old classic ASP pages; I would bet that is what is affecting you.
you can test the actual permissions of the proc as well in management studio, by testing via EXECUTE AS
EXECUTE AS LOGIN='MyASP_User';
EXECUTE MyProcedure @Parameter1='Whatever';
REVERT --change back to original/superuser
Lowell
September 18, 2017 at 8:10 am
Thom A - Monday, September 18, 2017 4:42 AMIf you are using the EXACT same SQL that is being run by the application (captured in profiler) under the same credentials, and SSMS is returning results and your application isn't, I'd hazard a guess thatt he way that your application is interpreting the results is not as you expect, and thus thinks it is returning none. Otherwise, you're not running the statement in exactly the same environment (I.e. Different User/Login, different T-SQL).I'm not an expert (at all) on ASP.net, however, when working with PHP, I know that it can have some odd interpretations. For example, in PHP 7, using Microsoft's own PHP dll, it doesn't work well with Multi-part Statement SPs. The dll will interpret every SELECT statement as a return, if it that statement itself doesn't return results.
For example:DECLARE @CurrDate date;
SELECT @CurrDate = GETDATE();SELECT @CurrDate AS CurrentDate;
GO
In PHP 7, simply returning the first resultset would yield nothing, as the the driver interprets SELECT @CurrDate = GETDATE(); as a result set. You therefore have to cycle to the second resultset in the code. This is an "intended" feature, according to Microsoft.Not sure if that logic is applicable to PHP.
It only Returns 1 resultset.
September 18, 2017 at 8:29 am
Do you have a SET NOCOUNT ON in your stored procedure like Lowell and I suggested? I know it can interfere with some ODBC calls. It won't interfere with @@ROWCOUNT (if you have them) in your procedure, just suppress the "N row(s) affected" messages.
September 18, 2017 at 8:57 am
The error centered around dates. In the SP I have dateformat set to DMY. The DB language is set to British English. In the ODBC Connection the Language for SQLSErver Messages was also set to British English. What I Didn't have set was Use regional settings when outputting currency,numbers, dates and times in the ODBC Driver, so when returning with the resultset, it was reverting to default settings thus not mapping datetimes correctly and killing the resultset.
September 18, 2017 at 9:06 am
Glad you got it figured out. Thanks for posting back.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply