External stored procedure, performance?

Hi everybody,
I should begin to write a DLL library for Sql2000 server.
The functions I like to implement are mathematical functions, like standard
deviation, and similar, nothing really complex. Often I have to use more
then one standard deviation inside the sama function, using subset of a
record set.
Of course I can use sql2000, that implemets standard deviation and basic
mathematical function, so the question is: is it opportune to write a DDL to
improve performance, or is it worse, or just the same?
thanks a lot for any kindly advice
cesareI think the key words here are "using a subset of a recordset". Based on
that I would implement it in T-SQL, or on the application side but not in an
XP. The cost of connecting back to the SQL Server to grab a subset of rows
is going to be big, especially if you perform this std dev calc several
times in a row.
"Cesare" <cvairetti@.mcgestioni.it> wrote in message
news:uO9XdpvjGHA.1640@.TK2MSFTNGP02.phx.gbl...
> Hi everybody,
> I should begin to write a DLL library for Sql2000 server.
> The functions I like to implement are mathematical functions, like
> standard
> deviation, and similar, nothing really complex. Often I have to use more
> then one standard deviation inside the sama function, using subset of a
> record set.
> Of course I can use sql2000, that implemets standard deviation and basic
> mathematical function, so the question is: is it opportune to write a DDL
> to
> improve performance, or is it worse, or just the same?
> thanks a lot for any kindly advice
> cesare
>sql

External Stored Procedure in SQL Server 2005(x64)

I have generated a DLL file in VC++ 2005 by a 'C' file. It works fine when I put in a 32bits machine(32bits Windows Server 2003 + 32 bits SQL Server 2005).

However, when I build it into 64 bits, it doesn't work in a 64 bits machine. I have checked by Dependenct Walker, the DLL generated is linked with KERNEL32.DLL / OPENDS60.DLL / MSVCR80D.DLL, all of these DLL files are on the 64 bits machines and linked correctly.

I used the command

sp_addextendedproc 'abc', 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\abc.dll'

to create a ext. stored procedure. When I run it, the error message shows that

Could not load the DLL C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\abc.dll, or one of the DLLs it references. Reason: 126(error not found).

I would like to ask what is cause of the problem? Do I need to use CLR instead?

Thank you very much!!~

BTW, it is because of your thread I posted the "32Bit Vs 64Bit" SQLCLR platform differences thread. If there are any definite differences this would be a great addition to my upcoming book. Have you experienced any other problems on 64bit platform for sqlclr?

thanks,

derek

external stored procedure (DLL) in Java?

Hi,

I am going to be writing an external stored procedure (my first) for SQL server 2000.

Has anyone out there written a DLL in Java (J++ or .NET) and then accessed the functions within the DLL as an external stored procedure in T/SQL?

I ask the question becaues I'm likely to get it done considerably faster if I write it in Java then VB ;-)

Any advice / suggestions most welcome.

Cheers,

EwanIt shouldn't really make a difference what language you write your dll in. As long as its compiled as a dll, you should be able to call it from a Stored Proc.

Just ensure that the dll is registered on the server that as executing the StoredProc (not the client).

As far as I know , there is no way to return values from the dll into the Stored Proc. Please let me know if there is.

I take no credit for the information below. I copied it from an old posting and saved it, and I cannot remember who posted it originally.

Good luck.

Lionel.

You can do it with the SP_OA* extened stored procedures, located in the
MASTER database (of ms-sql 7.0/2000). Look at this example (for sending
email
through jmail);

CREATE PROCEDURE sp_Send_JMail

@.fromName as char(50),
@.fromEmail as char(50),
@.toName as char(50),
@.toEmail as char(50),
@.subject as char(100),
@.Body as char(500)

AS
DECLARE @.ObjTok int
DECLARE @.RetVal int

EXEC @.RetVal=sp_OACreate'JMail.SMTPMail',@.ObjTok OUT
EXEC sp_OASetProperty @.ObjTok, 'ServerAddress','yourmailserver.com'
EXEC sp_OASetProperty @.ObjTok, 'SenderName', @.fromName
EXEC sp_OASetProperty @.ObjTok, 'Sender', @.fromEmail
EXEC sp_OASetProperty @.ObjTok,'Subject', @.Subject
EXEC @.RetVal = sp_OAMethod @.ObjTok, 'AddRecipient', Null,@.toEmail
EXEC sp_OASetProperty @.ObjTok, 'Body', @.Body
EXEC @.RetVal = sp_OAMethod @.ObjTok, 'Execute'
EXEC sp_OADestroy @.ObjTok GO

Extended Stored Procedures and VB

I have two functions in a .DLL created in VB6 I want to use.
I create two Extended Stored Procedures using:

sp_addextendedproc 'MyFunctionA', 'MyFunctions.dll'
sp_addextendedproc 'MyFunctionB', 'MyFunctions.dll'

When I run:
EXECUTE @.ReturnValue = MyFunctionA @.Paramate1

I get:
"Cannot find the function MyFunctionA in the library C:\Program Files\MyDLLs\MyFunctions.dll. Reason: 127(The specified procedure could not be found.)"

What am I missing?

Hi.

Do you get the same with CREATE ASSEMBLY and CREATE PROC?
CREATE ASSEMBLY ...
CREATE PROC ... AS EXTERNAL NAME ...

I guess I would not use MyFunctionA, not 'MyFunctionA', but I don't know if that matters.|||You cannot use DLLs created by VB as extended stored procedures. There is no way to do it actually using VB. You need a low-level language like C, C++ or Delphi to do it. The extended stored procedures require specific entry points in the dll to work and this cannot be done from VB. Can you implement the VB code using UDF/SPs? Alternatively, you can use the VB program outside of SQL Server by running it from a SQLAgent job.|||

Hi,

I do not know Assembler (MASM, I Guess?), so I would not know. About an hour after you posted, someone answered my query. The answer is: You cannot do it in VB. You must use a lower level language like C, C++, or Delphi. If you want more info, you can view the answer at:

http://forums.microsoft.com/msdn/showpost.aspx?postid=126929&siteid=1

Thanks for your help though.

|||

Hi,

Unfortunately I cannot implement the VB Code using UDFs or SPs. The VB Code I wrote is simply a wrapper around another DLL to simplify the interface.

Unfortunatley, I'm sort of stuck using the VB Function directly inside of the SP. I did some more digging and found C++ has an Extended Stored Procedure Wizard.

I know it sounds a bit clugy, but can I use the Wizard to create a C++ Program, which is a wrapper around the VB Program, which is a wrapper around the DLL?

Since I do not know C++, this might be the easier approach instead of rewriting VB in C++.

Books On-Line for SQL Server (Extended Stored Procedures >> Creating) gives some instructions, but it is confusing. The Example xp_hello may be helpful along with the other examples.

Thanks for your help!

|||The wizard will only create stubs that you have to implement. So you will have to call the VB program or use the DLL directly from C++. But this seems like lot of effort and features like extended stored procedures for example needs to be used with care. You can create a VB OLE automation object and then use it via the OLE automation SPs in SQL Server. Look for sp_OACreate. This may be the solution you are looking for. In any case, running external code from TSQL can have performance issues and it depends on the usage.|||

I have tried this solution and it seems to be very easy to implement: sp_OACreate, sp_OAMethod, and sp_OADestroy. It also seems to be much faster than you indicated.
Thank you very much for your help!!!

Extended Stored Procedures and VB

I have two functions in a .DLL created in VB6 I want to use.
I create two Extended Stored Procedures using:

sp_addextendedproc 'MyFunctionA', 'MyFunctions.dll'
sp_addextendedproc 'MyFunctionB', 'MyFunctions.dll'

When I run:
EXECUTE @.ReturnValue = MyFunctionA @.Paramate1

I get:
"Cannot find the function MyFunctionA in the library C:\Program Files\MyDLLs\MyFunctions.dll. Reason: 127(The specified procedure could not be found.)"

What am I missing?

Hi.

Do you get the same with CREATE ASSEMBLY and CREATE PROC?
CREATE ASSEMBLY ...
CREATE PROC ... AS EXTERNAL NAME ...

I guess I would not use MyFunctionA, not 'MyFunctionA', but I don't know if that matters.|||You cannot use DLLs created by VB as extended stored procedures. There is no way to do it actually using VB. You need a low-level language like C, C++ or Delphi to do it. The extended stored procedures require specific entry points in the dll to work and this cannot be done from VB. Can you implement the VB code using UDF/SPs? Alternatively, you can use the VB program outside of SQL Server by running it from a SQLAgent job.|||

Hi,

I do not know Assembler (MASM, I Guess?), so I would not know. About an hour after you posted, someone answered my query. The answer is: You cannot do it in VB. You must use a lower level language like C, C++, or Delphi. If you want more info, you can view the answer at:

http://forums.microsoft.com/msdn/showpost.aspx?postid=126929&siteid=1

Thanks for your help though.

|||

Hi,

Unfortunately I cannot implement the VB Code using UDFs or SPs. The VB Code I wrote is simply a wrapper around another DLL to simplify the interface.

Unfortunatley, I'm sort of stuck using the VB Function directly inside of the SP. I did some more digging and found C++ has an Extended Stored Procedure Wizard.

I know it sounds a bit clugy, but can I use the Wizard to create a C++ Program, which is a wrapper around the VB Program, which is a wrapper around the DLL?

Since I do not know C++, this might be the easier approach instead of rewriting VB in C++.

Books On-Line for SQL Server (Extended Stored Procedures >> Creating) gives some instructions, but it is confusing. The Example xp_hello may be helpful along with the other examples.

Thanks for your help!

|||The wizard will only create stubs that you have to implement. So you will have to call the VB program or use the DLL directly from C++. But this seems like lot of effort and features like extended stored procedures for example needs to be used with care. You can create a VB OLE automation object and then use it via the OLE automation SPs in SQL Server. Look for sp_OACreate. This may be the solution you are looking for. In any case, running external code from TSQL can have performance issues and it depends on the usage.|||

I have tried this solution and it seems to be very easy to implement: sp_OACreate, sp_OAMethod, and sp_OADestroy. It also seems to be much faster than you indicated.
Thank you very much for your help!!!

Extended Stored Procedures -> loading linked files

Hello everybody

I actually wrote a stored procedure (in xp_wrapper.dll) that is using a dll (original.dll) which uses a license file (no file extension)... clear? :)

Anyway.

All the required files are placed in the BINN dir of the server.

The problem is now, that original.dll can't find it's license file. It seems, that this file was not load by SQL Server.

How can I load this file into SQL Server's heap?

Yours
MikeYou need to checkout the Win32 level LoadLibrary() call to explicitly load the DLL. You will need to explicitly FreeLibrary the DLL before you exit from the xp call.

Extended Stored Procedures

The database I am setting up requires a dll file to be added to extended
stored prcoedures - "Using a Microsoft tool such as Enterprise Manager, add
the file to the extended stored prcoedures already installed on SQL Server."
Where/how is this done?
Thank you.
Regards,
DianeYou can run a query like the one below from Query Analyzer. See the Books
Online for more information.
EXEC sp_addextendedproc 'MyFunction', 'MyApp.dll'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Diane" <Diane@.discussions.microsoft.com> wrote in message
news:508ECDA1-08D8-4593-BAF8-628108DDFF0B@.microsoft.com...
> The database I am setting up requires a dll file to be added to extended
> stored prcoedures - "Using a Microsoft tool such as Enterprise Manager,
> add
> the file to the extended stored prcoedures already installed on SQL
> Server."
> Where/how is this done?
> Thank you.
> Regards,
> Diane|||Diane wrote:
> The database I am setting up requires a dll file to be added to
> extended stored prcoedures - "Using a Microsoft tool such as
> Enterprise Manager, add the file to the extended stored prcoedures
> already installed on SQL Server."
> Where/how is this done?
> Thank you.
> Regards,
> Diane
See "sp_addextendedproc" and "Creating Extended Stored Procedures" in
BOL.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com

Extended Stored Procedures

The database I am setting up requires a dll file to be added to extended
stored prcoedures - "Using a Microsoft tool such as Enterprise Manager, add
the file to the extended stored prcoedures already installed on SQL Server."
Where/how is this done?
Thank you.
Regards,
DianeYou can run a query like the one below from Query Analyzer. See the Books
Online for more information.
EXEC sp_addextendedproc 'MyFunction', 'MyApp.dll'
Hope this helps.
Dan Guzman
SQL Server MVP
"Diane" <Diane@.discussions.microsoft.com> wrote in message
news:508ECDA1-08D8-4593-BAF8-628108DDFF0B@.microsoft.com...
> The database I am setting up requires a dll file to be added to extended
> stored prcoedures - "Using a Microsoft tool such as Enterprise Manager,
> add
> the file to the extended stored prcoedures already installed on SQL
> Server."
> Where/how is this done?
> Thank you.
> Regards,
> Diane|||Diane wrote:
> The database I am setting up requires a dll file to be added to
> extended stored prcoedures - "Using a Microsoft tool such as
> Enterprise Manager, add the file to the extended stored prcoedures
> already installed on SQL Server."
> Where/how is this done?
> Thank you.
> Regards,
> Diane
See "sp_addextendedproc" and "Creating Extended Stored Procedures" in
BOL.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Extended Stored Procedures

The database I am setting up requires a dll file to be added to extended
stored prcoedures - "Using a Microsoft tool such as Enterprise Manager, add
the file to the extended stored prcoedures already installed on SQL Server."
Where/how is this done?
Thank you.
Regards,
Diane
You can run a query like the one below from Query Analyzer. See the Books
Online for more information.
EXEC sp_addextendedproc 'MyFunction', 'MyApp.dll'
Hope this helps.
Dan Guzman
SQL Server MVP
"Diane" <Diane@.discussions.microsoft.com> wrote in message
news:508ECDA1-08D8-4593-BAF8-628108DDFF0B@.microsoft.com...
> The database I am setting up requires a dll file to be added to extended
> stored prcoedures - "Using a Microsoft tool such as Enterprise Manager,
> add
> the file to the extended stored prcoedures already installed on SQL
> Server."
> Where/how is this done?
> Thank you.
> Regards,
> Diane
|||Diane wrote:
> The database I am setting up requires a dll file to be added to
> extended stored prcoedures - "Using a Microsoft tool such as
> Enterprise Manager, add the file to the extended stored prcoedures
> already installed on SQL Server."
> Where/how is this done?
> Thank you.
> Regards,
> Diane
See "sp_addextendedproc" and "Creating Extended Stored Procedures" in
BOL.
David Gugick
Quest Software
www.imceda.com
www.quest.com
sql

Extended Stored Procedure: Get the current db of the client

Hi
Is there a way to get the current database of the client who calls my
Extended Stored Procedure?
I have written a DLL in Visual Studion 2005 for the SQL server 2003 in C/C++
using the functions srv_*.
Thanks.
HansWhat version of SQL Server?
It's a limitation of extended stored procedure programming
with SQL Server 2000. Some have tried using svr_rpcdb but it
will generally just return master as the database name. And
it's no longer supported.
-Sue
On Mon, 8 May 2006 14:41:59 +0200, "Hans Stoessel"
<hstoessel.list@.pm-medici.ch> wrote:

>Hi
>Is there a way to get the current database of the client who calls my
>Extended Stored Procedure?
>I have written a DLL in Visual Studion 2005 for the SQL server 2003 in C/C+
+
>using the functions srv_*.
>Thanks.
>Hans
>|||This never worked correctly, this is not way you can get the database
context from within an XP, easiest work around is to use a wrapper SP that
passes the db_name() or db_id() as a parameter.
In general using wrapper SP's is a good practice for doing parameter
validation, and meta data exposure since XP's do not emit the parameter
signatures.
GertD@.SQLDev.Net
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:feuv521gpm893g6hfi4aja0vpp8vguiun4@.
4ax.com...
> What version of SQL Server?
> It's a limitation of extended stored procedure programming
> with SQL Server 2000. Some have tried using svr_rpcdb but it
> will generally just return master as the database name. And
> it's no longer supported.
> -Sue
> On Mon, 8 May 2006 14:41:59 +0200, "Hans Stoessel"
> <hstoessel.list@.pm-medici.ch> wrote:
>
>|||Works for SP's, might work with XP's as well:
1. Prefix the name with "sp_"
2. Mark it as a system object with sp_MS_MarkSystemObject
This causes the SP to run under the context of the database it was called
from, not the master database where it resides. At the least, you can put
an SP wrapper in the master DB for the XP, and pass in the db_name() as a
parameter to the XP and it will have the correct database context (not
"master").
"Gert E.R. Drapers" <GertD@.SQLDev@.Net> wrote in message
news:%23ZtUJnzcGHA.3632@.TK2MSFTNGP05.phx.gbl...
> This never worked correctly, this is not way you can get the database
> context from within an XP, easiest work around is to use a wrapper SP that
> passes the db_name() or db_id() as a parameter.
> In general using wrapper SP's is a good practice for doing parameter
> validation, and meta data exposure since XP's do not emit the parameter
> signatures.
> GertD@.SQLDev.Net
>
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:feuv521gpm893g6hfi4aja0vpp8vguiun4@.
4ax.com...
>|||Does not matter, an XP does not have a call to retrieve the database
context.
GertD@.SQLDev.Net
"Mike C#" <xxx@.yyy.com> wrote in message news:wzR8g.505$Ut2.124@.fe09.lga...
> Works for SP's, might work with XP's as well:
> 1. Prefix the name with "sp_"
> 2. Mark it as a system object with sp_MS_MarkSystemObject
> This causes the SP to run under the context of the database it was called
> from, not the master database where it resides. At the least, you can put
> an SP wrapper in the master DB for the XP, and pass in the db_name() as a
> parameter to the XP and it will have the correct database context (not
> "master").
>
> "Gert E.R. Drapers" <GertD@.SQLDev@.Net> wrote in message
> news:%23ZtUJnzcGHA.3632@.TK2MSFTNGP05.phx.gbl...
>|||DBLIB, dbname() function.
http://msdn.microsoft.com/library/d...br />
2gtz.asp
enjoy
"Gert E.R. Drapers" <GertD@.SQLDev@.Net> wrote in message
news:u8i0W6WdGHA.4720@.TK2MSFTNGP03.phx.gbl...
> Does not matter, an XP does not have a call to retrieve the database
> context.
> GertD@.SQLDev.Net
> "Mike C#" <xxx@.yyy.com> wrote in message
> news:wzR8g.505$Ut2.124@.fe09.lga...
>|||No, because then you need to connect first! So what database do you
establish your connection to?
Please don't answer try to answer questions you do not know the answer to.
-GertD
"Mike C#" <xxx@.yyy.com> wrote in message news:Ga99g.60$Id.19@.fe10.lga...
> DBLIB, dbname() function.
> http://msdn.microsoft.com/library/d... />
z_2gtz.asp
> enjoy
> "Gert E.R. Drapers" <GertD@.SQLDev@.Net> wrote in message
> news:u8i0W6WdGHA.4720@.TK2MSFTNGP03.phx.gbl...
>|||And you plan to what? Put the same "wrapper" stored procedure in every
single database on a server?
Don't be a dick Gertrude.
"Gert E.R. Drapers" <GertD@.SQLDev@.Net> wrote in message
news:Ou6TvyjdGHA.3632@.TK2MSFTNGP05.phx.gbl...
> No, because then you need to connect first! So what database do you
> establish your connection to?
> Please don't answer try to answer questions you do not know the answer to.
> -GertD
> "Mike C#" <xxx@.yyy.com> wrote in message news:Ga99g.60$Id.19@.fe10.lga...
>|||underprocessable|||"Gert E.R. Drapers" wrote:

> No, you are incorrect; for an extended stored procedure you have to pass i
n
> the database context as a parameter if you need it, that is the only thing
> that works. Did you ever write an extended stored procedure?
I have written several, several, several extended stored procedures. In
fact, I just publicly released about 3 dozen that cover everything from AES,
Blowfish, Twofish, DES and TripleDES encryption to regular expressions to
recursively reading a local subdirectory listing.
In fact, here's a little experiment for you extended procedure maestro: Put
this regular stored procedure in the Master database:
CREATE PROCEDURE dbo.Test1
AS
SELECT db_Name()
GO
Now run it from within the Model database. Or the Northwind database. What
database name comes up? Master, that's what. According to your solution,
you need to recreate this exact same stored procedure in every single
database you own in order to get the current database context out of it.
As I said: changing the name to "sp_..." and marking it as a system object
will allow you to use JUST ONE copy of the stored procedure in Master. It
will run in the context of the CURRENT DATABASE, no matter what database you
invoke it from. But I'm sure you're well aware of that.

> Besides that it does not make sense to call the DB-Lib function dbname()
> untill you established a loopback connection over DB-Library, which would
> default to the default database for the user which is not the same as the
> database context. See the attached example which shows this behavior.
And that's all well and good. I was simply pointing out some things that
might be tried, and you pointed out that it wouldn't work in your own little
snide way.

> The srv_rpc* class methods in the OPENDS60.LIB file are obsolete since the
y
> are gateway calls and not longer supported; srv_rpcdb() only gave you a
> database context when you where a remote procedure, which is something
> different than an extended stored procedure, so that is not giving you wan
t
> you want either.
I know srv_rpcdb doesn't work, and didn't suggest it as a solution. I'm
sure whoever didn't know that will be happy to hear it from you, however.

> So Mike C#, the ONLY solution is to pass it in as a parameter!
Which is fine, and perfectly acceptable. The difference is simply this, if
you refer back to my original post: Your method requires the same stored
procedure be copied to all 28 of my databases. Alternatively I can put a
single copy in the Master database and be done with it.

> BTW: Next time you are calling somebody names you might want to check your
> facts before replying an making a fool out of yourself.
BTW: You should check your facts before you accuse someone of not having
any experience in your little domain over there before making a fool of
yourself.
http://www.sqlservercentral.com/col...oolkitpart1.asp
http://www.sqlservercentral.com/col...oolkitpart2.asp
http://www.sqlservercentral.com/col...oolkitpart3.asp
http://www.sqlservercentral.com/col...oolkitpart4.asp
Of course I'd love an opportunity to learn at the master's feet. So where
does Master Gert keep his extended procedures, that I may immerse myself in
the knowledge to be gained?sql

Extended stored procedure, performance?

Hi everybody,
I should begin to write a DLL library for Sql2000 server.
The functions I like to implement are mathematical functions, like standard
deviation, and similar, nothing really complex. Often I have to use more
then one standard deviation inside the sama function, using subset of a
record set.
Of course I can use sql2000, that implemets standard deviation and basic
mathematical function, so the question is: is it opportune to write a DDL to
improve performance, or is it worse, or just the same?
thanks a lot for any kindly advice
cesare"Cesare" <cvairetti@.mcgestioni.it> wrote in message
news:et2MIqvjGHA.4304@.TK2MSFTNGP03.phx.gbl...
> Hi everybody,
> I should begin to write a DLL library for Sql2000 server.
> The functions I like to implement are mathematical functions, like
> standard
> deviation, and similar, nothing really complex. Often I have to use more
> then one standard deviation inside the sama function, using subset of a
> record set.
> Of course I can use sql2000, that implemets standard deviation and basic
> mathematical function, so the question is: is it opportune to write a DDL
> to
> improve performance, or is it worse, or just the same?
>
Extended stored procedures are so dangerous to the stability of the database
server that they should be used very, very carefully.
In SQL 2005 CLR integration provides a safe way to extent the SQL engine
with custom calculations.
David

extended stored procedure question

Hi,
Is there possible to write extended stored procedure (i.e. Delphi DLL) which
do insert (or other DML operation)? I saw some samples - but I didnt find
such kind.
Thanks and nice dayYes, it is possible to write extended stored procedures with Visual C++. SQ
L
Books Online provides information in the article "Creating Extended Stored
Procedures"
"Petez" wrote:

> Hi,
> Is there possible to write extended stored procedure (i.e. Delphi DLL) whi
ch
> do insert (or other DML operation)? I saw some samples - but I didnt find
> such kind.
> Thanks and nice day
>

extended stored procedure question

Hi,
Is there possible to write extended stored procedure (i.e. Delphi DLL) which
do insert (or other DML operation)? I saw some samples - but I didnt find
such kind.
Thanks and nice dayonly in C
"PeterZ" wrote:

> Hi,
> Is there possible to write extended stored procedure (i.e. Delphi DLL) whi
ch
> do insert (or other DML operation)? I saw some samples - but I didnt find
> such kind.
> Thanks and nice day
>|||Yes, see http://www.berenddeboer.net/article/1293/1293.html
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"PeterZ" <PeterZ@.discussions.microsoft.com> wrote in message
news:B59D6BDC-B73B-45B9-97B0-DD701953B3D8@.microsoft.com...
> Hi,
> Is there possible to write extended stored procedure (i.e. Delphi DLL)
> which
> do insert (or other DML operation)? I saw some samples - but I didnt find
> such kind.
> Thanks and nice day
>|||Finally, it is nice to see you here :)
Salesql

Extended Stored Procedure - ODBC Loopback Connection Problem

Hi,
I have a loopback connection using ODBC in the DLL initialization code of
the SQL Server ESP Module (SQL Server 2000). The loopback connection works
fine when the DSN is specifed with the "NT Authentication", however the same
fails when specified with the "SQL Server user authentication". I have tried
using both the SQLConnect and SQLDriverConnect calls, butu none of them
works. Also the same code works fine on SQL Server 2005. Is this a known
problem with some fix, or am I doing something wrong here'
The code is as given below,
// ESPODBCLoopback.cpp : Defines the entry point for the DLL application.
//
#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 Kumar
Arizcon Corporation ( http://www.arizcon.com )I dont know if this will make any difference, but maybe you can try adding
";trusted_connection=no"
- Neil
"Anil Saharan" <aks@.discussions.microsoft.com> wrote in message
Hi,
I have a loopback connection using ODBC in the DLL initialization code of
the SQL Server ESP Module (SQL Server 2000). The loopback connection works
fine when the DSN is specifed with the "NT Authentication", however the same
fails when specified with the "SQL Server user authentication". I have tried
using both the SQLConnect and SQLDriverConnect calls, butu none of them
works. Also the same code works fine on SQL Server 2005. Is this a known
problem with some fix, or am I doing something wrong here'
The code is as given below,
// ESPODBCLoopback.cpp : Defines the entry point for the DLL application.
//
#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 Kumar
Arizcon Corporation ( http://www.arizcon.com )|||Nope, It does not solve the problem

Extended Stored Procedure - ODBC Loopback Connection Problem

Hi,

I have a loopback connection using ODBC in the DLL initialization code
of
the SQL Server ESP Module (SQL Server 2000). The loopback connection
works
fine when the DSN is specifed with the "NT Authentication", however the
same
fails when specified with the "SQL Server user authentication". I have
tried
using both the SQLConnect and SQLDriverConnect calls, butu none of them

works. Also the same code works fine on SQL Server 2005. Is this a
known
problem with some fix, or am I doing something wrong here??

The code is as given below,
// ESPODBCLoopback.cpp : Defines the entry point for the DLL
application.
//

#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 Kumar
Arizcon Corporation ( http://www.arizcon.com )Anil Kumar Saharan (aksaharan@.gmail.com) writes:
> I have a loopback connection using ODBC in the DLL initialization code
> of the SQL Server ESP Module (SQL Server 2000). The loopback connection
> works fine when the DSN is specifed with the "NT Authentication",
> however the same fails when specified with the "SQL Server user
> authentication". I have tried using both the SQLConnect and
> SQLDriverConnect calls, butu none of them works.

I don't really see why you would use SQL authentication for a loopback -
Windows authentication appears to be the best choice.

Then again, we have an SP that loops back, and in 6.5 days it used
SQL authentication, since we could not rely on Window authentication
being on.

Also, a DSN for a loopback seems to be an overkill. (Then again I have
always found DSN to be an overkill in all situations. Never understood
them.)

Anyway, there is something funny in your code:

> /*------ 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))
> {

The call to GetWindow appears to be incomplete, and the above looks
like a syntax error to me. In any case, calling GetWindow in a
extended stored procedure, appears to be a really bad thing to do.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Soory for the mistake there in the code... I was trying out something
and just messed up before pasting it here... Kindly read the valid Part
to be the "SQLConnect" statement for the connection..

It works fine with Windows authentication but fails for the SQL Server
Authentication method.

Knowing that DSN is bad, ODBC is bad.. is good, Windows authentication
has its own known pitfalls, and so does SQL authentication, but that
does not solve the problem. Is there a reason for that code to fail for
SQL Server Authentication (with SQLConnect) ??

Anil
~~~~~~~~~~~~~
Work: http://www.arizcon.com/

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Anil Kumar (aksaharan@.yahoo.com) writes:
> Soory for the mistake there in the code... I was trying out something
> and just messed up before pasting it here... Kindly read the valid Part
> to be the "SQLConnect" statement for the connection..
> It works fine with Windows authentication but fails for the SQL Server
> Authentication method.
> Knowing that DSN is bad, ODBC is bad.. is good, Windows authentication
> has its own known pitfalls, and so does SQL authentication, but that
> does not solve the problem. Is there a reason for that code to fail for
> SQL Server Authentication (with SQLConnect) ??

I have to admit that I don't have much expierence of ODBC programming,
so I cannot answer questions about SQLConnect.

But two questions:
1) Can you get an error message from ODBC, telling you why the login
failed?
2) Stupid check: SQL authentication is enabled on the server?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> But two questions:
> 1) Can you get an error message from ODBC, telling you
> why the login failed?
The error is :
Failed to connect to database : [0 : HYT00 : [Microsoft][ODBC SQL Server
Driver]Timeout expired]

This is the return error for SQLConnect failure, I'm not sure whether it
is getting in some kind of deadlock or something similar due to the
loopback ..

> 2) Stupid check: SQL authentication is enabled on the
> server?
SQL Authentication is enabled, and the same DSN works fine with SQL
authentication when used from outside to connect.

Anil Kumar
~~~~~~~~~~~~
http://www.arizcon.com/

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Anil Kumar (aksaharan@.yahoo.com) writes:
>> But two questions:
>> 1) Can you get an error message from ODBC, telling you
>> why the login failed?
> The error is :
> Failed to connect to database : [0 : HYT00 : [Microsoft][ODBC SQL Server
> Driver]Timeout expired]
> This is the return error for SQLConnect failure, I'm not sure whether it
> is getting in some kind of deadlock or something similar due to the
> loopback ..

Mysterious. The most likely reason for a timeout while connecting is
that the server does not exist. But what I can see in the MDAC Books
Online, you ought to get HYT01 in that case. Unless you first started
transaction, acquired locks on the sysxlogins table, and the called your
XP, I can't see how you could block yourself. (And I don't think you
can get locks on sysxlogins easily.)

I still sort of suspect that you are connecting to the wrong server.
I don't really what is that DSN (which must be a System DSN by the way),
and if I were do to this myself, I would pass @.@.servername to the
XP. In fact, as long as you are not using a named instance, there's
no need to specify server at all.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>SQL Authentication is enabled, and the same DSN works fine with SQL
>authentication when used from outside to connect.

maybe i dont't understand what is it all about
but i would play with client network utility on the server machine
namely to check if there is an alias defined (with the same name as it
appears in DSN) on server machine ?
(and playing with protocols)

Extended Stored Procedure

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

Theoffficial position from Microsoft is that they do not support the use of the CLR for extended stored procedures
At one point I had considered trying to use regular expressions withSQL Server 2000. Here'a a CodeProject article explaining how togo about it:xp_regex:Regular Expressions in SQL Server 2000. This also covers the topic of running managed code from an extendedstored procedure. Maybe you will find something in there to helpyou.
|||

Yes I was slowly finding this out through my research last night. Thanks for the validation, and I will check out your code.

I did find someone who did it anyway by wrapping a COM object:
http://sqljunkies.com/Article/C5A500EB-B8BE-42C0-B23B-258A342CAAAB.scuk

Who knows what kind of problems that could cause.

My solution is apparently around the bend in SQL Server and Visual Studio 2005 though. SQL 2005 natively supporting CLR.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sqlclrguidance.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnreal/html/realworld03112005.asp

Thanks for the feedback, if anyone has comments about the above please don't hold back.

Brian

|||Just to be clear, nothing I provided is my own code, just what I found when I researched the same subject.
And indeed, SQL 2005 will be your answer!
|||

I have seen Jeff Prosise live use them in a Demo at our .NET user group but Jeff Prosise is the person to call if you want to know MFC(Microsoft Foundation Class). I would swing by BMC website to test drive their SQL Programmer tool because anything you can do in SQL Server their tool will do it. They bought the best Transact-SQL minds in 1999. I would also browse Ken Henderson books at my local book store, there are three of them on Transact-SQL because he covers undocumented DBCCs and functions that may help you. Hope this helps.

http://www.wintellect.com

http://www.bmc.com/products/proddocview/0,,0_0_0_8739,00.html

Kind regards,

Gift Peddie

extended stored procedure

Hi
I wrote a Delphi program to create a dll.after that, I added an extended
procedure to sql server using this dll. unfortunately this extended procedur
e
is too slow ( for excution on a table with 100000 rows, it takes 1 minute to
finish the extended procedure) . I recognized that the lack of speed arises
from communication between sql and delphi. writing this procedure by sql led
to worse performance.
what should I do to speed up the extended procedure?
Thanks."Tajik" <Tajik@.discussions.microsoft.com> wrote in message
news:1B4C8834-16CA-4B43-B691-9F385F3C437C@.microsoft.com...
> Hi
> I wrote a Delphi program to create a dll.after that, I added an extended
> procedure to sql server using this dll. unfortunately this extended
> procedure
> is too slow ( for excution on a table with 100000 rows, it takes 1 minute
> to
> finish the extended procedure) . I recognized that the lack of speed
> arises
> from communication between sql and delphi. writing this procedure by sql
> led
> to worse performance.
> what should I do to speed up the extended procedure?
> Thanks.
>
What does the extended proc do? I'm guessing you are calling the proc or
some other code once for each row. If I'm right then I don't think it will
scale well. Try to implement the same logic as set-based code using TSQL,
then call the TSQL proc from your Delphi code.
David Portas
SQL Server MVP
--

Extended properties created by Visual Studio on CLR UDF''s

When one deploys a CLR DLL that contains [SqlFunction]'s, Visual Studio creates three extended properties on the SQL function that is created: these are,

AutoDeployed, SqlAssemblyFile, SqlAssemblyFileLine.

What I haven't found anywhere on Help or MSDN online is what these extended properties mean.

Could someone enlighten me?

1. What is a description of the above properties?

2. What is the benefit of using them (clearly the function works without using them). Does it help debugging?

3. Does the developer need to manually keep for instance the SqlAssemblyFileLine in sync with changes in the .NET code?

Thanks a lot.

Aneela_B wrote:

When one deploys a CLR DLL that contains [SqlFunction]'s, Visual Studio creates three extended properties on the SQL function that is created: these are,

AutoDeployed, SqlAssemblyFile, SqlAssemblyFileLine.

What I haven't found anywhere on Help or MSDN online is what these extended properties mean.

Could someone enlighten me?

1. What is a description of the above properties?

2. What is the benefit of using them (clearly the function works without using them). Does it help debugging?

3. Does the developer need to manually keep for instance the SqlAssemblyFileLine in sync with changes in the .NET code?

AFAIK, the above properties do not have any use at the moment. They have no impact on debugging.

If you change the .NET code you have to re-deploy your assembly (either CREATE or ALTER).

Niels

Extended properties created by Visual Studio on CLR UDF''s

When one deploys a CLR DLL that contains [SqlFunction]'s, Visual Studio creates three extended properties on the SQL function that is created: these are,

AutoDeployed, SqlAssemblyFile, SqlAssemblyFileLine.

What I haven't found anywhere on Help or MSDN online is what these extended properties mean.

Could someone enlighten me?

1. What is a description of the above properties?

2. What is the benefit of using them (clearly the function works without using them). Does it help debugging?

3. Does the developer need to manually keep for instance the SqlAssemblyFileLine in sync with changes in the .NET code?

Thanks a lot.

Aneela_B wrote:

When one deploys a CLR DLL that contains [SqlFunction]'s, Visual Studio creates three extended properties on the SQL function that is created: these are,

AutoDeployed, SqlAssemblyFile, SqlAssemblyFileLine.

What I haven't found anywhere on Help or MSDN online is what these extended properties mean.

Could someone enlighten me?

1. What is a description of the above properties?

2. What is the benefit of using them (clearly the function works without using them). Does it help debugging?

3. Does the developer need to manually keep for instance the SqlAssemblyFileLine in sync with changes in the .NET code?

AFAIK, the above properties do not have any use at the moment. They have no impact on debugging.

If you change the .NET code you have to re-deploy your assembly (either CREATE or ALTER).

Niels

Extended Proc

Hi,
I have compiled a dll in delphi but I cannot use it as extended procedure(it
always returns null). Here is my source and usage in sql server:
--
library testdll;
uses
SysUtils,
Classes;
{$R *.res}
function xp_a:string;
begin
{for test}
result:='10';
end;
exports xp_a;
end.
________________________________________
_
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
exec sp_addextendedproc N'xp_a', N'testdll.dll'
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
________________________________________
_
declare @.a varchar(10)
exec master..xp_a @.a output
select @.a as output
--
Any help would be greatly appreciated.
LeilaYou can not return a string, you need to return it as an output parameter
instead, an XP always and only returns an integer.
See for examples and helper libraries:
http://www.howtodothings.com/viewar...spx?article=223
http://mastercluster.com/xproc.html
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Leila" <Leilas@.hotpop.com> wrote in message
news:OSr6YI6JFHA.2796@.tk2msftngp13.phx.gbl...
> Hi,
> I have compiled a dll in delphi but I cannot use it as extended
> procedure(it
> always returns null). Here is my source and usage in sql server:
> --
> library testdll;
> uses
> SysUtils,
> Classes;
> {$R *.res}
> function xp_a:string;
> begin
> {for test}
> result:='10';
> end;
> exports xp_a;
> end.
> ________________________________________
_
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS OFF
> GO
> exec sp_addextendedproc N'xp_a', N'testdll.dll'
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> ________________________________________
_
> declare @.a varchar(10)
> exec master..xp_a @.a output
> select @.a as output
> --
> Any help would be greatly appreciated.
> Leila
>
>