I have installed Microsoft SQL server 2000 on a server.
My web server is having windows NT having all asp pages.
I want to connect to SQL Server 2000 using a DSN less
connection for my asp pages. Could anyone help me with
that?
Also, if I try to create system DSN on windows NT, it
gives me an error message and does not create DSN. What
could be the reason?
ThanksThe syntax depends on whether you are using OLE DB or ODBC,
it you are connecting using a standard SQL login or Windows
Authentication, etc. You can find sample ADO connection
strings which cover different scenarios at:
http://www.able-consulting.com/ADO_Conn.htm
The problem creating the DSN would depend on what the error
message is that you are receiving - it's hard to say without
more information about the error.
-Sue
On Wed, 25 Feb 2004 13:43:59 -0800, "namita"
<anonymous@.discussions.microsoft.com> wrote:
>I have installed Microsoft SQL server 2000 on a server.
>My web server is having windows NT having all asp pages.
>I want to connect to SQL Server 2000 using a DSN less
>connection for my asp pages. Could anyone help me with
>that?
>Also, if I try to create system DSN on windows NT, it
>gives me an error message and does not create DSN. What
>could be the reason?
>Thanks|||Hello,
<%
set conn1=server.CreateObject("adodb.connection")
conn1.cursorlocation=3
conn1.Open "Provider=sqloledb;" & _
"Data Source=Server;" & _
"Initial Catalog=DB;" & _
"User Id=ayaz;" & _
"Password=ayaz"
%>
and also
try this one too.
conn1.open " dsn=ayaz;uid=ayaz;pwd=ayaz;DATABASE=ayaz
;APP=ASP Script"
i hope its will help you , if any problem then email me.
Thanks,
Warm Regards,
Ayaz Ahmed
Software Engineer & Web Developer
Creative Chaos (Pvt.) Ltd.
"Managing Your Digital Risk"
http://www.csquareonline.com
Karachi, Pakistan
Mobile +92 300 2280950
Office +92 21 455 2414
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!
Showing posts with label windows. Show all posts
Showing posts with label windows. Show all posts
Thursday, March 29, 2012
Sunday, March 25, 2012
Dropped column being "dropped" before drop ??
(SQL Server 2000 SP3, Developer Edition, Windows XP Professional)
I am getting an error "Invalid column name 'sys_login_name'." when running
the following query. The query is a minor conversion of a table between
versions. One column (userid_fk) had been added in a previous batch. In
this batch several columns are being dropped IF they exist. In the first
column 'sys_login_name', before it is dropped, data is pulled in from
another table (sy_user) before the sys_user.sys_login_name is dropped. I've
used col_length() as a quick way to detect if a column exists (returns null
if it doesn't exist). In the code below, note the section where "if
col_length('sys_user', 'sys_login_name') is not null" which means it only
gets executed when sys_login_name DOES exist. The UPDATE statement in that
IF block pulls data into sys_user from sy_user based upon the sys_login_name
field. The next statement then DROPS the sys_login_name field. In Query
Analyzer, I'm getting an "Invalid column name 'sys_login_name'" error that
points back to the UPDATE statement, but the rest of the batch is executing.
The output from Query Analyzer is:
=====OUTPUT
START===================================
====================================
====
doing v2->v3 on sys_user
updating sys_user
dropping sys_login_name
dropping other columns
Server: Msg 207, Level 16, State 3, Line 14
Invalid column name 'sys_login_name'.
=====OUTPUT
END=====================================
====================================
==
Oddly enough, the error is after the PRINT statement's output, but I've seen
that asynchronous-ness (?) of PRINT and error output enough before to not be
alarmed.
Here's the batch:
=====BATCH
START===================================
====================================
====
-- v2->v3: Check if sy integration changes need to be done STEP 2: convert
and drop fields
if dbo.fn_sy_get_table_version(N'sys_user') = 2
begin
print 'doing v2->v3 on sys_user'
-- if sys_login_name exists, pull data from sy_user for conversion
-- and drop sys_login_name
if col_length('sys_user', 'sys_login_name') is not null
begin
-- fill in userid_fk from sy_user.userid_pk via login name
-- and set password to 'test' for all accounts
-- before dropping login name; entry may not exist in sy_user
print 'updating sys_user'
update sys_user
set userid_fk = isnull(SY.userid_pk, 0),
sys_password = '098f6bcd4621d373cade4e832627b4f6'
from sys_user SYS left join sy_user SY on SYS.sys_login_name =
SY.login
print 'dropping sys_login_name'
alter table sys_user drop column sys_login_name
end
print 'dropping other columns'
-- drop sys_user_first if it exists
if col_length('sys_user', 'sys_user_first') is not null
alter table sys_user drop column sys_user_first
-- drop sys_user_last if it exists
if col_length('sys_user', 'sys_user_last') is not null
alter table sys_user drop column sys_user_last
-- drop timestamp if it exists
if col_length('sys_user', 'timestamp') is not null
alter table sys_user drop column [timestamp]
exec sp_sy_addextprops N'PPD_Version', 3, N'USER', N'dbo', N'TABLE',
N'sys_user'
end
go
=====BATCH
END=====================================
===================================
Here's a sample I did to test to see if ALTER TABLE DROP COLUMN somehow gets
executed before the UPDATE, but it doesn't:
=====SAMPLE
START===================================
====================================
=
create table testdrop
(
ident int identity(100,1) not null primary key,
col1 int null,
col2 int null
)
go
insert testdrop (col1, col2) values (1,2)
update testdrop set col2 = 22 where col1=1
select * from testdrop
alter table testdrop drop column col2
select * from testdrop
go
drop table testdrop
go
=====SAMPLE
END=====================================
===================================
Thanks for any help!
Mike JansenOK, I was able to reproduce the problem by enhancing my sample:
========= BEGIN SAMPLE ================
create table testdrop
(
ident int identity(100,1) not null primary key,
col1 int null,
col2 int null
)
go
create table testdrop2
(
pk int identity(100,1) not null primary key,
col1 int null
)
go
insert testdrop2 (col1) values (1)
insert testdrop2 (col1) values (2)
insert testdrop (col1, col2) values (1,0)
insert testdrop (col1, col2) values (2,0)
insert testdrop (col1, col2) values (3,0)
select * from testdrop
update testdrop
set col2 = T2.pk
from testdrop T1 left join testdrop2 T2 on T1.col1=T2.col1
select * from testdrop
--go
alter table testdrop drop column col2
select * from testdrop
go
drop table testdrop
drop table testdrop2
go
========= END SAMPLE ====================
Note that if you uncomment the one GO statement, it works. If the ALTER
TABLE DROP COLUMN is in the same batch as the UPDATE with the JOIN in the
FROM clause, it has the error.
Thanks,
Mike|||Does anyone have any idea about the following problem with the UPDATE
statement not working (get "Invalid Column" error) when you use a column in
the UPDATE's FROM clause (in a JOIN) and then drop that column via ALTER
TABLE in the same batch?
I can work around the problem, but I'd like to know if this is a SQL bug or
if I am ignorant of something fundamental in SQL Server (working with the
guys I work with, I had to qualify what I might be ignorant about or they
might pipe in all too quickly to confirm that I'm just ignorant <g> )
Thanks,
Mike
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:ek5KSIaVFHA.1508@.tk2msftngp13.phx.gbl...
> OK, I was able to reproduce the problem by enhancing my sample:
> ========= BEGIN SAMPLE ================
> create table testdrop
> (
> ident int identity(100,1) not null primary key,
> col1 int null,
> col2 int null
> )
> go
> create table testdrop2
> (
> pk int identity(100,1) not null primary key,
> col1 int null
> )
> go
> insert testdrop2 (col1) values (1)
> insert testdrop2 (col1) values (2)
> insert testdrop (col1, col2) values (1,0)
> insert testdrop (col1, col2) values (2,0)
> insert testdrop (col1, col2) values (3,0)
> select * from testdrop
> update testdrop
> set col2 = T2.pk
> from testdrop T1 left join testdrop2 T2 on T1.col1=T2.col1
> select * from testdrop
> --go
> alter table testdrop drop column col2
> select * from testdrop
> go
> drop table testdrop
> drop table testdrop2
> go
> ========= END SAMPLE ====================
> Note that if you uncomment the one GO statement, it works. If the ALTER
> TABLE DROP COLUMN is in the same batch as the UPDATE with the JOIN in the
> FROM clause, it has the error.
> Thanks,
> Mike
>|||Dropping the column forces a recompile of the entire batch. That's why
you get an error. It's the expected behaviour.
For this and other reasons try to keep DDL and DML code entirely
separate. Put your ALTER statements in a separate batch.
David Portas
SQL Server MVP
--|||1. If it's recompiling the batch because of the DDL, why does my "simple
sample" work where I have an UPDATE with no FROM clause but I SET the column
and then drop it in the next line with an ALTER TABLE? If it were
recompiling the batch, I'd think that it would fail on that as well.
Here's a re-post of my simple sample that works:
==== BEGIN SAMPLE ==============
create table testdrop
(
ident int identity(100,1) not null primary key,
col1 int null,
col2 int null
)
go
insert testdrop (col1, col2) values (1,2)
update testdrop set col2 = 22 where col1=1
select * from testdrop
alter table testdrop drop column col2
select * from testdrop
go
drop table testdrop
go
==== END SAMPLE ==============
2. What are the other reasons for putting DDL and DML in separate batches
(besides the re-compile issue)?
Thanks for your help,
Mike
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1115815055.958304.192040@.g47g2000cwa.googlegroups.com...
> Dropping the column forces a recompile of the entire batch. That's why
> you get an error. It's the expected behaviour.
> For this and other reasons try to keep DDL and DML code entirely
> separate. Put your ALTER statements in a separate batch.
> --
> David Portas
> SQL Server MVP
> --
>|||I did a little research and found the answer to question #2 (What are the
other reasons for putting DDL and DML in separate batches - besides the
re-compile issue): It's related to the re-compile issue: performance. The
recompilation obviously causes performance issues. Since the compilation of
the batches is probably 1% or less of the time in the scenario I'm talking
about and it's a once-in-a-while script, that isn't really a significant
factor. I did end up changing my script though to put the DDL and DML in
separate batches since I'm still getting the "invalid column" error -- which
probably is related to the re-compiling (perhaps in the simple example
something is optimized in such a way that the batch didn't need to be
re-compiled ')
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:O1nV6niVFHA.2420@.TK2MSFTNGP12.phx.gbl...
> 1. If it's recompiling the batch because of the DDL, why does my "simple
> sample" work where I have an UPDATE with no FROM clause but I SET the
column
> and then drop it in the next line with an ALTER TABLE? If it were
> recompiling the batch, I'd think that it would fail on that as well.
> Here's a re-post of my simple sample that works:
> ==== BEGIN SAMPLE ==============
> create table testdrop
> (
> ident int identity(100,1) not null primary key,
> col1 int null,
> col2 int null
> )
> go
> insert testdrop (col1, col2) values (1,2)
> update testdrop set col2 = 22 where col1=1
> select * from testdrop
> alter table testdrop drop column col2
> select * from testdrop
> go
> drop table testdrop
> go
> ==== END SAMPLE ==============
> 2. What are the other reasons for putting DDL and DML in separate batches
> (besides the re-compile issue)?
>
> Thanks for your help,
> Mike
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1115815055.958304.192040@.g47g2000cwa.googlegroups.com...
>
I am getting an error "Invalid column name 'sys_login_name'." when running
the following query. The query is a minor conversion of a table between
versions. One column (userid_fk) had been added in a previous batch. In
this batch several columns are being dropped IF they exist. In the first
column 'sys_login_name', before it is dropped, data is pulled in from
another table (sy_user) before the sys_user.sys_login_name is dropped. I've
used col_length() as a quick way to detect if a column exists (returns null
if it doesn't exist). In the code below, note the section where "if
col_length('sys_user', 'sys_login_name') is not null" which means it only
gets executed when sys_login_name DOES exist. The UPDATE statement in that
IF block pulls data into sys_user from sy_user based upon the sys_login_name
field. The next statement then DROPS the sys_login_name field. In Query
Analyzer, I'm getting an "Invalid column name 'sys_login_name'" error that
points back to the UPDATE statement, but the rest of the batch is executing.
The output from Query Analyzer is:
=====OUTPUT
START===================================
====================================
====
doing v2->v3 on sys_user
updating sys_user
dropping sys_login_name
dropping other columns
Server: Msg 207, Level 16, State 3, Line 14
Invalid column name 'sys_login_name'.
=====OUTPUT
END=====================================
====================================
==
Oddly enough, the error is after the PRINT statement's output, but I've seen
that asynchronous-ness (?) of PRINT and error output enough before to not be
alarmed.
Here's the batch:
=====BATCH
START===================================
====================================
====
-- v2->v3: Check if sy integration changes need to be done STEP 2: convert
and drop fields
if dbo.fn_sy_get_table_version(N'sys_user') = 2
begin
print 'doing v2->v3 on sys_user'
-- if sys_login_name exists, pull data from sy_user for conversion
-- and drop sys_login_name
if col_length('sys_user', 'sys_login_name') is not null
begin
-- fill in userid_fk from sy_user.userid_pk via login name
-- and set password to 'test' for all accounts
-- before dropping login name; entry may not exist in sy_user
print 'updating sys_user'
update sys_user
set userid_fk = isnull(SY.userid_pk, 0),
sys_password = '098f6bcd4621d373cade4e832627b4f6'
from sys_user SYS left join sy_user SY on SYS.sys_login_name =
SY.login
print 'dropping sys_login_name'
alter table sys_user drop column sys_login_name
end
print 'dropping other columns'
-- drop sys_user_first if it exists
if col_length('sys_user', 'sys_user_first') is not null
alter table sys_user drop column sys_user_first
-- drop sys_user_last if it exists
if col_length('sys_user', 'sys_user_last') is not null
alter table sys_user drop column sys_user_last
-- drop timestamp if it exists
if col_length('sys_user', 'timestamp') is not null
alter table sys_user drop column [timestamp]
exec sp_sy_addextprops N'PPD_Version', 3, N'USER', N'dbo', N'TABLE',
N'sys_user'
end
go
=====BATCH
END=====================================
===================================
Here's a sample I did to test to see if ALTER TABLE DROP COLUMN somehow gets
executed before the UPDATE, but it doesn't:
=====SAMPLE
START===================================
====================================
=
create table testdrop
(
ident int identity(100,1) not null primary key,
col1 int null,
col2 int null
)
go
insert testdrop (col1, col2) values (1,2)
update testdrop set col2 = 22 where col1=1
select * from testdrop
alter table testdrop drop column col2
select * from testdrop
go
drop table testdrop
go
=====SAMPLE
END=====================================
===================================
Thanks for any help!
Mike JansenOK, I was able to reproduce the problem by enhancing my sample:
========= BEGIN SAMPLE ================
create table testdrop
(
ident int identity(100,1) not null primary key,
col1 int null,
col2 int null
)
go
create table testdrop2
(
pk int identity(100,1) not null primary key,
col1 int null
)
go
insert testdrop2 (col1) values (1)
insert testdrop2 (col1) values (2)
insert testdrop (col1, col2) values (1,0)
insert testdrop (col1, col2) values (2,0)
insert testdrop (col1, col2) values (3,0)
select * from testdrop
update testdrop
set col2 = T2.pk
from testdrop T1 left join testdrop2 T2 on T1.col1=T2.col1
select * from testdrop
--go
alter table testdrop drop column col2
select * from testdrop
go
drop table testdrop
drop table testdrop2
go
========= END SAMPLE ====================
Note that if you uncomment the one GO statement, it works. If the ALTER
TABLE DROP COLUMN is in the same batch as the UPDATE with the JOIN in the
FROM clause, it has the error.
Thanks,
Mike|||Does anyone have any idea about the following problem with the UPDATE
statement not working (get "Invalid Column" error) when you use a column in
the UPDATE's FROM clause (in a JOIN) and then drop that column via ALTER
TABLE in the same batch?
I can work around the problem, but I'd like to know if this is a SQL bug or
if I am ignorant of something fundamental in SQL Server (working with the
guys I work with, I had to qualify what I might be ignorant about or they
might pipe in all too quickly to confirm that I'm just ignorant <g> )
Thanks,
Mike
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:ek5KSIaVFHA.1508@.tk2msftngp13.phx.gbl...
> OK, I was able to reproduce the problem by enhancing my sample:
> ========= BEGIN SAMPLE ================
> create table testdrop
> (
> ident int identity(100,1) not null primary key,
> col1 int null,
> col2 int null
> )
> go
> create table testdrop2
> (
> pk int identity(100,1) not null primary key,
> col1 int null
> )
> go
> insert testdrop2 (col1) values (1)
> insert testdrop2 (col1) values (2)
> insert testdrop (col1, col2) values (1,0)
> insert testdrop (col1, col2) values (2,0)
> insert testdrop (col1, col2) values (3,0)
> select * from testdrop
> update testdrop
> set col2 = T2.pk
> from testdrop T1 left join testdrop2 T2 on T1.col1=T2.col1
> select * from testdrop
> --go
> alter table testdrop drop column col2
> select * from testdrop
> go
> drop table testdrop
> drop table testdrop2
> go
> ========= END SAMPLE ====================
> Note that if you uncomment the one GO statement, it works. If the ALTER
> TABLE DROP COLUMN is in the same batch as the UPDATE with the JOIN in the
> FROM clause, it has the error.
> Thanks,
> Mike
>|||Dropping the column forces a recompile of the entire batch. That's why
you get an error. It's the expected behaviour.
For this and other reasons try to keep DDL and DML code entirely
separate. Put your ALTER statements in a separate batch.
David Portas
SQL Server MVP
--|||1. If it's recompiling the batch because of the DDL, why does my "simple
sample" work where I have an UPDATE with no FROM clause but I SET the column
and then drop it in the next line with an ALTER TABLE? If it were
recompiling the batch, I'd think that it would fail on that as well.
Here's a re-post of my simple sample that works:
==== BEGIN SAMPLE ==============
create table testdrop
(
ident int identity(100,1) not null primary key,
col1 int null,
col2 int null
)
go
insert testdrop (col1, col2) values (1,2)
update testdrop set col2 = 22 where col1=1
select * from testdrop
alter table testdrop drop column col2
select * from testdrop
go
drop table testdrop
go
==== END SAMPLE ==============
2. What are the other reasons for putting DDL and DML in separate batches
(besides the re-compile issue)?
Thanks for your help,
Mike
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1115815055.958304.192040@.g47g2000cwa.googlegroups.com...
> Dropping the column forces a recompile of the entire batch. That's why
> you get an error. It's the expected behaviour.
> For this and other reasons try to keep DDL and DML code entirely
> separate. Put your ALTER statements in a separate batch.
> --
> David Portas
> SQL Server MVP
> --
>|||I did a little research and found the answer to question #2 (What are the
other reasons for putting DDL and DML in separate batches - besides the
re-compile issue): It's related to the re-compile issue: performance. The
recompilation obviously causes performance issues. Since the compilation of
the batches is probably 1% or less of the time in the scenario I'm talking
about and it's a once-in-a-while script, that isn't really a significant
factor. I did end up changing my script though to put the DDL and DML in
separate batches since I'm still getting the "invalid column" error -- which
probably is related to the re-compiling (perhaps in the simple example
something is optimized in such a way that the batch didn't need to be
re-compiled ')
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:O1nV6niVFHA.2420@.TK2MSFTNGP12.phx.gbl...
> 1. If it's recompiling the batch because of the DDL, why does my "simple
> sample" work where I have an UPDATE with no FROM clause but I SET the
column
> and then drop it in the next line with an ALTER TABLE? If it were
> recompiling the batch, I'd think that it would fail on that as well.
> Here's a re-post of my simple sample that works:
> ==== BEGIN SAMPLE ==============
> create table testdrop
> (
> ident int identity(100,1) not null primary key,
> col1 int null,
> col2 int null
> )
> go
> insert testdrop (col1, col2) values (1,2)
> update testdrop set col2 = 22 where col1=1
> select * from testdrop
> alter table testdrop drop column col2
> select * from testdrop
> go
> drop table testdrop
> go
> ==== END SAMPLE ==============
> 2. What are the other reasons for putting DDL and DML in separate batches
> (besides the re-compile issue)?
>
> Thanks for your help,
> Mike
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1115815055.958304.192040@.g47g2000cwa.googlegroups.com...
>
Monday, March 19, 2012
Drop login error
Hi
I am trying to drop a windows login via Management Studio (CTP June) but it comes up with
'Login domain\user' has granted one or more permissions. Revoke the permission before dropping the login (Microsoft SQL Server, Error: 15173)
I cannot see of any permissions that this user has granted. Is there a system view in SQL 2005 which shows permissions this user has granted?
Thanks,
Priyanga
You need to look at the "Security Catalog Views" topic in Books Online. Specifically, take a look at sys.server_permissions and sys.server_principals views to map principal_id of the login you are looking at to the permissions. You can do that at database level as well.
I am trying to drop a windows login via Management Studio (CTP June) but it comes up with
'Login domain\user' has granted one or more permissions. Revoke the permission before dropping the login (Microsoft SQL Server, Error: 15173)
I cannot see of any permissions that this user has granted. Is there a system view in SQL 2005 which shows permissions this user has granted?
Thanks,
Priyanga
You need to look at the "Security Catalog Views" topic in Books Online. Specifically, take a look at sys.server_permissions and sys.server_principals views to map principal_id of the login you are looking at to the permissions. You can do that at database level as well.
Something along these lines (use this as an idea):
select * from sys.server_permissions
where grantee_principal_id =
(select principal_id from sys.server_principals where name = N'DOMAIN\user')
Alternatively, in Object explorer, you can select Security->Logins->DOMAIN/user, Properties of the login, go Securables and add objects (e.g. server(s)) and this will show you explicit permissions of this login on the object(s). You can revoke there explicit permissions as well.
Then you can also go to the properties of the server in Object Explorer, Permissions and click on the login and click on Effective Permissions. This will show all the permissions for this user based on its role membership and explicitly granted permissions.
HTH,
Boris.
Thanks Boris.
What was causing the issue was that the principal_id (nation\pk0159_a) was linked to grantor_principal_id in the sys.server_permissions as apposed to the grantee_principal_id. The object responsible for this issue was an endpoint. When i dropped the endpoint, i was allowed to drop nation\pk0159_a.
This explains why no objects were showing as being granted permissions by nation\pk0159_a in object explorer in SQL Management Studio.
Cheers,
Priyanga
Sunday, February 26, 2012
Drives on Server and How to Define Raid for them
We are looking for advise. I know that there is no right or wrong answer. "It
depends."
We currently have a server with the OS as Windows Server 2003 SP2 and SQL
Server 2000 SP3. This Server has 6 physical drives; only 3 of these physical
drives are being used. These 3 physical drives are 1 container with Raid 5.
This 1 container is divided into 3 logical drives.
We would like to fill the other 3 physical drive slots and create another
container. We were thinking of making this Raid 1.
I should put in my disclaimer that Raid 10 is out of the question and so is
SAN. The company just doesn't have the money.
Is Raid 1 the best choice? This is my first question.
Next is how should we split up the files among the containers.
For example, OS, log and swap file on container 1 with Raid 1 and datafiles
on container 2 with Raid 5?
What are most people doing? Is there a standard? Can people provide examples
of what they are doing or provide suggestions?
Is there a microsoft recommendation for a windows server 2003/sql server
2000 with 6 physical drives on how the drives and raid should be set up? Is
there a cook book recipe? My management wants facts. They want certified
microsoft documentation on this subject.
I was thinking what if some type of external drive could be attached. The
first container of 3 physical drives could be Raid5 and the second container
of 3 physical drives could be Raid 1 and then the external drive could be
Raid 0 for tempdb. Maybe this is a bad idea?If you can completely redo the server from the ground up I would do consider
either a) raid1 set for OS/apps/pagefile and a raid10 set for all sql server
data and log files (with perhaps tempdb on the raid 1 or b) raid1 same as
above but make 4 remaining drives a raid5 set if you need the space or if
read performance is key.
if you can redo from scratch then if you want all 3 new drives involved in
raid to prevent single-disk-failure issue then you are pretty much stuck
with raid5 and it's inherent write performance issue. You could go with 2
disk raid1 and use the third drive alone as a backup drive. That sounds
pretty good.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"lcerni" <lcerni@.discussions.microsoft.com> wrote in message
news:410EFCF8-8D93-4722-A689-CAE1BF237A9B@.microsoft.com...
> We are looking for advise. I know that there is no right or wrong answer.
> "It
> depends."
> We currently have a server with the OS as Windows Server 2003 SP2 and SQL
> Server 2000 SP3. This Server has 6 physical drives; only 3 of these
> physical
> drives are being used. These 3 physical drives are 1 container with Raid
> 5.
> This 1 container is divided into 3 logical drives.
> We would like to fill the other 3 physical drive slots and create another
> container. We were thinking of making this Raid 1.
> I should put in my disclaimer that Raid 10 is out of the question and so
> is
> SAN. The company just doesn't have the money.
> Is Raid 1 the best choice? This is my first question.
> Next is how should we split up the files among the containers.
> For example, OS, log and swap file on container 1 with Raid 1 and
> datafiles
> on container 2 with Raid 5?
> What are most people doing? Is there a standard? Can people provide
> examples
> of what they are doing or provide suggestions?
> Is there a microsoft recommendation for a windows server 2003/sql server
> 2000 with 6 physical drives on how the drives and raid should be set up?
> Is
> there a cook book recipe? My management wants facts. They want certified
> microsoft documentation on this subject.
> I was thinking what if some type of external drive could be attached. The
> first container of 3 physical drives could be Raid5 and the second
> container
> of 3 physical drives could be Raid 1 and then the external drive could be
> Raid 0 for tempdb. Maybe this is a bad idea?|||There is no way to document what you should have because there are too many
factors to be considered. Mainly it comes down to how you use your database,
how the server overall is configured and what your transactions rates will
be. If you have a very low volume server then having it all on one Raid 5
may be fine. But as you get into higher transactions rates or more data
manipulation you will eventually need to split the logs onto another
physical array. Adding a Raid 1 for the OS /Swap file / Logs is probably
fine if this is not a large system and if you have enough memory to avoid
lots of direct disk access. But I don't recommend creating multiple logical
drives on either the Raid 5 or the Raid 1. That would do nothing for
performance and leave you with the possibility you may run out of room on
any one of the logical drives. One last comment. You said no Raid 10 but
you will have 1 slot left if you do 3 Disk Raid 5 and 2 disk Raid 1. You can
make a 4 disk Raid 10 and a 2 disk Raid 1. Or you can add the other drive to
the Raid 5 which would give better performance over the 3 disk Raid 5.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"lcerni" <lcerni@.discussions.microsoft.com> wrote in message
news:410EFCF8-8D93-4722-A689-CAE1BF237A9B@.microsoft.com...
> We are looking for advise. I know that there is no right or wrong answer.
> "It
> depends."
> We currently have a server with the OS as Windows Server 2003 SP2 and SQL
> Server 2000 SP3. This Server has 6 physical drives; only 3 of these
> physical
> drives are being used. These 3 physical drives are 1 container with Raid
> 5.
> This 1 container is divided into 3 logical drives.
> We would like to fill the other 3 physical drive slots and create another
> container. We were thinking of making this Raid 1.
> I should put in my disclaimer that Raid 10 is out of the question and so
> is
> SAN. The company just doesn't have the money.
> Is Raid 1 the best choice? This is my first question.
> Next is how should we split up the files among the containers.
> For example, OS, log and swap file on container 1 with Raid 1 and
> datafiles
> on container 2 with Raid 5?
> What are most people doing? Is there a standard? Can people provide
> examples
> of what they are doing or provide suggestions?
> Is there a microsoft recommendation for a windows server 2003/sql server
> 2000 with 6 physical drives on how the drives and raid should be set up?
> Is
> there a cook book recipe? My management wants facts. They want certified
> microsoft documentation on this subject.
> I was thinking what if some type of external drive could be attached. The
> first container of 3 physical drives could be Raid5 and the second
> container
> of 3 physical drives could be Raid 1 and then the external drive could be
> Raid 0 for tempdb. Maybe this is a bad idea?
depends."
We currently have a server with the OS as Windows Server 2003 SP2 and SQL
Server 2000 SP3. This Server has 6 physical drives; only 3 of these physical
drives are being used. These 3 physical drives are 1 container with Raid 5.
This 1 container is divided into 3 logical drives.
We would like to fill the other 3 physical drive slots and create another
container. We were thinking of making this Raid 1.
I should put in my disclaimer that Raid 10 is out of the question and so is
SAN. The company just doesn't have the money.
Is Raid 1 the best choice? This is my first question.
Next is how should we split up the files among the containers.
For example, OS, log and swap file on container 1 with Raid 1 and datafiles
on container 2 with Raid 5?
What are most people doing? Is there a standard? Can people provide examples
of what they are doing or provide suggestions?
Is there a microsoft recommendation for a windows server 2003/sql server
2000 with 6 physical drives on how the drives and raid should be set up? Is
there a cook book recipe? My management wants facts. They want certified
microsoft documentation on this subject.
I was thinking what if some type of external drive could be attached. The
first container of 3 physical drives could be Raid5 and the second container
of 3 physical drives could be Raid 1 and then the external drive could be
Raid 0 for tempdb. Maybe this is a bad idea?If you can completely redo the server from the ground up I would do consider
either a) raid1 set for OS/apps/pagefile and a raid10 set for all sql server
data and log files (with perhaps tempdb on the raid 1 or b) raid1 same as
above but make 4 remaining drives a raid5 set if you need the space or if
read performance is key.
if you can redo from scratch then if you want all 3 new drives involved in
raid to prevent single-disk-failure issue then you are pretty much stuck
with raid5 and it's inherent write performance issue. You could go with 2
disk raid1 and use the third drive alone as a backup drive. That sounds
pretty good.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"lcerni" <lcerni@.discussions.microsoft.com> wrote in message
news:410EFCF8-8D93-4722-A689-CAE1BF237A9B@.microsoft.com...
> We are looking for advise. I know that there is no right or wrong answer.
> "It
> depends."
> We currently have a server with the OS as Windows Server 2003 SP2 and SQL
> Server 2000 SP3. This Server has 6 physical drives; only 3 of these
> physical
> drives are being used. These 3 physical drives are 1 container with Raid
> 5.
> This 1 container is divided into 3 logical drives.
> We would like to fill the other 3 physical drive slots and create another
> container. We were thinking of making this Raid 1.
> I should put in my disclaimer that Raid 10 is out of the question and so
> is
> SAN. The company just doesn't have the money.
> Is Raid 1 the best choice? This is my first question.
> Next is how should we split up the files among the containers.
> For example, OS, log and swap file on container 1 with Raid 1 and
> datafiles
> on container 2 with Raid 5?
> What are most people doing? Is there a standard? Can people provide
> examples
> of what they are doing or provide suggestions?
> Is there a microsoft recommendation for a windows server 2003/sql server
> 2000 with 6 physical drives on how the drives and raid should be set up?
> Is
> there a cook book recipe? My management wants facts. They want certified
> microsoft documentation on this subject.
> I was thinking what if some type of external drive could be attached. The
> first container of 3 physical drives could be Raid5 and the second
> container
> of 3 physical drives could be Raid 1 and then the external drive could be
> Raid 0 for tempdb. Maybe this is a bad idea?|||There is no way to document what you should have because there are too many
factors to be considered. Mainly it comes down to how you use your database,
how the server overall is configured and what your transactions rates will
be. If you have a very low volume server then having it all on one Raid 5
may be fine. But as you get into higher transactions rates or more data
manipulation you will eventually need to split the logs onto another
physical array. Adding a Raid 1 for the OS /Swap file / Logs is probably
fine if this is not a large system and if you have enough memory to avoid
lots of direct disk access. But I don't recommend creating multiple logical
drives on either the Raid 5 or the Raid 1. That would do nothing for
performance and leave you with the possibility you may run out of room on
any one of the logical drives. One last comment. You said no Raid 10 but
you will have 1 slot left if you do 3 Disk Raid 5 and 2 disk Raid 1. You can
make a 4 disk Raid 10 and a 2 disk Raid 1. Or you can add the other drive to
the Raid 5 which would give better performance over the 3 disk Raid 5.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"lcerni" <lcerni@.discussions.microsoft.com> wrote in message
news:410EFCF8-8D93-4722-A689-CAE1BF237A9B@.microsoft.com...
> We are looking for advise. I know that there is no right or wrong answer.
> "It
> depends."
> We currently have a server with the OS as Windows Server 2003 SP2 and SQL
> Server 2000 SP3. This Server has 6 physical drives; only 3 of these
> physical
> drives are being used. These 3 physical drives are 1 container with Raid
> 5.
> This 1 container is divided into 3 logical drives.
> We would like to fill the other 3 physical drive slots and create another
> container. We were thinking of making this Raid 1.
> I should put in my disclaimer that Raid 10 is out of the question and so
> is
> SAN. The company just doesn't have the money.
> Is Raid 1 the best choice? This is my first question.
> Next is how should we split up the files among the containers.
> For example, OS, log and swap file on container 1 with Raid 1 and
> datafiles
> on container 2 with Raid 5?
> What are most people doing? Is there a standard? Can people provide
> examples
> of what they are doing or provide suggestions?
> Is there a microsoft recommendation for a windows server 2003/sql server
> 2000 with 6 physical drives on how the drives and raid should be set up?
> Is
> there a cook book recipe? My management wants facts. They want certified
> microsoft documentation on this subject.
> I was thinking what if some type of external drive could be attached. The
> first container of 3 physical drives could be Raid5 and the second
> container
> of 3 physical drives could be Raid 1 and then the external drive could be
> Raid 0 for tempdb. Maybe this is a bad idea?
driver's sqlallochandle on sql_handle_env failed
i have just installed sql clinet
(Microsoft SQL Enterprise Manager Microsoft Corporation Version: 8.0)
on windows XP sp2 . whenever i try to reg new sql servers through Enterprise
Mgr it gives me this error . it looks like its associated with ODBC . need to
figure out whats the problem
Thanks & Regards
Sid
Try applying SQL Server 2000 SP4 + Cumulative Hotfix Build 2187.
http://www.microsoft.com/downloads/details.aspx?FamilyID=8e2dfc8d-c20e-4446-99a9-b7f0213f8bc5&DisplayLang=en
http://www.microsoft.com/downloads/details.aspx?FamilyID=9c9ab140-bdee-44df-b7a3-e6849297754a&displaylang=en
http://www.microsoft.com/downloads/details.aspx?FamilyID=1705bd2f-1fb8-4ec8-b3db-0935361308c7&displaylang=en
http://www.microsoft.com/downloads/details.aspx?familyid=A643980A-26A4-44C1-9B50-53E20E7210B5&displaylang=en
http://www.microsoft.com/downloads/details.aspx?FamilyId=243A8A89-74D6-48FD-933F-32FF9D8459C2&displaylang=en
http://www.microsoft.com/downloads/details.aspx?FamilyId=2BB62F35-D041-42AC-98DA-6EC97168BE21&displaylang=en
Sincerely,
Anthony Thomas
"moharil" <sid_m15@.yahoo.com> wrote in message
news:DCFB87D2-813A-4707-8D8B-9EDB6E781058@.microsoft.com...
> i have just installed sql clinet
> (Microsoft SQL Enterprise Manager Microsoft Corporation Version: 8.0)
> on windows XP sp2 . whenever i try to reg new sql servers through
Enterprise
> Mgr it gives me this error . it looks like its associated with ODBC . need
to
> figure out whats the problem
> --
> Thanks & Regards
> Sid
(Microsoft SQL Enterprise Manager Microsoft Corporation Version: 8.0)
on windows XP sp2 . whenever i try to reg new sql servers through Enterprise
Mgr it gives me this error . it looks like its associated with ODBC . need to
figure out whats the problem
Thanks & Regards
Sid
Try applying SQL Server 2000 SP4 + Cumulative Hotfix Build 2187.
http://www.microsoft.com/downloads/details.aspx?FamilyID=8e2dfc8d-c20e-4446-99a9-b7f0213f8bc5&DisplayLang=en
http://www.microsoft.com/downloads/details.aspx?FamilyID=9c9ab140-bdee-44df-b7a3-e6849297754a&displaylang=en
http://www.microsoft.com/downloads/details.aspx?FamilyID=1705bd2f-1fb8-4ec8-b3db-0935361308c7&displaylang=en
http://www.microsoft.com/downloads/details.aspx?familyid=A643980A-26A4-44C1-9B50-53E20E7210B5&displaylang=en
http://www.microsoft.com/downloads/details.aspx?FamilyId=243A8A89-74D6-48FD-933F-32FF9D8459C2&displaylang=en
http://www.microsoft.com/downloads/details.aspx?FamilyId=2BB62F35-D041-42AC-98DA-6EC97168BE21&displaylang=en
Sincerely,
Anthony Thomas
"moharil" <sid_m15@.yahoo.com> wrote in message
news:DCFB87D2-813A-4707-8D8B-9EDB6E781058@.microsoft.com...
> i have just installed sql clinet
> (Microsoft SQL Enterprise Manager Microsoft Corporation Version: 8.0)
> on windows XP sp2 . whenever i try to reg new sql servers through
Enterprise
> Mgr it gives me this error . it looks like its associated with ODBC . need
to
> figure out whats the problem
> --
> Thanks & Regards
> Sid
Friday, February 24, 2012
Drive missing from Enterprise Manager
Hi,
I have an SQL cluster on Windows 2003 with 2 drives (1 for dbs and 1 for logs). Both of these drives are accessible from Windows explorer.
In Enterprise Manager only the db drive appears as a location to house dbs and logs.
I can't work out how to get the second drive to appear and adding the path manual doesn't work as I end up with the other drive letter appending to the front eg "y:\z:\logs"
Has anyone seen this or have any ideas how to fix it?
Help much appreciated.What's your cluster like? Active/Passive or Active/Active?|||The cluster is active/passive.
Just figured it out, I forgot to add the log drive to the dependennce list for the SQL service.
I have an SQL cluster on Windows 2003 with 2 drives (1 for dbs and 1 for logs). Both of these drives are accessible from Windows explorer.
In Enterprise Manager only the db drive appears as a location to house dbs and logs.
I can't work out how to get the second drive to appear and adding the path manual doesn't work as I end up with the other drive letter appending to the front eg "y:\z:\logs"
Has anyone seen this or have any ideas how to fix it?
Help much appreciated.What's your cluster like? Active/Passive or Active/Active?|||The cluster is active/passive.
Just figured it out, I forgot to add the log drive to the dependennce list for the SQL service.
Drive capacity
Hi folks,
I need to find out Drive capacity and free space, I do not have access
to Windows. I just have sa access to the SQL Server. I used
xp_fixeddrives to find out the free space, but how do I know the
capacity of the drive.
Thanks in advance.
--
*** Sent via Developersdex http://www.examnotes.net ***Well, if you are using Windows Server 2003, you can use wmic for this.
SET NOCOUNT ON;
CREATE TABLE #drives
(
drive CHAR(2),
MBFree BIGINT
);
CREATE TABLE #scratch
(
lineitem NVARCHAR(2048)
);
INSERT #drives EXEC master..xp_fixeddrives;
-- might be a good idea to call wmic manually once first
-- to make sure it is installed and loaded
INSERT #scratch EXEC master..xp_cmdshell 'wmic volume list'
SELECT d.drive, s.Capacity, d.MBFree,
[%free] = CONVERT(DECIMAL(5,2), d.MBFree / s.Capacity * 100)
FROM #drives d INNER JOIN (
SELECT capacity = CONVERT(DECIMAL(10,2), CONVERT(BIGINT,
SUBSTRING(lineitem, 23, 13)) / 1024 / 1024.0),
drive = REPLACE(SUBSTRING(lineitem, 112, 2), ':', '')
FROM #scratch
WHERE SUBSTRING(lineitem, 125, 1) = '3'
) s ON d.drive = s.drive
ORDER BY 1;
DROP TABLE #drives, #scratch;
Else, I would use something outside of SQL Server. E.g. you can get this
information from scripting.filesystemobject in a VBS script, called through
wscript and scheduled through windows task scheduler, and stuff its output
into the database, instead of expecting the database to do it internally...
"Venkat" <nospam_venkat_asp@.yahoo.co.uk> wrote in message
news:OxTikU2FGHA.2912@.tk2msftngp13.phx.gbl...
> Hi folks,
> I need to find out Drive capacity and free space, I do not have access
> to Windows. I just have sa access to the SQL Server. I used
> xp_fixeddrives to find out the free space, but how do I know the
> capacity of the drive.
> Thanks in advance.
> --
> *** Sent via Developersdex http://www.examnotes.net ***|||Wow!!! This is what I was looking for. Thanks a lot Aaron. You are
great.
*** Sent via Developersdex http://www.examnotes.net ***
I need to find out Drive capacity and free space, I do not have access
to Windows. I just have sa access to the SQL Server. I used
xp_fixeddrives to find out the free space, but how do I know the
capacity of the drive.
Thanks in advance.
--
*** Sent via Developersdex http://www.examnotes.net ***Well, if you are using Windows Server 2003, you can use wmic for this.
SET NOCOUNT ON;
CREATE TABLE #drives
(
drive CHAR(2),
MBFree BIGINT
);
CREATE TABLE #scratch
(
lineitem NVARCHAR(2048)
);
INSERT #drives EXEC master..xp_fixeddrives;
-- might be a good idea to call wmic manually once first
-- to make sure it is installed and loaded
INSERT #scratch EXEC master..xp_cmdshell 'wmic volume list'
SELECT d.drive, s.Capacity, d.MBFree,
[%free] = CONVERT(DECIMAL(5,2), d.MBFree / s.Capacity * 100)
FROM #drives d INNER JOIN (
SELECT capacity = CONVERT(DECIMAL(10,2), CONVERT(BIGINT,
SUBSTRING(lineitem, 23, 13)) / 1024 / 1024.0),
drive = REPLACE(SUBSTRING(lineitem, 112, 2), ':', '')
FROM #scratch
WHERE SUBSTRING(lineitem, 125, 1) = '3'
) s ON d.drive = s.drive
ORDER BY 1;
DROP TABLE #drives, #scratch;
Else, I would use something outside of SQL Server. E.g. you can get this
information from scripting.filesystemobject in a VBS script, called through
wscript and scheduled through windows task scheduler, and stuff its output
into the database, instead of expecting the database to do it internally...
"Venkat" <nospam_venkat_asp@.yahoo.co.uk> wrote in message
news:OxTikU2FGHA.2912@.tk2msftngp13.phx.gbl...
> Hi folks,
> I need to find out Drive capacity and free space, I do not have access
> to Windows. I just have sa access to the SQL Server. I used
> xp_fixeddrives to find out the free space, but how do I know the
> capacity of the drive.
> Thanks in advance.
> --
> *** Sent via Developersdex http://www.examnotes.net ***|||Wow!!! This is what I was looking for. Thanks a lot Aaron. You are
great.
*** Sent via Developersdex http://www.examnotes.net ***
Sunday, February 19, 2012
Drillthrough problems with Reporting services in Integrated Mode
We have started to use reporting services in integrated mode with windows sharepoint. Since this installation the drillthrough reports have stopped working. We get the following error when a hyperlink is cllicked:-
In the textbox properties we have set the 'Jump to Report' hyperlink option to use a condition. Based on the link clicked, one of two reports will be displayed. If i remove this condition and manually select the report to run then there are no problems. This however is not the functionality that is needed.
Any help will be appreciated.
In WSS mode, the report path is completely different. It must reference the report location in the WSS library wherethe report is deployed. See this blog for more details.Tuesday, February 14, 2012
Drill Down Reports Permissions Problem
Hi,
I've successfully setup the reports server 2005, on Windows Server 2003, and
had the permissions for the specific users configured to enable access to
the report. When a user logs into the report server, they can access the
reports I've created and deployed.
The problem arises when they try to drill down to the next level of the
report. I've created a report that is a matrix/crosstab report in the report
builder and the user can see the default (top level) of it without a problem.
However, as soon as they click on a record to drill down and get details on
it, they get an rsAccessDenied error that DOMAIN\User does not have
permissions.
As a test, I gave a specific user full content-manager rights (essentially I
tried everything short of giving them local admin rights to the box) and they
still can't run the drill down.
I'm wondering if they need report builder installed to generate the drill
down on the fly? We are using IWA on the domain to access the report server
itself. I thought I had the permissions set up fine, since they can run the
report at first, and I haven't found any options to allow/disallow drill
downs.
Any suggestions to try would be much appreciated. Thanks in advance for the
help.
-PeteHello Pete,
I would like to know this issue more clearly.
1. Did you mean you create the report in the report builder?
2. Have you provided proper permission on the Report Model you use in the
report builder?
3. Did this issue appeared on all the report you developed in report
builder?
4. Have you applied the latest services pack of SQL Server?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Wei,
Thanks for the quick response. I'll answer your questions in-lin below, and
I appreciate any help you can give.
1. Did you mean you create the report in the report builder?
Yes, I created the data source, and data model using the report manager, and
then created the report using report builder. I did all of this as the local
admin on the box on which reporting services was running, and had no
permissions problems (as I was in the default group on RS). The user was on
an external WinXP bx, without report builder loaded when trying to access the
report. They were manually added in as a Content Manager User on Report
Server, at the top level (which I believe gives them rights to all the lower
levels like th report and model.)
2. Have you provided proper permission on the Report Model you use in the
report builder?
Yes, I did. They are a Content Manager role user at the top level. They
can see the model, as well as the top level of the report.
3. Did this issue appeared on all the report you developed in report
builder?
It only appeared in Cross-tab (matrix) reports that I created. Standard
reports that do not have drill down capabilities ran fine.
4. Have you applied the latest services pack of SQL Server?
No, I haven't. I don't believe they have been approved by internal IT
support yet, but I can check if necessary. Would you recommend I install
just SP1, or the additional hot fixes that are currently available after SP1
as well?
Thank you for the help. Please let me know if you need more information,
and I'd be happy to provide it.
-Pete
"Wei Lu [MSFT]" wrote:
> Hello Pete,
> I would like to know this issue more clearly.
> 1. Did you mean you create the report in the report builder?
> 2. Have you provided proper permission on the Report Model you use in the
> report builder?
> 3. Did this issue appeared on all the report you developed in report
> builder?
> 4. Have you applied the latest services pack of SQL Server?
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello Pete,
Thanks for the update.
First, please apply the SP1 for SQL 2005 and let me know the result.
Second, I would like to know whether you have grant the proper permission
on the datasource for the user.
That means, did the user have permission to access the datasource?
If the datasource is the sql server, did they have permission to access it?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Wei,
I checked the user account, and he does have permissions to the data source
- he is able to edit or modify it, and generate a new model if he wanted to.
As I mentioned, the SP1 has not been approved yet for deployment internally.
Is there a knowledge base article that shows how this solves the problem
perhaps that I can reference to attempt to push the addition of it through?
If not, are there any other options or thoughts? Thanks for the help - I
appreciate it. Please let me know if you need more information.
-Pete
"Wei Lu [MSFT]" wrote:
> Hello Pete,
> Thanks for the update.
> First, please apply the SP1 for SQL 2005 and let me know the result.
> Second, I would like to know whether you have grant the proper permission
> on the datasource for the user.
> That means, did the user have permission to access the datasource?
> If the datasource is the sql server, did they have permission to access it?
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Pete,
There is no KB about this issue.
When I ask you to check the permission , I mean you need to check whether
the user could access the SQL Server, not whether he could modify the data
source file in the report manager.
Also, could you generate a sample report so that I may try to reproduce
this on my side?
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
=====================================================
PLEASE NOTE: The partner managed newsgroups are provided to assist with
break/fix
issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader: microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================.|||Wei,
Thanks for the information. The data source is created using credentials
stored on the report server, which can successfully access the database. We
have one user to access the DB, and don't impersonate the one making the
request.
As for a sample report, I'm not sure can provide that easily. Due to the
environment where this is deployed, we can't let out any data or reports. I
could potentially create a similar one, but that would be on a different
database (Northwind?) and reports server.
The steps to create one are pretty easy, since i am not using ANY advanced
configuration. It is built only with the web-based report builder, and
contains two tables, in a matrix, or cross-tab, report.
Thanks for the help - any other ideas you may have would be appreciated.
-Pete
"Wei Lu [MSFT]" wrote:
> Hello Pete,
> There is no KB about this issue.
> When I ask you to check the permission , I mean you need to check whether
> the user could access the SQL Server, not whether he could modify the data
> source file in the report manager.
> Also, could you generate a sample report so that I may try to reproduce
> this on my side?
> Sincerely yours,
> Wei Lu
> Microsoft Online Partner Support
> =====================================================> PLEASE NOTE: The partner managed newsgroups are provided to assist with
> break/fix
> issues and simple how to questions.
> We also love to hear your product feedback!
> Let us know what you think by posting
> - from the web interface: Partner Feedback
> - from your newsreader: microsoft.private.directaccess.partnerfeedback.
> We look forward to hearing from you!
> ======================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others
> may learn and benefit from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================.
>
>|||Hello Pete,
The reason why I need a sample report is that I need to know the model you
created and the report design.
So any simple report which could reproduce the issue may help me to
troubleshooting.
Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)
I've successfully setup the reports server 2005, on Windows Server 2003, and
had the permissions for the specific users configured to enable access to
the report. When a user logs into the report server, they can access the
reports I've created and deployed.
The problem arises when they try to drill down to the next level of the
report. I've created a report that is a matrix/crosstab report in the report
builder and the user can see the default (top level) of it without a problem.
However, as soon as they click on a record to drill down and get details on
it, they get an rsAccessDenied error that DOMAIN\User does not have
permissions.
As a test, I gave a specific user full content-manager rights (essentially I
tried everything short of giving them local admin rights to the box) and they
still can't run the drill down.
I'm wondering if they need report builder installed to generate the drill
down on the fly? We are using IWA on the domain to access the report server
itself. I thought I had the permissions set up fine, since they can run the
report at first, and I haven't found any options to allow/disallow drill
downs.
Any suggestions to try would be much appreciated. Thanks in advance for the
help.
-PeteHello Pete,
I would like to know this issue more clearly.
1. Did you mean you create the report in the report builder?
2. Have you provided proper permission on the Report Model you use in the
report builder?
3. Did this issue appeared on all the report you developed in report
builder?
4. Have you applied the latest services pack of SQL Server?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Wei,
Thanks for the quick response. I'll answer your questions in-lin below, and
I appreciate any help you can give.
1. Did you mean you create the report in the report builder?
Yes, I created the data source, and data model using the report manager, and
then created the report using report builder. I did all of this as the local
admin on the box on which reporting services was running, and had no
permissions problems (as I was in the default group on RS). The user was on
an external WinXP bx, without report builder loaded when trying to access the
report. They were manually added in as a Content Manager User on Report
Server, at the top level (which I believe gives them rights to all the lower
levels like th report and model.)
2. Have you provided proper permission on the Report Model you use in the
report builder?
Yes, I did. They are a Content Manager role user at the top level. They
can see the model, as well as the top level of the report.
3. Did this issue appeared on all the report you developed in report
builder?
It only appeared in Cross-tab (matrix) reports that I created. Standard
reports that do not have drill down capabilities ran fine.
4. Have you applied the latest services pack of SQL Server?
No, I haven't. I don't believe they have been approved by internal IT
support yet, but I can check if necessary. Would you recommend I install
just SP1, or the additional hot fixes that are currently available after SP1
as well?
Thank you for the help. Please let me know if you need more information,
and I'd be happy to provide it.
-Pete
"Wei Lu [MSFT]" wrote:
> Hello Pete,
> I would like to know this issue more clearly.
> 1. Did you mean you create the report in the report builder?
> 2. Have you provided proper permission on the Report Model you use in the
> report builder?
> 3. Did this issue appeared on all the report you developed in report
> builder?
> 4. Have you applied the latest services pack of SQL Server?
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello Pete,
Thanks for the update.
First, please apply the SP1 for SQL 2005 and let me know the result.
Second, I would like to know whether you have grant the proper permission
on the datasource for the user.
That means, did the user have permission to access the datasource?
If the datasource is the sql server, did they have permission to access it?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Wei,
I checked the user account, and he does have permissions to the data source
- he is able to edit or modify it, and generate a new model if he wanted to.
As I mentioned, the SP1 has not been approved yet for deployment internally.
Is there a knowledge base article that shows how this solves the problem
perhaps that I can reference to attempt to push the addition of it through?
If not, are there any other options or thoughts? Thanks for the help - I
appreciate it. Please let me know if you need more information.
-Pete
"Wei Lu [MSFT]" wrote:
> Hello Pete,
> Thanks for the update.
> First, please apply the SP1 for SQL 2005 and let me know the result.
> Second, I would like to know whether you have grant the proper permission
> on the datasource for the user.
> That means, did the user have permission to access the datasource?
> If the datasource is the sql server, did they have permission to access it?
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Pete,
There is no KB about this issue.
When I ask you to check the permission , I mean you need to check whether
the user could access the SQL Server, not whether he could modify the data
source file in the report manager.
Also, could you generate a sample report so that I may try to reproduce
this on my side?
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
=====================================================
PLEASE NOTE: The partner managed newsgroups are provided to assist with
break/fix
issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader: microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================.|||Wei,
Thanks for the information. The data source is created using credentials
stored on the report server, which can successfully access the database. We
have one user to access the DB, and don't impersonate the one making the
request.
As for a sample report, I'm not sure can provide that easily. Due to the
environment where this is deployed, we can't let out any data or reports. I
could potentially create a similar one, but that would be on a different
database (Northwind?) and reports server.
The steps to create one are pretty easy, since i am not using ANY advanced
configuration. It is built only with the web-based report builder, and
contains two tables, in a matrix, or cross-tab, report.
Thanks for the help - any other ideas you may have would be appreciated.
-Pete
"Wei Lu [MSFT]" wrote:
> Hello Pete,
> There is no KB about this issue.
> When I ask you to check the permission , I mean you need to check whether
> the user could access the SQL Server, not whether he could modify the data
> source file in the report manager.
> Also, could you generate a sample report so that I may try to reproduce
> this on my side?
> Sincerely yours,
> Wei Lu
> Microsoft Online Partner Support
> =====================================================> PLEASE NOTE: The partner managed newsgroups are provided to assist with
> break/fix
> issues and simple how to questions.
> We also love to hear your product feedback!
> Let us know what you think by posting
> - from the web interface: Partner Feedback
> - from your newsreader: microsoft.private.directaccess.partnerfeedback.
> We look forward to hearing from you!
> ======================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others
> may learn and benefit from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================.
>
>|||Hello Pete,
The reason why I need a sample report is that I need to know the model you
created and the report design.
So any simple report which could reproduce the issue may help me to
troubleshooting.
Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)
Subscribe to:
Posts (Atom)