Complex query execution doesn't work and it doesn't report any error

Jun 1, 2012 at 5:46 AM

ok here's my script:

<?php
	//
	//header("Content-Type: text/xml");
	//
	$srv = 'SRVxxxx';
	$uid = 'xxxxx';
	$pwd = 'xxxxxxxx';
	$dbf = 'zzzazzaz';
	$par = array('uid' => $uid, 'pwd' => $pwd, 'database' => $dbf);
	//
	$con = sqlsrv_connect($srv, $par);
	//
	if ($con) {
		//
		$sql = "
		CREATE TABLE #temp(
			dt nvarchar(10),
			dc nvarchar(50),
			qt decimal
		)
		GO
		CREATE TABLE #RMGLCATES(
			rlt_numlista varchar(6),
			rlt_codsoc varchar(2),
			rlt_codforn varchar(12),
			rlt_numordine varchar(12),
			rlt_dtchiusura datetime
		)
		GO
		INSERT INTO #RMGLCATES (rlt_numlista, rlt_codsoc, rlt_codforn, rlt_numordine, rlt_dtchiusura)
		SELECT rlt_numlista, rlt_codsoc, rlt_codforn, rlt_numordine, rlt_dtchiusura
		FROM dbo.RMGLCATES
		WHERE dbo.RMGLCATES.rlt_codsoc = 'LJ' AND rlt_dtchiusura >= CAST('20120501' AS Date) AND rlt_dtchiusura < CAST('20120601' AS Date)
		GO
		WITH DateTable AS (
			SELECT CAST('20120501' AS Date) AS d
			UNION ALL
			SELECT DATEADD(dd, 1, d) AS Expr1
			FROM DateTable AS DateTable_2
			WHERE (DATEADD(dd, 1, d) < CAST('20120601' AS Date))
		)
		INSERT INTO #temp (dt, dc, qt)
		SELECT CONVERT(nvarchar(10), DateTable_1.d, 101) AS dt, dbo.BCCCATEGO.cat_descr AS dc, dbo.RMGLCARIG.rlr_totqtacontr AS qt
		FROM #RMGLCATES
		INNER JOIN dbo.RMGLCARIG ON #RMGLCATES.rlt_numlista = dbo.RMGLCARIG.rlr_numlista AND #RMGLCATES.rlt_codsoc = dbo.RMGLCARIG.rlr_codsoc AND #RMGLCATES.rlt_codforn = dbo.RMGLCARIG.rlr_codforn AND #RMGLCATES.rlt_numordine = dbo.RMGLCARIG.rlr_numordine
		INNER JOIN dbo.BANTARTIC ON dbo.RMGLCARIG.rlr_codart = dbo.BANTARTIC.bat_codart AND dbo.RMGLCARIG.rlr_codsoc = dbo.BANTARTIC.bat_codsoc
		INNER JOIN dbo.BCCCATEGO ON dbo.BANTARTIC.bat_codsoc = dbo.BCCCATEGO.cat_codsoc AND dbo.BANTARTIC.bat_catmerceologica = dbo.BCCCATEGO.cat_categoria
		RIGHT OUTER JOIN DateTable AS DateTable_1 ON CAST(#RMGLCATES.rlt_dtchiusura AS Date) = DateTable_1.d
		GO
		DECLARE @columns VARCHAR(8000)
		DECLARE @totalcolumns VARCHAR(8000)
		SELECT  @columns = COALESCE(@columns + ',[' + cast(dt as varchar) + ']','[' + cast(dt as varchar)+ ']'),
						@totalcolumns = COALESCE(@totalcolumns + ',SUM([' + cast(dt as varchar) + ']) as [' + cast(dt as varchar) + ']','SUM([' + cast(dt as varchar)+ ']) as [' + cast(dt as varchar)+ ']')
		FROM #temp
		GROUP BY dt
		DECLARE @query VARCHAR(8000)
		SET @query = '
		SELECT * FROM #temp PIVOT (SUM(qt) FOR [dt] IN (' + @columns + ')) AS p
		UNION
		SELECT ''@total'',' + @totalcolumns + ' FROM #temp PIVOT (SUM(qt) FOR [dt] IN (' + @columns + ')) AS t
		'
		EXECUTE(@query)
		GO
		DROP TABLE #RMGLCATES
		GO
		DROP TABLE #temp
		GO
		";
		//
		$stm = sqlsrv_query($con, $sql);
		if ($stm) {
			//
			while ($row = sqlsrv_fetch_array($stm, SQLSRV_FETCH_ASSOC)) {
				echo $row['dc'];
			}
			//
			sqlsrv_free_stmt($stm);
		} else { print_r( sqlsrv_errors(), true); }
		//
		sqlsrv_close($con);
	}
?>

please HELP ME! :)

Thanx,
Alessandro. 

Jun 1, 2012 at 5:49 AM

BTW, I cannot write a stored procedure because my user doesn't have the permission.

Jun 1, 2012 at 8:23 PM

Hi zerodefects74,

Could you please post your issue in our forums? We provide support for the driver in that location.

http://social.msdn.microsoft.com/Forums/en-US/sqldriverforphp/threads/

Thanks,

Jonathan