Mennyi az idő?

Mai villámkérdésünk: az alábbi kódban mi lehet a @num változó két szélső értéke (min/max)?

DECLARE @date datetime, @num float

SET @date = @num

És ha a @date típusa datetime2?

Update csavarral

Mi a történik  akkor, ha egy tábla rekordjait rangsorolnám, és ezt a rangsort rögzíteném is a tábla egy új 

oszlopában? Mondjuk így:

1. UPDATE Buildings SET nRank = ROW_NUMBER() OVER (ORDER BY nHeight);  

Ez történik:

Windowed functions can only appear in the SELECT or ORDER BY clauses.

Szóval csak SELECT vagy ORDER BY. Szerencsére ez nem olyan nagy gond. Használhatunk CTE-t, beágyazott query-t vagy view-t, és már teljesítettük is a fenti megkötést. Hogy pontosan hogyan, az ebből a script-sorból kiderül. A CTE-s verziót be is kopizom ide (szerintem ez a legelegánsabb).

1. WITH Ranking AS  
2. (  
3. SELECT  
4.       nRank  
5.       ,ROW_NUMBER() OVER (ORDER BY nHeight) AS nComputedRank  
6. FROM  
7.       Buildings  
8. )  
9.   
10. UPDATE  
11.       Ranking  
12. SET  
13.       nRank = nComputedRank;  

Nagy rekordszám esetén érdemes elgondolkodni egy indexelt temp tábla használatán. A lemezre írásnak ugyan van költsége, de lehet, hogy ezt az indexek miatt bőven behozzuk az update-nél.

SPID = -2

Hegedűs Miklós kollégám - miután az SSIS a kezei közt lehelte ki a lelkét egy pakk futtatása közben -, észrevette, hogy az SQL Server-en, az SSIS után beragadt session a rejtélyes -2-es azonosítót kapta. Ezt sajnos a KILL parancs nem tudja kezelni, hibával elszáll:

 Msg 6101, Level 16, State 1, Line 1
 Process ID -2 is not a valid process ID. Choose a number between 1 and 2048.

Kis nyomozás után Miki a megoldást is megtalálta Utsab Chattopadhyay blogján, a CONSULTDBA-n. A -2 az elárvult MSDTC session-öknek dedikált azonosító. Ha a session-t kézzel kell lezárnunk, használhatjuk a KILL-t, ami egy SPID-et, vagy egy UOW (Unit Of Work) azonosítót vár paraméterként. Mivel az előbbi csak 1 és 2048 közé eshet, keressünk egy UOW id-t. A CONSULTDBA szerint így:

1. SELECT req_transactionUOW FROM master..syslockinfo WHERE req_spid = -2;

Mivel a syslockinfo egy igen régi darab, és a Microsoft azzal fenyeget, hogy a következő SQL Server verzióban már meg se találjuk, én inkább a sys.dm_tran_locks hívását javaslom:

1. SELECT request_owner_guid FROM master.sys.dm_tran_locks WHERE request_session_id = -2;  

Mindkét select egy guid-ot ad vissza, amivel a KILL már működni fog.

Migrating MySQL to MS SQL

I got a question about the available solutions of migration from MySQL to MS SQL. Well, I have you admit I haven’t experiences of this but I found this topic very interesting. So I looked for a difficulties and possibilities.

I had a guess that I can solve parts of this task with SSIS. But perhaps this isn’t the best solution because SSIS can’t migrate right the procedures, triggers, etc.

I found two better way, both are tools designed directly to this job. One of these options is Spectral Core’s SQLTRAN that you can buy for 999 USD. This can also translate between the two SQL dialects (i.e. inside of stored procedures).

The other way is the free Microsoft migration tool, called SQL Server Migration Assistant (SSMA). This has many options and features too. On the website of this application you will find many useful links and there is a guideline to migrating that contains the most important issues and the main differences between these two databases.

Update:
Additionally, I recently read about one more solution that's called SwisSQL.

Hány bit egy byte?

Két napja kérdezték tőlem, hogy egy bit típusú oszlop mennyivel növeli meg egy sor méretét? Kis gondolkodás után azt válaszoltam, hogy egy byte-tal. És kettő? Két byte-tal - válaszoltam gyanútlanul, és már bele is sétáltam a csapdába.

Az SQL Server ugyanis trükkösen tárolja a bit típusú adatokat. Nyolc bit egy byte, ennek megfelelően nyolc bit típusú oszlop fér el egyetlen byte-on. Ha az oszlopok száma 1 és 8 között van, akkor 1 byte-ot foglalnak, ha 9 és 16 között, akkor 2 byte-ot, és így tovább.

T-SQL lottószám generátor

Komolytalan, szombat esti móka, de legalább jó példa arra, hogy a RAND() függvénnyel hogyan kaphatunk vissza 1 és 90 közti egész számot.

1. DECLARE @i tinyint;  
2. DECLARE @t as table (nNumber tinyint);  
3.   
4. WHILE (SELECT COUNT(*) FROM @t) < 5  
5.     BEGIN  
6.         SET @i = ROUND (((90-1) * RAND()) +1, 0)  
7.         IF NOT EXISTS (SELECT nNumber FROM @t WHERE nNumber = @i)  
8.             BEGIN  
9.                 INSERT INTO @t (nNumber) VALUES (@i)  
10.             END  
11.     END;  
12.       
13. SELECT nNumber FROM @t ORDER BY nNumber;

Akinek ezzel a kóddal sikerül elvinnie az ötöst, az ne feledkezzen meg rólam! ;)

ISNULL vagy COALESCE

Két napja bukkantam egy érdekes cikkre az MSSQLTips-en, Aaron Bertrand-tól, amiben az ISNULL és a COALESCE viselkedését hasonlítja össze. Talán nem egy millió forintos kérdés, de hibakeresésnél, vagy milliszekundumokra kihegyezett lekérdezéseknél jól jöhet, érdemes elolvasni.

Akinek nincs ennyi ideje, vagy csak lusta :), azoknak hajtás utánra kanyarítottam egy összefoglalót.

Láthatatlan szóközök

Ma hajnalban a cég egyik adatkockája a betöltésnél elhasalt. A hibaüzenet pontosan megadta, melyik package, melyik lookup transzformációja a hibás. BIDS-ben futtatva szépen reprodukálható volt a hiba, bizonyos értékeket nem talált meg a referencia táblában. Nosza, a "no match" kimeneten elkaptam néhány rekordot, a hiányzó értékeket pedig SQL-ből ellenőriztem a referencia táblában... És ott voltak. Nahát. Ráadásul úgy nézett ki a dolog, mintha ugyanahhoz a kulcshoz hol találna párt, hol nem.

Nos, egy jó adag kínlódás - és sok guglizás -  után lehullt a lepel: a T-SQL nem törődik a karakteres mezők végén található szóközökkel, az SSIS lookup viszont igen (legalábbis Full Cache esetén). Az igazán gonosz dolog az, hogy gondoltam is erre, és a LEN() függvénnyel ellenőriztem is az értékeket, csakhogy a LEN() szintén nem számolja ezeket a szóközeket.

1. SELECT LEN('WHITESPACE'), LEN('WHITESPACE ')  

Az eredmény 10 és 10, a LEN() az utolsó szóközt nem látja. Viszont a DATALENGTH() már igen:

1. SELECT DATALENGTH('WHITESPACE'), DATALENGTH('WHITESPACE ')  

Itt már 10 és 11 a függvény értéke.

Ilyen esetekben megoldás lehet, ha SSIS-ben a TRIM() függvényt, SQL oldalon pedig az RTRIM()-et használjuk, vagy ha a lookup transzformációnál nem a Full Cache-t állítjuk be.

Jegyezzük meg: az SSIS lookup nem csak a kis- és nagybetűkre érzékeny, de a sorvégi szóközökre is!

Észrevettétek...

...hogy az SQL és a sör ugyanazzal a betűvel kezdődik? Ez nem lehet véletlen! :) Ez az összefüggés különösen kidomborodik egy HUG-MSSQL meetupon, ahol kényelmesen hátradőlve, hideg sörrel a kezedben hallgathatsz előadásokat az MS SQL világából, olyan szakiktól, mint Horváth Zoltán vagy Berke János. Ha van olyan téma, ami különösen érdekel, bátran dobd be a javaslatok közé!

TekTeeShirts.com

Ha érdekel az MS SQL és szeretnél többet tudni róla, mindenképp érdemes csatlakoznod a HUG-MSSQL-hez. Aki pedig BI-os vagy DBA, annak kötelező! :)

Találkozzunk a következő meetupon!

SSIS csomagok listája 2.

Első posztomban írtam arról, hogyan kérdezhetjük le az SSIS csomagok listáját PowerShell-el SQL Server 2008 alatt. Az új, 2012-es kiadásban azonban egy teljesen új módszert és környezetet kapunk csomagjaink telepítéséhez és kezeléséhez. (Ezzel párhuzamosan – a korábbi SQL Server-ekkel való kompatibilitás miatt – a korábbi struktúra is megmarad egyelőre.)

