Trouble Deploying CLR functions
-
I have some CLR functions that were compiled and under .Net framework 2.0 for SQL Server 2008. I modified and recompiled them under framework 4.0 to run on SQL Server 2014. When I try to publish the assemblies to the server from VS 2012, VS says the publish was successful, but the assemblies never get created on the server.
Any help would be appreciated.
Thanks,
-
You can run CLRs compiled and under .Net framework 2.0 on a SQL 2014 instance.
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
-
fxferguson (3/3/2016)
I have some CLR functions that were compiled and under .Net framework 2.0 for SQL Server 2008. I modified and recompiled them under framework 4.0 to run on SQL Server 2014. When I try to publish the assemblies to the server from VS 2012, VS says the publish was successful, but the assemblies never get created on the server.Any help would be appreciated.
Thanks,
Apologies for the stupid question but did you refresh the display after you created the assemblies?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks for the response Jeff, I did refresh the view on the DB server. After reviewing the result script generated by VS, I noticed that there was no "CREATE ASSEMBLY" statement in the script. I'm pretty new to CLR creation and deployment. Is that normal? It seems the script really didn't do anything. Hints the successful publish message.
-
Thanks for the response Jeff, I did refresh the view on the DB server. I reviewed the script generated by Visual Studio and noticed that the "CREATE ASSEMBLY" statement wasn't in the script. It seemed like script really didn't to anything but set the database variable.
-
Thanks Alan,
The CLR assemblies I'm trying to deploy performs a lot of external file manipulation. I attempted to use them on a 2014 server and they would bomb. The assemblies that do not use external file manipulation works fine under framework 2.0.
This is message I get when I tried to deploy the 2.0 CLRs on a 2014 server:
"CREATE ASSEMBLY for assembly 'CLRDirectoryBrowser' failed because the assembly is built for an unsupported version of the Common Language Runtime."
-
fxferguson (3/4/2016)
Thanks Alan,The CLR assemblies I'm trying to deploy performs a lot of external file manipulation. I attempted to use them on a 2014 server and they would bomb. The assemblies that do not use external file manipulation works fine under framework 2.0.
This is message I get when I tried to deploy the 2.0 CLRs on a 2014 server:
"CREATE ASSEMBLY for assembly 'CLRDirectoryBrowser' failed because the assembly is built for an unsupported version of the Common Language Runtime."
Sorry I never replied, I just noticed that you replied to me.
That's weird; I am by no means a .NET or CLR expert. Hopefully someone else can offer a little insight.
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
-
I've also got some CLR's that i deploy to 2008R2 or 2012/2014 .
like you, i've got multiple codebases, for R2,2012 or 2014.
for the 2012/2014 stuff, I've also not been able to use VS to deploy,.
For 2014, what I've been doing is is selecting this selection to create a script, and running the script manually.
this process creates a {DLLname}.sql script in the bin directory.
I actually edited it a bit and made it so it runs in SSMS instead of SQMCMD, but that's how i deploy.
i suspect the right way is to create a dacpac, but I've not really been doing that...i'm more of a script monkey.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
fxferguson (3/4/2016)
The CLR assemblies I'm trying to deploy performs a lot of external file manipulation.Hi there. File manipulation alone should not be a problem. What classes / methods are you using, exactly? I have done quite a bit with the standard File, FileSystemInfo, DirectoryInfo, etc classes and have not run into any problem with Assemblies running fine between SQL Server 2005 through 2014 (the exact same Assemblies -- no recompiles to handle different CLR versions).
I attempted to use them on a 2014 server and they would bomb. The assemblies that do not use external file manipulation works fine under framework 2.0.
Please be more specific about what "bomb" means. Please provide exact error messages. Not the full strack trace, but from the top "Msg" SQL Server error line down a few lines into the stack trace.
This is message I get when I tried to deploy the 2.0 CLRs on a 2014 server:
"CREATE ASSEMBLY for assembly 'CLRDirectoryBrowser' failed because the assembly is built for an unsupported version of the Common Language Runtime."
Was this a Visual Studio error or SQL Server error? What is the error number? Compiling for Framework version 2.0, 3.0, and 3.5 should have little to no impact when deploying on SQL Server 2012 or newer which are all linked to CLR version 4.0, which handles .NET Framework versions 4.0 and newer (4.5, 4.5.2, 4.6, etc). I compile most of the Assemblies in my SQL# library against .NET Framework version 2.0 so that one code base can be supported equally across all versions of SQL Server that support CLR Integration (i.e. from SQL Server 2005 onward). So far I have not seen any issues, and there are over 270 functions / procs in there now, hence plenty of opportunity to run into a problem with this methodology. This is not to say that I have used all available .NET methods, and so there are some that might behave differently, but even if they did, that wouldn't result in this issue. Hence, I am thinking that this is either
* a Visual Studio generated error, in which case you need to enable the deploy option to ignore version compatibility, or
* potentially a setting to "require" a particular version of the .NET Framework. Though I did not think that would be embedded in the Assembly itself; usually it is specified in an app.Config file.
After reviewing the result script generated by VS, I noticed that there was no "CREATE ASSEMBLY" statement in the script. I'm pretty new to CLR creation and deployment. Is that normal?
That is normal in terms of the standard SQL deployment script generated upon doing a Publish since publishing is incremental. So you likely last made a change to something that did not change the Assembly itself, so the deployment script had no need to recreate or alter the Assembly. If you want a full deploy script, you need to enable the "Create Script (.sql file)" that will not be incremental. But it will attempt to drop/recreate the DB. Personally, I don't use the built-in SSDT deployment process outside of pushing changes to my dev server. I build manual deploy script that do just what they should. But for more info in general, please see the articles I just publish here on SQL Server Central that address doing SQLCLR development with Visual Studio:
Stairway to SQLCLR Level 6: Development Tools Intro[/url]. That is really part 1 of a 3 part article that is a complete walk through of dealing with this and other issues. But if you are new to this, you might want to read the series from the beginning :-).
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR -
Thanks Lowell,
I found I had to do the same thing. Create the SQL scripts to deploy them on the server. I was just wondering if there was something I was doing wrong that wouldn't allow the assemblies to deploy using VS. I guess it's one of those Microsoft mysteries to add to the X-Files.
-
Lowell (3/21/2016)
for the 2012/2014 stuff, I've also not been able to use VS to deploy.Do you get an error when trying to deploy to 2012/2014? Obviously SQL Server 2014 wasn't around when VS 2012 came out, but the deployment process is handled by SSDT and that has been updated. Have you updated your install of SSDT? if not, doing so might help. I don't see why this couldn't be made to work properly. But still, not sure getting it to work for 2012/2014 would get you anywhere as there are other deployment nuances to contend with.
i suspect the right way is to create a dacpac
I wouldn't think so. A dacpac is just the current data model of the target DB plus your changes. Doing a deployment from the dacpac via SqlPackage.exe should get you the exact same deployment script as Visual Studio generates, since Visual Studio farms that out to SSDT (i.e. its all the same process). There might be a configuration option to set differently, but I don't think that a dacpac file would change anything.
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR -
The error is a SQL server error. VS doesn't give an error at all. VS says the deployment was successful. I will check and try the deployment setting you mentioned and let you know how it worked out.
Thank you for your response. As I stated before I'm pretty new at the CLR thing. Everyone's help has been greatly appreciated.
-
fxferguson (3/21/2016)
The error is a SQL server error. VS doesn't give an error at all. VS says the deployment was successful. I will check and try the deployment setting you mentioned and let you know how it worked out.Well, then again, can you please post the entire exact error messages? Also, please specify what classes/methods you are using. From what you have described so far, it does not seem like you should be getting an error. In fact, I still use VS 2012 for some projects and have never experienced this error, yet I compile to .NET Framework 2.0 or 3.5 and do most of my testing on SQL Server 2012 and 2014.
Have you updated your SSDT install? If not, you might want to do that, though it shouldn't be the source of this error if the error is coming from SQL Server. Still, would be good to be on a recent version of SSDT.
Also, is it possible that you are compiling for a particular architecture (32 vs 64 bit) and are trying to deploy to the other? That is a long-shot, though. Typically the error that you are getting is for going in the opposite direction: compiling for a .NET Framework version that works with CLR 4.0 (i.e. 4.0, 4.5.x, 4.6.x) and then trying to load that onto SQL Server 2005, 2008, or 2008 R2. It is a bit strange, to say the least, that you are getting that error going in the direction that is specifically supported 😉
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply