Skip to main content

Grailsify legacy ms sql database

I had a problem. I'm developing using grails but my constraint is that I have to use a legacy ms sql database that was created by importing data from excel to access to ms sql. Thus the tables do not have any id field or primary key. And not only that, once I've done my work on my pc, and I do a full dump onto the server using MS own "SQL Server Import and Export Wizard 2008" it does not retain the identity property of the id field (that's auto-increment for all you lucky mysqlers). So when grails try to create a new record it fails. So so sad. But after a few days of depressive wanderings I decided to be a man and solve this. So here's the script in full, of how you would automatically create an id if the table does not have it yet, and if already has, will do the whole recreate process to make the id an identity again. Hope it would benefit someone.

declare @tablename varchar(1000),@importnew nvarchar(1000),@addconstraint nvarchar(1000),@dropold nvarchar(1000),@dorename nvarchar(1000),@dname nvarchar(1000),@dquery nvarchar(1000)
declare tables cursor for select TABLE_NAME from INFORMATION_SCHEMA.TABLES
open tables
fetch next from tables into @tablename
while @@FETCH_STATUS = 0
begin
print 'processing ' + @tablename
if exists(select column_name from INFORMATION_SCHEMA.columns where table_name = @tablename and column_name = 'idold')
begin
set @dropold = 'alter table '+ @tablename +' drop column idold'
exec sp_executesql @dropold
end
if not exists(select column_name from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = @tablename)
begin
if exists(select column_name from INFORMATION_SCHEMA.columns where table_name = @tablename and column_name = 'id')
begin
set @dorename = 'exec sp_rename "' + @tablename + '.id", "idold", "column"'
exec sp_executesql @dorename
set @importnew = 'select identity(numeric(19,0)) as id,* into ' + @tablename +'_tmp from ' + @tablename + ' where 1=0'
exec sp_executesql @importnew
set @dname = null
select @dname = coalesce(@dname+',','') + column_name from information_schema.columns where table_name=@tablename
print @dname
set @dquery = 'set identity_insert '+ @tablename + '_tmp on;insert into ' + @tablename + '_tmp (id, '+ @dname +' ) select idold,* from ' + @tablename + ';set identity_insert '+ @tablename + '_tmp off'
exec sp_executesql @dquery
end
else
begin
set @importnew = 'select identity(numeric(19,0)) as id,* into ' + @tablename +'_tmp from ' + @tablename
exec sp_executesql @importnew
end
set @addconstraint = 'alter table ' + @tablename + '_tmp add constraint ' + @tablename + '_pk primary key (id)'
exec sp_executesql @addconstraint
set @dropold = 'drop table ' + @tablename
exec sp_executesql @dropold
set @dorename = 'exec sp_rename ' + @tablename + '_tmp, ' + @tablename
exec sp_executesql @dorename
if exists(select column_name from INFORMATION_SCHEMA.columns where table_name = @tablename and column_name = 'idold')
begin
set @dropold = 'alter table '+ @tablename +' drop column idold'
exec sp_executesql @dropold
end
end
fetch next from tables into @tablename
end

It took a lot of googling to get that much. In that tiny piece of accumulated wisdom is how you would create a cursor so that you can loop over the items, how you would check whether a field exist or not in a particular table, does the table already have a primary key, how to rename column, how to insert value into an identity field by setting identity_insert to on, how to get a list of table fields and turn it into a comma separated string so that you can put that into another query. Phew.. But most importantly it allows me to import and export my ms sql 2008 database and know that this time the id field would still be intact and functioning (after running the script of course).

/me misses mysql, postgres and zodb... :(

Comments

Popular posts from this blog

haproxy on Centos 7 with SELinux

Setting up haproxy and varnish with reference to the configuration from these sites: http://sharadchhetri.com/2014/12/20/how-to-install-varnish-4-version-on-centos-7-rhel-7/ https://www.upcloud.com/support/haproxy-load-balancer-centos/ And finally can access plone on the server. But when I check the haproxy status, only client1 can be accessed. All other clients were down with general socket error, permission denied. Googling a bit I finally found out it was due to SELinux. Refer to this page: http://stackoverflow.com/questions/26420729/haproxy-health-check-permission-denied With the solution from that page, I installed policycoreutils-python and tried to run the command given: semanage port --add --type http_port_t --proto tcp 8081 But that command failed with the error that the port was already defined. When I looked it up using: semanage port -l | grep 8081 It belonged to some other type (transproxy_port_t). When I tried to delete the type using: semanage port -d -t transpro…

Installing Centos 7 on HP G7 Server

When I first initially tried it, the installer cannot detect any local hard disk to install to. After much googling found out that it was because a certain driver for the Smart Array Controller is no longer available on Centos 7. To allow for use of the new driver on old hardware, you need to enable the option. Once the DVD has booted and on the first menu screen, highlight the first option and then press tab which would bring you to a command line where you can add additional parameters to boot. Add the following parameters: hpsa.hpsa_allow_any=1 hpsa.hpsa_simple_mode=1 And then press enter to boot. You should now be able to install as you wish. After installation, on the first boot, make sure at the menu, press 'e' to edit the parameters and add them again. Then once booted, go to /boot/grub2/grub.cfg to permanently add the parameters to the menu. Refer to http://serverfault.com/questions/611182/centos-7-x64-and-hp-proliant-dl360-g5-scsi-controller-compatibility and it&#…

Me and my sleep apnea

I have been suffering from sleep apnea for quite a few years already. It started to show when I was still with OSCC some 6 years back when I would sometimes "accidentally" sleep at my cubicle. Early on I just dismissed it as because I was growing fat (we ate a lot at OSCC.. :P and I barely went anywhere except my cubicle) and also because of babies. Oh babies.. those cute little things that keeps you awake at night so that they can be so cute and charming when your friends and relatives come over the next day. So it was dismissed. Later on I got more and more used to sleeping at work, started associating it with my lack of motivation and sometimes feeling of downright depression. And I kept growing fatter and fatter, which I assumed was the reason why I was getting more and more tired.

But then around 2 years ago a friend of mine suggested maybe I've got sleep apnea. And a few other people suggested I go to a sleep clinic and all that. Put it off for quite a while. Until…