May 132010
 

I was pushing data from a SQL table to an Informix table using an openquery statement in a trigger and would get the following error.

 

Msg 7343, Level 16, State 2, Procedure MyProcedure, Line 166

The OLE DB provider “Ifxoledbc” for linked server “INFORMIX” could not INSERT INTO table “[Ifxoledbc]”.

 

The following is an example of the T-SQL I was trying to insert. Its been generalized.

insert into openquery(INFORMIX,'select column1,column2,column3,column4,column5,column6,column7,column8,column9,column10,column11,column12,column13,column14,column15,column16,column17,column18,column19,column20,column21,column32, column33 from mytable')
Values ('somedata'
           ,'somedata'
           ,'anumber'
           ,'anumber'
           ,NULL
           ,'2010-03-16 00:00:00.000'
           ,'1'
           ,NULL
           ,NULL
           ,NULL
           ,'2010-03-17 19:00:00.000'
           ,'aname'
           ,'BÃ…KER'
           ,'an address'
           ,NULL
           ,'acity'
           ,'astate'
           ,'azipcode'
           ,NULL
           ,NULL
           ,NULL
           ,NULL
           ,'astatuscode')

I narrowed it down to value:’BÃ…KER’. I did a little more testing and Informix won’t accept the following characters.

 

BadCharacters

I grabbed this table from the following link.

http://www.addressmunger.com/hex_ascii_tables/

 

Does anyone know why Informix won’t accept these characters? I don’t have access to the informix side of things, and I have only an basic knowledge of Informix so input on this problem would be nice.

Mar 192010
 

As everyone already knows, to extract the time from a datetime (pre SQL 2008), you have to use the convert function

HOWEVER: beware of using the smalldatetime type, as it trucates the seconds from the value convert returns

Run the following code:

declare @thissmalldatetime smalldatetime,@thisdatetime datetime
select @thissmalldatetime=getdate(),@thisdatetime=getdate()
select convert(nvarchar(8),@thissmalldatetime,14) as [Small Date Time],convert(nvarchar(8),@thisdatetime,14) as [Date Time]
waitfor delay '00:00:01'
select @thissmalldatetime=getdate(),@thisdatetime=getdate()
select convert(nvarchar(8),@thissmalldatetime,14) as [Small Date Time],convert(nvarchar(8),@thisdatetime,14) as [Date Time]

Small Date Time Date Time
--------------- ---------
13:13:00        13:12:40

(1 row(s) affected)

Small Date Time Date Time
--------------- ---------
13:13:00        13:12:41

(1 row(s) affected)

Feb 232010
 

I have had to look this query up a few times, so I thought I would write about it here:

USE [DBInstanceName]
GO
SELECT  st.row_count as [Row Count]
FROM sys.dm_db_partition_stats st
WHERE index_id < 2
and OBJECT_NAME(OBJECT_ID)='TableName'

Aug 202009
 

Here is a way to execute a sql statement that returns one column of one row. If there is more than one row returned, it will return nothing.

        public static string CreateReader(string connectionString, string queryString)
        {
            string result="";
            int rowCount = 0;
            using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(connectionString))
            {
                System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand(queryString, connection);
                connection.Open();
                System.Data.OleDb.OleDbDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    rowCount = rowCount + 1;
                    result = reader[0].ToString();
                }
                reader.Close();
                return (rowCount == 0 || rowCount > 1) ? "" : result;
            }
        }

May 112009
 

I remember searching for this T-SQL statement a few months ago, so time to put it on the blog.

--GET FIRST DAY OF LAST MONTH 
SELECT DATEADD(MM, DATEDIFF(MM, '01/01/2000', DATEADD(MM, -1,GETDATE())), '01/01/2000') as [Date of First Day of Last Month]
--GET LAST DAY OF LAST MONTH 
SELECT DATEADD(SS,-1,DATEADD(MM, DATEDIFF(MM,'01/01/2000',GETDATE()),'01/01/2000')) as [Date of Last Day of Last Month]

Jul 232008
 

I was given the task of connecting BizTalk to an Informix database. When the project was first kicked off, the client started exploring their options, one of the companies that they looked at was Attunity.

I told them that the Linked Server option was a much better approach, while offering significant cost savings!

So I started my research on how to get connected, and I came across a few pages of interest:

The first is IBM documentation page about how to set up the actual connection within Enterprise Manager.

When I first started connecting, I was getting this error:

The OLE DB provider Ifxoledbc for linked server INFORMIX_TEST reported an error. Authentication failed. Cannot initialize the data source object of OLE DB provider Ifxoledbc for linked server INFORMIX_TEST. OLE DB provider Ifxoledbc for linked server INFORMIX_TEST returned message EIX000: (-951) Incorrect password or user Administrator@::ffff         is not known on the database server.. (Microsoft SQL Server, Error: 7399)

After doing some more research I found additional articles that were of interest:

One that I thought might be a problem was the permissions for the temp folder as documented on KB814398 to deal with Error: 7399. I set the permissions as documented with the same results.

Another IBM’s documentation page about how to set up the linked server and the fact that you need to install the coledbp.sql on the Informix database so that the OLE DB can actually connect.

Here is what I had setup (of course the actual connectivity is different)

informixsetup1

I had read an article about mapping between the local instance of SQL Server and the security account on a remote server, but did not try it.

I finally clicked on the security button and filled in the username and password

informixsetup2

It worked!

You also have to go into the Provider list and in the properties of the Ifxoledbc click on the Allow inprocess

informixsetup3

What I don’t quite understand is the relationship between the Provider string and the security context they are the same, but when would they be different?

Jun 302008
 

This is now the second time that it has happened, and I didn’t write about it before.

I am setting BizTalk up in a multi server environment where the SQL Server exists on a different machine.

I got the following error after applying the configuration: System.EnterpriseServices.TransactionProxyException

After a couple of hours of going thru database permissions, checking MSTDC settings in component services. I found that on the SQL Server MS DTC was not installed (Add/Remove windows components -> Application Server)

DTC

Jun 192008
 

I don’t know how many times I’ve had to execute a stored procedure or a SQL statement from within a BizTalk map or within a C# class, so I’m putting this entry as a reminder to myself of how to do it.

// 1. Create your SQL Connection SqlConnection conn = null; // 2. Create and open a connection object conn = new SqlConnection("Connection String Goes Here"); // 3. Open the Connection conn.Open(); // 4. Create the SQL Command and assign it it a string string strSQLCommand = "SELECT * FROM TABLE"; // 5. Execute the SQL Command SqlCommand command = new SqlCommand(strSQLCommand, conn); // 6. Use ExecuteScalar() to return the first result string returnvalue = (string)command.ExecuteScalar(); // 7. Close the Connection conn.Close(); // 8. Return the Value return returnvalue;

Jun 262007
 

The adapter “SQL” raised an error message. Details “HRESULT=”0x80040e07″ Description=”Error converting data type nvarchar to numeric.”
 ?<Root xmlns:ns00=”urn:schemas-microsoft-com:xml-updategram”><?MSSQLError HResult=”0x80040e07″ Source=”Microsoft OLE DB Provider for SQL Server” Description=”Error converting data type nvarchar to numeric.”?></Root>”.

I was trying to insert nothing into a field defined as a decimal.

image 

image

I ended up changing it to varchar(21) and it worked fine.

Apr 132007
 

UPDATE!!!

Do not use this method if you are planning on doing any bulk loading. There is a 10,000 variable limitation that is reported through the BizTalk Event Log entries but is actually a SQL Server design limitation.

image

and

image

I recently was working for a company who needed to store data coming in from the 837 claim into a database.

Experiencing many poorly designed databases to store claim data before, I create a database structure that will capture what the client was looking for, but also allow easy implementation of additional values to be captured into the database as use of this process increased. Now my goal/issue was to get BizTalk to take the 837 data and insert all of the data into the various tables.

For the purposes of this blog, I have simplified the example, but the concepts are the same.

The first thing I have done is created a psuedo837Claim schema, that looks somewhat like the 837 schema looks like. In the picture, I have marked what is repeatable.

The next thing I have done is created the database structure:

So now I need to start generating the schemas that represented the web I have just created. We will start by following Steve Kaufman’s guidance here and creating an at-identity schema that we will use throughout the process. Once I have completed that, I generate the Header schema from the Header table. I select all of the columns (as I will for all but the end, but we will get to that later).

Then for the purposes of this blog, I will import the Application table. Here comes the modifications. Once I have imported the Application table, I need to import the at-identity schema, and I also need to make the Application record a Complex Type for later use. Because the prefix for the namespace is tns, the Data Structure Type needs to be tns:Application

I then add the primary key field and set the Data Type to the at-identity

After saving it, I then go back to my Header schema and import both the at-identity and the application schema

I then add a new record as a sibling to Header, and I name the new record Application (the name of the table), I choose as the Data Structure Type ns0:Application (ComplexType) and set the Max Occurs as blank, as we will not be repeating until later

I complete the process for all of the other tables, except for the tables that do not have children, and there I don’t need to select the primary key field (as I will let SQLXML do that without me coding for that)

So I generate the schemas for everything and now we are getting to childless table importing, ProviderInfo for example, I don’t need to add the primary key, I simply need to include the foreign key when I generate the schema

Remember that all I have to do is define the Data Structure Type as tns:ProviderInfo for the table name

When I import the InsertProviderInfoService into the InsertHeaderService schema I choose ns2:ProviderInfo and again, since it is not repeating, you can simply leave the Max and Min Occurs blank, for the repeating, you will change the max occurs to Unbounded

You continue this same pattern, adding primary keys if there are children to the table, omitting them if not.

The final output should look like this:

Delete all of the Orchestrations except the first one that has defined the Header and rename it to InsertClaimProcess and change the Typename to InsertClaimService also

Now for the mapping (finally). Create the map with the EDI input, and the header schema output. Since the Header, Application, and provider are all single instances, I hard code a, b, and c respectively into the updg:at-identity primary key and the foreign keys. For the ProviderInfo, ProviderLocation, and Subscriber, I link them all to ‘c’

Since Subscriber is the first repeatable child table, I add an index from the source to the d primary key and then distribute the concatenated foreign key thru the child tables

Finishing out the relationships look like this:

 

Now all you have to do is finish the mapping and I am complete, invoke the map in the orchestration and it is complete.

 

Hope that this
was as fun to do as it was to discover!