Archive for the ‘SqlServer 2005’ Category

Microsoft SQL Server 2000 provides a powerful and comprehensive data management platform. The ports used by each instance of SQL Server can be configured through the Server Network Utility, the default port numbers used by Sql Server are:

  • Sql Over TCP (TCP): 1433
  • Sql Probe (UDP): 1434

Read Full Post »

Suppose you use a Sql Server user to execute a shell command on a remote shared folder

exec xp_cmdshell 'command UNC path'

and you get an Access Denied error.

This is because MSSQLSERVER, SQLSERVERAGENT services run as Local System Account that is not recognized on the remote machine you are pointing by the UNC path.


  • go to Control Panel, Administrative Tools, Services 
  • change Logon as user from local system to This User, specifying an administrative account that is the same on the two machine and with the same password (be aware changing it).
  • give privileges to that account to the remote shared folder
  • retry the exec xp_cmdshell

Read Full Post »

How to restart IIS…Suppose to have an ASP3 or ASP.Net application running on the same machine where you Microsoft Sql Server runs.

For sure you have experienced that IIS grows up in memory (or better: the underlying worker processes) day by day decreasing constantly the overall system performances.

The most used solution to restore an initial idle state of the system is to schedule a nightly IIS RESTART.

Also, many System Administrators wants to keep a unique eye on a unique Job Scheduler and Log, and if you have Sql Server Agent you can think to use it to run any kind of processes (from stored procedure to windows executables).

So, to restart your IIS, schedule this piece of T-SQL code directly in Sql Server Agent:

exec master..xp_cmdshell 'iisreset /RESTART'

Simply not?

Read Full Post »

Running RobotHave you some Sql Server Agent job you want to test the execution status?

EXEC msdb..sp_help_job
@job_name = 'InfiniteLoop',
@job_aspect = N'job'

The result set includes a column called current_execution_status, but you might not know how to interpret the column values because SQL Server Books Online (BOL) doesn’t document them.
However, you can learn a lot by directly reading stored-procedure code. The parameter-declaration section of sp_help_job, as shown below, tells you how to interpret the value of current_execution_status:

  • @execution_status INT = NULL,
  • — 1 = Executing,
  • — 2 = Waiting For Thread,
  • — 3 = Between Retries,
  • — 4 = Idle,
  • — 5 = Suspended,
  • — 6 = [obsolete],
  • — 7 = PerformingCompletion
  • — Actions

For example, a job that’s running will have a value of 1; values 2 and 3 show that the job has started but isn’t executing commands.
Now, you can pass the result set to your program by using the syntax:


which stores sp_help_job’s output in a table. . . .

Read Full Post »

SqlServer 2005I remember the annoying tricks with SqlServer 2000 to have paged results from a query.
For example, in MySql exists the useful keyword limit … using limit 10,10 extracts 10 records after the 10th.
But the question is, what exists in SqlServer 2005?

Well it is not so easy as in MySql but you can do it like:


declare @pagesize int;
declare @pageindex int;

-- page size
set @pagesize = 10;
-- page offset where 0 == first page
set @pageindex = 0;
select top(@pagesize) *
from sysobjects
where id not in
select top(@pagesize * @pageindex) id
from sysobjects
order by name asc
order by name asc;

Read Full Post »