powershell - I am writing a script to run in a terminal using sqlcmd. There are variables my script expects but what if one or m

admin管理员组

文章数量:1332345

I have this script (my_script.sql):

SET NOCOUNT ON;
DECLARE @SchemaName NVARCHAR(128);
DECLARE @TableName NVARCHAR(128);

SET @SchemaName = '$(SchemaName)';
SET @TableName = '$(TableName)';

IF @SchemaName IS NULL or @SchemaName = ''
BEGIN
    SET @SchemaName = 'dbo';
END

PRINT 'The full table name is: ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName);

When I call this from the command line using:

sqlcmd -S my_db_ip -U username -P myPassword -d my_db_name 
       -i my_script.sql -v SchemaName dbo TableName Users

I get this output in my terminal:

The full table name is: [dbo].[Users]

I want, however if the script is deployed and the user does not know what the SchemaName is, they can let it use the default 'dbo'.

However, when it is called using

sqlcmd -S my_db_ip -U username -P myPassword -d my_db_name 
       -i my_script.sql -v  TableName Users

(Note there was no SchemaName provided)

I get the following output to the terminal:

'SchemaName' scripting variable not defined.
The full table name is: [@SchemaName)].[Users]

How do I fix this so that, if the variable is not included in the sqlcmd parameters, I do not get the not defined message and the full table name is [dbo].[Users]?

I have tried changing where I default the value but that still gives the variable not defined message and it still gives the same output:

If I change the variable check:

IF @SchemaName IS NULL OR @SchemaName = '' OR @SchemaName = '$(SchemaName)'
BEGIN
    SET @SchemaName = 'dbo';  -- Default to 'dbo' if not provided
END

...

I get:

'SchemaName' scripting variable not defined.  
The full table name is: [dbo)].[Users]

This being that the error message still is included.

I have no clue how to solve this.

I have this script (my_script.sql):

SET NOCOUNT ON;
DECLARE @SchemaName NVARCHAR(128);
DECLARE @TableName NVARCHAR(128);

SET @SchemaName = '$(SchemaName)';
SET @TableName = '$(TableName)';

IF @SchemaName IS NULL or @SchemaName = ''
BEGIN
    SET @SchemaName = 'dbo';
END

PRINT 'The full table name is: ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName);

When I call this from the command line using:

sqlcmd -S my_db_ip -U username -P myPassword -d my_db_name 
       -i my_script.sql -v SchemaName dbo TableName Users

I get this output in my terminal:

The full table name is: [dbo].[Users]

I want, however if the script is deployed and the user does not know what the SchemaName is, they can let it use the default 'dbo'.

However, when it is called using

sqlcmd -S my_db_ip -U username -P myPassword -d my_db_name 
       -i my_script.sql -v  TableName Users

(Note there was no SchemaName provided)

I get the following output to the terminal:

'SchemaName' scripting variable not defined.
The full table name is: [@SchemaName)].[Users]

How do I fix this so that, if the variable is not included in the sqlcmd parameters, I do not get the not defined message and the full table name is [dbo].[Users]?

I have tried changing where I default the value but that still gives the variable not defined message and it still gives the same output:

If I change the variable check:

IF @SchemaName IS NULL OR @SchemaName = '' OR @SchemaName = '$(SchemaName)'
BEGIN
    SET @SchemaName = 'dbo';  -- Default to 'dbo' if not provided
END

...

I get:

'SchemaName' scripting variable not defined.  
The full table name is: [dbo)].[Users]

This being that the error message still is included.

I have no clue how to solve this.

• powershell
• error-handling
• variable-assignment
• sqlcmd

Share Improve this question Follow edited Nov 21, 2024 at 4:52 marc_s 756k184184 gold badges1.4k1.4k silver badges1.5k1.5k bronze badges asked Nov 20, 2024 at 21:23 dfashimpaurdfashimpaur 9911 silver badge44 bronze badges 3

• Have you tried resolving the variables TableName and SchemaName at script generation time, using a template engine? Decades ago, when I was doing SQL, we had no tools like the ones you have today. So I built a tool in VMS DCL to make scripts from a template and a data list. I later learned that this tool is called a template engine. When I learned powershell, I rewrote the tool in PS code as an exercise. It's not hard to do. – Walter Mitty Commented Nov 21, 2024 at 13:11
• If you are interested in my template engine, I can post it as an answer. – Walter Mitty Commented Nov 21, 2024 at 13:13
• @WalterMitty I am interested in any solution and yours sounds like a great way to get past my issue. If you don't mind posting your template engine in PS, I am eager to see if it will work for me too – dfashimpaur Commented Nov 21, 2024 at 13:30

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

I have a template engine that I wrote several years ago to combine a simple template with a CSV file containing actual values. It's possible that this tool could be adapted to your case.

Here is my template engine:

 <#
.NOTES
    Script: Expand-Csv    Rev:  3.2
    By:     DGC           Date: 2-21-19
.SYNOPSIS
    Generates multiple expansions of a template,
    driven by data in a CSV file.
.DESCRIPTION
    This function is a table driven template tool. 

    It generates output from a template and
    a driver table.  The template file contains plain
    text and embedded variables.  The driver table 
    (in a csv file) has one column for each variable, 
    and one row for each expansion to be generated.
#>
function Expand-csv {
    [CmdletBinding()]
    Param (
       [Parameter(Mandatory=$true)] [string] $driver,
       [Parameter(Mandatory=$true)] [string] $template
    )
    Process {
       Import-Csv $driver | % {
           $_.psobject.properties | % {Set-variable -name $_.name -value $_.value}
           Get-Content $template | % {$ExecutionContext.InvokeCommand.ExpandString($_)} 
       }
    }
}

Here is a demo of my template engine, with two examples. One of the examples generates a reiterative SQL script with the variable data resolved.

    Example 1:  CSV File
    --------------------
    
NAME,ID
John,18
Dave,19
Carmen,20
Eric,21
Tom,22
Lisa,23
Kyle,24
   
    CSV Data viewed as a table
    

NAME   ID
----   --
John   18
Dave   19
Carmen 20
Eric   21
Tom    22
Lisa   23
Kyle   24


    
    Example 1:  Template
    --------------------
    
java -jar --create -user $name -id $id -file D:/HR/$name-$id-form.pdf
    
    Example 1:  Results
    -------------------
java -jar --create -user John -id 18 -file D:/HR/John-18-form.pdf
java -jar --create -user Dave -id 19 -file D:/HR/Dave-19-form.pdf
java -jar --create -user Carmen -id 20 -file D:/HR/Carmen-20-form.pdf
java -jar --create -user Eric -id 21 -file D:/HR/Eric-21-form.pdf
java -jar --create -user Tom -id 22 -file D:/HR/Tom-22-form.pdf
java -jar --create -user Lisa -id 23 -file D:/HR/Lisa-23-form.pdf
java -jar --create -user Kyle -id 24 -file D:/HR/Kyle-24-form.pdf
    
    
    
    Example 2:  CSV File
    --------------------
    
privs,table,user
ALL,Employees,DBA
READ,Employees,Analyst
"READ, WRITE", Employees, Application
ALL,Departments,DBA
READ,Departments,"Analyst, Application"
    
    CSV DAta viewed as a table
    

privs       table       user                
-----       -----       ----                
ALL         Employees   DBA                 
READ        Employees   Analyst             
READ, WRITE Employees   Application         
ALL         Departments DBA                 
READ        Departments Analyst, Application


    
    Example 2:  Template
    --------------------
    
grant $privs 
   on $table 
   to $user;

    
    Example 2:  Results
    -------------------
    
grant ALL 
   on Employees 
   to DBA;

grant READ 
   on Employees 
   to Analyst;

grant READ, WRITE 
   on Employees 
   to Application;

grant ALL 
   on Departments 
   to DBA;

grant READ 
   on Departments 
   to Analyst, Application;

I haven't yet figured out how to apply this to your case. Maybe you can take it from here.

This tool is avaible on Github.
www.github/dcressey/Expand-csv

本文标签：