Hello,
I'm running into some trouble writing a function that returns a table. I'm using OPENQUERY in the FROM clause to fetch data from a remote server (ORACLE). Because the query must be dynamic, I have to execute it using the EXECUTE command.
Basically, I first dynamically create a string containing my query and then I execute it by calling the EXECUTE function and passing my string as an argument.
Now, I'd like my function to return what's produced by the EXECUTE call. Here's how I wrote the function declaration:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
CREATE FUNCTION [dbo].[beh_GetRemoteData] (@.STARTDATE varchar(20), @.POS int, @.ID_ANALOG varchar(10))
RETURNS @.RET TABLE (HIST_TIMESTAMP DATETIME, ID_ANALOG INT, STATUT INT, QUALITY INT, VALUE NUMERIC(12,5)) AS
BEGIN
DECLARE @.REMOTEQUERY varchar(300)
DECLARE @.LOCALQUERY varchar(400)
--======== Create remote Query =======--
SET @.REMOTEQUERY = 'SELECT * FROM [...]'
--======== Create local Query =======--
SET @.LOCALQUERY = 'SELECT [...] FROM OPENQUERY(REMOTE_SERVER, ''' + @.REMOTEQUERY + ''')'
INSERT INTO @.RET
EXEC(@.LOCALQUERY)
RETURN
END
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Of course, this doesn't even passes the "Check syntax" because the EXEC statement cannot be used as as source when inserting into a table variable. This might be but it's EXACTELY what I want to do.
Any ideas on how I should write my function?
Thanks,
Skip.According to my reading of books on line for SQL Server 2000 you can't execute anything other than an extended stored procedure inside a function anyway - and those can't return results sets. Therefore, you can't use dynamic sql either.
Showing posts with label execute. Show all posts
Showing posts with label execute. Show all posts
Monday, March 19, 2012
Friday, March 9, 2012
Function like Informix DB's Set Explain
Hi,
In SQL Server is any function like Informix DB's Set Explain?
It's mean that during execute SQL syntax and I can know the SQL syntax use
what's kind of Index or not.
So, what's name of this function in SQL Server? And how to run it? Any
document can reference?
Thanks for help!
Angiangi
I think what you need is to look at Show Execution Plan option in the Query
menu on QA.
"angi" <enchiw@.sanrong.com.tw> wrote in message
news:uK5sjN71EHA.1192@.tk2msftngp13.phx.gbl...
> Hi,
> In SQL Server is any function like Informix DB's Set Explain?
> It's mean that during execute SQL syntax and I can know the SQL syntax use
> what's kind of Index or not.
> So, what's name of this function in SQL Server? And how to run it? Any
> document can reference?
> Thanks for help!
> Angi
>|||"angi" <enchiw@.sanrong.com.tw> wrote in message
news:uK5sjN71EHA.1192@.tk2msftngp13.phx.gbl...
> Hi,
> In SQL Server is any function like Informix DB's Set Explain?
> It's mean that during execute SQL syntax and I can know the SQL syntax use
> what's kind of Index or not.
> So, what's name of this function in SQL Server? And how to run it? Any
> document can reference?
> Thanks for help!
> Angi
>
Use the Showplan icon in the Query Analyzer.
A text based option is to run the SET SHOWPLAN ON statement.
Take a look in Books Online for SHOWPLAN option for analyzing query plans.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks Uri Dimant and Rick Sawtell.
Thank you so much!
Angi
In SQL Server is any function like Informix DB's Set Explain?
It's mean that during execute SQL syntax and I can know the SQL syntax use
what's kind of Index or not.
So, what's name of this function in SQL Server? And how to run it? Any
document can reference?
Thanks for help!
Angiangi
I think what you need is to look at Show Execution Plan option in the Query
menu on QA.
"angi" <enchiw@.sanrong.com.tw> wrote in message
news:uK5sjN71EHA.1192@.tk2msftngp13.phx.gbl...
> Hi,
> In SQL Server is any function like Informix DB's Set Explain?
> It's mean that during execute SQL syntax and I can know the SQL syntax use
> what's kind of Index or not.
> So, what's name of this function in SQL Server? And how to run it? Any
> document can reference?
> Thanks for help!
> Angi
>|||"angi" <enchiw@.sanrong.com.tw> wrote in message
news:uK5sjN71EHA.1192@.tk2msftngp13.phx.gbl...
> Hi,
> In SQL Server is any function like Informix DB's Set Explain?
> It's mean that during execute SQL syntax and I can know the SQL syntax use
> what's kind of Index or not.
> So, what's name of this function in SQL Server? And how to run it? Any
> document can reference?
> Thanks for help!
> Angi
>
Use the Showplan icon in the Query Analyzer.
A text based option is to run the SET SHOWPLAN ON statement.
Take a look in Books Online for SHOWPLAN option for analyzing query plans.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks Uri Dimant and Rick Sawtell.
Thank you so much!
Angi
Function like Informix DB's Set Explain
Hi,
In SQL Server is any function like Informix DB's Set Explain?
It's mean that during execute SQL syntax and I can know the SQL syntax use
what's kind of Index or not.
So, what's name of this function in SQL Server? And how to run it? Any
document can reference?
Thanks for help!
Angiangi
I think what you need is to look at Show Execution Plan option in the Query
menu on QA.
"angi" <enchiw@.sanrong.com.tw> wrote in message
news:uK5sjN71EHA.1192@.tk2msftngp13.phx.gbl...
> Hi,
> In SQL Server is any function like Informix DB's Set Explain?
> It's mean that during execute SQL syntax and I can know the SQL syntax use
> what's kind of Index or not.
> So, what's name of this function in SQL Server? And how to run it? Any
> document can reference?
> Thanks for help!
> Angi
>|||"angi" <enchiw@.sanrong.com.tw> wrote in message
news:uK5sjN71EHA.1192@.tk2msftngp13.phx.gbl...
> Hi,
> In SQL Server is any function like Informix DB's Set Explain?
> It's mean that during execute SQL syntax and I can know the SQL syntax use
> what's kind of Index or not.
> So, what's name of this function in SQL Server? And how to run it? Any
> document can reference?
> Thanks for help!
> Angi
>
Use the Showplan icon in the Query Analyzer.
A text based option is to run the SET SHOWPLAN ON statement.
Take a look in Books Online for SHOWPLAN option for analyzing query plans.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks Uri Dimant and Rick Sawtell.
Thank you so much!
Angi
In SQL Server is any function like Informix DB's Set Explain?
It's mean that during execute SQL syntax and I can know the SQL syntax use
what's kind of Index or not.
So, what's name of this function in SQL Server? And how to run it? Any
document can reference?
Thanks for help!
Angiangi
I think what you need is to look at Show Execution Plan option in the Query
menu on QA.
"angi" <enchiw@.sanrong.com.tw> wrote in message
news:uK5sjN71EHA.1192@.tk2msftngp13.phx.gbl...
> Hi,
> In SQL Server is any function like Informix DB's Set Explain?
> It's mean that during execute SQL syntax and I can know the SQL syntax use
> what's kind of Index or not.
> So, what's name of this function in SQL Server? And how to run it? Any
> document can reference?
> Thanks for help!
> Angi
>|||"angi" <enchiw@.sanrong.com.tw> wrote in message
news:uK5sjN71EHA.1192@.tk2msftngp13.phx.gbl...
> Hi,
> In SQL Server is any function like Informix DB's Set Explain?
> It's mean that during execute SQL syntax and I can know the SQL syntax use
> what's kind of Index or not.
> So, what's name of this function in SQL Server? And how to run it? Any
> document can reference?
> Thanks for help!
> Angi
>
Use the Showplan icon in the Query Analyzer.
A text based option is to run the SET SHOWPLAN ON statement.
Take a look in Books Online for SHOWPLAN option for analyzing query plans.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks Uri Dimant and Rick Sawtell.
Thank you so much!
Angi
Function like Informix DB's Set Explain
Hi,
In SQL Server is any function like Informix DB's Set Explain?
It's mean that during execute SQL syntax and I can know the SQL syntax use
what's kind of Index or not.
So, what's name of this function in SQL Server? And how to run it? Any
document can reference?
Thanks for help!
Angi
angi
I think what you need is to look at Show Execution Plan option in the Query
menu on QA.
"angi" <enchiw@.sanrong.com.tw> wrote in message
news:uK5sjN71EHA.1192@.tk2msftngp13.phx.gbl...
> Hi,
> In SQL Server is any function like Informix DB's Set Explain?
> It's mean that during execute SQL syntax and I can know the SQL syntax use
> what's kind of Index or not.
> So, what's name of this function in SQL Server? And how to run it? Any
> document can reference?
> Thanks for help!
> Angi
>
|||"angi" <enchiw@.sanrong.com.tw> wrote in message
news:uK5sjN71EHA.1192@.tk2msftngp13.phx.gbl...
> Hi,
> In SQL Server is any function like Informix DB's Set Explain?
> It's mean that during execute SQL syntax and I can know the SQL syntax use
> what's kind of Index or not.
> So, what's name of this function in SQL Server? And how to run it? Any
> document can reference?
> Thanks for help!
> Angi
>
Use the Showplan icon in the Query Analyzer.
A text based option is to run the SET SHOWPLAN ON statement.
Take a look in Books Online for SHOWPLAN option for analyzing query plans.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
|||Thanks Uri Dimant and Rick Sawtell.
Thank you so much!
Angi
In SQL Server is any function like Informix DB's Set Explain?
It's mean that during execute SQL syntax and I can know the SQL syntax use
what's kind of Index or not.
So, what's name of this function in SQL Server? And how to run it? Any
document can reference?
Thanks for help!
Angi
angi
I think what you need is to look at Show Execution Plan option in the Query
menu on QA.
"angi" <enchiw@.sanrong.com.tw> wrote in message
news:uK5sjN71EHA.1192@.tk2msftngp13.phx.gbl...
> Hi,
> In SQL Server is any function like Informix DB's Set Explain?
> It's mean that during execute SQL syntax and I can know the SQL syntax use
> what's kind of Index or not.
> So, what's name of this function in SQL Server? And how to run it? Any
> document can reference?
> Thanks for help!
> Angi
>
|||"angi" <enchiw@.sanrong.com.tw> wrote in message
news:uK5sjN71EHA.1192@.tk2msftngp13.phx.gbl...
> Hi,
> In SQL Server is any function like Informix DB's Set Explain?
> It's mean that during execute SQL syntax and I can know the SQL syntax use
> what's kind of Index or not.
> So, what's name of this function in SQL Server? And how to run it? Any
> document can reference?
> Thanks for help!
> Angi
>
Use the Showplan icon in the Query Analyzer.
A text based option is to run the SET SHOWPLAN ON statement.
Take a look in Books Online for SHOWPLAN option for analyzing query plans.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
|||Thanks Uri Dimant and Rick Sawtell.
Thank you so much!
Angi
Friday, February 24, 2012
Full-Text Search is not installed, or a full-text component cannot
I am unable to get Full Text Indexing to work with SQL Server 2000 Developers
Edition. When I execute this command:
EXEC sp_fulltext_database 'enable'
This is what I get:
(1 row(s) affected)
Server: Msg 7609, Level 17, State 2, Procedure sp_fulltext_database, Line 46
Full-Text Search is not installed, or a full-text component cannot be loaded.
I attempted to install FTS again from the CD, but it indicates that it is
already installed.
Here is the version I am using:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright 1988-2003 Microsoft Corporation Developer Edition on Windows NT
5.1 (Build 2600: Service Pack 2)
When I run this at the command prompt:
net start mssearch
I get this:
The requested service has already been started.
More help is available by typing NET HELPMSG 2182
I’m not sure where to go from here.
Hello,
The possible causes of this kind of issue may be as followings:
* Full-Text Search(FTS) is installed AFTER SQL Service Service Pack(SP).
This is the most common cause. Reapplying the SP fixes FTS.
* Microsoft Search service is not installed or started
* SQL server or Microsoft Search service is not started as a administrative
user
To narrow down the issue, I suggest that you perform the following steps:
1. Make sure SQL server and Microsoft Search service are started as the
same domain\administrative user account
2. Install the latest SQL SP4 or reinstall SP3.
Microsoft SQL Server 2000 Service Pack 4
<http://www.microsoft.com/downloads/d...FC8D-C20E-4446
-99A9-B7F0213F8BC5&displaylang=en#filelist>
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Dale,
Sorry for the late reply on this thread, but this type of question is best
answered in the fulltext newsgroup.
The key bit of info here is the SQL Server edition "SQL Server 2000
Developers Edition" as FTS is not installed by default as it is with Std.
Edition and above. You can "re-install" the FTS components via removing or
renaming the following product tracking registry key (remove :
"<Instance_Name>\" if this is not a named instance):
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\<Instance_Name>\Tracking\{E07FDDA7-5A21-11d2-9DAD-00C04F79D434}
Then you will be able to re-install the FTS / MSSearch components. You may
also want to review the procedures in KB article 827449 "How to manually
reinstall the Microsoft Search service for an instance of SQL 2000" at
http://support.microsoft.com/default...b;EN-US;827449
Sophie, the advise that you've provided is wrong and will cause known
problems, i.e.. high CPU utilization (there is a KB article on this).
Specifically, the Microsoft Search service MUST be started and running under
the "System account" (LocalSystem), while the MSSQLServer service can be
started and running under either LocalSystem or DOMAIN\Account (but NOT the
DOMAIN\Administrators account!). All this I've learned from years of
experience with SQL FTS since 1998 SQL 7.0 Beta3 while at MSFT...
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:Lg1hyhFqFHA.2152@.TK2MSFTNGXA01.phx.gbl...
> Hello,
> The possible causes of this kind of issue may be as followings:
>
> * Full-Text Search(FTS) is installed AFTER SQL Service Service Pack(SP).
> This is the most common cause. Reapplying the SP fixes FTS.
> * Microsoft Search service is not installed or started
> * SQL server or Microsoft Search service is not started as a
> administrative
> user
>
> To narrow down the issue, I suggest that you perform the following steps:
> 1. Make sure SQL server and Microsoft Search service are started as the
> same domain\administrative user account
> 2. Install the latest SQL SP4 or reinstall SP3.
> Microsoft SQL Server 2000 Service Pack 4
> <http://www.microsoft.com/downloads/d...FC8D-C20E-4446
> -99A9-B7F0213F8BC5&displaylang=en#filelist>
>
> I hope the information is helpful.
>
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ================================================== ===
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>
Edition. When I execute this command:
EXEC sp_fulltext_database 'enable'
This is what I get:
(1 row(s) affected)
Server: Msg 7609, Level 17, State 2, Procedure sp_fulltext_database, Line 46
Full-Text Search is not installed, or a full-text component cannot be loaded.
I attempted to install FTS again from the CD, but it indicates that it is
already installed.
Here is the version I am using:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright 1988-2003 Microsoft Corporation Developer Edition on Windows NT
5.1 (Build 2600: Service Pack 2)
When I run this at the command prompt:
net start mssearch
I get this:
The requested service has already been started.
More help is available by typing NET HELPMSG 2182
I’m not sure where to go from here.
Hello,
The possible causes of this kind of issue may be as followings:
* Full-Text Search(FTS) is installed AFTER SQL Service Service Pack(SP).
This is the most common cause. Reapplying the SP fixes FTS.
* Microsoft Search service is not installed or started
* SQL server or Microsoft Search service is not started as a administrative
user
To narrow down the issue, I suggest that you perform the following steps:
1. Make sure SQL server and Microsoft Search service are started as the
same domain\administrative user account
2. Install the latest SQL SP4 or reinstall SP3.
Microsoft SQL Server 2000 Service Pack 4
<http://www.microsoft.com/downloads/d...FC8D-C20E-4446
-99A9-B7F0213F8BC5&displaylang=en#filelist>
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Dale,
Sorry for the late reply on this thread, but this type of question is best
answered in the fulltext newsgroup.
The key bit of info here is the SQL Server edition "SQL Server 2000
Developers Edition" as FTS is not installed by default as it is with Std.
Edition and above. You can "re-install" the FTS components via removing or
renaming the following product tracking registry key (remove :
"<Instance_Name>\" if this is not a named instance):
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\<Instance_Name>\Tracking\{E07FDDA7-5A21-11d2-9DAD-00C04F79D434}
Then you will be able to re-install the FTS / MSSearch components. You may
also want to review the procedures in KB article 827449 "How to manually
reinstall the Microsoft Search service for an instance of SQL 2000" at
http://support.microsoft.com/default...b;EN-US;827449
Sophie, the advise that you've provided is wrong and will cause known
problems, i.e.. high CPU utilization (there is a KB article on this).
Specifically, the Microsoft Search service MUST be started and running under
the "System account" (LocalSystem), while the MSSQLServer service can be
started and running under either LocalSystem or DOMAIN\Account (but NOT the
DOMAIN\Administrators account!). All this I've learned from years of
experience with SQL FTS since 1998 SQL 7.0 Beta3 while at MSFT...
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:Lg1hyhFqFHA.2152@.TK2MSFTNGXA01.phx.gbl...
> Hello,
> The possible causes of this kind of issue may be as followings:
>
> * Full-Text Search(FTS) is installed AFTER SQL Service Service Pack(SP).
> This is the most common cause. Reapplying the SP fixes FTS.
> * Microsoft Search service is not installed or started
> * SQL server or Microsoft Search service is not started as a
> administrative
> user
>
> To narrow down the issue, I suggest that you perform the following steps:
> 1. Make sure SQL server and Microsoft Search service are started as the
> same domain\administrative user account
> 2. Install the latest SQL SP4 or reinstall SP3.
> Microsoft SQL Server 2000 Service Pack 4
> <http://www.microsoft.com/downloads/d...FC8D-C20E-4446
> -99A9-B7F0213F8BC5&displaylang=en#filelist>
>
> I hope the information is helpful.
>
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ================================================== ===
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>
Subscribe to:
Posts (Atom)