August 2007 - Posts
Did you know that Microsofts new operating system will only allow you to install SSRS's full features on its Business and Ultimate editions? Yep. Vista does not allow users access to a little itsy feature in IIS called Windows Authentification. So while your Vista Premium machine might have IIS, it has this little tiny thing excluded from it. Microsofts explaination is that IIS 7 on Vista Premium is for the Hobyist web developer.
I found out the hard way; I asked my Dell rep when I baught my PC if the Vista Premium had IIS, and he said "YES!" Too bad he didn't know that Microsoft decided to exclude Windows Authentification from some versions. So much for the Vista Premium purchase; and Dell didn't help me at all upgrade to Ultimate. I had to purchase a Vista Ultimate upgrade on my own from some online retailer. Yikes was my wife anoyed at the cost of my being an IT geek. On the bright side I can now play Texas Hold Em. (Deep sarcasm.)
Let this be a warning to those of you who want to develop and install all of SSRS from home on your own PC.
Here are a couple of links that I found on the subject:
http://www.iis.net/articles/view.aspx/IIS7/Deploy-an-IIS7-Server/Installing-IIS7/IIS7-Features-and-Windows-Vista-Editions
http://support.microsoft.com/default.aspx/kb/920201/en-us
If you are like me, SQL Server is a breeze to write a Stored Procedure against and Oracle isn't as intuitive. I had a report that simply needed to do the following:
1. Run a query in Oracle.
2. Return the data to Reporting Services so that it could report on it.
3. I couldn't link through SQL Server.
Its really simple code, but different than the way we do it on the SQL
Server side. In SQL Server you just do a Select at the end of your SP
and it gets presented to Reporting Services, but Oracle needs to use a
Cursor. I scoured the internet real quick to find an example SP like I can find for all kinds of things in regards to SQL Server, but not Oracle.
So, eventually I figured out I needed to pass SSRS a SYS_REFCURSOR and then figured out this simple little SP.
Anyway, if this saves someone 20 minutes of googling, I thought I'd post it. There is nothing revolutionary about it.
CREATE OR REPLACE PROCEDURE SCHEMA_NAME.USP_RPT_SP_NAME (PARAM1 IN DATE, PARAM2 IN DATE, p_recordset1 OUT SYS_REFCURSOR) AS
BEGIN
OPEN p_recordset1 FOR
SELECT * from TABLE_NAME
WHERE ADDDTTM between PARAM1 and PARAM2;
END USP_RPT_SP_NAME;
/
I haven't posted anything here yet on this blog, so I thought I'd pull some lesson I learned a while back and use it as a post. If it helps you; great, if not, no worries.
The problem I tried to solve involved 5 or 6 Oracle environments that need to have data pulled from them into SQL Server. The user knows what environment they want to query off of, so this SP gives them the flexibility they need. I actually used this as a SP for a SSRS report. I hide the Oracle Parameter in SSRS so that only the users with technical experience could change this parameter, but you can make it a live parameter too.
-- =============================================
-- Author: Keith Ramsey
-- Description: Dynamic Environment SP to report from any linked server passed through your parameter.
-- =============================================
CREATE PROCEDURE [dbo].[uspDynamicLinkServerSP]
@ORACLE_ENVIRONMENT VARCHAR (5)
AS
BEGIN
-- Create variables for the view that you read in ORACLE, for
-- the run of the SQL Statement and for the com SQL String.
DECLARE @ORACLE_VIEWNAME VARCHAR (45)
DECLARE @SQLSTRING VARCHAR (1000)
DECLARE @S2 NVARCHAR(1000)
Set @Oracle_VIEWNAME = '..IMSV7.tableORviewName'
SET @SQLSTRING = 'SELECT * from '
SET @SQLSTRING = @SQLSTRING + @ORACLE_ENVIRONMENT + @ORACLE_VIEWNAME
SELECT @S2 = CAST(@SQLSTRING AS NVARCHAR(1000))
execute sp_executesql @S2
END