Delete / Drop Extended Stored Procedure In 2005
Oct 5, 2007
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 1 Replies
ADVERTISEMENT
Jul 13, 2007
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 2 Replies
View Related
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
Apr 12, 2005
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 4 Replies
View Related
Jun 25, 2002
I am looking for examples written in VB. Thanks ALOT!!
View 1 Replies
View Related
Oct 15, 2001
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 1 Replies
View Related
Sep 11, 2007
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 4 Replies
View Related
Jan 31, 2005
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 4 Replies
View Related
Mar 25, 2002
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 3 Replies
View Related
Nov 19, 2001
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 2 Replies
View Related
Sep 21, 2001
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 1 Replies
View Related
Feb 17, 2004
Hi
Can i be able to use the DLL which is created in VB for Extended Stored Procedures.
if so how?
View 1 Replies
View Related
Mar 15, 2004
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 11 Replies
View Related
May 3, 2004
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 2 Replies
View Related
Jul 20, 2005
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 1 Replies
View Related
Jul 12, 2007
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 15 Replies
View Related
Feb 28, 2008
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 4 Replies
View Related
Jun 1, 2007
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 1 Replies
View Related
Nov 24, 2006
Hi ,
Any Body can help in knowing, How i can migrate the Extended Stored Procedure from one server to another.
View 1 Replies
View Related
Oct 3, 2007
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 1 Replies
View Related
Jan 15, 2015
I am logging into a SQL instance to run the following query:
DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_create_subdir N'sharemasterFULL' IF @ReturnCode <> 0 RAISERROR('Error creating directory.', 16, 1)
The share in which the folder is to be created has my account added with full permissions to create files. However this command fails unless I add the SQL Service account user with rights to the folder also.
Is this expected behaviour, is this something specific to extended stored procedures?
View 0 Replies
View Related
Jul 23, 2005
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 3 Replies
View Related
Jan 25, 2006
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 3 Replies
View Related
Aug 10, 2007
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 9 Replies
View Related
Mar 12, 2007
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 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
Jun 5, 2007
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 2 Replies
View Related
Mar 1, 2006
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 6 Replies
View Related
May 23, 2008
Hi,
I have been working around with SQL Server Express 2005 on VISTA. I created a small C++2008 application which interface with SSE 2005. When running my application, it automatically created a user instance which I would like to get rid of. I just forgot from start to set "User Instance = False" in my connectionstring. (oups...)
I'm aware, from readings I did, that a couple of tables as well as log files, etc. are created under directory "utilisateurs...AppDataLocalMicrosoftMicrosoft SQL Server DataSQLEXPRESS". SQLEXPRESS is the instance name chosen during install of SSE2005.
Also using following SQL Statement:
select owning_principal_name, instance_pipe_name, heart_beat
from sys.dm_os_child_instances
I got following result:
owning_principal_name: PC-DE-STEPHANEStéphane
instance_pipe_name: \.pipe6172F4E8-622E-4A sqlquery
heart_beat: dead (at this moment...)
==> How can I make a good cleanup of all this as well as get rid of the user instance itself?
Thanks in advance for any help.
Stéphane
View 7 Replies
View Related
Jul 23, 2005
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 6 Replies
View Related
Jul 20, 2005
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 1 Replies
View Related
Jun 20, 2007
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 1 Replies
View Related
