Zine.net online

Witaj na Zine.net online Zaloguj się | Rejestracja | Pomoc
w Szukaj

dev2dev

  • Sesja na VirtualStudy.pro o automatyzacji testów dla procedur składowanych i funkcji w SQL Server

    Wznowiłem sesje na Virtual Study. Zapraszam. Link tutaj.



    opublikowano 22 marca 2014 21:06 przez marekpow | 0 komentarzy
    Filed under:
  • Ostrożnie z tą siekierką Eugeniuszu!

    Wracam do blogowania po dłuższej przerwie (ech, minęło dokładnie dwa lata!).

    Jako rasowy programista T-SQL nie byłem entuzjastą Linq2SQL ale postanowiłem się z nim zapoznać bliżej.
    Miałem dość archaicznie zbudowaną bazę (nie była mego autorstwa!) i spróbowałem zrobić dość proste zapytanie oparte na złączeniu dwóch tabel.

    Przedstawię problem w uproszczeniu nie podając oryginalnych tabel bo by się wydało o jaki system chodzi :)

    Tabele są dwie i oto ich definicje:

    CREATE TABLE [dbo].[T1](
        [Id] [int] NULL,
        [Col] [varchar](50) NULL,
        [Key] [int] IDENTITY(1,1) NOT NULL,
     CONSTRAINT [PK_T1] PRIMARY KEY CLUSTERED
    (
        [Key] 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

    SET ANSI_PADDING OFF



    Oraz druga tabela:

    CREATE TABLE [dbo].[T2](
        [Id] [int] NULL,
        [Col] [nchar](10) NULL,
        [Key] [int] IDENTITY(1,1) NOT NULL,
     CONSTRAINT [PK_T2] PRIMARY KEY CLUSTERED
    (
        [Key] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
    ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    W aplikacji utworzyłem model w Entity Framework:




    Tabele miały następujące dane:

    Tabela T1:



    Tabela T2:



    W LINQ zrobiłem zapytanie łączące te tabel poprzez INNER JOIN.
    Złączenie będzie na kolumny null-owalne Id w obu tabelach.

    oto fragment programu w C#, który to realizował:



    Przy tych danych spodziewałem się dwóch rekordów w wyniku dla id równego 1 i 2.

    Uruchomiłem program i otrzymałem wynik:



    A co robi tutaj ten rekord podkreślony przez mnie czerwoną ramką? odpowiedź może przynieść jedynie SQL Server Profiler.
    Podsłuchując to zapytanie otrzymujemy odpowiedź:



    W czerwonej ramce mamy odpowiedź dlaczego otrzymaliśmy taki wynik

    OR (([Extent1].[Id] IS NULL) AND ([Extent2].[Id] IS NULL))

    Ten fragment zapytania musi budzić trwogę u programisty bazy danych. LINQ po prostu traktuje NULL jako wartość! W Lin2Objects może sobie tak robić ale w Linq2SQL nie ma prawa tak postępować. Edgar Frank Codd przewraca się w grobie.

    A wracając do tytułu notki, to fani zespołu Pink Floyd wiedzą o co chodzi. Tym, którzy nie znają polecam ostatnie fragmenty tego utworu.

    opublikowano 17 listopada 2012 21:34 przez marekpow | 9 komentarzy
    Filed under:
  • SQL Server Profiler - zrzut śledzenia do pliku tekstowego


    Konieczność wykonania śledzenia pewnego procesu - składającego się z wielu instrukcji -  po raz kolejny okazała się nazbyt uciążliwa. Wiedząc o tym, że śledzenie można zapisać do pliku XML, postanowiłem wykorzystać możliwości XML w SQL Server.

    Najpierw trzeba śledzenie zapisać do pliku:



    Przykładową zawartość tego pliku kopiujemy do zmiennej typu XML w New Query w SQL Server Management Studio:

    declare @xml xml = N'<?xml version="1.0" encoding="utf-16"?>
    <TraceData xmlns="http://tempuri.org/TracePersistence.xsd">
      <Header>
        <TraceProvider name="Microsoft SQL Server" MajorVersion="9" MinorVersion="0" BuildNumber="3042" />
        <ServerInformation name="SRV" />
        <ProfilerUI>
          <OrderedColumns>
            <ID>27</ID>
            <ID>1</ID>
            <ID>10</ID>
            <ID>6</ID>
            <ID>11</ID>
            <ID>18</ID>
            <ID>16</ID>
            <ID>17</ID>
            <ID>13</ID>
            <ID>9</ID>
            <ID>12</ID>
            <ID>14</ID>
            <ID>15</ID>
          </OrderedColumns>
          <TracedEvents>
            <Event id="196">
              <EventColumn id="12" />
              <EventColumn id="1" />
              <EventColumn id="9" />
              <EventColumn id="6" />
              <EventColumn id="10" />
              <EventColumn id="14" />
              <EventColumn id="11" />
            </Event>
            <Event id="12">
              <EventColumn id="15" />
              <EventColumn id="16" />
              <EventColumn id="1" />
              <EventColumn id="9" />
              <EventColumn id="17" />
              <EventColumn id="6" />
              <EventColumn id="10" />
              <EventColumn id="14" />
              <EventColumn id="18" />
              <EventColumn id="11" />
              <EventColumn id="12" />
              <EventColumn id="13" />
            </Event>
            <Event id="13">
              <EventColumn id="12" />
              <EventColumn id="1" />
              <EventColumn id="9" />
              <EventColumn id="6" />
              <EventColumn id="10" />
              <EventColumn id="14" />
              <EventColumn id="11" />
            </Event>
          </TracedEvents>
        </ProfilerUI>
      </Header>
      <Events>
        <Event id="65534" name="Trace Start">
          <Column id="14" name="StartTime">2010-11-11T14:04:17.687+01:00</Column>
        </Event>
        <Event id="13" name="SQL:BatchStarting">
          <Column id="1" name="TextData">
    EXECUTE [dbo].[OrderAdd] </Column>
          <Column id="9" name="ClientProcessID">7396</Column>
          <Column id="11" name="LoginName">mpowichrowski</Column>
          <Column id="6" name="NTUserName">mpowichrowski</Column>
          <Column id="10" name="ApplicationName">Microsoft SQL Server Management Studio - Query</Column>
          <Column id="12" name="SPID">69</Column>
          <Column id="14" name="StartTime">2010-11-11T14:04:22.56+01:00</Column>
        </Event>
    <Event id="12" name="SQL:BatchCompleted">
          <Column id="11" name="LoginName">Sharepoint</Column>
          <Column id="15" name="EndTime">2010-11-11T14:20:45.95+01:00</Column>
          <Column id="6" name="NTUserName">Sharepoint</Column>
          <Column id="10" name="ApplicationName">Windows SharePoint Services</Column>
          <Column id="12" name="SPID">65</Column>
          <Column id="14" name="StartTime">2010-11-11T14:20:45.95+01:00</Column>
          <Column id="16" name="Reads">0</Column>
          <Column id="18" name="CPU">0</Column>
          <Column id="1" name="TextData">select collationname(0x0904100000)</Column>
          <Column id="9" name="ClientProcessID">2612</Column>
          <Column id="13" name="Duration">155</Column>
          <Column id="17" name="Writes">0</Column>
        </Event>
        <Event id="13" name="SQL:BatchStarting">
          <Column id="1" name="TextData">exec sp_oledb_ro_usrname</Column>
          <Column id="9" name="ClientProcessID">2612</Column>
          <Column id="11" name="LoginName">Sharepoint</Column>
          <Column id="6" name="NTUserName">Sharepoint</Column>
          <Column id="10" name="ApplicationName">Windows SharePoint Services</Column>
          <Column id="12" name="SPID">67</Column>
          <Column id="14" name="StartTime">2010-11-11T14:20:53.95+01:00</Column>
        </Event>
        <Event id="12" name="SQL:BatchCompleted">
          <Column id="11" name="LoginName">Sharepoint</Column>
          <Column id="15" name="EndTime">2010-11-11T14:20:53.95+01:00</Column>
          <Column id="6" name="NTUserName">Sharepoint</Column>
          <Column id="10" name="ApplicationName">Windows SharePoint Services</Column>
          <Column id="12" name="SPID">67</Column>
          <Column id="14" name="StartTime">2010-11-11T14:20:53.95+01:00</Column>
          <Column id="16" name="Reads">4</Column>
          <Column id="18" name="CPU">0</Column>
          <Column id="1" name="TextData">exec sp_oledb_ro_usrname</Column>
          <Column id="9" name="ClientProcessID">2612</Column>
          <Column id="13" name="Duration">242</Column>
          <Column id="17" name="Writes">0</Column>
        </Event>   
      </Events>
    </TraceData>'

     
    I najważniejszy fragment, przetworzenie tego XML w w inny:

    declare @LoginName varchar(100)
    declare @DatabaseName varchar(100)
    declare @ServerName varchar(100)
    declare @ApplicationName varchar(100)
    declare @Action varchar(100)

    -- set @Action = 'Completed'    -- 'Started'

    --set @LoginName = ''
    --set @DatabaseName = ''
    --set @ServerName = ''
    --set @ApplicationName = ''

    ;with xmlnamespaces( default 'http://tempuri.org/TracePersistence.xsd')
    select char(10)+
    '----------------------------------------------'+char(10)+
    '-- Action: '+Action+char(10)+
    '-- Start time: '+isnull(convert(char(30),StartTime,120),'')+char(10)+
    '--   End time: '+isnull(convert(char(30),EndTime,120),'')+char(10)+
    '----------------------------------------------'+
    char(10)+TextData+
    char(10)+char(10)
    from (
    select *, row_number() over (order by StartTime) rn
    from (
    select    case when t.c.value('@name','nvarchar(100)') like '%Completed%' then 'Completed' else 'Started' end Action,
            t.c.value('(Column[@name="StartTime"])[1]','nvarchar(30)') StartTime,
            t.c.value('(Column[@name="EndTime"])[1]','nvarchar(30)') EndTime,
            t.c.value('(Column[@name="TextData"])[1]','nvarchar(max)') TextData
    from @xml.nodes('/TraceData/Events/Event') t(c)
    where    (@LoginName is null or t.c.value('(Column[@name="LoginName"])[1]','nvarchar(100)') = @LoginName) and
            (@ServerName is null or t.c.value('(Column[@name="ServerName"])[1]','nvarchar(100)') = @ServerName) and
            (@DatabaseName is null or t.c.value('(Column[@name="DatabaseName"])[1]','nvarchar(100)') = @DatabaseName) and
            (@ApplicationName is null or t.c.value('(Column[@name="ApplicationName"])[1]','nvarchar(100)') = @ApplicationName)
            ) q
    ) r
    where @Action is null or Action = @Action
    for xml path('')


    I po uruchomieniu tego query



    klikamy w link otrzymując przetworzony trace profilera w zapis w pliku tekstowym, który możemy wygodnie przeglądać i przeszukiwać.


    ----------------------------------------------
    -- Action: Started
    -- Start time: 2010-11-11T14:04:22.56+01:00 
    --   End time:                              
    ----------------------------------------------

    EXECUTE [dbo].[OrderAdd]


    ----------------------------------------------
    -- Action: Completed
    -- Start time: 2010-11-11T14:20:45.95+01:00 
    --   End time: 2010-11-11T14:20:45.95+01:00 
    ----------------------------------------------
    select collationname(0x0904100000)


    ----------------------------------------------
    -- Action: Started
    -- Start time: 2010-11-11T14:20:53.95+01:00 
    --   End time:                              
    ----------------------------------------------
    exec sp_oledb_ro_usrname


    ----------------------------------------------
    -- Action: Completed
    -- Start time: 2010-11-11T14:20:53.95+01:00 
    --   End time: 2010-11-11T14:20:53.95+01:00 
    ----------------------------------------------
    exec sp_oledb_ro_usrname



    Odpowiednio ustawiając wartości zmiennych (których nazwy są bardzo czytelne) możemy wykonać odfiltrowania interesujących nas danych:
    -- set @Action = 'Completed'    -- 'Started'

    --set @LoginName = ''
    --set @DatabaseName = ''
    --set @ServerName = ''
    --set @ApplicationName = ''

    Pozdrawiam wszystkich, którzy będą śledzić przy pomocy tej metody.
    opublikowano 11 listopada 2010 14:40 przez marekpow | 0 komentarzy
    Filed under: , ,
  • Walidacja daty w schemacie XML w SQL Server 2005

    Tworzę komunikację między Service Broker'ami działającymi na odległych instancjach serwerów i na dodatek działajacymi na dwóch wersjach (2005 i 2008). Budując ich infrastrukturę postanowiłem stworzyć typy komunikatów z walidacją opartą o schematy XSD.
    Powiedzmy, że stworzyłem sobie typizowany dokument XML w SQL Server 2005. Niech ten dokument ma następujący kształt:

    <Doc Id="837453847598" Date="2010-10-17T15:47:14.140">
        <Items>
            <Item>
                <Whatever>:-)</Whatever>
            </Item>
        </Items>
    </Doc>


    Do tego dokumentu wygenerowałem stosowną schema collection:

    create xml schema collection MojaSchema as N'
    <xs:schema attributeFormDefault="unqualified"
               elementFormDefault="qualified"
               xmlns:xs="http://www.w3.org/2001/XMLSchema"
               xmlns:sql="http://schemas.microsoft.com/sqlserver/2004/sqltypes">
        <xs:element name="Doc">
            <xs:complexType>
                <xs:sequence>
                    <xs:element name="Items">
                        <xs:complexType>
                            <xs:sequence>
                                <xs:element name="Item">
                                    <xs:complexType>
                                        <xs:sequence>
                                            <xs:element name="Whatever" type="xs:string"  />
                                        </xs:sequence>
                                    </xs:complexType>
                                </xs:element>
                            </xs:sequence>
                        </xs:complexType>
                    </xs:element>
                </xs:sequence>
                <xs:attribute name="Id" type="xs:unsignedLong" use="required" />
                <xs:attribute name="Date" type="xs:dateTime" sql:DbType="datetime" use="required" />
            </xs:complexType>
        </xs:element>
    </xs:schema>'

    I teraz pozostało nam utworzyć typizowany obiekt XML zgodny z tym schema collection:

    declare @xml xml(MojaSchema)

    set @xml = N'
    <Doc Id="837453847598" Date="
    2010-10-17T15:47:14.140">
        <Items>
            <Item>
                <Whatever>:-)</Whatever>
            </Item>
        </Items>
    </Doc>'


    I tu czeka nas niemiła niespodzianka:

    Msg 6926, Level 16, State 1, Line 2
    XML Validation: Invalid simple type value: '2010-10-17T00:00:00.000'. Location: /*:Doc[1]/@*:Date


    Problem wynika z błędu walidacji względem typu xs:datetime w wersji 2005 (w wersji 2008 nie ma tego problemu).
    Aby nastąpiła poprawna walidacja wartość atrybuty Date powinna być z określeniem timezone.
    Ale funkcje datetime w wersji 2005 nie obsługują timezone natomiast w wersji 2008 i owszem obsługują (datetimeoffset), tylko nie walidują się z typem xs:dateTime a i nie konwertują się do typu datetime po stronie wersji 2005. Nic tylko pisać własną obsługę timezone.
  • Polskie wyzwania z T-SQL, Barca, śledzie w kurkach. itp, itd...

    Paweł Potasiński, polski SQL Server MVP zarzekał się, że zorganizuje kiedyś jedną edycję odpowiednika TSQL Challenges (TC). I jak powiedział tak zrobił. Można się z tym zadaniem zapoznać tu.

    Moje pierwsze wrażenia? Zadanie jest trafione w 10. Na TC zdarzają się często zadania odbiegające od rzeczywistości, czysto akademickie. Paweł zrobił zadanie z tabelą rozgrywek piłkarskich. Na basic test poszły wyniki rozgrywek reprezentacji. Jak łatwo się przekonać nasza reprezentacja jest na pierwszym miejscu w tym rankingu. Co prawda przeciwnicy są z "dolnej półki", ale żadnego przeciwnika nie można lekceważyć :). A i nasze orły nie latają wysoko...

    Poziom zadania? Najwyższa półka z TC. Naprawdę trzeba popracować nad rozwiązaniem.

    Co jest inaczej? Oprócz basic data są już opublikowane load data (na TC load data są po tricky data). A w load data miła niespodzianka. Tabela rozgrywek Primera Division z sezonu 2009/2010. I miły dla serca kibica Barcy widok: Barca Campeon! U Pawła tricky data będą potem. A ich zrobienie nie jest wcale łatwe. Przecież to wcale nie musi być jeden do jednego replika z TC.

    Idą długie jesienne wieczory. Koniec konkursu 3 października, więc czasu jest jeszcze trochę. Jeżeli lubisz tworzyć w T-SQL'u, masz trochę żyłki sportowej to stań na ubitej ziemi i walcz o główne trofea (albo baw się z nami dobrze i nie pozwól aby Twój umysł leżał bezczynnie na kanapie).

    A skoro idzie jesień i czas grzybobrania zbiegł się z obfitością grzybów w naszych lasach to polecam przepis (prosty) na "śledzie gajowego".
    Składniki:
    - 1/2 kg płatów śledziowych (marynowanych),
    - trzy cebule,
    - dowolna ilość kurek,
    - przyprawa maggi, olej, pieprz czarny świeżo zmielony, vegeta.
    Jak wykonać?
    Cebule obrać i pokroić w plasterki.Cebulę zeszklić na oleju z dodatkiem vegety, ostudzić.
    Śledzie krótko namoczyć w zimnej wodzie, obrać ze skóry, pokroić w paski podobne do plasterków cebuli.
    Kurki oczyścić i umyć w zimnej wodzie. Kurki udusić z pieprzem dodanym do smaku na patelni aż stracą wodę (ale nie na zupełnie sucho!). Ostudzić.
    Wymieszać kurki, cebulę i śledzie dodając do smaku maggi (tak aby wyraźnie ją było czuć).
    Odstawić do lodówki na kilka godzin.

    Smacznego i powodzenia na wyzwaniach!


  • Mój pierwszy raz - TSQL Challenges badge

    Mogę już używać tej oto odznaki:

    TSQL Challenge Winner

    W 26 TSQL Challenger uzyskałem pierwsze 8 punktów (8 SQL Stars).
    Leszek Gniadkowski, z którym "nakręcamy" się co do rozwiązań zdobył w tym rozdaniu 9 punktów (gratulacje!).
    W "drodze" są jeszcze zaakceptowane rozwiązania od 27 do 33 challengera czekające na testy logiczne i wydajnościowe. Może jeszcze coś się uda zdobyć.

    PS. Znaczek jak znaczek. Bardziej mi się kojarzy z jakimś klubem koszykarskim NBA :)
  • VirtualStudy Conference 2010 - nie przegap tego wydarzenia!

    Portal wiedzy VirtualStudy to przedsięwzięcie niezwykle cenne. Pozwala na bezpłatne poszerzenie swojej wiedzy o najnowszych technologiach informatycznych. Wystarczy tylko zarejestrować się na wybraną sesję.

    Podobnie będzie ze najnowszą inicjatywą portalu: VirtualStudy Conference 2010. Na uczestników czeka doborowy oddział prelegentów. Wystarczy usiąść z laptopem w cieniu drzewa i popijając chłodne piwo chłonąć to co mają do powiedzenia uznani wymiatacze. Oczywiście osobistych spotkań w czasie takich konferencji nie da się niczym zastąpić ale to już widocznie taki przewrotny signum temporis, że będąc blisko jesteśmy daleko od siebie.

    Tak więc nie czekaj! Zarejestruj się jeszcze dziś!


    VirtualStudy.pl
  • TSQL Challenge - musisz tam być!

    Portal Beyond Relational założył SQL Server MVP Jacob Sebastian a jego sztandarowym "produktem" jest zdobywający coraz większe zainteresowanie nieustający konkurs TSQL Challenge, czyli zadania będące rzeczywistymi wyzwaniami w tworzeniu zapytań T-SQL. Wyzwania o tyle trudne, że muszą mieć postać jednej instrukcji języka T-SQL rozpoczynającej się od SELECT lub WITH. Zapytania mają się poprawnie wykonać głównie w nowszych wersjach SQL Server (2005 i 2008) ale nie brakuje również zadań wyłącznie w wersji 2000 (czyli żegnajcie "wodotryski" z nowszych wersji).
     
    Pierwszy raz TSQL Challenge w szerszym odbiorze pojawiło się na portalu wss.pl przy okazji 20 chellenger'a dotyczącego identyfikacji powtarzających się cyfr w wyrazach ciągu Fibonacciego. Na wyniki klasyfikacji trzeba było trochę poczekać. Z naszego kraju było sklasyfikowanych kilka osób a zwyciężył Maciej Pilecki, którego rozwiązanie było banalnie proste. A przy tym niezwykle pomysłowe. Byłem pod wrażeniem.

    Po krótkim spotkaniu przy okazji 20 challenger'a na jakiś czas o nim zapomniałem, ale głównie za sprawą Leszka Gniadkowskiego (leszek_g na portalu wss.pl) wróciłem do tych wyzwań. I od numeru 26 jestem na bieżąco.

    Jakie refleksje?
    • Przekonuje się, że wcale nie jest trudno dojść do satysfakcjonujących wyników (jedne zadania wymagają "błysku" inne wymagają większego "fizycznego" nakładu pracy ale wszystkie wymagają dobrej znajomości T-SQL'a).
    • Można to samo zadanie rozwiązać na wiele sposobów (cały czas z użyciem jednej instrukcji -SQL).
    • T-SQL jest sumie bardzo elastycznym językiem (chociaż jak się te zadania rozwiązuje to chciałoby się mieć jeszcze więcej możliwości).
    • Rozwiązywanie tych zadań to świetna metoda na doskonalenie warsztatu. To jak opanowywanie piłki przez piłkarza poprzez trening żonglerki (pamiętając jednak o tym, że najlepsi w tej materii nie byli najlepszymi piłkarzami).
    • Można się uczyć dobrych rozwiązań od zwycięzców - bezcenne.
    • Za najlepsze 10 rozwiązań przyznawane są gwiazdy. Zwycięzca dostaje "10 SQL Stars" plus ewentualnie jakieś nagrody. Dziesiąty w klasyfikacji dostaje "1 SQL Star". jest więc element sportowej rywalizacji.
    Minusy? Długie oczekiwanie na klasyfikację. Minus pozorny, bo dokąd tu się spieszyć?

    Podsumowując. Watro ten portal odwiedzić i spróbować zmierzyć się z kolejnym wyzwaniem.

    opublikowano 15 czerwca 2010 23:11 przez marekpow | 0 komentarzy
    Filed under:
  • XML-owe przypadki – transformacja (nie)odwracalna

    Zapytanie z opcją FOR XML generujące obiekt XML i metoda nodes() zastosowana do niego mogą stanowić wzajemnie odwrotne transformacje. Zobaczmy to na przykładzie. Najpierw przygotujemy tablicę z danymi testowymi:

    declare @t table(x int)

    insert into @t values
    (3),(2),(1),(0),(3)


    i teraz wykonamy proste zapytania:


    declare @xml xml

    -- 1. Zapytanie podstawowe ----------------------------------
    select * from @t

    -- 2. xsinil ------------------------------------------------
    set @xml = (select * from @t for xml raw, elements xsinil)

    select t.c.value('./x[1]','int') x
    from @xml.nodes('/row') t(c)

    -- 3. absent ------------------------------------------------
    set @xml = (select * from @t for xml raw, elements absent)

    select t.c.value('./x[1]','int') x
    from @xml.nodes('/row') t(c)


    Pierwsze zapytanie zwraca po prostu zawartość tabeli @t.

    W drugim zapytaniu tworzymy obiekt XML poprzez najprostszą formę klauzuli FOR XML czyli RAW, ale tworzymy go z opcją ELEMENTS XSINIL. Opcja ta w przypadku gdy wyrażenie tworzące element ma wartość NULL tworzy element z atrybutem xsi:nil="true" i z pustą zawartością. Następnie na obiekt XML działamy metodą nodes() aby przywrócić danym formę tabelaryczną.

    W trzecim zapytaniu tworzymy obiekt XML poprzez najprostszą formę klauzuli FOR XML czyli RAW, ale tworzymy go z opcją ELEMENTS ABSENT. Opcja ta w przypadku gdy wyrażenie tworzące element ma wartość NULL pomija tworzenie elementu XML z nim związanego. Następnie na obiekt XML działamy metodą nodes() aby przywrócić danym formę tabelaryczną.

    Jak łatwo się przekonać, wszystkie zapytania zwrócą ten sam rezultat (czyli z obiektu XML wygenerowanego zapytaniem z klauzulą FOR XML otrzymaliśmy ponownie tablicę dzięki zastosowaniu metody nodes() na tym obiekcie - czyli stanowią wzajemnie odwrotne operacje):



    Skoro nam tak dobrze idzie to wprowadzimy zaburzenie do naszych danych testowych w sposób następujący:


    declare @t table(x int)

    insert into @t values
    (3),(2),(1),(0),(null)

    Wykonanie tych samych zapytań da jednak zaskakujący efekt:



    Widać z tego, że drugie zapytanie zwróciło wartość zero zamiast oczekiwanej wartości NULL. Czy można temu zachowaniu zapobiec? Tak, wystarczy inaczej zapisać drugie zapytanie, rzutując element zwracany przez metodę value() na typ xs:int?

    set @xml = (select * from @t for xml raw, elements xsinil)

    select t.c.value('./x[1] cast as xs:int?','int') x
    from @xml.nodes('/row') t(c)



    I wszystko wydawałoby się już poukładane, do momentu gdy nie zastosujemy kolumny typu varchar:

    declare @t table(x int, y varchar(100))

    insert into @t values
    (3,'a'),(2,'b'),(1,'c'),(0,'d'),(null,null)

    declare @xml xml

    set @xml = (select * from @t for xml raw, elements xsinil)

    select
        t.c.value('./x[1] cast as xs:int?','int') x,
        t.c.value('./y[1] cast as xs:string?','varchar(100)') y
    from @xml.nodes('/row') t(c)


    W tym wypadku zapytanie zwróci nam wynik:



    i widzimy, że w piątym wierszu jedynie w kolumnie y zamiast oczekiwanej wartości NULL mamy pusty string. Nawet zastosowanie rzutowania na xs:string? nie daje spodziewanego rezultatu jak przy rzutowaniu na typ xs:int? przy kolumnie typu int. W rzutowaniu cast as xs:int? oraz cast as xs:string? istotne są znaki zapytania na końcu definicji typu docelowego. Mówią one, że wartość atomowa uzyskiwana przez zapytanie XQuery w metodzie value() a będąca przedmiotem rzutowania może przyjmować wartość null. Dlaczego jednak działa to dla typu int a nie działa dla typu varchar pozostaje dla mnie tajemnicą. Wydaje mi się, że za tym wszystkich gdzieś przebija .NET-owe podejście do XML-a.
    opublikowano 27 maja 2010 20:45 przez marekpow | 1 komentarzy
    Filed under: , ,
  • SELECT TOP 99.99999999999999 PERCENT...


    Jak działa ta instrukcja SELECT TOP wie każdy programista SQL. Ale ja chcę się skupić na pewnym jego przypadku użycia. Na początek przygotujmy dane testowe:

    declare @t table(x varchar(100))

    insert into @t values
    ('x'),('z'),('b'),('a')

    select top 100 percent * from @t order by x


    Instrukcja SELECT TOP zwraca rekordy uporządkowane w sposób następujący:

    a,b,x,z

    I tu wszystko jest OK. Ale ja pomyślałem o zastosowaniu opcji SELECT TOP szczególnie w subquery, gdzie jak wiadomo użycie ORDER BY nie jest dopuszczalne. Sprawdźmy to:

    select * from (select * from @t order by x) q

    Wykonanie takiego zapytania kończy się wyjątkiem:

    The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

    Ale skoro mogę zastosować TOP to spróbujmy zastosować najbardziej nasuwający się sposób użycia zwracający wszystkie dane uporządkowane w subquery czyli SELECT TOP 100 PERCENT:

    select * from (select top 100 percent * from @t order by x) q

    Ale ze zdziwieniem stwierdzamy, że zapytanie zwraca rekordy w porządku:

    x,z,b,a

    Czyli w porządku, w jakim dane te występują w tabeli @t. Zobaczmy wobec tego jak wygląda plan zapytania dla tego przypadku:



    Czyli operacja sortowania wcale nie zachodzi!

    Zróbmy w takim razie niewielka zmianę w zapytaniu:

    select * from (select top 99.99 percent * from @t order by x) q

    ale tym razem otrzymujemy zgodnie z oczekiwaniami uporządkowanie

    a,b,x,z

    i plan zapytania wyraźnie pokazuje zastosowanie sortowania



    i sortowanie zachodzi aż do wartości granicznej dla klauzuli TOP:


    select * from (select top 99.9999999999999 percent * from @t order by x) q



    Z tego planu wykonania wynika, że "dołożenie" kolejnej 9-tki po przecinku do wielkości TOP ... PERCENT spowoduje zaokrąglenie do 100 i ponowny "zanik" sortowania w subquery. Czy można jednak zapewnić, że sortowanie będzie miało jednak miejsce w subquery i jednocześnie zostaną zwrócone wszystkie rekordy? Można. Wystarczy zamiast stosować PERCENT podać maksymalna ilość wierszy zwracanych przez @@ROWCOUNT_BIG czyli zastosować następujące zapytanie:

    select * from (select top 9223372036854775807 * from @t order by x) q

    Wszystkie powyższe rozważania odnoszą się również to wyrażeń CTE, wewnątrz których nie można również stosować ORDER BY (bez użycia klauzuli TOP). Zastosowanie klauzuli TOP 100 PERCENT zachowuje się podobnie jak w subquery czyli nie sortuje danych:
    ;with cte as
    (
    select top 100 percent x
    from @t
    order by x
    )
    select * from cte


    Lecz mają zastosowanie wszystkie pozostałe opisane powyżej sposoby na uzyskanie sortowania wewnątrz wyrażenia CTE z zastosowaniem klauzuli TOP (w rozwinięciu dziesiętnym i opcją PERCENT oraz z podaniem maksymalnej liczby bigint w serwerze SQL).
  • XML-owe przypadki – dziwne zachowanie ‘every…satisfies’

    Przygotowując przykłady do sesji “XML w SQL Server 2008” na portalu Virtual Study w cyklu SQL nie tylko dla orłów organizowanym wspólnie z Polish SQL Server User Group trafiłem na ciekawy przypadek z użyciem konstrukcji ‘every … satisfies’ w metodzie query zastosowanej do obiektu typu XML.

    Ale aby dojść do sedna sprawy trzeba zdefiniować dwa pojęcia:

    • sekwencje,
    • sposób wartościowania logicznego w XQuery.

    I. Sekwencje

    Na początek definicja pustej sekwencji. Wygląda to tak:

    ()

    Sekwencję tworzy się poprzez użycie operatora przecinkowego do: pustej sekwencji, typów atomowych oraz węzłów. Przykładowe poprawne sekwencje mogą wyglądać tak:

    (1, ”dwa”, 3.0,0000004, 5e+0, ())

    ((), /,  /x)

    Pusta sekwencja jest pomijana w wynikowej sekwencji.

    Nie można w jednej sekwencji użyć typów atomowych i węzłów (generuje to błąd heterogenicznych sekwencji):

    (1, ”dwa”, 3.0,0000004, 5e+0, /x)

    II. Wartościowanie logiczne w XQuery

    Wartościowanie logiczne w XQuery w SQL Server jest zawężone (nie można na przykład wartościować logicznie wartości typu xs:string oraz wartości typu numerycznego, chociaż specyfikacja XQuery przywiduje taką możliwość). Wartościowanie w SQL Server ma zastosowanie do:

    • atomowej wartości logicznej (funkcje true() i false() generują odpowiednie wartości logiczne odpowiadające wartości prawy i fałszu logicznego, funkcja not() generuje zaprzeczenie logiczne)
    • pustej sekwencji
    • sekwencji węzłów
    W SQL Server wartościowanie logiczne zwraca true gdy:
    • sekwencja zawiera węzły lub gdy jest wartość logiczna true
    • false w przeciwnym wypadku

    III. Przećwiczmy to na przykładach.

    Na początek zobaczmy jak funkcja not wartościuje pustą sekwencję:

    select CAST('' as xml).query('not(())')

    W wyniku dostajemy wynik true, co świadczy, że pusta sekwencja jest wartościowana jak false.

    Konstrukcja 'every ... satisfies' zwraca true gdy wszystkie elementy z sekwencji są wartościowane na true.

    Aby się o tym przekonać zobaczmy jak zadziała takie zapytanie:

    select CAST('' as xml).query('every $x in (true(), true()) satisfies $x')
    select CAST('' as xml).query('every $x in (false(), true()) satisfies $x')
    select CAST('' as xml).query('every $x in (true(), false()) satisfies $x')
    select CAST('' as xml).query('every $x in (false(), false()) satisfies $x')

    Wyniki są jak najbardziej poprawne, czyli true, false, false, false.

    W takim razie zobaczmy czy puste elementy  sekwencji za instancjami węzłów:

    select CAST('' as xml).query('every $x in (() instance of node(), () instance of node()) satisfies ($x)')

    Nie są, zapytanie zwraca false. 

    To może jeszcze jedno zapytanie sprawdzające z ilu węzłów składa się pusta sekwencja:

    select CAST('' as xml).query('every $x in (count(()), count(())) satisfies $x > 0')

    Jest OK, nie zawiera żadnych węzłów.

    W takim razie zobaczmy jaki rezultat będzie takiego zapytania:

    select CAST('' as xml).query('every $x in (()) satisfies $x')

    O dziwo, pomimo, że sekwencja zawiera sekwencję pustą to powyższe zapytanie zwraca również true.

    Hmm, dziwne.  OK, wobec tego zobaczmy co zwróci to zapytanie:

    select CAST('' as xml).query('every $x in ((), ()) satisfies $x')

    True! Dlaczego?

    Wobec tego zróbmy zapytanie z zaprzeczeniem

    select CAST('' as xml).query('every $x in ((), ()) satisfies not($x)')

    Znowu true! Ale przecież zaprzeczyliśmy warunek dla satisfies!

    Czy gdzieś popełniam błąd? Dwa powyższe zapytania są równoważne tym zapytaniom:

    select CAST('' as xml).query('every $x in () satisfies ($x)')

    select CAST('' as xml).query('every $x in () satisfies not($x)')

    ponieważ SQL Server pomija pute sekwencje występujące w sekwencji i w takim wypadku nie dochodzi do wartościowania warunku po satisfies ponieważ sekwencja nie zawiera żadnego elementu. Ale czy w takim wypadku nie powinna być zwraca wartość false? Morze to rzecz gustu albo tak po prostu ma być "by design"?
  • Najkrótsza droga w labiryncie (w T-SQL!)

    Interesujący problem został przedstawiony na wss.pl. To klasyczny problem labiryntu. Zmieniłem nieco definicję danych wejściowych problemu zastępując tablicę tymczasową tablicą pamięciową.

    declare @t table (n int, r char(10)) values
    ( 1, ' '),
    ( 2, ' S '),
    ( 3, 'xxxxxx '),
    ( 4, ' '),
    ( 5, ' '),
    ( 6, ' xxxxxx'),
    ( 7, ' '),
    ( 8, ' E'),
    ( 9, ' '),
    (10, ' '),

    Rozwiązaniem problemu jest znalezienie najkrótszej drogi pomiędzy dwoma punktami z uwzględnieniem punktów zabronionych (murów). Nie można stosować zmiennych i pętli, można stosować jedynie instrukcje SELECT i CTE.

    Rozwiązanie powinno sie dać uzyskać poprzez wygenerowanie wszystkich możliwych ciągów permutacji dwuelementowych bez powtórzeń ze zbioru {1,10} rozpoczynających się od permutacji S(w,k) do permutacji E(w,k) (z wyłączeniem permutacji określających punkty muru) gdzie kolejne permutacje różnią się jednym elementem i różnią się wyłącznie o wartość 1. Wskaźniki permutacji są to wiersz i kolumna macierzy.

    Rozwiązanie nie powinno zależeć od istnienia murów. Zawsze można usunąć dowolne punkty ze zbioru możliwych permutacji. Maksymalna ilość dopuszczalnych permutacji (poza punktem startowym) to N x N-1 bez istnienia murów i N x N-M-1 gdzie M jest całkowitą długością murów). Wygenerowanie wszystkich takowych ciągów permutacji dla tablicy 10 x 10 wydaje sie być zadaniem karkołomnym czasowo. W związku z tym postanowiłem działać indukcyjnie – od macierzy mniejszych rozmiarów.

    Zbudowałem macierz o rozmiarze 6 x 6:

    declare @t table (n int, r char(6))
    insert into @t values
    (1, '      '),
    (2, ' S    '),
    (3, 'xxxx  '),
    (4, '      '),
    (5, '   xxx'),
    (6, '     E')

    declare @n int = (select DATALENGTH(r) from @t where n =1 )

    powstałą przez zredukowanie wierszy i kolumn przy zachowaniu ogólnego wyglądu danych podstawowych. Aby ułatwić sobie generowanie rozwiązań dodałem zmienną @n określającą rozmiar macierzy (jest to jedyna zmienna w rozwiązaniu ale nie jest ona wykorzystywana do przechowywania wyników a jedynie wykorzystywana zamiast stałej).

    Moje rozwiązanie będzie sie składało z szeregu wywołań CTE, które będę prezentował kolejno. Na koniec podam kompletne rozwiązanie.

    I. Zapytanie (algorytm)

    1. Współrzędne punktu startu:

    ;with start as
    (
    select n w, CHARINDEX('S',r) k
    from @t
    where r like '%S%'
    )

    2. Współrzędne punktu docelowego:

    , stop as
    (
    select n w, CHARINDEX('E',r) k
    from @t
    where r like '%E%'
    )

    3. Uzyskanie współrzędnych “cegieł w murze”. CTE wall wyciąga wiersze zawierające znak X, CTE bricks wyciąga “cegły”, CTE brickPos podaje współrzędne położenia “cegły” w murze.

    , wall as
    (
    select n w
    from @t
    where r like '%X%'

    , bricks as
    (
    select t.n w, 1 as k, SUBSTRING(r,1,1) b, r
    from @t t
    inner join wall w on w.w = t.n
    union all
    select b.w, b.k+1, SUBSTRING(b.r,b.k+1,1), r
    from bricks b
    where b.k < @n
    )
    , brickPos as
    (
    select b.w, b.k
    from bricks b
    where b.b = 'X'
    )

    4. Utworzenie tabeli wierzy numerów wierszy (allRows) i kolumn (allCols):

    , allRows as
    (
    select 1 as r
    union all
    select a.r+1 r
    from allRows a
    where a.r < @n)
    , allCols as
    (
    select 1 as c
    union all
    select a.c+1 c
    from allCols a
    where a.c < @n)

    5. Utworzenie tabeli możliwych do wykorzystania permutacji. Każdy element tabeli zawiera permutację składającą sie z numeru wiersza i kolumny tabeli danych wejściowych. Z tabeli permutacji usunięty został punkt startowy oraz wszystkie “cegły”.

    , points as
    (
    select r.r w, c.c k
    from allRows r, allCols c
    except
    select b.w, b.k
    from brickPos b
    except
    select s.w, s.k
    from start s
    )

    6. Generowanie ciągów permutacji bez powtórzeń z permutacji zawartych w tabeli generowanej przez CTE points. CTE routes ma jedną kotwicę będącą permutacją określającą punkt startowy. Nie musimy sie martwić o przeszkody na drodze ponieważ permutacje je określające zostały już przez nas wyeliminowane w pkt. 5. CTE routes musi mieć cztery elementy rekursywne określające cztery możliwe do wyboru następne permutacje (odpowiadające czterem kierunkom ruchu).

    , routes as
    (
    select w,k,
    cast('('+CAST(w as varchar(2))+','+
    CAST(k as varchar(2))+')' as varchar(max)) path,
    1 level
    from start
    -------------
    union all
    select r.w, r.k+1,
    cast(r.path+',('+CAST(r.w as varchar(2))+','+
    CAST(r.k+1 as varchar(2))+')' as varchar(max)),
    r.level+1
    from routes r
    inner join points p on p.k = r.k+1 and p.w = r.w
    where not(r.k+1 = (select k from stop) and r.w = (select w from stop))
    and r.path not like '%('+CAST(r.w as varchar(2))+','+
    CAST(r.k+1 as varchar(2))+')%'
    ---------------
    union all
    select r.w, r.k-1,
    cast(r.path+',('+CAST(r.w as varchar(2))+','+
    CAST(r.k-1 as varchar(2))+')' as varchar(max)),
    r.level+1
    from routes r
    inner join points p on p.k = r.k-1 and p.w = r.w
    where not(r.k-1 = (select k from stop) and r.w = (select w from stop))
    and r.path not like '%('+CAST(r.w as varchar(2))+','+
    CAST(r.k-1 as varchar(2))+')%'
    ---------------
    union all
    select r.w-1, r.k,
    cast(r.path+',('+CAST(r.w-1 as varchar(2))+','+
    CAST(r.k as varchar(2))+')' as varchar(max)),
    r.level+1
    from routes r
    inner join points p on p.k = r.k and p.w = r.w-1
    where not(r.w-1 = (select w from stop) and r.k = (select k from stop))
    and r.path not like '%('+CAST(r.w-1 as varchar(2))+','+CAST(r.k as varchar(2))+')%'
    -----------------
    union all
    select r.w+1, r.k,
    cast(r.path+',('+CAST(r.w+1 as varchar(2))+','+
    CAST(r.k as varchar(2))+')' as varchar(max)),
    r.level+1
    from routes r
    inner join points p on p.k = r.k and p.w = r.w+1
    where not(r.w+1 = (select w from stop) and r.k = (select k from stop))
    and r.path not like '%('+CAST(r.w+1 as varchar(2))+','+CAST(r.k as varchar(2))+')%'
    )

    7. Prezentacja wszystkich permutacji prowadzących od punktu startowego do punktu końcowego (czyli po prostu drgi z punktu startowego do punktu końcowego):

    select r.level+1,
    r.path+',('+CAST((select w from stop) as varchar(2))+','+
    CAST((select k from stop) as varchar(2))+')'
    from routes r
    where (r.k = (select k-1 from stop) and r.w = (select w from stop)) or
    (r.k = (select k+1 from stop) and r.w = (select w from stop)) or
    (r.k = (select k from stop) and r.w = (select w+1 from stop)) or
    (r.k = (select k from stop) and r.w = (select w-1 from stop))
    order by r.level
    option (maxrecursion 100)

    Dla podanych danych uzyskałem jedną najkrótszą trasę o długości 13:


    Co prowadzi do rozwiązania graficznego:


    Dla tych danych istnieją również dwie najdłuższe drogi o długości 29:


    I jego reprezentacja graficzna dla rozwiązania numer 924:


    oraz dla rozwiązania numer 923:


    Poszukiwanie najdłuższych tras jest moim zdaniem ciekawsze niż znajdowanie najkrótszych. Przypomina trochę problem wypełnienia płaszczyzny krzywą. I nie jest łatwe do uzyskania metodą “na oko” co jest łatwiejsze w przypadku poszukiwania drogi najkrótszej.

    II. Relacje czasowe

    Rozwiązania przetestowałem dl dla następujących rozmiarów macierzy:

    Rozmiar macierzy

    Czas wykonania [sek]

    5x5

    1

    6x6

    8

    7x7

    923

    8x8

    w momencie pisania tego tekstu 4 godziny i nie było widać końca

    Z zestawienia widać, że wzrost nakładów obliczeniowych następuje lawinowo (postaram się wytrwać w poszukiwaniu rozwiązania 8x8, ). Poszukiwanie rozwiązania dla macierzy 10x10 pewnie trwałoby “wieczność”.

    III. Kompletne rozwiązanie

    declare @t table (n int, r char(6))
    insert into @t values
    (1, '      '),
    (2, ' S    '),
    (3, 'xxxx  '),
    (4, '      '),
    (5, '   xxx'),
    (6, '     E')

    declare @n int = (select DATALENGTH(r) from @t where n =1 )

    ;with start as
    (
    select n w, CHARINDEX('S',r) k
    from @t
    where r like '%S%'
    )
    , stop as
    (
    select n w, CHARINDEX('E',r) k
    from @t
    where r like '%E%'
    )
    , wall as
    (
    select n w
    from @t
    where r like '%X%'
    )
    , bricks as
    (
    select t.n w, 1 as k, SUBSTRING(r,1,1) b, r
    from @t t
    inner join wall w on w.w = t.n
    union all
    select b.w, b.k+1, SUBSTRING(b.r,b.k+1,1), r
    from bricks b
    where b.k < @n
    )
    , brickPos as
    (
    select b.w, b.k
    from bricks b
    where b.b = 'X'
    )
    , allRows as
    (
    select 1 as r
    union all
    select a.r+1 r
    from allRows a
    where a.r < @n)
    , allCols as
    (
    select 1 as c
    union all
    select a.c+1 c
    from allCols a
    where a.c < @n)
    , points as
    (
    select r.r w, c.c k
    from allRows r, allCols c
    except
    select b.w, b.k
    from brickPos b
    except
    select s.w, s.k
    from start s
    )
    , routes as
    (
    select w,k,
    cast('('+CAST(w as varchar(2))+','+
    CAST(k as varchar(2))+')' as varchar(max)) path,
    1 level
    from start
    -------------
    union all
    select r.w, r.k+1,
    cast(r.path+',('+CAST(r.w as varchar(2))+','+
    CAST(r.k+1 as varchar(2))+')' as varchar(max)),
    r.level+1
    from routes r
    inner join points p on p.k = r.k+1 and p.w = r.w
    where not(r.k+1 = (select k from stop) and r.w = (select w from stop))
    and r.path not like '%('+CAST(r.w as varchar(2))+','+
    CAST(r.k+1 as varchar(2))+')%'
    ---------------
    union all
    select r.w, r.k-1,
    cast(r.path+',('+CAST(r.w as varchar(2))+','+
    CAST(r.k-1 as varchar(2))+')' as varchar(max)),
    r.level+1
    from routes r
    inner join points p on p.k = r.k-1 and p.w = r.w
    where not(r.k-1 = (select k from stop) and r.w = (select w from stop))
    and r.path not like '%('+CAST(r.w as varchar(2))+','+
    CAST(r.k-1 as varchar(2))+')%'
    ---------------
    union all
    select r.w-1, r.k,
    cast(r.path+',('+CAST(r.w-1 as varchar(2))+','+
    CAST(r.k as varchar(2))+')' as varchar(max)),
    r.level+1
    from routes r
    inner join points p on p.k = r.k and p.w = r.w-1
    where not(r.w-1 = (select w from stop) and r.k = (select k from stop))
    and r.path not like '%('+CAST(r.w-1 as varchar(2))+','+CAST(r.k as varchar(2))+')%'
    -----------------
    union all
    select r.w+1, r.k,
    cast(r.path+',('+CAST(r.w+1 as varchar(2))+','+
    CAST(r.k as varchar(2))+')' as varchar(max)),
    r.level+1
    from routes r
    inner join points p on p.k = r.k and p.w = r.w+1
    where not(r.w+1 = (select w from stop) and r.k = (select k from stop))
    and r.path not like '%('+CAST(r.w+1 as varchar(2))+','+CAST(r.k as varchar(2))+')%'
    )
    select r.level+1,
    r.path+',('+CAST((select w from stop) as varchar(2))+','+
    CAST((select k from stop) as varchar(2))+')'
    from routes r
    where (r.k = (select k-1 from stop) and r.w = (select w from stop)) or
    (r.k = (select k+1 from stop) and r.w = (select w from stop)) or
    (r.k = (select k from stop) and r.w = (select w+1 from stop)) or
    (r.k = (select k from stop) and r.w = (select w-1 from stop))
    order by r.level
    option (maxrecursion 100)

    Miłej zabawy w poszukiwaniu właściwej drogi!

    opublikowano 14 maja 2010 20:57 przez marekpow | 2 komentarzy
    Filed under: ,
  • SQL Server Event Notifiations - porady praktyczne

       SQL Server Event Notifications (EN) to właściwość wprowadzona już w wersji 2005 ale niedoceniona przeze mnie wówczas i myślę, że przez wielu entuzjastów SQL-a. Funkcjonalnie działanie opiera się na SQL Server Service Broker i dlatego implementując EN warto wesprzeć się na moim add-in do SSMS czyli Service Broken, którego źródła są tutaj. Wersja tam znajdująca działa z wersją SQL Server 2008 (również Express).

       Dlaczego warto mieć takie narzędzie do wsparcia? Dlatego, że Service Broker bywa bardzo kapryśny w implementowaniu. Kiedy wydaje się, że już wszystko zostało zrobione i następuję moment końcowego odliczania do uruchomienia implementacji nagle okazuje się, że po naciśnięciu klawisza F5 następuje głucha cisza. Wówczas niezbędne okazuje się sprawne przejrzenie właściwości i infrastruktury Service Broker'a aby odkryć możliwą przyczynę braku działania. Szczególnie przydatna w takich momentach jest grupa opcji Show oraz Other options, która w prosty i szybki sposób dostarcza szeregu informacji w postaci okienkowej bez konieczności znajomości wywołań widoków systemowych związanych z Service Broker.

       Z moich doświadczeń wynika, że najlepszym rozwiązaniem jest posiadanie zaimplementowanego jednego bazowego rozwiązania, które tworzyłoby środowisko nasłuchujące zdarzenia generowane przez SQL Server. Taka implementacja powinna mieć dosyć szeroką klasę event'ów do nasłuchiwania. Dosyć szeroką ale nie za szeroką. W moich doświadczeniach skupiłem się głównie na klasie TRC_ERRORS_AND_WARNINGS, która obejmuje głownie sytuacje wyjątkowe oraz informacje o zachowaniu się SQL Server (interesowała mnie głownie ta klasa a nie klasa DDL_EVENTS ponieważ interesowały mnie głównie błędy generowane prze implementację posadowione na SQL Server). Napisałem, że nie powinna być za szeroka ponieważ w doświadczeniach posunąłem się do zastosowania klasy TRC_ALL_EVENTS co pokazało, że tabela do przechowywania komunikatów "pompowanych" przez SQL Server w błyskawicznym tempie zaczęła się zapełniać masą rekordów z komunikatami zdarzeń. Ta implementacja bazowa byłaby wznawiana na czas uruchomień nowych implementacji EN nasłuchując możliwych sytuacji wyjątkowych, w tym związanych z tworzoną właśnie nową implementacją. Poz zakończeniu wdrażania nowej implementacji EN tę bazową należałoby wyłączyć do czasu uruchomienia następnej.

       Nie chcę tu szczegółowo opisywać jak zaimplementować EN. Jest to niezwykle proste. Przykład jest tutaj. Na co jednak warto zwrócić szczególną uwagę?
    1. Najważniejsze jest aby baza danych, której dotyczy EN miała włączony Service Broker. Bez tego nie mam mowy o działaniu EN. Tworzenie nowej bazy danych przez GUI z SSMS domyślnie wyłącza Service Broker. Więc jest to punkt krytyczny. Mój add-in pozwala na włączenie i wyłączenie go z zakładki Brokers, która pokazuje wszystkie Service Broker's aktualnie dostępnego servera SQL. Odblokowanie i zablokowanie odbywa się poprzez naciśnięcie kontrolki check box.
    2. Tworząc subskrypcje zdarzeń przez CREATE EVENT NOTIFICATION należy bezwzględnie pamiętać, że nazwa serwisu jest literałem a nie sysname i do tego jest case sesitive i jest to błąd czasami trudny do zauważenia (dlatego tworząc tę subskrypcję należy do nazwy serwisu zastosować metodę Copy-Paste nazwy serwisu z polecenia CREATE SERVICE).
    3. Jeżeli w tworzeniu subskrypcji EN korzystamy ze składni 'current database' informującą, że subskrypcja odnosi się do bieżącej bazy danych to literał ten jest również case sensitive.
    4. Poza stosowaniem metody Copy-Paste do nazwy serwisu zdecydowanie odradzam stosowanie tej metody to tworzenia nowej implementacji na podstawie już istniejącej. Wbrew pozorom trzeba dokonać zmian w wielu miejscach i pominięcie choćby jednej zmiany owocuje w braku działania implementacji EN. Schemat tworzenia EN jest prosty i można go szybko opanować: tworzenie kolejki, tworzenie serwisu w oparciu o kontrakt http://schemas.microsoft.com/SQL/Notifications/EventNotification, utworzenie implementacji EN w oparciu o wcześniej utworzony serwis z wyborem określonych klas zdarzeń, utworzenie procedury przetwarzającej komunikaty z kolejki, aktywowanie kolejki z utworzoną wcześniej procedurą. Korzystając z Service Broken do SSMS można to zrobić bez znajomości poleceń T-SQL, korzystając głównie z GUI tego dodatku (poza oczywiście tworzeniem procedury aktywującej, którą trzeba napisać dostosowując do do konkretnej implementacji).
    5. EN generuje zdarzenia w postaci dokumentów XML i tworząc tabelę rejestrującą komunikaty warto utworzyć kolumnę, której zawartość będzie pobierana elementu z EVENT_INSTANCE/EventType oraz kolumnę na moment powstania zdarzenia, której zawartość pochodzi od elementu EVENT_INSTANCE/PostTime. Pierwsza informacja pozwala nam szybko zorientować się z jakim zdarzeniem mamy do czynienia a druga pozwala zgrupować szereg rekordów dotyczących tej samej sytuacji wyjątkowej. No i oczywiście kolumna na sam dokumenty XML. Jak może wyglądać jego konkretny wygląd dla poszczególnych zdarzeń można przeczytać ten dokument (jeśli ktoś lubi czytać dokumenty XSD).
    6. W przypadku problemów zobaczyć co zwraca zapytanie: SELECT * FROM sys.server_event_notifications. Może się okazać, że w zestawie odpowiedzi nie będzie naszego EN. Wówczas należy przyjrzeć się całej strukturze tworzonej w pkt. 4 czy nie zawiera błędów.
    Podsłuchowywując :) SQL Server, szczególnie poprzez TRC_ALL_EVENTS można si wiele dowiedzieć o tym jak funkcjonuje SQL Server. Widać jak ważna jest baza master oraz jak często używany jest SHOWPLAN_XML.

     W SSMS wpisując polecenie:

    RAISERROR('Uuuuuuuuuus!", 16, 1)

    dowiadujemy się, że idzie za tym zdarzenie USER_ERROR_MESSAGE. Natomiast polecenie

    DELETE FROM [blablabla]

    generuje najpierw zdarzenie EXCEPTION (w SSMS, na zakładce Messages jest pierwsza linia komunikatu zawierająca treść Msg 208, Level 16, State 1, Line 1), natomiast drugim zdarzeniem jest USER_ERROR_MESSAGE i odpowiada to drugiej linii komunikatu na zakładce Messages: Invalid object name 'blablabla'.

    Jak to z podsłuchowywaniem bywa można się dowiedzieć rzeczy niekoniecznie fajnych. Pisałem już o tym, że postawienie bazy w tryb offline powoduje dziwne komunikaty przy generowaniu skryptów z bazy danych. Okazuje się, że ten wyjątek jest generowany stale w EN przy każdej sytuacji gdy server używa używa do jakichś celów listy baz podpiętych do server'a. Błąd jest chyba w metodzie ConnectionInfo.

    EN są świetnym narzędziem, które pozwala na lekką implementacje systemu wczesnego ostrzegania o sytuacjach wyjątkowych dziejących się na bazie danych obsługiwanych przez nasza aplikację. EN nie generują dodatkowego narzutu na działanie server'a ponieważ są one generowane stale niezależnie czy mamy zaimplementowane EN czy nie. Jeżeli log zdarzeń postawimy na odrębnym wolumenie niż baza produkcyjna to może to być rozwiązanie bardzo optymalne.

    Dla zainteresowanych podaję linki do najbardziej interesujących przypadków śledzenia sytuacji wyjątkowych przy pomocy EN:
    1. Alertowanie zdarzenia DEADLOCK_GRAPH poprzez e-mail opisane na blogu ReSQueL.
    2. Alertowanie zdarzenia BLOCKED_PROCESS_REPORT opisane na blogu Tony Rogerson'a.
    Hmm, udało sie zrobić notkę na blogu bez obrazków :)
  • Problem z aparatem Microsoft.Jet.OLEDB.4.0 - ostateczne rozwiązanie

    Używanie aparatu danych Microsoft.Jet.OLEDB.4.0 do łączenia linked servers będących plikami DBF lub plikami Excel niejednemu dodało siwych włosów. Czasami mam wrażenie, że aparat ten ma jedną konstrukcję try-catch wyglądającą mniej-więcej tak:
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;

    namespace jet
    {
        class Program
        {
            static void Main(string[] args)
            {
                try
                {
                    flyByJet4();
                }
                catch
                {
                    Console.WriteLine("Unknown error!");
                }
            }
        }
    }



    Szereg doświadczeń z tym aparatem oraz poszukiwanie możliwych rozwiązań problemów przy uruchomieniu dolinkowanego serwera do instancji SQL Server ostatecznie doprowadziło do zbioru zasad, którymi należy się kierować przy korzystaniu z niego.

    1. Oczywiste. Microsoft.Jet.OLEDB.4.0 musi być zainstalowany na komputerze, na którym stoi SQL Server.
    2. Mniej oczywiste. Jeżeli mamy konstrukcję tworzącą linked server: EXEC master.dbo.sp_addlinkedserver @server = N'DBFFOLDER', @srvproduct=N'Jet 4.0', @provider=N'Microsoft.Jet.OLEDB.4.0', @datasrc=N'd:\mp\mrk', @provstr=N'dBASE IV' i katalog w którym znajdują się pliki wskazany zmienną @datasrc jest lokalny na komputerze, na którym zainstalowana jest instancja SQL Server odwołująca się do niego to katalog ten musi mieć dodane pełne prawa dostępu dla wszystkich użytkowników.
    3. Zupełnie nieoczywiste. Po spełnieniu dwóch powyższych warunków należy zrestartować usługę SQL Server. Bez tej czynności każda próba odwołania do Tables zawartych w tym linked server zakończy sie komunikatem Unknown error...

    Jak już się przebijemy przez to to nareszcie można stare tabele DBF śmigać na SQL Server 2008. Ale jak znam życie to pewnie niejednym mnie jeszcze Jet  zaskoczy...
  • Dziennik pokładowy MSSQL 2008 (wpis 0x0012) - Problem jawnej konwersji

    Ostatnio na forum wss.pl było sporo wątków na temat niejawnych konwersji dokonywanych przez SQL Server i zagrożeniach z tego płynących. Problemów związanych z niejawną konwersją dotyczyło również moje zgłoszenie na microsoft.connect.com dotyczące wyrażeń zawierających wartości typu datetime i datetime2. Ale robiąc wpis na moim blogu dotyczącym przeniesienia danych tabelarycznych ze strony HTML do tabeli SQL Server natknąłem się na problem jawnej konwersji (kolumna z poprzednim miejsce w rankingu była liczbą ujętą w nawiasy lub skrótem 'N.Rkd' gdy drużyna nie była dotąd notowana).
    Na początek zasadnicze pytanie: co to jest?

    (1)

    Czy to jest wyrażenie numeryczne? Tak mi się wydawało.

    Ale:

    select CAST('(1)' as numeric)

    zwraca zaskakujący wynik:

    Msg 8114, Level 16, State 5, Line 1
    Error converting data type varchar to numeric.

    W takim razie spróbujmy zastosować funkcję ISNUMERIC do określenia czy według SQL Server jest to wyrażenie numeryczne. W BOL dla tej funkcji jest wyraźnie napisane:

    Determines whether an expression is a valid numeric type

    Kiedyś przeanalizowałem cały zestaw znaków szukając tych dla których funkcja ta zwróci wartość 1. Więc wiem, że niekoniecznie musi być to liczba. Ale:

    select ISNUMERIC('(1)')

    zwraca zero. Czyli po prostu to nie jest wyrażenie numeryczne według SQL Server. Dziwne.



    opublikowano 8 grudnia 2009 09:57 przez marekpow | 0 komentarzy
    Filed under: , , , ,
Więcej wypowiedzi Następna strona »
W oparciu o Community Server (Personal Edition), Telligent Systems