export داده ها از sql server به acsses از طریق delphi

[vatal]

با سلام خدمت دوستان :flower:
با فرض اینکه :

database برنامه sqlserver است و با delphi نوشته شده :گیج:
حال به چه شکل میتوانیم از طریق delphi مقادیر یک stored procudre در
sqlserver رو که تعدادی فیلد رو بر میگردونه ....رو به Acsses ... منتقل کرد ؟ :(

[majid_afra222]

سلام
یکی از روشهایی که میتونی استفاده کنی از طریق استفاده از قابلیت DTS خو SQL Server هستش
یه کمی پیچیده هستش ولی خیلی سریعه
من برای ارسال اطلاعات به اکسل ازش استفاده کردم اینم نمونه
البته چون نمی دونم چطور باید فایل رو آپلود کنم کل کد رو میزارم :embr:


library ExportToExcel;

{
Important note about DLL memory management: ShareMem must be the
first unit in your library's USES clause AND your project's (select
Project-View Source) USES clause if your DLL exports any procedures or
functions that pass strings as parameters or function results. This
applies to all strings passed to and from your DLL--even those that
are nested in records and classes. ShareMem is the interface unit to
the BORLNDMM.DLL shared memory manager, which must be deployed along
with your DLL. To avoid using BORLNDMM.DLL, pass string information
using PChar or ShortString parameters.
}

//ارسال به اکسل با استفاده از DTS SQL Server-

uses
SysUtils,
Classes,
Dialogs,
DTS_TLB,
OleServer;

{$R *.res}

function tracePackageError(oPackage: Package2) : Boolean;
var
ErrorCode: integer;
ErrorSource,
ErrorDescription,
ErrorIDofInterfaceWithError,
ErrorHelpFile: WideString;
ErrorHelpContext: integer;
i: integer;
begin
Result := False;
for i := 1 to oPackage.Steps.Count do
if oPackage.Steps.Item(i).ExecutionResult = DTSStepExecResult_Failure Then
begin
oPackage.Steps.Item(i).GetExecutionErrorInfo(Error Code, ErrorSource,
ErrorDescription, ErrorHelpFile, ErrorHelpContext, ErrorIDofInterfaceWithError);
ShowMessage(oPackage.Steps.Item(i).Name + ' failed' + #13#10 + ErrorSource +
#13#10 + ErrorDescription);
Result := True;
end;
end;

//دستور ایجاد جدول برای فایل اکسل خروجی-
procedure Task_CreateTable(DTS : Package2);
var
oTask: Task;
oCustomTask1: ExecuteSQLTask2;
oCustomTaskOld: CustomTask;
begin
oTask := DTS.Tasks.New('DTSExecuteSQLTask');
oTask.Name := 'Create Table New_Table Task';
oCustomTaskOld := oTask.CustomTask;
oCustomTask1 := oCustomTaskOld as ExecuteSQLTask2;

oCustomTask1.Name := 'Create Table New_Table Task';
oCustomTask1.Description := 'Create Table New_Table Task';
oCustomTask1.SQLStatement := 'CREATE TABLE New_Table (' +
'St_Id NUMERIC , ' +
'Ch_Name NVARCHAR(5) , ' +
'Ch_Freq NUMERIC , ' +
'Ch_Atten NVARCHAR(50) , ' +
'Ch_Mode NVARCHAR(5) , ' +
'St_Time CHAR(15) , ' +
'St_Day CHAR(10) , ' +
'Ch_MinSignal NUMERIC , ' +
'Rec_Level INT , ' +
'St_Type NCHAR(10))';
oCustomTask1.ConnectionID := 2;
oCustomTask1.CommandTimeout := 0;
oCustomTask1.OutputAsRecordset := False;

DTS.Tasks.Add(oTask);
oCustomTask1 := nil;
oCustomTaskOld := nil;
oTask := nil;
end;

procedure Trans_Sub1(oCustomTask1 : DataPumpTask2);
var
oTransformationOld: Transformation;
oTransformation: Transformation2;
oTransProps: Properties;
oColumn: Column;
begin
oTransformationOld := oCustomTask1.Transformations.New('DTS.DataPumpTran sformCopy');
oTransformation := oTransformationOld as Transformation2;
oTransformation.Name := 'DirectCopyXform';
oTransformation.TransformFlags := 63;
oTransformation.ForceSourceBlobsBuffered := 0;
oTransformation.ForceBlobsInMemory := False;
oTransformation.InMemoryBlobSize := 1048576;
oTransformation.TransformPhases := 4;

oColumn := oTransformation.SourceColumns.New('St_Id', 1);
oColumn.Name := 'St_Id';
oColumn.Ordinal := 1;
oColumn.Flags := 32792;
oColumn.Size := 0;
oColumn.DataType := 131;
oColumn.Precision := 18;
oColumn.NumericScale := 0;
oColumn.Nullable := False;

oTransformation.SourceColumns.Add(oColumn);
oColumn := nil;

oColumn := oTransformation.SourceColumns.New('Ch_Name', 2);
oColumn.Name := 'Ch_Name';
oColumn.Ordinal := 1;
oColumn.Flags := 96;
oColumn.Size := 50;
oColumn.DataType := 130;
oColumn.Precision := 0;
oColumn.NumericScale := 0;
oColumn.Nullable := True;

oTransformation.SourceColumns.Add(oColumn);
oColumn := nil;

oColumn := oTransformation.SourceColumns.New('Ch_Freq', 3);
oColumn.Name := 'Ch_Freq';
oColumn.Ordinal := 1;
oColumn.Flags := 112;
oColumn.Size := 0;
oColumn.DataType := 131;
oColumn.Precision := 18;
oColumn.NumericScale := 0;
oColumn.Nullable := True;

oTransformation.SourceColumns.Add(oColumn);
oColumn := nil;

oColumn := oTransformation.SourceColumns.New('Ch_Atten', 4);
oColumn.Name := 'Ch_Atten';
oColumn.Ordinal := 1;
oColumn.Flags := 96;
oColumn.Size := 50;
oColumn.DataType := 130;
oColumn.Precision := 0;
oColumn.NumericScale := 0;
oColumn.Nullable := True;

oTransformation.SourceColumns.Add(oColumn);
oColumn := nil;

oColumn := oTransformation.SourceColumns.New('Ch_Mode', 5);
oColumn.Name := 'Ch_Mode';
oColumn.Ordinal := 1;
oColumn.Flags := 96;
oColumn.Size := 50;
oColumn.DataType := 130;
oColumn.Precision := 0;
oColumn.NumericScale := 0;
oColumn.Nullable := True;

oTransformation.SourceColumns.Add(oColumn);
oColumn := nil;

oColumn := oTransformation.SourceColumns.New('St_Time', 6);
oColumn.Name := 'St_Time';
oColumn.Ordinal := 1;
oColumn.Flags := 24;
oColumn.Size := 15;
oColumn.DataType := 129;
oColumn.Precision := 0;
oColumn.NumericScale := 0;
oColumn.Nullable := False;

oTransformation.SourceColumns.Add(oColumn);
oColumn := nil;

oColumn := oTransformation.SourceColumns.New('St_Day', 7);
oColumn.Name := 'St_Day';
oColumn.Ordinal := 1;
oColumn.Flags := 24;
oColumn.Size := 10;
oColumn.DataType := 129;
oColumn.Precision := 0;
oColumn.NumericScale := 0;
oColumn.Nullable := False;

oTransformation.SourceColumns.Add(oColumn);
oColumn := nil;

oColumn := oTransformation.SourceColumns.New('Ch_MinSignal', 8);
oColumn.Name := 'Ch_MinSignal';
oColumn.Ordinal := 1;
oColumn.Flags := 112;
oColumn.Size := 0;
oColumn.DataType := 131;
oColumn.Precision := 18;
oColumn.NumericScale := 0;
oColumn.Nullable := True;

oTransformation.SourceColumns.Add(oColumn);
oColumn := nil;

oColumn := oTransformation.SourceColumns.New('Rec_Level', 9);
oColumn.Name := 'Rec_Level';
oColumn.Ordinal := 1;
oColumn.Flags := 24;
oColumn.Size := 0;
oColumn.DataType := 3;
oColumn.Precision := 0;
oColumn.NumericScale := 0;
oColumn.Nullable := False;

oTransformation.SourceColumns.Add(oColumn);
oColumn := nil;

oColumn := oTransformation.SourceColumns.New('St_Type', 10);
oColumn.Name := 'St_Type';
oColumn.Ordinal := 1;
oColumn.Flags := 24;
oColumn.Size := 10;
oColumn.DataType := 129;
oColumn.Precision := 0;
oColumn.NumericScale := 0;
oColumn.Nullable := False;

oTransformation.SourceColumns.Add(oColumn);
oColumn := nil;

oColumn := oTransformation.DestinationColumns.New('St_Id', 1);
oColumn.Name := 'St_Id';
oColumn.Ordinal := 1;
oColumn.Flags := 118;
oColumn.Size := 0;
oColumn.DataType := 5;
oColumn.Precision := 0;
oColumn.NumericScale := 0;
oColumn.Nullable := True;

oTransformation.DestinationColumns.Add(oColumn);
oColumn := nil;

oColumn := oTransformation.DestinationColumns.New('Ch_Name', 2);
oColumn.Name := 'Ch_Name';
oColumn.Ordinal := 1;
oColumn.Flags := 102;
oColumn.Size := 255;
oColumn.DataType := 130;
oColumn.Precision := 0;
oColumn.NumericScale := 0;
oColumn.Nullable := True;

oTransformation.DestinationColumns.Add(oColumn);
oColumn := nil;

oColumn := oTransformation.DestinationColumns.New('Ch_Freq', 3);
oColumn.Name := 'Ch_Freq';
oColumn.Ordinal := 1;
oColumn.Flags := 118;
oColumn.Size := 0;
oColumn.DataType := 5;
oColumn.Precision := 0;
oColumn.NumericScale := 0;
oColumn.Nullable := True;

oTransformation.DestinationColumns.Add(oColumn);
oColumn := nil;

oColumn := oTransformation.DestinationColumns.New('Ch_Atten', 4);
oColumn.Name := 'Ch_Atten';
oColumn.Ordinal := 1;
oColumn.Flags := 102;
oColumn.Size := 255;
oColumn.DataType := 130;
oColumn.Precision := 0;
oColumn.NumericScale := 0;
oColumn.Nullable := True;

oTransformation.DestinationColumns.Add(oColumn);
oColumn := nil;

oColumn := oTransformation.DestinationColumns.New('Ch_Mode', 5);
oColumn.Name := 'Ch_Mode';
oColumn.Ordinal := 1;
oColumn.Flags := 102;
oColumn.Size := 255;
oColumn.DataType := 130;
oColumn.Precision := 0;
oColumn.NumericScale := 0;
oColumn.Nullable := True;

oTransformation.DestinationColumns.Add(oColumn);
oColumn := nil;

oColumn := oTransformation.DestinationColumns.New('St_Time', 6);
oColumn.Name := 'St_Time';
oColumn.Ordinal := 1;
oColumn.Flags := 102;
oColumn.Size := 255;
oColumn.DataType := 130;
oColumn.Precision := 0;
oColumn.NumericScale := 0;
oColumn.Nullable := True;

oTransformation.DestinationColumns.Add(oColumn);
oColumn := nil;

oColumn := oTransformation.DestinationColumns.New('St_Day', 7);
oColumn.Name := 'St_Day';
oColumn.Ordinal := 1;
oColumn.Flags := 102;
oColumn.Size := 255;
oColumn.DataType := 130;
oColumn.Precision := 0;
oColumn.NumericScale := 0;
oColumn.Nullable := True;

oTransformation.DestinationColumns.Add(oColumn);
oColumn := nil;

oColumn := oTransformation.DestinationColumns.New('Ch_MinSign al', 8);
oColumn.Name := 'Ch_MinSignal';
oColumn.Ordinal := 1;
oColumn.Flags := 118;
oColumn.Size := 0;
oColumn.DataType := 5;
oColumn.Precision := 0;
oColumn.NumericScale := 0;
oColumn.Nullable := True;

oTransformation.DestinationColumns.Add(oColumn);
oColumn := nil;

oColumn := oTransformation.DestinationColumns.New('Rec_Level' , 9);
oColumn.Name := 'Rec_Level';
oColumn.Ordinal := 1;
oColumn.Flags := 118;
oColumn.Size := 0;
oColumn.DataType := 5;
oColumn.Precision := 0;
oColumn.NumericScale := 0;
oColumn.Nullable := True;

oTransformation.DestinationColumns.Add(oColumn);
oColumn := nil;

oColumn := oTransformation.DestinationColumns.New('St_Type', 10);
oColumn.Name := 'St_Type';
oColumn.Ordinal := 1;
oColumn.Flags := 102;
oColumn.Size := 255;
oColumn.DataType := 130;
oColumn.Precision := 0;
oColumn.NumericScale := 0;
oColumn.Nullable := True;

oTransformation.DestinationColumns.Add(oColumn);
oColumn := nil;

oTransProps := oTransformation.TransformServerProperties;
oTransProps := nil;

oCustomTask1.Transformations.Add(oTransformation);
oTransformation := nil;
oTransformationOld := nil;
end;

//ایجاد پرس و جوی مورد نیاز برای واکشی اطلاعات-
procedure Task_Sub1(DTS : Package2; ExportedTable : string);
var
oTask: Task;
oCustomTaskOld: CustomTask;
oCustomTask1: DataPumpTask2;
begin
oTask := DTS.Tasks.New('DTSDataPumpTask');
oTask.Name := 'Copy Data from New_Table to New_Table Task';

oCustomTaskOld := oTask.CustomTask;
oCustomTask1 := oCustomTaskOld as DataPumpTask2;

oCustomTask1.Name := 'Copy Data from New_Table to New_Table Task';
oCustomTask1.Description := 'NewPack_DTS';
oCustomTask1.SourceConnectionID := 1;
oCustomTask1.SourceSQLStatement := 'SELECT St_Id' + #13#10;
oCustomTask1.SourceSQLStatement := oCustomTask1.SourceSQLStatement + ' , (SELECT Ch_Name FROM Table_Channels TCh1 WHERE TCh1.Ch_Id =AllSt.Ch_Ref) as [Ch_Name]' + #13#10;
oCustomTask1.SourceSQLStatement := oCustomTask1.SourceSQLStatement + ' , (SELECT Ch_Frequency FROM Table_Channels TCh1 WHERE TCh1.Ch_Id =AllSt.Ch_Ref) as [Ch_Freq]' + #13#10;
oCustomTask1.SourceSQLStatement := oCustomTask1.SourceSQLStatement + ' , (SELECT Atten FROM Table_Attens T_At ' + #13#10;
oCustomTask1.SourceSQLStatement := oCustomTask1.SourceSQLStatement + ' WHERE T_At.Atten_Id = (SELECT Ch_Atten ' + #13#10;
oCustomTask1.SourceSQLStatement := oCustomTask1.SourceSQLStatement + ' FROM Table_Channels TCh1 WHERE TCh1.Ch_Id =AllSt.Ch_Ref)) as [Ch_Atten]' + #13#10;
oCustomTask1.SourceSQLStatement := oCustomTask1.SourceSQLStatement + ' , (SELECT Mode FROM Table_Modes T_M ' + #13#10;
oCustomTask1.SourceSQLStatement := oCustomTask1.SourceSQLStatement + ' WHERE T_M.Mode_Id = (SELECT Ch_Mode ' + #13#10;
oCustomTask1.SourceSQLStatement := oCustomTask1.SourceSQLStatement + ' FROM Table_Channels TCh1 WHERE TCh1.Ch_Id =AllSt.Ch_Ref)) as [Ch_Mode]' + #13#10;
oCustomTask1.SourceSQLStatement := oCustomTask1.SourceSQLStatement + ' , [St_Time], [St_Day]' + #13#10;
oCustomTask1.SourceSQLStatement := oCustomTask1.SourceSQLStatement + ' ,(SELECT Ch_MinSignal FROM Table_Channels TCh1 WHERE TCh1.Ch_Id =AllSt.Ch_Ref) as [Ch_MinSignal]' + #13#10;
oCustomTask1.SourceSQLStatement := oCustomTask1.SourceSQLStatement + ' , [In_Frq] As [Rec_Level], [St_Type]' + #13#10;
// oCustomTask1.SourceSQLStatement := oCustomTask1.SourceSQLStatement + 'FROM Table_All_State AllSt';
oCustomTask1.SourceSQLStatement := oCustomTask1.SourceSQLStatement + 'FROM ' + ExportedTable+ ' AllSt';

oCustomTask1.DestinationConnectionID := 2;
oCustomTask1.DestinationObjectName := 'New_Table';
oCustomTask1.ProgressRowCount := 1000;
oCustomTask1.MaximumErrorCount := 0;
oCustomTask1.FetchBufferSize := 1;
oCustomTask1.UseFastLoad := True;
oCustomTask1.InsertCommitSize := 0;
oCustomTask1.ExceptionFileColumnDelimiter := '|';
oCustomTask1.ExceptionFileRowDelimiter := #13#10;
oCustomTask1.AllowIdentityInserts := False;
oCustomTask1.FirstRow := '0';
oCustomTask1.LastRow := '0';
oCustomTask1.FastLoadOptions := 2;
oCustomTask1.ExceptionFileOptions := 1;
oCustomTask1.DataPumpOptions := 0;

Trans_Sub1(oCustomTask1);

DTS.Tasks.Add(oTask);
oCustomTaskOld := nil;
oCustomTask1 := nil;
oTask := nil;
end;

//تابع اصلی ارسال اطلاعات به اکسل-
function ExportDataToExcel(FileName : string; ExportedTable : string) : Boolean;
var
DTSOld : Package;
DTSNew : Package2;
DtsConnection : Connection;
OpStep : Step;
OpPrecConstraint : PrecedenceConstraint;
begin
DTSOld := CoPackage.Create;
DTSNew := DTSOld as Package2;

DTSNew.Name := 'ExportExcel';
DTSNew.Description := 'Package For Export Data to Excel';
DTSNew.WriteCompletionStatusToNTEventLog := False;
DTSNew.FailOnError := False;
DTSNew.PackagePriorityClass := 2;
DTSNew.MaxConcurrentSteps := 4;
DTSNew.LineageOptions := 0;
DTSNew.UseTransaction := True;
DTSNew.TransactionIsolationLevel := 4096;
DTSNew.AutoCommitTransaction := True;
DTSNew.RepositoryMetadataOptions := 0;
DTSNew.UseOLEDBServiceComponents := True;
DTSNew.LogToSQLServer := False;
DTSNew.LogServerFlags := 0;
DTSNew.FailPackageOnLogFailure := False;
DTSNew.ExplicitGlobalVariables := False;
DTSNew.PackageType := 0;

// dts source info
DtsConnection := DTSNew.Connections.New('SQLOLEDB');

DtsConnection.ConnectionProperties.Item('Persist Security Info').Value := True;
DtsConnection.ConnectionProperties.Item('User ID').Value := 'sa';
DtsConnection.ConnectionProperties.Item('Initial Catalog').Value := 'ScanChanel';
DtsConnection.ConnectionProperties.Item('Data Source').Value := '(local)';
DtsConnection.ConnectionProperties.Item('Applicati on Name').Value := 'DTS Designer';

DtsConnection.Name := 'Microsoft OLE DB Provider for SQL Server';
DtsConnection.ID := 1;
DtsConnection.Reusable := True;
DtsConnection.ConnectImmediate := False;
DtsConnection.DataSource := '(local)';
DtsConnection.UserID := 'sa';
DtsConnection.ConnectionTimeout := 60;
DtsConnection.Catalog := 'ScanChanel';
DtsConnection.UseTrustedConnection := True;
DtsConnection.UseDSL := False;

DTSNew.Connections.Add(DtsConnection);
DtsConnection := nil;

// dts data to excel
DtsConnection := DTSNew.Connections.New('Microsoft.Jet.OLEDB.4.0');

DtsConnection.ConnectionProperties.Item('User ID').Value := 'Admin';
DtsConnection.ConnectionProperties.Item('Data Source').Value := FileName; //'C:\Excel-Data.XLS';
DtsConnection.ConnectionProperties.Item('Extended Properties').Value := 'Excel 8.0;HDR=YES;';

DtsConnection.Name := 'Microsoft Excel 97-2000';
DtsConnection.ID := 2;
DtsConnection.Reusable := True;
DtsConnection.ConnectImmediate := False;
DtsConnection.DataSource := FileName; //'C:\ExlExpWinR.xls';
DtsConnection.UserID := 'Admin';
DtsConnection.ConnectionTimeout := 60;
DtsConnection.UseTrustedConnection := False;
DtsConnection.UseDSL := False;

DTSNew.Connections.Add(DtsConnection);
DtsConnection := nil;
///////////////////

OpStep := DTSNew.Steps.New;

OpStep.Name := 'Create Table New_Table Step';
OpStep.Description := 'Create Table New_Table Step';
OpStep.ExecutionStatus := 1;
OpStep.TaskName := 'Create Table New_Table Task';
OpStep.CommitSuccess := false;
OpStep.RollbackFailure := false;
OpStep.ScriptLanguage := 'VBScript';
OpStep.AddGlobalVariables := true;
OpStep.RelativePriority := 3;
OpStep.CloseConnection := false;
OpStep.ExecuteInMainThread := false;
OpStep.IsPackageDSORowset := false;
OpStep.JoinTransactionIfPresent := false;
OpStep.DisableStep := false;

DTSNew.Steps.Add(OpStep);
OpStep := nil;

//------------- a new step defined below
OpStep := DTSNew.Steps.New;

OpStep.Name := 'Copy Data from New_Table to New_Table Step';
OpStep.Description := 'Copy Data from New_Table to New_Table Step';
OpStep.ExecutionStatus := 1;
OpStep.TaskName := 'Copy Data from New_Table to New_Table Task';
OpStep.CommitSuccess := False;
OpStep.RollbackFailure := False;
OpStep.ScriptLanguage := 'VBScript';
OpStep.AddGlobalVariables := True;
OpStep.RelativePriority := 3;
OpStep.CloseConnection := False;
OpStep.ExecuteInMainThread := True;
OpStep.IsPackageDSORowset := False;
OpStep.JoinTransactionIfPresent := False;
OpStep.DisableStep := False;

DTSNew.Steps.Add(OpStep);
OpStep := nil;

//------------- a precedence constraint for steps defined below
OpStep := DTSNew.Steps.Item('Copy Data from New_Table to New_Table Step');
OpPrecConstraint := OpStep.PrecedenceConstraints.New('Create Table New_Table Step');
OpPrecConstraint.StepName := 'Create Table New_Table Step';
OpPrecConstraint.PrecedenceBasis := 0;
OpPrecConstraint.Value := 4;

OpStep.precedenceConstraints.Add(OpPrecConstraint) ;
OpPrecConstraint := nil;
///////////////////////

Task_CreateTable(DTSNew);

Task_Sub1(DTSNew, ExportedTable);

Result := True;
DTSNew.Execute();
if tracePackageError(DTSNew) then
Result := False;
DTSNew.Uninitialize();

DTSNew := nil;
DTSOld := nil;
end;

exports
ExportDataToExcel;
end.

برای تبدیل به اکسس هم کار زیادی لازم نیست انجام بدی

امیدوارم بدردت بخوره

[vatal]

دستت درد نکنه .... ببینم چکار میتونم بکنم :flower: :تشویق:

باز هم ممنون :موفق:

[vatal]

ایا دوستان دیگه راه اسانتری سراغ ندارن ؟ :گیج:

[majid_afra222]

سلام مهندس جان
این راه واقعا عالیه راه سختی هم نیست
تو یکی دو ثانیه 30 - 40 هزار رکورد رو export میکنه برای حجم بالا واقعا عالیه به هر فرمتی هم میتونه ارسال کنه
در ضمن به حجم کد نگاه نکن خیلی سادس همش کپی شده عین هم هستن فقط یه جاهایی فرق کوچیکی داره

اگه حجم اطلاعات رو بگی شاید راه ساده تری هم باشه

[سید مسعود موحد]

دوستان عزیز
من همین راه را بصورت یک کلاس طراحی کرده ام
فقط اگر کسی میتواند بگوید که Event های آنرا به چه صورت میتوان مدیریت کرد متشکر میشوم

[ali_abbasi22145]

SMExport ,EMS quick export

[Mojgan110]

SMExport ,EMS quick export

حالا چرا اینقدر تلگرافی ؟ :گیج:

نیم سطر درباره اش صحبت میکنید لطفا آیا ؟