Re: Best Approach For Designing Database - Database Discussion Boards

muya mwansa27-Nov-12 6:46

27-Nov-12 6:46

Hi, I was given a tricky challenge question to attempt and allowed the use of any resources at my disposal. So far all my attempts have produced results close to the expected output but not quiet the expected out put. Here's the link to the question: Question and here's where you can test your query: test here.

Here's my query that produced the closest results:

SELECT        DISTINCT Competencies.CompName AS compname, Users.FirstName AS firstname, Users.SurName AS surname, CompPos.Level AS positionlevel, CompUser.Level AS userlevel
FROM            Positions INNER JOIN CompPos 
				ON Positions.PosId = CompPos.PosID 
				INNER JOIN Competencies   
				ON CompPos.CompID = Competencies.CompID
				INNER JOIN CompUser
				ON Competencies.CompID = CompUser.CompID	
				CROSS JOIN Users
WHERE 		(Users.UserId IN ('999209', 'helpdesk1', '999339', 'helpdesk5', '999827'))
AND        (CompUser.UserID IN ('999209', 'helpdesk1', '999339', 'helpdesk5', '999827'))
AND 		(Positions.Position LIKE '%Corp HR-People Development-Wellness-Manager(Process)%')
ORDER BY CompPos.Level DESC, Competencies.CompName, CompUser.Level, Users.FirstName, Users.SurName

Re: Need help with a tricky MS SQL Query

Eddy Vluggen27-Nov-12 13:31

Eddy Vluggen

27-Nov-12 13:31

Website wrote:
I only want to see results for users with the following userids '999209','helpdesk1','999339','helpdesk5','999827'

Even if they don’t have records in compuser.

"Want"? How about learning SQL, breaking the problem into smaller pieces?

..and no, it's generally not a good idea to have a server open like that; it's obvious that you're running Sql Server, and one can request a list of all the databases in there.

Ever heard of Bobby Tables[^]? "All input is evil until proven otherwise".

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]
They hate us for our freedom![^]

Re: Need help with a tricky MS SQL Query

jschell28-Nov-12 8:43

jschell

28-Nov-12 8:43

Eddy Vluggen wrote:
..and no, it's generally not a good idea to have a server open like that; it's
obvious that you're running Sql Server, and one can request a list of all the
databases in there.

From the question I would guess that the OP doesn't own the database server.

Re: Need help with a tricky MS SQL Query

Deepak Kr128-Nov-12 1:03

Deepak Kr1

28-Nov-12 1:03

Please try this one, if this helps, u might need to do some modification.
=============
select Competencies.CompName AS compname, Users.FirstName AS firstname, Users.SurName AS surname, CompPos.Level AS positionlevel, CompUser.Level AS userlevel from Users
left outer join compuser
on users.userid = compuser.userid
left outer join Competencies
on compuser.compid = Competencies.compid
left outer join CompPos
on CompPos.compid = Competencies.compid
left outer join Positions
on Positions.posid = CompPos.posid and Positions.Position LIKE '%Corp HR-People Development-Wellness-Manager(Process)%'
WHERE Users.UserId IN ('999209', 'helpdesk1', '999339', 'helpdesk5', '999827')
--and Positions.Position LIKE '%Corp HR-People Development-Wellness-Manager(Process)%'
ORDER BY CompPos.Level DESC, Competencies.CompName, CompUser.Level, Users.FirstName, Users.SurName

Best Approach For Designing Database

AmbiguousName27-Nov-12 3:59

AmbiguousName

27-Nov-12 3:59

Hello. I am developing a small project in C#, in which I show chosen passages from a book.
Now I am not sure how do I store these passages in database? Currently I am considering these two options
1 - Storing passages as pictures in folders BUT their paths in the database. My application will get these paths and load the content in the window.
2 - Storing the actual text content in the database and load it in the window.

Can you please help me decide which way is better? Or is there any better way to do it? Thanks

This world is going to explode due to international politics, SOON.

Re: Best Approach For Designing Database

jschell27-Nov-12 13:13

jschell

27-Nov-12 13:13

Probably 2 but it could be 1.

Some examples for 1.
- text from illuminated book
- ancient text whose translation is in doubt
- legal documents where originals matter

Re: Best Approach For Designing Database

Mycroft Holmes27-Nov-12 13:14

Mycroft Holmes

27-Nov-12 13:14

Storing an image will give you the least amount of useful data, storing the text allows it to be searchable.

Never underestimate the power of human stupidity
RAH

Re: Best Approach For Designing Database

Eddy Vluggen27-Nov-12 13:23

Eddy Vluggen

27-Nov-12 13:23

AmbiguousName wrote:
Can you please help me decide which way is better?

There is no "better" way; each has it's own advantages and disadvantages, and you'd need to check your requirements which suits better.

On a folder;

You can have clashes in naming. Filenames must be unique.
You have the advantage of the virusscanner picking things up. Most scanners don't do databases.
You track an external asset

In a database;

Easy with backups/restores; are you gonna backup that folder?
Centralized; no need to document outside-repositories
As easy to (hyper)link to as a document on a webserver. However, not being a file, the server cannot response to a header-request on it's freshness.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]
They hate us for our freedom![^]

Re: Best Approach For Designing Database

Krishna Kumar Soni3-Dec-12 1:04

Krishna Kumar Soni

3-Dec-12 1:04

Plz try this...

http://stackoverflow.com/questions/5095533/storing-a-book-using-sql-server

EXEC sp_helptext

jojoba201126-Nov-12 22:43

jojoba2011

26-Nov-12 22:43

my stored procedure is :

SQL

USE [CRM-CMSAlmas]
GO
/****** Object:  StoredProcedure [dbo].[tblVacationDaily_GetAllDetailsSenderReciverbyRecordIdANDJobIdAndStepId]    Script Date: 11/27/2012 13:07:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[tblVacationDaily_GetAllDetailsSenderReciverbyRecordIdANDJobIdAndStepId] 
(@RecordId INT,@JobId INT,@StepId int)
AS
BEGIN

SELECT     WF.Cartable.JobId, Forms.tblVacationDaily.Id,  Forms.tblVacationDaily.IsWorkFlow, Forms.tblVacationDaily.CreateUserID, 
                      Forms.tblVacationDaily.CreateDate, Forms.tblVacationDaily.StepId, Forms.tblVacationDaily.RecordId, Forms.tblVacationDaily.OrderId, Forms.tblVacationDaily.PersonId, 
                      WF.Cartable.ForwardedUserId, WF.Cartable.RecieveUserId, WF.Cartable.IsDeleted, p1.FullName AS RecieveUserName, p2.FullName AS ForwardedUserName, 
                      WF.Cartable.IsBack
FROM         WF.Cartable INNER JOIN
                      WF.JobFormConjuction ON WF.JobFormConjuction.JobId = WF.Cartable.JobId INNER JOIN
                      Forms.tblVacationDaily ON Forms.tblVacationDaily.RecordId = WF.JobFormConjuction.RecordId AND Forms.tblVacationDaily.StepId = WF.Cartable.StepID - 1 INNER JOIN
                      Persons.People AS p1 ON p1.Id = WF.Cartable.RecieveUserId INNER JOIN
                      Persons.People AS p2 ON p2.Id = WF.Cartable.ForwardedUserId
WHERE     (Forms.tblVacationDaily.RecordId = @RecordId) AND (WF.Cartable.JobId = @JobId) AND (Forms.tblVacationDaily.StepId<=@StepId)
END

but when i use <pre lang="SQL">EXEC sp_helptext [dbo.tblVacationDaily_GetAllDetailsSenderReciverbyRecordIdANDJobIdAndStepId]</pre><br />
<br />
the result is :[Note: I have change Create ----> Alert

SQL

Alter PROCEDURE [dbo].[tblVacationDaily_GetAllDetailsforSenderAndReciversbyRecordIdANDJobId] 
(@RecordId INT,@JobId INT,@StepId int)
AS
BEGIN

SELECT     WF.Cartable.JobId, Forms.tblVacationDaily.Id,  Forms.tblVacationDaily.IsWorkFlow, Forms.tblVacationDaily.CreateUserID, 
                      Forms.tblVacationDaily.CreateDate, Forms.tblVacationDaily.StepId, Forms.tblVacationDaily.RecordId, Forms.tblVacationDaily.OrderId, Forms.tblVacationDaily.PersonId, 
                      WF.Cartable.ForwardedUserId, WF.Cartable.RecieveUserId, WF.Cartable.IsDeleted, p1.FullName AS RecieveUserName, p2.FullName AS ForwardedUserName, 
                      WF.Cartable.IsBack
FROM         WF.Cartable INNER JOIN
                      WF.JobFormConjuction ON WF.JobFormConjuction.JobId = WF.Cartable.JobId INNER JOIN
                      Forms.tblVacationDaily ON Forms.tblVacationDaily.RecordId = WF.JobFormConjuction.RecordId AND Forms.tblVacationDaily.StepId = WF.Cartable.StepID - 1 INNER JOIN
                      Persons.People AS p1 ON p1.Id = WF.Cartable.RecieveUserId INNER JOIN
                      Persons.People AS p2 ON p2.Id = WF.Cartable.ForwardedUserId
WHERE     (Forms.tblVacationDaily.RecordId = @RecordId) AND (WF.Cartable.JobId = @JobId) AND (Forms.tblVacationDaily.StepId<=@StepId)
END
 Go

Now the BUG/ERROR :
The content is same but the names are diffrent :
[dbo].[tblVacationDaily_GetAllDetailsforSenderAndReciversbyRecordIdANDJobId] 

but the sp is :

[dbo].[tblVacationDaily_GetAllDetailsSenderReciverbyRecordIdANDJobIdAndStepId]

why the names are different ?

Re: EXEC sp_helptext

Mycroft Holmes26-Nov-12 23:30

Mycroft Holmes

26-Nov-12 23:30

You probably renamed the proc and the sp_help has not refreshed the name.

If one of my devs named a proc like that he would get a first warning, there would be no second.

Never underestimate the power of human stupidity
RAH

Re: EXEC sp_helptext

jojoba201126-Nov-12 23:38

jojoba2011

26-Nov-12 23:38

Thanks in advanced but the sp was renamed before and now its working properly!
but when i right click the sp and click on modify is diffrent from sp_helptext. and event the name of it in sp list is also different from sp_helptext !

But i dont get why this is happing@

Re: EXEC sp_helptext

Corporal Agarn27-Nov-12 0:59

Corporal Agarn

27-Nov-12 0:59

Try updating your statistics, "UPDATE STATISTICS..." This may not do anything but it might help

Re: EXEC sp_helptext

jojoba201127-Nov-12 3:09

jojoba2011

27-Nov-12 3:09

thanks but no use ...
on google :
should update :sys.object/sys.storedprocedures/sys.sql_modules

https://www.google.com/search?hl=en&tbo=d&q=sp_helptext+renamed+stored+procedures++%2B+sql&oq=sp_helptext+renamed+stored+procedures++%2B+sql&gs_l=serp.3...8091.13502.0.13693.20.20.0.0.0.0.488.3055.7j7j2j2j1.19.0.les%3B..0.0...1c.1.MmW-E40j8mE[^]

I dont know how

modified 27-Nov-12 9:20am.

Re: EXEC sp_helptext

Eddy Vluggen27-Nov-12 13:33

Eddy Vluggen

27-Nov-12 13:33

Try logic; you renamed it?

Drop it, and create it anew?

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]
They hate us for our freedom![^]

RECORD VALIDATION IN ACCESS

teto26-Nov-12 5:19

teto

26-Nov-12 5:19

HOW TO MAKE ACCESS CHECK THE WHOLE RECORD FORM THE OTHER TABLES
I WANT TO CHECK THREE FIELDS IN TABLE FROM ANOTHER TABLE DURING DATA ENTRY

Re: RECORD VALIDATION IN ACCESS

André Kraak26-Nov-12 5:56

André Kraak

26-Nov-12 5:56

DO NOT SHOUT when asking a question. When you use all capital letters it is seen as shouting on the Internet and considered rude. Next time when posting a question please use proper capitalization.

0100000101101110011001000111001011101001

Re: RECORD VALIDATION IN ACCESS

Corporal Agarn26-Nov-12 6:05

Corporal Agarn

26-Nov-12 6:05

What have you tried?

how to compare an operation in where expression In sql

GREG_DORIANcod26-Nov-12 4:29

GREG_DORIANcod

26-Nov-12 4:29

hi everybody I need to do something like that:

SQL

select x.ProductName, sum(x.field1+y.field2) as Mysum, y.field2 from tablexxx as x inner join tableyyy as y on x.id =y.id where as Mysum - y.field2 > 0

obviously it doesnt work!, but I'm looking for suggestions

modified 26-Nov-12 10:42am.

Re: how to compare an operation in where expression In sql

s_magus26-Nov-12 5:20

s_magus

26-Nov-12 5:20

Something like this should do the trick.

SQL

Select ProductName, MySum, field2
From   (Select x.ProductName, SUM(x.field1 + y.field2) as MySum, y.field2
        From tablexxx as x inner join
             tableyyy as y on x.id = y.id) as q
Where MySum - field2 > 0

Re: how to compare an operation in where expression In sql

Mycroft Holmes26-Nov-12 11:56

Mycroft Holmes

26-Nov-12 11:56

Add Group By ProductName, Field2
after the where clause

Never underestimate the power of human stupidity
RAH

grant permission on table in another database

Danzy8326-Nov-12 3:55

Danzy83

26-Nov-12 3:55

Hi everyone! I need to grant permission on a table in a database to a database role in another database.

I have client database containing information about clients. In another database, I have roles for different departments. Now I need to grant permission on specific tables in the client database to roles or departments which are located in another database.

I have been struggling for a solution but have not succeeded. Is there any way to accomplish this task? Please help.

Re: grant permission on table in another database

Chris Meech26-Nov-12 8:25

Chris Meech

26-Nov-12 8:25

Dan_K wrote:
Now I need to grant permission

Is some kind of permission problem ocurring now that wasn't occuring before? Maybe I'm missing the obvious. Smile | :)

Chris Meech
I am Canadian. [heard in a local bar]

In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]

posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]

How to mearge local database with server database

Jitendra Parida - Jeetu23-Nov-12 22:00

Jitendra Parida - Jeetu

23-Nov-12 22:00

I use two database one is local server because there internet is not available. another is on global server my problem is that how to update local database on global database.

Re: How to mearge local database with server database

Mycroft Holmes23-Nov-12 22:17

Mycroft Holmes

23-Nov-12 22:17

Do some research into "replication" this may meet your needs or you can script the data transfer. There is no simple way to "merge" 2 databases. You may also look into Data Compare from Red Gate but I don't think that is going to be the tool.

Never underestimate the power of human stupidity
RAH

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