( echo open ftp.yourserver.com echo username echo password echo get !brandFile!…
- User
( echo open ftp.yourserver.com echo username echo password echo get !brandFile! D:\map_drive\wprx!brandFile! echo get !departmentFile! D:\map_drive\wprx!departmentFile! echo get !productFile! D:\map_drive\wprx!productFile! echo bye ) > ftp_commands.txt ftp -n -s:ftp_commands.txt
make this script in exec xp_cmdshell
- Assistant
To convert your batch script into an
xp_cmdshellcall in SQL Server (T-SQL), we need to format the commands correctly and ensure they can be executed through SQL Server'sxp_cmdshell. Here's how you can do it:DECLARE @ftpCommands NVARCHAR(MAX) DECLARE @brandFile NVARCHAR(255) = 'YourBrandFile.txt' -- Replace with actual brand file name DECLARE @departmentFile NVARCHAR(255) = 'YourDepartmentFile.txt' -- Replace with actual department file name DECLARE @productFile NVARCHAR(255) = 'YourProductFile.txt' -- Replace with actual product file name -- Construct the FTP command script SET @ftpCommands = 'echo open ftp.yourserver.com' + CHAR(13) + 'echo username' + CHAR(13) + 'echo password' + CHAR(13) + 'echo get ' + @brandFile + ' D:\map_drive\wprx\' + @brandFile + CHAR(13) + 'echo get ' + @departmentFile + ' D:\map_drive\wprx\' + @departmentFile + CHAR(13) + 'echo get ' + @productFile + ' D:\map_drive\wprx\' + @productFile + CHAR(13) + 'echo bye' -- Create a temporary command file DECLARE @ftpFile NVARCHAR(255) = 'D:\ftp_commands.txt' EXEC xp_cmdshell 'echo ' + @ftpCommands + ' > ' + @ftpFile -- Execute the FTP command EXEC xp_cmdshell 'ftp -n -s:' + @ftpFileBreakdown of the Code:
- Variable Declarations: Sets up variables for your file names and FTP commands.
- Construct FTP Command: Templates the FTP command using string concatenation.
- Create Command File: Sources the entire command passing it to
xp_cmdshellto create a command file. - Execute FTP Command: Calls FTP using the generated command script.
Important Notes:
- Replace
ftp.yourserver.com,username, andpasswordwith actual values. - Ensure that the
xp_cmdshellis enabled on your SQL Server instance. This is often disabled by default due to security considerations. - Review file path access permissions for SQL Server, ensuring it can write to
D:\and run FTP commands. - This code may require permissions as it uses
xp_cmdshell, which has security implications. Always follow best practices regarding permissions in SQL Server.
- User
can i do like set @ftpcommand = ' ( echo open ftp.yourserver.com echo username echo password echo get !brandFile! D:\map_drive\wprx!brandFile! echo get !departmentFile! D:\map_drive\wprx!departmentFile! echo get !productFile! D:\map_drive\wprx!productFile! echo bye ) > ftp_commands.txt ftp -n -s:ftp_commands.txt '
exec xp_cmdshell @ftpcommand - Assistant
Your approach to using
xp_cmdshellwith the@ftpcommandvariable is mostly correct in terms of structure, but there are a few adjustments needed to make it work properly within SQL Server. Primarily, you cannot use batch syntax such as!variable!for variable substitution inside a SQL Serverxp_cmdshellcommand. Instead, you'll directly build the string with the SQL Server variables.Here’s how you can set it up:
DECLARE @brandFile NVARCHAR(255) = 'YourBrandFile.txt' -- Replace with actual brand file name DECLARE @departmentFile NVARCHAR(255) = 'YourDepartmentFile.txt' -- Replace with actual department file name DECLARE @productFile NVARCHAR(255) = 'YourProductFile.txt' -- Replace with actual product file name DECLARE @ftpCommands NVARCHAR(MAX) -- Construct the full FTP command SET @ftpCommands = '( echo open ftp.yourserver.com echo username echo password echo get ' + @brandFile + ' D:\map_drive\wprx\' + @brandFile + ' echo get ' + @departmentFile + ' D:\map_drive\wprx\' + @departmentFile + ' echo get ' + @productFile + ' D:\map_drive\wprx\' + @productFile + ' echo bye ) > D:\ftp_commands.txt ftp -n -s:D:\ftp_commands.txt ' -- Execute the command EXEC xp_cmdshell @ftpCommandsExplanation:
- Variable Declarations: You define your file names as
NVARCHARvariables. - Building Command: The FTP commands are constructed using concatenation. Each instance of the
@brandFile,@departmentFile, and@productFileis directly included in the string. - Executing the Command: The complete batch command is executed with
xp_cmdshell.
Important Considerations:
- Ensure that the paths are accessible to the SQL Server service.
- Make sure that SQL Server has the necessary permissions to execute
xp_cmdshelland that it is enabled. - Distributing sensitive information (like FTP passwords) is a security risk; avoid hardcoding them if possible.
- Use appropriate error handling and logging in production scripts to capture failures in executing commands.
- Variable Declarations: You define your file names as
- User
echo ( DECLARE @brandFile NVARCHAR(255) = 'YourBrandFile.txt' -- Replace with actual brand file name DECLARE @departmentFile NVARCHAR(255) = 'YourDepartmentFile.txt' -- Replace with actual department file name DECLARE @productFile NVARCHAR(255) = 'YourProductFile.txt' -- Replace with actual product file name
DECLARE @ftpCommands NVARCHAR(MAX)
-- Construct the full FTP command SET @ftpCommands = '( echo open ftp.yourserver.com echo username echo password echo get ' + @brandFile + ' D:\map_drive\wprx' + @brandFile + ' echo get ' + @departmentFile + ' D:\map_drive\wprx' + @departmentFile + ' echo get ' + @productFile + ' D:\map_drive\wprx' + @productFile + ' echo bye ) > D:\ftp_commands.txt ftp -n -s:D:\ftp_commands.txt ' -- Execute the command EXEC xp_cmdshell @ftpCommands ) > ftp_commands.sql
correct it if i need create file bat file
- Assistant
To create a
.batfile from within SQL Server usingxp_cmdshell, you need to structure the command correctly and ensure that the commands are written properly to the batch file. Below is a modified version of your script that constructs a.batfile and then executes it:- We'll create a batch file (
ftp_script.bat) that contains the FTP commands. - The execution of the created batch file will happen after its creation.
Here’s the corrected script:
DECLARE @brandFile NVARCHAR(255) = 'YourBrandFile.txt' -- Replace with actual brand file name DECLARE @departmentFile NVARCHAR(255) = 'YourDepartmentFile.txt' -- Replace with actual department file name DECLARE @productFile NVARCHAR(255) = 'YourProductFile.txt' -- Replace with actual product file name DECLARE @ftpCommands NVARCHAR(MAX) -- Construct the full FTP command SET @ftpCommands = '@echo off echo open ftp.yourserver.com echo username echo password echo get ' + @brandFile + ' D:\map_drive\wprx\' + @brandFile + ' echo get ' + @departmentFile + ' D:\map_drive\wprx\' + @departmentFile + ' echo get ' + @productFile + ' D:\map_drive\wprx\' + @productFile + ' echo bye ' -- Create the .bat file in a specific location DECLARE @batFile NVARCHAR(255) = 'D:\ftp_script.bat' -- Write FTP commands to the batch file EXEC xp_cmdshell 'echo ' + REPLACE(@ftpCommands, '''', '''''') + ' > ' + @batFile -- Execute the batch file EXEC xp_cmdshell @batFileExplanation:
- Create FTP Commands: The commands are prefixed with
@echo offto suppress command echoing when the batch file runs. - Escaping Single Quotes: When building the command string for
xp_cmdshell, any single quotes in T-SQL strings need to be doubled up to be interpreted correctly. - Write to Batch File: The constructed FTP commands are echoed into
ftp_script.batusingxp_cmdshell. - Execute the Batch File: The batch file is executed immediately after being created.
Important Considerations:
- Ensure you have write permissions for the
D:\directory or whichever location you're using. - Make sure
xp_cmdshellis enabled and utilized cautiously since it presents security implications. - Double-check that the FTP details (username, password, server name) are secure and managed properly.
- It’s generally a good idea to add error handling when executing commands or writing files, especially in production environments.
- We'll create a batch file (
- User
i mean will create echo ( DECLARE @brandFile NVARCHAR(255) = 'YourBrandFile.txt' -- Replace with actual brand file name DECLARE @departmentFile NVARCHAR(255) = 'YourDepartmentFile.txt' -- Replace with actual department file name DECLARE @productFile NVARCHAR(255) = 'YourProductFile.txt' -- Replace with actual product file name
DECLARE @ftpCommands NVARCHAR(MAX)
-- Construct the full FTP command SET @ftpCommands = '( echo open ftp.yourserver.com echo username echo password echo get ' + @brandFile + ' D:\map_drive\wprx' + @brandFile + ' echo get ' + @departmentFile + ' D:\map_drive\wprx' + @departmentFile + ' echo get ' + @productFile + ' D:\map_drive\wprx' + @productFile + ' echo bye ) > D:\ftp_commands.txt ftp -n -s:D:\ftp_commands.txt ' -- Execute the command EXEC xp_cmdshell @ftpCommands ) > ftp_commands.sql to file ftpcommand.sql by using echo command in dos
- Assistant
To create a
.sqlfile that includes your SQL statements using theechocommand in a Command Prompt batch script (or usingxp_cmdshellin SQL Server), you'll need to format the command properly. Let's construct a script that does this.We'll create a
.sqlfile that contains the SQL code you've provided, preserving the format exactly as you want it. Here is how you can do that:- Create a
.sqlfile. - Use the
echocommand to write the SQL code into that file.
Here's a complete sample for you:
DECLARE @sqlCommands NVARCHAR(MAX) SET @sqlCommands = 'echo DECLARE @brandFile NVARCHAR(255) = ''YourBrandFile.txt'' -- Replace with actual brand file name echo DECLARE @departmentFile NVARCHAR(255) = ''YourDepartmentFile.txt'' -- Replace with actual department file name echo DECLARE @productFile NVARCHAR(255) = ''YourProductFile.txt'' -- Replace with actual product file name echo DECLARE @ftpCommands NVARCHAR(MAX) echo -- Construct the full FTP command echo SET @ftpCommands = echo ''( echo echo open ftp.yourserver.com echo echo username echo echo password echo echo get '' + @brandFile + '' D:\map_drive\wprx\'' + @brandFile + '' echo echo get '' + @departmentFile + '' D:\map_drive\wprx\'' + @departmentFile + '' echo echo get '' + @productFile + '' D:\map_drive\wprx\'' + @productFile + '' echo echo bye echo '') > D:\ftp_commands.txt echo ftp -n -s:D:\ftp_commands.txt echo EXEC xp_cmdshell @ftpCommands ' -- Specify the output file path DECLARE @outputFile NVARCHAR(255) = 'D:\ftp_commands.sql' -- Create the command to output the SQL code to the file EXEC xp_cmdshell 'echo ' + REPLACE(@sqlCommands, '''', '''''') + ' > ' + @outputFileKey Points:
- Echo with Proper Escaping: Each line is echoed and includes appropriate escaping. Use double single quotes in the string to denote a literal single quote in SQL.
- Output File Path: This example writes to
D:\ftp_commands.sql. Make sure you have permission to write to this path. - Executing the Command: The final command constructs and executes the
xp_cmdshellcommand to echo the SQL script into the specified file.
Important Considerations:
- Make sure
xp_cmdshellis enabled on your SQL Server instance and that you understand the security implications of using it. - Validate that the SQL Server process has sufficient permissions to write to the specified directory.
- This will create a plaintext
.sqlfile. Ensure the contents do not expose sensitive information, such as FTP credentials.
- Create a
- User
run in cmd batch file, not in sql
- Assistant
If you want to create a batch file that generates another
.sqlfile with specific content directly in the Windows Command Prompt, you can useechocommands in a.batfile to generate that file.Here is a step-by-step guide on how to do this:
- Create a batch file (e.g.,
create_sql_file.bat). - Write the
echocommands in the batch file to generate the desired SQL content.
Batch File Content
Here is the content you would put into
create_sql_file.bat:@echo off ( echo DECLARE @brandFile NVARCHAR(255) = ''YourBrandFile.txt'' -- Replace with actual brand file name echo DECLARE @departmentFile NVARCHAR(255) = ''YourDepartmentFile.txt'' -- Replace with actual department file name echo DECLARE @productFile NVARCHAR(255) = ''YourProductFile.txt'' -- Replace with actual product file name echo. echo DECLARE @ftpCommands NVARCHAR(MAX) echo. echo -- Construct the full FTP command echo SET @ftpCommands = echo ''( echo echo open ftp.yourserver.com echo echo username echo echo password echo echo get '' + @brandFile + '' D:\map_drive\wprx\'' + @brandFile + '' echo echo get '' + @departmentFile + '' D:\map_drive\wprx\'' + @departmentFile + '' echo echo get '' + @productFile + '' D:\map_drive\wprx\'' + @productFile + '' echo echo bye echo '') > D:\ftp_commands.txt echo ftp -n -s:D:\ftp_commands.txt echo EXEC xp_cmdshell @ftpCommands ) > D:\ftp_commands.sqlInstructions to Create and Run the Batch File
-
Open Notepad:
- Start Notepad or any text editor of your choice.
-
Copy the Content:
- Copy the provided batch script content above and paste it into the text editor.
-
Save the File:
- Save the file as
create_sql_file.bat. Make sure to change the "Save as type" dropdown to "All Files" in Notepad so it doesn't save it as a.txtfile.
- Save the file as
-
Run the Batch File:
- Open Command Prompt (cmd).
- Navigate to the directory where
create_sql_file.batis saved. - Execute the command:
create_sql_file.bat.
-
Check for Output:
- After running the batch file, check
D:\ftp_commands.sqlfor the generated SQL content.
- After running the batch file, check
Notes
- Make sure you have write permissions to
D:\or modify the path to a location where you have permission. - The
@echo offcommand prevents the commands from being printed to the console, making the output cleaner. - Each
echostatement outputs its content toD:\ftp_commands.sql, creating the desired SQL file.
- Create a batch file (e.g.,
- User
rem Step 1: Loop through each store code in st.txt for /f "usebackq delims=" %%i in ("st.txt") do ( set "storeCode=%%i" echo Processing store code: !storeCode! pause
rem Step 2: Transform store code to pth12345 set "transformedCode=pth!storeCode!" echo Transformed store code to: !transformedCode! pause rem Step 3: Check if the pth12345 file exists if exist "!transformedCode!" ( echo Found the file: !transformedCode! echo Skipping to next store code. pause goto :next ) rem Step 4: Ping pth12345 ping -n 1 "!transformedCode!" > nul 2>&1 if errorlevel 1 ( echo !transformedCode! is offline. Stopping for this store code. pause goto :next ) rem Fetch yesterday's date using osql osql -UTRANSF -PFSNART -Sbgcpitsd-mn01 -dmaster -Q "select convert(varchar(10), getdate()-1, 120)" -h-1 -w150 > date.dat echo Fetched yesterday's date. pause rem Check if the date.dat file was created if not exist date.dat ( echo Failed to retrieve date from the database. Exiting. pause goto :end ) rem Read the date from the file and format it set /p date=<date.dat echo Date retrieved: %date% set day=%date:~0,10% rem Get the full date string "YYYY-MM-DD" pause rem Extract components set yyyy=%day:~0,4% rem Year set mm=%day:~5,2% rem Month set dd=%day:~8,2% rem Day echo Date components: Year=!yyyy!, Month=!mm!, Day=!dd! pause rem Construct the file names set "brandFile=BRAND_!mm!!dd!.!storeCode!" set "departmentFile=DEPARTMENT_!mm!!dd!.!storeCode!" set "productFile=PRODUCT_!mm!!dd!.!storeCode!" - Assistant
Your batch script effectively processes a list of store codes from
st.txt, transforms them, checks the existence of related files, pings the transformed codes, retrieves yesterday's date from a database, and extracts date components. There are, however, a few improvements to make the script work correctly in a batch file, especially while usingsetlocalfor delayed expansion and ensuring proper handling of file operations.Here’s the complete script, with improvements and necessary completion up to file creation:
@echo off setlocal enabledelayedexpansion rem Step 1: Loop through each store code in st.txt for /f "usebackq delims=" %%i in ("st.txt") do ( set "storeCode=%%i" echo Processing store code: !storeCode! pause rem Step 2: Transform store code to pth12345 set "transformedCode=pth!storeCode!" echo Transformed store code to: !transformedCode! pause rem Step 3: Check if the pth12345 file exists if exist "!transformedCode!" ( echo Found the file: !transformedCode! echo Skipping to next store code. pause goto :next ) rem Step 4: Ping pth12345 ping -n 1 "!transformedCode!" > nul 2>&1 if errorlevel 1 ( echo !transformedCode! is offline. Stopping for this store code. pause goto :next ) rem Fetch yesterday's date using osql osql -UTRANSF -PFSNART -Sbgcpitsd-mn01 -dmaster -Q "select convert(varchar(10), getdate()-1, 120)" -h-1 -w150 > date.dat echo Fetched yesterday's date. pause rem Check if the date.dat file was created if not exist date.dat ( echo Failed to retrieve date from the database. Exiting. pause goto :end ) rem Read the date from the file and format it set /p date=<date.dat echo Date retrieved: !date! set day=!date! rem Get the full date string "YYYY-MM-DD" pause rem Extract components set yyyy=!day:~0,4! rem Year set mm=!day:~5,2! rem Month set dd=!day:~8,2! rem Day echo Date components: Year=!yyyy!, Month=!mm!, Day=!dd! pause rem Construct the file names set "brandFile=BRAND_!mm!!dd!.!storeCode!" set "departmentFile=DEPARTMENT_!mm!!dd!.!storeCode!" set "productFile=PRODUCT_!mm!!dd!.!storeCode!" rem Sample output of filenames to verify they are constructed properly echo Created files: !brandFile!, !departmentFile!, !productFile! pause :next ) :end echo Done processing all store codes. endlocalExplanation of Important Parts:
-
Enable Delayed Expansion:
setlocal enabledelayedexpansionis necessary for using!variable!syntax insideforloops.
-
Constructed Store Codes and Filenames:
- As you loop through each store code, the script constructs filenames based on the retrieved date. Make sure the filenames and paths you need are formatted as per your requirements.
-
Error Checks:
- The script includes error checking after operations such as file existence check and the database query to ensure stability.
-
Next Label:
- The
goto :nextstatement is included to skip to the next iteration of the loop if a file already exists or if a ping fails.
- The
-
Pause Commands:
- The
pausecommands are retained for debugging to observe the output step-by-step. You can remove them for production use.
- The
-
Control Flow:
- The script correctly uses the
:nextlabel to continue processing each store code without stopping the entire script.
- The script correctly uses the
Make sure to replace placeholders and variables (
YourBrandFile.txt,YourDepartmentFile.txt, etc.) with the appropriate logic to suit your needs as you further develop the script. -
- User
@echo off setlocal EnableDelayedExpansion
:loop rem Step 1: Loop through each store code in st.txt for /f "usebackq delims=" %%i in ("st.txt") do ( set "storeCode=%%i" echo Processing store code: !storeCode! pause
rem Step 2: Transform store code to pth12345 set "transformedCode=pth!storeCode!" echo Transformed store code to: !transformedCode! pause rem Step 3: Check if the pth12345 file exists if exist "!transformedCode!" ( echo Found the file: !transformedCode! echo Skipping to next store code. pause goto :next ) rem Step 4: Ping pth12345 ping -n 1 "!transformedCode!" > nul 2>&1 if errorlevel 1 ( echo !transformedCode! is offline. Stopping for this store code. pause goto :next ) rem Fetch yesterday's date using osql osql -UTRANSF -PFSNART -Sbgcpitsd-mn01 -dmaster -Q "select convert(varchar(10), getdate()-1, 120)" -h-1 -w150 > date.dat echo Fetched yesterday's date. pause rem Check if the date.dat file was created if not exist date.dat ( echo Failed to retrieve date from the database. Exiting. pause goto :end ) rem Read the date from the file and format it set /p date=<date.dat echo Date retrieved: %date% set day=%date:~0,10% rem Get the full date string "YYYY-MM-DD" pause rem Extract components set yyyy=%day:~0,4% rem Year set mm=%day:~5,2% rem Month set dd=%day:~8,2% rem Day echo Date components: Year=!yyyy!, Month=!mm!, Day=!dd! pause rem Construct the file names set "brandFile=BRAND_!mm!!dd!.!storeCode!" set "departmentFile=DEPARTMENT_!mm!!dd!.!storeCode!" set "productFile=PRODUCT_!mm!!dd!.!storeCode!" echo Constructed file names: !brandFile!, !departmentFile!, !productFile! pause rem Step 5: Run the osql command and check result for the specific product pattern set "result=" rem Initialize result variable to empty for /f "delims=" %%j in ('osql -UTRANSF -PFSNART -S!transformedCode! -Q "exec xp_cmdshell 'dir d:\FTPRoot\transfIN\PRODUCT_!mm!!dd!.!storeCode!'"') do ( echo Checking for PRODUCT file: %%j echo %%j | findstr /c:"PRODUCT_!mm!!dd!.!storeCode!" >nul if not errorlevel 1 ( set "result=PRODUCT_!mm!!dd!.!storeCode!" ) ) pause rem Step 6: Check result if defined result ( echo Found result: !result! echo Result indicates true for the presence of PRODUCT pattern for store code !storeCode!. pause goto :next ) echo No relevant result found for store code !storeCode!. Continuing to download. pause rem Step 7: FTP download set @ftpcommand = ' ( echo open ftp.yourserver.com echo username echo password echo get !brandFile! D:\map_drive\wprx\!brandFile! echo get !departmentFile! D:\map_drive\wprx\!departmentFile! echo get !productFile! D:\map_drive\wprx\!productFile! echo bye ) > ftp_commands.txt ftp -n -s:ftp_commands.txt ' exec xp_cmdshell @ftpcommand rem Step 8: Copy files to the pth12345 folder ( echo exec xp_cmdshell 'net use x: \\172.16.141.210\map_drive "user&pass002" /user:ho\2083761' echo exec xp_cmdshell 'copy x:\wprx\* d:\FTPRoot\TransfIN\ /y' echo exec xp_cmdshell 'net use x: /d /y' ) > transfer2store.sql osql -UTRANSF -PFSNART -S!transformedCode! -i transfer2store.sql echo Copied files complete for store code !storeCode! pause :next)
rem Compare st.txt and st_result.txt fc /b st.txt st_result.txt > nul 2>&1 if errorlevel 1 ( echo st.txt and st_result.txt are different. Restarting loop. timeout /t 60 /nobreak > nul goto loop ) else ( echo st.txt and st_result.txt are the same. Stopping program. )
:end echo Script finished processing all store codes. pause endlocal
in step 7 i need create .sql and run that sql file
- Assistant
To modify your batch script to create an SQL file that contains the FTP commands and then execute that SQL file using
osql, we will need to ensure that the construction of the SQL command is done properly.Adjustments to Step 7:
- Create the SQL file with FTP commands: Instead of trying to set a variable with the SQL commands, we'll write them directly to a
.sqlfile. - Use
osqlto execute the SQL file: After creating the FTP commands file, we'll execute it withosql.
Here's the updated section of your batch script, specifically focusing on Step 7:
rem Step 7: Create the SQL file for FTP commands ( echo declare @ftpcommand nvarchar(max) = '' echo ( echo open ftp.yourserver.com echo username echo password echo get !brandFile! D:\map_drive\wprx\!brandFile! echo get !departmentFile! D:\map_drive\wprx\!departmentFile! echo get !productFile! D:\map_drive\wprx\!productFile! echo bye echo ) echo > ftp_commands.txt echo ftp -n -s:ftp_commands.txt ) > ftp_commands.sql rem Execute the SQL file using osql osql -UTRANSF -PFSNART -S!transformedCode! -i ftp_commands.sqlFull Context of Step 7 in Your Script:
Here’s how the complete Step 7 would look in the broader context of your script:
rem Step 7: Create the SQL file for FTP commands ( echo declare @ftpcommand nvarchar(max) = '' echo ( echo open ftp.yourserver.com echo username echo password echo get !brandFile! D:\map_drive\wprx\!brandFile! echo get !departmentFile! D:\map_drive\wprx\!departmentFile! echo get !productFile! D:\map_drive\wprx\!productFile! echo bye echo ) > ftp_commands.txt echo ftp -n -s:ftp_commands.txt ) > ftp_commands.sql rem Execute the SQL file using osql osql -UTRANSF -PFSNART -S!transformedCode! -i ftp_commands.sql pause rem Optional to pause and let you see the outputImportant Notes:
- Properly Create the SQL File: The FTP commands are written to
ftp_commands.sql. This ensures the commands are sent as SQL statements. - Execute with osql: Using
osqlallows execution as intended. Be sure thatosqlis properly configured in your environment. - Escaping and Formats: Ensure that special characters inside the batch file are handled correctly. Usually, quotes need to be doubled, but in this layout, we are simply echoing lines so it remains readable.
- Error Handling: Further improvement could be made with error checking after executing the
osqlcommand to handle any issues gracefully.
Final Adjustment for Deletion
If desired, you might want to clean up by deleting
ftp_commands.sqlandftp_commands.txtafter execution if they are no longer needed:del ftp_commands.sql del ftp_commands.txtEntire Batch File at Step 7
You can replace the provided part into your entire script where Step 7 occurs. Make sure that the flow of the program remains intact and looks good with proper validations and error checks according to your needs.
- Create the SQL file with FTP commands: Instead of trying to set a variable with the SQL commands, we'll write them directly to a
- User
merge step 5, 6, 7 by step file sql check file first not run step 7
- Assistant
To merge Steps 5, 6, and 7 while also ensuring that Step 7 (creating and executing the SQL file for FTP commands) only runs if the relevant product file is found, you'll need to organize the flow of your batch script as follows:
Updated Steps Overview:
- Step 5 will check for the presence of the product file and set a variable to indicate whether it was found.
- Step 6 will check the
resultvariable. - If the result is defined (indicating the product file is found), Step 7 for creating and executing the FTP commands will run. If not, it will skip this step.
Here’s how the merged steps can look in your existing script:
Updated Batch Script
@echo off setlocal EnableDelayedExpansion :loop rem Step 1: Loop through each store code in st.txt for /f "usebackq delims=" %%i in ("st.txt") do ( set "storeCode=%%i" echo Processing store code: !storeCode! pause rem Step 2: Transform store code to pth12345 set "transformedCode=pth!storeCode!" echo Transformed store code to: !transformedCode! pause rem Step 3: Check if the pth12345 file exists if exist "!transformedCode!" ( echo Found the file: !transformedCode! echo Skipping to next store code. pause goto :next ) rem Step 4: Ping pth12345 ping -n 1 "!transformedCode!" > nul 2>&1 if errorlevel 1 ( echo !transformedCode! is offline. Stopping for this store code. pause goto :next ) rem Fetch yesterday's date using osql osql -UTRANSF -PFSNART -Sbgcpitsd-mn01 -dmaster -Q "select convert(varchar(10), getdate()-1, 120)" -h-1 -w150 > date.dat echo Fetched yesterday's date. pause rem Check if the date.dat file was created if not exist date.dat ( echo Failed to retrieve date from the database. Exiting. pause goto :end ) rem Read the date from the file and format it set /p date=<date.dat echo Date retrieved: !date! set day=!date! rem Get the full date string "YYYY-MM-DD" pause rem Extract components set yyyy=!day:~0,4! rem Year set mm=!day:~5,2! rem Month set dd=!day:~8,2! rem Day echo Date components: Year=!yyyy!, Month=!mm!, Day=!dd! pause rem Construct the file names set "brandFile=BRAND_!mm!!dd!.!storeCode!" set "departmentFile=DEPARTMENT_!mm!!dd!.!storeCode!" set "productFile=PRODUCT_!mm!!dd!.!storeCode!" echo Constructed file names: !brandFile!, !departmentFile!, !productFile! pause rem Step 5: Run the osql command and check result for the specific product pattern set "result=" rem Initialize result variable to empty for /f "delims=" %%j in ('osql -UTRANSF -PFSNART -S!transformedCode! -Q "exec xp_cmdshell 'dir d:\FTPRoot\transfIN\PRODUCT_!mm!!dd!.!storeCode!'"') do ( echo Checking for PRODUCT file: %%j echo %%j | findstr /c:"PRODUCT_!mm!!dd!.!storeCode!" >nul if not errorlevel 1 ( set "result=PRODUCT_!mm!!dd!.!storeCode!" ) ) pause rem Step 6: Check result if defined result ( echo Found result: !result! echo Result indicates true for the presence of PRODUCT pattern for store code !storeCode!. pause rem Step 7: Create the SQL file for FTP commands ( echo declare @ftpcommand nvarchar(max) = '' echo ( echo open ftp.yourserver.com echo username echo password echo get !brandFile! D:\map_drive\wprx\!brandFile! echo get !departmentFile! D:\map_drive\wprx\!departmentFile! echo get !productFile! D:\map_drive\wprx\!productFile! echo bye echo ) > ftp_commands.txt echo ftp -n -s:ftp_commands.txt ) > ftp_commands.sql rem Execute the SQL file using osql osql -UTRANSF -PFSNART -S!transformedCode! -i ftp_commands.sql pause rem Optional to pause and let you see the output ) else ( echo No relevant result found for store code !storeCode!. Continuing to download. pause ) rem Step 8: Copy files to the pth12345 folder ( echo exec xp_cmdshell 'net use x: \\172.16.141.210\map_drive "user&pass002" /user:ho\2083761' echo exec xp_cmdshell 'copy x:\wprx\* d:\FTPRoot\TransfIN\ /y' echo exec xp_cmdshell 'net use x: /d /y' ) > transfer2store.sql osql -UTRANSF -PFSNART -S!transformedCode! -i transfer2store.sql echo Copied files complete for store code !storeCode! pause :next ) rem Compare st.txt and st_result.txt fc /b st.txt st_result.txt > nul 2>&1 if errorlevel 1 ( echo st.txt and st_result.txt are different. Restarting loop. timeout /t 60 /nobreak > nul goto loop ) else ( echo st.txt and st_result.txt are the same. Stopping program. ) :end echo Script finished processing all store codes. pause endlocalSummary of Changes:
- Combined Steps: Steps 5, 6, and 7 are combined, where the script checks for the presence of the product file and only proceeds to create and execute the FTP command file if the product file exists.
- Use of Simplified Logic: Utilized the
if defined resultto determine if the product was found. - Removed Redundant Code: Only the necessary parts are executed based on the presence of the product file.
- Pause for Debugging: Optional pauses are retained for debugging purposes, allowing you to see each step's output.
This restructured script will help maintain a clear and efficient flow through the necessary steps, ensuring that the FTP command is only executed when the required conditions are met.
- User
@echo off setlocal EnableDelayedExpansion cls rem Create or clear st_result.txt at the beginning echo. > st_fail.txt
:loop cls rem Step 1: Loop through each store code in st.txt for /f "usebackq delims=" %%i in ("st.txt") do call :process "%%i" goto :compare
:process set "storeCode=%~1"
rem Step 2: Transform store code to PTH12345 set "transformedCode=PTH!storeCode!" echo. echo Processing code: !transformedCode! rem Step 3: Check if !storeCode! already exists in st_result.txt findstr /x /c:"!storeCode!" st_fail.txt > nul if errorlevel 1 ( echo Found !transformedCode! in st_fail.txt. ) rem Step 4: Ping PTH12345 ping -n 1 "!transformedCode!" > nul 2>&1 if errorlevel 1 ( echo !transformedCode! is offline. Stopping for this store code. echo !storeCode! >> st_fail.txt & echo Try later goto :next ) rem Step 5: Get Date osql -UTRANSF -PFSNART -Sbgcpitsd-mn01 -dmaster -Q "set nocount on; select convert(varchar(10), getdate()-1, 120)" -h-1 -w150 > date.dat if errorlevel 1 ( echo Failed to retrieve date. Check OSQL command. goto :next ) set /p day=<date.dat set mm=!day:~6,2! set dd=!day:~9,2! del date.dat set "brandFile=BRAND_!mm!!dd!.!storeCode!" set "departmentFile=DEPARTMENT_!mm!!dd!.!storeCode!" set "productFile=PRODUCT_!mm!!dd!.!storeCode!" rem Step 6: Run the osql command and check result for the specific product pattern set "result=" ( echo exec xp_cmdshell 'dir d:\FTPRoot\transfIN\!productFile!' ) > check_product.sql osql -UTRANSF -PFSNART -S!transformedCode! -i check_product.sql > result.dat del /q check_product.sql > nul rem Check if result.dat contains the product filename findstr /c:"PRODUCT_!mm!!dd!.!storeCode!" result.dat > nul if not errorlevel 1 ( set "result=PRODUCT_!mm!!dd!.!storeCode!" ) rem Clean up the output file del result.dat rem Step 7: Check result if defined result ( echo Found !result! echo Next Store rem echo !storeCode! >> st_fail.txt & type st_fail.txt & pause goto :next ) rem If not found, proceed to download echo Not Found !result! rem ( rem echo user hotrn hotrn rem echo cd .. rem echo cd .. rem echo cd Aldata/LIFE/HEAD/SAV/DOWN/POS rem echo get !productFile! D:\map_drive\wprx\!productFile! rem echo get !departmentFile! D:\map_drive\wprx\!departmentFile! rem echo get !brandFile! D:\map_drive\wprx\!brandFile! rem echo bye rem ) > ftpcmd.dat rem echo ftp -n -s:ftpcmd.dat 10.4.9.128 rem START /B cmd /c "ftp -n -s:ftpcmd.dat 10.4.9.128 > NUL 2>&1" rem del ftpcmd.dat rem echo Downloaded files to D:\map_drive\wprx\ rem Step 7: Create the SQL file for FTP commands ( echo declare @ftpcommand nvarchar(max) = '' echo ( echo user hotrn hotrn echo cd Aldata/LIFE/HEAD/SAV/DOWN/POS echo get !brandFile! d:\FTPRoot\TransfIN\!brandFile! echo get !departmentFile! d:\FTPRoot\TransfIN\!departmentFile! echo get !productFile! d:\FTPRoot\TransfIN\!productFile! echo bye echo ) echo > ftpwprx.dat echo ftp -n -s:ftpwprx.dat ) > ftp_wprx.sql type ftp_wprx.sql & pause rem osql -UTRANSF -PFSNART -S!transformedCode! -i ftp_wprx.sql del /q ftp-wprx.sql rem Step 8: Copy files to the PTH12345 folder ( echo exec xp_cmdshell 'net use x: \\172.16.141.210\map_drive "user&pass002" /user:ho\2083761' echo exec xp_cmdshell 'copy x:\wprx\* d:\FTPRoot\TransfIN\ /y' echo exec xp_cmdshell 'net use x: /d /y' ) > transfer2store.sql echo osql -UTRANSF -PFSNART -S!transformedCode! -i transfer2store.sql echo Copied files complete for store code !storeCode! del /q transfer2store.sql > nul rem Caution: Ensure you want to delete all files in the D:\map_drive\wprx directory. del /q D:\map_drive\wprx\* > nul :next exit /b 0:compare rem Step 9: Compare st.txt and st_result.txt fc /b st.txt st_result.txt > nul 2>&1 if errorlevel 1 ( rem echo st.txt and st_result.txt are different. Restarting loop. timeout /t 10 /nobreak goto loop ) else ( echo st.txt and st_result.txt are the same. Stopping program. )
:end echo Script finished processing all store codes. pause endlocal
change process check fail to file and try again
- Assistant
To modify your batch script so that it handles failed attempts to process store codes correctly—by marking them in
st_fail.txtand allowing for retrying later—you can implement a loop to check for failed store codes after processing them. If a store code is found to have failed, it gets recorded inst_fail.txt, and you'll need to check that file before processing the next loop of store codes.Updated Script
Here's a revised version of your script. This version captures failed codes, retries processing them, and ensures that those entries are handled in
st_fail.txt.@echo off setlocal EnableDelayedExpansion cls rem Create or clear st_fail.txt at the beginning echo. > st_fail.txt echo. > st_result.txt rem Create or clear result file to track processed stores :loop cls rem Step 1: Loop through each store code in st.txt for /f "usebackq delims=" %%i in ("st.txt") do call :process "%%i" goto :compare :process set "storeCode=%~1" rem Step 2: Transform store code to PTH12345 set "transformedCode=PTH!storeCode!" echo. echo Processing code: !transformedCode! rem Step 3: Check if !storeCode! already exists in st_fail.txt findstr /x /c:"!storeCode!" st_fail.txt > nul if errorlevel 1 ( echo Found !transformedCode! in st_fail.txt. ) rem Step 4: Ping PTH12345 ping -n 1 "!transformedCode!" > nul 2>&1 if errorlevel 1 ( echo !transformedCode! is offline. Stopping for this store code. echo !storeCode! >> st_fail.txt & echo Try later goto :next ) rem Step 5: Get Date osql -UTRANSF -PFSNART -Sbgcpitsd-mn01 -dmaster -Q "set nocount on; select convert(varchar(10), getdate()-1, 120)" -h-1 -w150 > date.dat if errorlevel 1 ( echo Failed to retrieve date. Check OSQL command. echo !storeCode! >> st_fail.txt & echo Try later goto :next ) set /p day=<date.dat set mm=!day:~6,2! set dd=!day:~9,2! del date.dat set "brandFile=BRAND_!mm!!dd!.!storeCode!" set "departmentFile=DEPARTMENT_!mm!!dd!.!storeCode!" set "productFile=PRODUCT_!mm!!dd!.!storeCode!" rem Step 6: Run the osql command and check result for the specific product pattern set "result=" ( echo exec xp_cmdshell 'dir d:\FTPRoot\transfIN\!productFile!' ) > check_product.sql osql -UTRANSF -PFSNART -S!transformedCode! -i check_product.sql > result.dat del /q check_product.sql > nul rem Check if result.dat contains the product filename findstr /c:"PRODUCT_!mm!!dd!.!storeCode!" result.dat > nul if not errorlevel 1 ( set "result=PRODUCT_!mm!!dd!.!storeCode!" ) rem Clean up the output file del result.dat rem Step 7: Check result if defined result ( echo Found !result! echo Next Store echo !storeCode! >> st_result.txt rem Record the successful store code goto :next ) rem If not found, proceed to download echo Not Found !result! rem Step 7: Create the SQL file for FTP commands ( echo declare @ftpcommand nvarchar(max) = '' echo ( echo user hotrn hotrn echo cd Aldata/LIFE/HEAD/SAV/DOWN/POS echo get !brandFile! d:\FTPRoot\TransfIN\!brandFile! echo get !departmentFile! d:\FTPRoot\TransfIN\!departmentFile! echo get !productFile! d:\FTPRoot\TransfIN\!productFile! echo bye echo ) echo > ftp_wprx.dat echo ftp -n -s:ftp_wprx.dat ) > ftp_wprx.sql type ftp_wprx.sql & pause del /q ftp_wprx.sql rem Step 8: Copy files to the PTH12345 folder ( echo exec xp_cmdshell 'net use x: \\172.16.141.210\map_drive "user&pass002" /user:ho\2083761' echo exec xp_cmdshell 'copy x:\wprx\* d:\FTPRoot\TransfIN\ /y' echo exec xp_cmdshell 'net use x: /d /y' ) > transfer2store.sql echo osql -UTRANSF -PFSNART -S!transformedCode! -i transfer2store.sql echo Copied files complete for store code !storeCode! del /q transfer2store.sql > nul rem Caution: Ensure you want to delete all files in the D:\map_drive\wprx directory. del /q D:\map_drive\wprx\* > nul :next exit /b 0 :compare rem Step 9: Compare st.txt and st_fail.txt fc /b st.txt st_fail.txt > nul 2>&1 if errorlevel 1 ( echo st.txt and st_fail.txt are different. Restarting loop. timeout /t 10 /nobreak goto loop ) else ( echo st.txt and st_fail.txt are the same. Stopping program. ) :end echo Script finished processing all store codes. pause endlocalKey Changes Implemented:
-
Error Handling:
- If the OSQL command fails to retrieve the date, the store code is recorded in
st_fail.txt.
- If the OSQL command fails to retrieve the date, the store code is recorded in
-
Record Successful Codes:
- A successful store code is added to
st_result.txtfor tracking purposes.
- A successful store code is added to
-
Retry Logic and Failures:
- When the original store code fails due to issues like offline status or date retrieval failure, it retries the failed store codes by checking
st_fail.txt.
- When the original store code fails due to issues like offline status or date retrieval failure, it retries the failed store codes by checking
-
Cleanup:
- The script ensures temporary files such as
result.datand others are deleted once they are no longer needed.
- The script ensures temporary files such as
-
Output Visibility:
- The relevant output is printed to the console to observe the flow of processing.
This updated script is better structured for retrying failed store codes, providing a mechanism to ensure that anything that fails will be handled appropriately and can be addressed in a future run.
-