Cannot connect to Analysis Services version '10.50.1600.1'.

 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

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

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

Here is the steps to escape from this issue.

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

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#

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

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#

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

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

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

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

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

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

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

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

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

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"

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

        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

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

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

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

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

"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

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

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

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

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

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

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

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

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

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

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/