Log Dagnabit 2!

A quick and dirty followup to my earlier posting
http://billfellows.blogspot.com/2009/01/log-dagnabit.html but now with
pictures

An invalid parameter or option was specified for procedure 'sys.sp_change_users_login'

Ran into this one today. Restored a database with a local user
account and the SIDs didn't align. Simple matter to fix, run
sp_change_users_login with auto_fix and I'm done.
*clickety*clickety*click* Boom

"
EXECUTE sp_change_users_login @action = 'auto_fix', @UserNamePattern =
'SQL_USER'

Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 207
An invalid parameter or option was specified for procedure
'sys.sp_change_users_login'.
"

I ran this a few days ago against the same restore and it worked.
Same command even since I'm lazy and have a cheatsheet of infrequently
used but needed TSQL. A quick lap around google found similar people
having problems but no resolution. Invalid parameter... I tried
changing the case of auto_fix to all lower, all upper but no such
luck. I tried it by explicitly specifying the missing parameters to
no avail.

Being a simple man and not well versed in SQL Server users,
principals, etc I decided to try the simple things first. When I ran
the original command, I was in the sales database. I changed into the
master database and reran. Low and behold, it worked. Where it gets
weird is the command doesn't throw the invalid parameter or option
error if I run it against the parts database. I'm sure once I get
versed in securables in SQL Server, this will all make sense but for
now, I'm documenting it so I remember.

Reference
http://technet.microsoft.com/en-us/library/ms174378.aspx
http://blog.sqlauthority.com/2007/02/15/sql-server-fix-error-15023-user-already-exists-in-current-database/

RDC & dual monitors

Using multiple monitors can cause weirdness when you RDC (remote
desktop connection) into that same desktop. In particular, I notice
that things like the file windows dialog can show up in a non-visible
display area if the app was running on the second monitor. While
there may be other solutions out there, the one I came up with is to
close the application and re-open it in the RDC session. Windows
figures out where to display it correctly from then on.

And back to production support.

I'll take Linked Servers for Fail, Alex!

The fail could actually be on my part and I'm fine with it. I'm just looking for the answers and couldn't be terse enough to fit in a tweet.

We have a fairly sizable project at work and they are looking at using cross database queries in them. I'm not a fan of them, mostly on the Wookie's rationale of you can't just restore one database to get something working.

The scenario I'm struggling with is our database names change per environment. e.g. SLSDEVDB, SLSTESTDB, SLSLOADDB, SLSSTAGEDB, SLSPRODDB. In my simple mind, if I'm writing a cross database, henceforth abbreviated as xdb, query, it would take the form of "SELECT * FROM SLSDEVDB.dbo.Sales" in the development environment but when it got to production, it'd be "SELECT * FROM SLSPRODDB.dbo.Sales" We obviously don't want to change the database name per environment as someone would bone a reference and crash production. Architecture has suggested we use linked servers to overcome this challenge. By creating a link/alias to the sales database our code can be uniform across environments. "SELECT * FROM SLSDB.dbo.Sales" That's not a bad idea for once. Use linked servers to reference to the same box but allow us to alias the database.

The devil's in the details of course as I can't get that to work. I can get "SELECT SLSDB.WEBDEVDB.dbo.Sales" to work but that's aliasing the server, not the database name. Since their documentation basically ends with "and all these problems are solved with linked servers sp_addlinkedserver ..." perhaps there is a permutation I'm not seeing.

-- add a loopback linked server

EXECUTE sp_addlinkedserver   
,   @server='SLSDB'
,   @srvproduct=''
,   @provider='SQLNCLI'
,   @datasrc='localhost'
,   @catalog='SLSDEVDB' 

--An invalid schema or catalog was specified for the provider "SQLNCLI" for linked server "SLSDB".
SELECT top 1 * FROM SLSDB...Sales
--Invalid object name 'SLSDB..Sales'.
SELECT top 1 * FROM SLSDB..Sales
--Invalid object name 'SLSDB.dbo.Sales'.
SELECT top 1 * FROM SLSDB.dbo.Sales
--Invalid object name 'SLSDB.Sales'.
SELECT top 1 * FROM SLSDB.Sales
-- This works but is useless as it does not address the changing database names
SELECT top 1 * FROM SLSDB.SLSDEVDB.dbo.Sales
--An invalid schema or catalog was specified for the provider "SQLNCLI" for linked server "SLSDB".
SELECT top 1 * FROM SLSDB..dbo.Sales
-- An invalid schema or catalog was specified for the provider "SQLNCLI" for linked server "SLSDB".
SELECT top 1 * FROM SLSDB.SLSDEVDB..Sales

Maybe it'll work if I create a user and login, default them to the specified catalog and add a linked server login but that seems like more hassle than it'll be worth. It's certainly not called out in their solution so I'm inclined to believe they never tested it.

Comments, solutions, etc always appreciated.