.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

Updating a table using a variable object

Posted By:      Posted Date: October 13, 2010    Points: 0   Category :Sql Server

I have a variable of Data Type 'Object'. I populate this with 1 row of data via an Execute SQL Task control,  because I have a For Each Loop Container around this Execute SQL task control that gets 1 ID at a time. What I want to do is now that I have the row of data in my variable object, is update my table with the new data.

I am thinking I need to start with a Data Flow Task? But I'm not sure how/what to use to reference my variable so that I can map it to my destination.

View Complete Post

More Related Resource Links

How to access the refrenced table fields in Ilist object of table MVC asp.net



I have 2 tables, master, detail. 1.master table have fields (id, username, plan)-->id is primary key (PK) 2. detail table have fields (srNo,id, worksummary, ... )--> srNo is PK.

I have created foreign key relationship from detail to .master table for "id" field.

the code is:

IList<detail> objDetail=new List<detail> (); 
IList<master> objMaster = new List<master> ();
string[] sarray = queryFields.Split('|');//

for (int i = 0; i < sarray.Length; i++)
string[] sfields = sarray[i].Split(',');

if (sfields[0] != "")

objDetail.Add(new _detail { Id = count , modify = sfields[1].ToString(), verified = sfields[2], I});


I have problem to add fields in "objDetail" using Add method. But I am unable to access the reference field "Id" , rest of the field of detail table can be accessed using objDetail.

How can I access the "Id" field from objDetail object to add in IList.

I want to solve this problem

How to load a new table with the value of a variable from SSIS package?

Hi, i have two variables in a SSIS package Var1 and Var2. Both of these variables have values. is there any way i can put the values of these two variables in a new table? e.g In New table col1 having value of Var1 and col2 having value of Var2. Thanks

How to send record(which is a weblink) from a table to the value of the variable in SSIS package and

Hi Folks, I have table called Table1 with columns, col1 and col2 with col1 having weblinks for the report and col2 the name of the report. Now, i have a package with a variables var1 and var2 which should get the col1 and col2 values respectively from table1 and send it through an email. if the weblink gets updated in the table, package should send the updated link. i know the reverse way of it but trying to do somethig like this. Appreciate any help from you guys. Thanks

Using EXEC with a table variable?

I want to pass the results of a stored proc into a table variable in SQL SEVER 2000. Something like this declare @a table ( employeeid int )   exec @a=dbo.MetricsProcessor_GetTopEmployees @parameter1, @parameter2   ---------------------------------------------------------------------------------------------------------------------------------- I tried insert @a execute dbo.MetricsProcessor_GetTopEmployees @parameter1, @parameter2    but got this error: Msg 197, Level 15, State 1, Line 6 EXECUTE cannot be used as a source when inserting into a table variable.   How can i do this.   Thanks.

Using a Variable in SSIS - Error - "Command text was not set for the command object.".

Hi All, i am using a OLE DB Source in my dataflow component and want to select rows from the source based on the Name I enter during execution time. I have created two variables, enterName - String packageLevel (will store the name I enter) myVar - String packageLevel. (to store the query) I am assigning this query to the myVar variable, "Select * from db.Users where (UsrName =  " + @[User::enterName] + " )" Now in the OLE Db source, I have selected as Sql Command from Variable, and I am getting the variable, enterName,. I select that and when I click on OK am getting this error.   Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E0C.An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80040E0C  Description: "Command text was not set for the command object.". Can Someone guide me whr am going wrong? myVar variable, i have set the ExecuteAsExpression  Property to true too. Please let me know where am going wrong? Thanks in advance.

Updating table from a table on another server

I am updating a column in table from a table on another server and am having some troubles. My code looks like this...   UPDATE TABLE1 SET COLUMN1A = SERVER2.DATABASE2.SCHEMA2.TABLE2.COLUMN2A FROM TABLE1, SERVER2.DATABASE2.SCHEMA2.TABLE2 WHERE TABLE1.COLUMN1B = SERVER2.DATABASE2.SCHEMA2.TABLE2.COLUMN2B   I keep getting "The multi-part identifier ____ could not be bound."   Security rights are in place and I have access to Server2    Thoughts?

Invalid object error on temp table on a particular environment

