title: Why does my (large) string get cut off when inserting into SQL Server database from Powershell?
body: <p>I am trying to insert a large string (49,443 characters) into my SQL Server database from a Powershell script, but for some reason it gets cut off after 43,679 characters. This is my script:</p>
  
  <pre><code>$SqlServer = "MyServer";
  $SqlCatalog = "MyDatabase";
  $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
  $SqlConnection.ConnectionString = "MyConnectionString"
  $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
  
  $LargeString = gc C:\SomeTextFile.txt -Raw
  
  $SqlQuery = "INSERT INTO MyTable (LargStringColumn) VALUES ('" + $LargeString "')"
  
  $SqlCmd.CommandText = $SqlQuery
  $SqlCmd.Connection = $SqlConnection
  $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
  $SqlAdapter.SelectCommand = $SqlCmd
  $DataSet = New-Object System.Data.DataSet
  $nRecs = $SqlAdapter.Fill($DataSet)
  </code></pre>
  
  <p>The database column is of type varchar(max), which can hold 2GB of data. And my research shows that a powershell string can hold around the same amount of data. And the data that I am actually trying to insert is only a small fraction of this (97KB).</p>
  
  <p>If I do</p>
  
  <pre><code>Write-Host $LargeString
  </code></pre>
  
  <p>I can see all the data. The same is true if I use Out-File. I am losing data somehow when my insert command is ran. The data DOES appear in the database, it's just not the entire string.</p>
  
  <p>Why does my string keep getting cut off when it is inserted into the database?</p>