Archive for the ‘SQL Server’ Category

SQLDMO for older programs

Sometimes i must maintain ancient software, Visual Basic 6 applications of the last millennium.
I still wondering why opening an Visual Basic 6 project after some years, i remember that was all ok and i’m the only one in the world that opens this ancient vm, magically some DLL reference is no more working.
So some code that was using SQL-DMO was causing the error “can’t create the object”.
In a folder there was a sqldmo.dll, no way to register/unregister, delete and the add the reference.
Fortunately i found this download, after the install pointing the dll reference to c:\Programs\Microsoft SQL Server\80\Tools\binn\sqldmo.dll (is a Windows XP vm…) my program can run; by the way connecting to SQL Server 2016 Developer inside a Docker Windows container.

Categories: SQL Server, Visual Basic 6

SQL Server Developer 2016 containerized under Docker for Windows

Until the last format of my notebook (end of August: there was no way to repair Office 2016 , and after i got a malware from the infamous CCleaner 5.33) SQL Server Developer was installed with the other programs.
But this time my approach was “no more VmWare Workstation” because it conflicts with Hyper-V needed from Docker.
Currently i’m ok with Hyper-V even if there are issues, as the access from the vm to the host that i resolved with the mapping of the host disks as “Local resouces” in the remote desktop connection: not the better approach, but for me works.
Then I decided to use SQL Server 2016 Developer in a container: could be that one day in my pc there will be only Windows 10, Hyper-V, Docker and all of the programs (Office, Visual Studio, Android Studio, SQL Server..) will be containerized?
Installed Docker for Windows, i opened a cmd shell and launched

c:\>docker pull microsoft/mssql-server-windows-developer

After some Gb of download, the Docker page tell us that we can launch

c:\>docker run -d -p 1433:1433 -e sa_password=<SA_PASSWORD> -e ACCEPT_EULA=Y microsoft/mssql-server-windows-developer

Well, at this point our server is running, the sa user password was initialized to the passed value and i can do

c:\>docker ps
CONTAINER ID        IMAGE                                   <other columns..>
5fd521dc8565        microsoft/mssql-server-windows-developer

We have the CONTAINER ID that we can use for connecting to our instance, for example (note that we can use the entire CONTAINER ID but also only the first 3 chars)

docker exec -it 5fd sqlcmd -S. -Usa -P<SA_PASSWORD>

and if all is ok we can see the SQLcmd prompt, launch some commands:

1> select @@version
2> go
Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)
        Oct 28 2016 18:17:30
        Copyright (c) Microsoft Corporation
        Developer Edition (64-bit) on Windows Server 2016 Datacenter 6.3 <X64> (Build 14393: ) (Hypervisor)

(1 rows affected)
3> Quit

Ok well, but at this point I would use SQL Server Management Studio, use my MDF files that are in the main pc filesystem.
First of all, by default the containers change the container name (sometimes with hilarious names, as “drunken_borg”) and the IP, which is tipically a private IP network 172.xx.xx.xx.
If you do a

c:\>docker stop 5fd

and then relaunch the “docker run” you will see (with a docker inspect CONTAINER_ID) that the container name and the IP are changed, and if some services rely on your SQL is a problem.
But for the moment we connect from SQL Management studio on the host, if our server has IP we would try

But without success, we have an error about the certificate chain:

After some head scratching, i finally found that we need to set in Options >> Additional Connection Parameters this magic setting, TrustServerCertificate=True:

Done this, we can finally see our server:

But if we try to attach our MDF files that are in the host, no way, we can explore only the local c: (of the containerized Windows Server which runs our SQL instance)

The solution is to use the Docker Volume parameter: we need to changes some parameters in the SQL server starting, so close SQL Management Studio, run a

c:\>docker stop <CONTAINER ID>

and then

c:\>docker rm <CONTAINER ID>

The new command is:

c:\>docker run -d -p 1433:1433 --name sqlcontainer1 –hostname mysqlhost --ip -v G:\DbAndMail:c:\data -e sa_password=<SA_PASSWORD> -e ACCEPT_EULA=Y microsoft/mssql-server-windows-developer

In this command:

-p 1433:1433 : maps the local port 1433 to the 1433 port of the container

–name mysqlhost: we will have our defined host name, no “drunken_borg”

–ip we define a fixed IP for our instance, so the connected clients that uses our SQL can rely on a certain value

-v G:\DbAndMail:c:\data : we map the host path G:\DbAndMail on the container c:\data; the path inside the container will be created if not exists.

Entering in SQL Management Studio, we can this time explore our host path mounted in c:\data in order to attach the already existents MDF files.

At this point if we launch

c:\>docker stop <CONTAINER ID>

and then

c:\>docker run -d -p 1433:1433 --name sqlcontainer1 …

Docker complains that sqlcontainer1 already exists, we need a docker rm.
But in this manner we lose the changes, if we attached an existing MDF in the previous SQL Management Studio session every previous change in the SQL data is maintained but we must re-attach the MDF, every configuration change for example

exec sp_configure "remote access", 1

is lost: is another instance.
So we need to see the NEW_CONTAINER_ID with

c:\>docker ps – a 
CONTAINER ID        IMAGE                                      …
05e581c12329        microsoft/mssql-server-windows-developer …

where we can see the stopped containers, and then launch

c:\>docker start -ai  05e
CONTAINER ID        IMAGE                                      …
05e581c12329        microsoft/mssql-server-windows-developer …
where we can see the stopped containers, and then launch
c:\>docker start -ai  05e
VERBOSE: Starting SQL Server
VERBOSE: Changing SA login credentials
VERBOSE: Started SQL Server.

TimeGenerated          EntryType Message
-------------          --------- -------
9/26/2017 4:55:56 PM Information Software Usage Metrics is enabled.
9/26/2017 4:55:55 PM Information Recovery is complete. This is an informatio...
9/26/2017 4:55:55 PM Information Service Broker manager has started.
9/26/2017 4:55:55 PM Information The Database Mirroring endpoint is in disab...
9/26/2017 4:55:55 PM Information The Service Broker endpoint is in disabled ...
9/26/2017 4:55:55 PM Information The tempdb database has 2 data file(s).

We can see that in SQL Management Studio nothing was changed, we find our previously attached MDF and other changes.

Categories: Docker, SQL Server

SQL Server 2016 Service Pack 1 with updates

Categories: SQL Server

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.collation_name
sys.columns col
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:

   'Not specified' 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')
	EXEC msdb.dbo.sp_delete_job @job_name = '_tmp_batch'
-- 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


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))
   INSERT INTO @arrValues (val) VALUES ('IL')
   INSERT INTO @arrValues (val) VALUES ('AR')

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

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

is working , instead

   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