Hi, I am using a temp table in my sp. I have written the following tsql before and after using the temp table. IF OBJECT_ID('tempdb..##TEMP') IS NOT NULL DROP TABLE tempdb..##TEMP; Data will be inserted to the temp table consecutively using a cursor. I use SELECT * INTO tempdb..##TEMP FROM EXEC('query') to fill this temp table for the first time and INSERT INTO tempdb..##TEMP(<fields>) EXEC('query') for the remaining times. The SP is working fine in my laptop, but I am getting an Invalid Object error on this temp table name in the production server. Could anyone tell me the possible causes please? Thanks Sree

Stored Procedure utilizing table variable

I'm creating a stored procedure that will take the results of a temp table and check for existence using the IN keyword.  The code looks like this: ALTER PROCEDURE [dbo].[studentBranchLogin] -- Add the parameters for the stored procedure here @userName varchar(50), @userPass varchar(20) AS BEGIN DECLARE @dActive bit; DECLARE @TT TABLE (dept int, active bit); -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; INSERT INTO @TT(dept, active) SELECT deptid, active FROM srcuDeptId WHERE active = TRUE -- Insert statements for procedure here SELECT srcuid FROM srcuLogins WHERE logon = @userName AND password = @userPass AND srcuid IN @TT END I get a Msg 170, Level 15 state 1 Line 24: Incorrect syntax near '@TT'. So while I'm sure the @TT temp table isn't being read, I find very few resources for answer what the proper syntax should be.   Any assistance is greatly appreciated.

Problem with listing records from table (object not found)

Hi everyone, I'm creating a website based on ASP.NET MVC 2 and SQL Server 2008 R2 Express for the database. After creating the table in database, I want to list all records in that table. For that, I use "scaffold'ing in ASP.NET to automatically generate the view file. But I encountered an error when loading that page. The error message was: [CODE] Server Error in '/' Application. Invalid object name 'dbo.Accommodations'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Invalid object name 'dbo.Accommodations'. Source Error: Line 41:         </tr> Line 42: Line 43:     <% foreach (var item in Model) { %> Line 44: Line 45:         <tr> Source File: d:\MyProject\Views\Accommodation\Index.aspx    Line: 43 Stack Trace: [SqlException (0x80131904): Invalid object name 'dbo.Accommodations'.]    System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +2030802    System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +5009584 &n

How to declare a table variable in SSIS and then insert rows into it

Hello everyone,   I'll try to explain my problem as clearly as I can: 1)I have an Execute SQL Task that's inside a Foreach Loop Container. 2)Inside the Execute SQL Task, I have an int OUTPUT column. 3)With each iteration, I need to insert the data from the INT output colum into a table variable. 4)After the Foreach Loop finishes the iterations, I need to use the table variable to create a report based on the data inside.   My question is the following: How can I declare a table variable so that I can do all of the above?   Thank you, CostinP

Passing Table Variable to Stored Procedure

My stored procedure expects a uniqueidentifier as a parameter.  However, this unique identifier is stored in a table variable, which looks like this:DECLARE @TableGiftGuid table ( GiftGuid uniqueidentifier )When I try to execute a stored procedure by passing the GiftGuid like so:EXEC my_procedure (SELECT GiftGuid FROM @TableGiftGuid) I get an error. What I ended up doing is declaring another variable, storing the GiftGuid into it and then passing the variable to the stored procedure like this: DECLARE @TableGiftGuid table ( GiftGuid uniqueidentifier ) DECLARE @GiftGuid uniqueidentifieDECLARE @TableGiftGuid table ( GiftGuid uniqueidentifier ) DECLARE @GiftGuid uniqueidentifier SET @GiftGuid = (SELECT GiftGuid FROM @TableGiftGuid) EXEC my_procedure @GiftGuidThat works, but is there a more elegant way of doing this?

Update producing invalid object name for temp table

If IWantThisDSN = 0 Then SQLString = "SELECT importparamsID, ESubmitType, Description, ImportFileName,ClientCode, Notes00, Notes01, Notes02, Notes03, Notes04 into #t0 from Importparams " Else SQLString = "SELECT importparamsID, ESubmitType, Description, ImportFileName, ClientCode, Notes00, Notes01, Notes02, Notes03, Notes04 into #t0 from Importparams " End If cn.Execute(SQLString) SQLString = "UPDATE #t0 Set Description = ' ' WHERE Description IS NULL" cn.Execute(SQLString) I'm getting the error in the last cn.Execute(SQLString) line...   Message=[Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name '#t0'. I'm using VB 2010 and SQL Server 2008. This worked fine before with VB 6 and SQL Server 2000 but with the "upgrade" it's not working... If you need any more info let me know what I may have left out.  Thanks!

can dynamic sql be used against a table variable?


This Works

create table #tmp1(rowID int)
declare @rowID int
set @rowID = 1
exec('insert into #tmp1(rowID) SELECT ' + @rowID)

This does not work

Declare @tmp1 table(rowID int)
declare @rowID int
set @rowID = 1
exec('insert into @tmp1(rowID) SELECT ' + @rowID)

Error Msg:
Msg 137, Level 15, State 2, Line 1
Must declare the variable '@tmp1'.

Is there a way to use Dynamic Sql against a Table var -- @tmp1?
How to do this?

The goal is to be able to pass in a string param like this to a proc

s1 = 'aa'',''bb'',''cc'

declare @s1 varchar(20)

Begin As

declare @tmp1(...)
exec('Insert Into ' + @tmp1 + ' (...) Where fldx In (''' + @s1 + ''')')


how to store the values in a object variable (package level scope) in a script component

I have declared a variable of object type with package level scope.  I want to assign values to this variable inside a script component.  My object should hold 5 columns in a row.  How to implement this ?

Bridge Table dimension or fact? updating from snapshots



Scenario: Bank Accounts and Customers. One Account can have many customers and many customers can have one joint Account. so its Many to Many relationship.

Special Scenaio: Bank provide us daily snapshot of all thier dimensions and facts, every night thier ETL run, and newsnapshot is available, previous is gone.

I am using SCD Transformation to update the dimensions, Type 1 for all the columns.

Tables1: DimAccounts (AccountsID(PK))

Table2:DimCustomers (CustomerID(PK))

Table3:DimBridge (AccountID (FK), CustomerID(FK), RelationShip (varchar10))

Question1: Are we supposed to treat bridge tables as Dimensions or Facts?

Question2:If it is to be treated as Dimension, How would I apply SCD Wizard to it, Since there are two business keys involved?

Question3: Do i need surrogate key in Bridge Table, like i have in other dimensions?

Thank You


Performance Drop when switching from a #temp table to a @temp table variable


I am trying to convert a stored procedure to a table valued function and the performance has taken a HUGE hit and I was wondering if there was anything that can be done about it. Since a table valued function can not use #temp tables it must be converted to a @temp table variable.

Here are some steps I have already taken...

The original stored proc starts off by populating a #temp table via "Select x Into #temp ..."

Leaving it a stored proc for now, I explicitly created the #temp table and did an "Insert Into ... Select From" to more closely model how it must work when using a @temp table variable. There was no discernible performance difference.

Still leaving it as a stored proc, I then swapped out the #temp table with the @temp table variable and now, all of the sudden, the performance drops from sub-second to over a minute!!!

The temp table only has one field defined as an int and it is distinct, so I tried making the field the Primary Key to see if that would help and it did not.

The temp table is created by scanning a table with around 11,000 rows and the temp table itself has about 4400 rows in it (if it makes a difference to anyone).

Does anyone have any suggestions (or hope) for me?


convert string to int before updating database table?


I have a form in which I display data from a database table, change the data and then update the database table. The form consists of textboxes, so all data displayed in the form is string. However - one data is int in the database table (the rest is varchar), so I need to convert it from string to int when I update the database table. How do I do it?

protected void SaveProd_Click(object sender, EventArgs e)
        string BildID = TextBoxProdPictID.Text;
        string namn = TextBoxProdName.Text;
        string kategori = TextBoxProdCategory.Text;
        string stl = TextBoxProdSize.Text;
        string pris = TextBoxProdPrice.Text;
        string iLager = TextBoxProdNumberOf.Text; //this is int in the database table

        SqlConnection conn = new SqlConnection(config);
      //  conn.Open();
        string sql = "UPDATE Produkter SET kategori ='" + kategori + "', namn ='" + namn + "', stl ='" + stl + "', pris ='" + pris + "', iLager ='" + iLager + "' WHERE ID ='" + ID + "'";
        SqlCommand comm = new SqlCommand(sql, conn);//Conversion failed when converting the varchar value '__Page' to data type int.
        SqlDataReader dr = comm.ExecuteReader(); 

        //clear t
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