3G ram for SQL server: running

Showing posts with label running. Show all posts

Sunday, March 25, 2012

A couple of easy questions

Hi everyone,

Running Windows 2003 x64 and Sql25k 64-bit edition

We just have a couple of questions related with our system:

-What is Sql Server VSS Writer service for?

-What sort of implications must we assume when Integration Services service is running as NT AUTHORITY\NetworkService

Any link or advice would be welcomed.

Thanks for your time,

SQL Writer Service
(http://msdn2.microsoft.com/en-us/library/ms175536.aspx)

Setting Up Windows Service Accounts
(http://msdn2.microsoft.com/en-us/library/309b9dac-0b3a-4617-85ef-c4519ce9d014.aspx)

For SSIS the implications are much the same as any service, just bear in mind what the SSIS service does, not a lot really.

|||Thank you|||Be sure to mark the answers to your questions as an answer to the thread.

Thursday, March 22, 2012

A connection could not be made to the report server

While attempting to deploy from within VS2005 running on 2003 server we get the following error:

TITLE: Microsoft Semantic Model Designer

A connection could not be made to the report server http://xxxxx:90/ReportServer.

Running sql2005 sp2 integrated w Sharepoint 2007

Thanks..

Can you navigate to the Report manager surface ? on http://xxxx:90/reports ? Is Sharepoint running in the same port as the reporting server ?

Jens K. Suessmeyer.

http.77www.sqlserver2005.de

|||

Hi - no attempting to get to /reports generates the following error: The operation is not supported on a report server that is configured to run in SharePoint integrated mode. (rsOperationNotSupportedSharePointMode)

Sharepoint is port 80 - rs is 90.

admin is the service account and anon access is disabled on the web side

Hope that answers the questions - we are new to using rs and visual studio

|||

Anonymous,

What sku are do you have for SQL RS 2005 SP2? Share Point Integrated mode is supported only for Enterprise, Developer, Standard, and Evaluation.

Mihaela

|||

Hi - we are using SQL2005 Standard & SP Enterprise. Just to provide more detail if it helps...

We did the SQL install - installed the additional services (report, analysis etc..) - then loaded SP2.. The install for the RS was not the default - just files only.

We recreated in IIS another website RSSITE using port 90

Downloaded and installed the Report add-in for SP2007

Went into Report Config went through the options for a native install

We were able to get to http://xxxx:90/reports

We then changed the installation to SP integrated

We went into SP admin and followed the setup steps including creating a doc library

We could get to http://xxxx:90/reportserver (however there was nothing there)

We then followed these directions to get a model created: http://www.sharepointblogs.com/helloitsliam/archive/2007/03/04/20410.aspx

All went well until the deploy option -

all service accounts are the admin account and all actions were executed on the 2003 server..

Thanks - hope this is helpful info....

|||

Anonymous,

It seems that you have the right sku for Reporting Services to be able to work in Share Point Integrated mode.

When you are working in Share Point integrated mode, the Report Manager feature is disabled, so the error you see when you try to connect to ../reports after switching to Share Point mode is expectable.

I would like to ask you to take a look int he Report Server log files and to see what errors you find there and to post them here.

Thank you,

Mihaela

|||

w3wp!library!1!04/02/2007-21:45:55:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.OperationNotSupportedSharePointModeException: The operation is not supported on a report server that is configured to run in SharePoint integrated mode., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.OperationNotSupportedSharePointModeException: The operation is not supported on a report server that is configured to run in SharePoint integrated mode.

Each time we run the deploy from the VS application we see this error in the <Path>C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\LogFiles\ReportServer__04_02_2007_21_28_51.log</Path>

No other RS logs seem to update and nothing in the event logs...

(Mon Apr 02 21:28:56 2007.205463218) : Received Event
(Mon Apr 02 21:28:56 2007.205463234) : ***************************************
(Mon Apr 02 21:28:56 2007.205463234) : Could not get pointer to binary resource for file:
(Mon Apr 02 21:28:56 2007.205463234) : \\?\C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_isapi.dll[AspNetMofResource](Mon Apr 02 21:28:56 2007.205463234) :
(Mon Apr 02 21:28:56 2007.205463234) : ***************************************

The above was in our wmiprov.log - dont know if it helps...

Thanks

|||I'm seeing the same error under the same set of circumstances. Have you been able to resolve the issue?sql

A connection could not be made to the report server

While attempting to deploy from within VS2005 running on 2003 server we get the following error:

TITLE: Microsoft Semantic Model Designer

A connection could not be made to the report server http://xxxxx:90/ReportServer.

Running sql2005 sp2 integrated w Sharepoint 2007

Thanks..

Can you navigate to the Report manager surface ? on http://xxxx:90/reports ? Is Sharepoint running in the same port as the reporting server ?

Jens K. Suessmeyer.

http.77www.sqlserver2005.de

|||

Sharepoint is port 80 - rs is 90.

admin is the service account and anon access is disabled on the web side

Hope that answers the questions - we are new to using rs and visual studio

|||

Anonymous,

What sku are do you have for SQL RS 2005 SP2? Share Point Integrated mode is supported only for Enterprise, Developer, Standard, and Evaluation.

Mihaela

|||

Hi - we are using SQL2005 Standard & SP Enterprise. Just to provide more detail if it helps...

We did the SQL install - installed the additional services (report, analysis etc..) - then loaded SP2.. The install for the RS was not the default - just files only.

We recreated in IIS another website RSSITE using port 90

Downloaded and installed the Report add-in for SP2007

Went into Report Config went through the options for a native install

We were able to get to http://xxxx:90/reports

We then changed the installation to SP integrated

We went into SP admin and followed the setup steps including creating a doc library

We could get to http://xxxx:90/reportserver (however there was nothing there)

We then followed these directions to get a model created: http://www.sharepointblogs.com/helloitsliam/archive/2007/03/04/20410.aspx

All went well until the deploy option -

all service accounts are the admin account and all actions were executed on the 2003 server..

Thanks - hope this is helpful info....

|||

Anonymous,

It seems that you have the right sku for Reporting Services to be able to work in Share Point Integrated mode.

I would like to ask you to take a look int he Report Server log files and to see what errors you find there and to post them here.

Thank you,

Mihaela

|||

No other RS logs seem to update and nothing in the event logs...

The above was in our wmiprov.log - dont know if it helps...

Thanks

|||I'm seeing the same error under the same set of circumstances. Have you been able to resolve the issue?

A connection could not be established...Cannot open user default database...

Hello,
I'm running SQL7 on a Win2K server. I was in the midst of
restoring a database when I lost connectivity to the
network and the job failed.
Now if I try to view the database within Enterprise
Manager, I get the following error:
---
A connection could not be established to <servername>.
Cannot open user default database '<ID>. Using master
database instead. Please varify SQL Server is running and
check your SQL Server registration properties (by right-
clicking on the <servername> node) and try again.
---
I checked and SQL is running and the registration
properties are correct.
Any help would be appreciated.Seems like the database is "lost", as you terminated the restore, and the
logins that your EM registration login is using has that database as the
default database. I suggest you login using ISQL.EXE and change default
database using sp_defaultdb.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jimbo" <anonymous@.discussions.microsoft.com> wrote in message
news:0c6201c3df7b$ee9a7fe0$a101280a@.phx.gbl...
> Hello,
> I'm running SQL7 on a Win2K server. I was in the midst of
> restoring a database when I lost connectivity to the
> network and the job failed.
> Now if I try to view the database within Enterprise
> Manager, I get the following error:
> ---
> A connection could not be established to <servername>.
> Cannot open user default database '<ID>. Using master
> database instead. Please varify SQL Server is running and
> check your SQL Server registration properties (by right-
> clicking on the <servername> node) and try again.
> ---
> I checked and SQL is running and the registration
> properties are correct.
> Any help would be appreciated.
>|||Thanks Tibor - I tried that as outlined in MS KB 196076,
but I still get a the same error.(?)
>--Original Message--
>Seems like the database is "lost", as you terminated the
restore, and the
>logins that your EM registration login is using has that
database as the
>default database. I suggest you login using ISQL.EXE and
change default
>database using sp_defaultdb.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Jimbo" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0c6201c3df7b$ee9a7fe0$a101280a@.phx.gbl...
>> Hello,
>> I'm running SQL7 on a Win2K server. I was in the midst
of
>> restoring a database when I lost connectivity to the
>> network and the job failed.
>> Now if I try to view the database within Enterprise
>> Manager, I get the following error:
>> ---
>> A connection could not be established to <servername>.
>> Cannot open user default database '<ID>. Using master
>> database instead. Please varify SQL Server is running
and
>> check your SQL Server registration properties (by right-
>> clicking on the <servername> node) and try again.
>> ---
>> I checked and SQL is running and the registration
>> properties are correct.
>> Any help would be appreciated.
>>
>
>.
>|||Tibor Karaszi wrote:
> Seems like the database is "lost", as you terminated the restore, and the
> logins that your EM registration login is using has that database as the
> default database. I suggest you login using ISQL.EXE and change default
> database using sp_defaultdb.
>
Thanks for the information. That seems to have fixed the lost db server
issue in the drop down. Still can't get the damn app server to
connect to the db server though. Will keep searching.|||> Thanks for the information. That seems to have fixed the lost db server
> issue in the drop down. Still can't get the damn app server to connect
> to the db server though. Will keep searching.
Everyone may ignore my last post.

A connection could not be established...Cannot open user default database...

quote:

> Hello,
> I'm running SQL7 on a Win2K server. I was in the midst of
> restoring a database when I lost connectivity to the
> network and the job failed.
> Now if I try to view the database within Enterprise
> Manager, I get the following error:
> ---
> A connection could not be established to <servername>.
> Cannot open user default database '<ID>. Using master
> database instead. Please varify SQL Server is running and
> check your SQL Server registration properties (by right-
> clicking on the <servername> node) and try again.
> ---
> I checked and SQL is running and the registration
> properties are correct.
> Any help would be appreciated.
>

|||Thanks Tibor - I tried that as outlined in MS KB 196076,
but I still get a the same error.(?)

quote:

>--Original Message--
>Seems like the database is "lost", as you terminated the

restore, and the

quote:

>logins that your EM registration login is using has that

database as the

quote:

>default database. I suggest you login using ISQL.EXE and

change default

quote:

>database using sp_defaultdb.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?

oi=djq&as_ugroup=microsoft.public.sqlserver

quote:

>
>"Jimbo" <anonymous@.discussions.microsoft.com> wrote in

message

quote:

>news:0c6201c3df7b$ee9a7fe0$a101280a@.phx.gbl...
of[QUOTE]
and[QUOTE]
>
>.
>

Tuesday, March 20, 2012

A Bug With Running Total Query?

Using SQL Server 2005
Query Result From 1 Table with 3 Cols:
Date Amount RunningTotal
6/1/2005 730.0000 730.0000
7/1/2005 415.0000 830.0000
8/1/2005 415.0000 1245.0000
9/1/2005 415.0000 1660.0000
10/1/2005 415.0000 2075.0000
11/1/2005 415.0000 2490.0000
12/1/2005 415.0000 2905.0000
1/1/2006 415.0000 3320.0000
2/1/2006 415.0000 3735.0000
3/1/2006 415.0000 4150.0000
4/1/2006 415.0000 4565.0000
5/1/2006 415.0000 4980.0000
Query:
SELECT Table1.AmountDate, Table1.Amount, SUM(Table1.Amount) AS
RunningTotal
FROM AmountTable AS Table1 INNER JOIN
AmountTable AS Table2 ON Table1.AmountDate >=
Table2.AmountDate
GROUP BY Table1.AmountDate, Table1.Amount
ORDER BY Table1.AmountDate
The problem is with the 7/1 date sum. It only added 100 and not 415
Does anybody see a problem I did or is this something I should post to MSDN
Product FeedBack?
The query was a bit more complex when I unioned 2 tables and the amount was
not coming out right and I broke it down very simply and found this to be a
n
issue.
On a side note relating to the union relation and doing a running total, I
only had 1 Amount in the other table for now and for some reason, that wasn'
t
summing with the rest of the set.
SELECT AmountTable1.AmountDate, SUM(AmountTable1.AmountAmount)
FROM (SELECT Table1Amount.AmountDate, Table1Amount.AmountAmount
FROM Table1Amount
INNER JOIN
Table1 ON Table1Amount.Table1ID = Table1.Table1ID
UNION
SELECT Table2Amount.AmountDate, Table2Amount.AmountAmount
FROM Table2Amount INNER JOIN
Table1 AS Table1_1 ON Table2Amount.Table2ID =
Table1_1.Table2ID
WHERE (Table2Amount.Table2ID = '1')) AS AmountTable1
CROSS JOIN
(SELECT Table1Amount.AmountDate, Table1Amount.AmountAmount
FROM Table1Amount INNER JOIN
Table1 ON Table1Amount.Table1ID = Table1.Table1ID
UNION
SELECT Table2Amount.AmountDate, Table2Amount.AmountAmount
FROM Table2Amount INNER JOIN
Table1 AS Table1_1 ON Table2Amount.Table2ID =
Table1_1.Table2ID
WHERE (Table2Amount.Table2ID = '1')) AS AmountTable2
WHERE (AmountTable1.AmountDate >= AmountTable2.AmountDate)
GROUP BY AmountTable1.AmountDate, AmountTable1.AmountAmount
ORDER BY AmountTable1.AmountDate
Produces:
Date Amount RunningTotal
6/1/2005 730.0000 730.0000
6/3/2005 10.0000 20.0000*****From Table1
7/1/2005 415.0000 1245.0000
8/1/2005 415.0000 1660.0000
9/1/2005 415.0000 2075.0000
10/1/2005 415.0000 2490.0000
11/1/2005 415.0000 2905.0000
12/1/2005 415.0000 3320.0000
1/1/2006 415.0000 3735.0000
2/1/2006 415.0000 4150.0000
3/1/2006 415.0000 4565.0000
4/1/2006 415.0000 4980.0000
5/1/2006 415.0000 5395.0000
6/1/2006 415.0000 5810.0000
So that 6/3 Amount from Table1 is excluded from summing with the rest of the
set.
I thought it would have been included with the Union.
Any input is welcome,
NathanHi
This should be posted to the beta newsgroups along with version number, DDL
and sample data so that the problem can be recreated.
http://communities.microsoft.com/ne...onalserver.tsql
John
"Nathan" <Nathan@.discussions.microsoft.com> wrote in message
news:C771A09A-69DA-49D0-B71C-5C541E15EC51@.microsoft.com...
> Using SQL Server 2005
> Query Result From 1 Table with 3 Cols:
> Date Amount RunningTotal
> 6/1/2005 730.0000 730.0000
> 7/1/2005 415.0000 830.0000
> 8/1/2005 415.0000 1245.0000
> 9/1/2005 415.0000 1660.0000
> 10/1/2005 415.0000 2075.0000
> 11/1/2005 415.0000 2490.0000
> 12/1/2005 415.0000 2905.0000
> 1/1/2006 415.0000 3320.0000
> 2/1/2006 415.0000 3735.0000
> 3/1/2006 415.0000 4150.0000
> 4/1/2006 415.0000 4565.0000
> 5/1/2006 415.0000 4980.0000
> Query:
> SELECT Table1.AmountDate, Table1.Amount, SUM(Table1.Amount) AS
> RunningTotal
> FROM AmountTable AS Table1 INNER JOIN
> AmountTable AS Table2 ON Table1.AmountDate >=
> Table2.AmountDate
> GROUP BY Table1.AmountDate, Table1.Amount
> ORDER BY Table1.AmountDate
> The problem is with the 7/1 date sum. It only added 100 and not 415
> Does anybody see a problem I did or is this something I should post to
> MSDN
> Product FeedBack?
> The query was a bit more complex when I unioned 2 tables and the amount
> was
> not coming out right and I broke it down very simply and found this to be
> an
> issue.
> On a side note relating to the union relation and doing a running total, I
> only had 1 Amount in the other table for now and for some reason, that
> wasn't
> summing with the rest of the set.
> SELECT AmountTable1.AmountDate, SUM(AmountTable1.AmountAmount)
> FROM (SELECT Table1Amount.AmountDate, Table1Amount.AmountAmount
> FROM Table1Amount
> INNER JOIN
> Table1 ON Table1Amount.Table1ID = Table1.Table1ID
> UNION
> SELECT Table2Amount.AmountDate, Table2Amount.AmountAmount
> FROM Table2Amount INNER JOIN
> Table1 AS Table1_1 ON Table2Amount.Table2ID =
> Table1_1.Table2ID
> WHERE (Table2Amount.Table2ID = '1')) AS AmountTable1
> CROSS JOIN
> (SELECT Table1Amount.AmountDate, Table1Amount.AmountAmount
> FROM Table1Amount INNER JOIN
> Table1 ON Table1Amount.Table1ID = Table1.Table1ID
> UNION
> SELECT Table2Amount.AmountDate,
> Table2Amount.AmountAmount
> FROM Table2Amount INNER JOIN
> Table1 AS Table1_1 ON Table2Amount.Table2ID =
> Table1_1.Table2ID
> WHERE (Table2Amount.Table2ID = '1')) AS AmountTable2
> WHERE (AmountTable1.AmountDate >= AmountTable2.AmountDate)
> GROUP BY AmountTable1.AmountDate, AmountTable1.AmountAmount
> ORDER BY AmountTable1.AmountDate
> Produces:
> Date Amount RunningTotal
> 6/1/2005 730.0000 730.0000
> 6/3/2005 10.0000 20.0000*****From Table1
> 7/1/2005 415.0000 1245.0000
> 8/1/2005 415.0000 1660.0000
> 9/1/2005 415.0000 2075.0000
> 10/1/2005 415.0000 2490.0000
> 11/1/2005 415.0000 2905.0000
> 12/1/2005 415.0000 3320.0000
> 1/1/2006 415.0000 3735.0000
> 2/1/2006 415.0000 4150.0000
> 3/1/2006 415.0000 4565.0000
> 4/1/2006 415.0000 4980.0000
> 5/1/2006 415.0000 5395.0000
> 6/1/2006 415.0000 5810.0000
> So that 6/3 Amount from Table1 is excluded from summing with the rest of
> the
> set.
> I thought it would have been included with the Union.
> Any input is welcome,
> Nathan|||
Nathan wrote:

>Using SQL Server 2005
>Query Result From 1 Table with 3 Cols:
>Date Amount RunningTotal
>6/1/2005 730.0000 730.0000
>7/1/2005 415.0000 830.0000
>8/1/2005 415.0000 1245.0000
>9/1/2005 415.0000 1660.0000
>10/1/2005 415.0000 2075.0000
>11/1/2005 415.0000 2490.0000
>12/1/2005 415.0000 2905.0000
>1/1/2006 415.0000 3320.0000
>2/1/2006 415.0000 3735.0000
>3/1/2006 415.0000 4150.0000
>4/1/2006 415.0000 4565.0000
>5/1/2006 415.0000 4980.0000
>Query:
>SELECT Table1.AmountDate, Table1.Amount, SUM(Table1.Amount) AS
>RunningTotal
>FROM AmountTable AS Table1 INNER JOIN
> AmountTable AS Table2 ON Table1.AmountDate >=
>Table2.AmountDate
>GROUP BY Table1.AmountDate, Table1.Amount
>ORDER BY Table1.AmountDate
>
Nathan,
The results are correct for this query. You are grouping by and summing
the same column: Table1.Amount. Table2 only determines how many times
each Table1 row is repeated, so SUM(Table1.Amount) is just a multiple of
Table1.Amount:
Table1.Amount*(number of rows in the table with earlier or the same date).
Note that your "running sums" are not that at all. They are multiples:
730*1 = 730
415*2 = 830
415*3 = 1245
415*4 = 1660
...
415*12 = 4980
If you want running sums, you will need SUM(Table2.Amount).
Steve Kass
Drew University

>The problem is with the 7/1 date sum. It only added 100 and not 415
>Does anybody see a problem I did or is this something I should post to MSDN
>Product FeedBack?
>The query was a bit more complex when I unioned 2 tables and the amount was
>not coming out right and I broke it down very simply and found this to be
an
>issue.
>On a side note relating to the union relation and doing a running total, I
>only had 1 Amount in the other table for now and for some reason, that wasn
't
>summing with the rest of the set.
>SELECT AmountTable1.AmountDate, SUM(AmountTable1.AmountAmount)
>FROM (SELECT Table1Amount.AmountDate, Table1Amount.AmountAmount
> FROM Table1Amount
> INNER JOIN
> Table1 ON Table1Amount.Table1ID = Table1.Table1ID
> UNION
> SELECT Table2Amount.AmountDate, Table2Amount.AmountAmount
> FROM Table2Amount INNER JOIN
> Table1 AS Table1_1 ON Table2Amount.Table2ID =
>Table1_1.Table2ID
> WHERE (Table2Amount.Table2ID = '1')) AS AmountTable1
> CROSS JOIN
> (SELECT Table1Amount.AmountDate, Table1Amount.AmountAmount
> FROM Table1Amount INNER JOIN
> Table1 ON Table1Amount.Table1ID = Table1.Table1ID
> UNION
> SELECT Table2Amount.AmountDate, Table2Amount.AmountAmou
nt
> FROM Table2Amount INNER JOIN
> Table1 AS Table1_1 ON Table2Amount.Table2ID =
>Table1_1.Table2ID
> WHERE (Table2Amount.Table2ID = '1')) AS AmountTable2
>WHERE (AmountTable1.AmountDate >= AmountTable2.AmountDate)
>GROUP BY AmountTable1.AmountDate, AmountTable1.AmountAmount
>ORDER BY AmountTable1.AmountDate
>Produces:
>Date Amount RunningTotal
>6/1/2005 730.0000 730.0000
>6/3/2005 10.0000 20.0000*****From Table1
>7/1/2005 415.0000 1245.0000
>8/1/2005 415.0000 1660.0000
>9/1/2005 415.0000 2075.0000
>10/1/2005 415.0000 2490.0000
>11/1/2005 415.0000 2905.0000
>12/1/2005 415.0000 3320.0000
>1/1/2006 415.0000 3735.0000
>2/1/2006 415.0000 4150.0000
>3/1/2006 415.0000 4565.0000
>4/1/2006 415.0000 4980.0000
>5/1/2006 415.0000 5395.0000
>6/1/2006 415.0000 5810.0000
>So that 6/3 Amount from Table1 is excluded from summing with the rest of th
e
>set.
>I thought it would have been included with the Union.
>Any input is welcome,
>Nathan
>|||AAAAHHHHHH!!!!
I can't believe I didn't catch that one. I was working on the original issue
for so long that ended up going blind.
Kudos for the good catch Steve
Nathan
"Steve Kass" wrote:

>
> Nathan wrote:
>
> Nathan,
> The results are correct for this query. You are grouping by and summing
> the same column: Table1.Amount. Table2 only determines how many times
> each Table1 row is repeated, so SUM(Table1.Amount) is just a multiple of
> Table1.Amount:
> Table1.Amount*(number of rows in the table with earlier or the same date).
> Note that your "running sums" are not that at all. They are multiples:
> 730*1 = 730
> 415*2 = 830
> 415*3 = 1245
> 415*4 = 1660
> ...
> 415*12 = 4980
>
> If you want running sums, you will need SUM(Table2.Amount).
> Steve Kass
> Drew University
>
>

A bug in SQL Server 2000?

Hi!
I'm running MS Windows 2000 SP4 + SQL Server 2000 SP3a.
I've got a SQL Server Assertion: File:
<p:\sql\ntdbms\storeng\drs\include\record.inl>, line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <=
MAXDATAROW'. Error: 3624, Severity: 20, State: 1. The
CHECKDB of every DB on that server came up clean (0
allocation and 0 consistency errors). Could this
effectively mean that I've discovered a bug in SQL Server?
Thanks,
Osk
As you alluded to, this typically indicates corruption in your database.
However, there is a potential for in-memory kinds of corruption events in
which the page in memory is corrupted, but the image on disk is not. That
is most often caused by hardware issues. I would recommend that you open a
case with Microsoft PSS (http://support.microsoft.com) after you have
verified that you have no hardware issues.
Ryan Stonecipher
Microsoft SQL Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Osk" <anonymous@.discussions.microsoft.com> wrote in message
news:243401c50d2a$2000fb90$a401280a@.phx.gbl...
> Hi!
> I'm running MS Windows 2000 SP4 + SQL Server 2000 SP3a.
> I've got a SQL Server Assertion: File:
> <p:\sql\ntdbms\storeng\drs\include\record.inl>, line=1447
> Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <=
> MAXDATAROW'. Error: 3624, Severity: 20, State: 1. The
> CHECKDB of every DB on that server came up clean (0
> allocation and 0 consistency errors). Could this
> effectively mean that I've discovered a bug in SQL Server?
> --
> Thanks,
> Osk
>

A bug in SQL Server 2000?

Hi!
I'm running MS Windows 2000 SP4 + SQL Server 2000 SP3a.
I've got a SQL Server Assertion: File:
< p:\sql\ntdbms\storeng\drs\include\record
.inl>, line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <=
MAXDATAROW'. Error: 3624, Severity: 20, State: 1. The
CHECKDB of every DB on that server came up clean (0
allocation and 0 consistency errors). Could this
effectively mean that I've discovered a bug in SQL Server?
Thanks,
OskAs you alluded to, this typically indicates corruption in your database.
However, there is a potential for in-memory kinds of corruption events in
which the page in memory is corrupted, but the image on disk is not. That
is most often caused by hardware issues. I would recommend that you open a
case with Microsoft PSS (http://support.microsoft.com) after you have
verified that you have no hardware issues.
Ryan Stonecipher
Microsoft SQL Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Osk" <anonymous@.discussions.microsoft.com> wrote in message
news:243401c50d2a$2000fb90$a401280a@.phx.gbl...
> Hi!
> I'm running MS Windows 2000 SP4 + SQL Server 2000 SP3a.
> I've got a SQL Server Assertion: File:
> < p:\sql\ntdbms\storeng\drs\include\record
.inl>, line=1447
> Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <=
> MAXDATAROW'. Error: 3624, Severity: 20, State: 1. The
> CHECKDB of every DB on that server came up clean (0
> allocation and 0 consistency errors). Could this
> effectively mean that I've discovered a bug in SQL Server?
> --
> Thanks,
> Osk
>sql

A bug in SQL Server 2000?

Hi!
I'm running MS Windows 2000 SP4 + SQL Server 2000 SP3a.
I've got a SQL Server Assertion: File:
<p:\sql\ntdbms\storeng\drs\include\record.inl>, line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'. Error: 3624, Severity: 20, State: 1. The
CHECKDB of every DB on that server came up clean (0
allocation and 0 consistency errors). Could this
effectively mean that I've discovered a bug in SQL Server?
--
Thanks,
OskAs you alluded to, this typically indicates corruption in your database.
However, there is a potential for in-memory kinds of corruption events in
which the page in memory is corrupted, but the image on disk is not. That
is most often caused by hardware issues. I would recommend that you open a
case with Microsoft PSS (http://support.microsoft.com) after you have
verified that you have no hardware issues.
--
Ryan Stonecipher
Microsoft SQL Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Osk" <anonymous@.discussions.microsoft.com> wrote in message
news:243401c50d2a$2000fb90$a401280a@.phx.gbl...
> Hi!
> I'm running MS Windows 2000 SP4 + SQL Server 2000 SP3a.
> I've got a SQL Server Assertion: File:
> <p:\sql\ntdbms\storeng\drs\include\record.inl>, line=1447
> Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <=> MAXDATAROW'. Error: 3624, Severity: 20, State: 1. The
> CHECKDB of every DB on that server came up clean (0
> allocation and 0 consistency errors). Could this
> effectively mean that I've discovered a bug in SQL Server?
> --
> Thanks,
> Osk
>

A better Windows Scheduler

I am looking for a scheduler to run batch jobs and vbscripts. I like
the SQLServerAgent Job scheduler but these jobs will not be running on
a SQL server.
The features I like in SQL are: history, email notifications and
advanced scheduling logic. Also, as a DBA, I've found SQLAgent to be
trustworthy - stable and reliable (unlike Windows Scheduler - which I
find pale in comparisson).
I'm aware that I could add this logic to the batches themselves, but
for the amount of files that will be scheduled daily - that's not a
practical solution.
Anyone thoughts? Thanks.I know CA do a scheduler but i think it will be overkill for your
requirements. Think it's called Unicentre
--
HTH
Ryan Waight, MCDBA, MCSE
"Sean O'Thule" <othule@.hotmail.com> wrote in message
news:63840202.0310060541.15fcf0e1@.posting.google.com...
> I am looking for a scheduler to run batch jobs and vbscripts. I like
> the SQLServerAgent Job scheduler but these jobs will not be running on
> a SQL server.
> The features I like in SQL are: history, email notifications and
> advanced scheduling logic. Also, as a DBA, I've found SQLAgent to be
> trustworthy - stable and reliable (unlike Windows Scheduler - which I
> find pale in comparisson).
> I'm aware that I could add this logic to the batches themselves, but
> for the amount of files that will be scheduled daily - that's not a
> practical solution.
> Anyone thoughts? Thanks.|||It may not meet you needs, but have you considered using DTS packages as the
container for managing the VB scripts, etc you need to run. You can then use
SQL Agent to actually manage the DTS packages.
You could easily run jobs that are not actually on the SQL Server... Not a
true general purpose scheduler, but it's free.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Sean O'Thule" <othule@.hotmail.com> wrote in message
news:63840202.0310060541.15fcf0e1@.posting.google.com...
> I am looking for a scheduler to run batch jobs and vbscripts. I like
> the SQLServerAgent Job scheduler but these jobs will not be running on
> a SQL server.
> The features I like in SQL are: history, email notifications and
> advanced scheduling logic. Also, as a DBA, I've found SQLAgent to be
> trustworthy - stable and reliable (unlike Windows Scheduler - which I
> find pale in comparisson).
> I'm aware that I could add this logic to the batches themselves, but
> for the amount of files that will be scheduled daily - that's not a
> practical solution.
> Anyone thoughts? Thanks.|||I think you are referring to Autosys.
Linchi
>--Original Message--
>I know CA do a scheduler but i think it will be overkill
for your
>requirements. Think it's called Unicentre
>--
>HTH
>Ryan Waight, MCDBA, MCSE
>"Sean O'Thule" <othule@.hotmail.com> wrote in message
>news:63840202.0310060541.15fcf0e1@.posting.google.com...
>> I am looking for a scheduler to run batch jobs and
vbscripts. I like
>> the SQLServerAgent Job scheduler but these jobs will
not be running on
>> a SQL server.
>> The features I like in SQL are: history, email
notifications and
>> advanced scheduling logic. Also, as a DBA, I've found
SQLAgent to be
>> trustworthy - stable and reliable (unlike Windows
Scheduler - which I
>> find pale in comparisson).
>> I'm aware that I could add this logic to the batches
themselves, but
>> for the amount of files that will be scheduled daily -
that's not a
>> practical solution.
>> Anyone thoughts? Thanks.
>
>.
>|||Looks expensive, I forgot to mention, I was hoping to find something
inexpensive or free.
I'm surprised MS doesn't take the componenets of the SQLAgent job
scheduler and generalize it for the OS. It's really a useful tool.
I want to avoid running everything from a SQL server. The first
server that I am looking to run this on will be an ftp server. Bits
of code will exist on that server itself (to encrypt/decrypt files).
So I prefer to run scripts locally.
Thanks for the input.

A better way of running SPs from Excel

Hi All,

I didn't find an Excel forum here so I thought this might be the 'next best place'.

We have developed a 'packaged EDW' and the first version is all on the MSFT stack....though we do not want to use AS as we do want the product to be able to be back ended by other databases.....(heresy here, I know).

We have built what we are now called the 'Data Access Layer'.

The DAL is a suite of stored procedures.......the idea is simple.......ask a question via a SP with a set of parameters and it gives you a data stream in return. We are using Report Services first and it calls the DAL for its data.

But we have been thinking.....

The entire world has Excel on their desktops........and lots of those people love to have their reports in Excel (and not Report Services)....

We would like to find a really good way of using Exel on top of the Data Access Layer....so far, in looking into excel it seems we can really only run MS Query and return the data to a sheet and then go from there....this seems quite 'clunky'.

We are well aware of the idea of putting the data into AS and going from there...but we want to explore getting the data from SPs so that if we build some excel based reports and put the data into another database it will still work....

We have seen Oracle provides java add ins to Excel to get data directly from Oracle databases...

We are wondering if someone, somewhere, has developed 'the best' way of getting data from SPs into an excel workbook.

What we have are SPs to select options....such as time, product hierarchy, company reporting structure etc....and then the user selects from these options and the selections are passed to an SP to get the results for the selection..all the usual things needed for RS reports.....

We are wondering if someone has done something that is similar where the data is then delivered to Excel reports...

Our first big questions would be:

How to effectively select parameters for the report where the parameters are in the database?

How to effectively refresh the Excel reports on request or on a schedule?
How to most effectively imbed the calls to the stored procedures in Excel?

Sorry if I am in the wrong place....

Best Regards

OK, agreed that calling stored procedures from Excel is really clumsy. You will have to use VBA to do all the stuff behind, there is no easy way to implement that in Excel. Another thing would be to use an Analysis Source rather than a relational. This is far away from your original questions but can ive you the data in a much better and effective way than procedures can eventually do. But now to your questions:
1. You want tsomething like a dropdown box for selectable values ? You will have to populate them using VBA. A usable approach would be to fill some invisible excel sheets and point the filter boxes to the range of the extracted values.
2. You can use timers in VBA to refresh the code. Off the top of my head I cannot remember wehre the option in Excel was to refresh datasources on a regular basis. (By the way, using Reporting Services can do this all behind the scenes using a delivered report to a fileshare)
3. Well, all I know is that the most flexible way to do this is calling the procedures from VBA.
Hope my thoughts can help you, maybe you post your question also in one of the public newsgroups regarding Excel as there is none here in the MSDN ones.
Jens K. Suessmeyer

http://www.sqlserver2005.de

Monday, March 19, 2012

9MB of errors in log file each time RS report is run

I have a report that is running extremely slowly. The Reporting
Services log files seem to generate 9MB of errors each time I run the
report. The error is the text below repeated over and over. Can anyone
help?
Thanks,
Burt
<Header>
<Product>Microsoft SQL Server Reporting Services Version
8.00.743.00</Product>
<Locale>en-US</Locale>
<TimeZone>Pacific Daylight Time</TimeZone>
<Path>C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\LogFiles\ReportServer__07_01_2004_11_38_28.log</Path>
<SystemName>SFCMSREPORTS</SystemName>
<OSName>Microsoft Windows NT 5.0.2195.0</OSName>
<OSVersion>5.0.2195.0</OSVersion>
</Header>
aspnet_wp!processing!1c24!7/1/2004-11:38:28:: a ASSERT: Assertion
failed! Call stack:
Microsoft.ReportingServices.ReportProcessing.ExprHostObjectModel.IndexedExprHost.get_Item(Int32
index)
Microsoft.ReportingServices.ReportProcessing.be.a(e A_0, ObjectType
A_1, String A_2, String A_3)
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+as.a(e
A_0, String A_1)
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+as.c()
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ay.b()
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ay.e()
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ay.h()
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ay.c()
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ac.b()
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ax.d()
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+a2.g()
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ax.a(a5
A_0)
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+a2.f()
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+a2.a(Boolean&
A_0)
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ax.b()
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+a2.a(Object
A_0)
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+s.a()
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+s.a(Object
A_0)
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+bb.a(ParameterInfoCollection
A_0, Boolean A_1, GetReportChunk A_2)
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.a(Report
A_0, ProcessingContext A_1, Boolean A_2, GetReportChunk A_3, ax A_4,
DateTime A_5, z& A_6)
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderReport(DateTime
executionTimeStamp, GetReportChunk getCompiledDefinitionCallback,
ProcessingContext pc, RenderingContext rc)
Microsoft.ReportingServices.Library.RSService.RenderAsLive(CatalogItemContext
reportContext, ItemProperties properties, ParameterInfoCollection
effectiveParameters, Guid reportId, ClientRequest session, String
description, ReportSnapshot intermediateSnapshot,
DataSourceInfoCollection thisReportDataSources, Boolean
cachingRequested, Warning[]& warnings, ReportSnapshot&
resultSnapshotData, DateTime& executionDateTime,
RuntimeDataSourceInfoCollection& alldataSources, UserProfileState&
usedUserProfile)
Microsoft.ReportingServices.Library.RSService.RenderAsLiveOrSnapshot(CatalogItemContext
reportContext, ClientRequest session, Warning[]& warnings,
ParameterInfoCollection& effectiveParameters)
Microsoft.ReportingServices.Library.RSService.RenderFirst(CatalogItemContext
reportContext, ClientRequest session, Warning[]& warnings,
ParameterInfoCollection& effectiveParameters, String[]&
secondaryStreamNames)
Microsoft.ReportingServices.Library.RenderFirstCancelableStep.Execute()
Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()
Microsoft.ReportingServices.Library.RenderFirstCancelableStep.RenderFirst(RSService
rs, CatalogItemContext reportContext, ClientRequest session,
JobTypeEnum type, Warning[]& warnings, ParameterInfoCollection&
effectiveParameters, String[]& secondaryStreamNames)
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderReport(HttpResponseStreamFactory
streamFactory)
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderReport()
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderItem(ItemType
itemType)
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPageContent()
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPage()
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.ProcessRequest(HttpContext
context)
System.Web.HttpApplication+CallHandlerExecutionStep.System.Web.HttpApplication+IExecutionStep.Execute()
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean&
completedSynchronously)
System.Web.HttpApplication.ResumeSteps(Exception error)
System.Web.HttpApplication.System.Web.IHttpAsyncHandler.BeginProcessRequest(HttpContext
context, AsyncCallback cb, Object extraData)
System.Web.HttpRuntime.ProcessRequestInternal(HttpWorkerRequest wr)
System.Web.HttpRuntime.ProcessRequest(HttpWorkerRequest wr)
System.Web.Hosting.ISAPIRuntime.ProcessRequest(IntPtr ecb, Int32
iWRType)Hi Burt, could you please send report definition?
"Burt" burt_5920@.yahoo.com wrote in message
news:19e5f39f.0407011047.ccfd350@.posting.google.com...
> I have a report that is running extremely slowly. The Reporting
> Services log files seem to generate 9MB of errors each time I run the
> report. The error is the text below repeated over and over. Can anyone
> help?
> Thanks,
> Burt
> <Header>
> <Product>Microsoft SQL Server Reporting Services Version
> 8.00.743.00</Product>
> <Locale>en-US</Locale>
> <TimeZone>Pacific Daylight Time</TimeZone>
> <Path>C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\LogFiles\ReportServer__07_01_2004_11_38_28.log</Path>
> <SystemName>SFCMSREPORTS</SystemName>
> <OSName>Microsoft Windows NT 5.0.2195.0</OSName>
> <OSVersion>5.0.2195.0</OSVersion>
> </Header>
> aspnet_wp!processing!1c24!7/1/2004-11:38:28:: a ASSERT: Assertion
> failed! Call stack:
>
Microsoft.ReportingServices.ReportProcessing.ExprHostObjectModel.IndexedExpr
Host.get_Item(Int32
> index)
> Microsoft.ReportingServices.ReportProcessing.be.a(e A_0, ObjectType
> A_1, String A_2, String A_3)
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing+as.a(e
> A_0, String A_1)
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing+as.c()
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ay.b()
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ay.e()
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ay.h()
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ay.c()
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ac.b()
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ax.d()
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing+a2.g()
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ax.a(a5
> A_0)
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing+a2.f()
>
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+a2.a(Boolean&
> A_0)
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ax.b()
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing+a2.a(Object
> A_0)
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing+s.a()
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing+s.a(Object
> A_0)
>
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+bb.a(Parameter
InfoCollection
> A_0, Boolean A_1, GetReportChunk A_2)
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing.a(Report
> A_0, ProcessingContext A_1, Boolean A_2, GetReportChunk A_3, ax A_4,
> DateTime A_5, z& A_6)
>
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderReport(D
ateTime
> executionTimeStamp, GetReportChunk getCompiledDefinitionCallback,
> ProcessingContext pc, RenderingContext rc)
>
Microsoft.ReportingServices.Library.RSService.RenderAsLive(CatalogItemContex
t
> reportContext, ItemProperties properties, ParameterInfoCollection
> effectiveParameters, Guid reportId, ClientRequest session, String
> description, ReportSnapshot intermediateSnapshot,
> DataSourceInfoCollection thisReportDataSources, Boolean
> cachingRequested, Warning[]& warnings, ReportSnapshot&
> resultSnapshotData, DateTime& executionDateTime,
> RuntimeDataSourceInfoCollection& alldataSources, UserProfileState&
> usedUserProfile)
>
Microsoft.ReportingServices.Library.RSService.RenderAsLiveOrSnapshot(Catalog
ItemContext
> reportContext, ClientRequest session, Warning[]& warnings,
> ParameterInfoCollection& effectiveParameters)
>
Microsoft.ReportingServices.Library.RSService.RenderFirst(CatalogItemContext
> reportContext, ClientRequest session, Warning[]& warnings,
> ParameterInfoCollection& effectiveParameters, String[]&
> secondaryStreamNames)
> Microsoft.ReportingServices.Library.RenderFirstCancelableStep.Execute()
>
Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()
>
Microsoft.ReportingServices.Library.RenderFirstCancelableStep.RenderFirst(RS
Service
> rs, CatalogItemContext reportContext, ClientRequest session,
> JobTypeEnum type, Warning[]& warnings, ParameterInfoCollection&
> effectiveParameters, String[]& secondaryStreamNames)
>
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderReport(
HttpResponseStreamFactory
> streamFactory)
>
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderReport(
)
>
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderItem(It
emType
> itemType)
>
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPageCon
tent()
>
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPage()
>
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.ProcessReques
t(HttpContext
> context)
>
System.Web.HttpApplication+CallHandlerExecutionStep.System.Web.HttpApplicati
on+IExecutionStep.Execute()
> System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean&
> completedSynchronously)
> System.Web.HttpApplication.ResumeSteps(Exception error)
>
System.Web.HttpApplication.System.Web.IHttpAsyncHandler.BeginProcessRequest(
HttpContext
> context, AsyncCallback cb, Object extraData)
> System.Web.HttpRuntime.ProcessRequestInternal(HttpWorkerRequest wr)
> System.Web.HttpRuntime.ProcessRequest(HttpWorkerRequest wr)
> System.Web.Hosting.ISAPIRuntime.ProcessRequest(IntPtr ecb, Int32
> iWRType)

8Gb RAM for SQL Server 2000 on Win 2003

What the setting should be (/3Gb, PAE, AWE) for the best utilization of
8GB RAM for SQL Server 2000 running on Win 2003?
ThanksHi,
After this you will have to do this:-
Probably you could configure 6 GB for SQL Server: Login as SA using query
analyzer and do:-
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 6144
RECONFIGURE
GO
Thanks
Hari
SQL Server MVP
"vlad" <vesmusor@.yahoo.com> wrote in message
news:1158846464.619887.179240@.h48g2000cwc.googlegroups.com...
> What the setting should be (/3Gb, PAE, AWE) for the best utilization of
> 8GB RAM for SQL Server 2000 running on Win 2003?
> Thanks
>|||Thanks for your reply, Hari. Does it mean I need to use both /3GB and
/PAE switches for this configuration?
Hari Prasad wrote:
> Hi,
> After this you will have to do this:-
> Probably you could configure 6 GB for SQL Server: Login as SA using query
> analyzer and do:-
> sp_configure 'show advanced options', 1
> RECONFIGURE
> GO
> sp_configure 'awe enabled', 1
> RECONFIGURE
> GO
> sp_configure 'max server memory', 6144
> RECONFIGURE
> GO
> Thanks
> Hari
> SQL Server MVP
> "vlad" <vesmusor@.yahoo.com> wrote in message
> news:1158846464.619887.179240@.h48g2000cwc.googlegroups.com...
> > What the setting should be (/3Gb, PAE, AWE) for the best utilization of
> > 8GB RAM for SQL Server 2000 running on Win 2003?
> >
> > Thanks
> >|||You need to use PAE (and SQL Server use AWE) to have 32 but app on 32 bit OS access > 2GB memory.
Whether you use 3GB or not is up to you. For a system with 8BG memory, it is probably a good idea to
use 3GB. Google for "Slava Oks" and read the Blogs, this will give you more information so you
understand the ins and outs of setting 3GB, PAE and also AWE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"vlad" <vesmusor@.yahoo.com> wrote in message
news:1158848235.068934.133350@.i42g2000cwa.googlegroups.com...
> Thanks for your reply, Hari. Does it mean I need to use both /3GB and
> /PAE switches for this configuration?
> Hari Prasad wrote:
>> Hi,
>> After this you will have to do this:-
>> Probably you could configure 6 GB for SQL Server: Login as SA using query
>> analyzer and do:-
>> sp_configure 'show advanced options', 1
>> RECONFIGURE
>> GO
>> sp_configure 'awe enabled', 1
>> RECONFIGURE
>> GO
>> sp_configure 'max server memory', 6144
>> RECONFIGURE
>> GO
>> Thanks
>> Hari
>> SQL Server MVP
>> "vlad" <vesmusor@.yahoo.com> wrote in message
>> news:1158846464.619887.179240@.h48g2000cwc.googlegroups.com...
>> > What the setting should be (/3Gb, PAE, AWE) for the best utilization of
>> > 8GB RAM for SQL Server 2000 running on Win 2003?
>> >
>> > Thanks
>> >
>|||Doesn't this all assume he's running Enterprise Edition of SQL Server?
I thought non enterprise version didn't address this much RAM?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eHS1C9a3GHA.3344@.TK2MSFTNGP05.phx.gbl...
> You need to use PAE (and SQL Server use AWE) to have 32 but app on 32 bit
> OS access > 2GB memory.
> Whether you use 3GB or not is up to you. For a system with 8BG memory, it
> is probably a good idea to use 3GB. Google for "Slava Oks" and read the
> Blogs, this will give you more information so you understand the ins and
> outs of setting 3GB, PAE and also AWE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "vlad" <vesmusor@.yahoo.com> wrote in message
> news:1158848235.068934.133350@.i42g2000cwa.googlegroups.com...
>> Thanks for your reply, Hari. Does it mean I need to use both /3GB and
>> /PAE switches for this configuration?
>> Hari Prasad wrote:
>> Hi,
>> After this you will have to do this:-
>> Probably you could configure 6 GB for SQL Server: Login as SA using
>> query
>> analyzer and do:-
>> sp_configure 'show advanced options', 1
>> RECONFIGURE
>> GO
>> sp_configure 'awe enabled', 1
>> RECONFIGURE
>> GO
>> sp_configure 'max server memory', 6144
>> RECONFIGURE
>> GO
>> Thanks
>> Hari
>> SQL Server MVP
>> "vlad" <vesmusor@.yahoo.com> wrote in message
>> news:1158846464.619887.179240@.h48g2000cwc.googlegroups.com...
>> > What the setting should be (/3Gb, PAE, AWE) for the best utilization
>> > of
>> > 8GB RAM for SQL Server 2000 running on Win 2003?
>> >
>> > Thanks
>> >
>|||Correct. I assumed EE as no edition was mentioned in the original post...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Nick" <no.thanks@.nowhere.com> wrote in message news:enNh7uk3GHA.1796@.TK2MSFTNGP06.phx.gbl...
> Doesn't this all assume he's running Enterprise Edition of SQL Server?
> I thought non enterprise version didn't address this much RAM?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:eHS1C9a3GHA.3344@.TK2MSFTNGP05.phx.gbl...
>> You need to use PAE (and SQL Server use AWE) to have 32 but app on 32 bit
>> OS access > 2GB memory.
>> Whether you use 3GB or not is up to you. For a system with 8BG memory, it
>> is probably a good idea to use 3GB. Google for "Slava Oks" and read the
>> Blogs, this will give you more information so you understand the ins and
>> outs of setting 3GB, PAE and also AWE.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "vlad" <vesmusor@.yahoo.com> wrote in message
>> news:1158848235.068934.133350@.i42g2000cwa.googlegroups.com...
>> Thanks for your reply, Hari. Does it mean I need to use both /3GB and
>> /PAE switches for this configuration?
>> Hari Prasad wrote:
>> Hi,
>> After this you will have to do this:-
>> Probably you could configure 6 GB for SQL Server: Login as SA using
>> query
>> analyzer and do:-
>> sp_configure 'show advanced options', 1
>> RECONFIGURE
>> GO
>> sp_configure 'awe enabled', 1
>> RECONFIGURE
>> GO
>> sp_configure 'max server memory', 6144
>> RECONFIGURE
>> GO
>> Thanks
>> Hari
>> SQL Server MVP
>> "vlad" <vesmusor@.yahoo.com> wrote in message
>> news:1158846464.619887.179240@.h48g2000cwc.googlegroups.com...
>> > What the setting should be (/3Gb, PAE, AWE) for the best utilization
>> > of
>> > 8GB RAM for SQL Server 2000 running on Win 2003?
>> >
>> > Thanks
>> >
>>
>

8Gb RAM for SQL Server 2000 on Win 2003

8gb of log files - how to i stop ?

Hi,
Reporting services has been running for a couple of months. I noticed today
that I had 8GB of log files. Is there anything I can do to stop so much
being logged ? i don't really need it.
Thanks
ScottThis is a known issue (Bug #: 304241 -
http://support.microsoft.com/kb/885286/) and is fixed in Reporting Services
SP2 http://www.microsoft.com/downloads/details.aspx?FamilyID=502C0D89-130...
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"scott" <nospamscott@.yahoo.com> wrote in message
news:uConQxBlFHA.708@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Reporting services has been running for a couple of months. I noticed
> today that I had 8GB of log files. Is there anything I can do to stop so
> much being logged ? i don't really need it.
> Thanks
> Scott
>|||many thanks
scott

Sunday, March 11, 2012

8114 error w/db maintenance plan

Running SQL Desktop Engine 2000, sp3. I have a db maintenance plan that I
can initially set up, but when I go back into and hit OK, I get the following
error:
Error 8114: Error converting data type into to tinyint.
This happens whether I make any changes or not. The job fails with the
basic sqlmaint.exe error:
Executed as user: NT AUTHORITY/SYSTEM. sqlmaint.exe failed [SQLSTATE 42000]
(Error 22029). The step failed.
Also, when I try to set the amount of time after which to purge backup
files, the field is blank/unpopulated. It should be 'days', 'weeks', etc.
I saw this same issue on some other websites, but no one had an answer. Any
advice? Thanks.
I can't remember what the 8114 error is but the unpopulated
days, weeks, months on the backups was sometimes related to
a missing registry value for the backup directory.
On a default instance, you would add a new string value to:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ MSSQLServer
The new string value is BackupDirectory
The data value for this is the path to your backup
directory. The default would be
C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP
-Sue
On Mon, 27 Jun 2005 11:50:03 -0700, MEB
<MEB@.discussions.microsoft.com> wrote:

>Running SQL Desktop Engine 2000, sp3. I have a db maintenance plan that I
>can initially set up, but when I go back into and hit OK, I get the following
>error:
>Error 8114: Error converting data type into to tinyint.
>This happens whether I make any changes or not. The job fails with the
>basic sqlmaint.exe error:
>Executed as user: NT AUTHORITY/SYSTEM. sqlmaint.exe failed [SQLSTATE 42000]
>(Error 22029). The step failed.
>Also, when I try to set the amount of time after which to purge backup
>files, the field is blank/unpopulated. It should be 'days', 'weeks', etc.
>I saw this same issue on some other websites, but no one had an answer. Any
>advice? Thanks.

8114 error w/db maintenance plan

Running SQL Desktop Engine 2000, sp3. I have a db maintenance plan that I
can initially set up, but when I go back into and hit OK, I get the following
error:
Error 8114: Error converting data type into to tinyint.
This happens whether I make any changes or not. The job fails with the
basic sqlmaint.exe error:
Executed as user: NT AUTHORITY/SYSTEM. sqlmaint.exe failed [SQLSTATE 42000]
(Error 22029). The step failed.
Also, when I try to set the amount of time after which to purge backup
files, the field is blank/unpopulated. It should be 'days', 'weeks', etc.
I saw this same issue on some other websites, but no one had an answer. Any
advice? Thanks.I can't remember what the 8114 error is but the unpopulated
days, weeks, months on the backups was sometimes related to
a missing registry value for the backup directory.
On a default instance, you would add a new string value to:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer
The new string value is BackupDirectory
The data value for this is the path to your backup
directory. The default would be
C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP
-Sue
On Mon, 27 Jun 2005 11:50:03 -0700, MEB
<MEB@.discussions.microsoft.com> wrote:
>Running SQL Desktop Engine 2000, sp3. I have a db maintenance plan that I
>can initially set up, but when I go back into and hit OK, I get the following
>error:
>Error 8114: Error converting data type into to tinyint.
>This happens whether I make any changes or not. The job fails with the
>basic sqlmaint.exe error:
>Executed as user: NT AUTHORITY/SYSTEM. sqlmaint.exe failed [SQLSTATE 42000]
>(Error 22029). The step failed.
>Also, when I try to set the amount of time after which to purge backup
>files, the field is blank/unpopulated. It should be 'days', 'weeks', etc.
>I saw this same issue on some other websites, but no one had an answer. Any
>advice? Thanks.

8114 error w/db maintenance plan

Running SQL Desktop Engine 2000, sp3. I have a db maintenance plan that I
can initially set up, but when I go back into and hit OK, I get the followin
g
error:
Error 8114: Error converting data type into to tinyint.
This happens whether I make any changes or not. The job fails with the
basic sqlmaint.exe error:
Executed as user: NT AUTHORITY/SYSTEM. sqlmaint.exe failed [SQLSTATE 420
00]
(Error 22029). The step failed.
Also, when I try to set the amount of time after which to purge backup
files, the field is blank/unpopulated. It should be 'days', 'weeks', etc.
I saw this same issue on some other websites, but no one had an answer. Any
advice? Thanks.I can't remember what the 8114 error is but the unpopulated
days, weeks, months on the backups was sometimes related to
a missing registry value for the backup directory.
On a default instance, you would add a new string value to:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer
The new string value is BackupDirectory
The data value for this is the path to your backup
directory. The default would be
C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP
-Sue
On Mon, 27 Jun 2005 11:50:03 -0700, MEB
<MEB@.discussions.microsoft.com> wrote:

>Running SQL Desktop Engine 2000, sp3. I have a db maintenance plan that I
>can initially set up, but when I go back into and hit OK, I get the followi
ng
>error:
>Error 8114: Error converting data type into to tinyint.
>This happens whether I make any changes or not. The job fails with the
>basic sqlmaint.exe error:
>Executed as user: NT AUTHORITY/SYSTEM. sqlmaint.exe failed [SQLSTATE 42
000]
>(Error 22029). The step failed.
>Also, when I try to set the amount of time after which to purge backup
>files, the field is blank/unpopulated. It should be 'days', 'weeks', etc.
>I saw this same issue on some other websites, but no one had an answer. An
y
>advice? Thanks.

8 hours to add a bit column

I ran the following:

ALTER TABLE Recipients ADD Obscene BIT NOT NULL DEFAULT 0

On a table with 80 million records. It's been running for 8 hours and counting now. This is ridiculous. No one else is using this server.

Server configuration:
SQL Server 2000 Enterprise Edition with SP3a
2 GB RAM
3.0 GHz P4 with hyperthreading
SCSI RAID

Any ideas why this is taking so long? Can I find out what it's doing? Is there anything I can do to make it go faster?You won't like any of my answers.

Before we descend into that morass though, crank up the NT Performance Monitor and have a look-see at the server. Is it CPU bound, disk bound, memory bound, or are all of those counters at reasonable levels? How many SQL threads (spids) are active? What do the SQL page counters look like?

My guess would be that the box is hideously RAM bound, that it is having page splits up the ying-yang, and that it might be disk bound as well.

Is your log file on a different disk device than your data files? Is that device mirrored instead of RAID? Do you have the ability to add RAM with the box running (some servers can do that!)?

-PatP|||If your answers give me any ideas and any closer to figuring these kinds of problems out, I like them. The business that I'm working for is very data driven and we are moving quickly in a database direction and we need this knowledge.

I've been watching CPU and disc levels in perfmon all day. The system has been steadily disc bound.
sp_who 'active' returns 17 rows.
How do I look at the page counters?

A RAM bound system exhibits itself as disc bound, correct? With lots of paging to/from disc.

The data files and temp db are on a SCSI RAID 5. The main log file is on a separate 250 GB IDE disc (it has grown to 150 GB in the past which wouldn't fit on the RAID)

You won't like any of my answers.

Before we descend into that morass though, crank up the NT Performance Monitor and have a look-see at the server. Is it CPU bound, disk bound, memory bound, or are all of those counters at reasonable levels? How many SQL threads (spids) are active? What do the SQL page counters look like?

My guess would be that the box is hideously RAM bound, that it is having page splits up the ying-yang, and that it might be disk bound as well.

Is your log file on a different disk device than your data files? Is that device mirrored instead of RAID? Do you have the ability to add RAM with the box running (some servers can do that!)?

-PatP|||if you trace an alter table statement it will show you the issue i think you are hitting.

alter table creates a new table in temp space with the new column, inserts all the data from the old into the new temp, then swaps the names around.

with 80million rows, i'd be willing to bet your are being I/O thottled either creating the temp (which using a low logged select..into..) or the insert, (which is using a fully logged insert into..select).

either way, 8 hours sux but doesnt really suprise me.|||I'v never had to wait 8 hours to add a column to a big table. Besides, I thought that the EM did the swap-trick and the alter table did not.|||i'd agree...8 hours is way out there.
you are correct...EM does the old swap'r'roo trick. that was an assumption on my part - uber apologies - that you were using the Enterprise Mangler.

i would suspect the same thing is happening underneath an alter, though.
(i'm moving into the 'out of my ass' realm so i'm going to qualify that statement as an 'idea'- not something i claim to know.)

the page counters are under the Memory object with some useful other SQL specifc page counters SQLServer:BufferManager.

i wonder if your disk queues are backing up for reads or writes?

perhaps that could help understand if a large read/write is actually occuring underneath your alter?|||hm, here's another though. Perhaps the Analyzer is waiting for the table to be freed from a lock. What does sysprocesses say?|||Just curious as to why you made it NOT NULL?

USE Northwind
GO

CREATE TABLE myTable99 (Col1 int)
GO
INSERT INTO myTable99(Col1) SELECT 1
GO

SELECT * FROM myTable99

ALTER TABLE myTable99 ADD Obscene BIT NOT NULL DEFAULT 0
GO

SELECT * FROM myTable99
GO

ALTER TABLE myTable99 ADD Obscene2 BIT DEFAULT 0
GO

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO

If you needed it null maybe you caould have performed batch updates after the fact, the changed ALTERed the column to make it NOT NULL...

Is it still running?|||OK, here comes Robert with his BCP again...But it's true, no matter how you look at it! Non-logged data load would beat "in-line" DDL+DML (because this is exactly what happens when you add a new NON-NULLable column with default - the only way to add a new non-nullable column) If you added the same column but made it nullable, - you'd be onto something else 7 hours and 59 minutes ago. At this point though you can't even interrupt this operation because ALTER TABLE is a fully logged operation. It means that every 0 that came from your DEFAULT is logged in your transaction log (thus its size is very explainable). If you decide to kill the process you'll be looking at 8+ hours of rollback. If you stop the service you'll be looking at "Recovering database x..." for probably as much.|||Just curious as to why you made it NOT NULL?

That's an application issue, right? We are trying to mark certain records as "obscene", so every record should be obscene or not. There should be no NULL. I didn't realize that this would be a large performance issue. If I knew in advance I could have dealt with NULL values in one way or another.

It finished overnight but as of 12:30 AM last night, it was running for 13 hours and still going.

I've previously added datetime columns to the same table with EM (this was added via QA with a ALTER TABLE statement) and it took less than an hour. I was really surprised that this took so long. Ideally, I know what to look for to remedy such an issue and how to prevent such things from happening.

thanks guys!|||Did you look at the code that em scripted for you?

Just make sure you don't have a table called tmp_yourtable..

WAIT...damn I just tested it...it's smart enough to add a _1 to the end...damn that's good|||WAIT...damn I just tested it...it's smart enough to add a _1 to the end...damn that's goodTricky little devils, ain't they ?

-PatP|||curious - when you added the datetime values last time did you set a default of getdate()? or something else? or allow them to be null?|||curious - when you added the datetime values last time did you set a default of getdate()? or something else? or allow them to be null?

They defaulted to NULL. And it was added through EM as opposed to an ALTER TABLE statement.

The exact time on adding the bit column was 17 hours and 23 minutes.

Sunday, March 25, 2012

A couple of easy questions

Thursday, March 22, 2012

A connection could not be made to the report server

A connection could not be made to the report server

A connection could not be established...Cannot open user default database...

A connection could not be established...Cannot open user default database...

Tuesday, March 20, 2012

A Bug With Running Total Query?

A bug in SQL Server 2000?

A bug in SQL Server 2000?

A bug in SQL Server 2000?

A better Windows Scheduler

A better way of running SPs from Excel

Monday, March 19, 2012

9MB of errors in log file each time RS report is run

8Gb RAM for SQL Server 2000 on Win 2003

8Gb RAM for SQL Server 2000 on Win 2003

8Gb RAM for SQL Server 2000 on Win 2003

8gb of log files - how to i stop ?

Sunday, March 11, 2012

8114 error w/db maintenance plan

8114 error w/db maintenance plan

8114 error w/db maintenance plan

8 hours to add a bit column

3G ram for SQL server

Blog Archive

About Me