کار با 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 را فعال و بعد اجرا خروج میکند
ادامه کد مانند مثال قبل است
دستور osql عملکردی مانند sqlcmd دارد اما مایکروسافت اعلام کرده که از نسخهها آینده آن را حذف میکند.
برای انتخاب نام سرور میتوانید از سویچ -S استفاده کنید
برای استفاده از اعتبار سنجی Windows Authentication نیازی به وارد کردن سویچ یا نام کاربر و رمز عبور نیست
در سویچها فاصله فرقی نمیکند یعنی Usa- با U /sa- تفاوتی ندارد