Copy a database from SQL Server to SQL Server Express

Since Microsoft's SQL Server doesn't support direct database copy from a full SQL Server version to their Express version (why?  who knows, perhaps they felt it would drive sales?) you can't use their GUI tools to copy a database.  You also can't import into Express from an SQL Server Export.

But I did find a way to do it manually.

Step 1: in MS SQL Server Management Studio, right click on the database you want to copy and choose Tasks-->Generate Scripts...

Step 2: Work through the wizard, but if you want to copy the data, take care to click the "Advanced" button on the "Set Scripting Options" step and find "Types of data to script" and choose "Schema and Data".  Finish the wizard and if all goes well it will save an SQL script you can use to reconstitute the database on your Express instance.

Step 3: Copy that sql script somewhere that you can access from the target computer running SQL Server Express.

Step 4: Using MS SQL Management Studio on your target computer, create the database you want to copy the schema and data into.  Create a user with ownership or at least create permissions.  Now, if the database you are copying is small, you can open the file in a query window in Management Studio and run it.  If your file is large, however, that won't work.

Step 5: Once step 4 is complete, then you can use a command line tool to run the sql script.  You will probably need to open the file in something like Notepad++ and tweak the initial USE command to use the database you have created.  You may also need to tweak other things.  Then you can run:

sqlcmd -S .\SQLEXPRESS -U myuser -P SECRETPASSWORD -i My_File_Export.sql

Good luck!

ken.

Classic ASP excel export fails with "download was interrupted"

After updating to Internet Explorer 10 (or maybe it was 11) we suddenly started getting an error "File.xls download was interrupted." from our classic ASP data exporter tool.  Finally I found this blog post:

http://blogs.msdn.com/b/ieinternals/archive/2012/07/16/content-length-and-transfer-encoding-validation-in-ie10-download-manager-couldnt-be-downloaded-retry-cancel.aspx

Which led me to understand that there was a problem with the way we had written our export code.

This is what we had:

string strFileName = lblRiverName.Text;
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment; filename=" + strFileName + ".xls");

Response.Write("...output here...");

Response.Flush();
Response.Close();
Response.End();

After reading the msdn blog post, I see that don't in fact want to call Response.Close(). It also sounded like we shouldn't call Response.End() either. So I commented both of those lines out and traded one problem for another. Now I was getting what seemed to be the initial html of the web page saved into the export file! Very strange. After reading more blogs and docs, I decided that perhaps Response.End() was important after all, contrary to what many folks seemed to say. That did the trick. So in the end all that needed to be done was to comment out the Response.Close():

string strFileName = lblRiverName.Text;
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment; filename=" + strFileName + ".xls");

Response.Write("...output here...");

Response.Flush();
//Response.Close();
Response.End();

Another win for Microsoft! (tic)

Moving average of an average: TSQL

We track water temperature data over a wide variety of locations and want to be able to report a 7 day moving average of the average temperature for a given day.  We're using Microsoft SQL Server 2012 which has nifty OVER and PARTITION functions that help.


First I noticed that I need to calculate the average temperature and then calculate the moving average of that value.  It seemed to me what I really wanted was to be able to do the simple average aggregate using a GROUP BY date, and then pipe that resultset to an OVER PARTITION query. 


Here's how I ended up writing it:


WITH DAILY_TEMP_AVG AS
(
 SELECT
  convert(date, ReadingDateTime) as TempDate,
  avg(WaterTemperature) as AvgWaterTemp,
  LocationId
 FROM WaterTemp_VW
 GROUP BY LocationId, convert(date, ReadingDateTime)
)
SELECT
 LocationId,
 TempDate,
 AvgWaterTemp,
 avg(AvgWaterTemp) over (PARTITION by locationid
       ORDER BY TempDate
       ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) as theaverage
 FROM DAILY_TEMP_AVG
 WHERE LocationId = 1150
 AND TempDate BETWEEN '6/1/2013' AND '7/1/2013'