Category: SQL

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

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

dbForge SQL Complete – SQL Server Management Studio – Visual Studio

SQL, SQL-Utilities, Varinder Sandhu

SQL Complete is a tool available freely (and advanced professional version) which acts as a plugin to SQL Server Management Studio and Visual Studio, and provides powerful autocompletion and formatting of T-SQL code that replaces native Microsoft T-SQL Intellisense. That increases development productivity to quite an extent. For more detail and download one can refer given URL – http://www.devart.com/dbforge/sql/sqlcomplete/ For Demo refer the URL – http://www.devart.com/dbforge/sql/sqlcomplete/sql-complete-demo.html

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

SQL Server 2008 – Database Backup Compression

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

Backup compression was introduced in SQL Server 2008 Enterprise. At installation, backup compression default is set to 0, which makes backup compression off by default. To change the default to COMPRESSION, set backup compression default to 1. To revert the default to NO_COMPRESSION, set backup compression default back to 0. Backup Compress at Server Level (Thru Script) USE master; GO EXEC sp_configure 'show advanced option', '1'; RECONFIGURE GO EXEC sp_configure 'backup compression default', '1'; RECONFIGURE WITH OVERRIDE; GO EXEC sp_configure

» Read more
, ,

SQL Server – Download SQL Server 2012 – Evaluation

SQL, SQL-Server 2012, Varinder Sandhu

Microsoft SQL Server 2012 enables a cloud-ready information platform that will help organizations unlock breakthrough insights across the organization as well as quickly build solutions and extend data across on-premises and public cloud backed by capabilities for mission critical confidence. Deliver required uptime and data protection with AlwaysOn Gain breakthrough & predictable performance with ColumnStore Index Help enable security and compliance with new User-defined Roles and Default Schema for Groups Enable rapid data discovery for deeper insights across the organization

» Read more
,
1 2 3 4 5 6 … 12