What is a good approaching for tracking incremental changes to database tables, stored procedures, etc for Azure SQL Data Warehouse? I am in the process of moving a large database over to Azure SQL Data Warehouse. The prior approach for change tracking was using a 'Database Project' in Visual Studio 2015. This allows easy source control integration with TFS or Git or whatever. When you want to publish, you just target the destination database and it generates a change script. This functionality does not at all work for Azure SQL Data Warehouse. Visual Studio (and the latest SSDT) simply can't target SQL DW. This means the process of publishing is extremely tedious, entirely manual and extremely error prone. Is there another comparable approach you are using for this type of project?

At work we have an old server and we're moving everything from there to our new server. We're a very small team, and one of my team-members says without visual studio 2015 in the new server he can not transfer/create SSIS packages there. Any thoughts on this?

In SQL Server Data Tools (SSDT) after compared the schema, I want to update few changes of mine only (as red-circled in the screenshot). But there are many other changes are listed in the window, I need to manually uncheck all the other items. Is there any option to uncheck all or invert selection the items?

We retrieved source code from a SQL Server 2016 database and made a Visual Studio 2015 SSDT (SQL Server Data Tool) project. The source code contains a file Security\foglight5.sql with the below content:

CREATE USER [foglight5] FOR LOGIN [foglight5];

We want to deploy the source code on a testing instance of SQL Server, so that we have a development environment with the same DDL as the server. However, when trying to publish the source code to a testing instance, Visual Studio requires building first. And, when building the project, we got an error saying Error SQL71501: User: [foglight5] has an unresolved reference to Login [foglight5].. **Our Question:** How to resolve the above error and proceed with our testing deployment?

I need to remove the below auto-generated deployment script for my SQL project. I am using VS 2015 with SSDT 14.06. Would this be something that can be set in the Advanced Deployment settings under the Drop tab ? This is for an existing Database deployment. IF (DB_ID(N'$(DatabaseName)') IS NOT NULL) BEGIN ALTER DATABASE [$(DatabaseName)] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE [$(DatabaseName)]; END GO PRINT N'Creating $(DatabaseName)...' GO CREATE DATABASE [$(DatabaseName)] ON PRIMARY(NAME = [$(DatabaseName)], FILENAME = N'$(DefaultDataPath)$(DefaultFilePrefix)_Primary.mdf') LOG ON (NAME = [$(DatabaseName)_log], FILENAME = N'$(DefaultLogPath)$(DefaultFilePrefix)_Primary.ldf') COLLATE SQL_Latin1_General_CP1_CI_AS GO USE [$(DatabaseName)]; I have the following items all **unchecked** for my deployment options in the properties: -- Deploy database properties -- Always re-create database -- Block incremental deployment if data loss might occur -- DROP objects in target but not in project -- Do not use ALTER ASSEMBLY statements to update CLR types

I'm writing a program for my client in C# Winform. And the client gave this excel sheet. **Process** I have a table that used to calculate BULK amount of alcohol by "DIP" and "SLIDE". In excel I need to select the specific DIP and SLIDE to get the BULK. I need to do the same thing in the database. I exported a table from excel to MySQL table. I can get the BULK amount by this query SELECT SLIDE 0.8 FROM tbl_vat_01_alcohol_esa WHERE DIP = 212; **Requirement** I need to get the DIP and/or SLIDE when I enter BULK amount. I wanna know how to do it? or Is it possible to do that kind of work in this table? or do I need to change the table structure or something. OR How to access the excel sheet from C# winform. I mean, If I make a form that contain 3 textbox for DIP, SLIDE and BULK. When I enter DIP and SLIDE I get BULK and when I enter BULK I get DIP and SLIDE... Is it possible? If it is, How to achieve that? OR Is there any other method. I'm new to StackExchange and Database!

I have to develop an ETL to extract some data from an Oracle DB 11g to a SQL Server 2017 DB. I am pretty new at all this technology so I would like some advices. The strongest problem so far is to understand what I exactly need to perform this, as the more I read, the more confused I feel about all the tools I am supposed to install. Here what I have so far: - The SQL Server 2017 DB is on a server. I can access the DB through Microsoft SQL Manager. Also, I have access to the server itself through remote connection but no permission to install whatever I want. However, I guess I could ask for any installation if it was mandatory. - The Oracle DB is on another server. I do not have access to the server itself. I have access to the DB through SQL Developer Tool. I access to the DB using EZ Connect, not a TNS. - On my PC, I have the following tools installed: - Visual Studio 2015 - Visual Studio extension Microsoft SQL data tool, in order to develop the SSIS - Visual Studio extension Oracle Developer Tools in order to connect to the Oracle DB through Visual Studio I can already develop a SSIS and transfer some data from my PC to the SQL server DB. However, I am not able to connect to the Oracle DB through the SSIS. I am pretty sure there are plenty of details that I am missing so I would be grateful to whoever that would give me any advice.

Every time something fails the publish, assuming it passes the preview generation , the error line and executed script sample are completely useless at helping me trace the source of the error, since the line number is relative to the last GO statement, and the executed script doesn't always appear in the publish script. In this instance, it fails with this error: The executed script doesn't exist in the publish script: And this is where it navigates in the publish script upon double clicking the error message: Usually I am able to trace it down based on the error information, but this time I'm at a loss because the only relevant keywords I can think of are so common that it seems hopeless. It looks like logging is hackish and the errors sometime come before the postdeployment scripting (e.g. table triggers executing on incomplete data since publish settings only include an option to disable DDL triggers ). I've tried running SQL Server Profiler during the publish but that tool omits even more crucial debugging information (and gets overwhelmed by my large database project). Perhaps another reason to upgrade to get the Extended Events Profiler? (I verified this statement was not the culprit)

Is there any reason to save the schema compare file once you are done comparing, creating scripts or just simply updating?

Has anyone come across Class Not Registered issue while opening Data Source Views in SSAS using Visual Studio 2015? There are similar posts not haven't found anything exactly the same. Error text: > Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)) (msddsp)

SQL Server 2016, Enterprise Edition in Windows Is there anyway to display the execution plan of a stored procedure created in C# with Visual Studio Data Tools? This is a question that someone with Visual Studio experience probably knows the answer to. My searches on Google haven't turned up anything useful. Here is the stored procedure in c# using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] static public void Addition (SqlInt32 a1, SqlInt32 a2, out SqlInt32 sum) { sum = a1 + a2; } } Here is the execution in SSMS 2017 v17.3 USE CLR1; declare @sum int exec dbo.Addition 3, 4, @sum output select @sum I have also been able to find the execution plan in cache. Even though there is a plan_handle available in dm_exec_procedure_stats, I can't seem to use a cross apply to [dm_exec_query_plan] to view it.

I am trying to figure out a way to have all of our random SQL scripts that we write under source control. I am already using SSDT for the database project. This is not for these sort of scripts. It is for all the other random queries that we run over time. Before we put them in a stored procedure. Or the other scripts that just don't really fit as a SP. How can I bring my non SSDT Sql scripts under source control using Visual Studio?

I want to debug in TSQL.When I start the debug the following message box popup. In SSMS v17.2 Then I see this message: In Microsoft Visual Studio 2015 Server Explorer same Error: Then I see this message: Thank You!

I recently installed visual studio 2015 and there is a really simple action that I can not make, When I rerun (Ctr+Shift+F5) SSIS package (re-run, not run), it causes visual studio to throw this error: I do not know if this is related, but the SSIS project is on the sql server 2014. This is a routine operation that worked without problems at visual studio 2013.

I don't think I can post bugs on Microsoft connect. I'm trying to use SSDT on VS community edition. And the UI is terribly buggy. Here are some screenshots: The Enumerator properties manager is just painful. I can drag it down a tiny bit but can still barely read one line at a time. And this image is another example. What tools do most people use to develop SSIS in? I can't believe that it's the same ones I'm using... I'm using a surface pro 4 that is up-to-date as far as I know

I have a request to send emails the first of every month for each department that hasn't submitted their hours. I have a query that generates a list of email addresses, the email subject, and the email body. However, in the Data Flow SSIS Toolbox there is not a "Send Email Task" option like there is in the Control Flow. How can I send emails based off of my query results?

