xsd error There is no Unicode byte order mark

This post shows one of the possibles solution to the error “There is no Unicode byte order mark”.

As can see below I issue an xsd command to convert xsd to POCO.

Then I hit the error “There is no Unicode byte order mark. Cannot switch to Unicode.”

D:\SSIS\chanmm\ptp\uCustoms\XML+Schema\Common>xsd Header.xsd XSDCommonAck-Res.xs

d /classes

Microsoft (R) Xml Schemas/DataTypes support utility

[Microsoft (R) .NET Framework, Version 4.6.1055.0]

Copyright (C) Microsoft Corporation. All rights reserved.

Error: There is no Unicode byte order mark. Cannot switch to Unicode.

If you would like more help, please type “xsd /?”.

Type xsd /? will be useless.

What you need to do is open the xsd in Notepad. Save the xsd again as Unicode file.

Advertisements
Posted in .Net, Cloud, Community, Computers and Internet | Tagged , | Leave a comment

SSIS using ADO connection manager for Script Component

This post is to show how to use ADO connection in SSIS Script Component. At the same time, this is what myself always forget.

Bear in mind, I never successfully using OLEDB connection for this. I only successfully using this with ADO.NET connection.

Before going into the Script Editor make sure you create a Connection Manager for your Script Component.

Open your Script Editor in Script Component (inside the Data Flow normally) by double clicking in. In the root area outside any method, I declared the following.

IDTSConnectionManager100 ConMgr;

object Targetconnection;

In between PreExecute and PostExecute methods, insert the follow AcquireConnections method. Sure there are other way to do this.

public override void AcquireConnections(object Transaction)

{

    ConMgr = this.Connections.ConnectionBlock;

    Targetconnection = this.Connections.ConnectionBlock.AcquireConnection(null);

    SqlConn = Targetconnection as SqlConnection;

}

You do not need to SqlConn.Open() to open the connection if you are utilizing the Connection Manager.

Posted in .Net, Cloud, Community, Computers and Internet, Data Platform | Tagged , , | Leave a comment

R accessing SQL Server Northwind table columns

This post shows you how to access to SQL Server database table using R.

In the previous Connect R to Microsoft SQL Server https://chanmingman.wordpress.com/2018/08/21/connect-r-to-microsoft-sql-server/ article. I showed you how to access to the database. In here we will access to Northwind database tables.

Let’s assign Order Details table to dataFetch.

> dataod <- sqlFetch(cn, 'Order Details', colnames=FALSE, rows_at_time=1000)

To list the table type the following.

> daraod

The list is very long so I cannot see the header. Hence, I create the same table with 10 rows of data.

> dataod <- sqlFetch(cn, ‘OrderDetail10’, colnames=FALSE, rows_at_time=1000)

Display the ProductId column.

> dataod[2]

You can assign the column to variable.

newcol <- dataod[2]

To find out the unique product id in the list you can convert the column to Factor

> pid <- factor(newcol)

> pid

The result display as below.

ProductID

<NA>

Levels: c(11, 42, 72, 14, 51, 41, 65, 22, 57)

Posted in .Net, Cloud, Community, Computers and Internet, Data Platform | Tagged , | 1 Comment

Reconstruct database with tables using C#

This post shows you how to reconstruct the database using the scripts you have generated, https://chanmingman.wordpress.com/2018/08/11/sql-server-generate-database-script/.

This code split into 2 major functionalities.

1. Reconstruct the database.

2. Reconstruct the tables.

The below is to construct database only without the table.

public
static
void CreateeDB(string strConn, string strDBPath, string strDBName)

{

    String str;

    SqlConnection myConn = new SqlConnection(strConn);

    str = “CREATE DATABASE “ + strDBName + ” ON PRIMARY “ +

        “(NAME = “ + strDBName + “_Data, “ +

        “FILENAME = ‘”+ strDBPath + “\\” + strDBName + “.mdf’, “ +

        “SIZE = 10MB, MAXSIZE = 10MB, FILEGROWTH = 10%) “ +

        “LOG ON (NAME = “ + strDBName + “_Log, “ +

        “FILENAME = ‘” + strDBPath + “\\” + strDBName + “.ldf’, “ +

        “SIZE = 4MB, “ +

        “MAXSIZE = 5MB, “ +

        “FILEGROWTH = 10%)”;

    myConn.Open();

    SqlCommand myCommand = new SqlCommand(str, myConn);

    try

    {

        myCommand.ExecuteNonQuery();

    }

    catch (System.Exception ex)

    {

        Console.Write(ex.ToString());

    }

    finally

    {

        if (myConn.State == ConnectionState.Open)

        {

        myConn.Close();

    }

}

The part below will create all the tables.

public
static
void CreateTable(string strDBName, string strConn)

{

    string strSql = ReadSqlToString(strDBName);

    SqlConnection conn = new SqlConnection(strConn);

    conn.Open();

    SqlCommand cmd = new SqlCommand(strSql, conn);

    cmd.ExecuteNonQuery();

    conn.Close();

}

public
static
string ReadSqlToString(string strDBName)

{

    string temp = “”;

    string path = strDBName;

    temp = File.ReadAllText(path);

    return temp;

}

Of course, you can do this in SQL Server Management Studio. Somehow you might just want to run the exe to rebuild the database and tables without installing the SQL Server Management Studio.

You can download the full code here: https://github.com/chanmmn/SQLUtilities/tree/master/ConAppReconstructDBusingSql

Posted in .Net, Cloud, Community, Computers and Internet, Data Platform | Tagged | Leave a comment

Database ‘Database’ is already open and can only have one user at a time

This post gives one of the possible solution to the following error for SQL Server.

Most of the time we will be setting the database properties using SQL Server Management Studio, the GUI management interface. Somehow for some of the databases that have set to single user mode. When you right click those databases and choose properties, unfortunately, you have gotten the following error message.

Database ‘Database’ is already open and can only have one user at a time”

At this point of time, you have no way to set anything on that database because SQL Server Management Studio keep popping out the error and refuses to bring you to the Properties page.

This is the time you need to utilize your SQL Query. Use the follow statements to turn your database back to multi user mode then you should be fine.

Use WhateverDatabase

ALTER DATABASE WhateverDatabase

SET MULTI_USER

WITH ROLLBACK IMMEDIATE

Reference:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/77a83d0a-d49a-4b2d-9cd5-ee3c31836874/database-test-is-already-open-and-can-only-have-one-user-at-a-time?forum=sqlsecurity

Posted in .Net, Cloud, Community, Computers and Internet, Data Platform | Tagged | Leave a comment

Connect R to Microsoft SQL Server

This post is showing you how to connect R language to Microsoft SQL Server.

I could not use the odbc as stated here https://support.rstudio.com/hc/en-us/articles/214510788-Setting-up-R-to-connect-to-SQL-Server-.. I have tried it in R 3.4.3 as well as R 3.5.1.

I used RODBC, and it has been working for the 2 mentioned versions.

I used the command below to connect to northwind sample database.

> install.packages(“RODBC”)

> library(RODBC)

> cn <- odbcDriverConnect(connection=”Driver={SQL Server Native Client 11.0};server=localhost;database=NORTHWND;trusted_connection=yes;”)

> dataFetchEUR <- sqlFetch(cn, ‘Products’, colnames=FALSE, rows_at_time=1000)

> View(dataFetchEUR)


Reference:

https://www.red-gate.com/simple-talk/sql/reporting-services/making-data-analytics-simpler-sql-server-and-r/

Posted in .Net, Cloud, Community, Computers and Internet, Data Platform | Tagged | 1 Comment

SQL Server replication new table not replicated

This post shows you why you are waiting for the table to replicate and there never replicated.

There is something call Article Properties in the Publisher Properties.

Right click the Publisher and choose Properties.

Go to Articles then you will see a check box Show only checked articles in the list.

Uncheck that you will see the new table that you have created or the tables that have not been selected for replication.

You can now select those tables.

Posted in .Net, Cloud, Community, Computers and Internet, Data Platform | Tagged , | Leave a comment