SQL Server Reporting, are you having fun yet?

If you are visiting my blog today in search of help because you are configuring a SQL Server Reporting server, I hope this helps. I have had to setup the same reporting server about 3 times now. This last time because I bought a new dev PC and the client decided to use a custom configuration instead of the kinda standard setup that Reporting Services has.

Setting up a Report Server requires careful detail to permissions. Yes, love, love, love permissions.

Here are a few pointers:

Say you are setting your own local copy for dev purposes, and you get some sort of pop-up 'Access Denied' permission, right as you try to 'Start' you ReportServer, you may want to check your folder permissions on the 'Reporting Services', for myself I was using the 'Network Service' account to run the show and somehow the permission on the folder was all of the sudden gone after working for some time. IDK but make sure you go to the highest folder 'Reporting Services' and allow your selected user 'Full Control' on that folder!

Sometimes you may need to create a Login to run reports, so go into SQL Server Management Studio, create a Login with a SQL Server authentication and a password you can remember, give it permissions to the database you are trying to get a report to run on. Sometimes you may need to give it 'Execute' permissions on Store Procs too! This user may even come in handy as the user that a 'Shared Data Source' can use. Since I am just doing this for dev purposes, I have found this is the easiest way to get passed permission errors....

Now, let say you got the ReportServer to start but it is still acting funny. You get a 500 Service error when you try to visit the ReportServer URL. Go to the log files and check into errors there.

If you see this error:

ERROR: AppDomain ReportServer_MSSQLSERVER_0 failed to start. Error: Invalid syntax on line 166 - 'Expected > character.'.

Please inspect your config files, make sure you have all begin and end tags correct (and double check all those attributes!). I was actually missing an end tag and this error kept coming up. I was editing the config files in TextPad so it was hard to spot. Once I opened them in Visual Studio, it was much easier to identify!

Ok. So you have stuff running, and you are trying to run as the 'Integrated Security' for stuff, you visit the Report Server URL, it keeps prompting you for a login or maybe permission, try this. Open IE Explorer as 'Run as Administrator' then try again. This is one case where you will have to drop FireFox/Chrome. IE plays better here. I suppose since it is a MS product....

Hope that helps! It is always a headache setting up a report server! I feel for you. Just do little steps at a time, DOUBLE-CHECK! It is very finicky one little thing and it doesn't work!

GoDaddy - Configuration to allow PHP in HTML files

I had an existing site with a lot of existing html files that also needed to execute php. However, when configuring anything, there may be numerous ways to solve the same problem. You can find this question on many sites and some solutions work for people and some do not. I found I had to keep trying slightly different versions until one finally worked. It all depends on the particular hosting service you are using, the various settings your particular site has..on and on.

On my local server, I could put the following at the top of my .htaccess file and it ran the php inside the .html files fine:

AddHandler x-httpd-php .php4

AddType application/x-httpd-php .html

On the GoDaddy server, the configuration was a bit different:

Options +ExecCGI

AddType text/html .html

AddHandler x-httpd-php5 .php .html

I had struggled with this for awhile, this article gave me the best hint:

http://www.sandisviksna.com/blog/2011/10/16/php-in-html-with-godaddy/

which recommended this:

Options +ExecCGI

AddType application/x-httpd-php .php .htm .html

AddHandler x-httpd-php5 .php .htm .html

So I kept tweaking that and it worked. Honestly, I have not completely read the Apache manual on what exactly these directives mean. I am one of those people that is willing to use what works without really knowing the details because I have to keep working. The client want stuff done and right now I do not have the leisure time to research the details...I hope this helps someone else!

ASP.net Web.config use older DLL

I ran into a situation where the web server I was publishing too had an older DLL file than the DLL file I had used to compile on my local server. So when I published to the server it kept looking for the new version rather than just using the one available on the server.

You can specify the version to use in the web.config file. However, it is a bit deceiving when you want to go backwards meaning I want to user version 1.0 but the dll I used to compile was version 2.0. The tag uses the term oldVersion and newVersion. I think most people want to go in the direction from old to new, but in my case it was new to old.

That kept tripping me up. I kept specifying the old and new version thinking it would understand me...no, i had to say the old version is 2.0 and the new version that should be used is 1.0.

Maybe the tags should have been versionToUse and versionToIgnore...

Here is my sample web.config snippet:

pop-uptext

 <runtime>
        <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
         <dependentAssembly>
           <assemblyIdentity name="DLLname" culture="neutral" publicKeyToken="a7a0b3f5184f2169"/>
           <codeBase version="8.0.0.5" href="FILE://C:/windows/assembly/GAC_MSIL/DLLname"/>
           <bindingRedirect oldVersion="8.1.2.3" newVersion="8.0.0.5" />  
          </dependentAssembly>      
        </assemblyBinding>
        </runtime>

 <runtime>
  <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
   <dependentAssembly>
     <assemblyIdentity name="DLLname" culture="neutral" publicKeyToken="a7a0b3f5184f2169"/>
     <codeBase version="8.0.0.5" href="FILE://C:/windows/assembly/GAC_MSIL/DLLname"/>
     <bindingRedirect oldVersion="8.1.2.3" newVersion="8.0.0.5" />  

    </dependentAssembly>      
  </assemblyBinding>
     </runtime>

IvyCat Testimonial Rotator WordPress Plugin

I have worked a bit with WordPress over the past year. It is an interesting template-type application. I don't know the actual term for what it is but it is based on PHP. The nice thing about it is that there are so many pre-written modules available some free, some you have to pay for. The selection is nice and you can always modify the code.

Recently I needed to create a Testimonial Rotator. I sampled the 8-10 free ones available. The one I ended up selecting was the IvyCat Testimonial.

It was the best one that could pretty much be used out of the box. I tweeked it just a bit to fit into the page I needed. It was easy to use, pretty flexible, fast and I would totally recommend it!

Setting up a SqlCacheDepdency

The great thing about setting up a SqlCacheDepdency is that once it is working, you never really have to do anything with it. In my case, it has been roughly 5 years since I set one up...the downfall? I can't remember all the little steps it takes to set it up. And it is not easy. It has to be precisely setup....precisely!

Today, I wanted to beat my PC into oblivion. Seeing that was not really going to help, I had to research...and research. Here are the steps. Though not clear because when you try 1 million things, you don't really know what was needed and what was not...

Details, asp.net, SQLServer 2008R2

1. NEEDED, Enable Broker
ALTER DATABASE myDbName SET ENABLE_BROKER with rollback immediate;

2. [Not SQL2008R2] Needed for lesser versions...I think. I will add this just in case you need it:

GRANT CREATE PROCEDURE TO "NT AUTHORITY\NETWORK SERVICE"
GRANT CREATE QUEUE TO "NT AUTHORITY\NETWORK SERVICE"
GRANT CREATE SERVICE TO "NT AUTHORITY\NETWORK SERVICE"
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO "NT AUTHORITY\NETWORK SERVICE"
GRANT SELECT ON OBJECT::dbo.TableName TO "NT AUTHORITY\NETWORK SERVICE"
GRANT RECEIVE ON QueryNotificationErrorsQueue TO "NT AUTHORITY\NETWORK SERVICE"


3. Setup Global.asax- Application_Start:

System.Data.SqlClient.SqlDependency.Start(connectionString);

4. Setup Cache code:

pop-uptext

ConfigurationInfo configInfo = null;
        System.Web.Caching.SqlCacheDependency dependency = null;
        if (Cache["Config"] == null)
        {
            try
            {
                
                 cacheConfigInfo = GetConfiguration(out dependency);
            }
            catch {}
            finally
            {
                Cache.Insert("Config", cacheConfigInfo, dependency);
         
            } 
        }
....

ConfigurationInfo configInfo = null;
        System.Web.Caching.SqlCacheDependency dependency = null;

        if (Cache["Config"] == null)
        {
            try
            {
                
                 cacheConfigInfo = GetConfiguration(out dependency);
            }
            catch {}
            finally
            {
                Cache.Insert("Config", cacheConfigInfo, dependency);
         
            } 
        }
....

5. VERY Important: You must attach the SqlCacheDependency to the SqlCommand before you execute the command:

It must be a simple query (no crazy aggregates), it can be in a stored procedure.
If it is in a stored proc qualify the db name like this :dbo.MyTableName

pop-uptext

public ConfigurationInfo GetConfiguration(out SqlCacheDependency dependency)
        {
            ConfigurationInfo configInfo = null;
          
            
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = SQLHelper.DbConnectionString;
            SqlCommand command = new SqlCommand("up_GetConfig", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;
            dependency = new SqlCacheDependency(command);
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.DbConnectionString, "up_GetConfig"))
            {
                if (reader.Read())
                {
                    configInfo = new ConfigurationInfo();
                    configInfo.Id  = reader["ID"] == DBNull.Value ? 0 : (int)reader["ID"];
        ....
                    
                }
            }
            return configInfo;
        }

public ConfigurationInfo GetConfiguration(out SqlCacheDependency dependency)
        {
            ConfigurationInfo configInfo = null;
          
            
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = SQLHelper.DbConnectionString;
            SqlCommand command = new SqlCommand("up_GetConfig", conn);

            command.CommandType = System.Data.CommandType.StoredProcedure;

            dependency = new SqlCacheDependency(command);

            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.DbConnectionString, "up_GetConfig"))
            {
                if (reader.Read())
                {
                    configInfo = new ConfigurationInfo();

                    configInfo.Id  = reader["ID"] == DBNull.Value ? 0 : (int)reader["ID"];
        ....
                    
                }
            }

            return configInfo;
        }

I hope that helps....I am sure in another 5 years I might need to reference this again!

Blogger SyntaxHighlight

I have been trying to use SyntaxHighlighter for months now and it has been quite cumbersome. I don't post every week so every time I have to post code, I have to go back to remember the steps to post formatted code.

Plus I thought I had added the calls to my blogger template but now they are missing? very strange. maybe for some odd reason i decided to take out something i wanted to use from my template....OR MAYBE NOT! i don't know if blogger decided to remove the calls or if they did an update that took out my calls but they are not there...

Overall, I pretty much regret selecting Blogger as my blogging tool. If I could go back in time and start with a blogging tool that was more programmer friendly, I sooooo wish I would have. But now I am stuck in the Blogger trap....

anyhow, since SyntaxHighlighter is hard for me to remember how to use, i am trying out a new solution. http://www.syntaxhighlight.in/. This has a nice option to pop-up the code or display in text. I will see in time if this option is less time consuming to use..

Jquery Tab does not work in ASP UpdatePanel

I have been using Jquery Tabs a lot lately and they usually work just fine. An issue here and there. Today I had an issue when trying to put the tabs inside an ASP Update Panel. The code did not seem to work. I found this easy fix buried in some forum....so I thought I would put it in my blog to be more accessible...and it worked really...

pop-uptext

 <script type="text/javascript">
       function pageLoad(sender, args) {
            if (args.get_isPartialLoad()) {
               //put any javascript code
                $('#myTabs').tabs();
            }
        }
</script>

 <script type="text/javascript">
       function pageLoad(sender, args) {
            if (args.get_isPartialLoad()) {
               //put any javascript code
                $('#myTabs').tabs();
            }
        }
</script>