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.