Welcome to SQLServer MVP Blog

To check file exists or not in c#

2012-01-30T18:02:00.000-08:00

Method to check whether the file is exist or not,

C#

if(System.IO.File.Exists(@”d:\mytest.xml”))

{

//True statements comes here

}

This code check whether a file named “mytest.xml” exist in the D:\ drive or not.

Cheers,
Venkatesan Prabu .J

Unable to invoke a class in C#

2012-01-27T11:35:00.000-08:00

I've tried to invoke an object for a class in the external project in Windows Forms (C#).

using

Somehow, I've added the dll as the reference.
Tried to add the namespace. Unfortunately, I couldn't include the namespace.

Digging the content, I found the real reason for this issue. The reason is,

1. The class is not declared as public
2. May be the issue is with the namespace.

Cheers,
Venkatesan Prabu .J
ExcelObject;

Exception from HRESULT: 0x800AC472

2012-01-27T10:58:00.000-08:00

Exception from HRESULT: 0x800AC472 - We will get this error while creating excel sheet through C# or while populating the data from the database considering SQL Server.

Reason: A normal issue happening due to cross-threading in the applicaiton.

It's due to user's interruption while Excel document creation or any other process interrupting Excel document creation.

Cheers,
Venkat

Toggle key issue in Windows Vista

2012-01-27T10:48:00.000-08:00

While pressing Control button for a quite long time, you will be entered into Toggle key mode.

It disables some of the necessary functionalities like moving the cursor. In turn, it will select the content instead of navigating the cursor.

Do not get tensed on this issue? here is the solution for the same.

http://askville.amazon.com/turn-filter-keys/AnswerViewer.do?requestId=10388385

In simple words, Press both the shift button, you will comeout from the toggle mode.

All the best.

Cheers,
Venkatesan Prabu .J
Head, KaaShiv InfoTech, Chennai

Dotnet 4.0 Chart issue

2012-01-24T17:01:00.000-08:00

While working with Chart control in .net 4.0. I found the below issue.

A chart element with the name 'Default' could not be found in the 'ChartAreaCollection'.

Reason for the issue: It indicates that, the element default is not initilized as ChartAreaCollection.

To resolve this problem, we need to create it.

chartl.ChartAreaCollection.Add("Default");

The above line will resolve the problem.

Cheers,
Venkatesan Prabu .J
Head, www.kaashivinfotech.com

Time dimesions in SSAS

2012-01-14T23:35:00.000-08:00

Wish you happy new year to all my readers.

Once I have started working on Time dimesions for SSAS cubes. I got a peculiar error while executing it. Below is the error,

A time dimension is required to enable this functionality. Ensure that you have a dimension of type Time,
that contains at least one hierarchy with a level flagged as a time period.

The reason for this issue is,

1. Atleast one dimensions for the cube should have the type as "Time"
2. So that, the cubes data can be evaluated based on the time dimensions.

To create Time dimensions, click "Add Business Intelligence"

For the Time dimensions, you need to right click and change the type in the properties window.

Cheers,

Venkatesan Prabu .J

Cannot connect to Analysis Services version '10.50.1600.1'.

2011-11-21T17:43:00.000-08:00

Here is one of the peculiar issue related to SSAS.

Error Details
---------------------------

Microsoft Visual Studio
---------------------------
Cannot connect to Analysis Services version '10.50.1600.1'.
---------------------------
OK
---------------------------

Error 1 The project could not be deployed to the 'Venkat-PC' server because of the following connectivity problems : Cannot connect to Analysis Services version '10.50.1600.1'. To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server.

------------------------- ---------------------------------------------------------------------------
As usual, I have done the changes specified in the error message. Still, I couldn't figure out the issue.
Eureka, got a strike on this issue.

Can you guess the problem??????????

Solution:

1. May be ur SSAS port is blocking( add port 2383 to the SQL Server Connections in the Windows Firewall.)

2. Ensure your SSAS services and Browser services is running fine in Services.msc console.

3. Right click on the project properties and check the deployment server as your server name instead of "local host"

4. Finally :-) Do not open your project in Microsoft Visual studio console. Instead try to open the BIDS tool to deploy your cubes. Some how, SSAS is not allowing the users to deploy the cubes from this console. (here is the mistake, I have done) Fortunately resolved it.

Cheers,
Venkatesan Prabu .J

Issue with DatetimePicker data

2011-11-19T23:45:00.000-08:00

Error String: The string was not recognized as a valid DateTime. There is a unknown word starting at index 0.

The reason for this issue is, the application is trying to convert the data from DateTimePicker to datetime and it's failing.

The reason is, I;ve tried to pick the data wrongly. dtTimePicker.ToString() -> Instead we have to use dtTimePicker.Text.

Cheers,
Venkatesan Prabu .J

404 Errors after clicking Wordpress links

2011-11-16T23:09:00.000-08:00

404 Errors - Page Not found issue. After clicking Wordpress links

Here is the steps to escape from this issue.

Login to the WordPress Dashboard
Click of Settings on the menu on the left hand side.
Click on Permanlinks under Settings.
Remember which setting it is set on now. (If you are using a custom structure, copy or save the custom structure somewhere.)
Select Default
Click on Save Changes --- Stay here and check your site. It will work fine :-)
Change it back to the setting it was on before you selected Default. (Put the custom structure back if you had one.)
Click Save Changes

If the above steps didn't fix your issue. Then, change the .htaccess file.

# BEGIN WordPress

RewriteEngine On
RewriteBase /
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule . /index.php [L]


# END WordPress

Option2:

rename the .htaccess file. change the permalink settings. This will generate the new .htaccess file and you are able to login perfectly.

Cheers,

Venkatesan Prabu. J

Head, Kaashiv Infotech

Chennai.

Carefull while copying the code from pdf

2011-11-04T07:22:00.000-07:00

I've happened to copy a small test code from PDF and while executing, I got the below error.

System.Windows.Markup.XamlParseException: Invalid character in the given encoding. Line 1, position 13.

The reason is due to Double quotes and it's not a technical bad code :-)

Be careful while copying the code from PDF. Mostly, it won't work due to this kind of simple issues.

Cheers,
Venkatesan Prabu .J
Head - Kaashiv infotech
www.kaashivinfotech.com

Enabling delay process in c#

2011-11-02T06:36:00.000-07:00

To achieve a delay in processing, you have add the name space

using System.Threading;

Add the below code in the place where you want the delay

Thread.Sleep(200);

Cheers,
Venkatesan Prabu .J
Head, Kaashiv infotech,
Chennai
http://www.kaashivinfotech.com/

Auto Scroll option in List box

2011-11-02T06:22:00.000-07:00

Here is a small code snippet to scroll the content in List box. (If the items are more)

In the top, you should add the code

this.listBox1.TopIndex = 0;

After wards, you have to modify the index of the list box slowly.

listBox1.Items.Add(line);
this.listBox1.TopIndex = listBox1.Items.Count - 1;

Check my previous post for the entire piece of code.

Cheers,
Venkatesan Prabu .J
Head, KaaShiv InfoTech,
Chennai.

WWW.kaashivinfotech.com

Read text file line by line in C#

2011-11-02T06:18:00.000-07:00

I happened to work on files. My condition to read the file line by line.

Here is a small piece of code to read the file line by line.... I got the snippet from Microsoft BOL.

private void btnBrowse_Click(object sender, EventArgs e)
        {
            int counter = 0;
            string line;
            rtb_filecontent.Text = "";
            // Read the file and display it line by line.
            System.IO.StreamReader file =
               new System.IO.StreamReader("D:\\1.txt");
            this.listBox1.TopIndex = 0;
            while ((line = file.ReadLine()) != null)
            {
               // rtb_filecontent.Text = rtb_filecontent.Text + "\n" + line;
                listBox1.Items.Add(line);
                //rtb_filecontent.AutoScrollOffset = new Point(rtb_filecontent.AutoScrollOffset.Y, 10);
                Thread.Sleep(200);
                counter++;
            }

            file.Close();
        }

Happy Coding
Cheers,
Venkatesan Prabu .J
Head, KaaShiv InfoTech.
Chennai

www.kaashivinfotech.com

Quoting the input string with the specified character

2011-11-01T16:35:00.000-07:00

While focusing injection related issue, I happen to look into the function QuoteName method in SQL Server. It's used to Enclose or Quote a string with the specified character string.

Consider the below query, am asking SQL Server to enclose the string with [ ] (Bracket)

SELECT QUOTENAME('abc [ ] def','[]')

The output is,

[abc [ ]] def]

This function is really useful to enclose the string with the specified literal and avoid SQL Injection issues while passing the parameter.

Cheers,
Venkatesan Prabu .J
www.kaashivinfotech.com

Listing down Server directories in SQL Server

2011-11-01T06:40:00.000-07:00

Listing down the directories in the server:

Below is the command used to list down the directories in the server.

exec master..xp_cmdshell 'dir'

Thanks and Regards,
Venkatesan Prabu .J
Microsoft MVP
Head - KaaShiv InfoTech
Chennai.

www.kaashivinfotech.com

Error in executing xp_cmdshell command

2011-11-01T06:35:00.000-07:00

xp_cmdshell is one of the important command to access the system resources.

SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

Solution:

As told in my previous post, by default highly secure system related properties will be made disabled.

EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE

Cheers,
Venkatesan Prabu .J
Head - KaaShiv InfoTech,
www.kaashivinfotech.com

Error in executing OpenRowset or OpenDatasource queries

2011-11-01T04:59:00.000-07:00

While executing OpenRowset or OpenDatasource queries, you will get the below error.

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries',

Solution:

By default, SQL Server will disable the options related to the server. We need to enable the same by executing the below queries.

EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE WITH OVERRIDE

Cheers,
Venkatesan Prabu. J
Head - Kaashiv InfoTech, Chennai

Session State Timeout in ASP.Net

2011-09-03T09:50:00.000-07:00

Recently, I thought of including some job related information in my website WWW.KaaShivInfoTech.com

After deploying my code, I faced a typical issue of application pool fill for the objects.

Due to some poor coding, I allowed the user to logged in with a username and password. Unfortunately, I didn't give proper access for signing out.

Guessing, the session of the user took 30 minutes expiration time (Standard session expiration time). Since, most of the users are accessing this site and closing it with proper signing out. The objects started accumulating. At present, I dont have any idea to implement sign out option.

So I thought putting some timeout options in the connection string and web.config file. Thought of sharing the same to you all.

In the connection string, I've added timout option as 5 minutes. If the database is accessed for more than 5 minutes, the connection should be thrown out.

or else,

we can do some changes in web.config file under system.web section

cookieless="true"

regenerateExpiredSessionId="true">

Cheers,

Venkatesan Prabu .J

String connection = "Server=xxx; port=3309; DATABASE=xxx;UID=xxx;PASSWORD=xxxxxx#;timeout=5;";

Installing AdventureWorks database for SQL Server 2008R2 version

2011-08-12T06:57:00.000-07:00

Path for getting the sample Adventure works database is,

http://msftdbprodsamples.codeplex.com/releases/view/55926

Download and execute the exe available in the path.

Analysis Services related databases needs to be manually configured and here is the procedure to achieve the same,

http://msftdbprodsamples.codeplex.com/wikipage?title=Installing%20Analysis%20Services%20Database

For any issues, you can check my previous articles

http://venkattechnicalblog.blogspot.com/2011/08/adventure-works-deployment-issue-in-sql.html

Cheers,

Venkatesan Prabu .J

Deployment issue in SQL Server Analysis Services

2011-08-12T06:50:00.000-07:00

While trying to deploy SQL Server Analysis related database, I got the below error.

-------------------------------------------------------------------------------------------------------------

Microsoft Visual Studio

--------------------------

The solution file 'C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks 2008R2 Analysis Services Project\enterprise\Adventure Works.sln' was not written successfully. Check that the location is available, the file is writeable and there is enough room on the disk.

-------------------------------------------------------------------------------------------------------------

Solution:

Open the application under administrator rights, Deployment or building the application will work fine.

Cheers,

Venkatesan Prabu .J

Adventure Works deployment issue in SQL Server Analysis Services

2011-08-12T06:42:00.000-07:00

Errors while deploying the AdventureWorksDW2008R2 database:

