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:
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.
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'
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.
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??
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.
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
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
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
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.
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?
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.
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!!!
...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?
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?
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.
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????
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.
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?
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'm attempting to copy over extended procedures from another database, and it's only copying stored when I use the "Transfer Database" wizard. Anyone know why it doesn't copy extended, and how I can get it to? Thanks!
I have to write a stored procedure to gather .dat files from multiple network shares to a single location and bulk insert them using format files. I have the SP working that accepts the filename and performs the bulk insert into my SQL tables using the appropriate format file.
Now, I had originally wrote the "copy files" portion in VB using the FSO, but was told it has to all be in a SQL SP. I have been investigating the xp_cmdshell extended procedure, which would work if I weren't pulling the .dat files from a Novell volume. For example connecting to a windows share this works...
exec xp_cmdshell 'dir servershare', no_output
but not when using a servervolume (Novell), even though from the command prompt this command will work to the novell volume.
Does anyone know of any other extended procedure or method in T-SQL that can perform file system functions?
Hi, I€™m trying to secure my SQL Server 2005 infrastructure, and I€™m seeing that some sites are recommending that certain extended procedures be restricted to sysadmin only.
http://www.sqlsecurity.com/FAQs/SQLSecurityChecklist/tabid/57/Default.aspx This site recommended securing the following extended procedures:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3184075&SiteID=1 This thread recommended (implicitly) securing the following extended procedures:
Looking at these lists, I can see they might have missed other extended procedures like xp_regwrite, xp_regdeletekey, and xp_regdeletevalue.
My questions are: Is there any way I can find an exhaustive list as to what extended procedures should be restricted? Is there a website/Microsoft resource that can help me identify what to restrict?
Any other information you can point me to to secure our infrastructure would be appreciated.
I have a extended procedure for sending email by smtp already.
Are there anywheres can be downloaded the rest? e.g. copy, move, detect, and delete some files and directories in harddisk access Active directories read and write .ini files etc.......
I am trying to run a stored procedure in SQL 2005 which calls a registry value using the RegRead stored procedure which in conjunction executes a line of code. This stored procedure has previously worked in both Sql 7/2000.
However, I am getting the following error when trying to run it in SQL 2005:
Msg 22001, Level 16, State 1, Line 0 xp_regread() returned error 5, 'Access is denied.'
Does anyone know if Extended Stored Procedures are supported in SQL 2005?
Hi(sorry this has turned into a bit of an epic...thought I'd as theexperts!)SQL 2k, sp3a. Dual xeon 2.4. 2 gig ram. everything on a 3 disk raid 5.Microsoft SQL Server 2000 - 8.00.760 (Intel X86)Enterprise Edition on Windows NT 5.0 (Build 2195: )Problem seems to be some extended procs that seemed to be running fineon a 'vanilla' install on a different server now seem to be failing.Failures occuring c 6 - 10 hours.Sometimes the failure logs as the xp failure with severity 20 that Ikindof expect :s. other times absolutely nothing is logged.There are a variety of error messages - or sometimes nothing at all.http://www.mapinfo.com/common/docs/.../SWMSS48_RN.pdfdescribes one of our issues, which seems to come out on the restartafter a failure:Error event id is 19011, Message "SuperSocket info:ConnectionListen(Shared-Memory (LPC)) : Error 5."That site suggests:[color=blue]>Workaround>Suggested solutions include:>• Obtain a hotfix from Microsoft (http://www.microsoft.com). You will[/color]need to >cite the 94302[color=blue]>reference number.>• Turn off shared memory in the client network configuration utility[/color]to >disable shared[color=blue]>memory.>• Replace the SQL Server SSMSLPCN.DLL with the SSMSLPCN.DLL[/color](8.00.540) from >SQL[color=blue]>Server 2000 Service Pack 2.[/color]- but I cant find any sensible information which describes this tocorrelate! Searching MS for that number (94302) came up with zip.The dll version we are running is 2000.80.760.0, although earlierversions are elsewhere on the server (disk images).Interesting to note that we seem to be able to replicate the 'leakage'of improperly closed handles on this version, but not on the oldserver runningthat dll version 2000.80.194.0. This I understood to have been fixedin sp3.On one failure we caught the following (involving our extended procs):2004-02-15 15:03:31.40 spid58 Error: 0, Severity: 20, State: 02004-02-15 15:03:31.40 spid58 Stored function'xp_Contest_CalculateScores' in the library 'e:sqlxpga_procs.dll'generated an access violation. SQL Server is terminating process 58..2004-02-15 15:04:32.14 spid64 Error: 0, Severity: 20, State: 02004-02-15 15:04:32.14 spid64 Stored function 'xp_XML_GetSubTree'in the library 'e:sqlxpga_procs.dll' generated an access violation.SQL Server is terminating process 64..2004-02-15 15:04:48.48 spid53 Using 'dbghelp.dll' version '4.0.5'*Dump thread - spid = 53, PSS = 0x472d7200, EC = 0x725fc098*Stack Dump being sent to e:sqlMSSQLlogSQLDump0001.txt* ************************************************** ******************************* BEGIN STACK DUMP:* 02/15/04 15:04:48 spid 53This was followed about a minute later by:2004-02-15 15:04:55.75 spid53 Stack Signature for the dump is0x1CC032652004-02-15 15:04:55.75 spid53 SQL Server Assertion: File:<recbase.cpp>, line=1378Failed Assertion = 'm_offBeginVar < m_SizeRec'.2004-02-15 15:04:55.89 spid53 Using 'dbghelp.dll' version '4.0.5'*Stack Dump being sent to e:sqlMSSQLlogSQLDump0002.txt2004-02-15 15:04:56.01 spid53 SqlDumpExceptionHandler: Process 2292generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQLServer is terminating this process.* ************************************************** ******************************* BEGIN STACK DUMP:* 02/15/04 15:04:55 spid 53As a side issue we have a number of 'initialisation of notify failed'notes.Any one have any clue about this?Might the DLL need rolling back?Is sp3a knacked for extended procs?Is there some daft setting I've missed :/Any good events to track in particular?Cheers all!
I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
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
