RcBuilder@walla.com
call centre: 054-5614020

LINQ 2 SQL

Posted by: RcBuilder  /   Category: ADO.NET / button / Button / DBA / div / GridView / HTML / LINQ 2 SQL / SOURCE CODE / SQL   /   No Comments
09
Nov
2014

LINQ 2 SQL STEPS 1. add new Item -> Linq To SQL Classes (.dbml) 2. open server explorer -> create new connection to the desired dataBase 3. drag-drop the tables you want to map to objects remark: you can also drag stored Procedures, views and more ... 4. add -> associations (create the connections between the tables - foreign keys) 5. use.

Read more

SQL PIVOT

Posted by: RcBuilder  /   Category: DATES / DBA / SQL / SQL Data Types / SQL Function / Stored Procedure   /   No Comments
07
Oct
2014

SQL PIVOT creates an horizontal group by. MUST define the groups names. structure pivot(<aggregation function> FOR <group by column> IN (<group by column values>)) USING example 1: declare @tbl1 table(Name nvarchar(10), Quantity int) insert into @tbl1 SELECT 'Roby', 100 UNION ALL SELECT 'Avi', 40 UNION ALL SELECT 'Avi', 60 UNION ALL SELECT 'Avi', 15 UNION ALL SELECT 'Ron', 50 UNION ALL SELECT 'Roby', 77 UNION ALL SELECT 'Roby', 12 UNION.

Read more

SQL COUNT NULLIF

Posted by: RcBuilder  /   Category: DBA / SQL / SQL Function / Stored Procedure / System Function   /   No Comments
28
Sep
2014

SQL COUNT NULLIF // nullif return null if the condition is true // use this technique to count multiple counters in a single group by clause declare @tbl table(Id int, isOK bit, Grade tinyint) insert into @tbl select 1, 1, 50 union all select 1, 1, 75 union all select 4, 0, 25 union all select 6, 1, 54 union all select 6, 0,.

Read more

SQL aggregation with NULL values

Posted by: RcBuilder  /   Category: Aggregation / DBA / SQL   /   No Comments
28
May
2014

SQL aggregation functions with null values all aggregation sql functions count only non null rows!! therefore, we can use this rule in order to filter out unwanted rows examples: // sum only firstDeposit rows SUM(case when firstDeposit = 0 then null else USDValue end) // count only firstDeposit rows // if firstDeposit = 0 return NULL COUNT(NULLIF(0,.

Read more

SQL UNION Example

Posted by: RcBuilder  /   Category: DBA / SQL / SQL Function / Stored Procedure   /   No Comments
28
May
2014

SQL UNION Example declare @t1 table(Name varchar(20)) insert into @t1 select 'ROBY' UNION ALL select 'STEVE' UNION ALL select 'YOSSI' UNION ALL select 'RON' UNION ALL select 'OFIR' declare @t2 table(Name varchar(20)) insert into @t2 select 'TAL' UNION ALL select 'ITAY' UNION ALL select 'SHIRLY' ;with temp(Name) AS( select * from @t1 UNION ALL select * from @t2 ) select * from.

Read more

SQL CTE with insert into Example

Posted by: RcBuilder  /   Category: CTE / DBA / SQL / Stored Procedure   /   No Comments
28
May
2014

SQL CTE with insert into Example declare @Depositors table(BURowID int, FTDDepositors int, Depositors int) ;with CTE_Depositors(OwningBusinessUnit, AccountId, FTDDepositors, Depositors) AS( select OwningBusinessUnit, lv_accountid, COUNT(case when lv_firsttimedeposit = 0 then null else 1 end), COUNT(1) from Leadercapital_MSCRM.dbo.lv_monetarytransactionBase group by OwningBusinessUnit, lv_accountid ) insert into @Depositors select BU.RowID, COUNT(D.FTDDepositors), COUNT(D.Depositors) from @BusinessUnits BU, CTE_Depositors D where BU.BusinessUnitId = D.OwningBusinessUnit -- join -- group by.

Read more

SQL CTE with UNION Example

Posted by: RcBuilder  /   Category: CTE / DBA / SQL / SQL Function / Stored Procedure   /   No Comments
28
May
2014

SQL CTE with UNION Example declare @TradingDataOrders table(TPName nvarchar(100), TradingVolume money) ;with CTE_trades(TPName, TradingVolume) AS( select CAST(ClientNumber AS varchar(10)), Volume from dbo.ClosedTrades where (OpenTime between @dtFrom and @dtTo) OR (CloseTime between @dtFrom and @dtTo) UNION ALL select CAST(ClientNumber AS varchar(10)), Volume from dbo.OpenTrades where OpenTime between @dtFrom and @dtTo ) insert into @TradingDataOrders select * from.

Read more

Server ‘SERVERNAME’ is not configured for RPC

Posted by: RcBuilder  /   Category: BUGS / DBA / Linked Server / Master / SQL   /   No Comments
08
May
2014

Server ‘SERVERNAME’ is not configured for RPC error message: -------------- Server .... is not configured for RPC. problem: after adding linked server and trying to execute remote stored procedure located in this server exception was thrown regular queries not throw exception!! fix: * in the local server go to the defined linked server (Server Objects -> Linked Servers) * properties -> Server Properties.

Read more

SQL recursive using CTE

Posted by: RcBuilder  /   Category: DBA / SQL / SQL Data Types / SQL Function / Stored Procedure   /   1 Comment
08
May
2014

SQL recursive using CTE Data source Id Name Parent 1 'ROOT ITEM NULL 2 'PARENT 1' '1' 3 'PARENT 2' '1' 4 'PARENT 3' '1' 5 'PARENT 1' 'SON 1 2' 6 'PARENT 1' 'SON 2 2' 7 'PARENT 1' 'SON 3 2' 8 'PARENT 2' 'SON 1 3' 9 'PARENT 2' 'SON 2 3' 10 'PARENT 2' 'SON 3 3' 11 'PARENT 2' 'SON 4 3' 12 'PARENT.

Read more
12
  • פיתוח מערכות
  • פתרונות טכנולוגים
  • קידום אתרים
  • בניית אתרים