May 7, 2008 at 11:50 am
I am using the below query, where [LR-DB003] is my linked server and some other @Session variables....
SELECT A,B
into #tbl_Temp
FROM [LR-DB003].[ATL].dbo.tbl_TimeSeries TS1
inner join [LR-DB003].[ATL].dbo.tbl_home_UserFunds huf1 on huf1.SessionID = @Session2 and huf1.ID = TS1.FundID
left join dbo.tbl_funddatasource fds on fds.ID = huf1.fdsid
WHERE TS1.SessionID = @Session2 AND TS1.TimeSeriesID = 117
AND TS2.SessionID = @Session2 AND TS2.TimeSeriesID = 121
INSERT INTO [LR-DB003].[ATL].dbo.tbl_mat_Matrix (Row,Col)
SELECT A,B From #tbl_Temp
If the Select query doesn't return any result then I am getting error invalid column name A,
Invalid column name B.
However, If I don't use linked server then query works fine even if the select query returns no record.
I want to use the query with linked server (my DB would be on remote network ..)
Please suggest how to make it working using linked server
May 7, 2008 at 1:51 pm
Create the temp table first, then insert into it, instead of creating it as part of the select statement.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 8, 2008 at 2:12 am
Thanks for reply.
I thought of doing the same but multiple user would be accessing the procedure.
Any other solution..please
May 8, 2008 at 11:17 am
I have tried using your solution : Created temp table locally.
But that is also giving the same error..Invalid column name..
Could you please help
May 8, 2008 at 1:26 pm
It doesn't matter that multiple users will access the proc at the same time. Temp tables are connection-specific.
So long as your tempdb database has enough room in it (or can grow enough), it won't be a problem.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 8, 2008 at 2:41 pm
keep in mind in this context "temp table" is an #-named table.
Don't create normal tables in tempdb ! You'll loose them at sqlserver restart time!
Create table #tmp (A int not null, B varchar(128) not null)
insert into #tmp
Select col1, col1
from your_whole_world_joining_query 😉
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 8, 2008 at 4:55 pm
Ritesh, to get more help on this we are going to need more/accurate information.
I have been unable to reproduce the problem you are having, regardless of my configuration. So far...
But, I have noticed a few things about your original post. First, you reference "TS2" in your search arguments, but there is no table/alias "TS2". That's a pretty good indicator we are missing something big.
Second, where are you getting the error? Based on the original post, it could be in two different Select statements.
Can you post the actual executing code, maybe some DDL and the specific error messages?
Thanks and GL!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply