Hi, I have 2 similar databases on the same SQL server (due to licencing and customisation issues for off the shelf software).
I would like to create a third database with site specific lookup tables to map data across to site codes, to do summary queries for data reporting, and to create a single point for data access. I can't modify the 2 databases.
I have created the third database and added the necessary tables, relationships, views, etc. But, how do I link to tables in the other databases on the same server so that I can do joins in a query/view ? I have SQL Server Enterprise manager.
Dear Readers,Is it possible, like in Access, to link to tables in other SQL databases that are on the same server? I have a query that I originally had in Access that queered from multiply databases. It did this by having those other tables in the other databases linked to the database that had the query.
In a database, I am creating a new db. From there, I am setting up thetables, so that I can eventually create a front end (usually access,but I may attempt to be brave and lose the shell.) Anyway, I want touse a table, read-only for a lookup. It exists in another database onour system. Is there a way for me to link it into this database that Iam working on? I would think of it like in access when you go to do anew table and you choose to link the table from somewhere else. Thatis what I want to do. Can anyone possibly step me through this?I know basics of sql server and like to learn.Thanks!
I have two questions : 1) Linking SQL Server Databases 2) Data Formatting like InputMask
1) I want to know if there is a way to link two seperate SQL server databases (it's tables) within the same server (local server). I created one Global database - with clients, etc. tables. The reason I did it like this is that I have to handle three seperate invoices databases, but using the same clients, etc. tables to get their information from. I used MS Access like this, linking Clients, etc tables from the Clients Database with the three other Databases. It worked fine but now I need some new features like setting the InvoiceNumber to something different, eg . 04/0001 for this year and then 05/0001 for next year. My Access databases can't change this numbers - it's just a autonumber I used. I know some Visual Basic code can make a change like perhaps adding a new column like InvYear and then adding it together with InvoiceNumber to get the Invoice Number, but I want to change it to SQL Server because I want to learn how SQL Server works. I am just starting out and not doing courses.
2) The other thing is I want to know if there is some feature in SQL Server to make data input easier, like a InputMask in Access where you can let a text column be formatted as you want. Eg. for a telephone number, it can store it in the format you made; like (011) 123 4567, not like you typed it : 0111234567.
First off, I am a novice in regards to SQL server and have the rudimentary skills to navigate, execute, query under SQL Server Manager. So, please bear with me.
I am working on a program that was not developed by myself, it is for my business and the company that developed it has gone under, so I have no technical support. The program uses SQL Server and ASP.net. We had a recent hard drive crash and I am in the predicament of having to re-install a new instance of the server onto a new computer. I have the most recent saved "data" files within "Microsoft SQL server/MSSQL/Data" directory. It includes the master, model, MSDB, etc. files.
Long story short, I have been successful in making the program operational again and I can connect to the server properly. It even works when web connecting with internet explorer. The problem I am facing is that all of the data is missing. When I connect through SQL Server Manager, I can see that there are two databases, I will name them "data1" and "data2". When I run a query of the tables, all the data is in "data2". All the stored procedures and tables are identical in both "data1" and "data2".
I am assuming (hopefully I'm right) that "data1" and "data2" are somehow linked or joined in some way. Any insight in how to get these two tables to link would be very much appreciated. Again, I am a novice, and will need help with specific coding that I can put into a query window and execute.
I'm new to SQL Server 2000. I decided to purchase this server because my old company (UPS) used the server to help expedite queries and manage on-line reporting. So, now it's up to me to utilize and understand the new server. I have been using MS Access as my front-end for several ad-hoc databases, so I'd like to keep utilizing this method. I want to test my first database using the SQL Server.
I've got everything set up. The IT department has installed the server software on my computer. Within my Access database, I link in tables from a database called "Progress" using a Merant driver. Queries, forms, macros and modules are all pulling from these "linked" tables. Do I need to link these tables into the SQL Server? How do I transistion this database over to SQL? I guess I'm confused about the relationship of how all of this should work.
Right now it looks like this:
PROGRESS ..... linked....... MS Access ....... reporting/results
I am trying to add a linked table to my server, it is an access table.
Here is the code i am using but i get an error: EXEC sp_addlinkedserver @server = 'AITdb_be2', @provider = 'Microsoft.Jet.OLEDB.4.0', @srvproduct = 'Access', @datasrc = '\kcapp03deptsCommonEIQAIT DBTestAITdb_be.mdb' GO
-- Set up login mapping using current user's security context EXEC sp_addlinkedsrvlogin @rmtsrvname = 'AITdb_be2', @useself = 'false', @rmtuser = 'Admin', @rmtpassword = '' GO
-- List the tables on the linked server EXEC sp_tables_ex 'AITdb_be2' GO
ERROR: OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "AITdb_be2" returned message "The Microsoft Jet database engine cannot open the file '\kcapp03deptsCommonEIQAIT DBTestAITdb_be.mdb'. It is already opened exclusively by another user, or you need permission to view its data.". Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41 Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "AITdb_be2".
I added a linked server successfully; but the only tables accessible areonly the system tables!I do have all the rights on both servers (Windows & SQL server).All the non system tables are designated as DBO.Strange thing is that I was able to access the non system tables onlyfrom the query analyzer (not from the SQL Manager tree) by using thefollowing script:EXEC sp_addlinkedserver 'LINKED_SERVER_NAME', N'SQL Server'GOSet ANSI_NULLS onSet ANSI_WARNINGS onGOSelect * From LINKED_SERVER_NAME.Database_name.DBO.table_nameGOThank you very muchMike*** Sent via Developersdex http://www.developersdex.com ***
Looks like the Office 2003 MS Access has no support for creating a linked server and implement design changes. While you could link to the SQL 2005 tables, which show up as queries in MS Access 2003, any further use of the linked queries are not supported unless the Access 2003 version came out after the SQL 2005 release. What kind of an update is needed, or fix is needed so that Access projects can be developed?
I am not a database manager, but I work with people that use databases for sales and customer service... so I am not too familiar with the code and lingo... please bear with me.
We use two databases - one is in SQL, and it is for pre-sales customer service... i.e. tracking notes, phone calls, emails, and opportunities. The second database is in Progress, and it is for actual quotes, tracking time, labor, materials, cost, and revenue. As of now, our sales people have to enter new customers in the SQL database, and then duplicate the information in the Progress database. It's very frustrating, because everything needs to be entered twice - name, address, phone, shipping info, etc. etc. etc... When we first started this - 10 years ago - we were told that it could all be linked.
So my question is - is it possible to link these databases together? When our sales people enter a new contact in CRM, I'd like it to be automatically entered into the Progress database. If they make a change to a phone number, I'd like that to be updated in the Progress database. Also the other way around - if a shipping address is changed in the Progress database, I'd like it updated automatically in the SQL database.
Please tell me this is possible - and easy!!! Thanks for any help you can provide at all.
We are considering to implement Microsoft CRM 3.0 in our (small) organization. But we already have some customer information in another system (Unit4). We want to duplicate this information to our new CRM-system. This data will be read-only in the CRM-application.
Both the applications are installed on different servers with their own SQL databases(server 2000 and server 2005)
How can we duplicate the customer information from the unit4-system to Microsoft CRM? Can we simply link those databases using ?ODBC? (I heard something about ODBC but don’t know what it really is.)
Could someone clearly explain me what we have to do to make a construction as described above. (you don't have to give me all details) Is this construction possible anyway?
I have an app that creates and attaches tables from SQL Server into an Access mdb using an ODBC link from VB6 using DAO 3.51 on Windows 2000. Recently I upgraded to SQL Server Express edition and tested it. It worked fine against the Express Edition. So, I installed SQL Server 2005 Standard Edition on our Server hoping that they operate identically. How wrong one can be! the same code that worked against Express Edition does not work on Standard Edition.
It is strange that the App works for about 5 seconds which attaches about 38 tables and after that the connection drops and I have a hell of a time to get the rest of the tables linked. The error that comes up is:
Connection failed: SQLState: '01000' SQL Server Error: 10060 [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (connect()). Connection failed: SQLState: '08001' SQL Server Error: 11 [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]General network error. Check your network documentation.
The PC that I am using to connect is in a different domain to the Server where the SQL Server database resides. I thought that domains might be the reason. But even joining the PC to the same domain as the server still does not work and behaves the same. So I do not think it is a domain related issue.
I have also looked at the SQL Server and database configurtaions. I cannot see anywhere any specific time out of around 5 seconds. Even the time out through the SQL Server ODBC driver is set to 30 seconds by default as far as I know. So, it looks like there is a time out somewhere but I am absolutely confused as to where this 5 second time out is comming from.
I also thought permissions on the database or tables might be the issue but after looking at the database and table permissions, they all look OK. The user has prettymuch the same access as the SA on the database.
Has anybody came across this problem? If so, I appreciate if someboday can give me a hint as to where I can start.
I have an MS-Access database on a shared drive.The Access database has tables linked to a SQL server database.When I try to query the tables in MS-Access database by accessing the share drive,I get the ODBC--call failed error.So I tried creating an ODBC driver for the SQL Server and when I try to link the tables,no tables are displayed.Can anyone help me out with this problem?
We're trying to figure out whether it's possible to link AD objects to SQL tables. For example, if we have a table of Users and a table of Companies, we'd like to enter the users into Active Directory, create the Companies as either OU's or Security Groups, then have those entities magically appear in the corresponding SQL Server tables. Is that possible? Thanks in advance.
Does anyone know the best way to get around this problem.
Basically, I have three servers containing the same database structure. The data is different, each pertaining to the location in question.
Speaking in laymans terms, I would like to throw all the data into a big box from the three servers so that I can get the information I need from ONE source.
A problem I foresee is primary keys. For example, let's take the patient table(I work in health) the patient_key field in table patient although unique within it's own database, is not unique across the other databases from the other sites I want to "throw into the box". I can't just add another primary key of my own because it would not appear in the other tables that patient_key does. Obviously patient_key is one of many such primary keys that share this problem.
Does anyone have any suggestions as to how to go about this problem. Is there a way to "join" these databases together, like a union command does to more than one query.
My organisation uses MS SQL Server 2000, we have no analysis or reporting services add ons.
My SQL server knowledge is ok when it comes to querying and simple stuff, but the more complex administration is all new to me. So treat me like a rookie please.
select a.RecordID,a.RelocateID,a.WhenConfirmed,b.RequestI D,b.DocumentID,b.RequestWhen from RelocateeDTA a inner join docRequests b on ({a.RecordID}=' + CONVERT(VARCHAR, a.RecordID)'= b.WhereClause)
because I am trying to link RelocateeDTA that has RecordID to docRequests that has the same piece of data in field WhereClause but is a varchar, and I am getting an error message (syntax error or access violation)
dear experts, i have two tables, tblcustomer and tblform. in tblcustomer i have cutomerid PK and i have also a field that link these two tables customername. in tblforms i have formname. customername and formname are not PK and customername has a lot of null values. my question is that i want to link these two tables in customerid not the name and also to get the forms used by this customer. the thing is that i have to write a join query. any suggestion to do that?
I know it's possible to link tables between Access and SQL Server using ODBC....however can u use DAO to do this? If there's a way i would appreciate some help. Thanks in advance.
URL...let's say every student in the left table, has a corresponding table to the right. If we were to take the average of all the classes in the corresponding table, how would you be able to transfer that in the table to the left, assuming there could be thousands of these individual tables.
I am no stranger to Databases, I worked a lot with MySQL but never really cared about proper DB design as long as it worked. Now I am playing with SQL in a ASP.NET project and want to get things done the right way.Let's say I have a Movies database. My movies can have multiple genres so I set my tables up like this:
[Movies] MovieID MovieName MovieRelease
[code]....
Is this the proper way of doing things? The problem with this is when I want to enter a record manually I have to know the ID of the movie and the ID of the Genres of the movie. And what about naming conventions? By default the identifier is always Id, from my MySQL experience I liked naming it like the table, same goes with other columns. This is my T-SQL code for above tables in VS-2013.
CREATE TABLE [dbo].[Movies] ( [MovieID] INT IDENTITY (1, 1) NOT NULL, [MovieName] VARCHAR (50) NOT NULL, [MovieRelease] NUMERIC (18) NOT NULL, CONSTRAINT [PK_Movies] PRIMARY KEY CLUSTERED ([MovieID] ASC)
Hi and thanks for taking the time to read this. I get really confused really quickly when it comes to table linking, and I think I am doing it the hard way. I am going to show my stupidity and ask if there is a better way than what I do, because it takes a LOT of querying and seems like a huge waste.
In reality, I have about 20 tables to join like this, but I'm going to use three for the example:
SELECT Main.AcctNo, Main.Name, Height.HeightName, Weight.WeightName FROM Main INNER JOIN Height ON Height.HeightID = Main.Height INNER JOIN Weight ON Weight.WeightID = Main.Weight WHERE Main.AcctNo < 3
My question: is doing 20 INNER JOINS really the best way to do this with 20 tables, or is there some other way to link Main.Height to Height.HeightID that is less intensive?
Thanks again for reading, and in advance for any information or insight you can provide.
I have two tables, one for cases and one for inventors. When I query a case linked to the inventor table I get as many rows as there are inventors. Is there a way that I can get just one row for the case and have the inventors lists Doe, John; Doe, Jane; Buck, Jack? I usually export results to Excel and my people want the inventors in one column for certain reports. I would search for it but I don't know what it's called. Thanks, Don
I have two tables, TableA and TableB.Each has a date in smalldatetime format.TableA has a qty field in int format.TableB also has a nvarchar field in which the date is in the formatYYYYMMDDA simple Query on TableA sum Qty returns a result of 100.However, When I do a left outer join from TableA to TableB on Date, thesum of Qty on TableA becomes massivley larger.What could be causing this?Regards,Ciarán
Hi,If you have lookup tables which are used by multiple tables (e.g.'City' lookup table might be used by the 'Employee' and the 'Company'tables) do we need to link it to both tables? Or can it sit by itselfand just referenced?Cheers,Jack
I have an Access database that contains local files and files linked on a SQL server through a DNS connection. I have been using this for years and during that time I have dropped links, added links, changed the DNS server information, etc.
I changed the DNS server information this weekend, deleted all the links, and was ready to start linking the tables with the new DNS information. I click on File >> Get External Data >> Link Tables and am given the traditional file selection dialogue. I go to the bottom (Files of Type) and scroll to the last selection... ODBC Databases().
In the past, after I made that selection, I would then be taken to the ODBC dialogue from my computer to select the connection type and then the actual connection.
Now however, the dialogue immediately stops. It doesn't lock, it just stops and returns me to the Database window from my database as though I wasn't doing anything.
I have reinstalled Office and redone all the updates. Does anyone have any suggestions?
I am trying to create a single query from 2 tables on different SQL servers. In Access 2003 I have been able to accomplish this by created a Linked Table via ODBC connection to a table then create my query reference the linked table.
Is something link this possible with SQL? Any direction would be greatly appriciated!
Scenerio:
Server1
Books
Book_ID Book_Author Book_Title Server2
PurchasedBooks
Customer_ID Book_ID vPurchasedBooks (View on Server 2)
Customer_ID Book_Title (Joined on Book_ID on Server1Books)
Hello, I have a table called "ShoppingCart" which has fields for CartID, UniqueID and Quantity. At the moment I haven't specifed a primary key for this table becuase none of the fields are unique. I need to be able to link this ShoppingCart table to a table called "Size" using the UniqueID (both tables contain a field for UniqueID) in order to obtain the ProductID and CategoryID of the data item. I then need to link the ProductID and CategoryID I obtained from the first part of the SQL statement to a table called "plants" in order to get information from the following fields in the plants table: Common_Name, Latin_Name and Thumb_URL. So far I've been able to use an INNER JOIN with the Size table to obtain the ProductID and Category ID using.... SELECT * FROM ShoppingCart INNER JOIN Size ON ShoppingCart.UniqueID = Size.UniqueID WHERE CartID = @CartID ORDER BY ProductID I now need to be able to use the ProductID and Category ID I've obtained from this part of the query to reference the Common Name, Latin Name etc of the data item from the plants table. Is there any way that I could do this? I do sometimes wonder if I'm making this whole thing a lot more complicated than it needs to be. At the moment I'm storing details about the different types of plants the garden centre sells in the "plants" table. However, some plants are available in different sizes so I've got a table called "Size" which links to the plants table using the ProductID and CategoryID of data items. The Size table has a unique primary key field called "UniqueID" which is used to uniquely identify every plant that the garden centre sells (this cannot be done using Product/CategoryID becuase different sizes of the same plant have the same ProductID/CategoryID). I'm then storing just the CartID, UniqueID and Quantity in the "ShoppingCart" table. There must be an easier way of structuring the whole thing! I'd really appreciate any help you can offer me. Many thanks, Luke
I have a province table in a my database. I would like to link this province table to a resource table's Province_ID. This Province_ID is an int. Vic Valentic CEO/President Open Door 2 Elite Dr. #33 Hamilton, Ontario L8W 2N3 905-389-7492 http://www.wlu.ca/next/opendoor
