Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Friday, 20 September 2019

Bulk load failed


Msg 5301, Level 16, State 1, Line 1

Bulk load failed. User does not have ALTER TABLE permission on table 'Table_2'. ALTER TABLE permission is required on the target table of a bulk load if the target table contains triggers or check constraints, but the  'FIRE_TRIGGERS' or 'CHECK_CONSTRAINTS' bulk hints are not specified. ALTER TABLE permission is also required if the 'KEEPIDENTITY' bulk hint is specified.



Issue:

While we perform Bulk insert operation on table it will through error as requires bulkadmin and alter table permission for user

When you use bulk insert on main table it ignores constraints and disables triggers.





Permissions:
Requires INSERT and ADMINISTER BULK OPERATIONS permissions. Additionally, ALTER TABLE permission is required if one or more of the following is true:
·         Constraints exist and the CHECK_CONSTRAINTS option is not specified
·         Triggers exist and the FIRE_TRIGGER option is not specified.
·         You use the KEEPIDENTITY option to import identity value from data file.


  • By default, the BULK INSERT statement ignores constraints and disables triggers.
  • By default, the INSERT…INTO statement executes check constraints and triggers.

Fix for this issue:

We can create temp table first and use this temp table in bulk insert operation then we can get the row from temp table will resolve disable triggers and permission requirement on user.






   bulk insert Table_2 from  'D:\test.csv' with ( fieldterminator = ',', rowterminator='\n')



create table #temp(tid int,tname varchar(50))

bulk insert #temp from  'D:\test.csv' with ( fieldterminator = ',', rowterminator='\n')

insert into Table_2


select * from #temp 

Friday, 6 September 2019

How to find Missing index on all database on TSQL query

Missing index on all database on single TSQL query


SELECT

mid.database_id dbid,DB_NAME(mid.database_id) [DB_Name], mid.[object_id],mid.statement,

  migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,

  'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)

  + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'

  + ' ON ' + mid.statement

  + ' (' + ISNULL (mid.equality_columns,'')

    + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END

    + ISNULL (mid.inequality_columns, '')

  + ')'

  + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,

  migs.*

FROM sys.dm_db_missing_index_groups mig

INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle

INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle

WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
and migs.avg_user_impact >=90.00

ORDER BY DB_NAME(mid.database_id),migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC