0

Powershell command (whitespace added for readability):

Invoke-Sqlcmd -ServerInstance "$db" -Database "$dbname" -Username "xxxx" -Password "xxx"
              -Query "select logmessages from log
                      where TIME_STAMP BETWEEN '2019-01-11 09:00:00' and '2019-01-11 09:02:00'
                      AND logmessages like '%string%'
                      order by TIME_STAMP"
              -QueryTimeout 0 -ConnectionTimeout 0
| Export-Csv "C:\Users\xxxxx\Desktop\PS\data.csv" -Append

Each row result is expected to be around 01 mb. The problem is I'm seeing partial data.

The table has 17 columns and I'm trying to fetch data from one of the column (type:varchar; length: -1) which typically stores xml data close to the size of 1mb per record. When I run a select statement against that column, only partial data is returned.

closed as unclear what you're asking by Mr.Brownstone, Colin 't Hart, mustaccio, Max Vernon, Josh Darnell Jan 14 at 20:28

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    what do you mean by partial data? Can you put more info? table structure, example of data, expected results and actual results? We can then help you understand what's going on. – JohnG Jan 11 at 19:15
  • Hi John, the table has 17 columns and I'm trying to fetch data from one of the column (type:varchar; length: -1) which typically stores xml data close to the size of 1mb per record. When I run a select statement against that column, only partial data is returned. – SKC Jan 11 at 20:43
  • It seems to me that an XML column in a .csv file is a problem waiting to happen. Have you looked at the .CSV file to confirm that the data being put in it is both what you'd expect, and formatted as you'd expect? – RDFozz Jan 11 at 22:03
  • The xml data is getting written but its just that the data is incomplete. – SKC Jan 12 at 2:11
2

The column you are having trouble with us a varchar(Max) column. This is one of several "LOB data types", which can be up to 2GB.

The invoke-sqlcmd PowerShell cmdlet will, by default, truncate strings to 4000 characters. This can be very useful to prevent accidently pulling back many GB of data from a query without realizing it. This is similar to the default query timeout, which prevents accidental long-running queries.

A "surprise" LOB column containing 2GB per row could pull back 1TB of data in as few as 500 rows--which would have unintended performance issues for other users on the server and possibly even on the local network, assuming a low bandwidth network.

To override that default, simply provide the -MaxCharLength parameter to indicate you want more than 4000 characters from your varchar(Max) column. (Similar to the way you already supply the -QueryTimeout 0 parameter.

Not the answer you're looking for? Browse other questions tagged or ask your own question.