.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

Returning rows not found in inner query

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

View Complete Post

More Related Resource Links

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.

Search query returning 0 results

I'm using a query which returns 0 results but was working some weeks ago.  I checked the code and nothing has changed since then.  I created a Search Center Site on another port and using the same terms I get many results there.  I checked my scopes and they are all right. Nothing changed there either. Could someone help me figure out what may be the problem in my query.  Or maybe it's my Scopes/Content Sources configuration. SELECT URL, FileName, Title, Description, Rank, Size, HitHighlightedSummary, Path FROM SCOPE() WHERE freetext(defaultproperties, ' +légumes')  AND ( "scope" = 'Scope - My Portal - All Site - fr'  )    ORDER BY Rank DESCDaniel Siconnelli There is neither good nor evil... only mogwais and gremlins

T-sql returning a specified # of rows.

I dont know why this works but it does except I cannot execute it from the Dataset page.   It give me an error that @Count needs to be defined or Set is not valid Set  ROWCOUNT @Count  SELECT Prodid from Prod @Count is a parameter and if I set it to 100 I get the 1st 100 rows, which is what I want.  If I can run it from the Preview tab and it works why can't I run it from the Dataset tab. Is there a better/correct way to structure the query  

Query Sharepoint list, rich text columns returning HTML tags

Hi! So I have an issue that maybe someone of you have encountered as well. I am querying a Sharepoint list and creating a report from the data collected from this list. This all works fine except that one of the columns in this list is of the type "Multiple lines of text" and also supports "rich text" with different fonts, sizes and so on. This has the effect that the text returned from this column is wrapped in HTML tags that specifies how the text should be formatted. Naturally I do not want this HTML code to appear in my report so for now I have used custom code to do a string.replace and replaced for example <div></div> and <br> with "". However, this column also has url references to documents and these links shows up like this "/servername/site/subsite/Gemensamma%20dokument/Mwh.docx">http://servername/site/subsite/Gemensamma%20dokument/Mwh.docx There is some sort of double reference to the document that I guess like the HTML tags are embedded in the answer from the rich text column and this is not so easy to do a string replace on because the url varies with the different document and document names. My question really is if anyone of you have done a similar report that I am doing, and have encountered this fenomen with this sharepoint column and how you did to solve this? I have tried to creat

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

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,




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


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)

Table header should remain visible when no rows found ?


Hi ,

Can any one shade some light on achieving "Table header should remain visible when no rows found ?

Rajkumar Yelugu

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.






Select * query not returning all data in a row.


I am using SQL Server 2008 and am new to databases so this may be an obvious one to others.  I have setup a database and have 2 rows in a table which I am trying to read.  If I write a query in SQL Server it works fine, but the exact same query via ODBC connection only returns some of the of data in the affected rows.   The data not being returned is rather large (text field +1000 characters) but even if I create a row with just 1,2,3,4, (one in each column) it still only returns some of the data. 

SELECT * FROM [database].[dbo].[table] WHERE [station_name] = 'XXXXX'

Expected return XXXXX 1 3 5 10-11-2010  (where XXXXX is station name from above)

Actual return XXXXX (null) (null) 5 10-11-2010

The server the database resides on is administrated by IT so I have no idea what settings it has.  I had everything running in a MySQL database and my boss wanted it in MSSQL on the new server so I am trying to convert. This is the last thing that doesn't work.

Selecting Result from Content Query Wb Part gets Webpage cannot be found Error

I have MOSS 2007 running on Windows Server 2008.

I have added a custom List to a MOSS 2007 site sollection. The List works fine.

I have added a new webpage to the site collection Home page that includes a Content Query Web Part. When the Content Query WP is configured it returns items from the custom List as expected. But when I select one of the result items returned by the Content Query WP, the Browser says "The webpage cannot be found."

Why doesn't the Content Query WP result navigate to a page that displays the List item selected from the results list?

I have 2 items returned in the CQWP results list and both generate the same error when selected.

If I go to the custom List, the items display OK when selected.

The URLs generating the error looks like:

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

The error is: Method 'ProxyNamespace.ServiceNow_GetMetric.query' not found..


Hi guys -

I am working on a project where I need to call a WebService Method via the WebService Task in SSIS, and I am getting below error . The content of the WDSL file is also attached. Has anyone got this kind of error? I use BIDS with SQL Service 2008/Windows XP professional.

Thanks for your help


"[Web Service Task] Error: An error occurred with the following error message: Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException:
Could not execute the Web method. The error is: Method 'ProxyNamespace.ServiceNow_GetMetric.query' not found..

   at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebMethodInvokerProxy.InvokeMethod(DTSWebMethodInfo methodInfo, String serviceName, Object connection)
   at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTaskUtil.Invoke(DTSWebMethodInfo methodInfo, String serviceName, Object connection, VariableDispenser taskVariableDispenser)
   at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTask.executeThread()". 


WDSL File content:

<?xml version="1.0" encoding="UTF-8"?>

<wsdl:definitions targetNamespace="http://www.service-now.com" xmlns:tns="http:

Linq Query Returning Distinct


This is my query 

    Dim s_Person = From Person In db.People Join ProspectStatus In db.ProspectStatus

                       On Person.Id Equals ProspectStatus.Prospect_ID

                       Where (Person.Organization_ID = s_Organization_ID)

                       Select Person

I only want to return DISTINCT values of Person.ID.  Right now, it returns the same person multiple times because there are several entries in the status table.

Any ideas?

    Dim s_Person = From Person In db.People Join ProspectStatus In db.ProspectStatus
                       On Person.Id Equals ProspectStatus.Prospect_ID

Cannot find execute any query, stored procedure not found even if it is there


Hey guys,

I am getting frustrated with this problem, I dont know what i did, but now I cannot execute any stored procedured when I could last time.

When I use my asp.net application to run the query, it finds the stored procedure but when I execute it is sql management studio it says it cannot find the stored procedure even though it is there.

I tried to execute other procedures and the samething happens. Even when I try a simple query it says it cannot find the table

I could execute the query if i placed Use [databasename] in front, but even with this, I cannot execute stored procedures.

does any1 know how to fix this?

Ju Lian

Having issues with Query parameters not returning my filter value when specifying >=


SSRS 2008 w SP2
VB.Net to pass the parameters
Report Viewer on a form setup for Server Report

I am currently creating a SSRS Report and having problems with my query filters. Basically I am trying to pass 2 values to filter (there are actually 4 range values that can be used to filter the report, all of which work the same way). The parameter values are passed to the server report through an exe with a seperate front end form for the user to specifiy their filters. The report has a Text Query for the Dataset using ODBC connection and unnamed parameters. For the Range parameter I am doing as "WHERE ord_no >= param.from AND ord_no <= param.to" in the dataset query. The paramaters are setup and in the correct order. The problem is that whatever "param.from" value I use, It never shows in the report. When I catch the query from Profiler and run it in Sql Management Studio, it returns the results correctly.

For example:
My filter range would be for orders number 60 through 62; When displaying the SSRS report, orders number 61 and 62 show, but 60 does not (it seems to always return the results as if it was WHERE ord_no > param.from AND ord_no <= param.to)... If I catch the query from Profiler and run it Management Studio, the results include orders 60, 61, and 62 correctly like I would expect to see on display o

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