Depending on your needs, here are a couple of ways: IF EXISTS (SELECT * FROM TABLE WHERE COLUMN = ‘SOME VALUE’) –INSERT SOMETHING ELSE –INSERT SOMETHING ELSE Or a bit longer DECLARE @retVal int SELECT @retVal = COUNT(*) FROM TABLE WHERE COLUMN = ‘Some Value’ IF (@retVal > 0) BEGIN –INSERT SOMETHING END ELSE BEGIN … Read more
You need to create a UDF to convert the NVARCHAR data to bytes in UTF-8 Representation. Say it is called dbo.NCharToUTF8Binary then you can do: hashbytes(‘md5′, dbo.NCharToUTF8Binary(N’abc’, 1)) Here is a UDF which will do that: create function dbo.NCharToUTF8Binary(@txt NVARCHAR(max), @modified bit) returns varbinary(max) as begin — Note: This is not the fastest possible routine. … Read more
I needed this too. So I spent a day and here it is: The Code public class DbDescriptionUpdater<TContext> where TContext : System.Data.Entity.DbContext { public DbDescriptionUpdater(TContext context) { this.context = context; } Type contextType; TContext context; DbTransaction transaction; public void UpdateDatabaseDescriptions() { contextType = typeof(TContext); this.context = context; var props = contextType.GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public); transaction = … Read more
If you happen to be using Windows 8 and up, here’s how to get to it: The newer Microsoft SQL Server Configuration Manager is a snap-in for the Microsoft Management Console program. It is not a stand-alone program as used in the previous versions of Microsoft Windows operating systems. SQL Server Configuration Manager doesn’t appear … Read more
There are probably several different ways to do it, some uglier than others. Here’s one: (Note: dat = the string of characters) select *, substring(dat,1,charindex(‘-‘,dat)-1) as Section, substring(dat,charindex(‘-‘,dat)+1,charindex(‘-‘,dat)-1) as TownShip, reverse(substring(reverse(dat),0,charindex(‘-‘,reverse(dat)))) as myRange from myTable
You cannot expect any records to be outputted when using a GROUP BY clause, when no records exist in your source. If you want an output of 0 from the SUM and COUNT functions, then you should not use GROUP BY. The reason is that when you have no records, the GROUP BY clause has … Read more
Here’s how I solved it: Working SQL Fiddle First I have create a function which splits the string value i.e. ‘1,2,4,5’ Split function: CREATE FUNCTION fn_Split(@text varchar(8000), @delimiter varchar(20) = ‘ ‘) RETURNS @Strings TABLE ( position int IDENTITY PRIMARY KEY, value varchar(8000) ) AS BEGIN DECLARE @index int SET @index = -1 WHILE (LEN(@text) … Read more
This did the trick for me. http://thecodeattic.wordpress.com/category/ssrs/. Go down to step 35 to see the error you are getting. Paraphrasing: Once you’re able to log in to YourServer/Reports as an administrator, click Home in the top-right corner, then Folder Settings and New Role Assignment. Enter your user name and check a box for each role … Read more
You can’t refer to an alias outside of SELECT and ORDER BY because of the way a query is parsed. Typical workaround is to bury it in a derived table: SELECT FirstName, LastName, Other, Flag = CASE WHEN Other IS NOT NULL THEN 1 ELSE 0 END FROM ( SELECT FirstName, LastName, CASE WHEN LastName=”Jones” … Read more
What makes the cross apply query perform so poorly on this simple XML document, and perform exponentially slower as the dataset grows? It Is the use of the parent axis to get the attribute ID from the item node. It is this part of the query plan that is problematic. Notice the 423 rows coming … Read more