.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

How to write the correctly query to this task?

Posted By:      Posted Date: September 25, 2010    Points: 0   Category :Sql Server

Hi All.

I have sql table with three columns Cparent_alias ,Custom_alias,C_descript I wont to update the name in C_descript column BUT before I need to check if the name already exist or not.




View Complete Post

More Related Resource Links

How to write the LINQ query for the following

Hello All, I have an entity type with the following properties: UnitId, UnitDescription, ParentId, EffectiveDate - (Say the entitty type is Unit)  I want to filter out a list of UnitIds whose effective date is less than a given date. But since there could be many EffectiveDate s less than a given date, I want to take the latest one(Max). Simply told, I want to get the unit ids with the most recent effective date prior to a given date. How can I do this in a single linq statement? Appriciate any help.

Access the SQL Command Query Value Property from the OLEDB Container within a Script Task

Hello, I'd like to Access the SQL Command Query Value Property from the OLEDB Container within a Script Task so I can audit the queries which are executing. What is the syntax for this or does anyone have a sample on how to do this? Thank you

How to write query with subquery more efficient

Hello, I have this query which generates sick days. I have a sick start date and end date, and by joining it with a date table I get all sick days for each employee. But as you can see i select a number of fields, but in the subquery i select a number of fields which are the same. Is it possible to write it more efficient? Thans in advance! SELECT z .ziekmeldingID, z .hrmID, z .ZiekStartDatum, z .ZiekEindDatum, z .ZiekStartTijd, z .ZiekEindTijd, z .ZiekOpen, z .ZiekTypeID, z .ZiekRedenID, z .ZiekStart_KEY, z .ZiekEind_KEY, z .Verzuimduur, D .Datum AS ZiekDag,   CAST(CONVERT(char(8), D.Datum, 112) AS int) AS ZiekDag_KEY, z .hr301_date, z .PercentageZiek, z .PercentageHersteld FROM     (   SELECT dbo .hr300.hr300_obj AS ziekmeldingID, dbo .hr300.hr300_date_start AS ZiekStartDatum,   CASE   WHEN dbo.hr300.hr300_date_end IS NOT NULL   THEN dbo.hr300.hr300_date_end   ELSE getdate()   END AS ZiekEindDatum, dbo .hr300.hr300_time_start AS ZiekStartTijd, dbo .hr300.hr300_time_end AS ZiekEindTijd, dbo .hr300.hr300_open AS ZiekOpen, dbo .hr300.hr104_obj AS ZiekTypeID, dbo .hr300.hr038_obj AS ZiekRedenID, dbo .hr300.hr200_obj AS hrmID,   CAST(CONVERT(char(8), dbo.hr300.hr300_date_start, 112) AS int) AS ZiekStart_KEY,   CAST(CONVERT(char(8), dbo.hr300.hr300_da

Data query task issue

Hi Someone help me undersand how to implement a Data meaning query  in SSIS project step by step. even a small package for testing this task ThanksThe complexity resides in the simplicity

Efficiency: new column in source query or derived column task?

Hi All, I've just started working on an SSIS package that pulls data from an OLE DB Source by a query. A new column needs to be added based on the value of a queried column. I was wondering if it's better to do that in the query or with a derived column? A simple example: I have a table that contains CustomerName and CustomerCode (this one can be V /valid/ or I /invalid/). I need to store the CustomerCodeDesc in a separate column in the destination table. Is it better to alter the query like this: SELECT CustomerName, CustomerCode, CASE WHEN CustomerCode = 'V' THEN 'Valid' ELSE 'Invalid' END AS CustomerCodeDesc FROM CustomerTable Or is it better to use a DerivedColumn task in the DataFlow? Or maybe it doesn't really matter...

Cannot make use of the Merge Task correctly

Hi All, I'm Testing the Merge Task and here is the situation I have two sources one is an SQL  table CREATE TABLE MyTable ( Identifier INT NOT NULL IDENTITY(1,1), Content NVARCHAR(20) )  the other one is a flat file that has content suitable to that above tabe, For the SQL table I used an OLE DB Source and I specify a sql command to retrieve the data, the sql commad is as follow:  SELECT     Identifier, Content FROM         dbo.MergeTable ORDER BY Identifier, Content As you can see the command sorts the data. In the other side (The flat file) I added a sort transorm that recieves data fom that flat file and I configured that sort task to sort data exactly like the ole db one. Now the problem is when I tie the both inputs to the merge task then this last one still raise the error Error 4 Validation error. Data Flow Task Merge [923]: The input is not sorted. The "input "Merge Input 1" (924)" must be sorted.   DlmCustomer.dtsx 0 0  Inspite of the fact that the ole db source data specifie a command that sort data??? Thank you   The complexity resides in the simplicity

Write select query with a paremetrized where clause, and populate database with the retrieved column

I want to be able to retrieve column values using the dropdownlist values as parameters then populate the tbPO table with the retreived values. Where and how do I do it. ImportsSystem.Data.OleDb ImportsSystem ImportsSystem.IO Imports ?directcostDataSetTableAdapters PartialClass po_header  Inherits System.Web.UI.Page  Dim cn As OleDbConnection  Dim cmd As OleDbCommand  Dim dr As OleDbDataReader  Dim icount As Integer  Dim str As String  Dim vendor_id As Object   ?  Public Sub btn_click(ByVal sender As Object, ByVal e As System.EventArgs) Handles button1.ClickTry   context.Items.Add(     cmd.CommandType = System.Data.   cmd.CommandText = Dim context As HttpContext = HttpContext.Current"Company_Name", DropDownList3.Text)Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Users\Owner\Documents\Visual Studio 2010\WebSites\WebSiteJun25\App_Data\directcost.mdb;")Dim cmd As New OleDbCommand()CommandType.Text"insert into tbPO(ponumber, suppliername, ShipTo, basictype, DateNeeded, Purpose,ShippingInstr, ProjId) values(?,?,?,?,?,?,?,?)" cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.Add

Query help to write case statement in Where clause?

Hi All, I have table something like this:- SELECT UserName, IsManager FROM Employee IsManager has only two values either 1 (which means user is a Manager) or 0 (which means user is not a Manager) Now I want to write below query:- DECLARE @IsManager INT SET @IsManager = 2 SELECT UserName, IsManager FROM Employee WHERE IsManager = CASE WHEN @IsManager = 1 THEN 1 WHEN @IsManager = 0 THEN 0 WHEN @IsManager = 2 THEN 1 | 0 ----- THIS IS NOT WORKING, when I set @IsManager value to 2 then where clause should be like this "IsManager = 1 OR IsManager = 0" END Can anybody help me out in modifying about query so that it produces both the results when @IsManager variable value "2". Thanks Regards, Kumar

How to extend this query to write the results back to a database?

FOr this query I have four tables I would like to access. SELECT StockA, SUM(Score) AS Total FROM dbo.ScoresNew_LongPrice WHERE [When] >= @StartTime AND [When] < @EndTime GROUP BY StockA ORDER BY SUM(Score) DESC _LongPrice, _ShortPrice, _LongVolume, _ShortVolume I would like to improve the above query to return not just the results from LongPrice, but four columns, of sum(score), from each table, if possible. And, if that is possible, I would also like to have the query then update another table with its results.  Not a new table, but just updated to an existing table. Anyway, I can do some stuff in T-SQL but this seems over my head.  Any help would be appreciated. (The four tables all have identical structures)

is there any tool or add-on for write color and comfortable query on access-2007 ?

hi is there any tool or add-on for write color and comfortable query on access-2007 ? thank's in advance

Help to write very simple query...


How I can write this query ?

I have value = 1000

I wont to get all value from table [MHGROUP].CUSTOM2

from column CPARENT_ALIAS

all values which beginning with num 1000.



cmd2 = new SqlCommand("SELECT * FROM [MHGROUP].CUSTOM2 WHERE CPARENT_ALIAS ='" + value + "'", con2

Can't make parameterized query work in data flow task


I have an OLE DB source in a data flow task.  The connection is pointing to a SQL Server 2008 instance.  My SQL works fine when I click the preview button.  Now I need to make it parameterized by adding a question mark in my where clause.  After doing so & clicking the Parameters button, I get the error described in this post .  When I try to do what's described therein to correct the problem (click the "build query" button), I get the error "The PIVOT SQL construct or statement is not supported."

I can't make this query an expression; it's simply too complex to maintain that way (besides, just pasting it into an expression builder window and trying to evaluate as is doesn't work - can't evaluate).

So the question is, how do I parameterize a query?  And why does SQL here work so radically differently from SQL in an execute SQL task?

Custom Task form of a SharePoint Designer workflow is not displaying correctly


We have created a custom workflow using SharePoint Designer. We are using one action “Collect Data from a User”. As you know, this action sends an email to a user with a link to a task form where user is supposed to accept or reject a request. We are encountering following issues with this form:

  1. Form is not a workflow form, it is a task item of the task list that the “Collect Data from a User” action of the workflow creates.
  2. Form opens as an item of the workflow task list in View mode (DispForm.ASPX) instead of Edit mode (EditForm. ASPX)
  3. When user clicks on “Edit Item” button, it displays all the fields of the task as editable – we want only the Approve/Reject option buttons and the comments TextBox (that we created during the workflow design) to be displayed as editable

Can someone please explain what is going on and how can we resolve the above issues?

"Access Denied" Error for WMI Reader Task Query


I am using SSIS to query all servers for their disk space from a remote machine using the WMI Reader Task by providing the Administrator user credentials to access all servers. The package execution fails and I get the following error in the Execution Results pane:

[WMI Data Reader Task] Error: An error occurred with the following error message: "Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))".

However, I do get the results for the Development and Staging servers but the Production servers come back with an Access Denied message. The user credentials for the Dev and Staging servers belong to the "Administrator" group whereas for the Production, the user belongs to the "Administrator" and the "User" group as well.

I am pretty much sure that it has something to do with access to running WMI queries on the servers but I could not find a forum that discussed this issue elsewhere. I even checked for the DCOM Security settings after a few searches led me to a post about "Connecting to WMI on a Remote Computer" on an msdn forum but they seem to be enabled for all environments.

My production database sits on a cluster, so does that make any difference? I have not used the WMI ta

LinQ query for My task


hi all,

I have to filter the MyTask according to "Assigned To" = "SharePoint Group" and "TaskStatus" = "InProgress", I have created the SPLinq.cs file using the SPMetal.exe for the particular webapplication but I am not able to get the "TaskStatus" field in the linq MyTask Object, also have checked the SPLinq.cs file it's having the definition of "Assigned To" and "TaskStatus".

Please Help me how to do filter the MyTask according to "Assigned To" = "SharePoint Group" and "status" = "InProgress" by using Linq.


Arun Kumar

How to write SQL Insert into query using LINQ


How I can write following SQL query using LINQ. There is no default constraint define for the ID column so we have explicitly pass the NEWID().

I want to replicate the products of a company to another company.

Insert Into Products
Select NEWID() AS ProductID, ProductName, CompanyID = @DestinationID, ProductCategoryID From Products Where CompanyID = @SourceID

Also once I get these result how can I use InsertAllOnSubmit to insert all the records at once without looping it.

Fetching Task List for the logged in User with LINQ/CAML/SQL query


Hi All,

We have some workflows which are been created already. The tasks are been assigned to users for their approval. Now, when the approver logs in it should display the Tasks List which are belonging to that particular user only. OR if the normal user logs inn, it should display Tasks belonging to that User only. A web part should display this. Pls. note that I have to show the Tasks from both "Workflow Task" & "Task".

The Task list should come from across site collections of a Web Application.

Hence,The Scopen should be WebApplication.
How do I achieve the same? Need help ASAP.



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