none
VS2013: SQL CLR create script problems RRS feed

  • Question

  • I have created a CLR for SQL and would like to have VS2013 create a install script that can be copy-pasted to SSMS, preferably without having to change anything in the process, but can accept to change the database name.

    My problems so far are:

    1) every script has the "DROP DATABASE" line (even when asked not to include it).
    2) build fails because I have selected to create the SP in a different schema (called main). VS2013 cannot handle such a situation (only dbo works).
    3) every script wants to run in single user mode (again, not selected).
    4) every script assumes that the target database is empty (i.e. has not detection for already present SP/CLR and optionally drops this before creating new).

    The only usable thing from the script is actually the "CREATE ASSEMBLY" line and the "ALTER ASSEMBLY" line and the last one is optional, i.e. not necessary.

    In VS2010, the deploy script had was pretty much perfect and I would like to know if I can make VS2013 create a similar deplay script.

    One note: My development pc does NOT have access to the server where the code is to be deployed which is why I have to copy the deploy script to server and load it there.

    Thursday, March 19, 2015 11:43 AM

Answers

  • Hi Jack

    Sorry for being so short-tempered, but I was really disappointed that VS2013 only has live database deployment and that I had to fix this myself. Anyway, I have created this little post-build script that does what I need. I may have done a few things in a strange way, but it seems to work fine. This may help others with same/similar frustrations:

    set TargetName=$(TargetName)

    set outfile=$(ProjectDir)$(OutputPath)$(TargetName)_Deploy.sql

    REM Find the procedure name (includes Schema).
    FOR /f "tokens=3" %%a IN ('findstr /c:"CREATE PROCEDURE" "%TargetName%_Create.sql"') DO SET  procedurename=%%a

    echo -- Select the database > "%outfile%"
    echo USE main >> "%outfile%"
    echo GO >> "%outfile%"
    echo. >> "%outfile%"
    echo -- Step1 - Remove stored procedure if already exist and re-create stored procedure. >> "%outfile%"
    echo IF OBJECT_ID('%procedurename%') IS NOT NULL >> "%outfile%"
    echo     DROP PROCEDURE %procedurename% >> "%outfile%"
    echo GO >> "%outfile%"
    echo.  >> "%outfile%"
    echo -- Step2 - Remove assembly >> "%outfile%"
    echo IF ASSEMBLYPROPERTY('%TargetName%', 'MvID') IS NOT NULL >> "%outfile%"
    echo     DROP ASSEMBLY [%TargetName%] >> "%outfile%"
    echo GO >> "%outfile%"
    echo.  >> "%outfile%"
    echo -- Step 3,4,5 - Load assembly, add source, create stored procedure >> "%outfile%"

    powershell -command "(get-content %TargetName%_Create.sql | out-string) | select-string -Pattern '(?sm)((CREATE|ALTER) ASSEMBLY|CREATE PROCEDURE).*?GO' -AllMatches | Foreach {$_.Matches} | Foreach {$_.Value} | out-file -filePath \"%outfile%\" -encoding ASCII -append"

    Monday, March 23, 2015 3:35 PM

All replies

  • I found that when I used the wrong schema (dbo) and selected "Publish..." and published to (localdb)\ProjectsV12, I would get a script looking pretty much like the one from VS2010.

    I am still not able to get the Schema to be main (build always fails).

    Thursday, March 19, 2015 12:38 PM
  • I tried adding a Schema file to the project that basically said "CREATE SCHEMA main" and while it allowed me to build the project and generate a publish sql script, the script is even worse than before. Now it drops the old procedure from dbo and create the new in main. It also unconditionally creates the schema main.

    Microsoft apparently expect all developers to be directly linked to the production servers. Weird assumption. Either that, or I have missed something obvious.

    I am connection to localdb solely because I cannot connect to the production server. Nor the test server.

    Thursday, March 19, 2015 12:48 PM
  • Ok, I found a way to generate a working deplay script almost automatically.

    I added a build script that created the schema.

    Then I published to localdb.

    Then I made a project change (insert space, delete space) and build the project again.

    On the subsequent publish, I published not to localdb (but specified the localdb as the connection string), but to a sql script. This created a script that would drop the old procedure/clr using the correct schema and created new clr/procedure with the correct schema. The only thing I have to edit is the database name. Not sure if I can avoid that.

    That was not obvious to me...

    Thursday, March 19, 2015 1:45 PM
  • Hi Bernhard Ege,

    Glad to know that you have found a workaround for this issue.

    You know that this forum is to discuss the VS IDE, just to make this issue clearly, do you mean that this issue is related to the SQL Database project like the following documents?

    http://www.codeproject.com/Tips/841439/Create-Run-Debug-and-Deploy-SQL-CLR-Function-with

    http://www.codeproject.com/Articles/825831/SQL-Server-Database-Development-in-Visual-Studio

    If so, I suggest you post the new issue to the SSDT forum here:

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?forum=ssdt

    Best Regards,

    Jack


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, March 20, 2015 6:16 AM
    Moderator
  • Jack,

    My problem was with the VS2013 IDE. As I stated, my VS2013 does not have access to the server SQL database and as such, the Publish... method in the IDE is pretty much crap for offline server publishing. The publish method REQUIRES a database and is unable to publish without. I reverted to publish to the localdb database and managed to generate a change script that sort of works, but is inferior to what VS2010 generates.

    Your two links to guides for deploying CLR to databases both requires direct access from VS2013 to the target database. These guides are worthless to me.

    I have SSDT installed, but I don't know if I am using it. SSDT isn't mentioned anywhere in the windows I open. But as far as I have read, SSDT also requires a direct database link to work.

    There isn't even an option to have VS2013 output a single SQL script with only the assembly creation and procedure creation in it. I could have made a post build script that would create a proper CLR install script woth that. Now, I have to make scripts to extract these lines and wrap them with the necessary checks and drops that VS2010 does, but are missing from VS2013.

    Friday, March 20, 2015 9:07 AM
  • Hi Bernhard Ege,

    Thanks for your friendly response.

    >> Your two links to guides for deploying CLR to databases both requires direct access from VS2013 to the target database. These guides are worthless to me.

    Actually the links provided in my previous reply were used to make sure that whether this issue is related to the Sql Server database project.

    The SSDT forum is used to discuss the database project, so if this issue is related to the Database project, and to prove that whether it is related to the VS IDE or others, I feel that the SSDT expert could repro this issue better.

    >> I have to make scripts to extract these lines and wrap them with the necessary checks and drops that VS2010 does, but are missing from VS2013.

    As you said that it the VS2010 works well, not the real database experts, but to really repro this issue, maybe you could share me the detailed steps (to understand this issue better, please also share some screen shots )about how you achieve it in VS2010. So I could repro it with VS2010 and VS2013.

    Of course, if we make sure that it is the VS2013 IDE issue, you can submit this feedback to Microsoft Connect feedback portal: http://connect.microsoft.com/VisualStudio/feedback/CreateFeedback.aspx, Microsoft engineers will evaluate them seriously. Thanks for your understanding.

    If there's any concern, please feel free to let me know.

    Best Regards,

    Jack


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, March 23, 2015 1:42 AM
    Moderator
  • Hi Jack

    Sorry for being so short-tempered, but I was really disappointed that VS2013 only has live database deployment and that I had to fix this myself. Anyway, I have created this little post-build script that does what I need. I may have done a few things in a strange way, but it seems to work fine. This may help others with same/similar frustrations:

    set TargetName=$(TargetName)

    set outfile=$(ProjectDir)$(OutputPath)$(TargetName)_Deploy.sql

    REM Find the procedure name (includes Schema).
    FOR /f "tokens=3" %%a IN ('findstr /c:"CREATE PROCEDURE" "%TargetName%_Create.sql"') DO SET  procedurename=%%a

    echo -- Select the database > "%outfile%"
    echo USE main >> "%outfile%"
    echo GO >> "%outfile%"
    echo. >> "%outfile%"
    echo -- Step1 - Remove stored procedure if already exist and re-create stored procedure. >> "%outfile%"
    echo IF OBJECT_ID('%procedurename%') IS NOT NULL >> "%outfile%"
    echo     DROP PROCEDURE %procedurename% >> "%outfile%"
    echo GO >> "%outfile%"
    echo.  >> "%outfile%"
    echo -- Step2 - Remove assembly >> "%outfile%"
    echo IF ASSEMBLYPROPERTY('%TargetName%', 'MvID') IS NOT NULL >> "%outfile%"
    echo     DROP ASSEMBLY [%TargetName%] >> "%outfile%"
    echo GO >> "%outfile%"
    echo.  >> "%outfile%"
    echo -- Step 3,4,5 - Load assembly, add source, create stored procedure >> "%outfile%"

    powershell -command "(get-content %TargetName%_Create.sql | out-string) | select-string -Pattern '(?sm)((CREATE|ALTER) ASSEMBLY|CREATE PROCEDURE).*?GO' -AllMatches | Foreach {$_.Matches} | Foreach {$_.Value} | out-file -filePath \"%outfile%\" -encoding ASCII -append"

    Monday, March 23, 2015 3:35 PM
  • Hi Bernhard Ege,

    Thank you for sharing us the solution here. It will be very beneficial for other community members who have similar questions.

    If you have any feedbacks, please also feel free to submit a connect report for it.

    Best Regards,

    Jack


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, March 24, 2015 1:34 AM
    Moderator