Who's On?

	Membership:
	Latest: Saef
	Past 24 Hours: 0
	Prev. 24 Hours: 0
	Overall: 5226

	People Online:
	Visitors: 417
	Members: 0
	Total: 417

Name	Points
Greg Moeller	4184
David Williams	3349
JonA	3291
Kat V	2984
Woozy	1973
Jimmy Chiu	1883
Kwane McNeal	1437
Ragu Raghavan	1372
Roger French	1315
mark.cook	1244

SQL From A Command line in IPA

6 Replies

1 Subscribed to this topic

52 Subscribed to this forum

Sort:

Author

Messages

tambrosi

Veteran Member

Posts: 55

9/22/2015 3:27 PM

Hello, hope somebody can help me out on this. I seen this from another post, but not sure of the commands that need to be execute.

If somebody would be willing to share an example of running sql from a command line, I would sure appreciate it. I am new to IPA and Lawson software.

"You can also run the sql from a command line and provide an option to have the sql output to a csv - actually faster than the iterative approach. Then you can manipulate the csv file from inside the flow."

Thanks

Terry

mikeP

Veteran Member

Posts: 151

9/22/2015 3:45 PM

I haven't done it in IPA, but this is the command I use to reorg the indexes in my WSUS server:

"C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE" -I -i"D:\Data\WSUS\WsusDBMaintenance.sql" -S np:\\.\pipe\MICROSOFT##WID\tsql\query

You can see where it's passed the *.sql file containing the query. I think you just need to figure out the command line options for SQLCMD.EXE, then redirect the output to a file using a ">".

SQLCMD.EXE needs to be installed on your Landmark server, I think I got mine as part of SQL Server management studio.

Tim Cochrane

Veteran Member

Posts: 154

9/22/2015 4:30 PM

Why not use the SQL node in Designer and have the iterative logic within the node?? Seems like you're adding an extra step by running from the command line, output to a CSV...THEN iterating thru the CSV

ttredwell

Basic Member

Posts: 9

9/22/2015 6:40 PM

I agree with Tim. That is how we would approach this one.

tambrosi

Veteran Member

Posts: 55

9/23/2015 10:14 AM

I agree I would like to use the SQL Node, but when I tested the SQL Node, here is what I found out in timings to process 6600 records. With the SQL Node after approx 20 minutes I processed about 1800 records of the 6600. I switched to the SQLCMD.exe process and I was able to process all 6600 records in less than 5 seconds. I ran both tests off hours and a couple of times with the same results.
Maybe the question I need to ask, is how to speed up the SQL Node Process so it can get close to the processing times of the SQLCMD.
Thanks
Terry

John Henley

Posts: 3353

9/23/2015 11:02 AM

You do have to be careful with SQL query node and large result sets. Pay particular attention to the checkbox about variables outside of the loop, as a JavaScript variable can be returned for every column of every row. also be careful what you're doing in the loop. If you're doing file access to LSF server and IPA is on a different server, you're round-tripping with each loop iteration

Tim Cochrane

Veteran Member

Posts: 154

9/23/2015 11:58 AM

as John was saying - I never use the "Enable results outside loop" option. I guess it depends on the processing your performing on each record found.
as an example - one of my flows that uses the SQLQRY will read ~50 records daily, and will take 5-6 minutes for the work unit to complete. The query itself took <1 second to complete...HOWEVER:
1. i also make a System Command call, using a VB script, to AD to get some info on the employee
2. make 3 Landmark queries and 1 LS query on each employee
3. trigger a work object if criteria is met on the employee
4. build an xml file that "loadusers" calls in another SYSCMD node

SO, while the SQLQRY took <1 second, ALL the processing took a bit longer