Table-Value Paramater Throws an Exception

This forum is only for questions or discussions about working with the mojoPortal source code in Visual Studio, obtaining the source code from the repository, developing custom features, etc. If your question is not along these lines this is not the right forum. Please try to post your question in the appropriate forum.

Please do not post questions about design, CSS, or skinning here. Use the Help With Skins Forum for those questions.

This forum is for discussing mojoPortal development

You can monitor commits to the repository from this page. We also recommend developers to subscribe to email notifications in the developer forum as occasionally important things are announced.

Before posting questions here you might want to review the developer documentation.

Do not post questions about design, CSS, or skinning here. Use the Help With Skins Forum for those questions.

This thread is closed to new posts. You must sign in to post in the forums.

9/14/2010 12:20:39 AM

alkos333

Total Posts 156

View Posts

Table-Value Paramater Throws an Exception

I'm trying to use a table-value parameter and this is the definition I used for it in DefineSqlPamater method of my data layer:

sph.DefineSqlParameter("@DesignationList", SqlDbType.Structured, ParameterDirection.Input, designationList);

Unfortunately, when run a db method with this parameter, I'm getting the following error:

The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Table-valued parameter 30 ("@DesignationList"), row 0, column 0: Data type 0xF3 (user-defined table type) has a non-zero length database name specified. Database name is not allowed with a table-valued parameter, only schema name and type name are valid.

Or error 8047 as defined here http://msdn.microsoft.com/en-us/library/cc645597.aspx

Supposedly, culprit is the TypeName property of SqlPamater class which is set to "mojodev.dbo.DesignationList," but should be set to "dbo.DesignationList" as the error suggests? Indeed, if I set a breakpoint and modify the variable to "dbo.DesignationList," the stored procedure fails, but I"m getting a Guid.Empty, which indicates to me that the data layer function has completed.

Haven't had the time to take a closer look at SqlHelper.cs in order to figure out how to fix this.

Any thoughts, ideas, suggestions?

9/14/2010 1:45:42 PM

alkos333

Total Posts 156

View Posts

Re: Table-Value Paramater Throws an Exception

Alright, I've resolved the issue, but this has to be addressed upstream as anybody who uses SqlDBType.Structured (table-valued parameter) will run into this.

Basically, when using TVP, SqlParamter.TypeName has to be set. Neither SqlParamterHelper.cs nor SqlHelper accommodate this.

I think a good way to fix this would be to overload DefineSqLParamter for a case in which SqlDBType.Structured is used, so that a type name could be passed in and then do a simple check to see if type is Structured and define TypeName which consists of schema name ( ConfigurationManager.AppSettings["MSSQLOwnerPrefix"] could serve this purpose or an additional key could be added) and the type name would just be the newly added parameter from DefineSqlParameter method.

What are you thoughts on this?

I can supply a patch later.

9/15/2010 5:56:46 AM

Joe Audette

Total Posts 18439

View Posts

Re: Table-Value Paramater Throws an Exception

Sure, sounds reasonable, send along the patch or just the modified file, or if the new overloaded method is not large just paste the code here and I'll add it.

Best,

Joe

9/16/2010 11:31:58 AM

alkos333

Total Posts 156

View Posts

Re: Table-Value Paramater Throws an Exception

Just sent you an e-mail with the modified SQLParameterHelper.cs file.

9/17/2010 8:28:47 AM

Joe Audette

Total Posts 18439

View Posts

Re: Table-Value Paramater Throws an Exception

Thanks for the patch!

Best,

Joe

6/22/2011 8:04:42 PM

alkos333

Total Posts 156

View Posts

Re: Table-Value Paramater Throws an Exception

Joe,

I recently ran into odd behavior with my soon to be in production web service where it worked on my local machine, but failed when I deployed it to a testing/production server. The fact that only one method failed on these testing/production servers and the rest remained functional was even more puzzling.

So I had to ask myself - what is special about that one method that keeps failing and there it was - the Table-Value parameter that it utilized.

You included my patch in revision 450, but then there were building issues in Mono since it did not have a .TypeName property, so 454 fixed that.

So now the project file for mojoportal.Data.MSSQL looks like this:

<PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Debug|AnyCPU' ">
    <OutputPath>bin\Debug\</OutputPath>
    <BaseAddress>285212672</BaseAddress>
    <ConfigurationOverrideFile>
    </ConfigurationOverrideFile>
    <DefineConstants>TRACE;DEBUG;MONO</DefineConstants>
    <DocumentationFile>
    </DocumentationFile>
    <DebugSymbols>true</DebugSymbols>
    <FileAlignment>4096</FileAlignment>
    <Optimize>false</Optimize>
    <RegisterForComInterop>false</RegisterForComInterop>
    <RemoveIntegerChecks>false</RemoveIntegerChecks>
    <WarningLevel>4</WarningLevel>
    <DebugType>full</DebugType>
    <ErrorReport>prompt</ErrorReport>
</PropertyGroup>
<PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Release|AnyCPU' ">
    <OutputPath>bin\Release\</OutputPath>
    <BaseAddress>285212672</BaseAddress>
    <ConfigurationOverrideFile>
    </ConfigurationOverrideFile>
    <DefineConstants>TRACE;MONO</DefineConstants>
    <DocumentationFile>..\Documents\xml\mojoPortal.Data.XML</DocumentationFile>
    <FileAlignment>4096</FileAlignment>
    <Optimize>true</Optimize>
    <RegisterForComInterop>false</RegisterForComInterop>
    <RemoveIntegerChecks>false</RemoveIntegerChecks>
    <WarningLevel>4</WarningLevel>
    <DebugType>none</DebugType>
    <ErrorReport>prompt</ErrorReport>
</PropertyGroup>

So if I try to compile for Release or Debug, the MONO constant is always defined in there and, therefore, the support for TypeName property is never compiled into the Data DLL included in official releases for MSSQL. Once I get rid of MONO compiler flag and copy the mojoportal.Data.dll over to my testing/production machines
, everything works.

Am I missing something here?

6/23/2011 12:49:37 PM

Joe Audette

Total Posts 18439

View Posts

Re: Table-Value Paramater Throws an Exception

Hi,

If you send me your updated patch I will accept it as long as it has no side effects for other overloads.

I will remove the MONO compiler directive for that project, I don't ship a package for Mono that is configured for MS SQL and I doubt many if any people use MS SQL with the web site running on Mono.

Best,

Joe

6/23/2011 3:46:56 PM

alkos333

Total Posts 156

View Posts

Re: Table-Value Paramater Throws an Exception

There is no need for an updated patch here though. You have already applied the patch - the MONO constants just have to be removed.

I think there should be another property group for mono to which this #MONO directive would be applied.

You must sign in to post in the forums. This thread is closed to new posts.