Showing posts with label user. Show all posts
Showing posts with label user. Show all posts

Friday, March 30, 2012

Raiserror

Is there a way to raise an exception inside a user function in the Sqlserver2000?.Guess not

Did you check out BOL for specifics?

CREATE FUNCTION udf_myFunction99 (@.x varchar(8000))
RETURNS int
AS
BEGIN
RAISERROR 50001 'Error Raised'
RETURN -1
END
GO|||Originally posted by Brett Kaiser
Guess not

Did you check out BOL for specifics?

CREATE FUNCTION udf_myFunction99 (@.x varchar(8000))
RETURNS int
AS
BEGIN
RAISERROR 50001 'Error Raised'
RETURN -1
END
GO



I got the error

Server: Msg 443, Level 16, State 2, Procedure udf_myFunction99, Line 5
Invalid use of 'RAISEERROR' within a function.|||Yeah, I know...That's why I said:

"guess not"

Check out books online for a better description of Functions...

I know there a few limitations...

What are you trying to do?|||Originally posted by Brett Kaiser
Yeah, I know...That's why I said:

"guess not"

Check out books online for a better description of Functions...

I know there a few limitations...

What are you trying to do?

There are many limitations I know, but this one I didn't found.
I supose its not allowed to use RAISERROR in functions.
I am generating a calc module from a CASE system using only functions. So far I found a solution for every limitation. Otherwise I will have to rebuild the entire system using only stored procedure instead of functions.

That1s why I am looking for some "magic" to raise the exception.

Thank You.|||...or we could hope for a miracle...

Since you haven't been able to raise out in the other functions, why do you want to with this one?

And yeah, sprocs would have been the way to go (MOO)...how are you using the functions?

BOL

The following statements are allowed in the body of a multi-statement function. Statements not in this list are not allowed in the body of a function:

Assignment statements.

Control-of-Flow statements.

DECLARE statements defining data variables and cursors that are local to the function.

SELECT statements containing select lists with expressions that assign values to variables that are local to the function.

Cursor operations referencing local cursors that are declared, opened, closed, and deallocated in the function. Only FETCH statements that assign values to local variables using the INTO clause are allowed; FETCH statements that return data to the client are not allowed.

INSERT, UPDATE, and DELETE statements modifying table variables local to the function.

EXECUTE statements calling an extended stored procedures.sql

raid question

Hi,
I have 2 user databases and would like to know aouut the
configuration. I have read about RAID and intend to use
raid 1 for the logs and raid 5 for the databases. However,
(1) what is the ideal RAID level for tempdb?
(2) Also, when people talk about raid 5, do they use raid
5 for each database - eg 3 disks for master, 3 for msdb, 3
for each user database - or is it 3 disks for raid 5 and
all databases are placed on it?
(3) And the same question for the logs - presumably each
database has its own raid1 pair of disks?
TIA,
JB
JB,

> I have 2 user databases and would like to know aouut the
> configuration. I have read about RAID and intend to use
> raid 1 for the logs and raid 5 for the databases. However,
> (1) what is the ideal RAID level for tempdb?
RAID 1 - you will get some boost if tempdb can get its own raid array.
If you cannot do that, then put its data and log files with the other data
and log files.

> (2) Also, when people talk about raid 5, do they use raid
> 5 for each database - eg 3 disks for master, 3 for msdb, 3
> for each user database - or is it 3 disks for raid 5 and
> all databases are placed on it?
All the databases on the same RAID array, unless you have really serious
I/O issues and the money to spend. (If you have two databases, it is
unlikely that you have that big an issue.)

> (3) And the same question for the logs - presumably each
> database has its own raid1 pair of disks?
Almost never, as above. All logs on one raid array.
Russell Fields

> TIA,
> JB
|||Thanks Russell for the reply,
you mention that typically all logs are on the same raid
1 pair. I was thinking that the disk head not being where
it need to be to do the next write would mean that
performance would degrade in this case? Is there much
difference in your experience/opinion between one raid
pair per log or one pair for everything?
TIA,
JB
|||Few people have the luxury of having a separate Raid 1 for each log file
when they have multiple databases. It is better to share a Raid 1 with
other log files than data files. Unless you are constantly doing hundreds
or thousands of transactions per second a Raid 1 with several log files
should do fine.
Andrew J. Kelly SQL MVP
"JB" <anonymous@.discussions.microsoft.com> wrote in message
news:623a01c492a3$0ef79e50$a301280a@.phx.gbl...
> Thanks Russell for the reply,
> you mention that typically all logs are on the same raid
> 1 pair. I was thinking that the disk head not being where
> it need to be to do the next write would mean that
> performance would degrade in this case? Is there much
> difference in your experience/opinion between one raid
> pair per log or one pair for everything?
> TIA,
> JB
>
|||Also most folks have a single raid 5 array for all of their user database.
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
"JB" <anonymous@.discussions.microsoft.com> wrote in message
news:5f9001c4928b$ce1ef020$a601280a@.phx.gbl...
> Hi,
> I have 2 user databases and would like to know aouut the
> configuration. I have read about RAID and intend to use
> raid 1 for the logs and raid 5 for the databases. However,
> (1) what is the ideal RAID level for tempdb?
> (2) Also, when people talk about raid 5, do they use raid
> 5 for each database - eg 3 disks for master, 3 for msdb, 3
> for each user database - or is it 3 disks for raid 5 and
> all databases are placed on it?
> (3) And the same question for the logs - presumably each
> database has its own raid1 pair of disks?
> TIA,
> JB
sql

raid question

Hi,
I have 2 user databases and would like to know aouut the
configuration. I have read about RAID and intend to use
raid 1 for the logs and raid 5 for the databases. However,
(1) what is the ideal RAID level for tempdb?
(2) Also, when people talk about raid 5, do they use raid
5 for each database - eg 3 disks for master, 3 for msdb, 3
for each user database - or is it 3 disks for raid 5 and
all databases are placed on it?
(3) And the same question for the logs - presumably each
database has its own raid1 pair of disks?
TIA,
JBJB,
> I have 2 user databases and would like to know aouut the
> configuration. I have read about RAID and intend to use
> raid 1 for the logs and raid 5 for the databases. However,
> (1) what is the ideal RAID level for tempdb?
RAID 1 - you will get some boost if tempdb can get its own raid array.
If you cannot do that, then put its data and log files with the other data
and log files.
> (2) Also, when people talk about raid 5, do they use raid
> 5 for each database - eg 3 disks for master, 3 for msdb, 3
> for each user database - or is it 3 disks for raid 5 and
> all databases are placed on it?
All the databases on the same RAID array, unless you have really serious
I/O issues and the money to spend. (If you have two databases, it is
unlikely that you have that big an issue.)
> (3) And the same question for the logs - presumably each
> database has its own raid1 pair of disks?
Almost never, as above. All logs on one raid array.
Russell Fields
> TIA,
> JB|||Thanks Russell for the reply,
you mention that typically all logs are on the same raid
1 pair. I was thinking that the disk head not being where
it need to be to do the next write would mean that
performance would degrade in this case? Is there much
difference in your experience/opinion between one raid
pair per log or one pair for everything?
TIA,
JB|||Few people have the luxury of having a separate Raid 1 for each log file
when they have multiple databases. It is better to share a Raid 1 with
other log files than data files. Unless you are constantly doing hundreds
or thousands of transactions per second a Raid 1 with several log files
should do fine.
--
Andrew J. Kelly SQL MVP
"JB" <anonymous@.discussions.microsoft.com> wrote in message
news:623a01c492a3$0ef79e50$a301280a@.phx.gbl...
> Thanks Russell for the reply,
> you mention that typically all logs are on the same raid
> 1 pair. I was thinking that the disk head not being where
> it need to be to do the next write would mean that
> performance would degrade in this case? Is there much
> difference in your experience/opinion between one raid
> pair per log or one pair for everything?
> TIA,
> JB
>|||Also most folks have a single raid 5 array for all of their user database.
--
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
"JB" <anonymous@.discussions.microsoft.com> wrote in message
news:5f9001c4928b$ce1ef020$a601280a@.phx.gbl...
> Hi,
> I have 2 user databases and would like to know aouut the
> configuration. I have read about RAID and intend to use
> raid 1 for the logs and raid 5 for the databases. However,
> (1) what is the ideal RAID level for tempdb?
> (2) Also, when people talk about raid 5, do they use raid
> 5 for each database - eg 3 disks for master, 3 for msdb, 3
> for each user database - or is it 3 disks for raid 5 and
> all databases are placed on it?
> (3) And the same question for the logs - presumably each
> database has its own raid1 pair of disks?
> TIA,
> JB

Friday, March 23, 2012

Race condition in SQL Server 2005

hi,
How SQL Server 2005 handel concurrent access to database.
for example. if two application user same data base and same user name to
access it. when both application attempts to access the database how Server
handle this race condition.
Thank in advance
with regards,
Anbu
On 29 May, 07:00, anbu <a...@.discussions.microsoft.com> wrote:
> hi,
> How SQL Server 2005 handel concurrent access to database.
> for example. if two application user same data base and same user name to
> access it. when both application attempts to access the database how Server
> handle this race condition.
> Thank in advance
> with regards,
> Anbu
What you describe is normal. A user's session is scoped to a
connection not a user name, so there is no special problem associated
with multiple users under the same user name accessing the same
database.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Thanks David,
iam very new to SQL Server. can you refer any links where i can find details
about how race condition is handled by Server.
with regards,
Anbu
"David Portas" wrote:

> On 29 May, 07:00, anbu <a...@.discussions.microsoft.com> wrote:
>
> What you describe is normal. A user's session is scoped to a
> connection not a user name, so there is no special problem associated
> with multiple users under the same user name accessing the same
> database.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
|||See Books Online topics related to locking and row versioning
(http://msdn2.microsoft.com/en-us/library/ms187101.aspx).
Keep in mind the scope that David mentioned when reading the documentation.
You will often see the terms "user" and "session" used interchaneably to
refer to a user's session on a database database connection. This is
completely unrelated to the login/user used to connect to SQL Server.
Hope this helps.
Dan Guzman
SQL Server MVP
"anbu" <anbu@.discussions.microsoft.com> wrote in message
news:53410F4E-C145-446F-8F28-DFB6A24F2823@.microsoft.com...[vbcol=seagreen]
> Thanks David,
> iam very new to SQL Server. can you refer any links where i can find
> details
> about how race condition is handled by Server.
> with regards,
> Anbu
> "David Portas" wrote:
|||"anbu" <anbu@.discussions.microsoft.com> wrote in message
news:53410F4E-C145-446F-8F28-DFB6A24F2823@.microsoft.com...
> Thanks David,
> iam very new to SQL Server. can you refer any links where i can find
> details
> about how race condition is handled by Server.
>
Really this isn't a true race condition. (Well, I suppose it can be, but
what you seem to be getting at more is simply multiple accesses.)
Keep in mind like any decent RDBMS, SQL Server is designed to handle this.
At a very basic level, "whoever gets there first wins".
But you need to look up blocking and things like Committed vs. uncommitted
read, read serializable. etc.
[vbcol=seagreen]
> with regards,
> Anbu
> "David Portas" wrote:
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
sql

Race condition in SQL Server 2005

hi,
How SQL Server 2005 handel concurrent access to database.
for example. if two application user same data base and same user name to
access it. when both application attempts to access the database how Server
handle this race condition.
Thank in advance
with regards,
AnbuOn 29 May, 07:00, anbu <a...@.discussions.microsoft.com> wrote:
> hi,
> How SQL Server 2005 handel concurrent access to database.
> for example. if two application user same data base and same user name to
> access it. when both application attempts to access the database how Serve
r
> handle this race condition.
> Thank in advance
> with regards,
> Anbu
What you describe is normal. A user's session is scoped to a
connection not a user name, so there is no special problem associated
with multiple users under the same user name accessing the same
database.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks David,
iam very new to SQL Server. can you refer any links where i can find details
about how race condition is handled by Server.
with regards,
Anbu
"David Portas" wrote:

> On 29 May, 07:00, anbu <a...@.discussions.microsoft.com> wrote:
>
> What you describe is normal. A user's session is scoped to a
> connection not a user name, so there is no special problem associated
> with multiple users under the same user name accessing the same
> database.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||See Books Online topics related to locking and row versioning
(http://msdn2.microsoft.com/en-us/library/ms187101.aspx).
Keep in mind the scope that David mentioned when reading the documentation.
You will often see the terms "user" and "session" used interchaneably to
refer to a user's session on a database database connection. This is
completely unrelated to the login/user used to connect to SQL Server.
Hope this helps.
Dan Guzman
SQL Server MVP
"anbu" <anbu@.discussions.microsoft.com> wrote in message
news:53410F4E-C145-446F-8F28-DFB6A24F2823@.microsoft.com...[vbcol=seagreen]
> Thanks David,
> iam very new to SQL Server. can you refer any links where i can find
> details
> about how race condition is handled by Server.
> with regards,
> Anbu
> "David Portas" wrote:
>|||"anbu" <anbu@.discussions.microsoft.com> wrote in message
news:53410F4E-C145-446F-8F28-DFB6A24F2823@.microsoft.com...
> Thanks David,
> iam very new to SQL Server. can you refer any links where i can find
> details
> about how race condition is handled by Server.
>
Really this isn't a true race condition. (Well, I suppose it can be, but
what you seem to be getting at more is simply multiple accesses.)
Keep in mind like any decent RDBMS, SQL Server is designed to handle this.
At a very basic level, "whoever gets there first wins".
But you need to look up blocking and things like Committed vs. uncommitted
read, read serializable. etc.
[vbcol=seagreen]
> with regards,
> Anbu
> "David Portas" wrote:
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Race condition in SQL Server 2005

hi,
How SQL Server 2005 handel concurrent access to database.
for example. if two application user same data base and same user name to
access it. when both application attempts to access the database how Server
handle this race condition.
Thank in advance
with regards,
AnbuOn 29 May, 07:00, anbu <a...@.discussions.microsoft.com> wrote:
> hi,
> How SQL Server 2005 handel concurrent access to database.
> for example. if two application user same data base and same user name to
> access it. when both application attempts to access the database how Server
> handle this race condition.
> Thank in advance
> with regards,
> Anbu
What you describe is normal. A user's session is scoped to a
connection not a user name, so there is no special problem associated
with multiple users under the same user name accessing the same
database.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks David,
iam very new to SQL Server. can you refer any links where i can find details
about how race condition is handled by Server.
with regards,
Anbu
"David Portas" wrote:
> On 29 May, 07:00, anbu <a...@.discussions.microsoft.com> wrote:
> > hi,
> >
> > How SQL Server 2005 handel concurrent access to database.
> > for example. if two application user same data base and same user name to
> > access it. when both application attempts to access the database how Server
> > handle this race condition.
> >
> > Thank in advance
> > with regards,
> > Anbu
>
> What you describe is normal. A user's session is scoped to a
> connection not a user name, so there is no special problem associated
> with multiple users under the same user name accessing the same
> database.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||See Books Online topics related to locking and row versioning
(http://msdn2.microsoft.com/en-us/library/ms187101.aspx).
Keep in mind the scope that David mentioned when reading the documentation.
You will often see the terms "user" and "session" used interchaneably to
refer to a user's session on a database database connection. This is
completely unrelated to the login/user used to connect to SQL Server.
Hope this helps.
Dan Guzman
SQL Server MVP
"anbu" <anbu@.discussions.microsoft.com> wrote in message
news:53410F4E-C145-446F-8F28-DFB6A24F2823@.microsoft.com...
> Thanks David,
> iam very new to SQL Server. can you refer any links where i can find
> details
> about how race condition is handled by Server.
> with regards,
> Anbu
> "David Portas" wrote:
>> On 29 May, 07:00, anbu <a...@.discussions.microsoft.com> wrote:
>> > hi,
>> >
>> > How SQL Server 2005 handel concurrent access to database.
>> > for example. if two application user same data base and same user name
>> > to
>> > access it. when both application attempts to access the database how
>> > Server
>> > handle this race condition.
>> >
>> > Thank in advance
>> > with regards,
>> > Anbu
>>
>> What you describe is normal. A user's session is scoped to a
>> connection not a user name, so there is no special problem associated
>> with multiple users under the same user name accessing the same
>> database.
>> --
>> David Portas, SQL Server MVP
>> Whenever possible please post enough code to reproduce your problem.
>> Including CREATE TABLE and INSERT statements usually helps.
>> State what version of SQL Server you are using and specify the content
>> of any error messages.
>> SQL Server Books Online:
>> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
>> --
>>|||"anbu" <anbu@.discussions.microsoft.com> wrote in message
news:53410F4E-C145-446F-8F28-DFB6A24F2823@.microsoft.com...
> Thanks David,
> iam very new to SQL Server. can you refer any links where i can find
> details
> about how race condition is handled by Server.
>
Really this isn't a true race condition. (Well, I suppose it can be, but
what you seem to be getting at more is simply multiple accesses.)
Keep in mind like any decent RDBMS, SQL Server is designed to handle this.
At a very basic level, "whoever gets there first wins".
But you need to look up blocking and things like Committed vs. uncommitted
read, read serializable. etc.
> with regards,
> Anbu
> "David Portas" wrote:
>> On 29 May, 07:00, anbu <a...@.discussions.microsoft.com> wrote:
>> > hi,
>> >
>> > How SQL Server 2005 handel concurrent access to database.
>> > for example. if two application user same data base and same user name
>> > to
>> > access it. when both application attempts to access the database how
>> > Server
>> > handle this race condition.
>> >
>> > Thank in advance
>> > with regards,
>> > Anbu
>>
>> What you describe is normal. A user's session is scoped to a
>> connection not a user name, so there is no special problem associated
>> with multiple users under the same user name accessing the same
>> database.
>> --
>> David Portas, SQL Server MVP
>> Whenever possible please post enough code to reproduce your problem.
>> Including CREATE TABLE and INSERT statements usually helps.
>> State what version of SQL Server you are using and specify the content
>> of any error messages.
>> SQL Server Books Online:
>> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
>> --
>>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Wednesday, March 21, 2012

Quoted Identifier ON as default

Currently my user databases, and model database, all have QUOTED IDENTIFER
set to false.
I want to help ensure that QUOTED_IDENTIFIER is set to ON under the following
conditions:
1. When a stored procedure is created (assuming the connection that is
performing the create, allows database default settings) to use the database
default settings. In other words, there is not a SET QUOTED_IDENTIFIER ON
statement when the procedure is created or altered, but instead it uses the
database default setting.
2. When a new database is created, its default setting is QUOTED IDENTIFIERS
ENABLED = TRUE.
How would one go about doing this?
Message posted via http://www.droptable.com
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6a6bb12d8cd92@.uwe...
> Currently my user databases, and model database, all have QUOTED IDENTIFER
> set to false.
> I want to help ensure that QUOTED_IDENTIFIER is set to ON under the
> following
> conditions:
> 1. When a stored procedure is created (assuming the connection that is
> performing the create, allows database default settings) to use the
> database
> default settings. In other words, there is not a SET QUOTED_IDENTIFIER ON
> statement when the procedure is created or altered, but instead it uses
> the
> database default setting.
> 2. When a new database is created, its default setting is QUOTED
> IDENTIFIERS
> ENABLED = TRUE.
> How would one go about doing this?
For the database you will have to monitor and complain, since creating
databases cannot be rolled back.
For stored procedures you can add a DDL Trigger in each database that will
prevent stored procedure creation or modification from a connection that
does not have the required setting. EG
CREATE TRIGGER ddl_trig_enforce_quoted_identifiers
ON database
FOR CREATE_PROCEDURE, ALTER_PROCEDURE
AS
begin
declare @.quoted_identifiers varchar(5)
set @.quoted_identifiers = EVENTDATA().value(
'(/EVENT_INSTANCE/TSQLCommand/SetOptions/@.QUOTED_IDENTIFIER)[1]',
'nvarchar(max)')
if @.quoted_identifiers <> 'ON'
begin
raiserror('You must use SET QUOTED_IDENTIFIER ON to create or alter a
procedure.',16,1)
end
--select @.quoted_identifiers quoted_identifiers
end
GO
Then
SET QUOTED_IDENTIFIER off
go
create procedure foo
as
select 1 a
Fails with
Msg 50000, Level 16, State 1, Procedure ddl_trig_enforce_quoted_identifiers,
Line 13
You must use SET QUOTED_IDENTIFIER ON to create or alter a procedure.
David

Quoted Identifier ON as default

Currently my user databases, and model database, all have QUOTED IDENTIFER
set to false.
I want to help ensure that QUOTED_IDENTIFIER is set to ON under the followin
g
conditions:
1. When a stored procedure is created (assuming the connection that is
performing the create, allows database default settings) to use the database
default settings. In other words, there is not a SET QUOTED_IDENTIFIER ON
statement when the procedure is created or altered, but instead it uses the
database default setting.
2. When a new database is created, its default setting is QUOTED IDENTIFIERS
ENABLED = TRUE.
How would one go about doing this?
Message posted via http://www.droptable.com"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6a6bb12d8cd92@.uwe...
> Currently my user databases, and model database, all have QUOTED IDENTIFER
> set to false.
> I want to help ensure that QUOTED_IDENTIFIER is set to ON under the
> following
> conditions:
> 1. When a stored procedure is created (assuming the connection that is
> performing the create, allows database default settings) to use the
> database
> default settings. In other words, there is not a SET QUOTED_IDENTIFIER ON
> statement when the procedure is created or altered, but instead it uses
> the
> database default setting.
> 2. When a new database is created, its default setting is QUOTED
> IDENTIFIERS
> ENABLED = TRUE.
> How would one go about doing this?
For the database you will have to monitor and complain, since creating
databases cannot be rolled back.
For stored procedures you can add a DDL Trigger in each database that will
prevent stored procedure creation or modification from a connection that
does not have the required setting. EG
CREATE TRIGGER ddl_trig_enforce_quoted_identifiers
ON database
FOR CREATE_PROCEDURE, ALTER_PROCEDURE
AS
begin
declare @.quoted_identifiers varchar(5)
set @.quoted_identifiers = EVENTDATA().value(
'(/EVENT_INSTANCE/TSQLCommand/SetOptions/@.QUOTED_IDENTIFIER)[1]',
'nvarchar(max)')
if @.quoted_identifiers <> 'ON'
begin
raiserror('You must use SET QUOTED_IDENTIFIER ON to create or alter a
procedure.',16,1)
end
--select @.quoted_identifiers quoted_identifiers
end
GO
Then
SET QUOTED_IDENTIFIER off
go
create procedure foo
as
select 1 a
Fails with
Msg 50000, Level 16, State 1, Procedure ddl_trig_enforce_quoted_identifiers,
Line 13
You must use SET QUOTED_IDENTIFIER ON to create or alter a procedure.
David|||Also, the database option is largely useless as all modern APIs (and tools)
will override the
database settings.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in messa
ge
news:%23TX5k4kGHHA.4920@.TK2MSFTNGP05.phx.gbl...
>
> "cbrichards via droptable.com" <u3288@.uwe> wrote in message news:6a6bb12d
8cd92@.uwe...
>
> For the database you will have to monitor and complain, since creating dat
abases cannot be rolled
> back.
> For stored procedures you can add a DDL Trigger in each database that will
prevent stored
> procedure creation or modification from a connection that does not have th
e required setting. EG
> CREATE TRIGGER ddl_trig_enforce_quoted_identifiers
> ON database
> FOR CREATE_PROCEDURE, ALTER_PROCEDURE
> AS
> begin
> declare @.quoted_identifiers varchar(5)
> set @.quoted_identifiers = EVENTDATA().value(
> '(/EVENT_INSTANCE/TSQLCommand/SetOptions/@.QUOTED_IDENTIFIER)[1]',
> 'nvarchar(max)')
> if @.quoted_identifiers <> 'ON'
> begin
> raiserror('You must use SET QUOTED_IDENTIFIER ON to create or alter a p
rocedure.',16,1)
> end
> --select @.quoted_identifiers quoted_identifiers
> end
> GO
> Then
> SET QUOTED_IDENTIFIER off
> go
> create procedure foo
> as
> select 1 a
> Fails with
> Msg 50000, Level 16, State 1, Procedure ddl_trig_enforce_quoted_identifier
s, Line 13
> You must use SET QUOTED_IDENTIFIER ON to create or alter a procedure.
>
> David
>

Quoted Identifier ON as default

Currently my user databases, and model database, all have QUOTED IDENTIFER
set to false.
I want to help ensure that QUOTED_IDENTIFIER is set to ON under the following
conditions:
1. When a stored procedure is created (assuming the connection that is
performing the create, allows database default settings) to use the database
default settings. In other words, there is not a SET QUOTED_IDENTIFIER ON
statement when the procedure is created or altered, but instead it uses the
database default setting.
2. When a new database is created, its default setting is QUOTED IDENTIFIERS
ENABLED = TRUE.
How would one go about doing this?
--
Message posted via http://www.sqlmonster.com"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6a6bb12d8cd92@.uwe...
> Currently my user databases, and model database, all have QUOTED IDENTIFER
> set to false.
> I want to help ensure that QUOTED_IDENTIFIER is set to ON under the
> following
> conditions:
> 1. When a stored procedure is created (assuming the connection that is
> performing the create, allows database default settings) to use the
> database
> default settings. In other words, there is not a SET QUOTED_IDENTIFIER ON
> statement when the procedure is created or altered, but instead it uses
> the
> database default setting.
> 2. When a new database is created, its default setting is QUOTED
> IDENTIFIERS
> ENABLED = TRUE.
> How would one go about doing this?
For the database you will have to monitor and complain, since creating
databases cannot be rolled back.
For stored procedures you can add a DDL Trigger in each database that will
prevent stored procedure creation or modification from a connection that
does not have the required setting. EG
CREATE TRIGGER ddl_trig_enforce_quoted_identifiers
ON database
FOR CREATE_PROCEDURE, ALTER_PROCEDURE
AS
begin
declare @.quoted_identifiers varchar(5)
set @.quoted_identifiers = EVENTDATA().value(
'(/EVENT_INSTANCE/TSQLCommand/SetOptions/@.QUOTED_IDENTIFIER)[1]',
'nvarchar(max)')
if @.quoted_identifiers <> 'ON'
begin
raiserror('You must use SET QUOTED_IDENTIFIER ON to create or alter a
procedure.',16,1)
end
--select @.quoted_identifiers quoted_identifiers
end
GO
Then
SET QUOTED_IDENTIFIER off
go
create procedure foo
as
select 1 a
Fails with
Msg 50000, Level 16, State 1, Procedure ddl_trig_enforce_quoted_identifiers,
Line 13
You must use SET QUOTED_IDENTIFIER ON to create or alter a procedure.
David|||Also, the database option is largely useless as all modern APIs (and tools) will override the
database settings.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in message
news:%23TX5k4kGHHA.4920@.TK2MSFTNGP05.phx.gbl...
>
> "cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:6a6bb12d8cd92@.uwe...
>> Currently my user databases, and model database, all have QUOTED IDENTIFER
>> set to false.
>> I want to help ensure that QUOTED_IDENTIFIER is set to ON under the following
>> conditions:
>> 1. When a stored procedure is created (assuming the connection that is
>> performing the create, allows database default settings) to use the database
>> default settings. In other words, there is not a SET QUOTED_IDENTIFIER ON
>> statement when the procedure is created or altered, but instead it uses the
>> database default setting.
>> 2. When a new database is created, its default setting is QUOTED IDENTIFIERS
>> ENABLED = TRUE.
>> How would one go about doing this?
>
> For the database you will have to monitor and complain, since creating databases cannot be rolled
> back.
> For stored procedures you can add a DDL Trigger in each database that will prevent stored
> procedure creation or modification from a connection that does not have the required setting. EG
> CREATE TRIGGER ddl_trig_enforce_quoted_identifiers
> ON database
> FOR CREATE_PROCEDURE, ALTER_PROCEDURE
> AS
> begin
> declare @.quoted_identifiers varchar(5)
> set @.quoted_identifiers = EVENTDATA().value(
> '(/EVENT_INSTANCE/TSQLCommand/SetOptions/@.QUOTED_IDENTIFIER)[1]',
> 'nvarchar(max)')
> if @.quoted_identifiers <> 'ON'
> begin
> raiserror('You must use SET QUOTED_IDENTIFIER ON to create or alter a procedure.',16,1)
> end
> --select @.quoted_identifiers quoted_identifiers
> end
> GO
> Then
> SET QUOTED_IDENTIFIER off
> go
> create procedure foo
> as
> select 1 a
> Fails with
> Msg 50000, Level 16, State 1, Procedure ddl_trig_enforce_quoted_identifiers, Line 13
> You must use SET QUOTED_IDENTIFIER ON to create or alter a procedure.
>
> David
>sql

Tuesday, March 20, 2012

Quick sysobjects Question

Hi,
How do I distinguish user objects from system objects in the sysobjects
table (other than user tables and system tables)?
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
WHERE OBJECTPROPERTY(id, 'IsMsShipped') = 0
However, there are some gotchas, such as sysdiagrams, dtproperties, and
maybe some replication objects...
A
"Daniel Jameson" <djameson@.childrensoncologygroup.org> wrote in message
news:%23aw9FX3BHHA.1220@.TK2MSFTNGP04.phx.gbl...
> Hi,
> How do I distinguish user objects from system objects in the sysobjects
> table (other than user tables and system tables)?
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>
|||Hi,
Take a look into INFORMATION_SCHEMA views.
Thanks
Hari
"Daniel Jameson" <djameson@.childrensoncologygroup.org> wrote in message
news:%23aw9FX3BHHA.1220@.TK2MSFTNGP04.phx.gbl...
> Hi,
> How do I distinguish user objects from system objects in the sysobjects
> table (other than user tables and system tables)?
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>

Quick sysobjects Question

Hi,
How do I distinguish user objects from system objects in the sysobjects
table (other than user tables and system tables)?
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.orgWHERE OBJECTPROPERTY(id, 'IsMsShipped') = 0
However, there are some gotchas, such as sysdiagrams, dtproperties, and
maybe some replication objects...
A
"Daniel Jameson" <djameson@.childrensoncologygroup.org> wrote in message
news:%23aw9FX3BHHA.1220@.TK2MSFTNGP04.phx.gbl...
> Hi,
> How do I distinguish user objects from system objects in the sysobjects
> table (other than user tables and system tables)?
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>|||Hi,
Take a look into INFORMATION_SCHEMA views.
Thanks
Hari
"Daniel Jameson" <djameson@.childrensoncologygroup.org> wrote in message
news:%23aw9FX3BHHA.1220@.TK2MSFTNGP04.phx.gbl...
> Hi,
> How do I distinguish user objects from system objects in the sysobjects
> table (other than user tables and system tables)?
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>

Quick sysobjects Question

Hi,
How do I distinguish user objects from system objects in the sysobjects
table (other than user tables and system tables)?
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.orgWHERE OBJECTPROPERTY(id, 'IsMsShipped') = 0
However, there are some gotchas, such as sysdiagrams, dtproperties, and
maybe some replication objects...
A
"Daniel Jameson" <djameson@.childrensoncologygroup.org> wrote in message
news:%23aw9FX3BHHA.1220@.TK2MSFTNGP04.phx.gbl...
> Hi,
> How do I distinguish user objects from system objects in the sysobjects
> table (other than user tables and system tables)?
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>|||Hi,
Take a look into INFORMATION_SCHEMA views.
Thanks
Hari
"Daniel Jameson" <djameson@.childrensoncologygroup.org> wrote in message
news:%23aw9FX3BHHA.1220@.TK2MSFTNGP04.phx.gbl...
> Hi,
> How do I distinguish user objects from system objects in the sysobjects
> table (other than user tables and system tables)?
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>

Wednesday, March 7, 2012

Quick custom security question

I am trying to implement some type of security based on the user, the
report, and what parameters the are supplying.
So lets say that I have a report called "Salary Report" that shows
employees salary and bonus data. The people who can run the report
with Department = 1 are not allowed to run the report as Department = 2.
Previously I believe the answer was to use the "UserId" in my reports,
which would be populated with the current users Username, but we
already have a very complex system for determining peoples rights to
different things on our intranet site.
People can delegate rights to others, people get rights based on their
project assignments, people get rights based on their position in the
company, people get rights based on who they report to, and who reports
to them, and on and on.
So all I need to know to validate is username (or userID), what report
they are running, and what parameters they are supplying.
Is there any way to do this?Would a query based parameter work for you where you use a stored procedure
to return a list of valid parameters that they can use for that report based
on who they are and what rights they have inherrited?
Steve MunLeeuw
"cmay" <cmay@.walshgroup.com> wrote in message
news:1161293948.030847.304270@.m73g2000cwd.googlegroups.com...
>I am trying to implement some type of security based on the user, the
> report, and what parameters the are supplying.
> So lets say that I have a report called "Salary Report" that shows
> employees salary and bonus data. The people who can run the report
> with Department = 1 are not allowed to run the report as Department => 2.
> Previously I believe the answer was to use the "UserId" in my reports,
> which would be populated with the current users Username, but we
> already have a very complex system for determining peoples rights to
> different things on our intranet site.
> People can delegate rights to others, people get rights based on their
> project assignments, people get rights based on their position in the
> company, people get rights based on who they report to, and who reports
> to them, and on and on.
> So all I need to know to validate is username (or userID), what report
> they are running, and what parameters they are supplying.
> Is there any way to do this?
>

Saturday, February 25, 2012

Queue Reader ODBC Error: Login failed for user "sa"

After implementing a password for the sa account, I'm getting the error:
ODBC Error: Login failed for user "sa"
I thought that there was stored procedure where I could set the password for
the Queue Reader Agent but I can't find it. Can anyone assist me here or
have any insight into this error?
Thanks in advance.
does this help?
http://support.microsoft.com/default...&Product=sql2k
"Salty Dog" <Salty Dog@.discussions.microsoft.com> wrote in message
news:7B1EB304-FC0E-4495-AB1E-44AC6666634E@.microsoft.com...
> After implementing a password for the sa account, I'm getting the error:
> ODBC Error: Login failed for user "sa"
> I thought that there was stored procedure where I could set the password
> for
> the Queue Reader Agent but I can't find it. Can anyone assist me here or
> have any insight into this error?
> Thanks in advance.
|||This looks like the information that I need. Thank you so much and I will
get back after I apply.
"Hilary Cotter" wrote:

> does this help?
> http://support.microsoft.com/default...&Product=sql2k
> "Salty Dog" <Salty Dog@.discussions.microsoft.com> wrote in message
> news:7B1EB304-FC0E-4495-AB1E-44AC6666634E@.microsoft.com...
>
>
|||Still the same error. I've recreated the Publisher and Subscriptions several
time and still not go. I have Subscription security using sa with password
instead of NT authentication. I'm really stumped or could you tell?
I'm configured for Immediate with Queued for failover. This was working
prior to adding a password. Any other help you can give me?
Another note: Prior to a complete startover of creating the Publisher and
all, I was getting the same error. The crazy thing was that the Red Error Xs
would disappear every once in a while and processing would resume like
everything was OK for just a few seconds. It would stay down longer than
logged on/up and running.
"Salty Dog" wrote:
[vbcol=seagreen]
> This looks like the information that I need. Thank you so much and I will
> get back after I apply.
> "Hilary Cotter" wrote:
|||More info...
The same symptom of it works a bit and then fails is happening again. I
think that this is just indicating that everything else is working except
communication between the subscriber and distributor/publisher so this is
probably not relevant.
The Queue Reader logon is still not passing the password. All other agents
are running fine. Still stumped and still really appreciating your help.
"Salty Dog" wrote:

> After implementing a password for the sa account, I'm getting the error:
> ODBC Error: Login failed for user "sa"
> I thought that there was stored procedure where I could set the password for
> the Queue Reader Agent but I can't find it. Can anyone assist me here or
> have any insight into this error?
> Thanks in advance.
|||authentication is done on a exe level. Can you right click on your queue
reader agent, select agent properties, run steps, and click on the run agent
command. What is your sa password there? It should be something encrypted.
Is there no way you can use trusted authentication to bypass this problem?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Salty Dog" <SaltyDog@.discussions.microsoft.com> wrote in message
news:805E9ECC-CD66-489C-8977-0B2DF77CD4C6@.microsoft.com...
> Still the same error. I've recreated the Publisher and Subscriptions
several
> time and still not go. I have Subscription security using sa with
password
> instead of NT authentication. I'm really stumped or could you tell?
> I'm configured for Immediate with Queued for failover. This was working
> prior to adding a password. Any other help you can give me?
> Another note: Prior to a complete startover of creating the Publisher and
> all, I was getting the same error. The crazy thing was that the Red Error
Xs[vbcol=seagreen]
> would disappear every once in a while and processing would resume like
> everything was OK for just a few seconds. It would stay down longer than
> logged on/up and running.
>
> "Salty Dog" wrote:
will[vbcol=seagreen]
http://support.microsoft.com/default...&Product=sql2k[vbcol=seagreen]
error:[vbcol=seagreen]
password[vbcol=seagreen]
here or[vbcol=seagreen]

Monday, February 20, 2012

questions on SQL Profiler

I am using SQL Profiler to monitor one of my database.
The temporary trace file is stored under C:\Documents and Settings\user
\Local Settings\Temp
by default. Is it possible to re-configure it to place to another
location as the file become huge after some time.

ThanksOn Apr 17, 2:26 pm, "akkha1...@.gmail.com" <akkha1...@.gmail.comwrote:

Quote:

Originally Posted by

I am using SQL Profiler to monitor one of my database.
The temporary trace file is stored under C:\Documents and Settings\user
\Local Settings\Temp
by default. Is it possible to re-configure it to place to another
location as the file become huge after some time.
>
Thanks


Are you using SQL 2k or SQL 2k5? In SQL 2005, in the New Trace dialog,
there is a "save to file" option, where you can specify the drive to
log trace data to.

Alternatively, if you're doing the trace programmatically, you can set
a param to denote location to save trace to.

Chadd|||akkha1234@.gmail.com (akkha1234@.gmail.com) writes:

Quote:

Originally Posted by

I am using SQL Profiler to monitor one of my database.
The temporary trace file is stored under C:\Documents and Settings\user
\Local Settings\Temp
by default. Is it possible to re-configure it to place to another
location as the file become huge after some time.


You can change the TEMP and TMP environment variables in the System
applet in the Control Panel. (Under the Advanced tab.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Changing the location of trace file does not serve the purposes.
Changing the TMP is the answer and you have to log off and then login
to make it to be effective for profiler.
Thanks for both of you anyway for the prompt response. Now I know a
bit more about the windows environment.