I want to know how inserted and deleted temp tables in SQL server work. My question is more regarding how they work when multiple users accessing the same database. Suppose two users update the database at the same time. In that case what are the values stored in the inserted and deleted tables.
I have a trigger that records changes to the database as in an audit trail. Like any other audit trail I insert data into my audit table from the inserted and deleted temp tables in MS SQL Server. I however am not clear as to how these inserted and deleted tables store values when two users update the database at the same time. Are there separate inserted and deleted tables for each session. The users access the database thru ASP pages.
The audit trail I am trying to use is http://www.nigelrivett.net/AuditTrailTrigger.html
I actually would like to store the inserted and deleted temp tables into other temporary tables so that I can access these tables thru a stored procedure. This is when the problem of same users updating the temporary tables is more pronounced.
Thanks in advance.If memory serves, the INSERTED and DELETED tables are special objects that belong to each session. So if two users update at the same time you should have two copies of these virtual tables
User1INSERTED/DELETED
User2INSERTED/DELETED
Brent
Showing posts with label temp. Show all posts
Showing posts with label temp. Show all posts
Wednesday, March 28, 2012
Monday, March 26, 2012
Insert with a Identity
Say you have a temp table with three columns (f1,f2,f3)
and you want to insert them into a permanent table that
has four columns (f1,f2,f3,f4) where f4 is an identity
column. Assuming you don't want to DTS, what is the
correct syntax to do this? I have tried things like
insert into permtable
select * from #temptable
values (f1, f2, f3)
but of course it fails. Any ideas?
If you're not sensitive about what value gets placed in the identity column,
then all you need to do is qualify your destination columns in the insert.
That, and you don't want the "values..." line in your insert...select.
insert into permtable (colA, colB, colC)
select f1, f2, f3 from #temptable
"HTX" wrote:
> Say you have a temp table with three columns (f1,f2,f3)
> and you want to insert them into a permanent table that
> has four columns (f1,f2,f3,f4) where f4 is an identity
> column. Assuming you don't want to DTS, what is the
> correct syntax to do this? I have tried things like
> insert into permtable
> select * from #temptable
> values (f1, f2, f3)
> but of course it fails. Any ideas?
>
|||"HTX" <anonymous@.discussions.microsoft.com> wrote in message
news:01e201c4a589$d2355670$a401280a@.phx.gbl...
> Say you have a temp table with three columns (f1,f2,f3)
> and you want to insert them into a permanent table that
> has four columns (f1,f2,f3,f4) where f4 is an identity
> column. Assuming you don't want to DTS, what is the
> correct syntax to do this? I have tried things like
> insert into permtable
> select * from #temptable
> values (f1, f2, f3)
> but of course it fails. Any ideas?
Have you tried:
insert into permtable (f1, f2, f3)
select * from #temptable
Steve
and you want to insert them into a permanent table that
has four columns (f1,f2,f3,f4) where f4 is an identity
column. Assuming you don't want to DTS, what is the
correct syntax to do this? I have tried things like
insert into permtable
select * from #temptable
values (f1, f2, f3)
but of course it fails. Any ideas?
If you're not sensitive about what value gets placed in the identity column,
then all you need to do is qualify your destination columns in the insert.
That, and you don't want the "values..." line in your insert...select.
insert into permtable (colA, colB, colC)
select f1, f2, f3 from #temptable
"HTX" wrote:
> Say you have a temp table with three columns (f1,f2,f3)
> and you want to insert them into a permanent table that
> has four columns (f1,f2,f3,f4) where f4 is an identity
> column. Assuming you don't want to DTS, what is the
> correct syntax to do this? I have tried things like
> insert into permtable
> select * from #temptable
> values (f1, f2, f3)
> but of course it fails. Any ideas?
>
|||"HTX" <anonymous@.discussions.microsoft.com> wrote in message
news:01e201c4a589$d2355670$a401280a@.phx.gbl...
> Say you have a temp table with three columns (f1,f2,f3)
> and you want to insert them into a permanent table that
> has four columns (f1,f2,f3,f4) where f4 is an identity
> column. Assuming you don't want to DTS, what is the
> correct syntax to do this? I have tried things like
> insert into permtable
> select * from #temptable
> values (f1, f2, f3)
> but of course it fails. Any ideas?
Have you tried:
insert into permtable (f1, f2, f3)
select * from #temptable
Steve
Insert with a Identity
Say you have a temp table with three columns (f1,f2,f3)
and you want to insert them into a permanent table that
has four columns (f1,f2,f3,f4) where f4 is an identity
column. Assuming you don't want to DTS, what is the
correct syntax to do this? I have tried things like
insert into permtable
select * from #temptable
values (f1, f2, f3)
but of course it fails. Any ideas?If you're not sensitive about what value gets placed in the identity column,
then all you need to do is qualify your destination columns in the insert.
That, and you don't want the "values..." line in your insert...select.
insert into permtable (colA, colB, colC)
select f1, f2, f3 from #temptable
"HTX" wrote:
> Say you have a temp table with three columns (f1,f2,f3)
> and you want to insert them into a permanent table that
> has four columns (f1,f2,f3,f4) where f4 is an identity
> column. Assuming you don't want to DTS, what is the
> correct syntax to do this? I have tried things like
> insert into permtable
> select * from #temptable
> values (f1, f2, f3)
> but of course it fails. Any ideas?
>|||"HTX" <anonymous@.discussions.microsoft.com> wrote in message
news:01e201c4a589$d2355670$a401280a@.phx.gbl...
> Say you have a temp table with three columns (f1,f2,f3)
> and you want to insert them into a permanent table that
> has four columns (f1,f2,f3,f4) where f4 is an identity
> column. Assuming you don't want to DTS, what is the
> correct syntax to do this? I have tried things like
> insert into permtable
> select * from #temptable
> values (f1, f2, f3)
> but of course it fails. Any ideas?
Have you tried:
insert into permtable (f1, f2, f3)
select * from #temptable
Steve
and you want to insert them into a permanent table that
has four columns (f1,f2,f3,f4) where f4 is an identity
column. Assuming you don't want to DTS, what is the
correct syntax to do this? I have tried things like
insert into permtable
select * from #temptable
values (f1, f2, f3)
but of course it fails. Any ideas?If you're not sensitive about what value gets placed in the identity column,
then all you need to do is qualify your destination columns in the insert.
That, and you don't want the "values..." line in your insert...select.
insert into permtable (colA, colB, colC)
select f1, f2, f3 from #temptable
"HTX" wrote:
> Say you have a temp table with three columns (f1,f2,f3)
> and you want to insert them into a permanent table that
> has four columns (f1,f2,f3,f4) where f4 is an identity
> column. Assuming you don't want to DTS, what is the
> correct syntax to do this? I have tried things like
> insert into permtable
> select * from #temptable
> values (f1, f2, f3)
> but of course it fails. Any ideas?
>|||"HTX" <anonymous@.discussions.microsoft.com> wrote in message
news:01e201c4a589$d2355670$a401280a@.phx.gbl...
> Say you have a temp table with three columns (f1,f2,f3)
> and you want to insert them into a permanent table that
> has four columns (f1,f2,f3,f4) where f4 is an identity
> column. Assuming you don't want to DTS, what is the
> correct syntax to do this? I have tried things like
> insert into permtable
> select * from #temptable
> values (f1, f2, f3)
> but of course it fails. Any ideas?
Have you tried:
insert into permtable (f1, f2, f3)
select * from #temptable
Steve
Friday, March 23, 2012
Insert unique rows in temp table
i have temp table name "#TempResult" with column names Memberid,Month,Year. Consider this temp table alredy has some rows from previuos query. I have one more table name "Rebate" which also has columns MemberID,Month, Year and some more columns. Now i wanted to insert rows from "Rebate" Table into Temp Table where MemberID.Month and Year DOES NOT exist in Temp table.
MemberID + Month + Year should ne unique in Temp table
I don't think what you are doing is valid because a local temp table scope is very limited, but if it valid it will be covered in the link below by one of the best minds in T-SQL. Hope this helps.
http://www.awprofessional.com/articles/article.asp?p=25288&seqNum=4&rl=1
Wednesday, March 21, 2012
Insert to Subset of Columns
In a sp I am populating a temp table that may have a number of null column
values. I'd like to populate the columns that will not have null values by
using an INSERT statement, and then separately UPDATE the other columns
which may have null values.
When I execute the INSERT statement to populate *only* the MemberID,
LinkText, and TargetURL columns, I get the following error:
<< Insert Error: Column name or number of supplied values does not match
table definition >>
My Question: Must I specify some value for all columns in the INSERT
statement, or is there a way to insert into a subset of the columns and then
update the rest later?
CREATE TABLE #LINKS
(
MemberID int,
LinkText varchar(100),
TargetURL varchar(100),
ImageURL varchar(150) NULL,
ActiveImageURL varchar(150) NULL,
ExpandedImageURL varchar(150) NULL,
HoverImageURL varchar(150) NULL,
ImageHeight int NULL,
ImageWidth int NULL,
)DO you mean that ? :
INSERT INTO (Column1[,COlumn2...])
VALUES (Value1[,Value2...])
--OR
INSERT INTO (Column1[,COlumn2...])
<Query>
Refer to the BOL there are some example in there.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Smithers" <A@.B.com> schrieb im Newsbeitrag
news:%23%234o0ZjWFHA.2692@.TK2MSFTNGP15.phx.gbl...
> In a sp I am populating a temp table that may have a number of null column
> values. I'd like to populate the columns that will not have null values by
> using an INSERT statement, and then separately UPDATE the other columns
> which may have null values.
> When I execute the INSERT statement to populate *only* the MemberID,
> LinkText, and TargetURL columns, I get the following error:
> << Insert Error: Column name or number of supplied values does not match
> table definition >>
> My Question: Must I specify some value for all columns in the INSERT
> statement, or is there a way to insert into a subset of the columns and
> then update the rest later?
> CREATE TABLE #LINKS
> (
> MemberID int,
> LinkText varchar(100),
> TargetURL varchar(100),
> ImageURL varchar(150) NULL,
> ActiveImageURL varchar(150) NULL,
> ExpandedImageURL varchar(150) NULL,
> HoverImageURL varchar(150) NULL,
> ImageHeight int NULL,
> ImageWidth int NULL,
> )
>|||You have two options. Assume table has three columns, and you don't want to
insert in the first or
third columns:
INSERT INTO tblname(first, second, third)
VALUES(default, 23, default)
INSERT INTO tblname(second)
VALUES(23)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Smithers" <A@.B.com> wrote in message news:%23%234o0ZjWFHA.2692@.TK2MSFTNGP15.phx.gbl...[col
or=darkred]
> In a sp I am populating a temp table that may have a number of null column
values. I'd like to
> populate the columns that will not have null values by using an INSERT sta
tement, and then
> separately UPDATE the other columns which may have null values.
> When I execute the INSERT statement to populate *only* the MemberID, LinkT
ext, and TargetURL
> columns, I get the following error:
> << Insert Error: Column name or number of supplied values does not match t
able definition >>
> My Question: Must I specify some value for all columns in the INSERT state
ment, or is there a way
> to insert into a subset of the columns and then update the rest later?
> CREATE TABLE #LINKS
> (
> MemberID int,
> LinkText varchar(100),
> TargetURL varchar(100),
> ImageURL varchar(150) NULL,
> ActiveImageURL varchar(150) NULL,
> ExpandedImageURL varchar(150) NULL,
> HoverImageURL varchar(150) NULL,
> ImageHeight int NULL,
> ImageWidth int NULL,
> )
>[/color]|||INSERT INTO #LINKS (MemberID, LinkText, TargetURL)
VALUES (<MemberID>, <LinkText>, <TargetURL> )
works.
How do you exactly populate that table, because that error message doesn't
look familiar?
Jacco Schalkwijk
SQL Server MVP
"Smithers" <A@.B.com> wrote in message
news:%23%234o0ZjWFHA.2692@.TK2MSFTNGP15.phx.gbl...
> In a sp I am populating a temp table that may have a number of null column
> values. I'd like to populate the columns that will not have null values by
> using an INSERT statement, and then separately UPDATE the other columns
> which may have null values.
> When I execute the INSERT statement to populate *only* the MemberID,
> LinkText, and TargetURL columns, I get the following error:
> << Insert Error: Column name or number of supplied values does not match
> table definition >>
> My Question: Must I specify some value for all columns in the INSERT
> statement, or is there a way to insert into a subset of the columns and
> then update the rest later?
> CREATE TABLE #LINKS
> (
> MemberID int,
> LinkText varchar(100),
> TargetURL varchar(100),
> ImageURL varchar(150) NULL,
> ActiveImageURL varchar(150) NULL,
> ExpandedImageURL varchar(150) NULL,
> HoverImageURL varchar(150) NULL,
> ImageHeight int NULL,
> ImageWidth int NULL,
> )
>|||INSERT INTO tbl
VALUES(111,222, DEFAULT, DEFAULT, N'bla-bla', DEFAULT, ....)
Message posted via http://www.webservertalk.com
values. I'd like to populate the columns that will not have null values by
using an INSERT statement, and then separately UPDATE the other columns
which may have null values.
When I execute the INSERT statement to populate *only* the MemberID,
LinkText, and TargetURL columns, I get the following error:
<< Insert Error: Column name or number of supplied values does not match
table definition >>
My Question: Must I specify some value for all columns in the INSERT
statement, or is there a way to insert into a subset of the columns and then
update the rest later?
CREATE TABLE #LINKS
(
MemberID int,
LinkText varchar(100),
TargetURL varchar(100),
ImageURL varchar(150) NULL,
ActiveImageURL varchar(150) NULL,
ExpandedImageURL varchar(150) NULL,
HoverImageURL varchar(150) NULL,
ImageHeight int NULL,
ImageWidth int NULL,
)DO you mean that ? :
INSERT INTO (Column1[,COlumn2...])
VALUES (Value1[,Value2...])
--OR
INSERT INTO (Column1[,COlumn2...])
<Query>
Refer to the BOL there are some example in there.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Smithers" <A@.B.com> schrieb im Newsbeitrag
news:%23%234o0ZjWFHA.2692@.TK2MSFTNGP15.phx.gbl...
> In a sp I am populating a temp table that may have a number of null column
> values. I'd like to populate the columns that will not have null values by
> using an INSERT statement, and then separately UPDATE the other columns
> which may have null values.
> When I execute the INSERT statement to populate *only* the MemberID,
> LinkText, and TargetURL columns, I get the following error:
> << Insert Error: Column name or number of supplied values does not match
> table definition >>
> My Question: Must I specify some value for all columns in the INSERT
> statement, or is there a way to insert into a subset of the columns and
> then update the rest later?
> CREATE TABLE #LINKS
> (
> MemberID int,
> LinkText varchar(100),
> TargetURL varchar(100),
> ImageURL varchar(150) NULL,
> ActiveImageURL varchar(150) NULL,
> ExpandedImageURL varchar(150) NULL,
> HoverImageURL varchar(150) NULL,
> ImageHeight int NULL,
> ImageWidth int NULL,
> )
>|||You have two options. Assume table has three columns, and you don't want to
insert in the first or
third columns:
INSERT INTO tblname(first, second, third)
VALUES(default, 23, default)
INSERT INTO tblname(second)
VALUES(23)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Smithers" <A@.B.com> wrote in message news:%23%234o0ZjWFHA.2692@.TK2MSFTNGP15.phx.gbl...[col
or=darkred]
> In a sp I am populating a temp table that may have a number of null column
values. I'd like to
> populate the columns that will not have null values by using an INSERT sta
tement, and then
> separately UPDATE the other columns which may have null values.
> When I execute the INSERT statement to populate *only* the MemberID, LinkT
ext, and TargetURL
> columns, I get the following error:
> << Insert Error: Column name or number of supplied values does not match t
able definition >>
> My Question: Must I specify some value for all columns in the INSERT state
ment, or is there a way
> to insert into a subset of the columns and then update the rest later?
> CREATE TABLE #LINKS
> (
> MemberID int,
> LinkText varchar(100),
> TargetURL varchar(100),
> ImageURL varchar(150) NULL,
> ActiveImageURL varchar(150) NULL,
> ExpandedImageURL varchar(150) NULL,
> HoverImageURL varchar(150) NULL,
> ImageHeight int NULL,
> ImageWidth int NULL,
> )
>[/color]|||INSERT INTO #LINKS (MemberID, LinkText, TargetURL)
VALUES (<MemberID>, <LinkText>, <TargetURL> )
works.
How do you exactly populate that table, because that error message doesn't
look familiar?
Jacco Schalkwijk
SQL Server MVP
"Smithers" <A@.B.com> wrote in message
news:%23%234o0ZjWFHA.2692@.TK2MSFTNGP15.phx.gbl...
> In a sp I am populating a temp table that may have a number of null column
> values. I'd like to populate the columns that will not have null values by
> using an INSERT statement, and then separately UPDATE the other columns
> which may have null values.
> When I execute the INSERT statement to populate *only* the MemberID,
> LinkText, and TargetURL columns, I get the following error:
> << Insert Error: Column name or number of supplied values does not match
> table definition >>
> My Question: Must I specify some value for all columns in the INSERT
> statement, or is there a way to insert into a subset of the columns and
> then update the rest later?
> CREATE TABLE #LINKS
> (
> MemberID int,
> LinkText varchar(100),
> TargetURL varchar(100),
> ImageURL varchar(150) NULL,
> ActiveImageURL varchar(150) NULL,
> ExpandedImageURL varchar(150) NULL,
> HoverImageURL varchar(150) NULL,
> ImageHeight int NULL,
> ImageWidth int NULL,
> )
>|||INSERT INTO tbl
VALUES(111,222, DEFAULT, DEFAULT, N'bla-bla', DEFAULT, ....)
Message posted via http://www.webservertalk.com
insert tmpTable vs Table
Is there a difference between inserting data into a temp table vs a real
table? I'm using an SP and I created a temp table. Then tried to INSERT and
failed. Then, for troubleshooting, I created a real table and INSERT works
fine. What characteristic about temp tables am I missing?
Both tables exist and I do a SELECT * on both. But only the real table has
records.
thanks
CREATE PROCEDURE stp_DOD_TrackNumbers
@.PID int, @.SK int, @.IDD int
AS
--if exists (select * from dbo.sysobjects where id =
object_id(N'[#tmpDODSongs]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
--drop table [#tmpDODSongs]
--CREATE TABLE [#tmpDODSongs] (
-- [ProjectID] [numeric](18, 0) NOT NULL ,
-- [SortKey] [numeric](18, 0) NULL ,
-- [OldIDD] [numeric](18, 0) NULL ,
-- [ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL
--) ON [PRIMARY]
INSERT INTO #tmpDODSongs (ProjectID, SortKey, OldIDD)
VALUES (@.PID, @.SK, @.IDD)
--INSERT INTO tmpDODSongs (ProjectID, SortKey, OldIDD)
--VALUES (@.PID, @.SK, @.IDD)
-- perform other tasks here before dropping temp table
--drop table [#tmpDODSongs]
GO> Then tried to INSERT and failed.
Could you be a bit more specific?|||I created a temp table #tmpDODSongs and logical table tmpDODSongs.
Both with the same attributes, structure, etc.
Using the same 17 records...
I could INSERT INTO the logical table tmpDODSongs with no problem.
When I tried to INSERT INTO temp table #tmpDODSongs - no records were
inserted.
I did not get any errors, just no inserted recods.
I used the same SP and would comment out one INSERT statement of the other.
I was just wondering if there was a consideration I needed to make for temp
tables.
thanks
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23nGpR766FHA.1188@.TK2MSFTNGP12.phx.gbl...
> Could you be a bit more specific?
>|||Where in the process is the select from the table?
shank wrote:
> Is there a difference between inserting data into a temp table vs a real
> table? I'm using an SP and I created a temp table. Then tried to INSERT an
d
> failed. Then, for troubleshooting, I created a real table and INSERT works
> fine. What characteristic about temp tables am I missing?
> Both tables exist and I do a SELECT * on both. But only the real table has
> records.
> thanks
> CREATE PROCEDURE stp_DOD_TrackNumbers
> @.PID int, @.SK int, @.IDD int
> AS
> --if exists (select * from dbo.sysobjects where id =
> object_id(N'[#tmpDODSongs]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> --drop table [#tmpDODSongs]
> --CREATE TABLE [#tmpDODSongs] (
> -- [ProjectID] [numeric](18, 0) NOT NULL ,
> -- [SortKey] [numeric](18, 0) NULL ,
> -- [OldIDD] [numeric](18, 0) NULL ,
> -- [ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL
> --) ON [PRIMARY]
>
> INSERT INTO #tmpDODSongs (ProjectID, SortKey, OldIDD)
> VALUES (@.PID, @.SK, @.IDD)
> --INSERT INTO tmpDODSongs (ProjectID, SortKey, OldIDD)
> --VALUES (@.PID, @.SK, @.IDD)
>
> -- perform other tasks here before dropping temp table
> --drop table [#tmpDODSongs]
> GO
>|||I'm using QA to select the tracks from each table for the sake of
troubleshooting.
SELECT *
FROM #tmpDODSongs
SELECT *
FROM tmpDODSongs
thanks
"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:e25lJP76FHA.636@.TK2MSFTNGP10.phx.gbl...
> Where in the process is the select from the table?
> shank wrote:|||"shank" <shank@.tampabay.rr.com> wrote in
news:#nx8da76FHA.3752@.tk2msftngp13.phx.gbl:
> I'm using QA to select the tracks from each table for the sake of
> troubleshooting.
> SELECT *
> FROM #tmpDODSongs
[snip]
[snip]
Well, as per the code above, you drop the temp table - so if you try to
select out of it afterwards, you wouldn't get any data - would you?
Niels
table? I'm using an SP and I created a temp table. Then tried to INSERT and
failed. Then, for troubleshooting, I created a real table and INSERT works
fine. What characteristic about temp tables am I missing?
Both tables exist and I do a SELECT * on both. But only the real table has
records.
thanks
CREATE PROCEDURE stp_DOD_TrackNumbers
@.PID int, @.SK int, @.IDD int
AS
--if exists (select * from dbo.sysobjects where id =
object_id(N'[#tmpDODSongs]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
--drop table [#tmpDODSongs]
--CREATE TABLE [#tmpDODSongs] (
-- [ProjectID] [numeric](18, 0) NOT NULL ,
-- [SortKey] [numeric](18, 0) NULL ,
-- [OldIDD] [numeric](18, 0) NULL ,
-- [ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL
--) ON [PRIMARY]
INSERT INTO #tmpDODSongs (ProjectID, SortKey, OldIDD)
VALUES (@.PID, @.SK, @.IDD)
--INSERT INTO tmpDODSongs (ProjectID, SortKey, OldIDD)
--VALUES (@.PID, @.SK, @.IDD)
-- perform other tasks here before dropping temp table
--drop table [#tmpDODSongs]
GO> Then tried to INSERT and failed.
Could you be a bit more specific?|||I created a temp table #tmpDODSongs and logical table tmpDODSongs.
Both with the same attributes, structure, etc.
Using the same 17 records...
I could INSERT INTO the logical table tmpDODSongs with no problem.
When I tried to INSERT INTO temp table #tmpDODSongs - no records were
inserted.
I did not get any errors, just no inserted recods.
I used the same SP and would comment out one INSERT statement of the other.
I was just wondering if there was a consideration I needed to make for temp
tables.
thanks
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23nGpR766FHA.1188@.TK2MSFTNGP12.phx.gbl...
> Could you be a bit more specific?
>|||Where in the process is the select from the table?
shank wrote:
> Is there a difference between inserting data into a temp table vs a real
> table? I'm using an SP and I created a temp table. Then tried to INSERT an
d
> failed. Then, for troubleshooting, I created a real table and INSERT works
> fine. What characteristic about temp tables am I missing?
> Both tables exist and I do a SELECT * on both. But only the real table has
> records.
> thanks
> CREATE PROCEDURE stp_DOD_TrackNumbers
> @.PID int, @.SK int, @.IDD int
> AS
> --if exists (select * from dbo.sysobjects where id =
> object_id(N'[#tmpDODSongs]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> --drop table [#tmpDODSongs]
> --CREATE TABLE [#tmpDODSongs] (
> -- [ProjectID] [numeric](18, 0) NOT NULL ,
> -- [SortKey] [numeric](18, 0) NULL ,
> -- [OldIDD] [numeric](18, 0) NULL ,
> -- [ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL
> --) ON [PRIMARY]
>
> INSERT INTO #tmpDODSongs (ProjectID, SortKey, OldIDD)
> VALUES (@.PID, @.SK, @.IDD)
> --INSERT INTO tmpDODSongs (ProjectID, SortKey, OldIDD)
> --VALUES (@.PID, @.SK, @.IDD)
>
> -- perform other tasks here before dropping temp table
> --drop table [#tmpDODSongs]
> GO
>|||I'm using QA to select the tracks from each table for the sake of
troubleshooting.
SELECT *
FROM #tmpDODSongs
SELECT *
FROM tmpDODSongs
thanks
"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:e25lJP76FHA.636@.TK2MSFTNGP10.phx.gbl...
> Where in the process is the select from the table?
> shank wrote:|||"shank" <shank@.tampabay.rr.com> wrote in
news:#nx8da76FHA.3752@.tk2msftngp13.phx.gbl:
> I'm using QA to select the tracks from each table for the sake of
> troubleshooting.
> SELECT *
> FROM #tmpDODSongs
[snip]
[snip]
Well, as per the code above, you drop the temp table - so if you try to
select out of it afterwards, you wouldn't get any data - would you?
Niels
Monday, March 19, 2012
insert temp with linked server
Hi,
I have a problem with insert temp table with linked server. I have a sql
2000 with sp4 and I have a dozen linked servers. All of them work except one
which is running on windows 2003. I didn't get any error message and the
transaction just open and never stop. I have to kill it manually.
I have checked the DTC on windows 2003, which is quite different. I am sure
that the settings are all right, including tricks like network service, etc.
I guess it might be some kind of bug but I just can't find anything about it
after searching Google and MS KB.
Is anyone running same problem or has any clue,
Thanks,
m
I think I've got more or less the same problem.
Since I've load 2000 SP4 the data return from a SP called to a Default
instance is not the same as in the case of a Named Instance. Instead it does
not return a error but return false results.
IOW :
Before SP4 --> No Problems
After SP4 -->
exec[missql01].SERVER_CONTROL_DB.DBO.sp_Is_Job_Running
'Free_Disk_Space_Watcher',@.Result OUTPUT
/* For any Default Instance : Return Correct Results */
exec [M24BLACKB01\BES_001].SERVER_CONTROL_DB.DBO.sp_Is_Job_Running
'Free_Disk_Space_Watcher',@.Result OUTPUT
/* For Any Named Instance : Return INCORRECT Results */
Same command on Local server M24BLACKB01\BES_001:
exec SERVER_CONTROL_DB.DBO.sp_Is_Job_Running
'Free_Disk_Space_Watcher',@.Result OUTPUT
/* Return CORRECT Results */
"dp" wrote:
> Hi,
> I have a problem with insert temp table with linked server. I have a sql
> 2000 with sp4 and I have a dozen linked servers. All of them work except one
> which is running on windows 2003. I didn't get any error message and the
> transaction just open and never stop. I have to kill it manually.
> I have checked the DTC on windows 2003, which is quite different. I am sure
> that the settings are all right, including tricks like network service, etc.
> I guess it might be some kind of bug but I just can't find anything about it
> after searching Google and MS KB.
> Is anyone running same problem or has any clue,
> Thanks,
>
> --
> m
I have a problem with insert temp table with linked server. I have a sql
2000 with sp4 and I have a dozen linked servers. All of them work except one
which is running on windows 2003. I didn't get any error message and the
transaction just open and never stop. I have to kill it manually.
I have checked the DTC on windows 2003, which is quite different. I am sure
that the settings are all right, including tricks like network service, etc.
I guess it might be some kind of bug but I just can't find anything about it
after searching Google and MS KB.
Is anyone running same problem or has any clue,
Thanks,
m
I think I've got more or less the same problem.
Since I've load 2000 SP4 the data return from a SP called to a Default
instance is not the same as in the case of a Named Instance. Instead it does
not return a error but return false results.
IOW :
Before SP4 --> No Problems
After SP4 -->
exec[missql01].SERVER_CONTROL_DB.DBO.sp_Is_Job_Running
'Free_Disk_Space_Watcher',@.Result OUTPUT
/* For any Default Instance : Return Correct Results */
exec [M24BLACKB01\BES_001].SERVER_CONTROL_DB.DBO.sp_Is_Job_Running
'Free_Disk_Space_Watcher',@.Result OUTPUT
/* For Any Named Instance : Return INCORRECT Results */
Same command on Local server M24BLACKB01\BES_001:
exec SERVER_CONTROL_DB.DBO.sp_Is_Job_Running
'Free_Disk_Space_Watcher',@.Result OUTPUT
/* Return CORRECT Results */
"dp" wrote:
> Hi,
> I have a problem with insert temp table with linked server. I have a sql
> 2000 with sp4 and I have a dozen linked servers. All of them work except one
> which is running on windows 2003. I didn't get any error message and the
> transaction just open and never stop. I have to kill it manually.
> I have checked the DTC on windows 2003, which is quite different. I am sure
> that the settings are all right, including tricks like network service, etc.
> I guess it might be some kind of bug but I just can't find anything about it
> after searching Google and MS KB.
> Is anyone running same problem or has any clue,
> Thanks,
>
> --
> m
insert temp with linked server
Hi,
I have a problem with insert temp table with linked server. I have a sql
2000 with sp4 and I have a dozen linked servers. All of them work except one
which is running on windows 2003. I didn't get any error message and the
transaction just open and never stop. I have to kill it manually.
I have checked the DTC on windows 2003, which is quite different. I am sure
that the settings are all right, including tricks like network service, etc.
I guess it might be some kind of bug but I just can't find anything about it
after searching Google and MS KB.
Is anyone running same problem or has any clue,
Thanks,
mI think I've got more or less the same problem.
Since I've load 2000 SP4 the data return from a SP called to a Default
instance is not the same as in the case of a Named Instance. Instead it does
not return a error but return false results.
IOW :
Before SP4 --> No Problems
After SP4 -->
exec[missql01].SERVER_CONTROL_DB.DBO.sp_Is_Job_Running
'Free_Disk_Space_Watcher',@.Result OUTPUT
/* For any Default Instance : Return Correct Results */
exec [M24BLACKB01\BES_001].SERVER_CONTROL_DB.DBO.sp_Is_Job_Running
'Free_Disk_Space_Watcher',@.Result OUTPUT
/* For Any Named Instance : Return INCORRECT Results */
Same command on Local server M24BLACKB01\BES_001:
exec SERVER_CONTROL_DB.DBO.sp_Is_Job_Running
'Free_Disk_Space_Watcher',@.Result OUTPUT
/* Return CORRECT Results */
"dp" wrote:
> Hi,
> I have a problem with insert temp table with linked server. I have a sql
> 2000 with sp4 and I have a dozen linked servers. All of them work except o
ne
> which is running on windows 2003. I didn't get any error message and the
> transaction just open and never stop. I have to kill it manually.
> I have checked the DTC on windows 2003, which is quite different. I am sur
e
> that the settings are all right, including tricks like network service, et
c.
> I guess it might be some kind of bug but I just can't find anything about
it
> after searching Google and MS KB.
> Is anyone running same problem or has any clue,
> Thanks,
>
> --
> m
I have a problem with insert temp table with linked server. I have a sql
2000 with sp4 and I have a dozen linked servers. All of them work except one
which is running on windows 2003. I didn't get any error message and the
transaction just open and never stop. I have to kill it manually.
I have checked the DTC on windows 2003, which is quite different. I am sure
that the settings are all right, including tricks like network service, etc.
I guess it might be some kind of bug but I just can't find anything about it
after searching Google and MS KB.
Is anyone running same problem or has any clue,
Thanks,
mI think I've got more or less the same problem.
Since I've load 2000 SP4 the data return from a SP called to a Default
instance is not the same as in the case of a Named Instance. Instead it does
not return a error but return false results.
IOW :
Before SP4 --> No Problems
After SP4 -->
exec[missql01].SERVER_CONTROL_DB.DBO.sp_Is_Job_Running
'Free_Disk_Space_Watcher',@.Result OUTPUT
/* For any Default Instance : Return Correct Results */
exec [M24BLACKB01\BES_001].SERVER_CONTROL_DB.DBO.sp_Is_Job_Running
'Free_Disk_Space_Watcher',@.Result OUTPUT
/* For Any Named Instance : Return INCORRECT Results */
Same command on Local server M24BLACKB01\BES_001:
exec SERVER_CONTROL_DB.DBO.sp_Is_Job_Running
'Free_Disk_Space_Watcher',@.Result OUTPUT
/* Return CORRECT Results */
"dp" wrote:
> Hi,
> I have a problem with insert temp table with linked server. I have a sql
> 2000 with sp4 and I have a dozen linked servers. All of them work except o
ne
> which is running on windows 2003. I didn't get any error message and the
> transaction just open and never stop. I have to kill it manually.
> I have checked the DTC on windows 2003, which is quite different. I am sur
e
> that the settings are all right, including tricks like network service, et
c.
> I guess it might be some kind of bug but I just can't find anything about
it
> after searching Google and MS KB.
> Is anyone running same problem or has any clue,
> Thanks,
>
> --
> m
Subscribe to:
Posts (Atom)