Formatting SQL Server TSQL with Oracle's SQL Developer

Note that in the time since I wrote this post, another (much better) option for formatting SQL Server script has come up.
If you do much troubleshooting on Microsoft SQL Server, you inevitably will end up having to deal with a poorly-formatted (and hard-to-read) SQL statement from a query using sys.dm_exec_sql_text or the like.

There are lots of online formatters out there, though I've had decidedly mixed results with them. There also are a lot of add-in and standalone products available that will do a good at this. Here's another one to add to your list, until SSMS includes a formatting feature: Oracle's SQL Developer.

SQL Developer is a free download (here) that will, in fact, connect to SQL Server instances. While I do not use it for my day-to-day SQL Server administration tasks, I use it regularly to reformat SQL that I've pulled from the DMVs.

How to Change the Owner of All SQL Agent Jobs in a SQL Server Instance

Each job in a SQL Server instance has an owner, and you may run into a situation in which that owner needs to be changed.
If there are a lot of jobs that were created by that owner, this can be a tedious task.
Here we’re opening a cursor and looping through the SQL Agent Jobs in the instance that are owned by the old user (@olduser) and executing the sp_update_job stored procedure to change that to match @newuser.

USE MSDB
GO
declare @jobname varchar (200) declare @oldusername varchar (30) declare @newusername varchar(30) set @oldusername='DOMAIN\oldusername' set @newusername='DOMAIN\newusername' declare cur_jobname cursor LOCAL for select name from sysjobs where suser_sname(sysjobs.owner_sid) =@oldusername open cur_jobname fetch next from cur_jobname into @jobname While @@FETCH_STATUS = 0 begin
EXEC msdb.dbo.sp_update_job @job_name=@jobname, @owner_login_name=@newusername fetch next from cur_jobname into @jobname end close cur_jobname deallocate cur_jobname

How to Change the Owner of All Databases in a SQL Server Instance

Each database in a SQL Server instance has an owner, and you may run into a situation in which that owner needs to be changed. One example of this would be a case when a DBA moves departments, but stays in the organization. In that case, the account would still be active, but you’d probably want to change the database owner.
If there are a lot of databases that were created by that owner, this can be a tedious task.
Here, we’re opening a cursor and looping through the databases in the instance that are owned by the old user (@olduser) and executing the sp_changedbowner stored procedure to change that to match @newuser.

USE MASTER
GO
declare @dbname varchar (50)
declare @oldowner varchar (30)
declare @newowner varchar (30)
declare @sql varchar (300)
set @oldowner='DOMAIN\oldusername'
set @newowner='DOMAIN\newusername'
SET @sql=''
declare cur_dbname cursor LOCAL
for SELECT name
FROM master.sys.databases where SUSER_SNAME(owner_sid)=@oldowner
open cur_dbname
fetch next from cur_dbname
into @dbname
While @@FETCH_STATUS = 0
begin
set @sql='exec ['+@dbname+'].sys.sp_changedbowner ''' + @newowner + ''''
-- PRINT @sql
EXEC (@sql)
fetch next from cur_dbname
into @dbname
end
close cur_dbname
deallocate cur_dbname

AD Authentication with RHEL 6

We’ve been using AD authentication with our RHEL and CENTOS 4 and 5 systems for some time, now, so I was anxious to see what kinds of changes might have come up with RHEL6. Not much, happily, but there was one change that took a little while to figure out. We’ll run through all the steps, from beginning to end, here.

Using Error (and other multiple) Paths in SSIS

SSIS provides for multiple paths between tasks.  Very helpful stuff; here we’ll look at a simple solution to a common scenario.
Below is a job that stops a blocking service, reads from the source DB (SQL CE) file, truncates the destination table, and then copies the data from the source to destination.  Finally, it starts the service again.

What if, however, our test job fails after stopping the service?  We can provide for failure notification through SQL Agent jobs, but wouldn’t it be nice, also, to have the service start again, even after a failure?
We’ll set that up using failure paths, such that the tasks will go directly to the start service task in the event of a failure.

Creating a SSIS SQL Compact Data Source

There’s no out-of-the-box SQL Compact data source in SSIS, which presents a problem when you’re needing to copy data from a SQLCE data file.

 It turns out, though, that it’s easy to repurpose a OLEDB connection to read from a SQL Compact DB.

Connection Failed error message with PeopleTools Change Assistant on 64 Bit Windows

With PeopleTools 8.5, Oracle moved the app servers into 64-bit territory. That was welcome news. Unfortunately, the whole stack isn't quite there, yet, and we ran into an irritating problem when running Change Assistant to upgrade from 8.49 to 8.51. Specifically, when setting up the environment, we recevied a "connection failed!" error when testing the connection. Here's what we saw: No logs, no details. Just failed. But then, finally, a hint: Data mover (which shouldn't connect, since the database is still at 8.49) wouldn't even run: it failed with an error "missing or invalid version of sql library psora". Aha! Now that is something one can work with. It turns out it needs the 32-bit Oracle client. Install that, and everything is good, again.

How to Delegate Services control in Windows

Microsoft offers a very helpful document here and here detailing how to use subinacl to give control over a service to a user. Unfortunately, they’ve not updated that article in quite some time, and it’s now out of date: beginning with Windows Server 2003 SP1, authenticated users no longer can enumerate services.
While that’s a good thing, it renders the solution presented by Microsoft only partially complete.
So we’ll correct that, going through all the steps that are necessary to give an (otherwise) unprivileged user permissions to control any given services through the services control panel. This will work on Windows Server through v2008 R2.

Linux error id: cannot find name for user ID xxxxx when using Domain Authentication

We recently had a problem, after re-doing some samba configurations on RHEL 5, in which a user would log in (successfully), but then be presented with the follow errors:

id: cannot find name for user ID 10001
id: cannot find name for group ID 10000
id: cannot find name for user ID 10001

Of course, none of our domain ACLs worked for this user, either, which was a real problem. Finally, after running through the more obvious problems (communication with domain controllers: verified with wbinfo; uid and gid allocation and linking: set explicitly with wbinfo; winbind cache (cleared, both in /var/cache/samba and /var/lib/samba); date/time discrepancies; domain membership), we found the culprit: file permissions.

NT_STATUS_PIPE_DISCONNECTED with Samba Winbind and Windows Server 2008 R2 Domain Controller

We recently upgraded our domain controllers to Windows Server 2008 R2, and our RHEL 5 authentication through our Windows domain immediately broke.
Here was the error:

[2010/06/21 09:32:57, 0] rpc_client/cli_pipe.c:rpc_api_pipe(790) rpc_api_pipe: Remote machine adserver.my.edu pipe \NETLOGON fnum 0x8007returned critical error. Error was NT_STATUS_PIPE_DISCONNECTED

A little searching online shows a lot of people with this or related problems, but the solutions appear to be many, and there mostly isn’t a solution posited.
But there is this: https://bugzilla.redhat.com/show_bug.cgi?id=561325
In short: there's a bug in the samba package that prevents it from working with Windows Server 2008 R2 domains. If you’re running into this problem, the solution is to remove your existing samba installation and install, instead, the samba3x packages.
Note that samba3x was a "technology preview" from RedHat, which means that it offered little support for it. This has changed, and it's now a supported package in RHEL 5.

yum erase samba samba-common
yum install samba3x samba3x-client

You’ll have to re-do your configuration, so it might be worthwhile to back up your /etc/samba/smb.conf file.