Extended Stored Procedure problem

Hi,
I made some extended strored procedures
and they goes well when they are placed at
c:\windows\system32 or the same derectory to
sqlservr.exe which is usualy in C:\Program Files\
Microsoft SQL Server\MSSQL\Binn.

I thought it's OK wherever they are placed
as long as I put them to the system path.

But actually they didn't work at all
when they were placed to the other path
that I had newly added as a system path.

Here is the contents of 'path' valiable:

%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\Sy stem32\Wbem;C:\WINDOWS\System32\AmiClick;C:\Progra m Files\Microsoft SQL Server\80\Tools\BINN:C:\Program Files/INC

I just added only "C:\Program Files/INC",
placed the extended stored procedures there,
then called them through a trigger.

Can Somebody tell me what was wrong?
Please help me if you have any informations about this issue.

regards,
Takanobu MaekawaKeep in mind that the forward slash causes all kinds of problems in the NT environment. I would very strongly recommend that you change it to a backslash, which is the NT default path element separator.

-PatP|||Thanks.

I've fixed it now as you said and tried again,
but the problem remains. mm..why??|||Have you enabled the path on SQL server and from where you;re calling this XP.|||Thanks for reply, but I couldn't understand what you meant ...

I just want to put the Extended SPs into the place except system32
or bin directory of SQL Server or the other system directory,
and then run them from the remote hosts.

well,So far,I wonder the problem might be that
the SQL Server uses local system account.(I don't want to change it)

I guess every thing would be OK if I can put the path which I want to add to the local system account's path...but how??
Isn't the system path shared with the local system acount's one?
I'm very confused.

Is there some one who had a problem like me and solved it?
If you know any informations about this issue,please tell me too.

regards.|||ENsure the path is set on the server not on the local client machine.|||Originally posted by Satya
ENsure the path is set on the server not on the local client machine.

Yes, I'm sure that the path is set on the server's system path, not the client's one ...|||Can you post the error generated while working with this XP.|||well, here is the result of running one of XPs
through the Query Analyzer.

----------------------
<R_DB_T_KANJA>Jn 03 17 2004 10:36AM
R_MQ_T_BEDKANJALINK Jn 17 03 2004 10:36:27:390
R_MQ_T_BEDKANJALINK I 17 03 2004 10:36:27:390
R_MQ_T_BEDKANJALINK Jn 17 03 2004 10:36:27:403
R_MQ_T_BEDKANJALINK I 17 03 2004 10:36:27:403
R_MQ_T_BEDKANJALINK Jn 17 03 2004 10:36:27:403
R_MQ_T_BEDKANJALINK I 17 03 2004 10:36:27:403
R_MQ_T_BEDKANJALINK Jn 17 03 2004 10:36:27:403
R_MQ_T_BEDKANJALINK I 17 03 2004 10:36:27:403
R_MQ_T_BEDKANJALINK Jn 17 03 2004 10:36:27:403
R_MQ_T_BEDKANJALINK I 17 03 2004 10:36:27:403
R_MQ_T_BEDKANJALINK Jn 17 03 2004 10:36:27:403
R_MQ_T_BEDKANJALINK I 17 03 2004 10:36:27:403
<R_DB_T_KANJA>I 03 17 2004 10:36AM
<R_DB_T_KANJA2>I 03 17 2004 10:36AM
R_MQ_T_KANJA<UPDATE> Jn 17 03 2004 10:36:27:967
ODBC: bZ[W 0Ax 16A 1
DLL INC_MNSQL.dll [h??AQ?? DLL 1 ?[h??BR : 126(w肳?W[??B)B

R_MQ_T_KANJA<UPDATE> @.lSeqNum=1 @.nByotoCode=21
R_MQ_T_KANJA<UPDATE> (?ID 1005)XV?B @.Old_bytSex=1 @.New_bytSex =0 (NULL -> 0)

(1 ?)
R_MQ_T_KANJA<UPDATE> I 17 03 2004 10:36:28:060
------------------------

As you see, the localization of SQL Server is set to Japanese.
Should I translate the messages into English?

'R_DB_ ...' are output of print function in triggers.
XP is called from them.
The DLL the XP is written is INC_MNSQL.dll.
The error said the DLL can not be loaded.|||Based upon the last link of your reply, I feel this KBA http://support.microsoft.com/default.aspx?scid=kb;en-us;Q151596 may be suitable to resolve.

HTH|||Can you put the XP DLL into the SQL binary directory (by default, C:\Program Files\Microsoft SQL Server\Mssql\Binn although that can be changed at installation time) and have them work correctly? Did you by any chance specify a pathname when you called sp_addextendedproc (which is a bad thing)?

-PatP|||Finally, I've solved this problem.
I don't know this is the essential solution for this problem,
but today, once I rebooted the Server machine
(it's been long time --at least 2 months since I rebooted the server machine last time),then the system path which I added began to work well !!

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