INSERT INTO OPENROWSET

• Ian_McCann

  SSCertifiable

  Points: 5163

  More actions

  November 26, 2008 at 1:42 am

  #196641

  I am running the following

  INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',

  'Excel 8.0;Database=C:\cm_mssql_2005_profiling\Reports\Template.xls;',

  'SELECT TSQLString, Duration,Reads,Writes,CPU,RowCounts,StartTime,EndTime,ServerName

  FROM [Top_10_by_Reads$]')

  select top 10 TSQLString, duration/1000 as Duration, reads, writes, cpu, rowcounts, starttime, endtime,servername

  from cm_mssql_2005_TRC

  where textdata is not null

  order by Reads desc

  GO

  and relieving the following error

  Msg 8152, Level 16, State 10, Line 1

  String or binary data would be truncated.

  The statement has been terminated.

  The offending column is TSQLString - VARCHAR(max)

  The data does fit, I can cut & paste it.

  ( I'm using SQL 2005, SSIS & export wizards are not options available to me)

  Thanks in Advance

  Ian

• Ian_McCann

  SSCertifiable

  Points: 5163

  More actions

  December 11, 2008 at 1:26 am

  #910877

  Well it is not the most elegant solution I have ever come up with but it is working.

  As I said I can cut and paste the data to the cell in excel, and that is what I have done.

  By placing a record into row 2, setting the row properties to hidden and placing it above the frozen pane my report readers can not see the record

  and the above TSQL works providing me with the body of my report.

  If you know how this should be done, please share it with me.

  Thanks

  Ian