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
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
HI
I need help
how can i fill data in textboxes from sql databases but two different tables when i select a name that is inside a dropdownlist
my controls are as follows
<asp:DropDownList ID="ddl" runat="server" DataSourceID="SqlDataSource13" DataTextField="fullname" DataValueField="fullname">
</asp:DropDownList>
<asp:SqlDataSource ID="SqlDataSource13" runat="server" ConnectionString="<%$ ConnectionStrings:NPI Employee MasterConnectionString2 %>"
SelectCommand="SELECT [FirstName]+' '+ [Surname] as fullname FROM [Employee] where CurrentEmployee_YN=1 order by FirstName "></asp:SqlDataSource><table bordercolor="#111111" cellpadding="0" cellspacing="0" style="width: 100%;
border-collapse: collapse; height: 32px; visibility: hidden;" id="table0">
<tr>
<td style="width: 159px; visibility: hidden;">
</td>
<td style="width: 170px">
</td>
<td bgcolor="#eeeddb" style="width: 20%; height: 25px">
<strong>
Order No：</strong></td>
<td bgcolor="#eeeddb" style="width: 26%; height: 25px">
<asp:Label ID="OrderNo" runat="server" Width="104px"></asp:Label></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 159px; height: 25px">
<strong>
Account No：</strong></td>
<td bgcolor="#eeeddb" style="width: 170px">
<asp:TextBox ID="AccountNo" runat="Server" MaxLength="10" Width="130px"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator6" runat="server" ControlToValidate="AccountNo"
Display="Static" ErrorMessage="Enter Acc No." Text="*"></asp:RequiredFieldValidator></td>
<td bgcolor="#eeeddb" style="width: 20%; height: 25px">
<strong>
Today's Date:</strong></td>
<td>
<asp:Label ID="Label1" runat="server" Text="Label" Width="200px"></asp:Label></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 159px; height: 25px">
<strong>
Travel Consultant：</strong></td>
<td bgcolor="#eeeddb" style="width: 170px">
<asp:TextBox ID="Consultant" runat="Server" MaxLength="30" Width="128px"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator7" runat="server" ControlToValidate="Consultant"
Display="Static" ErrorMessage="Enter Travel Consultant." Text="*"></asp:RequiredFieldValidator></td>
</tr>
</table>
<center>
</center>
<center>
</center><table bordercolor="#111111" cellpadding="0" cellspacing="0" style="width: 80%;
border-collapse: collapse; height: 32px; display: block; visibility: hidden;" id="table2">
<tr>
<td align="center" bgcolor="#ffcc33" colspan="3" style="width: 90%; height: 29px">
<font color="#000000" size="5">Enter Passenger(s) Details</font></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 25px">
<strong>
Surname：</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<asp:TextBox ID="Surname" runat="Server" MaxLength="30" Width="148px"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"
ControlToValidate="Surname" Display="Static" ErrorMessage="Enter Surname." Text="*"></asp:RequiredFieldValidator></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 20px">
<strong>
Name：</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 20px">
<asp:TextBox ID="Name" runat="Server" MaxLength="30" Width="148px"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="Name"
Display="Static" ErrorMessage="Enter Name." Text="*"></asp:RequiredFieldValidator></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 25px">
<strong>
Initials：</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<asp:TextBox ID="Initials" runat="Server" MaxLength="5" Width="148px"></asp:TextBox>
</td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 25px">
<strong>
Title：</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<asp:DropDownList ID="DropDownList1" runat="server" Width="156px">
<asp:ListItem></asp:ListItem>
<asp:ListItem Value="Mr"></asp:ListItem>
<asp:ListItem Value="Mrs"></asp:ListItem>
<asp:ListItem Value="Ms"></asp:ListItem>
<asp:ListItem Value="Dr"></asp:ListItem>
<asp:ListItem Value="Prof"></asp:ListItem>
<asp:ListItem Value="Min"></asp:ListItem>
<asp:ListItem Value="Other"></asp:ListItem>
</asp:DropDownList>
<asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ControlToValidate="Dropdownlist1"
Display="Static" ErrorMessage="Select Title." Text="*" Width="20px"></asp:RequiredFieldValidator>
</td>
</tr>
<tr><td bgcolor="#eeeddb">
<strong>
Department</strong>
</td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<asp:TextBox ID="Department" runat="server"></asp:TextBox></td>
</tr>
<tr><td bgcolor="#eeeddb">
<strong>
Cost Centre</strong>
</td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<asp:TextBox ID="CostCentre" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 25px">
<strong>
Tel：</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<input id="Tel" runat="SERVER" maxlength="15" name="Tel" onkeypress="if((event.keyCode<48)|| (event.keyCode>57))event.returnValue=false"
style="width: 143px" type="text" />
</td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 25px">
<strong>
Fax：</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<input id="Fax" runat="SERVER" maxlength="15" name="Fax" onkeypress="if((event.keyCode<48)|| (event.keyCode>57))event.returnValue=false"
style="width: 143px" type="text" />
</td>
</tr>
</table>
cost centre and department are from cost table
and the rest are from employee table
My environment:XP Home, VWD, SQLEXPRESS.A purely local setting, no network, no remote servers.
I try to do a JOIN query between tables in the membership ASPNETDB.mdf and one table in a self created 3L_Daten.mdf.
After dragging the tables into the Query Design window and connecting them VWD creates this query (here I added the control declaration):
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringAspNetDB %>" SelectCommand="SELECT aspnet_Users.UserName, aspnet_Membership.Password, aspnet_Membership.Email, aspnet_Membership.PasswordQuestion, aspnet_Membership.PasswordAnswer, aspnet_Membership.CreateDate, aspnet_Membership.LastLoginDate, aspnet_Roles.RoleName, [D:VISUAL STUDIO 2005WEBSITES3L_V1APP_DATA3L_DATEN.MDF].dbo.Personendaten.Age, [D:VISUAL STUDIO 2005WEBSITES3L_V1APP_DATA3L_DATEN.MDF].dbo.Personendaten.Sex, [D:VISUAL STUDIO 2005WEBSITES3L_V1APP_DATA3L_DATEN.MDF].dbo.Personendaten.Area FROM [D:VISUAL STUDIO 2005WEBSITES3L_V1APP_DATA3L_DATEN.MDF].dbo.Personendaten INNER JOIN aspnet_Users ON [D:VISUAL STUDIO 2005WEBSITES3L_V1APP_DATA3L_DATEN.MDF].dbo.Personendaten.User_ID = aspnet_Users.UserId LEFT OUTER JOIN aspnet_Roles INNER JOIN aspnet_UsersInRoles ON aspnet_Roles.RoleId = aspnet_UsersInRoles.RoleId ON aspnet_Users.UserId = aspnet_UsersInRoles.UserId LEFT OUTER JOIN aspnet_Membership ON aspnet_Users.UserId = aspnet_Membership.UserId"></asp:SqlDataSource>
THIS WORKS, BUT:
As you can see the database 3L_Daten.mdf is inserted with its full path, which is not feasible for deployment reasons.
My question: How can I address both databases purely by their database names ? Both have been created within VWD and lie under App_Data.
(I tried almost everything, I practiced with the SQL Server 2005 Management Studio Express Edition, I tried linked servers, all without success).
Thank you for your consideration.
How do you select data from 2 separate databases residing on 2 servers?
Is there a way?
Angel
i need to create a rowset containing database names where a particualr table name exists.
ie. in Oracle i would select owner from sys.all_tables where table_name='MYTABLE'
I know master.dbo.sysdatabases contains my owners, and in each database sysobjects would identify MYTABLE ..
sp_tables only lists those in the current database, but i want to list all databases that contain MYTABLE
a single select statement would be nice.
Hi,
I'm quite new to MS SQL and am having a few problems I hope someone here can help me with.
I've created a small web application that enables users to backup and restore databases easily.
There is one table that holds information about the backups that have been taken and one table that holds details about each database (name, location etc)
What I want to do is have a web interface that allows a user to link a new database (one that is already attached to SQL but) to the system by inputting it into the database details table.
Ideally this will be a drop down menu that shows the databases attached to the server that arnt:
1. Already in the backup system table
2. Not the system databases.
This will allow the user to easily select the database and give it a title (usually different from the local name of the database) for identification.
The database local name, and title would then be input into the database details table.
I'm thinking that I need to use a SELECT statement, but I'm not quite sure how I can restrict the results to databases that arnt already attached or already in the system.
I hope that wasn't too muddled an explanation!
Thanks,
Steve
I have several databases on the same server that have the same table structures within them. Call them
Server.A
Server.B
Server.C
...etc.
I have designed a report that works in one database, say "A". I would like to add a parameter box to the report giving the user the ability to select which database to run the report in. I'm not sure if this is possible.
There may be more complex ways to handle this, say, with dynamic SQL or by building a large UNION of select statements across the various databases. But I figured I might ask for a simple solution first. :-)
Just wondering, in DTS, can I run a SELECT script that selects from 2 different databases (both on the same server)?
I can run this in Sql Query Analyzer, but in a DTS, it doesnt accept my database name prefixes:
SELECT
a.something
FROM
DB_ONE.dbo.product a, DB_ONE.dbo.mp_brand b, DB_TWO.dbo.lk_pcat_cutover c
WHERE
a.PCat <> c.Pcat
(where DB_ONE and DB_TWO are the 2 different db names)
I have one connection to DB_ONE... does this mean I cant access DB_TWO when using this connection? I want to try and avoid using a temporay table for storing DB_TWO's data in DB_ONE... Is this possible?
Thanks,
Andre
I need assigning a group of users read, write, update (not delete) permission on all user databases. is there any way to do so instead of creating role on each database separately.
View 2 Replies View RelatedHi,We are discussing possible implementation for sql2005 database(s). This database will serve one web portal. Part of data will get into it by hand, and part will be replicated from internal system.Some of us are for creating two separate databases, since there are two separate datasources. One, automatic, will change very little over time and requires almost no maintenance. Other datasource will be manual input. Tables and procedures related to this part will change over time.Some of us are for creating single database, since it will serve one web site. More important this group is concerned about performance issues since almost every select will require join between tables that would be stored in two separate databases. Do these issues exist? Can you share some insights, comments, links about this?
View 2 Replies View RelatedHow do I write an sql statement to link tables in two databases and query them? For example: SELECT db1.table1.field1, db2.table1.field1 FROM Table1 INNER JOIN db1.table1.field1 ON db2.table1.field1 = db1.table1.field1
Thanks
I have a basic sql statement, where I have a usersID, and I want to joing that usersID to another table in another database to get the users first and last names. How do I join across databases... each with a different connection string?
Here's what I want..
Select usersID from tableA in databaseA, and usersFirstName, usersLastName from table B in database B where the usersID from tableA = the usersID in tableb.
I am trying to find out what records on in one DB and not the other. This SQL statement works find if the DBs are on the same Server. If I replace on the DBs with a linked DB (on another server) I get the following error.
SELECT *
FROM [LinkedServer].[DBonLinkedServer].[dbo].[Sales Price]
WHERE NOT EXISTS
(SELECT [LocalDB].[dbo].[Sales Price].*
FROM [LocalDB].[dbo].[Sales Price]
WHERE ([LocalDB].[dbo].[Sales Price].[Item No_] collate Latin1_General_CS_AS = [LinkedServer].[DBonLinkedServer].[dbo].[Sales Price].[Item No_]) AND
([LocalDB].[dbo].[Sales Price].[Unit of Measure Code] collate Latin1_General_CS_AS = [LinkedServer].[DBonLinkedServer].[dbo].[Sales Price].[Unit of Measure Code]) )
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "LinkedServer.DBonLinkedServer.dbo.Sales Price.Item No_" could not be bound.
Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "LinkedServer.DBonLinkedServer.dbo.Sales Price.Unit of Measure Code" could not be bound.
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
I want to customize database maintenance to run only on selected databases based on their status in sys.databases, without having to statically select them in the GUI.
I'm trying to write an SSIS package that will do the following:
1. Query for a list of databases that are not read-only or recovering, etc.
2. Perform database maintenance tasks (e.g., rebuild indexes) on those databases.
I'm doing this because the database maintenance tasks do not provide a way to select databases based on criteria or a SQL query filter.
My package includes an Execute SQL task to get the list of databases, a Foreach loop container to loop through the databases, and then...this is where I get stuck. I can't use the existing maintenance tasks (Rebuild Index, Update Statistics, etc.) because I don't know how to provide an input variable for the list of databases. I'd rather not write dynamic SQL to generate commands for each database object.
Does anyone know how to do this?
Thanks much.
David
I am unable to the access on table even after providing the SELECT permission on table.
Used Query by me :
Here Test is schema ; Card is table ; User is Satish
To grant select on Table
GRANT SELECT ON TEST.Card TO satish
Even after this it is not working, So provided select on schema also.
used query : GRANT SELECT ON SCHEMA::TEST TO Satish.
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
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 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
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