#sqlazure

[arshikasingh]

[ecorptrainings]

SQL Azure Online Training at Ecorptrainings Hyderabad India.

#SQL Azure is Microsoft’s cloud database service. Based on SQL Server database technology and built on Microsoft’s Windows Azure cloud computing platform, SQL Azure enables organizations to store relational data in the cloud and quickly scale the size of their databases up or down as business needs change.

ABOUT ECORPTRAININGS:

Ecorp Trainings are one of the best institute providing quality level of training in E-learning process.This is instructor led online training.

We also provide corporate training , if group of people interested in same technology.

Contact us for detailed course content & register for a free demo.

We also provide support in client interviews , resume preparation , ticket resolving.

Contact us for custom designed training course by experts exclusively for yourself.

We provide training for almost all IT technologies i.e ; JAVA , DOTNET , SAP ,ORACLE , PEOPLESOFT ,HYPERION etc, contact us if you have any particular need.

Contact:

Ecorptrainings

Email: training@ecorptrainings.com

USA: +1-703-445-4802 UK : +44 20 3287 2021

India: +91-7207043304 / 7207043306,+91-8143-111-555

Gtalk ID : ecorptrainings

Skype ID : ecorptrainings

For content:click here

[knowledgewiki]

Database first EF and ASP.NET Core 3 Web API published on Azure, connection string error

I’m developing an ASP.NET Core 3 Web API with a database first generated db context. I have the connection string in the appsettings.json file.

Everything is fine when I run it locally on IIS Express.

The problem is that when I publish it on Azure, it gives me the error:

System.ArgumentException: Keyword not supported: 'data source'. [...]

I noticed that the connection string, when published changed from:

metadata=res://*/DTOs.csdl|res://*/DTOs.ssdl|res://*/DTOs.msl;provider=System.Data.SqlClient;provider connection string='data source=*****;initial catalog=*****;persist security info=True;user id=*****;password=*****;MultipleActiveResultSets=True;App=EntityFramework'",

to:

metadata=res://*/DTOs.csdl|res://*/DTOs.ssdl|res://*/DTOs.msl;provider=System.Data.SqlClient;provider connection string="data source=*****;initial catalog=*****;persist security info=True;user id=*****;password=*****;MultipleActiveResultSets=True;App=EntityFramework""

As a work around I changed the line

services.AddScoped<palmdtos>(_ => new MyDbContext(Configuration.GetConnectionString("myConnectionString")));

to

services.AddScoped<palmdtos>(_ => new MyDbContext(Configuration.GetConnectionString("myConnectionString").Replace(""","'").Replace("&", "&")));

Is there a better way to do it?

2 Answers

Models created with the EF Designer are different from Code First in that your model already exists and is not generated from code when the application runs. The model typically exists as an EDMX file in your project.

The designer will add an EF connection string to your app.config or web.config file. This connection string is special in that it contains information about how to find the information in your EDMX file.

Refer to this article.

The cause was the connection string for one of the EDMX files we were using. Since the EDMX has to be read-only, we had to use a different connection string in Azure.

When replacing &quote; by a single quote ', it will work fine again. So go to azure website > Configuration > Connection strings > add your conn string with custom type.

Note: Make sure you also select Custom instead of SQLAzure for your Entity Framework connection string, even though the database runs on Azure.

Can you try updating the settings?

In Azure Panel:

Select App -> Application Settings -> Enter new Connection String -> Save

Archive from: https://stackoverflow.com/questions/59061094/database-first-ef-and-asp-net-core-3-web-api-published-on-azure-connection-stri

from https://knowledgewiki.org/database-first-ef-and-asp-net-core-3-web-api-published-on-azure-connection-string-error/

[gillyroze]

*** NEW FEATURE *** Azure AI Supporting @PyTorch 1.0 http://bit.ly/2IfxSHk  Follow Us @MSBISF #azure #BigData #DataPlatform #Cloud #AI #ArtificialIntelligence #MachineLeaning #DeepLearning #DataScience #ML #DL #sqlfamily #data #DataScience #sqlAzure #AzureDatawarehousepic.twitter.com/B06hEE1sdH *** NEW FEATURE *** Azure AI Supporting @PyTorch 1.0 Follow Us @MSBISF #azure #BigData #DataPlatform #Cloud #AI #ArtificialIntelligence #MachineLeaning #DeepLearning #DataScience #ML #DL #sqlfamily #data #DataScience #sqlAzure #AzureDatawarehousepic.twitter.com/B06hEE1sdH source

[dbareactions]

When Microsoft changes their cloud database brand name for the fifth time in four years

[sqlchamp]

New Database scoped Configurations

#New Database scoped Configurations #SQL #Azure #SQLAzure @SQLChamp MS introduced new Database scoped configurations in SQL Azure (V12) and SQL Server 2016; Need for new Database Scoped Configurations Have you ever faced a situation where one of the database requirements are totally different from other like MAXDOP settings and you wanted to set a different MAXDOP for a single DB; or you wanted a feature which can keep such Primary and secondary database configurations in sync. MS is listening and they introduced Database Scoped Configurations and you can change them with this DDL statement: Alter Database Scoped Configuration. This was a much needed enhancement. Here's a list of Database scoped configurations which are now available on DB level. These are not new configurations, but now you can also configure these options at the database level rather than the server level. So imagine you are planning to consolidate servers on a single instance, but earlier you were not able to do it because of different needs of such configurations, http://www.sqlchamp.com/2016/06/database-scoped-configurations/230 http://www.sqlchamp.com/2016/06/database-scoped-configurations/230

[mjliu]

The unintuitive Could Not SQLExecDirect RODBC Error and solution

So I've got this non sense error while using sqlSave

[RODBC] ERROR: Could not SQLExecDirect 'CREATE TABLE ... blah blah blah

Here is the short story, and it is important to talk about the setup, I'm using RODBC and FreeTDS to connect to the SQL Azure database. Remember it is SQL Azure database. First, it does not play nice with the database without a cluster key, or a key, so what need to do is addPK = T or longer version addPK = TRUE

Solution #1. two steps approach

This will help create the proper table structure, however it'll still fail you. Don't worry. Now what we need to do is to append the data sqlSave(ch, USArrests, addPK = T) It will tell you the error, then what you need to do next is to append the data sqlSave(ch, USArrests, tablename = 'USArrests', append = T) Now you ready to hail or shout hurray?!

Solution #2.

That is not me, I'm lazy, it should be done in one command

That solution does not seem to be making sense, here is the better version

sqlSave(ch, cust, tablename="dbo.customers", safer=F, addPK=T)

Safer = F does the trick. not sure if this is the bug from FreeTDS or RODBC sqlSave function. 

======

If you actually read this far, congratulations, you found the solution - INSTALL Microsoft SQL Server ODBC Driver

http://www.microsoft.com/en-us/download/details.aspx?id=36437 

[mrstevec]

Get Row Counts of all Tables in SQL DB (also Azure)

---- ROW COUNTS OF ALL TABLES ----

SELECT

    t.TABLE_SCHEMA + '.' + t.TABLE_NAME as 'TableName'

    ,[RowCount] = SUM(sp.[rows])

FROM INFORMATION_SCHEMA.TABLES t

JOIN sys.partitions sp

    ON sp.object_id = OBJECT_ID(t.TABLE_SCHEMA + '.' + t.TABLE_NAME)

WHERE TABLE_TYPE = 'BASE TABLE'

GROUP BY t.TABLE_SCHEMA + '.' + t.TABLE_NAME

ORDER BY t.TABLE_SCHEMA + '.' + t.TABLE_NAME

-----------------------------------

---- ROW COUNTS IN SQL AZURE ----

select 

t.schema_id,t.name as 'TableName'

, s.row_count as 'RowCount'

from sys.tables t

join sys.dm_db_partition_stats s ON t.object_id = s.object_id

and t.type_desc = 'USER_TABLE'

and s.index_id = 1

ORDER BY t.schema_id,t.name

---------------------------------

[ken-200]

これで小一時間詰まってた。結局、ローカルではSQLServerなどを使用して、Azureへデプロイする際に接続文字列をSQLAzure用に切り替えましょうとの事。

[usingcloud]

Microsoft SQL Server 2016 and Azure SQL Database / Database Engine Permissions Poster  #azurejp

Permissions (Database Engine)

(Permissions (Database Engine)から)

複雑過ぎる。

[sqlchamp]

AT Time Zone – Convert timezone directly using SQL

AT Time Zone – Convert #timezone directly using #SQL #azure @SQLCHAMP Globalization brings new opportunities and enhance economic growth to any business, but along with all the goodness it also brings complexity to manage different time zones. One has to be always on toes to match different times. but what if you've got an application\code where you have to convert timezone within SQL Server data? If you are using SQL Azure Databases or SQL Server 2016 you don't have to worry about this trouble because Microsoft introduced this brand new function to make things easy for you: AT TIME ZONE; AT Time Zone This function converts an inputdate to target time zone as per your wish, isn't it cool. I am loving it, it simplified too many things. Now I don't have to write code\use dlls to convert date time – DST was the biggest issue and this function simply takes care of DST on its own. http://www.sqlchamp.com/2016/06/at-time-zone/206 http://www.sqlchamp.com/2016/06/at-time-zone/206

[pgzdatalabs-blog]

Using related data in custom sync provider:The source of data is a database 4 which we'd use an out of box #:

[mjliu]

So I had some fun with Windows Azure VM and SQL Azure and of coz R

The Goal is to setup RStudio server and have it work with SQL Azure via RODBC

Step 1. Instal R

   26  sudo apt-get update

   28  sudo apt-get install r-base

Step 2. Install RStudio Server

   33  sudo apt-get install gdebi-core

   34  sudo apt-get install libapparmor1

   36  wget http://download2.rstudio.org/rstudio-server-0.98.1062-amd64.deb

   37  sudo gdebi rstudio-server-0.98.1062-amd64.deb

Step 3. Instal ggplot2, unixODBC and RODBC

   47  sudo apt-get install r-cran-ggplot2

   51  sudo apt-get install unixODBC

   52  sudo apt-get install r-cran-rodbc

Step 4. Install FreeTDS and configure the DSN to point to SQL Azure database

  123  sudo apt-get install tdsodbc

  127  sudo ln -s /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so /usr/lib/libtdsodbc.so

  128  sudo ln -s /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so /usr/lib/libtdsS.so

  130  odbcinst -j

  132  odbcinst -q -d

  154  tsql -H <azureserver>.database.windows.net -p 1433 -U mike -D <databasename>

  155 isql -v sqlserver mike pass 

here are the ini files

$ cat /etc/odbcinst.ini  # or ~/.odbcinst.ini

[FreeTDS]

Driver = /usr/lib/libtdsodbc.so

Setup  = /usr/lib/libtdsS.so

$ cat /etc/odbc.ini  # or ~/.odbc.ini

[sqlserver]

Description = SQL Azure

Driver = FreeTDS

Server = <azureserver>.database.windows.net

Database = <database name>

Port = 1433

TDS_Version = 8.0

[sqlchamp]

Find End-Of-Month (EOMONTH) date from SQLServer

Find End-Of-Month ( #EOMONTH ) date from #SQLAzure #SQL Hi Friends, have you ever struggled to find the end of the month date; I know its not a big deal you can get it from Calendar but what if you need that date for some business logic of yours? May be for Invoice processing or some other scheduling within SQL Server and you want to store \process that date? Yes, it becomes time consuming. Thankfully SQL Server has this (EOMONTH) datetime function which can be used to calculate the end of the month date automatically for any given month. Function:EOMONTH (this functions is available in Azure SQL Databases & starting SQL Server 2012) this takes two parameters http://www.sqlchamp.com/2016/05/eomonth-sql/199

[sqlchamp]

Know these restrictionslimitations before moving to SQLAzure

Know these restrictions #limitations before moving to #SQLAzure #SqlChamp As we already discussed SQLAzure is an offering under Microsoft Azure umbrella as PaaS (Platform as a service) & there are limitations of using SQLAzure (SQLDatabase) over SQLServer in Azure VM (or on-premise SQLServer) I am listing down all the limitations under specific headings which will help you prepare accordingly and decide before shooting the arrow (Making the switch). Make sure you read the detailed difference between both offerings so that you understand it better. The Limitations of SQLAzure are as follows: Troubleshooting & Audit related Limitations Eventing: events, event notifications, query notifications Read More at http://www.sqlchamp.com/2016/04/sqlazure-limitations/166 http://www.sqlchamp.com/2016/04/sqlazure-limitations/166