Nummerous Deadlocks

I receive numerous deadlocks while processing transactions with more than 2
users in SQLServer 2000 SP3. While these does not happen often, it does
happen often enough where it has become an annoyance. The error that the
users are getting are very similar or identical to the one below:
Microsoft SQL Server:1205[Microsoft][ODBC SQL Server Driver][SQL
Server]Transaction (Process ID 53) was deadlocked on lock resources with
another process and has been chosen as the deadlock
Does anybody have an idea in how to correct this issue.
Start here
http://www.sql-server-performance.com/deadlocks.asp
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Pete Ocasio" <pete.ocasio@.execupay.com> wrote in message
news:uIJgrapuEHA.2016@.TK2MSFTNGP15.phx.gbl...
>I receive numerous deadlocks while processing transactions with more than 2
> users in SQLServer 2000 SP3. While these does not happen often, it does
> happen often enough where it has become an annoyance. The error that the
> users are getting are very similar or identical to the one below:
> Microsoft SQL Server:1205[Microsoft][ODBC SQL Server Driver][SQL
> Server]Transaction (Process ID 53) was deadlocked on lock resources with
> another process and has been chosen as the deadlock
> Does anybody have an idea in how to correct this issue.
>
>

Nummerous Deadlocks

I receive numerous deadlocks while processing transactions with more than 2
users in SQLServer 2000 SP3. While these does not happen often, it does
happen often enough where it has become an annoyance. The error that the
users are getting are very similar or identical to the one below:
Microsoft SQL Server:1205[Microsoft][ODBC SQL Server Driver][SQL
Server]Transaction (Process ID 53) was deadlocked on lock resources with
another process and has been chosen as the deadlock
Does anybody have an idea in how to correct this issue.
Start here
http://www.sql-server-performance.com/deadlocks.asp
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Pete Ocasio" <pete.ocasio@.execupay.com> wrote in message
news:uIJgrapuEHA.2016@.TK2MSFTNGP15.phx.gbl...
>I receive numerous deadlocks while processing transactions with more than 2
> users in SQLServer 2000 SP3. While these does not happen often, it does
> happen often enough where it has become an annoyance. The error that the
> users are getting are very similar or identical to the one below:
> Microsoft SQL Server:1205[Microsoft][ODBC SQL Server Driver][SQL
> Server]Transaction (Process ID 53) was deadlocked on lock resources with
> another process and has been chosen as the deadlock
> Does anybody have an idea in how to correct this issue.
>
>

Nummerous Deadlocks

I receive numerous deadlocks while processing transactions with more than 2
users in SQLServer 2000 SP3. While these does not happen often, it does
happen often enough where it has become an annoyance. The error that the
users are getting are very similar or identical to the one below:
Microsoft SQL Server:1205[Microsoft][ODBC SQL Server Driver][SQL
Server]Transaction (Process ID 53) was deadlocked on lock resources with
another process and has been chosen as the deadlock
Does anybody have an idea in how to correct this issue.
Start here
http://www.sql-server-performance.com/deadlocks.asp
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Pete Ocasio" <pete.ocasio@.execupay.com> wrote in message
news:uIJgrapuEHA.2016@.TK2MSFTNGP15.phx.gbl...
>I receive numerous deadlocks while processing transactions with more than 2
> users in SQLServer 2000 SP3. While these does not happen often, it does
> happen often enough where it has become an annoyance. The error that the
> users are getting are very similar or identical to the one below:
> Microsoft SQL Server:1205[Microsoft][ODBC SQL Server Driver][SQL
> Server]Transaction (Process ID 53) was deadlocked on lock resources with
> another process and has been chosen as the deadlock
> Does anybody have an idea in how to correct this issue.
>
>
|||hi
you just check the join.if there is a join and condtion
is there then you have to applay where condtion for both
biswajit das

Nummerous Deadlocks

I receive numerous deadlocks while processing transactions with more than 2
users in SQLServer 2000 SP3. While these does not happen often, it does
happen often enough where it has become an annoyance. The error that the
users are getting are very similar or identical to the one below:
Microsoft SQL Server:1205[Microsoft][ODBC SQL Server Driver][SQL
Server]Transaction (Process ID 53) was deadlocked on lock resources with
another process and has been chosen as the deadlock
Does anybody have an idea in how to correct this issue.Start here
http://www.sql-server-performance.com/deadlocks.asp
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Pete Ocasio" <pete.ocasio@.execupay.com> wrote in message
news:uIJgrapuEHA.2016@.TK2MSFTNGP15.phx.gbl...
>I receive numerous deadlocks while processing transactions with more than 2
> users in SQLServer 2000 SP3. While these does not happen often, it does
> happen often enough where it has become an annoyance. The error that the
> users are getting are very similar or identical to the one below:
> Microsoft SQL Server:1205[Microsoft][ODBC SQL Server Driver][SQL
> Server]Transaction (Process ID 53) was deadlocked on lock resources with
> another process and has been chosen as the deadlock
> Does anybody have an idea in how to correct this issue.
>
>sql

Nummerous Deadlocks

I receive numerous deadlocks while processing transactions with more than 2
users in SQLServer 2000 SP3. While these does not happen often, it does
happen often enough where it has become an annoyance. The error that the
users are getting are very similar or identical to the one below:
Microsoft SQL Server:1205[Microsoft][ODBC SQL Server Driver][SQL
Server]Transaction (Process ID 53) was deadlocked on lock resources with
another process and has been chosen as the deadlock
Does anybody have an idea in how to correct this issue.
Start here
http://www.sql-server-performance.com/deadlocks.asp
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Pete Ocasio" <pete.ocasio@.execupay.com> wrote in message
news:uIJgrapuEHA.2016@.TK2MSFTNGP15.phx.gbl...
>I receive numerous deadlocks while processing transactions with more than 2
> users in SQLServer 2000 SP3. While these does not happen often, it does
> happen often enough where it has become an annoyance. The error that the
> users are getting are very similar or identical to the one below:
> Microsoft SQL Server:1205[Microsoft][ODBC SQL Server Driver][SQL
> Server]Transaction (Process ID 53) was deadlocked on lock resources with
> another process and has been chosen as the deadlock
> Does anybody have an idea in how to correct this issue.
>
>

Nummerous Deadlocks

I receive numerous deadlocks while processing transactions with more than 2
users in SQLServer 2000 SP3. While these does not happen often, it does
happen often enough where it has become an annoyance. The error that the
users are getting are very similar or identical to the one below:
Microsoft SQL Server:1205[Microsoft][ODBC SQL Server Driver][SQL
Server]Transaction (Process ID 53) was deadlocked on lock resources with
another process and has been chosen as the deadlock
Does anybody have an idea in how to correct this issue.Start here
http://www.sql-server-performance.com/deadlocks.asp
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Pete Ocasio" <pete.ocasio@.execupay.com> wrote in message
news:uIJgrapuEHA.2016@.TK2MSFTNGP15.phx.gbl...
>I receive numerous deadlocks while processing transactions with more than 2
> users in SQLServer 2000 SP3. While these does not happen often, it does
> happen often enough where it has become an annoyance. The error that the
> users are getting are very similar or identical to the one below:
> Microsoft SQL Server:1205[Microsoft][ODBC SQL Server Driver][S
QL
> Server]Transaction (Process ID 53) was deadlocked on lock resources with
> another process and has been chosen as the deadlock
> Does anybody have an idea in how to correct this issue.
>
>

Numerous windows open during debugging

Hello all

Im sure this is something minor that I am overlooking but for some reason when I go to debug my ssis package all these windows keep opening( callstack, watch1, watch2,watch3,breakpoints, etc) . I cant seem to find out how to disable these -- any suggestions? I dont remember actually setting these to run

thanks

kam

When in debug mode you can close the windows with the 'X' in the upper right hand corner of the window...|||

thanks

but last week I could run the ssis package and only have output, solution explorer, tools be the only visible windows now I get about 15 windows popping up

|||Sorry. Try closing them. While in debug mode you can go through the debug menu to chose windows.

Numerous SSIS and SSAS problems

First, in SSIS I cannot delete the data viewer from a connection between two tasks in the data flow without crashing BIDS. Even if I delete the connector or the tasks, BIDS crashes.

Second, in SSAS I accidentally deleted an attribute and the undo does not work in SSAS/BIDS. I put the attribute back and the dim won't deploy. It just runs forever.

Finally, in ProClarity my drill to detail is not displaying the fact rows I want. Plus the column headers are not easy to read. How can I make SSAS reference the correct data and corresponding column headers.

TJ

> Second, in SSAS I accidentally deleted an attribute and the undo does not work in SSAS/BIDS.

> I put the attribute back and the dim won't deploy. It just runs forever.

Indeed, undo is not yet implemented. After putting back the attribute, when you deploy, do you get an error or the deployment takes a long time ? If the deployment takes a long time, it's probably because the data is re-processed (although you put back the attribute, I believe its ID changed and will trigger a re-processing of the dimension). To stop re-processing on each deployment, right click on the project node -> Properties -> Deployment tab -> Processing Option.

Things to check/recover when putting back the attribute: was the attribute involved in hierarchies (was it source of levels in hierarchies ?) or attribute relationships or was used as granularity in measure groups ? (by default, the key attribute is the granularity)

Adrian Dumitrascu

Numerous Numeric Fields to 1 Numeric Field in New Table

Are there any routines out there that will automatically convert a table (A)
with numerous numeric fields to a new table (B) with just one numeric field.
Thus the number of records in the table (B) would be the number or records
in A mutliplied by the number of numeric fields.
Thanks in advance
Please include DDL with your questions so that we don't have to guess at what
your tables might look like.
Here's an example. Suppose you have a denormalized structure like this:
CREATE TABLE monthly_accounts (account_no INTEGER PRIMARY KEY, jan INTEGER
NULL, feb INTEGER NULL, mar INTEGER NULL, ...)
You can convert this to a more usable form as follows:
CREATE TABLE accounts (account_no INTEGER NOT NULL, dt DATETIME NOT NULL
CHECK (DAY(dt)=1), amount INTEGER NOT NULL, PRIMARY KEY (account_no, dt))
INSERT INTO accounts (account_no, dt, amount)
SELECT account_no, '20040101', jan
FROM monthly_accounts
WHERE jan IS NOT NULL
UNION ALL
SELECT account_no, '20040201', feb
FROM monthly_accounts
WHERE feb IS NOT NULL
UNION ALL
SELECT account_no, '20040301', mar
FROM monthly_accounts
WHERE mar IS NOT NULL
...
Notice that you will usually add at least one column to the key when you do
this.
David Portas
SQL Server MVP
|||Thanks David
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:E5E28C97-6FF6-4EBA-8152-33063A6C813C@.microsoft.com...
> Please include DDL with your questions so that we don't have to guess at
what
> your tables might look like.
> Here's an example. Suppose you have a denormalized structure like this:
> CREATE TABLE monthly_accounts (account_no INTEGER PRIMARY KEY, jan INTEGER
> NULL, feb INTEGER NULL, mar INTEGER NULL, ...)
> You can convert this to a more usable form as follows:
> CREATE TABLE accounts (account_no INTEGER NOT NULL, dt DATETIME NOT NULL
> CHECK (DAY(dt)=1), amount INTEGER NOT NULL, PRIMARY KEY (account_no, dt))
> INSERT INTO accounts (account_no, dt, amount)
> SELECT account_no, '20040101', jan
> FROM monthly_accounts
> WHERE jan IS NOT NULL
> UNION ALL
> SELECT account_no, '20040201', feb
> FROM monthly_accounts
> WHERE feb IS NOT NULL
> UNION ALL
> SELECT account_no, '20040301', mar
> FROM monthly_accounts
> WHERE mar IS NOT NULL
> ...
> Notice that you will usually add at least one column to the key when you
do
> this.
> --
> David Portas
> SQL Server MVP
> --
|||Hi
I occasionally concatenate field names from sysColumn rows (by object) in
order to save myself the typing (in sprocs, etc.). Don't see any reason why
you couldn't add the values in your case (if this is what you're trying to
do).
example:
CREATE PROCEDURE dbo.sp_tablecolumns
@.object_id varchar(100)
AS
DECLARE @.FldCat1 VARCHAR(8000)
SET @.FldCat1=''
SELECT @.FldCat1=@.FldCat1+(sysColumns.name + char(44))
FROM sysColumns with (NOLOCK)
WHERE id = object_id(@.object_id)
ORDER BY sysColumns.colorder
PRINT @.FldCat1
GO
usage: dbo.sp_tablecolumns 'MyTableNamethatIwantfieldsfor'
rob
"Joe" wrote:

