Geeks 4 Data

If you have worked with PowerShell remoting then you will have seen this error before: : Connecting to remote server BLAH failed with the following error message : The WinRM client received an HTTP status code of 403 from the remote WS-Management service. For more information... It is not a happy message, especially when you have been using PowerShell to remotely manage the particular server for ages! So then you try remoting from another client and it works! You go back to your original client and try remoting to anything else and it fails...dohh! "But this worked just yesturday!" you scream. Ahh, little things can make a big difference and in my case the issue was related to a VSTS Agent update that I did the day before. In order for the new version of the agent to communicate with VSTS in the cloud I needed to set a WinHTTP proxy. Once the agent was configured I could use a .proxy file in the agent directory instead...but I forgot to remove the WinHTTP proxy and in s...

Simple one this... How can we enable inbound transactions in MSDTC on a core machine? Well we use PowerShell of course! :-) PS C:\>Set-DtcNetworkSetting -InboundTransactionsEnabled $True Nice one.

We sometimes use the Biztalk WCF adapter to talk to SQL Server and for that we need there to be an endpoint stored procedure in the target database for BTS to execute. When the adapter needs to execute the procedure to get or post data to the database, it will first execute a metadata query to confirm the stored procedure's existence - this is unusual behavior and it can lead to some head scratching if the lights are not all on :-) This is an example of the query that BTS executes: exec sp_executesql N'SELECT sp.type AS [ObjectType], modify_date AS [LastModified]FROM sys.all_objects AS sp WHERE (sp.name=@ORIGINALOBJECTNAME and SCHEMA_NAME(sp.schema_id)=@ORIGINALSCHEMANAME);SELECT [param].name AS [ParameterName], usrt.name AS [DataType], SCHEMA_NAME(usrt.schema_id) AS DataTypeSchemaName, baset.name AS [SystemType], usrt.is_table_type as IsTableType, usrt.is_assembly_type as IsAssemblyType, CAST(CASE WHEN baset.name IN (N''nchar'', N''nvarchar''...

If you use SQL Server Data Tools and deploy your database code using the SQL Server Database Project in Visual Studio, then read on. If you need to deploy a database to multiple instances and have some objects only deploy to certain instances, read on. If you didn't know that you can reference a database project within another database project and have the reference database schema included as part of the database, read on. If you didn't know that you need to specifically configure your deployment to include composite objects when you have a 'composite' database project, you do now :-) Later dudes!

We use SQL dacpacs for database references in our SQL Data Tools projects. Sometimes the database we need to reference is a vendor database with loads of issues in it, so if we try and extract a dacpac using management studio, it just bombs out with an ugly exception. I have seen all kinds of issues when doing this. Your first though may be to re-create the database using scripts and only include the objects that you need. That works but it's a bit of a pain to look after. What we really need is way to extract the dacpac, warts and all, and the only way I know how to do that is by using the SqlPackage.exe command line tool. Use the following to extract a simple dacpac for a reference: C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin\SqlPackage.exe /Action:Extract /SourceServerName:SQLCLUSTER3 /SourceDatabaseName:$(DbName) /TargetFile:"C:\Temp\$(DbName) .dacpac" Simples dudes!

The scenario: GIVEN a SQL 2000 data source AND a SQL 2016 destination AND a metadata driven, BIML generated SSIS package to move data from source to destination WHEN you try build the SSIS package using an OLEDB Connection THEN SSIS says it can't connect because SQL 2000 is not supported So, what do you do? Well obviously ODBC comes to mind and so you try that avenue (ala ODBC Driver for SQL Server) only to find you're presented with another unfriendly message: Now what? Well, simply use the SQL Server provider and that will work. SSIS is able to use it with an ODBC connection, so all is good, until you execute your package and get the most excellent of exceptions, the dreaded VS_NEEDSNEWMETADATA ! Or, in my words "AAAAHHHHGGGRRRRHHH!!!" What just happened? My package built without any issues and didn't throw any warnings so why does this happen when I run it? Weird ODBC behavior I guess. Turns out that the ODBC connection defaults to UNICODE (whe...

I have been doing Scrum for years and have had some great teachers ( Richard Hundhausen , Derek Davidson and even Jeff Sutherland  - well, I read his book :-)) but one very important aspect of of the framework has continued to haunt me until now. I have always struggled to explain the difference between effort and work required and no matter how many times I suggested that effort was related to complexity I was never able to come up with an analogy or explanation that would suffice. The team would constantly revert to the assumption that effort meant how much of the sprint (therefore time) would be needed to complete the work; I would say, "no, that is what we use work remaining for" and the team would invariably reply "what's the point of effort then?". The other day while explaining the importance of doing design in the planning meeting to a bunch of colleagues, one very astute colleague asked the question "won't we run out of time in the planni...