SQLNovice

Where to find how many CPU's there are in your server?

2010-09-14T11:46:00.002+02:00

Some simple hints:

for version 2000+: exec xp_msver 'ProcessorCount'

for version 2005+ : sys.dm_os_sys_info

Flaw in sp_helpdb system stored procedure

2010-09-10T10:15:00.004+02:00

Well, it is the first time ever for me to encounter this problem, so I documented it here with 3external links which explain the cause and solution pertty well already: link1, link2, link3.

The script to quickly locate databases without owners is:

select name from master..sysdatabases
where suer_sname(sid) is null
go

Watch out function isnumeric!

2010-08-10T17:32:00.003+02:00

You might get hit hard when you try to use it in some conditional clauses.

select isnumeric('.') -->1

select isnumeric(',') -->1

select isnumeric('1,1,1') -->1

select isnumeric('1,1.1') -->1

select isnumeric('1.1.1') -->0

select isnumeric('1D1') -->1

select isnumeric('1d1') -->1

select isnumeric('1E1') -->1

select isnumeric('1d1') -->1

Removing full duplicates by using row_number() function and common table expression

2010-03-24T13:54:00.005+01:00

This blog article is inspired by another very informative one which explains really well the meaning of the new row_number() function introduced since SQL2005. Since the original article did not give a full answer to how to delete multiple fully duplicated rows in a table, I am putting my code here for the test.

The good thing about row_number() function is that it can be based on partition of columns. New row number count starts for each partition of full duplicates and those rows with row number bigger than 1 can be easily spotted and removed. Here is the example that explains everything I am talking about here.

First of all, create a table in a test db

USE [test_db]
GO

CREATE TABLE [dbo].[Employee](
[EMPID] [int] NULL,
[FNAME] [varchar](50) NULL,
[LNAME] [varchar](50) NULL
) ON [PRIMARY]

Then let's insert many duplicated rows:

INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (2021110, 'MICHAEL', 'POLAND')
GO
INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (2021110, 'MICHAEL', 'POLAND')
GO
INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (2021115, 'JIM', 'KENNEDY')
GO
INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (2021115, 'JIM', 'KENNEDY')
GO
INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (2021115, 'JIM', 'KENNEDY')
GO
INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (2121000, 'JAMES', 'SMITH')
GO
INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (2121000, 'JAMES', 'SMITH')
GO
INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (2011111, 'ADAM', 'ACKERMAN')
GO
INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (3015670, 'MARTHA', 'LEDERER')
GO
INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (3015670, 'MARTHA', 'LEDERER')
GO
INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (3015670, 'MARTHA', 'LEDERER')
GO
INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (3015670, 'MARTHA', 'LEDERER')
GO
INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (1021710, 'MARIAH', 'MANDEZ')
GO

If you selete everything from this table now, the following should be there:

EMPID FNAME LNAME
1021710 MARIAH MANDEZ
2011111 ADAM ACKERMAN
2021110 MICHAEL POLAND
2021110 MICHAEL POLAND
2021115 JIM KENNEDY
2021115 JIM KENNEDY
2021115 JIM KENNEDY
2121000 JAMES SMITH
2121000 JAMES SMITH
3015670 MARTHA LEDERER
3015670 MARTHA LEDERER
3015670 MARTHA LEDERER
3015670 MARTHA LEDERER

And here comes the de-dup script with the help of common table expression:

with temp_employee as
( select row_number() over (partition by EMPID, FNAME, LNAME order by EMPID) as rowid, * from EMPLOYEE)
delete temp_employee where rowid > 1

Hope this article is illustrative enough.

Several tips about query performance optimization

2010-03-18T10:34:00.003+01:00

The original article is here.

The 4 following tips are particularly helpful and can be immediately put into daily use:

Views and Stored Procedures

We can avoid using heavy duty queries by using stored procedures and views. Stored procedures are commonly used flexible database programming objects. Stored procedures are compiled once, so it will not create the execution plan every time, thus reducing your network traffic. Views give more security. For example, you have a table containing sensitive data; you might wish to hide those columns from certain users, you can use views.

Table Variables

Avoid temporary tables if you don’t need transactions on that table. In that case you can use Table variables. When you create the temporary table, it will create the physical table in the tempdb database. However, if you create a table variable, it only resides in the memory. It will be much faster than temporary tables, and table variables reduce the recompilation of the code.

Notes: a good practice is to use table variable where you need a small table to operate on, loading big table variable into memory simply erodes its performance gain by interfering with other in-memory activities of SQL Server.

Row Count

If you’re in the situation to use the count(*), then use the following query. This is way faster than function count(), especially for huge tables with millions of rows

SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND (indid < 2)

Set No Count On

If the No Count is off in your stored procedures, then the number of rows affected value will be returned. It will increase the network traffic. Set No Count should be On in your stored procedures. Hence the number of rows affected will not return.

How to move tempdb?

2010-03-15T11:23:00.003+01:00

Today I got a call from our Window system admin that our production server is running low on C: drive. The cause turns out that the tempdb was bloated up and ate a huge chunk of disk space on our system drive. Since we have a separate drive F with plenty of free space, the task became how to move our tempdb from C: to F:. Here is an article that sheds much light on this solution. I am giving a more detailed action path here.

First of all, locate tempdb's files:

use tempdb
go

select * from sys.sysfiles
go

Then you'll have something like this:

fileid groupid size maxsize growth status perf name filename
1 1 1024 -1 10 1048578 0 tempdev c:\temp\tempdb.mdf
2 0 64 -1 10 1048642 0 templog c:\temp\templog.ldf

And the following script will move the database files to the designated locations:

alter database tempdb
modify file (name = tempdev, filename = 'F:\SQLDATA\tempdb.mdf')
go

alter database tempdb
modify file (name = templog, filename = 'F:\SQLDATA\templog.ldf')
go

But you'll also get a message saying:

The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.

The final step will be to restart SQL Sever service either from the SSMS directly or through windows services. Probably it is a better idea to do it outside working hours.

DAC connection problem from SSMS

2010-03-10T11:29:00.004+01:00

Here is a common error when using SQL Server's Dedicated Administrator Connection:

'Dedicated administrator connections are not supported. (ObjectExplorer)'

A quick answer is to NOT use 'new query' button, but instead use 'new database engine query' button.

Here is a more detailed article regarding this issue.

How to grant viewing privileges of database diagrams to a user?

2010-03-02T16:53:00.002+01:00

This is a biting question that I just bumped into today, it turns out MS has not supplied with established mechanisms to support distributing viewing privileges for database diagrams at least for SQL 2005 or 2008. According to BOL:

"Although any user with access to a database can create a diagram once it
has been created, the only users who can see it are the diagram's creator and
any member of the db_owner role."

Drop User: The database principal owns a schema in the database, and cannot be dropped

2010-02-26T10:41:00.002+01:00

This is a common error when dropping a user from a database when it owns some schema. Usually you can use SSMS UI to uncheck the user from the owned schema.

After locating the owned schema by the user, the correct sql statement to change the owner of a schema is:

alter authorization on schema::schema_name to user_name

How to reset a remote desktop session when you can't remote desktop to the target server

2010-02-18T15:37:00.001+01:00

A refusing remote server can be annoying when you have ever tried to connect to it because of some urgent issue.

Solution is in this link.

Understanding the Identity column

2010-02-18T11:24:00.003+01:00

Here is the original article.

A common problem encountered by most DBA's is to insert values into a table with an identity column and "set identity_insert on table_name on" should be used.

Three quick ways to show the last identity value that was just inserted:

select SCOPE_IDENTITY()

select @@IDENTITY

select ident_current('table_name')

The differences between them are stated in the above linked article.

Another important command for reseeding the identity column is:

DBCC CHECKIDENT('table_name', RESEED, 1)

Check available disk space in all the database files

2010-01-29T16:07:00.002+01:00

Here is the script:

use [db_name]
go

select name, size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') as int)/128.0 as AvailableSpaceInMB
from sys.database_files
go

Knowing this info is a good guide for file shrinking operations.

The link between bcp utility and the bulk insert statement

2010-01-29T12:06:00.002+01:00

Just tried to find out the real difference between bcp utility and bulk insert statement and here is the article from Microsoft's official site.

Quoted too: "The BULK INSERT statement allows you to bulk copy data to an instance of SQL Server using the functionality of the bcp utility with a Transact-SQL statement, rather than from the command prompt."

Therefore, it seems the whole "bulk insert" statement is purely another way of "bcp in" command.

The use of object varaibles, resultset and foreach loop in SSIS

2010-01-25T11:05:00.003+01:00

I've recently been involved in documenting an SSIS package made by our lead developer and there is a foreach loop that he used to loop through a resultest from a query. An object variable is used to hold the resultset temporarily and the foreach loops through this result to repeat some actions. Since this trick is pretty new to me, I hence document a very good tutorial here to keep a memo.

A very good article about the concept of filegroup

2010-01-12T11:36:00.001+01:00

Here is the link.

Script to see index usage stats

2010-01-12T10:20:00.003+01:00

SELECT object_name(s.object_id) AS ObjectName

, s.object_id

, i.name as IndexName

, i.index_id

, user_seeks

, user_scans

, user_lookups

, user_updates

FROM sys.dm_db_index_usage_stats s

INNER JOIN sys.indexes i

ON i.object_id = s.object_id

AND i.index_id = s.index_id

WHERE database_id = db_id ()

AND objectproperty(s.object_id,'IsUserTable') = 1

ORDER BY (user_seeks + user_scans + user_lookups + user_updates) ASC

-- Possible bad Indexes (writes > reads)

DECLARE @dbid int

SELECT @dbid = db_id()

SELECT 'Table Name' = object_name(s.object_id), 'Index Name' =i.name, i.index_id,

'Total Writes' = user_updates, 'Total Reads' = user_seeks + user_scans + user_lookups,

'Difference' = user_updates - (user_seeks + user_scans + user_lookups)

FROM sys.dm_db_index_usage_stats AS s

INNER JOIN sys.indexes AS i

ON s.object_id = i.object_id

AND i.index_id = s.index_id

WHERE objectproperty(s.object_id,'IsUserTable') = 1

AND s.database_id = @dbid

AND user_updates > (user_seeks + user_scans + user_lookups)

ORDER BY 'Difference' DESC, 'Total Writes' DESC, 'Total Reads' ASC

Case sensitive search query

2010-01-06T15:36:00.002+01:00

To view the full list of all collocation ino, you can run the following query:

SELECT * FROM fn_helpcollations()
go

For the real technique of enable case-sensitive search query, carry on and read this.

bcp utility quick ref

2010-01-06T13:10:00.002+01:00

I found the -c argument is interesting for dumping data out of database and then being open in Excel easily.

The original post is here.

DBCC checkdb primer

2010-01-06T12:19:00.001+01:00

This is another very good article.

You can add statistics on columns that don't have statistics in order to boost query performance.

2010-01-05T15:25:00.002+01:00

This trick is totally something new to me. Please read further here.

How to script SQL Server 2008 diagrams and restore them

2009-12-15T14:39:00.001+01:00

I got a recent request of restoring diagrams from one database to another and found an awesome solution in here.

What SQL Statements Are Currently Executing?

2009-12-13T14:26:00.002+01:00

This is a fundamentally useful script.

How to grant execute permission of all stored procedures to a user?

2009-12-01T10:32:00.003+01:00

One recent request from our testers is that they need to execute all stored procedures on the testing database. And the simplest solution is to create a database role which is granted the execute privilege and add the testers' database users into this role.

Here it goes the script:

use [testing_db]
go
create role [testers]
go

grant exec to [testers]
go

exec sp_addrolemember N'testers', N'tester_db_user_account'
go

By running the above script, not only the execute rights of stored procedures, but also those of scalar functions are granted to the user.

Here are the two links that you might find interesting as well regarding this topic:
1 and 2.

Once you can't remote desktop your server, you can try to reset the session like this

2009-11-25T15:12:00.001+01:00

See the original post here.

Command to open SQL Server Configuration Manager

2009-10-07T15:49:00.003+02:00

I believe most of us already know to type "ssms" (for 2008) or "sqlwb" (for 2005) or "isqlw" (for 2000) in the "Run" command box from the start menu to open SSMS studio or Query Analyzer.

Here is one more, to open SQL Server Configuration Manager, you need to type "SQLServerManager10.msc" to get there. If you want to see the list of all the windows services, "services.msc" is the command you need.

Error: The partner transaction manager has disabled its support for remote/network transactions

2009-10-06T10:13:00.009+02:00

I bumped into this error message when our developers created a trigger on a Windows 2003 server that updates a table in a linked server (a Windows 2008 server). The second part of the error message is: "linked server was unable to begin a distributed transaction". I put the whole error message text here purely for the sake of Google keywords and the essential issue behind is actually how to enable the distributed transaction function on a Windows server.

If you Google the title, there is a useful link from MSDN, which provides the following solution:

First verify the "Distributed Transaction Coordinator" Service is
running on both database server computer and client computers
1. Go to "Administrative Tools > Services"
2. Turn on the "Distributed Transaction Coordinator" Service if it is not running

If it is running and client application is not on the same computer as
the database server, on the computer running database server
1. Go to "Administrative Tools > Component Services"
2. On the left navigation tree, go to "Component Services > Computers
> My Computer" (you may need to double click and wait as some nodes
need time to expand)
3. Right click on "My Computer", select "Properties"
4. Select "MSDTC" tab
5. Click "Security Configuration"
6. Make sure you check "Network DTC Access", "Allow Remote Client",
"Allow Inbound/Outbound", "Enable TIP" (Some option may not be
necessary, have a try to get your configuration)
7. The service will restart
8. BUT YOU MAY NEED TO REBOOT YOUR SERVER IF IT STILL DOESN'T WORK(In my case, we didn't reboot the server)

On your client computer use the same above procedure to open the
"Security Configuration" setting, make sure you check "Network DTC
Access", "Allow Inbound/Outbound" option, restart service and computer
if necessary.

On you SQL server service manager, click "Service" dropdown, select
"Distribute Transaction Coordinator", it should be also running on
your server computer.

Basically speaking, to enable distributed transaction for linked server, besides making sure the Distributed Transaction Coordinator service is up and running, you have to enable network DTC access. And there is a very good article (for Windows server 2003) from Microsoft's knowledge base officially documenting exactly the same set of actions.

Here are the screen shots you have to encounter during the settings for Windows server 2003:

Component Services:

My Computer Properties

Security Configuration

If you tried to enable network DTC access for Windows server 2008 (or Windows vista if you are fan of it), Microsoft has already provided another article in its knowledge base for your viewing pleasure as well. The settings are almost the same as those for Windows Server 2003, the slight change lies in where you can find the "Security Configuration" dialog box; in Windows Server 2008, it is in the "Local DTC" properties instead of "My Computer" properties in the component services MMC.

One screen shot speaks more than 1000 words.

Good tutorial script for full recovery and transaction log backup

2009-10-01T13:24:00.002+02:00

--Creating the database for the demo
create database DemoDB
go

--Make sure that the database is in full recovery model
alter database DemoDB set recovery full
go

--Making the first full backup. From now on I can use log backups
backup database DemoDB to disk = 'c:\DemoDB_1.bak' with init
go

--Creating a new object in the database
use DemoDB
go
create table DemoTable (i int)
go

--backup the log
backup log DemoDB to disk = 'c:\DemoDB_1.trn' with init
go

--Inserting a record that doesn't exist in all the backups that were taken into the table
insert into DemoTable (i) values (1)
go

--Creating a second full backup. I won't be using this backup when I'll restore the database
backup database DemoDB to disk = 'c:\DemoDB_2.bak' with init
go

insert into DemoTable (i) values (2)
go

--Creating the second log backup that includes the second record in DemoTable
backup log DemoDB to disk = 'c:\DemoDB_2.trn' with init
go

--Dropping the table (to mimic an error)
drop table DemoTable
go

--Now I'll restore the database. I'll use only the first full backup
--with the 2 log backups. This proves that I don't need to use the
--copy_only switch, if I have a full backup with all log backups that
--were taken since then even if another full backup was taken between
--the log backups
use master
go
restore database DemoDB from disk = 'C:\DemoDB_1.bak' with norecovery, replace
restore log DemoDB from disk = 'c:\DemoDB_1.trn' with norecovery
restore log DemoDB from disk = 'c:\DemoDB_2.trn' with recovery
go

Use DemoDB
go
--Notice that I have the table with 2 records
select * from DemoTable
go

--cleanup
use master
go
drop database DemoDB

How to know the logical files' names from a database backup?

2009-10-01T10:54:00.003+02:00

To use the "move" clause in a restore script, we need first to know the logical files inside the backup. The following sql command can do the trick:

restore filelistonly
from disk = N'the backup location'

How to export from Excel to a CSV file with double quotes on each field

2009-09-10T14:40:00.003+02:00

The default "save as CSV" option from Excel does not include any quotes on the fields, therefore a macro is needed in this case:

Sub CSVFile()

Dim SrcRg As Range
Dim CurrRow As Range
Dim CurrCell As Range
Dim CurrTextStr As String
Dim ListSep As String
Dim FName As Variant
FName = Application.GetSaveAsFilename("", "CSV File (*.csv), *.csv")

If FName <> False Then
ListSep = Application.International(xlListSeparator)
If Selection.Cells.Count > 1 Then
Set SrcRg = Selection
Else
Set SrcRg = ActiveSheet.UsedRange
End If
Open FName For Output As #1
For Each CurrRow In SrcRg.Rows
CurrTextStr = ""
For Each CurrCell In CurrRow.Cells
CurrTextStr = CurrTextStr & """" & CurrCell.Value & """" & ListSep
Next
While Right(CurrTextStr, 1) = ListSep
CurrTextStr = Left(CurrTextStr, Len(CurrTextStr) - 1)
Wend
Print #1, CurrTextStr
Next
Close #1
End If
End Sub

The original post can be retrieved here.

The two most important differences between temp table and table variable

2009-09-07T16:45:00.004+02:00

Since this can be one of the mostly asked interview questions, I better have the answers here instead of doing another round of Googling.

1. table variable does not support transaction while temp table does
2. table variable does not support constraints while temp table does

A full expiation is here.

SourceSafe plug-in setting for Visual Studio

2009-09-07T13:06:00.003+02:00

This might sound very stupid, but it took quite some time for me to figure out why my Visual Studio failed to connect to our SourceSafe database on which it turns out that the internet connection is switched off by default(On Server, when installing VSS, select Custom Install and make sure you select the "Server Components" or at least the HTTP Remote Access component; they are not installed in Default installation confuguration). While the Visual Studio by default connects to internet SSL connection based database, my multiple attempts failed without providing me too much of a clue since a VNC and a URL in a LAN setting do not vary from each other too much. So after checking an interesting article, the solution is plain simple as shown in the below screen shot in Visual Studio's options.

How to bring a restored database from the "restoring" state back to operational

2009-08-28T10:38:00.004+02:00

If you are trying to find a solution to bring your database out of the "restoring" state after a restore, this is the right place you should be.

When you use restore statement the with the "with norecovery" option, after the restore, the database will be put into a 'restoring' state. To bring it back online and operational, you need the follow script:

restore database db_name with recovery

To avoid yourself from this situation, always run the restore with the "with recovery" option.

More info about recovery state after a restore operation can be found at the "Restore Database (Options Page)" at MSDN.

Script to monitor backup and restore progress

2009-08-26T14:53:00.002+02:00

I have been recently busy with tuning the restore performance on a testing server. The original disk array was configure to be RAID5 and the restore process was super slow which cost 16 hours to restore a 300 GB database backed up from prod. I've also double-checked CPU and memory stats, none is believed to be the bottleneck. So we decided to downgrade RAID5 to RAID0 since our testing environment doesn't require fault tolerance.

Right now, I am testing the restore again to see if the RAID0 can actually bring the expected performance benefits. However, the restore script I am using was accidentally set to have the argument with "stats = 100" (should've used stats = 1) which will not report progress info until the full restore is finished. So the questions becomes how to monitor a restore process when it is running in the background.

Luckily enough, there are at least two scripts that can be found through Google to monitor the progress of a backup/restore process. The first one is hidden in a thread in SQLServerCentral's forum and the other one is here.

Needless to say, I would like to post them in this post as well.

The first script can only monitor "restore" progress:

SELECT
A.name,
B.total_elapsed_time/60000 AS [Running Time in minutes],
B.estimated_completion_time/60000 AS [Remaining in minutes],
B.percent_complete as [% of completion],
(SELECT TEXT FROM sys.dm_exec_sql_text(B.sql_handle))AS Command
FROM
MASTER.sys.sysdatabases A inner join sys.dm_exec_requests B
on A.dbid = B.database_id
WHERE
B.command LIKE '%RESTORE%'
order by B.percent_complete desc,B.total_elapsed_time/60000 desc

The second script give more general info about both "restore" and "backup".

SELECT command,

s.text,

start_time,

percent_complete,

CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '

+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '

+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,

CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '

+ CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '

+ CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,

dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time

FROM sys.dm_exec_requests r

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s

WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')

How to rename a database?

2009-08-24T15:54:00.002+02:00

sp_renamedb is the stored proc that can be used. The problem you might encountered is the exclusive lock might not be assigned properly. The best practice is to set the db to single user mode before changing its name and back to multi-user mode afterwards.

ALTER DATABASE old_db_name
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
SP_RENAMEDB old_db_name, new_db_name
Go
ALTER DATABASE new_db_name
SET MULTI_USER
GO

How to drop all the foreign key constraints in a database

2009-08-13T10:20:00.003+02:00

Sometimes, foreign keys become annoying in a secondary database where reporting is the main purpose while integrity is not. So dropping all the foreign keys may become a user request from time to time.

The keyword to this question is the catalog view "sys.foreignkeys". And you can go to here to read about about how to retrieve all the foreign key constraints from a database and why catalog view is preferred.

Here is the script to delete all the foreign keys from a database.

use db_name
go

declare @fk_name varchar(100)
declare @tb_name varchar(100)
declare @cmd varchar(200)

declare cur_fk cursor
for
select name as ForeignKey_Name, OBJECT_NAME(parent_object_id) as Table_Name from sys.foreign_keys
order by Table_Name

open cur_fk

fetch next from cur_fk into @fk_name, @tb_name
while @@FETCH_STATUS = 0
begin
set @cmd = 'alter table ' + @tb_name + ' drop constraint ' + @fk_name
select @cmd
exec (@cmd)
fetch next from cur_fk into @fk_name, @tb_name
end

close cur_fk
deallocate cur_fk

How to drop all the user stored procedures from a database

2009-08-10T18:08:00.004+02:00

A very informative script is here. But since the original posted script has very annoying line numbers, I would rather re-post the same code here again. And now you can copy and paste it to SSMS very easily. Also something worth mentioning is that the INFORMATION_SCHEMA.ROUTINES view actually contains user defined functions' info, so the SQL command "Drop procedure" will fail if you have any function defined in your target database. Therefore, I changed the following script to use sys.sysobjects view to retrieve the full list of names of user stored procedures.

-- this sets up the test database

-- Drop the database if it already existt

USE master
GO

IF EXISTS (
SELECT name
FROM sys.databases
WHERE name = N'testdb'
)

DROP DATABASE testdb
GO

CREATE DATABASE testdb
GO

USE testdb
GO

CREATE PROC UserStoredProcedure_Sample1
AS
SELECT 'SQL Server rocks'
GO

CREATE PROC UserStoredProcedure_Sample2
AS
SELECT 'SQL Server rocks'
GO

SET NOCOUNT ON
-- to do this we have to use EXEC instead of sp_executesql

-- sp_executesql does not accept a DROP command in the SQL String

DECLARE @UserStoredProcedure VARCHAR(100)
DECLARE @Command VARCHAR(100)

DECLARE UserStoredProcedureCursor CURSOR SCROLL STATIC READ_ONLY FOR
select name from sys.sysobjects where xtype = 'P'

/*
SELECT
SPECIFIC_NAME
FROM
INFORMATION_SCHEMA.ROUTINES
*/

OPEN UserStoredProcedureCursor

FETCH NEXT FROM UserStoredProcedureCursor
INTO @UserStoredProcedure
WHILE (@@FETCH_STATUS = 0) BEGIN
SET @Command = 'DROP PROCEDURE ' + @UserStoredProcedure
-- display; visual check

SELECT @Command
-- when you are ready to execute, uncomment below

EXEC (@Command)

FETCH NEXT FROM UserStoredProcedureCursor

INTO @UserStoredProcedure

END

CLOSE UserStoredProcedureCursor

DEALLOCATE UserStoredProcedureCursor

SET NOCOUNT OFF

Job notification fails to send emails

2009-08-09T21:47:00.006+02:00

This may be another rookie mistake, but I believe it is worth mentioning it here. I have two servers under my administration and one uses maintenance plan to send out emails after a backup operation is done and on the other one I tried to use job notification to send out email to my account when a "move database copies" PowerShell script job is complete. Both servers' email profiles have been set up properly and test emails have been sent successfully too. However, the one with the maintenance plan does send emails successfully every night while the other one with job notification kept silent for quite a few days in a row and I was suspecting the connectivity of the smtp server it uses. But after some research, the reason actually turned out to be a missed configuration check box on the "Alert System" tab of SQL Server Agent Properties. Strangely, on the first server where the maintenance plan is located and used to send out notification emails, this check box is left empty too. So the conclusion is that maintenance plan, which in essence is an SSIS package, does not require this "Enable mail profile" option to be turned on but email-sending jobs do. Also don't forget to restart SQL Server Agent service after the setting is changed.

How to determine whether a file exists or not in PowerShell script?

2009-08-07T20:02:00.003+02:00

This is purely for my personal note, cmdlet "test-path" is the keyword:

if (Test-Path "file_lccation")
{
write-host "the file is there"
}
else
{
Write-Host "the file is missing"
}

PowerShell script to script all tables and stored procedures in a database

2009-08-07T14:52:00.004+02:00

The script to script all the tables is available here.

Here I made my part of scripting all the stored procedures (excluding system stored procedures) from the ubiquitous database "pubs".

#the full path of the file that you want to script the stored procs to
$scriptfile = "C:\temp\StoredProcs.sql"

#if the script file already exists, remove it
if (Test-Path $scriptfile)
{
Remove-Item -Path $scriptfile
}

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null

$MyScripter=new-object ("Microsoft.SqlServer.Management.Smo.Scripter")
$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "localhost"
$procs = $srv.databases["pubs"].StoredProcedures
$MyScripter.Server=$srv

$MyScripter.Options.FileName = $scriptfile
#AppendTofile has to be 'true' in order that all the procs' scripts will be appended at the end
$MyScripter.Options.AppendToFile = "true"

foreach ($proc in $procs)
{
#Assuming that all non-system stored procs have proper naming convention and don't use prefixes like "sp_" or "xp_"
if ( $proc.Name.IndexOf("sp_") -eq -1 -and $proc.Name.IndexOf("xp_") -eq -1 )
{
$MyScripter.Script($proc)|out-null
}
}

SQL Server connection string format for PowerShell scripting

2009-08-07T14:15:00.006+02:00

This might look very primitive, but since I am a newbie to PowerShell scripting, it does no harm to write them down here and reference them in a later time when needed.

For sql server authentication:
"Server=server_name or server_ip;Database=db_name;Integrated Security=False;UID=login_name;Password=password"

For windows authentication:
"Server=server_name or server_ip;Database=db_name;Integrated Security=True"

Another website which is dedicated to connection strings might also come in handy, the link is here.

But shockingly, I can't find the above PowerShell script connection strings on the website.

Where to find the staring time of a sql server database restore?

2009-08-04T16:01:00.004+02:00

I just encountered this problem today. And composed a post on SCC:

Hello everyone

This might sound very silly due to my novice to DB administration work. Yesterday I started restoring a database with a size of 350GB from a 75GB backup on a testing server from 11pm, and this afternoon at 3pm, when I remote-desktoped the server I found the restore has already finished. I then checked the sql server log in the log file viewer but can't see anything has been logged around 11pm yesterday and there are only two records around 6am early this morning saying that "Starting up database db_name" and "The database is marked RESTORING and is in a state that does not allow recovery to run". Also, there is another log around 2:20pm today stating that "Restore is complete on database db_name. The database is now available".

So my question is whether this restore started around 11pm yesterday when I starting running the restore script or it actually happened around 6am this morning? How to determine the starting time of a restore then? Or did I totally miss something really important here? Thanks very much in advance for your advice!

regards,
Ning

Feed-backs came surprisingly quick that the msdb.dbo.restorehistory is the table I need to look at to retrieve the starting time info for all the restore actions against all the databases in the instance. But it still seems strange to me that SQL Server log does not record the staring time of a restore action.

Change instance name of SQL Server

2009-07-27T19:55:00.005+02:00

It turns out that after you've changed the Windows Server's name, the underlying SQL Server's instance name is not changed accordingly, although you can see an instance name same with the Windows Server's is displayed in the SSMS.

Everything else might work fine until you want to set up replication/distribution on this server. An error message like "SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, ‘old_name’. (Replication.Utilities)" is popped right into your eyes.

To see the old instance name, you can use "sp_helpserver" or "select @@servername" and to change the instance name once and for all, the following script has to be executed:

sp_dropserver 'old_name'
go
sp_addserver 'new_name','local'
go

A detailed and better formatted how-to article can be retrieved from here.

Remote desktop resolution problem

2009-07-24T15:41:00.001+02:00

Here is the thing, I use command line short cuts (%windir%\system32\mstsc.exe /v: ) on my desktop to connect to our multiple database server environment and for two days a week, I go to my company and have my laptop docked to a much bigger monitor at the office. So from the bigger screen, the remote session's resolution becomes (automatically) much bigger than my 12 inch laptop's but displays beautifully on the 22 inch LCD screen. But when I come back and start working from home, the RDP session's resolution somehow "remembers" its latest setting and becomes more than my small screen can handle and I have to scroll up and down, from left to right to locate the things I have to work on.

A little bit of research shows that one of mstsc.exe's command line options gives a default full screen setting by using "/f" and I changed all my short cuts on my desktop according to this, and they are all working fine now. So the best practice to use mstsc command line is always put the "/f" argument at the end to ensure the remote resolution is in sync with your local ones in a full screen mode.

IIS logging problem

2009-07-24T11:24:00.000+02:00

Although my official title is DBA, but at a small start-up, an "IT dude" is supposed to be capable of solving all kinds of problems which may be way beyond the database realm. And to please our big boss, I happily accepted a task to investigate why one of our IIS websites fails to generate any logging records in the log file directory. What entails is a significant amount of hours which only leads to a simple and stupid overlook by a previous "IT dude" who actually set up the whole website.

The following screen shot from tab "Web Site" on the IIS site properties is a must-go place for checking all the logging settings for IIS and it surely looks perfect on our server.

However, the other missing check box on the "Home Directory" tab turned out to be the ultimate cause for this site failing to deliver any log files.

Two tricks discovered on Dell's Latitude E4300 laptop

2009-07-22T23:15:00.000+02:00

The first one is pretty straight forward and probably more Dell-related. I just found out that you can turn off battery charging by pressing Fn+F2 which is an awesome feature because I usually prefer to have my battery on all the time in case there is a power outage, but at the same time, I don't wanna make too much over-charging which might cause a shortened battery life. So having the option of stopping charging the battery is definitely something nice to have. Big applause to Dell's engineers.

The second one is how to type in special signs for example the pound sign £ through a US-layout keyboard. The solution is pretty simple although I've spent quite a load of time Googling for it. First of all, turn on the num lock of the numeric keypad by press Fn+F4 (at least on Dell's Latitude E4300, you can find other key combinations on products from other manufacturers); after this, the num lock blue indicator will be lit up on the upper verge of the keyboard. And then, you can type the key stroke combination Alt+Fn+mjol (for the pound sign) which can be looked up in the "Programs->Accessories->System Tools->Character Map"; combination "mjol" from the normal keyboard is the same as "0163" from the numeric keypad. The second trick comes in handy especially when you have to type such a rarely used character in the password field of windows log-on screen where normal copy and paste does not work.

How to Remove the Windows Genuine Advantage (WGA) Notification Installation Wizard

2009-07-17T03:30:00.000+02:00

I am not against Microsoft at all, actually I am pretty much a big fan of MS's products, e.g. SQL Server. But when Windows tries to annoy me with the WGA notification installation wizard, I have to strike back a bit. Here is the link to the video instructions on youtube.

http://www.youtube.com/watch?v=-OaC_9GStUo

Note: this is only for disabling the installation wizard, if you have already installed the WGA notification on your system, then please Google for other solutions.

How to script the schema and/or data from database objects?

2009-07-13T13:11:00.001+02:00

Recently, I have been facing an requirement to replicate the same data structure from production to a testing environment in a VM machine. And the data in the production db is too large to be hosed in the VM and only the schema is of more interest for my assignment. Therefore, I did some research into how to actually replicate a database schema from one place to another, which put it another can be referred to as "publishing a database".

A complete answers lies here, but for now I've only tested Microsoft's official "database publishing wizard". Strangely, this wizard does not officially support SQL Server 2008 version yet.

How to upgrade an expired evaluation edition of SQL Server 2008

2009-07-10T17:51:00.000+02:00

I recently tried to upgrade SQL Server 2008's evaluation edition to Developer Edition. Since it has been an expired copy, the upgrade although finished with success, the SSMS still kept popping up expiration message. I later did some research on Google and SQLServerCentral, just to find out that no edition upgrade actually upgrade the SSMS and there is some registry modification need to be done for a workaround. This article from Jonathan Kehayias gives a better step-by-step guide to change the registry and after the change is made, you can use the "Edition Upgrade" function from the "Maintenance" page that you can access from the SQL Server Installation Center. One thing to bear in mind is that you have to change the registry first and then run the Edition Upgrade. The sequence can not be reversed.

Two of the most important screen shots are as follows:

How to get Windows to manage wireless network

2009-07-06T22:58:00.001+02:00

This is a strange issue that I met recently on my company's laptop that every time I wanna configure in the way that Windows is used to manage the wireless network, the tab "Wireless Networks" on the "Wireless Network Connection Properties" window simply disappears; even after several reboots, it refuses to reveal itself.

A quick Google search redirects to a forum post and the solution is pretty simple too: just start the Wireless Zero Configuration service either manually or reset it as automatic.

"cannot drop database because it is currently in use"

2009-06-08T19:56:00.000+02:00

A common mistake is usually made when executing the "drop database db_name" statement is forgetting to terminate the current session connected to the target database. A quick solution is to switch to master db and then drop it.

use master
drop database db_name

Most of the time, this can succeed. If the same error message still pops up, disconnect query analyzer or management studio from the SQL Server and reconnect to it and run the above t-sql command again.

Start from being a novice at SQL

2009-05-16T12:04:00.000+02:00

This is the first post on this blog which will document the growth of my technological knowledge regarding MS Sql Server database management system. Since I don't have either the edge or the age advantage anymore, hopefully the road from a novice to a true professional can be shortened by my dedicated efforts. We'll see in the next couple of months how this blog is maintained and updated. Anyhow, I am still a technical person who is determined to shine no matter what I am made of. If anyone ever bumps into this blog while trying to google a technical solution to SQL problems, he/she is welcome to leave a footprint here which will be highly appreciated.