Killing automatically crashed sessions ??

If one of my workstations (which communicate using ODBC) crashes, I still see it as connected in Enterprise Manager under management\current activity\.
Is there anyway to kill automatically the crashed cessions?
Thkx in advanceYou could periodicaly scan the sysprocesses table and kill any connection that hasn't had activity in some predeturmined amount of time.

Killer problem with Scheduled Reports

I'm running on SQL 2000 with SRS. Standard install (I think, it was all set up by IT)

I have reports that run fine in the Report Manager but when they are run through a schedule (email) some of them (albeit most) "rsProcessingAborted" because of a report error.

Looking at the logs it seems generally based around comparison failure because of datatypes, eg (from the logs):

Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: The processing of sort expression for the list 'List1' cannot be performed. The comparison failed. Please check the data type returned by sort expression. Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: The processing of group expression for the table 'tblGroupTable' cannot be performed. The comparison failed. Please check the data type returned by group expression.

Now, when I remove sorting/grouping the reports run through the schedule OK; but that is obviously NOT what I want the report to look like. But it proves that the sorting/grouping has something to do with the problem.

I'm pretty sure that they are NOT the problem though, as the reports render in Report Manager fine with all the original sorting/grouping in place (ie no problems during development, just when we wanted them served to the employees. Isn't that always the way it goes?). We've even had some reports which scheduled OK previously, but throw errors now.

I'm at a loss!!

Could it be a time-out issue somewhere? If so: where? Looking at the Execution Logs, the reports that fail have unusually long Process Times, with a Data Retrieval Time of 0. I guess that the process time is longer because of processing the error, but does the Data Retrieval time of 0 mean that no data was delivered to the report? How can that be? IT tell me it can't be memory issues as it's sitting on a 2GB machine, but could there be something else with SQL set-up? I googled the error and found a few pages where people said that puting SRS in its own application pool fixed their problem. When I passed this on to IT they said that would only help memory... Any ideas?

Any suggestions greatfully appreciated

Do the report subscriptions ALWAYS fail, or intermittently?

Do those same reports ALWAYS succeed when executed live?

How much RAM is on the Dev machine, and what OS?

What OS is on the productio machine?

What changed between the time that some scheduled reports worked fine, and when they started erroring?

Removing the grouping/sorting may have alleviated the load on the processing engine enough so that the report succeeded.

|||Also, what are the types and #'s of CPU's on the Dev and Production machines?|||

Mike Schetterer -- MSFT wrote:

Do the report subscriptions ALWAYS fail, or intermittently?

The reports seem to fail always when they contain some data. Maybe the oposite is better: The reports don't fail when they do not contain data. Of the report I can remember off the top of my head: the schedule didn't fail on 4 occasions, three of those didn't have any data, one had one record.

For example, for one of our reports that is meant to run this morning:

Time Start

Time End

Data Retrieval

Process

Render

Status

User Name

pmtStartDate=01/01/2000 00:00:00
pmtEndDate=02/06/2006 00:00:00
Team=3

6/02/2006 7:00:08 AM

6/02/2006 7:00:09 AM

0

112

0

rsProcessingAborted

NT AUTHORITY\NETWORK SERVICE

---

pmtStartDate=01/01/2000 00:00:00
pmtEndDate=02/06/2006 00:00:00
Team=8

6/02/2006 7:00:08 AM

6/02/2006 7:00:12 AM

2979

359

255

rsSuccess

NT AUTHORITY\NETWORK SERVICE

---

pmtStartDate=01/01/2000 00:00:00
pmtEndDate=02/06/2006 00:00:00
Team=6

6/02/2006 7:00:57 AM

6/02/2006 7:00:58 AM

0

47

0

rsProcessingAborted

NT AUTHORITY\NETWORK SERVICE

---

pmtStartDate=01/01/2000 00:00:00
pmtEndDate=02/06/2006 00:00:00
Team=11

6/02/2006 7:00:57 AM

6/02/2006 7:00:58 AM

0

60

0

rsProcessingAborted

NT AUTHORITY\NETWORK SERVICE

---

pmtStartDate=01/01/2000 00:00:00
pmtEndDate=02/06/2006 00:00:00
Team=13

6/02/2006 7:00:58 AM

6/02/2006 7:00:58 AM

0

51

0

rsProcessingAborted

NT AUTHORITY\NETWORK SERVICE

---

pmtStartDate=01/01/2000 00:00:00
pmtEndDate=02/06/2006 00:00:00
Team=1

6/02/2006 7:00:58 AM

6/02/2006 7:00:58 AM

0

72

0

rsProcessingAborted

NT AUTHORITY\NETWORK SERVICE

---

pmtStartDate=01/01/2000 00:00:00
pmtEndDate=02/06/2006 00:00:00
Team=9

6/02/2006 7:00:59 AM

6/02/2006 7:00:59 AM

0

63

0

rsProcessingAborted

NT AUTHORITY\NETWORK SERVICE

---

pmtStartDate=01/01/2000 00:00:00
pmtEndDate=02/06/2006 00:00:00
Team=10

6/02/2006 7:00:59 AM

6/02/2006 7:00:59 AM

0

48

0

rsProcessingAborted

NT AUTHORITY\NETWORK SERVICE

Mike Schetterer -- MSFT wrote:

Do those same reports ALWAYS succeed when executed live?

YES!

Mike Schetterer -- MSFT wrote:

How much RAM is on the Dev machine, and what OS?

Intel Xeon 2.4Gb x 2, 2Gb Ram

The Dev machine and the production machine are the same. I use a Remote Desktop into the production machine from my desktop, I guess the documents are stored on my workstation and deployed to the production machine.

Mike Schetterer -- MSFT wrote:

What OS is on the productio machine?

Windows Server 2003, SP1

Mike Schetterer -- MSFT wrote:

What changed between the time that some scheduled reports worked fine, and when they started erroring?

Nothing that I am aware of... but I'll ask the IT people...

Mike Schetterer -- MSFT wrote:

Removing the grouping/sorting may have alleviated the load on the processing engine enough so that the report succeeded.

The schedules fail regardless of the time of day they run (I wondered if the server got overloaded whilst trying to do other things): but there is no difference.

Another interesting fact: whilst investigating I discovered that one of the reports that ran through a schedule after all grouping and sorting was removed displayed "#error" in a cell that contained the result from a Library I'd written. Though this report runs and displays fine through the Report Manager! (this field was not used in the grouping or sorting either).

Yes, I've read somewhere else that how you've configured the Application Pool can make a difference (our IT said it wouldn't here). But I've definitely read in a different forum that that fixed their problem.

At the moment the Report Server Interface and the Report Server are in the DefaultAppPool.

Thanks for your interest!

Perry

|||

Do your reports have parameters?

When you create your subscription - are the parameters set staticly or are the based on a query. If they're query based - can you try running the query and selecting some of the value combinations you receive in return through the UI to see if they work?

Your comment above about report executions failing when there is data, but succeeding when there is no data - is it true to say that whenever there is data the subscription fails, or just some of the time?

Thanks,

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Lukasz Pawlowski -- MS wrote:

Do your reports have parameters?

Some do, others don't, but it doesn't seem to be a problem.

Lukasz Pawlowski -- MS wrote:

When you create your subscription - are the parameters set staticly or are the based on a query. If they're query based - can you try running the query and selecting some of the value combinations you receive in return through the UI to see if they work?

Both, or sometimes all three (included type in)

Yes, ALL the reports run fine through the Report Manager.

Lukasz Pawlowski -- MS wrote:

Your comment above about report executions failing when there is data, but succeeding when there is no data - is it true to say that whenever there is data the subscription fails, or just some of the time?

No: they fail most of the time. In the section of the ExecutionLog in a previous post you can see that for that report it ran once (with data) and failed the rest (they would have had data too.

Thanks,

Perry

|||

What are the group and sort expressions from the report that you find is failing?

-Lukasz

|||

Lukasz Pawlowski -- MS wrote:

What are the group and sort expressions from the report that you find is failing?

In every case it's "=Fields!FieldName.value"

Perry

Keyset does not exist

I get this error when trying to deploy a report from VS2005, when trying to get to the report manager page, when trying to do anything in SSMS with the report server.

Keyset does not exist (Exception from HRESULT: 0x80090016) (rsRPCError) (Microsoft.ReportingServices.Diagnostics)

Can anyone give me a clue what to do?

Hi GraemeH.

Have you had any luck resolving this?

I am getting the same error while attempting to deploy a report from VS 2005. Report Manager is running. The Report project folders deployed from VS fine, just not the report.

I am running XP Pro SP2.

Thanks.

|||

Hi Folks - I'm hitting the same brick wall. Did you manage to find a resolution? I would be very interested in your solution.

Thanks!

|||I did but can't remember how - I think it was a security problem and RS was running under a wrong account, or I had a renegade version of SQL Express. Sorry can't do better than that!|||I had the same problem so changed my Windows Service Identity to Network Service. I know this is a security issue but at least now I can work back from here to create an account that has just the permissions it needs... later, when I have some time.|||but, I can't create encryption key backup ... how about that?

Keyset does not exist

I get this error when trying to deploy a report from VS2005, when trying to get to the report manager page, when trying to do anything in SSMS with the report server.

Keyset does not exist (Exception from HRESULT: 0x80090016) (rsRPCError) (Microsoft.ReportingServices.Diagnostics)

Can anyone give me a clue what to do?

Hi GraemeH.

Have you had any luck resolving this?

I am getting the same error while attempting to deploy a report from VS 2005. Report Manager is running. The Report project folders deployed from VS fine, just not the report.

I am running XP Pro SP2.

Thanks.

|||

Hi Folks - I'm hitting the same brick wall. Did you manage to find a resolution? I would be very interested in your solution.

Thanks!

|||I did but can't remember how - I think it was a security problem and RS was running under a wrong account, or I had a renegade version of SQL Express. Sorry can't do better than that!|||I had the same problem so changed my Windows Service Identity to Network Service. I know this is a security issue but at least now I can work back from here to create an account that has just the permissions it needs... later, when I have some time.|||but, I can't create encryption key backup ... how about that?

Keyset does not exist

I get this error when trying to deploy a report from VS2005, when trying to get to the report manager page, when trying to do anything in SSMS with the report server.

Keyset does not exist (Exception from HRESULT: 0x80090016) (rsRPCError) (Microsoft.ReportingServices.Diagnostics)

Can anyone give me a clue what to do?

Hi GraemeH.

Have you had any luck resolving this?

I am getting the same error while attempting to deploy a report from VS 2005. Report Manager is running. The Report project folders deployed from VS fine, just not the report.

I am running XP Pro SP2.

Thanks.

|||

Hi Folks - I'm hitting the same brick wall. Did you manage to find a resolution? I would be very interested in your solution.

Thanks!

|||I did but can't remember how - I think it was a security problem and RS was running under a wrong account, or I had a renegade version of SQL Express. Sorry can't do better than that!|||I had the same problem so changed my Windows Service Identity to Network Service. I know this is a security issue but at least now I can work back from here to create an account that has just the permissions it needs... later, when I have some time.|||but, I can't create encryption key backup ... how about that?

Key column is insufficient or incorrect. too many rows were affect

Hi,
When I open table, return all rows using Enterprise Manager and I tried to
edit a column, it returned "Key column is insufficient or incorrect. too man
y
rows were affected by update"
This is a standalone table. No constraint. No formula.
Advice please. TIA !Any reasons to open it in EM?
Do you have any triggers on the table that you modifies to?
If you edit some row , click om another one and see if it worked
Do any modifications thru SP and not by EM
"Desmond" <Desmond@.discussions.microsoft.com> wrote in message
news:51F80698-3DE1-49C7-88A0-093E583DF49F@.microsoft.com...
> Hi,
> When I open table, return all rows using Enterprise Manager and I tried to
> edit a column, it returned "Key column is insufficient or incorrect. too
> many
> rows were affected by update"
> This is a standalone table. No constraint. No formula.
> Advice please. TIA !|||Has the table a primary key defined?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Desmond" <Desmond@.discussions.microsoft.com> wrote in message
news:51F80698-3DE1-49C7-88A0-093E583DF49F@.microsoft.com...
> Hi,
> When I open table, return all rows using Enterprise Manager and I tried to
> edit a column, it returned "Key column is insufficient or incorrect. too m
any
> rows were affected by update"
> This is a standalone table. No constraint. No formula.
> Advice please. TIA !

Keep users from view other databases

Hi,
We are a small ISP that allows user to use Enterprise Manager to work with
their databases. We would like the users to only see that databases they hav
e
permissions for. The way SQL 200 is setup by default the users can see all
the databases on the server.
So the question is can we change something that would prevent users from
seeing the other databases on the server?
Thanks in advance,
BobNot with Enterprise Manager and SQL 2000. The users will be
able to see the other databases. They can't access them
without the appropriate permissions but they will be able to
see them.
-Sue
On Mon, 28 Feb 2005 16:57:03 -0800, "Bob Melani" <Bob
Melani@.discussions.microsoft.com> wrote:

>Hi,
>
>We are a small ISP that allows user to use Enterprise Manager to work with
>their databases. We would like the users to only see that databases they ha
ve
>permissions for. The way SQL 200 is setup by default the users can see all
>the databases on the server.
>So the question is can we change something that would prevent users from
>seeing the other databases on the server?
>Thanks in advance,
>Bob|||Bummer,
Thanks for the answer..
"Sue Hoegemeier" wrote:

> Not with Enterprise Manager and SQL 2000. The users will be
> able to see the other databases. They can't access them
> without the appropriate permissions but they will be able to
> see them.
> -Sue
> On Mon, 28 Feb 2005 16:57:03 -0800, "Bob Melani" <Bob
> Melani@.discussions.microsoft.com> wrote:
>
>

Keep all servers registered and connected

I have 5 servers in my Server Mgmt Studio divided into 4 groups,

Local

Production

Test

Development

In Sql 2000 Enterprise Manager would put the little green arrow next to your db if it was connected, I noticed that Sql 2005 does do that but it wont allow me to keep all 5 servers connected and I need them all to stay connected.

If I look under Registered Servers it shows all 5 servers there but again only 2 show as connected even if I connect the other ones they dont stay connected. I have deleted the registerd ones and re added them and still no difference.

How can I keep all servers registered and connected and then have them show up on start-up?

Thanks!

Stokh

Launch Mgmt Studio

In the Registered Servers tool window right-click on the server and select Properties

On the Properties dialog select the Connection Properties tab

Set Connection time-out to 0 (the default is 15 seconds; 0 indicates no time-out).

|||I already did that and it still does the same thing, only shows 2 servers registered and doesnt keep the other 3 connected|||I suggest you file a bug using the Product Feedback Center: http://lab.msdn.microsoft.com/productfeedback/Default.aspx.|||I would also like to complain about this new 2005 behavior. As a DBA managing dozens of servers, it is ridiculous to keep jumping through the connection hoops in 2005 whenever I switch server context. It's hard to believe MS could screw up something so basic... and something that worked perfectly well in 2000.|||The best thing to do is search the Product Feedback Center for the issue and vote on it. In SP2 we are targeting many of the annoyances of the Mgmt Studio but we need your votes. The voting system let's us know what issues are causing pain for people and those tend to float to the top part of the list.