BorlandTalk.com

Tin Le · Guest

Please help.

I just cannot generate a table script using SQL-DMO.

I Can use the following:

dmoDatabase2 := coDatabase2.Create;
dmoDatabase2 := dmo.Databases.Item(strDB,EmptyParam) as _Database2;

spProcedure := coStoredProcedure2.Create;
spProcedure :=
dmoDatabase2.StoredProcedures.item('usp_MRL0663072_Drug_ReSupply',EmptyParam
) as _StoredProcedure2;
strTest := spProcedure.Text;
ShowMessage(strTest);

spView := coView2.Create;
spView := dmoDatabase2.Views.item('vw_APV20003_Visits',EmptyParam) as
_View2;
strTest := spView.Text;
ShowMessage(strTest);

fnFunction := coUserDefinedFunction.Create;
fnFunction :=
dmoDatabase2.UserDefinedFunctions.item('fn_LeadZeroes',EmptyParam) as
_UserDefinedFunction;
strTest := fnFunction.Text;
ShowMessage(strTest);

But not the table. Please help.
Thank you,
tbTable := coTable2.Create;
tbTable := dmoDatabase2.Tables.item('APP280005_Demog',EmptyParam) as
_Table2;
strTest := tbTable.Script(4,1,EmptyParam,EmptyParam); // Here is
what I am stuck.
ShowMessage(strTest);

Thank you,

Tin Le

Dennis Passmore · Guest

tbTable := coTable2.Create;
tbTable := dmoDatabase2.Tables.item(
'APP280005_Demog',EmptyParam) as _Table2;
strTest := tbTable.Script(4, 0, EmptyParam, EmptyParam);
ShowMessage(strTest);

the second parameter should be 0 ( nil ) value.

Dennis Passmore
Ultimate Software, Inc.

Tin Le · Guest

Thank you Dennis!

Here is what I did:

tbTable := coTable2.Create;
tbTable := dmoDatabase2.Tables.item('APP280005_Demog',EmptyParam) as
_Table2;
strTest := tbTable.Script(4,0,EmptyParam, EmptyParam);
ShowMessage(strTest);

It did not work. The error message is "Could not convert varaint of type
(error) into type of (integer)

I have no idea of what I do wrong! Could you and someone else lead me
further.

Thank you,

Tin le

<Dennis Passmore> wrote

Dennis Passmore · Guest

This works for me:

uses
sqldmo_tlb;

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
var
f_SQLName,
f_Username,
f_Password: string;
f_Table: _Table;
f_DataBase: _Database;
f_SQLServer: _SQLServer;
begin
f_SQLName := edit1.text;
f_Username := edit2.text;
f_Password := edit3.text;
f_SQLServer := nil;
f_SQLServer := CoSQLServer.Create;
if assigned(f_SQLServer) then
begin
f_SQLServer.AutoReConnect := true;
f_SQLServer.Connect(f_SQLName, f_Username, f_Password);
end
else
raise exception.create('SQLDMO is not installed');

f_DataBase := nil;
f_DataBase := f_SQLServer.Databases.item('pubs','dbo');
if assigned(f_DataBase) then
try
f_Table := nil;
f_Table := f_DataBase.Tables.item('authors','dbo');
if assigned(f_Table) then
try
Memo1.lines.Text := f_Table.Script(
SQLDMOScript_Default, EMPTYPARAM,
EMPTYPARAM, SQLDMOScript2_Default);
finally
f_Table := nil;
end;
finally
f_DataBase := nil;
end;

f_SQLServer.DisConnect;
f_SQLServer := nil;
end;

Dennis Passmore
Ultimate Software, Inc.

Tin Le · Guest

Thank you Dennis!

Yes, It work! and I have learned a lots from you.
I am really appreciated it.

Thank you again.

Tin Le

FYI: Here is what I did in the last couple of days.

Result := TStringList.Create;
SQLObjects.SQL_Table :=
SQLObjects.SQL_DataBase2.Tables.item(strObject,EmptyParam) as _Table2;
//strTest := tbTable.Script(256 + 73736 + 262144 + 2 + 4, 'C:' + dmo.Name
+ '_' + dmoDatabase2.Name + '_TBL.txt',Null,8388608);
//strTest := tbTable.Script(256 + 73736 + 262144 + 2 + 4,
EmptyParam,EmptyParam,524288);
//strTest := tbTable.Script(256 + 4096 + 73736 + 131072 + 8192 + 16 + 1024
+ -1 + 262144 + 2 + 4, EmptyParam,EmptyParam,0 + 4194304 + 4 + 1 + 2);
{
strTest := tbTable.Script(256 + 4096 + 262144 + 2 + 4 + -1,
EmptyParam,EmptyParam,524288);
ShowMessage(strTest);
strTest := tbTable.Script(256 + 4096 + 262144 + 2 + 4 ,
EmptyParam,EmptyParam,524288);
ShowMessage(strTest);

//strTest := spProcedure.Script(32 + 131072 + 4096 + -1,EmptyParam,2097152
+ 4);
// strTest := spProcedure.Script(32 + 131072 + 4096 + 262144
+ -1,EmptyParam,2097152 + 4);
//Result.Text := spProcedure.Script(32 + 131072 + 4096 + 262144
+ -1,EmptyParam,2097152 + 4);
}
Result.Text := SQLObjects.SQL_Table.Script(256 + 4096 + 262144 + 2 + 4
+ -1 , EmptyParam,EmptyParam,524288);

<Dennis Passmore> wrote