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

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

Query returns correct rows but I am not able to concatenate the rows into a variable :(

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

Hi all,

Why in the code below I am able to list all rows and display the field 'stepID' but I am not able to concatenate them all in the variable @stepIds?

declare @date as datetime
set @date = '2010-09-20 23:01:20:000'
declare @instance as uniqueidentifier
set @instance = '04658F34-CBF1-4CCD-A683-F23BDB669D35'

  Declare @CrLf char(2)
  Set @CrLf = Char(13) + Char(10)
	Declare @stepIds varchar(8000)
	Set @stepIds = ''
		--@stepIds = @stepIds + ' - Step: ' + CONVERT(varchar(4), stepID)

View Complete Post

More Related Resource Links

Select second query if first query returns no rows



I need to return result of the second select statement if the first select statement returns nothing:

-- First statement
SELECT Salesmen.SalesmanID, Salesmen.FullName, 
  Salesmen.AssignedAppointments, Salesmen.Picture, 
FROM Appointments INNER JOIN
  Salesmen ON 
  Appointments.SalesmanID = Salesmen.SalesmanID
WHERE (Appointments.Closed = 1)
ORDER BY Salesmen.AssignedAppointments
-- Second statement
FROM Salesmen
ORDER BY AssignedAppointments

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

Returning rows not found in inner query

select * FROM [C_Accessioning].[HL7].[GMessages] where MessageID not in (Select MessageID from HL7.ORM_MSH) There are two rows of data in GMessages: MessageID MessageDateTime              Filename 28             2010-09-07 16:29:02.543    ORM1.hl7 29             2010-09-07 16:29:07.547    ORM2.hl7 There is no data in ORM_MSH. Please see sql statement copied above. The inner query returns null, I am expecting that the query return both rows from GMessages, but it does not return any rows. Please let me know how to fix.   Thanks,Deep

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

Searching a NVarChar Column with multiple wildcards returns all rows

I'm building a stored procedure to return a set of records, yes nothing big. The column is an NVarchar column and I'm using a select statement of Select * from Table1 where Column1 Like @Column1 Table is currently one record containing the words:  ***Some Test Word***  Is my test word I've set the value of @Column1 to: Some - no records returned Some% - no records returned %Some% - 1 record returned  % - 1 record returned *% - no records returned %*% - 1 record returned %Not Here% - 1 record returned   Can someone tell me why if I have a leading and trailing wildcard I will get all records returned?  Does it have something to do with the '*' characters in the field because some of my users are using these characters. I've also tried changing the select to: where RTRIM(Column1) Like @Column1 with the same result and where RTRIM(Column1) Like N'%' + @Column1 with the same result   What way should I give a user the way to search for a substring inside of an NVarchar field? Oh, I tried using the Substring function and got the same result. Thanks Mike    

Query to return TOP n with 'Other' grouping for all remaining rows

Hi all, I've been given a specific type of report to produce, and am having difficulty in getting my SQL right to return the data as needed. I need to return TOP n Categories based on amount of sales (which I can do) but then a final category with a sum of all other sales. The purpose would be to capture all of the small sales values into one 'Other' category so that the chart I'll produce does not have lots and lots of small-value columns. I found the following post which seems to come close: SELECT TOP 10 CompanyID, CompanyName, SUM(GrossSales) [GrossSales] FROM CompanySales GROUP BY CompanyID, CompanyName ORDER BY SUM(GrossSales) desc UNION SELECT -1, 'Other' [CompanyName], SUM(GrossSales) [GrossSales] FROM CompanySales WHERE CompanyID NOT IN ( SELECT TOP 10 CompanyID, CompanyName, SUM(GrossSales) [GrossSales] FROM CompanySales GROUP BY CompanyID, CompanyName ORDER BY SUM(GrossSales) desc ) But this gives a syntax error on UNION, which I'm guessing is due to the ORDER BY clauses. However I think the ORDER BY is needed to ensure I return the results in the correct order. Does anyone have any ideas of how to do this? Thanks for any assistance Matt

Need advice on preformance (web service returns list items on 20000 rows list)

Greetings I need to write a web service that will return sharepoint list items (default web services of sharepoint are no use to me since they just suck when the site is not in english).  Since we have lists with 20k+ items, the return xml might get large. Query web service did manage to do it, yet it took some time to output the xml with those 20k rows.  I am affraid that my custom made web service will preform poorly since it wont be "native" to sharepoint. I am asking for your judgement, did i fell into common pitfalls or something or is there any way to improve preformance of my code? My own web service code looks like this:   [WebMethod] public XmlDocument GetListItems(string siteurl, string listguid,string querytext,string rowlimit) { InitSchemaExporter(siteurl); SPQuery query = new SPQuery(); query.RowLimit = rowlimit.Length==0?10:Convert.ToUInt32(rowlimit); query.Query=querytext; return SchemaExporter.GetListItems(listguid, query); } public void InitSchemaExporter(string siteurl) { SchemaExporter.SetSite(siteurl); SchemaExporter.IgnoreField = ConfigurationSettings.AppSettings["IgnoreField"].Split(new string[1] { "\r\n" }, System.StringSplitOptions.None).ToList<string>(); ; SchemaExporter.IgnoreList = ConfigurationSettings.AppSettings["IgnoreList"].Split(new string[

function or query to return continuous span of months $ years as rows in query



I am using SS2008.

There are many situations in which I need to return a table where the rows are EVERY calendar month between two dates, not just the months where there is data present.

Is there a function that would take a beginning and end date mmddyyyy which would do this?

For example if I wanted 1/2004 through 8/2010?




and so on to





Thanks in advance,




SQL Query Multiple rows Top problem.

This is a simplified example of my problem.
I have a Product table, and a ProductSupplier table.  A Product can have multiple Suppliers; these suppliers are ordered according to a sort order field and each has an associated price for the Product.

I want to run a query to return all my products, along with their first supplier and the cost against this supplier.

Currently the only way I can do this is by performing select statements in the manin select clause to retrieve the supplier information.






SqlDataReader returns no rows


I have a simple C# 2.0 app that performs a simple query.

  SqlConnection oSqlCon=new SqlConnection(strConnectStr);
  SqlCommand oSqlCmd=new SqlCommand("select Name from Person",oSqlCon);
  SqlDataReader oSqlRdr=oSqlCmd.ExecuteReader();

I has been working fine. A client recently installed SQL2005 (their old version is SQL2000). When executing the above code on a machine other than the server no rows are returned. The connection uses sqlserver authentication, not integrated authentication.

To summarize: the above code works against the old sql instance from both the server machine and other local network machines; works against the new sql instance only from the new server; does NOT work against the new sql instance from local network machines

There are no errors reported; the query simply acts as though the table has no rows. I believe this to a security/permission issue but the two slq instances appear to be configured identically.

Any ideas or suggestions would be appreciated.

Select Primary Key Column of Table returns 0 rows


I have three tables: Design, Box, Wall.

Design has a column DesignId that is a GUID. It does not allow nulls, is unique, and is the primary key. The identity is set to false for compatibility with Entity Framework v4.

Box has  a column BoxId that is a GUID. It does not allow nulls, is unique, and is the primary key. The identity is set to false as well.

Box also has a column DesignId that is a GUID. It does not allow nulls, is not unique and is not the primary key.

Wall has  a column WallId that is a GUID. It does not allow nulls, is unique, and is the primary key. The identity is set to false as well.

Wall also has a column DesignId that is a GUID. It does not allow nulls, is not unique and is not the primary key.

Wall also has a column BoxId that is a GUID. It does not allow nulls, is not unique and is not the primary key.

Box.DesignId has been set as a ForeignKey relating to Design.DesignId.

Wall.DesignId has been set as a ForeignKey relating to Design.DesignId

Wall.BoxId has been set as a ForeignKey relating to Box.BoxId.


If I execute      SELECT DesignId FROM Design     I see the correct number of rows.

If I execute     SELECT BoxId From Box        I see zer

SQL Server user-defined aggregate returns an error if aggregating 0 rows

I have this SQL Server user-defined aggregate:

[SqlUserDefinedAggregate(Format.UserDefined, Name="median", IsInvariantToDuplicates=false, IsInvariantToNulls=true, IsInvariantToOrder=true, IsNullIfEmpty=true, MaxByteSize=8000)] 
public class MedianCalculator : IBinarySerialize { 
    private List<double> values; 
    public void Init() { 
        values = new List<double>(); 
    public void Accumulate(SqlDouble value) { 
        if (!value.IsNull) 
    public void Merge(MedianCalculator other) { 
    public SqlDouble Terminate() { 
        if (values == null || values.Count == 0) 
            return SqlDouble.Null; 
        return (values[(int)Math.Floor((values.Count - 1) / 2.0)] + values[(int)Math.Ceiling((values.Count - 1) / 2.0)]) / 2.0; 
    public void Read(BinaryReader r) { 
        int c = r.ReadInt32(); 
        values = new List<double>(c); 
        for (int i = 0; i < c; i++) 
    public void Write(BinaryWriter w) { 
        for (int i = 0; i < values.Count; i++) 
After deploying this aggregate, I try to run this query:

How to update a variable in Data Flow component of lookup has rows that don't find matches



I'm using SSIS 2008

I have a data flow that gets data from an Excel spreadsheet. It then does numerous lookups to my database to ensure that all the entities in the spreadsheet are present in my dimension tables. For each of these lookups, I redirect rows with "no matching entries" to be written to another spreadsheet which is then sent to an email address for action.

I have a Package scoped variable that I want to update if any of the lookups have rows with no matching entries. This is used later in the package to determine if it should proceed or not. So this variable only needs to be updated once if any of the lookups fail. 


What are the options for doing this? What is the best way?


Craig Bryden - Please mark correct answers

FullTextSqlQuery returns the right number of rows, but they have no data!


Hey all,

I'm running my first fulltextsqquery, using the function below - it seems to all execute fine and 24 rows get returned (query below) which sounds about right for all the people on my site. What's weird though is that all the columns in the results are empty string ("" or {}) - they definitely aren't empty in the user's profile - can anyone help me? Preferred email is a custom field and is mapped onto a People:PreferredEmail(Text) property in search admin - anything else i need to do?

 string query = string.Format("SELECT {0},{1} FROM SCOPE() WHERE \"scope\"='People'""PreferredEmail", "FirstName");
 public static ResultTableCollection RunFullTextSqlQuery(string sql, int rowLimit)

t-sql function returns only 3 rows


hi all,

the following function returns only 3 results, though it should return 7 result sets.

can someone please help me here?

thanks ahead.



ALTER PROCEDURE dbo.GetMissionsForBrowsing
    @Type int,
    @PageIndex INT,
    @NumRows INT,
    @LastItemPulled int

    Declare @startRowIndex INT;
    set @startRowIndex = (@PageIndex * @NumRows) + 1;

    With Entries as (
        SELECT ROW_NUMBER() OVER (ORDER BY Missions.Id ASC) as Row, 
               Missions.Id, Missions.SiteId,Missions.Uploader,Missions.TimeAdded,Missions.Subject,
			   Missions.Watched,Missions.Helped,Missions.Active,Users.Username, Sites.SiteName
        FROM Missions inner join Users
            ON Missions.Uploader = Users.Id 
			inner join Sites on Missions.SiteId = Sites.Id
        WHERE Missions.Type = @type
              AND (Missions.Visible = 1)

    SELECT Id, SiteId,Uploader,TimeAdded,Watched,Helped,Active,Username,Subject,SiteName
    FROM Entries
    WHERE Row between 
    @startRowIndex and @StartRowIndex+@NumRows-1


Copying rows using a dynamic SQL Command variable


I have a table with the Source TableName and Source ColumnName. I am trying to use SSIS to extract data from my source based on the tables and columns. I am using a Script Task to dynamically create the SELECT statement, no problems with that.

Next, I have a data flow task with a Source OLE DB Connection and the Data Access mode is using this dynamically created select statement. I am however unable to use this because this SELECT statement variable is not populated until runtime.

Basically I want to COPY ALL ROWS from the source to a destination. 

Is this possible? What is wrong with my assumptions? Any suggestions?


You are so wise...like a miniature budha covered in fur. -Anchorman

Duplicate rows occures in my query


i'm facing a problem to get a query. i have a query which have a simple join with another table.
plz download and exevute my sample db script to reproduce the problem (plz skip some errors during creating database):

1. plz execute first query :

select * from dbo.Reception

Comment : As u can see in the result, we have a single record in result Set.

2. then, plz execute second query :

select * from dbo.InsuranceTarrifs

Comment : This table have relation with Reception table via Reception.BasicInsuranceID and InsuranceTarrifs.InsuranceID fields.

3. nowm we want to query from both table and join together as follow :

select r.* from Reception r
 inner join InsuranceTarrifs it on r.BasicInsuranceID=it.InsuranceID

As u think like me, we expect get a single row in result set, but there is a 2 rows in result set which the second row as the same as the first one (duplicate row).

where is my problem and how to solve this ?


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