Bonsoir fantastic Monks,
I need to create a table in MSSQL2008, then drop all its constraints if there are any, then to recreate the same table again!..Simple!
So i put this bit of code together (with help from PerlMonks)
$dbh = DBI->connect("DBI:ODBC:driver={SQL Server};Server=$server;Datab
+ase=$database;UID=$dbusername;PWD=$dbpassword") || die "$Error_Messa
+ge $DBI::errstr";
print ".......Connection successful\n\n";
&prep_tbl("Sites");
&prep_tbl("Data_Centers");
sub prep_tbl
{
my ($tbl) = @_;
print "\nPreparing Mssql2008 table: $tbl,....";
my $dbh_tmp = DBI->connect("DBI:ODBC:driver={SQL Server};Server=$s
+erver;Database=$database;UID=$dbusername;PWD=$dbpassword");
# Check Database for tables, IF NOT EXISTS then just create it
$dbh->do("IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = objec
+t_id(N'[dbo].[$tbl]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE [dbo].[$tbl] (@{$tables->{$tbl}})");
my $sth = $dbh->prepare("SELECT constraint_name FROM INFORMATION_S
+CHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = '$tbl'");
$sth->execute;
my $temp = $sth->fetchrow_arrayref;
foreach my $rel (@$temp)
{
next unless ($rel =~ /ID/);
$dbh_tmp->do("ALTER TABLE $tbl DROP CONSTRAINT $rel");
}
#### The code below doesn't work - it doesn't recreate the table a
+gain!
$dbh->do("IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id
+(N'[dbo].[$tbl]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[$tbl];
CREATE TABLE [dbo].[$tbl] (@{$tables->{$tbl}})");
print "Done\n";
}
But when I ran the code I got the following errors!
DBD::ODBC::db do failed: [Microsoft][ODBC SQL Server Driver]Connection
+ is busy with results for another hstmt (SQL-HY000) at U:\Do
ccuments\Scripts\Test\test.pl line 60.
Preparing Mssql2008 table: Sites,....Done
DBD::ODBC::db do failed: [Microsoft][ODBC SQL Server Driver]Connection
+ is busy with results for another hstmt (SQL-HY000) at U:\Do
ccuments\Scripts\Test\test.pl line 60.
Preparing Mssql2008 table: Data_Centers,....Done
So I said ok, it deletes the relationships but it doesn't recreate the table again! let me add another handle here!
my $dbh3 = DBI->connect("DBI:ODBC:driver={SQL Server};Server=$serv
+er;Database=$database;UID=$dbusername;PWD=$dbpassword");
$dbh3->do("IF EXISTS (SELECT * FROM sysobjects WHERE id = object_i
+d(N'[dbo].[$tbl]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[$tbl];
CREATE TABLE [dbo].[$tbl] (@{$tables->{$tbl}})");
Still doesn't work, I don't get any errors but the table does not get re-created!
Can someone enlighten me please why this is happening?
Merci