|
Oops, the following is simpler:
<br />
SELECT min(ID), Status, 'N'<br />
FROM myTable<br />
WHERE Status in (select distinct Status from myTable where Deleted = 'N')<br />
GROUP BY Status<br />
<br />
UNION <br />
<br />
SELECT min(ID), Status, 'Y'<br />
FROM myTable<br />
WHERE not Status in (select distinct Status from myTable where Deleted = 'N')<br />
GROUP BY Status;<br />
My articles and software tools
|
|
|
|
|
Thank you very much for helping in this regard.
with regards
irsh
|
|
|
|
|
Thank you very much for helping in this regard.
with regards
irsh
|
|
|
|
|
Thank you very much for helping in this regard.
with regards
irsh
|
|
|
|
|
Does MS SQL Server support dates in 'mmyy' format?
If so, how??
|
|
|
|
|
Left(Convert(char(6),getdate(),12),4)
michanne
|
|
|
|
|
Thanks...
But i want the data to retain datetime datatype, so that i can apply datetime functions to the data.
|
|
|
|
|
Hi,
I have an ACCESS DB populate by an automation.
I am looking for reading this database and populate it in a sql database.
For the first time, when I reading the ACCESS DATABASE, I using DTS WIZARD in enterprise Manager for create the same ACCES DB in an SQL DATASE.(I would like to use a STORPROC for this).
After I would like to use TRIGGER or SQL AGENT, ... for detecting that new records were inserted in the ACCESS DB.
And populate new records to the appropriated TABLE in SQL DB.
If you have any suggestions for my question it is great.
youssef
|
|
|
|
|
I'm not sure I quite understand what you are trying to achieve. Why are you continuing to have an Access database? Why not migrate it all to SQL Server and leave it in there? If you need to access the data in Access then why not link the tables back from SQL Server?
"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar
The Second EuroCPian Event will be in Brussels on the 4th of September
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
My Blog
|
|
|
|
|
Thanks for your answer.
I can't change the DB because it isn't my Automation and the customer wont migrate to SQL Server (probably for license).
So, I used Linked servers in Entreprise Manager.
But How can I do for reading each table with a storproc and how can I do for create a Linked servers by storproc because the filename of the log change each day ?
youssef
|
|
|
|
|
youssef wrote:
the customer wont migrate to SQL Server (probably for license)
cough, MSDE, cough cough....
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
So, I find all of my questions.
Now I would like to use a TRIGGER for populate with my Linked server (ACCESS) my Table in SQL.
How can I do or is it possible ?
youssef
|
|
|
|
|
Hi,
I have an ACCESS DB (.mdb) generate by an Automation.
Now, I would like to:
1/ Copy all datas from the ACCESS DB in my SQL Database.
I using DTS. All are working.
But, after copying, I would like at each time a new record is inserted in the ACCESS DB, I copy this Record in my SQL DATABASE (synchronize the SQL DATABASE).
For this, I create LINKED SERVERS for the ACCESS DB (Named LOG).
In my SQL DATABASE, I create a VIEW that read one TABLE from LOG (linked ACCESS DB).
Now, I would like to create a TRIGGER from this VIEW for retrieve each INSERT and copy this insert in my SQL DATABASE.
Please find the CODE below :
CREATE TRIGGER trgLog on [dbo].[VIEW_LOG]
INSTEAD OF INSERT
AS
BEGIN
declare
@Log_Date datetime,
@Log_Input smallint,
@Log_Level smallint,
@Log_Milliseconds smallint,
@Log_Output smallint,
@Log_Panel smallint,
@Log_Response smallint,
@Log_Status smallint
set @Log_Date ='05/07/2004 00:00:00'
set @Log_Input =1
set @Log_Level =1
set @Log_Milliseconds =1
set @Log_Output =1
set @Log_Panel =1
set @Log_Response =1
set @Log_Status =1
exec InsertLog @Log_Date,@Log_Input,
@Log_Level,@Log_Milliseconds,
@Log_Output,@Log_Panel,
@Log_Response,@Log_Status
END
So in this code I Insert a test message after hat a select "insert".
But when I refresh my SQL DATABASE, I see nothing whereas I the ACCESS DATABASE the AUTOMATION insert record.
Can You Help me about that ?
Best Regards
youssef
|
|
|
|
|
How to overcome that? I'm using MFC ODBC classes. I need sometimes to send a prety long statements. Is there a way to lift the limit?
|
|
|
|
|
I don't know, but you should also be aware that the underlying DBMS may also have a statement limit, so even if you do override the ODBC limit, you may still be stuck.
my blog
|
|
|
|
|
Hi everyone,
I have an ODBC question. I have two databases on the network. One is running under SQL Server and the other is running under Oracle. There is a schema on the Oracle database that has the exact schema as the SQL Server database (more or less). What I want to do is copy the contents of each table from SQL Server to Oracle. I know there must be a way to do this with a view or a script. I cannot use any tool like Oracle migration or anything else and it has to go over ODBC.
Can someone suggest a way to do this? Could Access be used for something like this? I am afraid I am extremely inexperienced with database stuff.
Thanks. Any help would be greatly appreciated.
Sincerely,
Pankaj
Without struggle, there is no progress
|
|
|
|
|
Could you write an app to do it? Could open two connections and zap the data across from one to the other....
Suspect that you won't be able to do it with a view or script, since running on two different RDBMS..
Just out of interest, why can't you use Migration (Or DTS in SQL), and why ODBC Only?
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
Hi Richard,
Thanks for replying. Actually I did ultimately use DTS. The thing was that Oracle has a 30 character limitation on the database identifiers. My source database had big table, view, procedure names etc. Also, there is no auto-numbering in Oracle the way SQL Server has it.
However, I did manage to solve the problem using DTS. I was having a bad day and I was kicking myself for not thinking about DTS. I wrote a script that created the tables on the target database and used ODBC with DTS to copy the data over. The I ran a script which created the sequence and triggers and started auto numbering on the Oracle tables.
Best always,
Pankaj
Without struggle, there is no progress
|
|
|
|
|
pankajdaga wrote:
Also, there is no auto-numbering in Oracle the way SQL Server has it.
Oracle relies on Sequences for autonumbering - similar, but the Select My_Sequence.nextval from dual had to be used....apologies if you already knew that one lol!
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
Yeah, I found that out online. But thanks for pointing it out to me. I was never a database fan. After using Oracle tools I came to the realization that the job of a database administrator is one that I will never take 
Also, all the limitations on the length of tables names and other identifiers is something I found strange. Probably something that has dragged on since the days of the first release. It is about time they removed these non sensical limitations.
Cheers,
Pankaj
Without struggle, there is no progress
|
|
|
|
|
hello every body
i have a small question
How can I insert a row using values from binded text boxes without supplying every column value of my DataRow
Best Regards
UsamaDBA
|
|
|
|
|
Can anyone tell me how to get the number of rows affected by select query using datareader.when we insert,update or delete we can get the number of rows affected,but not when we use select query.So can anyone help me urgent
problemguy
|
|
|
|
|
I've solved this problem once in an ugly way:
I wanted to give back the total number of rows but return only the first 100. So I created a stored Procedure that made first the query with select count(*) and then the same query but with the needed columns and as the last column the result of the first query.
ex:
declare @num as int<br />
select @num = count(*) from Users where groupID = 10<br />
<br />
select top 100 username, address , @num from Users where groupID = 10
Hope this helps a little bit
Greets
Roland
|
|
|
|
|
The datareader does not know the number of rows until it gets them all. If you really need the (expected) number of rows, run a SELECT COUNT(*) query.
my blog
|
|
|
|
|
One way to do it is to put in a return field which counts the records that are returned. For instance add Count(FIeldName) As RowsAffected as one of your fields. This will however return an extra field for each row, which may not be a good idea.
The reason there is no way for the datareader to know how may rows are returned is that it is still connected to the data and not all rows are retrieved yet.
You could count the rows as you use them, through a loop, or if you bind them to a control, count them that way.
hope this helps,
sivilian
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
