Archive For The “DB – SQL Server” Tutte le categorie:

BACKUP

Execute the BACKUP DATABASE statement to create the full database backup, specifying: The name of the database to back up. The backup device where the full database backup is written. declare @databaseName nvarchar(1024); declare @FileName nvarchar(1024); set @FileName = ‘C:\BIZERBA\DbBackup\ORTOFIN_’ + cast(@CD_TIPO_CONFEZIONAMENTO as varchar) + ‘_’ + convert(varchar,getdate(),112)+’.bak’; select @databaseName = ‘ORTOFIN’ ; backup database…

HOW TO READ ONE STORED PROC. CONTENT

sp_helptext @objname = ‘<storedname>’

– OPENDATASOURCE X EXCEL –

leggo un file excel da SQL SELECT * FROM OPENDATASOURCE(‘Microsoft.Jet.OLEDB.4.0’, ‘Data Source=c:\prova.xls; Extended Properties=Excel 8.0’)…[Foglio1$] se non è abilitato il comando OPENDATASOURCE, lanciare il seguente script per abilitarlo: sp_configure ‘show advanced options’, 1 RECONFIGURE GO sp_configure ‘Ad Hoc Distributed Queries’, 1 RECONFIGURE GO

– OPERATORE DI DIVISIONE (/) –

L’operatore divisione (/) restituisce un valore dello stesso tipo del dividendo: es: 1 / 2 = 0 es: 1.0 / 2 = 0.5 

BULK INSERT

1) COPIA DATI IN FILE (da command promt) bcp “SELECT * FROM <tablename>” queryout <filename> -c -S <*servername>* -T [-U -P] 2) IMPORTA I DATI STATICI NEL DATABASE DELETE FROM <tablename> BULK INSERT <tablename> FROM <path> WITH (DATAFILETYPE = ‘widenative’, KEEPIDENTITY, KEEPNULLS) (es: ‘\\APOLLO\Prj\Pneumax\Pnx-Silog\Sql\Dati Statici\filename’)

TEMPORARY TABLES

Sintassi: – tabelle temporanee locali: #<nomeTabella> (è vista solo dalla sessione di connessione al DB che la crea e muore alla fine della sessione stessa) – tabelle temporanee globali: ##<nomeTabella> (è vista da tutte le sessioni e vive fino all’esecuzione dell’ultimo statement SQL lanciato nella sessione di creazione della tab) – variabile tabella: @<nomeTabella> Esempio…

FOR XML EXPLICIT

CREATE PROCEDURE [dbo].[forecastExpPianoConsumiXML] AS BEGIN SET NOCOUNT ON; SELECT DISTINCT 1 AS Tag, NULL AS Parent, RTRIM(fc1.itemId) AS [item!1!itemId], NULL AS [forecast!2!StartDate], NULL AS [forecast!2!FreqCode], NULL AS [forecast!2!Active], NULL AS [forecast!2!InventQty], NULL AS [forecast!2!Comment], NULL AS [forecast!2!ItemGroupId], NULL AS [forecast!2!Currency], NULL AS [forecast!2!Report], NULL AS [forecast!2!SalesQty], NULL AS [forecast!2!PriceUnit], NULL AS [forecast!2!ModelId], NULL AS [forecast!2!InventDimId]…

OBJECT DEPENDENCIES

E’ possibile visualizzare le dipendenze di un oggetto del database nei seguenti modi: 1) INFORMATION_SCHEMA.ROUTINES:  SELECT routine_name, routine_type FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE ‘%objectname%’; 2) sp_depends:  EXEC sp_depends @objname = N’HumanResources.Employee’; 3) syscomments:  SELECT distinct so.name FROM syscomments sc INNER JOIN sysobjects so ON sc.id = so.id WHERE charindex(‘Employee’, text) > 0; 4) sp_MSdependencies:  EXEC…

GET VERSION

Comandi per leggere la versione e l’edizione dell’istanza di sql server installata: SELECT @@VERSION AS [Server Information] SELECT SERVERPROPERTY(‘ServerName’) AS [ServerName] SELECT SERVERPROPERTY(‘MachineName’) AS [MachineName] SELECT SERVERPROPERTY(‘InstanceName’) AS [InstanceName] SELECT SERVERPROPERTY(‘productversion’) AS [ProductVersion] SELECT SERVERPROPERTY(‘ProductLevel’) AS [ProductLevel] SELECT SERVERPROPERTY(‘Edition’) AS [Edition] SELECT SERVERPROPERTY(‘Collation’) AS [Collation] SELECT SERVERPROPERTY(‘IsClustered’) AS [IsClustered] SELECT SERVERPROPERTY(‘IsFullTextInstalled’) AS [IsFullTextInstalled] SELECT SERVERPROPERTY(‘IsIntegratedSecurityOnly’)…

RESTORE DATABASE

DECLARE @path AS nvarchar(4000) DECLARE @databasename AS nvarchar(100) SET @path = ‘\\apollo\Users\Nicola.Riccardi\historian.bak’ SET @databasename = ‘HistorianBak’ RESTORE LABELONLY FROM DISK= @path RESTORE FILELISTONLY FROM DISK= @path RESTORE HEADERONLY FROM DISK= @path RESTORE DATABASE @databasename FROM DISK = @path WITH REPLACE oppure RESTORE DATABASE @databasename FROM DISK = @path WITH MOVE ‘AXADEV’ TO ‘D:\MsSql\Data\AxPnxProdIT.mdf’, MOVE ‘AXADEV_log’…

Go Top