The new `STRING_SPLIT` [method][1] is not available in my Azure SQL database. I had already ran `ALTER DATABASE [DatabaseName] SET COMPATIBILITY_LEVEL = 130` a couple days ago, and I have verified the compatibility level is indeed set to 130. `SELECT database_id, name, compatibility_level FROM sys.databases`

Has anyone else been able to use the new method, and if so, did you need to do anything else to get it working?


  [1]: https://msdn.microsoft.com/en-us/library/mt684588.aspx

&#39;STRING_SPLIT&#39; is not a recognized built-in function name

Could you guide me on how to view the current roles/permissions granted to any database user in Azure SQL Database or in general for a MSSQL Server instance? 

I have this below query: 

    SELECT r.name role_principal_name, m.name AS member_principal_name
    FROM sys.database_role_members rm 
    JOIN sys.database_principals r 
        ON rm.role_principal_id = r.principal_id
    JOIN sys.database_principals m 
        ON rm.member_principal_id = m.principal_id
    WHERE r.name IN (&#39;loginmanager&#39;, &#39;dbmanager&#39;);

I further need to know what are the permissions granted to these roles &quot;loginmanager&quot; and &quot;dbmanager&quot;? 

Could you help me on this?


How to view the roles and permissions granted to any database user in Azure SQL server instance?

I have an application that I am just migrating to Azure. Currently I use web.config transformation to manage changing the database connecting string dev/staging/prod environments. How is it best to manage these multiple connection strings in Azure?

Azure connection string best practices

Does anyone know how I can copy a SQL Azure database to my development machine? I&#39;d like to stop paying to have a development database in the cloud, but it&#39;s the best way to get production data. I copy my production database to a new development database but I&#39;d like to have that same database local. 

Any suggestions? 

How do I copy SQL Azure database to my local development server?

I have inserted records into a SQL Server database table. The table had a primary key defined and the auto increment identity seed is set to “Yes”. This is done primarily because in SQL Azure, each table has to have a primary key and identity defined. 

But since I have to delete some records from the table, the identity seed for those tables will be disturbed and the index column (which is auto-generated with an increment of 1) will get disturbed.

**How can I reset the identity column after I deleted the records so that the column has sequence in ascending numerical order?**

The identity column is not used as a foreign key anywhere in database.

Reset identity seed after deleting records in SQL Server

I have got into a bad state with my ASP.Net MVC 5 project, using Code-First Entity Framework. I don&#39;t care about losing data, I just want to be able to start fresh, recreate the database and start using Code-First migrations. 

Currently I am in a state where every attempt to Update-Database results in an exception being thrown or getting an error message. Also the website can&#39;t access the database correctly. How can I wipe all migrations, re-create the database and start from scratch without having to create a new project? In other words, I want to keep my code but drop the database.

Later I will also want to get the deployment database (SQL Server on Azure) in sync. Again, I don&#39;t mind dropping all the data - I just want to get it working.

Please provide any how-to steps to get back to a clean state. Much appreciated.

How to re-create database for Entity Framework?

I&#39;ve created a BACPAC backup of my Azure SQL Database using the &quot;Export&quot; option within the Azure Management Console.

Having downloaded this to my machine I&#39;m a little stuck on how I can restore this to a local SQL Server instance. I came across the DacImportExportCli tool but couldn&#39;t find an example of a local restore.

Also if anyone has written a script that does this (so it can be scheduled) that would be awesome.

Azure SQL Database Bacpac Local Restore

Is there any way to stop a SQL Azure DB so that it doesn&#39;t charge anything towards our account? I don&#39;t want to delete it, just while in testing and it&#39;s not being used than we set it to &quot;stopped&quot; like we can do with websites, cloud services, and VMs.


Stopping SQL Azure DB when not in use

I&#39;m finding a way to aggregate strings from different rows into a single row. I&#39;m looking to do this in many different places, so having a function to facilitate this would be nice. I&#39;ve tried solutions using `COALESCE` and `FOR XML`, but they just don&#39;t cut it for me.

String aggregation would do something like this:

    id | Name                    Result: id | Names
    -- - ----                            -- - -----
    1  | Matt                            1  | Matt, Rocks
    1  | Rocks                           2  | Stylus
    2  | Stylus

I&#39;ve taken a look at [CLR-defined aggregate functions][1] as a replacement for `COALESCE` and `FOR XML`, but apparently **SQL Azure** *does not* support CLR-defined stuff, which is a pain for me because I know being able to use it would solve a whole lot of problems for me.

Is there any possible workaround, or similarly optimal method (which might not be as optimal as CLR, but *hey* I&#39;ll take what I can get) that I can use to aggregate my stuff?

[1]: http://picnicerror.net/development/sql-server/concatenate-multiple-row-values-sql-server-with-clr-2012-01-10/

Optimal way to concatenate/aggregate strings

I moved a database from SQL Server 2012 to Azure.  I don&#39;t want to use the user `master`, so I created a user `test`.  This is what I did for database XXX on Azure:

    create user test from login test with default_schema=[dbo]
    exec sp_addrolemember &#39;db_owner&#39;,&#39;test&#39;

I checked, and the database objects I am interested in are all in the schema `dbo`.  The table `Users` is in the schema `dbo`.

The connection string in my web project has `test` as the login.  It produces the error message:

    The SELECT permission was denied on the object &#39;Users&#39;, database &#39;XXX&#39;, schema &#39;dbo&#39;

What does the error message mean and what can I do to let user `test` access the database XXX?

The SELECT permission was denied on the object &#39;Users&#39;, database &#39;XXX&#39;, schema &#39;dbo&#39;

I have a new Azure account. I am able to log into the &#39;manage&#39; page as admin, but I forgot the password to one of my databases. I would like to reset the password on that one DB. How do I do that? Microsoft doesn&#39;t seem to have a KB on that - at least not one I could find. Thx.

Password reset for Azure database

After the new model was implemented, all of my websites now belong to individual Resource Groups called &quot;Default-Web-East&quot; and all of my SQL databases belong to individual Resource Groups called &quot;Default-SQL-East&quot;.

This is confusing to say the least.

I would like to rename the groups to have some semantic meaning.  I would also like to group the associated SQL database and Web Site in the same Resource Group. 

However, I do not see anyway to do either.  Is this possible?

1) Rename the Resource Group?
2) Combine an existing SQL DB and Website together into one Resource Group?

How do I change the name of an Azure Resource Group?

With the new [Azure SQL Database tier structure][1], it seems important to monitor your database &quot;DTU&quot; usage to know whether to upgrade or downgrade to another tier.

When reading [Azure SQL Database Service Tiers and Performance Levels][2], it only talks about monitoring with CPU, Data and Log percentage usage.

But, when I add new metrics, I also have an **DTU percentage** option:

![Add Database Metrics][3]

I can&#39;t find any about this online.  Is this essentially a summary of the other DTU-related metrics?


  [1]: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-purchase-models
  [2]: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-service-tiers-dtu
  [3]: http://i.stack.imgur.com/BXH2g.png

Azure SQL Database &quot;DTU percentage&quot; metric

I am trying to use the following template:

    -- =================================================
    -- Create User as DBO template for SQL Azure Database
    -- =================================================
    -- For login &lt;login_name, sysname, login_name&gt;, create a user in the database
    CREATE USER &lt;user_name, sysname, user_name&gt;
    	FOR LOGIN &lt;login_name, sysname, login_name&gt;
    	WITH DEFAULT_SCHEMA = &lt;default_schema, sysname, dbo&gt;
    GO
    
    -- Add user to the database owner role
    EXEC sp_addrolemember N&#39;db_owner&#39;, N&#39;&lt;user_name, sysname, user_name&gt;&#39;
    GO

I would like to create a user called user1 with a password of &#39;user1pass&#39;. I connected with my default database &#39;authentication&#39; and I have a query window open.

But the template does not make sense for me. For example what&#39;s sysname, where do I supply the password and what should I use as the default_schema?

