June 3, 2011 at 9:35 am
Guys,
I am trying to use SQLCMD to run the scripts, however I am facing couple issues
1. The log file generated is not working for mass update where 1mill rows are updated i.e the entry is not being registered in the log file
The test.log from the below shows 2 entries corresponding to the 2 insert statements from the test.sql file, however the update statement which effected >
1 mill rows is not getting logged even though the update completed successfully
2. How do we make to print the statement with corresponding result in the log file as supposed to only the result.
From below example the current test.log output is
(1 rows affected)
(1 rows affected)
Can it be changed to incorporate corresponding command in the log file
INSERT INTO TEST (ID) VALUES (1)
(1 rows affected)
INSERT INTO TEST (ID) VALUES (2)
(1 rows affected)
DECLARE @cmd varchar(2000)
SET @CMD = 'SQLcmd -S SQLINSTANCE -U build_user -P @ccess4U -d TEST -i "\\MC\shares\test.sql" -W >> \\MC\shares\test.log'
EXEC master.dbo.xp_cmdShell @cmd
TEST.SQL
update [test1mill]
set date_time_mod = getdate()
go
SET IDENTITY_INSERT TEST ON
GO
INSERT INTO TEST (ID) VALUES (1)
GO
INSERT INTO TEST (ID) VALUES (2)
GO
SET IDENTITY_INSERT TEST OFF
GO
SET NOCOUNT OFF
TEST.LOG
(1 rows affected)
(1 rows affected)
Any suggestions/inputs would help.
Thanks
June 3, 2011 at 12:29 pm
Use the -e parameter of sqlcmd.
As an aside...why are you compelled to call sqlcmd from within a T-SQL process using xp_cmdshell? You generally better leaving the default of having xp_cmdshell disabled on your instances and calling sqlcmd from a SQL Agent Job, a PowerShell script or some other programming context besides T-SQL.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 3, 2011 at 1:01 pm
Thanks for the reply
-e command worked for the question#1, however for question 1 if the update statement takes more than a min to run, it does not log the number of rows effected by the update. Is there any way round it, tried -t 360 but doesn't seem to help.
Any suggestions/inputs would help.
Also this snippet is run every night via a SQL Job.
Thanks
June 3, 2011 at 1:19 pm
am-244616 (6/3/2011)
Thanks for the reply-e command worked for the question#1,
Excellent!
however for question 1 if the update statement takes more than a min to run, it does not log the number of rows effected by the update. Is there any way round it, tried -t 360 but doesn't seem to help.
Are you positive it only happens when it takes longer than a minute? Is it possible there is a SET NOCOUNT ON somewhere in your code?
Also this snippet is run every night via a SQL Job.
If it's run from a SQL job then why you do not need xp_cmdshell. You can use a CmdExec Agent Job step and call sqlcmd directly rather than using a T-SQL step to call sqlcmd via xp_cmdshell.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply