Re: working with a DB over a network - Database Discussion Boards

Vipin.d15-Jun-07 23:25

15-Jun-07 23:25

HI i am using MSSQL 2000.

I ve some doubt in group by cluase or selecting data on a certain manner.

i ve 3 table
tbl1 contain fileds Refno(p),catId,remitId,Amt
egs: r1 c1 rm1 1000
r2 c2 rm2 300
r3 c1 rm2 2000
r4 c3 rm3 50
r5 c2 rm3 4000
r6 c1 rm2 500

tbl2 catId(p), catname

eg : c1 cat1
c2 cat2
c3 cat3
c4 cat4

tabl3 remitID(p),remitname
eg: rm1 remit1
rm2 remit2
rm3 remit3
i want to display data like
r1 cat1 remit1 1000
r3 cat1 remit2 2000
r6 cat1

like

i want group tthe data based on category id

how i do this?

is it

SELECT Refno,catname,remitname,Amt from
tbl1,tbl2,tbl3 where
tbl1.catId=tbl2.catId and tbl1.remitId=tbl3.remitId

GRoup by ??????

thanks in advance

All I ever wanted is what others have....
CrazySanker

Re: group by clause.

Arun.Immanuel16-Jun-07 1:07

Arun.Immanuel

16-Jun-07 1:07

I think you need to sort them like

<br />
SELECT Refno,catname,remitname,Amt from<br />
tbl1,tbl2,tbl3 where<br />
tbl1.catId=tbl2.catId and tbl1.remitId=tbl3.remitId<br />
order by catname <br />
<br />

Regards,
Arun Kumar.A

Re: group by clause.

Vipin.d16-Jun-07 20:59

Vipin.d

16-Jun-07 20:59

thank u. but when we use group by clause??

i am really confused with groupby clause and order by...
Anyway thank u

All I ever wanted is what others have....
CrazySanker

Re: group by clause.

Arun.Immanuel16-Jun-07 21:16

Arun.Immanuel

16-Jun-07 21:16

Vipin dev wrote:
when we use group by clause??

ORDER BY clause is used to sort the records based on certain fields.

GROUP BY clause is used to find SUM,AVG,MAX,etc.,
for each group of a field.

For more information Click Here[^]

Regards,
Arun Kumar.A

coping tables os Sql srver to folder

razanabanu15-Jun-07 22:58

razanabanu

15-Jun-07 22:58

Hi All,I would like to copy my database to my folder..how can I open that database.how can I do that?help me,please..

Hi,Im Razana

opening a db on network path?[sql servr 05]

giddy_guitarist15-Jun-07 22:37

giddy_guitarist

15-Jun-07 22:37

hi ,

ok i thought it was as simple as putting the path to the db into AttahDbFileName , but it does'nt work.

I get "<path+database> is on a network path and this is not supported"

I tried messing with the surface area cfg , no change. Could someone give me a pointer as to what i have to do to add a database that is on a network??

Also , The "server" computer that does have the database has SQLSERVER 05 exp. installed. But "client" computer that tries to connect to this "server" also has SQLSERVER 05 exp. installed , so maybe its using the wrong instance of the service?

My connection string looks like this:
"Data Source=.\SQLEXPRESS;AttachDbFilename=\\Gidlaptop\data (e)\Gid\My Creations\Hotel Management Studio\HotelManager.Core\bin\Debug \DataServices\HTLDATA.mdf;Integrated Security=True;User Instance=True";

[using C under .NET 2.0]

Thanks so much!

Gideon

Re: opening a db on network path?[sql servr 05]

originSH18-Jun-07 2:51

originSH

18-Jun-07 2:51

I'ts a very bad idea to have the files running over a network.

Whilst being about Sql Server 7, this [^] article has a good summary of the pains it can cause.

If you really want to do this then heres a link to the knowledge base article:

Description of support for network database files in SQL Server[^]

Re: working with a DB over a network

giddy_guitarist18-Jun-07 9:22

giddy_guitarist

18-Jun-07 9:22

Hi,

Thank you! for the reply, but because of my ms access background i did'nt realize how i need to connect to a database that is on a locally connected computer. I did'nt want to use the DB from a UNC path , i just wanted to connect to it! Big Grin | :-D

I thought , naively Sigh | :sigh:

, i could just use attachDbFile and specify the UNC location. But it seems i had to "Attach" the database on the "server" first[^]

Then i was told to use either one of these connection strings:
Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername; Password=myPassword; OR

Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;

(Where myDataBase is the name i used when i "Attached" the database.)

Also, i needed to allow remote connections.[^].(Dont forget to enable the sql browser service, its off by default)

I hope this helps someone else , if they had the same mis understading i had!

Gideon

Problem in recreating the table Full text catalog in sql server

AmitSumit15-Jun-07 21:45

AmitSumit

15-Jun-07 21:45

Hi,

Thanks in advance….

I have table and having Full text catalog implemented. I want to drop the Full text catalog by using script (Tsql) and then again generate the same Full text catalog on the same table.

I can drop the table Full text catalog successfully but when I execute the script to recreate the Full text catalog it through the following error.

## "File 'sysft_adm_groups_full_text_new1' cannot be reused until after the next BACKUP LOG operation."

Below are the script that I am using to recreate.

CREATE FULLTEXT CATALOG [adm_Test_full_text]
IN PATH N'C:\Program Files\Microsoft SQL Server\MSSQL.5\MSSQL\FTData'
AUTHORIZATION [dbo]

Thanks again

Amit

datepart function.....

Member 387988115-Jun-07 21:18

Member 3879881

15-Jun-07 21:18

Hi folowing my query is not considering the datepart function in where condition...,
How to make it proper plz any one hlp me...,

SELECT dbo.tblEmployeeMaster.empId,dbo.tblEmployeeMaster.empName,
dbo.tblMonthHourDetails.SDate, dbo.tblTimeCardmain.shift,
dbo.tblMonthHourDetails.AppWHours,dbo.tblMonthHourDetails.projectid
FROM dbo.tblEmployeeMaster INNER JOIN dbo.tblMonthHourDetails
ON dbo.tblEmployeeMaster.empId = dbo.tblMonthHourDetails.EmpId
INNER JOIN dbo.tblTimeCardmain
ON dbo.tblMonthHourDetails.EmpId = dbo.tblTimeCardmain.empid
AND dbo.tblMonthHourDetails.SDate = dbo.tblTimeCardmain.carddate
WHERE dbo.tblmonthhourdetails.projectid=100 And
(dbo.tblMonthHourDetails.MedicalLeave <> 0) OR
(dbo.tblMonthHourDetails.AnnualLeave <> 0)
and datepart(mm,sdate)=5 and datepart(yyyy,sdate)=2007

Advance thanks...,

Magi

Re: datepart function.....

Arun.Immanuel15-Jun-07 21:47

Arun.Immanuel

15-Jun-07 21:47

Magh_M wrote:
and datepart(mm,sdate)=5 and datepart(yyyy,sdate)=2007

It is because of And has higher precedence than Or.

Try this:

<br />
WHERE dbo.tblmonthhourdetails.projectid=100 And<br />
<big>(</big>(dbo.tblMonthHourDetails.MedicalLeave <> 0) OR<br />
(dbo.tblMonthHourDetails.AnnualLeave <> 0)<big>)</big><br />
and datepart(mm,sdate)=5 and datepart(yyyy,sdate)=2007

along with the column list and check the expression evaluates like you expect.

If you still find the same problem,
try to include the exressions
datepart(mm,sdate),datepart(yyyy,sdate)
along with the column list and check if the expressions evaluate like you expected.

Regards,
Arun Kumar.A

Re: datepart function.....

Member 387988116-Jun-07 1:20

Member 3879881

16-Jun-07 1:20

Thank u
Keep in touch

Magi

VB6

xxxxxzer15-Jun-07 12:25

xxxxxzer

15-Jun-07 12:25

Hi Can any one give the code for importing text file or excel sheet of say 10k records into sql database and then searching those 10k records with the existing database whether record exixt or not and give the status of records found in data grid.all this has to be developed from front end application in vb6.0..Please help

Thanks Sandeep

gbfbnhfgnbh

datetime difference

seemamltn15-Jun-07 11:33

seemamltn

15-Jun-07 11:33

Hi,

I have a table which has a field ItemsReceived of type datetime. I have a grid view which has two columns.

In first column i have to show the data from field ItemsReceived and in second column I have to show
difference between Currenttime and date from ItemReceived. How can I do that.

e'g

ItemRecieved Difference
6/13/2007 12:38am 1d 21h 45m
6/13/2007 3:54pm 1d 06h 10m
6/15/2007 12:26pm 34m

So the second coulmn displays the difference of current time minus ItemRecieved. the format shouild be <mintues>m,
or<hours>h, <mintues>m, or <days>d.

How can I do that.

Thanks in advance

seema

Search Facilities-Please Suggest

adnanrafiq15-Jun-07 10:22

adnanrafiq

15-Jun-07 10:22

Hi All,

I have to make search routine which can search against each field of the table, and user can search with these option example, there is a field Name in the table, user can select from combo "Equal","Contains","Starts With","End With" then type something in the text box, system willl search according.
I am thinking to use freetext search facilities of SQL server 2005, please confirm , can we implement search like explained above using freetext search.
if yes please suggest me an article, other option is obviously is like query.
Second Question
and one more thing, each record will have a pdf document, i m intended to store these doc into the database, and give search facilitess against them as well, please suggest from where i have to start to store pdf doc in binary, and give search facilites against the docs.

Please take in cosideration , that there is thousands of records in each table.and document size can be upto 50MB.

Any help will be highly appreciated.

Many Thanks & Best Regards,
Adnan

Many Thanks,
Adnan Rafiq
muhammadadnanrafiq@gmail.com

Delete the rows

seemamltn15-Jun-07 8:04

seemamltn

15-Jun-07 8:04

How can I delete last 20 rows or records from a table.

seema

Re: Delete the rows

snorkie15-Jun-07 9:46

snorkie

15-Jun-07 9:46

If you have an ID column on the table, you could use the following.

DELETE FROM Table
WHERE ID IN
(
SELECT TOP 20 ID
FROM Table
ORDER BY ID DESC
)

Hogan

Index server

originSH15-Jun-07 5:00

originSH

15-Jun-07 5:00

How would this WHERE caluse return no results?

WHERE UniqueID = 'C7B411C6-E17D-454a-9B7B-1A170F33A072'
OR UniqueID != 'C7B411C6-E17D-454a-9B7B-1A170F33A072'

There are items in the IS catalog, if i remove the WHERE clause they are returned. If I do a similar query using Contains() then its fine too.

WHERE Contains(UniqueID,'C7B411C6-E17D-454a-9B7B-1A170F33A072')

Now I don't want to jump the gun and suggest there is something fundimentally wrong here without fully understanding IS and its Sql Query language but equality is pretty basic, it's either equal to or not equal to.

Is this a case where FILE_NOT_FOUND might come in?

(WTF FTW Laugh | :laugh:

)

Any insight into this wierd behaviour would be great.

Andy

EDIT: Forgot to mention this is on Windows Xp Pro, where UniqueID is a cached VT_LPSTR, 40 length.
(Yes I know I cross posted ... naughty me :P but I'm really not sure usre how much user cross over the OS and Sql forums get Wink | ;)

)

NULL vs '' (Nothing)

dboy22115-Jun-07 4:24

dboy221

15-Jun-07 4:24

I have a sql statement where I am trying to pull all records that have a value as well as those records that do not have a value for a particular attribute in a table. To pull the values that were Null I used the following condition "OR cd.BCAT IS NULL" (where BCAT is the attribute) When adding this condition to my sql statement none of the records that have NULL values are returned. When checking the design of the table in SQL Server, I noticed that whoever set the table up, specified the default value for this particular attribute to '' (which I assume is nothing as opposed to null). Knowing this I changed the condition to "OR cd.BCAT IS ''". However, when doing this it seems like the query takes for ever, does not return anything, and basically hangs up. Is there a difference between NULL and ''. If so how can I return records that have '' as the value? Thanks in advance to all that reply.

Re: NULL vs '' (Nothing)

Pete O'Hanlon15-Jun-07 4:30

Pete O'Hanlon

15-Jun-07 4:30

NULL is the absence of a value. '' is a value - an empty string. Basically, if you have something which does not have a valid value, e.g. an optional date, you would normally use a nullable column. This means that you don't have to store a "magic" value to identify these values.

Deja View - the feeling that you've seen this post before.

Re: NULL vs '' (Nothing)

dboy22115-Jun-07 4:46

dboy221

15-Jun-07 4:46

Thanks Pete. Making the column's default value NULL should make my sql statement work. However, w/o jeopardizing the integrity of the database (i.e. relationships, data), I am assuming that making this change will only affect the values in this table (and not update other tables where this value may be referenced). Secondly will I need to do an update on existing records such that they now incorporate the NULL value or will making this change apply it to existing records? Is there absolutely any way to check for ''? (if not I will try the suggested) Thanks, again.

Re: NULL vs '' (Nothing)

Pete O'Hanlon15-Jun-07 5:13

Pete O'Hanlon

15-Jun-07 5:13

Alternatively you could just add WHERE (column IS NULL or column = '') to your query. It's not pretty, but it would work.

Deja View - the feeling that you've seen this post before.

Re: NULL vs '' (Nothing)

andyharman15-Jun-07 7:38

andyharman

15-Jun-07 7:38

Or WHERE IsNull(column, '') = ''

Re: NULL vs '' (Nothing)

PIEBALDconsult15-Jun-07 4:32

PIEBALDconsult

15-Jun-07 4:32

dboy221 wrote:
"OR cd.BCAT IS ''".

Perhaps you mean OR cd.BCAT = ''?

HELP PLEase with SQL form data entry

theimmortalmoo15-Jun-07 4:21

theimmortalmoo

15-Jun-07 4:21

my boss sent me this, I dont know how to do any kinda SQL

Hey Danny, can you make a data entry form so Jane can type customers' email addresses into our database? It needs to have two fields: store number and email address, and a submit button that inserts into sutherlands_mailing_list. Can you save it as www.webice.net/addresses.asp

and the second email she sent

Either way - you're connecting to a table on the SQL server, which is called sutherlands_mailing_list, not an access database. Look at the data entry pages on www.sutherlandshomedesigncenter.com/manager to see how to connect. Your connection string is already built if you use the file include 'database_connect' - you basically just have to write a simple SQL INSERT statement.

can anyone help me with this?

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