task => generate script with tool sqlCmd

Question

task => generate script with tool sqlCmd

christophe.bernard 47659

Ten Centuries

Points: 1169
More actions
July 12, 2017 at 6:16 am

#343470

Hello all,
i would like to know if it's possible to generate a script (create schema and datas) with the command line ?
because every time i try to generate a script by these step i receive an error :
- right click on db
- select task
- generate script
- next
- i select 'select specific database objects'
- click next
- click advanced => select schema and data in row types of data to script
- file name => path/script.sql
every time i receive and error but without any description ...
thanks for your time

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

I've seen issues like this in the GUI scripting; when you use an older version of SQL Server Management Studio(lets say 2008 or 2012) and are connecting to a higher version of SQL(say 2012/14/16)
the issue is the version of Server Management Objects being used;
what specific version of SQL Server Management Studio are you using? what SLQ engine are you connecting to?
grabbing the latest version for SSMS(17.15 i think, or higher) should help. it;s downwards compatible.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 2

Just a suggestion, don't use the GUI. Microsoft has a new command line utility that does a pretty good job.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004410 More actions · Answer 3

Grant Fritchey - Wednesday, July 12, 2017 7:13 AM
Just a suggestion, don't use the GUI. Microsoft has a new command line utility that does a pretty good job.

The need to generate scripts isn't an uncommon one. Since MS does it fairly well through the GUI, you would think they'd make it easy to do via a stored procedure and/or the CLI. It's currently pretty easy to do with coded objects but there's nothing so simple for tables (as an example, but you knew that). We shouldn't have to load a bunch of 3rd party stuff to do what should be built in.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 4

Jeff Moden - Wednesday, July 12, 2017 8:10 AM
Grant Fritchey - Wednesday, July 12, 2017 7:13 AM
Just a suggestion, don't use the GUI. Microsoft has a new command line utility that does a pretty good job.
The need to generate scripts isn't an uncommon one. Since MS does it fairly well through the GUI, you would think they'd make it easy to do via a stored procedure and/or the CLI. It's currently pretty easy to do with coded objects but there's nothing so simple for tables (as an example, but you knew that). We shouldn't have to load a bunch of 3rd party stuff to do what should be built in.

Agreed. That's why when I really need it I use PowerShell and an ADO command to do it. Works great. No extra installs needed.

Well, or I use a Redgate tool. 😛

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004410 More actions · Answer 5

Grant Fritchey - Wednesday, July 12, 2017 8:52 AM
Jeff Moden - Wednesday, July 12, 2017 8:10 AM
Grant Fritchey - Wednesday, July 12, 2017 7:13 AM
Just a suggestion, don't use the GUI. Microsoft has a new command line utility that does a pretty good job.
The need to generate scripts isn't an uncommon one. Since MS does it fairly well through the GUI, you would think they'd make it easy to do via a stored procedure and/or the CLI. It's currently pretty easy to do with coded objects but there's nothing so simple for tables (as an example, but you knew that). We shouldn't have to load a bunch of 3rd party stuff to do what should be built in.
Agreed. That's why when I really need it I use PowerShell and an ADO command to do it. Works great. No extra installs needed.
Well, or I use a Redgate tool. 😛

Oooooohhh... Any chance of you posting the Powershell that you have for that?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 6

Jeff Moden - Wednesday, July 12, 2017 9:05 AM
Oooooohhh... Any chance of you posting the Powershell that you have for that?

In a small panic because I can't find my script, however, here's a very good example. It's not hard.

Also, I said ADO and I meant SMO. My new brain is on back-order I think.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004410 More actions · Answer 7

Grant Fritchey - Wednesday, July 12, 2017 9:57 AM
Jeff Moden - Wednesday, July 12, 2017 9:05 AM
Oooooohhh... Any chance of you posting the Powershell that you have for that?
In a small panic because I can't find my script, however, here's a very good example. It's not hard.
Also, I said ADO and I meant SMO. My new brain is on back-order I think.

Thanks, Grant. I'll give it a shot.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

christophe.bernard 47659 Ten Centuries Points: 1169 More actions · Answer 8

Hello all,
thanks for your time and your knowledge that's really great to share it ...
sorry for the delay ...little problem at the moment 🙂

@lowell => we use actually sql server management in version 11.0.300.0 and the sql server version is 2012
i'm not sure if i can update the sql server management because this machine is a production server .. but i will ask it

@grand fritchey => thanks for the link i will try it

thanks