ISQL - Blank Line At The End Of The ISql Output File
Sep 23, 2005
Hi,
I'm using isql to query data and output the same to a flat file.
The isql has the following command options ' -h-1 -w500 -n -b -s"" '.
In the SQL_CODE, the first two lines before the select statement are
use dbname
set nocount on
go
When I run this, an additional blank line is put into the output file.
Actually, there are two lines after the last result set in the output
file. This file is being fed into another system and the blank line is
causing validation issues.
How can I supress this blank line?
This script is run from windows and the isql is called from a bat
script.
Batch script ...
================================================== ========
.....
isql -Uuserid -Ppassword -Sserver -i"%SQL_CODE%" -h-1 -w500 -n -b -s""[color=blue][color=green]
>>"%OUT_FILE%"[/color][/color]
IF ERRORLEVEL 0 SET RC=0
IF ERRORLEVEL 1 exit 4
================================================== ========
SQL code ...
================================================== ========
use punclaim
set NOCOUNT ON
GO
select * from XYZ;
GO
================================================== ========
I am doing a ISQL join on 4 tables that creates a few million record output. This causes some memory grief on my laptop. How do I have my query output to my c: drive??
G'day everyoneThat's a space between the ticks.It's all part of a longer script but seeing as the failure occurs online 1if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[config]') and OBJECTPROPERTY(id, N'IsUserTable') =1)drop table [dbo].[config]GOThat's three lines only. Does it matter that they're in Unicode?Any ideas?Kind regards,Bruce M. AxtensSoftware EngineerStrapper Technologies
Does any know of a technique to filter out headers dashes and row count in an output file of an isql result? These switches seem to be available when running as isql/w, but not the command line. The -h-1 argument for no headers had no effect. Thanks in advance. Ron Hurley
i have to print Go statement in new line through isql command my isql command is isql /Q -SServername -dDatabasename -Usa -PPassword -h-1 -n -q "set nocount on Select ' grant execute on ' + name + ' to user go ' from sysobjects where type = 'U' and name not like 'dt%'" -o SFGRants.sql >> Deploy.log
and out is like select grant execute tablename to user go
but i want in different format like select grant execute tablename to user go
I want to run and 'Alter Database' statement from within a batch file.The batch file is to be run from an external automation program,(don't ask why)..I know the syntax for the alter db statement but not sure how to do itin a batch file and also If I have 2 instances how do I run it againsteach instance.All being done on Windows 2000 with Sql 2000.Any ideas will be a help.
I have a query I`d like to run using the isql command utility. The query works correctly and the output can be sent to a local file with no problem. When I try to designate a network location as an output file (e.g. abcdefgfile.txt), though, I get an error message "No such file or directory."
Is it possible to send SQL output to a remote location?
I am running a stored procedure thru' isql. This returns only one field. The sp is executed fine but if the data has length greater than 255 the data is truncated. How do u capture the full output ?
I am trying to use a command line program to run a stored procedurethat generates output in a comma-delimitted format. Somehow, ISQL orOSQL always wrap the lines at 256 characters. I believe this hassomething to do with the column width switch (-w). But enlarging thecolumn width to 800 characters max still doesn't help. The followingis a stored procedure that is essentially doing what my storedprocedure is doing:create procedure MyTest asset ansi_padding onset nocount ondeclare @sTest varchar(300)-- Output three lines. Each line has 259 characters.select @sTest = "1234 6789 ... 1234 6789"print @sTestselect @sTest = "1 3 5 7 9 ... 1 3 5 7 9"print @sTestselect @sTest = "1 3 5 7 9 ... 1 3 5 7 9"print @sTestset nocount offreturn( 0 )I invoke this stored procedure using this command:isql -SMyDbSrv -E -dMyDb -w800 -x800 -h-1 -n -Q"exec MyTest"-oMyTest.txt-- or --osql -SMyDbSrv -E -dMyDb -w800 -h-1 -n -Q"exec MyTest" -oMyTest.txtBut they have the same problem. The output lines all wrap around at256 characters.Strangely, if I store the result in a temporary table, and then useSELECT to output the result from the temporary table, I will not havethat problem. Seem like the "-w" switch only works for output fromtables, but not for output coming from PRINT. Unfortunately, usingthis approach has another set of problems (one blank space in front ofeach line, "number-of-rows affected" shows up at the bottom).Therefore, I would like to stick with using PRINT statements to outputthe result.Please suggest a way to fix this line-wrapping-around problem.Thanks.Jay Chan
I am writing a trigger that I want to call a BAT file when there is an insert or update to a paricular table, but don't know the code to that will execute my BAT file.
To reitrate, when a row is inserted, I want to automatically start a particular EXE that our developers created to process rows written into this table.
My BAT that currently runs OK from the Command line is : CBBOP.BAT ----------------- @echo "d:program filescabs2000CBBOP.exe" /cabsit /password 2,2,time -----------------
But what I need to know is the SQL that will run my CBBOP.BAT, and I will code it into my trigger.
Hello, how can i do to launch a .bat file that execute my procedures in whitch i populate a table that after must be send whith bcp command to a file?? Can you give me an example for that?What about ISQL?? Thanks
Does anyone use ISQL to run maintenance? I am trying to write a batch file that will log on to my server and dump a specifice tran log when triggered by a perfmon warning.
I can successfully log on to the server however the dump command does not seem to run? IS it possible to run a predefined task??
When I am running an SQL query on a SQL server DB from ISQL the results are returning a space in front of every row, plus it is returning the number of rows effected at the bottom. When I run the query without ISQL the results come back without the space in front of every row, but it does come back with the number of rows effected. I need the spaces reomed and the number of rows removed. Here is an example of the ISQL. ISQL.EXE /U????? /P?????? /n /d????? /h-1 /w 218 /S????? /i????? /o?????
Are ther any arguments to help remove the preceding space and the trailing footer?
Here is a copy of the SQL
select CONVERT(char(49),i.item_number),CONVERT(char(17),s um(s.actual_qty)),CONVERT(char(19),"WMS"), CONVERT(char(2),DATEPART(month,GETDATE())) + '/' + CONVERT(char(2),DATEPART(day,GETDATE())) + '/' + CONVERT(char(4),DATEPART(year,GETDATE())) + CONVERT(char(109),''), CONVERT(char(8),"01:00:00") from t_stored_item s, t_item_master i, t_location l where i.c5 = 'FVM' and i.item_number = s.item_number and l.location_id = s.location_id and l.location_id like 'V-%' group by i.item_number order by i.item_number
I'm trying to run a script in isql with a file that contains create database statements and I'm completely lost. If someone could help me and point me in the right direction, please!
Hi, When I execute a script by using isql, it displays the number of statement executed. can anybody tell me how to turn this off (no display)? Thanks,
Bonjour, J'execute un script en sql 7 qui lance isql qui crée un fichier texte en sortie. j'ai un souci, ce fichier de resultat a remplacer les lettres accentuées é è ... par des virgules , Quelle est la solution ? Merci d'avance ...
I am looking for a shareware product I can use in place of ISQL. I am working with databases on a web site, and I want to be able to give different clients access to their data, without forcing them to go out and buy SQL Server.
All the wanted to know whether the ISQL utility is optional during the sqlserver setup??? Is it possible to install sql server without installing ISQL utility??
I'm running sql 6.5. trying to load lots of data. Rec msg that memory was low so I increased memory obvisouly too high. Now sql will not start. found all the documentation to start sql in single user mode however when trying to start isql to change to memory setting, isql would not start. The server is using named pipes. Message I receive is sql server is unavailable or does not exist.
You help would be greatly appreciated as this is a production environment and we are down.
I would like to pass parameters from isql command line to an input query file. I am working on SQL Server 7.0. How can I accomplish this?
My eg. is like this:
> isql.exe (all the server options etc.,) /iInputfile /oOutput file I need the output depend on the query given in the input file with the query depending on the parameter I supply on the command line.Thanks for your help in advance.
The problem is that this query looks for the C: drive on the SERVER not on my local machine. C:QRYIn.txt exists on my machine so this does not work. However if I create a C:QRY folder on the SERVER the whole thing works.
Basically I want this query to go to execute the In.txt query from my local machine as opposed to the SERVER.
Gurus, For some reason the INPUT REDIRECTION of FTP commands from a file does not work when executed from a ISQL session.
"ftp -i -n host_name <serverstare_dirFTP-commands.dat >serverstare_dirFTP-errors.dat " would not work. It gives me an error message - "The handle could not be opened during redirection of handle 0 "
How ever it works from COMMAND prompt and BAT files. I changed it use "-s:" option instead of INPUT REDIRECTION and it works just fine every where... The command I used is "ftp -i -n -s:serverstare_dirFTP-commands.dat host_name >serverstare_dirFTP-errors.dat "
I still could not figure out why INPUT REDIRECTION would not work from ISQL only.
I have copied a script from the web that allows me to run scripts from a file over multiple databases(have attached file). Now everything works fine on my local SQL server but when I try to run it on my web server I get this msg ''isql' is not recognized as an internal or external command, operable program or batch file.'
I have tried to use the full path to the isql.exe file but still get the msg
Thats about as much info I can provide, my understanding of this is very slight
ISQL.EXE hangs during installation. In task manager, when I end task on ISQL.EXE the installation continues, but msdb comes up suspect. Can someone please help me.
I need to be able to query a db on server A, in a batch file, return the result (DateTime value) into a variable, and then use that date as a parameter in a query that I will query on server b.
Hi,I have created a database as pubs.qa and wrote a command utility toquery a table.isql -S server -E -d pubs.qa -i qry.sql -b -ncontents of qry.sqlselect * from usersgowhen i run this command, error messageMsg 911, Level 16, State 1, Server XXXXXXX, Line 1Could not locate entry in sysdatabases for database 'pubs'. No entryfound with that name. Make sure that the name is entered correctly.However, I am able to use this database in Query Analyzer and executethe query.I am just wondering if the database name with '.' is the problem...Any pointers to proceed further.Thanks in advance-Anu
Hello everyone!I have the following problem. I`ve opened a Query Analyser and write thestatement:-isql -S 'PL6XXXX' -i 'd:SQL_Server_2000Raport_WWW.sql'I got the following erorr message:Incorrect syntax near -SHow to call the statement which is placed inside the 'Raport_WWW.sql'file, which is located on folder D:SQL_Server_2000??? Do I have to doit in the cmd panel???Thank you in advance for your helpMarcin from Poland*** Sent via Developersdex http://www.developersdex.com ***
I need to have an Application owner backup a SQL Server dbase after his updates. Im not that familiar with ISQL. He does not have EM installed on his machine. What minimum installments do I have to do on his machine for him to log into SQL Server, after I create a login for him ? Once in the command window I would give him the script of isql -Usa -Ppassword -SSqlServerA -Q "BACKUP DATABASE [test] TO DISK = N'E:Program FilesMicrosoft SQL ServerMSSQLBackupdbase1' WITH NOINIT , NOUNLOAD , NAME = N'test backup', NOSKIP , STATS = 10, NOFORMAT"
I have a stored proc which creates a couple of temp tables, then bounces them against a production table an updates a column in the production table.
This stored proc takes about 10 minutes to run and update about 20,000 rows. If I execute each statement seperately in an ISQL window, it all runs in under 2 minutes.
Any ideas on why this is happening are GREATLY appreciated.