The particular user needs to have the power to do everything. But how do I set it up so he can do everything, is that done if I make the user a database owner?

So far I have tried:

    CREATE USER user1, sysname, user1
    	FOR LOGIN user1, sysname, user1
    	WITH DEFAULT_SCHEMA = dbo, sysname, dbo
    GO

Giving:

&gt; Msg 102, Level 15, State 1, Line 1 Incorrect syntax near &#39;,&#39;.

and:

    CREATE USER user1
    	FOR LOGIN user1
    	WITH DEFAULT_SCHEMA = dbo
    GO

Giving:

&gt; Msg 15007, Level 16, State 1, Line 1 &#39;user1&#39; i

s not a valid login or you do not have permission.




How do I create a new user in a SQL Azure database?

For two environments, local and cloud, how would I set up custom settings or parameters for resources such as Sql databases, storage accounts, etc... Ideally it would be one parameter name called in code to say, point a DbContext towards a particular database, that in configurations for either a local or cloud environment be different. Thank you. 

Where do you set and access run-time configuration parameters per environment for service fabric?

When I am querying from database in continuous looping, after some time 
I get an error :

&gt; An exception has been raised that is likely due to a transient
&gt; failure. If you are connecting to a SQL Azure database consider using SqlAzureExecutionStrategy.

Normally it is working fine.


Error when connect database continuously

I have uploaded my application to Windows Azure Now I want to store My Local database to Windows Azure Database.

Please Suggest me how do I create table and backup the table there?



How to restore my Local database to Windows Azure Database?

When should i use Sql Azure and when should I use table Storage? 
I was thinking , use table storage for transaction processing scenarios e.g. debit credit accounts kind of  scenario and use Sql Azure when data will not be used for transactional purposes e.g reporting. 
What do you think? 

When should I use Sql Azure and when should I use table Storage?

How do I add or access an `app.config` file in Azure functions to add a database connection string? 

If you&#39;re not supposed to add an `app.config` and there is a better way to access an external database to execute the function please let me know best practices.  Thanks!

Azure Functions Database Connection String

I have a SQL Azure Database Server and I need to query between the Databases but can&#39;t figure out how to accomplish this. 

Here is the structure of my databases:

 - **Server.X**
  - **Database.A**
  - **Database.B**
  - **Database.C**

In **Database.A** I have a Stored Procedure that needs to retrieve data from **Database.B**.  Normally, I would reference the database like `SELECT * FROM [Database.B].[dbo].[MyTable]` but this does not appear to be allowed in SQL Azure. 

`Msg 40515, Level 15, State 1, Line 16
Reference to database and/or server name in &#39;Database.B.dbo.MyTable&#39; is not supported in this version of SQL Server.`

Is there a way to do this on the database end? 

In the final version Databases A &amp; C will both need data from Database B.

Can&#39;t query between databases in SQL Azure

I am creating a new read/write user on SQL Azure as follows:

    -- Connected to master
    create login [fred] with password = &#39;xxx&#39;;

    -- Connected to my DB
    create user [fred] from login fred;
    EXEC sp_addrolemember &#39;db_datareader&#39;, &#39;fred&#39;;
    EXEC sp_addrolemember &#39;db_datawriter&#39;, &#39;fred&#39;;

When I login using SSMS I get an error saying `Cannot open database &quot;master&quot; requested by the login. The login failed.`

What am I doing wrong or missing?

New user cannot login to SQL Azure

How can i rename the database in sql Azure? 

I have tried `Alter database old_name {MODIFY NAME = new_name}` but not worked.

Is this feature available in SQL Azure or not?

 


Rename SQL Azure database?

Apparently one must explicitly specify (whitelist) the IP addresses that will be allowed access to a SQL Azure DB. I want, though, to have N-gazillion* users that will access these tables for data specific to them.

* delusions of grandeur/megalomania

Will these need to first provide me with their IP address so that I can add it as a valid entry, or is there a programmatic way to do it, or some other workaround?

How can I allow unknown users to access my SQL (Azure) DB?

Is there a way to simply take a SQL backup .bak file, and using that as a restore point to generate a new database on Azure SQL Database?  

The wizard that I see generally recommended in this situation wants to create a custom file to use, but it pukes on the encrypted objects ( functions and views ) and I don&#39;t see another way to get it going.



Restoring SQL Server backup to Azure SQL Database

I have an ASP.NET MVC 5 project that works local and whenever I need to blow away the DB, I just open a new query on it, change the available database dropdown to master, then close the connection on my local db and run the query &quot;drop database [name]&quot;.  Then I build the project, go into the package manager console and run &quot;Update-Database&quot;. This seems to rebuild a fresh local database and runs the seed method in my configuration.cs file.

The problem is when I need to test things in a live environment so I can test API&#39;s and such better, I will do a deploy to an Azure Website and the accompanying Azure DB, which is nice and easy to do. I check off the &quot;Execute code first migrations&quot; in the publish wizard and most of the time it works and I can run and debug my live version. Sometimes I need to blow away that db and start from scratch again, but the only way I&#39;ve really found to do it is to go into the Azure portal, delete the database, and then re-create it with the same name. This takes some time for Azure to process, so this is a slow testing cycle.

**Is there a quick way to just drop/reset a Azure SQL DB to it&#39;s fresh, empty, virgin state and then re-publish with &quot;execute code first migrations&quot; to have it re-create the tables and re-seed the data?**

I&#39;ve seen some talk of creating an initial migration after I create the db, and then trying to use the Powershell to do some sort of roll-back to that initial state, but I haven&#39;t had luck getting it to work, and I want to delete all the data at the same time. Maybe I&#39;ve just got the wrong syntax or haven&#39;t found a good enough tutorial. While I can run a query on the Azure DB to &quot;drop database [x]&quot; it literally kills the SQL Azure DB instance as you&#39;d expect and you need to go back into the portal to recreate it. Sometimes that initial state is no good as the model has since been updated, so this may not be useful anyway.

I feel like there should be some easier quicker way to test changes on a live environment as there all these great tools and shortcuts provided by MS, but did they just drop the ball here for this phase of development or am I missing something?

How to drop all tables and reset an Azure SQL Database

I have setup Sever, database and Firewall Setting (Rule) In windows Azure. I Have added The IP In firewall setting which is populating under the Windows Azure Firewall Tab.

When I am trying connect with Database using Manage from Azure Platform I am getting this error message.


**Firewall check failed. Cannot open server &#39;XXXXXXXXX&#39; requested by the login. Client with IP address &#39;XXXXXXXXX&#39; is not allowed to access the server.  To enable access, use the SQL Azure Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range.  It may take up to five minutes for this change to take effect.**

And I am also Not able to connect via Sql Server Management Studio. Getting Same connection issue Error for My IP Address.

I am not using static IP but my IP haven&#39;t change during this process. I have tried lot Example but all are saying To add firewall rule That I had already done.

I have Added Firewall exception for port Number 1433.

But still this is not working Please let me know that what type of setting is still missing.

Thanks in advance.

Windows Azure Client with IP address &#39;XXX.XXX.XXX.XX&#39; is not allowed to access the server

I&#39;m just starting to learn Azure and I can&#39;t see too many scenarios where you would want to put something into an Azure storage table as opposed to SQL. I guess maybe I&#39;m used to working in environments where I need related data?

When is it better to put something into an Azure storage table and not in a table in a Azure SQL database?

Azure storage tables vs SQL

Is there any difference between the Web Edition and Business Edition of Azure SQL Database other than the maximum supported database sizes? I&#39;m assuming the naming has some significance but all of the information I find simply talks about the max db size. I want to know if there are any other differences such as SLA, replication, scalability, etc.

Any clues?


Azure SQL Database Web vs Business Edition

In mssql2005 when I want to get size of table in MBs, I use EXEC sp_spaceused &#39;table&#39;.

Is there any way to get space used by particular table in SQL Azure using some query or API?

All Azure Sql-Database Solutions on Gang of Coders

Total of 29 Azure Sql-Database Solutions

'STRING_SPLIT' is not a recognized built-in function name