Thanks in advance for your help,
Is there an easy way to "link" data from one SQL database to another, preferably within a view?
More specifically, can one "link" to data from 2 seperate databases to a 3rd without importing the data?
Thanks,
Lee
I'm in the middle of developing a Database for a hospital that measures its audits, inhouse operations, and finance. What we currently have and do everyday is collect data from a large database that is real time with patient data, progess, infomation, etc and dump it into a data warehouse that runs on TSI/Eclipsys. We run reports using a number of programs and dump it into Excel sheets that have charts, reports, etc. This Database for which I'm developing won't come soley from the TSI/Eclypsys source, but this is the only source thats updated regularly. I don't want to have in sync with TSI/Eclipysys in fear that every day when it updates data may be lost, not read, or worse won't be up date if there is a problem. My question is is it possible to run a query from Sequel Server 2005 that will take that data upon request using the reporing features on Sequel Server 2005. i.e. What if I need to run a report on measure B in department 12 from Jan 1-Feb 1, instead of being in sync, can I just write queries to take that information rather than double the data and take up twice the space and trouble. FYI, these datatypes rarely change in the TSI/Eclipsys data warehouse. This sure was long question and didn't intended it to be . Thanks for listening and hope to hear back.
Stephen
Hi All,
We currently got two databases in the application(Visual web express with vb script). The one database is a sql 2000 that sits on a server and is fed by people that does E Learning. The app. got its own database(SQL 2005 express) with the record of the people doing the learning and some other attendances from other courses. I would like to determine attendances from both databases etc. How do I link this to table form diffrent databases into one table for me to do training cost etc.
Thanks for your help.
hi, I currently uses SQL Server 2005.I need to move the data to my website, how do I do that? I have the SQL Server Management Studio Express but I couldn't not get it to export the data in .sql file (which is needed to import to my website's SQL database).Please let me know of any tools I need to perform the task. thanks.
View 3 Replies View RelatedHello-
I have a chart I am producing in .net and I need to values. The problem is the values (sums) i need are on 2 different servers. Is there any where to combine the query using two databases? Does anyone have any suggestions?
thanks
Hi,
I develop ASP.NET applications and use MS SQL 2000 Enterprise.
Each customer has one database and one custom design application. And, there is a gateway and a central database to get data from all databases.
Supposed there are 10 DBs. I want the data in those 10 databases updating to the central database directly.
So, the gateway can gather all data of 10 DBs from a central DB.
Now, my question is how should I do it in MS SQL 2000? Can I do it with a stored procedure (SP)? If so, how does the SP like?
Or, SP can only perform on it's own DB, but not execute across DBs? If so, how can I do that?
Thanks for advice
I have a web app that has been regulated to a disconnected PC. It's runing IIS and 2.0 with sql server express, but no connectivity. I have changes that are made to some of the data in the db (data, bot schema). There is one particular table that I cannot overwrite, and must extract the data.
What methods are available to do this swap of data between databases? I was thinking of doing something like this:
Track last date that remote db was updated. Upload updated database into data directory, loop through records for all affected tables, any date that was past logged date then update the record if it exists or insert new record, and then loop through the remote db and delete this records that dont exist in the updated db.
This seems intensive and slow - especially as the tables get bigger but I can't think of another solution that can be done by a user using sometime of web interface.
Dear all,
I faced a problem. I found all data/records of tables in a few databases were missing yesterday. I don't know what had happened. Can anyone tell me why? Can anyone tell me how to trace the root of the problem. Any log files I can trace? I fear it will happen again. My server is sql2000 and run on win server 2000.
Regards,
Simon
Hi, I would like to know if it is possible to compare data in two different databases (both SQL7.0),
with same exact schema. If possible, how could we do it?
Thanks
- Sharma
Hi
Is there a way of comparing the data contained in two separate databases (on the same server)?
I have two databases: DevDB and DB and all I want to verify is that the data in both are *exactly* the same.
Is there a nifty function to do this in SQL Server?
Thanks
Sara
If I have to copy data from one database to a temporary database on the same server, which transaction log will be written to as a result? Will it be the DB that the command is run from, the source DB, or possibly tempdb's log?
Thanks
Derek
I am trying to copy information from one Database to another. Each of the databases reside on a different server.
My question is
1. How can I use the DSN name in a stored procedure?
2. Do I need to use dynamic SQL?
3. Any other ideas of how to implement this?
Thanks
i have two databases one has
firstname, lastname
and the othere
firstname, lastname, emailaddress
how do i get to merge the data from emailaddress to the 1st datbase?
Melvin Felicien
IT Manager
DCG Properties Limited
Hi All,
I wish to copy between 2 tables in different databases.
( The source and destiantion tables already exist)
The source table has duplicate entries which i want to avoid from being copied to destination table.
How can i achieve this?
Thanks in advance..!!
Vishu
Hi I'm trying to select data from different databases, Actually databases have the same structure and tables but different data, so I want to mix data from specific tables of all databases.
Any help would be great
I have two databases DB1 and DB2
With Tables T11,T12 and T21, T22 respectively.
All the tables in the two databases have the same structure.
I need help on a script to clear the contents of Table T21 and T22 in the DB2 database
And load the contents of Table T11 and T12 ( Table have about 2 Million records of data ) into T21 and T22.
hi,
1. I am new to SSRS. I want to show the data from two different databases in a single report. That databases may be in different machines also. Can you please suggest me how to do.
2. How to use the configurable connection string in reports. bcoz i'll have reporting service in one machine and database in another machine. At the time i want to read from config file. If database changing mean i need to reconfigure connection string. how to do.
Thanks.
Dear all
I have tow server on the same intranet. One server has a sql server 2000 database and the other one has SQL server 2005 databse.
The sql 2000 database has a table called employee. When ever a new employee is inserted in the database i would like the same values to be sent to the sql 2005 database. But this cant be done on the application level. It has to be done in the database. The application level can not be changed.
I was thinking a trigger but how to achieve the writing from one database to another. If they were on the same server then it would be easier but because they are on different servers i dont know how to do it.
Has anyone had similar issue before?
Any help is apreciated.
Sincerely
Dan
I want to import data from a live site database into a development database (SQL Server 2005 Express) using the DTSWizard. Once I copy/paste the live database to my dev machine, I cant attach the live site database because it has the same name as the database on the dev site.A simple solution I would assume is to change one of the names. But I can't seem to change the "orignal file name". A backup/restore won't work for me because I made table/field changes to the dev database. Thanks --Dietrich
View 2 Replies View RelatedWhat is the best way to transfer data between 2 databases.
Any ideas will be appreciated.
Regards
Karen
Hi
I need to retrive data from multiple tables which are in two databases. How do i connect to both of them at a time and query the tables with some criteria.
Thank you
Hi, can anyone point me in the right direction of how to sync an in-house SQL Server 7 with a remote SQL server 7.
We are a development house. We build website using SQL server 7 databases then move them up to an entirely different SQL server 7 database in a remote location.
Now, please don't laugh, currently we export all the data from the development server into a csv and import it back into the live server. As you can imagine, it's clunky, time consuming and unreliable.
Is there a way to automatically sync the two? I tried desperately to find any information but even the MicroSoft MCSE training manual on SQL doesn't say how to do this.
Any help would be greatly appreciated.
Thanks in advance.
Chris
How can I create a database link so that I can check data between 2 similar
SQL Server 7 databases ?
Hi there,
I was just wondering is it possible to select data from two different databases on two different servers?
ie Select * from
Server1.databasename..table, Server2.databasename..table
If anyone has any suggestions they would be appreciated,
Thanks,
Fin
Ok say we've got two databases with two tables and we need to
transfer data from one to another. However, the data structure in
sourceDB is different to targetDB. Better to illustrate:
sourceDB
tablesource
PIN SYSTEM HEADER PROPERTY VALUE STATUS
1000 AF ADDRESS DETAILS LINE 1 The Grange Active
1000 AF ADDRESS DETAILS LINE 2 69 Tintagel Way
1000 AF ADDRESS DETAILS LINE 3 Woking
1001 AF ADDRESS DETAILS LINE 1 50 Active
8888 AF ADDRESS DETAILS LINE 2 Evans Way
8888 AF ADDRESS DETAILS LINE 3 Islington
8888 AF ADDRESS DETAILS
8888 AF ADDRESS DETAILS
8888 AF ADDRESS DETAILS
Now each matching PIN on the multiple rows in sourceDB above relates to
one customer. In order to extract the relevant info for an address for
instance for each customer, we need to know the unique PIN, header,
property and value fields.
So I created this piece of SQL which gives me the result in targetDB:
INSERT into targetDB.dbo.tabletarget (address1)
SELECT
value
FROM sourceDB.dbo.tablesource
WHERE pin = 1000
AND header ='address details'
AND property = 'line 1'
targetDB
tabletarget
PIN ADDRESS1 ADDRESS2 ADDRESS3 SOURCE
The Grange
Fine, I've proved I can extract a specific field, but I need to do
this for all fields and all customers.
Any ideas?
Edit: Some changes added
This is my procedure for "rescuing" data from a corrupted database. Obviously restoring from backup is a lot easier!
0) Set the damaged database to Read-Only. if you don't have a backup make one now.
1) Script the database
2a) Create a new, TEMP database - preferably on a different machine in case of hardware problems on the original machine
2b) Size the Data for the TEMP database same size as the original (to avoid dynamic extensions). Size the Log something large-ish!
3) Run the Script on the TEMP database. Do NOT create any FK etc. yet
4a) Attempt to transfer all tables:
-- Prepare script of: INSERT INTO ... SELECT * FROM ...
SET NOCOUNT ON
SELECT 'PRINT ''' + name + '''' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)
+ CASE WHEN C.id IS NULL
THEN ''
ELSE 'SET IDENTITY_INSERT dbo.[' + name + '] ON' + CHAR(13) + CHAR(10)
END
+ 'INSERT INTO MyTempDatabase.dbo.[' + name + ']' + CHAR(13) + CHAR(10)
+ 'SELECT * FROM dbo.[' + name + ']' + CHAR(13) + CHAR(10)
+ CASE WHEN C.id IS NULL
THEN ''
ELSE 'SET IDENTITY_INSERT dbo.[' + name + '] OFF' + CHAR(13) + CHAR(10)
END
+ 'GO'
FROMdbo.sysobjects AS O
LEFT OUTER JOIN
(
SELECT DISTINCT C.id
FROMdbo.syscolumns AS C
WHEREC.colstat = 1-- Identity column
) AS C
ON C.id = O.id
WHERE type = 'U'
AND name NOT IN ('dtproperties')
ORDER BY name
SET NOCOUNT OFF
this generates statements like this:
PRINT 'MyTable'
GO
SET IDENTITY_INSERT dbo.[MyTable] ON
INSERT INTO RESTORE_XFER_TEMP.dbo.[MyTable]
SELECT * FROM dbo.[MyTable]
SET IDENTITY_INSERT dbo.[MyTable] OFF
GO
4b) This will give some sort of error on the tables which cannot be copied, and they will need to be rescued by some other means.
5a) Each "broken" table needs to be rescued using an index. Ideally you will have a clustered index on the PK and that will be undamaged, so you can "rescue" all the PKs into a temp table:
-- Copy PK fields to a temporary table
-- DROP TABLE MyRestoreDatabase.dbo.TEMP_RESCUE_PK
-- TRUNCATE TABLE MyRestoreDatabase.dbo.MyBrokenTable
SELECT[ID]=IDENTITY(int, 1, 1),
[IsCopied]=CONVERT(tinyint, 0),
MyPK
INTOMyRestoreDatabase.dbo.TEMP_RESCUE_PK
FROMMyBrokenDatabase.dbo.MyBrokenTable
ORDER BY MyPK
5b) If that is successful you have a list of all the PKs, so can can try to copy data matching those PKs, in batches:
-- If OK then selectively copy data across
-- First Prep. a temp Batch table
-- DROP TABLE MyRestoreDatabase.dbo.TEMP_RESCUE_BATCH
SELECT TOP 1 [ID]=CONVERT(int, NULL), [IsCopied]=CONVERT(bit, 0), MyPK
INTOMyRestoreDatabase.dbo.TEMP_RESCUE_BATCH
FROMMyRestoreDatabase.dbo.TEMP_RESCUE_PK
GO
--
DECLARE@intStartint,
@intStopint,
@intBatchSizeint
-- NOTE: After the first run set these to any "gaps" in the table that you want to fill
SELECT
@intStart = 1,
@intBatchSize = 10000,
@intStop = (SELECT MAX([ID]) FROM MyRestoreDatabase.dbo.TEMP_RESCUE_PK)
SELECT@intStart = MIN([ID])
FROMMyRestoreDatabase.dbo.TEMP_RESCUE_PK
WHERE IsCopied = 0
AND [ID] >= @intStart
WHILE@intStart < @intStop
BEGIN
SET ROWCOUNT @intBatchSize
-- Isolate batch of Keys into separate table
TRUNCATE TABLE MyRestoreDatabase.dbo.TEMP_RESCUE_BATCH
INSERT INTO MyRestoreDatabase.dbo.TEMP_RESCUE_BATCH
SELECTT.*
FROMMyRestoreDatabase.dbo.TEMP_RESCUE_PK AS T
WHERE IsCopied = 0
AND [ID] >= @intStart
AND [ID] < @intStart + @intBatchSize
-- Attempt to copy matching records, for this batch
PRINT CONVERT(varchar(20), @intStart)
INSERT INTO MyRestoreDatabase.dbo.MyBrokenTable
SELECTS.*
FROMMyRestoreDatabase.dbo.TEMP_RESCUE_BATCH AS T
LEFT OUTER JOIN MyRestoreDatabase.dbo.MyBrokenTable AS D
ON D.MyPK = T.MyPK
-- This will try to get the data from the broken table, it may fail!
JOIN MyBrokenDatabase.dbo.MyBrokenTable AS S
ON S.MyPK = T.MyPK
WHERED.MyPK IS NULL-- Belt and braces so as not to copy existing rows
-- Flag the rows just "Copied"
UPDATEU
SETIsCopied = 1
FROMMyRestoreDatabase.dbo.TEMP_RESCUE_PK AS U
WHEREIsCopied = 0
AND [ID] >= @intStart
AND [ID] < @intStart + @intBatchSize
-- Loop round, until done
SELECT@intStart = @intStart + @intBatchSize
END
GO
SET ROWCOUNT 0-- Turn OFF!!
GO
5c) This will copy in batches of 10,000 [you can adjust @intbatchSize depending on table size] until it gets to a damaged part of the table, then it will abort.
Change the @intStart to the last ID number displayed, and reduce @intBatchSize (by an order of magnitude each time) until you have rescued as many records as possible in the first "part" of the table.
5d) Reset the batch size @intBatchSize to 10,000 [or whatever size is appropriate], and increase the @intStart repeatedly until you are past the damaged section - copying will start again, and will abort if there are further damaged sections
5e) Repeat that process until you have rescued as much of the data as is possible
6) Check what is left to be rescued
-- Check amount NOT done:
SELECTCOUNT(*), MIN([ID]), MAX([ID])
FROMMyRestoreDatabase.dbo.TEMP_RESCUE_PK
WHERE IsCopied = 0
--AND [ID] > 123456-- Optionally count items after a "gap"
--
-- Double check that IsCopied set correctly, and the number of records "lost"
SELECTCOUNT(*),
[IsCopied] = SUM(CONVERT(int, IsCopied)),
[IsCopied+Record] = SUM(CASE WHEN IsCopied = 1 AND C.MyPK IS NOT NULL THEN 1 ELSE 0 END),
[IsCopiedNoRecord] = SUM(CASE WHEN IsCopied = 1 AND C.MyPK IS NULL THEN 1 ELSE 0 END),
[IsNOTCopied] = SUM(CASE WHEN IsCopied = 0THEN 1 ELSE 0 END),
[IsNOTCopied+Record] = SUM(CASE WHEN IsCopied = 0 AND C.MyPK IS NOT NULL THEN 1 ELSE 0 END),
[IsNOTCopiedNoRecord] = SUM(CASE WHEN IsCopied = 0 AND C.MyPK IS NULL THEN 1 ELSE 0 END)
FROMMyRestoreDatabase.dbo.TEMP_RESCUE_PK AS T
LEFT OUTER JOIN MyRestoreDatabase.dbo.MyBrokenTable AS C
ON C.MyPK = T.MyPK
--
-- List of "Lost" records
SELECTMyPK
FROMMyRestoreDatabase.dbo.TEMP_RESCUE_PK
WHERE IsCopied = 0
ORDER BY [ID]
You will then have to "find" and rescue the lost records somewhere.
I have a further process using OPENQUERY() to rescue records to fill the gaps in the event that they are available on a remote system - a straight JOIN to get them is going to be far to slow on anything other than tiny tables!
7a) Create the FKs etc. Update the statistics, rebuild indexes, and possibly shrink the Log if it is unrealistically big
7b) Backup and Restore over the original database
7c) DBCC CHECKDB ('MyDatabaseName') WITH ALL_ERRORMSGS, NO_INFOMSGS
Good luck!
Kristen
I've got a customer who thinks their data should be sepeated and reside on two seperate databases in case one is compromised. Now I've never heard of anyone doing this and would like comments from the users here as to why this should/(shoult not) be done.
I'm assuming that they want to keep their general info e.g. name and address on one database and keep other (medical info) on the other db.
None of our db's are outside of the firewalls so to me this makes no sense but I would like feedback.
thanks.
I have to DBs. One is the original and the other is for testing. They both have similar data structure. Wanted to know if there is a way to update tables contents in one db with data that is in the other one.
View 4 Replies View RelatedI'm not quite sure if this is the correct forum to post this, if not please advise where should I post.
I have 7 databases with same structure, but different data in it, I need to have a query to consolidade some info from all of them in one report, is it possible just in onw script? how should I do it?
thanks,
Marcus
PS: I'm a beguinner in this so I apologize if the question seems stupid, or wrong.
Hi,
I have a database on sqlserver 2000 which contains some tables which receive Create/Read/Update statements from applications, and +-5 tables which contain a sort of read-only data: the applications are only reading from these tables.
Sometimes these 5 tables need to be updated with new data. Currently I am doing this as follows:
Execute a long-running operation (e.g. 1 week), which will add new data, against a Test-database (which is a restore from a backup of the original database)
Some people do some checks to see if the new data in the Test-database is correct
If (2) is OK, the only thing which needs to be done is copying the data of the 5 tables in testdatabase to the 5 tables of the production-database.For (3), I currently use a DTS-package which consists of a "Copy SQL Server Objects"-task. This task is configured to copy the 5 tables (objects) from testdatabase to productiondatabase. The data in these 5 tables is around 20GB, and this task takes a lot of time compared to a backup/restore of the same size of data. I already tried to speed it up by creating different filegroups and wanted to restore only 1 filegroup but you can read here that making a filegroup backup on a testdatabase and trying to restore it on a production database won't work. Is there some other way to speed this up? Is my current way of working good practice?
Thx!
Hello
I'm looking for sample database for data mining in SQL Server 2005. In my project I need to use all algorithms provided by SQL Server 2005. I know that we have "SQL Server 2005 Samples and Sample Databases" on microsoft.com site, but there are only AdventureWorks data sets. I need sth less popular, but similar.
I have found http://www.kdnuggets.com/datasets/index.html and others, but I don't see data sets good for multidimensional analysis.
The origin of data may be natural or artificial, it doesn't matter.
I don't have to generate my own data, because I had to discover new knowledge, that will be surprising to me.
Any help would be appreciated.
Thanks in advance.
(sorry for my English)
I wanted to check and see if anyone else has experienced an issue with querying sys.databases and not getting all the database rows returned?
I have a query that pulls back a list of databases so I can do backups on them. Randomly I will miss a row or two of databases. If I immediately (in code time) re-issue the same query, I'll get the missing databases. Oddly it seems a more prevalent problem on our Itanium servers, but all our 2005 servers seem to have the same symptom. Also odd, but the master database seems to be most likely to not be in the list (i.e. it happens the most frequently).
It's not a huge deal, but it's a pain to have to run the same query twice, compare the two lists and then double check that the adding or removing of a database should have happened.
hy all..How can I achieve the above ?Moreover can I retrieve data from multiple databases which are lying on different DBMSs ( like retrieving from database A which is on SQL and from database B which is on Oracle ) ?Rgds.
View 2 Replies View Related