August 9, 2012 at 11:24 am
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
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
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
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
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