Login Fail for user 'XYZ' - Database Discussion Boards

First Prev Next

Re: Jan-December chart with specific information

riced25-Jun-10 1:21

riced

25-Jun-10 1:21

I think you probably want to use a parametrized query with species as input.
I'm slightly confused about the stage - should the chart only show the selected stage or all stages?
If you go this route then the query should group by month and stage. That should easily be done in designer.
An alternative might be to use a PivotTable as the source for the chart.

My Access is pretty rusty but I think this is fairly straightforward. I'm not too good on charts - never really used them in Access.

Would it be better to give the user a list of species and allow them to select from the list. Saves all sorts of bother if the user gets the spelling wrong.

Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis
The only valid measurement of code quality: WTFs/minute.

Re: Jan-December chart with specific information

Dave McCool25-Jun-10 1:39

Dave McCool

25-Jun-10 1:39

Hi Dave,

First, for stage, prefer a drop down list to choose the stage and the results change accordingly, just show one type of stage, say a table showing the Pieris Napi at adult stage only.

What you suggested on having a drop down list to chose the scientific name would be far better, how would I do this so you can choose what you want proir to creating the table, would an input form work best for this, choose the scientific name and stage on a small form then press a button to take you to a chart on that species. I know how to create the form, but not how to get the information onto a chart

Havn't used much with charts or inputting data except on tables latley, so I am a bit rusty.

In the end we're all just the same

sql function to calculate work hours of an employee

Thanusree Duth24-Jun-10 21:03

Thanusree Duth

24-Jun-10 21:03

Hii..
I m in a great confusion. Confused | :confused:

Can u help me?I want to calculate total work hours of a day using sql function.I m having three tables.
SG_Emp_Master,SG_Daily_Register,SG_Emp_Department.Employee ID,From date,ToDate are the parameters passed..Here is my code..But i could'nt get correct output.pls help me.

