Category: SQL-Tips and Tricks

SQL Server – Location of Installation Log file

SQL, SQL-Advanced, SQL-General, SQL-Tips and Tricks, Varinder Sandhu

A few times we were trying to locate the installation log file folder if we faced any issue or problem while installation of SQL Server. Basically, we want to know reason of issue or problem. So location of Log file folder as below SQL Server 2008 and 2008 R2 %programfiles%\Microsoft SQL Server\100\Setup Bootstrap\Log\ SQL Server 2005 %ProgramFiles%\Microsoft SQL Server\90\Setup Bootstrap\LOG\ You can find the Summary.txt file in above mentioned location. This file shows the SQL Server components that were detected during

» Read more
, ,

SQL Server – Check the compatibility level of a database

SQL, SQL-Advanced, SQL-Tips and Tricks, Varinder Sandhu

You can check the compatibility of a database simply by running the below sql command. sp_dbcmptlevel 'Database_Name' Read more about sp_dbcmptlevel Values of compatibility levels and their corresponding MS SQL Server versions 60 = SQL Server 6.0 65 = SQL Server 6.5 70 = SQL Server 7.0 80 = SQL Server 2000 90 = SQL Server 2005 100 = SQL Server 2008 The values 60 and 65 are deprecated and will be removed in a SQL 2008 and later. You

» Read more
, ,

SQL Server – Error Handling Using TRY…CATCH

SQL, SQL-Advanced, SQL-General, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

Prior to SQL server 2005, the only way of performing the error handling was to test vale of @@ERROR global variable. Once the SQL Server completes the execution of a Transact-SQL statement, it records the status of result in @@ERROR. If the error occurred, @@ERROR contains the error number. If the statement executed successfully it contain the 0. You need to check the variable if the statement succeeded or failed. Using @@ERROR to perform the error handling is not easy

» Read more
, , , ,

SQL Server – Get Comma Separated Values from table

SQL, SQL-Advanced, SQL-General, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

I had requirement to get the Comma Separated Values from the table for specific column. So here sharing with you example how i handle this situation. USE tempdb GO CREATE TABLE test1 ( column1 INT, column2 VARCHAR(6) ) INSERT INTO test1 VALUES (1,'Test1'), (1,'Test2'), (2,'Test3'), (2,'Test4'), (3,'Test5') Now see the table SELECT * FROM TEST1  Create a function to get the Comma Separated Values as below CREATE FUNCTION dbo.CommaSeparatedvalues ( @parameter1 AS INT ) RETURNS VARCHAR(5000) AS BEGIN DECLARE @CSV

» Read more
, , ,

SQL Server – Mirror Backup of databases

SQL, SQL-Advanced, SQL-Backup & Restore, SQL-General, SQL-Tips and Tricks, Varinder Sandhu

It is very common practice to create an exact copy of the database backup and store it to different places so that in case any emergency or misfortune, DBA should have the alternative location to find the backup of databases.  Once a full backup is accomplished DBAs generally copy the database to another location in their network using utility tools like R-sync or native DOS commands like xcopy etc. Mirroring a media set increases backup reliability by reducing the impact

» Read more
, , ,

Find the List of the SQL Servers running on a network

SQL, SQL-Advanced, SQL-General, SQL-Tips and Tricks, Varinder Sandhu

I had requirement to find list of all the SQL Server installed on local network then I found few very useful commands. Here sharing with you all. You can use sqlcmd, osql and isql with the -L option as shown below. Using sqlcmd -L Servers: SQLServer1 SQLServer2\DBServer SQLServer3\ReportServer SQLServer4\SQLExpress SQLServer5 Using osql -L Servers: SQLServer1 SQLServer2\DBServer SQLServer3\ReportServer SQLServer4\SQLExpress SQLServer5 Using isql -L Locally configured servers: –None– Announced network servers: SQLServer1 SQLServer2 SQLServer3 SQLServer4 SQLServer5 Note: sqlcmd  and osql is able

» Read more
, , ,

SQL Server – Table-Valued Parameters

SQL, SQL-Advanced, SQL-General, SQL-Query, SQL-Scripts, SQL-Stored Procedure, SQL-Tips and Tricks, Varinder Sandhu

Table-valued parameters are a new parameter type introduced in SQL Server 2008. With the help of table-valued parameters, you can send multiple rows of data to a stored procedure or function, without creating a temporary table or many parameters. Steps to create Table-Valued Parameters Create a table type and define the table structure. Declare a stored procedure or function that has a parameter of the table type. Declare a variable of the table type, and reference the table type. Fill

» Read more
, , ,

SQL Server – Inside of Next Generation SQL Server – Denali

SQL, SQL-Advanced, SQL-General, SQL-Server 2011, SQL-Tips and Tricks, Varinder Sandhu

Today I have attended the webcast topic “Inside of Next Generation SQL Server – Denali” at Zeollar.com. This session presented by Pinal Dave (http://blog.sqlauthority.com) It was really a nice presentation about features of SQL Server 2011 – Denali.  You can get the details of this session at here You can watch or download the video at http://zeollar.cloudapp.net/Session/234

» Read more
, , ,

SQL Server – Local temporary table vs. Global temporary table

SQL, SQL-Advanced, SQL-General, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

Temporary tables are a useful feature provided by SQL Server. Temporary tables created at runtime and can do all kinds of operations that one normal table can do. Because this is available at runtime, that’s why the scope of temporary tables is limited. These tables are created inside the tempdb database. There are two types of Temporary tables. Local Temporary table Global Temporary table Local temporary table: Local temporary tables are available to the current connection or sessions to the

» Read more
, , , , ,

SQL Server – User Defined Functions vs. Stored Procedures

SQL, SQL-Functions(UDF), SQL-General, SQL-Stored Procedure, SQL-Tips and Tricks, Varinder Sandhu

SQL Server user-defined functions and stored procedures have almost similar functionality. Both allow you to create a batch of SQL statements to execute on SQL server. Benefits: We can reuse the code from one program to another, this way we can increase the productivity. Centralize control, to make business logic changes in a single place that automatically affect all dependent applications. Difference between User Defined Functions and Stored Procedures   Stored procedures are called independently, using the EXEC command. Example: EXEC dbo.procedure_name ‘Parameter1’Functions are

» Read more
, , , , ,

SQL Server – Database Read Only

SQL, SQL-Advanced, SQL-General, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

Here is simple script with you can set your database in Read-Only Mode Once you specify database is in read-only mode then users can read data from the database, not modify it. Here is Script : Mark Database Read Only USE [master] GO ALTER DATABASE database_name SET READ_ONLY WITH NO_WAIT Similarly you can change database Read-Only Mode to Read-Write Here is script : Mark Data Read- Write Mode USE [master] GO ALTER DATABASE database_name SET READ_WRITE WITH NO_WAIT GO Alternative

» Read more
, , , ,

SQL Server – Management Studio – Add an External Tool to the Tools Menu

SQL, SQL-Advanced, SQL-General, SQL-Tips and Tricks, Varinder Sandhu

We can launch any Microsoft Windows or Microsoft .NET Framework application from SQL Server Management Studio. External applications can be added to, and run from, the Tools menu. To add an external tool to the Tools menu On the Tools menu, click External Tools. In the Title text box, type the name you want to appear in the Menu contents list. In the Command text box, type the program name. Include the path to the executable file if necessary. In the Arguments text

» Read more
, , , ,
1 … 4 5 6 7 8