SQL CMD and Scripting

Hot Topic

TDSSNIClient initialization failed with error 0x7e
Profiler in April CTP
how to use CASE or IF in SSIS
TargetServerURL for named instance?
Business Scorecard Integration with SQL server 2000/2005
.net StoredProc
Service Pack 1 Database Services Installation Failure
triggercontext.columncount returns wrong values
Database is read-only.
SQL Server Reporting Services Installation - Urgent

SQL Server

metadata driven SSIS package--table variable
Managment Studio Scripting options?
Index was outside the bounds of the array
How to set data regions to have a fixed height
Managemnt Studio - Extensible?
Package Configurations not so portable?
User Instance=True causes invalid connection string
remote connection error SQL2005
to get the latest price value
creating a database

SQL CMD and Scripting

Hi Gurus/MVPS:

Can you please share or show me the code for scripting out stored procedures in SQL Server 2005 using SQL Cmd . I need to perform the following:

1). Script out the text of the stored procedure

2). Output to a sql file (text file) under some directory like C:\sql

3). Import the file into the destination db and run it.

The above three need to be automated in a DTS Package. I tried to use the DMO but my source server is a SQL2k5 and so DTS desginer does not let me use the ActiveX with DMO against SQL 2k5. I need to run the first step against a SQL2k5 machine and the second and third on a SQL2k machine.

Please help!!!.

Thanks

Ankith

Answer this question

SQL CMD and Scripting

  • Bar?? Soner U?akl?

    You cannot use SQLCMD for this task. It doesn't have any functionality that will help directly in doing this. For simpler one off stuff, you can use SQLCMD to output result of PRINT OBJECT_DEFINITION(OBJECT_ID('yoursp')) or sp_helptext 'yoursp' to a file and run that against destination server. If you want to automate for lot of objects and script different types of objects then you will have to use SMO. For the SSIS question, could you please post it in the SQL Server Integration Services forum here. Folks who frequent that forum will be able to help you with the SSIS part.

  • SQL CMD and Scripting
Answer this question