Error when running script

Ask your PowerShell-related questions, including questions on cmdlet development!
Forum rules
Do not post any licensing information in this forum.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
This topic is 1 year and 3 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked
Nigelh57
Posts: 1
Last visit: Mon Dec 19, 2022 4:32 am

Error when running script

Post by Nigelh57 »

Here is the code

Code: Select all

##############################################
$SQLMerge = "MERGE $SQLDatabase.dbo.$SQLTable Target
USING $SQLTempDatabase.dbo.$SQLTempTable Source
ON (Target.key_id = Source.key_id)
WHEN MATCHED 
     THEN UPDATE
     SET    Target.value_deleted_at = Source.value_deleted_at,
            Target.value_storage_quota = Source.value_storage_quota,
            Target.value_lti_context_id = Source.value_lti_context_id,
            Target.value_created_at = Source.value_created_at,
            Target.value_updated_at = Source.value_updated_at,
            Target.value_workflow_state = Source.value_workflow_state,
            Target.value_sortable_name = Source.value_sortable_name,
            Target.value_avatar_image_url = Source.value_avatar_image_url,
            Target.value_avatar_image_source = Source.value_avatar_image_source,
            Target.value_avatar_image_updated_at = Source.value_avatar_image_updated_at,
            Target.value_short_name = Source.value_short_name,
            Target.value_last_logged_out = Source.value_last_logged_out,
            Target.value_pronouns = Source.value_pronouns,
            Target.value_merged_into_user_id = Source.value_merged_into_user_id,
            Target.value_locale = Source.value_locale,
            Target.value_name = Source.value_name,
            Target.value_default_time_zone = Source.value_default_time_zone,
            Target.value_uuid = Source.value_uuid,
            Target.meta_ts = Source.meta_ts

 WHEN NOT MATCHED BY TARGET
 THEN INSERT (key_id,value_deleted_at,value_storage_quota,value_lti_context_id,value_created_at,value_updated_at,value_workflow_state,value_sortable_name,value_avatar_image_url,value_avatar_image_source,value_avatar_image_updated_at,value_short_name,value_last_logged_out,value_course_template_id,value_created_at,value_updated_at,value_pronouns,value_merged_into_user_id,value_locale,value_name,value_default_time_zone,value_uuid,meta_ts)
      VALUES (Source.key_id,Source.value_deleted_at,Source.value_storage_quota,Source.value_lti_context_id,Source.value_created_at,Source.value_updated_at,Source.value_workflow_state,Source.value_sortable_name,Source.value_avatar_image_url,Source.value_avatar_image_source,Source.value_avatar_image_updated_at,Source.value_short_name,Source.value_last_logged_out,Source.value_pronouns,Source.value_created_at,Source.value_updated_at,Source.value_pronouns,Source.value_merged_into_user_id,Source.value_locale,Source.value_name,Source.value_default_time_zone,Source.value_uuid,Source.meta_ts);"
# Running the INSERT Query
Invoke-SQLCmd -Query $SQLMerge -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword 
Here is the error message

Code: Select all

Invoke-SQLCmd : Incorrect syntax near '.'. 
 Msg 102, Level 15, State 1, Procedure , Line 1.
At line:30 char:1
+ Invoke-SQLCmd -Query $SQLMerge -ServerInstance $SQLInstance -Username ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Error when running script

Post by jvierra »

You are using variables from PowerShell in you SQL. That will not work. All elements in a SQL statement must resolve to text. Table objects are no allowed.
User avatar
gareth.jacobs
Posts: 34
Last visit: Wed Jan 24, 2024 7:43 pm

Re: Error when running script

Post by gareth.jacobs »

You can do SQL calls, you just have to frame the code a slightly different way, as follows:
  1. function Exec-get_system_information {
  2.    
  3.     $Query = @"
  4.        DECLARE @MacAddress varchar(20);
  5.        EXEC [$ProgramName].[dbo].[get_system_information];
  6. "@
  7.     ExecuteQuery -QueryString $Query
  8. }
Here this code sets up the $Query variable to parse to the function, ExecuteQuery (this code not shown). The global variable $ProgramName is replaced before being parsed - in this instance executing an SQL stored procedure that was created in the SQL database by an earlier piece of PowerShell code.

Note that the variable $Query uses double quotes so that any embedded variables get evaluated first. Just remember that after any variables have been resolved, what is left inside $Query, in this instance, can only be pure SQL statements. If it is a legal SQL statement - it will work.

With this method you can execute some really complex SQL from PowerShell, the same as if working from the SQL Management Console.
This topic is 1 year and 3 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked