We help IT Professionals succeed at work.


Last Modified: 2017-04-12
Hello All,

I recently did the migration of MS Access to SQL Server 2014 Express edition and I have only moved Tables to SQL Servers and few straight forward queries are done as pass through Queries but i have one Query that's taken a week for me to resolve with no results and that query calls the VBA function and it takes lot of time. Can someone please suggest how can i convert this function to SQL Server UDF
Public Function Comp(sql As String) As String
Dim db As Database, rs As Recordset, i As Long, Res As String, Tmp As String
Dim Stelle As Variant, drin As Variant
Dim Part_SQL1 As String, SQL1 As String, SQL2 As String

  On Error Resume Next
  Set db = CurrentDb()
  Set rs = db.OpenRecordset(sql, dbOpenSnapshot)
  If Err <> 0 Then
    Res = "#Fehler"
    On Error GoTo 0
    Res = ""
    Do While Not rs.EOF
       SQL1 = rs(0)
       SQL2 = rs(1)
       'If SQL1 Or SQL2 = "" Then
       '   GoTo Weiter
       'End If
          Stelle = InStr(1, SQL1, ",")
          If Stelle = 0 Then
             If Left(SQL1, 1) = " " Then
                Part_SQL1 = Mid(SQL1, 2)
                Part_SQL1 = SQL1
             End If
             Part_SQL1 = Left(SQL1, Stelle - 1)
          End If
          drin = InStr(1, SQL2, Part_SQL1)
          If Left(Part_SQL1, 1) = " " Then
             Part_SQL1 = Mid(Part_SQL1, 2)
          End If
          If drin <> 0 Then
             Res = Res & ", " & Part_SQL1
          End If
          SQL1 = Mid(SQL1, Stelle + 1)
       Loop Until Stelle = 0
       Res = Mid(Res, 3)
  End If
   Comp = Res
End Function

Open in new window

Watch Question

Distinguished Expert 2017

Can you tell us in words what this function is doing?  It looks like it is reading through a recordet and concatenating stuff.

Since you don't know how to write this as a SQL Server function (and neither do I), it might be best to just start a Gig and pay someone to do it for you.
HainKurtSr. System Analyst

what is doing?

give us a sample...


To be honest even i don't know what this function does..... :( I had hired a consultant from freelancer.com but they couldn't complete it, neither they were able to optimize the query nor this function .... Let me try what best i could do.... If anyone could help to suggest improvements in the query i would be glad

SELECT DISTINCT Datensätze.Datensatz, Datensätze.[A-Sachnummer], Datensätze.ZGS, Datensätze.Konfiguration, Datensätze.Zertifizierungsnummer, Datensätze.Applikationsfamilie, Applikationsvarianten.*, Motorvarianten.*, Baumuster.Baumuster, Baumuster.Code, Baumuster.Einbau, Datensätze.Ölwannen, [qry Ölwanne Beschreibung].Ölwanne1, [qry Ölwanne Beschreibung].Ölwanne2, [qry Ölwanne Beschreibung].Ölwanne3, [qry Ölwanne Beschreibung].Ölwanne4, [qry Ölwanne Beschreibung].Ölwanne5, [qry Ölwanne Beschreibung].Ölwanne6, [qry Ölwanne Beschreibung].Ölwanne7, [qry Ölwanne Beschreibung].Ölwanne8, Datensätze.Lüfterkupplungen, [qry Lüfterkupplungen-Beschreibung].[Lüfterkupplung 0], [qry Lüfterkupplungen-Beschreibung].[Lüfterkupplung 1], [qry Lüfterkupplungen-Beschreibung].[Lüfterkupplung 2], [qry Lüfterkupplungen-Beschreibung].[Lüfterkupplung 3], [qry Lüfterkupplungen-Beschreibung].[Lüfterkupplung 4], [qry Lüfterkupplungen-Beschreibung].[Lüfterkupplung 5], [qry Lüfterkupplungen-Beschreibung].[Lüfterkupplung 6], [qry Lüfterkupplungen-Beschreibung].[Lüfterkupplung 7], [qry Lüfterkupplungen-Beschreibung].[Lüfterkupplung 8], [qry Lüfterkupplungen-Beschreibung].[Lüfterkupplung 9], Datensätze.[Status (A, B, C, F, V, P)], Datensätze.[ATL-Nummer], Datensätze.[Düsen-Art], Datensätze.Kolbennummer, Datensätze.[Nockenwellen-Nummer], Datensätze.Emissionstechnologie, Datensätze.[SPS-Nummer], Datensätze.[Zylinderkopf-Nummer], Datensätze.Überströmventil, IIf(IsNull([Datensätze].[ATL-Nummer]),[Motorvarianten].[ATL-Nummer],[Datensätze].[ATL-Nummer]) AS ATL_korr, IIf(IsNull([Datensätze].[Düsen-Art]),[Motorvarianten].[Düsen-Art],[Datensätze].[Düsen-Art]) AS Düsen_korr, IIf(IsNull([Datensätze].[Kolbennummer]),[Motorvarianten].[Kolbennummer],[Datensätze].[Kolbennummer]) AS Kolben_korr, IIf(IsNull([Datensätze].[Nockenwellen-Nummer]),[Motorvarianten].[Nockenwellen-Nummer],[Datensätze].[Nockenwellen-Nummer]) AS NoWe_korr, IIf(IsNull([Datensätze].[Emissionstechnologie]),[Motorvarianten].[Emissionstechnologie],[Datensätze].[Emissionstechnologie]) AS EmTech_korr, IIf(IsNull([Datensätze].[SPS-Nummer]),[Motorvarianten].[SPS-Nummer],[Datensätze].[SPS-Nummer]) AS SPS_korr, IIf(IsNull([Datensätze].[Überströmventil]),[Motorvarianten].[Überströmventil],[Datensätze].[Überströmventil]) AS Ü_ventil_korr, IIf(IsNull([Datensätze].[Zylinderkopf-Nummer]),[Motorvarianten].[Zylinderkopf-Nummer],[Datensätze].[Zylinderkopf-Nummer]) AS ZylKoNr_korr, Datensätze.Bemerkungen AS Ausdr, IIf([Datensätze].[Status (A, B, C, F, V, P)]="V" Or "P",[Datensätze].[Bemerkungen],"") AS Bemerk_korr, ATL.Bezeichnung, Düsen.HD, Düsen.[SK-Winkel], Düsen.Lochzahl, Kolben.Verdichtung, Nockenwellen.C1000, Nockenwellen.[H in OT], Nockenwellen.[Grundkreis-Radius], Emissionstechnologie.AGN, Emissionstechnologie.innermotorisch, Datensätze.CAL_ID, Datensätze.Ersteller, Datensätze.Erstelldatum, Datensätze.[Einsatz Termin], IIf(Not IsNull([Einsatz_MV]),Comp("SELECT Einsatz, Einsatz_MV FROM [qry Einsatz DS900 Euro4_2] WHERE [ID-Datensatz] = " & [ID-Datensatz]),[Einsatz]) AS Einsatz_Toni, Datensätze.Zertifiziert
FROM Motorvarianten INNER JOIN ((Applikationsvarianten LEFT JOIN Baumuster ON Applikationsvarianten.Applikationsvariante = Baumuster.Applikationsvariante) RIGHT JOIN (Nockenwellen RIGHT JOIN (Kolben RIGHT JOIN (Emissionstechnologie RIGHT JOIN (Düsen RIGHT JOIN (ATL RIGHT JOIN ((Datensätze LEFT JOIN [qry Ölwanne Beschreibung] ON Datensätze.Ölwannen = [qry Ölwanne Beschreibung].Ölwannen) LEFT JOIN [qry Lüfterkupplungen-Beschreibung] ON Datensätze.Lüfterkupplungen = [qry Lüfterkupplungen-Beschreibung].Lüfterkupplungen) ON ATL.[ATL-Nummer] = Datensätze.[ATL-Nummer]) ON Düsen.[Düsen-Art] = Datensätze.[Düsen-Art]) ON Emissionstechnologie.Emissionstechnologie = Datensätze.Emissionstechnologie) ON Kolben.Kolbennummer = Datensätze.Kolbennummer) ON Nockenwellen.[Nockenwellen-Nummer] = Datensätze.[Nockenwellen-Nummer]) ON Applikationsvarianten.Applikationsvariante = Datensätze.Applikationsvariante) ON Motorvarianten.Motorvariante = Datensätze.Motorvariante
WHERE (((Datensätze.[Status (A, B, C, F, V, P)])="V" Or (Datensätze.[Status (A, B, C, F, V, P)])="P"));

Open in new window

Distinguished Expert 2017
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions