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.
Monday, February 18, 2008
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
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/
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 -----
----- 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
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!
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!
Subscribe to:
Posts (Atom)