|
declare <cursor_name> cursor for
open cursor_name
while @@fetch_status=0
begin
fetch next from cursor_name into variable_name1,var2......varn
end
If U Get Errors U Will Learn
If U Don't Get Errors U Have Learnt
|
|
|
|
|
Sophia Rekhi wrote: i want to know that "how to write triggers in stored procedures" is it possible?
A trigger is a stored procedure of sorts that is run automatically on an event, such as data being inserted into a table.
I don't understand the benefit of writing one in a stored procedure? I also doubt if it is possible. You could write some dynamic SQL and execute that in order to create a trigger from within a stored procedure, but wouldn't it be better just to write the trigger and be done with it?
|
|
|
|
|
i have 3com router and i have Puplic IP address
Nat is Enabled and i configer it like 81.10.29.106:Defule sqlserver port
how to configer sql clint to creat connection Between sql server and sql clint
and thnx
Ahmed El-Badry
|
|
|
|
|
The Server (or Data Source) component of the connection string should contain the IP address and instance name if applicable and Port number
e.g.
81.10.29.106/MyInstance:1433
If you are running the default instance then just the IP and Port.
NOTE: You must also configure SQL Server to accept TCP/IP connection. And you must ensure that all firewalls between the client and server will allow the traffic through. As exposing a SQL Server directly to the internet is a dangerous thing to do it is not normal practice. Some ISPs may automatically block the ports SQL Server uses.
|
|
|
|
|
Enter the IP Address where u Write the Server name.
and give the some information .
|
|
|
|
|
I am inserting Data from one form into two linked tables but i don't know the sql syntax for such a query, plz send me an example.
yasso
|
|
|
|
|
|
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
|
|
|
|
|
Why can't you use 2 separate insert statements?
Regards,
Arun Kumar.A
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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?
|
|
|
|
|
what SQL server is this??
L.W.C. Nirosh.
Colombo,
Sri Lanka.
|
|
|
|
|
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  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.
|
|
|
|
|
i m inserting unicode character from c# to sql server 2005 but sql server is not supporting the unicode format.
|
|
|
|
|
Please do not cross post.
I replied you in the C# forum
|
|
|
|
|
Really, is the column an NVARCHAR? are all the parameters and variable types that hold the character an NVARCHAR?
|
|
|
|
|
and is your database collation is set properly?
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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)
|
|
|
|
|
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
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
