External stored procedures and sockets

I hope this is the right group for this question.
I am trying to deliver a UDP multicast data stream as a notification method
using an extended stored procedure. The multicast message, however, is not
making it out on the network. (At least not in a form that I can recognize.
)
- I can make the multicast work from a stand-alone console application using
the same procedure with dummy data, so I think I'm setting up the multicast
properly.
- I have verified that the extended stored procedure receives the parameters
from the trigger that calls it successfully, so the external stored procedur
e
is being called properly.
- I have verified that there are no error returns from any of the socket (or
other) API calls in the extended stored procedure, so the procedure thinks
it's doing what it ought to.
My conclusion is that there is something in the execution environment that
is keeping the extended stored procedure from working as expected (i.e., as
it does as a simple subroutine in the console application). On the other
hand, I know I've seen people write about using sockets in their extended
stored procedures.
Any ideas as to what I'm doing wrong?
Thanks.
Ed Hoch
--
GEDDS Manager
Geophysical Institute
University of Alaska FairbanksHi
Have you checked that there is not a firewall blocking this port? Also make
sure that your account has the correct access to do this.
John
"Edward Hoch" wrote:

> I hope this is the right group for this question.
> I am trying to deliver a UDP multicast data stream as a notification metho
d
> using an extended stored procedure. The multicast message, however, is no
t
> making it out on the network. (At least not in a form that I can recogniz
e.)
> - I can make the multicast work from a stand-alone console application usi
ng
> the same procedure with dummy data, so I think I'm setting up the multicas
t
> properly.
> - I have verified that the extended stored procedure receives the paramete
rs
> from the trigger that calls it successfully, so the external stored proced
ure
> is being called properly.
> - I have verified that there are no error returns from any of the socket (
or
> other) API calls in the extended stored procedure, so the procedure thinks
> it's doing what it ought to.
> My conclusion is that there is something in the execution environment that
> is keeping the extended stored procedure from working as expected (i.e., a
s
> it does as a simple subroutine in the console application). On the other
> hand, I know I've seen people write about using sockets in their extended
> stored procedures.
> Any ideas as to what I'm doing wrong?
> Thanks.
> Ed Hoch
> --
> GEDDS Manager
> Geophysical Institute
> University of Alaska Fairbanks|||Hi John,
I am assuming that since I can run the same code successfully from a console
application, that it is not a firewall issue. I don't think it's possible t
o
"firewall" a specific process (like SQL Server), is it? Also, I have logged
in to the console as the user that SQL Server runs under. The console
application still works. So, unless SQL Server is somehow managinig to
affect the permissions of that account, I don't think it's an access
problem, either.
Feel free to correct me if any of my assumptions are wrong. Thanks very
much for your response.
Ed
"John Bell" wrote:
> Hi
> Have you checked that there is not a firewall blocking this port? Also mak
e
> sure that your account has the correct access to do this.
> John
> "Edward Hoch" wrote:
>

External columns from Sybase stored procedures

I need to execute several stored procedures on a Sybase server and copy the results to SQL Server 2005 tables. While using an ad-hoc sql statement the "Available External Columns" list is correct, however when using a stored procedure the list is empty. I've tried to work around this a couple of ways without success.

1) DelayedValidation.
I ran the sql from the stored procedure body in the OLE DB Source to set the column list, then turned on DelayValidation for the Data Flow component. When I switch to using a stored procedure it still connects to the Sybase database and removes the column list. Still does this even after turning on DelayedValidation for the sequence container and entire package (the OLE DB source itself does not have the option).

2) Using a variable that changes during runtime.
I copied the sql from the Sybase procedure into the default value of a variable. A script changes it to a procedure at runtime. This provides a column list in design mode but throws the error "The external metadata column collection is out of synchronization with the data source columns. The external metadata column xxxx needs to be removed from the external metadata column collection" and repeats for every column in the list. I know that the column names and data types are identical.

3) Manually updated the external/output columns list.
Was very painful and gave me the same errors.

It seems that DelayedValidation is the route I'm supposed to take, but I don't see how it would be any different during runtime.

Any ideas would be greatly appreciated!

I since learned that the OLE DB data source also has a DelayValidation property, but I still see the same errors with this enabled.
|||Well I think I answered my own question. After switching the connection from the "Native\Sybase" type to ".Net Providers\Sybase" and changing the OLE DB connection to a DataReader I found a new option on the DataFlow property that allowed me to change the sql through an expression at runtime. I may have also needed to turn off the ValidateExternalMetadata property too, not sure yet.

External columns from a Sybase stored procedures

I need to execute several stored procedures on a Sybase server and copy the results to SQL Server 2005 tables. While using an ad-hoc sql statement the "Available External Columns" list is correct, however when using a stored procedure the list is empty. I've tried to work around this a couple of ways without success.

1) DelayedValidation.
I ran the sql from the stored procedure body in the OLE DB Source to set the column list, then turned on DelayValidation for the Data Flow component. When I switch to using a stored procedure it still connects to the Sybase database and removes the column list. Still does this even after turning on DelayedValidation for the sequence container and entire package (the OLE DB source itself does not have the option).

2) Using a variable that changes during runtime.
I copied the sql from the Sybase procedure into the default value of a variable. A script changes it to a procedure at runtime. This provides a column list in design mode but throws the error "The external metadata column collection is out of synchronization with the data source columns. The external metadata column xxxx needs to be removed from the external metadata column collection" and repeats for every column in the list. I know that the column names and data types are identical.

3) Manually updated the external/output columns list.
Was very painful and gave me the same errors.

It seems that DelayedValidation is the route I'm supposed to take, but I don't see how it would be any different during runtime.

Any ideas would be greatly appreciated!

I since learned that the OLE DB data source also has a DelayValidation property, but I still see the same errors with this enabled.
|||Well I think I answered my own question. After switching the connection from the "Native\Sybase" type to ".Net Providers\Sybase" and changing the OLE DB connection to a DataReader I found a new option on the DataFlow property that allowed me to change the sql through an expression at runtime. I may have also needed to turn off the ValidateExternalMetadata property too, not sure yet.

extending function: view dependencies

So I have a number of separate databases on my SQL 2005 Server.

I also have a number of Reports in SSRS.

