sobota, 20 listopada 2010

concat

Normalizacja, pierwsza postać normalna (1FN) sobie a życie czasami sobie.
Sposobów „dobierania” się do danych oddzielonych przecinkiem, średnikiem, spacją i innymi jest wiele, jednym z dobrych pomysłów jest użycie potęgi XML.
Mamy tabelę, przykładowo dane oddzieliłem przecinkiem ale może to być inny znak.

CREATE TABLE #test (id INT IDENTITY(1,1),tekst VARCHAR(255))
go

INSERT #test(tekst)
SELECT'jeden,dwa,trzy,cztery,pięć,sześć,siedem,osiem,dziewięć'
UNION ALL
SELECT'112,134,12345,1234,1789,234,28'

Rozwiązanie problemu

SELECT id,
      tx.parser.value('@element', 'varchar(100)')AS elementy
      FROM(SELECT id, 
            CONVERT(XML,'<rekord element="'
            + REPLACE(tekst,',','"/><rekord element="')+'"/>')
            AS parser
      FROM #test)AS t
CROSS APPLY t.parser.nodes('/rekord')AS tx (parser)


Dostajemy wynik

id          elementy
--------------------------
1           jeden
1           dwa
1           trzy
1           cztery
1           pięć
1           sześć
1           siedwm
1           osiem
1           dziewięć
2           112
2           134
2           12345
2           1234
2           1789
2           234
2           28
A teraz w drugą stronę
Zrobimy tabelę #test2

SELECT id,
      tx.parser.value('@element', 'varchar(100)')AS elementy
into #test2
      FROM(SELECT id, 
            CONVERT(XML,'<rekord element="'
            + REPLACE(tekst,',','"/><rekord element="')+'"/>')
            AS parser
      FROM #test)AS t
CROSS APPLY t.parser.nodes('/rekord')AS tx (parser)

SELECT * FROM #test2

id          elementy
----------- ----------
1           jeden
1           dwa
1           trzy
1           cztery
1           pięć
1           sześć
1           siedem
1           osiem
1           dziewięć
2           112
2           134
2           12345
2           1234
2           1789
2           234
2           28


Zapytnie

SELECT p1.id,
       ( SELECT  elementy + ','
           FROM #test2 p2
          WHERE p2.id = p1.id
          ORDER BY id          
          FOR XML PATH('') ) AS tekst
      FROM #test2 p1
      GROUP BY id

lub

SELECT DISTINCT id,tekst
    FROM #test2 p1
   CROSS APPLY ( SELECT elementy + ','
                     FROM #test2 p2
                     WHERE p2.id = p1.id
                     ORDER BY id
                     FOR XML PATH('') )  D ( tekst )

Wynik

id          tekst
----------------------------------------------------------------------
1           jeden,dwa,trzy,cztery,pięć,sześć,siedem,osiem,dziewięć,
2           112,134,12345,1234,1789,234,28,

wtorek, 16 listopada 2010

dual

Tabela dual w bazie Oracle jest tabelą w schemacie SYS do którego uprawnienia odczytu na rola PUBLIC wprowadzonym by pomóc rozwiązać problemy z zapytaniami nie operującymi na zbiorach. Oracle inaczej niż SQL Server nie pozwala na użycie klauzuli SELECT bez FROM.
Używamy jej gdy chcemy wyciągnąć zmienne systemowe, pobrania nowej wartości sekwencji i wielu innych miejscach.

SQL> select sysdate from dual;

SYSDATE
-------------------
2010-11-15 15:22:13

SQL> select user from dual;

USER
------------------------------
SYS

Tabelę jako taka możemy obejrzeć przy pomocy DESC

SQL> desc dual;

 Name       Null?    Type
 ---------- -------- --------------
 DUMMY               VARCHAR2(1)

Możemy również obejrzeć jej zawartość

SQL> select * from dual;

D
-
X

Jednym z podstawowych założeń jest fakt że tabela dual ma jeden rekord w kolumnie DUMMY

SQL> select count(*) from dual;

  COUNT(*)
----------
         1

O tyle ciekawym wydaje się fakt, że bez większych problemów możemy go usunąć lub dodać następne a nawet zmienić samą tabelę co nie jest dobrą praktyką.
Po dodaniu wiersza do tabeli

SQL> select count(*) from dual;

  COUNT(*)
----------
         1

Ale musimy się liczyć z błędami

ORA-01422: exact fetch returns more than requested number of rows
Podczas operacji pozornie nie związanymi z ta tabela np

SQL> Drop table test

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01422: exact fetch returns more than requested number of rows

Ciekawostką jest to że tabela dual jest dostępna również przed otwarciem  bazy ale zwraca inne wyniki

SQL> select * from  dual;

ADDR           INDX    INST_ID D
-------- ---------- ---------- -
0A6231A7          0          1 X

niedziela, 14 listopada 2010

management studio

Ciekawostka, miałem do wgrania plik sql zapisany na dysku sieciowym, link do pliku dostałem pocztą. Otworzyłem plik i Microsoft SQL Server Management Studio zakomunikował mi o krytycznym błędzie i się zamknął.


Czasami się zdarza, może za dużo otwartych obiektów. Otworzyłem plik jeszcze raz studio otworzyło swoje GUI i znowu nakrzyczało błędem, spróbowałem inny skrypt, poszło bez problemu. Co może być w takim skrypcie co wywala mi studio, podejrzałem plik w notatniku nie było w nim nic podejrzanego, zwykły alter obiektu bazy. Może to moje środowisko z zainstalowanym np. SQL PROMPT firmy Redgate i innymi pomocnymi dodatkami, uruchomiłem skrypt z innej stacji. Tym razem dostałem jaśniejszy komunikat


Okazało się, że nazwa razem ze ścieżką miała ponad 260 znaków. Bez tego komunikatu nie było to takie oczywiste.

czwartek, 11 listopada 2010

tabela tymczasowa i jej spid z trace

Inna metoda znalezienia sesji (SPID) , na której została utworzona tabela tymczasowa bazuje na monitorowaniu SQL Servera za pomocą trace. Z tego samego źródła możemy korzystać jeżeli potrzebujemy informację kto zmienił obiekty w bazie lub je skasował, zmienił ustawienia serwera itp.
Do poszukiwania zdarzeń należy odfiltrować odpowiednie eventy, listę wszystkich możemy znaleźć zapytaniem

SELECT e.trace_event_id,e.name,c.name category
FROM sys.trace_events e INNER JOIN
sys.trace_categories c ON e.category_id = c.category_id

dla tworzenia nowych obiektów będzie to trace_event równy 46

CREATE PROC dbo.rk_get_spid_temp @name varchar(128)=''
as
DECLARE @FileName VARCHAR(MAX) 

SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc' 
FROM sys.traces  
WHERE is_default = 1; 
SET @name =@name+'%'
SELECT  
     o.name,  
     o.OBJECT_ID, 
     o.create_date,
     gt.NTUserName, 
     gt.HostName, 
     gt.SPID, 
     gt.DatabaseName, 
     gt.TEXTData
FROM sys.fn_trace_gettable( @FileName, DEFAULT ) AS gt 
JOIN tempdb.sys.objects AS o  
     ON gt.ObjectID = o.OBJECT_ID 
WHERE gt.DatabaseID = 2
  AND gt.EventClass = 46
  AND o.create_date >= DATEADD(ms, -100, gt.StartTime)  
  AND o.create_date <= DATEADD(ms, 100, gt.StartTime) 
  AND o.name LIKE @name

Procedura wywołana z parametrem zwraca dane dotyczące konkretnej tabeli , bez parametru zwraca informacje o wszystkich tabelach tymczasowych

wtorek, 9 listopada 2010

linked SQL Server

Linkowanie SQL Server w Oracle jest możliwe dzięki Oracle Transparent Gateway for MS SQL Server, jeżeli nie zainstalowałeś go przy instalacji serwera można go ściągnąć ze strony oracla.
Edytujemy plik konfiguracyjny

$ORACLE_HOME/tg4msql/admin/inittg4msql.ora

Wypełniając sekcje

HS_FDS_CONNECT_INFO="SERVER=name_server;DATABASE=name_db"
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=user
HS_FDS_RECOVERY_PWD=pass

Skonfiguruj listnera  $ORACLE_HOME/network/admin/listener.ora

(SID_DESC =
 (PROGRAM = tg4msql)
 (SID_NAME = SQLServer)
 (ORACLE_HOME = /home/oracle/product/10.2.0/db_1/
 )
 )
Restartujemy serwis listera

lsnrctl stop
lsnrctl start

edytujemy plik $ORACLE_HOME/network/admin/tnsnames.ora

SQLServer=
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)
  (HOST = sqlserver)
  (PORT = 1521)))
 (CONNECT_DATA =
 (SID = SQLServer)
 )
 (HS=OK)
 )

