Database Discussion Boards

Ennis Ray Lynch, Jr.13-Jul-06 11:21

13-Jul-06 11:21

If you can't get it with one select (with a lot of tweaking you usually can) you can always sutff the results into a table variable and do another select.

DECLARE @table TABLE (int a)

INSERT INTO @table
SELECT
(t1.Value1 - SUM(t2.Value2))
FROM
table1 t1,
table2 t2
GROUP BY
t1.id

SELECT SUM(a) FROM @table

A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."

-- Stephen Crane

Re: SUM() of a SUM() help please!

Mike Dimmick14-Jul-06 0:39

Mike Dimmick

14-Jul-06 0:39

I'd use a subquery:

SELECT SUM(t1.value1 - t2sum) 
FROM table1 t1 
INNER JOIN 
  ( SELECT parent, SUM( value2 ) as t2sum
    FROM t2
    GROUP BY parent ) t2
ON
  t2.parent = t1.id
WHERE
  t1.prop = 0 AND
  t1.payed = 0 AND
  t1.date = '20060321'

Stability. What an interesting concept. -- Chris Maunder

Re: SUM() of a SUM() help please!

Heinz_15-Jul-06 12:12

Heinz_

15-Jul-06 12:12

Thanks man! it worked just fine! your're bright man. The only type error you had was "From table2" instead of "from t2". It worked at first run. I'll study the structure of this query to lear how it works.

Thanks man again, good luck.

Transfer data between databases

Gulfraz Khan13-Jul-06 4:54

Gulfraz Khan

13-Jul-06 4:54

I have two SQL server databases on the same server.
The table structure is same except for few.
I want to move some records from different tables one database to another.
What is the best way to do it?

create a DTS package, but I don't know how it supports 'Begin Tran' and the 'Execute SQL Task' in the DTS does not take any parameter (i.e. global variables) in the update and delete statements.

If I create a Stored procedure and use the 'Begin Tran' and 'Commit' statement in the procedure. It also does not work for me since the stored procedure keeps on executing the statements that are followed even if any error occurs (i.e. primary key violation etc.).
How do I make sure that the statements in the stored procedure are rolled back or procedure exits when any error occurs in it.

Thank you for helping me.

If an error occurs

Ennis Ray Lynch, Jr.13-Jul-06 9:22

Ennis Ray Lynch, Jr.

13-Jul-06 9:22

set @errornum = @@error after every sql statement then check for the error number. @@error is reset after every succesful statement so if(@@error = 15) will set @@error = 0.

http://www.sommarskog.se/error-handling-I.html

A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."

-- Stephen Crane

It works for me!

Gulfraz Khan14-Jul-06 4:19

Gulfraz Khan

14-Jul-06 4:19

Thank you Ennis. It really works for me.
and thank you for the reference URL too.

Multiple user database

codeamin13-Jul-06 4:51

codeamin

13-Jul-06 4:51

Hello all,

I have a MS Access database. There are several employees who uses that database everyday. But when someone opens it, it locks for other user to open the database.
How can I share the database between all the users at the same time? Can someone let me know ASAP.
THANKS

Bashar Amin
Sr. Software Engineer

Open the database as read/only

Ennis Ray Lynch, Jr.13-Jul-06 9:18

Ennis Ray Lynch, Jr.

13-Jul-06 9:18

Access is not designed to be a multi-user database. If it is really required create an application front-end that uses jet or odbc and keeps its fingers crossed. Make daily back-ups because Access likes to get corrupted when it is used with multiple users.

A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."

-- Stephen Crane

Recursive SELECT paradox!!! [modified]

innocent7313-Jul-06 3:40

innocent73

13-Jul-06 3:40

Hi Folks,

I have a database table named Voyage_History that stores the voyage information of our company's vehicles. I have imported data from an MS Excel file and finally I had get the result as screen below:

---------------------------------------

SELECT Voyage_ID,PlateNumber,StartDate from Voyage_End as ddd
Group By Voyage_ID,PlateNumber,StartDate

Voyage_ID PlateNumber StartDate
----------- ----------- ------------------------------------------------------
1 34 DD 0254 2005-12-28 00:00:00
2 34 DD 0254 2005-12-28 00:00:00
3 34 DD 0254 2005-12-30 00:00:00
4 34 DD 0254 2005-12-31 00:00:00
5 34 DD 0254 2006-01-01 00:00:00
6 34 DD 0254 2006-01-04 00:00:00
7 34 DD 0254 2006-01-05 00:00:00
8 34 DD 0254 2006-01-05 00:00:00
9 34 DD 0254 2006-01-06 00:00:00
10 34 DD 0254 2006-01-06 00:00:00
11 34 DD 0254 2006-01-12 00:00:00
12 34 DD 0254 2006-01-13 00:00:00
13 34 DD 0254 2006-01-16 00:00:00
14 34 DD 0254 2006-01-17 00:00:00
15 34 DD 0254 2006-01-17 00:00:00
16 34 DD 0254 2006-01-21 00:00:00
17 34 DD 0254 2006-01-23 00:00:00
18 34 DD 0254 2006-01-26 00:00:00

------------------------------------------------

In fact, voyage_ID 1 and Voyage_ID 2 are same voyages. Because all data is same.

How can I create another table that stores unique Voyage_ID, and the most important part of the question how can I import this data correctly? ( I mean how to determine the same voyages)

Should I have apply a recursive select statement?

-- modified at 9:44 Thursday 13th July, 2006

Re: Recursive SELECT paradox!!!

Eric Dahlvang13-Jul-06 4:13

Eric Dahlvang

13-Jul-06 4:13

This will give you all duplicates:

SELECT a.* 
FROM Voyage_End a, Voyage_End b 
WHERE a.Voyage_ID != b.Voyage_ID AND a.platenumber = b.platenumber AND a.startdate = b.startdate

But you could use something like this to insert into your table:

SELECT (SELECT TOP 1 voyage_id 
        FROM Voyage_End a where a.platenumber = b.platenumber and a.startdate = b.startdate) as voyage_id, PlateNumber,StartDate 
FROM Voyage_End b GROUP BY platenumber, startdate

Or, if you don't care about the IDs, then just:

insert into voyage_end2 (platenumber, startdate) select distinct platenumber, startdate from Voyage_End

--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

Re: Recursive SELECT paradox!!! [modified]

innocent7313-Jul-06 4:30

innocent73

13-Jul-06 4:30

Wow...really good results...

Is it possible to move a column from second row to first row? I mean suppose that I made a basic select operation and got the result as below:

---------------------------------------------------------

SELECT Voyage_ID,StartDate,PlateNumber,Driver1 FROM voyage_END
order by startdate

Voyage_ID StartDate PlateNumber Driver1
421 2005-12-28 34 VS 0845 X
750 2005-12-28 34 VS 0845 Y

----------------------------------------------------------------

The MAJOR problem is:

How can I create a new table such as:

Voyage_ID StartDate PlateNumber Driver1 Driver2

421 2005-12-28 34 VS 0845 X Y

difficult case...right?

Re: Recursive SELECT paradox!!!

Eric Dahlvang13-Jul-06 4:37

Eric Dahlvang

13-Jul-06 4:37

I don't understand the question.

--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

Re: Recursive SELECT paradox!!!

innocent7313-Jul-06 4:40

innocent73

13-Jul-06 4:40

The best thing

Ennis Ray Lynch, Jr.13-Jul-06 5:20

Ennis Ray Lynch, Jr.

13-Jul-06 5:20

Would be to have driver in another table since there is an obvious 1 to many relationship between the data. However, you can write a T-SQL cursor to loop (I know, I know I am laughing as I type) through the data and create a mock data set that matches your hearts desire. It will be slow though. Split the tables make your life easy.

A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."

-- Stephen Crane

Re: Recursive SELECT paradox!!!

Eric Dahlvang13-Jul-06 5:50

Eric Dahlvang

13-Jul-06 5:50

This isn't very pretty...and there are probably better ways to do what you want. But, if there is only ever two drivers, you can still get it all into another table with just one SQL:

insert into voyage_end2 (voyage_id,platenumber, startdate,Driver1,Driver2)
SELECT (SELECT TOP 1 voyage_id 
        FROM Voyage_End a where a.platenumber = b.platenumber and a.startdate = b.startdate) as voyage_id, 
PlateNumber,StartDate, 
(SELECT TOP 1 Driver1 
        FROM Voyage_End a where a.platenumber = b.platenumber and a.startdate = b.startdate order by Driver1) as Driver1, 
(SELECT TOP 1 Driver1 
        FROM Voyage_End a where a.platenumber = b.platenumber and a.startdate = b.startdate and 
	(SELECT TOP 1 Driver1 FROM Voyage_End a where a.platenumber = b.platenumber and a.startdate = b.startdate order by Driver1)  != Driver1 order by Driver1 desc) as Driver2
FROM Voyage_End b
 GROUP BY platenumber, startdate

However, your best long term solution is to do as Ennis Ray Lynch, Jr. has suggested and modify your table structure.

--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

SQL replication using .net

farshad.A13-Jul-06 2:38

farshad.A

13-Jul-06 2:38

Hi,
I want to do a transactional replication for a SQL database using c# code; can anyone please help me to do so ?
Of course any code on any language would do the the job for me, as I can change it to c#.
I think the best way is making use of SQLDMO, although I've got no idea on how to do it.

Thanks

farshad

SQl file Log

AnhTin12-Jul-06 18:13

AnhTin

12-Jul-06 18:13

I have web application use Database is SQLServer 2000
every day have 10000 people visit my Web site
and Size File Log increase very higth but my server end space

can i use add new database server
Some body help me

Re: SQl file Log

kumarprabhakar7412-Jul-06 18:37

kumarprabhakar74

12-Jul-06 18:37

Can you calrify, You are talking about database log file or any extra log file like texxt file on the server to record log.

Kumar Prabhakar

Re: SQl file Log

AnhTin12-Jul-06 18:59

AnhTin

12-Jul-06 18:59

yes when many people visit and many query to database get Data
so file Log of Database very big

Re: SQl file Log

WoutL12-Jul-06 20:40

WoutL

12-Jul-06 20:40

This growing of the log-file will stop if you backup it regularly.

Or you can change the recovery model to Simple.

Wout Louwers

Re: SQl file Log [modified]

Mike Dimmick13-Jul-06 3:18

Mike Dimmick

13-Jul-06 3:18

You're not backing up your transaction log. SQL Server maintains a record of all transactions within the log to enable you to restore to any point in time.

See this comment[^] of mine on someone else's similar problem.

See also here[^] for more about what the transaction log contains.

Stability. What an interesting concept. -- Chris Maunder

-- modified at 9:19 Thursday 13th July, 2006

Comparing strings in SQL

kscadi12-Jul-06 17:42

kscadi

12-Jul-06 17:42

FYI:
I'm programming in Visual Basic 6.0
I have an ADODC control that is linked to a .mdb file I created in Microsoft Access
The provider I am using to connect to the database is Microsoft Jet OLE DB 4.0

My Problem:
String comparisons in my SQL queries are not behaving the way I'd like:

As a silly example,

SELECT * FROM tbl WHERE 'a' = 'A' returns everything in tbl
SELECT * FROM tbl WHERE 'a' > 'A' returns nothing in tbl
SELECT * FROM tbl WHERE 'a' < 'A' returns nothing in tbl

How do I make the above comparisons so they are case sensitive?

I need my comparisons to abide by the following character ranking scheme:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnoffpro01/html/GettingStartedwithADO.asp

Under this scheme, the following holds:

('a' = 'A') = FALSE
('a' > 'A') = TRUE
('.0000m3d10_1' > '.0000m3d10Z') = TRUE

Your help is greatly appreciated!

-Lee``

Re: Comparing strings in SQL [modified]

technomanceraus12-Jul-06 18:09

technomanceraus

12-Jul-06 18:09

I think you'll probably find that an Access DB is not case sensitive (for indexes and data) and i don't believe you can get it to be either. Why not try SQL Server 2005 Express edition instead as you will be able to set the database up with a binary or case sensitive sort order (i.e. There will be case sensitivity for strings)

You can do it in code though in access check this out here for more information.

-= Technomancer =-

-- modified at 0:09 Thursday 13th July, 2006

getting query names

hamidreza_buddy12-Jul-06 7:44

hamidreza_buddy

12-Jul-06 7:44

hi Access databases in spite of tables have queries stored in them. i wanted to know do other Databases (oracle,sqlserver,..) have Queries stored in them. if so, how should i get their names or their SQL statement?
is there something in databases's SCHEMAINFORMATION for this?

Re: getting query names

leckey12-Jul-06 7:58

leckey

12-Jul-06 7:58

I'm familiar with SQL Server 2000. They don't have "queries" but they have stored procedures in which you can create queries, and return results.

For example, my datagrid calls a stored procedure that looks up the information about a part, based on what the user entered as a part number.

If you have Enterprise Manager installed onto your computer, you can connect to the server, then the database. Inside the database you can look at the tables, stored procedures, users, etc. Without EM I am not sure how else to look at them unless you are on the server directly.

Hope that helps.

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