Showing posts with label error. Show all posts
Showing posts with label error. Show all posts

Wednesday, March 28, 2012

Replacing a cluster node: SQL silent install setup installshield error

Can anyone tell me how to get around this? Installing SQL- no issues, just when applying the sp

I have tried numerous things, including rebuilding the 2nd node again

Trying to install a service pack on SQL 2000 node, and Install shield keeps returning this error, .

[InstallShield Silent]
Version=v5.00.000
File=Log File
[ResponseResult]
ResultCode=-8

I have reset temp directories on both nodes,

I have made user permissions and profiles exist

I have copied install files to both local and remote locations

I know the response – Installshield cannot find the silent response file, but SQL setup creates this on its own.

Short of bringing this production system down and re-installing on both nodes ( I did test this on a non-prod box by the way, no issues), I am at a loss

Well

After 2 weeks of searching, finally!

It turns out, a couple of old log files and a registry key were the culprits..

I found this article this am, and it was the saviour

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=66737

Specific to my case were:

all mif and logs EVERYWHERE on the replaced node and:

\SOFTWARE\Microsoft\Microsoft SQL Server\80\Need_SP_Key - delete this key (backup 1st of course) on the new node

SP3a went on like a dream, and I can now finally move onto SP4 testing.

Replacing "Reporting Services Error" page with custom one?

Hi All!!
Looked everywhere... I think ;)
Is there a way to replace/override the "Reporting Services Error" page.
Tried with customErrors on in web.config as well as a onError method in
a httpModule. None of these seems to fetch the errors from reporting
services. Is the error handled internally, without raising i error to
the IIS?
I would like to be able to parse the error content, and in some cases
replace it with a more userfriendly version.
Is there a way?
Regards
Jonas MontonenThis is not a supported scenario. We did not create any mechanism for doing
this. All errors are handled internally and we almost always return success
from the http call.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jonas Montonen" <jonas.montonen@.ascade.se> wrote in message
news:u5zl5PY%23EHA.4028@.TK2MSFTNGP15.phx.gbl...
> Hi All!!
> Looked everywhere... I think ;)
> Is there a way to replace/override the "Reporting Services Error" page.
> Tried with customErrors on in web.config as well as a onError method in a
> httpModule. None of these seems to fetch the errors from reporting
> services. Is the error handled internally, without raising i error to the
> IIS?
> I would like to be able to parse the error content, and in some cases
> replace it with a more userfriendly version.
> Is there a way?
> Regards
> Jonas Montonen

Monday, March 26, 2012

Replace syntax error

Hello

In my database table I have replaced <h3> Some Text </h3> with <h2> Some Text </h2> in the Description column as below:

UPDATE CAT_Products

SET Description = replace (Description, '</h3>', '</h2>')
WHERE Description <> '</h2>'

UPDATE CAT_Products
SET Description = replace (Description, '<h3>', '<h2>')
WHERE Description <> '<h2>'

However, when I try the same replace syntax code with the DescriptionHTML column in the same table, it does not work, e.g.

UPDATE CAT_Products
SET DescriptionHTML = replace (DescriptionHTML, ' <h3> ', '<h2>')
WHERE Description <> '<h2>'

What do I need to adjust?

Thanks


can you explain what you mean by not working? also pls provide some sample data you have that you are expecting the REPLACE to happen on.

|||

Hello ndinaker

When I put in this code:

UPDATE CAT_Products
SET DescriptionHTML = replace (DescriptionHTML, ' <h3> ', '<h2>')
WHERE Description <> '<h2>'

I dose not execute and I get this error message instead:

[Error] Script lines: 1-5 --------
Argument data type ntext is invalid for argument 1 of replace function.

[Executed: 12/04/07 19:17:21 BST ] [Execution: 0/ms]

As in the text Description column, I am trying to update the <h3> and </h3> to <h2> and </h2> for example: <h3> Some Text </h3> to be replaced by <h2> Some Text </h2>

However, as it is written in html format in the DescriptionHTML database column it looks like:

<h3> Some Text </h 3> to be replaced by <h 2> Some Text </h2>

Thanks

|||

I think the pattern you are matching is only for <h3> and not </h3>. So you probably need to a double replace.

declare

@.str varchar(100)

set

@.str='<h3> Some Text </h3>'

--final value = <h 2> Some Text </h2>

select

@.str,replace(@.str,'<h3>','<h2>'),replace(replace(@.str,'<h3>','<h2>'),'</h3>','</h2>')

|||

Hi ndinakar

Thanks for the code, but I needed to change:

@.str varchar(100)

To


@.str ntext

H

owever this change cause the code to fail:

UPDATE CAT_Products


@.str ntext
set

@.str = '<h3> * </h3>'
--final value = <h 2> * </h2>

select

@.str, replace(@.str,'<h3>', '<h2>'), replace( replace(@.str,'<h3>', '<h2>'), '</h3>', '</h2>')

I got this error message:

[Error] Script lines: 1-13 --------
Line 4: Incorrect syntax near'@.str'.

More exceptions ... Must declare the variable'@.str'.

[Executed: 12/04/07 21:49:22 BST ] [Execution: 0/ms]

|||

Try if this works:

UPDATE

CAT_Products

set

DescriptionHTML=replace(replace(DescriptionHTML,'<h3>','<h2>'),'</h3>','</h2>')

WHERE

Description<>'<h2>'

|||

Before you try the update try a SELECT first to see what records are getting affected and how.

SELECT

NewDescriptionHTML=replace(replace(DescriptionHTML,'<h3>','<h2>'),'</h3>','</h2>'),

DescriptionHTML

FROM

CAT_Products

WHERE

Description<>'<h2>'

|||

As suggested, I tried this first:

SELECT

NewDescriptionHTML = replace( replace(DescriptionHTML,'<h3>', '<h2>'), '</h3>', '</h2>'),
DescriptionHTML

FROM

CAT_Products
WHERE

DescriptionHTML <> '<h2>'

But I got this error message:

[Error] Script lines: 1-11 --------
Argument data type ntext is invalid for argument 1 of replace function.

More exceptions ... The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

[Executed: 12/04/07 23:40:42 BST ] [Execution: 0/ms]

Then I tried this:

UPDATE

CAT_Products
set

DescriptionHTML = replace( replace(DescriptionHTML,'<h3>', '<h2>'), '</h3>', '</h2>')
WHERE

DescriptionHTML <> '<h2>'

But I got this error message:

[Error] Script lines: 1-11 --------
Argument data type ntext is invalid for argument 1 of replace function.

More exceptions ... The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

[Executed: 12/04/07 23:43:54 BST ] [Execution: 0/ms]

It looks like I will have to update this column manually, I had best roll up my sleeves.

Thanks


|||Ahhhh...its the NTEXT. Its going to be a pain to retrieve those values/update them through the query analyzer. Perhaps you can write a little tool..a VB tool with a form...that retrieves the values into a textbox and a button to update it..that will be easier..|||

Try this:

SELECT

NewDescriptionHTML

=replace(replace(convert(varchar(4000),DescriptionHTML),'<h3>','<h2>'),'</h3>','</h2>'),

DescriptionHTML

FROM

CAT_Products

WHERE

Description<>'<h2>'

|||

Hi ndinakar

Thanks again for your help and support

Yes you are right the Ntext is a pain, cos when I tried your latest code suggestion I got this error:

[Error] Script lines: 1-15 --------
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

[Executed: 13/04/07 09:17:43 BST ] [Execution: 0/ms]

It certainly dose not like the NTEXT.

A cup of coffee and rolling up of the sleeves coming up.

Thanks

sql

Wednesday, March 21, 2012

Replace Function

