Hello All,
I have a question on linking tables with SQL Server. I have SQL Server 2000 with database A on machine A. I have SQL Server 2000 with database B on machine B. I want to link tables from the database on machine A into a database on machine B. I am not quite sure how to do this - I believe this can be done as I have seen it before. Any information and or resources to direct me to would be greatly appreciated.
Thanks
Hi,
I'm looking for a possible way to access tables from SAP but in another database. This is not possible using ABAP from SAP. Therefore I wonder if it's possible to define some kind of virtual table in a database which in fact refers to a table in another MSSQL database...
Help and/or tips highly appreciated,
Paul
Hi all,
We have lookup tables we share on 6 database servers. We keep copying these very large tables to every database we use on each server, obviously a great waste of space. We don't mind copying these tables onto each of the servers but we want to be able to link to these tables from each database from within our servers. How is this done? Thanks in advance.
ddave
When trying to link to an SQL table in Access 2003, the software appears to be malfunctioning.
The sequence of events is File - Get External Data - Link Tables - Files of Type: ODBC Databases().
The Problem: On two of my computers, the select data source window does not pop up, preventing me from linking to any ODBC data source.
Observations: This function has worked normally in the recent past and works on other computers running Access 2003. One difference between the computers working and non-working computers is Norton Antivirus 2006 (recent upgrade).
Has anyone experienced anything like this? What's going on?
I have created an Access2003 project (existing data) that links to external data. First I connected to a SQL Server 2000 database. Success. Then I tried to set up a Transact SQL data connection to a legacy MDW-secured Access97 database. (A third-party VB6 application goes against it, and we don't have the source code, so we cannot upgrade it.)
The Transact SQL link tests OK but I cannot select any of the tables or queries from the list presented. However, with the same credentials, I can use these same objects in Excel 2003.
When setting up the link in Access2003, I specify JET 4.0 OLE DB Provider, I enter the MDW file on the All tab, a username and a password on the Connection tab where I browse to the MDB file, and specify Shared Deny None on the Advanced tab. When I test the connection, it tests OK ("Test connection succeeded"). Yet on the "Select the Database and Table/Cube which contains the data you want" dialog, "(Default)" appears in the grayed-out dropdown. Then, beneath that dropdown, there is a grid with Name and Description columns. The grid contains query names but the grid is not enabled. The list of queries is this table is grayed out. Neither of the scrollbars works.
BUT... if I use the SAME username and password in Excel2003, and specify the same MDW, there is no problem working with these same database objects in the legacy Access97 database. WHAT IS DIFFERENT ABOUT THE WIZARD IN EXCEL THAT ALLOWS IT TO SUCCEED AND THE WIZARD IN ACCESS THAT CAUSES IT TO FAIL HERE? In Excel, the list of available providers says Microsoft Access Driver, not JET 4.0 OLE DB Provider.
Thanks
TR
I'm a long-time Access developer who is looking to migrate his approach to SQL Server CE 3.5 instead of having all these MDBs. One thing that I do like is using Access to surf through databases; it's a lightweight way to see the data without having to fire up the overhead of Visual Studio etc. Plus there's the QBE grid for easy query design for those of us whose tool-less SQL coding is a bit rusty...
Anyway, I was wondering whether it was possible to "attach" to a SSCE database so you can see it in Access. I've tried but haven't found a way of doing it. Any thoughts? Or is this an intentional lack of capability so we have to use a more robust tool such as Management Studio or Visual Studio?
Thanks in advance!
I'm working on a report to show financial transactions from a table over a certain period. For most transactions there is a PDF document that is stored in a separate table in a binairy format. In my report I would like to include a link on every line with transaction information in the report that opens the PDF that is linked to that transaction. Just to be clear, I don't want to embed the PDF in the report but I want the users of the report to have the option to view the PDF that is related to that transaction in their standard pdf reader (adobe).
Code to do the following:
Once a user clicks on the link to view the PDF I need the code to get the binairy data of the PDF file from the table, convert it back to a PDF and open it in the default pdf reader (for example adobe reader). If it can't directly open the file then it's maybe possible to activate the 'open or download' pop up that you also get when you download something from a website.
There is a way to create a link from a SQL Server database to a table located on a MSAccess database? I mean like creating links from MSAccess to other databases. The requested table is updated many times/day, and I dont want to import the table each time an update happens.
Thanks,
Richard
Hi!
Is it possible to use database links on MS SQL-Server 2000, like on Oracle? If its possibe, what is the syntax and can i create a database link from Microsoft SQL Server 2000 to Oracle?
Nick
I am working on a databse on my local box, my source data is onanother. How can I link the database and table from one server toanother? Currently I am using DTS to just transfer the records!
View 2 Replies View RelatedHi, is correct add a relation to Asp.net_User to one another table(example orders)With column relactioned the table users with the table Orders , UserId , UserName , I am a little confused I need help!. Thank you
View 1 Replies View RelatedI have used the Microsoft code below to create a link to SQL server from access. I am using DAO not ADODB. Someone said that I should use dblib instead of ODBC in the connection string below in order to get the connection to work.
Does anyone know the correct syntax for OLE DB connection string and can it be used with DAO? I am getting an error "ISAM path not found"?? Seems I have wrong connection string syntax?? Please advise.
Thanks again for your help.
-------------------------------------------------------------------------
Sub ClientServerX3()
Dim dbsCurrent as Database
Dim tdRoy as TableDef
Set dbsCurrent = CurrentDb
Set tdfRoy = dbsCurrent.CreateTableDef("Roy")
tdfRoy.Connect = "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers"
***************IS THERE ANYWAY TO USE AN OLEDB (OR OTHER DSN-LESS)
***************CONNECTION HERE? How??
tdfRoy.SourceTableName = "Royalties"
dbsCurrent.TableDefs.Append tdfRoy
End Sub
Tell me please how can I make a foreing key constraint for a table using from another not all primary key or not all data. For example, I need to link a table "subject" with primary key "dscp_num, depart" using depart as a foreing key. But there is no table with such primary key, there is only table "codif_values" where selecting records by condition "codif_num = 1" gives the relation with necesary primary key to link. Also I need to link "subject" table as a primary key table with the table "ses_curr". But this table doesn't include "depart" field. It includes field "reg_num" that is a primary key in table "students". This table include field "stgroup" as a foreing key for table "groups". The last table includes "speciality" field that is a foreing key for table "specialities". And only this table includes "depart" field. Tell me please how I can make a foreing key constraint for table "ses_curr" by table "subject".
View 3 Replies View RelatedI need to append data to the existing Table1 from a .txt file stored in a link e.g. "http://xx00xx0123.abcdef.net/ABC_REPORTS/DATA/Table1.csv"
(Columns in both tables are identical)
This .csv contains a rolling 7 days of stats. which means if it is added every Morning 6 of those Days will have been added before and must be Deleted.
I would like to schedule an automatic procedure to create a temp table in the server every day and a script removing duplicates.
I have a few questions:
*) Can I shedule from the Enterprise Console to read/create table from the above link to do this?
**) I heard I need an SQL agent. If so why and what is it?
***) Is it better to append data and then script removing Duplicates, or is it better to import into a temp table run comparison between the 2, Delete from Temp what is Common and then append whats left of the Temp to the Table1?
****) Please could someone paste a sample of CREATE TABLE from a http link like the one above?
Thanks for your help,
Gezza
Hi,How can I run a query to figure out what tables and correspondingcolumns link to any column in my table T?Thanks, - Dave
View 1 Replies View RelatedHello all, I have a quick question. Is it possible to create linked tables in MS SQL, similar to the was MS Access does?
I have a new application(x) that is going to be dependant on another application(y). I just want to link 5 or 6 tables from y into x for the purpose of not duplicating information. Is this possible?
I apologize if I seem a little lacking in the knowledge of MS SQL, I am new to it and trying to figure it out. Any help would be greatly appreciated.
Thanks,
Dan
We are looking for a way to have sales data to flow from 1 database to another database on same SQL server. Can we build or have a link between 2 database? If yes, how to and how much effort will that be?
View 2 Replies View RelatedHi all, i'm new here. plz help me solve this question. i want to know how the login component that already have at visual studio, link with the database i created? what i mean is..when an user1 had login to the page, then it will show user1 database at the page..i dunno how to link the database tat created automatically(asp.net configuration) with my own database created.. izit using stored procedure? can i have some example or guide how to do tat from you all?
mayb this is a noob question..but can u all help me? thx...
MS sql 2005 database i link to access 2003 database one of the table showing all record "deleted" on the field
can you please help me
Hi every body.
We have instaled Sql Server 2000 Standard Sp4, whit a Nortel Product. That aplication make to gestion calls using MSQL2000; in some moment the DB stoping and the Reason is "[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Arithmetic overflow error for data type smallint, value = 33459."
We do not have any idea about this problem.
Thx
Eliseo Berdugo
Hi All,
Could you please tell me how to create link between the two databases which is in the same server?
Thanks in advance.
is it possible to link a table with odbc into sql server?
it would be nice to link an MS Access table into sql server where i could use stored procedures to access the MS Access table.
I have used to following Microsoft supplied code to create a link to
SQL server from access: (Is there a way to use a DSN-less connection at comment below? If not is there a way to create a DSN using code?
-------------------------------------------------------------------------
Sub ClientServerX3()
Dim dbsCurrent as Database
Dim tdRoy as TableDef
Set dbsCurrent = CurrentDb
Set tdfRoy = dbsCurrent.CreateTableDef("Roy")
tdfRoy.Connect = "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers"
***************IS THERE ANYWAY TO USE AN OLEDB (OR OTHER DSN-LESS)
***************CONNECTION HERE? How??
tdfRoy.SourceTableName = "Royalties"
dbsCurrent.TableDefs.Append tdfRoy
End Sub
Thanks, I really appreciate the help
Sam
I'm trying to find how to link to an Access table from within SQL Server. I know I have seen it once, but can not remember where I saw it. I'm using SQL Server 2005. TIA,
View 5 Replies View RelatedUsing an MS A2K front end running on WinXP and ODBC connections to SQLServer 7 on Win 2K server; there are about 10 users who access thefront end via Terminal Server and use the same front end; there areabout another 10 users who run a copy of the db on their local machine.SQL server has 2 databases, with approx. 20 tables between them (oneis for processing ckts from a remote sql server for local use, theother is a site database).There is one user that always has to refresh the same single table eachtime a release of the db is copied to his local machine. Once thetable is refreshed in link manager, the front end is good to go...untilthe the next release is made (about every 2 months--for changes inbusiness rules). We have gone and checked his WINS and DNS settingsand at this point, the only difference between other users and this onewho can't get the table normally, is that he is using a different brandmachine (he uses a toshiba satellite, all others use a ibm thinkpad).Since purchasing a new laptop for him is out of the question, doesanyone have a suggestion for correcting this problem?TIA for the help,EricO.
View 4 Replies View Relatedsqlplus for Oracle has a CREATE DATABASE LINK command. What is the equivalent functionality for MSSQL using sqlcmd or other script mechanism.
View 1 Replies View RelatedHello,
I am getting an error trying to select data from a Linked Server that is connected to a secured MS Access database. I have entered the login and password for an admin in the sytem but I am still getting the error below. It seems that all of the examples shown on the Online help are for unsecured databases. Can anyone help me with this issue?
Thanks
Will
SELECT * FROM TMS_SECURED...Assignments
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "TMS_SECURED" returned message "You do not have the necessary permissions to use the '\FileSrv-05ProductionTMSTMS-DATA.mdb' object. Have your system administrator or the person who created this object establish the appropriate permissions for you.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "TMS_SECURED".
How can make link to a table stored in an sql express 2005 database in access 2003 ? the classic method doesen't work. i tried to use odbc databases as file type to link, then build a data source using SQL native clint driver, but in the combo for default database i don't have the database created before, only master,msdb, model, tempdb. If i try to use the attach database filename text box i receive an error "the datadase entered is not valid". Any suggestions ?
Thx
Hi I need to know if it is possible to write on a table from another database and this using a stored procedure. If yes what is the correct syntax to connect to the other database.
View 1 Replies View Relatedi'm pretty familiar w/ mysql, but relatively new to ms sql. Since i can do a lot more w/ ms sql, I have been trying to do all tasks in sql queries or scripts, but i'm stumped on this one. I have a table that links two things together. t1 has rows id1 and id2. When a row has these two id's, there is a "link" between them. I'm trying to make a command or script that will see if there is only 2 links for any given id.
for example, it would loop through each row, and run this command:
SELECT COUNT(*) AS Expr1
FROM links
WHERE (Id1 = this_rows_id1) OR (Id2 = this_rows_id1)
and again w/ id2
and then i would be interested in any row that only had a count of 2. is this possible?
I am designing a database schema where several tables have one-to-many relationships to records in other tables. One way to implement it is to create a link table for each pair of tables that have a relationship:
identity
Table1_ID
Table2_ID
1
12
9
2
12
15
3
18
42With the SQL 2005 support for the XML data type, there is the possibility of storing the IDs in an XML column. The XML stored with a record (equivalent to record 12 from the above example) might look like this:
<Links>
<Table2_LinkType>
<ID>9</ID>
<ID>15</ID>
</Table2_LinkType>
<Table8_LinkType>
. . .
</Table8_LinkType>
</Links>The XML column method has the advantage of not requiring that a separate table be created but does not enforce referential integrity. The link table method has the advantage of allowing constraints to enforce referential integrity but has the disadvantage of requiring the creation of a separate table for each pair of tables having a relationship and joining to an additional table has performance implications. Implementing standard Add, List and Delete operations for the link table method is straightforward. As a test and to familiarize myself with the new XML features, I created Set, List and Delete stored procedures for the XML method. Both methods will work.
In deciding which method to go with are there any other issues I should be considering besides database integrity, complexity and possible performance issues?
From the standpoint of best practices and coding standards is one method preferred over the other?
Here is some additional information. The data set I will be working with has table sizes numbering in the hundreds of thousands. Any given record will only be linked to at most a half-dozen records in any other table.
I searched on-line for information and I was able to find plenty of good articles discussing how to use the new XML data type in SQL. However, I was not able to find any information on when not to use the XML data type over equivalent joined-table methods.
Dear all,
I face a problem about linking the SQL express database. Originally, I work with my desktop at home. However, I need to take it outside with my laptop. Once I copy whole project to my laptop and use Visual Studio 2008 again to develop. The following message appear when I run some pages that need to connect the database:
"An attempt to attach an auto-named database for file E:d drivedocAssignmentProgramming ProjectProgrammingSourceApp_DataASPNETDB.MDF failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share. "
How can I solve this problem? I think it is an absoultue address and logical address problem. Thanks a lot.
Regards,
Eric Chan
Hi All,
Can somebody please help me, how to create database link to Oracle from MsSqlServer200.
Ex:
If I want to view data in both databases at a time then query would be like the following:
dblink name : SqltoOraclelink
table Name : EMP (this could be available in both the databases)
query in MsSqlServer200 EnterpriseManger:
select empname, empno from EMP msemp, emp@SqltoOracle oraemp where msemp.empname = oraemp.empname
Then this query results the rows from both the databases where empname is same in both the tables
My question is: How can I create this Database link "SqltoOraclelink" as I can do the same thing in Oracle.
As this is in urgency, it would be greate if somebody help us asap.
Thanks in Anticipation,
Regards,
RK