Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
System.Data.SqlClient.SQLConnection Open() raises exception in some win7 RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello,
    I have been using this code without problems since 2 years ago more or less.

    Our 25 machines for this project have Windows 7 professional 64 bits, windows up to date, with powershell 4.0 and it's Net framework up to date as 4.5 full is needed.

    I am asking support for this one as I am astonished with the problem I am facing. Although I finally found a work around, it still can prevent me from sleeping well.

    Here is the code snippet that I was using succesfully:

           $ConnectionString = "Data Source=" + $SQLHost + ";Initial Catalog=" + $SQLDatabase + ";Connection Timeout=0;Integrated Security=False; User ID = " + $SQLAdminUserName + ";Password=" + $SQLAdminPassword + ";"

    $conn=new-object System.Data.SqlClient.SQLConnection 

    $conn.ConnectionString=$ConnectionString 

    $conn.Open()

    I am only copying this piece of code, as it is where I amb getting the error, thus the problem.

    Since early this year I noticed some of them are not executing properly the script, and the error comes from trying to Open the connection:

    Exception calling "Open" with "0" argument(s): "Value cannot be null.
    Parameter name: value"
    At C:\Program Files\ascv\ascv_bentley_start.ps1:83 char:5
    +     $conn.Open()
    +     ~~~~~~~~~~~~
        + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
        + FullyQualifiedErrorId : ArgumentNullException

    Again I clarify that the SAME script is running in all 25 pc's, only giving error on 3 of them.
    Could not find any differences with windows updates, tried updating PS to 5.0, tried comparing assembly properties which where the same:

    $conn.getType().Assembly.Location

    GAC    Version        Location
    ---    -------        --------
    True   v4.0.30319     C:\Windows\Microsoft.Net\assembly\GAC_64\System.Data\v4.0_4.0.0.0__b77a5c561934e089\System.Data.dll

    The workaround consisted in compiling the Ps1 script to exe with ps2exe utility from a machine where it was working, then deploy the executable for the 3 'wrong' machines.

    I would like to know where I was wrong or missed the obvius.

    Thank you.

    • Moved by Bill_Stewart Friday, July 27, 2018 8:26 PM This is not "debug/fix/rewrite my script for me" forum
    Tuesday, May 22, 2018 12:06 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    You have a variable that is null.  You have to debug the code to find out why the variable is null.

    This is a better way to create complex strings. It will also output the results so you can see if any values are missing.

    $tmplt = 'Data Source={0};Initial Catalog={1};Connection Timeout=0;Integrated Security=False;User ID={2};Password={3};'
$ConnectionString = $tmplt -f $SQLHost,$SQLDatabase,$SQLAdminUserName,$SQLAdminPassword
Write-Host $ConnectionString -Fore Green

    \_(ツ)_/

    Tuesday, May 22, 2018 12:24 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thank you jrv for your answer.

    Please keep in mind that this script has been working correctly on all 25 win7.64b pc's that it was installed since July 2016. The prior lines in the script are clearly defining the variables for the connection. Already tried to change those and hard-code it (without variables)...

    And it has been early 2018 when only 3 of them have changed its behaviour with the SAME code, to raise that error and stop working.

    The script is configured to be triggered by WMI events, which should not matter; executes as system user. But on all pc's with the SAME configuration and ps1 code.

    Windows updates seems to me not involved as nearly all those 25 are up to date, via wsus (windows offline updates). 


    Tuesday, May 22, 2018 2:38 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Then you have an issue that is not caused by the script.

    \_(ツ)_/

    Tuesday, May 22, 2018 2:45 PM