使用Google Apps Script通过JDBC连接到MSSQL Server时出错

问题描述

我尝试使用Google Apps脚本连接到Microsoft SQL Server。我正在使用SQL Server 2008 R2，我正在使用应该读取数据并将其放入电子表格的建议脚本之一：

错误消息是：

用户名和密码正常，用户是DBowner。该端口也是正确的，我试图通过Telnet连接到服务器使用：
o IP地址1433
，它的工作原理。

以下是代码：

  function foo（）{
 var conn = Jdbc.getConnection（jdbc：sqlserver：// IP-address：1433 / DBName，user，password）; 
 var stmt = conn.createStatement（）; 
 stmt.setMaxRows（100）; 
 var start = new Date（）; 
 var rs = stmt.executeQuery（select * from person）; 
 var doc = SpreadsheetApp.getActiveSpreadsheet（）; 
 var cell = doc.getRange（'a1'）; 
 var row = 0; $（变量col = 0; col< rs.getMetaData（）。getColumnCount（）; col ++）{
 cell.offset（row， col）.setValue（rs.getString（col + 1））; 
} 
 row ++; 
} 
 rs.close（）; 
 stmt.close（）; 
 conn.close（）; 
 var end = new Date（）; 
 Logger.log（time took：+（end.getTime（） -  start.getTime（）））; 
} 
  

您对什么可能是错误有任何想法吗？我必须在我的服务器上进行一些配置吗？或者在数据库中？上述说明指出，以确保Google的IP地址可以访问数据库。但不是列出我授予该端口所有人使用的所有Google IP地址。我还在Sql Server Configuration Manager中启用了TCP / IP协议。我在MSSMS中授予了远程连接服务器。任何其他的想法，请？

解决方案

好吧，我在这里找到答案：

显然，连接字符串必须如下所示：

  var conn = Jdbc.getConnection（jdbc：sqlserver：// IP地址： 1433;+databaseName = DBName; user = username; password = password;）; 
  

我不明白为什么连接字符串与Google文档中的连接字符串不同，但这一个适用于我...

I'am trying to connect to Microsoft SQL Server using Google Apps Script. I'm using SQL Server 2008 R2 and I am using one of the suggested scripts that's supposed to read data and put it into a Spreadsheet:https://developers.google.com/apps-script/jdbc#reading_from_a_database

The error message is:

Username and password is OK, the user is DBowner. The port is also correct, I tried to connect to the server via Telnet using: o IP-adress 1433and it works.

Here's the code:

function foo() {
    var conn = Jdbc.getConnection("jdbc:sqlserver://IP-adress:1433/DBName","user","password");
    var stmt = conn.createStatement();
    stmt.setMaxRows(100);
    var start = new Date();
    var rs = stmt.executeQuery("select * from person");
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    var cell = doc.getRange('a1');
    var row = 0;
    while (rs.next()) {
        for (var col = 0; col < rs.getMetaData().getColumnCount(); col++) {
            cell.offset(row, col).setValue(rs.getString(col + 1));
        }
        row++;
    }
    rs.close();
    stmt.close();
    conn.close();
    var end = new Date();
    Logger.log("time took: " + (end.getTime() - start.getTime()));
}

Do you have any idea of what can be wrong? Do I have to make some configuration on my Server? Or in the database? The instructions mentioned above says to ensure that Google's Ip-addresses can reach the database. But instead of listing all of Google's IP-addresses I granted access to all on that port. I also enabled TCP/IP Protocol in Sql Server Configuration Manager. And I granted "Remote connections" to the Server in MSSMS. Any other idea, please?

解决方案

Well, I found the answer here:Google Apps Scripts/JDBC/MySQL

Obviously, the connection string has to look like this:

var conn = Jdbc.getConnection("jdbc:sqlserver://IP-address:1433;" + "databaseName=DBName;user=username;password=password;");

I don't understand why connection string differs from the one in Google Documentation but this one works for me...

这篇关于使用Google Apps Script通过JDBC连接到MSSQL Server时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！