Hide aspnet_* objects

Hello, I would like to not have to see the aspnet_ tables and stored procedures that are created when using the Membership, roles, and personalization. Currently I have to suffer seeing the handful of tables and 40+ stored procedures in both the Visual Studio and the SQL Management tool. I have found that on a 2000 SQL Server I can execute a command that forces objects to be created as system objects. If I execute this before creating the objects they become system objects and I don't have to see them any longer.

However, this trick doesnot work in SQL Server 2005. So, I would like to know either 1) is there an easy way to hide these objects or 2) is there a way to change the objects to system objects in SQL Server 2005?

CodeGuy

Right click on the tables folder in Management Studio and choose Filter -> Filter settings.

Now create a filter using name NOT CONTAINING aspnet_

Hope it helps

|||

Klaus, thank you for mentioning that. I actually found reference to that as well. Two downsides to that approach, first you have to set the filter upEVERY TIME, because it's not saved. Second, that only helps me a small amount because it only works in the Management Studio.

CodeGuy

|||

Another option would be to use a separate database for the aspnet_* tables... but that has drawbacks aswell. Hope you find a good solution and keep us posted.

|||

Klaus, thank you for the ideas. You suggestion definitely would work, however, I'd prefer to have these objects in the same database for portability reasons.

Anyone else have any good ideas?

HexToInt and HexToSmallInt

Inspired/challenged by Hans Lindgren's stored procedures of these same names
posted on SQLServerCentral, I created these. Note that they produce strange
results on non-hexadecimal strings, and may have issues with byte-ordering
in some architectures (but Itanium is little-endian like x86 and x64,
right?).

How do they work? well, the distance between one after '9' (':') and 'A' is
7 in ASCII. Also, if I subtract 48 from an upper-cased hex-digit, digit & 16
will always be equal to 16. So I can mask that bit out, shift it down 4 bits
(/16), multiply by 7, subtract from the original value, and come up with a
value from 0 to 15. This can be done on all 8 digits in parallel, as you can
see below.

I use CAST(CAST('1234ABCD' AS BINARY(8))AS BIGINT) to put the hex value
1234ABCD into a number I can manipulate, then subtract the value '00000000'
(CAST(0x3030303030303030 AS BIGINT)), then mask out the hex overflow bits,
shift right, multiply by 7, subtract to make the values 0x010203040A0B0C0D,
then I shift the bits into the proper places and add.

It's probably easier in assembly language than SQL, but oh well.

It's only about 20% faster than Hans's series of CHARINDEX calls.

CREATE FUNCTION dbo.HexToINT
(
@.Value VARCHAR(8)
)
RETURNS INT
AS
BEGIN
DECLARE @.I BIGINT
SET @.I = CAST(CAST(RIGHT( UPPER( '00000000' + @.Value ) , 8 )
AS BINARY(8)) AS BIGINT) - 3472328296227680304
SET @.I=@.I-((@.I/16)&CAST(72340172838076673 AS BIGINT))*7
RETURN (
(@.I&15)
+((@.I/16)&240)
+((@.I/256)&3840)
+((@.I/4096)&61440)
+((@.I/65536)&983040)
+((@.I/1048576)&15728640)
+((@.I/16777216)&251658240)
+((@.I/72057594037927936)*268435456) -- cause an OF if > 0x80000000
)

END
GO

CREATE FUNCTION dbo.HexToSMALLINT
(
@.Value VARCHAR(4)
)
RETURNS SMALLINT
AS
BEGIN
DECLARE @.I INT
SET @.I = CAST(CAST(RIGHT( UPPER( '0000' + @.Value ) , 4 )
AS BINARY(4)) AS INT) - 808464432
SET @.I=@.I-(@.I&269488144)*7/16
RETURN (
@.I&255
+(@.I&65280)/16
+(@.I&16711680)/256
+(@.I&2130706432)/4096
)
END
GOThe revised versions below will allow negative numbers, eg.
HexToINT('80000000')

--

Inspired/challenged by Hans Lindgren's stored procedures of these same names
posted on SQLServerCentral, I created these. Note that they produce strange
results on non-hexadecimal strings, and may have issues with byte-ordering
in some architectures (but Itanium is little-endian like x86 and x64,
right?).

How do they work? well, the distance between one after '9' (':') and 'A' is
7 in ASCII. Also, if I subtract 48 from an upper-cased hex-digit, digit & 16
will always be equal to 16. So I can mask that bit out, shift it down 4 bits
(/16), multiply by 7, subtract from the original value, and come up with a
value from 0 to 15. This can be done on all 8 digits in parallel, as you can
see below.

I use CAST(CAST('1234ABCD' AS BINARY(8)) AS BIGINT) to put the
string of hexadecimal digit characters 1234ABCD into a number I can
manipulate,
then subtract the value '00000000' (CAST(0x3030303030303030 AS BIGINT)),
then mask out the hex overflow bits, shift right 4 places (/16), multiply by
7,
subtract to make the values 0x010203040A0B0C0D,
then I shift the bits into the proper places and add. to result in
0x1234ABCD, CAST AS INT.

alter FUNCTION dbo.HexToSMALLINT
(
@.Value VARCHAR(4)
)
RETURNS SMALLINT
AS
BEGIN
DECLARE @.I INT
SET @.I = CAST(CAST(RIGHT( UPPER( '0000' + @.Value ) , 4 )
AS BINARY(4)) AS INT) - 808464432
SET @.I=@.I-(@.I&269488144)*7/16
RETURN CAST(CAST(
(@.I&15)
+((@.I/16)&240)
+((@.I/256)&3840)
+((@.I/4096)&61440)
AS BINARY(2))AS SMALLINT)
END
GO

alter FUNCTION dbo.HexToINT
(
@.Value VARCHAR(8)
)
RETURNS INT
AS
BEGIN
DECLARE @.I BIGINT
SET @.I = CAST(CAST(RIGHT( UPPER( '00000000' + @.Value ) , 8 )
AS BINARY(8)) AS BIGINT) - 3472328296227680304
SET @.I=@.I-((@.I/16)&CAST(72340172838076673 AS BIGINT))*7
RETURN CAST(CAST(
(@.I&15)
+((@.I/16)&240)
+((@.I/256)&3840)
+((@.I/4096)&61440)
+((@.I/65536)&983040)
+((@.I/1048576)&15728640)
+((@.I/16777216)&251658240)
+(CAST(@.I/72057594037927936 AS BIGINT)*268435456)
AS BINARY(4))AS INT)
END
GO

Heterogeneous Query and ANSI_NULLS/_WARNINGS

I'm trying to create a stored proc that creates a link to a remote server and executes
other stored procedures as needed. Each of the sub-procs will be running Heterogeneous Queries.
I'm having trouble creating these sub-procs, I get the error message that states
Heterogeneous Queries require ANSINULLS and ANSIWARNINGS be set...
I have the remote server linked when I try to create the procs.
I've tried SET ANSI_NULLS and _WARNINGS with no effect so I'm assuming these need to be set on the linked server.
I tried EXEC sp_serveroption 'sqlSOLOMON', 'ANSI_NULLS', 'ON'
EXEC sp_serveroption 'sqlSOLOMON', 'ANSI_WARNINGS', 'ON'
but got an 'invalid options' error.
Does this need to be set as server defaults on each server or can I make these settings in a usp?
After the procs are created and I create the link at runtime can I expect some problems?
Any suggestions/links would be greatly appreciated.
I am also an Application developer who gets roped into this DBA stuff as needed. I'm the closest they have here
to a DBA and I cant find any decent answers in 'books on-line' or my books on-desk.
'Ack!'
troy
Troy,
The settings need to be set when you create your stored
procedures - not in the stored procedures but for the
connection creating the stored procedures - so that the
stored procedures are created with these settings. Something
along the lines of:
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE YourStoredProc...etc.
-Sue
On Fri, 9 Apr 2004 13:06:06 -0700, "Troy"
<anonymous@.discussions.microsoft.com> wrote:

>I'm trying to create a stored proc that creates a link to a remote server and executes
>other stored procedures as needed. Each of the sub-procs will be running Heterogeneous Queries.
>I'm having trouble creating these sub-procs, I get the error message that states
> Heterogeneous Queries require ANSINULLS and ANSIWARNINGS be set...
>I have the remote server linked when I try to create the procs.
>I've tried SET ANSI_NULLS and _WARNINGS with no effect so I'm assuming these need to be set on the linked server.
>I tried EXEC sp_serveroption 'sqlSOLOMON', 'ANSI_NULLS', 'ON'
> EXEC sp_serveroption 'sqlSOLOMON', 'ANSI_WARNINGS', 'ON'
>but got an 'invalid options' error.
>Does this need to be set as server defaults on each server or can I make these settings in a usp?
>After the procs are created and I create the link at runtime can I expect some problems?
>Any suggestions/links would be greatly appreciated.
>I am also an Application developer who gets roped into this DBA stuff as needed. I'm the closest they have here
>to a DBA and I cant find any decent answers in 'books on-line' or my books on-desk.
>'Ack!'
>troy
|||Thanks Sue, that took care of it.
|||Troy / Sue
I'm experiencing the same issue raised in this thread. Sounds like you
guys figured out what the problem was but the solution is not in this
thread. Any chance you can share your wisdom?
imalik
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message177518.html
|||If you have the same issues with a stored procedure, the
answer is in the post in this thread. You need to create the
procedure with the appropriate setting e.g.
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE YourStoredProc...etc.
-Sue
On Mon, 10 Jan 2005 04:45:31 -0600, imalik
<imalik.1inb94@.mail.webservertalk.com> wrote:

>Troy / Sue
>I'm experiencing the same issue raised in this thread. Sounds like you
>guys figured out what the problem was but the solution is not in this
>thread. Any chance you can share your wisdom?
|||Hi all!
I have the same problem with a view I'm trying to use with Crystal Reports.
I did recreate my view with ANSI_NULL, ANSI_WARNINGS statements but it's still doesn't work...
How can I solve it please?

Quote:

Originally Posted by Sue Hoegemeier

If you have the same issues with a stored procedure, the
answer is in the post in this thread. You need to create the
procedure with the appropriate setting e.g.
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE YourStoredProc...etc.
-Sue
On Mon, 10 Jan 2005 04:45:31 -0600, imalik
<imalik.1inb94@.mail.webservertalk.com> wrote:

>Troy / Sue
>I'm experiencing the same issue raised in this thread. Sounds like you
>guys figured out what the problem was but the solution is not in this
>thread. Any chance you can share your wisdom?

|||I'm not sure what you mean by trying to use - what is the
datasource? Is it a stored procedure, a query, the view
itself?
What is the exact error message and when/how do you get it?
Whatever you are executing that involves this view in
Crystal, try executing the same thing in a query tool and
see if you get the same message. Take Crystal out of the mix
to start with.
-Sue
On Wed, 16 May 2007 10:53:15 -0500, zen69
<zen69.2qp5em@.no-mx.forums.yourdomain.com.au> wrote:
[vbcol=seagreen]
>Hi all!
>I have the same problem with a view I'm trying to use with Crystal
>Reports.
>I did recreate my view with ANSI_NULL, ANSI_WARNINGS statements but
>it's still doesn't work...
>How can I solve it please?
>Sue Hoegemeier;3612624 Wrote:
|||

Quote:

Originally Posted by Sue Hoegemeier

I'm not sure what you mean by trying to use - what is the
datasource? Is it a stored procedure, a query, the view
itself?
What is the exact error message and when/how do you get it?
Whatever you are executing that involves this view in
Crystal, try executing the same thing in a query tool and
see if you get the same message. Take Crystal out of the mix
to start with.
-Sue
On Wed, 16 May 2007 10:53:15 -0500, zen69
<zen69.2qp5em@.no-mx.forums.yourdomain.com.au> wrote:
[vbcol=seagreen]
>Hi all!
>I have the same problem with a view I'm trying to use with Crystal
>Reports.
>I did recreate my view with ANSI_NULL, ANSI_WARNINGS statements but
>it's still doesn't work...
>How can I solve it please?
>Sue Hoegemeier;3612624 Wrote:

I fix my problem by setting ANSI_NULL/WARNINGS in my ODBC source. Thx anyway

Heterogeneous Query and ANSI_NULLS/_WARNINGS

I'm trying to create a stored proc that creates a link to a remote server an
d executes
other stored procedures as needed. Each of the sub-procs will be running He
terogeneous Queries.
I'm having trouble creating these sub-procs, I get the error message that st
ates
Heterogeneous Queries require ANSINULLS and ANSIWARNINGS be set...
I have the remote server linked when I try to create the procs.
I've tried SET ANSI_NULLS and _WARNINGS with no effect so I'm assuming these
need to be set on the linked server.
I tried EXEC sp_serveroption 'sqlSOLOMON', 'ANSI_NULLS', 'ON'
EXEC sp_serveroption 'sqlSOLOMON', 'ANSI_WARNINGS', 'ON'
but got an 'invalid options' error.
Does this need to be set as server defaults on each server or can I make the
se settings in a usp?
After the procs are created and I create the link at runtime can I expect so
me problems?
Any suggestions/links would be greatly appreciated.
I am also an Application developer who gets roped into this DBA stuff as nee
ded. I'm the closest they have here
to a DBA and I cant find any decent answers in 'books on-line' or my books o
n-desk.
'Ack!'
troyThanks Sue, that took care of it.|||Troy / Sue
I'm experiencing the same issue raised in this thread. Sounds like you guys
figured out what the problem was but the solution is not in this thread. Any
chance you can share your wisdom?|||If you have the same issues with a stored procedure, the
answer is in the post in this thread. You need to create the
procedure with the appropriate setting e.g.
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE YourStoredProc...etc.
-Sue
On Mon, 10 Jan 2005 04:45:31 -0600, imalik
<imalik.1inb94@.mail.webservertalk.com> wrote:

>Troy / Sue
>I'm experiencing the same issue raised in this thread. Sounds like you
>guys figured out what the problem was but the solution is not in this
>thread. Any chance you can share your wisdom?|||Hi all!
I have the same problem with a view I'm trying to use with Crystal
Reports.
I did recreate my view with ANSI_NULL, ANSI_WARNINGS statements but
it's still doesn't work...
How can I solve it please?
Sue Hoegemeier;3612624 Wrote:[vbcol=seagreen]
> If you have the same issues with a stored procedure, the
> answer is in the post in this thread. You need to create the
> procedure with the appropriate setting e.g.
> SET ANSI_NULLS ON
> GO
> SET ANSI_WARNINGS ON
> GO
> CREATE PROCEDURE YourStoredProc...etc.
>
> -Sue
> On Mon, 10 Jan 2005 04:45:31 -0600, imalik
> <imalik.1inb94@.mail.webservertalk.com> wrote:
>
> you
zen69|||I'm not sure what you mean by trying to use - what is the
datasource? Is it a stored procedure, a query, the view
itself?
What is the exact error message and when/how do you get it?
Whatever you are executing that involves this view in
Crystal, try executing the same thing in a query tool and
see if you get the same message. Take Crystal out of the mix
to start with.
-Sue
On Wed, 16 May 2007 10:53:15 -0500, zen69
<zen69.2qp5em@.no-mx.forums.yourdomain.com.au> wrote:
[vbcol=seagreen]
>Hi all!
>I have the same problem with a view I'm trying to use with Crystal
>Reports.
>I did recreate my view with ANSI_NULL, ANSI_WARNINGS statements but
>it's still doesn't work...
>How can I solve it please?
>Sue Hoegemeier;3612624 Wrote: