SQL 2012 Equiv of BACKUP LOG WITH TRUNCATE_ONLY

Your transaction log is full and you can no longer write to your database.  In earlier versions of SQL Server BACKUP LOG WITH NOLOG OR WITH TRUNCATE_ONLY allowed you to carve out the contents of the transaction log without dumping it to disk.  For SQL 2012, you will want to issue a command along the lines of the following example – you will lose transactions but you will clear out the log….


BACKUP LOG <databasename> TO DISK = 'NUL:'
 

In this scenario you can afford to lose transactions and simply want your database back online to do more development work.  If you cannot afford to lose data then first thing you may want to consider is backing up the tail.  For discussion around tail log backups read here … http://technet.microsoft.com/en-us/library/ms179314.aspx

 

SSIS – Dynamic Connection String Expression for your MDB Files

Lets assume for starters you have a bunch of MDB files, all the same structure, but different filenames. You want to use the same SSIS package to loop through and import each file into the database. Now we cover 5 steps to create a dynamic MDB file connection string inside your SSIS package.

Step 1 : Create your Data flow, stitch up your basic data flow between your OLEDB Data Source (this will prompt you to create a Microsoft Jet 4.0 OLEDB Connection Manager where you will specify the location of your MDB file), and create your preferred destination. Test to make sure this is working before making the MDB source file dynamic.

Step 2 : Create a variable called filename and set it to data type String. Either manually populate the value for variable “filename” (e.g. D:\Folder\Somefile.MDB) or populate this variable automatically using the For Each File Loop Enumerator.

Step 3 : Create a variable called mdbconnection and set it to data type String. Use the following expression to form a valid MDB connection string using your filename.

(DT_WSTR,12)"Data Source=" 
+ (DT_WSTR,100)@[User::filename] 
+ (DT_WSTR,34)";Provider=Microsoft.Jet.OLEDB.4.0;"

Step 4 : Go to the Properties of your OLEDB Connection manager of type “Native OLE DB\Microsoft Jet 4.0 OLE DB Provider”, set the Delay Validation Property to True, then expand Expressions, and create an expression for the “ConnectionString” property, setting this to

@[User::mdbconnection]

.

Step 5 : If the tablename within each MDB file stays the same your are in business otherwise, you will need to additional dynamically handle the OpenRowset property of the Source connection inside the Data Flow task.

Step 6 : Crack open a can of Michelob – job done !

Me Without You … Passing SSRS Report Parameters through a URL

Me without you is like a Report without Report Parameters …. 🙂

This post discusses how to pass parameters into a Sharepoint Reporting Services report through a formatted URL.  The following url example http://myspsite/subsite/_vti_bin/reportserver?http://myspsite/subsite/AdventureWorks/Employee_Sales_Summary.rdl&ReportMonth=3&ReportYear=2008 passes two parameters (ReportMonth and ReportYear) to a report (Employee_Sales_Summary.rdl).  We will break this example down using the url constructs.

This is the general formula …

rswebserviceurl
            ?reportpath
            [&prefix:param=value]…n]

And these are the components of the URL example broken down into digestable chunks …

Report Web Service URL (http://myspsite/subsite/_vti_bin/reportserver) – This is the URL of the Report Server (effectively the Sharepoint server)
Report Path (http://myspsite/subsite/AdventureWorks/Employee_Sales_Summary.rdl) – Passes the fully qualified URL of the location of the Reporting Services report in the Sharepoint document library
First Parameter (ReportMonth=3) – Passes the value 3 into Report parameter “ReportMonth”.  If for example the report is designed to accept a null for this parameter you would format this as ReportMonth=:isnull=true
Second Parameter (ReportYear=2008) – Passes the value 2008 into Report parameter “ReportYear”

If your parameters or dates, the approach is similar, however express your parameter value in the format mm/dd/yyyy for a date, and for a string just type in the value like you would a number.  For multi value parameters there is a good example here http://altecnotes.blogspot.com.au/2012/05/pass-multi-value-parameter-to-ssrs.html.

The following link goes through the basics…

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

And you can use the URL api to do a heap of other cool things, like export the report to a file without opening the report in the browser…

http://technet.microsoft.com/en-us/library/6a3b7fc3-3d91-4d12-8371-42ea12e74517 – e.g. The URL example http://myspsite/subsite/_vti_bin/reportserver?http://myspsite/subsite/myreport.rdl&rs:Format=PDF shows how to export the report myrereport.rdl to PDF.

Read more about other url functions here…

http://technet.microsoft.com/en-us/library/ms153586