May 23, 2011 at 10:09 pm
Below is the stored procedure in my first server . I am executing the stored procedure in second server with the 4 part name convention like exec servername.dbname.dbo. Spname. while executing this in second server rows has been affected with no errors . but unable to find the temporary tables in the first server. Please help me with this.
Below is my SP
USE
[dbname]
GO
/****** Object: StoredProcedure [dbo].[spname] Script Date: 05/23/2011 23:06:37 ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER OFF
GO
CREATE
PROCEDURE [dbo].[spname]
AS
IF
(SELECT COUNT(*)
FROM tempdb..sysobjects
WHERE name LIKE '##table1%') > 0
DROP
TABLE ##table1
CREATE
TABLE ##table1
(
column1
varchar(8)COLLATE Latin1_General_CS_AS,
column2
varchar(15) COLLATE Latin1_General_CS_AS,
tworkdt
datetime ,
column3
decimal(16,2)
)
CREATE INDEX [i_bi_TempTime1] ON [dbo].[##table1]
([column1]
, [column2])
ON [PRIMARY]
IF
(SELECT COUNT(*)
FROM tempdb..sysobjects
WHERE name LIKE '##table2%') > 0
DROP
TABLE ##table2
CREATE
TABLE ##table2
(
column1varchar(8)COLLATE Latin1_General_CS_AS,
column2
varchar(15)COLLATE Latin1_General_CS_AS
)
CREATE INDEX [i_bi_TempTime2] ON [dbo].[##table2]
([column1]
, [column2])
ON [PRIMARY]
INSERT
INTO ##table1 (column1, column2,column3,column4)
SELECT
DISTINCT t.column1
,t.column2
,t.column3
,sum(t.column4)'workhrs'
FROM
tablename t (nolock)
INNER
JOIN (select column2, max(column3)'maxwork' from tablename (nolock) group by tmatter) th on t.column1=th.column1
INNER
JOIN (select max(column3)'maxdate',column2 from tablename (nolock) group by tmatter) th1 on t.column1=th1.tcolumn1 and t.column2=th1.column2 GROUP
BY t.column1, t.column2, t.column3
INSERT
INTO ##table2 (column1,column2)
SELECT
MAX(t.column1)
,t.column2
FROM
##table1 t (nolock)
INNER
JOIN (select max(column3)'maxhrs',column2
from ##table1 m (nolock) group by column2) m on t.column2=m.column2 and m.column2=t.column2
GROUP
BY t.column2
UPDATE
##table2
set
column1 = column1
from
##table2, table2
where
##table2.column1=table2.column1
and
len(column3) <= 8
GO
May 23, 2011 at 10:48 pm
Are you trying to execute the query on a second server against the first server via linked server?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 24, 2011 at 4:34 am
i dont think you can see "no of rows affected" when you use linked server query.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 24, 2011 at 5:08 am
Yes , I am trying to execute the store procedure through linked servers ...
May 24, 2011 at 5:37 am
the safest way is to use output variables or return values ( if one number ) to get that sort of info.
Generally you'd want to have set nocount on in a proc anyway - returning messages is a quick way to get all sorts of problems.
Best be precise and code to get what you want, not leave it to chance.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 24, 2011 at 6:27 am
yes, I am using linked servers
May 24, 2011 at 1:07 pm
Since nobody clarified this for you...the temp tables reside on the server where the code is executed. So if Server1 calls a sproc on Server2 and the code in the sproc creates a temp table, that temp table is not visible on Server1. You will need to have your sproc return the data you want.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 24, 2011 at 1:17 pm
If a Global Temporary Table has no connections to it then it is deleted.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply