SQL CLR Deployment and Error Resolution: Question and Answer

Q.1. What Will Happen if Any T-SQL in The Project Mentions an Object in The Database That is Not in Your Project?

Ans: Let us explain this with the help of an example. Choose the SQLCLRDemo project node in the Solution Explorer and select "Build | Build SQLCLRDemo" from the main menu to begin building the project. The "Build" option, present in the shortcut menu of the project node or the Shift+F6 keyboard shortcut, is equivalent to UI selections (after selecting the project node).

If you are using the SQLCLRDemo sample code demo project, you should see a message indicating that the build failed, and the following error will be displayed in the Error List window.

Double-click an error in the Error List window. A code editor window with a T-SQL deployment script generated by SSDT will open. It contains a CREATE TRIGGER command for the trgUpdatePerson trigger and a red wave under the database name to which the trigger is applied, Person.Person. Because the T-SQL is correct, you may wonder why SSDT would report an error.

SQL Server Database projects are designed to replicate the databases into which their contents will be deployed. Although you can create projects that contain only a subset of a database, such as the assets for a SQL CLR assembly, SSDT will stop you if any T-SQL in the project references a database object that is not also in your project. The deployment script in your project refers to the database "Person" because the SqlTrigger attribute referred to the trgUpdatePerson CLR trigger refers to it. However, your project has no T-SQL script that states that table. As a result, according to SSDT's criteria, the T-SQL is incorrect.

Q.2. What is The Solution to Remove The Error Caused by Adding an Object not in Your Project?

Ans: You could fix this by adding a script for the "Person.Person" table and then adding scripts piecemeal for any objects on which the table depends. When doing so, it will allow the project to be built, but it would still be a workaround, which you should prevent. The solution is to import the "AdventureWorks2012" table into the project; this way, your project will reflect the database's full definition. "Importing" the database means having SSDT discover the structure of the database and create scripts for each object. Consider doing some online SQL certifications to learn more.

Q.3. Show, Using an Example, How to Perform an Import Operation.

Ans: To perform an import operation, follow these steps:

• Close Visual Studio, open Windows Explorer, and navigate to the source code directory containing the project file (not the solution file) and the project's contents (e.g., C:DemosVSSQLCLRDemoSQLCLRDemo).
• Find and delete the SQLCLRDemo.dbmdl file, then reopen the solution in Visual Studio. This preliminary step is required to enable the Import Database functionality we require; if it is not carried out after an unsuccessful build, the Import Database option will be greyed out.
• Choose the SQLCLRDemo project node in the Solution Explorer once more, then right-click it and choose "Import | Database" from the shortcut menu (or "Project | Import | Database" from the main menu). This option will open the Import Database dialogue box.
• Choose the "AdventureWorks2012" database table from the Source Database Connection combo box. Press the New Connection button, state a connection to the "AdventureWorks2012" table on localhost, and press OK.
• Now press the Start button. The Import Database dialogue box's Summary screen will appear, and the import process will begin.
• When the Finish button appears, click it to complete the import process. Now re-create the project. This time, the build should succeed, and you should be able to deploy your assembly. 

Q.4. How to Deploy Your Assembly Using Visual Studio?

Ans: After completing a successful build, you can publish the database table and deploy the assembly. Now, from the main menu, choose "Build | Publish SQLCLRDemo" (or, in the Solution Explorer, right-click the SQLCLRDemo project node and choose "Publish" from the shortcut menu); this will open the Publish Database dialogue box.

To open the standard Connection Properties dialogue box, click the Edit button next to the read-only Target Database Connection text box. Build a connection pointing to your target server to utilize Windows Authentication, and to the "AdventureWorks2012" database (do not accept the SQLCLRDemo database name), then click OK. To implement your CLR assembly to the database, return to the Publish Database dialogue box and click the Publish button.

If "C:Demos" is the sample code parent directory, you can deploy the Class Library project version by running the following T-SQL statement from within a query pane:

CREATE ASSEMBLY SQLCLRDemo FROM 'C:DemosVSSQLCLRDemoManualSQLCLRDemobinDebugSQLCLRDemo.dll' WITH PERMISSION_SET = SAFE;

Learn more about the deployment process of an assembly by enrolling in an online SQL certification.

Q.5. What does The Authorization Clause do While Deploying an Assembly Using Visual Studio?

Ans: The AUTHORIZATION clause allows you to mention a name or role to which the assembly's ownership is assigned. The current user's authorization is used by default, and because you are possibly logged in as dbo for AdventureWorks2012, the clause is unnecessary in this case.

Q.6. What does The With Permission_set Clause do While Deploying an Assembly Using Visual Studio?

Ans: The WITH PERMISSION SET clause allows you to specify the security permissions that your assembly must have. The WITH PERMISSION SET clause, like the AUTHORIZATION clause, is technically unnecessary in this case because SAFE is the default PERMISSION SET value exercised when a CREATE ASSEMBLY command is executed. Regardless, including both clauses is a good practice. You can learn more about it by doing an Online SQL certification.

Q.7. If an Assembly has Dependencies on Other Assemblies, What does SQL Server do?

Ans: Suppose your assembly is dependent on another assembly. In that case, SQL Server checks to see if those assemblies have already been added to the database and, if so, verifies that their ownership is the same as the specified assemblies. SQL Server looks for dependent assemblies in the same folder as the specified assembly if they still need to be added to the database. Suppose it discovers any dependent assemblies in that directory. In that case, it loads them and gives them the same ownership as the primary assembly. The CREATE ASSEMBLY command will fail if the dependent assemblies are not found in that folder or the global assembly cache (GAC).

Q.8. Can You Provide a String Expression Rather Than a Literal in The FROM Clause of The Code Segment Used in The Previous Example to Deploy The Assembly Using Visual Studio?

Ans: Yes, in the FROM clause, you can use a string expression instead of a literal one, which opens up some interesting data-driven possibilities. For example, you could get an assembly path reference from a database table. It is also possible to provide an assembly directly inline by using a binary stream instead of a file specification in the FROM clause. This is accomplished by mentioning a varbinary literal value or [removed]or a comma-separated list of varbinary values or expressions, if dependent assemblies must be specified) containing the actual binary content of your assembly (or assemblies).This enables the configuration of a database table, including any CLR assemblies it consists of, to be entirely scripted without requiring actual assembly files to be distributed. The binary stream could be embedded in the script or retrieved from a database table using an expression. If you want to learn more about assembly, consider doing an SQL certificate course.

Q.9. How to Deploy an Assembly into Your Database Through SSMS?

Ans: You can upload the assembly into the table interactively from SSMS, using Visual Studio deployment and the T-SQL CREATE ASSEMBLY statement. Simply right-click in the Object Explorer on the servername/Databases/AdventureWorks2012/Programmability/Assemblies node (where servername is the actual name of your server), and then select "New Assembly" from the shortcut menu.

In the "Path To Assembly" text box, type the assembly path and file name, or use the Browse button to specify it interactively. Inside the "Assembly Owner" text box, using the ellipsis button if required, and the "Permission Set" combo box, you can specify AUTHORIZATION and WITH PERMISSION SET details, respectively.Whatever deployment method you use, once your assembly is added to your database, it becomes a part of that table and its underlying .mdf file. This means that when your table is backed up and restored or implemented, any assemblies within it are moved along with the data and do not need to be added manually as a subsequent step.

Q.10. How to Deploy The Stored Procedures?

Ans: When the SQL Server Database Project version of the sample code is deployed, Visual Studio handles the deployment of all stored procedures. This is due to the SqlProcedure attribute being applied to the methods in the StoredProcedures class, present in the spTest.cs file. The SqlProcedure attribute receives an optional Name parameter, which contains the name of the actual callable stored procedure. If the Name parameter is not specified, the name of the .NET method is employed as the stored procedure name.

Only Visual Studio uses the SqlProcedure attribute when auto-deploying SQL CLR assemblies. As a result, it does not appear in the Class Library project's source code. Deploying the stored procedures from that version of the source code necessitates using the EXTERNAL NAME clause to specify the assembly, the fully qualified class name specifier, and the method name. For example, to laden the Class Library version of spContacts, use the following command:

CREATE PROCEDURE [dbo].[spContacts]

    @ContactTitle [nvarchar](30) = NULL

AS

EXTERNAL NAME [SQLCLRDemo].[SQLCLRDemo.StoredProcedures].[spContacts]

The preceding command states that the spContacts method in the StoredProcedures class in the loaded assembly with the T-SQL name "SQLCLRDemo" be registered as a CLR stored procedure referable under the name "spContacts". The class name specifier will change to "SQLCLRDemo.StoredProcedures" if the CLR stored procedure has been written in Microsoft Visual Basic.NET instead of C#. This would necessitate the following changes to the deployment of T-SQL code.

Q.11. Where do all The Required CREATE PROCEDURE Commands for The Class Library Project Version of The Example Code Reside?

Ans: The CreateObjects.sql script inside the SSMS project supplied with the sample code of the chapter on the book's companion website contains all required CREATE PROCEDURE SQL commands for the Class Library project version of the code. You must execute that script to use the SQL CLR entities deployed in the Class Library project. Before running the script, change the CREATE ASSEMBLY command at the top to point to the location on your system where the assembly is installed. By default, the script looks forward to the assembly in your PC's C:DemosVSSQLCLRDemoManualSQLCLRDemobinDebug folder.

Q.12. Are Class Specifier and Method Name in The EXTERNAL NAME Clause Case-Sensitive?

Ans: It is critical to understand that the class specifier and method name inside the EXTERNAL NAME clause are case-sensitive, and this applies even to assemblies created in Visual Basic.NET. SQL Server looks for methods in your assemblies, not in your source code. In other words, it searches Microsoft Intermediate Language (MSIL) code rather than Visual Basic.NET or C# source code. SQL Server must be case-sensitive because it looks within an assembly for a specific class and method.

It makes no difference that SQL Server is not case-sensitive by default (though it once was) or that Visual Basic.NET is not a case-sensitive language! If you try to register a method and get an error saying it can't be found in the assembly, double-check that the case in your command matches the case in your source code.

Q.13. What does The TestStoredProcs.Sql Script File do?

Ans: The SSMS project's TestStoredProcs.sql script file will execute both CLR stored procedures (spContactCount and spContacts). When you run the script, the single-valued result of the spContactCount stored procedure should appear first, and the results of the spContacts stored procedure will appear below the spContactCount result. The "Starting Data Dump" status message must be seen on the Messages or on the Results tab if you're utilizing the Results To Text option in SSMS. After all rows have been fetched, the "Data Dump Complete" message should appear.The TestStoredProcs.sql script code can be used to test your stored procedures. If you want to learn more about testing stored procedures, then consider doing SQL training.

Conclusion

We have addressed the questions related to deploying assemblies, resolving errors, and executing stored procedures in SQL CLR projects. We have also emphasized the importance of case-sensitivity in EXTERNAL NAME clauses and provided guidance on deploying and testing the code using Visual Studio and SSMS. Additionally, we highlighted the use of scripts and the significance of T-SQL commands in the deployment process. Consider furthering your knowledge with SQL certification courses to enhance your skills in SQL development.