Many of the stored procedures in the various databases reference tables, functions, and stored procedures in other databases on the same server.

How can I accomplish the effect of right clicking on a stored procedure for instance, clicking view dependencies, and having everything show up in that list, not just the items in that DB?

It is all stored in the database in one form or another, but I must be missing some crucial piece to integrating it all together.

Note: I did not design this system, just maintaining and modifying existing items. There are no schemas to speak of. SSRS will most likely move to a dedicated server at one point with other data warehousing functions, so the ability to span servers would be useful.

Thanks!

Matt

Mhmm, I think this is not possible unless you parse the procedue on your own. There are no entries for dependencies in the system tables created for non-db-local objects. Additionally you can′t use schemabinding for maintainance as schemabinding applies only to two part names.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

|||Are there any third party tools that would combine all this information into an enterprise version of "view dependencies"?

thanks|||

Hi,

Jens is correct. There is a lot of confusion about "sysdepends", so we've added a new section explaining SQL dependencies in a web refresh section of the BOL. Please read:

SQL Server 2005 Books Online

Understanding SQL Dependencies

New: 5 December 2005
http://msdn2.microsoft.com/en-us/library/ms345449.aspx

Re: " any third party tools" - A quick MSN search turned up

http://www.red-gate.com/products/sql_dependency_tracker/index.htm

which I have never used.

Regards

extended stored procedures: is there a 256 character limit on INPUT parameters?

Hello,
On SQL Server 2000 (SP3), for extended stored procedures: is there a
256 character limit on INPUT parameters?
And if so, is there a way around that?
Thanks,
BertAFAIK, there is no such limit.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Bert Szoghy" <webmaster@.quadmore.com> schrieb im Newsbeitrag
news:34276ef9.0504241118.4596c291@.posting.google.com...
> Hello,
> On SQL Server 2000 (SP3), for extended stored procedures: is there a
> 256 character limit on INPUT parameters?
> And if so, is there a way around that?
> Thanks,
> Bert|||Here's a quick test script for you:
CREATE PROCEDURE dbo.usp_Char256Test
@.s VARCHAR(257)
AS
PRINT LEN(@.s)
GO
DECLARE @.TempString VARCHAR(257)
SET @.TempString = REPLICATE('X', 257)
PRINT LEN(@.TempString)
EXEC dbo.usp_Char256Test @.TempString
This creates an SP that allows a 257 char VARCHAR to be passed in. It then
prints the Length of the passed in string to verify that all 257 chars were
passed successfully.
The limit you're encountering is probably due to a CHAR(256) or VARCHAR(256)
limit placed on the definition of the parameter in the SP declaration (above
I set the limit to 257, but any value up to 8000 for VARCHAR [4,000 for
NVARCHAR] should work).
"Bert Szoghy" <webmaster@.quadmore.com> wrote in message
news:34276ef9.0504241118.4596c291@.posting.google.com...
> Hello,
> On SQL Server 2000 (SP3), for extended stored procedures: is there a
> 256 character limit on INPUT parameters?
> And if so, is there a way around that?
> Thanks,
> Bert|||Steve Kass <skass@.drew.edu> wrote in message news:<OJxPTgSSFHA.3716@.TK2MSFTNGP14.phx.gbl>..
.
> Bert,
> For some system extended stored procedures, the parameters
> are defined as varchar(256). For those, there is no way around
> unless you rewrite the xp (not a good idea I think). For xp's you
> write yourself, I'm not aware of any limit. Are you having a
> particular problem you need help with?
> Steve Kass
> Drew University
> Bert Szoghy wrote:
>
Hello,
Thank you for your responses.
I have an extended stored proc DLL with C++ code which just looks fine
to me, which it refuses to give me more than 256 characters.
I found the following reference before posting here which made me
suspect it was a SQL Server limitation:
http://groups.google.ca/groups?hl=e...r />
GP15.phx.
gbl%26rnum%3D1
I am using a varchar(8000) in the trigger calling the extended stored
proc.
I'm about to try stepping through the DLL but the code is a bit nasty
and it would be nice if a SQL Server guru would point out a detail to
tweak.
Thanks guys!
Thanks again,
Bertsql

extended stored procedures: is there a 256 character limit on

Bert,
For some system extended stored procedures, the parameters
are defined as varchar(256). For those, there is no way around
unless you rewrite the xp (not a good idea I think). For xp's you
write yourself, I'm not aware of any limit. Are you having a
particular problem you need help with?
Steve Kass
Drew University
Bert Szoghy wrote:

>Hello,
>On SQL Server 2000 (SP3), for extended stored procedures: is there a
>256 character limit on INPUT parameters?
>And if so, is there a way around that?
>Thanks,
>Bert
>Bert,
The thread you posted indicates there is a 256-character limit
for DBLib, not the SQL Server xp architecture. According to John
Kane in that thread:
Anil,
ESP or (Extended Stored Procs) can use the OLE-DB API and if you are using
the DBLib API you are cutting your own code's future as it is most likely
(un-confirmed) that Microsoft will drop, i.e.. remove the DB-Library API
from future SQL Server versions, such as SQL Server 2005 (Yukon).
See SQL Server 2000 BOL titles "Running Stored Procedures (OLE DB)",
"Extended Stored Procedure Architecture", "Programming Extended Stored
Procedures" and "Samples" and specifically the sample app xp-ODBC for more
info.
This is not a SQL Server limitation, but a limitation of the old DBLib
interface
only. Rewriting the xp to use a more modern interface, as John suggested,
is probably what you need to do.
SK
Bert Szoghy wrote:

>Steve Kass <skass@.drew.edu> wrote in message news:<OJxPTgSSFHA.3716@.TK2MSFT
NGP14.phx.gbl>...
>
>Hello,
>Thank you for your responses.
>I have an extended stored proc DLL with C++ code which just looks fine
>to me, which it refuses to give me more than 256 characters.
>I found the following reference before posting here which made me
>suspect it was a SQL Server limitation:
>http://groups.google.ca/groups?hl=e...K2MSFTNGP15.phx
.gbl%26rnum%3D1
>I am using a varchar(8000) in the trigger calling the extended stored
>proc.
>I'm about to try stepping through the DLL but the code is a bit nasty
>and it would be nice if a SQL Server guru would point out a detail to
>tweak.
>Thanks guys!
>Thanks again,
>Bert
>

Extended stored procedures of SQL 2k vs CLR in Yucon

I got a simple table with 2mln rows and it contine to grow from 2 to 6% a we
ek.
I need to create derivative table with from 50 to 100 computed fields(they
coud be float,int or nulls and to depend on up to 30 previous rows for the
same ID and the same day).
I unpractical to do this via stored procedure with cursor.
I tryed to write this as C# 2003 client program for SQL 2000 but it took 20
min to process first 30,000 and then I quited.
I understand that the only alternatives is to write extended stored
procedure on sql 2k or to try CLR on sql2005 beta.
Which is more preferable?
I understand that in order to do yucon I have to use Visual Studio.NET 2.0
beta2.
Or maybe I missed something?"UncleSam89" <UncleSam89@.discussions.microsoft.com> wrote in message
news:8A36CC0D-491B-494B-B117-B9068DDDBB2D@.microsoft.com...
>I got a simple table with 2mln rows and it contine to grow from 2 to 6% a
>week.
> I need to create derivative table with from 50 to 100 computed fields(they
> coud be float,int or nulls and to depend on up to 30 previous rows for the
> same ID and the same day).
> I unpractical to do this via stored procedure with cursor.
> I tryed to write this as C# 2003 client program for SQL 2000 but it took
> 20
> min to process first 30,000 and then I quited.
> I understand that the only alternatives is to write extended stored
> procedure on sql 2k or to try CLR on sql2005 beta.
> Which is more preferable?
> I understand that in order to do yucon I have to use Visual Studio.NET 2.0
> beta2.
> Or maybe I missed something?
>
You're headed in the wrong direction. Extended stored procedures and .NET
stored procedures will be slower, not faster than a TSQL cursor-based
solution.
You should try to implement a set-based solution in SQL. Instead of using a
cursor, try to build the table with a small number of INSERT and UPDATE
statements.
David|||You David don' understand what I am talking about.
You thinking that performance is the first priority of everything and this
not true.
The first priority is a customer needs.
The second priority is make simple and easy to maintain.
.................................
And probably the tenth priority is performance.
I would like to write statement like
Insert Derived table (myResultSet(...) from myInputTable;
and do myResultSet as function in C++ or C# with its own state(it means
static inside variables ).
It does not work.
If to do on SQL 2k i need to create stored procedure with cursor whivh will
execute my extended procedure with in put parameters from my input table and
use output parameters for insert in output table.
I still don't know what CLR allow in Yucon but suspect that it maybe allow
to something like previous statement .
There is impossible to do small number of insert statement because of the
way how the information collected and processed.
To compute this set based I need to have much richer set of operations than
transact SQL and by the way computations of moving averages( if you know wha
t
I am talking about) can't be set based.
So I need the procedural language like C++ or C#.
Believe me some problems can't set based resolved and my problem is one of
them.
"David Browne" wrote:

> "UncleSam89" <UncleSam89@.discussions.microsoft.com> wrote in message
> news:8A36CC0D-491B-494B-B117-B9068DDDBB2D@.microsoft.com...
> You're headed in the wrong direction. Extended stored procedures and .NET
> stored procedures will be slower, not faster than a TSQL cursor-based
> solution.
> You should try to implement a set-based solution in SQL. Instead of using
a
> cursor, try to build the table with a small number of INSERT and UPDATE
> statements.
> David
>
>|||"UncleSam89" <UncleSam89@.discussions.microsoft.com> wrote in message
news:E5FAE589-DDA0-4B33-866E-150CDDC40E2F@.microsoft.com...
> You David don' understand what I am talking about.
> You thinking that performance is the first priority of everything and this
> not true.
> The first priority is a customer needs.
> The second priority is make simple and easy to maintain.
> .................................
> And probably the tenth priority is performance.
> I would like to write statement like
> Insert Derived table (myResultSet(...) from myInputTable;
> and do myResultSet as function in C++ or C# with its own state(it means
> static inside variables ).
> It does not work.
> If to do on SQL 2k i need to create stored procedure with cursor whivh
> will
> execute my extended procedure with in put parameters from my input table
> and
> use output parameters for insert in output table.
> I still don't know what CLR allow in Yucon but suspect that it maybe
> allow
> to something like previous statement .
> There is impossible to do small number of insert statement because of the
> way how the information collected and processed.
> To compute this set based I need to have much richer set of operations
> than
> transact SQL and by the way computations of moving averages( if you know
> what
> I am talking about) can't be set based.
> So I need the procedural language like C++ or C#.
> Believe me some problems can't set based resolved and my problem is one of
> them.
>
Ok, I agree. Moving averages, YTD calculations and other windowing
operations are faster and easier in procedural languages than TSQL.
So C# vs C++. If your priority is to make it simple and easy to maintain, a
C# program is probabaly better than an extended stored procedure. Extended
stored procedures have to be written in C or C++ and bugs in an extended
stored procedure can crash the Sql Server.
The challenge with C# is (until Yukon) it's tricky to invoke from TSQL. So
your options are:
-Invoke the C# from the client, not the database server.
-Deploy the C# program as a console application on the database server and
invoke it with xp_cmdshell, or as scheduled task.
-Deploy the C# program as a COM+ server application on the database server
and invoke it with the sp_OAxxx procedures.
If you can define a permanent table which you can rebuild daily with a
scheduled task, this is pretty easy. If you have to run the calculation on
every user request and you can't insert C# between the request and the
database, then it's more of a pain.
David|||It does seems that you never wrote C++ and working only with sql 2k.
I actually asked if SQL Server 2005 beta is worth an aggravation to try in
order to resolve my problems which actually include the performance(I need a
t
least 60~100 time boost),ie
1)Will my other C# 2003 programs for SQL Server 2000 continue to work with
both beta vs.net 2.0 beta and sql 2005 beta
2)will sql server 2005 beta server CLR procedures(C# with internal state)
easy to write?
3).will it be possible for a client to query progress of the work(ie
internal state of CLR procedure?
"David Browne" wrote:

> "UncleSam89" <UncleSam89@.discussions.microsoft.com> wrote in message
> news:E5FAE589-DDA0-4B33-866E-150CDDC40E2F@.microsoft.com...
> Ok, I agree. Moving averages, YTD calculations and other windowing
> operations are faster and easier in procedural languages than TSQL.
> So C# vs C++. If your priority is to make it simple and easy to maintain,
a
> C# program is probabaly better than an extended stored procedure. Extend
ed
> stored procedures have to be written in C or C++ and bugs in an extended
> stored procedure can crash the Sql Server.
> The challenge with C# is (until Yukon) it's tricky to invoke from TSQL.
So
> your options are:
> -Invoke the C# from the client, not the database server.
> -Deploy the C# program as a console application on the database server and
> invoke it with xp_cmdshell, or as scheduled task.
> -Deploy the C# program as a COM+ server application on the database server
> and invoke it with the sp_OAxxx procedures.
> If you can define a permanent table which you can rebuild daily with a
> scheduled task, this is pretty easy. If you have to run the calculation o
n
> every user request and you can't insert C# between the request and the
> database, then it's more of a pain.
> David
>
>

Extended stored procedures of SQL 2k vs CLR in Yucon

I got a simple table with 2mln rows and it contine to grow from 2 to 6% a week.
I need to create derivative table with from 50 to 100 computed fields(they
coud be float,int or nulls and to depend on up to 30 previous rows for the
same ID and the same day).
I unpractical to do this via stored procedure with cursor.
I tryed to write this as C# 2003 client program for SQL 2000 but it took 20
min to process first 30,000 and then I quited.
I understand that the only alternatives is to write extended stored
procedure on sql 2k or to try CLR on sql2005 beta.
Which is more preferable?
I understand that in order to do yucon I have to use Visual Studio.NET 2.0
beta2.
Or maybe I missed something?
"UncleSam89" <UncleSam89@.discussions.microsoft.com> wrote in message
news:8A36CC0D-491B-494B-B117-B9068DDDBB2D@.microsoft.com...
>I got a simple table with 2mln rows and it contine to grow from 2 to 6% a
>week.
> I need to create derivative table with from 50 to 100 computed fields(they
> coud be float,int or nulls and to depend on up to 30 previous rows for the
> same ID and the same day).
> I unpractical to do this via stored procedure with cursor.
> I tryed to write this as C# 2003 client program for SQL 2000 but it took
> 20
> min to process first 30,000 and then I quited.
> I understand that the only alternatives is to write extended stored
> procedure on sql 2k or to try CLR on sql2005 beta.
> Which is more preferable?
> I understand that in order to do yucon I have to use Visual Studio.NET 2.0
> beta2.
> Or maybe I missed something?
>
You're headed in the wrong direction. Extended stored procedures and .NET
stored procedures will be slower, not faster than a TSQL cursor-based
solution.
You should try to implement a set-based solution in SQL. Instead of using a
cursor, try to build the table with a small number of INSERT and UPDATE
statements.
David
|||You David don' understand what I am talking about.
You thinking that performance is the first priority of everything and this
not true.
The first priority is a customer needs.
The second priority is make simple and easy to maintain.
..................................
And probably the tenth priority is performance.
I would like to write statement like
Insert Derived table (myResultSet(...) from myInputTable;
and do myResultSet as function in C++ or C# with its own state(it means
static inside variables ).
It does not work.
If to do on SQL 2k i need to create stored procedure with cursor whivh will
execute my extended procedure with in put parameters from my input table and
use output parameters for insert in output table.
I still don't know what CLR allow in Yucon but suspect that it maybe allow
to something like previous statement .
There is impossible to do small number of insert statement because of the
way how the information collected and processed.
To compute this set based I need to have much richer set of operations than
transact SQL and by the way computations of moving averages( if you know what
I am talking about) can't be set based.
So I need the procedural language like C++ or C#.
Believe me some problems can't set based resolved and my problem is one of
them.
"David Browne" wrote:

> "UncleSam89" <UncleSam89@.discussions.microsoft.com> wrote in message
> news:8A36CC0D-491B-494B-B117-B9068DDDBB2D@.microsoft.com...
> You're headed in the wrong direction. Extended stored procedures and .NET
> stored procedures will be slower, not faster than a TSQL cursor-based
> solution.
> You should try to implement a set-based solution in SQL. Instead of using a
> cursor, try to build the table with a small number of INSERT and UPDATE
> statements.
> David
>
>
|||"UncleSam89" <UncleSam89@.discussions.microsoft.com> wrote in message
news:E5FAE589-DDA0-4B33-866E-150CDDC40E2F@.microsoft.com...
> You David don' understand what I am talking about.
> You thinking that performance is the first priority of everything and this
> not true.
> The first priority is a customer needs.
> The second priority is make simple and easy to maintain.
> .................................
> And probably the tenth priority is performance.
> I would like to write statement like
> Insert Derived table (myResultSet(...) from myInputTable;
> and do myResultSet as function in C++ or C# with its own state(it means
> static inside variables ).
> It does not work.
> If to do on SQL 2k i need to create stored procedure with cursor whivh
> will
> execute my extended procedure with in put parameters from my input table
> and
> use output parameters for insert in output table.
> I still don't know what CLR allow in Yucon but suspect that it maybe
> allow
> to something like previous statement .
> There is impossible to do small number of insert statement because of the
> way how the information collected and processed.
> To compute this set based I need to have much richer set of operations
> than
> transact SQL and by the way computations of moving averages( if you know
> what
> I am talking about) can't be set based.
> So I need the procedural language like C++ or C#.
> Believe me some problems can't set based resolved and my problem is one of
> them.
>
Ok, I agree. Moving averages, YTD calculations and other windowing
operations are faster and easier in procedural languages than TSQL.
So C# vs C++. If your priority is to make it simple and easy to maintain, a
C# program is probabaly better than an extended stored procedure. Extended
stored procedures have to be written in C or C++ and bugs in an extended
stored procedure can crash the Sql Server.
The challenge with C# is (until Yukon) it's tricky to invoke from TSQL. So
your options are:
-Invoke the C# from the client, not the database server.
-Deploy the C# program as a console application on the database server and
invoke it with xp_cmdshell, or as scheduled task.
-Deploy the C# program as a COM+ server application on the database server
and invoke it with the sp_OAxxx procedures.
If you can define a permanent table which you can rebuild daily with a
scheduled task, this is pretty easy. If you have to run the calculation on
every user request and you can't insert C# between the request and the
database, then it's more of a pain.
David
|||It does seems that you never wrote C++ and working only with sql 2k.
I actually asked if SQL Server 2005 beta is worth an aggravation to try in
order to resolve my problems which actually include the performance(I need at
least 60~100 time boost),ie
1)Will my other C# 2003 programs for SQL Server 2000 continue to work with
both beta vs.net 2.0 beta and sql 2005 beta
2)will sql server 2005 beta server CLR procedures(C# with internal state)
easy to write?
3).will it be possible for a client to query progress of the work(ie
internal state of CLR procedure?
"David Browne" wrote:

> "UncleSam89" <UncleSam89@.discussions.microsoft.com> wrote in message
> news:E5FAE589-DDA0-4B33-866E-150CDDC40E2F@.microsoft.com...
> Ok, I agree. Moving averages, YTD calculations and other windowing
> operations are faster and easier in procedural languages than TSQL.
> So C# vs C++. If your priority is to make it simple and easy to maintain, a
> C# program is probabaly better than an extended stored procedure. Extended
> stored procedures have to be written in C or C++ and bugs in an extended
> stored procedure can crash the Sql Server.
> The challenge with C# is (until Yukon) it's tricky to invoke from TSQL. So
> your options are:
> -Invoke the C# from the client, not the database server.
> -Deploy the C# program as a console application on the database server and
> invoke it with xp_cmdshell, or as scheduled task.
> -Deploy the C# program as a COM+ server application on the database server
> and invoke it with the sp_OAxxx procedures.
> If you can define a permanent table which you can rebuild daily with a
> scheduled task, this is pretty easy. If you have to run the calculation on
> every user request and you can't insert C# between the request and the
> database, then it's more of a pain.
> David
>
>

Extended Stored Procedures in SQL 2005?

The Extended Stored Procedures (Written in C++ unmanaged code) are supported in SQL Server 2005?

Thanks

Yes they are, but they are being deprecated.

Just out of curiousity; what are you writing that cannot be done in a .NET method, but needs an xp?

Niels

Extended Stored Procedures developed in .NET?

Is it possible to create an extended sproc for SQL2K using .NET (ie C#,
VB.NET, etc)? All of the examples I've found focus on C++.
Cheers, JonNo, not in managed code. There's a KB describing that it is not supported fo
r SQL Server to host
managed code. Below is my "canned" answer:
It is not supported for extended stored procedures or sp_OA procedures to ca
ll .NET code in CLR;
hosted within SQL Server's address space..
See:
http://support.microsoft.com/defaul...kb;en-us;322884
Also, below is with permission from David Browne, explaining how you can hav
e SQL Server execute CLR
code executing in its own process:
"
Short answer: Don't do it.
Calling managed code inside a stored procedure is not supported.
http://support.microsoft.com/defaul...kb;en-us;322884
At least not directly. You need some sort of unmanaged proxy to communicate
with your component running in another process.
For instance, http, or, drum roll, a COM+ Server Application.
This will cause COM+ to load an unmanaged proxy object in the SqlServer
process and will load the CLR into a COM+ surrogate process (dllhost.exe).
Which somebody here mentioned last w, and I just got around to testing.
It's all perfectly transparent to you, but you have to set up the COM+
server application.
Remember this is something different from .net remoting. With .NET remoting
you have a _managed_ proxy object in the local process, and so you load the
CLR in the local process as well as the remote process.
Anyway here's what I did:
I created this VB class
comTest.vb listing:
Imports System.Runtime.InteropServices
<ClassInterface(ClassInterfaceType.AutoDual),
ProgId("comTest.comTestClass")> _
Public Class comTest
Public Function Hello() As String
Return "hello"
End Function
End Class
build comTest.dll and registered it with
regasm /codebase comTest.dll /tlb:comTest.tlb
(complains that I haven't strong-named my assembly, which you should do.)
created an empty COM+ server application, set to run under a local
administrator account, and dragged comTest.dll into its components folder.
created an unmanaged host (vbscript will do), and invoked the component
using IDispach just like SQLServer.
test.vbs listing
Set d = CreateObject("comTest.comTestClass")
MsgBox d.Hello
Then I used the .net command line debugger cordbg.exe's 'pro' command to
list the processes hosting the CLR. And procexp.exe from
www.sysinternals.com to verify that the CLR's dll's were not loaded in my
unmanaged process. My unmanaged host did not load the CLR, although it
loaded "comsvcs.dll", and the CLR was loaded by the dllhost.exe process.
Then in sql I ran
declare @.object int
declare @.msg varchar(50)
declare @.rc int
declare @.hr int
declare @.source varchar(1000)
declare @.description varchar(1000)
exec @.rc = sp_oacreate 'comTest.comTestClass', @.object output
if @.rc <> 0
begin
EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
print 'create failed ' + @.description
return
end
exec @.rc = sp_oamethod @.object, 'Hello', @.msg output
if @.rc <> 0
begin
EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
print 'method failed ' + @.description
return
end
print 'return: ' + @.msg
exec @.rc = sp_oadestroy @.object
if @.rc <> 0
begin
EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
print 'destroy failed ' + @.description
return
end
Ran fine, and still only one CLR loaded into dllhost.exe's process. So
think we can safely conclude that COM+ server applications do not violate
the prohibition against running managed code in SQLServer's process and
provide a convenient mechanism for interoperating with managed code from
TSQL.
David
"
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jon Pope" <jon.pope@.healthlinkinc.com> wrote in message
news:u3nFUEQGFHA.2156@.TK2MSFTNGP10.phx.gbl...
> Is it possible to create an extended sproc for SQL2K using .NET (ie C#, VB
.NET, etc)? All of the
> examples I've found focus on C++.
> Cheers, Jon
>|||You rock!
Cheers, Jon
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23yum2SQGFHA.1140@.TK2MSFTNGP10.phx.gbl...
> No, not in managed code. There's a KB describing that it is not supported
> for SQL Server to host managed code. Below is my "canned" answer:
> It is not supported for extended stored procedures or sp_OA procedures to
> call .NET code in CLR; hosted within SQL Server's address space..
> See:
> http://support.microsoft.com/defaul...kb;en-us;322884
> Also, below is with permission from David Browne, explaining how you can
> have SQL Server execute CLR code executing in its own process:
> "
> Short answer: Don't do it.
> Calling managed code inside a stored procedure is not supported.
> http://support.microsoft.com/defaul...kb;en-us;322884
> At least not directly. You need some sort of unmanaged proxy to
> communicate
> with your component running in another process.
> For instance, http, or, drum roll, a COM+ Server Application.
> This will cause COM+ to load an unmanaged proxy object in the SqlServer
> process and will load the CLR into a COM+ surrogate process (dllhost.exe).
> Which somebody here mentioned last w, and I just got around to testing.
> It's all perfectly transparent to you, but you have to set up the COM+
> server application.
> Remember this is something different from .net remoting. With .NET
> remoting
> you have a _managed_ proxy object in the local process, and so you load
> the
> CLR in the local process as well as the remote process.
> Anyway here's what I did:
> I created this VB class
> comTest.vb listing:
> Imports System.Runtime.InteropServices
> <ClassInterface(ClassInterfaceType.AutoDual),
> ProgId("comTest.comTestClass")> _
> Public Class comTest
> Public Function Hello() As String
> Return "hello"
> End Function
> End Class
>
> build comTest.dll and registered it with
> regasm /codebase comTest.dll /tlb:comTest.tlb
> (complains that I haven't strong-named my assembly, which you should do.)
> created an empty COM+ server application, set to run under a local
> administrator account, and dragged comTest.dll into its components folder.
> created an unmanaged host (vbscript will do), and invoked the component
> using IDispach just like SQLServer.
> test.vbs listing
> Set d = CreateObject("comTest.comTestClass")
> MsgBox d.Hello
>
> Then I used the .net command line debugger cordbg.exe's 'pro' command to
> list the processes hosting the CLR. And procexp.exe from
> www.sysinternals.com to verify that the CLR's dll's were not loaded in my
> unmanaged process. My unmanaged host did not load the CLR, although it
> loaded "comsvcs.dll", and the CLR was loaded by the dllhost.exe process.
> Then in sql I ran
> declare @.object int
> declare @.msg varchar(50)
> declare @.rc int
> declare @.hr int
> declare @.source varchar(1000)
> declare @.description varchar(1000)
> exec @.rc = sp_oacreate 'comTest.comTestClass', @.object output
> if @.rc <> 0
> begin
> EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
> print 'create failed ' + @.description
> return
> end
> exec @.rc = sp_oamethod @.object, 'Hello', @.msg output
> if @.rc <> 0
> begin
> EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
> print 'method failed ' + @.description
> return
> end
> print 'return: ' + @.msg
> exec @.rc = sp_oadestroy @.object
> if @.rc <> 0
> begin
> EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
> print 'destroy failed ' + @.description
> return
> end
>
> Ran fine, and still only one CLR loaded into dllhost.exe's process. So
> think we can safely conclude that COM+ server applications do not violate
> the prohibition against running managed code in SQLServer's process and
> provide a convenient mechanism for interoperating with managed code from
> TSQL.
>
> David
> "
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Jon Pope" <jon.pope@.healthlinkinc.com> wrote in message
> news:u3nFUEQGFHA.2156@.TK2MSFTNGP10.phx.gbl...
>|||> You rock!
In this case, I'd say that it is David who rock. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jon Pope" <jon.pope@.healthlinkinc.com> wrote in message
news:%23kLhBsQGFHA.3376@.TK2MSFTNGP12.phx.gbl...
> You rock!
> Cheers, Jon
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%23yum2SQGFHA.1140@.TK2MSFTNGP10.phx.gbl...
>sql

Extended Stored Procedures DB-Lib Alternative

Since DBlib is no longer the suggested method for connecting back to
sql server from an Extended Stored Procedure, has anyone built any
extended stored procedures that use other connection methods like
OLEDB? Has anyone seen links to any sample extended stored procedures
that use something other than db-lib? In particular I am interested
in something that connects back to the database as the user who
invoked the extended stored procedure. I haven't had much luck
finding any.

Also, is there an alternative for the bcp api that is a little more
current and has support for newer datatypes like bigint? We currently
use the bcp api from an extended stored procdure written in C++, but
now need to add bigint support which the bcp api doesn't have.

Thanks for any advice.You can use ODBC or OLEDB, I prefer ODBC because it is lean and mean and I
do not like COM. Both support the full set of data types including BIGINT.

ODBC contains an updated version of the BCP API since SQL Server 7.0 which
also supports all new data types or alternatively you can use the
IRowsetFastload interface if you want to use OLE DB.

There ships an ODBC sample with SQL Server, see "C:\Program Files\Microsoft
SQL Server\80\Tools\DevTools\Samples\ods\xp_odbc", there is no OLE-DB
sample.

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-2004 All rights reserved.

"Bruce" <sandell@.pacbell.net> wrote in message
news:595024a5.0409171238.10aed173@.posting.google.c om...
> Since DBlib is no longer the suggested method for connecting back to
> sql server from an Extended Stored Procedure, has anyone built any
> extended stored procedures that use other connection methods like
> OLEDB? Has anyone seen links to any sample extended stored procedures
> that use something other than db-lib? In particular I am interested
> in something that connects back to the database as the user who
> invoked the extended stored procedure. I haven't had much luck
> finding any.
> Also, is there an alternative for the bcp api that is a little more
> current and has support for newer datatypes like bigint? We currently
> use the bcp api from an extended stored procdure written in C++, but
> now need to add bigint support which the bcp api doesn't have.
> Thanks for any advice.|||> Since DBlib is no longer the suggested method for connecting back to
> sql server from an Extended Stored Procedure, has anyone built any
> extended stored procedures that use other connection methods like
> OLEDB? Has anyone seen links to any sample extended stored procedures
> that use something other than db-lib? In particular I am interested

ODBC Connection works fine for the loopback in ESP's. Sample
application for the same is provided in the SQL Server Samples|||Thanks very much for the advice. I'll take a look into bcp for odbc.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

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 and UDP sockets

Hello,
I'm trying this question again; please excuse the repetition.
I have written an extended stored procedure using Visual C++ .Net
(unmanaged). The procedure is intended to send out a UDP multicast message
when new data arrives. The message doesn't seem to be sent.
I have verified that the ESP is getting called correctly (in response to an
INSERT trigger). I have verified that none of the Winsock API procedures
return errors when called from the ESP. I have verified the code that
actually sends the multicast message by running the exact same code from a
console application, where it works fine--including running it when logged i
n
under the account that SQL Server runs under.
There seems to be something unexpectedly different about the environment
that the ESP runs in. I'm at a loss to figure out what it is. Any clues
would be appreciated.
Thanks.
Ed Hoch
GEDDS Manager
Geophysical Institute
University of Alaska FairbanksI'll throw out some thoughts, though I don't have anything definite:
1. Are you initializing the Windows socket library right within the proc?
(I think it's WSAInitialize()). That definitely would need to be done.
2. If you can't get a solution, I know you could set a named Windows event
inside your extended proc (I've done that) and then have another process
waiting on that event and have it send the data gram.
3. Do you have fiber mode turned on in SQL Server? If so, that may affect
threading and sockets (just guessing...)
Mike
"Edward Hoch" <EdwardHoch@.discussions.microsoft.com> wrote in message
news:1A237FAF-EFD9-481B-99F8-6B05CCB3DA35@.microsoft.com...
> Hello,
> I'm trying this question again; please excuse the repetition.
> I have written an extended stored procedure using Visual C++ .Net
> (unmanaged). The procedure is intended to send out a UDP multicast
message
> when new data arrives. The message doesn't seem to be sent.
> I have verified that the ESP is getting called correctly (in response to
an
> INSERT trigger). I have verified that none of the Winsock API procedures
> return errors when called from the ESP. I have verified the code that
> actually sends the multicast message by running the exact same code from a
> console application, where it works fine--including running it when logged
in
> under the account that SQL Server runs under.
> There seems to be something unexpectedly different about the environment
> that the ESP runs in. I'm at a loss to figure out what it is. Any clues
> would be appreciated.
> Thanks.
> Ed Hoch
> --
> GEDDS Manager
> Geophysical Institute
> University of Alaska Fairbanks|||Hi Mike,
Thanks for the reply.
1. I am initializing the socket library with WSAStartup. It seems to work
from the console version of the program, so I think the initialization is
working properly.
2. Named events seem like a possibility. What's a good reference for
those?
3. I am not using the "Windows NT fibers" in my SQL Server. Would it be
more or less likely for the thing to work with fibers enabled?
Thanks again for the reply.
Ed Hoch
"Mike Jansen" wrote:

> I'll throw out some thoughts, though I don't have anything definite:
> 1. Are you initializing the Windows socket library right within the proc?
> (I think it's WSAInitialize()). That definitely would need to be done.
> 2. If you can't get a solution, I know you could set a named Windows event
> inside your extended proc (I've done that) and then have another process
> waiting on that event and have it send the data gram.
> 3. Do you have fiber mode turned on in SQL Server? If so, that may affect
> threading and sockets (just guessing...)
> Mike
> "Edward Hoch" <EdwardHoch@.discussions.microsoft.com> wrote in message
> news:1A237FAF-EFD9-481B-99F8-6B05CCB3DA35@.microsoft.com...
> message
> an
> in
>
>|||> Thanks for the reply.
> 1. I am initializing the socket library with WSAStartup. It seems to
work
> from the console version of the program, so I think the initialization is
> working properly.
WSAStartup, that was it :)

> 2. Named events seem like a possibility. What's a good reference for
> those?
In Windows API look at CreateEvent(), SetEvent(), OpenEvent(). I think
there's even an overview section on synchronization objects. The thing to
be aware of for named objects is that in order for them to work across
process boundaries you need to set up the SECURITY_ATTRIBUTES properly
(unless the other process happens to be running under the same account as
the SQL Server process). The named event won't pass any other context
information.
If you need to pass context information along with triggering an event, you
may want to do something like write out a file and have the process
monitoring a folder rather than an event. There are other options besides
files but file is simple and straightforward.
If you want more information, we can take this offline or to another group
since it's off-topic and can get fairly involved.

> 3. I am not using the "Windows NT fibers" in my SQL Server. Would it be
> more or less likely for the thing to work with fibers enabled?
Like I said, I was just guessing, but I'd guess you might have issues if
fibers were ENABLED a fiber is a "mutant" thread.
Also, perhaps you could try this (though I don't know if it's wise): In
your stored proc WinMain/on load, create a separate thread. Do your sockets
in that thread. That way it's still in the same process but on its own
thread. So your worker thread starts and waits for an item in a context
queue (something you create). When it receives an item in the queue, it
dequeues it and sends a datagram. In your exetended proc, just add items to
the queue. First, I'd do some scanning to see if secondary threads in
extended procs are OK. Regardless, the divantage of this is that its
starting to get complicated and complicated things mess up more often and
messing up inside the process space of SQL Server isn't the greatest thing.
So even if secondary threads are OK, BE VERY CAREFUL.
Mike|||What is the service account of SQL Server? Start the SQL Server service from
the command line, and execute the XP, see if that makes a difference.
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.
"Edward Hoch" <EdwardHoch@.discussions.microsoft.com> wrote in message
news:1A237FAF-EFD9-481B-99F8-6B05CCB3DA35@.microsoft.com...
> Hello,
> I'm trying this question again; please excuse the repetition.
> I have written an extended stored procedure using Visual C++ .Net
> (unmanaged). The procedure is intended to send out a UDP multicast
> message
> when new data arrives. The message doesn't seem to be sent.
> I have verified that the ESP is getting called correctly (in response to
> an
> INSERT trigger). I have verified that none of the Winsock API procedures
> return errors when called from the ESP. I have verified the code that
> actually sends the multicast message by running the exact same code from a
> console application, where it works fine--including running it when logged
> in
> under the account that SQL Server runs under.
> There seems to be something unexpectedly different about the environment
> that the ESP runs in. I'm at a loss to figure out what it is. Any clues
> would be appreciated.
> Thanks.
> Ed Hoch
> --
> GEDDS Manager
> Geophysical Institute
> University of Alaska Fairbanks

Extended Stored Procedures 7.0 - 2000

I have a problem with passing parameters into an extended stored procedure in sql 2000 that was not present in sql 7.0.

I pass in a parameter of type varchar(8000) but sql 2000 truncates this value to 255 characters.

I was using srv_paramdata(sproc,1) for example to get the pertinent data. Microsoft says that srv_paramdata has been superceded by srv_paraminfo, but this function is crashing my dll when I run it....

BYTE bType;
unsigned long cbMaxLen;
unsigned long cbActualLen;
BOOL fNull;
BYTE data;

// Use srv_paraminfo to get data type and length information.
srv_paraminfo(sproc, 2, &bType, &cbMaxLen, &cbActualLen,&data, &fNull);

Any ideas?

This worked in sql 7.0.

wsprintf(string,"%s",(const char*) srv_paramdata(sproc,1));

Umm help appreciated.

WintermuteUmm actual guys... thanks to anyone who has looked to help me out, but I think I have (*this).sorted->muchos[gracias++];

umm in keeping with Open Source Software paradigms and stuff if anyone is curious

PBYTE*data;
BYTE type;
unsigned long Maxlen;
unsigned long Reallen;
int nParams;
BOOL boolnull;
char head_descriptor[24];
FILE*file;

nParams=srv_rpcparams(sproc);


data=new PBYTE[1];
if(data==NULL)
{
ServerErrorMsg(sproc,"Was unable to allocate the requisite memory for this data operation");
return -1;
};
memset(data,0,nParams*sizeof(PBYTE));


srv_paraminfo(sproc,2,&type,&Maxlen,&Reallen,NULL,&boolnull);

sprintf(head_descriptor,"Parameter 2: Input");
srv_describe(sproc,2,head_descriptor,SRV_NULLTERM, type,Reallen,type,Reallen,NULL);

if(boolnull==0)
{
data[0]=(unsigned char*)malloc(Reallen);
if(data[0]==NULL)
{
ServerErrorMsg(sproc,"Unable to allocate memory for this variable!");
delete data;
return -1;
};

srv_paraminfo(sproc,2,&type,&Maxlen,&Reallen,data[0],&boolnull);
}
else
{
ServerErrorMsg(sproc,"There Seems to be no data present for parameter 2");
return -1;
};

file=fopen("C:/bod.txt","a+");
if(file==NULL)
{
ServerErrorMsg(sproc,"Unable to access the specified filename for file input, Please check the filename and try again!");
return -1;
};

fwrite(data[0],Reallen,1,file);
fclose(file);


return 0;
};

Regards
Wintermute.sql

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

Hello!
I have a difficult situation with a clustered server. It consists of two
parts - Cluster_1 and Cluster_2 computers with WINDOWS Server2003. Cluster_1
and Cluster_2 are with 6 GB RAM.
The whole clustered server is named Cluster.
SQL Server 2000 is installed on Cluster.
Service Pack 4 for SQL Server 2000 is installed and latest hot-fix KB899761
is installed too.
I have some extended stored procedures implemented in xp_parser.dll.
One of those is - lets say xp_MyProc.
The problem is - when I call xp_MyProc, the returned result is correct, but
the execution time is extremely long.
xp_parser.dll is placed in C:\WINDOWS\System32 on both Cluster_1 and
Cluster_2.
The shared hard disk between Cluster_1 and Cluster_2 is F:
A copy of xp_parser.dll is not located on F:
What is the cause of the extremely long execution time of xp_MyProc?
When I call xp_MyProc for second and third time - the situation is not
better.
Thank you!
What other environment did this xp run quick? Did it ever? What does it
do?
Here's my car and it runs slow Mr. Mechanic, but I'm not going to tell you
what type of car it is. Good luck.
Sincerely,
Anthony Thomas

"ggeshev" <ggeshev@.tonegan.bg> wrote in message
news:%23OX0z563FHA.2816@.tk2msftngp13.phx.gbl...
> Hello!
>
> I have a difficult situation with a clustered server. It consists of two
> parts - Cluster_1 and Cluster_2 computers with WINDOWS Server2003.
Cluster_1
> and Cluster_2 are with 6 GB RAM.
> The whole clustered server is named Cluster.
>
> SQL Server 2000 is installed on Cluster.
> Service Pack 4 for SQL Server 2000 is installed and latest hot-fix
KB899761
> is installed too.
>
> I have some extended stored procedures implemented in xp_parser.dll.
> One of those is - lets say xp_MyProc.
> The problem is - when I call xp_MyProc, the returned result is correct,
but
> the execution time is extremely long.
>
> xp_parser.dll is placed in C:\WINDOWS\System32 on both Cluster_1 and
> Cluster_2.
> The shared hard disk between Cluster_1 and Cluster_2 is F:
> A copy of xp_parser.dll is not located on F:
>
> What is the cause of the extremely long execution time of xp_MyProc?
> When I call xp_MyProc for second and third time - the situation is not
> better.
>
> Thank you!
>
|||XP's should be installed in the BINN directory, which is on the shared
resource disk, same as all the other SQL Server XP's
In that case you do not have to register the XP with an absolute PATH so it
simply works.
With regards to your performance, does it perform when not running on a
cluster?
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.
"ggeshev" <ggeshev@.tonegan.bg> wrote in message
news:%23OX0z563FHA.2816@.tk2msftngp13.phx.gbl...
> Hello!
>
> I have a difficult situation with a clustered server. It consists of two
> parts - Cluster_1 and Cluster_2 computers with WINDOWS Server2003.
> Cluster_1
> and Cluster_2 are with 6 GB RAM.
> The whole clustered server is named Cluster.
>
> SQL Server 2000 is installed on Cluster.
> Service Pack 4 for SQL Server 2000 is installed and latest hot-fix
> KB899761
> is installed too.
>
> I have some extended stored procedures implemented in xp_parser.dll.
> One of those is - lets say xp_MyProc.
> The problem is - when I call xp_MyProc, the returned result is correct,
> but
> the execution time is extremely long.
>
> xp_parser.dll is placed in C:\WINDOWS\System32 on both Cluster_1 and
> Cluster_2.
> The shared hard disk between Cluster_1 and Cluster_2 is F:
> A copy of xp_parser.dll is not located on F:
>
> What is the cause of the extremely long execution time of xp_MyProc?
> When I call xp_MyProc for second and third time - the situation is not
> better.
>
> Thank you!
>

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