SQL / Extended Proc Dll's
Oct 25, 2000
I created an extended procedure DLL according to the guidelines set forth in the Books Online. I placed this DLL to be called in a trigger. The DLL fires, but some aspects of the dll fail. Is there a C lang. limitation when used with SQL server? I can do file i/o in the dll (open a file, write to it) but other functions such as C API functions FindWindow / SendMessage always fail. I have tested the DLL outside of SQL server and it works fine. Whats up, I can't beleive that Microsoft would write SQLServer where only certain functions of the DLL called by SQL server will work.. Any ideas would be appreciated! KT
View 1 Replies
ADVERTISEMENT
Jun 26, 2007
Hi,
I have written an extended stored proc: xp_DoSomething that can be called from T-SQL:
Code Snippet
declare @txt varchar(max)
exec xp_DoSomething @txt output
select @txt
The proc generates a correct value for @txt, but I can't get the result into the output parameter - the important part of the code is:
Code Snippet
if(FAIL == srv_paramsetoutput(srvproc, 1, dataPtr, len, FALSE))
{
throw OutputParameterDataError();
}
It seems that srv_paramsetoutput function always returns FAIL if type of the output parameter is LOB (varchar(max),nvarchar(max), varbinary(max)). Is there any way to return LOB parameter by an extended procedure?
Any help gratefully received
Thanks,
Va1era
View 2 Replies
View Related
Feb 19, 2015
I know very little about Extended Events, but I know it is supposed to be more powerful than Profiler. The SQL Instance involved is 2008R2. I was asked whether we could capture when a stored proc was called with a certain parameter. So for example, if dbo.usp_mystoredproc is called and is passed a value of '12345a' for @customer, can that be captured? I would want to know the time it was called, the parameter and parameter value. Probably would be interested in the SPID or LoginName as well.
View 4 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
Jun 15, 2006
Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK
View 3 Replies
View Related
Feb 23, 2007
I have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.
View 1 Replies
View Related
Apr 8, 2004
Is it possible to retrieve the resultset of a stored procedure from another procedure in sql server 2000.
Basically I am calling proc2 from the inside of proc1.
proc2 returns 2 resultsets. I want to process these two resultsets
from within proc1.
If its possible , please provide sample code.
thanks in advance,
Alok.
View 1 Replies
View Related
Feb 13, 2008
I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code.
So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message:
Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages.
(1 row(s) affected)
(1 row(s) affected)
I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution?
Also, Is there a way to trace into a stored procedure through Query Analyzer?
-------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised:
SELECT @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId
IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END
View 3 Replies
View Related
Oct 11, 1999
i'm calling a create table SQL from my ASP. There are a lot of fields created
by this sql. Wondering if an extended stored procedure would be a better choice? (can't use a stored procedure as i need to pass arrays) Will it enhance performance and also, can i use VB to make that DLL or i need to use VC?
thanks
View 1 Replies
View Related
Oct 5, 2004
Get current free space for all drives in megabytes (Documented)
EXEC master.dbo.xp_fixeddrives
Determine if file exists (UnDocumented Procedure)
DECLARE @i int
EXECUTE master.dbo.xp_fileexist '\myServerc$oot.ini', @i OUTPUT
SELECT @i
View 3 Replies
View Related
Mar 8, 1999
Hey everyone,
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!
Alex
View 1 Replies
View Related
Aug 15, 2002
Hi all,
Plz help me to know whats wrong with following script:
EXEC sp_addextendedproperty
@name = 'col_Programs_ID_Description',
@Value = 'The Unique Identifier for a specific Program',
@level1Type = 'Programs',
@level2Type = 'ID'
I get the following error:
Server: Msg 15600, Level 15, State 1, Procedure sp_addextendedproperty, Line 42
An invalid parameter or option was specified for procedure 'sp_addextendedproperty'.
NOTE: If I dont use the last 2 parameters this procedure runs fine, but it adds the Extended Property to the Database Level while I'm trying to add it to the TABLE-->COLUMN level.
Thanks
Mariah
View 2 Replies
View Related
Aug 22, 2002
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?
Thanks in advance for any help!
View 4 Replies
View Related
Jul 20, 2005
I would like to know if I can determine the calling user from withinan extended stored procedure. I assume it's accessible in theSRV_PROC structure somewhere.Also, does anyone know of a comprehensive list of what is included inthe SRV_PROC structure?This is for SQL Server 2000.Thanks
View 2 Replies
View Related
Jul 11, 2007
Hi:
Does anyone know of a good primer on extended properties and why I would want to use them. I have you the on-line books sections but that does a clear "why". I have search for white papers and similar documents.
I understand they are good for documentation, but I was looking for something that explain Microsoft long-term vision. How I might use them in template scripts, VSTS, etc.
Thanks in advance for your help.
Gene
View 1 Replies
View Related
Oct 31, 2006
hi,
Can anyone tell me how to use the extended stored property like sp_addextendedproperty in java code?
Thanks,
Meghna
View 2 Replies
View Related
Oct 13, 2006
What is the procedure to create a extended stored procedure using C#.
Actually we have created a dll file using c# and it was added to the bin folder of mssql server.
Also dll file is added to the sql server using "xp_extendedproc"
But while executing the extended proc we got this error
"ODBC: Msg 0, Level 16, State 1
Cannot load the DLL xp_TestSql.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.)"
View 8 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
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
May 8, 2002
Please help to find an example of the code written on VC++ for an extended stored procedure (dll) for SQL Server 2000.
Thanks
View 1 Replies
View Related
Jun 25, 2002
I am looking for examples written in VB. Thanks ALOT!!
View 1 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
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
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
Nov 3, 2006
During a Wise Installation upgrade of our software, we are renaming a
directory that contains our ExtendedSP DLL. We issue a
DBCC TSWSQLXP(Free) call before doing the rename, yet SQL Server,
still "holds on" to that DLL. We install a new version, and the ListDLLs
utility (from sysinternals) lists the new DLL in the correct directory.
However, when trying to remove the renamed directory, it won't let us
remove the old DLL because it says it is in use. We can delete the NEW
DLL in the NEW directory with no problem.
I have run the DBCC call numerous times and SQL Server STILL won't
release the DLL for deletion. The only way to delete the OLD DLL is to
stop the SQL Server, delete the DLL, and then restart SQL Server. We
do NOT want to do this because there may be other processes running in
SQL Server.
Any help here would be greatly appreciated.
View 4 Replies
View Related
May 14, 2007
I require Api call or any ways to find the installed path of SQL SERVER 2005.
This is required becauses you application register set of extended procedures. As part of SQL SERVER 2005 we found that the dll has to be registered with full path in general
<b> "c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinn" </b>
since customers may install the server in any drive , i require to find the installed path.
A help on this will help me a lot..
Thanks
Ilanthendral.R
View 1 Replies
View Related
Jul 23, 2005
Is there an easy way to perform a SELECT where you haveblah LIKE 'asdf'but instead of just returning all the asdf's, it also looks for àsdf,ásdf, âsdf, etc?
View 4 Replies
View Related
Sep 11, 2007
Hello All,
i having one DLL which return Keywords.
i want to use that DLL in SQL Server Stored Proc.
as i know it is possible through Extended Stored Proc.
how to create that ? how i can pass Text Stream as input to that DLL in Extended Stored Procedure.
View 3 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
Aug 13, 2006
Under SSMS I can add simple text and carriage returns to an extended property associated with a column. SSMS preserves the <CRLF> formatting that I used. But when I try to read this same text using, for example,
SELECT name, value FROM ::fn_listextendedproperty
(NULL, 'schema', 'dbo', 'table', 'Customers', 'column', 'CompanyName')
I find that "value" has the <CRLF>'s removed! What am I doing wrong here?
TIA,
Barkingdog
View 1 Replies
View Related
May 7, 2008
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:
Extended Procedurs:sp_sdidebug xp_availablemedia xp_cmdshell
xp_deletemail xp_dirtree xp_dropwebtask
xp_dsninfo xp_enumdsn xp_enumerrorlogs
xp_enumgroups xp_enumqueuedtasks xp_eventlog
xp_findnextmsg xp_fixeddrives xp_getfiledetails
xp_getnetname xp_grantlogin xp_logevent
xp_loginconfig xp_logininfo xp_makewebtask
xp_msver xp_perfend xp_perfmonitor
xp_perfsample xp_perfstart xp_readerrorlog
xp_readmail xp_regread xp_revokelogin
xp_runweb
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3184075&SiteID=1
This thread recommended (implicitly) securing the following extended procedures:
Extended Procedures:sp_OACreate sp_OADestroy sp_OAGetErrorInfo sp_OAGetProperty
sp_OAMethod sp_OASetProperty sp_OAStop sp_sdidebug
xp_availablemedia xp_cmdshell xp_deletemail xp_dirtree
xp_dropwebtask xp_dsninfo xp_enumdsn xp_enumerrorlogs
xp_enumgroups xp_enumqueuedtasks xp_eventlog xp_findnextmsg
xp_fixeddrives xp_getfiledetails xp_getnetname xp_grantlogin
xp_logevent xp_loginconfig xp_logininfo xp_regread
xp_perfend xp_perfmonitor xp_perfsample xp_perfstart
xp_readerrorlog xp_readmail xp_revokelogin xp_runwebtask
xp_schedulersignal xp_sendmail xp_servicecontrol xp_snmp_getstate
xp_snmp_raisetrap xp_sprintf xp_sqlinventory xp_sqlregister
xp_sqltrace xp_sscanf xp_startmail xp_stopmail
xp_subdirs xp_unc_to_drive xp_dirtree
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.
View 6 Replies
View Related
Jun 6, 2007
Hello,
Can I read and write the extended properties with Access?
If it€™s possible is better create a project (ADP) or a classic MDB?
When I try with this query in the query panel (in a MDB)
SELECT objtype, objname, name, value
FROM fn_listextendedproperty
(NULL, 'schema', 'Person', 'table', default, NULL, NULL)
I have this error message:
The size of a field is too long
If I try with an ADP, when I ask a new query in design view, I have a message for some roblems between the Access version (2003) and SQL Server version (2005) and I can€™t save the query.
Tanks.
View 1 Replies
View Related
