|
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
|
|
|
|
|
i am using ms access 2000 as database and i want to calculate salary of the employee on hourly basis. in database a field contain daily working hour in "HH:MM:SS" format.Now i want take sum of whole month time.
Here I have no idea how to take the sum of the time when the format is "date/time".output should must in the format of "HH:MM:SS"
Thanks
|
|
|
|
|
If iam making a table of location in that table i have columns city,county,condition. And i had some values added in these columns..
after addeding the values in these three columns now i want to add the new column temperature by
alter table location add(tempareture number(2));
but i want to add the values from start in this new column how that is possible is that possible for 1st column value as follow..
insert into location(tempareture) values(34) where city='Athens';
like that in other columns i can put the value tempareture????
please tell me solution..
Raj Khatri
|
|
|
|
|
seems you need to update not to insert
update location<br />
set tempareture=34<br />
where city='Athens';
|
|
|
|
|
Hi all the experts in Code Project,
I've a problem with returning the value returned by ExecuteScalar function under OleDbCommand. Here is a code sample.
...
cmd.CommandText = searchSqlstr;
long result = Convert.ToInt64(cmd.ExecuteScalar());
connection.Close();
return result;
Is there a way to rewrite this code so that I DO NOT need the 'result' variable?
since we always have to close the connection, if I write:
return Convert.ToInt64(cmd.ExecuteScalar());
it would be a bad idea right?
So is there a trick to do this?
Thanks a Lot
|
|
|
|
|
cmd.CommandText = searchSqlstr;
try
{
return Convert.ToInt64(cmd.ExecuteScalar());
}
finally
{
connection.Close();
}
Better yet would be a using(){} statement for the connection object, so that it will automatically be disposed, but since you didn't list all the code from instantiation to disposal, I can't reacreate it accurately.
using (SqlConnection connection = new SqlConnection(connString))
{
connection.Open();
try
{
SqlCommand cmd = connection.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = searchSqlstr;
return Convert.ToInt64(cmd.ExecuteScalar());
}
finally
{
connection.Close();
}
}
Grim (aka Toby) MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL
GO
(0 row(s) affected)
|
|
|
|
|
Ah! Great tip! totally forgot about the using statements 
Thanks a Lot!
|
|
|
|
|
In the example given, the connection.Close(); is redundant, since the using block will take care of that. Which makes the try-finally is also redundant, (unless you want to implement a catch). So a simple version is:
using (SqlConnection connection = new SqlConnection(connString))
{
connection.Open();
SqlCommand cmd = connection.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = searchSqlstr;
return Convert.ToInt64(cmd.ExecuteScalar());
}
my blog
|
|
|
|
|
Hi, I tried the c# forum, but this might be better. How do I programatically get table names from a .mdb file? There doesn't seem to be a way. I'd like to be able to open any .mdb file, read in the list of tables, etc.
Thanks for any help or tips.
Brian.
|
|
|
|
|
There are a couple of ways to do it....
One way is to use ADODB.Connection.OpenSchema method - returns a recordset with table names and characteristics if specifying the adSchemaTables flag
Other way is to use ADOX - Create a Catalog object, give it an ActiveConnection and it exposes a Tables collection - useful for seeing what's there and also modifying schema info...
As for doing it in .NET - no idea! SUspect that ADO.NET will support the above, but worth checking lol
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
