Post reply

SQLCMD vs SSMS

  • August 9, 2012 at 11:24 am

    #279446

    Hi,

    We get databse request from developer for data fixes, DML scripts with number of rows going to insert/update/delete

    Currently we are just sending email to developer that script is executed with results having number of rows inserted/updated/deleted using SSMS

    But now, we need to send them the reults with the more details

    database name in which script ran

    User name

    Date and time

    number of rows inserted/updated/deleted

    if the row count matches with dbrequest, then issue COMMIT statement else isuse ROLLBACK statement.

    Can we achive this using SSMS or SQLCMD? Please advise.

    For example, I provide how we do in Oracle from sqlplus.

    Developer provided a scipt called Insert_TTP22.sql

    SQL> @show.sql

    DATE_TIME

    -------------------

    08/09/2012 09:01:37

    USER is "DATAFIX"

    NAME

    ---------

    DB1

    DB1-DATAFIX>

    DB1-DATAFIX>ed Insert_TTP22.sql

    DB1-DATAFIX>@Insert_TTP22.sql

    Total Records inserted 22

    Total Records updated - 0

    Total Records deleted - 0

    PL/SQL procedure successfully completed.

    DB1- DATAFIX>commit;

    Commit complete.

    @show.sql have the below sql statements in it

    contentset sqlprompt"_connect_identifier- _user>"

    @time

    show user

    select name from v$database;

    I tried to execute show.sql file using sqlcmd but I'm getting the below error:

    C:\DBAScripts>sqlcmd -S sql1\ins1 -E

    1> @show

    2> go

    Msg 137, Level 15, State 2, Server SQL1\INS1, Line 1

    Must declare the scalar variable "@show".

    1>

    This show.sql file has below t-sql statements

    select getdate()as DATE_TIME

    select db_name() as NAME

    select SYSTEM_USER as "USER IS"

  • August 9, 2012 at 11:35 am

    #1524000

    Easy enough to do in SSMS but I haven't had much exposure to SQL CMD and running scripted files. Does it have to be done in one or the other specifically?

    Erin

  • August 9, 2012 at 12:04 pm

    #1524014

    Does it have to be done in one or the other specifically

    No. I just wanted to know which tool is the best one (sqlcmd or ssms) to log the script results that I'm looking for.

    How to achieve this in ssms?

    Thanks for your response.

  • August 9, 2012 at 12:44 pm

    #1524042

    Is this something like what you're looking for?

    select getdate() as DATE_TIME

    select DB_NAME() as DB_NAME

    select SYSTEM_USER as Cur_USER

    begin transaction

    [insert statement]

    commit

    select @@ROWCOUNT as InsertedRows

    go

  • August 9, 2012 at 2:44 pm

    #1524127

    how you guys do you datafixes in SQL Server?

Viewing 5 posts - 1 through 4 (of 4 total)

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