How to create empty guid in SQL Server – c# Guid.Empty() equivalent

In dotnet you can create a new Guid by using the static Guid class like this

(c# syntax)

Guid g = Guid.NewGuid();

And you can create an empty guid like this

Guid g = Guid.Empty;

In SQL Server you can create a new guid like this

DECLARE @g uniqueidentifier = newid()

select @g

But SQL Server does not have a function for creating an empty guid.

One way (a poor way) of achieving this is to create one from a string.

DECLARE @g uniqueidentifier = '00000000-0000-0000-0000-000000000000'

But if you get this wrong then an error will occur and its not easy to see why. E.g.

DECLARE @g uniqueidentifier = '00000000-0000-0000-0000-00000000000'

(hint: its got a 0 missing)

A safer way is to cast 0 to a binary and then the result to a uniqueidentifier

Declare @g uniqueidentifier = cast(cast(0 as binary) as uniqueidentifier)

SELECT @g

I hope this helps someone out there to produce safer, more reliable and readable code.

If you use this often and really want to make your code more readable then encapsulate this in a function.

 

-- =============================================
-- Author     : David Bridge
-- Create date: June 2014
-- Description: Returns an empty guid
-- =============================================
CREATE FUNCTION GuidEmpty()
RETURNS uniqueidentifier
AS
BEGIN
RETURN cast(cast(0 as binary) as uniqueidentifier)
END

Then use

select dbo.GuidEmpty()

Posted in Developer stuff, SQL Stuff | Tagged , , , , , | 1 Comment

MOTOROLA Universal Bluetooth Keyboard & Mouse – pc use – missing backslash

I bought a Motorola droid bluetooth keyboard which is supposed to be for a mobile phone or tablet (Xoom) but it has no backslash on it which is annoying when you connect it to a pc (which obviously you can or I would not be writing this. No really, I am using it to write this).

motorola bluetooth keyboard

Now this is no ordinary 103 key QUERTY keyboard. I wanted a small, black, pretty, and quality hideaway keyboard for a Windows 8 box that I use with a TV in the bedroom. Most of the time the pc gets used for NETFLIX but sometimes gaming and a bit of surfing. It’s plugged in to a 40 inch LED TV so it is kind of cool, especially when you factor in the specs. The pc is a tiny Gigabite Brix that is about 4 inches square and an inch tall with 16GB memory and 250GB SSD. Its a great gaming rig for Asphalt 8, ideal for Netflix and it boots into windows from cold in about 3 seconds. It shuts down in about the same time too.

Anyway, digression over. The keyboard is normally tucked away in a draw or under the telly so I wanted a small Bluetooth keyboard and mouse and opted for the Motorola Droid.

Its great but does not have a backslash. Here’s how to get one….

 

Find and hold “Alt Gr” to the right of the SPACE bar and click the # key (also has the pipe “|” character on it). That’s it!

Another top tip is the Euro symbol – €

To get this also use the “Alt Gr” key in combination with the $ key.

 

Now this may not be a SQL or code tip but if like me you like your gadgets and thing a dainty travel size but useful keyboard is a good idea then this could be something that will help you when you use that pc for something other than a bit of gaming. And its always good to know how to get the euro sign up isn’t it.

Dave

 

 

Posted in Hardware stuff, Other stuff | Tagged , , , , , , , , , , , | Leave a comment

SSRS report print button missing in IE11

If you use reporting services though a report viewer control then you might be missing the print button when viewing in IE11.

SSRS_NoPrintButton

To fix this, install the latest reportwiewer.exe.

This will return the print button but annoyingly it also changes the colour scheme and toolbar layout. It’s actually a nicer colour scheme but it might not fit with your page in both size and colour.

SSRS_NoPrintButton_restored

Direct download location=

http://www.microsoft.com/en-us/download/confirmation.aspx?id=35747

or if you don’t trust links, go to http://www.microsoft.com

and search for “MICROSOFT® REPORT VIEWER 2012 RUNTIME

Posted in Developer stuff, SQL Stuff | Tagged , , , , | Leave a comment

SSRS report hangs in IE11

If your SSRS report viewer’s “Loading” popup message never goes away (the report never renders) then chances are you have a reference to an image that does not exist in your report.

SSRS_Loading

I got this because I have a report template that I use for many customers and I like to show the customer’s log with an image with expression like

=Parameters!Customer.Value & ".jpg"

Which then requires that I copy a jpeg file called customername.jpg to my reports folder. If I did not have the logo, which was common for new customers then the logo was just blank but they Microsoft released IE11 and reports without a logo failed to render at all.

I have not found a way to fix this as there is no native IfFileExists() function (reports do not have access to the file system directly).

I tried updating the report viewer control to the latest version and this fixes other issues like the print button not working properly but it does not appear to fix this missing image issue.

You could move the jpegs to another place like a database or create a CLR extension but that’s a bit of overkill for a single browser issue (that shouldn’t be your responsibility to fix).

Workaround is therefore to simply make sure the file exists

Note:

The reports render fine in other browsers with the Report Viewer control!

The reports render fine when using SSRS directly without the Report Viewer control. So this is definitely a Report Viewer issue.

Posted in Developer stuff, SQL Stuff | Tagged , , , , , | Leave a comment

SSAS / Excel 2010 – An error was encountered in the transport layer

Connecting to SQL Server Analysis Services from a remote domain / login

When connecting to SSAS from a different remote domain / login using Excel 2010 where you have set up a windows user at the SSAS server, provided adequate permissions at the SSAS server to read the cube etc. but still cannot connect from a local Excel the issue is probably on account of the different domains / logins and a lack of trust between the servers.

All of your settings are correct, its probably just the windows being over cautious.

I found two workarounds for this

  • Create a local user with the same login details. Login as that user and then connect (not ideal)
  • Connect using the runas command and include the /netonly switch

A little more info on the second method….

  1. Navigate to excel in Windows explorer (“C:\Program Files (x86)\Microsoft Office\Office14″)
  2. hold down the right shift key then right click the explorer window and choose Open command window here
  3. now paste this in to the window “runas /netonly /user:REMOTEDOMAIN\USERNAME EXCEL
  4. You will be prompted for a password. Enter it and press the ENTER key
  5. Excel will load.

You will now be able to connect to the Analysis Services on the remote machine with the same details as your excel session will be trusted.

If this has fixed your problem you might like to create a shortcut.

Simply create a standard shortcut to EXCEL and then edit the command to this

C:\Windows\System32\runas.exe /netonly /user:DOMAIN\USER "C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE"

 

NOTE: This also works for other programs exhibiting the same behaviour.
The reason this works is that you are allowing the remote server to trust your connection. Obviously you will still need all of the appropriate permissions and ports open etc. so this article has assumed that you have done this already.

Posted in Developer stuff, SQL Stuff | Tagged , , , , , , | 3 Comments

The SQL Server instance specified in SSIS service configuration is not present or is not available

If you get this error when you are trying to expand the MSDB section in SSIS and your target SQL Server is clustered then this is probably because the default reference to an instance of SQL Server in the SSIS configuration file is pointing at “.” (which is the alias for local server). As the locally installed instance of sql server is only a node of a cluster and not a standalone sql server then “.” is not a valid server name.

To get a “.” server name you would have to be on the cluster, not the node machine

Simply edit the config file for SSIS and enter the name of the cluster instead.

e.g.

The file is at C:\Program Files\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.ini.xml

and contains (by default)

<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
<TopLevelFolders>
<Folder xsi:type="SqlServerFolder">
<Name>MSDB</Name>
<ServerName>.</ServerName>
</Folder>
<Folder xsi:type="FileSystemFolder">
<Name>File System</Name>
<StorePath>..\Packages</StorePath>
</Folder>
</TopLevelFolders>
</DtsServiceConfiguration>

Change the <ServerName>.</ServerName> bit, replacing . with the cluster name.

You will now have to restart the SSIS service to force it to read the new configuration file. NOTE THAT YOU DO NOT HAVE TO RESTART THE BOX OR SQL SERVER! JUST SSIS.

Note: To edit the config file you may need to use an editor in administrator mode depending on the version of windows. Open notepad (or whatever) using right click and Run as Administrator then browse for the file.
I recommend turning off the hide extensions for known types. If you want to do this then use Windows explorer, press left ALT and F then on Tools menu choose Folder options to search for this option.

Sadly the rest of the information presented by the error message is a tad misleading but it is factually correct as the SQL Server really is not found (there is no local sql server in a cluster node)…


ADDITIONAL INFORMATION:

This might occur when there is no default instance of SQL Server on the computer.

Login timeout expired
A network-related or instance-specific error has occurred while establishing a connection to SQL Server.
Server is not found or not accessible.
Check if instance name is correct and if SQL Server is configured to allow remote connections.
For more information see SQL Server Books Online.
Named Pipes Provider: Could not open a connection to SQL Server. (MsDtsSrvr)

Regards

Dave

Posted in Developer stuff, SQL Stuff | Tagged , , , , | 3 Comments

Attaching the Script debugger to process ‘[5836] iexplore.exe’ on machine ” failed. A debugger is already attached.

If you get this error message when running a c# application in Visual Studio 2010 by pressing F5 (Start Debugging) then the chances are you have multiple browsers installed and this has caused problems with the debugger.

—————————
Microsoft Visual Studio
—————————
Attaching the Script debugger to process ‘[5836] iexplore.exe’ on machine ‘machinename’ failed. A debugger is already attached.
—————————
OK
—————————

Debugging still works but you have to cancel the annoying message in Visual Studio every time.

To get rid of the error message simple close all browsers (leave VS 2010 running if you like) and then in a command window with admin privilege, simply enter the following command then continue debugging as you were before the problem started. Simple!

regsvr32.exe "%ProgramFiles(x86)%\Common Files\Microsoft Shared\VS7Debug\msdbg2.dll"

Posted in Developer stuff | Leave a comment