Equivalent To Informix/DB2 Synonym
Apr 28, 2004
Hi all. Informix and DB2 support something called synonyms that allow you to basically create sort of an alias for a table at the database level. Think of it sort of as a shortcut or link to a table. Does SQL Server 2000 have a similar ability and if so how?
I know someone will ask why you want to do this, so heres a quick example:
If you have one legacy application that expects to write to one particualr table, but you wish to partition that table across several tables, you can break table1 up into tablea,tableb,tablec and then create a synonym called table1 that would point to only the appropriate table at the appropriate time. This way you can break a huge HUGE table up into logically discreet smaller tables and manage the creation of the appropriate synonym in some wrapper that sits in front of the legacy application...thus allowing you to retool a table that has outgrown its original design without having to crack open dreaded legacy code.
So, anyone?
View 1 Replies
ADVERTISEMENT
Jul 23, 2005
Hello. I have an Informix SQL statement that I need to run in MS SQLServer. When I try to execute it I get the following error message fromQuery Analyzer:Server: Msg 195, Level 15, State 10, Line 4'date' is not a recognized function name.Can anyone help me convert this informix sql statement into and MS SqlStatement? Here is the query I have:selecta.comp_code,a.comp_date,case when date(date(date(comp_date - day(comp_date) +1) - 2 unitsmonth) - 1 units day) < b.inception_date then b.inception_dateelse date(date(date(comp_date - day(comp_date) +1) - 2 units month) - 1units day)end prior_date,a.net_return,a.net_uv,a.gross_return,a.gross_uv,a.estimated_flagfrom composite_perf a, composite_detail bwhere(month(a.comp_date) in (3,6,9,12) or a.comp_date = b.inception_date)and a.comp_code = 'AEU'and a.comp_code = b.comp_codeinto #tmp_composite_dataThanks in advance.
View 4 Replies
View Related
Oct 4, 2006
Is it possible to create a SYNONYM that does not require you to use the owner prefix? Similar to an Oracle PUBLIC synonym.
for example:
CREATE SYNONM MySynonym FOR dbo.myfunction.
SELECT MySynonym FROM dbo.mytable;
View 3 Replies
View Related
Apr 12, 2006
Suppose that a synonym foobar exists pointing to the table foo.bar. I also have a table with the same name in my schema. (mpswaim.foobar)
If I do a select
select * from foobar
Which table does the select run against? mpswaim.foobar, or foo.bar?
In Oracle, mpswaim.foobar would win, and we used this to all ow individual developers to have their own version of application tables during development.
View 1 Replies
View Related
Jul 15, 2006
I get an error in query analyzer when running (parsing query):
CREATE PUBLIC SYNONYM LIB_GROUP_PERMITS FOR LIB_GROUP_PERMITS;
with an error of:
"Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'PUBLIC'."
Can anyone help me at all please!
Thanks!
View 3 Replies
View Related
Feb 25, 2008
I ran the following command to create a synonym for a function -
create synonym testfunc for myschema.myfunc
Then testfunc will be created in the dbo schema. When I call this function from my stored procedure by 'testfunc', I received an error indicating 'testfunc' is not a recognized build-in function name. If I call it by 'dbo.testfunc' then it will work.
If I create a synonym for a table, I can access the table using the synonym in my stored procedure without any problem.
Is it true that synonym works differently on tables vs. functions?
View 4 Replies
View Related
Oct 23, 2006
I may just be completely missing something here but, when I view a query plan from a SQL statment that involves a join with a synonym I do not see any reference to the synonym or the underlying table referenced by it in the query plan? Any thoughts?
Thx!
View 5 Replies
View Related
Aug 15, 2006
Can anyone tell me why I am getting this error when I try to select * from a table through a newly created synonym? I have admin rights to both db, but they are on separate servers.
<Error>
OLE DB provider "SQLNCLI" for linked server "srvDEV" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18456, Level 14, State 1, Line 0
Login failed for user 'NT AUTHORITYANONYMOUS LOGON'.
</error>
<code>
CREATE SYNONYM ARContractTerms_syn FOR srvDEV.EricsAdeptCastle.dbo.tblARContractTerms
SELECT * FROM ARContractTerms_syn
</code>
Am I running into schema problems?
Thanks all
Microsoft SQL Server Management Studio 9.00.2047.00
Microsoft Analysis Services Client Tools 2005.090.2047.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 6.0
Microsoft Internet Explorer 6.0.2900.2180
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600
View 2 Replies
View Related
Mar 17, 2007
I am working with SQL Server 2005, here is what I am doing:
declare @cx as varchar(100)
set @cx = substring(db_name(), dbo.instrrev('_', db_name()) + 1, datalength(db_name()) - dbo.instrrev('_', db_name()))
exec ('CREATE SYNONYM tblsynonym FOR ' + @cx + '..TableName')
---Procedure
drop synonym tblsynonym
The application i'm working on uses stored procedures that will at some point be called by more than one user at a time. At the start of the stored procedure the synonym is created and then it is dropped when the procedure completes, the issue is this: if two users access the same stored procedure at the same time then the first procedure will create the synonym and the second will fail because the syonym already exists.
Here is what I would like to do:
declare @cx as varchar(100)
declare @timestamp as datetime
set @cx = substring(db_name(), dbo.instrrev('_', db_name()) + 1, datalength(db_name()) - dbo.instrrev('_', db_name()))
exec ('CREATE SYNONYM tblsynonym' + @timestamp + ' FOR ' + @cx + '..TableName')
---Procedure
drop synonym tblsynonym
Any ideas??????
View 7 Replies
View Related
Jan 25, 2006
I'd like to use a data flow task to load data into a table by specifying the synonym name of the destination table, instead of the actual table name.
The OLE DB Destination is forcing me to pick an actual table or view from a drop down list. Any ideas on how to get around this?
Thank you.
View 4 Replies
View Related
Apr 16, 2015
I'm using SQL Server 2008R2. I am developing a database which requires access to data from other servers. So far I have been creating views using OPENQUERY (where there's a performance benefit) to select specifically the columns I want. Generally, for my purposes, I find these OPENQUERY based views to perform better (some times significantly so) to simple SELECT <COLUMNS> FROM <SERVER>.<DATABASE>.<SCHEMA>.<TABLE> WHERE <Where clause Statements> format views. My understanding is that this is because an OPENQUERY "pushes" the query processing to the remote server and simply returns the final result set to the local server i.e. there's no cross-server join/synchronization going on.
My question is, if I were to create a Synonym for a table object on the remote server, where does the processing happen if I query from this Synonym or create a join with this synonym to a table in my local database?Essentially, I am trying to understand if there are any "hidden gotcha's" primarily from a performance perspective, to using synonyms.
View 1 Replies
View Related
Apr 23, 2015
A heavily-selected database will be in an inconsistent state for several hours during a batch process. For that time, a database snapshot is created and accessed instead. To allow constant client read access to the database, a database that only contains synonyms exists. Those synonyms point to the main database except during the batch process, at which time they point to the database snapshot.
To switch the synonyms, each synonym is dropped and then created pointing to the database snapshot (after its creation, of course). The drop/create occurs inside a transaction. Roughly, the SQL looks like this:
SET XACT_ABORT ON;
BEGIN TRANSACTION;
DROP SYNONYM [dbo].[some_proc];
CREATE SYNONYM [dbo].[some_proc] FOR [snapshot_db].[dbo].[some_proc];
GRANT EXECUTE, SELECT ON [dbo].[some_proc] TO public;
COMMIT TRANSACTION;
When the batch update is completed, the process is reversed with "snapshot_db" replaced with "regular_db". The SQL snippet above is dynamic SQL. What I've pasted is the dynamic SQL that is executed as a single batch.
While this switch is happening, clients are accessing the procedures through the synonyms, potentially at a high request rate. Testing reveals that clients can get the error:
Error=-2147217900, Id=0, Meaning=IDispatch error #3092,
Source=Microsoft OLE DB Provider for ODBC Drivers,
Description=[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored procedure 'dbo.some_proc'.
This error only occurs once. If the same SPID retries its request and the transaction has not completed (for testing, a delay was added), then it blocks until the transaction completes.
Any way to prevent it besides a client-side retry?
View 2 Replies
View Related
Jun 17, 2014
I need to get the object type (view, table ...etc) for a synonym base object inside a script. The only place where I see something related to this stored is in column "base_object_name" in sys.synonyms but there I can see only the same with format [database].[user/schema].[name]. After some testing playing with different users without specifying database/schema I think that maybe the object_id must be stored in a another place, my first idea was parent_object_id in sys.synonyms but it isn't stored there.
know if object_id for the base object is stored in any other place ?
View 6 Replies
View Related
Sep 2, 2004
Any SQL Server people out there with Informix experience?
View 7 Replies
View Related
May 16, 2008
Please help. I am trying to translate this to SQL 2005. This language is in Informix SQl. I do not know anything about informix. I am especaially confused with the "JOINS". Any help would be appreciated. THANKS.
SELECT ppi.id,
ppi.property_id,
ppi.party_id,
ppi.address_id,
ppi.eff_from_date,
ppi.eff_to_date,
address.line_1,
address.line_2,
address.line_3,
address.city,
state.code_table_cd,
address.zip_postal_code,
country.code_description,
address.line_care_of,
prop_role.code_table_cd
FROM property,
address,
ppi,
outer code_table state,
outer code_table country,
code_table prop_role
WHERE property.id = ppi.property_id
AND ppi.address_id = address.id
{AND (property.eff_to_date IS NULL OR property.eff_to_date >= TODAY)
AND (ppi.eff_to_date IS NULL OR ppi.eff_to_date >= TODAY)
AND ppi.eff_from_date <= TODAY}
AND state.id = address.province_state_cd
AND country.id = address.country_cd
AND prop_role.id = ppi.prop_role_cd
AND prop_role.code_table_cd = "OWNER"
AND property.pact_code <> 'PERS'
ORDER BY ppi.id
View 1 Replies
View Related
Jul 10, 2007
how can I create a database synonym for the actual database name?
View 3 Replies
View Related
Jul 14, 1999
I have downloaded the datadirect ole db provider for informix 7.x and am need the correct sp_addlinkedserver syntax to get my sql server 7 and informix hooked up. Can anyone help?
Thanks!
Dean
View 1 Replies
View Related
Apr 8, 2004
Does anyone have any pointers on how i may import an Informix database into my SQL 2000 server?
What ODBC driver would i need?
Can i connect via DTS to import the data?
The application is not going to be moved, i am only interested in getting the data into a SQL2000 environment.
TIA
View 1 Replies
View Related
Oct 16, 2001
Hey everyone,
I'm trying to hit an informix box... I've found two software packages (both priced in the THOUSANDS of dollars)... Looking for a cheap...er, a FREE way to hit this informix box via DTS.
Thank you!
View 1 Replies
View Related
Jan 14, 2004
Hi,
I want to transfer data from an Informix database using DTS. What driver do I need to use for that? What are the settings for the Data Source Name? Has anyone of you accomplished DTS from Informix to SQL Server?
Let me know if you have any ideas that you can share.
Thank you.
Vivek
View 7 Replies
View Related
Mar 18, 2008
My task is to convert this Informix SQl Script to SQL 2005, unfortunately I am not really familiar with Informix SQL. Below is an example script. I can figure out the "Select" statement and the "Where" statement, but the "From" statement is giving me a hard time. Any assistance would be appreciated.Thanks
set explain on;
UNLOAD TO '/export/home/permit/propinact.txt'
SELECT distinct property.parcel_number,
property.id,
property.eff_from_date,
property.eff_to_date,
property.situs_address_id,
legal_lines.legal_desc_line,
tax_code_area.tca_number,
usecd.value,
ct.code_description,
property_char.value,
property.property_status_cd,
legal_description.section,
legal_description.township,
legal_description.range,
property.pact_code
FROM property,
OUTER (legal_description, legal_lines),
outer property_char,
outer (property_char usecd, code_table ct),
tax_code_area,
prop_valuation
WHERE property.id = prop_valuation.property_id
AND property.id = legal_description.property_id
AND property.eff_to_date <= TODAY
AND legal_description.id = legal_lines.legal_id
AND legal_lines.line_nr = 1
AND tax_code_area.id = prop_valuation.tca_id
AND property.id = property_char.property_id
AND property_char.prop_char_typ_code = "SIZE"
AND property.id = usecd.property_id
AND usecd.prop_char_typ_code = "USECD"
AND usecd.value = ct.code_table_cd
AND property.pact_code <> 'PERS'
ORDER BY id
View 2 Replies
View Related
Apr 4, 2006
Hello,
I'm getting the below errors when using sql server's data import wizard. Please Note: My test connection was successful and the error occurs when I click the "Next" button after closing the properties window. For Security reasons I've filled the User Id and DataSource with a bogus account, database, and server i.e. xxxx. Notice the password is blank?... Is that normal? I'm in a jam and appreciate your help.
Thanks,
Matt
===================================
Cannot get the supported data types from the database connection "Provider=Ifxoledbc;Password=;Persist Security Info=True;User ID=xxx;Data Source=xxx@xxxx". (SQL Server Import and Export Wizard)
===================================
IErrorInfo.GetDescription failed with E_NOINTERFACE(0x80004002).
IErrorInfo.GetDescription failed with E_NOINTERFACE(0x80004002). (System.Data)
------------------------------
Program Location:
at System.Data.OleDb.OleDbConnectionInternal.GetSchemaRowset(Guid schema, Object[] restrictions)
at System.Data.OleDb.OleDbConnection.GetOleDbSchemaTable(Guid schema, Object[] restrictions)
at Microsoft.SqlServer.Dts.DtsWizard.DataSourceInfo.LoadTypeData(OleDbConnection myDestConnection)
at Microsoft.SqlServer.Dts.DtsWizard.DataSourceInfo.CacheDataSourceInfo(IDbConnection myConnection)
View 10 Replies
View Related
Oct 8, 2007
We are using Informx for our transactional DB and want to be using reporting services to provide reports. We have some using ODBC directly to Informix, but need to start extracting data from Infomix
and aggregating it in SS 2005.
Is anybody else doing this?
Where can I find information of how to do this? We are totally new to SSIS.
Thanks
JPS
View 1 Replies
View Related
Oct 12, 2007
I need to create packages that connects to a client's Informix DB server. When I tried to create a new OLEDB datasource from configuration manager, I could not find any Informix driver option. Can someone shed some light as to what I'm missing and the steps I need to take to be able to source data from Informix? Any drivers that needs to be installed and configured on the server and the developer's machine?
Thanks in advance.
PS. I discovered from the link below that IBM's Informix OLEDB driver does not support SSIS. Can someone validate this claim and what alternatives I have?
http://ssis.wik.is/Data_Sources
View 1 Replies
View Related
Nov 2, 2007
I am having trouble using an OLE-DB source in my data flow task. I am using Informix. In the OLE DB source editor I get the following message:
Warning at {4f24a418-f95e-4185-8989-4f960aaabd68} [OLE DB Source [1]]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.
Using the preview button in the OLE DB source editor, it maps the integers to DT_I4 so most of the numbers in the preview convert as null and that is how they end up in the target. I added a data conversion component and they still end up in the target as null.
View 4 Replies
View Related
Feb 21, 2000
Hi ,
I am an Informix user who has shifted to SQL Server,,,,I just wanted to know the equivalent of 'ISOLATION TO DIRTY READ' as in Informix in SQL SERVER....
Thanks,
Visitor
View 1 Replies
View Related
Jan 5, 2001
Hi,
Could anyone tell me how the data can be ported from informix to sql server ?
Is there a way to run the informix query from sql server ?
Thanks,
Vijay.
View 1 Replies
View Related
Oct 25, 2000
Hi all,
Anyone here ever used the Informix database and can give me some differences between Informix and SQL.
One of our users is thinking about purchasing a COTS product that only supports an Informix database. I need to convince the user to evaluate other rival applications that can support SQL and need some arguments in favor of not going with Informix.
Any ideas appreciated,
Faustina
View 6 Replies
View Related
Oct 6, 2006
Hi,
I am considdering to link a Informix database (running on SCO Unix) to a SQL Server 2005. Only problem is ... I have no idea how to do this! The reason I'm attempring this is to try and see if it would fix a problem I'm experiencing when using Reporting Services to generate reports from a Informix data source. I've been told that setting up a linked server might fix the problem. If anyone could perhabs guide me through setting up the linked server I would be very grateful! The ODBC credentials for setting up a connection to the Informix DB as follows:
Servername : pal_tli
Hostname : server_name
Service : pal_ser
Protocal : sesoctcp
Database : db_name
Username : user_name
Password : pword
:confused:
View 1 Replies
View Related
Dec 19, 2013
Migrate an Informix table (and eventually an entire database) to MS SQL Server 2012. This is my first experience using SSIS. I started off using the SSIS Import/Export Wizard to create a package. I was actually able to successfully copy a table, but it just didn't copy all the data. It skipped all the data that was in string columns. It created the columns with the proper datatype in the destination table, it just didn't copy the data. The other columns all copied properly. I need to do to get the string column data to copy?
View 7 Replies
View Related
Jan 15, 2004
Our Informix server is struggling with all the reports we run and so we are thinking of making a dedicated server for reporting.
SQL is an obvious choice because we have it already for our retail system.
However, the challenge is how to download the data we need each night. DTS works a treat but it is the volume of data that is the problem.
We are a retail operation and we need to download the transactions from our Informix server into SQL. This data gets into Informix from the EPOS system in our stores.
What we don't want to do is download everynight the entire back history of transactions. We could do this by using the date of the transactions but we discovered it wont work.
The problem is that if a store doesn't post their transactions e.g. because of a system failure then these will get missed.
What we need to do is record which transactions are downloaded into SQL and then compare this against what is on the Informix server and then download the difference each night.
We thought of adding a flag onto the Informix server but we are not able to make any modifications to it.
I think we could log the downloaded transactions in a SQL table and then use this as a record of what has been downloaded. We could then run a query that compares this to what is on the Informix server.
With the right indexes I think this could work really well. Any thoughts? Incidently the two servers are separated by a 512Kbps wan link......
View 1 Replies
View Related
Mar 1, 2004
Hi, folks!
I want to pump data from Informix to SQL Server using DTS to fill / fresh up my SQL-Server data warehouse every day.
In the SQL documentation I found "Don't use the ODBC driver from Informix"
Which is a good choice of ODBC/OLE-DB drivers to deal with Informix via SQL servers DTS?
Regards from Berlin!
SeBaFlu
View 3 Replies
View Related
Apr 13, 2004
Dear All,
I'm using the DTS to transfer data from Informix 7.30 UC9 to MS SQL 2000 Enterprise. The tables I'm transferring contains data for BaaN ERP.
While transferring certain tables, I am successful. On some, I get an error that says "Data overflow". It was caused by "DBTYPE_DBTIMESTAMP".
Any idea why this happens?
Thanks!
View 2 Replies
View Related
