Using EXECUTE Statements Calling An Extended Stored Procedures From Function..
Apr 29, 2004
Hi, all
I'm using Sql server 2000
I want to make select statement dynamically and return table using function.
in sp, I've done this but, in function I don't know how to do so.
(I have to create as function since our existing API..)
Following is my tials...
1.
alter Function fnTest
( @fromTime datetime, @toTime datetime)
RETURNS Table
AS
RETURN Exec spTest @from, @to
GO
Yes, it give syntax error..
2. So, I found the following
From Sql Server Books Online, Remark section of CREATE FUNCTION page of Transact-SQL Reference , it says following..
"The following statements are allowed in the body of a multi-statement function. Statements not in this list are not allowed in the body of a function: "
.....
* EXECUTE statements calling an extended stored procedures.
So, I tried.
alter Function fnTest
( @fromTime datetime, @toTime datetime)
RETURNS Table
AS
RETURN Exec master..xp_msver
GO
It doesn't work... syntax err...
Here I have quick question.. How to execute statements calling an extended stored procedures. any examples?
Now, I'm stuck.. how can I create dynamic select statement using function?
I want to know if it's possible or not..
View 13 Replies
ADVERTISEMENT
May 15, 2008
i have created the folowing function but keep geting an error.
Only functions and extended stored procedures can be executed from within a function.
Why am i getting this error!
Create Function myDateAdd
(@buildd nvarchar(4), @avdate as nvarchar(25))
Returns nvarchar(25)
as
Begin
declare @ret nvarchar(25)
declare @sqlval as nvarchar(3000)
set @sqlval = 'select ''@ret'' = max(realday) from (
select top '+ @buildd +' realday from v_caltable where realday >= '''+ @avdate +''' and prod = 1 )a'
execute sp_executesql @sqlval
return @ret
end
View 3 Replies
View Related
Jun 7, 2006
Hi, l've created an function [GSM].[KPIAging], and test it in studio by substitule declare value, i.e.
DECLARE @sCellName VARCHAR(8)
DECLARE @dDate DATETIME
SET @sCellName = "CELL1M_1"
SET @dDate = CAST('06/Jun/2006' AS DATETIME)
EXEC GSM.KPIAging @sCellName, 'CSSR', @dDate
It work fine and return the desired result, but when l used this function in SQL,
SELECT DATEKEY, CELLREGIONKEY, CELL_NAME, CELL_ID, CSSR, GSM.KPIAging(Cell_Name, 'CSSR', @dDate)
FROM GSM.GSMCellDaily_vw
WHERE CSSR BETWEEN 0 AND 85
AND FULLDATE = @dDate
AND CM_SERV > 30
AND (TCH_TRAFFIC > 2 AND TCH_SEIZURES_ATTS > 30)
I got the following error, i.e.
Msg 557, Level 16, State 2, Line 19Only functions and extended stored procedures can be executed from within a function.
Does anyone have any idea on this, and what's the workaround for this?
Thanks you!
View 15 Replies
View Related
May 7, 2008
Hi mister, I have this script sql but I get this error:
Mens. 557, Nivel 16, Estado 2, LÃnea 1
Only functions and extended stored procedures can be executed from within a function.
DROP FUNCTION ObtenerTablaPorNombre2
GO
CREATE FUNCTION ObtenerTablaPorNombre2 (@ParamNombreTabla VARCHAR(100))
RETURNS @T Table ( Descripcion VARCHAR(20) NOT NULL, CIF VARCHAR(8) NULL )
AS
BEGIN
DECLARE @cmd nvarchar(max)
DECLARE @params nvarchar(max)
DECLARE @NombreTabla VARCHAR(MAX)
DECLARE @Descripcion VARCHAR(MAX)
DECLARE @CIF VARCHAR(MAX)
SELECT @NombreTabla = [CD_NOMBRE_TABLA], @Descripcion = [DS_CAMPO_DESCRIPCION] , @CIF = [DS_CAMPO_CIF]
FROM [TABLA_MAESTRA] WHERE [CD_NOMBRE_TABLA] = @ParamNombreTabla
SET @cmd = 'SELECT ' + @Descripcion + ',' + @CIF + ' FROM ' + @NombreTabla
--EXEC (@cmd)
SET @cmd = 'SELECT @pDescripcion, @pCIF FROM @pNombreTabla'
SET @params = N'@pDescripcion varchar(100), @pCIF varchar(100), @pNombreTabla varchar(100) '
EXEC sp_executesql @cmd, @params, @pDescripcion = @Descripcion, @pCIF = @CIF, @pNombreTabla = @NombreTabla
RETURN
END
GO
SELECT * FROM [dbo].ObtenerTablaPorNombre2 ('tabla2')
-- Only functions and extended stored procedures can be executed from within a function
View 2 Replies
View Related
Sep 11, 2013
I have this function called fn_GetTimedKey() when I run it in my SQL2005 environment it works fine.
When I run it in my SQL2008R2 environment I get the error:
Only functions and some extended stored procedures can be executed from within a function.
The SQL2008R2 server is new. What can I look for?
Here's the code for the function:
BEGIN
DECLARE @v_Key CHAR(12)
EXEC master..xp_usg_gettimedkey_VAL @v_Key OUTPUT
RETURN @v_Key
END
View 4 Replies
View Related
Jan 23, 2004
Hello,
Is it possible to execute DBCC statements remotely in destination servers through stored procedures existing in source server.
I don’t want to use isql, osql, openquery, openresultset because I have to pass user id or password.
I have linked the required SQL servers using Linked servers.
If so please give some example.
Thanks in Advance,
Sateesh
View 9 Replies
View Related
Oct 12, 1999
I'm trying to execute an exetended stored procedure from a trigger. The scenario is this: when an input occurs on a table, my trigger is fired, the extended stopred procedure is to be run, and VB app is to be notified that there is new input to process.
Problem is, I can't seem to get the XP to run. I have written a minimal DLL
with VC++ 6, has nothing but the required __GetXpVersion function and the
trigger function, as:
__declspec(dllexport) SRVRETCODE xp_InputAlarm(SRV_PROC *pSrvProc)
{ return XP_NOERROR; }
__declspec(dllexport) ULONG __GetXpVersion()
{ return ODS_VERSION; }
Pretty simple. I have defined the XP in the master database, set the properties point to the DLL. Believe everything is set up properly. However,
when an input occurs, a run-time error is generated on the XP. Attempting to execute it in Query Analyzer gives this error:
"Cannot find the function xp_InputAlarm in the library D:CustomMedInstaCert SourceInputAlarmInputAlaInputAlarm.dll. Reason: 127(The specified procedure could not be found.)."
Which I think means it can't find the proper entry point (xp_InputAlarm) in the DLL. Any help would be greatly appreciated.
mjo
View 2 Replies
View Related
Jan 6, 2005
Is there any way I can create an SQL script on any extended stored procedures? I need to see what they do. I looked in books online, but it didn't help. Thanks.
View 3 Replies
View Related
Jun 13, 2001
I am working with Visual Basic and SQL Server 2000. I am looking to convert some of the VB into extended stored procedures, yet everything I've been reading has said that xp's can only be written in C or C++. Is this true??
Thanks.
Caroline Kaplonski
ckaplonski@buckconsultants.com
View 1 Replies
View Related
Jan 6, 2005
Howdy,
I'm trying to call XP_CMDSHELL from a stored procedure that's not in the Master db. How do I call XP_CMDSHELL? I've tried "Use Master" but I get an error telling me that I can't use "USE" inside of a stored procedure.
Thanks!
View 2 Replies
View Related
Sep 30, 1998
I would like to know if the the DLL`s one can build with VB5/6 can be used to construct Extended Stored Procedures in MS SQLServer 6.5? If so, how does one do this. All the reference material I`ve come across is for C
Thanks in Advance - Ralph
View 1 Replies
View Related
Sep 30, 1998
I would like to know if the the DLL`s one can build with VB5/6 can be used to construct Extended Stored Procedures in MS SQLServer 6.5? If so, how does one do this. All the reference material I`ve come across is for C
Thanks in Advance - Ralph
View 1 Replies
View Related
Jul 18, 2007
Hi,
How can I remove access to extended (xp_) stored procedures?
Is there any revoke on <stored_procedure_name> ... command? How can I generate a script of all users who have execute privileges for these procedures? Also, is there any way of restricting (instead of removing) access to those procedures?
Any help will be greatly appreciated!!!
Thanks,
Alla
View 2 Replies
View Related
Jun 18, 2008
Hi all,
In one of my UDF I use the following functions:
.....
and len(@int_date) = 4
and isnumeric(substring(@int_date,5,6)) = 1
when I use the function I get
Only functions and extended stored procedures can be executed from within a function.
Yes, when I comment the two lines the function works fine.
Ehm.... why can't I use these functions in my function ?
Thanks: Peter
View 3 Replies
View Related
Jul 20, 2005
Since DBlib is no longer the suggested method for connecting back tosql server from an Extended Stored Procedure, has anyone built anyextended stored procedures that use other connection methods likeOLEDB? Has anyone seen links to any sample extended stored proceduresthat use something other than db-lib? In particular I am interestedin something that connects back to the database as the user whoinvoked the extended stored procedure. I haven't had much luckfinding any.Also, is there an alternative for the bcp api that is a little morecurrent and has support for newer datatypes like bigint? We currentlyuse the bcp api from an extended stored procdure written in C++, butnow need to add bigint support which the bcp api doesn't have.Thanks for any advice.
View 3 Replies
View Related
Sep 9, 2007
The Extended Stored Procedures (Written in C++ unmanaged code) are supported in SQL Server 2005????
Thanks
View 5 Replies
View Related
Jun 11, 2007
I have a database script that uses the extended stored procedures sp_OACreate and sp_OCMethod to execute an .exe file. The .exe file is located on the same machine as the SQL Server. At this time it does nothing but log the name of the user calling it.
When I execute the script from Management Studio (logged in as myself) the user being logged as the caller of the .exe is still NT AUTHORITYSYSTEM. I don't know why NT AUTHORITYSYSTEM is the caller, cause the SQL Server service runs under another domain account.
I have tried playing around with EXECUTE AS USER but no matter what, the caller of the .exe is always logged as NT AUTHORITYSYSTEM.
Are there any way I can pass my credentials to the executable that I am calling from the T-SQL script?
View 2 Replies
View Related
Apr 26, 2004
Is it possible to create an extended stored procedure in C Sharp.
This is for Sql Server 2000.
Books online mentions that you have to use c / c++ to create an extended stored procedure.
However have Microsoft added any support so that the same thing can be done through a simpler language like C Sharp.
Thanks,
Alok.
View 1 Replies
View Related
Jul 20, 2005
I'm trying to pass parameters to an extended stored procedure, to noavail. I would like to pass two integers to the dll and I have thefollowing three snippets:1. The C++ portion of the dll:....declspec(dllexport) int myAddNumbers(int m, int n)....2. The creation of the extended stored procedure:EXEC sp_addextendedproc myAddNumbers , 'foodll.dll';3. The usage:create function TestFunction()returns integerasbegindeclare @rc integerexec @rc = myAddNumbersreturn (@rc)endHow do any of the above three things need to be modified in order tomake this work?Thanks!!!
View 1 Replies
View Related
Feb 22, 2008
...we understand that perhaps some xp's might come with the product, but for sp's that we author ourselves, are they two different things in 2005, and if so, why would one be chosen over the other?
View 1 Replies
View Related
Jun 29, 2007
Our security team wants us to disable access to (or drop) all of the built-in extended stored procedures in MSDE 2000 as they feel it is a vulnerability. Where can I find out which extended procs are safe to disable or how we can disable them during install time? Or, is the security team being too cautious and we should just tell them to leave these intact?
View 5 Replies
View Related
May 28, 2007
Hi. Does anyone know how to display the results if i execute "xp_fixeddrives, xp_availablemedia and xp_subdirs" commands with VC++ 6.0? I can't obtained the results using Recordset class. Can someone help me? Thank you.
|_N_T_|
View 3 Replies
View Related
Mar 9, 2001
You know this sample ODS dll project:
$80oolsdevtoolssamplesodsxp_hello
I need to find a template like this in Visual Basic.
On this site I read through the xp_Encrypt project which was developed in VB. I did not see source code or a downloadable project file.
My searching on the internet hasn't yielded any practicle results, all examples are either how to use an extended stored procedure or are a MSVC++ project.
Anyone find VB related resources, anywhere? Books, TV, magazines????
View 1 Replies
View Related
Feb 11, 2004
Hello everybody
I actually wrote a stored procedure (in xp_wrapper.dll) that is using a dll (original.dll) which uses a license file (no file extension).... clear? :)
Anyway.
All the required files are placed in the BINN dir of the server.
The problem is now, that original.dll can't find it's license file. It seems, that this file was not load by SQL Server.
How can I load this file into SQL Server's heap?
Yours
Mike
View 1 Replies
View Related
Jul 23, 2005
What is the overhead of using extended stored procedures?I created a table with 500,000 rows.1) I ran a select on two columns and it runs in about 5 seconds.2) I ran a select on one column and called an UDF (it returns aconstant string) and it takes 10 seconds.3) I ran a select on one column and called a UDF that calls an extendedstored procedure that returns a string and it takes 65 seconds.I also tried running test 3 with 4 concurrent clients and each clienttakes about 120 seconds.
View 1 Replies
View Related
Jul 20, 2005
Hello all,I have spend quite some time now but don't manage tofind out how to have the srv_describe function workingproperly for target NUMERIC(x,x) and NUMERIC types.Already tried several alternatives like e.g.// ...PVOID pvdata1;strcpy((char*)pvdata1, "12345.6789");wsprintf(colname, "Score");srv_describe(srvproc, 1 /* column #1 */, colname /* column name */, SRV_NULLTERM /* column name ending */, SRVNUMERIC, (DBINT)sizeof(DBNUMERIC), SRVNUMERIC, (DBINT)sizeof(DBNUMERIC), pvdata1);srv_setcoldata(srvproc, 1, pvdata1);srv_setcollen (srvproc, 1, strlen((char*)pvdata1));// ...but obviously does not work, I get an empty resultset.I could only manage by converting the numeric valueto string from C and then sending only strings but theSQL side expects actually a number(x,x) and is a customer, in which case, I would never propose to be casting thething from TSQL.Any ideas how to do it? The documentation and examples isquite limited in this area.Thanks in advance,Best Regards,Giovanni
View 2 Replies
View Related
Feb 21, 2008
Hi,
This is driving me up the wall,
I have used SQL for donkey's years, but never really used Stored Procedures, well, I am starting to migrate functions from a front end ASP.Net system, and utilising the stored procedures.
I have a problem with something I thought would be really basic, i am trying to use IF THEN statements within the stored procedure to change the WHERE elements based on the parameters passed to it.
Here is the script (copied from the modification screen)
USE [QP]
GO
/****** Object: StoredProcedure [dbo].[USERS_LIST] Script Date: 02/21/2008 21:50:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[USERS_LIST]
@SearchVarChar(100)
As
SELECT TOP 100 PERCENT dbo.Members.EntryID, dbo.Members.EntryDate, dbo.Members.Username, dbo.Members.Forename, dbo.Members.Surname, dbo.Members.Gender,
dbo.Members.DateofBirth, dbo.Members.LastAction, dbo.Members.AdminUser, dbo.ActiveMember_Status.Status, dbo.ActiveMember_Mobile.Value AS Mobile,
dbo.ActiveMember_Email.Value AS Email, dbo.ActiveMember_Location.Location1, dbo.ActiveMember_Location.Location2, dbo.ActiveMember_Location.Location3,
dbo.ActiveMember_Location.Location4, dbo.F_AGE_IN_YEARS(dbo.members.dateofbirth, GetDate()) As Age
FROM dbo.Members INNER JOIN
dbo.ActiveMember_Status ON dbo.Members.EntryID = dbo.ActiveMember_Status.UserID LEFT OUTER JOIN
dbo.ActiveMember_Location ON dbo.Members.EntryID = dbo.ActiveMember_Location.UserID LEFT OUTER JOIN
dbo.ActiveMember_Email ON dbo.Members.EntryID = dbo.ActiveMember_Email.UserID LEFT OUTER JOIN
dbo.ActiveMember_Mobile ON dbo.Members.EntryID = dbo.ActiveMember_Mobile.UserID
IF (@Search='Dowle')
WHERE (dbo.Members.Username = @Search) OR
(dbo.Members.Forename = @Search) OR
(dbo.Members.Surname = @Search) OR
(dbo.ActiveMember_Mobile.Value = @Search) OR
(dbo.ActiveMember_Email.Value = @Search) OR
(dbo.ActiveMember_Location.Location1 = @Search) OR
(dbo.ActiveMember_Location.Location2 = @Search) OR
(dbo.ActiveMember_Location.Location3 = @Search) OR
(dbo.ActiveMember_Location.Location4 = @Search)
END IF
ORDER BY dbo.Members.Username
The bit I am trying to do above isn't real, but the same error appears every which way I try.
Msg 156, Level 15, State 1, Procedure USERS_LIST, Line 14
Incorrect syntax near the keyword 'WHERE'.
What am i doing wrong?
Thanks
David
View 12 Replies
View Related
Mar 3, 2008
Hi all,
I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):
(1) /////--spTopSixAnalytes.sql--///
USE ssmsExpressDB
GO
CREATE Procedure [dbo].[spTopSixAnalytes]
AS
SET ROWCOUNT 6
SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName
FROM LabTests
ORDER BY LabTests.Result DESC
GO
(2) /////--spTopSixAnalytesEXEC.sql--//////////////
USE ssmsExpressDB
GO
EXEC spTopSixAnalytes
GO
I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class
///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance,
Scott Chang
More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
View 11 Replies
View Related
Jul 1, 2007
Hi All... I'm calling a stored procedure from C#. I'm sending it input parameters as follows:SqlCommand c = new SqlCommand("AddAuthor", myConnection);c.CommandType = CommandType.StoredProcedure;
c.Parameters.Add(new SqlParameter("@LastName", SqlDbType.VarChar, 100));c.Parameters["@LastName"].Value = "Last";
c.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.VarChar, 100));c.Parameters["@FirstName"].Value = "First";
In my opinion, that works nice - and it's easy. The reference I'm using says I can also specify "output" parameters - instead of supplying a value, one needs to change a property called direction.
But my stored procedure doesnt return any output parameters per se, but it does return a value. That is the last statement in the stored procedure is "RETURN @@IDENTITY" - it returns the identity field after an INSERT... So how do I get that value back in my C# code?
Thanks for the help in advance. Happy 4th to all! : ) -- Curt
View 4 Replies
View Related
Jan 16, 2008
Hi,
I was wondering if someone can tell me why my sub for calling stored procedures doesn't work.The string strFinFileId is not null.
I keep getting the following error System.IndexOutOfRangeException: Total
Here's my sub.
'//get GrandTotal Protected Sub GetGrandTotal(ByVal finfileid As String) Dim myConnection As SqlConnection Dim connString As String Dim strFinFileId As String Dim strGrandTotal As Decimal
strFinFileId = finfileid connString = ConfigurationManager.ConnectionStrings("PMOConnectionString1").ConnectionString
myConnection = New SqlConnection(connString) 'you need to provide password for sql server myConnection.Open()
Dim sql1 As String
sql1 = "GetGrandTotal " & Convert.ToInt32(strFinFileId)
Dim myCommand As SqlCommand
'Response.Write(sql1) 'Response.End()
myCommand = New SqlCommand(sql1, myConnection)
Dim reader As SqlDataReader = myCommand.ExecuteReader() While reader.Read() strGrandTotal = reader.Item("Total") LabelGrandTotal.Text = strGrandTotal 'Response.Write(strFinFileId) 'Response.End()
End While reader.Close() myConnection.Close() End Sub
Cheers
Mark :)
View 2 Replies
View Related
Apr 16, 2008
I have a stored procedure written to update a table:
The stored procedure has the following parameters:
@Original_TypeID int,
@Type_Desc varchar(35),
@Contact_Name varchar(20),
@Contact_Ad1 varchar(25),
@Contact_Ad2 varchar(25),
@Contact_City varchar(10),
@Contact_Phone varchar(12),
@Contact_Fax varchar(12),
@Contact_Email varchar(35)
And I want to call this procedure when the "update" button is clicked, my code is:Protected Sub UpdateButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("myConnectionString").ConnectionString)Dim myCommand As SqlCommandDim UpdteParam As New SqlParameter()
myConnection.Open()myCommand = New SqlCommand("[dbo].[sp_Update_Types]", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
????????????????????????myCommand.Parameters.Add(UpdateParam)
???????????myCommand.ExecuteNonQuery()
myConnection.Close()
End Sub
Can you please help me with the ????? areas? I don't know how to pass the parameters into the procedure, and also the second set of ??? s, I'm not sure if I am executing the command correctly.
Thank you.
View 3 Replies
View Related
Jun 21, 2001
I'm relatively new to SQL Server and I was wondering if there is any way to call a DLL from a regular stored procedure? I believe I've read that the extended stored procedure is a DLL and therefore should be able to call another DLL. Can this also be verified? We're looking to speed up some sections of Visual Basic code which does some heavy I/O work. I've also asked this once before but didn't get an absolute answer - can you write the extended stored procedures in Visual Basic or is C/C++ the only language supported right now?
Thanks.
Caroline Kaplonski
ckaplonski@buckconsultants.com
View 2 Replies
View Related
Jul 20, 2005
Hi AllI was wondering if there is a way to call a stored procedure from insideanother stored procedure. So for example my first procedure will call asecond stored procedure which when executed will return one record and iwant to use this data in the calling stored procedure. Is this possible ?Thanks in advance
View 18 Replies
View Related
