From Sql-server enterprise manager : Management-Current Activity-Locks/Process ID, I found several locks, which preventing some of application working property.
So, I want to remove locks manually.
How Can I do this?do sp_who2 and post the results...
Do mean you blocking? Locks are held until the work is done...which is what should be happening...
if it's a blocked spid, you'll probably have to kill the blocking spid...|||I tried to kill process of the locks(property, Kill Process) but, it doesn't work!
1 BACKGROUND sa . . NULL LAZY WRITER 563 0 06/15 13:44:44 1
2 BACKGROUND sa . . master SIGNAL HANDLER 31 0 06/15 13:44:44 2
3 sleeping sa . . NULL LOG WRITER 9719 0 06/15 13:44:44 3
4 BACKGROUND sa . . NULL LOCK MONITOR 141 0 06/15 13:44:44 4
5 BACKGROUND sa . . master TASK MANAGER 0 530 06/15 13:44:44 5
6 BACKGROUND sa . . master TASK MANAGER 0 0 06/15 13:44:44 6
7 sleeping sa . . NULL CHECKPOINT SLEEP 94 282 06/15 13:44:44 7
8 BACKGROUND sa . . master TASK MANAGER 0 0 06/15 13:44:44 8
9 BACKGROUND sa . . master TASK MANAGER 0 11 06/15 13:44:44 9
10 BACKGROUND sa . . master TASK MANAGER 0 0 06/15 13:44:44 10
11 BACKGROUND sa . . master TASK MANAGER 0 0 06/15 13:44:44 11
12 BACKGROUND sa . . master TASK MANAGER 0 0 06/15 13:44:44 12
13 BACKGROUND sa . . master TASK MANAGER 0 20 06/15 13:44:44 13
14 BACKGROUND sa . . master TASK MANAGER 0 10 06/15 13:44:44 14
15 BACKGROUND sa . . master TASK MANAGER 0 0 06/15 13:44:44 15
51 sleeping HRUser IT009 . TimeClock AWAITING COMMAND 9892 228 06/21 13:05:58 MS SQLEM 51
52 sleeping HRUser MIS008 . TimeClock AWAITING COMMAND 0 0 06/21 13:19:14 .Net SqlClient Data Provider 52
53 sleeping HRUser MIS008 . TimeClock AWAITING COMMAND 0 0 06/21 13:19:14 .Net SqlClient Data Provider 53
54 sleeping HRUser IT009 . TimeClock AWAITING COMMAND 0 0 06/21 13:07:43 prjMapDrive 54
55 RUNNABLE HRUser IT009 . TimeClock SELECT INTO 16 3 06/21 13:19:21 SQL Query Analyzer 55
56 sleeping HRUser PAY0516 . TimeClock AWAITING COMMAND 63 0 06/21 12:58:38 56
57 sleeping NT_DOMAIN\Administrator MIS008 . msdb AWAITING COMMAND 93 29 06/17 09:57:05 SQLAgent - Generic Refresher 57
58 sleeping NT_DOMAIN\Administrator MIS008 . msdb AWAITING COMMAND 601318 1 06/21 13:19:26 SQLAgent - Alert Engine 58
63 sleeping HRUser PAY0516 . TimeClock AWAITING COMMAND 219 0 06/21 12:59:05 63|||Which process did you try to kill?|||I do not see any blocking activity in this output. Are you sure there is a problem? Maybe I am not understanding what the problem is?|||I want to remove TimeClock DB related locks..
Since My application has problem with transaction on TimeClock DB, it sometimes begins transaction and doesn't commit or rollback. In this case problem occurs...
Then I have to remove locks related those transaction. but, I couldn't...
I just stopped Sql server and restarted.. But, I want to remove locks without restarting and affecting other DBs.
Thanks...|||Fix the application. Anytime an application has a timeout, it should automatically roll back the transaction. Killing processes is dangerous and should only be used in rare, extreme cases.|||If the application is leaving transactions open, without committing them, you can use dbcc opentran to identify the connection (SPID) that is at fault. This command must be run from the TimeClock database. With this information, you can convince the vendor/contractor/programmer to clean up their mess.sql
Showing posts with label process. Show all posts
Showing posts with label process. Show all posts
Wednesday, March 28, 2012
How to remove default schema?
Hi,
Re: sql server 2005
I recently migrated a database from one server to another. In the process of doing this, I renamed one of the sql server 2000 migrated logins to a new name, using "alter login with name" and "alter user with name"
Now, I'm having problems with permissions. This user cannot execute sp_send_dbmail, even though it has explicit execute permissions on this stored proc in the msdb database.
What I noticed about this user is that is carried over a default schema from the old 2000 server. I'm beginning to think that this has something to do with the lost permissions. So I tried to drop the default schema. No dice. Next, I set the default schema to dbo. That still didn't work.
Can someone please tell me how to drop a default schema for a user? I've tried everything I can think of, from removing the schema name from the properties windows, to "alter user". Nothing has worked.
Thanks
You cannot remove, you can just change the default schema.
Jens K. Suessmeyer
http://www.sqlserver2005.de
|||Thanks. Yes, I gathered as much.
I realize that the problem with sp_send_dbmail didn't have anything to do with the default schema. It had to do with the fact that the login wasn't mapped to the msdb database, even though it existed as a user in the msdb database. (long story)
Dropping the user, then remapping the login to the user solved the problem.
However, some of the migrated users do NOT have a default schema assigned to them. How is this possible?
All of the other migrated users DID have a default schema, i.e.) the user name = default schema name.
How to Remove Carriage Returns from database field?
I have saved user input from an html text area into a MS SQL 2000
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the help
Hi Peter !
See if the values are related to the value of which is a carriage
return CHAR(13)
http://www.asciitable.com, then REPLACE it with the REPLACE function to
whatever you want to display.
HTH, Jens Suessmeyer.
|||Try:
update MyTable
set
MyCol = replace (MyCol, char (13), '')
where
MyCol like '%' + char (13)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
<peter.rietmann@.swisscom.com> wrote in message
news:1129721023.545769.275420@.o13g2000cwo.googlegr oups.com...
I have saved user input from an html text area into a MS SQL 2000
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the help
|||Just to be a nut picker ;-).
You don=B4t need that:
where=20
MyCol like '%' + char (13)=20
Jens Suessmeyer.
|||that's great, it has solved my problem. thanks
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the help
Hi Peter !
See if the values are related to the value of which is a carriage
return CHAR(13)
http://www.asciitable.com, then REPLACE it with the REPLACE function to
whatever you want to display.
HTH, Jens Suessmeyer.
|||Try:
update MyTable
set
MyCol = replace (MyCol, char (13), '')
where
MyCol like '%' + char (13)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
<peter.rietmann@.swisscom.com> wrote in message
news:1129721023.545769.275420@.o13g2000cwo.googlegr oups.com...
I have saved user input from an html text area into a MS SQL 2000
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the help
|||Just to be a nut picker ;-).
You don=B4t need that:
where=20
MyCol like '%' + char (13)=20
Jens Suessmeyer.
|||that's great, it has solved my problem. thanks
How to Remove Carriage Returns from database field?
I have saved user input from an html text area into a MS SQL 2000
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the helpHi Peter !
See if the values are related to the value of which is a carriage
return CHAR(13)
http://www.asciitable.com, then REPLACE it with the REPLACE function to
whatever you want to display.
HTH, Jens Suessmeyer.|||Try:
update MyTable
set
MyCol = replace (MyCol, char (13), '')
where
MyCol like '%' + char (13)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<peter.rietmann@.swisscom.com> wrote in message
news:1129721023.545769.275420@.o13g2000cwo.googlegroups.com...
I have saved user input from an html text area into a MS SQL 2000
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the help|||Just to be a nut picker ;-).
You don=B4t need that:
where=20
MyCol like '%' + char (13)=20
Jens Suessmeyer.|||that's great, it has solved my problem. thanks
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the helpHi Peter !
See if the values are related to the value of which is a carriage
return CHAR(13)
http://www.asciitable.com, then REPLACE it with the REPLACE function to
whatever you want to display.
HTH, Jens Suessmeyer.|||Try:
update MyTable
set
MyCol = replace (MyCol, char (13), '')
where
MyCol like '%' + char (13)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<peter.rietmann@.swisscom.com> wrote in message
news:1129721023.545769.275420@.o13g2000cwo.googlegroups.com...
I have saved user input from an html text area into a MS SQL 2000
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the help|||Just to be a nut picker ;-).
You don=B4t need that:
where=20
MyCol like '%' + char (13)=20
Jens Suessmeyer.|||that's great, it has solved my problem. thanks
How to Remove Carriage Returns from database field?
I have saved user input from an html text area into a MS SQL 2000
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the helpHi Peter !
See if the values are related to the value of which is a carriage
return CHAR(13)
http://www.asciitable.com, then REPLACE it with the REPLACE function to
whatever you want to display.
HTH, Jens Suessmeyer.|||Try:
update MyTable
set
MyCol = replace (MyCol, char (13), '')
where
MyCol like '%' + char (13)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<peter.rietmann@.swisscom.com> wrote in message
news:1129721023.545769.275420@.o13g2000cwo.googlegroups.com...
I have saved user input from an html text area into a MS SQL 2000
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the help|||Just to be a nut picker ;-).
You don=B4t need that:
where MyCol like '%' + char (13)
Jens Suessmeyer.|||that's great, it has solved my problem. thanks
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the helpHi Peter !
See if the values are related to the value of which is a carriage
return CHAR(13)
http://www.asciitable.com, then REPLACE it with the REPLACE function to
whatever you want to display.
HTH, Jens Suessmeyer.|||Try:
update MyTable
set
MyCol = replace (MyCol, char (13), '')
where
MyCol like '%' + char (13)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<peter.rietmann@.swisscom.com> wrote in message
news:1129721023.545769.275420@.o13g2000cwo.googlegroups.com...
I have saved user input from an html text area into a MS SQL 2000
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the help|||Just to be a nut picker ;-).
You don=B4t need that:
where MyCol like '%' + char (13)
Jens Suessmeyer.|||that's great, it has solved my problem. thanks
Monday, March 26, 2012
How to release user connections?
I'm in the process of moving our database server to new hardware, but when I
try to use the Copy Database Wizard it finds to concurrent users and won't
allow for the copy. How can I find out who is on and then disconnect them?exec sp_who2 to find them
KILL spid to close them
You only need to kill the spid's where the spid is greater than 50
http://sqlservercode.blogspot.com/|||EXEC sp_who for discovering who is connected
KILL spid for killing a connection
Both are documented in Books On Line
--
Med Bouchenafa
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:2D491056-49C8-4061-BE1E-5E1F6F77D722@.microsoft.com...
> I'm in the process of moving our database server to new hardware, but when
> I
> try to use the Copy Database Wizard it finds to concurrent users and won't
> allow for the copy. How can I find out who is on and then disconnect them?
>|||Another method for forcibly terminate database connections is:
ALTER DATABASE MyDatabase
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
and then back to normal:
ALTER DATABASE MyDatabase
SET MULTI_USER
See the Books Online for more information.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:2D491056-49C8-4061-BE1E-5E1F6F77D722@.microsoft.com...
> I'm in the process of moving our database server to new hardware, but when
> I
> try to use the Copy Database Wizard it finds to concurrent users and won't
> allow for the copy. How can I find out who is on and then disconnect them?
>|||I find that stopping and starting the SQL services usually does the
trick. Nice and easy too.|||Thats a hard one if you have more than one userdatabase one your
server, because ALL serverusers will be wiped out of the system, so
better altering the database like DAN stated would be the best (it is
also my perferable solution, for a hard kick-off)
HTH, jens Suessmeyer.
try to use the Copy Database Wizard it finds to concurrent users and won't
allow for the copy. How can I find out who is on and then disconnect them?exec sp_who2 to find them
KILL spid to close them
You only need to kill the spid's where the spid is greater than 50
http://sqlservercode.blogspot.com/|||EXEC sp_who for discovering who is connected
KILL spid for killing a connection
Both are documented in Books On Line
--
Med Bouchenafa
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:2D491056-49C8-4061-BE1E-5E1F6F77D722@.microsoft.com...
> I'm in the process of moving our database server to new hardware, but when
> I
> try to use the Copy Database Wizard it finds to concurrent users and won't
> allow for the copy. How can I find out who is on and then disconnect them?
>|||Another method for forcibly terminate database connections is:
ALTER DATABASE MyDatabase
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
and then back to normal:
ALTER DATABASE MyDatabase
SET MULTI_USER
See the Books Online for more information.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:2D491056-49C8-4061-BE1E-5E1F6F77D722@.microsoft.com...
> I'm in the process of moving our database server to new hardware, but when
> I
> try to use the Copy Database Wizard it finds to concurrent users and won't
> allow for the copy. How can I find out who is on and then disconnect them?
>|||I find that stopping and starting the SQL services usually does the
trick. Nice and easy too.|||Thats a hard one if you have more than one userdatabase one your
server, because ALL serverusers will be wiped out of the system, so
better altering the database like DAN stated would be the best (it is
also my perferable solution, for a hard kick-off)
HTH, jens Suessmeyer.
How to release user connections?
I'm in the process of moving our database server to new hardware, but when I
try to use the Copy Database Wizard it finds to concurrent users and won't
allow for the copy. How can I find out who is on and then disconnect them?
exec sp_who2 to find them
KILL spid to close them
You only need to kill the spid's where the spid is greater than 50
http://sqlservercode.blogspot.com/
|||EXEC sp_who for discovering who is connected
KILL spid for killing a connection
Both are documented in Books On Line
Med Bouchenafa
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:2D491056-49C8-4061-BE1E-5E1F6F77D722@.microsoft.com...
> I'm in the process of moving our database server to new hardware, but when
> I
> try to use the Copy Database Wizard it finds to concurrent users and won't
> allow for the copy. How can I find out who is on and then disconnect them?
>
|||Another method for forcibly terminate database connections is:
ALTER DATABASE MyDatabase
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
and then back to normal:
ALTER DATABASE MyDatabase
SET MULTI_USER
See the Books Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:2D491056-49C8-4061-BE1E-5E1F6F77D722@.microsoft.com...
> I'm in the process of moving our database server to new hardware, but when
> I
> try to use the Copy Database Wizard it finds to concurrent users and won't
> allow for the copy. How can I find out who is on and then disconnect them?
>
|||I find that stopping and starting the SQL services usually does the
trick. Nice and easy too.
|||Thats a hard one if you have more than one userdatabase one your
server, because ALL serverusers will be wiped out of the system, so
better altering the database like DAN stated would be the best (it is
also my perferable solution, for a hard kick-off)
HTH, jens Suessmeyer.
try to use the Copy Database Wizard it finds to concurrent users and won't
allow for the copy. How can I find out who is on and then disconnect them?
exec sp_who2 to find them
KILL spid to close them
You only need to kill the spid's where the spid is greater than 50
http://sqlservercode.blogspot.com/
|||EXEC sp_who for discovering who is connected
KILL spid for killing a connection
Both are documented in Books On Line
Med Bouchenafa
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:2D491056-49C8-4061-BE1E-5E1F6F77D722@.microsoft.com...
> I'm in the process of moving our database server to new hardware, but when
> I
> try to use the Copy Database Wizard it finds to concurrent users and won't
> allow for the copy. How can I find out who is on and then disconnect them?
>
|||Another method for forcibly terminate database connections is:
ALTER DATABASE MyDatabase
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
and then back to normal:
ALTER DATABASE MyDatabase
SET MULTI_USER
See the Books Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:2D491056-49C8-4061-BE1E-5E1F6F77D722@.microsoft.com...
> I'm in the process of moving our database server to new hardware, but when
> I
> try to use the Copy Database Wizard it finds to concurrent users and won't
> allow for the copy. How can I find out who is on and then disconnect them?
>
|||I find that stopping and starting the SQL services usually does the
trick. Nice and easy too.
|||Thats a hard one if you have more than one userdatabase one your
server, because ALL serverusers will be wiped out of the system, so
better altering the database like DAN stated would be the best (it is
also my perferable solution, for a hard kick-off)
HTH, jens Suessmeyer.
How to release user connections?
I'm in the process of moving our database server to new hardware, but when I
try to use the Copy Database Wizard it finds to concurrent users and won't
allow for the copy. How can I find out who is on and then disconnect them?exec sp_who2 to find them
KILL spid to close them
You only need to kill the spid's where the spid is greater than 50
http://sqlservercode.blogspot.com/|||EXEC sp_who for discovering who is connected
KILL spid for killing a connection
Both are documented in Books On Line
Med Bouchenafa
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:2D491056-49C8-4061-BE1E-5E1F6F77D722@.microsoft.com...
> I'm in the process of moving our database server to new hardware, but when
> I
> try to use the Copy Database Wizard it finds to concurrent users and won't
> allow for the copy. How can I find out who is on and then disconnect them?
>|||Another method for forcibly terminate database connections is:
ALTER DATABASE MyDatabase
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
and then back to normal:
ALTER DATABASE MyDatabase
SET MULTI_USER
See the Books Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:2D491056-49C8-4061-BE1E-5E1F6F77D722@.microsoft.com...
> I'm in the process of moving our database server to new hardware, but when
> I
> try to use the Copy Database Wizard it finds to concurrent users and won't
> allow for the copy. How can I find out who is on and then disconnect them?
>|||I find that stopping and starting the SQL services usually does the
trick. Nice and easy too.|||Thats a hard one if you have more than one userdatabase one your
server, because ALL serverusers will be wiped out of the system, so
better altering the database like DAN stated would be the best (it is
also my perferable solution, for a hard kick-off)
HTH, jens Suessmeyer.
try to use the Copy Database Wizard it finds to concurrent users and won't
allow for the copy. How can I find out who is on and then disconnect them?exec sp_who2 to find them
KILL spid to close them
You only need to kill the spid's where the spid is greater than 50
http://sqlservercode.blogspot.com/|||EXEC sp_who for discovering who is connected
KILL spid for killing a connection
Both are documented in Books On Line
Med Bouchenafa
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:2D491056-49C8-4061-BE1E-5E1F6F77D722@.microsoft.com...
> I'm in the process of moving our database server to new hardware, but when
> I
> try to use the Copy Database Wizard it finds to concurrent users and won't
> allow for the copy. How can I find out who is on and then disconnect them?
>|||Another method for forcibly terminate database connections is:
ALTER DATABASE MyDatabase
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
and then back to normal:
ALTER DATABASE MyDatabase
SET MULTI_USER
See the Books Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:2D491056-49C8-4061-BE1E-5E1F6F77D722@.microsoft.com...
> I'm in the process of moving our database server to new hardware, but when
> I
> try to use the Copy Database Wizard it finds to concurrent users and won't
> allow for the copy. How can I find out who is on and then disconnect them?
>|||I find that stopping and starting the SQL services usually does the
trick. Nice and easy too.|||Thats a hard one if you have more than one userdatabase one your
server, because ALL serverusers will be wiped out of the system, so
better altering the database like DAN stated would be the best (it is
also my perferable solution, for a hard kick-off)
HTH, jens Suessmeyer.
Monday, March 12, 2012
How to Receive emails
I've recently migrated to sql 2005 and I'm trying to find a solution
to some new requests.
I need to setup a system that process excel spreadsheet received by
email. The sheets will contain data coming from veterinaries that -
after an integration with centrally stored data - should trigger a
report.
I can receive emails both on Lotus Notes (that I don't manage) or POP3
account.
Can't' install Exchange because of the centralized Active Directory
policy.
Anyone, who had a similar issue, can recommend me a simple and robust
solution?
The alternative is to set up a web page to upload the files, but the
IIS server is located on the intranet, so people will need to dial a
modem or use the vpn. Not straight as an email...
Many Thanks,
josephOn Oct 11, 3:37 am, curi...@.spore.it wrote:
> I've recently migrated to sql 2005 and I'm trying to find a solution
> to some new requests.
> I need to setup a system that process excel spreadsheet received by
> email. The sheets will contain data coming from veterinaries that -
> after an integration with centrally stored data - should trigger a
> report.
> I can receive emails both on Lotus Notes (that I don't manage) or POP3
> account.
> Can't' install Exchange because of the centralized Active Directory
> policy.
> Anyone, who had a similar issue, can recommend me a simple and robust
> solution?
> The alternative is to set up a web page to upload the files, but the
> IIS server is located on the intranet, so people will need to dial a
> modem or use the vpn. Not straight as an email...
> Many Thanks,
> joseph
You will most likely want to set this up via a custom ASP.NET
application. You can either have an SSIS pkg triggered through an
ASP.NET application to loop through a directory and import several
excel files into a table, etc -or- you can use bulk insert or BCP
through an ASP.NET application'/stored procedure to import the excel
spreadsheets. Then you could call Reporting Services through the web
service method (http://msdn2.microsoft.com/en-us/library/
microsoft.wssux.reportingserviceswebservice.rsexecutionservice2005.reportexecutionservice.render.aspx )
to create a report. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant
to some new requests.
I need to setup a system that process excel spreadsheet received by
email. The sheets will contain data coming from veterinaries that -
after an integration with centrally stored data - should trigger a
report.
I can receive emails both on Lotus Notes (that I don't manage) or POP3
account.
Can't' install Exchange because of the centralized Active Directory
policy.
Anyone, who had a similar issue, can recommend me a simple and robust
solution?
The alternative is to set up a web page to upload the files, but the
IIS server is located on the intranet, so people will need to dial a
modem or use the vpn. Not straight as an email...
Many Thanks,
josephOn Oct 11, 3:37 am, curi...@.spore.it wrote:
> I've recently migrated to sql 2005 and I'm trying to find a solution
> to some new requests.
> I need to setup a system that process excel spreadsheet received by
> email. The sheets will contain data coming from veterinaries that -
> after an integration with centrally stored data - should trigger a
> report.
> I can receive emails both on Lotus Notes (that I don't manage) or POP3
> account.
> Can't' install Exchange because of the centralized Active Directory
> policy.
> Anyone, who had a similar issue, can recommend me a simple and robust
> solution?
> The alternative is to set up a web page to upload the files, but the
> IIS server is located on the intranet, so people will need to dial a
> modem or use the vpn. Not straight as an email...
> Many Thanks,
> joseph
You will most likely want to set this up via a custom ASP.NET
application. You can either have an SSIS pkg triggered through an
ASP.NET application to loop through a directory and import several
excel files into a table, etc -or- you can use bulk insert or BCP
through an ASP.NET application'/stored procedure to import the excel
spreadsheets. Then you could call Reporting Services through the web
service method (http://msdn2.microsoft.com/en-us/library/
microsoft.wssux.reportingserviceswebservice.rsexecutionservice2005.reportexecutionservice.render.aspx )
to create a report. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant
Friday, March 9, 2012
How to read the log of transaction of database
Hello
I tray to read the log of transactions of one databese,
but I don't know which are the process, Could you please
helpme..
Saludos desde ColombiaSQL Server transaction log architecture is SQL Server
proprietary. Only tool that im aware to read "transaction
log" is "log explorer" from Lumigent.
Visit
www.lumigent.com
- Vishal
>--Original Message--
>Hello
>I tray to read the log of transactions of one databese,
>but I don't know which are the process, Could you please
>helpme..
>
>Saludos desde Colombia
>.
>|||I know of a 3rd party tool that lets you view the SQL Transaction Log.
Lumigent Log Explorer
--
HTH
Ryan Waight, MCDBA, MCSE
"David Tinjaca" <tinjano@.hotmail.com> wrote in message
news:256801c38697$623833e0$a301280a@.phx.gbl...
> Hello
> I tray to read the log of transactions of one databese,
> but I don't know which are the process, Could you please
> helpme..
>
> Saludos desde Colombia
I tray to read the log of transactions of one databese,
but I don't know which are the process, Could you please
helpme..
Saludos desde ColombiaSQL Server transaction log architecture is SQL Server
proprietary. Only tool that im aware to read "transaction
log" is "log explorer" from Lumigent.
Visit
www.lumigent.com
- Vishal
>--Original Message--
>Hello
>I tray to read the log of transactions of one databese,
>but I don't know which are the process, Could you please
>helpme..
>
>Saludos desde Colombia
>.
>|||I know of a 3rd party tool that lets you view the SQL Transaction Log.
Lumigent Log Explorer
--
HTH
Ryan Waight, MCDBA, MCSE
"David Tinjaca" <tinjano@.hotmail.com> wrote in message
news:256801c38697$623833e0$a301280a@.phx.gbl...
> Hello
> I tray to read the log of transactions of one databese,
> but I don't know which are the process, Could you please
> helpme..
>
> Saludos desde Colombia
Wednesday, March 7, 2012
How to read active log file
I am programming to read read sqlserver log file without shutdowning sqlserver. However it is said that the file is used by another process which I believe is sqlserver.
Is there any way to open log file without shutdowning server? I know that Log Explorer can read online log file. But I do not know the technology they are using.Any help is very appreciated.|||I also know that 'dbcc log' and 'select * from ::fn_dblog(null,null)' can list log data. But I do not want to connect to sqlserver. I just want to open the log file and read the data from the log file directly. Thanks.|||Why? There are ways you can do this, but there are so problems to work around that it probably isn't worth the effort when there are so many easier ways to achieve the same results.
-PatP
Is there any way to open log file without shutdowning server? I know that Log Explorer can read online log file. But I do not know the technology they are using.Any help is very appreciated.|||I also know that 'dbcc log' and 'select * from ::fn_dblog(null,null)' can list log data. But I do not want to connect to sqlserver. I just want to open the log file and read the data from the log file directly. Thanks.|||Why? There are ways you can do this, but there are so problems to work around that it probably isn't worth the effort when there are so many easier ways to achieve the same results.
-PatP
Subscribe to:
Posts (Atom)