SQL SERVER – 2008 – Fix Connection Error with Visual Studio 2008 – Server Version is not supported – VS SP1 ISO Download

I previously wrote article SQL SERVER – 2008 – Fix Connection Error with Visual Studio 2008 – Server Version is not supported where I discussed how downloading Visual Studio SP1 will fix the error of Visual Studio 2008 connecting to SQL Server 2008. I have provided link to SP1 which was downloading only installer and after that it downloads SP1 component from internet.

.NET Expert Vidya Vrat Agarwal has pointed out that Visual Studio SP1 can be downloaded as ISO. It is really good that now after downloading only one it can be used again to installed SP1 on multiple computers.

Download Microsoft Visual Studio 2008 Service Pack 1 (iso)

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL Server – 2008 – Cheat Sheet – One Page PDF Download

Very frequently I have been asked to create a page, post or article where in one page all the important concepts of SQL Server are covered. SQL Server 2008 is very large subject and can not be even covered 1000 of pages. In daily life of DBA there are few commands very frequently used and for novice developers it is good to keep all the important SQL Script and SQL Statements handy.

I have attempted to create cheat sheet for SQL Server 2008 most important commands. User can print this in one A4 size page and keep along with them. This can be used in interviews where T-SQL scripts are being asked.

Let me know your opinion and if you find this useful.

Download SQL Server 2008 Cheat Sheet

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Guidelines and Coding Standards Part – 2

  • To express apostrophe within a string, nest single quotes (two single quotes).

Example:

SET @sExample = 'SQL''s Authority'

  • When working with branch conditions or complicated expressions, use parenthesis to increase readability.

IF ((SELECT 1
FROM TableName
WHERE 1=2) ISNULL)

  • To mark single line as comment use (–) before statement. To mark section of code as comment use (/*…*/).
  • If there is no need of resultset then use syntax that doesn’t return a resultset.

IF EXISTS   (SELECT 1
FROM UserDetails
WHERE UserID = 50)

    Rather than,

IF EXISTS  (SELECT COUNT (UserID)
FROM UserDetails
WHERE UserID = 50)

  • Use graphical execution plan in Query Analyzer or SHOWPLAN_TEXT or SHOWPLAN_ALL commands to analyze SQL queries. Your queries should do an “Index Seek” instead of an “Index Scan” or a “Table Scan”. (Read More Here)
  • Do not prefix stored procedure names with “SP_”, as “SP_” is reserved for system stored procedures.
    Example:
    SP<App Name>_ [<Group Name >_] <Action><table/logical instance>
  • Incorporate your frequently required, complicated joins and calculations into a view so that you don’t have to repeat those joins/calculations in all your queries. Instead, just select from the view. (Read More Here)
  • Do not query / manipulate the data directly in your front end application, instead create stored procedures, and let your applications to access stored procedure.
  • Do not store binary or image files (Binary Large Objects or BLOBs) inside the database. Instead, store the path to the binary or image file in the database and use that as a pointer to the actual file stored on a server.
  • Use the CHAR datatype for a non-nullable column, as it will be the fixed length column, NULL value will also block the defined bytes.
  • Avoid using dynamic SQL statements if you can write T-SQL code without using them.
  • Minimize the use of Nulls. Because they incur more complexity in queries and updates. ISNULL and COALESCE functions are helpful in dealing with NULL values
  • Use Unicode datatypes, like NCHAR, NVARCHAR or NTEXT if it needed, as they use twice as much space as non-Unicode datatypes.
  • Always use column list in INSERT statements of SQL queries. This will avoid problem when table structure changes.
  • Perform all referential integrity checks and data validations using constraints instead of triggers, as they are faster. Limit the use of triggers only for auditing, custom tasks, and validations that cannot be performed using constraints.
  • Always access tables in the same order in all stored procedure and triggers consistently. This will avoid deadlocks. (Read More Here)
  • Do not call functions repeatedly in stored procedures, triggers, functions and batches, instead call the function once and store the result in a variable, for later use.
  • With Begin and End Transaction always use global variable @@ERROR, immediately after data manipulation statements (INSERT/UPDATE/DELETE), so that if there is an Error the transaction can be rollback.
  • Excessive usage of GOTO can lead to hard-to-read and understand code.
  • Do not use column numbers in the ORDER BY clause; it will reduce the readability of SQL query.
    Example: Wrong Statement
    SELECT UserID, UserName, Password
    FROM UserDetails
    ORDER BY 2

Example: Correct Statement
SELECT UserID, UserName, Password
FROM UserDetails
ORDER BY UserName

  • The RETURN statement is meant for returning the execution status only, but not data. If you need to return data, use OUTPUT parameters.
  • If stored procedure always returns single row resultset, then consider returning the resultset using OUTPUT parameters instead of SELECT statement, as ADO handles OUTPUT parameters faster than resultsets returned by SELECT statements.
  • Effective indexes are one of the best ways to improve performance in a database application.
  • BULK INSERT command helps to import a data file into a database table or view in a user‐specified format.
  • Use Policy Management to make or define and enforce your own policies fro configuring and managing SQL Server across the enterprise, eg. Policy that Prefixes for stored procedures should be sp.
  • Use sparse columns to reduce the space requirements for null values. (Read More Here)
  • Use MERGE Statement to implement multiple DML operations instead of writing separate INSERT, UPDATE, DELETE statements.
  • When some particular records are retrieved frequently, apply Filtered Index to improve query performace, faster retrieval and reduce index maintenance costs.
  • EXCEPT or NOT EXIST clause can be used in place of LEFT JOIN or NOT IN for better peformance.

Example:
SELECT EmpNo, EmpName
FROM EmployeeRecord
WHERE Salary > 1000 AND Salary
NOT IN (SELECT Salary
FROM EmployeeRecord
WHERE Salary > 2000);

    (Recomended)

SELECT EmpNo, EmpName
FROM EmployeeRecord
WHERE Salery > 1000
EXCEPT
SELECT
EmpNo, EmpName
FROM EmployeeRecord
WHERE Salery > 2000
ORDER BY EmpName;

© Copyright 2000-2008 Pinal Dave. All Rights Reserved. SQLAuthority.com

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Guidelines and Coding Standards Part – 1

  • Use “Pascal” notation for SQL server Objects Like Tables, Views, Stored Procedures. Also tables and views should have ending “s”.

Example:

UserDetails

Emails

  • If you have big subset of table group than it makes sense to give prefix for this table group. Prefix should be separated by _.

Example:

Page_ UserDetails

Page_ Emails

  • Use following naming convention for Stored Procedure. sp<Application Name>_[<group name >_]<action type><table name or logical instance> Where action is: Get, Delete, Update, Write, Archive, Insert… i.e. verb

Example:

spApplicationName_GetUserDetails

spApplicationName_UpdateEmails

  • Use following Naming pattern for triggers: TR_<TableName>_<action><description>

Example:

TR_Emails_LogEmailChanges

TR_UserDetails_UpdateUserName

  • Indexes : IX_<tablename>_<columns separated by_>

Example:

IX_UserDetails_UserID

  • Primary Key : PK_<tablename>

Example:

PK_UserDetails

PK_ Emails

  • Foreign Key : FK_<tablename_1>_<tablename_2>

Example:

FK_UserDetails_Emails

  • Default: DF_<table name>_<column name>

Example:

DF_ UserDetails _UserName

  • Normalize Database structure based on 3rd Normalization Form. Normalization is the process of designing a data model to efficiently store data in a database. (Read More Here)
  • Avoid use of SELECT * in SQL queries. Instead practice writing required column names after SELECT statement.

Example:

SELECT Username, Password
FROM UserDetails

  • Use SET NOCOUNT ON at the beginning of SQL Batches, Stored Procedures and Triggers. This improves the performance of Stored Procedure. (Read More Here)
  • Properly format SQL queries using indents.

Example: Wrong Format

SELECT Username, Password FROM UserDetails ud INNER JOIN Employee e ON e.EmpID = ud.UserID

Example: Correct Format

SELECT Username, Password
FROM UserDetails ud
INNER JOIN Employee e ON e.EmpID = ud.UserID

  • Practice writing Upper Case for all SQL keywords.

Example:

SELECT, UPDATE, INSERT, WHERE, INNER JOIN, AND, OR, LIKE.

  • It is common practice to use Primary Key as IDENTITY column but it is not necessary. PK of your table should be selected very carefully.
  • If “One Table” references “Another Table” than the column name used in reference should use the following rule :

Column of Another Table : <OneTableName> ID

Example:

If User table references Employee table than the column name used in reference should be UserID where User is table name and ID primary column of User table and UserID is reference column of Employee table.

  • Columns with Default value constraint should not allow NULLs.
  • Practice using PRIMARY key in WHERE condition of UPDATE or DELETE statements as this will avoid error possibilities.
  • Always create stored procedure in same database where its relevant table exists otherwise it will reduce network performance.
  • Avoid server-side Cursors as much as possible, instead use SELECT statement. If you need to use cursor then replace it next suggestion.
  • Instead of using LOOP to insert data from Table B to Table A, try to use SELECT statement with INSERT statement. (Read More Here)

INSERT INTO TABLE A (column1, column2)
SELECT column1, column2
FROM TABLE B
WHERE ....

  • Avoid using spaces within the name of database objects; this may create issues with front-end data access tools and applications. If you need spaces in your database object name then will accessing it surround the database object name with square brackets.

Example:

[Order Details]

  • Do not use reserved words for naming database objects, as that can lead to some unpredictable situations. (Read More Here)
  • Practice writing comments in stored procedures, triggers and SQL batches, whenever something is not very obvious, as it won’t impact the performance.
  • Do not use wild card characters at the beginning of word while search using LIKE keyword as it results in Index scan.
  • Indent code for better readability. (Example)
  • While using JOINs in your SQL query always prefix column name with the table name. (Example). If additionally require then prefix Table name with ServerName, DatabaseName, DatabaseOwner. (Example)
  • Default constraint must be defined at the column level. All other constraints must be defined at the table level. (Read More Here)
  • Avoid using rules of database objects instead use constraints.
  • Do not use the RECOMPILE option for Stored Procedure unless there is specific requirements.
  • Practice to put the DECLARE statements at the starting of the code in the stored procedure for better readability (Example)
  • Put the SET statements in beginning (after DECLARE) before executing code in the stored procedure. (Example)

© Copyright 2000-2008 Pinal Dave. All Rights Reserved. SQLAuthority.com

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – 2008 – Fix Connection Error with Visual Studio 2008 – Server Version is not supported

While attending conference SQLAuthority Author Visit – Microsoft Student Partner Conference, some developers informed me that SQL SERVER 2008 cannot be connected to Visual Studio 2008 and error displays as MS does not support SQL Server version. I was surprised initially as I could not believe that two MS products are not compatible. When trying myself I got the same error.

SQL Server 2008 when connected to Visual Studio 2008 gives the error that “This server version is not supported.  Only servers up to Microsoft SQL Server 2005 are supported“.

This error can be easily resolved by just installing Service pack. Download Visual Studio Service Pack 1 and install. Once service pack installed it will connect just fine.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – SharePoint – Steps To Create A Custom WebPart – Deploy It SharePoint Site

SharePoint is one interesting software from Microsoft. My outsourcing location unit is working on one large project of SharePoint. Based on users feedback and overwhelming response to article SQL Server – Error : Fix : SharePoint Stop Working After Changing Server (Computer) Name I am posting one more article which is very important for SharePoint developers.

SharePoint does not allow custom coding for any of the webpart. It is possible to create webpart in Visual Studio and integrate it with SharePoint. The process to create webpart in .NET framework and make it working in SharePoint often fails due to lack of guidance about this subject on internet. Software developer Rashmika and Dhruval has extensively worked in this subject and helped me to create this tutorial.

1. First of all we need to create a webpart in visual studio (2005,2008). For that, Go to File>> New>>Project.

2. Under Templates section select WebPart (this option is available only in 2008). For VS 2005, select Class Library and add reference for System.Web.

3. Write code WebPart you want to create. Here, code is written for a label having text “Hello World”.

using System;
using System.Runtime.InteropServices;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Serialization;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using Microsoft.SharePoint.WebPartPages;
namespace CustomWebPart
{
[Guid(“b8f18240-bc27-4f08-a03f-61bd44279d1a”)]
public class WebPart1 : System.Web.UI.WebControls.WebParts.WebPart
{
public WebPart1()
{
}
protected override void CreateChildControls()
{
base.CreateChildControls();
Label label = new Label();
label.Text = “Hello World”;
this.Controls.Add(label);
}
}
}

4. Now, Buid Webpart and generate an assembly for this webpart. For that GoTo>>Build>>Build Solution. And the GoTo>>Build>>CustomWebPart.

The WebPart is now Build, we need to deploy this assembly in sharepoint site. For deploying webpart we need to perform following steps.

5. Put the assembly in the bin folder of sharepoint site. The MOSS 2007 creates every portal in the inetpub\wwwroot\wss folder. To find the path of bin folder of the portal for which you want to deploy the webpart, identified with port number, run inetmgr(IIS).

Right Click the portal and click on Properties

6. Under HomeDirectory tab, Local Path describes the whole path, copy the path and verify it by opening it in a browser and see if the bin folder exists, if it does not exist then create a new folder and rename it bin.

7. Now copy the assembly from project output folder.

C:\Documents and Settings\Administrator\My Documents\Visual Studio 2008\Projects\CustomWebPart\CustomWebPart\bin\Debug.

Paste it in the portal bin folder. C:\Inetpub\wwwroot\wss\VirtualDirectories\80\bin

Now everytime we change the webpart we need to copy and paste the assembly in portal bin folder. Hence to avoid this stuff we can do the following step which will automatically replace the new assembly in the portal bin folder.

8. In VS .Net, Solution Explorer, Right Click the project name(CustomWebPart) Click on Properties, click on Build. Into the Output Path paste the path which we copied from inetmgr console.

9. Now to make the webpart usable we need to modify the web.config file of the portal. To declare the control safe we need to add a <safecontrols> entry in web.config file.

10. For <safecontrols> tag we require a parameter “PublicTokenKey” to generate this key drag the assembly from project folder and drop it in C:/Windows/assembly folder and it will generate a publictokenkey.

Right Click on CustomWebPart and copy Public Key Token.

Open web.config file and enter the following text :

<SafeControls>
…..
…..
<SafeControl Assembly=“CustomWebPart, Version=1.0.0.0,
Culture=neutral, PublicKeyToken=9f4da00116c38ec5″

Namespace=“CustomWebPart” TypeName=“*” Safe=“True”
AllowRemoteDesigner=“True” />

</SafeControls>

11. Now we need to Configure Portal to use CustomWebPart.

Till now the web part has been created and deployed to the site directory. Now the next part is how to use the web part on the Portal’s Site. The web part created can be placed on any site of the portal.

Open the portal site in the internet explorer, in my case its http://win2003, ensure that the logged in user has the administrative rights on the portal site, else it will not allow adding the web part.

12. Click on the Site Action>>Site Settings

13. On the site settings page under Galleries column click on the Web Parts.

14. On the Web Part Gallery Page click on the New button, to add the new web part assembly to the gallery.

15. On the New Web Parts page locate the CustomWebPart in the list, check the check box on the left and click on the Populate Gallery button the top of the page. This will result in the Web Part entry creation in the Web Part Gallery list, and hence it can be used from now on from the gallery.

Now the Web Part is ready to be added to the page.

16. Open a site created in the Portal. Click on Site Action>>Edit Page

17. This will modify the appearance of the page to enable the edit view. In this view Web Part Zones are highlighted so that a user can add a web part to the zone, Click on the Add a Web Part button in the left zone to add the Web Part.

18. Select the CustomWebPart from the web part list . It is found under the Miscellaneous section and then click on Add.

19. Click on the Exit Edit Mode link on the page and the site will return to the view mode.

20. At the end the page will appear like this.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL Server – Error : Fix : SharePoint Stop Working After Changing Server (Computer) Name

If Microsoft Office SharePoint Server (MOSS) and your database (MS SQL Server) are running together on same physical server, changing the name of the server (computer) using operating system may create non-functional SharePoint website.

When you change the physical server name the SharePoint is already connected to the SQL instance of old computer name (OldServerName/SQLInstance) and on changing the name the SharePoint will not able to connect the SQL Server  as now the SQL Server instance will run on new computer name (NewServerName/SQLInstance).

To solve this problem you need to reconfigure the entire Microsoft Office SharePoint Server with SQL Server Instance. Please follow the steps to fix the issue.

1. Open command prompt, change directory to where stsadm.exe exists.

C:\cd C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN

2. To invoke setconfigdb operation we need to use stsadm.exe. Run following command on command prompt

stsadm.exe -o setconfigdb -databaseserver ServerName -farmuser MyUserName – farmpassword MyPassword

3. Go to IIS, All Programs>>Administrative Tools>>Internet Information Service (IIS) Manager.

4. Right Click SharePoint Central Administration v3, click on delete and re-run the configuration wizard; the web application will be re-created by the configuration wizard.

5. After the above operation has completed successfully, run the SharePoint Products and Technologies Configuration Wizard.

All Programs>>Microsoft Office Server >> SharePoint Product and Technologies Configuration Wizard

6. Complete all the steps of wizard by clicking on Next button.

At the end of this process you will get a success wizard.

On completion of this whole procedure Microsoft Office SharePoint Server gets connected to the database with new Computer Name.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority Author Visit – Microsoft Student Partner Conference

The Microsoft Student Partner Program is a worldwide initiative to sponsor students who are interested in technology. The program mainly focuses on improving students skills for enjoyability, called Microsoft Student Partners (MSP).

I was recently (August 30, 2008) invited to present technical session at conference held in my City. I really enjoyed presenting the session with very enthusiastic students. I see all the students as future strong members of developer community and Microsoft is doing great job encouraging them and giving them global platform.

The program allows selected students to work along with professionals from Microsoft and to be a student representative for Microsoft on their college campus. It gives them access to the latest technology and an opportunity to explore and discover a whole new range of technologies. The best Student Partners also get the opportunity to apply for jobs at Microsoft.

I enjoyed meeting MSP program lead Siddharth Prakash and .Net Expert Mahesh Dhola at conference. There were more than 450 students attending conference. I really enjoy presenting my session of SQL Server.

I have promised to all the listener that I will soon publish SQL Server 2008 interview questions and answers and I will keep my promise. Attached are few of the photo graphs from the event.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – SQL Server Express 2008 Downloads

Microsoft SQL Server 2008 Express with Tools

Microsoft SQL Server 2008 Express with Tools (SQL Server 2008 Express) is a free, easy-to-use version of SQL Server Express that includes graphical management tools. SQL Server 2008 Express provides powerful and reliable data management tools and rich features, data protection, and fast performance. It is ideal for small server applications and local data stores.

Download Microsoft SQL Server 2008 Express with Tools

Microsoft SQL Server 2008 Express with Advanced Services

Microsoft SQL Server 2008 Express with Advanced Services (SQL Server 2008 Express) is a free, easy-to-use version of SQL Server Express that includes a graphical management tool and powerful features for reporting and advanced text-based searches. SQL Server 2008 Express provides powerful and reliable data management tools and rich features, data protection, and fast performance. It is ideal for small server applications and local data stores.

SQL Server 2008 Express with Advanced Services has all of the features in SQL Server 2008 Express, plus you can:

* Easily manage and administer SQL Server 2008 Express with a easy-to-use graphical management tool – SQL Server 2008 Management Studio Basic.

* Issue full-text queries against plain character-based data in SQL Server tables. Full-text queries can include words and phrases, or multiple forms of a word or phrase.

* Run SQL Server Reporting Services reports on local relational data.

Download Microsoft SQL Server 2008 Express with Advanced Services

Abstract courtesy : Microsoft

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – Microsoft SQL Server 2008 Books Online – BOL – English

SQL Server 2008, the latest release of Microsoft SQL Server, provides a comprehensive data platform. Books Online is the primary documentation for SQL Server 2008. The Help viewer used by Books Online requires the Microsoft .NET Framework version 2.0.

Books Online includes the following types of information:

  • Setup and upgrade instructions.
  • Information about new features and backward compatibility.
  • Conceptual descriptions of the technologies and features in SQL Server 2008.
  • Procedural topics describing how to use the various features in SQL Server 2008.
  • Tutorials that guide you through common tasks.
  • Reference documentation for the graphical tools, command prompt utilities, programming languages, and application programming interfaces (APIs) that are supported by SQL Server 2008.

Download SQL Server 2008 Book Online – BOL

Reference : Pinal Dave (http://blog.SQLAuthority.com)