Osql Batch Slow
Jun 3, 2008Have 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
ADVERTISEMENT
Osql Batch Processing Without -i
Jan 17, 2006Hi,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 RelatedOSQL Batch File Problem
Aug 22, 2006I 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
Executing Osql Commands Through Batch File
Sep 13, 2006Hello
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
Formatting Question With Osql Batch File
Jul 23, 2005Hi all,This is my first batch file and I want to query a database and outputit in a textfile. Up to now that works, the only problem is theformatting in the text file. It's all screewed up...lines aren't linedup and columns aren't right....how do you format the result from aquery to a text file. Here's my code:osql.exe -S MYMACHINE -w 30 -E -d Demo2 -Q "select * from Title wherefrequency = 'monthly'" -o "C:output.txt"Thanks....JMT
View 1 Replies View RelatedDOS Batch File Running Osql - Opening/closing Connections
Jul 20, 2007I'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:
SQL Batch - Slow Response - Remote DB Query
Oct 16, 2006Hello.I have a slow response with a system that I am setting up. The OS isWin 2000 Server with SQL Server 2000. My first execution of the SQLprocedure is slow (about 40 seconds), while the second execution isfaster (approx. 3 seconds and what I expect).Using query analyser I can leave the window up and come back ( aftersay 10 minutes ) and my next response is will be slow (and fasterstraight after this).The query itself is the "TOP 10" rows from a table of a remote DB (MSDE2000 - NT station).This is similar to;---------------------DECLARE @SelectRowsFromRemoteDB NVARCHAR(400)SET @SelectRowsFromRemoteDB = "SELCT a.* FROM OPENROWSET('SQLOLEDB', 'Remote', ....) a"EXEC sp_executesql @SelectRowsFromRemoteDB---------------------The CPU usage goes high (with Task Manager), but oddly the task withmost usage is the "Idle Process".Please help with an answeror a good starting point to known where the resources are being used.Regards JC.....
View 2 Replies View RelatedPassing Parameters To Batch File And Executing Batch File Loop
Aug 7, 2007HELP,
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
SQLCMD Batch File With Script In Batch File
Dec 5, 2006I 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
Osql
Jun 28, 2001Hi, we are having problems getting osql to work. When we try and open it quickly opens and then closes again. Has anybody else had this problem?
View 1 Replies View RelatedOsql
Oct 25, 2004Hi all.
My first post here.
I want to distriburte a msde database. When doing so, I also want to make a new login, make a user, make the user the dbowner of the database i installed.
I found the Stored procedures I need to use, and I have tested that it works using OSQL.
What I want to do now is to make this automatic. After installing the database the OSQL commands should be executed and no user interference should be necessary. How can that be done ?
peet
OSQL Help
May 15, 2008Hi All,
I am trying to use OSQL (somthing I am new too)
I have the folllowing syntax, but it does not seam to work. Any ideas would be very gratfull.
osql -Usa -Ppassword -SServername -Q"select @@version"
Thanks in advance
Dave
Dave Dunckley says there is a law for the rich and a law for the poor and a law for
Dirty Davey.
OSQL
Mar 11, 2008Hello,
I have several sql scripts to be executed on a set schedule, with the output directed to a text file. If the schedule triggers this process daily, is it possible to append each days output to the same output file? I've researched the osql switches and various online sources...nothing really covers this.
This is done (relatively) easily with .vbs, but so far it looks tougher with TSQL.
Many thanks to all who respond!
Osql
Mar 12, 2008is there some way to activate osql from a c++ app and stream theoutput to my program?
View 1 Replies View RelatedOsql
Dec 22, 2006i'm trying to execute some scripts created by the express studio script wizard. i can connect with the studio, the website (asp worker) but i can't create the right cmdline for osql ..... this is my osql line ......
C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinn>osql -S (local)\SQLEXPRESS -U sa -P sablah -i run.sql.......
this is the error i'm getting in my logs
Error: 18456, Severity: 14, State: 16.
2006-12-22 15:30:45.11 Logon Login failed for user 'sa'. [CLIENT: <local machine>]
"Server=(local)\SQLEXPRESS;Database=ggmi;User Id=sa;Password=cr79cr02;Trusted_connection=false;";
the following is the working connection string for my aspworker.
"Server=(local)\SQLEXPRESS;Database=ggmi;User Id=sa;Password=cr79cr02;Trusted_connection=false;";
when i try to put in the trust conenction parameter is says that it conflicts with the user flag ,. probably because its a differant type of login process. any ideas?
Osql Utility
Jul 28, 2004using osql utility i have converted a table in database to excel format. I have also chinese data in my database . The problem is while converting the data into excel format ,the chinese data is not comming in excel sheet.
Does anybody have an idea about this .
For converting i have used
declare @x varchar(300)
set @x = 'osql -S Servername -U username -P passwd -q "select * from northwind..region order by cand_index" -w 3000 -s "," -o C:directoryfilename.csv'
Thanks
Osql Utility
Aug 14, 2004Hi all
I am using osql utility in sql server 2000 to convert data in database to excel sheet. My datas contain both english and chinese . While converting to excel sheet english datas are comming but the datas in chinese is comming like (?) this. Does any body has any idea to solve this problem. is there any way to set font in osql utility.
set @x = 'osql -S servername -U username -P password -q "select * from
northwind..region" -w 3000 -s "," -o C:Downloaddownload.csv'
This is what i have used.
Note: I tried giving chinese font in excel still it is not working.
Its really urgent pls reply
thanks
Osql Syntax Help
Oct 8, 2004Dear friends,
I was trying to run this script from a dos batch file under win xp but it's not working. Please help with the syntax.
================================================== =======
OSQL -U sa -P samsde -S firebird /Q "RESTORE DATABASE NAVIMEX FROM DISK = 'C:30704' WITH
REPLACE, MOVE'NAVIMEX_Data' TO 'C:Program FilesMicrosoft SQL
ServerMSSQLDataNAVIMEX_Data.MDF',MOVE 'NAVIMEX_Log' TO 'C:Program FilesMicrosoft SQL
ServerMSSQLDataNAVIMEX_LOG.LDF'" QUIT
================================================== ========
Please provide me with the correct syntax to be put in a 'restore.bat' file :rolleyes:
Thanks in advance.
HotBird
Osql -b Question!
Oct 25, 2004the help document said that
"osql -b will specifies that OSQL exits and returns a DOS ERRORLEVEL value when an error occurs. The value returned to the DOS ERRORLEVEL variable is 1 when the SQL Server error message has a severity of 10 or greater; otherwise, the value returned is 0."
my question is : how can I get the DOS ERRORLEVEL.
example:
C:>isql -E -b -Q"backup log pubs to disk='C:adsfasd.tmp'"
the result:
Msg 4208, Level 16, State 0, Server YANG, Line 1
当恢复模型为 SIMPLE 时，不允许使用 BACKUP LOG 语句。请使用 BACKUP DATABASE 或用
ALTER DATABASE
更改恢复模型。
Msg 3013, Level 16, State 1, Server YANG, Line 1
BACKUP LOG 操作异常终止。
It's in chinese language,means
"can not use BACKUP LOG when the restore model being SIMPLE,you can use ALTER DATABASE or BACKUP DATABASE to change the restore model.
Msg 3013, Level 16, State 1, Server YANG, Line 1
BACKUP LOG aborted"
where is DOS ERRORLEVEL???? How can I get it????
thanks
Running A Job Via Osql??
Dec 1, 2004Hello All,
A VERY green SQL Server DBA here looking for some help. Our main production environment is Oracle, which utilizes Control-M as a scheduler. At the end of the Oracle batch process, we would like to automate a process to kick off a sql server job (perhaps via osql??) Is this possible?
Thanks in advance,
Tony
Osql And Carc.:éàè
Dec 14, 2005I run a script with Osql and got no error but the accent caracter (french message) are modified.
the script is
CREATE PROCEDURE dbo.AccentTest
AS
Print('é à è î ï ù')
GO
and come
CREATE PROCEDURE dbo.AccentTest
AS
Print('Θ α Φ ε ∩ ∙')
GO
:o
This is very bad! Can someone can help me?
Any suggestion?
PLease.....
osql.exe -S MyServer -U username-P pwd-d TestDB -n -o "C:Devosql_test.log" -i "C:DevAccentTest.sql" :
Dynamic SQL Vs OSQL
Sep 5, 2006Question for the experts here:
Is there any advantage of running an SQL statement through osql with the database information over using dynamic sql?
Example:
DECLARE @DB Varchar(50)
DECLARE @SQL Varchar(4000)
SET @DB = '<nvr_changing_server>.<my_dynamic_db_name>'
SET @SQL = 'SELECT admissiontype_id AS atype, admissiontype AS atype_desc, start_date, end_date
INTO tlkAdmitType
FROM <nvr_changing_server>.<nvr_changing_DB>.dbo.tlkAdmissionTypes'
exec master..xp_cmdShell 'osql -U sa -P sapwd -S nvr_changing_server -d my_dynamic_db_name -Q @SQL....'
vs. something like:
DECLARE @DB Varchar(50)
DECLARE @SQL Varchar(4000)
SET @DB = '<nvr_changing_server>.<my_dynamic_db_name>'
SET @SQL = 'SELECT admissiontype_id AS atype, admissiontype AS atype_desc, start_date, end_date
INTO ' + @DB +'.dbo.tlkAdmitType
FROM <nvr_changing_server>.<nvr_changing_DB>.dbo.tlkAdmissionTypes'
EXEC(@SQL)
The purpose of all this is...I need to pass a parameter for the DB that I will be inserting into...here we create a new db with a specific name based on quarterly data. We collect, crunch, validate data and ship it. Then when it's old we archive it then eventually delete it.
I have written a script that makes this quarterly build less painful. In fact I won't have to do it! :)...our Sr. Data Analysts will do it now. In order for this beautiful thing (*in my mind anyway*) to work they need to set parameters for which data to pull and where to put it. The DB is scripted into existance and the data is moved into it. So therefore they need to enter the Qtr,Yr and dbname. I have done DSQL before on smaller scripts and I am just curious if the expert pool here can shed some light on this approach. The script will most likely be run in a DTS SQL Task.
Thanks in advance...R
Running OSQL
Feb 25, 2004Hi,
When i want to run the OSQL utility, it will open the command prompt, but only for about a second, then, gone...
I'm running an XP pro machine with MSDE 2000 installed locally... any ieads??
thanks!
OSQL Question
Mar 18, 2004I created a sql script that transfers data from my test system to production adn vice versa. I would like to use OSQL and be able to pass parameters to the SQL script.
Example: param 1 = test; param 2 = production.
Can anyone tell me if this is possible and how to do it?
Thanks in advance for all the help.
Error With Osql
Apr 21, 2004Locally executing osql to create db files on a remote Webserver, I get this error:
41> 42> 43> 44> 45> Meldung 916, Ebene 14, Status 1, Server USER-EQ9OV60EMF, Zeile 12
Server user 'Robse_Testlogin' is not a valid user in database 'msdb'.
(The above is german and translates into "Message 916, Level 14,..., Row 12")
This is the command with parameters:
osql -S servername -U Robse_Testlogin -P password -i c:Storestoredb.sql"
Up to that line, everything works fine...what could be wrong?
Osql Formatting
May 5, 2004At 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.
Osql / Isql
Jan 17, 2008Is osql/isql supported in SQL 2005? How about in 2008? Thanks
View 2 Replies View RelatedOsql Timeout
Jul 23, 2005Hi,I have several big tables with rows more than 25 mil rowsand to update/delete/insert data in these tables,it can take minutes.I use BULK Insert/DELETE/Update with osql.While I run one of these updates,if I try to select, it seems like both read and write get locked.Shouldn't SQL resolve this kind of locking?I left these to see if it gets resolved but both never returned.So I need to kill these processes.Does anyone have any scripts to find how long queries are running?Also I need to make osql timeout and tried -t but it didn't work.I used -t 1200 with DELETE in osql but it was running for more than 40minutes. So I killed it and ran DBCC DBREINDEX on the table and re-ranit and it worked.Shouldn't the query get killed after 10 minutes?What is exactly -t option for ?thanks,
View 2 Replies View RelatedOsql Feedback
Jul 23, 2005I have some long running scripts which I fire at my database using osql.(These are big files and mostly doing inserts but some also do a few otherthings.) It would be nice to have some activity indication (other than thedisk activity light) that these are running. When I used to use Oracle,their equivalent to osql had an option to print a dot (without a carriagereturn) for every "n" statements. This gave a nice "I'm alive" indicator. Ican simulate this by adding a few "print" statements in my sql, but printalways adds a carriage return. Does anyone know a way of doing a print butwithout the addition of a CR (or CR/LF)? So that a second "print" sends itsoutput to the same line as the first?I know this is a nicety and I can live without it, but it would be nice.thanks in advance,Brianwww.cryer.co.uk/brian
View 4 Replies View RelatedUsing OSQL And DTSRUN
Mar 21, 2006I have a question. I am doing some work for someone and I have a batchfile that they can run that will execute an OSQL line and a DTSRUNline. In both lines I run them using the /S /U /P switches and ofcourse the /N or /i switch to tell it what to run. I have also triedreplacing the /U /P switches with the /E switch.My problem is that as long as I specify the users password on the OSQLline (either with /U & /P or with /E & /P) it will run. If I try andjust use the /E it will say password failed for DOMAIN/USER . Ok, Idon't really care I can specify the password and the script will run.However no matter what I do the DTSRUN line will not run, it gives methis same password error.I can run this line just fine on my PC on my network and my domainusing just the /S /E switches.Any ideas as to why it will work for OSQL but not DTSRUN?Thanks in advance.
View 5 Replies View RelatedUsing OSQL For BCP Handling
Feb 1, 2008I find this thread very useful. But how can I run those scripts in command line using OSQL???
View 5 Replies View RelatedWhat's Going On With OSQL On My Computer??
Mar 20, 2008I have not seen this ever on my computer until today...
I start CuteFTP or NetObjects Fusion 8 and I get an installer window popping up saying:
"Please wait while Microsoft configures Microsoft SQL desktop engine"
ZoneAlarm warns me that OSQL.exe is trying to connect to my network.
I let it.
Then ZA warns me that OSQL is trying to connect to the internet.
At this point, if I let it, or deny it, the thing installer (which looks to be half way done) reloads itself and starts over with ZA warning that it's trying to connect locally, then to the internet. Over and over this happens.
It wont let either of my program start until it goes through about 5 or 6 cycles of whatever it's trying to do.
Then it just disappears and my program will load.
If I close either CuteFTP of NetObjects and start them back up, the whole OSQL.exe thing starts again.
I have rebooted and it still does this.
The odd thing is some of the IP addresses it's trying to connect to.
72.14.207.104 - Google
96.6.129.187 - Akamai
207.46.248.249 - Microsoft (ok, that's understandable)
I didn't write the IP address, but it tried to get to Hotwired.com too..
How do I fix this? Why is it doing this?
I'm running XP Pro. P-4 3Ghz 1.5Gb RAM
Thanks for any help!
-Rich
OSQL Restore Database
Jul 12, 2004Hi
I need to do a restore similar to the Restore Database in SQL Enterprise Manager using OSQL. I created an app to do the OSQL Run script command and it works fine. Is this the best way to create a setup to restore the database? Any ideas please!!
Best Regards
Philip