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.
| Author |
Topic |
|
jtwork
Yak Posting Veteran
82 Posts |
 Posted - 2007-11-06 : 04:14:15
|
| I am putting a function together which pulls data from a server using a similar command.openquery(Database, 'my select satement here')This works fine in query analyser and i want to use this method to set up a function but want to pull the data based on a variable.This is where it starts to become an issue.I'm unable to just add the variable into the openquery command so i decided to create a sql string to action.i.eopenquery(database, insert into table select .....)originally i was going to create a temp table and have the data inserted into a temp table but i now know that temp tables cant be used in functions. I then tried to create a variable table but i cant get that working either?anyone got any suggestions? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-11-06 : 04:20:44
|
If inserting into other tables, why not rewrite function as SP?
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
jtwork
Yak Posting Veteran
82 Posts |
Posted - 2007-11-06 : 05:33:54
|
| Im not putting into a table i was going to use a temp table to handle the data like so.As i cant write the followingselect * from openquery(database, 'select * from table where field =' + @Ref)i was going to create a #temp table to put the data into whilst writing a sql string first@MySQL = 'insert into #temp select * from openquery(database, 'select * from table where field =' + @Ref)exec (@MySQL)then pick data up from temp tablehowever i cant do this because i cant use temp tables in a function.Ive also looked at variable tables but couldnt get these working.ANY IDEAS ???? |
|
|
|
Wire
Starting Member
1 Post |
Posted - 2007-11-08 : 15:19:40
|
| I don't think you can make a remote call in a function anyway, as the result is non-deterministic. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-11-08 : 15:53:03
|
| You cannot use EXECUTE in a function, except to call an extended stored procedure, and you cannot use it to call sp_executesql.CODO ERGO SUM |
|
|
|
jtwork
Yak Posting Veteran
82 Posts |
Posted - 2007-11-13 : 06:34:12
|
| is there any other way around this? Basically the problem is that i have a linked server to an oracle database and when i link in i get the following errorOLE DB provider 'MSDAORA' returned an invalid schema definition.The only way t get around this is to set up the query like soselect * from openquery(server,'select satemanet')with this though i cant include a parameter unless i use a temp table which then causes me a problem with my function.Is the only way around this to use an extended stored proc? Other people must have come across this? |
|
|
|
|
|
|
Function Question