9

Just before a bulkinsert (SqlBulkCopy) I see a

excec sp_tablecollations_100 N'.tb_tablename'

in the profiler

What does this do? Is it a sign of a problem or is it normal?

Thanks for your help

update

the bulik insert afterwards show with the COLLATION in the insert statement. It is working fine, just wondering if this is 'normal behaviour' or that the COLLATE in the bulk insert is somehow affecting performance?

insert bulk tb_dvr_patient ([geboortejaar] VarChar(4) COLLATE Latin1_General_CI_AS,...
Pleun
  • 8,856
  • 2
  • 30
  • 50
  • Interesting. It looks like an [undocumented](http://msdn.microsoft.com/en-us/library/ms187961.aspx) sproc, probably used to prepare for the bulk copy operation. The `_100` probably refers to the SQL Server version. – Blorgbeard Mar 14 '11 at 14:57
  • select @@trancount; SET FMTONLY ON select * from [tbl_tablename] SET FMTONLY OFF exec ..sp_tablecollations_100 N'.[tbl_tablename]' -- is the full query I am seeing. FMTONLY means no data is returned. Just metadata. Found nvarchar uses 2 bytes instead of 1. sys.columns shows max_length = 44 when it is really 22 characters. This was immensely helpful! – TamusJRoyce Oct 11 '17 at 17:28

1 Answers1

6

I was curious myself so I had a look at the SQL code behind the stored proc and it is this:-

ALTER procedure [sys].[sp_tablecollations_100]
(
    @object nvarchar(4000)
)
as
    select
        colid               = s_tcv.colid,
        name                = s_tcv.name,
        tds_collation       = s_tcv.tds_collation_100,
        "collation"         = s_tcv.collation_100
    from
        sys.spt_tablecollations_view s_tcv
    where
        s_tcv.object_id = object_id(@object, 'local')
    order by colid

Seems that it just tells you the collation for the columns on that table.

Does that help?

Rick.

Richard Adnams
  • 3,128
  • 2
  • 22
  • 30
  • 1
    Well, this answers one part of my question. Any idea if this is 'standard behaviour' or something that can be avoided? I alreay checked the collation of the source and target table and they are the same – Pleun Mar 14 '11 at 15:02
  • Unfortunatly I don't know if it can be avoided as my SQL knowledge isn't that great but it does sound like something SQL would do to verify that they are the same. Hopefully someone else can answer you :) – Richard Adnams Mar 14 '11 at 15:05
  • This information might be used by the SSI Service to match the source and destination collation sets. – Gerardo Lima Dec 03 '13 at 12:21