Sql Server 2000 progressive index or row number select query

SET NOCOUNT ON CREATE TABLE #RowNumber ( RowNumber int IDENTITY (1, 1), emp_id char(9) ) INSERT #RowNumber (emp_id) SELECT emp_id FROM employee WHERE job_id = 10 ORDER BY lname SELECT RowNumber, e.emp_id, lname, fname, job_id FROM #RowNumber r JOIN employee e ON r.emp_id = e.emp_id ORDER BY RowNumber DROP TABLE #RowNumber SET NOCOUNT OFF

Read Full Post »

How to insert in Sql Server 2000 database table a XML document String

DECLARE @idoc int DECLARE @doc nvarchar(300) SET @doc = ‘<?xml version=”1.0″ ?> <ROOT> <Shippers ShipperID=”4″ CompanyName=”The RoadRunner” Phone=”(503) 666-9831″/> <Shippers ShipperID=”5″ CompanyName=”Speedy Gonzalez” Phone=”(503) 666-3199″/> </ROOT>’ EXEC sp_xml_preparedocument @idoc OUTPUT, @doc INSERT Northwind..Shippers SELECT * FROM OPENXML (@idoc, N’/ROOT/Shippers’)           WITH Northwind..Shippers EXEC sp_xml_removedocument @idoc

Read Full Post »

Microsoft Sql Server Network Port, Ports

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 »

How to copy SqlServer 2000 DTS packages between servers

on the destination server create a new DTS Package (yes to transfer DTS you have to make a new DTS) create 2 different Microsoft OLE DB Provider for SQL Server connections (a source one and a destination one) select the source and then add a Transform Data Task connecting to the destination connection do not [...]

Read Full Post »

Access Denied issue when exec xp_cmdshell on a UNC pathname

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. Solution: [...]

Read Full Post »

XML Select with Microsoft SqlServer 2000

Avete mai provato ad aggiungere la clausola FOR XML AUTO in fondo a una select fatta su SQLServer 2000? Provate, otterete come risposta un recordset di una riga una colonna con dentro una bella stringona che è un XML Fragment contenente la risposta alla Vs query. La sintassi generale per avere resultset in formato XML [...]

Read Full Post »

How to Restart IIS from a Sql Server Agent Job

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 [...]

Read Full Post »

Sql Server Agent – How to Test Job Execution Status

Have 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 [...]

Read Full Post »

Change users login after a SqlServer 2000 Restore or a Detach Attach

Problem: You make a SqlServer 2000 DB backup from server A. You need to restore it on server B. In alternative suppose you detach a DB from server A to attach it on server B (copying the MDF, NDF and LDF files). After the restoration, what you get at 99% is that to the DB [...]

Read Full Post »

Paged Select with SqlServer 2005, finally!

I 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 [...]

Read Full Post »