Month: December 2009

DATETIME truncation methods speed comparison

Pavel Pawlowski SQL Server, T-SQL , ,

A lot of time we need truncate (trim) a datetime value.

E.g.. we use GETDATE() function, but we need only the Date portion of the value and not the time. So we need to set the time to the midnight 00:00:00.000.

There are several possibilities to to this and and mostly I see using the CONVERT function with the style parameter converting to varchar and than back to datetime.

CONVERT(datetime, CONVERT(varchar(10), GETDATE(), 112))

Although it is possible to use this syntax, it is not the best solution.

Much better is to use DATEADD and DATEDIFF functions.

DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

This counts a difference in days from zero and than it adds it back to zero, so we receive the value trimmed to whole days.

By simple modification we can adjust this to the first of month, last of month, whole hours, minutes etc. by simply changing the datepart.

--First of Month:
DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)

--Last of Month:
DATEADD(month, DATEDIFF(month, 0, GETDATE()) + 1, 0) - 1

--First of Year:
DATEADD(year, DATEDIFF(year, 0, GETDATE()), 0)

--Last of Year:
DATEADD(year, DATEDIFF(year, 0, GETDATE()) + 1, 0) – 1

--Hour:
DATEADD(hour, DATEDIFF(hour, 0, GETDATE()) + 1, 0) – 1

--Minute:
DATEADD(minute, DATEDIFF(minute, 0, GETDATE()) + 1, 0) – 1

Not only by this format is easier to achieve needed results but it’s even around two times faster!

We can compare the speed by simple speed test script:

DECLARE @startTime datetime
DECLARE @truncDate datetime
DECLARE @cnt int
DECLARE @msg varchar(400)

DATEADD and DATEDIFF

SELECT
    @cnt = 0,
    @startTime= GETDATE()
    
WHILE @cnt < 1000000
    SELECT
        @truncDate = CONVERT(datetime, CONVERT(varchar(10), GETDATE(), 112)),
        @cnt = @cnt + 1    

SELECT
    @msg = 'Trucation using CONVERT: resutl = ' + CONVERT(varchar(30), @truncDate) +
        ' : Took: ' + CONVERT(varchar, DATEDIFF(MILLISECOND, @startTime, GETDATE())) + ' ms'
PRINT @msg        

SELECT
    @cnt = 0,
    @startTime= GETDATE()
    
WHILE @cnt < 1000000
    SELECT
        @truncDate = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0),
        @cnt = @cnt + 1    

SELECT
    @msg = 'Trucation using DATEDIFF & DATEADD: resutl = ' + CONVERT(varchar(30), @truncDate) +
        ' : Took: ' + CONVERT(varchar, DATEDIFF(MILLISECOND, @startTime, GETDATE())) + ' ms'
PRINT @msg
Advertisement

SQL Server Reporting Services – Report schedule not deleted in SSRS 2005 and 2008

Pavel Pawlowski SQL Server, SSRS ,

If you delete a job which handles particular SSRS schedule by mistake, then it can happen, that you will be unable to delete a report schedule.

You delete the schedule and when you refresh the management the schedule you wanted to delete appears again.

This is caused by a trigger [dbo].[Schedule_DeleteAgentJob] trigger on the [dbo].[Schedule] table in the report server database, which doesn’t check for the job if it exists before it tries to delete it. And therefore if you deleted the job by mistake, then there is raised an exception in the trigger and whole transaction with deletion of schedule is rolled back.

You can simply modify the trigger by adding additional check for the existence of the job. And once the modification is applied, your SSRS schedules will work even you delete the job for that schedule manually.

ALTER TRIGGER [dbo].[Schedule_DeleteAgentJob] ON [dbo].[Schedule]
AFTER DELETE
AS
DECLARE id_cursor CURSOR
FOR
    SELECT ScheduleID from deleted
OPEN id_cursor
DECLARE @next_id uniqueidentifier
FETCH NEXT FROM id_cursor INTO @next_id
WHILE (@@FETCH_STATUS <> -1) -- -1 == FETCH statement failed or the row was beyond the result set.
BEGIN
    if (@@FETCH_STATUS <> -2) -- - 2 == Row fetched is missing.    
    BEGIN  -- Check that job exists before trying to delete it.         
        IF (EXISTS(SELECT 1 FROM msdb.dbo.sysjobs WHERE name = CONVERT(nvarchar(128), @next_id)))
            exec msdb.dbo.sp_delete_job @job_name = @next_id -- delete the schedule    
    END
    FETCH NEXT FROM id_cursor INTO @next_id
END
CLOSE id_cursor

Both SSRS 2005 and SSRS 2008 have the same problem.

I’m mentioning this also in my previous blog item about scale-out deployment of SSRS on Standard edition of SQL Server.

SQL Server 2005 Reporting Services Scale-out deployment (SSRS 2005 scale-out deployment)

Pavel Pawlowski SQL Server, SSRS , ,

Overview

SQL Server 2005 reporting services support scale-out deployment in Enterprise version. Unfortunately the scale-out deployment is not supported in Standard version of SQL Server 2005.

Some time ago I received a task to make automatic failover solution on reporting services across 3 SQL server reporting services instances on 3 different machines. There was setup a load balancing for that machines and it was necessary do create a solution when all 3 instances are fully synchronized and if some of the servers fails, user cannot be affected by this situation. (User must be able to run their reports and must receive all reports to which they subscribed. Also it is necessary when users create some reports, they must be automatically deployed across all 3 nodes).

When you run 2 or more instances of the SSRS standard version against one SSRS database which is normal in the enterprise version, you will receive a message, that the scale-out deployment is not supported.

So you can make a redundancy making 3 separate instances with 3 separate databases. Unfortunately this solution is hard to maintain in situation when you have hundreds of different reports on the server and new one are often created and some modified. Also it is hard to maintain subscriptions for the reports.

Solution

I started about solution for that situation and was investigating for some time.

Finally I’ve played with Replication which is supported by the standard version and it works. I’ve configured a fully automatic fail-over solution using the standard version of SSRS, which also correctly handles user subscriptions.

Setting up SSRS scale-out deployment on standard version of SQL Server 2005

The steps for successful setup of scale-out deployment using the standard version you need to setup the primary node first and then all the secondary nodes. The term primary node here means the one which database will be published for the other (secondary) nodes. It is also necessary that all nodes runs the same version of SQL Server Reporting services including the same service packs and patches. Also this assumes, that the report server databases will be running on the same instance of SQL Server (or Cluster).

Setup primary SSRS node

So we install and setup first node of the SSRS services. As we will be using more nodes, we have to backup encryption keys of the primary SSRS instance as it will have to be used also by all other instances.

After the primary instance is fully setup, we will create a Merge publication for the primary database. We will publish only tables and will publish all tables except the [Keys], [Events], [Notifications] tables. We publish only the report server database, not the temporary database.

We need to exclude the Keys table, because each instance has it’s own key stored in the table. Event we later restore the encryption key and it will be the same among all instances, each instance has it’s own name and creates a new entry in the Keys table. And when there is more than one entry in the table, it is automatically detected as scale-out deployment, which is supported only by the Enterprise version.

The Events and Notifications tables are used for monitoring of Subscriptions and events in SSRS. To support correct subscriptions processing we cannot replicate those tables.

The Merge replication is necessary as users are automatically directed to the nodes by load balances and we need automatically update all 3 nodes whenever there is any change on any node.

Setup secondary SSRS nodes

Once the primary node is set-up, we can setup all secondary nodes and the steps will be the same for each additional node.

So we install and setup the secondary node as normal standalone installation of SSRS. Once the node is set-up and configured, we restore encryption key of the primary SSRS instance to be able to share encrypted data among all instances.

After the encryption key is restored, we will setup replication for the database of the secondary node. Then we are ready to subscribe to the publication of the primary node database. You can run the replication agent continuously or schedule it depending how quickly you need the changes among nodes to be synchronized. I suggest also to run the subscription under Server subscription type with each node set up to other priority, so you have automatic conflict resolution when multiple users eventually changes the same report at the same time.

Metadata changes on databases

First we need to update [dbo].[Schedule_DeleteAgentJob] trigger on the [dbo].[Schedule]. This update we do on the Primary node database and will be automatically replicated to the secondary nodes. It is necessary, because there is a bug in the trigger (event in SSRS 2008). For each subscription in SSRS there is a job created. And if you delete the job first, then you are unable to delete the subscription at all as the trigger fails and the delete subscription is rolled back. The problem is, that the existence of the job is not checked in the trigger before deletion. And we need this check, because when the table is replicated then the trigger will try to delete the job again and will fail.

ALTER TRIGGER [dbo].[Schedule_DeleteAgentJob] ON [dbo].[Schedule]
AFTER DELETE
AS
DECLARE id_cursor CURSOR
FOR
    SELECT ScheduleID from deleted
OPEN id_cursor
DECLARE @next_id uniqueidentifier
FETCH NEXT FROM id_cursor INTO @next_id
WHILE (@@FETCH_STATUS <> -1) -- -1 == FETCH statement failed or the row was beyond the result set.
BEGIN
    if (@@FETCH_STATUS <> -2) -- - 2 == Row fetched is missing.    
    BEGIN  -- Check that job exists before trying to delete it.         
        IF (EXISTS(SELECT 1 FROM msdb.dbo.sysjobs WHERE name = CONVERT(nvarchar(128), @next_id)))
            exec msdb.dbo.sp_delete_job @job_name = @next_id -- delete the schedule    
    END
    FETCH NEXT FROM id_cursor INTO @next_id
END
CLOSE id_cursor

After update of the trigger we need some metadata changes on all secondary databases. We need to drop tables [dbo].[Event] and [dbo].[Notifications] and Create instead of the aliases to the primary server databases. It is necessary so there is correct processing of the subscriptions and other events. IF we do not make this change, it will often happen, that the event will be processed by more than one node, you will receive exceptions or receive subscribed reports more than once or the processing never ends. By this change it is ensured, that only on node processes the event, as when a node is taking event it puts lock on the tables and no other nodes can process the event.

-- Drop Event Table 
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Event]') AND type in (N'U')) 
    DROP TABLE [dbo].[Event] 
GO 

-- Create Alias for the Event table in the Publisher database 
CREATE SYNONYM [dbo].[Event] 
FOR [PrimaryReportServerDB].[dbo].[Event] 
GO 

-- Drop the Notifications table 
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Notifications_Subscriptions]') AND parent_object_id = OBJECT_ID(N'[dbo].[Notifications]')) 
    ALTER TABLE [dbo].[Notifications] 
        DROP CONSTRAINT [FK_Notifications_Subscriptions] 
GO 

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Notifications]') AND type in (N'U'))
    DROP TABLE [dbo].[Notifications] 
GO 

-- Create Alias for the Notifications table in the Publisher database 
CREATE SYNONYM [dbo].[Notifications] 
FOR [PrimaryReportServerDB].[dbo].[Notifications] 
GO

After this modifications you have fully working Load balancing and fail over of SSRS under SQL Server 2005 standard.

Encryption Keys and Not Supported Scale out deployment feature error message

It is essential to have the same key on all nodes to be able to read the data among nodes. Also as mentioned it is essential not to replicate the Keys table. As each node adds its key under its instance and machine name into the table and it is used for scale out deployment of Enterprise edition of SQL server. Unfortunately when standard edition finds more keys in the table it shows error messages about unsupported scale out deployment.

If this eventually on some or all nodes happen it is necessary to delete all records from the table except the record with value -1 in the Client column. Then it is necessary to restore the encryption key again to be able to access encrypted data.

Converting AS400 EBCDIC 870 into Win1250 code page on SQL Server 2005

Pavel Pawlowski CLR, SQL Server, T-SQL , , ,

I have to read data from AS400 machine using an ODBC driver under SQL Server 2005.  The data on the AS400 machine are encoded using EBCDIC 870 – Multilingual/ROECE (Latin-2) code page and contains Czech diacritics.

Unfortunately,SQL Server 2005 have no native support for EBCDIC 870 code page and when you read the data, you receive wrong characters.

There are some EBCDIC character sets supported by SQL server, but not the 870 one.

select 
    * 
from fn_helpcollations()

I was googling and binging for some time, but didn’t find any suitable solution, if you need to select live data from the AS400 machine.

Finally I was looking deeper into to the problem and found an acceptable work around.

The ODBC driver and AS400 support HEX() function, which returns sequence of hexadecimal codes representing the value.

SELECT 
    * 
FROM OPENQUERY(AS400, 'SELECT HEX(FieldName) AS FieldName FROM AS400Table')
Once we have an hexadecimal sequence, we can handle it on the SQL Server side in a way we want.

You can crate an CLR function which will allow you to convert the source CBCDIC 870 code page to the destination code page of you choice.

[SqlFunction()]
public static SqlString fnEBCDIC870ToSql(SqlString hexBytes, int destinationCodePage)
{
    if (hexBytes.IsNull) return hexBytes;
    string str = hexBytes.ToString();
    byte[] buffer = new byte[str.Length / 2];

    Encoding destinationEncoding = Encoding.GetEncoding(destinationCodePage);
    for (int i = 0; i < buffer.Length; i++)
    {
        buffer[i] = byte.Parse(str.Substring(i * 2, 2), NumberStyles.HexNumber);
    } 

    buffer = Encoding.Convert(Encoding.GetEncoding(870), destinationEncoding, buffer);
    str = destinationEncoding.GetString(buffer);
    return new SqlString(str);
}

If you cannot use the CLR function on the SQL server or Database, you can simply create an Conversion table and conversion scalar function for this conversion.

for conversion to code page 1250:

CREATE FUNCTION [dbo].[fnEBCDIC870ToWin1250] ( 
    @pHex varchar(8000) 
) 
RETURNS varchar(4000) 
AS 
BEGIN 
    DECLARE @result varchar(4000) 

    SET @result = '';

    WITH Tally AS (
        SELECT
            ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) As RowNo
        FROM sys.all_columns
    )
    SELECT
        @result = @result + ISNULL(C.Character, '?') 
    FROM Tally
    LEFT JOIN CodePage870 C ON UPPER(SUBSTRING(@pHex, (RowNo - 1) * 2 + 1, 2)) = C.HexByte
    WHERE RowNo <= LEN(@pHex) / 2
    ORDER BY RowNo

    RETURN @result
END

The function uses a conversion table CodePage870.

CREATE TABLE CodePage870 (
    HexByte char(2) NOT NULL,
    Character char(1) NOT NULL,
    CONSTRAINT PK_CodePage870 PRIMARY KEY (HexByte)
)
INSERT CodePage870 (
    HexByte,
    Character
)
SELECT '40' As HexByte, ' ' As Character UNION ALL
SELECT '41' As HexByte, ' ' As Character UNION ALL
SELECT '42' As HexByte, 'â' As Character UNION ALL
SELECT '43' As HexByte, 'ä' As Character UNION ALL
SELECT '44' As HexByte, 'ţ' As Character UNION ALL
SELECT '45' As HexByte, 'á' As Character UNION ALL
SELECT '46' As HexByte, 'ă' As Character UNION ALL
SELECT '47' As HexByte, 'č' As Character UNION ALL
SELECT '48' As HexByte, 'ç' As Character UNION ALL
SELECT '49' As HexByte, 'ć' As Character UNION ALL
SELECT '4A' As HexByte, '[' As Character UNION ALL
SELECT '4B' As HexByte, '.' As Character UNION ALL
SELECT '4C' As HexByte, '<' As Character UNION ALL
SELECT '4D' As HexByte, '(' As Character UNION ALL
SELECT '4E' As HexByte, '+' As Character UNION ALL
SELECT '4F' As HexByte, '!' As Character UNION ALL
SELECT '50' As HexByte, '&' As Character UNION ALL
SELECT '51' As HexByte, 'é' As Character UNION ALL
SELECT '52' As HexByte, 'ę' As Character UNION ALL
SELECT '53' As HexByte, 'ë' As Character UNION ALL
SELECT '54' As HexByte, 'ů' As Character UNION ALL
SELECT '55' As HexByte, 'í' As Character UNION ALL
SELECT '56' As HexByte, 'î' As Character UNION ALL
SELECT '57' As HexByte, 'ľ' As Character UNION ALL
SELECT '58' As HexByte, 'ĺ' As Character UNION ALL
SELECT '59' As HexByte, 'ß' As Character UNION ALL
SELECT '5A' As HexByte, ']' As Character UNION ALL
SELECT '5B' As HexByte, '$' As Character UNION ALL
SELECT '5C' As HexByte, '*' As Character UNION ALL
SELECT '5D' As HexByte, ')' As Character UNION ALL
SELECT '5E' As HexByte, ';' As Character UNION ALL
SELECT '5F' As HexByte, '^' As Character UNION ALL
SELECT '60' As HexByte, '-' As Character UNION ALL
SELECT '61' As HexByte, '/' As Character UNION ALL
SELECT '62' As HexByte, 'Â' As Character UNION ALL
SELECT '63' As HexByte, 'Ä' As Character UNION ALL
SELECT '64' As HexByte, '˝' As Character UNION ALL
SELECT '65' As HexByte, 'Á' As Character UNION ALL
SELECT '66' As HexByte, 'Ă' As Character UNION ALL
SELECT '67' As HexByte, 'Č' As Character UNION ALL
SELECT '68' As HexByte, 'Ç' As Character UNION ALL
SELECT '69' As HexByte, 'Ć' As Character UNION ALL
SELECT '6A' As HexByte, '|' As Character UNION ALL
SELECT '6B' As HexByte, ',' As Character UNION ALL
SELECT '6C' As HexByte, '%' As Character UNION ALL
SELECT '6D' As HexByte, '_' As Character UNION ALL
SELECT '6E' As HexByte, '>' As Character UNION ALL
SELECT '6F' As HexByte, '?' As Character UNION ALL
SELECT '70' As HexByte, 'ˇ' As Character UNION ALL
SELECT '71' As HexByte, 'É' As Character UNION ALL
SELECT '72' As HexByte, 'Ę' As Character UNION ALL
SELECT '73' As HexByte, 'Ë' As Character UNION ALL
SELECT '74' As HexByte, 'Ů' As Character UNION ALL
SELECT '75' As HexByte, 'Í' As Character UNION ALL
SELECT '76' As HexByte, 'Î' As Character UNION ALL
SELECT '77' As HexByte, 'Ľ' As Character UNION ALL
SELECT '78' As HexByte, 'Ĺ' As Character UNION ALL
SELECT '79' As HexByte, '`' As Character UNION ALL
SELECT '7A' As HexByte, ':' As Character UNION ALL
SELECT '7B' As HexByte, '#' As Character UNION ALL
SELECT '7C' As HexByte, '@' As Character UNION ALL
SELECT '7D' As HexByte, '''' As Character UNION ALL
SELECT '7E' As HexByte, '=' As Character UNION ALL
SELECT '7F' As HexByte, '"' As Character UNION ALL
SELECT '80' As HexByte, '˘' As Character UNION ALL
SELECT '81' As HexByte, 'a' As Character UNION ALL
SELECT '82' As HexByte, 'b' As Character UNION ALL
SELECT '83' As HexByte, 'c' As Character UNION ALL
SELECT '84' As HexByte, 'd' As Character UNION ALL
SELECT '85' As HexByte, 'e' As Character UNION ALL
SELECT '86' As HexByte, 'f' As Character UNION ALL
SELECT '87' As HexByte, 'g' As Character UNION ALL
SELECT '88' As HexByte, 'h' As Character UNION ALL
SELECT '89' As HexByte, 'i' As Character UNION ALL
SELECT '8A' As HexByte, 'ś' As Character UNION ALL
SELECT '8B' As HexByte, 'ň' As Character UNION ALL
SELECT '8C' As HexByte, 'đ' As Character UNION ALL
SELECT '8D' As HexByte, 'ý' As Character UNION ALL
SELECT '8E' As HexByte, 'ř' As Character UNION ALL
SELECT '8F' As HexByte, 'ş' As Character UNION ALL
SELECT '90' As HexByte, '°' As Character UNION ALL
SELECT '91' As HexByte, 'j' As Character UNION ALL
SELECT '92' As HexByte, 'k' As Character UNION ALL
SELECT '93' As HexByte, 'l' As Character UNION ALL
SELECT '94' As HexByte, 'm' As Character UNION ALL
SELECT '95' As HexByte, 'n' As Character UNION ALL
SELECT '96' As HexByte, 'o' As Character UNION ALL
SELECT '97' As HexByte, 'p' As Character UNION ALL
SELECT '98' As HexByte, 'q' As Character UNION ALL
SELECT '99' As HexByte, 'r' As Character UNION ALL
SELECT '9A' As HexByte, 'ł' As Character UNION ALL
SELECT '9B' As HexByte, 'ń' As Character UNION ALL
SELECT '9C' As HexByte, 'š' As Character UNION ALL
SELECT '9D' As HexByte, '¸' As Character UNION ALL
SELECT '9E' As HexByte, '˛' As Character UNION ALL
SELECT '9F' As HexByte, '¤' As Character UNION ALL
SELECT 'A0' As HexByte, 'ą' As Character UNION ALL
SELECT 'A1' As HexByte, '~' As Character UNION ALL
SELECT 'A2' As HexByte, 's' As Character UNION ALL
SELECT 'A3' As HexByte, 't' As Character UNION ALL
SELECT 'A4' As HexByte, 'u' As Character UNION ALL
SELECT 'A5' As HexByte, 'v' As Character UNION ALL
SELECT 'A6' As HexByte, 'w' As Character UNION ALL
SELECT 'A7' As HexByte, 'x' As Character UNION ALL
SELECT 'A8' As HexByte, 'y' As Character UNION ALL
SELECT 'A9' As HexByte, 'z' As Character UNION ALL
SELECT 'AA' As HexByte, 'Ś' As Character UNION ALL
SELECT 'AB' As HexByte, 'Ň' As Character UNION ALL
SELECT 'AC' As HexByte, 'Đ' As Character UNION ALL
SELECT 'AD' As HexByte, 'Ý' As Character UNION ALL
SELECT 'AE' As HexByte, 'Ř' As Character UNION ALL
SELECT 'AF' As HexByte, 'Ş' As Character UNION ALL
SELECT 'B0' As HexByte, '˙' As Character UNION ALL
SELECT 'B1' As HexByte, 'Ą' As Character UNION ALL
SELECT 'B2' As HexByte, 'ż' As Character UNION ALL
SELECT 'B3' As HexByte, 'Ţ' As Character UNION ALL
SELECT 'B4' As HexByte, 'Ż' As Character UNION ALL
SELECT 'B5' As HexByte, '§' As Character UNION ALL
SELECT 'B6' As HexByte, 'ž' As Character UNION ALL
SELECT 'B7' As HexByte, 'ź' As Character UNION ALL
SELECT 'B8' As HexByte, 'Ž' As Character UNION ALL
SELECT 'B9' As HexByte, 'Ź' As Character UNION ALL
SELECT 'BA' As HexByte, 'Ł' As Character UNION ALL
SELECT 'BB' As HexByte, 'Ń' As Character UNION ALL
SELECT 'BC' As HexByte, 'Š' As Character UNION ALL
SELECT 'BD' As HexByte, '¨' As Character UNION ALL
SELECT 'BE' As HexByte, '´' As Character UNION ALL
SELECT 'BF' As HexByte, '×' As Character UNION ALL
SELECT 'C0' As HexByte, '{' As Character UNION ALL
SELECT 'C1' As HexByte, 'A' As Character UNION ALL
SELECT 'C2' As HexByte, 'B' As Character UNION ALL
SELECT 'C3' As HexByte, 'C' As Character UNION ALL
SELECT 'C4' As HexByte, 'D' As Character UNION ALL
SELECT 'C5' As HexByte, 'E' As Character UNION ALL
SELECT 'C6' As HexByte, 'F' As Character UNION ALL
SELECT 'C7' As HexByte, 'G' As Character UNION ALL
SELECT 'C8' As HexByte, 'H' As Character UNION ALL
SELECT 'C9' As HexByte, 'I' As Character UNION ALL
SELECT 'CA' As HexByte, '­' As Character UNION ALL
SELECT 'CB' As HexByte, 'ô' As Character UNION ALL
SELECT 'CC' As HexByte, 'ö' As Character UNION ALL
SELECT 'CD' As HexByte, 'ŕ' As Character UNION ALL
SELECT 'CE' As HexByte, 'ó' As Character UNION ALL
SELECT 'CF' As HexByte, 'ő' As Character UNION ALL
SELECT 'D0' As HexByte, '}' As Character UNION ALL
SELECT 'D1' As HexByte, 'J' As Character UNION ALL
SELECT 'D2' As HexByte, 'K' As Character UNION ALL
SELECT 'D3' As HexByte, 'L' As Character UNION ALL
SELECT 'D4' As HexByte, 'M' As Character UNION ALL
SELECT 'D5' As HexByte, 'N' As Character UNION ALL
SELECT 'D6' As HexByte, 'O' As Character UNION ALL
SELECT 'D7' As HexByte, 'P' As Character UNION ALL
SELECT 'D8' As HexByte, 'Q' As Character UNION ALL
SELECT 'D9' As HexByte, 'R' As Character UNION ALL
SELECT 'DA' As HexByte, 'Ě' As Character UNION ALL
SELECT 'DB' As HexByte, 'ű' As Character UNION ALL
SELECT 'DC' As HexByte, 'ü' As Character UNION ALL
SELECT 'DD' As HexByte, 'ť' As Character UNION ALL
SELECT 'DE' As HexByte, 'ú' As Character UNION ALL
SELECT 'DF' As HexByte, 'ě' As Character UNION ALL
SELECT 'E0' As HexByte, '' As Character UNION ALL
SELECT 'E1' As HexByte, '÷' As Character UNION ALL
SELECT 'E2' As HexByte, 'S' As Character UNION ALL
SELECT 'E3' As HexByte, 'T' As Character UNION ALL
SELECT 'E4' As HexByte, 'U' As Character UNION ALL
SELECT 'E5' As HexByte, 'V' As Character UNION ALL
SELECT 'E6' As HexByte, 'W' As Character UNION ALL
SELECT 'E7' As HexByte, 'X' As Character UNION ALL
SELECT 'E8' As HexByte, 'Y' As Character UNION ALL
SELECT 'E9' As HexByte, 'Z' As Character UNION ALL
SELECT 'EA' As HexByte, 'ď' As Character UNION ALL
SELECT 'EB' As HexByte, 'Ô' As Character UNION ALL
SELECT 'EC' As HexByte, 'Ö' As Character UNION ALL
SELECT 'ED' As HexByte, 'Ŕ' As Character UNION ALL
SELECT 'EE' As HexByte, 'Ó' As Character UNION ALL
SELECT 'EF' As HexByte, 'Ő' As Character UNION ALL
SELECT 'F0' As HexByte, '0' As Character UNION ALL
SELECT 'F1' As HexByte, '1' As Character UNION ALL
SELECT 'F2' As HexByte, '2' As Character UNION ALL
SELECT 'F3' As HexByte, '3' As Character UNION ALL
SELECT 'F4' As HexByte, '4' As Character UNION ALL
SELECT 'F5' As HexByte, '5' As Character UNION ALL
SELECT 'F6' As HexByte, '6' As Character UNION ALL
SELECT 'F7' As HexByte, '7' As Character UNION ALL
SELECT 'F8' As HexByte, '8' As Character UNION ALL
SELECT 'F9' As HexByte, '9' As Character UNION ALL
SELECT 'FA' As HexByte, 'Ď' As Character UNION ALL
SELECT 'FB' As HexByte, 'Ű' As Character UNION ALL
SELECT 'FC' As HexByte, 'Ü' As Character UNION ALL
SELECT 'FD' As HexByte, 'Ť' As Character UNION ALL
SELECT 'FE' As HexByte, 'Ú' As Character UNION ALL
SELECT 'FF' As HexByte, '?' As Character

The Script was generated by simple C# method.

public static void CreateEBCDICScript(string destinationFileName, int destinationCodePage)
{
    StringBuilder sb = new StringBuilder();
    byte[] buffer = new byte[1];
    Encoding ebcdic = Encoding.GetEncoding(870);
    Encoding destinationEncoding = Encoding.GetEncoding(destinationCodePage);    

    for (int i = 64; i < 256; i++)
    {
        buffer[0] = (byte)i;
        buffer = Encoding.Convert(ebcdic, destinationEncoding, buffer);
        string target = destinationEncoding.GetString(buffer);
        sb.AppendFormat("SELECT '{0:X}' AS HexByte, '{1}' AS Character UNION ALL", i, target.Equals("'") ? "''" : target);
        sb.AppendLine();
    }    

    File.WriteAllText(destinationFileName, sb.ToString();
}
You can use it to generate script for whatever encoding you want.

Finally as you have the conversion functions in database you can use below selects to receive correctly encoded strings from within the AS400 machine.

--CLR Version 
SELECT 
    fnECDIC870ToSql(FieldName, 1250) As FieldName 
FROM OPENQUERY(AS400, 'SELECT HEX(FieldName) AS FieldName FROM AS400Table')

--T-SQL Version 
SELECT 
    fnECDIC870ToWin1250(FieldName) As FieldName 
FROM OPENQUERY(AS400, 'SELECT HEX(FieldName) AS FieldName FROM AS400Table')