Database Discussion Boards

First Prev Next

Re: Import XML to SQL

Ashfield20-Jul-09 4:44

Ashfield

20-Jul-09 4:44

Vuyiswa Maseko wrote:
The Problem is that there was a Unique index on a certain Field

.. which means there were duplicate values for this field, otherwise your unique index would not have caused a failure

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Re: Import XML to SQL

Vimalsoft(Pty) Ltd20-Jul-09 4:46

Vimalsoft(Pty) Ltd

20-Jul-09 4:46

Yes but not in a Row Level. Because the Defination of the Field allowed Duplicates in a Field Level.

Vuyiswa Maseko,

Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/

Re: Import XML to SQL

Ashfield20-Jul-09 4:52

Ashfield

20-Jul-09 4:52

Vuyiswa Maseko wrote:
Yes but not in a Row Level

I kind of expected you would look at the fields that form the unique indices, not look for duplicate rows (although obviously they would also cause the problem).

Anyway, all solved now Smile | :)

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Re: Import XML to SQL

Vimalsoft(Pty) Ltd20-Jul-09 4:53

Vimalsoft(Pty) Ltd

20-Jul-09 4:53

Thanks i never thought there would be an index there, i just inherited the System. thanks Smile | :)

Data Migration- ORACLE and VB.NET

cbe_pav20-Jul-09 0:43

cbe_pav

20-Jul-09 0:43

Hi all,

I am doing Data Migration using ORACLE and Vb.net 2005. Im transferring Values from one database to another.
Currently,getting values from Source DB and those values are filled in a Dataset,using For loop,the values are inserted into the Destination DB.
The above mentioned method is working fine for Less number of records(500 approx.)

For more than one lakh records,it is not a gud solution. It is taking more than an hour...

How to do BULK INSERTION using ORACLE and VB.NET.

Pls any one help me...

Thanks in advance

Re: Data Migration- ORACLE and VB.NET

dan!sh 20-Jul-09 0:54

dan!sh

20-Jul-09 0:54

Why not use SSIS?

sql code problem Help pls

TALHAKOSEN19-Jul-09 22:21

TALHAKOSEN

19-Jul-09 22:21

Hi all, i know maybe its is really easy but I've been working on it for ages ! (

Name Diploma year
A 1991
A 1986
A 1981

B 2001
B 1996
B 1992

C 1990
C
C

D 2006
D
D

i have a 3-4 tables and join them, then it returns lots of people have diplomas from different
school, but i need to show the last graduated school for everyone. like that

A 1991
B 2001
C 1990
D 2006
.
.
.
.
.

thanks for now ...
Talha

thanks for everything i have...

Re: sql code problem Help pls

Blue_Boy19-Jul-09 22:29

Blue_Boy

19-Jul-09 22:29

select max(diplomayear),[name]
from mytable
group by [name]

Hope this will help you.

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

Re: sql code problem Help pls

TALHAKOSEN19-Jul-09 23:10

TALHAKOSEN

19-Jul-09 23:10

thank you, it works !

thanks for everything i have...

Re: sql code problem Help pls

Blue_Boy19-Jul-09 23:13

Blue_Boy

19-Jul-09 23:13

No problem.

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

Generating dynamic where clause in stored procedure in sqlserver 2005

nainakarri19-Jul-09 20:34

nainakarri

19-Jul-09 20:34

Hi
I have a stored procedure with an input parameter varchar.

I am passing the parameters from UI to stored procedure by looping through the parameters and making them as string and passing to stored procedure. now i want to execute this where clause in select query.

the input parameter is like this
@value = 'statusid=2 or statusid=3 or statusid=4'

select * from table1 where @value
i dont know how query the dynamically generated where.

Kindly help. Thanks in advance

Naina

Re: Generating dynamic where clause in stored procedure in sqlserver 2005

Robin_Roy19-Jul-09 21:32

Robin_Roy

19-Jul-09 21:32

Hello Naina,

You can use EXEC statement to execute your dynamic query inside your stored procedure....

Refer the exeample below...

CREATE PROCEDURE dbo.sproc_SampleProc
@strTableName VARCHAR(100),
@strColumnNames VARCHAR(255) --Can be comma seperated column names...
AS
BEGIN
SET NOCOUNT ON;

DECLARE @strDynamicQuery VARCHAR(500)

SET @strDynamicQuery = 'SELECT '+@strColumnName+' FROM '+@strTableName
EXEC (@strDynamicQuery)
END
GO

Enjoy Programming !!!

Robin

Re: Generating dynamic where clause in stored procedure in sqlserver 2005

Blue_Boy19-Jul-09 21:59

Blue_Boy

19-Jul-09 21:59

CREATE PROCEDURE dbo.spSelectData
@whereConditions VARCHAR(max)
AS
BEGIN

declare @selectqeury as nvarchar(max)
set @selectqeury='SELECT * from mytable where '+  @whereConditions
exec (@selectqeury)

END

Hope this will help you.

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

Re: Generating dynamic where clause in stored procedure in sqlserver 2005

--CELKO--20-Jul-09 8:44

--CELKO--

20-Jul-09 8:44

This is a very bad SQL programming technique. Read about the poor performance and dangers of SQL Injection. Since T-SQL can handle a long parameter list, use that instead.

CREATE PROCEDURE X (p01 INTEGER, p02 INTEGER, ..p99 INTEGER)
AS
BEGIN ..

SELECT ..
FROM ..
WHERE p IN (COALESCE (p01, 0), COALESCE (p02, 0), COALESCE (p99, 0)zzz0;

.. END;

There are better ways to write this in SQL-2008 and Standard, but the idea is to build a one-coolumn table from parameter and remove the NULLs. Also, by definitio, there is no such thing as a "status_id"; nan attribute can be an identifier or a status, but not both at once.

Re: Generating dynamic where clause in stored procedure in sqlserver 2005

PIEBALDconsult20-Jul-09 10:44

PIEBALDconsult

20-Jul-09 10:44

You may have better luck just using a parameterized command rather than a stored procedure.

Using Crystal Reprot 10 within MS Access 2003

aftab8318-Jul-09 0:44

aftab83

18-Jul-09 0:44

Hi
I am using MS Access 2003 as database and also as front end. I want to open crystal reprot on MS Access form using coding in access. I want to open report when a buton on form is clicked.
I m searching for this but could not find the solution

Re: Using Crystal Reprot 10 within MS Access 2003

Jerry Hammond18-Jul-09 6:34

Jerry Hammond

18-Jul-09 6:34

You're more than likely going to have to use VBA to accomplish this task.

I believe that you would be better served creating your reports by using Crystal Reports to hit the MS Access db than the way you seem to be approaching this problem. YMMV.

"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering

Re: Using Crystal Reprot 10 within MS Access 2003

Mycroft Holmes18-Jul-09 15:23

Mycroft Holmes

18-Jul-09 15:23

The mans a masochist, Access AND Crystal Reports, what a nightmare.

Never underestimate the power of human stupidity
RAH

Storing an array in a string or ...?

Ehsan Baghaki17-Jul-09 21:36

Ehsan Baghaki

17-Jul-09 21:36

I have a product table with 100000 products and also I have a user table with 5000 records. and suppose a user can give us feedback about any combination of this products. So suppose he chooses to compare products 1,3,100,200,400,500 and so on. (he can send us another feedback about some other products)

So here is my question, I just want to know what an expert database designer would think about this situation where the number of products and users are big. One way to store this ratings are in a single string like: 1#5 3#4 100#5 .... x#y means that he gave y stars to product with id of x. I can load this vote_string in my php script for example and extract the details of it.
So the rating table would likely have this structure: id, user_id, vote_string

Another way is to store this feedback in this structure:
vote Table: id, user_id, date
vote_details Table: vote_id, product_id, vote ==> (vote_id,products_id) as primary key and vote_id is foreign key.

(or may these 2 tables can be compressed in a single table like id,user_id,product_id,vote [id,user_id,product_id] as primary key)

So i guess it's very easier to query in the second design but it consumes more space and time for each query, also in case of product deletion it's quite better to use the second design. What would you do?

Any kind of idea is appreciated.

Re: Storing an array in a string or ...?

Luc Pattyn18-Jul-09 0:08

Luc Pattyn

18-Jul-09 0:08

All you need is a single votes Table: user_id, date, product_id, vote

And never store something that isn't a string in a string!

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles]

The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Show formatted code inside PRE tags, and give clear symptoms when describing a problem.

Re: Storing an array in a string or ...?

riced18-Jul-09 0:29

riced

18-Jul-09 0:29

I'd go with Luc's suggestion, it's spot on for what you need.
Would the string be able to handle the pathological case where a user rates every single product in the db?
Not likely but there are some strange people out there. Big Grin | :-D

Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis

Re: Storing an array in a string or ...?

Mycroft Holmes18-Jul-09 15:34

Mycroft Holmes

18-Jul-09 15:34

The first option is definitely NOT viable, you will never be able to query the product/vote value.

Your 2nd option is technically correct, if you need to manage the instance of a user/vote event then the 2 table structure saves you storing the userid and date multiple times and is therefore technically correct.

If you do not need to manage the vote event (when did user x vote) then you can use Luc's suggestion. Personally I would use your 2nd structure but them I'm a pedant where data is concerned having been bitten by lousy design in the past.

Never underestimate the power of human stupidity
RAH

Re: Storing an array in a string or ...?

Luc Pattyn18-Jul-09 18:03

Luc Pattyn

18-Jul-09 18:03

I don't mind a little pedant discussion, so here it goes.

For a simultaneous vote on N products the one-table approach takes N records of 4 fields each; the two-table approach takes N records of 3 fields each plus one record of 3 fields.

So the storage break-even would be where 4N=3N+3, hence N=3 (that is assuming IDs and dates have same cost). Therefore the 2-table approach would save space only for N>=3. How likely that is depends much on the correlation between products, and the kind of GUI used for voting.
Also selecting/searching/ordering the votes would take an extra join in the 2-table approach.

Having heard no real arguments in favor of 2-table (e.g. "we expect users to vote on an average of 5 products), I would call it over-normalized; to carry it somewhat further, one could come up with 3 tables, by listing all (product_id, vote) combinations in a third table. That would favor situations where some products always get high votes, and others always get low votes. However there are no facts to justify this approach.

Hence I'm with Einstein, who said something along the line "the simplest approach that covers it, is the right approach" or was it "you should simplify as much as possible, but no more". So I'll stick with the 1-table design.

Cheers.

Luc Pattyn [Forum Guidelines] [My Articles]

The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Show formatted code inside PRE tags, and give clear symptoms when describing a problem.

modified on Sunday, July 19, 2009 12:23 AM

Re: Storing an array in a string or ...?

Mycroft Holmes18-Jul-09 22:31

Mycroft Holmes

18-Jul-09 22:31

Luc Pattyn wrote:
So the storage break-even would be where 4N=3N+3

I don't think the storage size/fields is relevant these days, I was focused more on the normalisation issue of storing redundant data.

I don't agree with the over normalised point although the additional join has it's issue. Trying to get the number of times a user has voted from the single table structure would be more difficult than with the 2 table approach. I know it was not requested but I can hear his manager (or marketing) asking for the information about 3 weeks after the thing goes live and while the solution is trivial it does highlight the issue.

Nope I'd go for the 2 table approach but acknowledge that neither structure is perfect, thats where the art comes in and while my design may not suit all it would suit me.

Never underestimate the power of human stupidity
RAH

Re: Storing an array in a string or ...?

Luc Pattyn19-Jul-09 2:24

Luc Pattyn

19-Jul-09 2:24

Interesting. I'll keep your marketing remark in mind next time I'm faced with such decisions.

Cheers.

Luc Pattyn [Forum Guidelines] [My Articles]

The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Show formatted code inside PRE tags, and give clear symptoms when describing a problem.

Last Visit: 31-Dec-99 18:00 Last Update: 2-May-24 14:28

Refresh

ᐊ Prev 1...802 803 804 805806807 808 809 810 811 Next ᐅ

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