Useful Extended SPs

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


ADVERTISEMENT

Only Functions And Extended Stored Procedures Can Be Executed From Within A Function. Sp_executesql Is A Extended Stored Prod

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

Extended SP

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

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 View Related

Extended Procedures

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

Help ::: Extended Properties

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

Extended Procedures

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

Extended SP Questions

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

Extended Properties

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

Regarding Extended Property

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

Extended Procs Using C#

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

Extended Stored Procedure

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

Extended Stored Procedures

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

Extended Stored Procedures

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

VC++ Code Example For Extended SP - WANTED

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

Extended Stored Procedure -- VB

Jun 25, 2002

I am looking for examples written in VB. Thanks ALOT!!

View 1 Replies View Related

Extended Stored Procedures - C/C++ Only?

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

Extended Stored Procedure

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

Extended Stored Procedures

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

SQL Server Is Not Releasing Extended SP DLL

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

Registeration Of Extended Dlls

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

Extended Character Search

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

How To Extended Stopred Procedure??

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

How To Use C# DLL In Extended Stored Procedure?

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

Sql 2005 Extended Properties

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

Securing Extended Procedures

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

Access And Extended Properties

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

What Is Extended Store Procedure?

Mar 26, 2008

View 6 Replies View Related

Using Extended SQLCmd Commands With T-SQL

Jun 7, 2006

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

View 3 Replies View Related

Does Anyone Know That Where Have The Most Mssql Extended Procedures For Download?

Aug 30, 2004

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.......

View 2 Replies View Related

Extended Stored Procedure 'xp_cmdshell'

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

Extended Stored Procedure Xp_fixeddrives

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

Executing Extended Stored Procs???

Dec 3, 1999

I've written a very simple extended stored proc--little more than what VC++
6.0's ext stored proc wizard generates--set up the xp in the master database,
and eventually want to execute it from a trigger defined on a table in another database...

I've tested the xp in Query Analyzer. If the DB is set to 'master', the xp
runs (exec xp_InputTrigger), no problem. If I change databases and try to run the xp, I get:

Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'xp_InputTrigger'.

Keying "exec master.xp_InputTrigger", also fails: Could not find stored procedure 'master.xp_InputTrigger'.

I've checked permissions--all seem to be set right. Anyone have any insight
for me here? Many thanks,
mjo

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved