I thought I'd put a little tutorial together on how to find my site within SQL Books Online and Visual Studio Help, to hopefully make my site more accessible and more helpful to those who you use it regularly.

First, you need to setup BOL or VS Help to utilize online help as the primary resource.  When starting up BOL or VS Help for the first time you're prompted with the below dialogs, simply make the "Online" selections.

Then from within BOL or VS Help, click Search and enter your search term.  Here I've used the term 'query method' within SQL BOL to search for the sql xml data type query method.

After opening the search topic, browse to the bottom of the MSDN topic and you can see if I've written any particular community content for that particular MSDN article.

Simply click my blog link and you'll be home sweet home!

If you'd like to request I make any particular community content samples, examples, etc you're welcome to post the MSDN article as a comment for this post and I'll see what I can do it.  The same applies for Visual Studio.  If you already have BOL/Visual Studio running, then you should review your Help settings in Tools->Options->Environment->Help->Online (SSMS and VS).  Make sure to have "Try online first, then local" selected and reorder your search providers with MSDN Online at the top.

HTH,

Jeff

I've always been frustrated when having retrieved a Visual Studio project that's read only.  So, today I decided to fix the problem once and for all and create a simple batch file that clears the RO flags necessary to successfully build.  I know I'm not the only one who's experienced this, so here you go.  Let me know if I've over looked any file extensions and I'll add to the list.  Just stick the contents below (or download the file below) into a batch file and execute it at the command prompt like the provded example.

attrib -r /s %1*.exe
attrib -r /s %1*.exe.manifest
attrib -r /s %1*.dll
attrib -r /s %1*.pdb
attrib -r /s %1*.xml
attrib -r /s %1*.resources
attrib -r /s %1*.FileListAbsolute.txt
attrib -r /s %1*.txt
attrib -r /s %1*.cache
FOR /F "eol=; tokens=* delims=, " %%i in ('dir/b/s/ad %1*Debug') do attrib -r -h /s %%i\*.*

Example execution: Buildable.bat C:\Temp\BrokenBuild\

Buildable.bat (325.00 bytes)

HTH,

Jeff

This particular issues appears when you F5 (or Debug->Start Debugging) your web application or website to begin debugging your website.  The following message appears.

The first step to resolve this issue is to verify the Local IIS Web Server location you're attempting to debug.  By right-clicking your project within Visual Studio and click Properties, then navigate to the Web tab of your project properties.  You'll notice under the Server section of the interface your selection of Use Local IIS Web server and the coresponding Project Url.  It should appear as such: 

The solution within IIS 6 is to open IIS Manager within Administrative Tools, right click the website corresponding to the Project Url within Visual Studio and click properties.  This particular error implies that integrated Windows Authenticatin is not enabled within IIS, so navigate to the Directory Security tab, click the Edit button under Authentication and access control

and ensure that the Integrated Windows Authentication checkbox is selected, click OK through the dialog boxes and retry.

HTH,

Jeff

Back again on the performance bend,

While developing a tool that was to output a significant amount of messaging from a stored prcoedure I had decided to utilize SQL's xml dml statements to incrementally build xml output that was to be handed back via an XML type OUTPUT parameter from the stored procedure.  This appeared, at first glance, to be a pretty familiar concept just adding some single elements to xml containing a message type and message text, here's an example of the proposed xml output. 

<root versionmessage type="1" text="" text="Here’s the second message, it’s warning you." />

    <messagemessages>

</root> 

Like I said, seemed pretty simple.  Here's the simple procedure I put together to add messages to the resulting xml.

ALTER@ResultXml xml OUTPUT

AS

            ')

END

So, the big issue became when I started looking at the execution plan of the procedure executing.  I was doing some inserts which I assumed may be taking some time to perform.  To my surprise, I found that every call of my procedure was taking 16% of the total query.  This was an insane amount of time for what I thought should be extraordrinarily compared to the more complicated inserts contained within the larger procedure.  So, I thought "Hmmmmmm... Maybe the optimizer's being slowed because the xml I'm providing it is untyped.  Maybe it's performing the insert of the m✃乤cerun: yes">    @Message VARCHAR(512),

    @ResultXml xml (Message: EN-US; mso-bidi-language: AR-SA; mso-bidi-font-family: 'Times New Roman'" lang="EN">…

Another surprise, now instead of each execution of the MessageUpdate procedure taking 16%, now they take 21%!  Note to self, from a performance perspective, apparently applying schema validation is far more expensive than any possible performance improvement gained by having the xml strongly typed.

So, my conclusion is that SQL's XML DML is not the right tool for the job.  I quickly left XML DML behind for the comfort, safety and performance of FOR XML EXPLICIT.  I created a temporary table in my 'parent' stored prorcedure and revamped the MessageUpdate procedure to instead perform inserts into the temporary table.  Now that the MessageUpdate portion of the execution plan was back down to 0% of the query, I could concentrate on building functionality rather than worrying about performance.

Good luck!

Jeff 

Within SQL Server I've created several split functions over time, very handy each of them.  I had the thought to use a recursive CTE for splitting a string and thought I'd try my hand at that.  I believe this is one of the more concise versions of the split function that I've thought of within SQL.  Of course, some of my other versions were slightly more enhanced than this, but this get's the job done.  I'll do more enhancements later.

CREATE FUNCTION dbo.fnSplit (

   @List varchar(8000)

      WITH List (ListValue, RestOfList) As (

            SELECT SUBSTRING(@List, 1, CHARINDEX(@Delimiter, @List) - 1) As ListValue, SUBSTRING(@List, CHARINDEX(@Delimiter, @List) + 1, 8000) As RestOfList

            UNION ALL

      )

      INSERT INTO @SplitList

      SELECT ListValue

      FROM List

      RETURN

END

GO

Jeff

As a follow up to my two previous articles Query too complex – SQL XML and SQL XML performance procedure cache, I have found a solution to the problem.  Turns out the problem stems from a compound join against a query using .nodes to return a result set from an xml variable.  It looked something like this:

INNER JOIN 'varchar(50)') product_id

,x.a.value('../@id', 'varchar(100)') media_id

, 'varchar(MAX)') attribute_value

FROM @xml.nodes('/root/products/product/mediagroup/media/attribute[@action="update"]') as x(a) ) v

AND ipa.LocaleCode = v.language 

The solution that I found was to include a query hint to make it so SQL Server didn't have to do any heavy thinking (that it apparently is incapable of in the first place).  I was lucky enough to only be resource bound on a development box and not a production server.  So, I utilized the query plan from the server to determine the right hint to use in development.  Here was the rsulting code (notice the LOOP hint in the INNER JOIN statement):

Now SQL Server no longer hurts itself trying to lift that heavy SQL.

INNER LOOP JOIN (

<pan style="color: #ff0000; font-size: x-small;">) product_id

,x.a.value('../@id', 'varchar(100)') media_id

, @xml.nodes('/root/products/product/mediagroup/media/attribute[@action="update"]') as x(a) ) v

ON ipa.ProductId = v.language

HTH,

Jeff

I experienced an issue yesterday, relatively annoying, while trying to performance analyze some code within VSTS.  I thought I'd throw this out on the blog to try and help people find the solution a little easier than rummaging around in forums or the blogesphere looking to find the answer.  After having setup to performance analyze a solution of mine, I ran it once and something happened (what exactly, I don't recall), but my profile didn't end up running.  I believe I had the web.config file open during the profiling and it was some sequence of me either ignoring the update to web.config when it said that it needs to be reloaded.  That appeared to have put my web.config value into a "bad state" from that point forward.  Here's the error I received.

A pretty non-descript error if you ask me.  I found the solution over here to the problem. As it turns out, Visual Studio adds a couple app.config values to your web.config during the performance profiling.  Here are tr: #0000ff; font-size: x-small;"><add key="microsoft.visualstudio.teamsystems.aspnetdevserver:/path/page.aspx?querystring=value&amp;querystring2=value2&amp;querystring3=value3&amp;locale=en" value="1000;True;7184;1;-8589569366691159969" />

The prior solution states that  the below web.config section is added by profiling, but I did not modify that as my web.config uses that section for an System.Web.Extensions assembly binding configuration section.

The long story made short is I would suggest closing your web.config file while profiling ASP.NET.  The same would hold true for console app app.config files I would assume.  I haven't gone as far a testing that yet though.

HTH,

Jeff Fischer

This particular issues appears when you F5 (or Debug->Start Debugging) your web application or website to begin debugging your website and your host header with port number does not match the host header and port number entered in visual studio (where your host header matches, but port # does not).  The following message appears.

The first step to resolve this issue is to verify the Local IIS Web Server location you're attempting to debug.  By right-clicking your project within Visual Studio and click Properties, then navigate to the Web tab of your project properties.  You'll notice under the Server section of the interface your selection of Use Local IIS Web server and the coresponding Project Url.  It should appear as such:

The solution within IIS 6 is to open IIS Manager within Administrative Tools, right click the website corresponding to the Project Url within Visual Studio and click properties.  This particular error implies that your Url is mal-formed.  The thing to do is to match the host header you've specified within your Project Url and verify the port number specified for that host header within IIS.

By clicking the Advanced button

you can click one of the identities in the list and verify that the port number specified within the IIS matches that within Visual Studio and rectify if necessary.

HTH,

Jeff

This particular issues appears when you F5 (or Debug->Start Debugging) your web application or website to begin debugging your website and your host header with port number does not match the host header and port number entered in visual studio.  The following message appears.

 The first step to resolve this issue is to verify the Local IIS Web Server location you're attempting to debug.  By right-clicking your project within Visual Studio and click Properties, then navigate to the Web tab of your project properties.  You'll notice under the Server section of the interface your selection of Use Local IIS Web server and the coresponding Project Url.  It should appear as such:

The solution within IIS 6 is to open IIS Manager within Administrative Tools, right click the website corresponding to the Project Url within Visual Studio and click properties.  This particular error implies that your Url is mal-formed.  The thing to do is to match the port number you've specified within your Project Url and verify the Host header specified for that port number within IIS.

By clicking the Advanced button

you can click one of the identities in the list and verify that the host header value specified within the IIS matches those within Visual Studio and rectify the if necessary.

HTH,

Jeff

Found an error in this url, refer to this blog article is here - Unable to start debugging the web server.  Debugging failed because integrated Windows authentication is not enabled.

HTH,

Jeff