Database

Arun.Immanuel5-May-07 22:38

5-May-07 22:38

Please explain clearly what you need. Confused | :confused:

Regards,
Arun Kumar.A

yasso_1155-May-07 22:55

yasso_115

5-May-07 22:55

Hi Arun thnx for being interested.
As i told u i want the SQL insert query syntax to insert data into two linked tables.

lets say this is the syntax to insert data into one table
INSERT INTO TABLE1(column1, column2)
VAlUES (x, y)

whats is the syntax to insert data into two linked tables or more.

thanx for ur help

yasso

Arun.Immanuel5-May-07 23:15

5-May-07 23:15

Why can't you use 2 separate insert statements?

Regards,
Arun Kumar.A

yasso_1155-May-07 23:43

yasso_115

5-May-07 23:43

i used them but its gave a syntax error. Look this is what am going to do:
I am inserting data from one form into two linked tables "Student" and "Grade" linked together by the Grade_ID column which is a primary key in the Grade table and a forgien key in the Student table. After i insert the data i go to the database to see that the primary key
in the Grade table is inserted and its ok but the forgien key in the student table is still null. How can i link them together so that the forgien key in the Student table can take the same value as that in the grade table.

yasso

Arun.Immanuel6-May-07 1:26

Fulltext catalog not populating?

6-May-07 1:26

How can you say that the foreign key column will be updated automatically
when the primary key column is updated?

I think you have to use two insert statements like:

insert into Student(GradeID,OthercolumnName) values(GradeValue,OthercolumnValue)

insert into Grade(GradeID,OthercolumnName) values(GradeValue,OthercolumnValue).

Regards,
Arun Kumar.A

alexjamesbrown5-May-07 17:36

alexjamesbrown

5-May-07 17:36

I execute the following:

EXEC sp_fulltext_catalog 'DescriptionsOfProducts', 'create'

EXEC sp_fulltext_table 'tblProducts', 'create', 'DescriptionsOfProducts', 'PK__tblProducts__50FB042B'

EXEC sp_fulltext_column 'tblProducts','ProductName','add'
EXEC sp_fulltext_column 'tblProducts','ProductDescription','add'

EXEC sp_fulltext_table 'tblProducts','activate'

EXEC sp_fulltext_catalog 'DescriptionsOfProducts', 'start_full'

On my local pc it works fine... populates the fulltext catalog just great.
however on my web server, it creates everything correctly, however has an item count of 0???

i cannot find whats going on! the databases are identical, both in structure, indexes and data.

any ideas?

Re: Fulltext catalog not populating?

Nirosh6-May-07 4:48

Nirosh

6-May-07 4:48

what SQL server is this??

L.W.C. Nirosh.
Colombo,
Sri Lanka.

How robust the SQL Server 2005 is..?

Nirosh5-May-07 8:50

Nirosh

5-May-07 8:50

Hi All,

I am planing to use SQL server fulltext search and xml to store data of a
ecommerce site (mainly its products).. the site is having about 20 million
products.. I am going to store all the product in xml type.. and I need to
search them extensively.... The site going to have lots of users .. I am
assuming about 500000 page views per hour.. but all these will not hit the
search button .. (we use cache to reduce the database hits as much as
possible)

In a scenario like this .. is it a good approach to use xml ?

Is the SQL fulltext search can perform well here, in simple term is the SQL
server the choice here??

Please give me your honest Smile | :)

answers I need to make a choice here..

Also if you suggest me to go with SQL please attempt to assist me with some links where
I can find tips to improve the SQL server performances to hold on to this heavy load..

L.W.C. Nirosh.
Colombo,
Sri Lanka.

how to insert unicode character into SQL server 2005 from c#

rehab14-May-07 22:00

rehab1

4-May-07 22:00

i m inserting unicode character from c# to sql server 2005 but sql server is not supporting the unicode format.

Re: how to insert unicode character into SQL server 2005 from c#

Hesham Amin4-May-07 22:29

Hesham Amin

4-May-07 22:29

Please do not cross post.
I replied you in the C# forum

Hesham A. Amin
My blog

Re: how to insert unicode character into SQL server 2005 from c#

Colin Angus Mackay4-May-07 22:30

Colin Angus Mackay

4-May-07 22:30

Really, is the column an NVARCHAR? are all the parameters and variable types that hold the character an NVARCHAR?

Re: how to insert unicode character into SQL server 2005 from c#

subai5-May-07 4:18

subai

5-May-07 4:18

and is your database collation is set properly?

Re: how to insert unicode character into SQL server 2005 from c#

Software_Guy_1236-May-07 2:58

Software_Guy_123

6-May-07 2:58

Hello Subai,

Datatype should be Nvarchar or Ntext. While inserting from C# or VB.Net prefix N with variables.

i.e. sp name is like - 'usp_add_customer_details' this sp accepts company id and customer name(this is multilanguage).
While calling that SP you should write code like

dim strSql as string
strSql = 'usp_add_customer_details '"& strCompId &"', N'"& strCustName &"''

Note : here N is prefixed with strcustName.

I hope this will work for you.

Thanks & Regards,
Kumar

Incorrect query result onINNER JOINS over three tables

Joe Smith IX4-May-07 17:21

Re: Incorrect query result onINNER JOINS over three tables

4-May-07 17:21

Hi, I have 3 tables below. How can I query total payment and prod_principal group by date?
This is what I have, but it results incorrect, why? Help please, anyone? Thanks.

SELECT    DAY(shift.date) AS day1, 
          MONTH(shift.date) AS month1, 
          YEAR(shift.date) AS year1, 
          SUM(invoice.payment) AS sum1, 
          SUM(invoice_details.prod_principal) AS sum2
FROM      shift INNER JOIN
            invoice ON shift.id = invoice.shift_id INNER JOIN
            invoice_details ON invoice.id = invoice_details.invoice_id
GROUP BY DAY(shift.date), MONTH(shift.date), YEAR(shift.date)
ORDER BY year1, month1, day1

shift   invoice    invoice_details
-----   --------   ---------------
id      id         invoice_id
date    shift_id   prod_id
        payment    prod_principal

Arun.Immanuel4-May-07 20:25

Re: Incorrect query result onINNER JOINS over three tables

4-May-07 20:25

Try this:

select DAY(A.date) AS day1,
MONTH(A.date) AS month1,
YEAR(A.date) AS year1,
SUM(A.payment) AS sum1,
SUM(A.prod_principal) AS sum2 From
(select * from shift,invoice,invoice_details
where shift.id=invoice.shift_id and invoice.id=invoice_details.invoice_id
)
A Group by DAY(A.date), MONTH(A.date), YEAR(A.date)
ORDER BY year1, month1, day1

Regards,
Arun Kumar.A

Joe Smith IX5-May-07 0:45

Re: Incorrect query result onINNER JOINS over three tables

5-May-07 0:45

I tried it, but the result is exactly the same as my original query.

the reason i know it's wrong is because sum1 is not the same as queried from the following:

SELECT     DAY(shift.date) AS day1, 
           MONTH(shift.date) AS month1, 
           YEAR(shift.date) AS year1, 
           SUM(invoice.payment) AS sum1
FROM       shift, invoice
WHERE      shift.id = invoice.shift_id
GROUP BY DAY(shift.date), 
         MONTH(shift.date), 
         YEAR(shift.date)
ORDER BY year1, month1, day1

maybe i should specify these:
- there are many invoices in one shift (having the same shift_id)
- there are many invoice_details in one invoice (having the same invoice_id)

Please help, thanks.

Arun.Immanuel5-May-07 1:07

Re: Incorrect query result onINNER JOINS over three tables

5-May-07 1:07

I do not know much about invoice.

Can U provide 4(or minumum) rows of data for each table
and the output U exactly need.

May be some other person, who know the solution but cannot understand Ur need
will help U.

Regards,
Arun Kumar.A

Joe Smith IX5-May-07 2:04

Re: Incorrect query result onINNER JOINS over three tables

5-May-07 2:04

here are some examples. i hope it's enough. thanks.

shift: id  date
       --  ----------
       1   05/01/2007
       2   05/02/2007
 
invoice: id  shift_id  payment
         --  --------  -------
         1   1         100
         2   1         250
         3   2         375
         4   2         210
         5   2         333
          
invoice_details: invoice_id  prod_id  prod_principal
                 ----------  -------  --------------
                 1           101      30
                 1           156      55
                 2           258      180
                 3           147      100
                 3           268      225
                 4           251      60
                 4           369      35
                 4           158      88
                 5           125      100
                 5           395      50
                 5           158      65
                 5           228      33
                 5           358      20

the query i want:

day1  month1  year1  sum1  sum2
----  ------  -----  ----  ----
1     5       2007   350   265
2     5       2007   918   776
 
note: 350 = 100+250      (total payment on 05/01/2007)
      918 = 375+210+333  (total payment on 05/02/2007)
      265 = 30+55+180    (total prod_principal on 05/01/2007)
      776 = 100+...+20   (total prod_principal on 05/02/2007)

Arun.Immanuel5-May-07 2:29

Re: Incorrect query result onINNER JOINS over three tables

5-May-07 2:29

Use this for the moment, untill you find better solution.

SELECT     DAY(shift.date) AS day1, 
           MONTH(shift.date) AS month1, 
           YEAR(shift.date) AS year1, 
           C.SumPay AS sum1,
	   C.SumPri AS Sum2		
FROM       shift ,

(select shift_id,sum(payment) "SumPay" ,sum(A.sumPrincipal) "SumPri"  from invoice B,

(select invoice_id,sum(prod_principal) "sumPrincipal" from invoice_details group by invoice_id) A

where B.id=A.invoice_id group by B.shift_id) C

where shift.id=C.shift_id

Regards,
Arun Kumar.A

Joe Smith IX5-May-07 4:42