How to Find Faulty Machine

How to Find Faulty Machine ?

We have 10 Machines that produce Chocolate, each weighing 100 grams.  

One of the machines, however, produces chocolate weighing 90 grams only. 

We are allowed only one weighing and need to determine which machine is faulty.

you have to find faulty machine in only once try.

Machine :  [1] [2] [3] [4] [5] [6] [7] [8] [9] [10]

Bucket    :  [1] [2] [3] [4] [5] [6] [7] [8] [9] [10]

 

 

 

Solutions:

Pick 1   Chocolate From Bucket - 1

Pick 2   Chocolate From Bucket - 2

Pick 3   Chocolate From Bucket - 3

Pick 4   Chocolate From Bucket - 4

Pick 5   Chocolate From Bucket - 5

Pick 6   Chocolate From Bucket - 6

Pick 7   Chocolate From Bucket  -7

Pick 8   Chocolate From Bucket  - 8

Pick 9   Chocolate From Bucket  - 9

Pick 10 Chocolate From Bucket  - 10      
 

If All Machine are Perfect then Weight should be

Total Weight : (1 * 100) + (2 * 100) + (3 * 100) + (4 * 100) + (5 * 100) +

(6 * 100) + (7 * 100) + (8 * 100) + (9 * 100) + (10 * 100)

= 5500

 But Here 1 Machine is Faulty so Weight will be Less then 5500.

Suppose Weight is X;

you will find faulty machine No by below equation :

 

(5500 - X) / 10

will gives you faulty machine No.

lets take an example.

If 6th Machine is Faulty then 

Weight will be:

(1 * 100) + (2 * 100) + (3 * 100) + (4 * 100) + (5 * 100) +

(6 * 90) + (7 * 100) + (8 * 100) + (9 * 100) + (10 * 100)

= 5440

then 

 

= (5500 - 5440) /10 

= 60 / 10 

= 6th Machine is faulty.

as Per this you will get faulty Machine No by Only Once Weighing in One Try.

SQL Server CLR Functions

 SQL Server CLR Functions
You can create a database object inside an instance of SQL Server that is programmed in an assembly created in the Microsoft .NET Framework common language runtime (CLR). Database objects that can leverage the rich programming model provided by the common language runtime include aggregate functions, functions, stored procedures, triggers, and types.
Creating a CLR function in SQL Server involves the following steps:

• Define the function as a static method of a class in a language supported by the .NET Framework. For more information about how to program functions in the common language runtime, see CLR User-Defined Functions. Then, compile the class to build an assembly in the .NET Framework by using the appropriate language compiler.
  
• Register the assembly in SQL Server by using the CREATE ASSEMBLY statement. For more information about assemblies in SQL Server, see Assemblies (Database Engine).
  
• Create the function that references the registered assembly by using the CREATE FUNCTION statement.
  
  CLR functions can be used to access external resources such as files, network resources, Web Services, other databases (including remote instances of SQL Server). This can be achieved by using various classes in the .NET Framework, such as System.IO, System.WebServices, System.Sql, and so on. The assembly that contains such functions should at least be configured with the EXTERNAL_ACCESS permission set for this purpose. For more information, see CREATE ASSEMBLY (Transact-SQL). The SQL Client Managed Provider can be used to access remote instances of SQL Server. However, loopback connections to the originating server are not supported in CLR functions. 
  
  

1. Create the CLR Dll for the SQL function to use, and copy it to SQL Binn
2. Register the CLR Dll in SQL server
3. Create a normal SQL function that uses the CLR Dll 

   1:  using System;
   2:  using System.Collections.Generic;
   3:  using System.Text;
   4:  using Microsoft.SqlServer.Server;
   5:  using System.Data;
   6:  using System.Data.Sql;
   7:  using System.Data.SqlTypes;
   8:  using Encrypt;
   9:  
  10:  public class StoredProcedures
  11:  {
  12:  
  13:      [Microsoft.SqlServer.Server.SqlFunction()]
  14:      public static string Enc(SqlString password, SqlString encStringOut)
  15:      {
  16:          Encryption enc = new Encryption();
  17:          return enc.Encrypt(password.ToString(), encStringOut.ToString());;
  18:      }
  19:  
  20:      [Microsoft.SqlServer.Server.SqlFunction()]
  21:      public static string Dec(SqlString password, SqlString encStringOut)
  22:      {
  23:          Encryption enc = new Encryption();
  24:          return enc.Decrypt(password.ToString(), encStringOut.ToString()); ;
  25:      }
  26:  }

So next we need to do the SQL server work. So firstly I copied the SQLServerEncryption.Dll to the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn directory of the SQL server machine.
I also copied the Dll generated (SQLServerEncryption.Dll) to the C:\ drive on the SQL server machine, as it makes the Dll registration code that needs to be run for SQL a bit easier.
So we’ve copied to \binn and C:\ so far, so now we need to register the Dll with SQL server. So lets look at that
Firstly we need to allow CLR types in the SQL server installation. Which is either done using the following SQL

EXEC dbo.sp_configure ‘clr enabled’,1 RECONFIGURE WITH

Or if you have issues doing it that way use the SQL Server Surface Area 
Configuration, use the “Surface Area Configuration For Features” link, 
and then CLR integration from the presented treeview. Once this is done 
we can register the CLR Dll with SQL, as follows

create assembly SQLServerEncryption from ‘c:SQLServerEncryption.dll’ WITH PERMISSION_SET = SAFE

Now that weve done that, all thats left to do is create a normal SQL 
server function that uses the CLR Dll. Which is simply done as follows 

ALTER FUNCTION [dbo].[ENCRYPT](@password [nvarchar](255), @encStringOut [nvarchar](255))
RETURNS [nvarchar](255) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLServerEncryption].[StoredProcedures].[Enc]

And that it you can now use the CLR Function as you like. For example

dbo.ENCRYPT(‘xxxx’,’sb_SQL’) 

How Add/Drop Extended Store Procedures

Adding an Extended Stored Procedure:

 
To add an extended stored procedure function in an extended stored procedure DLL, you must run the sp_addextendedproc system stored procedure, specifying the name of the function and the name of the DLL in which that function resides. The user executing the command has to be a SQL Server system administrator.
For example, this command registers the function xp_sample, located in xp_sample.dll, as a SQL Server extended stored procedure:

sp_addextendedproc 'xp_sample','xp_sample.dll'

It is a good practice to place the extended stored procedure dll in the SQL Server bin path
along with any dependent dlls. 

SQL Server uses the LoadLibrary() method to locate the dll.

Example :

1. Extract or build the DLL file xp_md5.dll and place it in C:\Program Files\Microsoft SQL Server\MSSQL\Binn (or wherever appropriate). A precompiled DLL is in the Release directory of the source distribution.
2. Create an Extended Stored Procedure called xp_md5 in the "master" database. Right-click "Extended Stored Procedures" under the master database in the Server Manager and click "New Extended Stored Procedure...". Enter xp_md5 for the "Name" and for the "Path", enter the full path to xp_md5.dll. 

USE master;
EXEC sp_addextendedproc 'xp_md5', 'xp_md5.dll'

   3. Create a user-defined function for each database in which you plan to 
use the MD5 procedure. Right-click "User Defined Functions" under the 
appropriate database(s) and click "New User Defined Function...". Enter 
the following:  

 CREATE FUNCTION [dbo].[fn_md5] (@data TEXT) 
RETURNS CHAR(32) AS
BEGIN
  DECLARE @hash CHAR(32)
  EXEC master.dbo.xp_md5 @data, -1, @hash OUTPUT
  RETURN @hash
END

 

Removing an Extended Stored Procedure:

sp_dropextendedproc 'xp_sample','xp_sample.dll'

How Extended Stored Procedures Work
When a client call (from ISQL, your own application) to the extended stored procedure is performed which in turn executes an extended stored procedure, the request is transmitted to the SQL Server. SQL Server then locates the DLL associated with the extended stored procedure, and loads the DLL if it is not already loaded using the LoadLibrary() method. So inherently the first call to the stored procedure will have the overhead of loading the dll. Then SQL server calls the exported extended stored procedure located in the dll, which gets an interface to the SQL Server in form of a Server Procedure. Your procedure can then read parameters passed to it and return back results if it so intends.
However if your extended stored procedure raises an exception its process is killed and you have to restart the SQL server. SQL server has to be restarted even if you intend to replace the XP dll as it may have been loaded.

Debugging an Extended Stored Procedure
To debug an extended stored procedure DLL by using Microsoft Visual C++ follow the following steps.

1. Stop SQL Server.
2. Copy the updated SQL server extended to SQL Server bin directory preferably.
3. Register the SQL Server using the sp_addextendedproc system stored procedure.
4. In the project settings output Debug category for:
   • Executable for debug session: Provide $MSSSQLSERVERPATH$\BINN\sqlservr.exe
   • Working Directory: Provide $MSSSQLSERVERPATH$\BINN
   • Program arguments: -c so that SQL server starts as an application and not as an service.
5. You can then put your break points and debug as normal.

Note:

1. If you plan to use MFC in the stored procedure or any other dependencies be sure that it is statically linked or the dependency dlls are also available in the $MSSSQLSERVERPATH$\BINN path.
2. Your include directory in options must contain the SQL Development tools include and lib paths.
3. I have tried compiling and checking whether it works on SQL Server 6.5, it does. But I doubt Microsoft supports it for version 6.5.

What is Extended Store Procedure ?

What Is Extended Stored Procedures ?

Extended stored procedures let you create your own external routines in a programming language such as C. The extended stored procedures appear to users as regular stored procedures and are executed in the same way. Parameters can be passed to extended stored procedures, and extended stored procedures can return results and return status.

Extended stored procedures are DLLs that an instance of SQL Server can dynamically load and run. Extended stored procedures run directly in the address space of an instance of SQL Server and are programmed by using the SQL Server Extended Stored Procedure API.

After an extended stored procedure has been written, members of the sysadmin fixed server role can register the extended stored procedure with the instance of SQL Server, and then grant permission to other users to execute the procedure. Extended stored procedures can be added only to the master database. 

An extended stored procedure is a C or C++ DLL that can be called from Transact-SQL using the same syntax as calling a Transact-SQL stored procedure. Extended stored procedures are a way to extend the capabilities of Transact-SQL to include any resources or services available to Microsoft Win32 applications. In other words you can create a Extended Stored Procedure in your custom language (VC++, VB, Delphi). These dll's can use API's provided by Open Data Services to interact, control and enhance the functionality of SQL server to provide the functionality you might require. 

When to Use Extended Store Procedures ?

Examples:

1. Imagine you have an application that performs some processing on the basis of the some records inserted newly in a particular table. The common way that one may do the same is open a recordset connection on the table and keep polling and re-querying till a new entry is inserted. The drawback in the above example is polling, it would put a lot of burden on the table on frequently accessed tables. The Extended Stored Procedure would adopt an event based approach, where an extended stored procedure residing on the server would be triggered on a new insert and perform custom processing.
2. Imagine you have an application that needs to perform some checks on some condition and immediately send a response mail to someone notifying of the event. The drawback in the above example is you might have a particular condition to trigger the event today and a totally different condition tomorrow. The Extended Stored Procedure would adopt an event-based approach where your custom dll could generate and manage mail, all that one would have to do is to trigger the Extended Stored Procedure in the SQL Server trigger.
3. Implemented where there is intense string operation, thread management or iteration methods which can be complicated for T-SQL.

How to Use SQL Server CLR Integration

The common language runtime (CLR) is the heart of the Microsoft .NET Framework and provides the execution environment for all .NET Framework code. Code that runs within the CLR is referred to as managed code. The CLR provides various functions and services required for program execution, including just-in-time (JIT) compilation, allocating and managing memory, enforcing type safety, exception handling, thread management, and security.
With the CLR hosted in Microsoft SQL Server (called CLR integration), you can author stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates in managed code. Because managed code compiles to native code prior to execution, you can achieve significant performance increases in some scenarios.
Managed code uses Code Access Security (CAS), code links, and application domains to prevent assemblies from performing certain operations. SQL Server uses CAS to help secure the managed code and prevent compromise of the operating system or database server.
This section is meant to provide only enough information to get started programming with SQL Server CLR integration, and is not meant to be comprehensive. For more detailed information, see the version of SQL Server Books Online for the version of SQL Server you are using.

Beginning with SQL Server 2005, SQL Server features the integration of the common language runtime (CLR) component of the .NET Framework for Microsoft Windows. This means that you can now write stored procedures, triggers, user-defined types, user-defined functions, user-defined aggregates, and streaming table-valued functions, using any .NET Framework language, including Microsoft Visual Basic .NET and Microsoft Visual C#.


