Alright, so this is one of those 15 minute things I did to save myself a little time, but someone will probably show up and tell me a better way to do it..

Below is the code for a function that accepts an input in the form of a SQL string as generated by the DQE... The output is a SQL statement that can be executed easily in Enterprise Manager (OR SQLMS)

There is NO error checking here.. I just needed something to work quickly..

     Private Function Convert(ByVal input As String) As String
        Dim newSQL As String = input
        newSQL = newSQL.Substring(newSQL.IndexOf("'"c, 0) + 1)
        Dim paramInfo As String = newSQL.Substring(newSQL.IndexOf("'"c) + 1)
        paramInfo = paramInfo.Substring(paramInfo.IndexOf("'"c, 3) + 2)

        newSQL = newSQL.Substring(0, newSQL.IndexOf("'"c))



        Dim charPos As Integer = 0
        Dim pair As String = ""
        Dim inQuotes As Boolean = False

        While charPos < paramInfo.Length
            Dim thisChar = paramInfo(charPos)

            If thisChar = "'" Then
                If inQuotes Then
                    inQuotes = False
                Else
                    inQuotes = True
                End If
            End If

            If (thisChar = "," And Not inQuotes) Or charPos = paramInfo.Length - 1 Then
                If charPos = paramInfo.Length - 1 Then
                    pair &= thisChar
                End If

                Dim name As String = pair.Split("="c)(0)
                Dim value As String = pair.Split("="c)(1)
                newSQL = newSQL.Replace(name, value)
                pair = ""
            Else
                pair &= thisChar
            End If

            charPos += 1

        End While

        Return newSQL

    End Function

Sample input

 exec sp_executesql N'SELECT [SBLP].[dbo].[User].[UserId], [SBLP].[dbo].[User].[SchoolId], [SBLP].[dbo].[User].[UserName], 
[SBLP].[dbo].[User].[Password], [SBLP].[dbo].[User].[FirstName], [SBLP].[dbo].[User].[LastName], [SBLP].[dbo].[User].[Email], 
[SBLP].[dbo].[User].[Description], [SBLP].[dbo].[User].[ApplicationName], [SBLP].[dbo].[User].[PasswordQuestion], 
[SBLP].[dbo].[User].[PasswordAnswer], [SBLP].[dbo].[User].[IsApproved], [SBLP].[dbo].[User].[LastActivityDate], 
[SBLP].[dbo].[User].[LastLoginDate], [SBLP].[dbo].[User].[LastPasswordChangeDate], [SBLP].[dbo].[User].[LastLockedOutDate], 
[SBLP].[dbo].[User].[IsOnline], [SBLP].[dbo].[User].[IsLockedOut], [SBLP].[dbo].[User].[FailedPasswordAttemptCount], 
[SBLP].[dbo].[User].[FailedPasswordAttemptWindowStart], [SBLP].[dbo].[User].[FailedPasswordAnswerAttemptCount], 
[SBLP].[dbo].[User].[FailedPasswordAnswerAttemptWindowStart], [SBLP].[dbo].[User].[ShowAdvancedStandardsOptions], 
[SBLP].[dbo].[User].[AllowClassManagement], [SBLP].[dbo].[User].[CreatedByUser], [SBLP].[dbo].[User].[CreatedDate], 
[SBLP].[dbo].[User].[ModifiedByUser], [SBLP].[dbo].[User].[ModifiedDate] FROM [SBLP].[dbo].[User]  WHERE ( ( 
[SBLP].[dbo].[User].[UserName] = @UserName1 AND [SBLP].[dbo].[User].[ApplicationName] = @ApplicationName2))',N'@UserName1 
varchar(50),@ApplicationName2 varchar(50)',@UserName1='asdf',@ApplicationName2='SBLP'

Output

 SELECT [SBLP].[dbo].[User].[UserId], [SBLP].[dbo].[User].[SchoolId], [SBLP].[dbo].[User].[UserName], 
[SBLP].[dbo].[User].[Password], [SBLP].[dbo].[User].[FirstName], [SBLP].[dbo].[User].[LastName], [SBLP].[dbo].[User].[Email], 
[SBLP].[dbo].[User].[Description], [SBLP].[dbo].[User].[ApplicationName], [SBLP].[dbo].[User].[PasswordQuestion], 
[SBLP].[dbo].[User].[PasswordAnswer], [SBLP].[dbo].[User].[IsApproved], [SBLP].[dbo].[User].[LastActivityDate], 
[SBLP].[dbo].[User].[LastLoginDate], [SBLP].[dbo].[User].[LastPasswordChangeDate], [SBLP].[dbo].[User].[LastLockedOutDate], 
[SBLP].[dbo].[User].[IsOnline], [SBLP].[dbo].[User].[IsLockedOut], [SBLP].[dbo].[User].[FailedPasswordAttemptCount], 
[SBLP].[dbo].[User].[FailedPasswordAttemptWindowStart], [SBLP].[dbo].[User].[FailedPasswordAnswerAttemptCount], 
[SBLP].[dbo].[User].[FailedPasswordAnswerAttemptWindowStart], [SBLP].[dbo].[User].[ShowAdvancedStandardsOptions], 
[SBLP].[dbo].[User].[AllowClassManagement], [SBLP].[dbo].[User].[CreatedByUser], [SBLP].[dbo].[User].[CreatedDate], 
[SBLP].[dbo].[User].[ModifiedByUser], [SBLP].[dbo].[User].[ModifiedDate] FROM [SBLP].[dbo].[User]  WHERE ( ( 
[SBLP].[dbo].[User].[UserName] = 'asdf' AND [SBLP].[dbo].[User].[ApplicationName] = 'SBLP'))

I don't know about a better way, but this was on my list of "things to do" for a while, so your code saved me some time

MattWoberts wrote:

I don't know about a better way, but this was on my list of "things to do" for a while, so your code saved me some time

I had been wanting to do it for a while to. If more people like the idea, I can send frans the very simple winforms app I created to harness the power of this simple function. It's just two text boxes and a button but has saved me loads of time already (Imagine converting close to a thousand parameters by hand).

Nice Job

Hi.

It turned out I needed something different, so I had to write my own. I wanted some code to work on the output from LLBLGens built-in tracing, and turn that into SQL I can execute directly in query analyser.

Heres a sample input:

Query: SELECT [EntropyDemo].[dbo].[Site].[ID] AS [Id], [EntropyDemo].[dbo].[Site].[Company_ID] AS [CompanyId], [EntropyDemo].[dbo].[Site].[BusinessArea_ID] AS [BusinessAreaId], [EntropyDemo].[dbo].[Site].[Country_ID] AS [CountryId], [EntropyDemo].[dbo].[Site].[Type_ID] AS [TypeId], [EntropyDemo].[dbo].[Site].[Status_Abbrev] AS [StatusAbbrev], [EntropyDemo].[dbo].[Site].[Name], [EntropyDemo].[dbo].[Site].[Altern_Name] AS [AlternName], [EntropyDemo].[dbo].[Site].[GMT_Diff] AS [GmtDiff], [EntropyDemo].[dbo].[Site].[Building_Name] AS [BuildingName], [EntropyDemo].[dbo].[Site].[Street_Name] AS [StreetName], [EntropyDemo].[dbo].[Site].[Town], [EntropyDemo].[dbo].[Site].[County], [EntropyDemo].[dbo].[Site].[Postcode], [EntropyDemo].[dbo].[Site].[Description], [EntropyDemo].[dbo].[Site].[Address], [EntropyDemo].[dbo].[Site].[Website_URL] AS [WebsiteUrl], [EntropyDemo].[dbo].[Site].[SiteArchived] FROM [EntropyDemo].[dbo].[Site] WHERE ( ( [EntropyDemo].[dbo].[Site].[ID] = @Id1))

Parameter: @Id1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1663.

And the output...

SELECT [EntropyDemo].[dbo].[Site].[ID] AS [Id], [EntropyDemo].[dbo].[Site].[Company_ID] AS [CompanyId], [EntropyDemo].[dbo].[Site].[BusinessArea_ID] AS [BusinessAreaId], [EntropyDemo].[dbo].[Site].[Country_ID] AS [CountryId], [EntropyDemo].[dbo].[Site].[Type_ID] AS [TypeId], [EntropyDemo].[dbo].[Site].[Status_Abbrev] AS [StatusAbbrev], [EntropyDemo].[dbo].[Site].[Name], [EntropyDemo].[dbo].[Site].[Altern_Name] AS [AlternName], [EntropyDemo].[dbo].[Site].[GMT_Diff] AS [GmtDiff], [EntropyDemo].[dbo].[Site].[Building_Name] AS [BuildingName], [EntropyDemo].[dbo].[Site].[Street_Name] AS [StreetName], [EntropyDemo].[dbo].[Site].[Town], [EntropyDemo].[dbo].[Site].[County], [EntropyDemo].[dbo].[Site].[Postcode], [EntropyDemo].[dbo].[Site].[Description], [EntropyDemo].[dbo].[Site].[Address], [EntropyDemo].[dbo].[Site].[Website_URL] AS [WebsiteUrl], [EntropyDemo].[dbo].[Site].[SiteArchived] FROM [EntropyDemo].[dbo].[Site] WHERE ( ( [EntropyDemo].[dbo].[Site].[ID] = 1663))

And finally, the code if anyone is interested

public static string MakeNice(string src)
        {
            int paramIndex, firstParamIndex;
            string newSQL = src.Replace("Query: ", "") + "\n";
            firstParamIndex = newSQL.IndexOf("Parameter:");
            if (firstParamIndex < 1) return src;

            paramIndex = 0;
            string paramSearchSQL = newSQL.Substring(firstParamIndex);
            newSQL = newSQL.Substring(0, firstParamIndex);
            string paramName = "";
            string paramValue = "";

            Regex paramRegEx = new Regex(@"Parameter:\s(?<paramName>.*?)\s:.*Value:\s(?<paramValue>.*?)\.",
                RegexOptions.Compiled | RegexOptions.IgnoreCase);

            while (paramIndex >= 0)
            {
                MatchCollection matches = paramRegEx.Matches(paramSearchSQL.Substring(paramIndex,
                    paramSearchSQL.IndexOf("\n", paramIndex)));
                foreach (Match match in matches)
                {
                    newSQL = newSQL.Replace(match.Groups["paramName"].Value, match.Groups["paramValue"].Value);
                }
                paramIndex = paramSearchSQL.IndexOf("Parameter:", paramIndex+1);
            }

            return newSQL;

        }

MattWoberts wrote:

Hi.

It turned out I needed something different, so I had to write my own. I wanted some code to work on the output from LLBLGens built-in tracing, and turn that into SQL I can execute directly in query analyser.

Heres a sample input:

I'm confused. My code example came from Profile analyzer so I don't know why yours is any different. I believe you, but I don't know why it would appear different on your system. Am I just not thinking clearly this morning?

alexdresko wrote:

I'm confused. My code example came from Profile analyzer so I don't know why yours is any different. I believe you, but I don't know why it would appear different on your system. Am I just not thinking clearly this morning?

You're working off Profiler analyser, I'm working off LLBLGen's own build in trace support - the format I gave is how LLBLGen reports SQL statements when you add this to your web.config:

        <switches>
            <add name="SqlServerDQE" value="4" />
        </switches>

MattWoberts wrote:

alexdresko wrote:

I'm confused. My code example came from Profile analyzer so I don't know why yours is any different. I believe you, but I don't know why it would appear different on your system. Am I just not thinking clearly this morning?

You're working off Profiler analyser, I'm working off LLBLGen's own build in trace support - the format I gave is how LLBLGen reports SQL statements when you add this to your web.config:

        <switches>
            <add name="SqlServerDQE" value="4" />
        </switches>

Ah.. then we need one utility that does both.. Then Frans needs to add it as a utility window in LLBLGen.

I agree - "one tool to format them all" is the way to go I'm not sure what Frans will make of a utility window though

Maybe we should get something together and sent to Frans for the third-party tools section..

MattWoberts wrote:

I agree - "one tool to format them all" is the way to go I'm not sure what Frans will make of a utility window though

Maybe we should get something together and sent to Frans for the third-party tools section..

Does the tracing output always begin with "Query:" ? I think we could do this relatively easily if we planned it all out here. I'm thinking a simple winforms app with two text boxes and a button.

We could call it Aglaizer (one day I'll stop putting her on a pedestle)

Howdee.

Right, the output from LLBLGen, when verbose tracing is required looks a like this:

Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: <Query>
Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
        Query: <Query>

Where <Query> is of the format I posted before:

Query: SELECT blah blah blah....
    Parameter: @Language_Code1 : AnsiStringFixedLength. Length: 3. Precision: 0. Scale: 0. Direction: Input. Value: en.
    Parameter: @Language_Code2 : AnsiStringFixedLength. Length: 3. Precision: 0. Scale: 0. Direction: Input. Value: fr.

So yesy the query is always prefixed with "Query:" - whci allows you to differentiate between profiler and llblgen output.

It makes sense to wrap this up into a winforms app - have you started this yet? Why 2 textboxes - are you thinking one for the input and one for the output?

Like you said it should be dead easy, but I have issues with the name "Aglaizer" !!

MattWoberts wrote:

Howdee.

Right, the output from LLBLGen, when verbose tracing is required looks a like this:

Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: <Query>
Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
        Query: <Query>

Where <Query> is of the format I posted before:

Query: SELECT blah blah blah....
    Parameter: @Language_Code1 : AnsiStringFixedLength. Length: 3. Precision: 0. Scale: 0. Direction: Input. Value: en.
    Parameter: @Language_Code2 : AnsiStringFixedLength. Length: 3. Precision: 0. Scale: 0. Direction: Input. Value: fr.

So yesy the query is always prefixed with "Query:" - whci allows you to differentiate between profiler and llblgen output.

It makes sense to wrap this up into a winforms app - have you started this yet? Why 2 textboxes - are you thinking one for the input and one for the output?

Like you said it should be dead easy, but I have issues with the name "Aglaizer" !!

Yes, one tb for input, one tb for output. The tb for output is appropriate because it's easy to copy the contents of a tb to the clipboard.

I really was joking about Aglaizer. Aglaia probably thinks I'm some kind of perverted old man, when really I'm just a perverted 27 year old. For all I care it can be called "WindowsApplication1"

It would probably take less time to throw it all together than discuss it back and forth at this point.

I really was joking about Aglaizer. Aglaia probably thinks I'm some kind of perverted old man, when really I'm just a perverted 27 year old. For all I care it can be called "WindowsApplication1"

Lol - I knew you were joking, or at least I thought you were joking

Its good to come accross someone younger than myself in this forum - me being a grand old age of 28!

WindowsApplication1 - Hmmm, I'm sure i have a program of the same name No, lets call it, erm, LLBLGen-SqlFormattingThing or something equally dull !!