Dear All,
In my Item Master table, the item column has values like 'printing' in more
than 1000 rows. This was a data entry error. Now my user want all the
instances of 'printing' to be replace by 'printed'. Is there any ways to
update the entire rows in one go, or I will have to change the name from VB
ror by row, or using cusror in SQL.
Please help.
Thanks & Regards,
PramodYou didn't provide much details, but assuming you have a column named "x" fo
r which some rows
currently have the string "printing" and you want to change that string to "
printed", then it is
dead simple:
UPDATE tblname
SET x = 'printed'
WHERE x = 'printing'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<pramod@.luxoroffice.com> wrote in message news:%235smP8lHFHA.3500@.TK2MSFTNGP14.phx.gbl...[c
olor=darkred]
> Dear All,
> In my Item Master table, the item column has values like 'printing' in mor
e
> than 1000 rows. This was a data entry error. Now my user want all the
> instances of 'printing' to be replace by 'printed'. Is there any ways to
> update the entire rows in one go, or I will have to change the name from V
B
> ror by row, or using cusror in SQL.
> Please help.
> Thanks & Regards,
> Pramod
>[/color]|||If when you say "values like 'printing'", you mean that
'printing' is not the entire column value, you may need
something like this.
update ItemMaster set
item = replace(item, 'printing', 'printed')
where item like '%printing%'
Steve Kass
Drew University
pramod@.luxoroffice.com wrote:

>Dear All,
>In my Item Master table, the item column has values like 'printing' in more
>than 1000 rows. This was a data entry error. Now my user want all the
>instances of 'printing' to be replace by 'printed'. Is there any ways to
>update the entire rows in one go, or I will have to change the name from VB
>ror by row, or using cusror in SQL.
>Please help.
>Thanks & Regards,
>Pramod
>
>|||try this:-
update <tablename>
set <fieldname> = replace(<fieldname>, 'Printing', 'Printed')
from table
steve
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23d4ofZmHFHA.1392@.TK2MSFTNGP10.phx.gbl...
> If when you say "values like 'printing'", you mean that
> 'printing' is not the entire column value, you may need
> something like this.
> update ItemMaster set
> item = replace(item, 'printing', 'printed')
> where item like '%printing%'
> Steve Kass
> Drew University
> pramod@.luxoroffice.com wrote:
>|||Dear Steve,
My Problem is sloved by the query u provided:
UPDATE ItemMaster
SET ItemName = REPLACE(ItemName, 'Printing', 'Printed')
FROM ItemMaster
WHERE ItemName LIKE '%Printing%'
Thanks,
"Steven Richardson" <steve@.cheltenhamdirectory.com> wrote in message
news:Ocz9w2mHFHA.1860@.TK2MSFTNGP15.phx.gbl...
> try this:-
> update <tablename>
> set <fieldname> = replace(<fieldname>, 'Printing', 'Printed')
> from table
>
> steve
>
> "Steve Kass" <skass@.drew.edu> wrote in message
> news:%23d4ofZmHFHA.1392@.TK2MSFTNGP10.phx.gbl...
>

replace command

can the command replace use variables ie
replace(@.Name,'.com','.com;')
im getting an error with this and i was just wondering if it was just me
JimOriginally posted by JDionne
can the command replace use variables ie

replace(@.Name,'.com','.com;')

im getting an error with this and i was just wondering if it was just me
Jim

figured it out i needed a select in my code
select replace(@.Name,'.com','.com;')
Jim

Tuesday, March 20, 2012

Repl Mgr reporting error

Win 2k & SQL 2k
I have trans & merge replication between several remote
sites and my main office. On a couple of the Replication
Managers, an error is being reported on a publication.
When I expand the dist agent & the publication folder to
the actual publication, there is no error. I have
restarted the remote server and still the error exists,
even after I refresh the Replication Manager in EM.
Any one have an idea?
Larry,
when I have seen these rogue errors reported before, it was because of
incorrect data in tempdb so restarting the sql server service on the
distributor removed both the erroneous table and the red icon. hilary
mentions that running sp_MSload_replication_status normally clears this
error as well.
HTH,
Paul Ibison
|||I have restarted the server where the publications exist
and they are their own distributors. I ran
sp_MSload_replication_status on both the publisher and
the subscriber and the errors were not cleared. Any
ideas?
|||Larry,
can you try restarting the sql server service on the server which has
replication manager.
If this still doesn't fix it, try using profiler after you refresh the
replication manager and see if you can track it down.
HTH,
Paul Ibison

Repl failure on <> in data field

We added data to a table with ‘<’ and ‘>’ symbols. The replication failed on
SQLServer with the error “Syntax error or access violation”. We replaced the
'<' and '>' with '(' and ')', which fixed the replication error. Can you not
use brackets in a table that is replicated?
This applies to the following characters: * ,< , > , ? , / , \ , % , | , : ,
" and '
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Is this a known bug?
Is there any fix available?
"Paul Ibison" wrote:

> This applies to the following characters: * ,< , > , ? , / , \ , % , | , : ,
> " and '
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||You could describe it as a limitation, a feature or a bug
There's no fix - apart from removing the relevant characters (see
http://support.microsoft.com/default.aspx?scid=kb;[LN];829746)
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Sorry I did not expained well.
The table name doesn't contain special character but a space. (Space is not
in the list of special characters). For example [Title Types]
Replication works fine. Adding new data, updating data gets replicated
successfully. Now, when you add data with any special characters in one of
the nvarchar data fields of replicated table ( i.e. [Title Types]).
Replication fails.
The microsoft article addressing the issue of article name with special
characters but I have issue with data when table name contains space(which is
not special character listed).
I can not stop users to not enter special characters in data. So I think I
have only 2 options:
1. Rename table to TitleTypes (i.e remove space), All special characters
will work in data. (Lot of work as table is getting referenced from many
places)
2. add instead of trigger on table to get rid of special characters. (Extra
load on DB for insert and update due to trigger).
Further suggestions will be helpful.
Thanks
"Paul Ibison" wrote:

> You could describe it as a limitation, a feature or a bug
> There's no fix - apart from removing the relevant characters (see
> http://support.microsoft.com/default.aspx?scid=kb;[LN];829746)
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>

Friday, March 9, 2012

Repeatable error, with script, with code

Hello

I have spent the last weeks trying to solve this problem but with no
luck at all, I have a piece of code that looks like this

declare@.bestnr int,
@.artnr varchar(25),
@.journalnrrow int,
@.bestlevant decimal,
@.vb_inpris money

set @.bestnr = 33434
set @.artnr = '1441'
set @.journalnrrow = 11
set @.bestlevant = 50
set @.vb_inpris = 10

insert into bpl (bestnr, artnr, jibpjournal, bestlevant, vb_inpris,
bestlevantextqty)
values (@.bestnr, @.artnr, @.journalnrrow, @.bestlevant, @.vb_inpris,
@.bestlevant)

This code works just fine in query analyzer, but in SQL server agent
it does not work it creates an error message about string or bnary
data would be truncated, Simon suggested this has to do with
ansi_warnings being OFF in query analyser and ON when SQL srver agent
makes its connection.

I have scripted my entire DB at
http://donald.fruitsalad.org/microsoft/entiredb.sql

That script creates all the deps and tables and triggers for our DB,
any help would be appreciated, I just don't know where to turn, we
have hired a consultant but that did not help either as he could not
solve the problem.

If anyone know a good sql consultant in sweden that might have a clue
about this, please let us know we are getting pretty desperate.

Regards

MattOn 18 Apr 2004 10:42:00 -0700, Matt wrote:

>Hello
>I have spent the last weeks trying to solve this problem but with no
>luck at all, I have a piece of code that looks like this
>
>declare@.bestnr int,
>@.artnr varchar(25),
>@.journalnrrow int,
>@.bestlevant decimal,
>@.vb_inpris money
>set @.bestnr = 33434
>set @.artnr = '1441'
>set @.journalnrrow = 11
>set @.bestlevant = 50
>set @.vb_inpris = 10
>
>insert into bpl (bestnr, artnr, jibpjournal, bestlevant, vb_inpris,
>bestlevantextqty)
>values (@.bestnr, @.artnr, @.journalnrrow, @.bestlevant, @.vb_inpris,
>@.bestlevant)
>This code works just fine in query analyzer, but in SQL server agent
>it does not work it creates an error message about string or bnary
>data would be truncated, Simon suggested this has to do with
>ansi_warnings being OFF in query analyser and ON when SQL srver agent
>makes its connection.

Hi Matt,

To begin with: Simon (Hayes?) is correct. The reason that you see this
different behaviour is due to the settings Simon mentioned. These
settings are provided for historic reasons.

Previous versions of SQL Server had the feature of trimming strings
that didn't fit the declared length of a variable or column without
notice. For SQL Server 7.0, Microsoft wanted to certify as compliant
with the SQL-92 standard. One of the things this standard proscribed
was that trying to store a string in a columns with a shorter length
is an error condition. In order to provide backwards compatibility,
Microsoft supplied option that allow users to select the amount of
ANSI compliancy. The behaviour on string truncation is governed by the
ANSI_WARNINGS setting.

Try the following in Query Analyzer to see what I mean:

drop table test
go
create table test (try# int, shorttext varchar(6))
go
set ansi_warnings off
insert into test select 1, convert ( varchar(30) ,
COALESCE (SUser_SName () , 'XX' ) )
go
set ansi_warnings on
insert into test select 2, convert ( varchar(30) ,
COALESCE (SUser_SName () , 'XX' ) )
go
select * from test
go

>I have scripted my entire DB at
>http://donald.fruitsalad.org/microsoft/entiredb.sql

Boy, am I glad I'm no longer on a dialup connection - almost 100MB!!
When I saw how long the download would take, I assumed you had lots of
inserts embedded in the script, but I now see that it's just the DDL.
You sure make some pretty complex databases... :-|

>That script creates all the deps and tables and triggers for our DB,
>any help would be appreciated, I just don't know where to turn, we
>have hired a consultant but that did not help either as he could not
>solve the problem.

I killed the osql process that executed your script after more than 30
minutes, as I've already found the cause (though it's absolutely
possible that there are more culprits).

This line is taken from the create table statement for bpl:
[perssign] [varchar] (6) COLLATE Finnish_Swedish_CI_AS NOT NULL ,

This column has a default, specified in the (old and proprietary)
Transact-SQL syntax:

create default DefDbLoginId as convert ( varchar(30) ,
COALESCE (SUser_SName () , 'XX' ) )
GO
(...)
EXEC sp_bindefault N'[dbo].[DefDbLoginId]', N'[bpl].[perssign]'
GO

The default results in a varchar(30) value. You attempt to store this
in a varchar(6) column. That's why the error gets raised if the ANSI
warnings option is set to on.

Some ways to get around this:
a) Change the default DefDbLoginId - replace "varchar(30)" with
"varchar(6)" in the convert function call. (ANSI standards does permit
truncation of strings in explicit conversions).
b) Make sure you always run your code with ANSI warnings set to off.
c) Change the maximum length of bpl.perssign to varchar(30).

(*) Note on ANSI (and other) settings: a default for these settings
can be set per database. This default can be overriden for each
connection. Many tools (like Query Analyzer) will do so - the settings
QA usses for each connection can be found (and changed) via menu
option Query / Currenc Connection Properties and the defaults for new
connection via Tools / Options / Connection Properties. Within the
connection, the setting can be flipped as often as wanted with the SET
command (like I did in the example above).

>If anyone know a good sql consultant in sweden that might have a clue
>about this, please let us know we are getting pretty desperate.

In Sweden? No, sorry. I only know SQL consultants in the Netherlands.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Wednesday, March 7, 2012

Repairing a Database

When Trying to Attatch a Database, i get the following message.
Error 9003: The LSN(2768:609:1) passed to log scan in database 'master DB' is invalid.
Is there a repair tool, or something you can do in enterprise manager to repair the database.
Hi,
You may get this error while attaching the Database if the log file of the
database is corrupted.
If you have a clean backup, please restore the Database from the backup.
This is preferred method of recovering the database, because at this point
in time we will not be able to determine the impact of corrupted Log file.
If no clean backup is available, try this workaround, this is not
recommended, as you may have corrupted data.
Move the log file (.ldf) to a different location.
execute the following SP
sp_attach_single_file_db
Attaches a database having only one data file to the current server.
Syntax
sp_attach_single_file_db [ @.dbname = ] 'dbname'
, [ @.physname = ] 'physical_name'
Arguments
[@.dbname =] 'dbname'
Is the name of the database to be attached to the server. dbname is
sysname, with a default of NULL.
[@.physname =] 'phsyical_name'
Is the physical name, including path, of the database file. physical_name
is nvarchar(260), with a default of NULL.
This should be able to attach your database to the Server.
After attaching the database, execute DBCC CHECKDB without a repair clause
to
determine the extent of the corruption. DBCC CHECKDB may recommend a repair
clause
to use. Then, execute DBCC CHECKDB with the appropriate repair clause to
repair the
corruption.
CAUTION: If you are unsure what effect DBCC CHECKDB with a repair clause
has on your data, contact your primary support provider before executing
this
statement.
If running DBCC CHECKDB with one of the repair clauses does not correct the
problem, contact your primary support provider.
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.

repair torn page?

I have inherited a poorly administered/maintained database that
contains the following error:

"I/O error (torn page) detected during read at offset 0x000018ee23e000
in file 'F:\Program Files\ISS\RealSecure SiteProtector\Site
Database\Data\RealSecureDB.mdf"

I do not have database backups and so therefore cannot restore from
them. I want to move the database to a new platform and somehow
repair the torn page. Is this possible?
>I have inherited a poorly administered/maintained database that
>contains the following error:
>"I/O error (torn page) detected during read at offset 0x000018ee23e000
>in file 'F:\Program Files\ISS\RealSecure SiteProtector\Site
>Database\Data\RealSecureDB.mdf"
>I do not have database backups and so therefore cannot restore from
>them. I want to move the database to a new platform and somehow
>repair the torn page. Is this possible?

If you want to get your data out of this damaged database, we can help
you. Have a look at www.sql-server-repair.com

The error message you got indicates that a data page (8kByte) has not
been written completely. So at this page will be some data loss.

Regards

Thilo Immel
The SQL Sevrer Druid www.sql-server-repair.com|||You might try DBCC CHECKDB, if you haven't already done so. The end of the
report will suggest the DBCC CHECKDB repair option needed to correct the
problem. As Thilo said, this will likely be REPAIR_ALLOW_DATA_LOSS due to
the torn page. See the Books Online for more information.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"bbbad_999" <bericks999@.yahoo.com> wrote in message
news:6c6096fb.0408201308.7e1a5645@.posting.google.c om...
> I have inherited a poorly administered/maintained database that
> contains the following error:
> "I/O error (torn page) detected during read at offset 0x000018ee23e000
> in file 'F:\Program Files\ISS\RealSecure SiteProtector\Site
> Database\Data\RealSecureDB.mdf"
> I do not have database backups and so therefore cannot restore from
> them. I want to move the database to a new platform and somehow
> repair the torn page. Is this possible?|||see
http://www.nigelrivett.net/RecoverCorruptDatabase.html

Nigel Rivett
www.nigelrivett.net

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Repair database that grew over 2 GB

Because of a configuration error by field engineer who didn't check the
error logs before leaving, an error logging table in an MSDE database has
made it grow to 2GB in a couple of days.
The result is that the database status became "suspect" (I had to connect
through enterprise manager to see that) and it can no longer be opened.
At the same time, I noticed that the $&*$##% has renamed the computer after
installing MSDE and initializing the database, despite my urging him never
to do so: as a result the backup schedule that's created along with the
database was no longer executed, the last backup dates from last April
instead of a few hours before it stopped working.
Needless to say that the customer is very happy.
Now I think I made an additional error by trying to detach and re-attach
the database: MSDE won't even re-attach it anymore, with an error "the
primary file group is full".
Is there a way to force MSDE to load it anyway so the extraneous data can
be deleted?
Can the file be attached to a full SQL server, the runaway table purged
there, and then re-attached to MSDE?
Sorry, it seems I clicked 'Reply' in stead of 'new post' by mistake.

Repair database that grew over 2 GB

