Formatting Question With Osql Batch File
Jul 23, 2005
Hi all,
This is my first batch file and I want to query a database and output
it in a textfile. Up to now that works, the only problem is the
formatting in the text file. It's all screewed up...lines aren't lined
up and columns aren't right....how do you format the result from a
query to a text file. Here's my code:
osql.exe -S MYMACHINE -w 30 -E -d Demo2 -Q "select * from Title where
frequency = 'monthly'" -o "C:output.txt"
Thanks....
JMT
View 1 Replies
ADVERTISEMENT
Aug 22, 2006
I have a scheduled task that runs a batch file in windows 2003 server which
is running SQL Server 2000 Enterprise.
The batch file contains the line:
OSQL -i myscript.sql -Umyuser -Pmypassword -Slocalhost -o mytrans.log
the 'myscript.sql' file updates a table with another table of the exact
number of rows.
When the scheduled task runs, the 'mytrans.log' file shows 0 rows were
affected.
If I run this myself from the command line, 22,000 rows are affected which
is correct.
I am guessing there is some sort of permission/authentication issue here. I
am sending the right username/password for SQL and for windows to run the
task.
Any idea what I could do to fix this?
Thanks,
Nitrox
View 1 Replies
View Related
Sep 13, 2006
Hello
I have a script ,which runs with osql
The script is :
osql -E
declare @cmd nvarchar(1000)
declare @cmd2 nvarchar(1000)
declare @state1 varchar(100)
declare @message varchar(100)
set @message = ''
-- Build command to determine state of SQLSERVERAGENT service on Master Server
SET @CMD = 'create table #state (state varchar(2000))' + char(10) +
'declare @cmdx varchar(1000)' + char(10) +
'insert into #state EXEC master..xp_servicecontrol ''''QueryState'''', ''''SQLSERVERAGENT''''' +
+ char(10) + 'select @state=state from #state' + char(10) +
'drop table #state'
-- Build command to execute command that determines state of service being monitored
set @cmd2 = 'declare @state varchar(100)' + char(10) +
'exec ' + rtrim(@@servername) + '.master.dbo.sp_executesql N''' + @CMD + ''',' +
'N''@state varchar(100) out'',' +
'@state out' + char(10) +
'set @state1 = @state'
-- Execute command and return state of service being monitored
exec master.dbo.sp_executesql @cmd2,N'@state1 varchar(100) out',@state1 out
-- Is the service that was monitored not
IF (UPPER(@state1) <> 'RUNNING.')
--if @state1 <1 'Running.'
begin
-- Display message that primary monitor is down
select @message = @message+char(13)+ @@servername + ' -' + 'Sql Server Agent Not Running'+char(13)
print 'Master server "' + rtrim(@@servername) + '" for monitoring is not available.'
exec master.dbo.xp_smtp_sendmail
It works fine when I run it on the command line prompt.
And I receive a mail , if the server agent is running.
But when I save it as bat file and try to run , it stops and doesnot even give an error.
Can anyone let me know what I can do.
Thanks
View 1 Replies
View Related
Jul 20, 2007
I've written a small dos batch script that runs a simple query throughosql and then calls an exe which sends an email if the returned value
Quote:
View 2 Replies
View Related
May 5, 2004
At first I'd like to justify myself - I'm rather a greenhorn in MS SQL ;-)
What I'd like to do is to create a very simple spool file from my database.
I'd like to do this using OSQL tool and it's very important for me to have the spool file in the same format as it was earlier when I used Oracle and simple spool command.
And it's almost the same, but white spaces...
There's always one white space at the begining of each line and at least one at the and of each line.
I've been trying many OSQL switches and ltrim(..) / rtrim(..) functions but the problem still exists...
The spool file is rather big - around 300MB so it's not good idea to use SED (for example) to remove white spaces.
So my question:
IS IT POSSIBLE TO REMOVE WHITE SPACES COMPLETELY USING OSQL SPOOL?
Thanks in advance for any help.
View 9 Replies
View Related
Jun 3, 2008
Have a osql batch when i run it on my desktop its fast. then when i run it on the server is very slow.
Any ideas? how to solve it?
MCTP
View 3 Replies
View Related
Jan 17, 2006
Hi,I have just started using MSSQL and the DOS environment at work. I havea lot of experience with Sybase and the UNIX environment, but this is awhole new ball of wax.I'd like to use osql from a batch file to log into the dataserver andrun a fairly long list of SQL and then exit. I don't want to have a bunchof SQL files sitting around that I have to use the -i option to run, andI'd rather not create temporary SQL files like this.echo "exec sp_who2" >tmp.sqlecho "select * from....... " >>tmp.sqlosql -E -S <DATASERVER> -n -w999 -i tmp.sqldel tmp.sqlI can't use the -Q option, of course because as I said, I'll be writingquite a few lines of SQL, and it won't all fit on the one line, or at leastit wouldn't be pretty if I did.In UNIX, I can simply execute the following from either the command lineor ina script.isql -S<DATASERVER> -U<USER> <<-EOFsp_whogoselect * from .....goEOF --EOF is the isql session terminator exits me back to the command line.This behavior does not appear to work with OSQL. There is a -O optionwhich help mentions, and that talks about disabling the EOF terminator forbatch processing, but I wasn't able to find any usage or examples on the netwhere someone is using EOF to terminate their OSQL SQL batch.This is what help listed.[-O use Old ISQL behavior disables the following]<EOF> batch processingAuto console width scalingWide messagesdefault errorlevel is -1 vs 1Any help you could offer would be appreciated. Thanks.Darren
View 2 Replies
View Related
Aug 7, 2007
HELP,
I need to take a variable from a tabel in SQL Server pass to a Batch file and execute the batch file. Right now I can exec the batch file with XP_CMDSHELL but how can I pass the variable to the batch file and loop through all the variables.
Please help
Phil
View 4 Replies
View Related
Dec 5, 2006
I am using the following batch file to execute a script that creates a db and all its objects in the local sql express:
sqlcmd -S (local)SQLExpress -i C:CreateDB.sql
This works fine, but I'm wondering if there's an easy way to put the script in the batch file, so users don't have to worry about putting the script in the C drive. I tried getting rid of the i parameter and pasting the script from the sql file into the batch file, but it didn't work.
Thanks,
Dave
View 1 Replies
View Related
Sep 4, 2003
Why osql igores parameter -w for command PRINT... and why cut the last character ????
osql -S<Server> -d<DB> -E -Q"Print replicate('ab',250)" -otest.txt -n -h -b -w1000
this one example returns two rows, first has 255 characters and second only 244 characters...
View 3 Replies
View Related
Jul 20, 2005
Everybody,I've been doing a lot of on-line research and cannot findany reference to the exact problem I'm having.Let me preface this question with the fact that I'm comingfrom an Oracle background so my approach may not be the bestway to tackle this. However, from the research I have donethis approach seems reasonable. Also, I know about theundocumented procedure sp_MSforeachtable. That can give me aresult similar to what I'm looking for but the format of theoutput is not what I need.Now the problem. I'm trying to write a reusable script to giveme a list of all the tables in a database that have 1 or more rows.My approach is to a BAT file (see script 1 below) that calls OSQLtwice, once to call a SQL script (see script 2 below) that uses theInformation_Schema views to generate the SELECT COUNT(*) statementsand fill in all the tables names in the database, write this to atemporary output file and the second OSQL command to read thetemporary output file and generate me the results formatted theway I need.The result of the first OSQL run is correct EXCEPT for 1> 2> 3> 4> 5>6> 7> 8> 9> 10> 11> 12> 13> garbage at the beginning of the file.Because of this garbage the 2nd OSQL command blows up! Anyone haveany idea what is generating this garbage?If I manually edit out the garbage and then just run the 2nd OSQLcommandI get similar garbage in the final result file (see 2nd result filebelow).In Query Analyzer, when I run the GET_TABLE_COUNT.SQL Script manuallythen take its output and copy and paste it to a new query window andrun that it works OK except for generating lots of blank lines wherethe result of the tables that have zero rows are. I am suppressingheadings but am still getting the blank lines but at least it works!Any ideas anybody? Thanks For Any HelpFYI -- SQL Server 2000 with SP3a.Bob================== Script 1 - BAT File to Call OSQL ===============@echo off@echo ************************************************** *************@echo .@echo get_table_count.bat@echo .@echo Before you run this script change to the drive and directory@echo where the input SQL script is located!@echo .@echo Input parameters:@echo 1) SQL Server userid@echo .@echo You will be prompted twice for your password!@echo .@echo The output is written to file TABLE_COUNT_RESULT.TXT@echo .@echo ************************************************** *************pauseosql -U %1 -S devkc-db -d C3T_Architecture -i get_table_count.sql -otemp_table_count_query.txt -h-1 -w500osql -U %1 -S devkc-db -d C3T_Architecture -itemp_table_count_query.txt -o table_count_result.txt -h-1 -w500del temp_table_count_result.txt@echo on================================================== ==================================== Script 2 - GET_TABLE_COUNT.SQL Script ===============set nocount onselect 'set nocount on'select 'select ''Table Name Count'''select 'select ''========== ====='''select 'select '''+ table_name+ ''', count(*) from '+ table_name+ ' having count(*) > 0 'from information_schema.tableswhere table_type = 'BASE TABLE'order by table_name================================================== ================================ Partial Result of 1st OSQL Run ==========================1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> set nocount onselect 'Table Name Count'select '========== ====='select 'ACT_ASSERTION_RULE', count(*) from ACT_ASSERTION_RULE havingcount(*) > 0select 'ACT_ASSOC', count(*) from ACT_ASSOC having count(*) > 0select 'ACT_DOC', count(*) from ACT_DOC having count(*) > 0================================================== ================================ Partial Result of @nd OSQL Run ==========================1> 2> 3> 4> ... I edited out the intervening numbers for this message.... 664> 665> 666> 667> Table Name Count========== =====.... I edited out lots of blank lines in the result for this messagebefore I get to the first table with 1 or more rows ...ARCH 6================================================== ====================
View 2 Replies
View Related
Nov 2, 2007
Hi all,
I have the "Northwind" database in my Sql Server Management Studio Express.
In my C:ProSSEAppsSamplesForChapter02Chapter02 folder, I have the following 2 files:
(1) ListColumnValues (MS-DOS Batch File)
sqlcmd -S .sqlexpress -v DBName = "Northwind" CName = "CompanyName" TName =
"Shippers" -i c:prosseappschapter02ListListColumnVales.sql -o
c:prosseappschapter02ColumnValuesOut.rpt
(2) ListColumnValues (Microsoft SQL Server Query File)
USE $(Northwind)
GO
SELECT $(CompanyName) FROM $(Shippers)
GO
When I ran the following SQLcmd:
C:ProSSEAppsSamplesForChapter02Chapter02>ListColumnValues.bat
I got the following "ColumnValuesOut.rpt" with error messages:
'Northwind' scripting variable not defined.
Msg 102, Level 15, State 1, Server L1P2P3SQLEXPRESS, Line 1
Incorrect syntax near '$'.
'CompanyName' scripting variable not defined.
'Shippers' scripting variable not defined.
Msg 102, Level 15, State 1, Server L1P2P3SQLEXPRESS, Line 1
Incorrect syntax near 'CompanyName'.
I copied these T-SQL statements from a book and I do not know how to correct them.
Please help and tell me how to correct these errors.
Thanks in advance,
Scott Chang
View 3 Replies
View Related
Jan 5, 2005
The Sql Server database can only see the local drive.
I would like to set up a batch file that will copy a SQL Server
backup file from the local drive to the network drive. I would
like to append the file date to the end of the copied file. I
assume a batch file can accomplish this but I am new to batch
file writing. Does anyone have code that they already created
for this sort of task??
Thank you!
View 13 Replies
View Related
Mar 5, 2007
Hi guys
I need to apply a sql script file on Sql server 2000 by .net 2.0 program. but on the current running machine, there is not Osql.exe file. Do you guys know how to execute the sql file without the command? Is there any way provided in .net library ?
Thanks for you response!
View 3 Replies
View Related
Jul 20, 2005
Hi,I have dumped a very large database from mysql (using mysqldump program)as a raw sql file. The reason was, convert this database to a MSSQLdatabase. Since mysqldump creates the file as raw sql file with thedatabase-table structures and the data in it, I thought using OSQL commandline utilities should work to out this whole database in MSSQL server.I have run this command from command line:osql -u sa -i mysqldump.sqlIt is going since yesterday. It has been almost 36 hours that it'sstarted. And in the mssql server, I see no database created yet. On thescreen of the command line, I see bunch of numbers are going in order. Iassume they are row numbers of the tables processed. But, if it is doing it,then where is it saving all this data ? I have checked the tempdb, pub db,other dbs, and I see no tables related to the database I am inserting. Willit populate it at the and of the job ? Or, am I doing something wrong here?Regards.Murtix.
View 2 Replies
View Related
Jan 22, 2002
hi,
I need some help in accomplishing this task.
I want to design a DTS task which will:
a)copy a certain given files from one directory to another
b)import the files into the tables
c)upon successful import delete the files from the original directory.
I done know much about scripting and need help in figuring out steps a) and c).
thanks
Zoey
View 2 Replies
View Related
Sep 13, 2001
Do u know how to write a batch file
for example i will need to type the sql server name it has to connect to the server and run a script that I have
let me know if u have any ideas of doing it
View 2 Replies
View Related
Sep 13, 2001
Do u know how to write a batch file
for example i will need to type the server name it has to connect to the server and run a script that I have
let me know if u have any ideas of doing it
View 3 Replies
View Related
Mar 29, 2000
Can someone show me an example of the syntax required to execute multiple BCP commands within the same batch (*.bat) file?
Sorry if this is a bit of a basic question, but not being a programmer by profession, I need to plead ignorance. I've tried a few things, but I just can't seem to figure it out.
Thanks!
Rich
View 4 Replies
View Related
Apr 3, 2007
Hi pals,
I need a small help from u all.
I need a Windows batch file script which does the ftp to remote machine and "puts" a file
say "data.xls" from a predefined dir say "c:uploads" and puts on the database server "d:dumps".
Regards,
Franky
View 2 Replies
View Related
Jul 20, 2005
I have a windows batch file that executes a SQL Server bcp command. Iwould like to obtain a return code if the bcp command fails. However,I cannot seem to find the return code (if any) for bcp. For example,if the bcp command is improperly formatted, or has a bad password, Iwant the batch file to return an error. Right now, my batch filesimply executes and returns success, even when the bcp command fails.Has anyone run into this before?Thanks!
View 3 Replies
View Related
Mar 14, 2006
Hey all.. I am wanting my SQL Server installation to restart its services at a time I define in the Scheduled Tasks feature of the server its on
I need help with command line commands to:- Stop SQL Server Agent service- Stop and Start SQL Server service- Restart SQL Server Agent service
Thanks in advance
View 1 Replies
View Related
Nov 6, 2000
Hi all,
I am executing a backup batch job scheduled daily. It runs
successfully but havent backed up any of the databases. The message
is
"The name specified is not recognized as an
internal or external command, operable program or batch file."
I am trying this for a full day and i gave up..please can anyone help
me or suggest me what to do?..The same batch file is executed in all
servers and is working fine..
Thank you in advance
Rani.
View 2 Replies
View Related
Mar 31, 2000
Hi,
I would like to ask someone who could help me with one small problem:
I would like to run SQL server task on one of my servers, that will connect
to all other servers (including ones that are not SQL servers, ie: Exchange,
Test Server etc) one at the time, and that will execute one batch file (update.bat) that is residing on each server under C: drive.
Batch file will copy some things from all of the servers to one server.
Thanks, in advance.
Regards,
Vilko
View 1 Replies
View Related
Oct 21, 1998
Hi,I want to Utilize the schedule feature in window NT to run a batch file which will migrate data from a text file into an Sql server 6.5 table.
here is the senario,
I want to program window NT using AT,For, Start etc. I do not know how and have not yet succeeded. Please help?
the case:
I already wrote a code using bcp in a batch file as follow
if exist c:authors.out bcp pubs..authors in c:authors.out /Sservername /Usa /Ppassword /fp:autors.fmt
so the command line is basically if the file named authors.out exists in drive c: then run the bcp command. here is the problem, how can I run this code in the first place? A I going to use the AT command in window NT to run this code on a specific day and time to check if the author.out file exists.
Please help , I am in urgent need for a solution
You can write back to me at a.alhussein@mci.com
Thanks again for your time and effort to help me
regards
Ali
View 3 Replies
View Related
Jan 1, 2005
Dear All,
i need to put a query in a batch file to be easy to use by the user.
jusy only double click on it and it will work.
can any one help?
View 10 Replies
View Related
Apr 5, 2006
I'm trying schedule a batch file to run as a job from sql server agent. The batch file copies files from one server directory to a directory on another server. The batch file works properly when executed directly. The job is being executed under the sql service account login. I've given the service account access to both the source and destination directories.
When I try to run the job it fails with an "Access is denied" error on both the source and destination directories (as read from job history).
Any ideas?
View 4 Replies
View Related
Apr 15, 2004
Ok.. I thought I could do this but I can't get this to work.. I need to create a batch file to run an sql update statement.. This is simple but I can't get it to wortk.. How do I do it?
View 4 Replies
View Related
Jun 13, 2008
Hi All, I need to create a batch file to execute some 5-6 sql scripts. I have sql scripts for creating a table and related storedprocs and I need to generate a batch file to run all this scripts.
View 3 Replies
View Related
Sep 27, 2006
MS SQL Server 2005
i get the following error when trying to run this job as user1:
'Unable to start execution of step 1 (reason: Error authenticating proxy serv1user1, system error: Logon failure: unknown user name or bad password.). The step failed.'
and the following error when trying to run the same job as 'sql agent service account':
'Executed as user: serv1user1. The process could not be created for step 1 of job 0x3653ACCFFA46DD47B3703C87DDFD0717 (reason: 2). The step failed.'
>job:
job name: job1
server: (local)
connection: net1user1
owner: serv1user1
step type: operating system (cmdexec)
run as: user1
command:@echo off
copy C: est.txt C:folder
ECHO Job completed
PAUSE
>the sql server agent service is running under 'serv1user1' and works fine for other jobs running ssis packages.
>i have created the following credentials under security/credentials:
credential name: serv1user1
identity: serv1user1
password: same as sql server agent service
>i have created a proxy under the sql server agent/operating system (cmdexec):
proxy name: user1
credential name: serv1user1
active subsystem: operating system (cmdexec)
principals (sql login): serv1user1
thank you
View 2 Replies
View Related
Jul 25, 2007
Hi All,
I would like to embed some stored procedures in a batch file and execute it from the command prompt on windows.
I have no idea of how to embed a stored proc in a .bat file.
Can you please redirect me to the solution to this?
Thanks in advance
vishu
View 1 Replies
View Related
Mar 10, 2008
Hi
Is it possible using T-SQL to run a batch file located on different server, ie. PC1 has SQL Server on it, PC2 has the batchfile, I need to run the batchfile stored on PC2 on PC1.
Thanks
View 3 Replies
View Related
Aug 9, 2007
Is it possible to use sql to execute a batch file? I would like toexecute the following "C:BTWartend.exe /f=C:BTWToolboxFormatscarnum.btw /p", 6Thanks,Matt
View 6 Replies
View Related