Re: ms access - Database Discussion Boards

Wendelius25-Feb-09 4:34

25-Feb-09 4:34

And to add to J4amieC's post, you can create an Access database file for example:
- using an Access ODBC driver from Control Panel / DataSources (ODBC) if the db file is just for you
- programmatically using ADOX: How to create an Access database by using ADOX and Visual C# .NET[^]

The need to optimize rises from a bad design.My articles[^]

how to get last row of the database table.or only latest row entered in table

anujbanka178425-Feb-09 1:28

anujbanka1784

25-Feb-09 1:28

how to get last row of the database table.or only latest row entered in table

Re: how to get last row of the database table.or only latest row entered in table

Diving Flo25-Feb-09 3:36

Diving Flo

25-Feb-09 3:36

Depends a little bit on the database. If it is a SQL Server try this:
SELECT TOP(1) * FROM table ORDER BY field DESC
By providing a orderby clause you can specify what the condition behind "the last" record is. If you are interested in the last record that was added, you may use a timestamp or auto incremented field in the orderby clause.

Re: how to get last row of the database table.or only latest row entered in table

anujbanka178425-Feb-09 16:57

anujbanka1784

25-Feb-09 16:57

thnx for reply its working.....

DTS package Send Mail

Ramkithepower25-Feb-09 0:52

Ramkithepower

25-Feb-09 0:52

Hi ,

I have created a DTS package to send a mail. This package works fine in my local system since I have a outlook profile. but when moved to the server this task fails because this server does not have a outlook profile installed. Is there a way to configure this Send Mail task to use my user id and microsoft exchange server.

Thanks in advance

Re: DTS package Send Mail

Wendelius25-Feb-09 8:26

Wendelius

25-Feb-09 8:26

Ramkithepower wrote:
Is there a way to configure this Send Mail task to use my user id and microsoft exchange server.

You have to have a MAPI client installed and configured correctly in order to SQL Mail to work. Go through this check-list: Common SQL Mail Problems[^]

The need to optimize rises from a bad design.My articles[^]

ms access

hrishiS25-Feb-09 0:34

hrishiS

25-Feb-09 0:34

Hi to all

Could anyone please help me with a sql query.

I am using MS Access database with Vb.net2005

I have 3 table student,class_held,attendance with the following fields

1. student table (contains the details of students)

id (primary key)
fname
lname
batch_id
.
.

2.class_held (contains the details of each class held)

class_id (primary key)
date
batch_id
subject

3.attendance (contains the details of only absentees student) [there is no primary key I have given, class_id can be same for n number of students who are absent for a particular class ].[absentees are nothing but the student_id in the student table]

class_id
absentees

Now I want the list of students who are present for a particular class.
Thanks in advance

Regards,
HImangshuS

I am a learner Always

Re: ms access

Wendelius25-Feb-09 1:13

Wendelius

25-Feb-09 1:13

Sounds like homework...

However, is the idea that every student should be on every class??? If that's correct you could use cartesian product to generate all combinations and then use NOT IN clause to check that the user wasn't marked as absent.

BTW, posting the query you have already tried (you have tried, haven't you) would help a lot.

The need to optimize rises from a bad design.My articles[^]

Re: ms access

hrishiS25-Feb-09 2:10

hrishiS

25-Feb-09 2:10

Thanks for your reply Mika,

This is not a homework but for my college project.

Anyway,I have already done the Cartesian product and got all the combinations. But, I don't know how to use NOT IN clause with 2 condition (because I need to check for the student Id as well as class_id).

Regards,
Himangshu S

I am a learner Always

Re: ms access

Wendelius25-Feb-09 5:05

Wendelius

25-Feb-09 5:05

Perhaps it would be easiest to use something like:

...
WHERE NOT EXISTS( SELECT 1 
                  FROM   attendance
                  WHERE  attendance.class_id  = class_held.class_id
                  AND    attendance.absentees = student.id)

The need to optimize rises from a bad design.My articles[^]

Re: ms access

hrishiS25-Feb-09 20:10

hrishiS

25-Feb-09 20:10

Hello Mika,

Thanks a lot...I got that correct..

Regards,
HimangshuS

I am a learner Always

Covert Data in .DBF files to MS Sql

pavanip24-Feb-09 22:23

pavanip

24-Feb-09 22:23

Hi,

I am having database files in .dbf format.I am new to Foxpro database.Now i want to convert those foxpro database files to Sql server. Can you guide me is there any other tools to convert .Dbf files to Sql server(.mdf,.ldf).Its very urgent to me if you know please suggest me.

Thanks in Advance
Pavani

Re: Covert Data in .DBF files to MS Sql

Wendelius25-Feb-09 1:19

Wendelius

25-Feb-09 1:19

There seems to be lots of results when you google: http://www.google.com/search?hl=en&q=convert+dbf+to+sql+server&meta=&aq=f&oq=[^]

The need to optimize rises from a bad design.My articles[^]

can't crate trigger in stored procedure

papy-boom24-Feb-09 22:22

papy-boom

24-Feb-09 22:22

hi all,
i want to create a trigger on table through a stored procedure but i got error in excuting the script
it say syntax problem near word TRIGGER

CREATE PROCEDURE CreateMyTrigger
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

CREATE TRIGGER [dbo].[InsertQuestionTrigger]
ON [dbo].[Question]
FOR INSERT AS
UPDATE child
-- set the depth of this "child" to be the
-- depth of the parent, plus one.
SET depth = ISNULL(parent.depth + 1,0),
-- the lineage is simply the lineage of the parent,
-- plus the child's ID (and appropriate '/' characters
lineage = ISNULL(parent.lineage,'/') + LTrim(Str(child.QuestionId)) + '/'
-- we can't update the "inserted" table directly,
-- so we find the corresponding child in the
-- "real" table
FROM Question child INNER JOIN inserted i ON i.QuestionId=child.QuestionId
-- now, we attempt to find the parent of this
-- "child" - but it might not exist, so these
-- values may well be NULL
LEFT OUTER JOIN Question parent ON child.IdParent=parent.QuestionId
END
GO
Any Idea and how can create trigger using function or stored procedure.
Thank you in advance!

Re: can't crate trigger in stored procedure

Wendelius24-Feb-09 22:44

Wendelius

24-Feb-09 22:44

Instead of writing the trigger creation directly to the stored procedure, put it in a varchar variable. Then you can use EXECUTE[^] to execute the string.

The need to optimize rises from a bad design.My articles[^]

Re: can't crate trigger in stored procedure

Riaan Booyzen24-Feb-09 23:57

Riaan Booyzen

24-Feb-09 23:57

for example;
declare @query varchar(500)

set @query = "your trigger create here, I would suggest using not exists clause to prevent you from trying to create it when it is already created Smile | :)

"

EXECUTE @query

Good luck

How to Select the Next 20 records in SQL server?

star_platinum24-Feb-09 19:25

star_platinum

24-Feb-09 19:25

Hi All,

I know "TOP" Function can select the TOP n records. How can I select records from for example 20 to 50? I know SQL server doesn't have "LIMIT" Function....

Thanks

Tin

Re: How to Select the Next 20 records in SQL server?

Wendelius24-Feb-09 19:30

Wendelius

24-Feb-09 19:30

Have a look at ROW_NUMBER[^]

The need to optimize rises from a bad design.My articles[^]

Re: How to Select the Next 20 records in SQL server?

star_platinum24-Feb-09 22:19

star_platinum

24-Feb-09 22:19

Thanks for your reply, I able to create the row number column but when I tried to select a range of row number. It appeared this error message "
Conversion failed when converting the varchar value 'Row_Number' to data type int"

I have used cast and convert already.

Any idea?

Tin

Re: How to Select the Next 20 records in SQL server?

Wendelius25-Feb-09 0:01

Wendelius

25-Feb-09 0:01

Could you post the query so it would be easier to see the problem

The need to optimize rises from a bad design.My articles[^]

Re: How to Select the Next 20 records in SQL server?

Riaan Booyzen24-Feb-09 23:55

Riaan Booyzen

24-Feb-09 23:55

Hi,

what you oculd also try is something like this...

SELECT top(20)* FROM _yourtable
WHERE _tableID not in (select top(20) tableID from _yourtable)

SP Optimization

Guvera24-Feb-09 19:20

Guvera

24-Feb-09 19:20

Hi friends,

Is any tutorial or useful link for stored procedure optimization with example.

I searched in google.But it is not enough. Please if you know send me,

Hope your's reply.

Thanks & Regards

Every Successful Person Have A Painful Story

Re: SP Optimization

Wendelius24-Feb-09 19:34

Wendelius

24-Feb-09 19:34

That's a very large area. A quick look on this search seems to have several excellent articles, snippets etc concerning optimization. Although you said you have googled, have you had a good look on the articles. See: http://www.google.com/search?hl=en&q=sql+server+stored+procedure+optimization&meta=[^].

The need to optimize rises from a bad design.My articles[^]

Re: SP Optimization

Guvera24-Feb-09 19:38

Guvera

24-Feb-09 19:38

Hi mik

Thanks for yours reply.

Every Successful Person Have A Painful Story

Mega Pole - What is Your Favorite Database?

pinaldave24-Feb-09 19:18

pinaldave

24-Feb-09 19:18

I have been listening so many different answer to question of favorite database. I would like to know what is favorite database of SQLAuthority.com readers. I encourage all of you to participate in this poll. It is very quick poll and takes a second.

A gift of USB drive will be provided to user who will also add comment discussing this subject matter. I encourage to send emails to your friends to participate in poll. I am hopping this poll becomes landmark poll for people who are finding answer to favorite database.

http://blog.sqlauthority.com/2009/02/25/the-poll-what-is-your-favorite-database/[^]

Kind Regards,
Pinal Dave

http://www.SQLAuthority.com

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