TSQL – show all tables and columns with is nullable and is primary key

Here is a small query to help you get a list of all tables and columns in your database, showing whether each column is nullable or not, and showing whether each column is part of the primary key or not.

Enjoy!


SELECT col.TABLE_SCHEMA, col.TABLE_NAME, col.COLUMN_NAME, ORDINAL_POSITION,

        COLUMN_DEFAULT, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,

        NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,

        DATETIME_PRECISION, is_nullable,

       CASE WHEN keycol.primary_key = col.COLUMN_NAME THEN 'Yes' ELSE 'No' END AS is_primarykey

 FROM INFORMATION_SCHEMA.COLUMNS col

 LEFT JOIN

 (

select pk.TABLE_SCHEMA, pk.TABLE_NAME, column_name as 'primary_key'

from information_schema.table_constraints pk

      inner join information_schema.key_column_usage c on c.table_name = pk.table_name

            and c.constraint_name = pk.constraint_name

where constraint_type = 'primary key'

) keycol

      ON col.TABLE_SCHEMA = keycol.TABLE_SCHEMA

      AND col.TABLE_NAME = keycol.TABLE_NAME   

      AND col.COLUMN_NAME = keycol.primary_key

Self Service BI Navigation Menu in Sharepoint

Less than 10 easy steps to create a Sharepoint Self Service Business Intelligence Navigation menu like this…

SelfServiceBINavigation

A link to PowerPivot will open your PowerPivot Site, Dashboard Designer will open your Performance Point Dashboard Designer, and the third item in the menu is for the Report Builder 3.0 click once application.

First, open your site, go to Site Actions > Site Settings > then Under Look and Feel select Navigation.

SharepointNavigation

Under Global Navigation select these settings…

SharepointGlobalNavigation

Under Navigation and Sorting, select Global Navigation and select Add Heading…

Sharepoint Navigation and Sorting

Give the Heading a name…Self Service…

SharepointSelfServiceHeading

Now select the Self Service Heading and we are going to add 3 links for PowerPivot, Performance Point Dashboard Designer and Report Builder.  The PowerPivot link will already be there if you have created a PowerPivot site.  Simply select PowerPivot under Global Navigation and select Move Up or Move Down to position this item directly underneath the Self Service Heading.

SelfService3links

To Add a Dashboard Designer link, select Self Service then Select Add Link…

SharepointBIAddLink

Name the link “Dashboard Designer” and set the URL to be something like this (may vary for your Sharepoint site) e.g. if your BI site is referenced as /sites/BI/yoursite then try…

/sites/BI/yoursite/_layouts/ppswebparts/designerredirect.aspx

SharepointDashboardDesignerLink

Now add another link and name the link “Report Builder 3.0″…with the URL set to be something like this (may vary for your Sharepoint site) e.g. try this for start…

/_vti_bin/reportbuilder/ReportBuilder_3_0_0_0.application

SharepointReportBuilder

Now click OK on your Navigation screen MOST IMPORTANTLY !….Clicking OK at your Navigation screen will save all the changes you just made to the Navigation menu – otherwise all your good work will be lost! Scroll down until you see the heading Show and Hide ribbon – the OK button you need is below here!

SharepointNavigationOK

Now test each link in your new Global Navigation Self Service menu to make sure each link launches successfully…

TestingLinks

Thats it – a Self Service BI menu in Sharepoint in under 10 easy steps !

Location of RSReportServer.config for SSRS Web Service URL Sharepoint Integrated

You want to find the rsReportServer.config file for your SQL Server Reporting Services 2012 Installation.

Basically….for a Sharepoint Integrated Mode install of Reporting Services look in …

<Drive Letter>:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\WebServices\Reporting

Typically the location of your reportserver url is going to look something like this if your sharepoint web url is http://yourserver.com.au …


<a href="http://yourserver.com.au/_vti_bin/reportserver">http://yourserver.com.au/_vti_bin/reportserver</a>

and for a Native Mode Reporting Services installation look in …

<Drive Letter>:\Program Files\Microsoft SQL Server\MSRS1111.MSSQLSERVER\Reporting Services\ReportServer

 
http://msdn.microsoft.com/en-us/library/ms157273.aspx

Save Outlook Attachments to Disk

There are a number of cool easy to use plugins for Outlook that facilitate transfer of attachments out of emails automatically. Lets say you have a folder called “D:\SCRALADFolder” … and you want all the email attachments dumped in there – so in essence, an automated data feed.

You can go and download, try and purchase one of these Outlook add-ins … or …  

a DIY approach … you can add a module inside Outlook to strip out your attachments, then create a rule to execute this module.  Yes you have to write some code, but read on, its easy and can be done in a few minutes.

If you don’t know where to access your development environment in Outlook, try Alt+F11 or enable the Developer tab and click the Visual Basic button in the ribbon (see here)..https://popbi.wordpress.com/2013/07/05/how-to-access-the-developer-tab-in-excel-2013-visual-basic/.

After you have pasted the code below into your Visual basic module, create a rule and as a suggestion you could include the following options in your rule.
1. Apply this rule after the message arrives
2. Through the specified email account
3. Which has an attachment
4. Run a script (the module below)

saveAttachmenttoDisk(itm As Outlook.MailItem)
Dim objAtt As Outlook.Attachment
Dim SCRALADFolder
SCRALADFolder = "D:\YourSCRALADFolder"
For Each objAtt In itm.Attachments
objAtt.SaveAsFile SCRALADFolder & "\" & objAtt.DisplayName
Set objAtt = Nothing Next
End Sub

This will save the attachment as it is named in the email.  If you want to tack a datestamp on the filename you can format a datetime string then append it to the filename here in this line….

objAtt.SaveAsFile SCRALADFolder & "\" & saveDate & "_" & objAtt.DisplayName

How to Access the Developer tab in Excel 2013 – Visual Basic

Handy link outlining the procedure to open the Visual Basic Development environment inside Microsoft Excel 2013.   Alt + F11 may work for you on a PC but through virtual machines this can become problematic.   Try enabling the Developer tab for easy access through the menu bar.  Once the Developer tab is enabled, click on the Visual Basic button in the ribbon.  If a Module doesn’t exist create it as follows, then double click the module and start coding.  Possibilities are endless, but a classic use for this is stripping attachments out of your emails automatically as they arrive and onto a folder on disk.

Screen Shot 2013-07-05 at 8.54.55 AM

http://msdn.microsoft.com/en-us/library/office/ee814736(v=office.14).aspx

Visual Studio Devenv.exe hangs on open of SSIS Package

Visual Studio Devenv.exe hangs when you attempt to open a SSIS package. Make sure you have Delay Validation properties set to False, though if you are at this point and still not responding, that won’t help you. Try setting the max degree of parallelism on the SQL instance back to 1 (if it is already set to the default of 0).  No need to restart the instance, just try the code below to make settings effective.   You may need to kill devenv.exe though, then open your solution again, and try opening up your package again.

 
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'max degree of parallelism', 1
GO
RECONFIGURE WITH OVERRIDE;
GO