SQL: How to get depth from a recursive cte

If you need to know what level deep you are when recursing through a series of parent child relationships, there is a pretty easy way.

DECLARE @Folders TABLE ( ID INT NOT NULL, FolderTypeLID INT NOT NULL, Depth INT NOT NULL,SortOrder INT NOT NULL );
WITH cteFolder( FolderID,Depth )
AS
(
SELECT @topFolderID,Depth = 0
UNION ALL
SELECT f.ID, Depth +1
FROM Folder f
JOIN cteFolder cte
ON f.ParentID = cte.FolderID
)

 

Happy Coding!

SQL: How to include an incrementing column in your query without a cursor or loop

I had the need to do a recursive call that included depth and sort orders. I wanted to reformat the sort orders in a single query without using a cursor or loop.

Turns out there is an easy way with SQL 2005 or higher.

@Folders is a table var from my CTE with Depth and SortOrder in it.

SELECT ID,(ROW_NUMBER() OVER (ORDER BY f.Depth,f.SortOrder,r.SortOrder)) * 100 as SortOrder
FROM someTable r
JOIN @Folders f
ON r.FolderID = f.ID
ORDER BY SortOrder;

Happy Coding!

I can’t drop a database user: The user owns one or more databases

This was frustrating me a bit, as I wish SQL Management Studio would just prompt me to remove myself as owner from those db’s and let me drop. Nope. It doesn’t.

So, what to do? Well, I used management studio to select another one of my accounts. I get the awesome message of “The proposed new database owner is already a user or aliased in the database.”. So, no go there.

I then realized I never mapped SA to these db. Nice and simple. Assign each db owner to SA, then your done.

Or run this script and keep changing the db name. Probably could automate this more by querying the db’s and looping.

 

alter authorization on DATABASE::[MyDBName] to [SA]

Hope this helps someone.

Happy Coding!

How to fix SqlError: FILESTREAM feature is disabled.

I was trying to restore a database and got the error “System.Data.SqlClient.SqlError: FILESTREAM feature is disabled. (Microsoft.SqlServer.Smo) from the Restore Wizard.

This baffled me a bit as I know I went into the DB Server setting in Sql Manager and specifically set that value to Allow it. Well, after smacking the computer around a bit, she finally confessed that there is another place you have to enable that setting. Go figure.

You need to go to the Sql Configuration Manager (Not SQL Server Management Studio) and also enable the settings.

 

Here is the original error:

image

Here is me checking the settings on the DB:

Look, I think it’s set:

image

 

But really, no. Check the Running Values to see it laugh at you.

image

 

So, resolution? Go to Sql Configuration Manager. Open the properties window of your Server.

image

Check the boxes. Hit OK. Now right click your server instance and hit Restart. Watch the progress bar and ponder great thoughts.

Now, go to SQL Management Studio.

Connect to your server and run this simple command.

EXEC sp_configure filestream_access_level, 2

RECONFIGURE

 

Now restart your Sql instance once more. You are done. Repeat whatever miserable operation you were doing before you got this error and repeat to yourself. “When I design software, I will never make someone set an option in two programs ever….ever….never ever…”

 

Happy Coding!

SQL Script to extract address information into individual fields

I had a need to write a script or a program that would extract an address that is stored in one field and break it out into its parts. Address, Address2, City, State and Postal Code(zip code).

It’s a real bugger to do in a single SQL statement, I had one but it was really really ugly. I mean, wing man, take one for the team, dating the hot girls friend kind of ugly.

So, I made this simple script that you can either run outright to convert your data or you can easily modify this into a procedure.

Background:

I created the new fields on the original table and I also added a field I called SiteAddressBackup. I did an update into this field before running this script in order run against real live data and still have it safe and sound if I mucked things up. I suggest you do the same to preserve your data.

The script. Should be easy to follow.


--Seekford script to extract addresses
--Tested on 2100 real addresses from early production db
--examples
--1234 GREENVIEW DR SW,SUITE # 222,ROCHESTER, MN 55902-1080
--1555 SAM NUNN BLVD,PERRY, GA 31069-1954
   
DECLARE AddressUpdate CURSOR FOR         
SELECT MyOrdersID,SIteAddress
FROM tMyOrders
ORDER BY MyOrdersID
        
DECLARE @ID INT        
DECLARE @SiteAddress VARCHAR(255)        

update tMyOrders set SiteAddress = SiteAddressBackup
        
OPEN AddressUpdate        
        
FETCH NEXT FROM AddressUpdate         
INTO @ID, @SiteAddress
        
WHILE @@FETCH_STATUS = 0        
BEGIN  

	
declare @Addr as varchar(100)
declare @Addr2 as varchar(100)
declare @City as varchar(100)
declare @State as varchar(100)
declare @Postal as varchar(100)
declare @Temp as varchar(100)

--extract the postal and state
set @Temp = rtrim(ltrim(SUBSTRING(@SiteAddress,( LEN(@SiteAddress) - CHARINDEX(',', REVERSE(@SiteAddress), 1)) +2 ,100)))
set @Postal = SUBSTRING(@Temp,( LEN(@Temp) - CHARINDEX(' ', REVERSE(@Temp), 1)) +2 ,100)
set @State = SUBSTRING(rtrim(ltrim(@Temp)),0,( LEN(@Temp) - CHARINDEX(' ', REVERSE(@Temp), 1))+1)

--extract the city )pulls everything but the state and zip
set @Temp = SUBSTRING(@SiteAddress,0,( LEN(@SiteAddress) - CHARINDEX(',', REVERSE(@SiteAddress), 1))+1  )

set @City = SUBSTRING(@Temp,( LEN(@Temp) - CHARINDEX(',', REVERSE(@Temp), 1)) +2 ,100)

--reparse
set @Temp = SUBSTRING(@temp,0,len(@temp) - len(@city) )
set @Addr2 = SUBSTRING(@Temp,( LEN(@Temp) - CHARINDEX(',', REVERSE(@Temp), 1)) +2 ,100)

if(LEN(@Addr2) =0) 
begin
set @Addr = SUBSTRING(@temp,0,len(@temp) - len(@Addr2)+1 )
end
else
begin
set @Addr = SUBSTRING(@temp,0,len(@temp) - len(@Addr2) )
end


update tMyOrders set SiteAddress = @Addr,siteaddress2=@Addr2,SiteCity = @City,SiteState = @State , SitePostalCode = @Postal
where MyOrdersID = @ID 
     
FETCH NEXT FROM AddressUpdate         
INTO @ID, @SiteAddress
 
END        
        
CLOSE AddressUpdate        
DEALLOCATE AddressUpdate   

--Query to show them broken out against the original
SELECT [MyOrdersID]      ,
SiteAddress,SiteAddress2,SiteCity,SiteState,SitePostalCode  ,siteaddressbackup   
  FROM [tMyOrders]
 

Value is not valid for OracleType when setting to 0 in .NET…..

So you thought you could do a an easy new OracleParameter(“p_id”, 0) and have it work, right? Well, that would be too easy.

If you do that, you get a nice nastygram from the class:.

'new OracleParameter("p_id", 0)' threw an exception of type 'System.ArgumentOutOfRangeException'
 base {System.ArgumentException}: {"Value is not valid for OracleType: 0.rnParameter name: oracleType"}
 ActualValue: null
Message: "Value is not valid for OracleType: 0.rnParameter name: oracleType"

So, how do you fix this? Easy enough, but goofy.

int zero = 0;
var param= new OracleParameter("p_ID",zero);

This works quite well. Oh yeah, casting 0 to int doesn’t work either. I know you were thinking about it. (int)0.

Update a table from a Select query

I had to update a table with missing product id’s using the item master table to do the correlation. This led to having to formulate a lovely SQL query that would let me update the table based on matching criteria. In effect, I knew the SKU in both records should match, so I could correlate them via that data point. This SQL can easily be adapted to work with your scenario. I used this against a SQL Server 2005 database.


update torderdetails set torderdetails.productid = tproducts.productid
from torderdetails join tproducts on tproducts.partnumber = torderdetails.partnumber where torderdetails.productid =0

Hopefully this helps someone out their with the headaches of these rare tasks.

How to delete those pesky duplicate SQL records…

So every once in a while I find that I have some duplicate records in my database as a result of testing or development or plain oversight. How to remedy the situation?

Well, its as easy as this simple SQL.

delete sourceTable
from YOURTABLENAME sourceTable, YOURTABLENAME compareTable
where sourceTable.fieldWithDuplicatesToMatch= compareTable.fieldWithDuplicatesToMatch
and sourceTable.PrimaryKey > compareTable.PrimaryKey

Basically, replace YOURTABLENAME with the name of your table.

Replace fieldWithDuplicatesToMatch with the field on the table that has the duplicates. Such as SKU or Email.

Replace the PrimaryKey with the table PrimaryKey or a time stamp (DateTime) field or whatever you want to use as the basis for which record to keep.

Voila! Duplicates be gone.

.NET Illuminator v1 Alpha Released to the wild

So….I finally decided to release my alpha version to all you hooligans who want to see whats under the hood of fellow .NET’ers applications. You can use .NET Illuminator to see all the UI objects in another .NET application and even manipulate them. Yes, pretend like you are in Visual Studios Form Designer and just start viewing and changing properties.

You can also view all connection strings the application has used to any SQL  servers.

I will post more when I add more.  Happy hacking!

Click here for .NET Illuminator Page

Let’s all jump into the pool..The SQL Connection pool that is..

I was out swimming in the data pool and I started thinking to myself, how many people are sharing the same pool without me even knowing they are there? How did I even get into the pool? And why is the water so cold?

Well, if you are using ADO.NET with SqlConnection , you are playing in the pool to. ADO.NET is a pretty smart animal. It realizes that if you are going to be connecting to a database a lot, then its probably a good idea to keep the connection to the database server open awhile. It does this behind the scenes, so when you create multiple data connections, it only really grabs one from the available pool of connections and uses those.

So….with that in mind, I started thinking about how I can tell what data connections an application has used and who is swimming in my pool. Coupling that with the brainstorming involved in .NET Illuminator, I came up with a very nice and simple piece of code that grabs all the connection strings from the Pool and throws them in rafts for you to see.

Why might this be handy for .NET Illuminator, well…Its always interesting to see what data connections an app has made and what the connection string is for them 🙂

Here is my simplistic example of using C# with .NET to walk the SQL pooled connections and return their connection strings.

         /// <summary>
        /// Loads all pooled connection strings.
        /// </summary>
        /// <returns></returns>
        public static List<string> LoadAllPooledConnectionStrings()
        {
            try
            {
                SqlConnection connection = new SqlConnection();

                FieldInfo connectionFactory = connection.GetType().GetField("_connectionFactory", BindingFlags.GetField | BindingFlags.NonPublic | BindingFlags.Static);

                object cFactory = connectionFactory.GetValue(connection);
                FieldInfo connectionPoolDictionary = connectionFactory.FieldType.GetField("_connectionPoolGroups", BindingFlags.GetField | BindingFlags.NonPublic | BindingFlags.Instance);

                object dictPools = connectionPoolDictionary.GetValue(cFactory);

                Type poolDictionaryType = dictPools.GetType();
                PropertyInfo pooledKeys = poolDictionaryType.GetProperty("Keys", BindingFlags.Public | BindingFlags.Instance);

                object keys = pooledKeys.GetValue(dictPools, null);

                List<string> allKeys = new List<string>();
                foreach (object key in (IEnumerable)keys)
                    allKeys.Add(key.ToString());
                return allKeys;
            }catch(Exception err)
            {
                throw err;
            }
        }

So…What did we learn? Just because you don’t see the pool….smell the chlorine…or even get wet, you open a data connection and you have jumped all the way in.. and left your stench behind in the water..

Happy swimming!