Category Archives: SQL Server 2008

Everything that is related to SQL Server 2008

A week far away…24 HOP!

I just have an email with notice that 24 Hours Of PASS, is a week far away! Once again great sessions with tracks about DBA, BI, DB Dev, and Porfessional Development. Once again the presenters will be the top – notch experts and SQL Server MVPs with strong experience and I think that we will have many things to refresh and learn again, also learn something new from them.

You can check the sessions listing by tracks >>> http://tinyurl.com/2w4bkkl

To register for the any session that you are interested click >>> http://tinyurl.com/2uvemqtis better that you can pick up all sessions if you want, this is the great possibility that you have in 24 HOP!

Have a nice stay with next 24 HOP!

A great free tool for SQL Server…

Another free tool for SQL Server that supports SQL Server 2008/2005/2000, comes from xSQL Software, the Script Executor Commnity Edition, that provides for transactional execution of T-SQL scripts, it is a small application, totally free to use and very powerful for the DBAs and also Developers that allows you to run multiple T-SQL scripts. It runs on Windows OS, it is client – server, it has .Net framework 2.0 installed.

There is some nice features of this small and very powerful tool:

  • Execute multiple T-SQL scripts at once
  • Support different transactional execution modes
  • Robust scripts execution error handling
  • Command line options for batch execution
  • Simple and intuitive interface

For more details and free to download, you can check it here

~~~Have a nice SQLing

Getting Started with SQL Azure (doc)

Getting Started with SQL Azure – Very nice & short document about SQL Azure beginning process. This is the official document that is published from Microsoft, this document provides guidelines on how to sign up for SQL Azure, it has 23 pages and illustrated with examples and figures on how to start with SQL Azure server and databases.

Document details:

Author: Selcin Turkarslan

Technical Reviewr: David Robinson & Lubor Kollar

Published: July 2010.

For more info and download check here

~~~Stay Tuned!

Stop’n’Start SQL Services via command prompt (cmd)

Using the command prompt for stopping and starting services is a very nice possibility and very fast! We know that SQL Server services must never stop, especially the SQL Server core engine service. During the system maintenance sometimes we need to restart the processes and it can be done also with cmd, but at the first we must stop them then start them again, and the restart process will happened! The correct way how to do it with cmd is:

NET STOP <ServiceName>
NET START <ServiceName>

Usually, the name of the service can be the default name of the service like MSSQLServer or can be a named service like MSSQL$InstancedName. However if you want to know the exact what you are trying to do, you can check the service name from regedit @ this location:

HKEY_LOCAL_MACHINE – SYSTEM – CurrentControlSet – services – MSSQLServer

The list of the available SQL Server services after successful install of the SQL Server with all services are:

  • Core engine  = MSSQLSERVER (default instance) or MSSQL$InstanceName (Instance Name)
  • SQL Agent = SQLSERVERAGENT (default instance) or SQLAGENT$InstanceName (Instance Name)
  • SSRS = ReportServer
  • SSIS = MsDtsServer100 (SQL Server 2008), MsDtsServer (SQL Server 2005)
  • SSAS = MSSQLServerOLAPService
  • SQL Browser = SQLBROWSER
  • Full-Text = MSSQLFDLauncher
  • SQL Server VSS Writer = SQLWRITER


An example:

Restarting the core engine of the SQL Server:

NET STOP MSSQLAGENT
NET STOP MSSQLSERVER

then

NET START MSSQLSERVER
NET START MSSQLAGENT

In the given example above, I have stop the SQL Agent service name first, why!? – the reason is that the SQL Server Agent Service si connected with core SQL Server Service. You can see, if you restart the SQL Server while the SQL Agent Server is running you will prompted to restart also the SQL Agent Service! So, in this case you should stop the SQL Agent first then SQL Server, then starting them again!

SQL Express Edition:

In SQL Server Express we have the same situation like Named Instance of the SQL Server, so if you want to restart the service you must do like this:

NET STOP MSSQL$SQLEXPRESS
NET START MSSQL$SQLEXPRESS

~~~ Stay Tuned!

SQL Server 2008 R2 feature pack

Microsoft, has published things that can be very useful, in this case for the SQL Server users. They announced the feature pack for SQL Server 2008 R2, where you can find a lot of stuff and big collection of stand-alone packages which provide additional value for SQL Server 2008 R2. It includes the latest version of:

  • Redistributable components for Microsoft SQL Server 2008 R2.
  • Add-on providers for Microsoft SQL Server 2008 R2.

There it is the list with some useful files in this package:

  • Microsoft SQL Server PowerPivot for Microsoft® Excel
  • Microsoft SQL Server Report Builder 3.0 for Microsoft SQL Server 2008 R2
  • Microsoft SQL Server 2008 R2 Reporting Services Add-in for Microsoft SharePoint Technologies 2010
  • Microsoft SQL Server 2008 R2 Policies
  • Microsoft SQL Server JDBC Driver 3.0
  • Microsoft System CLR Types for SQL Server 2008 R2
  • Microsoft SQL Server 2008 R2 Upgrade Advisor
  • Microsoft SQL Server 2008 R2 Native Client
  • Microsoft Windows PowerShell Extensions for SQL Server 2008 R2
  • Microsoft SQL Server Driver for PHP 1.1
  • etc.

For the full list and other details, including downloads, you can check here.

~~~ Stay Tuned!

One more Fix Database Role in SQL Server

Fixed Database Rols are: db_ddladmin, db_owner, db_accessadmin, db_securityadmin, db_backupoperator, db_datawriter, db_datareader, db_denydatawriter, db_denydatareader + one more that is dbm_monitor.

This is not a new Fixed Database Role, but you can use it when you establish the database mirroring on your SQL Server. It will be available in msdb sys database, at the moment when the first database is registered in Database Mirroring Monitor. This database role has no users asiggned, so the sys admin must assign the user to this role.

When you are member of the dbm_monitor database role, you are granted to view any database (mirroring), also when you connected to the server you will have limited permissions on the instance where you would be able to view the most recent status in Database Mirroring Monitor.

~~~ 
Stay Tuned!

SQL Azure and T-SQL

SQL Server Azure database supports the T-SQL, but no at all! For the SQL Server Azure and T-SQL are available three list (Supported, Partially Supported & Unsupported – statements):

Supported T-SQL statements.

  • ALTER (SCHEMA, VIEW, ROLE)
  • BEGIN…END
  • CAST & CONVERT
  • DROP (SCHEMA, ROLE, LOGIN, USER, VIEW, STATISTICS, etc)
  • ORDER BY
  • OUPUT
  • RAISERROR
  • SET (DATEFIRST, DATEFORMAT etc…)
    etc.
    Check for the full list here.

Partially supported T-SQL statements.

  • ALTER (DATABASE, FUNCTION, INDEX, PROCEDURE, etc)
  • CREATE (TABLE, INDEX, TRIGGER etc)
  • GRANT (Database Persmissions, Database Principal Permissions etc)
  • DROP (DATABASE, INDEX, TABLE, TRIGGER, etc)
    etc.
    Check for the full list and further details here.

Unsupported T-SQL statements.

  • BACKUP & RESTORE
  • BULK INSERT
  • RECONFIGURE
  • DATABASEPROPERTY
  • DBCC (HELP, CHECKDB, CHECKTABLE, SHRINKFILE etc)
  • RECONFIGURE
    etc.
    Check for the full list and other details here.

Let’s take a look with an example:

~You cannot use:
SELECT * INTO NewTableName
FROM SourceTable

to create direct new table with T-SQL statement.

In this case you should create the NewTableName with the same structure as source table:

CREATE TABLE NewTableName
(
Fields datatypes
… same structure as source_table

);
GO

then

INSERT INTO NewTableName (Fields…)
SELECT Fields
FROM SourceTable

* Check for more samples.

~~~
Stay Tuned!