Trace Will not Capture row data if got inserted via Bulk Insert

fyi,
i was capturing all events happening against a Table, I was able to find S/I/U/D etc events for few Phone number
But there was a phone number i was particular about and i couldn't see in Insert ,so with the time it got inserted I see SSIS package doing BULK INSERT.


so, what i learned is if we insert data via ssis or bulk copy/insert we will not see ROW data.
we can see  all Statements happening with SSIS or BULK ...

The row was not found at the Subscriber when applying the replicated command. (Source: MSSQLServer, Error number: 20598)

I had this error:
Databases were out of sync.
Looked at links below

https://support.microsoft.com/en-us/kb/3066750
http://stackoverflow.com/questions/567597/sql-replication-row-not-found-error


Re Initialize Subscription : DID IT

not null vs != OR <>

NULL Cannot be compared to any value using the comparison operators. NULL = NULL is false. Null is not a value. The IS operator is specially designed to handle NULL comparisons.



<> is Standard SQL-92; != is its equivalent: Both evaluate for values,
WHERE NULL CANNOT

Logins, Roles and Permissions , Sysadmins

find out what login has what role and who are sysadmins?


Step1:
SET NOCOUNT ON

CREATE TABLE #DatabaseRoleMemberShip
   (
        Username VARCHAR(100),
        Rolename VARCHAR(100),
        Databasename VARCHAR(100)
       
    )DECLARE @Cmd AS VARCHAR(MAX)DECLARE @PivotColumnHeaders VARCHAR(4000)           SET @Cmd = 'USE [?] ;insert into #DatabaseRoleMemberShip
select u.name,r.name,''?'' from sys.database_role_members RM inner join
sys.database_principals U on U.principal_id=RM.member_principal_id
inner join sys.database_principals R on R.principal_id=RM.role_principal_id
where u.type<>''R'''EXEC sp_MSforeachdb @command1=@cmd

SELECT  @PivotColumnHeaders =                        
  COALESCE(@PivotColumnHeaders + ',[' + CAST(rolename AS VARCHAR(MAX)) + ']','[' + CAST(rolename AS VARCHAR(MAX))+ ']'                    
  )                    
  FROM (SELECT DISTINCT rolename FROM #DatabaseRoleMemberShip )a ORDER BY rolename  ASC


SET @Cmd = 'select
databasename,username,'+@PivotColumnHeaders+'
from
(
select   * from #DatabaseRoleMemberShip) as p
pivot
(
count(rolename  )
for rolename in ('+@PivotColumnHeaders+') )as pvt'

--drop table #DatabaseRoleMemberShip
EXECUTE(@Cmd )      



Step2:
who are sys admins

SELECT   name,type_desc,is_disabled
FROM     master.sys.server_principals
WHERE    IS_SRVROLEMEMBER ('sysadmin',name) = 1
ORDER BY name

How to resolve the “The server principal “Domain\LoginName” already exists” Error Msg 15025, Level 16, State 2, Line 1

Step1: SELECT SUSER_SID( 'Domain\LoginName');
SID:0x010500000000000515000000C5F84793CB280B51AD7C2F5320370000

Step2:
Select * from sys.server_principals
where SID=0x010500000000000515000000C5F84793CB280B51AD7C2F5320370000
go

Step3:
drop login [Domain\LoginName']
go

Step4:
CREATE LOGIN [Domain\LoginName'  FROM WINDOWS WITH DEFAULT_DATABASE=[master],
DEFAULT_LANGUAGE=[us_english]
 go

Step5: If required, this is for a particular database
 USE AuditDB
 go
EXEC sp_addrolemember 'db_datareader', 'Domain\LoginName''
GO

SELECT Top 10 and order by Results vary every time ( different results every time i run)

EX:

Select top 100 col1, col2, col3,xyz.col4
from ABC
JOIN XYZ
Order col3


i have that query ran at 10AM and i ran the same query at 10:15am
the results were different...
Y?


Oderby Clo3 is not uinique


I had to put Unique column (PK column, INDEX Column) to get the same results everyTime

This is just my exp:
i see online some parallel processing,. ,Maxdop etc things

SQL Server – Track Database Size Growth Trend

https://sqljourney.wordpress.com/2013/02/13/sql-server-track-database-size-growth-trend/

Unlock SQL login/User with out Resetting Password

 Alter login [XYZ] with CHECK_POLICY = OFF

go

Alter login [XYZ] with CHECK_POLICY = ON

go

Backup and Restore Permissions

To Backup and Restore Database:

Create a login, add Login to Individual database  Roles,
db_backupoperator--Can Backup Database , but cannot Restore 
DB_Creator-Server Role --Can Restore database 

BACKUP
BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles

RESTORE
If the database being restored does not exist, the user must have CREATE DATABASE permissions to be able to execute RESTORE. If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database (for the FROM DATABASE_SNAPSHOT option, the database always exists).

RESTORE permissions are given to roles in which membership information is always readily available to the server. Because fixed database role membership can be checked only when the database is accessible and undamaged, which is not always the case when RESTORE is executed, members of the db_owner fixed database role do not have RESTORE permissions.

How to exit from SP with out running

EX: I have SP:

BEGIN

PRINT 'you have bad input'
RETURN

END

to exit with out running SP




BEGIN

RETURN---- the SP will exit here
PRINT 'you have bad input'

END

Maximum Degree of Parallelism ,Cost Of Parallelism

Maximum Degree of  Parallelism =8

Cost Of Parallelism =50

Source:BrendtOzar

https://www.youtube.com/watch?v=QkymlR4ZdIE