SQL Native Client

Sorry for the information overload at the beginning of this blog. I've got 12 years of CNC stories (and some development stories) to tell just to catch up!

Anyway, let's get down to business and talk about the SQL Native Client. Not a whole lot to say here, but it's pretty important. The bottom line with that is you have to make sure the native client matches across all the machines you're using - including the database server.

That's right - the client has to be installed on the database server. It is a separate product and has to be on all the servers and workstations. I bet if you go look, you'll find a difference in the client versions between your machines. One client I had did not make their native client version match and they were getting 12 zombies a day. Once they fixed that, the zombies went away completely.

Another thing about SQL- E1 normally is compatible with all service packs and cumulative updates. What, you don't know about cumulative updates? Oh yeah, they're out there and you have to search for them. Not only that, most of those updates are not available for download. You have to request the update from Microsoft and they'll email you a link to download. I think SQL is up to update 10 now or some such thing, so go check it out.

In summary, just make sure SQL is up-to-date and all your native client version match. Just fixing that one little thing will make you system run smoother.

Change Table Owners in SQL

If you're doing a path code refresh, you know it's faster just to backup and restore the databases through SQL rather than use R98403. What I've found is a lot of CNCs still use R98403 because they're not sure how to straighten things out after a restore.

You see, when you back up JDE_PRODUCTION (for example) and restore it as JDE_CRP, it doesn't bother to change the owners. The R98403 will do that for you, but it will take three weeks to finish it.

I found this awesome script that will change all the table owners quick and easy. I got it from Scott Forsythe over at http://weblogs.asp.net/owscott/

DECLARE @old sysname, @new sysname, @sql varchar(1000)

SELECT

@old = 'OldOwner_CHANGE_THIS'

, @new = 'NewOwner_CHANGE_THIS'

, @sql = '

IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES

WHERE

QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) = ''?''

AND TABLE_SCHEMA = ''' + @old + '''

)

EXECUTE sp_changeobjectowner ''?'', ''' + @new + ''''

EXECUTE sp_MSforeachtable @sql

This script will work in SQL 2005 and SQL 2008. It's the greatest!