note 84307 added to function.mssql-connect

For those trying to connect PHP 5.2 to a 2005 Microsoft SQL Server Analysis Services (SSAS) cube and execute MDX queries, you have to establish a link via a trusted connection from the Database Service to SSAS using a stored procedure, then execute the stored procedure via PHP. Here is an example stored procedure that retrieves records from the Adventure Works sample cube that ships with SSAS. From SQL Server Query Analyzer, you could test it as: exec testMDX From PHP, you would execute something like the following: $resultset = mssql_query("exec testMDX",$res_id); then loop thorugh the result set. -- STORED PROCEDURE BEGIN set ANSI_NULLS ON -- Must be enabled at time Stored Proc is created set QUOTED_IDENTIFIER ON -- Must be enabled at time Stored Proc is created GO Create Procedure [dbo].[testMDX] as BEGIN SET ANSI_WARNINGS ON: -- Must be enabled SET ANSI_NULLS ON; -- Must be enabled Declare @SQL varchar(1200), -- Variable to hold SQL query @MDX varchar (1000), -- Variable to hold MDX query ; -- Establish a link to Analysis Server exec sp_addlinkedserver @server='linked_olap', -- Alias used to reference the link @srvproduct='', -- Not used @provider='MSOLAP.3', -- OLAP driver @datasrc='servername', -- Database server name @catalog='Adventure Works DW Standard Edition' -- Database name ; -- Analysis Server requires a TRUSTED connection exec sp_addlinkedsrvlogin @rmtsrvname = 'linked_olap', -- Alias used to reference the link @useself = 'false', -- Use own credentials @locallogin = NULL, -- Apply to all local logins @rmtuser = 'domain\username', -- Remote user name @rmtpassword = 'xyz123' -- Remote user password ; -- Create a temporary table that will be used to hold the MDX output create table #temp_table (column1 text null, column2 text null); -- Setup a string to hold the MDX so that the precompiler does not try to validate the syntax SET @MDX = 'SELECT [Product].[Category].members ON ROWS, {Measures.[Internet Order Count]} ON COLUMNS FROM [Adventure Works] ' ; -- Setup a string to insert the MDX results into the temporary table SET @SQL = 'Insert into #temp_table SELECT * FROM OpenQuery(linked_olap,'''+@MDX+''')'; -- Execute the SQL and remote MDX query EXEC (@SQL) ; -- Select the results from the temporary table to return to the calling program Select column1, column2 from #temp_table ; -- Drop the temporary table drop table #temp_table; -- Release the TRUSTED connection exec sp_droplinkedsrvlogin 'linked_olap', NULL ; -- Release the link to the Analysis Server exec sp_dropserver 'linked_olap' ; END -- STORED PROCEDURE END ---- Server IP: 69.147.83.197 Probable Submitter: 67.94.168.45 ---- Manual Page -- http://www.php.net/manual/en/function.mssql-connect.php Edit -- https://master.php.net/note/edit/84307 Del: integrated -- https://master.php.net/note/delete/84307/integrated Del: useless -- https://master.php.net/note/delete/84307/useless Del: bad code -- https://master.php.net/note/delete/84307/bad+code Del: spam -- https://master.php.net/note/delete/84307/spam Del: non-english -- https://master.php.net/note/delete/84307/non-english Del: in docs -- https://master.php.net/note/delete/84307/in+docs Del: other reasons-- https://master.php.net/note/delete/84307 Reject -- https://master.php.net/note/reject/84307 Search -- https://master.php.net/manage/user-notes.php