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.
Hi;I am looking for suggestions about how to solve a problem using tsql.I have been asked to create a report concerning 4 tables.Each of the 4 tables is in its own database.The 4 tables are identical in name and structure.I would like to query all 4 of these tables and get the results as ifthey were one table.All suggestions welcome, thanks.Steve
View 2 Replies View RelatedHey there!
I'm not sure how to explain this but here goes...
I'm a bit stuck, a new project that's come about "requires" me to query multiple databases as if they were tables.
1 Oracle database which stores information on our staff (this database is a part of some MIS software and can't be changed in any way)
1 SQL Server 2005 database which stores information on staff sickness
Basically say theres 1 table in each database
Oracle DB
People
Person_Code
Forename
Surname
SQL DB
Sickness
Person_Code
Daydate
Lets say the query I want to perform is to select all the records from sickness where person code is 22334 and also get their name from the other db, so the output may look like
22334 Dann Rees 01/01/2007
22334 Dann Rees 03/01/2007
22334 Dann Rees 10/02/2007
Now I realise I can write a quick function to pull the information but this is just a basic example. Effectivley what I "need" is to be able to query sickness while sub querying people to get the names, or some kind of pass through query?
Please remember this is just a very simple example and the "actual" queries will be far more complicated, for instance finding all the employees of a certain department who is male and was sick in January. All the data for that example is stored in people (oracle) except for the dates which is stores in sick (SQL 2005). Now these are easy enough if they were tables in 1 database....but their not, their tables in 2 databases, and theres nothing I can do to change that :(
All help appreciated as this is becoming very urgent.
Many thanks
Dann
(I couldnt post this in the General data access forum for some reason)
Hello,
This is probably a simple solution but I'm a bit confused about which flows to use.
I have two databases on different servers that contain the same information but for the appropriate server. I want to retrieve data from each of the servers using a "select" statement.
Would I use a ForLoop and change the connection in a Script Task and then execute the SQL statement? Or can a ForEach Loop be used, but I'm not sure which type of enumerator.
Thanks for any help you can give.
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!
View 3 Replies View RelatedCan Anyone Help? I have 2 Databases, Database1 and Database2.
Database1 is a content management backend database including a helpdesk and support ticket tracking table.
Database2 is a clients complete website
How do i link the tables from Database1 into Database2 so that the client can view the helpdesk thru their content management system.
(Client1, Client2, Client3 etc... will all have access to the same Database1)
Any help someone could give me would be a big help
Hello,I am quite new to ms-sql and I have a problem : I want to create an SQLrequest which would copy serveral records from a table in a given databaseto another table (with exactly the same structure) in another database(theses two tables and databases already exist).Could you please tell me how to do this ? I dont know how to access twodifferent databases in a single SQL request.Thank you for you help.
View 3 Replies View RelatedHi, 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.
Thanks.
Hi all,
How do I query two tables in different databases on the same SQL Server?
In short, I want to do:
Select A.*
from database 1. table 1 as A
inner join database 2. table 1 as B
on A.COL1 = B.COL1
Both database 1 and 2 are on the same SQL Server.
Please advise.
Thanks,
V
Hi!
I have 7 source databases and one target database, all using the same structure. The structure is made of 10 tables, with foreign key constraints.
I need to merge the source databases into the target (which won't have any data before that process, but will already have the correct schema), and to keep the relationships between the records.
I know how to iterate over the source databases (with SMO foreach), but I'd like to know if someone can advise the best copy method for that context in SSIS ? (I don't want to keep the primary keys, but I need to keep the relationships...)
Any pointer most welcome!
best regards and thanks
Thibaut
Hi,
I need to merge tables from several databases. Before the merge the table contents must be checked to rule out any duplicates.
Is there a most efficient way to do it? Thanks to you all.
Hello,
I am in the progress of designing a new section of my database and was thinking of creating a hole new database instead of just creating tables inside the database. My question is can you JOIN multiple tables in an SQL Statement from multiple databases. Ie, In the Management program I have a database called 'Convention' and another one called 'Services', inside the two databases there are many tables. Can I link say tblRegister from Convention to tblUser in Services?
Thanks
I have a query that involves tables from 2 different databases. Using the query analyzer I can construct my query and it works great. Now I am trying to implement the query into my code and I am confused about I handle the connection string(s) since I am now using 2 different databases.
Can anybody help?
Cheers,
AzF
Hello All,
I have multiple SQL Server 2000 databases all identical in structure (Company1.mdf, Company2.mdf, Company3.mdf). I have a situation where while connected to Company1, i want to create a record in Table1 and have that same record automatically be created within Company2 and Company3.
If i was working with MSAccess, i could use linked tables where i would link Company2 and Company3 to Company1 Table1. This way if any changes are made, all three companies and working from the same record set.
My situation deals with mutiple tables that need to be in line with each other across mutliple databases.
Your suggestions are appreciated.
Thanks
I'm trying import 7 tables from each of 30 SQL2005 databases into a SQL2005 Consolidation database. I can simply create data flow tasks for each one but instead I would like loop through a list instead.
I've created a table to house the names of the databases from which I want to import the data.
I've created SQL task to return the database names from the table as a "Full Result Set".
I've assigned the result set to a user variable (type = Object) an named the result name 0
What I'd like to do is create a data flow task which connects to each of the databases and imports 7 specified tables from each database appending the table name with my database name in the result set.
I'm stuck on how I'd set the connection strings in my OLE DB Source in my Data Flow task. Any insight would be greatly appreciated.
Thanks in advance.
Bill Webster
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.
Jon
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?
Thanks!
Hello
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.
thanks in advance
Paul
Hi All,
I am very new to sql and I am not really sure of what I am doing.
I have a bit of a complicated stored procedure that I am trying out. There is 3 parts to it to search for new articles,updates and discontinued articles for a particular Supplier. Right now I am just trying to get the first part working - I am trying to query another database that has 4 tables, 2 are joined ActiveArticle & ActiveArticlePrice and the other 2 are also joined UpdateArticle & UpdateArticlePrice. But all tables also hold data for ALL Suppliers, they are recognised by a GUID - SupplierGuid.
I want to compare them to each other to see if there are any new articles added to the Update tables. This is what I have so far, I was hoping someone could tell me if they see some obvious mistakes(I'm sure theres many!). For the first select, I tried adding a couple of parameters Flag and Text to use so that I can order it all once i have the 3 parts working. But I don't know the syntax is right, because then I also want to select all fields in both tables?.
CREATE PROCEDURE Portal_GetPriceListUpdates
(
@SupplierGUID uniqueidentifier
)
AS
SELECT 'A' AS FLAG, 'New Product' AS TEXT, * FROM SupplierUnits.UpdateArticle UA, SupplierUnits.UpdateArticlePrice UAP WHERE EXISTS
(SELECT * FROM UA, UAP WHERE UA.SupplierGuid=@SupplierGUID AND UA.SupplierArticleNumber NOT IN (SELECT SupplierArticleNumber FROM SupplierUnits.ActiveArticle.SupplierArticleNumber))
GO
I would appreciate any help!
Thanks,
pp
Hi,I'm having two databases in the name of n1,n2 having same designI want to query both the databases by giving the database name in queryDatabasesN1N2Table(both database contains same table)DetailTable detailsNo -intName -varchar(10)Now i want to check the detail table in both n1 and n2 using the selectqueryFor that I have written query it is not workingselect * from N1.DetailSelect * from N2.DetailThe above query is not working in query analyser,I'm getting error as"Invalid object name"In query analyser the Northwind database is currently selected, anywayI have given the database name it has to query from N1 database. How touse a select query with database name?Mani
View 1 Replies View RelatedHi,I need to run a query that gets an ID from one database table and then matches it to another table in a different database, is a query of this type possible if so how?Thanks,drazic19
View 2 Replies View RelatedMy query pulls data from two separate databases, and it runs extremely slow. The queries run well when they are pulling from their own database. When I join them to pull from both databases.
View 9 Replies View RelatedI know that a heterogeneous query joining tables from two different servershas performance penalties but is the same true when joining tables from twodifferent databases on the same SQL Server 2000 instance?We are looking at setting up a Data Warehouse using DTS on a SQL Server boxand I'm wondering about the best way to logically set it up; i.e. one bighonking db or several dbs determined by some logical organization. With thelatter there will still be some queries that would need data from more thanone db and I'm wondering if that will have worse performance than if theywere all in one db.I thought that was the case in older versions of SS, but I couldn't findanything in 2000's BOL that indicated a problem with that.TIA
View 1 Replies View RelatedHi
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.
I would appreciate some help.
Thank you
Corné
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.
View 4 Replies View RelatedI am trying to query the Topics in my discussion forum...The Topic contains a "last_poster_id" and a "author_id" I need the username and userid for both "last_poster_id" and "author_id" in the table "aspnet_Users"How do I do this?I would guess I need to use sub select statements. Can someone help me?
View 12 Replies View RelatedHello,
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
Hi all,
lets say i have a table as follows
id name1 red2 orange3 green
and this has s join to another table via the id
table2
id event 1 stop2 stop3 start
now if i make a view and drop these two tables the jkoin is there but i would liek to report on all names who have a event of "go"but it gives me no results , which is right . but how can i output all the names and in the evnet put a empty SO
name eventred emptyorange emptygreen empty
thanksrobby
I'm trying to create an email report which gives a result of multiple results from multiple databases in a table format bt I'm trying to find out if there is a simple format I can use.Here is what I've done so far but I'm having troble getting into html and also with the database column:
EXEC msdb.dbo.sp_send_dbmail
@subject
= 'Job Summary',Â
@profile_name =
'SQL SMTP',
  Â
[code]....
Hi,
I'm trying to create a database that takes specific information from a number of databases on different servers to make some reporting that we have much easier.
I'm pretty new to SQL so I'm not sure of the best way to proceed. I read an article that suggested I use the OPENROWSET command. The problem is, the version of SQL that came with one of the programmes we use is limited and will not allow you to turn on the allow "Ad Hoc distributed Queries" so the SLQ statement will not execute.
I'm confused why it won't let me to connect through ODBC as I've created a web page that selects data from this database with no problems!
Here is the SQL statement that I've written to make sure it is the correct one (on the msdn library page it said that this was the ODBC connection):
SELECT a.*
FROM OPENROWSET('MSDASQL','DRIVER=(SQL Server);SERVER=APPOLOACT7;UID=sa;PWD=***************',
'SELECT * FROM MDCTestAndDev.dbo.TBL_CONTACT') AS a
I've also created the ODBC connection using the tool on Administration Tools>Data Sources ODBC
Any help would be greatly appreciated (also any ways of selecting from one database and inserting it into another will be helpfull)
Thanks
I am trying to restore multiple .bak backup SQL database files onto a new server. However, I have found that it will not allow me to restore multiple databases at once. Is there a way to do this so that I do not have to manually upload one at a time? I tried adding all the .bak files at once to the backup device window but it only did the first one listed. It would be so much easier to restore them all at once so that I do not have to continue this manual process. I am restoring them via device.
View 13 Replies View RelatedI am trying to query multiple tables that have the same column. They are a supposed to be a unique ID to the product, but not to the database. I have a primary key already set to each othe tables. The tables do not share anything between them, but i would like to search for these IDs in other tables before my insert command is executed. I am writing my code in VB. example of what i am trying to accomplish.
Table1 = TVs
ID/SKU_ID/Manufacturer
1 87654 Samsung
Table2 = DVD_Players
ID/SKU_ID/Manufacturer
2 765234 Kenwood
I want to search for SKU_ID in multiple tables( I think a total of 9 tables). I want to search between tables to catch for the same SKU_IDs. and if it finds it, raise error. I will be able to figure the vb.net portion. I just dont know how to go about the sql query portion. Thanks in advance and appreciate any help. Thanks!
I have transaction table where the rows entered into the transactioncan come a result of changes that take place if four different tables.So the situation is as follows:Transaction Table-TranId-Calc AmountTable 1 (the amount is inserted into the transaction table)- Tb1Id- Tb1AmtTable 2 (an amount is calculated based on the percentage and insertedinto the transaction table)-Tbl2Id-Tb2PercentageTable 3 (the amount is inserted into the transaction table)-Tbl3Id-Tbl3AmutTable 4 (an amount is calculated based on the percentage and insertedinto the transaction table. )-Tbl2Id-Tb2PercentageHow do I create referential integrity between the Transaction table andthe rest of the tables. When I make changes to the values in Table 1 -4, I need to be able to reflect this in the Transaction table.Thanks.
View 6 Replies View Related