DB Permissions in VB.NET
-
Hey Guys,
I'm writing a module to return information from a look up table I've created in my database. I'm trying to get the value of one cell that contains a percentage. I've written a select statement that returns just the value from the table I'm looking for and puts it in a temp Data Table and my code should pull that and store it as a variable. Everything works fine, or should, I think, and my function deploys to my DB just fine but I keep getting this error, I know it has to do with permissions but I can't figure out why I'm getting it. I have the SQLFunction properties set with both DataAccess:=DataAccessKind.Read as well as the SystemDataAccessKind.Read... still I'm getting:
"InvalidOperationException: Data Access is not allowed in this context. Either the context is a function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain data from FillRow method of a Table Valued Function, or is a UDT Validation Method."
I figure this has something to do with the way I'm setting the permissions... or the connection itself (I'm using "Context Connection = True")... I don't use FillRow (I do use the DataTable method .Fill() but I'm not sure this would be the issue..) so I don't think that part of the message applies to me.. and the last I know doesn't.
I tried to write this as concisely as possible, If you think you could help and need any extra info (code, etc) I can happily supply it.
Thanks for your attention,
M. Hovde
-
I don't have the answer to your question, but maybe you've choosen the wrong object to server your data need.
Why did you choose a clr-object ?
Why did you choose a sqlfunction and not a sqlproc ?
In most cases when just performing regular TSQL stuff, a non-clr function or procedure will outperform a clr equivalent !!
Create a simple tsql procedure or function and test it !
If you just want one row with one value returned from your object, don't use a dataset/datatable !
Execute your sqlcommandobject with the option .(cannot recall right now).. so it doesn't create the memoryneeds to receive the returned rowset.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I think you're talking about the SendAndExecute method... as part of the SQLCommand obj,
I realize the performance differences you are talking about, however I'm sort of creating a package that is all done through the CLR, and does not really rely on code written in T-SQL
I tried that originally honestly and it gave me an error saying the method didn't return a value when i tried to assign it to a variable...though it did give me the value I wanted when I ran the function as a stored proc, it gave me the right results in SS2K8 but If I can't use the value returned as a variable it's worthless to me. It's possible I just wasn't doing the right thing to get this value assigned, but I need to be able to use the % to manipulate other data, not just see the number as the select statement returns it.
Any more advice is greatly appreciated,
Thanks Again,
M. Hovde
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply