Locally 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")
I am facing one problem while running below given query from SSMS or QA while the same query running fine while running through osql at command prompt. while running through command prompt it is giving correct value like 0,1,2 or 3 . With SSMS everytime it is giving null as result.
How can I create a new database on sql server using OSQL command incommand prompt. I have the sql file for database creation and I use thefollowing commandC:> osql -S servername -U sa -i db1.sqlit prompt my for password n i enter it since sa has a blank passwordbut after that it returns the following error.1> 2> 3> Msg 170, Level 15, State 1, Server servername, Procedure ,Line 2[Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrectsyntax near 'COLLATE'.1> 2> 3> Msg 15010, Level 16, State 1, Server servername, Proceduresp_dboption, Line 95[Microsoft][ODBC SQL Server Driver][SQL Server]The database'db1' does not exist. Use sp_helpdb to show available databasesCan anyone help me on that?
I get following error trying to use SQLCMD OR OSQL
Named Pipes Provider: Could not open a connection to SQL Server [2]. Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi shing a connection to the server. When connecting to SQL Server 2005, this failu re may be caused by the fact that under the default settings SQL Server does not allow remote connections.. Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
I searched this forum for answers and I found one solution where it was said that Remote connections for TCP and Named Pipes needs to be enabled. I have them enabled, but I still get this error. I had working SQL server before, but I had to uninstall it. Now after installing ti again I started to get this error.
Anything else besides disabled remote connections cause this problem? I'm trying a local connection. I heard elsewhere that this could be firewall related, but shouldn't it use local connection always when I try to connect it with sqlcmd and no other parameters given?
I have a process that is running on the os. This process is picking up FTP files every 5 min, it renames them so not to confuse them with the new files, copies all renamed files into one file to be used by bulk insert, runs the bulk insert to populate a table, and then runs the stored procedure that scrubbing the data and insert it into another table. For every transaction that I do in my stored procedure, I do the error checking as follows:
IF @@error <> 0 BEGIN ROLLBACK TRANSACTION RETURN
If my stored procedure encounters an error, return statement will stop it from running. If this happens, I need to stop the process that is running on the os as well.
Questions:
How can that be accomplished?
How to restart the stored procedure ones the error has been corrected?
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 ?
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.
i'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 ......
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?
using 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'
Hi 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
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
"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"
Hello 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?
I 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.....
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'
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.
I 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?
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?
Hi,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,
I 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
I 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.
I 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..
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!!
Does anyone know of a way to prevent the results of an OSQl query from breaking into multiple lines. The results come in a mess I need to clean them up to import into a spreadsheet.
I am trying to set up a command line so a user can use osql to run a stored procedure on SQL Server 2000. An example of what I am using looks like this: osql -E -Q "EXEC Penguin.Foot.dbo.sp_who". When I run this I get an error that the login failed for the user. However, this user can run the stored procedure from Query analyzer. I've not found a lot of info on osql and any help would be greatly appreciated.
When using osql from a command prompt the following script was run: EXEC sp_password 'Current Password', '', 'TestLogin'
We were trying to set a password for a login to blank, but now cannot access login into the application because the application was looking a blank password for this user but the password would not work for blank.
Note ---This client only has MSDE installed, not SQL Enterprise.
If the script is run from Query Analyzer it works fine.
The script should have been this: EXEC sp_password 'Current Password', Null, 'TestLogin'
It should have explictly stated NULL for the new password but it wasn't done.
Is there anyway to reset the password without knowing the existing password. No other logins exist. They tried logging into the system with no password, '',"". I am not sure what the password was set to.
