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


Post New Web Links

Get inserted ids when inserting from User Defined Table Type

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


View Complete Post


More Related Resource Links

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!

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?

"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

SimpleCtrl.aspx------------------------------

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

<div>
    Simple control
</div>

SimpleCtrl.aspx.vb------------------------------

Partial Class _SimpleCtrl
    Inherits UserControl

    'Nothing to see here.

End Class

ContainerCtrl.aspx------------------------------

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

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

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!

inserting data to Users Table in ASPNETDB from Create User Wizard

  

When a user starts a membership using the Create User wizard supplied by the aspnetdb profile service, I need to add a field to a table in aspnetdb and upon clicking the ContinueButtonClick insert some data.  The field I'll add is "SubscId".  My first question is what table is best for this - Users or Membership?

Secondly, what is the best way to insert new data for this scenario?  code behind?  markup?  Can someone provide basic syntax of insert code in code behind for this scenario?


Passing a user defined type property to WCF contract

  

Hi,

i have a class with a user defined type as follow.

[DataContract(Namespace = "a.com")]
  public class Order : IOrderDescription, INotifyPropertyChanged
  {
    public static OrderLimitations OrderLimits { get; set; }
    public OrderLimitations ThisOrderLimits { get; set; }
   
    [DataMember]
    public OrderAlgorithmDescription AlgoDescription { get; set; }
}


 [DataContract(Namespace = "a.com")]
  public class OrderAlgorithmDescription
  {
    [DataMember]
    public OrderAlgorithmName AlgorithmName { get; set; }
    [DataMember]
    public DateTime UTCStartTime { get; s

can i access to clr user defined type with indexer ([]) ?

  

i created a clr udt with indexor but is not accessible from sql .

how can i do it?

thanks


user defined return type

  

Hello

For a assignment I have to implement a Service and a Client which communicate of WCF.

The problem is, that the Service has as return value a user defined type (or better class). I can open the Server and send everything, but the Client breaks up when it receive the type.

Could somebody help me pls.

Here ist my code:

Server Side

using System;
using System.ServiceModel;

namespace
 Stest
{
 [ServiceContract]
 public interface IService
 {
   [OperationContract]
   IStock getPrice(String symbol);
 } 
}


using System;

namespace Stest
{
 public interface IStock
 {
   String toString();
 }
}

using System;
using System.Runtime.Serialization;

namespace
 Stest
{
 [DataContract]
 public class Stock : IStock
 {
  [DataMember]
  string symbol;
  [DataMember]
  float price;
  [DataMember]
  float change;
  [DataMember]
  long lastUpdated;

  public Stock(string symbol, float price, float change, long lastUpdated)
 {
   this.symbol = symbol;
   this.price = price;
   this.change = change;
   this.lastUpdated = lastUpdated;
 }
 public String toString()
 {
  return "Symbol: " + symbol + "\nPrice: " + price +  "\nChange: " + change + "\nLast Update: " + lastUpdated;

user-defined table constraint behaving unexpectedly

  

I have a table where I want to prevent insertions/updates being made based on a (UDF-based) check to see what is already in the table.   The constraint does not behave as I would expect though. I have put this into a simplified example:

USE tempdb;
GO
IF OBJECT_ID('tempdb.dbo.UniqueNamesOnly') IS NOT NULL
  DROP TABLE UniqueNamesOnly
IF OBJECT_ID('tempdb.dbo.fnCheckFirstNameNotInTable') IS NOT NULL
  DROP FUNCTION fnCheckFirstNameNotInTable
GO
CREATE FUNCTION fnCheckFirstNameNotInTable (@FirstName VARCHAR(50))
RETURNS TINYINT
AS

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.

how to get id value from the row inserted in a table with thr trigger.

  

I have a reports table with column report id and and report version. I created a trigger on this table to execute an application to do some processing each time a row is inserted in reports table. I need to pass the new report id execute this application from the trigger. How do I get the report id from the row that was inserted?


Thanks.


The type 'System.Data.Linq.DataContext' is defined in an assembly that is not referenced. You must

  

Hi,

This is frustrating, and I don't know how to solve it.

I have a strange problem. I am adding a LinqDatasource object, and set the context:

        protected void LinqDataSource_ContextCreating(object sender, LinqDataSourceContextEventArgs e)
        {
            e.ObjectInstance = new KaruselaDataContext(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
        }


Then I get this error:
The type 'System.Data.Linq.DataContext' is defined in an assembly that is not referenced. You must add a reference to assembly 'System.Data.Linq, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.

 

On web.config I already have this:
<add assembly="System.Data.Linq, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>

NOTE: The datacontext is in a different project (DLL proect) where I added a refernce to System.Data.Linq.

is there another way adding a refernce to a web project? or only though teh web.config?

Thanks.


BC30506: Handles clause requires a WithEvents variable defined in the containing type or one of its

  

why do i get

BC30506: Handles clause requires a WithEvents variable defined in the containing type or one of its base types.

with the code

 below ..thanks

 

<form id="form1" runat="server">
    <div>
               <asp:Button ID="Button1" runat="server" Text="Run Access" />
    </div>
    </form>
In your code behind:

=User!UserID from windows authentication using table from another database

  

hi,

i'm using sql server 2005, sql server business intelligence development studio for my reports and deploy them in my wss3.0 site.

what i'm trying to do is i'm trying to restrict wich users can see specific data within a report.

now, the thing is. the report gets the data out of my DB1 database and my windows authenticated user are in the WSS_content database.

when i deploy my report in my wss3 site. it gets the user that is loged in. but of course it does not display any data because the tables are not in the same database.

does someone knows how to do this?


want to autoincrement a character type in table

  
For Example, "E0001" Everytime  it will automatically incremened by 1. "E0001" -> "E0002-> "E0003" and so on. "E0010" -> "E0011" and so on. "E0100" -> "E0101" and so on.  
Categories: 
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