Hello,

I have written a query in SQL which works fine and i get the desired output. But when i try to use the same in windows powershell i get the following error : 

Invoke-Sqlcmd : Invalid length parameter passed to the LEFT or SUBSTRING function.
At line:1 char:11
+ $result = Invoke-Sqlcmd -Query "declare @var int
+           ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Please find below my sql query :

                                                                                      

declare @var int
set @var = (select top 1 logid from com_logline 
where text like '%COMMON_MSG%' order by logid desc)                                                                                                     

declare @temp varchar(max)
declare @print varchar(max)
declare MsgError_cursor CURSOR

for SELECT SUBSTRING(text, CHARINDEX ('SMTP:', text)+4, CHARINDEX (':$:L', text)-CHARINDEX ('SMTP:', text)-4) 
FROM com_logline where logid = 41
and text like '%COMMON_MSG_SEND_ERROR%'
order by lstseqno desc

open MsgError_cursor
fetch next from MsgError_cursor into @temp

WHILE @@FETCH_STATUS = 0
BEGIN

set @print = (SELECT REPLACE((@temp),':$',(char(13) + char(10))));
insert into #temp_emailError values (@print)

FETCH NEXT FROM MsgError_cursor into @temp
END 

DEALLOCATE MsgError_cursor

Please note, I have even tried using a temp table but to no effect. Any help would be much appreciated.Thanks in advance

How are you passing the query ?

What happens if you run like shown below(here string)

$query = @"
declare @var int
set @var = (select top 1 logid from com_logline 
where text like '%COMMON_MSG%' order by logid desc)                                                                                                     

declare @temp varchar(max)
declare @print varchar(max)
declare MsgError_cursor CURSOR

for SELECT SUBSTRING(text, CHARINDEX ('SMTP:', text)+4, CHARINDEX (':$:L', text)-CHARINDEX ('SMTP:', text)-4) 
FROM com_logline where logid = 41
and text like '%COMMON_MSG_SEND_ERROR%'
order by lstseqno desc

open MsgError_cursor
fetch next from MsgError_cursor into @temp

WHILE @@FETCH_STATUS = 0
BEGIN

set @print = (SELECT REPLACE((@temp),':$',(char(13) + char(10))));
insert into #temp_emailError values (@print) 

FETCH NEXT FROM MsgError_cursor into @temp
END 

DEALLOCATE MsgError_cursor
"@

Invoke-Sqlcmd -Query $query

This is how i am passing the query :

$result = Invoke-Sqlcmd -Query "declare @var int
                                        set @var = (select top 1 logid from com_logline 
                                        where text like '%COMMON_MSG%' order by logid desc)                                                                                                    
                                                                                     
                                        declare @temp varchar(max)
                                        declare @print varchar(max)
                                        declare MsgError_cursor CURSOR

                                        for SELECT SUBSTRING(text, CHARINDEX ('SMTP:', text)+4, CHARINDEX (':$:L', text)-CHARINDEX ('SMTP:', text)-4) 
                                        FROM com_logline where logid = 41
                                        and text like '%COMMON_MSG_SEND_ERROR%'
                                        order by lstseqno desc

                                        open MsgError_cursor
                                        fetch next from MsgError_cursor into @temp
																				
                                        WHILE @@FETCH_STATUS = 0
                                        BEGIN

                                        set @print = (SELECT REPLACE((@temp),':$',(char(13) + char(10))));									 

                                        FETCH NEXT FROM MsgError_cursor into @temp
                                        END 

                                        DEALLOCATE MsgError_cursor" -ServerInstance "QTCDB050PA\DBSQL050PA" -Database "CODA_PROD"
        write-host $result.print

Okay try using here string - the error is expected the way you used the query

Note: I haven't checked your SQL Query - But below sample code may give some hint for you!

$query = @"
declare @var int
set @var = (select top 1 logid from com_logline 
where text like '%COMMON_MSG%' order by logid desc)                                                                                                     

declare @temp varchar(max)
declare @print varchar(max)
declare MsgError_cursor CURSOR

for SELECT SUBSTRING(text, CHARINDEX ('SMTP:', text)+4, CHARINDEX (':$:L', text)-CHARINDEX ('SMTP:', text)-4) 
FROM com_logline where logid = 41
and text like '%COMMON_MSG_SEND_ERROR%'
order by lstseqno desc

open MsgError_cursor
fetch next from MsgError_cursor into @temp

WHILE @@FETCH_STATUS = 0
BEGIN

set @print = (SELECT REPLACE((@temp),':$',(char(13) + char(10))));
insert into #temp_emailError values (@print) 

FETCH NEXT FROM MsgError_cursor into @temp
END 

DEALLOCATE MsgError_cursor
"@

Invoke-Sqlcmd -Query $query -ServerInstance "QTCDB050PA\DBSQL050PA" -Database "CODA_PROD"

Still getting the same error . I am sure the SQL query is fine because when i run the same query in SQL server 2012, i get the expected output.

Put in a file and execute in SQS.  Then run like this:

Invoke-SqlCmd -Inputfile c:\yourfile.sql -ServerInstance yourServer -database yourDB

The error is no longer there, but when i am trying to output the file to a text file, all i am getting are dots in place of the actual result.

Invoke-SqlCmd -Inputfile E:\CodaEmailErrorQuery.sql -ServerInstance "QTCDB050PA\DBSQL050PA" -Database "CODA_PROD" | Format-Table -HideTableHeaders | out-file E:\CodaEmailErrorQuery_output.txt

Where i should be getting 4 rows of data (expected output, verified by running just the sql query in ssms 2012 ), i am getting 4 rows of dots.

Please help!

The output of Format-Table is for human consumption only.  Use Export-Csv.