Database Discussion Boards

Eddy Vluggen4-Dec-12 3:12

4-Dec-12 3:12

..and the table "timesheet" holds the day as a VARCHAR? That's not a bug, that's by design. Strings are sorted depending on the settings, should have been DATE type.

Bastard Programmer from Hell Suspicious | :suss:

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

Re: SQL query Date Sort Problem

AnirbanM 24-Dec-12 3:27

AnirbanM 2

4-Dec-12 3:27

No sir, the sorting is made on 'ToDate' column which is of Date type, not varchar type, as I already given my column types before.

Re: SQL query Date Sort Problem

Eddy Vluggen4-Dec-12 4:44

Eddy Vluggen

4-Dec-12 4:44

AnirbanM 2 wrote:
No sir, the sorting is made on 'ToDate' column which is of Date type, not varchar type, as I already given my column types before.

Still, it sorts like it would be. Can you create a TSQL-script that reproduces the problem? Seems I can't, my dates keep returning ordered in a way that could be expected;

SQL

DECLARE @TEST TABLE (SD DATE);
INSERT INTO @TEST (SD) VALUES ('20010101');
INSERT INTO @TEST (SD) VALUES ('20011010');
INSERT INTO @TEST (SD) VALUES ('20010310');
INSERT INTO @TEST (SD) VALUES ('20010210');
INSERT INTO @TEST (SD) VALUES ('20010301');
INSERT INTO @TEST (SD) VALUES ('20010201');
SELECT * FROM @TEST ORDER BY SD;

Results:
2001-01-01
2001-02-01
2001-02-10
2001-03-01
2001-03-10
2001-10-10

Bastard Programmer from Hell Suspicious | :suss:

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

Re: SQL query Date Sort Problem

Michael Potter4-Dec-12 8:11

Michael Potter

4-Dec-12 8:11

I would think the error is happening after the query is returned. Test the query in SQL alone and see if you get the error you describe.

Re: SQL query Date Sort Problem

jschell4-Dec-12 8:23

jschell

4-Dec-12 8:23

AnirbanM 2 wrote:
Please suggest where is the problem, thanks to all in advance.

Some posssible reasons.
- The problem originates from some other place in the code. So nothing to do with the code that you posted. And one very obvious possibility is that there is code somewhere that is sorting based on alphabetic ("10", "11" versus "1", "2) instead of using the sort that you gave.
- You are not looking at the actual data.
- You are not running the code that you think you are.

oltp vs olap

a.fatemeh2-Dec-12 10:51

a.fatemeh

2-Dec-12 10:51

hi every one

i'm not sure if it's true to brought my question here but I want to make clear how much Datawarehouse is useful for a company.what i want is a query example in sql which is long and hard for operational database and is a piece of cake for analytical database.
I've searched a lot but I can't fine one may be I searched wrongly but I would be so thanks full if any one could help me with this as soon as possible.

Re: oltp vs olap

Mycroft Holmes2-Dec-12 11:49

Mycroft Holmes

2-Dec-12 11:49

Instead of trying to find a "query" difference try researching the uses of the different types of database, a datawharehouse is a snapshot, usually delayed from real time, of the transaction (OLPT) database that is optimised for reporting (querying).

It is not an either/or question, once you reporting queries begin impacting on your transaction processing you need an OLAP solution as well as the OLTP.

This is a very simplistic view, there are entire libraries written on this subject!

Never underestimate the power of human stupidity
RAH

Re: oltp vs olap

a.fatemeh2-Dec-12 11:57

a.fatemeh

2-Dec-12 11:57

thanks so much,i almost know what you are talking about. but I really want a query .
this is a an extract from a book :"Although many decision-support queries can be written in SQL, others either
cannot be expressed in SQL or cannot be expressed easily in SQL."

I want an example of this kind of query which is written in both.

Re: oltp vs olap

Mycroft Holmes2-Dec-12 13:28

Mycroft Holmes

2-Dec-12 13:28

a.fatemeh wrote:
I want an example of this kind of query which is written in both.

Good luck with that, even if you could get the examples it would take a lot explaining to get the context of the requirement. You might want to look into the Microsoft example databases, they have various OLTP and OLAP dbs (AdventureWorks etc), there may be such comparative examples based on them but I doubt it.

Never underestimate the power of human stupidity
RAH

Re: oltp vs olap

a.fatemeh2-Dec-12 13:41

a.fatemeh

2-Dec-12 13:41

thanks by the way.
it seems I'd hardly find one.

SQL instance name when using network group

Aamir Saleem2-Dec-12 10:32

Aamir Saleem

2-Dec-12 10:32

i have made a network group and i have installed sql in server pc so i m having problem of instance name while logging in sql management stdio in other network pc's ... plx give any solution to this prblm

Re: SQL instance name when using network group

Mycroft Holmes2-Dec-12 11:43

Mycroft Holmes

2-Dec-12 11:43

As we are not telepathic we cannot help you without knowing what the error is. Try giving us some detail on the error message you are getting!

The instance name is the name you gave the server when installing OR then name/IP of the server you installed it on.

Never underestimate the power of human stupidity
RAH

ACL vs SQL for fuzzy matching

Ben Dessau29-Nov-12 4:15

Ben Dessau

29-Nov-12 4:15

We are discussing a new project that will do lots of word matching, including some fuzzy logic in the matching.

We are deciding between using SQL and ACL for this job, and I've been told that SQL has more fuzzy logic facilities than ACL.

Anyone know about this or what the additional fuzzy facilities are?

Thanks in advance - Ben

Re: ACL vs SQL for fuzzy matching

Pete O'Hanlon29-Nov-12 5:07

Pete O'Hanlon

29-Nov-12 5:07

That's a pretty broad assumption. What variant of SQL database are you talking about here, because different engines have different features?

*pre-emptive celebratory nipple tassle jiggle* - Sean Ewington

"Mind bleach! Send me mind bleach!" - Nagy Vilmos

CodeStash - Online Snippet Management | My blog | MoXAML PowerToys | Mole 2010 - debugging made easier

Re: ACL vs SQL for fuzzy matching

Ben Dessau29-Nov-12 6:43

Ben Dessau

29-Nov-12 6:43

Currently we're using Microsoft SQL Server 2008 but could upgrade to the latest for this project.

Re: ACL vs SQL for fuzzy matching

Pete O'Hanlon29-Nov-12 6:50

Pete O'Hanlon

29-Nov-12 6:50

At the most basic, you're talking about Soundex matching, which isn't great. You might want to consider Full Text Indexing, which will return misspelled items if they are close enough. Of course, you can always implement your own fuzzy logic inside SQL Server, using CLR functions.

*pre-emptive celebratory nipple tassle jiggle* - Sean Ewington

"Mind bleach! Send me mind bleach!" - Nagy Vilmos

CodeStash - Online Snippet Management | My blog | MoXAML PowerToys | Mole 2010 - debugging made easier

SQL Query

berba29-Nov-12 2:22

berba

29-Nov-12 2:22

I have the following query:

select CASE when EA.Address1 <> '' and EA.Address2 <> '' and EA.Address3 <> '' then Replace(EA.Address2, ',' , ' ') + '' + Replace(EA.Address3, ',' , ' ')

else EA.Address1 end as employeeaddress

from EmployeeAdress EA

However employeeaddress should hold a maximum of 35 characters irrespective the number of characters in columns Address1, Address2, Address3.

Any advice how to do this?

Thanks
Berba Smile | :)

Re: SQL Query

Richard Deeming29-Nov-12 2:50

Richard Deeming

29-Nov-12 2:50

Try:

SQL

SELECT LEFT(CASE ... END, 35) As EmployeeAddress ...

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: SQL Query

berba2-Dec-12 18:30

berba

2-Dec-12 18:30

Thanks. It works Smile | :)

SQL Server windows authentication

Zeyad Jalil28-Nov-12 23:49

Zeyad Jalil

28-Nov-12 23:49

Hi All,

We want to disable the windows authentication mode in the sql server, because we use the sql authentication and we don't want the user to login from the management studio using windows authentication.

so we need a sql script to disable the windows authentication from our windows application, what is the script please.

Thanks all

Re: SQL Server windows authentication

Garth J Lancaster28-Nov-12 23:59

Garth J Lancaster

28-Nov-12 23:59

not sure if it can be done with t-sql or such - but this may help http://sqlblog.com/blogs/allen_white/archive/2011/05/19/change-sql-servers-authentication-mode-with-powershell.aspx[^]

Re: SQL Server windows authentication

jschell29-Nov-12 8:09

jschell

29-Nov-12 8:09

Rather certain that isn't possible nor logically advisable.

SQL Server supports Windows authentication and as an option SQL identification. The later is an option the former isn't.

Additionally you seem to think that you can disallow access to SQL server completely. Which suggests that you think that there will never be problems that need investigation. Which is unlikely to be true.

If you don't trust your users then either address that at the management level or the contract level. And don't try to solve it with technology.

Re: SQL Server windows authentication

Chris Meech30-Nov-12 3:45

Chris Meech

30-Nov-12 3:45

jschell wrote:
If you don't trust your users then either address that at the management level or the contract level. And don't try to solve it with technology.

That's got my five.

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]

Need help with a tricky MS SQL Query

muya mwansa27-Nov-12 6:46

muya mwansa

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![^]

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