Keep it Simple and Smart
I have seen many different reasons and point of view on the use of cursors. Some people never used, some used at last resort and some used regularly. In each of these, they must have different reasons for their stand on cursor usage. There must have a reason or place where we can use cursors in an efficient manner. Cursors are a bad choice, unless you understand enough about them to justify their use in limited circumstances. Cursors have their
» Read moreHere is an example – Import CSV file to SQL table using Bulk-Insert CSV file means Comma Separated Values file. USE testdb GO CREATE TABLE table1 ( id INT, name VARCHAR(255), address VARCHAR(255) ) GO Create CSV file in Shared folder on any remote location with name csv.txt Suppose location of the file is \\dcc-566\Share\CSV.txt Content of CSV file as : Now let‘s Import this CSV file into Table1. BULK INSERT table1 FROM '\\dcc-566\Share\CSV.txt' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR
» Read moreHere is new feature of SQL Server 2008 – “Comma Separated INSERT Option” In this feature using a single INSERT statement you can pass multiple value for the table using comma separated option. USE testdb GO CREATE TABLE table1 ( id INT IDENTITY(1,1), name VARCHAR(255), address VARCHAR(255) ) SELECT * FROM testdb..table1 INSERT INTO testdb..table1 VALUES (‘Varinder’,’ABC Street’), (‘Amit’, ‘CDE Street’), (‘Dinesh’,’XYZ Street’) Result :
» Read moreHere is an example: Inserting same value multiple time in SQL table USE testdb GO CREATE TABLE table1 ( id INT IDENTITY(1,1), name VARCHAR(255), address VARCHAR(255) ) SELECT * FROM testdb..table1 INSERT INTO testdb..table1 VALUES (‘Varinder’,’ABC Street’) go 5 Result :
» Read moreDatabase backup are insurance policy for a database, we need to ensure that backups created are valid and useful. To validate the backup, we can use following command Restore verifyonly from <backup device> When a backups is validated, SQL Server performs the following steps Calculated a checksum for the backup and compare to checksum stored in the backup files Verify that the header of backup is correctly written and valid Transits the page chain to ensure that all pages are
» Read moreSharing with you script to backup all the SQL server databases Script: DECLARE @name VARCHAR(50) — database name DECLARE @path VARCHAR(256) — path for backup files DECLARE @fileName NVARCHAR(256) — filename for backup DECLARE @fileDate VARCHAR(20) — used for file name SET @path = ‘D:Backup’ SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),113) SELECT name,flag=0 INTO #temp FROM master.dbo.sysdatabases WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’) –according to requirement we can filter databases in where clause SET ROWCOUNT 1 WHILE (exists(SELECT * FROM #temp WHERE flag=0))
» Read moreHello Friends, I have setup the Linked server with the help of script here. My one of colleague helps me to write the script with we can map all user with one statement. This script will map all local SQL server users with remote user that you provide in the script. You don’t need to map all user manually. Script: EXEC master.dbo.sp_addlinkedserver @server = N‘dsql2k’, @srvproduct=N”, @provider=N‘SQLOLEDB’, @datasrc=N‘servername’ EXEC master.dbo.sp_addlinkedsrvlogin@rmtsrvname=N‘dsql2k’,@useself=N‘False’, @locallogin=NULL,@rmtuser=N‘sa’,@rmtpassword=‘XXXXXX’ if anybody have any other experience, please share as
» Read moreDear Friends, I have tried the Utility SQL Search, it is really helpful for me… Thought it will be handy to all of us and it is a free tool from Red Gate. SQL Search is an add-in for SQL Server Management Studio that lets you quickly search for SQL across your databases. Click here for more detail.
» Read moreOnce you restore a Microsoft SQL Server database on a different machine, you cannot access the database until you fix the users. The problem is that the user in the database is an “orphan”. This means that there is no login id or password associated with the database user So before accessing database we need to maps an existing database user to a SQL Server login. For that use sp_change_users_login to link a database user in the current database with
» Read moreIdentity columns are commonly used as primary keys in database tables. These columns automatically assign a value for each new row inserted. But sometime Identity columns missed the value, we want to insert missed value into the column. For that we need to enable IDENTITY_INSERT for the table. USE mytempDB GO CREATE TABLE myTable ( myIdentity INT NOT NULL IDENTITY(1,1) PRIMARY KEY, myValue NVARCHAR(30) NOT NULL ) GO INSERT myTable(myIdentity, myValue) VALUES (5, ‘Varinder Sandhu’) –Result = Error because IDENTITY
» Read moreHello Friends Generally, we choose the @@VERSION to get the version information for SQL Server, but using this function we can’t get the Service Pack information installed… Alternative way Get Editions of SQL Server SELECT SERVERPROPERTY(‘EDITION‘) AS EDITION Get Version Detail of SQL Server SELECT SERVERPROPERTY(‘PRODUCTVERSION‘) AS VERSION Get the Service Pack installed on SQL Server SELECT SERVERPROPERTY(‘PRODUCTLEVEL‘) AS SERVICEPACK
» Read more— To allow advanced options to be changed EXEC sp_configure ‘show advanced options’, 1 GO —- To update the currently configured value for advanced options. RECONFIGURE GO —- To enable the feature. EXEC sp_configure ‘xp_cmdshell’, 1 GO —- To update the currently configured value for this feature. RECONFIGURE GO Alternative way 1. Click the Start button. 2. Select All Programs. 3. Navigate to the Microsoft SQL Server 2008 folder. 4. Right Click on Server name then click facets options 5. In the Facets Option choose Surface Area Configuration in dropdown 6. At the bottom of the window, mark True for xp_cmdshell. 7. Click OK.
» Read more