Feeds:
Posts
Comments

Archive for the ‘SqlServer 2000’ Category

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

Read Full Post »

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 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 »

  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

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.

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

Read Full Post »

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

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 »

Older Posts »