Pages

Thursday, June 18, 2009

What Happens When You Issue INSERT statement

Today I was in the interview panel, and one of the panelists asked one very basic question. The question is “What happens when you execute INSERT statement on table which has Identity column, Primary Key, Check constraint, Not Null Constraint, Foreign key, Instead Of Trigger and After Trigger.” Which one gets executed first?

This question looks very simple but its bit tricky. by the time he answers the question, I was trying to find the answer to this by creating simple example.

The Order is:

1) IDENTITY Insert check
2) Not Null Constraint Validation.
3) Data Type Verification
4) Instead of trigger execution If it exists.
5) Primary key Constraint Validation
6) Check Constraint Validation
7) Foreign Key Constraint Validation
8) After Trigger Execution

Wednesday, June 17, 2009

Insert Multiple Rows with Single INSERT statement in SQL Server

Today, I am going to give you simple and efficient TIP to insert multiple records with single INSERT statement.
--Lets Create a Dummy table
CREATE TABLE EMPLOYEE(
EMP_ID INT IDENTITY(1,1),
FIRST_NAME VARCHAR(30),
MIDLE_NAME VARCHAR(30),
LAST_NAME VARCHAR(30),
SALARY BIGINT,
DEPARTMENT_ID INT
)

-- Single Insert statement with multiple Value Clauses
INSERT INTO Employee(FIRST_NAME,MIDLE_NAME,LAST_NAME,SALARY,DEPARTMENT_ID)
Values ('Vijaya','Krishna','Kadiyala',20000,10),
('Namrath',Null,'Kadiyala',40000,10),
('Glen',Null,'Jhonson',35000,20),
('Ray','Fine','Muran',21000,20)

That’s it.

If we didn’t had this feature then we had to use following techniques to do the same.

Using Multiple Insert statements:


INSERT INTO Employee(FIRST_NAME,MIDLE_NAME,LAST_NAME,SALARY,DEPARTMENT_ID)
Values ('Vijaya','Krishna','Kadiyala',20000,10);


INSERT INTO Employee(FIRST_NAME,MIDLE_NAME,LAST_NAME,SALARY,DEPARTMENT_ID)
Values ('Namrath',Null,'Kadiyala',40000,10);


INSERT INTO Employee(FIRST_NAME,MIDLE_NAME,LAST_NAME,SALARY,DEPARTMENT_ID)
Values ('Glen',Null,'Jhonson',35000,20);


INSERT INTO Employee(FIRST_NAME,MIDLE_NAME,LAST_NAME,SALARY,DEPARTMENT_ID)
Values ('Ray','Fine','Muran',21000,20);

Using SELECT statement with Union All:


INSERT INTO Employee(FIRST_NAME,MIDLE_NAME,LAST_NAME,SALARY,DEPARTMENT_ID)
SELECT 'Vijaya','Krishna','Kadiyala',20000,10
UNION ALL
SELECT 'Namrath',Null,'Kadiyala',40000,10
UNION ALL
SELECT 'Glen',Null,'Jhonson',35000,20
UNION ALL
SELECT 'Ray','Fine','Muran',21000,20

Tuesday, June 16, 2009

Filtering Sensitive Text in SQL Server

A very common concern when dealing with sensitive data such as passwords is how to make sure that such data is not exposed through traces. SQL Server can detect and filter the SQL statements in traces that include the usage of DDL and built-ins (such as OPEN SYMMETRIC KEY, and EncryptByKey) that are known to include potentially sensitive data.

Check out the complete article Filtering (obfuscating) Sensitive Text in SQL Server from Raul Garcia

Monday, June 15, 2009

Reading XML file in PowerShell

Today, I am going to talk about reading an XML file in PowerShell.

Let’s define the XML structure file:


Declare a variable to get the content of XML file. Make sure the file path. In my case file is located in H:\ Drive and also my powerShell Home directory is H:\. I would always recommend you to use Full Path of the file.

PS H:\> $EmpList = get-content Employee_details.xml

And use the path to display the XML content information in tabular format.

PS H:\> $EmpList.Employee.Row


Dept_ID Dept Dept_Mgr
------- ---- --------
1 Admin Krishna
2 Finance Kadiyala
3 HR Vijaya
4 Consulting Vijaya Kadiyala

Sunday, June 14, 2009

Powerful text editor - UltraEdit

In this article I would like to explain some of the cool features of UltraEdit v15.00 that I liked when I was working with it. This tool will significantly save your time when you are at work.

Below are the some of the features that I had explored.

Macros to do the repetitive tasks:
One very common task that we do in our day-to-day life is “Find & Replace”. If you want to perform Find & Replace of certain characters in all the files with in a directory then you need to write your own program to do it or you need to use some special DOS commands. But with UltraEdit you can write a macro to do all these for you. Creating a macro is very similar to creating it in Excel Sheet. You can also assign Short Cut key to this while creating a macro. This will definitely save lot of time if you are doing repetitive tasks. In addition to this we can also edit macros and add existing commands of UltraEdit to perform some more tasks.

Summary to see the Occurrences of the string:
You can also see the summary of the occurrences of the string in a file. If you use “Highlight All Items Found” in Find dialog box under advanced section, it will highlight the occurrence of the string in the file. In addition to this if you select the option “List Lines Containing the String” then it will display summary dialog box with the line numbers of the string in file.

Opening Browser from Your file with the selected string:
One another cool feature of this tool is to open the web browser. You can simply select the text and click on any of the Icons of Google, yahoo or WikiPedia to open web Browser and search of the selected string. This will certainly save a lot of time. This is just click away for search.

Select specific portion of the file content:
This is very interesting feature of this tool, where you can change the mode of selection to column and then you can select vertically. In addition to this, you can also apply Sum operation based on the selection.

Regular Expression to replace:
If we want to remove all the blank lines in a file with out using regular expressions, imagine how much work we need to do. If we have a regular expression option in the tool then in just few clicks you can replace all the blank lines. Any tool which provides this feature is a great tool.

Find History:
When ever you search for something in a file, it stores the search string for future use. So in this way you don’t have to remember the search string. This is also applicable to replace.

Like this, there are so many countless features available in this tool. Check out the cool tips on what you can do with this tool. Use this tool to work in a smart way…..

Friday, June 12, 2009

How to Find out the dependencies using SQL Server Management Studio

Today I would like to give you simple tip to find out all the dependencies on a table using SQL Server Management Studio.
>>>> Open and Login into SQL Server Management Studio (SSMS)>>>> Choose the database in the Object Explorer
>>>> Expand the Tables section
>>>> Right Click on the Table Name and Click on View Dependencies.





I have only two objects which depends on this table.

Interesting Observation on IntelliSense in SQL Server – Part 1

In my previous article I talked about one interesting observation on SQL Server Intellisense. Today I came across another one. I hope Microsoft is going to improve the intellisense in the next release of Service Pack or probably in the next version of SQL Server.

I am using following statement to create a new table and load the data.

SELECT * INTO EMP_TMP
FROM EMPLOYEES


And then querying data from new table, There is nothing wrong the below statement. It is just that Intellisense is not updated with the information.

SELECT * FROM EMP_TMP

If you look at the below screen shot there is red line around EMP_TMP table. When I hover the mouse on EMP_TMP it displays “Invalid Object Name ‘EMP_TMP’”.

Thursday, June 11, 2009

Most Power Full commands in PowerShell

Today I am going to talk about the 3 most power full commands you need to know to get started on PowerShell.

The three Power Full Commands are
1) get-help
2) get-command
3) get-member

get-help command displays the information about cmdlets and concepts. You can also use “help” command to get the same kind of information.

PowerShell DotNetVJ:\> Get-help get-command

This command would display short or one page description about the command. If you want more and detailed information then you need to pass “-Detailed” parameter.

PowerShell DotNetVJ:\> Get-help get-command -Detailed

The above command displays all the information related to get-command which spans in multiple and you have scroll thru to read everything. Add “ more” to limit the out put to one page and press Space key to continue.

You can also get the help about get-help command.

PowerShell DotNetVJ:\> Get-help get-help

Get-command command displays the information about cmdlets and other elements of PowerShell commands.

PowerShell DotNetVJ:\> get-command

The above command displays all the cmdlets. You can also use wild characters to limit the records.

Lets say you want to display all the cmdlets starting with “write” then simply issue the below command.

PowerShell DotNetVJ:\> get-command write*

Get-Member gets information about the members of objects. This method can accept the value from the pipeline or from inputObject parameter. Your output depends on how you are passing the input to this command.

PowerShell DotNetVJ:\> get-command get-member


This one gave just the definition of the get-member. Now lets look at the command with Pipe character.

PowerShell DotNetVJ:\> get-command get-member
If you look at the output both are totally different. The one with pipe gave lot of information like what are the methods that are allowed on this get-command.

Wednesday, June 10, 2009

An Introduction to Oslo

What is Oslo?
"Oslo" is the code name for a family of new technologies that enable data-driven model based development. First we will explore the nature of model driven development and then apply the concept of model driven development to Oslo.

Speaker:
Stephen Forte, TelerikStephen Forte is the Chief Strategy Officer of Telerik, a leading vendor in .NET components.

Date: Thursday, June 18, 2009
Time: Reception 6:00 PM , Program 6:15 PM
Location: Microsoft , 1290 Avenue of the Americas (the AXA building - bet. 51st/52nd Sts.) , 6th floor

You must register at https://www.clicktoattend.com/invitation.aspx?code=138919 in order to be admitted to the building and attend.

Reference: NYC .NET Developer Group

.Net Interview Questions

ASP.NET
ASP.Net Interview Questions - Part 1
ASP.Net Interview Questions - Part 2
ASP.Net Interview Questions - Part 3
ASP.Net Interview Questions - Part 4
ASP.Net Interview Questions - Part 5
ASP.Net Interview Questions - Part 6
ASP.Net Interview Questions - Part 7
ASP.Net Interview Questions - Part 8
ASP.Net Interview Questions - Part 9
ASP.Net Interview Questions - Part 10
ASP.Net Interview Questions - Part 11
ASP.Net Interview Questions - Part 12

C#
C# Interview Questions

Very Famous Error in PowerShell

In this post i would like to give you the solution to a very famous error in PowerShell.

File H:\WindowsPowerShell\profile.ps1 cannot be loaded because the execution of scripts is disabled on this system. Please see "get-help about_signing" for more details.At line:1 char:2+ . <<<< 'H:\\WindowsPowerShell\profile.ps1'

If you are getting the above error it means you are trying to execute some scripts (.ps1) or some configuration files (.ps1xml).

This error is related to the execution Policy of the PowerShell. Use Get-ExecutionPolicy command to find out the current execution policy.

PS H:\> get-ExecutionPolicy
Restricted

Now change the execution policy to "UnRestricted" to resolve or eliminate the error.

PS H:\> Set-ExecutionPolicy UnRestricted
PS H:\>

That's it :)

Changing Prompt And Execution Policy in PowerShell

In this article I am going to show you, how to change the prompt of your PowerShell and few lines about Execution Policy in PowerShell.

When you start the PowerShell I am getting following prompt
PS H:\>




I want to change this PS H:\> to PowerShell VJ:\>.

In order to change this first you need to understand Execution Policy. Execute the
get-ExecutionPolicy command to find out the current execution policy.

PS H:\> get-ExecutionPolicy
Restricted
PS H:\>


Execution Policy is not thing but set of rules that governs how PowerShell execute scripts on your machine.

There are 4 different execution policies in PowerShell.

Restricted:
If the Current Execution Policy is set to Restricted then You can't exeucte any scripts (.ps1) or configuration (.ps1xml) files.

AllSigned:
If the Current Execution Policy is set to AllSigned then Scripts (.ps1) and Configuration (.ps1xml) files must be digitally signed.

Remote Signed: Ps1 and .Ps1xml files from the internet must be digitally signed.
If the Current Execution Policy is set to RemoteSigned then Any Scripts (.ps1) and Configuration (.ps1xml) files which are downloaded from the internet must be digitally signed.

Unrestricted: No digital signatures are required and all scripts can be executed.
If the Current Execution Policy is set to Unrestricted then All scripts and configuration files can be executed.

There are two different ways in which you can change the execution policy.
The first method, using Set-ExecutionPolicy command and second method is using Windows Registry. I do not recommend you to modify the execution policy using Windows Registry.

Using Set-Execution Policy
PS H:\> set-executionpolicy Unrestricted
PS H:\>

What this does is, it will create an entry in the registry in the following location.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.PowerShell

"ExecutionPolicy"="UnRestricted "

Using Registry
Goto the following location
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.PowerShell

Add new string Value If it doesn’t exists.

Name = ExecutionPolicy
Value = UnRestricted

Now lets see, how to change the prompt.
Create “WindowsPowerShell” Folder in "My Documents" Folder and create profile.ps1 file.

Put the following lines of code in that

function prompt
{
"PowerShell DotNetVJ:\> "
}


And start the PowerShell to see new the Prompt.

Windows PowerShell
Copyright (C) 2006 Microsoft Corporation. All rights reserved.

PowerShell DotNetVJ:\>

That’s it.

Tuesday, June 9, 2009

Working with Arrays and Hash Tables in PowerShell

PowerShell variables can also store arrays. Each item is assigned a unique Index number and starts with 0.

To create an array in PowerShell, create a variable and assign list of values separated by comma.

PS H:\> $MyArray = ('A','B','C','D','E','F','G','H')

To display the values just use square brackets along with the Array Index.

PS H:\> $MyArray[0]
A


You can simply type the PowerShell Variable names to display all the values in that array.

PS H:\> $MyArray
A
B
C
D
E
F
G
H


To Get the length of the array just use the Count Property of the Array.

PS H:\> $MyArray.Length
8


Updating Array

You can also change any element in array by using the Index.
PS H:\> $MyArray[4] = "VIJAY"
PS H:\> $MyArray
A
B
C
D
VIJAY
F
G
H

Adding new element to Existing Array

You can’t add new element to the existing array. You need to create new array and using + operator you can add new elements to existing array.

PS H:\> $MyNewArray = ('KADIYALA','KRISHNA')
PS H:\> $MyArray = $MyArray+$MyNewArray
PS H:\> $MyArray
A
B
C
D
VIJAY
F
G
H
KADIYALA
KRISHNA

You can also specify the range of the integers to create an array. You just have to specify ranges of integers using the .. operator

PS H:\> $MyArray = 1..10
PS H:\> $MyArray
1
2
3
4
5
6
7
8
9
10

Creating Multi Dimensional Arrays
Creating multi dimensional array is very similar to creating single dimensional arrays. You need to enclose the values in “(“and “)”.

PS H:\> $MyMultiArray = (('A','B'),('C','D'),('E','F'))
PS H:\> $MyMultiArray
A
B
C
D
E
F
PS H:\> $MyMultiArray[0]
A
B
PS H:\> $MyMultiArray[0][0]
A
PS H:\> $MyMultiArray[1][0]
C

Creating Hash Tables

Hash tables are very similar to multi dimensional arrays. The major difference between Multi dimensional and Hash tables are the Index. In Hash tables you can use your own index name to read the values.

PS H:\> $MyHash = @{'First_Name'='Vijaya'; 'Middle_Name'='Krishna'; 'Last_Name'='Kadiyala'}
PS H:\> $myHash['First_Name']
Vijaya
PS H:\> $myHash['Middle_Name']
Krishna
PS H:\> $myHash['Last_Name']
Kadiyala
PS H:\> $myHash['First_Name'] + ' ' + $myHash['Middle_Name'] + ' ' + $myHash['Last_Name']
Vijaya Krishna Kadiyala
PS H:\>

Monday, June 8, 2009

Expression in PowerShell

Additional capability of PowerShell is expressions.

Let’s look at the simple example

PS H:\> (150*15)/20
112.5
PS H:\>

One thing to note here is PowerShell displayes the values immediately. Where in other scripting languages you need to use either variable or use special commands to print the value.

You can also store the output of this expression into a variable.

PS H:\> $Result = (150*15)/20
PS H:\> $Result
112.5
PS H:\>

Friday, June 5, 2009

Windows Mobile 6.5 Developer Tool Kit

The Windows Mobile 6.5 Developer Tool Kit adds documentation, sample code, header and library files, emulator images and tools to Visual Studio that let you build applications for Windows Mobile 6.5. This document contains important information about this package. For general information about writing software for Windows Mobile, please see the Windows Mobile Developer Center. The Windows Mobile 6 SDK must also be installed in order to use any of the Windows Mobile 6.5 Gesture API or samples. Windows Mobile 6.5 Developer Tool Kit comes with the following Emulator Images:
>> Windows Mobile 6.5 Professional Square Emulator
>> Windows Mobile 6.5 Professional QVGA Emulator
>> Windows Mobile 6.5 Professional WQVGA Emulator
>> Windows Mobile 6.5 Professional VGA Emulator
>> Windows Mobile 6.5 Professional WVGA Emulator
>> Windows Mobile 6.5 Standard Square Emulator
>> Windows Mobile 6.5 Standard QVGA Emulator

Download Windows Mobile 6.5 Developer Tool Kit

Thursday, June 4, 2009

Variables in PowerShell

Why do we need a variable?
Variable is a storage place for data. In PowerShell we can store almost anything, from strings to Objects.

How do you define a Variable?
To define a variable just prefix $ to any sequence of characters. PowerShell variables are not case sensitive. Variable can have any thing, starting from (A-Z) and (0-9) and (_).
There is no length restriction to the variable name but it is always good to follow the same naming conventions, which we use in .Net or in T-SQL programming languages.

Built-in Variables
When PowerShell is started, a number of variables are defined and assigned the environmental values.

PS H:\> set-location variable:
PS Variable:\> get-childitem





There are two types of built-in variables. One type of variable stores the configuration information for the current session and another type of variable stores the personal preferences.

Make your life easy with Alias
Alias makes the life very easy. PowerShell has alias names for most of its commands. So you don’t have to remember the complete command to use. You can use the alias to perform the same operation.

Get-process is the command to get all the processes on your machine and the alias for this is gps.

To get all the aliases just use get-alias method.




Working with alias
Several alias cmdlets enable you to define new aliases, export aliases, import aliases and display existing aliases. By using the following command, you can get a list of all related alias cmdlets.

Export and Import aliases are to export and import the aliases from different sessions. Get-Alias is to get the all aliases. New and Set Aliases are to define new aliases with in current session. If you want to persist your aliases then you need to set these things in profile.ps1.

One thing to remember is you can’t use predefined alias names to your own commands.
If you try to do that then you will get below error.

PS Variable:\> set-alias gps pwd
Set-Alias : Alias is not writeable because alias gps is read-only or constant and cannot be written to.

Scope of variables
Scope is a logical boundary in PowerShell. Scopes can be defined as global, local, script, and private.

Global scope applies to an entire PowerShell instance. These variables can’t be shared between the sessions. To declare these kinds of variables just prefix the $Global: to the variable.

Local scope variables are declared inside the function. Once the processing is completed you can’t access this variable.

Script scope variables are declared inside the script. The lifetime of this variable is as long as the script is running.

Private scope is similar to Local but only difference is this value can’t be inherited. To declare these kinds of variables just prefix the $Private: to the variable.


Is TRUNCATE faster to delete the data from a table?

When I want to delete all the records from a table, the only command that comes to my mind is TRUNCATE. Is there any command that is faster than TRUNCATE to delete the data? Answer YES. Instead of issuing TRUNCATE on a table, you can simply rename the table and CREATE new table with the same structure. Drop the Old table when there is less load on the server.

When we perform RENAME or DROP operations on a table, all the objects which depends on the table gets invalidated. So use sp_recompile stored procedure to compile all the objects which depends on this table.

Wednesday, June 3, 2009

Summer 2009 MSDN “Suburban” Roadshow

Peter Laudati and Bill Zack are happy to announce that they will be kicking off a late spring/early Summer 2009 series of the MSDN “Suburban” Roadshow! There’s a tendency for Microsoft (and other companies) to host live events in NYC as it’s the center of our area.
Agenda:
Up, Up, and Away! Storage in the Microsoft Cloud
Developing on Microsoft Windows 7
Using MS AJAX & jQuery with ASP.NET

Location Infragistics Headquarters 50 Millstone Rd., Building 100 East Windsor, NJ 08520
Date & Time June 11, 2009 1:00 PM - 5:00 PM
Registration Click here to Register Or call 877.673.8368 With Event ID: 1032415493

Location Set Focus 4 Century Drive Parsippany, NJ 07054
Date & Time June 16, 2009 1:00 PM - 5:00 PM
Registration Click here to Register Or call 877.673.8368 With Event ID: 1032415492

Location Microsoft New York Office 1290 Avenue of the Americas, 6th Floor New York, NY
Date & Time June 24, 2009 1:00 PM - 5:00 PM
Registration Click here to Register Or call 877.673.8368 With Event ID: 1032415487

Reference : NYC .NET Dev Group

To Get More details on this you need to register/subscribe to NYC .Net Dev User Group.

Announcing the NYC Agile Firestarter

Are you just starting out with Agile, XP or Scrum and need to get up to speed? Or do you know a thing or two about Agile but want to learn the basics so you can implement it in your organization? Then this Firestarter is for you. We'll take you from 0 to 60 in 8 hours. Bring a laptop with Visual Studio 2008 Express edition or better for an all day hands on seminar led by some of the NY area's Agile practitioners.

Register today, space is limited!

Reference: .Net NY Dev User Group

Find Active Connections in SQL Server Using PowerShell

Today i would like to show you simple code to find the active connections in SQL Server using PowerShell. PowerShell code is very easy to write. All you need is the Class names to connect to the Database to do the required job.

In Simple Steps:
1) Load the SMO object into the memory.
2) Create a new instance of SqlConnection Object
3) Assign the connection string.
4) Open the connection.
5) Create a new instance of SqlCommand Object.
6) Build the query
7) Set the Connection to Command Object
8) Execute the Command and store the results in DataReader.
9) Use While Loop to display the information.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')
$oConn = New-Object -TypeName System.Data.SqlClient.SqlConnection
$oConn.ConnectionString = "SERVER=WNYCDEF3Q9W721\VJ_LCL;Integrated Security = True"
$oConn.Open()

$Cmd = New-Object -TypeName system.data.sqlclient.sqlcommand
$sSQL = "SELECT db_name(dbid) as Database_Name, "
$sSQL = $sSQL + " count(dbid) as No_Of_Connections "
$sSQL = $sSQL + " FROM sys.sysprocesses "
$sSQL = $sSQL + " WHERE dbid > 0 "
$sSQL = $sSQL + " GROUP BY dbid "
$cmd.Connection = $oConn
$Cmd.CommandText = $sSQL
$data_reader = $Cmd.ExecuteReader()
$DB_Name
$No_Of_Conn

while ($data_reader.Read())
{

$DB_Name = $data_reader.GetString(0)
$No_Of_Conn = $data_reader.GetInt32(1)
Write-host "Database Name:" $DB_Name " " "No_Of_Connections:" $No_Of_Conn " "
}
$oConn.Close()

Interesting observation with IntelliSense in SQL Server 2008

IntelliSense is a great feature of SQL Server 2008 but some times it is giving me hard time.

Check out the below image.Can you see a red line just under the SP_DEPENDS stored procedure? When I hover the mouse on SP_DEPENDS it show “Incorrect syntax near ‘SP_DEPENDS”. Actually there is no issue with the incorrect syntax.

When I move this statement just above to the SELECT statement then message and red line disappears.

Tuesday, June 2, 2009

Very Common SQL Server Errors And Resolutions Part 3

In this article I would like to list out most frequently occurred errors and their solutions. This article will also you tell how to reproduce them.

Error: 1
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'VIJAY KADIYALA' to data type int.

Cause:
This error comes into the picture when you are performing operations on CHAR column and trying to convert into INT.

How to re-produce:
SELECT 'VIJAY KADIYALA' + 2

Solution:
Explicitly convert the value 2 using CAST or CONVERT function
SELECT 'VIJAY KADIYALA' + CAST(2 AS VARCHAR(2))

Error: 2
Msg 141, Level 15, State 1, Line 4
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

Cause:
This error comes into the picture when you are assigning all the column values from SELECT statement into Local variable don’t match.

How to re-produce:
DECLARE @FirstName VARCHAR(30)
DECLARE @LastName VARCHAR(30)

SELECT @FirstName = FIRST_NAME,@LastName = LAST_NAME,MIDDLE_NAME FROM EMPLOYEES
WHERE LAST_NAME = 'Kadiyala'

Solution:
Match the Local variables with the No.Of Column you ate retrieving from SELECT statement.


Error: 3
Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Cause:
This error comes into the picture when you are using the DISTINCT Clause in the SELECT statement and the column which appears in the ORDER BY doesn’t part of the SELECT clause.

How to re-produce:
SELECT DISTINCT FIRST_NAME,LAST_NAME FROM EMPLOYEES
ORDER BY MIDDLE_NAME

Solution:
When you are using DISTINCT clause make sure all the Order by Columns are also part of SELECT clause.

Error: 4
Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.

Cause:
This error comes into the picture when length of the value being inserted is longer than the length of the column.

How to re-produce:
INSERT INTO EMPLOYEES(FIRST_NAME) VALUES ('12345678901234567890123456789011')
The length of FIRST_NAME column is 30 Characters only.

Solution:
Make sure you pass the values based on the column Length.

Monday, June 1, 2009

PowerShell Articles

Variables in PowerShell
In this article, I discussed about System Level variables,Environmental Variables and User Defined variables alond with the scope of the variables.

Working with Arrays and Hash Tables in PowerShell
In this article, I discussed about creating Single, Multi dimensional arrays along with Hash Tables.

Expression in PowerShell
In this article, I discussed about working with expressions.

Changing Prompt And Execution Policy in PowerShell
In this article, I discussed about Changing the Prompt and understaning the execution policies.

Most Power Full commands in PowerShell
In this article, i discussed about the very famous commands in PowerShell with out which we can't work...

Reading XML file in PowerShell
In this article, I discussed about reading an XML file in PowerShell

list out the Databases in SQL Server using PowerShell
This is a small script to list out all the databases in SQL Server using PowerShell.

Find Active Connections in SQL Server Using PowerShell
This is a small script to find out all the active connections in SQL Server using PowerShell.

PowerShell Errors and Solutions:
Very Famous Error in PowerShell

list out the Databases in SQL Server using PowerShell

Today, I started to learn Windows PowerShell. Initially I thought it is difficult but by it surprised me completely. It’s very easy to learn in fact if you have knowledge of VBScript or .Net then it is much easy to learn.

Let’s look at the simple script that displays the Databases in SQL Server

PowerShell:
Step 1:
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') out-null

Step 2:
$sSQLServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "VIJAY_MACHINE\VJ_LCL"

VIJAY_MACHINE => is my Machine Name
VJ_LCL => is my SQL Server Instance Name

Step 3:
$SQLServerDatabases = $sSQLServer.Databases

Step 4:
$SQLServerDatabases Select Name,RecoveryModel,Size,SpaceAvailable

Output Is

Where can I use Windows PowerShell

I found one very interesting article, which talks about Windows Power Shell. This article explains the differences between various scripting languages in layman terms.

Check out the what is powershell used for a laymans comparison article for complete information.

Windows Azure Tools for Microsoft Visual Studio May 2009 CTP

Windows Azure Tools for Microsoft Visual Studio extend Visual Studio 2008 and Visual Studio 2010 Beta 1 to enable the creation, building, debugging, running and packaging of scalable web applications and services on Windows Azure. Installation includes the Windows Azure SDK.

New for the May 2009 CTP:
>> Support for Visual Studio 2010 Beta 1
>> Update for Visual Studio 2008
>> Improved integration with the Development Fabric and Storage services to improve the reliability of debug and run of Cloud Services from Visual Studio
>> Enhanced robustness and stability

Check out and download the complete information from Microsoft Site.

Bing is Live......

Microsoft's much awaited Search engine is on......My first comment,it looks so great and the features which i like are
1) Additional Information in the search results
2) 3D view of the maps
3) Bird's eye ...
4) Collections... in the Maps...
5) Sharing the Map Location and can also blog it...
6) Add a Stop in between Source and destinations in Maps..
7) Showing the directions based on Shortest Path and Shortest Distance....

Keep watching it's going to rock...

Need to explore more....