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
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 ...
10
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
A 2008 and 2008R2 user database is schema compatible, hence one version for both in SSDT. In other words, there aren't any schema objects that you could add to a 2008R2 SSDT model that couldn't be created in 2008.
This isn't the same as either database version or database compatibility level.
Database version:
The database version is a number stamped in ...
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 ...
6
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.
6
Be sure to go to the Project properties, not the solution properties. See image below.
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
The answer is to add sys. in front of the hierarchyId:
sys.hierarchyid::GetRoot()
4
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
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 ...
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
On line 9, instead of GetType(Byte) you need GetType(Byte()).
Encoding.GetString only takes a byte array; never a single byte, so regardless of the type provided by the framework, what's there is logically not going to work.
If there is another field in the table that comes back as a Byte, you'll need to add another branch to the If statement to handle the ...
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
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] + "*....
3
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."
...
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
I found a Schema Compare utility in Visual Studio. It is included with the premium, ultimate, and perhaps other editions of Visual Studio.
To use it:
Open Visual Studio
Click "Data" on the menu bar
Click "Schema Compare"
This utility is awesome. It is like WinMerge or BeyondCompare but optimized for SQL Server database objects. This should definitely be ...
2
You can purchase SQL Compare from Red-Gate.
2
It is not just the compatibility level. Also the .NET options themselves are the same against both 2008 and 2008 R2.
In a word:
Is there not a SQL Server 2008 R2 specific option?
No
Is this because SQL 2008 R2 shares the same compatibility
level of 100 with SQL Server 2008?
Not exactly, but it doesn't really matter.
answered Feb 4 '13 at 14:51
Jonesome Reinstate Monica
2,9486 gold badges28 silver badges51 bronze badges
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
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 ...
Only top voted, non community-wiki answers of a minimum length are eligible
