October 26, 2010 at 2:39 pm
I have an OpenQuery
Select A, B, C
From OpenQuery
(MyServer, 'Select A, B, C, From Table_A')
As vTable
I know that normally you cannot use parameters with OpenQueries, but!
I tried this in a stored procedure
Declare @strMyParameter nvarchar(25);
Set @strMyParameter = 'MyName';
strSQL = '
Select A, B, C
From OpenQuery
(MyServer, ''Select A, B, C, From Table_A Where Variable = ' + @strMyParameter + ''')
As vTable'
Execute (strSQL)
Of course I can edit the strSQL and insert any paramater I need which works well.
But how do I make this last example (one with parameters) a table that can be used in a Select From blah, blah, blah...
Which can be joined to other tables, etc...
Is there a way to use Execute in Table-valued Functions?
ANY HELP would be appreciated. This is the last piece of a long project.
P.S. I finally got all of the tables joined with the calcs in a stored procedure and all without using Row by Row calculations. Yeah!!! You guys were right, again. I owe you. This is part of it and the last piece.
Thank you,
October 26, 2010 at 2:56 pm
You can probably use Insert...Execute. Just have the Execute(@SQL) statement where you would normally put a Values or Select statement. I know it works with procs, and I think it works with dynamic SQL.
- 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
October 26, 2010 at 3:12 pm
Can you elaborate?
I'm not sure what you mean and you I would do the Left Join .... On ...
October 27, 2010 at 6:36 am
Create a temp table, Insert...Exec into that, then join to the temp table.
create table #T (Col1 int, Col2 int);
insert into #T (Col1, Col2)
exec ('My script that returns two columns');
select *
from MyTable
inner join #T
on X = Y;
Something like that.
- 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
October 27, 2010 at 8:59 am
No go!
This works fine:
create table #T (Col1 int, Col2 int);
insert into #T (Col1, Col2)
My script that returns two columns;
select *
from MyTable
I also tested the following just to see if the select statement worked.
exec ('My script that returns two columns');
But if I:
create table #T (Col1 int, Col2 int);
insert into #T (Col1, Col2)
exec ('My script that returns two columns');
I keep getting:
The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction.
My Script =
Select Col1, Col2
From OpenQuery(MyServer,'Select Col1, Col2 From MyTable')
But I need to modify the script each time I run it and without using Execute (or Exec) or a UDF I am still stuck!
Argh!!!
Any thoughts?
October 27, 2010 at 10:21 am
I'm assuming your data source isn't OLE DB or ODBC compliant, or you'd just set up a linked server and be done with it.
Otherwise, you'll have to have the Where clause outside of the OpenQuery statement. I've used OpenDatasource that way, and it works out okay.
- 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
October 27, 2010 at 10:29 am
Actually,
I use 'Where' statements in OpenQuerys to the database often. Often in the statements I willl going multiple [linked] tables with each having their own 'Where'.
And I use these both with Execute and in regular Selects.
Never any problems.
But with this insert I am getting that error.
Would EVERYthing work and only the insert fail?
Remember I can do the insert with a regular 'Select From OpenQuery('Select From Where')' no problem. It's just the Execute that failed.
October 27, 2010 at 12:52 pm
If
A) Select From OpenQuery
And
B) Execute ('string') with string = 'Select From OpenQuery'
And
C) Insert Select From OpenQuery
All work
Then why doesn't
D) Insert Execute ('string')
Or Even
E) Insert Execute Stored Procedure
Work?
October 28, 2010 at 7:03 am
The error you're getting per a prior post is that it can't open a distributed transaction. Have you checked the DTC is running on that machine?
- 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
October 28, 2010 at 8:52 am
Yep, DTC is running.
October 28, 2010 at 11:14 am
I got a slimmed down version to work using EXECUTE sp_executesql.
However, I have more that 4,000 characters in the Select and...
This version of SQL Server has a 4,000 character max on nvarchar and does not allow concatenation when using sp_executesql.
So... I'm back to square 1.
October 28, 2010 at 11:28 am
What happens if you include the insert statement in your dynamic SQL string?
- 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
October 28, 2010 at 12:04 pm
Sucess!!!!
Thank you,
Now I have to work out the particulars, i.e. the actual parameters to pass.
Thank you again.
October 28, 2010 at 1:45 pm
You're welcome.
- 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
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply