Archive

Archive for the ‘SQL Server’ Category

SQL Server 2016 Developer available

Categories: SQL Server

String or binary data would be truncated

Doing an INSERT INTO a SQL Server 2014 table from a complex join with UNION i got this error.
There were many fields, very difficult to understand at a first sight where was the problem.
Fortunately in SQL Management Studio right clicking the database with Tasks->Import data i inserted the SQL with UNION in the Data Source, and the destination table in the Destination.
The curious thing: in SQL Management Studio the INSERT INTO failed and no records imported, the same operation in the SQL Server Import and Export Wizard instead succeed but with warnings, which are useful to understand which fields causes truncation:
01-03-2016 14-49-13

Categories: SQL Server, SSIS

Cannot resolve the collation conflict

Making an UNION between two SQL Server 2014 views i got the error

Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS”

There are many fields, a problem.. fortunately there is an easy solution with

SELECT
col.name, col.collation_name
FROM
sys.columns col
WHERE
object_id = OBJECT_ID('<yourview>')

With this query we get something as (fields name hidden for privacy):
01-03-2016 16-10-38
I tried with the first view and then with the another view in UNION, here is immediately visible which are the problematic fields (the few ones with another collation, in this case “Latin1_General_CI_AS”) .
Then for these fields we can add the collation, as in this case where there is also an CASE WHEN:

CASE WHEN TABDDETB = '' OR TABDDETB IS NULL THEN
   'Not specified' COLLATE SQL_Latin1_General_CP1_CI_AS
   ELSE TABDDETB COLLATE SQL_Latin1_General_CP1_CI_AS
END AS category

and the error is solved.

Categories: SQL Server

Firefox needs UTF8 for GeoXml3


I’m developing an WebAPI2 solution with Angular that receives, from Android devices, GPS points.
So Latitude , Longitude , Speed etc. are saved in a SQL Server 2014 table; from the web site the headquarter would follow the real time navigation.
The chosen approach was to write an KML file from the db data, and show this KML in a GoogleMaps window.
Having local files (not on another domain) the best solution is to use the GeoXml3 library, which is relatively easy to use (examples here and here).
For the javascript part, no problem: there is an simple setTimeout that calls via jQuery an WebAPI2 method that re-write the kml (the name is the record ID), and then adding an random number to the querystring the boss can follow the persons wandering somewhere, seeing the kml line that slowly progress on the map.
But there was an problem: On IE10 , 11, Chrome, Edge all ok; instead on Firefox (Windows or Ubuntu or Android tablet) no kml shown.
Sometimes the things are discovered in a very casual manner…i tried to save the KML with another name using Notepad++ , and change the code in order to not use the API call and instead try to show immediately this second file where i wanted to test some change, in order to understand what was wrong : bingo, KML immediately visible!
This was a real head scratching…the files (the generated from the system, and the one saved from the previous) in Notepad++ was absolutely the same, no visible difference.
So i tried to compare them with WinMerge and surprise, the generated file was full of strange characters before every normal character, immediately i realized “oops, the encoding?” and only after i noticed in Notepad++ that in the Encoding menu the file saved from Notepad++ itself was UTF8 encoded, and the one produced from the C# code was without encoding.
In the WebAPI2 method effectively i used Unicode: so now, all the browsers shows the KML writing the file as:

private async Task WriteTextAsync(string filePath, string text)
{
    //byte[] encodedText = Encoding.Unicode.GetBytes(text);
    byte[] encodedText = Encoding.UTF8.GetBytes(text);
    //
    using (FileStream sourceStream = new FileStream(filePath, FileMode.Append, FileAccess.Write, FileShare.None, bufferSize: 1048576, useAsync: true))
    {
        await sourceStream.WriteAsync(encodedText, 0, encodedText.Length);
    };
}

xp_cmdshell strange problem

I was working on this request: from a ship are unloaded many containers, it is needed to track the ones not still invoiced; in this case must be sent an email with an Excel file as attachment, containing the list of these containers.
The containers are register in a table of an SQL Server 2008 SP3 10.0.5520.0 (X64).
I tried OPENQUERY but without success (it seems that read and write Excel with OPENQUERY is a sort of black magic) so , giving also the fact that in every case there were many steps too complex to achieve with TSQL (CreateObject and CLR stored procedures were not acceptable options) i used an SSIS package , without using a SSIS catalog: it must be used dtexec.
I shoud try with SQL Server 2014, but with SQL 2008 there is this problem and double quoted strings are tipically requested in a dtexec command line: so i use the trick of the old MSDOS command ECHO to create a batch file and then execute this .bat from xp_cmdshell.
My TSQL is :

DECLARE @varSql nvarchar(500)
DECLARE @varFileName nvarchar(20)
DECLARE @varEcho nvarchar(1000)
DECLARE @XlsFileName nvarchar(500) 
DECLARE @intRes int
SET @varFileName = 'D:\SSIS\ContainersWithoutBilling\batches\' + CAST(@p_Voyage AS varchar) + '.bat'
SET @XlsFileName = 'D:\SSIS\ContainersWithoutBilling\Voyages\' + CAST(@p_Voyage AS varchar) + '_NotInvoiced.xlsx'
SET @varSql = '' + CHAR(34) + 'C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec' + CHAR(34) + ' /FILE ' + CHAR(34) + 
				'D:\SSIS\ContainersWithoutBilling\SSISPkg\bin\Package.dtsx' + CHAR(34) + ' /CHECKPOINTING OFF  /REPORTING EW  /SET \Package.Variables[IDVoyage].Value;' + 
				CAST(@p_Voyage AS varchar)
SELECT @varEcho = 'ECHO ' + @varSql  + ' &amp;amp;gt; ' + @varFileName 
EXEC @intRes =  master..xp_cmdshell @varEcho , no_output	

At this point we have an batch file, for example is generated something as this 116243.bat:

"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec" /FILE "D:\SSIS\ContainersWithoutBilling\SSISPkg\bin\Package.dtsx" /CHECKPOINTING OFF  /REPORTING EW  /SET \Package.Variables[IDVoyage].Value;116243

This example works without problems launched from the File Explorer, and you can think that with

EXEC @intRes = master..xp_cmdshell @varFileName, no_output

your problems are resolved, instead i was surprised that sometimes the .bat file, that was perfectly working launched by hand, under xp_cmdshell the process hangs: and i was constrained to kill both the cmd and the dtexec processes.
Tipically a xp_cmdshell is blocked if is requested an user input , but this was not the case.
After some head scratching, i tried with an temporary job:

DECLARE @jobID uniqueidentifier
DECLARE @cmd varchar(1000) 
SET @cmd = 'D:\SSIS\ContainersWithoutBilling\batches\' + CAST(@p_Voyage AS varchar) + '.bat'
EXEC msdb.dbo.sp_add_job @job_name = '_tmp_batch', @enabled  = 1, @start_step_id = 1, @owner_login_name='sa', @job_id = @jobID OUTPUT 
EXEC msdb.dbo.sp_add_jobstep @job_id = @jobID, @step_name = 'launch batch', @step_id = 1, @subsystem = 'CMDEXEC', @command = @cmd
EXEC msdb.dbo.sp_add_jobserver @job_id = @jobID
EXEC msdb.dbo.sp_start_job @job_id = @jobID, @output_flag = 0 
WAITFOR DELAY '00:00:30' -- wait an reasonable time of 30 seconds until the job should be securely completed
IF EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = '_tmp_batch')
BEGIN
	EXEC msdb.dbo.sp_delete_job @job_name = '_tmp_batch'
END
-- or you could delete if exists the temp job before the creation, and avoid the
-- WAITFOR if there are not other instructions that depends from the job work

and this is working also for the .bat files that hangs under xp_xmdshell.
I think that using an SSIS catalog there aren’t these problems, but in this case the dtexec was mandatory.
At the end it seems that xp_cmdshell has some bug, and an temporary job is always a better choice.

Categories: SQL Server, SSIS

Dynamic parameters in SQL IN

The SQL IN is one of the universally implemented keyword, for example

SELECT * FROM Customers WHERE STATE IN ('IL', 'AR')

This is the common syntax, but sometimes is requested to have the IN list dynamic.
In Microsoft Sql Server could be used an function, for example

CREATE FUNCTION [dbo].[GetStates](
)
    RETURNS @arrValues table(val char(2))
AS
BEGIN
   INSERT INTO @arrValues (val) VALUES ('IL')
   INSERT INTO @arrValues (val) VALUES ('AR')
   RETURN
END

Then can be used

SELECT * FROM Customers WHERE STATE IN (SELECT val FROM dbo.GetStates())

Unfortunately (still searching for an workaround…) this syntax cannot be used in grouping , for example

SUM(CASE 
   WHEN [groupcomponentcode] IN ( '1004', '1003', '1005', '1063' ) THEN Amount 
   ELSE 0 
END)  AS EngineGroup

is working , instead

SUM(CASE 
   WHEN [groupcomponentcode] IN (SELECT val FROM dbo.SomeFunc()) THEN Amount 
   ELSE 0 
END)  AS EngineGroup

gives the error
‘Cannot perform an aggregate function on an expression containing an aggregate or a subquery’

Categories: SQL Server

WebAPI2 errors management in Android

In my last project is used an SQL Server 2014 Phones table, with an Android app that register the device itself in this table via an WebAPI2 call.

The table is simple:


We have an primarykey named “PK_Phones” on PhoneId.

The PhoneID is the IMEI code, if the device has phone capabilities and is installed an working SIM, or the Android ID.

There are other common fields for multiuser management as the timestamp which is very useful with the Entity Framework 6 used in this solution.

The Web counterpart (created with Visual Studio 2013) is a common MVC5 project using WebAPI2 with Breeze, Angular , Bootstrap, Modernizr, Moment, Ninject, Toastr…an huge blob installed from NuGet using the HotTowel package (Ninject , Jasmine for testing and other package needs to be installed by hand).

The first move was to create an ADO.NET Entity Data Model (here i don’t speak in detail of the project organization) and using “Code first from Database” i created the class representing my table:

namespace net.studioalessi.walkad.dl
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;
    using System.Data.Entity.Spatial;

    public partial class Phones
    {
        [Key]
        [StringLength(20)]
        public string PhoneId { get; set; }

        [StringLength(30)]
        public string Manufacturer { get; set; }

        [StringLength(20)]
        public string Model { get; set; }

        [StringLength(150)]
        public string Notes { get; set; }

        [StringLength(20)]
        public string PhoneNumber { get; set; }

        public bool Banned { get; set; }

        [StringLength(50)]
        public string UtenteModifica { get; set; }

        public DateTime? OraModifica { get; set; }

        [StringLength(50)]
        public string UtenteInserimento { get; set; }

        public DateTime? OraInserimento { get; set; }

        [Column(TypeName = "timestamp")]
        [MaxLength(8)]
        [Timestamp]
        public byte[] SSMATimeStamp { get; set; }
    }
}

Then the necessary Controller (right click on Controllers folder->add new scaffolded item->


Web API 2 Controller with actions, using Entity Framework as in figure.

Using the previous Phones class we generate the Controller and the Context, in our case the context is the class PhonesContext:

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Web;

namespace net.studioalessi.walkad.dl
{
    public class PhonesContext : DbContext
    {
        public PhonesContext()
            : base("name=WalkadConn")
        {
        }

        // no virtual ? in the previous EF version was generated as virtual...
        public DbSet<Phones> Phones { get; set; }

        // Also the code below was generated automatically in the previous version.
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Phones>()
                .Property(e => e.UtenteModifica)
                .IsUnicode(false);

            modelBuilder.Entity<Phones>()
                .Property(e => e.UtenteInserimento)
                .IsUnicode(false);

            modelBuilder.Entity<Phones>()
                .Property(e => e.SSMATimeStamp)
                .IsFixedLength();
        }
    }
}

And the Controller is changed from the generated version to an BreezeController

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Threading.Tasks;
using System.Web.Http;
using System.Web.Http.Description;
using Walkad.Models;
using net.studioalessi.walkad.dl;
using Breeze.WebApi2;

namespace Walkad.Controllers
{
    [BreezeController]
    public class PhonesController : ApiController
    {
        private IPhoneRepository _repo;

        public PhonesController(IPhoneRepository repo)
        {
            _repo = repo;
        }

        [HttpGet]
        public string Metadata()
        {
            return _repo.MetaData;
        }

        [HttpPost]
        [AllowAnonymous]
        public async Task<IHttpActionResult> RegisterImei(PhoneModel model)
        {
            PhonesContext objT = new PhonesContext();
            try
            {
                Phones objTrace = new Phones
                {
                    UtenteInserimento = "RemoteUser",
                    OraInserimento = DateTime.Now,
                    Model = model.Model,
                    Manufacturer = model.Manufacturer,
                    PhoneId = model.Imei
                };
                //
                objT.Phones.Add(objTrace);
                await objT.SaveChangesAsync();
                return Ok();
            }
            catch (Exception ex)
            {
                return BadRequest(ex.ToString());
            }
            finally
            {
                //
            }
        }
    }
}

The PhoneModel was created by hand and is the model representing the JSON string sent from the client (Web site or Android app, as in this case)

public class PhoneModel
{
    [Required]
    [StringLength(15)]
    public string Imei { get; set; }

    [StringLength(30)]
    public string Manufacturer { get; set; }

    [StringLength(20)]
    public string Model { get; set; }
}

At this point we can try the service from Fiddler


By clicking “Execute” we can see that our call was successful


And in SQL Management Studio we can see the new record:


Ok , we can create the Android app with Eclipse Luna (with the Android Developer Tools).

I don’t show the details, i created an simple Android app with an menu item that calls the routine named registerThisDevice:

private void registerThisDevice() {
    SharedPreferences sharedPrefs = PreferenceManager.getDefaultSharedPreferences(this);
    String strBasePath = sharedPrefs.getString((String)getText(R.string.pref_httpbase), "http://192.168.0.77/Walkad");
    new WebApiRegisterImei().execute(strBasePath + "/api/Phones/RegisterImei");
}

Note the i’m using the IP , the tablet used for the test is in the same WiFi network but is not Windows, is not able to resolve the NETBIOS name.

Note also that we web address is registered in a Preference that defaults to my WiFi address.

In order to follow the best practices , the routine WebApiRegisterImei is an AsyncTask so is called with .execute() passing the server address as String argument (in every case , if we don’t use an AsyncTask we get an NetworkOnMainThread exception):

private class WebApiRegisterImei extends AsyncTask<String, Void, JSONObject> {
    ProgressDialog myProgressBar;
    public String ExportResult = "";
    public String LastException = "";
    public Exception LastExceptionObject;       

    protected JSONObject doInBackground(String... params) {
        BufferedInputStream  in = null;
        HttpURLConnection urlConnection = null;
        try {
            URL url = new URL(params[0]);
            urlConnection = (HttpURLConnection) url.openConnection();
            // the Android docs says that HttpURLConnection uses the GET method by default. It will use
            // POST if setDoOutput(true) has been called. Other HTTP methods
            // (OPTIONS, HEAD, PUT, DELETE and TRACE) can be used with
            // setRequestMethod(String). Anyway forcing POST as request method is useful.
            urlConnection.setRequestMethod("POST");
            urlConnection.setDoOutput(true);
            urlConnection.setDoInput(true);
            //
            JSONStringer vm;
            vm = new JSONStringer()
                    .object().key("Imei").value(MainActivity.ImeiId)
                             .key("Manufacturer").value(android.os.Build.MANUFACTURER)
                             .key("Model").value(android.os.Build.MODEL)
                    .endObject();

            urlConnection.setFixedLengthStreamingMode(vm.toString().getBytes().length);
            urlConnection.setRequestProperty("Content-Type", "application/json");
            urlConnection.setRequestProperty("charset", "utf-8");
            DataOutputStream wr = new DataOutputStream (
                    urlConnection.getOutputStream ());
                    wr.writeBytes (vm.toString());
                    wr.flush ();
                    wr.close ();                           
            //
            int statusCode = urlConnection.getResponseCode();
            if (statusCode == HttpURLConnection.HTTP_UNAUTHORIZED) {
                // handle unauthorized (if service requires user login)
            } else if (statusCode != HttpURLConnection.HTTP_OK) {
                InputStream errorstream = urlConnection.getErrorStream();
                BufferedReader br = null;
                if (errorstream == null){
                    InputStream inputstream = urlConnection.getInputStream();
                    br = new BufferedReader(new InputStreamReader(inputstream));
                }else{
                    br = new BufferedReader(new InputStreamReader(errorstream));
                }
                String strResponse = "";
                String strTemp;
                while ((strTemp = br.readLine()) != null){
                    strResponse += strTemp;
                }   
                strResponse = strResponse.toLowerCase();
                if(strResponse.contains("pk_phones"))
                {
                    ExportResult = getErrorDuplicated();
                    return null;
                }
            }
            //
            ExportResult = getExportCompleted();
        } catch (Exception e) {
            Mint.logException(e);
            LastException = e.toString();
            LastExceptionObject = e;                
        } finally {
            if (in != null){
                try {
                    in.close();
                } catch (IOException e) {
                    Mint.logException(e);
                }
            }
            if(urlConnection != null){
                urlConnection.disconnect();  
            }
        }
        return null;
    }

    private String getErrorDuplicated() {
        return (String)getText(R.string.imeiduplicatederr);
    }

    private String getExportCompleted(){
        return (String)getText(R.string.registerimeidone);
    }           

    @Override
    protected void onPreExecute() {
        try {
            myProgressBar = new ProgressDialog(MainActivity.thisMainActivity, ProgressDialog.STYLE_SPINNER);    
            myProgressBar.setMessage((String)getText(R.string.registerimeiwait));
            myProgressBar.show();
        } catch (Exception e) {
            Mint.logException(e);
            Toast.makeText(getBaseContext(), (String)getText(R.string.genericerror),Toast.LENGTH_LONG).show();                  
        }
    }

    @Override
    protected void onPostExecute(JSONObject result) {
        try {
            myProgressBar.dismiss();
            if(LastException.length() > 0){
                Toast.makeText(getBaseContext(), (String)getText(R.string.genericerror),Toast.LENGTH_LONG).show();   
                Mint.logException(LastExceptionObject);
            }else{
                Toast.makeText(MainActivity.thisMainActivity, ExportResult, Toast.LENGTH_LONG).show();
            }
        } catch (Exception e) {
            Mint.logException(e);
            Toast.makeText(getBaseContext(), (String)getText(R.string.genericerror),Toast.LENGTH_LONG).show();                  
        }
    }
}

Examining the code , we can note that onPreExecute and onPostExecute are the only places where we can interact with the main thread and relative controls (the progress bar) so in the background part we register the exceptions details in one string and one object, examining them on the onPostExecute.

Note the use of the JSONStringer: debugging the code you can see that is created the JSON string as used in Fiddler.

If all is ok, we can see that in SQL Server is written a new row in the Phones table.

The test for statusCode != HttpURLConnection.HTTP_OK was the difficult part to discover how it works, the subject of this post.

If you reissue the command from the Android app on the same device you violate the primary key, so the WebAPI code goes in the Exception and is sent to the client “BadRequest” (HTTP error 400) instead of “Ok”(200), sending as other info ex.ToString().

The management of the returned error is done as you can read in the code: we need to examine the returned stream , by reading this stream we can create a string that contains the original ex.ToString, for example

{"$id":"1","$type":"System.Web.Http.HttpError, System.Web.Http","Message":"System.Data.Entity.Infrastructure.DbUpdateException: 
An error occurred while updating the entries. See the inner exception for details. ---> 
System.Data.Entity.Core.UpdateException: An error occurred while updating the entries. 
See the inner exception for details. ---> System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'PK_Phones'. 
Cannot insert duplicate key in object 'dbo.Phones'. The duplicate key value is…..

So in the Android code we search if the string contains the name of the primary key , giving to the user an scoped message, or we give to the user an generic message and the code uses Splunk Mint Express for registering all infos about the unmanaged exception (that could be an network failure, and so on).

Oracle RAC Linked server in Sql 2014 64 bit

I was needing to create a linked server from SQL Server 2014 64 bit, on a pc with Windows 8.1 64 bit , to an Oracle 10.2.0.3 configured in RAC (Real Application Clusters).

The RAC configuration is composed from 3 servers .

The first step is to install the Oracle software from http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html

Here must be downloaded 64-bit ODAC 12c Release 2 (12.1.0.1.2) for Windows x64 , the file ODAC121012_x64.zip because there is an guided installer.

After a while it starts then use the default choice, that is the Oracle Data Access Components which installs the Data Provider for .NET, Oracle Provider for OLEDB, Oracle Services for Microsoft Transaction Server, Oracle instant client.

Then use the Next button accepting all defaults until the end.

At the end there is a wizard for the compilation of tnsnames.ora file, but in this case we are working with a RAC and the wizard cannot manage this configuration.

After the installation is recommended to reboot, then we should see in Sql Management Studio the OraOLEDB provider listed:


By default in C:\app\studio\product\12.1.0\client_1\Network\Admin there is our TNSNAMES.ORA file

We must know the NETBIOS or the IP of the servers in RAC and the name of the Oracle instance, in our case TRAFSRV for example.

So the TNSNAMES.ORA , indicating the IP for each or our 3 RAC server, should be :

TRAFSRV =
  (DESCRIPTION =
   (LOAD_BALANCE = ON)
   (FAILOVER = ON)
   (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.86.13.19)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.86.13.21)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.86.13.23)(PORT = 1521))
   )
   (CONNECT_DATA =
    (SERVICE_NAME = trafsrv.world)
    (FAILOVER_MODE =
     (TYPE = SESSION)
     (METHOD = BASIC)
     (RETRIES = 5)
     (DELAY = 3)
    )
   )
  )

It should be verified that the 1521 port is opened in the firewall, in both Sql and Oracle servers.

Now we can create the linked server , for example

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'ORAARTA33', @srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'TRAFSRV'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ORAARTA33',@useself=N'False',@locallogin=NULL,@rmtuser=N'ORACLEUSER',@rmtpassword='ORACLEUSERPWD'

In this sample ORAARTA33 is the name of the linked server, it there are no problems we should see


Categories: .NET, Oracle, SQL Server

BI for Visual Studio 2013

Still unavailable…someday at this address.

Update 05/23 : available !

Categories: BI, SQL Server, SSIS