کار با SQL Server با دستورات CMD و.BAT یا (SQLCMD)

در مورد T-SQL در اینجا با هم صحبت کردیم

در این مطلب می‌خواهیم بدون استفاده از SSMS و با استفاده از خط فرمان ویندوز CMD به مدیریت SQL و اجرای کوئری های آن بپردازیم

دستوری که اینجا بررسی میکنید SQLCMD می‌باشد

SQLCMD در اصل یک ابزار می‌باشد که به شما اجازه می‌دهد دستورات T-SQL و اسکریپت‌های SQL را از طریق خط فرمان اجرا کنید

SQLCMD از ODBC برای اجرای فرامین استفاده می‌کند.

نحوه دستور SQLCMD

sqlcmd options

Options:

   -a packet_size  
   -A  (dedicated administrator connection)  
   -b  (terminate batch job if there is an error)  
   -c batch_terminator
   -C  (trust the server certificate)  
   -d db_name
   -e  (echo input)  
   -E  (use trusted connection)  
   -fcodepage | i:codepage[,o:codepage] | o:codepage[,i:codepage]  
   -g (enable column encryption)
   -G (use Azure Active Directory for authentication)
   -h rows_per_header
   -H workstation_name
   -i input_file
   -I  (enable quoted identifiers)
   -j (Print raw error messages)
   -k[1 | 2] (remove or replace control characters)  
   -K application_intent
   -l login_timeout
   -L[c]  (list servers, optional clean output)  
   -m error_level
   -M multisubnet_failover
   -N  (encrypt connection)  
   -o output_file
   -p[1] (print statistics, optional colon format)  
   -P password
   -q "cmdline query"
   -Q "cmdline query" (and exit)  
   -r[0 | 1] (msgs to stderr)  
   -R (use client regional settings)  
   -s col_separator
   -S [protocol:]server[\instance_name][,port]  
   -t query_timeout
   -u  (unicode output file)  
   -U login_id
   -v var = "value"
   -V error_severity_level
   -w column_width
   -W  (remove trailing spaces)  
   -x  (disable variable substitution)  
   -X[1] (disable commands, startup script, environment variables and optional exit)  
   -y variable_length_type_display_width
   -Y fixed_length_type_display_width
   -z new_password
   -Z new_password (and exit)
   -?  (usage)

برای استفاده می‌توانیدخط فرمان CMD را اجرا کنید

سپس دستور زیر را برای ارتباط به دیتابیس وارد کنید

( توجه کنید بعد از U نام کاربری  و بعد از P رمز عبور را بنویسید)

sqlcmd -U sa -P 123456

علامت <1 نشان دهنده ارتباط موفق با سرور است

در صورت درست نبودن نام کاربری یا رمز عبور پیغامی مانند تصویر زیر را نمایش میدهد

می تواندی دستور خود را وارد کنید

برای مثال در انجا من دستور ساخت یک دیتابیس به نام amirweb را وارد می‌کنم

CREATE DATABASE amirweb

سپس فرمان go برای اجرا را در خط بعدی می‌نویسم و enter میکنم

go

خب دستور دیگری را امتحان کنیم

می خواهیم یه SELECT از یک دیتابیس بگیر با تعیین شرط

USE IODB
SELECT * FROM [Gvkhrooj] WHERE HOST=1 and STID=1
go

حالا می‌خواهیم یک کوئری را از فایل اجرا کینم (با پارامتر -i ) من کوئری خودم را در مسیر D:\SQLQuery.sql  قرار داد

انبار انتخاب دیتا بیس را هم در خط اول انجام میدهیم ( با پارامتر -d)

و خروجی را در فایل متنی بگیرم ( با پارامتر -o ) من مسیر D:\log.txt را برای خروجی در نظر می‌گیرم

sqlcmd -U sa -P 123456 -d IODB -i D:\SQLQuery.sql -o D:\log.txt

برای ایجاد فایل کوئری شما می توانید در SSMS طبق آموزش بخش اول یک کوئری بنویسید و از همانجا با نام دلخواه در محل مورد نظر ذخیره کنید

یا به سادگی در داخل فایل متنی NotePad ویندوز دستورات را بنویسید و با پسوند .sql ذخیره کنید

خب بهی است شما می تواندی با آموزش‌های T-SQL و اسکریپت‌نویسی .bat را ترکیب و روند کار را خودکار کنید

مثال اول :

یک اسکریپت به شکل زیر ایجاد میکنیم:

@echo off
title Run Query SQL Server V1
cls
set /p SName=Server Name :
set /p UName=User Name :
set /p Pwd=Password :
set /p choice=ARE YOU SURE TO EXECUTE SCRIPTS in %DbName% (y/n) ?
if '%choice%'=='y' goto begin
goto end
:begin
sqlcmd -S %SName% -U %UName% -P %Pwd% -i SQLQuery.sql > Report.txt
:end

توضیحات:

با title یک عنوان برای سربرگ انتخاب می‌کنیم

با cls صفحه را خالی می‌کنم

توسط set /p  نام سرور، نام کاربری و کلمه عبور را از کاربر دریافت می‌کنیم و پیغامی برای اطمینان کاربر از اجرای اسکریپت با y یا n دریافت می‌کنیم

یک شرط با if می‌گذاریم که اگر کاربر وروردی  %choice% را  y بزند به بخش begin برو و در غیر این صورت :end را اجرا کن

در بخش :begin هم دستور SQLCMD را با ساتفاده از دریافتی‌های کاربر پر می‌کینم و سپس با پارامتر -i فایل ورودی که در انجا کنار فایل اصلی می‌باشد و همچنین خروجی به نام Report.txt در کنار فایل اجرای را اضافه می‌کنیم

مثال دوم:

یک اسکریپت به شکل زیر ایجاد میکنیم:

@echo off
title Run Query SQL Server V2
cls
set /p SName=Server Name :
set /p UName=User Name :
set /p Pwd=Password :
set /p choice=ARE YOU SURE TO EXECUTE SCRIPTS in %DbName% (y/n) ?
if '%choice%'=='y' goto begin
goto end
:begin
sqlcmd -S %SName% -U %UName% -P %Pwd% -b -i SQLQuery.sql
if %errorlevel% neq 0 goto error /b %errorlevel%
echo.
echo .......................
echo . Query Successful .
echo .......................
pause
:error
echo.
echo.
echo ........................
echo . Query NOT Successful .
echo ........................
echo.
echo.
pause

توضیحات:

در این نسخه از اسکریپت قبلی خروجی گزارش را حذف کردیم و بجای آن سویچ -b را برای فعال کردن error_level اضافه کردیم

پس ا if یک شرط میگذاریم که اگر  %errorlevel% نابرابر با 0 باشد به بخش نمایش Query NOT Successful برو در غیر این صورت Query Successful را نمایش بده

مثال سوم:دریافت اطلاعات و تزرق به جداول

یکی از مورادی که برای من بسیار جالب و در اینحال سخت بود ساخت اسکریپت .bat ی بود که از کاربر اطلاعات رو بگیره و با دستور sqlcmd به پایگاه داده و جدول مورد نظر اضافه کنه

بسیار جستجو کردم تا به کد و نحوه نوشتن صحیح این کد برسم

برای این کار یک اسکریپت به شکل زیر ایجاد میکنیم:

@Echo OFF
title Run Query SQL Server V3
Echo.
Echo Please Enter SQl Server Connection settings:
Echo choose Your Connection:
Echo (w)Windows Authentication
Echo (p)User AND Password Authentication
set /p SQLT=(p/w)?
if '%SQLT%'=='w' goto WindowsAuthe
if '%SQLT%'=='p' goto passsql
:WindowsAuthe
set /p ID=ID:
set /p date=Date(Format:980301) :
set /p time=Time(Format:1415):
set QuerY=-v ID = "%ID%" -v date = "%date%" time = "%time%" -Q "insert into Table_1 (ID, date, time) select '$(ID)', '$(date)', '$(time)'"
sqlcmd -d amirweb -b %QuerY%
if %errorlevel% neq 0 goto error /b %errorlevel%
goto Handel
echo.
:passsql
set /p SName=Server Name :
set /p UName=User Name :
set /p Pwd=Password :
set /p ID=ID:
set /p date=Date(Format:980301) :
set /p time=Time(Format:1415):
set QuerY=-v ID = "%ID%" -v date = "%date%" time = "%time%" -Q "insert into Table_1 (ID, date, time) select '$(ID)', '$(date)', '$(time)'"
sqlcmd -S %SName% -U %UName% -P %Pwd% -d amirweb -b %QuerY%
if %errorlevel% neq 0 goto error /b %errorlevel%
echo.
:Handel
echo.
echo .......................
echo . Query Successful .
echo .......................
pause
:error
echo.
echo.
echo ........................
echo . Query NOT Successful .
echo ........................
echo.
echo.
pause
:end

توضیحات:

ابتدا از کاربر نوع اتصال را میگیرم (Windows Authentication يا SQL Authentication) و با goto به بخش مربوطه می‌پریم ( تفاوت کد در این دو این است که در Windows Authentication از کاربر نام سور نام کاربر و رمز عبور را نمی‌پرسد)

با set /p اطلاع مورد نظر را از کاربر میگیریم برای مثال من سه دیتای ID – Date -Time را دریافت کردم

دوباره با دستور set مقدار QuerY را بر اساس دیتای ورودی کاربر تنظیم می‌کنیم

نکته مهم نحوه دستور insert into در sqlcmd می‌باشد که باید بصورت زیر وارد شود ( به فاصله‌ها و علایم ” $ , دقیت کنید)

sqlcmd -S ServerName -U UserName -P Password -d DBNeme -v MyVar1 = "value1" -v MyVar2 = "value2" MyVar3 = "value3" -Q "insert into MyTable (value1, value2, value3) select '$(value1)', '$(value2)', '$(value3)'"

• value مقدار
• MyVar3 نام ستون
• DBNeme نام دیتابیس
• سویچ Q- حالت cmdline query را فعال و بعد اجرا خروج می‌کند

ادامه کد مانند مثال قبل است

منابع: + + +