Category: SQL-Advanced

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

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 2008 – Get the List of Stored Procedures update/modify in last N number days

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

With the help of the given procedure one can get the list of Stored Procedures update or modify in last N number days. Script: — Get the List of Stored Procedures update/modify in last N number days CREATE PROCEDURE GetListModifySP ( @days INT ) AS SELECT name AS SP_Name, modify_date as Modfiy_Date FROM sys.objects WHERE TYPE = 'P' AND DATEDIFF(DD,modify_date, GETDATE()) < @days –Excecute the GetListModifySP –You can give the N of days as per requirement EXEC GetListModifySP 10 Hope

» Read more
,

SQL Server – Last Execution Date/Time of a Stored Procedure

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

If you want to get the  Last Execution Date/Time of a Stored Procedure then here is solution. Sys.dm_exec_procedure_stats it’s a system dynamic view that returns aggregate performance statistics for cached stored procedures. This view has been introduced from SQL Server 2008. The view returns one row for each cached stored procedure plan, and the lifetime of the row is as long as the stored procedure remains cached. When a stored procedure is removed from the cache, the corresponding row is

» Read more
,

SQL Server 2008 R2- Find Database Collation

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

How to find collation of current database? There are two different ways to find out SQL Server database collation. Using T-SQL SELECT DATABASEPROPERTYEX('Database_Name', 'Collation') as SQLCollation; Using SQL Server Management Studio (SSMS) Right Click on the database Go to Properties You will get the dialog box as shown in the figure   You can execute the system function fn_helpcollations to retrieve a list of all the valid collation names for Windows collations and SQL Server collations: SELECT * FROM fn_helpcollations()

» Read more
,

SQL Server – Stored Procedure with Output Parameters

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

In SQL Server, there are two ways with we can pass parameters to procedures. INPut parameter OUTPut parameter INPut parameter These types of parameters are used to send values to stored procedures. OUTPut parameter These types of parameters are used to get values from stored procedures. This is similar to a return type in functions. In this post we will try to understand how stored procedure returns the parameter Example — Create a table CREATE TABLE myTable ( ID INT

» Read more
,
1 2 3 4 5 … 8