Ivara blogs : SQL

Kā sajūgt Excel 2007 ar SQL serveri

ivars.arins — Fri, 27 Mar 2009 09:35:40 GMT

Nezinu, kā jums, bet man reizēm gadās, kad dati no Excel tabulām ir jāiedabū SQL datubāzē. Reizēm vienkārši jāievieto, reizēm nākas izveikt arī dažādas transformācijas. Bieži šiem mērķiem izmantoju Excel funkciju CONCATENATE, ar kuras palīdzību saģenerēju INSERTus, kurus pēc tam izpildu SQL Server Management Studio.

Nesen radās vajadzība izmantot datus no tabulas ar apmēram 30 stabiņiem, no kuriem daudzi bija jāpielāgo atbilstoši vērtībām kādās datubāzes tabulās. Šajā gadījumā rakstīt milzīgo INSERTu, kuri ievietos datus pagaidu tabulā, konkatenāciju likās neprātīgi, tāpēc nolēmu izmēģināt alternatīvu – piesaistīt Excel failu kā saistīto serveri (linked server) SQL serverim un izmantot SQL teikumus, lai darbotos ar šīs tabulas datiem.

Lai varētu SQL serverim piesaistīt Excel 2007 failu kā saistīto serveri, uz datora, kur darbojas SQLs, nepieciešams uzstādīt Office 2007 Data Connectivity Components un izveidot savienojumu. Savienojumu var izveidot izmantojot grafiskos rīkus, bet es dodu priekšroku skriptam, kas izsauc sistēmas procedūru, jo šādu skriptu ir iespējams saglabāt un izmantot atkārtoti.

EXEC sp_addLinkedServer
    @server= 'services',
    @srvproduct = 'ACE 12.0',
    @provider = 'Microsoft.ACE.OLEDB.12.0',
    @datasrc = 'C:\temp\services.xlsx',
    @provstr = 'Excel 12.0; HDR=No'

Tālāk jau var vienkārši izpildīt parastus SQL vaicājumus, kā piemēram:

SELECT * FROM services...sheet1$

vai

SELECT * FROM services...sheet1$ WHERE F2 = 'ddd'

Ja apskatam pievienošanas procedūras parametrus, tad tie īpašus komentārus neprasa. Vienīgais, kas varētu interesēt ir @provstr parametrs, kurā ir iespējams norādīt vai tabula satur stabiņu nosaukumus (HDR=Yes), vai arī tie nav norādīti (HDR=No). Otrajā gadījumā stabiņi būs numurēti, t.i. nosaukti nosaukumos F1, F2, u.t.t.

Ceru, ka kādam noderēs!

T-SQL špikeris

ivars.arins — Wed, 16 Apr 2008 11:08:17 GMT

Kā visi zinām, ir jautājumi, kuri tiek uzdoti atkal un atkal. Tā kā MSDN SQL servera foruma dalībniekiem ir apnicis atbildēt uz mūzšeniem jautājumiem, tad viņi ir sagatavojuši špikeri priekš tiem, kam slinkums meklēt.

Nākošreiz, pirms jautāt, kā izdzēst dublējošās rindas, ieskaties Solutions for Common T-SQL Problems, vai tur jau nav atbilde uz Tavu jautājumu.

Kuros stabiņos ir atļautas NULL vērtības?

ivars.arins — Tue, 08 Jan 2008 14:21:15 GMT

Pēdējā laikā sanāk padarboties ar dažādām DB lietām, kas ļauj izpausties bijušā pielietojuma administratora garam un rakstīt dažādus SQL skriptus.

Ja kādam ievajagās atrast kuros datubāzes tabulu stabiņos ir atļautas NULL vērtības, var noderēt šāds skripts:

SELECT 
    s.name + '.' + t.name AS 'Table', 
    c.name AS 'Column', 
    types.name AS 'Type'
FROM 
    sys.schemas s 
INNER JOIN sys.tables t 
    ON s.schema_id = t.schema_id
INNER JOIN sys.columns c 
    ON t.object_id = c.object_id
INNER JOIN sys.types types 
    ON c.system_type_id = types.system_type_id 
    AND c.user_type_id = types.user_type_id
WHERE
    c.is_nullable = 1
    AND s.name + '.' + t.name IN ('MySchema.MyTable')
ORDER BY 1

Tā kā man šobrīd rit maģistrantūras sesija, kuru cenšos savienot ar darbu, tad rakstīšanai neiznāk veltīt tik daudz laika cik gribētos. Bet nesatraucieties, nekur neesmu pazudis un netaisos pazust. Pacietību un gan jau drīzumā atkal dalīšos ar Jums savā pieredzē.

SQL lietotāju masveida dzēšana

ivars.arins — Fri, 21 Dec 2007 10:02:00 GMT

Nezinu, cik bieži (vai reti) gadās sastapt nepieciešamību izdzēst vairākus datubāzes lietotājus. Tā kā man šorīt darbā šāda vajadzība radās un ātrumā nevarēju iedomāties pareizos atslēgas vārdus, lai atrastu skriptu, kas veic šo darbību, tad nācās patērēt nedaudz laika, lai tādu uzrakstītu.

Tātad, ideja ir vienkārša. Ņemam lietotājus no sistēmas skata sys.sysusers, atfiltrējam pēc kādiem kritērijiem dzēsīsim. Sasaistam rezultātu ar sys.schemas skatu, lai pārbaudītu, vai lietotājs nav saimnieks kādai shēmai. Šāda pārbaude ir nepieciešama tādēļ, ka MS SQL serveris neļauj dzēst lietotājus, kuriem ir kādas shēmas saimnieki. Tālāk jau paliek "ģelo tehņiki".

Rezultāta skripts:

/********************************************************************
 * SP for droping users from database                               *
 * (c) 2008 Ivars "PiRX" Āriņš                                      *
 * e-mail: ivars_a@inbox.lv                                         *
 * !!!NB:Use it on your own risk!!!                                 *
 *==================================================================*
 * Usage:                                                           *
 *    EXEC dbo.CleanUsers                                           *
 *        @WhereClause = ''                                         *
 *        @ExecDrop = '' ;                                          *
 * Prameters:                                                       *
 * @WhereClause - WHERE clause for user filtering from sys.sysusers *
 * @ExecDrop - Indicates whether DROP actualy must be executed      *
 *             (Y - exec DROP, any other value - just simulate)     *
 ********************************************************************/

CREATE PROCEDURE dbo.CleanUsers
    WhereClause nvarchar(300),
    @ExecDrop nchar(1)
AS
BEGIN
    SET NOCOUNT ON

    IF NOT @ExecDrop = 'Y'
        PRINT 'Running in simulation mode'; 

    CREATE TABLE #FilteredUsers (id int);

    DECLARE @UsersSql nvarchar(400);
    SET @UsersSql = 'INSERT INTO #FilteredUsers SELECT uid FROM sys.sysusers ';

    IF LEN(@WhereClause) > 0
        SET @WhereClause = 'WHERE ' + @WhereClause + ';';
    
    SET @UsersSql = @UsersSql + @WhereClause;

    PRINT 'Filtering users using query:';
    PRINT '    SELECT uid FROM sys.sysusers ' +  @WhereClause
    EXEC(@UsersSql);

    DECLARE curs_users CURSOR LOCAL FORWARD_ONLY
    FOR SELECT DISTINCT
        u_id = u.uid, --id
        u_name = u.name,
        s_id = s.schema_id,
        s_name = s.name,
        s_objects = (SELECT COUNT(*) FROM sys.all_objects o WHERE o.schema_id = s.schema_id)
    FROM #FilteredUsers f
    INNER JOIN sys.sysusers u ON f.id = u.uid
    LEFT JOIN sys.schemas s ON s.principal_id = u.uid;

    OPEN curs_users;

    DECLARE @u_id int,
            @u_name nvarchar(255),
            @s_id int,
            @s_name nvarchar(255),
            @s_objects int;

    FETCH NEXT FROM curs_users INTO @u_id, @u_name, @s_id, @s_name, @s_objects;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        --DROP schema if exists and containts no objects
        IF @s_name IS NOT NULL
            IF (@s_objects = 0)
            BEGIN
                PRINT 'Droping schema ' + @s_name;
                IF @ExecDrop = 'Y' 
                    EXEC ('DROP SCHEMA [' + @s_name + '];');
                SET @s_objects = 0;
            END
        --DROP user if doesn't own any objects
        IF (@s_objects > 0)
            PRINT 'Not droping user ' + @u_name + ' because owned schema ' + @s_name + ' contains ' 
                  + CAST(@s_objects AS nvarchar(10)) + ' object(s)';
        ELSE
        BEGIN
            PRINT 'Droping user ' + @u_name;
            IF @ExecDrop = 'Y' 
                EXEC ('DROP USER [' + @u_name + '];');
        END

        FETCH NEXT FROM curs_users INTO @u_id, @u_name, @s_id, @s_name, @s_objects;
    END

    CLOSE curs_users;
    DEALLOCATE curs_users;

    DROP TABLE #FilteredUsers