> Are there any routines out there that will automatically convert a table (A)
> with numerous numeric fields to a new table (B) with just one numeric field.
> Thus the number of records in the table (B) would be the number or records
> in A mutliplied by the number of numeric fields.
> Thanks in advance
>
>
|||sorry, I get what you're saying now.
u can do the same kind of thing I mentioned in my 1st post. u'd have to find
the numeric columns from table A first, loop thru A (by row and then columns)
and do the insert (into B) in the loops.
u could write a generic routine starting with the code I posted.
rob
"RobKaratzas" wrote:
[vbcol=seagreen]
> Hi
> I occasionally concatenate field names from sysColumn rows (by object) in
> order to save myself the typing (in sprocs, etc.). Don't see any reason why
> you couldn't add the values in your case (if this is what you're trying to
> do).
> example:
> CREATE PROCEDURE dbo.sp_tablecolumns
> @.object_id varchar(100)
> AS
> DECLARE @.FldCat1 VARCHAR(8000)
> SET @.FldCat1=''
> SELECT @.FldCat1=@.FldCat1+(sysColumns.name + char(44))
> FROM sysColumns with (NOLOCK)
> WHERE id = object_id(@.object_id)
> ORDER BY sysColumns.colorder
> PRINT @.FldCat1
> GO
> usage: dbo.sp_tablecolumns 'MyTableNamethatIwantfieldsfor'
> rob
> "Joe" wrote:
|||> I occasionally concatenate field names from sysColumn rows (by
object) in
> order to save myself the typing (in sprocs, etc.).
In SQL2000 Query Analyzer can do that automatically for you. Just drag
the Columns node from the Object Browser into the editing window. In
7.0 and earlier you don't have Object Browser so the method you
described may be useful. Not sure what it has to do with Joe's question
though :-)
David Portas
SQL Server MVP
|||What I usually do to copy the column names is
Setting the Result to Text option and then do a
SELECT * FROM table WHERe 1 = 0
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1103200824.858097.15460@.z14g2000cwz.googlegro ups.com...
> object) in
> In SQL2000 Query Analyzer can do that automatically for you. Just drag
> the Columns node from the Object Browser into the editing window. In
> 7.0 and earlier you don't have Object Browser so the method you
> described may be useful. Not sure what it has to do with Joe's question
> though :-)
> --
> David Portas
> SQL Server MVP
> --
>
|||thanks Roji
I did misread the original post.
But in order to handle this problem with a generic solution, you're going to
require some means to programatically gather what these numerous columns are
for whatever Table A has.
Rob
"Roji. P. Thomas" wrote:

> What I usually do to copy the column names is
> Setting the Result to Text option and then do a
> SELECT * FROM table WHERe 1 = 0
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1103200824.858097.15460@.z14g2000cwz.googlegro ups.com...
>
>
sql

Numerous Numeric Fields to 1 Numeric Field in New Table

Are there any routines out there that will automatically convert a table (A)
with numerous numeric fields to a new table (B) with just one numeric field.
Thus the number of records in the table (B) would be the number or records
in A mutliplied by the number of numeric fields.
Thanks in advance
Please include DDL with your questions so that we don't have to guess at what
your tables might look like.
Here's an example. Suppose you have a denormalized structure like this:
CREATE TABLE monthly_accounts (account_no INTEGER PRIMARY KEY, jan INTEGER
NULL, feb INTEGER NULL, mar INTEGER NULL, ...)
You can convert this to a more usable form as follows:
CREATE TABLE accounts (account_no INTEGER NOT NULL, dt DATETIME NOT NULL
CHECK (DAY(dt)=1), amount INTEGER NOT NULL, PRIMARY KEY (account_no, dt))
INSERT INTO accounts (account_no, dt, amount)
SELECT account_no, '20040101', jan
FROM monthly_accounts
WHERE jan IS NOT NULL
UNION ALL
SELECT account_no, '20040201', feb
FROM monthly_accounts
WHERE feb IS NOT NULL
UNION ALL
SELECT account_no, '20040301', mar
FROM monthly_accounts
WHERE mar IS NOT NULL
...
Notice that you will usually add at least one column to the key when you do
this.
David Portas
SQL Server MVP
|||Thanks David
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:E5E28C97-6FF6-4EBA-8152-33063A6C813C@.microsoft.com...
> Please include DDL with your questions so that we don't have to guess at
what
> your tables might look like.
> Here's an example. Suppose you have a denormalized structure like this:
> CREATE TABLE monthly_accounts (account_no INTEGER PRIMARY KEY, jan INTEGER
> NULL, feb INTEGER NULL, mar INTEGER NULL, ...)
> You can convert this to a more usable form as follows:
> CREATE TABLE accounts (account_no INTEGER NOT NULL, dt DATETIME NOT NULL
> CHECK (DAY(dt)=1), amount INTEGER NOT NULL, PRIMARY KEY (account_no, dt))
> INSERT INTO accounts (account_no, dt, amount)
> SELECT account_no, '20040101', jan
> FROM monthly_accounts
> WHERE jan IS NOT NULL
> UNION ALL
> SELECT account_no, '20040201', feb
> FROM monthly_accounts
> WHERE feb IS NOT NULL
> UNION ALL
> SELECT account_no, '20040301', mar
> FROM monthly_accounts
> WHERE mar IS NOT NULL
> ...
> Notice that you will usually add at least one column to the key when you
do
> this.
> --
> David Portas
> SQL Server MVP
> --
|||Hi
I occasionally concatenate field names from sysColumn rows (by object) in
order to save myself the typing (in sprocs, etc.). Don't see any reason why
you couldn't add the values in your case (if this is what you're trying to
do).
example:
CREATE PROCEDURE dbo.sp_tablecolumns
@.object_id varchar(100)
AS
DECLARE @.FldCat1 VARCHAR(8000)
SET @.FldCat1=''
SELECT @.FldCat1=@.FldCat1+(sysColumns.name + char(44))
FROM sysColumns with (NOLOCK)
WHERE id = object_id(@.object_id)
ORDER BY sysColumns.colorder
PRINT @.FldCat1
GO
usage: dbo.sp_tablecolumns 'MyTableNamethatIwantfieldsfor'
rob
"Joe" wrote:

> Are there any routines out there that will automatically convert a table (A)
> with numerous numeric fields to a new table (B) with just one numeric field.
> Thus the number of records in the table (B) would be the number or records
> in A mutliplied by the number of numeric fields.
> Thanks in advance
>
>
|||sorry, I get what you're saying now.
u can do the same kind of thing I mentioned in my 1st post. u'd have to find
the numeric columns from table A first, loop thru A (by row and then columns)
and do the insert (into B) in the loops.
u could write a generic routine starting with the code I posted.
rob
"RobKaratzas" wrote:
[vbcol=seagreen]
> Hi
> I occasionally concatenate field names from sysColumn rows (by object) in
> order to save myself the typing (in sprocs, etc.). Don't see any reason why
> you couldn't add the values in your case (if this is what you're trying to
> do).
> example:
> CREATE PROCEDURE dbo.sp_tablecolumns
> @.object_id varchar(100)
> AS
> DECLARE @.FldCat1 VARCHAR(8000)
> SET @.FldCat1=''
> SELECT @.FldCat1=@.FldCat1+(sysColumns.name + char(44))
> FROM sysColumns with (NOLOCK)
> WHERE id = object_id(@.object_id)
> ORDER BY sysColumns.colorder
> PRINT @.FldCat1
> GO
> usage: dbo.sp_tablecolumns 'MyTableNamethatIwantfieldsfor'
> rob
> "Joe" wrote:
|||> I occasionally concatenate field names from sysColumn rows (by
object) in
> order to save myself the typing (in sprocs, etc.).
In SQL2000 Query Analyzer can do that automatically for you. Just drag
the Columns node from the Object Browser into the editing window. In
7.0 and earlier you don't have Object Browser so the method you
described may be useful. Not sure what it has to do with Joe's question
though :-)
David Portas
SQL Server MVP
|||What I usually do to copy the column names is
Setting the Result to Text option and then do a
SELECT * FROM table WHERe 1 = 0
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1103200824.858097.15460@.z14g2000cwz.googlegro ups.com...
> object) in
> In SQL2000 Query Analyzer can do that automatically for you. Just drag
> the Columns node from the Object Browser into the editing window. In
> 7.0 and earlier you don't have Object Browser so the method you
> described may be useful. Not sure what it has to do with Joe's question
> though :-)
> --
> David Portas
> SQL Server MVP
> --
>
|||thanks Roji
I did misread the original post.
But in order to handle this problem with a generic solution, you're going to
require some means to programatically gather what these numerous columns are
for whatever Table A has.
Rob
"Roji. P. Thomas" wrote:

> What I usually do to copy the column names is
> Setting the Result to Text option and then do a
> SELECT * FROM table WHERe 1 = 0
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1103200824.858097.15460@.z14g2000cwz.googlegro ups.com...
>
>

Numerous Numeric Fields to 1 Numeric Field in New Table

Are there any routines out there that will automatically convert a table (A)
with numerous numeric fields to a new table (B) with just one numeric field.
Thus the number of records in the table (B) would be the number or records
in A mutliplied by the number of numeric fields.
Thanks in advanceHi
I occasionally concatenate field names from sysColumn rows (by object) in
order to save myself the typing (in sprocs, etc.). Don't see any reason why
you couldn't add the values in your case (if this is what you're trying to
do).
example:
CREATE PROCEDURE dbo.sp_tablecolumns
@.object_id varchar(100)
AS
DECLARE @.FldCat1 VARCHAR(8000)
SET @.FldCat1=''
SELECT @.FldCat1=@.FldCat1+(sysColumns.name + char(44))
FROM sysColumns with (NOLOCK)
WHERE id = object_id(@.object_id)
ORDER BY sysColumns.colorder
PRINT @.FldCat1
GO
usage: dbo.sp_tablecolumns 'MyTableNamethatIwantfieldsfor'
rob
"Joe" wrote:

> Are there any routines out there that will automatically convert a table (
A)
> with numerous numeric fields to a new table (B) with just one numeric fiel
d.
> Thus the number of records in the table (B) would be the number or record
s
> in A mutliplied by the number of numeric fields.
> Thanks in advance
>
>|||sorry, I get what you're saying now.
u can do the same kind of thing I mentioned in my 1st post. u'd have to find
the numeric columns from table A first, loop thru A (by row and then columns
)
and do the insert (into B) in the loops.
u could write a generic routine starting with the code I posted.
rob
"RobKaratzas" wrote:
> Hi
> I occasionally concatenate field names from sysColumn rows (by object) in
> order to save myself the typing (in sprocs, etc.). Don't see any reason wh
y
> you couldn't add the values in your case (if this is what you're trying to
> do).
> example:
> CREATE PROCEDURE dbo.sp_tablecolumns
> @.object_id varchar(100)
> AS
> DECLARE @.FldCat1 VARCHAR(8000)
> SET @.FldCat1=''
> SELECT @.FldCat1=@.FldCat1+(sysColumns.name + char(44))
> FROM sysColumns with (NOLOCK)
> WHERE id = object_id(@.object_id)
> ORDER BY sysColumns.colorder
> PRINT @.FldCat1
> GO
> usage: dbo.sp_tablecolumns 'MyTableNamethatIwantfieldsfor'
> rob
> "Joe" wrote:
>|||> I occasionally concatenate field names from sysColumn rows (by
object) in
> order to save myself the typing (in sprocs, etc.).
In SQL2000 Query Analyzer can do that automatically for you. Just drag
the Columns node from the Object Browser into the editing window. In
7.0 and earlier you don't have Object Browser so the method you
described may be useful. Not sure what it has to do with Joe's question
though :-)
David Portas
SQL Server MVP
--|||What I usually do to copy the column names is
Setting the Result to Text option and then do a
SELECT * FROM table WHERe 1 = 0
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1103200824.858097.15460@.z14g2000cwz.googlegroups.com...
> object) in
> In SQL2000 Query Analyzer can do that automatically for you. Just drag
> the Columns node from the Object Browser into the editing window. In
> 7.0 and earlier you don't have Object Browser so the method you
> described may be useful. Not sure what it has to do with Joe's question
> though :-)
> --
> David Portas
> SQL Server MVP
> --
>|||thanks Roji
I did misread the original post.
But in order to handle this problem with a generic solution, you're going to
require some means to programatically gather what these numerous columns are
for whatever Table A has.
Rob
"Roji. P. Thomas" wrote:

> What I usually do to copy the column names is
> Setting the Result to Text option and then do a
> SELECT * FROM table WHERe 1 = 0
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1103200824.858097.15460@.z14g2000cwz.googlegroups.com...
>
>

Numerous Numeric Fields to 1 Numeric Field in New Table

Are there any routines out there that will automatically convert a table (A)
with numerous numeric fields to a new table (B) with just one numeric field.
Thus the number of records in the table (B) would be the number or records
in A mutliplied by the number of numeric fields.
Thanks in advance
Hi
I occasionally concatenate field names from sysColumn rows (by object) in
order to save myself the typing (in sprocs, etc.). Don't see any reason why
you couldn't add the values in your case (if this is what you're trying to
do).
example:
CREATE PROCEDURE dbo.sp_tablecolumns
@.object_id varchar(100)
AS
DECLARE @.FldCat1 VARCHAR(8000)
SET @.FldCat1=''
SELECT @.FldCat1=@.FldCat1+(sysColumns.name + char(44))
FROM sysColumns with (NOLOCK)
WHERE id = object_id(@.object_id)
ORDER BY sysColumns.colorder
PRINT @.FldCat1
GO
usage: dbo.sp_tablecolumns 'MyTableNamethatIwantfieldsfor'
rob
"Joe" wrote:

> Are there any routines out there that will automatically convert a table (A)
> with numerous numeric fields to a new table (B) with just one numeric field.
> Thus the number of records in the table (B) would be the number or records
> in A mutliplied by the number of numeric fields.
> Thanks in advance
>
>
|||sorry, I get what you're saying now.
u can do the same kind of thing I mentioned in my 1st post. u'd have to find
the numeric columns from table A first, loop thru A (by row and then columns)
and do the insert (into B) in the loops.
u could write a generic routine starting with the code I posted.
rob
"RobKaratzas" wrote:
[vbcol=seagreen]
> Hi
> I occasionally concatenate field names from sysColumn rows (by object) in
> order to save myself the typing (in sprocs, etc.). Don't see any reason why
> you couldn't add the values in your case (if this is what you're trying to
> do).
> example:
> CREATE PROCEDURE dbo.sp_tablecolumns
> @.object_id varchar(100)
> AS
> DECLARE @.FldCat1 VARCHAR(8000)
> SET @.FldCat1=''
> SELECT @.FldCat1=@.FldCat1+(sysColumns.name + char(44))
> FROM sysColumns with (NOLOCK)
> WHERE id = object_id(@.object_id)
> ORDER BY sysColumns.colorder
> PRINT @.FldCat1
> GO
> usage: dbo.sp_tablecolumns 'MyTableNamethatIwantfieldsfor'
> rob
> "Joe" wrote:
|||> I occasionally concatenate field names from sysColumn rows (by
object) in
> order to save myself the typing (in sprocs, etc.).
In SQL2000 Query Analyzer can do that automatically for you. Just drag
the Columns node from the Object Browser into the editing window. In
7.0 and earlier you don't have Object Browser so the method you
described may be useful. Not sure what it has to do with Joe's question
though :-)
David Portas
SQL Server MVP
|||What I usually do to copy the column names is
Setting the Result to Text option and then do a
SELECT * FROM table WHERe 1 = 0
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1103200824.858097.15460@.z14g2000cwz.googlegro ups.com...
> object) in
> In SQL2000 Query Analyzer can do that automatically for you. Just drag
> the Columns node from the Object Browser into the editing window. In
> 7.0 and earlier you don't have Object Browser so the method you
> described may be useful. Not sure what it has to do with Joe's question
> though :-)
> --
> David Portas
> SQL Server MVP
> --
>
|||thanks Roji
I did misread the original post.
But in order to handle this problem with a generic solution, you're going to
require some means to programatically gather what these numerous columns are
for whatever Table A has.
Rob
"Roji. P. Thomas" wrote:

> What I usually do to copy the column names is
> Setting the Result to Text option and then do a
> SELECT * FROM table WHERe 1 = 0
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1103200824.858097.15460@.z14g2000cwz.googlegro ups.com...
>
>

Numerous Login failed for user 'sa' entries in the event viewer

Hello,
We have a Windows 2000 Server and SQL Server 2000. Lately in my event
viewer - Application Log I see just thousands of entries that state
18456 :
Login failed for user 'sa'. It happens just at random times and as far
as I know doesn't affect the server or access to it in any way.
Has anyone experinced this before? My first guess would be that it
could be some program trying to use the local 'sa' userID to login in
to SQL.
Any suggestions?
Any help would be greatly appreciated.
AlekHi Alek,
I would suggest using SQL Profiler to trace the login attempts. If your
server is accessed via the Internet I would also look at your firewall logs.
Mike O.
"Alek" <alek@.widernet.org> wrote in message
news:7703dc03.0401151430.e66191f@.posting.google.com...

quote:

---

> Hello,
> We have a Windows 2000 Server and SQL Server 2000. Lately in my event
> viewer - Application Log I see just thousands of entries that state
> 18456 :
> Login failed for user 'sa'. It happens just at random times and as far
> as I know doesn't affect the server or access to it in any way.
> Has anyone experinced this before? My first guess would be that it
> could be some program trying to use the local 'sa' userID to login in
> to SQL.
> Any suggestions?
> Any help would be greatly appreciated.
>
> Alek

---

Numerous events - Starting up database 'Database_name'.

I have an SQL server with 2000 SE installed. I noticed in the SQL logs that
my server is constantly trying to start one database on numerous occasions. I
must have over 70 entries for this single database. Has anyone experienced
this problem before?
Nick
Seems ,that database's option AutoClose set ON
"Nick Pennisi" <NickPennisi@.discussions.microsoft.com> wrote in message
news:1BE05286-4681-4BA8-B9EE-9EA05E376BDB@.microsoft.com...
>I have an SQL server with 2000 SE installed. I noticed in the SQL logs that
> my server is constantly trying to start one database on numerous
> occasions. I
> must have over 70 entries for this single database. Has anyone experienced
> this problem before?
|||Turn off the autoclose database property. This also hurts performance.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Nick Pennisi" <NickPennisi@.discussions.microsoft.com> wrote in message
news:1BE05286-4681-4BA8-B9EE-9EA05E376BDB@.microsoft.com...
>I have an SQL server with 2000 SE installed. I noticed in the SQL logs that
> my server is constantly trying to start one database on numerous occasions. I
> must have over 70 entries for this single database. Has anyone experienced
> this problem before?
|||Thank you Tibor and Uri
"Tibor Karaszi" wrote:

> Turn off the autoclose database property. This also hurts performance.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Nick Pennisi" <NickPennisi@.discussions.microsoft.com> wrote in message
> news:1BE05286-4681-4BA8-B9EE-9EA05E376BDB@.microsoft.com...
>
sql

Numerous events - Starting up database 'Database_name'.

I have an SQL server with 2000 SE installed. I noticed in the SQL logs that
my server is constantly trying to start one database on numerous occasions.
I
must have over 70 entries for this single database. Has anyone experienced
this problem before'Nick
Seems ,that database's option AutoClose set ON
"Nick Pennisi" <NickPennisi@.discussions.microsoft.com> wrote in message
news:1BE05286-4681-4BA8-B9EE-9EA05E376BDB@.microsoft.com...
>I have an SQL server with 2000 SE installed. I noticed in the SQL logs that
> my server is constantly trying to start one database on numerous
> occasions. I
> must have over 70 entries for this single database. Has anyone experienced
> this problem before'|||Turn off the autoclose database property. This also hurts performance.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Nick Pennisi" <NickPennisi@.discussions.microsoft.com> wrote in message
news:1BE05286-4681-4BA8-B9EE-9EA05E376BDB@.microsoft.com...
>I have an SQL server with 2000 SE installed. I noticed in the SQL logs that
> my server is constantly trying to start one database on numerous occasions
. I
> must have over 70 entries for this single database. Has anyone experienced
> this problem before'|||Thank you Tibor and Uri
"Tibor Karaszi" wrote:

> Turn off the autoclose database property. This also hurts performance.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Nick Pennisi" <NickPennisi@.discussions.microsoft.com> wrote in message
> news:1BE05286-4681-4BA8-B9EE-9EA05E376BDB@.microsoft.com...
>

Numerous events - Starting up database 'Database_name'.

I have an SQL server with 2000 SE installed. I noticed in the SQL logs that
my server is constantly trying to start one database on numerous occasions. I
must have over 70 entries for this single database. Has anyone experienced
this problem before'Nick
Seems ,that database's option AutoClose set ON
"Nick Pennisi" <NickPennisi@.discussions.microsoft.com> wrote in message
news:1BE05286-4681-4BA8-B9EE-9EA05E376BDB@.microsoft.com...
>I have an SQL server with 2000 SE installed. I noticed in the SQL logs that
> my server is constantly trying to start one database on numerous
> occasions. I
> must have over 70 entries for this single database. Has anyone experienced
> this problem before'|||Turn off the autoclose database property. This also hurts performance.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Nick Pennisi" <NickPennisi@.discussions.microsoft.com> wrote in message
news:1BE05286-4681-4BA8-B9EE-9EA05E376BDB@.microsoft.com...
>I have an SQL server with 2000 SE installed. I noticed in the SQL logs that
> my server is constantly trying to start one database on numerous occasions. I
> must have over 70 entries for this single database. Has anyone experienced
> this problem before'|||Thank you Tibor and Uri
"Tibor Karaszi" wrote:
> Turn off the autoclose database property. This also hurts performance.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Nick Pennisi" <NickPennisi@.discussions.microsoft.com> wrote in message
> news:1BE05286-4681-4BA8-B9EE-9EA05E376BDB@.microsoft.com...
> >I have an SQL server with 2000 SE installed. I noticed in the SQL logs that
> > my server is constantly trying to start one database on numerous occasions. I
> > must have over 70 entries for this single database. Has anyone experienced
> > this problem before'
>

Numerous event 17137 in event log

