renaming database

I tried to renaming database using query analyser of sql
server 2000 but I got this error msg.
Server: Msg 5030, Level 16, State 2, Line 1
The database could not be exclusively locked to perform
the operation.
My sysntax was
sp_renamedb 'old_dbname', 'new_dbname'
How to handle this? what could be the solution?
Thanx
Dave
hi dave,
make sure no-one else is connected to the database, including you.
you can try:
use master
go
exec sp_renamedb ..........
--or (if sql 2000) you can try
alter database old_Database_name modify name = new_database_name
--if, still you get errors you can run following command,to disconnect other
users:
--note:it will rollback existing transaction
alter database <db_name> set single_user with rollback IMMEDIATE
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||still same proble, bro...
dave
>--Original Message--
>hi dave,
>make sure no-one else is connected to the database,
including you.
>you can try:
>use master
>go
>exec sp_renamedb ..........
>--or (if sql 2000) you can try
>alter database old_Database_name modify name =
new_database_name
>--if, still you get errors you can run following
command,to disconnect other
>users:
>--note:it will rollback existing transaction
>alter database <db_name> set single_user with rollback
IMMEDIATE
>--
>Vishal Parkar
>vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
>
>.
>
|||Try this. Stop the SQL Server from SQL Server Service Manager and Start
again, and then try to rename it.
Rohtash Kapoor
http://www.sqlmantra.com
"dave" <anonymous@.discussions.microsoft.com> wrote in message
news:28f5f01c4656d$493c0f50$a601280a@.phx.gbl...[vbcol=seagreen]
> still same proble, bro...
> dave
> including you.
> new_database_name
> command,to disconnect other
> IMMEDIATE
|||Hi,
Follow the below steps to rename a database in SQL server 2000.
1. Remove all the users connected to the database
alter database <dbname> set single_user with rollback immediate
2. Run the below command to rename the database
alter database <current_db_name> modify name=<new_db_name>
3. Set to multi user mode
alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"dave" <anonymous@.discussions.microsoft.com> wrote in message
news:28e1e01c4654c$a8962ab0$a601280a@.phx.gbl...
> I tried to renaming database using query analyser of sql
> server 2000 but I got this error msg.
> Server: Msg 5030, Level 16, State 2, Line 1
> The database could not be exclusively locked to perform
> the operation.
> My sysntax was
> sp_renamedb 'old_dbname', 'new_dbname'
> How to handle this? what could be the solution?
> Thanx
> Dave
|||all you can do is restart the sql server service, which will clear out all
the connection to the db, and run
use master
go
exec sp_renamedb --
--or (if sql 2000) you can try
alter database old_Database_name modify name = new_database_name
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||You could detach the database and then re-attach it with a different
name.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

renamed server

does anyone have a suggestion for this problem: somewhere along the line, my database server changed names and now I have jobs I cannot delete out of the Agent. Can I go to the table the jobs are in (msdb.sysjobs) and just delete the ones not belonging t
o this server?
You need to update the originating_server column in sysjobs to reflect the
new server name, You can use this proc to do that
http://sqldev.net/download/sqlagent/...ent_rename.sql
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"rich" <anonymous@.discussions.microsoft.com> wrote in message
news:437DE682-C216-4327-B318-85F47F64CD56@.microsoft.com...
> does anyone have a suggestion for this problem: somewhere along the line,
my database server changed names and now I have jobs I cannot delete out of
the Agent. Can I go to the table the jobs are in (msdb.sysjobs) and just
delete the ones not belonging to this server?
sql

renamed server

does anyone have a suggestion for this problem: somewhere along the line, my database server changed names and now I have jobs I cannot delete out of the Agent. Can I go to the table the jobs are in (msdb.sysjobs) and just delete the ones not belonging to this server?You need to update the originating_server column in sysjobs to reflect the
new server name, You can use this proc to do that
http://sqldev.net/download/sqlagent/sp_sqlagent_rename.sql
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"rich" <anonymous@.discussions.microsoft.com> wrote in message
news:437DE682-C216-4327-B318-85F47F64CD56@.microsoft.com...
> does anyone have a suggestion for this problem: somewhere along the line,
my database server changed names and now I have jobs I cannot delete out of
the Agent. Can I go to the table the jobs are in (msdb.sysjobs) and just
delete the ones not belonging to this server?

renamed server

does anyone have a suggestion for this problem: somewhere along the line, my
database server changed names and now I have jobs I cannot delete out of th
e Agent. Can I go to the table the jobs are in (msdb.sysjobs) and just dele
te the ones not belonging t
o this server?You need to update the originating_server column in sysjobs to reflect the
new server name, You can use this proc to do that
http://sqldev.net/download/sqlagent...gent_rename.sql
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"rich" <anonymous@.discussions.microsoft.com> wrote in message
news:437DE682-C216-4327-B318-85F47F64CD56@.microsoft.com...
> does anyone have a suggestion for this problem: somewhere along the line,
my database server changed names and now I have jobs I cannot delete out of
the Agent. Can I go to the table the jobs are in (msdb.sysjobs) and just
delete the ones not belonging to this server?

removing special characters

I have a large table with a text field that includes line break characters. The line break characters are wreaking havoc on other applications, and they need to be removed and replaced with something more friendly. I found a referenced to a stored procedure that can search and replace all strings in a database, but it isn't quite what I need to do, since it only accepts valid keyboard input and not special characters.

Anyone got any suggestions?You should be able to submit the ASCII value of the special character you want to target.

blindman|||If your text field is a varchar or nvarchar you can simply replace the line breaks when selecting the data from the database.

I.E.

SELECT REPLACE(REPLACE(@.StringToBeSearched,Char(10),' '),Char(13), '')

Hope this helps..|||can u post some example so that it can be tested and answered!!!!!!!!!!|||Originally posted by smorton
If your text field is a varchar or nvarchar you can simply replace the line breaks when selecting the data from the database.

I.E.

SELECT REPLACE(REPLACE(@.StringToBeSearched,Char(10),' '),Char(13), '')

Hope this helps..

These are TEXT fields, unfortunately.

Next question: if I can enter the ascii value of the character in the find/replace SP, how do I do so?|||That's what this code is doing for you:

SELECT REPLACE(REPLACE(@.StringToBeSearched,Char(10),' '),Char(13), '')

The CHAR() function converts and integer value to an ASCII character that the REPLACE function can accept. Some common ASCII values are:
Tab: CHAR(9)
Line feed: CHAR(10)
Carriage return: CHAR(13)

If your data is in TEXT format, you may need to use the CAST function to convert it to VARCHAR first:

SELECT REPLACE(REPLACE(Cast(@.StringToBeSearched as varchar(8000)),Char(10),' '),Char(13), '')

blindman|||Originally posted by blindman
That's what this code is doing for you:

SELECT REPLACE(REPLACE(@.StringToBeSearched,Char(10),' '),Char(13), '')

The CHAR() function converts and integer value to an ASCII character that the REPLACE function can accept. Some common ASCII values are:
Tab: CHAR(9)
Line feed: CHAR(10)
Carriage return: CHAR(13)

If your data is in TEXT format, you may need to use the CAST function to convert it to VARCHAR first:

SELECT REPLACE(REPLACE(Cast(@.StringToBeSearched as varchar(8000)),Char(10),' '),Char(13), '')

blindman

I've been instructed to actually copy this table to an Access database on my desktop, where changes I make won't affect any other applications. So, I need to find an Access solution to the same problem. I'm completely clueless when it comes to Access, s I will probably be doing a LOT of research!|||Originally posted by rscrawford
I've been instructed to actually copy this table to an Access database on my desktop, where changes I make won't affect any other applications. So, I need to find an Access solution to the same problem. I'm completely clueless when it comes to Access, s I will probably be doing a LOT of research!

Kinda like going in the wrong direction...can't they just create a table for you or a small database for you on sql server?

Access was (2002 may be don't know) as feature rich as sql server...|||Originally posted by Brett Kaiser
Kinda like going in the wrong direction...can't they just create a table for you or a small database for you on sql server?


You're right, of course. I duplicated the table in question.

Now, forgive me for being a newbie, but... I have the SELECT statement from blindman:

SELECT REPLACE(REPLACE(Cast(@.StringToBeSearched as varchar(8000)),Char(10),' '),Char(13), '')

How do I execute this? I've opened the Query Analyzer, but I don't see where this statement allows for a FROM clause.

Bear in mind, I'm not at all used to SQL Server. Normally, I'd spend a few hours researching on the web, but this particular piece has a priority to it.|||SELECT REPLACE(REPLACE(Cast(@.StringToBeSearched as varchar(8000)),Char(10),' '),Char(13), '')

Replace @.StringToBeSearched with the column name then add your from clause.

I.E.

SELECT REPLACE(REPLACE(Cast(t.ColumnName as varchar(8000)),Char(10),' '),Char(13), '') AS 'ConvertedText'
FROM TableName t

If your column is varchar or nvarchar you do not need to cast the column. If the column is text or ntext the above cast is valid.|||SELECT REPLACE(REPLACE(Cast(t.ColumnName as varchar(8000)),Char(10),' '),Char(13), '') AS 'ConvertedText'
FROM TableName t


Thank you very much!!! That did exactly what I needed it to do!

removing dash lines in query result

Is there any way to remove the dash line in a query result?
use pubs
select emp_id, fname, lname from employee
emp_id fname lname
-- -- --
A-C71970F Aria Cruz
A-R89858F Annette Roulet
AMD15433F Ann Devon
ARD36773F Anabela Domingues
Thanks,
DougTo copy and paste the results ?
Switch to grid format (CTRL + D)
http://sqlservercode.blogspot.com/
"Doug Hood" wrote:

> Is there any way to remove the dash line in a query result?
> use pubs
> select emp_id, fname, lname from employee
> emp_id fname lname
> -- -- --
> A-C71970F Aria Cruz
> A-R89858F Annette Roulet
> AMD15433F Ann Devon
> ARD36773F Anabela Domingues
> Thanks,
> Doug|||Are you copying and pasting the results somewhere else? Try results to grid
instead of results to text. Much more useful for pasting into Excel, at
least.
"Doug Hood" <Doug Hood@.discussions.microsoft.com> wrote in message
news:60C4473C-FF97-4E45-B8B6-D637EE0E12D5@.microsoft.com...
> Is there any way to remove the dash line in a query result?
> use pubs
> select emp_id, fname, lname from employee
> emp_id fname lname
> -- -- --
> A-C71970F Aria Cruz
> A-R89858F Annette Roulet
> AMD15433F Ann Devon
> ARD36773F Anabela Domingues
> Thanks,
> Doug|||No, This ends up as the body of an email. So I need to remove them in the
query.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Are you copying and pasting the results somewhere else? Try results to gr
id
> instead of results to text. Much more useful for pasting into Excel, at
> least.
>
>
> "Doug Hood" <Doug Hood@.discussions.microsoft.com> wrote in message
> news:60C4473C-FF97-4E45-B8B6-D637EE0E12D5@.microsoft.com...
>
>|||Doug,
Might consider using Reporting Services. Much more flexible and robust for
generating reports including via email.
HTH
Jerry
"Doug Hood" <DougHood@.discussions.microsoft.com> wrote in message
news:D544663C-FA83-4AF7-9BAC-24A6AAE1DB91@.microsoft.com...
> No, This ends up as the body of an email. So I need to remove them in the
> query.
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||In query analyzer go to tools options results and un check print column head
ers
"Doug Hood" wrote:
> No, This ends up as the body of an email. So I need to remove them in the
> query.
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||Thanks but that turns off both the dashed line AND the column names. I want
to just remove the dashed line.
Doug
"Lontae Jones" wrote:
> In query analyzer go to tools options results and un check print column he
aders
> "Doug Hood" wrote:
>|||I think you're out of luck. Just delete the line after pasting the data into
your email.
That's assuming that you are using QA to run this query. It's not the query
that's inserting these blank lines: it's QA. You could alway write your own
query execution tool in your favorite programming language and have complete
control over the formatting of the query results.
Bob Barrows
Doug Hood wrote:
> Thanks but that turns off both the dashed line AND the column names.
> I want to just remove the dashed line.
> Doug
> "Lontae Jones" wrote:
>
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Well then I'll say it. You're stuck with them. :-(
You just don't have much control over how QA returns the data.
(and it looks crappy anyway)
The best way I can think of is Jerry's suggestion,
Reporting services or another reporting tool.
"Doug Hood" <DougHood@.discussions.microsoft.com> wrote in message
news:282F0632-3D60-4B31-A8BD-AF9318D986C2@.microsoft.com...
> Thanks but that turns off both the dashed line AND the column names. I
> want
> to just remove the dashed line.
> Doug
> "Lontae Jones" wrote:
>

removing chr(13) and chr(10) from SQL

Hi.

Is there a way to remove carriage returns and line feeds from a field in SQL.

Here is what I have at the moment:

CAST(m.Description AS VARCHAR(8000))AS "Product Description"

(have had to cast as VarChar because of problems with aggregate functions in rest of query).

Problem is that this m.description field has carriage returns and line breaks in it .. how do I remove these inside the SQL statement?

thanksHave you tried REPLACE ( StringExpression1 , StringExpression2 , StringExpression3 ) T-SQL function?|||I tried something like this:

CAST(replace(m.Description, vlbf, ' ') AS VARCHAR(8000))AS "Product Description",

and tried variations of vlbf such as chr(10) chr(13) or \r\n and none of them worked.

I can't put them in single quotes as SQL will look for the actual occurence in the string rather than what it stands for.

I get the error column vlbf is unknown or chr(10) is an unknown function...|||sorted it now.... used this in the end:

REPLACE(CAST(m.Description AS varChar(1000)),char(13) + char(10),' ') AS "Product Description",

Thanks for the help

Removing all end of line character from a string

Hello all,

Im looking for an efficient way to remove all end of line character from a string.

Is there a function to do that or to replace them with another character?

This should help you out:

declare @.string varchar(100),

@.CrLf varchar(2)

set @.String = 'line

with a break'

print @.string

set @.CrLf = char(13) + char(10) -- carriage return + line feed

set @.string = replace(@.string, @.CrLf, '')

print @.string

regards Gert-Jan