[R Beginners]: Beginner guide for connecting and using SQL Server in R. Code for Windows included.
ฝัง
- เผยแพร่เมื่อ 6 ม.ค. 2025
- Easy and step by step guide explains how to connect to SQL Server, how to read, write, delete and append data in SQL Server in Windows environment.
Code rpubs.com/tech...
How to run stored procedures from R and how to provide parameters to the stored procedures.
Easy and step by step guide for Windows.
![[R beginners]: Use RSQLite, a fast and easy database in R](http://i.ytimg.com/vi/GYfmN2IeY1w/mqdefault.jpg)
![[R beginners]: Use RSQLite, a fast and easy database in R](/img/tr.png)
Simple and concise. Could not make it more perfect. Connected to a legit instance too, not a localhost practice db. easy peasy.
You rock. The best tutorial vid on youtube, simple and quite efficient.
Glad you liked it!
simple, clear and very efficient , thx for this video ;-)
Glad you liked it!
This saved me thank you!
Glad it helped!
best tutorial on u tube
Thanks.
Good day
Thank you very much for your videos, they really help a lot and they taught me valuable things about Access, thank you very much, but I have another question now with SQL Server. When listing the corresponding drivers, how do I know which one to choose? Or which one would be correct? For example, in the case of SQL Server I see that 3 appear, so which one should I choose?
Greetings from Mexico and seriously thank you for your videos :)
Hi
As a rule of thumb just pick the "SQL Server" that will work.
Greetings from Mexico
One question, several tutorials that I have seen instead of putting the code directly as you do, you should modify the files directly in Windows from the odbc data sources, I am of the idea of doing everything directly from R but in your case, what? What procedure or way could be the most correct?
First of all, what a good explanation you just gave.
Greetings
Hi
I think you are referring to the fact that why am I not using the ODBC in the connection string .
On a Microsoft Windows machine and using Microsoft SQL Server it is always good to use the direct connection string. But there is no harm in going through the ODBC also.
When you are using non Microsoft databases then the direct connection strings will not work and you would have to create an ODBC connection first and then use the ODBC name in your connection string in R.
Hope this is what you wanted to clarify.
@@DataAnalytic thanks comrade, Greetings from Mexico!!!!!
Hi, thanks for the very informative video! I have a question - when you are using a remote server (for example, your company's server) where do you set the actual connection to the server? Like, where do you put the IP address for the server? My guess is you have to do it on your computer, in the ODBC Data Sources, is that correct? Thank you so much! sorry for the confusion
Hi Javid
You would not need to define it in your odbc as we are going to use the SQL Server driver which are already installed in your windows environment.
see the syntax below.
Just put your server IP address of your company server eg. "xyz.yourcompany.com"
# When you have got a login and password and the server name
con
@@DataAnalytic So here I would replace 'yourservername' with the IP address? Thanks :)
Yes. That is correct.
@@DataAnalytic Thank you so much. You are truly a huge help and will be back on your channel
Glad to know that it helped you. You are alway welcome.
Easy tutorial, thank you. I am trying to access an external SQL Database and get following message:
Error: nanodbc/nanodbc.cpp:3166: 07009: [Microsoft][ODBC SQL Server Driver]Ungültiger Deskriptorinde
Warning message:
In dbClearResult(rs) : Result already cleared
Any idea, why?
Hi
Try adding a line in your script (before you open the connection) and see if it makes a difference.
option(odbc.batch_rows= 1024)
The other issue is the length of the fields,
column.types %
arrange(cml) %>%
pull(COLUMN_NAME)
fields
@@DataAnalytic Thank you for the quick reply. Is the function called options oder option? With optionS it gives me the same error:
> library(odbc)
> library(DBI)
> library(ggplot2)
> options(odbc.batch_rows= 1024)
> con
> Activity
Hi
Also implement the following and see, this actually worked for me .
Do not use the dbReadTable and use dbGetQuery by providing an SQL, if you try different field in your sql you would realise that certain fields in your database will cause the issue.
The following code fixes it for me.
column.types %
arrange(cml) %>%
pull(COLUMN_NAME)
fields
@@DataAnalytic Hey it worked now out! I am super happy. Here ist the code:
# load the RODBC package, vorher einmalige Installation erforderlich
library(RODBC)
# Verbindung zum Server herstellen, Server IP-Adresse muss einmalig im Windows-Programm "ODBC-Datenquellen" definiert sein
# Nutzer und Passwort sind Vektoren, welche ich weiter oben definiert habe
conn
Great work. Wishing you all the best.
Hello, why would a stored procedure return a data frame with 0 rows and 0 columns? If I copy and paste the same exact string into a new query in MSSQL it works fine.
For the stored procedures, you can run it as below.
Inn this example I have used two parameters also
Otherwise simply say "EXEC Yourprocedurename"
dfe
@@DataAnalytic I did something very similar. I think really the only difference is I am using a pool (check out library pool) to make my ODBC connection.
Do you think that might be it?
Yes, you are right
Using the pool package the pool::dbExecute command returned 0.dbExexute - executes the stored procedure on the server but did not fetch the data back.
This works well in pool package
library(pool)
dd
Good day
In my case I try to connect in the same way as you present in the video, I have the same driver as you and it won't let me connect, in my case this is the code:
> with
In RStudio you will find the "correct" way to connect to SQL Server, it comes on the one hand the one they use in the video and another when you have another version of SQL Server installation, use the other way different from yours and still I couldn't, but there they marked the activation of a port, port 1433, I found on the internet how to activate it without the need to modify the odbc files since I created this form of connection that some users make via DNS, although it is valid, I don't think it is correct since system files are modified, but the solution was that, activate the port connections, a complementary video should be put together in that sense, it could give added value added to all the material they have, without further ado I send you a hug from the Mexico City
Great that you fixed the issue.
The databases like sql server, postgress, mongodb or any other client-server based databases need specific ports to be opened through which they listen to the incoming requests.
Some sql server instances use dynamic ports and some use 1433 to listen to the requests.
If for whatever reason the port is not open then it can be opened like you were able to do it.
It is easier to fix the issue on personal or home comupters but when you have a corporate server then the ports might be blocked due to the security policies.
@@DataAnalytic excelent, thanks comrades!!!!
thanks.
You're welcome