15
I'm really glad you asked this question. I had this very same need for SQL Server jobs in a Visual Studio database project and job deployments along with a database.
I wanted the solution file to contain everything that is needed for the SQL database process I had implemented, all in one place, to Create this solution AND Deploy this solution to any ...
13
I'd guess the problem is your SQL Server login does not have it's "default database" properly set.
This will show you what your "default database" is set to:
SELECT sp.name
, sp.default_database_name
FROM sys.server_principals sp
WHERE sp.name = SUSER_SNAME();
You can either change the default database of your login, or you can specify the database in ...
12
After a fair amount of frustration, finally found the cause, and posting as an answer as it may help others....
Finding the reason for why SSDT thought the scheme was different I think is the first point of call. SQL Scheme compare is your friend here. (In VS under Tools => SQL => New Scheme Comparison...)
Firstly thanks to @jadarnel27, was very helpful so ...
9
I've managed to get it working:
I created a new database project master. In there I created a folder Server Object and a file LinkedServer.sql. In the SQL file i added the linked server:
GO
EXECUTE sp_addlinkedserver @server = N'LinkedServer', @srvproduct = N'sqlserver', @provider = N'SQLNCLI', @datasrc = N'LinkedServer.domain';
After adding the ...
8
That's not how it works. A .dsv (data source view) is generated by defining which tables/queries you want to use in your cube.
The flow is:
create one or more .ds data sources by defining how to connect to the source databases
create a dsv (data source view) by adding tables and named queries defining how to get data from your .ds
create dimensions and ...
7
You have to increase the query timeout allowed on the connection. Unfortunately there is no user-interface-exposed way to do this. You have to search the Windows Registry for the key "QueryTimeoutSeconds" and increase the value. I increased mine from 60 to 360 and that made my schema compare timeouts disappear in Visual Studio 2012 and Visual Studio 2013.
7
Be sure to go to the Project properties, not the solution properties. See image below.
6
Although Management Studio is based-upon Visual Studio, the two are different products and have different target audiences. You will not be able to use Visual Studio to do all of the tasks that you used to use Management Studio for. Even installing the data tools and creating a SQL Server project within Visual Studio will not give you the functionality ...
6
That is correct. Forward compatibility is guaranteed. Backwards compatibility does not exist*. Having 4 different BIDS/SSDT instances on a machine is a pain I've known all too well but there's not much you can do.
There are ways of constructing SSIS packages such that your solution can target whatever version of SQL Server will be hosting them but that's ...
5
Publish>Advanced Settings>Drop>"Drop objects in target but not in source"
https://stackoverflow.com/questions/1799802/dropping-a-table-with-visual-studio-2010-database-project
5
@RC = Return code. The boilerplate unit test code will assign the stored procedure return code value to this variable, where it can later be used in a test case assertion.
Assuming there's a subsequent SELECT @RC after the proc execution in the unit test code, the scalarValueCondition1 assertion in your screen image will verify the return code value is 1 in ...
4
Visual studio 2015 SSDT work with SQL Server 2016 and has a backward compatibility to SQL server 2014 and 2012 only.
Target version of SQL Server | Development environment for SSIS packages
2016 | SQL Server Data Tools for Visual Studio 2015
2014 | SQL Server Data Tools for Visual Studio 2015 or SQL Server Data Tools - Business Intelligence ...
4
I think the best option is to include the create job statement in a post-deployment script. Like the solution in this thread:
How to deploy a SQL Server Agent Job in a VS SQL project?
4
The only time you need to use Schema Generation is if you did top down development and have never generated the schema before. Now that you have previously generated the SQL schema, you can just script out all the objects in SSMS and provide that script along with the SSAS project to other developers.
Then the new developer would deploy the SQL scripts and ...
4
Unfortunately, SSIS variables can't hold NULL values. NULL is allowed in data flow's columns but not on variables, this is a limitation of the underlying COM objects used by SSIS.
You will have to work-around this by supplying a default or sentinel value on your query, and later on check on this value to correctly assign a NULL. You can change your query to ...
4
This is happening because you've added the column in the middle of the CREATE TABLE script. This causes a "table rebuild" to happen. I talked about this on my blog a few months ago: SSDT problems: Table Rebuilds
The section on column ordinality is the specific problem you have. Say you have a table like this:
CREATE TABLE [dbo].[Post]
(
[Id] INT ...
4
You're right, as far as I know, that there's nothing in the sqlpackage publish options that would change the recovery model. That property is set in the dacpac. If you have access to the source code of the dacpac - the SQL Server Data Tools (SSDT, .sqlproj) project - make sure that the "Recovery Model" setting is configured to "Simple" rather than "Full."
...
3
I think this is happening because you're qualifying the database name in the query.
So remove **DBName** form the select clause; Visual Studio would then accept it.
3
You don't need a special SQL Server license to use Visual Studio database projects. As mentioned in srutzky's answer the database projects are driven by SSDT which is a free download.
If you are willing to use a newer version of Visual Studio than 2012, and you want to use a database project within Visual Studio then you should consider Visual Studio 2015 ...
3
I do not have any clear explanation for this problem solution. However I used SID instead of service name in connection string and it worked.
Edit: A service name is more flexible than a SID would be. A database can dynamically register with a listener using one or more service names. In fact, more than one database can register with a listener using the ...
3
Advanced settings in connection has that ability
3
If I understand correctly what you are trying to do I don't think you should put your expression in the Directory property.
I think you need to put:
Your directory name (C:\NewExcelFile\) in as the Directory. If this never changes, it can be entered directly in the configuration window, instead of as an expression.
Your filename pattern (@[User::Name] + "*....
2
There are a couple of prerequisites to use the SCHEMABINDING option:
· You cannot use * in the SELECT clause in the query, you have to specify column names
· You have to use two-part naming convention when referring to objects (which is in general a good practice)
So, you need to use schema name always when referring to objects when you ...
2
Have you turned on TCP/IP access in the SQL configuration manager? It's off by default for security reasons and is the first thing you have to turn on for remote DB access.
You will also need to open port 1433 in the firewall and in the case of a non-default instance, port 1434 as well. For non-default instances, the SQL Server Browser service must also ...
2
I think you mean SSDT (SQL Server Data Tools) which handles Visual Studio Database projects, but can be run independently of Visual Studio.
Look into the documentation for SSDT. Start here:
Download SQL Server Data Tools (SSDT)
Also might want to look here:
SQL Server Data Tools Team Blog
2
That occurred because VS 2015 was built to support only SQL server 2016 and you probably deployed the package under SQL Server 2014 or lower(unsupported versions).
With the latest version of Data tools in VS 2015, you can chose the targeted SQL server version in the package to allow SSDT to chose the correct .NET libraries used in the script component.
...
2
According to the message shown in the Question, this is actually a warning ("warnung") and not an error. An error will stop the build process and prevent the project from being published to the server. Warnings merely show up in the "Error List" window, but don't prevent the build or publish processes from completing.
I created a Database project in Visual ...
2
from the SQL management studio, connect to Analysis services and go to Roles, under the roles, the user that has been removed from active directory will appear in question marks "????????????????" , remove him and reprocess the cube
2
There is actually no official guide (AFAIK) for which one you should install first but based on my personal experience here is what I recommend:
If you are just installing the management components of SQL Server, such as Management Studio and the client libraries then I recommend always installing Visual Studio first followed by the SQL Server components.
...
2
You have either installed an older version of SSDT/Visual Studio side by side with your 2015 visual studio or you are trying to open a data source view created in a newer version of SSDT/Visual Studio.
Some binary data stream is written in the .dsv file and includes the version information. The solution is to install a recent enough version of Visual Studio ...
Only top voted, non community-wiki answers of a minimum length are eligible