Question regarding size of varchar field

Hi,
I am using MSDE together with Enterprise Manager.
I have a table with a field nameddescription.
This field will be filled by a web forms's textbox web control.
The textbox'smaxsize attribute is set to "3000" characters.
What size do I have to adjust for my DB fielddescription?
Is the size of3000 in Enterpise Manager equal to3000 characters for the textbox?
I am just trying to avoid errors if MSDE cuts off the string that comes from the textbox webcontrol.Yes, you should set the width of your varchar column to 3000. This unit of measurement is actually bytes, but each character takes 1byte to store, so in effect the column can hold 3000 characters..
|||

I'm answering a question you didn't ask, but maxsize doesn't work if your textbox is multi-line. I just assumed it would be if you allowed that much in it. If you want to limit a multi-line textbox, you need to use a regular expression validator to do it.

|||Thanks for letting me know - and you are right... the texbox is indeed multi-line.
Maybe you can answer my question I have asked in another thread addressing a regular expression issue I am currently faced with - I am still waiting for some helpers there ...
This is the thread:
http://forums.asp.net/937464/ShowPost.aspx|||One more point is if you will use unicode (nvarchar or nchar data type), then physical size for a character will be doubled which means 2 bytes for a character.
If you run
sp_help TableName
you will see "Length" column which keeps physical length of column in bytes

Question re: security issue detailed in KB887459

Is the Report Manager vulnerable to the issue described here:
http://support.microsoft.com/?kbid=887459
I'm not very experienced with ASP.NET - can I assume that any additional
safeguards for the "canonicalization" issues would have to come from
Microsoft in the case of Report Manager as it is a compiled app? TIA.
-BAHI Brian:
There is now an MSI file that will install an HttpModule to protect
all ASP.NET applications.
See: http://www.microsoft.com/security/incident/aspnet.mspx
--
Scott
http://www.OdeToCode.com/
On Thu, 07 Oct 2004 17:00:46 -0700, Brian Almond
<pythonista@.sbcglobal.net> wrote:
>Is the Report Manager vulnerable to the issue described here:
>http://support.microsoft.com/?kbid=887459
>I'm not very experienced with ASP.NET - can I assume that any additional
>safeguards for the "canonicalization" issues would have to come from
>Microsoft in the case of Report Manager as it is a compiled app? TIA.
>-BA|||Scott Allen wrote:
> There is now an MSI file that will install an HttpModule to protect
> all ASP.NET applications.
> See: http://www.microsoft.com/security/incident/aspnet.mspx
Thanks for posting the link Scott. Unfortunately it looks like
something that MSI does has confused Report Manager on my our
development RS box so that now I'm getting a security exception when
browsing to it. Playing with it now to see if I just need to make
simple config. changes or if it's something more involved causing me
trouble.
-BA|||Interesting, I'll give it a try tommorow at home and see what happens.
--
Scott
http://www.OdeToCode.com/
On Fri, 08 Oct 2004 10:02:30 -0700, Brian Almond
<pythonista@.sbcglobal.net> wrote:
>Scott Allen wrote:
>> There is now an MSI file that will install an HttpModule to protect
>> all ASP.NET applications.
>> See: http://www.microsoft.com/security/incident/aspnet.mspx
>Thanks for posting the link Scott. Unfortunately it looks like
>something that MSI does has confused Report Manager on my our
>development RS box so that now I'm getting a security exception when
>browsing to it. Playing with it now to see if I just need to make
>simple config. changes or if it's something more involved causing me
>trouble.
>-BA|||Brian,
I had the exact same problem when I installed - let me know if you get a
resolution on this.
Thanks,
Dan
"Brian Almond" <pythonista@.sbcglobal.net> wrote in message
news:u8eYViVrEHA.3172@.TK2MSFTNGP10.phx.gbl...
> Scott Allen wrote:
> > There is now an MSI file that will install an HttpModule to protect
> > all ASP.NET applications.
> >
> > See: http://www.microsoft.com/security/incident/aspnet.mspx
> Thanks for posting the link Scott. Unfortunately it looks like
> something that MSI does has confused Report Manager on my our
> development RS box so that now I'm getting a security exception when
> browsing to it. Playing with it now to see if I just need to make
> simple config. changes or if it's something more involved causing me
> trouble.
> -BA|||Yes, it's a problem, unfortunately.
I have everything working again after adding a new CodeGroup to both
policy config files, see:
http://odetocode.com/Blogs/scott/archive/2004/10/08/538.aspx
Let me know if this gets you up and running again. If anyone from MS
has an official recommendation I'll update the blog.
--
Scott
http://www.OdeToCode.com/
On Fri, 8 Oct 2004 19:57:15 -0400, "Dan Plaskon"
<dplaskon@.sympatico.ca> wrote:
>Brian,
>I had the exact same problem when I installed - let me know if you get a
>resolution on this.
>Thanks,
>Dan
>"Brian Almond" <pythonista@.sbcglobal.net> wrote in message
>news:u8eYViVrEHA.3172@.TK2MSFTNGP10.phx.gbl...
>> Scott Allen wrote:
>> > There is now an MSI file that will install an HttpModule to protect
>> > all ASP.NET applications.
>> >
>> > See: http://www.microsoft.com/security/incident/aspnet.mspx
>> Thanks for posting the link Scott. Unfortunately it looks like
>> something that MSI does has confused Report Manager on my our
>> development RS box so that now I'm getting a security exception when
>> browsing to it. Playing with it now to see if I just need to make
>> simple config. changes or if it's something more involved causing me
>> trouble.
>> -BA
>|||That change doesn't work on my server. I get a parse error on the
ValidatePathModule line of
machine.config... Very strange error as it only says "?" as error message.
/Per Salmi
"Scott Allen" <bitmask@.[nospam].fred.net> skrev i meddelandet
news:4viem0dkbh4apfl41f6btmtufl6pq8mio0@.4ax.com...
> Yes, it's a problem, unfortunately.
> I have everything working again after adding a new CodeGroup to both
> policy config files, see:
> http://odetocode.com/Blogs/scott/archive/2004/10/08/538.aspx
> Let me know if this gets you up and running again. If anyone from MS
> has an official recommendation I'll update the blog.
> --
> Scott
> http://www.OdeToCode.com/
> On Fri, 8 Oct 2004 19:57:15 -0400, "Dan Plaskon"
> <dplaskon@.sympatico.ca> wrote:
>>Brian,
>>I had the exact same problem when I installed - let me know if you get a
>>resolution on this.
>>Thanks,
>>Dan
>>"Brian Almond" <pythonista@.sbcglobal.net> wrote in message
>>news:u8eYViVrEHA.3172@.TK2MSFTNGP10.phx.gbl...
>> Scott Allen wrote:
>> > There is now an MSI file that will install an HttpModule to protect
>> > all ASP.NET applications.
>> >
>> > See: http://www.microsoft.com/security/incident/aspnet.mspx
>> Thanks for posting the link Scott. Unfortunately it looks like
>> something that MSI does has confused Report Manager on my our
>> development RS box so that now I'm getting a security exception when
>> browsing to it. Playing with it now to see if I just need to make
>> simple config. changes or if it's something more involved causing me
>> trouble.
>> -BA
>|||Tried the same thing on another server and now the parse error on
machine.config says:
Description: An error occurred during the processing of a configuration file
required to service this request. Please review the specific error details
below and modify your configuration file appropriately.
Parser Error Message: Assembly microsoft.web.validatepathmodule.dll security
permission grant set is incompatible between appdomains.
Source Error:
Line 320: <add name="FileAuthorization"
type="System.Web.Security.FileAuthorizationModule"/>
Line 321: <add name="ErrorHandlerModule"
type="System.Web.Mobile.ErrorHandlerModule, System.Web.Mobile,
Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
Line 322: <add name="ValidatePathModule"
type="Microsoft.Web.ValidatePathModule, Microsoft.Web.ValidatePathModule,
Version=1.0.0.0, Culture=neutral,
PublicKeyToken=eba19824f86fdadd"/></httpModules>
Line 323: <!--
Line 324: processModel Attributes:
/Per Salmi
"Scott Allen" <bitmask@.[nospam].fred.net> skrev i meddelandet
news:4viem0dkbh4apfl41f6btmtufl6pq8mio0@.4ax.com...
> Yes, it's a problem, unfortunately.
> I have everything working again after adding a new CodeGroup to both
> policy config files, see:
> http://odetocode.com/Blogs/scott/archive/2004/10/08/538.aspx
> Let me know if this gets you up and running again. If anyone from MS
> has an official recommendation I'll update the blog.
> --
> Scott
> http://www.OdeToCode.com/
> On Fri, 8 Oct 2004 19:57:15 -0400, "Dan Plaskon"
> <dplaskon@.sympatico.ca> wrote:
>>Brian,
>>I had the exact same problem when I installed - let me know if you get a
>>resolution on this.
>>Thanks,
>>Dan
>>"Brian Almond" <pythonista@.sbcglobal.net> wrote in message
>>news:u8eYViVrEHA.3172@.TK2MSFTNGP10.phx.gbl...
>> Scott Allen wrote:
>> > There is now an MSI file that will install an HttpModule to protect
>> > all ASP.NET applications.
>> >
>> > See: http://www.microsoft.com/security/incident/aspnet.mspx
>> Thanks for posting the link Scott. Unfortunately it looks like
>> something that MSI does has confused Report Manager on my our
>> development RS box so that now I'm getting a security exception when
>> browsing to it. Playing with it now to see if I just need to make
>> simple config. changes or if it's something more involved causing me
>> trouble.
>> -BA
>|||If you restart the web server after making the configuration changes
it should all be working then.
--
Scott
http://www.OdeToCode.com/
On Mon, 11 Oct 2004 12:11:14 +0200, "Per Salmi"
<per.salmi@.nospam.nospam> wrote:
>Tried the same thing on another server and now the parse error on
>machine.config says:
>Description: An error occurred during the processing of a configuration file
>required to service this request. Please review the specific error details
>below and modify your configuration file appropriately.
>Parser Error Message: Assembly microsoft.web.validatepathmodule.dll security
>permission grant set is incompatible between appdomains.
>Source Error:
>Line 320: <add name="FileAuthorization"
>type="System.Web.Security.FileAuthorizationModule"/>
>Line 321: <add name="ErrorHandlerModule"
>type="System.Web.Mobile.ErrorHandlerModule, System.Web.Mobile,
>Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
>Line 322: <add name="ValidatePathModule"
>type="Microsoft.Web.ValidatePathModule, Microsoft.Web.ValidatePathModule,
>Version=1.0.0.0, Culture=neutral,
>PublicKeyToken=eba19824f86fdadd"/></httpModules>
>Line 323: <!--
>Line 324: processModel Attributes:
>
>/Per Salmi
>
>"Scott Allen" <bitmask@.[nospam].fred.net> skrev i meddelandet
>news:4viem0dkbh4apfl41f6btmtufl6pq8mio0@.4ax.com...
>> Yes, it's a problem, unfortunately.
>> I have everything working again after adding a new CodeGroup to both
>> policy config files, see:
>> http://odetocode.com/Blogs/scott/archive/2004/10/08/538.aspx
>> Let me know if this gets you up and running again. If anyone from MS
>> has an official recommendation I'll update the blog.
>> --
>> Scott
>> http://www.OdeToCode.com/
>> On Fri, 8 Oct 2004 19:57:15 -0400, "Dan Plaskon"
>> <dplaskon@.sympatico.ca> wrote:
>>Brian,
>>I had the exact same problem when I installed - let me know if you get a
>>resolution on this.
>>Thanks,
>>Dan
>>"Brian Almond" <pythonista@.sbcglobal.net> wrote in message
>>news:u8eYViVrEHA.3172@.TK2MSFTNGP10.phx.gbl...
>> Scott Allen wrote:
>> > There is now an MSI file that will install an HttpModule to protect
>> > all ASP.NET applications.
>> >
>> > See: http://www.microsoft.com/security/incident/aspnet.mspx
>> Thanks for posting the link Scott. Unfortunately it looks like
>> something that MSI does has confused Report Manager on my our
>> development RS box so that now I'm getting a security exception when
>> browsing to it. Playing with it now to see if I just need to make
>> simple config. changes or if it's something more involved causing me
>> trouble.
>> -BA
>>
>|||I called Microsoft support services, they were clueless. I'm blogging
about it at http://www.dogcaught.com/dpack/index.php?p=52
Aaron
http://www.hockley.org
"Per Salmi" <per.salmi@.nospam.nospam> wrote in message news:<ebdglq3rEHA.192@.tk2msftngp13.phx.gbl>...
> Tried the same thing on another server and now the parse error on
> machine.config says:
> Description: An error occurred during the processing of a configuration file
> required to service this request. Please review the specific error details
> below and modify your configuration file appropriately.
> Parser Error Message: Assembly microsoft.web.validatepathmodule.dll security
> permission grant set is incompatible between appdomains.
> Source Error:
> Line 320: <add name="FileAuthorization"
> type="System.Web.Security.FileAuthorizationModule"/>
> Line 321: <add name="ErrorHandlerModule"
> type="System.Web.Mobile.ErrorHandlerModule, System.Web.Mobile,
> Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
> Line 322: <add name="ValidatePathModule"
> type="Microsoft.Web.ValidatePathModule, Microsoft.Web.ValidatePathModule,
> Version=1.0.0.0, Culture=neutral,
> PublicKeyToken=eba19824f86fdadd"/></httpModules>
> Line 323: <!--
> Line 324: processModel Attributes:
>
> /Per Salmi
>|||Scott Allen wrote:
> I have everything working again after adding a new CodeGroup to both
> policy config files, see:
> http://odetocode.com/Blogs/scott/archive/2004/10/08/538.aspx
> Let me know if this gets you up and running again. If anyone from MS
> has an official recommendation I'll update the blog.
I have to admit that I'm left wondering why Microsoft released the patch
without getting a green light on their web apps. Coincidentally I
have tried your fix for their patch, but am now getting an error message
complaining about a request for StrongNameIdentityPermission. I guess
I'm going to have to bear down and study materials on configuration
ASP.NET applications if I want to get any semblance of a grip on this.
-BA|||Brian Almond wrote:
> configuration ASP.NET applications
_Configuring_ ASP.NET applications. Bah! I should really review my
messages prior to posting ;)|||Just an FYI for everyone. This doesn't help solve the issue with a system
being messed up but I don't think that RS is vulnerable to this exploit. I
have had some discussions with MS people and the bottom line is that Report
Server stores all of its secure content in the database. Report Manager is
the portal to Report Server and although it could be affected that there
would not be an exploit because it does not have secure content.
Further explanation given to me is that this exploit is seen when using form
authentication and you use the result of authentication to grant
permissions to files inside your application vroot which Report Manager does
not do.
If you think about how Report Server is designed as a service and if you
look on the server you will not see any rdl files. SQL Server db is used to
store all this information. So it is not like Report Manager is just opening
up report files.
I hesitated to jump in but I hate to see people messing around and wasting
time and energy on a patch that isn't needed. Please note that I am not the
official MS voice. At a minimum I would delay working on it. Hopefully we
can get an official MS person to bless what I said above.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Brian Almond" <pythonista@.sbcglobal.net> wrote in message
news:%23oJu899rEHA.2684@.TK2MSFTNGP12.phx.gbl...
> Scott Allen wrote:
> > I have everything working again after adding a new CodeGroup to both
> > policy config files, see:
> > http://odetocode.com/Blogs/scott/archive/2004/10/08/538.aspx
> >
> > Let me know if this gets you up and running again. If anyone from MS
> > has an official recommendation I'll update the blog.
> I have to admit that I'm left wondering why Microsoft released the patch
> without getting a green light on their web apps. Coincidentally I
> have tried your fix for their patch, but am now getting an error message
> complaining about a request for StrongNameIdentityPermission. I guess
> I'm going to have to bear down and study materials on configuration
> ASP.NET applications if I want to get any semblance of a grip on this.
> -BA|||Bruce,
Thanks for commenting on this. I hope we do get an official word at
some point on this issue. It would be nice not to have to worry about
this issue in the future.
Unfortunately, at this point I have a 'dead' report server. Luckily
it's my test server, but I would like to get it back up without
reinstalling RS if possible. (Uninstalling the MS patch doesn't seem to
revert all of its changes.) I didn't backup all of the configuration
files before applying the Microsoft patch, so I've given myself a more
difficult restore situation than I could have had otherwise.
It is certainly time better spent elsewhere.
-BA|||Hi Bruce:
I'm inclined to agree with you after some experimenting today.
In the case where someone *has* to install the module on a machine
with SSRS (because there are other ASP.NET applications present), the
fix is to put the following entry in the web.config file (both
ReportManager and ReportServer config files) in the system.web
section:
<httpModules>
<remove name="ValidatePathModule"/>
</httpModules>
This disables the module for just the SSRS applications.
There sure has been some confusion. I've seen a couple reputable
sources say the vulnerability exists for Windows authentication in
addition to forms authentication. I've also heard that Windows 2003 is
affected, even though I haven't been able to exploit the vulnerability
on any of my 2003 machines.
--
Scott
http://www.OdeToCode.com/
On Mon, 11 Oct 2004 17:33:19 -0500, "Bruce L-C [MVP]"
<bruce_lcNOSPAM@.hotmail.com> wrote:
>Just an FYI for everyone. This doesn't help solve the issue with a system
>being messed up but I don't think that RS is vulnerable to this exploit. I
>have had some discussions with MS people and the bottom line is that Report
>Server stores all of its secure content in the database. Report Manager is
>the portal to Report Server and although it could be affected that there
>would not be an exploit because it does not have secure content.
>Further explanation given to me is that this exploit is seen when using form
>authentication and you use the result of authentication to grant
>permissions to files inside your application vroot which Report Manager does
>not do.
>If you think about how Report Server is designed as a service and if you
>look on the server you will not see any rdl files. SQL Server db is used to
>store all this information. So it is not like Report Manager is just opening
>up report files.
>I hesitated to jump in but I hate to see people messing around and wasting
>time and energy on a patch that isn't needed. Please note that I am not the
>official MS voice. At a minimum I would delay working on it. Hopefully we
>can get an official MS person to bless what I said above.|||We are working on a KB article that will have the offical workaround for
this. We hope to have it posted tomorrow.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Scott Allen" <bitmask@.[nospam].fred.net> wrote in message
news:kobmm0hepkp6gh7voej1d882b56mpbt1en@.4ax.com...
> Hi Bruce:
> I'm inclined to agree with you after some experimenting today.
> In the case where someone *has* to install the module on a machine
> with SSRS (because there are other ASP.NET applications present), the
> fix is to put the following entry in the web.config file (both
> ReportManager and ReportServer config files) in the system.web
> section:
> <httpModules>
> <remove name="ValidatePathModule"/>
> </httpModules>
> This disables the module for just the SSRS applications.
> There sure has been some confusion. I've seen a couple reputable
> sources say the vulnerability exists for Windows authentication in
> addition to forms authentication. I've also heard that Windows 2003 is
> affected, even though I haven't been able to exploit the vulnerability
> on any of my 2003 machines.
> --
> Scott
> http://www.OdeToCode.com/
> On Mon, 11 Oct 2004 17:33:19 -0500, "Bruce L-C [MVP]"
> <bruce_lcNOSPAM@.hotmail.com> wrote:
>>Just an FYI for everyone. This doesn't help solve the issue with a system
>>being messed up but I don't think that RS is vulnerable to this exploit. I
>>have had some discussions with MS people and the bottom line is that
>>Report
>>Server stores all of its secure content in the database. Report Manager is
>>the portal to Report Server and although it could be affected that there
>>would not be an exploit because it does not have secure content.
>>Further explanation given to me is that this exploit is seen when using
>>form
>>authentication and you use the result of authentication to grant
>>permissions to files inside your application vroot which Report Manager
>>does
>>not do.
>>If you think about how Report Server is designed as a service and if you
>>look on the server you will not see any rdl files. SQL Server db is used
>>to
>>store all this information. So it is not like Report Manager is just
>>opening
>>up report files.
>>I hesitated to jump in but I hate to see people messing around and wasting
>>time and energy on a patch that isn't needed. Please note that I am not
>>the
>>official MS voice. At a minimum I would delay working on it. Hopefully we
>>can get an official MS person to bless what I said above.
>|||You are right in this that the RS might not be affected by the vulnerability
but as there might be lots of other asp.net applications running on the same
server that are vulnerable it would feel better to have the patch installed,
and still have a working report server application.
Best regards,
Per Salmi
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> skrev i meddelandet
news:ODmNQJ%23rEHA.2096@.TK2MSFTNGP11.phx.gbl...
> I hesitated to jump in but I hate to see people messing around and wasting
> time and energy on a patch that isn't needed. Please note that I am not
> the
> official MS voice. At a minimum I would delay working on it. Hopefully we
> can get an official MS person to bless what I said above.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services|||There is now a KB article that describes the workaround at
http://support.microsoft.com/?kbid=887787.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Per Salmi" <per.salmi@.nospam.nospam> wrote in message
news:uBzQx2BsEHA.3748@.TK2MSFTNGP09.phx.gbl...
> You are right in this that the RS might not be affected by the
> vulnerability but as there might be lots of other asp.net applications
> running on the same server that are vulnerable it would feel better to
> have the patch installed, and still have a working report server
> application.
> Best regards,
> Per Salmi
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> skrev i meddelandet
> news:ODmNQJ%23rEHA.2096@.TK2MSFTNGP11.phx.gbl...
>> I hesitated to jump in but I hate to see people messing around and
>> wasting
>> time and energy on a patch that isn't needed. Please note that I am not
>> the
>> official MS voice. At a minimum I would delay working on it. Hopefully we
>> can get an official MS person to bless what I said above.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>|||Thanks, Brian.
--
Scott
http://www.OdeToCode.com/
On Tue, 12 Oct 2004 16:09:32 -0700, "Brian Welcker [MSFT]"
<bwelcker@.online.microsoft.com> wrote:
>There is now a KB article that describes the workaround at
>http://support.microsoft.com/?kbid=887787.|||Thanks! That worked perfectly on both of our servers.
/Per Salmi
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> skrev i meddelandet
news:eXlZICLsEHA.2560@.tk2msftngp13.phx.gbl...
> There is now a KB article that describes the workaround at
> http://support.microsoft.com/?kbid=887787.
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server Reporting Services

Question re SQL Server Enterprise Manager

Hi folks
Maybe my memory is wrong, but I sem to recall that it was possible to
configure Enterprise Manager to automatically open to the last DB that I was
using. Also, I seem to recall that this was done via Tools...Options.
Just recently I flattened my PC and did a new install of Windows XP SP2,
Office, VS.Net & SQL Server 2000 Developer Edition. However, since then that
option just isn't there.
The only tabs that appear under Tools...Options are General & Advanced.
Neither of these tabs talk about the above.
Any clues?
Kind regards & Thanks in advance.
Ross Petersen
Tools, Options, Advanced, Startup Options, "Open the console tree to the last active node".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ross" <Ross@.discussions.microsoft.com> wrote in message
news:5455461A-519D-4716-B6F8-C9D17E2B7D4E@.microsoft.com...
> Hi folks
> Maybe my memory is wrong, but I sem to recall that it was possible to
> configure Enterprise Manager to automatically open to the last DB that I was
> using. Also, I seem to recall that this was done via Tools...Options.
> Just recently I flattened my PC and did a new install of Windows XP SP2,
> Office, VS.Net & SQL Server 2000 Developer Edition. However, since then that
> option just isn't there.
> The only tabs that appear under Tools...Options are General & Advanced.
> Neither of these tabs talk about the above.
> Any clues?
> Kind regards & Thanks in advance.
> Ross Petersen
|||Thanks Tibor
I must be blind!!!
Kind regards
Ross Petersen
"Tibor Karaszi" wrote:

> Tools, Options, Advanced, Startup Options, "Open the console tree to the last active node".
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ross" <Ross@.discussions.microsoft.com> wrote in message
> news:5455461A-519D-4716-B6F8-C9D17E2B7D4E@.microsoft.com...
>
>

question on tasks

hi,

if a package fails on error, would i be able to know code wise which particular task failed?
also, if the task is using a connection manager, how will i be able to access that particular connection object?

If you turn on logging then you will know what failed. Does that not suffice?

-Jamie

|||

i'm not sure if this answers you questions, however...

you could execute the package programatically and trap for a DtsTaskException that may be generated. then, you can determine which task caused the error by checking the subcomponent property of the dtserror object.

i believe that you should already know which connection manager a task uses because (as far as i know) this property cannot be set dynamically.

|||

if a task fails, i want to be able to do this PROGRAMMATICALLY on error event:

1. know which task failed and get the connection object used by the task, if any.
2. so that i will try to establish connection to the connection object and
3. reexecute the task if #2 is successful.

please let me know how to do this.

thanks.

|||

Ranier wrote:

if a task fails, i want to be able to do this PROGRAMMATICALLY on error event:

1. know which task failed and get the connection object used by the task, if any.
2. so that i will try to establish connection to the connection object and
3. reexecute the task if #2 is successful.

please let me know how to do this.

thanks.

do you really want to do this? why don't you just execute the package within a transaction and then re-execute it if the package fails?|||

Hi Duane,

In essence, I want to do a retry component for every task... is this possible?

|||

Ranier wrote:

Hi Duane,

In essence, I want to do a retry component for every task... is this possible?

it may be possible, but unnecessary.

if i understand you correctly, you want to re-execute a task if it fails. why do you want to do this? wouldn't it make more sense to rollback the entire package if a task fails, and then re-execute it?

|||

the component that i want to make should be on a script task. i need to be able to reuse it on every package just drag and drop.

why do i need to retry? in cases such as intermittent connection or the target server suddenly reboots and becomes back online after a couple of minutes, i would want the script task to retry the task that failed programmatically and without human intervention.

|||

Ranier wrote:

why do i need to retry? in cases such as intermittent connection or the target server suddenly reboots and becomes back online after a couple of minutes, i would want the script task to retry the task that failed programmatically and without human intervention.

wouldn't it make more sense to rollback the entire package if a task fails, and then re-execute it? you can use transactions and checkpoints to accomplish this.

Question on optimization done by Enterprize manager.

Hi I have set up a scheduled dbase optimation to be done 1 per week. Just
wondering if anyone knows exactly what it does? How does it shrink the
database and how does this help performance?
thanks.
This is what it lists as being done.
Reorganize data and index pages, changing the free space to 10 percent of
the original space.
Shrink database when it grows beyound 50 MB. Leave 10 percent of data space
as free space.
Paul G
Software engineer.
Paul,
What you're refering to is a Database Maintanence Plan. The plan will
create several jobs (depending on the options selected) for the database. A
reorg rebuilds the indexes applying a new fillfactor to remove
fragmentation. A shrink database reduces the size of the database. While I
would recommend the former, I would not recommend performing an automatic
shrink of the database. Also, I would recommend that you periodically
backup the transaction log as part of the plan to keep the size of the log
managable.
HTH
Jerry
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:F8635F69-6A9C-4985-B22C-E53E9035C54D@.microsoft.com...
> Hi I have set up a scheduled dbase optimation to be done 1 per week. Just
> wondering if anyone knows exactly what it does? How does it shrink the
> database and how does this help performance?
> thanks.
> This is what it lists as being done.
> Reorganize data and index pages, changing the free space to 10 percent of
> the original space.
> Shrink database when it grows beyound 50 MB. Leave 10 percent of data
> space
> as free space.
> --
> Paul G
> Software engineer.
|||ok the database is not that large so think I will remove this option. Thanks.
Paul G
Software engineer.
"Jerry Spivey" wrote:

> Paul,
> What you're refering to is a Database Maintanence Plan. The plan will
> create several jobs (depending on the options selected) for the database. A
> reorg rebuilds the indexes applying a new fillfactor to remove
> fragmentation. A shrink database reduces the size of the database. While I
> would recommend the former, I would not recommend performing an automatic
> shrink of the database. Also, I would recommend that you periodically
> backup the transaction log as part of the plan to keep the size of the log
> managable.
> HTH
> Jerry
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:F8635F69-6A9C-4985-B22C-E53E9035C54D@.microsoft.com...
>
>
|||Hi Paul,
Jerry is right on shrinking of log file.
But did u think that reindexing will make any effect on ur performance.
Reindexing takes lot of resources and u should not go for indexing
daily.
make it once a week or one time in two weeks.
once ur database will grow it will take lot of buffer size which will
effect ur database performance.
hope u understand
from
Doller
|||Reorganize (defragment indexes):
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Shrink: http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:F8635F69-6A9C-4985-B22C-E53E9035C54D@.microsoft.com...
> Hi I have set up a scheduled dbase optimation to be done 1 per week. Just
> wondering if anyone knows exactly what it does? How does it shrink the
> database and how does this help performance?
> thanks.
> This is what it lists as being done.
> Reorganize data and index pages, changing the free space to 10 percent of
> the original space.
> Shrink database when it grows beyound 50 MB. Leave 10 percent of data space
> as free space.
> --
> Paul G
> Software engineer.
|||The index rebuild frequency will depend on a number of things but mostly
how often data is changed.
If your tables, on average, have huge amounts of change each day (eg. >
25% of the indexed data changes on a daily basis) then you will want to
reorganise your indexes more often (perhaps daily in the example
given). If, on the other hand, your tables, on average, are mostly
static (eg. < 1% of the indexed data changes on a daily basis) then you
won't need to reorganise as often (in that example once a month would
probably be fine). This should ensure your indexes have a satisfactory
fragmentation (i.e. low) and a reasonably even distribution throughout
the b-tree.
If you have specific tables that have quite a different change rate from
the other tables you may want to implement the reindexing through T-SQL
code you have more control over (rather than using the DB Maint
Wizard). For example, if you have a dozen large tables with lots of
indexes that are mostly static and you had a couple large tables with
lots of indexes that had a huge amount of daily change, then you'd want
to rebuild the indexes on those couple tables quite often (perhaps
daily) and not so often for the rest (perhaps monthly). You'd do that
by scheduling a couple SQLAgent jobs to run the DBCC DBREINDEX (or DBCC
DEFRAGINDEX) statement on specific tables - one job for those very
dynamic tables that runs daily and the other job for the relatively
static tables that runs monthly.
You can't lay a blanket rule down for every database, but you do start
with a general plan (like through the DB Maint Wizard) and
monitor/modify as appropriate for each database.
Modifying indexes does result in significant I/O & CPU load on the
server though, as Doller mentioned. So you should do the reindexing out
of hours. Also, moving all those index pages around results in a large
volume of data logged in the transaction log. So you'll want to keep an
eye on your transaction log size and make sure it doesn't get out of
hand. (Sometimes, in certain circumstances, it's even beneficial to
change the DB recovery model to simple, do your reindexing, change the
recovery model back to full or bulk-logged, and do a full DB backup.
Sometimes a full DB backup is smaller than the transaction log after a
substantial reindexing session.)
*mike hodgson*
blog: http://sqlnerd.blogspot.com
doller wrote:

>Hi Paul,
>Jerry is right on shrinking of log file.
>But did u think that reindexing will make any effect on ur performance.
>Reindexing takes lot of resources and u should not go for indexing
>daily.
>make it once a week or one time in two weeks.
>once ur database will grow it will take lot of buffer size which will
>effect ur database performance.
>hope u understand
>from
>Doller
>
>

Question on optimization done by Enterprize manager.

Hi I have set up a scheduled dbase optimation to be done 1 per week. Just
wondering if anyone knows exactly what it does? How does it shrink the
database and how does this help performance?
thanks.
This is what it lists as being done.
Reorganize data and index pages, changing the free space to 10 percent of
the original space.
Shrink database when it grows beyound 50 MB. Leave 10 percent of data space
as free space.
Paul G
Software engineer.Paul,
What you're refering to is a Database Maintanence Plan. The plan will
create several jobs (depending on the options selected) for the database. A
reorg rebuilds the indexes applying a new fillfactor to remove
fragmentation. A shrink database reduces the size of the database. While I
would recommend the former, I would not recommend performing an automatic
shrink of the database. Also, I would recommend that you periodically
backup the transaction log as part of the plan to keep the size of the log
managable.
HTH
Jerry
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:F8635F69-6A9C-4985-B22C-E53E9035C54D@.microsoft.com...
> Hi I have set up a scheduled dbase optimation to be done 1 per week. Just
> wondering if anyone knows exactly what it does? How does it shrink the
> database and how does this help performance?
> thanks.
> This is what it lists as being done.
> Reorganize data and index pages, changing the free space to 10 percent of
> the original space.
> Shrink database when it grows beyound 50 MB. Leave 10 percent of data
> space
> as free space.
> --
> Paul G
> Software engineer.|||ok the database is not that large so think I will remove this option. Thank
s.
--
Paul G
Software engineer.
"Jerry Spivey" wrote:

> Paul,
> What you're refering to is a Database Maintanence Plan. The plan will
> create several jobs (depending on the options selected) for the database.
A
> reorg rebuilds the indexes applying a new fillfactor to remove
> fragmentation. A shrink database reduces the size of the database. While
I
> would recommend the former, I would not recommend performing an automatic
> shrink of the database. Also, I would recommend that you periodically
> backup the transaction log as part of the plan to keep the size of the log
> managable.
> HTH
> Jerry
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:F8635F69-6A9C-4985-B22C-E53E9035C54D@.microsoft.com...
>
>|||Hi Paul,
Jerry is right on shrinking of log file.
But did u think that reindexing will make any effect on ur performance.
Reindexing takes lot of resources and u should not go for indexing
daily.
make it once a week or one time in two weeks.
once ur database will grow it will take lot of buffer size which will
effect ur database performance.
hope u understand
from
Doller|||Reorganize (defragment indexes):
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Shrink: http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:F8635F69-6A9C-4985-B22C-E53E9035C54D@.microsoft.com...
> Hi I have set up a scheduled dbase optimation to be done 1 per week. Just
> wondering if anyone knows exactly what it does? How does it shrink the
> database and how does this help performance?
> thanks.
> This is what it lists as being done.
> Reorganize data and index pages, changing the free space to 10 percent of
> the original space.
> Shrink database when it grows beyound 50 MB. Leave 10 percent of data spac
e
> as free space.
> --
> Paul G
> Software engineer.|||The index rebuild frequency will depend on a number of things but mostly
how often data is changed.
If your tables, on average, have huge amounts of change each day (eg. >
25% of the indexed data changes on a daily basis) then you will want to
reorganise your indexes more often (perhaps daily in the example
given). If, on the other hand, your tables, on average, are mostly
static (eg. < 1% of the indexed data changes on a daily basis) then you
won't need to reorganise as often (in that example once a month would
probably be fine). This should ensure your indexes have a satisfactory
fragmentation (i.e. low) and a reasonably even distribution throughout
the b-tree.
If you have specific tables that have quite a different change rate from
the other tables you may want to implement the reindexing through T-SQL
code you have more control over (rather than using the DB Maint
Wizard). For example, if you have a dozen large tables with lots of
indexes that are mostly static and you had a couple large tables with
lots of indexes that had a huge amount of daily change, then you'd want
to rebuild the indexes on those couple tables quite often (perhaps
daily) and not so often for the rest (perhaps monthly). You'd do that
by scheduling a couple SQLAgent jobs to run the DBCC DBREINDEX (or DBCC
DEFRAGINDEX) statement on specific tables - one job for those very
dynamic tables that runs daily and the other job for the relatively
static tables that runs monthly.
You can't lay a blanket rule down for every database, but you do start
with a general plan (like through the DB Maint Wizard) and
monitor/modify as appropriate for each database.
Modifying indexes does result in significant I/O & CPU load on the
server though, as Doller mentioned. So you should do the reindexing out
of hours. Also, moving all those index pages around results in a large
volume of data logged in the transaction log. So you'll want to keep an
eye on your transaction log size and make sure it doesn't get out of
hand. (Sometimes, in certain circumstances, it's even beneficial to
change the DB recovery model to simple, do your reindexing, change the
recovery model back to full or bulk-logged, and do a full DB backup.
Sometimes a full DB backup is smaller than the transaction log after a
substantial reindexing session.)
*mike hodgson*
blog: http://sqlnerd.blogspot.com
doller wrote:

>Hi Paul,
>Jerry is right on shrinking of log file.
>But did u think that reindexing will make any effect on ur performance.
>Reindexing takes lot of resources and u should not go for indexing
>daily.
>make it once a week or one time in two weeks.
>once ur database will grow it will take lot of buffer size which will
>effect ur database performance.
>hope u understand
>from
>Doller
>
>

Question on optimization done by Enterprize manager.

Hi I have set up a scheduled dbase optimation to be done 1 per week. Just
wondering if anyone knows exactly what it does? How does it shrink the
database and how does this help performance?
thanks.
This is what it lists as being done.
Reorganize data and index pages, changing the free space to 10 percent of
the original space.
Shrink database when it grows beyound 50 MB. Leave 10 percent of data space
as free space.
--
Paul G
Software engineer.Paul,
What you're refering to is a Database Maintanence Plan. The plan will
create several jobs (depending on the options selected) for the database. A
reorg rebuilds the indexes applying a new fillfactor to remove
fragmentation. A shrink database reduces the size of the database. While I
would recommend the former, I would not recommend performing an automatic
shrink of the database. Also, I would recommend that you periodically
backup the transaction log as part of the plan to keep the size of the log
managable.
HTH
Jerry
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:F8635F69-6A9C-4985-B22C-E53E9035C54D@.microsoft.com...
> Hi I have set up a scheduled dbase optimation to be done 1 per week. Just
> wondering if anyone knows exactly what it does? How does it shrink the
> database and how does this help performance?
> thanks.
> This is what it lists as being done.
> Reorganize data and index pages, changing the free space to 10 percent of
> the original space.
> Shrink database when it grows beyound 50 MB. Leave 10 percent of data
> space
> as free space.
> --
> Paul G
> Software engineer.|||ok the database is not that large so think I will remove this option. Thanks.
--
Paul G
Software engineer.
"Jerry Spivey" wrote:
> Paul,
> What you're refering to is a Database Maintanence Plan. The plan will
> create several jobs (depending on the options selected) for the database. A
> reorg rebuilds the indexes applying a new fillfactor to remove
> fragmentation. A shrink database reduces the size of the database. While I
> would recommend the former, I would not recommend performing an automatic
> shrink of the database. Also, I would recommend that you periodically
> backup the transaction log as part of the plan to keep the size of the log
> managable.
> HTH
> Jerry
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:F8635F69-6A9C-4985-B22C-E53E9035C54D@.microsoft.com...
> > Hi I have set up a scheduled dbase optimation to be done 1 per week. Just
> > wondering if anyone knows exactly what it does? How does it shrink the
> > database and how does this help performance?
> > thanks.
> > This is what it lists as being done.
> > Reorganize data and index pages, changing the free space to 10 percent of
> > the original space.
> > Shrink database when it grows beyound 50 MB. Leave 10 percent of data
> > space
> > as free space.
> >
> > --
> > Paul G
> > Software engineer.
>
>|||Hi Paul,
Jerry is right on shrinking of log file.
But did u think that reindexing will make any effect on ur performance.
Reindexing takes lot of resources and u should not go for indexing
daily.
make it once a week or one time in two weeks.
once ur database will grow it will take lot of buffer size which will
effect ur database performance.
hope u understand
from
Doller|||Reorganize (defragment indexes):
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Shrink: http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:F8635F69-6A9C-4985-B22C-E53E9035C54D@.microsoft.com...
> Hi I have set up a scheduled dbase optimation to be done 1 per week. Just
> wondering if anyone knows exactly what it does? How does it shrink the
> database and how does this help performance?
> thanks.
> This is what it lists as being done.
> Reorganize data and index pages, changing the free space to 10 percent of
> the original space.
> Shrink database when it grows beyound 50 MB. Leave 10 percent of data space
> as free space.
> --
> Paul G
> Software engineer.|||This is a multi-part message in MIME format.
--020500030202020509000109
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
The index rebuild frequency will depend on a number of things but mostly
how often data is changed.
If your tables, on average, have huge amounts of change each day (eg. >
25% of the indexed data changes on a daily basis) then you will want to
reorganise your indexes more often (perhaps daily in the example
given). If, on the other hand, your tables, on average, are mostly
static (eg. < 1% of the indexed data changes on a daily basis) then you
won't need to reorganise as often (in that example once a month would
probably be fine). This should ensure your indexes have a satisfactory
fragmentation (i.e. low) and a reasonably even distribution throughout
the b-tree.
If you have specific tables that have quite a different change rate from
the other tables you may want to implement the reindexing through T-SQL
code you have more control over (rather than using the DB Maint
Wizard). For example, if you have a dozen large tables with lots of
indexes that are mostly static and you had a couple large tables with
lots of indexes that had a huge amount of daily change, then you'd want
to rebuild the indexes on those couple tables quite often (perhaps
daily) and not so often for the rest (perhaps monthly). You'd do that
by scheduling a couple SQLAgent jobs to run the DBCC DBREINDEX (or DBCC
DEFRAGINDEX) statement on specific tables - one job for those very
dynamic tables that runs daily and the other job for the relatively
static tables that runs monthly.
You can't lay a blanket rule down for every database, but you do start
with a general plan (like through the DB Maint Wizard) and
monitor/modify as appropriate for each database.
Modifying indexes does result in significant I/O & CPU load on the
server though, as Doller mentioned. So you should do the reindexing out
of hours. Also, moving all those index pages around results in a large
volume of data logged in the transaction log. So you'll want to keep an
eye on your transaction log size and make sure it doesn't get out of
hand. (Sometimes, in certain circumstances, it's even beneficial to
change the DB recovery model to simple, do your reindexing, change the
recovery model back to full or bulk-logged, and do a full DB backup.
Sometimes a full DB backup is smaller than the transaction log after a
substantial reindexing session.)
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
doller wrote:
>Hi Paul,
>Jerry is right on shrinking of log file.
>But did u think that reindexing will make any effect on ur performance.
>Reindexing takes lot of resources and u should not go for indexing
>daily.
>make it once a week or one time in two weeks.
>once ur database will grow it will take lot of buffer size which will
>effect ur database performance.
>hope u understand
>from
>Doller
>
>
--020500030202020509000109
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>The index rebuild frequency will depend on a number of things but
mostly how often data is changed.<br>
<br>
If your tables, on average, have huge amounts of change each day (eg.
> 25% of the indexed data changes on a daily basis) then you will
want to reorganise your indexes more often (perhaps daily in the
example given). If, on the other hand, your tables, on average, are
mostly static (eg. < 1% of the indexed data changes on a daily
basis) then you won't need to reorganise as often (in that example once
a month would probably be fine). This should ensure your indexes have
a satisfactory fragmentation (i.e. low) and a reasonably even
distribution throughout the b-tree.<br>
<br>
If you have specific tables that have quite a different change rate
from the other tables you may want to implement the reindexing through
T-SQL code you have more control over (rather than using the DB Maint
Wizard). For example, if you have a dozen large tables with lots of
indexes that are mostly static and you had a couple large tables with
lots of indexes that had a huge amount of daily change, then you'd want
to rebuild the indexes on those couple tables quite often (perhaps
daily) and not so often for the rest (perhaps monthly). You'd do that
by scheduling a couple SQLAgent jobs to run the DBCC DBREINDEX (or DBCC
DEFRAGINDEX) statement on specific tables - one job for those very
dynamic tables that runs daily and the other job for the relatively
static tables that runs monthly.<br>
<br>
You can't lay a blanket rule down for every database, but you do start
with a general plan (like through the DB Maint Wizard) and
monitor/modify as appropriate for each database.<br>
<br>
Modifying indexes does result in significant I/O & CPU load on the
server though, as Doller mentioned. So you should do the reindexing
out of hours. Also, moving all those index pages around results in a
large volume of data logged in the transaction log. So you'll want to
keep an eye on your transaction log size and make sure it doesn't get
out of hand. (Sometimes, in certain circumstances, it's even
beneficial to change the DB recovery model to simple, do your
reindexing, change the recovery model back to full or bulk-logged, and
do a full DB backup. Sometimes a full DB backup is smaller than the
transaction log after a substantial reindexing session.)<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
doller wrote:
<blockquote
cite="mid1127969601.827232.288720@.g14g2000cwa.googlegroups.com"
type="cite">
<pre wrap="">Hi Paul,
Jerry is right on shrinking of log file.
But did u think that reindexing will make any effect on ur performance.
Reindexing takes lot of resources and u should not go for indexing
daily.
make it once a week or one time in two weeks.
once ur database will grow it will take lot of buffer size which will
effect ur database performance.
hope u understand
from
Doller
</pre>
</blockquote>
</body>
</html>
--020500030202020509000109--|||Hi thanks for the additional information. I do not expect large amounts of
data daily perhaps 30 records in several tables so have set indexing to 1 per
week.
--
Paul G
Software engineer.
"Mike Hodgson" wrote:
> The index rebuild frequency will depend on a number of things but mostly
> how often data is changed.
> If your tables, on average, have huge amounts of change each day (eg. >
> 25% of the indexed data changes on a daily basis) then you will want to
> reorganise your indexes more often (perhaps daily in the example
> given). If, on the other hand, your tables, on average, are mostly
> static (eg. < 1% of the indexed data changes on a daily basis) then you
> won't need to reorganise as often (in that example once a month would
> probably be fine). This should ensure your indexes have a satisfactory
> fragmentation (i.e. low) and a reasonably even distribution throughout
> the b-tree.
> If you have specific tables that have quite a different change rate from
> the other tables you may want to implement the reindexing through T-SQL
> code you have more control over (rather than using the DB Maint
> Wizard). For example, if you have a dozen large tables with lots of
> indexes that are mostly static and you had a couple large tables with
> lots of indexes that had a huge amount of daily change, then you'd want
> to rebuild the indexes on those couple tables quite often (perhaps
> daily) and not so often for the rest (perhaps monthly). You'd do that
> by scheduling a couple SQLAgent jobs to run the DBCC DBREINDEX (or DBCC
> DEFRAGINDEX) statement on specific tables - one job for those very
> dynamic tables that runs daily and the other job for the relatively
> static tables that runs monthly.
> You can't lay a blanket rule down for every database, but you do start
> with a general plan (like through the DB Maint Wizard) and
> monitor/modify as appropriate for each database.
> Modifying indexes does result in significant I/O & CPU load on the
> server though, as Doller mentioned. So you should do the reindexing out
> of hours. Also, moving all those index pages around results in a large
> volume of data logged in the transaction log. So you'll want to keep an
> eye on your transaction log size and make sure it doesn't get out of
> hand. (Sometimes, in certain circumstances, it's even beneficial to
> change the DB recovery model to simple, do your reindexing, change the
> recovery model back to full or bulk-logged, and do a full DB backup.
> Sometimes a full DB backup is smaller than the transaction log after a
> substantial reindexing session.)
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> doller wrote:
> >Hi Paul,
> >
> >Jerry is right on shrinking of log file.
> >But did u think that reindexing will make any effect on ur performance.
> >Reindexing takes lot of resources and u should not go for indexing
> >daily.
> >
> >make it once a week or one time in two weeks.
> >once ur database will grow it will take lot of buffer size which will
> >effect ur database performance.
> >
> >hope u understand
> >
> >from
> >Doller
> >
> >
> >
>

Question on Job history

Please could someone enlight me.
In enterprise manager -> management-> jobs -> view history
I could normally see the historic runs of the jobs.
I have a server, for some reason, only hold one historic
record of each job. Each time a job is run the history is
then refreshed and delete with only the latest job remain.
Where can I set in Enterprise Manager so I could adjust
the amount of the history held for the jobs?
Thanks
AlexAlex Au wrote:

> Please could someone enlight me.
> In enterprise manager -> management-> jobs -> view history
> I could normally see the historic runs of the jobs.
> I have a server, for some reason, only hold one historic
> record of each job. Each time a job is run the history is
> then refreshed and delete with only the latest job remain.
See How to resize the job history log (Enterprise Manager)
http://msdn.microsoft.com/library/d...>
aem_8xpj.asp
Why not create Your Own SQL Server Job Management System?
http://www.sql-server-performance.com/rd_jobs.asp
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Sebastian
Thanks for this. I have checked and found that the Job
agent setting no different from other servers. I think the
real issue seem to be in the time the job history are
kept.. All the jobs that have only one history are only
run once a month, and I have jobs that run daily - they
have all the job history up to a month.
Alex
>--Original Message--
>Alex Au wrote:
>
history
is
remain.
>
>See How to resize the job history log (Enterprise Manager)
>http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/howtosql/ht_automaem_8xpj.asp
>Why not create Your Own SQL Server Job Management System?
>http://www.sql-server-performance.com/rd_jobs.asp
>sincerely,
>--
>Sebastian K. Zaklada
>Skilled Software
>http://www.skilledsoftware.com
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>
>.
>|||The trouble is that if you have a job that runs very often e.g once every
minute, it can quickly eat up the 1000 rows that is the default job history
log size. This setting applies to the server so even if the job history rows
per job is 100, it will not honour this if the max history log size >1000.
Thus it might be worth lowering the rows per job and increasing the max log
size to find the balance that works for you.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<anonymous@.discussions.microsoft.com> wrote in message
news:2d9401c40075$21391820$a601280a@.phx.gbl...
> Sebastian
> Thanks for this. I have checked and found that the Job
> agent setting no different from other servers. I think the
> real issue seem to be in the time the job history are
> kept.. All the jobs that have only one history are only
> run once a month, and I have jobs that run daily - they
> have all the job history up to a month.
> Alex
> history
> is
> remain.
> url=/library/en-us/howtosql/ht_automaem_8xpj.asp
> confers no rights.|||Thanks Jasper. This is exactly the problem, and your
suggested solution is spot on.
Alex
>--Original Message--
>The trouble is that if you have a job that runs very
often e.g once every
>minute, it can quickly eat up the 1000 rows that is the
default job history
>log size. This setting applies to the server so even if
the job history rows
>per job is 100, it will not honour this if the max
history log size >1000.
>Thus it might be worth lowering the rows per job and
increasing the max log
>size to find the balance that works for you.
>--
>HTH
>Jasper Smith (SQL Server MVP)
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:2d9401c40075$21391820$a601280a@.phx.gbl...
the
historic
history
Manager)
System?
and
>
>.
>

Question on Job history

Please could someone enlight me.
In enterprise manager -> management-> jobs -> view history
I could normally see the historic runs of the jobs.
I have a server, for some reason, only hold one historic
record of each job. Each time a job is run the history is
then refreshed and delete with only the latest job remain.
Where can I set in Enterprise Manager so I could adjust
the amount of the history held for the jobs?
Thanks
AlexAlex Au wrote:
> Please could someone enlight me.
> In enterprise manager -> management-> jobs -> view history
> I could normally see the historic runs of the jobs.
> I have a server, for some reason, only hold one historic
> record of each job. Each time a job is run the history is
> then refreshed and delete with only the latest job remain.
See How to resize the job history log (Enterprise Manager)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_automaem_8xpj.asp
Why not create Your Own SQL Server Job Management System?
http://www.sql-server-performance.com/rd_jobs.asp
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Sebastian
Thanks for this. I have checked and found that the Job
agent setting no different from other servers. I think the
real issue seem to be in the time the job history are
kept.. All the jobs that have only one history are only
run once a month, and I have jobs that run daily - they
have all the job history up to a month.
Alex
>--Original Message--
>Alex Au wrote:
>> Please could someone enlight me.
>> In enterprise manager -> management-> jobs -> view
history
>> I could normally see the historic runs of the jobs.
>> I have a server, for some reason, only hold one historic
>> record of each job. Each time a job is run the history
is
>> then refreshed and delete with only the latest job
remain.
>
>See How to resize the job history log (Enterprise Manager)
>http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/howtosql/ht_automaem_8xpj.asp
>Why not create Your Own SQL Server Job Management System?
>http://www.sql-server-performance.com/rd_jobs.asp
>sincerely,
>--
>Sebastian K. Zaklada
>Skilled Software
>http://www.skilledsoftware.com
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>
>.
>|||The trouble is that if you have a job that runs very often e.g once every
minute, it can quickly eat up the 1000 rows that is the default job history
log size. This setting applies to the server so even if the job history rows
per job is 100, it will not honour this if the max history log size >1000.
Thus it might be worth lowering the rows per job and increasing the max log
size to find the balance that works for you.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<anonymous@.discussions.microsoft.com> wrote in message
news:2d9401c40075$21391820$a601280a@.phx.gbl...
> Sebastian
> Thanks for this. I have checked and found that the Job
> agent setting no different from other servers. I think the
> real issue seem to be in the time the job history are
> kept.. All the jobs that have only one history are only
> run once a month, and I have jobs that run daily - they
> have all the job history up to a month.
> Alex
> >--Original Message--
> >Alex Au wrote:
> >
> >> Please could someone enlight me.
> >>
> >> In enterprise manager -> management-> jobs -> view
> history
> >> I could normally see the historic runs of the jobs.
> >>
> >> I have a server, for some reason, only hold one historic
> >> record of each job. Each time a job is run the history
> is
> >> then refreshed and delete with only the latest job
> remain.
> >
> >
> >See How to resize the job history log (Enterprise Manager)
> >http://msdn.microsoft.com/library/default.asp?
> url=/library/en-us/howtosql/ht_automaem_8xpj.asp
> >
> >Why not create Your Own SQL Server Job Management System?
> >http://www.sql-server-performance.com/rd_jobs.asp
> >
> >sincerely,
> >--
> >Sebastian K. Zaklada
> >Skilled Software
> >http://www.skilledsoftware.com
> >This posting is provided "AS IS" with no warranties, and
> confers no rights.
> >
> >
> >.
> >|||Thanks Jasper. This is exactly the problem, and your
suggested solution is spot on.
Alex
>--Original Message--
>The trouble is that if you have a job that runs very
often e.g once every
>minute, it can quickly eat up the 1000 rows that is the
default job history
>log size. This setting applies to the server so even if
the job history rows
>per job is 100, it will not honour this if the max
history log size >1000.
>Thus it might be worth lowering the rows per job and
increasing the max log
>size to find the balance that works for you.
>--
>HTH
>Jasper Smith (SQL Server MVP)
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:2d9401c40075$21391820$a601280a@.phx.gbl...
>> Sebastian
>> Thanks for this. I have checked and found that the Job
>> agent setting no different from other servers. I think
the
>> real issue seem to be in the time the job history are
>> kept.. All the jobs that have only one history are only
>> run once a month, and I have jobs that run daily - they
>> have all the job history up to a month.
>> Alex
>> >--Original Message--
>> >Alex Au wrote:
>> >
>> >> Please could someone enlight me.
>> >>
>> >> In enterprise manager -> management-> jobs -> view
>> history
>> >> I could normally see the historic runs of the jobs.
>> >>
>> >> I have a server, for some reason, only hold one
historic
>> >> record of each job. Each time a job is run the
history
>> is
>> >> then refreshed and delete with only the latest job
>> remain.
>> >
>> >
>> >See How to resize the job history log (Enterprise
Manager)
>> >http://msdn.microsoft.com/library/default.asp?
>> url=/library/en-us/howtosql/ht_automaem_8xpj.asp
>> >
>> >Why not create Your Own SQL Server Job Management
System?
>> >http://www.sql-server-performance.com/rd_jobs.asp
>> >
>> >sincerely,
>> >--
>> >Sebastian K. Zaklada
>> >Skilled Software
>> >http://www.skilledsoftware.com
>> >This posting is provided "AS IS" with no warranties,
and
>> confers no rights.
>> >
>> >
>> >.
>> >
>
>.
>

Question on generating SQL scripts using Enterprise Manager

Hi,

I was using enterprise manager to generate a script for my DB. I
scripted only my tables and views and in Options I picked all the
options EXCEPT "script Primary Keys, Foreign Keys and Constraits " (
which I was going to script seperately ). I noticed that the the
generated file still had all FKs and PKs scripted. When I additionally
unchecked the "script Full-Text indexes" option, it worked as expected.
Any idea why the full-text option causes all constraints to be
scripted. Using SQL server 2000.

Thanksdrdeadpan (vkat01-nospam@.yahoo.com) writes:
> I was using enterprise manager to generate a script for my DB. I
> scripted only my tables and views and in Options I picked all the
> options EXCEPT "script Primary Keys, Foreign Keys and Constraits " (
> which I was going to script seperately ). I noticed that the the
> generated file still had all FKs and PKs scripted. When I additionally
> unchecked the "script Full-Text indexes" option, it worked as expected.
> Any idea why the full-text option causes all constraints to be
> scripted. Using SQL server 2000.

Sounds like a bug.

It would be interesting to see a repro. That is a complete database script
with at most three tables with all these features, and when scripted in
EM displays all these problems. I doubt that the bug will ever be fixed
in Enterprise Manager, but since I'm on the SQL 2005 beta, I would like
to test if the problem is there as well.

By the way, did your tables actually have any full-text indexes?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland for your response.

No, we have NO full text indexes defined. Our tables have a rather
large number of columns so rather than pasting the script here ,I
tested it again. This time I picked 3 tables to be scripted with the
following options.

Script Database
Script database users and database roles
Script object-level permissions
Script indexes
Script full-text indexes.

The above options once again scripted all PKs and FKs even though it
was not requested.

I reran the script without the full-text scripting option and it works
fine i.e no Pks and FKs. SO, I guess it is prefectly reproducable on
Sql Server 2000. I just wanted to make sure I was'nt seeing things.
Great website BTW.

DrD|||drdeadpan (vkat01-nospam@.yahoo.com) writes:
> No, we have NO full text indexes defined. Our tables have a rather
> large number of columns so rather than pasting the script here ,I
> tested it again. This time I picked 3 tables to be scripted with the
> following options.
> Script Database
> Script database users and database roles
> Script object-level permissions
> Script indexes
> Script full-text indexes.
> The above options once again scripted all PKs and FKs even though it
> was not requested.

You don't have to post your actual tables. It's enough to post a few
tables for which the problem appears.

Anyway, I was able to reproduce the problem in SQL 2000, but when I did
a quick test in SQL 2005, no constraints were brought it.

As I mentioned earlier, the likelyhood that this will be fixed in SQL2000
is about nil.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Question on generating SQL scripts using Enterprise Manager

Hi,

I was using enterprise manager to generate a script for my DB. I
scripted selected my tables and views and in Options I picked all the
options. I noticed that the
generated file does not include FKs, EXTs or PKs scripted.
Any idea why the full-text options are not scripting the constraints?
Using SQL server 2000.

Thanks(chawes40@.yahoo.com) writes:
> I was using enterprise manager to generate a script for my DB. I
> scripted selected my tables and views and in Options I picked all the
> options. I noticed that the
> generated file does not include FKs, EXTs or PKs scripted.
> Any idea why the full-text options are not scripting the constraints?
> Using SQL server 2000.

Did the database actually have any full-text indexes? I tried to reproduce
the problem according your description, and my script included PKs and
FKs. But I don't even have full-text installed on my machine.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Question on connecting to Oracle

Hello all,

I'm using a OLE DB Connection Manager to Oracle (I've installed the client) and using Oracle Native OLE DB Provider.

The problem was the view In Oracle I'm returning a column that uses a fuction on a numeric field in the the select part of the statement, and by this, the Precition is not displayed when I'm trying to look at the data types of the fields in the view.

The Precision is shown as 0 in the external field part of the component.

I have read the previous post concerning the problem connecting to Oracle Using the Oracle provider, and solved it by using the Microsoft provider, but still I have a question about the oracle provider:

I changed the precision on the External column (from 0 to 15) and also an the Output column, but now I'm getting an error on about error output not matching:

Error 6 Validation error. Data Flow Task: OLE DB Source 1 [8970]: The output column "UPDATE_TIME_NUM" (9333) on the non-error output has no corresponding output column on the error output. Dim_registered_user.dtsx 0 0

I cannot change the error output datatype, cannot delete it or do anything at all.

Is there a way to fix the problem?

This may or may not help you, but I've switch my Oracle connections over to the ADO.NET provider for Oracle. The only side effect is that the resulting data flow is Unicode by default...so all your string data is DT_WSTR instead of DT_STR. You can change that, but it's a manual operation go through all the External Columns and change their type.

I had some weird data issues like you describe above and after poking around the internet came across this article by Donald Farmer (http://www.sqljunkies.com/WebLog/donald_farmer/archive/2005/03/13/8819.aspx). That article explains alot about SSIS Oracle issues.

|||The problem with ADO.NET provider is that it doesn't allow the use of variables as a source SQL

question on AqcuireConnection

Hi,

I created a function that supposedly checks a connection manager if a connection can be established.
When I was testing the code, I found out that the following code always returns "Successful" for flat files and SMTP connection even if the flat file does not even exist or there are no connection parameters for SMTP.

Can you tell me what to use to check if a connection can be established for all types of connection managers?

Here is the function that I created which does not seem to work as I expected:

Public Function connect(ByVal connMgr As Object) As String
Dim connected As String
Try
Dts.Connections(connMgr.name).AcquireConnection(Nothing)
Return "Successful"
Catch ex As Exception
Return "Failure"
Finally
Dts.Connections(connMgr.name).ReleaseConnection(Nothing)
End Try
End Function

Thanks.

AcquireConnection returns an object that represents the connection. You need to check this object. It will be different for each connection manager.

An ADO.NET connection manager will return an IDbConnection object.

The SMTP Connection manager simply returns the connection string of the connection manager.

There isn't a generic way to determine if a connection can be established for all types of connection managers.
|||

A flat file does not need to exist for AcquireConnection to be successful. It can point to a destination file to be created at the runtime.

Thanks.