SQL References for all Databases

So I’m working on migrating to a new instance of SQL and thought wouldn’t it be nice to see where all of my databases are referencing.

I had a quick look on Google, as you do, and found sys.sql_expression_dependencies, I thought this is great I can see all of the references for the current database.

After trying this out for a bit I then thought I should see if I could run this against each of my databases, this turned out to be easier than I thought by using sp_MSforeachdb, the only problem was that it returns multiple result sets not one long list.

After a bit of playing I came up with the following:

DECLARE @command varchar(1000)

DECLARE @RefInfo TABLE(
	[Database Name] NVARCHAR(255), 
	[Referencing Object] NVARCHAR(255), 
	[Referenced Database] NVARCHAR(255), 
	[Referenced Schema] NVARCHAR(255), 
	[Referenced Entity] NVARCHAR(255)
	)

SELECT @command = 'USE [?] 
SELECT  
	''?'' AS [Database Name], 
	OBJECT_NAME (referencing_id) AS referencing_object, 
	referenced_database_name, 
	referenced_schema_name, 
	referenced_entity_name 
FROM sys.sql_expression_dependencies 
WHERE referenced_database_name IS NOT NULL 
AND referenced_database_name NOT IN (
	''?'',
	''msdb'',
	''master''
	)
AND is_ambiguous = 0
AND ''?'' NOT IN(
	''master'',
	''msdb''
	)
' 

INSERT INTO @RefInfo EXEC sp_MSforeachdb @command

SELECT * FROM @RefInfo

This inserts all of the results into a single table variable, which is nice, and excludes any references to the master, msdb and the containing database.

Hope you get some use out of it.

Today was a sad day #!

Recently I have been using #! Crunchbang as my main OS, mostly because I use old equipment as anything from the last 7 years is suitable for most of my needs.

I was sad to hear that the sole developer of #! has decided to call it a day (http://crunchbang.org/forums/viewtopic.php?pid=416493#p416493). Good luck to him though.

I suppose I will have to decide on what I’m going to use going forward or whether I try to do my own thing.

Building a timer using jQuery

Ok, so another week and another post.

This week I will give you an example of how to create a timer using some of the methods from last weeks post.

The idea here is to use jQuery to build a timer, which is pretty much the same as the clock, however we will be adding some buttons to allow you to start/resume, pause & stop the timer.

The plan going forward is to use this as part of a time logging application, there are some parts of this code, such as the id parameter in the start function, which will become clearer as we move on.

First of all we will start by adding a new element to the body of our html document from last week, just stick it between the body tags:

<div class="timer">Timer</div>

The next thing we are going to do is add a script reference in the head of our document, just as we did last week, with a reference to a new script file:

<script src="timer.js"></script>

Lastly we need to create our script file, copy the code below and save it as timer.js:

$(document).ready(function(){
	
	//Build a point span element, so it can be re-used.
	var timerPoint = $('<span>').addClass("point").text(':');
	
	//Find the timer element
	var timerElem = $('.timer');
	//Clear the timer element
	timerElem.empty();
	//Build up the contents of the timer element using jQuery
	timerElem.append(
		$('<p>').addClass('time').append(
			$('<span>').addClass("hour")
		).append(
			timerPoint.clone()
		).append(
			$('<span>').addClass("minute")
		).append(
			timerPoint.clone()
		).append(
			$('<span>').addClass("second")
		).append(
			timerPoint.clone().text('.')
		).append(
			$('<span>').addClass("millisecond")
		)
		);
	
	//Create an object to contain information for the timer
	var timerObj = [];
	timerObj.currentDuration = 0;
	timerObj.originalStart = null;
	timerObj.currentStart = null;
	timerObj.currentId = null;
	timerObj.state = 0;
	
	//Create an anonymous function which will handle updating the text of the provided elements
	//target is the element we want to update
	//value is the numeric value we want to use
	//padding is a pattern we want to use to pad the value
	updateValue = (function(target, value, padding){

		value = padding + value.toString();
		value = value.substr(value.length - padding.length);
		
		if(target.text()!=value){
			
			target.text(value);//.hide().fadeIn('slow');
		}
	});
	
	//Add a function to the timer which will handle the start button event
	timerObj.start = function(id){
			
			this.currentId = id;
			
			if (timerObj.state == 0){
				timerObj.originalStart = new Date();
				timerObj.currentStart = timerObj.originalStart;
				timerObj.currentDuration = 0;
				timerObj.state = 1;
			} else {
				timerObj.currentStart = new Date();
			}
			
			timerObj.tick = function(){return setInterval(
				function(){
					var now = new Date();
					var duration = new Date((now - timerObj.currentStart)+ timerObj.currentDuration);
					
					updateValue($('.hour',timerElem),duration.getHours(),'00');
					updateValue($('.minute',timerElem),duration.getMinutes(),'00');
					updateValue($('.second',timerElem),duration.getSeconds(),'00');
					updateValue($('.millisecond',timerElem),duration.getMilliseconds(),'000');
					$('.point',timerElem).fadeOut(500).fadeIn(400);
				}
			,100);}();
			
		};
	
	//Add a function which will handle the pause button event
	timerObj.pause = function(){
			timerObj.state = 2;
			var now = new Date();
			timerObj.currentDuration = ((now.valueOf() - timerObj.currentStart.valueOf()) + timerObj.currentDuration.valueOf());
			clearInterval(timerObj.tick);
		};
	
	//Add a function which will handle the stop button event
	timerObj.stop = function(){
			timerObj.state = 0;
			var now = new Date();
			timerObj.currentDuration = ((now - timerObj.currentStart)+ timerObj.currentDuration);
			clearInterval(timerObj.tick);
		};
	
	//Add the buttons to the timer
	timerElem.append(
		$('<p>').addClass('controls').append(
			$('<button/>',{text: 'Start', click: function(){timerObj.start();}})
		).append(
			$('<button/>',{text: 'Pause', click: function(){timerObj.pause();}})
		).append(
			$('<button/>',{text: 'Stop', click: function(){timerObj.stop();}})
		)
		);
	
});

Building a simple clock in jQuery

Here is a quick example of how to build up an element using jQuery. I’m going to presume you know a bit about HTML and JavaScript, so i’m not going to bore you with all of that. You can always visit w3schools.com, which is a good reference for HTML, JavaScript, CSS …

To start off we create a very simple HTML document, copy the code below and save it as index.html:

<!doctype html>
<html lang="en">
	<head>
		<meta charset="utf-8">
		<title>Clock Demo</title>
		<script src="http://code.jquery.com/jquery-1.10.2.js"></script><!-- This is a link to a hosted jQuery library -->
		<script src="clock.js"></script><!-- This is our JavaScript file -->
	</head>
	<body>
		<!-- This div is the one we are going to use to hold our clock -->
		<div class="clock">Clock</div>
	</body>
</html>

Notice the two <script> tags, one references a hosted version of the jQuery library we are going to use, the other references an external file which we will use to create our clock.

Also notice the <div> element with a class of clock, we will use this in our clock.js JavaScript file to change the element into our clock.

Now we will create our JavaScript file, copy the code below and save it as clock.js:

//When the document is ready call this anonymous function
$(document).ready(function(){

	//Find the clock element
	var clockElem = $('.clock');
	
	//Write the content of the element directly
	//clockElem.html('<div class="time"><span class="hour"/><span class="point">:</span><span class="minute"/><span class="point">:</span><span class="second"/></div>');
	
	//Build the content of the element using jQuery
	//Empty the clock element.
	clockElem.empty();
	//Build a point span element, so it can be re-used.
	var clockPoint = $('<span>').addClass("point").text(':');
	//Append all of the required elements to the clock element.
	clockElem.append(
			$('<span>').addClass("hour")
		).append(
			clockPoint.clone()
		).append(
			$('<span>').addClass("minute")
		).append(
			clockPoint.clone()
		).append(
			$('<span>').addClass("second")
		);
	
	//Function object, this is used to hold one or more functions to be used within this function.
	var clockFunc = [];
	//A function to pad a value with additional 0 if required, and update the target if the value is different
	clockFunc.updateValue = (function(target, value){
		if(value < 10) {
			value = '0' + value;
		}
		if(target.text()!=value){	
			target.text(value).hide().fadeIn('slow');
		}
	});

	//We return a function which is repeated at a 1 second interval
	return setInterval(
		function(){
			var now = new Date(); //Get the current date & time
			clockFunc.updateValue($('.hour',clockElem),now.getHours()); //Update the hour value
			clockFunc.updateValue($('.minute',clockElem),now.getMinutes()); //Update the minute value
			clockFunc.updateValue($('.second',clockElem),now.getSeconds()); //Update the second value
			$('.point',clockElem).hide().fadeIn('fast'); //Flash the separators
		}	
	,1000);
	
});

What we are doing in this file is calling an anonymous (unnamed) function when the document is in a ready state (i.e. the document structure is loaded). The function locates our <div> using the clock class.

We then build the content of the <div> element, either directly with HTML or by building the content.

After we have built the structure of the clock we then need to update it every second. To accomplish this we create another anonymous function which updates the contents of the clock. To do this we create a helper function updateValue that we can re-use to format the values, I have added it to an object but you could just declare it as a variable without the need to create the containing object.

I hope this makes at least a bit of sense, this post is a bit of a rush job so I will probably come back and edit it over the next day or so.

2015

In 2014 I only published 1 post, which to be honest is a lot less than I intended. I have written other draft posts but never got round to finishing them. I don’t do this full-time, only as a way to record things that I have done. Although, I am doing more than one bit of work in a whole year!

This year, 2015, my intent to publish a few series of posts covering the stuff I am interested in and trying to explain them. Hopefully this will help other people, it will help me to solidify things in my own mind as I can’t explain to others that which I don’t understand myself.

Some of the topics I am considering on covering are:

  • Building an HTML5 application (HTML, CSS, JavaScript/jQuery, PHP)
  • Building a C# application
  • Building an Android application
  • Working with Linux, coming from a Windows background

My plan is to publish at least 1 post a week, I will try to keep them short and concise but provide enough information to be helpful to those who need it. I will also be going back over the things I have done over the last year or so and post any content that I deem useful, so keep an eye out for that as well.

That’s the first post of the year done, time to start writing the other 51+.

CRM 4.0 – User unable to access Filtered views

This is one that stumped me for a little while.

I was trying to figure out why one of my users was unable to run an Excel macro which read information from our CRM database. The user has a CRM account with all of the nessesary permissions but was unable to view any information from any Filtered views.

I figured out that it wasn’t an issue with the Roles in CRM, or the Security group used by CRM, or the security on the views themselves but actually it was an entry in the dbo.SystemUserBase table in the CRM database.

The user used to have an apostrophe (‘ “single quote”) in their username in AD when they first joined, this was subsequently removed in their AD account but in the dbo.SystemUserBase table the apostrophe was still there.

When a Filtered View is queried it checks the dbo.SystemUserBase table and tries to match it to the current users domain name, provided by the SUSER_NAME() function. If the values do not match then no records will be returned in the view.

The fix is to edit the value in the DomainName field in the dbo.SystemUserBase table to reflect the users domain name in AD.

VLC transcode script

Did you know that if you have ever wanted to transcode a load of video in one go and wanted to do it using the command line you can use VLC.

To set it up complete the following steps:
Download the Zip version of VLC.

Setup the folder structure, run the following from the command window whilst in the root folder.

mkdir in
mkdir processed
mkdir out
mkdir vlc

Extract the contents of the downloaded Zip file into the VLC folder.

Copy and paste the following code into notepad and save as transcode.bat in the root folder:

@ECHO off
FOR %%a IN (in\*.mkv) DO (
	ECHO Transcoding "%%a" to "out\%%~na.mp4"
	"vlc\vlc.exe" -I dummy -vvv "%%a" --sout=#transcode{vcodec=h264,acodec=mpga,ab=128,channels=2}:standard{access=file,mux=mp4,dst="out\%%~na.mp4"} --file-caching=300 vlc://quit
	ECHO Moving "%%a" to "processed\%%~nxa"
	MOVE "%%a" "processed\%%~nxa"
	ECHO ----------------------------------------
	)
PAUSE

Drop your files, in the case above *.mkv, into the in folder and then double click on the transcode.bat file to transcode them.

To find out how to transcode to different formats have a look at the command-line help on the VideoLAN website.

%d bloggers like this: