MS SQL Server :: Extended Stored Procedure Problem (srv

Only Functions And Extended Stored Procedures Can Be Executed From Within A Function. Sp_executesql Is A Extended Stored Prod
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 Replies !

Only Functions And Extended Stored Procedures Can Be Executed From Within A Function.
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 Replies !

Only Functions And Extended Stored Procedures Can Be Executed From Within A Function.
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 Replies !

Using EXECUTE Statements Calling An Extended Stored Procedures From Function..
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 Replies !

Extended Stored Procedure
Could someone who has done it before be kind enough to post a short example of how to make a call from an extended stored procedure to a .NET DLL? Or even direct me to an example, or tell me that this is possible / not possible, it would help.
Thanks,Brian

View Replies !

Extended Stored Procedure
Hello,
I have a question about the executing of an SQL clause in xp(extended stored procedure).
I saw the examples about the ODS: xp_gettable_dblib and xp_gettable_odbc.
When I call xp_gettable_dblib, this xp execute the SQL clause in session, which is different from the session from which it is called(EXEC xp_gettable_dblib. Of course this is because of the dbopen).
I also saw the extended stored procedure xp_execresultset(xp_repl.dll) , which also can execute an SQL clause, but in contrast to xp_gettable_dblib, this SQL clause is executed in the same session, from which it is called.
I am interested how it is made?
I saw that in xp_repl.dll are imported 5 API procedures from ODBC32.dll. In this case is ODBC API is used?
I want to write my own xp, using the technology used in xp_execresultset.

View Replies !

Extended Stored Procedure

View Replies !

Extended Stored Procedure
Hi Guys.

I am looking for sample code for creating Extended stored procedure using
Visual Basic 6.0.

All I can get is Using VC++ and ODS APIs.

There is not enough info available on ODS lib either.

Please help.
Thanks
-MAK

View Replies !

Extended Stored Procedure -- VB
I am looking for examples written in VB. Thanks ALOT!!

View Replies !

Extended Stored Procedure
is it possible to view the source of an extended stored procedure.
I know that it is a dll program
but i want to know how works one of my extented stored proc

View Replies !

Extended Stored Procedure
Is it possible to create an extended stored procedure
using visual basic?

Thanks,
MMS

View Replies !

How To Use C# DLL In Extended Stored Procedure?
Hello,
i have created one C# DLL

xp_hello.dll
Its having below code in Class1.cs

namespace xp_hello

{

public class Class1

{

public string xp_hello()

{

string strReturn = string.Empty;

strReturn = "Chirag Patel";

return strReturn;

}

}

}

i compiled that DLL and kept it in C drive.
now i am trying to create Extended stored procedure using

sp_addextendedproc 'xp_hello', 'c:xp_hello.dll'
its showing successful creation.
Now i am trying to Execute it with

DECLARE @txt varchar(33)

EXEC xp_hello @txt OUTPUT

now its showing below message.

Msg 17751, Level 16, State 0, Procedure xp_hello, Line 1

Could not find the function xp_hello in the library xp_hello.dll. Reason: 127(The specified procedure could not be found.).

can anyone please help me out in this?
Thanks

View Replies !

Extended Stored Procedure 'xp_cmdshell'
Hello,

I have a question regarding the extended SPC 'xp_cmdshell'.

Basically I want to determine the username and userdomain in a stored procedure and what I know is that you can get this information in a "DOS-Box" with 'Set username' or 'Set userdomain'.

But if I use the above mentioned extended stored procedure in the following way:exec master..xp_cmdshell 'set username'I don´t get any resultset.

Does anyone know why I get different results depending on the fact if I call the 'set'-command in a "DOS-Box" or with the appropriate stored procedure?

Thank you for any help

View Replies !

Output From Extended Stored Procedure
Hi allIve got a problem I was hoping someone may be able to help with.Im calling an extended stored procedure provided by a third party(master..xp_OrderHeader).This xp requires 3 inputs and is supposed to provie one output.When I call it in SQL Query Analyser all runs OK and I get a column result(single result) titled "Output_Info" with a value of say 300051Here is an example callexecute master..xp_OrderHeader @CustID, @TodayDate, @OrderID,@Output_Info OUTPUTI would have thought that my variable @Output_Info would hold the output,but all I get is NULL?Any ideas what Im doing wrong. Seems bizarre that the XP Ive been providedis displaying a result (be it a coumn I haven't named) ... but I can get itinto my variable for use.CheersCraig

View Replies !

Probelm With Extended Stored Procedure
Hi

Can i be able to use the DLL which is created in VB for Extended Stored Procedures.
if so how?

View Replies !

Extended Stored Procedure Problem
Hi,
I made some extended strored procedures
and they goes well when they are placed at
c:windowssystem32 or the same derectory to
sqlservr.exe which is usualy in C:Program Files
Microsoft SQL ServerMSSQLBinn.

I thought it's OK wherever they are placed
as long as I put them to the system path.

But actually they didn't work at all
when they were placed to the other path
that I had newly added as a system path.

Here is the contents of 'path' valiable:

%SystemRoot%system32;%SystemRoot%;%SystemRoot%Sy stem32Wbem;C:WINDOWSSystem32AmiClick;C:Progra m FilesMicrosoft SQL Server80ToolsBINN:C:Program Files/INC

I just added only "C:Program Files/INC",
placed the extended stored procedures there,
then called them through a trigger.

Can Somebody tell me what was wrong?
Please help me if you have any informations about this issue.

regards,
Takanobu Maekawa

View Replies !

Extended Stored Procedure API Not In New Platform SDK
Does anyone know why the Extended Stored Procedure API (formerly Open Data Services) headers and libraries (e.g. srv.h, srvdbtyp.h, opends60.lib) are not in the new Platform SDK?
Older versions of the Platform SDK had these tools included, but I installed the new Platform SDK and they're not included. Is there another SDK that I need to download and install now?

View Replies !

How To Create Extended Stored Procedure?
Hi All,

I am looking for good samples about extended stored procedure using C++.
Is anyone know a Web site where I could downloaded that?

Thanks
Herve

View Replies !

Problem With Extended Stored Procedure
I am trying to run an extended stored procedure recently loaded on my development server.

error msg:
Cannot load the DLL 'nnnn_ci.dll'. Reason: '126(The specified module could not be found.)'
(Message 0)

I have the dll in the mssql/binn directory. I scripted adding the extended stored procedures from my production system. I ran the script, queried
the syscomments and sysobjects tables in master. everything is identical to production and looked normal. I have stopped and started the SQL
server and re-booted the server. I have other extended stored procedures on the system that are working fine.

At this point I am totally stumped. ANY suggestions would be greatly appreciated.
Thanks
tcb

View Replies !

Extended Stored Procedure Xp_fixeddrives
We are on Windows 2000, SQL Server 2000, and a SAN. Our data and backups are located on the Storage Area Network.

I am attempting to restore a database with a backup on the SAN through Enterprise Manager. I selected 'From Device', 'Selected Devices'. SQL appears to hang. After looking at Current Activity, I have identified that it is running 'EXECUTE master.dbo.xp_fixeddrives 2'.

Are there any known problems with SQL Server 2000 and SAN? Has anyone encountered this error?

Thanks
Kim

View Replies !

Missing Extended Stored Procedure
I have installed Standard Sql 2000 .
However i don't find the ex stored Procedures xp_sqlinventory and sp_sqlregister ..
Can anyone Point out here i can get them?

Thanks

View Replies !

Error In Extended Stored Procedure
Hi Everybody,

I have created an Extended Stored Procedure without any error in 'master' database, i.e. sp_addextendedproc @functname = xp_dump, @dllname = 'xp_dump.dll'. In xp_dump.dll, I have written a function with xp_dump name. I have developed this DLL in VB with the following sample code:

Function xp_dump() As Boolean
Dim con As New ADODB.Connection
Dim auid As String
auid = "999-99-9999"
con.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=*********;Initial Catalog=pubs;Data Source=WASSERVER"
con.BeginTrans
con.Execute ("INSERT INTO table999(au_id,au_lname,au_fname,phone,address,cit y,state,zip,contract) SELECT '" & auid & "',au_lname,au_fname,phone,address,city,state,zip, contract FROM table999 WHERE au_id='213-46-8915'")
con.CommitTrans
con.Close

End Function

But when I am trying to EXECUTE the same in Query Analyzer the following error is found:

ODBC: Msg 0, Level 16, State 1
Cannot find the function xp_dump in the library xp_dump.dll. Reason: 127(The specified procedure could not be found.).

Kindly help me to resolve my problem.

Thanking you.

Michael.

View Replies !

Extended Stored Procedure And C Code
Hi,

I am using vs2005, vc++, sql2005 and trying to write a complex extended stored procedure.

It seems I must use switch clrafe for my executable to deploy to sql.

But with clrafe, I can no longer use such c code tools such as typedef structures and pointer manipulation!

But I need to do these things to translate a contiuous block of binary data into a series of field/value pairs to be written to the sql server!!! Doing so externally would mean a severe drop in throughput: going back to the server for each field instead of all as one block!

Is there a technique, or pragma command to allow "unmanaged" c code to reside in a sql extended stored procedure?

View Replies !

Extended Stored Procedure Error
Hi I have written a small C++ Extended Stored Procedure

#include "CPPDLL.h"

#include <windows.h>

#include <Srv.h>

#include <stdio.h>

#define XP_NOERROR 0

#define SRV_MAXERROR 20000

#define CMDSHELL_ERROR SRV_MAXERROR + 2

BOOL APIENTRY DllMain( HMODULE hModule,

DWORD ul_reason_for_call,

LPVOID lpReserved

)

{

return TRUE;

}

int xp_Easy(SRV_PROC * srvproc)

{

srv_sendmsg(srvproc, SRV_MSG_INFO , CMDSHELL_ERROR, SRV_INFO, (DBTINYINT)0,

NULL, 0, 0, "This is how easy XP's can be!", SRV_NULLTERM);

srv_senddone(srvproc, SRV_DONE_MORE, 0, 0);

return(XP_NOERROR);

}

When I register the DLL in SQL server 2005 and execute it. I get the following error message

Msg 17750, Level 16, State 0, Procedure xp_Easy, Line 1

Could not load the DLL C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinnCPPDLL.dll, or one of the DLLs it references. Reason: 14001(This application has failed to start because the application configuration is incorrect. Reinstalling the application may fix this problem.).

Does anyone know why this is happening.

Thanks

Reeves

View Replies !

Migration Of Extended Stored Procedure
Hi ,

Any Body can help in knowing, How i can migrate the Extended Stored Procedure from one server to another.

View Replies !

Can Anybody Explain Me What Is An Extended Stored Procedure And When Can It Be Useful ?

Hello Friends.....
I want to ask that What is mean by Extended Sored Porcedure and when can it be useful ?

Where can be it useful in our day to day SQL Seveloper's activites ?

waiting for reply....

View Replies !

How To Get Extended Error Information From Stored Procedure
Hi
I have a stored procedure in SQL server 2005. It works fine when I execute it from the Management Studio.But when executing it from ASP.NET code like this:
..... Of course more code is executed before this call .....int retVal = this.odbcCreateDataBaseCommand.ExecuteNonQuery();
retVal is -1. But -1 doesn't really tell me what the problem is?
Is there anyway to get extended error information so I can figure out whats going wrong?
(The stored procedure was working fine in SQL server 2000 before I upgraded to SQL server 2005. I use .NET Framework 1.1 and ODBC Sql Native Client to access the 2005 server.)
Regards
Tomas

View Replies !

Extended Stored Procedure Performance Tuning
We have an application that is based on several extended storedprocedures. When we run our application in house, or when most othercustomers run it, they see performance of about X transactions persecond. One customer is seeing performance of about X/5, and I'mhaving a hard time troubleshooting it. The performace bottleneck hasbeen narrowed to the execution of the extended stored procedures. Doesanyone know of tuneable SQL Server parameters that may specificallyaffect the performance of extended stored procedures. I know theprocedures get run by a scheduler. Is there some way the priority orfrequency of the scheduler can be modified? Thanks for any advice.

View Replies !

Scalability Questions For Extended Stored Procedure
Hi,First I would like to apologize for cross posting in three groups asI did not know which one would be the appropriate group. If some onepoints me to the correct one I will use that in the future.I am trying to findout the scalabilty of an user written extendedstore procedure. I have created a dll using a C program(modified/plagarized from some of the examples) . The main function ofthis extended SP is to act as a passthru to connect to an third partyODBC driver. All this is supposed to do is take the passthru sqlstatement, userid, passsword and connect to a remote server (IBM 3090Mainframe in our case) using a system ODBC connection and pass thereturned result set back to the stored procedure calling this extendedSP. I am trying to find out the answers for the following questions.1. What are the limitations of this approach.2. What would happen say if 2,000 concurrent calls per minute are madeto the extended SP from the web app.3. What would happen if this continued for say 4 hours. Will the memoryusage increase to point that will cripple the server assuming there isno memory leak in the dll.4. Are there any connection pooling concerns that I should payattention to specifically from an Extended SP point of view.5. Apart from compiling the dll using the "MultiThread" option should Ibe using other options to make sure the dll is threadsafe.SQL server Environment :OS - Windows 2000 Advanced Server SP4SQL - SQLServer 2000 Enterprise edition SP3Hardware - 8 way 2 node cluster with 6Gb RAMAny help regarding this is greately appreciated.Prahalad

View Replies !

Extended Stored Procedure To Query From Database?
Hi everyone,

I am trying to write a extended stored procedure to query from a database and then write it to a file. Does anyone have experience with this?

Appreciate all your help,

cobenho

View Replies !

Manipulating Result Set Of Extended Stored Procedure
Can anyone help?

I need to be able to manipulate result set of a completed Extended Stored Procedure (SQL 7 or 2000), perhaps by putting the results into a temporary table (??)

I call my dll with a fixed number of address parameters from Query Analyzer as follows:

EXEC master.dbo.xp_geozip param1, param 2, to number of params

I see the (correct) results in a results grid at the bottom of the screen. The result can either be a single row or a multiple rows. However, I cannot process the results further.

If my resulting columns are: col1 col2 col3 col4; how can I individually select fields from this result set or put these results into a temporary table or assign them to variables? I don't have the option of calling the dll with output params.

In Informix there is a structure which apparently allows you to insert results of a stored procedure into a dynamically-created temp table.
select execute nameofstoredprocedure into nameoftemp_table or similar. Does anyone know of a similar capability in SQL Server.

Thanks in advance

View Replies !

Trigger Calling Extended Stored Procedure
Hello,

I've installed a "For Update" trigger for some table that calls a extended stored procedure (ESP). In some cases, the ESP needs to modify the same table, maybe causing a recursive call of the trigger.

Is there a way to "disable" the trigger inside the code just before the operation that updates the table and "re-enable" it just after? I know I can disable recursive triggering at database level, but I'm developing a database add-on so I don't want to interfere with the existing triggers behavior (if any) for the rest of the tables.

10x.

View Replies !

Creating DLL File For Extended Stored Procedure In C#
Hi All,

Is there any way that I can create a dll file for Extended stored procedure(SQL server) in C# ?

I an able to create that in VC++. in VC++ there is DLLMain which serve as entry point and a paramter can be passed to it using Extended stored procedure. But since C# does not have any such entry point. Hence is there any way that I can achieve this?

Any pointers to this will be highly appreciated.

Thanks
Sid

View Replies !

Where To Look For Extended Stored Procedure DLL Wizard In VS 2005
Hi

View Replies !

Extended Stored Procedure Sp_OAMethod Failed

Hi,

I got an problem while executing a extended stored procedure from SQL Server 2000.

I have a extended stored procedure (C++ com dll) called "TisNotificationCom.dll" which creates a Message and sends to MSMQ, When I am executing com dll from the stored procedure as shown below:

    begin
        select @objectname = 'TISNotificationCom.TISNotification',
               @methodname = 'SendXferNotificationTo'
    end

    exec @sp_create_ret = sp_OACreate @objectname, @oaObject output

   exec @sp_method_ret = sp_OAMethod @oaObject,
        @methodname,
         @method_ret output,
         @notificationdata,
         @seventtype,
         @sRowid,
         @eventdata,
         '3'

-----------------
in the above code the sp_OACrete method is success full, - The 'TISNotificationCom.TISNotification' is loaded successfully,
but sp_OAMethod is failed. - The Com class instance creation is failed.

When i debug the dll, the dll is loading and going to the 'TISNotificationCom.TISNotification' constructor and returning without creating instance of the class and unloading the dll.
-----------------------------------
When I execute the same on different machine with the same environment with Win2000 server and SQL server 2000, it is executing successfully and sending message to MSMQ whithout any issue.
------------------------------------
What could be the problems?
Can any one provide me the solution

Rajith,

View Replies !

Extended Stored Procedure Using C# For SQL Server 2000

Can anyone of you help me in finding online - resources to write extended stored procedure for Sql Server 2000 using C#.

Thank you,

Andy Rogers

View Replies !

Extended Stored Procedure In SQL SERVER 2005
The error list below is the one we are getting when attemping to run the extended stored procedure

Msg 17750, Level 16, State 0, Procedure sp_HVC_helpDLL, Line 1

Could not load the DLL C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinnsp_hvc.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).

As per earlier test to run the extended stored procedure in SQL SERVER 2005 server,it was successful,the edition of the SQL SERVER 2005 was Microsoft SQL Server 2005 - 9.00.1406.00 (Intel X86) Mar 3 2007 18:40:02 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1).

