Okay, I hit a rabbithole today. I'm not saying it's not fun, but probably not my smartest use of time.

Long story shorter than I want, I find myself googling "SSMS equivalent on macOS" fairly often, meaning [Microsoft] SQL Server Management Studio, the Windows-specific tool you use not simply to manage SQL Servers via a GUI, but to throw raw T-SQL at it and view the results. It's barely changed since at least the 1990s.

I used to use SQuirreL-SQL on Mac a lot when I dabbled (and also worked!) in Java, which really came in handy once when my boss didn't realize we were installing a Java app dbms onto a Linux-only network, and I briefly enjoyed Azure Data Studio until it got 86'd.

Azure Data Studio was introduced as a lightweight, cross-platform tool tailored for database professionals working with SQL Server, Azure SQL Database, and other data platforms. It provided a modern interface, integrated notebooks, and extensibility through extensions. However, over time, the overlap between Azure Data Studio and Visual Studio Code became increasingly apparent. Both tools shared similar architectures, extensibility models, and even some of the same extensions.

It would probably still work great, but it's frustrating that yet another MS tool got tossed into a VS Code extension. Look, VS Code is cool, but it's not an IDE. It feels like chicken wire and duct tape on a good day. And the last thing I want to do is have MORE things living in this single scripting app. I like dedicated apps, for heaven's sake.

But you know what I've done for nearly thirty years now? Written apps that interface with SQL Server! Why don't I just write my own query client for SQL Server!

Ah, but with what UI? Originally, I figured why not just the console? In an hour or so, I got a pretty simple client up that would send queries and parse up results into columns of plain text. Not too shabby!

My introduction to TUIs

But was that enough? Of course not! We need a true console app, whatever that is, that intelligently formatted results and allowed complex SQL edits, not just line by line input.

I started by wading into Spectre Console which allows you to style console text like mad and even has some neat widgets like this table, but it also says it's not really about complex UI management:

Thank you for posting an issue, but this goes beyond the scope of what Spectre.Console is, so this is not something that we will consider. I would recommend you to take a look at something like Textual or GUI.cs

Also all its widgets are output-minded, not truly interactive. Okay, fine. Fair.

Next up was Terminal.Gui, which seems to be a modern, C#-powered take on the sort of 1980s style DOS apps that you still find in backwater DMVs and tax preparer offices (I'm seeing a TurboTax commercial running during Celtics-76ers making fun of just such a UI now). Which is awesome! Except it was immediately buggy on macOS, and even when I got that working, the first text editing widget I tried allowed text entered to bleed into the widget under it. So that's a mess. Next?

I've played with C#'s Console objects a bit before. How tough can writing my own TUI views from scratch be?

Insanely. It's insanely tough. I've copiloted the heck out of this today, and, you know, it turns out writing a text editor in a console is a pita.

The toughest part? You have to manage all the scrolling. And since you're in a console, you lose all the built in cursor movement... if you want to insert instead of overwrite, that takes some work. And if you want to go to the end of the line, well, the macOS Terminal eats command-right arrow (which means "end") and uses that combo as a shortcut to go to the next terminal window instead.

Solutions? Well, rather than reinvent the wheel, I find that readline on Linux has a lot of solutions. Here are some good ones:

• C-b
  • Move back one character.
• C-f
  • Move forward one character.
• DEL or Backspace
  • Delete the character to the left of the cursor.
• C-d
  • Delete the character underneath the cursor.
• C-a
  • Move to the start of the line.
• C-e
  • Move to the end of the line.
• M-f
  • Move forward a word, where a word is composed of letters and digits.
• M-b
  • Move backward a word.

I'm starting to see why vi was born. Though apparently I'm also learning Emacs now too (the above shortcuts are the same in Emacs, maybe?).

So throw those in. Awesome! A little esoteric, but that'll do, pig.

Except the Meta ones aren't working...

Guess what?!

Why that happens

• On macOS Option by default produces alternate characters (Option+B → ∫ on many layouts).
• Terminal.app only treats Option as Meta if you enable "Use Option as Meta key" (or remap the key). Otherwise the terminal sends the Unicode character.
• .NET's Console reports what the terminal sends: a character with no ConsoleModifiers in this case.

How to get Meta-M (M-b) behavior instead

• Terminal.app → Preferences → Profiles → Keyboard → check "Use Option as Meta key" (or add a custom mapping to send ESC+b).

[thanks GPT-5 mini]

And there you have it. That's a lot learned today. Not sure I needed the history lesson or to write a scrollable text view for a rich console app, but I've taken on both, nonetheless.

Labels: c#, console, macOS, SQL Server

I was following a video from Pinal Dave called "Who Dropped Your Table?" and was getting a message that, "Currently, this report does not have any data to show, because default trace does not contain relevant information."

I googled for the reason why and guess what? The first hit (and answer) was on sqlauthority.com:

One of my clients of Comprehensive Database Performance Health Check recently asked me if there is a way to fix the error which they get when they try to open Schema Changes History. The error was related to Not Have Any Data to Show in the report. Let us see how we can fix that.

…

The solution of the same is very simple and I have previously blogged about it here: SQL SERVER – SSMS: Configuration Changes History.

Here is the solution:

1 2 3 4 5 6 7 8

EXEC sp_configure 'Show Advanced Options', 1; GO RECONFIGURE; GO sp_configure 'default trace enabled', 1 GO RECONFIGURE WITH override GO

When you run the above command, SQL Server will first enable the advanced options. Right following that, it will enable the default trace.

And now I too can tell who dropped tables on my database.

(I do worry a little that enabling this might be a significant performance hit, but I just set it on my local dev machine for now. The answer to who messed things up will, here at least, always be me. Hopefully!)

Labels: noteToSelf, SQL Server

From SQL Authority with Pinal Dave:

SQL Server Management Studio (SSMS) lets you configure the settings you want using a shortcut. Here is how you can do it.

Go to Options, then to Environment. Proceed to Keyboard, and from there, configure your T-SQL with the desired keyword.

A neat trick. After setting this up, you type a table name, highlight it, hit Ctrl-3 (or whatever you set up), and poof, the top 100 rows are up in Query Analyzer (or I guess it's all just SSMS these days).

Labels: noteToSelf, SQL Server

I had no idea T-SQL's CONVERT had special codes for converting dates. Well, it does.

From How to use ROW_NUMBER() to enumerate and partition records in SQL Server - CodeProject:

Here is our final SQL statement, which achieves the business logic we wanted to implement.

Hide   Copy Code

SELECT
       [PersonID]
      ,[FamilyID]
      ,[FirstName]
      ,[LastName]
      ,[DateOfBirth]
      ,ROW_NUMBER() over(PARTITION BY FamilyID,
                         CONVERT(NVARCHAR(25), DateOfBirth, 111)
                         ORDER BY DateOfBirth ASC) TwinCode
  FROM [People]
ORDER BY PersonID

IIn the ROW_NUMBER function above, I am doing several things. I’m grouping on FamilyID, and also grouping on a converted DateOfBirth. I convert the DateOfBirth to an nvarchar using the 111 conversion code, because that gets results like ‘2009/10/11′ and ‘2009/10/12′ which can easily be grouped by to achieve distinct dates. [emph mine -mfn]

The full list is here, at docs.microsoft.com. Here's a taste...

Without century (yy) (1)	With century (yyyy)	Standard	Input/Output (3)
-	0 or 100(1,2)	Default for datetime and smalldatetime	mon dd yyyy hh:miAM (or PM)
1	101	U.S.	1 = mm/dd/yy 101 = mm/dd/yyyy
2	102	ANSI	2 = yy.mm.dd 102 = yyyy.mm.dd

Labels: noteToSelf, SQL Server, t-sql

Argh. Fun day of setting up LocalDB.

First, you have to have it installed. Unfortunately, our friend http://downloadsqlserverexpress.com/ is busted, and one of the more promising links from MSDN is busted too.

If I'm remembering correctly, the new location seems to be here:
https://www.microsoft.com/en-us/download/details.aspx?id=54284

But the real sticking point for me is that the default connection string for LocalDB from the Web App Template in Visual Studio 2015U3 -- at least if you've installed LocalDB from SQL Server Express 2016 -- doesn't work. The real pain was that I trusted the template, but SQL Server Management Studio was working with LocalDB just fine.

Here are the steps I used to create the solution...

From microsoft.com:

• From the File menu, select New > Project.

• From the left pane, select Templates > Visual C# > Web.

• Select the ASP.NET Core Web Application (.NET Core) project template.

• Enter ContosoUniversity as the name and click OK.

  

Now if I did everything correctly (yes, I took the default name instead of changing it to ContosoUniversity, for instance), here's the appsettings.json file's connection string that came out of that template...

"DefaultConnection": "Server=(localdb)\\\\mssqllocaldb;Database=aspnet-WebApplication2-ac2d2954-804a-4157-821e-f0e494fdb334;Trusted_Connection=True;MultipleActiveResultSets=true",

... and here's what actually works...

"DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=BoodaPow;Trusted_Connection=True;MultipleActiveResultSets=true"

It's just an extra backslash, as far as I can tell. Not sure how I could've introduced that, but perhaps I did?

I ended up getting the correct format from here, which is the github repo for the completed Contoso U example I referenced earlier.

Enjoy. Your C:\Users\YourUserName\ folder is now full of mdf & tdf files, just like you wanted.

Labels: noteToSelf, SQL Server, visual studio

I've done these enough now that I'd would've thought I'd have accidentally memorized it already, but thanks to the ease with which you can learn the exact syntax of any concept stuck if your head with a half-second of googling, I hadn't yet.

Anyhow, SQL Server object checks are pretty easy. All you need to know is...

IF OBJECT_ID('name') IS NOT NULL

Now you can leave out the object type and use the above for anything in your db:

IF OBJECT_ID('myFreakinView') IS NOT NULL
    DROP VIEW myFreakinView;
GO

Or you can use object types to ensure you're really getting the object type that you're checking for, in this case, a view:

IF OBJECT_ID('myFreakinView', 'V') IS NOT NULL
    DROP VIEW myFreakinView;
GO

Seems I've listed these before...

SQ = Service queue
TA = Assembly (CLR) DML trigger
TF = SQL table-valued-function
TR = SQL DML trigger
TT = Table type
U = Table (user-defined)
UQ = UNIQUE constraint
V = View
X = Extended stored procedure

In other news, my Y700 is supposed to show up today. Ordered yesterday, free shipping option, on the truck this morning. Not too shabby. Guess that's what ordering the preconfigured loadout does for you. Of course I don't think I'll be home when it arrives. ARGH. Oh well.

Labels: noteToSelf, SQL Server

One thing I've constantly wondered about with ORMs is why they use different mental models than SQL, the thing they're almost always abstracting. We hire for SQL proficiency, but then immediately throw it away with complex edge case stuff. You know, like LEFT OUTER JOINs. /sarcasm

From MSDN:

The first step in producing a left outer join of two collections is to perform an inner join by using a group join....

The second step is to include each element of the first (left) collection in the result set even if that element has no matches in the right collection. This is accomplished by calling DefaultIfEmpty on each sequence of matching elements from the group join.

Yes, you heard that right. To perform a left outer join, you perform an inner join into a join table, then fill that join table in with default (null?) values if there's no inner join match.

Wth? Their example:

var query = from person in people
    join pet in pets on person equals pet.Owner into gj
    from subpet in gj.DefaultIfEmpty()
    select new { 
        person.FirstName, 
        PetName = (subpet == null ? String.Empty : subpet.Name) 
    };

I'm tempted just to write a view and use that instead. This is stupid. The syntax really should be...

var query = from person in people
    left outer join pet in pets 
        on person equals pet.Owner
    select new { 
        person.FirstName, 
        PetName = subpet.Name ?? string.Empty
    };

I don't get it. I wonder how hard it'd be to write an extension that does that...

Labels: ef, noteToSelf, SQL, SQL Server

If you've been using Visual Studio, but bounce in and out of SQL Server Management Studio, and all of a sudden hitting F5 is opening up a file selection dialog asking where you want to save your *.rpt file, I think I know what happened.

Did you just search for something?

Argh, annoying.

Labels: noteToSelf, SQL Server

Thank you very much:

There are cases where the local cache used by IntelliSense becomes stale. Refreshing the cache is easy but not necessarily obvious. There are two ways to refresh the cache: 1) Go to Edit -> IntelliSense -> Refresh Local Cache and 2) Hit Ctrl+Shift+R

There are times that the Intellisense for SQL Server Management Studio gets horribly out of sync with what's in the database now, especially when you're creating new databases and/or scads of tables with SQL. I'm not sure why that'd be, as you'd think the Intellisense within a SQL client would be able to keep up with changes made by that same client, for heavens sake, but it often-to-usually doesn't.

Wish I'd known about this cache refreshing sooner.

Labels: noteToSelf, SQL Server

type char(2)
 

Object type:
AF = Aggregate function (CLR)
C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
FN = SQL scalar function
FS = Assembly (CLR) scalar-function
FT = Assembly (CLR) table-valued function
IF = SQL inline table-valued function
IT = Internal table
P = SQL Stored Procedure
PC = Assembly (CLR) stored-procedure
PG = Plan guide
PK = PRIMARY KEY constraint
R = Rule (old-style, stand-alone)
RF = Replication-filter-procedure
S = System base table
SN = Synonym
SO = Sequence object 

Applies to: SQL Server 2012 through SQL Server 2016.
SQ = Service queue
TA = Assembly (CLR) DML trigger
TF = SQL table-valued-function
TR = SQL DML trigger
TT = Table type
U = Table (user-defined)
UQ = UNIQUE constraint
V = View
X = Extended stored procedure

IF OBJECT_ID('TableName', 'U') IS NOT NULL
DROP TABLE TableName;

Labels: noteToSelf, SQL, SQL Server, t-sql

I was trying to install SQL Server Express so I wouldn't have to connect to our dev database, but ran into the dreaded "Setup account privileges" issue (image above, I think). If you're having this problem installing SQL Server Express, I can't guarantee that you're going to get great news here, but this is how you'd fix it if you could...

You can open the Local Security Policy interface (or the Group Policy editor) and add the permissions you're missing from there, but the buttons to add to the ones I needed were disabled for me, even though I'm an admin on my box.

This is apparently known as the HasSecurityBackupAndDebugPrivilegesCheck rule.

The best "how to nuke this issue" I found was from this kb article from MS:

To work around this issue, run the following command at an elevated command prompt to set explicit permissions of the SeBackupPrivilege user right for the msiserver service:

C:\Windows\system32>sc privs msiserver SeTcbPrivilege/SeCreatePagefilePrivilege/SeLockMemoryPrivilege/SeIncreaseBasePriorityPrivilege/SeCreatePermanentPrivilege/SeAuditPrivilege/SeSecurityPrivilege/SeChangeNotifyPrivilege/SeProfileSingleProcessPrivilege/SeImpersonatePrivilege/SeCreateGlobalPrivilege/SeAssignPrimaryTokenPrivilege/SeRestorePrivilege/SeIncreaseQuotaPrivilege/SeShutdownPrivilege/SeTakeOwnershipPrivilege/SeLoadDriverPrivilege/SeBackupPrivilege

Now that'll do it. Well, unless you can't.

I can't.

C:\Windows\system32>whoami /priv

PRIVILEGES INFORMATION

Privilege Name           Description                        State  
==============           ===========                        ========  
SeIncreaseQuotaPrivilege Adjust memory quotas for a process Disabled
SeSecurityPrivilege      Manage auditing and security log   Disabled
...
SeBackupPrivilege Back up files and directories Disabled
...

Argh. Oh well. It's always a little painful when you have enough power to really get your box into trouble, which I do, but then you can't install something that'd actually help you do your job, making things better for everyone. /sigh But if I could've installed Express, that's how I could've fixed the SeDebugPrivilege, SeBackupPrivilege, etc issue.

(Note that using ntrights is apparently the wrong answer on Windows 7. `'ntrights' is not recognized as an internal or external command, operable program or batch file.')

Labels: noteToSelf, SQL Server

Getting the text of existing sprocs is apparently pretty easy: EXEC sp_helptext N'sp_get_composite_job_info';

Voila.

I've been having trouble with ordering the results of a stored procedure, probably by putting results in a temp table. Seeing, in this case, the code to create the temp table the sproc's giving back should be useful.

Although, in my case, no dice. I ended up cheating and trivially rewriting the sproc and the sproc it called.

sp_help_job calls sp_get_composite_job_info, which ends with a statement with its own ORDER BY, which is all I was interesting in changing.

So a quick change there...

-- ...
FROM @filtered_jobs fj
LEFT OUTER JOIN msdb.dbo.sysjobs_view sjv ON (fj.job_id = sjv.job_id)
LEFT OUTER JOIN msdb.dbo.sysoperators so1 ON (sjv.notify_email_operator_id = so1.id)
LEFT OUTER JOIN msdb.dbo.sysoperators so2 ON (sjv.notify_netsend_operator_id = so2.id)
LEFT OUTER JOIN msdb.dbo.sysoperators so3 ON (sjv.notify_page_operator_id = so3.id)
LEFT OUTER JOIN msdb.dbo.syscategories sc ON (sjv.category_id = sc.category_id)
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
--ORDER BY sjv.job_id
ORDER BY name
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

... and a quick change in `sp_help_job`...

-- Generate results set...
--EXECUTE sp_get_composite_job_info @job_id,
EXECUTE WACK_sp_get_composite_job_info @job_id,
    @job_type,
    @owner_login_name,
    @subsystem,
    @category_id,
    @enabled,
    @execution_status,
    @date_comparator,
    @date_created,
    @date_last_modified,
    @description

... and I'm working. (Or I could have just overwritten spgetcompositejobinfo with one that sorted different, but that's obviously destructive, and usually A Very Bad Idea.)

Labels: sproc, SQL, SQL Server

[Update May 2009] My collegue found a good tool online which could be very helpful for users to isolate issues related to this error message. You can download PortQry from http://support.microsoft.com/kb/832919, run "portqry.exe -n yourservername -p UDP -e 1434". If this command returns information and it contains your target instance, then you can rule out possiblity 4) and 5) above, meaning you do have a SQL Browser running and your firewall does not block SQL Browser UDP packet. In this case, you can check other issue, e.g. wrong connection string. [bold mine -mfn]

Labels: noteToSelf, SQL Server

Labels: noteToSelf, SQL Server

Sorry, that was a PEBKAC (Problem Exists Between User And Chair)

All I needed to do was turn on "auto commit" in SQL Developer.
Tools -> Preferences
Click next to Database,
Select Worksheet Parameters
Click checkbox to mark "Autocommit in SQL Worksheet"
Close SQL Developer and re-launch.

Problem solved.

Labels: oracle, problem solved, SQL Server

If you select Windows Authentication, Setup creates an sa account that is disabled by default. To use Mixed Mode Authentication and activate the sa account after Setup completes, see the How to: Change Server Authentication Mode and ALTER LOGIN (Transact-SQL) topics in SQL Server 2005 Books Online.

If Windows Authentication mode is selected during installation, the sa login is disabled. If you later change authentication mode to SQL Server and Windows Authentication mode, the sa login remains disabled. To enable the sa login, use the ALTER LOGIN statement.
...
To enable the sa login by using Transact-SQL

1. Execute the following statements to enable the sa password and assign a password.

ALTER LOGIN sa ENABLE ;
GO
ALTER LOGIN sa WITH PASSWORD = '<password>' ;
GO

Sql Express is going to be a good match for the Sql Server 2005 JDBC provider, it is free, it is relatively easy to set up and deploy and it has all the power of Sql Server 2005 for low end applications.

There are a few things to keep in mind when using Sql Express with the JDBC driver:

· You need to enable mixed mode security when you install the server so that you can connect using user name and password.
· Make sure that the sqlbrowser is enabled. Sql Server configuration Manager ->SQL Server 2005 Services ->SQL Server Browser ->Start
· The JDBC provider only works with the TCP/IP protocol which is disabled by default on Sql Express. You need to enable the TCP/IP Protocol from the SQL Server Configuration Manager that ships with SqlExpress and re-start the server. Look under SQL Server 2005 Network Configuration -> Protocols for SQLEXPRESS-> TCP/IP->Enable.

Labels: FUN, jdbc, SQL Server