MD5 value mismatch between SQL server and PostgreSQL

In order to write some code to do consistency check of data stored in both sql-server and PostgreSQL, I plan to calculate the MD5 on table data for both the databases, and verify if they are equal. This works fine as long as data is plain text ( ANSI ) as below:

sql-server> SELECT master.dbo.fn_varbintohexstr(HashBytes('MD5', 'a'));
0x0cc175b9c0f1b6a831c399e269772661


postgres=# select MD5('a');
0cc175b9c0f1b6a831c399e269772661

Now, If I try to use some hangul(korean) characters, MD5 match fails:

sql-server> SELECT master.dbo.fn_varbintohexstr(HashBytes('MD5', '무'));
0x0cc175b9c0f1b6a831c399e269772661


postgres=# select MD5('무');
cb3e9be1a3a28b355eabae1fa1e291b3

As per my understanding, reason of mismatch is that unicode characters are stored as UCS-2 encoding (fixed 16 bits encoding) in sql-server and UTF-8 encoding in PostgreSQL. And as MD5 works on character bits, the character bits sequence would be different in both SQL server and PostgreSQL.

AS I have been dealing mostly with hangul character-set, the workaround I used in PostgreSQL is to convert the encoding from UTF-8 to UHC ( Universal Hangul Character-set) before calculating hash as below:

postgres=# select MD5(CONVERT('무'::bytea,'UTF8','UHC'));
7827b52f65d9f7777d37071cbbbf7f2d

As you can see, the above hash value is same as that for SQL server.

All is fine as long as I am dealing with Hangul characters. But some tables contains mix of Hangul and Chinese characters, and the conversion fails in that case:

postgres=# select MD5(CONVERT('무么'::bytea,'UTF8','UHC'));
ERROR:  character 0xe4b988 of encoding "UTF8" has no equivalent in "UHC"
postgres=# 

The error makes sense as there are no equivalent of Chinese characters in UHC character-set.

How can I make it work? Basically, I need to find way to convert UCS-2 to UTF-8 in SQL server, or to convert UTF-8 to UCS-2 in PostgreSQL before calculating MD5. I want to perform all these operations within database engine, and not load data in external application to calculate MD5, as some tables has huge data set.

Querying a View with IN vs UNION performance

I have a view that the purpose is to create a regular table instead of a attribute-based representation. In doing so, it casts the varchar field into other varchars and some ints.

Today I'm running into an issue and not sure how to troubleshoot it.

If I try:

select * from vNormalTable where ItemId in (...query to get the affected item ids...)

it never completes (at least, not after 5 minutes). I've run the subquery, and it loads 9 numbers in less than a second.

If I try:

select * from vNormalTable where ItemId in (1, 2, 3, 4, 5, 6, 7, 8, 9)

It's the same thing. Nothing loads.

If I try:

select * from vNormalTable where ItemId = 1
union
select * from vNormalTable where ItemId = 2
union
....

It returns all 9 rows in less than a second.

The view itself is a bunch of left joins (well, just 10) with the item_attributes table (ie dbo.item_attributes as color where item_id = i.item_id = color.item_id and attribute_id = 10001), I don't see any special things happening other than the previously mentioned casting.

I don't know the inner workings of IN () (I always assumed it was the equivalent of = each item and union results, but that doesn't seem to be the case here). Is there anything to look for that causes it to fail only in the IN case? It was working fine before today but may have to do with a combination of bad data (doesn't seem to be the case here) and/or more records, don't know.

SQL Server 2005: System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint

Here is my table Structure:

CREATE TABLE [dbo].[Invoice](
    [InvoiceNumber] [int] IDENTITY(1,1) NOT NULL,
    [InvoiceDate] [datetime] NOT NULL,
    [DueDate] [datetime] NOT NULL,
    [SubTotal] [numeric](18, 2) NULL,
    [Tax] [numeric](18, 2) NULL CONSTRAINT [DF_Invoice_Tax]  DEFAULT (0.0),
    [InvoiceTotal] [numeric](18, 2) NOT NULL CONSTRAINT [DF_Invoice_InvoiceTotal]  DEFAULT (0.0),
    [Remark] [text] NULL,
 CONSTRAINT [PK_Invoice] PRIMARY KEY CLUSTERED 
(
    [InvoiceNumber] ASC
)
GO

CREATE TABLE [dbo].[InvoiceDetail](
    [InvoiceDetailID] [int] IDENTITY(1,1) NOT NULL,
    [InvoiceNumber] [int] NULL,
    [Amount] [numeric](18, 2) NOT NULL,
    [Tax] [numeric](18, 2) NOT NULL CONSTRAINT [DF_InvoiceDetail_Tax]  DEFAULT (0.0),
    [TransactionType] [varchar](2) NULL,
    [Remark] [text] NULL,
 CONSTRAINT [PK_InvoiceDetail] PRIMARY KEY CLUSTERED 
(
    [InvoiceDetailID] ASC
)
GO

ALTER TABLE [dbo].[InvoiceDetail]  WITH NOCHECK ADD  CONSTRAINT [FK_InvoiceDetail_Invoice] FOREIGN KEY([InvoiceNumber])
REFERENCES [dbo].[Invoice] ([InvoiceNumber])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

Here is my code snippet for inserting fresh invoice and invoice details data:

    try
    {
        using (TransactionScope transactionScope = new TransactionScope())
        {

            //... Lots of other insert / update / delete operations ...

            invoice.Add(); //Adds invoice to DB and sets the PK value in invoice.InvoiceNumber;

            foreach (InvoiceDetail invoiceDetail in invoice.InvoiceDetails.Values)
            {
                invoiceDetail.InvoiceNumber = invoice.InvoiceNumber;
                invoiceDetail.AddInvoiceDetail(); //Randomly fails.
            }

            //... Lots of other code + Payment Gateway integration
            transactionScope.Complete();
        }
    }
    catch (Exception ex)
    {
        errorType = ErrorType.General;
        if (HttpContext.Current != null)
        {
            Elmah.ErrorSignal.FromCurrentContext().Raise(ex);
        }
        else
        {
            Elmah.ErrorLog errorLog = Elmah.ErrorLog.GetDefault(null);
            errorLog.ApplicationName = "Billing Engine";
            errorLog.Log(new Elmah.Error(ex));
        }
    }

I use Enterprise Library for DB operations.

I get random exceptions at line# invoiceDetail.AddInvoiceDetail();

I have advised my staff to re-try whenever failure occurs. Re-try of the same operation without any changes passes. We have hundreds of transactions happening daily via this code. And daily I see one or two transactions failing with the below error.

ELMAH logs the following error:

System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_InvoiceDetail_Invoice". The conflict occurred in database "XXXX", table "dbo.Invoice", column 'InvoiceNumber'. The statement has been terminated.

I modified this code to serialize and store invoice + invoicedetails when an exception is caught. Strangely, I was able to see invoicenumber was correctly generated and set in the invoice object. Also, first invoice detail insertion operation succeeded (because serialized data had invoicedetailid set for one invoicedetail but zero for all others). So I assume the second invoicedetail insertion failed with the FK error.

This is totally random, 98% of times it passed and all failures pass upon re-try.

Things that I have tried:

1. I have checked there is no corrupt data in InvoiceDetails table.

2. I have executed sp_updatestats

3. Someone suggested this is a bug in SQL Server 2005 http://ift.tt/1N2pyFg http://ift.tt/1N2pANm

So we got the updates installed on SQL Server 2005. Upto SP4 (Version : 9.00.5057)

But still no good. I keep getting these random failures.

I suspect this is some sort of concurrency issue or may be some transactionscope problem. But I have no clue how to debug further.

Truncate multiple table at a time using single sql query

i am using sql server 2005 and i have 20 tables in my database. now what i want is how to truncate( not delete) selected 10 tables in my database in single sql query. i know it is possible and i do not know how to do that. please any one can help me?

Sum across columns and rows

Consider a table like this

table
+--------+---------+-----------+---------+-----------+
| BookId | ItemId1 | Quantity1 | ItemId2 | Quantity2 |
+--------+---------+-----------+---------+-----------+
|      1 |       1 |         2 |       2 |         1 |
|      1 |       3 |         1 |       2 |         1 |
|      2 |       1 |         1 |       2 |         1 |
+--------+---------+-----------+---------+-----------+

Now I want to get the sum of columns quantity for each item grouped by book. How can I take the sum across different columns then? right now I use an awkward solution like building a temporary table and then querying this one, but it must be possible in a more elegant way!?

select
    BookId 'BookId',
    ItemId1 'ItemId',
    Quantity1 'Quantity'
into #temptab
from table
union all
select
    BookId,
    ItemId2,
    Quantity2
from table

and after that

select 
    BookId,
    ItemId,
    sum(Quantity)
from #temptab
group by ItemId, BookId

How can I get rid of this intermediate step?

Desired output:

+--------+--------+----------+
| BookId | ItemId | Quantity |
+--------+--------+----------+
|      1 |      1 |        2 |
|      1 |      3 |        1 |
|      1 |      2 |        2 |
|      2 |      1 |        1 |
|      2 |      2 |        1 |
+--------+--------+----------+

How to Find the Database Views Which are not executed or Accessed for more than 6 Months in SQL Server 2005

I would like to clean up my database by identified & removing the views & stored procedures which were not in use or not accessed for a longer period (May be for last 6 months or 1 year) in SQL Server 2005.

Please help.

SQL Server Filtering by DateTime column, when TIME portion is provided sometimes

In an SSRS report, the user searches based on start date and end date.

The challenge is, as I discovered recently, he sometimes, not always, provides the time component while searching.

Currently, the filter is done like this:

if @pEndDate is null  
  SET @pEndDate = getdate()  
SET @PEndDate = DateAdd(dd,1,@PEndDate) 

SELECT ........
FROM .....
WHERE ( Createdon >= @PStartDate AND Createdon < @PEndDate)

This is fine when he searches without time (example - @PStartDate = 2/23/2015 and @PEndDate = 2/24/2015)

How should I structure the query to deal with the time portion when he provides it? (example - @PStartDate = 2/23/2015 15:00 and @PEndDate = 2/24/2015 15:00)

If this is answered elsewhere, please point me to it. Thank you.