Problem using SQLDMO and Delphi to scriptout SQL Server SPs

Question

Problem using SQLDMO and Delphi to scriptout SQL Server SPs

SSCrazy

Points: 2515

January 12, 2007 at 2:50 pm

{DB_NAME and SCRIPT_PATH are consts}

var
  SQL_DMO: _SQLServer;
  SQLDatabase: _Database;
  SQLStoredProcedure: _StoredProcedure;
  oServer: OleVariant;
  iScriptOptions: Integer;
  strScript: TStringList;
  i: Integer;
begin
  SQL_DMO := CoSQLServer.Create;
  try
    SQL_DMO.LoginSecure := True;
    oServer := GetLocalComputerName;
    SQL_DMO.Connect(oServer, '', '');
    SQLDatabase := SQL_DMO.Databases.item(DB_NAME,'dbo');
    if assigned(SQLDataBase) then
    try
      for i := 1 to SQLDatabase.StoredProcedures.Count do
      begin
        SQLStoredProcedure := SQLDatabase.StoredProcedures.Item(i, 'dbo');
        if Assigned(SQLStoredProcedure) then
        begin
          try
            // bypass system objects
            if not SQLStoredProcedure.SystemObject then
            begin
              iScriptOptions := SQLDMOScript_Default or SQLDMOScript_Drops;
              strScript      := TStringList.Create;
              try
                // Here, the Script call is not returning anything.
                strScript.Text := SQLStoredProcedure.Script(iScriptOptions, SCRIPT_PATH + SQLStoredProcedure.Name + '.sql', SQLDMOScript2_Default);
                // Even tired this with no affect: (EmptyParam is same as NULL)                
                // strScript.Text := SQLStoredProcedure.Script(iScriptOptions, EmptyParam, SQLDMOScript2_Default);
                strScript.SaveToFile(SCRIPT_PATH + SQLStoredProcedure.Name + '.sql');
              finally
                strScript.Free;
              end;
            end; {if not SQLStoredProcedure.SystemObject}
          finally
            SQLStoredProcedure := nil;
          end;
        end; {if Assigned(SQLStoredProcedure)}
      end; {for i := 0 to SQLDatabase.StoredProcedures.Count-1}
    finally
      SQLDataBase := nil;
    end;
  finally
    SQL_DMO.DisConnect;
    SQL_DMO := nil;
  end;
end;

January 15, 2007 at 8:00 am

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Feifarek SSC Veteran Points: 247 More actions · Answer 1

I was able to compile your code and get it to work in Delphi 5 with SQL Server 2000, so I'm not where your problem might be.

In the past I have not used DMO to get Stored Procedure scripts-- I have used sp_helptext 'spName'. Of course then you need to get the stored procedures with ADOConnection.GetProcedureNames.

Another place I look for help is the stored knowledge of the Borland newsgroups at http://www.fulltextsearch.com. Let me know if I can help any more with your problem.

Brian

MrBaseball34 SSCrazy Points: 2515 More actions · Answer 2

usually I use tamarack.com .vs fulltextsearch.com and I have

not found an answer yet.

MrBaseball34 SSCrazy Points: 2515 More actions · Answer 3

Problem with using sp_helptext is that it will not get the full text of large procedures. I think the limit is 8K.

The Script method is the same thing used by Enterprise Mgr when you select Generate SQL...

Feifarek SSC Veteran Points: 247 More actions · Answer 4

I have not experienced that limitation-- I just got the script for a 25 kb stored procedure through sp_helptext. Since sp_helptext outputs the procedure code line by line (respecting the line breaks inside your stored procedure), there may be a problem if a single line in the procedure was more than 8000 characters long, but I have not tested that extreme situation.

I still would like to help you solve your original problem, but since it works for me in Delphi 5 I'm not sure what the next step would be. What version of Delphi and SQL Server are you testing on?

MrBaseball34 SSCrazy Points: 2515 More actions · Answer 5

D7 and SQL2000 SP4. I have a vbs script that works flawlessly.

I just can't get this to work correctly usign Delphi because we have a utility to script out our data and I'm adding this functionality into it. The Delpih is a simple one to one translation of the VBS script using early binding. Maybe I could try late binding?

I was mistaken about the sp_helptext, it seems. I had answered a guy over at Experts-Exchange on a question about returning complete SP text and came up with another solution.

MrBaseball34 SSCrazy Points: 2515 More actions · Answer 6

OK, I have found the problem as to why it doesn't work. I found an old type lib file

on my system that was for version 7. SQL2K uses version 8 and SQLl2005 uses version

8.5. I have reimported the typelib and renamed it to SQLDMO_TLB_8_0 and

SQLDMO_TLB_8_5 and renamed the old one SQLDMO_TLB_7_0. It now works.

Feifarek SSC Veteran Points: 247 More actions · Answer 7

That's great! Thanks for the update-- it will help others who bump into similar problems. (And sorry I didn't follow up earlier, but I was out of ideas and wouldn't expect any benefit from changing the late/early binding but didn't know.)