I've looked at all the microsoft articles relating to moving a database from
one location to another using the attach/detach commands and I have one
question. Are those commands typed from a command prompt or do I need to run
these from SQL manager?
Hi,
You can execute those commands from
1. Selecting Query Analyzer from SQL Program groups. (issue the user name
and password to login)
Once you go inside the Query analyzer you could execute all TSQL
commands which includes the detach and attach db commands.
2. FROM command prompt execute OSQL
OSQL -Usa -Ppassword -S <SQL Servername> (enter)
This will go to a SQL prompt; there execute those sp_detach_db and
sp_attach_db commands.
Thanks
Hari
SQL Server MVP
"JL Adamson" <JLAdamson@.discussions.microsoft.com> wrote in message
news:8886FE74-93B2-4FD9-997F-25884FA0F953@.microsoft.com...
> I've looked at all the microsoft articles relating to moving a database
> from
> one location to another using the attach/detach commands and I have one
> question. Are those commands typed from a command prompt or do I need to
> run
> these from SQL manager?
Showing posts with label ive. Show all posts
Showing posts with label ive. Show all posts
Sunday, March 25, 2012
Monday, March 19, 2012
Associate User to Login
Is there a way to associate an existing user in a database to an existing
SQL Server login?
This is SQL Server 2000.
I've restored a database from a backup and when I look at the users, the
users which were there before don't have a Login Name associated with them.
The only way I found to associate it is to delete the user and go to the
login to give that user database access (which automatically creates the
user in the database). If I try to give the user database access with the
old user still in the database, it will give me an error indicating that the
user already exists.
The reason for this is I didn't create the database and the users and was
hoping to associate the user's to the obvious logins to avoid changing any
permissions or roles by accident or omission. Using the existing user would
keep the same configuration for that user.
Thank you,
Gary
This is what sp_change_users_login is for.
Or transfer the logins properly in the first place using sp_help_revlogin (search KB for this).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Gary" <tuffboystl@.hotmail.com> wrote in message news:OdGnkRJsFHA.912@.TK2MSFTNGP11.phx.gbl...
> Is there a way to associate an existing user in a database to an existing
> SQL Server login?
> This is SQL Server 2000.
> I've restored a database from a backup and when I look at the users, the
> users which were there before don't have a Login Name associated with them.
> The only way I found to associate it is to delete the user and go to the
> login to give that user database access (which automatically creates the
> user in the database). If I try to give the user database access with the
> old user still in the database, it will give me an error indicating that the
> user already exists.
> The reason for this is I didn't create the database and the users and was
> hoping to associate the user's to the obvious logins to avoid changing any
> permissions or roles by accident or omission. Using the existing user would
> keep the same configuration for that user.
> Thank you,
> Gary
>
|||You can use sp_change_users_login to change the login/user mapping. See the
Books Online for usage details.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gary" <tuffboystl@.hotmail.com> wrote in message
news:OdGnkRJsFHA.912@.TK2MSFTNGP11.phx.gbl...
> Is there a way to associate an existing user in a database to an existing
> SQL Server login?
> This is SQL Server 2000.
> I've restored a database from a backup and when I look at the users, the
> users which were there before don't have a Login Name associated with
> them. The only way I found to associate it is to delete the user and go to
> the login to give that user database access (which automatically creates
> the user in the database). If I try to give the user database access with
> the old user still in the database, it will give me an error indicating
> that the user already exists.
> The reason for this is I didn't create the database and the users and was
> hoping to associate the user's to the obvious logins to avoid changing any
> permissions or roles by accident or omission. Using the existing user
> would keep the same configuration for that user.
> Thank you,
> Gary
>
|||HI,
Execute the below command:-
Use dbname
go
sp_change_users_login 'update_one','login_name','User_name'
Thanks
Hari
SQL Server MVP
"Gary" <tuffboystl@.hotmail.com> wrote in message
news:OdGnkRJsFHA.912@.TK2MSFTNGP11.phx.gbl...
> Is there a way to associate an existing user in a database to an existing
> SQL Server login?
> This is SQL Server 2000.
> I've restored a database from a backup and when I look at the users, the
> users which were there before don't have a Login Name associated with
> them. The only way I found to associate it is to delete the user and go to
> the login to give that user database access (which automatically creates
> the user in the database). If I try to give the user database access with
> the old user still in the database, it will give me an error indicating
> that the user already exists.
> The reason for this is I didn't create the database and the users and was
> hoping to associate the user's to the obvious logins to avoid changing any
> permissions or roles by accident or omission. Using the existing user
> would keep the same configuration for that user.
> Thank you,
> Gary
>
SQL Server login?
This is SQL Server 2000.
I've restored a database from a backup and when I look at the users, the
users which were there before don't have a Login Name associated with them.
The only way I found to associate it is to delete the user and go to the
login to give that user database access (which automatically creates the
user in the database). If I try to give the user database access with the
old user still in the database, it will give me an error indicating that the
user already exists.
The reason for this is I didn't create the database and the users and was
hoping to associate the user's to the obvious logins to avoid changing any
permissions or roles by accident or omission. Using the existing user would
keep the same configuration for that user.
Thank you,
Gary
This is what sp_change_users_login is for.
Or transfer the logins properly in the first place using sp_help_revlogin (search KB for this).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Gary" <tuffboystl@.hotmail.com> wrote in message news:OdGnkRJsFHA.912@.TK2MSFTNGP11.phx.gbl...
> Is there a way to associate an existing user in a database to an existing
> SQL Server login?
> This is SQL Server 2000.
> I've restored a database from a backup and when I look at the users, the
> users which were there before don't have a Login Name associated with them.
> The only way I found to associate it is to delete the user and go to the
> login to give that user database access (which automatically creates the
> user in the database). If I try to give the user database access with the
> old user still in the database, it will give me an error indicating that the
> user already exists.
> The reason for this is I didn't create the database and the users and was
> hoping to associate the user's to the obvious logins to avoid changing any
> permissions or roles by accident or omission. Using the existing user would
> keep the same configuration for that user.
> Thank you,
> Gary
>
|||You can use sp_change_users_login to change the login/user mapping. See the
Books Online for usage details.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gary" <tuffboystl@.hotmail.com> wrote in message
news:OdGnkRJsFHA.912@.TK2MSFTNGP11.phx.gbl...
> Is there a way to associate an existing user in a database to an existing
> SQL Server login?
> This is SQL Server 2000.
> I've restored a database from a backup and when I look at the users, the
> users which were there before don't have a Login Name associated with
> them. The only way I found to associate it is to delete the user and go to
> the login to give that user database access (which automatically creates
> the user in the database). If I try to give the user database access with
> the old user still in the database, it will give me an error indicating
> that the user already exists.
> The reason for this is I didn't create the database and the users and was
> hoping to associate the user's to the obvious logins to avoid changing any
> permissions or roles by accident or omission. Using the existing user
> would keep the same configuration for that user.
> Thank you,
> Gary
>
|||HI,
Execute the below command:-
Use dbname
go
sp_change_users_login 'update_one','login_name','User_name'
Thanks
Hari
SQL Server MVP
"Gary" <tuffboystl@.hotmail.com> wrote in message
news:OdGnkRJsFHA.912@.TK2MSFTNGP11.phx.gbl...
> Is there a way to associate an existing user in a database to an existing
> SQL Server login?
> This is SQL Server 2000.
> I've restored a database from a backup and when I look at the users, the
> users which were there before don't have a Login Name associated with
> them. The only way I found to associate it is to delete the user and go to
> the login to give that user database access (which automatically creates
> the user in the database). If I try to give the user database access with
> the old user still in the database, it will give me an error indicating
> that the user already exists.
> The reason for this is I didn't create the database and the users and was
> hoping to associate the user's to the obvious logins to avoid changing any
> permissions or roles by accident or omission. Using the existing user
> would keep the same configuration for that user.
> Thank you,
> Gary
>
Wednesday, March 7, 2012
Assertion errors? Error: 3624, Severity: 20, State: 1
I've had a sudden rush of about 22 assertion errors come out in the SQL
Server log, that stopped the active replication dead in it's tracks. Most of
the other clients and processes seemed unaffected but I can't understand the
source of some of these errors...
- This first one has been identified under kb 828337 BUT that doesn't help
me drill down to the source of the problem! It occurred the most - about 19
times in 4 minutes.
SQL Server Assertion: File: <p:\sql\ntdbms\storeng\drs\include\record.inl>,
line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
- This next one is mentioned in kb 324630 BUT I was not running DBCC
INDEXDEFRAG as suggested and the SQL Server is SP 3.
Location: page.cpp:1668
Expression: pageFull == 0
SPID: 6
Process ID: 2228
- This one seems more interesting since I cannot find any suitable
information about it on-line
Location: page.cpp:2610
Expression: spaceNeeded <= spaceContig && spaceNeeded <= space_usable
SPID: 6
Process ID: 2228
Any help to track down potential causes and or silent problems resulting
from this would be most appreciated - I am unwilling to run DBCC CHECKDB on a
database of this size (~100GB). The server has 13GB free disk space as well
so I can't see it being a problem related to anything so simple!
This really looks like a bug. Open a support incident with MS on this one.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Robert" <MSDNNospam209@.nospam.nospam> wrote in message
news:50F9478D-E5AA-473F-B79C-0C42CD4F16DD@.microsoft.com...
> I've had a sudden rush of about 22 assertion errors come out in the SQL
> Server log, that stopped the active replication dead in it's tracks. Most
> of
> the other clients and processes seemed unaffected but I can't understand
> the
> source of some of these errors...
> - This first one has been identified under kb 828337 BUT that doesn't help
> me drill down to the source of the problem! It occurred the most - about
> 19
> times in 4 minutes.
> SQL Server Assertion: File:
> <p:\sql\ntdbms\storeng\drs\include\record.inl>,
> line=1447
> Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
> - This next one is mentioned in kb 324630 BUT I was not running DBCC
> INDEXDEFRAG as suggested and the SQL Server is SP 3.
> Location: page.cpp:1668
> Expression: pageFull == 0
> SPID: 6
> Process ID: 2228
> - This one seems more interesting since I cannot find any suitable
> information about it on-line
> Location: page.cpp:2610
> Expression: spaceNeeded <= spaceContig && spaceNeeded <= space_usable
> SPID: 6
> Process ID: 2228
> Any help to track down potential causes and or silent problems resulting
> from this would be most appreciated - I am unwilling to run DBCC CHECKDB
> on a
> database of this size (~100GB). The server has 13GB free disk space as
> well
> so I can't see it being a problem related to anything so simple!
>
|||Hello Robert,
I understand that you encounter many asssertion errors in SQL logs. I'd
like to confirm if you have SQL 2000 SP4 and latest culmulative patch
installed. There is a related known issue was fixed in SP4
841776FIX: Additional diagnostics have been added to SQL Server 2000 to
detect unreported read operation failures
http://support.microsoft.com/default.aspx?scid=kb;EN-US;841776
Please note this issue is most likely caused by hardware or driver related
issues, you may want to enable trace flag 806 as descirbed in 841776 to see
more detailed errors.
Also, as Hilary mentioned, since the issue is related to internal error, if
you cannot solve the issue by above method, to find out the root cause of
this issue we may need to analyze memory dumps, this work has to be done by
contacting Microsoft Product Support Services. Therefore, we probably will
not be able to resolve the issue through the newsgroups. If the issue is
urgent, I recommend that you open a Support incident with Microsoft Product
Support Services so that a dedicated Support Professional can assist with
this case. If you need any help in this regard, please let me know.
For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp
Please let's know if you ahve any questions or concerns. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Robert,
You may have to run the DBCC CHECKDB afterall..
The KBA Peter has below talks about check that SQL will perform after you
install the fix and turn on the trace flag. And it also appears that you'd
probably take some degree of performance hit with the trace flag
enabled...because every read from the disk will be audited by SQL for
consistency once you turnon the trace flag.
But I'd think if the damage has been done already, in some form or fashion,
you'd want to know about it. You could may be restore the backup of the
database on another server and run CHECKDB there.
Thanks
Emaniel
"Peter Yang [MSFT]" wrote:
> Hello Robert,
> I understand that you encounter many asssertion errors in SQL logs. I'd
> like to confirm if you have SQL 2000 SP4 and latest culmulative patch
> installed. There is a related known issue was fixed in SP4
> 841776FIX: Additional diagnostics have been added to SQL Server 2000 to
> detect unreported read operation failures
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;841776
> Please note this issue is most likely caused by hardware or driver related
> issues, you may want to enable trace flag 806 as descirbed in 841776 to see
> more detailed errors.
> Also, as Hilary mentioned, since the issue is related to internal error, if
> you cannot solve the issue by above method, to find out the root cause of
> this issue we may need to analyze memory dumps, this work has to be done by
> contacting Microsoft Product Support Services. Therefore, we probably will
> not be able to resolve the issue through the newsgroups. If the issue is
> urgent, I recommend that you open a Support incident with Microsoft Product
> Support Services so that a dedicated Support Professional can assist with
> this case. If you need any help in this regard, please let me know.
> For a complete list of Microsoft Product Support Services phone numbers,
> please go to the following address on the World Wide Web:
> http://support.microsoft.com/directory/overview.asp
> Please let's know if you ahve any questions or concerns. Thank you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
Server log, that stopped the active replication dead in it's tracks. Most of
the other clients and processes seemed unaffected but I can't understand the
source of some of these errors...
- This first one has been identified under kb 828337 BUT that doesn't help
me drill down to the source of the problem! It occurred the most - about 19
times in 4 minutes.
SQL Server Assertion: File: <p:\sql\ntdbms\storeng\drs\include\record.inl>,
line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
- This next one is mentioned in kb 324630 BUT I was not running DBCC
INDEXDEFRAG as suggested and the SQL Server is SP 3.
Location: page.cpp:1668
Expression: pageFull == 0
SPID: 6
Process ID: 2228
- This one seems more interesting since I cannot find any suitable
information about it on-line
Location: page.cpp:2610
Expression: spaceNeeded <= spaceContig && spaceNeeded <= space_usable
SPID: 6
Process ID: 2228
Any help to track down potential causes and or silent problems resulting
from this would be most appreciated - I am unwilling to run DBCC CHECKDB on a
database of this size (~100GB). The server has 13GB free disk space as well
so I can't see it being a problem related to anything so simple!
This really looks like a bug. Open a support incident with MS on this one.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Robert" <MSDNNospam209@.nospam.nospam> wrote in message
news:50F9478D-E5AA-473F-B79C-0C42CD4F16DD@.microsoft.com...
> I've had a sudden rush of about 22 assertion errors come out in the SQL
> Server log, that stopped the active replication dead in it's tracks. Most
> of
> the other clients and processes seemed unaffected but I can't understand
> the
> source of some of these errors...
> - This first one has been identified under kb 828337 BUT that doesn't help
> me drill down to the source of the problem! It occurred the most - about
> 19
> times in 4 minutes.
> SQL Server Assertion: File:
> <p:\sql\ntdbms\storeng\drs\include\record.inl>,
> line=1447
> Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
> - This next one is mentioned in kb 324630 BUT I was not running DBCC
> INDEXDEFRAG as suggested and the SQL Server is SP 3.
> Location: page.cpp:1668
> Expression: pageFull == 0
> SPID: 6
> Process ID: 2228
> - This one seems more interesting since I cannot find any suitable
> information about it on-line
> Location: page.cpp:2610
> Expression: spaceNeeded <= spaceContig && spaceNeeded <= space_usable
> SPID: 6
> Process ID: 2228
> Any help to track down potential causes and or silent problems resulting
> from this would be most appreciated - I am unwilling to run DBCC CHECKDB
> on a
> database of this size (~100GB). The server has 13GB free disk space as
> well
> so I can't see it being a problem related to anything so simple!
>
|||Hello Robert,
I understand that you encounter many asssertion errors in SQL logs. I'd
like to confirm if you have SQL 2000 SP4 and latest culmulative patch
installed. There is a related known issue was fixed in SP4
841776FIX: Additional diagnostics have been added to SQL Server 2000 to
detect unreported read operation failures
http://support.microsoft.com/default.aspx?scid=kb;EN-US;841776
Please note this issue is most likely caused by hardware or driver related
issues, you may want to enable trace flag 806 as descirbed in 841776 to see
more detailed errors.
Also, as Hilary mentioned, since the issue is related to internal error, if
you cannot solve the issue by above method, to find out the root cause of
this issue we may need to analyze memory dumps, this work has to be done by
contacting Microsoft Product Support Services. Therefore, we probably will
not be able to resolve the issue through the newsgroups. If the issue is
urgent, I recommend that you open a Support incident with Microsoft Product
Support Services so that a dedicated Support Professional can assist with
this case. If you need any help in this regard, please let me know.
For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp
Please let's know if you ahve any questions or concerns. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Robert,
You may have to run the DBCC CHECKDB afterall..
The KBA Peter has below talks about check that SQL will perform after you
install the fix and turn on the trace flag. And it also appears that you'd
probably take some degree of performance hit with the trace flag
enabled...because every read from the disk will be audited by SQL for
consistency once you turnon the trace flag.
But I'd think if the damage has been done already, in some form or fashion,
you'd want to know about it. You could may be restore the backup of the
database on another server and run CHECKDB there.
Thanks
Emaniel
"Peter Yang [MSFT]" wrote:
> Hello Robert,
> I understand that you encounter many asssertion errors in SQL logs. I'd
> like to confirm if you have SQL 2000 SP4 and latest culmulative patch
> installed. There is a related known issue was fixed in SP4
> 841776FIX: Additional diagnostics have been added to SQL Server 2000 to
> detect unreported read operation failures
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;841776
> Please note this issue is most likely caused by hardware or driver related
> issues, you may want to enable trace flag 806 as descirbed in 841776 to see
> more detailed errors.
> Also, as Hilary mentioned, since the issue is related to internal error, if
> you cannot solve the issue by above method, to find out the root cause of
> this issue we may need to analyze memory dumps, this work has to be done by
> contacting Microsoft Product Support Services. Therefore, we probably will
> not be able to resolve the issue through the newsgroups. If the issue is
> urgent, I recommend that you open a Support incident with Microsoft Product
> Support Services so that a dedicated Support Professional can assist with
> this case. If you need any help in this regard, please let me know.
> For a complete list of Microsoft Product Support Services phone numbers,
> please go to the following address on the World Wide Web:
> http://support.microsoft.com/directory/overview.asp
> Please let's know if you ahve any questions or concerns. Thank you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
Assertion errors? Error: 3624, Severity: 20, State: 1
I've had a sudden rush of about 22 assertion errors come out in the SQL
Server log, that stopped the active replication dead in it's tracks. Most of
the other clients and processes seemed unaffected but I can't understand the
source of some of these errors...
- This first one has been identified under kb 828337 BUT that doesn't help
me drill down to the source of the problem! It occurred the most - about 19
times in 4 minutes.
SQL Server Assertion: File: <p:\sql\ntdbms\storeng\drs\include\record.inl>,
line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
- This next one is mentioned in kb 324630 BUT I was not running DBCC
INDEXDEFRAG as suggested and the SQL Server is SP 3.
Location: page.cpp:1668
Expression: pageFull == 0
SPID: 6
Process ID: 2228
- This one seems more interesting since I cannot find any suitable
information about it on-line
Location: page.cpp:2610
Expression: spaceNeeded <= spaceContig && spaceNeeded <= space_usable
SPID: 6
Process ID: 2228
Any help to track down potential causes and or silent problems resulting
from this would be most appreciated - I am unwilling to run DBCC CHECKDB on a
database of this size (~100GB). The server has 13GB free disk space as well
so I can't see it being a problem related to anything so simple!
This really looks like a bug. Open a support incident with MS on this one.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Robert" <MSDNNospam209@.nospam.nospam> wrote in message
news:50F9478D-E5AA-473F-B79C-0C42CD4F16DD@.microsoft.com...
> I've had a sudden rush of about 22 assertion errors come out in the SQL
> Server log, that stopped the active replication dead in it's tracks. Most
> of
> the other clients and processes seemed unaffected but I can't understand
> the
> source of some of these errors...
> - This first one has been identified under kb 828337 BUT that doesn't help
> me drill down to the source of the problem! It occurred the most - about
> 19
> times in 4 minutes.
> SQL Server Assertion: File:
> <p:\sql\ntdbms\storeng\drs\include\record.inl>,
> line=1447
> Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
> - This next one is mentioned in kb 324630 BUT I was not running DBCC
> INDEXDEFRAG as suggested and the SQL Server is SP 3.
> Location: page.cpp:1668
> Expression: pageFull == 0
> SPID: 6
> Process ID: 2228
> - This one seems more interesting since I cannot find any suitable
> information about it on-line
> Location: page.cpp:2610
> Expression: spaceNeeded <= spaceContig && spaceNeeded <= space_usable
> SPID: 6
> Process ID: 2228
> Any help to track down potential causes and or silent problems resulting
> from this would be most appreciated - I am unwilling to run DBCC CHECKDB
> on a
> database of this size (~100GB). The server has 13GB free disk space as
> well
> so I can't see it being a problem related to anything so simple!
>
Server log, that stopped the active replication dead in it's tracks. Most of
the other clients and processes seemed unaffected but I can't understand the
source of some of these errors...
- This first one has been identified under kb 828337 BUT that doesn't help
me drill down to the source of the problem! It occurred the most - about 19
times in 4 minutes.
SQL Server Assertion: File: <p:\sql\ntdbms\storeng\drs\include\record.inl>,
line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
- This next one is mentioned in kb 324630 BUT I was not running DBCC
INDEXDEFRAG as suggested and the SQL Server is SP 3.
Location: page.cpp:1668
Expression: pageFull == 0
SPID: 6
Process ID: 2228
- This one seems more interesting since I cannot find any suitable
information about it on-line
Location: page.cpp:2610
Expression: spaceNeeded <= spaceContig && spaceNeeded <= space_usable
SPID: 6
Process ID: 2228
Any help to track down potential causes and or silent problems resulting
from this would be most appreciated - I am unwilling to run DBCC CHECKDB on a
database of this size (~100GB). The server has 13GB free disk space as well
so I can't see it being a problem related to anything so simple!
This really looks like a bug. Open a support incident with MS on this one.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Robert" <MSDNNospam209@.nospam.nospam> wrote in message
news:50F9478D-E5AA-473F-B79C-0C42CD4F16DD@.microsoft.com...
> I've had a sudden rush of about 22 assertion errors come out in the SQL
> Server log, that stopped the active replication dead in it's tracks. Most
> of
> the other clients and processes seemed unaffected but I can't understand
> the
> source of some of these errors...
> - This first one has been identified under kb 828337 BUT that doesn't help
> me drill down to the source of the problem! It occurred the most - about
> 19
> times in 4 minutes.
> SQL Server Assertion: File:
> <p:\sql\ntdbms\storeng\drs\include\record.inl>,
> line=1447
> Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
> - This next one is mentioned in kb 324630 BUT I was not running DBCC
> INDEXDEFRAG as suggested and the SQL Server is SP 3.
> Location: page.cpp:1668
> Expression: pageFull == 0
> SPID: 6
> Process ID: 2228
> - This one seems more interesting since I cannot find any suitable
> information about it on-line
> Location: page.cpp:2610
> Expression: spaceNeeded <= spaceContig && spaceNeeded <= space_usable
> SPID: 6
> Process ID: 2228
> Any help to track down potential causes and or silent problems resulting
> from this would be most appreciated - I am unwilling to run DBCC CHECKDB
> on a
> database of this size (~100GB). The server has 13GB free disk space as
> well
> so I can't see it being a problem related to anything so simple!
>
Saturday, February 25, 2012
aspnetdb.mdf on Hosting Server ?
I have established roles to restrict access to the dataentry pages. The database is SQL Server which I've established on the hosting server. Security was previously maintained through~/app_data/aspnetdb.mdb (MS Access). It works, but isn't too stable, so I have modified the security to useSQL Server with an aspnetdb.mdf file, also located in the app_datafolder. Is this appropriate? Since it doesn't work, I assume not.
It's now my assumption that I need to setup a SQL Serverdatabase to support the security/roles. I have seen some reference tousing a script to build the aspnet.mdf on the hosting server. IfI do create such a database, is there anything special I need to do tohave my application read the aspnetdb? Do I need a connection stringin the membership and/or roleManager sections of my web.config.
A little guidance would be appreciated.
Thanks
http://www.eggheadcafe.com/articles/20060529.asp
|||
Thank you Dr. Bromberg for the extensive material you've pointed me to. I have downloaded the code and created a web site on my local Visual Studio server. I was able to run the SQL to create the two databases, Articles and aspnetdb (I think that's what I was suppose to do?). I have modifed the web.config to reflect the localhost information. When I run the Default.aspx I receive the following error:
Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection.
OK, something's not setup properly. I attempted to run the SetupASPNetDatabase.aspx page and get absolutely nowhere, most likely because I have no idea what values to submit in the query boxes.
Bottom line is, I am not clear on what I need to do to get started. I am sure there is considerable information to glean from your article and sample web site. I would genuinely like to learn from it. The problem I seem to be having through is getting the security configured, which takes me back to square one with my initial question. I feel as if I am in the proverbial Catch 22. I need to get my own hosted security working and/or get your sample working before I can get either of them working.
To try and answer your question about setting up security with SQL Server...
1. Install the security schema (tables, procedures etc) on the SQL Server database. You can do this on your local machine by running aspnetregsql.exe (it's an installation wizard which comes with the ASP.Net framwork). If you have full control on your hosted server, then install the same way, otherwise your hosting provider will have to assist (some offer the installation as an option via their Control Panel). Note that the database doesn't have to be called ASPNETDB, you can install the security schema into an existing database of a different name.
2. Set up a connection string that points explicitly at the SQL Server database i.e. server name, database name, user id and password.
3. Point the Membership provider at this connection string.
I've put a specific example from one of my own web.config files using SQL Server - this is the connection string from mydevelopment environment (so uses localhost for the server name). Can you see that the Membership provider points at the "MainDB" connection string - which is pointing at the SQL Server?
The live web.config settings are very similar, except that instead of localhost I have the name of the server. Hope this helps!
<connectionStrings><addname="MainDB"connectionString="Server=localhost;Database=DB_138621;User ID=*****;Password=*****"providerName="System.Data.SqlClient" />
</connectionStrings>--- snip -----
<membershipdefaultProvider="AspNetSqlMembershipProvider"><providers>
<clear/><addname="AspNetSqlMembershipProvider"type="System.Web.Security.SqlMembershipProvider"connectionStringName="MainDB"minRequiredPasswordLength="5"minRequiredNonalphanumericCharacters="0"requiresQuestionAndAnswer="false"enablePasswordRetrieval="true"enablePasswordReset="false" applicationName="/"/>
</providers>
|||
Thank you Salmon. Using your example and wading through my web.config with fat fingers, I was finally able to make this work.
Friday, February 24, 2012
ASPNETDB replaces my database in connection
Hi,
I've a little problem. When I connect my local server by this connection string:Data Source=NB126\SQLEXPRESS;Initial Catalog=TVRM;Integrated Security=TrueI get content of ASPNETDB.MDF (placed in App_Data directory) instead content of my own database named TVRM. Please help...
Hi ciTrus,
Are you sure your connection string is actually used when connecting to database?(a small trick here is that, assign a wrong server name which doesnot exist to your connection string and test again. see if your data still can be populated).
Rember that there is a default "AspNetSqlMembershipProvider" in you machine.config and if you are using sql express, this provider will direct your membership database to App_Data folder silently (which i think is the case you are currently in). If you want to use a custom membership database, you need to add a new membership provider and put it to your web.config.
like: <system.web>
<membership defaultProvider="SqlProvider" userIsOnlineTimeWindow="20">
<providers>
<remove name="AspNetSqlProvider" />
<add name="SqlProvider"
type="System.Web.Security.SqlMembershipProvider"
connectionStringName=HERE IS YOUR CONNECTION STRING NAME
enablePasswordRetrieval="false"
enablePasswordReset="true"
requiresQuestionAndAnswer="true"
passwordFormat="Hashed"
applicationName="/" />
</providers>
</membership>
</system.web>
Hope my suggestion helps
|||
Bo Chen,
thank you very much. I did exactly what you wrote and it worked.
CiTrus
Sunday, February 12, 2012
Asp.net DataSet Object
I've been using the asp.net's DataSet and it seems to be leaving connections open, I'm not entirly sure if its due to the DataSet or some random code I forgot to close its connection, but I just wanted to cover all of my Basis. Is it possible though, that this DataSet object is leaving connections open?
Thanks for reading!
-Teenprogrammer
Hey,
A dataset works in disconnected mode; it wouldn't be the dataset that might be the problem, but the object querying it, like the command/dataadapter. Could you post any code? That would be helpful. As far as the ADO.NET base objects, I have not heard of that being a problem by someone else.
ASP.NET C# Calendar DataAccess Issue
Hello there
I'm having a little issue with Updating a SQL Table. *Deep Breath* OK, lets begin from the start...
I Have a Table Called Job. I've an ASP Page setup to insert a new record into the Job table via a DetailsView Control with an SQLDataSource bound to it. This table has many columns in it, 4 of which are DateTime columns. I use a calendar Control from the toolbox for the DateTime selection and bound all the Calender control's SelectedDate to: SelectedDate='<%# Bind('EnteredDate') %>' using the EnteredDate field as my example.
I only insert DateTime Values into 2 of the 4 DateTime columns in the original INSERT, so the remaining 2 columns are NULL. I have an UPDATE setup on a different page to edit the previously inserted rows in job using another SQLDataSource again using the bind method for calendars: '<%# Bind('RevisedDate') %>'
My Problem is (according to this post I found onlinehttp://www.msdner.com/forum/thread529052.html ) the page crashes with "Specified cast is not valid"
. The reason being I'm trying to bind to the remaining 2 NULL DateTime columns in an attempt to update them with a value my user will not be able to insert in the original INSERT. The solution Code I found on the link above is in VisualBasic and I have had little luck in trying to convert it into C# to even see if it solves my problem:
VB Code:
Function FixDBNull(ByVal inVal As Object) As DateTime
If inVal Is DBNull.Value Then
Return DateTime.Now
Else
Return inVal
End If
End Function
ASP Code:
<asp:calendar id="calEditRevisedDate" runat="server"
selecteddaystyle-backcolor="red"
selecteddate='<%#FixDBNull(eval("RevisedDate")) %>'
visibledate='<%# FixDBNull( eval("RevisedDate")) %>'
selectorstyle-backcolor="Green">
</asp:calendar>
Any suggestions or work arounds would be greatly appreciated
Thank You
Hi Hornwook509,
Do you want to convert that vb code to C#? If yes, try the code below
protected DateTime FixDBNull(DateTime inVal){if(inVal==DBNull.value)return DateTime.Now;else return inVal;}|||Thank you for the reply
I get the following compile error with the code you supplied... "Operator '==' cannot be applied to operands of type 'System.DateTime' and 'System.DBNull' "
The Null DB Field is quite an annoying problem. As a means of overstepping this frustrating issue I have resorted to using 3rd party software that handles the NULL DB Date Entry http://www.basicdatepicker.com/download/. I'm just running low on time trying to sort this issue out
The free version (BDP Lite) of this software (as far as i can tell) does not handle the the Null DateTime column problem, but the Registered full version of basic Date Time Picker does.
|||forget about the dbnull issue. Try inVal==null instead. I think it should work
|||I managed to sort my problem out. Found this link onlinehttp://www.syncfusion.com/FAQ/aspnet/WEB_c8c.aspx
This bit of the page in particular
using System.Data.SqlTypes;
Hope this helps anybody who is having Null DateTime issues
Thursday, February 9, 2012
ASP.NET 2.0 / ASP and SQL Server 2005
I'm having to do some maintenance on a legacy site done in "classic" ASP. I've taken the site onto our new internal win2k3 box as it doesn't need to be public any longer and put the DB on sql server 2005. All good so far.
I can't get the app to connect to the DB at all. If I use the exact same connection string that works in an asp.net 2.0 app on the same server, it doesn't work. I don't even have to do a query, merely opening the connection will break it.
I use...
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.open "Data Source=********;Initial Catalog=intranet_database;Integrated Security=True" '(that's the connection string that works just fine on the asp.net app)
I get..
Microsoft OLE DB Service Components error '80040e21'
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
/v1/testing/default.asp, line 13
Most of the stuff I can see relates to datatypes and so on, but given the above I can't see how that might be.
Any ideas? Please! ;-)
I solved this by creating a black UDL file on the desktop and it generated a very different connection string. When I try and grant the ISR_MACHINENAME user access to the database I keep getting this not very helpful error message.
|||
Can't you edit a post? What I meant to say was...
Yeskin Gallen:
I solved this by creating a blank UDL file on the desktop and it generated a very different connection string. When I try and grant the IUSR_MACHINENAME user access to the database I keep getting this not very helpful error message...
TITLE: Microsoft SQL Server Management Studio
----------
Cannot show requested dialog.
----------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
----------
The server principal "BEL-INTRANET-01\IUSR_BEL-INTRANET-01" is not able to access the database "self-builder-v1" under the current security context. (Microsoft SQL Server, Error: 916)
For help, click:http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=916&LinkId=20476
----------
BUTTONS:
OK
----------
ASP.NET 2 Data Binding with SQL 2005 XML
I am starting a project where I will be using the XML data type in SQL Server 2005, and ASP.NET to display and update the content of the XML.
I've been digging around for a bit trying to find some sources that will explain the process of doing data binding in ASP.NET with the SQL XML, but haven't been able to locate anything too useful yet.
The XML will be charts, each holding 3 groups of 70 rows of data, each row having 5 fields (boolean, datetime, datetime, int, string).
I will need to pull 1 or more charts down to be rendered in a web page, modified, and then stored back to SQL.
I'm assuming from what I've read so far that this is possible, but I need more information and hopefully some examples.
I've worked somewhat with SQL 2000 and ASP.NET 2 (mostly hand-written pages), but want to get full bore into using VS2005 to do this. I am very familiar with XML, XSLT, HTML, C# and JavaScript, moderately familiar with SQL and VS.
Any leads to web sites and books that would help along these lines would also be appreciated.
Greg Collins [Microsoft MVP]
Visit Braintrove ( http://www.braintrove.com )
On Mar 21, 6:15 pm, "Greg Collins [Microsoft MVP]"
<gcollins_AT_msn_DOT_com> wrote:
> I am starting a project where I will be using the XML data type in SQL Server 2005, and ASP.NET to display and update the content of the XML.
> I've been digging around for a bit trying to find some sources that will explain the process of doing data binding in ASP.NET with the SQL XML, but haven't been able to locate anything too useful yet.
> The XML will be charts, each holding 3 groups of 70 rows of data, each row having 5 fields (boolean, datetime, datetime, int, string).
> I will need to pull 1 or more charts down to be rendered in a web page, modified, and then stored back to SQL.
> I'm assuming from what I've read so far that this is possible, but I need more information and hopefully some examples.
> I've worked somewhat with SQL 2000 and ASP.NET 2 (mostly hand-written pages), but want to get full bore into using VS2005 to do this. I am very familiar with XML, XSLT, HTML, C# and JavaScript, moderately familiar with SQL and VS.
> Any leads to web sites and books that would help along these lines would also be appreciated.
> --
> Greg Collins [Microsoft MVP]
> Visit Braintrove (http://www.braintrove.com)
Work with XML Data Type in SQL Server 2005 from ADO.NET 2.0
http://www.developer.com/net/net/article.php/3406251
XML data type tips in SQL Server 2005
http://www.codeproject.com/dotnet/XMLdDataType.asp
and...
http://www.google.com/search?hl=en&q=XML+data+type+SQL+2005+asp.net
I've been digging around for a bit trying to find some sources that will explain the process of doing data binding in ASP.NET with the SQL XML, but haven't been able to locate anything too useful yet.
The XML will be charts, each holding 3 groups of 70 rows of data, each row having 5 fields (boolean, datetime, datetime, int, string).
I will need to pull 1 or more charts down to be rendered in a web page, modified, and then stored back to SQL.
I'm assuming from what I've read so far that this is possible, but I need more information and hopefully some examples.
I've worked somewhat with SQL 2000 and ASP.NET 2 (mostly hand-written pages), but want to get full bore into using VS2005 to do this. I am very familiar with XML, XSLT, HTML, C# and JavaScript, moderately familiar with SQL and VS.
Any leads to web sites and books that would help along these lines would also be appreciated.
Greg Collins [Microsoft MVP]
Visit Braintrove ( http://www.braintrove.com )
On Mar 21, 6:15 pm, "Greg Collins [Microsoft MVP]"
<gcollins_AT_msn_DOT_com> wrote:
> I am starting a project where I will be using the XML data type in SQL Server 2005, and ASP.NET to display and update the content of the XML.
> I've been digging around for a bit trying to find some sources that will explain the process of doing data binding in ASP.NET with the SQL XML, but haven't been able to locate anything too useful yet.
> The XML will be charts, each holding 3 groups of 70 rows of data, each row having 5 fields (boolean, datetime, datetime, int, string).
> I will need to pull 1 or more charts down to be rendered in a web page, modified, and then stored back to SQL.
> I'm assuming from what I've read so far that this is possible, but I need more information and hopefully some examples.
> I've worked somewhat with SQL 2000 and ASP.NET 2 (mostly hand-written pages), but want to get full bore into using VS2005 to do this. I am very familiar with XML, XSLT, HTML, C# and JavaScript, moderately familiar with SQL and VS.
> Any leads to web sites and books that would help along these lines would also be appreciated.
> --
> Greg Collins [Microsoft MVP]
> Visit Braintrove (http://www.braintrove.com)
Work with XML Data Type in SQL Server 2005 from ADO.NET 2.0
http://www.developer.com/net/net/article.php/3406251
XML data type tips in SQL Server 2005
http://www.codeproject.com/dotnet/XMLdDataType.asp
and...
http://www.google.com/search?hl=en&q=XML+data+type+SQL+2005+asp.net
Subscribe to:
Posts (Atom)