SQL Server

Rename a Primary Key in SQL Server (T-SQL)

by

In SQL Server, you can use the sp_rename stored procedure to rename a user created object in the current database, including a primary key.

This can be handy if you’ve got a primary key that had its name automatically assigned, and you now want to give it a more readable name.

When you create a primary key without explicitly providing a name for it, SQL Server automatically delegates a name for it. Such names typically include a long numeric suffix, which makes it harder to remember. If you need to refer to that primary key (e.g. in your code, documentation, etc), such names can make your life more difficult. Fortunately, sp_rename provides a quick and easy way to change this name.

Read more

Format sysjobhistory datetime & duration Columns in SQL Server

by

If you’ve ever queried the sysjobhistory table in the msdb database, you’ll probably know that the datetime and duration columns are stored as integers.

In particular, when you query this table, the run_date, run_time, and duration columns are returned as integers, which can make it difficult to read.

Below is a query you can use to return this data in an easier to read format.

Read more

List of Date Formats Available with CONVERT() in SQL Server

by

The following table contains a list of the date formats that you can provide to the CONVERT() function when you convert a date/time value to a string.

These formats are provided as an optional third argument when calling the CONVERT() function. They’re provided as an integer expression that specifies how the CONVERT() function will format the date. 

Read more

How to Fix “The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION” in SQL Server

by

If you’re receiving error Msg 3902, Level 16, which reads “The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION”, it’s probably because you’ve got a stray COMMIT statement.

You could be getting this due to implementing error handling, and forgetting that you’ve already committed or rolled back the transaction elsewhere in your code.

Read more

Create a SQL Server Agent Schedule with T-SQL

by

When using T-SQL, you can use the sp_add_schedule stored procedure to add a SQL Server Agent schedule.

This procedure creates the schedule, but it doesn’t attach it to any jobs. To attach it to a job, you must use the sp_attach_schedule procedure.

You can also use sp_add_jobschedule to create the schedule and attach it all with the same procedure.

Read more