|
Now this is something I don't see very often these days. ![Beer | [beer]](https://www.codeproject.com/script/Forums/Images/beer.gif)
|
|
|
|
|
mistakes? by me? never!  hahaha
Sincerely Yours,
Brian Hart
|
|
|
|
|
hi
I have scheduled a job on backup cluster to take backup from production server. To access my production server i have a link server. It was worked fine untill last week. Now it has failed due to 'login failed for 'NT AUTHORITY/ANONYMOUS'. Plz any one let me know the solution.
D.Willington
|
|
|
|
|
When you configured the linked server, did you setup security to use that login('NT AUTHORITY/ANONYMOUS') as a security context? If yes make sure that the password is correct and that this user has permission in the production server you are trying to access.
|
|
|
|
|
I am creating a project in .net and i have to retrieve some data from tables on the conditions:
1) A user can create multiple profiles,upto 20 in 20 different categories (The data is stored in "profiles" table)
2) There is a "project" table in which projects are stored in 20 different categories
3) project categories are same as profile categories
The problem is i want to retrieve all the projects where project categories are same as a users categories
For example:
if user A has profiles in categories x,y, and z I want to retrieve the details of projects in categories x,y, and z.
number of profiles for different users are differen someone has 2 profiles while other one has 5 profiles it can be varied
If anyone can help me in SQL Query I will be highly thankfull
|
|
|
|
|
not getting wht u exactly wants ?
sql statement for dis tables ? coz its very simple you hav to use inner joins dats it
Reasons are not Important but Results are Important
|
|
|
|
|
Let me first explain you the requirement
Table Profiles:
employeeid ------ category
-----------------------------------
a ----------- Finance
b ---------- HRD
c ---------- Sales
a ------------ HRD
a ------------ Sales
b ------------ Finance
Table Work
category --------- Title
------------------------------------------------
Finance ---------- maintaining annual accounts
HRD ------------ Recruitement of skilled employees
Sales ------------ Sales of newly launched product
Finance----------- Creating Report (Quarterly)
HRD--------------- Managing Employee's Feedback
Now if I want to find work available the result should be like:
for employee a-
Category --------- Title
---------------------------------------------
Finance ---------- maintaining annual accounts
Finance----------- Creating Report (Quarterly)
HRD--------------- Managing Employee's Feedback
HRD ------------ Recruitement of skilled employees
Sales ----------- Sales of newly launched product
for employee b-
Category -------- Title
--------------------------------------------------------
Finance----------- Creating Report (Quarterly)
Finance --------- maintaining annual accounts
HRD ------------ Recruitement of skilled employees
HRD--------------- Managing Employee's Feedback
The problem is that I don't know the exact appearance of an employee in the profile table, and thats why I think joins are out of scope(I am not an expert in SQL)
Hope this may clear some fog.Thanks.
modified on Tuesday, August 19, 2008 8:33 AM
|
|
|
|
|
When emplpyeeid is a then use query down
<br />
SELECT profiles.Employeeid,profiles.Category,[work].Title<br />
FROM profiles,[work]<br />
WHERE profiles.category = [work].category<br />
and profiles.employeeid='a'<br />
ORDER BY [work].category ASC
If you have to show all employeeid's then use this another query
<br />
SELECT profiles.Employeeid,profiles.Category,[work].Title<br />
FROM profiles,[work]<br />
WHERE profiles.category = [work].category<br />
and profiles.employeeid in(SELECT DISTINCT t1.Employeeid FROM profiles AS t1)<br />
ORDER BY profiles.Employeeid,[work].category ASC
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
what i got is there is 3 tables
1> category - Master table
2> profile - here employee with category(Transaction table)
3> project - here project in different category(Transaction table)
where category is common in profile and project.
select distinct profile.employeeid,project.projectname,project.categoryname
from project
inner join category on
category.catid = project.project_catid
inner join profile on
profile.prof_catid = project.catid
where profile.employeeid='a'
if above is correct Now tell me where r u not getting?
or May be i am not getting you
Reasons are not Important but Results are Important.
Swati
|
|
|
|
|
Both of you thanks for suggestions but its not working.
Let me make a final attempt to clear my issue
if I create a stored procedure as:
create procedure selectworks
(
@maincategory nvarchar(50)
)
as
select profile.category,
project.title,
project.subcategory,
project.expirydate from profile,project
where profile.category=@maincategory
and project.category=@maincategory
this is going to give me result for one category which i am going to provide( @maincategory)
but the issue is that the user has profiles in more than one category
if he has profiles in 3 categories this procedure should run for 3 times for 3 different categories but the result should come combined similarly if a user has profiles in 5 categories this procedure should run for 5 times for 5 different categories but should give combined result.
Thanks in advance..
|
|
|
|
|
1>First of all why you wants to pass category rather then employee id ???
2>if there is any necessity of passing category then you can use of
char index Or IN where you have to pass all the category separated by comma.
ex:- @maincategory='1,2,3'
1> CHARINDEX(',',@maincategory ) > 0
2> project.category IN (@maincategory)
Reasons are not Important but Results are Important.
Swati
|
|
|
|
|
Thanks a lot Swati I got the solution.Thanks...
|
|
|
|
|
hi all
can anyone tell me how to (query to)find the first three highest salaries from a table.
Example
input
Name Salary
a 1000
b 5000
c 8000
d 11000
e 15000
f 2000
output:
salary
15000
11000
8000
thanks in advance...
pintoo
|
|
|
|
|
select top 3 salary from
tablename
order by salary desc
Reasons are not Important but Results are Important
|
|
|
|
|
hi
thanks for answering..can u tell me how to write(modify) this query without using TOP keyword.
pintoo
|
|
|
|
|
I wonder why you don't want to use TOP, but here's another version:
SELECT Name, Salary
FROM TableName alias1,
WHERE 4 < (SELECT COUNT(*)
FROM TableName alias2
WHERE alias2.Salary > alias1.Salary)
Mika
|
|
|
|
|
I think that the Top will be the best choice, unless your using something other than SQL Server.
If your using MySQl, use Limit 3 at the end of the statement.
Select Salary
From tablename
Order By Salary Desc
Limit 3
I think any other method will cause overhang.
|
|
|
|
|
A view is a virtual table that consists of columns from one or more tables. Though it is similar to a table, it is stored in the database. It is a query stored as an object.
So what is the use of Views because i think one can do anything and everything with the tables without having Views...........
Plz explain ?????????
|
|
|
|
|
|
Views are faster and they hide the query complicity.
|
|
|
|
|
Hi,
Please go though the following articles for better explaination:
Article1[^]
Article2[^]
Hope this helps  .
Regards,
John Adams
ComponentOne LLC
|
|
|
|
|
Putting it simply, a View is a boon to security and simplifies your access to information. It's quicker in many respects than a full blooded table scan.
With a View we can grant access to the View without granting access to the underlying table thus limiting someone to only the data we wish them so access. Case in point, we could give a User access to a View from an employee table that omits SS# or salary information.
Does that answer your question?
“If we are all in agreement on the decision - then I propose we postpone further discussion of this matter until our next meeting to give ourselves time to develop disagreement and perhaps gain some understanding of what the decision is all about.”-Alfred P. Sloan
|
|
|
|
|
View was designed for two purposes, security and query simplification as many people has already pointed out. Articles provided clarify these aspects well. However, a common misbelief is that a view is faster than a query without a view. This is not always true. There are several situations where views are slower than plain select statements.
When a view is created, it's stored as an object which gives the optimizer a possibility to create a better plan for the query. When executing view, some (but not all) of the parsing and optimization can be skipped and this can result in (small) performance benefit.
However creating a view does not significantly modify execution plan. For example if SELECT * FROM Customers results in a full table scan, the situation isn't typically any different if the statement is wrapped inside a view. Execution of the view can be boosted in several ways but they always require structural changes in the database (materializing, different kinds of indexing, partitioning, federations etc) so they require administration.
Since optimizer must be very careful when calculating permutations that it doesn't risk correct results, many default SQL optimizations are easily skipped if a query is executed against a view. Typical example is so called predicate pushing when optimizer tries to apply a condition as soon as possible. However if a view is defined, predicate pushing may not be used since it would modify view results hence modifying the overall results. This is just one of the many examples when query against a view can be (significantly) slower that plain query.
Mika
|
|
|
|
|
Hi,
Can any body suggest me how to take backup file from 2005 to 2000 in sql server.
Ex: I have taken backup from Sql2005...
I want to restore in 2000.
Is it possible? If yes please suggest me.
other wise
is there any other way to do this task.
|
|
|
|
|
Ashish Kumar Vyas wrote: Can any body suggest me how to take backup file from 2005 to 2000 in sql server.
You cannot.
Ashish Kumar Vyas wrote: is there any other way to do this task.
You could look for a scripting tool. Modify the script to remove any SQL Server 2005 enhancements then run the script on SQL Server 2000 - It is still risky though.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
