Protected: WordPress CRM Lead Gen Tester

This content is password protected. To view it please enter your password below:

Password:

SQL Database moved to another server – how to fix orphaned logins

Last night I was on support for a software deployment that saw one of our databases being moved from a legacy server to our main production SQL Server cluster.  Once we did, the SQL login we had created for our application to connect to the database was unable to access the db on the destination server, despite a SQL login of the same name and password having had access on the legacy server.

At first, I was surprised, as we had done this same type of move several times during testing, moving from my development machine to a QA SQL Server, and never experienced this issue.  Upon a few moment’s further thought, we were using a different SQL login in QA than in dev.  So after moving the database from dev to QA, we had a step to update the user mapping to give it access to the database.  I didn’t think this was necessary on the production move, since the login already existed on both servers, and had access to the database on the legacy server.

Then I remembered that in the past we had experienced orphaning of some logins when moving databases between servers.  I couldn’t remember the resolution of how to resolve it, so I set out to Google and quickly found an answer I liked, on this thread:  http://dba.stackexchange.com/questions/40844/after-moving-database-backup-restore-i-have-to-re-add-user

I’ve decided to capture it here for prosperity.

First step is diagnosis–you can run this query to check for any orphaned logins in your database:

use [Your Database Name Here]
go

select
    dp.name [user_name]
    ,dp.type_desc [user_type]
    ,isnull(sp.name,'Orhphaned!') [login_name]
    ,sp.type_desc [login_type]
from   
    sys.database_principals dp
    left join sys.server_principals sp on (dp.sid = sp.sid)
where
    dp.type in ('S','U','G')
    and dp.principal_id >4
order by sp.name

Yes, the above query is a direct copy and paste from the article I linked.  I just wanted to have it on my own blog for easy accessibility in the future.  Full credit to Mike Fal, the first responder on that thread.

Once you’ve confirmed that the login you’re interested in displays “Orphaned!” (and yes, ours did), a very simple fix exists:

ALTER USER [LoginNameHere] WITH LOGIN=[LoginNameHere]

This re-associates the database user LoginNameHere, with the server login LoginNameHere.  After we did this step, all was good.

Simulate collapsed borders with DIVs

I had a situation where I wanted to have a number of boxes, arranged in rows of up to 3, in a table-like layout, but using floated DIVs instead of a table.  The problem is, when you add borders around the boxes using DIVs, they “double up” where DIVs are next to each other.  Using a table layout instead, you could specify border-collapse:collapse; and that situation goes away.  It does not appear to do so for DIVs.  Here’s an illustration:

Notice the area in the green rectangle.  The borders where the DIVs adjoin are clearly visually thicker than the outer borders where no other DIV adjoins.

My initial styling looked like:

            div.boxcontainer {
                width: 561px;
                font-size:large;
                text-align:center;
                background-color:#ffffff;
                float:left;
                margin-left: 10px;
            }

            div.campaignbox {
                float: left;
                width: 185px;
                height: 185px;
                background-color: #def;
                margin:0x;
                padding:0px;
                border: 1px solid #aaa;        
            }                
                
            div.oneLine {
                margin-top:80px;
            }
            div.twoLines {
                margin-top:70px;
            }
            div.threeLines {
                margin-top:60px;
            }
            div.fourLines {
                margin-top:50px;
            }
            div.fiveLines {
                margin-top:40px;
            }

And my example markup for the boxes shown in the image above like this:

<div class="boxcontainer">

    <!-- insert new items here, at the top of the list: -->

    <div class="campaignbox">
        <div class="twoLines">
            <a href=".">My top-left<br />bx</a>
        </div>
    </div>
    <div class="campaignbox">
        <div class="threeLines">
            <a href=".">Example<br />three<br />liner</a>
        </div>
    </div>
    <div class="campaignbox">
        <div class="oneLine">
            <a href=".">Cool Stuff Here</a>
        </div>
    </div>
    <div class="campaignbox">
        <div class="threeLines">
            <a href=".">Well<br />hello<br/>there</a>
        </div>
    </div>
    <div class="campaignbox">
        <div class="fiveLines">
            <a target="_blank" href=".">This<br />Box<br />Has<br />Much<br />to Say</a>
        </div>
    </div>

</div> <!-- boxcontainer    -->

After some googling and a bit of trial-and-error, I adapted this technique for my own use.

Basically the only thing I needed to change was the campaignbox style.  I replaced the border attribute with the box-shadow as described in the article I linked above:

            div.campaignbox {
                float: left;
                width: 185px;
                height: 185px;
                background-color: #def;
                margin:0x;
                padding:0px;
                  /* And here comes the trick to collapse
                     the borders.  Based on: http://codepen.io/Hawkun/pen/rsIEp */
                  box-shadow: 
                    1px 0 0 0 #AAA, 
                    0 1px 0 0 #AAA, 
                    1px 1px 0 0 #AAA,   /* Just to fix the corner */
                    1px 0 0 0 #AAA inset, 
                    0 1px 0 0 #AAA inset;
            }

Everything else stayed the same, but now my borders are properly collapsed, as shown below:

Listing explicitly granted database persmissions (SQL Server)

I recently needed to know what explicit permissions had been granted to some tables and sprocs in one of my databases, and wasn’t able to find a way to do it through the SQL Management Studio UI.  So I googled, and found this query:

(Code)

Get SSRS Tablix headers to repeat on each page

This approach worked for me (from https://stackoverflow.com/questions/11285923/tablix-repeat-header-rows-on-each-page-not-working-report-builder-3-0):

1. Open Advanced Mode in the Groupings pane. (Click the arrow to the right of the Column Groups and select Advanced Mode.)
2. In the Row Groups area (not Column Groups), click on a Static group, which highlights the corresponding textbox in the tablix. Click through each Static group until it highlights the leftmost column header. This is generally the first Static group listed.
3. In the Properties window, set the RepeatOnNewPage property to True.
4. Make sure that the KeepWithGroup property is set to After.

The KeepWithGroup property specifies which group to which the static member needs to stick. If set to After then the static member sticks with the group after it, or below it, acting as a group header. If set to Before, then the static member sticks with the group before, or above it, acting as a group footer. If set to None, Reporting Services decides where to put the static member.

 

T-SQL: Collapse multiple rows of SQL data into a single string

This posting may not be useful to anyone but me again in the future, but I needed to figure out how to take some text and other information from multiple rows of a table and combine them into a single string for display.

My main goal was to build a string which is a summary of a number of activities that were logged against a specific entity.  First I gathered the important activity details into a table variable defined as:

    declare @Activities table
    (
        ActivityID            uniqueidentifier,
        RegardingObjectID    uniqueidentifier,
        ActivityTypeName    nvarchar(50),
        ActivityDate        datetime,
        DescriptionText        nvarchar(max),
        CreatorId            uniqueidentifier,
        CreatorName            nvarchar(200) 
    )

Once that was populated (I’ll omit that part, as it’s pretty specific to our source system schema and not interesting for the example), I then took each of the activity entries and flattened them down into one string each in another table variable @ActivitiesStrings:

    -- Now, roll up each activity into a string representation (one per activity)
    declare @ActivitiesStrings table ( ActivityString nvarchar(max) )

    insert into @ActivitiesStrings (ActivityString)
    select '--------------------------' + char(13) + char(10) + '** ' + ActivityTypeName + 
        case when ActivityTypeName <> 'Opportunity Close' then ' added by ' else 'd by ' end +
        CreatorName + ' on ' + 
        datename(m, ActivityDate) + ' ' + datename(d, ActivityDate) + ', ' + datename(yyyy, ActivityDate) + ', ' +
        case when datepart(hh, ActivityDate) < 12 then  datename(hh, ActivityDate) else convert(nvarchar(2),datepart(hh, ActivityDate) - 12) end +
        ':' + right('00' + datename(n, ActivityDate), 2) +
        case when datepart(hh, ActivityDate) < 12 then ' AM' else ' PM' end + char(13) + char(10) + 
        'Description:' + char(13) + char(10) + 
        case when isnull(DescriptionText,'') = '' 
            then 'N/A'
            else replace(DescriptionText,  char(10), char(13) + char(10))
        end as ActivityString
    from @Activities
    order by ActivityDate desc

The char(13) + char(10) starts a new line in the system this string ultimately gets displayed in.  The —–‘s were used as visual separators for when the strings are all combined.

Next came the part I didn’t knowhow to do without looking, which was to combine these multiple activity strings into one big activity summary string.  I googled a bit and found this simple example and used it as mymy basic guide:

http://www.codeproject.com/Tips/334400/Concatenate-many-rows-into-a-single-text-string-us

My code came out as follows:

    -- finally, roll the strings all up into one big string representation of all the activity
    DECLARE @results nvarchar(max)
    SELECT @results = COALESCE(@results + char(13) + char(10) + '--------------------------' + char(13) + char(10) + char(13) + char(10) , '') + ActivityString FROM @ActivitiesStrings

    --SELECT @results  --debug

    -- and return the results
    if len(@results) > 0  
        set @results = @results + char(13) + char(10) + '--------------------------' + char(13) + char(10) + char(13) + char(10)
    else
        set @results = ''

    RETURN @results

Once again there are extra carriage returns and separator lines included to mimic how some other items are displayed in the target system showing this string.  Your milage may vary!  The return statement was included since this code is part of a scalar-valued user defined function I created for this purpose, and that’s how you return results from a UDF.

Don’t… Forget… the Admin.. Service… Jobs……..!

Seeing weird behaviour in your SharePoint installation after making changes and just can’t explain it?  Have you tried executing the admin service jobs yet???

For the unaware, that means running this from your command line:

stsadm -o execadmsvcjobs

A lot of times, this will make SharePoint finally “notice” something you’ve changed.  I forgot that lesson until I strugged today with getting it to notice a change in the FieldRef nodes in one of my content types, no matter how many times I monkeyed around with it.

So, in summary, always try the service jobs!  You never know when they just might fix your #%*()#$%ing issue and save you a bunch of time.

Until the next time…

Make your custom web service work on a SharePoint server

So I had written a small web service that uses the SharePoint APIs (the DLLs, not the SP  web services) to update list items on a particular list.  In my dev environment inside visual studio, it works great using ctrl-F5 or F5 to start it.  When I went to deploy it to a shared SharePoint staging environment, I didn’t know how to set it up.  Initially I tried just copying it into inetpub\wwwroot\myWS\ and marking the folder as an application in IIS, naively thinking that was sufficient.  But since this is SharePoint, of course it just isn’t!

I did some googling and found a walkthrough from Microsoft: http://msdn.microsoft.com/en-us/library/ms464040%28office.12%29.aspx (or for 2010: http://msdn.microsoft.com/en-us/library/ms464040(office.14).aspx).  At first I thought it seemed odd to have to install my web service to the 12\ISAPI folder where it would get mapped to http://myserver/_vti_bin/, and to have to convert my static wsdl and disco files (generated by VS) to .aspx pages to have them handle the correct path, etc., etc.  I resisted the idea and went home for the day.

The next day I decided to try the walkthrough and sure enough it works great.  So I thought I should jot this down for when I run into it again.  Maybe it will help you some day, too.

EDIT: this link may also prove helpful: http://www.codeproject.com/KB/sharepoint/MOSS4Devs-08-WebServices.aspx

Unbinding events using jQuery

Recently, I was trying to use jQuery’s .Unbind() function to remove an onclick event handler that was was being bound elsewhere, out of my control. (Basically I wanted to suppress a “click to add a new item” link to force something to be read-only in a 3rd party app.) Frustratingly, I could not seem to get the Unbind() to take effect. I knew my selector was okay, because I could change the background-color of the item I was targeting without issue.

Eventually I googled it and found other people with the same problem. Turns out, jQuery only tracks event handlers that have been bound using jQuery. So, if the event handler was bound declaratively in the HTML markup (or output by some other system/framework), Unbind() won’t touch it (doesn’t know about it unless you used Bind() to add it).

The workaround solution is to use .removeAttr(eventname), such as .removeAttr(“onclick”). This worked for me and stopped me from banging my head on the wall any further. Hopefully it helps you, too.

Code block not allowed in your SharePoint Master Page error

Code blocks are not allowed in this file

I’m posting this now because I just ran into it for a second time, but had completely forgotten what the issue was when I googled it the first time.  So I’m posting it here for my convenience, mostly!

If you suddenly start getting an exception from SharePoint stating that code blocks aren’t allowed in your master page, you need to add a new <PageParserPath> element to web.config to allow the code.  Why this starts happening all of a sudden when the same code block existed in the path previously without the exception, I will never understand.  But the point is, follow the directions in one of these blog postings to resolve it:

http://mystepstones.wordpress.com/2009/02/13/code-blocks-are-not-allowed-in-this-file/

http://svengillis.blogspot.com/2008/03/code-block-are-not-allowed-in-this-file.html

http://dotnetdeveloper.co.uk/blogs/sharepoint/archive/2008/10/10/code-blocks-are-not-allowed-in-this-file.aspx