SQL Server: Linked Server

Hi...

Seems in SQL Server 2005 there is a way to link to other Databases like Oracle,DB2 for extracting data through LinkedServer.

Once you add them as a Linked server, later you can write queries like how you use to write in SQL Server.

Syntax:
EXEC sp_addlinkedserver

server='LINKEDDB2NAME',

@srvproduct='Microsoft OLE DB Provider for DB2',

@catalog='DB2',

@provider='DB2OLEDB',

@provstr='Initial Catalog=PUBS;Data Source=DB2;HostCCSID=1252;Network Address=SOME;Network Port=50000;Package Collection=admin;Default Schema=admin;'

To check:

SELECT * FROM LinkedServer.Catalog.Schema.Table

Related Links:
To know more on Linked Sever : http://msdn.microsoft.com/en-us/library/ms190479.aspx

Defining a DB2 as a linked server in SQL Server 2005: http://blogs.msdn.com/dotnetinterop/archive/2006/01/20/defining-a-db2-as-a-linked-server.aspx

Hope it helps...
Regards
Fauzi

Sample to connnect DB2

Hi...

So far we have written applications to talk to various Databases. This is the first time, wrote a C# .Net console application to fetch records from DB2. Following is the sample code.

Code:

using System;

using System.Collections.Generic;

using System.Text;

using System.Data.Odbc;

class Program

{

static void Main()

{

OdbcCommand cmd = null;

OdbcConnection con = null;

OdbcDataReader rdr = null;

try

{

con = new OdbcConnection("DSN={LibName};UID={User Name};PWD={Password};" + "Driver={IBM DB2 ODBC DRIVER};");

cmd = new OdbcCommand();

cmd.Connection = con;

cmd.CommandText = "SELECT * FROM SOMETABLE";

cmd.comCommandTimeout = 20;

con.Open();

rdr = cmd.ExecuteReader(System.Data.CommandBehavior.SingleResult);

int intCount = 0;

while (rdr.Read())

{

intCount++;

Console.WriteLine(rdr.GetString(2).ToString() + ", " + rdr.GetString(3) + " ->" + intCount.ToString());

}

con.Close();

Console.ReadLine();

}

catch (OdbcException myException)

{

}

}

}

Regards
Fauzi

Cannot convert between unicode and non-unicode string data types

Hey,

I was creating a Integration service which needs to fetch data from a POS (Point of Sale) application and migrate to JDE ERP system. The Source is MS Access Database & the destination tables are in JD-Edwards DB2. In between there are vital manipulation logic to be done, which is taken care in MS SQL Server with the help of SSIS. When i was creating OleDB data destination for DB2 there were lot of issues. I am really thankful to this thread in MSDN http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/4128e62f-92d1-4125-baa3-fdd9fed59d48 which solved the connectivety issues. The final data flow there supposed to migrate data from SQL server to DB2. The problem i faced & invested lot of hours was on this error..

"Cannot convert between unicode and non-unicode string data types"

To solve lot of people gave lot of advices in forum http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/21646e0b-d742-467a-8880-255ebf198c46 .
Some people told Data conversion component is helpful ,In my case I am transferring data into Z-files. These are tables F4001Z & F4011Z which have 149 columns & 249 columns each. And doing mapping for each fields is a boring task...

Solution: From the error it is clear that the issue is converting unicode to non-unicode string. So What i did was, I created a Local table in SQL Server with same table structure of F4001Z & F4011Z tables (like F4001Z_local & F4011Z_local). And in Table creation SQL script, where ever there was nchar datatype, I replaced with varchar . Now in the source Data Flow section, the manipulated data will we inserted into these local tables F4001Z_local & F4011Z_local and then a Select query is run through a stored procedure. Doing so in the string is non-unicode string or DT_STR format we can say... The Error got resolved :) The screen shots says the rest, The data conversion component was placed before. I believe if we remove the component also the application would work fine....

Best Regards
Fauzi

SSIS: OleDB Data source: cannot retrieve the column code page info from the OLE DB provider

Hi,

When i tried to set a DB2 data source in a SSIS package, the following warning was shown when the clicked preview button.

Warning at {Local Component ID} [OLE DB Source [1]]:
Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.

Solution:
To resolve, Under properties -> Custom Properties -> set AlwaysUseDefaultCodePage : True

Resource: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/bb08af3a-5080-40d0-b00b-e278115cb2ad

Regards
Fauzi

SQL: Query to find duplicate rows

Hi,

If you would like to list duplicate rows with the count for a table, the following query would be helpful.

Query: Duplicate Rows

SELECT Column1,Column2,Column3,Count(*)
FROM dbo.TableName
GROUP BY Column1,Column2,Column3
HAVING ( COUNT(*) > 1 )

And there are scenarios where we need to find the combination of Columns in a table occurred only once. In such case the following query could be used.

Query: Unique combination of columns

SELECT Column1,Column2,Column3,Count(*)
FROM dbo.TableName
GROUP BY Column1,Column2,Column3
HAVING ( COUNT(*) = 1 )

Hope it helps.

Regards
Fauzi

SSIS: cannot be processed because more than one code page (1256 and 1252) are specified for it

Hi,

In My SSIS, I got the following error when I changed the Data type through Data Conversion component.

Error:
Data Flow Task: The column "" cannot be processed because more than one code page (1256 and 1252) are specified for it. (SQL Server Import and Export Wizard)

When we searched the forums, most common answer found was to set AlwaysUseDefaultCodePage=true in Properties.

When I did the same it worked for me

Regards

Fauzi

SQL: Date Format

The Following syntax are helpful to get desired date formats in SQL.

Syntax for CAST:
CAST ( expression AS data_type [ (length ) ])

Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Example: DD/MM/YYYY
select convert(varchar(16), getdate(), 103)

MM/DD/YYYY
select convert(varchar(16), getdate(), 101)

Without century (yy) (1) With century (yyyy) Standard Input/Output (3)
- 0 or 100 (1, 2) Default mon dd yyyy hh:miAM (or PM)

1 101 U.S. mm/dd/yyyy
2 102 ANSI yy.mm.dd
3 103 British/French dd/mm/yy
4 104 German dd.mm.yy
5 105 Italian dd-mm-yy
6 106 (1) - dd mon yy
7 107 (1) - Mon dd, yy
8 108 - hh:mi:ss
- 9 or 109 (1, 2) Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM)
10 110 USA mm-dd-yy
11 111 JAPAN yy/mm/dd
12 112 ISO yymmdd
- 13 or 113 (1, 2) Europe default + milliseconds dd mon yyyy hh:mi:ss:mmm(24h)
14 114 - hh:mi:ss:mmm(24h)
- 20 or 120 (2) ODBC canonical yyyy-mm-dd hh:mi:ss(24h)
- 21 or 121 (2) ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi:ss.mmm(24h)
- 126 (4) ISO8601 yyyy-mm-ddThh:mi:ss.mmm (no spaces)
127(6, 7) ISO8601 with time zone Z. yyyy-mm-ddThh:mi:ss.mmmZ
(no spaces)
- 130 (1, 2) Hijri (5) dd mon yyyy hh:mi:ss:mmmAM
- 131 (2) Hijri (5) dd/mm/yy hh:mi:ss:mmmAM

More Examples:

SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY]
SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]
SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY]
SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]
SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY]
SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY]
SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY]
SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY]
SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY]
SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY]
SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY]

SQL: Georgian date to Julian date

Hi...

Here is the function to convert Georgian date to Julian date. Today it was useful when I was writing a application to transfer data to ERP system.

Syntax:

CREATE FUNCTION dbo.to_julian(@date datetime) RETURNS char(6) AS

BEGIN

RETURN (SELECT '1'+ RIGHT(CAST(YEAR(@date) AS CHAR(4)),2) + RIGHT('000' + CAST(DATEPART(dy, @date) AS varchar(3)),3))

END

Execute:

SELECT dbo.to_julian(getdate())

The output is 109217 for today(2009-08-05) Where 1-> 21st century, 09 -> Current Year & 217 -> Is the number of days since the start of this year.

Regards

Fauzi

C# Check whether string is valid Date

Hi...

There are many places where we need to validate whether the given string is a valid Date or not. The following simple method handy for the same...

private bool IsDate(string strDate)

{

DateTime dt;

bool boolIsDate = true;

try

{

dt = DateTime.Parse(strDate);

}

catch

{

boolIsDate = false;

}

return boolIsDate;

}

Hope it Helpz...

4Z ~ Fauzi