How to convert a CLR project from 2008 to 2012

Question

I currently have a clr project for sql 2008 databases, it's compatible with sql 2008 and net framework 3.5. I am having difficulties converting the project to 2012, I still need to use the clr assembly projects (querying active directory, querying wmi, file system, parsing transact sql statements using scriptdom etc). Some of these assemblies are safe and some are unsafe. All these assemblies are consumed by a main database project and I deploy through database project. I tried couple methods to convert this into sql 2012, first if I do not do anything but restore the database from sql 2008 to sql 2012 most of the clr works but there are issues with file system (system.management and system.directoryservices) clr, they generate error, therefore that path did not go too far. I cannot create a clr project referencing .net 40 against a sql 2012 from the current visual studio projects , sql 2012 is not an option in the list.

When I tried converting to data tier application I was not able to create references to DLL's, I must be doing something wrong there but I was not able to figure it out.

Any advise would be appreciated.

Thank you

---

Gokhan Varol

Answer 1

To convert a SQLCLR project to be compatible with SQL Server 2012 you need to install the SQL Server Data Tools http://msdn.microsoft.com/en-us/data/gg427686. It’s free, integrates into VS2010 SP1, and provides services (including auto-deploy and upgrade of SQLCLR projects that used to run on SQL Server 2008). See http://www.sqlskills.com/BLOGS/BOBB/post/Adventures-in-autodeploy-SQL-Server-2012-SSDT-and-SQLCLR.aspx.

 

Cheers, Bob

 

"Gokhan Varol" wrote in message news:edfbfffb-93c4-4f7b-8eb9-5a3737413ec8...

I currently have a clr project for sql 2008 databases, it's compatible with sql 2008 and net framework 3.5. I am having difficulties converting the project to 2012, I still need to use the clr assembly projects (querying active directory, querying wmi, file system, parsing transact sql statements using scriptdom etc). Some of these assemblies are safe and some are unsafe. All these assemblies are consumed by a main database project and I deploy through database project. I tried couple methods to convert this into sql 2012, first if I do not do anything but restore the database from sql 2008 to sql 2012 most of the clr works but there are issues with file system (system.management and system.directoryservices) clr, they generate error, therefore that path did not go too far. I cannot create a clr project referencing .net 40 against a sql 2012 from the current visual studio projects , sql 2012 is not an option in the list.

When I tried converting to data tier application I was not able to create references to DLL's, I must be doing something wrong there but I was not able to figure it out.

Any advise would be appreciated.

Thank you

---

Gokhan Varol

Answer 2

I opened the project with SQL server data tools, before I did that I made sure all the referenced dll's were mapped to .net 4.0 and clr projects were mapped to .net 4.0. Datatools opened the project and did not prompt for anything, and I went and change the main project setting to "Data-tier Application" from SQL 2008, and compiled the project. Now I am getting hundredths of the errors below, am I doing something wrong?

Thank you

Schema Objects\Schemas\dbo\Programmability\Functions\TrimMultiline.sql(1,1): Error DAC05000: The 'CREATE FUNCTION' statement is not supported in a data-tier application. Remove the statement before rebuilding.
Schema Objects\Schemas\dbo\Programmability\Functions\TokenizeSQL.sql(1,1): Error DAC05000: The 'CREATE FUNCTION' statement is not supported in a data-tier application. Remove the statement before rebuilding.
Schema Objects\Schemas\dbo\Programmability\Functions\GetProcCodeAfterAS.sql(1,1): Error DAC05000: The 'CREATE FUNCTION' statement is not supported in a data-tier application. Remove the statement before rebuilding.
Schema Objects\Schemas\dbo\Programmability\Functions\GetProcCodeSplitByAS.sql(1,1): Error DAC05000: The 'CREATE FUNCTION' statement is not supported in a data-tier application. Remove the statement before rebuilding.
Schema Objects\Schemas\dbo\Programmability\Functions\ProcessSQLforAudit.sql(1,1): Error DAC05000: The 'CREATE FUNCTION' statement is not supported in a data-tier application. Remove the statement before rebuilding.
Schema Objects\Schemas\dbo\Programmability\Functions\ParseColumnDependenciesSQL.sql(1,1): Error DAC05000: The 'CREATE FUNCTION' statement is not supported in a data-tier application. Remove the statement before rebuilding.
Schema Objects\Schemas\dbo\Programmability\Functions\ParseColumnDependenciesXML.sql(1,1): Error DAC05000: The 'CREATE FUNCTION' statement is not supported in a data-tier application. Remove the statement before rebuilding.
Schema Objects\Schemas\RE\Programmability\Functions\RegExSplit.function.sql(1,1): Error DAC05000: The 'CREATE FUNCTION' statement is not supported in a data-tier application. Remove the statement before rebuilding.
Schema Objects\Schemas\RE\Programmability\Functions\RegExMatches.function.sql(1,1): Error DAC05000: The 'CREATE FUNCTION' statement is not supported in a data-tier application. Remove the statement before rebuilding.
Schema Objects\Schemas\FS\Programmability\Functions\ReadLine.function.sql(1,1): Error DAC05000: The 'CREATE FUNCTION' statement is not supported in a data-tier application. Remove the statement before rebuilding.
Schema Objects\Schemas\FS\Programmability\Functions\GetVolumeInfo_CLR.function.sql(1,1): Error DAC05000: The 'CREATE FUNCTION' statement is not supported in a data-tier application. Remove the statement before rebuilding.

