Skip to content

Category: SQL

Disable and Enable SQL Jobs

Quick to disable and enable all SQL jobs on your server

USE MSDB

-- DISABLE ALL SQL JOBS
UPDATE dbo.sysjobs
SET Enabled = 0
WHERE Enabled = 1

-- ENABLE ALL SQL JOBS
UPDATE dbo.sysjobs
SET Enabled = 1
WHERE Enabled = 0

Restoring a DB to a different directory

RESTORE DATABASE [MyDatabase]
FROM DISK = N'E:\MyDatabase.bak'
WITH FILE = 1,
MOVE N'MyDatabase'
TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\MyDatabase.mdf',
MOVE N'MyDatabase_Log'
TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\MyDatabase.ldf',
NOUNLOAD, REPLACE, STATS = 10
GO

http://www.codeproject.com/Articles/708720/Solution-The-WITH-MOVE-clause-can-be-used-to-reloc

 

SQL Changing Schema Name

Changing it one by one use the following.

ALTER SCHEMA dbo TRANSFER someschema.someobject;

Changing all stored procedures.

select 'ALTER SCHEMA dbo TRANSFER ' + SPECIFIC_SCHEMA + '.' + ROUTINE_NAME from INFORMATION_SCHEMA.ROUTINES  
where ROUTINE_TYPE='procedure'

 

Simple Linq to SQL Insert and Update

private void InsertAndUpdate()
{
  NorthwindDataContext db = new NorthwindDataContext();

  //Insert a record

  Customer newCus = new Customer();
  newCus.CustomerID = "YYYZZ";
  newCus.CompanyName = "Company_Z";

  db.Customers.InsertOnSubmit(newCus);
  db.SubmitChanges();      

  //Update a record
  Customer record = (from p in db.Customers
         where p.CustomerID == "12345" 
         select p).SingleOrDefault();

  Console.WriteLine(default(Customer));
  if (record != default(Customer))
  {
    record.CompanyName = "Company_A";
  }

  db.SubmitChanges();
}