END
GO

Procedūru izsaucot, tai nodod divus parametrus, @WhereClause, kurā norāda derīgu SQL WHERE nosacījumu (bez atslēgvārda WHERE) un @ExecDrop, ar kuru norāda, vai tiešām izpildīt lietotāju un shēmu dzēšanu, vai tika simulēt skripta darbību, neizpildot DROP operāciju.

Piemēram, lai izdzēstu visus lietotājus ar Windows autentifikāciju, kuru lietotājvārdi nestaur Ivars, skripts ir jāpalaiž šādi:

--Simulācija
EXEC dbo.CleanUsers 
     @WhereClause = N'isntuser = 1 AND name NOT LIKE ''%Ivars%''', 
     @ExecDrop = 'N';
GO

--Izpilde
EXEC dbo.CleanUsers 
     @WhereClause = N'isntuser = 1 AND name NOT LIKE ''%Ivars%''', 
     @ExecDrop = 'Y';
GO

P.S. Nepretendēju uz T-SQL guru statusu, tāpēc pieļauju, ka šo visu varētu uzrakstīt labāk. Manām vajadzībām derēja arī šādi.

Laikam jau zemrindas piezīmes man sāk kļūt par ieradumu. Nekas, būs konsistents veids, kā informēt par dažādām bloga saimnieciskajām lietām. Joprojām gaidu jebkādus komentārus un atsauksmes par rakstīto.

Šodienas ziņa ir tāda, ka esmu pieslēdzis FeedBurner statistiku un tāpēc ar pirmdienu atslēgšu tiešo RSS plūsmu. Tāpēc, lūdzu, pārslēdziet savus lasītājus uz http://feeds.feedburner.com/IvaraBlogs

How to shoot yourself in the foot with nullable types and SQL types *

ivars.arins — Thu, 13 Dec 2007 20:03:00 GMT

Labvakar!

Šovakar vēlos jums pastāstīt par kādu lietu, kuru, iespējams, daži jau uzskata par novecojušu. Tomēr ne visi jau ir pārgājuši uz .NET platformas jaunākajām versijām, kā arī jebkurā laikā katram no mums var sanākt uzturēt legacy kodu. Tāpēc pastāstīšu par kādu .NET SQL datu tipu īpatnību, ar kuru ne pārāk sen saskāros.

Lai iepazīstinātu ar problēmu, varētu sarīkot nelielu konkursu, pajautājot, kāds rezultāts būs šādam kodam, gadījumā, ja no datubāzes atgriezīs NULL vērtību (atbilde jāsniedz, nepārbaudot ar programmu):

String output = SomeMethodThatReturnsDbResultWhichIsNull() ?? "Datubāzē nav datu";
Console.WriteLine(output);

Pirmajā brīdī liekas, ka ar kodu viss ir kārtībā, atgrieztais rezultāts tiek pārbaudīts un rezultāts būs "Datubāzē nav ieraksta", tomēr praksē izrādās savādāk un izpildot šādu kodu iegūst SqlNullValueException. Izrādās, ka gadījumos, kad no datubāzes var tikt atgriezta NULL vērtība, ir jāpārbauda īpašība IsNull, kura tad arī norāda, vai mainīgais satur kādu vērtību. No vienas puses vienkārši, no otras - ir redzēti gadījumi, kad izstrādātāji uz šo ir "iekrituši".

Tiem, kam mans apraksts liekas nesaprotams - lai runā kods:

using System;
using System.Data.SqlTypes;

namespace NullableDemo
{
    class Example
    {
        private SqlGuid _guid = new SqlGuid();

        /// <summary>
        /// Gets the property wrong.
        /// </summary>
        /// <value>Exception :)</value>
        public Guid? PropertyWrong
        {
            get { return _guid.Value; }
        }

        /// <summary>
        /// Gets the property right.
        /// </summary>
        /// <value>null</value>
        public Guid? PropertyRight
        {
            get
            {
                if (_guid.IsNull)
                    return null;

                return _guid.Value;
            }
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            Example example = new Example();
            try
            {
                Console.WriteLine("Right way:");
                Guid guid = example.PropertyRight ?? Guid.Empty;
                Console.WriteLine(guid);

                Console.WriteLine("Wrong way:");
                guid = example.PropertyWrong ?? Guid.Empty;
                Console.WriteLine(guid);
            }
            catch (SqlNullValueException e)
            {
                Console.WriteLine("Exception:");
                Console.WriteLine(e.Message);
            }
        }
    }
}

Un ekrānšāviņš:

^*How to shoot yourself in the foot links: