Monday, February 18, 2008

Question: DB from SQL 2005 to SQL 2000

When initially trying to migrate SharePoint databases from the same machine that is running the whole FARM, I got this error:

SqlException: Too many backup devices specified for backup or restore; only 64 are allowed. RESTORE DATABASE is terminating abnormally.

Afer researching this, I discovered that it should work after upgrading the SQL 2000 engine to SP4 (as we had SP3). Over the weekend, we upgraded our production database to SP4. Then I tried the SharePoint restore to that production database again
(using stsadm -o restore -directory \\server2\disks\sharepointbackup -restoremethod new and applying the new db server name, server db and log file location, and then the new db names). The new error is:


SqlException: The backed-up database has on-disk structure version 611. The server supports version 539 and cannot restore or upgrade this database. RESTORE DATABASE is terminating abnormally.

If anyone else is having a similary issue and knows how to get around this, please post a comment or email me.

Friday, February 15, 2008

osql query to text file - thanks to Brain Knight


Piping a Query to a Text File
By Brian Knight, 2001/05/21

The case where one has to send the results of a query to a text file comes up quite often. General this case take the form of a maintenance script that you run through Agent nightly and would like to see the results in the morning.
In the above example, you can create a CMDEXEC job to run the below command. This will output the results of the query inside c:\inputquery.txt to a separate file.


\mssql7\binn\osql -Usa -Ppassword -Ic:\inputquery.txt -Oc:\outputdir\output.txt


You can also use -E switch for trusted connection if you don't want to hard code a password into your job. From an Query Analyzer window or inside the stored procedure you can run the above statement with an extended stored procedure called XP_CMDSHELL:


master..xp_cmdshell 'osql
-Usa
-Pmisty
-dNorthwind
-Q"select * from products"
-oc:\output.txt'


The -Q parameter in the above syntax runs the query then exits ISQL. You may also want to add the additional NO_OUTPUT parameter at the end of query as shown below to supress any output to the client:


master..xp_cmdshell 'osql
-Usa
-Pmisty
-dNorthwind
-Q"select * from products"
-oc:\output.txt', no_output


There are other methods of piping a query to a text file by using OLE Automation but they are much more complex than this method.


By Brian Knight, 2001/05/21


usage: osql [-U login id] [-P password]
[-S server] [-H hostname] [-E trusted connection]
[-d use database name] [-l login timeout] [-t query timeout]
[-h headers] [-s colseparator] [-w columnwidth]
[-a packetsize] [-e echo input] [-I Enable Quoted Identifiers]
[-L list servers] [-c cmdend] [-D ODBC DSN name]
[-q "cmdline query"] [-Q "cmdline query" and exit]
[-n remove numbering] [-m errorlevel]
[-r msgs to stderr] [-V severitylevel]
[-i inputfile] [-o outputfile]
[-p print statistics] [-b On error batch abort]
[-O use Old ISQL behavior disables the following]
batch processing
Auto console width scaling
Wide messages
default errorlevel is -1 vs 1
[-? show syntax summary]

Thursday, February 14, 2008

Petit Jean National State Park, 2006

Here are my boys, my daughter, and 2 of my neices and 2 of my nephews. This pic was taken at Petit Jean Ntl Park during the winter of 2006. Here's a mountaintop view of the beautiful falls there.

IPTV at the Coppock Household

The day has finally arived! A box shipped today from the company we've chosen to replace Charter Communication as our TV entertainment provider. We chose Sky Angel (www.skyangel.com) due to the now available Family Package along with their Faith Package. We were blowing through about $80/month to have digital cable (expanded basic). Now, through our broadband connection, we are watching TV over our home network. (They even have the Miliary Channel and NFL Network!) And the best part so far is that the commercials are not offensive, violent, sexually suggestive, or in any way a thing that would cause myself or my wife to yell out, "Close your eyes!" or "Don't look!" We are a family that loves God, and chooses to please Him. We couldn't be happier with our choice to drop cable and go with IPTV. If you are interested, you'll look at the price difference. I don't want to post them; I am not trying to be as salesman for them.

IIS 7.0 Config change

This command-line execution will correctly configure your IIS settings after they get hosed by inadvertent changes or applications that make unwanted changes.

From: Drew Coppock
Sent: Wednesday, November 07, 2007 3:27 PM
To: Drew Coppock
Subject: IIS 7.0 Config change


RUN:
C:\Windows\system32\inetsrv\APPCMD.EXE set app "Default Web Site/" /applicationPool:"Classic .NET AppPool"

Should see
APP object "Default Web Site/" changed

Did I mention I am a Cubmaster?

Yep, it's true. I am the Cubmaster for Agape Church Cub Scout Pack 70 in Little Rock, Arkansas. Need to ask me about this? email me.
Check out the BLOG for our Pack - http://quapawpack70.blogspot.com/
Check out the BLOG for our Troop - http://quapawtroop70.blogspot.com/

SQL 2005 User migration script

I found this script very useful when migrating databases from 2000 to 2005. The security accounts were successfuly imported with the tables and views, but none of them worked properly. This script fixed things in a jiffy!

----- Begin Script, Create sp_help_revlogin procedure -----


USE master

GO

IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL

DROP PROCEDURE sp_hexadecimal

GO

CREATE PROCEDURE sp_hexadecimal

@binvalue varbinary(256),

@hexvalue varchar(256) OUTPUT

AS

DECLARE @charvalue varchar(256)

DECLARE @i int

DECLARE @length int

DECLARE @hexstring char(16)

SELECT @charvalue = '0x'

SELECT @i = 1

SELECT @length = DATALENGTH (@binvalue)

SELECT @hexstring = '0123456789ABCDEF'

WHILE (@i <= @length) BEGIN DECLARE @tempint int DECLARE @firstint int DECLARE @secondint int SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1)) SELECT @firstint = FLOOR(@tempint/16) SELECT @secondint = @tempint - (@firstint*16) SELECT @charvalue = @charvalue + SUBSTRING(@hexstring, @firstint+1, 1) + SUBSTRING(@hexstring, @secondint+1, 1) SELECT @i = @i + 1 END SELECT @hexvalue = @charvalue GO IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL DROP PROCEDURE sp_help_revlogin GO CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS DECLARE @name sysname DECLARE @xstatus int DECLARE @binpwd varbinary (256) DECLARE @txtpwd sysname DECLARE @tmpstr varchar (256) DECLARE @SID_varbinary varbinary(85) DECLARE @SID_string varchar(256) IF (@login_name IS NULL) DECLARE login_curs CURSOR FOR SELECT sid, name, xstatus, password FROM master..sysxlogins WHERE srvid IS NULL AND name <> 'sa'

ELSE

DECLARE login_curs CURSOR FOR

SELECT sid, name, xstatus, password FROM master..sysxlogins

WHERE srvid IS NULL AND name = @login_name

OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd

IF (@@fetch_status = -1)

BEGIN

PRINT 'No login(s) found.'

CLOSE login_curs

DEALLOCATE login_curs

RETURN -1

END

SET @tmpstr = '/* sp_help_revlogin script '

PRINT @tmpstr

SET @tmpstr = '** Generated '

+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'

PRINT @tmpstr

PRINT ''

PRINT 'DECLARE @pwd sysname'

WHILE (@@fetch_status <> -1)

BEGIN

IF (@@fetch_status <> -2)

BEGIN

PRINT ''

SET @tmpstr = '-- Login: ' + @name

PRINT @tmpstr

IF (@xstatus & 4) = 4

BEGIN -- NT authenticated account/group

IF (@xstatus & 1) = 1

BEGIN -- NT login is denied access

SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''

PRINT @tmpstr

END

ELSE BEGIN -- NT login has access

SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''

PRINT @tmpstr

END

END

ELSE BEGIN -- SQL Server authentication

IF (@binpwd IS NOT NULL)

BEGIN -- Non-null password

EXEC sp_hexadecimal @binpwd, @txtpwd OUT

IF (@xstatus & 2048) = 2048

SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'

ELSE

SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'

PRINT @tmpstr

EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name

+ ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '

END

ELSE BEGIN

-- Null password

EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name

+ ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '

END

IF (@xstatus & 2048) = 2048

-- login upgraded from 6.5

SET @tmpstr = @tmpstr + '''skip_encryption_old'''

ELSE

SET @tmpstr = @tmpstr + '''skip_encryption'''

PRINT @tmpstr

END

END

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd

END

CLOSE login_curs

DEALLOCATE login_curs

RETURN 0

GO

----- End Script -----

To make SQL Server Express 2005 work in Management Studio

1. Run SAC (Surface Area Configuration)
2. Add authority for user
3. Run Configuration Manager
4. Set to run as Local System, not Network Service
5. Should restart automatically

Wednesday, February 13, 2008

Twilight2K

My younger brother and his 2 oldest sons play Twilight 2000 with me. We will post some gaming details here.

Welcome!

Greets! I am excited to have finally gotten started in the whole blogging world. This has all happened at the bequest of a certain blogger I met just today. (Robin Meuré) I say met, but actually, I just IM'd with him. He asked me if I had a blog. I had to admit reluctantly that I had not taken that step as yet. His is glorix.blogspot.com.

I will be posting on different topics on this blog. Topics will include my professional life and skills I have developed, as well as important issues to me (like family life).

My professional life has given me the opportunity to grow in many different areas, but not excel enough to be a guru in any area. I have been doing HTML since 1997, a skill I picked up while killing time at the University of Central Arkansas' Computer Science Unix Lab. In 1998, I started an internship with Acxiom Corp (http://www.acxiom.com/) working on their Y2k intranet. In 1999, I started full time with them in an R&D group, doing VBScript (ASP) and Javascript to provision a site for their much-touted Acxiom Data Network. While working, I picked up some T-SQL skills, and SQLServer skills, but not enough to succeed at being a DBA. (Which just happened to be my next gig.) In 2003, I moved on to work for a 1-man shop doing T-SQL, DTS Packages, and batch scripts for automation as a consultant with Alltel. After 6 months of this, I moved on to a small company in the area who shut their doors after I worked for them for 2 weeks. I then moved on to work as a consultant for the state as an area coordinator. While doing this work, I maintained the web site and database. I stuck with this consultancy role far too long. I ended up being hired by the state in 2005 to fill a role that was not IT related. I just considered all my IT skills a bonus. So did the director. In total, I filled that role for a year and a half. During that time, I was able to attend an ASP.Net training class at Complete Computing (http://www.complete.com/). I left this role for the opportunity to be a consultant for this division, but according to the lawyers, a state employee may not leave the employ of the state and subsequently enter into a consultancy until after a 12 month time period has passed. So, I moved on to my next gig; working for a school district's IT department, doing ASP, ASP.Net, T-SQL, Javascript, HTML, and heavy DHTML. I moved on after 6 months of fun. Since 2006, I've been with a company serving as a Solutions Developer, working on VBA, VB.Net, ASP.Net, DotNetNuke, T-SQL, DTS Packages, SQLServer 2000 and 2005, as well as my HTML/DHTML and Javascript.

That's enough about work. Now for a little about my family. I've been married to my wife since 1995, and we have 2 boys who are 9 and 6. We also have a daughter who is 17. She is mine, but many people say she looks enough like my wife that folks cannot tell. All of my family currently live in Arkansas. My Mom lives a bit more than an hour away in North Central Arkansas. My Dad lives in Conway, my sister and her family live in Conway, and my younger brother and his family live in Conway. My older brother and his family live west of Little Rock. So I can visit any of my family within about an hour drive. We are a very close family, and we celebrate our closeness as often as we can. We enjoy food. Can I say that again? We enjoy putting a feed on! I may end up posting some of our favorite recipies here, too! (Not too many, though!) This is a guy's blog, after all.

Enough of the introductions. I'll post some more later.
Thanks for visiting!