Database

Re: storing xml file in sqlserver

27-Dec-05 18:19

Can you tell me urgency to store whole file in sql server database.
Instead, u can save url of the file and file physically to some folder in server. Smile | :)

Any systematic work reflects its significance for a long time, Though I m not totally against "The Prototyping".So let's discuss in depth...

suneelbabu_net27-Dec-05 18:42

suneelbabu_net

27-Dec-05 18:42

Iam happy about your reply. thank u.

As i have two stored procedures in my project, so i want to get all details from one stored procedure and i want to send the information as input paramenter to the second stored procedure.

suneel

MS-SQL server Self join

Amit Malik25-Dec-05 19:11

Amit Malik

25-Dec-05 19:11

Hello,
This is urgent.
I have a Table ARTICLE_MASTER.
fields are item_code, activation_date.

I can different item_code for each article.
each article has a activation date.

select * from ARTICLE_MASTER
inner join
ARTICLE_MASTER ARTICLE_MASTER1
on ARTICLE_MASTER.item_code= ARTICLE_MASTER1.item_code
where ARTICLE_MASTER.activation_date < = getdate() and
ARTICLE_MASTER.end_date > = getdate() and
ARTICLE_MASTER.activation_date > ARTICLE_MASTER1.activation_date
order by article_master.item_code

I can have 4 rows for a single article - say 001- for Comb.
and for a given month say December, my acticle master can have 4 rows for article 001. with activation date and end date, for a single article i can have 4 rows.
say

ITEM_CODE               ACTIVATION_DATE            END_DATE        PRICE
001                     01/12/2005                 31/12/2005      15
001                     07/12/2005                 31/12/2005      20
001                     14/12/2005                 31/12/2005      25
001                     21/12/2005                 31/12/2005      20

SO After comparison with my current date i have to decide which price to apply.
say on 2nd dec price should be 15
on 8th dec price should be 20
on 15th dec price should be 25
on 21-22th dec price should be 20
so i need to pick the appropriate row for a given date.
The query i have written above gives only the latest one say on 16th still it gives me 21st dec's row.

Please give me a solution for it.
its urgent

Amit Malik

.NET Developer

Re: MS-SQL server Self join

Michael Potter27-Dec-05 3:22

Michael Potter

27-Dec-05 3:22

It may look something like this:

SELECT
     am1.* 
FROM 
     ARTICLE_MASTER am1
WHERE 
     getdate() BETWEEN am1.activation_date AND am1.end_date AND
     am1.activation_date = (SELECT 
                                 MIN(am2.activation_date)
                            FROM 
                                 ARTICLE_MASTER am2
                            WHERE 
                                 am2.item_code = am1.item_code AND
                                 getdate() BETWEEN am2.activation_date AND am2.end_date)
ORDER BY 
     item_code

delete trigger

Vipin.d25-Dec-05 17:16

Vipin.d

25-Dec-05 17:16

hi ,
i am new in mssql2000.
i have table named journal. before inserting dat into it , check that journal no. exist, if exist delete that journal and insert the new one.

is it do with trigger? then how?

with regards
vipin Smile | :)

vipin

Re: delete trigger

Colin Angus Mackay26-Dec-05 2:40

Re: need help about database

26-Dec-05 2:40

You could do this with a trigger. However, why delete the row? Why not update the existing row with the new data.

Personally, I do this with a stored procedure.

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius

need help about database

ngAnhTuan24-Dec-05 20:54

ngAnhTuan

24-Dec-05 20:54

hello everybody
i'm Tuan, i have just stated with database(SQL server 2000).i hardly knwon anything about database,if anyone who have a small program about database , please send to hepl me to do practice :How to creat database, add in with VB.net with component connection!
Thanks bro!!! Smile | :)

send to me at: anhtuantelecom@yahoo.com
-- modified at 2:54 Sunday 25th December, 2005

Colin Angus Mackay26-Dec-05 2:37

Re: need help about database

26-Dec-05 2:37

I would suggest that your best course of action would be to get a book on ADO.NET and one SQL Server 2000.

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius

Paul Conrad28-Dec-05 5:34

Paul Conrad

28-Dec-05 5:34

Or take a look around at Code Project. There are numerous articles that can help.

PL/SQL

sarojsneha24-Dec-05 19:40

sarojsneha

24-Dec-05 19:40

HOW TO DELETE A RECORDS FROM THE TABLE WHICH HAS THE FIELD SAY CSA WHICH IS NOT A NUMBER.
I.E IT SHOULD RETAIN THE RECORDS WHOSE CSA IS NUMBER???

Re: PL/SQL

rihdus26-Dec-05 1:29

26-Dec-05 1:29

use regular expressions to filter your numeric valued records

Any systematic work reflects its significance for a long time, Though I m not totally against "The Prototyping".So let's discuss in depth...

Re: PL/SQL

rihdus26-Dec-05 3:56

SQL DATENAME() QUESTION...

26-Dec-05 3:56

sorry for the delay, but i cannt sent the r.e. at this time.

well, there is no native support of regular xpressions in MS SQL server 2000
go to this link, http://www.sqlteam.com/item.asp?ItemID=13947
wish u sharp ur skills.

u need pl/sql function to filter numeric valued tuples.

if this is not a problem for always

you can apply this line of query(vb.net or vbscript)
dim sql as string
dim i as integer
dim maxlimit as integer

sql="delete from tblName where tblid not in (select tblid from tblName where csa in ("
for i=0 to maxlimit-1
sql & "'" & cstr(i) &"',"
next
sql & "'" & cstr(maxlimit) &"')"

NOTE: this code is not tested

try this out.

Wish you solve your problem Smile | :)

Any systematic work reflects its significance for a long time, Though I m not totally against "The Prototyping".So let's discuss in depth...

lior654#hotmail.com24-Dec-05 18:58

lior654#hotmail.com

24-Dec-05 18:58

I have a number representing a month:

DECLARE @MONTH INT
SET @MONTH = 5

How can I use the DATENAME() function to get
the name of the month?
I must have a full date variable to use this
and I don't know how to build a date using
@MONTH as its month...

PLEASE HELP...

Re: SQL DATENAME() QUESTION...

Colin Angus Mackay26-Dec-05 2:36

26-Dec-05 2:36

lior654#hotmail.com wrote:
How can I use the DATENAME() function to get
the name of the month?

DATENAME() can accept a varchar as the date, so you can just create a random date, with the month as you want:

DECLARE @somedate varchar(20);
SET @somedate = '2005-01-01';
SELECT datename(month,@somedate);

This will return "January".

However, I would recommend that user interface type things like getting a month name are best carried out in the UI layer leaving the data layer to get on with things that it is good at (like manipulating the data in the database)

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius

SQL Query

karthik_dotnet123-Dec-05 20:09

karthik_dotnet1

23-Dec-05 20:09

My Table has the following structure:
ID Name Description
1 A Desc1
2 A Desc2
3 A Desc3
4 B Desc1
5 B Desc2

The expected output is:
Name D1 D2 D3
A Desc1 Desc2 Desc3

How do i get the above result by SQL Query or Function and not using CURSORS.

Pls. help me out!!!

Re: SQL Query

Gunvant Patel26-Dec-05 22:58

Gunvant Patel

26-Dec-05 22:58

i want to create query from two different SQL Server 2000 from two different computer. then how i can access this command. please help me

Re: SQL Query

Colin Angus Mackay27-Dec-05 0:38

27-Dec-05 0:38

If you are going to ask a new question then you should start a new thread!

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius

Re: SQL Query

rihdus27-Dec-05 19:44

Create .Net SQL At Runtime

27-Dec-05 19:44

hi,
sorry for the delay
i want to help you, will you like to do some application side programming,
if you format your output in vb,C# etc.

its very easy,

but if you not interested in that, let me know, i can send u later Smile | :)

Any systematic work reflects its significance for a long time, Though I m not totally against "The Prototyping".So let's discuss in depth...

Steve4523-Dec-05 6:46

Steve45

23-Dec-05 6:46

Is there a free/simple SQL tool somewhere that will help me create a SELECT SQL statement at runtime. I perfer the code to be in C#. I'd like to pass some SQL SELECT parameters and have it return a SQL SELECT string.

Thanks

Steve

Colin Angus Mackay23-Dec-05 7:14

23-Dec-05 7:14

String.Format()?

I think you may need to explain what you are looking for a bit better because I don't quite understand what you want.

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius

Steve4523-Dec-05 7:47

Steve45

23-Dec-05 7:47

Thank You for repling

A need to "Push" a Crystal report with a DataSet.

The user opens a ADO.Net report selection form. The user makes selections on the form for the MS-Acess database report. I roll his selections into a SQL string (SELECT, FROM, WHERE and ORDER BY). Create the C# DataAdapter and DataSet and send the DataSet to the Crystal report (reportDocument()).

I'm looking for a C# class/method that I can pass the user selections to and it returns a SQL string like "SELECT...". Because I'm a non-profit it has to be free.

Hope this helps.

Steve

Colin Angus Mackay23-Dec-05 8:04

23-Dec-05 8:04

The simplest thing I can think of is not far off the String.Format() thing I flippantly suggested already. It looks like you need some code to build your SQL dynamically and a string builder could be used for that. (Remember that a SQL command is just a string that is sent to the database) There is a command builder for building SQL statements in .NET already, but I've never used it - it may have some useful methods that may help build a SQL statement dynamically without exposing you to the risk of SQL Injection Attacks (something you'd have to manage yourself if you were building the command by concatenating strings).

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius

Steve4523-Dec-05 13:33

Steve45

23-Dec-05 13:33

Thanks Colin

Two possible choices to evaluate for on-the fly SQL statements
http://www.easyobjects.net
http://www.reebsoftware.com

I think that command builder is for stored SQL procedures. Also, if you have VS do a Help search for SQL Pane - I think its for Access.

If I have to I'll reduce my report requirements and have 3 or 4 SQl templates and insert the parameters in the preformed SQL string.

string s = "SELECT " + field.text + "FROM " + "....";

Building dynamic SQL statement is an excellent choice for a Code Project code sample. Hope all this helps someone.

Thanks

Steve