Add the value into an Identity Column in SQL Server

Identity 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 is OFF
–Below enable IDENTITY_INSERT

SET IDENTITY_INSERT myTable ON

INSERT myTable(myIdentity, myValue)
VALUES (5, ‘Varinder Sandhu’)

SET IDENTITY_INSERT myTable OFF

Note:
Once we enabled IDENTITY_INSERT on a table you must specify a value for the identity column.

–If IDENTITY_INSERT is OFF then use  below insert statement for above example

INSERT myTable(myValue)
VALUES (‘Varinder Sandhu’)

Find EDITION, VERSION and SERVICEPACK information of SQL Serve

Hello 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

SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY not working with Linked Server or Remote server ???

Hello Friends,


I have faced a problem using SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY with Linked server.

Let me explain with example as below

Question: 

We have set of applications which are running on SQL 2000, due to some reason we need to migrate sub set of applications to SQL 2008.
Now the few applications running on SQL 2008 and few on SQL 2000, applications running on SQL 2008 using some of databases which lie on SQL 2000 with Linked server

Now the problem is the some triggers and stored procedures which need to insert the values generated from SQL 2008 to SQL 2000 databases using functions SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are not working in this scenario.

We are fixing this problem by writing another select statement for selecting identity based on unique parameters in that scope.

We are not aware why this is happening and if there is any solution available.


Answer:


The scope of the @@IDENTITY function is current session on the local server on which it is executed. This function cannot be applied to remote or linked servers. To obtain an identity value on a different server, execute a stored procedure on that remote or linked server and have that stored procedure (which is executing in the context of the remote or linked server) gather the identity value and return it to the calling connection on the local server.

Same for SCOPE_IDENTITY, IDENT_CURRENT also......

If anybody has any idea, please share as comment…

Bullzip FREE PDF Printer

Hello Friends

I have tried the Utility Bullzip PDF Printer, it really helpful for me….
That’s why I am sharing with you all …

Introduction

The Bullzip PDF Printer works as a Microsoft Windows printer and allows you to write PDF documents from virtually any Microsoft Windows application.
This program is FREEWARE with limitations, which means that it is FREE for personal and commercial use up to 10 users. It does not contain any advertising or popups. For commercial applications with more that 10 users there is a commercial version of the product available at www.biopdf.com.

Features

• Print to PDF from almost any Windows program.
• Runs on Microsoft Windows 2000/XP/XP x64/2003/2003 x64/Vista/Vista x64/2008/Windows 7.
• Supports 64-bit operating systems.
• Direct output to the same file each time or prompt for destination.
• Control if the printer should ask if you want to see the resulting PDF document.
• Control output and prompts programmatically.
• Setup can run unattended.
• Graphical user interface.
• Password protect PDF documents.
• 128/40 bit encryption.
• Quality settings (screen, printer, ebook, prepress).
• Set document properties.
• Watermark text, size, rotation, and transparency.
• Superimpose/background documents.
• Appending/prepending documents.
• User interface control.
• Command line interface to all settings.
• COM/ActiveX interface for programmatic control.
• Support for Citrix MetaFrame
• Support for Windows Terminal Server
• Multiple output types supported: BMP, JPEG, PCX, PDF, PNG, and TIFF.

Download URL :  http://www.bullzip.com/products/pdf/info.php

Reference URL 

SQL SERVER 2008 – Enable xp_cmdshell

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