Createc Connect Bug for an issue caused by a Visual Studio dialog adding a ASCII 127 character to a database name.

https://connect.microsoft.com/SQLServer/feedback/details/608963/database-names-containing-control-characters

You can't tell the difference between the names within Management Studio.

Vote for it if you like.

I've logged a bug against SQL Server 2008 for sp_help throwing errors on columns greater than 106 characters (I know, a bit of a stretch).  I was in the middle of writing some code for automating a data pull and ran into this.  If you're able, go and add to the repro count for the bug with the script I've attached.  It's really simple, it creates a two column table, runs sp_help on it then drops the table.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=496501

Thanks,

Jeff

I was just helping a friend figure out his connection issue to SQL Server.  He was getting error number 18456 with a severity of 14 and state of 11.  He was running this on a recent fresh install of Windows 7.  It turns out that he still had UAC enabled on his box and this was the culprit.  It was then that I realized that UAC is like a computer prophylactic.  As you can surmise, I'm not too fond of UAC, like the rest of the population.  Just beware of your prophylactic causing you problems in the event you run into this severity and state.

Either right click SQL Management Studio and "run as administrator" before trying to connect or just disable UAC all together.  I prefer the latter.

Jeff

Today, while building a procedure that imports data into a database I ran into a pretty significant SQL Server performance problem that I thought I should blog about to get ahead of someone running into the problem in production.

The basics of the solution that I was building are this, I am passing in an input xml file into a procedure (datatype xml) and querying this xml against the data to be updated by this import xml.  The query takes data from the xml and does various checks against the database to see where their may be issues with the "to be imported" data.  It formulates the results of these checks into a resulting validation xml using FOR XML EXPLICIT.  Once verified, the procedure modified the data within the database.  The results xml created by the validation was used as an output parameter of the stored procedure.

I found that the compilation of the procedure seemed to take a significant amount of time based on the selection of the nodes method of the xml variable.  Upon adding statemetns including @xml.nodes(...) into the stored procedure the compilation of the procedure took drastically longer (1 min 15 seconds per compile to e exact).  After compilation the procedure took less than 1 second to execute.  I tested the procedure DBCC FREEPROCCACHE to see if it was the procedure cache creation specifically that was slowing the procedure down and sure enough it was.  I then tested to see what the effect of adding WITH RECOMPILE to my procedure would be.  As I suspected, every execution of the procedure began taking 1 min 15 seconds to execute because of the recompilation of the procedure.  The final behavior that I tested was the number of @xml.nodes(...) references within the procedure.  It appeared to have a direct correlation between the number of @xml.nodes(...) statements/joins and the length of time it took to create the procedure cache.  Of course, after first execution (excluding WITH RECOMPILE) the procedure executed instananeously.

So, my suggestion is to be very weary of procedure recompiles on procedures that require the nodes method.  Please be ware!  Take a look at the attached script for example purposes.  I've duplicated similar, yet more condensed, logic as an example of my efforts and experience.

AdventureWorksSQLXmlProcedurePlan.sql (1.50 kb)

Jeff Fischer

I had the pleasure of performing an age-old task within SQL Server this evening, recreating a SQL Server dependency chain.  After googling and not coming up with much immediately I went down the path of creating the SQL myself.  Actually, I was fixing a bug in a previously failed attempt at creating the dependency chain with a T-SQL statement, but anyways.  I created the below T-SQL based on the following assumptions that I had validated within my database.

1. That the database contains a currently valid dependency chain.  ie. that you have not dropped a "lower level" dependent object and recreated it.  If so, you need to recreate the "upward" dependent objects to ensure a valid dependency chain exists within the database.
2. That the dependencies are stricly based on views.
3. That the procedures are only dependent on views and no cross-procedure dependencies exist.

CREATE VIEW [dbo].FROM sys.objects so

      INNER JOIN sys.sql_dependencies sd

      ON so.object_id = sd.object_id

      WHERE so.type = 'V'

         so.object_id

       , sd.referenced_major_id

       , so.name

       , type

       , 1 As Level

.type = 'V'

      AND so.name Not Like 'vwObject%'

      AND sd.object_id Is Null

      UNION ALL

Level

      FROM dbo.vwObjectDistinctDependencies so

      INNER JOIN DependencyChang dc

      ON  so.referenced_major_id = dc.object_id

  &ᰀᄄ䭰䑉晥畡瑬敋卹数ㅣ„Ѓ㄁萰ᜀ଄䭰䭉祥獕条ㅥ„Єꀂ　„Н瀒䥋慍䥸獳極杮敄瑰ㅨ„Ѓ、萰㌀ᔄ䭰䍉楲楴慣䕬瑸湥楳湯ㅳ„Ж㈉㔮㈮⸹㔱ऄ⸲⸵㤲ㄮ〷„Х瀓䥋硅楰慲楴湯敐楲摯萱਀ࠄ䀀蜹￾萰∀င䭰佉敶汲灡敐楲摯萱਀ࠄ耀દ�￿萰夀ጄ䭰䕉瑸湥敤䭤祥獕条ㅥ„оㄑ㌮㘮ㄮ㔮㔮㜮㌮㈮ᄄ⸱⸳⸶⸱⸵⸵⸷⸳бㄖ㌮㘮ㄮ㐮ㄮ㌮ㄱ㈮⸰⸲〲„ч瀎䥋敄慦汵䍴偓ㅳ„бㄯ䴬捩潲潳瑦删䅓匠桃湡敮牃灹潴牧灡楨⁣牐癯摩牥萰ᴀሄ獭䭐ⵉ䅒匭杩慮畴敲萱̀Ą〰„С洕偳䥋䔭牮汯浬湥⵴汆条萱ЀȄ㈳萰℀ᘄ獭䭐ⵉ牐癩瑡ⵥ敋⵹汆条萱̀Ą〰„Ю洛偳䥋䌭牥楴楦慣整中浡ⵥ汆条萱଀ऄ㌱㈴㜱㈷〸„Ф洖偳䥋䴭湩浩污䬭祥匭穩ㅥ„І㈄㐰〸„Ш洝偳䥋吭浥汰瑡ⵥ捓敨慭嘭牥楳湯萱̀Ą〲„Ш洝偳䥋吭浥汰瑡ⵥ楍潮⵲敒楶楳湯萱̀Ą〰„Ѫ洗偳䥋䌭牥⵴敔灭慬整伭䑉萱䬀䤄⸱⸳⸶⸱⸴⸱ㄳ⸱ㄲ㠮ㄮ〰㜹㠱⸰ㄱㄶ㈶㈳ㄮ㈵㘸㈹⸶㘱〳㠶㈲ㄮ㔴㈴㈸⸶㈶ㄮ㈮〸„г洙偳䥋倮儔ូ䠂Ⴇ쇧롚ꉦԁԀ⮯慡ⱚ泽맳Ȅ(İ
쥨ฐ磻ᇒ풐쀀祏嗜āԀ (İ
賂ꁛូ䠂Ⴇ쇧롚ꉦāԀ $ÿԁԀ⮯慡ⱚ泽맳Ȁ$ÿԁԀ⮯慡ⱚ泽맳ȇ”āԀԁԀ⮯慡ⱚ泽맳ȇԁԀ⮯慡ⱚ泽맳ȇ萰᐀Ԅ汦条ㅳ„Ї㘅㘵㈳萰ᔀࠄ敲楶楳湯萱Ԁ̄ㄱ〰„М瀑䥋敄慦汵䭴祥灓捥萱̀Ą〱„З瀋䥋敋啹慳敧萱ЀȄ 萰ᴀሄ䭰䵉硡獉畳湩䑧灥桴萱̀Ą〰„г瀕䥋牃瑩捩污硅整獮潩獮萱ᘀऄ⸲⸵㤲ㄮе㈉㔮㈮⸹㜱萰─ጄ䭰䕉灸物瑡潩偮牥潩ㅤ„Њ㥀⺇ﻡヿ„Т瀐䥋癏牥慬偰牥潩ㅤ„ЊꚀ＊￞ヿ„Ѫ瀓䥋硅整摮摥敋啹慳敧萱伀ᄄ⸱⸳⸶⸱⸵⸵⸷⸳вㄑ㌮㘮ㄮ㔮㔮㜮㌮ㄮᘄ⸱⸳⸶⸱⸴⸱ㄳ⸱〲㈮㈮༄⸱⸳⸶⸱⸵⸲⸳〵„ч瀎䥋敄慦汵䍴偓ㅳ„бㄯ䴬捩潲潳瑦删䅓匠桃湡敮牃灹潴牧灡楨⁣牐癯摩牥萰ᴀሄ獭䭐ⵉ䅒匭杩慮畴敲萱̀Ą〰„С洕偳䥋䔭牮汯浬湥⵴汆条萱ЀȄ㈳萰℀ᘄ獭䶈es;">  , CASE type WHEN 'V' THEN 'View' WHEN 'P' THEN 'Procedure' END As levelname

 , MAX(level) As Level

FROM DependencyChang

 <olor: blue;">type WHEN 'V' THEN 'View' WHEN 'P' THEN 'Procedure' END As levelname

 , 100

FROM sys<ul>

HTH,

Jeff Fischer