SQL

  • 21 Apr

    How to import data into a SQL table where a primary key as guid is needed

    How to import data into a SQL table where a primary key as guid is needed

    Q: I have a question about importing data into a SQL table where a primary key (guid/uniqueidentifier) needs to be generated. In a SQL command like the below, NEWID() will create the required id. Can this be achieved in Limagito?

    limagito file mover sql primary key as guid

    INSERT INTO dbo.testtable(intconID, caseID)

    VALUES (NEWID(), 1)

    Error received:

    8/04/2024 12:37:55 PM TFDPhysMSSQLCommand DB SQL Exception: [FireDAC][Phys][ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column ‘intconID’, table ‘dbo.TestTable’; column does not allow nulls. INSERT fails.

    8/04/2024 12:37:55 PM Batch Move Exception: [FireDAC][Phys][ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column ‘intconID’, table ‘ dbo.TestTable ‘; column does not allow nulls. INSERT fails.

    A: We added a new feature in version v2024.4.9.0 which replaces the NEWID() in the WriteSQL by a GUID when the Rule is triggered.

    If you need any help with this ‘primary key as guid’ request, please let us know.

    Best Regards,

    Limagito Team

    #sql #managedfiletransfer #FileManagement #filetransfer

    By Limagito-Team SQL , , ,
  • 24 Sep

    How to exit rule when SQL record count is 0

    How to exit rule when SQL record count is 0

    Q: We have an SQL as source job that emails the results of the SQL statement as a file. The problem is that we do not want the results/email sent when there are no records. I see that the variable %QSC contains the record count. How can I use that variable to exit the rule and not email the empty results file?

    A: So I guess you have SMTP as Destination?
    Please add the following SQL ‘On Destination’ script and try once more:

    limagito file mover paascal script option

    Begin
      psExitCode:= 1;
      // ... add your code here
      If psQSC = 0 Then
        psExitCode:= 0;
    End.

    limagito file mover pascal script setup

    • Feedback customer: Yes, it is a SMTP destination. Based off what you sent below I added the Pascal script with the addition of some logging:

    limagito file mover pascal script setup

    This works as expected and what we want, but it also creates the temp file. Is there a way the temp file can be suppressed? If not, I can add a rule to delete them.

    8/9/2023 5:07:43 PM RecordCount: 0

    8/9/2023 5:07:43 PM FieldDefs: “contributor_system”,”logged_datetime”,”mrn”,”patient_name”,”fin”,”error_status”,”error_text”,”message_text”

    8/9/2023 5:07:43 PM Copied Cerner_ESI_log_other_2023-08-09.csv to C:\Users\SVCFIL~1\AppData\Local\Temp\LimagitoX\LimagitoX.165\Cerner_ESI_log_other_2023-08-09.csv;110

    8/9/2023 5:07:43 PM Backup Successful C:\Users\SVCFIL~1\AppData\Local\Temp\LimagitoX\LimagitoX.165\Cerner_ESI_log_other_2023-08-09.csv to C:\Data\SourceFileBackups\Prod\2023\08\09\Cerner_ESI_log_other_2023-08-09.csv

    8/9/2023 5:07:43 PM Compiling..

    8/9/2023 5:07:43 PM Compiled Succesfully

    8/9/2023 5:07:43 PM psQSC (SQL record count) = 0. Exiting rule.

    8/9/2023 5:07:43 PM Total Files Redo, Count: 1 & Size: 110

    • Reply to customer:

    Please try to add

    DeleteFile(psFilePath + psFileName);

    to the script.

    Begin
      psExitCode:= 1;
      // ... add your code here
      If psQSC = 0 Then
      Begin
        DeleteFile(psFilePath + psFileName);
        psExitCode:= 0;
      End;
    End.

    It seems you are using our Backup option. In your case, we would add the ‘backup’ as a second WIN destination because otherwise you still get the temporary file in your backup folder (instead of using our BACKUP option).

    • Feedback customer: That got it! Thanks you for your help. We are all good now and the rule is working as we want it to.

    If you need any info about this ‘exit rule when SQL record count is 0’ question, please let us know.

    #sql #filetransfer #filemanagement

    Best regards,

    Limagito Team

    By Limagito-Team SQL ,
  • 19 Mar

    SQL Source – RecordCount as variable?

    Q: With the SQL Source is there a way to access the RecordCount as variable? Would be nice to include it in the subject line of an email Destination. Many thanks!

    A: We added two new parameters in version v2022.3.18.0.

    1. %QDC = Query Destination record Count, when using SQL as Destination
    2. %QSC = Query Source record Count, when using SQL as Source

    You can use %QDC and %QSC within your email or at other positions where parameters can be used. Some screenshots to get you started.

    1. In our example we’ll be using SQL as Source. You can find more information about the setup of ‘SQL as Source’ in our Blog.
    Limagito File Mover SQL as Source
    2. We’ll be using the new %QSC parameter in an email event:
    Limagito File Mover Rule Events
    3. We enabled the ‘On Success’ event and the ‘Enable Mail’ option:

    Limagito File Mover Rule Event Setup

    4. In the email setup we used our new %QSC parameter so the RecordCount will be displayed.

    Limagito File Mover Email Message RecordCount

    4. Received Email:

    Limagito File Mover SQL RecordCount Email

    #SQL #Filetransfer

    If you need any info about this ‘RecordCount as variable’ option, please let us know.

    Best regards,

    Limagito Team

    By Limagito-Team SQL ,
1 2 3 4 5
SEARCH