2018-10-22         Milo

Script to copy database to existing database

Using Azure Portal, I have copied existing production database to a new staging database.Now, I need to copy DB Schema and Data from Production to Staging periodically. I wand to avoid deleting the Staging database, because I want to keep settings like Pricing Tier, backup settings etc.How to I sync DB Schema and Data from one database to already another already existing database? You can consider using Azure Data Factory and automating its execution using Azure Automation. [XXX] I need to copy DB Schema and Data from Production to Staging periodicallyNo, you do not....

 sql-server                     2 answers                     78 view
 2018-10-22         Kim

what feature is missing in SQL Azure

SQL Azure seems pretty cool. Some things are not available in SQL Azure though. For example, you can't do switch database with a USE statement - which is pretty understandable.What other features are missing in SQL Azure? SQL Server Feature Limitations (SQL Azure Database)Guidelines and Limitations (SQL Azure Database) [XXX]Here's a decent round-up of a feature comparison between SQL Azure and SQL Server. It's a big list of items - probably too big to enumerate here.http://social.technet.microsoft.com/wiki/contents/articles/compare-sql-server-with-sql-azure.aspxHere'...

 sql-server                     2 answers                     85 view
 2018-10-22         Maxine

What is XE_FILE_TARGET_TVF wait type in Azure SQL Database?

I'm experiencing periodical Azure SQL Database connection slow downs. As recommended in Wait statistics, or please tell me where it hurts article I ran sys.dm_db_wait_stats (analogue of sys.dm_os_wait_stats for Azure SQL Database) aggregation script which show me that longest waits are of type XE_FILE_TARGET_TVF. Average wait time is 54 seconds. XE_FILE_TARGET_TVF didn't mentioned in documentation as in any other online resources that I know. I suspect that "XE" means "Extended Events", "TVF" is "table-valued functions", "FILE_TARGET" is probably indicator that something is...

 sql-server                     2 answers                     100 view
 2018-10-22         Faithe

Alter table causing SQL Azure database unavailable

I have to perform the exchange of two tables and it's making my system unavailable for 20 minutes(are many data).My sample code:query = "DROP TABLE MY_TABLE; ";query += "EXEC sp_rename MY_TABLE_TEMP, MY_TABLE; ";query += "ALTER TABLE MY_TABLE ADD CONSTRAINT PK_MY_TABLE PRIMARY KEY CLUSTERED (ID ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]";Executed the above statements on a SQL Azure database, and this caused the database unavailable for 20 minutes. Any insights in this? Is your DB in the US East? T...

 sql-server                     2 answers                     103 view
 2018-10-22         Yvonne

I am unable to create an Azure external table with the DATA_SOURCE option

I am trying to do cross database queries and have setup an external data source via:CREATE EXTERNAL DATA SOURCE ExampleDataSourceWITH ( TYPE = RDBMS, LOCATION = 'example.database.windows.net', DATABASE_NAME = 'Database2', CREDENTIAL = "credentials");After that, I created an elastic database pool and added my databases to it:From what I've been reading, that's all I should have to do. But it isn't working. Here's the external table on trying to create on the first database:CREATE EXTERNAL TABLE [dbo].[tblEmployee]( [EmployeeId] [UNIQUEIDENTIFIER] NOT NULL, ...

 sql-server                     1 answers                     7 view
 2018-10-22         Ruth

Why doesn't Azure SQL DW support Error_Line() function?

Azure SQL DW supports all error handing function such as Error_Procedure(), Error_Message() except Error_Line(). Why? Is there any other alternative? The code you submit for execution against SQLDW is not the same as the code that is ultimately executed. This is a good thing! SQLDW uses internal representations for database objects so that renames etc are only simple metadata changes at the control node. However, this does mean that errors generated by the system do not line up, line for line with the code you executed. Therefore the ERROR_LINE() does not mean anything i...

 sql-server                     1 answers                     8 view
 2018-10-22         Alexia

sql azure setting value to null increases table size

I had an uniqueidentifier field in SQL Server (SQL Azure to be precise) that I wanted to get rid of. Initially, when I ran the code as mentioned in SQL Azure table size to determine the size of the table it was about 0.19 MB.As a first step I set all values in the uniqueidentifier field to null. There are no constraints/indexes that use the column. Now, when I ran the code to determine the table sizes the table had increased in size to about 0.23 MB. There are no records being added to a table (its in a staging environment). I proceeded to delete the column and it still hov...

 sql-server                     2 answers                     10 view
 2018-10-22         Marlon

How do I identify a deadlock in SQL Azure?

I have a Windows Azure role that consists of two instances. Once in a while a transaction will fail with an SqlException with the following text Transaction (Process ID N) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.Now I've Googled for a while and read this post about identifying deadlocks using SQL Server logs.The problem is...How do I do it in SQL Azure? What tools do I use to access the internals of SQL Azure and getting enough data? Monitoring of SQL Azure is more limited than SQL Server, b...

 sql-server                     3 answers                     13 view
 2018-10-22         Nina

Emitting application event like objects from SQL Server with Service Broker in nodejs

I want to do something like OracleChangeDatabaseNotitications or PostgreSQL LISTEN/NOTIFY with SQL Server - that is, I want to emit events asynchronously over a connection to the database.It seems possible to do this from .NET code using ServiceBroker and SQLNotificationServices but I can't find if this is possible to do outside of .NET.So I tried throwing some JS together.I used this to setup the ServiceBroker environment.I can send an event like this:DECLARE @handle uniqueidentifierBEGIN DIALOG CONVERSATION @handleFROM SERVICE MyServiceTO SERVICE 'MyRecvService'ON CONTRAC...

 sql-server                     1 answers                     17 view
 2018-10-22         Owen

SQL Server Service Broker - certificate on endpoint is sufficient?

I have databases on two separate instances(not in domain) and i want to use service broker. Is it sufficient to use certificate only on endpoint, some tutorials show using certificate when creating service for example: https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/bb839497(v%3dsql.105) or do i need to use on both endpoint and service? Assuming network connectivity between the two instances, using a certificate as the shared secret for them to identify and authenticate to each other should be sufficient.The other place where certificates are us...

 sql-server                     1 answers                     17 view
 2018-10-22         Liz

EXECUTE sp_executesql not working in SQL Server

I'm trying to implement a mechanism that would perform calculations based on dynamically defined algorithms. The way I do it is:Build a string containing the definition of all the variables along with their values,Fetch (from a table) the calculation formula,Invoke sp_executesql.Here is the contents of the string passed as the calculation (contents of variable @_l_Execute_Formula):DECLARE @_1 FLOAT = 678;DECLARE @_2 FLOAT = NULL;DECLARE @_3 FLOAT = NULL;SET @_l_Result = @_1 + @_2 + @_3and the invocation is:EXECUTE sp_executesql @_l_Execute_Formula , ...

 sql-server                     1 answers                     38 view
 2018-10-22         Eli

Automated Azure SQL DB export fails

Like the posting I reference below, we have also experienced failure in our automated Azure SQL DB backup. The automated backup has been running for months, but failed this weekend. In our case, this is for a Database running V2. Note that while the entry for AutoExportToStorageAccount succeeded, there is no .bacpac file to be found.Has anybody else seen this?Automated Azure SQL DB export fails - indexing for document type .xml is not supported on MS Azure SQL DB v12 Try logging into the older portal (https://manage.windowsazure.com) and running the backup/export manuall...

 sql-server                     1 answers                     42 view
 2018-10-22         Daisy

Sort Column names alphabetically in a table

In SQL Server, we get the table description using below command:Sp_help TableNameWhen it displays all column names in a random order. Is there a way If I want to get all column names alphabetically sorted in some order (Descending or Ascending)?This will help me to have a quick look at the table to see what all columns are present and whether a specific column is present in the table or not. You may get the List of Column from the System View which is INFORMATION_SCHEMA.COLUMNS. You can do a Select on the View and Filter by Table there order the List based on any of yo...

 sql-server                     3 answers                     55 view
 2018-10-22         Dinah

File and folder permissions after changing sqlserver service account

I read this article and concluded that the file system permissions needs to be manually set when I change the service account for my SQL server instance. So I changed the service account for my SQL server instance to a brand new local account via SQL Server Configuration Manager and got surprised that the SQL server service can start, even though I haven't set any file or folder permissions for the new account yet. So the service now runs as the local user, but that user does not have access to the default SQL data folder when I check from Windows. I can also read and write...

 sql-server                     2 answers                     30 view
 2018-10-22         Drew

How to get column with identity specifcation on for view created in SQL Server?

I have created a view where one of my column is primary key.In that field identity specifcation is also set to yes.when i open the view it doesnot contains primary key field.When i set the identity specification to No then only my view contains the primary key field.What is the reason?Are there any sql queries to set is identity to No?My view : create view userAccounts as select user_id ,br_id,user_name,role_id, ,email ,created_dt ,updated_dt ,status from user_accountsHere user_id doesnot appear in my view. I can't replicate the behaviour the OP is describing. In the S...

 sql-server                     1 answers                     34 view
 2018-10-22         Perry

Two databases with same tables and column names, How to pull out one column with similar data while all the other columns showing the differences?

I have 2 databases named 'OK' and 'TX' with same table named 'E_Model' and columns like: 'Product_ID' , 'Description', 'Warranty' and 'Category'I want to have the list of Product ID's which both the tables have and want to know the difference in all the other column at the same time. Desired result should be like:**'*Database' 'Product_ID' , 'Description', 'Warranty' 'Category'***OK LB9456 COFFEE 436 NULL 10 TX LB9456 TOASTER 956 1 12OK QR3300 APPLE 31 3 15TX ...

 sql-server                     1 answers                     106 view

Page 1 of 762  |  Show More Pages:  Top Prev Next Last