|
SELECT str(datepart('d',now()))+'-'+str(datepart('m',now()))+'-'+ str(datepart('yyyy',now()))
This worked for me. You can try format function as well.
|
|
|
|
|
ello,
I have a database that will need to be modified, i.e. adding columns, stored procedures, etc.
I can't create a backup as the database is live and will be continue to be updated with customer data.
My colleges what me to create the updates in code which I have done. However, my real problem is how do I know if the database has been updated and is current version.
I will not be updating the data that has been entered, only doing things like:
Alter [tableName] add Salary money null
create procedure dbo.insertPayment .....
example
if(currentVersion) then
'No need to update
Else
'Updates are available
'Add column
'Drop column
'create procedure
'etc
Is there any method that I could use to detect if the database is current or not.
I hope I have been clear on this.
Using VS 2005 & SQL Server 2005
Many thanks in advance,
Steve
|
|
|
|
|
steve_rm wrote: Is there any method that I could use to detect if the database is current or not.
That really depends on your idea of "current".
Before each ALTER statement I'd have an
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTableName'
AND COLUMN_NAME = 'ColumnName')
BEGIN
-- Add the column we checked for
ALTER TABLE ....
END
(The above code is for SQL Server 2000, there will be a similar equivalent in SQL Server 2005, but I don't know what it is)
You may also like to take a copy of your live database and practice your scripts on the copy first.
|
|
|
|
|
Hi,
I've a problem when using Carlos Antollini ADO class in a "realtime" application. What I do
is, that I call a thread that does all the database operations once every 10 minutes
or so. But it seems like the database operations are not called asyncron, because
the other threads within the application get freezed!?
Would be great if you could give advice, otherwise I've to rewrite the whole DB part 
Thanks
Mario
m_hThread = (HANDLE) _beginthreadex(NULL,0,_ThreadWriteToDB,this, 0,&nDummy);
UINT WINAPI CDBManager::_ThreadWriteToDB(LPVOID pParam)
{
ASSERT(pParam);
CDBManager *pDBMan = reinterpret_cast<cdbmanager *=""> (pParam);
DosSetPriority(PRTYS_THREAD, PRTYC_IDLETIME, THREAD_PRIORITY_IDLE, 0);
pDBMan->m_Con.BeginTransaction();
...
...
...
pDBMan->m_Con.CommitTransaction();
// this thread has finished its work so we close the handle
CloseHandle(pDBMan->m_hThread);
}
|
|
|
|
|
hai,
in my application i want to create one trigger.
the situation is i 've two tables T1 and T2.
when i update T1 i want to insert the data before updated ie old values to table T2.
i used one trigger with 'instead of', but the pblm is T1 is not updated but values inserted to T2 perfromed fine.
any help is appreciated!!
All I ever wanted is what others have....
CrazySanker
|
|
|
|
|
crazysanker wrote: i used one trigger with 'instead of', but the pblm is T1 is not updated but values inserted to T2 perfromed fine.
Try a regular trigger, and not an instead of trigger.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
can u explain something in detail
All I ever wanted is what others have....
CrazySanker
|
|
|
|
|
If you do not explicitly insert the values into the table within an Instead Of trigger, they will not be inserted. You have it working where you put the old values into your T2 table...but the new values aren't being put into the T1 table. This is because you are using an Instead Of trigger, and you aren't inserting the inserted table values into T1. You could continue to use an Instead Of trigger (which doesn't make much sense), but you would need to do something like this:
INSERT INTO T1
SELECT Field1,Field2,Field3
FROM inserted
However, you could just use a regular trigger, and NOT an Instead Of trigger. Rather than this:
CREATE TRIGGER Trig_InsteadOfINS_T1 ON T1
INSTEAD OF INSERT
AS
--etc, etc.
Do this:
CREATE TRIGGER Trig_INS_T1 ON T1
AS
--etc, etc.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
I am following this article to import data from CSV file into my database table:
http://www.codeproject.com/aspnet/ImportExportCSV.asp[^]
The problem I am facing is that one of my table contains a money data-type column. Therefore, my application gives me Conversion error.
When I try to import data from csv file into this table, I get:
Disallowed implicit conversion from data type varchar to data type money, table 'MyTable', column 'Total'. Use the CONVERT function to run this query.
The above article that I am following, puts all the data values from the csv file into quotes, such as: Insert into MyTable...... Values ('value 1','23.34'...). Thus SQL cannot convert 23.34 into money.
Any way to solve this problem?
Thanks
|
|
|
|
|
If you are having a problem with a specific article, you should contact the article author. However, from the way you've described the problem the article isn't all that great because it looks like there is the opportunity for SQL Injection Attacks to take place via the CSV file.
|
|
|
|
|
Hi friends,
I am working in .net 2005 and sql server 2000 use as back end.
My problem is how to insert jpeg file into database and how to retrieve it into .net form.
|
|
|
|
|
Store it into an image column. Information sent back and forth will be using byte arrays. You can obtain a byte array representation of an image from the Bitmap class, you can also instantiate an instance of a bitmap class using a byte array.
|
|
|
|
|
Hi All
What is actual use of inserted and deleted temporary system table in triggers in sql server2000?
what is the importance of using it?
Is there any kind of special situation where we need to use inserted and deleted temporary system table in sql server?
Thanks
monika
|
|
|
|
|
|
What is actual use of inserted and deleted temporary system table in triggers in sql server2000?
In trigger fired, you have to know what changes were made as part of the data modification. You can find this information in the inserted and deleted tables. For the AFTER trigger, these tables are actually views of the rows in the transaction log that were modified by the statement.
what is the importance of using it?
In case of Database Rollbacking....!!
Cheer
Pavan Pareta
|
|
|
|
|
Suppose i have a data table with 1 lakh records.My table have 25 columns. i want to search data table based on 10 columns. I may or may not provide input for 10 columns but i have to built query. How can i built query.
|
|
|
|
|
hi
you can also do this type
SELECT Column_1, Column_2, Column_3, Column_4, Column_5, Column_6, Column_7, Column_8, Column_9, Column_10 FROM tblName
Try
Pavan Pareta
|
|
|
|
|
can somebody throw some light on what data ware housing is.
|
|
|
|
|
|
Hi,
I am having problems logging into my reportserver on our 64bit sql server 2005, which runs on Windows 2003 server. It works 100%on the test machine, which is a 32bit normal WinXP pc, with sql server 2005 enterprise.
I noticed that if I browse to the reports (localhost/reports) on the 64bit machine via IE, I cant open the page - get "page cannot be displayed" error. I also get an error in the event log that says "Login failed for user 'NT AUTHORITY\SYSTEM'. [CLIENT: <local machine="">]". My Reporting Services Configuration tool says everything is ok, and working.
I am very new to reporting services, and dont know where to start debugging, or look for help.
|
|
|
|
|
Hi,
How can i concatenate two rows together? I have a table with id and three columns. I want to display all the colunms with same id in a row.
Original Table:
id column1 column2 column3
1 a b c
1 d e f
2 g h i
2 k l m
Desired Result:
id column1 column2 column3 column4 column5 column6
1 a b c d e f
2 g h i k l m
So rows are shifted up where ID is same. Is it possible to get this result through query? I'll very much appreciate any help.
Thanks
|
|
|
|
|
nobody is caring about this poor guys.
|
|
|
|
|
Maybe someone else has a better idea, but I can't think of a pretty way to do this. If the data is ALWAYS in two records, you could try something like:
select ids.id,
(select top 1 column1 from mytesttable t2 where t2.id = IDs.id order by column1) as column1,
(select top 1 column2 from mytesttable t2 where t2.id = IDs.id order by column1) as column2,
(select top 1 column3 from mytesttable t2 where t2.id = IDs.id order by column1) as column3,
(select top 1 column1 from mytesttable t2 where t2.id = IDs.id order by column1 desc) as column4,
(select top 1 column2 from mytesttable t2 where t2.id = IDs.id order by column1 desc) as column5,
(select top 1 column3 from mytesttable t2 where t2.id = IDs.id order by column1 desc) as column6
from (select distinct id from mytesttable) as IDs
Any other solution would probably involve a cursor and/or temporary table.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Thank you for your solution. I have over 100 colunms in some of my tables so i guess using cursor and temp table would be the choice. Anyway, thanks for the idea.
|
|
|
|
|
Hi.
I use sql parameters every time i connect to a database, so it's about time i know the truth 
Does SQL parameters block SQL injections 100%? and how is parameters different from normal SQL strings? (In the way it works)
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
