sql bit data type: system

Showing posts with label system. Show all posts

Thursday, March 22, 2012

Book Recommendations

Hello -
My background is networking and system administration. I know basic
programming constructs, and have done .bat, VBA, and *nix shell
scripting/programming. I am looking for, at most, two books that can
help me in my new role as DBA. Most of my SQL work will be bringing
in data that our database application's GUI can't deal with. An
example is to do Inserts Where IDs are In a list.
What recommendations do you have on books that will serve my purpose?
Thanks
-tomThe Guru's guide to Transact by SQL Ken Henderson
Inside SQL server 2000 by Kalen Delaney
http://sqlservercode.blogspot.com/
"tom" wrote:

> Hello -
> My background is networking and system administration. I know basic
> programming constructs, and have done .bat, VBA, and *nix shell
> scripting/programming. I am looking for, at most, two books that can
> help me in my new role as DBA. Most of my SQL work will be bringing
> in data that our database application's GUI can't deal with. An
> example is to do Inserts Where IDs are In a list.
> What recommendations do you have on books that will serve my purpose?
> Thanks
> -tom
>|||"The Guru's guide to Transact by SQL Ken Henderson" is a great book for SQL
programmers.
I would also recommend "Sams teach yourself MS SQL Server in 21 days" for
more dba related issues.
Archer
"SQL" wrote:
> The Guru's guide to Transact by SQL Ken Henderson
> Inside SQL server 2000 by Kalen Delaney
>
> http://sqlservercode.blogspot.com/
>
> "tom" wrote:
>|||Tom,
1. Inside SQL server 2000 by Kalen Delaney -- by far the most indepth and
the best
2. Microsoft SQL Server 2000 Unleashed by Ray Rankins
or
Microsoft SQL Server 2000 Bible by Paul Nielsen
Optional 3rd - for T-SQL programming:
3. Professional SQL Server 2000 Programming by Robert Vieira
HTH
Jerry
"tom" <tomfeldsten@.hotmail.com> wrote in message
news:1127404813.829779.198790@.z14g2000cwz.googlegroups.com...
> Hello -
> My background is networking and system administration. I know basic
> programming constructs, and have done .bat, VBA, and *nix shell
> scripting/programming. I am looking for, at most, two books that can
> help me in my new role as DBA. Most of my SQL work will be bringing
> in data that our database application's GUI can't deal with. An
> example is to do Inserts Where IDs are In a list.
> What recommendations do you have on books that will serve my purpose?
> Thanks
> -tom
>|||Get a book on RDBMS basics. SQL is a declarative, high level language
which is nothing like you have seen or used before. Once you have
foundations, then get some of my books. SQL FOR SMARTIES is a classic,
but you might want to start with SQL PROGRAMMING STYLE or DATA &
DATABASES.
.|||Tom,
SQL for Smarties is a fine book. I do not have a copy of the others but I'm
sure they are good as well.
Nice job by the way Joe.
Jerry
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1127415608.320083.296190@.g43g2000cwa.googlegroups.com...
> Get a book on RDBMS basics. SQL is a declarative, high level language
> which is nothing like you have seen or used before. Once you have
> foundations, then get some of my books. SQL FOR SMARTIES is a classic,
> but you might want to start with SQL PROGRAMMING STYLE or DATA &
> DATABASES.
> .
>|||>The Guru's guide to Transact by SQL Ken Henderson
in fact, all the books by Ken Henderson|||Joe Celko highly recommends The Guru's Guide to Transact SQL by Ken Henderso
n.
I have read SQL for Smarties and it's good, I really enjoyed SQL Puzzles and
Answers.
Joe do you intend to write an updated puzzle book any time soon?
If so let us know.
http://sqlservercode.blogspot.com/
"Jerry Spivey" wrote:

> Tom,
> SQL for Smarties is a fine book. I do not have a copy of the others but I
'm
> sure they are good as well.
> Nice job by the way Joe.
> Jerry
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1127415608.320083.296190@.g43g2000cwa.googlegroups.com...
>
>|||> but you might want to start with SQL PROGRAMMING STYLE
well I have p

ed insed, thanks to Amazon, and I'm a little bit
surprised
the recommendation to use stored procedures (p. 122) and not to use
functions (p. 123). is it really consistent?
you list "portabilty problems" as reason to avoid UDFs, but for
- T-SQL (MS SQL Server)
- PL/SQL (Oracle)
- SQL-PL (DB2)
SQL UDFs are just as portatble, or not portable, as stored procedures
I don't see any major difference in portability between stored
prodeures and UDFs. Could you please explain?|||also the statement on the same that udfs are not inline seems to be not
true, sometimes they are (MS SQL Server):
create table states(state char(2))
insert into states values('IL')
insert into states values('MI')
insert into states values('WI')
create table stateNames(state char(2), sname varchar(20))
insert into stateNames values('IL', 'Illinois')
insert into stateNames values('MI', 'Michigan')
insert into stateNames values('WI', 'Wisconsin')
CREATE FUNCTION getAllStateNames()
RETURNS TABLE
AS
RETURN ( select sname from stateNames )
now look at the execution plan of this statement:
select * from states,getAllStateNames()
obviously the UDF's body is compiled into it. the plan clearly
describes access to stateNames
AFAIK in Oracle the plan would not display what is accessed inside the
UDF, but Oracle is very different

Thursday, March 8, 2012

Blocking a specific host

here's one... we have a rogue system on our network. Every 8months or so,
someone flips the "on" switch to the computer and brings it up. Aparently, it
has an application that uses a generic login (used by other applications) to
access SQL Server. The problem is, what ever file that once was there is no
longer there, so when the startup routine trys connecting to the db and
application, it gets connected to the db, but hangs trying to get connected
to the app. Our network team has no idea where the offending system is, but
obviously it's booted up every once in a blue moon.
My question is, can one block access from a Host to a SQL Server box? If
not, does anyone have ideas on how to capture the ip address or something
that could identify this rogue system by means other than User (remember,
it's a generic userid) or Host?
Thanks a ton for any idea presented!!
Andy wrote:
> here's one... we have a rogue system on our network. Every 8months or
> so, someone flips the "on" switch to the computer and brings it up.
> Aparently, it has an application that uses a generic login (used by
> other applications) to access SQL Server. The problem is, what ever
> file that once was there is no longer there, so when the startup
> routine trys connecting to the db and application, it gets connected
> to the db, but hangs trying to get connected to the app. Our network
> team has no idea where the offending system is, but obviously it's
> booted up every once in a blue moon.
> My question is, can one block access from a Host to a SQL Server box?
> If not, does anyone have ideas on how to capture the ip address or
> something that could identify this rogue system by means other than
> User (remember, it's a generic userid) or Host?
> Thanks a ton for any idea presented!!
You can get the MAC address and Host Name from the sysprocesses table in
the master database once the rogue system in connected. Your network
admin should be able to trace that back to the PC that's causing the
problem.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Blocking a specific host

here's one... we have a rogue system on our network. Every 8months or so,
someone flips the "on" switch to the computer and brings it up. Aparently, it
has an application that uses a generic login (used by other applications) to
access SQL Server. The problem is, what ever file that once was there is no
longer there, so when the startup routine trys connecting to the db and
application, it gets connected to the db, but hangs trying to get connected
to the app. Our network team has no idea where the offending system is, but
obviously it's booted up every once in a blue moon.
My question is, can one block access from a Host to a SQL Server box? If
not, does anyone have ideas on how to capture the ip address or something
that could identify this rogue system by means other than User (remember,
it's a generic userid) or Host?
Thanks a ton for any idea presented!!Andy wrote:
> here's one... we have a rogue system on our network. Every 8months or
> so, someone flips the "on" switch to the computer and brings it up.
> Aparently, it has an application that uses a generic login (used by
> other applications) to access SQL Server. The problem is, what ever
> file that once was there is no longer there, so when the startup
> routine trys connecting to the db and application, it gets connected
> to the db, but hangs trying to get connected to the app. Our network
> team has no idea where the offending system is, but obviously it's
> booted up every once in a blue moon.
> My question is, can one block access from a Host to a SQL Server box?
> If not, does anyone have ideas on how to capture the ip address or
> something that could identify this rogue system by means other than
> User (remember, it's a generic userid) or Host?
> Thanks a ton for any idea presented!!
You can get the MAC address and Host Name from the sysprocesses table in
the master database once the rogue system in connected. Your network
admin should be able to trace that back to the PC that's causing the
problem.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com

Blocking a specific host

here's one... we have a rogue system on our network. Every 8months or so,
someone flips the "on" switch to the computer and brings it up. Aparently, i
t
has an application that uses a generic login (used by other applications) to
access SQL Server. The problem is, what ever file that once was there is no
longer there, so when the startup routine trys connecting to the db and
application, it gets connected to the db, but hangs trying to get connected
to the app. Our network team has no idea where the offending system is, but
obviously it's booted up every once in a blue moon.
My question is, can one block access from a Host to a SQL Server box? If
not, does anyone have ideas on how to capture the ip address or something
that could identify this rogue system by means other than User (remember,
it's a generic userid) or Host?
Thanks a ton for any idea presented!!Andy wrote:
> here's one... we have a rogue system on our network. Every 8months or
> so, someone flips the "on" switch to the computer and brings it up.
> Aparently, it has an application that uses a generic login (used by
> other applications) to access SQL Server. The problem is, what ever
> file that once was there is no longer there, so when the startup
> routine trys connecting to the db and application, it gets connected
> to the db, but hangs trying to get connected to the app. Our network
> team has no idea where the offending system is, but obviously it's
> booted up every once in a blue moon.
> My question is, can one block access from a Host to a SQL Server box?
> If not, does anyone have ideas on how to capture the ip address or
> something that could identify this rogue system by means other than
> User (remember, it's a generic userid) or Host?
> Thanks a ton for any idea presented!!
You can get the MAC address and Host Name from the sysprocesses table in
the master database once the rogue system in connected. Your network
admin should be able to trace that back to the PC that's causing the
problem.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Wednesday, March 7, 2012

Blocked Process Command

Hi,
My system is suffering long time blocking by one process (Here for simple, I
name it Process A), I want to know what was blocked, but only saw
'sp_executesql;1' in the blocked process's (Process B) command window. And
there are lots of processes blocked by second process (Process B).
That is Porcess A is blocking Process B, and Process B is blocking many
other processes.
Please anyone help to express what could be the Process B.
Thanks in advance
FrankFrank
Run sp_who2 and take a look at BlkBy column
For more details please refer to the BOL.
"Frank" <wangping@.lucent.com> wrote in message
news:%23aThAQ6SFHA.2096@.TK2MSFTNGP14.phx.gbl...
> Hi,
> My system is suffering long time blocking by one process (Here for simple,
I
> name it Process A), I want to know what was blocked, but only saw
> 'sp_executesql;1' in the blocked process's (Process B) command window. And
> there are lots of processes blocked by second process (Process B).
> That is Porcess A is blocking Process B, and Process B is blocking many
> other processes.
> Please anyone help to express what could be the Process B.
> Thanks in advance
> Frank
>|||Thanks Uri,
I will try on this, but I still wondering why the blocked process's command
is only 'sp_executesql;1'. And also wondering why it blocking the other
processes.
B/R
Frank
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eiofb46SFHA.160@.TK2MSFTNGP10.phx.gbl...
> Frank
> Run sp_who2 and take a look at BlkBy column
> For more details please refer to the BOL.
>
> "Frank" <wangping@.lucent.com> wrote in message
> news:%23aThAQ6SFHA.2096@.TK2MSFTNGP14.phx.gbl...
simple,
> I
And
>|||Frank
http://www.sql-server-performance.com/blocking.asp
"Frank" <wangping@.lucent.com> wrote in message
news:eCcCRG7SFHA.3936@.TK2MSFTNGP15.phx.gbl...
> Thanks Uri,
> I will try on this, but I still wondering why the blocked process's
command
> is only 'sp_executesql;1'. And also wondering why it blocking the other
> processes.
> B/R
> Frank
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eiofb46SFHA.160@.TK2MSFTNGP10.phx.gbl...
> simple,
> And
many
>

block a user from executing system stored procs?

How do you block a user from executing system stored procs? Seems a user is
granted access to execute them even when they are not part of any schema or
role. The user security page says "default schema > dbo" and owned schemas
are all blank, and no role members are checked. So why can the user run
system stored procs? I need to block that but don't want to run a "deny" on
every system stored proc if I can help it.What version?
Why do you need to do this?
Some of the procedures are used to manage information returned by the
Management Studio or Enterprise Manager, so if you denied access to all
procs, the tools could stop working.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
news:45edfbd4$0$16944$4c368faf@.roadrunner.com...
> How do you block a user from executing system stored procs? Seems a user
> is granted access to execute them even when they are not part of any
> schema or role. The user security page says "default schema > dbo" and
> owned schemas are all blank, and no role members are checked. So why can
> the user run system stored procs? I need to block that but don't want to
> run a "deny" on every system stored proc if I can help it.
>|||SQL 2005
I'm trying to lock down an ASP.NET username so that if it gets compromised,
the user can't run things *like* sp_stored_procedures to see all the nice
little stored procedures he could call to do damage. That's just one
example. I was surprised when someone showed me an example of how a hacker
could learn all the stored procs and other object information in a database
this way. I can't believe this access is enabled by default and not
discussed in security practices articles.
There are hundreds of system procs, as you know, and I wouldn't know which
ones to manually DENY without a lot of research, and then a lot of
one-by-one commands. So, I want to block all the system procs. This user
does not need any Windows Client tools. It is just an ASP.NET user that
shouldn't be calling anything other than the procs used in the application
code.
Gordon
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:epgkYyEYHHA.5044@.TK2MSFTNGP05.phx.gbl...
> What version?
> Why do you need to do this?
> Some of the procedures are used to manage information returned by the
> Management Studio or Enterprise Manager, so if you denied access to all
> procs, the tools could stop working.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
> news:45edfbd4$0$16944$4c368faf@.roadrunner.com...
>> How do you block a user from executing system stored procs? Seems a user
>> is granted access to execute them even when they are not part of any
>> schema or role. The user security page says "default schema > dbo" and
>> owned schemas are all blank, and no role members are checked. So why
>> can the user run system stored procs? I need to block that but don't
>> want to run a "deny" on every system stored proc if I can help it.
>|||The object information is not available by default. All metadata is
protected.
A user might be able to run sp_help, but the only objects they could see
would be the objects they own, or the ones they were specifically granted
access to.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
news:45ee0c12$0$1398$4c368faf@.roadrunner.com...
> SQL 2005
> I'm trying to lock down an ASP.NET username so that if it gets
> compromised, the user can't run things *like* sp_stored_procedures to see
> all the nice little stored procedures he could call to do damage. That's
> just one example. I was surprised when someone showed me an example of
> how a hacker could learn all the stored procs and other object information
> in a database this way. I can't believe this access is enabled by
> default and not discussed in security practices articles.
> There are hundreds of system procs, as you know, and I wouldn't know which
> ones to manually DENY without a lot of research, and then a lot of
> one-by-one commands. So, I want to block all the system procs. This
> user does not need any Windows Client tools. It is just an ASP.NET user
> that shouldn't be calling anything other than the procs used in the
> application code.
> Gordon
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:epgkYyEYHHA.5044@.TK2MSFTNGP05.phx.gbl...
>> What version?
>> Why do you need to do this?
>> Some of the procedures are used to manage information returned by the
>> Management Studio or Enterprise Manager, so if you denied access to all
>> procs, the tools could stop working.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> http://sqlblog.com
>>
>> "K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
>> news:45edfbd4$0$16944$4c368faf@.roadrunner.com...
>> How do you block a user from executing system stored procs? Seems a
>> user is granted access to execute them even when they are not part of
>> any schema or role. The user security page says "default schema > dbo"
>> and owned schemas are all blank, and no role members are checked. So
>> why can the user run system stored procs? I need to block that but
>> don't want to run a "deny" on every system stored proc if I can help it.
>>
>|||This is not consistent with my testing. My test shows that any user can run
system stored procs.
Thus, the question remains.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:e%23ssIOGYHHA.4232@.TK2MSFTNGP05.phx.gbl...
> The object information is not available by default. All metadata is
> protected.
> A user might be able to run sp_help, but the only objects they could see
> would be the objects they own, or the ones they were specifically granted
> access to.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
> news:45ee0c12$0$1398$4c368faf@.roadrunner.com...
>> SQL 2005
>> I'm trying to lock down an ASP.NET username so that if it gets
>> compromised, the user can't run things *like* sp_stored_procedures to see
>> all the nice little stored procedures he could call to do damage. That's
>> just one example. I was surprised when someone showed me an example of
>> how a hacker could learn all the stored procs and other object
>> information in a database this way. I can't believe this access is
>> enabled by default and not discussed in security practices articles.
>> There are hundreds of system procs, as you know, and I wouldn't know
>> which ones to manually DENY without a lot of research, and then a lot of
>> one-by-one commands. So, I want to block all the system procs. This
>> user does not need any Windows Client tools. It is just an ASP.NET user
>> that shouldn't be calling anything other than the procs used in the
>> application code.
>> Gordon
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:epgkYyEYHHA.5044@.TK2MSFTNGP05.phx.gbl...
>> What version?
>> Why do you need to do this?
>> Some of the procedures are used to manage information returned by the
>> Management Studio or Enterprise Manager, so if you denied access to all
>> procs, the tools could stop working.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> http://sqlblog.com
>>
>> "K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
>> news:45edfbd4$0$16944$4c368faf@.roadrunner.com...
>> How do you block a user from executing system stored procs? Seems a
>> user is granted access to execute them even when they are not part of
>> any schema or role. The user security page says "default schema > dbo"
>> and owned schemas are all blank, and no role members are checked. So
>> why can the user run system stored procs? I need to block that but
>> don't want to run a "deny" on every system stored proc if I can help
>> it.
>>
>>
>|||> This is not consistent with my testing. My test shows that any user can run system stored procs.
> Running a proc isn't the same as seeing meta-data through the proc.
That is not what I am seeing. How many rows is returned from thelast SELECT in the first section of
above code, for instance?
USE master
CREATE LOGIN TestLogin WITH PASSWORD = 'jdhsjfdjk2'
CREATE DATABASE testdb
GO
USE testdb
CREATE TABLE dbo.t(c1 int)
CREATE USER TestLogin
GRANT EXECUTE TO TestLogin
EXECUTE AS LOGIN = 'TestLogin'
EXEC sp_tables --No "t" table
SELECT * FROM sys.tables WHERE name = 't' --0 rows
GO
--Cleanup
REVERT
USE master
DROP DATABASE testdb
DROP LOGIN TestLogin
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
news:45ef019b$0$4902$4c368faf@.roadrunner.com...
> This is not consistent with my testing. My test shows that any user can run system stored procs.
> Thus, the question remains.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:e%23ssIOGYHHA.4232@.TK2MSFTNGP05.phx.gbl...
>> The object information is not available by default. All metadata is protected.
>> A user might be able to run sp_help, but the only objects they could see would be the objects
>> they own, or the ones they were specifically granted access to.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> http://sqlblog.com
>>
>> "K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
>> news:45ee0c12$0$1398$4c368faf@.roadrunner.com...
>> SQL 2005
>> I'm trying to lock down an ASP.NET username so that if it gets compromised, the user can't run
>> things *like* sp_stored_procedures to see all the nice little stored procedures he could call to
>> do damage. That's just one example. I was surprised when someone showed me an example of how
>> a hacker could learn all the stored procs and other object information in a database this way.
>> I can't believe this access is enabled by default and not discussed in security practices
>> articles.
>> There are hundreds of system procs, as you know, and I wouldn't know which ones to manually DENY
>> without a lot of research, and then a lot of one-by-one commands. So, I want to block all the
>> system procs. This user does not need any Windows Client tools. It is just an ASP.NET user
>> that shouldn't be calling anything other than the procs used in the application code.
>> Gordon
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:epgkYyEYHHA.5044@.TK2MSFTNGP05.phx.gbl...
>> What version?
>> Why do you need to do this?
>> Some of the procedures are used to manage information returned by the Management Studio or
>> Enterprise Manager, so if you denied access to all procs, the tools could stop working.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> http://sqlblog.com
>>
>> "K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
>> news:45edfbd4$0$16944$4c368faf@.roadrunner.com...
>> How do you block a user from executing system stored procs? Seems a user is granted access to
>> execute them even when they are not part of any schema or role. The user security page says
>> "default schema > dbo" and owned schemas are all blank, and no role members are checked. So
>> why can the user run system stored procs? I need to block that but don't want to run a "deny"
>> on every system stored proc if I can help it.
>>
>>
>>
>|||Yes, I said that. I said a user can run sp_help, but the only objects
returned will be ones that the user has a right to see.
So unprivileged users can run sp_help and get an empty set back. Why do you
need to prevent that?
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
news:45ef019b$0$4902$4c368faf@.roadrunner.com...
> This is not consistent with my testing. My test shows that any user can
> run system stored procs.
> Thus, the question remains.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:e%23ssIOGYHHA.4232@.TK2MSFTNGP05.phx.gbl...
>> The object information is not available by default. All metadata is
>> protected.
>> A user might be able to run sp_help, but the only objects they could see
>> would be the objects they own, or the ones they were specifically granted
>> access to.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> http://sqlblog.com
>>
>> "K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
>> news:45ee0c12$0$1398$4c368faf@.roadrunner.com...
>> SQL 2005
>> I'm trying to lock down an ASP.NET username so that if it gets
>> compromised, the user can't run things *like* sp_stored_procedures to
>> see all the nice little stored procedures he could call to do damage.
>> That's just one example. I was surprised when someone showed me an
>> example of how a hacker could learn all the stored procs and other
>> object information in a database this way. I can't believe this access
>> is enabled by default and not discussed in security practices articles.
>> There are hundreds of system procs, as you know, and I wouldn't know
>> which ones to manually DENY without a lot of research, and then a lot of
>> one-by-one commands. So, I want to block all the system procs. This
>> user does not need any Windows Client tools. It is just an ASP.NET user
>> that shouldn't be calling anything other than the procs used in the
>> application code.
>> Gordon
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:epgkYyEYHHA.5044@.TK2MSFTNGP05.phx.gbl...
>> What version?
>> Why do you need to do this?
>> Some of the procedures are used to manage information returned by the
>> Management Studio or Enterprise Manager, so if you denied access to all
>> procs, the tools could stop working.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> http://sqlblog.com
>>
>> "K. Abit" <sendtothegrouponly@.this-is-not-real.com> wrote in message
>> news:45edfbd4$0$16944$4c368faf@.roadrunner.com...
>> How do you block a user from executing system stored procs? Seems a
>> user is granted access to execute them even when they are not part of
>> any schema or role. The user security page says "default schema >
>> dbo" and owned schemas are all blank, and no role members are checked.
>> So why can the user run system stored procs? I need to block that but
>> don't want to run a "deny" on every system stored proc if I can help
>> it.
>>
>>
>>
>

block a user from executing system stored procs?

Saturday, February 25, 2012

Blobs: File System or DB?

The conventional wisdom on where best to store large binary objects used to
be to store them on the file system because they were slowly processed in
the database. Is this still the case with the latest SQL Server?
We are designing a new system that will store many thousands of TIF files.
We would like to store them in the database because they would then be
coordinated with Backup, Restore, and Transaction issues. How much slower
is the processing of varbinary fields in the db versus the file system?
Thanks,
T"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:Oy9qbdKSIHA.6060@.TK2MSFTNGP05.phx.gbl...
> The conventional wisdom on where best to store large binary objects used
> to be to store them on the file system because they were slowly processed
> in the database. Is this still the case with the latest SQL Server?
> We are designing a new system that will store many thousands of TIF files.
> We would like to store them in the database because they would then be
> coordinated with Backup, Restore, and Transaction issues. How much slower
> is the processing of varbinary fields in the db versus the file system?
> Thanks,
> T
>
The following article discusses file system storage vs SQL Server 2005
BLOBs:
ftp://ftp.research.microsoft.com/pub/tr/TR-2006-45.pdf
In SQL Server 2008 there is a new feature called FILESTREAM that directly
addresses the problem. FILESTREAM permits both SQL and file-system access to
binary data while managing the files as if they were part of the database
for backup and transaction control purposes. From my own experience I have
found that FILESTREAM can outperform file-system access for files larger
than about 2MB (YMMV).
http://msdn2.microsoft.com/en-us/library/bb895234(SQL.100).aspx
David Portas|||> The conventional wisdom on where best to store large binary objects used
> to be to store them on the file system because they were slowly processed
> in the database. Is this still the case with the latest SQL Server?
If you mean SQL Server 2005, then yes; if you mean SQL Server 2008, then
no... as David pointed out, FILESTREAM will address part of this problem
(but I am a little skeptical about performance of backups, concurrency, and
translation layer).

> We are designing a new system that will store many thousands of TIF files.
> We would like to store them in the database because they would then be
> coordinated with Backup, Restore, and Transaction issues. How much slower
> is the processing of varbinary fields in the db versus the file system?
Naturally I would expect a layer of overhead, depending on the number and
size of files you have, in translating the data back and forth between the
database and the application(s).
But, more importantly, and again depending on the number and size of files
you have, I am very sensitive to using expensive disk. I would be very
careful to place the files in a filegroup which is not on a very expensive
SAN (which your database might be using). High performance, fault-tolerant
disk space is very expensive, and it seems a waste to take this space away
from your database to use for, essentially, flat files. If you are using
locally attached storage then this isn't really an issue, but if the file
usage is going to be large, you might consider using a less expensive array
for the files themselves. (You can always keep backups of directory
structures as well, it;'s just very difficult to keep them point-in-time in
sync with the database.) Also keep this in mind if you use a 3rd party for
database hosting; they typically charge a lot more per TB of database
storage, versus flat file storage, so in that case make sure you weigh the
benefits you get from synchronizing your files and data, versus the hit it
takes on your budget.
A

Blobs: File System or DB?

The conventional wisdom on where best to store large binary objects used to
be to store them on the file system because they were slowly processed in
the database. Is this still the case with the latest SQL Server?
We are designing a new system that will store many thousands of TIF files.
We would like to store them in the database because they would then be
coordinated with Backup, Restore, and Transaction issues. How much slower
is the processing of varbinary fields in the db versus the file system?
Thanks,
T
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:Oy9qbdKSIHA.6060@.TK2MSFTNGP05.phx.gbl...
> The conventional wisdom on where best to store large binary objects used
> to be to store them on the file system because they were slowly processed
> in the database. Is this still the case with the latest SQL Server?
> We are designing a new system that will store many thousands of TIF files.
> We would like to store them in the database because they would then be
> coordinated with Backup, Restore, and Transaction issues. How much slower
> is the processing of varbinary fields in the db versus the file system?
> Thanks,
> T
>
The following article discusses file system storage vs SQL Server 2005
BLOBs:
ftp://ftp.research.microsoft.com/pub/tr/TR-2006-45.pdf
In SQL Server 2008 there is a new feature called FILESTREAM that directly
addresses the problem. FILESTREAM permits both SQL and file-system access to
binary data while managing the files as if they were part of the database
for backup and transaction control purposes. From my own experience I have
found that FILESTREAM can outperform file-system access for files larger
than about 2MB (YMMV).
http://msdn2.microsoft.com/en-us/library/bb895234(SQL.100).aspx
David Portas
|||> The conventional wisdom on where best to store large binary objects used
> to be to store them on the file system because they were slowly processed
> in the database. Is this still the case with the latest SQL Server?
If you mean SQL Server 2005, then yes; if you mean SQL Server 2008, then
no... as David pointed out, FILESTREAM will address part of this problem
(but I am a little skeptical about performance of backups, concurrency, and
translation layer).

> We are designing a new system that will store many thousands of TIF files.
> We would like to store them in the database because they would then be
> coordinated with Backup, Restore, and Transaction issues. How much slower
> is the processing of varbinary fields in the db versus the file system?
Naturally I would expect a layer of overhead, depending on the number and
size of files you have, in translating the data back and forth between the
database and the application(s).
But, more importantly, and again depending on the number and size of files
you have, I am very sensitive to using expensive disk. I would be very
careful to place the files in a filegroup which is not on a very expensive
SAN (which your database might be using). High performance, fault-tolerant
disk space is very expensive, and it seems a waste to take this space away
from your database to use for, essentially, flat files. If you are using
locally attached storage then this isn't really an issue, but if the file
usage is going to be large, you might consider using a less expensive array
for the files themselves. (You can always keep backups of directory
structures as well, it;'s just very difficult to keep them point-in-time in
sync with the database.) Also keep this in mind if you use a 3rd party for
database hosting; they typically charge a lot more per TB of database
storage, versus flat file storage, so in that case make sure you weigh the
benefits you get from synchronizing your files and data, versus the hit it
takes on your budget.
A

Blobs: File System or DB?

The conventional wisdom on where best to store large binary objects used to
be to store them on the file system because they were slowly processed in
the database. Is this still the case with the latest SQL Server?
We are designing a new system that will store many thousands of TIF files.
We would like to store them in the database because they would then be
coordinated with Backup, Restore, and Transaction issues. How much slower
is the processing of varbinary fields in the db versus the file system?
Thanks,
T"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:Oy9qbdKSIHA.6060@.TK2MSFTNGP05.phx.gbl...
> The conventional wisdom on where best to store large binary objects used
> to be to store them on the file system because they were slowly processed
> in the database. Is this still the case with the latest SQL Server?
> We are designing a new system that will store many thousands of TIF files.
> We would like to store them in the database because they would then be
> coordinated with Backup, Restore, and Transaction issues. How much slower
> is the processing of varbinary fields in the db versus the file system?
> Thanks,
> T
>
The following article discusses file system storage vs SQL Server 2005
BLOBs:
ftp://ftp.research.microsoft.com/pub/tr/TR-2006-45.pdf
In SQL Server 2008 there is a new feature called FILESTREAM that directly
addresses the problem. FILESTREAM permits both SQL and file-system access to
binary data while managing the files as if they were part of the database
for backup and transaction control purposes. From my own experience I have
found that FILESTREAM can outperform file-system access for files larger
than about 2MB (YMMV).
http://msdn2.microsoft.com/en-us/library/bb895234(SQL.100).aspx
--
David Portas|||> The conventional wisdom on where best to store large binary objects used
> to be to store them on the file system because they were slowly processed
> in the database. Is this still the case with the latest SQL Server?
If you mean SQL Server 2005, then yes; if you mean SQL Server 2008, then
no... as David pointed out, FILESTREAM will address part of this problem
(but I am a little skeptical about performance of backups, concurrency, and
translation layer).
> We are designing a new system that will store many thousands of TIF files.
> We would like to store them in the database because they would then be
> coordinated with Backup, Restore, and Transaction issues. How much slower
> is the processing of varbinary fields in the db versus the file system?
Naturally I would expect a layer of overhead, depending on the number and
size of files you have, in translating the data back and forth between the
database and the application(s).
But, more importantly, and again depending on the number and size of files
you have, I am very sensitive to using expensive disk. I would be very
careful to place the files in a filegroup which is not on a very expensive
SAN (which your database might be using). High performance, fault-tolerant
disk space is very expensive, and it seems a waste to take this space away
from your database to use for, essentially, flat files. If you are using
locally attached storage then this isn't really an issue, but if the file
usage is going to be large, you might consider using a less expensive array
for the files themselves. (You can always keep backups of directory
structures as well, it;'s just very difficult to keep them point-in-time in
sync with the database.) Also keep this in mind if you use a 3rd party for
database hosting; they typically charge a lot more per TB of database
storage, versus flat file storage, so in that case make sure you weigh the
benefits you get from synchronizing your files and data, versus the hit it
takes on your budget.
A

Tuesday, February 14, 2012

Black Reports in Print Layout

Hello all,
We have an issue with reports we created using the VS 2005 Team System. When
we open a report everything looks fine and report prints fine as well.
The problem is that when we click the Print Layout button on the report we
get a black page and only report title and group headers are visible. Mind
you, the report still prints fine on the printer but none of the data is
visible in the report.
Any ideas and solutions appreciated.
BR,
GAZOn Dec 24, 8:33 am, "GAZ" <g...@.gaz.com> wrote:
> Hello all,
> We have an issue with reports we created using the VS 2005 Team System. When
> we open a report everything looks fine and report prints fine as well.
> The problem is that when we click the Print Layout button on the report we
> get a black page and only report title and group headers are visible. Mind
> you, the report still prints fine on the printer but none of the data is
> visible in the report.
> Any ideas and solutions appreciated.
> BR,
> GAZ
This should help.
http://support.microsoft.com/kb/935356/en-us
Regards,
Enrique Martinez
Sr. Software Consultant|||"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:d2fc2115-efce-4d3e-9ee2-4965bd60ec77@.f52g2000hsa.googlegroups.com...
> On Dec 24, 8:33 am, "GAZ" <g...@.gaz.com> wrote:
>> Hello all,
>> We have an issue with reports we created using the VS 2005 Team System.
>> When
>> we open a report everything looks fine and report prints fine as well.
>> The problem is that when we click the Print Layout button on the report
>> we
>> get a black page and only report title and group headers are visible.
>> Mind
>> you, the report still prints fine on the printer but none of the data is
>> visible in the report.
>> Any ideas and solutions appreciated.
>> BR,
>> GAZ
>
> This should help.
> http://support.microsoft.com/kb/935356/en-us
> Regards,
> Enrique Martinez
> Sr. Software Consultant
Thanks for the info. Got it but it won't install. Every single hotfix
reports a failure to install. Has anyone else had any problems with the
installation of the said hotfix?
BR,
GAZ|||On Dec 25, 3:41 am, "GAZ" <g...@.gaz.com> wrote:
> "EMartinez" <emartinez...@.gmail.com> wrote in message
> news:d2fc2115-efce-4d3e-9ee2-4965bd60ec77@.f52g2000hsa.googlegroups.com...
>
> > On Dec 24, 8:33 am, "GAZ" <g...@.gaz.com> wrote:
> >> Hello all,
> >> We have an issue with reports we created using the VS 2005 Team System.
> >> When
> >> we open a report everything looks fine and report prints fine as well.
> >> The problem is that when we click the Print Layout button on the report
> >> we
> >> get a black page and only report title and group headers are visible.
> >> Mind
> >> you, the report still prints fine on the printer but none of the data is
> >> visible in the report.
> >> Any ideas and solutions appreciated.
> >> BR,
> >> GAZ
> > This should help.
> >http://support.microsoft.com/kb/935356/en-us
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
> Thanks for the info. Got it but it won't install. Every single hotfix
> reports a failure to install. Has anyone else had any problems with the
> installation of the said hotfix?
> BR,
> GAZ
You're welcome. You may have to install the Hot fixes in a particular
order. Have you tried verifying the order in which the hot fixes must
be installed? Hope this helps further.
Regards,
Enrique Martinez
Sr. Software Consultant|||"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:3d431f4b-bd40-49c7-bb5f-c0f2b2c4b64e@.21g2000hsj.googlegroups.com...
> On Dec 25, 3:41 am, "GAZ" <g...@.gaz.com> wrote:
>> "EMartinez" <emartinez...@.gmail.com> wrote in message
>> news:d2fc2115-efce-4d3e-9ee2-4965bd60ec77@.f52g2000hsa.googlegroups.com...
>>
>> > On Dec 24, 8:33 am, "GAZ" <g...@.gaz.com> wrote:
>> >> Hello all,
>> >> We have an issue with reports we created using the VS 2005 Team
>> >> System.
>> >> When
>> >> we open a report everything looks fine and report prints fine as well.
>> >> The problem is that when we click the Print Layout button on the
>> >> report
>> >> we
>> >> get a black page and only report title and group headers are visible.
>> >> Mind
>> >> you, the report still prints fine on the printer but none of the data
>> >> is
>> >> visible in the report.
>> >> Any ideas and solutions appreciated.
>> >> BR,
>> >> GAZ
>> > This should help.
>> >http://support.microsoft.com/kb/935356/en-us
>> > Regards,
>> > Enrique Martinez
>> > Sr. Software Consultant
>> Thanks for the info. Got it but it won't install. Every single hotfix
>> reports a failure to install. Has anyone else had any problems with the
>> installation of the said hotfix?
>> BR,
>> GAZ
>
> You're welcome. You may have to install the Hot fixes in a particular
> order. Have you tried verifying the order in which the hot fixes must
> be installed? Hope this helps further.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
Thanks for your answer.
The hotfixes are acutally rolled into one package which does not give you an
option to choose a particular order for installation. When we start the
installation it always asks for the SQL installation disk and then it reads
files from it and reports that the hotfix installation failed. And so on for
each of the components.
Well, I guess that we'll have to wait for the SP3 to have the issue
resolved.
Thanks again for your help.
BR,
GAZ|||On Dec 26, 1:58 am, "GAZ" <g...@.gaz.com> wrote:
> "EMartinez" <emartinez...@.gmail.com> wrote in message
> news:3d431f4b-bd40-49c7-bb5f-c0f2b2c4b64e@.21g2000hsj.googlegroups.com...
>
> > On Dec 25, 3:41 am, "GAZ" <g...@.gaz.com> wrote:
> >> "EMartinez" <emartinez...@.gmail.com> wrote in message
> >>news:d2fc2115-efce-4d3e-9ee2-4965bd60ec77@.f52g2000hsa.googlegroups.com...
> >> > On Dec 24, 8:33 am, "GAZ" <g...@.gaz.com> wrote:
> >> >> Hello all,
> >> >> We have an issue with reports we created using the VS 2005 Team
> >> >> System.
> >> >> When
> >> >> we open a report everything looks fine and report prints fine as well.
> >> >> The problem is that when we click the Print Layout button on the
> >> >> report
> >> >> we
> >> >> get a black page and only report title and group headers are visible.
> >> >> Mind
> >> >> you, the report still prints fine on the printer but none of the data
> >> >> is
> >> >> visible in the report.
> >> >> Any ideas and solutions appreciated.
> >> >> BR,
> >> >> GAZ
> >> > This should help.
> >> >http://support.microsoft.com/kb/935356/en-us
> >> > Regards,
> >> > Enrique Martinez
> >> > Sr. Software Consultant
> >> Thanks for the info. Got it but it won't install. Every single hotfix
> >> reports a failure to install. Has anyone else had any problems with the
> >> installation of the said hotfix?
> >> BR,
> >> GAZ
> > You're welcome. You may have to install the Hot fixes in a particular
> > order. Have you tried verifying the order in which the hot fixes must
> > be installed? Hope this helps further.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
> Thanks for your answer.
> The hotfixes are acutally rolled into one package which does not give you an
> option to choose a particular order for installation. When we start the
> installation it always asks for the SQL installation disk and then it reads
> files from it and reports that the hotfix installation failed. And so on for
> each of the components.
> Well, I guess that we'll have to wait for the SP3 to have the issue
> resolved.
> Thanks again for your help.
> BR,
> GAZ
You're welcome. Have you checked the Event Viewer for errors that
might be logged during the process? That might provide some better
details. Hope this helps further.
Regards,
Enrique Martinez
Sr. Software Consultant|||"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:7c2d5ac2-a280-4fe0-9a51-a53e315ea7dd@.1g2000hsl.googlegroups.com...
> On Dec 26, 1:58 am, "GAZ" <g...@.gaz.com> wrote:
>> "EMartinez" <emartinez...@.gmail.com> wrote in message
>> news:3d431f4b-bd40-49c7-bb5f-c0f2b2c4b64e@.21g2000hsj.googlegroups.com...
>>
>> > On Dec 25, 3:41 am, "GAZ" <g...@.gaz.com> wrote:
>> >> "EMartinez" <emartinez...@.gmail.com> wrote in message
>> >>news:d2fc2115-efce-4d3e-9ee2-4965bd60ec77@.f52g2000hsa.googlegroups.com...
>> >> > On Dec 24, 8:33 am, "GAZ" <g...@.gaz.com> wrote:
>> >> >> Hello all,
>> >> >> We have an issue with reports we created using the VS 2005 Team
>> >> >> System.
>> >> >> When
>> >> >> we open a report everything looks fine and report prints fine as
>> >> >> well.
>> >> >> The problem is that when we click the Print Layout button on the
>> >> >> report
>> >> >> we
>> >> >> get a black page and only report title and group headers are
>> >> >> visible.
>> >> >> Mind
>> >> >> you, the report still prints fine on the printer but none of the
>> >> >> data
>> >> >> is
>> >> >> visible in the report.
>> >> >> Any ideas and solutions appreciated.
>> >> >> BR,
>> >> >> GAZ
>> >> > This should help.
>> >> >http://support.microsoft.com/kb/935356/en-us
>> >> > Regards,
>> >> > Enrique Martinez
>> >> > Sr. Software Consultant
>> >> Thanks for the info. Got it but it won't install. Every single hotfix
>> >> reports a failure to install. Has anyone else had any problems with
>> >> the
>> >> installation of the said hotfix?
>> >> BR,
>> >> GAZ
>> > You're welcome. You may have to install the Hot fixes in a particular
>> > order. Have you tried verifying the order in which the hot fixes must
>> > be installed? Hope this helps further.
>> > Regards,
>> > Enrique Martinez
>> > Sr. Software Consultant
>> Thanks for your answer.
>> The hotfixes are acutally rolled into one package which does not give you
>> an
>> option to choose a particular order for installation. When we start the
>> installation it always asks for the SQL installation disk and then it
>> reads
>> files from it and reports that the hotfix installation failed. And so on
>> for
>> each of the components.
>> Well, I guess that we'll have to wait for the SP3 to have the issue
>> resolved.
>> Thanks again for your help.
>> BR,
>> GAZ
>
> You're welcome. Have you checked the Event Viewer for errors that
> might be logged during the process? That might provide some better
> details. Hope this helps further.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
Thanks for your help. Unfortunatelly the Event Log is of no help as it does
not provide any useful information. The logs created by the installation
process say that the hotfix package could not be found but i have no idea
what to do with that information as the package physically exists on the
hard drive where the setup process extracted it.
Well, I can only wait for the next SP i guess.
BR,
GAZ|||On Dec 27, 12:06 pm, "GAZ" <g...@.gaz.com> wrote:
> "EMartinez" <emartinez...@.gmail.com> wrote in message
> news:7c2d5ac2-a280-4fe0-9a51-a53e315ea7dd@.1g2000hsl.googlegroups.com...
>
> > On Dec 26, 1:58 am, "GAZ" <g...@.gaz.com> wrote:
> >> "EMartinez" <emartinez...@.gmail.com> wrote in message
> >>news:3d431f4b-bd40-49c7-bb5f-c0f2b2c4b64e@.21g2000hsj.googlegroups.com...
> >> > On Dec 25, 3:41 am, "GAZ" <g...@.gaz.com> wrote:
> >> >> "EMartinez" <emartinez...@.gmail.com> wrote in message
> >> >>news:d2fc2115-efce-4d3e-9ee2-4965bd60ec77@.f52g2000hsa.googlegroups.com...
> >> >> > On Dec 24, 8:33 am, "GAZ" <g...@.gaz.com> wrote:
> >> >> >> Hello all,
> >> >> >> We have an issue with reports we created using the VS 2005 Team
> >> >> >> System.
> >> >> >> When
> >> >> >> we open a report everything looks fine and report prints fine as
> >> >> >> well.
> >> >> >> The problem is that when we click the Print Layout button on the
> >> >> >> report
> >> >> >> we
> >> >> >> get a black page and only report title and group headers are
> >> >> >> visible.
> >> >> >> Mind
> >> >> >> you, the report still prints fine on the printer but none of the
> >> >> >> data
> >> >> >> is
> >> >> >> visible in the report.
> >> >> >> Any ideas and solutions appreciated.
> >> >> >> BR,
> >> >> >> GAZ
> >> >> > This should help.
> >> >> >http://support.microsoft.com/kb/935356/en-us
> >> >> > Regards,
> >> >> > Enrique Martinez
> >> >> > Sr. Software Consultant
> >> >> Thanks for the info. Got it but it won't install. Every single hotfix
> >> >> reports a failure to install. Has anyone else had any problems with
> >> >> the
> >> >> installation of the said hotfix?
> >> >> BR,
> >> >> GAZ
> >> > You're welcome. You may have to install the Hot fixes in a particular
> >> > order. Have you tried verifying the order in which the hot fixes must
> >> > be installed? Hope this helps further.
> >> > Regards,
> >> > Enrique Martinez
> >> > Sr. Software Consultant
> >> Thanks for your answer.
> >> The hotfixes are acutally rolled into one package which does not give you
> >> an
> >> option to choose a particular order for installation. When we start the
> >> installation it always asks for the SQL installation disk and then it
> >> reads
> >> files from it and reports that the hotfix installation failed. And so on
> >> for
> >> each of the components.
> >> Well, I guess that we'll have to wait for the SP3 to have the issue
> >> resolved.
> >> Thanks again for your help.
> >> BR,
> >> GAZ
> > You're welcome. Have you checked the Event Viewer for errors that
> > might be logged during the process? That might provide some better
> > details. Hope this helps further.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
> Thanks for your help. Unfortunatelly the Event Log is of no help as it does
> not provide any useful information. The logs created by the installation
> process say that the hotfix package could not be found but i have no idea
> what to do with that information as the package physically exists on the
> hard drive where the setup process extracted it.
> Well, I can only wait for the next SP i guess.
> BR,
> GAZ
You're welcome. Do you have SQL Server and/or Reporting Services
installed in a non-standard location (i.e., on a D or E partition)? If
that is the case, this could be the problem.
Regards,
Enrique Martinez
Sr. Software Consultant

Bkp Restore Failed

I am trying to restore backup and the system gives me error for LSN. No error no. is displayed but it seems that the system doesnot accept trn log restore.

My bkp strategy is:

Full Backup: weekly

Diff bkp: daily

T Log Bkp : every 2 hours.

But sometime I need to shrink Log file due to heavy growth. Hope that is not creating problem. The following is what i do for shrinking TLog file.

BACKUP LOG ABC TO ABC_BackUp

DBCC SHRINKFILE(ABC_Log, 500)

pL. guide why my restore fails.

you are right on the track... THe shrinking is creating the problem. shrinking log file step will invalidate the transaction log backup chain. So what you should have done after shrinking was take fullbackup of your database. If the transaction log size is the problem, then increase the frequency of the TL Backup. Now you have every 2 hourse, keep it every 15 min or so... it will control the size of the log and then there will be no need to shrink. If you are shrinking then the first step after the shrinking of log should be Full backup remember that.

Read follwing links

FileSize -How to stop the log file from growing
http://www.support.microsoft.com/?id=873235

FileSize -Log file filling up
http://www.support.microsoft.com/?id=110139

FileSize -Log File Grows too big
http://www.support.microsoft.com/?id=317375

FileSize -Log File issues
http://www.nigelrivett.net/TransactionLogFileGrows_1.html

FileSize -Shrinking Log in SQL Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=272318

Madhu|||

Thanks. I tried restoring full bkp, diff bkp and trn bkp which is without any shrinkage and i get the same error.

ERROR:

The log in this backup set begins at LNS .....502600001, which is too recent to apply to the database. An earlier log backup
that includes LSN .....478700001 can be restored.

what is this error and why my restore fails.

|||I would recommend that you do a RESTORE HEADERONLY on all you backups and check the FirstLSN and LastLSN fields of your backup series. The values should not have any gaps. If there are, the backup would be invalid...more of like a "do 5 first after 4 and not 6" kind of thing is how I would explain LSNs|||

Thanks. When i try to exclude filed with gaps, i get the error:

System.Data.sqlclient.sqlerror: Invalid column name 'lightweight'

Following is the bkp procedure i follow;

Full bkp-Weekly
BACKUP DATABASE [abc] TO [abc_Backup] WITH INIT
RESTORE VERIFYONLY
FROM DISK = 'C:\Database Backup\User Databases\abc\abc_BackUp.BAK'

Diff bkp-Daily
BACKUP DATABASE [abc] TO [abc_BackUp] WITH DIFFERENTIAL;

Tlog bkp-Every 2 hr
BACKUP LOG [abc] TO [abc_BackUp];

Pl. guide

|||

Thanks. When i see the LSN of my TLog bkp, the last 6 doenot match and hence i tried to restore the first files Full bkp - TLog - TLog - Diff bkp.

This gave me error:

Msg 207, Level 16, State 1, Procedure sp_MSremovedbreplication, Line 242

Invalid column name 'lightweight'.

Msg 3165, Level 16, State 1, Line 1

Database 'ABC' was restored, however an error was encountered while replication was being restored/removed. The database has been left offline. See the topic MSSQL_ENG003165 in SQL Server Books Online.

Msg 3167, Level 16, State 1, Line 1

RESTORE could not start database 'ABC'.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

Que1 why my LSN of TLogbkps are not matching?

Que2 why i got the error as above?

Following steps i follow to take bkps;

Full bkp-Weekly
BACKUP DATABASE [abc] TO [abc_Backup] WITH INIT
RESTORE VERIFYONLY
FROM DISK = 'C:\Database Backup\User Databases\abc\abc_BackUp.BAK'

Diff bkp-Daily
BACKUP DATABASE [abc] TO [abc_BackUp] WITH DIFFERENTIAL;

Tlog bkp-Every 2 hr
BACKUP LOG [abc] TO [abc_BackUp];

Pls. guide. Thanks in advance

|||

Thanks. When i see the LSN of my TLog bkp, the last 6 doenot match and hence i tried to restore the first files Full bkp - TLog - TLog - Diff bkp.

This gave me error:

Msg 207, Level 16, State 1, Procedure sp_MSremovedbreplication, Line 242

Invalid column name 'lightweight'.

Msg 3165, Level 16, State 1, Line 1

Msg 3167, Level 16, State 1, Line 1

RESTORE could not start database 'ABC'.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

Que1 why my LSN of TLogbkps are not matching?

Que2 why i got the error as above?

Following steps i follow to take bkps;

Full bkp-Weekly
BACKUP DATABASE [abc] TO [abc_Backup] WITH INIT
RESTORE VERIFYONLY
FROM DISK = 'C:\Database Backup\User Databases\abc\abc_BackUp.BAK'

Diff bkp-Daily
BACKUP DATABASE [abc] TO [abc_BackUp] WITH DIFFERENTIAL;

Tlog bkp-Every 2 hr
BACKUP LOG [abc] TO [abc_BackUp];

Pls. guide. Thanks in advance

Bkp Restore Failed

I am trying to restore backup and the system gives me error for LSN. No error no. is displayed but it seems that the system doesnot accept trn log restore.

My bkp strategy is:

Full Backup: weekly

Diff bkp: daily

T Log Bkp : every 2 hours.

But sometime I need to shrink Log file due to heavy growth. Hope that is not creating problem. The following is what i do for shrinking TLog file.

BACKUP LOG ABC TO ABC_BackUp

DBCC SHRINKFILE(ABC_Log, 500)

pL. guide why my restore fails.

Read follwing links

FileSize -How to stop the log file from growing
http://www.support.microsoft.com/?id=873235

FileSize -Log file filling up
http://www.support.microsoft.com/?id=110139

FileSize -Log File Grows too big
http://www.support.microsoft.com/?id=317375

FileSize -Log File issues
http://www.nigelrivett.net/TransactionLogFileGrows_1.html

FileSize -Shrinking Log in SQL Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=272318

Madhu|||

Thanks. I tried restoring full bkp, diff bkp and trn bkp which is without any shrinkage and i get the same error.

ERROR:

The log in this backup set begins at LNS .....502600001, which is too recent to apply to the database. An earlier log backup
that includes LSN .....478700001 can be restored.

what is this error and why my restore fails.

Thanks. When i try to exclude filed with gaps, i get the error:

System.Data.sqlclient.sqlerror: Invalid column name 'lightweight'

Following is the bkp procedure i follow;

Full bkp-Weekly
BACKUP DATABASE [abc] TO [abc_Backup] WITH INIT
RESTORE VERIFYONLY
FROM DISK = 'C:\Database Backup\User Databases\abc\abc_BackUp.BAK'

Diff bkp-Daily
BACKUP DATABASE [abc] TO [abc_BackUp] WITH DIFFERENTIAL;

Tlog bkp-Every 2 hr
BACKUP LOG [abc] TO [abc_BackUp];

Pl. guide

|||

Thanks. When i see the LSN of my TLog bkp, the last 6 doenot match and hence i tried to restore the first files Full bkp - TLog - TLog - Diff bkp.

This gave me error:

Msg 207, Level 16, State 1, Procedure sp_MSremovedbreplication, Line 242

Invalid column name 'lightweight'.

Msg 3165, Level 16, State 1, Line 1

Msg 3167, Level 16, State 1, Line 1

RESTORE could not start database 'ABC'.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

Que1 why my LSN of TLogbkps are not matching?

Que2 why i got the error as above?

Following steps i follow to take bkps;

Full bkp-Weekly
BACKUP DATABASE [abc] TO [abc_Backup] WITH INIT
RESTORE VERIFYONLY
FROM DISK = 'C:\Database Backup\User Databases\abc\abc_BackUp.BAK'

Diff bkp-Daily
BACKUP DATABASE [abc] TO [abc_BackUp] WITH DIFFERENTIAL;

Tlog bkp-Every 2 hr
BACKUP LOG [abc] TO [abc_BackUp];

Pls. guide. Thanks in advance

|||

Thanks. When i see the LSN of my TLog bkp, the last 6 doenot match and hence i tried to restore the first files Full bkp - TLog - TLog - Diff bkp.

This gave me error:

Msg 207, Level 16, State 1, Procedure sp_MSremovedbreplication, Line 242

Invalid column name 'lightweight'.

Msg 3165, Level 16, State 1, Line 1

Msg 3167, Level 16, State 1, Line 1

RESTORE could not start database 'ABC'.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

Que1 why my LSN of TLogbkps are not matching?

Que2 why i got the error as above?

Following steps i follow to take bkps;

Full bkp-Weekly
BACKUP DATABASE [abc] TO [abc_Backup] WITH INIT
RESTORE VERIFYONLY
FROM DISK = 'C:\Database Backup\User Databases\abc\abc_BackUp.BAK'

Diff bkp-Daily
BACKUP DATABASE [abc] TO [abc_BackUp] WITH DIFFERENTIAL;

Tlog bkp-Every 2 hr
BACKUP LOG [abc] TO [abc_BackUp];

Pls. guide. Thanks in advance

Sunday, February 12, 2012

Bitwise or aggregate function

Hi,
I understand there are no aggregate function for ORing multiple row columns
together. We have a access control system that looks like
CREATE TABLE AccessTable
(
UserID int NOT NULL PRIMARY KEY,
EntityID int NOT NULL PRIMARY KEY,
AccessMask int NOT NULL
)
where AccessMask contains an int with values ORed together 1, 2, 4, 8, 16
etc. We would like to be able to do something like
select AGGR_ORSUM(AccessMask) As AggSum
from AccessTable
where EntityID = @.EntityID
I've seen some samples of a two table solution but I just wanted to double
check if someone has a better solution. Running SQL Server 2005.
Thanks,
MansoManso wrote:
> Hi,
> I understand there are no aggregate function for ORing multiple row column
s
> together. We have a access control system that looks like
> CREATE TABLE AccessTable
> (
> UserID int NOT NULL PRIMARY KEY,
> EntityID int NOT NULL PRIMARY KEY,
> AccessMask int NOT NULL
> )
> where AccessMask contains an int with values ORed together 1, 2, 4, 8, 16
> etc. We would like to be able to do something like
> select AGGR_ORSUM(AccessMask) As AggSum
> from AccessTable
> where EntityID = @.EntityID
> I've seen some samples of a two table solution but I just wanted to double
> check if someone has a better solution. Running SQL Server 2005.
> Thanks,
> Manso
Why would you store this data as a bitmap in the first place? Try:
SELECT
MAX(AccessMask & 1)+
MAX(AccessMask & 2)+
MAX(AccessMask & 4)+
MAX(AccessMask & 8)+
MAX(AccessMask & 16)+
MAX(AccessMask & 32)+
MAX(AccessMask & 64)+
MAX(AccessMask & 128) AS aggr_orsum
FROM AccessTable
WHERE EntityID = @.EntityID ;
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
--|||"Manso" <Manso@.discussions.microsoft.com> wrote in message
news:C61C4BDC-BFAD-4B20-B379-3504674E28A4@.microsoft.com...
> Hi,
> I understand there are no aggregate function for ORing multiple row
> columns
> together. We have a access control system that looks like
> CREATE TABLE AccessTable
> (
> UserID int NOT NULL PRIMARY KEY,
> EntityID int NOT NULL PRIMARY KEY,
> AccessMask int NOT NULL
> )
> where AccessMask contains an int with values ORed together 1, 2, 4, 8, 16
> etc. We would like to be able to do something like
> select AGGR_ORSUM(AccessMask) As AggSum
> from AccessTable
> where EntityID = @.EntityID
> I've seen some samples of a two table solution but I just wanted to double
> check if someone has a better solution. Running SQL Server 2005.
>
Well, it so happens SQL Server 2005 supports User-Defined Aggregate
functions.
For instance
--Aggregate.cs--
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
[Serializable]
[SqlUserDefinedAggregate(Format.Native,
IsInvariantToDuplicates=true,
IsInvariantToNulls = true,
IsInvariantToOrder = true,
IsNullIfEmpty = false)]
public struct AGGR_ORSUM
{
int accumulator;
public void Init()
{
accumulator = 0;
}
public void Accumulate(SqlInt32 Value)
{
if (Value.IsNull)
return;
accumulator |= Value.Value;
}
public void Merge(AGGR_ORSUM Group)
{
accumulator |= Group.accumulator;
}
public SqlInt32 Terminate()
{
return new SqlInt32(accumulator);
}
}
--end Aggregate.cs--
--Test.sql--
\
drop table AccessTable
CREATE TABLE AccessTable
(
UserID int NOT NULL,
EntityID int NOT NULL ,
AccessMask int NOT NULL
)
insert into AccessTable(UserID, EntityID,AccessMask) values (1,1,2)
insert into AccessTable(UserID, EntityID,AccessMask) values (2,1,16)
insert into AccessTable(UserID, EntityID,AccessMask) values (3,1,2)
select dbo.AGGR_ORSUM(AccessMask) As AggSum
from AccessTable
where EntityID = 1
--end Test.sql--
David|||Thanks for your prompt reply David.
Because it's a convenient way of representing access masks. I know it has a
few drawbacks but the upside is we can append new access bits without adding
columns or modifying the schema. Since we're never using the access column i
n
a where clause I find it to be the best option. We have about 13 different
access types today but that will increase.
I just managed to put together a function that does what we want:
declare @.Return int
set @.Return = 0
select @.Return = @.Return | AccessMask
from AccessTable
where where EntityID = @.EntityID
return @.Return
Thanks,
Manso
"David Portas" wrote:

> Manso wrote:
> Why would you store this data as a bitmap in the first place? Try:
> SELECT
> MAX(AccessMask & 1)+
> MAX(AccessMask & 2)+
> MAX(AccessMask & 4)+
> MAX(AccessMask & 8)+
> MAX(AccessMask & 16)+
> MAX(AccessMask & 32)+
> MAX(AccessMask & 64)+
> MAX(AccessMask & 128) AS aggr_orsum
> FROM AccessTable
> WHERE EntityID = @.EntityID ;
> --
> 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
> --
>|||Manso wrote:
> Thanks for your prompt reply David.
> Because it's a convenient way of representing access masks. I know it has
a
> few drawbacks but the upside is we can append new access bits without addi
ng
> columns or modifying the schema. Since we're never using the access column
in
> a where clause I find it to be the best option. We have about 13 different
> access types today but that will increase.
How about :
CREATE TABLE AccessTable
(
UserID int NOT NULL,
EntityID int NOT NULL,
AccessTypeID int NOT NULL CHECK AccessType BETWEEN (1 AND 13),
PRIMARY KEY (UserID, EntityID, AccessTypeID)
)

> I just managed to put together a function that does what we want:
> declare @.Return int
> set @.Return = 0
> select @.Return = @.Return | AccessMask
> from AccessTable
> where where EntityID = @.EntityID
> return @.Return
>
That gives an undefined result because it's a multi-row assignment,
which isn't officially supported. For that reason it may break under
some conditions. Also, it isn't a general aggregation solution because
it cannot GROUP.
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
--|||Beautiful, David. Thanks a lot. We've been really indecisive whether to use
CLR in this project but maybe we should look into it...looks

.
Thanks,
Manso
"David Browne" wrote:

> "Manso" <Manso@.discussions.microsoft.com> wrote in message
> news:C61C4BDC-BFAD-4B20-B379-3504674E28A4@.microsoft.com...
> Well, it so happens SQL Server 2005 supports User-Defined Aggregate
> functions.
> For instance
> --Aggregate.cs--
> using System;
> using System.Data;
> using System.Data.SqlClient;
> using System.Data.SqlTypes;
> using Microsoft.SqlServer.Server;
>
> [Serializable]
> [SqlUserDefinedAggregate(Format.Native,
> IsInvariantToDuplicates=true,
> IsInvariantToNulls = true,
> IsInvariantToOrder = true,
> IsNullIfEmpty = false)]
> public struct AGGR_ORSUM
> {
> int accumulator;
> public void Init()
> {
> accumulator = 0;
> }
> public void Accumulate(SqlInt32 Value)
> {
> if (Value.IsNull)
> return;
> accumulator |= Value.Value;
> }
> public void Merge(AGGR_ORSUM Group)
> {
> accumulator |= Group.accumulator;
> }
> public SqlInt32 Terminate()
> {
> return new SqlInt32(accumulator);
> }
>
> }
> --end Aggregate.cs--
> --Test.sql--
> \
> drop table AccessTable
> CREATE TABLE AccessTable
> (
> UserID int NOT NULL,
> EntityID int NOT NULL ,
> AccessMask int NOT NULL
> )
> insert into AccessTable(UserID, EntityID,AccessMask) values (1,1,2)
> insert into AccessTable(UserID, EntityID,AccessMask) values (2,1,16)
> insert into AccessTable(UserID, EntityID,AccessMask) values (3,1,2)
> select dbo.AGGR_ORSUM(AccessMask) As AggSum
> from AccessTable
> where EntityID = 1
> --end Test.sql--
> David
>
>|||I left out the check constraint for clarity.
I read somewhere else that this could give unwanted results somewhere else
but I haven't managed to find out under which circumstances. Do you know
when/why/how this could break?
Thanks,
Manso
"David Portas" wrote:

> Manso wrote:
> How about :
> CREATE TABLE AccessTable
> (
> UserID int NOT NULL,
> EntityID int NOT NULL,
> AccessTypeID int NOT NULL CHECK AccessType BETWEEN (1 AND 13),
> PRIMARY KEY (UserID, EntityID, AccessTypeID)
> )
>
> That gives an undefined result because it's a multi-row assignment,
> which isn't officially supported. For that reason it may break under
> some conditions. Also, it isn't a general aggregation solution because
> it cannot GROUP.
> --
> 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
> --
>|||Manso wrote:
> I left out the check constraint for clarity.
But in my example you don't need the bitmap (because I changed the
key).

> I read somewhere else that this could give unwanted results somewhere else
> but I haven't managed to find out under which circumstances. Do you know
> when/why/how this could break?
Books Online (2000 and 2005) says
"If the SELECT statement returns more than one value, the variable is
assigned the last value returned."
So if your "aggregate" solution works it is arguably a bug!
The following KB confirms that "The correct behavior for an aggregate
concatenation query is undefined." So you are definitely on thin ice if
you rely on it.
http://support.microsoft.com/kb/287515/en-us
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
--|||"The correct behavior for an aggregate concatenation query is undefined."
This classic contrived phrase was written by some still unknown fuzzball
confirmed by
one intelligent MS employee.It is meaningless and that's putting it kindly:)
Perhaps it was Celko:) My guess is that since it came from Sybase MS thinks
the less said
about it the better.Aside from the functionality,which boils down to 'it
depends',
it is another example that 'words due matter':)
For interested readers see what sybase says about it (especially in Update)
in their
enterprise db.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1145543785.636307.71580@.i39g2000cwa.googlegroups.com...
> Manso wrote:
> But in my example you don't need the bitmap (because I changed the
> key).
>
> Books Online (2000 and 2005) says
> "If the SELECT statement returns more than one value, the variable is
> assigned the last value returned."
> So if your "aggregate" solution works it is arguably a bug!
> The following KB confirms that "The correct behavior for an aggregate
> concatenation query is undefined." So you are definitely on thin ice if
> you rely on it.
> http://support.microsoft.com/kb/287515/en-us
> --
> 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
> --
>|||Writing assembly language in SQL will come back and get you. Use a
normalized table design with one column for each access privilege or a
hierarchy of privileges in a nested sets model.
Also, you had two PRIMARY KEYs!

Thursday, March 22, 2012

Book Recommendations

Thursday, March 8, 2012

Blocking a specific host

Blocking a specific host

Blocking a specific host

Wednesday, March 7, 2012

Blocked Process Command

block a user from executing system stored procs?

block a user from executing system stored procs?

block a user from executing system stored procs?

Saturday, February 25, 2012

Blobs: File System or DB?

Blobs: File System or DB?

Blobs: File System or DB?

Tuesday, February 14, 2012

Black Reports in Print Layout

Bkp Restore Failed

Bkp Restore Failed

Sunday, February 12, 2012

Bitwise or aggregate function

sql bit data type

Blog Archive

About Me