Powershell script to do the inserts into a sql table is failing.

 function Do-FilesInsertRowByRow ([Data.SqlClient.SqlConnection] $OpenSQLConnection) {
     $sqlCommand = New-Object System.Data.SqlClient.SqlCommand
     $sqlCommand.Connection = $sqlConnection
     $sqlCommand.CommandText = "SET NOCOUNT ON; " + 
         "INSERT INTO dbo.dir (Database_Name,Dir_Path,Script_Out_Time) " + 
         "VALUES ('$DbName','$Dir',$creation_time);"
     #Invoke-Sqlcmd -Query $CommandText  
 # Open SQL connection (you have to change these variables)
 $DBServer = "server1"
 $DBName = "db1"
 $dir = "D:\Script_Out\"+$DBName
 $creation_time =  Get-Item $dir | Select LastWriteTime
 $sqlConnection = New-Object System.Data.SqlClient.SqlConnection
 $sqlConnection.ConnectionString = "Server=$DBServer;Database=$DBName;Integrated Security=True;"
 # Call the function that does the inserts.
 Do-FilesInsertRowByRow ($sqlConnection)
 # Close the connection.
 if ($sqlConnection.State -eq [Data.ConnectionState]::Open) {

The error that I am getting is: Exception calling "ExecuteScalar" with "0" argument(s): "Must declare the scalar variable "@"." At line:16 char:5 + $sqlCommand.ExecuteScalar() + ~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : SqlException

more ▼

asked Feb 04, 2015 at 02:31 PM in Default

avatar image

1 1 1 3

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

If you change the line




you will see what is being executed - on my pc that came up with

 SET NOCOUNT ON; INSERT INTO dbo.dir (Database_Name,Dir_Path,Script_Out_Time) 
       VALUES ('sandpit','C:\temp\sandpit',@{LastWriteTime=02/04/2015 14:36:47});

that @ symbol that the error was complaining about - is the array/hashtable that is returned as the 'value' of the $creation_time variable

so change

 $creation_time =  Get-Item $dir | Select LastWriteTime


 $creation_time =  (Get-Item $dir).LastWriteTime

and then make sure you have single quotes around the variable in the function :

 $sqlCommand.CommandText = "SET NOCOUNT ON; " + 
    "INSERT INTO dbo.dir (Database_Name,Dir_Path,Script_Out_Time) " + 
    "VALUES ('$DbName','$Dir','$creation_time');

and it should work

more ▼

answered Feb 04, 2015 at 03:12 PM

avatar image

Kev Riley ♦♦
65.3k 48 63 81

Even after making all those changes I am getting errors. I changed the code with the insert statements to make it work...

Thanks, I will try this method and will post if I get it execute.

Feb 04, 2015 at 07:59 PM anuk
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Feb 04, 2015 at 02:31 PM

Seen: 664 times

Last Updated: Feb 04, 2015 at 07:59 PM

Copyright 2016 Redgate Software. Privacy Policy