January 4, 2008 at 12:08 pm
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
January 4, 2008 at 12:45 pm
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
January 4, 2008 at 1:02 pm
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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply