Eksport danych z MSSQL-a bezpośrednio do MySQL-a

Dziś pokażę w jaki prosty sposób wyeksportować dane za pomocą jednego polecenia SQL. Wykorzystamy do tego mechanizm LINKED SERVER obecny w MSSQL-u.
Pierwszym krokiem jest stworzenie  łącza do zdalnej maszyny:

/****** Object:  LinkedServer [MYSQL]    Script Date: 09/09/2010 20:33:31 ******/
IF  EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'MYSQL')
 EXEC master.dbo.sp_dropserver @server=N'MYSQL', @droplogins='droplogins'
GO

/****** Object:  LinkedServer [MYSQL]    Script Date: 09/09/2010 20:33:31 ******/
EXEC master.dbo.sp_addlinkedserver 
 @server = N'MYSQL', 
 @srvproduct=N'MySQL', 
 @provider=N'MSDASQL', 
 @provstr=N'Driver=MySQL ODBC 5.1 Driver;SERVER=localhost;UID=root;PWD=tajnehaslo;DATABASE=sprzedaz;PORT=3306;CHARSET=utf8'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin 
 @rmtsrvname=N'MYSQL',
 @useself=N'True',
 @locallogin=NULL,
 @rmtuser=NULL,
 @rmtpassword=NULL

załóżmy że mamy tabelkę w bazie zdefiniowaną jako:

CREATE TABLE [dbo].[tab_import](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [O1] [varchar](30) NULL,
 [L1] [varchar](70) NULL,
 [O2] [varchar](30) NULL,
 [L2] [varchar](70) NULL,
 [O3] [varchar](30) NULL,
 [L3] [varchar](70) NULL,
 [O4] [varchar](30) NULL,
 [L4] [varchar](70) NULL,
 [O5] [varchar](30) NULL,
 [L5] [varchar](70) NULL,
 CONSTRAINT [PK_tab_import] PRIMARY KEY CLUSTERED ([id] ASC)
) ON [PRIMARY]

Posiadamy również tabelkę po stronie MySQL-a zdefiniowaną jaką

CREATE TABLE `tab_import` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `O1` varchar(30) DEFAULT NULL,
  `L1` varchar(70) DEFAULT NULL,
  `O2` varchar(30) DEFAULT NULL,
  `L2` varchar(70) DEFAULT NULL,
  `O3` varchar(30) DEFAULT NULL,
  `L3` varchar(70) DEFAULT NULL,
  `O4` varchar(30) DEFAULT NULL,
  `L4` varchar(70) DEFAULT NULL,
  `O5` varchar(30) DEFAULT NULL,
  `L5` varchar(70) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

i chcemy przetransferować dane z bazy MSSQl do bazy MySQL. Skorzystamy w takim przypadku z możliwości INSERT INTO ... SELECT

INSERT INTO openquery (MYSQL,'select * from test.tab_import where 1 = 0')
                      (O1, L1, O2, L2, O3, L3, O4, L4, O5, L5)
SELECT     O1, L1, O2, L2, O3, L3, O4, L4, O5, L5
FROM         tab_import AS tab_import_1

Ciekawostką w tym układzie jest konstrukcja zagnieżdżonego SELECT-a wykorzystywanego przez OPENQUERY. warunek w tym zapytaniu filtruje wszystkie rekordy gdyż de fakto nie są one nam do niczego potrzebne. Podobna sztuczka nie jest wskazana w przypadku gdybyśmy chcieli wykonać DELETE lub UPDATE.

Dodatki i usprawnienia dla MSSQL w wersji Express

MSSQL w wersji Express to ciekawa baza, tyle że pozbawiona wielu użytecznych narządzi. Dzięki kilku dodatkom praca z tą wersją bazy będzie o wiele prostsza i zaoszczędzi nam mnóstwa pracy.

Automatyzacja

• Automating Backups on SQL Server Express Version - CodeProject
• ExpressMaint

Shulder-y

• Scheduling Jobs in SQL Server Express
• SQLScheduler
• Express Agent for SQL Server Express: Jobs, Jobs, Jobs, and Mail

Dodatki

• Profiler for Microsoft SQL Server 2005/2008 Express Edition
• Enabling Database Mail on SQL Server Express

Menadżery

• Toad

Określenie daty zakończenia miesiąca w SQL-u

Dziś pokażę jak określić koniec miesiąca dla dowolnej daty. Wykorzystam tu pewną sztuczkę związaną z dodawaniem dat za pomocą funkcji DATEADD oraz wyciąganiem części składowych za pomocą funkcji YEAR i MONTH.

declare @dstop as datetime
set @dstop = getdate()
set @dstop =  dateadd(d,-1,dateadd(mm,1,convert(datetime,cast(year(@dstop) as nvarchar(4)) + '-' + RIGHT('0' + cast(MONTH(@dstop) as nvarchar(4)),2) + '-01',120)))

select @dstop

Algorytm postępowania jest następujący:
Wyciągamy Rok i miesiąc i na podstawie tego sklejamy datę określającą pierwszy dzień miesiąca
dodajemy miesiąc do tak otrzymanej daty
dodajemy -1 dzień do otrzymanej wcześniej sumy

Inna metoda to:

declare @dstop datetime
set @dstop = getdate() + 1
set @dstop =  dateadd(d,-day(dateadd(m,1,@dstop)),dateadd(m,1,@dstop))

select @dstop

Sposób ten zaprezentował kolega Bartosz Ślepowroński w tym wątku

Z powodzeniem ten sposób można zastosować w innych dialektach SQL np. JET

MSSQL i polskie nazwy miesięcy i dni tygodnia

W MSSQL-u w bardzo prosty sposób można uzyskać poprawną polską nazwę miesiąca. wystarczy tylko wykonać prostą instrukcję przed wykonaniem głównego zapytania. Chodzi o wymuszenie języka w jakim będą prezentowane dane przez MSSQL. robimy to tak:

SET LANGUAGE Polish

Zaś wykorzystanie możemy zobaczyć tutaj:

select DATENAME (mm,GETDATE()) as [miesiąc], DATENAME (dw,GETDATE()) as [dzień]

Sprawdzenie aktualnego języka możemy za pomocą zmiennych systemowych @@language i @@langid.

SELECT @@language, @@langid

No i możemy również zmienić domyślny język dla loginu za pomocą menagment Studio: Security -> Logins -> Wybrany login , właściwości -> General -> Default Language.


Lub za pomocą T-SQL-a

ALTER LOGIN sa WITH DEFAULT_LANGUAGE = Polish;

Pełną informację o dostępnych językach uzyskamy zaś po wykonaniu komendy:

select * from sys.syslanguages

Szybkie przekodowanie pliku teksowego

Częstą zmorą podczas pracy z plikami jest ich kodowanie. Czasem można sobie poradzić ręcznie jakimś prostym narzędziem np. Notepad++, czasem importujemy plik do Access-a i eksportujemy w żądanym kodowaniu. Te metody się sprawdzają do momentu gdy nie zderzymy się z plikiem wielkości ~1GB. Cóż można wtedy zrobić? Ano skorzystać z dobrodziejstw darmowego narzędzia ICONV dla platformy Win32 czyli Windowsa.
Pierwotnie narzędzie to było dostępne dla systemów rodziny *nix, lecz w chwili obecnej możemy się cieszyć że jest dostępne też dla nas szarych użytkowników okieek.

Pliki wykonywalne Iconv można ściągnąć z adresu: http://gnuwin32.sourceforge.net/packages/libiconv.htm. Do wyboru mamy paczkę zip lub instalator exe. W zależności od wyboru ściągamy żądany plik i wypakowywujemy lub instalujemy.

