|
Hi Guys ,
Is there any script that i can run on the database server so that i can retreive all the Computers(IP addresses) that tried connect to the database?
Please advice.
Many thanks,
Regards,
kibrom
|
|
|
|
|
I think you can't retrieve client IP's trying to connect to your DB.You could query net_address from sys.sysprocesses which is unique identifier for the network adapter on the workstation of each user ,but how to refer it to user IP I don't know.
|
|
|
|
|
Hi all,
how to check the existance of user name from Sql server with case sensitivity.
|
|
|
|
|
|
I would like to change my default port 1433 for remote connection to some another port? What port number can give as another port? Is there any rule? How to change the default port?
Iam using sql server 2008.
Any Ideas and Guidences?
Thanks
|
|
|
|
|
From SQL Configuration Manager, Just need to change the tcp/ip port number as anyone new number.
Let it be useful to someone like me.
|
|
|
|
|
I Want to display more than one rows data in just one row.
My Data is:
Code Name
50141 abc
50141 pqr
I want Following Data in result.
Code name
50141 abc,pqr
Please Give me a solution.
Thanks & Regards,
Fazal
|
|
|
|
|
Create Function to return the all names(seperated by comma) for a given code.
Create FUNCTION [dbo].[GetNamesById]
(
@code int
)
RETURNS varchar(max)
AS
BEGIN
declare @result varchar(max)
select @result = COALESCE(@result + ',', '') + [name]
from tblCode
where code = @code
return @result
END
GO
now use following query:
select code,dbo.GetNamesById(code) from tblCode group by code
above query will give output as:
Code name
50141 abc,pqr
|
|
|
|
|
I have two tables that share two columns:
Table 1:
Name: EMR.mmPatientAdmissionSymptoms
Columns: PatientID, AdmittanceID,SymptomID
Table 2:
Name: EMR.mmPatientAdmissionSymptomsOther
Columns: PatientID, AdmittanceID,Symptom
EMR.mmPatientAdmissionSymptoms.SymptomID is an integer whilst EMR.mmPatientAdmissionSymptomsOther.Symptom is nvarchar(MAX).
Now, I want to pull all the data in both tables, where the data in either table not existing being null. That should be a straight full outer join, right?
SELECT
pas.PatientID, pas.AdmittanceID, pas.SymptomID, paso.Symptom
FROM EMR.mmPatientAdmissionSymptoms as pas full outer join
EMR.mmPatientAdmissionSymptomsOther as paso
on pas.PatientID = paso.PatientID
AND pas.AdmittanceID = paso.AdmittanceID
But it doesn't; It seems to preform a cross join. Out of frustration I tried left, right, cross and inner joins just to see if I'm on the right track or not. They ALL give the same result
Scripts if you want to try this: (altered slightly so you don't have to remove the FK and Schemas)
-- Table 1
CREATE TABLE [mmPatientAdmissionSymptoms](
[PatientID] [char](10) NOT NULL,
[AdmittanceID] [int] NOT NULL,
[SymptomID] [int] NOT NULL,
CONSTRAINT [PK_mmPatientAdmissionSymptoms] PRIMARY KEY CLUSTERED
(
[PatientID] ASC,
[AdmittanceID] ASC,
[SymptomID] ASC
)
--Table 2
CREATE TABLE [EMR].[mmPatientAdmissionSymptomsOther](
[PatientID] [char](10) NOT NULL,
[AdmittanceID] [int] NOT NULL,
[ID] [int] IDENTITY(1,1) NOT NULL,
[Symptom] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_mmPatientAdmissionSymptomsOther] PRIMARY KEY CLUSTERED
(
[PatientID] ASC,
[AdmittanceID] ASC,
[ID] ASC
)
--ID is an Identity field so that I can differentiate between one entry and the other for each patient at each admittance
Results are returned as:
PatientID AdmittanceID SymptomID Symptom
============================================================
1234 1 1 This is a symptom
1234 1 3 This is a symptom
Table 1:
PatientID AdmittanceID SymptomID
==============================================
1234 1 1
1234 1 3
Table 2:
PatientID AdmittanceID ID Symptom
====================================================================
1234 1 1 This is a symptom
One final note, there are no FK relations between the two tables
If the post was helpful, please vote, eh!
Current activities:
Book: Devils by Fyodor Dostoyevsky
Project: Hospital Automation, final stage
Learning: Image analysis, LINQ
Now and forever, defiant to the end.
What is Multiple Sclerosis[ ^]?
|
|
|
|
|
Are you wanting to relate/compare the tables or are you in the end trying to merge the tables?
|
|
|
|
|
More like merge them but with the repeated columns removed (PatientID & AdmittanceID).
In a way, I'd want results of the query to be as such: (based on the tables given in the OP)
(Table1) (Table1) (Table1) (Table2)
PatientID AdmittanceID SymptomID Symptom
============================================================
1234 1 1 NULL
1234 1 3 NULL
1234 1 NULL This is a symptom
Makes sense?
[Edit] I just realized that the tables aren't very clear. Try this:
Table 1
| PatientID | AdmittanceID | SymptomID | | 1234 | 1 | 1 | | 1234 | 1 | 3 |
Table 2
| PatientID | AdmittanceID | ID | Symptom | | 1234 | 1 | 1 | This is a symptom |
Wanted result
| PatientID | AdmittanceID | SymptomID | Symptom | | 1234 | 1 | 1 | NULL | | 1234 | 1 | 3 | NULL | | 1234 | 1 | NULL | This is a symptom |
What I get:
| PatientID | AdmittanceID | SymptomID | Symptom | | 1234 | 1 | 1 | This is a symptom | | 1234 | 1 | 3 | This is a symptom |
If the post was helpful, please vote, eh!
Current activities:
Book: Devils by Fyodor Dostoyevsky
Project: Hospital Automation, final stage
Learning: Image analysis, LINQ
Now and forever, defiant to the end.
What is Multiple Sclerosis[ ^]?
|
|
|
|
|
Would a union not make more sense than a join for this scenario?
|
|
|
|
|
I was sorta pressed for time so I physically merged the two tables on the database but I'll be certain to try it again next time!
Thanks 
If the post was helpful, please vote, eh!
Current activities:
Book: Devils by Fyodor Dostoyevsky
Project: Hospital Automation, final stage
Learning: Image analysis, LINQ
Now and forever, defiant to the end.
What is Multiple Sclerosis[ ^]?
|
|
|
|
|
You can't use an outer join in this scenario because outer joins return nulls where there is not a match in the other table; You have matches.
A union query would be more appropriate in this case.
|
|
|
|
|
I was sorta pressed for time so I physically merged the two tables on the database but I'll be certain to try it again next time!
Thanks for the explanation though. But I thought Outer joins will return nulls because there was no corresponding columns that had a value (of course, now that I type this I remember that all this is row based).
If the post was helpful, please vote, eh!
Current activities:
Book: Devils by Fyodor Dostoyevsky
Project: Hospital Automation, final stage
Learning: Image analysis, LINQ
Now and forever, defiant to the end.
What is Multiple Sclerosis[ ^]?
|
|
|
|
|
|
hi i have a table with following schema
MsgID SystemID Msg Date(YYYY-MM-DD)
1 1 "" 2009/01/02
2 1 "" 2009/04/03
3 1 "" 2009/05/02
4 2 "" 2009/01/01
I want to identify the SystemIDs which has messages (column : Msg) generated with a Time difference greater than 3 months
In the given table its systemID "1" . ( logged first message on 2009/01/02 and after 3 months 2009/04/03).
please help to get a proper query
My small attempt...
|
|
|
|
|
And what have you tried so far, or are you expecting someone to write the query for you. Some hints.
Select top #n
datetime
dateadd
datediff
row_number() partition over
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
don't understand.
plz send codez ASAP to lazy@yahoo.in
Luc Pattyn [Forum Guidelines] [My Articles]
The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Show formatted code inside PRE tags, and give clear symptoms when describing a problem.
|
|
|
|
|
In one of the project they used System.Data.OleDb for connecting the SQL Server 2000 database. How does it differ from using System.Data.SqlClient.
Regards,
John.L.Ponratnam
|
|
|
|
|
Hi,
System.Data.SqlClient can only be used for SQL server. System.Data.OleDb is built to access a wide variety of databases as it is build on top of ODBC (for details look here: http://en.wikipedia.org/wiki/OLE_DB[^]).
I would suggest using the more common approach (ODBC or OleDB) as it gives you the opportunity to change your database provider without too much effects on your code.
Regards
Sebastian
|
|
|
|
|
hi guys ,
actually i replaced my ldf and mdf files accidently and
now my database has changed , but i want it to restore to previous
ldf and mdf files , what to do ? i replaced files from the path
Tauseef A Khan
MCP Dotnet framework 2.0.
|
|
|
|
|
Hi,
Do you have any backup files of your database? Don't know if your recycle bin contains a copy of the old file (does this work when replacing files?).
Regards
Sebastian
|
|
|
|
|
Hi Guys,
I have created a program that exports data from our old system to a .sql file that drops/creates and inserts, which works fine, the only problem I have is automating.
How do you execute a SQL file from a DTS package?
Obviuosly the file changes on a daily basis, but the filename can remain the same.
Please help and thanks in advance,
Paul.
|
|
|
|
|
Paul Hayman wrote: How do you execute a SQL file from a DTS package?
that's a big ask in a forum message, you may find some tutorial and articles on Google [^]though
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
