MS Access 2000 + VS 2008 / Crystal Reports + issue. - Database Discussion Boards

Re: how to merge or copy one database to another at Midnight every day [modified]

It_tech11-Nov-09 1:05

11-Nov-09 1:05

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

select @@basedir

Davitor8-Nov-09 17:48

Davitor

8-Nov-09 17:48

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.

Weighted average in T-SQL (like Excel's SUMPRODUCT)

Brady Kelly8-Nov-09 12:48

Brady Kelly

8-Nov-09 12:48

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.

Re: Weighted average in T-SQL (like Excel's SUMPRODUCT)

Mycroft Holmes8-Nov-09 13:11

Mycroft Holmes

8-Nov-09 13:11

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.

Re: Weighted average in T-SQL (like Excel's SUMPRODUCT) [modified]

Niladri_Biswas8-Nov-09 17:30

Niladri_Biswas

8-Nov-09 17:30

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
Smile | :)

Niladri Biswas

modified on Monday, November 9, 2009 12:00 AM

backp updated row

Fuhrer8-Nov-09 3:40

Fuhrer

8-Nov-09 3:40

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?

Re: backp updated row

Abhishek Sur8-Nov-09 4:35

Abhishek Sur

8-Nov-09 4:35

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;

Abhishek Sur
Don't forget to click "Good Answer" if you like this Solution.
My Latest Articles-->
Simplify Code Using NDepend
Basics of Bing Search API using .NET
Microsoft Bing MAP using Javascript

MS Access 2000 + VS 2008 / Crystal Reports + issue.

alexvw8-Nov-09 1:25

alexvw

8-Nov-09 1:25

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!

In MYSQL with Innodb engine - How to Implement ACID Transaction in Procedures that are calling other stroed proc

AditiPatil7-Nov-09 0:40

AditiPatil

7-Nov-09 0:40

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

What would be the equivalent in SQL??

TheJudeDude6-Nov-09 10:10

TheJudeDude

6-Nov-09 10:10

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

Re: What would be the equivalent in SQL??

David Mujica6-Nov-09 10:38

David Mujica

6-Nov-09 10:38

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.

Re: What would be the equivalent in SQL??

Niladri_Biswas6-Nov-09 18:22

Niladri_Biswas

6-Nov-09 18:22

Yes you are right. View is to rescue and PIVOT is available since SQL SERVER version 2005.
In 2000, Pivoting is archived via CASE statements

e.g.
http://jdixon.dotnetdevelopersjournal.com/pivot_table_data_in_sql_server_2000_and_2005.htm[^]
Smile | :)

Niladri Biswas

Re: What would be the equivalent in SQL??

TheJudeDude9-Nov-09 4:39

TheJudeDude

9-Nov-09 4:39

Thanx a bunch!

Jude

Sql server license key

prathapcode6-Nov-09 2:37

prathapcode

6-Nov-09 2:37

Hi,

I developed a small website using express edition and MS SQL server 2005(sql server management studio express). Now iam planning to host the application.To publish website with express edition is possible?
What is difference between Proffesional and standard editions of visual studio?
To publish a website standard edition is enough?
And now coming to SQL server whether licensed key is required?

Thanks in advance

Regards,
Prathap

Re: Sql server license key

Abhishek Sur8-Nov-09 9:05

Abhishek Sur

8-Nov-09 9:05

prathapcode wrote:
And now coming to SQL server whether licensed key is required?

AFAIK sql server express can be freely distributed. Just you need to create a setup application and execute its installer from your program.
Smile | :)

Abhishek Sur
Don't forget to click "Good Answer" if you like this Solution.
My Latest Articles-->
Simplify Code Using NDepend
Basics of Bing Search API using .NET
Microsoft Bing MAP using Javascript

Mysql

geeeeeeeetha5-Nov-09 23:59

geeeeeeeetha

5-Nov-09 23:59

hi,

I want to search particular string in entire database and i need to retun the full row in Mysql...

Regards
Jayabharathi Ramasubbu

Re: Mysql

Niladri_Biswas6-Nov-09 1:14

Niladri_Biswas

6-Nov-09 1:14

Hi Jayabharati,
I am sorry but I am a SQL SERVER guy. Henceforth I am not able to write the query for you.
But you can find your answer form here(Exactly the same question you are looking for.. I believe)

http://stackoverflow.com/questions/1384245/way-to-search-entire-database-for-a-string-in-mysql[^]

Hope this helps.
Smile | :)

Niladri Biswas

How to update multiple rows in a table.

chakran5-Nov-09 23:08

chakran

5-Nov-09 23:08

Hi,
I have requirement to update the weekno and StartdayofWeek.

By using the below query I can get weekno and StartdayofWeek but I need to insert/update these values in respective columns in the original table. Can any one help me.

Select BugDate,
          DATEADD(wk, DATEDIFF(wk, 6,[BugDate]), 6) as startdayofweek,
          Datepart(wk,[BugDate]) + ((Datepart(year,[BugDate]) - 2008) * 52) as WeekNumber
from [BugsDB_DefectVolume]

Table:

ID     BugDate              Weekno   BugNo StartDayofWeek
111     2009-04-12      NULL     3     NULL
111     2009-04-19      NULL     1     NULL
111     2009-04-26      NULL     1     NULL
111     2009-05-03      NULL     1     NULL
111     2009-05-10      NULL     9     NULL
111     2009-05-17      NULL     9     NULL
111     2009-05-24      NULL     4     NULL
111     2009-05-31      NULL     2     NULL
111     2009-06-07      NULL     4     NULL

Thanks.

Re: How to update multiple rows in a table.

geeeeeeeetha6-Nov-09 0:17

geeeeeeeetha

6-Nov-09 0:17

hi,
if both table having unique column,then u can use below query

update MyTable
set NtextColumn = ot.NtextColumn
from dbo.MyTable
join anotherDB.dbo.OtherTable ot
on MyTable.KeyColumn = ot.KeyColumn

Re: How to update multiple rows in a table.

chakran6-Nov-09 0:24

chakran

6-Nov-09 0:24

sorry there is no other table. We have only one table called "BugsDB_DefectVolume". In that single table we need to update weekno and Startdayofweek using Bugdate column...

Re: How to update multiple rows in a table.

geeeeeeeetha6-Nov-09 0:35

geeeeeeeetha

6-Nov-09 0:35

hi,

create one temporarytable...

insert values to temporary table...
and use previous post query...

if i'm wrong plz excuse

Re: How to update multiple rows in a table. [modified]

Niladri_Biswas6-Nov-09 1:05

Niladri_Biswas

6-Nov-09 1:05

Hi,

Update the target table based on bug date

Here is the example

Just creating a dummy source table

declare @tblSource table(bugdate datetime)
insert into @tblSource 
select '2009-04-12' union all 
select '2009-04-19' union all
select '2009-04-26' union all
select '2009-05-03' union all
select '2009-05-10' union all
select '2009-05-17'	union all
select '2009-05-24'	union all
select '2009-05-31'	union all
select ' 2009-06-07'

Your target table schema

create table tblTarget (id int,bugdate date,weekno int,bugno int,startdayofweek datetime)
insert into tblTarget 
select 111,'2009-04-12',null,3,null union all 
select 111,'2009-04-19',null,1,null union all
select 111,'2009-04-26',null,1,null union all
select 111,'2009-05-03',null,1,null union all
select 111,'2009-05-10',null,9,null union all
select 111,'2009-05-17',null,9,null	union all
select 111,'2009-05-24',null,4,null	union all
select 111,'2009-05-31',null,2,null	union all
select 111,' 2009-06-07',null,4,null
select * from tblTarget

Output (Before updation)

id	bugdate	          weekno	bugno	startdayofweek
111	2009-04-12	          NULL	         3	     NULL
111	2009-04-19	          NULL	         1	     NULL
111	2009-04-26	          NULL	         1	     NULL
111	2009-05-03	          NULL	         1	     NULL
111	2009-05-10	          NULL	         9	     NULL
111	2009-05-17	          NULL	         9	     NULL
111	2009-05-24	          NULL	         4	     NULL
111	2009-05-31	          NULL	         2	     NULL
111	2009-06-07	          NULL	         4	     NULL

Next execute the query

;with cte as
(
	select	bugdate,
			DATEADD(wk, DATEDIFF(wk, 6,bugdate), 6) as startdayofweek,          
			Datepart(wk,bugdate) + ((Datepart(year,bugdate) - 2008) * 52) as WeekNumber     
	from @tblSource
)
update tblTarget
set tblTarget.weekno = c.WeekNumber,tblTarget.startdayofweek = c.startdayofweek
from cte c
where c.bugdate = tblTarget.bugdate

Output(After Updation)

id	bugdate	       weekno	  bugno	       startdayofweek
111	2009-04-12	        68	   3	    2009-04-12 00:00:00.000
111	2009-04-19	        69	   1	    2009-04-19 00:00:00.000
111	2009-04-26	        70	   1	    2009-04-26 00:00:00.000
111	2009-05-03	        71	   1	    2009-05-03 00:00:00.000
111	2009-05-10	        72	   9	    2009-05-10 00:00:00.000
111	2009-05-17	        73	   9	    2009-05-17 00:00:00.000
111	2009-05-24	        74	   4	    2009-05-24 00:00:00.000
111	2009-05-31	        75	   2	    2009-05-31 00:00:00.000
111	2009-06-07	        76	   4	    2009-06-07 00:00:00.000

Hope this helps.
Smile | :)

Niladri Biswas

modified on Friday, November 6, 2009 7:26 AM

Unable to get the data from 2 tables which are in 2 differenet servers

vnr5-Nov-09 18:58

vnr

5-Nov-09 18:58

Hi all,

I am trying to get the data from 2 tables which are in 2 different servers , to get that i have used
"sp_addlinkedserver" .Using this I am able to connect to the server also .I have used the following commands

for example my server ip is 11.12.23.121

EXEC sp_addlinkedserver '11.12.23.121'
EXEC sp_addlinkedsrvlogin @rmtsrvname = '11.12.23.121'

after this I am not bale to get the results of the linked server using following query

select * from '11.12.23.121'.testdb.dbo.userreg

Please tell me how to get the data from the linked server.

Thanks in advance

Re: Unable to get the data from 2 tables which are in 2 differenet servers

Mycroft Holmes7-Nov-09 13:17

Mycroft Holmes

7-Nov-09 13:17

Try the following

select * from [11.12.23.121].testdb.dbo.userreg

Never underestimate the power of human stupidity
RAH

Re: Unable to get the data from 2 tables which are in 2 differenet servers

vnr7-Nov-09 19:15

vnr

7-Nov-09 19:15

HI,

Thank for your reply , i have tried to get the data using the query but getting the following error

"OLE DB provider "SQLNCLI" for linked server "[server]" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "[server]" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
Msg 53, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [53]. "

can you please tell me the solution for this.

Thank you

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.