How to: create login with 'sa' credentials

CREATE LOGIN loginname
WITH PASSWORD = 'changeme' MUST_CHANGE
,CHECK_EXPIRATION = ON
,DEFAULT_DATABASE = DBName
GO
EXECUTE sp_addsrvrolemember
@loginame = 'loginname',
@rolename = 'sysadmin'

How to: assign IDs/SortVal values using ROW_NUMBER()

I have a lookup table L_MyLookup with columns ID and NAME.

Later, my client wanted a SORTVAL column because they want to define the order of the values through that column instead of ID.

So

1. add the SORTVAL column
2. update the values
3. alter SORTVAL to become not nullable

Like the following code:

ALTER TABLE L_MyLookup

ADD SORTVAL int NULL

go

UPDATE L_MyLookup set SORTVAL = temp.SORTVAL

FROM

(SELECT ID, ROW_NUMBER() over (order by ID) as SORTVAL

FROM L_MyLookup) as temp

WHERE temp.ID = L_MyLookup.ID

go

ALTER TABLE L_MyLookup

ALTER COLUMN SORTVAL int NOT NULL

FTS: Create new Catalog

See: how to drop existing FTS Catalog

EXEC sp_fulltext_database 'enable'
go

CREATE FULLTEXT CATALOG MyFTSCatalog
go

CREATE FULLTEXT INDEX ON MyTable
(
Document
TYPE COLUMN DocumentExtension
LANGUAGE 0x0407 --this is German
)
KEY INDEX PK_MyTable
ON MyFTSCatalog
go

--to populate the catalog,

--do the following when traffic is light:
ALTER FULLTEXT INDEX ON Production.Document SET CHANGE_TRACKING AUTO;
GO

FTS: Drop Catalog

sp_fulltext_table 'MyTable', 'drop'
go
sp_fulltext_database 'enable'
go
sp_fulltext_catalog 'MyFTSCatalog', 'drop'
go
sp_fulltext_service 'clean_up'
go

SSIS auto generate update scripts

Specify the TableName in @TableName variable to create the Update script to be used for SSIS.

DECLARE @counter int;

DECLARE @QueryString nvarchar(2400) = ''

DECLARE @ColumnName nvarchar(120);

DECLARE @TableName nvarchar(120) = 'MyTable'

CREATE TABLE #ColumnList

(

ID int IDENTITY(1,1)

,ColumnName varchar(1200)

)

INSERT #ColumnList

select sys.columns.name from sys.columns

inner join sys.tables on sys.tables.object_id = sys.columns.object_id

where sys.tables.name = @TableName

and sys.columns.name <> 'ID'

SET @counter = 1

SET @QueryString = 'UPDATE dbo.' + @TableName + '

SET '

SET @ColumnName = (SELECT ColumnName FROM #ColumnList WHERE ID=@counter)

SET @QueryString = @QueryString + '

' + @ColumnName+ ' = ? '

SET @counter = @counter + 1;

WHILE @counter <= (SELECT COUNT(*) FROM #ColumnList)

BEGIN

SET @ColumnName = (SELECT ColumnName FROM #ColumnList WHERE ID=@counter)

SET @QueryString = @QueryString + '

,' + @ColumnName+ ' = ? '

SET @counter = @counter + 1;

END

SET @QueryString = @QueryString + '

WHERE ID = ?;'

PRINT @QueryString

DROP TABLE #ColumnList