Relative path for child packages

hello again!, this time I'm trying to run a Master Package from the SQL Server Agent but I can't set relative paths to the connections for all the child packages that the master package contains.
It only finishes execution when I set absolute paths for all connections in the connection manager within the SSIS Project.

Is there any property in the SQL Server Agent or mayby a workaround to solve this?
Use absolute paths. Any reason you need relative paths?|||

Hi Santiago,

Unfortunately you cannot pass parameters to SQL Agent jobs. Workarounds usually involve:

1. Package configurations: set the path in a variable and use Expressions in the Package Connection Managers to dynamically apply the variable value.

2. Call the package dynamically: use a stored procedure to build a "dtexec" command-line and execute it via xp_cmdshell.

There are other ways to accomplish this as well. Personally, I recommend package configurations. I use xp_cmdshell less and less these days and when I do I enable it, do what I need to do, and disable it.

Hope this helps,

Andy

|||You can use a variable and expression-based connection strings if you need the paths to be dynamic at runtime. I usually store a root path in a variable, then use expressions to prefix that onto the name of the child package before calling it.|||

jwelch wrote:

You can use a variable and expression-based connection strings if you need the paths to be dynamic at runtime. I usually store a root path in a variable, then use expressions to prefix that onto the name of the child package before calling it.

Same here. This is (one of) the reason(s) that I always use the same folder structure on all of my projects.

Common folder structure

(http://blogs.conchango.com/jamiethomson/archive/2006/01/05/SSIS_3A00_-Common-folder-structure.aspx)

I also include the root path variable in my package template.

SSIS: Package Template

(http://blogs.conchango.com/jamiethomson/archive/2007/03/11/SSIS_3A00_-Package-Template.aspx)

-Jamie

Relationships between Report and SubReport

Hi,
Is there any way to establish a relationship between multiple datasets
generated at the report level?
For example is it possible to run two queries and reference the columns to
do in memory joins? The client we are working for has a poor data structure
and unfortunately our hands are tied so we need to work with multiple
queries across datasets.
Thanks for any info!
RonThe way you do this is with subreports. There is no way to join in memory
datasets.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:u%23Fc%23e04HHA.5316@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Is there any way to establish a relationship between multiple datasets
> generated at the report level?
> For example is it possible to run two queries and reference the columns to
> do in memory joins? The client we are working for has a poor data
> structure and unfortunately our hands are tied so we need to work with
> multiple queries across datasets.
> Thanks for any info!
> Ron
>

reinstalling SQL ODBC Drivers on Windows XP

Apparently one of my users lost their ODBC drivers for SQL Server.
When I try to run the program that uses those drivers obviously I get
all kind of errors.
I tried going into the ODBC administrator and when I try to edit or
delete the DSN I get a message about the drivers being missing and I
need to reinstall them.
I tried running MDAC 2.7 and everything ran, but I still get the error.
MDAC 2.8 will not run on that machine - it gives a message saying that
Windows XP already has all of the files associated with MDAC 2.8.
Any ideas on how to reinstall the drivers in a case like this?
Thanks."Matt Bateman" <mattcb@.gmail.com> wrote in message
news:1155132832.189431.183410@.n13g2000cwa.googlegroups.com...
> Apparently one of my users lost their ODBC drivers for SQL Server.
> When I try to run the program that uses those drivers obviously I get
> all kind of errors.
> I tried going into the ODBC administrator and when I try to edit or
> delete the DSN I get a message about the drivers being missing and I
> need to reinstall them.
> I tried running MDAC 2.7 and everything ran, but I still get the error.
> MDAC 2.8 will not run on that machine - it gives a message saying that
> Windows XP already has all of the files associated with MDAC 2.8.
> Any ideas on how to reinstall the drivers in a case like this?
>
Not really, but you can install the SQL Native Client, which contains a
brand-new, MDAC-independant ODBC driver for SQL Server.
Microsoft SQL Server Native Client
http://msdn.microsoft.com/data/ref/sqlnative/
David

reinstalling SQL ODBC Drivers on Windows XP

Apparently one of my users lost their ODBC drivers for SQL Server.
When I try to run the program that uses those drivers obviously I get
all kind of errors.
I tried going into the ODBC administrator and when I try to edit or
delete the DSN I get a message about the drivers being missing and I
need to reinstall them.
I tried running MDAC 2.7 and everything ran, but I still get the error.
MDAC 2.8 will not run on that machine - it gives a message saying that
Windows XP already has all of the files associated with MDAC 2.8.
Any ideas on how to reinstall the drivers in a case like this?
Thanks."Matt Bateman" <mattcb@.gmail.com> wrote in message
news:1155132832.189431.183410@.n13g2000cwa.googlegroups.com...
> Apparently one of my users lost their ODBC drivers for SQL Server.
> When I try to run the program that uses those drivers obviously I get
> all kind of errors.
> I tried going into the ODBC administrator and when I try to edit or
> delete the DSN I get a message about the drivers being missing and I
> need to reinstall them.
> I tried running MDAC 2.7 and everything ran, but I still get the error.
> MDAC 2.8 will not run on that machine - it gives a message saying that
> Windows XP already has all of the files associated with MDAC 2.8.
> Any ideas on how to reinstall the drivers in a case like this?
>
Not really, but you can install the SQL Native Client, which contains a
brand-new, MDAC-independant ODBC driver for SQL Server.
Microsoft SQL Server Native Client
http://msdn.microsoft.com/data/ref/sqlnative/
Davidsql

reinstalling reporting services

so i uninstalled reporting services and rebooted

now when i run setup it doesnt even ask me for any options to fill out, it just runs for a minute and says done, but it doesn't really install anything (IIS sites, DB, etc...)

i deleted the DBs, the IIS sites, and the files left over. i did everything except delete stuff in the registry

what do i have to do to get it to reinstall from scratch?

i tried using the .ini file and using the command line utility with no luckwelcome to the hell that Reporting Services 2000. read chapter 7 of this...

http://www.awprofessional.com/articles/article.asp?p=357694&seqNum=1&rl=1

and then read the whole thing.

SQL 2005 reporting Services is a lot more agreeable.|||so i uninstalled reporting services and rebooted

now when i run setup it doesnt even ask me for any options to fill out, it just runs for a minute and says done, but it doesn't really install anything (IIS sites, DB, etc...)

i deleted the DBs, the IIS sites, and the files left over. i did everything except delete stuff in the registry

what do i have to do to get it to reinstall from scratch?

i tried using the .ini file and using the command line utility with no luck

Check your Sql server Service pack as well as Windows server service pack.It happened to me also.Reboot and Install it again...|||Check your Sql server Service pack as well as Windows server service pack.It happened to me also.Reboot and Install it again...

using all of the latest service packs, and i rebooted twice with the same results :o|||welcome to the hell that Reporting Services 2000. read chapter 7 of this...

http://www.awprofessional.com/articles/article.asp?p=357694&seqNum=1&rl=1

and then read the whole thing.

SQL 2005 reporting Services is a lot more agreeable.

i did read this chapter, and looked over the whole thing

i also read the entire sql reporting services book from the people at www.sqlreportingservices.net

none of their solutions helped

i think i am going to try an unattended installation to try to force it to use my ini file, after that i'm out of ideas|||Some info for your help...

Renaming a computer causes a corresponding name change for the Web server and SQL Server instance (if it is on the same computer).

In some cases, Reporting Services is not functional after a computer name change. If you are running a Windows 2000 server and the ReportServer Windows service runs as Local System, the ReportServer Windows service no longer starts automatically when you restart the computer. If you are running Windows Server 2003, the service will start, but you cannot connect to the report server database. To restore service after a computer name change, follow these steps:

Run rsconfig at the command line to update the encrypted connection information in the report server database. Use this syntax when running rsconfig (your values may be different).

rsconfig -s<NewServerName> -dreportserver -aSQL -uSA -p<SAPassword>

Reset IIS. To do this at the command line, type iisreset.

In the RSWebApplication.config file, modify the ReportServerURL setting to reflect the new server name.

In the RSReportServer.config file, modify the URLRoot setting to reflect the new server name.

Update the URL property of the program item used to access Report Manager from the Start menu. To do this, point to Start, point to Program Files, point to Microsoft SQL Server, point to Reporting Services, right-click on Report Manager. On the Web Document page, update the URL to the new server name.
Open Report Manager and update any shared data sources or report-specific data sources that contain references to the old computer name.
If you are running Windows 2000 server, perform these additional steps:

Manually start the ReportServer Windows service.

Run rsactivate at the command line. Use this syntax when running rsactivate (note that your actual values may be different if you did not install to the default path).

rsactivate -c"%installdir%\Reporting

Services\ReportServer\RSReportServer.config"

Reset IIS. To do this at the command line, type iisreset.

For a period of time, the report server and Report Manager may continue to be available under the previous name if you are using Windows Internet Naming Service (WINS) on your corporate network. WINS maps an IP address to each computer it services. Once WINS refreshes the IP address for the renamed computer, the old computer name can no longer be used to access a report server or Report Manager.|||i did this as well, the problem is that RSCONFIG does not support Named Instances as per Microsoft's website. plus the config files are envrypted - i dont think you can see server names listed in the config files, i remember checking before i did anything

it has to be reinstalled through the GUI which does support named instances

i solved the problem to my issue once again:

asp.net 2.0 was installed on this machine as well, which caused the registry key RootVer to be set to ASP 2.0. This is why the install package was saying that 1.1 was not installed (even though it was). YOu have to temporarily change the RootVer key to 1.1, install, and then set it back. This allows you to reinstall report server etc...

[\\HKLM\SOFTWARE\Microsoft\ASP.NET\RootVer] = "1.1.4322.573"

re-installing Client Tools

I run a medical software and the software company messed
something up and now they say that I need to re-install
the client tools. I own Small Business Server 2000 that
came with sql 2000. The software company said that the
Client Tools are located on disk 2 of sql 2000 cd pak.
But I dont have those all I got were the Small Business
Server 2000 cd's. On disk 2 there is a sql2000 directory
but I don't see the client tools directory. Can anyone
help??
Charles wrote:
> I run a medical software and the software company messed
> something up and now they say that I need to re-install
> the client tools. I own Small Business Server 2000 that
> came with sql 2000. The software company said that the
> Client Tools are located on disk 2 of sql 2000 cd pak.
> But I dont have those all I got were the Small Business
> Server 2000 cd's. On disk 2 there is a sql2000 directory
> but I don't see the client tools directory. Can anyone
> help??
Just run whatever SQL Server installation CD you have. The client tools
are a part of the main installation and can be selected as the install
only option once the installation starts. Then download the and install
the latest SQL Server service pack on the client to update the tools.
David Gugick
Imceda Software
www.imceda.com
sql

Reindexing tables with computed columns

I need to reindex a table with a computed column. The column is not
included in any indexes, but when I run the DBCC it crashes with the
following error:
DBCC failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER'.
Any ideas on how I can reindex these tables?
Thanks!
Richard
*** Sent via Developersdex http://www.codecomments.com ***
Richard,
Sounds like the QUOTED_IDENTIFIER option needs to be ON.
Try the last section of this link:
Creating Indexes on Computed Columns
http://msdn.microsoft.com/library/de...es_05_8os3.asp
HTH
Jerry
"Richard" <nospam@.devdex.com> wrote in message
news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>I need to reindex a table with a computed column. The column is not
> included in any indexes, but when I run the DBCC it crashes with the
> following error:
> DBCC failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> Any ideas on how I can reindex these tables?
> Thanks!
> Richard
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||If you're using a non-named instance and are running SP4, you can use
a -supportcomputedcolumn parameter in the first step of the job. If you're
using < SP4 or a named instance, you'll have to create a separate job to
execute the integrity/optimizations. See
http://support.microsoft.com/default...b;en-us;902388
I had this trouble in a Sharepoint database. I created a separate job with
two steps, one for integrity checks and one for reorg on all tables. This
KB will give you the script to reorg all tables
http://support.microsoft.com/kb/301292/
HTH
--Lori
"Richard" <nospam@.devdex.com> wrote in message
news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>I need to reindex a table with a computed column. The column is not
> included in any indexes, but when I run the DBCC it crashes with the
> following error:
> DBCC failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> Any ideas on how I can reindex these tables?
> Thanks!
> Richard
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||Note that the scripts reorgs whether the index is fragmented or not (just as maint wiz does). If you
only want to reorg if there is any fragmentation in the first place, you should use the sample code
provided in Books Online, DBCC SHOWCONTIG.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Lori Clark" <lclark@.dbadvisor.com> wrote in message news:eCYr0NM2FHA.3864@.TK2MSFTNGP12.phx.gbl...
> If you're using a non-named instance and are running SP4, you can use a -supportcomputedcolumn
> parameter in the first step of the job. If you're using < SP4 or a named instance, you'll have to
> create a separate job to execute the integrity/optimizations. See
> http://support.microsoft.com/default...b;en-us;902388
> I had this trouble in a Sharepoint database. I created a separate job with two steps, one for
> integrity checks and one for reorg on all tables. This KB will give you the script to reorg all
> tables
> http://support.microsoft.com/kb/301292/
>
> HTH
> --Lori
> "Richard" <nospam@.devdex.com> wrote in message news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>

Reindexing tables with computed columns

I need to reindex a table with a computed column. The column is not
included in any indexes, but when I run the DBCC it crashes with the
following error:
DBCC failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER'.
Any ideas on how I can reindex these tables'
Thanks!
Richard
*** Sent via Developersdex http://www.codecomments.com ***Richard,
Sounds like the QUOTED_IDENTIFIER option needs to be ON.
Try the last section of this link:
Creating Indexes on Computed Columns
http://msdn.microsoft.com/library/d...>
_05_8os3.asp
HTH
Jerry
"Richard" <nospam@.devdex.com> wrote in message
news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>I need to reindex a table with a computed column. The column is not
> included in any indexes, but when I run the DBCC it crashes with the
> following error:
> DBCC failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> Any ideas on how I can reindex these tables'
> Thanks!
> Richard
>
> *** Sent via Developersdex http://www.codecomments.com ***|||If you're using a non-named instance and are running SP4, you can use
a -supportcomputedcolumn parameter in the first step of the job. If you're
using < SP4 or a named instance, you'll have to create a separate job to
execute the integrity/optimizations. See
http://support.microsoft.com/defaul...kb;en-us;902388
I had this trouble in a Sharepoint database. I created a separate job with
two steps, one for integrity checks and one for reorg on all tables. This
KB will give you the script to reorg all tables
http://support.microsoft.com/kb/301292/
HTH
--Lori
"Richard" <nospam@.devdex.com> wrote in message
news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>I need to reindex a table with a computed column. The column is not
> included in any indexes, but when I run the DBCC it crashes with the
> following error:
> DBCC failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> Any ideas on how I can reindex these tables'
> Thanks!
> Richard
>
> *** Sent via Developersdex http://www.codecomments.com ***|||Note that the scripts reorgs whether the index is fragmented or not (just as
maint wiz does). If you
only want to reorg if there is any fragmentation in the first place, you sho
uld use the sample code
provided in Books Online, DBCC SHOWCONTIG.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Lori Clark" <lclark@.dbadvisor.com> wrote in message news:eCYr0NM2FHA.3864@.TK2MSFTNGP12.phx.
gbl...
> If you're using a non-named instance and are running SP4, you can use a -s
upportcomputedcolumn
> parameter in the first step of the job. If you're using < SP4 or a named
instance, you'll have to
> create a separate job to execute the integrity/optimizations. See
> http://support.microsoft.com/defaul...kb;en-us;902388
> I had this trouble in a Sharepoint database. I created a separate job wit
h two steps, one for
> integrity checks and one for reorg on all tables. This KB will give you t
he script to reorg all
> tables
> http://support.microsoft.com/kb/301292/
>
> HTH
> --Lori
> "Richard" <nospam@.devdex.com> wrote in message news:%23Qes6HM2FHA.460@.TK2M
SFTNGP15.phx.gbl...
>

Reindexing tables with computed columns

I need to reindex a table with a computed column. The column is not
included in any indexes, but when I run the DBCC it crashes with the
following error:
DBCC failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER'.
Any ideas on how I can reindex these tables'
Thanks!
Richard
*** Sent via Developersdex http://www.developersdex.com ***Richard,
Sounds like the QUOTED_IDENTIFIER option needs to be ON.
Try the last section of this link:
Creating Indexes on Computed Columns
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_8os3.asp
HTH
Jerry
"Richard" <nospam@.devdex.com> wrote in message
news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>I need to reindex a table with a computed column. The column is not
> included in any indexes, but when I run the DBCC it crashes with the
> following error:
> DBCC failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> Any ideas on how I can reindex these tables'
> Thanks!
> Richard
>
> *** Sent via Developersdex http://www.developersdex.com ***|||If you're using a non-named instance and are running SP4, you can use
a -supportcomputedcolumn parameter in the first step of the job. If you're
using < SP4 or a named instance, you'll have to create a separate job to
execute the integrity/optimizations. See
http://support.microsoft.com/default.aspx?scid=kb;en-us;902388
I had this trouble in a Sharepoint database. I created a separate job with
two steps, one for integrity checks and one for reorg on all tables. This
KB will give you the script to reorg all tables
http://support.microsoft.com/kb/301292/
HTH
--Lori
"Richard" <nospam@.devdex.com> wrote in message
news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>I need to reindex a table with a computed column. The column is not
> included in any indexes, but when I run the DBCC it crashes with the
> following error:
> DBCC failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> Any ideas on how I can reindex these tables'
> Thanks!
> Richard
>
> *** Sent via Developersdex http://www.developersdex.com ***|||Note that the scripts reorgs whether the index is fragmented or not (just as maint wiz does). If you
only want to reorg if there is any fragmentation in the first place, you should use the sample code
provided in Books Online, DBCC SHOWCONTIG.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Lori Clark" <lclark@.dbadvisor.com> wrote in message news:eCYr0NM2FHA.3864@.TK2MSFTNGP12.phx.gbl...
> If you're using a non-named instance and are running SP4, you can use a -supportcomputedcolumn
> parameter in the first step of the job. If you're using < SP4 or a named instance, you'll have to
> create a separate job to execute the integrity/optimizations. See
> http://support.microsoft.com/default.aspx?scid=kb;en-us;902388
> I had this trouble in a Sharepoint database. I created a separate job with two steps, one for
> integrity checks and one for reorg on all tables. This KB will give you the script to reorg all
> tables
> http://support.microsoft.com/kb/301292/
>
> HTH
> --Lori
> "Richard" <nospam@.devdex.com> wrote in message news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>>I need to reindex a table with a computed column. The column is not
>> included in any indexes, but when I run the DBCC it crashes with the
>> following error:
>> DBCC failed because the following SET options have incorrect settings:
>> 'QUOTED_IDENTIFIER'.
>> Any ideas on how I can reindex these tables'
>> Thanks!
>> Richard
>>
>> *** Sent via Developersdex http://www.developersdex.com ***
>

Reindexing 27GB table

On one of the database, I support has 16957722 records in a table and it's
clustered index size is 27606.30.
I used to run the Reindex job created on weekly Sunday starting at 11:00 PM.
It takes more than 7 hours.
Since it is a 24x7 server, when Reindexing job runs it causes blocks so I am
cancelling the job.
In this situation what would you suggest to reindex the table of
such big one ( 27 GB ) ?
Appreciate your advise.
--
Thanks
S SIVAPRASADFirst you need to determine whether you benefit from reindexing. See
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx.[/url
]
To check frag level use DBCC SHOWCONTIG (2000) or sys.dm_db_index_physical_s
tats (2005).
In 2000, you have little options of you want to do this "online", your optio
n is DBCC INDEXDEFRAG.
In 2005, you can use either ALTER INDEX with REORGANIZE (same as INDEXDEFRAG
), or use ALTER INDEX
with REBUILD, ONLINE (same as DBREINDEX, except for ONLINE which is new opti
on).
All have pro and cons, one thing to consider is the usage of transaction log
space (which depends on
what type of command you run and can also be influenced by recovery model).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"S Siva Prasad [SIVA]" <SSivaPrasadSIVA@.discussions.microsoft.com> wrote
in message
news:2484E248-2FCD-47F8-BACB-0AB9E71DEAC3@.microsoft.com...
> On one of the database, I support has 16957722 records in a table and it's
> clustered index size is 27606.30.
> I used to run the Reindex job created on weekly Sunday starting at 11:00 P
M.
> It takes more than 7 hours.
> Since it is a 24x7 server, when Reindexing job runs it causes blocks so I
am
> cancelling the job.
> In this situation what would you suggest to reindex the table of
> such big one ( 27 GB ) ?
> Appreciate your advise.
> --
> Thanks
> S SIVAPRASAD|||It is on SQL Server 2000 Enterprise Edition with SP4 (8.00.2040) .
Database is on Full recovery model and it has enough space for Log file
growth.
DBCC SHOWCONITG() ran and rebuilding of the indexes is necessary now.
Let me know what would be the best optimal way of rebuilding the indexes.
The box is on 24x7 and I want to rebuild the index as quick as possible.
The clustered index sized around 27 GB.
--
Thanks
S SIVAPRASAD
"Tibor Karaszi" wrote:

> First you need to determine whether you benefit from reindexing. See
> [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx.[/u
rl]
> To check frag level use DBCC SHOWCONTIG (2000) or sys.dm_db_index_physical
_stats (2005).
> In 2000, you have little options of you want to do this "online", your opt
ion is DBCC INDEXDEFRAG.
> In 2005, you can use either ALTER INDEX with REORGANIZE (same as INDEXDEFR
AG), or use ALTER INDEX
> with REBUILD, ONLINE (same as DBREINDEX, except for ONLINE which is new op
tion).
> All have pro and cons, one thing to consider is the usage of transaction l
og space (which depends on
> what type of command you run and can also be influenced by recovery model)
.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "S Siva Prasad [SIVA]" <SSivaPrasadSIVA@.discussions.microsoft.com> wro
te in message
> news:2484E248-2FCD-47F8-BACB-0AB9E71DEAC3@.microsoft.com...
>
>|||There's no "optimal way". There are different options, all with advantages a
nd disadvantages. Based
on your requirements, you can weigh the advantages to the disadvantages and
pick the one that suits
your particular need best. Make sure you understand the pros and cons with e
ach method and read the
whitepaper I posted a link to.
Having said that, if the box is 24*7, indexdefrag might be better. But it ca
n take longer time than
dbreindex (but it doesn't block nearly as much), it can produce more log rec
ords, and the
defragmentation might not be as perfect as dbreindex.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"S Siva Prasad [SIVA]" <SSivaPrasadSIVA@.discussions.microsoft.com> wrote
in message
news:8A93A9FA-E357-475D-BB40-8F9BE0A43CCF@.microsoft.com...[vbcol=seagreen]
> It is on SQL Server 2000 Enterprise Edition with SP4 (8.00.2040) .
> Database is on Full recovery model and it has enough space for Log file
> growth.
> DBCC SHOWCONITG() ran and rebuilding of the indexes is necessary now.
> Let me know what would be the best optimal way of rebuilding the indexes.
> The box is on 24x7 and I want to rebuild the index as quick as possible.
> The clustered index sized around 27 GB.
> --
> Thanks
> S SIVAPRASAD
>
> "Tibor Karaszi" wrote:
>

Reindexing 27GB table

On one of the database, I support has 16957722 records in a table and it's
clustered index size is 27606.30.
I used to run the Reindex job created on weekly Sunday starting at 11:00 PM.
It takes more than 7 hours.
Since it is a 24x7 server, when Reindexing job runs it causes blocks so I am
cancelling the job.
In this situation what would you suggest to reindex the table of
such big one ( 27 GB ) ?
Appreciate your advise.
--
Thanks
S SIVAPRASADFirst you need to determine whether you benefit from reindexing. See
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx.
To check frag level use DBCC SHOWCONTIG (2000) or sys.dm_db_index_physical_stats (2005).
In 2000, you have little options of you want to do this "online", your option is DBCC INDEXDEFRAG.
In 2005, you can use either ALTER INDEX with REORGANIZE (same as INDEXDEFRAG), or use ALTER INDEX
with REBUILD, ONLINE (same as DBREINDEX, except for ONLINE which is new option).
All have pro and cons, one thing to consider is the usage of transaction log space (which depends on
what type of command you run and can also be influenced by recovery model).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"S Siva Prasad [SIVA]" <SSivaPrasadSIVA@.discussions.microsoft.com> wrote in message
news:2484E248-2FCD-47F8-BACB-0AB9E71DEAC3@.microsoft.com...
> On one of the database, I support has 16957722 records in a table and it's
> clustered index size is 27606.30.
> I used to run the Reindex job created on weekly Sunday starting at 11:00 PM.
> It takes more than 7 hours.
> Since it is a 24x7 server, when Reindexing job runs it causes blocks so I am
> cancelling the job.
> In this situation what would you suggest to reindex the table of
> such big one ( 27 GB ) ?
> Appreciate your advise.
> --
> Thanks
> S SIVAPRASAD|||It is on SQL Server 2000 Enterprise Edition with SP4 (8.00.2040) .
Database is on Full recovery model and it has enough space for Log file
growth.
DBCC SHOWCONITG() ran and rebuilding of the indexes is necessary now.
Let me know what would be the best optimal way of rebuilding the indexes.
The box is on 24x7 and I want to rebuild the index as quick as possible.
The clustered index sized around 27 GB.
--
Thanks
S SIVAPRASAD
"Tibor Karaszi" wrote:
> First you need to determine whether you benefit from reindexing. See
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx.
> To check frag level use DBCC SHOWCONTIG (2000) or sys.dm_db_index_physical_stats (2005).
> In 2000, you have little options of you want to do this "online", your option is DBCC INDEXDEFRAG.
> In 2005, you can use either ALTER INDEX with REORGANIZE (same as INDEXDEFRAG), or use ALTER INDEX
> with REBUILD, ONLINE (same as DBREINDEX, except for ONLINE which is new option).
> All have pro and cons, one thing to consider is the usage of transaction log space (which depends on
> what type of command you run and can also be influenced by recovery model).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "S Siva Prasad [SIVA]" <SSivaPrasadSIVA@.discussions.microsoft.com> wrote in message
> news:2484E248-2FCD-47F8-BACB-0AB9E71DEAC3@.microsoft.com...
> > On one of the database, I support has 16957722 records in a table and it's
> > clustered index size is 27606.30.
> >
> > I used to run the Reindex job created on weekly Sunday starting at 11:00 PM.
> > It takes more than 7 hours.
> >
> > Since it is a 24x7 server, when Reindexing job runs it causes blocks so I am
> > cancelling the job.
> >
> > In this situation what would you suggest to reindex the table of
> > such big one ( 27 GB ) ?
> >
> > Appreciate your advise.
> > --
> > Thanks
> > S SIVAPRASAD
>
>|||There's no "optimal way". There are different options, all with advantages and disadvantages. Based
on your requirements, you can weigh the advantages to the disadvantages and pick the one that suits
your particular need best. Make sure you understand the pros and cons with each method and read the
whitepaper I posted a link to.
Having said that, if the box is 24*7, indexdefrag might be better. But it can take longer time than
dbreindex (but it doesn't block nearly as much), it can produce more log records, and the
defragmentation might not be as perfect as dbreindex.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"S Siva Prasad [SIVA]" <SSivaPrasadSIVA@.discussions.microsoft.com> wrote in message
news:8A93A9FA-E357-475D-BB40-8F9BE0A43CCF@.microsoft.com...
> It is on SQL Server 2000 Enterprise Edition with SP4 (8.00.2040) .
> Database is on Full recovery model and it has enough space for Log file
> growth.
> DBCC SHOWCONITG() ran and rebuilding of the indexes is necessary now.
> Let me know what would be the best optimal way of rebuilding the indexes.
> The box is on 24x7 and I want to rebuild the index as quick as possible.
> The clustered index sized around 27 GB.
> --
> Thanks
> S SIVAPRASAD
>
> "Tibor Karaszi" wrote:
>> First you need to determine whether you benefit from reindexing. See
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx.
>> To check frag level use DBCC SHOWCONTIG (2000) or sys.dm_db_index_physical_stats (2005).
>> In 2000, you have little options of you want to do this "online", your option is DBCC
>> INDEXDEFRAG.
>> In 2005, you can use either ALTER INDEX with REORGANIZE (same as INDEXDEFRAG), or use ALTER INDEX
>> with REBUILD, ONLINE (same as DBREINDEX, except for ONLINE which is new option).
>> All have pro and cons, one thing to consider is the usage of transaction log space (which depends
>> on
>> what type of command you run and can also be influenced by recovery model).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "S Siva Prasad [SIVA]" <SSivaPrasadSIVA@.discussions.microsoft.com> wrote in message
>> news:2484E248-2FCD-47F8-BACB-0AB9E71DEAC3@.microsoft.com...
>> > On one of the database, I support has 16957722 records in a table and it's
>> > clustered index size is 27606.30.
>> >
>> > I used to run the Reindex job created on weekly Sunday starting at 11:00 PM.
>> > It takes more than 7 hours.
>> >
>> > Since it is a 24x7 server, when Reindexing job runs it causes blocks so I am
>> > cancelling the job.
>> >
>> > In this situation what would you suggest to reindex the table of
>> > such big one ( 27 GB ) ?
>> >
>> > Appreciate your advise.
>> > --
>> > Thanks
>> > S SIVAPRASAD
>>

re-index takes twoce as long on 2005..

Hi
I run a reindex of a 130GB dabase on SQL 2000 and it takes almost exactly 4
hours. IF I run exactly the same command on a 2005 box against a restore of
exactly the same database, it takes almost exactly 8 hours.
am I missing someting here? does a re-ind on 2005 work differently than on
2000? is it doing something twice that I havent asked it to, and that it must
be doing by default?
Any help would be appreciated.
ta
Hi,
Can you see if you have enough room in LDF file while doing the reindex in
SQL 2005. Otherwise the autogrow will happen
and slow down the reindex command...
Thanks
Hari
"Methodology" <Methodology@.discussions.microsoft.com> wrote in message
news:285AE110-BFF1-4CAF-88A6-616C2D8491C9@.microsoft.com...
> Hi
> I run a reindex of a 130GB dabase on SQL 2000 and it takes almost exactly
> 4
> hours. IF I run exactly the same command on a 2005 box against a restore
> of
> exactly the same database, it takes almost exactly 8 hours.
> am I missing someting here? does a re-ind on 2005 work differently than on
> 2000? is it doing something twice that I havent asked it to, and that it
> must
> be doing by default?
> Any help would be appreciated.
> ta
|||Ive tried 'alter index x rebuild' instead of 'dbcc dbreindex' and its
slightly more thsan an hour quicker on my largest table...I think therefore
problem solved.
ta
"Hari Prasad" wrote:

> Hi,
> Can you see if you have enough room in LDF file while doing the reindex in
> SQL 2005. Otherwise the autogrow will happen
> and slow down the reindex command...
> Thanks
> Hari
> "Methodology" <Methodology@.discussions.microsoft.com> wrote in message
> news:285AE110-BFF1-4CAF-88A6-616C2D8491C9@.microsoft.com...
>
>
|||They use exactly the same code. Is the hardware the same between 2000 and
2005, specifically number of CPUs and IO bandwidth?
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Methodology" <Methodology@.discussions.microsoft.com> wrote in message
news:F3B117CE-C3AF-4FCB-A328-A7FC66B7344B@.microsoft.com...[vbcol=seagreen]
> Ive tried 'alter index x rebuild' instead of 'dbcc dbreindex' and its
> slightly more thsan an hour quicker on my largest table...I think
> therefore
> problem solved.
> ta
> "Hari Prasad" wrote:

reindex and shrinking question

Hi,
I have a large table and would like to run dbcc dbreindex
on the table and also shrink the database. As I understand
it, both of these can be used to remove fragmentation. My
question is what is the correct order? Reindex first then
shrink or vice-versa?
TIA,
JBShrink then reindex. If you do it the other way, the shrink will scatter
your index into the available spaces and you work will have been for naught.
Russell Fields
"JB" <anonymous@.discussions.microsoft.com> wrote in message
news:5f5301c49281$20d2aa10$a601280a@.phx.gbl...
> Hi,
> I have a large table and would like to run dbcc dbreindex
> on the table and also shrink the database. As I understand
> it, both of these can be used to remove fragmentation. My
> question is what is the correct order? Reindex first then
> shrink or vice-versa?
> TIA,
> JB|||Thanks again Russell,
final question - do you need to do shrink, reindex
iteratively until nothing happens, as the reindex will
presumably free up some pages, or are the pages it frees
up already at the end of the file.
TIA,
JB|||Actually ideally you don't want to shrink at all. Shrinking will fragment
your tables and indexes and you need lots of free space in the database to
do operate properly for such things as Reindexing. If you shrink the DB and
remove most or all of the free space then reindex it will just have to grow
the database all over again. If the database grew to that size before it
will most likely need that much room again. The best thing to do is usually
leave it at that size. You can then reindex the tables and get the best
chance of optimized indexes and won't have to worry about the files growing
when you do so.
--
Andrew J. Kelly SQL MVP
"JB" <anonymous@.discussions.microsoft.com> wrote in message
news:607501c492a9$c0a343b0$a501280a@.phx.gbl...
> Thanks again Russell,
> final question - do you need to do shrink, reindex
> iteratively until nothing happens, as the reindex will
> presumably free up some pages, or are the pages it frees
> up already at the end of the file.
> TIA,
> JB
>|||Thanks Andrew,
so the number of page reads would be the same if you do
shrink followed by reindex compared to reindex on its own?
TIA,
JB|||Well the number of pages in the index will be the same given they both use
the same fill factor. But the order in which they get stored on disk may
not be the same.
--
Andrew J. Kelly SQL MVP
"JB" <anonymous@.discussions.microsoft.com> wrote in message
news:68e701c493e7$783bcd10$a501280a@.phx.gbl...
> Thanks Andrew,
> so the number of page reads would be the same if you do
> shrink followed by reindex compared to reindex on its own?
> TIA,
> JB

reindex and shrinking question

Hi,
I have a large table and would like to run dbcc dbreindex
on the table and also shrink the database. As I understand
it, both of these can be used to remove fragmentation. My
question is what is the correct order? Reindex first then
shrink or vice-versa?
TIA,
JB
Shrink then reindex. If you do it the other way, the shrink will scatter
your index into the available spaces and you work will have been for naught.
Russell Fields
"JB" <anonymous@.discussions.microsoft.com> wrote in message
news:5f5301c49281$20d2aa10$a601280a@.phx.gbl...
> Hi,
> I have a large table and would like to run dbcc dbreindex
> on the table and also shrink the database. As I understand
> it, both of these can be used to remove fragmentation. My
> question is what is the correct order? Reindex first then
> shrink or vice-versa?
> TIA,
> JB
|||Thanks again Russell,
final question - do you need to do shrink, reindex
iteratively until nothing happens, as the reindex will
presumably free up some pages, or are the pages it frees
up already at the end of the file.
TIA,
JB
|||Actually ideally you don't want to shrink at all. Shrinking will fragment
your tables and indexes and you need lots of free space in the database to
do operate properly for such things as Reindexing. If you shrink the DB and
remove most or all of the free space then reindex it will just have to grow
the database all over again. If the database grew to that size before it
will most likely need that much room again. The best thing to do is usually
leave it at that size. You can then reindex the tables and get the best
chance of optimized indexes and won't have to worry about the files growing
when you do so.
Andrew J. Kelly SQL MVP
"JB" <anonymous@.discussions.microsoft.com> wrote in message
news:607501c492a9$c0a343b0$a501280a@.phx.gbl...
> Thanks again Russell,
> final question - do you need to do shrink, reindex
> iteratively until nothing happens, as the reindex will
> presumably free up some pages, or are the pages it frees
> up already at the end of the file.
> TIA,
> JB
>
|||Thanks Andrew,
so the number of page reads would be the same if you do
shrink followed by reindex compared to reindex on its own?
TIA,
JB
|||Well the number of pages in the index will be the same given they both use
the same fill factor. But the order in which they get stored on disk may
not be the same.
Andrew J. Kelly SQL MVP
"JB" <anonymous@.discussions.microsoft.com> wrote in message
news:68e701c493e7$783bcd10$a501280a@.phx.gbl...
> Thanks Andrew,
> so the number of page reads would be the same if you do
> shrink followed by reindex compared to reindex on its own?
> TIA,
> JB

Reindex

How do you keep a system running optimum if you can't run
dbcc reindex because it locks the tables? The indexdefrag
has no perceptible performance increase, or very little
compared to rebuilding the indexes.
The db is about 60G. If we cant run the reindex because
of other jobs running all night that would get killed by
it, the speed the next day is affected, searches take
longer, concurrency gets affected, etc.
MichelIt's hard to believe that indexdefrag has little effect. If one nights
worth of work fragments the tables that badly then you might want to change
the fill factor or possibly rethink the clustered index. How fragmented do
the tables get after just one night of work?
--
Andrew J. Kelly
SQL Server MVP
"Michel" <michel@.micro-world.ca> wrote in message
news:081501c37735$a9c52ef0$a101280a@.phx.gbl...
> How do you keep a system running optimum if you can't run
> dbcc reindex because it locks the tables? The indexdefrag
> has no perceptible performance increase, or very little
> compared to rebuilding the indexes.
> The db is about 60G. If we cant run the reindex because
> of other jobs running all night that would get killed by
> it, the speed the next day is affected, searches take
> longer, concurrency gets affected, etc.
> Michel|||dbcc showcontig shows between 10 - 50% fragmentation on
the tables.
Some has high as 100% (log tables with lots of inserts).
The fill factor is 80% on tables. Its really not that much
new data.
All tables have a decent clustered index. It's just a
matter of one or
2 tables. This DB is fully relational with about 300
related tables.
It just seems that when data is inserted, the index just
dosent work
well anymore until I reindex. I may be missing something?
80% fill
factor on a table with 10M rows, mostly queried all day,
maybe 20-50 new
records added not much more. All the related tables get a
few records
here and there inserted. I can give more details but not
sure wich
details to give to help.
Michel
>--Original Message--
>It's hard to believe that indexdefrag has little
effect. If one nights
>worth of work fragments the tables that badly then you
might want to change
>the fill factor or possibly rethink the clustered
index. How fragmented do
>the tables get after just one night of work?
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Michel" <michel@.micro-world.ca> wrote in message
>news:081501c37735$a9c52ef0$a101280a@.phx.gbl...
>> How do you keep a system running optimum if you can't
run
>> dbcc reindex because it locks the tables? The
indexdefrag
>> has no perceptible performance increase, or very little
>> compared to rebuilding the indexes.
>> The db is about 60G. If we cant run the reindex because
>> of other jobs running all night that would get killed by
>> it, the speed the next day is affected, searches take
>> longer, concurrency gets affected, etc.
>> Michel
>
>.
>

ReHigh memory usage-SQLServer 7

Hi to all,
we are using MSSQLServer 7 for both Win2K and Win95/98 OS.
My problem is, that as we run our application overnight,
inserting records in the table, we notice that the memory
usage of sqlserver.exe in the Task Bar Manager keep on
increasing, thus "eats-up" the available physical memory.
How can we limit the memory usage of the sqlserver.exe?
coz in a low physical memory we could not open any
application unless we will stop/restart SQLServer to free-
up mem. we are just using 260MB of RAM.
thank you and more power.Hi Alvin.
SQL Server is designed to use up the available memory on a
computer. This is because it is configured (by default) to
behave like the only software running on the computer.
When installed on a dedicated (so often the case) this is
usually true - you want SQL Server to make use of the
available memory to run queries as quickly as possible.
If you're installing SQL Server on a computer where it is
NOT the only software running (ie not a dedicated server -
such as a desktop), you can configure SQL Server to use a
maximum (and / or a minimum) amount of memory.
Easiest way to do this is from the SQL Enterprise Manager.
As long as you've registered your EM connection to connect
as a systadmin, you should be able to simply right click
on the SQL Server in the EM, select properties, go to the
memory tab & set your memory settings.
HTH
Regards,
Greg Linwood
SQL Server MVP
>--Original Message--
>Hi to all,
>we are using MSSQLServer 7 for both Win2K and Win95/98
OS.
>My problem is, that as we run our application overnight,
>inserting records in the table, we notice that the memory
>usage of sqlserver.exe in the Task Bar Manager keep on
>increasing, thus "eats-up" the available physical memory.
>How can we limit the memory usage of the sqlserver.exe?
>coz in a low physical memory we could not open any
>application unless we will stop/restart SQLServer to free-
>up mem. we are just using 260MB of RAM.
>thank you and more power.
>.
>

Registry settings for service account permissions

We are trying to configure registry settings to allow sql server service to run on a service account in SQL Server 2005. The registry has changed quite a bit from SQL 2000, and we are missing a setting in the software keys that causes sql server service not to start. If we apply permissions to all of HKEY_LOCAL_MACHINE/SOFTWARE, then the service starts, however company security policies do not allow this. Are there any specific keys we should look at, other than the obvious Microsoft/Microsoft SQL Server and Microsoft/MSSQL keys, in which we have already granted permissions to the service account?

Thanks,

Dave

Manually changing the service account is not a supported operation. Changing the service account should only be done via the SQL Server Configuration Manager tool. For further questions about this tool, you can use the SQL Server Tools General forum.

Thanks
Laurentiu

Registering sqldmo.dll

Hi everybody,

I have a problem with registering sqldmo.dll (version 2000) on Win98 on client machine with no sql-server installed.
First I have run the sqlredis.exe and than I have copied all files described in sqlredis.txt into the windows system directory and than I run regsvr32 to register sqldmo.dll. On Win2000 seems to be all OK but on Win98 I got error 0x80040154: DllRegisterServer in sqldmo.dll failed.

Does anybody know what is the source of this problem?Well I have found It!

You have to register atl.dll (for Win9x it have to be ANSI version) before sqldmo.dll - such simply, but Microsoft doesn't mention this nowhere...

registering SQL Server behind firewall

We just implemented a SQL Server, that is in a PDMZ
(public facing dmz). It is using a local account to run
the services as it it not on our corporate network. We
cannot register the server. Does anyone know what ports
need to be open, in order to register this SQL Server? In
talking with our network folks, I'm pretty sure 1433 /
1434 are open. Any ideas...Thanks.
Tom.INF: TCP Ports Needed for Communication to SQL Server Through a Firewall
http://support.microsoft.com/?id=287932
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Tom Mongold" <tom.mongold@.stdreg.com> wrote in message
news:024a01c35606$4072e550$a401280a@.phx.gbl...
We just implemented a SQL Server, that is in a PDMZ
(public facing dmz). It is using a local account to run
the services as it it not on our corporate network. We
cannot register the server. Does anyone know what ports
need to be open, in order to register this SQL Server? In
talking with our network folks, I'm pretty sure 1433 /
1434 are open. Any ideas...Thanks.
Tom.

Register DLL

i want to register some dll's for my application with regards to crystal reports

the application runs off a citrix server...however when i run the regserver32

it produces an error <filename>.dll was loaded but dllregisterserver entry point was not found. the file cannot be registered

As a shot in the dark i wondered whether anyone had encountered this problem and new how to fix it

thanks

frankDo you want to register that dll in local system or remote system?