Category: SQL-Tips and Tricks

SQL Server – How to add column dynamically in where clause

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

In this post, we will learn with example how to add the column dynamically in where clause. Example: For demo we have table as shown in the snapshot Basically we want to execute following script (i.e. Script: 1) but column name (i.e. FIRST_NAME) added dynamically in where clause. Script: 1 SELECT * FROM EMPLOYEE WHERE FIRST_NAME = 'VARINDER' Created a table and insert the column name value (i.e. FIRST_NAME) as shown in the snapshot Now we will execute the Script:

» Read more
, ,

SQL Server – The multi-part identifier could not be bound State – Msg 4104 – Fix

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

Error Msg Server: Msg 4104, Level 16, State 1, Line 1 The multi-part identifier could not be bound. Cause of this Error Msg: Case: 1 This error usually occurs when an alias is used when referencing a column in a SELECT statement and the alias used is not defined anywhere in the FROM clause. Case: 2 This error occurs if we are using the tables from the two different databases in the join statement and not using the table alias

» Read more
, ,

Best Way to Configure Firewall for SQL Server Access

Guest Post, SQL, SQL-Advanced, SQL-General, SQL-Tips and Tricks

Windows firewall is nothing but the software and hardware based network security. It generally controls the transaction traffic mixed up with data packets. Simultaneously it determines whether the upcoming signal is eligible for further routing or not. The threats coming from outside can be prevented with this protective shield. On the other hand, SQL server is a categorized section of relation database management system. Here the data are stored and retrieved to reduce the workload. Principal steps: When the basic

» Read more
, , , , ,

SQL Server 2008 – Create Temp table Permission/Access

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

Question:  Do we need any permission to create temp table in SQL Server? Or Question: Which permission is required to create temp table in SQL Server? Answer: No permissions are required to create temp tables.  Any user can create temp table, as long as they can connect to the SQL Server. Example: As user does not have permission to create a regular table But user can create a temp table as Temp Table as

» Read more
, ,

SQL Server 2008 – Auto Generate Insert Statement

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

This is very quick way to generate the Insert Statement for all of the data in your table. This is really very useful feature of SQL Server Summary: Right click on the database and go to Tasks -> Generate Scripts Select the tables for that you want to generate the script Click on the Advanced button. In the General category, go to Type of data to script. There are 3 options: Schema Only, Data Only, and Schema and Data. Select

» Read more
, ,

SQL Server – Generate Row Number in Select and Select into

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

How to Generate the Row Number in Select and Select into script? Using IDENTITY for Select Into SELECT RowNumber=IDENTITY(int,1,1),FIRST_NAME,LAST_NAME into EMPLOYEE_backup FROM EMPLOYEE Using Row_Number() for Select SELECT ROW_NUMBER() OVER (ORDER BY ID ASC) RowNumber, FIRST_NAME,LAST_NAME FROM EMPLOYEE Using Row_Number() for Select into SELECT ROW_NUMBER() OVER (ORDER BY ID ASC) RowNumber , FIRST_NAME,LAST_NAME into EMPLOYEE_backup1 FROM EMPLOYEE

» Read more
, ,

SSIS – Import/Export Wizard – Unexpected Unrecoverable Error

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

SSIS Import/Export Wizard throws up “Unexpected Unrecoverable Error”.  Error message dialog box have three buttons – Abort, Retry, and ignore all cause the wizard to close. The reason I found of this: Installation the .Net Framework 4 Client Profile Solution: Uninstall the .Net Framework 4 Client Profile Or Install Extended .NET Framework 4 You can choose any one solution as per the requirement. If any body have any other solution then please share as comment.  

» Read more
, , ,

SQL Server 2008 – How to copy a Table

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

Lets see how to copy an existing table to new table in SQL Server. There are two options. Copy the table with Data. Copy the table without data (i.e.Table Structure Only) — Create Table CREATE TABLE EMPLOYEE ( ID INT NOT NULL, FIRST_NAME VARCHAR(10), LAST_NAME VARCHAR(10) );   — INSERT SOME VALUES INTO TABLES INSERT INTO EMPLOYEE (ID,FIRST_NAME,LAST_NAME) VALUES (1,'VARINDER','SANDHU'); INSERT INTO EMPLOYEE (ID,FIRST_NAME,LAST_NAME) VALUES (2,'DINESH','SHARMA'); INSERT INTO EMPLOYEE (ID,FIRST_NAME,LAST_NAME) VALUES (3,'RANJOYT','SINGH'); INSERT INTO EMPLOYEE (ID,FIRST_NAME,LAST_NAME) VALUES (4,'VIKRAM','SINGH');   –CREATE

» Read more
,

SQL Server – NEWID () vs. NEWSEQUENTIALID ()

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

NEWID () NEWID () creates a unique value of type uniqueidentifier. NEWID () can be referenced in the queries. Example: NEWSEQUENTIALID () NEWSEQUENTIALID () creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started. After restarting Windows, the GUID can start again from a lower range, but is still globally unique. NEWSEQUENTIALID () cannot be referenced in queries. Example: Example: Using table Imp Note: Each GUID generated by

» Read more
, , ,

SQL Server – Import Export Wizard – Enable Identity Insert

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

I had requirement to move the data from source table with his identity column to destination table that have also identity column. Identity columns are commonly used as primary keys in database tables. These columns automatically assign a value for each new row inserted. Identity column does not allow any kind of insertion from the user. Before starting I was thinking I need to enable the IDENTITY_INSERT on the destination table by using SET IDENTITY_INSERT myTable ON   and once I

» Read more
, ,

SQL Server 2008 – Get the next Identity Value

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

Question: How to get the next Identity Value in SQL Server 2008? Answer: It is very simple to the get the identity value in SQL Server. Let’s see with the example below Lets‘s create some dummy data as mentioned below … In SQ Server, there are two functions IDENT_CURRENT and IDENT_INCR. With the help of these funtions, we can get next value Identity. IDENT_CURRENT: It returns the last identity value generated for a specified table or view. The last identity

» Read more
, ,

SQL Server – System Configuration Values

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

Many times we need to review the SQL Server instance configuration values while troubleshooting performance problems. We have one view in SQL Server 2005 or higher version that contains a row per server-wide configuration option value in the system. The catalog view that provides details about all the server wide configuration values is called sys.configurations. sys.configurations view have the following columns as configuration_id — Unique ID for the configuration value. name — Name of the configuration option. value — Configured value

» Read more
,
1 2 3 4 … 8