CREATE FUNCTION[dbo].[FN_TIME_ATTNDNC_REPORTS] ( @EMPID varchar(50)
,@FROMDATE datetime ,@TODATE datetime )RETURNS TABLE
AS RETURN(
SELECT DR_EmployeeID as EMPID,EM_FirstName+EM_MiddleName+EM_LastName as EMPNAME,ED_Department as DEPARTMENT,
SUBSTRING(CONVERT(VARCHAR,ES_TimeIn,9),14,15)+'-'+SUBSTRING(CONVERT(VARCHAR,ES_TimeOut,9),14,15)as SHIFTTIME,
CASE WHEN ((CONVERT(VARCHAR,DR_TimeIn,101)=CONVERT(VARCHAR,DR_TimeOut,101))AND(SUBSTRING(CONVERT(VARCHAR,DR_TimeIn,9),14,15)>'11:59:59:000PM'))
THEN
ISNULL(CONVERT(VARCHAR,DR_TimeIn,107)+' 12:01:00:000AM','Not checked')
ELSE
ISNULL(CONVERT(VARCHAR,DR_TimeIn,109),'Not checked')
END
AS TIMEIN,
CASE WHEN(CONVERT(VARCHAR,DR_TimeIn,101)!=CONVERT(VARCHAR, DR_TimeOut,101))
THEN
ISNULL(CONVERT(VARCHAR,DR_TimeIn,107)+' 11:59:59:000PM','Not checked')
WHEN (CONVERT(VARCHAR,DR_TimeIn,101)=CONVERT(VARCHAR,DR_TimeOut,101))
THEN
ISNULL(CONVERT(VARCHAR,DR_TimeOut,109),'Not checked')
END
AS TIMEOUT,
CASE WHEN(CONVERT(VARCHAR, DR_TimeIn,101)=CONVERT(VARCHAR, DR_TimeOut,101))
THEN
ISNULL(CONVERT(VARCHAR(5),DATEDIFF(SS,DR_TimeIn,DR_TimeOut)/3600)
+':'+
CONVERT(VARCHAR(5),DATEDIFF(SS,DR_TimeIn,DR_TimeOut )%3600/60)
+':'+
CONVERT(VARCHAR(5),(DATEDIFF(SS,DR_TimeIn,DR_TimeOut)%60)),'0 Blush | :O

')

WHEN(CONVERT(VARCHAR, DR_TimeIn,101)!=CONVERT(VARCHAR, DR_TimeOut,101))
THEN

ISNULL(CONVERT(VARCHAR(5),DATEDIFF(SS,DR_TimeIn,CAST(CONVERT(VARCHAR,DR_TimeIn,101)+' 11:59:59:000PM'AS DATETIME))/3600)
+':'+
CONVERT(VARCHAR(5),DATEDIFF(SS,DR_TimeIn,CAST(CONVERT(VARCHAR, DR_TimeIn,101)+' 11:59:59:000PM'AS DATETIME))%3600/60)
+':'+
CONVERT(VARCHAR(5),(DATEDIFF(SS,DR_TimeIn,CAST(CONVERT(VARCHAR, DR_TimeIn,101)+' 11:59:59:000PM'AS DATETIME))%60)),'0 Blush | :O

')
ELSE 'NOT CHECKED'
END
AS HOURS
FROM SG_Daily_Register
INNER JOIN
SG_Emp_Shift on SG_Daily_Register.DR_ShiftID=SG_Emp_Shift.ES_ShiftID
INNER JOIN SG_Emp_Master on SG_Daily_Register.DR_EmployeeID=SG_Emp_Master.EM_EmployeeID
INNER JOIN SG_Emp_Department on SG_Emp_Master.EM_DeptID=SG_Emp_Department.ED_DeptID
WHERE DR_EmployeeID=@EMPID AND CONVERT(VARCHAR,DR_TimeIn,101)BETWEEN @FROMDATE AND @TODATE

UNION

SELECT DR_EmployeeID as EMPID,EM_FirstName+EM_MiddleName+EM_LastName as EMPNAME,ED_Department as DEPARTMENT,
SUBSTRING(CONVERT(VARCHAR,ES_TimeIn,9),14,15)+'-'+SUBSTRING(CONVERT(VARCHAR,ES_TimeOut,9),14,15)as SHIFTTIME,

CASE WHEN ((CONVERT(VARCHAR,DR_TimeIn,101)=CONVERT(VARCHAR,DR_TimeOut,101))AND(SUBSTRING(CONVERT(VARCHAR,DR_TimeIn,9),14,15)>'11:59:59:000PM'))
THEN
ISNULL(CONVERT(VARCHAR,DR_TimeIn,107)+' 12:01:00:000AM','Not checked')
ELSE
ISNULL(CONVERT(VARCHAR,DR_TimeIn,109),'Not checked')
END

AS TIMEIN,

CASE WHEN(CONVERT(VARCHAR,DR_TimeIn,101)!=CONVERT(VARCHAR, DR_TimeOut,101))
THEN
ISNULL(CONVERT(VARCHAR,DR_TimeIn,107)+' 11:59:59:000PM','Not checked')
WHEN (CONVERT(VARCHAR,DR_TimeIn,101)=CONVERT(VARCHAR,DR_TimeOut,101))
THEN
ISNULL(CONVERT(VARCHAR,DR_TimeOut,109),'Not checked')
END
AS TIMEOUT,
CASE WHEN(CONVERT(VARCHAR, DR_TimeIn,101)=CONVERT(VARCHAR, DR_TimeOut,101))
THEN
ISNULL(CONVERT(VARCHAR(5),DATEDIFF(SS,DR_TimeIn,DR_TimeOut)/3600)
+':'+
CONVERT(VARCHAR(5),DATEDIFF(SS,DR_TimeIn,DR_TimeOut )%3600/60)
+':'+
CONVERT(VARCHAR(5),(DATEDIFF(SS,DR_TimeIn,DR_TimeOut)%60)),'0 Blush | :O

')

WHEN(CONVERT(VARCHAR, DR_TimeIn,101)!=CONVERT(VARCHAR, DR_TimeOut,101))
THEN
ISNULL(CONVERT(VARCHAR(5),DATEDIFF(SS,DR_TimeIn,CAST(CONVERT(VARCHAR,DR_TimeIn,101)+' 11:59:59:000PM'AS DATETIME))/3600)
+':'+
CONVERT(VARCHAR(5),DATEDIFF(SS,DR_TimeIn,CAST(CONVERT(VARCHAR, DR_TimeIn,101)+' 11:59:59:000PM'AS DATETIME))%3600/60)
+':'+
CONVERT(VARCHAR(5),(DATEDIFF(SS,DR_TimeIn,CAST(CONVERT(VARCHAR, DR_TimeIn,101)+' 11:59:59:000PM'AS DATETIME))%60)),'0 Blush | :O

Re: sql function to calculate work hours of an employee

thatraja24-Jun-10 23:56

thatraja

24-Jun-10 23:56

Mate, please format the code because there are many smileys in your sql query.

thatraja

Tips/Tricks|Brainbench certifications|Facebook|Twitter

Query to get all row values as comma separated

indian14324-Jun-10 9:14

indian143

24-Jun-10 9:14

Hi All,

I want to get all the values of a column as comma separated values in SQL Server. Can anybody help me writing the query for that.

Thanks in advance.

Thanks & Regards,

Md. Abdul Aleem
NIIT technologies

Re: Query to get all row values as comma separated

Mycroft Holmes24-Jun-10 11:55

Mycroft Holmes

24-Jun-10 11:55

Here is an example

DECLARE @String VARCHAR(1000)

SET @String = ''

SELECT @String = @String + ',' + Column1
FROM Table1

SELECT @String

Never underestimate the power of human stupidity
RAH

How to avoid specification of return parameters in an inline table function

jophinmichael24-Jun-10 3:05

jophinmichael

24-Jun-10 3:05

Hi guys,
I am having an inlined table valued function to search the users in my site.
The search is optional ie. sometimes user will search with first name, sometimes he searches with last name, and sometimes both.
As of now I am using if-else statements to check whether first name is ''(empty),or last name is ''(empty) or both are ''(empty).

I have two questions?

1. Is there any other way, so that I can avoid these if-else statements and make it a single query?
2. If I am using a function as shown below, I have to specify all the return parameters. Is there any way I can avoid this and return the complete table(I do not want to use stored procedures)?

I'm using a freetext search,because I want to get the search results according to relevence(ranked results).

ALTER FUNCTION [dbo].[Search]
(

@FirstName Varchar(400),@LastName Varchar(400)
)
RETURNS
@SearchResults TABLE
(
-- Add the column definitions for the TABLE variable here
UserName Varchar(400),
EmailAddress Varchar(400)
)
AS
BEGIN

IF (@FirstName<>'' AND @LastName<>'')
begin
insert into @SearchResults
Select Top 100 Percent UserName,EmailAddress
From UsersFirst t
JOIN FreetextTable(UsersFirst, FirstName, @FirstName) K On t.Id = K.[Key]
JOIN UsersSecond U on t.UserId=U.Id
JOIN FreetextTable(UsersSecond, LastName, @LastName) As L On U.Id = L.[Key]
ORDER BY K.[Rank] DESC;
end
ELSE IF (@FirstName<>'')
begin
insert into @SearchResults
Select Top 100 Percent UserName,EmailAddress
From UsersFirst t
JOIN FreetextTable(UsersFirst, FirstName, @FirstName) K On t.Id = K.[Key]
JOIN UsersSecond U on t.UserId=U.Id
ORDER BY K.[Rank] DESC;
end

return
END

Thanks In Advance

jophin

Sasmi_Office24-Jun-10 2:19

Sasmi_Office

24-Jun-10 2:19

Dear Friend's,

My Issue scenario is as follow's

i have two servers Name
"Server_A"
"Server_B"

In "Server_A" i have one Database "DB1"
In "Server_B" i have one Database "DB2"

in Database "DB1" of Server "Server_A" i have one table "tbl_ListOfCountry"

now as per my business logic that table must be centralize where my client will update information and i need to use this table data in "Server_B".

For that i have create one procedure which pull all the data of table "tbl_ListOfCountry" from "Server_A".

for that i have created Synonym "tbl_ListOfCountry"
using following syntex

CREATE SYNONYM [dbo].[tbl_ListOfCountry] FOR [Server_A].[DB1].[dbo].[tbl_ListOfCountry]

now for security purpose i have created one user "user1" with "Server Role" as "Public" and "User Mapping" as "db_datareader", "db_datawriter" and "public" in "Server_B"

and Grant As Execute Permission of this user to my procedure "Proc1" which use to pull the record.

now the main issue is started

whenever i execute that procedure "Proc1" using "user1" login instead of "sa" it give me error

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'user1'.

Please, help me to overcome this issue...

Thanks in advanced..

Sasmi

Re: Login Fail for user 'XYZ'

Chris Meech24-Jun-10 2:53

Chris Meech

24-Jun-10 2:53

Let me guess. The password for the 'sa' account on DB1(ServerA) is the same as on DB2(ServerB). 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]

Re: Login Fail for user 'XYZ'

Sasmi_Office24-Jun-10 4:38

Sasmi_Office

24-Jun-10 4:38

Thanks for the quick reply...

I am also think same but when i am using this Procedure in my application it is giving me same error can you explain me why this happening....

Re: Login Fail for user 'XYZ'

Chris Meech24-Jun-10 4:46

Chris Meech

24-Jun-10 4:46

I suspect that it works when you are logged in to DB2 as 'sa', because it attempts to use those same credentials on DB1, it will work since 'sa' also exists in DB1 with the same password. When you login to DB2 with 'XYZ', the same login will be attempted on DB1 and since 'XYZ' doesn't exist on DB1, the login fails. The quick and dirty solution is to replicate the userid on DB1 and I suspect that will allow the link to work. 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]

sql function to calculate work hours

Athira.G.Krishnan24-Jun-10 2:11

Athira.G.Krishnan

24-Jun-10 2:11

if the shift time is 7pm to 3am..after 12am,it will be considered as next day..and the time after 12am will be calculated as work hour of next day..please help me to write a sql function for this..pls...

Re: sql function to calculate work hours

Scubapro24-Jun-10 4:18

Scubapro

24-Jun-10 4:18

Check this out:

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

Re: sql function to calculate work hours

David Mujica24-Jun-10 6:16

David Mujica

24-Jun-10 6:16

Things you may want to consider when designing a system which includes a start time and end time:

1) Store both the date and time. This will make it much easier to determine if a shift crosses a midnight boundary.

2) Use the concept of a "End of Day" time. For example, lots of manufacturing sites use 11pm (23:00) because they consider anyone working after 11pm to be working the next day.

3) Also take into consideration a "Pay Date", typically this is what is considered the day the person works (and gets paid for). For example if your worker starts his shift on Friday at 7pm and works into Saturday morning, he would be paid for working Friday. Not 2 different pay days. (Fri & Sat)

Best of luck.

Re: sql function to calculate work hours

CitrusTech24-Jun-10 21:01

CitrusTech

24-Jun-10 21:01

Time can be a real nightmare to handle. Other issues which you might want to think about include:

Day-light saving time (esp. around midnight boundaries).
Time zones -- don't look applicable in your case, but I worked on a car hire problem which supported pickup/drop off across time zones.

Of course, I've not actually answered the original question. Some idea of the data structure/data available might be useful.

Group By 30 Minutes

It_tech23-Jun-10 23:42

It_tech

23-Jun-10 23:42

Hi All,

I have used (datepart(hour,CR_callstart)) As Hours to group Data by Hours.

What i would like is to group them by Every 30 Minutes. Is there any thing i can use so that
I can get data grouped every hlaf an hour.

At the moment i can get :

Time Revenue
10:00 $100
11:00 $200

With :

Select (datepart(hour,CR_callstart)) As Hours,sum(cost)as Revenue from dbo.Tablename
Group by Hours

But I want to get

Time Revenue
10:00 $75
10:30 $25
11:00 $100
11:30 $100

Please advice.

Thanks

Re: Group By 30 Minutes

Mycroft Holmes24-Jun-10 1:04

Mycroft Holmes

24-Jun-10 1:04

Test the time component (using datepart) for the number of minutes, set a value 0 or 30 and then group by the value. This probably can all me done in 1 select statement.

Never underestimate the power of human stupidity
RAH

Re: Group By 30 Minutes

It_tech24-Jun-10 2:59

It_tech

24-Jun-10 2:59

Thanks so Much for your reply.Its much appreciated.Sorry i am Having trouble to understand it.How do i modify :
(datepart(hour,CR_callstart)) As Hours

Many thanks.

Re: Group By 30 Minutes

J4amieC24-Jun-10 3:31

J4amieC

24-Jun-10 3:31

This should do it.

SELECT 
DATEPART(hour,CR_callstart) as hour,
CASE 
	WHEN DATEPART(minute,CR_callstart) between 0 AND 29 THEN 0
	ELSE 30
END as minute,
sum(cost) as revenue
from tablename
group by DATEPART(hour,CR_callstart),
CASE 
	WHEN DATEPART(minute,CR_callstart) between 0 AND 29 THEN 0
	ELSE 30
END

Re: Group By 30 Minutes

It_tech24-Jun-10 3:56

It_tech

24-Jun-10 3:56

That is Fantastic.Is there any way I can put Hour and Minute in One Column.

Like :

Time
10:00
10:30
11:00
11:30
etcc..

Thanks so much for your time.

Re: Group By 30 Minutes

J4amieC24-Jun-10 6:15

J4amieC

24-Jun-10 6:15

It_tech wrote:
Is there any way I can put Hour and Minute in One Column.

Sure, but thats presentation logic and should be handled appropriately.

Re: Group By 30 Minutes

It_tech24-Jun-10 6:20

It_tech

24-Jun-10 6:20

Thanks For your reply and sorry to bother you.Can i do that from a Sql level?

It would be great if you can give me an example.

Many thanks.

Re: Group By 30 Minutes

Mycroft Holmes24-Jun-10 11:49

Mycroft Holmes

24-Jun-10 11:49

He wrote the code for you - what do you want, that he should go out and test your app as well.

Jamie stated that the requirement is PRESENTATION logic therefore it does not belong in the database and you then ask him if it can be done in the database.

Do your own work and use your brain instead of relying on the forum for ideas.

Never underestimate the power of human stupidity
RAH

Re: Group By 30 Minutes

It_tech25-Jun-10 0:50

It_tech

25-Jun-10 0:50

Thanks.I have already sorted it out.
Cheers

Trigger Issue

Elizma23-Jun-10 23:08

Elizma

23-Jun-10 23:08

Hi Guru's

I have a Linked Server in SQL 2005 to an Access 2003 database.

I have written a stored procedure to insert a record into the Access DB. When I run the Proc on it's own, it perfoms the task, but the moment I call it from a Insert Trigger, do I get an Error Message Saying that the Linked Server does not support the required interface.

Could someone please help me with a solution as to how I am going to insert / update the table in Access from SQL?

Your help will be greatly appreciated.

Kind Regards,

Elizma

Last Visit: 31-Dec-99 18:00 Last Update: 27-Apr-24 8:35

Refresh

ᐊ Prev 1...601 602 603 604605606 607 608 609 610 Next ᐅ

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