Anuj Rathi

What Is The Difference Between Set And Select

2010-09-25T12:13:00.000+05:30

Here is The Combined answer that i have collected from various articles and experience. Here is the Difference. 1. SET is the ANSI standard for variable assignment, SELECT is not. 2.You can use SELECT to assign values to more than one variable at a time. SET allows you to assign data to only one variable at a time. Here's how: /* Declaring variables */ DECLARE @Variable1 AS int, @Variable2

SQL Server Import and Export Wizard

2010-05-26T15:15:00.000+05:30

Yesterday, I was to Upload very large data. So I opened my Sql Server management studio & tried Import/Export wizard. & Surprise !!, I was not able to start Import/Export wizard. The SSIS Data Flow Task could not be created. Verify that DTSPipeline.dll is available and registered. The wizard cannot continue and it will terminate. ADDITIONAL INFORMATION: Cannot create a task with the name "STOCK

string.IsNullOrWhiteSpace()

2010-02-25T14:27:00.000+05:30

.NET 4 adds new method called string.IsNullOrWhiteSpace() which checks for spaces, empty or null. This is a nice time-saver for developers.. This static method returns true if a string is full of whitespace characters. Let us consider the below example . static void Main() { string strTest = "Simple Talk"; string strNull = null; string strEmpty = string.Empty; string strWhiteSpace = "\t\r\n\n ";

Stored Procedures vs. User Defined Functions in Microsoft SQL Server

2010-02-20T12:56:00.000+05:30

SQL Server user-defined functions and stored procedures offer similar functionality. Both allow you to create bundles of SQL statements that are stored on the server for future use. This offers you a tremendous efficiency benefit, as you can save programming time by: Reusing code from one program to another, cutting down on program development time. Hiding the SQL details,

Benefits of SQL Server Stored Procedures

2010-02-20T12:51:00.000+05:30

Microsoft SQL Server provides the stored procedure mechanism to simplify the database development process by grouping Transact-SQL statements into manageable blocks. Benefits of Stored ProceduresWhy should we use stored procedures? Let's take a look at the key benefits of this technology: Precompiled execution. SQL Server compiles each stored procedure once and then reutilizes the execution

List all Constraints in Sql Server

2010-02-03T12:45:00.000+05:30

Hi All, Yesterday, I was facing a problem. Actually I was to rename all the constraints as our Standards. When I create tables in query analyzer, Sql Server create Constraints name itself. Now, I have to change all constraints according to naming conventions. This is the below script by which I get all the constraints in my database. SELECT OBJECT_NAME(OBJECT_ID) AS ConstraintName,

Find all dependent objects of an Object

2010-01-18T21:12:00.000+05:30

You can use this system Stored Procedure to find the all dependencies of an Object. EXEC sp_depends @objname = N'StoredProcedureName' ;

How to check the SQL Server Varsion, Name & Edition

2010-01-18T18:18:00.000+05:30

WE can see the Sql Server Name, version, edition etc using below query: SELECT @@SERVERNAME , SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

View all Database File Sizes

2010-01-14T18:03:00.000+05:30

SELECT s_mf.[name], s_mf.physical_name, s_mf.[size]/128 AS SizeInMB, s_mf.max_size/128 AS MaxSizeInMB, s_mf.growthFROM sys.master_files s_mfORDER BY s_mf.[size] DESC Above query will return Database Name, its physical path & database files size in MB. Happy Coding !!

How to Encrypt Stored procedures in Sql Server

2010-01-13T21:13:00.000+05:30

At times, it is needed that you encrypt the text of stored procedures containing sensitive information. SQL Server provides the WITH ENCRYPTION to encrypt the text of the stored procedure. CREATE procedure [dbo].[TestProc] WITH ENCRYPTION AS BEGIN SELECT 'TEST' as TestColumn END Once the stored procedure has been created WITH ENCRYPTION, attempts to view the stored procedure returns a message

ASP.NET Session State Management Using SQL Server

2009-12-19T16:41:00.000+05:30

Web applications are by nature stateless. Statelessness is both an advantage and a disadvantage. When resources are not being consumed by maintaining connections and state, scalability is tremendously improved. But the lack of state reduces functionality severely. Ecommerce applications require state to be maintained as the user navigates from page to page. ASP.NET’s Session object makes it easy

Session.SessionID is not unique

2009-12-19T14:56:00.001+05:30

I have seen many forums in which users are asking that Session.SessionID is not unique. You also have seen forums saying that “I am getting different value for SessionID on every page or in each post back.” Yes they are correct !!!! “This is not possible. How can it be?”, I know this is your reaction. We have read in all the books and seen practically that SessionID is unique, until user logs off

Select Database Schema Name with their Owner Name

2009-11-27T16:49:00.000+05:30

SELECT s.[name], s.[schema_id], aa.CATALOG_NAME, aa.SCHEMA_OWNER FROM sys.schemas s JOIN INFORMATION_SCHEMA.SCHEMATA aa ON aa.[SCHEMA_NAME] = s.[name]

List all Error Codes with description

2009-11-25T16:02:00.000+05:30

Recently, I was facing some problem to check the proper error code in my procedure. Thanks to Sql Server 2005. It gives a great user friendly Schema & System table names. So This is the query by which we can see all the Error Codes, SELECT s.[error], s.severity, s.dlevel, s.[description], s.msglangid FROM sys.sysmessages s Now, I can use Try-Catch block in sq l server 2005 & can raise

Select all Stored Procedures with their parameters

2009-10-30T12:25:00.000+05:30

SELECT s.[name], s2.[name], s2.type, s2.length, s2.colorder, s3.[name] FROM sysobjects sLEFT JOIN syscolumns s2 ON s.id = s2.idLEFT JOIN systypes s3 ON s3.[xusertype] = s2.xusertypeWHERE s.[type] = 'P' AND s.category = 0 ORDER BY s.[name], s2.colorder

Get Current User – Get Logged In User in sql server 2005

2009-10-06T19:44:00.002+05:30

To get current user run following script in Query EditorSELECT SYSTEM_USERSYSTEM_USER will return current user. From Book On-Line – SYSTEM_USER returns the name of the currently executing context. If the EXECUTE AS statement has been used to switch context, SYSTEM_USER returns the name of the impersonated context.Alternatively we can also use:SELECT SUSER_SNAME()

Find Tables With Foreign Key Constraint in Database

2009-10-06T13:48:00.001+05:30

SELECTK_Table = FK.TABLE_NAME,FK_Column = CU.COLUMN_NAME,PK_Table = PK.TABLE_NAME,PK_Column = PT.COLUMN_NAME,Constraint_Name = C.CONSTRAINT_NAMEFROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS CINNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAMEINNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAMEINNER JOIN

show the parameters for a function or stored procedure

2009-10-06T13:38:00.000+05:30

SELECT PARAMETER_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTHFROM INFORMATION_SCHEMA.PARAMETERSWHERE SPECIFIC_NAME='Procedurename'

List Schema Name and Table Name for Database

2009-10-06T13:36:00.001+05:30

SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']'AS SchemaTableFROM sys.tables

Execute permissions to db_executor Role

2009-10-05T18:37:00.002+05:30

Giving users access to only stored procedure execute permissions is a common issue. It is a good security practice. In 2005, we can grant excecute permissions at the database level instead of having to do it on each and every stored procedure, which was a pain. So now people do this:CREATE ROLE db_executorGRANT EXECUTE TO db_executorBut I would definitely say that granting at the database level

Difference between Varchar & Nvarchar in Sqlserver

2009-09-26T11:09:00.003+05:30

Varchar means Variable-length Character string.Nvarchar will store Unicode characters.Both will be used all most for the same purpose but with little difference.Varchar will store the 8-bit data in database where as Nvarchar will be stored as 16-bit data in Database.In Sql server 2005The size for a table page is 8,196 bytes, and no one row in a table can be more than 8,060 characters.This in turn

Create Table dynamically in sql server

2009-09-26T10:43:00.001+05:30

I'd like to create a stored procedure that takes the name of table as a parameter, and then creates that table, if it doesn't exist, according to a schema stored somewhere.Is that possible-- Can I store a schema and use is as a template to create tables from? Or must I hard-code the CREATE TABLE and its scheme in the stored procedure itself?The idea here is to be able to change the schema without

Find Current User Information

2009-09-11T15:28:00.003+05:30

SELECT spid, uid=rtrim(loginame), Program_name=rtrim(Program_name),dbname=db_name(dbid), status=rtrim(status) FROM master.dbo.sysprocesses WHERE loginame = 'UserName';You can also use sp_who command to see all users associated with current database.

Retrieve and Upload data From ExcelWorkbook

2009-09-11T15:27:00.000+05:30

I got this link is very helpful when you want to upload data in bulk from the excel workbook or you have to retrieve the data from the excel workbook. http://support.microsoft.com/default.aspx?scid=kb;en-us;316934#12

Difference between Web Services and Remoting?

2009-09-11T15:24:00.000+05:30

Both Remoting and Web Services are ways of communication between applications. Remoting - In remoting, the applications involved in the communication process may be located on the same computer, different computers in a same or different network. In remoting, both applications know about each other. A proxy of an application object is created on the other application. Web Services - Communication