dcsimg
Results 1 to 40 of 40

Thread: Publishing a database project from Visual Studio 2017

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Dec 2015
    Location
    Hastings, UK
    Posts
    137

    Publishing a database project from Visual Studio 2017

    I am trying to understand how the Publisher works, so that I can understand whats going wrong.

    The project published OK and I installed on another PC, it installed the SQL server and my project without any errors, but when I tried to use the project and use the data I get an exception:
    System.Data.SqlClient.SqlException (0x80131904): The target principal name is incorrect. Cannot generate SSPI context.

    My first thought is that it is to do with the Data source, as the project had my datasource details.

    I need to understand how a project of this type works and how the database that I used in the Visual Studio is copied and used in the published project.

    Out of curiousity I loaded MSSMS on the recipient computer, I could not detect a database, even though SQL LocalDB 2012 was loaded.

    Can one of you experts help me understand and fix this problem, as I have spent 100's of hours creating this project and I am failing at the last hardle.

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    104,705

    Re: Publishing a database project from Visual Studio 2017

    What's your connection string?

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Dec 2015
    Location
    Hastings, UK
    Posts
    137

    Re: Publishing a database project from Visual Studio 2017

    Here is the datasource string from Visual Studio. I have X out the serial number for security reasons.
    Data Source=DESKTOP-XXXXXXX\SQLEXPRESS;Initial Catalog=Verses_Find;Integrated Security=True

    Thanks!

  4. #4

    Thread Starter
    Addicted Member
    Join Date
    Dec 2015
    Location
    Hastings, UK
    Posts
    137

    Re: Publishing a database project from Visual Studio 2017

    Here is the datasource string from Visual Studio. I have X out the serial number for security reasons.
    Data Source=DESKTOP-XXXXXXX\SQLEXPRESS;Initial Catalog=Verses_Find;Integrated Security=True

    Thanks!

  5. #5
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    104,705

    Re: Publishing a database project from Visual Studio 2017

    Is that the name of the machine you're running on and does it have a SQL Server instance named SQLEXPRESS?

  6. #6

    Thread Starter
    Addicted Member
    Join Date
    Dec 2015
    Location
    Hastings, UK
    Posts
    137

    Re: Publishing a database project from Visual Studio 2017

    I asume you mean the recipient computer - the answer is no!
    Should this have been installed by the setup program when I installed my published project?
    Is there something I missed setting in the Publisher Properties setup?

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    Dec 2015
    Location
    Hastings, UK
    Posts
    137

    Re: Publishing a database project from Visual Studio 2017

    I have been doing some research and most of the sites talk about right clicking on your project in Solution Explorer and selecting Publish from the Pop-Up menu, then Advance Publish Settings, check the 'Generate smart defaults', but there is no Advance Publish Settings? (extract from web page)
    1. Right click the project name in the “Solution Explorer” window and select “Publish” from the pop-up menu. ...
    2. On the Advanced Publish Settings window, check the “Generate smart defaults, when applicable” checkbox.
    3. Then click the “Publish” button.
    But all that happens on my computer is that it goes into the Publish Wizard?
    Also most expect you to publish to the web, but I want to publish to a PC using a DVD?

    Does this info help?
    Last edited by Rocky48; Jul 29th, 2019 at 12:58 PM.

  8. #8
    Fanatic Member
    Join Date
    Nov 2017
    Posts
    1,003

    Re: Publishing a database project from Visual Studio 2017

    Do you recognize the workstation name of DESKTOP-XXXXXXX? Is that the name of the PC that you write your VB.NET code on?

  9. #9
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    104,705

    Re: Publishing a database project from Visual Studio 2017

    No matter how the application gets to the other machine, once there, your connection string tells it how to connect to its database, so that connection string has to make sense in that context. The server and instance names have to represent a server and instance that exist in that context. There's no point having the name of your development machine in the connection string when the application is running on some other machine on some other network, and there's no point referring to an instance that doesn't exist as far as that machine is concerned. It doesn't matter how the instance gets there - it can be installed by your app installer or not - but it has to exist. SQL Server instances don;t appear by magic and they are not a default part of Windows.

    I don't have VS installed here to check exactly what is available when publishing in VS 2017 but I'm sure that you can specify prerequisites on the Publish page of the project properties. This is an example of why EVERYONE should explore VS, just to see what's there. You'll find things that may not be important at the time but will become important later. Everyone should explore the IDE options and the project properties.

  10. #10
    Fanatic Member
    Join Date
    Feb 2003
    Posts
    748

    Re: Publishing a database project from Visual Studio 2017

    Quote Originally Posted by jmcilhinney View Post
    Is that the name of the machine you're running on and does it have a SQL Server instance named SQLEXPRESS?
    I have noticed that when I use SQL Express I sometimes need to go to the Services Control Panel app to start the "SQL Server (SQLEXPRESS)" service manually.

  11. #11

    Thread Starter
    Addicted Member
    Join Date
    Dec 2015
    Location
    Hastings, UK
    Posts
    137

    Re: Publishing a database project from Visual Studio 2017

    I do get annoyed when I certain person tells me how stupid I am and that I must search the galaxy of commands on VS.
    Just a pointer as to where one should look would be useful?
    As you can see from post #7 I have tried to find the method to find the Advance settings, but it apears to be missing on my PC.
    Optionbase:
    As to the Workstation name, yes that is the workstation I developed the project on.

  12. #12
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    104,705

    Re: Publishing a database project from Visual Studio 2017

    Quote Originally Posted by Rocky48 View Post
    Just a pointer as to where one should look would be useful?
    Gee, if only I had said:
    Quote Originally Posted by jmcilhinney View Post
    I'm sure that you can specify prerequisites on the Publish page of the project properties.
    I'm done with this. If that's a "galaxy" then I have better places to waste my time.

  13. #13

    Thread Starter
    Addicted Member
    Join Date
    Dec 2015
    Location
    Hastings, UK
    Posts
    137

    Re: Publishing a database project from Visual Studio 2017

    If you'd asked I would have told you that I I have set the Prerequisites:
    Name:  Prerequisites.png
Views: 828
Size:  24.2 KB
    To answer #10:
    Name:  Services running.png
Views: 800
Size:  6.9 KB

  14. #14
    Fanatic Member
    Join Date
    Feb 2014
    Location
    Norfolk UK (inbred)
    Posts
    512

    Re: Publishing a database project from Visual Studio 2017

    I don't think you are going the best way about getting help from those with knowledge here.

  15. #15

    Thread Starter
    Addicted Member
    Join Date
    Dec 2015
    Location
    Hastings, UK
    Posts
    137

    Re: Publishing a database project from Visual Studio 2017

    I'm sorry, but I get frustrated when I only get half an answer. I am not that stupid that I would not have set the prerequisites.
    The PC that I am installing a copy on has installed SQL 2012 local DB, but not the database file.
    I posts #7 & #11 I mentioned about advanced settings, but nobody has mentioned as to why I do not have that on my PC.
    I have a suspicion that the answer may be there, but I am only an aging novice!

  16. #16
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,610

    Re: Publishing a database project from Visual Studio 2017

    Why wouldn't you be putting your connection string in a settings file (app.config or something like that) so its not hard coded and you can set it per machine ?
    Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you



  17. #17
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    104,705

    Re: Publishing a database project from Visual Studio 2017

    Quote Originally Posted by Rocky48 View Post
    I am not that stupid that I would not have set the prerequisites.
    We have no idea how stupid you are and you apparently have no idea how stupid some of the people who ask questions on the internet actually are. We only know what you tell us so if you just provide all the relevant information, there can be no confusion. If I tell you where to set the prerequisites and you've already set the prerequisites, I would suggest that a more appropriate response than complaining about galaxies of options would be that you have already set the prerequisites. Maybe that's just me.

  18. #18

    Thread Starter
    Addicted Member
    Join Date
    Dec 2015
    Location
    Hastings, UK
    Posts
    137

    Re: Publishing a database project from Visual Studio 2017

    Could you explain a bit more?
    How do you create this file, in vs or a a program like Activestate Komodo 7.
    If this config file is going to multiple users, how do you write the connection string to suit any user?
    An example would be great!
    I'm really a novice especialy regarding publishing projects.
    I would really apreciate so help!

  19. #19

    Thread Starter
    Addicted Member
    Join Date
    Dec 2015
    Location
    Hastings, UK
    Posts
    137

    Re: Publishing a database project from Visual Studio 2017

    Duplicate previous post

  20. #20
    Frenzied Member jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    1,604

    Re: Publishing a database project from Visual Studio 2017


  21. #21

    Thread Starter
    Addicted Member
    Join Date
    Dec 2015
    Location
    Hastings, UK
    Posts
    137

    Re: Publishing a database project from Visual Studio 2017

    Thanks jdc2000!
    I notice that they are for SQL Server 2005 & 2008.
    Will I need to change much as I am going to use SQL Server 2012, as that is the only one available in Prerequisites.
    The project was written in VS 2017.

  22. #22

    Thread Starter
    Addicted Member
    Join Date
    Dec 2015
    Location
    Hastings, UK
    Posts
    137

    Re: Publishing a database project from Visual Studio 2017

    Am I missing something as several websites talk about “Advanced Publish Setting” window.
    According to the website I am looking at it should be shown after clicking the Publish item on the menu, see quote from web page:
    Right click the project name in the “Solution Explorer” window and select “Publish” from the pop-up menu. Configure the “Target Database Settings” as follows, then click on the Advanced button.
    I have Visual Studio Community Edition 2017 and Microsoft SQL server 2017.
    Am I not getting this because I have the community edition?

  23. #23
    Frenzied Member
    Join Date
    Jul 2011
    Location
    UK
    Posts
    1,282

    Re: Publishing a database project from Visual Studio 2017

    Quote Originally Posted by Rocky48 View Post
    Am I missing something as several websites talk about “Advanced Publish Setting” window.
    According to the website I am looking at it should be shown after clicking the Publish item on the menu, see quote from web page:

    I have Visual Studio Community Edition 2017 and Microsoft SQL server 2017.
    Am I not getting this because I have the community edition?
    Hey Rocky,

    I know nothing (John Snow) about data bases, but as you're not getting much help here I'll make a couple of observations:

    I think that what the websites are referring to is not a VB.NET WinForms Project, but an actual SQL Server DataBase Project (New Project/Installed/Other Languages/SQL/SQL Server DataBase Project). Publishing that type of project gives access to the Advanced button and the “Advanced Publish Setting” window. See here for example (Steps 11 onwards for Publish).

    I don't think you need to jump through all these hoops for your project though. As I understand it, your database is used for retrieving information only; you don't allow the user to update any information in it. So even if you push an update to your end users, you will not run the risk of overwriting any of their user data.

    You mentioned earlier that you want the end user to be using LocalDB, not the full SQL Server. As such, I think all you'd need to do would be to add the database file to your project so that it is included in the published "Application Files", and also alter the connection string that gets published.

    The connection string for LocalDB is quite different to the connection string you are using now, and does not need any specific information that identiffies the machine that LocalDB is running on. Maybe you should change your development app so that it also is using LocalDB instead of SQL Server. I suspect that doing this would make publishing much more straightforward.

  24. #24

    Thread Starter
    Addicted Member
    Join Date
    Dec 2015
    Location
    Hastings, UK
    Posts
    137

    Re: Publishing a database project from Visual Studio 2017

    Thanks John!
    Got LocalDB installed and was able to import database from the original one, but don't understand how the database is installed perminantly?
    Restarted PC and started LocalDB,but database is not there?
    I'm guessing that you use some command level file to start LocalDB as I did, but how do you add the database?
    Regarding the point about the use of the database- Yes users will be able to add verse to the dtabase!
    Last edited by Rocky48; Aug 6th, 2019 at 05:31 AM.

  25. #25
    Frenzied Member
    Join Date
    Jul 2011
    Location
    UK
    Posts
    1,282

    Re: Publishing a database project from Visual Studio 2017

    Quote Originally Posted by Rocky48 View Post
    Got LocalDB installed and was able to import database from the original one
    You clearly have a much greater knowledge of working with databases than I do. I wouldn't have a clue how to do that.

    Quote Originally Posted by Rocky48 View Post
    I'm guessing that you use some command level file to start LocalDB as I did, but how do you add the database
    I've never manually started LocalDB. All I've ever done is selected:
    'Microsoft SQL Server Database File'
    as the Data Source in the 'Add New Data Source' Wizard, and supplied (or browsed to) the name of the .mdf file.

    If you are trying to change your app from using SQL Server to LocalDB, I suspect that all you need to do is add the .mdf file to your project (right click the application in Solution Explorer and select 'Add.Existing item'). Possibly the .ldf file as well?? I don't really know what the purpose of that file is.

    Then you'd change the connection string in Application Settings (from the Project/Properties/Settings Tab) to:

    Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\Verses_Find.mdf;Integrated Security=True

    Chang Verses_Find.mdf to the actual name of your database file, obviously.

    If you've hard coded the connection string in your Form code then you'll have to change its value there as well.


    Now when you Publish, the list of 'Application Files' should include your database files; Verses_Find.mdf and Verses_Find_log.ldf as 'Data Files'.

    The use of |DataDirectory| in the connection string should ensure they are automatically installed in the correct folder on the end user machine.


    Quote Originally Posted by Rocky48 View Post
    Regarding the point about the use of the database- Yes users will be able to add verse to the dtabase!
    Then I have no idea what would happen if or when you supply an update to your app. It might be as simple as not supplying the db files in the update. I've never tried so I don't know.

  26. #26
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,795

    Re: Publishing a database project from Visual Studio 2017

    The prerequisite that was selected in post 13 was the wrong one... LocalDb is for when you use the AttachDBFileName method Inferred showed in post #25. That's why you're not seeing your database it's only attached when connections is active.

    What should have been selected is the option just above that. That would have installed SQLExpress proper. At that point, it should have been a simple matter of then attaching the mdf to the instance and it would be there permanently.

    Personally I don't like shipping mdf files for this very reason. I usually ship the SQL scripts instead. I let the installer setup SQLExpress, then connect to it, create the database, then switch to that database, then start creating the tables, indexes, views, etc. Once that's in place, then I fill it with the data. When it comes to updates, same thing, I ship differential scripts - ones that alter the existing tables, or what ever. Then I don't have to worry about losing data entered by the user.


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  27. #27

    Thread Starter
    Addicted Member
    Join Date
    Dec 2015
    Location
    Hastings, UK
    Posts
    137

    Re: Publishing a database project from Visual Studio 2017

    I don't know where anyone got the idea that the database was a mdf (Access) file. The files were setup and updated in SQL.
    The database is called Verses_Find and the tables dbo.tablename.
    Going back to the post Inferrd posted#25, I thought I should look at an SQL Database Project. I think I have a lot to learn! How do you use this type of project? Can you use this together with the Visual Basic Project that I have already written, as there is no Windows forms, so I'm guessing that you must get the VB project to connect to this project somehow?
    If there's anyone out there who knows how to use this feature I would apprciate their guidance.

  28. #28
    Fanatic Member
    Join Date
    Nov 2017
    Posts
    1,003

    Re: Publishing a database project from Visual Studio 2017

    Quote Originally Posted by Rocky48 View Post
    I don't know where anyone got the idea that the database was a mdf (Access) file. The files were setup and updated in SQL.
    The database is called Verses_Find and the tables dbo.tablename.
    No one said anything about Access until this post of yours, and an mdf file is not an Access database file.

  29. #29

    Thread Starter
    Addicted Member
    Join Date
    Dec 2015
    Location
    Hastings, UK
    Posts
    137

    Re: Publishing a database project from Visual Studio 2017

    Sorry its my lack of overall knowledge. I've always associated mdf with Access files. I thought that everyone used SQL for VS projects. I'm aware that they can be imported into VS, as I have looked at AdventureWorks sample.

    Forgetting that can anyone help me to find out how to use a VS Database project and a VS Windows Forms Project?
    I have setup a database now in a VS database Project and all my windows forms are on a VS Windows Forms project.
    Last edited by Rocky48; Aug 6th, 2019 at 12:35 PM.

  30. #30
    Fanatic Member
    Join Date
    Nov 2017
    Posts
    1,003

    Re: Publishing a database project from Visual Studio 2017

    Maybe you are confusing mdf files with mdb files, I don't know.

    I don't know where anyone got the idea that the database was a mdf (Access) file. The files were setup and updated in SQL. The database is called Verses_Find and the tables dbo.tablename.
    Behind the scenes, your data, stored in that table you reference, which is stored in that database you reference, is all stored in a file. And that file is an mdf file.

  31. #31
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,795

    Re: Publishing a database project from Visual Studio 2017

    Quote Originally Posted by Rocky48 View Post
    Sorry its my lack of overall knowledge. I've always associated mdf with Access files. I thought that everyone used SQL for VS projects. I'm aware that they can be imported into VS, as I have looked at AdventureWorks sample.

    Forgetting that can anyone help me to find out how to use a VS Database project and a VS Windows Forms Project?
    I have setup a database now in a VS database Project and all my windows forms are on a VS Windows Forms project.
    First.... SQL is a language - it stands for Structured Query Language. So yeah, we all use it for various VS Project wether it's in Access, SQL Server, or Oracle. There is no such product called SQL. It's an acronym for a language. That said, yeah, we use SQL Server (which is probably what you meant) and we all use it in various ways. What most of us probably don't do is use the database project template. I know I never have. I may have once or twice to try it out, but in the end I usually feel better just creating my database directly, scripting it out and then shipping the scripts as I mentioned in my previous posts.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  32. #32

    Thread Starter
    Addicted Member
    Join Date
    Dec 2015
    Location
    Hastings, UK
    Posts
    137

    Re: Publishing a database project from Visual Studio 2017

    TechGnome:

    What should have been selected is the option just above that. That would have installed SQLExpress proper. At that point, it should have been a simple matter of then attaching the mdf to the instance and it would be there permanently.
    I obviously am taking the wrong path trying to use LocalDB, so I am going to return to use SQLExpress as you mentioned in #26.
    If that is the case what do you specify as the server, as my 'DESKTOP-XXXXXXX\SQLEXPRESS', as the computer name will not work for another user.

    Personally I don't like shipping mdf files for this very reason. I usually ship the SQL scripts instead. I let the installer setup SQLExpress, then connect to it, create the database, then switch to that database, then start creating the tables, indexes, views, etc. Once that's in place, then I fill it with the data. When it comes to updates, same thing, I ship differential scripts - ones that alter the existing tables, or what ever. Then I don't have to worry about losing data entered by the user.
    Another point I don't understand how to achieve, how whether it be an mdf file or SQL scripts do you get the Publisher to copy these files to the Users PC. I am going to assume that my end users have little knowledge apart from installing a program.
    If you or any other expert can enlighten me i would be very grateful!

  33. #33
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,795

    Re: Publishing a database project from Visual Studio 2017

    Quote Originally Posted by Rocky48 View Post
    I obviously am taking the wrong path trying to use LocalDB, so I am going to return to use SQLExpress as you mentioned in #26.
    If that is the case what do you specify as the server, as my 'DESKTOP-XXXXXXX\SQLEXPRESS', as the computer name will not work for another user.
    I use ".\SQLEXPRESS" for the server... or "localhost\SQLEXPRESS" ... either one... both are the same.


    Quote Originally Posted by Rocky48 View Post
    Another point I don't understand how to achieve, how whether it be an mdf file or SQL scripts do you get the Publisher to copy these files to the Users PC. I am going to assume that my end users have little knowledge apart from installing a program.
    If you or any other expert can enlighten me i would be very grateful!
    As for this part... you include them as part of your project, just like a text file... make sure the Copy to Output is set correctly... then the install is configured to pick up the output of the project...
    So I put my scripts into a script folder, mark each one with Copy to Output as always (or at least "if newer") Then I make sure that what ever installer I use (if I use one, sometimes I jsut zip things up) ... includes what ever is dumped into the apps folder upon build... which will include the script folder with the scripts. Now I know where I can find them.

    When it comes to mdf, though... it's a bit more involved because the files need to go someplace where they can be read/write... AND somewhere the SQL Server is going to find it... AND then they have to be attached before they can be used... which is why I don't normally ship them. And use code/scripts instead. Then I don't care what version of SQL Server they have, where the files are installed, or whether the database will have the correct read/write access of not.


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  34. #34
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,445

    Re: Publishing a database project from Visual Studio 2017

    I'm not an expert on deploying SQL Server Mdf databases but this is the method I used.

    Code:
    Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\testdatabase.mdf;Integrated Security=True
    With this method your mdf file would need to be in the same folder as the application (.exe file).

    If you don't have SQL Server LocalDb installed then https://docs.microsoft.com/en-us/sql...ql-server-2017

    When you publish this app using ClickOnce you can add SQL Server Express localdb as a prerequisite .
    Last edited by wes4dbt; Aug 9th, 2019 at 05:21 PM.

  35. #35

    Thread Starter
    Addicted Member
    Join Date
    Dec 2015
    Location
    Hastings, UK
    Posts
    137

    Re: Publishing a database project from Visual Studio 2017

    TechGnome:
    As for this part... you include them as part of your project, just like a text file... make sure the Copy to Output is set correctly... then the install is configured to pick up the output of the project...
    So I put my scripts into a script folder, mark each one with Copy to Output as always (or at least "if newer") Then I make sure that what ever installer I use (if I use one, sometimes I jsut zip things up) ... includes what ever is dumped into the apps folder upon build... which will include the script folder with the scripts. Now I know where I can find them.
    1) Copy to Output - Where is it? Seeing as I have never come across this setting before, I haven't a clue where to set it?
    2) SQL scripts - I used SSMS to generate a script using Task/Generate Scripts and made a script of the whole database. Tried to execute it and got a message Database already exists. Is this because I am trying to create a database which already exists?
    Update: Tried executing the generated script on the user installation PC, but it was looking for the mdf file?
    Are you suggesting that I make individual sql files for each of the tables?

    Found it!! On StackOverflow found an example so understood where this is set.
    One question though! There are only datasets in Solution Explorer, do I set all of these to Copy Always?
    Last edited by Rocky48; Aug 9th, 2019 at 04:16 PM. Reason: Addition iinfo

  36. #36
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,795

    Re: Publishing a database project from Visual Studio 2017

    1) it's part of hte properties in the solution explorer windoiw... right-click, select properties. You should get a window with properties of the file... looks something like this (I'm using VS 2018 on a Mac)
    Name:  Screen Shot 2019-08-09 at 4.38.36 PM.jpg
Views: 708
Size:  27.1 KB

    set the type to content, the copy to either always or when newer...


    2) Ummm yeah.... that's exactly what that error means. You can't have two databases with the same name.



    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  37. #37

    Thread Starter
    Addicted Member
    Join Date
    Dec 2015
    Location
    Hastings, UK
    Posts
    137

    Re: Publishing a database project from Visual Studio 2017

    I think we are overlapping our posts. Found that setting, but still got problems(see below).

    Tried publishing project after setting all the datasets to copy always.
    Could not see any of the files in any of the Build folders?
    This is the error I got when running the project on the end user PC.
    See the end of this message for details on invoking
    just-in-time (JIT) debugging instead of this dialog box.

    ************** Exception Text **************
    System.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
    at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
    at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
    at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
    at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
    at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
    at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
    at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
    at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
    at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
    at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
    at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
    at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
    at System.Data.SqlClient.SqlConnection.Open()
    at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
    at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
    at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
    at My_Greeting.Verses_DataSetTableAdapters.VerseTableAdapter.Fill(VerseDataTable dataTable)
    at My_Greeting.frmChoose.frmChoose_Load(Object sender, EventArgs e)
    at System.Windows.Forms.Form.OnLoad(EventArgs e)
    at System.Windows.Forms.Form.OnCreateControl()
    at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
    at System.Windows.Forms.Control.CreateControl()
    at System.Windows.Forms.Control.WmShowWindow(Message& m)
    at System.Windows.Forms.Control.WndProc(Message& m)
    at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
    at System.Windows.Forms.Form.WmShowWindow(Message& m)
    at System.Windows.Forms.Form.WndProc(Message& m)
    at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
    at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
    at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
    ClientConnectionId:00000000-0000-0000-0000-000000000000
    Error Number:-1,State:0,Class:20


    ************** Loaded Assemblies **************
    mscorlib
    Assembly Version: 4.0.0.0
    Win32 Version: 4.8.3815.0 built by: NET48REL1LAST_C
    CodeBase: file:///C:/Windows/Microsoft.NET/Framework/v4.0.30319/mscorlib.dll
    ----------------------------------------
    My Greeting
    Assembly Version: 1.0.0.0
    Win32 Version: 1.0.0.0
    CodeBase: file:///C:/Users/tehud/AppData/Local/Apps/2.0/DN5T5ZLK.JJC/R56D6ENT.13X/mygr..tion_9b62e7fe1a0b0606_0001.0000_bae64425ab072d07/My%20Greeting.exe
    ----------------------------------------
    Microsoft.VisualBasic
    Assembly Version: 10.0.0.0
    Win32 Version: 14.8.3752.0 built by: NET48REL1
    CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/Microsoft.VisualBasic/v4.0_10.0.0.0__b03f5f7f11d50a3a/Microsoft.VisualBasic.dll
    ----------------------------------------
    System
    Assembly Version: 4.0.0.0
    Win32 Version: 4.8.3801.0 built by: NET48REL1LAST_B
    CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/System/v4.0_4.0.0.0__b77a5c561934e089/System.dll
    ----------------------------------------
    System.Core
    Assembly Version: 4.0.0.0
    Win32 Version: 4.8.3815.0 built by: NET48REL1LAST_C
    CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/System.Core/v4.0_4.0.0.0__b77a5c561934e089/System.Core.dll
    ----------------------------------------
    System.Windows.Forms
    Assembly Version: 4.0.0.0
    Win32 Version: 4.8.3815.0 built by: NET48REL1LAST_C
    CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/System.Windows.Forms/v4.0_4.0.0.0__b77a5c561934e089/System.Windows.Forms.dll
    ----------------------------------------
    System.Drawing
    Assembly Version: 4.0.0.0
    Win32 Version: 4.8.3752.0 built by: NET48REL1
    CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/System.Drawing/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll
    ----------------------------------------
    System.Configuration
    Assembly Version: 4.0.0.0
    Win32 Version: 4.8.3752.0 built by: NET48REL1
    CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/System.Configuration/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Configuration.dll
    ----------------------------------------
    System.Xml
    Assembly Version: 4.0.0.0
    Win32 Version: 4.8.3752.0 built by: NET48REL1
    CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/System.Xml/v4.0_4.0.0.0__b77a5c561934e089/System.Xml.dll
    ----------------------------------------
    System.Runtime.Remoting
    Assembly Version: 4.0.0.0
    Win32 Version: 4.8.3752.0 built by: NET48REL1
    CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/System.Runtime.Remoting/v4.0_4.0.0.0__b77a5c561934e089/System.Runtime.Remoting.dll
    ----------------------------------------
    System.Data
    Assembly Version: 4.0.0.0
    Win32 Version: 4.8.3752.0 built by: NET48REL1
    CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_32/System.Data/v4.0_4.0.0.0__b77a5c561934e089/System.Data.dll
    ----------------------------------------
    System.Data.DataSetExtensions
    Assembly Version: 4.0.0.0
    Win32 Version: 4.8.3752.0 built by: NET48REL1
    CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/System.Data.DataSetExtensions/v4.0_4.0.0.0__b77a5c561934e089/System.Data.DataSetExtensions.dll
    ----------------------------------------
    System.Numerics
    Assembly Version: 4.0.0.0
    Win32 Version: 4.8.3752.0 built by: NET48REL1
    CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/System.Numerics/v4.0_4.0.0.0__b77a5c561934e089/System.Numerics.dll
    ----------------------------------------
    System.Transactions
    Assembly Version: 4.0.0.0
    Win32 Version: 4.8.3752.0 built by: NET48REL1
    CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_32/System.Transactions/v4.0_4.0.0.0__b77a5c561934e089/System.Transactions.dll
    ----------------------------------------
    System.EnterpriseServices
    Assembly Version: 4.0.0.0
    Win32 Version: 4.8.3752.0 built by: NET48REL1
    CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_32/System.EnterpriseServices/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.EnterpriseServices.dll
    ----------------------------------------

    ************** JIT Debugging **************
    To enable just-in-time (JIT) debugging, the .config file for this
    application or computer (machine.config) must have the
    jitDebugging value set in the system.windows.forms section.
    The application must also be compiled with debugging
    enabled.

    For example:

    <configuration>
    <system.windows.forms jitDebugging="true" />
    </configuration>

    When JIT debugging is enabled, any unhandled exception
    will be sent to the JIT debugger registered on the computer
    rather than be handled by this dialog box.
    Also, I checked using SSMS and the database was not there.
    I'm still floundering around and not getting anywhere.
    Can someone tell me if it's correct to copy the datasets, as in Soluton Explorer the whole database is not shown?

    Just found an item 'Connected Services' whats this for? Looks like it can be used if using Azure or Access! Would this be something I could use?

    Can somebody tell me where I am going wrong?
    Last edited by Rocky48; Aug 9th, 2019 at 05:18 PM.

  38. #38
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,795

    Re: Publishing a database project from Visual Studio 2017

    If I had to guess, it would be because you're trying to connect to your database in your connection string, but it doesn't exist yet... so you're probably wondering, well, how the hell do I connect so I can run my scripts? Rightfully so. I probably forgot to mention how to do that part. You need to make the connection to the master database FIRST... run the script to CREATE DATABASE ... then once that is done, close that connection, and create one to your database (which will now exist), and then run the rest of hte scripts to create the tables and everything else.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  39. #39

    Thread Starter
    Addicted Member
    Join Date
    Dec 2015
    Location
    Hastings, UK
    Posts
    137

    Re: Publishing a database project from Visual Studio 2017

    Have not had time to check yet.
    I realised that if I reinstalled from SQL scripts,the verses will not be formatted (I had to edit the table to put the line feeds in the correct place), otherwise they look a mess and the user would have to do the line feeds as they use the verse. Do think that that would look very professional! Is there a way that the line feeds can be put into the verses in the table.
    Otherwise I think it would be better for me to use the mdf file.

    I still don't know how you install the files automaticlly on the users PC. Do you have to write a script to do that. An example would be most helpful.
    Last edited by Rocky48; Aug 11th, 2019 at 07:38 AM.

  40. #40

    Thread Starter
    Addicted Member
    Join Date
    Dec 2015
    Location
    Hastings, UK
    Posts
    137

    Re: Publishing a database project from Visual Studio 2017

    I have been having a look around the project properties and under Compile Build Events, there is Pre - Build and Post - Build window, where it looks like you can put command lines.
    Is this how you load other files, such as copying mdf file or SQL scripts to the user PC?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width