lock timeout

my SQL studio view is running into timeout error block. how do i insert the

SET LOCK_TIMEOUT -1
GO

in the SQL statement of the view to allow this to run to completion? an example of the SQL view is;

SELECT TOP (100) PERCENT dbo.Entry_Race.E_TDR, dbo.Entry_Race.E_Surface, dbo.Entry_Race.E_Race_Class_Codes,
FROM dbo.Entry_Race INNER JOIN
dbo.Entry_Horse ON dbo.Entry_Race.E_TDR = dbo.Entry_Horse.E_TDR
WHERE (CONVERT(varchar(07), dbo.Entry_Horse.E_Date) BETWEEN CONVERT(varchar(07), GETDATE(), 0) AND CONVERT(varchar(07), GETDATE() + 1, 0))
ORDER BY dbo.Entry_Race.E_TDR, dbo.Entry_Horse.E_Horse, dbo.Entry_Horse.E_Traininer

Do you really need to wait indefinitelly? It is not a very normal situation to have the client waiting tens of seconds for a response - why not using a more optimistic locking mechanism?

You shouldn't user the convert funcion to compare the dates, but using dateadd () over the getdate() functions and compare directly - as it is, any indexes over dbo.Entry_Horse.E_Date will not be used by SQL...

Lock issue during insert

:confused:

I opened 2 sql analizer windows to simulate 2 users:

In the first one I did this:
begin transaction;
insert into tst values (15);

In the second one I sent
begin transaction;
select * from tst where col=3;

The second statement is blocked waiting for the lock to be released.
Why? I tried update in plce of the insert and then there is no lock.

What is the problem with insert ?

regards

phildo you have an index on col?
if not then the select will do a table scan and be blocked. It will return nothing until the output buffer is full or the lock is released.

In both cases you should find a few intent exclusive locks and at least one exclusive.

If the select tries to access a resource that is locked then it will be blocked - if not it won't be.

Maybe your insert was forcing some page splits whereas the update wasn't?|||You're right, it does not happen when an index is on the table.
Why this happen only for inserts , and not updates.
What do you mean by lock return when the buffer is full.
regards

phil|||>> What do you mean by lock return when the buffer is full.
When you run a query in query analyser the output will be dumped to the result window when the output buffer is full or when the query completes. That's why if you run a large select you will see the results in batches - and why it doesn't mean that a query is stuck after the last result displayed.

It should happen for both inserts and updates - it just depends on what is being locked.

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 DB WHEN INSERTING

Hello.

I need to insert some records to an accounting table and calculate the balance after that. Thus, other users can be trying to do the same. How to lock the db and make the other users wait until the right moment? I'm using SqlDataSource to do that.

Thanks.

You don't need to lock the db, just lock the database objects (accounting table in this case). You can use SqlTransaction to control the locks in your code, with setting the IsolationLevel according to your requirement. Keep in mind that you should commit or rollback the opened transaction after you finish/cancel using the database resource, otherwise it may give rise to database blocking/deadlock issues. For example:

connection.Open();

SqlCommand command = connection.CreateCommand();
SqlTransaction transaction;

transaction = connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted,"SampleTransaction");

Here are some useful links:

SqlTransaction Class:

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction.aspx

Isolation levels:

http://msdn2.microsoft.com/en-us/library/system.data.isolationlevel.aspx

|||

Good.

I'm doing some research about that.

Assuming I'm using sqldatasource, the code below should work?

sqlReceiver.InsertCommand =

"SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; " & insertCommand &"SET TRANSACTION ISOLATION LEVEL READ COMMITTED; "
sqlReceiver.Insert()

I couldn't find any property to use with sqldatasource for that purpose.

Thanks!

|||Good Your sql command should work. Unfortunately it seems there is no property to set the isolation level in SqlDataSource.|||

I believe you can have the sqldatasouce's insert command join a transaction in _Inserting, and commit it in _Inserted.

Or maybe not. You should be able to catch the insert attempt in _Inserting, create your own connection, start a transaction, steal the inserting's command object (With the parameters already set up for you), then execute it, commit the transaction, and set "e.cancel=true" to cancel the original inserting request.

Or, you can put the whole thing in a stored procedure, and call it from the sqldatasource, that works too.

Or you can just wrap your original Insert statement like you did (Just put it in the sqldatasource's insertcommand property along with your insert statement). That works as well.

|||

Good, thanks for the answers.

I was monitoring the commands via Sql Server Profiler. I thought I would be able to see theSET TRANSACTION ISOLATION LEVEL SERIALIZABLEcommand being executed there, but I couldn't. That made me think that the command was not being executed.

Now, I'm using something like that to set the isolation level:

(...)
transaction = connection.BeginTransaction(System.Data.IsolationLevel.Serializable)

Any additional comments?

Thanks.

|||

Hey,

about my problem, I still have it. Let me explain.

I'm doing several SELECTS, to get the balances from different accounts. So, until I finish that and insert the new records, the table would be really locked. I couldn't do that using a transaction level isolation. Well, I guess I can do, but that would make me to rewrite lots of code.

So, it's possible to lock the entire table with one command, and unlock only after another command?

Thanks.

|||

sqldatasource1.selectcommand="BEGIN TRANSACTION SELECT TOP 0 * FROM Table1 WITH (TABLOCKX,HOLDLOCK) More SQL Statements COMMIT TRANSACTION"

With that said, you are probably coding your T-SQL poorly, and could do what you want to do in one or two statements. There are also a lot better ways of handling the locking than this as well. This will cause concurrency issues, and you will probably start hitting deadlock issues if you use this technique very much.

LOCK & SELECT Behavior

I do a transactional INSERT on a table (using VB COM+ as middle tier calling
SPs) and then immediately do a SELECT on that same table (using VB COM+ to
call the SP and return data for display) to display info from the table. On
my development machine everything works fine, but I've noticed on the
production server that often, the returned dataset from the SELECT query
returns no rows (even though the old and new data are in the database as I
look later - the INSERT did work). I'm thinking since the INSERT and SELECT
are almost called simultaneously that this might have something to do with
transactional locking and that I should use WITH (NOLOCK) for the SELECT
statement.
When a SELECT is executed and the table is temporarily locked because a
transaction is still in process, is the behavior just to return no rows? or
should the rows be returned eventually? Is there any type of notification
when this happens? Right now, I get no errors, nothing in the event log, and
just don't get the rows I expect back to display even though they are there.
Thanks for any tips.What exactly do you mean by "do a transactional Insert"? Are the Insert and
Select done from the same connection or two different ones?
If the row is locked then a SELECT will be blocked unless you are using
NOLOCK or Read Uncommitted already. Can you give a little more details on
exactly what code you are calling and how?
Andrew J. Kelly SQL MVP
"Don Miller" <nospam@.nospam.com> wrote in message
news:%23UGy9H$2FHA.700@.TK2MSFTNGP15.phx.gbl...
>I do a transactional INSERT on a table (using VB COM+ as middle tier
>calling
> SPs) and then immediately do a SELECT on that same table (using VB COM+ to
> call the SP and return data for display) to display info from the table.
> On
> my development machine everything works fine, but I've noticed on the
> production server that often, the returned dataset from the SELECT query
> returns no rows (even though the old and new data are in the database as I
> look later - the INSERT did work). I'm thinking since the INSERT and
> SELECT
> are almost called simultaneously that this might have something to do with
> transactional locking and that I should use WITH (NOLOCK) for the SELECT
> statement.
> When a SELECT is executed and the table is temporarily locked because a
> transaction is still in process, is the behavior just to return no rows?
> or
> should the rows be returned eventually? Is there any type of notification
> when this happens? Right now, I get no errors, nothing in the event log,
> and
> just don't get the rows I expect back to display even though they are
> there.
> Thanks for any tips.
>|||The entire process of entering data from my ASP web app is transactional,
that is, if the INSERT fails (e.g. a typo error in an SP or a VBscript or
ASP error) any changes to the database will be automatically rolled back. My
VB COM+ "write" components "use transactions", my ASP code
"requires_transaction".
The INSERT and SELECT are done from different components and different
connections (same login to SQLServer though).
I haven't been using NOLOCK or Read Uncommitted in any of my SELECTs.
As far as my code, somebody fills out a web form, I pass the form to the VB
COM, that access an SP to insert the data. The same method that does the
INSERT and returns a string to the caller also calls another component that
does the SELECT, formats the returned rows as HTML and is eventually
inserted asynchronously into an existing web page (Ajax).
Since my post I've tried the NOLOCK with the SELECT statement and now the
correct rows appear more often than they did but not consistently. If I
refresh the view, all of the target rows are there including the new row. I
just don't know why the SELECT statement does not return rows when it
should.
When you say the SELECT will be blocked, what does that mean? No rows
returned, or the rows returned by a few microseconds later when the row is
not locked, or is some error logged?
Thanks for helping me out on this one.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e2ruoQ$2FHA.3880@.TK2MSFTNGP12.phx.gbl...
> What exactly do you mean by "do a transactional Insert"? Are the Insert
and
> Select done from the same connection or two different ones?
> If the row is locked then a SELECT will be blocked unless you are using
> NOLOCK or Read Uncommitted already. Can you give a little more details on
> exactly what code you are calling and how?
> --
> Andrew J. Kelly SQL MVP
>
> "Don Miller" <nospam@.nospam.com> wrote in message
> news:%23UGy9H$2FHA.700@.TK2MSFTNGP15.phx.gbl...
to
I
with
notification
>|||> Since my post I've tried the NOLOCK with the SELECT statement and now the
> correct rows appear more often than they did but not consistently. If I
> refresh the view, all of the target rows are there including the new row.
> I
> just don't know why the SELECT statement does not return rows when it
> should.
What view? Are you using a view on a data set? Is so then it certainly
won't just appear. If it is a SQL Server view then what does the code look
like that creates the view?

> When you say the SELECT will be blocked, what does that mean? No rows
> returned, or the rows returned by a few microseconds later when the row is
> not locked, or is some error logged?
If the row that was inserted is still in an open transaction (the outer most
commit has not been issued yet) the select will wait at any locked rows
before it will read and return the data. If the commit is happening just
before the select is run it may only wait a very brief time if at all. But
it should not return any data if the row to be read is in an open
transaction and has been modified in any way. I suspect there is more to
this than it seems when it comes to how you are reading and inserting the
data. I would like to know more of these views and how many layers are in
between the com objects and the actual data.
Andrew J. Kelly SQL MVP
"Don Miller" <nospam@.nospam.com> wrote in message
news:e4oZre$2FHA.3020@.TK2MSFTNGP15.phx.gbl...
> The entire process of entering data from my ASP web app is transactional,
> that is, if the INSERT fails (e.g. a typo error in an SP or a VBscript or
> ASP error) any changes to the database will be automatically rolled back.
> My
> VB COM+ "write" components "use transactions", my ASP code
> "requires_transaction".
> The INSERT and SELECT are done from different components and different
> connections (same login to SQLServer though).
> I haven't been using NOLOCK or Read Uncommitted in any of my SELECTs.
> As far as my code, somebody fills out a web form, I pass the form to the
> VB
> COM, that access an SP to insert the data. The same method that does the
> INSERT and returns a string to the caller also calls another component
> that
> does the SELECT, formats the returned rows as HTML and is eventually
> inserted asynchronously into an existing web page (Ajax).
> Since my post I've tried the NOLOCK with the SELECT statement and now the
> correct rows appear more often than they did but not consistently. If I
> refresh the view, all of the target rows are there including the new row.
> I
> just don't know why the SELECT statement does not return rows when it
> should.
> When you say the SELECT will be blocked, what does that mean? No rows
> returned, or the rows returned by a few microseconds later when the row is
> not locked, or is some error logged?
> Thanks for helping me out on this one.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e2ruoQ$2FHA.3880@.TK2MSFTNGP12.phx.gbl...
> and
> to
> I
> with
> notification
>sql

Location of logging for temp tables

I have a stored procedure that creates a temp table that is used to
insert data from a dynamic query. My question is does the logging of
the insert occur in the local database running the stored procedure or
does it occur in the tempdb database? My log file for local database
is growing significantly and I want to rule out that the temp table is
not writing to the local database log. I have done some testing and it
appears that is not but I just confirm my findings."Travis" <Travi424@.hotmail.com> wrote in message
news:1126279238.782948.112340@.z14g2000cwz.googlegr oups.com...
>I have a stored procedure that creates a temp table that is used to
> insert data from a dynamic query. My question is does the logging of
> the insert occur in the local database running the stored procedure or
> does it occur in the tempdb database? My log file for local database
> is growing significantly and I want to rule out that the temp table is
> not writing to the local database log. I have done some testing and it
> appears that is not but I just confirm my findings.

An INSERT into the temp table will be logged in tempdb; an INSERT from the
temp table into a permanent table will be logged in that database.

Simon|||Thanks!

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

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!