Ha a csomagok listájára van szükségünk, az SQL Server 2012 (RC0)-ban az SSISDB-ben (T-SQL) vagy a Microsoft.SqlServer.Management.IntegrationServices névtérben (.Net, PowerShell) kell kutakodnunk. A T-SQL megoldás nagyjából így néz ki:

1. use SSISDB  
2. go  
3.   
4. select  
5.     pack.name as [PackageName]  
6.     ,pack.[description] as [Description]  
7.     ,fold.name as [Folder Name]  
8.     ,proj.name as [Project Name]  
9.     ,fold.created_by_name as [OwnerName]  
10.     ,proj.created_time as [Project Created Time] 
11.     ,proj.last_deployed_time as [Project Last Deployed Time]  
12.     ,cast (pack.version_major as varchar(10))
13.      + '.' + cast (pack.version_minor as varchar(10))  
14.      + '.' + cast (pack.version_build as varchar(10))  
15.      as [Version]  
16.     ,pack.version_comments  
17.     ,proj.object_version_lsn as [Project Version]  
18. from  
19.     catalog.packages pack  
20.     inner join catalog.projects proj on pack.project_id = proj.project_id  
21.     inner join catalog.folders fold on proj.folder_id = fold.folder_id;  

Érdemes azonban alaposabban végigbogarászni az SSISDB-t, mert olyan információkra bukkanhatunk, amikről korábban nem is álmodtunk (pl. catalog.executions).

PowerShell-el közelítve a dologhoz, hasonló listát kaphatunk, ha végigzongorázzuk a Catalogs.Folders.Projects.Packages matrjoskát:

1. Add-type -path "C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.IntegrationServices\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.IntegrationServices.dll";  
2.   
3. $ssis = New-Object Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices;  
4. $constr = "Data Source=localhost;Initial Catalog=master;Integrated Security=SSPI;"                          
5.   
6. $con = New-Object System.Data.SqlClient.SqlConnection $constr  
7. $ssis.Connection = $con;  
8.   
9. # Well, we can use here a foreach in the catalogs, but currently only the SSISDB catalog supported.  
10. $folders = $ssis.Catalogs["SSISDB"].Folders;  
11.   
12. $regex = [regex]"(?i)[0-9]"  
13.   
14. foreach ($folder in $folders)  
15.     {      
16.     foreach ($project in $folder.Projects)  
17.         {          
18.         $project.Packages | ft name, `  
19.             @{name="Folder name"; expression={$folder.name}}, `  
20.             @{name="Project name"; expression={$project.name}}, `  
21.             @{name="Actual Version"; expression={$project.objectversionLsn}; align="center"}, `  
22.             @{name="All Version"; expression={$regex.matches($project.versions)}; align="center"}, `  
23.             @{name="Last Deployed Time"; expression={$project.LastDeployedTime.LocalDateTime}; align="center"} `  
24.             -autosize -wrap | out-string | sort -property name;  
25.         }        
26.     };  

Persze, az új struktúra előnyei nem a csomagok listázásánál csúcsosodnak ki. Az SSIS 2012 (RC0) egyik remek újdonsága például, hogy a már telepített project verziók között szabadon válthatunk (feltéve, hogy az SSISDB katalógus Periodically Remove Old Versions tulajdonsága False, mert ellenkező esetben a Maximum Number of Versions per Project-nél régebbi verzióinkat az SQL Server szanálja).

Erre – természetesen – nem csak a GUI-n keresztül van lehetőségünk. Az SSISDB tárolt eljárásai közt ott lapul a catalog.restore_project,

1. exec catalog.restore_project 'Sample Folder', 'Sample Project', 5;  

az IntegrationServices assembly-ben pedig az Restore() metódus.

1. $ssis.Catalogs["SSISDB"].Folders["Version Rollback Sample"].Projects["Sample Project 3"].Versions[5].Restore();  

(Az $ssis változóból sejthető, de azért tisztázzuk: ez akkor működik, ha az adott session-ben már betöltöttük az assembly-t és csatlakoztunk a szerverhez (ő lenne az $ssis)).

Hogyan nézhetem meg az Excel által generált MDX-et?

Például az OLAP PivotTable Extensions nevű bővítménnyel. És ha nincs ilyen a gépen? Akkor természetesen PowerShell-el :)

1. add-type -assemblyname "Microsoft.Office.Interop.Excel";  
2. $xls = [System.Runtime.InteropServices.Marshal]::GetActiveObject("Excel.Application");  
3. $xls.workbooks.item(1).Worksheets.item(2).PivotTables(1).MDX;  

Ha változik a pivot, csak az utolsó sort kell újra meghívni. Viszont a kimenet típusa betűleves, ezzel még kezdeni kell valamit.

Egy egyszerű megoldás lehet az MDX Studio Online (by Mosha Pasumansky), vagy elcsenhetjük a fent említett add-in trükkjét, ami Nick Medveditskov webservice-ét használja. Ezzel öt sorosra bővül a szkript:

1. add-type -assemblyname "Microsoft.Office.Interop.Excel";  
2. $xls = [System.Runtime.InteropServices.Marshal]::GetActiveObject("Excel.Application");  
3. $mdx = $xls.Workbooks.item(1).Worksheets.item(2).PivotTables(1).MDX;  
4. $mdxformatter = new-WebServiceProxy -uri http://formatmdx.msftlabs.com/formatter.asmx;  
5. $mdxformatter.FormatAsPlainText($mdx);  

Na, így mindjárt más.

Megjegyzés: a Workbooks és a Worksheets után nem lehet elhagyni az Item elemet, az indexek 1-től kezdődnek.

Package helyett Project

Elkészült SSIS csomagjainkat eddig kissé nehézkesen juttathattuk rendeltetési helyükre – az Analysis Services-hez vagy a Reporting Services-hez hasonlítva mindenképpen. Születtek izgalmas handmade megoldások (pl. kötegelt package export SSIS-szel vagy akár PowerShell-el), de azért hiányzott egy igazán korrekt megoldás. Szerencsére ezt a Microsoftnál is érzékelték, ezért a 2012-es Integration Services-ben megjelent a Project Deployment Model.

Koen Verbeeck szerint, amikor az SSAS-csapat előállt a Tabular Model-lel, az SSIS fejlesztőknél betelt a pohár: ”Mindenki az SSAS-re figyel majd! Hát egy frászt! Mostantól az SSIS-ben is lesz egy extra fejlesztési modell!” És lőn. J

A legfontosabb újdonságok:

• Alapvető változás, hogy nem egy package-et, hanem egy egész project-et kezel egy egységként.
• Az elkülönülő konfigurációk helyett paramétereket használhatunk, ezek a project részeként kerülnek telepítésre.
• Közvetlenül egy SQL Server instance-ra kerül a project, az Integration Services Catalogs alá.
• A végrehajtás közvetlenül az SQL Serveren történik, nincs szükség a DTExec utilra.
• A végrehajtás során az eseményeket automatikusan kezeli és tárolja, ezek később T-SQL-el lekérdezhetők.
• Verziókövetés az SQL Serveren. Minden project-telepítés során automatikusan létrejön egy új verzió, és pár kattintással vissza lehet állni egy előző verzióra. Ez jól hangzik, ugye?
• Microsoft.SqlServer.Management.IntegrationServices. Egy teljesen új assembly az új struktúrához.

A Project Deployment Model-ben tehát már nem egy SSIS instance-ra kerülnek a csomagok, hanem az SQL Server-re, az Integration Services Catalogs node alá. Itt először létre kell hoznunk egy katalógust: jobb klikk, majd Create Catalog.... A felugró ablakban engedélyeznünk kell a CLR integrációt, és kötelezően meg kell adnunk egy jelszót.

Jelenleg egyetlen, SSISDB nevű katalógust hozhatunk itt létre, amivel automatikusan létrejön egy ugyanilyen nevű adatbázis is (és két login, plusz egy SSIS Server Maintenance nevű job is, csak a teljesség kedvéért).

Ha már létezik az SSISDB, nyissunk meg az SSDT-ben (Sql Server Data Tools, a BIDS utódja) egy Integration Services projectet. A Solution Explorer-ben kattintsunk jobb gombbal a projectre és a megjelenő menüből válasszuk a Deploy-t. Ezzel elindítunk egy varázslót, ami pár lépésben végigvisz a telepítésen. A kihelyezett projectet már az SSMS-ből tudjuk menedzselni.

Akár a Deploy, akár a Build parancsot választjuk, automatikusan létrejön egy .ispac kiterjesztésű fájl (a helyét a project tulajdonság-lapján adhatjuk meg). Dupla kattintásra az SSDT-ben megismert Deployment Wizard indul el, a project így is telepíthető.

Változás esetén az új verziót ugyanígy élesíthetjük, az új telepítés automatikusan felülírja a régebbit. (Egy apró betűs figyelmeztetés jelenik meg csupán a Deployment Wizard ablakának alján.)

Nagyot nem hibázhatunk, mivel SQL Serveren bármelyik telepített project verzióra visszaállhatunk. Az SSMS Object Explorerében kattintsunk jobb gombbal a project nevére, és a helyi menüben válasszuk a Versions…-t. 

A felugró ablakban megjelenő verziók közül pedig jelöljük be azt, amelyiket használni akarjuk, szabadon léphetünk előre-hátra a verziók között.

(NEM a Current mezőt kell kipipálni, hanem csak rá kell állni az adott sorra, aztán katt a Restore to Selected Version gombra.)

Az új rendszer mindezek mellett még számos újdonságot tartogat. Új táblák, nézetek és tárolt eljárások, valamint igen részletes beépített riportok segítik a munkát.

Az új Project Deployment Model mellett a korábbi telepítési modell is elérhető még, Package Deployment Model néven találhatjuk meg (néhol Legacy Deployment Model-ként is hivatkoznak rá). A SSIS fejlesztői környezetben már az új modell az alapértelmezett, de a korábbit is lehet használni, ha átkonvertáljuk a projectet.

SSIS csomagok listája 1.

Az Addictive Measures blogon találtam nemrég egy hasznos T-SQL szkriptet, ami kilistázza az MSDB alá telepített SSIS csomagokat. Hasznos cucc, rajtam viszont egyre jobban elharapózik a PowerShell mánia (bárcsak időm is lenne rá), ezért kicsit utánajártam, vajon nem lehet ezt összehozni PS-sel is? Hát persze hogy de :)

A GetDtsServerPackageInfos eljárásnak egy szerver és egy mappa nevét kell megadnunk, és már ontja is az információkat. A szerver azonosításánál pontos nevet, vagy ˝.˝-t használjunk, a ˝(local)˝ vagy ˝localhost˝ nem működik.

1. param([string]$folder = "\", [string]$server = ".", $drill = 0);  
2.   
3. # Get assembly (this version come with SQL Server 2012 RC0)  
4. $dll = "C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.SqlServer.ManagedDTS\v4.0_11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.ManagedDTS.dll"  
5. add-type -path $dll;  
6.   
7. $ssisapp = new-object Microsoft.SqlServer.Dts.Runtime.Application;  
8. $packages = $ssisapp.GetDtsServerPackageInfos($folder, $server);  
9.   
10. # Get output  
11. $packages `  
12.     | select name, description, flags, folder, creationdate, packagedatasize, `  
13.     @{name="version";expression={[string]$_.versionmajor + "." + [string]$_.versionminor + "." + [string]$_.versionbuild}}, `  
14.     versioncomments `  
15.     |sort name | format-table -autosize;  
16.   
17. # Drill down if it's needed  
18. if ($drill -eq 1)  
19.     {  
20.     foreach ($pkg in $packages)  
21.         {  
22.         if ($pkg.flags -eq "Folder")  
23.             {  
24.             $subfolder = $folder + "\" + $pkg.name; 
25.             $subfolder = $subfolder -replace "\\\\", "\";  
26.               
27.             .\pkg_list $subfolder $server 1;  
28.             }  
29.         }  
30.     };  

(A syntax highlighter egy kicsit belezavarodott a backslash-ekbe. A kód helyes, csak a színezés nem. Sorry.)


update: a fenti szkript akkor fut hibátlanul, ha pkg_list.ps1 néven mentjük el, és abból a könyvtárból futtatjuk, ahová tettük. Ha a $folder mappa alatti almappák tartalma is kell, akkor $drill=1 paraméterrel hívjuk meg. Pl.: .\pkg_list "MSDB" "." 1. Ilyenkor saját magát hívja, pkg_list néven. Már a következő poszton járt az eszem. :)

A GetDtsServerPackageInfos meghívásához a Microsoft.SqlServer.ManagedDTS assembly-re van szükségünk. Csakhogy ennek a dll-nek az SQL Server 2012 RC0-val már a .NET 4-es verziója érkezik. Mivel a PowerShell a .NET 2-re van felkészítve, ez gondot jelent. Két lehetőségünk van:

1. Ha a gépen fut SQL 2005/2008/2008R2, akkor előkereshetjük egy korábbi verzióját. Például: C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SQLServer.ManagedDTS.dll
2. Engedélyezzük a .NET 4-et a PowerShell környezetünkben egy config fájl segítségével. Erről bővebben pl. itt vagy itt lehet infót találni.

Az SSIS 2012-es kiadásával azonban egy teljesen új csomag-telepítő metódus (deploy mode) érkezik, ami átveszi a helyét a korábbi megoldásoknak. A kompatibilitás miatt ugyan továbbra is választható a korábbi módszer, és ugyanezért megmaradtak az msdb.dbo.sysssis... táblák is, de az új módszerhez egy teljesen új struktúra tartozik.

A következő poszt erről fog szólni. Maradjanak velünk, a szünet után folytatjuk! ;)