Osql / Isql
Jan 17, 2008Is osql/isql supported in SQL 2005? How about in 2008? Thanks
View 2 Replies
ADVERTISEMENT
ISQL And OSQL Output Lines Wrapped Around At 256 Characters?
Jul 20, 2005I 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
View 5 Replies View RelatedOutputting Large Rows (>1000 Chars) Via Osql Or Isql?
Jan 25, 2001Iam 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?
Isql / Osql / Windows XP / Disable Automatic ANSI To OEM Conversion
Jan 18, 2006Hello 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 RelatedISQL - Blank Line At The End Of The ISql Output File
Sep 23, 2005Hi,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 RelatedISQL
Oct 27, 2000Hello,
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
Isql
Jan 3, 2000Does 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
Isql
Dec 16, 2002When 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!
Isql Help
May 4, 2004Hello 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
Isql
Jan 31, 2007Hi,
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>
Isql Display
Aug 9, 2000Hi,
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
ISQL --URGENT
Oct 3, 2000Hi
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
Pb Codepage Et ISQL
Aug 24, 2000Bonjour,
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 ...
ISQL Shareware
Sep 2, 2000I 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
ISQL Utility
Aug 6, 2001Hi,
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
URGENT Isql Will Not Start
Jan 24, 2002I'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.
thanks!!
darlene
Passing Parameters In ISQL
Aug 30, 2001Hi,
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.
thanks,
Sravan.
ISQL, Xp_cmdShell Question
Dec 27, 1999Hi,
I have an input file which contains a simple select query. I want to output the results of the query to an output file. I use the following query:
DECLARE @sTemp1 Varchar(100)
SELECT @sTemp1 = "isql -d TestDB /U sa /P SAPassword -n /i C:QRYIn.txt > C:QRYOut.txt"
EXEC master..xp_cmdshell @sTemp1
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.
Redirection In FTP Would Not Work From ISQL..
Sep 21, 2000Gurus,
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.
any ideas !!!!!!!!
Thanks,
- HemantH
Calling A BAT File With ISQL ?
Sep 26, 1999I 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.
Cheers
Brett
ISQL Output To A File...
Jul 25, 2002SQL 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
Cant Get ISQL To Work With Xp_cmdshell
Dec 30, 2002I 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
Mar 8, 1999ISQL.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.
View 2 Replies View RelatedHelp! ISQL And DOS Batch Files
Aug 13, 2004Hi all,
Can anyone help me?? I'm just a newb :D
Please consider the following:
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.
I have the following code:
isql -E -d firstDB -S ServerA -Q "select max(load_date) from mydates"
How would I pipe the results of the above query into a variable???
I cannot create a linked server between the two servers. (Permissions)
Thanks in advance!
Isql Error Message
Jul 23, 2005Hi,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
View 3 Replies View RelatedISQL Call Problem
Aug 13, 2005Hello 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 ***
View 4 Replies View RelatedIsql Command Line Arguments
Jun 26, 2000Does 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
View 1 Replies View RelatedUsing ISQL To Have End User Do Backup Of Database
Jul 28, 2004I 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"
Is this the correct way to go ?
Thank you
Stored Proc Slow Vs. ISQL
Jul 26, 1999Hello all.
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.
HowTo: Use ISQL To Include Several Scripts
May 26, 1999Hi there:
Using Oracle SQL*Plus, I can have a script to execute other scripts written before such as this:
@script1
@script2
..
@scriptn
Using ISQL or ISQL/w, how can I do this?
Thanks in advance
Isql Problem With Null Values
Jun 26, 2006I am having a problem running a stored procedure using isql to launch the procedure.
My stored procedure contains a statement like:
select @myvar = count(*) from mytable where mycolumn is null
when I run the procedure from query analyser a value is set in @myvar showing the correct number of null values from mytable which is output to another table where I can see it.
Without making any changes, when I run the identical procedure using isql to execute the procedure @myvar gets set to zero. The procedure cannot find the null values in mytable
I have tried setting ANSI_NULLS on and off inside the procedure but still get the same result.
Can someone please tell me what is going on, and how I might get isql to show me how many null values exist in the table?
Windows Authentication And EM/QA/ISQL/Office
Jun 26, 2006Hi,
A while ago we changed from Sybase to MS Sql Server 2000. Thereby we also introduced Windows Authentication instead of SQL Logins. When end-users wanted to read directly (tools like Enterprise Manager/Query Analyzer/ isql and Office products (like Access and Excel via MS Query)) from the Sybase database we managed that with a SQL login with read-only rights. With Windows Authentication in MS Sql Server 2000 the end-user can enter the database with update rights. Is there a way to manage tools like EM/QA/isql/Office products etcetera so that the end-user can approach the database only with a SQL Login instead of Windows authentication ? Although we are building tooling to kill every user who approaches the database with the tooling mentioned, we think this is not a proper way to manage database access.
We hope someone has encountered the same problem and knows how to handle this and can help us.
Greetings,
Ferrie
Print Go In NEw Line Through Isql Command
Mar 9, 2007i 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
like that
thanking in anticipation