Bahramov’s Personal Blog

The following T-SQL statements will print the logical name and physical location of system database files:

USE master;
GO
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'master')

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb')

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'model')

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'msdb')

Result:

Today Microsoft Released Service Pack 1 for SQL Server 2008

Some of the changes include:

• SQL Server 2008 SP1 has made it easier and more efficient to deploy and manage service packs. By adding the ability to Slipstream, SQL Server 2008 and SP1 can be installed simultaneously. This decreases the total time for an installation, including a fewer number of reboots.
• Service Pack Uninstall allows administrators to uninstall the service pack separately from the database release.
• Report Builder 2.0 Click-Once deployment. Up till now, Report Builder 2.0 was only available as a separate install on the client machines. Now .NET’s Click-Once technology is used so that users can run Report Builder without having to install it first through a setup file locally.
• In addition to the a roll-up of previous cumulative updates, SP1 includes quick fix engineering updates and minor fixes in response to requests reported through the SQL Server community. There are no new feature updates in this service pack.

Read more…

In this post I will describe how to create a compressed backup of any SQL Server database in Windows. This is especially useful if you have Express or MSDE edition of SQL Server where there’s no SQL Agent. Let’s look a this example:

1. First we will create a folder, where we want to keep or database backups. Let’s call it C:\DbBackup.
2. Now, we’ll create a sql script that will backup our database. I will use Northwind database in this example. The created backup file will have the name Northwind_[current date]. bak Open a Notepad and paste the following T-SQL code:

-- Create backup of NorthWind Database
-- add current date to the file name and
-- save it inside C:\DbBackup folder
--
DECLARE @str NVARCHAR(255)
SET @str=N'C:\DbBackup\Northwind_'+CONVERT(VARCHAR, GETDATE(), 105)+'.bak'
BACKUP DATABASE Northwind TO DISK=@str

3. Save file as NorthwindBackup.sql

Read more…

Error Message:
Msg 16821, Level 11, State 1, Procedure sp_makewebtask, Line 131
SQL Web Assistant: Could not open the output file.
____________________________
Script completed with errors


Description:
This error message appears when you try to call the procedure sp_makewebtask but the output file cannot be opened.

Consequences:
The T-SQL statement can be parsed, but causes the error at runtime.

Resolution:
Errors of the Severity Level 11 are generated by the user and are corrigible by the user. The statement cannot be executed this way. You must specify a valid and accessible path. Also you have to make sure that the Service Logon account for SQL Server has appropriate rights to write to the specified location.

Versions:
All versions of SQL Server.

Example(s):

-- In this example we try to call the sp_makewebtask procedure
-- to output the result to a file on drive A:\. As our machine
-- does not have such a drive, the error is raised.

EXEC sp_makewebtask 'A:\test.html','SELECT * FROM Northwind.dbo.Orders' 

Remarks:

How to change the SQL Server or SQL Server Agent service account without using SQL Enterprise Manager in SQL Server 2000 or SQL Server Configuration Manager in SQL Server 2005:  http://support.microsoft.com/kb/283811