Ошибка cross database references are not implemented - Решение и исправление самых разных ошибок на TopOshibok.ru

I am trying to convert SQL inner join query into PostgreSQL inner join query. In this inner join query which tables are using that all tables are not present in one database. we separated tables into two databases i.e. application db and security db

users and permission table are present in security db
userrolemapping and department are present in application db

I tried like below but I am getting following error

Error

ERROR:  cross-database references are not implemented: "Rockefeller_ApplicationDb.public.userrolemapping"
LINE 4:         INNER JOIN "Rockefeller_ApplicationDb".public.userro..

SQL Stored Function

SELECT   Department.nDeptID 
    FROM Users INNER JOIN Permission 
         ON Users.nUserID = Permission.nUserID INNER JOIN UserRoleMapping
         ON Users.nUserID = UserRoleMapping.nUserID INNER JOIN Department
         ON Permission.nDeptInst = Department.nInstID
         AND  Department.nInstID = 60
    WHERE     
         Users.nUserID = 3;

PostgreSQL Stored Function

SELECT dep.ndept_id 
        FROM "Rockefeller_SecurityDb".public.users as  u 
        INNER JOIN  "Rockefeller_SecurityDb".public.permissions p ON u.nuser_id = p.nuser_id
        INNER JOIN "Rockefeller_ApplicationDb".public.userrolemapping as urm ON u.nuser_id = urm.nuser_id
        INNER JOIN "Rockefeller_ApplicationDb".public.department dep ON p.ndept_inst = dep.ninst_id
           AND  dep.ninst_id = 60
                        WHERE     
                            u.nuser_id = 3;

asked Aug 10, 2018 at 10:52

You cannot join tables from different databases.

Databases are logically separated in PostgreSQL by design.

If you want to join the tables, you should put them into different schemas in one database rather than into different databases.

Note that what is called “database” in MySQL is called a “schema” in standard SQL.

If you really need to join tables from different databases, you need to use a foreign data wrapper.

answered Aug 10, 2018 at 12:43

Laurenz AlbeLaurenz Albe

211k17 gold badges207 silver badges266 bronze badges

For future searchs, you can to use dblink to connect to other database.

Follow commands:

create extension dblink;

SELECT dblink_connect('otherdb','host=localhost port=5432 dbname=otherdb user=postgres password=???? options=-csearch_path=');

SELECT * FROM dblink('otherdb', 'select field1, field2 from public.tablex')
AS t(field1 text, field2 text);

answered Jul 22, 2020 at 11:58

New to postrgreSQL and I had the same requirement. FOREIGN DATA WRAPPER did the job.

IMPORT FOREIGN SCHEMA — import table definitions from a foreign server

But first I had to:

enable the fdw extension
define the foreign server (which was the locahost in this case!)
create a mapping between the local user and the foreign user.

CREATE EXTENSION postgres_fdw;

CREATE SERVER localsrv
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'localhost', dbname 'otherdb', port '5432');

CREATE USER MAPPING FOR <local_user>
SERVER localsrv
OPTIONS (user 'ohterdb_user', password 'ohterdb_user_password');

IMPORT FOREIGN SCHEMA public
FROM SERVER localsrv 
INTO public;

After that I could use the foreign tables as if they were local. I did not notice any performance cost.

Jeremy Caney

7,12271 gold badges48 silver badges77 bronze badges

answered Dec 6, 2021 at 20:40

In my case, I changed my query from:

SELECT * FROM myDB.public.person

to this:

SELECT * FROM "myDB".public.cats

and it worked.

You can read more at mathworks.com.

answered Feb 20, 2022 at 9:57

BehnamBehnam

1,0492 gold badges14 silver badges39 bronze badges

Источник

Introduction

The restore process which is focusing only into the execution of an insert statement ended in a failure. Actually, there is an SQL file containing hundreds of records or rows available as values for insert query in its associated columns. But the process for importing those records or row data fail because of a certain cause. The following is the execution of the process for importing records or row data from an SQL file with the name of ‘insert-active-employee.sql’ :

C:\>psql -Udb_user -d db_app < "C:\Users\Personal\Downloads\insert-active-employee.sql" 
Password for user db_user: ERROR: cross-database references are not implemented: "main.dbo.employee" 
LINE 1: INSERT INTO main.dbo.employee (name,birthdate,address... 
^

Solution

Actually, the solution for solving the above problem is very simple. It exist in the name of the database which is being the target for the import process. There is no cross-reference database in this context. The SQL file actually exist as the process from Microsoft SQL Server backup or SQL insert statement generated process. In other words, the SQL file source is from Microsoft SQL Server. But the target for the restore process is not a Microsoft SQL Server. Instead, it is a PostgreSQL database server as the target of the database. The solution is very simple, just replace the cross-database references above with another suitable format.

So, check the database PostgreSQL name and then look up for the table. Actually in the command for restoring or importing the records or the row data, the database name is already becoming part of the value from one of the argument. The argument exist in ‘-d db_app’ where the database name is ‘db_app.

The only part left is to edit the SQL file further. Just replace the cross-database references exist in the above which is ‘main.dbo.employee’ into a name of a table from the database ‘db_app’. In this context as an example it is ”. The following is the pattern of the INSERT statement available in the SQL file before the editing process :

INSERT INTO main.dbo.employee (name,birthdate,address...  VALUES(...,,,)

Following after, below is the actual content of the SQL file after the editing process :

INSERT INTO public.org_employee (name,birthdate,address...  VALUES(...,,,)

Since, PostgreSQL has a default schema of ‘public’, so the definition of the table will have a reference of ‘public.org_employee’ where ‘org_employee’ is the name of the table itself. After editing it, just execute it once more and the INSERT query process will be proceed normally if there are no more errors exist.

Источник

Yes the tabel ( product ) in the public schema
postgreSQL version : postgresql-9.2.1-1-windows-x64
The following is restsql.properties file:

logging.facility=log4j
logging.config=resources/properties/log4j.properties
logging.dir=/geronimo/geronimo-tomcat7-javaee6-web-3.0.0-bin/var/log

sqlresources.dir=sqlresources

request.useXmlDirective=false
request.useXmlSchema=false
response.useXmlSchema=false
response.useXmlDirective=false

database.driverClassName=org.postgresql.Driver
database.url=jdbc:postgresql://localhost:5432/jeisDB
database.user=testUser
database.password=testUsers

org.restsql.core.SqlResourceMetaData=org.restsql.core.impl.SqlResourceMetaDataPostgreSql

org.restsql.core.Factory.RequestFactory=org.restsql.core.impl.RequestFactoryImpl
org.restsql.core.Factory.RequestDeserializerFactory=org.restsql.core.impl.RequestDeserializerFactoryImpl
org.restsql.core.Factory.ResponseSerializerFactory=org.restsql.core.impl.ResponseSerializerFactoryImpl
org.restsql.core.HttpRequestAttributes=org.restsql.core.impl.HttpRequestAttributesImpl
org.restsql.core.RequestLogger=org.restsql.core.impl.RequestLoggerImpl
org.restsql.core.SqlBuilder=org.restsql.core.impl.SqlBuilderImpl
org.restsql.security.Authorizer=org.restsql.security.impl.AuthorizerImpl

The following is the product.xml file:
xml version=»1.0″ encoding=»UTF-8″?
rs:sqlResource xmlns:rs=»http://restsql.org/schema» xmlns:xsi=»http://www.w3.org/2001/XMLSchema-instance» xsi:schemaLocation=»http://restsql.org/schema SqlResource.xsd «
query
SELECT productid «id», name «thisName» FROM product
/query
metadata
database default=»jeisDB» table role=»Parent» name=»product»

/metadata
/rs:sqlResource

Источник

https://www.devart.com/odbc/postgresql/docs/microsoft_sql_server_manager_s.htm

https://www.devart.com/odbc/postgresql/docs/troubleshooting_ssms.htm

Existing Architecture:

I have SQL Server 2016 installed on On-premise VM.
I have PostgreSQL Installed on Azure Ubuntu VM. Also DBeaver client tool has been installed on this VM to query PostgreSQL DB.
https://scalegrid.io/blog/which-is-the-best-postgresql-gui-2019-comparison/

Requirement:

Want to create a link server between SQL Server and PostgreSQL DB so that PostgreSQL query can be executed from SSMS.
Need to create a SSIS connection to connect to PostgreSQL DB to pull data from it.

Steps:

Download the ODBC Drivers (32-bit and 64-bit both) from link:
https://www.postgresql.org/ftp/odbc/versions/msi/ . You need 32-bit for SSIS packages and 64-bit for Linked server (If your SQL server is 64)
Install PostgreSQL ODBC Driver: Follow this link for installation.
https://www.mssqltips.com/sqlservertip/3662/sql-server-and-postgresql-linked-server-configuration—part-2/Install both 32-bit and 64-bit on your local computer as well as on SQL Server Compute.
Create ODBC Data Source: Do this for both 32-bit and 64-bit and on your local computer (this is for SSIS to create and run packages from your local) as well as on SQL server Machine. Once the driver has been installed,
it’s time to create a System DSN from it. So, let’s start the ODBC Data Source (64-bit) application from the Server Manager applet (see below) or by typing ODBC. Repeat these steps for 32-bit also.

In the next few screenshots, we can see how an ODBC data source is created.

Step 1: First let’s choose the System DSN tab and then click Add…

Step 2: Next we choose the PostgreSQL Unicode (x64) version and click Finish.

Step 3: In the dialog box that pops-up, provide a name and description for the data source, specify the database name, server’s IP address, port, username and password as connection parameters.
Once done, test the details by clicking on the Test button.

In my case I used port 5433 as 5432 was not working and after checking with infrastructure guy, I have been advised to use port 5433.

If the test is successful, click Save and then click OK in the ODBC Data Source Administrator.

Troubleshooting: When creating a linked server in SSMS, most errors happen due to security issues with DCOM class MSDAINITIALIZE. We need to alter the DCOM Class MSDAINITIALIZE security settings
on the System/Machine where SQL Server is installed to make it work.
https://www.devart.com/odbc/postgresql/docs/troubleshooting_ssms.htm

Following are the steps:

RDP to the Machine your SQL Server is installed.
Open Component Services (Start>Run>DCOMCNFG)
Expand Component Services>Computers>My Computer>DCOM Config
From the list of DCOM components on the right side, select MSDAINITIALIZE and go to its properties:
Go to the Security Tab, choose ‘Customize’ and click on the ‘Edit’ Button:
Add the Domain User (you need to add all individual users who are going to use this link server) who is accessing the linked server and ‘Allow’ all the permissions available (Local Launch, Remote Launch, Local Activation, Remote Activation). If
you are connecting to SQL server using SQL account, you need to provide this permission to the account under which the SQL service is running.
Do this for all the 3 sections in the above screenshot.

OR you can run query like this:

SELECT
*

FROM

OpenQuery([POSTGRESQL64S],

‘SELECT * From stg.pageviews’)

As a result, you can see the contents of the selected table retrieved directly from the PostgreSQL account you are connected to.

Create a SSIS Connection: Create an ODBC connection in SSIS and select DNS from dropdown. Note: With SSIS only 32-bit DNS does work.

https://mask-me.net/datamaskingwiki/wiki/168/ssis-with-postgresql-connect-to-postgresql-with-ssis-components

https://docs.microsoft.com/en-us/sql/integration-services/import-export-data/connect-to-a-postgresql-data-source-sql-server-import-and-export-wizard?view=sql-server-ver15

Thanks Shiven:) If Answer is Helpful, Please Vote

Marked as answer by
Shivendoo Kumar Dubey
Tuesday, March 31, 2020 6:20 AM

Источник

Please try renaming your database to use all lower-case letters. The lower-case naming convention is suggested by PostgreSQL, and should resolve the error you are seeing in MATLAB.

This error is due to a difference in naming conventions between SQL and PostgreSQL.

According to PostgreSQL’s standards, any unquoted names in queries are converted to lower-case letters, so when myDB is a part of the query, PostgreSQL interprets it as mydb instead. Since Database Explorer prepends the database name and schema name to the table name, and since the database name is not all lowercase, PostgreSQL interprets Database Explorer’s query for myDB.public.person as mydb.public.person. This raises an error because you are connected to myDB but PostgreSQL thinks you are trying to query mydb, which it interprets as a different database.

The way to enforce capitalization in PostgreSQL and SQL is to surround each case-sensitive name in quotes. For example, if you change the query from

SELECT * FROM myDB.public.person

SELECT * FROM «myDB».public.cats

The query should be successful.

Note that this forcing to lower-case is a PostgreSQL-specific behavior. Database Explorer is generally agnostic to the type of database being used, so this behavior is not being accounted for.

You can read more about this behavior in the PostgreSQL documentation:

The relevant section is 4.1.1, particularly the last paragraph.

Источник