Hello,
I am using SQL SERVER 2005 EXPRESS SP1 with an ASP.NET application, and I
get an event 17137 "Starting database MyDB" at each connection to my
database, which is a very frequent event.
The problem is that the application event log of the machine is full of this
event, with the consequence on old servers like Windows 2000 to get a message
saying the log is full and you have to empty it.
I would like to know how I can turn off the logging of SQL SERVER event 17137.
--
Olivier GIL
LAFON SAOliver GIL,
Is your MyDB in AUTO_CLOSE = ON mode? If so, you can turn this OFF, thus
keeping MyDB open by:
ALTER DATABASE MyDB SET AUTO_CLOSE OFF
That should get rid of the messages and make your accesses to MyDB run more
quickly.
RLF
"Olivier GIL" <ogil@.newsgroup.nospam> wrote in message
news:D985059D-D4CD-490B-8299-FB4924A4FF7F@.microsoft.com...
> Hello,
> I am using SQL SERVER 2005 EXPRESS SP1 with an ASP.NET application, and I
> get an event 17137 "Starting database MyDB" at each connection to my
> database, which is a very frequent event.
> The problem is that the application event log of the machine is full of
> this
> event, with the consequence on old servers like Windows 2000 to get a
> message
> saying the log is full and you have to empty it.
> I would like to know how I can turn off the logging of SQL SERVER event
> 17137.
> --
> Olivier GIL
> LAFON SA|||Hello,
AUTO_CLOSE OFF actually solves the problem.
It is surprising this option not being the default value for a CREATE
DATABASE command, knowing that the performance is better in that case.
--
Olivier GIL
LAFON SA
"Russell Fields" wrote:
> Oliver GIL,
> Is your MyDB in AUTO_CLOSE = ON mode? If so, you can turn this OFF, thus
> keeping MyDB open by:
> ALTER DATABASE MyDB SET AUTO_CLOSE OFF
> That should get rid of the messages and make your accesses to MyDB run more
> quickly.
> RLF
> "Olivier GIL" <ogil@.newsgroup.nospam> wrote in message
> news:D985059D-D4CD-490B-8299-FB4924A4FF7F@.microsoft.com...
> > Hello,
> >
> > I am using SQL SERVER 2005 EXPRESS SP1 with an ASP.NET application, and I
> > get an event 17137 "Starting database MyDB" at each connection to my
> > database, which is a very frequent event.
> >
> > The problem is that the application event log of the machine is full of
> > this
> > event, with the consequence on old servers like Windows 2000 to get a
> > message
> > saying the log is full and you have to empty it.
> >
> > I would like to know how I can turn off the logging of SQL SERVER event
> > 17137.
> >
> > --
> > Olivier GIL
> > LAFON SA
>
>|||Hi Olivier,
Regarding your concerns, you can find the following description regarding
AUTO_CLOSE OFF in SQL Server Books Online:
AUTO_CLOSE Default Value:
ON for all databases when using Microsoft SQL Server 2000 Desktop Engine or
SQL Server 2005 Express Edition, and OFF for all other editions, regardless
of operating system.
(REF: http://msdn2.microsoft.com/en-us/library/ms190249.aspx)
"The AUTO_CLOSE option is useful for desktop databases because it allows
database files to be managed as normal files. They can be moved, copied to
make backups, or even e-mailed to other users. The AUTO_CLOSE option should
not be used for databases accessed by an application that repeatedly makes
and breaks connections to SQL Server. The overhead of closing and reopening
the database between each connection will impair performance."
(REF: http://technet.microsoft.com/en-us/library/aa933082(SQL.80).aspx)
Please feel free to let us know if you have any other questions or
concerns. Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hello Olivier,
AUTO_CLOSE option if ON by default only for MSDE and Express Editions.
I suggest you to take a look at the following link for further info:
http://msdn2.microsoft.com/en-us/library/ms190249.aspx
--
Ekrem Ã?nsoy
"Olivier GIL" <ogil@.newsgroup.nospam> wrote in message
news:C20E229A-83E5-4D09-9B1F-F830E84EA438@.microsoft.com...
> Hello,
> AUTO_CLOSE OFF actually solves the problem.
>
> It is surprising this option not being the default value for a CREATE
> DATABASE command, knowing that the performance is better in that case.
> --
> Olivier GIL
> LAFON SA
>
> "Russell Fields" wrote:
>> Oliver GIL,
>> Is your MyDB in AUTO_CLOSE = ON mode? If so, you can turn this OFF,
>> thus
>> keeping MyDB open by:
>> ALTER DATABASE MyDB SET AUTO_CLOSE OFF
>> That should get rid of the messages and make your accesses to MyDB run
>> more
>> quickly.
>> RLF
>> "Olivier GIL" <ogil@.newsgroup.nospam> wrote in message
>> news:D985059D-D4CD-490B-8299-FB4924A4FF7F@.microsoft.com...
>> > Hello,
>> >
>> > I am using SQL SERVER 2005 EXPRESS SP1 with an ASP.NET application, and
>> > I
>> > get an event 17137 "Starting database MyDB" at each connection to my
>> > database, which is a very frequent event.
>> >
>> > The problem is that the application event log of the machine is full of
>> > this
>> > event, with the consequence on old servers like Windows 2000 to get a
>> > message
>> > saying the log is full and you have to empty it.
>> >
>> > I would like to know how I can turn off the logging of SQL SERVER event
>> > 17137.
>> >
>> > --
>> > Olivier GIL
>> > LAFON SA
>>

Numerous event 17137 in event log

Hello,
I am using SQL SERVER 2005 EXPRESS SP1 with an ASP.NET application, and I
get an event 17137 "Starting database MyDB" at each connection to my
database, which is a very frequent event.
The problem is that the application event log of the machine is full of this
event, with the consequence on old servers like Windows 2000 to get a messag
e
saying the log is full and you have to empty it.
I would like to know how I can turn off the logging of SQL SERVER event 1713
7.
Olivier GIL
LAFON SAOliver GIL,
Is your MyDB in AUTO_CLOSE = ON mode? If so, you can turn this OFF, thus
keeping MyDB open by:
ALTER DATABASE MyDB SET AUTO_CLOSE OFF
That should get rid of the messages and make your accesses to MyDB run more
quickly.
RLF
"Olivier GIL" <ogil@.newsgroup.nospam> wrote in message
news:D985059D-D4CD-490B-8299-FB4924A4FF7F@.microsoft.com...
> Hello,
> I am using SQL SERVER 2005 EXPRESS SP1 with an ASP.NET application, and I
> get an event 17137 "Starting database MyDB" at each connection to my
> database, which is a very frequent event.
> The problem is that the application event log of the machine is full of
> this
> event, with the consequence on old servers like Windows 2000 to get a
> message
> saying the log is full and you have to empty it.
> I would like to know how I can turn off the logging of SQL SERVER event
> 17137.
> --
> Olivier GIL
> LAFON SA|||Hello,
AUTO_CLOSE OFF actually solves the problem.
It is surprising this option not being the default value for a CREATE
DATABASE command, knowing that the performance is better in that case.
Olivier GIL
LAFON SA
"Russell Fields" wrote:

> Oliver GIL,
> Is your MyDB in AUTO_CLOSE = ON mode? If so, you can turn this OFF, thus
> keeping MyDB open by:
> ALTER DATABASE MyDB SET AUTO_CLOSE OFF
> That should get rid of the messages and make your accesses to MyDB run mor
e
> quickly.
> RLF
> "Olivier GIL" <ogil@.newsgroup.nospam> wrote in message
> news:D985059D-D4CD-490B-8299-FB4924A4FF7F@.microsoft.com...
>
>|||Hi Olivier,
Regarding your concerns, you can find the following description regarding
AUTO_CLOSE OFF in SQL Server Books Online:
AUTO_CLOSE Default Value:
ON for all databases when using Microsoft SQL Server 2000 Desktop Engine or
SQL Server 2005 Express Edition, and OFF for all other editions, regardless
of operating system.
(REF: http://msdn2.microsoft.com/en-us/library/ms190249.aspx)
"The AUTO_CLOSE option is useful for desktop databases because it allows
database files to be managed as normal files. They can be moved, copied to
make backups, or even e-mailed to other users. The AUTO_CLOSE option should
not be used for databases accessed by an application that repeatedly makes
and breaks connections to SQL Server. The overhead of closing and reopening
the database between each connection will impair performance."
(REF: http://technet.microsoft.com/en-us/library/aa933082(SQL.80).aspx)
Please feel free to let us know if you have any other questions or
concerns. Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hello Olivier,
AUTO_CLOSE option if ON by default only for MSDE and Express Editions.
I suggest you to take a look at the following link for further info:
http://msdn2.microsoft.com/en-us/library/ms190249.aspx
Ekrem ?nsoy
"Olivier GIL" <ogil@.newsgroup.nospam> wrote in message
news:C20E229A-83E5-4D09-9B1F-F830E84EA438@.microsoft.com...[vbcol=seagreen]
> Hello,
> AUTO_CLOSE OFF actually solves the problem.
>
> It is surprising this option not being the default value for a CREATE
> DATABASE command, knowing that the performance is better in that case.
> --
> Olivier GIL
> LAFON SA
>
> "Russell Fields" wrote:
>

Numerous event 17137 in event log

Hello,
I am using SQL SERVER 2005 EXPRESS SP1 with an ASP.NET application, and I
get an event 17137 "Starting database MyDB" at each connection to my
database, which is a very frequent event.
The problem is that the application event log of the machine is full of this
event, with the consequence on old servers like Windows 2000 to get a message
saying the log is full and you have to empty it.
I would like to know how I can turn off the logging of SQL SERVER event 17137.
Olivier GIL
LAFON SA
Oliver GIL,
Is your MyDB in AUTO_CLOSE = ON mode? If so, you can turn this OFF, thus
keeping MyDB open by:
ALTER DATABASE MyDB SET AUTO_CLOSE OFF
That should get rid of the messages and make your accesses to MyDB run more
quickly.
RLF
"Olivier GIL" <ogil@.newsgroup.nospam> wrote in message
news:D985059D-D4CD-490B-8299-FB4924A4FF7F@.microsoft.com...
> Hello,
> I am using SQL SERVER 2005 EXPRESS SP1 with an ASP.NET application, and I
> get an event 17137 "Starting database MyDB" at each connection to my
> database, which is a very frequent event.
> The problem is that the application event log of the machine is full of
> this
> event, with the consequence on old servers like Windows 2000 to get a
> message
> saying the log is full and you have to empty it.
> I would like to know how I can turn off the logging of SQL SERVER event
> 17137.
> --
> Olivier GIL
> LAFON SA
|||Hello,
AUTO_CLOSE OFF actually solves the problem.
It is surprising this option not being the default value for a CREATE
DATABASE command, knowing that the performance is better in that case.
Olivier GIL
LAFON SA
"Russell Fields" wrote:

> Oliver GIL,
> Is your MyDB in AUTO_CLOSE = ON mode? If so, you can turn this OFF, thus
> keeping MyDB open by:
> ALTER DATABASE MyDB SET AUTO_CLOSE OFF
> That should get rid of the messages and make your accesses to MyDB run more
> quickly.
> RLF
> "Olivier GIL" <ogil@.newsgroup.nospam> wrote in message
> news:D985059D-D4CD-490B-8299-FB4924A4FF7F@.microsoft.com...
>
>
|||Hi Olivier,
Regarding your concerns, you can find the following description regarding
AUTO_CLOSE OFF in SQL Server Books Online:
AUTO_CLOSE Default Value:
ON for all databases when using Microsoft SQL Server 2000 Desktop Engine or
SQL Server 2005 Express Edition, and OFF for all other editions, regardless
of operating system.
(REF: http://msdn2.microsoft.com/en-us/library/ms190249.aspx)
"The AUTO_CLOSE option is useful for desktop databases because it allows
database files to be managed as normal files. They can be moved, copied to
make backups, or even e-mailed to other users. The AUTO_CLOSE option should
not be used for databases accessed by an application that repeatedly makes
and breaks connections to SQL Server. The overhead of closing and reopening
the database between each connection will impair performance."
(REF: http://technet.microsoft.com/en-us/library/aa933082(SQL.80).aspx)
Please feel free to let us know if you have any other questions or
concerns. Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Hello Olivier,
AUTO_CLOSE option if ON by default only for MSDE and Express Editions.
I suggest you to take a look at the following link for further info:
http://msdn2.microsoft.com/en-us/library/ms190249.aspx
Ekrem ?nsoy
"Olivier GIL" <ogil@.newsgroup.nospam> wrote in message
news:C20E229A-83E5-4D09-9B1F-F830E84EA438@.microsoft.com...[vbcol=seagreen]
> Hello,
> AUTO_CLOSE OFF actually solves the problem.
>
> It is surprising this option not being the default value for a CREATE
> DATABASE command, knowing that the performance is better in that case.
> --
> Olivier GIL
> LAFON SA
>
> "Russell Fields" wrote:
sql