How to use sqlcmd to run my create database/tables/sp's script
-
Hi Guys,
I've done an application in VB.Net Express 2008. it uses sql server express 2005. I need to now package the software on a cd and let users be able to install. VB.net can check if sql server is already installed and if not it will install it.
However I need help in creating my database tables etc. I know how to create a script. is using sqlcmd to run it the best option? if so, please show me how.
Also, how will I be able to add my database if sql server already exists on someones pc. Will I have username/password issues??
Please please help
-
Hi
You can run the scripts from vb.net application itself. If you are creating database on sql server instance already installed in the users pc you need to get the username and password for connecting to the instance and creating your database and other objects inside the database.
"Keep Trying"
-
Hi
You can run the scripts from vb.net application itself. If you are creating database on sql server instance already installed in the users pc you need to get the username and password for connecting to the instance and creating your database and other objects inside the database.
Sorry for the doube post.
"Keep Trying"
-
Hi ChiragNS.
Thanks for the advice. I've already had a case where the user had sql server installed and I didn't know the sa password. So I had to uninstall and re-install sql server.
The thing now is, he wants to distribute the software on a cd and sell it overseas. I won't be able to fix those kind of problems then. So I need to script all these things and I have no idea how:-(
-
here's a question looking at your issue sideways:
if you are running a script to create a database and all it's objects, why not just include a backup of the complete database and simply restore that database backup instead? Or the mdf/ldf pair so they can be attached?
wouldn't that be easier?(one command of RESTORE DATABASE versus a suite of commands?)
fewer chances of errors due to permissions and stuff i would think.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
thanks Lowell.
That's a great idea. Is it the preffered way though? Also, say a client has SQL server installed on a server somewhere. Then the client installs my app on their local machine. How do I detect that sql server and add my database to it. I'm in way over my head here:crying:
-
Hey Guys,
I found this link which is very helpful:
http://msdn.microsoft.com/en-us/library/49b92ztk(VS.80).aspx
I have also acquired Visual Studio Team Edition over the past week. So I am now able to create Installer Classes etc.
The only thing I have done differently is change the connectionstring datasource to .\SQLExpress. It works fine on my PC but fails on another test pc. It says
"a network related or instance specific error occured while establishing a connection to the server.The server was not found or is not accesible. verify the instance name is correct...
I've attached the text file where I have my table, view, sp creates etc. Please have a look and let me know what I'm doing wrong. Thank you
-
well, i've done the basic setup lots of differnet ways, from providing a database for the dba to attach, to providing a script for the dba to run, to doing everything internally from the application itself using a ton of ExecuteNonQuery(mysqlStatement) to do everything you are talking about...create the database, add all it's objects, etc.
at some point, you are prompting the end user for credentials to the SLQ server and saving them someplace, i assume> you have some sort of form/interface that they provide the SQL info? you cannot/shouldnot assume things like a SQLExpress instance must exist, that it must have a CDrive so you can create N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Games.mdf' for example; one of our servers here does not ahve a c: drive, and others do not have the identical folder structure like a MSSQL.1 directory.
once you have a connection, you can try to create your database with the simplest CREATE DATABASE [Games] command, and build the rest from there, all within your application with executenonquery commands.
how are you planning on doing it?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
wow. that looks great Lowell. Please can you point me to an example of how to create that form, or even some code of your own. If I could do something like that all my problems will be solved. All your points are very very helpful
-
Hi again Lowell,
Please help me. I've seen examples of how to add all sql servers on a network into a combobox. How can I list all instances of sql server express? I'd love to make a form like the one you have but I'm battling to get it started
-
well, the command line program osql can give you a list of all SQL servers that answer on the network, whether they are SQLExpress versions or not would require running @@version on each one;
open a command line window and run this command:
OSQL -L
you'll see a list of sql servers; you can use this proc(if xp_cmdShell is enabled; to do the same from SQL, but you can capture the results with .NET as well.
CREATE PROCEDURE dbo.ListLocalServers
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #servers(sname VARCHAR(255))
INSERT #servers EXEC master..XP_CMDShell 'OSQL -L'
-- play with ISQL -L too, results differ slightly
DELETE #servers WHERE sname='Servers:'
SELECT LTRIM(sname) FROM #servers WHERE sname != 'NULL'
DROP TABLE #servers
END
EXEC ListLocalServers
results:
(local)
ASTRAH
AURORA
BELLE
etc....
If i put together a vb.net project for you with that form as an example, would you be able to use it? have you used .NET?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thank you Lowell. I will try that now. You are a lifesaver:-D
I would really really appreciate it if you could do a sample of that form for me as I am using VB.net for my project. Look forward to your reply. Thanks again for being so helpful. I am learning alot from you:-)
-
niteshrajgopal (6/11/2010)
Hi ChiragNS.Thanks for the advice. I've already had a case where the user had sql server installed and I didn't know the sa password. So I had to uninstall and re-install sql server.
The thing now is, he wants to distribute the software on a cd and sell it overseas. I won't be able to fix those kind of problems then. So I need to script all these things and I have no idea how:-(
Sorry for the delay in replying.
What i meant was for your application asking for the password during installation.
"Keep Trying"
-
niteshrajgopal (6/17/2010)
Thank you Lowell. I will try that now. You are a lifesaver:-DI would really really appreciate it if you could do a sample of that form for me as I am using VB.net for my project. Look forward to your reply. Thanks again for being so helpful. I am learning alot from you:-)
Nite that for was part of a larger app, and it used DevExpress controls, which I'm sure you wouldn't have;
I re-wrote it to use just native MS controls; It's got a LOT of different features that i left in. It was actually neat for me to rebuild it and switch objects around.
1. Keeps an XML File of a history of server connections to make it easy to re-connect.
2. Saves your last connection in a settings config file.
3. uses AES encryption to safeguard the passwords.
4. Added the ability to discover existing Servers on your network via the SMO object.
5. Allows the selection of either SQL or Windows connectionstrings.
6. Uses Microsoft Application Blocks SQLHelper class for ease of use.
7. main form simply calls the connection form, which returns a connection string; if it's not obvious, you could have it return a SQLConnection instead;
If you are a new-to-programming, this might be a little overwhelming; see if you can get your mental arms around this project.
put a breakpoint in the main form's "Get Connection" button; right now it does what you originally asked: after getting a connection, it tries to create a database(mySandBox) and then tries to Create one table; i figured those two steps would point you in the right direction for running your scripts.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell I cannot thank you enough for your help and effort. I appreciate you sacrificing so much of time to help me. I hope I can return the favour one day. I just downloaded the project now. Will go through it properly in a few minutes, but I noticed these namespaces cannot be found on my pc:
Imports Microsoft.SqlServer.ManagementImports Microsoft.SqlServer.Management.Smo
Probably because I don't have the full version of sql server installed. I only have the express version.
What do I do to resolve this:(
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply