SQL Query In Batch File Problem
Aug 11, 2006
Hi,
I am trying to run this query in a batch file but am getting an "Incorrect syntax near keyword 'and'" error. Any help would be much appreciated. Thanks, Jeff
ECHO OFF
set GatewayDB="SybaseHDR_Prod"
set DBUser="user"
set DBPswd="pswd"
set SQLServer="nnn.nn.nnn.nn"
set HdrctlNbr1 ="20060714000000000000"
set HdrctlNbr2 ="20060727999999999999"
ECHO ========================================================================
ECHO Start of Bulk Copy Process from Production Tables to Output Text Files
ECHO Database is : %GatewayDB%
ECHO Server : %SQLServer%
ECHO File AuthNbr: %AuthNbr%
ECHO ========================================================================
set TableName="hdr_ctl_nbr_status"
set outfile="C:BCPhdr_ctl_nbr_status.txt"
If exist %outfile% del %outfile%
bcp "SELECT * FROM %GatewayDB%..%TableName% where hdr_ctl_nbr between %HdrctlNbr1% and %HdrctlNbr2%" queryout %outfile% -b1000 -c -t~ -U %DBUser% -P %DBPswd% -S%SQLServer%
REM pause
:END
ECHO ================================
ECHO Bulk Copy process is completed !
ECHO ================================
pause
View 5 Replies
ADVERTISEMENT
Aug 7, 2007
HELP,
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
View 4 Replies
View Related
Nov 2, 2007
Hi all,
I have the "Northwind" database in my Sql Server Management Studio Express.
In my C:ProSSEAppsSamplesForChapter02Chapter02 folder, I have the following 2 files:
(1) ListColumnValues (MS-DOS Batch File)
sqlcmd -S .sqlexpress -v DBName = "Northwind" CName = "CompanyName" TName =
"Shippers" -i c:prosseappschapter02ListListColumnVales.sql -o
c:prosseappschapter02ColumnValuesOut.rpt
(2) ListColumnValues (Microsoft SQL Server Query File)
USE $(Northwind)
GO
SELECT $(CompanyName) FROM $(Shippers)
GO
When I ran the following SQLcmd:
C:ProSSEAppsSamplesForChapter02Chapter02>ListColumnValues.bat
I got the following "ColumnValuesOut.rpt" with error messages:
'Northwind' scripting variable not defined.
Msg 102, Level 15, State 1, Server L1P2P3SQLEXPRESS, Line 1
Incorrect syntax near '$'.
'CompanyName' scripting variable not defined.
'Shippers' scripting variable not defined.
Msg 102, Level 15, State 1, Server L1P2P3SQLEXPRESS, Line 1
Incorrect syntax near 'CompanyName'.
I copied these T-SQL statements from a book and I do not know how to correct them.
Please help and tell me how to correct these errors.
Thanks in advance,
Scott Chang
View 3 Replies
View Related
Jan 1, 2005
Dear All,
i need to put a query in a batch file to be easy to use by the user.
jusy only double click on it and it will work.
can any one help?
View 10 Replies
View Related
Sep 19, 2014
I'm trying to export a query to a csv file from a batch file.
batch file:
sqlcmd -S loni-sbs -d lonikahn0207 -E -Q "select * from ael" -o "AELORI.csv" -h-1 -s"," -w 700
BUT, I'm getting a user name and password issue inside the csv file:
Cannot open database "lonikahn0207" requested by the login. The login failed.
Msg 18456
Login failed for user 'LONIBOOKSAdministrator'.
View 1 Replies
View Related
Mar 25, 2008
Hello,
I am looking for some way to run a sql query from a batch file on the SQL server. The query needs a user entered parameter, if that can be accomodated it would be great.
I read about some osql command & tried to run it but it kept saying "Server does not exist or access denied."
Please give me the steps to go about this.
Thanks,
-R
View 4 Replies
View Related
Apr 16, 2001
How can I call a batch file from within Query Analyzer, which is the same batch I'm using with isqlw command.
Thanks
View 3 Replies
View Related
Dec 5, 2006
I 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
View 1 Replies
View Related
Jan 5, 2005
The Sql Server database can only see the local drive.
I would like to set up a batch file that will copy a SQL Server
backup file from the local drive to the network drive. I would
like to append the file date to the end of the copied file. I
assume a batch file can accomplish this but I am new to batch
file writing. Does anyone have code that they already created
for this sort of task??
Thank you!
View 13 Replies
View Related
Jan 22, 2002
hi,
I need some help in accomplishing this task.
I want to design a DTS task which will:
a)copy a certain given files from one directory to another
b)import the files into the tables
c)upon successful import delete the files from the original directory.
I done know much about scripting and need help in figuring out steps a) and c).
thanks
Zoey
View 2 Replies
View Related
Sep 13, 2001
Do u know how to write a batch file
for example i will need to type the sql server name it has to connect to the server and run a script that I have
let me know if u have any ideas of doing it
View 2 Replies
View Related
Sep 13, 2001
Do u know how to write a batch file
for example i will need to type the server name it has to connect to the server and run a script that I have
let me know if u have any ideas of doing it
View 3 Replies
View Related
Mar 29, 2000
Can someone show me an example of the syntax required to execute multiple BCP commands within the same batch (*.bat) file?
Sorry if this is a bit of a basic question, but not being a programmer by profession, I need to plead ignorance. I've tried a few things, but I just can't seem to figure it out.
Thanks!
Rich
View 4 Replies
View Related
Apr 3, 2007
Hi pals,
I need a small help from u all.
I need a Windows batch file script which does the ftp to remote machine and "puts" a file
say "data.xls" from a predefined dir say "c:uploads" and puts on the database server "d:dumps".
Regards,
Franky
View 2 Replies
View Related
Jul 20, 2005
I have a windows batch file that executes a SQL Server bcp command. Iwould like to obtain a return code if the bcp command fails. However,I cannot seem to find the return code (if any) for bcp. For example,if the bcp command is improperly formatted, or has a bad password, Iwant the batch file to return an error. Right now, my batch filesimply executes and returns success, even when the bcp command fails.Has anyone run into this before?Thanks!
View 3 Replies
View Related
Mar 14, 2006
Hey all.. I am wanting my SQL Server installation to restart its services at a time I define in the Scheduled Tasks feature of the server its on
I need help with command line commands to:- Stop SQL Server Agent service- Stop and Start SQL Server service- Restart SQL Server Agent service
Thanks in advance
View 1 Replies
View Related
Nov 6, 2000
Hi all,
I am executing a backup batch job scheduled daily. It runs
successfully but havent backed up any of the databases. The message
is
"The name specified is not recognized as an
internal or external command, operable program or batch file."
I am trying this for a full day and i gave up..please can anyone help
me or suggest me what to do?..The same batch file is executed in all
servers and is working fine..
Thank you in advance
Rani.
View 2 Replies
View Related
Mar 31, 2000
Hi,
I would like to ask someone who could help me with one small problem:
I would like to run SQL server task on one of my servers, that will connect
to all other servers (including ones that are not SQL servers, ie: Exchange,
Test Server etc) one at the time, and that will execute one batch file (update.bat) that is residing on each server under C: drive.
Batch file will copy some things from all of the servers to one server.
Thanks, in advance.
Regards,
Vilko
View 1 Replies
View Related
Oct 21, 1998
Hi,I want to Utilize the schedule feature in window NT to run a batch file which will migrate data from a text file into an Sql server 6.5 table.
here is the senario,
I want to program window NT using AT,For, Start etc. I do not know how and have not yet succeeded. Please help?
the case:
I already wrote a code using bcp in a batch file as follow
if exist c:authors.out bcp pubs..authors in c:authors.out /Sservername /Usa /Ppassword /fp:autors.fmt
so the command line is basically if the file named authors.out exists in drive c: then run the bcp command. here is the problem, how can I run this code in the first place? A I going to use the AT command in window NT to run this code on a specific day and time to check if the author.out file exists.
Please help , I am in urgent need for a solution
You can write back to me at a.alhussein@mci.com
Thanks again for your time and effort to help me
regards
Ali
View 3 Replies
View Related
Apr 5, 2006
I'm trying schedule a batch file to run as a job from sql server agent. The batch file copies files from one server directory to a directory on another server. The batch file works properly when executed directly. The job is being executed under the sql service account login. I've given the service account access to both the source and destination directories.
When I try to run the job it fails with an "Access is denied" error on both the source and destination directories (as read from job history).
Any ideas?
View 4 Replies
View Related
Apr 15, 2004
Ok.. I thought I could do this but I can't get this to work.. I need to create a batch file to run an sql update statement.. This is simple but I can't get it to wortk.. How do I do it?
View 4 Replies
View Related
Jun 13, 2008
Hi All, I need to create a batch file to execute some 5-6 sql scripts. I have sql scripts for creating a table and related storedprocs and I need to generate a batch file to run all this scripts.
View 3 Replies
View Related
Sep 27, 2006
MS SQL Server 2005
i get the following error when trying to run this job as user1:
'Unable to start execution of step 1 (reason: Error authenticating proxy serv1user1, system error: Logon failure: unknown user name or bad password.). The step failed.'
and the following error when trying to run the same job as 'sql agent service account':
'Executed as user: serv1user1. The process could not be created for step 1 of job 0x3653ACCFFA46DD47B3703C87DDFD0717 (reason: 2). The step failed.'
>job:
job name: job1
server: (local)
connection: net1user1
owner: serv1user1
step type: operating system (cmdexec)
run as: user1
command:@echo off
copy C: est.txt C:folder
ECHO Job completed
PAUSE
>the sql server agent service is running under 'serv1user1' and works fine for other jobs running ssis packages.
>i have created the following credentials under security/credentials:
credential name: serv1user1
identity: serv1user1
password: same as sql server agent service
>i have created a proxy under the sql server agent/operating system (cmdexec):
proxy name: user1
credential name: serv1user1
active subsystem: operating system (cmdexec)
principals (sql login): serv1user1
thank you
View 2 Replies
View Related
Jul 25, 2007
Hi All,
I would like to embed some stored procedures in a batch file and execute it from the command prompt on windows.
I have no idea of how to embed a stored proc in a .bat file.
Can you please redirect me to the solution to this?
Thanks in advance
vishu
View 1 Replies
View Related
Mar 10, 2008
Hi
Is it possible using T-SQL to run a batch file located on different server, ie. PC1 has SQL Server on it, PC2 has the batchfile, I need to run the batchfile stored on PC2 on PC1.
Thanks
View 3 Replies
View Related
Aug 9, 2007
Is it possible to use sql to execute a batch file? I would like toexecute the following "C:BTWartend.exe /f=C:BTWToolboxFormatscarnum.btw /p", 6Thanks,Matt
View 6 Replies
View Related
Sep 28, 2006
This is not working.
Set oShell = CreateObject("WScript.Shell")
oShell.Exec "test.bat"
View 4 Replies
View Related
Mar 11, 2008
Hi
I am trying to run a batch file stored on different server from the one that SQL Server is on using T-SQL. I am using the following syntax:
EXEC master..XP_CMDShell '\hit1DEVArchivingArchivingDataFiles.zipdata.bat'
But it doesnt seem to be working, it states that it is not recognised as an internal or external command.
Any ideas?
Thanks
View 1 Replies
View Related
Jan 16, 2008
HI There,
I just created batch file to restart analysis services and the job for executing this bat file.
<<Batch File >>
NET STOP "SQL Server Analysis Services (MSSQLSERVER)"
NET START "SQL Server Analysis Services (MSSQLSERVER)"
And I did the following step in sql agent:
Create the job > Select CmdExec Job > In commend section, I wrote CALL C:/RestartSSAS.bat
When I run this job, it seems everything went right but at the end, the job was filed.
However, I don't see any error on this step since the service was stopped and started successfully.
The below is the error message:
Executed as user: D20WKZC1SYSTEM. C:WINDOWSsystem32>NET STOP "SQL Server Analysis Services (MSSQLSERVER)" The SQL Server Analysis Services (MSSQLSERVER) service was stopped successfully. C:WINDOWSsystem32>NET START "SQL Server Analysis Services (MSSQLSERVER)"The SQL Server Analysis Services (MSSQLSERVER) service is starting. The SQL Server Analysis Services (MSSQLSERVER) service was started successfully. Process Exit Code 0. The step failed.
I've also tried to create the package using 'Execute Process Task ' and run it.
And it's successful but when I schedule it in sql agent then it failes.
Any thoughts?
Please advise me and thanks in advance.
View 5 Replies
View Related
Aug 14, 2001
Hi,
I am pretty new to DBA job, so please help me if you can.
I have to schedule a sql job (in sql 7.0) in sql job schdeduler which contains a batch file (.bat) with parameters.
Right now I have to run this job manually using command prompt.
If anyone can suggest how to automate this job.
Thanks alot,
Jannat
View 3 Replies
View Related
Jun 28, 2000
Hi everyone!
I'm new using SQL server (7.0) and I would like to know if it is possible to create a batch file that will automaticly start a job...
If so, let me know and give me some piece of information (command, parameters)
or a web site, anything that will help me do so...
I'll be very grateful.
If not, let me know anyways
Thanks a lot in advance...
View 2 Replies
View Related
Aug 4, 1998
Similar to a previous thread, but not exactly. I have a batch file that takes
two date parameters. I have a SQL script that gets the values I want and assigns
them to variables, but I don`t know how to pass these to the batch.
I`m thinking of something like:
declare @myvar1 datetime, @myvar2 datetime
select @myvar1 = <some date value>
select @myvar2 = <some date value>
exec xp_cmdshell "c:mssqlscriptsMYBATCH.BAT" @myvar1 @myvar2
This just errors out when it reaches the variables in the last line. If I move
the variables inside the quotes, it passes them as literal text strings, not as
the assigned datetime values.
Any ideas? Is this possible?
Thanks,
Robert
View 3 Replies
View Related
Jun 1, 1999
Exec master..xp_cmdshell "bcp sdnetpro..nbtorder11 out d:databtorder11.txt /c /t /Sservername /Usa /Ppassword"
Hi, this code run fine from within sql server query window. I want to put this code in a batch file and run the batch file.. it did not work. someone told me the revised code is :
bcp sdnetpro..nbtorder11 out d:databtorder11.txt /c /t,/r -Sservername -Usa -Ppassword"
This is not working. Please if anyone knows how to fix this, I would appreciate. One more thing, how can I confirm that the bcp has successfully done. do I have to create a log file and if so what is the code to create this log file.
thanks
Ali
View 1 Replies
View Related