Lock hint ?

Here I have a question like I have created one stored procedure which
InsertCustomer and call that stored procedure in the customer form... the
problem is that when more than 50 - 60 people access the same form
simultaneously then it gave me some time time out error or some times gave
me primary violation error... duplication key not allowed.
Can any one help like is there any lock hint I have to use in stored
procedure?
Thanks
Without seeing exactly what you are doing with the sp it is pretty hard to
say what the trouble is. How are you generating the PK?
Andrew J. Kelly SQL MVP
"Roy" <roy@.hotmail.com> wrote in message
news:uu808QxZFHA.3808@.TK2MSFTNGP09.phx.gbl...
> Here I have a question like I have created one stored procedure which
> InsertCustomer and call that stored procedure in the customer form... the
> problem is that when more than 50 - 60 people access the same form
> simultaneously then it gave me some time time out error or some times gave
> me primary violation error... duplication key not allowed.
> Can any one help like is there any lock hint I have to use in stored
> procedure?
> Thanks
>
>
|||This is basically a identity column so in the stored procedure I didn't use
this column.. it's auto inserted column.
Thanks
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uimiYrxZFHA.3320@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Without seeing exactly what you are doing with the sp it is pretty hard to
> say what the trouble is. How are you generating the PK?
> --
> Andrew J. Kelly SQL MVP
>
> "Roy" <roy@.hotmail.com> wrote in message
> news:uu808QxZFHA.3808@.TK2MSFTNGP09.phx.gbl...
the[vbcol=seagreen]
gave
>
|||Again, without seeing the actual stored procedure and the DDL for the
underlying table we can't begin to give an intelligent answer.
Andrew J. Kelly SQL MVP
"Joh" <joh@.mailcity.com> wrote in message
news:uiDPgqHaFHA.3400@.tk2msftngp13.phx.gbl...
> This is basically a identity column so in the stored procedure I didn't
> use
> this column.. it's auto inserted column.
> Thanks
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uimiYrxZFHA.3320@.TK2MSFTNGP12.phx.gbl...
> the
> gave
>

Lock a row in the database

Hello,

I created a form that retrieve a number from a database. Can I lock that record so that other people cannot access it?

Similar to "lock" function in Visual Fox Pro database.

Best regards,
Tee Song Yann

Sounds like you are doing some form of incrementing.

A very good way to handle that would be to use the OUTPUT capability of SQL 2005 -it allows you to BOTH increment and retrieve the incremented value in the same query.

Perhaps something like this:

DECLARE @.MyNumberTable table
( NextNumber int )

UPDATE MyNumberTable
SET NextNumber = ( NextNumber + 1 )
OUTPUT inserted.NextNumber INTO @.MyNumberTable

SELECT NextNumber
FROM @.MyNumberTable

|||

Hello Arnie Rowland,

Thanks for the reply. Your solution solved the first part of my problem. The record locking part is still remain unsolved. A friend from another forum suggested:

SET TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SNAPSHOT | SERIALIZABLE }[ ; ]

What do you think?

All your help is much appriciated.

Best regards,
Tee Song Yann

|||

Are you attempting to create an auto-incrementing column? If so use an identity column.

Updating a central value and then holding a lock on it will introduce a bottleneck into your application as all connections will eventually be blocked waiting to update the same value.

|||

Dear Jerome Halmans,

My idea is:

1) I have a table that store various last used number for various document.

2) I will use those number combine with a few more code to create a meaningful serial number. (For example: An invoice will consist of XX XXXXXX XXXX which is the company code, year/month/date and next number of last used number.)

3) If another person login from another company, it will have different company code and different last number.

4) The fun part is I do not want to have jump numbers.

5) If the first user cancel the record inserting, then the number should stay unchanged.

6) The only way that I can think of is by locking the record while the first user is inserting the record.

Is there a batter way for me to achieve that?

Best regards,

Tee Song Yann

|||

I HIGHLY recommend dropping the idea of trying to maintain consequtive 'numbers' if users can 'cancel' the insert action. That will prove to be a major headache. That will create a significant blocking issue and a major performance problem.

Unless, of course, you do not assign the 'number' until the data is saved. At that point, the number can be reported back to the user (post-hoc), and there is not a cancellation issue.

|||

Thanks Arnie,

I get what you mean. I think I will follow your second idea.

Then just out of curiosity, SQL server can perform record locking?

Best regards,

Tee Song Yann

|||

Yes. Look up [ Hints -Locking ] in Books Online.

There are several ways to accomplish locking -it depends upon what you want to accomplish. With the exception of TRANSACTIONS, and in the absence of a clear understanding of the implications and effects, it is best to leave record locking behavior to the server.

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 sqlexpress database

I have created my sqlexpress database in Visual Studio 2005. When i go to SQL Server Management Studio Express I cannot open my database. It is not listed automatically and I get an error 'There is no editor for 'database name' Make sure the application for file type (.mdf) if installed.'

Any help or suggestions will be appreciated.

Thanks

hmmmm.. When you open sql server management studio are you able to connect to your local sql express 05 instance? Can you see your system tables like Master etc.?

If so, is it possible that you just need to attach to the MDF by right clicking on the local server and selecting "Attach" and then pointing to this new database you have created?

|||

Databases that are created by Visual Studio reside in a per user location and are not attached to the main instance of SQL Express. VS uses a special kind of instance of SQL Express called a User Instance. VS provides a fairly complete set of database management tools built-in, so you should be able to do your management task from directly in VS.

There are a couple barriers if you want to open your database from VS in Management Studio:

VS database are created in a directory that SQL Express doesn't have permissions for, so the file can't be attached. You would need to grant the Network Service account permissions to the project directory where the database file exists.|||

Hey Mike,

Even after granting Network Service account full permissions to all directories, I still could not locate or attatch the dbase, it wouldnt see anything in the folder where it was at.

Regards

Tom

locating sqlexpress database

I have created my sqlexpress database in Visual Studio 2005. When i go to SQL Server Management Studio Express I cannot open my database. It is not listed automatically and I get an error 'There is no editor for 'database name' Make sure the application for file type (.mdf) if installed.'

Any help or suggestions will be appreciated.

Thanks

hmmmm.. When you open sql server management studio are you able to connect to your local sql express 05 instance? Can you see your system tables like Master etc.?

If so, is it possible that you just need to attach to the MDF by right clicking on the local server and selecting "Attach" and then pointing to this new database you have created?

|||

Databases that are created by Visual Studio reside in a per user location and are not attached to the main instance of SQL Express. VS uses a special kind of instance of SQL Express called a User Instance. VS provides a fairly complete set of database management tools built-in, so you should be able to do your management task from directly in VS.

There are a couple barriers if you want to open your database from VS in Management Studio:

VS database are created in a directory that SQL Express doesn't have permissions for, so the file can't be attached. You would need to grant the Network Service account permissions to the project directory where the database file exists.|||

Hey Mike,

Even after granting Network Service account full permissions to all directories, I still could not locate or attatch the dbase, it wouldnt see anything in the folder where it was at.

Regards

Tom

Localisation using custom assemblies

Hi,
I have created an assembly A that loads a resource assembly. The
resource assembly has other satellite assemblies (en-US, pt-BR, etc).
The aseembly A exposes a static method GetString() to access GetString
of a ResourceManager which loads those resource assemblies.
I have tested this code in a Win Forms App and it appears to work
fine, i.e. it loads my portugese resources, english resource as I make
it to.
When they are placed in the ReportServer\bin folder, only the neutral
resources are loaded and displayed.
I have also tried to include all of the dlls including the satellite
resources in the FullTrust code group and it still displays the same
symptoms.
Can anyone tell me why or how to fix it?
Please help,
Siew Fai.Hi Siew,
Welcome to using MSDN Managed Newsgroup!
You may refer the article below to use a Custom Assembly in Reporting
Services
Writing Custom Code in SQL Server Reporting Services
http://blogs.sqlxml.org/bryantlikes/articles/824.aspx
By default, report code has "ExecutionOnly" permissions only and therefore
it fails. You need to get familiar with .NET Code Access Security (CAS) to
setup the security policy files correctly for the report server and assert
security permissions accordingly.
You may also check the MSDN documentation of Assembly.Load and scroll down
to the section with the title Requirements / .NET Framework Security:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfSystemReflectionAssemblyClassLoadTopic1.asp
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks Michael,
I have finally got my the debuglocal version working, and it seems that
you have pointed me to the right track.
Really appreciate it.
Siew Fai|||Hi Siew,
You are welcome!
If you have any questions or concerns next time, don't hesitate to let me
know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Local Temporary Tables

I have created a local temporary table (#Temp) and placed data in it.

When I do:
SELECT * #Temp

The result is a set of rows with no columns.

The SELECT statement is in the same procedure as the creation of #Temp; so I
don't believe this is a scoping issue. Does that make sense?

Thank you in advance,
EricBeringer wrote:
> I have created a local temporary table (#Temp) and placed data in it.
> When I do:
> SELECT * #Temp
> The result is a set of rows with no columns.
> The SELECT statement is in the same procedure as the creation of #Temp; so I
> don't believe this is a scoping issue. Does that make sense?
> Thank you in advance,
> Eric

No, it doesn't make sense. First, your code above is invalid SQL.
Second, you don't provide any DDL or sample data for us to reproduce the
problem.

Zach|||Please be patient with me. I'm learning how to do this stuff by myelf and
just begining! :)

At anyrate here is an example:
ALTER PROCEDURE proc1

AS

SET NOCOUNT ON

CREATE TABLE #TempTable(my_text CHAR(10))

INSERT INTO #TempTable(my_text) VALUES ('test')

INSERT INTO #TempTable(my_text) VALUES ('test2')

INSERT INTO #TempTable(my_text) VALUES ('test3')

SELECT * FROM #TempTable

The result (visually in Access 2002, in datasheet view) after executing the
procedure is simply a row header with three rows and no columns. Of note,
if the SET NOCOUNT ON is commented out there is nothing.

Thanks again,

Eric

"nib" <individual_news@.nibsworld.com> wrote in message
news:2tqnlcF21lmapU2@.uni-berlin.de...
> Beringer wrote:
>> I have created a local temporary table (#Temp) and placed data in it.
>>
>> When I do:
>> SELECT * #Temp
>>
>> The result is a set of rows with no columns.
>>
>> The SELECT statement is in the same procedure as the creation of #Temp;
>> so I don't believe this is a scoping issue. Does that make sense?
>>
>> Thank you in advance,
>> Eric
>>
>>
> No, it doesn't make sense. First, your code above is invalid SQL. Second,
> you don't provide any DDL or sample data for us to reproduce the problem.
> Zach|||I do similar stored procedures like this all the time. Except I create the
SP via Enterprise Manager and Check Syntax etc . Have you tried executing
this from Query Analyzer just to see if it works? Or just create a stored
procedure in Enterprise Manager and EXECUTE it from Query Analyzer...

The column in your example should have one column heading and three rows of
data (if I am reading it correctly).

Is it generating any errors?

Barry

"Beringer" <borden_eric@.invalid.com> wrote in message
news:l1Xdd.56381$kz3.16039@.fed1read02...
> Please be patient with me. I'm learning how to do this stuff by myelf and
> just begining! :)
> At anyrate here is an example:
> ALTER PROCEDURE proc1
> AS
> SET NOCOUNT ON
> CREATE TABLE #TempTable(my_text CHAR(10))
> INSERT INTO #TempTable(my_text) VALUES ('test')
> INSERT INTO #TempTable(my_text) VALUES ('test2')
> INSERT INTO #TempTable(my_text) VALUES ('test3')
> SELECT * FROM #TempTable
> The result (visually in Access 2002, in datasheet view) after executing
> the procedure is simply a row header with three rows and no columns. Of
> note, if the SET NOCOUNT ON is commented out there is nothing.
> Thanks again,
> Eric
> "nib" <individual_news@.nibsworld.com> wrote in message
> news:2tqnlcF21lmapU2@.uni-berlin.de...
>> Beringer wrote:
>>> I have created a local temporary table (#Temp) and placed data in it.
>>>
>>> When I do:
>>> SELECT * #Temp
>>>
>>> The result is a set of rows with no columns.
>>>
>>> The SELECT statement is in the same procedure as the creation of #Temp;
>>> so I don't believe this is a scoping issue. Does that make sense?
>>>
>>> Thank you in advance,
>>> Eric
>>>
>>>
>>
>> No, it doesn't make sense. First, your code above is invalid SQL. Second,
>> you don't provide any DDL or sample data for us to reproduce the problem.
>>
>> Zach

Local table and OpenQuery

I created a stored procedure like this:

CREATE PROCEDURE SP
AS
BEGIN
CREATE TABLE #T( C INT )
INSERT INTO #T(C) VALUES (1)
SELECT * FROM #T
END

When I call it this way: EXEC SP, it works ok.

But when I do it like this:
SELECT * FROM OPENQUERY( MYSERVER, 'EXEC SP')
I receive an error: Invalid object name '#T'
Why?...

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Evgeny Gopengauz <evgop@.ucs.ru> wrote in message news:<4119d7ae$0$14493$c397aba@.news.newsgroups.ws>...
> I created a stored procedure like this:
> CREATE PROCEDURE SP
> AS
> BEGIN
> CREATE TABLE #T( C INT )
> INSERT INTO #T(C) VALUES (1)
> SELECT * FROM #T
> END
> When I call it this way: EXEC SP, it works ok.
> But when I do it like this:
> SELECT * FROM OPENQUERY( MYSERVER, 'EXEC SP')
> I receive an error: Invalid object name '#T'
> Why?...
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

It looks like this is another multi-posted question - if you want
feedback from different groups, then please post to all of them at
once with a single posting. Having said that, you might find Erland's
recent explanation in another thread useful:

http://groups.google.com/groups?hl=...es.ms-sqlserver

Also, if you are trying to do something with the output of a stored
procedure, this article (also from Erland) should point you in the
right direction:

http://www.sommarskog.se/share_data.html

Simon|||Simon! Thank you for your references, I just found the accurate solution
for my troubles.

Concerning with my mutli-posting... I'm sorry but I have no an ability
to post to the comp.databases.ms-sqlserver and
microsoft.public.sqlserver.programming simultaneously (at the same
message) because this conference is available for me through
www.developersdex.com, but microsoft.public through NNTP. Excuse me
please, hope it will never happen again, I will use a single conference
for each single quiestion.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Local cubes connection

Hello,
I have created 3 local cubes which resided in the same .cub file. The cubes created from AS 2005 cubes.
How can I make ADOMD connection to all 3 cubes at the same time in my client application (C#)?
The problem is that Initial Catalog (database name) in connection string in case of local cubes is the same as the cube name.

Thanks,
GB

In ADOMD you don't connect directly to any specific cube. Once you have connect to the database (catalog) you just specify which cube you want in the query. So you only need the one connection and you can use that to query any of the cubes in the catalog.

eg

SELECT measures.AllMembers ON COLUMNS FROM <CUBE_NAME>

|||

I think GB meant that the problem was that since for him database name and the cube name are the same - he ended up with 3 different databases, and ADOMD only works with one database at the time.

However, the limitation of database name being the same as the cube name only existed in AS2000, and also AS2000 officially only supports one cube per database. These limitations don't exist in AS2005.

|||That makes sense, If I had to create a local cube I would use the ASSL syntax, but if I am not mistaken the CREATE LOCAL CUBE (which is depreciated) and CREATE GLOBAL CUBE methods setup the database with the same name as the cube. There is a reasonable white paper on local cubes which contrasts the different methods of creating them here http://www.localcubetask.com/UsingLocalCubesinMicrosoftSQLServer2005AnalysisServices.doc|||

I have cteated my local cubes using ASSL CGC method in SSMS 2005.

I created 3 cubes: Cube1,Cube2,Cube3 in one My_cubes.cub file.

In my C# code application I have the following connection string:

string myConnectionString = "Provider=MSOLAP;Data Source='C:\\LOCALCUBES\\My_cubes.cub';Initial Catalog=Cube1";

When I try to get dimensions from Cube2 I 'v got an error related to the connection string - no connection to Cube2.

I tryed to create 2 saparate connection strings for the same My_cubes.cub file but different cubes, but application can not open same file twice.

So, I still have a problem to open connection to 2 local cubes at the same time.

Thanks,

GB

|||The solution that was suggested above, is to build real XMLA scripts to create your cubes instead of using CREATE GLOBAL CUBE statement - this way you will be able to put multiple cubes in the same database in the local cube.|||

Yes, XMLA script is working for me.

I found the instructions hear:

http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!883.entry

Thanks,

GB

|||

Still have the problem.

My local .cub file created by XMLA script contains 3 databases with 7 cubes each.

When I open this .cub file in MDX Sample application I can make

a connection and MDX query to any of 3 databases and accordingly their cubes.

But in my C# application when I try open connection to all local databases in a loop from the same .cub file

I got the error just after attampting to open second database in a loop.This is ADOMDClient.AdomdConnection error:

"A connection cannot be made. Ensure that the server is running".

Any hints how to fix the problem?

Thanks,

GB

|||

You can only have one connection to the local cube file - therefore the second connection fails. Your options:

1. Split it to the 3 different local cube files

2. Put all the cubes in the same database

3. Close the previous connection before opening a new one

4. Keep one connection, but change the current database when you need to go to another one.

Local cubes connection

Hello,
I have created 3 local cubes which resided in the same .cub file. The cubes created from AS 2005 cubes.
How can I make ADOMD connection to all 3 cubes at the same time in my client application (C#)?
The problem is that Initial Catalog (database name) in connection string in case of local cubes is the same as the cube name.

Thanks,
GB

In ADOMD you don't connect directly to any specific cube. Once you have connect to the database (catalog) you just specify which cube you want in the query. So you only need the one connection and you can use that to query any of the cubes in the catalog.

eg

SELECT measures.AllMembers ON COLUMNS FROM <CUBE_NAME>

|||

I think GB meant that the problem was that since for him database name and the cube name are the same - he ended up with 3 different databases, and ADOMD only works with one database at the time.

However, the limitation of database name being the same as the cube name only existed in AS2000, and also AS2000 officially only supports one cube per database. These limitations don't exist in AS2005.

|||That makes sense, If I had to create a local cube I would use the ASSL syntax, but if I am not mistaken the CREATE LOCAL CUBE (which is depreciated) and CREATE GLOBAL CUBE methods setup the database with the same name as the cube. There is a reasonable white paper on local cubes which contrasts the different methods of creating them here http://www.localcubetask.com/UsingLocalCubesinMicrosoftSQLServer2005AnalysisServices.doc|||

I have cteated my local cubes using ASSL CGC method in SSMS 2005.

I created 3 cubes: Cube1,Cube2,Cube3 in one My_cubes.cub file.

In my C# code application I have the following connection string:

string myConnectionString = "Provider=MSOLAP;Data Source='C:\\LOCALCUBES\\My_cubes.cub';Initial Catalog=Cube1";

When I try to get dimensions from Cube2 I 'v got an error related to the connection string - no connection to Cube2.

I tryed to create 2 saparate connection strings for the same My_cubes.cub file but different cubes, but application can not open same file twice.

So, I still have a problem to open connection to 2 local cubes at the same time.

Thanks,

GB

|||The solution that was suggested above, is to build real XMLA scripts to create your cubes instead of using CREATE GLOBAL CUBE statement - this way you will be able to put multiple cubes in the same database in the local cube.|||

Yes, XMLA script is working for me.

I found the instructions hear:

http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!883.entry

Thanks,

GB

|||

Still have the problem.

My local .cub file created by XMLA script contains 3 databases with 7 cubes each.

When I open this .cub file in MDX Sample application I can make

a connection and MDX query to any of 3 databases and accordingly their cubes.

But in my C# application when I try open connection to all local databases in a loop from the same .cub file

I got the error just after attampting to open second database in a loop.This is ADOMDClient.AdomdConnection error:

"A connection cannot be made. Ensure that the server is running".

Any hints how to fix the problem?

Thanks,

GB

|||

You can only have one connection to the local cube file - therefore the second connection fails. Your options:

1. Split it to the 3 different local cube files

2. Put all the cubes in the same database

3. Close the previous connection before opening a new one

4. Keep one connection, but change the current database when you need to go to another one.

Local cube with calculated menber?

Hello,

I created a cube with BI

Studio which included a calculated member ‘[Measures].[Base

per Unit]’. It’s work in BI Studio. When I tried to create a local cube using ‘Create

Global cube’, this calculated member could not include. Is it possible bring calculated member to the local

cube?And how?

Thanks

in advance.

You can't explicitly add calculated members to local cubes when you're using CREATE GLOBAL CUBE. If you include all the real measures in the local cube that are used in the calculated measure's definition, then that calculated measure will automatically appear in the local cube too.

HTH,

Chris

|||Hi Chris,

Thanks for the reply. Yes, the calculated measures

automatically appear in the local cube but not for

calculated Time dimension member. I have YTD in time dimension; it works in BI

Studio but does not work in local cube. I can see YTD attribute in

local cube, but can not see YTD value. Anything is missing?

Thanks.|||

Hmm, well I've done some testing and I seem to be able to create local cubes with CREATE GLOBAL CUBE that have calculated members on the Time dimension, so there must be something else going on here. Can you post the CREATE GLOBAL CUBE statement you're using along with some details of your server cube?

Chris

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 ...
>

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;In
itial
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/defaul...kb;EN-US;914277
Configuration -Connect to SQL Express from "downlevel clients"
http://blogs.msdn.com/sqlexpress/ar.../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@.gi
ganews.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;In
itial
> 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/defaul...kb;EN-US;914277
> Configuration -Connect to SQL Express from "downlevel clients"
> http://blogs.msdn.com/sqlexpress/ar.../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@.gi
ganews.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@.gi
ganews.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...
>|||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@.gi
ganews.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@.gi
ganews.com...
>