Showing posts with label instance. Show all posts
Showing posts with label instance. Show all posts

Friday, March 30, 2012

Replacing Default Instance

Our default instance of SQL 2000 is corrupted. The msdb database
tables do not have several columns required by the SQL Agent. The
corruption apparently occurred while running the SQL Wizard to convert
our 6.5 version to 2000. TO rectify the problem we successfully
installed a new instance of SQL which gave us a clean set of msdb and
master tables. We then copied our user databases over to the new
instance. My question is this: can we now make that new instance the
default instance? How can we do this? Thanks.You can't rename an instance, backup/restore is the way to go (or some simil
ar thing). Before you do that,
script out the job definitions etc from the msdb database so you can run tho
se scripts in the dest db.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jim" <jromano@.phs-us.com> wrote in message news:e1e1a687.0403241251.1c364809@.posting.googl
e.com...
> Our default instance of SQL 2000 is corrupted. The msdb database
> tables do not have several columns required by the SQL Agent. The
> corruption apparently occurred while running the SQL Wizard to convert
> our 6.5 version to 2000. TO rectify the problem we successfully
> installed a new instance of SQL which gave us a clean set of msdb and
> master tables. We then copied our user databases over to the new
> instance. My question is this: can we now make that new instance the
> default instance? How can we do this? Thanks.|||Hi ,
Add on to Tibor, Instead of moving the user databases to new named instance,
why dont you take a backup of MSDB database from the new
named instance and restore it in Default instance.
This is under the assumption only your MSDB has got issues. This case all
your Logins/Backup devices and configuration info in Master
database can be preserved.
Have a look into the below article (Just read the MSDB part only)
http://support.microsoft.com/defaul...b;EN-US;Q224071
If you have problems in detach/attach, try with backup and restore strategy.
Thanks
Hari
MCDBA
"Jim" <jromano@.phs-us.com> wrote in message
news:e1e1a687.0403241251.1c364809@.posting.google.com...
> Our default instance of SQL 2000 is corrupted. The msdb database
> tables do not have several columns required by the SQL Agent. The
> corruption apparently occurred while running the SQL Wizard to convert
> our 6.5 version to 2000. TO rectify the problem we successfully
> installed a new instance of SQL which gave us a clean set of msdb and
> master tables. We then copied our user databases over to the new
> instance. My question is this: can we now make that new instance the
> default instance? How can we do this? Thanks.

Wednesday, March 28, 2012

Replacing blank records with text

Hi there,

I'm in a bit of a jam here and will appreciate any help.

I need the SQL code to replace a record if the record is empty.

For instance, I have about 7 columns containing over 40K records. In the firstname field, some records are blank. I need to replace all the blank firstname fields with this: 'now invalid' (without the quotes)

What would be the best way to achieve this?

Thanks

newbie:oUpdate [YourTable]
set FirstName = isnull(FirstName, 'now invalid'),
LastName = isnull(LastName, 'now invalid'),
etc...|||Thanks alot for your response blindman.

I didnt add that for the blank firstname fields I want to replace, all their lastnames contain valid data which I do not want to replace. all the lastnames are 'Design Professional'

So i modified the sql like this:

Update [My Table]
set FirstName = isnull(FirstName, 'Now Invalid')
where lastname = 'Design Professional'

I think the above query has a problem because it generated an error saying:
saying 'wrong number of arguements used with function in query expression isnull(FirstName, 'now invalid')

can you help with this?

Thank you very much|||Thanks alot for your response blindman.

I didnt add that for the blank firstname fields I want to replace, all their lastnames contain valid data which I do not want to replace. all the lastnames are 'Design Professional'

So i modified the sql like this:

Update [My Table]
set FirstName = isnull(FirstName, 'Now Invalid')
where lastname = 'Design Professional'

I think the above query has a problem because it generated an error saying:
saying 'wrong number of arguements used with function in query expression isnull(FirstName, 'now invalid')

can you help with this?

Thank you very much|||Are you using SQL Server or MS Access? The isnull() function is different in TSQL than in Access VB.|||If you are in Access look up NZ in help. Same thing as the T-SQL ISNull basically. IsNull in Access means something totally different to IsNull in T-SQL.|||Thanks a lot for the suggestions. I'm using MS Access.

Replacing a text globally

I have a instance with many databases in it.

due to company/product name change,

I want to search for a string "xyz" in
database name,
table name,
column name,
stored procedure name
content of all stored procedures

and replace all of them with "abc" without affecting the databases an application.

Can u please help me with step by step guidance?

muralidaran rYou want to change the names of things without affecting an application that uses this data?
Slow down and think about what effects this will have.|||hi

thanks for your reply

the only change expected from application is the change of connection string.

Muralidaran r|||Your application doesn't do anything like

SELECT Field1 FROM xyzTable

?|||My application code never uses table name or column name.

it uses only stored procedure names.

muralidaran r|||I want to search for a string "xyz" in
database name,
table name,
column name,
stored procedure name
content of all stored procedures

I rest my case.|||If your application calls stored procedure names have "xyz" in them, then there is no solution to your problem... When you rename the called procedures, the application will fail (because it will still try to use the old names that contain "xyz" instead of "abc").

-PatP|||due to company/product name change,

Can u please help me with step by step guidance?

Sure, learn how to code correctly|||Thank you guys. Now I will retype the question. try to understand better. Actually the coding was done by someone else and now i have to manage this. OK. try to give a solution.

I have a instance with many databases in it.

due to company/product name change,

I want to search for a string "xyz" in
database name,
table name,
column name,
content of all stored procedures

and replace all of them with "abc"

Can u please help me with step by step guidance?

muralidaran r|||It shouldn't be done IMO.
Users don't see database name(s), table names, column names or content of stored procedures. Users should only see data that you let them see.|||How to search for a string "xyz" in

database name,

table name,

column name,

content of all stored procedures

muralidaran|||I want to search for a string "xyz" in
...
and replace all of them with "abc"

I did listen and I responded appropriately. My advice is don't do it, simple as that.

As always you can chose to ignore my advice but I can assure you that others will respond in a similar fashion.|||Hi

Already i started changing the string by some manuall method which is tedious and hard.

at each and every stage i am checking the applications performance and accuracy.

I need help to speed up this.|||Speaking from a SQL2000 perspective, you need to check "name" in sysobjects for tablenames, view names, proc names, etc. You need to check text in syscomments for sproc content, etc, you need to check syscolumns for column names, etc.

I'm sure for every place you catch, there will be 2 you don't catch.

I'm with GeorgeV, DO NOT DO IT. Let sleeping dogs lay.

Have fun.|||Dear muralidaran_r,

Have you understood what the users here are telling you?
If you change a table name from xyzMyTable to abcMyTable, then all views, triggers, procedures functions, client side calls etc must be modified to point to the new name.

Or are you asking how to search for and change data stored in the tables?|||muralidaran,

Script your entire database to a single text file. Do a search and replace in that text file, and then run the script against a new empty database, as I am sure there will be many errors that occur and that will need to be fixed.|||muralidaran,

Script your entire database to a single text file. Do a search and replace in that text file, and then run the script against a new empty database, as I am sure there will be many errors that occur and that will need to be fixed.

....or, find another career|||"If you change a table name from xyzMyTable to abcMyTable, then all views, triggers, procedures functions, client side calls etc must be modified to point to the new name"

Yes to modify all, is there any automated method, tool, procedure available.

Muralidaran r|||No, not that I know of.

Monday, March 26, 2012

Replace strings in Text column

Hi!
I would like to replace some strings (for instance 'mystring1' with 'mystring2') in a column of datatype Text. Replace function does not work with Text columns. The following works:
update mytable set myfield=replace(convert(varchar(8000), myfield),'mystring1','mystring2')
but it truncates data the exceed the 8000 bytes. Ofcourse I have some rows containing more than 8000 bytes in that field, that's why it is set a Text.
Any ideas?You might be able to use PATINDEX along with UPDATETEXT to replace all occurances in your TEXT column. Have a look here:

http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part3/c1161.mspx
http://www.aspfaq.com/show.asp?id=2445

However, I think it is more effective to do such things client-side.
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. (http://www.sqlpass.de)
|||Thanks Frank. This will do the job.sql

Wednesday, March 21, 2012

replace instance of multiple char(32)

I have several fileds in a sql table which holds some text and a hell of a lot of white spaces (char(32)) which is causing some problems when I show the data on the front-end

What I want to do is use sql replace function to replace the char(32). However, because there is text I can't do a simple replace as the text will become just one word !

Can anyone tell me how to loop through the field to find the char(32), where the char(32) is greater than say 5 sapces and then replace this with just one single space

E.g. a field in a table contains the following :

"my text field and loads of spaces then some more text with loads of spaces "

I want to replace the char(32) with just one space so it looks like this :
"my text filed and loads of spaces then some more text with loads of spaces"

Thanks for your helpI'd try using:SELECT Replace('Target string with spaces', ' ', ' ')-PatP|||I had to run the replace twice...

declare @.Targetstring varchar(50)
set @.Targetstring = '1 2 3 4 5 6'

select @.TargetString
select Replace(@.TargetString, ' ', ' ') --Not quite there yet...
select Replace(Replace(@.TargetString, ' ', ' '), ' ', ' ') --Lookin' good!|||Replace is kinda like that... It only makes one pass through the string on its own. It can be coerced into doing the right thing, if you have a big enough stick!

-PatP

Tuesday, March 20, 2012

replace a column with the contents of another with SQL?

Is there any way to replace the contents of a column with the contents of another column with SQL?

For instance, suppose I want to decrease the field size of the primary key from 15 to 10. In order to do that with Oracle I would need to copy the column data to a temporary location, null out the original column, decrease the size, then copy the keys back.

I am having trouble with the last step. I can't seem to figure out how to copy the keys back into there realitive locations.

Can anyone offer any help?

Thanks,
JaminWhat did you use to copy the data to the temporary location?

If you want to copy data from one column to another:

update table1
set column1 = column2;

(Make sure the data in column2 is clean and will not violate any constraints in column1)|||Will that work if I want to copy the column from a different table?|||Urquel's solution would work if you altered the table by adding another column, copy your primary key column into the new one, decrease size of the primary key column and update your table by copying new column value into the primary key column.

If you don't want to alter the table that way, would you consider this: create table as select * From the original table; truncate the original one, decrease primary key column, insert into original table select * from new table.
But, you might encounter problems if the primary key values are referenced by foreign keys (temporarily remove bindings and enable them after import is done).

Another way: export the table, truncate it, decrease size of the primary key column, import data (using IGNORE=Y). Same thing with referential integritiy as above, of course.

Monday, March 12, 2012

Repeated 'Starting up database' messages

Hi,
Our network monitoring software reported the following message :
The database "MSCRM" in the instance "MSSQLSERVER" is in a critical state.
The state is: Suspect
After looking at SQL Enterprise Manager, the database appears to be fine and
I can browse tables, views, stored procedures and query the database as
normal. However when I looked at the SQL Server Error Log I found many
messages reporting 'Starting up database 'MSCRM'.
Any ideas?
Jonathan
Here is a sample log from this morning :
2004-07-02 09:29:53.76 spid89 Starting up database 'MSCRM'.
2004-07-02 09:29:54.48 spid89 Starting up database 'MSCRM'.
2004-07-02 09:29:59.17 spid89 Starting up database 'MSCRM'.
2004-07-02 09:29:59.43 spid89 Starting up database 'MSCRM'.
2004-07-02 09:29:59.67 spid89 Starting up database 'MSCRM'.
2004-07-02 09:29:59.93 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:00.24 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:00.48 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:00.82 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:01.06 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:01.46 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:01.74 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:02.03 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:02.32 spid89 Starting up database 'MSCRM'.
2004-07-02 09:32:19.81 spid89 Starting up database 'MSCRM'.
2004-07-02 09:45:00.48 spid72 Starting up database 'MSCRM'.
2004-07-02 09:45:01.06 spid72 Starting up database 'MSCRM'.
2004-07-02 10:00:00.23 spid91 Starting up database 'MSCRM'.
2004-07-02 10:00:00.65 spid91 Starting up database 'MSCRM'.
2004-07-02 10:00:00.89 spid101 Starting up database 'MSCRM'.
2004-07-02 10:00:01.60 spid101 Starting up database 'MSCRM'.
2004-07-02 10:00:06.99 spid91 Starting up database 'MSCRM'.
Turn off autoclose for the database. What probably happened is that you have autoclose, some other
process (like anti-virus) got hold of the file while SQL Server didn't have it opened, SQL server
tried to access the file, ouch!, suspect database. Next restart, file weren't open by other process,
SQL Server turned off the autoclose option.
Autoclose is not designed for production databases!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jonathan Wareham" <JonathanW@.CooperParry.com> wrote in message
news:OHdeeaBYEHA.644@.tk2msftngp13.phx.gbl...
> Hi,
> Our network monitoring software reported the following message :
> The database "MSCRM" in the instance "MSSQLSERVER" is in a critical state.
> The state is: Suspect
> After looking at SQL Enterprise Manager, the database appears to be fine and
> I can browse tables, views, stored procedures and query the database as
> normal. However when I looked at the SQL Server Error Log I found many
> messages reporting 'Starting up database 'MSCRM'.
> Any ideas?
> Jonathan
> Here is a sample log from this morning :
>
> 2004-07-02 09:29:53.76 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:29:54.48 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:29:59.17 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:29:59.43 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:29:59.67 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:29:59.93 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:00.24 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:00.48 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:00.82 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:01.06 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:01.46 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:01.74 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:02.03 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:02.32 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:32:19.81 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:45:00.48 spid72 Starting up database 'MSCRM'.
> 2004-07-02 09:45:01.06 spid72 Starting up database 'MSCRM'.
> 2004-07-02 10:00:00.23 spid91 Starting up database 'MSCRM'.
> 2004-07-02 10:00:00.65 spid91 Starting up database 'MSCRM'.
> 2004-07-02 10:00:00.89 spid101 Starting up database 'MSCRM'.
> 2004-07-02 10:00:01.60 spid101 Starting up database 'MSCRM'.
> 2004-07-02 10:00:06.99 spid91 Starting up database 'MSCRM'.
>
|||Many thanks Tibor!
Jonathan
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23U0H0dBYEHA.3156@.TK2MSFTNGP12.phx.gbl...
> Turn off autoclose for the database. What probably happened is that you
have autoclose, some other
> process (like anti-virus) got hold of the file while SQL Server didn't
have it opened, SQL server
> tried to access the file, ouch!, suspect database. Next restart, file
weren't open by other process,[vbcol=seagreen]
> SQL Server turned off the autoclose option.
> Autoclose is not designed for production databases!
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jonathan Wareham" <JonathanW@.CooperParry.com> wrote in message
> news:OHdeeaBYEHA.644@.tk2msftngp13.phx.gbl...
state.[vbcol=seagreen]
and
>
|||I just noticed a typo in my last post. I wrote:
" Next restart, file weren't open by other process, SQL Server turned off the autoclose option."
Should have been:
"...SQL Server turned off the suspect status."
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jonathan Wareham" <JonathanW@.CooperParry.com> wrote in message
news:%23R%23QHwBYEHA.2016@.TK2MSFTNGP11.phx.gbl...
> Many thanks Tibor!
> Jonathan
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23U0H0dBYEHA.3156@.TK2MSFTNGP12.phx.gbl...
> have autoclose, some other
> have it opened, SQL server
> weren't open by other process,
> state.
> and
>

Repeated 'Starting up database' messages

Hi,
Our network monitoring software reported the following message :
The database "MSCRM" in the instance "MSSQLSERVER" is in a critical state.
The state is: Suspect
After looking at SQL Enterprise Manager, the database appears to be fine and
I can browse tables, views, stored procedures and query the database as
normal. However when I looked at the SQL Server Error Log I found many
messages reporting 'Starting up database 'MSCRM'.
Any ideas?
Jonathan
Here is a sample log from this morning :
2004-07-02 09:29:53.76 spid89 Starting up database 'MSCRM'.
2004-07-02 09:29:54.48 spid89 Starting up database 'MSCRM'.
2004-07-02 09:29:59.17 spid89 Starting up database 'MSCRM'.
2004-07-02 09:29:59.43 spid89 Starting up database 'MSCRM'.
2004-07-02 09:29:59.67 spid89 Starting up database 'MSCRM'.
2004-07-02 09:29:59.93 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:00.24 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:00.48 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:00.82 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:01.06 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:01.46 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:01.74 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:02.03 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:02.32 spid89 Starting up database 'MSCRM'.
2004-07-02 09:32:19.81 spid89 Starting up database 'MSCRM'.
2004-07-02 09:45:00.48 spid72 Starting up database 'MSCRM'.
2004-07-02 09:45:01.06 spid72 Starting up database 'MSCRM'.
2004-07-02 10:00:00.23 spid91 Starting up database 'MSCRM'.
2004-07-02 10:00:00.65 spid91 Starting up database 'MSCRM'.
2004-07-02 10:00:00.89 spid101 Starting up database 'MSCRM'.
2004-07-02 10:00:01.60 spid101 Starting up database 'MSCRM'.
2004-07-02 10:00:06.99 spid91 Starting up database 'MSCRM'.Turn off autoclose for the database. What probably happened is that you have
autoclose, some other
process (like anti-virus) got hold of the file while SQL Server didn't have
it opened, SQL server
tried to access the file, ouch!, suspect database. Next restart, file weren'
t open by other process,
SQL Server turned off the autoclose option.
Autoclose is not designed for production databases!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jonathan Wareham" <JonathanW@.CooperParry.com> wrote in message
news:OHdeeaBYEHA.644@.tk2msftngp13.phx.gbl...
> Hi,
> Our network monitoring software reported the following message :
> The database "MSCRM" in the instance "MSSQLSERVER" is in a critical state.
> The state is: Suspect
> After looking at SQL Enterprise Manager, the database appears to be fine a
nd
> I can browse tables, views, stored procedures and query the database as
> normal. However when I looked at the SQL Server Error Log I found many
> messages reporting 'Starting up database 'MSCRM'.
> Any ideas?
> Jonathan
> Here is a sample log from this morning :
>
> 2004-07-02 09:29:53.76 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:29:54.48 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:29:59.17 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:29:59.43 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:29:59.67 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:29:59.93 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:00.24 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:00.48 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:00.82 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:01.06 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:01.46 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:01.74 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:02.03 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:02.32 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:32:19.81 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:45:00.48 spid72 Starting up database 'MSCRM'.
> 2004-07-02 09:45:01.06 spid72 Starting up database 'MSCRM'.
> 2004-07-02 10:00:00.23 spid91 Starting up database 'MSCRM'.
> 2004-07-02 10:00:00.65 spid91 Starting up database 'MSCRM'.
> 2004-07-02 10:00:00.89 spid101 Starting up database 'MSCRM'.
> 2004-07-02 10:00:01.60 spid101 Starting up database 'MSCRM'.
> 2004-07-02 10:00:06.99 spid91 Starting up database 'MSCRM'.
>|||Many thanks Tibor!
Jonathan
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23U0H0dBYEHA.3156@.TK2MSFTNGP12.phx.gbl...
> Turn off autoclose for the database. What probably happened is that you
have autoclose, some other
> process (like anti-virus) got hold of the file while SQL Server didn't
have it opened, SQL server
> tried to access the file, ouch!, suspect database. Next restart, file
weren't open by other process,
> SQL Server turned off the autoclose option.
> Autoclose is not designed for production databases!
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jonathan Wareham" <JonathanW@.CooperParry.com> wrote in message
> news:OHdeeaBYEHA.644@.tk2msftngp13.phx.gbl...
state.[vbcol=seagreen]
and[vbcol=seagreen]
>|||I just noticed a typo in my last post. I wrote:
" Next restart, file weren't open by other process, SQL Server turned off th
e autoclose option."
Should have been:
"...SQL Server turned off the suspect status."
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jonathan Wareham" <JonathanW@.CooperParry.com> wrote in message
news:%23R%23QHwBYEHA.2016@.TK2MSFTNGP11.phx.gbl...
> Many thanks Tibor!
> Jonathan
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:%23U0H0dBYEHA.3156@.TK2MSFTNGP12.phx.gbl...
> have autoclose, some other
> have it opened, SQL server
> weren't open by other process,
> state.
> and
>

Repeated 'Starting up database' messages

Hi,
Our network monitoring software reported the following message :
The database "MSCRM" in the instance "MSSQLSERVER" is in a critical state.
The state is: Suspect
After looking at SQL Enterprise Manager, the database appears to be fine and
I can browse tables, views, stored procedures and query the database as
normal. However when I looked at the SQL Server Error Log I found many
messages reporting 'Starting up database 'MSCRM'.
Any ideas?
Jonathan
Here is a sample log from this morning :
2004-07-02 09:29:53.76 spid89 Starting up database 'MSCRM'.
2004-07-02 09:29:54.48 spid89 Starting up database 'MSCRM'.
2004-07-02 09:29:59.17 spid89 Starting up database 'MSCRM'.
2004-07-02 09:29:59.43 spid89 Starting up database 'MSCRM'.
2004-07-02 09:29:59.67 spid89 Starting up database 'MSCRM'.
2004-07-02 09:29:59.93 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:00.24 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:00.48 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:00.82 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:01.06 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:01.46 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:01.74 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:02.03 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:02.32 spid89 Starting up database 'MSCRM'.
2004-07-02 09:32:19.81 spid89 Starting up database 'MSCRM'.
2004-07-02 09:45:00.48 spid72 Starting up database 'MSCRM'.
2004-07-02 09:45:01.06 spid72 Starting up database 'MSCRM'.
2004-07-02 10:00:00.23 spid91 Starting up database 'MSCRM'.
2004-07-02 10:00:00.65 spid91 Starting up database 'MSCRM'.
2004-07-02 10:00:00.89 spid101 Starting up database 'MSCRM'.
2004-07-02 10:00:01.60 spid101 Starting up database 'MSCRM'.
2004-07-02 10:00:06.99 spid91 Starting up database 'MSCRM'.Turn off autoclose for the database. What probably happened is that you have autoclose, some other
process (like anti-virus) got hold of the file while SQL Server didn't have it opened, SQL server
tried to access the file, ouch!, suspect database. Next restart, file weren't open by other process,
SQL Server turned off the autoclose option.
Autoclose is not designed for production databases!
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jonathan Wareham" <JonathanW@.CooperParry.com> wrote in message
news:OHdeeaBYEHA.644@.tk2msftngp13.phx.gbl...
> Hi,
> Our network monitoring software reported the following message :
> The database "MSCRM" in the instance "MSSQLSERVER" is in a critical state.
> The state is: Suspect
> After looking at SQL Enterprise Manager, the database appears to be fine and
> I can browse tables, views, stored procedures and query the database as
> normal. However when I looked at the SQL Server Error Log I found many
> messages reporting 'Starting up database 'MSCRM'.
> Any ideas?
> Jonathan
> Here is a sample log from this morning :
>
> 2004-07-02 09:29:53.76 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:29:54.48 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:29:59.17 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:29:59.43 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:29:59.67 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:29:59.93 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:00.24 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:00.48 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:00.82 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:01.06 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:01.46 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:01.74 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:02.03 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:02.32 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:32:19.81 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:45:00.48 spid72 Starting up database 'MSCRM'.
> 2004-07-02 09:45:01.06 spid72 Starting up database 'MSCRM'.
> 2004-07-02 10:00:00.23 spid91 Starting up database 'MSCRM'.
> 2004-07-02 10:00:00.65 spid91 Starting up database 'MSCRM'.
> 2004-07-02 10:00:00.89 spid101 Starting up database 'MSCRM'.
> 2004-07-02 10:00:01.60 spid101 Starting up database 'MSCRM'.
> 2004-07-02 10:00:06.99 spid91 Starting up database 'MSCRM'.
>|||Many thanks Tibor!
Jonathan
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23U0H0dBYEHA.3156@.TK2MSFTNGP12.phx.gbl...
> Turn off autoclose for the database. What probably happened is that you
have autoclose, some other
> process (like anti-virus) got hold of the file while SQL Server didn't
have it opened, SQL server
> tried to access the file, ouch!, suspect database. Next restart, file
weren't open by other process,
> SQL Server turned off the autoclose option.
> Autoclose is not designed for production databases!
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jonathan Wareham" <JonathanW@.CooperParry.com> wrote in message
> news:OHdeeaBYEHA.644@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > Our network monitoring software reported the following message :
> >
> > The database "MSCRM" in the instance "MSSQLSERVER" is in a critical
state.
> > The state is: Suspect
> >
> > After looking at SQL Enterprise Manager, the database appears to be fine
and
> > I can browse tables, views, stored procedures and query the database as
> > normal. However when I looked at the SQL Server Error Log I found many
> > messages reporting 'Starting up database 'MSCRM'.
> >
> > Any ideas?
> >
> > Jonathan
> >
> > Here is a sample log from this morning :
> >
> >
> > 2004-07-02 09:29:53.76 spid89 Starting up database 'MSCRM'.
> > 2004-07-02 09:29:54.48 spid89 Starting up database 'MSCRM'.
> > 2004-07-02 09:29:59.17 spid89 Starting up database 'MSCRM'.
> > 2004-07-02 09:29:59.43 spid89 Starting up database 'MSCRM'.
> > 2004-07-02 09:29:59.67 spid89 Starting up database 'MSCRM'.
> > 2004-07-02 09:29:59.93 spid89 Starting up database 'MSCRM'.
> > 2004-07-02 09:30:00.24 spid89 Starting up database 'MSCRM'.
> > 2004-07-02 09:30:00.48 spid89 Starting up database 'MSCRM'.
> > 2004-07-02 09:30:00.82 spid89 Starting up database 'MSCRM'.
> > 2004-07-02 09:30:01.06 spid89 Starting up database 'MSCRM'.
> > 2004-07-02 09:30:01.46 spid89 Starting up database 'MSCRM'.
> > 2004-07-02 09:30:01.74 spid89 Starting up database 'MSCRM'.
> > 2004-07-02 09:30:02.03 spid89 Starting up database 'MSCRM'.
> > 2004-07-02 09:30:02.32 spid89 Starting up database 'MSCRM'.
> > 2004-07-02 09:32:19.81 spid89 Starting up database 'MSCRM'.
> > 2004-07-02 09:45:00.48 spid72 Starting up database 'MSCRM'.
> > 2004-07-02 09:45:01.06 spid72 Starting up database 'MSCRM'.
> > 2004-07-02 10:00:00.23 spid91 Starting up database 'MSCRM'.
> > 2004-07-02 10:00:00.65 spid91 Starting up database 'MSCRM'.
> > 2004-07-02 10:00:00.89 spid101 Starting up database 'MSCRM'.
> > 2004-07-02 10:00:01.60 spid101 Starting up database 'MSCRM'.
> > 2004-07-02 10:00:06.99 spid91 Starting up database 'MSCRM'.
> >
> >
>|||I just noticed a typo in my last post. I wrote:
" Next restart, file weren't open by other process, SQL Server turned off the autoclose option."
Should have been:
"...SQL Server turned off the suspect status."
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jonathan Wareham" <JonathanW@.CooperParry.com> wrote in message
news:%23R%23QHwBYEHA.2016@.TK2MSFTNGP11.phx.gbl...
> Many thanks Tibor!
> Jonathan
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23U0H0dBYEHA.3156@.TK2MSFTNGP12.phx.gbl...
> > Turn off autoclose for the database. What probably happened is that you
> have autoclose, some other
> > process (like anti-virus) got hold of the file while SQL Server didn't
> have it opened, SQL server
> > tried to access the file, ouch!, suspect database. Next restart, file
> weren't open by other process,
> > SQL Server turned off the autoclose option.
> >
> > Autoclose is not designed for production databases!
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Jonathan Wareham" <JonathanW@.CooperParry.com> wrote in message
> > news:OHdeeaBYEHA.644@.tk2msftngp13.phx.gbl...
> > > Hi,
> > >
> > > Our network monitoring software reported the following message :
> > >
> > > The database "MSCRM" in the instance "MSSQLSERVER" is in a critical
> state.
> > > The state is: Suspect
> > >
> > > After looking at SQL Enterprise Manager, the database appears to be fine
> and
> > > I can browse tables, views, stored procedures and query the database as
> > > normal. However when I looked at the SQL Server Error Log I found many
> > > messages reporting 'Starting up database 'MSCRM'.
> > >
> > > Any ideas?
> > >
> > > Jonathan
> > >
> > > Here is a sample log from this morning :
> > >
> > >
> > > 2004-07-02 09:29:53.76 spid89 Starting up database 'MSCRM'.
> > > 2004-07-02 09:29:54.48 spid89 Starting up database 'MSCRM'.
> > > 2004-07-02 09:29:59.17 spid89 Starting up database 'MSCRM'.
> > > 2004-07-02 09:29:59.43 spid89 Starting up database 'MSCRM'.
> > > 2004-07-02 09:29:59.67 spid89 Starting up database 'MSCRM'.
> > > 2004-07-02 09:29:59.93 spid89 Starting up database 'MSCRM'.
> > > 2004-07-02 09:30:00.24 spid89 Starting up database 'MSCRM'.
> > > 2004-07-02 09:30:00.48 spid89 Starting up database 'MSCRM'.
> > > 2004-07-02 09:30:00.82 spid89 Starting up database 'MSCRM'.
> > > 2004-07-02 09:30:01.06 spid89 Starting up database 'MSCRM'.
> > > 2004-07-02 09:30:01.46 spid89 Starting up database 'MSCRM'.
> > > 2004-07-02 09:30:01.74 spid89 Starting up database 'MSCRM'.
> > > 2004-07-02 09:30:02.03 spid89 Starting up database 'MSCRM'.
> > > 2004-07-02 09:30:02.32 spid89 Starting up database 'MSCRM'.
> > > 2004-07-02 09:32:19.81 spid89 Starting up database 'MSCRM'.
> > > 2004-07-02 09:45:00.48 spid72 Starting up database 'MSCRM'.
> > > 2004-07-02 09:45:01.06 spid72 Starting up database 'MSCRM'.
> > > 2004-07-02 10:00:00.23 spid91 Starting up database 'MSCRM'.
> > > 2004-07-02 10:00:00.65 spid91 Starting up database 'MSCRM'.
> > > 2004-07-02 10:00:00.89 spid101 Starting up database 'MSCRM'.
> > > 2004-07-02 10:00:01.60 spid101 Starting up database 'MSCRM'.
> > > 2004-07-02 10:00:06.99 spid91 Starting up database 'MSCRM'.
> > >
> > >
> >
> >
>

Wednesday, March 7, 2012

Repair a Sql Server

Hi,
I have an instance of Sql Server 2000 on a Windows Server 2000
The server has 2 mirror hard disk
On disk failed and we replace it by a new one
Since than, some DTS does not work because the connection to another
database on the same server failed
Also the Console Entreprise Manager can not connect to all database
If a create a new registration from a remote computer the registration work
and I can see all the database
It seem's that some files are corrupted on the server
Is there a way to do a repair on SqlServer 2000?
If yes How can I do that?
Or what should I do to resolve the problem?
Thanks in advance?firstly to be sure if SQL server databases are corrupted, you can check
by doing DBCC CheckDB on all the databases|||It sounds like your redundant disk array isn't. Find and fix any disk
corruption issues, then you can restore the SQL Server. I have found that
fixing databases in place with underlying file system corruption to have
very poor success rates.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"GC" <GC@.discussions.microsoft.com> wrote in message
news:B7E33597-020E-49BB-B91A-3D181A020FF5@.microsoft.com...
> Hi,
> I have an instance of Sql Server 2000 on a Windows Server 2000
> The server has 2 mirror hard disk
> On disk failed and we replace it by a new one
> Since than, some DTS does not work because the connection to another
> database on the same server failed
> Also the Console Entreprise Manager can not connect to all database
> If a create a new registration from a remote computer the registration
> work
> and I can see all the database
> It seem's that some files are corrupted on the server
> Is there a way to do a repair on SqlServer 2000?
> If yes How can I do that?
> Or what should I do to resolve the problem?
> Thanks in advance?

Repair a Sql Server

Hi,
I have an instance of Sql Server 2000 on a Windows Server 2000
The server has 2 mirror hard disk
On disk failed and we replace it by a new one
Since than, some DTS does not work because the connection to another
database on the same server failed
Also the Console Entreprise Manager can not connect to all database
If a create a new registration from a remote computer the registration work
and I can see all the database
It seem's that some files are corrupted on the server
Is there a way to do a repair on SqlServer 2000?
If yes How can I do that?
Or what should I do to resolve the problem?
Thanks in advance?
firstly to be sure if SQL server databases are corrupted, you can check
by doing DBCC CheckDB on all the databases
|||It sounds like your redundant disk array isn't. Find and fix any disk
corruption issues, then you can restore the SQL Server. I have found that
fixing databases in place with underlying file system corruption to have
very poor success rates.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"GC" <GC@.discussions.microsoft.com> wrote in message
news:B7E33597-020E-49BB-B91A-3D181A020FF5@.microsoft.com...
> Hi,
> I have an instance of Sql Server 2000 on a Windows Server 2000
> The server has 2 mirror hard disk
> On disk failed and we replace it by a new one
> Since than, some DTS does not work because the connection to another
> database on the same server failed
> Also the Console Entreprise Manager can not connect to all database
> If a create a new registration from a remote computer the registration
> work
> and I can see all the database
> It seem's that some files are corrupted on the server
> Is there a way to do a repair on SqlServer 2000?
> If yes How can I do that?
> Or what should I do to resolve the problem?
> Thanks in advance?

Repair a Sql Server

Hi,
I have an instance of Sql Server 2000 on a Windows Server 2000
The server has 2 mirror hard disk
On disk failed and we replace it by a new one
Since than, some DTS does not work because the connection to another
database on the same server failed
Also the Console Entreprise Manager can not connect to all database
If a create a new registration from a remote computer the registration work
and I can see all the database
It seem's that some files are corrupted on the server
Is there a way to do a repair on SqlServer 2000?
If yes How can I do that?
Or what should I do to resolve the problem?
Thanks in advance?firstly to be sure if SQL server databases are corrupted, you can check
by doing DBCC CheckDB on all the databases|||It sounds like your redundant disk array isn't. Find and fix any disk
corruption issues, then you can restore the SQL Server. I have found that
fixing databases in place with underlying file system corruption to have
very poor success rates.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"GC" <GC@.discussions.microsoft.com> wrote in message
news:B7E33597-020E-49BB-B91A-3D181A020FF5@.microsoft.com...
> Hi,
> I have an instance of Sql Server 2000 on a Windows Server 2000
> The server has 2 mirror hard disk
> On disk failed and we replace it by a new one
> Since than, some DTS does not work because the connection to another
> database on the same server failed
> Also the Console Entreprise Manager can not connect to all database
> If a create a new registration from a remote computer the registration
> work
> and I can see all the database
> It seem's that some files are corrupted on the server
> Is there a way to do a repair on SqlServer 2000?
> If yes How can I do that?
> Or what should I do to resolve the problem?
> Thanks in advance?

Rep service 2005 with 2000 db

Hi,
Does anyone know of what problems will I encounter If I run a dedicated SS
2005 instance with DBs replicated from a 2000 production server
Any URLs / books /advice is much apprciated
RegardsHi,
Does anyone know of what problems will I encounter If I run a dedicated SS
2005 instance and reporting services with DBs replicated from a 2000
production server
Any URLs / books /advice is much apprciated
Regards
"DB King" wrote:
> Hi,
> Does anyone know of what problems will I encounter If I run a dedicated SS
> 2005 instance with DBs replicated from a 2000 production server
> Any URLs / books /advice is much apprciated
> Regards

Monday, February 20, 2012

Rendering problems

Hi,
In my footer, textboxes do not appear where I place them when viewing on the
intranet when I hide other things. For instance if I have two textboxes
beside each other but only show the left one on the last page, for all pages
preceding this page, the right text box is aligned over the top of the left
box. And the report looks unprofessional. Any ideas on how I can get the
box to stay put?
thanks
MattTry experimenting hosting your text controls within a Rectangle Control with
CanGrow, CanShrink set to false.
--
Peter Blackburn
Windows Server Systems - SQL Server MVP
Hitchhiker's Guide to SQL Server Reporting Services
http://www.sqlreportingservices.net
"Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
news:upSqtNJyEHA.1984@.TK2MSFTNGP14.phx.gbl...
> Hi,
> In my footer, textboxes do not appear where I place them when viewing on
> the
> intranet when I hide other things. For instance if I have two textboxes
> beside each other but only show the left one on the last page, for all
> pages
> preceding this page, the right text box is aligned over the top of the
> left
> box. And the report looks unprofessional. Any ideas on how I can get the
> box to stay put?
>
> thanks
> Matt
>