Alternative to INSERT EXEC in nested query? Help

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Alternative to INSERT EXEC in nested query? Help

Author

Topic

djorre
Yak Posting Veteran

94 Posts

Posted - 2010-11-08 : 07:19:13

Can somebody please help me because I seem not to find any way to solve this. When the user wants to show the tag elements, then the tag database needs to be updated first. This happens over a linked server.

Simplified:

SP ShowTags
BEGIN
INSERT INTO #WebSetFilters (Id, Name)
EXEC dbo.webGetTags 
Select * from #WebSetFilters
END

SP WebGetTags
EXEC stpUpdateTags
select * from tblTags
...

SP stpUpdateTags
	SET @selectString='SELECT tag FROM [classic] WHERE (pointsource = ' + '''FTLD''' + ' and pointtypex = ' + '''float32''' + ')'

        SET @selectString = N'select * from OPENQUERY(PI_HISTORIAN, ''' + 
		REPLACE(@selectString, '''', '''''') + ''')'
	insert into #PI_HISTORIAN_PIPOINT
	EXEC (@selectString)
        insert into tblTags 
        select * from #PI_HISTORIAN_PIPOINT

"INSERT EXEC cannot be done inside nested query"

I seem not to find a way around this, except for putting all my code into a single stored procedure but that is not an option.

I can not use 'openquery' because I have a variable querystring.
I can not use functions because EXEC is not allowed in functions.

Please help.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-11-08 : 11:36:54

Best option is probably to replicate the [classic] table to the server that is running this code. Then you have the table locally.
OR possibly:

You can create a parameterized SP on your remote server that will return tags from [classic] for @pointsource and @pointtypex.
Then:
insert into #pi_historian_pippoint
exec <server>.<db>.<owner>.<SP> [<parameter1=value>[,parameter2=value...]]

You would need to make sure you have remote procedure calls and distributed transaction coordinator configured properly.

Be One with the Optimizer
TG

djorre
Yak Posting Veteran

94 Posts

Posted - 2010-11-09 : 05:15:05

quote:
Originally posted by TG

Best option is probably to replicate the [classic] table to the server that is running this code. Then you have the table locally.
...

Thank you.. but I think that is exactly what I am already trying to do here?

I am putting it in a temp table and then copying the temp table to tblTags.

quote:
You can create a parameterized SP on your remote server that will return tags from [classic] for @pointsource and @pointtypex.

Yes this might be the idea, a stored procedure that does not insert this data into a temp table but just has it as the output query. Thanks I will try this.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-11-09 : 08:40:30

quote:
Originally posted by djorre

quote:
Originally posted by TG

Best option is probably to replicate the [classic] table to the server that is running this code. Then you have the table locally.
...

Thank you.. but I think that is exactly what I am already trying to do here?

I am putting it in a temp table and then copying the temp table to tblTags.

quote:
You can create a parameterized SP on your remote server that will return tags from [classic] for @pointsource and @pointtypex.

Yes this might be the idea, a stored procedure that does not insert this data into a temp table but just has it as the output query. Thanks I will try this.

I mean use sql server's built in replication...not a home-grown technique.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources