Alter table db size getting increased.

Hi,
I am doing alter table and updating Nvarchar datatype to Ntext. AS there are
almost
54,09,873 records installation is taking more than 3 hours and log/database
files are getting increased beyond limit. Now there is hardly space left and
still installation is not completed.
Tasks Restore Size After installation
Datafile Size 39000 46529 MB
Log Size 4550 14032 MB
ALTER TABLE [dbo].[xyz] ALTER COLUMN EmailText ntext
54,09,873 Rows affected.
As far as I know and understand Alter basically copy the contents to some
temp table, deletes the table, recreate the table with new definition and
again copy back to original table( Sorry if sequence is wrong ?) and I thi
nk
this is the reason why this script is taking more space and impacting log
size.
IS THERE ANY SOLN OR ALTERNATIVE WHICH CAN MAKE THIS FASTER AND REDUCE THE
SIZE OF DB INCREASE.
--
SanjayYes, you might try dumping the data into an external text file, and then bc
p
or bulk insert it back into the new table structure.
"Sanjay" wrote:

> Hi,
> I am doing alter table and updating Nvarchar datatype to Ntext. AS there a
re
> almost
> 54,09,873 records installation is taking more than 3 hours and log/databas
e
> files are getting increased beyond limit. Now there is hardly space left a
nd
> still installation is not completed.
> Tasks Restore Size After installation
> Datafile Size 39000 46529 MB
> Log Size 4550 14032 MB
> ALTER TABLE [dbo].[xyz] ALTER COLUMN EmailText ntext
> 54,09,873 Rows affected.
> As far as I know and understand Alter basically copy the contents to some
> temp table, deletes the table, recreate the table with new definition and
> again copy back to original table( Sorry if sequence is wrong ?) and I t
hink
> this is the reason why this script is taking more space and impacting log
> size.
> IS THERE ANY SOLN OR ALTERNATIVE WHICH CAN MAKE THIS FASTER AND REDUCE THE
> SIZE OF DB INCREASE.
> --
> Sanjay|||Thanks for your help,
can you help me in syntax and will this be logged in log file.
i never tried that but will this help in reducing time and DB size issues.
"CBretana" wrote:
> Yes, you might try dumping the data into an external text file, and then
bcp
> or bulk insert it back into the new table structure.
> "Sanjay" wrote:
>|||Look up BCP in BOL -- it's a utility that comes w/SQL server.
Setting the logging level to SIMPLE would help too -- look up ALTER
DATABASE; the setting is SET RECOVERY
"Sanjay" wrote:
> Thanks for your help,
> can you help me in syntax and will this be logged in log file.
> i never tried that but will this help in reducing time and DB size issues.
> "CBretana" wrote:
>|||I tried but log file was increased around 5 gb and data file also.
i am doing something like this.
alter database xyz
set recovery simple
alter table and modifying nvarchar to ntext ( around 54,09,873 rows)
set recovery full.
"KH" wrote:
> Look up BCP in BOL -- it's a utility that comes w/SQL server.
> Setting the logging level to SIMPLE would help too -- look up ALTER
> DATABASE; the setting is SET RECOVERY
>
> "Sanjay" wrote:
>

Allowing remote connections during setup?

Is there a way to automatically setup SQL Server Express to allow remote connections during the installation process?

We are deploying SQL Server Express with our application and I really can't ask SMB customers to go through a series of rather complicated steps after our "turn key" setup installs everything in order for any of the other computers in their office to connect.

What's the reasoning behind that, anyway? Why create a database server setup which by default doesn't allow anyone except the server to access it? I guess that makes sense for ASP.NET but the web fad isn't the only platform developers use these days.

Any assistance would be greatly appreciated.

Cheers,

Evan

Evan, have you played around with the DISABLENETWORKPROTOCOLS switch? The default for Express is TCP=Off (1). This is the snippet from BOL.

;--
; The DISABLENETWORKPROTOCOLS switch is used to disable network protocol for SQL Server instance.
; Set DISABLENETWORKPROTOCOLS = 0; for Shared Memory= On, Named Pipe= On, TCP= On
; Set DISABLENETWORKPROTOCOLS = 1; for Shared Memory= On, Named Pipe= Off (Local Only), TCP= Off
; Set DISABLENETWORKPROTOCOLS = 2; for Shared Memory= On, Named Pipe= Off (Local Only), TCP= On

; Note: DISABLENETWORKPROTOCOLS if not specified has the following defaults.
; Default value for SQL Server Express/Evaluation/Developer: DISABLENETWORKPROTOCOLS =1
; Default value for Enterprise/Standard /Workgroup: DISABLENETWORKPROTOCOLS =2

Thanks,
Samuel Lester (MSFT)

Allow both Anonymous Access and Windows Integrated Access on RS

How can I configure IIS to allow anonymous and integrated requests to a
single Reporting Services 2005 installation?
If I try to mix the two securities (anonymous access and Windows integrated
authentication), all requests run as anonymous (under the IUSR account) and
therefore I get an access denied errors when trying to access a report/folder
that does not allow IUSR as a report "browser").
I have tried to set up a separate web site on the IIS box that points to the
Reporting Sever folders that implements either security; but with out
success. It appears that RS 2005 requires to be installed on the "Default
Web Site"?
--
LulkTwo different questions here. The two websites that need to be running as
integrated are: reports and reportserver. Your other websites can run
anonoymous.
Your second question: As far as the default website. The install for 2000
made it very difficult to not have it as the default. However, with 2005
this should be possible. I haven't done it. For this question (how to
install where not the default website), I suggest another posting so someone
will know your question and can answer it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Lulk" <Lulk@.discussions.microsoft.com> wrote in message
news:05F07710-5C48-47F2-B1BC-EA8E8D768A29@.microsoft.com...
> How can I configure IIS to allow anonymous and integrated requests to a
> single Reporting Services 2005 installation?
> If I try to mix the two securities (anonymous access and Windows
> integrated
> authentication), all requests run as anonymous (under the IUSR account)
> and
> therefore I get an access denied errors when trying to access a
> report/folder
> that does not allow IUSR as a report "browser").
> I have tried to set up a separate web site on the IIS box that points to
> the
> Reporting Sever folders that implements either security; but with out
> success. It appears that RS 2005 requires to be installed on the "Default
> Web Site"?
> --
> Lulk|||I have a single web site set up to strictly to access Reporting Services.
One I want to run Integrated and allow Anonymous. I tried creating a separate
web site that points to the same app directories, but that's not working
cleanly.
--
Lulk
"Bruce L-C [MVP]" wrote:
> Two different questions here. The two websites that need to be running as
> integrated are: reports and reportserver. Your other websites can run
> anonoymous.
> Your second question: As far as the default website. The install for 2000
> made it very difficult to not have it as the default. However, with 2005
> this should be possible. I haven't done it. For this question (how to
> install where not the default website), I suggest another posting so someone
> will know your question and can answer it.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Lulk" <Lulk@.discussions.microsoft.com> wrote in message
> news:05F07710-5C48-47F2-B1BC-EA8E8D768A29@.microsoft.com...
> > How can I configure IIS to allow anonymous and integrated requests to a
> > single Reporting Services 2005 installation?
> > If I try to mix the two securities (anonymous access and Windows
> > integrated
> > authentication), all requests run as anonymous (under the IUSR account)
> > and
> > therefore I get an access denied errors when trying to access a
> > report/folder
> > that does not allow IUSR as a report "browser").
> > I have tried to set up a separate web site on the IIS box that points to
> > the
> > Reporting Sever folders that implements either security; but with out
> > success. It appears that RS 2005 requires to be installed on the "Default
> > Web Site"?
> > --
> > Lulk
>
>|||You cannot run both anonymous and integrated. That just doesn't work. Once
you are anonymous you are anoymous.
If you are in an intranet environment, most domains have a generic Users
group which is everybody in that domain. That would allow you to let
everyone access to the report.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Lulk" <Lulk@.discussions.microsoft.com> wrote in message
news:E2CC088F-2D45-4BD1-91C7-409823061287@.microsoft.com...
>I have a single web site set up to strictly to access Reporting Services.
> One I want to run Integrated and allow Anonymous. I tried creating a
> separate
> web site that points to the same app directories, but that's not working
> cleanly.
> --
> Lulk
>
> "Bruce L-C [MVP]" wrote:
>> Two different questions here. The two websites that need to be running as
>> integrated are: reports and reportserver. Your other websites can run
>> anonoymous.
>> Your second question: As far as the default website. The install for 2000
>> made it very difficult to not have it as the default. However, with 2005
>> this should be possible. I haven't done it. For this question (how to
>> install where not the default website), I suggest another posting so
>> someone
>> will know your question and can answer it.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Lulk" <Lulk@.discussions.microsoft.com> wrote in message
>> news:05F07710-5C48-47F2-B1BC-EA8E8D768A29@.microsoft.com...
>> > How can I configure IIS to allow anonymous and integrated requests to a
>> > single Reporting Services 2005 installation?
>> > If I try to mix the two securities (anonymous access and Windows
>> > integrated
>> > authentication), all requests run as anonymous (under the IUSR account)
>> > and
>> > therefore I get an access denied errors when trying to access a
>> > report/folder
>> > that does not allow IUSR as a report "browser").
>> > I have tried to set up a separate web site on the IIS box that points
>> > to
>> > the
>> > Reporting Sever folders that implements either security; but with out
>> > success. It appears that RS 2005 requires to be installed on the
>> > "Default
>> > Web Site"?
>> > --
>> > Lulk
>>