I am experiencing an issue, which is beyond my understanding in trying to figure out, I am not sure if it is some glitch with Visual Studio, or something wrong with my SQL Server DB. I originally thought I just broke my code in an WebAPI I was creating, hence my question on [SO](https://stackoverflow.com/questions/42989855/asp-net-database-query-exception-for-one-table-even-though-another-works) . But on further evaluation it could be something more. The issue is even occurs with creating a new ASP.Net Core WebAPI with Individual Authentication from the template, only adding two classes, and scaffolding out the controllers and views, I am only able to query one table while being able to save to all tables. This with the fact my Model class data is from one database and the AspNetUser tables are on another database while all on the same SQL Server server. For reference anytime Index/Detail/Edit is called to controllers for my Order model class it gives a "NullReferenceException: Object reference not set to an instance of an object." in the browser from await _context.Order.ToListAsync()); And yet I can type /Order/Create to get to the Create view for Order and have it update the DB. While my Company model class allows for lookups, updates, and inserts to the DB; both of which run off the same DBContext connection. This issue appeared after another issue I had and put on hold to figure out, and that other issue I noticed was when I "registered" a new user everything worked fine but when I would logoff and try to log back in it always failed. I thought maybe it had to do with an incorrect size on the nvarchar on a field; but I have lookup the newest setups and seem to be fine there, as I was thinking I was maybe truncating some value. The one reason I didn't correlate these two issues together is because the first one with User login didn't throw any error code in the browser (but of course Identity.EntityFramework probably already has some behind the scene error checks) to take care of that. But since the User cannot log back in after a register and logoff, that is telling me that a query is failing even though its from a separate connection. And this fails no matter how many times I try to create a new program. Update: I changed my connectionString to include MARS > "Data Source={ServerName}\\{InstanceName}; Initial Catalog={DatabaseName}; Integrated Security=True; Connect Timeout=15; MultipleActiveResultSets=true;" for both my userLogin database and my appDatabase. This allowed me to log back in for the user. It did not however let me query the Order table. I therefore tried a new class, State (which as Name/Code of all US states), I then scaffolded out the controller and views. The query works now for both Company and State but not Order. So I thought it had to do with the virtual keyword in the DataMineDbContext for the DBSets, as State did not scaffold that in. But that did not fix my issue. I therefore though it was a naming convention thing and renamed Order to Purchase both in the app and in my Database. This has not helped. From comment by Mr. BrownStone the information obtained from _context in the Orders (now Purchases) Controller > HResult: -2147467261 > > InsideExpression: _COMPlusExceptionCode: -532462766 s_EDILock: > {object} > > Messge: "Object reference not set to an instance of an object." > > Source: "Anonymously Hosted DynamicMethods Assembly" > > StackTrace: "at lambda_method(Closure , ValueBuffer )\r\n at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.UnbufferedEntityShaper\1.Shape(QueryContext queryContext, ValueBuffer valueBuffer)\r\n at Microsoft.EntityFrameworkCore.Query.QueryMethodProvider._ShapedQuery>d__\1.MoveNext()\r\n at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider._TrackEntities>d__15\2.MoveNext()\r\n at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor\1.EnumeratorExceptionInterceptor.MoveNext()\r\n at System.Collections.Generic.EnumerableHelpers.ToArray[T](IEnumerable\1 source, Int32& length)\r\n at System.Collections.Generic.EnumerableHelpers.ToArray[T](IEnumerable\1 source)\r\n at System.Linq.Enumerable.ToArray[TSource](IEnumerable\1 source)\r\n at System.Linq.SystemCore_EnumerableDebugView\1.get_Items()"

I'm using Visual Studio 2015. In Explore Server, I create a new DB.mdf file. When I right click on "Tables" I don't have the option to create a new table. Do I need to install something in order to create MDF files' tables?

We are using sqlpackage.exe to deploy scripts onto a SQL Server 2016 SP1 instance. Running sqlpackage.exe executes this SELECT SELECT * FROM ( SELECT SCHEMA_NAME([o].[schema_id]) AS [SchemaName], [si].[object_id] AS [ColumnSourceId], [o].[name] AS [ColumnSourceName], [o].[type] AS [ColumnSourceType], [ic].[column_id] AS [ColumnId], [c].[name] AS [ColumnName], [si].[index_id] AS [IndexId], [si].[name] AS [IndexName], [ds].[type] AS [DataspaceType], [ds].[data_space_id] AS [DataspaceId], [ds].[name] AS [DataspaceName], [si].[fill_factor] AS [FillFactor], [si].[is_padded] AS [IsPadded], [si].[is_disabled] AS [IsDisabled], [si].[allow_page_locks] AS [DoAllowPageLocks], [si].[allow_row_locks] AS [DoAllowRowLocks], [sit].[cells_per_object] AS [CellsPerObject], [sit].[bounding_box_xmin] AS [XMin], [sit].[bounding_box_xmax] AS [XMax], [sit].[bounding_box_ymin] AS [YMin], [sit].[bounding_box_ymax] AS [YMax], [sit].[level_1_grid] AS [Level1Grid], [sit].[level_2_grid] AS [Level2Grid], [sit].[level_3_grid] AS [Level3Grid], [sit].[level_4_grid] AS [Level4Grid], [sit].[tessellation_scheme] AS [TessellationScheme], [s].[no_recompute] AS [NoRecomputeStatistics], [p].[data_compression] AS [DataCompressionId], CONVERT(bit, CASE WHEN [ti].[data_space_id] = [ds].[data_space_id] THEN 1 ELSE 0 END) AS [EqualsParentDataSpace] FROM [sys].[spatial_indexes] AS [si] WITH (NOLOCK) INNER JOIN [sys].[objects] AS [o] WITH (NOLOCK) ON [si].[object_id] = [o].[object_id] INNER JOIN [sys].[spatial_index_tessellations] [sit] WITH (NOLOCK) ON [si].[object_id] = [sit].[object_id] AND [si].[index_id] = [sit].[index_id] INNER JOIN [sys].[data_spaces] AS [ds] WITH (NOLOCK) ON [ds].[data_space_id] = [si].[data_space_id] INNER JOIN [sys].[index_columns] AS [ic] WITH (NOLOCK) ON [si].[object_id] = [ic].[object_id] AND [si].[index_id] = [ic].[index_id] INNER JOIN [sys].[columns] AS [c] WITH (NOLOCK) ON [si].[object_id] = [c].[object_id] AND [ic].[column_id] = [c].[column_id] INNER JOIN [sys].[objects] AS [o2] WITH (NOLOCK) ON [o2].[parent_object_id] = [si].[object_id] INNER JOIN [sys].[stats] AS [s] WITH (NOLOCK) ON [o2].[object_id] = [s].[object_id] AND [s].[name] = [si].[name] INNER JOIN [sys].[partitions] AS [p] WITH (NOLOCK) ON [p].[object_id] = [o2].[object_id] AND [p].[partition_number] = 1 LEFT JOIN [sys].[indexes] AS [ti] WITH (NOLOCK) ON [o].[object_id] = [ti].[object_id] LEFT JOIN [sys].[tables] AS [t] WITH (NOLOCK) ON [t].[object_id] = [si].[object_id] WHERE [si].[is_hypothetical] = 0 AND [ti].[index_id] < 2 AND OBJECTPROPERTY([o].[object_id], N'IsSystemTable') = 0 AND ([t].[is_filetable] = 0 OR [t].[is_filetable] IS NULL) AND ([o].[is_ms_shipped] = 0 AND NOT EXISTS (SELECT * FROM [sys].[extended_properties] WHERE [major_id] = [o].[object_id] AND [minor_id] = 0 AND [class] = 1 AND [name] = N'microsoft_database_tools_support' )) ) AS [_results] In a previous question I was able to resolve this problem by running a UPDATE STATISTICS WITH FULLSCAN on the system tables of the target database. Since our SP1 upgrade though, updating stats is no longer resolving the problem. The above SELECT takes on average 3 minutes to execute. sqlpackage will only wait 30 seconds before timing out. Adding STATISTICS IO to the query, I get this output - Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. - Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. - Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. - Table 'sysobjvalues'. Scan count 19452, logical reads 86055648, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. - Table 'sysschobjs'. Scan count 1, logical reads 155, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Notice the extremely high logical reads value on sysobjvalues. Now if I turn on the old cardinality estimator with **OPTION(QUERYTRACEON 9481)**, I get this output. - Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. - Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. - Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. - Table 'sysidxstats'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. - Table 'sysschobjs'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. - Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. - Table 'sysiscols'. Scan count 1, logical reads 29, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Since I don't have access to the sqlpackage code, I can't add this option to it. I can't turn on the legacy cardinality estimator for the entire database because that will cause issues elsewhere. - What other options are there? - Plan Guide? - Can an option be added to sqlpackage to force it to use querytraceon 9481? Thanks, Craig

I'm building an ETL process in SSIS that extracts data from a 4D data source using the manufacturer's own ODBC provider (4D v16 ODBC Driver). When I try to extract any type of "string" column, it comes out as a LOB column, meaning that 'Row by Row' fetch method is enforced because the table has LOB column(s). My go-to solution would be to CAST the offending LOB columns in the source SQL statement to a fixed-length type, but I can't seem to figure out the correct syntax or data type name in 4D. I think the following error message points to a syntax error from 4D: SELECT x, y, CAST(z AS varchar(100)) AS z FROM someTbl Documentation on the database product is a bit thin, but it would seem that there is no such thing as fixed-length alphanumeric datatypes, but rather just a "string" datatype, which the ODBC provider perhaps translates to LOB. Is there a way to convert the database column to an alphanumeric datatype that SSIS will see as a fixed-length column?