The extended stored procedures are running fine even without the registering them with full path of the dll i.e. â€œC:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinnsp_hvc.dllâ€?.

The same procedure was run in an another server with the following edition

Microsoft SQL Server 2005 â€“ 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright Â© 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1).

We suspect that the failure of the extended stored procedures might be due to the difference in SQL SERVER 2005 Editions.

Would this information do any help?
Can any one help

View Replies !

Extended Stored Procedure - Adjacency List To Nested Set
Hello,

I'm new here. If this isn't the right group for this question, please let me know.

I'm working with sql server 2000 (and learning C++) and I wanted to know if it is possible to create an extended stored procedure that would convert an adjacency list model to a nested set model (http://www.developersdex.com/gurus/articles/112.asp?Page=1) on-the-fly. I have done this in a regular stored procedure, but was curious if an extended stored procedure might be faster. I haven't seen any C++ examples though ..

Any thoughts or suggestions would be appreciated.

View Replies !

Recreating The Functionality Of A Extended Stored Procedure That Uses Srv_impersonate_client
I need help rewriting an extended stored procedure as a CLR.

What this extended stored procedure does is to return the domain username of the person connected via named pipes.

This is accomplished by using the srv_impersonate_client and GetUserName functions from opends60.lib. I have tried rewriting this in CLR using Microsoft.SQLServer.Server.SQLContext.WindowsIdentity but have been unable to replicate the functionality to return the same values as the srv_impersonate_client.