Not sure, why microsoft made this deployement too complex. Anyway, i've started deploying my database into my server.

Got the database from the path,

http://msftdbprodsamples.codeplex.com/releases/view/55926

Error 2 OLE DB error: OLE DB or ODBC error: Login timeout expired; HYT00; A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.; 08001; Named Pipes Provider: Could not open a connection to SQL Server [2]. ; 08001. 0 0

Error 3 Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'Adventure Works DW', Name of 'Adventure Works DW'. 0 0

Solution:

The solution file for deploying your database is located in the below path,

C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks 2008R2 Analysis Services Project

Do the below checks,

1. Right click on the project -> Properties -> Deployment -> Give the ServerName and Instance Name properly.

2. Open the application under administrator. So that, you won't face any deployment issue.

3. Double click on the Adventure Works.ds (DataSource)- > In connection string, click "Edit" -> Give the Servername and instance name properly and "Test" the connection properly.

4. Try checking your Analysis services by logging through SSMS.

That's it, your deployment is successful. All the best.... Keep loving SQL. :-) Server

Happy Learning!!!

Cheers,

Venkatesan Prabu .J

Connecting SQL Server Analaysis Services Engine

2011-08-12T04:34:00.000-07:00

Dear Readers,

Writing my article after a very long time. I am totally engaged with my office works and couldn't spend my time to my lovable SQL Server Technology. Anyway, I bagged my Microsoft award as Most Valuable Professional for the fourth year and I would like to thank Microsoft MVP Selection commitee for awarding me this award.

Ok, here is some interesting topics. Now, am into SQL BI area :-)

Installed all the services in my machine (SQL Server DB Engine, SQL Server Analysis Services)

---------------------------------------------------------------------------------------------------------

ADDITIONAL INFORMATION:

A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. (Microsoft.AnalysisServices.AdomdClient)

------------------------------

No connection could be made because the target machine actively refused it 127.0.0.1:2382 (System)

---------------------------------------------------------------------------------------------------------

Tried to access my SSAS, I couldn't connect. I got the above error while connecting the SSAS engine.

Reason:

1. Check your SQL Browser Services and if it's not started then start the same.

2. The second interesting thing is :-) :-) All the services should run under the same Service account.

Now refresh the Services.msc console. Goto your SSMS, now try to connect it...Yes, you are into the world of SQL Server Analaysis Services.

Keep watching my blog for more SSAS artciles.

Happy Learning!!!

Cheers,

Venkatesan Prabu .J

Error: "Attempted to access an unloaded AppDomain"

2011-07-14T07:29:00.000-07:00

If we get the following error "Error: "Attempted to access an unloaded AppDomain"

The error occurs due to conflict in the Appdomain.

Solution : Conflict in the Appdomain can be resolved by restarted the IIS

Step: Goto run type IISreset and click "OK" button.

Cheers,
Venkatesan Prabu .J

DIFFERENCE BETWEEN DROP AND TRUNCATE

2011-04-30T09:50:00.000-07:00

----- DIFF BETWEEN DROP AND TRUNCATE

1. DROP WILL REMOVE THE OBJECT WHERE ARE DELETE WILL REMOVE ONLY THE DATA DELETE WILL DELETE EACH RECORD

2. NO LOGGING IN TRUNCATE ... LOGGING WILL HAPPEN IN DROP STATEMENT

Cheers,
Venkatesan Prabu .J
Head - www.kaashivinfotech.com
http://venkattechnicalblog.blogspot.com/

DIFF BETWEEN TRUNCATE AND DELETE

2011-04-30T09:47:00.001-07:00

----- DIFF BETWEEN TRUNCATE AND DELETE

1. TRUNCATE WILL DELETE THE ENTIRE TABLE @ A STRETCH DELETE WILL DELETE EACH RECORD
2. NO LOGGING IN TRUNCATE ... LOGGING DELETE
3. PROCESS IS TOO FAST AND WE CAN RECOVER THE DATA IN TRUNCATE

Cheers,
Venkatesan Prabu .J
Head - www.kaashivinfotech.com
http://venkattechnicalblog.blogspot.com/

Concatenate row data into a single data in SQL Server

2011-04-30T09:35:00.000-07:00

How to concatenate the row data into a single data?

[CODE]
DECLARE @NAME VARCHAR(100) =''
SELECT @NAME= @NAME + ',' + NAME FROM VENKAT_NAME_TABLE
SELECT SUBSTRING(@NAME,1,LEN(@NAME))
[/CODE]

Cheers,
Venkatesan Prabu .J
Head - www.kaashivinfotech.com
http://venkattechnicalblog.blogspot.com/

Converting 0 to 1 and 1 to 0 in SQL Server table

2011-04-30T09:15:00.000-07:00

Here is a code snippet to swap the values in SQL Server Table.

DROP TABLE VENKAT_SAMPLETABLE

CREATE TABLE VENKAT_SAMPLETABLE(ID INT)
INSERT INTO VENKAT_SAMPLETABLE VALUES(1)
INSERT INTO VENKAT_SAMPLETABLE VALUES(0)
INSERT INTO VENKAT_SAMPLETABLE VALUES(0)
INSERT INTO VENKAT_SAMPLETABLE VALUES(1)

SELECT * FROM VENKAT_SAMPLETABLE

UPDATE VENKAT_SAMPLETABLE SET ID=
       ( CASE
                   WHEN ID=1 THEN 0
                    WHEN ID=0 THEN 1
            END
        )
SELECT * FROM VENKAT_SAMPLETABLE

Cheers,

Venkatesan Prabu .J
Head - www.kaashivinfotech.com
http://venkattechnicalblog.blogspot.com/

Distributed Database Management System

2011-04-25T22:59:00.000-07:00

Distributed Database management system is nothing but the data will be distributed among different databases and may be located in different servers in different locations. But, they are logically interdependent.

If we think about an advanced concepts like Database Mirroring, yes it will considered as distributed databases. Because, the data will be distributed to different locations.

Cheers,
Venkatesan Prabu .J
Head - http://www.kaashivinfotech.com/
http://venkattechnicalblog.blogspot.com/

Confused Nation - A Social thought

2011-04-25T01:40:00.000-07:00

Confused Nation ???

WORTH A QUICK READ !!!

A nation where Rice is Rs.40/- per kg and a SIM Card is free•

Where a pizza you have ordered reaches home faster than an ambulance or police, even if you were being murdered or having a heart attack•

Where a car loan is charged at 5% but an education loan, so necessary for our youth is charged an interest of 12%•

A nation where students with 45% get into elite institutions through the quota system and those with 90% are sent away because of merit.•

Where a millionaire buys a cricket team, spending crores instead of donating the money to any charity. Where two IPL teams were auctioned at 3300 crores, yet still a poor country where people starve for two square meals per day.•

A country where footwear is sold in AC showrooms, but the vegetables we eat, are sold on the footpath and very often next to garbage dumps.•

Where everybody wants to be famous, not by doing good for others, but by looting others and finally getting their names in the newspapers through some scam or other!•

It’s a strange nation we live in, where complex buildings get ready within a year while public bridges, flyovers and sea links take several years even to get off the drawing board and another decade to be completed•

We are a nation where two brothers fight with each other for the nation’s spoils, but the nation doesn’t know that the two of them are cleverly looting the nation while we watch their mock battles.•

We have malls, and sky-rises, with slums forming their boundary wall.•

A country where men and women squat on railway tracks, with no where else to go while watching them from windows, are couples with three bathrooms and one for the guests•

A country where politicians who are supposed to serve the people accept money from the same people they are supposed to serve, then take a salary from the government for their services to the people•

We are a nation where we talk in hushed whispers about the corruption in the country and then dig into our pockets to bribe a cop when we are caught cutting a red light

Cheers,
Venkatesan Prabu .J
Head - http://www.kaashivinfotech.com/

Converting RGB image or psd to CMYK

2011-04-22T22:56:00.000-07:00

Usually, we will face a problem while printing the images(For instance, visiting cards).

They will advise us to convert the image from RGB(Default one) to CMYK color.

Here is an option in Photoshop to convert your image into required format.

Cheers,
Venkatesan Prabu .J
Head - www.KaaShivinfotech.com
http://venkattechnicalblog.blogspot.com/

List down all the collations available in SQL Server

2011-04-22T12:03:00.000-07:00

Below query is used to find all the collations available in SQL Server

select * from ::fn_helpcollations()

Cheers,
Venkatesan Prabu .J
Head - WWW.Kaashivinfotech.com
http://venkattechnicalblog.blogspot.com/

Puzzle question in SQL Server

2011-04-22T11:43:00.000-07:00

I have tried to execute the query and got a different result,

CREATE TABLE VENKAT_TABLE (ID INT IDENTITY(1,1))

INSERT INTO VENKAT_TABLE DEFAULT VALUES
GO 100

Detailed steps:

   1. Created table
   2. Insert records in the table with default values
   3. Execute the statement for 100 times.

Oops.. One record inserted and for second am getting the error as.... the table is already exists.

Reason,

    The "GO" statement is considering both the statements above it and it's trying to create the table and insert the record into it.

      CREATE TABLE VENKAT_TABLE (ID INT IDENTITY(1,1))

      GO -- Should have seperate GO here.
      INSERT INTO VENKAT_TABLE DEFAULT VALUES
      GO 100

Cheers,
Venkatesan Prabu .J
Head - WWW.Kaashivinfotech.com
http://venkattechnicalblog.blogspot.com/

Generating Alpha numeric values in SQL Server like Identity property

2011-04-22T11:27:00.000-07:00

My application has three atributes id, name, sal and a create button. but the task is the user should not enter id according to his choice..
it should be generated automatically..... when new user is registering...
the no's should be like that a1,a2,a2.... b1,b2,..... no's should be alphanumeric...
s there any solution for this... help me out....

Answer for the above query,

DECLARE @ALPHA_VAL CHAR(1) , @INT_VAL VARCHAR(1000)

DECLARE @VAL INT =97, @VAL1 INT =1
WHILE @VAL <= 122
BEGIN

      SET @VAL1=1

      WHILE @VAL1 < 1000

       BEGIN

                SET @ALPHA_VAL = CHAR(@VAL)
                SET @INT_VAL = CONVERT(CHAR,@VAL1)
               SET @INT_VAL= @ALPHA_VAL + @INT_VAL

                PRINT @INT_VAL
                SET @VAL1 = @VAL1+1

        END
SET @VAL=@VAL+1
END

The output is

a1
a2
a3
a4
....b1,b2,....b1000...c1,c2.......etc... to z1000

Cheers,

Venkatesan Prabu .J
Head - WWW.Kaashivinfotech.com
http://venkattechnicalblog.blogspot.com/

Inline Declaration and value assignation in SQL Server 2008

2011-04-22T11:09:00.000-07:00

In older environment, we need to declare the variable and set the value for the varaible as below,

DECLARE @VAL INT , @VAL1 INT
SET @VAL=10
SET @VAL=20

In the newer version of SQL Serve 2008, we are having inline declaration and value assignation.

DECLARE @VAL INT =97, @VAL1 INT =1
Cheers,
Venkatesan Prabu .J
Head - WWW.Kaashivinfotech.com
http://venkattechnicalblog.blogspot.com/

CHAR function in SQL Server

2011-04-22T10:52:00.000-07:00

Char is a data type which everyone knows. Do you know char is a function which is a reverse of ASCII function in SQL Server?

Select Char(65)

will return the output as "A"

Cheers,

Venkatesan Prabu .J
Head - WWW.Kaashivinfotech.com
http://venkattechnicalblog.blogspot.com/

SQL Server User Group meet at Chennai

2011-04-22T10:45:00.000-07:00

Dear Blog readers,

We are planning for SQL Server User group monthly meeting @ KaashivInfoTech chennai (My Office).

http://sql-articles.com/cssug/april-2011-ug-scheduled

I am presenting a sesssion on DBA track (Management DataWarehouse).

It's free for everyone. Please register it ASAP.

Cheers,

Venkatesan Prabu .J
Head - WWW.Kaashivinfotech.com
http://venkattechnicalblog.blogspot.com/

Varchar to Float in SQL Server

2011-04-22T10:41:00.000-07:00

Convert function can be used to convert your varchar value to Float.

declare @abc varchar(30)
set @abc = '10.5'
select convert(float,@abc)
Cast function will perform the same operation.
declare @abc varchar(30)
set @abc = '10.5'
select cast(@abc as float)
Cheers,

Venkatesan Prabu .J
Head - WWW.Kaashivinfotech.com
http://venkattechnicalblog.blogspot.com/

Constraints on the whole table in SQL Server

2011-04-22T10:32:00.000-07:00

One of my blog reader have asked me a query, Is it possible to give constraints to all the columns in a table in a single shot?

CREATE TABLE dbo.Venkat_Table

(
   id int primary key,
   Name varchar(50) unique,
   Age int Unique
)

Yes it's possible. We can provide the constraints.

Applying terms and conditions -> The constraints should not conflict with one another. Primary key constraints can be given only once for a table.
Cheers,

Venkatesan Prabu .J
Head - WWW.Kaashivinfotech.com
http://venkattechnicalblog.blogspot.com/

combining column names in sql server

2011-04-22T10:13:00.000-07:00

Consider am having column's as FirstName and another column as SecondName. If your result needs the output Name which is the combination of FirstName and SecondName or LastName.

Select FirstName +' '+ LastName as Name from YourTable

Cheers,

Venkatesan Prabu .J
Head - WWW.Kaashivinfotech.com
http://venkattechnicalblog.blogspot.com

Replace function in SQL Server

2011-04-21T08:23:00.000-07:00

Replace is one of the nice function in SQL Server. It's used to replace a particular string in the string.

The Syntax is,

Replace("Actual String","String to be replaced","String to replace")

-------- REPLACES THE STRING WITH THE PROVIDED STRING

SELECT REPLACE('VENKAT STRING','VENKAT','SANTHOSH')

The output is SANTHOSH STRING.

Cheers,

Venkatesan Prabu .J
Head - WWW.Kaashivinfotech.com
http://venkattechnicalblog.blogspot.com/

Changing the path of the MDF and LDF file for the database

2011-04-21T06:38:00.000-07:00

One of my friend have asked a query to me. How can I change my database files from one drive to another drive. Is it possible to achieve it?

Right Click on the database -> Properties -> Files - >Path

Here you can change the path your MDF and LDF files.

Cheers,
Venkatesan Prabu .J
Head - KaaShivInfotech
http://www.kaashivinfotech.com/

Some inbuilt functions in SQL Server

2011-04-21T06:36:00.000-07:00

Let's see some of the Sytem inbuilt functions in SQL Server

------- RETURNS ASCII VALUE FOR THE CHARACTER

SELECT ASCII('A')

------- RETURNS ASCII VALUE FOR THE FIRST CHARACTER OF THE STRING

SELECT ASCII('ASK')

------- CONVERT YOUR CAPITAL LETTERS TO SMALL LETTERS

SELECT LOWER('ASK')

------- CONVERT YOUR LOWER LETTERS TO UPPER LETTERS

SELECT UPPER('ask')

------- GIVES YOU THE LENGTH OF THE STRING

SELECT LEN('ask')

Cheers,
Venkatesan Prabu .J
Head - KaaShivInfotech
www.KaaShivinfotech.com

SQL Cache dependency

2011-04-21T06:29:00.000-07:00

SQL Cache dependency is a fantastic concept introduced with SQL Server 2005. It's nothing but maintaining the table data in the cache until the table is getting altered.

Conserdering am having a table named Venkat_table in the database Venkat_database.

The first step is to execute the below command in the command prompt,

aspnet_regsql.exe -Saa -Uaa -Paa -ed -d Venkat_database -et -t Venkat_table

(Search your machine for aspnet_regsql.exe and go to that path for executing the above command)

Once if you execute the above command AspNet_SqlCacheTablesForChangeNotification table will be created in the database and the above Venkat_table will be added in the Cache list.

This will increase the performance of data retrieval for this table.

Cheers,
Venkatesan Prabu .J
Head - KaaShivInfoTech

Identity insert in SQL Server table

2011-04-18T05:21:00.000-07:00

"An explicit value for the identity column in table 'VENKAT_TABLE' can only be specified when a column list is used and IDENTITY_INSERT is ON."

Usually, we will get this error while inserting the records/data into the identity column.

The reason for this error is,

"We can't explicitly insert data into the identity. If we want to achieve the same, we should first execute the command Identify insert on the table is ON"

Here is a small code snippet,

CREATE TABLE VENKAT_TABLE(ID INT IDENTITY(1,1),NAME VARCHAR(100),AGE INT)

GO

SP_HELP VENKAT_TABLE

GO

SET IDENTITY_INSERT VENKAT_TABLE ON

-- WE CAN INSERT THE IDENTITY VALUES EXPLICITLY

INSERT INTO VENKAT_TABLE(ID,NAME,AGE) VALUES(1,'VENKAT',20)

INSERT INTO VENKAT_TABLE VALUES(2,'SANTHOSH',20)

INSERT INTO VENKAT_TABLE VALUES(3,'DEEPAN',20)

OOPs, still am getting the same error. The reason is Microsoft SQL Server is insisting us to insert the records with the column specified in the query. Let's sse the actual solution to resolve the same.

INSERT

Cheers,
Venkatesan Prabu. J
Head - WWW.Kaashivinfotech.com INTO VENKAT_TABLE(ID,NAME,AGE) VALUES(1,'VENKAT',20)

ASCII function in SQL Server

2011-04-06T04:00:00.001-07:00

ASCII function is used to return the ascii value of the character which is provided as input.

------- RETURNS ASCII VALUE FOR THE CHARACTER
SELECT ASCII('A')

The output is 65.

Cheers,
Venkatesan Prabu
http://kaashivinfotech.com/

Fetch random records from the table using NewID() function

2011-04-06T03:35:00.000-07:00

Query: Am having 10000 records and I need some 2000 random records. How can I fetch the same? Answer:

This can be acheived using NEWID() function. NewID() will try to generate a unique id of 32 bit length. (Ex value A1E7AB10-3E0C-4685-9FEE-00239FB80F4F)

---------Am creating a sample table and insert the records into the table DROP TABLE VENKAT_TABLE GO CREATE TABLE VENKAT_TABLE (ID INT IDENTITY(0,1)) GO INSERT INTO VENKAT_TABLE DEFAULT VALUES GO 1000 ------- Inserting 1000 records into the table SELECT * FROM VENKAT_TABLE

--------- Query to fetch random 100 records from the table.

SELECT TOP 100 * FROM VENKAT_TABLE ORDER BY NEWID()

Cheers,

Venkatesan Prabu .J

http://kaashivinfotech.com/

Change Blog name in WordPress.com

2011-04-04T00:51:00.000-07:00

Recetly, I have created another blog in wordpress.com. Thought of changing the blog name. Struggled to find the option in the bunch of options available. Goto Setting - > General, you will get the text box to get the new name for your blog. Reconfirm the name in the second text box. Thats why, you are ready to go. Cheers, Venkatesan Prabu .J http://kaashivinfotech.com/ http://venkattechnicalblog.blogspot.com/

Creating script for SQL Server 2000 from SQL Server 2005

2011-04-01T03:24:00.000-07:00

I got a peculiar question like, "Can we create a script for downgraded version in the current higher version environment. For instance, am having SQL Server 2008 database and I would like to create the database script for SQL Server 2005. Is it possible? "

Yes, it's possible with an option. It's hidden safely somewhere and we need to dig the same to resolve the problem.

1) Right-click over the desired Database at 2005, Choose Tasks and the Generate Scripts (Option).

2) At the pop-up Dialog Box click at the Script All Objects in the selected Databases check box, to activate it and then Click the Next Button.

3) Set the following Elements to the following Values

a. Script Collation , set to TRUE

b. Script Database Create, set to TRUE

c. Script of SQL Version, set to SQL SERVER 2000

d. Script foreign keys, set to TRUE

e. Script Triggers, set to TRUE Then Hit the Next button

4) Select the way the generated scripts should be saved (There are different selections. The most common one is Clipboard). Finally click the Next button till you reach the end.

5) Click Finish

Cheers, Venkatesan Prabu .J http://www.kaashivinfotech.com/ http://venkattechnicalblog.blogspot.com/

Prevent read and write permissions to app.config file

2011-04-01T03:18:00.000-07:00

I started thinking about an option of restricting the read/write permission on my app.config file. Oops, as a typical programmer. I am things "inside the box" in the dotnet environment. Let me clear out the exact problem. Can we restrict the programmer to modify the config file? Because, all my connections are sitting in my config file and I would like to keep it safe. Afterwards came out from Dotnet world and started thinking as a windows user. It's really too simple, you can achieve the same through your windows Security property.Right click on the file -> Properties -> Security -> Click on the specific user and click the edit button, you can change the options by selecting or deselecting the check boxes. Cheers, Venkatesan Prabu .J http://www.kaashivinfotech.com/ http://venkattechnicalblog.blogspot.com/

Installer issue in Dotnet application

2011-04-01T02:57:00.000-07:00

I have an installer that runs seamlessly in any 32bit -x86 OS. But if I tried to run the exe(installer) to any 64bit OS, yeah it runs and creates a database, but does not create a tables on it... The installer is compiled in a platform 32bit OS. Do you have any idea about this? Do I need to recompile the application in a 64bit OS? I got the below issue: "SQL Server 2005 Setup cannot continue because the installation package could not be opened. The installation package has a missing file, or you are running a 32-bit only Setup program on a 64-bit computer. To continue, verify the SQL Server 2005 package you are running is supported on WOW64. If you are certain that you are running a supported SQL Server 2005 package, and you continue to see this message, contact the application vendor." Answer: Obviously, you need to provide two types of installers 1. One for 64 bit - x64 platform 2. One for 32 bit - x86 platform. While creating the installer, you need to change the targerplatform property. Cheers, Venkatesan Prabu .J http://www.kaashivinfotech.com/ http://venkattechnicalblog.blogspot.com/

Case in Select statement in SQL Server

2011-03-31T08:31:00.001-07:00

DROP TABLE VENKAT_TABLE GO CREATE TABLE VENKAT_TABLE(ID INT, NAME VARCHAR(100),MARKS INT) INSERT INTO VENKAT_TABLE VALUES (1,'VENKAT',20) INSERT INTO VENKAT_TABLE VALUES (2,'SUBA',80) INSERT INTO VENKAT_TABLE VALUES (3,'LAKSHMI',45) INSERT INTO VENKAT_TABLE VALUES (4,'SANTHI',70) SELECT ID, NAME, CASE WHEN MARKS >50 THEN 'GOOD' ELSE 'BAD' END AS RESULT FROM VENKAT_TABLE

Order of Execution of Select Statement

2011-03-31T08:31:00.000-07:00

This article is not clearly displayed in this blog, I have posted the same article in my other blog too. http://www.c-sharpcorner.com/Blogs/4536/order-of-execution-of-select-statement.aspx One of my blog reader have asked a query on SQL Server query execution order. Thought of writing an article on the same. select * from Student where id< 5000 group by studentID having subjectid=10 order by student name Here is the order of execution for your select statement. FROM --> Which Primary table ON --> On which column to join JOIN -->With which table, you want to join WHERE --> what are the conditions to filter the record GROUP BY --> on what basis, you want to group. WITH CUBE or WITH ROLLUP --> Show the data in the form of knowledge cubes. HAVING --> Another filter criteria S ELECT --> Get the data DISTINCT --> Remove duplicates ORDER BY --> Display in this order TOP --> Display only this much. That's really awesome processing of your query. My only concern is, why we are grouping the data afterwards we are having another filter criteria like having. May be having can be placed front before grouping it. Anyway, I will put this question to Microsoft. For our example, select * from Student where id < 5000 group by studentID having subjectid=10 order by student name The order of execution is, from -->where --> group by -->having --> select -->; Order by Cheers, Venkatesan prabu .J http://venkattechnicalblog.blogspot.com/ http://www.kaashivinfotech.com/

Horizontal ruler in HTML

2011-03-31T08:14:00.000-07:00

I happened to use HTML for my company website (http://www.kaashivinfotech.com/). During my design for segregating the content, I've used table row and table data with lines in the boundary like, OOps, it's a costliest work to do and it's not efficient way to give a break line between the contents.

On my random search, I found the horizontal ruler in my VS tool box. Awesome, Here is the one line code to achieve the same,

To provide colour to the ruler

To provide the size to the ruler. Here is an option

width - Indicates the length of the ruler

size - Indicates the thickness of the ruler.

Cheers,

Venkatesan Prabu .J

Combining two views in SQL Server

2011-03-23T00:11:00.000-07:00

Let's talk about views in SQL Server. Views are nothing but virtual tables which covers the table in SQL Server.

Am creating two tables,

DROP TABLE VENKAT_TABLE
GO
CREATE TABLE VENKAT_TABLE(ID INT, NAME VARCHAR(100))
GO
INSERT INTO VENKAT_TABLE VALUES(1,'SUBA')
GO
INSERT INTO VENKAT_TABLE VALUES(1,'ARUN')
GO
DROP TABLE VENKAT_SECOND_TABLE
GO
CREATE TABLE VENKAT_SECOND_TABLE(ID INT, AGE INT)
GO
INSERT INTO VENKAT_SECOND_TABLE VALUES(1,20)
GO
INSERT INTO VENKAT_SECOND_TABLE VALUES(1,30)
GO

-- Now am creating the views for the tables.

-- Views are nothing but virtual table which is like a mask on the table. For security reasons, instead of allowing the user to access the tables directly we will use views.

CREATE VIEW VENKAT_VIEW
AS
SELECT * FROM VENKAT_TABLE
GO
CREATE VIEW VENKAT1_VIEW
AS
SELECT * FROM VENKAT_TABLE
GO
CREATE VIEW VENKAT_SECOND_VIEW
AS
SELECT * FROM VENKAT_SECOND_TABLE
GO

Joining two views

------------------------------------------------------------------------------------
SELECT V1. * FROM VENKAT_VIEW V1 INNER JOIN VENKAT_SECOND_VIEW V2
ON V1.ID=V2.ID

Merging the results sets of the view.

(For merging the result set, we can use union all or union operator)

------------------------------------------------------------------------------------
SELECT * FROM VENKAT1_VIEW UNION ALL
SELECT * FROM VENKAT_VIEW

Cheers,
Venkatesan Prabu .J
Head - http://www.kaashivinfotech.com/
http://venkattechnicalblog.blogspot.com/

Enable Backup compression in SQL Server

2011-03-22T23:05:00.000-07:00

Backup compression is a very important feature introduced in SQL Server 2008. Using this technique, the daily backups from the server were compressed and lot of spaces were saved.

"Usually in production servers, DBA's /jobs will take the backup frequently. In least case, 1 backup per day. The file will be ended with *.bak". The size of the backup depends on the size of the database. To avoid more space for the backup's, Microsoft have introduced this backup compression technique." We will see more details on the new compression techniques in my future article.

Let's see how to enable the backup compression option in SQL Server.

USE master
GO
EXEC sp_configure 'backup compression default'
GO
EXEC sp_configure 'backup compression default', '1'
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'backup compression default'
GO

Cheers,Venkatesan Prabu .J

Head - www.KaaShivinfotech.com

http://venkattechnicalblog.blogspot.com/

Get the description of the Collation settings

2011-03-22T22:53:00.000-07:00

In my previous post, we have seen how to identify the collation settings in the server. The output which we got is not much clear to the normal users. That's why, Microsoft have introduced a built in function to identify the collation which can be understood by normal users.

The function name is sys.fn_helpcollations()

In my previous post, I got my collation setting for my server as, 'Latin1_General_CI_AI'. Below is the query to get the detailed description of the collation.

SELECT description FROM sys.fn_helpcollations() WHERE name = 'Latin1_General_CI_AI'

The output is,

Latin1-General, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive

Kanatype - It's Japanese support data
Width - Width of the data is not considered.

Try your query like below to get the collation settings and their detailed description.

SELECT description FROM sys.fn_helpcollations() WHERE name = 'Latin1_General_CI_AI'

Cheers,
Venkatesan Prabu .J
Head - www.KaaShivinfotech.com
http://venkattechnicalblog.blogspot.com/

Identify the Collation settings of the server

2011-03-22T22:50:00.000-07:00

Here is the handy query to identify the Collation settings on the Server.

SELECT SERVERPROPERTY('Collation')

The output is,

Latin1_General_CI_AI
It indicates my server will support Latin general language, with Case insensitivenss and Accent insensitiveness.

Cheers,
Venkatesan Prabu .J
Head - www.KaaShivinfotech.com
http://venkattechnicalblog.blogspot.com/

How to find Network Adapters available in your machine??

2011-03-22T22:47:00.000-07:00

Option 1:
In Control panel, you can see the available network adapters.

Option 2:
Goto Control Panel -> Device Manager -> Network Adapter

Cheers,

Venkatesan Prabu .J

Managing Director - www.Kaashivinfotech.com

http://venkattechnicalblog.blogspot.com/

Accessing Text Box content

2011-03-03T07:27:00.001-08:00

I have seen the below query in a popular forum. Thought of writing something on this. This is the below query,

Conversion failed when converting the varchar value 'System.Web.UI.WebControls.TextBox' to data type int.

I cant understand why it will occur,i insert the data regarding to the correct datatype only

The guy is trying to access the text box value and he want to convert the value to integer.

Guessing, he is trying to get the value 'System.Web.UI.WebControls.TextBox'. Error indicates, the guy is trying to access the textbox property instead of text box data. In turn you need to access the textbox content like textbox.text and after wards convert the same to integer.

Cheers,
Venkatesan Prabu .J
Head - www.KaaShivinfotech.com
http://venkattechnicalblog.blogspot.com/

Naming the Constraint in sql server

2011-03-03T06:30:00.000-08:00

Dynamically naming the constraint:

Constraint is nothing but a condition placed on the column or object. Let's see a small example to create a Primary Key constraint.

CREATE TABLE SANTHOSH_TABLE (NAME VARCHAR(100),ID INT PRIMARY KEY,DOB DATETIME)

---- INSERT RECORDS INTO THE TABLE ----------
INSERT INTO SANTHOSH_TABLE VALUES('VENKAT',1,'1/1/2010')
INSERT INTO SANTHOSH_TABLE VALUES('LINGAM',2,'1/1/2009')
INSERT INTO SANTHOSH_TABLE VALUES('ILAM',3,'1/2/2010')
INSERT INTO SANTHOSH_TABLE VALUES('SANTHOSH',4,'1/3/2010')
INSERT INTO SANTHOSH_TABLE VALUES('SIVARAM',5,'1/4/2010')

SELECT * FROM SANTHOSH_TABLE

The Primary key constraint name is
PK__SANTHOSH__3214EC2762AFA012 which looks tougher to remember the name. This name is automatically generated by SQL Server. Can we have a specific name to the Constriant?

Below query will drop the constraint located in to your table.

--------- DROP CONSTRAINT ON THE TABLE
ALTER TABLE SANTHOSH_TABLE DROP CONSTRAINT PK__SANTHOSH__3214EC2762AFA012

Now, lets see how to name a constraint.

---- DROPPING THE TABLE------------
DROP TABLE SANTHOSH_TABLE

---- NAMING THE CONSTRAINTS EXPLICITLY------------
CREATE TABLE SANTHOSH_TABLE (NAME VARCHAR(100),ID INT ,DOB DATETIME,
CONSTRAINT PK_ID_SANTHOSH PRIMARY KEY (ID))

Now, you constraint name is PK_ID_SANTHOSH.

Happy Learning!!!

Cheers,

Venkatesan Prabu .J

Performance tuning Query

2011-03-02T07:58:00.000-08:00

This is Dastagiri from hyderabad .I have two years experience in Database’s SQL Server 2005, MySql, ADS servers.But I dint not understand how to apply performance tuning for large time taking queries.Could you please send me any material or video links to me.

Thanks,
Dastagiri.

Sir,

Advising you to get some good books on performance tuning.

http://www.sql-server-performance.com/tips/performance_main.aspx

http://www.mssqltips.com/category.asp?catid=9

http://msdn.microsoft.com/en-us/library/ff647793.aspx

Here are some of the useful links to start your performance tuning with SQL Server

Cheers,
Venkatesan Prabu .J
www.Kaashivinfotech.com

Query on DateTime operations

2011-03-02T07:54:00.000-08:00

Anyway, here is the tips to resolve the problem.

Not working:

select * from Production_Table WHERE [Rep_Code] IN ('117','118','146','147','148','701','702','704','705','706','707','709','710','711','712','713','202') and Rep_Type LIKE '6%' and CONVERT(VARCHAR(8),CONVERT(DATETIME,(Load_Date)),112) between '20110120' and '20110126'

Error message:
Server: Msg 242, Level 16, State 3, Line 1 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Working:

SELECT CONVERT(VARCHAR(8),CONVERT(DATETIME,(Load_Date)),112) FROM from Production_Table WHERE [Rep_Code] IN ('117','118','146','147','148','701','702','704','705','706','707','709','710','711','712','713','202') and Rep_Type LIKE '6%'

So, the loop hole is with the between operator.

Step 1: Most probably, the issue is with the data. It’s a data issue.
Step 2: Check your objects whether any changes happened on the column.
Step 3: Check for the data, any manual entry to add special symbols (Single quotes) may happen.
Step 4: Use a loop operations to identify the exact error data or use top operator to identify it manually.

All the best and sorry for my delay response.

Cheers,
Venkatesan Prabu .J
Managing Director – KaaShiv Info Tech

MVP / MVM / MCITP / MCTS / MCAD / CCNA / QAI
Microsoft SQL Server MVP / Mind Cracker MVP

Website : http://www.kaashivinfotech.com/
Blog : http://venkattechnicalblog.blogspot.com/
Profile : http://beyondrelational.com/members/jvprabhusanthi/default.aspx

Check my Microsoft Profile for more details about me : https://mvp.support.microsoft.com/default.aspx/profile/venkatesan
-------------------------------------------------------------------------------------------
Hi Venkat,

Did you get a chance to look at this email?

Regards
Alex Madaswamy
On Fri, Jan 28, 2011 at 2:31 PM, > wrote:
Hi Venkat,

I was looking through some forums to find a solution my issue and got your blog information and your email id. Can you please help me to resolve this issue?

We have a table with more than 1M records and we are retrieving records from this table in my application. This was working fine till two days back, but now its throuwing an error. See below the query and the error message we are getting.

select * from Production_Table WHERE [Rep_Code] IN ('117','118','146','147','148','701','702','704','705','706','707','709','710','711','712','713','202') and Rep_Type LIKE '6%' and CONVERT(VARCHAR(8),CONVERT(DATETIME,(Load_Date)),112) between '20110120' and '20110126'

Please note:
All the fields in WHERE condition are of the data type VARCHAR
Values in the field Load_Date is in the format yymmdd

Error message:
Server: Msg 242, Level 16, State 3, Line 1The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

I tried this as well:

Removed the condition for the Load_Date from the WHERE condition and ran the query with just Rep_Code and Rep_Type, query returns the value without any issue.
Removed the condition for the Load_Date from the WHERE condition and included in the SELECT part and ran the query with just Rep_Code and Rep_Type, query runs well without any issue. Below is the modified query.
SELECT CONVERT(VARCHAR(8),CONVERT(DATETIME,(Load_Date)),112) FROM from Production_Table WHERE [Rep_Code] IN ('117','118','146','147','148','701','702','704','705','706','707','709','710','711','712','713','202') and Rep_Type LIKE '6%'
Can you please help me to find a solution for this issue?

Thanks in advance,
Alex Madaswamy

Telnet client in Windows Vista

2011-02-15T03:20:00.000-08:00

By default, you can't see your telnet window. I happened to run my telnet in my command prompt and found that, Telnet client is not installed. Below are the steps to get the "Telnet" client.

1. Open the Programs and Features Control Panel applet (Start, Control Panel, Programs and Features).
Select "Turn Windows features on or off."
Select the Telnet Client option and click OK.

You will get a window informing "Telnet" is getting installed in your machine.

Regards,

Venkatesan Prabu .J

My new Training based software company @ Chennai

2011-02-14T08:26:00.000-08:00

After a very long career with HCL and finishing my Australian trip, I resigned my job and started my new company "KaaShiv Info Tech" at Anna Nagar. It's a training based placement company.

Typical Pooja:

Gift from my relatives:

With my brothers and Sister:

Ribbon cutting by my mother: Am taking the photograph

My mother, switching on the official DELL laptop (i5 with 8GB RAM)

KaaShiv
Info Tech
Get Training from Microsoft Awarded And Oracle Certified Experts
100% Placement Guaranteed

Technology Training

Dot Net( C#, ASP.Net,
VB.Net)
Oracle
Linux
SQL Server
Java
Networking
Testing
Hardware

Project Guidance (IEEE projects)

Information Security
Image Processing
Mobile Computing
Data Mining and
Data Warehousing
Networking Placement Training

Placement Training

InterPersonal
Communication
Public Speaking
Aptitude
Technical Skills
Interview Skills
Motivation Classes
HR Development
Similar Competencies

In – Plant Training

Working on Live Projects
Technology Training
Inter Personal
Skills Development
Training based on
SDLC Technique

Address
KaaShiv Info Tech,
Shinandha Building, X41, 5th Floor,
2nd Avenue (Near Ayyappan Temple),
Anna Nagar, Chennai – 40
Ph No : 044 - 42057542
Mob : 9003718877, 9840678906,
9566060601,996-2345637
Email : Kaashiv.info@gmail.com
Website : www.KaaShivInfotech.com

Course Fees – Rs. 6000
Offer Price 25% Discount - Rs. 4500
Grab an Opportunity to work in
Live Projects

MDF data to MSSQL Server - Data Migration

2011-01-02T03:24:00.000-08:00

Wishing you Happy New Year

2011-01-02T03:03:00.001-08:00

Dear Blog Readers,

My heartiest wishes for this new year. May be your wishes comes true in this year 2011.

Cheers,

Venkatesan Prabu .J

Data Compression in SQL Server 2008

2010-12-06T20:02:00.001-08:00

Data compression is a new feature introduced in SQL Server 2008. It enables the DBA’s to effectively manage the MDF files and Backup files. There are two types of compressions,

1. Row Level Compression: This type of compression will work on the row level of the data page.

a. Operations like changing the fixed length datatype to Variable length data type. For instance, Char(10) is a fixed length datatype and If we store “Venkat” as data. The space occupied by this name is 6 and remaining 4 spaces will be wasted in the legacy system. Whereas, In SQL Server 2008, it is utilised effectively. Only 6 spaces will be given to this variable.
b. Removal of Null value and zeros. These values will not be stored in the disk. Instead, they will have a reference in the CI Structure.
c. Reduce the amount of metadata used to store the row.

2. Page Level Compression: This compression will be effective in the page level.

a. This compression follows Row Level Compression. On top of that, Page level compression will work.
b. Prefix Compression - This compression will work on the column level. Repeated data will be removed and a reference will be stored in the Compression information (CI) structure which is located next to the page header.
c. Dictionary Compression – This compression will be implemented as a whole on the page. It will remove all the repeated data and a reference will be placed on the page.

How it works:

Considering, If you a user is requesting for a data. In that case, Relational Engine will take care of getting the request compile, parse and it will request the data from the Storage engine.

Now, our data is in the compressed format. Storage engine will send the compressed data to the Buffer cache which in turn will take care of sending the data to relational engine in uncompressed format. Relational engine will do the modifications on the uncompressed data and it will send the same to buffer cache. Buffer cache will take care of compressing the data and have it for future use. In turn, it will send a copy to the Storage Engine.

Advantages:

1. More data will be stored in the Buffer cache. So, no need to go and search in the disk which inturn reduce the I/O.
2. Disk space is highly reduced.

Disadvantages:

1. More CPU cycles will be used to decompress the data.
2. It will be a negative impact, if the data doesn’t have more null values, zeros and compact/exact data (Equivalent to the declared data type).

Cheers,
Venkatesan Prabu .J

Indexing Table variables in SQL Server

2010-12-06T18:49:00.001-08:00

Table variables is a very nice handy tool to store the result set. The major advantage of table variable is that, it will log a very minimal information in the temp database. Initially, I had a thoughts that table variable will reside only in the memory and it won’t interact with any of the database. But, it’s not the case. The data will reside in the temp database but logged very minimally. Index created on the table variables will reside in the tempdb.

Let’s see, how to create indexes on the table variable.

-- Temp table created
DECLARE @VENKAT_VAR TABLE (ID INT)

-- Trying to create an index on the column. It's throwing error.
CREATE CLUSTERED INDEX IDX_VENKAT_VAR ON @VENKAT_VAR(ID)

-- Trying to alter the table variable. It's throwing error.
ALTER TABLE @VENKAT_VAR ADD CONSTRAINT CON_VENKAT_VAR PRIMARY KEY (ID)

-- Here is an option to create the clustered index or primary key on the temp variable.Index on the table variable can be created during table variable creation itself.

DECLARE @VENKAT_VAR TABLE
(ID INT PRIMARY KEY CLUSTERED )

-- We can query on the sys.indexes table to get the index details created on the table variable.

SELECT * FROM TEMPDB.SYS.indexes A INNER JOIN TEMPDB.SYS.tables B
ON A.object_id=B.object_id ORDER BY create_date DESC

Cheers,
Venkatesan Prabu .J

Composite primary key in SQL Server

2010-10-23T09:23:00.000-07:00

Primary Key:

A column which is used to identify the records uniquely is referred to as Primary Key.

1. Primary key won't allow Null values.
2. If a Primary key is created, It will automatically create the clustered index on the table. In turn, the data will be sorted based on this column.
3. Only one Primary key can be created for a table.

Composite Primary Key:

1. Multiple columns can participate in the Primary Key which is referred to as composite primary key.

Am creating the table with Composite Primary key on the column ID and Name

CREATE TABLE VENKAT_SAMPLE_TABLE ( ID INT, NAME VARCHAR(100),BIRTHDATE DATETIME
,PRIMARY KEY(ID,NAME))

To get the information about your Primary key on the columns.You can use the below system view.

SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

In one of the popular forum, I have seen a query. How to achieve it your designer window. Right click on the table and you will get the columns. Press control or Shift key and select the columns -> Right click and you can set the primary key.

Cheers,
Venkatesan Prabu .J

Specifying the schema Name in the Table designer

2010-10-17T04:21:00.000-07:00

I have seen a query in a popular forum and I would like to answer the query,

When I create a table, it creates under dbo.
I would like to create it under a different schema, but when I use the 'New Table' dialog, I can never find the field where to specify this.

The answer is,
You need to specify the schema name in the Properties window of the table.

Cheers,
Venkatesan Prabu .J

Grant vs With Grant option in SQL Server

2010-10-17T03:18:00.000-07:00

What's the difference between grant and with grant option in SQL Server?

Grant will allow the current user to access the object or provide access to the specified user.

CREATE TABLE venkat_Table (ID INT)
GRANT SELECT ON venkat_Table TOVenkatFriend

With grant option is nothing but a higher level of access provider. This will inform the database that, this user will have access to the particular object and he is having necessary previlege to provide access to other users on the object.

GRANT SELECT ON venkat_Table TO VenkatFriend WITH GRANT OPTION

You can see this option in the schema properties window.

Cheers,

Venkatesan Prabu .J

Sparse Columns in SQL Server 2008

2010-10-09T20:12:00.000-07:00

Memory handling on NULL values using sparse columns:

It's a new feature in SQL Server 2008. It enables us to store the null values effectively. Ideally, if the column is NULL. Then, the data won't be stored in the database and it's cost effective solution to save the space. If your table has more Null values, we can opt this solution.

Sparse column is associated with two specialised sub-features:

1. Column Sets - This will provide you the consolidated report of all the sparse columns. We will see this one in our example below.

2. Filtered index - Index will be created on the not null data in the sparse columns.

Limitations and conditions to use Sparse column:

1. We can create index on the sparse column.
2. Sparse column cannot be added on certain column types like text, filestream datatype, geography, image, ntext etc..,
3. Sparse column could not be bounded with default value or rule cannot be applied on it.

Let's see an example,

drop table Venkat_Sparse_ColumnCheck
go
-- created a table to check the sparse column
CREATE TABLE dbo.Venkat_Sparse_ColumnCheck
(
ID INT IDENTITY(1,1) NOT NULL,
FIRST_NAME NVARCHAR(50) NOT NULL,
LAST_NAME NVARCHAR(50) NOT NULL,
ADDRESS1 NVARCHAR(20) SPARSE NULL, -- Sparse columns
ADDRESS2 NVARCHAR(20) SPARSE NULL, -- Sparse columns
CITY NVARCHAR(20) SPARSE NULL, -- Sparse columns
STATE NVARCHAR(2) SPARSE NULL, -- Sparse columns
COUNTRY NVARCHAR(10) SPARSE NULL, -- Sparse columns
ZIP_CODE NVARCHAR(20) SPARSE NULL, -- Sparse columns
CONSTRAINT PK_Venkat_Sparse_ColumnCheck PRIMARY KEY (ID)
)

GO
set nocount on
go

insert into dbo.Venkat_Sparse_ColumnCheck (first_name, last_name, ADDRESS1, ADDRESS2, city, STATE, country, zip_code)
values ('Venkatesan', 'Prabu', '1, first raod', null, 'Dharmapuri', 'TN', 'India', NULL);
insert into dbo.Venkat_Sparse_ColumnCheck (first_name, last_name, ADDRESS1, ADDRESS2, city, STATE, country, zip_code)
values ('Subs', 'subs', '2 Second road', null, 'trichy', 'tr', 'india', '636701');
insert into dbo.Venkat_Sparse_ColumnCheck (first_name, last_name, ADDRESS1, ADDRESS2, city, STATE, country, zip_code)
values ('Janu', 'C', '3 Third road', null, 'trichy', 'tr', 'india', '636701');

-- Here, we are getting a very ordinary output.
Select * from dbo.Venkat_Sparse_ColumnCheck

To see the exact data storage, we need to use the column sets functionality.
Column Set should be defined with datatype as XML and it should be given the condition as ALL_SPARSE_COLUMNS

drop table Venkat_Sparse_ColumnCheck
go

-- created a table to check the sparse column
CREATE TABLE dbo.Venkat_Sparse_ColumnCheck
(
ID INT IDENTITY(1,1) NOT NULL,
FIRST_NAME NVARCHAR(50) NOT NULL,
LAST_NAME NVARCHAR(50) NOT NULL,
ADDRESS1 NVARCHAR(20) SPARSE NULL, -- Sparse columns
ADDRESS2 NVARCHAR(20) SPARSE NULL, -- Sparse columns
CITY NVARCHAR(20) SPARSE NULL, -- Sparse columns
STATE NVARCHAR(2) SPARSE NULL, -- Sparse columns
COUNTRY NVARCHAR(10) SPARSE NULL, -- Sparse columns
ZIP_CODE NVARCHAR(20) SPARSE NULL, -- Sparse columns
ADDRESS_SET XML COLUMN_SET FOR ALL_SPARSE_COLUMNS, -- Column Set to get all the details of the sparse columns
CONSTRAINT PK_Venkat_Sparse_ColumnCheck PRIMARY KEY (ID)
)
GO

set nocount on
go

-- Here, we are getting a very ordinary output.
Select * from dbo.Venkat_Sparse_ColumnCheck

Output for the first record,

1, first raod
Dharmapuri
TN
India

Output for the Second record,

2 Second road
trichy
tr
india
636701

Output for the Third record,

3 Third road
trichy
tr
india
636701

Cheers,

Venkatesan Prabu .J

Replication and autonumber generation issue

2010-10-03T05:11:00.000-07:00

Sent: 30 September 2010 22:22
To: jvprabhusanthi@gmail.com
Subject: Invoice Number Generation in offline/online database

Hi Venkatesan Prabu

I would like to get your suggestions in regards to invoice number generation in an application that work offline and online

We have a inventory application developed in .net and sql server ,that can work in a LAN by connecting to the server database

Now we need to provide the offline feature for the application, a counter can work independenly without connecting to the server database.i.e, by connecting
to a local copy of the server database

Before the enter any sales data in the counter ,the sales person has to synchronize the data with the server database .to get the stock details and all
Once synchronistation is done,sales bills can be entered from the counter.and in the end of the day or before starting next days work the sales person has to
synchronise the data with the server again.so the Sales details will be available in the server.

The problem here is that the invcoice number generation.Currently the Automatic invoice number is generated using the query

select isNull(Max(BillNumber),0)+1 from PurchaseHeader

If there is more one sales counters that work on local copy of the server database ,the sales billnumber will be duplicated.
because the billNumber is generated from the local database only. when this is uploaded to server there will be duplicate billnumbers

Could you please suggest me some possible solutions to this problem

--
Thanks,
Bijith
Check my Reply for the above problem. Share your thoughts and post some more solutions
Hi Bijith,

Very good practical problem. We can provide multiple solution for your problem.

Below is the best solution for your problem.

1. You need to use Replication for updating your data from local database to master database.
2. In the local database, generate a unique id (Your Invoice number) and amend another unique number (Local counter number). (Your invoice number is 120 and counter id is 20, then the actual invoice number is 12020. The last two number will be unique for your counter).
3. While pushing this data to the master database, generate unique id in the master database and another column will hold the invoice number generated in the local database.

Let me know for any other details needed.
Cheers,
Venkatesan Prabu .J
MVP / MVM / MCITP / MCTS / MCAD / CCNA / QAI
Microsoft SQL Server MVP / Mind Cracker MVP
http://venkattechnicalblog.blogspot.com/

My session at Sydney Microsoft Office

2010-09-20T21:31:00.000-07:00

"Presenting a session in Microsoft" It's my greates dream in my life.

In one word, “It’s a Marvellous evening”. Interesting part is, Adam Cogan is my fellow speaker. Working in Microsoft as a Technical Architect and as a Microsoft Regional Director too :-)

Presented my session in Microsoft office, Attended Adam’s session, Techie talks with hard core developers and finally my session is recorded and it will published soon in Microsoft portal.

To be frank, No words to express my joy…. In presenting an evening session to the techie experts in Microsoft, Sydney office.

Sydney Microsoft Office

Speaker’s desk

Preparing my laptop for the presentation

Oops, very big screen…

Started my session with the query, “How a DBA can achieve Enterprise wide Compliance standard?”

Practical demos

Adam cogan’s session on Sharepoint 2010 Wiki Vs Sharepoint 2010 Blog

My kid playing in Microsoft office

My wife and Kid…in MS office

Front row : My wife, Myself, Adam Cogan and a 22 year experienced Microsoft developer

Back row : Hard core techies

Cheers,

Venkatesan Prabu .J

My Session in Sydney SQL User group

2010-09-19T04:52:00.000-07:00

My presentation @ Greater Sydney.....

At last, it’s my dream evening. I gave a presentation to the SQL Server Gurus in Sydney…
Almost most of the attendees were having more than 15 years of experience in SQL Server :-)

They live with SQL Server technology. It was a pleasant moments and I have learnt a lot on meeting those technology Gurus.

Intro about me by Grant paisley (SQL Server MVP and Director of Angry Koala pty ltd)

