Locking

I've got the following stored procedure I'm working on:
-- ============================================= -- Create procedure postCash
-- ============================================= USE Prototype
GO
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'postCash'
AND type = 'P')
DROP PROCEDURE postCash
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE postCash
@.bank_id varchar(50), @.gl_id int, @.gl_code varchar(50),
@.post_date varchar(10), @.amount money, @.comment varchar(50),
@.check_number varchar(50) = NULL
AS
SET NOCOUNT ON
DECLARE @.err_code int
DECLARE @.current_bal money
BEGIN TRANSACTION
SET @.current_bal = (SELECT TOP 1 NewBalance FROM Cash ORDER BY GLID DESC)
SET @.err_code = @.@.ERROR
IF @.err_code <> 0 GOTO AbortTransaction
INSERT INTO Cash (BankAccountID, GLID, GLCode, [Date], Amount, NewBalance,
Comment, CheckNumber)
VALUES (@.bank_id, @.gl_id, @.gl_code, @.post_date, @.amount, @.amount +
@.current_bal, @.comment, @.check_number);
SET @.err_code = @.@.ERROR
IF @.err_code <> 0 GOTO AbortTransaction
COMMIT TRANSACTION
RETURN 0
AbortTransaction:
ROLLBACK TRANSACTION
RETURN @.err_code
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I want to make sure that no one else can read the value of NewBalance until
the entire transaction completes successfully. I've read through various
locking topics in BOL but I don't quite understand the difference between
putting, say SERIALIZABLE after FROM in the SELECT or using SET TRANSACTION
ISOLATION LEVEL SERIALIZABLE. In the examples, the second statment is always
followed by GO. But if I use that in my SP then won't it wipe out all my
variable declarations? Will putting SERIALIZABLE after FROM produce the
result I'm looking for? Or, if I use SET TRANSACTION ISOLATION LEVEL
SERIALIZABLE do I then need to "turn it off" at the end somewhere?Ron,
As I understand it, you could use HOLDLOCK for the query (equivalent to
SERIALIZABLE). No one should be able to see the INSERTed record until the
tran commits.
HTH
Jerry
"Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in message
news:O8uWkNc1FHA.3892@.TK2MSFTNGP12.phx.gbl...
> I've got the following stored procedure I'm working on:
> -- =============================================> -- Create procedure postCash
> -- =============================================> USE Prototype
> GO
> IF EXISTS (SELECT name
> FROM sysobjects
> WHERE name = N'postCash'
> AND type = 'P')
> DROP PROCEDURE postCash
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE PROCEDURE postCash
> @.bank_id varchar(50), @.gl_id int, @.gl_code varchar(50),
> @.post_date varchar(10), @.amount money, @.comment varchar(50),
> @.check_number varchar(50) = NULL
> AS
> SET NOCOUNT ON
> DECLARE @.err_code int
> DECLARE @.current_bal money
> BEGIN TRANSACTION
> SET @.current_bal = (SELECT TOP 1 NewBalance FROM Cash ORDER BY GLID DESC)
> SET @.err_code = @.@.ERROR
> IF @.err_code <> 0 GOTO AbortTransaction
> INSERT INTO Cash (BankAccountID, GLID, GLCode, [Date], Amount,
> NewBalance,
> Comment, CheckNumber)
> VALUES (@.bank_id, @.gl_id, @.gl_code, @.post_date, @.amount, @.amount +
> @.current_bal, @.comment, @.check_number);
> SET @.err_code = @.@.ERROR
> IF @.err_code <> 0 GOTO AbortTransaction
> COMMIT TRANSACTION
> RETURN 0
> AbortTransaction:
> ROLLBACK TRANSACTION
> RETURN @.err_code
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> I want to make sure that no one else can read the value of NewBalance
> until
> the entire transaction completes successfully. I've read through various
> locking topics in BOL but I don't quite understand the difference between
> putting, say SERIALIZABLE after FROM in the SELECT or using SET
> TRANSACTION
> ISOLATION LEVEL SERIALIZABLE. In the examples, the second statment is
> always
> followed by GO. But if I use that in my SP then won't it wipe out all my
> variable declarations? Will putting SERIALIZABLE after FROM produce the
> result I'm looking for? Or, if I use SET TRANSACTION ISOLATION LEVEL
> SERIALIZABLE do I then need to "turn it off" at the end somewhere?
>|||On Thu, 20 Oct 2005 15:12:40 -0700, "Ron Hinds"
<__ron__dontspamme@.wedontlikespam_garageiq.com> wrote:
>I want to make sure that no one else can read the value of NewBalance until
>the entire transaction completes successfully.
How sure?
If you mean COMPLETELY SURE I don't think you can do it (without
heroic methods) in SQLServer, if reader allows dirty reads. I don't
believe even serializable shuts out readers. You could throw the
database into single-user mode, but that's pretty extreme.
On the other hand, simply doing what you've already done, putting the
insert into a transaction, will assure that readers in the default
"read committed" or higher levels of isolation, will pend behind your
transaction if they try to read it.
> I've read through various
>locking topics in BOL but I don't quite understand the difference between
>putting, say SERIALIZABLE after FROM in the SELECT or using SET TRANSACTION
>ISOLATION LEVEL SERIALIZABLE. In the examples, the second statment is always
>followed by GO. But if I use that in my SP then won't it wipe out all my
>variable declarations?
Don't put GO in SPs, everything will be fine.
>Will putting SERIALIZABLE after FROM produce the
>result I'm looking for? Or, if I use SET TRANSACTION ISOLATION LEVEL
>SERIALIZABLE do I then need to "turn it off" at the end somewhere?
It ends with the SP, but I think you don't really need it.
J.sql

Locking

I've got the following stored procedure I'm working on:
-- =============================================
-- Create procedure postCash
-- =============================================
USE Prototype
GO
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'postCash'
AND type = 'P')
DROP PROCEDURE postCash
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE postCash
@.bank_id varchar(50), @.gl_id int, @.gl_code varchar(50),
@.post_date varchar(10), @.amount money, @.comment varchar(50),
@.check_number varchar(50) = NULL
AS
SET NOCOUNT ON
DECLARE @.err_code int
DECLARE @.current_bal money
BEGIN TRANSACTION
SET @.current_bal = (SELECT TOP 1 NewBalance FROM Cash ORDER BY GLID DESC)
SET @.err_code = @.@.ERROR
IF @.err_code <> 0 GOTO AbortTransaction
INSERT INTO Cash (BankAccountID, GLID, GLCode, [Date], Amount, NewBalance,
Comment, CheckNumber)
VALUES (@.bank_id, @.gl_id, @.gl_code, @.post_date, @.amount, @.amount +
@.current_bal, @.comment, @.check_number);
SET @.err_code = @.@.ERROR
IF @.err_code <> 0 GOTO AbortTransaction
COMMIT TRANSACTION
RETURN 0
AbortTransaction:
ROLLBACK TRANSACTION
RETURN @.err_code
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I want to make sure that no one else can read the value of NewBalance until
the entire transaction completes successfully. I've read through various
locking topics in BOL but I don't quite understand the difference between
putting, say SERIALIZABLE after FROM in the SELECT or using SET TRANSACTION
ISOLATION LEVEL SERIALIZABLE. In the examples, the second statment is always
followed by GO. But if I use that in my SP then won't it wipe out all my
variable declarations? Will putting SERIALIZABLE after FROM produce the
result I'm looking for? Or, if I use SET TRANSACTION ISOLATION LEVEL
SERIALIZABLE do I then need to "turn it off" at the end somewhere?
Ron,
As I understand it, you could use HOLDLOCK for the query (equivalent to
SERIALIZABLE). No one should be able to see the INSERTed record until the
tran commits.
HTH
Jerry
"Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in message
news:O8uWkNc1FHA.3892@.TK2MSFTNGP12.phx.gbl...
> I've got the following stored procedure I'm working on:
> -- =============================================
> -- Create procedure postCash
> -- =============================================
> USE Prototype
> GO
> IF EXISTS (SELECT name
> FROM sysobjects
> WHERE name = N'postCash'
> AND type = 'P')
> DROP PROCEDURE postCash
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE PROCEDURE postCash
> @.bank_id varchar(50), @.gl_id int, @.gl_code varchar(50),
> @.post_date varchar(10), @.amount money, @.comment varchar(50),
> @.check_number varchar(50) = NULL
> AS
> SET NOCOUNT ON
> DECLARE @.err_code int
> DECLARE @.current_bal money
> BEGIN TRANSACTION
> SET @.current_bal = (SELECT TOP 1 NewBalance FROM Cash ORDER BY GLID DESC)
> SET @.err_code = @.@.ERROR
> IF @.err_code <> 0 GOTO AbortTransaction
> INSERT INTO Cash (BankAccountID, GLID, GLCode, [Date], Amount,
> NewBalance,
> Comment, CheckNumber)
> VALUES (@.bank_id, @.gl_id, @.gl_code, @.post_date, @.amount, @.amount +
> @.current_bal, @.comment, @.check_number);
> SET @.err_code = @.@.ERROR
> IF @.err_code <> 0 GOTO AbortTransaction
> COMMIT TRANSACTION
> RETURN 0
> AbortTransaction:
> ROLLBACK TRANSACTION
> RETURN @.err_code
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> I want to make sure that no one else can read the value of NewBalance
> until
> the entire transaction completes successfully. I've read through various
> locking topics in BOL but I don't quite understand the difference between
> putting, say SERIALIZABLE after FROM in the SELECT or using SET
> TRANSACTION
> ISOLATION LEVEL SERIALIZABLE. In the examples, the second statment is
> always
> followed by GO. But if I use that in my SP then won't it wipe out all my
> variable declarations? Will putting SERIALIZABLE after FROM produce the
> result I'm looking for? Or, if I use SET TRANSACTION ISOLATION LEVEL
> SERIALIZABLE do I then need to "turn it off" at the end somewhere?
>
|||On Thu, 20 Oct 2005 15:12:40 -0700, "Ron Hinds"
<__ron__dontspamme@.wedontlikespam_garageiq.com> wrote:
>I want to make sure that no one else can read the value of NewBalance until
>the entire transaction completes successfully.
How sure?
If you mean COMPLETELY SURE I don't think you can do it (without
heroic methods) in SQLServer, if reader allows dirty reads. I don't
believe even serializable shuts out readers. You could throw the
database into single-user mode, but that's pretty extreme.
On the other hand, simply doing what you've already done, putting the
insert into a transaction, will assure that readers in the default
"read committed" or higher levels of isolation, will pend behind your
transaction if they try to read it.

> I've read through various
>locking topics in BOL but I don't quite understand the difference between
>putting, say SERIALIZABLE after FROM in the SELECT or using SET TRANSACTION
>ISOLATION LEVEL SERIALIZABLE. In the examples, the second statment is always
>followed by GO. But if I use that in my SP then won't it wipe out all my
>variable declarations?
Don't put GO in SPs, everything will be fine.

>Will putting SERIALIZABLE after FROM produce the
>result I'm looking for? Or, if I use SET TRANSACTION ISOLATION LEVEL
>SERIALIZABLE do I then need to "turn it off" at the end somewhere?
It ends with the SP, but I think you don't really need it.
J.

Lock_timeout

Hello,

in EM (SQL7) i got the following errormessage, when i click on
current activity:

"Error: 1222 lock request time out period exceeded"

I run the sp_who /sp_who2, but nothing is blocked.

I set the lock_timeout th 5000, but it does not help.

Does anyone have an idea ?

best regards

Martinnetwork traffic is an idea
try to change for instance from tcp/ip to netbeui, or vice versa as the protocol you use to connect to the db server
see what happens.

also try to run a performance monitor to the db server.

check for cpu usage and latches.

if avg.latch wait time is high, ie. over 1000 and total latch wait time is also high i.e. over 100 for instance, then you probably experience sql server locks (watch for lock timeout /second)...

this can mean that the server is small, the app is poor, problem with your net h/w...

but this is some place to start with...

Cheers.|||Thanks for help Arounis,

network traffic is not possible, because i can log on to the server an got
the same error message.

For remember: I only get the error message, when i click on current activity in EM. No User told me that a problem still exits.

Checking avg.latch time and total latch time results:

520 constant for avg latch time

0 - 16 for total latch time.

You don't think that the problem occours about a "user - transaction" ?

Martin|||Try seeing if there is a locked resource which doesn't have a corresponding entry in sysprocesses

select *
from master..syslocks a
left join master..sysprocesses b on a.spid = b.spid
where b.spid is null

You shouldn't get any results.
Don't know what to tell you if you get any results.|||Thanks Wilso_s, but i get no results.

One more detail i detected, is that i can't access the
tempdb in EM. EM hangs up.

This short KB artikel Q308518 describes problems with
tempdb, but there is no workaround.

Maybe a restart will help ?

Thnx

Lock Manager does a dead lock search?

I see the following in the SQL (error) log and am curious as to why it shows
up (repeatedly). Server OS is Windows 2003 x64 Enterprise, SQL Server 2000
SP4. This server uses the Intel EMT processor. We have two other similarly
configured servers and I don't see these mesages on those servers (same
databases, etc.).
Starting deadlock search 34450
0
2005-10-12 09:49:51.31 spid4 Target Resource Owner:
0
2005-10-12 09:49:51.31 spid4 ResType:ExchangeId Stype:'AND' SPID:73
ECID:0 Ec0xBBA39370) Value:0x800ee67c
0
2005-10-12 09:49:51.31 spid4 Node:1 ResType:ExchangeId Stype:'AND'
SPID:73 ECID:0 Ec0xBBA39370) Value:0x800ee67c
0
2005-10-12 09:49:51.31 spid4
0
2005-10-12 09:49:51.31 spid4 End deadlock search 34450 ... a deadlock
was not found.
jl
Sounds like a trace flag has been enabled for that SQL instance.
Perhaps -T1205. Look in the startup parameters for SQL Server or use DBCC
TRACESTATUS(-1) to see all currently enabled trace flags.
"John L" <JohnL@.discussions.microsoft.com> wrote in message
news:A460B54F-CE69-4601-82EC-4EB2806FDF2C@.microsoft.com...
>I see the following in the SQL (error) log and am curious as to why it
>shows
> up (repeatedly). Server OS is Windows 2003 x64 Enterprise, SQL Server
> 2000
> SP4. This server uses the Intel EMT processor. We have two other
> similarly
> configured servers and I don't see these mesages on those servers (same
> databases, etc.).
>
> Starting deadlock search 34450
> 0
> 2005-10-12 09:49:51.31 spid4 Target Resource Owner:
>
> 0
> 2005-10-12 09:49:51.31 spid4 ResType:ExchangeId Stype:'AND' SPID:73
> ECID:0 Ec0xBBA39370) Value:0x800ee67c
> 0
> 2005-10-12 09:49:51.31 spid4 Node:1 ResType:ExchangeId Stype:'AND'
> SPID:73 ECID:0 Ec0xBBA39370) Value:0x800ee67c
> 0
> 2005-10-12 09:49:51.31 spid4
>
> 0
> 2005-10-12 09:49:51.31 spid4 End deadlock search 34450 ... a deadlock
> was not found.
> --
> jl
|||Lori,
We don't have the 1205 flag enabled.
jl
"Lori Clark" wrote:

> Sounds like a trace flag has been enabled for that SQL instance.
> Perhaps -T1205. Look in the startup parameters for SQL Server or use DBCC
> TRACESTATUS(-1) to see all currently enabled trace flags.
> "John L" <JohnL@.discussions.microsoft.com> wrote in message
> news:A460B54F-CE69-4601-82EC-4EB2806FDF2C@.microsoft.com...
>
>

Lock Manager does a dead lock search?

I see the following in the SQL (error) log and am curious as to why it shows
up (repeatedly). Server OS is Windows 2003 x64 Enterprise, SQL Server 2000
SP4. This server uses the Intel EMT processor. We have two other similarly
configured servers and I don't see these mesages on those servers (same
databases, etc.).
Starting deadlock search 34450
0
2005-10-12 09:49:51.31 spid4 Target Resource Owner:
0
2005-10-12 09:49:51.31 spid4 ResType:ExchangeId Stype:'AND' SPID:73
ECID:0 Ec:(0xBBA39370) Value:0x800ee67c
0
2005-10-12 09:49:51.31 spid4 Node:1 ResType:ExchangeId Stype:'AND'
SPID:73 ECID:0 Ec:(0xBBA39370) Value:0x800ee67c
0
2005-10-12 09:49:51.31 spid4
0
2005-10-12 09:49:51.31 spid4 End deadlock search 34450 ... a deadlock
was not found.
--
jlSounds like a trace flag has been enabled for that SQL instance.
Perhaps -T1205. Look in the startup parameters for SQL Server or use DBCC
TRACESTATUS(-1) to see all currently enabled trace flags.
"John L" <JohnL@.discussions.microsoft.com> wrote in message
news:A460B54F-CE69-4601-82EC-4EB2806FDF2C@.microsoft.com...
>I see the following in the SQL (error) log and am curious as to why it
>shows
> up (repeatedly). Server OS is Windows 2003 x64 Enterprise, SQL Server
> 2000
> SP4. This server uses the Intel EMT processor. We have two other
> similarly
> configured servers and I don't see these mesages on those servers (same
> databases, etc.).
>
> Starting deadlock search 34450
> 0
> 2005-10-12 09:49:51.31 spid4 Target Resource Owner:
>
> 0
> 2005-10-12 09:49:51.31 spid4 ResType:ExchangeId Stype:'AND' SPID:73
> ECID:0 Ec:(0xBBA39370) Value:0x800ee67c
> 0
> 2005-10-12 09:49:51.31 spid4 Node:1 ResType:ExchangeId Stype:'AND'
> SPID:73 ECID:0 Ec:(0xBBA39370) Value:0x800ee67c
> 0
> 2005-10-12 09:49:51.31 spid4
>
> 0
> 2005-10-12 09:49:51.31 spid4 End deadlock search 34450 ... a deadlock
> was not found.
> --
> jl|||Lori,
We don't have the 1205 flag enabled.
--
jl
"Lori Clark" wrote:
> Sounds like a trace flag has been enabled for that SQL instance.
> Perhaps -T1205. Look in the startup parameters for SQL Server or use DBCC
> TRACESTATUS(-1) to see all currently enabled trace flags.
> "John L" <JohnL@.discussions.microsoft.com> wrote in message
> news:A460B54F-CE69-4601-82EC-4EB2806FDF2C@.microsoft.com...
> >I see the following in the SQL (error) log and am curious as to why it
> >shows
> > up (repeatedly). Server OS is Windows 2003 x64 Enterprise, SQL Server
> > 2000
> > SP4. This server uses the Intel EMT processor. We have two other
> > similarly
> > configured servers and I don't see these mesages on those servers (same
> > databases, etc.).
> >
> >
> > Starting deadlock search 34450
> >
> > 0
> > 2005-10-12 09:49:51.31 spid4 Target Resource Owner:
> >
> >
> > 0
> > 2005-10-12 09:49:51.31 spid4 ResType:ExchangeId Stype:'AND' SPID:73
> > ECID:0 Ec:(0xBBA39370) Value:0x800ee67c
> >
> > 0
> > 2005-10-12 09:49:51.31 spid4 Node:1 ResType:ExchangeId Stype:'AND'
> > SPID:73 ECID:0 Ec:(0xBBA39370) Value:0x800ee67c
> >
> > 0
> > 2005-10-12 09:49:51.31 spid4
> >
> >
> > 0
> > 2005-10-12 09:49:51.31 spid4 End deadlock search 34450 ... a deadlock
> > was not found.
> >
> > --
> > jl
>
>

Lock Manager does a dead lock search?

I see the following in the SQL (error) log and am curious as to why it shows
up (repeatedly). Server OS is Windows 2003 x64 Enterprise, SQL Server 2000
SP4. This server uses the Intel EMT processor. We have two other similarly
configured servers and I don't see these mesages on those servers (same
databases, etc.).
Starting deadlock search 34450
0
2005-10-12 09:49:51.31 spid4 Target Resource Owner:
0
2005-10-12 09:49:51.31 spid4 ResType:ExchangeId Stype:'AND' SPID:73
ECID:0 Ec0xBBA39370) Value:0x800ee67c
0
2005-10-12 09:49:51.31 spid4 Node:1 ResType:ExchangeId Stype:'AND'
SPID:73 ECID:0 Ec0xBBA39370) Value:0x800ee67c
0
2005-10-12 09:49:51.31 spid4
0
2005-10-12 09:49:51.31 spid4 End deadlock search 34450 ... a deadlock
was not found.
jlSounds like a trace flag has been enabled for that SQL instance.
Perhaps -T1205. Look in the startup parameters for SQL Server or use DBCC
TRACESTATUS(-1) to see all currently enabled trace flags.
"John L" <JohnL@.discussions.microsoft.com> wrote in message
news:A460B54F-CE69-4601-82EC-4EB2806FDF2C@.microsoft.com...
>I see the following in the SQL (error) log and am curious as to why it
>shows
> up (repeatedly). Server OS is Windows 2003 x64 Enterprise, SQL Server
> 2000
> SP4. This server uses the Intel EMT processor. We have two other
> similarly
> configured servers and I don't see these mesages on those servers (same
> databases, etc.).
>
> Starting deadlock search 34450
> 0
> 2005-10-12 09:49:51.31 spid4 Target Resource Owner:
>
> 0
> 2005-10-12 09:49:51.31 spid4 ResType:ExchangeId Stype:'AND' SPID:73
> ECID:0 Ec0xBBA39370) Value:0x800ee67c
> 0
> 2005-10-12 09:49:51.31 spid4 Node:1 ResType:ExchangeId Stype:'AND'
> SPID:73 ECID:0 Ec0xBBA39370) Value:0x800ee67c
> 0
> 2005-10-12 09:49:51.31 spid4
>
> 0
> 2005-10-12 09:49:51.31 spid4 End deadlock search 34450 ... a deadlock
> was not found.
> --
> jl|||Lori,
We don't have the 1205 flag enabled.
jl
"Lori Clark" wrote:

> Sounds like a trace flag has been enabled for that SQL instance.
> Perhaps -T1205. Look in the startup parameters for SQL Server or use DBC
C
> TRACESTATUS(-1) to see all currently enabled trace flags.
> "John L" <JohnL@.discussions.microsoft.com> wrote in message
> news:A460B54F-CE69-4601-82EC-4EB2806FDF2C@.microsoft.com...
>
>

Lock Information

Looking under Locks/Object I noticed the following items:
master.dbo.spt_values
tempdb.dbo.##lockinfo75
The lock mode is X (Exclusive Insert/delete or update)
Process details show 'sa' as the user
The Databases are all working okay (with 80+ users)
Is there any way of checking what this is (presumably a temp table was created as part of the process) ?
Also what is spt_values used for ?
thanks
Gerry
This is a temp table used by one of the stored procedures EM calls when you
look up information such as locks. It's nothing to worry about.

Andrew J. Kelly
SQL Server MVP
"Gerry" <anonymous@.discussions.microsoft.com> wrote in message
news:6F9059D0-29EF-4B05-B8FF-92388071AC37@.microsoft.com...
> Looking under Locks/Object I noticed the following items:
> master.dbo.spt_values
> tempdb.dbo.##lockinfo75
> The lock mode is X (Exclusive Insert/delete or update)
> Process details show 'sa' as the user
> The Databases are all working okay (with 80+ users)
> Is there any way of checking what this is (presumably a temp table was
created as part of the process) ?
> Also what is spt_values used for ?
> thanks
> Gerry

Lock Information

Looking under Locks/Object I noticed the following items:
master.dbo.spt_values
tempdb.dbo.##lockinfo75
The lock mode is X (Exclusive Insert/delete or update)
Process details show 'sa' as the user
The Databases are all working okay (with 80+ users)
Is there any way of checking what this is (presumably a temp table was creat
ed as part of the process) ?
Also what is spt_values used for ?
thanks
GerryThis is a temp table used by one of the stored procedures EM calls when you
look up information such as locks. It's nothing to worry about.
Andrew J. Kelly
SQL Server MVP
"Gerry" <anonymous@.discussions.microsoft.com> wrote in message
news:6F9059D0-29EF-4B05-B8FF-92388071AC37@.microsoft.com...
> Looking under Locks/Object I noticed the following items:
> master.dbo.spt_values
> tempdb.dbo.##lockinfo75
> The lock mode is X (Exclusive Insert/delete or update)
> Process details show 'sa' as the user
> The Databases are all working okay (with 80+ users)
> Is there any way of checking what this is (presumably a temp table was
created as part of the process) ?
> Also what is spt_values used for ?
> thanks
> Gerry

Lock and update problem

Hello,
I use sql server 2000.
I want to create the following stored procedure:
I have a table with serveral field including the field "isqueued"
I want to select one record (select top 1) from the table and immediatly
update the iqueued field to "true" while locking the record.
This to make sure that no other thread is going the read the same record and
use it.
Any ideas on how to do this?
Thanks in advance for the helpGM
BEGIN TRAN
SELECT <> FROM Table WITH (UPDLOCK,HOLDLOCK) WHERE blbalabl;
UPDATE Table_Other SET blblb;
COMMIT
Note : There is not true datatype in SQL Server . You probably menat to
update this column to 1 OR 0 did not you?
"GM" <invalid@.invalid.com> wrote in message
news:%239$Li0kBGHA.1372@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I use sql server 2000.
> I want to create the following stored procedure:
> I have a table with serveral field including the field "isqueued"
> I want to select one record (select top 1) from the table and immediatly
> update the iqueued field to "true" while locking the record.
> This to make sure that no other thread is going the read the same record
> and use it.
> Any ideas on how to do this?
> Thanks in advance for the help
>|||> Note : There is not true datatype in SQL Server . You probably menat to
> update this column to 1 OR 0 did not you?
>
Indeed, 0 or 1
I'll give this a try|||have you considered using a message queue?

Location for Temporary File

I have created the following table in Northwind Database:
CREATE TABLE #dts(c1 char(1), dt datetime)
INSERT INTO #dts (c1, dt) VALUES('a', '20040305 09:12:59')
INSERT INTO #dts (c1, dt) VALUES('b', '20040305 16:03:12')
INSERT INTO #dts (c1, dt) VALUES('c', '20040306 00:00:00')
INSERT INTO #dts (c1, dt) VALUES('d', '20040306 02:41:32')
INSERT INTO #dts (c1, dt) VALUES('e', '20040315 11:45:17')
INSERT INTO #dts (c1, dt) VALUES('f', '20040412 09:12:59')
INSERT INTO #dts (c1, dt) VALUES('g', '20040523 11:43:25')
However, when I search in both Northwind and Tempdb
databases, I am not able to find it. I would like to know
where is the Temporary Table being created ?
Thanks
Hi,
All the # (temp) tables and ## (global temp) table will be created in TEMPDB
database. But for temp tables the name will be
created with a unique prefix. This is because temp tables are created every
session and each session a unqie table needs to be created.
How to see this..
Select name from tempdb..sysobjects where name like 'dts%'
Thanks
Hari
SQL Server MVP
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:0a0f01c56d8c$8f52e270$a401280a@.phx.gbl...
>I have created the following table in Northwind Database:
> CREATE TABLE #dts(c1 char(1), dt datetime)
> INSERT INTO #dts (c1, dt) VALUES('a', '20040305 09:12:59')
> INSERT INTO #dts (c1, dt) VALUES('b', '20040305 16:03:12')
> INSERT INTO #dts (c1, dt) VALUES('c', '20040306 00:00:00')
> INSERT INTO #dts (c1, dt) VALUES('d', '20040306 02:41:32')
> INSERT INTO #dts (c1, dt) VALUES('e', '20040315 11:45:17')
> INSERT INTO #dts (c1, dt) VALUES('f', '20040412 09:12:59')
> INSERT INTO #dts (c1, dt) VALUES('g', '20040523 11:43:25')
> However, when I search in both Northwind and Tempdb
> databases, I am not able to find it. I would like to know
> where is the Temporary Table being created ?
> Thanks
|||In TempDB
Run the query after your creation
IF OBJECT_ID('tempdb..#dts') IS NOT NULL
PRINT 'TempDB'
ELSE
PRINT 'No'
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:0a0f01c56d8c$8f52e270$a401280a@.phx.gbl...
> I have created the following table in Northwind Database:
> CREATE TABLE #dts(c1 char(1), dt datetime)
> INSERT INTO #dts (c1, dt) VALUES('a', '20040305 09:12:59')
> INSERT INTO #dts (c1, dt) VALUES('b', '20040305 16:03:12')
> INSERT INTO #dts (c1, dt) VALUES('c', '20040306 00:00:00')
> INSERT INTO #dts (c1, dt) VALUES('d', '20040306 02:41:32')
> INSERT INTO #dts (c1, dt) VALUES('e', '20040315 11:45:17')
> INSERT INTO #dts (c1, dt) VALUES('f', '20040412 09:12:59')
> INSERT INTO #dts (c1, dt) VALUES('g', '20040523 11:43:25')
> However, when I search in both Northwind and Tempdb
> databases, I am not able to find it. I would like to know
> where is the Temporary Table being created ?
> Thanks
|||Dear Hari,
I do find it by using your query.
In this way, if I want to make query of that temporary table, should I use
the table name dts?
Besides, you mention that temp tables are created every session, does it
mean that it will be deleted automatically at a specific time ? If yes, when
(Like - When I stop SQL Service) ?
Thanks
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eA7wxCZbFHA.3712@.TK2MSFTNGP09.phx.gbl...
> Hi,
> All the # (temp) tables and ## (global temp) table will be created in
> TEMPDB database. But for temp tables the name will be
> created with a unique prefix. This is because temp tables are created
> every session and each session a unqie table needs to be created.
> How to see this..
> Select name from tempdb..sysobjects where name like 'dts%'
> Thanks
> Hari
> SQL Server MVP
>
> "Peter" <anonymous@.discussions.microsoft.com> wrote in message
> news:0a0f01c56d8c$8f52e270$a401280a@.phx.gbl...
>
|||Why bother about the physical name? Just use the temp table name you gave it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:0a0f01c56d8c$8f52e270$a401280a@.phx.gbl...
>I have created the following table in Northwind Database:
> CREATE TABLE #dts(c1 char(1), dt datetime)
> INSERT INTO #dts (c1, dt) VALUES('a', '20040305 09:12:59')
> INSERT INTO #dts (c1, dt) VALUES('b', '20040305 16:03:12')
> INSERT INTO #dts (c1, dt) VALUES('c', '20040306 00:00:00')
> INSERT INTO #dts (c1, dt) VALUES('d', '20040306 02:41:32')
> INSERT INTO #dts (c1, dt) VALUES('e', '20040315 11:45:17')
> INSERT INTO #dts (c1, dt) VALUES('f', '20040412 09:12:59')
> INSERT INTO #dts (c1, dt) VALUES('g', '20040523 11:43:25')
> However, when I search in both Northwind and Tempdb
> databases, I am not able to find it. I would like to know
> where is the Temporary Table being created ?
> Thanks
|||Peter wrote:
> Dear Hari,
> I do find it by using your query.
> In this way, if I want to make query of that temporary table, should I use
> the table name dts?
Why not use the temp name (#dts)?

> Besides, you mention that temp tables are created every session, does it
> mean that it will be deleted automatically at a specific time ? If yes, when
> (Like - When I stop SQL Service) ?
>
The table will be dropped as soon as the connection is closed.
Are you trying to access this table from a different connection?
If so, you might want a global temp as already specified ##TableName.
This should be available until the service is restarted.
The beauty of temp tables is they are localized to your current
connection so you can use them in a sp for instance and guarantee that
nothing else will disturb your temp table.
You might well be able to query the name from tempdb and then use it
'normally' but this would defeat the purpose of temp tables and could
well introduce some nasty side effects.
Cheers
JB

> Thanks
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eA7wxCZbFHA.3712@.TK2MSFTNGP09.phx.gbl...
>
>

Location for Temporary File

I have created the following table in Northwind Database:
CREATE TABLE #dts(c1 char(1), dt datetime)
INSERT INTO #dts (c1, dt) VALUES('a', '20040305 09:12:59')
INSERT INTO #dts (c1, dt) VALUES('b', '20040305 16:03:12')
INSERT INTO #dts (c1, dt) VALUES('c', '20040306 00:00:00')
INSERT INTO #dts (c1, dt) VALUES('d', '20040306 02:41:32')
INSERT INTO #dts (c1, dt) VALUES('e', '20040315 11:45:17')
INSERT INTO #dts (c1, dt) VALUES('f', '20040412 09:12:59')
INSERT INTO #dts (c1, dt) VALUES('g', '20040523 11:43:25')
However, when I search in both Northwind and Tempdb
databases, I am not able to find it. I would like to know
where is the Temporary Table being created ?
ThanksHi,
All the # (temp) tables and ## (global temp) table will be created in TEMPDB
database. But for temp tables the name will be
created with a unique prefix. This is because temp tables are created every
session and each session a unqie table needs to be created.
How to see this..
Select name from tempdb..sysobjects where name like 'dts%'
Thanks
Hari
SQL Server MVP
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:0a0f01c56d8c$8f52e270$a401280a@.phx.gbl...
>I have created the following table in Northwind Database:
> CREATE TABLE #dts(c1 char(1), dt datetime)
> INSERT INTO #dts (c1, dt) VALUES('a', '20040305 09:12:59')
> INSERT INTO #dts (c1, dt) VALUES('b', '20040305 16:03:12')
> INSERT INTO #dts (c1, dt) VALUES('c', '20040306 00:00:00')
> INSERT INTO #dts (c1, dt) VALUES('d', '20040306 02:41:32')
> INSERT INTO #dts (c1, dt) VALUES('e', '20040315 11:45:17')
> INSERT INTO #dts (c1, dt) VALUES('f', '20040412 09:12:59')
> INSERT INTO #dts (c1, dt) VALUES('g', '20040523 11:43:25')
> However, when I search in both Northwind and Tempdb
> databases, I am not able to find it. I would like to know
> where is the Temporary Table being created ?
> Thanks|||In TempDB
Run the query after your creation
IF OBJECT_ID('tempdb..#dts') IS NOT NULL
PRINT 'TempDB'
ELSE
PRINT 'No'
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:0a0f01c56d8c$8f52e270$a401280a@.phx.gbl...
> I have created the following table in Northwind Database:
> CREATE TABLE #dts(c1 char(1), dt datetime)
> INSERT INTO #dts (c1, dt) VALUES('a', '20040305 09:12:59')
> INSERT INTO #dts (c1, dt) VALUES('b', '20040305 16:03:12')
> INSERT INTO #dts (c1, dt) VALUES('c', '20040306 00:00:00')
> INSERT INTO #dts (c1, dt) VALUES('d', '20040306 02:41:32')
> INSERT INTO #dts (c1, dt) VALUES('e', '20040315 11:45:17')
> INSERT INTO #dts (c1, dt) VALUES('f', '20040412 09:12:59')
> INSERT INTO #dts (c1, dt) VALUES('g', '20040523 11:43:25')
> However, when I search in both Northwind and Tempdb
> databases, I am not able to find it. I would like to know
> where is the Temporary Table being created ?
> Thanks|||Dear Hari,
I do find it by using your query.
In this way, if I want to make query of that temporary table, should I use
the table name dts?
Besides, you mention that temp tables are created every session, does it
mean that it will be deleted automatically at a specific time ? If yes, when
(Like - When I stop SQL Service) ?
Thanks
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eA7wxCZbFHA.3712@.TK2MSFTNGP09.phx.gbl...
> Hi,
> All the # (temp) tables and ## (global temp) table will be created in
> TEMPDB database. But for temp tables the name will be
> created with a unique prefix. This is because temp tables are created
> every session and each session a unqie table needs to be created.
> How to see this..
> Select name from tempdb..sysobjects where name like 'dts%'
> Thanks
> Hari
> SQL Server MVP
>
> "Peter" <anonymous@.discussions.microsoft.com> wrote in message
> news:0a0f01c56d8c$8f52e270$a401280a@.phx.gbl...
>|||Why bother about the physical name? Just use the temp table name you gave it
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:0a0f01c56d8c$8f52e270$a401280a@.phx.gbl...
>I have created the following table in Northwind Database:
> CREATE TABLE #dts(c1 char(1), dt datetime)
> INSERT INTO #dts (c1, dt) VALUES('a', '20040305 09:12:59')
> INSERT INTO #dts (c1, dt) VALUES('b', '20040305 16:03:12')
> INSERT INTO #dts (c1, dt) VALUES('c', '20040306 00:00:00')
> INSERT INTO #dts (c1, dt) VALUES('d', '20040306 02:41:32')
> INSERT INTO #dts (c1, dt) VALUES('e', '20040315 11:45:17')
> INSERT INTO #dts (c1, dt) VALUES('f', '20040412 09:12:59')
> INSERT INTO #dts (c1, dt) VALUES('g', '20040523 11:43:25')
> However, when I search in both Northwind and Tempdb
> databases, I am not able to find it. I would like to know
> where is the Temporary Table being created ?
> Thanks|||Peter wrote:
> Dear Hari,
> I do find it by using your query.
> In this way, if I want to make query of that temporary table, should I use
> the table name dts?
Why not use the temp name (#dts)?

> Besides, you mention that temp tables are created every session, does it
> mean that it will be deleted automatically at a specific time ? If yes, wh
en
> (Like - When I stop SQL Service) ?
>
The table will be dropped as soon as the connection is closed.
Are you trying to access this table from a different connection?
If so, you might want a global temp as already specified ##TableName.
This should be available until the service is restarted.
The beauty of temp tables is they are localized to your current
connection so you can use them in a sp for instance and guarantee that
nothing else will disturb your temp table.
You might well be able to query the name from tempdb and then use it
'normally' but this would defeat the purpose of temp tables and could
well introduce some nasty side effects.
Cheers
JB

> Thanks
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eA7wxCZbFHA.3712@.TK2MSFTNGP09.phx.gbl...
>
>

Location for Temporary File

I have created the following table in Northwind Database:
CREATE TABLE #dts(c1 char(1), dt datetime)
INSERT INTO #dts (c1, dt) VALUES('a', '20040305 09:12:59')
INSERT INTO #dts (c1, dt) VALUES('b', '20040305 16:03:12')
INSERT INTO #dts (c1, dt) VALUES('c', '20040306 00:00:00')
INSERT INTO #dts (c1, dt) VALUES('d', '20040306 02:41:32')
INSERT INTO #dts (c1, dt) VALUES('e', '20040315 11:45:17')
INSERT INTO #dts (c1, dt) VALUES('f', '20040412 09:12:59')
INSERT INTO #dts (c1, dt) VALUES('g', '20040523 11:43:25')
However, when I search in both Northwind and Tempdb
databases, I am not able to find it. I would like to know
where is the Temporary Table being created ?
ThanksHi,
All the # (temp) tables and ## (global temp) table will be created in TEMPDB
database. But for temp tables the name will be
created with a unique prefix. This is because temp tables are created every
session and each session a unqie table needs to be created.
How to see this..
Select name from tempdb..sysobjects where name like 'dts%'
Thanks
Hari
SQL Server MVP
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:0a0f01c56d8c$8f52e270$a401280a@.phx.gbl...
>I have created the following table in Northwind Database:
> CREATE TABLE #dts(c1 char(1), dt datetime)
> INSERT INTO #dts (c1, dt) VALUES('a', '20040305 09:12:59')
> INSERT INTO #dts (c1, dt) VALUES('b', '20040305 16:03:12')
> INSERT INTO #dts (c1, dt) VALUES('c', '20040306 00:00:00')
> INSERT INTO #dts (c1, dt) VALUES('d', '20040306 02:41:32')
> INSERT INTO #dts (c1, dt) VALUES('e', '20040315 11:45:17')
> INSERT INTO #dts (c1, dt) VALUES('f', '20040412 09:12:59')
> INSERT INTO #dts (c1, dt) VALUES('g', '20040523 11:43:25')
> However, when I search in both Northwind and Tempdb
> databases, I am not able to find it. I would like to know
> where is the Temporary Table being created ?
> Thanks|||In TempDB
Run the query after your creation
IF OBJECT_ID('tempdb..#dts') IS NOT NULL
PRINT 'TempDB'
ELSE
PRINT 'No'
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:0a0f01c56d8c$8f52e270$a401280a@.phx.gbl...
> I have created the following table in Northwind Database:
> CREATE TABLE #dts(c1 char(1), dt datetime)
> INSERT INTO #dts (c1, dt) VALUES('a', '20040305 09:12:59')
> INSERT INTO #dts (c1, dt) VALUES('b', '20040305 16:03:12')
> INSERT INTO #dts (c1, dt) VALUES('c', '20040306 00:00:00')
> INSERT INTO #dts (c1, dt) VALUES('d', '20040306 02:41:32')
> INSERT INTO #dts (c1, dt) VALUES('e', '20040315 11:45:17')
> INSERT INTO #dts (c1, dt) VALUES('f', '20040412 09:12:59')
> INSERT INTO #dts (c1, dt) VALUES('g', '20040523 11:43:25')
> However, when I search in both Northwind and Tempdb
> databases, I am not able to find it. I would like to know
> where is the Temporary Table being created ?
> Thanks|||Dear Hari,
I do find it by using your query.
In this way, if I want to make query of that temporary table, should I use
the table name dts?
Besides, you mention that temp tables are created every session, does it
mean that it will be deleted automatically at a specific time ? If yes, when
(Like - When I stop SQL Service) ?
Thanks
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eA7wxCZbFHA.3712@.TK2MSFTNGP09.phx.gbl...
> Hi,
> All the # (temp) tables and ## (global temp) table will be created in
> TEMPDB database. But for temp tables the name will be
> created with a unique prefix. This is because temp tables are created
> every session and each session a unqie table needs to be created.
> How to see this..
> Select name from tempdb..sysobjects where name like 'dts%'
> Thanks
> Hari
> SQL Server MVP
>
> "Peter" <anonymous@.discussions.microsoft.com> wrote in message
> news:0a0f01c56d8c$8f52e270$a401280a@.phx.gbl...
>>I have created the following table in Northwind Database:
>> CREATE TABLE #dts(c1 char(1), dt datetime)
>> INSERT INTO #dts (c1, dt) VALUES('a', '20040305 09:12:59')
>> INSERT INTO #dts (c1, dt) VALUES('b', '20040305 16:03:12')
>> INSERT INTO #dts (c1, dt) VALUES('c', '20040306 00:00:00')
>> INSERT INTO #dts (c1, dt) VALUES('d', '20040306 02:41:32')
>> INSERT INTO #dts (c1, dt) VALUES('e', '20040315 11:45:17')
>> INSERT INTO #dts (c1, dt) VALUES('f', '20040412 09:12:59')
>> INSERT INTO #dts (c1, dt) VALUES('g', '20040523 11:43:25')
>> However, when I search in both Northwind and Tempdb
>> databases, I am not able to find it. I would like to know
>> where is the Temporary Table being created ?
>> Thanks
>|||Why bother about the physical name? Just use the temp table name you gave it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:0a0f01c56d8c$8f52e270$a401280a@.phx.gbl...
>I have created the following table in Northwind Database:
> CREATE TABLE #dts(c1 char(1), dt datetime)
> INSERT INTO #dts (c1, dt) VALUES('a', '20040305 09:12:59')
> INSERT INTO #dts (c1, dt) VALUES('b', '20040305 16:03:12')
> INSERT INTO #dts (c1, dt) VALUES('c', '20040306 00:00:00')
> INSERT INTO #dts (c1, dt) VALUES('d', '20040306 02:41:32')
> INSERT INTO #dts (c1, dt) VALUES('e', '20040315 11:45:17')
> INSERT INTO #dts (c1, dt) VALUES('f', '20040412 09:12:59')
> INSERT INTO #dts (c1, dt) VALUES('g', '20040523 11:43:25')
> However, when I search in both Northwind and Tempdb
> databases, I am not able to find it. I would like to know
> where is the Temporary Table being created ?
> Thanks|||Peter wrote:
> Dear Hari,
> I do find it by using your query.
> In this way, if I want to make query of that temporary table, should I use
> the table name dts?
Why not use the temp name (#dts)?
> Besides, you mention that temp tables are created every session, does it
> mean that it will be deleted automatically at a specific time ? If yes, when
> (Like - When I stop SQL Service) ?
>
The table will be dropped as soon as the connection is closed.
Are you trying to access this table from a different connection?
If so, you might want a global temp as already specified ##TableName.
This should be available until the service is restarted.
The beauty of temp tables is they are localized to your current
connection so you can use them in a sp for instance and guarantee that
nothing else will disturb your temp table.
You might well be able to query the name from tempdb and then use it
'normally' but this would defeat the purpose of temp tables and could
well introduce some nasty side effects.
Cheers
JB
> Thanks
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eA7wxCZbFHA.3712@.TK2MSFTNGP09.phx.gbl...
>>Hi,
>>All the # (temp) tables and ## (global temp) table will be created in
>>TEMPDB database. But for temp tables the name will be
>>created with a unique prefix. This is because temp tables are created
>>every session and each session a unqie table needs to be created.
>>How to see this..
>>Select name from tempdb..sysobjects where name like 'dts%'
>>Thanks
>>Hari
>>SQL Server MVP
>>
>>"Peter" <anonymous@.discussions.microsoft.com> wrote in message
>>news:0a0f01c56d8c$8f52e270$a401280a@.phx.gbl...
>>I have created the following table in Northwind Database:
>>CREATE TABLE #dts(c1 char(1), dt datetime)
>>INSERT INTO #dts (c1, dt) VALUES('a', '20040305 09:12:59')
>>INSERT INTO #dts (c1, dt) VALUES('b', '20040305 16:03:12')
>>INSERT INTO #dts (c1, dt) VALUES('c', '20040306 00:00:00')
>>INSERT INTO #dts (c1, dt) VALUES('d', '20040306 02:41:32')
>>INSERT INTO #dts (c1, dt) VALUES('e', '20040315 11:45:17')
>>INSERT INTO #dts (c1, dt) VALUES('f', '20040412 09:12:59')
>>INSERT INTO #dts (c1, dt) VALUES('g', '20040523 11:43:25')
>>However, when I search in both Northwind and Tempdb
>>databases, I am not able to find it. I would like to know
>>where is the Temporary Table being created ?
>>Thanks
>>
>

Locating S

I recently uploaded my site to the internet. But now, when I try to to log in, I receive the following error message:

An error has occurred while establishing a connection tothe server. When connecting to SQL Server 2005, this failure may becaused by the fact that under the default settings SQL Server does notallow remote connections. (provider: SQL Network Interfaces, error: 26- Error Locating Server/Instance Specified)

How do I fix this?

I assumed you mean you get this error when you uploaded your site to your hosting service.

This error usually means that your ASP.NET application is trying to connect to a SQL Express database. In general, very few webhosting provider support SQL Express because it is not intended for production use. Does your host offer SQL 2005? If so, migrate your data to the SQL 2k5 database and update your application's connection string in the web.config (or in the code itself) to point to the SQL 2k5 database.

Hope this helps.

|||You're right. Thanks for the help. Fixed it now.

Locate SQL Server info

Hello, I'm looking for info on how/where to find the following info about
our SQL Server:
- SQL Server version type (enterprise, standard, etc.)
- SQL Server character set
- SQL Server sort order
- SQL Server files location
- SQL Server master database location
- SQL Server tempdb data location
- SQL Server tempdb log location
The reason I am looking for this is that I have adopted an SQL server for
SMS, and I am needing to collect all pertinent info and settings for
disaster recovery. TIA.
--
Hermes> - SQL Server version type (enterprise, standard, etc.)
select @.@.version
> - SQL Server character set
> - SQL Server sort order
sp_helpsort
> - SQL Server files location
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Setup
> - SQL Server master database location
> - SQL Server tempdb data location
> - SQL Server tempdb log location
sp_helpdb your_database
"hermes" <hermes@.messenger.com> wrote in message
news:epFYIwqsDHA.2360@.TK2MSFTNGP10.phx.gbl...
> Hello, I'm looking for info on how/where to find the following info about
> our SQL Server:
> - SQL Server version type (enterprise, standard, etc.)
> - SQL Server character set
> - SQL Server sort order
> - SQL Server files location
> - SQL Server master database location
> - SQL Server tempdb data location
> - SQL Server tempdb log location
> The reason I am looking for this is that I have adopted an SQL server for
> SMS, and I am needing to collect all pertinent info and settings for
> disaster recovery. TIA.
> --
> Hermes
>|||In SQL 2000, you can also SELECT * from
master..sysaltfiles to get the locations of all your
data/log files.
Linchi
>--Original Message--
>> - SQL Server version type (enterprise, standard, etc.)
>select @.@.version
>> - SQL Server character set
>> - SQL Server sort order
>sp_helpsort
>> - SQL Server files location
>HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Setup
>> - SQL Server master database location
>> - SQL Server tempdb data location
>> - SQL Server tempdb log location
>sp_helpdb your_database
>
>"hermes" <hermes@.messenger.com> wrote in message
>news:epFYIwqsDHA.2360@.TK2MSFTNGP10.phx.gbl...
>> Hello, I'm looking for info on how/where to find the
following info about
>> our SQL Server:
>> - SQL Server version type (enterprise, standard, etc.)
>> - SQL Server character set
>> - SQL Server sort order
>> - SQL Server files location
>> - SQL Server master database location
>> - SQL Server tempdb data location
>> - SQL Server tempdb log location
>> The reason I am looking for this is that I have adopted
an SQL server for
>> SMS, and I am needing to collect all pertinent info and
settings for
>> disaster recovery. TIA.
>> --
>> Hermes
>>
>
>.
>|||While listening to the voices in their head blather on endlessly about
inspecific gibberish, Linchi Shea typed:
:: In SQL 2000, you can also SELECT * from
:: master..sysaltfiles to get the locations of all your
:: data/log files.
::
:: Linchi
::
::: --Original Message--
:::: - SQL Server version type (enterprise, standard, etc.) select
:::: @.@.version
:::: - SQL Server character set
:::: - SQL Server sort order
::: sp_helpsort
:::: - SQL Server files location
::: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Setup
:::: - SQL Server master database location
:::: - SQL Server tempdb data location
:::: - SQL Server tempdb log location
::: sp_helpdb your_database
:::
:::
::: "hermes" <hermes@.messenger.com> wrote in message
::: news:epFYIwqsDHA.2360@.TK2MSFTNGP10.phx.gbl...
:::: Hello, I'm looking for info on how/where to find the following
:::: info about our SQL Server:
:::: - SQL Server version type (enterprise, standard, etc.)
:::: - SQL Server character set
:::: - SQL Server sort order
:::: - SQL Server files location
:::: - SQL Server master database location
:::: - SQL Server tempdb data location
:::: - SQL Server tempdb log location
::::
:::: The reason I am looking for this is that I have adopted an SQL
:::: server for SMS, and I am needing to collect all pertinent info and
:::: settings for disaster recovery. TIA.
::::
:::: --
:::: Hermes
::::
::::
:::
:::
::: .
Thank you so much for the quick replies! Unfortunately I am pretty much
clueless when it comes to SQL server, and I have no knowledge of running
commands like this ( I am the pc tech and new sms admin). From all of the
info you have posted, I only know how to get the info from the registry.
Could you possibly give me more info on how to run these commands such as
which application would I go into, the SQL Enterprise Manager? Also, in the
registry location, is it the SQLPath entry? Thanks again.
--
Hermes|||Hi Hermes
When someone just suggests a command to run, the tool is usually the Query
Analyzer. This is the tool you use to have your SQL Server directly execute
SQL statements.
So you could enter
SELECT * FROM master..sysaltfiles
<now press the green arrow button on the toolbar to execute this query>
or sp_helpdb 'your database name'
<press green arrow>
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"hermes" <hermes@.messenger.com> wrote in message
news:erhxTzrsDHA.2248@.TK2MSFTNGP09.phx.gbl...
> While listening to the voices in their head blather on endlessly about
> inspecific gibberish, Linchi Shea typed:
> :: In SQL 2000, you can also SELECT * from
> :: master..sysaltfiles to get the locations of all your
> :: data/log files.
> ::
> :: Linchi
> ::
> ::: --Original Message--
> :::: - SQL Server version type (enterprise, standard, etc.) select
> :::: @.@.version
> :::: - SQL Server character set
> :::: - SQL Server sort order
> ::: sp_helpsort
> :::: - SQL Server files location
> ::: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Setup
> :::: - SQL Server master database location
> :::: - SQL Server tempdb data location
> :::: - SQL Server tempdb log location
> ::: sp_helpdb your_database
> :::
> :::
> ::: "hermes" <hermes@.messenger.com> wrote in message
> ::: news:epFYIwqsDHA.2360@.TK2MSFTNGP10.phx.gbl...
> :::: Hello, I'm looking for info on how/where to find the following
> :::: info about our SQL Server:
> :::: - SQL Server version type (enterprise, standard, etc.)
> :::: - SQL Server character set
> :::: - SQL Server sort order
> :::: - SQL Server files location
> :::: - SQL Server master database location
> :::: - SQL Server tempdb data location
> :::: - SQL Server tempdb log location
> ::::
> :::: The reason I am looking for this is that I have adopted an SQL
> :::: server for SMS, and I am needing to collect all pertinent info and
> :::: settings for disaster recovery. TIA.
> ::::
> :::: --
> :::: Hermes
> ::::
> ::::
> :::
> :::
> ::: .
>
> Thank you so much for the quick replies! Unfortunately I am pretty much
> clueless when it comes to SQL server, and I have no knowledge of running
> commands like this ( I am the pc tech and new sms admin). From all of the
> info you have posted, I only know how to get the info from the registry.
> Could you possibly give me more info on how to run these commands such as
> which application would I go into, the SQL Enterprise Manager? Also, in
the
> registry location, is it the SQLPath entry? Thanks again.
> --
> Hermes
>|||While listening to the voices in their head blather on endlessly about
inspecific gibberish, Kalen Delaney typed:
:: Hi Hermes
::
:: When someone just suggests a command to run, the tool is usually the
:: Query Analyzer. This is the tool you use to have your SQL Server
:: directly execute SQL statements.
::
:: So you could enter
::
:: SELECT * FROM master..sysaltfiles
:: <now press the green arrow button on the toolbar to execute this
:: query>
::
:: or sp_helpdb 'your database name'
:: <press green arrow>
::
:: --
:: HTH
:: --
:: Kalen Delaney
:: SQL Server MVP
:: www.SolidQualityLearning.com
::
::
:: "hermes" <hermes@.messenger.com> wrote in message
:: news:erhxTzrsDHA.2248@.TK2MSFTNGP09.phx.gbl...
::: While listening to the voices in their head blather on endlessly
::: about inspecific gibberish, Linchi Shea typed:
::::: In SQL 2000, you can also SELECT * from
::::: master..sysaltfiles to get the locations of all your
::::: data/log files.
:::::
::::: Linchi
:::::
:::::: --Original Message--
::::::: - SQL Server version type (enterprise, standard, etc.) select
::::::: @.@.version
::::::: - SQL Server character set
::::::: - SQL Server sort order
:::::: sp_helpsort
::::::: - SQL Server files location
:::::: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Setup
::::::: - SQL Server master database location
::::::: - SQL Server tempdb data location
::::::: - SQL Server tempdb log location
:::::: sp_helpdb your_database
::::::
::::::
:::::: "hermes" <hermes@.messenger.com> wrote in message
:::::: news:epFYIwqsDHA.2360@.TK2MSFTNGP10.phx.gbl...
::::::: Hello, I'm looking for info on how/where to find the following
::::::: info about our SQL Server:
::::::: - SQL Server version type (enterprise, standard, etc.)
::::::: - SQL Server character set
::::::: - SQL Server sort order
::::::: - SQL Server files location
::::::: - SQL Server master database location
::::::: - SQL Server tempdb data location
::::::: - SQL Server tempdb log location
:::::::
::::::: The reason I am looking for this is that I have adopted an SQL
::::::: server for SMS, and I am needing to collect all pertinent info
::::::: and settings for disaster recovery. TIA.
:::::::
::::::: --
::::::: Hermes
:::::::
:::::::
::::::
::::::
:::::: .
:::
:::
::: Thank you so much for the quick replies! Unfortunately I am pretty
::: much clueless when it comes to SQL server, and I have no knowledge
::: of running commands like this ( I am the pc tech and new sms
::: admin). From all of the info you have posted, I only know how to
::: get the info from the registry. Could you possibly give me more
::: info on how to run these commands such as which application would I
::: go into, the SQL Enterprise Manager? Also, in the registry
::: location, is it the SQLPath entry? Thanks again.
:::
::: --
::: Hermes
Kalen and everyone, you have been incredibly helpful, thanks! :) I have all
of the info I need now except the character set. Anyone out there who knows
how to find out which character set my SQL Server is using? Thanks again.
--
Hermes|||SELECT SERVERPROPERTY('collation')
will give you the default for the SQL Server, but databases can have a
different collation than the server default.
For a particular database:
SELECT DatabasePropertyEx('database_name','Collation')
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"hermes" <hermes@.messenger.com> wrote in message
news:OABB0$ssDHA.1884@.TK2MSFTNGP10.phx.gbl...
> While listening to the voices in their head blather on endlessly about
> inspecific gibberish, Kalen Delaney typed:
> :: Hi Hermes
> ::
> :: When someone just suggests a command to run, the tool is usually the
> :: Query Analyzer. This is the tool you use to have your SQL Server
> :: directly execute SQL statements.
> ::
> :: So you could enter
> ::
> :: SELECT * FROM master..sysaltfiles
> :: <now press the green arrow button on the toolbar to execute this
> :: query>
> ::
> :: or sp_helpdb 'your database name'
> :: <press green arrow>
> ::
> :: --
> :: HTH
> :: --
> :: Kalen Delaney
> :: SQL Server MVP
> :: www.SolidQualityLearning.com
> ::
> ::
> :: "hermes" <hermes@.messenger.com> wrote in message
> :: news:erhxTzrsDHA.2248@.TK2MSFTNGP09.phx.gbl...
> ::: While listening to the voices in their head blather on endlessly
> ::: about inspecific gibberish, Linchi Shea typed:
> ::::: In SQL 2000, you can also SELECT * from
> ::::: master..sysaltfiles to get the locations of all your
> ::::: data/log files.
> :::::
> ::::: Linchi
> :::::
> :::::: --Original Message--
> ::::::: - SQL Server version type (enterprise, standard, etc.) select
> ::::::: @.@.version
> ::::::: - SQL Server character set
> ::::::: - SQL Server sort order
> :::::: sp_helpsort
> ::::::: - SQL Server files location
> :::::: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Setup
> ::::::: - SQL Server master database location
> ::::::: - SQL Server tempdb data location
> ::::::: - SQL Server tempdb log location
> :::::: sp_helpdb your_database
> ::::::
> ::::::
> :::::: "hermes" <hermes@.messenger.com> wrote in message
> :::::: news:epFYIwqsDHA.2360@.TK2MSFTNGP10.phx.gbl...
> ::::::: Hello, I'm looking for info on how/where to find the following
> ::::::: info about our SQL Server:
> ::::::: - SQL Server version type (enterprise, standard, etc.)
> ::::::: - SQL Server character set
> ::::::: - SQL Server sort order
> ::::::: - SQL Server files location
> ::::::: - SQL Server master database location
> ::::::: - SQL Server tempdb data location
> ::::::: - SQL Server tempdb log location
> :::::::
> ::::::: The reason I am looking for this is that I have adopted an SQL
> ::::::: server for SMS, and I am needing to collect all pertinent info
> ::::::: and settings for disaster recovery. TIA.
> :::::::
> ::::::: --
> ::::::: Hermes
> :::::::
> :::::::
> ::::::
> ::::::
> :::::: .
> :::
> :::
> ::: Thank you so much for the quick replies! Unfortunately I am pretty
> ::: much clueless when it comes to SQL server, and I have no knowledge
> ::: of running commands like this ( I am the pc tech and new sms
> ::: admin). From all of the info you have posted, I only know how to
> ::: get the info from the registry. Could you possibly give me more
> ::: info on how to run these commands such as which application would I
> ::: go into, the SQL Enterprise Manager? Also, in the registry
> ::: location, is it the SQLPath entry? Thanks again.
> :::
> ::: --
> ::: Hermes
>
> Kalen and everyone, you have been incredibly helpful, thanks! :) I have
all
> of the info I need now except the character set. Anyone out there who
knows
> how to find out which character set my SQL Server is using? Thanks again.
> --
> Hermes
>

Locate Database Object Given The Page

Hi Folks,

Getting the following error after a DBCC CHECKDB

Server: Msg 8906, Level 16, State 1, Line 1
Page (3:15010) in database ID 9 is allocated in the SGAM (3:3) and PFS (3:8088), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.

Trying to located the object that the allocation has gone to pot over.

Any quick way to resolve a datbase object using a page ID?

Thank In Advancewhoops. not enough coffee.|||"database ID 9" relates to the the entry in master.dbo.sysdatabases

I need to know what object within this database the page is allocated to.

Thanks

local variable in select statement

Hello to all,
are there that have already used a select like the
following to catch backup history rows from different SQL
server defined as linked server
select *
from @.my_system.msdb.dbo.sysdbmaintplan_history
where .........
the local variable @...... set using a cursor seams to be
not sintactically correct.
Any idea
Thanks marinoYou need to use dynamic sql if the servername changes
e.g.
declare @.cmd nvarchar(500)
set @.cmd = N'select * from ' + @.my_system +
N'.msdb.dbo.sysdbmaintplan_history'
exec sp_executesql @.cmd
For more on dynamic sql see
http://www.algonet.se/~sommar/dynamic_sql.html
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Marino Prandini" <marino_prandini@.hotmail.com> wrote in message
news:181101c3aeb4$3af029b0$a601280a@.phx.gbl...
Hello to all,
are there that have already used a select like the
following to catch backup history rows from different SQL
server defined as linked server
select *
from @.my_system.msdb.dbo.sysdbmaintplan_history
where .........
the local variable @...... set using a cursor seams to be
not sintactically correct.
Any idea
Thanks marino

Local user XP -> remote XP

HI Guys,
Got the following scenario:
Client machine: Windows XP Pro (SP2), logged in as local user
Server machine: Windows XP Pro (SP2), running SQL Server 2000
both machines in same Domain.
I am attempting to connect from the client to the server using SQL
Server authentication, which is definitely enabled as an option on the
remote machine. SQL Client Network lists TCP/IP and Named Pipes
enabled, with TCP/IP highest. Neither connecting through TCP/IP nor
name pipes (using isql) succeeds, and I get a generic "SQL Server does
not exist" type message through query analyzer.
This started with a problem running an ASP.NET website against a remote
database, but I've verified I can 1) Connect to the server using
Windows Authentication, when I'm logged in as a domain user, 2) Connect
using the SQL Server credentials, when I'm logged in as a domain user.
I wouldn't be surprised for NP to fail when I'm a local user, but
TCP/IP shouldn't be affected by who's logged in, should it?
Okay, normal bits now of "this used to work, and I'm sure I haven't
changed anything". Only thing I have done recently was install the
latest MS updates as delivered through Automatic Updates. I'm not sure
if anything has changed on the server, since it's another devs
development machine, but I wouldn't expect him to have been "playing"
in this area.
I was initially unconcerned by this problem, since our live webserver
talks directly to a local instance of SQL, so all should be well. Until
I remembered that as part of the next rollout, we plan to move the
databases onto a separate box. So I'd like to understand this problem
in the development area in case it happens on live as well.
Sorry for rambling on. And I've probably forgotten something important
too. Thanks in advance for any help.
DamienHi,
I guess secuzrity is blocking your request on the other machine. YOu
have to open the ports of SQL Server on the hosting machine that the
client can connect to an instance. The default port of SQL Server is
1433, but that can be different on your machine if you changed another
one on installation time. For more information about Windows XP SP2 and
SQL Server read the following article.
http://support.microsoft.com/defaul...kb;en-us;841249
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Jens wrote:
> Hi,
> I guess secuzrity is blocking your request on the other machine. YOu
> have to open the ports of SQL Server on the hosting machine that the
> client can connect to an instance. The default port of SQL Server is
> 1433, but that can be different on your machine if you changed another
> one on installation time. For more information about Windows XP SP2 and
> SQL Server read the following article.
> http://support.microsoft.com/defaul...kb;en-us;841249
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
Doh! The thing I forgot to do was... Check that TCP connections worked
directly. Turned out that all my previous succesful connections were NP
connections. So I'll be looking at the firewall then...
Cheers,
Damien

local system privildges

I am trying to upgrade the SQL Server 6.5 on one server to SQL
Server2000 on the other machine.

At that time, i am getting the following error:

"The local SQL Server is running under localsystem priviledges,
preventing the upgrade wizard to connect to the export server."

I am logged on thru the administrator on both the machines.

Pls tell how to to eradicate this error, so that my upgrade is
successful.

Thanks in advance.thakwani@.rediffmail.com (Prashant Thakwani) wrote in message news:<bf0d42bf.0404252301.780bce11@.posting.google.com>...
> I am trying to upgrade the SQL Server 6.5 on one server to SQL
> Server2000 on the other machine.
> At that time, i am getting the following error:
> "The local SQL Server is running under localsystem priviledges,
> preventing the upgrade wizard to connect to the export server."
> I am logged on thru the administrator on both the machines.
>
> Pls tell how to to eradicate this error, so that my upgrade is
> successful.
>
> Thanks in advance.

During the upgrade, the SQL2000 server needs to connect to the
filesystem on the SQL6.5 server. This is not possible if the MSSQL
service is running as LocalSystem, because LocalSystem has no network
access. You should consider changing your SQL2000 installation to run
using a domain account, instead of LocalSystem - this is recommended
anyway, as some functionality requires a domain account, and it's also
possible to limit the permissions of the service account, which is
better for security.

See "Setting up Windows Services Accounts" and "Changing Passwords and
User Accounts" in Books Online.

Simon

Local or Remote?

Hi all - beginner here :)

Have not used Reporting Services before - used to use crystal report.

I have finally got a report working using the following code:

//ReportViewer1.Visible is set to false in design mode ReportViewer1.Visible =true;// Set the processing mode for the ReportViewer to Local ReportViewer1.ProcessingMode = ProcessingMode.Local; LocalReport rep = ReportViewer1.LocalReport; rep.ReportPath ="reports/Despatch.rdlc"; despatch desp2 =new despatch(); DataSet dtable2 = desp2.DespatchNotesByOrderID("52"); DataSet ds = dtable2;// Create a report data source for the sales order data ReportViewer1.LocalReport.DataSources.Clear(); ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("DespatchByID_despatchbyid", dtable2.Tables[0])); rep.Refresh();

This works perfectly :) However - I have just discovered that I cannot make use of the "print" button on the reportviewer when it is a local report. Can anyone tell me how I would go about using server reports for the same purpose?

Thanks in advance.

Stephen.

Hi - having looked around - I figure that I need to deploy my designed reports to my reporting server - I'm running Visual Studio 2005 Standard and SQL Server Express with Advanced Services. Any help greatly received :)

|||

This is easy this is a great article which go with u step by step

http://msdn2.microsoft.com/en-us/library/ms251712(VS.80).aspx

Local connection to SQL Server 2005 Express fails

I just installed SQL Server 2005 Express edition, and created an app in
Visual Studio using C#. I get the following error when trying to connect:
"An error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that
under the default settings SQL Server does not allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to
SQL Server)"
I enabled Named Pipes but left TCP/IP disabled because this is a localhost
connection on the same computer.
Here is the portion of the code I am using to connect:
string dbCString = "Persist Security Info=False;Integrated
Security=true;Trusted_Connection=true;Initial
Catalog=MxData;server=(local)";
SqlConnection ThisConnection = new SqlConnection(dbCString);
ThisConnection.Open();
Thanks in advance for any help.
Perhaps these will help:
Configuration -Configure SQL Server 2005 to allow remote connections
http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277
Configuration -Connect to SQL Express from "downlevel clients"
http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"TAC" <tac@.noemail.com> wrote in message
news:GsCdnUMH0OMdb_DYnZ2dnUVZ_qOdnZ2d@.giganews.com ...
>I just installed SQL Server 2005 Express edition, and created an app in
>Visual Studio using C#. I get the following error when trying to connect:
> "An error has occurred while establishing a connection to the server.
> When connecting to SQL Server 2005, this failure may be caused by the fact
> that under the default settings SQL Server does not allow remote
> connections. (provider: Named Pipes Provider, error: 40 - Could not open a
> connection to SQL Server)"
> I enabled Named Pipes but left TCP/IP disabled because this is a localhost
> connection on the same computer.
> Here is the portion of the code I am using to connect:
> string dbCString = "Persist Security Info=False;Integrated
> Security=true;Trusted_Connection=true;Initial
> Catalog=MxData;server=(local)";
> SqlConnection ThisConnection = new SqlConnection(dbCString);
> ThisConnection.Open();
>
> Thanks in advance for any help.
>
>
>
>
>
|||Thanks for the info, but I did everything suggested in those two articles
and I still get the same connection error. I also tried various alterations
of my connection string with no success. Further research indicates that
this is a common problem, so I wonder why Microsoft ships the Express
edition along with Visual Studio 2005, but makes it so difficult to connect.
If you or anyone else has any other ideas, I would be most grateful.
Thanks.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:OOy2vm$EHHA.1784@.TK2MSFTNGP06.phx.gbl...
> Perhaps these will help:
> Configuration -Configure SQL Server 2005 to allow remote connections
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277
> Configuration -Connect to SQL Express from "downlevel clients"
> http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to
> the top yourself.
> - H. Norman Schwarzkopf
>
> "TAC" <tac@.noemail.com> wrote in message
> news:GsCdnUMH0OMdb_DYnZ2dnUVZ_qOdnZ2d@.giganews.com ...
>
|||This is a WAG:
You only need one of these two settings, perhaps having both is causing the
connection string to cause failure.
Integrated Security=true;Trusted_Connection=true;
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"TAC" <tac@.noemail.com> wrote in message
news:ONCdne2ocahst_LYnZ2dnUVZ_u6dnZ2d@.giganews.com ...
> Thanks for the info, but I did everything suggested in those two articles
> and I still get the same connection error. I also tried various
> alterations of my connection string with no success. Further research
> indicates that this is a common problem, so I wonder why Microsoft ships
> the Express edition along with Visual Studio 2005, but makes it so
> difficult to connect.
> If you or anyone else has any other ideas, I would be most grateful.
> Thanks.
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:OOy2vm$EHHA.1784@.TK2MSFTNGP06.phx.gbl...
>
|||Thanks for the suggestion, but using only one or the other didn't work,
either.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:OD744%23LFHHA.1912@.TK2MSFTNGP03.phx.gbl...
> This is a WAG:
> You only need one of these two settings, perhaps having both is causing
> the connection string to cause failure.
> Integrated Security=true;Trusted_Connection=true;
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to
> the top yourself.
> - H. Norman Schwarzkopf
>
> "TAC" <tac@.noemail.com> wrote in message
> news:ONCdne2ocahst_LYnZ2dnUVZ_u6dnZ2d@.giganews.com ...
>
|||After a lot of research, here's the answer. The connection string should
read as follows:
"Server=.\\SQLEXPRESS;Initial Catalog=MxData;Integrated Security=SSPI";
Note that you need a dot followed by TWO left slashes in your server name.
BTW, Initial Catalog is whatever database you want to use.
"TAC" <tac@.noemail.com> wrote in message
news:bpadnbRK_s7Mz_LYnZ2dnUVZ_oadnZ2d@.giganews.com ...
> Thanks for the suggestion, but using only one or the other didn't work,
> either.
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:OD744%23LFHHA.1912@.TK2MSFTNGP03.phx.gbl...
>
|||Thanks for following up your own post. It prevents others from wasting their
time trying to help you after you have solved the problem, and it helps
others when you share your solution.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"TAC" <tac@.noemail.com> wrote in message
news:BKidnUwKtpML-PLYnZ2dnUVZ_qmdnZ2d@.giganews.com...
> After a lot of research, here's the answer. The connection string should
> read as follows:
> "Server=.\\SQLEXPRESS;Initial Catalog=MxData;Integrated Security=SSPI";
> Note that you need a dot followed by TWO left slashes in your server name.
> BTW, Initial Catalog is whatever database you want to use.
>
> "TAC" <tac@.noemail.com> wrote in message
> news:bpadnbRK_s7Mz_LYnZ2dnUVZ_oadnZ2d@.giganews.com ...
>