SQL Server传递表Netezza [英] SQL Server passing tables Netezza

问题描述

只是想知道什么是最佳实践:

Just wondering what is best practice to achieve:

我们已经在SQL Server中运行了存储过程，并且需要对要存储在Netezza上的庞大事实表进行一些计算.

We have stored procedure that runs in SQL Server and needs to do some calculations with a huge fact table to be stored on Netezza.

流程:

1. 存储过程将在SQL Server上创建临时表
2. 这些将被发送到Netezza与事实表一起加入
3. 将在Netezza中进行计算
4. 结果将传递回SQL Server

将临时表从SQL Server传递到Netezza的方法是什么?

谢谢

推荐答案

我知道唯一可以通过Netezza进出数据的方法是通过平面文件，或者使用ODBC驱动程序直接与Netezza一起使用并保持记录中的记录集，并通过odbc连接写入Netezza.

The Only way that I know of to get data in and out of Netezza is through flat files, or using the ODBC drivers to work with Netezza directly and hold a recordset in memerory and write to Netezza through an odbc connection..

无论您是在Windows还是Unix上，Netezza都带有可用于连接到Netezza的客户端工具. Fyi，不要打扰在互联网上寻找它们.您将必须直接从Netezza或管理该关系的人那里获得它们.

Regardless of if you are on Windows or Unix, Netezza comes with client tools that you can use to connect to Netezza. Fyi, don't bother looking for them on the internet. You will have to get them from Netezza directly, or from the person that manages that relationship.

我建议研究如何在SSIS中使用ODBC驱动器为您完成工作.我不是SSIS的专业人士，所以我不能说我会怎么做，但我会先研究一下.

I would suggest looking into how I might be able to use the ODBC drives in SSIS to do the work for you. I'm not a pro at SSIS so I can't say I would know how to do that, but I would look into that first.

如果必须完成任务，我会用C#编写一些内容来执行以下任务.

If I had to accomplish the task I would write something in C# to perform the following tasks.

• 从sql server创建平面文件
• 连接到Netezza创建链接到平面文件的外部表.
• 调用Netezza中的过程进行工作并生成要导出到临时表中的数据.
• 将新数据导出到平面文件，然后将其重新导入sql server.

现在我考虑一下，您也可以尝试以下操作，但是未经测试.我想知道是否可以在sql server中创建链接表，并在Netezza中创建使用同一平面文件的外部表.如果没有文件锁定，则可以创建一个从sql server到netezza的准链接.

Now that I think about it you might also try the following, it is untested however. I wonder if you can create a linked table in sql server and an external table in Netezza that uses the same flat file. Baring a file lock, if they can, you can create a quasi-link to netezza from sql server.

在netezza中查找有关外部表的更多信息.请参阅第5章的doc Netezza用户指南.

To find out more about external tables in netezza. Look in the doc Netezza User Guide in chapter 5.

《 Netezza用户指南》

此外，如果您对编码方面感兴趣，那么下面有一个很好的链接，介绍如何通过c#连接到netezza.

Also, if you are interested in the coding side there is a very good link below to how to connect to netezza via c#.

堆栈发布

我最终使用其中的一些内容构建了以下方法来针对Netezza执行命令.

I ended up using some of that post to build the method below to execute commands against Netezza.

OdbcDataReader GetReaderForCommand(string strCmd, string dbname)
{

    var conn = new OdbcConnection();
    conn.ConnectionString = "Driver={NetezzaSQL};servername=<servername>;port=5480;database="+dbname+"; username=<username>;password=<pwd>;";

    OdbcDataReader rdr = null;


        conn.Open();
        System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand(strCmd, conn);
        rdr = cmd.ExecuteReader();
        return rdr;


}

最后，我将跟进一些链接.

Lastly, here are a couple of links that I would follow up with.

enzeecommunity.com-搜索和询问问题的用户基础.

enzeecommunity.com - User base to search and ask questions of.

与Netezza一起使用的免费管理工作室.

A free management studio to use with Netezza.

http://www.aginity.com/ProductivityTools/WorkbenchOverview.aspx -

这篇关于SQL Server传递表Netezza的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！