XACT_ABORT

Opublikowano 6 lutego 2020, autor: micek

Stwórzmy bazę TEST, a następnie tabelę TABLE_TEST składają się z dwóch kolumn : ID i nazwy oraz z kluczem publicznym na kolumnie ID.

CREATE DATABASE [TEST]

Commands completed successfully.

USE [TEST]
GO
CREATE TABLE [dbo].[TABLE_TEST](
[id] [int] NOT NULL,
[nazwa] [nchar](10) NULL,
CONSTRAINT [PK_TableTest] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Commands completed successfully.

Następnie spróbujmy wykonać polecenie:

BEGIN TRANSACTION
INSERT INTO TABLE_TEST (ID,nazwa) VALUES(1,'rower');
INSERT INTO TABLE_TEST (ID,nazwa) VALUES(1,'wrotki');
COMMIT TRANSACTION;

(1 row affected)
Msg 2627, Level 14, State 1, Line 22
Violation of PRIMARY KEY constraint 'PK_TableTest'. 
Cannot insert duplicate key in object 'dbo.TABLE_TEST'. 
The duplicate key value is (1).
The statement has been terminated.

Jako że dane w kolumnie ID muszą być unikalne, zwróciło błąd jednak pierwsza operacja INSERT została zapisana do tabeli.

SELECT * FROM [TEST].[dbo].[TABLE_TEST]

id          nazwa
----------- ----------
1           rower     

(1 row affected)

Jest tak ponieważ domyślnie transakcje nie jest anulowane w przypadku wystąpienia błędów w trakcie jej wykonywania, tylko jest kontynuowana. Aby temu zapobiec należy ustawić zmienną XACT_ABORT na wartość ON:

select 16384 & @@OPTIONS as '16384_@OPTIONS'

16384_@OPTIONS
--------------
0

(1 row affected)

Ustawiamy zmienną XACT_ABORT na wartość ON.

SET XACT_ABORT ON;

Commands completed successfully.

Teraz wyczyścimy naszą tabelę z danych

TRUNCATE TABLE TEST.TABLE_TEST

Commands completed successfully.

Ponownie wykonujemy nasze polecenie INSERT

BEGIN TRANSACTION
insert into TABLE_TEST (ID,nazwa) values(1,'rower');
insert into TABLE_TEST (ID,nazwa) values(1,'wrotki');
COMMIT TRANSACTION;

(1 row affected)
Msg 2627, Level 14, State 1, Line 21
Violation of PRIMARY KEY constraint 'PK_TableTest'. 
Cannot insert duplicate key in object 'dbo.TABLE_TEST'. 
The duplicate key value is (1).

Zwróciło nam błąd, ale dane nie zostały zapisane do tabeli

SELECT * FROM [TEST].[dbo].[TABLE_TEST]

id          nazwa
----------- ----------

(0 rows affected)

XACT_ABORT powoduje, że w przypadku wystąpienia błędu cała transakcja jest anulowana (nastąpi rollback).
Oczywiście działa to tylko w przypadku stosowanie opcji transakcji BEGIN/COMMIT.
Jeśli wykonamy INSERT pojedynczo pierwszy zostanie zapisany do tabeli

insert into TABLE_TEST (ID,nazwa) values(1,'rower');
insert into TABLE_TEST (ID,nazwa) values(1,'wrotki');

(1 row affected)
Msg 2627, Level 14, State 1, Line 21
Violation of PRIMARY KEY constraint 'PK_TableTest'. 
Cannot insert duplicate key in object 'dbo.TABLE_TEST'. 
The duplicate key value is (1).

SELECT * FROM [TEST].[dbo].[TABLE_TEST]

id          nazwa
----------- ----------
1           rower     

(1 row affected)
Zaszufladkowano do kategorii Microsoft SQL | Dodaj komentarz

Backup i odtworzenie bazy z wykorzystaniem znacznika (mark)

Opublikowano 24 stycznia 2020, autor: micek

Odtworzenie bazy możemy wykonać z backupu pełnego lub do punktu w czasie z backupu logów transakcyjnych. Dziś pokaże jak odzyskać do punktu (mark) który „zaznaczymy podczas naszych prac na bazie. Pozwala to np na uruchomienie znacznika a następnie odtworzenie kilku zależnych baz do konkretnego punktu aby bazy były konsystentne między sobą.

Na początku utworzymy nową bazę i stworzymy prostą tabelę.

CREATE DATABASE BACKUP_MARK
CREATE TABLE [BACKUP_MARK].[dbo].[TABELA]([random] [int] NULL) 

Commands completed successfully.

Baza powinna być w trybie Full Recovery. Wykonamy pełny backup pustej bazy

BACKUP DATABASE [BACKUP_MARK] TO DISK = N'C:\SQLBackup\BACKUP_MARK.bak'
WITH NOFORMAT, INIT, NAME = N'BACKUP_MARK-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

10 percent processed.
20 percent processed.
31 percent processed.
41 percent processed.
50 percent processed.
60 percent processed.
71 percent processed.
81 percent processed.
90 percent processed.
Processed 376 pages for database 'BACKUP_MARK', file 'BACKUP_MARK' on file 1.
100 percent processed.
Processed 5 pages for database 'BACKUP_MARK', file 'BACKUP_MARK_log' on file 1.
BACKUP DATABASE successfully processed 381 pages in 0.082 seconds (36.299 MB/sec).

oraz backup logów

BACKUP LOG [BACKUP_MARK] TO DISK = N'C:\SQLBackup\BACKUP_MARK.bak'
WITH NOFORMAT, NOINIT, NAME = N'BACKUP_MARK-Log Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

57 percent processed.
100 percent processed.
Processed 14 pages for database 'BACKUP_MARK', file 'BACKUP_MARK_log' on file 2.
BACKUP LOG successfully processed 14 pages in 0.017 seconds (6.433 MB/sec).

Teraz wykonamy proste inserty do tabeli.

Najpierw uruchamiamy nasz znacznik (transakcję)

USE [BACKUP_MARK]
GO
BEGIN TRANSACTION TableInsert1
WITH MARK 'Insert do tabeli'; 

Commands completed successfully.

Teraz wykonujemy inserty. W naszym przypadku będzie to 499 losowych liczb.

DECLARE @Counter int;
SET @Counter = 1;
WHILE @Counter < 10 BEGIN Skok: --SELECT @Counter SET @Counter = @Counter + 1 INSERT INTO [BACKUP_MARK].[dbo].[TABELA] ([random]) VALUES (FLOOR(RAND()*100000)) IF @Counter = 500 GOTO Branch_Two goto SKOK END Branch_Two:

Zamykamy naszą transakcję.

COMMIT TRANSACTION TableInsert1;
GO 

Commands completed successfully.

Zapisany znacznik możemy sprawdzić zapytaniem

SELECT * FROM msdb.[dbo].[logmarkhistory]

database_name      mark_name        description          user_name              lsn                  mark_time
----------------------------------------------------------------------------------------------------------------------------
BACKUP_MARK        TableInsert1     Insert do tabeli     MICEK1968\micek1968    37000000036000531    2020-01-23 19:46:32.820

(1 row affected)

Aby sprawdzić jaki ostatni LSN został zapisany w backupie bazy

USE [BACKUP_MARK]
SELECT last_log_backup_lsn
FROM sys.database_recovery_status
WHERE database_id = DB_ID()

last_log_backup_lsn
---------------------------------------
37000000032800001

(1 row affected)

Sprawdzamy ile wierszy się zapisało w tabeli

SELECT count(*)
FROM [BACKUP_MARK].[dbo].[TABELA]

-----------
499

(1 row affected)

Wykonujemy następne 499 insertów

DECLARE @Counter int;
SET @Counter = 1;
WHILE @Counter < 10 BEGIN Skok: --SELECT @Counter SET @Counter = @Counter + 1 INSERT INTO [BACKUP_MARK].[dbo].[TABELA] ([random]) VALUES (FLOOR(RAND()*100000)) IF @Counter = 500 GOTO Branch_Two goto SKOK END Branch_Two:

Sprawdzamy ilość wierszy

SELECT count(*)
FROM [BACKUP_MARK].[dbo].[TABELA]

-----------
998

(1 row affected)

Wykonujemy kolejny backup logów naszej bazy

BACKUP LOG [BACKUP_MARK] TO DISK = N'C:\SQLBackup\BACKUP_MARK.bak'
WITH NOFORMAT, NOINIT, NAME = N'BACKUP_MARK-Log Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

11 percent processed.
20 percent processed.
31 percent processed.
40 percent processed.
52 percent processed.
60 percent processed.
72 percent processed.
81 percent processed.
92 percent processed.
100 percent processed.
Processed 259 pages for database 'BACKUP_MARK', file 'BACKUP_MARK_log' on file 3.
BACKUP LOG successfully processed 259 pages in 0.038 seconds (53.145 MB/sec).

I następne 499 inserty

DECLARE @Counter int;
SET @Counter = 1;
WHILE @Counter < 10 BEGIN Skok: --SELECT @Counter SET @Counter = @Counter + 1 INSERT INTO [BACKUP_MARK].[dbo].[TABELA] ([random]) VALUES (FLOOR(RAND()*100000)) IF @Counter = 500 GOTO Branch_Two goto SKOK END Branch_Two:

I ponownie sprawdzamy ilość wierszy w tabeli

SELECT count(*)
FROM [BACKUP_MARK].[dbo].[TABELA]

 -----------
1497

(1 row affected)

I kolejny backup logów

BACKUP LOG [BACKUP_MARK] TO DISK = N'C:\SQLBackup\BACKUP_MARK.bak'
WITH NOFORMAT, NOINIT, NAME = N'BACKUP_MARK-Log Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

12 percent processed.
22 percent processed.
31 percent processed.
41 percent processed.
50 percent processed.
60 percent processed.
73 percent processed.
82 percent processed.
92 percent processed.
100 percent processed.
Processed 251 pages for database 'BACKUP_MARK', file 'BACKUP_MARK_log' on file 4.
BACKUP LOG successfully processed 251 pages in 0.035 seconds (56.026 MB/sec).

Pora odzyskać naszą bazę. Interesuje nas punkt kiedy oznaczyliśmy znacznikiem (zakończyliśmy transakcję)

Wcześniej poznaliśmy numer LSN odpowiadającemu naszemu znacznikowi (LSN=37000000036000531)

Możemy sprawdzić z których plików backupu będzimy korzystać przy odzyskiwaniu bazy

use msdb
go
select s.backup_set_id,
s.first_lsn,
s.last_lsn,
s.database_name,
s.backup_start_date,
s.backup_finish_date,
s.type,
f.physical_device_name
from backupset s join backupmediafamily f
on s.media_set_id = f.media_set_id
WHERE s.database_name = 'BACKUP_MARK'
and first_lsn < 37000000036000531 order by s.backup_finish_date

backup_set_id   first_lsn             last_lsn              database_name     backup_start_date          backup_finish_date         type   physical_device_name
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1148            37000000021600170     37000000030400001     BACKUP_MARK       2020-01-23 19:46:16.000    2020-01-23 19:46:16.000    D      C:\SQLBackup\BACKUP_MARK.bak
1149            37000000021600170     37000000032800001     BACKUP_MARK       2020-01-23 19:46:20.000    2020-01-23 19:46:20.000    L      C:\SQLBackup\BACKUP_MARK.bak
1150            37000000032800001     38000000051200001     BACKUP_MARK       2020-01-23 19:46:48.000    2020-01-23 19:46:48.000    L      C:\SQLBackup\BACKUP_MARK.bak

(3 rows affected)

Jak widać będziemy potrzebowali pliku z backupem pełnym i dwa backupy logów

Przypominam że wykonaliśmy w trakcie naszych prac:
-pełny backup bazy (backup File=1)
-backup logów (backup File=2) - 0 wierszy w tabeli
-inserty do tabeli (499 wierszy)
-ustawiliśmy znacznik (TableInsert1)
-wykonaliśmy kolejne 499 insertów
-backup logów (backup File=3) - 998 wierszy
-inserty do tabeli (499 wierszy)
-backup logów (backup File=4) - 1497 wierszy

Tak więc aby odzyskać bazę do punktu oznaczenia naszego znacznika musimy odzyskać bazę z pełnego backupu (File=1) a następnie z backupu logów (File=2 i File=3). Nasz znacznik jest zbackupowany w backupie oznaczonym jako File=3

najpierw przestawimy bazę w tryb Single User
USE [master]
ALTER DATABASE [BACKUP_MARK] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Commands completed successfully.

Wykonujemy backup obecnych logów.
BACKUP LOG [BACKUP_MARK]
TO DISK = N'C:\SQLBackup\BACKUP_MARK_LogBackup_2020-01-23_19-26-06.bak'
WITH NOFORMAT, NOINIT, NAME = N'BACKUP_MARK_LogBackup_2020-01-23_19-26-06',
NOSKIP, NOREWIND, NOUNLOAD, NORECOVERY , STATS = 5

100 percent processed.
Processed 4 pages for database 'BACKUP_MARK', file 'BACKUP_MARK_log' on file 2.
BACKUP LOG successfully processed 4 pages in 0.017 seconds (1.838 MB/sec).

Teraz odtwarzamy naszą bazę z pełnego logu

RESTORE DATABASE [BACKUP_MARK] FROM DISK = N'C:\SQLBackup\BACKUP_MARK.bak'
WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5

6 percent processed.
10 percent processed.
16 percent processed.
20 percent processed.
25 percent processed.
31 percent processed.
35 percent processed.
41 percent processed.
46 percent processed.
50 percent processed.
56 percent processed.
60 percent processed.
65 percent processed.
71 percent processed.
75 percent processed.
81 percent processed.
86 percent processed.
90 percent processed.
96 percent processed.
100 percent processed.
Processed 376 pages for database 'BACKUP_MARK', file 'BACKUP_MARK' on file 1.
Processed 5 pages for database 'BACKUP_MARK', file 'BACKUP_MARK_log' on file 1.
RESTORE DATABASE successfully processed 381 pages in 0.774 seconds (3.845 MB/sec).

Spójrzmy co będzie jak będziemy chcieli uruchomić odtwarzanie od razu z ostatniego backupu (File=3) podając opcję STOPMARK i wpisując nazwę naszej transakcji (marka)

RESTORE LOG [BACKUP_MARK]
FROM DISK = N'C:\SQLBackup\BACKUP_MARK.bak'
WITH FILE = 3,
RECOVERY,
STOPATMARK = 'TableInsert1';

Msg 4305, Level 16, State 1, Line 236
The log in this backup set begins at LSN 89000000544800001, 
which is too recent to apply to the database.
 An earlier log backup that includes LSN 89000000542400001 can be restored.
Msg 3013, Level 16, State 1, Line 236
RESTORE LOG is terminating abnormally.

Jak widać SQL zwrócił nam błąd związany z błędnym numerem sekwencji (LSN). Musimy bazę odtworzyć kolejno z naszych backupów logów. Wykonywaliśmy je ręcznie i wiemy na którym pliku backupu logu mamy skończyć ale równie dobrze backup może wykonywać się automatycznie więc będziemy wykonywać odtworzenie do czasu prawidłowego zakończenia procesu.

Zaczynamy od pliku backupu logu File=2
RESTORE LOG [BACKUP_MARK]
FROM DISK = N'C:\SQLBackup\BACKUP_MARK.bak'
WITH FILE = 2,
RECOVERY,
STOPATMARK = 'TableInsert1'; 

Processed 0 pages for database 'BACKUP_MARK', file 'BACKUP_MARK' on file 2.
Processed 12 pages for database 'BACKUP_MARK', file 'BACKUP_MARK_log' on file 2.
This log file contains records logged before the designated mark. 
The database is being left in the Restoring state so you can apply another log file.
RESTORE LOG successfully processed 12 pages in 0.013 seconds (6.911 MB/sec).

Potrzebny jest kolejny plik backupu logów aby odtworzyć bazę do punktu naszego znacznika. Odtwarzamy kolejny plik backupu logów (File=3)

RESTORE LOG [BACKUP_MARK]
FROM DISK = N'C:\SQLBackup\BACKUP_MARK.bak'
WITH FILE = 3,
RECOVERY,
STOPATMARK = 'TableInsert1'; 

Processed 0 pages for database 'BACKUP_MARK', file 'BACKUP_MARK' on file 3.
Processed 259 pages for database 'BACKUP_MARK', file 'BACKUP_MARK_log' on file 3.
RESTORE LOG successfully processed 259 pages in 0.034 seconds (59.397 MB/sec).

Odzyskanie się zakończyło. Komunikat zwrócił informację o poprawnym odtworzniu bazy. Próby kolejnego odtwarzania plików backupu logów zwrócą nam błąd

RESTORE LOG [BACKUP_MARK]
FROM DISK = N'C:\SQLBackup\BACKUP_MARK.bak'
WITH FILE = 4,
RECOVERY,
STOPATMARK = 'TableInsert1';

Msg 3117, Level 16, State 1, Line 252
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 252
RESTORE LOG is terminating abnormally.

Tak więc bazę mamy odtworzoną. Powinno być 499 wierszy.

SELECT count(*)
FROM [BACKUP_MARK].[dbo].[TABELA]

-----------
499

(1 row affected)

Jak widać baza odzyskała się prawidłowo.

Po oznaczeniu znacznikiem bazy sprawdziliśmy numer LSN odpowiadający naszemu znacznikowi. Odtwarzając bazę możemy skorzystać też z tego numeru LSN (zamiast nazwy znacznika).

RESTORE LOG AdventureWorks2 FROM DISK = 'C:\SQLBackup\BACKUP_MARK.bak'
WITH STOPATMARK = 'lsn:37000000036000531'
GO

Na koniec czyścimy bazy...

USE master
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'A1'
GO
DROP DATABASE A1
GO

Zaszufladkowano do kategorii Microsoft SQL | Dodaj komentarz

PowerShell na usługach SQL – podstawowe funkcje

Opublikowano 19 stycznia 2020, autor: micek

Sprawdzenie wersji SQL-a

Get-SQLInstance -ServerInstance "localhost"

Lista baz w instancji

Get-SQLDatabase -ServerInstance "localhost"

Lista userów w instancji

Get-SQLLogin -ServerInstance "localhost"

Lista backupów baz danych

Get-SQLBackupHistory -ServerInstance "localhost"

Odczyt widoku

Read-SqlViewData -ServerInstance "localhost" -DatabaseName "AdventureWorks2012" -SchemaName "Sales" -ViewName "vSalesPerson" -ColumnName "FirstName","LastName" -TopN 10

Odczyt tabeli (Person.Person) w bazie danych (AdventureWorks2012)

Read-SqlTableData -ServerInstance "localhost" -DatabaseName "AdventureWorks2012" -SchemaName "Person" -TableName "Person" -TopN 3

Wykonanie T-SQL-a

Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance "localhost"

źródło: https://docs.microsoft.com/en-us/powershell/module/sqlps/Backup-SqlDatabase?view=sqlserver-ps

Zaszufladkowano do kategorii Microsoft SQL, SQL PowerShell | Dodaj komentarz

PowerShell na usługach SQL – konfiguracja

Opublikowano 18 stycznia 2020, autor: micek

Tak jak do innych celów również do SQL-a możemy wykorzystać powłoki PowerShell.

W management Studio klikamy w nasz serwer i uruchamiamy „Start PowerShell”.

Niestety nie został zainstalowany pakiet SQLServera więc konsolka nam się nie otworzy.

Mozemy kliknąć YES i wtedy uruchomi się nam konsola bez obsługi SQL-a lub „Open PowerShell Gallery” co spowoduje otwarcie strony z dodatkiem SQL do PowerShella oraz krótką instrukcją jak to zainstalować.

Aby zainstalować dodatek należy uruchomić PowerShell’a w poziomu Windowsa jako administrator.
Następnie wydać polecenie

Install-Module -Name SqlServer -AllowClobber

Po zakończeniu instalacji ponownie uruchamiamy PowerShella z SQL Management Studio. Tym razem powinno uruchomić się poprawnie.

Poprawność połączenia możemy sprawdzić wydając np komendę

Get-SQLInstance -ServerInstance "localhost"

Zaszufladkowano do kategorii Microsoft SQL, SQL PowerShell | Dodaj komentarz

Kto usunął tabele w bazie???

Opublikowano 8 stycznia 2020, autor: micek

W tej części pokażę jak sprawdzić kto i kiedy usunął nam tabelę z bazy danych.
Na początek utworzymy sobie testową tabelę w naszej bazie

USE HURTOWNIA
GO
CREATE TABLE TestTable (ID INT)
GO
ALTER TABLE TestTable
ADD FirstCol VARCHAR(100)
GO

Commands completed successfully.

Następnie ją usuwamy

DROP TABLE TestTable
GO

Commands completed successfully.

Teraz przyszła pora na śledztwo kto jest winny zniknięcia tabeli w bazie Hurtownia. Uruchamiamy Raport.

Jak widać w raporcie widać kto stworzył tabelę i kto ją usunął 😉

Zaszufladkowano do kategorii Microsoft SQL | Dodaj komentarz

SSIS – Loopy czyli pętle SSIS-owe

Opublikowano 27 października 2019, autor: micek

Poniżej opiszę wykorzystanie komponentu wykonującego proste pętle w naszych paczkach SSIS.
Na początek umieszczamy w naszym nowym projekcie komponent LOOP oraz umieszczamy w nim „Execute SQL Task”. Tworzymy też nowe dwie zmienne potrzebne do działania naszej „pętli”.

Zmienna COUNT to liczba powtórzeń a INC kolejna liczba która będzie stopniowo rosnąć lub maleć osiągając wartość COUNT gdzie nastąpi zatrzymanie pętli.

Klikamy w nasz komponent Loop i go modyfikujemy.

Wpisujemy nasze zmienne tak jak poniżej w odpowiednie okna zakładki FOR LOOP

Teraz klikamy w nasz komponent znajdujący się w środku komponentu LOOP. Będzie on powtarzany kilka razy.

Aby nie zaśmiecać bazy AdventuresWorks2012 tworzymy połączenie do naszej testowej bazy Hurtownia.


Polecenie ma robić prosty insert losowej liczby do tabeli LOSOWA.

Uzupełniamy pozostałe opcję komponentu.

Uruchamiamy naszą paczkę.

Sprawdzamy czy inserty się zapisały w tabeli. Jak widać wykonały się 10 razy więc jest OK

Dla porządku możemy jeszcze dołożyć komponent wykonujący TRUNCATE na naszej tabeli żeby liczba wierszy nam nie rosła z kazdym uruchomieniem naszej paczki.

Zaszufladkowano do kategorii SSIS | Dodaj komentarz

SSIS – Zapytanie, zmienne i MessageBoxy

Opublikowano 26 października 2019, autor: micek

W tym odcinku pokażę jak wykorzystać zmienne do prostego zapytania na bazie oraz wyświetlenia potem odpowiedniego komunikatu (MessageBox). Tworzymy nowy projekt i umieszczamy w nim dwa komponenty (Execute SQL Task i Script Task) po czym łączymy je ze sobą.

Klikamy prawym klawiszem myszy na polu naszego projektu i otwieramy okno zmiennych (Variables)

W oknie klikając na pierwszą ikonkę tworzymy nową zmienną i nazywamy ją VarLudzie typu Int32.

Teraz klikamy na nasz pierwszy komponent Execute SQL Task

Nim cokolwiek wpiszemy w ustawieniach naszego komponentu wykonujemy na naszej bazie danych proste zapytanie

Jak widać zapytanie zwróciło nam liczbę wierszy 19972. W ustawieniach komponentu wybieramy nasz połączenie do bazy (Connection).

W oknie SQLStatement wpisujemy nasze zapytanie.

W ResultSet zmienamy opcję na SingleRow

Przechodzimy do zakładki Result Set i wybieramy zapomocą przycisku ADD z listy naszą zmienną.

W kolumnie ResultName wpisujemy 0 (zero) jako że wzracana będzie tylko jeden wynik (jako pierwsza kolumna tabeli licząc od zera). Jeśli by było więcej kolumn w wyniku naszego zapytania oznaczylibyśmy je kolejno 0,1,2 itd

Aby sprawdzić co zwraca nasze zapytanie możemy założyć BreakPointa na naszym projekcie który spowoduje zatrzymanie się wykonywania na pierwszym kroku abyśmy mogli prześledzić jakie wartości przyjmują zmienne.
W tym celu klikamy na naszym pierwszym komponencie i wybieramy Edit Breakpoint

Wybieramy z listy drugą opcję „Break when the container receives the OnPostExecute event”

Potwierdzamy przyciskiem OK i uruchamiamy projekt.

Jak widać projekt zatrzymał się na naszym Breakpoint-cie.

Klikająć w ikonkę zmiennych (kostka) otworzy się nam okno ze zmiennymi.

Musimy tylko teraz przeciagnąć naszą zmienną (VarLudzie) do okna WATCH 1.

Jak widać zmienna przyjęła wartość 19972 czyli wszystko jest OK, więc wyłączamy nasz Breakpoint

Teraz zajmiemy się naszym drugim komponentem (Script Task) który będzie miał za zadanie wyświetlenie naszego wyniku za pomocą MessageBox-a. Klikamy w nasz komponent.

W ReadWriteVariables wybiarmy naszą zmienną przekazaną przez wcześniejszy komponent.

następnie klikamy w EDIT SCRIPT. Po otwarciu nowego okna Visual Studio znajdujemy w treści skryptu fragment

i modyfikujemy dodajemy do niego linie

string strMessage = Dts.Variables["User::VarLudzie"].Value.ToString();
MessageBox.Show(strMessage);

Wklejamy tekst tak jak poniżej

Zapisujemy skrypt, zamykamy okno Visual Studio, zapisujemy nasz projekt i uruchamiamy projekt

Powinno pojawić się okienko

Zaszufladkowano do kategorii SSIS | Dodaj komentarz

Liczba wierszy w tabelach bazy danych

Opublikowano 8 lipca 2019, autor: micek

Aby sprawdzić ilość wierszy we wszystkich tabelach w danej bazie danych wykonujemy zapytanie:

SELECT SCHEMA_NAME(schema_id) AS [SchemaName],
[Tables].name AS [TableName],
SUM([Partitions].[rows]) AS [TotalRowCount]
FROM sys.tables AS [Tables]
JOIN sys.partitions AS [Partitions]
ON [Tables].[object_id] = [Partitions].[object_id]
AND [Partitions].index_id IN ( 0, 1 )
-- WHERE [Tables].name = N'name of the table'
GROUP BY SCHEMA_NAME(schema_id), [Tables].name;

SchemaName			TableName			TotalRowCount
------------------------------------------------------------------------------
Person				Address				19614
Person				AddressType			6
dbo				AWBuildVersion			1
Production			BillOfMaterials			2679
...
...
Production			UnitMeasure			38
Purchasing			Vendor				104
Production			WorkOrder			72591
Production			WorkOrderRouting		67131

(71 rows affected)
Zaszufladkowano do kategorii Microsoft SQL | Dodaj komentarz

Kompresja tabel w SQL Server

Opublikowano 21 lutego 2019, autor: micek

Uruchamiamy kompresję na pojedynczej tabeli w naszej bazie.


Wybieramy rodzaj kompresji.

Za pomocą tego przycisku możemy sprawdzić jak mocno skompresuje się nasza tabela.





Jeśli wejdziemy we właściwości tabeli kompresja nie jest włączona.

Aby ja uruchomić na stałe musimy wykonać polecenie:

USE [AdventureWorks2012]
ALTER TABLE [Person].[Person] REBUILD PARTITION = ALL
WITH
(DATA_COMPRESSION = PAGE
)

Sprawdzamy czy kompresja jest włączona

Powyższy przykład pokazuje jak uruchomić kompresję na pojedynczje tabeli. Jeśli chcielibyśmy uruchomić kompresję na całej bazie danych (wszytskich tabelach) należy wykonać:

EXEC sp_MSforeachtable @command1 = 'alter table ? REBUILD WITH (DATA_COMPRESSION = PAGE);'

Zaszufladkowano do kategorii Microsoft SQL | Dodaj komentarz

BCP

Opublikowano 20 lutego 2019, autor: micek

Po tą tajemniczą nazwą skrywa się narzędzie Bulk Copy Program utility (w skrócie BCP). Służy ono do kopiowania danych pomiędzy instancją bazy danych SQL Server i płaskimi plikami. Programu można używać z poziomu SQL przy pomocy funkcji CMDSHELL lub z poziomu windowsowej linii poleceń CMD.

Więcej o tym narzędziu można znaleźć na stronie Microsoftu
https://docs.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-2017

Ja dziś pokaże jak przy pomocy tego programu można w prosty sposób wykonać backup pojedynczej tabeli.

Na początek musimy mieć włączoną w naszej instancji funkcję XP_CMDSHELL. Jeśli nie mamy włączonej to wywołanie BCP zwróci błąd:

Msg 15281, Level 16, State 1, Procedure master..xp_cmdshell, Line 1 [Batch Start Line 0]
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', search for 'xp_cmdshell' in SQL Server Books Online.

Uruchamiamy XP_CMDSHELL

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'xp_cmdshell' changed from 0 to 1. Run the RECONFIGURE statement to install.

Jeśli już ją uruchomiliśmy możemy przystąpić do tworzenia kopii naszej tabeli. W naszym przykładzie będzie to tabela PERSON z naszej bazy_testowej

USE [baza_testowa]
GO
SELECT [imie],[nazwisko],[wiek] FROM [dbo].[person]
GO

imie       nazwisko             wiek
---------- -------------------- -----------
Jan        Nowak                65
Julia      Pawlak               14
Adam       Kowalski             51
Katarzyna  Adamska              42
Anna       Duda                 66

(5 rows affected)

Uruchamiamy BCP z query SQL

DECLARE
@table VARCHAR(128),
@file VARCHAR(255),
@cmd VARCHAR(512)
SET @table = '[baza_testowa].[dbo].[person]'
SET @file = 'G:\SQLBackup\' + @table + '_' + CONVERT(CHAR(8), GETDATE(), 112) + '.bcp'
SET @cmd = 'bcp ' + @table + ' out ' + @file + ' -n -T '
EXEC master..xp_cmdshell @cmd

gdzie @table to nasza tabela której chcemy zrobić kopię.

output
-----------------------------------------------------------------
NULL
Starting copy...
NULL
5 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 1      Average : (5000.00 rows per sec.)
NULL

(7 rows affected)

Na dysku w folderze G:\SQLBACKUP utworzył się plik [baza_testowa].[dbo].[person]_20190220.bcp. Nie powinno się używać dysku C gdyż Windows ma problemy z uprawnieniami do plików na tym dysku (trzeba czasami mieć prawa administratora do ich odczytu)

Możemy to samo polecenie wykonać prościej z linii poleceń.

bcp [baza_testowa].[dbo].[person] OUT G:\SQLBackup\[baza_testowa].[dbo].[person]_20190220.bcp -c -T

W powyższym poleceniu wynik działania jest wyświetlony na ekranie. Można go też uruchomić w trybie cichym gdzie zarówno występujące błędy jak i wynik działania są zapisywane do plików tekstowych Error_out.log i Output_out.log

bcp [baza_testowa].[dbo].[person] OUT G:\SQLBackup\[baza_testowa].[dbo].[person]_20190220_2.bcp -m 1 -n -e G:\SQLBackup\Error_out.log -o G:\SQLBackup\Output_out.log -S -T

Zawartość pliku Output_out.log

Starting copy...

5 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 62     Average : (80.65 rows per sec.)

Jeśli chcemy wykorzystać uwierzytelnianie w bazie danych wydajemy polecenie

bcp [baza_testowa].[dbo].[person] out G:\SQLBackup\[baza_testowa].[dbo].[person]_20190220_3.bcp -c -U -S

Pora odzyskać tabelę z naszego backupu. Jako że polecenie BCP spowoduje odczyt danych z pliku i zapis ich do tabeli aby odzyskać tabelę najpierw należy ją wyczyścić z danych

TRUNCATE TABLE [baza_testowa].[dbo].[person_bcp]

Commands completed successfully.

Sprawdzamy zawartość tabeli

SELECT [imie],[nazwisko],[wiek]
FROM [baza_testowa].[dbo].[person]

imie       nazwisko             wiek
---------- -------------------- -----------

(0 rows affected)

Tabela jest wyczyszczona. Następnie wczytujemy dane z pliku (z linii poleceń)

bcp [baza_testowa].[dbo].[person] IN G:\SQLBackup\[baza_testowa].[dbo].[person]_20190220.bcp -c -T

lub z query w Management Studio

DECLARE @table VARCHAR(128),
@file VARCHAR(255),
@cmd VARCHAR(512)
SET @table = '[baza_testowa].[dbo].[person]'
SET @file = 'G:\SQLBackup\' + @table + '_' + CONVERT(CHAR(8), GETDATE(), 112) + '.bcp'
SET @cmd = 'bcp ' + @table + ' in ' + @file + ' -n -T '
EXEC master..xp_cmdshell @cmd

output
-------------------------------------------------------------------
NULL
Starting copy...
NULL
5 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 1      Average : (5000.00 rows per sec.)
NULL

(7 rows affected)

Sprawdzamy naszą tabelę

SELECT [imie],[nazwisko],[wiek]
FROM [baza_testowa].[dbo].[person]

imie       nazwisko             wiek
---------- -------------------- -----------
Jan        Nowak                65
Julia      Pawlak               14
Adam       Kowalski             51
Katarzyna  Adamska              42
Anna       Duda                 66

(5 rows affected)

Tabela odzyskana

Za pomocą BCP możemy wykonać backup pojedynczej kolumny

bcp "SELECT nazwisko FROM [baza_testowa].[dbo].[person] WITH (NOLOCK)" queryout g:\BCP\nazwisko_[baza_testowa].[dbo].[person].bcp -c -T

pojedynczych wierszy

bcp "SELECT * from [baza_testowa].[dbo].[person] WHERE imie = 'Adam'" queryout G:\BCP\Adam_[baza_testowa].[dbo].[person].bcp -d WideWorldImporters -c -T

lub zbackupować wynik zapytania

bcp "SELECT imie,nazwisko,wiek FROM [baza_testowa].[dbo].[person] ORDER BY wiek" queryout G:\BCP\People.txt -t, -c -T

Możemy też zdefiniować typ pliku backupu

REM non-XML character format
bcp WideWorldImporters.Warehouse.StockItemTransactions format nul -f D:\BCP\StockItemTransactions_c.fmt -c -T

REM non-XML native format
bcp WideWorldImporters.Warehouse.StockItemTransactions format nul -f D:\BCP\StockItemTransactions_n.fmt -n -T

REM XML character format
bcp WideWorldImporters.Warehouse.StockItemTransactions format nul -f D:\BCP\StockItemTransactions_c.xml -x -c -T

Zaszufladkowano do kategorii Microsoft SQL | Dodaj komentarz