.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

Syntax for joining the results of two UDF

Posted By:      Posted Date: August 31, 2010    Points: 0   Category :Sql Server
I have a table UDF similar to proj.MyFunction(2010).  I would like to join the results from the select of each of the functions.  I tried a syntax like below but it does not work.  What is the correct syntax? proj.MyFunction(2010) (select fn1.ProjectID from proj.MyFunction(2010)) fn1 inner join (select fn2.ProjectID from proj.MyFunction(2010)) fn2 on fn1.ProjectID = fn2.ProjectID   Thanks, LLoyd

View Complete Post

More Related Resource Links

Need Syntax To Make Results of LINQ Union Return Non-Generic Type



I have the below SQL which works just fine:

SELECT     Message, CreateDate, AccountId, AlertTypeId
FROM       dbo.Alerts
SELECT     TOP (100) PERCENT Status, CreateDate, AccountId,
                          (SELECT     10 AS Expr1) AS AlertTypeId
FROM         dbo.StatusUpdates
WHERE AccountId = PassedInParameter

I have been going about trying to convert the above into a LINQ method and am having problems with return types.
As the above SQL illustrates, I am querying two distinct LINQ tables - Alerts and StatusUpdates -
and then attempting to perform a Union on them. In order to do the union in LINQ, I have to have agreeing types. So, I
am using "var" to make the results of "alerts" and "updates" generic before the union.  

public IList GetStatusUpdatesAsAlertsByAccountId<T>(Int32 accountId)
            using (WorkbookDataContext dc = _conn.GetCont

Any opinions on using Projection or explicit join syntax when joining entities in Linq to SQL?



I would like to know whether people use the join syntax OR projection when joining entities in LINQ to SQL queries. I have noticed that when using projection that a CROSS JOIN is in the generated SQL


var query =
from o in Orders
from  oi in o.OrderItems
where oi.OrderId == o.OrderId

generates SQL something like...

FROM [Customer].[Orders] AS [t0]
CROSS JOIN [Customer].[OrderItems] AS [t1]
WHERE ([t1].[OrderId] = [t0].[OrderId]) AND ([t1].[OrderId] = [t0].[OrderId])

whereas with explicit joins I get a clean inner join..

var query =
from o in Orders
join oi in OrderItems on o.OrderId equals oi.OrderId


FROM [Customer].[Orders] AS [t0]
INNER JOIN [Customer].[OrderItems] AS [t1] ON [t0].[OrderId] = [t1].[OrderId] 

I know using explicit joins over projection is frowned upon, but I am wary of those cross joins.

Just wondering what other folks preferences are when using joins in L2SQL.





sqlDataReader dos not get results


I am trying to run a stored proc from this function. When I run it thorugh the debugger I can see that myReader.HasRows = true and myReader.FieldCount =14.

But the control does not go in the loop while (myReader.Read()) where I am trying to read Version field returned by the stored proc.

Pls check me code and suggest correction. Thank You.

private static int Get_CaseInfo(string CaseNum)
            int Ver = 0;
            String myConn = ConfigurationManager.ConnectionStrings["DBConnectionString"].ToString();
            SqlConnection conn = new SqlConnection(myConn);
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = System.Data.CommandType.Text;

            SqlDataReader myReader = null;
            String stmt1 = "EXEC [REPORTS].[CaseInfoByCaseNo_Get] @CaseNo = '" + CaseNum + "'"; 
            cmd.CommandText = stmt1;
            cmd.Connection = conn;
                myReader = cmd.ExecuteReader();
                while (myReader.Read())
                    Ver = (int)myReader["Version"];

            catch (Exception)
            return Ver;         

displaying xsl results in browser


Hi guys,

How can I test what my xsl spits out in the browser, rather than just viewing the xsl itself?


How to display a count of search results with keywords in a label?


Sorry for a basic post here; I am very new to VB!

I am currently building a search page, which includes a textbox and two drop-down lists, a search button, and a datagrid. If results are found and displayed, I would like a label to display:

(e.g.) "24 results found for 'blue sky clouds' "

...where the integer represents the results count, and the keywords and/or drop-down list selections are included in the string.

If no results are found I would like the label to display:

(e.g.) "No results were found that match your selection. Please try again"

I would also like the label to be invisible until a search occurs.

Thanks in advance!

SharePoint Security: Trim SharePoint Search Results for Better Security


SharePoint search may return too much information, causing data security problems. Learn how to use the custom security trimmer to ensure users see only the documents they have permission to view.

Ashley Elenjickal, Pooja Harjani

MSDN Magazine July 2010

{ End Bracket }: Election Results Even Voters Can Trust


Surprisingly, cryptography can be applied to the electoral process to allow every individual voter to check the integrity of an election tally. Find out how here.

Josh Benaloh

MSDN Magazine June 2008

Share The Load: Report Visual Studio Team System Load Test Results Via A Configurable Web Site


This article discusses a new load test tool in Visual Studio 2005 Team System for performance and stress testing your Web sites, Web services, and other server components. Combined with its handy reporting capabilities, the load test tool provides some powerful options for sharing and managing test results.

Wen Ding

MSDN Magazine June 2006

Test Run: Five Ways to Emit Test Results as XML


The use of XML files in software testing has steadily increased over the past few years. Test case data, test harness configuration information, and test result data are now stored as XML. Recently I was writing some .

Dr. James McCaffrey

MSDN Magazine June 2006

{ End Bracket }: Joining the Team


It's been more than a year since my words last floated across the pages of MSDN®Magazine. As readers of my blog probably know, my life's been interesting the past 12 months.

Matt Pietrek

MSDN Magazine January 2005

T-SQL in Yukon: Powerful New T-SQL Syntax Gives SQL Server a Programmability Boost


The T-SQL language in the upcoming version of SQL Server will provide more power and flexibility than previous versions. Additions and enhancements include error handling with the TRY/CATCH construct, SNAPSHOT isolation, and WAITFOR enhancements. Also important are the BULK rowset provider, common table expressions, recursive queries, PIVOT and UNPIVOT operators, and much more. This article introduces these features so readers will be ready for the next version of SQL Server.

Itzik Ben-Gan

MSDN Magazine February 2004

Default values in SQL syntax / MVC


Hi guys, I have a little problem for which I need a little advice about.

I have a repository function called getList which retrieves certain information that gets posted to it from a form. My form has 6 fields, and a user can enter searvh criteria in any or all the fields. According to what he has entered, I must execute a SQL string to retrieve matching information.

I have the following:

public List<Client> getList(string clientSurname, string clientInitials, string clientName, string clientNumber, string clientReference, int divisionID, int clientStatus, int usrID)


var clientlist = from c in DB.Clients
                             where c.Surname.Contains(clientSurname)
                             && c.FullName.Contains(clientName)
                             && c.Initials.Contains(clientInitials)
                             && c.OldClientNo.Contains(clientNumber)
                             && c.Reference.Contains(clientReference)
                             && c.DivisionID == divisionID
                             && c.Status == clientStatus
                             && division.Contains(c.DivisionID)
                             orderby c.Surname,c.Initials

Syntax error (missing operator) in query expression

Good Day Gurus!

I have a Excel user application which has a user form (named 'Registo') that displays criteria and an image that has been entered in it's corresponding spreadsheet. This works the way it should.  There's also the ability to search the spreadsheet via a form (by clicking 'Pesquisar' button) this opens a search form. However, I having a bit of a problem with it. When I try to search for something it basically doesn't do anything at all. It just sits there. So I tried to debug it and I think I'm having a problem with either the JET db engine or somethign with teh query or maybe I don't have the correct reference.

I 'borrowed' this Excel application from another forum because it's exactly what I'm looking for! However, I suck at vb.

So I was hoping somebody could take a look at the code and see if I'm missing something?  I'm kinda' desparate to get this working because I'm been trying to figure it out for days and I'm running out of time.  Cry
Option Explicit

'constantes para auxiliar na verificação do código
Private Const Ascendente As Byte = 0

How can I prioritise search results to show where document metadata has a hit first and then where t


When my users run a search against a site with a bunch of document libraries they will often search for terms that are in the title of the doc, if they do know the title they will put that in. What I would like to do is have the results ordered such that if any hits are on the document metadata (eg Title) then they are presented first and any results that are from hits on the content of the documents are presented later.

So, if they get the title spot on in their search query that document will appear first .... if they don't get it right then the likelyhood is that the title is something like what they entered so similar titled docs are presented first and then those with matching content ....

How can I achieve this?


No Results matching your search were found. WSS 3.0


I've got one sharepoint 3.0 site, which is a hostNamedSiteCollection which won't return any results when we search it - searching the entire site or a single list both produce the same "no results matching your search were found".
The site uses Windows Authentication.  We have no Alternate Access Mappings set up. It is regular HTTP
I've added test content to other sites housed in the same Web Application and searched - that new data is appearing in the results.
I did a backup using STSADM and restored the site to my dev server (as both a hostNamedSiteCollection & a regular site collection) - searches return results just fine.
I deleted the site from the prod server and restored it (into a different content database even!) from a backup  I made with STSADM - no results are returned.
I restored the site to the same web application as a Standard site (http://webapp/sites/sitename) (NOT a host named site collection) and results ARE returned.

I've looked at the trace logs & application logs on the server, but can't find any errors that I can tell might be related..

This is the only site I've ever seen have this problem.  We have plenty of other hostNamedSiteCollections and search is working fine on them.


Exclude the author name from the search results



How can i prevent author's name to appear in search results. shows many results when i search the "author.name".

I want, MOSS 2007 Search get results from the content only the user sees.


FAST Search for SP2010 scope setting works but not getting results.



I am trying this from last two days , but it does not work. I have created two scopes for FAST Search Query SSA , they appear in search center , but I am not getting any results back. I see them being fired in the FAST query log.

First one is a simple one:

New-SPEnterpriseSearchQueryScope -SearchApplication "FAST Query SSA" -Name DCO -Description "All DCO Documents" -DisplayInAdminUI 1 -ExtendedSearchFilter "ContentSource:DCO"

Content source is a managed property. and i can just query "contentsource:dco" from the text box which gives me results.

When I fire the query i see it in FAST Search query log

"GET /cgi-bin/search?qtf_keyword:context=ssgid%3a%3a4d020606-8532-45d8-b09a-db11283cccb4%7cSPS-Location%3a%3a%7cSPS-Responsibility%3a%2c%3a%7c&rpf_navigation:hits=50&rpf_navigation:enabled=True&spell=suggest&qtf_parsekw:timezone=10&type=kwall&qtf_teaser:dynlength=185&resubmitflags=8192&language=en&query=CASHIERING&sortby=%2bdefault&qtf_lemmatize=True&offset=0&version=

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