I would like to know if I can determine the calling user from within
an extended stored procedure. I assume it's accessible in the
SRV_PROC structure somewhere.
Also, does anyone know of a comprehensive list of what is included in
the SRV_PROC structure?
This is for SQL Server 2000.
Thanks
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 Relatedi 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
1. Is it legal and OK to use a MSDN SQL copy on a production environment or is it strickly for test environments ??
2. If I own a legal copy of SQL 7 with 5 cals, can I legally use SQL MSDE and have more than 5 people access my SQL server or am I also limited to 5 users as my original ??
Sorry I am a newbie at this SQL thing.
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
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
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 View RelatedHey 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
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
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!
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
hi,
Can anyone tell me how to use the extended stored property like sp_addextendedproperty in java code?
Thanks,
Meghna
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.)"
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
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
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 RelatedPlease help to find an example of the code written on VC++ for an extended stored procedure (dll) for SQL Server 2000.
Thanks
I am looking for examples written in VB. Thanks ALOT!!
View 1 Replies View RelatedI 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
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
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!
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.
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
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
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.
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
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
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.
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 6 Replies View Related
Are the extended command always executed even if it is contained within an IF statement???
I created test.sql script as follows:
if '$(CreateA)' = 'Y'
begin
:out $(myDir)testY.log
print 'CreateA is yes'
end
else
begin
:out $(myDir)testN.log
print 'CreateA is no'
end
At the command line prompt enter:
sqlcmd -i test.sql -v CreateA="Y" MyDir="C:Temp"
I expected the file C:Temp estY.log to be created with the line "CreateA is yes", which occurred successfully. However, C:Temp estN.log was created as well, but it was empty.
I deleted the log files and executed the following sqlcmd statement:
sqlcmd -i test.sql -v CreateA="N" MyDir="C:Temp"
The C:Temp estN.log was created with the line "CreateA is no", but C:Temp estY.log was also created.
Is there any way to conditionally execute a sqlcmd extended commands?
Thanks!
N Camp
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.......
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