search

aaronengels / DrmmToPowerBI

The PowerShell scripts in this project will allow you to pull data from Datto RMM and store it in a database, making it possible to create historical dashboards or reports in PowerBI.
GNU General Public License v3.0
15 stars 7 forks source link

error converting nvarchar to bigint #1

Open onnigk opened 3 years ago

onnigk commented 3 years ago

Getting the error below.

Invoke-Sqlcmd : Error converting data type nvarchar to bigint. Msg 8114, Level 16, State 5, Procedure drmm.insertDevice, Line 5. At C:\scripts\DrmmUpdateSQLTables.ps1:88 char:4

afincho commented 3 years ago

The issue appears to be with devices which have the Warranty Expiration Date field populated. The WarrantyDate field, its looking to convert an unix time stamp, but the API returns the date in yyyy-mm-dd format and it errors on trying to convert, whereas the lastAudit, lastReboot, & LastSeen all returns a unix time stamp which needs converting

PenguinEXE commented 2 years ago

I also had this issue. Once it completed that portion of the script and moved on to the task of merging the temp tables, each of those instrutions returned a variation of this error.

Invoke-Sqlcmd : The MERGE statement conflicted with the FOREIGN KEY constraint "FK_devices_site". The conflict occurred in database "DRMM", table "drmm.sites", column 'id'. The statement has been terminated. Msg 547, Level 16, State 0, Procedure drmm.mergeDevices, Line 4. At line:7 char:1

  • Invoke-Sqlcmd -ConnectionString $connString -QueryTimeout 0 -Query "E ...
  • CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
  • FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

After the script ended I reviewed the database and found that all the data was still in the temp fields and the drmm fields remained empty.

FaulhaberEDV commented 2 years ago

Got the same issue here, but on a different position: UpdateSQLTables.ps1:95 char:4 Anything I can do about it or how to get down to the source of the error?