I have two database:
FoxPro database - contains client info (name, address)
SQL Server database - contains appointmetns, orders, jobs etc.
How can I create reports using both databases?
Thank you
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 am a beginner of Reporting Service. I am trying to create a report using multiple databses. For example, I want to create a report called RevenueByCustomer, so I need to get data from the Customer Table of CRM database, which contains customer information, and I also need to get data from Transaction table of Sales database, which contains all the revenue information. In order to get data from both database, I have created two dataset. One is Customer dataset, which get all required customer data from CRM database, and the other is Revenue dataset, which gets data from Sales database, they used seperate datasource (because each datasource only contains one database connection). Now my problem is how can I make them be displayed in one report ? It seems to be like a Master-Detail report, I need to sum up all trasactions for a particular customer and also need to display the customer name with each TotalAmount, but how can I make these two dataset can be merged together or used an extra query to do it?
Please help me, thanks a lot
This Query below is used to calculate the total amount for each customer in Revenue dataset:
SELECT CustomerID, sum(TotalAmount - TotalTaxAmount) AS TotalExcTax, sum(TotalAmount) AS TotalIncTax
FROM TransactionMaster
WHERE (Updated >= @Start) AND (Updated <= @End)
Group By CustomerID
the other one here is used to get customer code and name in Customer dataset:
SELECT ID, Code, Name
FROM Customer
I am in the process of creating a Report, and in this, i need ONLY the row groups (Parents and Child).I have a Parent group field called "Dept", and its corresponding field is MacID.I cannot create a child group or Column group (because that's not what i want).I am then inserting rows below MacID, and then i toggle the other rows to MacID and MacID to Dept.
View 3 Replies View RelatedMy application uses 2 databases from 2 different server. When i create a report model in business intellegence development studio I can only create 1 datasource for each database, and 1 datasource view for each datasource. Then when I create a new report, I can only use one datasource.
Is it possible that my report can be built on more than one databases?
thanks in advance
I am looking to create an SSRS report based on multiple locations. There will be one report that presents a summary of all reports, and then individual report for each location.Instead of creating 2 separate reports, is it possible in SSRS to display different datasets based on the parameter selected?
View 3 Replies View Related
hai, iam new to ssrs please help me,
my report having 10 columns, i can explain my need with example that is, assume like this customer id is first column of my table customer id is 101 it having 3 departments a1,b1,c1,perticular department that is a1 having emp1,b1 having emp2,c1 having emp 3.
i want output like this when clicking + customer id driildwon it display 3 departments taht is a1,b1,c1, when +a1 drill down clicking i need to dispaly emp1, corresponding b1 to emp2 , c1 to emp 3.
above explanation is only one column of the table, like that iam also displaying this driiling procedure for remaining different columns.
and i need to display customer information by weekly,daily,monthly,yearly at bottom of the report
please give which logic used in creating format like above drilldown report which having multiple drilldowns for all columns in a table
if any body give procedure for creating fromat for drilldown report which having multiple drilldowns for all columns in a table is appriciate.
thanks
jacks
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'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
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
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 RelatedHello,
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 see under databases in the object explorer of SQL Server Management Studio that there are two sub-folders: System Databases and Database Snapshots.
Is there a way to create other folders here? It would be nice to be able to group the various databases (ex. Samples).
tia
Autofreak wrote:
Hi All,
I have installed the SQL Server 2005 Express edition and its running fine.
I like to connect to the server and create database. Should I do it
through command prompt only or any tool is available like
Enterprise manager for this ?
I am using Visual Studio 2008 and have created a .mdf database within my project. I want to be able to do a few things and I am not quite sure where to start. The program I am writing manages rental properties. Thus, each rental property that the user owns needs its own sql express database. For this to work:
1. I need to be able to dynamically create a database for each property that the user owns.
Is it best to do this as described here http://msdn2.microsoft.com/en-us/library/bb399420.aspx ? Or is it best to have a template database and then make a duplicate of the database using the File.Copy() method.
If it is best to do it programatically as shown in the link above, how do you create relationships between tables?
2. I need to be able to dynamically add users to the database via c# code. How does one do this? Is it possible do through LINQ?
Hopefully someone can point me in the right direction. Thanks
Nick
Is it possible to retrieve Data from SQL Database #1 and insert it into SQL Database #2 using a Stored Procedure? Thanks. If so Can you show example. Thanks
View 12 Replies View RelatedHi All
I have a question about having one or multiple databases for a large project. What are the main differences between designing my database as one or breaking in to multiple databases. I should mention that there are many relationships between tables in different DBs in the multiple DBs state?
regards
sasan
Hi,
Is it possible to create a relationship between to tables in a deferent databases and How to do it from SQL Server Management Server!?
Thanks.
Hi,
Will SQL7 create all databases on C: drive only
from now on?
After SQL7 Beta3 installation I tried to create a new db
but in the 'Browse' window in 'Create DB' procedure
I can see only C: & D: drives (btw D: is CD-ROM).
It doesn't see my E:, F: & G: hard drives.
When I try to create my db using 'create database'
statement it again gives me an error 'path not found'.
Also, I have 512 Mb free on C: drive, but
when I create new db with 100-mb data + 50-mb log
on C: SQL7 says that I don't have enough space for
this db.
Does anybody had the same problem already?
I need to convert my Sybase db into SQL Server7
and got this problem with creating db right in the start.
Thank you
Help me please!
I've installed SQL Express 2005 on my XP. I'm trying to create a database but I keep getting the error no matter what I do.
Msg 1802, Level 16, State 4, Server MYCOMPUTERSQLSERV, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
Msg 5123, Level 16, State 1, Server MYCOMPUTERSQLSERV, Line 1
CREATE FILE encountered operating system error 5(error not found) while attempting to open or create the physical file 'C:Program FilesExos9300DatabaseExos93.mdf'.
I seem to be having a number of problems using managment studio (MS) against my locally installed SQL 2005 Standard Edition. E.g. when I create a default database using MS it says...
TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The server could not load DCOM. (Microsoft SQL Server, Error: 7404)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=7404&LinkId=20476
However, the hyperlinks are less than helpful. I also get problems doing various maintance work from MS. I don't think its a database engine problem 'cause I can do all the work via TSQL DDL but the interface is so much easier to use (or should be). BTW it's running on XP Pro (firewall problem?).
Before I start coding ...
Is there any existing software/trick/hack to create a Report Project from the DataSources and Folders/Reports on a Report Server, i.e. the inverse of Deployment?
Would there be anybody interested in such a thing, and, if not, why not?
How do I display multiple parameter values on report page from a multi-value report parameter. For example, I have a report parameter where users can select multiple attendance codes and I want them displayed at the top of the report after it's run.
Currently, only the first value is showing on the report.
Thanks.
Hey 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)
Hi all:I am trying to create a website which will search and return results that are from multiple tables in different databases. I have the code to search a singular database right now, and I tried to make a connection to a second one, which worked, but I don't know how to send the proper sql commands to the second database or how to link them in the code. Does anyone have any pointers on creating a site which will search different tables in different databases with a known connection between databases? Any help would be really really really really appreciated.-Shrey
View 3 Replies View RelatedHow can I include tables and views from database A when building a view in database B, if possible?
Same for stored procedures.
so, I am trying to write an sql that requires information across two
different database. It's under the same sql server. However, the
location of the tables are from different database.
Any links I can read about that can show me how to write the queries?
Thanks in advance, and merry X'mas.
We have a number of databases(on sqlserver 7.0) which has the same structure but with data for different users. We would like to change the structure of the databases with minimum effort and with minimum down time.
Is there any method to automate the change so that the change is reflected in all the databases when a single database structure is changed?
Any suggestions in this regard are welcome and urgent
Okay this is a test...actually I am still learning SQL and need some help. Does anyone have any information on being able to move indexes from one database to another. My scenario is I have 3 databases, Development, QA and Production. I want to move/copy indexes I created in Development to the QA database. I have many indexes so I do not want to have to recreated them if I can avoid it. Any suggestions?
Thanks!
Hi,
Currently developping a c# database based application, which will be used in different establishments worldwide. I'm currently a bit confused by the ms sql features offered. Because the huge data amount which will be fetched during application use from the database, the best option is probably to put a ms sql server in each establishment to realise quick data fething. Problem is that the servers, which are all running on the same database design have to be synchronised real-time. I read about datareplication with the merge option which I thought was a nice solution. The subscribers can only have read-only rights on these subscriber databases, because the merging doesn't work appropriate i heard, so this is no good solution. Then I read about distributing transaction coordination. Seems a good solution as well, but what happens if a server system crashes for one day and then comes back online? It won't be up to date anymore... Right now I think about a combination of these 2 features. DTC in combination with merge replication, but there must be a better solution. Fact is that a lot of users will be editing data worldwide non stop, and everyone has to be up to date. Because of huge data fetching I don't think it's a good solution to let everyone work worldwide with one single database. Is there a possibility to realise this with ms sql server? Because I'm getting more and more confused... Thx in advance.
I need some guidance! We are running SQL Server 2000. We have 4 or 5 databases with the same schema, and I need to make sure that their structures all stay synched. We will have more soon. I also need certain tables to have the same data across all the databases.
I need a way to manage changes on the multiple databases! Right now I generate a script in Enterprise Manager, then run it in Query Analyzer on each database, manually. There's too much room for error here.
Any suggestions are appreciated! I am currently evaluating Embarcadero's products, but they are complicated and confusing - I'm not sure yet which of their tools is meant for this purpose.
Thanks,
Avi
I have a problem like this. I have an application which shud be internet hosted. All transactions should be updated to the central server of the net as well as an intranet DB server. (I'll have a DB server - SQL Server 2000 in each intranet). Is this possible.. i'm planning to use ASP to develop the net based application.
Thank you in advance..
Geetha R
Ok, so right now we have a bunch of queries similar to the following:
Code:
use db001 select co#=(001), fieldvalue from tablename where fieldid = '1'
use db002 select co#=(002), fieldvalue from tablename where fieldid = '1'
what i need to find is if there are duplicates of fieldvalue and have it display all in one.
Basically right now we run all these there are 25 databases and we export everything to a file and then open it in excel. we then format it it so its just co# and fieldvalue and then look for duplicates.
i would like to avoid the whole excel part and just run a query that will display any duplicates of fieldvalue across all 25 databases in "tablename"
hope that makes sense. - Thanks