SQL Server : ExecuteReader doesn't throw error

Question

I have the following code running, which should basically just execute a SQL statement against my database and if there is any error, the script should shout "ERROR!"

When I am running the script, with an invalid SQL statement in the test.sql file, the exception is correctly caught by the CATCH clause and "ERROR!" is written.

Test.sql file looks like this:

SELECT * FRRRROM [dbo].[testtable]

But, when I add a comment to the test.sql file, it doesn't throw the "ERROR!"-message anymore.

Test.sql file now looks like this:

-- my comment
SELECT * FRRRROM [dbo].[testtable]

I don't get it why. Any suggestions?

$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "server='myServer';database='myDatabase';trusted_connection=true;MultipleActiveResultSets=True;"
$Connection.Open()

$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection 

$sql = Get-Content 'C:\Test\test.sql'

$Command.CommandText = $sql
Try
{
    $Reader = $Command.ExecuteReader()
}
Catch 
{
    echo 'ERROR!!!!'
    Exit
}
$Connection.Close()

try $sql = Get-Content 'C:\Test\test.sql' | Out-String Do you get an error then? Difference in your second example $sql is an array and not just a newline delimited string. — Matt
– Matt, Commented Oct 7, 2015 at 15:12
Thank you very much! Solved the problem. Didn't knew that my example returned an array! Thx again! you made my day! — Esteban P.
– Esteban P., Commented Oct 7, 2015 at 15:22

Matt · Accepted Answer · 2015-10-07 15:23:57Z

2

I still would have expected an error but the main difference between your first and second example is you have another line. Get-Content returns a string array. With one line it would be just seen as a string. Easy way to be sure of that is to convert the text into one whole new line delimited string.

$sql = Get-Content 'C:\Test\test.sql' | Out-String

You could use -join or something else like that but I find Out-String is very simple and compatible with all versions of PowerShell.

answered Oct 7, 2015 at 15:23

Matt

47.1k9 gold badges90 silver badges125 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Esteban P. Over a year ago

Thanks again! additional question: Whats the best way to determine the error risen? reading the SQL Code of the execution. Sorry, i'm quite new into that topic

Matt Over a year ago

@EstebanP. Inside your catch you could look at $error[0].Exception which contains the exception message captured. Could be vague though. It depends on what System.Data.SQLClient.SQLCommand is willing to explain

Matt · Accepted Answer · 2015-10-07 15:43:33Z

0

As user Matt mentioned:

The code returns an array to the variable $sql

$sql = Get-Content 'C:\Test\test.sql'
$Command.CommandText = $sql

after adding | Out-String it's working correctly, as returning a string

$Command.CommandText = Get-Content 'C:\Test\test.sql'| Out-String

edited Oct 7, 2015 at 15:43

Matt

47.1k9 gold badges90 silver badges125 bronze badges

answered Oct 7, 2015 at 15:25

Esteban P.

2,8203 gold badges31 silver badges45 bronze badges

Collectives™ on Stack Overflow

SQL Server : ExecuteReader doesn't throw error

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related