missing column in my sproc call

  • Thread starter Thread starter Jose
  • Start date Start date
J

Jose

For some wierd reason, using the sproc
(GetParametersWithUserDomainName..shown below) with the
C# code below gives me a resultset **without the
ParameterDefault column**. However, when i execute this
sproc in QueryAnalyzer, or "my easy SQL tool (from
msdn)", it shows *all* columns as expected.

At first, i couldnt find the column, so i dumped it to
XML to take a look at it. The column isnt there!!! Why?


DataSet defaults = FetchDefaultsDataSet();
XmlDataDocument datadoc = new XmlDataDocument(defaults);

private DataSet FetchDefaultsDataSet()
{
DataSet ds = new DataSet();
SqlConnection conn = new SqlConnection
(ConfigurationSettings.AppSettings
["Mercury.DatabaseConnectionString"]);
SqlCommand cmd = new SqlCommand
("GetParametersWithUserDomainName", conn);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter paramUserName = new SqlParameter
( "@UserDomainName", _winIdentityName);
paramUserName.SqlDbType = (SqlDbType) Enum.Parse(
typeof(SqlDbType), "varchar", true);
paramUserName.Direction = ParameterDirection.Input;
cmd.Parameters.Add( paramUserName);
adapter.Fill(ds,"Results");
return ds;
}

CREATE PROCEDURE GetParametersWithUserDomainName
(
@UserDomainName varchar(50)
)

AS

SELECT
pc.ParameterName,
pc.ParameterType,
pc.ControlMode,
pc.Delimiter,
pc.Constraint1,
pc.Constraint2,
pd.ParameterDefault,
ct.ControlTypeDescription
FROM
ParameterConfiguration pc
INNER JOIN ControlType ct
ON pc.ControlTypeID = ct.ControlTypeID
LEFT JOIN ParameterDefault pd
ON pc.ParameterID = pd.ParameterID
AND pd.UserDomainName = RTRIM(@UserDomainName)
GO

<Results>
<ParameterName>region</ParameterName>
<ParameterType>mdx</ParameterType>
<ControlMode>multiselect</ControlMode>
<Delimiter>,</Delimiter>
<Constraint1>fff</Constraint1>
<Constraint2>ddd</Constraint2>
<ControlTypeDescription>hierarchicaltree</ControlTypeDescr
iption>
</Results>
<Results>
<ParameterName>begindate</ParameterName>
<ParameterType>standard</ParameterType>
<ControlMode>date</ControlMode>
<Delimiter></Delimiter>
<Constraint1>11/1/2002</Constraint1>
<Constraint2>11/30/2003</Constraint2>
<ControlTypeDescription>calendar</ControlTypeDescription>
</Results>


(results from Query Analyzer....the [Contact]... is the
data from the missing column)

region
mdx
multiselect
,
fff
ddd
[Contact].[Region].[All ContactRegion].[The Americas].
[North America].[United States]
hierarchicaltree

begindate
standard
date
""
11/1/2002
11/30/2003
3/3/2003
calendar


CREATE TABLE dbo.ParameterDefault
(
ParameterID [int] NOT NULL,
UserDomainName [varchar] (50) NOT NULL,
ParameterDefault [varchar] (250)
)
 
One more piece of information. I dumped the schema of
the result set (below).

I cant figure out why SQL query analyzer shows all the
columns below with data, but when i execute it in C# (in
top level post), this column and it's data are missing.

Any ideas?


<xs:sequence>
<xs:element name=\"ParameterName\" type=\"xs:string\"
minOccurs=\"0\" />
<xs:element name=\"ParameterType\" type=\"xs:string\"
minOccurs=\"0\" />
<xs:element name=\"ControlMode\" type=\"xs:string\"
minOccurs=\"0\" />
<xs:element name=\"Delimiter\" type=\"xs:string\"
minOccurs=\"0\" />
<xs:element name=\"Constraint1\" type=\"xs:string\"
minOccurs=\"0\" />
<xs:element name=\"Constraint2\" type=\"xs:string\"
minOccurs=\"0\" />
<xs:element name=\"ParameterDefault\"
type=\"xs:string\" minOccurs=\"0\" />
<xs:element name=\"ControlTypeDescription\"
type=\"xs:string\" minOccurs=\"0\" />
</xs:sequence>
 
Jose,

I assume the contents of this field is null. Nulls are not represented in
XML, the lack of data means null.

Repost if that isn't the problem.
 
Back
Top