c# - How to get the Microsoft SQLCLR Stored Procedure Example to work -

i'm new clr , struggling microsoft tutorial already.
tutorial: how to: create , run clr sql server stored procedure

there example stored procedure insert_currency_cs , test.sql

using system; using system.data; using system.data.sqlclient; using system.data.sqltypes; using microsoft.sqlserver.server;   public partial class storedprocedures {     [sqlprocedure()]     public static void insertcurrency_cs(         sqlstring currencycode, sqlstring name)     {         using (sqlconnection conn = new sqlconnection("context   connection=true"))         {             sqlcommand insertcurrencycommand = new sqlcommand();             sqlparameter currencycodeparam = new sqlparameter("@currencycode", sqldbtype.nvarchar);             sqlparameter nameparam = new sqlparameter("@name", sqldbtype.nvarchar);              currencycodeparam.value = currencycode;             nameparam.value = name;              insertcurrencycommand.parameters.add(currencycodeparam);             insertcurrencycommand.parameters.add(nameparam);              insertcurrencycommand.commandtext =                 "insert sales.currency (currencycode, name, modifieddate)" +                 " values(@currencycode, @name, getdate())";              insertcurrencycommand.connection = conn;              conn.open();             insertcurrencycommand.executenonquery();             conn.close();         }     } } 

and

exec insertcurrency 'aaa', 'currency test' select * sales.currency currencycode = 'aaa' 

to work have add adventureworks2014 guess, added new database project solution can copypasted content (tables , 2 scripts) first project database inside of solution explorer. syntax errors , not recognized option errors in deployment script clrtest.sql @ example these lines:

go :setvar databasename "clrtest" :setvar defaultfileprefix "clrtest" :setvar defaultdatapath "c:\users\torsten\appdata\local\microsoft\visualstudio\ssdt\clrtest" :setvar defaultlogpath "c:\users\torsten\appdata\local\microsoft\visualstudio\ssdt\clrtest" 

the first :setvar line syntax error , databasename not recognized option. here

:on error exit 

and here

:setvar __issqlcmdenabled "true" 

now can maybe explain me how make database project use adventureworks2014 database, tutorial clr stored procedure working when being executed test.sql.

i guess problem don't know how put adventureworks2014 database empty database project. trying sqlschemacompare now, update target (localdb)\projectsv12.clr test. here have problem, if include aw2014fulltextcatalog in upgrade, receive script execution error, , if exclude it, error in script create fulltext index on... additionally error:

error 1 sql72014: .net sqlclient data provider:
msg 9982, level 16, state 100, line 1 cannot use full-text search in user instance.
c:\users\torsten\appdata\local\temp\clr test_1_update3.publish.sql 47 1

i'm not sure right way put database empty database, ms tutorial wasn't vs2013 vs2010...

if have no knowledge of sqlclr, , unfamiliar "sqlcmd mode" in ssms / visual studio (hence syntax errors on lines starting :setvar), should not starting microsoft examples. example, tutorial linked in question states, @ top:

this documentation archived , not being maintained.

so, tutorial page isn't being read , number of things going on here, possibly related version differences in sql server, visual studio, etc. or, there prerequisite steps doing microsoft tutorials haven't been followed.

instead, should review series of articles writing on working sqlclr on sql server central:

stairway sqlclr

free registration required view content on site, worth there incredible amount of free information related working sql server.

the series on sqlclr writing guides through sqlclr , isn't, can (and can't) do, differences between sql server's clr host environment , windows clr host (i.e. "regular" .net environment) , fact sqlclr operates more web app console app / winform app.

the examples in articles step-by-step full explanations of going on. working assemblies provided first several articles 1 shouldn't messing creating sqlclr objects until there basic understanding of environment, given restrictions , nuances of working within sql server dangerous if start creating sqlclr objects without having basis understanding doing , if should doing it.

after that, then (maybe) take @ microsoft examples. either don't need sqlclr (i.e. t-sql fine), or perhaps can't want in t-sql doing in sqlclr won't work due environmental constraints.

---

with regards out-dated tutorial working on, don't see why trying include adventureworks2014 in visual studio / ssdt project. should able ignore existing schema / objects , publish it.