Works in Management Studio but not from C#

Question

Post reply

Works in Management Studio but not from C#

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

LadyReader Ten Centuries Points: 1192 More actions · Answer 1

LadyReader

Ten Centuries

Points: 1192

September 24, 2013 at 3:11 pm

#1652896

Yes, that query returns 1 row.

LadyReader Ten Centuries Points: 1192 More actions · Answer 2

Okay then, that's great. How are you defining your C# parm?

AndrewSQLDBA (9/24/2013)
Have you tried to just run a query:
SELECT
GroupId
, GroupName
, FileTypeName
, SubjectID
FROM
dbo.MyTable
WHERE
SubjectId = 'FBBE20B1-55F1-E111-BF73-00155D062F00'
ORDER BY
FileTypeName ASC
To see if there is a row with the FileTypeName of 'Enrollment' and a SubjectID of 'FBBE20B1-55F1-E111-BF73-00155D062F00' ?
Have you verified that your SQL Connection String C# code is using the database that you think it is using?
Just suggesting things now. I have setup the exact same table and an ASP.NET page using C#, and executing a sproc coded the same as you show, and I get a resultset.
Andrew SQLDBA

LadyReader Ten Centuries Points: 1192 More actions · Answer 3

RBarryYoung (9/24/2013)
You should use the SQL Profiler to see what's really happening on your SQL server when it's called from C#. In all likelihood, there's some unexpected difference at that point.

Thanks for responding but the link you sent seems to be only referring to db change queries (updates and deletes); I am just doing a Select. But I will try that and see what happens.

{sorry - quoted the wrong reply; meant to quote Andrew's NoCOUNT suggestion}

Tried that: no change.

I should mention that the table created as a column count = 2 after the Fill and this is correct. The row count is still 0.

michal.lisinski SSCertifiable Points: 6933 More actions · Answer 4

Hi LadyReader

Could you try to do another experiment and set as hardcoded:

//SqlCommand sqlCommand = new SqlCommand(sSprocName, Con);

SqlCommand sqlCommand = new SqlCommand("exec dbo.GetBySubjectId @SubjectIdString", Con);

Br.

Mike

LadyReader Ten Centuries Points: 1192 More actions · Answer 5

michal.lisinski (9/24/2013)
Hi LadyReader
Could you try to do another experiment and set as hardcoded:
//SqlCommand sqlCommand = new SqlCommand(sSprocName, Con);
SqlCommand sqlCommand = new SqlCommand("exec dbo.GetBySubjectId @SubjectIdString", Con);
Br.
Mike

Mike, thanks for the suggestion. I have not seen that syntax before. Are you sure that's what you intended to suggest?

I tried it and the Fill errored: "Could not find stored procedure 'exec GetBySubjectId @SubjectIdString'."

LadyReader Ten Centuries Points: 1192 More actions · Answer 6

Thanks to all who tried to help.

I figured it out. I didn't mention that I was working in a Dynamics CRM environment (because I didn't think it mattered) but it turned out to be significant.

After trying to rework the procedure and how I was calling it in any number of ways, with no change to the results, last evening I finally figured it out:

The first sproc is executed on a CRM view that is made of a base type and its extension, dbo.xxx.

The 2nd sproc was executed on a CRM Filtered View, dbo.Filteredyyy. This contains foreign keys into view dbo.StringMap (StringMap contains all the Option Set values and their texts). One or more of the tables that make up the design of StringMap does not allow even Select queries outside of the Dynamics environment. Although I didn’t track down which views or tables refuse permission to access itself, I was able to rewrite my sproc to use the unfiltered view, which consists of a base and its extension variables, just like in the first sproc.

This solved the problem.