Author Archives: Matt

2toria is moving to a new host

Apologies if anything seems broken whilst I’m moving things about…

SSRS: Remove the toolbar from an embedded SSRS report

If you embed an SSRS report on your page in an iframe using the Report Server URL, you’ll get the toolbar included:-

toolbar

To get rid of it, add the following to the end of your URL:-

&rc:Toolbar=false

…and the toolbar will disappear:-

toolbargone

Really useful if you want to embed a report containing just a chart or a simple table without the unnecessary cruft.

T-SQL: Spaces in the ISNUMERIC function

If you’ve ever used the ISNUMERIC function in T-SQL, you’ll know that it returns a 1 if a value passed to it can be evaluated as numeric, and a 0 if not.  For example:-

ISNUMERIC(123) --> 1
ISNUMERIC('123') --> 1
ISNUMERIC('abc') --> 0
ISNUMERIC('123.456') --> 1
ISNUMERIC('12/24') --> 0 (even though it looks like a fraction, it isn't)

What might surprise you then is that…

ISNUMERIC('         123     ')

….evaluates to 1, and is therefore considered numeric, which means that spaces before and after the value are ignored.  If there are spaces inbetween numbers however, you’ll get a return of 0:-

ISNUMERIC('123 456') --> 0

Worth keeping in mind….one of those things that might catch you out if you’re not aware of it.

SSMS: Script a table and its rows

SQL Server Management Studio provides the means to not only create a table to a script editor window, but to script its rows.

This tip is really handy if you’ve ever needed to copy a table from one database to another, or maybe if you’ve needed to script both a table and its rows to the DDL/Schema part of an SQLFiddle.

Here’s how:-

  1. Right click on your database in Object Explorer, and choose Tasks > Generate Scripts.Step1
  2. From the dialog, choose ‘Select specific database objects, expand the tables view and put a check in the table (or tables) you want to script.  Click Next when done.
    Step2
  3. Select ‘Save to new query window’, and then click the ‘Advanced’ button in the top right hand corner.
    Step3
  4. There’s a lot of options here that I might cover at another time, but for now you just need to find the ‘Types of data to script’ line (which is by default set to ‘Schema only’), and set it to ‘Schema and Data’ (this will ensure you get the DDL for the table as well as the insert statements you need to populate it.
    Step4
  5. Now click OK, Next and Next again and wait for the wizard to generate your script.
    Step5
  6. When done, the full script should be available in your Query Editor window.

Handy stuff, isn’t it?  Like I said, there are a load more options in the ‘Advanced’ dialog (such as choosing whether you want to add DROP table statements, extended properties, primary keys etc).  I’ll perhaps cover these and what they do in my next post, but why not have a click around and see what they do yourself?