Enable Agent XPS using T SQL

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

EXEC SP_CONFIGURE 'Agent XPs', 1;
GO
RECONFIGURE;
GO

 

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation

How to change SQL Server to Mixed Mode Authentication using t sql

EXEC xp_instance_regwrite

    N'HKEY_LOCAL_MACHINE'

    , N'Software\Microsoft\MSSQLServer\MSSQLServer'

    , N'LoginMode'

    , REG_DWORD, 2

go

restart sql server

How to Get DATABASE OUT OF SUSPECT MODE MS SQL SERVER

ALTER DATABASE DBName  SET  EMERGENCY
 
DBCC CHECKDB('DBName')
 
ALTER DATABASE DBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
 
DBCC CHECKDB('DBName', REPAIR_ALLOW_DATA_LOSS)
 
ALTER DATABASE DBName SET MULTI_USER

Error message “Explorer.exe - No such interface supported” in Windows 7

1. Click Start. In the Search box, type CMD. At the top of the window, right-click the cmd icon and choose Run as Administrator.
2. At the prompt, type in FOR /R C:\ %G IN (*.dll) DO "%systemroot%\system32\regsvr32.exe" /s "%G" and press.

This will take several minutes to complete. There will be several C+ Runtime errors that will appear, as well as the system slowing down. Once complete, restart the system and test.

How to migrate a MS SQL Server database to a lower version

Solution 1

Generate a script of schema and data using ssms "generate scipts" option

Solution 2

Generate a script of schema using ssms "generate scipts" option

Use data compare wizard is data is too big

Slow Database First Code Generation Visual Studio

Running the following on the DB worked for me:

ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION=ON

Then, after the update, setting it back using:

ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION=OFF

Renaming Primary key and references in all tables sql server

Use sp_fkeys procedure to get all foreign keys

EXEC sp_fkeys 'Students', 'Academics'

Use nimble text to update keys

UPDATE $5.$6
SET $7 = $1.$2.$3
FROM $5.$6
INNER JOIN $1.$2 on $5.$6.$7 =  $1.$2.$3

SQL SERVER – Unable to Attach Database – File Activation Failure – The Log Cannot be Rebuilt

Method 1: 

Src: https://www.mssqltips.com/sqlservertip/3579/how-to-attach-a-sql-server-database-without-a-transaction-log-and-with-open-transactions/

Last Resort:

CREATE DATABASE SQLAuthority

ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\file.mdf')

FOR ATTACH_FORCE_REBUILD_LOG

Src:
https://blog.sqlauthority.com/2016/11/04/sql-server-unable-attach-database-file-activation-failure-log-cannot-rebuilt/

7-Zip command line to zip all the content of a folder

7z a archivename pathtofolder *

SQl SERVER: How to set recovery model to SIMPLE for all databases

Steps on how to set recovery model to SIMPLE for all databases 

Step 1. Run script

SELECT 'ALTER DATABASE ' + [Name] + ' SET RECOVERY SIMPLE'
FROM sys.databases
WHERE recovery_model_desc = 'FULL'

Step 2. Copy output and execute

SQl SERVER: How to set recovery model to FULL for all databases

Steps on how to set recovery model to FULL for all databases 

Step 1. Run script

SELECT 'ALTER DATABASE ' + [Name] + ' SET RECOVERY FULL'
FROM sys.databases
WHERE recovery_model_desc = 'SIMPLE'

Step 2. Copy output and execute

New T-SQl String Functions in SQL SERVER 2016, 2017

1. STRING_SPLIT
2. TRANSLATE
3. STRING_AGG
4. TRIM
5. FORMATMESSAGE
6. STRING_ESCAPE

How to Shrink MS SQL Server Transaction Logs

Using T-SQL

ALTER DATABASE [db name]
SET RECOVERY SIMPLE
GO
DBCC SHRINKFILE('log_file_name.ldf', 100)--whatever size you need in MB's 
GO
ALTER DATABASE [db name]
SET RECOVERY FULL

To Find Log file name use below T-SQL

use [db name]
GO
select name as [log_file_name] from sys.database_files

where [type_desc] = 'LOG'

Windows Installer MSI Error 1001/2869

Make sure to disable any antivirus software

Solution 1:

1. Run cmd as administrator
2. go to directory containing the msi file using "cd"
3. then run "msiexec /i File.msi

Solution 2:

1. Run cmd as administrator
2. go to directory containing the msi file using "cd"
3. then run msiexec /a File.msi /qb targetdir="e:/NewPath/"
4. this will extract the contents of file where you may be able to launch the application

Export binary data from database using LINQPAD

Switch LinqPad to c# Program

void Main()
{
    var students = Students.Where(st => st.Photo != null).Select(st => new
    {
        st.Id,
        st.Photo
    });
    foreach (var student in students)
    {
        File.WriteAllBytes(@"D:\Photos\" + student.Id + ".jpg", student.Photo.ToArray());
    }
}

LINQ to Entities does not recognize the method 'System.String PadLeft(Int32)'

Convert to list then do padleft

The entity type List`1 is not part of the model for the current context

where db is List

db.Entry(bd).State = EntityState.Modified;

DbContext.Entry(object) expects a single object, not a collection.
so use a loop instead

foreach (var item in bd)
{
    db.Entry(item).State = EntityState.Modified;
}

Convert dictionary to list collection in C#

using linq

accountTypes.Select(kvp => new
            {
                account_type_no = kvp.Key,
                account_type_name = kvp.Value
            }).ToList()

using loop

foreach (var item in accountTypes)
{
    accountTypeList.Add(item.Key);
}

How to remove all CSS classes using jQuery?

$("#item").removeClass();