Joel Greijer

with  animal  as (    select   *   from        unnest ([ struct        ( 1   as  zoo_id,  'Horse'   AS  animal_name,  1   as  animal_count ) ,        ( 1 ,  'Cow' ,  25 ) ,        ( 2 ,  'Old World Swallowtail Butterfly' ,  1000   ) ,        ( 4 ,  'Fish'  , 5500 ) ,        ( 5 ,  'Beetle'  , 700000 )      ])   ) , zoo  as (    select   *   from        unnest ([ struct        ( 1   as  zoo_id,  'Skansen'   as  zoo_name,  100   as  city_id ) ,        ( 2 ,  'Fjärilshuset' ,  100   ) ,   ...

Converting iso dates with timezones to BigQuery timestamp can be a hassle if the date contains more than six decimals. select   cast ( '2023-03-21T06:49:19.1234567+00:00'   as   timestamp ) Invalid timestamp: '2023-03-21T06:49:19.1234567+00:00' One solution could be to truncate the fractional part like so create   or   replace   function  [your_dataset].truncate_iso_timestamp_string ( detailed_iso_timestamp  string ) /* big query crashes with more than 6 decimals on dates This function will truncate to 6 decimals and cast to timestamp. */ returns   timestamp as   (    cast (      concat (        left (          split ( detailed_iso_timestamp, '+' )[ offset ( 0 )] ,  26 ) ,          '+' ,    ...

If you ever exported a csv file from MsSql via bcp you probably got a csv file filled with NUL chars When using bq load you get this error  BigQuery error in load operation: Error processing job '[project]:[job_id]': Error while reading data, error message: Error detected while parsing row starting at position: 0. Error: Bad character (ASCII 0) encountered . File: gs://[bucket]/[path]/[file].csv Failure details: - Error while reading data, error message: CSV processing encountered One solution is to let bq load these characters using this flag --preserve_ascii_control_characters=true Note that the unicode general "unknown character"  �  symbol is inserted instead of null and you may want to clean this post bulk. But at least bq load does not crash.

  $ErrorActionPreference = "stop" $server = "your-server.database.windows.net" $database = "your-database" $query = "select 1 from dbo.yourtable" Connect-AzAccount $token = ( Get-AzAccessToken -ResourceUrl "https://database.windows.net" ).Token Invoke-Sqlcmd -server $server -Database $database -Query $query -as dataset -AccessToken $token write-output "Done!"

This is how to grant user from system assigned identity (e.g. automation account) to allow to connect and query azure sql database without any use of keys, secrets, vaults or hard coded passwords. The app does not have to be contributor in azure sql instance for this to work Enable System Assigned Identity Navigate to Automation Account / Identity Set System Assigned: On Copy Object (Principal) Id guid Navigate to Azure Active Directory Search for the guid in "Search your tenant" box Find the "enterprise application" popping up Copy the Name this is what you use below [App_Name] Grant access to Azure SQL Database CREATE USER [App_Name] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA = dbo ALTER ROLE db_datareader ADD MEMBER [App_Name] ALTER ROLE db_datawriter ADD MEMBER [App_Name] How to use from runbook (powershell) $server  =  "your-sql-server-name.database.windows.net" $database  =  "your-database-name" $query  =  "sel...

It's very tricky to find the schema of the crucial .configsettings file that is used when automating deployments of ssas projects.  It has come to my understanding that msbuild does not produce this file for Tabular Model projects. Though in some cases Microsoft forget to mention this in their guides.  I know clarification is pending on this page https://docs.microsoft.com/en-us/analysis-services/deployment/deployment-script-files-solution-deployment-config-settings?view=asallproducts-allversions Meanwhile. Here is a sample of the file in case you need it <ConfigurationSettings xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http:...

Since last update to Version: 2.85.681.0 64-bit (september 2020) 100% fail rate connecting to other than your local SQL server instance. Details: "Microsoft SQL: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: HTTP Provider, error: 0 - )" This happens With encryption Without encryption Authentication: Windows  Authentication: Microsoft account Solution Download August 2020 version and install Microsoft hides older versions as last one becomes available so you have to get them from someone who saved it. I have the 64 bit version. Contact me if you need it.