Hello,
I need to retrieve info from two tables(a, b) which reside in two different SQL databases.
SELECT a.name, b.address
FROM a, b
WHERE a.id=b.id /* a,b in two DATABASE!!!*/
How to do this?????
Any help will be greatly appreciated!
Dana
Hello;
I have two tables
Table 1 Rx
-------
RxNumber, RxId(no duplicates), RxDate, Name, Notes, Type, DOB
Table 2 Email
-------
EmailId, RxId(duplicates ok), Subject, Message, To, From, Type
I would like the result to be
Result Table
------------
Rx Id, RxDate, Name, Notes(or Subject), Type
Table 1 can be searched by date, or Name or DOB I want the result of this search to go through table 2 and return to me all emails that match thre previous results RxIds
For example Table 1
RxNumber, RxId, RxDate, Name, Notes, Type, DOB
1 99 1/1/2005 Person1 Note1 1 1/1/95
2 98 3/1/2005 Person2 Note2 1 1/1/96
3 97 5/1/2004 Person3 Note3 1 1/1/97
4 96 1/1/2004 Person4 Note4 1 1/1/98
5 95 6/1/2005 Person5 Note5 1 1/1/99
For Example Table 2
EmailId, RxId(dupl ok), Subject, Message, To, From, Type
1 100 S1 M1 T1 F1 2
2 98 S2 M2 T2 F2 2
3 101 S3 M3 T3 F3 2
4 95 S4 M4 T4 F4 2
5 98 S5 M5 T5 F5 2
6 95 S6 M6 T6 F6 2
7 96 S7 M7 T7 F7 2
8 98 S8 M8 T8 F8 2
9 100 S9 M9 T9 F9 2
10 100 S10 M10 T10 F10 2
If I do a search for Rxs that were prescribed from 1/1/2005 to 7/1/2005
I would like the resulting table to be
RxId RxDate Name Note/Subject Type
99 1/1/2005 Person1 Note1 1
98 3/1/2005 Person1 Note2 1
98 3/1/2005 Person2 S2 2
98 3/1/2005 Person2 S5 2
98 3/1/2005 Person2 S8 2
95 6/1/2005 Person5 Note5 1
95 6/1/2005 Person2 S4 2
95 6/1/2005 Person2 S6 2
I have tried a combination of inner joins and Union with no luck any suggestions
Thanks
Fernb200
I have a table that has a column with multiple manufacturer names. Many rows are identical manufacturers. I want to create an asp page that shows only one instance of a particular manufacturer.
Basically if I have 30 entries of Manufacturer 1, 40 entries of Manufacturer 2, etc. I only want to see Manufacturer 1 and Manufacturer 2 once in the display, not as many times as it is listed in the column. Is this an ASP or SQL question?
Basically I know the beginning should look like this:
Code:
SELECT Col1, Col2
FROM dbo.tbl_Myinfo
ORDER BY Col2
But that is about the extent of where I know what to do.
I wanted to create this so I can list the manufacturers that I currently have loaded in my SQL table/ available for more detailed searching.
Hello again!
I got a problem using an OLE-DB Source in my data flow task. It is an Informix-Server using the newest OLE DB-Driver provided by IBM. However, when I set up the OLE DB-Source with connection manager and SQl-Query, I get the following warning message:
Warning at {2B334CA3-A792-4BC2-93AA-12FADCF1E696} [OLE DB Source [45]]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.
Later when I try to execute the package, the destination adapter gets errors like
Error 3 Validation error. Data Flow Task: OLE DB Destination [2985]: Columns "antrag" and "Antrag" cannot convert between unicode and non-unicode string data types. Informix_OLEDB.dtsx 0 0
I guess that SQL Server cannot get the data types from the Source.
Has anyone an idea? Thanks in advance!
I have the master and msdb backup files of a broken server, as well as the .bak files of all other non system databases.
Now, in an online sql server, how do I retrieve the logins information from those .bak files?
(SQL 2005)I'm looking to create a stored procedure to first "select name fromsys.databases where name like '%site'" then pass each name to thefollowing using some kind of loop "USE @name select name fromsys.tables where type = 'U'"I tried a while statement, but the master sys.databases recordsetdoesn't change..It's the loop I can't get to work.Any help is greatly appreciated!
View 3 Replies View Related
hi
i want to retrieve all columns for all tables (for all databases) by this system sp :
Code Snippetexec sp_msforeachtable 'exec sp_columns ''?''' but when i execute this script, sql server return number of empty result set, how to solve this problem ?
thanks.
I want to be able to see when records have been added to a table. The issue is we have a DTS job scheduled to run every night. The developer who wrote it password protected it and doesn't work here anymore. I want to add a step to this series of DTS jobs and want to run it just prior to his job. Is there a way to see when the records are being added or when this job is being run? Thanks again, you guys are the best.
ddave
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.
I just restored my SQL server 2000 database on the SQL server 2005. after this i ran the Service broker sample ("Hello World") on this database by changing the AdventureWorks name to the new database name. The "setup.sql" runs fine. When i run the "SendMessage.sql" i was not getting any rows in the output (The message was not getting inserted into the queue). I checked the Service broker is enabled on this databased using the query "select is_broker_enabled from sys.databases where name = 'newdbname' " It was 1. I even tried the ALTER DATABASE SET ENABLE_BROKER. but it didnt work.
When i tried the sample on a newly created database it worked fine.
Is there any solution to make the restored database to work for service broker.
Thanks
Prashanth
Hi all,
I've inherited the administration of two SQL Servers one 7.0 and the other 2000. We are looking into upgrading to 2005 so I'm gathering all the info I can on the DBs and applications using them.
Now, how can I check if a DB is currently in use? Is there a way to find out when a DB was last accessed?
By the way, I've never done DB administration so you can figure out how desperate I'm getting.
Thanks all for your help,
GS
pratap writes " Dear respected sir,
i need small information regarding how the picture image data can be stored in the database and how it can be made possible. is there for the need for any conversion.
thank you
awaiting for your reply"
Hi, I hope someone can help me with this.
I want to get a query from a database
from between '3' AND '5' (NO PROBLEM)
but I want to add that I want info from 7 and up
how would I write that ? (if I do it in seperate lines the query returns nothing) which is not true.
Thank you.
Well i need to document what tables i have in my stored procedures. Im really new to sql as well. And the problem is if i try code i have or sp_depends for instance it will only tell me the tables that are in that DB so if I have multiple tables from different DB's they are left out. I was told that sp_MSForEachDb for go through each DB and I could try it that way. Any help or example code dumbed down for me would be awesome. This was posted in a previous thread but i though this would be a better explanation
View 3 Replies View RelatedHere's my string. I know it's way wrong right now SELECT binbox_receipt.partner_code ,binbox_receipt.link_id ,binbox_receipt_archive.partner_code,binbox_receipt_archive.link_id FROM binbox_receipt, binbox_receipt_archivewhere binbox_receipt_archive.link_id = binbox_receipt.link_idand binbox_receipt.partner_code = '1154' and binbox_receipt.link_id = '2684'and (binbox_receipt_archive.partner_code = '1154' and binbox_receipt_archive.link_id = '2684')I need to check 2 tables if in the first table the link_id and partner_code exist or the second table link_id and partner_code existany help would be greatly appreciated I'm a little new at this but having fun trying
View 11 Replies View RelatedHi
Iam not sure if the Question is approriate for this forum but here goes....
I have just started playing around with ASP .NET and since i do not have a lot of money i downloaded and installed MSDE as my database. So far i havent been able to find any free tools to access MSDE. Iam looking for GUI tools from where i can create Databases , tables etc... I have experience with MySQL database and am looking for something like MySQLAdmin for MSDE..
any help in this regard is appreciated
Thanks
Punit
Hi All,
I'm using a SQL selection to fill a DataGrid. One of the fields I have is called diagnosis. This field in the database can contain multiple diagnosis. But I use a set of characters to divide each diagnosis.
Example : Sick!@#$%Hurt!@#$%Ill!@#$%
My problem is this is how it looks in my Data Grid. Can someone tell me how to parse out each diagnosis.
Thanks
Hi,
Here i have a question..
Whenever i see my spid in LOCK/PROCESS ID
one command DBCC BUFFERINPUT(MY SPID) always running
and it is showing ''''tempdb.dbo.##lockinfo'''
MODE---- X
Is it pretty normal or something wrong..
i am not running any thing, just simply opened EM..
Pls any suggestions...
thank u
kavira
Hello!
I'm new DBA.
I have triggers on my table in the database have been created one individual a long time ago. Sp_helptrigger stored procedure gave me just a little info about triggers. I would like to see the real code for each trigger, but I don't have idea where to look for it.
Please help me to find it.
Thank you
Anny.
After Sql server has been installed. Is there a way to find out for the mode that it is installed in, ie per processor or per seat?
Thanks
Can anyone point me in the direction of some online resource that explains, in detail, the statistics returned by DBCC SQLPERF?
Thanks
Phill
To all:
I have installed a new Compaq 7000 with NT4.0 sp3 and Sql Server 6.5 sp4 into production about two weeks ago.
Everything went fine until yesterday. Around noon I received 2 Exception_Access_Violation about a second apart
with two differend spids. About 20 mins later I got the Out of connections message and sql server stopped
reponding. I Could not stop sql server and had to reboot the server. I have configured the connections at 600
and generally we average a little over 400. Everything seems to be running fine now but I would like to get some
information from all the great minds out there!
What exectly is Exception_Access_Violation? Can I figure out what caused it or can I debug it? Do you think that the
EAV caused the out of connections error? Any help, insight would help greatly!
Thanks in advanced,
Glen Whitling
Respected Sir,
I am from India. My office has 15 location all over India. I am in corporate office.We have Ms Sql server 6.5. Also all other centers have
have same Ms Sql Server 6.5. We develop a Intranet based application.
In which Ms Sql Server 6.5 is the back end and Netscape browser is the front end.
The centers have their independent database. all also have different data structurre.
But the Head Office needs data from them. How the replication technology help us. and How we we will conect through internet or we haveto have seprate dedicated
leased lines back to centers.
What will be data structure .
Himansu
I want to keep track of some application statistics.
-How many applications per day, month, year
-How many apps for a specific type of widget
-etc.
Is this best accomplished by a script that review the data or by using counters that are updated with every app?
Can someone give me some info on how to get started with this? I did a search, but only found a thread that recommends an article that costs $100.
I want to check who access the database in the sql server. When i go to current activity then process info. I found not all the users who access the database are displayed. Even though i refresh it. It didn't show up. But i am sure some user was accessing the database. Anybody know why, how can i avoid it. Thanks.
View 2 Replies View RelatedHi All,
What is similar to "Schema" and "Table space" in SQL Server?
What is the physical organization/structure of SQL Server?
Thanks in advance,
Jai
Folks, I've inherited a SQL 2K server that has a trace running. I can see the trace file being generated(.trc), I can also see the standard templates(.tdf). But BOL is not helpful in finding information about the
trace when you have no information. I just want to shut the damn thing off.
I can open the trc file in SQL Profiler but I am not proficient in the tool's use. I also don't see a trace table anywere in the database.
If anyone can be of any help it would be much appreciated. Thanks.
I am having problems with my store procedure displaying my last column name which is "DISPLAY_CATEGORY_DESC". I am trying to display the description from another table. But in order to get to the description I have to go through 2 tables. The tables look like something like this.
First TableTABLE_CATEGORY
PRIMARY_KEY = DIR_CATEGORY_ID = 5000
Column Name
DIR_CATEGORY_LVL_ID1 = 100
DIR_CATEGORY_LVL_ID2 = 200
DIR_CATEGORY_LVL_ID3 = 300
Second TableTABLE_CATEGORY_DESCRIPTION
PRIMARY KEY DIR_CATEGORY_LVL_ID1 = 100
Column Name
DESCRIPTION = "Car Model Company"
This is my DISPLAY_CATEGORY_DESC
Not sure how to get to this description. Thanks for your help.
select cwebs.SESSION_ID, webs.ENTERED_DATE, cwebs.DIR_CATEGORY_ID, DISPLAY_CATEGORY_DESC
FROM di_webclickstats cwebs
left join di_websessions webs on cwebs.session_id = webs.session_id
where cwebs.DIR_CATEGORY_ID <> 0 and webs.ENTERED_DATE between @startDate and @endDate+1
I have developed a great intrest in the MS SQL Server recently. I was just wondering if it was worth putting my time into learning this system. In the members opinion would SQL server be technically fit? Does it have a good campacity and flexibility? These areas are quite important to me. I would also like to know how web friendly the server is and its ability to handle complex data.
Any help would be much appriciated.
Hi there,
I have been asked buy my boss to try and solve a problem. We have a access database which has a hell of a lot of entries. We want to use sql server to store this database now.
We want to be able to print reports and analyze the data by running queries etc.
Apart from the obvious sql server software, what would be required to acheive this, would i need a dedicated front end built?
Im just testing water at the moment looking for any info that i might need.
Thanks
Trev
Vikas writes "can any one help me in getting the list of exceptions and there types and how to tackle them."
View 1 Replies View Relatedhow do i get yesterdays records only?
View 20 Replies View RelatedI dont know if this is the right place to post this lets hope. I need to get the total of sales for say marketed.
im trying a script like this but its not doing it right
IIF( Fields!Type.Value = "Non Promotional" then Fields!TotalSales.Value = 0 else Fields!TotalSales.Value)