How do you export a stored procedures in SQL server 2000 via command prompt??

Use the osql utility to refer to a file that essentially issues a "sp_helptext" to all stored procedures.
Initially pipe the results of the following to a file:


select distinct 'sp_helptext ' + object_name(so.id) + char(13) + 'GO' from syscomments sc inner join sysobjects so on
so.[id]=sc.[id] where so.xtype='P' and colid=1

and then run the osql utility that points to this input file.

Bryce

not really sure what you mean...
I usally right click on the stored procedure and goto <all tasks> and generate <SQL script>... then a window pops up with all my options etc. on how i want to out put that file to a .sql file.

i need to be able to do that via command prompt.

I think that I just told you exactly how to do this. If you go to Query Analyzer and run the above command you will get within a results pane exaclty the query you need to issue via the OSQL utility. That utility is located in the binn directory of your SQL Server installation. Once the results of the above command are copied to an input file of your choice. You may then issue the following command via a DOS prompt:

osql -E -S Servername -i INPUTFILE -o OUTPUTFILE

Then the output file will contain the DDL for all your stored procedures.

Bryce

yes im trying to us osql to run the command but how would i chouse the stored procedure, that im looking to run?  thanks so much for the help.

my pipe looking somthing like this

sp_helptext sp_MSrestore_sub_merge GO
sp_helptext sp_MSrestore_sub_tran GO
sp_helptext sp_MSretrieve_mergepublication GO
sp_helptext sp_MSretrieve_publication GO
etc..
ect...

then what should i do????

What you just wrote...

sp_helptext sp_MSrestore_sub_merge GO
sp_helptext sp_MSrestore_sub_tran GO
sp_helptext sp_MSretrieve_mergepublication GO
sp_helptext sp_MSretrieve_publication GO
etc..
ect...

Should be contained within your input file, now once you fire OSQL with these arguments you should have your output file housed with the DDL for all your stored procedures. So let us say you transfer the above commands to a file called
c:\input.txt, and your output file is called c:\output.txt, now you fire the OSQL utility in this way:

osql -E -S Servername -i c:\input.txt -o c:\output.txt

Now output.txt should contain all the DDL for your stored procedures.

Bryce

ok so my input.txt file is like this.

sp_helptext dt_addtosourcecontrol GO
sp_helptext dt_addtosourcecontrol_u GO
sp_helptext dt_adduserobject GO
sp_helptext dt_adduserobject_vcs GO
sp_helptext dt_checkinobject GO
sp_helptext dt_checkinobject_u GO
sp_helptext dt_checkoutobject GO

and my output command is empty.

this is the command i run in the command prompt
osql -E -S myserver -i input.txt -o output.txt


and this is my output.txt file after i run the command.....

1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22> 23> 24> 25> 26> 27> 28> 29> 30> 31> 32> 33> 34> 35> 36> 37> 38> 39> 40> 41> 42> 43> 44> 45> 46> 47> 48> 49> 50> 51> 52> 53> 54> 55> 56> 57> 58> 59> 60> 61> 62> 63> 64> 65> 66> 67> 68> 69> 70> 71> 72> 73> 74> 75> 76> 77> 78> 79> 80> 81> 82> 83> 84> 85> 86> 87> 88> 89> 90> 91> 92> 93> 94> 95> 96> 97> 98> 99> 100> 101> 102> 103> 104> 105> 106> 107> 108> 109> 110> 111> 112> 113> 114> 115> 116> 117> 118> 119> 120> 121> 122> 123> 124> 125> 126> 127> 128> 129> 130> 131> 132> 133> 134> 135> 136> 137> 138> 139> 140> 141> 142> 143> 144> 145> 146> 147> 148> 149> 150> 151> 152> 153> 154> 155> 156> 157> 158> 159> 160> Msg 170, Level 15, State 1, Server myserver, Line 1
Line 1: Incorrect syntax near 'GO'.

... am i doing somthing wrong?

No that was correct, no look at the contents of your ouput file, it should contain all the DDL for your stored procedures.

Bryce

Should have been ...NOW look at the contents of your ouput file, it should contain all the DDL for your stored procedures.

my output file contains this.......



1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22> 23> 24> 25> 26> 27> 28> 29> 30> 31> 32> 33> 34> 35> 36> 37> 38> 39> 40> 41> 42> 43> 44> 45> 46> 47> 48> 49> 50> 51> 52> 53> 54> 55> 56> 57> 58> 59> 60> 61> 62> 63> 64> 65> 66> 67> 68> 69> 70> 71> 72> 73> 74> 75> 76> 77> 78> 79> 80> 81> 82> 83> 84> 85> 86> 87> 88> 89> 90> 91> 92> 93> 94> 95> 96> 97> 98> 99> 100> 101> 102> 103> 104> 105> 106> 107> 108> 109> 110> 111> 112> 113> 114> 115> 116> 117> 118> 119> 120> 121> 122> 123> 124> 125> 126> 127> 128> 129> 130> 131> 132> 133> 134> 135> 136> 137> 138> 139> 140> 141> 142> 143> 144> 145> 146> 147> 148> 149> 150> 151> 152> 153> 154> 155> 156> 157> 158> 159> 160> Msg 170, Level 15, State 1, Server myserver, Line 1
Line 1: Incorrect syntax near 'GO'.

.....

Sorry the script should have ticks around the procedure name. Take the original query above and change it to:

select distinct 'sp_helptext ' + '''' + object_name(so.id) + ''''  + char(13) + 'GO' from syscomments sc inner join sysobjects so on
so.[id]=sc.[id] where so.xtype='P' and colid=1

put into the input file and retry.

Bryce

my command is "osql -E -S myserver -i input1.txt -o output.txt"

and my input is ======
sp_helptext 'MS_sqlctrs_users' GO
sp_helptext 'sp_ActiveDirectory_Obj' GO
sp_helptext 'sp_ActiveDirectory_SCP' GO
sp_helptext 'sp_ActiveDirectory_Start' GO
sp_helptext 'sp_add_agent_parameter' GO
sp_helptext 'sp_add_agent_profile' GO
sp_helptext 'sp_add_data_file_recover_suspect_db' GO


now my output file is like ========
1> 2> 3> 4> 5> 6> 7> 8> 9> Msg 170, Level 15, State 1, Server myserver, Line 2
Line 2: Incorrect syntax near 'GO'.

what should i do now?

The SQL given to you should the drop the 'GO' directive to a  new line as indicated by the char(13) within the script I provided you. This is why you are receiving the error. Ensure that each 'GO' is on a separate line.

Bryce

ok iv piped all the sql to my output.txt file

now what?
thanks again for you awsome help!

Well are you getting the DDL for your stored procedures within the output file after the correction?

Bryce

yep,

So that is what you want correct? Your output file is exactly the DDL for the stored procedures you were looking for, or not? The OSQL utility that you ran defaulted to the master database, if you wish to script out the stored procedures for another database you will have to add the "-d" switch to the OSQL utility followed by the database you desire.

Bryce

so what would i do if i only wanted to run say...
<sp_helptext 'sp_ActiveDirectory_Start'>??  which is like 287 on my list....?

You would have to restrict the original query for only that stored procedure:


select distinct 'sp_helptext ' + '''' + object_name(so.id) + ''''  + char(13) + 'GO' from syscomments sc inner join sysobjects so on
so.[id]=sc.[id] where so.xtype='P' and colid=1 and object_name(so.id) = 'sp_ActiveDirectory_Start'

so when i run the sp i would do a

osql -E -S myserver -i d:\myoutputfile.txt

is that right??