Long Running Re-Index Task

Hi All
I have a SQL Server 2005 Standard Edition on SP1 where the Re-Index
Maintenance task normally takes 5 minutes however every now and then the job
will take over 8 hours to complete.
Can anyone point me in the right direction to identify how to identify why
the re-index takes so long?
I have scripted the maintenance plan and listed below is a sample of the
re-index
ALTER INDEX [pk_Account] ON [dbo].[Account] REBUILD WITH ( PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,
SORT_IN_TEMPDB = OFF, ONLINE = OFF )
Thanks
David
How big is database? Any activities on the server while you are running MP?
"David" <David@.discussions.microsoft.com> wrote in message
news:44134DDF-BCC0-4130-BE08-B053231876BA@.microsoft.com...
> Hi All
> I have a SQL Server 2005 Standard Edition on SP1 where the Re-Index
> Maintenance task normally takes 5 minutes however every now and then the
> job
> will take over 8 hours to complete.
> Can anyone point me in the right direction to identify how to identify why
> the re-index takes so long?
> I have scripted the maintenance plan and listed below is a sample of the
> re-index
> ALTER INDEX [pk_Account] ON [dbo].[Account] REBUILD WITH ( PAD_INDEX =
> OFF,
> STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS =
> ON,
> SORT_IN_TEMPDB = OFF, ONLINE = OFF )
> Thanks
|||Is there a long-running transaction that's holding a lock on the table? That
may prevent the index rebuild acquiring the locks it requires.
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23ycFxx7tHHA.4800@.TK2MSFTNGP05.phx.gbl...
> David
> How big is database? Any activities on the server while you are running
> MP?
>
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:44134DDF-BCC0-4130-BE08-B053231876BA@.microsoft.com...
>

Long Running Re-Index Task

Hi All
I have a SQL Server 2005 Standard Edition on SP1 where the Re-Index
Maintenance task normally takes 5 minutes however every now and then the job
will take over 8 hours to complete.
Can anyone point me in the right direction to identify how to identify why
the re-index takes so long?
I have scripted the maintenance plan and listed below is a sample of the
re-index
ALTER INDEX [pk_Account] ON [dbo].[Account] REBUILD WITH ( PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,
SORT_IN_TEMPDB = OFF, ONLINE = OFF )
ThanksDavid
How big is database? Any activities on the server while you are running MP?
"David" <David@.discussions.microsoft.com> wrote in message
news:44134DDF-BCC0-4130-BE08-B053231876BA@.microsoft.com...
> Hi All
> I have a SQL Server 2005 Standard Edition on SP1 where the Re-Index
> Maintenance task normally takes 5 minutes however every now and then the
> job
> will take over 8 hours to complete.
> Can anyone point me in the right direction to identify how to identify why
> the re-index takes so long?
> I have scripted the maintenance plan and listed below is a sample of the
> re-index
> ALTER INDEX [pk_Account] ON [dbo].[Account] REBUILD WITH ( PAD_INDEX => OFF,
> STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS => ON,
> SORT_IN_TEMPDB = OFF, ONLINE = OFF )
> Thanks|||Is there a long-running transaction that's holding a lock on the table? That
may prevent the index rebuild acquiring the locks it requires.
--
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23ycFxx7tHHA.4800@.TK2MSFTNGP05.phx.gbl...
> David
> How big is database? Any activities on the server while you are running
> MP?
>
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:44134DDF-BCC0-4130-BE08-B053231876BA@.microsoft.com...
>> Hi All
>> I have a SQL Server 2005 Standard Edition on SP1 where the Re-Index
>> Maintenance task normally takes 5 minutes however every now and then the
>> job
>> will take over 8 hours to complete.
>> Can anyone point me in the right direction to identify how to identify
>> why
>> the re-index takes so long?
>> I have scripted the maintenance plan and listed below is a sample of the
>> re-index
>> ALTER INDEX [pk_Account] ON [dbo].[Account] REBUILD WITH ( PAD_INDEX =>> OFF,
>> STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS =>> ON,
>> SORT_IN_TEMPDB = OFF, ONLINE = OFF )
>> Thanks
>

Long Running Re-Index Task

