Regarding Accessing on every objects within the sql 2005

Hi guys , can I know is that any way to set up some authentication for user access all the objects within the database after login successfully. For example, after access login into the database server and would like to click on certain database (eg : A) , then pop up the user access login page again. Thxcould you explain this a bit more, I did′n′t get your point from the explanation.

Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Sorry for the short explanation. Er... let me give a scenario. Once I open SQL management studio, there's a user login pop up window in order I able to access into my server. After login successfully,there are 3 database displays and stored in my servers ( Db A,B and C). When I want to click on the A database, then will pop up the user login window again for authorization. After that, if I want to choose a specific object (eg: Table 'Test') , then there's the use login window pop up again for authorization. At here, which means that authorization will show first before proceed any actions on any objects from the database in my server,.

So is there any possible to make such scenario? I'm just curious about it and would like to enhance my SQL security for my server and away from data thefting (For example, while away from the pc for a while and there's a staff come towards the pc for viewing and modifying the data). Thx for any assistance. Have a nice day.

From,

Hans

|||

Hi,

no SSMS does not have this functionality. once autenticated, permissions are checked upon access to the objects not prompting for any credentials again.

HTH, jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

You should lock your console when away from your machine.

Thanks
Laurentiu

|||Thx for the advise. Seems like SQL 2005 doesn't have ' Always prompt for username and password login ' feature which in SQL 2000 then.|||

There was no such feature in SQL Server 2000. Maybe you are referring to a client feature, in which case you should post your inquiry on the Tools forum.

Thanks
Laurentiu

refreshview removes user defined functions from sysdepends

When I create a procedure that references a user defined procedure it
appears in sysdepends, but disapears after being refreshed. Is this
expected and is there an alternative to sp_refreshview that correctly
refreshes sysdepends
--0--0--
print 'drop depenencies'
go
drop view depends_test
go
print 'create view'
go
create view depends_test as
select
dbo.ufJsmTranslate('test') As test_column
from
(select 1 one) test
go
print 'check depenencies'
go
sp_depends depends_test
go
print 'refresh'
go
sp_refreshview depends_test
go
print 'check depenencies'
go
sp_depends depends_test
--0--0--
drop depenencies
create view
check depenencies
In the current database, the specified object references the following:
name type updated selected column
-- -- -- -- --
dbo.ufJsmTranslate scalar function no no
refresh
check depenencies
Object does not reference any object, and no objects reference it.
--0--0--May be this is a bug, It seems that sp_refreshview does not update reference
s
to udfs. Here I have a script with the same problem. I also tried to create
the udf and view with the SCHEMABINDING property, everything was ok, except
that sp_refresh is giving me an error when I try to refresh the view.
-- same problem
use northwind
go
create table t(colA int)
go
create function dbo.ufn_funct1 (
@.i int
)
returns int
as
begin
return (@.i)
end
go
create view myview
as
select dbo.ufn_funct1(colA) as colA from t
go
exec sp_depends myview
go
exec sp_refreshview myview
go
exec sp_depends myview
go
drop view myview
go
drop function dbo.ufn_funct1
go
drop table t
go
-- Tried to fix it using schemabinfing
-- sp_refreshview is giving me an error
use northwind
go
create table t(colA int)
go
create function dbo.ufn_funct1 (
@.i int
)
returns int
with schemabinding
as
begin
return (@.i)
end
go
create view dbo.myview
with schemabinding
as
select dbo.ufn_funct1(colA) as colA from dbo.t
go
exec sp_depends 'dbo.myview'
go
exec sp_refreshview 'dbo.myview'
go
-- Server: Msg 208, Level 16, State 8, Procedure sp_refreshview, Line 1
-- Invalid object name 'dbo.myview'.
exec sp_depends 'dbo.myview'
go
drop view dbo.myview
go
drop function dbo.ufn_funct1
go
drop table t
go
AMB
"bilbo.baggins@.freesurf.ch" wrote:

> When I create a procedure that references a user defined procedure it
> appears in sysdepends, but disapears after being refreshed. Is this
> expected and is there an alternative to sp_refreshview that correctly
> refreshes sysdepends
> --0--0--
> print 'drop depenencies'
> go
> drop view depends_test
> go
> print 'create view'
> go
> create view depends_test as
> select
> dbo.ufJsmTranslate('test') As test_column
> from
> (select 1 one) test
> go
> print 'check depenencies'
> go
> sp_depends depends_test
> go
> print 'refresh'
> go
> sp_refreshview depends_test
> go
> print 'check depenencies'
> go
> sp_depends depends_test
> --0--0--
> drop depenencies
> create view
> check depenencies
> In the current database, the specified object references the following:
> name type updated selected column
> -- -- -- -- --
> dbo.ufJsmTranslate scalar function no no
> refresh
> check depenencies
> Object does not reference any object, and no objects reference it.
> --0--0--
>|||Correction,

> May be this is a bug, It seems that sp_refreshview does not update referen
ces
> to udfs. Here I have a script with the same problem. I also tried to creat
e
> the udf and view with the SCHEMABINDING property, everything was ok, excep
t
> that sp_refresh is giving me an error when I try to refresh the view.
..., except that sp_refreshview ...

> -- Server: Msg 208, Level 16, State 8, Procedure sp_refreshview, Line 1
> -- Invalid object name 'dbo.myview'.
> exec sp_depends 'dbo.myview'
> go
-- Server: Msg 208, Level 16, State 8, Procedure sp_refreshview, Line 1
-- Invalid object name 'dbo.myview'.
exec sp_refreshview 'dbo.myview'
go
AMB
"Alejandro Mesa" wrote:
> May be this is a bug, It seems that sp_refreshview does not update referen
ces
> to udfs. Here I have a script with the same problem. I also tried to creat
e
> the udf and view with the SCHEMABINDING property, everything was ok, excep
t
> that sp_refresh is giving me an error when I try to refresh the view.
> -- same problem
> use northwind
> go
> create table t(colA int)
> go
> create function dbo.ufn_funct1 (
> @.i int
> )
> returns int
> as
> begin
> return (@.i)
> end
> go
> create view myview
> as
> select dbo.ufn_funct1(colA) as colA from t
> go
> exec sp_depends myview
> go
> exec sp_refreshview myview
> go
> exec sp_depends myview
> go
> drop view myview
> go
> drop function dbo.ufn_funct1
> go
> drop table t
> go
> -- Tried to fix it using schemabinfing
> -- sp_refreshview is giving me an error
> use northwind
> go
> create table t(colA int)
> go
> create function dbo.ufn_funct1 (
> @.i int
> )
> returns int
> with schemabinding
> as
> begin
> return (@.i)
> end
> go
> create view dbo.myview
> with schemabinding
> as
> select dbo.ufn_funct1(colA) as colA from dbo.t
> go
> exec sp_depends 'dbo.myview'
> go
> exec sp_refreshview 'dbo.myview'
> go
> -- Server: Msg 208, Level 16, State 8, Procedure sp_refreshview, Line 1
> -- Invalid object name 'dbo.myview'.
> exec sp_depends 'dbo.myview'
> go
> drop view dbo.myview
> go
> drop function dbo.ufn_funct1
> go
> drop table t
> go
>
> AMB
>
> "bilbo.baggins@.freesurf.ch" wrote:
>

Refreshing a User Defined Type

Hi,
Using a User Define Type (a class written in C# for use in SQL 2005).
I have added my assembly and created a new Type and tested inserting and
selecting.
I now need to add a new field to my class.
How can I update the assembly and the type without disrupting the data
that is currently stored under the UDT.
Many Thanks in Advance
Stuart
*** Sent via Developersdex http://www.examnotes.net ***Stuart Ferguson <stuart_ferguson1@.btinternet.com> wrote in
news:exAtpR7OGHA.3856@.TK2MSFTNGP12.phx.gbl:

> Using a User Define Type (a class written in C# for use in SQL 2005).
> I have added my assembly and created a new Type and tested inserting
> and selecting.
> I now need to add a new field to my class.
> How can I update the assembly and the type without disrupting the data
> that is currently stored under the UDT.
>
Unless you have created the UDT with Format.UserDefined you can't. If
you have created it with UserDefined you need to, in your Read method,
cater for the fact that the new field may not be available. Then you run
ALTER ASSEMBLY and you use the WITH UNCHECKED DATA option.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||"Niels Berglund" <nielsb@.develop.com> wrote in message
news:Xns9777AABB8FC2Bnielsbdevelopcom@.20
7.46.248.16...
> Unless you have created the UDT with Format.UserDefined you can't. If
> you have created it with UserDefined you need to, in your Read method,
> cater for the fact that the new field may not be available. Then you run
> ALTER ASSEMBLY and you use the WITH UNCHECKED DATA option.
Doing this is not a good idea for a number of reasons. It will only work if
your UDT is IsFixedLength = false. Any DBCC CheckTable command on your UDT
will now fail. If your UDT is IsByteOrdered=True, you will end up with 2
udts that have the same value but with different byte representation,
potentially returning wrong results in queries that filter on your UDT.
Additional reasons this is a bad idea are left unspecified.
A better approach is to make a new column and copy your UDT data into it in
either binary or string format. Drop the type and create it again from your
new assembly and reparse the old udt data into the new UDT column. This may
seem like a lot more work, but it's the only way to do it right.
Steven|||"Steven Hemingray [MSFT]" <stevehem@.online.microsoft.com> wrote in
news:e$Y15v9OGHA.3164@.TK2MSFTNGP11.phx.gbl:

> Doing this is not a good idea for a number of reasons. It will only
> work if your UDT is IsFixedLength = false. Any DBCC CheckTable
> command on your UDT will now fail.
Well, I never said it was a good idea :-)

> If your UDT is IsByteOrdered=True,
> you will end up with 2 udts that have the same value but with
> different byte representation, potentially returning wrong results in
> queries that filter on your UDT.
Hmm, can you elaborate on that; if my read method caters for missing a
field, how can two udt's with the same value have different byte
representations?
[SNIP]
> A better approach is to make a new column and copy your UDT data into
> it in either binary or string format. Drop the type and create it
> again from your new assembly and reparse the old udt data into the new
> UDT column. This may seem like a lot more work, but it's the only way
> to do it right.
OK, so when you say re=parse the data, I assume you mean that the user
has to - before inserting the old data into the new column - either
update the old data so it contains some bogus value for the missing
field, or have the write method handle it?
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||Thanks for the replies guys.
However how would i handle the case where a field was removed from the
UDT, would i then have to copy all the fields individually when copying
the class to another column ?
Stuart
*** Sent via Developersdex http://www.examnotes.net ***|||Stuart Ferguson <stuart_ferguson1@.btinternet.com> wrote in
news:u1v2IoGPGHA.740@.TK2MSFTNGP12.phx.gbl:

> However how would i handle the case where a field was removed from the
> UDT, would i then have to copy all the fields individually when
> copying the class to another column ?
>
Well, as I wrote in my original reply, the easiest way is that you
handle it in your read and write methods. However, if you copy the data
to a separate table, then - in this scenario - you should copy it as the
string representation. Then you would need to update that data and take
away the removed field. Finally you insert the data back into the table
with the updated type.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||"Niels Berglund" <nielsb@.develop.com> wrote in message
news:Xns97786A60D5CDAnielsbdevelopcom@.20
7.46.248.16...
> "Steven Hemingray [MSFT]" <stevehem@.online.microsoft.com> wrote in
> news:e$Y15v9OGHA.3164@.TK2MSFTNGP11.phx.gbl:
> Hmm, can you elaborate on that; if my read method caters for missing a
> field, how can two udt's with the same value have different byte
> representations?
What if you run Alter Assembly and your read method doesn't get called
again? When queries refer to your IsByteOrdered udt, the udt isn't
instantiated so your read method isn't called.
--Assume Point is Format.UserDefined, IsByteOrdered=True,
IsFixedLength=False, and MaxByteSize leaves enough room for the alter
assembly expansion
CREATE TABLE DataPoints(c1 Point)
go
insert into DataPoints values(convert(Point, '(2,2)'))
go
--now alter the assembly that contains Point to add a new field to the type
ALTER ASSEMBLY Point_Assembly
go
--This won't return any rows because the bytes of the new udt Point for
(2,2) are different than the bytes for the old udt Point (2,2)
select * from DataPoints where c1 = convert(Point, '(2,2)')
In order for this to work, you'd need to write a stored-proc to go through
and 'refresh' your udt every place it is persisted. If you're going to do
this, you might as well do it the safe way and drop the type to ensure that
you catch all cases and don't hit any surprises.

> OK, so when you say re=parse the data, I assume you mean that the user
> has to - before inserting the old data into the new column - either
> update the old data so it contains some bogus value for the missing
> field, or have the write method handle it?
I think you mean the Read method, but that's correct. If the String
representation of the type doesn't change, then the user could go through
the Parse method instead without requiring additional work.
Obviously, changing the serialization of your UDT when it's already in use
is something to be avoided if at all possible.
Steven|||"Steven Hemingray [MSFT]" <stevehem@.online.microsoft.com> wrote in
news:OEBRH7IPGHA.2440@.TK2MSFTNGP11.phx.gbl:

> What if you run Alter Assembly and your read method doesn't get called
> again? When queries refer to your IsByteOrdered udt, the udt isn't
> instantiated so your read method isn't called.
> --Assume Point is Format.UserDefined, IsByteOrdered=True,
> IsFixedLength=False, and MaxByteSize leaves enough room for the alter
> assembly expansion
> CREATE TABLE DataPoints(c1 Point)
> go
> insert into DataPoints values(convert(Point, '(2,2)'))
> go
> --now alter the assembly that contains Point to add a new field to the
> type ALTER ASSEMBLY Point_Assembly
> go
> --This won't return any rows because the bytes of the new udt Point
> for (2,2) are different than the bytes for the old udt Point (2,2)
> select * from DataPoints where c1 = convert(Point, '(2,2)')
>
Hmm, I see your point (pun intended).
Quick question, if this is now the case, is there any reason for ALTER
ASSEMBLY ... WITH UNCHECKED DATA? I.e., when changing a type we should
always move the data to some other table and then re-populate.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||"Niels Berglund" <nielsb@.develop.com> wrote in message
news:Xns977952A6CDE45nielsbdevelopcom@.20
7.46.248.16...
> Quick question, if this is now the case, is there any reason for ALTER
> ASSEMBLY ... WITH UNCHECKED DATA? I.e., when changing a type we should
> always move the data to some other table and then re-populate.
Yep - Alter Assembly's primary use case is for fixing bugs in your code.
With UDTs, you may have a bug in one of the methods that you want to fix or
you may need to add a new method. With Format.Native UDTs, you can do this
without requiring WITH UNCHECKED DATA even when your type is persisted
because the server can verify that the serialization did not change.
(UNCHECKED DATA might still be required in cases where a UDT method is
persisted somewhere). With Format.UserDefined UDTs, you could change the
serialization and the server would never know it so WITH UNCHECKED DATA is
required even if you are not modifying the serialization.
Steven

Reformed access user needs advise for future

Hi
I have a vba/vb/access background but I have finally seen the light and have
decided to take up my next project with sql server backend (vb.net front).
So here is
my question;
What are the recommended db development guidelines to achieve both a) a good
user experience of being able to scroll to any record using record
navigation buttons and b) the db efficiency requirement of not loading too
many records in dataset at one time. If there is such a strategy to which
many agree then there should be a sample code app somewhere. It would help
me enormously to see the guts of an actual well written db app - no matter
how trivial as long as it covers the necessary detail - instead of advise
like don't do this or that without the coding specifics.
So here is a chance for the worthy to lead a recent convert (albeit
reluctant due to self deficiency on sql server side).
Thanks
Regards
For the database side, see http://www.aspfaq.com/2120
Unfortunately, I wrote the article long before .NET came about, so you won't
get any client app coding specifics, but it should still be helpful.
If you have the opportunity for additional learning curve, I recommend
learning C# as opposed to VB.Net...
"John" <John@.nospam.infovis.co.uk> wrote in message
news:eGUQVFeaIHA.4684@.TK2MSFTNGP06.phx.gbl...
> Hi
> I have a vba/vb/access background but I have finally seen the light and
> have decided to take up my next project with sql server backend (vb.net
> front). So here is
> my question;
> What are the recommended db development guidelines to achieve both a) a
> good
> user experience of being able to scroll to any record using record
> navigation buttons and b) the db efficiency requirement of not loading too
> many records in dataset at one time. If there is such a strategy to which
> many agree then there should be a sample code app somewhere. It would help
> me enormously to see the guts of an actual well written db app - no matter
> how trivial as long as it covers the necessary detail - instead of advise
> like don't do this or that without the coding specifics.
> So here is a chance for the worthy to lead a recent convert (albeit
> reluctant due to self deficiency on sql server side).
> Thanks
> Regards
>
>
|||As a person who used ASP\VBScript\VBA and had a comfort zone myself I agree
with Aaron. Adopting C# now is the smartest thing you can do now that you're
starting to see that the light can burn even brighter.
Give us a break because we're tired of explaining why over and over. Just
get out of that comfort zone and do it now while you're in transition and
trying to learn OOP.
As for your questions I can briefly say searching the web always works for
me when I have broad open ended questions...
"John" <John@.nospam.infovis.co.uk> wrote in message
news:eGUQVFeaIHA.4684@.TK2MSFTNGP06.phx.gbl...
> Hi
> I have a vba/vb/access background but I have finally seen the light and
> have decided to take up my next project with sql server backend (vb.net
> front). So here is
> my question;
> What are the recommended db development guidelines to achieve both a) a
> good
> user experience of being able to scroll to any record using record
> navigation buttons and b) the db efficiency requirement of not loading too
> many records in dataset at one time. If there is such a strategy to which
> many agree then there should be a sample code app somewhere. It would help
> me enormously to see the guts of an actual well written db app - no matter
> how trivial as long as it covers the necessary detail - instead of advise
> like don't do this or that without the coding specifics.
> So here is a chance for the worthy to lead a recent convert (albeit
> reluctant due to self deficiency on sql server side).
> Thanks
> Regards
>
>

Reformed access user needs advise for future

Hi
I have a vba/vb/access background but I have finally seen the light and have
decided to take up my next project with sql server backend (vb.net front).
So here is
my question;
What are the recommended db development guidelines to achieve both a) a good
user experience of being able to scroll to any record using record
navigation buttons and b) the db efficiency requirement of not loading too
many records in dataset at one time. If there is such a strategy to which
many agree then there should be a sample code app somewhere. It would help
me enormously to see the guts of an actual well written db app - no matter
how trivial as long as it covers the necessary detail - instead of advise
like don't do this or that without the coding specifics.
So here is a chance for the worthy to lead a recent convert (albeit
reluctant due to self deficiency on sql server side).
Thanks
RegardsFor the database side, see http://www.aspfaq.com/2120
Unfortunately, I wrote the article long before .NET came about, so you won't
get any client app coding specifics, but it should still be helpful.
If you have the opportunity for additional learning curve, I recommend
learning C# as opposed to VB.Net...
"John" <John@.nospam.infovis.co.uk> wrote in message
news:eGUQVFeaIHA.4684@.TK2MSFTNGP06.phx.gbl...
> Hi
> I have a vba/vb/access background but I have finally seen the light and
> have decided to take up my next project with sql server backend (vb.net
> front). So here is
> my question;
> What are the recommended db development guidelines to achieve both a) a
> good
> user experience of being able to scroll to any record using record
> navigation buttons and b) the db efficiency requirement of not loading too
> many records in dataset at one time. If there is such a strategy to which
> many agree then there should be a sample code app somewhere. It would help
> me enormously to see the guts of an actual well written db app - no matter
> how trivial as long as it covers the necessary detail - instead of advise
> like don't do this or that without the coding specifics.
> So here is a chance for the worthy to lead a recent convert (albeit
> reluctant due to self deficiency on sql server side).
> Thanks
> Regards
>
>|||As a person who used ASP\VBScript\VBA and had a comfort zone myself I agree
with Aaron. Adopting C# now is the smartest thing you can do now that you're
starting to see that the light can burn even brighter.
Give us a break because we're tired of explaining why over and over. Just
get out of that comfort zone and do it now while you're in transition and
trying to learn OOP.
As for your questions I can briefly say searching the web always works for
me when I have broad open ended questions...
"John" <John@.nospam.infovis.co.uk> wrote in message
news:eGUQVFeaIHA.4684@.TK2MSFTNGP06.phx.gbl...
> Hi
> I have a vba/vb/access background but I have finally seen the light and
> have decided to take up my next project with sql server backend (vb.net
> front). So here is
> my question;
> What are the recommended db development guidelines to achieve both a) a
> good
> user experience of being able to scroll to any record using record
> navigation buttons and b) the db efficiency requirement of not loading too
> many records in dataset at one time. If there is such a strategy to which
> many agree then there should be a sample code app somewhere. It would help
> me enormously to see the guts of an actual well written db app - no matter
> how trivial as long as it covers the necessary detail - instead of advise
> like don't do this or that without the coding specifics.
> So here is a chance for the worthy to lead a recent convert (albeit
> reluctant due to self deficiency on sql server side).
> Thanks
> Regards
>
>

Refering Local DB Tables from Master Procedures

Hi,
I am facing a problem while reading user database tables from a procedure in
the master database.
Example.
create procedure sp_Test
as
select count(*) from syscolumns
select count(*) from UserTable
go
I granted public access to this procedure in the master database.
When I execute the procedure in Query Analyzer with master DB, it is pulling
the record counts from the master tables.
When I execute it for some other user database, it returns the count of
records from syscolumn tables of that local database, but still takes the
count from UserTable of master database. It does not take the count from the
local database.
Could anyone help me know, if there is any setting, to point to the local
database instead of master while refering user tables (for system tables it
takes from local databases). Version : SQL Server 2000
Thanks and Regards,
Prasanth
HI
USE master
GO
ALTER procedure sp_Test @.dbname as sysname, @.UserTable as sysname
as
DECLARE @.strSQL as nvarchar(100)
set @.strSQL = 'select count(*) from ' + @.UserTable
select count(*) from syscolumns
EXECUTE sp_executesql @.strSQL
GO
USE pubs
GO
sp_test 'pubs', 'employee'
GO
Andras Jakus MCDBA
"Prasanth" wrote:

> Hi,
> I am facing a problem while reading user database tables from a procedure in
> the master database.
> Example.
> create procedure sp_Test
> as
> select count(*) from syscolumns
> select count(*) from UserTable
> go
> I granted public access to this procedure in the master database.
> When I execute the procedure in Query Analyzer with master DB, it is pulling
> the record counts from the master tables.
> When I execute it for some other user database, it returns the count of
> records from syscolumn tables of that local database, but still takes the
> count from UserTable of master database. It does not take the count from the
> local database.
> Could anyone help me know, if there is any setting, to point to the local
> database instead of master while refering user tables (for system tables it
> takes from local databases). Version : SQL Server 2000
> --
> Thanks and Regards,
> Prasanth
|||Thanks for the reply Andras,
Currently I am using the procedure as you have given.
Is there any way I can directly query user tables just like system tables,
without passing the DB name and table names as parameters?
"Andras Jakus" wrote:
[vbcol=seagreen]
> HI
> USE master
> GO
> ALTER procedure sp_Test @.dbname as sysname, @.UserTable as sysname
> as
> DECLARE @.strSQL as nvarchar(100)
> set @.strSQL = 'select count(*) from ' + @.UserTable
> select count(*) from syscolumns
> EXECUTE sp_executesql @.strSQL
> GO
> USE pubs
> GO
> sp_test 'pubs', 'employee'
> GO
> Andras Jakus MCDBA
> "Prasanth" wrote:
|||HI
Try this, but without parameter you can use with onli one table name.
(The db name parameter in first procedure unnecessary)
ALTER procedure sp_Test
as
DECLARE @.strSQL as nvarchar(100)
set @.strSQL = 'select count(*) from dbo.employee'
select count(*) from syscolumns
EXECUTE sp_executesql @.strSQL
GO
Andras Jakus MCDBA
"Prasanth" wrote:
[vbcol=seagreen]
> Thanks for the reply Andras,
> Currently I am using the procedure as you have given.
> Is there any way I can directly query user tables just like system tables,
> without passing the DB name and table names as parameters?
> "Andras Jakus" wrote:

Refering Local DB Tables from Master Procedures

Hi,
I am facing a problem while reading user database tables from a procedure in
the master database.
Example.
create procedure sp_Test
as
select count(*) from syscolumns
select count(*) from UserTable
go
I granted public access to this procedure in the master database.
When I execute the procedure in Query Analyzer with master DB, it is pulling
the record counts from the master tables.
When I execute it for some other user database, it returns the count of
records from syscolumn tables of that local database, but still takes the
count from UserTable of master database. It does not take the count from the
local database.
Could anyone help me know, if there is any setting, to point to the local
database instead of master while refering user tables (for system tables it
takes from local databases). Version : SQL Server 2000
--
Thanks and Regards,
PrasanthHI
USE master
GO
ALTER procedure sp_Test @.dbname as sysname, @.UserTable as sysname
as
DECLARE @.strSQL as nvarchar(100)
set @.strSQL = 'select count(*) from ' + @.UserTable
select count(*) from syscolumns
EXECUTE sp_executesql @.strSQL
GO
USE pubs
GO
sp_test 'pubs', 'employee'
GO
Andras Jakus MCDBA
"Prasanth" wrote:
> Hi,
> I am facing a problem while reading user database tables from a procedure in
> the master database.
> Example.
> create procedure sp_Test
> as
> select count(*) from syscolumns
> select count(*) from UserTable
> go
> I granted public access to this procedure in the master database.
> When I execute the procedure in Query Analyzer with master DB, it is pulling
> the record counts from the master tables.
> When I execute it for some other user database, it returns the count of
> records from syscolumn tables of that local database, but still takes the
> count from UserTable of master database. It does not take the count from the
> local database.
> Could anyone help me know, if there is any setting, to point to the local
> database instead of master while refering user tables (for system tables it
> takes from local databases). Version : SQL Server 2000
> --
> Thanks and Regards,
> Prasanth|||Thanks for the reply Andras,
Currently I am using the procedure as you have given.
Is there any way I can directly query user tables just like system tables,
without passing the DB name and table names as parameters?
"Andras Jakus" wrote:
> HI
> USE master
> GO
> ALTER procedure sp_Test @.dbname as sysname, @.UserTable as sysname
> as
> DECLARE @.strSQL as nvarchar(100)
> set @.strSQL = 'select count(*) from ' + @.UserTable
> select count(*) from syscolumns
> EXECUTE sp_executesql @.strSQL
> GO
> USE pubs
> GO
> sp_test 'pubs', 'employee'
> GO
> Andras Jakus MCDBA
> "Prasanth" wrote:
> > Hi,
> > I am facing a problem while reading user database tables from a procedure in
> > the master database.
> > Example.
> > create procedure sp_Test
> > as
> > select count(*) from syscolumns
> > select count(*) from UserTable
> > go
> >
> > I granted public access to this procedure in the master database.
> > When I execute the procedure in Query Analyzer with master DB, it is pulling
> > the record counts from the master tables.
> > When I execute it for some other user database, it returns the count of
> > records from syscolumn tables of that local database, but still takes the
> > count from UserTable of master database. It does not take the count from the
> > local database.
> >
> > Could anyone help me know, if there is any setting, to point to the local
> > database instead of master while refering user tables (for system tables it
> > takes from local databases). Version : SQL Server 2000
> > --
> > Thanks and Regards,
> > Prasanth|||HI
Try this, but without parameter you can use with onli one table name.
(The db name parameter in first procedure unnecessary)
ALTER procedure sp_Test
as
DECLARE @.strSQL as nvarchar(100)
set @.strSQL = 'select count(*) from dbo.employee'
select count(*) from syscolumns
EXECUTE sp_executesql @.strSQL
GO
Andras Jakus MCDBA
"Prasanth" wrote:
> Thanks for the reply Andras,
> Currently I am using the procedure as you have given.
> Is there any way I can directly query user tables just like system tables,
> without passing the DB name and table names as parameters?
> "Andras Jakus" wrote:
> > HI
> > USE master
> > GO
> > ALTER procedure sp_Test @.dbname as sysname, @.UserTable as sysname
> > as
> >
> > DECLARE @.strSQL as nvarchar(100)
> >
> > set @.strSQL = 'select count(*) from ' + @.UserTable
> >
> > select count(*) from syscolumns
> > EXECUTE sp_executesql @.strSQL
> >
> > GO
> >
> > USE pubs
> > GO
> > sp_test 'pubs', 'employee'
> > GO
> >
> > Andras Jakus MCDBA
> >
> > "Prasanth" wrote:
> >
> > > Hi,
> > > I am facing a problem while reading user database tables from a procedure in
> > > the master database.
> > > Example.
> > > create procedure sp_Test
> > > as
> > > select count(*) from syscolumns
> > > select count(*) from UserTable
> > > go
> > >
> > > I granted public access to this procedure in the master database.
> > > When I execute the procedure in Query Analyzer with master DB, it is pulling
> > > the record counts from the master tables.
> > > When I execute it for some other user database, it returns the count of
> > > records from syscolumn tables of that local database, but still takes the
> > > count from UserTable of master database. It does not take the count from the
> > > local database.
> > >
> > > Could anyone help me know, if there is any setting, to point to the local
> > > database instead of master while refering user tables (for system tables it
> > > takes from local databases). Version : SQL Server 2000
> > > --
> > > Thanks and Regards,
> > > Prasanthsql

referencing fields in code

I'd like the user to decide how to group the report. For this I have the
parameter KStar and a piece of custom code.
Under 'Edit group' I use the expression:
=Code.GetGroups(Parameters!KStar.Value )
In the code window I have a function that starts like this:
Public Shared Function GetGroups (Byref KStar As integer)
Select (KStar)
Case 1
Return(Fields!K1.Value)
Case 2
Return(Fields!K2.Value)
It gives me the error:
[BC30469] Reference to a non-shared member requires an object reference.
How do I solve this? I am obviously very new to Reporting Services and I
would appreciate any help.
Thanks!First try replacing Fields!KStar.Value by Report.Fields!KStar.Value, if that
does not work you will have to pass the field values in as a parameter
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"maple" <maple@.discussions.microsoft.com> wrote in message
news:E87A2353-646F-4FA9-AA2D-367BCB91348D@.microsoft.com...
> I'd like the user to decide how to group the report. For this I have the
> parameter KStar and a piece of custom code.
> Under 'Edit group' I use the expression:
> =Code.GetGroups(Parameters!KStar.Value )
> In the code window I have a function that starts like this:
> Public Shared Function GetGroups (Byref KStar As integer)
> Select (KStar)
> Case 1
> Return(Fields!K1.Value)
> Case 2
> Return(Fields!K2.Value)
> It gives me the error:
> [BC30469] Reference to a non-shared member requires an object reference.
> How do I solve this? I am obviously very new to Reporting Services and I
> would appreciate any help.
> Thanks!
>

Referencial Integrity Constraints Questions ?

Hi, Group
Please let me know how can I do the following:
Will prevent the user from deleting an entry if the value is used in a
foreign key.
Best regard
MarioMario,
If you have Foreign key setup, you should not be able to delete a parent
before deleting the child. Below is an example showing FK in effect.
e.g.
create table t1(i int primary key, j int)
create table t2(k int primary key,i int foreign key references t1(i),l int)
go
insert t1 values(1,1)
insert t2 values(2,1,2)
go
--this delete will fail
--due to FK constraint
delete t1
where i=1
go
drop table t2,t1
go
-oj
http://www.rac4sql.net
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:%23uVCIbV2DHA.1736@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Hi, Group
> Please let me know how can I do the following:
> Will prevent the user from deleting an entry if the value is used in a
> foreign key.
> Best regard
> Mario
>

---

|||OJ describes the default behavour of FK constraints, which seems to be the
behavour you want... You may add the cascade option to the constraint, which
will cause the child records to be automatically deleted when the parent row
is deleted. (Just an FYI)
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:%23uVCIbV2DHA.1736@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Hi, Group
> Please let me know how can I do the following:
> Will prevent the user from deleting an entry if the value is used in a
> foreign key.
> Best regard
> Mario
>

---

|||OK my Friends but what can i do then and thanks so much for yours response.
Best Regard
Mario
"Mario Reiley" <mreiley@.cantv.net> escribi en el mensaje
news:%23uVCIbV2DHA.1736@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Hi, Group
> Please let me know how can I do the following:
> Will prevent the user from deleting an entry if the value is used in a
> foreign key.
> Best regard
> Mario
>

---

|||OK, let me try explaining.
I have two tables:
Personal (Is my Parent table)
Profession ( Is my Child table)
In Parent is: ProfessionId field.
The thing is how Can I void Delete a row in Profession table when there is a
value in Personal table.
Example:
IF the user not put the profession in the Personal record then no problem
the profession row Is OK (Delete) else the row profession can't be deleting.
Note: Sorry my English.
Best regard
MArio
"Mario Reiley" <mreiley@.cantv.net> escribi en el mensaje
news:%23uVCIbV2DHA.1736@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Hi, Group
> Please let me know how can I do the following:
> Will prevent the user from deleting an entry if the value is used in a
> foreign key.
> Best regard
> Mario
>

---

|||Here's how to add a the relationship.
ALTER TABLE Personal
ADD FOREIGN KEY (ProfessionId)
REFERENCES Profession (ProfessionId)
ON DELETE NO ACTION
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:%23gTfxJd2DHA.2620@.TK2MSFTNGP09.phx.gbl...

quote:

---

> OK, let me try explaining.
>
> I have two tables:
>
> Personal (Is my Parent table)
> Profession ( Is my Child table)
>
> In Parent is: ProfessionId field.
>
> The thing is how Can I void Delete a row in Profession table when there is

---

a

quote:

---

> value in Personal table.
>
> Example:
>
> IF the user not put the profession in the Personal record then no problem
> the profession row Is OK (Delete) else the row profession can't be

---

deleting.

quote:

---

>
> Note: Sorry my English.
>
> Best regard
> MArio
> "Mario Reiley" <mreiley@.cantv.net> escribi en el mensaje
> news:%23uVCIbV2DHA.1736@.TK2MSFTNGP09.phx.gbl...
>

---

Referencial Integrity Constraints Questions ?

Hi, Group
Please let me know how can I do the following:
Will prevent the user from deleting an entry if the value is used in a
foreign key.
Best regard
MarioMario,
If you have Foreign key setup, you should not be able to delete a parent
before deleting the child. Below is an example showing FK in effect.
e.g.
create table t1(i int primary key, j int)
create table t2(k int primary key,i int foreign key references t1(i),l int)
go
insert t1 values(1,1)
insert t2 values(2,1,2)
go
--this delete will fail
--due to FK constraint
delete t1
where i=1
go
drop table t2,t1
go
-oj
http://www.rac4sql.net
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:%23uVCIbV2DHA.1736@.TK2MSFTNGP09.phx.gbl...
> Hi, Group
> Please let me know how can I do the following:
> Will prevent the user from deleting an entry if the value is used in a
> foreign key.
> Best regard
> Mario
>|||OJ describes the default behavour of FK constraints, which seems to be the
behavour you want... You may add the cascade option to the constraint, which
will cause the child records to be automatically deleted when the parent row
is deleted. (Just an FYI)
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:%23uVCIbV2DHA.1736@.TK2MSFTNGP09.phx.gbl...
> Hi, Group
> Please let me know how can I do the following:
> Will prevent the user from deleting an entry if the value is used in a
> foreign key.
> Best regard
> Mario
>|||OK my Friends but what can i do then and thanks so much for yours response.
Best Regard
Mario
"Mario Reiley" <mreiley@.cantv.net> escribió en el mensaje
news:%23uVCIbV2DHA.1736@.TK2MSFTNGP09.phx.gbl...
> Hi, Group
> Please let me know how can I do the following:
> Will prevent the user from deleting an entry if the value is used in a
> foreign key.
> Best regard
> Mario
>|||OK, let me try explaining.
I have two tables:
Personal (Is my Parent table)
Profession ( Is my Child table)
In Parent is: ProfessionId field.
The thing is how Can I void Delete a row in Profession table when there is a
value in Personal table.
Example:
IF the user not put the profession in the Personal record then no problem
the profession row Is OK (Delete) else the row profession can't be deleting.
Note: Sorry my English.
Best regard
MArio
"Mario Reiley" <mreiley@.cantv.net> escribió en el mensaje
news:%23uVCIbV2DHA.1736@.TK2MSFTNGP09.phx.gbl...
> Hi, Group
> Please let me know how can I do the following:
> Will prevent the user from deleting an entry if the value is used in a
> foreign key.
> Best regard
> Mario
>|||Here's how to add a the relationship.
ALTER TABLE Personal
ADD FOREIGN KEY (ProfessionId)
REFERENCES Profession (ProfessionId)
ON DELETE NO ACTION
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:%23gTfxJd2DHA.2620@.TK2MSFTNGP09.phx.gbl...
> OK, let me try explaining.
>
> I have two tables:
>
> Personal (Is my Parent table)
> Profession ( Is my Child table)
>
> In Parent is: ProfessionId field.
>
> The thing is how Can I void Delete a row in Profession table when there is
a
> value in Personal table.
>
> Example:
>
> IF the user not put the profession in the Personal record then no problem
> the profession row Is OK (Delete) else the row profession can't be
deleting.
>
> Note: Sorry my English.
>
> Best regard
> MArio
> "Mario Reiley" <mreiley@.cantv.net> escribió en el mensaje
> news:%23uVCIbV2DHA.1736@.TK2MSFTNGP09.phx.gbl...
> > Hi, Group
> >
> > Please let me know how can I do the following:
> >
> > Will prevent the user from deleting an entry if the value is used in a
> > foreign key.
> >
> > Best regard
> > Mario
> >
> >
>

reference member in user hierarchy

hi,

I have a user hierarchy in my Date Dimension such as [Calendar]->[Years]->[Quarters]

And I can access the member by directly call the memeber name, for example:

select [Date].[Calendar].[Years].[2006].[Q2] on columns, ...................................

The results will be correct. However, if I use reference to call the member, for example:

select [Date].[Calendar].[Years].[2006].&[2] on columns, ...................................

There will be nothing in my results.

Would you please tell me what shall I do to enable the reference call to my hierarchy member? Thanks.

With "select [Date].[Calendar].[Years].[2006].&[2] on columns, you are referencing the keys for the member.

"[Date].[Calendar].[Years].&[2006].&[2]" might help.

Another method is [Date].[Calendar].[Years].Lastchild.Lastchild. With this method you will always find the last quarter of the last year in your time dimension.

Regards

Thomas Ivarsson

|||

Hi, Thomas

Thanks a lot for your answer. I have also tried "[Date].[Calendar].[Years].&[2006].&[2]" but no use. I still get nothing from that.

I was forced to use the format: "[Date].[Calendar].[Years].&[2006].&[2]" because this is a fixed format in a third-part software. I guess maybe there is sth wrong about the settings of my user hierarchy?

|||

It can be a problem with key for the Quarter member. Are you using integers like 1,2,3,4 for Quarters? In that case you can make a combination by year and quarter for the quarter key. Check also your attribute relation between quarter and year in the dimension editor.

Regards

Thomas

|||

Hi, Thomas,

Thanks and would you please give me a little bit more details?

Do you mean that I should use 1,2,3,4 as the quarter key for Quarters?

And do you mean that I should build the attribute relation between quarter and year? I have tried both with/without attribute relation but it did not work. Thx.

|||

No. You should use a composite key with both the year and the quarter. A quarter key like 1,2,3,4 is not unique. If you klick on the key-column, for the level, in the properties(dimension editor) you can add year to quarter(in the key) and create a collection. This is in the dataitem collection editor.

Regards

Thomas Ivarsson

|||

hi, Thomas,

Thanks and I have tried to add a composite key (year+quarter) according to your steps.

But during processing, there is an error message: The key columns of the Quarter measure group attribute do not match in either number or data types to the key columns of the source attribute.

Would you please tell me how to deal with this problem? Thanks a lot.

|||

Hello. Make a new column, for either year or quarter, and use the TSQL convert och cast-function to change the data type to something common.

You can also simply use the datepart function for year and quarter and return two integer fields.

Regards

Thomas Ivarsson

|||

hi, Thomas

I have checked that in the AS2005, my Year and Quarter are in the format: Integar.

And in the data source view, I see that the source columns of the key column are both: System.Int32

However, the error still exists, do you have any idea about this? Thanks.

|||

BTW, do I also need to change the Name Column (or the value column) to the integer column I defined in the source?

|||

hi, Thomas,

I have finally get the result I want ([Date].[Calendar].&[2005].&[2] is available).

And the step follows: 1. Remove any attribute relationship in dimension date. 2. Build a new integer column (1,2,3,4) as the source of the key column for Quarter. 3. Specify the key column of Quarter to that new integer column.

I do not know if this will cause any other problems. Anyway, thanks a lot for your help.

|||

I realized this now. So you have a join on one of the levels in your time hierarchy above the leaf level? In the dimension usage tab for the cube you must use the same combination as in your composite key. You do not have to change the name column.

Check the Adventure Works Demo project that is part of the installation. Have a look at the date.dim there(and the attribute month_name(properties)) and check the relation between the date dim and sales targets measure group in the Adventure Works cube.

Regards

Thomas Ivarsson

|||

Nice that it works. Without attribute relations you will not get any aggregations on the time dimension. Another problem is that your calculations can be wrong. What you needed to do was to add 2006(Year) to the quarters(1,2,3,4) so that their collection will point to year and quarter, at the same time. Actually you combine each year with each of their quarter in this way. Quarter(1,2,3,4) will not point to a specific year if you do not do this(ie add the year key to each quarter key).

Regards

Thomas Ivarsson

|||

Hi, Thomas

Thanks a lot for your response! I have several questions about your answer.

First, what do you mean by have a join on one of the levles above the leaf levels? And how can I see that?

Second, I have seen the AdventureWorks cube and see the attribute month_name has a combination key column of year + month. But in the dim usage table, I do not know how to modify my own project. I can see a RED line under my date dimension and I think that indicates error. However, if I add an attribute relationship between Quarter and Year, the dim usage table will automaticlly add this relationship to itself. (In other words, I can not find the place to add sth about the combination key in the dim usage table)

|||

Hello again Jeremy. If you scroll out to the right, in the dimension usage tab of the cube editor(In the Adventure Works demo cube), you will find a box where the dimension date intersect with the measure group Sales Targets. All the other relations between date and the measure groups in this cube are at the lowest(leaf level) or the box empty(no relation). Click the box(date dim and Sales Targets) There you can see the granularity attribute for the relation, thats the level that is used in the join between the measure group and the dimension.

Check here that you join on the correct column to avoid the error message you talked about before: "The key columns of the Quarter measure group attribute do not match in either number or data types to the key columns of the source attribute." You will only have to this if you have changed the key column for the Quarter member in your time dimension.

Regards

Thomas Ivarsson

reference member in user hierarchy

hi,

I have a user hierarchy in my Date Dimension such as [Calendar]->[Years]->[Quarters]

And I can access the member by directly call the memeber name, for example:

select [Date].[Calendar].[Years].[2006].[Q2] on columns, ...................................

The results will be correct. However, if I use reference to call the member, for example:

select [Date].[Calendar].[Years].[2006].&[2] on columns, ...................................

There will be nothing in my results.

Would you please tell me what shall I do to enable the reference call to my hierarchy member? Thanks.

With "select [Date].[Calendar].[Years].[2006].&[2] on columns, you are referencing the keys for the member.

"[Date].[Calendar].[Years].&[2006].&[2]" might help.

Another method is [Date].[Calendar].[Years].Lastchild.Lastchild. With this method you will always find the last quarter of the last year in your time dimension.

Regards

Thomas Ivarsson

|||

Hi, Thomas

Thanks a lot for your answer. I have also tried "[Date].[Calendar].[Years].&[2006].&[2]" but no use. I still get nothing from that.

I was forced to use the format: "[Date].[Calendar].[Years].&[2006].&[2]" because this is a fixed format in a third-part software. I guess maybe there is sth wrong about the settings of my user hierarchy?

|||

It can be a problem with key for the Quarter member. Are you using integers like 1,2,3,4 for Quarters? In that case you can make a combination by year and quarter for the quarter key. Check also your attribute relation between quarter and year in the dimension editor.

Regards

Thomas

|||

Hi, Thomas,

Thanks and would you please give me a little bit more details?

Do you mean that I should use 1,2,3,4 as the quarter key for Quarters?

And do you mean that I should build the attribute relation between quarter and year? I have tried both with/without attribute relation but it did not work. Thx.

|||

No. You should use a composite key with both the year and the quarter. A quarter key like 1,2,3,4 is not unique. If you klick on the key-column, for the level, in the properties(dimension editor) you can add year to quarter(in the key) and create a collection. This is in the dataitem collection editor.

Regards

Thomas Ivarsson

|||

hi, Thomas,

Thanks and I have tried to add a composite key (year+quarter) according to your steps.

But during processing, there is an error message: The key columns of the Quarter measure group attribute do not match in either number or data types to the key columns of the source attribute.

Would you please tell me how to deal with this problem? Thanks a lot.

|||

Hello. Make a new column, for either year or quarter, and use the TSQL convert och cast-function to change the data type to something common.

You can also simply use the datepart function for year and quarter and return two integer fields.

Regards

Thomas Ivarsson

|||

hi, Thomas

I have checked that in the AS2005, my Year and Quarter are in the format: Integar.

And in the data source view, I see that the source columns of the key column are both: System.Int32

However, the error still exists, do you have any idea about this? Thanks.

|||

BTW, do I also need to change the Name Column (or the value column) to the integer column I defined in the source?

|||

hi, Thomas,

I have finally get the result I want ([Date].[Calendar].&[2005].&[2] is available).

And the step follows: 1. Remove any attribute relationship in dimension date. 2. Build a new integer column (1,2,3,4) as the source of the key column for Quarter. 3. Specify the key column of Quarter to that new integer column.

I do not know if this will cause any other problems. Anyway, thanks a lot for your help.

|||

I realized this now. So you have a join on one of the levels in your time hierarchy above the leaf level? In the dimension usage tab for the cube you must use the same combination as in your composite key. You do not have to change the name column.

Check the Adventure Works Demo project that is part of the installation. Have a look at the date.dim there(and the attribute month_name(properties)) and check the relation between the date dim and sales targets measure group in the Adventure Works cube.

Regards

Thomas Ivarsson

|||

Nice that it works. Without attribute relations you will not get any aggregations on the time dimension. Another problem is that your calculations can be wrong. What you needed to do was to add 2006(Year) to the quarters(1,2,3,4) so that their collection will point to year and quarter, at the same time. Actually you combine each year with each of their quarter in this way. Quarter(1,2,3,4) will not point to a specific year if you do not do this(ie add the year key to each quarter key).

Regards

Thomas Ivarsson

|||

Hi, Thomas

Thanks a lot for your response! I have several questions about your answer.

First, what do you mean by have a join on one of the levles above the leaf levels? And how can I see that?

Second, I have seen the AdventureWorks cube and see the attribute month_name has a combination key column of year + month. But in the dim usage table, I do not know how to modify my own project. I can see a RED line under my date dimension and I think that indicates error. However, if I add an attribute relationship between Quarter and Year, the dim usage table will automaticlly add this relationship to itself. (In other words, I can not find the place to add sth about the combination key in the dim usage table)

|||

Hello again Jeremy. If you scroll out to the right, in the dimension usage tab of the cube editor(In the Adventure Works demo cube), you will find a box where the dimension date intersect with the measure group Sales Targets. All the other relations between date and the measure groups in this cube are at the lowest(leaf level) or the box empty(no relation). Click the box(date dim and Sales Targets) There you can see the granularity attribute for the relation, thats the level that is used in the join between the measure group and the dimension.

Check here that you join on the correct column to avoid the error message you talked about before: "The key columns of the Quarter measure group attribute do not match in either number or data types to the key columns of the source attribute." You will only have to this if you have changed the key column for the Quarter member in your time dimension.

Regards

Thomas Ivarsson

reference member in user hierarchy

hi,

I have a user hierarchy in my Date Dimension such as [Calendar]->[Years]->[Quarters]

And I can access the member by directly call the memeber name, for example:

select [Date].[Calendar].[Years].[2006].[Q2] on columns, ...................................

The results will be correct. However, if I use reference to call the member, for example:

select [Date].[Calendar].[Years].[2006].&[2] on columns, ...................................

There will be nothing in my results.

Would you please tell me what shall I do to enable the reference call to my hierarchy member? Thanks.

With "select [Date].[Calendar].[Years].[2006].&[2] on columns, you are referencing the keys for the member.

"[Date].[Calendar].[Years].&[2006].&[2]" might help.

Another method is [Date].[Calendar].[Years].Lastchild.Lastchild. With this method you will always find the last quarter of the last year in your time dimension.

Regards

Thomas Ivarsson

|||

Hi, Thomas

Thanks a lot for your answer. I have also tried "[Date].[Calendar].[Years].&[2006].&[2]" but no use. I still get nothing from that.

I was forced to use the format: "[Date].[Calendar].[Years].&[2006].&[2]" because this is a fixed format in a third-part software. I guess maybe there is sth wrong about the settings of my user hierarchy?

|||

It can be a problem with key for the Quarter member. Are you using integers like 1,2,3,4 for Quarters? In that case you can make a combination by year and quarter for the quarter key. Check also your attribute relation between quarter and year in the dimension editor.

Regards

Thomas

|||

Hi, Thomas,

Thanks and would you please give me a little bit more details?

Do you mean that I should use 1,2,3,4 as the quarter key for Quarters?

And do you mean that I should build the attribute relation between quarter and year? I have tried both with/without attribute relation but it did not work. Thx.

|||

No. You should use a composite key with both the year and the quarter. A quarter key like 1,2,3,4 is not unique. If you klick on the key-column, for the level, in the properties(dimension editor) you can add year to quarter(in the key) and create a collection. This is in the dataitem collection editor.

Regards

Thomas Ivarsson

|||

hi, Thomas,

Thanks and I have tried to add a composite key (year+quarter) according to your steps.

But during processing, there is an error message: The key columns of the Quarter measure group attribute do not match in either number or data types to the key columns of the source attribute.

Would you please tell me how to deal with this problem? Thanks a lot.

|||

Hello. Make a new column, for either year or quarter, and use the TSQL convert och cast-function to change the data type to something common.

You can also simply use the datepart function for year and quarter and return two integer fields.

Regards

Thomas Ivarsson

|||

hi, Thomas

I have checked that in the AS2005, my Year and Quarter are in the format: Integar.

And in the data source view, I see that the source columns of the key column are both: System.Int32

However, the error still exists, do you have any idea about this? Thanks.

|||

BTW, do I also need to change the Name Column (or the value column) to the integer column I defined in the source?

|||

hi, Thomas,

I have finally get the result I want ([Date].[Calendar].&[2005].&[2] is available).

And the step follows: 1. Remove any attribute relationship in dimension date. 2. Build a new integer column (1,2,3,4) as the source of the key column for Quarter. 3. Specify the key column of Quarter to that new integer column.

I do not know if this will cause any other problems. Anyway, thanks a lot for your help.

|||

I realized this now. So you have a join on one of the levels in your time hierarchy above the leaf level? In the dimension usage tab for the cube you must use the same combination as in your composite key. You do not have to change the name column.

Check the Adventure Works Demo project that is part of the installation. Have a look at the date.dim there(and the attribute month_name(properties)) and check the relation between the date dim and sales targets measure group in the Adventure Works cube.

Regards

Thomas Ivarsson

|||

Nice that it works. Without attribute relations you will not get any aggregations on the time dimension. Another problem is that your calculations can be wrong. What you needed to do was to add 2006(Year) to the quarters(1,2,3,4) so that their collection will point to year and quarter, at the same time. Actually you combine each year with each of their quarter in this way. Quarter(1,2,3,4) will not point to a specific year if you do not do this(ie add the year key to each quarter key).

Regards

Thomas Ivarsson

|||

Hi, Thomas

Thanks a lot for your response! I have several questions about your answer.

First, what do you mean by have a join on one of the levles above the leaf levels? And how can I see that?

Second, I have seen the AdventureWorks cube and see the attribute month_name has a combination key column of year + month. But in the dim usage table, I do not know how to modify my own project. I can see a RED line under my date dimension and I think that indicates error. However, if I add an attribute relationship between Quarter and Year, the dim usage table will automaticlly add this relationship to itself. (In other words, I can not find the place to add sth about the combination key in the dim usage table)

|||

Hello again Jeremy. If you scroll out to the right, in the dimension usage tab of the cube editor(In the Adventure Works demo cube), you will find a box where the dimension date intersect with the measure group Sales Targets. All the other relations between date and the measure groups in this cube are at the lowest(leaf level) or the box empty(no relation). Click the box(date dim and Sales Targets) There you can see the granularity attribute for the relation, thats the level that is used in the join between the measure group and the dimension.

Check here that you join on the correct column to avoid the error message you talked about before: "The key columns of the Quarter measure group attribute do not match in either number or data types to the key columns of the source attribute." You will only have to this if you have changed the key column for the Quarter member in your time dimension.

Regards

Thomas Ivarsson

ref mail error

when I attepmt to start SQL ServerAgent I get the
following error messages:
[298] SQLServer Error: 18456, Login failed for user 'sa'.
[SQLSTATE 28000]
[000] Unable to connect to server '(local)';
SQLServerAgent cannot start
In SQL ServerAgent properties my sa password is correct
and I get a similar error message when I select 'Use
Windows authentication' on the Server Conection tab, can
this be caused by a problem with my Service Startup
acount ?
> when I attepmt to start SQL ServerAgent I get the
> following error messages:
> [298] SQLServer Error: 18456, Login failed for user 'sa'.
> [SQLSTATE 28000]
> [000] Unable to connect to server '(local)';
> SQLServerAgent cannot start
> In SQL ServerAgent properties my sa password is correct
> and I get a similar error message when I select 'Use
> Windows authentication' on the Server Conection tab, can
> this be caused by a problem with my Service Startup
> acount ?
The Builtin\Administrators might have been removed as a sysadmin.
Hope this helps,
Eric Crdenas
SQL Server senior support professional

ref mail error

when I attepmt to start SQL ServerAgent I get the
following error messages:
[298] SQLServer Error: 18456, Login failed for user 'sa'.
[SQLSTATE 28000]
[000] Unable to connect to server '(local)';
SQLServerAgent cannot start
In SQL ServerAgent properties my sa password is correct
and I get a similar error message when I select 'Use
Windows authentication' on the Server Conection tab, can
this be caused by a problem with my Service Startup
acount ?> when I attepmt to start SQL ServerAgent I get the
> following error messages:
> [298] SQLServer Error: 18456, Login failed for user 'sa'.
> [SQLSTATE 28000]
> [000] Unable to connect to server '(local)';
> SQLServerAgent cannot start
> In SQL ServerAgent properties my sa password is correct
> and I get a similar error message when I select 'Use
> Windows authentication' on the Server Conection tab, can
> this be caused by a problem with my Service Startup
> acount ?
--
The Builtin\Administrators might have been removed as a sysadmin.
Hope this helps,
Eric Crdenas
SQL Server senior support professional

ref mail error

when I attepmt to start SQL ServerAgent I get the
following error messages:
[298] SQLServer Error: 18456, Login failed for user 'sa'.
[SQLSTATE 28000]
[000] Unable to connect to server '(local)';
SQLServerAgent cannot start
In SQL ServerAgent properties my sa password is correct
and I get a similar error message when I select 'Use
Windows authentication' on the Server Conection tab, can
this be caused by a problem with my Service Startup
acount ?> when I attepmt to start SQL ServerAgent I get the
> following error messages:
> [298] SQLServer Error: 18456, Login failed for user 'sa'.
> [SQLSTATE 28000]
> [000] Unable to connect to server '(local)';
> SQLServerAgent cannot start
> In SQL ServerAgent properties my sa password is correct
> and I get a similar error message when I select 'Use
> Windows authentication' on the Server Conection tab, can
> this be caused by a problem with my Service Startup
> acount ?
--
The Builtin\Administrators might have been removed as a sysadmin.
Hope this helps,
--
Eric Cárdenas
SQL Server senior support professional

reduce rendering time in RS2005

We have a reporting system where the default rendering format is HTML.
HOwever, in some cases user may export the data into Excel aftergenerating
the report in HTML. Howveer, this export is taking too much time. eg 5500
row report in HTML takes around 8 minutes to export into excel. Is there a
workaround for this? Please note that default rendering has to be in HTML
only.
Also another feature noticed is that in RS 2005, the report server execution
log seems to be logging seperate entries for export feature as well. This
was not happening in RS2000. Is this a new feature in RS2005 or is the
underlying SP for the new report being called again when the export to excel
happens?
Any help would be appreciatedOn Feb 19, 11:32 pm, SK <S...@.discussions.microsoft.com> wrote:
> We have a reporting system where the default rendering format is HTML.
> HOwever, in some cases user may export the data into Excel aftergenerating
> the report in HTML. Howveer, this export is taking too much time. eg 5500
> row report in HTML takes around 8 minutes to export into excel. Is there a
> workaround for this? Please note that default rendering has to be in HTML
> only.
> Also another feature noticed is that in RS 2005, the report server execution
> log seems to be logging seperate entries for export feature as well. This
> was not happening in RS2000. Is this a new feature in RS2005 or is the
> underlying SP for the new report being called again when the export to excel
> happens?
> Any help would be appreciated
Have you tried exporting to a CSV file?|||This seems excessive amount of time for 5500 rows. I do much more than that
all the time. Of course, complexity matters. Also, any images in the report?
As suggested see how long it takes to export CSV. CSV takes the same amount
of time as HTML.
Also, if you do this you will want to change the CSV to use ASCII instead of
unicode. Excel puts unicode into a single column. To have RS export CSV in
ASCII format you have to make a change to a config file.
You only need to change in one place, rsreportserver.config. Reboot after
the change. The below shows commenting out the existing entry and putting in
the needed change to have CSV export as ASCII
<!--
<Extension Name="CSV"
Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering"/>
-->
<Extension Name="CSV"
Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
<Configuration>
<DeviceInfo>
<Encoding>ASCII</Encoding>
</DeviceInfo>
</Configuration>
</Extension>
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"SK" <SK@.discussions.microsoft.com> wrote in message
news:D94C7011-C90D-46F7-AA75-E33CC8BEFF1A@.microsoft.com...
> We have a reporting system where the default rendering format is HTML.
> HOwever, in some cases user may export the data into Excel aftergenerating
> the report in HTML. Howveer, this export is taking too much time. eg
> 5500
> row report in HTML takes around 8 minutes to export into excel. Is there
> a
> workaround for this? Please note that default rendering has to be in HTML
> only.
> Also another feature noticed is that in RS 2005, the report server
> execution
> log seems to be logging seperate entries for export feature as well. This
> was not happening in RS2000. Is this a new feature in RS2005 or is the
> underlying SP for the new report being called again when the export to
> excel
> happens?
> Any help would be appreciated

Reduce file size of image when uploaded to sql server

Hi,

Is it possible to reduce the file size of an image when the user uploads it into a sql server image field.
i.e Crush it from 500K to less.

Regards,

RGHi,

If you just want to reduce the file size but keep the same content, you could ZIP it after uploading it.
http://www.icsharpcode.net/OpenSource/SharpZipLib/Default.aspx

Or, if you want to reduce the physical image dimensions, you could make a thumbnail.
http://www.csharp-station.com/Articles/Thumbnails.aspx

Or both!

A.|||Zipping won't help much. Most image types that I've used are already compressed and zipping them gets you very little. Make a thumbnail or use an image processing component to reduce the resolution.|||Thanks Guys,

The user uploads the pic which is then saved to an image field as the full image; as well as a Thumnail made and stored in another image field.
The images are then available through the page.

The thumnail is fine but the full image needs sometimes to be compressed further.

I'll take a look at image processing components but is there now easier way to do this?

Should I be storing images in SQL server anyway, some people say not??

Regards,

RG|||::The thumnail is fine but the full image needs sometimes to be compressed further.

The bad thing with thisis that unless the "image" is an uncompressed bitmap there basically is not too much you can compress out.

::I'll take a look at image processing components but is there now easier way to do this?

No. You need to change the image to compress it further, or you need to change the format of the image (like bitmap to jpg).

::Should I be storing images in SQL server anyway, some people say not??

Some people will also gladly tell you to kill yourself. Will you follow their advice, too?

Storing images in SQL Serverhas advantages and disadvantages. Disads are that SQL Server storage is more costly (in hosting), that operformance is lower (The file system IS faster) unless you manage to pull off some caching (not hard). Advantages are that you can use relational integrity, stuff like cascading delete etc. - basically it is pretty hard to accumulate garbage somewhere.|||That question is one of those which people always debate! Putting it in the database you gain referential integrity, but for much larger files you'll probably lose performance. For small stuff, putting them in the database is fine IMHO, disk is cheap and it keeps things tidy.

A.