Because of a configuration error by field engineer who didn't check the
error logs before leaving, an error logging table in an MSDE database has
made it grow to 2GB in a couple of days.
The result is that the database status became "suspect" (I had to connect
through enterprise manager to see that) and it can no longer be opened.
At the same time, I noticed that the $&*$##% has renamed the computer after
installing MSDE and initializing the database, despite my urging him never
to do so: as a result the backup schedule that's created along with the
database was no longer executed, the last backup dates from last April
instead of a few hours before it stopped working.
Needless to say that the customer is very happy.
Now I think I made an additional error by trying to detach and re-attach
the database: MSDE won't even re-attach it anymore, with an error "the
primary file group is full".
Is there a way to force MSDE to load it anyway so the extraneous data can
be deleted?
Can the file be attached to a full SQL server, the runaway table purged
there, and then re-attached to MSDE?
On Tue, 28 Dec 2004 12:14:46 +0100, Lucvdv <replace_name@.null.net> wrote:

> Because of a configuration error by field engineer who didn't check the
> error logs before leaving, an error logging table in an MSDE database has
> made it grow to 2GB in a couple of days.
I just tried to recreate the problem on a test machine: the .mdf file grew
to 1869 MB (1.82 GB), then it stopped with "PRIMARY file group full", but
the database remains accessible, only inserts fail.
In the problem case, the .mdf file was slightly over 2 GB (2.03GB or so, I
don't remember exactly.)
Is there an explanation for this?
|||On Tue, 28 Dec 2004 12:14:46 +0100, Lucvdv <replace_name@.null.net> wrote:

> Can the file be attached to a full SQL server, the runaway table purged
> there, and then re-attached to MSDE?
Relief all around: after copying and attaching the database to a full SQL
Server, shrinking it there and then moving it back to MSDE, everything is
up and running again.
|||hi,
"Lucvdv" <replace_name@.null.net> ha scritto nel messaggio
news:ivf2t0ln44sb9r9ll8ucnknidv4pg4ppad@.4ax.com
> ...
> Can the file be attached to a full SQL server, the runaway table
> purged there, and then re-attached to MSDE?
yes... you can get the mdf (+ ndf if present) + ldf database files set,
take them to your office, purge the "extra" data and shrink the database
data file(s) to a size allowed by MSDE..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||On Tue, 28 Dec 2004 17:12:37 +0100, "Andrea Montanari"
<andrea.sqlDMO@.virgilio.it> wrote:

> hi,
> "Lucvdv" <replace_name@.null.net> ha scritto nel messaggio
> news:ivf2t0ln44sb9r9ll8ucnknidv4pg4ppad@.4ax.com
> yes... you can get the mdf (+ ndf if present) + ldf database files set,
> take them to your office, purge the "extra" data and shrink the database
> data file(s) to a size allowed by MSDE..
Thanks. I had tried and found that it worked shortly before you replied.
I tried to repeat it on a test machine later, but couldn't make the .mdf
grow above 2GB: all inserts started failing when the final file grow step
had to be done. It actually stopped around 1850 MB with % growth steps, it
refused to go closer to 2G until I changed that to 5MB fixed increments,
and then it stopped at 2044 MB.
The database remained usable, I could detach and attach it, only inserts
failed.
Yet in the problem case it was 2.03GB or something like that (slightly
above 2GB anyway), and MSDE refused to use it any further.
Do you have an idea how that might have happened?
I also noticed when I changed the file growth settings from % to fixed
size, that the change didn't take effect until I had detached and
re-attached the database (rebooting or restarting the service would
probably have done it too, what I mean is that inserts didn't start working
again by just changing the setting).
Is that something known?
|||hi Lucvdv,
"Lucvdv" <replace_name@.null.net> ha scritto nel messaggio
news:94r4t0p6bvclndb2l8gjuhogmd9ho8hq00@.4ax.com
> ...
> I tried to repeat it on a test machine later, but couldn't make the
> .mdf grow above 2GB: all inserts started failing when the final file
> grow step had to be done. It actually stopped around 1850 MB with %
> growth steps, it refused to go closer to 2G until I changed that to
> 5MB fixed increments, and then it stopped at 2044 MB.
> The database remained usable, I could detach and attach it, only
> inserts failed.
> Yet in the problem case it was 2.03GB or something like that (slightly
> above 2GB anyway), and MSDE refused to use it any further.
> Do you have an idea how that might have happened?
>
> I also noticed when I changed the file growth settings from % to fixed
> size, that the change didn't take effect until I had detached and
> re-attached the database (rebooting or restarting the service would
> probably have done it too, what I mean is that inserts didn't start
> working again by just changing the setting).
> Is that something known?
AFAIK the Storage engine should throw an exception when the 2gb limit is
reached, but should not set the db in suspect mode... this is what I've been
always told, as I never pushed MSDE to that upper limit :D ...
and this is the very first time I hear about growth setting affect such a
behaviour.. will try investigating this...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Repair an SQL Server 2005 installation.

Hi,

Some part of my system registry seens corrupted, and I can not run the report services configuration tool, it gave me the following error:

0x80010002: RCP_E_CALL_CANCELED

1) Does a repair of the installation will register back the COM Servers?

2) After a repair does the report services encription key will be affected.

My main problem is to perform a backup of the report services encription key on a server that is not very stable.

3) Is there another way to backup the encription key without using the report services configuration tool?

Thanks

JF

Hi, found my answers... and it solve my problem.

Performing a Windows 2003 Server repair (not repair console) has fix my registry problem and was able to connect back with the report services configuration tool.

Also I found the following cmd line to backup the encryption key: C:\Program Files\Microsoft SQL Server\90\Tools\Binn\RSKeyMgmt.exe

Monday, February 20, 2012

Rendering without parameter values supplied give error

Hi

I hope someone can help me out here. I'm quite new to RS so perhaps this is just me not knowing something obvious.

I have a report with several parameters, most of which have values the user can select from a list, supplied by a query. Some of these lists are filtered depending on the value of other parameters. (Eg One list has all customers, one list has all orders. If I select a customer, the order list changes to only contain orders for the selected customer.) Some of the parameters are just float or date values that do not rely on any other parameters being selected first (eg OrderDate). Some parameters have default values and some do not.

When I first go into the report in Report Manager it is fine and does not automatically render - as not all parameters have values supplied.

The problem is that as soon as I select a parameter value from the list when that value is part of the query for another parameters values, the report renders automatically - without some of the other parameters being supplied yet - and consequently errors with the rsReportParameterTypeMismatch error. (Eg I select a customer and the report renders before I can enter the OrderDate parameter.)

How can I stop the report automatically rendering before I have entered/selected values for all the parameters?

Thanks, Phaedra

I am having a very similar issue with default values creating a large report that takes a long time to render. I would like to prevent the report from firing on load and for it to wait until the "view report" button is actually pressed. Any suggestions would be welcome.

Rendering without parameter values supplied give error

Hi

I hope someone can help me out here. I'm quite new to RS so perhaps this is just me not knowing something obvious.

I have a report with several parameters, most of which have values the user can select from a list, supplied by a query. Some of these lists are filtered depending on the value of other parameters. (Eg One list has all customers, one list has all orders. If I select a customer, the order list changes to only contain orders for the selected customer.) Some of the parameters are just float or date values that do not rely on any other parameters being selected first (eg OrderDate). Some parameters have default values and some do not.

When I first go into the report in Report Manager it is fine and does not automatically render - as not all parameters have values supplied.

The problem is that as soon as I select a parameter value from the list when that value is part of the query for another parameters values, the report renders automatically - without some of the other parameters being supplied yet - and consequently errors with the rsReportParameterTypeMismatch error. (Eg I select a customer and the report renders before I can enter the OrderDate parameter.)

How can I stop the report automatically rendering before I have entered/selected values for all the parameters?

Thanks, Phaedra

I am having a very similar issue with default values creating a large report that takes a long time to render. I would like to prevent the report from firing on load and for it to wait until the "view report" button is actually pressed. Any suggestions would be welcome.