sqlcmd not returning anything
-
viernes, 04 de mayo de 2012 20:58
Hi,
I am having problems with sqlcmd not returning anything. Can anyone explain this?
I run the following command from command prompt (as you can see it doesn't return anything to indicate success or failure):
C:\scripts\image-priv-upds>sqlcmd -l 1200 -S soundwave -E -i upd-img-priv-ids.sql -m-1 -t 12000 -r1
C:\scripts\image-priv-upds>
here is where it gets weird....if i make a minor adjustment in the input file (upd-img-priv-ids.sql) to qualify the record set to a smaller scope, it actually returns output:
C:\scripts\image-priv-upds>sqlcmd -l 1200 -S soundwave -E -i upd-img-priv-ids.sql -m-1 -t 12000
(0 rows affected)
C:\scripts\image-priv-upds>
- Editado daj3016 viernes, 04 de mayo de 2012 21:09
Todas las respuestas
-
viernes, 04 de mayo de 2012 21:44
C:\scripts\image-priv-upds>sqlcmd -l 1200 -S soundwave -E -i upd-img-priv-ids.sql -m-1 -t 12000 -r1
C:\scripts\image-priv-upds>sqlcmd -l 1200 -S soundwave -E -i upd-img-priv-ids.sql -m-1 -t 12000(0 rows affected)
C:\scripts\image-priv-upds>
please start sqlcmd with "-?" as the only option and read the description of -r.
if you specify -r 1 on the command line when executing sqlcmd the output is redirect to stderr instead of stdout.
you can get the output when calling with -r 1 if you redirect the stderr to a file and type the file.
C:\scripts\image-priv-upds>sqlcmd -l 1200 -S soundwave -E -i upd-img-priv-ids.sql -m-1 -t 12000 -r1 2> errors.txt
C:\scripts\image-priv-upds>type errors.txt
Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
- Propuesto como respuesta Aalam Rangi sábado, 05 de mayo de 2012 5:50
- Marcado como respuesta Kalman TothMicrosoft Community Contributor, Editor miércoles, 16 de mayo de 2012 0:29
- Desmarcado como respuesta daj3016 viernes, 03 de agosto de 2012 18:47
- Votado como útil daj3016 viernes, 03 de agosto de 2012 18:47
-
lunes, 07 de mayo de 2012 15:16
C:\scripts\image-priv-upds>sqlcmd -l 1200 -S soundwave -E -i upd-img-priv-ids.sql -m-1 -t 12000 -r1
C:\scripts\image-priv-upds>sqlcmd -l 1200 -S soundwave -E -i upd-img-priv-ids.sql -m-1 -t 12000(0 rows affected)
C:\scripts\image-priv-upds>
please start sqlcmd with "-?" as the only option and read the description of -r.
if you specify -r 1 on the command line when executing sqlcmd the output is redirect to stderr instead of stdout.
you can get the output when calling with -r 1 if you redirect the stderr to a file and type the file.
C:\scripts\image-priv-upds>sqlcmd -l 1200 -S soundwave -E -i upd-img-priv-ids.sql -m-1 -t 12000 -r1 2> errors.txt
C:\scripts\image-priv-upds>type errors.txt
Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
Thanks for your response Mark....
actually, I experienced (and am still experiencing) this problem without the "r" switch at all.....I was experimenting with it and unfortunately i screen-copied the wrong line for the post...sorry for the confusion.
so, just to clarify:
before narrowing the scope in my sql file:
C:\scripts\image-priv-upds>sqlcmd -l 1200 -S soundwave -E -i upd-img-priv-ids.sql -m-1 -t 12000
C:\scripts\image-priv-upds>
After narrowing the scope in my sql file:
C:\scripts\image-priv-upds>sqlcmd -l 1200 -S soundwave -E -i upd-img-priv-ids.sql -m-1 -t 12000
(0 rows affected)
- Editado daj3016 lunes, 07 de mayo de 2012 15:20
-
lunes, 07 de mayo de 2012 17:14
for me the two line looks identical.
could you provide the sql script upd-img-priv-ids.sql ?
does the script contains a line with SET NOCOUNT ON which gets except sometimes but not always or executing the script twice generates different output ?
please try the same script from within a query windows in SQL Server Management Studio and look for difference in the output window - do you see the same difference ?
Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
-
lunes, 07 de mayo de 2012 18:03
yes, the 2 lines are identical.
here is the sql command (sqlcmd will show the output for this):
update FACC_CLERICUS.dbo.docket
set image_privacy_ID = 1
where image_filename in
(
select image_filename
from netdms.dbo.HERN_CSS_DOCUMENT
where ISN in
(
SELECT distinct rtrim(ltrim(left(c.ImagePath, (patindex('%,%', c.ImagePath)-1))))
FROM facc_aiControl_historical.dbo.WorkItems a, facc_aiControl_historical.dbo.WorkItemHistory b, facc_mentis_civil_historical.dbo.MDocPage c, facc_mentis_civil_historical.dbo.MDoc d
WHERE c.dociden = d.DocIden
and d.dStatus = 2500
and d.DocIden = a.DocIden
and a.DocIden is not null
and a.JobId = 11
and a.WorkItemId = b.WorkItemId
and b.[Time] between DATEADD(HOUR, -24, GETDATE()) and GETDATE()
and b.Status = 3
)
)
and image_privacy_ID = 2
and image_filename <> ''The problem is that this is not updating everything that i need to update.....if I change this line:
and b.[Time] between DATEADD(HOUR, -24, GETDATE()) and GETDATE()
to:
and b.[Time] < GETDATE()
sqlcmd doesn't report anything.
If i run the sql in ssms they both are successful and report the exact same thing (0 rows).
-
martes, 08 de mayo de 2012 6:15
you're updating the field image_privacy_ID which is used in the WHERE clause to find the records. As such when any records are found at the first execution of the statement they will be update image_privcacy_ID = 1 and running the script a 2nd time will found not additional records resulting in the message (0 records found).
The other question I've is: how is the column [Time] declared ? is it a datetime, smalldatetime or a newer type date, time etc.?
Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
-
martes, 08 de mayo de 2012 19:59
you're updating the field image_privacy_ID which is used in the WHERE clause to find the records. As such when any records are found at the first execution of the statement they will be update image_privcacy_ID = 1 and running the script a 2nd time will found not additional records resulting in the message (0 records found).
Thats correct, but irregardless sqlcmd should report (0 records found)....and it doesn't when i am qualifying with "and b.[Time] < GETDATE()". If i qualify with "and b.[Time] between DATEADD(HOUR, -24, GETDATE()) and GETDATE()", sqlcmd reports (0 records found) everytime.....makes absolutely no sense....
Just in case you're wondering why it matters to me so much....it is because I'm running this from a scheduled task and I really need that output for auditing purposes....
-
martes, 08 de mayo de 2012 20:02
it declared as datetimeThe other question I've is: how is the column [Time] declared ? is it a datetime, smalldatetime or a newer type date, time etc.?
-
viernes, 03 de agosto de 2012 18:49
Just in case some other poor soul ever has this problem....i never could find out why sqlcmd behaves this way....however as a work around i added the following to the end of my .sql and it would list the number of records updated (which was my goal):
SELECT @@ROWCOUNT as "Records Updated"
go- Marcado como respuesta daj3016 viernes, 03 de agosto de 2012 18:49
.png)
