Latest Entries »

Site is Moving!

I got tired of not having any artistic license with this blog platform (or restricted, at least). So I moved over to DreamHost, where I can edit all my PHP, CSS, and experiment with design (plus I get my own email addresses). I will not be posting any more on this site. I have moved all old posts over to the new site, yet will leave this one open, as this one is in the search engines.

Once I finish my other site’s design, I will push to to search engines, and remove this one. Please be patient because while I am enthusiastic about design, I am not terribly great at it- so it might be a little while.

You can access the other site:

http://smartbserver.net/blog

And you can check out some of my random CCS work at http://smartbserver.net/blog/home.php

The home page is not navigable from inside the site, but might replace my welcome page eventually.

Email me at Chris.Dill@SmartBServer.Net

:)

I have been tasked with working with an Access 2010 database. this database needs to be available to non-technical users for basic data entry, and the CEO of my company needs to be able to run reports off of dynamic data. My first thought was to make a custom ASP.NET solution off of the database back-end, but let’s face it- that’s a lot of work. So I decided upon another route.

  • The database will be split in Access into a front end and back-end.
  • The front-end’s will be turned binary and distributed to multiple users.
  • In the front-end’s, our designer will create the forms needed for users to input information.
  • We will link the back-end access file to an instance of SQL.
  • The back-end file will remain editable, for the database creator to add data and fields.
  • SQL Server Reporting Services will connect to the linked SQL database.
  • I will pre-configure reports for the CEO.

This article takes into account that you know a thing or two about computers.

  1. How to share and distribute an item.
  2. The differences between file formats (.accdb, .mdb, .accde)
  3. You have all the required software installed and configured (SQL Non-Express, Access, IIS 6+).
  4. You are willing to use new tools.
  5. You are familiar with connection strings and working with database connections.

My set up (And you should have most of the items here, or at least comparable).

Member server running Windows Server 2008 R2.

SQL Server 2008 R2 Enterprise- most full editions will work.

SQL Server Reporting Services- this will not run on some versions of Express, and I had trouble getting it installed on a Small Business Server 2008 server.

Micrososft Access 2010. Access 2010 Help.

We are going to download a few more items to make this easier. These items are not always necessary, but just the way I went about it with limited database knowledge.

Download and install SQL Server Migration Assistant 2008 for Access from Microsoft here.

Download and install Microsoft Access Runtime 2010 here. You should not need this if you have Access 2010 installed (which I did and do), but the installer for the first tool told me to do it, so I did :P

Optionally, I also installed Microsoft SQL Server 2008 Reporting Services Add-in for Microsoft SharePoint Technologies.

You need to have your Reporting Server up and running, and configured. For help on doing this, visit this page. Once you are set up, make sure your Report services is running of an SQL instance. For this walk-through, mine is named

MEMBERSERVER\REPORTING

My websites (automatically configured by SSRS) are set as

http://memberserver/reports

-and-

http://memberserver/reporting

Lets begin.

Create a Shared Drive on your server or on a secure PC. Enable the drive to be accessible by Everyone, or Authenticated Users. I created my share on the member servers C: drive and named it theDatabase (UNC location: \\memberserver\thedatabase).

I am going to drop my Access 2010 database file there, which is still in .accdb form. The database has roughly 6 tables, 300 rows in the main table, and 20 columns. It will receive roughly 6 entries per week max, from no more than 2 users. As you can see- small scale project.

Inside of this shared folder, create a folder named Backup. At this time, before we do anything, you should make a backup of your Access database, make the filename into todays date and time, and place it in this folder. We will create several backups throughout this process, and they should all follow this format and location.

With the file in the right place, and backed up, open up the tool we installed SSMA 2008 for Access.

SSMA Wizard

SSMA Wizard

You are presented with a wizard (I love wizards) that tells you the upcoming steps. Let’s use it:

Click Next.

Enter a name for the project (not the database or connection). I named mine SQLMigration1. Make sure the last box is set on SQL (for this blog post. You can change this if you are doing other sources).

SSMA Project Name

SSMA Project Name

Click Next.

Click Add Database (or find database, but we know where it is). In the popup, browse to the folder we created and placed the database in, then select it and click Open.

Add Database File

Add Database File

You might at this point receive an error. Reading this error, you can upgrade your connectivity drivers, or you can re-run the program. Mine was running in 32-bit mode (shortcut on desktop). To run in 64-bit mode, go to the file location and double-click the .exe file without the 32 on it.

The default location is:

“C:\Microsoft SQL Server Migration Assistant 2008 for Access\bin\SSMAforAccess.exe”

Repeat the steps and it should complete successfully. Select the items that you are going to import or link (in my case) to SQL. I selected both of the queries and all the tables.

Select Table and Queries

Select Table and Queries

Now type in your destination SQL server information.

Server name: MEMBERSERVER\REPORTING

Server Port: [default]

Database: WIMS

Authentication: Windows Authentication.

Since there is no database named WIMS in that instance of SQL, the wizards asks about creating it. I select yes.

Create Database Error

Create Database Error

If you want to link it, check the box. Read the paragraph for more information.

Link Tables

Link Tables

Let the tool run. You will get a popup about metadata, triggers, indexes, etc. Just click ok and let it run.

After it finishes, I have a boatload of errors- so we will send the database back to the creator to fix, but we will continue the process with this database, today.

At this point if you open up the database in access, you should see all of the linked tables. You will also see your database does exist in SQL Management Studio.

Linked Tables in Access

Linked Tables in Access

Database Created in SQL

Database Created in SQL

Close out Access, and close out SSMA. Save your project. Now backup (copy) your database into the backup folder.

Now browse to your Reporting Server site (http://memberserver/reports).

I have created a few folders here, do not mind them. On the link bar in the middle of your screen click New Data Source.

New Data source

New Data source

Now customize this page to fit your set up. Here is a link on how to create the connection string portion. the security will be of your own choosing, I use Windows Integrated.

When you are finished, Test it (you will see a green success or a red error message), then click Ok.

You will then see an icon for the Database on your home view- oops! I do not want users to see that. click the little arrow next to the database, select manage. check the box for hide in tiled view and click apply changes, then navigate to home again.

Now click on Report Builder, and get to making your reports. I will get into that in my next post, mostly because I do not know how to use the tool well as of yet.

Now we are going to split our linked Access Database into a front and back-end. We will leave the back end in the shared folder, and distribute the front end. when users make changes to the front end, they trickle down to the back-end, which replicates to SQL, and is then displayed in your reporting.

Open up your Access database. Click on Database Tools Ribbon, and then select Move Data > Access database.

Access Move Data

Access Move Data

Select Split Database. Save it, I typically leave the default formatting alone- which is to add _be (back-end) to the file name. This is the file you leave alone. you now give out copies of the original file for people to access the database.

You can make this a compiled project by saving it as accde, in the file menu> Save and Send, then save as ACCDE.

This is information that exists all over the place- the problem is that it is spread around forum comments and the solutions are as plentiful as the questions. I am going to explain how to create connections strings in an ASP.NET web application. The app is hosted on IIS 7.5, Server 2008 R2, and created in Microsoft Visual Web Designer 2010 Express (VWD). For the back-end I use SQL 2008 R2 Standard. VWD uses SQL 2008 Express natively, but I am not going to downgrade for this app.

There is also a little issue of server architecture. I was running into all sorts of problems with IIS and VWD being x64, while the connections drivers were running as x32.

A couple of key points.

  • In order for your application to connect to a database of any sort, it needs a connection string, which is a bit of code inserted into the web.config file in the root of your site.
  • The connections string tells the app: the source of the data, the driver to use, and the data location.
  • Each connection to a database needs to use a specific driver.

The first situation in which you need a connection string is for your app to contain membership information. Most of this stuff is done through wizard-like windows in VWD, but you will often need to change the default settings. This database is created by default when you Configure ASP.NET from within VWD. But I would rather create my own database, and hook up my application to it.

So I first create my database. Open up SQL Server Management Studio. connect to the instance you wish to have your app use. You can create a new instance, but I had an old one not being used, named MyServer\SQLEXPRESS. Do not be confused- this is not actually and Express database, just the name of the instance.

Connect to the instance, use Windows Authentication- this is of course your choice.

Connect to SQL Instance

Connect to SQL Instance

I’m going to glaze over the rest, but here’s what has to happen. You need to create a database, the default settings are alright. You need to grant the account that runs your application pool access to the database. My apppool runs under its own identity, and that identity needs Connect, Read, Select, Update, and Delete rights on the database.

For your ASP.NET security database, it needs to be prepared with a tool for ASP.NET to access it. Make sure you run the right tool, there are many.

C:\Windows\Micrososft.NET has a Framework and a Framework64 folder. Choose the one which is the same architecture as your OS.

So C:\Windows\Micrososft.NET\Framework64\ and then choose the folder that corresponds to the app Framework Version. This can be found many different ways, but if you right-click on the address of your app in solution explorer, and select property pages, you can get an idea.

Framework

Framework

 

So  C:\Windows\Microsoft.NET\Framework64\v4.0.30319\ and click on the application named aspnet_regsql.exe. This will run you through a wizard that prepares the database to hold site membership information.

When you use the wizard in your ASP.NET configuration, it will create all the required connections strings and providers for you, I had to tweak mine a bit.

Here is an example of the connection string I used.

<add name=ApplicationServices connectionString=data source=.\SQLEXPRESS;Integrated Security=True;Initial Catalog=aspnet; providerName=System.Data.SqlClient/>

 The name is only the name of the connection, this san be whatever you wish. The connectionstring is important. this has more arguments such as username, password, security, etc.

 
Important: data source=.\SQLEXPRESS;
This is the source instance. .\ means the local box. You could also do:
 
localhost\instancename
servername\instancename
remoteservername\instancename
  
Then there is Initial catalog=aspnet
This is the database name that this connection uses. There are other ways to format this, but Initial catalog=databasename without prefix works the best for my app.
With the ASP.NET membership security comes some other entries in your web.config, and these need to point at the connectionstrings NAME, in this case AplicationSecurity.
Here are two more examples of connection strings:
<add name=TheDatabaseSQLConnectionString connectionString=Data Source=******MEMBER\SQLEXPRESS;Initial Catalog=TheDatabaseSQL;Integrated Security=True providerName=System.Data.SqlClient;

/> 

 

 <add name=ContactSQLConnectionString connectionString=Data Source=******MEMBER\SQLEXPRESS;Initial Catalog=ContactSQL;Integrated Security=True providerName=System.Data.SqlClient/>

<add name=ContactConnectionString connectionString=Data Source=C:\TheDatabase\thedatabase2\App_Data\Contact.mdb;User Id=Admin;Password=; providerName=Microsoft.Jet.OLEDB.4.0;Data/>

You will notice that the data sources on the second one points to a file location, .mdb. this is because it is a Microsoft Access database, which is not hosted, only a file. you also see the provider name is actually the driver that is used for the connection, Jet in this instance.

You can get these connections strings from inside of VWD.

Click Tools>Connect to Database.

Add Data Connection

Add Data Connection

 

The Data Source is the driver you will use for your connection. I use SQL Server and Microsoft Access.
Click the drop down box and select the server\instancename where your database is located.
 
Select your authentication, mine is Windows.
Select or enter database name is used mostly with SQL databases, pick the database you created or have for the link here. With Access,  you can use Attach a Database file.
 
Now Test Connection, if everything is configured and set up, you should get a success message.
Now click Advanced.
 
There are more things to customize here. In the bottom is a box- this is your connectionstring. You can copy this information and paste it into your web.config, then edit the formatting to match the examples above.
Advanced Properties

Advanced Properties

With these connectionstrings in your web.config, you can drag AccessDatasource and SqlDataSource controls from the VWD toolbox onto your application, and then point them at the correct databases.
 

<asp:SqlDataSource ID=”ListDataSource” runat=”server”ConnectionString=”<%$ ConnectionStrings:ContactSQLConnectionString %>SelectCommand=”select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = ‘Contact'”></asp:SqlDataSource>

 

 

<asp:SqlDataSource ID=”SqlDataSource1″ runat=”server”

ConnectionString=”<%$ ConnectionStrings:ContactSQLConnectionString %>

SelectCommand=”SELECT * FROM [Contact]”></asp:SqlDataSource>

 

Note: For Access database files, make sure to add them into your App_Data folder in the application, then point to them there.There is another thing to mess around with when talking about application connections. Well drivers and all that- but I won’t go into that here.

I was having one hell of a time getting my application to talk to my databases. that is because when you design in IIS 7.5 x64, and VWD x64, your application is ment for x64 machines. It does not talk well to x32 drivers (in theory it should, with Microsoft’s backwards compatible drivers). So I changed my application to x32. This can be done using the Build>Configuration Manager… menu in VWD. This can also be set on your application pools in IIS. I changed the apppool serving my web application to support x32, and everything is working great with my older databases and .JET drivers. Right click on your application pool, select Advanced Properties, then change the value for enable 32-bit Applications to True.

Enable 32-bit Applications

Enable 32-bit Applications

Running the SBS 2008 BPA, you receive a warning item that states:

The log file for the Windows SharePoint Services configuration database is larger than 1 GB in size. For information about how to reduce the size of the log file, see the Knowledge Base at http://go.microsoft.com/fwlink/?LinkId=159745.

SQL Config Log Warning in BPA

SQL Config Log Warning in BPA

If you follow the link, Microsoft explains how to make a SQL script that will trim your database. My problem was that even after trimming the database, it was still over 1Gb in size- 1.4Gb actually. Let’s solve this.

This is not a permanent solution, but rather a solution to temporarily shrink the log files. They will eventually build back up- I plan to execute this plan when ever I get the warning.

Also note that doing this process will make the database temporarily “Full Recovery” only. which means that if you use differential backup and recovery, after this process takes place you can only do a full recovery untill the log files build back up over transactions.

Perform a full server backup.

Now check the size of the database.

Log into your Microsoft##SSEE Database (Windows Internal Database).

By entering \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query as your server name. Select Windows Authentication. Click Connect.

Connect to Microsoft##SSEE

Connect to Microsoft##SSEE

You will see several databases- we will be working with the one titled Sharepoint_Config_#########.

Databases

Databases

Right click on that database and select Properties. You will see two numbers for Size and Space Available. Look at the size. Mine was 1449 Mb. I ran the Microsoft suggested SQL script, and it was still the same size. So here is what to do.

Under the options tab of the database properties, change the Recovery Model dropdown from Full to Simple. This tells SQL to truncate the log files, meaning only a full recovery is available.

Recovery Model: Simple

Recovery Model: Simple

Click Ok.

Now Run the logshrink.sql to trim the database. You can do this through a command prompt by running command:

sqlcmd -S \\.\pipe\mssql$microsoft##ssee\sql\query -E -i
 
-or-
 
You can double-click logshrink.sql. It will ask you to log in, use the same information as above. Above the window that opens, click Execute.
Execute Script

Execute Script

 

Now go back into the Properties of the config database, click Options, and change Recovery Model back to Full.

Trimmed Database

Trimmed Database

You should see your file size WAY smaller now, happy shrinking.

I am designing a simple HTML site for my intranet. this site will host our policies and procedures, which have been split up into roughly 600 PDF files. The pages are written in HTML with CSS 2 completely from scratch. I use NTFS permissions and URLAuthorization to control who can visit the Windows Authentication website.

The site consists of five static pages, a little CSS trickery, and now, a bit of PHP. I decided that due to the sheer volume of the PDF file’s (which are linked from within the pages, and each file name is a slew of very specific policy numbers) that it would be difficult to pin point a broken link myself.

So I came up with the idea of a broken link (or feedback) page. A very simple page, with two input fields and a textarea field (Name, Email, Comment, respectively). A submit button finishes it off.

The problem with this is that when you hit submit and the mailto: runs, it opens up your default email program and populates a new message. this is ok, but what about people without an email client, or without one configured by default?

So I decide to use a bit of PHP to get my IIS to send my mail for me, in the background.

Here it goes.

First, my setup. I have a SBS 2008 running IIS 7.0, Exchange 2007, Sharepoint, pretty much everything. I then have a very low resourced Windows Server 2008 R2 member. This server does very little, but it does have IIS 7.5 installed, in order to run Lync Server 2010.

First thing we will do is install SMTP for IIS. Open up Server Manager, expand Features.

Click Add Features on the right, then select SMTP Server. Click Install.

Install SMTP

Install SMTP

 

Once that completes, you will have a new tool under Administrative Tools: Internet Information Services 6.0 Manager. Make sure it is 6.0, the other IIS Manager will not let you do this.

IIS 6.0 Manager

IIS 6.0 Manager

You might get a popup about 32 or 64-bit, choose 64 and click ok.

Choose 32 or 64 Bit

Choose 32 or 64 Bit

You will see Internet Information Services in the left window, with a sub-tree of your server name. Expand that node, and you will see VirtualSMTP. Right click that and select rename, and give it a good name- in this case I named mine MemberSMTP (It’s on my domain MEMBER).

Rename SMTP Virtual Server

Rename SMTP Virtual Server

Now right-click the renamed server, and select properties.

Click the Access Tab, then select Connections.

You want to enable the server that is hosting your website (the one that needs to sendmail) to use that server. I used the radio button Only The List Below (less access is good access).

Click Add, and add the loopback or localhost IP address of the server hosting the IIS if it is local. For good measure, I also added the true IP address of the server.

Connections

Connections

Click OK, and select the button Relay…

Set this up exactly as the other list.

Click Ok, Apply, and Ok.

Right click on the VirtualServer and select Start. If it is already started, click Stop, then Start again.

Thats it for the IIS part (roughly), now on to the PHP and the form itself.

Well, before we get into it, we need to have three web pages in our site. the form itself, named feedback.htm. We need a thank you page, named thanks.htm, and an error page, named error.htm. Put these in the root of your website. Here is the HTML from one of my pages.

Note that it will not work for you perfectly lacking my CSS.


<!--pan class="hiddenSpellError" pre=-->DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "<a href="http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd</a>">
<html xmlns="<a href="http://www.w3.org/1999/xhtml">http://www.w3.org/1999/xhtml</a>">
<head>
<title>Feedback</title>
<meta http-equiv="Content-Type"
content="text/html; charset=iso-8859-1" />
<meta name="Robots" content="NOINDEX" />
<meta http-equiv="PRAGMA" content="NO-CACHE" />
css"
href="PolicyStyleSheet.css" />

 
    <style type="text/css">
        .style1
        {
            color: #FFFFFF;
}

</style>

    <!--[if IE 6]>
<style type="text/css">
/* some css fixes for IE browsers */
html {overflow-y:hidden;}
body {overflow-y:auto;}
#bg {position:absolute; z-index:-1;}
#content {position:static;}
    .style2
    {
        color: #FF0000;
    }
    .style3
    {
        color: #999999;
    }
</style>
<!--span class="hiddenSpellError" pre=-->endif]-->

</head>
<body>
<div id="bg"><img src="images/gradient.png" width="100%" height="100%" alt=""></div>
<div id="content">
<!-- Header -->
<div id="#hdr">
<table>80px; width: 80%;">
<tbody></tbody>
</table>
        <tr>
       
<td> </td>
<img src="images/small logo no background.jpg"
        style="height: 75px; margin-left: 10px;"  />
        <td align="center"><h1><a name="top">Report a Problem<br />
        or Broken Link</a></h1>
        </td>

        <td>
        <a href="feedback.htm" title="Feedback">Feedback</a>
        </td>
       
        </tr>
        </table></div>
<div>
    <table border="0" cellpadding="0" cellspacing="0"
        style="height: 55px; width: 48%; margin-left: 50px;">


    </table>
</div>

<div id="lh-col1">
</div>

<div id="rh-col"><br />
</div>


</body>
</html>

 You pretty much just need three basic pages, with names you can remember, that have a head and body. you will customize them more later, or take the time to do so now. the error page should say something like “Sorry, you did not fill out the form correctly. Click here to return.” The thanks.htm should say something like “Hey thanks for taking the time! Go back to the site.”

Anyhow, the HTML/CSS lessons will have to come from someone else another time, if you can’t program HTML at this point, stop reading this post.

Now let’s install PHP. All of the romantics and programmers will tell you to manually install it. I don’t have time for all that, so I download the Windows binary here- VC9 x86 Non Thread Safe (2011-Jan-05 21:37:35). Once it downloads, double-click the msi file to start the install.

I selected to install all of the options. you can customize this the way you wish. Make sure to select IIS FastCGI when you get to that point.

IIS FastCGI

IIS FastCGI

Once that completes, we need to make a file in the root of our website. Open up Notapad and save the file as feedback.php. How you get it into your website is up to you. I actually use MS Visual Web Developer 2010, and just copied it into the file directory.

Now we need to make our script, and add some code to feedback.htm and feedback.php. there is a website that will generate it for you- go HERE. Thanks SiteWizard for such a great tool!

Scroll down and select Create a PHP feedback form, and select go to step 2.

PHP Feedback Form

PHP Feedback Form

Fill in your email address, or whoever you want to form results to be mailed to. Enter in the web address of your feedback page, thank you page, and error page. these were the feedback.htm, thanks.htm, and error.htm pages we already created. To get the correct address click on View in Browser from whatever program you are using to author your site, then copy the address for each page.

Page URL's

Page URL's

Skip over all of the optional selections. You can change these if you wish, but you don’t have to. Select to agree to the conditions, and click Generate Script.

The screen has two text areas. the top one is your feedback.PHP contents. Select all of the text, copy it, and paste it into your feedback.php file, using Notepad or your site editor.

Feedback.PHP content

Feedback.PHP content

 

Select the text from the second box and insert this into your feedback.htm page. It should go in the body of the page, in between <div></div> tags.

Feedback.HTM content

Feedback.HTM content

 

Now browse to your feedback.htm. Enter some information, and hit send. The email should arrive quickly.

If you have any problems with the HTML, post a comment and I will help out. If you have problems with IIS 7.5, ask away. If you have problems setting up PHP, view their documentation. If you need to change your PHP.ini settings, I can point you at an entry, but that’s it:


[mail function]
; For Win32 only.
; http://php.net/smtp
SMTP = localhost
; http://php.net/smtp-port
smtp_port = 25

; For Win32 only.
; http://php.net/sendmail-from
;sendmail_from = me@example.com

; For Unix only.  You may supply arguments as well (default: "sendmail -t -i").
; http://php.net/sendmail-path
;sendmail_path =

And of course thanks again to TheSiteWizard.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: