Today I needed to move a SQL database log file to a different partition.  I only needed to move the log to a different partition so fluctuating logs do not interfere with the actual data size. Looking online a few people put “I don’t think you can do this in the UI” and gave steps on scripting it out. It’s possible just not completely institutive.

The basic steps are simple, detach the database like you would to move the data, then move the log file to a new partition using explorer, and reattach the database. Normally after you found you’re database file you’d be done, this time look on the bottom window. Find the log file mapping, point this to your new location and click ok. You’re done.

Now here are step by step instructions:  First I am going to use SQL Server Management Studio, I’m assuming you’re already connected to your database server. Next I like to set my database I’m about to change to “single_user” since I have to take the database offline always, this just helps close connections and keep people off before I start. You can do this by right clicking on the database name, select ‘properties’ then select ‘options’ in the select a page panel, then scroll to the bottom to see restrict access.

Since I am here I also want to note the location of the log file I will be moving. Look at the left panel and click on “file”. Mine is currently the default location. Click OK will close the window.

Next step is to right click on the database select task->detach. The database is now detached and ready to be moved.

Now you need to navigate to the location we saw in the previous image using explore. The log file normally has .ldf extension. Move this to your new partition and note the location.

We need to go back to Management Studio and right click on the ‘databases’ folder in the tree, and select “attach…”
I added my database file again, and notice that below the log file is still pointed to the old location, the message shows ‘Not found’ because we moved the file to a new location. If you copied the file you may not see an error message here.

Click the ellipses to the left of the file and brows to the new location of your log file.

Now we can click ok and our task is done, only the log file was moved to a new location leaving the data file on the same file.

The other day we were faced with the double hop sql error. Basically we needed to connect to a sql server with a service account that accessed linked servers but we were not able to connect to the box directly. The only way we could get past this issue was:

Create an ssis package that calls a wrapper sproc, edit the wrapper sproc to do any execution I needed to run under that service account, and set that account to run under a proxy.

First create an SSIS package, connect it to your database (right click connection manager) and either use “execute T-SQL Statement Task” as seen here or “Execute SQL Task” you can see the execute statement is just a simple wrapper sproc. 

 Next we must set up the database to create the proxy under the service account. Here is where you give a common name in the Credential name, and the username and password in identify and password field.

 Now we must create a proxy for SSIS package, proxy name can be anything you wish; Credential Name was the name you gave in the previous step. Make sure you select SQL Server Integration Service Package (SSIS).

Last step is to create a job using the newly created proxy to run the SSIS package. Here we are clicking on new job. In steps we are creating new step. In steps we are calling making sure the type is SQL Server Integration Service Package. The Run as should see the Proxy name you set in the drop down. If you do not see it, double check you selected SQL Server Integration Service package. Then set the source and call you your SSIS Package

 Now you can fire off this task and it will complete with the rights of the service account. Now all you have to do is modify the wrapper sproc while the ssis package remains untouched.

I had to set up an SSIS package that inserted data into a log, grab that logID and pass it to all of the sub tasks so they can use that LogID.

I am new to SSIS packages and had to do a lot of reading to come up with how I was going to create an SSIS Package, Define a package variable, then pass that variable to a task. I set up a simple proof of concept to test my ideas.

First I Opened VS, and created a new Integrated Service Project. If you have a hard time finding it, it’s Project Types: Business Intelligence Project, you may need to install this from your SQL installation.

Next I Clicked on Tools->Other Windows->Variables. Making sure I have the SSIS Package I want to work with selected and in the open window I clicked on the icon for new variable. This was important because your variable scope can be pointed to a different project or at the task level if you aren’t paying attrition.

I gave it a common name, and set the Data Type to Object. This is set to object because I had an issue passing int32 values. I’ll talk about it below.

Now in my Control Flow I opened up my “toolbar” and dropped two Execute SQL tasks, I double clicked on the first one. Because this one will pass the ID to my variable I need to set the ResultSet to ‘single row’ then I am using an OLE DB connection, and put my localHost for the server.

Then I clicked on the SQL Statement ellipses and pasted in a very simple statement “select 8 as Test” this is just so I pass a value, in my real solution I had a bunch of sql code and ened it with “select IDENT_CURRENT('log.TaskRun') as TaskRunID” .

Now I have to bind “test” in my example or “TaskRunID” in my second example to the variable. I do this in the “Result Set” tab. I simply click “Add” (if you do not see add you did not set a value for “ResultSet”) For ResultName, this is where I put “Test” or “TaskRunID” for Varable Name it will look like User::<name>. Click ok and open the next Task.

For the second Execute sql task you will fill out the same information as before this time with a new SQL Statement.  For simple sake I put this
Declare @temp numeric
set @temp = ?
insert into log.taskrunlog (message) values (@temp)

Here I am jut inserting my value into the database, to show I did something.  Things to note is that I am declaring my temp as numeric, this was the only int style choice I could find. Also note the “?” this is used to denote variable in SSIS.  In my simple example I could replace @temp with the ? and only have one line of code, but if I wanted to reuse the ? I must put it in a variable. If I had 3 parameters then each ? will refer to a different parameter.

Next  lets click on the Parameter Mapping tab and click “add” from here you are going to select the variable name from the drop down user::<name> this is an input, data type is Numeric, next it very important Parameter Name is 0. If you have a list of them the next will be 1 etc. This allows the ? to work correctly.

From here you can right click and ‘execute task or f5 or click the green arrow. In my case I had to save it to the server first before I could execute my package, I can do that by file->save copy of testVarable.dtsx as…

From here I was able to create a task and execute it from sql, I’ll be showing that part next week.

A few weeks ago I had to review code on an old Visual Studio 2008 project.  It has been six months to a year since I’ve needed to open anything in VS 2008 but that day every time I clicked on an .aspx and clicked off it the page, VS would freeze I would have to close VS it and reopen it. My solution was to open it for about 10-25 seconds then switch off it quickly, or when I finally found the part I was interested in leave it to freeze and open a new VS instance. The strange thing was it only happened to an .aspx page and not on code behind or web config files.  After a long search turning up nothing I came across this article that showed me the problem was in an unlikely place.

http://rosscify.com/blog/visual-studio-2008-freezes-when-editing-aspx-files/

My problem is from Office 2007? Even though I’ve only used office 2010 64-bit on this computer, a few months back accidently installed Project 2007 32-bit. The installation failed, but not before install the “Microsoft Visual Studio Web Authoring Component”.  Apparently since this is a shared component it did not properly uninstall itself on rollback.

In my case the solution was just going to add/remove programs and remove the “Microsoft Visual Studio Web Authoring Component” then my visual studio was back to normal.