I am trying with this script to query and collect data from a Postgres server and then load this data in a table DatenBankTable. After that i want to insert this collected information in a table in a SQL Server this is the table in the SQL Server. So it's basically data migration from Postgres to SQL Server.
#### connection to the Postgres server######
$MyServer = "H01POSxxx"
$MyPort = "5xx"
$MyDB = "daxx"
$MyUid = "akxx"
$MyPass = "xxxx"
$PgConnectionString = "Driver={PostgreSQL Unicode(x64)};Server=$MyServer;Port=$MyPort;Database=$MyDB;Uid=$MyUid;Pwd=$MyPass;"
$PgConn = New-Object System.Data.Odbc.OdbcConnection;
$PgConn.ConnectionString = $PgConnectionString;
$PgConn.Open();
#######connection to the SQL Server#########
$SqlConnecting = "Server=h01sqlxx\insxx;Database=KPIs_DBInxxx;Integrated Security= True;Connect Timeout=30";
$SqlConn = new-object System.DATA.Sqlclient.sqlconnection;
$SqlConn.ConnectionString = $SqlConnecting;
$SqlCommand = $SqlConn.CreateCommand();
$SqlConn.open();
#############query the data from the Postrgres server############
$DatenBankQuery = $PgConn.CreateCommand();
$DatenBankQuery.CommandText = "
SELECT pg_database.oid AS ID,
pg_database.datname AS Datenbank,
(pg_stat_file('base/'||pg_database.oid ||'/PG_VERSION')).modification AS Erstellt,
pg_size_pretty(pg_database_size(datname)) AS Datenbankgröße,
pg_database.datcollate AS Collation,
pg_shdescription.description AS Beschreibung,
joblog.jlgstart AS Backup
from pg_database LEFT JOIN pg_shdescription
ON pg_database.oid = pg_shdescription.objoid,
pgagent.pga_job jobs
LEFT JOIN pgagent.pga_joblog joblog
ON jobs.jobid = joblog.jlgjobid
WHERE jobs.jobname LIKE '%BACKUP%'
AND joblog.jlgstatus = 's'
AND joblog.jlgstart = (SELECT MAX(jlgstart) from pgagent.pga_joblog) "
######Execute the queries and load the data in the table "DatenBankTable"#########
$DatenBankAdapter = New-Object System.Data.Odbc.OdbcDataAdapter($DatenBankQuery)
$DatenBankTable = New-Object System.Data.DataTable
$null = $DatenBankAdapter.Fill($DatenBankTable)
##### insert the data in the table "postgres.DATABASE_TABLE" on the SQL Server########
foreach($tab in $DatnBankTable) {
$SqlCommand.CommandText = @"
insert into postgres.DATABASE_TABLE (ID, Datenbank, Erstellt, Datenbankgröße, Collation, Beschreibung, Backups)
VALUES ($tab.id, '$tab.datenbank', '$tab.erstellt', '$tab.datenbankgröße', '$tab.collation', '$tab.beschreibung', '$tab.backup');
"@;
}
$null = $SqlCommand.ExecuteNonQuery();
The queries are working and the data is stored in the table DatenBankTable.
PS C:\WINDOWS\system32> $DatenBankTable
id : 13012
datenbank : postgres
erstellt : 05.10.2020 13:47:37
datenbankgröße : 32 MB
collation : German_Germany.1252
beschreibung : default administrative connection database
backup : 11.12.2022 00:00:03
id : 83481
datenbank : lrsql_db
erstellt : 11.03.2022 13:57:31
datenbankgröße : 9549 kB
collation : German_Germany.1252
beschreibung :
backup : 11.12.2022 00:00:03
id : 1
datenbank : template1
erstellt : 05.10.2020 13:47:22
datenbankgröße : 7661 kB
collation : German_Germany.1252
beschreibung : default template for new databases
backup : 11.12.2022 00:00:03
id : 13011
datenbank : template0
erstellt : 05.10.2020 13:47:37
datenbankgröße : 7513 kB
collation : German_Germany.1252
beschreibung : unmodifiable empty database
backup : 11.12.2022 00:00:03
id : 53356
datenbank : CrosscapEnterprise_Q
erstellt : 01.12.2020 13:51:20
datenbankgröße : 14 MB
collation : German_Germany.1252
beschreibung :
backup : 11.12.2022 00:00:03
id : 86458
datenbank : TNM_Remox_Q
erstellt : 07.06.2022 11:22:34
datenbankgröße : 7773 kB
collation : German_Germany.1252
beschreibung :
backup : 11.12.2022 00:00:03
id : 115715
datenbank : Test_DB_LM
erstellt : 03.11.2022 15:55:00
datenbankgröße : 7805 kB
collation : German_Germany.1252
beschreibung : TEST11
backup : 11.12.2022 00:00:03
However, I get this error when trying to insert this data into SQL Server:
Exception when calling ExecuteNonQuery with 0 argument(s): "The multi-part identifier "System.Data.DataRow.id" could not be bound."
I'm not sure what the exact problem is, but if I had to guess, I would say that the values are not being interpreted, because I have more than one table, as you can see here:
PS C:\WINDOWS\system32> $SqlCommand
Connection : System.Data.SqlClient.SqlConnection
NotificationAutoEnlist : True
Notification :
Transaction :
CommandText :
insert into postgres.DATABASE_TABLE (ID, Datenbank, Erstellt, Datenbankgröße, Collation, Beschreibung, Backups)
VALUES (System.Data.DataRow.id, 'System.Data.DataRow.datenbank', 'System.Data.DataRow.erstellt', 'System.Data.DataRow.datenbankgröße', 'System.Data.DataRow.collation', 'System.Data.DataRow.beschreibung', 'System.Data.DataRow.backup');
ColumnEncryptionSetting : UseConnectionSetting
CommandTimeout : 30
CommandType : Text
DesignTimeVisible : True
Parameters : {}
UpdatedRowSource : Both
I tried to solve the problem with parameters but that doesn't work either.
$SqlCommand.CommandText = @"
insert into postgres.DATABASE_TABLE (ID, Datenbank, Erstellt, Datenbankgröße, Collation, Beschreibung, Backups)
VALUES ("@id", @da, @qw, @er, @rt, @tz, @zu);
"@;
$SqlCommand.Parameters.Add("@id", $DatenBankTable);
and I get this error:
The table type parameter '@id' must have a valid type name
PS C:\WINDOWS\system32> $SqlCommand.CommandText = @"
insert into postgres.DATABASE_TABLE (ID, Datenbank, Erstellt, Datenbankgröße, Collation, Beschreibung, Backups)
VALUES ("@id", @da, @qw, @er, @rt, @tz, @zu);
"@;
$SqlCommand.Parameters.Add("@id", $DatenBankTable);
$null = $SqlCommand.ExecuteNonQuery();
CompareInfo : None
XmlSchemaCollectionDatabase :
XmlSchemaCollectionOwningSchema :
XmlSchemaCollectionName :
ForceColumnEncryption : False
DbType : Object
LocaleId : 0
ParameterName : @id
Precision : 0
Scale : 0
SqlDbType : Structured
SqlValue :
UdtTypeName :
TypeName :
Value : {13012, 83481, 1, 13011...}
Direction : Input
IsNullable : False
Offset : 0
Size : 0
SourceColumn :
SourceColumnNullMapping : False
SourceVersion : Current
Ausnahme beim Aufrufen von "ExecuteNonQuery" mit 0 Argument(en): "Der table-Typparameter '@id' muss einen gültigen Typnamen haben."
In Zeile:11 Zeichen:1
+ $null = $SqlCommand.ExecuteNonQuery();
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : ArgumentException
I am really lost and I have been trying to solve this problem for 3 days. Any help is greatly appreciated
