I have a problem trying to compile a stored prodedure from within a stored procdure. I create a very simple script file (D:tmptest_proc.sql) with compilable code to demonstrate the issue:

CREATE PROCEDURE new_rtn
AS
BEGIN
 PRINT 'Hi!';
END

Next I try to compile this file using the following procedure:

CREATE PROCEDURE sp_compile
AS
BEGIN
   DECLARE
    @fn              NVARCHAR(35)   = 'sp_compile'
   ,@cmd             NVARCHAR(500)
   ,@db              NVARCHAR(60)  = DB_NAME()
   ,@server          NVARCHAR(90)  = @@SERVERNAME
   ,@script_file     NVARCHAR(500) = 'D:\tmp\test_proc.sql'
   ,@error_file      NVARCHAR(500) = 'D:\tmp\errors.txt'
   ,@q_tst_rtn_nm    NVARCHAR(60)  = 'sp_new_rtn'
   ,@ret             INT = NULL

   PRINT '000: starting';

   -- Test setup
   DROP PROCEDURE IF EXISTS sp_new_rtn;

   -- CREATE the DOS cmd
   SET @cmd = 
      CONCAT
      (
            'SQLCMD.EXE'
         ,' -S ',@server
         ,' -E -d ',@db
         ,' -i ',@script_file
         ,' -o ',@error_file
      );

   -- Print the DOS testable version - remove the escape  and change the error file for comparison
   PRINT CONCAT('060: @cmd=
',REPLACE(REPLACE(@cmd,'errors.txt','errors2.txt'),'\',''));
   -- Run the DOS cmd to create the procedure

   EXEC @ret = master..xp_cmdshell @cmd;
   SET @cmd = 'echo %ERRORLEVEL% > D:tmpout.txt'; -- dump output to out.txt
   EXEC master..xp_cmdshell @cmd;

   -- Check the postconditions
   PRINT '065: checking postconditions...';

   -- POST 01: rtn exists in db or EX 63200, 'failed to create the procedure'
   IF NOT EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE SPECIFIC_NAME = 'new_rtn')
   BEGIN
      PRINT ' ERROR: 071:  [new_rtn] does not exist';
      -- list the error line
      SELECT * FROM OPENROWSET(BULK 'D:tmperrors.txt', SINGLE_CLOB) F;
      THROW 63200, 'failed to create procedure', 1;
   END

   -- Processing complete
   PRINT '800: Processing complete';
END
GO
/*
EXEC sp_compile;
*/

This fails with the error:
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : The client cannot connect to the server because the requested instance was not available. Use SQL Server Configuration Manager to make sure the SQL Server instance is configured correctly. .

BUT if I run the command directly from dos
SQLCMD.EXE -S DEVI9SQLEXPRESS -E -d Tg -i D:tmptest_proc.sql -o D:tmperrors2.txt it works.

My server is DevI9SQLEXPRESS.

To help with reproducing the error the compile routine prints the dos equivalent command line replacing the with and writing to a different error file for comparison.

I am using Windows 10.0.19045 and
Microsoft SQL Server 2022 (RTM-GDR) (KB5035432) – 16.0.1115.1 (X64) Mar 15 2024 01:13:46 Copyright (C) 2022 Microsoft Corporation Express Edition (64-bit) on Windows 10 Home 10.0 (Build 19045: ) (Hypervisor).

I guess there must be some configuration I need to sort out – any ideas?

Thanks.

Summary and extra things I tried:

So the strategy I am using is to create the routine to be compiled in a script file
and compile that from a stored procedure using xp_cmdshell.

This fails because of access issues however when I run the command from a dos box it works fine.
this has to be a permissions thing I guess. I am an admin on my machine but am not running SSMS as admin … but when I do run SSMS as admin I still get teh same issue:

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : The client cannot connect to the server because the requested instance was not available. Use SQL Server Configuration Manager to make sure the SQL Server instance is configured correctly. .