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 SQL functions exported by DLLs

We have a few rather advanced calculation routines
developed in C++. Currently we are using a Sybase ASA
database for our app. and these routines are accessible
by so called external functions exported by a DLL
(dynamic link library) that we wrote in C++. Sybase
supports this quite nicely.
The client app can call such routines by:
"select get_roof_angle() as angle from dummy;"
or
":angle = call get_roof_angle();"
Now we are checking if other databases can provide us
with similar abilities. So I just wonder if SQLServer/MSDE
supports the ability to call a routine exported by a
DLL? If yes, does anyone know the SQL syntax to declare
it in SQLServer/MSDE
Best regards
Peter Sullvan
hi Peter,
Peter Sullvan wrote:
> We have a few rather advanced calculation routines
> developed in C++. Currently we are using a Sybase ASA
> database for our app. and these routines are accessible
> by so called external functions exported by a DLL
> (dynamic link library) that we wrote in C++. Sybase
> supports this quite nicely.
> The client app can call such routines by:
> "select get_roof_angle() as angle from dummy;"
> or
> ":angle = call get_roof_angle();"
> Now we are checking if other databases can provide us
> with similar abilities. So I just wonder if SQLServer/MSDE
> supports the ability to call a routine exported by a
> DLL? If yes, does anyone know the SQL syntax to declare
> it in SQLServer/MSDE
> Best regards
> Peter Sullvan
you can write so called "extended stored procedures" externded by dll... at
http://www.sqldev.net/xp.htm you can find some examples as long as usefull
links..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

External SQL functions exported by DLLs

We have a few rather advanced calculation routines
developed in C++. Currently we are using a Sybase ASA
database for our app. and these routines are accessible
by so called external functions exported by a DLL
(dynamic link library) that we wrote in C++. Sybase
supports this quite nicely.
The client app can call such routines by:
"select get_roof_angle() as angle from dummy;"
or
":angle = call get_roof_angle();"
Now we are checking if other databases can provide us
with similar abilities. So I just wonder if MS-SQLServer
supports the ability to call a routine exported by a
DLL? If yes, does anyone know the SQL syntax to declare
it in MS-SQLServer.
Best regards
Peter SullvanCurrently only as (extended) stored procedures.
EXEC xp_procname -- which really is a function in a DLL file.
SQL Server 2005 will implement CLR inside SQL Server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Peter Sullvan" <ps@.NOSPAMonline.de> wrote in message
news:%23Usr70jJFHA.3960@.TK2MSFTNGP09.phx.gbl...
> We have a few rather advanced calculation routines
> developed in C++. Currently we are using a Sybase ASA
> database for our app. and these routines are accessible
> by so called external functions exported by a DLL
> (dynamic link library) that we wrote in C++. Sybase
> supports this quite nicely.
> The client app can call such routines by:
> "select get_roof_angle() as angle from dummy;"
> or
> ":angle = call get_roof_angle();"
> Now we are checking if other databases can provide us
> with similar abilities. So I just wonder if MS-SQLServer
> supports the ability to call a routine exported by a
> DLL? If yes, does anyone know the SQL syntax to declare
> it in MS-SQLServer.
> Best regards
> Peter Sullvan
>|||In SQL Server 2000, you can create an extended stored procedure. See the
Books Online for details. For scalar functions, you can encapsulate an
extended stored procedure in a Transact-SQL scalar function like the example
below.
CREATE FUNCTION get_roof_angle()
RETURNS int
AS
BEGIN
DECLARE @.RoofAngle int
EXEC master..xp_get_roof_angle @.RoofAngle OUT
RETURN @.RoofAngle
END
GO
SELECT dbo.get_roof_angle()
GO
The planned SQL Server 2005 version will allow you to develop functions
using CLR managed code.
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter Sullvan" <ps@.NOSPAMonline.de> wrote in message
news:%23Usr70jJFHA.3960@.TK2MSFTNGP09.phx.gbl...
> We have a few rather advanced calculation routines
> developed in C++. Currently we are using a Sybase ASA
> database for our app. and these routines are accessible
> by so called external functions exported by a DLL
> (dynamic link library) that we wrote in C++. Sybase
> supports this quite nicely.
> The client app can call such routines by:
> "select get_roof_angle() as angle from dummy;"
> or
> ":angle = call get_roof_angle();"
> Now we are checking if other databases can provide us
> with similar abilities. So I just wonder if MS-SQLServer
> supports the ability to call a routine exported by a
> DLL? If yes, does anyone know the SQL syntax to declare
> it in MS-SQLServer.
> Best regards
> Peter Sullvan
>|||To clarify my response, you can create extended stored procedures in all
versions of SQL Server. However, Transact-SQL functions are available only
in SQL 2000+.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23YTFuOkJFHA.1280@.TK2MSFTNGP09.phx.gbl...
> In SQL Server 2000, you can create an extended stored procedure. See the
> Books Online for details. For scalar functions, you can encapsulate an
> extended stored procedure in a Transact-SQL scalar function like the
> example below.
> CREATE FUNCTION get_roof_angle()
> RETURNS int
> AS
> BEGIN
> DECLARE @.RoofAngle int
> EXEC master..xp_get_roof_angle @.RoofAngle OUT
> RETURN @.RoofAngle
> END
> GO
> SELECT dbo.get_roof_angle()
> GO
> The planned SQL Server 2005 version will allow you to develop functions
> using CLR managed code.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Peter Sullvan" <ps@.NOSPAMonline.de> wrote in message
> news:%23Usr70jJFHA.3960@.TK2MSFTNGP09.phx.gbl...
>|||SQL Server only supports Extended Stored Procedures, no Extended Functions,
but you could wrap an extended stored procedure inside a user defined T-SQL
function and get the same behavior, but you need to use output parameters in
your extended stored procedure to return values, you can not return a result
set.
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.
"Peter Sullvan" <ps@.NOSPAMonline.de> wrote in message
news:%23Usr70jJFHA.3960@.TK2MSFTNGP09.phx.gbl...
> We have a few rather advanced calculation routines
> developed in C++. Currently we are using a Sybase ASA
> database for our app. and these routines are accessible
> by so called external functions exported by a DLL
> (dynamic link library) that we wrote in C++. Sybase
> supports this quite nicely.
> The client app can call such routines by:
> "select get_roof_angle() as angle from dummy;"
> or
> ":angle = call get_roof_angle();"
> Now we are checking if other databases can provide us
> with similar abilities. So I just wonder if MS-SQLServer
> supports the ability to call a routine exported by a
> DLL? If yes, does anyone know the SQL syntax to declare
> it in MS-SQLServer.
> Best regards
> Peter Sullvan
>

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

Expressions

When creating expressions we have access to a list of functions. It is my understanding that while these functions seem to have the same names and parameters as SQL functions, they are not so. They are implemented in the package libraries themselves. It is also my understanding that this function library cannot be extended to add new ones.

Am I correct? If so... why not?

Leaving alone the fact that they follow the same screwy names as SQL (instead of .NET on which SSIS is built on) and what seems to be a limited library (i.e. You have YEAR(), MONTH(), DAY() functions but no HOUR(), MINUTE(), or SEC() functions -- instead you have to use DATEPART())

I mean honestly... a common expression for most people is using date and times for folder and filenames... So instead of a simple .NET type expression of DateTime.ToString("yyyyMMdd") or Format(DateTime.Now, "yyyyMMdd_hhmmss") I end up with the very complex:

(DT_STR, 4, 1252) YEAR( GETDATE() ) + RIGHT("0" + (DT_STR, 2, 1252) MONTH( GETDATE() ), 2) + RIGHT("0" + (DT_STR, 2, 1252) DAY( GETDATE() ), 2) + "_" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("hour", GetDate()), 2) + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("minute", GetDate()), 2) + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("second", GetDate()), 2)

Personally I find myself using Script Tasks and Variables to "build" my expressions and just use Expressions to set the property to the variable. (Which I think may defeat the full purpose of expressions.)

Any thoughts?

Hi Jason,

Yes, you are correct that the SSIS expression language in SQL Server 2005 is not extensible, and yes the set of available functions id fairly small. The primary reason for that is that the development team simply ran out of time. Several of the functions that you see today were in fact added in response to beta feedback from customers such as yourself. I think it would be great if you have the time to submit your suggestions for things that you would like to see added. You can do that by going here:http://msdn.microsoft.com/sql/bi/integration/ and choosing the MSDN Product Feedback link under Support.

As for why the expression language is not .net -- there is a fairly long history there. Note that the existing language is very C-like, and is optimized to work efficiently with DataFlow data types. The dataflow engine itself is native code, not .NET, and the expression language is tightly integrated with the type system.

I hope that helps to answer your questions. Do not hesitate to ask if you have further questions.

Thanks
Mark

|||

Thanks Mark,

Will take up your suggestion and submit feedback.

I am surprised (and a bit disheartened) to learn that the dataflow engine is not dotNET. Microsoft has spent a lot of time and publicity pushing .NET. (Hell, it was even integrated into the SQL engine...)

Authoring packages is done in Visual Studio 2005 which is PRIMARLY a dotnet development environment. (Lets not go down the road of asking why VS itself is not written in dotnet...)

Most of the tasks SEEM to be written in dotNET... (unless they are just PIA wrappers...)

Yet...

You guys (and Microsoft as a whole) does not seem fully utilize your own products... Microsoft Office is still plain old C++ (or C-like), VS is plain old C++ (or C-like), and the SSIS core engine...

(I apologize if this puts you on the spot Mark.)

|||

Not using our own products? Remember we own the best-selling c++ tools too! ;-)

One key reason why our engine is native code is that, through native code, we can write our own code to manage memory. One of the benefits of .Net is precisely that memory is managed for you - which is a real boon until you need to get down and dirty at the system level. .Net has indeed been widely marketed - but not as a replacement for native code in all places.

BTW, the excellent Steve Texeira has an interesting blog on his role as a C++ program manager which includes many insightful posts on such issues, such as this one: http://blogs.msdn.com/texblog/archive/2006/01/04/509428.aspx

I would never regard C++ as "plain old" - and again, Steve Tex has an good outlook on that too: http://blogs.msdn.com/texblog/archive/2006/01/04/509396.aspx

Donald

BTW - our designer, the UI bits, is indeed written in managed code. But the underlying components and tasks of SSIS are mostly native code.

|||

Thanks for responding Donald.

Of course, C++ is not "plain old"... I labelled it that way to emphasize what I perceived as a push for .NET technologies from Microsoft yet a reluctance to fully board the bandwagon...

We still use C++ here in our shop, but for server side process where speed is an issue.

|||

Mark Durley wrote:

Yes, you are correct that the SSIS expression language in SQL Server 2005 is not extensible, and yes the set of available functions id fairly small. The primary reason for that is that the development team simply ran out of time. Several of the functions that you see today were in fact added in response to beta feedback from customers such as yourself. I think it would be great if you have the time to submit your suggestions for things that you would like to see added. You can do that by going here:http://msdn.microsoft.com/sql/bi/integration/ and choosing the MSDN Product Feedback link under Support.

While we're on the subject of the usability of expressions - a slight digression.

I dislike the fact that sometimes you have to write horribly long, clunky expressions to achieve some things - particularly around concatenation. Jason gives a great example regarding datetimes in this thread.

A really nice feature would be to allow us to build up these long concatenations bit by bit in the same Derived Column component. The way I envisage this happening is by referencing other derived columns in the same component.

I've requested this feature at the feedback center but the site is down at the moment so I can't link to it!!

-Jamie

Express vs MySQL

Where can I go to get information on supported features on Express?

I had heard that express does not support functions.

I have a dotnet application that is connecting to Access and the client wants to go to either mysql or express and I am looking for a pro or con on both.I usually use SQL2000 but I am wondering if there is a limit on the size of the database for express.Which is not a concern for about year but will be an issue after that.

This should get you started up:

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

HTH, jens Suessmeyer.

Exporting Stored Procs

Hi,
Novice user here. I am developing a large number of stored procedures
and user defined functions and I want to be able to export them everynight
for back up. I know that I can user the Query Analyzer tool to export them
to .sql files one at a time, but I am approaching about 100 procedures and
functions and that can be quite tedious.
Anyone know how I can batch export a set of procs at one time?
JDWhy don't you just make a special backup of the database every night?
And have you considered using source control for database objects?
"Joe Delphi" <delphi561@.nospam.cox.net> wrote in message
news:OmpYe.261467$E95.192029@.fed1read01...
> Hi,
> Novice user here. I am developing a large number of stored procedures
> and user defined functions and I want to be able to export them everynight
> for back up. I know that I can user the Query Analyzer tool to export
> them
> to .sql files one at a time, but I am approaching about 100 procedures and
> functions and that can be quite tedious.
> Anyone know how I can batch export a set of procs at one time?
>
> JD
>|||Joe
One way to do this is to create a SQL Server Job scheduled to run at the
desired frequency to run an ActiveX Task that scripts out the Stored
Procedures.
Below is a VBScript example that can be used to script all Stored Procedures
in a Database to a file.
~~~
Const LOG_FILE = "c:\script.sql"
Const SQL_INSTANCE = "(local)" ' SQL Server Instance where the DB to script
exists
Const DB_NAME = "pubs" ' This could be replaced with a loop to script all DB
's
' FSO I/O Mode
Const FORWRITING = 2
Const FORAPPENDING = 8
' DMO Scripting Constants
Const SQLDMOScript_ObjectPermissions = 2
Const SQLDMOScript_Default = 4
Const SQLDMOScript_OwnerQualify = 262144
Dim oStoredProcedure
Dim oSQLServer
Dim oDatabase
Dim oFSO
Dim sTexttoWrite
Set oSQLServer = CreateObject("SQLDMO.SQLServer")
Set oFSO = CreateObject("Scripting.FileSystemObject")
oSQLServer.LoginSecure = True
oSQLserver.Connect(SQL_INSTANCE)
Set oStoredProcedure = CreateObject("SQLDMO.StoredProcedure")
For Each oStoredProcedure In oSQLServer.Databases(DB_NAME).StoredProcedures
If Not oStoredProcedure.SystemObject Then ' Only Script User Objects
' Script the Object Permissions and Owner
sTexttoWrite =
oSQLServer.Databases(DB_NAME).StoredProcedures(oStoredProcedure.Name).Script
(SQLDMOScript_Default
+ SQLDMOScript_ObjectPermissions + SQLDMOScript_OwnerQualify)
oFSO.OpenTextFile(LOG_FILE, FORAPPENDING, True).WriteLine(sTexttoWrite)
End If
Next
oSQLServer.DisConnect
Set oFSO = Nothing
Set oStoredProcedure = Nothing
Set oSQLServer = Nothing
~~~
- Peter Ward
WARDY IT Solutions
"Joe Delphi" wrote:

> Hi,
> Novice user here. I am developing a large number of stored procedures
> and user defined functions and I want to be able to export them everynight
> for back up. I know that I can user the Query Analyzer tool to export the
m
> to .sql files one at a time, but I am approaching about 100 procedures and
> functions and that can be quite tedious.
> Anyone know how I can batch export a set of procs at one time?
>
> JD
>
>|||if your problem is only scripting then
you can use Entrprise Manager> All Tasks > Genrate sql secript
you can select all sprocs at a time to .sql
Regards
R.D
"P. Ward" wrote:
> Joe
> One way to do this is to create a SQL Server Job scheduled to run at the
> desired frequency to run an ActiveX Task that scripts out the Stored
> Procedures.
> Below is a VBScript example that can be used to script all Stored Procedur
es
> in a Database to a file.
> ~~~
> Const LOG_FILE = "c:\script.sql"
> Const SQL_INSTANCE = "(local)" ' SQL Server Instance where the DB to scrip
t
> exists
> Const DB_NAME = "pubs" ' This could be replaced with a loop to script all
DB's
> ' FSO I/O Mode
> Const FORWRITING = 2
> Const FORAPPENDING = 8
> ' DMO Scripting Constants
> Const SQLDMOScript_ObjectPermissions = 2
> Const SQLDMOScript_Default = 4
> Const SQLDMOScript_OwnerQualify = 262144
>
> Dim oStoredProcedure
> Dim oSQLServer
> Dim oDatabase
> Dim oFSO
> Dim sTexttoWrite
> Set oSQLServer = CreateObject("SQLDMO.SQLServer")
> Set oFSO = CreateObject("Scripting.FileSystemObject")
> oSQLServer.LoginSecure = True
> oSQLserver.Connect(SQL_INSTANCE)
> Set oStoredProcedure = CreateObject("SQLDMO.StoredProcedure")
> For Each oStoredProcedure In oSQLServer.Databases(DB_NAME).StoredProcedure
s
> If Not oStoredProcedure.SystemObject Then ' Only Script User Objects
> ' Script the Object Permissions and Owner
> sTexttoWrite =
> oSQLServer.Databases(DB_NAME).StoredProcedures(oStoredProcedure.Name).Scri
pt(SQLDMOScript_Default
> + SQLDMOScript_ObjectPermissions + SQLDMOScript_OwnerQualify)
> oFSO.OpenTextFile(LOG_FILE, FORAPPENDING, True).WriteLine(sTexttoWrite)
> End If
> Next
> oSQLServer.DisConnect
> Set oFSO = Nothing
> Set oStoredProcedure = Nothing
> Set oSQLServer = Nothing
> ~~~
> - Peter Ward
> WARDY IT Solutions
>
> "Joe Delphi" wrote:
>|||Joe
In new groups. If you have a question do post as question. Never select as
comment. Question attract more responses.
Regards
R.D
"R.D" wrote:
> if your problem is only scripting then
> you can use Entrprise Manager> All Tasks > Genrate sql secript
> you can select all sprocs at a time to .sql
> Regards
> R.D
> "P. Ward" wrote:
>|||"R.D" <RD@.discussions.microsoft.com> wrote in message
news:76E5A96A-8011-4A72-9C96-E005877BAF63@.microsoft.com...
> Joe
> In new groups. If you have a question do post as question. Never select as
> comment. Question attract more responses.
> Regards
> R.D
In the English language, this is considered a question:
"Anyone know how I can batch export a set of procs at one time?"|||I think one of the frilly web guis to the newsgroup allows you to categorize
a post as a comment or a question, for some unknown reason.
A
"Joe Delphi" <delphi561@.nospam.cox.net> wrote in message
news:k1yYe.261492$E95.127285@.fed1read01...
> "R.D" <RD@.discussions.microsoft.com> wrote in message
> news:76E5A96A-8011-4A72-9C96-E005877BAF63@.microsoft.com...
> In the English language, this is considered a question:
> "Anyone know how I can batch export a set of procs at one time?"
>
>|||Joe
That is the problem. The people who knows only one language interpret in
terms of that only. NEWS GROUPS HAS LANGUAGE TOO. PLEASE READ HELP TO KNOW
THE DIFFERENCE BETWEEN COMMENT AND QUESTION.
what I meant was when you select new, you select it as a question for which
question mark appears besides your post.
Aaron
I dont expect those words that downgrade the norms of newgroups, from an MV
P.
Better ask MS why there are two varieties(comments and questions)
Regards
R.D
"Aaron Bertrand [SQL Server MVP]" wrote:

> I think one of the frilly web guis to the newsgroup allows you to categori
ze
> a post as a comment or a question, for some unknown reason.
> A
>
> "Joe Delphi" <delphi561@.nospam.cox.net> wrote in message
> news:k1yYe.261492$E95.127285@.fed1read01...
>
>|||On Thu, 22 Sep 2005 23:03:02 -0700, R.D wrote:

>Joe
>That is the problem. The people who knows only one language interpret in
>terms of that only. NEWS GROUPS HAS LANGUAGE TOO. PLEASE READ HELP TO KNOW
>THE DIFFERENCE BETWEEN COMMENT AND QUESTION.
>what I meant was when you select new, you select it as a question for which
>question mark appears besides your post.
>Aaron
>I dont expect those words that downgrade the norms of newgroups, from an M
VP.
>Better ask MS why there are two varieties(comments and questions)
>Regards
>R.D
Hi R.D.,
What you seem to miss is the fact that these groups are actually usenet
groups, which be used in many ways. The oldest form includes the use of
some dedicated software that can access usenet groups. I use Agent for
example; Joe and Aaron appear to use Outlook Express.
Usenet groups have been around for some decades already. They were quite
popular before "Internet" became a hype.
Nowadays, having a "Forum", "Message board", or other "Community" on a
web site is the fashionable thing. Many companies have recognised the
value of those, but also recognise the value and strength of the
existing usenet groups. So they create a website that appears to be
their "own" message board, but that actually is just a mirror of one or
more usenet groups. Microsoft's community pages are an example of such a
portal; others are dbforums.com, examnotes.net, tech-archive.net,
and of course groups.google.com. All posts you read on those web sites
are taken from a usenet group, and all posts you write there are
directly forwarded to the same group.
Some of those web portals decided to add some extra frills and buttons.
If I recall correctly, the Microsoft site has buttons to say if a post
is usefull or not, and some kind of rating for authors. You seem to be
using thhat site; from your comments I understand that they also offer a
way to distinguish "questions" from "comments".
All that is fine for those who use that site. But it's not supported by
the decades-old usenet architecture. So the result is that the MS site
can only present the extra info for posts that originate from their own
site.
If you, like Aaron, me, and a bunch of other "regulars" in these groups,
attempt to keep up with several hundred new messages each day, and reply
to a dozen or more each day then you'll quickly find that the interface
offered by any of those web portals will constantly get in your way.
You'll want a program that allows you to download all new messages, that
will keep track of which messages you have already read and which
discussions you've decided to skip, etc.
If I had to find each message I post at the Microsoft site, only to make
a few mouseclicks to show others if it's a question or a comment (and
then probably do the same on a few other sites as well), that would cost
me so much time that my total contribution to these groups would be
reduced severly. And it would cost me so much energy that I'd probably
stop contributing and find another hobby before the month is over.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo
This is point is still valid.
Regards
R.D
"Hugo Kornelis" wrote:
> On Thu, 22 Sep 2005 23:03:02 -0700, R.D wrote:
>
> Hi R.D.,
> What you seem to miss is the fact that these groups are actually usenet
> groups, which be used in many ways. The oldest form includes the use of
> some dedicated software that can access usenet groups. I use Agent for
> example; Joe and Aaron appear to use Outlook Express.
> Usenet groups have been around for some decades already. They were quite
> popular before "Internet" became a hype.
> Nowadays, having a "Forum", "Message board", or other "Community" on a
> web site is the fashionable thing. Many companies have recognised the
> value of those, but also recognise the value and strength of the
> existing usenet groups. So they create a website that appears to be
> their "own" message board, but that actually is just a mirror of one or
> more usenet groups. Microsoft's community pages are an example of such a
> portal; others are dbforums.com, examnotes.net, tech-archive.net,
> and of course groups.google.com. All posts you read on those web sites
> are taken from a usenet group, and all posts you write there are
> directly forwarded to the same group.
> Some of those web portals decided to add some extra frills and buttons.
> If I recall correctly, the Microsoft site has buttons to say if a post
> is usefull or not, and some kind of rating for authors. You seem to be
> using thhat site; from your comments I understand that they also offer a
> way to distinguish "questions" from "comments".
> All that is fine for those who use that site. But it's not supported by
> the decades-old usenet architecture. So the result is that the MS site
> can only present the extra info for posts that originate from their own
> site.
> If you, like Aaron, me, and a bunch of other "regulars" in these groups,
> attempt to keep up with several hundred new messages each day, and reply
> to a dozen or more each day then you'll quickly find that the interface
> offered by any of those web portals will constantly get in your way.
> You'll want a program that allows you to download all new messages, that
> will keep track of which messages you have already read and which
> discussions you've decided to skip, etc.
> If I had to find each message I post at the Microsoft site, only to make
> a few mouseclicks to show others if it's a question or a comment (and
> then probably do the same on a few other sites as well), that would cost
> me so much time that my total contribution to these groups would be
> reduced severly. And it would cost me so much energy that I'd probably
> stop contributing and find another hobby before the month is over.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>