|
I shortened up the SQL a bit, but I am getting the same error The error is on DbReader = DbCommand.ExecuteReader():
OdbcConnection DbConnection = new OdbcConnection("DSN=rsss");
OdbcCommand DbCommand = DbConnection.CreateCommand();
DbCommand.CommandText = "SELECT TICKET-STORE, DATEDIFF(day,'" + strDate + "','" + strDate + "') AS EXP FROM TICKET";
DbConnection.Open();
try
{
DbReader = DbCommand.ExecuteReader();
while(DbReader.Read())
{
intStore = Int32.Parse(DbReader["TICKET-STORE"].ToString());
}
}
catch(OdbcException caught)
{
Console.WriteLine(caught.ToString());
Console.Read();
DbConnection.Close();
}
Here is the error:
System.Data.Odbc.OdbcException: ERROR [HY000] Build Error: Invalid Function Name. Function name is 'DATEDIFF'.
at System.Data.Odbc.OdbcConnection.HandleError(HandleRef hrHandle, SQL_HANDLE hType, RETCODE retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method)
at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Odbc.OdbcCommand.ExecuteReader()
at acctmgr.Class1.Main(String[] args) in c:\documents and settings\brian\my documents\visual studio projects\acctmgr\class1.cs:line 43
Thanx for your help!
Jude
|
|
|
|
|
I don't see anything wrong, but then I'm not a DB expert at all.
However, from earlier messages, I think you target SQL Server 2000, and this[^] seems to suggest you use SqlConnection instead of OdbcConnection. Which would mean a different connection string, some C# code changes, and probably some SQL changes too.
Luc Pattyn
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
|
|
|
|
|
I figured it out. The contacted the company who produced the connector. It does not support a DateDiff() function :-/
I also contacted the third party vender and they stated that they have a built in function for it. Hopefully that works.
Thanks a lot for your time and help!
Jude
|
|
|
|
|
Good Day All
am restoring a Database Programatically in Sql. Now if the backup is located in the remote machine i use a Share
\\Vuyiswa\MyShare\
but i will get the Following Error
Exception caught in: ExecuteStoredProc: The file "\\Vuyiswa\Databases\\REmoteTest33.mdf" is on a network path that is not supported for database files. File 'TNGoedit_Data' cannot be restored to '\\Vuyiswa\Databases\\REmoteTest33.mdf'. Use WITH MOVE to identify a valid location for the file. The file "\\Vuyiswa\Databases\\REmoteTest33_log.ldf" is on a network path that is not supported for database files. File 'TNGoedit_Log' cannot be restored to '\\Vuyiswa\Databases\\REmoteTest33_log.ldf'. Use WITH MOVE to identify a valid location for the file. Problems were identified while planning for the RESTORE statement. Previous messages provide details. RESTORE DATABASE is terminating abnormally. Database 'REmoteTest33' does not exist. Make sure that the name is entered correctly.
Thanks
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
Vuyiswa Maseko wrote: The file "..." is on a network path that is not supported for database files.
That part of the error says that you cannot restore a backup that's sitting on a network drive. I suggest you copy the file locally, and restore from there
I are Troll
|
|
|
|
|
i know that
but i needed a Solution for this. This means that every time i do a restore i need to copy the filer over and if the Servers are not in the same place and the backup is big this can be a lengthy process.
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
Vuyiswa Maseko wrote: This means that every time i do a restore i need to copy the filer over and if the Servers are not in the same place and the backup is big this can be a lengthy process.
That's probably also the reason why you cannot restore from a network-drive. It would be slow, since every read would be reading from a remote drive over TCP/IP. I guess that it's faster to download the really big file and restore from a local drive, then try to restore from a network path (which may temporarily loose connectivity!)
The software refuses to use a network path. Solutions mean either changing the software, or the network path. Out of those two options, the path is probably the easier on to change.
I are Troll
|
|
|
|
|
Hi All,
I am trying to find a solution on gettting records from One database in to another one.So that i can only rely on one database for any information instead of having to login in to two databases.
Any idea will be much appreciated.
Many thanks
|
|
|
|
|
You can write a program to do the job, then schedule it using the scheduler (get access to it from Control Panel) to make it run every day at midnight.
|
|
|
|
|
|
Thanks so Much for your reply.Its much apprecaited.
Let me expalin clearly what i need. I have two mirrored databases exactly the same to each other.There is few table that contains different records.I want to copy the data from Database A to Database B
How can i do that?I would appreciate if you can give me a detailed step.
Many thanks for your help.
|
|
|
|
|
Currently I don't have much time to do this. I used the code (that I pointed out in my previous message) in my projects. I only needed to modify a little to solve some Microsoft Access problems. It worked very well.
What you need to do is to build a standlone program to conduct the copy operation, then schedule it using the Windows Scheduler. I can only guarantee that the code to copy tables across databases works well, whether they are of the same type (e.g. both of them are MS Access) or different (can be Oracle, MS SQL Server, MySQL Server, etc).
The hard part, though, is the creation of the table if it does not exist in the target database before the copy operation starts. This, of course, is assuming that you don't plan to create the table(s) by hand. You have to write that part of the program. However, from your description of your project, this problem does not exist. You already have all the tables ready.
I would suggest using C# and ADO.NET to write this program, since it will save you lots of time.
|
|
|
|
|
Thanks so much it really works for me.What i want to find out now is How can i make sure that the copy process doesnt copy data already copied when doing it on another session.
If the copy is done onece during the second operation will it know what records to add?
How do you also solve the problem when the two databases run in Different virtual private networks?
Lastly,when you copy the data it doesnt really check what has been copied before.So all the information will be be copied again every time the code runs.
Many thanks
modified on Wednesday, November 11, 2009 7:51 AM
|
|
|
|
|
Hi All
I am using "Select @@basedir" command to get installation path of mysql.
MYSQL_RES *res;
stt="select @@basedir;"
mysql_query(conn, strp)
res = mysql_store_result(conn) ;
How can i get result in CString.
|
|
|
|
|
I am looking for a way to derive a weighted average from two rows of data with the same number of columns, where the average is as follows (borrowing Excel notation):
(A1*B1)+(A2*B2)+...+(An*Bn)/SUM(A1:An)
The bold part reflects the same functionality as Excel's SUMPRODUCT() function.
My catch is that I need to dynamically specify which row gets averaged with weights, and which row the weights come from, and a date range.
|
|
|
|
|
Do the whole thing in a stored proc.
Create a temp table to hold the interim calculations and then do your processing on the temp table (table var in SQL Server). Your design seems a little odd if you hold both the values to be averaged and the weighting factor in the same table.
Row_number() is very useful in this type of processing.
|
|
|
|
|
If I have understand your requirement, then try this
SET DATEFORMAT dmy
declare @tbl table(A int, B int,recorddate datetime,KPI varchar(50))
insert into @tbl
select 1,10 ,'21/01/2009', 'Weighty'union all
select 2,20,'10/01/2009', 'Tons Milled' union all
select 3,30 ,'03/02/2009', 'xyz'union all
select 4,40 ,'10/01/2009', 'Weighty'union all
select 5,50 ,'05/01/2009', 'Tons Milled'union all
select 6,60,'04/01/2009', 'abc' union all
select 7,70 ,'05/01/2009', 'Weighty'union all
select 8,80,'09/01/2009', 'xyz' union all
select 9,90 ,'05/01/2009', 'kws' union all
select 10,100,'05/01/2009', 'Tons Milled'
select SUM(t1.A*t2.A)/SUM(t2.A)Result from
(select RecordDate,A,B,KPI from @tbl)t1
inner join(select RecordDate,A,B,KPI from @tbl t)t2
on t1.RecordDate = t2.RecordDate
and t1.KPI = t2.KPI
Hope this helps
Niladri Biswas
modified on Monday, November 9, 2009 12:00 AM
|
|
|
|
|
hi,
at oracle db i want to backup a row when an update request come for the row. how can i make this with pl/sql?
|
|
|
|
|
use Update Trigger.
Whenever the row is updated use :old and :new objects to get the data from updated row and new row.
If it is after update trigger, you need to use :old to get old values otherwise use select from table to get the value.
Now insert them to the log table. For instance :
CREATE OR REPLACE TRIGGER orders_after_update
AFTER UPDATE
ON orders
FOR EACH ROW
DECLARE
v_username varchar2(10);
BEGIN
-- Find username of person performing UPDATE into table
SELECT user INTO v_username
FROM dual;
-- Insert record into audit table
INSERT INTO orders_audit
( order_id,
quantity_before,
quantity_after,
username )
VALUES
( :new.order_id,
:old.quantity,
:new.quantity,
v_username );
END;
|
|
|
|
|
Hi,
Would you know why parameterized queires in a MS Access 2000 DB can't be seen from VS 2008 Report Wizard or Crystal Reports 2008?
When I try to create the report using their wizards, none of them see or detect any queries that use parameters, they only pick the ones that have no parameters at all.
The queries are fully functional, and I am able to invoke them from code.
Thanks for you assistance.
Cheers!
|
|
|
|
|
Hi All,
In MYSQL
1)we migrated one project from Oracle to MYSQL.
There is one Procedure that is called by many other procedures.
We will call the procedure Level2 (Called Procedure)
The Level1 (Calling ) Procedure is calling the Level2 Procedure.
If something Fails in Procedure2 - Level2 (.. Called Procedure );
Any other statements in Level1 Procedure before calling proc level2 - should be rolled back since autocommit=0; In Oracle this works fine. Can some one tell How to implement the same in MySQL - Innodb. Or do we have to use falcon for the same.
(In MySql as soon as we call the Level2 Procedure in Level1 - In Level2 procedure there is begin and due to this begin the previous trnasactions are commited )
Details :
Table T
DROP TABLE IF EXISTS `bestr`.`t`;
CREATE TABLE `bestr`.`t` (
`c` binary(3) DEFAULT NULL,
`Col1` varchar(45) NOT NULL DEFAULT '',
`Col2` varchar(45) DEFAULT NULL,
`LCT` datetime DEFAULT NULL,
`DeleteFlag` varchar(1) DEFAULT NULL,
PRIMARY KEY (`Col1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Insert into t Values ('1', '1', '1', CURRENT_TIMESTAMP, 'N');
Insert into t Values ('1', '2', '1', CURRENT_TIMESTAMP, 'N');
Insert into t Values ('1', '3', '1', CURRENT_TIMESTAMP, 'N');
-------------------- Proc 1 ---------
DELIMITER $$
DROP PROCEDURE IF EXISTS `bestr`.`lEVEL1` $$
CREATE DEFINER=`BESTR`@`%` PROCEDURE `lEVEL1`(
INO varchar(26),
Out R2 Int,
Out RC Int
)
Begin
Declare R1 Int;
Set AutoCommit=0;
Set R1=0;
Set R2=0;
START TRANSACTION ;
Update T Set COL2 = Col2 + 1 Where COL1 = INO;
-- Block1
Call Level2 ( INO, R1, RC);
Set R2=R1;
-- Block 1 end (Note if Block 1 is commented the transaction is implemented properly.
-- Set R2= ROW_COUNT();
if R1 > 0 then
Commit;
else
RollBack;
end if;
end $$
DELIMITER ;
========================================================
DELIMITER $$
DROP PROCEDURE IF EXISTS `bestr`.`lEVEL2` $$
CREATE DEFINER=`BESTR`@`%` PROCEDURE `lEVEL2`(
INO2 varchar(26),
Out R1 Int,
Out RC int
)
Begin
Set AutoCommit=0;
Set R1=0;
START TRANSACTION;
Select Count(1) into RC from T Where COL1 = INO2;
Update T Set LCT = CURRENT_TIMESTAMP Where COL1 = INO2;
Update T Set C = 'FailTest' Where COL1 = INO2;
Select ROW_COUNT() into R1;
if R1 > 0 then
Commit ;
else
Rollback ;
end if;
end $$
DELIMITER ;
==========================================================
---------------------------------------------
Call Level1 ('3', @R1, @R2);
Select @R1, @R2;
2) Is reliable, tested falcon Download link is available?
Your inputs will be very useful.
Regards
Manoj
|
|
|
|
|
I am having to build queries on a new database through an ODBC (our company just bought new POS software) to do reports. One of the DBA's at the company showed me how to access some of the data in MS Access. I am trying to do the equivalent in SQL (We are using MS SQL 2000 and do not need an upgrade for what we use it to do).
The first query is this:
SELECT TICKET.[TICKET-STORE], TICKET.[TICKET-ACCT-MANAGER], TICKET.[TICKET-TYPE], TICKET.[TICKET-CONTRACT-DATE], TICKET.[TICKET-NEXT-DUE-DATE], TICKET.[TICKET-LAST-PAID-DATE], DateDiff('d',Now()-1,[TICKET-NEXT-DUE-DATE]) AS Expr1, TICKET.[TICKET-TICKET-NBR]
FROM TICKET
WHERE (((TICKET.[TICKET-TYPE])="O")); Which is named qryactive. Then all of the other queries select from qryactive. My question is - Is there any more efficient way in MSSQL other than building a table out of that query then selecting from it?
The second is the use of TRANSFORM in MS Access. There is not an equivalent in MSSQL 2000. I read a few articles on how to possibly do it, but I get lost.
Could someone point me in the right direction?
Jude
|
|
|
|
|
1) A more efficient way to handle the query based on the results of another query is to build a MS-SQL view. In your case I belive the view would be equal to "qryactive". The other queries would reference this view just like a table in the "from" clause.
2) I believe the equivalent to TRANSFORM would be PIVOT in SQL-Server. However, I'm not 100% sure that PIVOT is available in SQL 2000.
|
|
|
|
|
|
|