long running RESTORE

Hi all !
I have 5 GB backup file of DB running in SIMPLE recovery mode on SQL
2000(SP3). It takes a few minutes to restore it on another box which has
enough resurses, but when I tried to do the same on a box that is a bit old
and not so powerfull I just couldn't wait until it's done, - i canceled
restore operation. As a workaround I detached DB file and then attached them
on that box. But I'm still wondering is there another way to restore DB, may
be group by group or ...?
Thanks a lot in advance,
Alex
Backup and restore
and
sp_detach_db and sp_attach_db
are the best options
You could also try using DTS, but that would probably involve much more
effort on your part.
Keith
"Alex" <amakarov_removethis_@.healthmetrx.com> wrote in message
news:10fo5ivqif63e54@.corp.supernews.com...
> Hi all !
> I have 5 GB backup file of DB running in SIMPLE recovery mode on SQL
> 2000(SP3). It takes a few minutes to restore it on another box which has
> enough resurses, but when I tried to do the same on a box that is a bit
old
> and not so powerfull I just couldn't wait until it's done, - i canceled
> restore operation. As a workaround I detached DB file and then attached
them
> on that box. But I'm still wondering is there another way to restore DB,
may
> be group by group or ...?
> --
> Thanks a lot in advance,
> Alex
>
|||Did you restore the database on the 'old box' when the database was not yet
created? If this was the first time, the 5 Gig file needs to be created
first before the restore can start, and depending on the speed of your disk
controllers, this might take some time. When you subsequently restore the
database again, don't delete the old database first.
Since you already have the database on the old box, try restoring using the
normal way, and see if it still takes as long. And by any chance, are you
using compressed folders?
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"Alex" <amakarov_removethis_@.healthmetrx.com> wrote in message
news:10fo5ivqif63e54@.corp.supernews.com...
> Hi all !
> I have 5 GB backup file of DB running in SIMPLE recovery mode on SQL
> 2000(SP3). It takes a few minutes to restore it on another box which has
> enough resurses, but when I tried to do the same on a box that is a bit
old
> and not so powerfull I just couldn't wait until it's done, - i canceled
> restore operation. As a workaround I detached DB file and then attached
them
> on that box. But I'm still wondering is there another way to restore DB,
may
> be group by group or ...?
> --
> Thanks a lot in advance,
> Alex
>
|||Yes, I created DB before restoring with two file groups 2 G each and log
file. And I wasn't using compressed folder. My guess is maybe I wasn't
patient enough although I waited for about half an hour to let it finish.
But that 'old box' is really slow and that could be the case. I'll give it
another try and be more patien this time.
Thank you,
Alex
"Peter Yeoh" <nospam@.nospam.com> wrote in message
news:u2AMSofbEHA.1732@.TK2MSFTNGP09.phx.gbl...
> Did you restore the database on the 'old box' when the database was not
yet
> created? If this was the first time, the 5 Gig file needs to be created
> first before the restore can start, and depending on the speed of your
disk
> controllers, this might take some time. When you subsequently restore the
> database again, don't delete the old database first.
> Since you already have the database on the old box, try restoring using
the[vbcol=seagreen]
> normal way, and see if it still takes as long. And by any chance, are you
> using compressed folders?
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backups? Try MiniSQLBackup
>
> "Alex" <amakarov_removethis_@.healthmetrx.com> wrote in message
> news:10fo5ivqif63e54@.corp.supernews.com...
has
> old
> them
> may
>

long running RESTORE

Hi all !
I have 5 GB backup file of DB running in SIMPLE recovery mode on SQL
2000(SP3). It takes a few minutes to restore it on another box which has
enough resurses, but when I tried to do the same on a box that is a bit old
and not so powerfull I just couldn't wait until it's done, - i canceled
restore operation. As a workaround I detached DB file and then attached them
on that box. But I'm still wondering is there another way to restore DB, may
be group by group or ...?
--
Thanks a lot in advance,
AlexBackup and restore
and
sp_detach_db and sp_attach_db
are the best options
You could also try using DTS, but that would probably involve much more
effort on your part.
Keith
"Alex" <amakarov_removethis_@.healthmetrx.com> wrote in message
news:10fo5ivqif63e54@.corp.supernews.com...
> Hi all !
> I have 5 GB backup file of DB running in SIMPLE recovery mode on SQL
> 2000(SP3). It takes a few minutes to restore it on another box which has
> enough resurses, but when I tried to do the same on a box that is a bit
old
> and not so powerfull I just couldn't wait until it's done, - i canceled
> restore operation. As a workaround I detached DB file and then attached
them
> on that box. But I'm still wondering is there another way to restore DB,
may
> be group by group or ...?
> --
> Thanks a lot in advance,
> Alex
>|||Did you restore the database on the 'old box' when the database was not yet
created? If this was the first time, the 5 Gig file needs to be created
first before the restore can start, and depending on the speed of your disk
controllers, this might take some time. When you subsequently restore the
database again, don't delete the old database first.
Since you already have the database on the old box, try restoring using the
normal way, and see if it still takes as long. And by any chance, are you
using compressed folders?
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"Alex" <amakarov_removethis_@.healthmetrx.com> wrote in message
news:10fo5ivqif63e54@.corp.supernews.com...
> Hi all !
> I have 5 GB backup file of DB running in SIMPLE recovery mode on SQL
> 2000(SP3). It takes a few minutes to restore it on another box which has
> enough resurses, but when I tried to do the same on a box that is a bit
old
> and not so powerfull I just couldn't wait until it's done, - i canceled
> restore operation. As a workaround I detached DB file and then attached
them
> on that box. But I'm still wondering is there another way to restore DB,
may
> be group by group or ...?
> --
> Thanks a lot in advance,
> Alex
>|||Yes, I created DB before restoring with two file groups 2 G each and log
file. And I wasn't using compressed folder. My guess is maybe I wasn't
patient enough although I waited for about half an hour to let it finish.
But that 'old box' is really slow and that could be the case. I'll give it
another try and be more patien this time.
Thank you,
Alex
"Peter Yeoh" <nospam@.nospam.com> wrote in message
news:u2AMSofbEHA.1732@.TK2MSFTNGP09.phx.gbl...
> Did you restore the database on the 'old box' when the database was not
yet
> created? If this was the first time, the 5 Gig file needs to be created
> first before the restore can start, and depending on the speed of your
disk
> controllers, this might take some time. When you subsequently restore the
> database again, don't delete the old database first.
> Since you already have the database on the old box, try restoring using
the
> normal way, and see if it still takes as long. And by any chance, are you
> using compressed folders?
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backups? Try MiniSQLBackup
>
> "Alex" <amakarov_removethis_@.healthmetrx.com> wrote in message
> news:10fo5ivqif63e54@.corp.supernews.com...
has[vbcol=seagreen]
> old
> them
> may
>

long running RESTORE

Hi all !
I have 5 GB backup file of DB running in SIMPLE recovery mode on SQL
2000(SP3). It takes a few minutes to restore it on another box which has
enough resurses, but when I tried to do the same on a box that is a bit old
and not so powerfull I just couldn't wait until it's done, - i canceled
restore operation. As a workaround I detached DB file and then attached them
on that box. But I'm still wondering is there another way to restore DB, may
be group by group or ...?
--
Thanks a lot in advance,
AlexBackup and restore
and
sp_detach_db and sp_attach_db
are the best options
You could also try using DTS, but that would probably involve much more
effort on your part.
--
Keith
"Alex" <amakarov_removethis_@.healthmetrx.com> wrote in message
news:10fo5ivqif63e54@.corp.supernews.com...
> Hi all !
> I have 5 GB backup file of DB running in SIMPLE recovery mode on SQL
> 2000(SP3). It takes a few minutes to restore it on another box which has
> enough resurses, but when I tried to do the same on a box that is a bit
old
> and not so powerfull I just couldn't wait until it's done, - i canceled
> restore operation. As a workaround I detached DB file and then attached
them
> on that box. But I'm still wondering is there another way to restore DB,
may
> be group by group or ...?
> --
> Thanks a lot in advance,
> Alex
>|||Did you restore the database on the 'old box' when the database was not yet
created? If this was the first time, the 5 Gig file needs to be created
first before the restore can start, and depending on the speed of your disk
controllers, this might take some time. When you subsequently restore the
database again, don't delete the old database first.
Since you already have the database on the old box, try restoring using the
normal way, and see if it still takes as long. And by any chance, are you
using compressed folders?
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"Alex" <amakarov_removethis_@.healthmetrx.com> wrote in message
news:10fo5ivqif63e54@.corp.supernews.com...
> Hi all !
> I have 5 GB backup file of DB running in SIMPLE recovery mode on SQL
> 2000(SP3). It takes a few minutes to restore it on another box which has
> enough resurses, but when I tried to do the same on a box that is a bit
old
> and not so powerfull I just couldn't wait until it's done, - i canceled
> restore operation. As a workaround I detached DB file and then attached
them
> on that box. But I'm still wondering is there another way to restore DB,
may
> be group by group or ...?
> --
> Thanks a lot in advance,
> Alex
>|||Yes, I created DB before restoring with two file groups 2 G each and log
file. And I wasn't using compressed folder. My guess is maybe I wasn't
patient enough although I waited for about half an hour to let it finish.
But that 'old box' is really slow and that could be the case. I'll give it
another try and be more patien this time.
--
Thank you,
Alex
"Peter Yeoh" <nospam@.nospam.com> wrote in message
news:u2AMSofbEHA.1732@.TK2MSFTNGP09.phx.gbl...
> Did you restore the database on the 'old box' when the database was not
yet
> created? If this was the first time, the 5 Gig file needs to be created
> first before the restore can start, and depending on the speed of your
disk
> controllers, this might take some time. When you subsequently restore the
> database again, don't delete the old database first.
> Since you already have the database on the old box, try restoring using
the
> normal way, and see if it still takes as long. And by any chance, are you
> using compressed folders?
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backups? Try MiniSQLBackup
>
> "Alex" <amakarov_removethis_@.healthmetrx.com> wrote in message
> news:10fo5ivqif63e54@.corp.supernews.com...
> > Hi all !
> >
> > I have 5 GB backup file of DB running in SIMPLE recovery mode on SQL
> > 2000(SP3). It takes a few minutes to restore it on another box which
has
> > enough resurses, but when I tried to do the same on a box that is a bit
> old
> > and not so powerfull I just couldn't wait until it's done, - i canceled
> > restore operation. As a workaround I detached DB file and then attached
> them
> > on that box. But I'm still wondering is there another way to restore DB,
> may
> > be group by group or ...?
> > --
> >
> > Thanks a lot in advance,
> >
> > Alex
> >
> >
>

Long running reports

We have several business areas that want to run a scheduled batch job to
render reports in a pdf file to them. We seem to be hitting an issue with
reports that take longer than 15 minutes to render.
We get the error message, " The underlying connection was closed: Could not
establish secure channel for SSL/TLS" and "An existing connection was
forcibly closed by the remote host"
I have been able to render the reports from the report manager after I set
the default timeout setting to nothing and let the report churn for in some
cases 45 minutes. We have larger volumes of financial data.
Corporately we are looking at setting a timeout standard and having the
report builders create temp tables to report from for the longer running
reports, but more specifiically I am wondering what are all the possible
timeout setting that could be forcing the connection closed? Report
manager? IIS? and has anyone else dealt with this issue and if so how did
you address it?Hello,
I think this issue is caused by IIS timeout settings:
1. Click "Start", and then click "Control Panel".
2. Double-click "Administrative Tools", and then double-click "Internet
Information Services Manager".
3. Expand "Web Sites", right-click "Default Web Site" , and then click
"Properties".
4. Click the "Web Site" tab.
5. In the "Connections" section, in the "Connection timeout" box, increase
the seconds to a value that is sufficient for the length of the import.
Also, please disable Keep-Alives.
6. Click "OK".
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Long running reports
| thread-index: AcVmNDfsZf6nv6RoTXysyOFdimp4OA==| X-WBNR-Posting-Host: 64.73.98.126
| From: "=?Utf-8?B?TWFyaw==?=" <markwh@.newsgroup.nospam>
| Subject: Long running reports
| Date: Tue, 31 May 2005 15:58:11 -0700
| Lines: 18
| Message-ID: <80219D15-436F-4B8E-AD0D-0C8C604C6B9F@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:45040
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| We have several business areas that want to run a scheduled batch job to
| render reports in a pdf file to them. We seem to be hitting an issue with
| reports that take longer than 15 minutes to render.
|
| We get the error message, " The underlying connection was closed: Could
not
| establish secure channel for SSL/TLS" and "An existing connection was
| forcibly closed by the remote host"
|
| I have been able to render the reports from the report manager after I
set
| the default timeout setting to nothing and let the report churn for in
some
| cases 45 minutes. We have larger volumes of financial data.
|
| Corporately we are looking at setting a timeout standard and having the
| report builders create temp tables to report from for the longer running
| reports, but more specifiically I am wondering what are all the possible
| timeout setting that could be forcing the connection closed? Report
| manager? IIS? and has anyone else dealt with this issue and if so how
did
| you address it?
||||Peter,
Some more information on the specific error and what we have tried. We have
bumped up the timeout settings for the report manager, the IIS settings on
the developers local maching and disabled keep alives. Are their any more
areas where we could be hitting a timeout parameter. While I wait for your
response I will discuss this further with our server team and SQL dba's for
their suggestions.
here is the actual error log.
******************************************************
I increased the Individual timeout for the report to 1800 seconds and the
IIS settings on my local machine, but the report is still timing out.
with KeepAlive enabled or disabled...
----
*****Creating Report GainsChartReport_CM dan.rhoades@.cunamutual.com
Type=Excel Campaign_Code=C000000010 AC_Product_Code=-1 Contract=-1 *****
Program Started at: 6/1/2005 11:16:35 AM
-- Exception Log Entry --
ENVIRONMENT INFORMATION:
TimeStamp: 6/1/2005 11:32:23 AM
MachineName: WWVXP525563
AppDomainName: RenderReports.exe
ThreadIdentity:
WindowsIdentity: CMUTUAL\cc1854
EXCEPTION INFORMATION:
Exception Type: System.Net.WebException
Status: SecureChannelFailure
Message: The underlying connection was closed: Could
not establish secure channel for SSL/TLS.
TargetSite: System.Net.WebResponse
GetWebResponse(System.Net.WebRequest)
Source: System.Web.Services
Stack Trace:
at
System.Web.Services.Protocols.WebClientProtocol.GetWebResponse(WebRequest
request)
at
System.Web.Services.Protocols.HttpWebClientProtocol.GetWebResponse(WebRequest
request)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String
methodName, Object[] parameters)
at RenderReports.ReportingServices.ReportingService.Render(String Report,
String Format, String HistoryID, String DeviceInfo, ParameterValue[]
Parameters, DataSourceCredentials[] Credentials, String ShowHideToggle,
String& Encoding, String& MimeType, ParameterValue[]& ParametersUsed,
Warning[]& Warnings, String[]& StreamIds)
at RenderReports.Main.RenderEmailLinkReport(String[] strParms, Int32
intReportParameters)
INNER EXCEPTION INFORMATION:
Exception Type: System.IO.IOException
Message: Unable to read data from the transport
connection.
TargetSite: Int32 EndRead(System.IAsyncResult)
Source: System
Stack Trace:
at System.Net.TlsStream.EndRead(IAsyncResult asyncResult)
at System.Net.Connection.ReadCallback(IAsyncResult asyncResult)
INNER EXCEPTION INFORMATION:
Exception Type: System.IO.IOException
Message: Unable to read data from the transport
connection.
TargetSite: Int32 EndRead(System.IAsyncResult)
Source: System
Stack Trace:
at System.Net.Sockets.NetworkStream.EndRead(IAsyncResult asyncResult)
at System.Net.TlsStream.AsyncReceiveComplete(IAsyncResult result)
INNER EXCEPTION INFORMATION:
Exception Type: System.Net.Sockets.SocketException
ErrorCode: 10054
NativeErrorCode: 10054
Message: An existing connection was forcibly closed by
the remote host
TargetSite: Int32 EndReceive(System.IAsyncResult)
Source: System
Stack Trace:
at System.Net.Sockets.Socket.EndReceive(IAsyncResult asyncResult)
at System.Net.Sockets.NetworkStream.EndRead(IAsyncResult asyncResult)
ADDITIONAL INFORMATION:
Error Rendering Report: GainsChartReport_CM
-- Exception Entry End --
**
**
** StartReport = 6/1/2005 11:16:36 AM StopReport = 6/1/2005 11:32:23 AM
** Report GainsChartReport_CM failed after 15 minutes 47 seconds
Program Ended at: 6/1/2005 11:32:25 AM
*----*
*******************************************************
"Peter Yang [MSFT]" wrote:
> Hello,
> I think this issue is caused by IIS timeout settings:
> 1. Click "Start", and then click "Control Panel".
> 2. Double-click "Administrative Tools", and then double-click "Internet
> Information Services Manager".
> 3. Expand "Web Sites", right-click "Default Web Site" , and then click
> "Properties".
> 4. Click the "Web Site" tab.
> 5. In the "Connections" section, in the "Connection timeout" box, increase
> the seconds to a value that is sufficient for the length of the import.
> Also, please disable Keep-Alives.
> 6. Click "OK".
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
> --
> | Thread-Topic: Long running reports
> | thread-index: AcVmNDfsZf6nv6RoTXysyOFdimp4OA==> | X-WBNR-Posting-Host: 64.73.98.126
> | From: "=?Utf-8?B?TWFyaw==?=" <markwh@.newsgroup.nospam>
> | Subject: Long running reports
> | Date: Tue, 31 May 2005 15:58:11 -0700
> | Lines: 18
> | Message-ID: <80219D15-436F-4B8E-AD0D-0C8C604C6B9F@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:45040
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | We have several business areas that want to run a scheduled batch job to
> | render reports in a pdf file to them. We seem to be hitting an issue with
> | reports that take longer than 15 minutes to render.
> |
> | We get the error message, " The underlying connection was closed: Could
> not
> | establish secure channel for SSL/TLS" and "An existing connection was
> | forcibly closed by the remote host"
> |
> | I have been able to render the reports from the report manager after I
> set
> | the default timeout setting to nothing and let the report churn for in
> some
> | cases 45 minutes. We have larger volumes of financial data.
> |
> | Corporately we are looking at setting a timeout standard and having the
> | report builders create temp tables to report from for the longer running
> | reports, but more specifiically I am wondering what are all the possible
> | timeout setting that could be forcing the connection closed? Report
> | manager? IIS? and has anyone else dealt with this issue and if so how
> did
> | you address it?
> |
>|||Hello,
You may also want to check if you encounter the following known issue
884537 FIX: You may receive a "System.Net.WebException" error message when
you
http://support.microsoft.com/?id=884537
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Long running reports
| thread-index: AcVmykgAseOX2lghQwamF3cLJGlxpw==| X-WBNR-Posting-Host: 64.73.98.126
| From: "=?Utf-8?B?TWFyaw==?=" <markwh@.newsgroup.nospam>
| References: <80219D15-436F-4B8E-AD0D-0C8C604C6B9F@.microsoft.com>
<xDIz1QoZFHA.3052@.TK2MSFTNGXA01.phx.gbl>
| Subject: RE: Long running reports
| Date: Wed, 1 Jun 2005 09:52:22 -0700
| Lines: 186
| Message-ID: <DB60FC6A-4119-4C9A-948C-BBE8500DA360@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:45109
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Peter,
|
| Some more information on the specific error and what we have tried. We
have
| bumped up the timeout settings for the report manager, the IIS settings
on
| the developers local maching and disabled keep alives. Are their any
more
| areas where we could be hitting a timeout parameter. While I wait for
your
| response I will discuss this further with our server team and SQL dba's
for
| their suggestions.
|
| here is the actual error log.
| ******************************************************
| I increased the Individual timeout for the report to 1800 seconds and the
| IIS settings on my local machine, but the report is still timing out.
|
| with KeepAlive enabled or disabled...
|
|
----
--
| *****Creating Report GainsChartReport_CM dan.rhoades@.cunamutual.com
| Type=Excel Campaign_Code=C000000010 AC_Product_Code=-1 Contract=-1 *****
| Program Started at: 6/1/2005 11:16:35 AM
|
|
| -- Exception Log Entry --
| ENVIRONMENT INFORMATION:
| TimeStamp: 6/1/2005 11:32:23 AM
| MachineName: WWVXP525563
| AppDomainName: RenderReports.exe
| ThreadIdentity:
| WindowsIdentity: CMUTUAL\cc1854
|
| EXCEPTION INFORMATION:
| Exception Type: System.Net.WebException
| Status: SecureChannelFailure
| Message: The underlying connection was closed:
Could
| not establish secure channel for SSL/TLS.
| TargetSite: System.Net.WebResponse
| GetWebResponse(System.Net.WebRequest)
| Source: System.Web.Services
| Stack Trace:
| at
| System.Web.Services.Protocols.WebClientProtocol.GetWebResponse(WebRequest
| request)
| at
|
System.Web.Services.Protocols.HttpWebClientProtocol.GetWebResponse(WebReques
t
| request)
| at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String
| methodName, Object[] parameters)
| at RenderReports.ReportingServices.ReportingService.Render(String
Report,
| String Format, String HistoryID, String DeviceInfo, ParameterValue[]
| Parameters, DataSourceCredentials[] Credentials, String ShowHideToggle,
| String& Encoding, String& MimeType, ParameterValue[]& ParametersUsed,
| Warning[]& Warnings, String[]& StreamIds)
| at RenderReports.Main.RenderEmailLinkReport(String[] strParms, Int32
| intReportParameters)
|
| INNER EXCEPTION INFORMATION:
| Exception Type: System.IO.IOException
| Message: Unable to read data from the transport
| connection.
| TargetSite: Int32 EndRead(System.IAsyncResult)
| Source: System
| Stack Trace:
| at System.Net.TlsStream.EndRead(IAsyncResult asyncResult)
| at System.Net.Connection.ReadCallback(IAsyncResult asyncResult)
|
| INNER EXCEPTION INFORMATION:
| Exception Type: System.IO.IOException
| Message: Unable to read data from the transport
| connection.
| TargetSite: Int32 EndRead(System.IAsyncResult)
| Source: System
| Stack Trace:
| at System.Net.Sockets.NetworkStream.EndRead(IAsyncResult asyncResult)
| at System.Net.TlsStream.AsyncReceiveComplete(IAsyncResult result)
|
| INNER EXCEPTION INFORMATION:
| Exception Type: System.Net.Sockets.SocketException
| ErrorCode: 10054
| NativeErrorCode: 10054
| Message: An existing connection was forcibly closed
by
| the remote host
| TargetSite: Int32 EndReceive(System.IAsyncResult)
| Source: System
| Stack Trace:
| at System.Net.Sockets.Socket.EndReceive(IAsyncResult asyncResult)
| at System.Net.Sockets.NetworkStream.EndRead(IAsyncResult asyncResult)
|
| ADDITIONAL INFORMATION:
| Error Rendering Report: GainsChartReport_CM
|
| -- Exception Entry End --
| **
| **
| ** StartReport = 6/1/2005 11:16:36 AM StopReport = 6/1/2005 11:32:23 AM
| ** Report GainsChartReport_CM failed after 15 minutes 47 seconds
| Program Ended at: 6/1/2005 11:32:25 AM
|
*----
*
| *******************************************************
|
| "Peter Yang [MSFT]" wrote:
|
| > Hello,
| >
| > I think this issue is caused by IIS timeout settings:
| >
| > 1. Click "Start", and then click "Control Panel".
| >
| > 2. Double-click "Administrative Tools", and then double-click "Internet
| > Information Services Manager".
| >
| > 3. Expand "Web Sites", right-click "Default Web Site" , and then click
| > "Properties".
| >
| > 4. Click the "Web Site" tab.
| >
| > 5. In the "Connections" section, in the "Connection timeout" box,
increase
| > the seconds to a value that is sufficient for the length of the import.
| > Also, please disable Keep-Alives.
| >
| > 6. Click "OK".
| >
| > Regards,
| >
| > Peter Yang
| > MCSE2000/2003, MCSA, MCDBA
| > Microsoft Online Partner Support
| >
| > When responding to posts, please "Reply to Group" via your newsreader
so
| > that others may learn and benefit from your issue.
| >
| > =====================================================| >
| > This posting is provided "AS IS" with no warranties, and confers no
rights.
| >
| >
| >
| >
| > --
| > | Thread-Topic: Long running reports
| > | thread-index: AcVmNDfsZf6nv6RoTXysyOFdimp4OA==| > | X-WBNR-Posting-Host: 64.73.98.126
| > | From: "=?Utf-8?B?TWFyaw==?=" <markwh@.newsgroup.nospam>
| > | Subject: Long running reports
| > | Date: Tue, 31 May 2005 15:58:11 -0700
| > | Lines: 18
| > | Message-ID: <80219D15-436F-4B8E-AD0D-0C8C604C6B9F@.microsoft.com>
| > | MIME-Version: 1.0
| > | Content-Type: text/plain;
| > | charset="Utf-8"
| > | Content-Transfer-Encoding: 7bit
| > | X-Newsreader: Microsoft CDO for Windows 2000
| > | Content-Class: urn:content-classes:message
| > | Importance: normal
| > | Priority: normal
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
| > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| > | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| > | Xref: TK2MSFTNGXA01.phx.gbl
microsoft.public.sqlserver.reportingsvcs:45040
| > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
| > |
| > | We have several business areas that want to run a scheduled batch job
to
| > | render reports in a pdf file to them. We seem to be hitting an issue
with
| > | reports that take longer than 15 minutes to render.
| > |
| > | We get the error message, " The underlying connection was closed:
Could
| > not
| > | establish secure channel for SSL/TLS" and "An existing connection was
| > | forcibly closed by the remote host"
| > |
| > | I have been able to render the reports from the report manager after
I
| > set
| > | the default timeout setting to nothing and let the report churn for
in
| > some
| > | cases 45 minutes. We have larger volumes of financial data.
| > |
| > | Corporately we are looking at setting a timeout standard and having
the
| > | report builders create temp tables to report from for the longer
running
| > | reports, but more specifiically I am wondering what are all the
possible
| > | timeout setting that could be forcing the connection closed? Report
| > | manager? IIS? and has anyone else dealt with this issue and if so
how
| > did
| > | you address it?
| > |
| >
| >
||||Thanks Peter,
I will follow up with our VS. net product manager to find out what service
packs we have deployed.
"Peter Yang [MSFT]" wrote:
> Hello,
> You may also want to check if you encounter the following known issue
> 884537 FIX: You may receive a "System.Net.WebException" error message when
> you
> http://support.microsoft.com/?id=884537
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
>
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
> --
> | Thread-Topic: Long running reports
> | thread-index: AcVmykgAseOX2lghQwamF3cLJGlxpw==> | X-WBNR-Posting-Host: 64.73.98.126
> | From: "=?Utf-8?B?TWFyaw==?=" <markwh@.newsgroup.nospam>
> | References: <80219D15-436F-4B8E-AD0D-0C8C604C6B9F@.microsoft.com>
> <xDIz1QoZFHA.3052@.TK2MSFTNGXA01.phx.gbl>
> | Subject: RE: Long running reports
> | Date: Wed, 1 Jun 2005 09:52:22 -0700
> | Lines: 186
> | Message-ID: <DB60FC6A-4119-4C9A-948C-BBE8500DA360@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:45109
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | Peter,
> |
> | Some more information on the specific error and what we have tried. We
> have
> | bumped up the timeout settings for the report manager, the IIS settings
> on
> | the developers local maching and disabled keep alives. Are their any
> more
> | areas where we could be hitting a timeout parameter. While I wait for
> your
> | response I will discuss this further with our server team and SQL dba's
> for
> | their suggestions.
> |
> | here is the actual error log.
> | ******************************************************
> | I increased the Individual timeout for the report to 1800 seconds and the
> | IIS settings on my local machine, but the report is still timing out.
> |
> | with KeepAlive enabled or disabled...
> |
> |
> ----
> --
> | *****Creating Report GainsChartReport_CM dan.rhoades@.cunamutual.com
> | Type=Excel Campaign_Code=C000000010 AC_Product_Code=-1 Contract=-1 *****
> | Program Started at: 6/1/2005 11:16:35 AM
> |
> |
> | -- Exception Log Entry --
> | ENVIRONMENT INFORMATION:
> | TimeStamp: 6/1/2005 11:32:23 AM
> | MachineName: WWVXP525563
> | AppDomainName: RenderReports.exe
> | ThreadIdentity:
> | WindowsIdentity: CMUTUAL\cc1854
> |
> | EXCEPTION INFORMATION:
> | Exception Type: System.Net.WebException
> | Status: SecureChannelFailure
> | Message: The underlying connection was closed:
> Could
> | not establish secure channel for SSL/TLS.
> | TargetSite: System.Net.WebResponse
> | GetWebResponse(System.Net.WebRequest)
> | Source: System.Web.Services
> | Stack Trace:
> | at
> | System.Web.Services.Protocols.WebClientProtocol.GetWebResponse(WebRequest
> | request)
> | at
> |
> System.Web.Services.Protocols.HttpWebClientProtocol.GetWebResponse(WebReques
> t
> | request)
> | at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String
> | methodName, Object[] parameters)
> | at RenderReports.ReportingServices.ReportingService.Render(String
> Report,
> | String Format, String HistoryID, String DeviceInfo, ParameterValue[]
> | Parameters, DataSourceCredentials[] Credentials, String ShowHideToggle,
> | String& Encoding, String& MimeType, ParameterValue[]& ParametersUsed,
> | Warning[]& Warnings, String[]& StreamIds)
> | at RenderReports.Main.RenderEmailLinkReport(String[] strParms, Int32
> | intReportParameters)
> |
> | INNER EXCEPTION INFORMATION:
> | Exception Type: System.IO.IOException
> | Message: Unable to read data from the transport
> | connection.
> | TargetSite: Int32 EndRead(System.IAsyncResult)
> | Source: System
> | Stack Trace:
> | at System.Net.TlsStream.EndRead(IAsyncResult asyncResult)
> | at System.Net.Connection.ReadCallback(IAsyncResult asyncResult)
> |
> | INNER EXCEPTION INFORMATION:
> | Exception Type: System.IO.IOException
> | Message: Unable to read data from the transport
> | connection.
> | TargetSite: Int32 EndRead(System.IAsyncResult)
> | Source: System
> | Stack Trace:
> | at System.Net.Sockets.NetworkStream.EndRead(IAsyncResult asyncResult)
> | at System.Net.TlsStream.AsyncReceiveComplete(IAsyncResult result)
> |
> | INNER EXCEPTION INFORMATION:
> | Exception Type: System.Net.Sockets.SocketException
> | ErrorCode: 10054
> | NativeErrorCode: 10054
> | Message: An existing connection was forcibly closed
> by
> | the remote host
> | TargetSite: Int32 EndReceive(System.IAsyncResult)
> | Source: System
> | Stack Trace:
> | at System.Net.Sockets.Socket.EndReceive(IAsyncResult asyncResult)
> | at System.Net.Sockets.NetworkStream.EndRead(IAsyncResult asyncResult)
> |
> | ADDITIONAL INFORMATION:
> | Error Rendering Report: GainsChartReport_CM
> |
> | -- Exception Entry End --
> | **
> | **
> | ** StartReport = 6/1/2005 11:16:36 AM StopReport = 6/1/2005 11:32:23 AM
> | ** Report GainsChartReport_CM failed after 15 minutes 47 seconds
> | Program Ended at: 6/1/2005 11:32:25 AM
> |
> *----
> *
> | *******************************************************
> |
> | "Peter Yang [MSFT]" wrote:
> |
> | > Hello,
> | >
> | > I think this issue is caused by IIS timeout settings:
> | >
> | > 1. Click "Start", and then click "Control Panel".
> | >
> | > 2. Double-click "Administrative Tools", and then double-click "Internet
> | > Information Services Manager".
> | >
> | > 3. Expand "Web Sites", right-click "Default Web Site" , and then click
> | > "Properties".
> | >
> | > 4. Click the "Web Site" tab.
> | >
> | > 5. In the "Connections" section, in the "Connection timeout" box,
> increase
> | > the seconds to a value that is sufficient for the length of the import.
> | > Also, please disable Keep-Alives.
> | >
> | > 6. Click "OK".
> | >
> | > Regards,
> | >
> | > Peter Yang
> | > MCSE2000/2003, MCSA, MCDBA
> | > Microsoft Online Partner Support
> | >
> | > When responding to posts, please "Reply to Group" via your newsreader
> so
> | > that others may learn and benefit from your issue.
> | >
> | > =====================================================> | >
> | > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> | >
> | >
> | >
> | >
> | > --
> | > | Thread-Topic: Long running reports
> | > | thread-index: AcVmNDfsZf6nv6RoTXysyOFdimp4OA==> | > | X-WBNR-Posting-Host: 64.73.98.126
> | > | From: "=?Utf-8?B?TWFyaw==?=" <markwh@.newsgroup.nospam>
> | > | Subject: Long running reports
> | > | Date: Tue, 31 May 2005 15:58:11 -0700
> | > | Lines: 18
> | > | Message-ID: <80219D15-436F-4B8E-AD0D-0C8C604C6B9F@.microsoft.com>
> | > | MIME-Version: 1.0
> | > | Content-Type: text/plain;
> | > | charset="Utf-8"
> | > | Content-Transfer-Encoding: 7bit
> | > | X-Newsreader: Microsoft CDO for Windows 2000
> | > | Content-Class: urn:content-classes:message
> | > | Importance: normal
> | > | Priority: normal
> | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | > | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | > | Xref: TK2MSFTNGXA01.phx.gbl
> microsoft.public.sqlserver.reportingsvcs:45040
> | > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> | > |
> | > | We have several business areas that want to run a scheduled batch job
> to
> | > | render reports in a pdf file to them. We seem to be hitting an issue
> with
> | > | reports that take longer than 15 minutes to render.
> | > |
> | > | We get the error message, " The underlying connection was closed:
> Could
> | > not
> | > | establish secure channel for SSL/TLS" and "An existing connection was
> | > | forcibly closed by the remote host"
> | > |
> | > | I have been able to render the reports from the report manager after
> I
> | > set
> | > | the default timeout setting to nothing and let the report churn for
> in
> | > some
> | > | cases 45 minutes. We have larger volumes of financial data.
> | > |
> | > | Corporately we are looking at setting a timeout standard and having
> the
> | > | report builders create temp tables to report from for the longer
> running
> | > | reports, but more specifiically I am wondering what are all the
> possible
> | > | timeout setting that could be forcing the connection closed? Report
> | > | manager? IIS? and has anyone else dealt with this issue and if so
> how
> | > did
> | > | you address it?
> | > |
> | >
> | >
> |
>sql

