Extension method must be defined in a non-generic static class

Goal 

You are creating a c# or vb.net program and when you try to compile you get an "Extension method must be defined in a non-generic static class". This happens to you, for instance if you are creating a windows service in c# and you just keep adding voids and functions to you main class in the namespace of the project.  It can be any type of project I don’t mean to pick on windows services.

Environment

Windows 7 64 bit and Visual Studio 2010.

Issue

            In my case I added many public static functions to my main class which is of course not static. Static voids or functions need to belong to a static class.            

 

Resolution                                             

            Add a class to your project. Make the class you create a public static class. Cut and paste your static voids and functions in.  Reference your voids and subs using the syntax: ClassName.FuntionorVoid();

            For example in my project I have a bunch of c# functions that do vb.net functions like right. I created a public static class called DataChecks. The syntax to call it from my main class is below:

         fields[7] = "0." + DataChecks.Right("00" + fields[7].ToString().Trim(), 2);

                                      

            Because it is public and static you can just reference it anywhere in the namespace using classname.voidorfunction. If it were not static public you would need to:

 

            ClassName myname = new ClassName();

            Myname.voidoffunction();

 
         Either way, the moral of the lesson is not to mix statics and non-statics.
 

Conclusion 

 

If you are like me, it is easy to want to get a project done quickly and forget some basic tenets, like you can’t put statics in a regular class. This will cost you debug time. You should break out your project in meaningful classes anyway, its just good practice.

 

 

 

Point in Time Restore to a New Database in SQL 2008 R2

Goal 

To Restore a Database to a point in time and also to a new Database. Restore of a database is simple, restore of a transaction log is simple also, but restore to a point of time and to a new database takes some doing. It is not well documented either, or better put there are so many parameters to the Restore statement that it gets lost in translation.

Environment

The server was 2008 R2. SQL server was 2008 R2.

Issue

            A client contacted me and said that he had deleted a batch in an accounting system that has SQL Server as a back end.  I found that we set up the backup plan to do a full backup in the evening and a transaction log backup from 7 AM to 7 PM every 4 hours.  The client informed me that the batch was deleted around 10:30.  
            We did not want to restore the entire database. Too many things happened since then and he didn’t realize the batch was deleted till late in the day. The plan was to restore the database to a new database and copy the batch in from the tables using transact sql.
            To do point in time you have to use Trasact-SQL Statements. You cannot do it through the interface. The issue was to get the proper syntax to restore to a new database and a point in time. I also had issues figuring the proper syntax for the with StopAt date that determines when the restore stops.

Resolution

           To restore to a new database you use a MOVE statement for each file contained in the original database.  You do the MOVE in the restore of the last full backup.  The syntax is
WITH MOVE ‘Name of Old File in SQL’ To ‘Name and Path of New Database File’.  At minimum
You would have a mdf and a log:
WITH MOVE ‘Database_Data’ To ‘C:\...\NewDatabase.MDF’,
            MOVE ‘Database_Log’ To ‘C:\...\NewDatabase.LDF’
 Notice the MOVE’s are comma delimited.
            To Restore to a point in time you are usually restoring a full backup then several log files till you reach the log file with your cut off time.  To do that you must do the full backup restore WITH NORECOVERY (keep off line), each full transaction log must be WITH NORECOVERY, and the final transaction log WITH RECOVERY (bring whole thing back on line).
            Finally the last Transaction log needs to have a way to tell the backup to stop restoring after a certain time in the log. You do that with a WITH STOPAT=’Date and time’ statement.
I had problems that my restore was restoring the full backup of the last transaction file. It seemed like it was ignoring my date. I put syntax like:
With STOPAT = '08/10/2012 10:22:00'
And many other date syntax. All seemed to ignore my date.
Finally I did a select GetDate() and looked at how SQL formatted the date:
'YYYY-MM-DD HH:MM:SS.MMM'
Once I changed my date to format just like SQL did in Enterprise Manager, the stop at started working.
See my full restore statements below:

RESTORE DATABASE NewDatabase

   FROM DISK = 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\OldDatabase\OldDatabase_backup_2012_08_10_020001_7099795.BAK'

   WITH

   MOVE 'OldDatabase_Data' TO 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\NewDatabase.mdf',

   MOVE 'OldDatabase_Log' TO 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\NewDatabase.ldf',

   NORECOVERY

GO

RESTORE LOG GetBatch FROM DISK = 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\OldDatabase\OldDatabase_backup_2012_08_10_070000_8603788.TRN'   

WITH NORECOVERY

GO

RESTORE LOG GetBatch FROM DISK = 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\OldDatabase\OldDatabase_backup_2012_08_10_110000_8897061.TRN'

With STOPAT = N'2012-08-10 10:22:00.000'

, RECOVERY

GO

Conclusion 

Restore to a new database to a point in time is possible as using the Move and With statements. You also need to keep your STOPAT date formatted properly.

Subscription Emails TO Field Greyed out in SSRS 2008

Goal 

You need your users to be able to create Subscriptions to SSRS reports in Report Manager. They need to enter a list of email addresses in the to fields separated by ;. You have just upgraded from 2005 SSRS to 2008. This functionality goes away by default.  

Environment

SSRS 2008 R2,  IIS 7.0.

Issue

                Default behavior out of box is that users with browser rights can setup subscriptions, but they To box is greyed out. Also the To field is set to your windows user id. It will then paste on your @company.com based on your Active Directory. Users want to be able to send to maybe their yahoo account or other users. Maybe multiple users just like in 2005.   

            In 2008 The user has a greyed out To: field as in the image below.

Resolution

You just need to change a setting in the RSReportServer.config file.  To find this file Navigate to the ReportServer Folder for SQL Server. Under it will be the config file.  Inside the file search for the SendEmailtoUserAlias Entry. Change True to False in that Tag. You may have to do an iisreset but I have never had to. That’s it. Nice and simple.

Conclusion 

SSRS has default settings that do not allow users to enter emails in subscriptions free text as it did in 2005. You have to change a config file to get it to do that.

Issues installing 32 bit SSRS on 64 Bit Windows 2008 Server

Goal 

You are moving an instance of SQL from a 2003 32 Bit server to 2008 server 64 Bit Server.  

     

Environment

2008 Server 64 bit SP 2, SQL Server 2005 32 Bit SP 2.  IIS 7.0.

Issue

           I had three separate issues. All were in the report on the System Configuration Check Page the SQL Install.   

1)      First even though IIS was Installed it was telling me that IIS was not installed or configured.

2)      Error saying that ASP.NET was Required

3)      Version Error saying I should run System SKUUPGRADE = 1

Resolution

The First Error was:

Microsoft Internet Information Services (IIS) is either not installed or is disabled. IIS is required by some SQL Server features. Without IIS, some SQL Server features will not be available for installation. To install all SQL Server features, install IIS from Add or Remove Programs in Control Panel or enable the IIS service through the Control Panel if it is already installed, and then run SQL Server Setup again. For a list of features that depend on IIS, see Features Supported by Editions of SQL Server in Books Online.

I had originally installed IIS from Windows 2008 using defaults. Don’t Do that. Uninstall and Reinstall IIS using the right options. See this article: http://support.microsoft.com/kb/920201
You have to check the following OVER AND ABOVE the defaults:

Component	Folder
Static Content	Common HTTP Features
Default Document	Common HTTP Features
HTTP Redirection	Common HTTP Features
Directory Browsing	Common HTTP Features
ASP.Net	Application Development
ISAPI Extension	Application Development
ISAPI Filters	Application Development
Windows Authentication	Security
IIS Metabase	Management Tools
IIS 6 WMI	Management Tools

Next Error I was getting this:

64-bit ASP.Net is Registered. Required 32-bit ASP.Net to install Microsoft Reporting Service 2005 (32.bit).

64 Bit ASP.Net was already installed. So was 32 Bit ASP.Net. Both were showing up in IIS. But I would click on next and the option to install SSRS would be greyed out. So I knew I had to fix the problem. To do this You just need to go into your main web site (Port 80) and click on application pools. Click on the Set Application Pool Defaults in the Actions Pane. Then in the General Section Set Enable32bitApponWin64 to True.
Fix is buried in here: http://support.microsoft.com/kb/934162

The final error you can ignore. I could have run the command line for Setup SKUUPGRADE = 1. But I didn’t have to. It let me proceed without error.

Another thing to mention: Make sure when you are setting up SSRS that you select the Classic not the Default App Pool. You will get a HTTP 523 error.

Last thing to mention: I restored the reportserver and reportservertempdb databases and connected them in the setup screen to my instance. Of course I had to delete encrypted content (an Option in SSRS Setup Screen under where you create keys or restore). This gets rid of data connection info in shared data sources and any schedules setup. Not a big deal to recreate either one.

Conclusion

                Moving SQL SSRS to a 64 bit server has some pitfalls but can easily be done. You should just do 64 bit (will get rid of most of these errors) but in my case we could not.

SharePoint Demo Image gets error message “IE cannot display web page” for all sites

Goal 

You need to demo SharePoint 2010. You have downloaded the SharePoint Value Pillars 2010 Demo vhd to do this.     

Environment

 Windows Server 2008 R2 with Hyper-V installed.

Issue

           You follow the instructions to install this image. Once complete you can load the admin page http://demo2010a:2010 but any other site will not load on the image. You get the generic error message the Internet Explorer cannot load the web page.
            The instructions basically have you create a internal Network connection so that the box is not exposed to the network.  

Resolution

            The instructions for install of the virtual image have you setup a network connection that goes nowhere for the image. They have you name it internal and give it a bogus ip address.  They have you change the network connection for the image to the internal connection.    
         To get around this issue I

1)      changed the network connection of the image to the network card.

2)      On the image itself I went into the network connection and change the ip to my new address. I assigned a free address.  

3)      Assigned dns server and wins server to the same ip address (The image is its own domain, dns and wins). 

4)      Finally I went into DNS, changed the Host Ip, reloaded and restarted.

Change the Network Connection:
            Open Hyper-V Manager. Click on the image (Demo2010a) and click on settings. Click down to the Network Adapter (see Image). Use the drop down to change the setting from internal to what you actual network card is.

On Actual image change ip addresses:
            Click on Start. Right Mouse on Network. Click on the Local Area Connection 3.
Click on properties button. Select Internet Protocol Version 4. Click on properties button.
Change IP Address, sub net and dns server (See Image)

Click on advanced and change DNS and Wins Sever (See Image)

On DNS change Host Address:
            Click on Start. Click on Administration Tools. Click on DNS.  Under Forward Lookup find Host Record and change ip address (See Image).

Right Mouse on contoso.com and select all tasks then reload.
Go to top of tree and select right mouse then restrart.

Conclusion 

            The instructions of the installation of the VHD image for SharePoint 2010 seems to go to great length to preserve internal ip addresses when that isn’t necessary. Just change it in all the right places and the image will work fine.

SQL Server Reporting Services and Subscriptions: Who are you again?

Goal 

In the last article I dealt with the one hop limit of authentication in NTLM and how it pertained to SSRS and determining who the user is that is running the report. In this article we will deal with the same need to show a user thier data and also the need to do Subscriptions.

We still have the need to write SSRS Reports that identify who a user is by their network identity and display appropriate information they should see. This information can be Projects that are assigned to a project manager or maybe Grants to a Grant Manager. As you remember we are using the User!UserID in SSRS to determine the user and paste their name into the where clause.  We also want to allow these users to schedule reports through subscriptions is SSRS. Key requirement here is you want the user to schedule reports NOT push reports to the user when you want (Not Data Driven Subscriptions, Event Driven Subscriptions).

Environment

SSRS 2005 (or 2008) is installed on two Servers: One Server is the IIS Web Server, the other is the back end database server.  NTLM Network.  IIS 6.0 or 7.0. You are using Shared Datasource in SSRS to stored database info.

There are two types of Subscriptions in SSRS 2005 and 2008: Standard and Data Driven. Data Driven allow you to have a table and push out reports to users in that table when you set it up to run. That will not work here.  We have to use Standard Subscriptions that allow users to setup their own schedule.

Issue

            The problem with subscriptions is that it requires you to use a hard coded user to access the data. It has to use Stored Credentials in our shared datasource. You cannot use windows authentication when hitting the database. Well actually you can but it will use whatever SQL Reporting Services runs under. And using the User!UserID we used before, if we change nothing and someone schedules a report, the user will get a report for the owner of SQL Reporting Services because that is the user that will evaluate the report. SSRS runs the subscription, not the user. So to get the same results as before, evaluate User in the report, show them data only they should see and do it with subscriptions where SSRS is running the report for us is not possible.  

            So to summarize and make the problem crystal clear:

1)      With SSRS Subscriptions you have use stored credentials in the datasource.  You cannot use the users Windows Credentials because they are not running the report SSRS is.

2)      User!UserID will not work running under subscriptions in the main select of the report because SSRS is running the subscription using the stored credentials to hit the data and using the SSRS account to hit the actual report.

It may seem that our two requirements cannot be met under standard subscriptions but that is not the case, there is hope yet.

Resolution

            Using User!UserID in the main select of the report will not work, because that is evaluated when the report is run by SSRS at the time the Subscription was set to run. BUT using User!UserID in the Parameter Select of the report will work. The one part of the report that is run by the user in Subscriptions is to select parameters. If you use User!UserID to filter the entities like Projects or Grants that a user can select, then you can have the user select their projects first (or have it default to select all) and you never have to worry about what account is running subscriptions or used to hit the database. 

           
            To show you exactly what I mean lets use a real world example. I had a client that needed budget vs actual reporting. The report had to show a range of period selected by the project manager and let them run any project (called an RC for them) that is assigned to the project manager. The project manager and RC relationship was saved in the xBPReportAccess table. It had the windows id and the RC.
            What I did was dynamically setup the RC Dataset in the report to query that table using the User!UserID as shown below. But I also setup the RC Dataset to supply the parameter to the report for RC shown below. It is required so a user has to setup the parameter even for subscription. They can select one to all (allows multiples).

Setup of RC Parameter

The user has to select that parameter but I build it like this:

="Select subacct as RC, rtrim(ltrim(subacct)) + ' ' + rtrim(ltrim(dbo.raffaGetSegmentDescription(subacct, 2))) as RCDescription from xBPReportAccess where userid = UPPER(rtrim(ltrim(Substring('" + User!UserID + "', charindex('\', '" + User!UserID + "') + 1, 30))))"

Insuring the user can only select from their RC’s.



Dynamically building RC Select using User!UserID.  Users have to select this to setup subscription so User!UserID will be evalued as the Project Manager properly.



The actual select from the main report is just a stored procedure that accepts the begin and end period and the list of RCs as a parameters.

Conclusion 

Using Report Services to determine the user and dynamically pass it to the SQL statement to select parameters by using User!UserID allows you to setup subscriptions and have a report determine what data a user can see at the same time.

 I am going to move on to SQL Server and Using Linked Server and how the one hop limit affects it next.  

SQL Server Reporting Services (SSRS): A Tale of Two Servers

Goal 

Need to write SSRS Reports that identify who a user is by their network identity and display appropriate information they should see. This can be a project manager only seeing the projects they managed or a grant manager seeing only the grants they manage. Target information to an individual based on who they are.    SSRS should be a natural fit for this because it is Windows Authentication Based.

Environment

SSRS 2005 (or 2008) is installed on two Servers: One Server is the IIS Web Server, the other is the back end database server.  NTLM Network Network.  IIS 6.0 or 7.0.

Issue

                You use the SQL statement behind the report to identify the user as in the following:

Select project from projectmanagers where manager = system_user

or

Select Grant from grantmanagers where manager = suser_sname()

You are using SQL Server to tell you who the user is. Reporting Server must pass windows credentials to another Server to evaluate the statement properly. You have a table that has the windows id of the manager and what entities they should see.  You use SQL Server to evaluate the user to the text Domain Name/User Name and then filter you data based on a table of users. I have used this model over and over again in different systems to identify who see’s what.   

Now if you have SSRS  installed on the same server as the database server, the scenario described works. No Problems. It is when you use the two server model that the script can’t seem to identify who is running the report.  As you know it is not recommended to install IIS on same machine as the Database Server. It is a security Risk, so most places will have two servers: A web server and a Database Server. This makes it necessary for the process to pass your credentials from the workstation to the SSRS Server and then to the Database Server. This is a two server jump.  

The problem you run into is the single server limit to authentication passes across machines. In an NTLM Network you cannot authenticate once then hijack as many machines as you want. You are challenged for your credentials each new machine. A Process cannot just keep passing along your credentials. There is a single server jump limit. So from your workstation to the Windows Server housing SSRS takes up that one jump. It can't just pass along to the Database back end.

Resolution

The best solution here is to determine the user in the SQL Report itself, build the select statement on the fly in the report  pasting the user id in the statement  or to use parameters to pass the user to the sql statement. SQL Reporting Services provides an internal way to grab the windows user id:  User!UserID.   In Our Original Example we would have:

Select * from ProjectTable where Manager = suser_sname()

Modified to evaluate in SQL Reporting Services:

="select * from ProjectTable where Manager = '" & User!UserID & "'"

Notice you can build a SQL Statement in the Data Window in a String and paste in the evaluation of User.UserID in key parts to make it work. This makes Reporting Services Determine the User First, then pass it to the sql statement to pull the appropriate records.

 Image of Original Report Select:



Image of New Report Select:

Conclusion 

Using Report Services to determine the user and dynamically pass it to the SQL statement avoids the two server jump issue with NTLM in a Two Server Environment. You can also pass it in a hidden parameter, same concept.

 I am going to stick with this two server jump issue and talk about subscriptions and running reports that need to determine the user as well next.