Z konsoli SQL*Plus wykonujemy polecenie

SQL> create database link msql
connect to user
identified by pass
using 'SQLServer';

Do bazy odwołujemy się przy pomocy zapytania ze składnią T-SQL dodając na końcu nazwę linka „@mssql”

SQL> select count(*) from tabela_test@msql;

  COUNT(*)
----------
     12237

SQL>

poniedziałek, 8 listopada 2010

status joba


Joby jak wiadomo są sekwencyjne więc nawet jeżeli zrobimy joba i będziemy go  uruchamiać co kilka sekund a czas jego wykonania będzie dłuższy to następne wywołanie z harmonogramu zostaną pominięte do czasu aż skończy się poprzednie wywołanie.
Jeżeli przed wywołaniem „joba B” chcemy mieć pewność że „job A” się zakończył, możemy ich uruchomienie włożyć do „jobaC” jako kolejne kroki

Krok 1
EXEC MSDB.DBO.SP_START_JOB @JOB_NAME = 'job A'

Krok 2

EXEC MSDB.DBO.SP_START_JOB @JOB_NAME = 'job B'


Można też w pierwszym kroku „joba B „ sprawdzać czy „job A” działa, procedura w prosty sposób zwraca aktualny stan joba.

CREATE PROC rk_status_job @job_name varchar(200)
as
declare     @job_id UNIQUEIDENTIFIER
DECLARE @job_owner sysname

SELECT @job_id =job_id,@job_owner=s.name FROM msdb..sysjobs_view v
INNER JOIN  sys.syslogins s
ON v.owner_sid =s.sid

       DECLARE @xp_results TABLE (job_id                UNIQUEIDENTIFIER NOT NULL,
                  last_run_date         INT              NOT NULL,
                  last_run_time         INT              NOT NULL,
                  next_run_date         INT              NOT NULL,
                  next_run_time         INT              NOT NULL,
                  next_run_schedule_id  INT              NOT NULL,
                  requested_to_run      INT              NOT NULL,
                  request_source        INT              NOT NULL,
                  request_source_id     sysname          COLLATE database_default NULL,
                  running               INT              NOT NULL,
                  current_step          INT              NOT NULL,
                  current_retry_attempt INT              NOT NULL,
                  job_state             INT              NOT NULL)
                                 
            INSERT INTO @xp_results
            EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,'sa',@job_id
            SELECT @job_name job,
            status =case running
               when 0 then 'Not idle'
               when 1 then 'Executing'
               when 2 then 'Waiting for thread'
               when 3 then 'Between retries'
               when 4 then 'Idle'
               when 5 then 'Suspended'
               when 7 then 'Performing completion actions'
               ELSE '???'
            END,
            running status_id
            FROM @xp_results
Wywołanie

EXEC rk_status_job 'job A

--------------------

job                       status         status_id
--------------------------------------- -----------
job A                     Not idle       0