If anyone knows how I can rewrite this as CLR, let me know, Also I am looking for where I can get a 64bit version of opends60.lib to run on an amd64

Thanks
Darryl

View Replies !

Delete / Drop Extended Stored Procedure In 2005
We have a secuiry review and they have recommended dropping several xp_ stored procedures. I have tried the drop procedure with no luck, Error 3701, or right click delete in Man Studio, same error. I have granted the user alter permission to the master database and when I try to delete get Error 4606. I try to grant CONTROL permission of the stored proc to the user and get another 4606 error.

Do I just have to control the permissions of these procedures tightly?

Thanks In Advance
MPM

View Replies !

Creating Extended Stored Procedure Using Vs.net 2005 For Sql2005

Hi,

web searches give no end of how extended stored procedures can only be written in C++ ( or maybe vb also) .

And that extended stored procedures should be abandonded in favour of CLR framework procedures.

And how most articles explain how to convert ESPs to CLR procedures!!!!!

But I need to pass a non-discript block of binary data, extract pieces of data identified by its offset into the block, data type inferred by offset; into data to be written to the SQL database. These offsets are determinede by mapping (C UNION) to C typedef structures.

This cannot be done by managed code, therefore cannot be done by C++ CLR.

It is also ill suited for C# .

Sounds like a job for C++ extended stored procedure.

But how does one create and deploy an ESP with Visual Studio 2005? All wizards seem to insist on CLR.

Help!?

Boyd

View Replies !

An Extended Stored Procedure Problem With SQL Server Express

I have created an extended stored procedure (that executes a DLL file) in SQL Server 2005 Express. When I execute this procedure the execution enters an infinite loop and hangs there. When I execute the same procedure on SQL Server 2000 it executes correctly. I wonder if the problem relates to SQL Server 2005, since no problem occurs with SQL Sever 2000. And if so, how could I repair it?

Best Regards,

Ramy

View Replies !

Extended Stored Procedure - ODBC Loopback Connection Problem
Hi,I have a loopback connection using ODBC in the DLL initialization codeofthe SQL Server ESP Module (SQL Server 2000). The loopback connectionworksfine when the DSN is specifed with the "NT Authentication", however thesamefails when specified with the "SQL Server user authentication". I havetriedusing both the SQLConnect and SQLDriverConnect calls, butu none of themworks. Also the same code works fine on SQL Server 2005. Is this aknownproblem with some fix, or am I doing something wrong here??The code is as given below,// ESPODBCLoopback.cpp : Defines the entry point for the DLLapplication.//#include "stdafx.h"#include <sql.h>#include <sqlext.h>#include <srv.h>#define XP_NOERROR 0#define XP_ERROR 1#define SEND_ERROR(szMessage, pServerProc) { srv_sendmsg(pServerProc, SRV_MSG_ERROR, 20001, SRV_INFO, 1, NULL, 0, (DBUSMALLINT) __LINE__, szMessage, SRV_NULLTERM); srv_senddone(pServerProc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0); }// typedef const char* (_MakeODBCConnection)(void);static const char* _szMessage = "ODBC Working out....";void_MakeODBCConnection(void){char szConnOut[1024];SQLSMALLINT nOut = 0;const char* szDSNName = "TestOdbc";const char* szUsername = "test";const char* szPassword = "test";SQLHANDLE hEnvironment = NULL;SQLHANDLE hDBConnection = NULL;if (SQL_ERROR == SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE,&hEnvironment)) {_szMessage = "Failed to create the environment handle";return;}SQLSetEnvAttr(hEnvironment, SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,SQL_IS_INTEGER);if (SQL_ERROR == SQLAllocHandle(SQL_HANDLE_DBC, hEnvironment,&hDBConnection)) {SQLFreeHandle(SQL_HANDLE_ENV, hEnvironment);_szMessage = "Failed to create the database connection";return;}/*----------------- This is where it fails -------------------*//* Tried both the with/Without database name */if (SQL_ERROR == SQLDriverConnect(hDBConnection, GetWindow(,(SQLCHAR*)"{DSN=TestOdbc;UID=test;PWD=test;DATABASE=test;}", SQL_NTS,(SQLCHAR*)szConnOut, sizeof(szConnOut), &nOut, SQL_DRIVER_COMPLETE)){SQLFreeHandle(SQL_HANDLE_DBC, hDBConnection);SQLFreeHandle(SQL_HANDLE_ENV, hEnvironment);_szMessage = "Failed to connect to the database";return;}/*if (SQL_ERROR == SQLConnect(hDBConnection, (SQLCHAR*)szDSNName,SQL_NTS,(SQLCHAR*)szUsername, SQL_NTS, (SQLCHAR*)szPassword, SQL_NTS)) {SQLFreeHandle(SQL_HANDLE_DBC, hDBConnection);SQLFreeHandle(SQL_HANDLE_ENV, hEnvironment);_szMessage = "Failed to connect to the database";return;}*/SQLFreeConnect(hDBConnection);SQLFreeHandle(SQL_HANDLE_DBC, hDBConnection);SQLFreeHandle(SQL_HANDLE_ENV, hEnvironment);_szMessage = "ODBC Connection cycle completed successfully";}BOOL APIENTRY DllMain( HANDLE hModule,DWORD ul_reason_for_call,LPVOID lpReserved){switch (ul_reason_for_call){case DLL_PROCESS_ATTACH:_MakeODBCConnection();break;case DLL_THREAD_ATTACH:break;case DLL_THREAD_DETACH:break;case DLL_PROCESS_DETACH:break;}return TRUE;}static void_CheckODBCConnection(void){//_MakeODBCConnection pFunction = NULL;//_szMessage = pFunction();}extern "C" __declspec(dllexport)RETCODE xp_test_odbc(SRV_PROC *pServerProc){//_szMessage = _MakeODBCConnection();if (FAIL == srv_paramsetoutput(pServerProc, 1, (BYTE*)_szMessage,(ULONG)strlen(_szMessage),FALSE)) {return XP_ERROR;}return XP_NOERROR;}Thanks,Anil KumarArizcon Corporation ( http://www.arizcon.com )

