Setting DBCC 1222 on startup
The following command is run to gain details on deadlocks.
DBCC TRACEON (1222,-1)
However, once the SQL instance is restarted this flag is set back to disabled.
Ableton Live & Lemur Setup (From a Windows User)
Had a chance to look at this program thanks to the generosity of the developer. They had a promotion that ended prematurely and they sent me a license as a goodwill gesture. Pretty fantastic service, and thanks to them for this.The documentation was a little sparse for the Windows setup, so I ran into some complications getting it to work.
I have used TouchOSC, and had some difficulty with configuring it for windows. True to other’s postings, Lemur didn’t make this easier. Especially …
Lack for Nothing
The message of this song is one that that has been driving itself home as more and more important to me. We are not viewed in our frailty as we come before God to worship Him. We are not judged and condemned. He doesn’t look and tell us to come back when we are in a better place. Instead, just as the author of Hebrews writes (Heb 10), we can come with bold confidence, knowing that the way has been made for us in Christ, with us being viewed through the “curtain” of Christ. This …
Remote Desktop Workflow Improvements
Remote server management is a fact of life for folks involved with sql server. Since we work so often with remote machines I looked into a few tools that provided a better workflow than the default Microsoft tools. This one came out as the winner by far.
A better remote desktop manager
First off, if you are using RDC…. why? At least move up to RDCMan, a microsoft tool that allows for much quick context switching between machines, inherited password settings for a group of servers, and …
SSMS - Connection Color with SQL Prompt & SSMSBoost
If you haven’t explored the visual color coding of tabs based on pattern matches with SQL Prompt, I’d suggest you check this out. Earlier iterations of Red Gate’s SQL Prompt did not change tab color immediately when the connection was changed. Red Gate’s tab color could get out of sync occasionally, so I stopped depending on it.
Apparently this has been improved on and my testing now shows that the tab recoloring for connections is changing when the connection is updated …
SQL 2016 - Brief Overview on some new features
These are notes taken from the Houston SQL Pass User group from July. This presentation was given by John Cook, (Data Platform Solution Architect Microsoft) who did a great job with limited time on providing some great details on the new functionality with SQL 2016. To follow him, take a look at sqlblog.com where he posts or follow him on twitter. Thanks to him for the overview.
JohnPaulCook (@JohnPaulCook) on Twitter
Microsoft Data Platform specialist and Registered Nurse
John …
Install ReadyRoll via Command Line
command line install options
ReadyRoll has some great features, including the ability to use without cost on a build server. If you want to ease setup on multiple build servers you could create a simple command line install step against the EXE.
future changes
ReadyRoll was recently acquired by Redgate, so the installer options may change in the future to be more inline with the standard Redgate installer. For now, this is a way to automate an install/updates.
autoupdating via …
Does sp_rename on a column preserve the ms_description?
Did some checking as couldn’t find help in the MSDN documentation. My test on SQL 2016 shows that since the column_id
isn’t changing, the existing mapping of the description for the column is preserved.
I know it’s probably pretty obvious, but I had someone ask me, so figured proving the mapping for ms_description is maintained would be a good thing to walk through. Score another point for Microsoft, for design practices
Regex With SQL Server - SQLSharp
In the context of my developer machine, I had log files I wanted to parse through. I setup a log library to output the results on a test server to a sql table instead of text files. However, this meant that my “log viewers” that handled regex parsing weren’t in the picture at this point. I wanted to parse out some columns from a section of message text, and thought about CLR as a possible tool to help this.Ideally, I wanted to feed the results for analysis easily into power bi, …
Improvements with SSMS 2016
choco upgrade sql-server-management-studio
and you’ll simplify the installation process greatly.
Also for servers, consider Azure Data Studio as much smaller download and might provide what you need to do basic management without a length install …Glasswire: (Giveaway Included) Networking Monitoring even a caveman could
Giveaway details at the bottom for those interestedDealing with development & sql servers, I like to know what type of network traffic is happening on my machine. What is the overhead of monitoring on network bandwidth, what is communicating across servers or even externally?
What is phoning home?
You can create perfmon counters, but realistically sometimes I just want a easy quick overview of network traffic with minimal overhead. I have been using a utility I came across called …
SQL Compare 12: Initial Look
I know there have been a few other folks going into more detail on SQL Compare 12 (beta), but I thought I’d share just a few looks at the new design. Looks pretty slick, and I like where the design is going. Just a quick look, as I’m sure there will be more to cover when it’s finally released. Until then….
Some GUI Improvements
Very clean. Not too drastic of a change to mess with current workflows. Still, nice to see some clean design like this.
SSMS Tools Pack - A Handy Tool for generating CRUD
So I’ve had this tool around for a while, but never found much usage out of it to be honest. I didn’t end up writing a review as I had other tools that did text replacements, and history/session saving. I’ve always considered this tools implementation of SQL History/Tabs saver the best period, even over Red Gate Tab History, SSMSBoost, etc. However, recommending the tool solely based on it’s fantastic history saver wasn’t really something I was going to do.However, …
OmniCompare: A free tool to compare SQL Instances
comparing instances
When working with a variety of instances, you can often deal with variances in configuration that might impact the performance. Without digging into each instance you wouldn’t know immediately that this had happened. There are fantastic tools, like Brent Ozar’s SP_Blitz, but this doesn’t focus on every single configuration value and cross instance comparison. To supplement great material like that a tool like OmniCompare is great.
side by side …
Continual Deployment of Visual Studio SqlProj
Unveil the inner workings of the esoteric build system…
As a data professional, I’ve never worked extensively with msbuild or other pipelines. I’d been mostly focused on just running schema comparisons and publishing. However, I’ve had the needed to try and deploy a database project from visual studio automatically, and this is my process through it.
There are benefits for those who don’t necessarily want to run this against production, but instead for those …
The Mysterious Black Box of R - For the SQL Server Guy
Took a class from Jamey Johnston @ SQLSaturday #516 in Houston. Lots of great information covered. Follow him for a much more detailed perspective on R. Jamey Johnston on Twitter @StatCowboy. Did a basic walkthrough of running an R query, and figured I’d share it as it had been a mysterious black box before this. Thanks to Jamey for inspiring me to look at the mysterious magic that is R….
Setup to Run Query
Simple-Talk: Making Data Analytics Simpler SQL Server and R This …
Red Gate Dependency Tracker - Making Databases Into Moving Art
data structures are art
If anyone thinks that working with complex data structures is boring… I don’t know what world they live in. The problem is often that sql tables and data structures are just script files and lists of tables in an object explorer.
However, once you crack open the visual aspect of database diagramming and data architecture, you can see some interesting patterns emerge.red gate dependency viewer tracker
I’ve long enjoyed playing around with Red …
Google Search Only Results from the last year
Tech changes quick. Reading google postings from something in 2009 is not my first choice.I found an option after digging through some google discussion posts on how to setup the default search in chrome (also applies to other browsers) to automatically apply the advanced filter option for “results in last year”.
Automating SSMS 2016 Updates & Install
Cool Tools: Powershell ISE-Steroids
Disclaimer: I have been provided with a free license because I am reviewing. This doesn’t impact my assessment of the tool. I have a passion for finding tools that help developers, and even more specifically SQL DBA/Developers improve their workflow and development. I don’t recommend tools without actually using them and seeing if I’d end up adding to my roster of essentials!
Expanding Powershell ISE
Powershell ISE is simple. Not much fluff, but it …
Failed to Initialize SQL Agent Log... not worthy
Moving system databases in SQL Server takes a bit of practice. I got that again, along with a dose of SQL humility (so tasty!), today after messing up some cleanup with sql agent server log files.
|
|
I was creating a sql template when this came about. SQL Server Agent wouldn’t start back up despite all the system databases having very little issues with my somewhat brilliant sql commands. I had moved all my databases to …
TFS Work-Item Fancy Filtering
If you want to create a TFS query that would identify work items that have changed, but were not changed by the person working it, there is a nifty way to do this.The filtering field can be set to <> another field that is available, but the syntax/setup in Visual Studio is not intuitive. It’s in the dropdown list, but I’d never noticed it before!
SQL 2012 SP3 and entity framework conflict
the problem
An issue with SQL Server 2012 SP3 was identified that impacted EF4/5 due to additional datatypes in the dll.
System.EntryPointNotFoundException: Unable to find an entry point named 'SetClrFeatureSwitchMap' in DLL 'SqlServerSpatial110.dll'
diagnosing
To easily identify the available dll versions of sql server, I ran a quick adhoc bat file.
The output returns a simple text file like this:
Previewing the new SSRS 2016 portal
Ran into an issue with the “Preview New Reporting Portal” link on a fresh install of 2016 giving me a not found error.
Changing the virtual directory in the Report URL tab for SSRS configuration fixed this invalid link. In my case, I …
Failover all databases to other server
Quick snippet I threw together to help with failing over synchronized databases to the other server in bulk. No way I want to click that darn Fail-over button repeatedly. This scripts the statements to print (i commented out the exec portion) so that you can preview the results and run manually.Note that it’s also useful to have a way to do this as leaving databases running on the mirror server for an indefinite period can violate licensing terms on the secondary server when it’s a …
Calculating Some Max Mirror Stats
This turned out to be quite a challenge. I couldn’t find anything that made this very clean and straight forward to calculate, and in my case I was trying to gauge how many mirroring databases I could run on a server.In my scenario, I wasn’t running Expensive Edition (@BrentO coined this wonderful phrase), so was looking for the best way to assess numbers by doing mirroring on a large number of databases, in my case > 300 eventually. The documentation was… well…. a …
Easy way to test log shipping or availability groups setup
Have been working through the fantastic training resources from Brent Ozar’s Everything Bundle and on the recommended resources they mention after all the training on log shipping and availability groups that you can use a lab environment from TechNet to actually get going on familiarizing yourself with the technology more.
This is great! Of course, it’s not the full deal, but this gives a tangible way to get moving on familiarizing yourself with this complex technology. TechNet …
Diff All Files - Reviewing Changesets Quickly
Diff all files
Transaction Logging & Recovery (part 3)
Continuation of some notes regarding the excellent content by Paul Randal in Pluralsight: SQL Server: Logging, Recovery, and the Transaction Log. Please consider supporting his excellent material by using Pluralsight and subscribing to his blog. He’s contributed a vast amount to the SQL server community through SQLSkills This is my absorbing of key elements that I never had worked through
Jackalopes Are Real….so are Virtual Log Files
Ever seen a picture of a jackalope? Image by …
Seeker & Servant: Fantastic Music with incredible dynamics
Just recently discovered this artist after being exposed to an article from Worship Leader magazine. Fantastic dynamics. The dynamics and beautiful harmonies are pretty darn close to what I’d love experimenting with if I had a group of folks playing those instruments. Interestingly, the vocal harmonies are very simple, but I found very beautiful. It’s compromised of a tenor and a baritone range, and is a fresh change stuff like Shane and Shane which both have incredibly high ranges. …
Verifying Instant File Initialization
Ran into a few issues verifying instant file initialization. I was trying to ensure that file initialization was enabled, but found out that running the xp_cmd to execute whoami /priv could be inaccurate when I’m not running it from the account that has the privileges. This means that if my sql service account has different permissions than I do, I could get the incorrect reading on if it is enabled.
Paul Randal covers a second approach using the sysinternals tool Accesschk, which seems …
Transaction Logging & Recovery (part 2)
Continuation of some notes regarding the excellent content by Paul Randal in Pluralsight: SQL Server: Logging, Recovery, and the Transaction Log. Please consider supporting his excellent material by using Pluralsight and subscribing to his blog. He’s contributed a vast amount to the SQL server community through SQLSkills
Transaction Log File
- The initial size of the log file is the larger of 0.58 MB or 25% of the total data files specified in the create database statement. For example, …
Transaction Logging & Recovery (101)
Logging & Recovery Notes from SQL Server Logging, Recovery, and the Transaction Log (pluralsight) Paul Randal Going to share some key points I’ve found helpful from taking the course by Paul Randal at pluralsight. I’ve found his knowledge and in detail presentation extremely helpful, and a great way to expand my expertise. Highly recommend pluralsight.com as well as anything by Paul Randal (just be prepared to have your brain turned to mush… he’s definitely not …
2015
Model needs exclusive lock
Ran into an issue where a developer was trying to create a database and was denied due to no ability to obtain exclusive lock on model. After verifying with other blogs, I found that creating a database required exclusive lock to use model as a template for the new database creation.
In my case I had connected with SSMS directly to model for some queries instead of master. In this case, SQL Complete (Devarts’s excellent alternative to SQL Prompt) was querying the schema had this open …
Documenting Your Database with Diagrams
Don’t get too excited. I know you love documentation, and just can’t wait to spend some time digging in to document your database thoroughly. I imagine you probably want to build visio charts manually, or draw whiteboard diagrams by hand and take pictures.
For the rest of us that are lazy, a tool to help document your database is a great idea. I’m a big fan of SQL Doc by Red Gate, and ApexSQL Doc . I ended up using ApexSQL doc to document the database at my work, though Red …
Best Tools for Taking Notes In Development
Dynamic SQL & Quotename
Not quite fineprint, but sure feels like it!
Quotename can be a pretty cool function to simplify your dynamic sql, as it can ease some of the escaping of strings. However, I normally use it for table/column names, and so hadn’t ran into a “gotcha” of this function until today. It’s limited to 128 characters, and if you pass in greater than 128 characters will yield a null. Yep… you could be trying to track down that error for a null string somewhere in your …
XML Attribute VS Element Assignment when working with SQL
XML handling with aliases
I find it interesting the difference in behavior with querying XML between column assignment, and quoted alias naming. It’s a generally understood best practice to not use the deprecated syntax of column aliasing using a quoted name. For example:
|
|
Split personality text editing in SSMS with Sublime Text 3
My preview post showed a demonstration of the multi-cursor editing power of Sublime Text 3 when speeding up your coding with SQL server.There is a pretty straight forward way to setup sublime (or one of your preferred text editors) to open the same file you are editing in SQL Management Studio without much hassle. I find this helpful when the type of editing might benefit from some of the fantastic functionality in Sublime.
External Tool Menu
Go to Tools > External Tools
Multi-Cursor Editing SQL feels like the movie Inception just became real
- Yes… multicursor editing is epic
- No… SSMS doesn’t support multi-cursor editing the way it should.
- Yes… you can do some basic editing with multiple lines using alt-shift
- No… it doesn’t come close to what you can do with Sublime. Cool thing is you can open text in Sublime synced w/SSMS cursor position and switch between the two with a shortcut. That will be a post for another day, I’m just telling you now to get your appetite going. If you can’t …
Brentozar''s Training: Chocolate & Cowboy Hats Included
It was entertaining to listen to a technical wizard fighting the obsession with waiting to eat chocolate with 3k viewers watching. Kendra wore about 4-5 cowboy hats in an effort to help those of us who wear many hats feel welcome…. Now that’s the kinda of training I enjoy! No pretense, just honest real, and insightful training with enjoyable humor included for free
Highly recommend attending the webex presentations occurring today and tomorrow with Brent Ozar and his amazing team. …
Monitoring SQL Server on a budget
Cheap ain’t easy
There’s a lot of tools out there, and very few that are polished, have a good UI, and some reasonable functionality to help monitoring, that don’t cost an arm and a leg. One such tool I’ve recently begun to appreciate is MiniDBA . I was generously provided with a license to evaluate this and continue testing, and have recently had an actual chance to start using it more in my environment. The cost for MiniDBA is one of the most affordable I’ve …
Stranger Danger... The need for trust with constraints
I ran into an issue with errors with an database upgrade running into a violation of a foreign key constraint. Don’t know how it happened. Figured that while I’m at it, I’d go ahead and evaluate every single check constraint in the database to see if I could identify any other violations, because they shouldn’t be happening.
improve the execution plan by checking the data
In my reading, I found out that checking the constraints can enable the constraint to be marked …
Red Gate SQL Source Control v4 offers schema locks
Looks like the rapid release channel now has a great feature for locking database objects that you are working on. Having worked in a shared environment before, this could have been a major help. It’s like the poor man’s version of checking an object out in visual studio except on database objects! With multiple developers working in a shared environment, this might help reduce conflicting multiple changes on the same object.
Note that this doesn’t look to evaluate dependency …
Using Qure Profiler To Benchmark Tuning Progress
Qure Analyzer Benchmark Testing
SET NOEXEC is my new friend
Have never really played around with the option: SET NOEXEC ON
Turns out this can be a helpful way to validate larger batch scripts before actually making changes, to ensure compilation happens. If you choose, you can verify syntax by “parsing” in SSMS. However, this doesn’t compile. Compilation checks more than your syntax. It actually validates the objects referenced exist.
The execution of statements in SQL Server has two phases: compilation and execution. This setting is …
Running very large scripts is not a strong area for SSMS
out of memory, argggh!
Am I the only one that has experienced the various out of memory issues with SSMS? Not according to google!
lovingly crafted in the forges of.. well … dbforge
I’ve a huge fan of Devarts products. I’ve done a review in the past on their SQL Complete addin, which is the single most used tool in my SQL arsenal. It vanquishes nasty unformatted code into a standard lined up format I can easily read. The 100’s of options to customize the …
Database Stuck in Single-User Mode Due to Botched Restore
Working in a development environment, I botched up a restore. After this restore attempt to overwrite my database with the previous version, I had it stuck in single-user mode.
SSMS provided me with helpful messages such as this:
Changes to the state or options of database 'PoorDb' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
Additionally, I was told I was the deadlock victim when attempting to set the user mode back to multi-user. …
What was I thinking? Deleting myself from localdb?
Was testing a batch file to add a user to a localdb instance. Assumed that my user as admin on the machine wouldn’t have an issue inserting myself back…. didn’t think that one through too carefully. Executing any type of SQLCMD against it denied me. SSMS denied me. No SA had been setup on it, so I couldn’t login as SA either. Looked for various solutions, and ended up uninstalling and reinstalling (localdb)v11.0 so that I’d stop having myself denied permissions. …
Some simple examples of querying xml with sql
XML is a beast if you’ve never tackled it. Here are some simple examples of what I discovered as I experimented and walked through obtaining values out of a XML column.
Enabling Instant File Initialization
Found a couple good walkthroughs on enabling instant file initialization. However, I’m becoming more familar with the nuances of various setups and found it confusing in trying to map the correct user/group to enable this option. In my case, I had the SQL Service running under NT SERVICE/MSSSQLSERVER and as such this logic wasn’t showing up when trying to find groups/users to add to the necessary permissions. Lo and behold…
I typed it in manually and it worked. If time permits …
Upgrade from SQL 2014 Evaluation to Developer Edition
Couldn’t find documentation showing that upgrade from SQL 2014 evaluation version was possible to developer edition. I just successfully converted an evaluation version to developer edition.
- Obtain the key for the developer edition (in my case I had to download the .ISO from MSDN downloads, and go through the installation wizard to get it)
- Run the installation center app for SQL Server 2014
- Select edition upgrade
- Plug in your new serial from the developer edition. Pretty simply, but …
Utilizing the power of table parameters to reduce IO, improve performance, decrease pollution, and achieve world peace...
I was dealing with a challenging dynamic sql procedure that allowed a .NET app to pass in a list of columns and a view name, and it would generate a select statement from this view. Due to requirements at the time, I needed the flexibility of the “MAIN” proc which generated a dynamic select statement, while overriding certain requested views by executing a stored proc instead of the dynamic sql.
During this, I started looking into the string parsing being completed for a comma …
SQL Sentry Pro Explorer is worth it...
Restoring a database that doesn't exist
When restoring a database that doesn’t exist, say for instance when a client sends a database to you, you can’t use the option to restore database, because there is no database matching to restore. To get around this you need to use the Restore Files and Filegroups option and then restore the database.
Statistics Parsing
Never really enjoyed reading through the statistics IO results, as it makes it hard to easily guage total impact when you have a long list of tables. A friend referred me to: http://www.statisticsparser.com/ This site is great! However, I really don’t like manually copying and pasting the results each time. I threw together a quick autohotkey script that will detect your clipboard change event, look for “scan count” keyword, and then open a “chrome app”, paste the …
Dev Tools: FARR2 Launching groups of files or apps at once
There are probably a common number of apps you pull up when you pull up your system. For example, I pull up my Trello board, outlook, XYplorer, Sublime text 3, Sql server management studio, and ketarin (app updater). Found that you can easily setup a simply alias and launch a group of apps or files at anytime by simply typing the keyword.
Dev Tools: The File Searcher/Launcher to rule them all
Why does this not have more recognition? In the experimentation of various file management and launching apps, I’ve tried several (Launchy, Listary, etc), but none have offered the speed and customization of Find and Run Robot. This app is a life saver for the power user! Here is an example of how you can have a hotkey to immediately launch a customized google search. The group alias gives you extensibility to filter the text you are typing to identify this alias of “Google Me” …
Case of the Mondays... causing me to randomly redefine the Scope of Global
Today, I was reminded that global temp tables scope lasts for the session, and doesn’t last beyond that. The difference is the scope of the global temp allows access by other users and sessions while it exists, and is not limited in scope to just the calling session. For some reason I can’t remember, I had thought the global temp table lasted a bit longer. Remembering this solved the frustration of wondering why my adhoc comparison report was empty….. #mondayfail SQLMag …
2014
Dev Tools: XYplorer (review 1) - Catalog
currently on version 14.60 I’m a big fan of finding tools that help automate and streamline things that should are routine actions.Surprisingly, I’ve found it incredibly challenging to move away from the default Windows Explorer for file management, as the familiarity it offers makes it somewhat tough to be patient with learning an alternative, especially if the alternative offers more complication. That’s where I stood with XYPlorer for sometime. It is a developer’s tool …
OR pattern causing indexing scans in parameter based queries
#Tl;dr
(time constraints prevented me from reworking significantly)
“or-condition-performance” article on SQL Server Pro was forwarded over to me to research by a friend who indicated that using a variable with an or pattern had historically caused table scans. This was a suprise to me as all previous queries with optional parameters I’d used in the past seemed to use index seeks. I had to dig into this a little deeper to see if had been missing this in my past work and needed …
Get Information on Current Traces Running
This is just a quick informational query to save as a snippet to get some quick information on running traces, and provide the stop and close snippet quickly if you want to stop a server side trace you created.
Generate Random Date With Starting Point
If you want to create sample random samples when dealing with date calculations to test your results, you can easily create a start and end point of randomly created dates. This is a snippet I’ve saved for reuse:
DATEADD(day, (ABS(CHECKSUM(NEWID())) % $Days Seed Value$), '$MinDate$')
This should let you set the starting point (Min Date) and choose how far you want to go up from there as a the seed value. Ie, starting point 1/1/2014 with seed of 60 will create random dates up to 60 days …
Snippet Alert: Useful dates (eom, bom, etc)
Common date values you may need to reference that you may not want to write from scratch each time. Hope this helps someone else!
Finding Groups - Consecutive Months
A step by step explanation on one way to get a consecutive period of months, which could easily be adapted to days, years, or other values. I’ll continue on this track and post a tutorial on eliminating overlapping dates soon.
Eliminate Overlapping Dates
I was looking for an efficient way to eliminate overlapping days when provided with a historical table that provided events that could overlap. In my case, I had dates show the range of a process. However, the multiple start and end dates could overlap, and even run concurrently. To eliminate double counting the days the process truly was in play I needed a way to find eliminate the overlap, and eliminate duplicate days when running in parallel. I researched ways to complete this and found the …
Scalar functions can be the hidden boogie man
Ran across a comment the other day that scalar functions prohibit parallelism for a query when included. I thought it would be worth taking a look, but didn’t take it 100% seriously. Came across the same indication today when reviewing MVP deep dives, so I put it to the test.Turns out even a simple select with a dateadd in a scalar format was affected enough with that one action to drop 5% on the execution plan. When dealing with merge or other processes that would benefit from …
2013
Renaming all references inside stored procedures and functions can be migraine worthy without a little help...
If you run across migrating or copying a database structure for some purpose, yet need to change the database references or …
Shortcut to reference examples, syntax, and definitions straight from SSMS
I’ve never really used the F1 key for help files with most applications. I was surprised at the usefulness in SSMS I discovered today that uses scripting to actually get you MSDN articles relevant to your current selection in the query editor.
If you have a keyword selected and want to view details, definition, and examples on it, you can highlight the phrase or select the word, press F1, and SSMS will pull up the appropriate MSDN article. The only issue I ran into was that it pulls up the …
Check Constraints can help enforce the all or nothing approach when it comes
If you have a set of columns inside your table that you want to allow nulls in, however if one of the columns is updated force all columns in the set to be updated, use a check constraint. In my case, I had 3 columns for delete info, which were nullable. However, if one value was updated in there, I want all three of the delete columns to require updating. I created the script below to generate the creation and removal of these constraints on a list of tables:
|
SSMS 2012 Extender for the times you want some organization to the random pile
When dealing with large amounts of objects in a database, navigation can be tedious with SSMS object explorer. This extender organizes the objects into groups based on schema, helping a developer easily navigate to the appropriate object. The current version didn’t work for views, but the other objects were grouped effectively. Highly recommend!
TSQL Snippet for viewing basic info on database principals and their permissions
Quick snippet I put together for reviewing basic info on database users/principals, permissions, and members if the principal is a role.:
|
|
A moment of void in the cranium reveals a recursive computed column with an
Msg 402, Level 16, State 1, Line 67 The data types varchar and void type are incompatible in the add operator.
I came across this error today when I accidentally used a computed column in a temp table, that referenced itself. This very unhelpful message was caused by referring to the computed column itself in the computed column definition, ie typo. Beware!
View computed columns in database
Snippet to quickly view computed column information. You can also view this by doing a “create table” script. This however, was a little cleaner to read and view for me.
|
|
On how to Googlify your SQL statements for future searching
For sake of future generations, let’s begin to reduce typing and reuse code we’ve built. I think we can all agree that TSQL statements are often repeated. Ideally, snippets should be created to reduce repeated typing and let us focus on logic and content. However, some statements may not really be “snippet worthy”, and just be quick adhoc queries. In the past, the solution for saving queries for reuse or reference in the future would be to just save in the projects folder …
Get synonym definitions for all databases in server
If you want to audit your enviroment to look at all your synonyms and see where they are pointing, you can use exec sys.sp_MSforeachdb
to loop through databases, and even filter. It will save some coding. However, my research indicates it is probably a bad practice to rely on this undocumented function as it may have issues not forseen and fully tested.
Additionally, support may drop for it in the future. I recreated what I needed with a cursor to obtain all the synonym definitions into a temp …
Calculating the next beginning of month and the current end of month
Handling dates is always a fun challenge in T-SQL! Finding the current end of month and next months beginning of month is straight forward, but I like to find new ways to do things that take less coding, and hate date conversions that require a lot of manipulation of characters and concatenation. This was what I came up with for avoiding character conversions and concatenation for finding the current BOM (beginning of month) and EOM (end of month) values. Adjust according to your needs. Cheers! …
Installing SSMS 2012 all by it's lonesome
SQL Server Management Studio (SSMS) is not offered as a standalone download on MSDN. Installation requires the user to download the sql server installation package and choose to install only this single feature. For developers, SQL Developer edition is a great choice.
Here’s some screenshots to give you a guide on installing SSMS by itself when working with the full installer.
snippet designate a certain time of the day in getdate()
Snippet to designate a certain time of the day to evaluate in the current day. If you need to limit a result to the current date after a particular time, strip the time out of the date, and concatenate the current time together with it, and then convert back to datetime2.
|
|