Troubleshooting Microsoft Excel cannot access the file

Here’s the scenario. I am running SQL Server (on Windows Server 2008 R2) and I have a created a Job that has among other steps a CmdExec step. This CmdExec step calls a C# console application that I wrote. My C# console application calls the Microsoft.Office.Interop.Excel.Application. The code throws the following exception / message.

Microsoft Excel cannot access the file 'c:\myDir\MyFile.xlsx'. There are several possible reasons:

• The file name or path does not exist.

• The file is being used by another program.

• The workbook you are trying to save has the same name as a currently open workbook.

 

The last line of the code below is the line that throws the exception

Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
string filename = @"c:\myDir\MyFile.xlsx"
Console.WriteLine("Exists: " + File.Exists(filename).ToString());
var wb = xlApp.Workbooks.Open(filename);

The message suggests that it can’t find my file that I am trying to open, so I made sure none of these were the issue. However, the actual problem is not anything to do with my file.  Instead I just had to create a directory called Desktop at the following path:

C:\Windows\SysWOW64\config\systemprofile\Desktop

It is worth noting I am running Windows Server 2008 R2 and is a 64-bit operating system. If you are running a 32-bit OS you would need to change make sure there is a desktop directory here:

C:\Windows\System32\config\systemprofile\Desktop

I noticed that I needed to be admin to follow the path above, but it didn’t seem to matter from a code perspective. However, if this solution doesn’t work, I would suggest playing with the permissions to that Desktop directory just to make sure since that is what I did to start with and then un-did my changes to the permissions. Also, I am running Windows 7 64-bit on my laptop where I am developing and I did not have any issues if when that directory did NOT exist. I don’t know why this seems to be particular to the server environment.

Thanks to this discussion that gave me this solution.

Why doesn’t my SQL Server Job that uses CmdExec not report a failure correctly

Here’s the scenario. I am running SQL Server and I have a created a Job that has among other steps a CmdExec step. This CmdExec step calls a C# console application that I wrote. My C# console application writes its errors to the Windows Event Log instead of just throwing an exception. True, I could just throw the exception and the exception would get logged to the Windows Event Log, but there would be 2 or 3 entries and they would be ugly and in general just confusing. I want to have better control over what gets written to the Windows Event Log so I have a try-catch at the highest level of my console application so that nearly no exception will be just thrown.

The problem

The problem is that when I catch the exception my program no longer returns a non-zero return code (error code) like it would if an uncaught exception was thrown. I want my program to have a return of 0 only if it was successful, and non-zero (let’s choose 1) if there is an error I caught and wrote to the Windows Event Log. The major reason I need to make sure the return code is correct is because SQL Server’s CmdExec step looks at the return code to determine if the step succeeded or not which it then affects the overall flow of the later steps and also what the status of the Job is.

Thank goodness there is an easy way to solve this problem if you are writing a C# console application.

Here is a very simple but illustrative example of how to change the return code. No need to set it for success since it will do that for us. We just have to handle the case where we catch the exception and want to have the return code to 1 (indicating an error).

static void Main(string[] args)
{
try
{
// some code here
throw new Exception(“Oh no an error”);
}
catch (Exception ex)
{
// write error to Windows Event log here using your favorite code
…

//Change the return code to 1 since there was an error
System.Environment.ExitCode = 1;
}
}

Troubleshooting ‘The client connection security context could not be impersonated. Attaching files require an integrated client login’

I am calling msdb.dbo.sp_send_dbmail from my C# console application and is connecting via a SQL Server Database user. When I call stored proc from C# I get the message below.

The client connection security context could not be impersonated. Attaching files require an integrated client login

there are some things you can try.

• If you want to use a SQL Server Database user you still can, but you will need to give that user sysadmin rights. You can add those permissions using the following command:
  
sp_addsrvrolemember '<Login>', 'sysadmin' 

• Try connecting as a Windows users instead of a SQL Server Database user. In other words, try a domain user.
  
• Make sure the file is not too large. The default size is 1,000,000 bytes (nearly 1MB). You can change the max attachment size using the following command:
  

  USE msdb
  EXEC sysmail_configure_sp @parameter_name='MaxFileSize', @parameter_value=N'1572864'
  GO
  
  In the example above the new max attachment is 1.5MB
  

Troubleshooting ‘profile name is not valid’ error

I got this message when I tried to call the msdb.dbo.sp_send_dbmail stored procedure. I had setup my profile (let’s call it Profile1). I even gave my user (let’s call it User1) the permissions to send mail as described here. I then called it from some C# code and got the following message.

profile name is not valid

Here are the steps I used to figure out what my problem was.

1. Double check that the name of the profile is correct and is being passed correctly to the stored proc using the @profile_name parameter.
   
2. The next step is to figure out if it is my code or SQL server side. The easiest way to do that is to open SQL Management Studio and connect to your database server using the exact user/login you were using when you got the error. Then try to send the mail message using the following (tweak as necessary):
   

   msdb.dbo.sp_send_dbmail
       @profile_name = 'Profile1',
       @recipients = 'someone@somewhere.com',
       @subject = 'Test subject',
       @body = '<p>Test Body</p>',
       @body_format = 'HTML'
   
   If you still get the error, it is something to do with SQL Server, otherwise, it is somewhere in your code that is called the stored proc. If it is in your code, you are on your own. If it is on the server (still getting the error) then proceed to the next step.

3. In SQL Management Studio, open another connection, but this time connect as a user that has more rights (probably yourself). Now execute the following stored proc.
   
   msdb.dbo.sysmail_help_principalprofile_sp @principal_name = 'User1', @profile_name = 'Profile1'
   
   If you get no results back then that means you have a permission problem. This likely means that your profile is NOT public or the user (User1) don’t have access to it. You an change the permission by going to the Database Mail Configuration Wizard | Manage profile security radio button | Next button. Here you can click the profile to make it public. You can alternatively go to the Private Profiles tab and select the user and then the profile. Either way should work.
   
   If you run the stored procedure again, it should show that you now have access to the profile now. You can also re-run the sp_send_dbmail and it should work this time.

Add-ins ribbon missing in Excel 2007

I was amazed that for some reason my installation of Excel 2007 didn’t have the Add-Ins ribbon like everyone else does. I don’t know how this happened, but here is the fix that worked for me.

1. Open Excel
2. Click Office button.
3. Click Excel Options button.
4. Click the Customize tab on the left.
5. Choose Commands Not in the Ribbon from the Choose commands from drop down list.
6. Click the Add-Ins item in list
7. Click the Add >> button
8. Click the OK button.

Robotic Lawnmower Ideas

Solar R/C Lawn mower out of wood
Arduino R/C Lawn mower
Step by Step how to build a R/C lawn mower
What not to do guide

Q&A on SharePoint Architect from MetaVis Technologies

Below are answers (in my own words and some of my own commentary) I got from a Q&A meeting with MetaVis Technologies on their SharePoint migration tooling called Architect Suite. They recommend their Architect Suite and the questions are answered based on the assumption that is the tool you will be using. However, depending on your needs some of the individual products in that suite can be purchased instead.

Q: What are the limitations of this product

A: The following are not supported:

· Highly customized look and feels

· Copying of assemblies for web parts that are installed. This must be manually BEFORE the content is copied.

· Best to copy solutions to target before copy content.

· Many workflows are not supported, but no clear way to tell.

· Running workflows are not supported.

· Workflow history is not preserved.

· Can on preserve either Workflow Approval Status or Last Modified By, but not both.

· Users must be in both active directories, otherwise the current user will be used.

Q: Are OOTB workflows supported

A: It depends. If there are dependencies or customizations then no. We can try it and see if they work. The copying of workflows is not enabled by default. In most cases they recommend just recreating the workflows.

Q: What happens to workflows that are running?

A: Running workflows are not support. Workflows MUST be completed before they are copied.

Q: Is Workflow history kept?

A: No, no workflow history is kept.

Q: What is the copying of a workflow actually doing?

A: They are getting the xaml for the workflows and then replacing links in them.

Q: Where should I install the Metavis application?

A: The Metavis application should be installed close to the source or target system.

Q: What kind of performance can I expect from this application?

A: 250MB an hour is slow and up to 6GB is the fast. Multithreading is recommended. This varies greatly though based on structure, permissions, location, bandwidth, etc. For example, moving files is fast, but re-creating structure, permissions, etc takes longer.

Q: What is the recommended approach for migrating

A: Make sure all the users that you want to keep history for are in active directory in the source and target.

Make sure all workflows are completed or don't copy workflows.

Enable claims based authentication if needed for Office 354 SharePoint migration.

Use one of the three approaches for copying:

Use the UI provided by Metavis

This has the advantage of requiring no other tooling and is still fairly efficient. Its disadvantage is that you must be present each time new sites need to be selected.

1. Take advantage of multi-threading.
2. To do this create the top level sites on the target.
3. Select a few sites at a time and start the copying.
4. Repeat step 3 while taking care not to overwhelm any systems. Do this until all sites have been processed.

Script / Schedule the Migration

This has the advantage of being executed again easily and give you more control. Perhaps the biggest advantage of this is you can run with minimal interaction.

1. Use the Metavis UI to migrate a site, but instead of executing, just generate the script. This script can be used on the command line, windows scheduler, etc.
2. Copy the script, change the source and destination to match each site you want to migrate
3. Execute the scripts in a parallel manner while taking care not to overwhelm any systems. Do this until all site have been processed.

Single-Threaded

This is the easiest because you just select all the sites and let it navigate the tree one site at a time. The downside is that it will take a lot longer to finish a large number of sites because it is only doing it one site at a time instead of in parallel as the other two approaches.

Test workflows that you may have copied. If they don't work, then re-create them.

Once everything is copied to the target have users do UAT to make sure everything is working ok. At this point keep the source system as the production one until UAT has finished. UAT will take some time. Don't expect everything to move over flawlessly, there will be issues in the beginning. Once everything looks good, go back and do an incremental copy from the source to the target system. No you can decommission the source system.

It is NOT recommended that you promise users that the system will be perfect on a given date. Plan to work with end users in UAT to solve issues. Once they have been solved it will be up to the users to start using the new system. This means that if you have a lot of users and sites you will likely have some users on the source system and some on the target system.

Q: Do site groups get copied over and over again

A: No they only get copied once, but there is a performance penalty the first time they are copied.

Q: There are a lot of settings on copy screen, should I just check everything.

A: No, the defaults are typically the best choice. Only change them if you know what they do (use the help icon near each option to better understand).

Q: How can script the migration

A: Use the UI and pretend like you are doing a migration using the UI. Before you get to the Finish / Execute step a button to generate a script will appear. Use that to generate a script you can use as a template for the other sites. You typically just need to change the source and target.

Q: Why aren't alerts copied by default?

A: Alerts are not copied by default, but you can enable them. Be careful though. The reason is that if you copy them when you are copying the site, the copying of the content will trigger alerts (lots of them). You can go back and copy the alerts after everything has been copied or you can leave it up to the end users to sign up for alerts again. Beware of copying alerts before you are sure you will not be doing any more incremental copies. Metavis recommends the later.

Q: How do I import data and metadata from other systems such as Lotus Notes, Alfresco, or even the file system?

A: In all cases, Metavis does not write custom connectors to different products. However, most products can be exported to the file system and the metadata put in CSV format. You can then drag and drop from the file system to the Metavis application. The application then allows you to map the metadata (including folder structure) to SharePoint. You can also map users, permissions, etc at this point.

Q: How can I keep history of users that are no longer with the company?

A: The users MUST be in the source and target active directory. If you are migrating to Office 365 SharePoint just be sure that these users are not assigned a license. You can mark them as disabled as well. Then you can map the usernames using the Metavis application.

Q: Can I expect 100% fidelity of my sites after a copy?

A: No, there are too many customizations that can be done to SharePoint to guarantee full fidelity copying. However, lists, calendars, master pages, permissions, users, groups, and most uncustomized functionality is copied without issue.

Q: Is there a way to compare two sites

A: Yes, the two provides a visual compare of two sites. Permissions can be ignored as well.

Q: In the trial version it is difficult to get my site to transfer all the files, how can I get the entire site copied.

A: The trial version will only copy the first 5 item in a list. You can go back and copy the ones that were missed. You can also use the compare features to see any differences. There are typically some hidden lists as well. If you right-click on lists you can choose to show hidden lists.

Q: Are there any tools to help migrate references in Outlook to SharePoint lists and calendars to the new locations after the migration?

A: Not that I am aware of, but a good place to start is Colligo.