ISQL And OSQL Output Lines Wrapped Around At 256 Characters?
Jul 20, 2005
I am trying to use a command line program to run a stored procedure
that generates output in a comma-delimitted format. Somehow, ISQL or
OSQL always wrap the lines at 256 characters. I believe this has
something to do with the column width switch (-w). But enlarging the
column width to 800 characters max still doesn't help. The following
is a stored procedure that is essentially doing what my stored
procedure is doing:
create procedure MyTest as
set ansi_padding on
set nocount on
declare @sTest varchar(300)
-- Output three lines. Each line has 259 characters.
select @sTest = "1234 6789 ... 1234 6789"
print @sTest
select @sTest = "1 3 5 7 9 ... 1 3 5 7 9"
print @sTest
select @sTest = "1 3 5 7 9 ... 1 3 5 7 9"
print @sTest
set nocount off
return( 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.txt
But they have the same problem. The output lines all wrap around at
256 characters.
Strangely, if I store the result in a temporary table, and then use
SELECT to output the result from the temporary table, I will not have
that problem. Seem like the "-w" switch only works for output from
tables, but not for output coming from PRINT. Unfortunately, using
this approach has another set of problems (one blank space in front of
each line, "number-of-rows affected" shows up at the bottom).
Therefore, I would like to stick with using PRINT statements to output
the result.
Please suggest a way to fix this line-wrapping-around problem.
Thanks.
Jay Chan
View 5 Replies
ADVERTISEMENT
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 areuse dbnameset nocount ongoWhen 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 outputfile. This file is being fed into another system and the blank line iscausing validation issues.How can I supress this blank line?This script is run from windows and the isql is called from a batscript.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=0IF ERRORLEVEL 1 exit 4================================================== ========SQL code ...================================================== ========use punclaimset NOCOUNT ONGOselect * from XYZ;GO================================================== ========Your help is greatly appreciated.Yash
View 3 Replies
View Related
Jan 17, 2008
Is osql/isql supported in SQL 2005? How about in 2008? Thanks
View 2 Replies
View Related
Jan 25, 2001
Iam attempting to generate files containing more than 1000 characters per line by outputting the results of a stored procedure via osql to a flat file. Osql (and isql) appear to force a newline after 1000 characters, even when specifiying a -w2000 parameter.
I have also tried to output the results of the stored procedure via DTS and this appears to do the same thing!
Does anybody know how to prevent osql (or isql) from forcing the newline?
View 1 Replies
View Related
Jan 18, 2006
Hello to all SQL Server junkies who work with non-English characters:For people running scripts from the command line using ANSI files withspecial characters, it is very important to use isql and disable"Automatic ANSI to OEM conversion":- This only affects isql from the command line, and no guiapplications- http://support.microsoft.com/?scid=kb;EN-US;153449- Start the "Client Network Utility"C:WINDOWSsystem32cliconfg.exe- Select the DB-Library Tab- Deselect "Automatic ANSI to OEM conversion"- Click OK or ApplyOr inject this registry entry:[HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSSQLServer ClientDB-Lib]"AutoAnsiToOem"="OFF"Here are some useful lines from a batch script to query the currentvalue of the registry and reset if necessary. This is tested onWindows XP. It will query the registry, throw away the first threelines of output, and return the value of the third field on the fourthline. Delims lists one tab character and one space character. Type thefollowing all on one line:@FOR /F "SKIP=3 TOKENS=3 DELIMS= " %%A IN ('REG QUERYHKLMSOFTWAREMicrosoftMSSQLServerClientDB-Lib /v AutoAnsiToOem') DO@SET AUTOANSITOOEM=%%AType the "reg add" line all on one line:@IF /i "%AUTOANSITOOEM%" EQU "ON" (@ECHO************************************************** **********************@ECHO ****@ECHO **** We need to disable "Automatic ANSI to OEM conversion"@ECHO **** Please seehttp://support.microsoft.com/?scid=kb;EN-US;153449@ECHO **** This only affects isql from the command line@ECHO ****@ECHO************************************************** **********************@REMREG ADD HKLMSOFTWAREMicrosoftMSSQLServerClientDB-Lib /vAutoAnsiToOem /t REG_SZ /d OFF)Alternatively, you must use Unicode script files and osql.PS: Thank you to Erland Sommarskog for http://www.sommarskog.se and Robvan der Woude for http://www.robvanderwoude.com
View 1 Replies
View Related
Feb 23, 2006
We have a very long and complicated SQL script which we run to upgradea version of our software from old to new. It works great in QueryAnalyzer, but when run through osql it takes errors on lines that arevery long and (I think) stops reading after a certain amount ofcharacters. I've searched the net but haven't found anyone mentioningthis before. I have tried the -w 5000 parm to no avail.Any suggestions?
View 4 Replies
View Related
Jul 25, 2002
SQL 6.5
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??
Thnaks in advance.
Ray
View 1 Replies
View Related
Sep 15, 1998
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?
TIA!
View 1 Replies
View Related
Jun 15, 2004
Hello,
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 ?
Thanks In Advance
Ashutosh
:rolleyes:
View 3 Replies
View Related
Dec 10, 2007
Hi All,
The command showed below provides the output shown:
set @cmd = 'osql -S bcgsql1cgsqldev -U checkdb -P checkdb -q "set nocount on; select errorlogdesc from ##errors where datediff(dd,errorlogdatetime,getdate()) = 1 and errorlogdesc like ''%Error:%''" -h-1 -w 900 -o J:ScriptsTestStuffErrorLogMsg.txt'
Here is the output:
Error: 17883, Severity: 1, State: 0
Error: 17883, Severity: 1, State: 0
I need to remove the space between the two lines so I set the -w parameter to 900 but that didn't fix it. Any idea how I can accomplish this?
Thanks
View 4 Replies
View Related
Jul 20, 2005
hi, i had been removed the row counts and the column spaces... but ifailed to remove the extra tabs between rows of data return from theosql output.how to detect the row delimiter?i noticed i can use bcp to have a more decent output file, but i don'twant to do too much of file read-write. the programming enviroment isquite easy to "capture" the output from the comand prompt.secondary, char(252), 253, 254, 255 is not being read by the commandprompt, for example, 253 turns into 132 when i decode the input.thankx.from alan.
View 1 Replies
View Related
Jun 12, 2006
I'm running a query with osql, and I'm trying to get some clean output that is comma delimited. So far my line looks like this:osql -E -n -d mydb -i custom.qry -o "c:output
esults.csv" -h-1 -s ","This works off a table with only two columns. I'm still left with a lot of extra spaces between the first column output and second column output, and at the bottom the text "(50 rows affected)".So instead of this:data1a (lots of spaces here) ,data1bdata2a (lots of spaces here) ,data2bdata3a (lots of spaces here) ,data3b(50 rows affected)I want to see this:data1a,data1bdata2a,data2bdata3a,data3bThanks very much for any help. :)
View 2 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
Jun 23, 2003
Hi All, I'm saving the output of a query into an html file. The dashed lines above rows that have a select in it are showing up in the final doc and are playing havoc with my page. Does anyone know of a command to stop them from appearing. nocount just suppresses the rows affected message. ANY help is appreciated.
Thanks!
View 1 Replies
View Related
Sep 29, 2004
I am running this stored Prcedure and getting multiple lines for the output. Below are the queries the create the temp table (its holding the data), and the query that generates the output:
/* this creates the temp table */
Select count(*) as 'Donations', d_vst_id
into Donations_temp
from dnr_vst_db_rec
where convert(varchar(10),d_vst_date) between convert(varchar(10), @Beg_Vst_Date, 112) and convert(varchar(10), @End_Vst_Date, 112)
and d_vst_status = 'DN'
and d_vst_dontyp in ('E1', 'E2')
group by d_vst_id
/* this query generates the output */
select distinct cast(getdate() as varchar(30)) as 'TODAY'
,CONVERT(varchar(10), @Beg_Vst_Date,101) as 'BEGDTE'
,CONVERT(varchar(10), @End_Vst_Date,101) as 'ENDDTE'
,case Donations
when '1' then sum(1)
else 0
end as 'ONE1'
,case Donations
when '2' then sum(1)
else 0
end as 'ONE2'
,case Donations
when '3' then sum(1)
else 0
end as 'ONE3'
,case Donations
when '4' then sum(1)
else 0
end as 'ONE4'
,case Donations
when '5' then sum(1)
else 0
end as 'ONE5'
,case Donations
when '6' then sum(1)
else 0
end as 'ONE6'
,case Donations
when '7' then sum(1)
else 0
end as 'ONE7'
,case Donations
when '1' then Sum(0)
when '2' then Sum(0)
when '3' then Sum(0)
when '4' then Sum(0)
when '5' then Sum(0)
when '6' then Sum(0)
when '7' then Sum(0)
else Sum(1)
end as 'ONEA'
from Donations_temp
group by Donations
Thanks.
View 1 Replies
View Related
Feb 12, 2002
have
select isntuser from syslogins
output
isntuser
--------
1
want
select isntuser as [Joe]+char(13) +[Blowwwwwwww] from syslogins
Joe
Blowwwwwww
--------
1
any idea ?
View 1 Replies
View Related
Apr 2, 2007
Hi,
iam bringing an output in flatfile which is truncating after 255 characters,some of the data is vanishing because of this.can anyone pls help.
pls advice.
Regards,
sg
View 7 Replies
View Related
Jul 20, 2005
I have been testing our SQL Mail setup in SQL Server 2000 (sp3a) andhave found that when I attach results as a file, every other characteris a control character which causes each real character output on aseparate line. I have no idea why this is happenening, I've never seenit before.The code looks like this;EXEC master.dbo.xp_sendmail@recipients = '<email address>',@dbuse = 'TestDB',@query = 'select top 50 descr from AdTable',@message = 'nathan email test',@subject='SQL Mail test',@attach_results = 'true',@width = 100,@separator = ','The results look like this! -descr-----etc.When I view the result text file with an advanced text editor I cansee that every other character is a control - these characters are notin the data, I have already checked this, so it looks like its theyare being created by SQL Server or the mail system? Any advice muchappreciated.Nathan
View 6 Replies
View Related
Feb 7, 2008
I have looked at the membership and roles stored procs from Microsoft and noticed that most of them are wrapped into a transaction. Ok some of the stored procs updated more than one table in which case it makes sense to wrap the code into a transaction. Our stored procs are a little simpler and insert, update or delete only one table for the most part. My question is: What is good practice, should I wrap my stored procs in transactions or because I am only updating one table leave it the way it is, see sample below: Please advise, newbie
ALTER PROCEDURE [dbo].[syl_Category_Insert] @CategoryName nvarchar(64), @LanguageID int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRYINSERT INTO [syl_Categories]
VALUES(
@CategoryName,
@LanguageID)
SELECT SCOPE_IDENTITY() AS [CategoryID]
RETURNEND TRY
BEGIN CATCH
--Execute LogError_Insert SP EXECUTE [dbo].[syl_LogError_Insert];
--Being in a Catch Block indicates failure.
--Force RETURN to -1 for consistency (other return values are generated, such as -6).RETURN -1
END CATCH
END
View 2 Replies
View Related
Feb 7, 2008
I have looked at the membership and roles stored procs from Microsoft and noticed that most of them are wrapped into a transaction. Ok some of the stored procs updated more than one table in which case it makes sense to wrap the code into a transaction. Our stored procs are a little simpler and insert, update or delete only one table for the most part. My question is: What is good practice, should I wrap my stored procs in transactions or because I am only updating one table leave it the way it is, see sample below: Please advise, newbie
ALTER PROCEDURE [dbo].[syl_Category_Insert]
@CategoryName nvarchar(64), @LanguageID int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY
INSERT INTO [syl_Categories]
VALUES(
@CategoryName,
@LanguageID)
SELECT SCOPE_IDENTITY() AS [CategoryID]
RETURN
END TRY
BEGIN CATCH
--Execute LogError_Insert SP
EXECUTE [dbo].[syl_LogError_Insert];
--Being in a Catch Block indicates failure.
--Force RETURN to -1 for consistency (other return values are generated, such as -6).
RETURN -1
END CATCH
END
View 3 Replies
View Related
Jul 20, 2005
I have some code that dynamically creates a database (name is @FullName) andthen creates a table within that database. Is it possible to wrap thesethings into a transaction such that if any one of the following fails, thedatabase "creation" is rolledback. Otherwise, I would try deleting on errordetection, but it could get messy.IF @Error = 0BEGINSET @ExecString = 'CREATE DATABASE ' + @FullNameEXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'CREATE TABLE ' + @FullName + '.[dbo].[Image] ( [ID][int] IDENTITY (1, 1) NOT NULL, [Blob] [image] NULL , [DateAdded] [datetime]NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'ALTER TABLE ' + @FullName + '.[dbo].[Image] WITHNOCHECK ADD CONSTRAINT [PK_Image] PRIMARY KEY CLUSTERED ( [ID] ) ON[PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorEND
View 2 Replies
View Related
Feb 19, 2008
In my application I must store over 16000 character in a sql table field . When I split into more than 1 field it gives "unclosed quotation mark" message.
How can I store over 16000 characters to sql table field (only one field) with language specific characters?
Thanks
View 3 Replies
View Related
Mar 5, 2008
Hi everybody,
I would like to know if there is any property in sql2000 database to separate lowercase characters from uppercase characters. I mean not to take the values €˜child€™ and €˜Child€™ as to be the same. We are transferring our ingres database into sqlserver. In ingres we have these values but we consider them as different values. Can we have it in sqlserver too?
Hellen
View 1 Replies
View Related
Oct 27, 2000
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
Massimo Nardi
View 2 Replies
View Related
Jan 3, 2000
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??
ANy assistance would be greatly appreciated,
Thanks
David
View 4 Replies
View Related
Dec 16, 2002
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
Thanks!
View 2 Replies
View Related
May 4, 2004
Hello all, I'm new to the board...
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!
Thank you
View 5 Replies
View Related
Jan 31, 2007
Hi,
I'm running isql from a dos batch file and the isql command is below:
%SQLPath%isql -U%DBUser% -P%DBPswd% -S%SQLServer% -d%Gatewaydb% -iBatchScriptsSQL_Get_Create_Batch.sql -oZ:mbox
ecsincreate_1.bat -w100 -s; -h
I want the rows from the SELECT statement in my ".sql" file to appear in the output file but what ends up being written to the file is this:
1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> (14 rows affected)
Not sure what I'm doing wrong.
Thanks,
J
1>
View 9 Replies
View Related
Aug 9, 2000
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,
Mark
View 1 Replies
View Related
Oct 3, 2000
Hi
I am running the Stored procedure from using ISQL like this and the stored procedure name is in the input file (c:empinp.txt)
c:>ISQL /S DB1 /U sa /P pass /d test_db /i c:empinp.txt
I want to write a Comment(say 'DONE' OR 'COMPLETED') into a OUTPUT FILE? after executing the stored procedure.
Is there a way to do it?
Thanks
VENU
View 1 Replies
View Related
Aug 24, 2000
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 ...
View 1 Replies
View Related
Sep 2, 2000
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.
Thanks,
Doug
View 1 Replies
View Related
Aug 6, 2001
Hi,
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??
Thanks in advance,
Asha
View 1 Replies
View Related