Connection string in database lookup functoid…

• January 10, 2007 at 7:51 AM #17193
  community-content

  Yes, makes perfect sense, especially now that I have a much better idea about the udl files than what I have read.

  Unfortunately though, no, still not working.

  I did figure out how to hook up an error return functoid and now can see the error, however slightly ambiguous to me…

  Format of the initialization string does not conform to specification starting at index 0

  after doing some googling on this, it looks like it relates to the connection string. it also looked like it could be a Microsoft bug based on some of the forums I read but not convinced.

  In any case, I created the .udl file like you said. and upon testing connection it succeeded. I am slightly lost when you said to use the SQL native client if I had it installed… This is something automatically installed on the SQL 2005 server, so is this also something that maybe needs to be installed onto the same server as Biztalk because currently these are both residing on seperate servers.

  does any of this sound familiar or do you have any tips on how I can continue to narrow this issue down?

  Thanks again…

  • January 10, 2007 at 8:15 AM #17196
    community-content

    I have to assume that the SQL Client was installed on the BizTalk server since it requires access to SQL Server.  Are you using SQL Native client?

    My Database Lookup functoid looks like this:

    • xpath statement created when I dragged a line from the input field.
    • String Left Trim (or String Concatenate) that holds the "File Name=C:\…\…uld" statement
    • USER_TYPE (Table name)
    • USER_TYPE_ID (Column name)

    The Value Extractor then looks like this:

    • Database Lookup
    • USER_TYPE_ID

    Of course this just confirms that the ID value I have is in the table, a very simple example.  One thing I wanted to be clear on is the fact that all these funcoid links are in the correct order.  If you verify this, then it might me a good idea to grab any standard SQL patches from Microsoft Update.  I'd make sure any SQL and SQLXML updates are present.

    • January 10, 2007 at 10:06 AM #17199
      community-content

      Boy, I hate to sound dumb here but this is what I have done…  I setup separate servers for SQL 2005 and Biztalk.  I was told that it was not a good idea to install both on the same server.  (Since then, I have seen where this would not be a problem) Anyway, All I have on my Biztalk server that is SQL related is the configuration manager.  I also just installed the distributable client found in the setup disks just to make sure I had that covered.

       You should also know that I have been interfacing with the SQL database because I have working projects so all that should be straight…  its just for some reason, I am getting this one error during the lookup…  I guess what I need to know is how do I check if this native client is active?  I have the udl file setup to use this…  as for the functoid configuration, I believe I have it setup correctly.  obviously, I am not so saavy on SQL, I just installed it on a development server just to fill the biztalk installation requirement.  nothing fancy

      This is exactly what I have in my DB lookup functoid

       01 (hardcoded key value just to get the test working)

      c:\DBConnection.udl (literal location of file)

      dbo.TESTDBF (database table name)

      ShipToNumber (table key column for lookup)

       The extractor functoid has the lookup input and just the output column

      This is the exact contents of my .uld file…

      [oledb]
      ; Everything after this line is an OLE DB initstring
      Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;User ID=kb17324dx;Initial Catalog=TESTDB;Data Source=HPSQL05T

       Does this give you any better idea what I have and what I dont have?

       

      • January 12, 2007 at 6:31 AM #17221
        community-content

        I am assuming that I stumped you…  I still havent been able to locate a solution for this. all of my patches are up to date and I am still getting this error…  any more ideas?

        • January 12, 2007 at 7:18 AM #17222
          community-content

          To be honest, I haven't found the time to test this since my last post, but I wanted to suggest that if you are not already doing this, test the map by setting up the properties and right clicking on Validate Map, and then Test Map if there are no errors.  Are you getting errors in this phase, or are your problems strictly when you run the app?

          I like this feature because you get to make incremental changes and test several times a minute if necessary.  Obviously there is a lot to setup when it comes to testing a DB connection, beyond the UDL, but in this situation we should be avoiding problems such as the wrong version of BizTalk for the OS, and bad configuration.  We're merely checking the connection to SQL and that the Transform works, as simple as it may be.

          I'm not really sure what additional questions to ask beyond, what else works or doesn't work so we know where to go.  This should obviously be a simple problem to solve but I think we're missing a few key pieces of information.  If you're new to BizTalk, then maybe it is something as simple as installation and configuration, but it’s hard to tell without knowing your system was installed using prescriptive guidance from Microsoft.

November 18, 2009 at 6:00 AM #23794

community-content

Hi,

  Did this issue got resolved. I am also facing same issue. Can you please let me know how you resolved, if you have already done.

Can reach me at: pavana.meghana@gmail.com

 

Thanks,

Pavana

• March 9, 2016 at 2:15 AM #26560
  community-content

  Connection string:    Provider=SQLOLEDB;Persist Security Info=False;Integrated Security=SSPI;Data Source=RENSYS019;Initial Catalog=BizTalkLookUpDB