|
Try following query hope it will work for you
select tablesA.AID AID,FileName,tablesA.date date,status
from tablesA
inner join
(
SELECT FileName,BID,AID from tablesB where bid in
(select max(bid) from tablesB group by aid)
) as b on tablesA.AID = b.AID
inner join
tablesC on b.BID = tablesC.BID
|
|
|
|
|
It works....
Great
Thanks for your Help
|
|
|
|
|
|
How to write a MS_SQL query to populate a column from another column.
I have a table:
name col_1 col_2
name1 null value1
name2 null value2
The result should be as follows:
name col_1 col_2
name1 value1 value1
name2 value2 value2
|
|
|
|
|
never mind.
update mytable set tbl.col_1 = tbl_col2 where col_1 is null
|
|
|
|
|
Hi!
I'm using just .NET Framework 1 and Visual Studio 2003.
Will I have any problem if I install .NET Framework 2.0 or 3? Some controls will stop working? Will I have access to new controls / methods while programming, or It will onley install runtime libraries? I.E: Will I be able to use BindigSource / BindingNavigator, or I have to keep on CurrencyManager?
Thanks!
|
|
|
|
|
I think you will probably have a better chance of getting a good answer to this question if you post it in the .NET Framework forum.
|
|
|
|
|
Hi,
1.
wrong forum, this is not a DB question.
2.
the .NET versions can live together, no worries at all.
3.
the corresponding Visual Studio versions can live together too; the one you install last will dictate the file associations.
4.
never versions of Visual Studio will convert your project files when opening them, making them no longer readable by older versions.
5.
Why would anyone still use .NET 1.0 or 1.1?
2.0 has fixed a lot and added very useful stuff; 3.0 and 3.5 have added a lot more, not always very useful, functionality. And 4.0 will emerge pretty soon.
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that. [The QA section does it automatically now, I hope we soon get it on regular forums as well]
|
|
|
|
|
I want to know how can I calculate the person's age from a person_birthday datetime field to show the year and month(if any) so if the age is exactly 25 then it should be 25y or if it's less than a year it will be 4m otherwise 25y 2m?
|
|
|
|
|
Use DateDiff(yyyy,birhtday,getdate()) to get the difference, the rest is just an excercise in maths and logic. DATEDIFF is the tool you need.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
try this
select cast(DATEDIFF ( month , '1999-10-26 12:30:02.960' , getdate()) /12 as varchar(10))+ ' Y '
+
cast (((DATEDIFF ( month , '1999-10-26 12:30:02.960' , getdate())) - (DATEDIFF ( month , '1999-10-26 12:30:02.960' , getdate()) /12) ) as varchar(10)) + 'M'
and for your scenario you also have to use case when
|
|
|
|
|
FINAL SOLUTION
select
case
WHEN (DATEDIFF ( month , '1999-10-26 12:30:02.960' , getdate()) /12 ) = 300
THEN (cast(DATEDIFF ( month , '1999-10-26 12:30:02.960' , getdate()) /12 as varchar(10))+ ' Y ')
WHEN (DATEDIFF ( month , '1999-10-26 12:30:02.960' , getdate()) /12 ) > 300
THEN (cast(DATEDIFF ( month , '1999-10-26 12:30:02.960' , getdate()) /12 as varchar(10))+ ' Y '
+
cast (((DATEDIFF ( month , '1999-10-26 12:30:02.960' , getdate())) - ((DATEDIFF ( month , '1999-10-26 12:30:02.960' , getdate()) /12))*12 ) as varchar(10)) + 'M')
WHEN (DATEDIFF ( month , '1999-10-26 12:30:02.960' , getdate()) /12 ) < 300
THEN (cast(DATEDIFF ( month , '1999-10-26 12:30:02.960' , getdate()) /12 as varchar(10))+ ' M ')
END
|
|
|
|
|
Hi,
To get the unique keys from a given table, i use a sql statement like this: Select [COLUMN_NAME] as FieldName, [CONSTRAINT_NAME] as KeyName, [ORDINAL_POSITION] as Ordinal FROM [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] WHERE TABLE_SCHEMA=@schema AND TABLE_NAME=@table AND CONSTRAINT_NAME LIKE "IX_%" ORDER BY [CONSTRAINT_NAME], [ORDINAL_POSITION]
First of all, it waorks fine, but I don't like the idea of selecting it by the name, but it was the only solution I found... For now it works, so if it's the only way, I'll live with it 
My main problem is that I didn't find any [INFORMATION_SCHEMA] table where the NonUnique keys are stored, but it has to be somewhere, right? Any ideas about it?
Thanks,
Dirso
|
|
|
|
|
I use the following against the sysobjects table (it predates the views) it is designed the get the IDENTITY column which for me is always the primary key for a table. It may help!
select syscolumns.name from syscolumns INNER JOIN sysobjects on syscolumns.ID = sysobjects.ID
where sysobjects.name = 'TableName' and colstat & 1 = 1
Fixed that univote - this is a valid concern and you are right in not wanting to rely on a constrain name.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Thank you both for your suggestions. I'll try it out in afew days and if I find a solution, I'll post it here.
Thanks,
Dirso
|
|
|
|
|
I have a SQL Server 2000 server that I have been accessing happily via SQL Server Management Studio (the 2005 version 9.00.3042.00) for ~16 months. Last week I started getting this error message:
"Cannot connect to <<server name="">>.
Additional information:
A connection was successfully established with the server, but then an error occured during the login process. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) (Microsoft SQL Server, Error: 10054)"
Some important notes:
- I'm a SQL Server admin on the box .. and my rights still exist
- SSMS = SQL Server Management Studio
Here is what I've tried:
- accessing from my PC using Windows Authentication = error message thrown
- accessing from another box (remote desktop'd onto another server) using SSMS using Windows Authentication = error message thrown
- accessing SQL Server 2005 databases (on a different server) from my PC using Windows Authentication = successful connection
- accessing from my PC using SQL Server Authentication = successful connection
- remote desktop onto the server in question, then open Enterprise Manager and access a database = successful connection
- accessing from another PC as someone else via WIndows Authentication = successful connection
As far as I know the only thing to change on my PC is that I installed a MS patch (KB972270). This patch is also on the other PC that works for other people so I'm guessing that it isn't the problem ...
Any ideas would be gratefully accepted ...
Thanks!
[edit] Seems some tinkering in the corporate firewall was the culprit - all fixed now.
|
|
|
|
|
Thank you for the update.
|
|
|
|
|
Imagine beating all your competition and flooding your site with tons of free targeted traffic! Hardly anyone knows about this incredible secret free tool, so watch this free video before your competition does, and in just 6 minutes, you'll be in the top spot on Google - and never spend a penny to do it - no catch - no gimmick. http://www.clicknearn.net/3189-78.html
|
|
|
|
|
Hi,
Will adding a foreign key column to an existing table with data, by using alter table command
affect the existing data in the table?
|
|
|
|
|
you wont be able to create the key if any record violate that foriegn key constraint. you would probably have to clean the data [ if any violates] before being able to create the constraint
|
|
|
|
|
adding a foreign key will not conflict with existing data but it would help in data integrity.
am only concerned if it will result in any loss of data already existing. I know that it should not but since i've never done this before on a table in production environment with data just needed to be doubly sure that it results in no data loss.
|
|
|
|
|
You may not want to do this in production environment. Try to duplicate the data in development environment and test it before doing it in a live production environment.
I want to say it will not cause data loss, but I still suggest you to do it in development environment. We had mishaps on production environment before, even though we believed everything should be okay. When that happens, it may keep you in your office for a few days without any sleep.
|
|
|
|
|
Many Thanks all. Done successfully
|
|
|
|
|
I'm trying to do a SQL Server script that will backup a database to a file.
DECLARE @Filename NVARCHAR(MAX)
SELECT @Filename = N'\\{mymachinenameonnetwork}\SQL Backups\mydb_backup_' + convert(varchar, getdate(), 102) + '_' + replace(convert(varchar, getdate(), 8),':','')
SELECT @Filename
BACKUP DATABASE [mydb] TO DISK = @Filename WITH NOFORMAT, NOINIT, NAME = N'mydb-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
This will not produce a backup file. But, if I create a variable of the entire statement and run it via sp_executesql, it works perfectly.
SELECT @Cmd = 'BACKUP DATABASE [mydb] TO DISK = ''' + @Filename + ''' WITH NOFORMAT, NOINIT, NAME = N''mydb-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
exec sp_executesql @stmt=@Cmd
There must be some reason that I'm not seeing. Can anyone shed some light on this problem?
Thanks,
Ed
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
