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

Post New Web Links

Why does the engine ignore my Cross Apply?

Posted By:      Posted Date: September 16, 2010    Points: 0   Category :Sql Server
I'm a beginner attempting to understand cross apply. Suppose I have a Numbers table with only one column: DECLARE @Nums Table(Num int) INSERT INTO @Nums (Num) Values (5) INSERT INTO @Nums (Num) Values (6) INSERT INTO @Nums (Num) Values (7) The following produces two columns of output (in SSMS Express 2005). SELECT  * FROM @Nums         CROSS APPLY         (             SELECT  Num * 2 as Doubled         ) as NewTable Now let's change the SELECT * to  SELECT Num (but I would expect the same result since that's the only column in the table). SELECT  Num FROM @Nums         CROSS APPLY         (             SELECT  Num * 2 as Doubled         ) as NewTable But instead I only get one column of output - the engine simply ignores my Cross Apply clause. Why?    After all, I'm told that using SELECT * is bad practice because I should name my columns explicitly. But in this case I'm getting punished for naming my columns explicitly?  I must be doing something wrong, but I don't know what it is.

View Complete Post

More Related Resource Links

XQUERY using Cross Apply and Outer Apply

Trying to shred XML into relational table rows and can do it for one set of node values .. having difficulty creating a pair of columns. The relevant part of the XML looks like this ... <ns:Coverage>      <ns:CoverageCd>cvalue1</ns:CoverageCd>      <ns:Form>           <ns:FormNumber>fvalue1</ns:FormNumber>      </ns:Form> </ns:Coverage> I can successfully navigate through the XML and pull back a set of rows for all values of CoverageCd. Now I'm trying to add a column to each output row for the FormNumber value associated with the CoverageCd value. However; FormNumber is not always present. I found an example where using 'Outer Apply' can account for that, but it's not working for me. Here's my XQuery: WITH XMLNAMESPACES('http://www.mycompanyname.com/ACORD1.11.0/Policy_1.0/xml' AS "ns") select cov.i.value('.', 'varchar(30)')   [Coverage],          form.i.value('.', 'varchar(30)')  [FormNumber] from  dbo.mytablename        cross apply XMLcolumn.nodes('.//ns:Coverage/ns:CoverageCd') as cov(i)        outer apply cov.i.nodes('.//ns:Coverage/ns:Form/ns:FormNumber) as form(


table1 has a full text index. I want to run multiple freetexttable searches against it in a single query, but the two attempts i have fail. any help would be appreciated, thanks! CREATE FUNCTION fnt_FullTextSearch ( @s NVARCHAR(4000) ) RETURNS TABLE AS RETURN ( SELECT [key], [rank] FROM FREETEXTTABLE(table1, *, @s) ) DECLARE @terms TABLE ( term VARCHAR(MAX) ) INSERT INTO @terms VALUES ( 'flu' ) INSERT INTO @terms VALUES ( 'acid' ) --The inline function "..." cannot take correlated parameters or subqueries -- because it uses a full-text operator. SELECT ft.[key], ft.[rank] FROM @terms CROSS APPLY fnt_FullTextSearch(term) ft --syntax error on term SELECT ft.[key], ft.[rank] FROM @terms CROSS APPLY FREETEXTTABLE(table1, *, term)

problem with cross apply query

Hey guys. This is one of the queries pasted from BOL. I'm having problems excuting this query. The problem lies in the CROSS APPLY part. When I copy this query and run it in SSMS, it gives me an error saying 'Incorrect syntax near .' It doesn't like the qs.sql_handle part. If I remove that and pass the actual handle in for some query, it works. Can someone please tell me what I'm doing wrong?????? Also, I've sp1 installed on my SQL Server 2005 Enterprise, just in case if this matters. Below is the query pasted which is giving me problems. Thank you. SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time], SUBSTRING(st.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st ORDER BY total_worker_time/execution_count DESC;

CROSS APPLY doesn't work in some databases

I've run across a weird error trying to use CROSS APPLY. Running SQL2005SP2 developer edition on XP2 (but same error occurs on Enterprise running on W2K3).A simple CROSS APPLY always works when run from master, but doesn't work when run from most (but not all) of my other user databases. I can't figure out what could be causing the error, or is there some reason it would only work when the current database is master?Here is the simplest testcase I can come up with:select a.spid,b.text from master..sysprocesses a cross apply ::fn_get_sql(a.sql_handle) bIf this is run while the current database is master, it returns spid and SQL without a problem. If it's run in the other SQL-delivered databases (model, msdb, tempb), it also works fine. However, if it's run in all but one of the user databases, I get back an error:Msg 102, Level 15, State 1, Line 1Incorrect syntax near 'a'.I'm stumped on how to troubleshoot this. There is no difference in the query; the only difference is the current database. The databases are all owned by sa. I get the same results with sys.sysprocesses instead of master..sysprocesses.As another datapoint, the employee/department example in the BOL for CROSS APPLY exhibits the same behavior; it works fine if run with master as the current database (regardless of where the tables are created), it fails withMsg 102, Level 15, State 1, Line 3Incorrect synt

Inline table valued function, full outer join and cross apply

Good afternoon, I'm experiencing quite strange issue with our T-SQL code. We have inline table-valued function which is using full outer join on 1 = 1. The reason why we do it this way is - we need to combine one row from 2 different result sets either of them can have either 0 or 1 row. The strange thing, that if we have the situation when both row-sets are empty, function does not return the row (as expected). Although, select which uses this function with cross apply "uses" the result from the previous row. Below is the simplified example. First select with ID = 2 predicate does not return any rows. Although if I remove this filter, it re-uses Value2 from the previous row. create FUNCTION dbo.Func(@Id int) RETURNS TABLE AS RETURN ( with CTE1(Value1) as ( select 'Value1' where @ID = 1 ) ,CTE2(Value2) as ( select 'Value2' from CTE1 ) select CTE1.Value1, CTE2.Value2 from CTE1 full outer join CTE2 on 1 = 1 ) go select * from dbo.Func(2) go create table dbo.TestTable ( ID int not null ) go insert into dbo.TestTable values(1), (2) go select t.ID, f.Value1, f.Value2 from dbo.TestTable t cross apply dbo.Func(t.ID) f where t.ID = 2 go select t.ID, f.Value1, f.Value2 from dbo.TestTable t cross apply dbo.Func(t.ID) f order by t.id go Our SQL version is: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) &nb

Using CROSS APPLY in a data source query causes ARITHABORT error


I have an issue where the following query as an OLEDB data source throws the following error:

SELECT XmlNode.value('SomeValue[1]', 'varchar(10)') as SomeValue

FROM (SELECT CAST('<TestXML><SomeValue>123</SomeValue></TestXML>' AS XML

group by clause in cross apply



I am using cross apply against 2 huge tables and I am trying to show the number of customers customers in 3 of the countries US, Canada, UK. When I use my cross apply how do I show the count of customers in the resultset. I have to do a cross apply becoz I do not have fields that have unique data that I can join on.


Product UK Customer US Customer CA Customer

A          1000              2000             3000

B           0                  500               2000

C         1200              2250              0

D         3400              5650              2390  



Cross Apply



  I have created the following funcation :

 ALTER FUNCTION [dbo].[fnTopNByCategory]

(@TopN int, @CategoryID int)


Cross Apply


How do I preserve __viewstate between cross domain posts or how do I get a __viewstate of a Remote S


Here is what I want to do:
I have a local site in which I want to display data from a remote site
Lets say: I want to display data of http://www.abc.com/Default.aspx on my localhost

Now Default.aspx requires some post-data which it sends to itself. Thus the __viewstate and __eventvalidation are posted back to it by itself. I want to directly post the data to Default.aspx from localhost without opening Default.aspx and display the Default.aspx's response on my localhost.

The WebApp on http://www.abc.com is configured for __eventvalidation i.e. I cannot post-data to it from my localhost without getting a __viewstate from it. I also have to post the current __viewstate of http://www.abc.com/Default.aspx to itself

How can I accomplish it?

Stop Sorting Cross Tab Report Header


Dear, I want to stop sorting in cross tab report header. And also want to sort in specific order. How can i do that? Please help me. Its urgent


Cloud Storage: Fueling Your Application's Engine with Windows Azure Storage


Yes, you can run background processes in the cloud. Kevin Hoffman and Nate Dudek use a shopping cart example to demonstrate how to build an application engine as well as implement asynchronous messaging and processing using Azure Storage.

Kevin Hoffman, Nathan Dudek

MSDN Magazine January 2010

Extreme ASP.NET: Search Engine Optimization with ASP.NET 4.0, Visual Studio 2010 and IIS7


In this article, the author takes a look at SEO practices that the reader can apply when using the latest Microsoft Web technologies.

Scott Allen

MSDN Magazine September 2009

SOA Simplified: Service Virtualization With The Managed Services Engine


By using Managed Services Engine service virtualization technology, you can begin participating in the cloud by simply configuring virtual service endpoints and operations that integrate with the .NET Service Bus.

Aaron Skonnard

MSDN Magazine May 2009

Foundations: Easily Apply Transactions To Services


Managing state and error recovery using transactions is the topic of this month's installment of Foundations.

Juval Lowy

MSDN Magazine January 2009

Toolbox: Cross Browser Testing, Mock Objects, and Raymond Chen


This month test your web site on many platforms and browsers without setting up a test environment, use mock objects for unit testing, and visit Raymond Chen.

Scott Mitchell

MSDN Magazine June 2008

Unit Testing: Apply Test-Driven Development to your Database Projects


Jamie Laflen extols the benefits of TDD when applied to database development-and supplies some useful techniques along the way.

Jamie Laflen

MSDN Magazine Launch 2008

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