KATHOLIEKE HOGESCHOOL KEMPEN GEEL
SQL SERVER 2008 WERKING VAN DE SQL COMMAND LINE TOOL
JOEY LEMMENS
WOORD VOORAF “WERKING VAN DE SQL COMMAND LINE TOOL” is bedoeld om mensen wegwijs te maken in deze niet-grafische tool van SQL Server. We zullen hier trachten een basis te schetsen van hoe je deze krachtige tool op een zinvolle en efficiënte manier kan gebruiken. Een niet-grafische omgeving straalt vaak een bepaalde moeilijkheidsgraad uit, maar na het doornemen van deze handleiding ben je ervan bewust dat de SQL command line tool zeer nuttig kan zijn, zelfs zonder al de visuele schoonheid van bijvoorbeeld SQL Management Studio. Bij het schrijven van deze handleiding werd er gebruik gemaakt van diverse officiële websites zoals MSDN. Alle voorbeelden opgenomen in deze handleiding zijn fictief en dienen louter ter ondersteuning. Deze voorbeelden zijn makkelijk zelf uit te voeren. Het enige wat vereist is, is een werkende SQL Server 2008.
2
INHOUDSOPGAVE WOORD VOORAF.................................................................................................................. 2 INHOUDSOPGAVE ................................................................................................................ 3 INLEIDING .............................................................................................................................. 4 1
Werking van de SQL Command Line Tool ............................................................ 5
1.1 1.2 1.3 1.3.1 1.4 1.4.1 1.5 1.6 1.7 1.8 1.8.1 1.8.2 1.8.3
Wat is een Command Line Interface? ......................................................................... 5 Command Line Interfaces in SQL Server ................................................................... 5 Starten van SQLCMD in Windows ............................................................................. 6 Connecteren met een named instance .......................................................................... 6 Een eerste voorbeeld in de nieuwe tool ....................................................................... 7 Query’s in sqlcmd ........................................................................................................ 7 De teksteditor ............................................................................................................... 8 Scripts uitvoeren ........................................................................................................ 10 Uitvoer opslaan .......................................................................................................... 10 Scripting variabelen ................................................................................................... 11 Impliciet scripting variabele aanmaken ..................................................................... 12 Expliciet scripting variabele aanmaken ..................................................................... 12 Scripts uitvoeren op meerdere databases ................................................................... 13
Besluit ...................................................................................................................................... 16 Lijst van de figuren ................................................................................................................ 17 Literatuurlijst ......................................................................................................................... 18
INLEIDING Samen met de installatie van SQL Server 2008 komt een klein maar zeer krachtige tool genaamd, SQLCMD. Deze tool kan gebruikt worden voor verschillende doeleinden. Zo is het mogelijk de SQL server te beheren m.b.v. deze tool, query’s uit de database te extraheren of gewoon scripts uit te voeren die bepaalde acties gaan verrichten. Al deze functies zullen aan bod komen in deze handleiding en zullen op een vlot leesbare en duidelijke manier stap voor stap worden uitgelegd.
4
1
Werking van de SQL Command Line Tool
1.1
Wat is een Command Line Interface?
Een command line interface of kortweg CLI genoemd, is een stukje software om met een besturingssysteem of bepaalde software te interageren. De interactie gebeurt door commando’s in te voeren die een specifieke taak zullen uitvoeren. Nadat het commando is uitgevoerd kan de gebruiker het programma opnieuw aangespreken m.b.v. de CLI door een nieuwe opdracht in te geven. De CLI is eigenlijk de tegenhanger van de grafische gebruikersinterface. Een voorbeeld van een bekende CLI is command.exe die terug te vinden is in de Windows besturingssystemen. Volgende figuur toont het programma command.exe met het commando help.
Figuur 1 - de CLI command.exe in Windows 7
1.2
Command Line Interfaces in SQL Server
Ook Microsoft SQL Server heeft een eigen command line tool namelijk, SQLCMD. Deze CLI werd geïntroduceerd met SQL Server 2005 en vervangt de oudere OSQL en de ISQL tools. Deze twee laatsten worden niet meer ondersteund door SQL Server 2005 en hoger. Voor de leergierigen onder ons: ISQL gebruikte een DB-Library om te communiceren met de SQL Server. Wordt niet meer ondersteund na SQL Server 2000. OSQL gebruikte een ODBC om te communiceren met de SQL Server. Wordt niet meer ondersteund na SQL Server 2005. SQLCMD gebruikt een OLE DB om te communiceren met de SQL Server. Deze tool wordt momenteel ondersteund door SQL Server 2005 en hoger. Elke nieuwe versie van de CLI kende natuurlijk vele verbeteringen t.o.v. de vorige versies. Ook SQLCMD kent enkele voordelen zoals betere prestaties, introductie van scripting variabelen, gebruik van een teksteditor, etc. SQLCMD is compatibel met ISQL en OSQL. Dit wil zeggen dat scripts of batches gemaakt in deze twee tools gebruikt kunnen worden in SQLCMD. De SQLCMD tool wordt vooral gebruikt voor het uitvoeren van Transact-SQL statements, system procedures en script files.
5
1.3
Starten van SQLCMD in Windows
Om de sqlcmd tool te starten open je een opdrachtpromptvenster. De makkelijkste manier om dit te starten in Windows 7 is door te klikken op het Startmenu en vervolgens cmd in het zoekvenster te typen. De standaardlocatie van sqlcmd.exe is terug te vinden onder C:\Program Files\Microsoft SQL Server\100\Tools\Binn. In het opdrachtpromptvenster typ je sqlcmd gevolgd door de entertoets. Vanaf nu is er een connectie met de standaard instantie van de SQL Server die op de computer actief is. Volgende figuur toont het resultaat dat je moet bekomen.
Figuur 2 - starten van sqlcmd in cmd
Merk op dat het connecteren automatisch verloopt omdat hij gebruik maakt van Windows Authenticatie. Wanneer je wil connecteren met SQL Server Authenticatie moet je de extra parameter –U meegeven gevolgd door de gebruikersnaam. Druk vervolgens op de entertoets en geef daarna het passwoord in. Een voorbeeld: C:\Users\user>sqlcmd –U admin
1> is de sqlcmd prompt. Bij elke entertoets verhoogt deze prompt met één. Om de sqlcmd sessie te beëindigen typ je exit in de CLI. 1.3.1 Connecteren met een named instance Als je niet wil connecteren met de standaard instantie van SQL Server kan je het volgende commando ingeven: 1> sqlcmd –S myServer\instanceName
Je vervangt de myServer door de naam van de computer en instanceName door de instantie van SQL Server waarmee je wil connecteren. Bij het connecteren kan je nog veel meer parameters opgeven zoals welke database je wil gebruiken, aanmelden met veilige connectie, etc. Voor de volledige lijst van alle parameters geef je in cmd.exe het volgende commando: C:\Users\user>sqlcmd /?
6
1.4
Een eerste voorbeeld in de nieuwe tool
We connecteren met de standaard instantie van SQL Server op de computer. Vervolgens vragen we de top 3 van de systeemobjecten op. Om deze query uit te voeren typen we vervolgens GO. 1> select top (3) name from sys.objects 2> go
Sqlcmd geeft het volgende resultaat terug:
Figuur 3 - een eerste query
1.4.1 Query’s in sqlcmd In principe kan je elke mogelijke query in deze tool wegschrijven. Je kan een nieuwe database aanmaken met daarin meerdere tabellen en in elke tabel nog eens waarden toevoegen. Het volgende voorbeeld illustreert dit.
Figuur 4 - verschillende query's in sqlcmd
7
Uiteraard is dit niet echt de meest comfortabele manier van werken. Het wordt pas interessanter als we gaan werken met de teksteditor en zo uiteindelijk scripts kunnen inladen of informatie kunnen extraheren uit de SQL Server. Bekijk hiervoor de hoofdstukken 1.6, Scripts uitvoeren en 1.7, Uitvoer opslaan.
1.5
De teksteditor
Een groot voordeel aan de sqlcmd tool is de teksteditor die makkelijk beschikbaar is. We vragen alle databases op die in SQL Server zitten door de volgende commando’s in te geven. 1> select name from sys.databases 2> go
Volgende figuur geeft de uitvoer van alle databases aanwezig op de SQL Server. De uitvoer kan uiteraard verschillend zijn wanneer je dit uitvoert op een andere SQL Server.
Figuur 5 - lijst van databases aanwezig op SQL Server
Wanneer we nu wijzigingen willen aanbrengen gebeurt dit nogal moeizaam. Daarom kunnen we ook gebruik maken van een editor zoals edit.com of notepad. Edit.com is eigenlijk de meest handige editor maar werkt enkel onder 32-bits besturingssystemen. Om de editor te openen typen we het commando :ED. Wanneer we dit uitvoeren op een 64-bits besturingssysteem krijgen we de volgende foutmelding:
Figuur 6 - foutmelding openen editor
8
We gaan de editor veranderen in notepad. Om dit in te stellen verbreken we de connectie met de SQL Server door het commando exit in te geven. Vervolgens geven we het volgende commando in, in het opdrachtpromptvenster van Windows. C:\Users\user> set SQLCMDEDITOR=notepad.exe
Wanneer we terug connecteren en het commando :ED ingeven opent kladblok met daarin de commando’s die werden opgegeven in het sqlcmd venster. Onderstaande figuur toont het verwachte resultaat.
Figuur 7 - resultaat met notepad editor
In het geopende notepad bestand kunnen we heel gemakkelijk het sql statement wijzigen in bijvoorbeeld select name as [database name] from sys.databases. Je slaat het bestand op en je sluit het. Het resultaat wordt onmiddellijk overgenomen in het sqlcmd venster.
9
1.6
Scripts uitvoeren
Bij het inladen van grote bestanden kan je sqlcmd gebruiken om het script te laten uitvoeren. We maken op de C-schijf een mapje ‘sql’ aan met daarin het volgende sql-bestand.
Figuur 8 - aanmaken database in extern sql-bestand
We kunnen vanuit sqlcmd dit bestand oproepen waarna alle statements worden uitgevoerd. Het volgende commando maakt dit mogelijk: C:\Users\user>sqlcmd –e –i C:\sql\test.sql
Bij het drukken op de entertoets verschijnt het volgende resultaat in de CLI:
Figuur 9 - resultaat inladen sql-bestand
We geven twee extra parameters op wanneer we de connectie maken met de SQL Server namelijk: –e en –i. De parameter –e toont de output. Deze parameter kan je best weglaten bij het inladen van grote bestanden. De tweede parameter –i is nodig voor de locatie van het in te laden sql-bestand terug te vinden.
1.7
Uitvoer opslaan
Uitvoer kan je ook opslaan in tekstdocumenten op de computer. We schrijven een transact sql statement dat alle databases teruggeeft met bijhorende id, status en de naam van het mdfbestand. Deze query slaan we op in een sql-bestand dat wordt ingeladen bij het connecteren met de database. Bekijk figuren 10 t.e.m. 12 die het voorbeeld illustreren: 10
Figuur 10 - T-SQL query als invoer
Figuur 11 - commando voor invoer & uitvoer
Figuur 12 - resultaat uitvoer in tekstdocument
In figuur 11 geven we de nieuwe parameters –Y en –o op. De parameter –Y[waarde] zorgt voor de regelafstand. Hoe kleiner de waarde hoe kleiner de regelafstand tussen de verschillende kolommen in figuur 12, het omgekeerd geldt natuurlijk ook. De parameter –o specifieert het pad waar het uitvoerdocument moet komen te staan. In dit voorbeeld is het resultaat terug te vinden onder C:\sql met als documentnaam: testoutput.txt
1.8
Scripting variabelen
Variabelen die gebruikt worden in scripts worden ook wel eens scripting variabelen genoemd. Het gebruik van deze scripting variabelen is een nieuwigheid ten opzichte van osql in de vorige SQL Server edities. Door gebruik te maken van deze scripting variabelen kan een bepaald script in meerdere scenario’s gebruikt worden. Een voorbeeld: als je een script wil uitvoeren op verschillende servers kan je i.p.v. elk script aan te passen, een scripting variabele voor de servernaam gebruiken. 11
1.8.1 Impliciet scripting variabele aanmaken Een scripting variabele impliciet aanmaken gebeurt bij het connecteren van de SQL server. In het volgende voorbeeld hebben we een simpel script die ons een bepaalde kolom teruggeeft uit de tabel customer. ColumnName is in dit voorbeeld de scripting variabele.
Figuur 13 - script met scripting variabele
Deze scripting variabele kunnen we nu opvullen wanneer we connecteren met de SQL server door de parameter –v mee te geven gevolgd door de naam van de scripting variabele en de waarde. Bekijk figuur 14 waar we de waarde firstname toekennen aan de variabele ColumnName
Figuur 14 - toekennen waarde aan scripting variabele in cmd
1.8.2 Expliciet scripting variabele aanmaken In een sqlcmd script kan je gebruik maken van variabelen door gebruik te maken van het commando :setvar. Je kan het later in het script terug oproepen door gebruik te maken van de $() placeholder. Sqlcmd vervangt vervolgens de placeholder door de waarde die werd toegekend aan de variabele. Bestudeer het voorbeeld in figuur 15.
Figuur 15 - declareren van variabelen in sqlcmd
Bovenstaand voorbeeld heeft nogal weinig nut. De bedoeling is om het server-commando interactief te gebruiken zoals wordt geïllustreerd in figuur 16.
12
Figuur 16 - interactiviteit met het setvar-commando
1.8.3 Scripts uitvoeren op meerdere databases Het volgende voorbeeld illustreert het aanmaken van een tabel in meerdere databases m.b.v. scripting variabelen. Deze tabel zal worden aangemaakt in de databases Excel en Test. De beginsituatie in onze proefopstelling ziet er als volgt uit:
Figuur 17 - beginsituatie Object Explorer
Het aanmaken van de tabel gebeurt in een simpel script. In dit script wordt gebruik gemaakt van twee scripting variabelen namelijk: tabel en attribuut. In figuur 18 zie je de create table instructie.
13
Figuur 18 - create table instructie
In een tweede script, zie figuur 19, dat we gaan aanmaken verwijzen we naar het zonet gemaakte script en kennen we aan de scripting variabelen een waarde toe.
Figuur 19 - script met scripting variabelen en verwijzing naar ander script
In figuur 19 merken we toch enkele nieuwe commando’s op zoals :Error, :OUT en :r. :Error
is een output commando dat alle fouten die voorkomen tijdens het uitvoeren van een script wegschrijven naar een bepaald bestand. :Out is een output commando dat alle resultaten van een query wegschrijft naar een bepaald bestand. :r voegt additionele T-SQL statements en sqlcmd commando’s in het statement cachegeheugen toe. Het enige wat nog moet gebeuren is het uitvoeren van het script. Voer de volgende code uit in de opdrachtprompt.
14
C:\Users\user>sqlcmd –i C:\sql\multiple_DB.sql
In het tekstbestand Report.txt dat automatisch werd aangemaakt bij het uitvoeren van bovenstaand commando staat nu de volgende informatie:
Figuur 20 - uitvoer Report.txt
Mochten er fouten zijn gevonden omdat bijvoorbeeld de database niet werd gevonden, zal je deze informatie terugvinden in het tekstbestand errors.txt. In figuur 21 zie je opnieuw de Object Explorer in SQL Server waarbij voor de databases Excel en Test een nieuwe tabel werd gecreëerd.
Figuur 21 - eindsituatie Object Explorer
15
Besluit De kracht van SQLCMD zit vooral in de scripting variabelen die de scripts zeer flexibel en herbruikbaar maken. Ervaren gebruikers van deze tool kunnen eenvoudig scripts uitvoeren op verschillende databases zoals in deze handleiding werd geïllustreerd met een simpel voorbeeld. Uiteraard kunnen ook complexere taken hiermee worden uitgevoerd zoals het backuppen van databases, rapporten laten generen, kortom bijna elke mogelijk taak die je kan inbeelden. Meer informatie kan je altijd vinden op de MSDN-website van Microsoft of door de literatuurlijst te raadplegen die je verder in deze handleiding terugvindt.
16
Lijst van de figuren Figuur 1 - de CLI command.exe in Windows 7 ......................................................................... 5 Figuur 2 - starten van sqlcmd in cmd ......................................................................................... 6 Figuur 3 - een eerste query ......................................................................................................... 7 Figuur 4 - verschillende query's in sqlcmd................................................................................. 7 Figuur 5 - lijst van databases aanwezig op SQL Server ............................................................. 8 Figuur 6 - foutmelding openen editor ........................................................................................ 8 Figuur 7 - resultaat met notepad editor ...................................................................................... 9 Figuur 8 - aanmaken database in extern sql-bestand................................................................ 10 Figuur 9 - resultaat inladen sql-bestand ................................................................................... 10 Figuur 10 - T-SQL query als invoer ......................................................................................... 11 Figuur 11 - commando voor invoer & uitvoer ......................................................................... 11 Figuur 12 - resultaat uitvoer in tekstdocument......................................................................... 11 Figuur 13 - script met scripting variabele ................................................................................ 12 Figuur 14 - toekennen waarde aan scripting variabele in cmd ................................................. 12 Figuur 15 - declareren van variabelen in sqlcmd ..................................................................... 12 Figuur 16 - interactiviteit met het setvar-commando ............................................................... 13 Figuur 17 - beginsituatie Object Explorer ................................................................................ 13 Figuur 18 - create table instructie ............................................................................................. 14 Figuur 19 - script met scripting variabelen en verwijzing naar ander script ............................ 14 Figuur 20 - uitvoer Report.txt .................................................................................................. 15 Figuur 21 - eindsituatie Object Explorer .................................................................................. 15
17
Literatuurlijst Muthusamy, A. K. (2007, 01 17). MS SQL. Opgeroepen op 10 22, 2011, van Database Journal: http://www.databasejournal.com/features/mssql/article.php/3654176/SQLServer-2005-Command-Line-Tool-147SQLCMD148-150-Part-I.htm SQL Server 2008 : The sqlcmd Command-Line Utility. (2010, 10 17). Opgeroepen op 10 22, 2011, van Mscerts: http://mscerts.programming4.us/sql_server/sql%20server%202008%20%20%20the%2 0sqlcmd%20command-line%20utility.aspx Command-line interface. (2011, 10 21). Retrieved 10 21, 2011, from Wikipedia: http://en.wikipedia.org/wiki/Command-line_interface Page, R., & Factor, P. (2008, 01 07). The SQLCMD workbench. Opgeroepen op 10 23, 2011, van Simple-Talk: http://www.simple-talk.com/sql/sql-tools/the-sqlcmd-workbench/ sqlcmd Utility - SQL Server 2008 R2. (sd). Opgeroepen op 10 21, 2011, van MSDN: http://msdn.microsoft.com/en-us/library/ms162773.aspx Using sqlcmd with Scripting Variables. (sd). Opgeroepen op 10 23, 2011, van MSDN: http://msdn.microsoft.com/en-us/library/ms188714.aspx Using the SQLCMD command line utility for SQL SERVER. (sd). Opgeroepen op 10 23, 2011, van SQLBook: http://www.sqlbook.com/SQL-Server/SQLCMD-command-lineutility-13.aspx
18
