Protected: Power BI – Templates – Also transferring PBI without data

This content is password protected. To view it please enter your password below:

Advertisements

Run SQL Server with Docker

 

Why

    1. Developer machines
      On my developer machines I have installed various version of SQL Server. These run services in the background and are difficult to stop and uninstall.Containers offer the opportunity to install then destroy and start again.
    2. Development and Testing
      Store images/containers and then recreate
    3. Future: create images which derive from base images and store and share in a repository possibly in Azure Container Registry

Get Started:

Install docker for windows

https://docs.docker.com/docker-for-windows/install/

Turn on virtualisation
Hyper-V
Docker

Quickstart: Run SQL Server container images with Docker

https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker?view=sql-server-2017

Note on using PowerShell

Some docker commands do not work inside the ISE.
For those, or all commands use PowerShell console window

Connect from outside the container

From SqlCmd or SSMS

Use the IP address of the machine that hosts your container (probably your machine)
e.g. 10.0.0.21,1433

The function inspect gives information about the container, but not the host IP:
docker inspect

docker inspect –format ‘{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}’ <container id>

 

 

Restore a database into the image

Now, we have SQL running in docker, it would be useful to restore a database into that.

  1. Outside Docker
    Backup database
    Tip: SSMS backup in UI, but then “Script action to new query window” and store it
  2. Copy file into the running image
    Use Bash
    docker exec -it sql1 “bash”Then Linux commands to make directory
    Exit Bash
    docker cp MyDatabase.bak sql1:\var\opt\mssql\backup
  3. Inside Docker
    Restore database – use script – may need to Move files

 

Bake files into an image for reuse

docker commit sql1 sqltestco:sql2017
docker images

Clean up with:
docker rmi sqltestco:sql2017

 

Use image to create docker images

Now we have an image with our database in it. We may want to use it once or many times.

Keep changing the name and the port
I found that –name needs to come first

docker run –name sqltestco1 -d -p 1434:1433 sqltestco:sql2017
docker run –name sqltestco2 -d -p 1435:1433 sqltestco:sql2017

Now have 3 instances of SQL running.

As needed:

docker start
docker stop
docker rm

 

Push images to Docker Hub

Use:
docker images
docker tag <imagename>:<tagname> <dockerId>/<repository>:<tagname>
docker login (good idea to spell everything correctly)
docker push <dockerId>/<repository>:<tagname>

Took a few attempts – got there. Private repository.

 

 

Troubleshooting

Seem to get a conflict if other containers exist but are stopped. Possibly good practice to be aware of all containers with:

docker ps -a

Error response from daemon: … userland proxy

Error response from daemon: driver failed programming external connectivity on endpoint … Error starting userland proxy: mkdir /port…

Restart docker – fixed it for me.

 

See also

Pluralsight : Getting started with Docker on Windows

https://app.pluralsight.com/library/courses/docker-windows-getting-started/table-of-contents

 

 

 

Upgrade TFS 2010 to 2015 on-premises

Was concerned this was going to be hard work, but turned out better than expected.

Really brief notes:

Install

TFS Server 2015 will install SQL Server 2014 standard

 

Process template upgrades

If the TFS work items intranet console does not work then:

  1. https://msdn.microsoft.com/library/vs/alm/work/customize/add-features-manually#update-processconfig
  2. Download process template
  3. I created .bat files to practice with a less significant project, then moved to main projects. Sample script below. Must be ran as administrator. May be remote to the TFS Server.
    Had to repeat for various work item types

 

cd C:\
set root="%programfiles(x86)%\Microsoft Visual Studio 14.0\Common7\IDE"
cd %root%
pause
witadmin importwitd /collection:http://<server>:8080/tfs/DefaultCollection /p:"<project>" /f:"D:\Users\<user>\Documents\Visual Studio 2015\VS-TFS Process Templates\Agile\WorkItem Tracking\TypeDefinitions\UserStory.xml"
echo Done
pause

 

 

 

 

Create an ODBC using a reg edit file

With thanks to:

https://kencenerelli.wordpress.com/2012/07/26/export-an-odbc-data-source-from-the-registry/

Save following as a file e.g. MyODBCName.reg
Double-click to install into the registry


Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\ODBC Data Sources]
"MyODBCName"="SQL Server Native Client 11.0"

[HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI\MyODBCName]
"Driver"="C:\\Windows\\SysWOW64\\sqlncli11.dll"
"Server"="MyServerName"
"Database"="MyDatabaseName"
"LastUser"="MyLastUserName"

Logging in .Net and My.Application.Log.WriteEntry does not write until flush

In app.config you need to remove the default listener:

<remove name=Default/>

Documentation on MSDN is ok, when testing you need to remember that the log will not write until either the application is closed, or the log is flushed. So:

My.Application.Log.DefaultFileLogWriter.AutoFlush = True
My.Application.Log.DefaultFileLogWriter.Flush()
My.Application.Log.TraceSource.Flush()

 

 

SQL Server Agent Job Verbose Logging

Please see useful page:

https://www.mssqltips.com/sqlservertip/1411/verbose-sql-server-agent-logging/

  1. In Job Step – Advanced – Output file
  2. Likely strategy is that on first step do not tick “Append output to existing file”
  3. On subsequent steps tick this and then you will get the full output of the series

 

 

Bug: Source not found, but some or all event logs could not be searched.

Bug:

The source was not found, but some or all event logs could not be searched. To create the source, you need permission to read all event logs to make sure that the new source name is unique. In accessible logs:Security

This can occur if a .Net application attempts to create the source at runtime, but not as an administrator. It is better to perhaps create the source ahead of time as an administrator in regedit, then just use the source in the application.

Private _eventLog As New EventLog(“Application”)
‘EventLog.CreateEventSource(SourceName, LogName, MachineName)

In registry at:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\EventLog\Application\
Add permission to Authenticated Users = Full Control

Or add to the specific key.

With thanks to:
https://sandroaspbiztalkblog.wordpress.com/2012/02/16/the-source-was-not-found-but-some-or-all-event-logs-could-not-be-searched-inaccessible-logs-security/

 

 

How to keep the task bar visible when using a maximised RDP client

I use RDP a lot now, but would like to be able to see my own PC’s task bar, for instance Outlook may show messages.
It is possible, with thanks to:
http://notworthrepeating.blogspot.co.uk/2012/03/maximizing-remote-desktop-client.html

So:

  1. Edit your .RDP file using Remote Desktop Client, making sure the “Display” tab is configured for “Full Screen”, and then “Save” the .RDP file again (this bakes your local resolution into the file, which is important)
  2. Edit the .rdp in notepad:
    1. screen mode id:i:2  – change this line to the following, to use windowed mode instead of fullscreen
      screen mode id:i:1
    2. Add this line to the file if it doesn’t exist already:
      smart sizing:i:1
    3. Change nothing about (but take notice of) two lines that talk about “desktopwidth” and “desktopheight”
  3. Save and close the .RDP file

Thanks.

 

Calling a WCF service using SOAP in unit test or with VB Script on a classic ASP page

I created a WCF service in .Net and this was fine for .Net customers who use the Add Service Reference wizard easily enough.
However one customer was on classic ASP and he found it difficult to connect. So when we had succeeded together it was worth writing down. This is the result.
Please ignore the property naming. I don’t use strPropertyName. This isn’t working code, just an idea of what is required.

Solved.

Tips:

  • Inspect the .wsdl file
  • Note the soapAction setting
  • Note the namespace. The WCF namespace will include the interface name

Unit test written in VB.Net for SOAP

Public Sub SOAPClientHelloWorldTestMethod()

strURLSubFolder = “ServiceName.svc”
strInterfaceName = “IServiceName”
strFunction = “HelloWorldMethod”
Dim parameter1 = “parameter1”

strRequest2FunctionNameOpen = “&lt ns1:HelloWorldMethod &gt”
strRequest3Parameters = “&lt” + parameter1Value + “&gt”
strRequest4FunctionNameClose += “&lt ns1:HelloWorldMethod &gt”

CallSOAP()

End Sub

Then the SOAP call method

Private Sub CallSOAP()

‘URL to SOAP namespace and connection URL
Dim strURLBase = “http://serviceaddress.net/&#8221;
Dim strURL = strURLBase + strURLSubFolder
Dim strNamespace = “http://tempuri.org/&#8221;

strRequest1SOAPPart1 = “<?xml version=””1.0″” encoding=””UTF-8″”?>” & _

“<SOAP-ENV:Envelope ” & _

“xmlns:SOAP-ENV=””http://schemas.xmlsoap.org/soap/envelope/”&#8221; ” & _

“xmlns:ns1=””” & strNamespace & “””>” & _

“<SOAP-ENV:Body>”

strRequest5SOAPEnd = “</SOAP-ENV:Body></SOAP-ENV:Envelope>”

Dim strRequest = strRequest1SOAPPart1 + strRequest2FunctionNameOpen + strRequest3Parameters + strRequest4FunctionNameClose + strRequest5SOAPEnd
TestContext.WriteLine(“URL = ” + strURL)
TestContext.WriteLine(“Request = ” + strRequest)
Dim soapAction As String
soapAction = strNamespace & strInterfaceName & “/” & strFunction
TestContext.WriteLine(“SOAPAction = ” + soapAction)

Dim objectName As String
‘objectName = “Msxml2.XMLHTTP.3.0”
‘objectName = “MSXML2.DOMDocument.6.0”
objectName = “MSXML2.ServerXMLHTTP”

Dim soap_request As Object
soap_request = CreateObject(objectName)

Dim contentType As String
contentType = “text/xml; charset=utf-8”
‘contentType = “application/x-www-form-urlencoded”

soap_request.open(“POST”, strURL, False)
soap_request.setRequestHeader(“Content-Type”, contentType)
soap_request.setRequestHeader(“Content-Length”, Len(strRequest))
soap_request.setRequestHeader(“SOAPAction”, soapAction)

‘send the request and capture the result
soap_request.send(strRequest)
Dim strResult = soap_request.responseText
soap_request = Nothing

‘display the XML
‘response.write (strResult)
‘TestContext.WriteLine(strResult)
TestContext.WriteLine(“”)
Dim xml = XDocument.Parse(strResult)
TestContext.WriteLine(xml.ToString())

End Sub

Classic ASP

Title
<%
Dim strRequest, strResult, strElement, strURL, strNamespace,interfacename,soap_request,parameter1

‘ Define services
strURL = “http://serviceaddress.svc&#8221; ‘ Service URL
strNamespace = “http://tempuri.org/&#8221; ‘ Name space
interfacename=”IServiceName/” ‘ interface name
strElement = “GetMethod” ‘ Element

set soap_request = Server.CreateObject(“Msxml2.XMLHTTP.3.0″)
strRequest =”<?xml version=””1.0″” encoding=””UTF-8″”?>”

strRequest=strRequest& “<SOAP-ENV:Envelope xmlns:SOAP-ENV=””http://schemas.xmlsoap.org/soap/envelope/”&#8221; xmlns:ns1=”””&strNamespace&”””>”

strRequest=strRequest& “<SOAP-ENV:Body>”

strRequest=strRequest& “<ns1:HelloWorldMethod>”

strRequest=strRequest& “<ns1:parameter1>”&soap_user&”</ns1:parameter1>”

strRequest=strRequest& “</ns1:HelloWorldMethod>”

strRequest=strRequest& “</SOAP-ENV:Body>”

strRequest=strRequest& “</SOAP-ENV:Envelope>”

soap_request.open “post”, “”&strURL&””, False
soap_request.setRequestHeader “Content-Type”, “text/xml; charset=utf-8”
soap_request.setRequestHeader “SOAPAction”,strNamespace&interfacename&strElement
soap_request.setRequestHeader “Content-Length”,Len(strRequest)

Call soap_request.send(strRequest) ‘ Send the soap request and capture the result
strResult = soap_request.responseText

‘display the XML
if strResult”” then
Set ResponseXML = Nothing
end if
Set soap_request = Nothing

end if

%>

Device and Resource Redirection

https://technet.microsoft.com/en-us/library/cc725887(v=WS.10).aspx

Audio redirection

  1. Run
  2. Type “group” to open Edit group policy
  3. Local computer policy
  4. Computer configuration
  5. Administrative Templates
  6. Windows Components
  7. Remote Desktop Services
  8. Remote Desktop Session Host
  9. Device and Resource Redirection
  10. Allow Audio and video playback redirection
  11. Change from Not Configured to Enabled.