Hi,
I want to perform a join on two tables from two separate database servers.
Code Block
SELECT *
FROM tbl1 AS t1
INNER JOIN
"SERVERASQL2000".Production.tbl2 AS t2
ON t2.UserID = t1.UserID
Why do I get this error...
Invalid object name 'SERVERASQL2000.Production.tbl2'
thanks.
Is there anyway,can we find the list of servers by querying at active directory?
View 3 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 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)
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
Hi all. I have the problem on query.
This is my query.
select D.fullname, P.religion, E.empno from pspersonaldata as P
inner join hremployees as E on P.dcno = E.empdcno
inner join psdatacenter as D on D.dcno = E.empdcno
where P.religion in ('Born Again','Baptist', 'Catholic')
How could I make a query that returns a result for either "Born Again, Baptist, or Catholic". The parameter would depend on the input of the user depending on how many religion the user inputed.
If the user inputed Born Again and Baptist, the result is the employees that have a religion of Born Again and Baptist.
Thanks
-Ron-
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.
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
Hello,I am relatively new to doing non-trivial SQL queries.I have to get data out of 8 diff views based on a parameter Name.There is a view having name-ssn pairs. All other views have SSN field.For a person there MAY NOT be data in all the views.I have to populate data into diff tables in a Report from differentviews.I would like to know what is the best way to approach it.So far I was trying an Inner join from the Name-ssn vies to all otherviews based on the SSN and test for the name field with the inputparameter.I am thinking there will be problem of Cross join if I dont have datain all views about a person.Or the best way is to write query for each view and have all of them ina stored procedure ?Any help will be appreciatedThanksBofo
View 1 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!
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 RelatedFolks,While I still have some hair left, can someone help me with thisquery?I have a table "TestRunInfo". Amongst other fields there are"TestRunIndex" (Pri Key), "TesterID", "Duration", and "Status".The Status field links to a Status table, which links the index valueto a more meaningful label "Pass", "Fail" etc...As you may have guessed, there is a record for each test that anindividual tester runs, and with that record is a duration, and status(1,2,3 etc).What Im trying to do, is create a datasheet view, with a single rowfor each testerID, summarising that Testers work as follows:TesterID, Total Duration, Count of passed tests, Count of failed testsSo far I have:Select TesterID, sum(Duration), count(Status) FROM TestRunInfo GROUPBY TesterIDBut this of course purely gives the total number of tests run by thatengineer as the count. I need to break it down. Help? Someone?Please?!?!?TIASteve
View 2 Replies View RelatedHi, all.
I have a question about how you would design this. I have to return a list of projects in a stored procedure with a statement like this:
SELECT ProjectID, ProjectName FROM PROJECTS;
This populates a DropDownList with all possible projects. When the user selects one, I need to find out more information about that project, like this:
SELECT a.ProjectID, a.ProjectName, b.OtherStuff FROM PROJECTS a INNER JOIN OTHERSTUFF b on a.ProjectID = b.ProjectID;
In the old ASP world, we just filled the ProjectID parameter with NULL if we wanted to return all results, and the stored procedure was set up to dynamically return results based on whether that parameter was NULL or not.
I'm looking for a more elegant way to do this. I can use two stored procedures, but I'm not sure what to name them. Obviously, I could call one 'SelectProjects' and the other 'SelectProject', but that just doesn't seem elegant enough. Anyone have any other ideas?
I'm fairly new to SQL and am just setting up a Windows 8 app using an Azure SQL server. The issue I have is looking up a part number supersession and getting the latest number. One part number can have multiple supersessions (ie RTC5756 > STC8572 > STC3765 > STC9150 > STC9191 > SFP500160 ).The data I am supplied monthly has both the superseeded items and the supersession information in both columns and is not easy to decipher - for example:
Supersessions Table
----------------------
RTC5756 | STC9191
SFP500160 | STC9191
STC9191 | STC2951
STC3765 | STC9191
STC8572 | STC9191
STC9150 | STC9191
[code]...
The newest part number is kept in a separate table - called "source" - which in this instance is SFP500160. I need access to the latest part number but also to the part's previous numbers, due to the fact that some people may still be stocking them as an old part number and for them to search by. Is there an easy and efficient way of doing both a lookup for the supersessions and a join on the two tables to minimize the queries on the database?
Hi All,I have recently published a website to our webserver and i get a sql error. We have a webserver that does not have sqlserver on it and and our database server which does. i have used the configuration utility to to setup my users and roles which created the ASPNETDB in my local App_Data folder. Is there a way to copy this database to our database server and change the references so the site refers to the new instance on the database server as apposed to the local instance when a user logs in?ThanksBryan
View 3 Replies View RelatedWhat's the easiest way to run the same db across multiple servers?
View 4 Replies View Relatedhi
Is there a way to have a select query on multiple servers ? (2 databases , each one located on a sql server)
suppose i am repliacting(Transactional Replication) to two servers using the same publications can i stop replicating to one server without disturbing the other server?
If i resume the replication to the server after two days what will happen to the two day's data?will they be replicated when i resume the replication?
Thank you
Question:
How to prevent a user/admin on one SQL 7 server, who knows the system admin password that is common to both servers, from accessing a second SQL 7 server via the SQL Server Group setup process.
Is there a way from shutting off remote access to a SQL server without limiting RPC?
All reading points towards 1) Remote Access Options, 2) Store User Independent, 3) Control Store as options to locking down a server. Would like to hear from someone that ran into this type of issue. Third party software available?
I've tried the following without any success: 1) I have deleted the second SQL server registration from the first SQL server group - but this is too easy to delete and recreate, 2) edit the SQL server registration for the remote server and force authentication - almost there if SQL prevented deletion of the registration without verifying the logon/password of the person who created it (again able to delete and recreate), 3) played around with the client utility to remove the TCP/IP pipe entry.
Anyone else pulling their hair out on this?
TIA!
Please excuse my niavety on this subject, But I have a simple question. When you have more than one SQl server 7 installation on the same network, each using different machines, does one automatically have the ability/authority to be able to stop the other server at will??
Thanks in advance for any help
Simon
Joe writes "I am trying to find whether I can have multiple SQL 2005 servers but having same security profiles and maintain them grom one place only. I prefer not to have windows authentication.
Thanks a lot"
I'm sort of new at MS SQL Server - I'm trying to gather system table information from multiple servers about backups on our site and insert the rows the query returns into a table on one central server where I'm running the stored procedure from. I have the server names stored into a table but when I attempt to connect to servers, I get an error which tells me that I need to create a linked server by using sp_addlinkedserver stored procedure. People that I work with tell me they want to do this without creating linked servers - Is there a way to accomplish? When I run the query, it only retrieves data from the local server I'm running the stored procedure on, but like I mentioned, when I attempt to connect to other servers, I get the error message that it couldn't find the server name in "syservers" and that I need to run sp_addlinkedserver. This also happens if I run the same query in OSQL from Windows command line.
Any help on this would certainly be appreciated.
Thanks in advance.
Hi,
I need to access a table from another server in my procedure...I am now connected to say SERVERA...I need to access another table SERVERB.TABLE2 in the same procedure...
Is there a way to do that...
I need to connect to two servers from the same procedure to get data..
Please get back
Thanks,
I have to maintain several SQL Server 7 DBs across multiple NT Domains (same network). How do I access the SQL Server on the second domain from enterprise manager?
Thanks!
Lia
We have a growing number of servers and databases on each server that all share the same (sub)set of sprocs and UDFs. DTS packages, which we use for data import, frequently need to be copied between the servers. What is the best way to maintain this? Ideally, I would like to be able to click a button and have a script creating or altering one or more sprocs automatically run aginst all DBs on all servers. Likewise, I'd like to be able to copy DTS packages to all servers.
We use SS2000 SP4 and plan to migrate to SS2005. We also use ASP.net 2.0 and VS 2005 SP1.
Hi,
I have zero experience running any databases that spread further than 1 machine, so I have a few theory questions here that hopefully someone can help with. Hopefully this is the right forum, I'm not sure if it classifies as 'clustering'.
Anyways, we are launching a web app that is going to start with just 1 webserver/db server. For speed reasons, after some growth we might have to have a load balanced setup with a webserver in europe and one in north america. Basically the webservers are going to be serving 100,000's of files and each time a file is served it needs to be recorded in the database.
I think that if I'm connecting my european webserver across the internet to my db server, thats killing the purpose of having a webserver in europe to make for faster responses.
I am thinking that this european web server/db serving is only going to be logging the files served. Is there a way to import them into north american database everynight ?
I'm not sure what the best approach would be for something like this, but any suggestions are greatly appreciated.
Thanks very much,
mike123
Is it possible to get an aggregate list of databases from multiple servers, without using SMO?
All target servers are running SQL Server 2005.
Ben Aminnia
Hi, I was wondering what the licensing requirement would be for following scenario:
1 active sql server installation installed on a windows failover cluster.
1 passive mirror sql server (single server).
1 passive log-shipped sql server (single server).
In the microsoft white paper it says:
When doing failover support, a server is designated as the passive server. The purpose of the passive server is to absorb the data and information held in another server that fails. A passive server does not need a license, provided that the number of processors in the passive server is equal or less than those of the active server. The passive server can take the duties of the active server for 30 days. Afterward, it must be licensed accordingly.
So do I need to buy an enterprise license for each of the additional passive servers...in the above example meaning I would need 3 licenses in total?
Thanks,
Paul
Apologies if this question has been asked before. I am about to upgrade a multiuser application based on Access97 and VB6 to C# and possibly SQL Server Express.
My customer does not want to use a server based database solution ie a single instance of SQL Server, but prefers to have a common networked dataset which is accessed via user's own individual database engines. This is because of restrictions on the installation of server based apps.
Does this scenario completely rule out the use of SQL Server Express?
Thanks
Can anyone outline some best practices for sending the same message out to multiple servers? What is the easiest way to do this?
Currently, I have a message sent from Server A to Server B, which is fired by a table insert, update, delete trigger.
The goal is to keep this table synchronized on many remote servers. It sounds like a tedious exercise to deploy the SSB messages, queues, services, etc to 20+ more sites! I am hoping there is a shortcut of some kind.
Any advice is appreciated.
I'm using EncryptByKey to encrypt data in my SS2005 database. Since our server is really slow to access from home to work on, I used the Database Publishing Wizard and installed the db to work on at home. Then I created the certificate and symmetric key in my home db.
When I pull info using the DecryptByKey on our database at work on Windows 2003 Server, no problem, the data is decrypted. However, the same data does not decrypt at home on my Windows XP computer. I'm using TripleDes on both machines for the symmetric key (AES won't work on XP).
--To create my cert and key:
USE My_DB;
CREATE CERTIFICATE MyCert
ENCRYPTION BY PASSWORD = 'some password'
WITH SUBJECT = My Data',
START_DATE = '01/01/2007',
EXPIRY_DATE = '01/01/2099';
GO
CREATE SYMMETRIC KEY MyKey WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE MyCert;
GO
To encrypt:
OPEN SYMMETRIC KEY MyKey
DECRYPTION BY CERTIFICATE MyCert
WITH PASSWORD = 'same password as above';
Insert my record, use scope_identity to return primary key into @CustomerID.
INSERT INTO [Customers] (EncryptByKey(Key_GUID('MyKey'), @DataToEncrypt, 1, CONVERT( varbinary, @CustomerID)))
CLOSE SYMMETRIC KEY MyKey
To decrypt:
SELECT CONVERT(varchar(3925), DecryptByKey(EncryptedField, 1, CONVERT( varbinary, @CustomerID))) as PlainTextData
FROM Customers
WHERE (CustomerID= @CustomerID)
Everything works fine when I run the decrypt query on the database on our work server. But I'm not getting decrypted data at home. Is the symmetric key or certificate machine specific? If so, that will cause a huge problem when we deploy to a production server.
Thanks in advance for your help!
I have an existing table that I want to partition across multiple servers. I am having a hard time finding an article that explains how to do it.
I am in the process of designing applying a scale out architecture to our database...but have hit this brick wall.
Any help would be great.
Thanks!
Eric Elliston
SWFLParent.com
http://www.swflparent.com
I have multiple SQL 2000 servers that hold data which I retrieve using a single SQL stored procedure. Unfortunately, I don't know how to retrieve this data asychronously (in parallel).
For example, I retrieve data from two linked servers using the following:
select * into #temp from openquery(SomeLinkedServer,'exec BigQueryHere')
select * into #temp2 from openquery(OtherLinkedServer,'exec BigQueryThere')
.
.
lots of manipulation of the temporary tables to get what I want, etc.
.
.
The problem I have with this is that there is no reason why the first query should have to finish before the second query begins (serially) because these are on separate servers. Is there a way to execute these so that they run at the same time?
Bill