Hi All
I have a SQL Server 2005 Standard Edition on SP1 where the Re-Index
Maintenance task normally takes 5 minutes however every now and then the job
will take over 8 hours to complete.
Can anyone point me in the right direction to identify how to identify why
the re-index takes so long?
I have scripted the maintenance plan and listed below is a sample of the
re-index
ALTER INDEX [pk_Account] ON [dbo].[Account] REBUILD WITH ( PAD_I
NDEX = OFF,
STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
,
SORT_IN_TEMPDB = OFF, ONLINE = OFF )
ThanksDavid
How big is database? Any activities on the server while you are running MP?
"David" <David@.discussions.microsoft.com> wrote in message
news:44134DDF-BCC0-4130-BE08-B053231876BA@.microsoft.com...
> Hi All
> I have a SQL Server 2005 Standard Edition on SP1 where the Re-Index
> Maintenance task normally takes 5 minutes however every now and then the
> job
> will take over 8 hours to complete.
> Can anyone point me in the right direction to identify how to identify why
> the re-index takes so long?
> I have scripted the maintenance plan and listed below is a sample of the
> re-index
> ALTER INDEX [pk_Account] ON [dbo].[Account] REBUILD WITH ( PAD
_INDEX =
> OFF,
> STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS =
> ON,
> SORT_IN_TEMPDB = OFF, ONLINE = OFF )
> Thanks|||Is there a long-running transaction that's holding a lock on the table? That
may prevent the index rebuild acquiring the locks it requires.
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23ycFxx7tHHA.4800@.TK2MSFTNGP05.phx.gbl...
> David
> How big is database? Any activities on the server while you are running
> MP?
>
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:44134DDF-BCC0-4130-BE08-B053231876BA@.microsoft.com...
>

LogShipping question

Hi guys
I am about to create a logshipping plan to keep a db updated in Server2 from
Server1.
I know that Server1 must run the Enerprise edition in order to enable the
logshipping. Does server2 need to be Enterprise edition too or standard
edition is sufficient.
thanks
Standard is sufficient.
"aolxp" <sa@.anonymous.com> wrote in message
news:ugSubz0rFHA.2212@.TK2MSFTNGP15.phx.gbl...
> Hi guys
> I am about to create a logshipping plan to keep a db updated in Server2
> from Server1.
> I know that Server1 must run the Enerprise edition in order to enable the
> logshipping. Does server2 need to be Enterprise edition too or standard
> edition is sufficient.
> thanks
>
|||Both need to be EE. If you want to involve SE in log shipping, use your own scripts. You can Google
and you will probably find examples of home-grown log shipping.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"aolxp" <sa@.anonymous.com> wrote in message news:ugSubz0rFHA.2212@.TK2MSFTNGP15.phx.gbl...
> Hi guys
> I am about to create a logshipping plan to keep a db updated in Server2 from Server1.
> I know that Server1 must run the Enerprise edition in order to enable the logshipping. Does
> server2 need to be Enterprise edition too or standard edition is sufficient.
> thanks
>

LogShipping question

Hi guys
I am about to create a logshipping plan to keep a db updated in Server2 from
Server1.
I know that Server1 must run the Enerprise edition in order to enable the
logshipping. Does server2 need to be Enterprise edition too or standard
edition is sufficient.
thanksStandard is sufficient.
"aolxp" <sa@.anonymous.com> wrote in message
news:ugSubz0rFHA.2212@.TK2MSFTNGP15.phx.gbl...
> Hi guys
> I am about to create a logshipping plan to keep a db updated in Server2
> from Server1.
> I know that Server1 must run the Enerprise edition in order to enable the
> logshipping. Does server2 need to be Enterprise edition too or standard
> edition is sufficient.
> thanks
>|||Both need to be EE. If you want to involve SE in log shipping, use your own scripts. You can Google
and you will probably find examples of home-grown log shipping.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"aolxp" <sa@.anonymous.com> wrote in message news:ugSubz0rFHA.2212@.TK2MSFTNGP15.phx.gbl...
> Hi guys
> I am about to create a logshipping plan to keep a db updated in Server2 from Server1.
> I know that Server1 must run the Enerprise edition in order to enable the logshipping. Does
> server2 need to be Enterprise edition too or standard edition is sufficient.
> thanks
>sql

LogShipping question

Hi guys
I am about to create a logshipping plan to keep a db updated in Server2 from
Server1.
I know that Server1 must run the Enerprise edition in order to enable the
logshipping. Does server2 need to be Enterprise edition too or standard
edition is sufficient.
thanksStandard is sufficient.
"aolxp" <sa@.anonymous.com> wrote in message
news:ugSubz0rFHA.2212@.TK2MSFTNGP15.phx.gbl...
> Hi guys
> I am about to create a logshipping plan to keep a db updated in Server2
> from Server1.
> I know that Server1 must run the Enerprise edition in order to enable the
> logshipping. Does server2 need to be Enterprise edition too or standard
> edition is sufficient.
> thanks
>|||Both need to be EE. If you want to involve SE in log shipping, use your own
scripts. You can Google
and you will probably find examples of home-grown log shipping.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"aolxp" <sa@.anonymous.com> wrote in message news:ugSubz0rFHA.2212@.TK2MSFTNGP15.phx.gbl...[vb
col=seagreen]
> Hi guys
> I am about to create a logshipping plan to keep a db updated in Server2 fr
om Server1.
> I know that Server1 must run the Enerprise edition in order to enable the
logshipping. Does
> server2 need to be Enterprise edition too or standard edition is sufficien
t.
> thanks
>[/vbcol]

Logshipping From a Cluster to a Cluster

Currently have 2 clusters for sql server 2000 enterprise
edition with one being my primary database server and
another at a DR site.
Is there any limitation which would prevent me from
logshipping my primary cluster to the secondary cluster?
Any violation of a best practice that you might see.
The clusters are configured as an active/passive cluster.
Any special tips or concerns/recommendations?I have never done that , but I think you should probably make sure that the
network share for the backups is a clustered network share, so it will be
available during failover...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jack Snow" <mrbrew5510@.hotmail.com> wrote in message
news:OABNb.57764$G04.12571479@.news4.srv.hcvlny.cv.net...
> Currently have 2 clusters for sql server 2000 enterprise
> edition with one being my primary database server and
> another at a DR site.
> Is there any limitation which would prevent me from
> logshipping my primary cluster to the secondary cluster?
> Any violation of a best practice that you might see.
> The clusters are configured as an active/passive cluster.
> Any special tips or concerns/recommendations?
>|||jack,
a couple of comments :-
if you are running statements like SELECT INTO or BCP into
on your primary server - these will not be log shipped as
they are 'non-logged' or 'minimally logged' operations.
Also, think how you would change your application to point
at the standby server if you did ever need to fail over ?
Normal log-shipping practice would be to rename your SQL
Server on the standby to be the same as the offline
primary server - this would be more complicated on a
cluster - you would probably have to repoint all your
application connections to the existing name of the
standby SQL Server. Would this mean changes on your
middle-tier, or changes on each SQL user's client network
utility ?
Hope this helps.
>--Original Message--
>Currently have 2 clusters for sql server 2000 enterprise
>edition with one being my primary database server and
>another at a DR site.
>Is there any limitation which would prevent me from
>logshipping my primary cluster to the secondary cluster?
>Any violation of a best practice that you might see.
> The clusters are configured as an active/passive cluster.
>Any special tips or concerns/recommendations?
>
>.
>

Log-shipping failure

Hi All,
sql server 2000 enterprise edition, 8.00.760
Am trying to set logs-shipping between two databases on two different servers on the same domain. Am using same sql server agent account for both the servers.
After log-shipping is configured, am able to see the transaction logs being backed up in the primary server, then copied on to the secondary server folder. But when it is trying to restore the transaction logs on the secondary server, I get the following
error:
sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029).
FYI, We are using compatibility level of 65 on the primary server, as this is how we want it. But I can change the compatibility level on secondary server to 80 (if its a must). Am wondering, if this is one of the reason for failure.
More information:
I have confugured it to restore in STANDBY mode and terminate any user connections. And the secondary server is used for monitoring log shipping activities.
Has anybody faced similar problem? Please advise
Thanks much
GYK
> Hi All,
> sql server 2000 enterprise edition, 8.00.760
> Am trying to set logs-shipping between two databases on two
> different servers on the same domain. Am using same sql server
> agent account for both the servers.
> After log-shipping is configured, am able to see the transaction
> logs being backed up in the primary server, then copied on to
> the secondary server folder. But when it is trying to restore the
> transaction logs on the secondary server, I get the following error:
> sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029).
> FYI, We are using compatibility level of 65 on the primary server,
> as this is how we want it. But I can change the compatibility
> level on secondary server to 80 (if its a must). Am wondering,
> if this is one of the reason for failure.
> More information:
> I have confugured it to restore in STANDBY mode and terminate
> any user connections. And the secondary server is used for
> monitoring log shipping activities.
> Has anybody faced similar problem? Please advise
> Thanks much
> GYK
Check that the sql server agent account has enough permissions on the
secondary server.
Run the sqlmaint with the parameters from the command-line and see what
errors are returned.
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

Log-shipping failure

Hi All,
sql server 2000 enterprise edition, 8.00.760
Am trying to set logs-shipping between two databases on two different server
s on the same domain. Am using same sql server agent account for both the se
rvers.
After log-shipping is configured, am able to see the transaction logs being
backed up in the primary server, then copied on to the secondary server fold
er. But when it is trying to restore the transaction logs on the secondary s
erver, I get the following
error:
sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029).
FYI, We are using compatibility level of 65 on the primary server, as this i
s how we want it. But I can change the compatibility level on secondary serv
er to 80 (if its a must). Am wondering, if this is one of the reason for fai
lure.
More information:
I have confugured it to restore in STANDBY mode and terminate any user conne
ctions. And the secondary server is used for monitoring log shipping activit
ies.
Has anybody faced similar problem? Please advise
Thanks much
GYK> Hi All,
> sql server 2000 enterprise edition, 8.00.760
> Am trying to set logs-shipping between two databases on two
> different servers on the same domain. Am using same sql server
> agent account for both the servers.
> After log-shipping is configured, am able to see the transaction
> logs being backed up in the primary server, then copied on to
> the secondary server folder. But when it is trying to restore the
> transaction logs on the secondary server, I get the following error:
> sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029).
> FYI, We are using compatibility level of 65 on the primary server,
> as this is how we want it. But I can change the compatibility
> level on secondary server to 80 (if its a must). Am wondering,
> if this is one of the reason for failure.
> More information:
> I have confugured it to restore in STANDBY mode and terminate
> any user connections. And the secondary server is used for
> monitoring log shipping activities.
> Has anybody faced similar problem? Please advise
> Thanks much
> GYK
--
Check that the sql server agent account has enough permissions on the
secondary server.
Run the sqlmaint with the parameters from the command-line and see what
errors are returned.
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

Logon failure?

Hello,
We just installed SQL Server developer's edition on a new Windows XP Pro
workstation. But whenever we bring up the SQL Server service manager, and
click Start, we get the error:
"The service did not start due to logon failure"
When we did the install, we left the "sa" password blank, and we're using
SQL Server authentication. Can anyone say what might be wrong?
Thanks!!
It's most likely the Windows account that starts up the SQL Server
service. The password is probably expired. Check your SQL
Server Service log on credentials
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:OfpH2J1dFHA.1136@.TK2MSFTNGP12.phx.gbl...
> Hello,
> We just installed SQL Server developer's edition on a new Windows XP Pro
> workstation. But whenever we bring up the SQL Server service manager, and
> click Start, we get the error:
> "The service did not start due to logon failure"
> When we did the install, we left the "sa" password blank, and we're using
> SQL Server authentication. Can anyone say what might be wrong?
> Thanks!!
>
|||Oh, by the way... USE A STRONG PASSWORD FOR 'sa'!!!!
Don't use blanks; a lot of SQL Server exploits use a brute force method
to compromise a system. They'll check for passwords such 'sa' or blank.
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:OfpH2J1dFHA.1136@.TK2MSFTNGP12.phx.gbl...
> Hello,
> We just installed SQL Server developer's edition on a new Windows XP Pro
> workstation. But whenever we bring up the SQL Server service manager, and
> click Start, we get the error:
> "The service did not start due to logon failure"
> When we did the install, we left the "sa" password blank, and we're using
> SQL Server authentication. Can anyone say what might be wrong?
> Thanks!!
>
|||Oh yes, for server deployments of SQL Server, we always use strong
passwords, but the workstation in question is at a home office, and not
connected to the Internet. Thanks!
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:O#ZOgN1dFHA.3836@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Oh, by the way... USE A STRONG PASSWORD FOR 'sa'!!!!
> Don't use blanks; a lot of SQL Server exploits use a brute force method
> to compromise a system. They'll check for passwords such 'sa' or blank.
> "Dean J Garrett" <info@.amuletc.com> wrote in message
> news:OfpH2J1dFHA.1136@.TK2MSFTNGP12.phx.gbl...
and[vbcol=seagreen]
using
>

Logon failure?

Hello,
We just installed SQL Server developer's edition on a new Windows XP Pro
workstation. But whenever we bring up the SQL Server service manager, and
click Start, we get the error:
"The service did not start due to logon failure"
When we did the install, we left the "sa" password blank, and we're using
SQL Server authentication. Can anyone say what might be wrong?
Thanks!!It's most likely the Windows account that starts up the SQL Server
service. The password is probably expired. Check your SQL
Server Service log on credentials
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:OfpH2J1dFHA.1136@.TK2MSFTNGP12.phx.gbl...
> Hello,
> We just installed SQL Server developer's edition on a new Windows XP Pro
> workstation. But whenever we bring up the SQL Server service manager, and
> click Start, we get the error:
> "The service did not start due to logon failure"
> When we did the install, we left the "sa" password blank, and we're using
> SQL Server authentication. Can anyone say what might be wrong?
> Thanks!!
>|||Oh, by the way... USE A STRONG PASSWORD FOR 'sa'!!!!
Don't use blanks; a lot of SQL Server exploits use a brute force method
to compromise a system. They'll check for passwords such 'sa' or blank.
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:OfpH2J1dFHA.1136@.TK2MSFTNGP12.phx.gbl...
> Hello,
> We just installed SQL Server developer's edition on a new Windows XP Pro
> workstation. But whenever we bring up the SQL Server service manager, and
> click Start, we get the error:
> "The service did not start due to logon failure"
> When we did the install, we left the "sa" password blank, and we're using
> SQL Server authentication. Can anyone say what might be wrong?
> Thanks!!
>|||Oh yes, for server deployments of SQL Server, we always use strong
passwords, but the workstation in question is at a home office, and not
connected to the Internet. Thanks!
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:O#ZOgN1dFHA.3836@.tk2msftngp13.phx.gbl...
> Oh, by the way... USE A STRONG PASSWORD FOR 'sa'!!!!
> Don't use blanks; a lot of SQL Server exploits use a brute force method
> to compromise a system. They'll check for passwords such 'sa' or blank.
> "Dean J Garrett" <info@.amuletc.com> wrote in message
> news:OfpH2J1dFHA.1136@.TK2MSFTNGP12.phx.gbl...
> > Hello,
> >
> > We just installed SQL Server developer's edition on a new Windows XP Pro
> > workstation. But whenever we bring up the SQL Server service manager,
and
> > click Start, we get the error:
> >
> > "The service did not start due to logon failure"
> >
> > When we did the install, we left the "sa" password blank, and we're
using
> > SQL Server authentication. Can anyone say what might be wrong?
> >
> > Thanks!!
> >
> >
>

Logon failure?

Hello,
We just installed SQL Server developer's edition on a new Windows XP Pro
workstation. But whenever we bring up the SQL Server service manager, and
click Start, we get the error:
"The service did not start due to logon failure"
When we did the install, we left the "sa" password blank, and we're using
SQL Server authentication. Can anyone say what might be wrong?
Thanks!!It's most likely the Windows account that starts up the SQL Server
service. The password is probably expired. Check your SQL
Server Service log on credentials
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:OfpH2J1dFHA.1136@.TK2MSFTNGP12.phx.gbl...
> Hello,
> We just installed SQL Server developer's edition on a new Windows XP Pro
> workstation. But whenever we bring up the SQL Server service manager, and
> click Start, we get the error:
> "The service did not start due to logon failure"
> When we did the install, we left the "sa" password blank, and we're using
> SQL Server authentication. Can anyone say what might be wrong?
> Thanks!!
>|||Oh, by the way... USE A STRONG PASSWORD FOR 'sa'!!!!
Don't use blanks; a lot of SQL Server exploits use a brute force method
to compromise a system. They'll check for passwords such 'sa' or blank.
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:OfpH2J1dFHA.1136@.TK2MSFTNGP12.phx.gbl...
> Hello,
> We just installed SQL Server developer's edition on a new Windows XP Pro
> workstation. But whenever we bring up the SQL Server service manager, and
> click Start, we get the error:
> "The service did not start due to logon failure"
> When we did the install, we left the "sa" password blank, and we're using
> SQL Server authentication. Can anyone say what might be wrong?
> Thanks!!
>|||Oh yes, for server deployments of SQL Server, we always use strong
passwords, but the workstation in question is at a home office, and not
connected to the Internet. Thanks!
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:O#ZOgN1dFHA.3836@.tk2msftngp13.phx.gbl...
> Oh, by the way... USE A STRONG PASSWORD FOR 'sa'!!!!
> Don't use blanks; a lot of SQL Server exploits use a brute force method
> to compromise a system. They'll check for passwords such 'sa' or blank.
> "Dean J Garrett" <info@.amuletc.com> wrote in message
> news:OfpH2J1dFHA.1136@.TK2MSFTNGP12.phx.gbl...
and[vbcol=seagreen]
using[vbcol=seagreen]
>

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.

Logins & password

I need to rebuild SQL Server from Enterprise Edition to Standard Edition.
Is there a way to save the passwords for all SQL logins (SQL
authentication)? I know that restoring master will get the password back
but does it defeat the purpose of changing the SQL Server edition? Thanks!This occur on the same server.
"Flicker" <hthan@.superioraccess.com> wrote in message
news:eI2znzXcDHA.2392@.TK2MSFTNGP10.phx.gbl...
> if you already have the std SQL box you can DTS to transfer logins.
> "Kevin" <kevin@.noemail.com> wrote in message
> news:eom3QuXcDHA.2820@.tk2msftngp13.phx.gbl...
> > I need to rebuild SQL Server from Enterprise Edition to Standard
Edition.
> > Is there a way to save the passwords for all SQL logins (SQL
> > authentication)? I know that restoring master will get the password
back
> > but does it defeat the purpose of changing the SQL Server edition?
> Thanks!
> >
> >
>|||HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q246133#4
Create the stored procedures in the KB article then run sp_help_revlogin in
Query Analyzer with results as text. You can then copy and paste the results
into Notepad and save it. Once you've rebuilt you can run the script to add
your logins with previous SID and passwords
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Kevin" <kevin@.noemail.com> wrote in message
news:eom3QuXcDHA.2820@.tk2msftngp13.phx.gbl...
I need to rebuild SQL Server from Enterprise Edition to Standard Edition.
Is there a way to save the passwords for all SQL logins (SQL
authentication)? I know that restoring master will get the password back
but does it defeat the purpose of changing the SQL Server edition? Thanks!

Login.Language property for sa is not set.

I installed SQL 2000 standard edition.

Then I called server.Logins["sa"].Language

For some reason the value of this property was "" (empty string).

Can anyone explain, why?

P.S. After changing Default language for 'sa' login through Management Studio Language property became accessible..

Alexander Sychev.

The language property is tied to the property set in SQL for the login. By default it is just <default> and that is an empty string. When you set it like you did, the property is explicitly set.

Login.Language property for sa is not set.

I installed SQL 2000 standard edition.

Then I called server.Logins["sa"].Language

For some reason the value of this property was "" (empty string).

Can anyone explain, why?

P.S. After changing Default language for 'sa' login through Management Studio Language property became accessible..

Alexander Sychev.

The language property is tied to the property set in SQL for the login. By default it is just <default> and that is an empty string. When you set it like you did, the property is explicitly set.

login works with SQL but not MSDE

Any idea why the following dmo connection string would not work on MSDE, but
does work on SQL Personal Edition? It appears to connects, but then can't
find the server name. In both cases we are using the latest SQL Personal,
MSDE, and Windows XP.
Thanks for any guidance.
Neil
connection string:
server=myserver;trusted_connection="yes";database= northwind;connection
timeout=10;
exception:
[INFO] 20040517 17:59:34.598 (myControl) Connected successfully to
server=VPC1W2KSQL\VPC1MSDE;Trusted_Connection=yes; database=master;connection
timeout=10;
[EXCEPTION] 20040517 17:59:51.352 (SQLServer) Cannot connect
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or
access denied.
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
at SQLDMO.SQLServerClass.Connect(Object ServerName, Object Login, Object
Password)
at SQLServer.Connect()
Was MSDE installed with the network protocols enabled? With SP3a they are
turned off by default.
Jim
"Neil W." <neilw@.netlib.com> wrote in message
news:eC6V4XdPEHA.1276@.TK2MSFTNGP11.phx.gbl...
> Any idea why the following dmo connection string would not work on MSDE,
but
> does work on SQL Personal Edition? It appears to connects, but then can't
> find the server name. In both cases we are using the latest SQL Personal,
> MSDE, and Windows XP.
> Thanks for any guidance.
> Neil
> connection string:
> server=myserver;trusted_connection="yes";database= northwind;connection
> timeout=10;
> exception:
> [INFO] 20040517 17:59:34.598 (myControl) Connected successfully to
>
server=VPC1W2KSQL\VPC1MSDE;Trusted_Connection=yes; database=master;connection
> timeout=10;
> [EXCEPTION] 20040517 17:59:51.352 (SQLServer) Cannot connect
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or
> access denied.
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
> at SQLDMO.SQLServerClass.Connect(Object ServerName, Object Login,
Object
> Password)
> at SQLServer.Connect()
>
|||This took me a little bit further. I can now actually see the tables in in
my program using the dmo calls. Thanks! owever, when I try to access the
individual columns, I continue to get errors such as:
EXCEPTION] 20040520 08:37:30.955 (sqlserver.myObject) Error retrieving info
from test2: Server user 'mymachine\mylogin' is not a valid user in database
'test2'.
Again, this all works fine using SQL Personal Edition.
Neil
"Jim Young" <thorium48@.hotmail.com> wrote in message
Was MSDE installed with the network protocols enabled? With SP3a they are
turned off by default.
Jim
"Neil W." <neilw@.netlib.com> wrote in message
news:eC6V4XdPEHA.1276@.TK2MSFTNGP11.phx.gbl...
> Any idea why the following dmo connection string would not work on MSDE,
but
> does work on SQL Personal Edition? It appears to connects, but then can't
> find the server name. In both cases we are using the latest SQL Personal,
> MSDE, and Windows XP.
> Thanks for any guidance.
> Neil
> connection string:
> server=myserver;trusted_connection="yes";database= northwind;connection
> timeout=10;
> exception:
> [INFO] 20040517 17:59:34.598 (myControl) Connected successfully to
>
server=VPC1W2KSQL\VPC1MSDE;Trusted_Connection=yes; database=master;connection
> timeout=10;
> [EXCEPTION] 20040517 17:59:51.352 (SQLServer) Cannot connect
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or
> access denied.
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
> at SQLDMO.SQLServerClass.Connect(Object ServerName, Object Login,
Object
> Password)
> at SQLServer.Connect()
>
|||Carefully read the error message. You need to make the user you are
connecting with, in this case a Windows login, a user in the database you
are trying to use. Everyone that can connect to a server can see the schema
of any database, but you can't select or change any data until you are given
explicit permissions in a database.
Jim
"Neil W." <neilw@.netlib.com> wrote in message
news:OShgsvmPEHA.2468@.TK2MSFTNGP11.phx.gbl...
> This took me a little bit further. I can now actually see the tables in
in
> my program using the dmo calls. Thanks! owever, when I try to access the
> individual columns, I continue to get errors such as:
> EXCEPTION] 20040520 08:37:30.955 (sqlserver.myObject) Error retrieving
info
> from test2: Server user 'mymachine\mylogin' is not a valid user in
database[vbcol=seagreen]
> 'test2'.
> Again, this all works fine using SQL Personal Edition.
> Neil
>
> "Jim Young" <thorium48@.hotmail.com> wrote in message
> Was MSDE installed with the network protocols enabled? With SP3a they are
> turned off by default.
> Jim
> "Neil W." <neilw@.netlib.com> wrote in message
> news:eC6V4XdPEHA.1276@.TK2MSFTNGP11.phx.gbl...
> but
can't[vbcol=seagreen]
Personal,
>
server=VPC1W2KSQL\VPC1MSDE;Trusted_Connection=yes; database=master;connection[vbcol=seagreen]
or
> Object
>
>

Login problems with SQL Express edition.

Hi,

when running my application and trying to access an SqlServer express edition DB, I encounter the folowing error:

Cannot open user default database. Login failed.

Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.

Does any one know how this problem can be resolved?

It is the SQL Server that comes togather with the VS2005.

Thank you!

The problem doesnt occure when i run the application using the default web server (cassini). It only occures when I run it using IIS.

But how can i run the application on IIS and use the DB?

Thnaks.