storing sql script in database table column ?

• mpradeep23

  SSCommitted

  Points: 1868

  More actions

  January 31, 2013 at 3:39 am

  #270169

  hi

  i want to create database by calling stored procedure

  i am able create database using sql script in file path and by using sql cmd and xpcmdshell

  but i want save the script in the database table and exec with sql cmd

  can any one spcify the syntax for sqlcmd with script in variable

  thanks

  with best regards

  pradeep

• Orlando Colamatteo

  SSC Guru

  Points: 182279

  More actions

  January 31, 2013 at 4:34 am

  #1582873

  If you already have a stored proc that can create a database using dynamic sql then why would you store any code in a table (bad idea in general) and why are you involving xp_cmdshell and sqlcmd from within a proc (also a bad idea in general)? You're mixing all kinds of technologies and saying you want to cross several programming boundaries in a single process but it is unclear what that would buy you. What are you really trying to do?

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• mpradeep23

  SSCommitted

  Points: 1868

  More actions

  January 31, 2013 at 5:17 am

  #1582897

  hi

  i have to create database dynimcally by calling a stored procedure

  for that i made 3 scripts 1.for database creation passing dbname

  2.tables creation 3.sp creation

  i am using sqlcmd and xpcmd_shell to exec that in sp

  now i am thinking to store this scripts in a table column get it into parameter and exec using sqlcmd

  give me the idea as i am new to this

  with regards

  pradeep

• Orlando Colamatteo

  SSC Guru

  Points: 182279

  More actions

  January 31, 2013 at 6:24 am

  #1582948

  I am still not seeing a need to use xp_cmdshell or sqlcmd.

  The stored procedure can execute the code dynamically using EXEC().

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• mpradeep23

  SSCommitted

  Points: 1868

  More actions

  January 31, 2013 at 9:07 am

  #1583057

  hi

  need to pass database name dynamically it is giving errors

  with best regards

  pradeep

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  January 31, 2013 at 9:13 am

  #1583066

  mpradeep23 (1/31/2013)

  ---

  hi

  need to pass database name dynamically it is giving errors

  with best regards

  pradeep

  You need to provide a little more information than that. We can't see you screen from here and simply saying it didn't work does not provide any information to go on. What error message did you get?

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/