Tag: SQL Server

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 – Export the Data from SQL Server to Oracle Database – Import-Export Wizard

SQL, SQL-Advanced, SQL-General, SQL-SSIS, Varinder Sandhu

The SQL Server Import and Export Wizard provides the simplest method of copying data between data sources. In this article given an example to copy the data from SQL Server to Oracle database. Click here to download the full PDF version of this article. After this article,for more detail about SQL Server Import and Export Wizard. please check http://msdn.microsoft.com/en-us/library/ms141209

» Read more
, ,

SQL Server 2008 – Import/Export wizard not allowing Create Temp Table

SQL, SQL-Advanced, SQL-SSIS, Varinder Sandhu

I had a requirement to export the data from SQL Server to Oracle using SQL Server Import/Export wizard. In the SQL Server, I have created a SQL Script that fetch the required data that I need to export in Oracle database. Because of some business logic, in the select script I have introduced the temp table. Now when I try export that data using select script that contain the local temp table the following error occurred. Now if I removed

» 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
, ,

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 – 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
,

SQL SERVER – 2008 – Inline Variable Assignment – Declare and Assign Variable

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

Inline variable assignment is available in SQL Server 2008 or higher version. Beside this is very simple but very useful feature. Many of the programming languages have this feature. But now SQL Server 2008 or higher version has this feature. Example: —- SQL Server 2005 or earlier versions DECLARE @var1 INT SET @var1 = 100 SELECT @var1 AS myVar GO —- SQL Server 2008 or Higher Version (Inline Variable Assignment) DECLARE @var1 INT = 100 SELECT @var1 AS myVar GO

» Read more
, , ,
1 2 3 4 5 … 9