Every day is a school daynoreply@blogger.com (_RichardABrown)Sun, 22 Mar 2026 00:12:42 -0700Blogger http://www.blogger.com101125http://richbrownesq-sqlserver.blogspot.com/en-ushttp://richbrownesq-sqlserver.blogspot.com/2012/09/iis-ie-doesnt-auto-authenticate.htmlauthenticationbrowserIEIISintegratedinternet explorerMVCSecurityURLVisual Studio 2010web appweb applicationweb developmentnoreply@blogger.com (_RichardABrown)Mon, 17 Sep 2012 07:43:00 -0700tag:blogger.com,1999:blog-5070731366789101952.post-5566012937131272311I've been doing a little web development work recently with MVC and have come across a (very) minor issue when deploying this to my test server. Essentially, I've deployed to the application to the server but when I access it via the IP Address, I get prompted for my windows credentials. However, when accessing it via server name I am not prompted and I get the (much better) user experience of 0http://richbrownesq-sqlserver.blogspot.com/2012/09/t-sql-allow-low-privileged-user-to.htmldatabaseddldeletedmlSecuritySQLSQL 2008SQL ServerStored ProcedureT-SQLtruncateusersnoreply@blogger.com (_RichardABrown)Mon, 17 Sep 2012 05:35:00 -0700tag:blogger.com,1999:blog-5070731366789101952.post-3655551884202200014The problem with TRUNCATE is that it is a DDL statement rather than the regulation DELETE statement which is DML. As such, its not captured by using the db_datewriter database role and so on the face of it, you're left with two options: 1) Add the user to db_ddladmin role (or db_owner) 2) Grant ALTER table permissions to the table(s) in question I'm not really a fan of either of these 0http://richbrownesq-sqlserver.blogspot.com/2012/07/ssms-help-prevent-oh-moment.htmlcodingconnectionsddldmlManagement StudioSQL Developer ToolsSSMSSSMS Tools PackT-SQLtoolsnoreply@blogger.com (_RichardABrown)Thu, 26 Jul 2012 03:01:00 -0700tag:blogger.com,1999:blog-5070731366789101952.post-8064122841902441088Its funny how sometimes you think of something which would be great idea and then the solution falls into your lap. This very thing happened to me the other day after an "oh ****" moment when I executed a query on a production server rather than the test server. Unfortunately, there weren't any fail safes in place (eg restricted privileges) so the query ran successfully and promptly updated 0http://richbrownesq-sqlserver.blogspot.com/2012/07/powershell-string-concatenation-with.htmlAdminfilefilesystemPowershellScriptingunderscorevariablesnoreply@blogger.com (_RichardABrown)Thu, 19 Jul 2012 06:36:00 -0700tag:blogger.com,1999:blog-5070731366789101952.post-490739389553926166In putting together a simple powershell script to download a file and store it on a filesystem, I came across some strangeness with the user of Underscore (_) when concatenation variables. Essentially, I wanted to create a filename based upon 2 powershell variables, separated by an underscore. I wanted my filename to be:  Richard_Brown.txt And my powershell read something like: $File = "C0http://richbrownesq-sqlserver.blogspot.com/2012/07/t-sql-tuesday-32-day-in-life.htmlAdminautomationcareercommunitydatabase adminDBAjobSPOFSQLT-SQL Tuesdaynoreply@blogger.com (_RichardABrown)Tue, 17 Jul 2012 12:58:00 -0700tag:blogger.com,1999:blog-5070731366789101952.post-9212022577337064583T-SQL Tuesday has been a little off my radar for the last few months (as has all blogging activity!), so I was delighted when Twitter reminded me that it was that time of the month. Even better, the topic chosen by Erin Stellato (Blog | Twitter) was a great one that I felt I could really engage with. So here we go. For the record, my job title is Database Administrator. The task as laid out 0http://richbrownesq-sqlserver.blogspot.com/2012/05/t-sql-change-schema-of-user-defined.htmlbooks onlineBugobjectsschemaSQLSQL 2008SQL 2008 R2SQL ServerT-SQLtransfernoreply@blogger.com (_RichardABrown)Tue, 1 May 2012 06:18:00 -0700tag:blogger.com,1999:blog-5070731366789101952.post-5887598625439570662I've been working with one of our developers to try and rationalise some database objects into some sensible schemas using the ALTER SCHEMA syntax: http://msdn.microsoft.com/en-us/library/ms173423.aspx Just out of interest, is it me or is this syntax for changing an objects schema a bit strange? Are you altering the Schema or the Object? Anyhow, I digress... In our example, we have several 0http://richbrownesq-sqlserver.blogspot.com/2012/02/t-sql-get-last-day-of-month.htmlDatedatetimeDenaliDeveloperdevelopmentFormat DateTimeSQL DenaliSQL ServerSQL2012T-SQLnoreply@blogger.com (_RichardABrown)Wed, 15 Feb 2012 03:30:00 -0800tag:blogger.com,1999:blog-5070731366789101952.post-619966485548883348One of my early blog posts highlighted how to get the first day of the current month. Well, another common task is get to the last day of a month and i'm (reasonably) pleased to see that SQL Server 2012 has an inbuilt function to achieve this. In versions previous to SQL2012, this problem can be solved by running the following: DECLARE @InputDate DATE = GETDATE()  SELECT DATEADD(D, -1, 1http://richbrownesq-sqlserver.blogspot.com/2012/02/t-sql-insert-with-xml-shredding-is-very.html.NetampersandBugC#clrconnectCTEdecodeMicrosoftSP1SQL 2008SQL ServerT-SQLXMLnoreply@blogger.com (_RichardABrown)Thu, 9 Feb 2012 04:04:00 -0800tag:blogger.com,1999:blog-5070731366789101952.post-1015905963827658374Back in 2010, I came across a performance bug with SQL2008 SP1. When shredding XML using the nodes query, the performance is fine with a SELECT but when you want to INSERT the data to a table/temptable/tablevariable the performance becomes very poor. The query I was having problems with was something like this (Thanks for the sample data wBob!): DECLARE @xml XML; -- Spin up some test data&1http://richbrownesq-sqlserver.blogspot.com/2012/02/t-sql-sql2012-code-snippets.htmlCode SnippetsDeveloperdevelopmentManagement StudioSQLSQL Developer ToolsSQL ServerSQL2012SSMST-SQLnoreply@blogger.com (_RichardABrown)Thu, 2 Feb 2012 07:21:00 -0800tag:blogger.com,1999:blog-5070731366789101952.post-3466325640487456326I've never really bought into the idea of object templates in SQL Server Management Studio and in my experience, not many other database professionals have either! Just recently however, I've been getting a increasingly frustrated at the non-standard development pattern of database objects and (just as importantly) the lack of documentation for procedures and functions. I've written in the past 1http://richbrownesq-sqlserver.blogspot.com/2012/01/admin-bulkadmin-vs-administer-bulk.htmlAdminadminister bulkbcpbulk insertbulkcopyDBASQL ServerSQL2008T-SQLnoreply@blogger.com (_RichardABrown)Tue, 31 Jan 2012 06:38:00 -0800tag:blogger.com,1999:blog-5070731366789101952.post-3244394805468168114I had an application thrust upon me recently which required the user executing it to have permissions to use the BULK INSERT command in T-SQL. I was aware of the server role bulkadmin which would have been a nice sledgehammer approach to crack this nut, but I was hoping for something a little more refined. Unfortunately, the only thing I came up with was the ADMINISTER BULK OPERATIONS command 1http://richbrownesq-sqlserver.blogspot.com/2012/01/visual-studio-change-default-browser.htmlasp.netbrowserC#Developerdevelopmentinternet explorerrazorVisual StudioVisual Studio 2010web appnoreply@blogger.com (_RichardABrown)Wed, 25 Jan 2012 08:54:00 -0800tag:blogger.com,1999:blog-5070731366789101952.post-4359817938480397326 When debugging web apps in Visual Studio, the browser it throws up is typically your default browser. Makes sense I guess. However, once you have many browsers on your machine you may wish to change this. This is the scenario I cam up against the other day and thanks to this article, I managed to do find a way. Essentially, you need to right click on an ASP.NET (.aspx) page in your project, 0http://richbrownesq-sqlserver.blogspot.com/2012/01/t-sql-find-missing-gaps-in-data.htmlCTEDenaliDevelopergapsleadranking functionsSQLSQL DenaliSQL2008SQL2012T-SQLnoreply@blogger.com (_RichardABrown)Fri, 20 Jan 2012 06:40:00 -0800tag:blogger.com,1999:blog-5070731366789101952.post-5276448656341934023I recently had solve a problem of finding missing values in a data series and at the same time, came across this article on the new Analytic Functions in SQL2012. It turns out that there is a new function which can simplify my solution although as the problem is on a SQL2008 database, I won't be able to implement it. Still, another piece of the jigsaw. Here, I'll present queries for each 1http://richbrownesq-sqlserver.blogspot.com/2012/01/what-is-right-technology.htmlapplicationsDesignExcelproblem solvingrambleSQL Serversupporttechnologynoreply@blogger.com (_RichardABrown)Mon, 16 Jan 2012 02:22:00 -0800tag:blogger.com,1999:blog-5070731366789101952.post-6908181873829448450One of things I strongly believe in is using the right technology for solving the right problem. You can often find (understandably) that IT professionals look for solutions using technologies they understand best which can lead to inappropriate decsions being taken. "When all you have is a hammer....". However, just how do you define the right technology? An analogy. Whats the best way of 0http://richbrownesq-sqlserver.blogspot.com/2012/01/start-and-stop-local-instance-of-sql.htmlAdminDBAservicesSQLSQL 2008SQL Servernoreply@blogger.com (_RichardABrown)Wed, 11 Jan 2012 02:54:00 -0800tag:blogger.com,1999:blog-5070731366789101952.post-4168403085808989167As a database professional, i'm accustomed to having a local sql server instance installed. However, it seems that SQL Server (typically Express) is finding its way onto more and more desktops, often unused and unknown to the user!! With the web browser FireFox taking up almost half your memory, you don't want any other applications taking your valuable resources. I recommend changing the 0http://richbrownesq-sqlserver.blogspot.com/2011/12/admin-windows-group-login-ambiguity.htmlAdminDateGroup MembershiplanguagepermissionsSecuritySQLSQL 2005SQL 2008SQL 2008 R2SQL ServerWindowsnoreply@blogger.com (_RichardABrown)Thu, 8 Dec 2011 01:23:00 -0800tag:blogger.com,1999:blog-5070731366789101952.post-2986686774221293436Its a typical scenario to grant login access to SQL Server via Windows groups as this eases the administrative burden of setting up multiple logins all with the same access. However, its not uncommon for a user to belong to multiple Windows groups and its possible that these groups could each have a SQL login. So whats the problem? Well, consider this example: User - DOMAIN\PeterPan Groups - 0http://richbrownesq-sqlserver.blogspot.com/2011/12/t-sql-audit-data-changes.htmlauditchange trackingdata changesDeveloperdevelopmentSQLSQL ServerT-SQLtriggernoreply@blogger.com (_RichardABrown)Thu, 1 Dec 2011 01:00:00 -0800tag:blogger.com,1999:blog-5070731366789101952.post-1182607771321382670I was recently asked about how to implement auditing of data changes for a database. I know SQL Server contains the Change Data Capture feature but (as is often the case with things I want!), its an Enterprise only feature meaning its out of the reach for the application in question. As it happened, it turned out that there was already an application in-house (that I hadn't developed) which 2http://richbrownesq-sqlserver.blogspot.com/2011/11/denali-now-gets-proper-name.htmlDenaliSQLsql 2012SQL Servernoreply@blogger.com (_RichardABrown)Sat, 26 Nov 2011 07:21:00 -0800tag:blogger.com,1999:blog-5070731366789101952.post-6392013287561763608Since I last blogged about Denali, Microsoft has announced the official name for this version of SQL Server to be....(drum roll). SQL2012. Ok, ok. I'm pretty underwhelmed too but hey ho. Project Crescent now has the rather grand title of Power View and Juneau has the slightly less impressive Data Tools. More exciting and interesting though are the changes to the Editions and also the licensing 0http://richbrownesq-sqlserver.blogspot.com/2011/11/admin-execute-folder-of-scripts.htmlAdminPowershellsqlcmdT-SQLnoreply@blogger.com (_RichardABrown)Fri, 18 Nov 2011 04:16:00 -0800tag:blogger.com,1999:blog-5070731366789101952.post-4419889605509766621Here's a simple powershell script to loop through a directory and execute the sql files contained within: ADD-pssnapin SqlServerCmdletSnapin100 $ServerName = "MySERVER"  $Db = "MyDB"  $scriptDir = "c:\FileDir\" $files = gci $scriptDir* -include *.sql foreach ($file IN $files)  {Invoke-Sqlcmd -ServerInstance $ServerName -DATABASE $Db -InputFile $File}0http://richbrownesq-sqlserver.blogspot.com/2011/11/ssrs-use-stored-procedures-in-datasets.htmlFunctionsRDLReportReporting ServicesReporting Services 2008SQLSQL ServerSSRSStored ProcedureT-SQLT-SQL Tuesdayudfnoreply@blogger.com (_RichardABrown)Tue, 8 Nov 2011 01:10:00 -0800tag:blogger.com,1999:blog-5070731366789101952.post-5795970666069255647 This is my contribution to T-SQL Tuesday #24 hosted by Brad Schulz (blog) on the subject of Prox ‘n’ Funx (Stored Procedures and Functions to you and me :-) ). I'm a big believer in using Stored Procedures (or at the very least, UDFs) for your Reporting Services datasets. and separating your presentation layer from your data layer and moving the SQL code away from the RDL. The benefits of 0http://richbrownesq-sqlserver.blogspot.com/2011/11/t-sql-why-have-null-bit-fields.htmlbitdata typesdatatypeDeveloperdevelopmentSQL 2008SQL ServerT-SQLnoreply@blogger.com (_RichardABrown)Thu, 3 Nov 2011 05:02:00 -0700tag:blogger.com,1999:blog-5070731366789101952.post-708367563948608464I was asked the question the other day, why SQL Server allows NULLs for the BIT datatype. What you need to bear in mind is what NULL represents - a value which is unknown - and, so there are 3 states which you need to cater for. True, False or Unknown. As is my wont, I like to explain things with analogies and this is no different. Consider a table holding job application details for a job 4http://richbrownesq-sqlserver.blogspot.com/2011/10/admin-day-of-day-series.htmlAdminBloggersDMVsextended eventsIntegration ServicesMythsSQL 2005SQL 2008SQL ServerSSISXEnoreply@blogger.com (_RichardABrown)Thu, 27 Oct 2011 01:11:00 -0700tag:blogger.com,1999:blog-5070731366789101952.post-513083665643286057There really are some incredible bloggers out there. Despite my recent 5 day series on Continuous Integration, I typically struggle to blog more frequently than once a week. Yet these guys have committed to a whole month of blogging on a single topic and the quality of the blogs isn't diluted at all giving a real "Deep Dive" into a topic. So here are some of the "A Day" series that i've dipped 0http://richbrownesq-sqlserver.blogspot.com/2011/10/admin-generate-restore-scripts.htmlAdminBackupdatabaseDRPowershellrecoveryrestoreSQLsql agentSQL2008tvfnoreply@blogger.com (_RichardABrown)Fri, 21 Oct 2011 00:30:00 -0700tag:blogger.com,1999:blog-5070731366789101952.post-7270738193787533646In order to speed up the recovery process of database (and to give the unintentional DBAs in my organsiation) a helping hand, I recently started experimenting with generating restore scripts for our production databases. As is my wont, my technology preference was again Powershell (although it did make use of T-SQL too) as looping through databases on a server and writing to file are all pretty 1http://richbrownesq-sqlserver.blogspot.com/2011/10/denali-ssis-loop-through-list-of.htmlDenalidtsexecute t-sqlfor each loopforeach loopIntegration Servicesiteratelooppackagescript taskSQLSQL ServerSSISVisual Studionoreply@blogger.com (_RichardABrown)Thu, 13 Oct 2011 00:42:00 -0700tag:blogger.com,1999:blog-5070731366789101952.post-4543964109063353116Its been tough to get any time recently to play with Denali, so I thought i'd put together a quick tutorial on SSIS using Denali to give people a tiny flavour of what it looks like. I intend (time permitting) to use this tutorial in the future to extend the package to show off a few of the new features of Denali. Note: this task can be achieved in previous versions of SSIS with minimal changes. 0http://richbrownesq-sqlserver.blogspot.com/2011/10/t-sql-helping-optimiser-can-hinder.htmlDeveloperdevelopmentfundamentalsJoinsquery planshowplanSQLSQL 2008SQL ServerT-SQLT-SQL TuesdayTSQL2SDaynoreply@blogger.com (_RichardABrown)Tue, 4 Oct 2011 03:02:00 -0700tag:blogger.com,1999:blog-5070731366789101952.post-678262551990545015 I've been meaning to blog about this for a while and noticing the (earlier than usual) T-SQL Tuesday #23 hosted by Stuart Ainsworth (blog | twitter) on the topic of Joins, I thought i'd cobble something together. Essentially, this post is about reading the results of SHOWPLAN_TEXT, being aware of the different physical join operators and also how the optimiser can be influenced (poorly) by a0http://richbrownesq-sqlserver.blogspot.com/2011/09/powershell-out-file-truncate-text.htmlAdminfilePowershellscriptSQLnoreply@blogger.com (_RichardABrown)Fri, 30 Sep 2011 00:00:00 -0700tag:blogger.com,1999:blog-5070731366789101952.post-8342139891941401413I was looking to use Powershell to write out some data from a SQL table using a combination of Invoke-Sqlcmd with out-file. Everything was going nicely until my output file suddenly started having data truncated. Something like: Invoke-Sqlcmd -Query "SELECT LongText FROM MyTable" -ServerInstance $sql | out-file -filepath $filepath Produced a file containing: LongText----------------This was 4