Change Logical File Name Containing a Hyphen

I'm running SQL Server 2000. I have a DB with a logical file name containing
a hyphen. When trying to change the size of the db and log files by running
the following SQL statement it throws an error stating the file name is not
listed in sysfiles:
-- Modify db file size and max size
alter database DB_NAME_DS
MODIFY FILE
(
NAME = [DB_NAME-DS_DATA],
SIZE = 50MB,
MAXSIZE = 100MB,
FILEGROWTH = 50MB
)
-- Modify log file size and max size
alter database DB_NAME_DS
MODIFY FILE
(
NAME = [DB_NAME-DS_Log],
SIZE = 50MB,
MAXSIZE = 100MB,
FILEGROWTH = 50MB
)
GO
I've verified the logical file names using SP_HELPDB. When the DB was
created I did not specify any logical file names. SQL Server assigned them
automatically and put the hyphens in. What is causing the problem with the
SQL command and how can I get around this problem.
Thanks for any help!Please post result of following:
sp_helpfile
"Skippy, DOT DBA" wrote:
> I'm running SQL Server 2000. I have a DB with a logical file name containing
> a hyphen. When trying to change the size of the db and log files by running
> the following SQL statement it throws an error stating the file name is not
> listed in sysfiles:
> -- Modify db file size and max size
> alter database DB_NAME_DS
> MODIFY FILE
> (
> NAME = [DB_NAME-DS_DATA],
> SIZE = 50MB,
> MAXSIZE = 100MB,
> FILEGROWTH = 50MB
> )
> -- Modify log file size and max size
> alter database DB_NAME_DS
> MODIFY FILE
> (
> NAME = [DB_NAME-DS_Log],
> SIZE = 50MB,
> MAXSIZE = 100MB,
> FILEGROWTH = 50MB
> )
> GO
> I've verified the logical file names using SP_HELPDB. When the DB was
> created I did not specify any logical file names. SQL Server assigned them
> automatically and put the hyphens in. What is causing the problem with the
> SQL command and how can I get around this problem.
> Thanks for any help!|||SP_HELPFILE results:
DB_NAME-DS_Data
1 <drive
letter>:\data\DB_NAME-DS_Data.MDF
PRIMARY 32896 KB Unlimited 10% data only
DB_NAME-DS_Log
2 <drive
letter>:\Logs\DB_NAME-DS_Log.LDF
NULL 102400 KB Unlimited 25600 KB log only
"Absar Ahmad" wrote:
> Please post result of following:
> sp_helpfile
> "Skippy, DOT DBA" wrote:
> > I'm running SQL Server 2000. I have a DB with a logical file name containing
> > a hyphen. When trying to change the size of the db and log files by running
> > the following SQL statement it throws an error stating the file name is not
> > listed in sysfiles:
> >
> > -- Modify db file size and max size
> > alter database DB_NAME_DS
> > MODIFY FILE
> > (
> > NAME = [DB_NAME-DS_DATA],
> > SIZE = 50MB,
> > MAXSIZE = 100MB,
> > FILEGROWTH = 50MB
> > )
> > -- Modify log file size and max size
> > alter database DB_NAME_DS
> > MODIFY FILE
> > (
> > NAME = [DB_NAME-DS_Log],
> > SIZE = 50MB,
> > MAXSIZE = 100MB,
> > FILEGROWTH = 50MB
> > )
> > GO
> >
> > I've verified the logical file names using SP_HELPDB. When the DB was
> > created I did not specify any logical file names. SQL Server assigned them
> > automatically and put the hyphens in. What is causing the problem with the
> > SQL command and how can I get around this problem.
> >
> > Thanks for any help!sql

challenging likes wildcard

Hi all,

I need to search the field containing the word I enter, but this word is bracketed by "{{" and "}}". For example, I would like to search the keyword apple, I need to search by the following sql statement.

select name from table where name likes '%{{apple}}%'

But the case is that that column may contain some space between the bracket and the keyword. i.e. {{ apple }}. How can I write a sql statement to search name from the keyword?

Thanks

Spencer

Hi

You could use

SELECT [name] FROM table WHERE REPLACE ([name] , ' ' , '' ) LIKE '%{{apple}}%'

this will remove all spaces for the prurposes of the search query

|||

Thanks Rod,

But this query will search the keyword ap ple , a pple , app le etc. This is the challenge for me.

Spencer

|||

Hi Spencer,

Can you try the below query

SELECT [name]

FROM "table" (nolock)

WHERE [name] LIKE '%' + LTRIM(RTRIM(REPLACE(REPLACE('{{ apple }}','{',''),'}',''))) + '%'

Eralper

http://www.kodyaz.com

|||

You could do something like below:

declare @.a varchar(30), @.b varchar(30), @.c varchar(30)
set @.a = '{{apple }}' -- '{{$apple$}}'
set @.b = substring(@.a, patindex('%{{%apple%}}%', @.a) + 2, 8000)
set @.c = substring(reverse(@.a), patindex(reverse('%{{%apple%}}%'), reverse(@.a)) + 2, 8000)
if @.a like '%{{%apple%}}%' and substring(@.b, 1, patindex('%apple%}}%', @.b) - 1) not like '%[^ ]%'
and substring(@.c, 1, patindex(reverse('%{{%apple%'), @.c) - 1) not like '%[^ ]%'
print 'Y'
else
print 'N'

You can perform this in a SELECT statement by using a single expression. I am assuming that there could be multiple spaces before and after the keyword. So it is not possible to write a single search pattern for LIKE using the TSQL support. The code checks for any non-space character in addition to keyword match.

|||

Thanks Umachandar

Certain records in a table cannot be edited, but others are OK

Hi,
I am using SQL Server 2000 as part of SBS2003.
I have a table containing approx. 25,000 records. It's a fairly simple
table with just 30 fields. There are no triggers on the table.
The table has existed in SQL for at least 12 months and has worked
fine. However, today I have a problem. When I try to edit certain
records in the table, the whole SQL system hangs until I eventually
get an ODBC time out error. Then the system works again, but I cannot
edit these records.
Strangely, the majority of the other records are fine and I can edit
them. This includes records which come before and after the "bad"
records. I have compared two records which fail, with two which are
ok, but I can't see any noticable difference, at least nothing which
might cause this problem.
Anybody got any ideas what might be happening? Presumably some kind of
lock on the records. If so, how do I identify which records are
effected and how do I clear the locks?
Thanks for any help,
ColinBobby
Don't edit the data throu EM .
Do you succed to run UPDATE .... from QA?
"Bobby" <bobby2@.blueyonder.co.uk> wrote in message
news:1191928927.027341.243860@.v3g2000hsg.googlegroups.com...
> Hi,
> I am using SQL Server 2000 as part of SBS2003.
> I have a table containing approx. 25,000 records. It's a fairly simple
> table with just 30 fields. There are no triggers on the table.
> The table has existed in SQL for at least 12 months and has worked
> fine. However, today I have a problem. When I try to edit certain
> records in the table, the whole SQL system hangs until I eventually
> get an ODBC time out error. Then the system works again, but I cannot
> edit these records.
> Strangely, the majority of the other records are fine and I can edit
> them. This includes records which come before and after the "bad"
> records. I have compared two records which fail, with two which are
> ok, but I can't see any noticable difference, at least nothing which
> might cause this problem.
> Anybody got any ideas what might be happening? Presumably some kind of
> lock on the records. If so, how do I identify which records are
> effected and how do I clear the locks?
> Thanks for any help,
> Colin
>|||On 9 Oct, 13:08, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Bobby
> Don't edit the data throu EM .
> Do you succed to run UPDATE .... from QA?
>
No, it won't edit from anywhere, including my application.
A bit more information which I have just discovered. On Monday morning
I had to restore my database to dinner time Friday due to a problem we
had. It appears that all of the records which are "bad" are in the
range which were originally entered on Friday afternoon.
Is this possible. Does this indicate an indexing error. If so, how do
I refresh my indexes?
Thanks
Colin|||Bobby
Howe many rows are affrectyed from your UPDATE? rUN DBCC CHECKDB to see what
is going on?
"Bobby" <bobby2@.blueyonder.co.uk> wrote in message
news:1191933223.214949.93940@.g4g2000hsf.googlegroups.com...
> On 9 Oct, 13:08, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> Bobby
>> Don't edit the data throu EM .
>> Do you succed to run UPDATE .... from QA?
> No, it won't edit from anywhere, including my application.
> A bit more information which I have just discovered. On Monday morning
> I had to restore my database to dinner time Friday due to a problem we
> had. It appears that all of the records which are "bad" are in the
> range which were originally entered on Friday afternoon.
> Is this possible. Does this indicate an indexing error. If so, how do
> I refresh my indexes?
> Thanks
> Colin
>|||On 9 Oct, 13:49, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Bobby
> Howe many rows are affrectyed from your UPDATE? rUN DBCC CHECKDB to see what
> is going on?
> "Bobby" <bob...@.blueyonder.co.uk> wrote in message
> news:1191933223.214949.93940@.g4g2000hsf.googlegroups.com...
>
> > On 9 Oct, 13:08, "Uri Dimant" <u...@.iscar.co.il> wrote:
> >> Bobby
> >> Don't edit the data throu EM .
> >> Do you succed to run UPDATE .... from QA?
> > No, it won't edit from anywhere, including my application.
> > A bit more information which I have just discovered. On Monday morning
> > I had to restore my database to dinner time Friday due to a problem we
> > had. It appears that all of the records which are "bad" are in the
> > range which were originally entered on Friday afternoon.
> > Is this possible. Does this indicate an indexing error. If so, how do
> > I refresh my indexes?
> > Thanks
> > Colin- Hide quoted text -
> - Show quoted text -
15 rows are effected. DBCC CHECKDB shows no errors.|||Bobby
Ok , so when you issue an UDDATE against a "bad" row on TEST server (you
said that you restotred the db) , what error do you get?
"Bobby" <bobby2@.blueyonder.co.uk> wrote in message
news:1191936289.762941.280800@.22g2000hsm.googlegroups.com...
> On 9 Oct, 13:49, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> Bobby
>> Howe many rows are affrectyed from your UPDATE? rUN DBCC CHECKDB to see
>> what
>> is going on?
>> "Bobby" <bob...@.blueyonder.co.uk> wrote in message
>> news:1191933223.214949.93940@.g4g2000hsf.googlegroups.com...
>>
>> > On 9 Oct, 13:08, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> >> Bobby
>> >> Don't edit the data throu EM .
>> >> Do you succed to run UPDATE .... from QA?
>> > No, it won't edit from anywhere, including my application.
>> > A bit more information which I have just discovered. On Monday morning
>> > I had to restore my database to dinner time Friday due to a problem we
>> > had. It appears that all of the records which are "bad" are in the
>> > range which were originally entered on Friday afternoon.
>> > Is this possible. Does this indicate an indexing error. If so, how do
>> > I refresh my indexes?
>> > Thanks
>> > Colin- Hide quoted text -
>> - Show quoted text -
> 15 rows are effected. DBCC CHECKDB shows no errors.
>|||On 9 Oct, 14:27, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Bobby
> Ok , so when you issue an UDDATE against a "bad" row on TEST server (you
> said that you restotred the db) , what error do you get?
>
The error doesn't occur on the test database, only on "live". If I run
the update on live I get,
[Microsoft][ODBC SQL Server Driver]Timeout expired
If I turn off the timeout it hangs indefinately. While it is hung,
nobody can do anything with the table. This only happens for the 15
"bad" records. All of the rest are fine.
Thanks for your help|||What happens if you create a duplicate table (different name) and insert all
rows into that table?
"Bobby" <bobby2@.blueyonder.co.uk> wrote in message
news:1191938900.611210.83780@.r29g2000hsg.googlegroups.com...
> On 9 Oct, 14:27, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> Bobby
>> Ok , so when you issue an UDDATE against a "bad" row on TEST server (you
>> said that you restotred the db) , what error do you get?
> The error doesn't occur on the test database, only on "live". If I run
> the update on live I get,
> [Microsoft][ODBC SQL Server Driver]Timeout expired
> If I turn off the timeout it hangs indefinately. While it is hung,
> nobody can do anything with the table. This only happens for the 15
> "bad" records. All of the rest are fine.
> Thanks for your help
>
>|||On 9 Oct, 15:25, "Jay" <s...@.nospam.org> wrote:
> What happens if you create a duplicate table (different name) and insert all
> rows into that table?
> "Bobby" <bob...@.blueyonder.co.uk> wrote in message
> news:1191938900.611210.83780@.r29g2000hsg.googlegroups.com...
>
> > On 9 Oct, 14:27, "Uri Dimant" <u...@.iscar.co.il> wrote:
> >> Bobby
> >> Ok , so when you issue an UDDATE against a "bad" row on TEST server (you
> >> said that you restotred the db) , what error do you get?
> > The error doesn't occur on the test database, only on "live". If I run
> > the update on live I get,
> > [Microsoft][ODBC SQL Server Driver]Timeout expired
> > If I turn off the timeout it hangs indefinately. While it is hung,
> > nobody can do anything with the table. This only happens for the 15
> > "bad" records. All of the rest are fine.
> > Thanks for your help- Hide quoted text -
> - Show quoted text -|||On 9 Oct, 15:25, "Jay" <s...@.nospam.org> wrote:
> What happens if you create a duplicate table (different name) and insert all
> rows into that table?
>
That seems to have fixed the problem. Only trouble is I can't do it
for real right now due to users on the system. I'll have to wait until
tonight, and login remotely.
Thanks|||Glad it helped.
This goes back to a very old DBA joke:
What part of "dump and reload" don't you understand?
"Bobby" <bobby2@.blueyonder.co.uk> wrote in message
news:1191941917.222229.228010@.d55g2000hsg.googlegroups.com...
> On 9 Oct, 15:25, "Jay" <s...@.nospam.org> wrote:
>> What happens if you create a duplicate table (different name) and insert
>> all
>> rows into that table?
> That seems to have fixed the problem. Only trouble is I can't do it
> for real right now due to users on the system. I'll have to wait until
> tonight, and login remotely.
> Thanks
>|||On 9 Oct, 15:58, Bobby <bob...@.blueyonder.co.uk> wrote:
> On 9 Oct, 15:25, "Jay" <s...@.nospam.org> wrote:
> > What happens if you create a duplicate table (different name) and insert all
> > rows into that table?
> That seems to have fixed the problem. Only trouble is I can't do it
> for real right now due to users on the system. I'll have to wait until
> tonight, and login remotely.
> Thanks
Unfortunately the problem is not resolved. Last night (with nobody
else on the system) I exported my table, deleted the original, created
a new table with the same name and fields as the original and used SQL
Analyser to copy the data from the export to the new table.
Everything was fine, I could modify the "bad" records no problem. This
morning I have just arrived in. Same as yesterday, there are 15
records which I cannot modify. Every other record in the table is ok.
If I look at the data, I can see the changes that I made last night,
but I can no longer modify them.
I'm not sure what to do now, apart from try to forget that the bad
records exist. Anybody got any better ideas?
Thanks for your help,
Confused
Colin