How To Stop Or Start Windows Service Thru SQL Script

This Article, helps to start a windows services thru SQL Script. To control the windows services, we can use SQL Server’s Extended Stored Procedure, xp_cmdshell

Through command prompt, we can control the services by using MS-DOS Commands

NET START - Start the specified network or local service.
NET STOP - Stop the specified network or local service.

To Execute MS-DOS Commands, we can use the Extended SP: xp_cmdshell

The following piece of script will helps to stop/Start a windows service. In this code, I planned to start or stop the Services of SQL Server Analysis Services

declare @CmdLine varchar(1024)
declare @Command varchar(100)
declare @RetInfo varchar(8000)
select @Command = 'net start MSSQLServerOLAPService'
print @Command
exec @RetInfo = master.dbo.xp_cmdshell @Command
print @RetInfo

When you Execute this code, you may face the following error like

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
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.

For security reason, SQL Server blocked access to the Procedure xp_cmdshell


If you face this error, you can enable this feature By following piece of script:

-- To allow advanced options to be changed.

EXEC sp_configure 'show advanced options', 1

GO

-- To update the currently configured value for advanced options.

RECONFIGURE

GO

-- To enable the feature.

EXEC sp_configure 'xp_cmdshell', 1

GO

-- To update the currently configured value for this feature.

RECONFIGURE

GO

Now if you run the previous code, it will start your services.

Hope it helps!

SQL Server 2000 Best Practices Analyzer

Microsoft SQL Server Best Practices Analyzer is a database management tool that lets you verify the implementation of common Best Practices. These best practices typically relate to the usage and administration aspects of SQL Server databases and ensure that your SQL Servers are managed and operated well.

Download bpasetup.msi

Basic Functions Of System Databases - SQL

What are the basic functions of master, msdb, tempdb databases?

Microsoft SQL Server has four system databases: 

Master - The master database records all of the system level information for a SQL Server system. It records all login accounts and all system configuration settings. Master is the database that records the existence of all other databases, including the location of the database files. 

Tempdb - tempdb holds all temporary tables and temporary stored procedures. It also fills any other temporary storage needs such as work tables generated by SQL Server. Tempdb is re-created every time SQL Server is started so the system starts with a clean copy of the database. 

By default, tempdb auto grows as needed while SQL Server is running. If the size defined for tempdb is small, part of your system processing load may be taken up with auto growing tempdb to the size needed to support your workload each time to restart SQL Server. You can avoid this overhead by using ALTER DATABASE to increase the size of tempdb.

Model - The model database is used as the template for all databases created on a system. When a CREATE DATABASE statement is issued, the first part of the database is created by copying in the contents of the model database, and then the remainder of the new database is filled with empty pages. Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system. 

Msdb - The msdb database is used by SQL Server Agent for scheduling alerts and jobs, and recording operators.

Database Replication - SQL

What is Database Replication?

It is a kind of Data Transformation Services and it is responsible for the process of copying/moving the data among the databases which may be in same or different server.

What are the Types of Replication available in SQL?

Replications are classified into following categories based on process it involved.

* Snapshot replication
* Transactional replication
* Merge replication

Snapshot replication:

It will distribute the data exactly from one database to another. It won’t monitor any changes like updating, deletion etc.., it can be used when data are infrequent. . It is often used for browsing data such as price lists, online catalog, or data for decision support where the current data is not required and data is used as read only.

Transactional replication:

Initially, it will start with snapshot replication, and then whenever data changes happen, it will capture the changes and the changes will be propagated to the subscribers.

Merge replication:

Merge replication is bit different than others, it is a 2 way process. This process distributing the data between publisher and subscriber, it allows the publisher and subscriber to update the data while connected or disconnected, and then merging the updates between the sites when they are connected.

Pass - Through Query In SQL

What is a pass-through query?

Microsoft SQL Server sends pass-through queries as un-interpreted query strings to an OLE DB data source. The query must be in a syntax the OLE DB data source will accept. A Transact-SQL statement uses the results from a pass-through query as though it is a regular table reference.

This example uses a pass-through query to retrieve a result set from a Microsoft Access version of the Northwind sample database.

SELECT * FROM
OpenRowset

(

'Microsoft.Jet.OLEDB.4.0', 'c:\northwind.mdb';'admin'; '',

'SELECT CustomerID, CompanyName FROM Customers WHERE Region = ''WA'' '

)

Cursor in SQL

What is Cursor?

Cursor is a SQL Objects and it is having Row by Row Scrolling behavior while processing on the result-set (Set of records). It will act as pointer and referencing to the each row at a time. We can change the reference to other row whenever need.

Types of Cursor in SQL:

Cursor is classified into following types which are Static, Dynamic, Forward-only and static.

Syntax:

DECLARE <Cursor_Name> CURSOR

[LOCAL | GLOBAL]

[FORWARD ONLY | SCROLL]

[STATIC | KEYSET | DYNAMIC | FAST_FORWARD]

[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]

[TYPE_WARNING]

FOR <Selecting Statements>

[FOR UPDATE [OF Column_name [,....N]]]

Sample Cursor Script:

Use AdventureWorks

GO

Declare DBLogCursr cursor

For Select top 10 * from dbo.DatabaseLog

Open DBLogCursr

Fetch Next From DBLogCursr

While @@FETCH_STATUS <> -1

Begin

Fetch Next From DBLogCursr

End

Close DBLogCursr

Deallocate DBLogCursr

GO

Note: Click on the image to see the Maximized View

To know more about cursor, please refer this documentation from

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

Disadvantages of Cursor:

• Whenever fetch the record thru cursor, it will do the network roundtrip between server and client
• Cursor need more temporary memory storage (For IO Operations)
• Some restrictions are in select statement when we use with cursor
• Cursor is not recommendable, because it will leads to performance issue

So Please use the cursor on critical situation. Hope it helps you!

View in SQL Server

What is View in SQL?

• View is a virtual table.
• it won’t occupies any memory space for the data.
• it is referencing to the other table like pointer concept in programming language.
• View definition contains only SELECT statement.
• Views simplify the user perception of the database
• View improves the data security by preventing undesired access
• View faciliates the provision of additional data independence
• View doesn’t allow ORDER BY clause in view definition.

Can You Drop a table, If it has a view?

If the view is created with SCHEMABINDING clause from Table or views then base object can’t delete otherwise you can.

Why SQL Server Doesn’t allow ORDER BY Clause in View definition?
SQL Server excludes an ORDER BY clause from a view to comply with the ANSI SQL-92 standard. Because analyzing the rationale for this standard requires a discussion of the underlying structure of the structured query language (SQL) and the mathematics upon which it is based, also if you use the ORDER BY Clause then it will lead to major performance issue, sothat by default SQL server doesn’t allow this clause in view definition.

If you try to create view with ORDER BY clause it will throw the following error.
Msg 1033, Level 15, State 1, Procedure , Line 2
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

Error Handling in SQL

How will you handle Errors in Sql Stored Procedure?

SQL Provides a system variable named as @@ERROR, If any error occured in query execution, then error code will be set to @@ERROR, if there is no error, by default it holds the 0 value. so based on this value we can handle the error in SQL

INSERT dbo.emp VALUES (@Column2)

IF @@ERROR <>0
BEGIN
PRINT 'Error Occured'
END

The given code will print the following error

Msg 137, Level 15, State 2, Line 1

Must declare the scalar variable "@Column2".

For further info about this variable, have a look at the following link

http://msdn.microsoft.com/en-us/library/aa933181(SQL.80).aspx

How will you raise an error in sql?
RAISERROR - Returns a user-defined error message and sets a system flag to record that an error has occurred. Using RAISERROR, the client can either retrieve an entry from the sysmessages table or build a message dynamically with user-specified severity and state information. After the message is defined it is sent back to the client as a server error message.

Refer RAISERROR

Stored Procedure And Its Types - SQL

A stored procedure is a set of Structured Query Language (SQL) statements that you assign a name to and store in a database in compiled form so that you can share it between a number of programs.

    * They allow modular programming.
    * They allow faster execution.
    * They can reduce network traffic.
    * They can be used as a security mechanism.

SP are classified as Following Types:

    * Temporary Stored Procedures
    * System stored procedures 
    * Automatically Executing Stored Procedures
    * User stored procedure

Temporary Stored Procedures:
SQL Server supports two types of temporary procedures:local and global.

A local temporary procedure is visible only to the connection that created it.

A global temporary procedure is available to all connections. Local temporary procedures are automatically dropped at the end of the current session. Global temporary procedures are dropped at the end of the last session using the procedure. Usually, this is when the session that created the procedure ends. Temporary procedures named with # and ## can be created by any user.

System stored procedures are created and stored in the master database and have the sp_ prefix.(or xp_) System stored procedures can be executed from any database without having to qualify the stored procedure name fully using the database name master. (If any user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.)

Automatically Executing Stored Procedures - One or more stored procedures can execute automatically when SQL Server starts. The stored procedures must be created by the system administrator and executed under the sysadmin fixed server role as a background process. The procedure(s) cannot have any input parameters.

User stored procedure:

It is a Normal Procedure which are all written by user. It will be available in the User Database.

Risk and Health Assessment Program for Microsoft SQL Server – Scoping Tool v1.1

Risk and Health Assessment Program includes all of the scoping tools necessary to prepare and qualify your environment to receive a Risk and Health Assessment Program for Microsoft SQL Server.

Note: This download package is intended for Microsoft Premier Customers Only.

Download SQLRAPScoping.zip

SQL Server Reporting Services (SSRS) Report - Fix for Firefox

When SQL Server Reporting Services (SSRS) Report is viewed in Report viewer control in Firefox Browser, it won't display properly. But the same report displays properly in IE.

Have a look at the following sample report in Firefox and IE.

Reports in Firefox:

In Firefox, report is not displayed in full size, instead of that, it displays minimal size, but IE shows proper size

Reports in IE:

Reason is, Browser displays SSRS report data thru IFrame. In Firefox, the IFRAME's height defaults to a few hundred pixels, so that you can see the top 2 inches of the report. In IE, IFrames automatically fixed to Data height, so it works fine in IE.

To Fix the Issue in Firefox, hope any one the following 2 ways will help us.

Step1:

1. Go to the following Location, where SQL Server is installed
..\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\Pages

2. Open ReportViewer.aspx File

3. add the style property marked in bold and blue color, Then try this
style="display:table; margin: 0px; overflow: hidden" ID="ReportViewerControl" runat="server" />

If not Helps the first step, then try the step 2.

Step2:

ADD the following code to the ReportingServices.css file (by default, it's found in "C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportManager\Styles\"):

.DocMapAndReportFrame{ min-height: 860px;}

If you want Width also, then use this code

.DocMapAndReportFrame
{
min-height: 860px;
min-width: 1000px;
}

Put this code at bottom of the ReportingServices.css file. After you added this code, Close your browser and clear your cookies then try again

Find All Tables and its Count from a Database

The Following Query Helps you to list out all the table available in a database along with it’s(table’s) count

select object_name(id) as Table_Name, rowcnt as Rows_count

from sysindexes WHERE id in (select OBJECT_ID(table_name) from information_schema.tables) AND

indid < 2

Have a look at the sample result of this Query:

SQL Server 2000 Driver for JDBC Service Pack 3

Microsoft SQL Server has been continually improving its Java connectivity support. As such we highly recommend you work with the latest version of our JDBC driver. The driver version i.e. SQL Server 2000 JDBC driver, that you are looking for is no longer available for download (please note that the support for the this driver will officially end on July 9, 2010). To download the latest JDBC drivers and additional information see:

• Microsoft SQL Server JDBC Driver Download Page


• SQL Server CSS Support Page