Question about sqlcmd - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Question about sqlcmd

Author

Topic

priyabala
Starting Member

6 Posts

Posted - 2010-09-07 : 11:41:12

Hi,

I'm pretty new to sqlcmd. I'm trying to write a command script using sqlcmd.Th script should connect to a remote server,connect to a database on the server and read a value from one of it's table's fields.

I tried
sqlcmd -U"%SQLLOGIN%" -P"%SQLPWD%" -S %SQLSERVER% -d "%SQLDBDATA%" -Q "SELECT complte_flg from dbo.Data_Load"

The question is how to get the value of "complte_flg" field. We need this value to start other jobs in the queue.

Please advise.

Thanks.

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-07 : 14:54:17

What other "Jobs"?

By the SQL Server Agent?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

priyabala
Starting Member

6 Posts

Posted - 2010-09-07 : 15:10:54

I meant other batch jobs in the queue.

Is there a way to get the output from a query using a variable.
Can we do this.Would this work? please let me know.

SET MyVar="SELECT top 1 complte_flg from dbo.Data_Load order by id desc"
echo "%MyVar%"

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-07 : 15:25:07

what langauage/platform are you doing this from ?

Can you call a stored procedure?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

priyabala
Starting Member

6 Posts

Posted - 2010-09-07 : 15:30:41

We use windows command script (.cmd file).Unfortunately we cannot create any stored procedures for this purpose.We are trying to accomplish this by re-directing the query output to a variable and then print out the variable's value.

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-07 : 15:54:15

hey...comman line junkie here

Still...I think it's a blood from a stone kind of thing...

You could probably redirect to a file...but to a command script variable??

Don't see how...unless you try and build another executable as a file

That might work

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-09-07 : 16:28:49

Here are a few options:

-- redirect to file (myfile.txt) using >
sqlcmd -U"%SQLLOGIN%" -P"%SQLPWD%" -S %SQLSERVER% -d "%SQLDBDATA%" -h-1 -W -Q"set nocount on;SELECT complte_flg from dbo.Data_Load" >myfile.txt

-- redirect to file (myfile.txt) using -o parameter
sqlcmd -U"%SQLLOGIN%" -P"%SQLPWD%" -S %SQLSERVER% -d "%SQLDBDATA%" -h-1 -W -Q"set nocount on;SELECT complte_flg from dbo.Data_Load" -omyfile.txt

-- capture output in local variable and echo
for /F "tokens=1 delims=" %a in ('sqlcmd -U"%SQLLOGIN%" -P"%SQLPWD%" -S %SQLSERVER% -d "%SQLDBDATA%" -h-1 -W -Q"set nocount on;SELECT complte_flg from dbo.Data_Load"') do @echo %a

-- capture output in local variable and assign to environment variable (myVar)
for /F "tokens=1 delims=" %a in ('sqlcmd -U"%SQLLOGIN%" -P"%SQLPWD%" -S %SQLSERVER% -d "%SQLDBDATA%" -h-1 -W -Q"set nocount on;SELECT complte_flg from dbo.Data_Load"') do @set myVar=%a

Make sure to have SET NOCOUNT ON in your SQL command, and include the -h-1 and -W flags to suppress column headings and extra white space.

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-07 : 16:36:49

Bookmarked

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

priyabala
Starting Member

6 Posts

Posted - 2010-09-07 : 17:03:35

Thanks Guys.....I'll try and let you know.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources