CODEWITHSUNDEEP
sql-servervisual-studiosql-server-data-toolssqlproj
Joe Enos
Joe Enos

Reputation: 40413

Multiple CLR schemas in Visual Studio SSDT project

I'm trying to have a single sqlproj project in Visual Studio 2022, representing my SQL Server database.

When creating a CLR stored proc or function in C#, the SqlFunction or SqlProcedure attribute has a Name property, but not a schema. It seems the entire project will use the default schema. I'd like to be able to have more than one schema with CLR items.

I've found a couple different ways of moving procs from one schema to another after deployment, but is there any way to get it right in the first place?

For example, by default, if I create a MyProc procedure, I can reference it as dbo.MyProc from a regular proc. But I want that to live in the other schema. Even if I change schemas as a post-deploy step, then my regular procs with EXEC [other].[MyProc] will not compile properly in Visual Studio since it thinks it's referencing a non-existent procedure.

I don't want multiple projects because I want fully-functional schema compare and publish steps for the whole database.

Upvotes: 1

Views: 269

Answers (0)

Related Questions