Załóżmy że plik iconw.exe znajduje się w katalogu: c:\dekoder\, zaś pliki do dekodowania znajdują się w katalogu d:\pliki\. To jak wykorzystać to narzędzie do tego żeby przekodować nasze pliki np. ze strony kodowej UTF-8 do CP1250 (Strona kodowa Windows). Należy wykonać polecenie z wiersza poleceń:

c:\dekoder\iconv.exe -f UTF-8 -t CP1250 d:\pliki\plik.txt > d:\pliki\plik.cp1250.txt

Konstrukcja taka to proste wykonanie instrukcji iconv z przekierowaniem strumienia ">" do nowego pliku. Jest niezwykle wydajna i na średniej klasy sprzęcie przekodowanie pliku o wielkości setek megabajtów zajmuje tylko kilkanaście sekund.

Lista dostępnych stron kodowych jest dostępna po wykonaniu polecenia:

c:\dekoder\iconv.exe -l 

Jednym z ciekawszych zastosowań takiej metody jest przekodowanie pliku przed importem do MSSQl-a. Jest to konieczne gdyż MSSQL nie wspiera tak jakbyśmy chcieli. Cóż można zrobić? ano można użyć procedury systemowej xp_cmdshell to przekonwertorownia pliku.

declare @cmd varchar(2000)
set @cmd = 'c:\dekoder\iconv.exe -f UTF-8 -t CP1250 d:\pliki\plik.txt > d:\pliki\plik_cp1250.txt'
exec xp_cmdshell @cmd

Jeżeli nie będziemy mieli aktywnej procedury xp_cmdshell możemy ją włączyć w następujący sposób:

EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE

Migracja i integracja bazy

Prezentacja zawiera szereg przydatnych wskazówek dotyczących migracji danych oraz aplikacji ze środowiskowa Access do bazy danych MSSQL w wersji 2005.

Migracja I Integracja Bazy

View more presentations from Przemysław ....

Współpraca MSSQL 2008 Express z pakietem Office

Zapraszam wszystkich do lektury materiału, którego jestem autorem.

Dokument OnLine

View more documents from Przemysław ....

Zaznaczam od razu że materiał ten może ewoluować  zgodnie z aktualnymi potrzebami odbiorców. Dlatego też zachęcam do dyskusji na temat treści w nim zawartych.

Ciekawy artykuł o kodowaniu

Dziś szukając informacji na temat kodowania tekstu w MS SQL serwerze natknąłem się na bardzo ciekawy wpis na blogu dotyczący właśnie tej kwestii.
W sposób łatwy i przystępny wyjasnia w jaki sposób użyć funkcji: EncryptByPassPhrase i DecryptByPassphrase.
http://www.pluralsight.com/community/blogs/dan/archive/2006/04/09/21375.aspx

Przedstawione przykłady przetestowałem na MS SQL 2005 Expres Edition.

Zmiana compatibility level

Dziś na stronie wss.pl znalazłem taki oto skrypt
Służy on do sprawdzenia kompatybilności obiektów w bazie danych podczas zmiany compatibility level na wyższy (np. z 2000 do 2005).

if OBJECT_ID('tempdb..#t') is not null
drop table #t

create table #t
(
name sysname not null primary key
, [type] char(2) not null
, error bit not null default 0
, done bit not null default 0
)

insert into #t (name, [type], error, done)
select name, [type], 0, 0 from sys.objects WHERE TYPE IN ('P', 'FN', 'IF', 'TF', 'V') order by name

declare @name sysname
declare @type char(2)

declare @i int
set @i = 0

while exists (select * from #t where done = 0)
begin
set @i = @i + 1
select top 1
@type = type
, @name = name
from #t
where done = 0

BEGIN TRY
IF @type = 'V'
exec sp_refreshview @name
ELSE
exec sp_refreshsqlmodule @name
END TRY
BEGIN CATCH
if XACT_STATE() = -1 rollback tran
update #t set error = 1 where name = @name
END CATCH

update #t set done = 1 where name = @name
print ltrim(str(@i)) + ' - ' + @name

if @i > (select count(*) from #t)
break
end