.......................

---

Gokhan Varol

Answer 3

Hi Gokhan,

 

I’m also totally confused by this statement: “and I went and change the main project setting to "Data-tier Application" from **SQL 2008**”. I have no idea what you mean here, unless you mean “changed the main project setting for deployment to from “Data-tier Application" from the SSDT tool. You can *connect to a SQL Server 2008 database and import a Data-Tier Application*, I’m not sure about deploying an SSDT data-tier application to SQL Server 2008. I thought the point of what you were doing was to use SQL Server 2012 (i.e. convert a SQL Server 2008 project for SQLCLR) to be deploy-able on SQL Server 2012. In addition, SQL Server 2008 SQLCLR does not support .NET 4.0. And never will, you need to move to SQL Server 2012 for SQLCLR .NET 4.0 support.

 

You might also try asking this question on the SQL Server Data Tools forum (http://social.msdn.microsoft.com/Forums/en-US/ssdt/threads), as well as asking it here, for assistance converting and migrating between the plethora of different project types that exist once you install SSDT over an existing VS2010 environment.

 

Cheers,

Bob

 

 

 

"Gokhan Varol" wrote in message news:61c3ae9a-16e5-42f3-9416-ef4f58b800c9...

I opened the project with SQL server data tools, before I did that I made sure all the referenced dll's were mapped to .net 4.0 and clr projects were mapped to .net 4.0. Datatools opened the project and did not prompt for anything, and I went and change the main project setting to "Data-tier Application" from SQL 2008, and compiled the project. Now I am getting hundredths of the errors below, am I doing something wrong?

Thank you

Schema Objects\Schemas\dbo\Programmability\Functions\TrimMultiline.sql(1,1): Error DAC05000: The 'CREATE FUNCTION' statement is not supported in a data-tier application. Remove the statement before rebuilding.
Schema Objects\Schemas\dbo\Programmability\Functions\TokenizeSQL.sql(1,1): Error DAC05000: The 'CREATE FUNCTION' statement is not supported in a data-tier application. Remove the statement before rebuilding.
Schema Objects\Schemas\dbo\Programmability\Functions\GetProcCodeAfterAS.sql(1,1): Error DAC05000: The 'CREATE FUNCTION' statement is not supported in a data-tier application. Remove the statement before rebuilding.
Schema Objects\Schemas\dbo\Programmability\Functions\GetProcCodeSplitByAS.sql(1,1): Error DAC05000: The 'CREATE FUNCTION' statement is not supported in a data-tier application. Remove the statement before rebuilding.
Schema Objects\Schemas\dbo\Programmability\Functions\ProcessSQLforAudit.sql(1,1): Error DAC05000: The 'CREATE FUNCTION' statement is not supported in a data-tier application. Remove the statement before rebuilding.
Schema Objects\Schemas\dbo\Programmability\Functions\ParseColumnDependenciesSQL.sql(1,1): Error DAC05000: The 'CREATE FUNCTION' statement is not supported in a data-tier application. Remove the statement before rebuilding.
Schema Objects\Schemas\dbo\Programmability\Functions\ParseColumnDependenciesXML.sql(1,1): Error DAC05000: The 'CREATE FUNCTION' statement is not supported in a data-tier application. Remove the statement before rebuilding.
Schema Objects\Schemas\RE\Programmability\Functions\RegExSplit.function.sql(1,1): Error DAC05000: The 'CREATE FUNCTION' statement is not supported in a data-tier application. Remove the statement before rebuilding.
Schema Objects\Schemas\RE\Programmability\Functions\RegExMatches.function.sql(1,1): Error DAC05000: The 'CREATE FUNCTION' statement is not supported in a data-tier application. Remove the statement before rebuilding.
Schema Objects\Schemas\FS\Programmability\Functions\ReadLine.function.sql(1,1): Error DAC05000: The 'CREATE FUNCTION' statement is not supported in a data-tier application. Remove the statement before rebuilding.
Schema Objects\Schemas\FS\Programmability\Functions\GetVolumeInfo_CLR.function.sql(1,1): Error DAC05000: The 'CREATE FUNCTION' statement is not supported in a data-tier application. Remove the statement before rebuilding.

.......................

---

Gokhan Varol

Answer 4

I reasked the question on sql data tools forums, but the issue may seem that I did not have data tools installed even though I have sql 2012 installed, I am installing data tools right now (takes a while), I'll post if that fixes the problem shortly.

How can I convert a CLR project from SQL 2008 to SQL2012?

---

Gokhan Varol

Answer 5

Hi Gokhan,

Did this work for you?

Thanks,

-Sam

Answer 6

yep. To customize the custom function declaration on the clr project I removed the ddl generation, and only used publish database but not dacpac and it worked like a charm.

---

Gokhan Varol