1. Home
  2. Point-Of-Sale Software
  3. Automate SQL Script

Automate SQL Script

Can somebody tell me how I can convert/write the below SQL script in a Transact SQL script so I can use the Windows task scheduler to automatically run the below SQL script from a batch file every so often? (It copies the matrix attribute to the Item subdescription fields, since MS broke this functionality with their 2.0 upgrade and is STILL working on a solution for this problem):

update item set subdescription1 = b.detail1 from item a, itemclasscomponent b where ((a.id = b.itemid) and (subdescription1 b.detail1))

update item set subdescription2 = b.detail2 from item a, itemclasscomponent b where ((a.id = b.itemid) and (subdescription2 b.detail2))

update item set subdescription3 = b.detail3 from item a, itemclasscomponent b where ((a.id = b.itemid) and (subdescription3 b.detail3))

Reply to
Alex
Loading thread data ...

BTW I am using SQL 2005 express.

Reply to
Alex

You could create two files (1) test.txt containing your update command lines and (2) test.cmd containing the following: osql -S server -d database -U user -P password < test.txt

Reply to
DC

Hi Alex, I tested the following batch file and it appears to work consistently - copy and paste this to a notepad, then save it with file extension of .bat in a local drive. It fills in subdescriptions 1 thru 3 with corresponding entries in details 1 thru 3 in the itemclasscomponent table.

osql -U sa -P YOURSAPASSWORDHERE -d YOURDATABASENAMEHERE-Q "UPDATE ITEM SET SUBDESCRIPTION1 = B.DETAIL1, SUBDESCRIPTION2 = B.DETAIL2, SUBDESCRIPTION3 B.DETAIL3 FROM ITEM A, ITEMCLASSCOMPONENT B WHERE ((A.ID = B.ITEMID) AND (SUBDESCRIPTION1 B.DETAIL1))"

Make sure reta> Can somebody tell me how I can convert/write the below SQL script in a

Reply to
convoluted

Thank you, this seems to run fine. Although, I'm not really sure why I get some strange output lines see below(besides the rows affected statements). Are these in reference to line numbers?

1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> (0 rows affected) (0 rows affected) (0 rows affected)

Reply to
Alex

Thank you for the reply and solution, but, I think I'll use DC's suggestion (by inputting the SQL from a batch file) since this will keep things a little cleaner.

Reply to
Alex

BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.