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

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Defaults and User Defined Types Tabs in SSMS

Posted By:      Posted Date: September 14, 2010    Points: 0   Category :Sql Server
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

View Complete Post

More Related Resource Links

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

Problem with accessing User defined types.



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

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"

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?

Auto Increment User defined Id

Hi can anyone give me an idea how to create an auto generated id like ED01,ED02 etc., so that when i am entering data the id should be automatically incremented

Best practice for user defined error messages in SQL Server 2005/2008 --Need help

My requirement is to setup a standard practice for our team in handling DB erros and also  user defined messages. We are handling exceptions using Raiseerror method with in Try and Catch blocks.  For user defined message, we are using Sp_addmessage to add our application specific error/warning messages with error codess>80000.  Ex: 80001 | Company name already exists. please check. Here is the sample code ********************************************************************* CREATE PROCEDURE [dbo].[usp_CompanyProfile_insert]  -- Add the parameters for the stored procedure here  (@company_name VARCHAR(50),  @company_code VARCHAR(3),  @user_id INT  ) AS BEGIN  -- SET NOCOUNT ON added to prevent extra result sets from  -- interfering with SELECT statements.  SET NOCOUNT ON;  BEGIN TRY -- Begin Try Block      -- Validate company name   IF EXISTS (SELECT company_id FROM companyprofile WHERE company_name = @company_name)    RAISERROR(90021, 16, 1)      INSERT INTO companyprofile (company_name,       company_code,       createdby)   VALUES(@company_name,     @company_code,     @user_id)    END TRY  -- End Try Block &

Get inserted ids when inserting from User Defined Table Type

Imagine this procedure:   CREATE PROCEDURE [dbo].[insertActive] ( -- Add the parameters for the stored procedure here @p_data dbo.activeUpdate readonly) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. insert into dbo.active (name,stateid) select name,stateid from @p_data END How can I get the ids of the rows that have been inserted?  I tried searching the net and the forums but could not find anything. Thanks for any and all help, Gareth.

How to make sure that user doesn't open same webpage in two different browsers or browser tabs?



We are using asp.net StateServer for asp.net session management. In our project we need to make sure that if a user is already logged in and if he is trying to acess the website from a different browser tab, we need to inform him that he is already logged in in another tab and use it instead of opening a new session. Consider the following scenarios:

1. User is logged on in tab1 and his session is still on, then user tries to logon from another tab, the browser sends the asp.net session_id cookie, In the page init i am checking if the Asp.net session Id cookie is present, if it is there i am checking if the it is a new Session (i.e. if the existing session timed out), if it is a new session then proceed with the user action else it means that the session in tab1 is still active so i am redirecting user to error page with appropriate message.

Now the problem with this approach is that, suppose session in tab1 is expired and user opens tab2 and performs some actions, now he goes back to tab1 and perform any action, i directly need to take him to log out page.

Please let me know how this can be achieved.

logging out users when user opens website in two browsers or two tabs of same browser.



We have the implement the following scenario in our application:

  1. We use windows authentication. User opens our website in tab1 and does some action but with out clicking on save he opens a new tab tab2 and opens the same website again.
  2. Now, if the user session in tab1 is active i need to warn users that the he is already logged on to application in some other browser and go to some log out page. But if the user session in tab1 is timed out then he must be able to continue with the website in tab2 as usual, but if he tries to do anything in tab1 he should go to session expired page.

I tried implementing it in following way.

  1. I have a hidden field in each page which will be set to unique Id using GUID.NewID().
  2. when user requests for a page the following code is executed.
  3. if (Request.Headers["Cookie"] == null)
                    //this means user is requesting hte page for first time.
                    //Generate a new GUID store it in session and also in the hidden variable on page.
                    Session["DefaultPage"] = Guid.NewGuid().ToString();
                    ((TextBox)page.FindControl("hiddenGUID")).Text = Session["DefaultPage"].ToString();
                else if (Reque

Calling a user defined function without Schema Prefix

Calling a user defined function without Schema


 Hi ,

I created a LOGIN User U1 and CREATED a database user DU1 and linked him with the Login User and I created a schema say S1 and set S1 as the default schema for DU1 .

I have a stored Procedure  and a userdefined function inside S1 . I logged in as the Login user, I can execute that procedure directly  without any schema prefix  but I cannot call a user defined function F1 directly (without schema prefix) . it throws an exception

"function name is not a recognized built-in function name"

How can i call a UDF directly with out a schema prefix


--Login User Creation
--Database User Creation and assign to default schema
--Giving User Privilege
EXEC sp_addrolemember db_owner,'S11'

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