Login failed for give user id

I tried to create a mining model from a remote SQL 2000 database using local user id of remote server. Whenver I try to process the model I am getting login failed error. But the same model runs fine when I configure datasource using windows credentials.I found out the reason, when I set the impersonation mode to service account/degfault its working. But when I set to specific user name and password, its not working. Can somebody suggest me the possible cause.|||I found answer myself. I just created a local user and a role in analysis service database mapping to local user and granted him appropriate rights. Now I can process the data model under "impersonate particular user mode"

Logical read and DBCC dropcleanbuffers

Hi! I post a question last week but didn't get any responses, so trying it
again.
Basically, I am in a process of tuning a query and number of logical reads
gets change after I run dbcc dropcleanbuffers.
I understand that physical reads will be different after cache is flushed
out but why logical read get changed after flushing the cache but with exact
same execution plan.
Sql 200 sp4
Hi James
The Reads reported in Profiler also includes reads performed in the
procedure cache, which is variable depending on activity levels & dbcc
commands.
Profiler's reads data is useful for zero'ing on problems, but you should use
SET STATISTICS IO ON for more accurate report on the logical reads
performed by an individual query in the Query Analyser / Management Studio.
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"james" <kush@.brandes.com> wrote in message
news:u51QEfm5GHA.3560@.TK2MSFTNGP03.phx.gbl...
> Hi! I post a question last week but didn't get any responses, so trying it
> again.
> Basically, I am in a process of tuning a query and number of logical reads
> gets change after I run dbcc dropcleanbuffers.
> I understand that physical reads will be different after cache is flushed
> out but why logical read get changed after flushing the cache but with
> exact same execution plan.
> Sql 200 sp4
>
|||Hi Greg,
Thanks for the reply. I am already using Set statistics io on to get the
logical reads, its not from Profiler.
Do you think of any reason why logical read would be different for same
qyery, same dataset with exact same execution plan and only difference is I
flushed the cache with DBCC dropcleanbuffer.
Any input is greatly appreciated.
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:OB$Xvum5GHA.2536@.TK2MSFTNGP06.phx.gbl...
> Hi James
> The Reads reported in Profiler also includes reads performed in the
> procedure cache, which is variable depending on activity levels & dbcc
> commands.
> Profiler's reads data is useful for zero'ing on problems, but you should
> use SET STATISTICS IO ON for more accurate report on the logical reads
> performed by an individual query in the Query Analyser / Management
> Studio.
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "james" <kush@.brandes.com> wrote in message
> news:u51QEfm5GHA.3560@.TK2MSFTNGP03.phx.gbl...
>
|||Hi James
Not off the top unfortunately. How much difference are you seeing? Are you
seeing only small differences or large differences/
Is your scenario reproducable via a script that you could post? If so, I'd
be happy to spend some time looking into it. If not, any chance you could
post the set statistics io output & profiler showplan all event's BinaryData
output for each call?
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"james" <kush@.brandes.com> wrote in message
news:enD3PXz5GHA.1860@.TK2MSFTNGP04.phx.gbl...
> Hi Greg,
> Thanks for the reply. I am already using Set statistics io on to get the
> logical reads, its not from Profiler.
> Do you think of any reason why logical read would be different for same
> qyery, same dataset with exact same execution plan and only difference is
> I flushed the cache with DBCC dropcleanbuffer.
> Any input is greatly appreciated.
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:OB$Xvum5GHA.2536@.TK2MSFTNGP06.phx.gbl...
>
|||Hi Greg,
I have replied to your email address the actual query, statistics io output
and profiler trace output that you asked for. Forgive me for sending to your
email rather than entire group. I thought with all the attachment and stuff
it would be better send it to directly. Let me know if any problem with
that. Thanks once again.
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:u5NBAjz5GHA.4112@.TK2MSFTNGP04.phx.gbl...
> Hi James
> Not off the top unfortunately. How much difference are you seeing? Are you
> seeing only small differences or large differences/
> Is your scenario reproducable via a script that you could post? If so, I'd
> be happy to spend some time looking into it. If not, any chance you could
> post the set statistics io output & profiler showplan all event's
> BinaryData output for each call?
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "james" <kush@.brandes.com> wrote in message
> news:enD3PXz5GHA.1860@.TK2MSFTNGP04.phx.gbl...
>

Logical read and DBCC dropcleanbuffers

Hi! I post a question last week but didn't get any responses, so trying it
again.
Basically, I am in a process of tuning a query and number of logical reads
gets change after I run dbcc dropcleanbuffers.
I understand that physical reads will be different after cache is flushed
out but why logical read get changed after flushing the cache but with exact
same execution plan.
Sql 200 sp4Hi James
The Reads reported in Profiler also includes reads performed in the
procedure cache, which is variable depending on activity levels & dbcc
commands.
Profiler's reads data is useful for zero'ing on problems, but you should use
SET STATISTICS IO ON for more accurate report on the logical reads
performed by an individual query in the Query Analyser / Management Studio.
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"james" <kush@.brandes.com> wrote in message
news:u51QEfm5GHA.3560@.TK2MSFTNGP03.phx.gbl...
> Hi! I post a question last week but didn't get any responses, so trying it
> again.
> Basically, I am in a process of tuning a query and number of logical reads
> gets change after I run dbcc dropcleanbuffers.
> I understand that physical reads will be different after cache is flushed
> out but why logical read get changed after flushing the cache but with
> exact same execution plan.
> Sql 200 sp4
>|||Hi Greg,
Thanks for the reply. I am already using Set statistics io on to get the
logical reads, its not from Profiler.
Do you think of any reason why logical read would be different for same
qyery, same dataset with exact same execution plan and only difference is I
flushed the cache with DBCC dropcleanbuffer.
Any input is greatly appreciated.
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:OB$Xvum5GHA.2536@.TK2MSFTNGP06.phx.gbl...
> Hi James
> The Reads reported in Profiler also includes reads performed in the
> procedure cache, which is variable depending on activity levels & dbcc
> commands.
> Profiler's reads data is useful for zero'ing on problems, but you should
> use SET STATISTICS IO ON for more accurate report on the logical reads
> performed by an individual query in the Query Analyser / Management
> Studio.
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "james" <kush@.brandes.com> wrote in message
> news:u51QEfm5GHA.3560@.TK2MSFTNGP03.phx.gbl...
>|||Hi James
Not off the top unfortunately. How much difference are you seeing? Are you
seeing only small differences or large differences/
Is your scenario reproducable via a script that you could post? If so, I'd
be happy to spend some time looking into it. If not, any chance you could
post the set statistics io output & profiler showplan all event's BinaryData
output for each call?
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"james" <kush@.brandes.com> wrote in message
news:enD3PXz5GHA.1860@.TK2MSFTNGP04.phx.gbl...
> Hi Greg,
> Thanks for the reply. I am already using Set statistics io on to get the
> logical reads, its not from Profiler.
> Do you think of any reason why logical read would be different for same
> qyery, same dataset with exact same execution plan and only difference is
> I flushed the cache with DBCC dropcleanbuffer.
> Any input is greatly appreciated.
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:OB$Xvum5GHA.2536@.TK2MSFTNGP06.phx.gbl...
>|||Hi Greg,
I have replied to your email address the actual query, statistics io output
and profiler trace output that you asked for. Forgive me for sending to your
email rather than entire group. I thought with all the attachment and stuff
it would be better send it to directly. Let me know if any problem with
that. Thanks once again.
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:u5NBAjz5GHA.4112@.TK2MSFTNGP04.phx.gbl...
> Hi James
> Not off the top unfortunately. How much difference are you seeing? Are you
> seeing only small differences or large differences/
> Is your scenario reproducable via a script that you could post? If so, I'd
> be happy to spend some time looking into it. If not, any chance you could
> post the set statistics io output & profiler showplan all event's
> BinaryData output for each call?
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "james" <kush@.brandes.com> wrote in message
> news:enD3PXz5GHA.1860@.TK2MSFTNGP04.phx.gbl...
>

Logical read and DBCC dropcleanbuffers

Hi! I post a question last week but didn't get any responses, so trying it
again.
Basically, I am in a process of tuning a query and number of logical reads
gets change after I run dbcc dropcleanbuffers.
I understand that physical reads will be different after cache is flushed
out but why logical read get changed after flushing the cache but with exact
same execution plan.
Sql 200 sp4Hi James
The Reads reported in Profiler also includes reads performed in the
procedure cache, which is variable depending on activity levels & dbcc
commands.
Profiler's reads data is useful for zero'ing on problems, but you should use
SET STATISTICS IO ON for more accurate report on the logical reads
performed by an individual query in the Query Analyser / Management Studio.
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"james" <kush@.brandes.com> wrote in message
news:u51QEfm5GHA.3560@.TK2MSFTNGP03.phx.gbl...
> Hi! I post a question last week but didn't get any responses, so trying it
> again.
> Basically, I am in a process of tuning a query and number of logical reads
> gets change after I run dbcc dropcleanbuffers.
> I understand that physical reads will be different after cache is flushed
> out but why logical read get changed after flushing the cache but with
> exact same execution plan.
> Sql 200 sp4
>|||Hi Greg,
Thanks for the reply. I am already using Set statistics io on to get the
logical reads, its not from Profiler.
Do you think of any reason why logical read would be different for same
qyery, same dataset with exact same execution plan and only difference is I
flushed the cache with DBCC dropcleanbuffer.
Any input is greatly appreciated.
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:OB$Xvum5GHA.2536@.TK2MSFTNGP06.phx.gbl...
> Hi James
> The Reads reported in Profiler also includes reads performed in the
> procedure cache, which is variable depending on activity levels & dbcc
> commands.
> Profiler's reads data is useful for zero'ing on problems, but you should
> use SET STATISTICS IO ON for more accurate report on the logical reads
> performed by an individual query in the Query Analyser / Management
> Studio.
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "james" <kush@.brandes.com> wrote in message
> news:u51QEfm5GHA.3560@.TK2MSFTNGP03.phx.gbl...
>> Hi! I post a question last week but didn't get any responses, so trying
>> it again.
>> Basically, I am in a process of tuning a query and number of logical
>> reads gets change after I run dbcc dropcleanbuffers.
>> I understand that physical reads will be different after cache is flushed
>> out but why logical read get changed after flushing the cache but with
>> exact same execution plan.
>> Sql 200 sp4
>|||Hi James
Not off the top unfortunately. How much difference are you seeing? Are you
seeing only small differences or large differences/
Is your scenario reproducable via a script that you could post? If so, I'd
be happy to spend some time looking into it. If not, any chance you could
post the set statistics io output & profiler showplan all event's BinaryData
output for each call?
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"james" <kush@.brandes.com> wrote in message
news:enD3PXz5GHA.1860@.TK2MSFTNGP04.phx.gbl...
> Hi Greg,
> Thanks for the reply. I am already using Set statistics io on to get the
> logical reads, its not from Profiler.
> Do you think of any reason why logical read would be different for same
> qyery, same dataset with exact same execution plan and only difference is
> I flushed the cache with DBCC dropcleanbuffer.
> Any input is greatly appreciated.
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:OB$Xvum5GHA.2536@.TK2MSFTNGP06.phx.gbl...
>> Hi James
>> The Reads reported in Profiler also includes reads performed in the
>> procedure cache, which is variable depending on activity levels & dbcc
>> commands.
>> Profiler's reads data is useful for zero'ing on problems, but you should
>> use SET STATISTICS IO ON for more accurate report on the logical reads
>> performed by an individual query in the Query Analyser / Management
>> Studio.
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> http://blogs.sqlserver.org.au/blogs/greg_linwood
>> "james" <kush@.brandes.com> wrote in message
>> news:u51QEfm5GHA.3560@.TK2MSFTNGP03.phx.gbl...
>> Hi! I post a question last week but didn't get any responses, so trying
>> it again.
>> Basically, I am in a process of tuning a query and number of logical
>> reads gets change after I run dbcc dropcleanbuffers.
>> I understand that physical reads will be different after cache is
>> flushed
>> out but why logical read get changed after flushing the cache but with
>> exact same execution plan.
>> Sql 200 sp4
>>
>|||Hi Greg,
I have replied to your email address the actual query, statistics io output
and profiler trace output that you asked for. Forgive me for sending to your
email rather than entire group. I thought with all the attachment and stuff
it would be better send it to directly. Let me know if any problem with
that. Thanks once again.
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:u5NBAjz5GHA.4112@.TK2MSFTNGP04.phx.gbl...
> Hi James
> Not off the top unfortunately. How much difference are you seeing? Are you
> seeing only small differences or large differences/
> Is your scenario reproducable via a script that you could post? If so, I'd
> be happy to spend some time looking into it. If not, any chance you could
> post the set statistics io output & profiler showplan all event's
> BinaryData output for each call?
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "james" <kush@.brandes.com> wrote in message
> news:enD3PXz5GHA.1860@.TK2MSFTNGP04.phx.gbl...
>> Hi Greg,
>> Thanks for the reply. I am already using Set statistics io on to get the
>> logical reads, its not from Profiler.
>> Do you think of any reason why logical read would be different for same
>> qyery, same dataset with exact same execution plan and only difference is
>> I flushed the cache with DBCC dropcleanbuffer.
>> Any input is greatly appreciated.
>> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
>> news:OB$Xvum5GHA.2536@.TK2MSFTNGP06.phx.gbl...
>> Hi James
>> The Reads reported in Profiler also includes reads performed in the
>> procedure cache, which is variable depending on activity levels & dbcc
>> commands.
>> Profiler's reads data is useful for zero'ing on problems, but you should
>> use SET STATISTICS IO ON for more accurate report on the logical reads
>> performed by an individual query in the Query Analyser / Management
>> Studio.
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> http://blogs.sqlserver.org.au/blogs/greg_linwood
>> "james" <kush@.brandes.com> wrote in message
>> news:u51QEfm5GHA.3560@.TK2MSFTNGP03.phx.gbl...
>> Hi! I post a question last week but didn't get any responses, so trying
>> it again.
>> Basically, I am in a process of tuning a query and number of logical
>> reads gets change after I run dbcc dropcleanbuffers.
>> I understand that physical reads will be different after cache is
>> flushed
>> out but why logical read get changed after flushing the cache but with
>> exact same execution plan.
>> Sql 200 sp4
>>
>>
>

Logic Question

Ok so I have a little problem. I dont want code or anything I just
want a little help with the logical steps to take to complete the
process.
Little background, we have students who come and take 10 courses to get
this certification. All 10 courses MUST be completed to get certified.
First I have three tables. tblStudent and tblCourse and
tblCourseCompleted.
tblStudent(StudentID) obviously contains all students enrolled,
tblCourse(CourseID) lists all courses offered(Only 10 courses
currently) and tblCourseCompleted lists the Student ID and Course ID
and Date the Course was completed.
So based off tblCourseCompleted i have a complete list of all the
students who have completed various courses.
BUT, What I want to do is generate a report that lists the which
courses the students HAVE NOT taken. So it needs to list Each student
and every course hes missing. So if student A is missing 4 courses, it
will list student A four times with each course he has not yet
completed.
So basically i need the EXACT opposite of tblCourseCompletions.
Can anyone help me with the logic of some how generating this type of
report?Sometimes it is simpler to explain logic with an example:
SELECT *
FROM tblStudent as S
CROSS JOIN tblCourse as C
WHERE NOT EXISTS
(select * from tblCourseCompleted as X
where X.course = C.course
and X.student = S.student)
The cross join gives every possible combination of student and course.
The NOT EXISTS correlated subquery eliminates those that have been
completed.
Roy Harvey
Beacon Falls, CT
On 12 Jul 2006 08:55:23 -0700, "wmureports" <justin.a.moran@.gmail.com>
wrote:
>Ok so I have a little problem. I dont want code or anything I just
>want a little help with the logical steps to take to complete the
>process.
>Little background, we have students who come and take 10 courses to get
>this certification. All 10 courses MUST be completed to get certified.
>First I have three tables. tblStudent and tblCourse and
>tblCourseCompleted.
>tblStudent(StudentID) obviously contains all students enrolled,
>tblCourse(CourseID) lists all courses offered(Only 10 courses
>currently) and tblCourseCompleted lists the Student ID and Course ID
>and Date the Course was completed.
>So based off tblCourseCompleted i have a complete list of all the
>students who have completed various courses.
>BUT, What I want to do is generate a report that lists the which
>courses the students HAVE NOT taken. So it needs to list Each student
>and every course hes missing. So if student A is missing 4 courses, it
>will list student A four times with each course he has not yet
>completed.
>So basically i need the EXACT opposite of tblCourseCompletions.
>Can anyone help me with the logic of some how generating this type of
>report?|||Try,
select t1.*
from
(
select a.StudentID, b.CourseID
from tblStudent as a cross join tblCourse as b
) as t1
where not exists(
select *
from tblCourseCompleted as t2
where t2.StudentID = t1.StudentID and t2.CourseID = t1.CourseID
)
AMB
"wmureports" wrote:
> Ok so I have a little problem. I dont want code or anything I just
> want a little help with the logical steps to take to complete the
> process.
> Little background, we have students who come and take 10 courses to get
> this certification. All 10 courses MUST be completed to get certified.
> First I have three tables. tblStudent and tblCourse and
> tblCourseCompleted.
> tblStudent(StudentID) obviously contains all students enrolled,
> tblCourse(CourseID) lists all courses offered(Only 10 courses
> currently) and tblCourseCompleted lists the Student ID and Course ID
> and Date the Course was completed.
> So based off tblCourseCompleted i have a complete list of all the
> students who have completed various courses.
> BUT, What I want to do is generate a report that lists the which
> courses the students HAVE NOT taken. So it needs to list Each student
> and every course hes missing. So if student A is missing 4 courses, it
> will list student A four times with each course he has not yet
> completed.
> So basically i need the EXACT opposite of tblCourseCompletions.
> Can anyone help me with the logic of some how generating this type of
> report?
>|||From a logic point of view , you want to SELECT courses per student NOT IN
the tblCoursesCompalted.
The way you'be described the first report would be using an INNER JOIN , i.e
only display the record , if it exists as a
relationship in the tblCourseCompleted . On a logic level, you need to find
the set that doesn't have a relationship in that table.
You could use a LEFT OUTER JOIN and then put a condition on the WHERE
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"wmureports" <justin.a.moran@.gmail.com> wrote in message
news:1152719723.696320.29420@.75g2000cwc.googlegroups.com...
> Ok so I have a little problem. I dont want code or anything I just
> want a little help with the logical steps to take to complete the
> process.
> Little background, we have students who come and take 10 courses to get
> this certification. All 10 courses MUST be completed to get certified.
> First I have three tables. tblStudent and tblCourse and
> tblCourseCompleted.
> tblStudent(StudentID) obviously contains all students enrolled,
> tblCourse(CourseID) lists all courses offered(Only 10 courses
> currently) and tblCourseCompleted lists the Student ID and Course ID
> and Date the Course was completed.
> So based off tblCourseCompleted i have a complete list of all the
> students who have completed various courses.
> BUT, What I want to do is generate a report that lists the which
> courses the students HAVE NOT taken. So it needs to list Each student
> and every course hes missing. So if student A is missing 4 courses, it
> will list student A four times with each course he has not yet
> completed.
> So basically i need the EXACT opposite of tblCourseCompletions.
> Can anyone help me with the logic of some how generating this type of
> report?
>|||OK that worked PERFECTLY! thank you very much :)
Roy Harvey wrote:
> Sometimes it is simpler to explain logic with an example:
> SELECT *
> FROM tblStudent as S
> CROSS JOIN tblCourse as C
> WHERE NOT EXISTS
> (select * from tblCourseCompleted as X
> where X.course = C.course
> and X.student = S.student)
> The cross join gives every possible combination of student and course.
> The NOT EXISTS correlated subquery eliminates those that have been
> completed.
> Roy Harvey
> Beacon Falls, CT
> On 12 Jul 2006 08:55:23 -0700, "wmureports" <justin.a.moran@.gmail.com>
> wrote:
> >Ok so I have a little problem. I dont want code or anything I just
> >want a little help with the logical steps to take to complete the
> >process.
> >
> >Little background, we have students who come and take 10 courses to get
> >this certification. All 10 courses MUST be completed to get certified.
> >
> >First I have three tables. tblStudent and tblCourse and
> >tblCourseCompleted.
> >
> >tblStudent(StudentID) obviously contains all students enrolled,
> >tblCourse(CourseID) lists all courses offered(Only 10 courses
> >currently) and tblCourseCompleted lists the Student ID and Course ID
> >and Date the Course was completed.
> >
> >So based off tblCourseCompleted i have a complete list of all the
> >students who have completed various courses.
> >
> >BUT, What I want to do is generate a report that lists the which
> >courses the students HAVE NOT taken. So it needs to list Each student
> >and every course hes missing. So if student A is missing 4 courses, it
> >will list student A four times with each course he has not yet
> >completed.
> >
> >So basically i need the EXACT opposite of tblCourseCompletions.
> >
> >Can anyone help me with the logic of some how generating this type of
> >report?