SSRS How To Take Data From Two Different Databases
Mar 17, 2008
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.
I want to use two databases for my reporting services 2005. I want to connect to one database and check whether the logged in user is a portal admin using portalid and userid.If yes, then I will allow the user to user use all the stores. Stores are displayed in the drop down list as the multiselect parameter. If no, then I will connect to another database and check the user rights. Based on the user rights I will select only the stores that are applicable the user. i don't know how to use two databases in the reporting services. Please give me an idea to do this.
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.
Was wondering if there was a best practice minimum permissions for creating a SQL login to use when setting up a new shared Data source for SSRS report manager?
Something along the lines of them being a data read for the DB and permissions to update tempdb?
Would have thought it not advisable to have the login be able to update the main db...
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.
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?
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?
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.
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.
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?
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?
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?
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.
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.
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
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.
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.
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
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.
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.
