Browse by Tags

TSQL Self join for expert
20 May 12 02:57 PM | uzid | with no comments

שאילתה שסופרת שמות של עובדים ע"פ מפתח, בכל פעם שעובד מתחלף הספירה מתאפסת ID Employee Count 1 Rafi 1 Rafi 2 Uzi 2 Uzi 3 Bio 3 Bio 4 Moti 4 Moti 5 Oth 5 Oth declare @temp table ( ID int, Employee nvarchar(500), ncount int ) INSERT INTO @temp (ID, Employee) SELECT * FROM ( SELECT [ProfessionID] ,Employee.Name FROM [DWH].[dbo].Employee )Main Update @temp SET ncount = (select count(1) from @temp a where a.id <= b.id and a.id = b.id) from @temp b select * from @temp ID Employee Count 1 Rafi 1 1...

תגים:DEV, SQL, .Net

MSSQL Tsql - track database file growth over a period.
27 December 11 10:18 AM | uzid | with no comments

Tsql: DECLARE @startDate datetime ; SET @startDate = GetDate (); SELECT PVT . DatabaseName , PVT . [0] , PVT . [-1] , PVT . [-2] , PVT . [-3] , PVT . [-4] , PVT . [-5] , PVT . [-6] , PVT . [-7] , PVT . [-8] , PVT . [-9] , PVT . [-10] , PVT . [-11] , PVT . [-12] FROM ( SELECT BS . database_name AS DatabaseName , DATEDIFF ( mm , @startDate , BS . backup_start_date ) AS MonthsAgo , CONVERT ( numeric ( 10 , 1 ), AVG ( BF . file_size / 1048576.0 )) AS AvgSizeMB FROM msdb . dbo . backupset as BS INNER...

תגים:SQL

MSSQL BackUp DB with percent complete
22 December 11 05:12 PM | uzid | 1 comment(s)

If you have big DB that you need to BackUp and you must see the percents completed then you need to run the following Tsql: select percent_complete , ( estimated_completion_time / 1000 / 60 ) estimated_time , db_name ( database_id ) from sys . dm_exec_requests where command like '%backup%'

תגים:SQL

C# - How to get Datatable from XML
06 January 11 01:19 PM | uzid | with no comments

//XML <?xml version="1.0" encoding="utf-8" ?> <currencies> <currency> <symbol>NIS</symbol> <code>1</code> </currency> <currency> <symbol>USD</symbol> <code>2</code> </currency> </currencies> XElement res = XElement.Load("Currency.xml"); List<Currency> cur = (from x2 in res.Elements("currency") select new Currency { CurrencyID = x2.Element("symbol").Value...

תגים:DEV, SQL, .Net

ADO.NET Entity Model - Using Stored Procedures That Return Non Entity Type
03 January 11 03:31 PM | uzid | 2 comment(s)

In order to get this working you might need to implement a partial class and then specify the method. take a look at this Link

תגים:DEV, SQL, .Net

C# - question marks and TSQL - COALESCE
27 December 10 10:07 AM | uzid | 2 comment(s)

מה המשותף בין שני סימני שאלה ?? ב C# לפונקצית COALESCE ב TSQL : TSQL: SELECT COALESCE(X, Y, Z) AS FirstNotNull FROM MyTable C#: Int32? N1 = null; //Nullable N1; Nullable N2 = 15; Console.WriteLine(N1 ?? N2); התשובה היא בשניהם נקבל את המשתנה הראשון שלא מכיר את NULL

תגים:DEV, SQL, .Net, MOSS

Moss 2007 - How to Get all files from Moss - Part 1
20 December 10 11:13 AM | uzid | 1 comment(s)

הדרך הקלה ביותר לקבלת מידע מפורטל ישירות ל reporting server היא דרך SQL, מצ"ב דרך לא שיגרתית לקבלת כל הקבצים מפורטל כולל גודל לינק וכו... SELECT Webs.Title AS 'Site Name' ,[AllLists].[tp_Title] [List Name] ,[DirName] ,[LeafName] AS [File Name] ,(Size /1024) [Size KB] ,[Version] ,[Docs].TimeCreated ,[TimeLastWritten] ,'http://PortalName/' + DirName + '/' + LeafName AS 'URL' FROM [MOSS_WSS_Content_Portal].[dbo].[Docs] (nolock) INNER JOIN [MOSS_WSS_Content_Portal...

תגים:SQL, .Net, MOSS

Reporting Server - Print report automatically by c#
01 December 10 04:47 PM | uzid | with no comments

הופתעתי לגלות שלא ניתן להדפיס דו"ח ישירות מהקוד אלא צריך לשמור את הדו"ח כתמונה ורק לאחר מכן להדפיס, מצ"ב הקוד המלא static int m_currentPageIndex; static IList m_streams; //First convert the Reporting Server to Image Export(reportViewer1.LocalReport); //Send to the Printer Print(); public static void Print() { if (m_streams == null || m_streams.Count == 0) throw new Exception("Error: no stream to print."); PrintDocument printDoc = new PrintDocument(); if (!printDoc.PrinterSettings...

תגים:DEV, SQL, .Net

MSSQL - Tsql information
21 November 10 07:04 PM | uzid | with no comments

Sizes of All Tables in a Database IF OBJECT_ID('tempdb..#TablesSizes') IS NOT NULL DROP TABLE #TablesSizes CREATE TABLE #TablesSizes (TableName sysname, ROWS BIGINT, reserved VARCHAR(100), DATA VARCHAR(100), index_size VARCHAR(100), unused VARCHAR(100)) DECLARE @SQL VARCHAR(MAX) SELECT @SQL = COALESCE(@SQL,'') + ' insert into #TablesSizes execute sp_spaceused ' + QUOTENAME(Table_Name,'''') FROM INFORMATION_SCHEMA.TABLES EXECUTE (@SQL) SELECT * FROM #TablesSizes...

תגים:DEV, SQL

DataTable - Lambda Expressions and LINQ query
14 November 10 07:44 AM | uzid | with no comments

ישנם מס' דרכים לקבל מידע מאוביקט, הדרך "הקלה" בעיני היא הדרך ה 1 1. Lambda Expressions: int i = dt.AsEnumerable().Where(c => c.Field("emp") == "emp").Count(); 2. Linq: var ResData = from myRow in dt.AsEnumerable() where myRow.Field("Emp") == "Emp" where myRow.Field("MyCardNumber") == "12345" where myRow.Field("MyID") == "1234" where myRow.Field("Mysum") == double.Parse(sum.ToString()) where...

תגים:DEV, SQL, .Net