I was doing some extensive software testing in a program that talks to a MS-SQL2005 database. I detected that a table INSERT/UPDATE Trigger was seemingly not firing.
I scrutinised the trigger code and could see nothing wrong, and know that it has been functioning well for several years in other applications, using both MSSQL2000 and MSSQL2005.
So what was different in this program? I searched the web and did not find an answer.
I added some code to a test program to work around the issue. That is when I started catching this error message:
Here I was trying to CALL a stored procedure to update some balances. Again, the stored procedure has been working great for many years with no change. So, what was this message all about? Back to the web again and still no resolution.
That is when I dug deeper and added some more tests into a couple of other test programs I wrote, each of which tried different approaches to see what was really happening.
Strangely, some tested OK with no errors, and others threw the same error! So, what was the difference?
The original error it seems is caught by the SQL Native Client ( or perhaps Microsoft Data Access Components (MDAC) ) and translated for the ODBC file drivers I was using. A translation (or miss-translation) is performed and a meaningless message is captured by the ODBC based drivers and passed back to my program as “Executing SQL directly; no cursor”.
Comparing the test programs I was working with pointed me to the answer. It was all to do with permissions! Nothing about “Executing SQL directly; no cursor”.
I found in some programs where I was using Application Role permissions to access the backend SQL, that the Application Role I was using did not have EXECUTE permission on the stored procedure.
Back into MS SQL Server Management Studio I went, and ran this command:
GRANT EXECUTE ON [dbo].[jg_AllBalances11] TO [LMGaus]
Now all my test programs began behaving as expected.
Also, my TRIGGER began working. The trigger was calling the stored procedure to do some of its work, and so now it too performed as expected.
Next time you see the
Executing SQL directly; no cursor
error, check all your permissions!
Hope this helps someone.