tag:blogger.com,1999:blog-76738372025-01-02T06:58:26.693+00:00eXtreme ProgrammingXP for the programmerRuihttp://www.blogger.com/profile/08292557643507451155noreply@blogger.comBlogger129125tag:blogger.com,1999:blog-7673837.post-40706636980341230722018-05-10T15:38:00.000+01:002018-05-10T15:39:59.760+01:00T-SQL Error: An aggregate may not appear in the set list of an update statementA reporting project contains a table with the product summary, the table has a lot of KPIs and specifically the "Last Sale Date" of a product.
According to the company rules, a product is sold when it is paid.
The following T-SQL code (shown here very simplified) can be used to update that column:
UPDATE Product
SET LastSalesDate = MAX(Orders.PaymentDate)
FROM Product
INNER JOIN Orders ON (Ruihttp://www.blogger.com/profile/08292557643507451155noreply@blogger.com0tag:blogger.com,1999:blog-7673837.post-40219107133288441032018-04-27T14:39:00.000+01:002018-04-27T15:25:57.914+01:00How to start SQL Agent job from Another serverA common scenario, when managing a server farm, is to have to sync the SQL Agent jobs in several servers.
You may want to start a job on a second server (SQL02) when a job completes on a primary server (SQL01).
The SQL02 may be a reporting or staging sever, and the SQL01 may be the transnational server.
Sql Server has a T-SQL statement - sp_start_job - to start a job on the server Ruihttp://www.blogger.com/profile/08292557643507451155noreply@blogger.com0tag:blogger.com,1999:blog-7673837.post-86743412049421665482018-01-05T12:08:00.000+00:002018-01-05T12:10:21.380+00:00High CPU usage in Visual Studio 2017Visual studio 2017 has a High CPU usage after using it for some time.
The issue is very annoying, since the work computer starts to get very slow and unresponsive.
I fixed my problem by performing the following steps:
Close all the visual studio open instances
Delete all the .suo files from the visual studio solutions folders
Remove all the .vs hidden directories from the visual studio Ruihttp://www.blogger.com/profile/08292557643507451155noreply@blogger.com0tag:blogger.com,1999:blog-7673837.post-87050405050371124832017-06-19T15:13:00.001+01:002017-06-19T15:17:07.127+01:00T-SQL: How to raise an error on a user defined function (udf)A common programming task is to create a user-defined function.
The function can perform some tasks and raise an error if some validation fails.
Let us try to raise an error
CREATE FUNCTION dbo.Division(@op1 INT, @op2 INT)
RETURNS INT
AS
BEGIN
IF (@op2 = 0)
BEGIN
RAISERROR ('Division by zero.', 16, 1);
END
RETURN CAST(@op1 AS DECIMAL(18, 8)) /@op2
END
But this approach Ruihttp://www.blogger.com/profile/08292557643507451155noreply@blogger.com0tag:blogger.com,1999:blog-7673837.post-65061313393079433382017-02-20T13:26:00.000+00:002017-02-20T13:41:11.448+00:00How to backup all the user databasesThe database administrator must backup periodically the databases on the Sql Servers.
The process is tedious and must be automated with a script to avoid forgetting any database.
The simple script can be scheduled on the Sql Server agent.
DECLARE @backupName VARCHAR(255) -- database backup name
DECLARE @databaseName VARCHAR(255) -- database name
DECLARE @path VARCHAR(256) -- Folder for Ruihttp://www.blogger.com/profile/08292557643507451155noreply@blogger.com0tag:blogger.com,1999:blog-7673837.post-66770663819990208662016-06-02T10:47:00.002+01:002016-06-03T07:33:08.187+01:00How to convert a string to a byte array in C#C# allow to easily convert a string to a byte array using the Encoding.ASCII.GetBytes function.
A sample usage is
string sample = "This is a simple string";
byte[] content = Encoding.ASCII.GetBytes(sample);
Note that this method only works if the string is with the ASCII format.
The framework supports other encodings (UTF8, Unicode, UTF32, ...) but a more generic method is needed.
The Ruihttp://www.blogger.com/profile/08292557643507451155noreply@blogger.com0tag:blogger.com,1999:blog-7673837.post-72660566547091495622016-06-01T14:23:00.001+01:002016-06-01T14:23:56.995+01:00T-SQL: How to split a path name in the path and file nameA common task in T-SQL is how to split a path name in the path and file name.
The following code will do the task:
DECLARE @file VARCHAR(200) = 'c:\temp\data\log.txt'
SELECT REVERSE(LEFT(REVERSE(@file), CHARINDEX('\', REVERSE(@file), 1) - 1)) AS [FileName]
SELECT LEFT(@file, LEN(@file) - CHARINDEX('\', REVERSE(@file), 1) + 1) AS [Path]
Ruihttp://www.blogger.com/profile/08292557643507451155noreply@blogger.com0tag:blogger.com,1999:blog-7673837.post-41790963999760044122016-04-07T18:12:00.001+01:002016-04-07T18:12:44.745+01:00Error when creating a Database diagram: Database owner is invalidAfter restoring a database, I got an error when creating a Database diagram. Like the following image:
The error informs that the database owner is invalid.
The solution is to change to database owner using the sp_changedbowner.
To change the database owner for the sa user:
EXEC sp_changedbowner 'sa'
Ruihttp://www.blogger.com/profile/08292557643507451155noreply@blogger.com0tag:blogger.com,1999:blog-7673837.post-30103863014309962232016-04-04T14:22:00.001+01:002016-04-04T14:25:35.791+01:00The mistery of the slow stored procedure in the web App and fast in SSMSSql Server has a lot of mysteries and hidden "features".
In the troubleshooting of a web application page response time, I got a timeout executing a Stored Procedure. The query took more than 30 seconds executing on the SQL Server profiler.
I captured the query and executed it in SQL Server Management Studio (SSMS), to start analyzing the query execution plan.
For my surprise it was Ruihttp://www.blogger.com/profile/08292557643507451155noreply@blogger.com0tag:blogger.com,1999:blog-7673837.post-78022753522596425852016-02-26T15:10:00.004+00:002016-02-26T15:20:22.606+00:00T-SQL: Converting rows to columnsA common task of a database developer is to convert rows to columns from a query result set.
T-SQL has the PIVOT that facilitates the process.
A simple example:
DECLARE @myValues TABLE (Quantity INT, Category VARCHAR(100))
INSERT INTO @myValues (Quantity, Category)
VALUES
(10, 'Fruits'),
(200, 'Vegetables'),
(40, 'Meats')
SELECT *
FROM @myValues
SELECT [Fruits], [Vegetables], [Meats]
Ruihttp://www.blogger.com/profile/08292557643507451155noreply@blogger.com0tag:blogger.com,1999:blog-7673837.post-69298247554303297962015-11-17T13:08:00.002+00:002015-11-17T14:12:11.382+00:00How to fix visual studio 2015 error "The package did not load correctly"When a visual studio extension is installed, a error can occur that gives the following error:
The 'zebre' package did not load correctly.
The problem may have been caused by a configuration change or by the installation of another extension. You can get more information by examining the file 'C:\Users\[user name]\AppData\Roaming\Microsoft\VisualStudio\14.0\ActivityLog.xml'.
Restarting Visual Ruihttp://www.blogger.com/profile/08292557643507451155noreply@blogger.com0tag:blogger.com,1999:blog-7673837.post-73247740177827466952015-10-02T17:18:00.001+01:002015-10-02T17:26:51.778+01:00Dapper: Powerful micro object mapper for .NetIntroduction
Dapper.Net or Dapper is a simple and powerful micro object mapper for .Net, that eases the development of a Data Access Layer (DAL).
Dapper is in production used by Stack Overflow, helpdesk and other companies.
Performance is the major focus in Dapper. It is very fast and the execution time is near equal to a hand coded DAL using the
SqlDataReader.
Dapper uses the POCO (Plain OldRuihttp://www.blogger.com/profile/08292557643507451155noreply@blogger.com0tag:blogger.com,1999:blog-7673837.post-72761950307711489682015-05-12T11:16:00.001+01:002017-01-05T18:09:46.395+00:00Time testing: How to prevent time syncingA common task when performing tests on data sets that vary over time is to advance the windows time.
The server or the workstation can however be part of a domain and the time is synced with it.
In this situation after the date/time is changed for the tests, the sync process will update them to the Domain Controllers value.
This will reset the date/time and the test environment is lost.
A Ruihttp://www.blogger.com/profile/08292557643507451155noreply@blogger.com0tag:blogger.com,1999:blog-7673837.post-15695322386914172922015-03-03T10:39:00.000+00:002015-03-03T10:43:19.151+00:00Fix: Property PopulationStatus is not available for FullTextCatalogAfter restoring a database with full text search active, on Sql Server 2014, on a staging server I got the following error when opening the Full Text Catalog:
Property PopulationStatus is not available for FullTextCatalog '[SearchDB]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)
The error is not self Ruihttp://www.blogger.com/profile/08292557643507451155noreply@blogger.com0tag:blogger.com,1999:blog-7673837.post-248254893267080762014-11-28T11:07:00.001+00:002014-11-28T14:18:33.861+00:00Management Studio: The type initializer for ‘PropertyMetadataProvider’ threw an exception Sql Server Management Studio, gave me the following error after I rebooted my computer:
The type initializer for ‘PropertyMetadataProvider’ threw an exception
I installed the windows updates and started relating the problem to the updates.
After some research I found a interesting artice in Sql Blog about the problem that states that the DLL versions of the cumulative update 4 for SQL Ruihttp://www.blogger.com/profile/08292557643507451155noreply@blogger.com0tag:blogger.com,1999:blog-7673837.post-13906407898633881812014-07-08T16:26:00.006+01:002014-07-08T16:26:51.884+01:00SQL Server Table Value ConstructorThe latest versions of Sql Server (SQL Server 2008 or above) implement the Table Value Constructor.
Definition
The Table Value Constructor defines a set of row value expressions to be added to a table.
It allows us to create tables of values and/or expressions.
The Table Value Constructor allows us to simplify the T-SQL syntax.
Syntax
The Table Value Constructor is defined as:
VALUES (<Ruihttp://www.blogger.com/profile/08292557643507451155noreply@blogger.com0tag:blogger.com,1999:blog-7673837.post-5003949831009093372014-07-01T10:21:00.001+01:002014-07-01T10:27:47.052+01:00SSAS: OLAP PivotTable ExtensionsExcel Pivot tables are a reporting tool that makes it easy to extract information from data sets without the use of formulas.
Excel Pivot tables are great for browsing Analysis Services cubes, since they allow to easily move, pivot and analyze data using drag and drop to see the same data in a number of different ways
OLAP PivotTable Extensions add advanced features to the pivot tables.
OLAP Ruihttp://www.blogger.com/profile/08292557643507451155noreply@blogger.com0tag:blogger.com,1999:blog-7673837.post-85955645639862088822014-06-30T16:07:00.000+01:002014-06-30T16:08:20.853+01:00Turn off Visual Studio 2013 Preview of filesVisual studio 2013 previews the file content when it is selected.
The feature can be very annoying and luckily it can be disabled.
To disable the feature, in the visual studio menu, select Tools -> Options -> Environment -> Tabs
Uncheck all the Preview tab checkboxes.
Ruihttp://www.blogger.com/profile/08292557643507451155noreply@blogger.com0tag:blogger.com,1999:blog-7673837.post-14068003341072006312014-06-06T11:43:00.001+01:002014-06-06T11:43:47.160+01:00Fix SSDT error" Could not load type Microsoft.SqlServer.TransactSql.ScriptDom.OnOffStatisticsOption"The SQL Server Data Tools for SQL Server 2014 (SSDT) are now available for Visual Studio 2012 and 2013.
The SQL Server Data Tools - Business Intelligence for Visual Studio 2013 (SSDT-BI) are also available.
I installed both and after a while the schema compare started failing with the error:
Error 6 Could not load type 'Microsoft.SqlServer.TransactSql.ScriptDom.OnOffStatisticsOption' from Ruihttp://www.blogger.com/profile/08292557643507451155noreply@blogger.com0tag:blogger.com,1999:blog-7673837.post-49548172757043776602014-06-02T17:01:00.002+01:002014-06-02T17:05:37.829+01:00Undocumented sp_MSforeachdb: Iterate through each databaseThe stored procedure sp_MSforeachdb is used to iterate through each database that exists in SQL Server, including the system databases.
The stored procedure is undocumented and can be removed at any time, so use it carefully.
It is used to run a command over a set of databases in a server.
The stored procedure receives a parameter with the command to execute. The ? is used as a placeholder to Ruihttp://www.blogger.com/profile/08292557643507451155noreply@blogger.com0tag:blogger.com,1999:blog-7673837.post-45542579284715095532014-05-27T17:23:00.001+01:002014-05-27T17:23:37.104+01:00Entity Frameword 6 Error: Unable to update the EntitySet 'X' because it has a DefiningQuery and no element exists in the element to support the current operation.I was inserting an object in entity framework 6 using the usual code:
context.X.Add(x);
context.SaveChanges();
The entity framework returned the error:
Unable to update the EntitySet 'X'because it has a DefiningQuery and no element exists in the element to support the current operation.
The solution to fix this error was to create a primary key in the table and update the entity model.Ruihttp://www.blogger.com/profile/08292557643507451155noreply@blogger.com0tag:blogger.com,1999:blog-7673837.post-23564633789535068642014-05-19T09:32:00.002+01:002014-10-28T15:45:18.172+00:00Poor Mans T-SQL formatterA database developer must have a set of SQL coding Standards to avoid each statement being written differently.
Steven Bates has witten the SQL Server 2005 coding Standards in a series of blog posts at the MSDN blogs.
The rules are implemented by a SQL Server Management Studio add-in that is named Poor Mans T-SQL formatter.
The Poor Mans T-SQL formatter is a open-source T-SQL formatter that is Ruihttp://www.blogger.com/profile/08292557643507451155noreply@blogger.com0tag:blogger.com,1999:blog-7673837.post-9054376139129937992014-02-06T15:47:00.000+00:002014-06-12T12:03:03.702+01:00Visual Studio 2013 connection to TFS very slowVisual Studio 2013 connects to Team Foundation Server (TFS) with the TFS client.
In some situations, out of our control, the connection to TFS is verty slow. The source control operations take ages to complete.
The problem appears to be network related, since at the same time one computer is very slow executing a source control operation and the others are working at a normal pace.
The TFS Ruihttp://www.blogger.com/profile/08292557643507451155noreply@blogger.com1tag:blogger.com,1999:blog-7673837.post-18286817402788722482014-02-03T18:06:00.000+00:002014-02-16T00:50:04.768+00:00Faster Android emulator on Intel ArchitectureThe Eclipse is the main stable development environment for Android.
The performance of the Android emulator is really bad, but Intel has a solution for computers on a Intel Architecture.
The recent computers with an Intel processor have the Intel Virtualization Technology enabled, that allows the acceleration of the the Android Emulator.
In win Windows and Apple Mac OS there is the Intel Ruihttp://www.blogger.com/profile/08292557643507451155noreply@blogger.com0tag:blogger.com,1999:blog-7673837.post-2312455897936679512013-07-18T10:49:00.001+01:002013-07-18T10:57:10.225+01:00WCF and the Try-Catch-Abort PatternProxy Classes are used to talk to Windows Communication Foundation (WCF) Services.
A communication channel is open to the WCF Service that must be closed after the calls to the service.
The proper way to close the communication channel is very important.
The WCF proxy is usually generated using Visual Studio or the svcutil tool.
The generated proxy inherits from a base class Ruihttp://www.blogger.com/profile/08292557643507451155noreply@blogger.com1