Categories
SQL / Stored Procedures SQL Server 2000

“SET NOCOUNT ON” vs “SET NOCOUNT OFF”

Let’s try this out. First, open your SQL Query Analyzer. Then, run these three lines of code. (I assume your SQL Server 2000 has default Pubs database).

Use Pubs
SET NOCOUNT ON
SELECT au_lname FROM authors

Then, change from SET NOCOUNT ON to SET NOCOUNT OFF and run them.

Use Pubs
SET NOCOUNT OFF
SELECT au_lname FROM authors

Spot the difference?

Okay, if you didn’t, the hint is at the left bottom tab. Click Messages tab. Got it? The difference is

  • SET NOCOUNT ON – the count is not returned
  • SET NOCOUNT OFF – the count is returned
  • Why we need to set it ON or OFF? Answer is performance. If set it ON, it has a better performance.

    Categories
    SQL / Stored Procedures SQL Server 2000

    Execute a stored procedure from another stored procedure

    You can execute a stored procedure from another stored procedure. This means that you can create a common stored procedure so that any stored procedure which needs this common stored procedure can execute it.

    Example, you have these three stored procedures. One is a common one. The other two are normal one.

  • sp_update
  • sp_delete
  • sp_common
  • Inside sp_update, you have

    UPDATE mytable SET status ‘N’

    Inside sp_delete, you have

    @ID int

    AS

    DELETE FROM mytable WHERE ID = @ID

    Inside sp_common, you have

    @ID int,
    @desc varchar(3000),
    @submit_by varchar(50),
    @submit_by varchar(50)

    AS

    INSERT INTO mytable (id, desc, submitby, submitdt, status)
    VALUES (@ID, @desc, @submit_by, @submit_dt, ‘N’)

    return 1

    If sp_update and sp_delete need sp_common, just add one more line (if there is no return value).

    Inside sp_update, add the line in bold

    UPDATE mytable SET status ‘N’

    EXEC sp_common

    Note: You can use either EXEC or EXECUTE. They are the same. But EXEC() is a different thing.

    If there is return value, then inside sp_update, add a declaration line and edit the existing execution line (in bold):

    DECLARE @status int

    UPDATE mytable SET status ‘N’

    EXEC @status = sp_common

    Categories
    SQL Server 2000

    The service did not start due to a logon failure

    When I go to SQL Server Enterprise Manager to create a New SQL Server Registration for localhost, I got an error message saying “The service did not start due to a logon failure“.

    So, I copy this error message and google it. And quickly I found the solution. The problem is I have changed my network password and I didn’t update it at MSSQLSERVER. To do so, I went to Start > Settings > Control Panel > Administrative Tools > Services > MSSQLSERVER.

    Then, I right click to go to Properties. At the Log on tab, I changed the password. Then, I went to SQL Server Service Manager to start it. I managed to start it. Then, I went back to SQL Server Enterprise Manager to create a New SQL Server Registration for localhost and it works!

    Categories
    ASP.NET 1.1 VB.NET Visual Studio 2003

    Some difficulties of learning ASP.NET

    Difficulties 1: Tools
    Say, in your company, you are using Visual Studio 2003 and in order to improve your skill, you want to put give more time to learn it at home. But, the problem is you do not have Visual Studio 2003 at home. You went to check and realize that there is one which is free called Visual Web Developer 2005, but it is for ASP.NET 2.0 only.

    Solution: Not found yet. (I think I’ll just learn ASP.NET 2.0 at home while in office, I will learn ASP.NET 1.1)

    Side-point about ASP.NET framework and Visual Studio IDE:

    IDE  Free light version IDE
    Visual Studio .NET 2002Requires 1.0 ASP.NET Framework *Supports 1.0 ASP.NET Framework onlyNot available
    Visual Studio .NET 2003Requires 1.1 ASP.NET FrameworkSupports 1.1 ASP.NET Framework onlyNot available
    Visual Studio .NET 2005Requires 2.0 ASP.NET FrameworkSupports 2.0 ASP.NET Framework onlyVisual Web Developer 2005 Express Edition
    Visual Studio 2008Requires 3.5 ASP.NET FrameworkSupports 2.0, 3.0, 3.5 ASP.NET Framework (2.0 and 3.0 ASP.NET Framework will automatically be installed)Visual Web Developer 2008 Express Edition **
    * During the earliest version of ASP.NET, in some places, you might not see the version number. Instead they just put ASP.NET. But this is actually refering to ASP.NET 1.0 version.

    ** You might bound into different terms like Visual Studio Express Edition instead of Visual Web Developer 2008 Express Edition. This is my understanding. An analogy to explain the word “Studio” is by using Microsoft Office. Under Microsoft Office, there are Microsoft Word, Microsoft Excel and Microsoft PowerPoint. This “Studio” is like Microsoft Office. So, to be more clear and organized, Visual Studio Express Edition consists of:

  • Visual Web Developer 2008 Express Edition
  • Visual Basic (.NET) 2008 Express Edition
  • Visual C# 2008 Express Edition
  • Visual C++ 2008 Express Edition
  • SQL Server 2008 Express Edition
  • Difficulties 2: Terms
    (You already gotten some confusing terms from above.)

    Certains terms in ASP.NET could be quite confusing. And seems like those to come out with this terms, do not realize this problem. Otherwise, there will be more articles and diagrams to clear up this confusion.

    Example: ASP.NET vs .NET Framework vs ASP.NET Framework vs .NET? For my knowledge right know, they all are refering to the same thing.

    More examples: dataset, datagrid, gridview, dataadapter, datareader, datatable, dataview, detailsview, datalist, formview and repeater. Some terms remain the same from .NET 1.1 to .NET 2.0, but some are new terms from .NET 2.0.

    Solution: The second diagram at the bottom at this page helps to clear up some of the above terms.


    Lastly, please correct me if I got any inaccurate information here. Thanks in advance.

    References

  • http://en.wikipedia.org/wiki/.NET_Framework
  • http://en.wikipedia.org/wiki/Microsoft_Visual_Studio
  • http://en.wikipedia.org/wiki/Microsoft_Visual_Studio_Express
  • Categories
    Software Engineering

    Server Application Error

    Don’t know why suddenly when I run an ASP file from my local IIS, I got an error message saying

    Server Application Error

    The server has encountered an error while loading an application during the processing of your request. Please refer to the event log for more detail information. Please contact the server administrator for assistance.

    So, I googled about it and tried out one of the solutions suggested. I went to DOS command and type iisreset.

    IISRESET
    Then, I went back to my IIS to browse the ASP file and it works now!