Logshipping problem with naming transaction log backups

Hey, my logshipping is working just fine but the name of the backup file is of a concern to me. It's using the name in the format of DbName_20061011200002.trn. I know 20061011 is the date. That's fine. 2000 is supposed to be the time but it's giving the wrong time. It's supposed to be 1500 because the time right now is 3pm. And I dont know why the last 2 digits before the .trn are for. Is this something new in SQL 2005 logshipping? SQL 2000 didn't have it. Thank you.

Log shipping uses UTC time when naming the files. This ensures that global deployments of log shipping work consistently.

Regards,

Matt Hollingsworth

Sr. Program Manager

Microsoft SQL Server

|||Is there any possibility for me to change the naming convention. I wanted to go back to SQL 2000 naming convention. Please let me know. Thank you for your help.|||

You can use VBScript to rename your transaction log files to your requirements. Here's a sample script I use:

'============================
'Script to change a filename using timestamps
strMonth = DatePart("m", Now())
strDay = DatePart("d",Now())

if Len(strMonth)=1 then
strMonth = "0" & strMonth
else
strMonth = strMonth
end if

if Len(strDay)=1 then
strDay = "0" & strDay
else
strDay = strDay
end if

strFileName = "LOG_" & DatePart("yyyy",Now()) & strMonth & strDay & FormatDateTime(Now(), vbShortTime) & ".TRN"
strFileName = Replace(strFileName,":","")

Set objFSO = CreateObject("Scripting.FileSystemObject")
'===============================================
'Change the drive letter, folder and filename if necessary
objFSO.MoveFile "D:\SQL_Backups\LOG.TRN" , "D:\SQL_Backups\" & strFileName

You can include this in your Log Shipping Jobs on both the source and the destination servers. One thing to note is that the names should be the same as what you created when the transaction logs are restored on the destination. Your Database maintenance plan won't work on this. Might as well use a customized log shipping plan.

Logshipping error

Hi,
I get the following error after I setup logshipping and
try to restore the copied trn file.
Any ideas'
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4326:
[Microsoft][ODBC SQL Server Driver][SQL Server]The log in
this backup set terminates at LSN 37123000007023700001,
which is too early to apply to the database. A more
recent log backup that includes LS
N 37731000001578500001 can be
restored.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE
LOG is terminating
abnormally.| I get the following error after I setup logshipping and
| try to restore the copied trn file.
| Any ideas'
|
| [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4326:
| [Microsoft][ODBC SQL Server Driver][SQL Server]The log in
| this backup set terminates at LSN 37123000007023700001,
| which is too early to apply to the database. A more
| recent log backup that includes LS
| N 37731000001578500001 can be
| restored.
|
| [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE
| LOG is terminating
| abnormally.
--
It looks like your transaction log backups are out-of-synch. Can you review
your scheduled jobs and make sure that only the log shipping process and no
other independent jobs are creating transaction log backups.
Hope this helps,
--
Eric Cárdenas
SQL Server support

logshipping

Hi,
I'll take FULL and transaction LOG backups via a custom written stored
procedure.
But its working only for single database file. Does not dynamically pick up
database files/filegroups from the backup files themselves.
Is it possible to take backup of database contain files/filegroups?
I used this code in stored procedure:
BACKUP DATABASE @.db_name TO @.vbackupdevice
WITH INIT, NAME=@.db_name, NOSKIP, STATS=10, DESCRIPTION=@.v_filename, NOFORMAT
Any help will be appreciated.
Kind Regards,
Melih.Yea maan, i think its very impressive,
bu i am away this technical issues for 3 months,
sorry maan.
I hope you will find more usefull solution technics..
Cihan.
"Melih" wrote:
> Hi,
> I'll take FULL and transaction LOG backups via a custom written stored
> procedure.
> But its working only for single database file. Does not dynamically pick up
> database files/filegroups from the backup files themselves.
> Is it possible to take backup of database contain files/filegroups?
> I used this code in stored procedure:
> BACKUP DATABASE @.db_name TO @.vbackupdevice
> WITH INIT, NAME=@.db_name, NOSKIP, STATS=10, DESCRIPTION=@.v_filename, NOFORMAT
>
> Any help will be appreciated.
> Kind Regards,
> Melih.
>|||Melih,
Your backup is probably working... Even when the database contains multiple
files, from one or more filegroups the backup file (given your command) will
contain all of this in a single backup file..
Do a small test , backup, then restore a multi-file database as a different
name. This should show you that things are working fine.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Melih" wrote:
> Hi,
> I'll take FULL and transaction LOG backups via a custom written stored
> procedure.
> But its working only for single database file. Does not dynamically pick up
> database files/filegroups from the backup files themselves.
> Is it possible to take backup of database contain files/filegroups?
> I used this code in stored procedure:
> BACKUP DATABASE @.db_name TO @.vbackupdevice
> WITH INIT, NAME=@.db_name, NOSKIP, STATS=10, DESCRIPTION=@.v_filename, NOFORMAT
>
> Any help will be appreciated.
> Kind Regards,
> Melih.
>|||I tested and get result:
Database 'Exchange' does not exist. Check sysdatabases.
ALTER DATABASE statement failed.
Device activation error. The physical file name 'C:\DATABASES
SQL2K\Exchange_Data2_Data.NDF' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 1
File 'Exchange_Data2' cannot be restored to 'C:\DATABASES
SQL2K\Exchange_Data2_Data.NDF'. Use WITH MOVE to identify a valid location
for the file.
Server: Msg 5105, Level 16, State 1, Line 1
Device activation error. The physical file name 'C:\DATABASES
SQL2K\Exchange_Data3_Data.NDF' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 1
File 'Exchange_Data3' cannot be restored to 'C:\DATABASES
SQL2K\Exchange_Data3_Data.NDF'. Use WITH MOVE to identify a valid location
for the file.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I used this code in stored procedure:
set @.found_full = 0
if @.@.fetch_status = 0 and @.found_full = 0
begin
set @.sqlstring = 'alter database ' + @.dbname + ' set RESTRICTED_USER with
rollback immediate'
exec (@.sqlstring)
set @.sqlstring = 'alter database ' + @.dbname + ' set MULTI_USER with
rollback immediate'
exec (@.sqlstring)
-- check file isnt zipped, if so decompress & restore
if charindex('.gz', @.restore_filename) > 0 or charindex('.zip',
@.restore_filename) > 0
begin
SELECT @.sqlstring = @.zippath + '\gzip.exe -d ' + @.backuppath +
@.restore_filename
EXEC @.error = master..xp_cmdshell @.sqlstring, NO_OUTPUT
set @.restore_filename = replace(@.restore_filename, '.gz', '')
set @.restore_filename = replace(@.restore_filename, '.zip', '')
end
--set @.sqlstring = 'alter database ' + @.dbname + ' set RESTRICTED_USER
with rollback immediate'
--exec (@.sqlstring)
set @.sqlstring = 'RESTORE DATABASE ' + @.dbname + ' ' +
'FROM DISK= ''' + @.backuppath + @.restore_filename + ''' ' +
'WITH ' + @.fullbackupmovecommand + ' , STANDBY = ''' + @.standbyfile + ''''
exec (@.sqlstring)
etc......

Logs with timestamp

Hi,

I'm using the SSIS log feature. However, I want a timestamp on the file everytime the IS package runs so as to keep track also that this particular log file is for this specific execution of IS.

How do i do that?

thanks a lot

cherriesh

Are you logging to SQL Server? If so, you can add a column to the dbo.sysdtslog90 table with a datetime datatype and set its default to GetDate().

I recommend that you read Jamie Thomson's blog about custom logging: http://blogs.conchango.com/jamiethomson/archive/2005/06/11/1593.aspx

|||

Here's a post from Jamie that shows how to set the log file name dynamically through an expression:

http://blogs.conchango.com/jamiethomson/archive/2006/10/05/SSIS-Nugget_3A00_-Dynamically-set-a-logfile-name.aspx

Logs not truncating

Hi,
we have a problem on one of our databases.. mainly the log is not truncating
after a backup.. By now it has grown to 3Gb, with the data file at 156Mb.
Any Ideas?
Thanks
NicholasHave a look here :-
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
--
HTH
Ryan Waight, MCDBA, MCSE
"Nicholas Aquilina" <naquilina@.gfi.com> wrote in message
news:e$cXQaKnDHA.372@.TK2MSFTNGP11.phx.gbl...
> Hi,
> we have a problem on one of our databases.. mainly the log is not
truncating
> after a backup.. By now it has grown to 3Gb, with the data file at 156Mb.
> Any Ideas?
> Thanks
> Nicholas
>|||Hi
The article helped..I had to use DBCC SHRINKFILE(Database_log,6). I shrunk
the log to 700Mb first from 3Gb, and then the rest with a backup & log
truncate worked normally. Now could the shrinkfile possibly affect any
replication? We have a snapshot replication from this same database to other
servers
Thanks Aagain
Nicholas
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:eWDTxdKnDHA.3024@.tk2msftngp13.phx.gbl...
> Have a look here :-
> http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "Nicholas Aquilina" <naquilina@.gfi.com> wrote in message
> news:e$cXQaKnDHA.372@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > we have a problem on one of our databases.. mainly the log is not
> truncating
> > after a backup.. By now it has grown to 3Gb, with the data file at
156Mb.
> >
> > Any Ideas?
> >
> > Thanks
> >
> > Nicholas
> >
> >
>

LOGS

hello, i get this error
Microsoft OLE DB Provider for SQL Server error '80040e14'
The log file for database 'autosmotos' is full. Back up the transaction log
for the database to free up some log space.
So i try to delete the LOF file with this command:
ALTER DATABASE [autosmotos]
REMOVE FILE [autosmotos_Log]
GO
But i get
The primary data or log file cannot be removed from a database
Q: how i can i set off this option or how can i empty the log file ?
Gerard.hi Gérard,
I'll do a shrink over that .LDF file or in the case that you would have
enough free space, you could make another LOG file in the same volume.
A question, what recovery model own your database? If your database contains
only 'development data' or 'test data' a good idea is have as simple model.
Current location: Alicante (ES)
"Gérard Leclercq" wrote:

> hello, i get this error
> Microsoft OLE DB Provider for SQL Server error '80040e14'
> The log file for database 'autosmotos' is full. Back up the transaction lo
g
> for the database to free up some log space.
>
> So i try to delete the LOF file with this command:
> ALTER DATABASE [autosmotos]
> REMOVE FILE [autosmotos_Log]
> GO
>
> But i get
> The primary data or log file cannot be removed from a database
>
> Q: how i can i set off this option or how can i empty the log file ?
>
> Gerard.
>
>
>|||underprocessable|||BACKUP LOG autosmotos WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE(autosmotos_LOG,1)
GO
You may then want to issue the following command :-
ALTER DATABASE autosmotos SET RECOVERY SIMPLE
But i would suggest reading up on this 1st
HTH. Ryan
"Grard Leclercq" <gl@.pasdemail.fr> wrote in message
news:MQ8Uf.330505$cT4.10333346@.phobos.telenet-ops.be...
>I work in hard conditions. The db is on a internetserver. I don't have
>access to the Enterprise so i have to ask, and ask, and ask my provider but
>i get no help. I found a trial version of a on-line manager. In attach some
>settings. Maybe this help.
> Is there an instruction to delete or schrink the log file or to turn it
> off ?
> "Enric" <vtam13@.terra.es.(donotspam)> schreef in bericht
> news:9033071E-3A41-42C8-85B2-A35FE6919F70@.microsoft.com...
>
>|||Thank you, the log is empty now(i suppose because the error is not longer
there)
gerard
"Ryan" <Ryan_Waight@.nospam.hotmail.com> schreef in bericht
news:uLJ7qSZTGHA.6084@.TK2MSFTNGP14.phx.gbl...
> BACKUP LOG autosmotos WITH TRUNCATE_ONLY
> GO
> DBCC SHRINKFILE(autosmotos_LOG,1)
> GO
> You may then want to issue the following command :-
> ALTER DATABASE autosmotos SET RECOVERY SIMPLE
> But i would suggest reading up on this 1st
> --
> HTH. Ryan
>
> "Grard Leclercq" <gl@.pasdemail.fr> wrote in message
> news:MQ8Uf.330505$cT4.10333346@.phobos.telenet-ops.be...
>

Logparser issue with SQL Server 2005 Report Pack for IIS

Hi, I don't know if it is the right place to post this question, I am trying
to populate IISLOG DB with IIS log file using the following expression
(provided in the package DOC)
LogParser "SELECT date, time,
strcat(strcat(strcat(TO_STRING(TO_TIMESTAMP(date, time), 'hh'),':00-'),
TO_STRING(TO_TIMESTAMP(date, time), 'hh')),':59') AS Hour,
TO_Int(TO_String(date,'MM')) as Month, TO_Int(TO_String(date,'yyyy')) as
year,c-ip, cs-username, s-sitename, s-computername, s-ip, s-port, cs-method,
cs-uri-stem, cs-uri-query, sc-status, sc-substatus, sc-win32-status,
sc-bytes, cs-bytes, time-taken, cs-version, cs-host, cs(User-Agent),
cs(Cookie), cs(Referer) FROM d:\tracing\Tracingex061025.log TO
GlobalIISLogWF4"
-i:IISW3C -o:SQL -server:(local) -database:IISLog -createtable:ON -e:10
In the command line window, I wait for a while, then I get 3 lines
indicating Statistics without inserting any row in the table that has been
created which is "GlobalIISLogWF4"
I re-run the commande and launched SQL 2005 profiler,
I see the following events
***************************
RPC:Starting
RPC:Completed
in the text I see only
exec sp_cursor 180150003,65536
***************************
but absolutly no inserts
It is a win2k3R2 64bit, sql server 2005 64 bit and logparser the 2 versions
32 bit and 64 bit.
ThanksHello Eliassal,
I would like to know that if you have enable the IIS to log the cs(Cookie),
cs(Referer) event.
Also, what about using the following Command?
LogParser "SELECT date, time,
strcat(strcat(strcat(TO_STRING(TO_TIMESTAMP(date, time), 'hh'),':00-'),
TO_STRING(TO_TIMESTAMP(date, time), 'hh')),':59') AS Hour,
TO_Int(TO_String(date,'MM')) as Month, TO_Int(TO_String(date,'yyyy')) as
year,c-ip, cs-username, s-sitename, s-computername, s-ip, s-port,
cs-method,
cs-uri-stem, cs-uri-query, sc-status, sc-substatus, sc-win32-status,
sc-bytes, cs-bytes, time-taken, cs-version, cs-host, [cs(User-Agent)],
[cs(Cookie)], [cs(Referer)] FROM d:\tracing\Tracingex061025.log TO
GlobalIISLogWF4"
-i:IISW3C -o:SQL -server:(local) -database:IISLog -createtable:ON -e:10
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||HI, yes both are enabled. Only"Host" is not enabled.
I ran the sql you suggested, no inserts as well, the ouput was as follows
Statistics:
--
Elements processed: 422762
Elements output: 422762
Execution time: 22.00 seconds
Any idea? Thanks for your help
"Wei Lu [MSFT]" wrote:
> Hello Eliassal,
> I would like to know that if you have enable the IIS to log the cs(Cookie),
> cs(Referer) event.
> Also, what about using the following Command?
> LogParser "SELECT date, time,
> strcat(strcat(strcat(TO_STRING(TO_TIMESTAMP(date, time), 'hh'),':00-'),
> TO_STRING(TO_TIMESTAMP(date, time), 'hh')),':59') AS Hour,
> TO_Int(TO_String(date,'MM')) as Month, TO_Int(TO_String(date,'yyyy')) as
> year,c-ip, cs-username, s-sitename, s-computername, s-ip, s-port,
> cs-method,
> cs-uri-stem, cs-uri-query, sc-status, sc-substatus, sc-win32-status,
> sc-bytes, cs-bytes, time-taken, cs-version, cs-host, [cs(User-Agent)],
> [cs(Cookie)], [cs(Referer)] FROM d:\tracing\Tracingex061025.log TO
> GlobalIISLogWF4"
> -i:IISW3C -o:SQL -server:(local) -database:IISLog -createtable:ON -e:10
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
>|||Hello Eliassal,
You could reduce the column, and check which column will cause this issue.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi, I launched a full sql 2005 profiler, In the session I HAVE THE FOLLOWING
ERROR
***********************************************************
exec sp_cursor 180150003, 65536
Error: 16902, Severity: 16, State: 33
sp_cursor: The value of the parameter 'optype' is invalid.
***********************************************************
I tried to execute the script by taking off 4 fields each time, no way
always the same error
The problem is we don't have the sql Insert phrase to execute in SSMS to be
able to see the error and don't no the internal of loparser of hiow it
inserts.
I am really going desperate. I need to demo the reporting to my director
next week
"Wei Lu [MSFT]" wrote:
> Hello Eliassal,
> You could reduce the column, and check which column will cause this issue.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Eliassal,
I searched our internal database and found that this error may caused by
the log file format is not correct.
You may try to get the IISLog from other machines and try it again.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||What is strange is that when I run the command against any other machine
(32bit) and not 64bit the upload works fine
The log files come from a win2k 32 bit machines. My sql2005 64bit is on a
win2k3 R2
"Wei Lu [MSFT]" wrote:
> Hello Eliassal,
> I searched our internal database and found that this error may caused by
> the log file format is not correct.
> You may try to get the IISLog from other machines and try it again.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Eliassal,
Please note the managed newsgroups are staffed weekdays by Microsoft
Support professionals. Our goal is to provide a one business day response
to all posts.
Since this issue is critical, I would like to suggest you to use the
following workaround:
1. Use the Logparser to import the IIS log to a SQL Server 2005 32bit
machine.
2. Use the SSIS to import from the 32 bit machine to the 64bit SQL Server.
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi, I setup another machine everything in 64bit and logparser works fine. So
I think it is something wrong with either the setup of sql or the OS.
However, I have a new issue now for displaying the results in Reporting
services. I have loaded 10 days worth of IIS log (5 million rows). Whenever I
try to launch any report, IN IE I have the message "Report is being
generated" and wait for almost 30 to 45 minutes then I get a timeout. I know
the table is provided with no optimization (indexes, non clustered....)
I launched SQKL profiler and found that in different SP there is a lot of
complex sql statements
Any idea or is there sombody who has optimized the table or the whole DB
Thanks in advance
"Wei Lu [MSFT]" wrote:
> Hi ,
> How is everything going? Please feel free to let me know if you need any
> assistance.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi Eliassal,
I would like to suggest you use the Report Cache to improve the report
performence.
Also, I suggest you to reduce the record number in your dataset.
Please note, our service does not provide support on Resource Kit Utilities.
For more detailed information, please refer our team blog:
http://blogs.msdn.com/msdnts/archive/2006/11/08/msdn-service-introduction.as
px
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Logon Error: 18456, Severity: 14, State: 8 - NEED HELP!!!!

Hi All,

I am getting the following error in the ERRORLOG File when I am
trying to connect to SQL Server 2005 Enterprise Edition 64bit with SP2 from
Reporting Services Configuration tool - Database Setup. The SQL Server
database and reporting services are on 2 different servers. Both are running
2003 SP2 64 bit.

2007-04-12 07:30:05.54 Logon Error: 18456, Severity: 14, State:
8.
2007-04-12 07:30:05.54 Logon Login failed for user 'sa'.
[CLIENT: xx.xx.xx.xx]

I know my password is valid as I can logged into SSMS using SA. I can create
the 2 reportserver databases using the Reporting Services Configuration tool
on the database server. The db server is in MIXED mode.

What am I missing?

Related articles I have followed are:

Login failed for user 'x'
http://msdn2.microsoft.com/en-us/library/ms366351.aspx

Change Server Authentication Mode
http://msdn2.microsoft.com/en-us/library/ms188670.aspx

Any help to get passed this problem would greatly be appreciated.
Thanks in advance for your time!

Hello,

Can you verify that you have "SQL Server Credentials" selected as the "Credentials Type" in the Database Setup section?

Hope this helps.

Jarret

|||Yes I have "SQL Server Credentials" selected as the "Credentials Type" in the Database Setup section.|||

Can you try to use a Windows account (with administrator privileges) instead of the SQL account?

Jarret

|||I un-install and re-installed Reporting Services on the web server. This corrected the problem.

Thanks to everyone for their replies.

Logon Error: 18456, Severity: 14, State: 8 - NEED HELP!!!!

Hi All,

I am getting the following error in the ERRORLOG File when I am
trying to connect to SQL Server 2005 Enterprise Edition 64bit with SP2 from
Reporting Services Configuration tool - Database Setup. The SQL Server
database and reporting services are on 2 different servers. Both are running
2003 SP2 64 bit.

2007-04-12 07:30:05.54 Logon Error: 18456, Severity: 14, State:
8.
2007-04-12 07:30:05.54 Logon Login failed for user 'sa'.
[CLIENT: xx.xx.xx.xx]

I know my password is valid as I can logged into SSMS using SA. I can create
the 2 reportserver databases using the Reporting Services Configuration tool
on the database server. The db server is in MIXED mode.

What am I missing?

Related articles I have followed are:

Login failed for user 'x'
http://msdn2.microsoft.com/en-us/library/ms366351.aspx

Change Server Authentication Mode
http://msdn2.microsoft.com/en-us/library/ms188670.aspx

Any help to get passed this problem would greatly be appreciated.
Thanks in advance for your time!

Are you using Vista?

http://www.microsoft.com/sql/howtobuy/windowsvistasupport.mspx

If not, try here:

http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx

|||On both servers we have Windows 2003 Server 64bit with SP2 applied. I previously visited the second link listed above. All its explains that a State: 8 is a password mismatch. I know I'm entering the password correctly in the Reporting Services Configuration tool. However, I believe when it gets sent to the db server it is missing or encrypted someway that the db server can't decrypt.

Thanks again.
|||

I wasn't sure where you were connecting from, hence the Vista question. In Vista, your admin group token is stripped, and SQL Server might be depending on that membership to get you in.

So where are you connecting from and to? In other words, you say you have two servers, but I'm not sure which one you're connecting from. I want to be sure I have the whole picture.

In the meantime, you can fire up Profiler and see what you're sending across the net to connect. you're correct, in SQL 2K5, the connection info is encrypted, but both sides should know how to deal with that.

Also, I'm sure you've tried this already, but make sure you try a couple of accounts, just in case. Try the sa and a Windows account.

Between all these, you should be able to figure out where the problem is, if not its resolution. Then reply back and we'll see if we can get you closer to the right answer.

|||

State 8 does indeed indicate a password mismatch, so you should check that the password provided to Reporting Services is indeed the correct one. If you are certain that the password is correct, then Reporting Services may not be passing it correctly to SQL Server - please check on the SQL Server Reporting Services forum, to see if this is a known issue.

Thanks

Laurentiu

|||I un-install and re-installed Reporting Services on the web server. This corrected the problem.

Thanks to everyone for their replies.

Login scripting to a file

I am new to DMO and I am trying to find a way where I can create a script file containing all server logins. THis needs to run on a daily basis.

Anyone have any examples to share?

Use the following procs, you will generate the login scripts.

Cut and paste the following proc and then execute

exec sp_script_generator 'c:\scripts\', <sa pwd>.

if sql server authentication, provide the password.

Please note that, I wrote the scripts long back.

If any issues in the procs, please post the same.

if exists (select * from sysobjects where id = object_id('dbo.sp_hexadecimal') and sysstat & 0xf = 4)
drop procedure dbo.sp_hexadecimal
GO

CREATE PROCEDURE sp_hexadecimal
@.binvalue varbinary(255),
@.hexvalue varchar(255) OUTPUT
AS
DECLARE @.charvalue varchar(255)
DECLARE @.i int
DECLARE @.length int
DECLARE @.hexstring char(16)
SELECT @.charvalue = '0x'
SELECT @.i = 1
SELECT @.length = DATALENGTH(@.binvalue)
SELECT @.hexstring = '0123456789abcdef'
WHILE (@.i <= @.length)
BEGIN
DECLARE @.tempint int
DECLARE @.firstint int
DECLARE @.secondint int
SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
SELECT @.firstint = FLOOR(@.tempint/16)
SELECT @.secondint = @.tempint - (@.firstint*16)
SELECT @.charvalue = @.charvalue +
SUBSTRING(@.hexstring, @.firstint+1, 1) +
SUBSTRING(@.hexstring, @.secondint+1, 1)
SELECT @.i = @.i + 1
END
SELECT @.hexvalue = @.charvalue

GO

if exists (select * from sysobjects where id = object_id('dbo.sp_displayoaerrorinfo') and sysstat & 0xf = 4)
drop procedure dbo.sp_displayoaerrorinfo
GO

CREATE PROCEDURE sp_displayoaerrorinfo
@.object int,
@.hresult int
AS
DECLARE @.output varchar(255)
DECLARE @.hrhex char(10)
DECLARE @.hr int
DECLARE @.source varchar(255)
DECLARE @.description varchar(255)
PRINT 'OLE Automation Error Information'
EXEC sp_hexadecimal @.hresult, @.hrhex OUT
SELECT @.output = ' HRESULT: ' + @.hrhex
PRINT @.output
EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
IF @.hr = 0
BEGIN
SELECT @.output = ' Source: ' + @.source
PRINT @.output
SELECT @.output = ' Description: ' + @.description
PRINT @.output
END
ELSE
BEGIN
PRINT " sp_OAGetErrorInfo failed."
RETURN
END

GO

set quoted_identifier off
go
if exists (select * from sysobjects where id = object_id('dbo.sp_srv_obj_scr_gen') and sysstat & 0xf = 4)
drop procedure dbo.sp_srv_obj_scr_gen
GO

/****************************************************************************************************************/
/* Procedure : sp_srv_obj_scr_gen */
/* Purpose : To generate server objects namely logins, devices and databases in a SQL Server */
/* Called From : sp_Script_Generator [Main] */
/* Author : Narasimhan Jayachandran */
/* Date : 09/28/98 */
/* Date : 03/15/00 */
/****************************************************************************************************************/

create proc sp_srv_obj_scr_gen
@.object int,
@.srvObjColl varchar(30),
@.strObjQry varchar(255),
@.strFile varchar(6000),
@.strFilePath varchar(6000)=null
as
begin

declare @.srvObjname varchar(128), @.strMethod varchar(6000),@.hr int,@.return varchar(255), @.strCmd varchar(6000)
declare @.dbCtr int, @.FileCtr int, @.DestFile varchar(6000)

select @.dbctr=0

select @.strCmd = "echo use master >> " + @.strFile
exec master..xp_cmdshell @.strCmd, no_output
select @.strCmd = "echo go >> " + @.strFile
exec master..xp_cmdshell @.strCmd, no_output

exec("declare server_obj_cursor cursor for "+ @.strObjQry)

open server_obj_cursor

fetch next from server_obj_cursor into @.srvObjname

while @.@.fetch_status = 0
begin
-- select @.srvobjname

select @.strMethod =' '+@.srvObjColl+'("'+@.srvObjname+'").Script(324,"'+@.strFile+'")'

exec @.hr = sp_OAMethod @.object, @.strMethod, @.return out

if @.hr <> 0
begin
close server_obj_cursor
deallocate server_obj_cursor
return 1
end

if @.srvObjColl="Databases" -- database files --
begin
select @.dbCtr = @.dbCtr + 1
select @.strCmd = "copy "+ @.strFile+" "+@.strFilePath+convert(varchar(30),@.dbCtr)+".sql"
end

exec master..xp_cmdshell @.strCmd, no_output

fetch next from server_obj_cursor into @.srvObjname

end

close server_obj_cursor
deallocate server_obj_cursor

if @.srvObjColl="Databases" -- database files --
begin
select @.DestFile=@.strFile
select @.strCmd = "copy ",@.FileCtr=1,@.strFile=" "
while @.FileCtr <= @.dbCtr
begin
select @.strFile = @.strFile + @.strFilePath+convert(varchar(30),@.FileCtr)+".sql + "
select @.FileCtr = @.FileCtr + 1
end
select @.strFile = left(@.strFile,len(@.strFile)-2)

select @.strCmd = @.strCmd+@.strFile+" "+@.destFile
exec master..xp_cmdshell @.strCmd, no_output

select @.strCmd = "del ",@.FileCtr=1
while @.FileCtr <= @.dbCtr
begin
select @.strCmd = "Del "+@.strFilePath+convert(varchar(30),@.FileCtr)+".sql"
exec master..xp_cmdshell @.strCmd, no_output
select @.FileCtr = @.FileCtr + 1
end
end

return 0
end

GO

GRANT EXECUTE ON dbo.sp_srv_obj_scr_gen TO public
GO

--exec sp_script_generator "D:\backup\sqlserver\Script\"

if exists (select * from sysobjects where id = object_id('dbo.sp_script_generator') and sysstat & 0xf = 4)
drop procedure dbo.sp_script_generator
GO
/* ************************************************************************************************************** */
/* Procedure : sp_script_generator */
/* Purpose : To generate scripts for all objects of all databases in a SQL Server 6.5/7.0 */
/* Sub Procedures : sp_srv_obj_scr_gen,sp_db_obj_scr_gen,sp_tb_obj_scr_gen,sp_con_obj_scr_gen */
/* Author : Narasimhan Jayachandran */
/* Version : 1.0 */
/* Date : 09/28/98 */
/* Version : 1.1 */
/* Date : 03/25/99 */
/* Version : 2.0 */
/* Date : 02/15/00 */
/* Usage Hints : sp_Script_Generator <Output File Path>,[SA Password - optional ] */
/* Example : sp_script_generator "c:\scripts\", [SA Password] */
/* Outputs : c:\scripts\SQLSERVER_logins.sql */
/* c:\scripts\SQLSERVER_devices.sql */
/* c:\scripts\SQLSERVER_databases.sql */
/* c:\scripts\SQLSERVER_<databaseName1>.sql */
/* ... */
/* ... */
/* ... */
/* c:\scripts\<SQLSERVER>_<databaseNameN>.sql */
/* Modified Date Reason */
/* 06/25/2001 Jobs Scripts added */
/* 07/03/2001 syslogins used to pick the loginnames */
/* Windows Authentication Mode added */
/* Use the System function ServerName instead of as a parameter */
/****************************************************************************************************************/
create proc sp_script_generator
@.strFilePath varchar(6000) = null,
@.pwd varchar(128) = null

as
begin

declare @.object int, @.hr int,@.return varchar(255)
declare @.dbname varchar(128),@.ObjColl varchar(30)
declare @.strFile varchar(6000), @.strCmd varchar(6000)
declare @.strObjQry varchar(6000), @.strObjQry1 varchar(255), @.strMethod varchar(255)
declare @.dbObjColl varchar(30), @.tbObjColl varchar(30),@.srvObjColl varchar(30)
declare @.dbobjname varchar(30), @.tbobjname varchar(30),@.ObjcollName varchar(60)
declare @.srvObjFlag tinyint,@.sts tinyint,@.TbCtr int,@.ScriptType int
declare @.Ver6570 char(4),@.ProgID varchar(16), @.Property varchar(6000)
declare @.src varchar(255), @.desc varchar(255)

declare @.objJobserver int

set nocount on

if @.strFilePath is null
begin
print "Usage : sp_Script_Generator <Output File Path>, [SA Password]"
print "-"
print "Example : sp_script_generator 'c:\scripts\',[SA Password]"
print "Outputs : c:\scripts\SQLSERVER_logins.sql"
print " c:\scripts\SQLSERVER_devices.sql"
print " c:\scripts\SQLSERVER_databases.sql"
print " c:\scripts\SQLSERVER_<databaseName1>.sql"
print " .."
print " .."
print " .."
print " c:\scripts\SQLSERVER_<databaseNameN>.sql"
print "-"
return
end

-- version check
select @.Ver6570=substring(@.@.version,23,4)

select @.ProgID =
case
when @.Ver6570 = '6.50' then 'SQLOLE.SQLServer'
else 'SQLDMO.SQLServer'
-- when @.Ver6570 = '7.00' then 'SQLDMO.SQLServer'
end

-- Create an object
print "Creating SQL SERVER Object..."
exec @.hr = sp_OACreate @.ProgID, @.object out
if @.hr <> 0
begin
exec sp_displayoaerrorinfo @.object, @.hr
return
end

print "Connecting to the SQL SERVER "+@.Ver6570+"..."
-- Set the property value to true if it is Windows Authentication
if @.pwd is null
begin
-- Set a property
Print "Connecting to SQLServer using Windows Authentication Mode...!"
exec @.hr = sp_OASetProperty @.object, 'LoginSecure', TRUE
if @.hr <> 0
begin
exec sp_OAGetErrorInfo @.object, @.src out, @.desc out
select hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
return
end

-- Connect to the sql server -- Windows Authentication Mode
select @.strMethod = 'Connect("'+@.@.ServerName+'")'
exec @.hr = sp_OAMethod @.object,@.strMethod
if @.hr <> 0
begin
exec sp_displayoaerrorinfo @.object, @.hr
exec @.hr = sp_OADestroy @.object
return
end
end
else
begin
Print "Connecting to SQLServer using SQLServer Authentication Mode...!"
-- Connect to the sql server
select @.strMethod = 'Connect("'+@.@.ServerName+'" , "sa", "'+@.pwd+'")'
exec @.hr = sp_OAMethod @.object,@.strMethod
if @.hr <> 0
begin
exec sp_displayoaerrorinfo @.object, @.hr
exec @.hr = sp_OADestroy @.object
return
end
end

-- Verify that the connection
exec @.hr = sp_OAMethod @.object, 'VerifyConnection' ,@.return out
if @.hr <> 0
begin
exec sp_displayoaerrorinfo @.object, @.hr
exec @.hr = sp_OADestroy @.object
return end

select @.srvObjFlag = 0

-- Server Objects

-- Logins
print "Creating Scripts for "+@.@.servername+" Server Logins..."
select @.strFile=@.strFilePath+@.@.servername+"_logins.sql"
select @.strCmd = "del "+@.strFile
exec master..xp_cmdshell @.strCmd, no_output

select @.srvObjColl = "Logins", @.sts = 0
select @.strObjQry =
case
when @.Ver6570 = "6.50" then
" select name from master..syslogins where status = 8 and name != 'probe' "
else
" select loginname from master..syslogins where loginname not in ('sa') "
end

exec @.sts = sp_srv_obj_scr_gen @.object,@.srvObjColl,@.strObjQry,@.strFile
if @.sts = 1 goto ProcErr

goto CloseDbCur

ProcErr: exec sp_displayoaerrorinfo @.object,@.hr
if @.srvObjFlag = 0 goto ProcFine

CloseDbCur:
close database_cursor
deallocate database_cursor
ProcFine:
exec @.hr = sp_OADestroy @.object
if @.hr <> 0
exec sp_displayoaerrorinfo @.object, @.hr
exec @.hr = sp_OADestroy @.ObjJobServer
if @.hr <> 0
exec sp_displayoaerrorinfo @.ObjJobServer, @.hr

set nocount off
return
end

GO

--

Thanks.

Naras.

|||Actually, you don't need DMO to do this - just search for sp_help_revlogin and install the procedure in your master database. It generates a script which creates all the logins on your server with the correct hash for the password in place for each login.|||

Cool.

Thanks Allen White.

But let him learn DMO...too

Thanks

Naras.

Login Question

Hi
Current configuration: SQL Server with SQL Security.
A disc where the backup file is written is protected and ony the members of
the domain are allowed to write to it.
Problem when a user sends a request to the SQL Server for a backup some sort
of errors occurs because the database doesn't have the permission to write
to the disc
I would expect that although the database does the backup the permission to
write to the disc will be based on the user that sent the request.
Can anyone please clarify the situation
Thank you in advance,
Shmuel Shulman
SBS Technologies LTDHi,
No, that depends up the SQL Server service startup account.
Thanks
Hari
SQL Server MVP
"S Shulman" <smshulman@.hotmail.com> wrote in message
news:ugJEuqtlFHA.3256@.TK2MSFTNGP12.phx.gbl...
> Hi
> Current configuration: SQL Server with SQL Security.
> A disc where the backup file is written is protected and ony the members
> of the domain are allowed to write to it.
> Problem when a user sends a request to the SQL Server for a backup some
> sort of errors occurs because the database doesn't have the permission to
> write to the disc
> I would expect that although the database does the backup the permission
> to write to the disc will be based on the user that sent the request.
> Can anyone please clarify the situation
> Thank you in advance,
> Shmuel Shulman
> SBS Technologies LTD
>|||Thnks,
Shmuel
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eoioCEulFHA.3256@.TK2MSFTNGP12.phx.gbl...
> Hi,
> No, that depends up the SQL Server service startup account.
> Thanks
> Hari
> SQL Server MVP
> "S Shulman" <smshulman@.hotmail.com> wrote in message
> news:ugJEuqtlFHA.3256@.TK2MSFTNGP12.phx.gbl...
>

login problem after restoring a database in SQL7

Since the database server was removed to a new Windows Advanced Server 2000, I restored a database from the backup file. When I tried to create a new Login under Security, I got the following error message:

Error 15023: User or role 'MyDatabaseLoginName' already exists in current database.

But I checked it and it doesn't exist. I even could not create a system DSN without the Login. Someone told me to create a new one with another name, but I do not want to change the connection string in my ASP files. Does anyone has any idea about it? Thanks in advance.Did you try to fix it with this system stored proc, can be used in sql server login, should fit ur situation.

sp_change_users_login