none
How to migrate extended store procedure 64x DLL from SQL 2005 to SQL 2008 R2 ?

    Question

  • Hi! all,

    I need to migrate a database with extended store procedures from SQL 2005 64 bit EE to SQL Server 2008 R2 EE, the targuet os is Windows 2008 R2 EE.

    I went trought the http://msdn.microsoft.com/en-us/library/ms164653(v=sql.90).aspx when I call a extended store procedure inside the dll é get :

    Msg 17750, Level 16, State 0, Procedure xp_fmove, Line 1
    Could not load the DLL R:\X_SP\xp_sgt_64.dll, or one of the DLLs it references. Reason: 14001(failed to retrieve text for this error. Reason: 15105).

    Am i doing any thing wrong ?

    Please assist me to fix the problem.

    Best regards.

    Wednesday, July 24, 2013 10:33 AM

Answers

  • MSVCRD80D is a version of the C runtime library.  You can install this.

    OPENDS60 is part of SQL Server; it will already be there in the SQL binn directory.

    The rest of the DLLs are Windows DLLs.  You cannot deploy them.  They are either installed as part of Windows or a Windows Feature, and cannot be individually deployed.

    These are very strange dependencies for a SQL extended stored procedure.

    Here's a little powershell script you can use test if you can load a dll.

    $src = @"
    using System.Runtime.InteropServices;
    using System;
    
    public class Utils
    {
      [DllImport("kernel32", SetLastError=true, CharSet = CharSet.Unicode)]
      static extern IntPtr LoadLibrary(string lpFileName);
    
    
      public static string TryLoadLibrary(string dllname)
      {
          var p = LoadLibrary(dllname);
          if (p == IntPtr.Zero)
          {
             return "Load Failed, Error:" + Marshal.GetLastWin32Error();
          }
          else
          {
            return "Loaded Sucessfully";
          }
    
      }
    
    }
    "@
    add-type -TypeDefinition $src
    
    $rv = [Utils]::TryLoadLibrary("wlanapi");
    write-host $rv

    David


    David http://blogs.msdn.com/b/dbrowne/

    Tuesday, July 30, 2013 10:11 PM

All replies

  • Have you tried to recompile the stored procedure?

    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Wednesday, July 24, 2013 1:16 PM
  • Why recompile ?
    Wednesday, July 24, 2013 1:35 PM
  • I don't know why this DLL can't be loaded, but you should check the normal suspects.  IE that you have all the dependencies of that DLL available in the PATH, or perhaps that you can successfully call LoadLibrary() on the DLL from a stand-alone program.

    I don't know what your extended stored procedure does, but you should _strongly_ consider replacing it with something safer for SQL Server and easier to maintain.  Perhaps a CLR stored procedure.

    David


    David http://blogs.msdn.com/b/dbrowne/


    Wednesday, July 24, 2013 2:11 PM
  • failed to retrieve text for this error. Reason: 15105 is kind of access issue.so need to check service account for SQL Server does have the sufficient permissions?

    After granting permission if you still facing the same let us know.

    • Edited by Pradeep_DBA Wednesday, July 24, 2013 5:13 PM help
    Wednesday, July 24, 2013 5:12 PM
  • The service account for sql server is a domain account that is local administrator; has full control in R:\X_SP\xp_sgt_64.dll and subfolders.

    Drive R: is beeing used for datafiles.

    I forgot an important detail, this sql is a cluster with 5 instances e 3 nodes.

    Wednesday, July 24, 2013 7:54 PM
  • Does xp_sgt_64.dll have any dependencies?  Perhaps an old version of the C runtime library that isn't installed?

    What does the XP do that justifies continuing to use it?

    David


    David http://blogs.msdn.com/b/dbrowne/

    Wednesday, July 24, 2013 8:14 PM
  • Hello,

    Is the used VC++ runtime (e.g. VC++ 2010) installed on the server?

    Please note: Extended stored procedures are a deprecated feature and will be removed in the next SQL Server version 2014, you should use CLR integration instead.

    See Deprecated Database Engine Features in SQL Server 2008 R2 => Extended stored procedures


    Olaf Helper

    Blog Xing

    Thursday, July 25, 2013 5:24 AM
    Moderator
  • Not sure that I have de correct version of VC++ I have :

    Microsoft Visual c++ 2008 redistributable - x86 9.0.30729.4148

    Microsoft Visual c++ 2008 redistributable - x64 9.0.30729.4148

    Microsoft Visual c++ 2008 redistributable - x64 9.0.21022

    Microsoft Visual c++ 2005 redistributable (x64)  (8.0.61000)

    Microsoft Visual c++ 2005 redistributable (8.0.61001)

    Thursday, July 25, 2013 9:47 AM
  • If you are not sure, ask the developer of the XP ... or install all, it won't harm your system.

    Olaf Helper

    Blog Xing

    Thursday, July 25, 2013 9:59 AM
    Moderator
  • I tried to copy dll to drive c:

    c:\xp_sgt_64.dll

    c:\sgt_64\xp_sgt_64.dll

    c:\sgt_64\xp_sgt.dll

    c:\sgt_64\xp_sgt_64.dll

    C:\Program Files\Microsoft SQL Server\MSSQL10_50.<instance>\MSSQL\Binn\xp_sgt_64.dll

    with no luck.

    DLL version I think Redistributable is c++ 2005,

    Do I need to recompile to c++ v2012 and install the Visual C++ Redistributable for Visual Studio 2012 Update 3 ?at http://www.microsoft.com/en-us/download/details.aspx?id=30679 ?

    Thursday, July 25, 2013 2:07 PM
  • >Do I need to recompile to c++ v2012 and install the Visual C++ Redistributable for Visual Studio 2012 Update >3 ?

    You need to have all the dll's dependencies available in the path.  Looking at the source code you should be able to figure out what those dependencies are.

    Rebuilding with VS 2012 will require you to figure this out, so is probably sufficient to get it working.

    Although, again, you shouldn't use extended stored procedures.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, July 25, 2013 2:11 PM
  • Hi! All

    I tryed to install the dll in another server and I got an error slight diffrent :

    Msg 17750, Level 16, State 0, Procedure xp_fmove, Line 1
    Could not load the DLL c:\sgt\xp_sgt_64.dll, or one of the DLLs it references. Reason: 14001(failed to retrieve text for this error. Reason: 15100).

    Help !

    Saturday, July 27, 2013 11:42 AM
  • I have installed the tool Dependancy Walker http://www.dependencywalker.com/

    This tool shows the dependencies.

    Acording to this tool are missing the following dlls :

    MSVCR80D
    OPENDS60
    WLANAPI

    MF
    MFPLAT
    MFREADWRITE
    IEFRAME
    IESHIMS

    Is it safe to copy/register the dlls to the path at targuet slq server ?

    In principle MF* and IE* are not required correct ?

    In priciple only the first 3 are requried correct ?

    Tuesday, July 30, 2013 4:32 PM
  • MSVCRD80D is a version of the C runtime library.  You can install this.

    OPENDS60 is part of SQL Server; it will already be there in the SQL binn directory.

    The rest of the DLLs are Windows DLLs.  You cannot deploy them.  They are either installed as part of Windows or a Windows Feature, and cannot be individually deployed.

    These are very strange dependencies for a SQL extended stored procedure.

    Here's a little powershell script you can use test if you can load a dll.

    $src = @"
    using System.Runtime.InteropServices;
    using System;
    
    public class Utils
    {
      [DllImport("kernel32", SetLastError=true, CharSet = CharSet.Unicode)]
      static extern IntPtr LoadLibrary(string lpFileName);
    
    
      public static string TryLoadLibrary(string dllname)
      {
          var p = LoadLibrary(dllname);
          if (p == IntPtr.Zero)
          {
             return "Load Failed, Error:" + Marshal.GetLastWin32Error();
          }
          else
          {
            return "Loaded Sucessfully";
          }
    
      }
    
    }
    "@
    add-type -TypeDefinition $src
    
    $rv = [Utils]::TryLoadLibrary("wlanapi");
    write-host $rv

    David


    David http://blogs.msdn.com/b/dbrowne/

    Tuesday, July 30, 2013 10:11 PM