Composite Primary keys - A question - Database Discussion Boards

Re: hi...How to store datas temp in database

SHatchard5-Jul-07 0:24

5-Jul-07 0:24

You can create a temporary table by prefixing the name with # which which create a table for that user in the TempDb database on the SQL server, meaning that each user will have their own copy

You can also do global temporary tables as well if you need to.

Read more about this here[^]

Re: hi...How to store datas temp in database

Leah_Garrett5-Jul-07 0:48

Leah_Garrett

5-Jul-07 0:48

He could have meant a table variable.

http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html[^]

SQL Database Backup

allan.cawood4-Jul-07 23:27

allan.cawood

4-Jul-07 23:27

Hi,

I'm trying to write a small application in VB6 to backup and restore databases. I have looked up the code in the SQL Server Books Online but it keeps on giving me an SQL error.

I have tried 2 different forms of code ... see below with the error.

1)
SQL
USE master
EXEC sp_addumpdevice 'disk', 'APDB', DISK ='C:\SQLBCKUTIL\DatabaseBackups\05Jul2007_101009_APDB.dat', 2

ERROR
Incorrect syntax near the keyword 'DISK'.

2)
SQL
BACKUP DATABASE APDB
TO DISK = 'C:\SQLBCKUTIL\DatabaseBackups\05Jul2007_101009_APDB.BAK'
WITH FORMAT,
NAME = 'Full Backup of APDB'

ERROR
Cannot open backup device 'C:\SQLBCKUTIL\DatabaseBackups\05Jul2007_101009_APCON.BAK'. Device error or device off-line. See the SQL Server error log for more details.
BACKUP DATABASE is terminating abnormally.

Anyone have any ideas?

Cheers for now,
Allan

PEBKAC
Problem Exists Between Keyboard And Chair

Re: SQL Database Backup

Paul Conrad13-Jul-07 11:55

Paul Conrad

13-Jul-07 11:55

Have you found a solution to your problem?

"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus

Re: SQL Database Backup

allan.cawood15-Jul-07 20:01

allan.cawood

15-Jul-07 20:01

unfortunately not.

any suggestions????

Cheers for now,
Allan

PEBKAC
Problem Exists Between Keyboard And Chair

Re: SQL Database Backup

Paul Conrad15-Jul-07 20:11

Paul Conrad

15-Jul-07 20:11

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx[^] looks like it could be helpful. Not sure if you have already seen this. Looks from your original post that you've tried stored procs?

"The clue train passed his station without stopping." - John Simmons / outlaw programmer

SDF files

ShuklaGirish4-Jul-07 23:03

ShuklaGirish

4-Jul-07 23:03

Hi,
can anyone tell me how to genrate sdf files in sql 7.0.
I had search this in google but i havent got proper answer.
So,can anyone help me in this.
Thanks.

GIRISH

Composite Primary keys - A question

vagl4-Jul-07 23:00

vagl

4-Jul-07 23:00

Hi,

I have question on usage of composite primary keys. I know it is suggested not to use them for performance reasons (wide indexes). But when it comes to enforcing the integrity constraints, composite keys help me.

Scenario:
I have the following tables.
Projects and Employees.

These tables share a m:n relationship and there is an intermediary table called "ProjectAssignments" with the following structure.

ProjectID, EmployeeID, EmpRole, AssignedBy, AssignedOn. (Primary Key: ProjectID & EmployeeID)

Employees with reviewer role, review the tasks of their reviewees on the specified date. This is captured with the following structure.

ReviewID, ProjectID, EmployeeID_Reviewer, EmployeeID_Reviewee, ReviewDate, TaskDesc. (PrimaryKey: ReviewID)

I would like the design to enforce the constraint that reviewer and reviewee are on the same project. I have defined the foreign keys as follows.

FOREIGN KEY([PRJ_ID], [EMP_ID_Reviewee]) REFERENCES [dbo].[ProjectAssignments] ([AGN_PRJ_ID], [AGN_EMP_ID])
FOREIGN KEY([PRJ_ID], [EMP_ID_Reviewer]) REFERENCES [dbo].[ProjectAssignments] ([AGN_PRJ_ID], [AGN_EMP_ID])

Is this is a good design?

The other alternative I thought has the following design. But, this does not enforce the above mentioned constraint.

Table ProjectAssignments: AssignID, ProjectID, EmployeeID, EmpRole, AssignedBy, AssignedOn. (Primary Key: AssignID)
(Unique Constraint: ProjectID, EmployeeID)

Table ProjectReviews:
ReviewID, AssignID_Reviewer, AssignIDReviewee, ReviewDate, ReviewTask. (Primary Key: ReviewID)

Which one should I prefer and why? I Unsure | :~

Thanks,
AGL

Re: Composite Primary keys - A question

Michael Potter5-Jul-07 5:01

Michael Potter

5-Jul-07 5:01

I prefer the second one. I assume that the reviewer is reviewing an employees actions on a particular project so I would revamp the ProjectReviews to look like this:

ReviewID (PK),
AssignID (FK - ProjectAssignment),
ReviewerEmployeeID (FK - Employee),
RevieweeEmployeeID (FK - Employee),
ReviewDate,
ReviewTask

The reason I would choose your second method is for maintenance reasons. I could look at the column structures and pretty much tell what links to what without having look too deeply into the FK constrants. This makes things much easier 2 years down the road.

I would enforce the relationship of the Reviewer/reviewee & AssignId in the ProjectReviews via a stored proceedure (or trigger). You could add deeper FK relationships to manage this for you but, the nature of the data does not seem to make it necessary. If a reviewer gets hit by a car and is removed from a project, is it necessary to remove all of his/her reviews? Only you can answer questions like that.

If you are expecting millions of records, then the second method will execute joins faster because of the smaller keys. If your database is small, the time saved will not be noticeable.

Re: Composite Primary keys - A question

vagl5-Jul-07 19:59

vagl

5-Jul-07 19:59

Thanks Mike. As you suggested, I chose the second option.

syntax error in insert into statement

magedhv4-Jul-07 22:53

magedhv

4-Jul-07 22:53

this is the msg that show when iam trying to apply this code

Dim f1, f2, f3, f4, f5, f6, f7, f8 As String<br />
<br />
f1 = "#" & DateTimePicker1.Value & "#,"<br />
<br />
f2 = "'" & ComboBox1.Text & "',"<br />
<br />
f3 = "'" & ComboBox2.Text & "',"<br />
<br />
f4 = "'" & TextBox1.Text & "',"<br />
<br />
f5 = "'" & TextBox2.Text & "',"<br />
<br />
f6 = "'" & TextBox3.Text & "',"<br />
<br />
f7 = "'" & TextBox4.Text & "',"<br />
<br />
f8 = "'" & Label10.Text & "'"<br />
<br />
s = "insert into sale"<br />
s = s & " (dte,op,nos,cost,coms,nii,npp)values "<br />
<br />
s = s & "(" & f1 & "," & f2 & "," & f3 & "," & f4 & "," & "," & f5 & "," & "," & f6 & "," & f7 & "')"<br />
<br />
cmd.CommandText = s<br />
cmd.ExecuteNonQuery()<br />
MsgBox("Saved succssefuly")<br />
<br />

any suggestion ?
plz help urgent

Re: syntax error in insert into statement

Krish - KP4-Jul-07 23:09

Krish - KP

4-Jul-07 23:09

dte column looks datetime datatype

magedhv wrote:
s = s & "(" & f1 & "," & f2 & "," & f3 & "," & f4 & "," & "," & f5 & "," & "," & f6 & "," & f7 & "')"

change the query to

s = s & "('" & f1 & "'," & f2 & "," & f3 & "," & f4 & "," & "," & f5 & "," & "," & f6 & "," & f7 & "')"

so that the date is incloded in single quotes

Regards
KP

Improving the database performance

S.Rajeshwar4-Jul-07 22:21

S.Rajeshwar

4-Jul-07 22:21

Hi all,

i am using SQL server 2000 , i have created 3 database. Among these 3 one database is running slow.for each database atmost there will be 40 connections open & remaining databases are running fast.

Can any body help me to solve this problem.

Thanks

Raju.

Re: Improving the database performance

Pete O'Hanlon4-Jul-07 22:32

Pete O'Hanlon

4-Jul-07 22:32

Wow. A wide ranging query in which you have provided virtually no useful information.

Try these articles to get you started?

Performance part 1[^]
Performance part 2[^]

Please visit http://www.readytogiveup.com/ and do something special today.
Deja View - the feeling that you've seen this post before.

Re: Improving the database performance

N a v a n e e t h4-Jul-07 22:50

N a v a n e e t h

4-Jul-07 22:50

raju_reddy wrote:
for each database atmost there will be 40 connections open

Won't you close connection after database operations ?

My Website | Ask smart questions

Re: Improving the database performance

andyharman4-Jul-07 22:51

andyharman

4-Jul-07 22:51

Following-on from Pete's reply, you might want use the sp_who2 stored procedure to check if any users hold locks that are blocking other users.

sql stored procedure

harithadotnet4-Jul-07 21:18

harithadotnet

4-Jul-07 21:18

HI
How to use insert and update query in a single stored procedure

if u know plz let me know too

Thanks in advance

Haritha

Re: sql stored procedure

Krish - KP4-Jul-07 21:24

Krish - KP

4-Jul-07 21:24

what exactly you are looking for

procedure can be created with both insert & update statements one after other.

Regards
KP

Re: sql stored procedure

N a v a n e e t h4-Jul-07 22:24

N a v a n e e t h

4-Jul-07 22:24

Hi
You can write any queries in stored procedures. Your procedure could be some thing like this

CREATE PROCEDURE InsertUpdateProcedure
AS

INSERT INTO TABLE VALUES(VALUE)

UPDATE TABLE SET COLUMN = VALUE WHERE CONDITION

GO

First insertion will be done and after that updation

Happy Programming

My Website | Ask smart questions

Have problem with displaying columns as rows

masyarial4-Jul-07 20:35

masyarial

4-Jul-07 20:35

I have have 2 table on my database,
table 1
nama test_type
-- --
table 2
test_type value
-- --

I'm doing sql command and the result is correct, below:
name test_type value
a 1A 0.01
a 2A 0.1
a 3A 0.03
b 1A 0.2
b 2A 0.2
b 3A 0.05

But, how can I display as below (assume that table 1 and 2 is read only)

name 1A 2A 3A
a 0.01 0.1 0.03
b 0.2 0.2 0.05

Thanks lot !!!

Re: Have problem with displaying columns as rows

Krish - KP4-Jul-07 21:22

Krish - KP

4-Jul-07 21:22

SELECT name,
CASE WHEN test_type = '1A' THEN value ELSE 0 END AS '1A',
CASE WHEN test_type = '2A' THEN value ELSE 0 END AS '2A',
CASE WHEN test_type = '3A' THEN value ELSE 0 END AS '3A'
FROM tableA

works for SQL Server

Regards
KP

Re: Have problem with displaying columns as rows

masyarial5-Jul-07 1:39

masyarial

5-Jul-07 1:39

Hi Krish, after I run your code here the result:

name 1A 2A 3A
a 0.01 0 0
a 2A 0.1 0
a 3A 0 0.03
b 0.2 0 0
b 0 0.2 0
b 0 0 0.05

so, there is any code for displaying table like below?:
I still can't find it.
name 1A 2A 3A
a 0.01 0.1 0.03
b 0.2 0.2 0.05

Thanks a lot !!!

Re: Have problem with displaying columns as rows

Krish - KP9-Jul-07 22:23

Krish - KP

9-Jul-07 22:23

Yes is possible actually i've missed out using group by

SELECT name,
Sum(CASE WHEN test_type = '1A' THEN value ELSE 0 END) AS '1A',
Sum(CASE WHEN test_type = '2A' THEN value ELSE 0 END) AS '2A',
Sum(CASE WHEN test_type = '3A' THEN value ELSE 0 END) AS '3A'
FROM tableA
GROUP BY name

regret for delay. was not in station

Regards
KP

Re: Have problem with displaying columns as rows

masyarial10-Jul-07 1:09

masyarial

10-Jul-07 1:09

Ok Krish, it's work fine, thanks !!!

BTW, it's not work if value on decimal and
data type is character right ??? so data type must in float
or another type decimal.

I'm still looking on internet for tsql converting
character into float, still didn't found it.

But, your help much welcome.

B.regards

Re: Have problem with displaying columns as rows

Krish - KP10-Jul-07 17:33

Krish - KP

10-Jul-07 17:33

CAST ('123.234' AS Decimal(10, 2))

CONVERT(Decimal(10, 2), '123.234')

Regards
KP

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