In SQL Server 2005 and later version of it database objects can be created which are created in CLR. Stored Procedures, Functions, Triggers can be coded in CLR. CLR is faster than T-SQL in many cases. CLR is mainly used to accomplish task which are not possible by T-SQL or can use lots of resources. CLR can be usually implemented where there is intense string operation, thread management or iteration methods which can be complicated for T-SQL. Implementing CLR provides more security to Extended Stored Procedure.

Let us create one very simple CLR where we will print current system datetime.

1) Open Microsoft Visual Studio >> Click New Project >> Select Visual C# >> Database >> SQL Server Project

2) Either choose from existing database connection as reference or click on Add New Reference.

3) If you have selected existing reference skip to next step or add database reference.

4) Once database reference is added then project will be displayed in Solution Explorer. Right click on Solution Explorer >> Click on Add >> Stored Procedure.

5) Add new stored procedure template from following screen.

6) Once template added it will look like below.    

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server; public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void CLRSPTest()
{
}
};


7) Now where it suggest to //Put your code here. Replace it with your code.
 Once the code is complete do following two steps.
a) Click on menu bar >> Build >> Build ProjectName
b) Click on menu bar >> Build >> Deploy ProjectName
Building and Deploying project should give successful message.


using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server; public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void CLRSPTest()
{
SqlPipe sp;
sp = SqlContext.Pipe;
String strCurrentTime = “Current System DateTime is: “
+ System.DateTime.Now.ToString();
sp.Send(strCurrentTime);
}
};

8) Now open SQL Server Management Studio and run following script in Query Editor. It should return current system datetime. Running it again the time will change.  

USE // Your DB
GO
EXEC dbo.CLRSPTest
GO
 
 

Prepare N Level Recursive/ Hierarchical List Using Common Table Expressions

Display Result In Hierarchical Level  In SQL ?
Prepare Hierarchy as Per Parent - Child Relationship ?


If you want to build recursive query for n level depth then you should go with CTE (Common Table Expressions).

Here i explain Simple Example so you will get better idea.


Declare @Temp Table
(
ID INT IDENTITY(1,1),
Name Varchar(50),
ParentID INT NULL
)

Declare @Depth INT

Set @Depth = 3; -- According No of Max Childs into any Parent ex 3 Means it Works for (Max 10^3 - 1) Childs

--Select * From @Temp

INSERT INTO @Temp
Values    ('HItesh Patel',0),
        ('Ajay Patel',1),
        ('Matang Dave',2),
        ('Tejas Patel',3),
        ('Vishal Patel',4),
        ('Jignesh Patel',7),
        ('Rajesh Patel',1),
        ('Mahesh Patel',3),
        ('Amit Patel',0),
        ('Piyush Patel',1),
        ('Ritesh Patel',1)
 
Select * From @Temp;


WITH List (    ID, Name, IndexNo, lvl) AS
(
    SELECT        i.ID, i.Name,
                CONVERT(FLOAT,ROW_NUMBER() OVER (ORDER BY i.Name)) IndexNo,
                1 lvl
    FROM        @Temp i
    WHERE       ISNULL(ParentID, 0)  =  0  

    UNION ALL

    SELECT        i.ID, i.Name,
                CONVERT(FLOAT, List.IndexNo + (CONVERT(FLOAT,ROW_NUMBER() OVER (ORDER BY i.Name)) * POWER(CONVERT(FLOAT,10) ,-1 * @Depth * List.lvl))) IndexNo,
                List.lvl + 1 lvl
    FROM        @Temp i
    INNER JOIN    List   ON  i.ParentID = List.ID
)

SELECT        ID, Name, REPLICATE('       ',lvl - 1) + Name
FROM        List
ORDER BY    IndexNo


Here @temp Table has Columns ID,Name, ParentID.
ID - Primary Key
Name - Name of Employee
ParentID - ID of Person which is Parent of him/her, which may be null or 0 as per your requirement.

Notes : 
ParentID  -> Null or 0 means it will be Top Most Parent  and any n > 0 value indicate that
Depth       -> Set value of @Depth according to Possibility of No of Maximum Child of any Parent
for ex if you set Depth = 3 then query works for only 999 (10^3 -1) child of any parents
means set 4 if possibility is 9999 child .....