|
I have a db table column which is of type varchar, but the value stored is date.
eg: 1/23/2005
2/24/2004
3/23/2006 etc.
I want to format this column as follows:
2__1/23/2005
1__1/23/2004
3__2/23/2006
ie, the reformatted value is the sorting index for date. Ie if the column is selected with an order by, the result will be sorted according to the date even though the column type is varchar.
This twisted way is adopted since this column is refered by crystal reports and the report is sorted according to this column.
Any query to format like this.
|
|
|
|
|
Hi,
Try this............
SELECT *
FROM TableName
ORDER BY CONVERT(DATETIME,DateColumn)
Hope this will help u.................:->
Reagrds,
Ritesh
|
|
|
|
|
HI,
I Configured a SQL Server with Merge Replication after few days some tables got some conflicts one table got 10012 conflict Records. Any one there to help me to solve this how to resolve this records or how can I avoid this conflicts?
(That particular table has a Trigger it calculate daily balance)
|
|
|
|
|
When I select all fields in the table, the datetime does not display. Instead it returns a NULL. Why is that? This is the code I used to populate the database.
recordset->Open("SELECT * from mytable", connection.GetInterfacePtr(),
ADODB::adOpenForwardOnly, ADODB::adLockReadOnly, ADODB::adCmdText);
while(!recordset->ADOEOF)
{
_variant_t vtValue;
vtValue = recordset->Fields->GetItem(L"value")->GetValue();
sprintf(fieldValue,"%s",(LPCSTR)(vtValue.bstrVal));
recordset->MoveNext();
};
Thanks!
|
|
|
|
|
A datetime value does not return as a VT_BSTR but as a VT_DATE value. But you can change the type by using vtValue.ChangeType(VT_BSTR) before displaying the value.
Wout Louwers
|
|
|
|
|
|
I have a thread that, every 3 seconds checks a table to see if a record
exists. If it does it retreives it and then deletes the record. is
there a way to use trigger dates in my table to tell me when a record
exists so that i do not have to set a timer to check every three
seconds?
Is there a way to have a SQL table fire an event so that I know if
something has changed and to go check it?
Thanks
Tom Wright
tawright915@yahoo.com
|
|
|
|
|
Tom Wright wrote:
Is there a way to have a SQL table fire an event
You mean like a "trigger"?
You could write a trigger that fires an event of some kind. You can set SQL server up to send emails if you want. Or you could use xp_cmdshell to execute an exe file which could perform the event you want. Be careful of these however as they are open to abuse if you don't think about the security aspect.
Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums
|
|
|
|
|
Use an insert/update trigger on the table.
CREATE TRIGGER TomsTrig
ON TomsTable
FOR INSERT, UPDATE
AS
-- Check if insert/update record matches record you are looking for
-- Do one or more of :
-- 1. Use matching record to insert/update another table
-- 2. Use an existing stored/extended procedure to send a message out (e.g. xp_sendmail, xp_logevent)
-- 3. Write your own stored/extended procedure to send a message out
-- Delete (maybe rollback ?) insert/update of matching record
GO
...cmk
Save the whales - collect the whole set
|
|
|
|
|
The probelm is my app will not reside on the same machine as the SQL server. I was wondering if, since I have a connection open to my SQL database, that it would be possible to send an event to my app telling me that something has changed.
Let me ask this. If at the beginning of my app I read in all the records from the table....connections. And someone uses another app to delete one of these records. How will i know that a record has been deleted. Will I have to do a refresh every so many seconds?
Thanks
Tom Wright
tawright915@yahoo.com
|
|
|
|
|
Yes, that is the scenario i expected.
Before going farther let me state - i am not a DBA. I am a C++ developer who writes a lot of database code (ODBC and OLE DB), but does not know all the ins and outs of SQL Server.
I had the same issue a couple years ago.
I wanted my server application to be aware of any changes made to certain tables by other applications.
What i ended up doing is writing a couple extended procedures for SQL Server.
Given:
Machine S contains my server program.
Machine D contains SQL Server.
The extended procedures are all contained in x.dll which has been installed on D.
My server on S, using ODBC, connects to MSSQL on D after setting up a listening port on S.
It then executes (via ODBC statement) 'xp_cmkInitSvcMsg <@server> <@port> [@handle OUTPUT]'.
The output handle uniquely identifies the MSSQL connection to S.
The triggers on the tables then call 'xp_cmkBroadcastSvcMsg <@msg>' when modified.
This sends @msg to all connected servers (i.e. those that called xp_cmkInitSvcMsg).
Just before the server on S disconnects from MSSQL on D it executes 'xp_cmkTermSvcMsg <@handle>'.
This just closes the socket connection from MSSSQL to S and cleans up the state.
As long as the server is connected it gets messages from MSSQL on its listening port.
It also allows more than one program to get change messages from MSSQL at the same time.
It's a bit of a round about way of doing things, but i needed a solution and this was the best i came up with.
...cmk
Save the whales - collect the whole set
|
|
|
|
|
I thought it would be this difficult. I've never called a sp on SQL2000, can you shoe me a code snippet on how to do this. Also where can I find info on how to create a sp like the one you show...or any that return something back to the calling app. I'm going to assume that they are all the same.
Thanks
Tom Wright
tawright915@yahoo.com
|
|
|
|
|
|
Awesome. Thanks for the help.
Tom Wright
tawright915@yahoo.com
|
|
|
|
|
Hi Firends,
I am trying to access remote Sqlserver instance using program, Can anyone tell me how to achieve this.
I am having difficulty because My machine is having more that one instance of MSDE/Sqlserver instance how to provide exact information of the Sqlserver instance to which to connect.
Please anyone tell me.
Thanks n Regards,
RuchirDhar Dwivedi
Software Engineer
Windowmaker Software Pvt.Ltd.
Baroda, India.
|
|
|
|
|
If you have more than one instance, you need to specify the machine and instance name to connect.
i.e. instead of connecting to "server_name", you would connect to "server_name\instance_name".
Database FAQ
|
|
|
|
|
Hi,
Since the server is on the remote machine in that case How to do this,
Let me give an example suppose my server name is Foo and the instance name is Bar, IP address of the remote sever is ppp.qqq.rrr.sss
Can anyone tell me what would be the connection string.
RuchirDhar Dwivedi
Software Engineer
Windowmaker Software Pvt.Ltd.
Baroda, India.
|
|
|
|
|
|
Hi,
I have gone through that site but
To connect to SQL Server running on a remote computer (via an IP address)
oConn.Open "Provider=sqloledb;" & _
"Network Library=DBMSSOCN;" & _
"Data Source=xxx.xxx.xxx.xxx,1433;" & _
"Initial Catalog=myDatabaseName;" & _
"User ID=myUsername;" & _
"Password=myPassword"
in this example it doesn't provide any information about what to do in the case where I have a named instance.
Plz help??
RuchirDhar Dwivedi
Software Engineer
Windowmaker Software Pvt.Ltd.
Baroda, India.
|
|
|
|
|
I have n databases, each one has the same tables,Sure same tables in different databases has different values. I want to join them in a just one table in dataset. For example, i have 2 databases MB0001 and MB0002 , also a table named IntegratedChequeAccounts in two . I want to join two to one.
SELECT * FROM mb0001.DBO.IntegratedChequeAccounts
, Mb0002.DBO.IntegratedChequeAccounts
In this sample IntegratedChequeAccounts tables has 3 columns and each of them has 2 row. after running "select", i get 6columnsX4row as a table. But i desire 3columnsX4rows.
Any Idea to successed in.
|
|
|
|
|
|
Thanks for answer but i dont know why it is not working in my way.
I have a table named "DebitsAndCredits" which has fallowing columns
AccountNumber char(12)
DebitCredit bit
Amount money
I have the same table in two databases which are MB0001 and MB0002
I would like to join them in a one result set.I write
SELECT DISTINCT (AccountNumber ) ,
(SELECT SUM(Amount ) FROM DebitsAndCredits m
WHERE m.DebitCredit =0 AND m.AccountNumber =f.AccountNumber ) as Credit,,
(SELECT SUM(Amount ) FROM DebitsAndCredits m
WHERE m.DebitCredit =1 AND m.AccountNumber =f.AccountNumber ) as Debit
FROM DebitsAndCreditsf Order BY AccountNumber
UNION
SELECT DISTINCT (AccountNumber ) ,
(SELECT SUM(Amount ) FROM DebitsAndCredits m
WHERE m.DebitCredit =0 AND m.AccountNumber =f.AccountNumber ) as Credit,
(SELECT SUM(Amount ) FROM DebitsAndCredits m
WHERE m.DebitCredit =1 AND m.AccountNumber =f.AccountNumber ) as Debit
FROM DebitsAndCreditsf Order BY AccountNumber
What do u offer for me?
|
|
|
|
|
Try this:
SELECT AccountNumber,
SUM(CASE DebitCredit=0 THEN Amount ELSE 0 END) as Credit,
SUM(CASE DebitCredit=1 THEN Amount ELSE 0 END) as Debit
FROM MB0001..DebitsAndCredits
ORDER BY AccountNumber
GROUP BY AccountNumber
UNION
SELECT AccountNumber,
SUM(CASE DebitCredit=0 THEN Amount ELSE 0 END) as Credit,
SUM(CASE DebitCredit=1 THEN Amount ELSE 0 END) as Debit
FROM MB0002..DebitsAndCredits
GROUP BY AccountNumber
ORDER BY AccountNumber
Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums
|
|
|
|
|
Hi all,
I have a problem for data transactions across two databases.
That is one object's data is comprised of two tables from
two different databases. I can perform rollback and commit within
one database. But for this case, how can I perform rollback and commit
across two databases(for two different SQL connections) for the object ?
Thanks
|
|
|
|
|
You need to use a two phase commit mechanism. What database are you using?
Database FAQ
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
