.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Post New Web Links

Problem with accessing User defined types.

Posted By:      Posted Date: May 22, 2011    Points: 0   Category :


I have a User defined type with .net CLR and I am trying to use that in sql server 2005.

Following is the class which I have created for User Defined Type.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public class TxnRecObj : INullable,IBinarySerialize
  private int _InOutCode;
  private string _InOutData;
  private int _OraCode;
  private string _OraError;
  private string _ErrorData;

  private string _TRANS_CODE;
  private string _DA

View Complete Post

More Related Resource Links

Defaults and User Defined Types Tabs in SSMS

Hello, Just to make sure I was using all the available tools for the right purpose, I have been going through all the tabs I haven't used in SSMS. I've managed to research what they all mean but two have me a little stumped. These two tabs are: [Database]->Programmability->Defaults Quite hard to search for help on as the word default is used to explain a lot of different things! [Database]->Programmability->Types->User-Defined Types I understand what "User-Defined Table Types" and "User-Defined Date Types" are, so what goes in "User-Defined Types"? Can anyone explain what these are? Thanks very much

SSIS User Defined Data Type (Alias Data Types) and OLE DB Command validation

Hello everyone, I've been having an issue with trying to run my SSIS package on a server, and it seems to be failing on the OLE DB Command step.  What we have in our SQL 2005 DB, is a User-Defined Data Type (base type char(7)) and the OLE DB Command is supposed to call a proc that passes in a value of this data type. ie:  CREATE PROCEDURE myProcedure ( @passedInFromSSIS MY_DATATYPE ) AS .... In my SSIS package, I have the type defined as DT_STR with a length of 7.  Now, when I run the package locally (via Visual Studio), the process runs with success.  However, once the package is deployed on a server and run from an application (note: it is run under a different user), the process fails on a validation step with a "Invalid Parameter Number" error. Now, if I change the input parameter in my proc to the base type of the user-defined data type, the process works again. Has anybody run into a similar issue or know what may be causing this issue?  I first suspected perhaps I needed to grant permissions on the user-defined data type (since I was able to run it under my security context, but not under the application's), however noticed that there isn't security tied to the types.  Any other thoughts?  Please let me know if you need further explanation.  Thanks!

Mapping custom UDT's (User Defined Types)


We have custom user defined types on SQL 2008. I would like to map these data types for SSIS when exporting / importing data. I've managed to do this successfully for the built-in types such geometry / geography by editing the MSSQLToSSIS10.XML.

My question would be if this is also possible for a custom UDT's created with a different schema, ie [myschema].[MyUDT]? If I define the mapping, SSIS only looks for the UDT definition without the schema (which works in the mapping file without the schema), but fails on the table creation, since the data type cannot be found.

If I define [myschema].[MyUDT] in the MSSQLToSSIS10.xml file, the mapping cannot be found in SSIS. I'm stuck here with a catch 22.

Any advice / workarounds ?


SQL Types and user defined aggregates


I have been experimenting with SQL 2008 CLR Aggregates with some success thanks to this forum.

I have tried changing a working aggregate from using SqlDouble to using SqlDecimal and the function compiles fine however when it comes to the CREATE AGGREGATE statement no matter what T-SQL types I use for the input and return values I get error messages.

According to BOL SqlDecimal should map to DECIMAL or NUMERIC quite happily but it doesn't seem to work.

SqlDouble works quite happily with FLOAT and REAL.

Should I even be using SqlDecimal and if so how?

Are there any other data type anomolies.  I suspect that the date datatypes will also give problems.

Workflow designer rehosting and user defined types


I'm rehosting workflow designer, and I want to use my custom types as variables of the workflow. At first I couldn't get my types to be shown in the "Browse and Select a .Net Type" Window. I maneged to do that by loading assembly to AppDomain:


But now when I want to assign a property of a variable which is of type from my assembly I get the message: Compiler error(s) encountered processing expression "variable1.Attribute1" Type "BusinessObject1" is not defined

User Defined Functions in Microsoft SQL Server

User Defined Functions are compact pieces of Transact SQL code, which can accept parameters, and return either a value, or a table. They are saved as individual work units, and are created using standard SQL commands. Data transformation and reference value retrieval are common uses for functions. LEFT, the built in function for getting the left part of a string, and GETDATE, used for obtaining the current date and time, are two examples of function use. User Defined Functions enable the developer or DBA to create functions of their own, and save them inside SQL Server.

"Type ASP.x is not defined" error for dynamic user control when publishing updateable


I have a user control (ContainerCtrl) which programmatically instantiates another user control (SimpleCtrl) in OnLoad. It builds and runs fine, but when I publish it I get the error "error BC30002: Type 'ASP.SimpleCtrl' is not defined." (If I publish without the "updateable" option there's no error, but I need the site to be updateable.)

I assume this has to do with the particulars of how the assembly(ies) are built for the different compiler options. My question is, how can I get it to work properly for "updateable" publishing?

VS 2008 / .NET 3.5 sp1

The code follows (web.config is the default from Visual Studio).

Many thanks, Dave


<%@ Control Language="VB" ClassName="SimpleCtrl" CodeFile="SimpleCtrl.ascx.vb" Inherits="_SimpleCtrl" %>

    Simple control


Partial Class _SimpleCtrl
    Inherits UserControl

    'Nothing to see here.

End Class


<%@ Control Language="VB" CodeFile="ContainerCtrl.ascx.vb" Inherits="_ContainerCtrl"

Problem in accessing resource file dynamically.


Steps I have followed are
1.I have created myres.en-GB.resx file at C:.
2.then created locale specific myres.en-GB.resources using resgen at c: only,
resgen c:\myres.en-GB.resx
3.Linked the Resource File to Assembly using AL.exe which is giving me assembly at c: only.
Al /t:lib /embed:myres.en-GB.resources /out:ClassLibrary1.dll
4.I have created one web application using vs 2008 & then build it.
5.Then placed both resource file & dll in web applications Bin\en-GB folder.
6.Anf finally written code at page load like
 CultureInfo info = new CultureInfo("en-GB");
            Thread.CurrentThread.CurrentCulture = info;
            Assembly asd = Assembly.Load("ClassLibrary1");
            ResourceManager res = new ResourceManager("myres.en-GB", asd.GetSatelliteAssembly(info));
            string sdf = res.GetString("Lab1");

but after running application I m getting following error
MissingManifestResourceException", "Could not find any resources appropriate for the specified culture or the neutral culture. Make sure "Cla

Problem accessing Sharepoint 2007 MySite


My users are having an issue accessing their mysites.  When they go to the main portal page or any other site and click the link at the top right of the page to go to their Mysite they are thrown into an endless authentication loop.  If they cancel out of the loop the URL in the address bar is for http://mossserver/_layouts/MySites.aspx

For a user to successfully get to their myself they need to enter the full url like http://mossserver/personal/username/default.aspx

Now, I know somewhat how this happened.  It resulted from me trying to delete a user's mysite and I apparently did it wrong but I'm not sure what I did.  It almost seems like I deleted a redirect page or something...

Looking for any ideas.



User Control not accessing the value of the properties send to it from the aspx page.


I have a dynamic accordion on a page. Set its datasource to an object of paged data source.The user control is in the accordions content template. When i was writing code the properties were showing but after giving the values of databases as


<uc:UserControl1  runat="server" Property1='<%#DataBinder.Eval(Container.DataItem,"columname1")%>' Property2='<%#DataBinder.Eval(Container.DataItem,"columname2")%>'  ></uc:UserControl1>


but the values shows null, these properties are shown as textboxes text property by user control.

MoreOver when I checked the values of database Items.So i made a textbox instead of usercontrol here as

<asp:TextBox ID="txtTest" runat="server" Text='<%#DataBinder.Eval(Container.DataItem,"Columname1")%>'></asp:TextBox>

It is showing the correct value. And if i pass the value directly it is also showing correctly like

<uc:UserControl1  runat="server" Property1="XYZ")%>' Property2="ABC")%>'  ></uc:UserControl1>

. where I am wrong

the problem is eval is not passing the value to usercont

Best practice? Accessing subset of data based on user


I have a DB and associated Asp.net 3.5 web app. The database holds information on our clients. We're adding client access functionality and I need to know how best to restrict user access to data only pertaining to their client.

The clients are stored in a hierarchy and each user can be associated with multiple clients. The user should only be able to access the data for their associated clients and any sub clients thereof.

My initial thought is to add a collection of clients to their profile, then use that to filter all queries; however, my gut instinct tells me that using the profile for such a security function is inadvisable.

Could anyone provide any insight on a best practice for this? I'm sure it must be a pretty common need.


Problem with User Control Properties


I've been trying to figure out a problem that I've had for the past week and believe that the issue is something with a user control that I created. In short, the properties for the user control are recognized by intelisense when I register the control and then add it in the markup for a page. However, the values that I set in the markup are ignored by the class as if the properties don't exist.



Parent Page

 <%@ Register Src="../../controls/contactdetail.ascx" TagName="ContactDetail" TagPrefix="tfc" %>

<tfc:ContactDetail runat="server" ID="UserControlContactDetail" Title="Contact-Detail" ContactId="1580" EnableViewState="true" />   



User Control Class

Imports System.Data
Imports System.Data.SqlClient

Partial Class control_ContactDetail
    Inherits System.Web.UI.UserControl
    Public Property ContactId() As Integer
            Return _ContactId
        End Get
        Set(ByVal value As Integer)
            _ContactId = value
        End Set
    End Property
    Private _ContactId As Integer
    Public Property Title() As String
            Return _Title
        End Get

Creating a table-valued function on a user defined type in SQL Server CLR

We would like to be able to create a table-valued function on a user defined type.  We would like the syntax in SQL to look like it works for the XML nodes function:   DECLARE @myXml XML = '<a><b>1</b><b>2</b><b>3</b></a>' ; SELECT  node.query('text()') FROM    @myXml.nodes('a/b') nodes (node) ;   In other words, in the FROM clause, we can access the "nodes" method of the xml variable and it returns a rowset. Thanks!

User Defined Data Type - using SMO *** NEED HELP ! ***

Hi,Is it possible to script out the User Defined Data Types via SMO in .NET and specifically VB.Net?  I have not been able to find some examples on how to do this.  If you have some experience with this, pleasepost some examples or provide links where I can find this information.Thank you in advance.-Sydney

how to include a user defined table type as input for stored procedure

Hi ,  I have a user defined table type which i need to pass as input parameter to the stored procedure .How can i do that?

Problem in accessing datakeys

I have kept an int element and char element as datakeys of gridview,first one being the int element. But when I write the following code, error comes up stating that "Index was out of range. Must be non-negative and less than the size of the collection.Parameter name: index" Why does the error come up? and how to remove it? protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e) { Char s =Convert.ToChar(GridView1.DataKeys[e.Row.RowIndex][1]); if (s == '0') e.Row.BackColor = System.Drawing.Color.Red; else e.Row.BackColor = System.Drawing.Color.SeaGreen; }  
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend