|
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
|
|
|
|
|
Thats a very funny error.
Just change "DECLARE @Filename NVARCHAR(MAX)" to VARCHAR(MAX). It seems that this functions
doesn't support unicode or something else.
Greetings
Covean
|
|
|
|
|
Thanks for your reply. Unfortunately, I made the change with the same result. I have to say that this one really has me stumped. I'm using the sp_executesql way, but I would love to know why the command way isn't working.
|
|
|
|
|
Hi,
I'm considering using MySQL in my next project so can you please tell me:
1. What is the lightest and most simple version to install and where to get it as I'm using it as a replacement for access to be accessed from a C# application.
2. How can I install it and configure it silently "like including a pre-configured response file in Oracle"
Thank you.
|
|
|
|
|
Ok, I found the answers in the MySQL forum but I still need an advice from you old users of MySQL as this will be my first shot and I need to make it right so please tell me what would you consider for my case. "A light stand alone database only accessed through one application".
Thank you.
|
|
|
|
|
Any version of MySQL is not heavy. I don't know how to silently install and configure it, though. MySQL is usually used in a multi-client environment. From your description you want to get access to it from just one application. And you want to install an instance of MySQL with each of your application. This may be overkill.
There is another option for you, which is SQLite. If you don't have problem with the restrictions of some of the data types in SQLite, you can try it instead of using MySQL. It's equally easy to program using C# to get access to SQLite, and the setup can be completely silent.
|
|
|
|
|
|
I actually had a similar situation on a project I'm working on. I think SQLite limits the database to 2GB or so, and that was too restrictive for us, so we went with MySQL.
As for the silent install/config, you can run the .msi installer with the "-s" option, and then run the MySQLInstanceConfig.exe utility to set up your instance. This can be called from the command line with a set of switches for your particular setup (see here[^] for the list of parameters). I just put all the commands in a small console utility that runs as a custom action in our installer.
Dybs
The shout of progress is not "Eureka!" it's "Strange... that's not what i expected". - peterchen
|
|
|
|
|
That was quite informative.. Thanks mate!
|
|
|
|
|
The information you offered here is quite useful to me. I was not aware of the 2GB size limit of SQLite databases. I will sure check that out.
The way to silently install MySQL is also very helpful.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
