SQL Scripts Archives - Page 289 of 318 - SQL Authority with Pinal Dave

SQL SERVER – Find Name of The SQL Server Instance

October 2, 2007

Few days ago, there was complex condition when we had one database on two different server. We were migrating database from one server to another server using nightly backup and restore. Based on database server stored procedures has to run different logic. We came up with two different solutions. 1)…

SQL SERVER – 2005 – OUTPUT Clause Example and Explanation with INSERT, UPDATE, DELETE

October 1, 2007

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

SQL Scripts

SQL Server 2005 has new OUTPUT clause, which is quite useful. OUTPUT clause has accesses to inserted and deleted tables (virtual tables) just like triggers. OUTPUT clause can be used to return values to client clause. OUTPUT clause can be used with INSERT, UPDATE, or DELETE to identify the actual…

SQL SERVER – Two Connections Related Global Variables Explained – @@CONNECTIONS and @@MAX_CONNECTIONS

September 29, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Function, SQL Scripts, SQL Transactions

Few days ago, I was searching MSDN and I stumbled upon following two global variables. Following variables are very briefly explained in the BOL. I have taken their definition from BOL and modified BOL example to displayed both the global variable together. @@CONNECTIONS Returns the number of attempted connections, either…

SQL SERVER – Introduction and Example for DATEFORMAT Command

September 28, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL DateTime, SQL Scripts

While doing surprise code review of Jr. DBA I found interesting syntax DATEFORMAT. This keywords is very less used as CONVERT and CAST can do much more than this command. It is still interesting to learn about learn about this new syntax. Sets the order of the dateparts (month/day/year) for…

SQL SERVER – FIX : Error 3154: The backup set holds a backup of a database other than the existing database

September 27, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Backup and Restore, SQL Data Storage, SQL Error Messages, SQL Scripts

Our Jr. DBA ran to me with this error just a few days ago while restoring the database. Error 3154: The backup set holds a backup of a database other than the existing database. Solution is very simple and not as difficult as he was thinking. He was trying to…

SQL SERVER – Effect of TRANSACTION on Local Variable – After ROLLBACK and After COMMIT

September 25, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Scripts, SQL Transactions

Few days ago, one of the Jr. Developer asked me this question (What will be the Effect of TRANSACTION on Local Variable – After ROLLBACK and After COMMIT?) while I was rushing to an important meeting. I was getting late so I asked him to talk with his Application Tech…

SQL SERVER – Stored Procedure to Know Database Access Permission to Current User

September 23, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Cursor, SQL Scripts, SQL Stored Procedure, undocumented SQL

Jr. DBA in my company only have access to the database which they need to use. Often they try to access database and if they do not have permission they face error. Jr. DBAs always check which database they have access using following system stored procedure. It is very reliable…