|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Capturing a query result from a sqlcmd script executiontables, primary keys, column defaults, stored procedures, webpages, DAL, etc. Then, with a batch file, the Build Automation executes test cases on the SQL Server database. It does this, within the batch file, by calling: SQLCMD /Sserver /ddatabase /Q"RunTestCases" After the test cases are executed, I need to know if they all passed or not. I do this with a query. In the database, I have a table, Failures, with a list of failed test cases, if any. SQLCMD /Sserver /ddatabase /Q "EXIT(SELECT COUNT(*) FROM Failures) How do I capture the result from this query? I need to know if the result is 0 (zero) or not. What I do next in the batch file depends on the query result. If the result is 0, then do "this". If the result is > 0, then do "that". Thanks, Michael Bloomfield Consider storing the result of count in a variable within the SQLCMD script.
SQLCMD can then choose what to do, based on the value of the variable. -- Show quoteTom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada t**@cips.ca www.pinpub.com "MGBloomfield" <MGBloomfi***@discussions.microsoft.com> wrote in message news:3F1B9D1F-1793-4CA8-86DF-9AD5FDE63B5C@microsoft.com... > We have a Build Automation process that creates a database, installs all > the > tables, primary keys, column defaults, stored procedures, webpages, DAL, > etc. > > Then, with a batch file, the Build Automation executes test cases on the > SQL > Server database. It does this, within the batch file, by calling: > > SQLCMD /Sserver /ddatabase /Q"RunTestCases" > > After the test cases are executed, I need to know if they all passed or > not. > I do this with a query. In the database, I have a table, Failures, with a > list of failed test cases, if any. > > SQLCMD /Sserver /ddatabase /Q "EXIT(SELECT COUNT(*) FROM Failures) > > How do I capture the result from this query? I need to know if the result > is > 0 (zero) or not. > > What I do next in the batch file depends on the query result. > > If the result is 0, then do "this". If the result is > 0, then do "that". > > Thanks, > Michael Bloomfield > Can you give me an code example?
Show quote "Tom Moreau" wrote: > Consider storing the result of count in a variable within the SQLCMD script. > SQLCMD can then choose what to do, based on the value of the variable. > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA > SQL Server MVP > Columnist, SQL Server Professional > Toronto, ON Canada t**@cips.ca > www.pinpub.com > > "MGBloomfield" <MGBloomfi***@discussions.microsoft.com> wrote in message > news:3F1B9D1F-1793-4CA8-86DF-9AD5FDE63B5C@microsoft.com... > > We have a Build Automation process that creates a database, installs all > > the > > tables, primary keys, column defaults, stored procedures, webpages, DAL, > > etc. > > > > Then, with a batch file, the Build Automation executes test cases on the > > SQL > > Server database. It does this, within the batch file, by calling: > > > > SQLCMD /Sserver /ddatabase /Q"RunTestCases" > > > > After the test cases are executed, I need to know if they all passed or > > not. > > I do this with a query. In the database, I have a table, Failures, with a > > list of failed test cases, if any. > > > > SQLCMD /Sserver /ddatabase /Q "EXIT(SELECT COUNT(*) FROM Failures) > > > > How do I capture the result from this query? I need to know if the result > > is > > 0 (zero) or not. > > > > What I do next in the batch file depends on the query result. > > > > If the result is 0, then do "this". If the result is > 0, then do "that". > > > > Thanks, > > Michael Bloomfield > > > > > What is it that you want to do if a condition is true? What do you want to
do if it is false? -- Show quoteTom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada t**@cips.ca www.pinpub.com "MGBloomfield" <MGBloomfi***@discussions.microsoft.com> wrote in message news:76860F6F-7407-4A0A-9A5D-E2C75AD3ACC8@microsoft.com... > Can you give me an code example? > > > "Tom Moreau" wrote: > >> Consider storing the result of count in a variable within the SQLCMD >> script. >> SQLCMD can then choose what to do, based on the value of the variable. >> >> -- >> Tom >> >> ---------------------------------------------------- >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA >> SQL Server MVP >> Columnist, SQL Server Professional >> Toronto, ON Canada t**@cips.ca >> www.pinpub.com >> >> "MGBloomfield" <MGBloomfi***@discussions.microsoft.com> wrote in message >> news:3F1B9D1F-1793-4CA8-86DF-9AD5FDE63B5C@microsoft.com... >> > We have a Build Automation process that creates a database, installs >> > all >> > the >> > tables, primary keys, column defaults, stored procedures, webpages, >> > DAL, >> > etc. >> > >> > Then, with a batch file, the Build Automation executes test cases on >> > the >> > SQL >> > Server database. It does this, within the batch file, by calling: >> > >> > SQLCMD /Sserver /ddatabase /Q"RunTestCases" >> > >> > After the test cases are executed, I need to know if they all passed or >> > not. >> > I do this with a query. In the database, I have a table, Failures, with >> > a >> > list of failed test cases, if any. >> > >> > SQLCMD /Sserver /ddatabase /Q "EXIT(SELECT COUNT(*) FROM Failures) >> > >> > How do I capture the result from this query? I need to know if the >> > result >> > is >> > 0 (zero) or not. >> > >> > What I do next in the batch file depends on the query result. >> > >> > If the result is 0, then do "this". If the result is > 0, then do >> > "that". >> > >> > Thanks, >> > Michael Bloomfield >> > >> >> >> I meant, code example for this specific part:
"Consider storing the result of count in a variable within the SQLCMD script." For example, this won't work... SET Result=SQLCMD /Sserver /ddatabase /Q "EXIT(SELECT COUNT(*) FROM Failures) How do I store the result of count(*)? Show quote "Tom Moreau" wrote: > What is it that you want to do if a condition is true? What do you want to > do if it is false? > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA > SQL Server MVP > Columnist, SQL Server Professional > Toronto, ON Canada t**@cips.ca > www.pinpub.com > > "MGBloomfield" <MGBloomfi***@discussions.microsoft.com> wrote in message > news:76860F6F-7407-4A0A-9A5D-E2C75AD3ACC8@microsoft.com... > > Can you give me an code example? > > > > > > "Tom Moreau" wrote: > > > >> Consider storing the result of count in a variable within the SQLCMD > >> script. > >> SQLCMD can then choose what to do, based on the value of the variable. > >> > >> -- > >> Tom > >> > >> ---------------------------------------------------- > >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA > >> SQL Server MVP > >> Columnist, SQL Server Professional > >> Toronto, ON Canada t**@cips.ca > >> www.pinpub.com > >> > >> "MGBloomfield" <MGBloomfi***@discussions.microsoft.com> wrote in message > >> news:3F1B9D1F-1793-4CA8-86DF-9AD5FDE63B5C@microsoft.com... > >> > We have a Build Automation process that creates a database, installs > >> > all > >> > the > >> > tables, primary keys, column defaults, stored procedures, webpages, > >> > DAL, > >> > etc. > >> > > >> > Then, with a batch file, the Build Automation executes test cases on > >> > the > >> > SQL > >> > Server database. It does this, within the batch file, by calling: > >> > > >> > SQLCMD /Sserver /ddatabase /Q"RunTestCases" > >> > > >> > After the test cases are executed, I need to know if they all passed or > >> > not. > >> > I do this with a query. In the database, I have a table, Failures, with > >> > a > >> > list of failed test cases, if any. > >> > > >> > SQLCMD /Sserver /ddatabase /Q "EXIT(SELECT COUNT(*) FROM Failures) > >> > > >> > How do I capture the result from this query? I need to know if the > >> > result > >> > is > >> > 0 (zero) or not. > >> > > >> > What I do next in the batch file depends on the query result. > >> > > >> > If the result is 0, then do "this". If the result is > 0, then do > >> > "that". > >> > > >> > Thanks, > >> > Michael Bloomfield > >> > > >> > >> > >> > > > My point is to do these things from within SQLCMD.
-- Show quoteTom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada t**@cips.ca www.pinpub.com "MGBloomfield" <MGBloomfi***@discussions.microsoft.com> wrote in message news:2248739B-1B28-430F-A88B-AA1262E613CA@microsoft.com... >I meant, code example for this specific part: > > "Consider storing the result of count in a variable within the SQLCMD > script." > > For example, this won't work... > > SET Result=SQLCMD /Sserver /ddatabase /Q "EXIT(SELECT COUNT(*) FROM > Failures) > > How do I store the result of count(*)? > > "Tom Moreau" wrote: > >> What is it that you want to do if a condition is true? What do you want >> to >> do if it is false? >> >> -- >> Tom >> >> ---------------------------------------------------- >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA >> SQL Server MVP >> Columnist, SQL Server Professional >> Toronto, ON Canada t**@cips.ca >> www.pinpub.com >> >> "MGBloomfield" <MGBloomfi***@discussions.microsoft.com> wrote in message >> news:76860F6F-7407-4A0A-9A5D-E2C75AD3ACC8@microsoft.com... >> > Can you give me an code example? >> > >> > >> > "Tom Moreau" wrote: >> > >> >> Consider storing the result of count in a variable within the SQLCMD >> >> script. >> >> SQLCMD can then choose what to do, based on the value of the variable. >> >> >> >> -- >> >> Tom >> >> >> >> ---------------------------------------------------- >> >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA >> >> SQL Server MVP >> >> Columnist, SQL Server Professional >> >> Toronto, ON Canada t**@cips.ca >> >> www.pinpub.com >> >> >> >> "MGBloomfield" <MGBloomfi***@discussions.microsoft.com> wrote in >> >> message >> >> news:3F1B9D1F-1793-4CA8-86DF-9AD5FDE63B5C@microsoft.com... >> >> > We have a Build Automation process that creates a database, installs >> >> > all >> >> > the >> >> > tables, primary keys, column defaults, stored procedures, webpages, >> >> > DAL, >> >> > etc. >> >> > >> >> > Then, with a batch file, the Build Automation executes test cases on >> >> > the >> >> > SQL >> >> > Server database. It does this, within the batch file, by calling: >> >> > >> >> > SQLCMD /Sserver /ddatabase /Q"RunTestCases" >> >> > >> >> > After the test cases are executed, I need to know if they all passed >> >> > or >> >> > not. >> >> > I do this with a query. In the database, I have a table, Failures, >> >> > with >> >> > a >> >> > list of failed test cases, if any. >> >> > >> >> > SQLCMD /Sserver /ddatabase /Q "EXIT(SELECT COUNT(*) FROM Failures) >> >> > >> >> > How do I capture the result from this query? I need to know if the >> >> > result >> >> > is >> >> > 0 (zero) or not. >> >> > >> >> > What I do next in the batch file depends on the query result. >> >> > >> >> > If the result is 0, then do "this". If the result is > 0, then do >> >> > "that". >> >> > >> >> > Thanks, >> >> > Michael Bloomfield >> >> > >> >> >> >> >> >> >> >> >>  |
|||||||||||||||||||||||
Other interesting topics