My presentations starts with the query…. Can a DBA manage 100 Servers in One go???????

Discussion on Policy Creation and deployment Strategies

Practical demo on Microsoft in-built policies

My favourite pic (With my 2009 MVP Award)… Query section..

Thank You slide

My Fellow speaker Darren Geros (22 years in IT, Director of NSI Technology Pty Ltd, Director AVOGA Pty Ltd)

Got a fantastic response from the audience and it’s too nice to see some smile faces

Thanks and Regards,

Venkatesan Prabu .J

Dotnet and SQL Job in Hyderabad

2010-09-17T15:05:00.000-07:00

I got this from one of my email... May be useful to some one... who really need it...

Urgent requirement for ASP.Net professionals with a minimum experience of 3yrs in relevant field in Hyderabad.

Skills
ASP.Net 35 frame work, Ajax, sql.server 2005/2008, WCSF, classic ASP

Package: 2.4L - 3.0L
Location: Hyderabad

If anybody is interested Send your resumes to bobby.simson13@gmail.com

Cheers,
Venkatesan Prabu .J

Steps in loading your web contents - About browsers

2010-09-16T21:08:00.000-07:00

Have you questioned yourself, why my browser is too slow ?????

To load the web page, your browser will traverse 11 sub systems or steps to display your web content :-) Interesting right !!! Let’s see,

1. Network – First subsystem which is responsible for client / server interaction. This relies on the hosted server’s performance and individual user’s network traffic.
2. HTML – Followed by the HTML – sub system. This allows you to download the HTML content from the server and structured it back to the client machine.
3. CSS – Cascade Style sheet, this will provide you the customised colouring and structure to the content. The details will be collected and it will be used later (In point 8).
4. Collections – Accessing metadata information related to your HTML. This guy will take care of Segregating your head and body contents separately.
5. Java script – Client side scripts which will execute based on the user’s action.
6. Marshalling – Java script engine can’t directly interact with the browser. Marshalling is a special communication layer between JavaScript engine and your browser.
7. DOM API – Special API’s responsible for interaction between JavaScript engine and your browser.
8. Formatting – All your objects will be formatted based on the CSS information collected.
9. Block building – Your objects will be segregated as separate blocks
10. Lay out formation – Now, depends on your Web parts or Frames or div tag or Table. Your layouts will be formed.
11. Image Rendering – Rendering of images (or) drawing to the screen through CPU or GPU.

Cheers,
Venkatesan Prabu .J

SQL Profiler InputBuffer events in SQL Server

2010-09-08T22:36:00.000-07:00

As a DBA, sometimes you need to identify the last execution happened or happening in your server. What you will exactly do?
1. Start your profiler and check it.
2. Check the process info for the server processes running.
3. In SQL Server 2008, you can opt Activity Monitor.

What else?????????

Here is another method using queries? If you got the spid then DBCC Inputbuffer command will provide you the last execution command of your user. Let's see how?????

Get the current session id

SELECT @@SPID
The output is, 51

Let's execute the below query now,

CREATE TABLE VENKAT_TABLE (Col1 int)
GO
DECLARE @i int;
SELECT @i = 0
BEGIN TRAN
SELECT @i = 0
WHILE (@i < i =" @i">

Now, open another session or connection, and execute the command

DBCC INPUTBUFFER (51);

You will get the last statement executed by the particular session user.

Usually, we will get confused on seeing the sequence of below statements in our profiler.

create table #tmpDBCCinputbuffer ([Event Type] nvarchar(512), [Parameters] int, [Event Info] nvarchar(512))
insert into #tmpDBCCinputbuffer exec ('DBCC INPUTBUFFER(51)')
select [Event Info] from #tmpDBCCinputbuffer

It's nothing but the in-house or profiler query itself. The above statements were used to fetch the latest statements of your server processes.

Cheers,
Venkatesan Prabu .J

Like operator on a column name in SQL Server

2010-09-08T12:22:00.000-07:00

To: jvprabhusanthi@gmail.comSubject: Help...Urgent
Hi Prabhu,

i need urgent help from you for sql script.
i have field called "Notes" in table ABC and have another table called XYZ.
XYZ table has five fields 1,2,3,4,5
the requirement is :

select * from abc
where 1=1
and abc.notes like '%xyz.1%'
and abc.notes like '%xyz.2%'
and abc.notes like '%xyz.3%'
and abc.notes like '%xyz.4%'
and abc.notes like '%xyz.5%'

did you understand?
notes field has some long strings... xyz table clumns string should match with that notes from the abc table.
how do i write the sql script.
becoz i have nearly 2000 records in the table.. can you help out plz....
its urgent.
thanks in advance
pinky

----------------------------------------------------------------------------------

It's a very usual question. How to use "Like operator on a column" -> We should use inner join on TableA.Column_Name like TableB.Column_Name

Let's see a small example to understand a little bit.

DROP TABLE PINKY1
DROP TABLE PINKY2

GO
CREATE TABLE PINKY1(NOTES VARCHAR(100))
INSERT INTO PINKY1 VALUES('VENKAT')
INSERT INTO PINKY1 VALUES('VENKATESAN PRABU')
INSERT INTO PINKY1 VALUES('rENKAT')
INSERT INTO PINKY1 VALUES('AENKAT')
INSERT INTO PINKY1 VALUES('AENKAT')
INSERT INTO PINKY1 VALUES('zxNKAT')
INSERT INTO PINKY1 VALUES('zxNKAT')
INSERT INTO PINKY1 VALUES('zxNKAT')
INSERT INTO PINKY1 VALUES('VERESEAEz')

GO
CREATE TABLE PINKY2(VAL1 VARCHAR(100),VAL2 VARCHAR(100),VAL3 VARCHAR(100),VAL4 VARCHAR(100),VAL5 VARCHAR(100))
INSERT INTO PINKY2 VALUES('VE%','%RE%','%SE%','%AE%','%E_')
INSERT INTO PINKY2 VALUES('BE%','%RE%','%SE%','%AE%','%E_')
INSERT INTO PINKY2 VALUES('SE%','%RE%','%SE%','%AE%','%E_')
INSERT INTO PINKY2 VALUES('DE%','%RE%','%SE%','%AE%','%E_')

SELECT * FROM PINKY1 INNER JOIN PINKY2 ON NOTES LIKE VAL1
WHERE NOTES LIKE VAL2 AND NOTES LIKE VAL3 AND NOTES LIKE VAL4 AND NOTES LIKE VAL5

Cheers,

Venkatesan Prabu .J

All about user creation in SQL Server

2010-09-07T20:47:00.000-07:00

Creating a new user in SQL Server:

Goto SSMS -> New User. Provide your user name, in my case its sample_user

Choose the authentication and choose the default database.

Default database indicates the database to which the user is having the access. In the next page, you can specify the level of access to the user. The next drop down is Default language -> I have made it as default and it's English.

Arabic, Brazilian, British English, Bulgarian, Croatian, Czech, Danish are some of the languages listed.

In the next window, you can specify the role of the user and it's server role. I have informed that, this person is a sysadmin and public too.

Next option is "User Mapping", here all the database will be listed. You can specify the level of access to be provided to this user for each database.

I do not want to provide any other access to this user except "VenkatDB".

Next screen is securables, you will have list of objects like Server, Endpoints and Logins.

You can choose the object on which you want to provide or deny access.

For example, I have selected Server -> Select an option "Create any database" and -> Select deny option. Here, we have restricted the user to create any database. Still, he is a sysadmin but he can't create any database.

The final page is status. It will define the current state of the user.

Now, let's move to our actual topic.

How to prevent access to a database for a particular SQL Login user????

Consider am having a user who is having access to multiple databases? I want to restrict the user not to use "VenkatDB" - > A specific database.

Now, you need to first make the user as public.

Provide necessary access level in the database level. Make the user as public for that specific database. Now click "OK". Reopen the login page for that particular user. "VenkatDB" is disappeared.

Now, I am trying to access the objects in the database. Below is the message received.

" The server principal "venkat" is not able to access the database "VenkatDB" under the current security context. "

Cheers,

Venkatesan Prabu .J

Database size in Express editions

2010-09-07T01:09:00.000-07:00

SQL Server 2005 Express editions - Database size is 4 GB
SQL Server 2008 Express editions - Database size is 4 GB
SQL Server 2008 R2 Express editions - Database size is 10 GB

Cheers,

Venkatesan Prabu .J

http://venkattechnicalblog.blogspot.com/

Check the Updates in our machine

2010-09-07T00:53:00.000-07:00

This one is pretty interesting findings, I found the check box "Show Updates" in the Add or Remove Program.

On selecting the check box, you will see a fantastic difference in listing the installed softwares.
It's a tree nature display which will give the information like which one is the master one and what all the patches applied on it.

It's a fantastic feature and I found it today!!!!! Eurekhaaaaaaaaaaaaaaaa

Cheers,

Venkatesan Prabu .J

http://venkattechnicalblog.blogspot.com/

Select Statements to access stored procedures

2010-09-07T00:37:00.000-07:00

Is it possible to use the select statement to access stored procedures?????

In one of the difference between functions and stored procedures is, we can use the "select"
statement in functions whereas we can't use the same in stored procedures.

Can we make this statement false????? Not completely but a little bit.

Before that, am trying to create a Linked server.

Goto SSMS -> Your DB Server -> ServerObjects -> Linked Server - > Create new Linked Server.

You will get a message box to type your Linked server. You need to provide the servername.

Specify whether it's SQL Server or not. If it's not SQL Server, provide necessary details to connect it.

The second window is authentication, you need to specify the credentials used to access the Linked Server.

The third window specifies the options to be provided for the Linked server. If everything is good. Click "Ok" button.

Now, your Linked Server is created.

Now, you can see your catalogs listed under the Linked Server.

Now, am trying to access the Linked Server's table,
SELECT * FROM LinkedServer.VENKATDB.dbo.VENKAT_TABLE

Awesome,

Am able to see my data.

Goto your Linked Server query window and create a procedure (use the below code)

DROP TABLE VENKAT_TABLE_LINKED
CREATE TABLE VENKAT_TABLE_LINKED (ID INT)
INSERT INTO VENKAT_TABLE_LINKED VALUES (1)
INSERT INTO VENKAT_TABLE_LINKED VALUES (2)
INSERT INTO VENKAT_TABLE_LINKED VALUES (3)
INSERT INTO VENKAT_TABLE_LINKED VALUES (4)
GO
DROP PROCEDURE VENKAT_PROCEDURE
GO
CREATE PROCEDURE VENKAT_PROCEDURE
AS
BEGIN
SELECT * FROM VENKAT_TABLE_LINKED
END
GO
EXEC VENKAT_PROCEDURE
GO

Now, your procedure is created in the Linked server and am trying to access that procedure from the actual server using select statement. Le's see how to do it.

This can be achieved using OpenQuery statement.

SELECT * FROM OPENQUERY ([LinkedServerName],'MASTER.DBO.VENKAT_PROCEDURE')

So, the statement is wrong :-)

The actual statement is, "Stored procedures can't use Select statement in it's own server"

Hope, you all agree with my statement.

Cheers,

Venkatesan Prabu .J

Linked servers in SQL Server

2010-09-06T22:39:00.000-07:00

Linked Server -

It's quite a different area, which I would like to concentrate more. What's this???? linked server......

Accessing the resource in the other server can be achieved using Linked server. Considering am having a server named "ServerA" and I would like to grab some data from the remote server named "ServerB". In that case, we need to create a linked server. In our scenario, the linked server is "ServerB".

Let's see, How to link it? How to access the data?

Goto SSMS -> Your DB Server -> ServerObjects -> Linked Server - > Create new Linked Server.

You will get a message box to type your Linked server. You need to provide the servername.

Specify whether it's SQL Server or not. If it's not SQL Server, provide necessary details to connect it.

The second window is authentication, you need to specify the credentials used to access the Linked Server.

The third window specifies the options to be provided for the Linked server. If everything is good. Click "Ok" button.

Now, your Linked Server is created.

Now, you can see your catalogs listed under the Linked Server.

Now, am trying to access the Linked Server's table,
SELECT * FROM LinkedServer.VENKATDB.dbo.VENKAT_TABLE

Awesome,
Am able to see my data.

Cheers,
Venkatesan Prabu .J

SQL Server installation issue with Windows XP sp3

2010-09-06T21:25:00.000-07:00

I happened to install SQL Server in Windows XP sp3 version. Oops, I couldn't make it out.

1. Am facing issue with MSXML6.

2. Because of the above point, workstation components and Database services were not installed in my machine.

Reason for this issue:

MSXML6 is trying to change the language settings of WindowsXP sp3 which it couldn't succeed. So the installation is failing. Looks like, it's a well know bug.

Just checking for the workaround, I found the below blog which may be useful for you. Please check it out,

http://sqlblog.com/blogs/aaron_bertrand/archive/2009/02/20/the-xp-sp3-msxml6-sp2-sql-server-debacle.aspx

Cheers,
Venkatesan Prabu .J

Format files in SQL Server

2010-09-05T23:11:00.000-07:00

For many years, I used to think about writing an article on Format files. I got shocked on seeing a procedure which is using format file and I am not aware of this area. Anyway, Learning starts or goes only if we don't know something right!!!!

Come one........Let's learn something...........

BCP - Bulk copy program

1. It's a utility to copy the data in bulk.

2. Data from files (or) to files in a specific format can be achieved using this BCP.

Format file:

1. If we want to read a file, it's very hard for SQL Server to read the file and store it in the database. Yes, we can store it but it will varchar field in the database.

2. To inform SQL Server about the datatype in the files. We use the Format file.

3. The Format file will tell you that, the file holds integer value, datetime field value with length 10 etc..,

How to create a Format file????? Let's see the below example to understand it.

Am having a table named Venkat_table

Below is the command to get the Format file,

bcp Venkat_Table format nul -S servername -T -n -f C:\Venkat_TableFormat.fmt

The Syntax is like, BCP DatabaseName.Ownername.ObjectName format nul -S servername -T -n -f formatfilename

nul - If there is no data file name to load. Here, we are fetching data from the table.

-T - Indicates trusted authentication or else -U and -P should come (SQL Server authentication)

-n - Indicates native data type. We will discuss more below.

-f - Informing the format file

We should go to the Windows command prompt and execute the above command, a file will be generated in the desired location.

On opening the Format file, you will get the below details.

Here is a generic details to understand the format file.

bcp Venkat_Table format nul -S servername -T -c -f C:\Venkat_TableFormat.fmt

-C indicates the type of columns to be assumed. C indicates the char type. In the below pic, you can see the datatypes were located as character datatype. If it's -n, then the datatype is native datatype.

Cheers,

Venkatesan Prabu .J

Pages in Cache - Dirty Pages details

2010-09-05T22:20:00.000-07:00

I got this query from one of the famous article. This query will provide you the information about the pages cached in the memory. Hope, it will useful for the DBA's to identify the performance issues.

-------------------------------------------------------------------------------------------------

;WITH memusage_CTE AS (SELECT bd.database_id, bd.file_id, bd.page_id, bd.page_type
, COALESCE(p1.object_id, p2.object_id) AS object_id
, COALESCE(p1.index_id, p2.index_id) AS index_id
, bd.row_count, bd.free_space_in_bytes, CONVERT(TINYINT,bd.is_modified) AS 'DirtyPage'
FROM sys.dm_os_buffer_descriptors AS bd
JOIN sys.allocation_units AS au
ON au.allocation_unit_id = bd.allocation_unit_id
OUTER APPLY (
SELECT TOP(1) p.object_id, p.index_id
FROM sys.partitions AS p
WHERE p.hobt_id = au.container_id AND au.type IN (1, 3)
) AS p1
OUTER APPLY (
SELECT TOP(1) p.object_id, p.index_id
FROM sys.partitions AS p
WHERE p.partition_id = au.container_id AND au.type = 2
) AS p2
WHERE bd.database_id = DB_ID() AND
bd.page_type IN ('DATA_PAGE', 'INDEX_PAGE','TEXT_MIX_PAGE') )
SELECT TOP 20 DB_NAME(database_id) AS 'Database',OBJECT_NAME(object_id,database_id) AS 'Table Name', index_id,COUNT(*) AS 'Pages in Cache', SUM(dirtyPage) AS 'Dirty Pages'
FROM memusage_CTE
GROUP BY database_id, object_id, index_id
ORDER BY COUNT(*) DESC

-------------------------------------------------------------------------------------------------

Cheers,

Venkatesan Prabu .J

Auto data population using GO statement

2010-09-05T21:54:00.000-07:00

Dear Readers, have you tried the below statement????????

INSERT INTO t1 DEFAULT VALUES;
GO 5 --- What this guy will do?????

What's the use of GO statement????? It's just a seperator between statements. It makes the statements as seperate batches.

Any wild guesses...........???????????????????

Ok let's see a small example,

DROP TABLE t1
go
CREATE TABLE t1 (
c1 INT IDENTITY,
c2 as c1*2
);
GO
-- I have create a table
SET NOCOUNT ON;
GO
INSERT INTO t1 DEFAULT VALUES;
GO 5
select * from t1

OK, go ahead and start populating your dummy tables.

Cheers,
Venkatesan Prabu .J

Update table based on the other column

2010-08-31T19:34:00.000-07:00

I got a new scenario in one of the popular forum. "Update the table based on the other column"
Let's move the exact scenario, there's a table with the below data
--------------------------------
ID Name GroupID GroupName
--------------------------------
1 A 2 ?
2 B 3 ?
3 C 3 ?
4 D 3 ?
--------------------------------
I need to update the groupname based on the group ID column?

The output should be,
--------------------------------
ID Name GroupID GroupName
--------------------------------
1 A 2 B
2 B 3 C
3 C 3 C
4 D 3 C
--------------------------------
Here we need to use,
1. Self join option - Joining the table with it's own
2. Update statement with inner join
Below is the query to achieve it,

--- Am creating the table
DROP TABLE VENKAT_TABLE
CREATE TABLE VENKAT_TABLE (ID INT, NAME VARCHAR(100),GROUPID INT, GROUPNAME VARCHAR(100))

-- Inserting needy data
INSERT INTO VENKAT_TABLE(ID,NAME,GROUPID) SELECT 1,'A',2
UNION ALL SELECT 2,'B',2
UNION ALL SELECT 3,'C',3
UNION ALL SELECT 4,'D',3
UNION ALL SELECT 5,'E',4

Self joining the table:

SELECT A.NAME,* FROM VENKAT_TABLE A INNER JOIN VENKAT_TABLE B
ON A.ID = B.GROUPID

update query with Innerjoin / self join:

UPDATE B
SET GROUPNAME = A.NAME
FROM VENKAT_TABLE A INNER JOIN VENKAT_TABLE B ON A.ID = B.GROUPID

SELECT * FROM VENKAT_TABLE

Cheers,
Venkatesan Prabu .J

http://venkattechnicalblog.blogspot.com/

Round function in SQL Server

2010-08-23T02:04:00.000-07:00

I happened to work in the round function. I found some interesting / peculiar nature of round fucntion. Hope you will enjoy this article. Can you believe you can use round function to validate the input values???????Are you ready for the excitement??

The syntax for the round function is
round(value, length)
length indicates the rounding at the precion level (If positive)...Or else it will round the actual value (If the value is negative)

You will understand it from the below sample queries.
Now, I am trying to round the below value. Let's see how it goes....

SELECT ROUND(19.46,0 ) as total
-- In above query, am informing sql to round the value with no precisions.

SELECT ROUND(19.46123,1 ) as total
-- In above query, am informing sql to round the value with 1 precisions. (.46) value is round off to (.5).
--if the value is (.44) it will rounded off to (.40). Since its (.46) which is greater than (.45) the value is (.50)

SELECT ROUND(19.46123,2 ) as total
-- In above query, am informing sql to round the value with 2 precisions.

SELECT ROUND(19.46123,-1 ) as total
---- In above query, am informing sql to round the value not the precisions.
--If the value is negative, the rounding operation will start from the left side. Here, it's trying the value 19 and not
--checking the precisions (.46123)

SELECT ROUND(19.46123,-2 ) as total
-- In above query, am informing sql to round the value. If the length is equal or more than the value. Then round function will make the value as 0.

Interesting right!!!!

So, If two digit value is mandatory and if the user is trying to give 1 digit. The above query will help us to evaluate the inputs.

Good one right!!!!!

Cheers,

Venkatesan Prabu .J

Date datatype in SQL Server 2008

2010-08-23T01:56:00.001-07:00

DATE DATA-TYPE:

Date datatype is one of the new data type introduced in SQL Server 2008. It provided you the date.

Reason for this data type:

There are lot of scenarios where, we need to get only date. In our legacy system, we dont have an option to get only date. We need to do some logic on the datetime column to get the time.

Let's play around this Date datatype in our SSMS :-)
-------------------------------------------------------------------------------------

DECLARE @VAL DATE
SELECT @VAL = GETDATE()
PRINT @VAL

The output is

2010-08-23 ( The format is Year:Month)
-------------------------------------------------------------------------------------

Memory space taken:
This datatype will take 3 bytes of memory.

Implicit conversion from datetime datatype:

From Datetime to Date , implicit conversion will happen. No need to explicitly convert the datatypes. Below is an example for the same.

-------------------------------------------------------------------------------------

DECLARE @VAL DATETIME
DECLARE @VAL1 DATE
SELECT @VAL = GETDATE()
SELECT @VAL1 = @VAL
PRINT @VAL
PRINT @VAL1

The Output is,

Aug 23 2010 7:01PM
2010-08-23
-------------------------------------------------------------------------------------

Cheers,
Venkatesan Prabu .J

Time datatype in SQL Server 2008

2010-08-23T01:41:00.000-07:00

TIME DATA-TYPE:

Time data type is one of the new data type introduced in SQL Server 2008. It provided you the time with the accuracy of 100 nanoseconds.

Reason for this data type:

There are lot of scenarios where, we need to get only time. In our legacy system, we dont have an option to get only time. We need to do some logic on the datetime column to get the time.

Let's play around this Time datatype in our SSMS :-)
-------------------------------------------------------------------------------------
DECLARE @VAL TIME
SELECT @VAL = GETDATE()
PRINT @VAL

The output is
18:45:29.0230000 ( The format is hour:Minute:Second:Nanoseconds upto 7 precision)
-------------------------------------------------------------------------------------

Memory space taken:
This datatype will take 3 to 5 bytes memory.

Let's see another example,
-------------------------------------------------------------------------------------

DECLARE @VAL TIME(0) -- This will take 3 bytes to store. Because it doesn't have nanoseconds to store
DECLARE @VAL1 TIME(7) -- -- This will take 5 bytes to store. Because, it holds nanoseconds
SELECT @VAL = GETDATE()
SELECT @VAL1= GETDATE()
PRINT @VAL
PRINT @VAL1

The output is,
18:48:43 -- 3 bytes to store only hour:minute:seconds
18:48:42.5530000 -- 5 bytes to store
-------------------------------------------------------------------------------------

Implicit conversion from datetime datatype:

From Datetime to time, implicit conversion will happen. No need to explicitly convert the datatypes. Below is an example for the same.

-------------------------------------------------------------------------------------
DECLARE @VAL DATETIME
DECLARE @VAL1 TIME(7)
SELECT @VAL = GETDATE()
SELECT @VAL1 = @VAL
PRINT @VAL
PRINT @VAL1

The Output is,

Aug 23 2010 6:51PM
18:51:40.3500000
-------------------------------------------------------------------------------------

Cheers,
Venkatesan Prabu .J

Running vs Configured Values in SQL Server 2008

2010-08-23T01:32:00.000-07:00

Have you noticed an option Running vs. Configured Values. What's this?

1. Configured values - Modified value and it's effectively only if you click OK button (Some options may be after server restart)
2. Running values - Current values stored or current property of the server.

Cheers,
Venkatesan Prabu .J

Index Creation Memory option in SQL Server 2008

2010-08-23T00:39:00.000-07:00

This option will control the maximum amount of memory to be allocated for the index creation. It's a defined size allocated for the index creation.

Scenario 1 : If the server memory is available and if the index needs more memory space. In that case, SQL Server will try to use the available server memory.

Scenario 2 : If the server memory is not available and if the index needs more memory space. In that case, SQL Server will try to use the existing allocated index space.

Right click on the server ->Properties -> Memory. You will get this option.

By default, the value is 0

Coniguring this setting through query,

--------------------------------------------------------------------------------
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'index create memory', 4096 -- Am setting the value as 4096KB
GO
RECONFIGURE;
GO

--------------------------------------------------------------------------------

Cheers,

Venkatesan Prabu .J

Function related to exceptions in sql server

2010-08-22T06:20:00.000-07:00

I happened to read an article on SQL Server exception. I found some of the useful functions which will be very useful to find the exception details like,

1. ERROR_NUMBER() - This function will provide your the exact error number.
2. ERROR_MESSAGE() - This function will provide the error message thrown
3. ERROR_LINE() - This function will provide you the exact line number where error occurs.
4. ERROR_PROCEDURE() - This function will provide your the procedure where error occurs.

BEGIN TRY BEGIN TRAN
DECLARE @dividebyzero INT
SET @dividebyzero = 100
SET @dividebyzero = @dividebyzero/0
COMMIT
END
TRY
BEGIN CATCH
PRINT ERROR_NUMBER()
PRINT ERROR_MESSAGE()
PRINT ERROR_LINE()
PRINT ERROR_PROCEDURE()
ROLLBack
END CATCH

Cheers,
Venkatesan Prabu .J

Swap values in a column

2010-08-22T04:26:00.000-07:00

I got a query from one of the blog reader. How to swap the values in a column?
There is a column "availability" in a table and it's have 2 values "Yes" or "No".

The user have wrongly entered the values. Instead of yes they have inserted No and similary for No, they have entered Yes. How to rectify this problem?

Here is the solution to solve this problem

DROP TABLE VENKAT
CREATE TABLE VENKAT(ID INT, NAME VARCHAR(100))
INSERT INTO VENKAT VALUES(1,'VENKAT1')
INSERT INTO VENKAT VALUES(2,'VENKAT2')
INSERT INTO VENKAT VALUES(3,'VENKAT1')
INSERT INTO VENKAT VALUES(4,'VENKAT2')
SELECT * FROM VENKAT
BEGIN TRANSACTION
UPDATE VENKAT SET NAME='VENKAT12' WHERE NAME='VENKAT2'
UPDATE VENKAT SET NAME='VENKAT2' WHERE NAME='VENKAT1'
UPDATE VENKAT SET NAME='VENKAT1' WHERE NAME='VENKAT12'
COMMIT
SELECT * FROM VENKAT

Cheers,
Venkatesan Prabu .J
http://venkattechnicalblog.blogspot.com/

Profile issue with Outlook

2010-08-21T05:25:00.000-07:00

I happened to configure my exchange server and gmail in my Outlook 2007.

Initially, I tried to configure my exchange server. I had some problem in connecting my server. After that, I have configured my gmail. Yahooo.... am able to access my gmails. But while opening my Outlook am getting errors about the exchange server. I thought of removing the same from my outlook.

Went to Options and tried deleting the exchange server under mail section. I couldn't succeed.

I got the below error,

"You cannot delete this Outlook data file. Configuration information in the file is being copied to your new default data file. You can delete the file after this information is copied."

I moved to data tab and tried to remove the data file. Am getting the below error,

"This data file is associated with a mail account. To remove it use the 'E-Mail' tab.

How can I solve this??????

Here is the simple approach,

1. Once you configured your email. A profile will be created for you.
2. Got control panel -> Mail -> You will get Email accounts/Data files/Profiles. Click the third button and remove your current profile. Create a new profile and add your Gmail account. Yes, now are ready to go -> Am able to check my gmails...

Cheers,
Venkatesan Prabu .J

Not In Vs Not exists clause in SQL Server - Part 1

2010-08-15T13:48:00.000-07:00

NOT IN Operator and NOT EXISTS Operator will work or produce similar results. But, the operational flow in getting the results will differ a lot.

Not In operator will check for a particular value in the subquery list. Subquery will provide a list of values and our main query will check for the non availability of specific column values in the subquery list. It will use nested loops to fetch the data. We will see in details while analysing the execution plans.

SELECT NAME FROM VENKAT WHERE ID NOT IN (SELECT ID FROM VENKAT_SECOND_TABLE)

Not Exists operator will do a similar kind of operation but the matching is done with correlated join and it will utilise the index to fetch the records. So performance wise, this will give you an apt solution.

SELECT NAME FROM VENKAT V1 WHERE NOT EXISTS (SELECT ID FROM VENKAT_SECOND_TABLE V2 WHERE V2.ID=V1.ID)

Your code snippet,

DROP TABLE VENKAT
GO
CREATE TABLE VENKAT(ID INT, NAME VARCHAR(100))
INSERT INTO VENKAT VALUES(1,'VENKAT1')
INSERT INTO VENKAT VALUES(2,'VENKAT2')
INSERT INTO VENKAT VALUES(3,'VENKAT3')

GO
DROP TABLE VENKAT_SECOND_TABLE
GO
CREATE TABLE VENKAT_SECOND_TABLE(ID INT, NAME VARCHAR(100))
INSERT INTO VENKAT_SECOND_TABLE VALUES(1,'VENKAT1')
INSERT INTO VENKAT_SECOND_TABLE VALUES(2,'VENKAT2')
INSERT INTO VENKAT_SECOND_TABLE VALUES(4,'VENKAT4')
GO

SELECT NAME FROM VENKAT WHERE ID NOT IN (SELECT ID FROM VENKAT_SECOND_TABLE)
GO

SELECT NAME FROM VENKAT V1 WHERE NOT EXISTS (SELECT ID FROM VENKAT_SECOND_TABLE V2 WHERE V2.ID=V1.ID)

Both above statements were providing a similar kind of results. Let's see the execution plan for both,

From the execution plan, we can easily identify the performance comparison these two operators. Hope, you will be using not exists operator in your future queries:-)

Cheers,
Venkatesan Prabu .J

Single quotes inside the string

2010-08-13T08:09:00.000-07:00

Storing character or string is possible in the database. For storing the value we will put the string inside the single quotes and we will insert it. For instance,

INSERT INTO VENKAT_TABLE VALUES(1,'VENKAT')

Now, I want to insert a data like Venkat's instead of venkat. Let's see the query now,

INSERT INTO VENKAT_TABLE SELECT 1,'MEN'S'

Am getting the error,

Msg 105, Level 15, State 1, Line 3
Unclosed quotation mark after the character string '

In this case, we need to use two single quotes to insert one single quote between the characters in a string.

DROP TABLE VENKAT_TABLE
CREATE TABLE VENKAT_TABLE(ID INT, NAME VARCHAR(100))
INSERT INTO VENKAT_TABLE SELECT 1,'MEN''S'
SELECT * FROM VENKAT_TABLE

Cheers,
Venkatesan Prabu .J

My SQL Server session in Chennai - One of the fabulous event

2010-08-10T21:38:00.000-07:00

Writing in my blog after a very long time. Last month, I got a very nice opportunity to share my knowledge on SQL Server 2008 - Manageability features to chennai SQL experts.

It was a unforgottable experience and I enjoyed the whole day with my fellow MVP's. I came to know about me.

As usual am late:

My session is in the afternoon and am bit late for the session. I missed Pinal's morning session on spatial indexing. Anyway, I have attended his final session.

This program is well organised and the entire credit goes to the organisers Sugesh, Vidya sagar, Madhivanan and Deepak. No words to express my joyfullness in meeting all the technology experts at the same time in Chennai.

My Laptop problem:

Unfortunately, my laptop is not connecting to the Projector. We tried our level best but nothing works for me. Moved all my presentation materials to vidya sagar's laptop. Unfortunately, VS2010 demo is not working in his laptop. Atlast, I moved my presentation materials to Deepak's laptop. Thank god, It's working fine. Everyone is too friendly, but I couldn't come out from my tension. Had little bit of food and started my session.

My session:

Am bit tensed and faced a problem with SQL Server authentication. Deepak and vidya sagar understood my situation and helped me at the initial stage. Now, I am relaxed a little bit.

Below are the topics covered in my session,
1. Activity Monitor
2. Policy Based Management
3. Resource Governor
4. SQL Server Audit

I have provided the Sample scenario, Details and Sample demo for each topics.

My snap with other technology experts Vidya sagar, Madhu K.Nair, Mathivanan, Pinal Dave, Deepak and myself (To know more about these people, you can just google their names)

I got a friendly gift/honor from SQL Server Chennai User group (Gift from vidya sagar). All attendees have got a black colour T-shirt with CSSUG embossed on it. In addition to this, speakers should select 2 active attendees and those two will be rewarded with SQL Server related books. Speakers will put their autograph and presented the books to the active participants.

Special note on Pinal Dave:

OOPS, Pinal is an extra ordinary person. I can remember he speaks atleast 30 minutes continuously on seeing me. He had the capability to talk on any topics for atleast 1 hour :-) (Just kidding). Too realistic, too jovial, too friendly and finally a very strong SQL Server expert. Proud to tell you all that, I am a big fan of Pinal Dave.

Anyway, Thanks to CSSUG organisers for providing me a very nice opportunity to share my knowledge and to meet everyone.

Cheers,
Venkatesan Prabu .J