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

Advertisements

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

How to copy SqlServer 2000 DTS packages between servers

  1. on the destination server create a new DTS Package (yes to transfer DTS you have to make a new DTS)
  2. create 2 different Microsoft OLE DB Provider for SQL Server connections (a source one and a destination one)
  3. select the source and then add a Transform Data Task connecting to the destination connection
  4. do not open the DataPump task properties yet, but open Disconnected Edit from the package menu
  5. navigate to your DataPump task and set the SourceObjectName and DestinationObjectName properties to [msdb].[dbo].[sysdtspackages]
  6. open the DataPump task properties and go to the Transformations tab to allow the transformations to auto-generate
  7. close properties and execute the package
  8. done

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:

  • go to Control Panel, Administrative Tools, Services 
  • stop  MSSQLSERVER and SQLSERVERAGENT
  • 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).
  • start  MSSQLSERVER and SQLSERVERAGENT
  • give privileges to that account to the remote shared folder
  • retry the exec xp_cmdshell

XML Select with Microsoft SqlServer 2000

XML SelectAvete 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 fragment è:

Code:

SELECT colonne_da_visualizzare
FROM nome_tabella
WHERE condizioni_di_ricerca
FOR XML AUTO | RAW | EXPLICIT [, XMLDATA] [, ELEMENTS] [, BINARY BASE64]

Esempio 1

SELECT nome,cognome FROM utenti FOR XML AUTO

restituisce le righe come elementi e le colonne come attributi, esempio:
Code:

<utenti nome="Dino" cognome="Olivieri" />
<utenti nome="Enrico" cognome="Porta" />

Esempio 2

SELECT nome,cognome FROM utenti FOR XML RAW

RAW sostituisce il nome riga della tabella con row sia che usi gli elements (vedi esempio successivo) sia che non li usi:
Code:

<row nome="Enrico" cognome="Porta" />

Esempio 3

SELECT nome,cognome FROM utenti FOR XML AUTO, ELEMENTS

restituisce le righe come elementi e le colonne come elementi figlio, esempio:
Code:

<utenti>
  <nome>Dino</nome>
  <cognome>Olivieri</cognome>
</utenti>
<utenti>
  <nome>Enrico</nome>
  <cognome>Porta</cognome>
</utenti>

Esempio 4

Ecco come ottenere un Fragment TR, TD con tanto di intestazione pronto da impaginare in un documento HTML

Code:

SELECT TOP 1 'ID GRUPPO' as td, 'NOME GRUPPO' AS td FROM table_groups AS tr
UNION ALL
SELECT TOP 10 CONVERT(varchar, idgroup) AS td, group_name AS td FROM table_groups AS tr
FOR XML AUTO, ELEMENTS

How to Restart IIS from a Sql Server Agent Job

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?