String to Byte Array and Byte Array to String

          //string to bytes
          var asciiEncoding = new ASCIIEncoding();
          var bytes = asciiEncoding.GetBytes("Hello World");

            //bytes to string
            var utf8Encoding = new UTF8Encoding();
            var message = utf8Encoding.GetString(bytes);

            Console.WriteLine(message);

Linked database, join with different server data

 

Prostrednictvim nasledujiciho kodu mohu selektit data napric databazemi. Po pridani linkovaneho serevru je take potreba ho na konci odstranit.

 

EXEC sp_addlinkedserver 

   N'sv205870.zitd.global.commerzbank.com\DEV_01', 
   N'SQL Server'

GO

 


select * from [sv205870.zitd.abc.com\DEV_01].
[Dev_Jirka].[MD].[Table] as x

inner join dbo.Test on dbo.Test.id = 
x.dimension_sequence

sp_dropserver    N'sv205870.zitd.global.commerzbank.com\DEV_01'

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Firefox is still asking for credentials (Selenium)

 

i.e.  http://sv161242.zitd.global.company.com/Project-PRE is asking for credentials for each session.

the setting is simple:

In the address bar in Firefox, type “about:config”
This will show all the settings for Firefox. 
In this list find this key “network.automatic-ntlm-auth.trusted-uris.” 
This is a comma-delimited list of all host names that you 
want to use NTLM with.
Just enter your host names like this: 
“sv161242.zitd.global.company.com”

http://superuser.com/questions/29624/how-can-i-make-firefox-behave-like-ie-on-a-windows-domain-when-requesting-user-c

Create new database from DB Backup

 

1 CREATE BACKUP – RIGHT CLICK -> TASK -> BACKUP
2 CREATE NEW EMPTY DATABASE CALLED Pragis2_ReleaseTest

------------------------------------------------------------------------------
--- 
---        CREATE NEW DATABASE FROM DATABASE BACKUP
---
------------------------------------------------------------------------------

-- 1 CREATE BACKUP - RIGHT CLICK -> TASK -> BACKUP
-- 2 CREATE NEW EMPTY DATABASE CALLED Pragis2_ReleaseTest

RESTORE DATABASE Pragis2_ReleaseTest FROM DISK = 'D:\db_data\DEVPRAG\SystemTest.bak'
  WITH REPLACE, NORECOVERY,
  MOVE 'Pragis2_SystemTest' TO 'D:\db_data\DEVPRAG\Pragis2_ReleaseTest.mdf' ,
  MOVE 'Pragis2_SystemTest_log' TO 'D:\db_data\DEVPRAG\Pragis2_ReleaseTest_log.ldf', 
REPLACE, NORECOVERY ;

-- AT THIS MOMENT IS DATABASE INRESTORING MODE 

GO
RESTORE DATABASE Pragis2_ReleaseTest WITH RECOVERY

 

http://dba.stackexchange.com/questions/51489/sql-server-2012-restore-backup-to-new-db-name

http://dba.stackexchange.com/questions/6674/database-after-restore-process-still-display-restoring

How to join rows into single text string ?

 

http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string

 

SP_MSFOREACHTABLE

Nasledujici snippet zobrazi obsah vsech tabulek

EXEC sp_msforeachtable 'select * from ? '

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

A tento snippet zas obsah vsech tabulek, ketere maji count >0 

EXEC sp_msforeachtable 
    'DECLARE @cnt AS INT
       select @cnt=count(*) from ? 
        IF(@cnt > 0)   
      BEGIN
           select * from ? 
      END'

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Get row count of all tables in database

If you want to use a query, you can use this (note: it’s using an undocumented stored procedure sp_msforeachtable):

create table #tempcount (tablename nvarchar(128), record_count bigint)
EXEC sp_msforeachtable 'insert #tempcount select ''?'', count(*) from ? with (nolock)'
select * from #tempcount
drop table #tempcount

http://stackoverflow.com/questions/22548938/get-row-count-of-all-tables-in-database-sql-server

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

T-SQL Generate data to table

Tento snipet generuje meny pro jednotlive dny.

 

--clear currencies
truncate table CurrencyRates
go 

IF EXISTS (SELECT * FROM sysobjects WHERE name='udp_FillCurrencies' and type='P')
drop procedure udp_FillCurrencies;
GO
create procedure udp_FillCurrencies(@startDate datetime2,@countOfDays integer, 
      @currency varchar(3), @rate decimal) 
as
begin
    DECLARE @startnum int = 1;
    WITH gen AS (
            SELECT @startnum AS num
            UNION ALL
            SELECT num+1 FROM gen WHERE num+1<=@countOfDays
    )
    insert into CurrencyRates ( [Date]     ,[Name]      ,[Amount]      ,[Rate])
    SELECT DATEADD(day,num,@startDate), @currency , 1, @rate+ CAST(num as float)/100  
     FROM gen option (maxrecursion 10000)
    return;
end;

go

execute udp_FillCurrencies '2015-08-01', 30, 'EUR',27;
execute udp_FillCurrencies '2015-08-01', 30, 'GBP',54;
execute udp_FillCurrencies '2015-08-01', 30, 'HUF',0.05;
execute udp_FillCurrencies '2015-08-01', 30, 'USD',24.07;
execute udp_FillCurrencies '2015-08-01', 30, 'CHF',12.07;
execute udp_FillCurrencies '2015-08-01', 30, 'RUB',2.52;
execute udp_FillCurrencies '2015-08-01', 30, 'PLN',8.16;
go

drop procedure udp_FillCurrencies;
go

select * from CurrencyRates

Favorite SQL interview question

Write a code of user store. Ciustomer, product, and invoice. Select how many items user bought

http://help.filemaker.com/app/answers/detail/a_id/3246/~/relational-database-design-101-%28part-3-of-3%29

result:
Tonda 0 0
Jarda 15 6
Franta 1 1

select Customer.Name , ISNULL(sum(LineItem.count),0) , count(LineItem.InvoiceFk) from Customer
left outer join invoice on invoice.CustomerFk = customer.id
left outer join LineItem on LineItem.InvoiceFk = Invoice.Id
group by Customer.Name
order by Customer.Name DESC

———————————————————————

–count the names
IF OBJECT_ID(‘LineItem’) is not null
drop table LineItem

–count the names
IF OBJECT_ID(‘Invoice’) is not null
drop table Invoice

–count the names
IF OBJECT_ID(‘Product’) is not null
drop table Product

–count the names
IF OBJECT_ID(‘Customer’) is not null
drop table Customer
GO

create table Customer(
Id int primary key identity(1,1),
Name varchar(100),
Address varchar(255)
);

create table Product(
Id int primary key identity (1,1),
Name varchar(100) ,
price money not null
);

create table Invoice(
Id int primary key identity (1,1),
CustomerFk int not null FOREIGN KEY references Customer(Id),
[Date] DateTime not null
);

create table LineItem(
id int primary key identity (1,1),
InvoiceFk int not null FOREIGN KEY references Invoice(Id),
ProductFk int not null FOREIGN KEY references Product(Id),
[count] int not null,
check ([count] > 0)
);

insert into Customer values (‘Jarda’,’Vrbno pod Pradedem’)
insert into Customer values (‘Franta’,’As’)
insert into Customer values (‘Tonda’,’Praha 3′)

insert into Product values (‘Morce’,200)
insert into Product values (‘Pavouk’,750)
insert into Product values (‘Kvetina’,50)
insert into Product values (‘Skalar’,200)

insert into Invoice values (1, ’10-10-2015′ )
insert into Invoice values (1, ’10-12-2015′ )
insert into Invoice values (2, ‘5-4-2015’ )

insert into LineItem values (1, 1,1)
insert into LineItem values (1, 2,5)
insert into LineItem values (1, 3,2)
insert into LineItem values (1, 3,2)

insert into LineItem values (2, 2,2)
insert into LineItem values (2, 3,3)

insert into LineItem values (3, 3,1)

Error500, error 400

Sravne reseni je mit tu konfiguraci 2x – 1x pro IIS  a 1x pro aspnet engine

 

wiz nasledujici clanek

 

http://benfoster.io/blog/aspnet-mvc-custom-error-pages