Clr Assembly must have main file specified

Coordinator
Feb 12, 2013 at 1:00 AM
Rok1 Today at 3:16 PM
Hello Furmangg. I'm using SQL Server 2012 and using the single database assembly, I followed your instructions on unblocking the DLL, But Still I get "Clr Assembly must have main file specified" - Error. But when I try it again, I get error " A .NET assembly already exists with that name. Remove the existing and try again."

I dropped and re-created ASDB many times, but the same error shows up. Any suggestions?
btw - It's working just fine on SQL SERVER R2.

-Rok
Coordinator
Feb 12, 2013 at 1:03 AM
You went to the Downloads tab and downloaded the ASSP 2012 zip file? And then you unzipped it and unblocked the ASSP.dll file. Then you used SSMS to create the assembly? If you get any failures, then you right clicked on the Assemblies folder and chose Refresh. Then you deleted ASSP and tried again?

One change I made to the release page is that I removed the default download so that it takes you to the Downloads page so you have to choose the right version of DLL. Let me know if that was the problem you were running into.
Feb 12, 2013 at 3:11 PM
These are the steps I took:
  • Went to the Downloads tab and downloaded the ASSP 2012 zip file. -> Unzipped it and unblocked the ASSP.dll file.
  • Right click on the assemblies folder (SSMS),--> New assembly and located the ASSP.dll file. Permissions set to unrestricted and impersonation set to Default.
  • Click OK --> "clr Assembly must have main file specified" - Error shows up.
  • Hit OK on the error message box and Hit OK again on [Register Database Assembly] box. "A .net assembly already exists with that name. remove the existing assembly and try agan." error pops up.
  • Cancel out of the [Register Database Assembly] box, and refresh the Assemblies folder, no ASSP assemblies exists.
I tried installing it on both single database and all database level. Both doesn't seem to work.
Coordinator
Feb 12, 2013 at 6:05 PM
Maybe some file got orphaned. When you try to create a new assembly, can you give it a name other than ASSP and see if that works?

You might also restart SSAS and see if that solves the problem. I don't think I've run into this scenario before.
Feb 12, 2013 at 11:09 PM
See this KB article.

I am receiving the same error when trying to add the 1.3.6 2012 dll to my 2012 instance. I have also unblocked the DLL before adding it.
Coordinator
Feb 13, 2013 at 1:19 AM
Can both of you provide the following:
  • the version number of Analysis Services you are using. Connect SSMS Object Explorer and look at the version on the server node
  • a list of every version of the .NET framework on the SSMS machine and the SSAS server
Are you creating the assembly in SSMS on Remote Desktop on the server or in SSMS from another machine?
Coordinator
Feb 13, 2013 at 2:14 AM
I tested ASSP 2012 out on several computers again tonight, just to make sure it still works and than no Windows Update patch had broken it or something.

It does work when I create the assembly in SQL Server Management Studio 2012.

I do get the "Clr Assembly must have main file specified" error (the first time, then the "assembly already exists" error the second time) if I try to create the assembly with SQL Server Management Studio 2008 R2 connected to a SSAS2012 instance.

What version of SSMS are you using? In SSMS, go to Help... About. You should see the first row listed as Microsoft SQL Server Management Studio version 11.something for SSMS 2012. Basically you want to make sure to create the ASSP assembly on the SSAS server that you're using the SSMS version that matches the SSAS version on the server.

Does that solve your problem?
Feb 13, 2013 at 9:56 PM
Hi furmangg,

Microsoft SQL Server Management Studio 11.0.3000.0
Microsoft Analysis Services Client Tools 11.0.3000.0
Microsoft .NET Framework 4.0.30319.18034
Operating System 6.1.7601

I am accessing a server running on my local machine through SSMS running locally.


All .NET versions
2.050727
3.0
3.5
4.0.30319

Let me know if you need anything else
Coordinator
Feb 14, 2013 at 10:49 AM
That's exactly what I'm running on my laptop except that the .NET Framework reported by my SSMS2012 is a bit older:
Microsoft .NET Framework 4.0.30319.296

Does 4.0.30319.18034 mean that you've got .NET Framework 4.5 installed? Can you open up the Control Panel and go to Programs and Features and see if you see "Microsoft .NET Framework 4.5" listed?

On another machine with .NET Framework 4.5 I installed SSMS and it reported .NET Framework 4.0.30319.17929. Then I installed http://support.microsoft.com/kb/2750147 and it now reports 4.0.30319.18034. Regardless, I still can't reproduce the error you're seeing unless the DLL is not unblocked or unless I'm using an older version of SSMS like SSMS2008.

Are you unable to create the ASSP assembly on every AS2012 server? If you're able to create it on another server, then instead of clicking OK to create the assembly, you can click Script and generate an XMLA script that you can run on the other AS2012 server where you're having problems.

You might try downloading the ASSP source code and compiling it yourself. I can't see why there would be a difference, but maybe there's something slightly different on your installation or laptop setup.

Final suggestion is to open a support case with Microsoft to get to the bottom of what's causing the problem in SSMS. If I could reproduce the problem, I'd see if there's a workaround on our end. But I can't reproduce so far.

If you have any more thoughts or datapoints about when it works and when it doesn't, let me know.
Feb 14, 2013 at 8:56 PM
I tried a new installation (SQL SERVER 2012) on a server and I encountered the same issue.

SQL SERVER 11.0.2100
Microsoft Analysis server 11.0.2100.60
Microsoft .NET Framework 4.0.30319

I'm remote login to the server. I tried renaming ASSP to something else as well. This is the first time(s) I'm trying to install ASSP on 2012. So far I've tried on 2 different machines.

I had no problems on 2008R2
Coordinator
Feb 15, 2013 at 1:29 PM
I provided Rok1 with the XMLA script to create the assembly (which may be a workaround). Stevebot, if you want that script, please feel free to click furmangg and contact me. If this works, we can put the XMLA script as a separate download in the release.
Coordinator
Feb 15, 2013 at 3:17 PM
Can you two try one other troubleshooting step? When you get to the dialog to register ASSP.dll, instead of clicking OK, can you click the "Details" button in the top right. I'm hoping it gives you a more helpful error message.
Coordinator
Feb 18, 2013 at 12:57 AM
Do either of you have any other assemblies registered or is ASSP all that you have loaded?
Feb 18, 2013 at 2:08 PM
Hi Furmangg,

The XMLA script did worked and I can finally see the assembly being registered. But i haven't tested the any store procs yet. Will update you soon.


Hi Dgosbell,

No, I don't have any other assemblies .
Feb 19, 2013 at 2:58 AM
Furmangg, I tested the store procs and it's working just fine. I tested with ASSP.getCustomDrillthroughMDX. Please let me know if you need me to further test on anything else.
Coordinator
Feb 19, 2013 at 3:26 AM
Edited Feb 19, 2013 at 3:29 AM
Rok1, thanks for testing the XMLA deploy. I've posted the XMLA on the release page in case it helps other users.

One question, can you deploy the SQLQuery assembly fine? I'm hoping it's just the ASSP assembly that's a problem because it has much more complicated dependencies.

And can you try clicking the "Details" button in the dialog where you create the assembly? I'm hoping there will be better error messages there.

Thanks for helping to get to the bottom of this and for reporting the problem. Hope the XMLA is a good workaround.
Coordinator
Feb 19, 2013 at 3:23 PM
Rok1 and stevebot, can you try this troubleshooting step, too?


a) Open regedit and browse to “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Fusion”

b) Create a DWORD value by name “EnableLog” and set its value to 1.

c) Create a DWORD value by name “ForceLog” and set its value to 1.

d) Create a DWORD value by name “LogFailures” and set its value to 1.

e) Create a String value by name “LogPath” and set its value to “c:\Fusion”

f) Create the folder “c:\Fusion”

Close and re-open Management Studio. Start creating the ASSP assembly using the UI (not the XMLA). Right before you click OK, go to c:\Fusion and delete all subfolders. Now in Management Studio, click OK. It should give you the typical "Clr Assembly must have main file specified" error.

Now see if you have the following file:
"C:\Fusion\Default\Ssms.exe\WhereRefBind!Host=(LocalMachine)!FileName=(ASSP.dll).HTM"

If so, open it and see if there's something near the end that says ERR: and gives an error message. If so, post the contents of that file. I'm hoping we'll get a good error message.

Go back to regedit and remove the keys you added to turn off logging.
Feb 21, 2013 at 6:44 PM
*** Assembly Binder Log Entry (2/21/2013 @ 1:38:55 PM) ***

The operation was successful.
Bind result: hr = 0x0. The operation completed successfully.

Assembly manager loaded from: C:\Windows\Microsoft.NET\Framework\v4.0.30319\clr.dll
Running under executable C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe
--- A detailed error log follows.

=== Pre-bind state information ===
LOG: User = --------- "Intentionally deleted"
LOG: Where-ref bind. Location = P:\ASSP 2012 v1.3.6\ASSP.dll
LOG: Appbase = file:///C:/Program Files (x86)/Microsoft SQL Server/110/Tools/Binn/ManagementStudio/
LOG: Initial PrivatePath = NULL
LOG: Dynamic Base = NULL
LOG: Cache Base = NULL
LOG: AppName = Ssms.exe

Calling assembly : (Unknown).

LOG: This bind starts in LoadFrom load context.
WRN: Native image will not be probed in LoadFrom context. Native image will only be probed in default load context, like with Assembly.Load().
LOG: Using application configuration file: C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe.Config
LOG: Using host configuration file:
LOG: Using machine configuration file from C:\Windows\Microsoft.NET\Framework\v4.0.30319\config\machine.config.
LOG: Attempting download of new URL file:///P:/ASSP 2012 v1.3.6/ASSP.dll.
LOG: Assembly download was successful. Attempting setup of file: P:\ASSP 2012 v1.3.6\ASSP.dll
LOG: Entering run-from-source setup phase.
LOG: Assembly Name is: ASSP, Version=1.3.6.0, Culture=neutral, PublicKeyToken=null
LOG: Re-apply policy for where-ref bind.
LOG: Where-ref bind Codebase does not match what is found in default context. Keep the result in LoadFrom context.
LOG: Binding succeeds. Returns assembly from P:\ASSP 2012 v1.3.6\ASSP.dll.
LOG: Assembly is loaded in LoadFrom load context
Coordinator
Feb 21, 2013 at 7:27 PM
Edited Feb 21, 2013 at 7:37 PM
SOLUTION!!!

I think we finally figured out what Rok1 was doing differently. It turns out that he had mapped a P: drive as \servername\sharename and then tried to register the assembly from that mapped shared drive.

When I do this, I'm able to repro the error and I reported it to Microsoft here:
https://connect.microsoft.com/SQLServer/feedback/details/779739/clr-assembly-must-have-main-file-specified-error-when-you-register-an-assembly-from-a-shared-network-drive

You should be able to do that, in my opinion, but it's not working for some reason.

If anyone else is hitting this problem, make sure to copy the ASSP.dll to a local hard drive before trying to register it.
Mar 8, 2013 at 6:36 PM
furmangg, I have tried the solution you recommended and I still am getting the same error. What details would you like me to provide?
Coordinator
Mar 8, 2013 at 6:42 PM
Stevebot, you're not putting ASSP on a shared network drive?

If you can provide info for any and all troubleshooting steps we can get to the bottom of this.
Mar 8, 2013 at 8:50 PM
furmangg wrote:
I provided Rok1 with the XMLA script to create the assembly (which may be a workaround). Stevebot, if you want that script, please feel free to click furmangg and contact me. If this works, we can put the XMLA script as a separate download in the release.
Hi furmangg can you send me the script so I can test this troubleshooting step? Thank you for your help.
Coordinator
Mar 8, 2013 at 10:16 PM
You have your profile set to not be contacted. Can you click furmangg and contact me?
Apr 29, 2013 at 1:39 PM
Edited Apr 29, 2013 at 1:41 PM
Hi,

I had the same issue but I found out what was going on. I had copied the 2012 ASSP.DLL to C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP and tried to register it after unblocking it - no joy. Then I double checked it was unblocked, and sure enough, it wasn't - even if I tried to unblock it repeatedly, it would appear to be working (no error appears when you click OK or Apply in Properties), but it had not unblocked it - clearly this is related to the folder being part of Program Files. I am a server admin by the way.

I then moved the file to my desktop, unblocked it again, went back in to Properties and checked and now it was unblocked - it then registered fine.