I am trying to insert data into a Azure SQLServer Table
using a PowerShell4
script. The script is:
$cn = new-object System.Data.SqlClient.SqlConnection("Data Source=oayfco5zuq.database.windows.net;Initial Catalog=myDB;Integrated Security=False;User ID=myuser;Password=myUser;Encrypt=False;TrustServerCertificate=False");
$cn.Open()
$ds = new-object "System.Data.DataSet" "dsServers"
$ds.ReadXmlSchema('C:\SOAPR\LatestAdvice.xsd')
$ds.ReadXml("C:\SOAPR\LatestAdviceRes.txt")
$dtProd = $ds.Tables[0]
$bc = new-object ("System.Data.SqlClient.SqlBulkCopy") $cn
$bc.DestinationTableName = "dbo.LatestAdvice"
$bc.WriteToServer($dtProd)
$cn.Close()
XSD
<?xml version="1.0" standalone="yes"?>
<xs:schema id="LatestAdvice" xmlns="" xmlns:xs="http://ift.tt/tphNwY" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="LatestAdvice" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="row">
<xs:complexType>
<xs:sequence>
<xs:element name="id" type="xs:string" minOccurs="0" />
<xs:element name="title" type="xs:string" minOccurs="0" />
<xs:element name="category" type="xs:string" minOccurs="0" />
<xs:element name="picture" type="xs:string" minOccurs="0" />
<xs:element name="short_description" type="xs:string" minOccurs="0" />
<xs:element name="long_description" type="xs:string" minOccurs="0" />
<xs:element name="pubDate" type="xs:string" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
Data
<LatestAdvice>
<row>
<id>392062</id>
<title>TITL</title>
<category>Advice</category>
<image>my-image-url</image>
<short_description>Between the Honda CB Trigger and the Honda Unicorn, which one to buy?</short_description>
<long_description>Lorem Ipsum
</long_description>
<pubDate>Mon, 08 Dec 2014 17:47:00</pubDate>
</row>
</LatestAdvice>
Table Script
CREATE TABLE [dbo].[LatestAdvice]
(
id VARCHAR(10) NOT NULL PRIMARY KEY,
title VARCHAR(100),
category VARCHAR(7),
picture VARCHAR(500),
short_description VARCHAR(60),
long_description VARCHAR(5000),
[pubDate] varchar(50)
)
Host running the script is allowed in firewall scripts but I am getting
Exception calling "WriteToServer" with "1" argument(s): "A transport-level
error has occurred when receiving results from the server. (provider: TCP
Provider, error: 0 - An existing connection was forcibly closed by the remote
host.)"
At C:\SOAPR\LoadLatestAdviceData.ps1:10 char:1
+ $bc.WriteToServer($dtProd)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : SqlException
Any one any idea?
No comments:
Post a Comment