SQL Query working in management studio but not in

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL Query working in management studio but not in

Author

Topic

rohit04413
Yak Posting Veteran

72 Posts

Posted - 2011-07-15 : 02:28:40

I have the following query:


; with tblASmax as
(
	select	serialnumber, runlocal,process,result,datein,operatorid
	from	(
			select	serialnumber,datein,runlocal,process,result,operatorid, row_no = row_number() over (partition by process,serialnumber order by runlocal desc)
			from	tblFA
		) m  	where	m.row_no	= 1 and process = '12'
),
cte AS
(
	select	productiono,partcode, serial = firstsr, lastsr
	from	tblpo where productiono = '5009486'

	union all

	select	productiono,partcode, serial = serial + 1, lastsr
	from	cte
	where	serial + 1 <= lastsr
)

select cte.productiono,partcode,datein,cte.serial,result,operatorid

FROM tblasmax right JOIN cte ON cte.serial = tblasmax.serialnumber order by serial
option (MAXRECURSION 10000)

This query works fine in MS SQL Server Management Studio, But When I run this in visual studio 2008 in a webform in gridview, it gives error. screenshot is here: [url]http://postimage.org/image/1i0n9f0dg/[/url]

Please help..

Sachin.Nand

2937 Posts

Posted - 2011-07-15 : 04:44:47

Looks like SqlDatasource does not like the CTE syntax.
Is it possible to convert the query to a stored procedure and then set it in the sqldatasource ?

PBUH

rohit04413
Yak Posting Veteran

72 Posts

Posted - 2011-07-15 : 04:50:56

quote:
Originally posted by Sachin.Nand

Looks like SqlDatasource does not like the CTE syntax.
Is it possible to convert the query to a stored procedure and then set it in the sqldatasource ?

PBUH

OK Now i'll try to do it using store procedure.
But i have also found that if i remove "Union All" from this, it works ok.
It'll be really helpful if it could work as it is.

rohit04413
Yak Posting Veteran

72 Posts

Posted - 2011-07-15 : 04:57:13

quote:
Originally posted by Sachin.Nand

Looks like SqlDatasource does not like the CTE syntax.
Is it possible to convert the query to a stored procedure and then set it in the sqldatasource ?

PBUH

Bad Luck...
Even using store procedure, this is showing same error
please help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources