Questions tagged [synonyms]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0
votes
3answers
53 views

Create Synonym error for a user-SQL Server

I have a user who has permission to create synonym in my sql server 2016 database.That user also has data reader permission. When the user tried to create synonym as shown below. USE [DEMODB] GO ...
0
votes
1answer
65 views

How to generate ddl for a synonym

what is the query used to generate ddl of synonym in one environment so that I can run it in the other environment
1
vote
2answers
64 views

Resetting a new archiving database

I have an archiving question. We have a History DB used for archiving old records from our main system and the application knows to query it However, it becomes too big. I would like to ...
1
vote
0answers
31 views

Postgres - want a database to have two names

One of my Postgres databases is named entirely in upper-case, and a new bit of software needs to see it in lower-case. I've tried renaming it, but unfortunately that breaks the huge multitude of ...
0
votes
0answers
36 views

How can I drop an undefined object

I'm struggling on creating synonyms for some package (that compiled successfully). Java application display this error: identifier 'blackPackage' must be declared When I try create public synonym ...
4
votes
1answer
244 views

Does restoring a database break external synonyms targeting objects in that database?

If one has a database where synonyms in another database refer to objects in that database, does restoring a backup into that database invalidate the synonyms? To be specific, imagine this situation: ...
1
vote
1answer
19 views

Synonym being recreated randomly - possible to create some kind of trigger/log?

We have a couple of databases with synonyms to link each other, one of the synonyms is randomly being recreate incorrectly with an additional set of square brackets in the database name. I've checked ...
-1
votes
2answers
600 views

How to create a synonym for a column in SQL Server?

The following is not working. I want to create a synonym on a column in SQL Server 2017. How would I go about doing this? CREATE SYNONYM dbo.[ProductTest] for [dbo].[Product].[ProductId] GO select ...
0
votes
1answer
48 views

Is it possible to limit the scope of a SYNONYM in SQL Server?

I have a series of scheduled stored procedures that need to be run against different databases that have identical schemas (think multiple instances of an application). So for instance, every database ...
1
vote
0answers
10 views

Querying a synonym (for a table using linked server) gives no data in return

I have a function which run in an application at a server. This server have a ODBC connection (user DSN) to the SQL server which is set to use windows authentication. The function is supposed to ...
0
votes
1answer
52 views

What happens with a synonym if the table which its based on gets deleted?

we are working on a project to implement a large ERP system. We found out that one of the tables has a synonym to another database. Both databases are in the same instance. Our project leader has ...
0
votes
1answer
41 views

Is there a way to identify the object behind the database synonym?

I have a script which uses a synonym. I have to alter that code and the problem is I can't understand which object that synonym refers to. Is there a way to find out? I am using the original database ...
0
votes
1answer
190 views

How to join sys.columns and sys.synonyms table using object_id column [duplicate]

I have used two servers, Server A(local server) and Server B(remote server). I have created synonyms in the Server A for the Server B table. I have created synonym using following syntax. CREATE ...
4
votes
2answers
1k views

How to fetch the columns of the synonyms for linked servers in SQL server

I am using two servers local (SQL Server running on my system) and remote (SQL Server running on another system). I have created synonyms in my local server for a table which is located on the remote ...
0
votes
1answer
33 views

Synonyms from instance

I have two different DB instances. Is there any way to find out from instance2 (from all_synonyms view) list of all objects using synonyms from instance2 to instance 1 ...? I want to, for example, ...
3
votes
1answer
121 views

Any way to make stored procedure code dynamically reference other databases, without dynamic sql?

I've a situation where I have two databases: ABCData ABCUsers One of the stored procedures in ABCData needs to perform an insert to a table in ABCUsers. This could just be hard coded: INSERT ...
0
votes
2answers
764 views

Table behind synonym does not exist

when I do select * from all_synonyms where synonym_name='SYNAAA' I can see synonym SYNAAA is public, and table behind it is TABAAA in schema A. Given SYNAA is public, I should be able to query it ...
2
votes
2answers
698 views

Is Synonyms to tables cost effective within SQL Server 2016

For one of our new projects we are thinking of using lots of Synonyms to tables which refer to another Database and we are fully aware on what it does and how we are going to use them. But what we don'...
1
vote
1answer
156 views

SQL Server - redirect synonym without downtime

I have multiple application databases that use synonyms point to a shared database with immutable data that needs to be updated periodically by restoring an external backup. We now have a new ...
1
vote
2answers
544 views

how to grant permissions to all the synonyms inside a view?

I have to grant permissions to a view. dbo.my_view, however, this view joins many tables, and other views and synonyms. using the following script I can find all all the synonyms in my server: ...
0
votes
1answer
41 views

View and Synonym hirarchy oracle

The Dba of our company has created views for the tables for all the users. We have 230 users, he has created 230 views for each tables! I want to replace this by public synonym and grant select to the ...
0
votes
1answer
85 views

PostgreSQL: Index Trigger / Hook? For Synonyms

Is it possible, in PostgreSQL, to add a trigger to modify data prior to it going into a full text index? PostgreSQL requires using files to add custom dictionaries for things like stop words, ...
0
votes
1answer
580 views

Is it possible grant a PRIVATE Synonym from an the Table Owner schema to an User schema?

Is it possible grant a PRIVATE Synonym from an the Table Owner schema to an User schema? Background: We are doing microservice development and there is an sole owner schema and many different service ...
1
vote
1answer
816 views

Synonym/Alias equivalent for a Database

If we have code in an SP Database1 then queries Database2 (on the same server) we want the same code to work on the databases Database1Dev and Database2Dev. But this currently means editing the full ...
1
vote
0answers
70 views

listing the permissions for a database object - and take into consideration it can be a synonym

I have a script that gives me all the permissions assigned to a database object: ------------------------------------------------- -- permissions to database object -- marcelo miorelli v. 20170401 ---...
0
votes
1answer
39 views

wildcard in creating synonymys

I want to create a synonyms for the user in my database. These are my user and my schema owner User : Scoot Schema Owner : kitty I would like to create a synonmys "Scoot" for accessing all the ...
2
votes
1answer
2k views

Creating Synonym For a Table in PostgreSQL over dblink?

I have two PostgreSQL databases connected over dblink extension. I want to create synonym for a table in first database so the queries that are coming through application I can route them to the table ...
3
votes
2answers
4k views

Oracle 12c - Create materialized view results in ORA-00942

I have a view that I can access through a database link as such: SQL> select count(*) from REMOTE_SCHEMA.REMOTE_VIEW@REMOTE_DB; COUNT(*) -------- 110 I can create and successfully query the ...
-2
votes
1answer
79 views

Post DB users into replica users the Synonyms still point to Source DB users

I have exported users HR,Fin AND imported into new users HR1,FIN1 in the same DB. The expdb is used for this purpose.The synonyms still point to the source users (HR,FIN) Example : Synonym def in ...
0
votes
2answers
358 views

INSERT INTO SQL Server : Linked Server without specifying database in semi-fully qualified name

I have a server called DEV, a database called Nus12345, a linked server called SnapshotDev, a schema called Snapshot, and a table called SnapshotPersonEmployee. On the DEV server, there are many ...
1
vote
1answer
2k views

How can I fix these broken synonyms?

When I run the following query: SELECT S.OWNER AS SYN_OWNER, S.SYNONYM_NAME AS SYN_NAME, S.TABLE_OWNER AS OBJ_OWNER, S.TABLE_NAME AS OBJ_NAME, CASE WHEN O.OWNER IS NULL THEN '...
1
vote
0answers
36 views

Synonims in SQL Server 2008 R2 FTS

The Search feature on my website is based on SQL Server's Full Text Search (I insert everything relevant to a single table and this table is in the FTS Catalog). We don't have money to buy a ...
3
votes
1answer
152 views

Changing database name based on project environment

I have three environments for our BI project (DEV, QA, PROD) and two databases in each environment, source(EDB) and target(EDM) DEV environment: Source = EDB_DEV Target = EDM_DEV QA environment: ...
1
vote
1answer
61 views

How to synonymize a database name?

Within one database instance, I need to restore a set databases into new names. The new names will be suffixed based on the development phases, like: _DEV and _QA. Where: DBApp_DEV shall access ...
1
vote
0answers
2k views

Postgres Public Synonym Issue

PostgreSQL ver 9.4.5. Linux OS. Application: Web Based Platform: App Server (java) --> jdbc call --> Database Server (PostgreSQL) I do know that PostgreSQL does not support the public ...
-4
votes
1answer
608 views

how to create a synonym in Oracle? [closed]

some one tell me the basic syntax for creating synonym for the table employees create synonym for emp for app.employees while creating it shows error ORA-00995: missing or invalid synonym ...
1
vote
4answers
2k views

Synonym with same name as linked server

I have a database that is used for reporting services. This database was created by a contractor who is no longer around. Buried all throughout the database are references to a single table on a ...
0
votes
1answer
1k views

Unable to update or insert values table with DateTime column defaulted to GETDATE

I have atlered table by adding Datetime column. This column is not null and also default value from GETDATE(). ALTER TABLE myTable ADD myDateColun DATETIME NOT NULL DEFAULT (GETDATE()) I'm trying ...
4
votes
2answers
6k views

How to List SYNONYMS that no longer point to a valid object?

I have a table (OBJECTS_TO_UPDATE) with several objects I need to update, some are SYNONYMs for objects that no longer exist. I want to create a query to list them. But I am stuck. My table ...
2
votes
1answer
11k views

ORA-00942: table or view does not exist while creating synonym

We are using oracle 11g, the db structure goes like this: We have users CST and FAS. CST contains a table ST_CAT2, a public synonym ST_CAT has been created for the table CST.ST_CAT2 as a SYS user. ...
2
votes
3answers
691 views

How to separate Stored Procedures (i.e. all the business logic) from Client Database so that it only contains client data?

Scenario: Our Application Database (in SQL Server 2012) contains entire business logic in the Stored Procedures (SPs). Everytime we have to publish the DB to the client, it unnecessarily results in ...
1
vote
1answer
706 views

How do I differentiate between user-defined public synonyms and built-in public synonyms?

I am trying to query the Oracle ALL_SYNONYMS view, but filtering by OWNER=PUBLIC returns both synonyms I have created and Oracle created ones. Is there a way to distinguish between the two?
4
votes
2answers
2k views

Synonyms and transactions

I'm working with an application that uses SQL synonyms to switch between tables on local and remote databases without duplicating code. BEGIN TRANSACTION if (@isRemote = 1) BEGIN CREATE ...
2
votes
1answer
2k views

Getting the database from synonym base object

Risking asking something idiotic, but not easily got by Google-fu, but how to query the database id (and other information) from the base object of an synonym? The synonym in question is an remote ...