0

I want to create a CLR assembly, and here's my code:

use [master]
go

exec sp_configure 'show advanced options', 1
go

reconfigure
go

exec sp_configure 'xp_cmdshell', 1
go

reconfigure
go

execute sp_configure 'clr enabled', 1
go

reconfigure
go

execute sp_configure 'clr strict security', 0
go

reconfigure
go

declare @path varchar(256) = '';
declare @table table
(
    Value varchar(256)
)
insert into @table execute xp_cmdshell 'echo %CompanyProjectsRoot%'
set @path = 
(
    select top 1 Value 
    from @table
)
set @path = @path + '\Framework\Packages\Company.ClrIntegration.dll'
create assembly CompanyClrIntegration from @path
go

And I receive this error:

CREATE ASSEMBLY failed because it could not read from the physical file '%CompanyProjectsRoot%\Framework\Packages\Company.ClrIntegration.dll': 50(The request is not supported.).

But if I change the path to absolute path C:\Copmany\Framework\Packages\Company.ClrIntegration.dll it works.

And I know that %CompanyProjectsRoot% is already defined for the entire machine using this command:

setx CompanyProjectsRoot C:\Company /m

And I have also restarted SQL Server Management Studio. But still it does not expand environment variable.

How can I fix this?

5
  • Did you restart SQL Server since you configured the variable? SSMS is irrelevant, it is just a client tool. Also note that the variable need to be on the server machine, not the client. Nov 18 '21 at 9:16
  • @Ah cloud, what is output of this table @table?
    – Biju jose
    Nov 18 '21 at 9:41
  • @TiborKaraszi, such a good advice. Yep, it worked. Please send your comment as an answer, so that I can accepts it.
    – AH Cloud
    Nov 18 '21 at 12:32
  • @AH Cloud, I'm glad it helped! Nov 18 '21 at 13:23
  • You'd be better defining it as a PATH environment variable, then you wouldn't need to mess around with xp_cmdshell at all Nov 21 '21 at 3:43
3

Did you restart the SQL Server service since you configured the environment variable? I believe that environment variables for MSSQL are read at start-up time for the database engine.

Also, SSMS is irrelevant, it is just a client tool. And, note that the variable need to be on the server machine, not the client.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.