I have a requirement in which I have to access a SQL view from within my customisation dictionary, in order to create a custom lookup for users to select a value based on an Extender form and an Extender lookup. Easiest option is to create an Extender view (which in turn creates a SQL view for us).
Now, this is the view that I am suppose to refer to from my custom dictionary. Dexterity allows us to refer to any SQL object by simply create a TABLE definition and mention the SQL object (table or view) name as the physical name.
Everything looks perfect till you actually see below error messages at runtime:
Error message is quite obvious; you do not have DEX_ROW_ID in that SQL view that you are referring to. Every single Dexterity table must have DEX_ROW_ID at the backend. It cannot afford to not have one.
So how am I going to resolve this? By simply adding a record number dynamically to the SQL view created by Extender. How to do that? By adding the T-SQL function ROW_NUMBER(). This is how I achieved it:
There is an amazingly simpler yet highly informative post up on TECHNET portal, which proved very handy this morning.
My systems admin reported to me that our SharePoint server ran out of storage for some reason unknown to him, but he could identify that it was SQL Server program files folder which consumed 80% of storage.
I immediately knew what could be the reason. It’s those crazy log files problem. To truncate and clear these overeating log files, I had to use this command: DBCC SHRINKFILE.
Once I cleared out all unnecessary file space from these log files, I had to setup the File Growth and Maximum File Size (shown in the screenshot beside):
Never for a DB log file, we should setup the file growth as “In Percent”. This would prove disastrous over a period of time. Instead, always set it up to “In Megabytes” and enter a least sensible value; in my case I had set it up to 5MB.
For a DB Log file, 2GB should be reasonable amount of file space to hold the logs. To learn more about Log files, read this post on TECHNET: The Transaction Log (SQL Server).
We must understand one thing, that shrinking the DB log has got it’s own impact.
We do not have a built in T-SQL function to convert any string (or a statement) to Proper Case format, also known as Title Case. This I feel is a very simple feature that SQL Server could have provided us, but missing even in it’s latest version, SQL Server 2012.
I thought I would highlight some links which would be useful for T-SQL programmers in our community:
Till SQL Server 2008 R2, if you want to change the database on which you would like to execute a SQL query in SQL Server Management Studio (SSMS), the keyboard shortcut to do so was CTRL + U from the query window.
Now from SQL Server 2012, it is CTRL + ALT + J.
So those who tried this using the old shortcut, keep in mind that the new shortcut.
In March 2012 (precisely 6th March 2012), Microsoft made available SQL Server 2012 Performance Dashboard Reports which can be used to identify whether there is a current bottleneck on the system, and if so, capture additional diagnostic data that may be required to resolve it.