Showing posts with label operation. Show all posts
Showing posts with label operation. Show all posts

Monday, March 12, 2012

optimal database configure

hi,
I use sqlw2k sp3 server. I have configure 2xRAID1 with 4 ucp and 8GB
RAM. The first RAID1 for operation system and binary sql and the secend
RAID1 for data. I want to use one instance for two independent database.
Both database will have about 10-20GB data.
I am not sure where put the transation log (on which RAID the first or
the secend). This is my one question.
And my secend question concerning filegroup. Do you have any recommend
to the optimal of this?
rgs,
sylwester
--
Posted via http://dbforums.comIf you only have two RAID1 arrays you might be better off placing the =OS, binaries, data files on one array and the log files on the other =array.
a better setup would involve three raid sets
RAID set: OS + binaries + tempdb + system databases
RAID set: data files
RAID set: log files
The level of RAID that you choose should depend on your desired level of =performance and redundancy.
-- Keith
"skosci" <skoscielecky@.hotmail.com> wrote in message =news:3334411.1062764290@.dbforums.com...
> > hi,
> > > > I use sqlw2k sp3 server. I have configure 2xRAID1 with 4 ucp and 8GB
> RAM. The first RAID1 for operation system and binary sql and the =secend
> RAID1 for data. I want to use one instance for two independent =database.
> Both database will have about 10-20GB data.
> > > > I am not sure where put the transation log (on which RAID the first or
> the secend). This is my one question.
> > > > > > And my secend question concerning filegroup. Do you have any recommend
> to the optimal of this?
> > > > rgs,
> > sylwester
> > > --
> Posted via http://dbforums.com|||Hi Keith,
So I supposed like you. I cannot change configure on the level
RAID just now.
On the one RAID1 installed OS with 33GB.
The secend RAID1 has got 66GB.
Real size of both database is 12GB. In your first recommend I should
reinstall OS.
Tell me more what do you think if I setup like below:
first RAID set: OS+binaries+tempdb+systemdatabase and
secend RAID set: data files and log files.
How much it's worse from your first recommend?
Regards,
Sylwester
Posted via http://dbforums.com

Opteron performance with reindex operation

Hi,

We have been migrating some of our itanium servers to Opteron. With the last migration, we move our Siebel analytics server to a 4 way Opteron (4 dual core) with 64 GB RAM - SQL server Enterprise edition 32 bit, with AWE enabled. Every task that is executed against this new server (queries, nightly data ETL jobs etc) have shown a massive improvement in performance with the sole exception of a reindex job that creates a clustered index (using with drop eixsting option) on one the the large ETL tables (6 - 8 million rows, clustered index is 7 columes wide, 2 varchar (30), 3 numeric, 2 datetime). The reindex job runs after the ETL process that loads the data into the table. The ETL modifies approx 30% - 40% of the table data, so fragmentation is severe after the ETL load. The prior reindex method that we were using (db reindex rather than a complete rebuld took much longer to complete). Also, dropping the clustered index before the ETL is not supported (although it is outside the scope of this question, I thought I would mention it in case anyone was wondering why we didnt drop the index before loading the data).

I can run the same reindex job against the same data set on a dual zeon with 6 GB of RAM in 26 minutes... On the Opteron it takes almost 2 hours.....

Performance in every other aspect is phenomenal Any help would be appreciated.

Hi Mike,

Please let us know if this issue is now resolved.

It could be that the problem is IO related on your new server. but then again everything else runs fine.

Are you able to tell if any of the susbsystems (IO, memory, CPU) are under pressure during the re-index. What the re-index process is mostly waiting on?

Is it SQL 2000?

What is the recovery model you use during the re-index on both the boxes?

regards

Jag

|||

Are you running on SQL Server 2005 or SQL Server 2000. Especially if you have 64GB of RAM, you might want to consider using the 64-bit version instead of using 32-bit with AWE, since it lets you use your memory more effectively than 32-bit with AWE.

Does it look like you are seeing memory pressure or IO pressure during the clustered index rebuild? Do you have the "Sort in TempDB" option enabled for the index?

Perhaps the index rebuild is spilling over to disk (where the index is stored or in TempDB). Microsoft recommends having one TempDB file per processor (and dual core processors count as two).

Friday, March 9, 2012

Opteron performance with reindex operation

Hi,

We have been migrating some of our itanium servers to Opteron. With the last migration, we move our Siebel analytics server to a 4 way Opteron (4 dual core) with 64 GB RAM - SQL server Enterprise edition 32 bit, with AWE enabled. Every task that is executed against this new server (queries, nightly data ETL jobs etc) have shown a massive improvement in performance with the sole exception of a reindex job that creates a clustered index (using with drop eixsting option) on one the the large ETL tables (6 - 8 million rows, clustered index is 7 columes wide, 2 varchar (30), 3 numeric, 2 datetime). The reindex job runs after the ETL process that loads the data into the table. The ETL modifies approx 30% - 40% of the table data, so fragmentation is severe after the ETL load. The prior reindex method that we were using (db reindex rather than a complete rebuld took much longer to complete). Also, dropping the clustered index before the ETL is not supported (although it is outside the scope of this question, I thought I would mention it in case anyone was wondering why we didnt drop the index before loading the data).

I can run the same reindex job against the same data set on a dual zeon with 6 GB of RAM in 26 minutes... On the Opteron it takes almost 2 hours.....

Performance in every other aspect is phenomenal Any help would be appreciated.

Hi Mike,

Please let us know if this issue is now resolved.

It could be that the problem is IO related on your new server. but then again everything else runs fine.

Are you able to tell if any of the susbsystems (IO, memory, CPU) are under pressure during the re-index. What the re-index process is mostly waiting on?

Is it SQL 2000?

What is the recovery model you use during the re-index on both the boxes?

regards

Jag

|||

Are you running on SQL Server 2005 or SQL Server 2000. Especially if you have 64GB of RAM, you might want to consider using the 64-bit version instead of using 32-bit with AWE, since it lets you use your memory more effectively than 32-bit with AWE.

Does it look like you are seeing memory pressure or IO pressure during the clustered index rebuild? Do you have the "Sort in TempDB" option enabled for the index?

Perhaps the index rebuild is spilling over to disk (where the index is stored or in TempDB). Microsoft recommends having one TempDB file per processor (and dual core processors count as two).

Wednesday, March 7, 2012

Operations on Parameters

Hi
I attempted to perform a simple mathematical operation (like dividing 10 by
4) in a text box of a Table or body of a report and I end up with an integer
result (10\4 gives me 2.0). I used convert method, CDbl method and I still
get the same result. I formated the text box as N5, C2 and other formats but
still the same result.
The original problem is that the same thing happens with a report parameter
when I try to use it in calculations but by troubleshooting the problem I
realized that it happens in any text box.
What am I doing wrong?
Thanks
ShawnInteresting, I was going to suggest trying 10.0/4.0, but after adding
an expression "=10/4" to a text box I see the result is 2.5. Is you
operation in an expression or in custom code?
--
Scott
http://www.OdeToCode.com/blogs/scott/
On Fri, 12 Nov 2004 13:40:08 -0800, "Shawn Kralj"
<ShawnKralj@.discussions.microsoft.com> wrote:
>Hi
>I attempted to perform a simple mathematical operation (like dividing 10 by
>4) in a text box of a Table or body of a report and I end up with an integer
>result (10\4 gives me 2.0). I used convert method, CDbl method and I still
>get the same result. I formated the text box as N5, C2 and other formats but
>still the same result.
>The original problem is that the same thing happens with a report parameter
>when I try to use it in calculations but by troubleshooting the problem I
>realized that it happens in any text box.
>What am I doing wrong?
>Thanks
>Shawn|||>10\4 gives me 2.0
If you are really using \ then you are getting the correct result. The
'backslash' operator returns only the integer portion of the result.
If you're expecting 2.5 as the result, then you have to use / instead.
On Fri, 12 Nov 2004 13:40:08 -0800, "Shawn Kralj"
<ShawnKralj@.discussions.microsoft.com> wrote:
>Hi
>I attempted to perform a simple mathematical operation (like dividing 10 by
>4) in a text box of a Table or body of a report and I end up with an integer
>result (10\4 gives me 2.0). I used convert method, CDbl method and I still
>get the same result. I formated the text box as N5, C2 and other formats but
>still the same result.
>The original problem is that the same thing happens with a report parameter
>when I try to use it in calculations but by troubleshooting the problem I
>realized that it happens in any text box.
>What am I doing wrong?
>Thanks
>Shawn|||Oooh , good catch.
--
Scott
http://www.OdeToCode.com/blogs/scott/
On Sun, 14 Nov 2004 23:17:36 -0500, Dan Knight
<dknight@.REMOVEmullinixpackages.THIScom> wrote:
>>10\4 gives me 2.0
>If you are really using \ then you are getting the correct result. The
>'backslash' operator returns only the integer portion of the result.
>If you're expecting 2.5 as the result, then you have to use / instead.
>On Fri, 12 Nov 2004 13:40:08 -0800, "Shawn Kralj"
><ShawnKralj@.discussions.microsoft.com> wrote:
>>Hi
>>I attempted to perform a simple mathematical operation (like dividing 10 by
>>4) in a text box of a Table or body of a report and I end up with an integer
>>result (10\4 gives me 2.0). I used convert method, CDbl method and I still
>>get the same result. I formated the text box as N5, C2 and other formats but
>>still the same result.
>>The original problem is that the same thing happens with a report parameter
>>when I try to use it in calculations but by troubleshooting the problem I
>>realized that it happens in any text box.
>>What am I doing wrong?
>>Thanks
>>Shawn

Operation Timed Out

Hi All,

I am getting below error when I am selecting all members of the dimension. It is the largest dimension among others. My query is very simple. An example is given below :

With Member [MyDimension].[SelectedDimension] as 'AGGREGATE({[MyDimension].[Dimension].MEMBERS })'
Select NON EMPTY ( {[MEASURES].[Sales Value]} ) ON COLUMNS ,
NON EMPTY { Distinct( { [Customer].[Customer].Members
From myCube
Where ([MyDimension].[SelectedDimension])

This query is throwing below exceptions:

Source: Microsoft? OLE DB Provider for Analysis Services
Message: The operation requested failed due to timeout
Stack Trace:
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForMultpleResults(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OleDb.OleDbCommand.ExecuteReader()

1. What will be the reason for this exception?
2. This is not connection time out issue. I wanted confirm that since there is lot of data being fetched from olap database the system is not able to complete the operation. Hence the exception thrown.
3. This is dynamic query. Meaning query will be generated at runtime with selected measures and dimension and applying filters selected. Is there any other way of writing query so that performance is improved?

First, if you remove () from NON EMPTY ( {[MEASURES].[Sales Value]} ) . NON EMPTY is not a function, by definition.

Next, what are you trying to achieve by this, With Member [MyDimension].[SelectedDimension] as 'AGGREGATE({[MyDimension].[Dimension].MEMBERS })?

In SSAS 2005 you will have to point to [dimension].[hierarchy] not [dimension].[dimension]

With hierarchy i mean user hierarchies that you have defined.

Are you using SSAS 2000 or 2005?

Regards

Thomas Ivarsson

|||

Thanks for your reply Thomas.

I am using SSAS 2000.

With Member [MyDimension].[SelectedDimension] as 'AGGREGATE({[MyDimension].[Dimension].MEMBERS }) is used to make set of tuples to use it in Where clause.

I have web application to show reports. User will select dimensions he/she wants in report and applies filters on them.

Therefore Aggregate is used for filter purposes.

But when user selects dimension Customer (which is having huge records) the above mentioned exception is thrown. Even if filters are applied on it the excpetions occurs.

I wanted to know under what conditions the exception occurs.

Will it occurs if OLAP database is taking too much time to return all records for the selected dimension?

Regards,

Raghu Deshpande

Operation on View

Hi All,
It is possilble in some way to make operation in a view?
Example:
SELECT fieldA > fieldB AS TestCompare FROM myTable
ThanksFor example:
SELECT
CASE WHEN a > b THEN 'A' ELSE 'B' END AS test_compare
FROM myTable ;
David Portas
SQL Server MVP
--|||What would you want the comparsion fieldA > fieldB to return? There's no Boo
lean datatype in SQL
Server. How about returning a string, something like:
SELECT
CASE WHEN fieldA > fieldB THEN 'ColA_GT'
ELSE 'ColANotGT'
END AS TestCompare
FROM myTable
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Bonato Pierantonio" <pbonato@.interfree.it> wrote in message
news:Ob1Opt0wFHA.3788@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> It is possilble in some way to make operation in a view?
> Example:
> SELECT fieldA > fieldB AS TestCompare FROM myTable
> Thanks
>

Operation on DateTime

Hi there
Presently I have a table with a DateTime column as the following:
Col1
2003/12/01 00:05:00
2003/12/01 00:10:00
2003/12/01 00:15:00
2003/12/01 00:20:00
What I want to do is to separate the date and time into two columns,
so it becomes:
Col1 Col2 Col3
2003/12/01 00:05:00 2003/12/01 00:05:00
2003/12/01 00:10:00 2003/12/01 00:10:00
2003/12/01 00:15:00 2003/12/01 00:15:00
2003/12/01 00:20:00 2003/12/01 00:20:00
.
.
..
I am wondering which way is easier - making a program to do this (e.g. in
C#)
or using SQL queries. Thanks for any suggestions.
YuelinDepends where you need the values, you can use the RIGHT Function for that,
just use aa format that also represents the time format and Cut of the 8
right Characters from it, therell you be.
Or you go by:
Select Getdate(),
Convert(varchar(10),getdate(),111),Conve
rt(varchar(10),getdate(),114)
But IMHO formatting should be a client work.
--
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Yuelin Liang" <yuelinliang@.hotmail.com> schrieb im Newsbeitrag
news:uacpnCsWFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Hi there
> Presently I have a table with a DateTime column as the following:
> Col1
> 2003/12/01 00:05:00
> 2003/12/01 00:10:00
> 2003/12/01 00:15:00
> 2003/12/01 00:20:00
>
> What I want to do is to separate the date and time into two columns,
> so it becomes:
> Col1 Col2 Col3
> 2003/12/01 00:05:00 2003/12/01 00:05:00
> 2003/12/01 00:10:00 2003/12/01 00:10:00
> 2003/12/01 00:15:00 2003/12/01 00:15:00
> 2003/12/01 00:20:00 2003/12/01 00:20:00
> .
> .
> ..
> I am wondering which way is easier - making a program to do this (e.g. in
> C#)
> or using SQL queries. Thanks for any suggestions.
> Yuelin
>|||Hi
You can write the query this way
UPDATE <TABLE>
SET
col2 = convert(varchar(10), col1,102),
col3 = convert(varchar(8), col1,108)
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Yuelin Liang" wrote:

> Hi there
> Presently I have a table with a DateTime column as the following:
> Col1
> 2003/12/01 00:05:00
> 2003/12/01 00:10:00
> 2003/12/01 00:15:00
> 2003/12/01 00:20:00
>
> What I want to do is to separate the date and time into two columns,
> so it becomes:
> Col1 Col2 Col3
> 2003/12/01 00:05:00 2003/12/01 00:05:00
> 2003/12/01 00:10:00 2003/12/01 00:10:00
> 2003/12/01 00:15:00 2003/12/01 00:15:00
> 2003/12/01 00:20:00 2003/12/01 00:20:00
> ..
> ..
> ...
> I am wondering which way is easier - making a program to do this (e.g. in
> C#)
> or using SQL queries. Thanks for any suggestions.
> Yuelin
>
>|||update <table>
set
col2 = convert(varchar(10),col1,111),
col3 = convert(varchar(10),col1,108)
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Yuelin Liang" wrote:

> Hi there
> Presently I have a table with a DateTime column as the following:
> Col1
> 2003/12/01 00:05:00
> 2003/12/01 00:10:00
> 2003/12/01 00:15:00
> 2003/12/01 00:20:00
>
> What I want to do is to separate the date and time into two columns,
> so it becomes:
> Col1 Col2 Col3
> 2003/12/01 00:05:00 2003/12/01 00:05:00
> 2003/12/01 00:10:00 2003/12/01 00:10:00
> 2003/12/01 00:15:00 2003/12/01 00:15:00
> 2003/12/01 00:20:00 2003/12/01 00:20:00
> ..
> ..
> ...
> I am wondering which way is easier - making a program to do this (e.g. in
> C#)
> or using SQL queries. Thanks for any suggestions.
> Yuelin
>
>|||Thanks for the advice.
The original table I have doesn't have the column 2 and column 3, I need to
populate the two columns based on the datetime data in the column 1.
How to do this in T-SQL?
Thanks
Yuelin
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:24C0B6EE-354B-41EA-9AD6-7CB11AC3B52C@.microsoft.com...
> update <table>
> set
> col2 = convert(varchar(10),col1,111),
> col3 = convert(varchar(10),col1,108)
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Yuelin Liang" wrote:
>|||Then you either have to extend the table before updating then table, or you
insert the new data in a new table via:
Select
col1,
convert(varchar(10),col1,111) as col2,
convert(varchar(10),col1,108) as col3
Into NewTable
From Yourtable
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Yuelin Liang" <yuelinliang@.hotmail.com> schrieb im Newsbeitrag
news:OYPTy0sWFHA.1508@.tk2msftngp13.phx.gbl...
> Thanks for the advice.
> The original table I have doesn't have the column 2 and column 3, I need
> to
> populate the two columns based on the datetime data in the column 1.
> How to do this in T-SQL?
> Thanks
> Yuelin
>
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:24C0B6EE-354B-41EA-9AD6-7CB11AC3B52C@.microsoft.com...
>|||Hi,
Then u can use this way
SELECT col1, convert(varchar(10),col1,111) col2,
convert(varchar(10),col1,108) col3
FROM <TABLE>
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Yuelin Liang" wrote:

> Thanks for the advice.
> The original table I have doesn't have the column 2 and column 3, I need t
o
> populate the two columns based on the datetime data in the column 1.
> How to do this in T-SQL?
> Thanks
> Yuelin
>
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:24C0B6EE-354B-41EA-9AD6-7CB11AC3B52C@.microsoft.com...
>
>|||The problem with changing the structure of a physical table to meet some
presentation requirement is that different applications or users have
different requirements. For example, what if one user wants YYYY/MM/DD and
another wants MM/DD/YYYY? Also, the transformed representation of a value
is ususally not as storage efficient as the native data format. Rather than
modify your existing physical table, leave the date as datetime in the table
and implement the transformations as Views. The convert function can be used
to represent datetime as a string in various formats.
"Yuelin Liang" <yuelinliang@.hotmail.com> wrote in message
news:uacpnCsWFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Hi there
> Presently I have a table with a DateTime column as the following:
> Col1
> 2003/12/01 00:05:00
> 2003/12/01 00:10:00
> 2003/12/01 00:15:00
> 2003/12/01 00:20:00
>
> What I want to do is to separate the date and time into two columns,
> so it becomes:
> Col1 Col2 Col3
> 2003/12/01 00:05:00 2003/12/01 00:05:00
> 2003/12/01 00:10:00 2003/12/01 00:10:00
> 2003/12/01 00:15:00 2003/12/01 00:15:00
> 2003/12/01 00:20:00 2003/12/01 00:20:00
> .
> .
> ..
> I am wondering which way is easier - making a program to do this (e.g. in
> C#)
> or using SQL queries. Thanks for any suggestions.
> Yuelin
>

Operation Not Supported: LogonUser method in the Reporting Web Service

I'm getting a SoapException stating "This operation is not supported in
this edition of Reporting Services --> This operation is not supported
in this edition of Reporting Services" when I try to call
myReportService.LogonUser(myName, myPassword, myDomain)
I have verified that I am running the Enterprise edition of both Sql
Server + Reporting Services... is there anything else required in order
to use this function?
Here's the version info from my web service proxy:
ReportServerEdition "Enterprise"
ReportServerVersionNumber "Microsoft SQL Server Reporting Services
Version 8.00.743.00"
I get this error when I hit the service through both http and https. I
have an invalid cert on my development Report Server, which I'm
ignoring with System.Net.ServicePointManager.CertificatePolicy = New
CertificateIgnorer
where CertificateIgnorer is a custom class I wrote that always returns
true for any cert.
Is this error returned when ssl isn't working correctly?
Any help appreciated.Any ideas out there?

operation not allowed when object is closed...

I have this stored procedure on SQL 2005:

USE [Eventlog]

GO

/****** Object: StoredProcedure [dbo].[SelectCustomerSoftwareLicenses] Script Date: 08/07/2007 16:56:32 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[SelectCustomerSoftwareLicenses]

(

@.CustomerID char(8)

)

AS

BEGIN

DECLARE @.Temp TABLE (SoftwareID int)

INSERT INTO @.Temp

SELECT SoftwareID FROM Workstations

JOIN WorkstationSoftware ON Workstations.WorkstationID = WorkstationSoftware.WorkstationID

WHERE Workstations.CustomerID = @.CustomerID

UNION ALL

SELECT SoftwareID FROM Notebooks

JOIN NotebookSoftware ON Notebooks.NotebookID = NotebookSoftware.NotebookID

WHERE Notebooks.CustomerID = @.CustomerID

UNION ALL

SELECT SoftwareID FROM Machines

JOIN MachinesSoftware ON Machines.MachineID = MachinesSoftware.MachineID

WHERE Machines.CustomerID = @.CustomerID

DECLARE @.SoftwareInstalls TABLE (rowid int identity(1,1), SoftwareID int, Installs int)

INSERT INTO @.SoftwareInstalls

SELECT SoftwareID, COUNT(*) AS Installs FROM @.Temp

GROUP BY SoftwareID

DECLARE @.rowid int

SET @.rowid = (SELECT COUNT(*) FROM @.SoftwareInstalls)

WHILE @.rowid > 0 BEGIN

UPDATE SoftwareLicenses

SET Installs = (SELECT Installs FROM @.SoftwareInstalls WHERE rowid = @.rowid)

WHERE SoftwareID = (SELECT SoftwareID FROM @.SoftwareInstalls WHERE rowid = @.rowid)

DELETE FROM @.SoftwareInstalls

WHERE rowid = @.rowid

SET @.rowid = (SELECT COUNT(*) FROM @.SoftwareInstalls)

END

SELECT SoftwareLicenses.SoftwareID, Software.Software, SoftwareLicenses.Licenses, SoftwareLicenses.Installs FROM SoftwareLicenses

JOIN Software ON SoftwareLicenses.SoftwareID = Software.SoftwareID

WHERE SoftwareLicenses.CustomerID = @.CustomerID

ORDER BY Software.Software

END

When i execute it in a Query in SQL Studio it works fine, but when i execute it from an ASP page, i get following error:

ADODB.Recordset error '800a0e78'

Operation is not allowed when the object is closed.

/administration/licenses_edit.asp, line 56

Here the conection:

Set OBJdbConnection = Server.CreateObject("ADODB.Connection")
OBJdbConnection.ConnectionTimeout = Session("ConnectionTimeout")
OBJdbConnection.CommandTimeout = Session("CommandTimeout")
OBJdbConnection.Open Session("ConnectionString")
Set SQLStmt = Server.CreateObject("ADODB.Command")
Set RS = Server.CreateObject("ADODB.Recordset")

SQLStmt.CommandText = "EXECUTE SelectCustomerSoftwareLicenses '" & Request("CustomerID") & "'"
SQLStmt.CommandType = 1
Set SQLStmt.ActiveConnection = OBJdbConnection
RS.Open SQLStmt
RS.Close

Can anyone help please?

It this because of the variable tables?

If I recall correctly, an ADODB recordset is not a disconnected object.

You must do your actions between the OPEN and CLOSE.

Are you using VB v6, or Access?

(.NET allows the use of disconnected data using a dataset -NOT a recordset.)

|||

I'm using VB v6 and SQL Server 2005

I am going through my recordset between the open and close.

I think the problem lies in the scope of the variable table in stored procedure, because if i remove that whole chunk with the variable tables, there are no problems.

I have made the script work in totally different way, so i haven't solved the problem, just worked around it Smile

But it would still be nice to know if it is the scope of the varible tables that is being exceeded, and how, if possible to avoid this...?

|||

Just add "set nocount on" as the first statement in your sproc and your problem should go away.

Code Snippet

ALTER PROCEDURE [dbo].[SelectCustomerSoftwareLicenses]

(

@.CustomerID char(8)

)

AS

set nocount on

BEGIN

DECLARE @.Temp TABLE (SoftwareID int)

INSERT INTO @.Temp

SELECT SoftwareID FROM Workstations

JOIN WorkstationSoftware ON Workstations.WorkstationID = WorkstationSoftware.WorkstationID

WHERE Workstations.CustomerID = @.CustomerID

UNION ALL

SELECT SoftwareID FROM Notebooks

JOIN NotebookSoftware ON Notebooks.NotebookID = NotebookSoftware.NotebookID

WHERE Notebooks.CustomerID = @.CustomerID

UNION ALL

SELECT SoftwareID FROM Machines

JOIN MachinesSoftware ON Machines.MachineID = MachinesSoftware.MachineID

WHERE Machines.CustomerID = @.CustomerID

DECLARE @.SoftwareInstalls TABLE (rowid int identity(1,1), SoftwareID int, Installs int)

INSERT INTO @.SoftwareInstalls

SELECT SoftwareID, COUNT(*) AS Installs FROM @.Temp

GROUP BY SoftwareID

DECLARE @.rowid int

SET @.rowid = (SELECT COUNT(*) FROM @.SoftwareInstalls)

WHILE @.rowid > 0 BEGIN

UPDATE SoftwareLicenses

SET Installs = (SELECT Installs FROM @.SoftwareInstalls WHERE rowid = @.rowid)

WHERE SoftwareID = (SELECT SoftwareID FROM @.SoftwareInstalls WHERE rowid = @.rowid)

DELETE FROM @.SoftwareInstalls

WHERE rowid = @.rowid

SET @.rowid = (SELECT COUNT(*) FROM @.SoftwareInstalls)

END

SELECT SoftwareLicenses.SoftwareID, Software.Software, SoftwareLicenses.Licenses, SoftwareLicenses.Installs FROM SoftwareLicenses

JOIN Software ON SoftwareLicenses.SoftwareID = Software.SoftwareID

WHERE SoftwareLicenses.CustomerID = @.CustomerID

ORDER BY Software.Software

END

|||add the following code before "RS.Open SQLStmt"

"Set RS.ActiveConnection = OBJdbConnection"|||

Yes. "SET NOCOUNT ON" will fix your issue. The recordset will not get the resultset from the procedures. Instead of the resultset, the Insert statement's feedback will go.

You can add the "SET NOCOUNT ON" on your sp at first line or you can use the bellow command text,

SQLStmt.CommandText = "SET NOCOUNT ON;EXECUTE SelectCustomerSoftwareLicenses '" & Request("CustomerID") & "'"

Operation not allowed in this context

This error message is showing up when I execute the following code (called in a function):

Set rs = New ADODB.Recordset
rs.Open "SELECT RacePl FROM IndResults, PartData WHERE RaceID = " & lRaceID & " AND IndResults.PartID = PartData.PartID ORDER BY EventPl", conn, adOpenKeyset, adLockOptimistic
With rs
For j = 0 To .RecordCount - 1
rs(0).Value = j + 1
If j < .RecordCount - 1 Then .MoveNext
Next j
.Close
End With
Set rs = Nothing

Any idea what causes this to fire?what line is giving the error?

Set rs = New ADODB.Recordset
rs.Open "SELECT RacePl FROM IndResults, PartData WHERE RaceID = " & lRaceID & " AND IndResults.PartID = PartData.PartID ORDER BY EventPl", conn, adOpenKeyset, adLockOptimistic
For j = 0 To rs.RecordCount - 1
rs(0).Value = j + 1
If j < rs.RecordCount - 1 Then rs.MoveNext
Next j
rs.Close
Set rs = Nothing|||...rs.Close.|||I have no idea. But try this one:

Set rs = CreateObject("ADODB.Recordset")
rs.Open "SELECT RacePl FROM IndResults, PartData WHERE RaceID = " & lRaceID & " AND IndResults.PartID = PartData.PartID ORDER BY EventPl", conn, adOpenKeyset, adLockOptimistic

For j = 0 To rs.RecordCount - 1
rs(0).Value = j + 1
If j < rs.RecordCount - 1 Then rs.MoveNext
Next j
rs.Close
Set rs = Nothing|||...what I have noticed is that if I comment out the rs.Close it runs just fine. That's not to say that it is ok to not close out your recordsets but, since I am new to ADO (always worked in DAO before) I am a little uncertain as to when that is/isn't necessary.

Any ideas on that?|||I suggest that you close your connection to the database and set the recordset to nothig:

Set rs = CreateObject("ADODB.Recordset")
rs.Open "SELECT RacePl FROM IndResults, PartData WHERE RaceID = " & lRaceID & _
" AND IndResults.PartID = PartData.PartID ORDER BY EventPl", conn, adOpenKeyset, adLockOptimistic

For j = 0 To rs.RecordCount - 1
rs(0).Value = j + 1
If j < rs.RecordCount - 1 Then rs.MoveNext
Next j
conn.Close 'Close the connection
Set conn = Nothing
Set rs = Nothing|||open due to the frequent requests for data in my app. But I usually close the recordsets immediately. Comment please!|||Originally posted by Bobba Buoy
open due to the frequent requests for data in my app. But I usually close the recordsets immediately. Comment please!

What should I comment?|||You suggested that closing the connection was probably a good idea. I am noting that I usually leave the connection open during the life of the application since it is usually pretty active. I was wondering how bad of an idea you thought that was.|||Originally posted by Bobba Buoy
You suggested that closing the connection was probably a good idea. I am noting that I usually leave the connection open during the life of the application since it is usually pretty active. I was wondering how bad of an idea you thought that was.

If your application is used by many users at same time, it's a good idea to close the database connection. One open connection takes several kilobytes in memory.

Operation is not valid due to the current state of the object

Hello,

We are getting this error when a user clients any report. they can see the directories OK, but when we upload a new report the error still happens.

Error is "Operation is not valid due to the current state of the object."

Any ideas?

Thanks

Michael

Hi Michael,

I am also getting the same error whenever I tried to open a report through browser. Please let me know if you have resolved this issue and the fix for the same.

Thanks & Regards,

Sathya

|||

This was address in a previous forum by Brian Hartman, I have provided you with a link:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=212322&SiteID=1

Ham

Operation is not valid due to the current state of the object

Hello,

We are getting this error when a user clients any report. they can see the directories OK, but when we upload a new report the error still happens.

Error is "Operation is not valid due to the current state of the object."

Any ideas?

Thanks

Michael

Hi Michael,

I am also getting the same error whenever I tried to open a report through browser. Please let me know if you have resolved this issue and the fix for the same.

Thanks & Regards,

Sathya

|||

This was address in a previous forum by Brian Hartman, I have provided you with a link:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=212322&SiteID=1

Ham

Operation is not valid due to the current state of the object

One of our developers intermittently gets this error "Operation is not valid
due to the current state of the object" when exporting a report to Excel from
Preview in Designer. It seems to happen when he attempts to export a second
time after changing a parameter value in Preview. Any thoughts?I'm getting the same error, only it doesn't happen in Report Designer, only
when viewing it on the web using the ReportServer. Very strange... And only
with certain parameters.
"toolman_2000" wrote:
> One of our developers intermittently gets this error "Operation is not valid
> due to the current state of the object" when exporting a report to Excel from
> Preview in Designer. It seems to happen when he attempts to export a second
> time after changing a parameter value in Preview. Any thoughts?

Operation is not allowed while the object is closed (was "Hmmm")

never seen this before, but I keep getting an error > "Operationg is not allowed while the object is closed" I thought maybe I was getting this due to an error in my stored procedure, however the procedure runs fine. Could this be an error in my procedure that it's just not catching? Or is it more likely to be in my VB code? Anyone know in general why this happens? Thanks.Yes this is sounds like an ADO error in your VB code. You are either trying to run a procedure against a closed connection object or you trying to access the data from a recordset object that has not been set or open.

Operation is not allowed when the object is closed...

First, let me apologize for not knowing where to post this question...I hope
I've found the proper forum.
Up until three days ago, we've had a native-windows application (dot net)
connecting to a SQL (2000) back-end working with absolutely no problems for
the better part of five years now. Unfortunately, now, upon launching the
client-side applications, we are receiving this error message:
Operation is not allowed when the object is closed.
And, of course, we have no one on staff that knows SQL any more.
Is there any way I can troubleshoot this issue and restore service to my
clients?
Thanx.This looks to me like a .NET error instead of a SQL Server error.
Linchi
"Steven Sinclair" wrote:
> First, let me apologize for not knowing where to post this question...I hope
> I've found the proper forum.
> Up until three days ago, we've had a native-windows application (dot net)
> connecting to a SQL (2000) back-end working with absolutely no problems for
> the better part of five years now. Unfortunately, now, upon launching the
> client-side applications, we are receiving this error message:
> Operation is not allowed when the object is closed.
> And, of course, we have no one on staff that knows SQL any more.
> Is there any way I can troubleshoot this issue and restore service to my
> clients?
> Thanx.|||Okay.
Is there a way I can determine that for sure?
Thanx.
"Linchi Shea" wrote:
> This looks to me like a .NET error instead of a SQL Server error.
> Linchi
> "Steven Sinclair" wrote:
> > First, let me apologize for not knowing where to post this question...I hope
> > I've found the proper forum.
> >
> > Up until three days ago, we've had a native-windows application (dot net)
> > connecting to a SQL (2000) back-end working with absolutely no problems for
> > the better part of five years now. Unfortunately, now, upon launching the
> > client-side applications, we are receiving this error message:
> >
> > Operation is not allowed when the object is closed.
> >
> > And, of course, we have no one on staff that knows SQL any more.
> >
> > Is there any way I can troubleshoot this issue and restore service to my
> > clients?
> >
> > Thanx.|||Is there more to the error message than that?|||Unfortunately, no. Just that message window with an [OK] button.
I'm thinking now, since I can access the DB directly, that it is a .Net
issue, not a DB issue.
Thanx.
"cappjr@.gmail.com" wrote:
> Is there more to the error message than that?
>|||Just Google for this error and you'll see lots of threads about this error
in various forums.
It looks like this is an error that occurs because of wrong coding... It's
not directly about SQL Server, it's about the codes in your app.
--
Ekrem Ã?nsoy
"Steven Sinclair" <StevenSinclair@.discussions.microsoft.com> wrote in
message news:F7E013EA-4A2F-4FB8-A1DF-B1A71A9AEB0A@.microsoft.com...
> First, let me apologize for not knowing where to post this question...I
> hope
> I've found the proper forum.
> Up until three days ago, we've had a native-windows application (dot net)
> connecting to a SQL (2000) back-end working with absolutely no problems
> for
> the better part of five years now. Unfortunately, now, upon launching the
> client-side applications, we are receiving this error message:
> Operation is not allowed when the object is closed.
> And, of course, we have no one on staff that knows SQL any more.
> Is there any way I can troubleshoot this issue and restore service to my
> clients?
> Thanx.|||Yes, I did find a whole lot of information relating to this error through
Google. However, unfortunately, we don't have access to any of the source
code. All we have to deal with is the SQL server and EXE applications that
connect to the SQL server.
Thanx.
"Ekrem Ã?nsoy" wrote:
> Just Google for this error and you'll see lots of threads about this error
> in various forums.
> It looks like this is an error that occurs because of wrong coding... It's
> not directly about SQL Server, it's about the codes in your app.
> --
> Ekrem Ã?nsoy
>
> "Steven Sinclair" <StevenSinclair@.discussions.microsoft.com> wrote in
> message news:F7E013EA-4A2F-4FB8-A1DF-B1A71A9AEB0A@.microsoft.com...
> > First, let me apologize for not knowing where to post this question...I
> > hope
> > I've found the proper forum.
> >
> > Up until three days ago, we've had a native-windows application (dot net)
> > connecting to a SQL (2000) back-end working with absolutely no problems
> > for
> > the better part of five years now. Unfortunately, now, upon launching the
> > client-side applications, we are receiving this error message:
> >
> > Operation is not allowed when the object is closed.
> >
> > And, of course, we have no one on staff that knows SQL any more.
> >
> > Is there any way I can troubleshoot this issue and restore service to my
> > clients?
> >
> > Thanx.
>|||I'd start by adding SET NOCOUNT ON to the relevant procedures.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Steven Sinclair" <StevenSinclair@.discussions.microsoft.com> wrote in message
news:F7E013EA-4A2F-4FB8-A1DF-B1A71A9AEB0A@.microsoft.com...
> First, let me apologize for not knowing where to post this question...I hope
> I've found the proper forum.
> Up until three days ago, we've had a native-windows application (dot net)
> connecting to a SQL (2000) back-end working with absolutely no problems for
> the better part of five years now. Unfortunately, now, upon launching the
> client-side applications, we are receiving this error message:
> Operation is not allowed when the object is closed.
> And, of course, we have no one on staff that knows SQL any more.
> Is there any way I can troubleshoot this issue and restore service to my
> clients?
> Thanx.|||> Operation is not allowed when the object is closed.
As the others have mentioned, this is an application error rather than a SQL
error. This error is often raised when the application tries to use an
object for data retrieval without first checking to ensure it is in a valid
state. My guess is that the application expects at least one row of data
but either no rows were returned or no results were returned at all.
Perhaps a recent data change introduced this error. I suggest you reproduce
the error with a Profiler trace running and examine the last SQL statements
executed on the connection. That might provide a clue in lieu of debugging
the application code.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Steven Sinclair" <StevenSinclair@.discussions.microsoft.com> wrote in
message news:F7E013EA-4A2F-4FB8-A1DF-B1A71A9AEB0A@.microsoft.com...
> First, let me apologize for not knowing where to post this question...I
> hope
> I've found the proper forum.
> Up until three days ago, we've had a native-windows application (dot net)
> connecting to a SQL (2000) back-end working with absolutely no problems
> for
> the better part of five years now. Unfortunately, now, upon launching the
> client-side applications, we are receiving this error message:
> Operation is not allowed when the object is closed.
> And, of course, we have no one on staff that knows SQL any more.
> Is there any way I can troubleshoot this issue and restore service to my
> clients?
> Thanx.

Operation cancelled by user

Hello Guys,

I am currently experiencing a Sql error: operation cancelled by user.

I don't know where this error come from. Is it from ADO.NET or SQL Server 2000.

This error doesn't always occur. It only occurs when many users access the web site. I want to know who issue the error. Why issue the error.

Let me know if you have any ideas

Thanks in advanced

See if the following post helps

http://www.devnewsgroups.net/group/microsoft.public.dotnet.framework.adonet/topic10278.aspx

|||

Hi Raj Kasi,

Thanks for replying. I did not use sqlcommand.cancel(). Also, the error occurs before I call the finally.

Here is my code:

Dim sqlConnection As New sqlConnection(ConfigurationSettings.AppSettings(String.Format("db.{0}.SQLconnString", Env)))

Dim sbTable As New StringBuilder

Dim strCmpy As String

Dim tblCounters As String

strCmpy = Server.UrlDecode(Request.Cookies("Companies").Value)

Dim sqlCommand As New sqlCommand("sp_getCompanyInfo", sqlConnection)

Dim sqlDataReader As sqlDataReader

Dim myParam As SqlParameter

Dim errLog As New errReport

Try

sqlCommand.CommandType = CommandType.StoredProcedure

sqlConnection.Open()

myParam = sqlCommand.Parameters.Add("@.Companies", SqlDbType.VarChar, 50)

myParam.Value = strCmpy

myParam = sqlCommand.Parameters.Add("@.Class", SqlDbType.VarChar, 2)

myParam.Value = selClass

myParam = sqlCommand.Parameters.Add("@.BrchTmpl", SqlDbType.VarChar, 6)

myParam.Value = selBrchTmpl

sqlDataReader = sqlCommand.ExecuteReader()

Catch ex As Exception

errLog.reportError("Error=" & ex.ToString())

Finally

If Not sqlCommand Is Nothing Then

If Not sqlConnection Is Nothing AndAlso sqlConnection.State <> ConnectionState.Closed Then

sqlCommand.Close()

End If

sqlCommand.Dispose()

End If

If Not sqlConnection Is Nothing Then sqlConnection.Dispose()

End Try

The error occurs when I call sqlCommand.ExecuteReader(). This error only occurs when many users come to use my asp.net application. I use asp.net 1.1 on windows 2000 server framework 1.1 with IIS 6.0.

Thanks

Troy123

|||

Hi Troy123,

Could you see if hitting "stop" or navigating away from the page during operation reproduces this problem? You may need to specify how you treat people navigating away from the page to ensure that it does not kill the back end process explicitly (or maybe you want that to lower the load on your server).

Hope something comes of this,

John (MSFT)

|||

Hi Guys,

I have found the problem. In other pages, I put command.cancel. Now, I removed all command.cancel. It is ok now.

I use ADO.NET 1.1. I think ADO.NET 1.1 doesn't manage command.cancel well.

Regards,

Troy123

Operation canceled

Hi,
I have a stored procedure which can take quite a long time to process. this
SP is run from a sql server Job.
The SP loops through rows in a table and stores the primary key values of
the rows that where processed in a table varaible such that when the loop
finishes it executes an update statment on the main table to flag each
processed row as processed!
My concerns is that if the user stops the Job while the loop is doing its
work, then the updated rows will not be flagged.
is there a way to trap the cancel request and perform the update statement
before quitting the SP?
regards,Emmanuel
Deny the user symin privileges.
"Emmanuel" <emmanuel@.email.com> wrote in message
news:%23yLvVSgKFHA.1176@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a stored procedure which can take quite a long time to process.
this
> SP is run from a sql server Job.
> The SP loops through rows in a table and stores the primary key values of
> the rows that where processed in a table varaible such that when the loop
> finishes it executes an update statment on the main table to flag each
> processed row as processed!
> My concerns is that if the user stops the Job while the loop is doing its
> work, then the updated rows will not be flagged.
> is there a way to trap the cancel request and perform the update statement
> before quitting the SP?
> regards,
>

Saturday, February 25, 2012

Operating system error 10038., An operation was attempted on something that is not a socke

Hi there
I have a SQL Server 7.0 error log full of the following events
Source OD
Operating system error 10038., An operation was attempted on something that is not a socket
an
Error: 17825, Severity: 17, State:
and
Could not close ListenOn connection.
My system is Windows 2000 Server with SP
Microsoft SQL Server 7.00 - 7.00.1063 (Intel X86)
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4
Any help would be greatly appreciated
Regards
MattMatt,
Check if the article at
http://support.microsoft.com/default.aspx?scid=kb;en-us;109787 is helpful.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Matt" <anonymous@.discussions.microsoft.com> wrote in message
news:C1657807-D180-4A83-B5D2-603EDD6FB12A@.microsoft.com...
> Hi there,
> I have a SQL Server 7.0 error log full of the following events;
> Source ODS
> Operating system error 10038., An operation was attempted on something
that is not a socket.
> and
> Error: 17825, Severity: 17, State: 3
> and
> Could not close ListenOn connection..
> My system is Windows 2000 Server with SP4
> Microsoft SQL Server 7.00 - 7.00.1063 (Intel X86)
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> Any help would be greatly appreciated.
> Regards,
> Matt

Operating system error 10038., An operation was attempted on something that is not a s

Hi there,
I have a SQL Server 7.0 error log full of the following events;
Source ODS
Operating system error 10038., An operation was attempted on something that
is not a socket.
and
Error: 17825, Severity: 17, State: 3
and
Could not close ListenOn connection..
My system is Windows 2000 Server with SP4
Microsoft SQL Server 7.00 - 7.00.1063 (Intel X86)
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Any help would be greatly appreciated.
Regards,
MattMatt,
Check if the article at
http://support.microsoft.com/defaul...kb;en-us;109787 is helpful.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Matt" <anonymous@.discussions.microsoft.com> wrote in message
news:C1657807-D180-4A83-B5D2-603EDD6FB12A@.microsoft.com...
> Hi there,
> I have a SQL Server 7.0 error log full of the following events;
> Source ODS
> Operating system error 10038., An operation was attempted on something
that is not a socket.
> and
> Error: 17825, Severity: 17, State: 3
> and
> Could not close ListenOn connection..
> My system is Windows 2000 Server with SP4
> Microsoft SQL Server 7.00 - 7.00.1063 (Intel X86)
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> Any help would be greatly appreciated.
> Regards,
> Matt