0

Within PowerShell, I want to run the Invoke-DbaQuery dbatools command and use a UNC path with the -File parameter:

Invoke-DbaQuery -SQLInstance $SQL_host -Database $DatabaseName -File "\\file_svr\scriptdir\userlist.sql"

The command runs without error but doesn't produce any output. Does anyone know if this is possible? If so, what am I missing?

Ken
  • 77
  • 9
  • I tried adding this to my script: set-location -Path "\\file-srv0\SHARED\I T\22 SQL Server" but it produces set-location : Access is denied At line:1 char:1 + set-location -Path "\\file-srv0\SHARED\I T\22 SQL Ser ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [Set-Location], UnauthorizedAccessException + FullyQualifiedErrorId : System.UnauthorizedAccessException,Microsoft.PowerShell.Commands.SetLocationCommand – Ken Sep 23 '21 at 20:33
  • Opened ticket 2109230040007978 with Microsoft support to try and get this figured out. – Ken Sep 23 '21 at 20:41

1 Answers1

0

It turns out that the call has been working but the output was not visible. This is because the script has code like this:

DECLARE UserCursor CURSOR FOR 
SELECT  
  'CREATE USER ' + SPACE(1) + QUOTENAME([name]) + ' FOR LOGIN ' + QUOTENAME([name]) + ' WITH DEFAULT_SCHEMA = ' + QUOTENAME([default_schema_name]) + ';'
  AS [-- SQL STATEMENTS --]
FROM    sys.database_principals AS rm
WHERE [type] IN ('U', 'S', 'G') -- windows users, sql users, windows groups
AND [name] <> 'dbo'
AND [name] <> 'guest'
AND default_schema_name is not null

OPEN UserCursor

FETCH NEXT FROM UserCursor INTO @addUserScript
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @addUserScript
    FETCH NEXT FROM UserCursor INTO @addUserScript
END

CLOSE UserCursor 
DEALLOCATE UserCursor;

The solution then was to call the script like this:

Invoke-DbaQuery                  `
   -SQLInstance $HCM_SQL_host    `
   -Database $Refresh_database   `
   -Verbose 4>&1                 `
   -File "\\file-srv0\scriptdir\Userlist.sql" | `
    out-string -Width 250 | Out-File -FilePath $Create_Users_File_Fullname

Note the line "-Verbose 4?&1 `" that redirects the verbose output (i.e. the PRINT output) back to the standard output and, ultimately, the file.

Ken
  • 77
  • 9