RSS

Archiwum kategorii: SQL

T-SQL – Moje ściągawki cz.1

1. Wprowadzanie wybranych danych do tabel:

insert into Uzytkownicy (Uzytkownik, RodzajUzyt, Nazwisko) values ('sa2','A','sa2')

2. Wprowadzanie wielu danych do tabeli:

insert into Uzytkownicy (Uzytkownik, RodzajUzyt, Nazwisko) values ('sa2','A','sa2'),('sa3','A','sa3'),('sa4','A','sa4')

3. Wprowadzanie danych z wyniku innego zapytania:

insert into limitykupieckie
(kontrahent, rokod, mcod, limit)
select kontrahent, '2013' as rokod, '11' as mcod, '25000' as limit from kontrahenci where grlimitu='h' and kontrahent not in (select kontrahent from limitykupieckie)

4. Sortowanie wyników zapytań:

select * from Uzytkownicy order by Uzytkownik

Odwrócenie sortowania wyników:

select * from Uzytkownicy order by Uzytkownik desc

5. Użycie operatora in w zapytaniu:

select * from Uzytkownicy where Uzytkownik in ('sa','sa2','sa3')

lub not in
select * from Uzytkownicy where Uzytkownik not in ('sa','sa2','sa3')

6. Zapytanie jako wynik dla operatora in:

select * from Sprzedaz where idSprzedaz in (select idSprzedaz from Sprzedaz where DataSprzedazy>'01.01.2015')

7. Dane pasujące do wzorca:
Dowolne znaki pasujące po P*
select * from Sprzedaz where SposobZaplatyPropozycja like 'P%'

Tylko jeden znak pasujący po P*
select * from Sprzedaz where SposobZaplatyPropozycja like 'P_'

8. Zapytanie dające tylko unikatowe wyniki:

select distinct SposobZaplatyPropozycja from Sprzedaz

9. Modyfikacja danych w tabelach:

update Uzytkownicy
set Nazwisko='Bartek'
where Uzytkownik like 'sa_'

lub kilku kolumn:

update Uslugi
set KodUslugi='woda',JednostkaMiary='m3'
where idUslugi=67

10. Usuwanie danych:

delete from Uzytkownicy
where Uzytkownik like 'sa_'

11. Łączenie kilku zapytań w jeden wynik:

select * from Uzytkownicy where RodzajUzyt='U'
union
select * from Uzytkownicy where RodzajUzyt='A'

Wyniki muszą mieć taką samą ilość kolumn.

union all pokazuje również duplikaty w wynikach zapytań.

12. Zapytanie pokazujące wyniki tylko te które się pokrywają w dwóch zapytaniach:

select * from KodyUrlopow
intersect
select * from KodyUrlopow where RozliczenieJednostka='D'

13. Zapytanie pokazujące wyniki tylko te które się nie pokrywają w dwóch zapytaniach:

select * from KodyUrlopow
except
select * from KodyUrlopow where RozliczenieJednostka='D'

14. Zliczanie ilości wierszy w zapytaniu :

select count(1) from Uzytkownicy

15. Szukanie ekstremów wartości :

select max(WartoscNetto) from SprzedazPozycje
select min(WartoscNetto) from SprzedazPozycje

średnia :

select avg(WartoscNetto) from SprzedazPozycje

suma :

select sum(WartoscNetto) from SprzedazPozycje

16. Użycie wielu funkcji oraz aliasy:

select avg(WartoscNetto) as 'Średnia', max(WartoscNetto) as 'Max', min(WartoscNetto) as 'Min' from SprzedazPozycje

17. Zapytanie grupujące wyniki po danej tabeli:

select idUslugi, sum(WartoscOgolem) from SprzedazPozycje group by idUslugi

18. Having czyli ograniczenie wyników po użyciu funkcji group by :

select idUslugi, sum(WartoscOgolem) from SprzedazPozycje group by idUslugi having sum(WartoscOgolem)>100

19. Funkcje zaokrąglania oraz obcinania cyfr po przecinku :

SELECT
OpisPozycji AS Pozycja,
Round(CenaNetto*StawkaVAT/100,2) AS WartoscZaokraglonaDO2MiejscPOprzecinku,
Floor(CenaNetto*StawkaVAT/100) AS WartoscBezLiczbPoPrzecinku
from SprzedazPozycje

20. Funkcja between czyli „pomiędzy” :

SELECT
OpisPozycji AS Pozycja,
Round(CenaNetto*StawkaVAT/100,2) AS Wartosc,
Floor(CenaNetto*StawkaVAT/100) AS WartoscBezLiczbPoPrzecinku
from SprzedazPozycje
where Floor(CenaNetto*StawkaVAT/100) between 1 and 20

21. Warunek case w polu where :

select * from Sprzedaz where OkresRok= CASE WHEN (select max(okresrok)-1 from OkresySprzedazy)=2015 THEN 2015 ELSE 2014 END

22. Case w wyniku select :

select
SP.IDSPRZEDAZPOZYCJE
,SP.IDSPRZEDAZPOZYCJEKOREKTA
,S.REJESTRSPRZEDAZYPROPOZYCJA
,SP.OPISPOZYCJI
,SP.WARTOSCOGOLEM
,sp.idSprzedazPozycjeKorekta as KOREKTAPOZYCJI
,sp.idSprzedazPozycje as PIERWSZAPOZYCJA
,(select WartoscOgolem from SprzedazPozycje where idSprzedazPozycje=sp.idSprzedazPozycjeKorekta) as War1
,(select WartoscOgolem from SprzedazPozycje where idSprzedazPozycje=sp.idSprzedazPozycje) as War2
,case when sp.idSprzedazPozycjeKorekta0 then (select WartoscOgolem from SprzedazPozycje where idSprzedazPozycje=sp.idSprzedazPozycje)-(select WartoscOgolem from SprzedazPozycje where idSprzedazPozycje=sp.idSprzedazPozycjeKorekta) else (select WartoscOgolem from SprzedazPozycje where idSprzedazPozycje=sp.idSprzedazPozycje) end
from
Sprzedaz S
left join SprzedazPozycje sp on SP.IDSPRZEDAZ=S.IDSPRZEDAZ
where
(S.OkresRok=2015) and
(S.OkresMc=3)

Reklamy
 
Dodaj komentarz

Opublikował/a w dniu Luty 20, 2015 w SQL

 

Zmiana hasła sa w SQL2008R2 jeżeli nie mamy SSMS

Zmiana hasła konta sa w SQL2008R2 jeżeli nie mamy SSMS ale mamy dostęp z konta administratora do konsoli
1. Start -> cmd -> osql -S localhost -E
2. gdy pojawi nam się 1> to wpisujemy : sp_password NULL, ‚nowe hasło’, ‚sa’
go

Jeżeli nie mamy możliwości logowania z autoryzacją Windows to odpalamy SQL Server Configuration Manager i przestawiamy na Enable Shared Memory. Restart serwera i możemy już zmienić hasło.

 
Dodaj komentarz

Opublikował/a w dniu Lipiec 15, 2014 w SQL

 

Płatnik 9.01.001 już jest !

Pojawił się długo oczekiwany Płatnik w wersji 9.01.001 z obligatoryjną obsługą serwera MS SQL co można uznać za duży plus. Całą lista zmian dostępna jest oczywiście na stronie http://www.platnik.info.pl/.
Podręczniki administratora można pobrać stąd http://www.platnik.info.pl/v901001/dok/PodrecznikAdministratora.pdf a użytkownika stąd http://www.platnik.info.pl/v901001/dok/PodrecznikUzytkownika.pdf.

Z dotychczasowych przejść na nową wersję zauważyłem, że najlepiej jest zainstalować sobie serwer MS SQL od 2005 w górę z SQL Server Management Studio i jeszcze na wersji 8.01.001A dokonać migracji na serwer SQL a dopiero potem zainstalować nowego płatnika.

A to kilka problemów i rozwiązań w nowym płatniku :
1. Pojawia się komunikat „Obiekt Nupdater_com nie został poprawnie zainstalowany.
„Błąd inicjacji interfejsu INUSZKlient. Komponent NUSZKlient_COM nie może być zarejestrowany w systemie”

rozwiązanie :
Start > w polu „uruchom” wpisujemy cmd -> przechodzimy do katalogu Płatnika (cd „C:\Program Files (x86)\Asseco Poland SA\Płatnik” ) -> i wydajemy polecenie :
rejasm ASSECO.IMIR.dll
rejasm NUpdater_COM.dll
rejasm NUSZKlient_COM.dll

2. Po utworzeniu i weryfikacji dokumentu ZUS DRA w programie Płatnik (wer. 9.01.001) otrzymuję komunikaty:
– „Dokument nie może być wysłany, ponieważ dane płatnika nie zostały zidentyfikowane w ZUS.” – w polu: NIP oraz
– „Dokument nie może być wysłany, ponieważ dane ubezpieczonego nie zostały zidentyfikowane w ZUS.” – w polu: Kod tytułu ubezpieczenia.

rozwiązanie :

Administracja -> Aktualizuj komponenty programu i dane płatnika

3. Błąd WININET Numer 2

rozwiązanie :

Internet Explorer czy nie jest on w trybie offline

5.Podczas migracji z bazy Accessowej na MS SQL pojawia nam się komunikat :
„SQL Server blocked access to STATEMENT
ad hoc distributed queries..”

Rozwiązanie nr 1:
Odaplamy SQL Server Management Studio -> New Query -> wybieramy bazę master -> wklejamy to:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

a na koniec Execute!

Rozwiązanie nr 2 dla SQL 2005 opisalem tutaj : https://lazowski.wordpress.com/2010/03/27/ad-hoc-distributed-queries-a-platnik-na-sql/

Rozwiązanie nr 3 dla SQL 2008 i wyżej :
Odpalamy SQL Server Management Studio -> prawy przycisk myszki na nazwie naszego serwera -> Facets -> Zmieniamy Facets na Surface area configurations -> AdHocRemoteQueriesEnabled zmieniamy na True

6. „OLE DB provider „Microsoft Jet OLEDB.4.0 for linked Server (null) returned message. Nie można użyć pliku lub Plik jest już w użyciu.

W płatniku w miejscu wyboru serwera zmieniamy na ip zamiast nazwy serwera np: 192.168.0.101\sqlexpress

7. Zanim zaczniemy instalację nowego Płatnika dobrze jest zainstalować MSXML 4.0 Service Pack 2 do pobrania tutaj http://www.microsoft.com/en-us/download/details.aspx?id=19662

Kolejne rzeczy będę dopisywał tutaj.

 
27 Komentarzy

Opublikował/a w dniu Grudzień 8, 2013 w płatnik, SQL

 

Automatyczny backup baz w Microsoft SQL Express

Darmowy serwer bazodanowy jakim jest Microsoft SQL Express oprócz limitu wykorzystania jednego fizycznego procesora, 1 GB pamięci RAM i 12 GB limitu wielkości bazy (R2)  , został pozbawiony Agenta SQL odpowiedzialnego między innymi za uruchamianie procesów na bazach w ustalonym harmonogramie, skutecznie ogranicza to również stworzenie zadania kopii znanego z pełnego rozwiązania Microsoft SQL Server. Można jednak to obejść dzięki skryptom i harmonogramu zadań Windowsa.

Skrypt backup.sql

USE NazwaBazy;
GO
BACKUP DATABASE  NazwaBazy
TO DISK = 'e:\backup\ NazwaBazy.Bak'
WITH FORMAT,
STATS = 10,
MEDIANAME = ' NazwaBazyBackup',
NAME = 'Pelny BackUP NazwaBazy';
GO

Plik wykonujący skrypt backup.sql który musimy wrzucić do harmonogramu : sqlbackup.cmd

SQLCMD.EXE -i backup.sql

 
1 Komentarz

Opublikował/a w dniu Styczeń 27, 2013 w SQL

 

Tagi:

Zezwalamy na połączenie z zewnątrz z serwerem SQL Server 2008 Express

Jeżeli podczas próby połączenia z zewnętrznym hostem zobaczymy następujące komunikaty :
a) (provider: SQL Network Interfaces, error: 28 – Server doesn’t support requested protocol) (Microsoft SQL Server)
b) (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) (Microsoft SQL Server)

To będzie oznaczało, że nie do końca ustawiliśmy nasz serwer. Domyślnie SQL Server 2008 Express i jego starsze wersje nie dopuszczają połączeń z zewnątrz. Żeby to zmienić musimy wykonać parę prostych ruchów:

1) Odpalamy menadżera konfiguracji w Start -> Programy -> Microsoft SQL Server 2008 -> Configuration Tools -> SQL Server Configuration Manager.
2) Przechodzimy do sekcji SQL Server Services
3) Otwieramy właściwości SQL Server Browser , w pierwszej zakładce zmieniamy konto z jakiego ma się uruchamiać agent na Local Service. W drugiej zakładce Start mode zmieniamy na Automatic
4) To samo co w punkcje 3 najlepiej zrobić z SQL Server.
5) Przechodzimy do SQL Server Network Configuration -> Protocols for SQLEXPRESS i po kliknięciu na każdą z pozycji zmieniamy na Enable
6) Po wykonaniu tych czynności musimy zrestartować nasz serwer. Możemy to zrobić ponownie uruchamiając komputer lub poprzez menadżera w SQL Server Services -> Prawy klik na SQL Server (SQLEXPRESS) i Restart.
7. Otwieramy Microsoft SQL Server Management Studio i łączymy się z naszą instancją serwera SQL przy pomocy autoryzacji Windowsa.
8. Naciskamy prawy przycisk na nazwie naszej instancji(drzewo serwera po lewej) i wybieramy z menu Properties -> Security -> i wybieramy -> SQL Server and Windows Authentication mode
9. Na koniec restartujemy serwer z tego samego menu kontekstowego co w punkcie 8.

Dobrze jest podczas testów wyłączyć wszystkie firewalle(windowsowe i te z antywirusów). Porty jakie później można otworzyć to 1433 + 1434 TCP

 
2 Komentarze

Opublikował/a w dniu Styczeń 2, 2012 w SQL, windows

 

Moje notatki – SQL Server 2008 R2 – część 2 – Procedury

Procedury składowane działają szybciej niż taki sam skrypt. Dzieje się to z kilku prostych przyczyn. Ponieważ SQL Server przy każdym uruchamianiu skryptu musi poczynić kilka ważnych kroków takich jak:
* sprawdzenie poprawności wpisanego skryptu
* sprawdzenie statystyk
* wykonanie planu zapytania
Jeśli wykonywana jest skrypt SQL wszystkie te operacje robione są podczas jego uruchomienia. Procedura składowana jest z definicji sprawdzana przez kompilator podczas jej tworzenia, a plan zapytania jest tworzony automatycznie przy jej pierwszym uruchomieniu. Tak więc przy dodatkowym atutem procedur jest szybkość ich działania.

Specyfikacja ważniejszych typów danych:
TINYINT – liczba jednobajtowa. Wartości od -128 do 127.
UNSIGNED TINYINT – liczba jednobajtowa bez znaku. Wartości od 0 do 255. Słowo ”UNSIGNED” przed nazwami kolejnych typów liczbowych robi to samo, co w tym przypadku.
SMALLINT – liczba dwubajtowa. Wartości od -32768 do 32767, a bez znaku od 0 do 65535.
MEDIUMINT – liczba trzybajtowa. Wartości od -8388608 do 8388607, a bez znaku od 0 do 16777215.
INT – liczba czterobajtowa. Wartości od -2147483648 do 2147483647, a bez znaku od 0 do 4294967294.
BIGINT – liczba ośmiobajtowa. Wartości od -9223372036854775808 do 9223372036854775807, a bez znaku od 0 do 18446744073709551615.
FLOAT – liczba zmiennoprzecinkowa czterobajtowa (tak, jak w PHP).
VARCHAR(M) – tekst o długości N od 0 do M znaków, gdzie M < 256. W pamięci zajmuje N + 1 bajtów (dodatkowy zawiera długość tekstu).
CHAR(M) – tekst o długości od 0 do M znaków, gdzie M < 256. W przeciwieństwie do poprzedniego typu, zajmuje w pamięci zawsze M bajtów nawet, jeżeli znajdujący się w nim tekst jest krótszy.
TEXT – typ tekstowy doskonały do przechowywania dłuższych treści. Można w nim zmieścić aż 64 kB danych (65535 znaków).
BLOB – typ do przechowywania danych binarnych, np. plików. Maksymalna wielkość to także 64 kB.
BOOL – typ logiczny, równoważnik zapisu ”TINYINT(1)”.
DATE – wyspecjalizowany typ do przechowywania daty. Bardzo rzadko wykorzystywany w poważniejszych aplikacjach PHP ze względu na jego niewygodne przetwarzanie i formatowanie

1. Tworzenie procedury :
CREATE PROCEDURE proc03
@OSOBA NVARCHAR(32)
AS
SELECT * FROM OSOBOADRESY
WHERE OSOBA = @OSOBA

2. Wywoływanie procedury :
EXEC PROC03

3. Zmiana procedury :
ALTER PROCEDURE proc03
@OSOBA NVARCHAR(32)
AS
SELECT * FROM OSOBOADRESY
WHERE OSOBA >= @OSOBA

4. Procedura z funkcją if
CREATE PROCEDURE KALKULATOR
@LICZBA1 INT,
@LICZBA2 INT,
@ZNAK VARCHAR(1)
AS
IF @ZNAK = '+' SELECT @LICZBA1+@LICZBA2 AS 'WYNIK'
IF @ZNAK = '-' SELECT @LICZBA1-@LICZBA2 AS 'WYNIK'
IF @ZNAK = '*' SELECT @LICZBA1*@LICZBA2 AS 'WYNIK'
IF @ZNAK = '/' SELECT @LICZBA1/@LICZBA2 AS 'WYNIK'
ELSE SELECT 'ZŁY ZNAK'

Wywołanie tej procedury :
EXEC KALKULATOR 1,2,'+'

CDN;]

Baza AdventureWorks 2008R2 SR1 do nauki :
http://msftdbprodsamples.codeplex.com/releases/view/55926
GDY PIERWSZE NIE ZADZIAŁA
http://msftdbprodsamples.codeplex.com/releases/view/59211

 
Dodaj komentarz

Opublikował/a w dniu Czerwiec 7, 2011 w SQL

 

Tagi:

Moje notatki – SQL Server 2008 R2 – część 1

1. Logowanie do serwera przy pomocy sqlcmd i autoryzacji Windowsa :
sqlcmd -S .\SQL -E
gdzie .\SQL to nasz serwer

2. Dodawanie nowych loginów z konsoli i nadawanie im uprawnień :
CREATE LOGIN root WITH PASSWORD='hasloroota'
GO

Nadajemy uprawnienia SYSADMIN dla użytkownika root
SYS.SP_ADDSRVROLEMEMBER 'root' , 'sysadmin'
GO

3. Zmiana hasła z konsoli :
ALTER LOGIN root WITH PASSWORD='nowe'
GO

4. Wykonanie komendy z opóźnieniem :
WAITFOR DELAY '000:00:20'
SELECT '20 SEKUND'
GO

5. SELECT z automatycznym dokańczaniem :
SELECT * FROM AdventureWorksLT.SalesLT.Address
WHERE AddressID like '9%'
GO

lub
select * from Osoby where Pesel like '[5-6]%'

6. Zaokrąglanie do 2 miejsc po przecinku
update SalesLT.Product
set ListPrice = ROUND(ListPrice * 1.1, 2)
where Name like 'AWC%'
GO

7. Pokazuje aktualnie zalogowanych użytkowników do serwera niebędących użytkownikiem sa:
CREATE TABLE Who
(spid INT, ecid INT, status VARCHAR(150), loginame VARCHAR(150),
hostname VARCHAR(150), blk INT, dbname VARCHAR(150), cmd VARCHAR(150), request_id VARCHAR(150))
INSERT INTO Who
EXEC sp_who
SELECT *
FROM Who
WHERE loginame!='sa'
WAITFOR DELAY '000:00:02'
DROP TABLE Who

Zabijanie procesów
KILL numerek_spid

8. Ograniczenie SELECTA do pokazania tylko 10 linijek
SET ROWCOUNT 10
SELECT * FROM Osoby
SET ROWCOUNT 0

9. Sprawdzanie poprawności bazy :
DBCC CHECKDB WITH PHYSICAL_ONLY

parametr PHYSICAL_ONLY sprawdza fizyczną strukturę pliku na dysku
innym fajnym parametrem jest NO_INFOMSGS, który pokaże nam tylko podsumowanie bez zbędnych informacji ( przez zbędne mam na myśli wszystko to co się pojawi gdy odpalimy DBCC CHECKDB bez żadnego parametru )

10. Naprawa bazy :
Naprawianie bazy wymaga trybu SINLE_USER na bazie.
ALTER DATABASE NaszaBaza
SET SINGLE_USER
GO
DBCC CHECKDB(NaszaBaza,REPAIR_REBUILD) WITH NO_INFOMSGS

Wszystkie opcje dla naprawiania to : REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD

Wracamy do trybu MULTI USER
ALTER DATABASE NaszaBaza
SET MULTI_USER

11. Tworzymy widoki :
Widok może być bardzo prosty, np:
CREATE VIEW V1
AS SELECT * FROM OSOBY

Jak i bardziej złożony :
CREATE VIEW OSOBOADRESY
AS SELECT O.Osoba, O.Nazwisko, O.Imie1, O.DataUrodzenia, AO.Miejscowosc, AO.KodPocztowy, AO.Ulica, AO.NumerDomu, AO.NumerLokalu FROM Osoby O
LEFT JOIN OsobyAdresy AO ON O.idOsoby=AO.idOsoby

Od tej pory możemy wykonywać polecenie SELECT na widoku jak na zwykłej tabeli, np:
SELECT * FROM OSOBOADRESY
WHERE OSOBA>='00020'

W bazie zapamiętane zostaną tylko definicje poszczególnych kolumn widoków, nie zaś wartości!

12. Łączenie zapytań poprzez UNION. Za pomocą operatora UNION możemy dodać wyniki poszczególnych zapytań (czyli zwiększyć liczbę wierszy wyniku. Złączenia JOIN zwiększały liczbę kolumn, złączenie UNION zwiększa liczbę wierszy). Łączone wyniki muszą składać się z takiej samej liczby kolumn, a poszczególne kolumny muszą być tego samego typu, poza tym konieczne jest, aby występowały one w tej samej kolejności w obu wynikach, np:
SELECT * FROM OSOBOADRESY
WHERE OSOBA>='00020'
UNION
SELECT O.Osoba, O.Nazwisko, O.Imie1, O.DataUrodzenia, AO.Miejscowosc, AO.KodPocztowy, AO.Ulica, AO.NumerDomu, AO.NumerLokalu FROM Osoby O
LEFT JOIN OsobyAdresy AO ON O.idOsoby=AO.idOsoby
WHERE O.Osoba<='00010'

13. Funkcja IF :
IF (SELECT KODPOCZTOWY FROM OSOBOADRESY O WHERE OSOBA='00023') = '78-320' PRINT 'KOD SIĘ ZGADZA'
ELSE UPDATE OsobyAdresy SET KODPOCZTOWY = '78-320' WHERE idOsoby=(select idOsoby from osoby where osoba='00023');

 
Dodaj komentarz

Opublikował/a w dniu Czerwiec 4, 2011 w SQL