View Replies !

Help!! SQL Server 2000 Extended Stored Procedure Hangs In Windows 98
I am trying to run xp_cmdshell from the Query Analyzer using SQLServer 2000 running on Windows 98.It seems like it should be simple - I'm typingxp_cmdshell 'dir *.exe'in the Query Analyzer in the Master db. I'm logged in as sa.The timer starts running and never stops. No error message.Can anyone PLEASE help me with this? Any suggestions would beappreciated. Are SQL Server 2000 extended stored procedures notsupported in Windows 98? I've tried searching the Knowledge Base butcan't find anything.Thanks!

View Replies !

How To Enable Extended Stored Procedure For MSSQL2005 Which Is Desabled For Vista?

Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 6.0 (Build 6000: )

UAC turned off. MSQSL runs under Administrator account.

Any call of user esp - and SQL server goes to loop.
If I call system esp - all works fine.

If I try to read properties of ESP from Management Studio - I have the same effect - SQL server is in loop.

select object_id('dbo.xp_mylog')
-- works.

EXEC('sp_helpextendedproc ''xp_mylog''')
xp_mylog c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinnLogEsp.dll

exec master..xp_mylog
die forever

View Replies !

Problem Passing Variables As Parameters To Extended Stored Procedure
Hello all,

I have written an XP for SQL Server 2000 SP2. It performs as expected if I call the XP with literal values for the parameters, however when I wrap the XP call into a regular stored procedure, only the first character of each input string is seen by the XP! Here are the relevant code snippets:

C++ Extended Stored Procedure:

(Basically all this code is doing is retrieving the parameters and printing them back out)

srv_paraminfo(srvproc, 1, &bType, &uMaxLen, &uLen, NULL, &bNull);

param1 = new BYTE[uLen + 1];

srv_paraminfo(srvproc, 1, &bType, &uMaxLen, &uLen, param1, &bNull);

param1[uLen] = '';

srv_paraminfo(srvproc, 2, &bType, &uMaxLen, &uLen, NULL, &bNull);

param2 = new BYTE[uLen + 1];

srv_paraminfo(srvproc, 2, &bType, &uMaxLen, &uLen, param2, &bNull);

param2[uLen] = '';

srv_paraminfo(srvproc, 3, &bType, &uMaxLen, &uLen, NULL, &bNull);

param3 = new BYTE[uLen + 1];

srv_paraminfo(srvproc, 3, &bType, &uMaxLen, &uLen, param3, &bNull);

param3[uLen] = '';

sprintf(msgText, "Params received by xp: %s, %s, %s", param1, param2, param3);

srv_sendmsg( srvproc, SRV_MSG_ERROR, 0,(DBTINYINT)0, (DBTINYINT)0,NULL,0,0,msgText,SRV_NULLTERM);

srv_senddone(srvproc, SRV_DONE_ERROR, (DBUSMALLINT)0, (DBINT)0);

Calling the XP with literal values:

EXEC xp_mytest 'one','two','three'

Output:

Params received by xp: one, two, three

Calling XP via a stored procedure:

create procedure sp_mytest

(

@myvar1 nvarchar(200),

@myvar2 nvarchar(50),

@myvar3 nvarchar(50)

)

as BEGIN

PRINT @myvar1

PRINT @myvar2

PRINT @myvar3

EXEC xp_mytest @myvar1, @myvar2, @myvar3

END

EXEC sp_mytest 'one','two','three'

Output:

one

two

three

Params received by xp: o,t,t

Any insight or assistance is greatly appreciated!!!

View Replies !

Retrieving Result Set From Dynamically Called Stored Procedure Or Function In A Function
Is there any way I can retrieve the result set of a Stored Procedurein a function.ALTER FUNCTION dbo.fn_GroupDeviceLink(@groupID numeric)RETURNS @groupDeviceLink TABLE (GroupID numeric, DeviceID numeric)ASBEGINDeclare @command nvarchar(255)SELECT @command = Condition// @command is an SQL string or stored procedue nameFROM DeviceGroupWHERE GroupID = @groupIDINSERT @groupDeviceLinkEXEC @commandRETURNENDIs there any way i can do anything like this. @command is a variableholding the name of a stored produre. I need to run that storedprocure and return the values in such a way that they can be used in aSELECT StatementMy goal is SELECT * FROM Device INNER JOINdbo.fn_GroupDeviceLink(@groupID) ON ....this fn_GroupDeviceLink should run the proper stored procedure andreturn the values. What i also want to do is play with that result setof the specific stored procedure before i return it. Is this possible?If not, what is the work arround?ThanksMark

View Replies !

Gridview / SqlDataSource Error - Procedure Or Function &<stored Procedure Name&> Has Too Many Arguments Specified.
Can someone help me with this issue? I am trying to update a record using a sp. The db table has an identity column. I seem to have set up everything correctly for Gridview and SqlDataSource but have no clue where my additional, phanton arguments are being generated. If I specify a custom statement rather than the stored procedure in the Data Source configuration wizard I have no problem. But if I use a stored procedure I keep getting the error "Procedure or function <sp name> has too many arguments specified." But thing is, I didn't specify too many parameters, I specified exactly the number of parameters there are. I read through some posts and saw that the gridview datakey fields are automatically passed as parameters, but when I eliminate the ID parameter from the sp, from the SqlDataSource parameters list, or from both (ID is the datakey field for the gridview) and pray that .net somehow knows which record to update -- I still get the error. I'd like a simple solution, please, as I'm really new to this. What is wrong with this picture? Thank you very much for any light you can shed on this.

View Replies !

SqlDataSource.SelectParameters Causing Procedure Or Function Stored Procedure Has Too Many Arguments Specified.
Hi everybody, I am having trouble how to fixed this code. I am trying to supply the parameterinside a stored procedure with a value, and displays error message shown below. If I did not supply the parameter with a value, it works. How to fix this?Error Message:Procedure or function <stored proc name> has too many arguments specified.Thanks,den2005
Stored procedure:

Alter PROCEDURE [dbo].[sp_GetIdeaByCategory]
@CatId int = 0
AS
BEGIN
SET NOCOUNT ON;

Select I.*, C.*, U.* From Idea I inner join IdeaCategory C on I.CategoryID = C.IdeaCategoryID inner join Users U on I.UserID = U.UserID Where I.CategoryID = @CatId Order By LastModifiedDate Desc
End

oDataSource.ConnectionString = constr;
oDataSource.SelectCommand = storedProc;//storedproc - sp_GetIdeaByCategory
oDataSource.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
oDataSource.SelectParameters.Add("@CatId", catId);
gdvCategories.DataSourceID = oDataSource.ID;

gdvCategories.DataBind(); <<--- Error occured here

View Replies !

Procedure Or Function 'stored Procedure Name' Expects Parameter Which Was Not Supplied
Has anyone encountered this before?
Procedure or Function 'stored procedure name' expects parameter '@parameter', which was not supplied.
It seems that my code is not passing the parameter to the stored procedure.
When I click this hyperlink:
<asp:HyperLink
ID="HyperLink1"
Runat="server"
NavigateUrl='<%# "../Division.aspx?CountryID=" + Eval("CountryID")%>'
Text='<%# Eval("Name") %>'
ToolTip='<%# Eval("Description") %>'
CssClass='<%# Eval("CountryID").ToString() == Request.QueryString["CountryID"] ? "CountrySelected" : "CountryUnselected" %>'>
</asp:HyperLink>
it is suppose to get the country name and description, based on the country id.
I am passing the country id like this.
protected void Page_Load(object sender, EventArgs e)
{
PopulateControls();
}
private void PopulateControls()
{
string countryId = Request.QueryString["CountryID"];
if (countryId != null)
{
CountryDetails cd = DivisionAccess.GetCountryDetails(countryId);
divisionNameLabel.Text = cd.Name;
divisionDescriptionLabel.Text = cd.Description;
}
}
To my app code like this:
public struct CountryDetails
{
public string Name;
public string Description;
}
public static class DivisionAccess
{
static DivisionAccess()
public static DataTable GetCountry()
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetCountry";
return GenericDataAccess.ExecuteSelectCommand(comm);
}
public static CountryDetails GetCountryDetails(string cId)
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetCountryDetails";
DbParameter param = comm.CreateParameter();
param.ParameterName = "@CountryID";
param.Value = 2;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
CountryDetails details = new CountryDetails();
if (table.Rows.Count > 0)
{
details.Name = table.Rows[0]["Name"].ToString();
details.Description = table.Rows[0]["Description"].ToString();
}
return details;
}

As you can see I have two stored procedures I am calling, one does not have a parameter and the other does. The getcountry stored procedure returns the list of countries in a menu that I can click to see the details of that country. That is where my problem is when I click the country name I get
Procedure or Function 'GetCountryDetails' expects parameter '@CountryID', which was not supplied
Someone please help!

Thanks Nickdel68

View Replies !

Extended Stored Procedure Problem (srv_describe Function)...