richbrownesq-sqlserver.blogspot.com
SQL Stuff: March 2011
http://richbrownesq-sqlserver.blogspot.com/2011_03_01_archive.html
Every day is a school day. Tuesday, 29 March 2011. SSMS: Beware of Modify. When you want to change the defintion of a sql programmability object in SQL2008 eg Trigger, Stored Procedure or Function using SQL Server Management Studio, you get to options when right clicking the object, Modify or Script.ALTER to. Friday, 25 March 2011. T-SQL: Table Valued Parameters - "adios" to the Split function. For editions up to SQL2005, the arrival in SQL2008 gave us Table Valued Parameters. Here is an example using a ...
richbrownesq-sqlserver.blogspot.com
SQL Stuff: July 2011
http://richbrownesq-sqlserver.blogspot.com/2011_07_01_archive.html
Every day is a school day. Thursday, 28 July 2011. SSRS: Link to report URL directly. In writing a quick and simple app the other day, I had a requirement to link directly to a report hosted on a Reporting Services instance. The tricky part was working with directories and parameters for the report. It was for a report called "Product Detail" in a directory called "Product Reports" taking a single parameter called ProductID. The following seemed to work. Thursday, 21 July 2011. We'll need to configure da...
richbrownesq-sqlserver.blogspot.com
SQL Stuff: T-SQL: Allow low privileged user to Truncate table
http://richbrownesq-sqlserver.blogspot.com/2012/09/t-sql-allow-low-privileged-user-to.html
Every day is a school day. Monday, 17 September 2012. T-SQL: Allow low privileged user to Truncate table. The 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. An alternative is to make use of the EXECUTE AS.
richbrownesq-sqlserver.blogspot.com
SQL Stuff: May 2011
http://richbrownesq-sqlserver.blogspot.com/2011_05_01_archive.html
Every day is a school day. Tuesday, 31 May 2011. Admin: Change drive and directory in CMD. Ok, so its not SQL based but one of the things I ALWAYS forget is the syntax to move quickly to a directory on a different drive when using the command prompt. If i'm on c: Dog. And want to move to d: Cat. Then simply issuing the following command doesn't cut the mustard:. C: Dog cd d: Cat. Instead, I need to issue it with the /D command. C: Dog cd /D d: Cat. Friday, 27 May 2011. Why I Love Conflict! It's important...
richbrownesq-sqlserver.blogspot.com
SQL Stuff: May 2012
http://richbrownesq-sqlserver.blogspot.com/2012_05_01_archive.html
Every day is a school day. Tuesday, 1 May 2012. T-SQL: Change Schema of User Defined Type. I'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. NewSchema TRANSFER OldSchema.MyUDT. SFTW) SQL Server Link...
richbrownesq-sqlserver.blogspot.com
SQL Stuff: IIS: IE doesn't auto authenticate
http://richbrownesq-sqlserver.blogspot.com/2012/09/iis-ie-doesnt-auto-authenticate.html
Every day is a school day. Monday, 17 September 2012. IIS: IE doesn't auto authenticate. I'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 auto authentication.
richbrownesq-sqlserver.blogspot.com
SQL Stuff: April 2011
http://richbrownesq-sqlserver.blogspot.com/2011_04_01_archive.html
Every day is a school day. Thursday, 28 April 2011. Admin: Sizing your transaction log. You will often hear DBA folk telling you how you need to size your transaction log appropriately and, like data files, avoid shrinking it. Why is this? Well, lets try using a finance theme with a Credit Card analogy which records all your purchases but it also has a limit. Thursday, 21 April 2011. SSRS: Using SSIS as Data Source - a gotcha. The issue I came across was due to me having SQL2005 on my machine previous to...
richbrownesq-sqlserver.blogspot.com
SQL Stuff: February 2012
http://richbrownesq-sqlserver.blogspot.com/2012_02_01_archive.html
Every day is a school day. Wednesday, 15 February 2012. T-SQL: Get Last Day of Month. One 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:. However, we can now just use the inbuilt function EOMONTH. Thursday, 9 February 2012. Table...
richbrownesq-sqlserver.blogspot.com
SQL Stuff: September 2012
http://richbrownesq-sqlserver.blogspot.com/2012_09_01_archive.html
Every day is a school day. Monday, 17 September 2012. IIS: IE doesn't auto authenticate. I'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 auto authentication.
richbrownesq-sqlserver.blogspot.com
SQL Stuff: December 2011
http://richbrownesq-sqlserver.blogspot.com/2011_12_01_archive.html
Every day is a school day. Thursday, 8 December 2011. Admin: Windows Group Login Ambiguity. Its 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. However, this works because t...
