Creating a variable to hold a table/view name

Hello all,
Is it possible to create a variable in a SQL script to hold a table/view
name?
Alot of my scripts are union queries where each part refers to the same
table/view, and sometimes those sources need to be changed. It would be
nice if I could change the value of the variable instead of doing a find &
replace to change the source's name.
Thanks for any help anyone can provide,
Conan Kelly
"Conan Kelly" <CTBarbarinNOSPAM@.msnNOSPAM.comNOSPAM> wrote in message
news:8LHDh.49165$5j1.25117@.bgtnsc04-news.ops.worldnet.att.net...
> Hello all,
> Is it possible to create a variable in a SQL script to hold a table/view
> name?
> Alot of my scripts are union queries where each part refers to the same
> table/view, and sometimes those sources need to be changed. It would be
> nice if I could change the value of the variable instead of doing a find &
> replace to change the source's name.
> Thanks for any help anyone can provide,
> Conan Kelly
>
http://www.sommarskog.se/dynamic_sql.html
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

Creating a variable to hold a table/view name

Hello all,
Is it possible to create a variable in a SQL script to hold a table/view
name?
Alot of my scripts are union queries where each part refers to the same
table/view, and sometimes those sources need to be changed. It would be
nice if I could change the value of the variable instead of doing a find &
replace to change the source's name.
Thanks for any help anyone can provide,
Conan Kelly"Conan Kelly" <CTBarbarinNOSPAM@.msnNOSPAM.comNOSPAM> wrote in message
news:8LHDh.49165$5j1.25117@.bgtnsc04-news.ops.worldnet.att.net...
> Hello all,
> Is it possible to create a variable in a SQL script to hold a table/view
> name?
> Alot of my scripts are union queries where each part refers to the same
> table/view, and sometimes those sources need to be changed. It would be
> nice if I could change the value of the variable instead of doing a find &
> replace to change the source's name.
> Thanks for any help anyone can provide,
> Conan Kelly
>
http://www.sommarskog.se/dynamic_sql.html
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Creating a variable to hold a table/view name

Hello all,
Is it possible to create a variable in a SQL script to hold a table/view
name?
Alot of my scripts are union queries where each part refers to the same
table/view, and sometimes those sources need to be changed. It would be
nice if I could change the value of the variable instead of doing a find &
replace to change the source's name.
Thanks for any help anyone can provide,
Conan Kelly"Conan Kelly" <CTBarbarinNOSPAM@.msnNOSPAM.comNOSPAM> wrote in message
news:8LHDh.49165$5j1.25117@.bgtnsc04-news.ops.worldnet.att.net...
> Hello all,
> Is it possible to create a variable in a SQL script to hold a table/view
> name?
> Alot of my scripts are union queries where each part refers to the same
> table/view, and sometimes those sources need to be changed. It would be
> nice if I could change the value of the variable instead of doing a find &
> replace to change the source's name.
> Thanks for any help anyone can provide,
> Conan Kelly
>
http://www.sommarskog.se/dynamic_sql.html
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Creating a table script that includes the data in the table

Hello,

I'm having a bear of a time moving my data from one database to another. Unfortunately both these databases are at webhosting providers so I don't readily have access to backup images. I might be able to get the new provider to put the backup image somewhere where I can reach it but the old provider is playing dead so I can't get to any backup image I could generate.

I've tried using Enterprise Manager for SQL2000 to move data from one to another. This grinds away for a while and eventually errors out with permission problems. I will try this again tonight as the provider has attempted to give me the needed permissions. In the meantime I'd like to try to pull the data out of the database just in case it goes belly-up. I've tried to create a script for my tables but all I get is the table structure, no data.

I don't have access to a full Enterprise Manager right now, only SQL Server Management Studio Express (someone needs to shorten that name :).

I'm a big SQL Server fan but it sure is easy to create one big SQL script for a MySQL databsae that you pull out of one db and apply to another one. Poof! Cloned copy. How do I do this with MSSQL and freely available tools? Command line is fine. Surely this is a problem that people have all the time?

Thanks,

Sander

Hi,

for a TSQL solution this could do the trick for you:

http://vyaskn.tripod.com/code.htm#inserts

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Hello,

thanks for posing this solution! I succeeded in the meantime to copy the data using EM but I still want this tool to work so I'll give that a try as well.

Thanks,

Sander

Creating a SQL script file from a stored procedure

I'd like to create a SQL script file from within a stored procedure. How do I:

1. Open the text file
2. Write to it
3. Close it when done

Thanks in advance,


Barrysounds like a job better accomplished through application code. filesystem object in vbscript perhaps.

but if you insist, perhaps I would write my sql string to a table and then fire a dts package from my sp to export the field to a text file.

what are you trying to accomplish? I can't imagine how this might be particularly useful.

more details would be helpful.

are you trying to open an application on the server side. Not a good idea. Saw someone trigger Access to open on the server side from the web once. If the the user closed the browser or something that instance of Access just stayed open on server and the mess this caused until I fixed it.|||You can use master..xp_cmdshell extended stored proc to run DOS command. It should look like

Exec master..xp_cmdshell 'Echo Whatever > MylogFile.Txt'

Cheers.|||Thrasymachus and mkostadinovic, thank you for your replies.

I ended up using osql to output the text from the SQL code:

osql -U xx -P xxxxxx -S (local) -d xxxx -h-1 -w 4000 -n -i "C:\...\ExtractUpdatePlateData.sql" -o "C:\...\Insert plate data.sql"

In case you are still wondering why, the reason for doing this was to script changes made to the contents of a number of database tables from ~20 updates that had built up over the last 18 months or so. I needed to be able to extract out the data, but had a constraint that I couldn't just use a file containing the data, and a script to import it. Here is part of the SQL from ExtractUpdatePlateData.sql:

SELECT InsertScript = 'INSERT INTO tblUPDsecttype (vv_secttype, vi_proctype, vi_numwells, vv_descrip) VALUES (' + '''' + vv_secttype + '''' + ', ' + CAST(vi_proctype AS NVARCHAR) + ', ' + CAST(vi_numwells AS NVARCHAR) + ', ' + '''' + vv_descrip + '''' + ')'
FROM tblUPDsecttype

This is how it appears in Insert plate data.sql:

INSERT INTO tblUPDsecttype (vv_secttype, vi_proctype, vi_numwells, vv_descrip) VALUES ('ANO2B', 1, 96, 'ANAEROBE SYSTEMIC Man MiC')

It all seems to work fine (and has saved me a week's tedious work already).


Best regards,


Barry

Creating a Script out of Sample Data

SQL Server 2000

Is there any way to take sample data in my database and create an INSERT INTO script?

I have a commercial application that I would like to include sample data, and instead of restoring a backup like I am doing now, I would like to first run a script that creates the database, stored procedures, etc, then run a script that inserts sample data if the customer so chooses.

I know I can do this manually, but is there any way to create the script based on exisiting data?

TIA

--
Tim Morrison

------------------------

Vehicle Web Studio - The easiest way to create and maintain your vehicle related website.
http://www.vehiclewebstudio.comTim,

You can use our free tool, QALite or ObjectScriptr to generate insert
statements. You can download it from site.

--
-oj
http://www.rac4sql.net

"Tim Morrison" <sales@.kjmsoftware.com> wrote in message
news:J%1Lb.761723$HS4.6022647@.attbi_s01...
SQL Server 2000

Is there any way to take sample data in my database and create an INSERT INTO
script?

I have a commercial application that I would like to include sample data, and
instead of restoring a backup like I am doing now, I would like to first run a
script that creates the database, stored procedures, etc, then run a script that
inserts sample data if the customer so chooses.

I know I can do this manually, but is there any way to create the script based
on exisiting data?

TIA

--
Tim Morrison

------------------------

Vehicle Web Studio - The easiest way to create and maintain your vehicle related
website.
http://www.vehiclewebstudio.com|||"Tim Morrison" <sales@.kjmsoftware.com> wrote in message news:<J%1Lb.761723$HS4.6022647@.attbi_s01>...
> SQL Server 2000
> Is there any way to take sample data in my database and create an INSERT
> INTO script?
> I have a commercial application that I would like to include sample
> data, and instead of restoring a backup like I am doing now, I would
> like to first run a script that creates the database, stored procedures,
> etc, then run a script that inserts sample data if the customer so
> chooses.
> I know I can do this manually, but is there any way to create the script
> based on exisiting data?
> TIA
> --
> Tim Morrison
> ----------------------
> ---
> Vehicle Web Studio - The easiest way to create and maintain your vehicle
> related website.
> http://www.vehiclewebstudio.com
> --

You could use BCP/DTS to load the sample data from external files, but
if you want to use pure TSQL, then this is one possibility:

http://vyaskn.tripod.com/code.htm#inserts

Simon|||Looks like ObjectScriptr is just what im looking for.

Thanks,

Tim Morrison

"oj" <nospam_ojngo@.home.com> wrote in message
news:JN7Lb.781998$Fm2.760879@.attbi_s04...
> Tim,
> You can use our free tool, QALite or ObjectScriptr to generate insert
> statements. You can download it from site.
> --
> -oj
> http://www.rac4sql.net
>
> "Tim Morrison" <sales@.kjmsoftware.com> wrote in message
> news:J%1Lb.761723$HS4.6022647@.attbi_s01...
> SQL Server 2000
> Is there any way to take sample data in my database and create an INSERT
INTO
> script?
> I have a commercial application that I would like to include sample data,
and
> instead of restoring a backup like I am doing now, I would like to first
run a
> script that creates the database, stored procedures, etc, then run a
script that
> inserts sample data if the customer so chooses.
> I know I can do this manually, but is there any way to create the script
based
> on exisiting data?
> TIA
> --
> Tim Morrison
> -----------------------
--
> Vehicle Web Studio - The easiest way to create and maintain your vehicle
related
> website.
> http://www.vehiclewebstudio.com|||Hi !
Try http://www.sqlscripter.com

This tool is able to create INSERT, UPDATE and DELETE commands.

Nicksql

Creating a script file from a diagram

Hi there,

I have created a diagram on my development SQL2005 database and want to transfer this diagram to my live environment. How do I go about creating a script to do this?

thx

Dawid

There is no way to script out diagrams in SQL Server 2005. The binary data in the diagram is tied to your SID (a unique identifier for security principals) when you created the diagram, so I can't even give you foolproof instructions on how to hack the tables to copy the diagram data.

I'm afraid you'll have to recreate your diagram on your live system. If this is a feature you'd like to see in the next version of SQL Server, please file a suggestion on http://connect.microsoft.com/SqlServer. Customer feedback is a very important criterion when we choose which features will make it into the next release.

Thanks,

Steve

|||

I highly recommend using Visio (or one of the other robust diagramming tools) to create your database diagrams.

Printouts are easier. Documentation is easier. Scripts are easy. Changes to the diagrams can be saved WITHOUT changing your database. (Great for development environments.) Multiple step Un-Do/Re-Do!!

Creating a Script component using SSIS Object model

I had a SSIS Package which was developed on Beta Version Of SSIS, Now We have Standard Edition Of SSIS(Sql2k5) Installed.
In the Package we have One Flat file Source, One Script Component and A Sql Server as Target, We are programaticaly creating the package, Now When we open this Package in the Designer and Try to Run We get this error
"The script component is configured to pre-compile the script, but binary code is not found. Please visit the IDE in Script Component Editor by clicking Design Script button to cause binary code to be generated. "
Now when we just open the Script Designer and close it, The package runs.
I found the Error description at this link
http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.hresults.dts_e_binarycodenotfound.aspx
but could not find any help on this.

Same thing use to work on Beta version now it is now working.
From my understanding the problem is that in beta version, the script use to run at runtime, Now what they have done is that they generate a compiled code out of script and run.
My problem is that since i am using C# code to generate the package, How Will I Create a Binary Code out of Script.

Creating the binary code is very easy. Go into the script component, click on "Script..." button to bring up VSA (i.e. the script editor) and then close VSA down again. This will create the binary code for you.

-Jamie

|||

What changed is that the default value of the Precompile property changed from False to True.

If you are not precompiling your scripts, all you need to do is change the value of that property on the Script component. Note that this will give you a significantly smaller package file, but you'll pay a small performance penalty at runtime.

-Doug

creating a login

Is there a way to create a login and password, or change a login's password
in management studio (sql 2005) without using a sql script? Any wizard or
dialog box?
In the Management Studio, open the Object Explorer and expand your Server,
then Security, then Logins and right-click on the login you want to change. A
dialog box will open and you can change the password in there, near the top.
Type in the password in the Password box, and the same password again in the
Confirm Password box.
AndyP,
Sr. Database Administrator,
MCDBA 2003
"mkiger" wrote:

> Is there a way to create a login and password, or change a login's password
> in management studio (sql 2005) without using a sql script? Any wizard or
> dialog box?
|||Thanks, that works for server logins, but what about database users?
"AndyP" wrote:
[vbcol=seagreen]
> In the Management Studio, open the Object Explorer and expand your Server,
> then Security, then Logins and right-click on the login you want to change. A
> dialog box will open and you can change the password in there, near the top.
> Type in the password in the Password box, and the same password again in the
> Confirm Password box.
>
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "mkiger" wrote:
|||You can go to the database, then Security, then Users, and add/remove the
users to/from the database and affect their overall database access that way.
You can then grant or revoke permissions at that level. The database user
does not have a password property, but is linked to a login which does have a
password.
AndyP,
Sr. Database Administrator,
MCDBA 2003
"mkiger" wrote:
[vbcol=seagreen]
> Thanks, that works for server logins, but what about database users?
> "AndyP" wrote:

creating a login

Is there a way to create a login and password, or change a login's password
in management studio (sql 2005) without using a sql script? Any wizard or
dialog box?In the Management Studio, open the Object Explorer and expand your Server,
then Security, then Logins and right-click on the login you want to change. A
dialog box will open and you can change the password in there, near the top.
Type in the password in the Password box, and the same password again in the
Confirm Password box.
AndyP,
Sr. Database Administrator,
MCDBA 2003
"mkiger" wrote:
> Is there a way to create a login and password, or change a login's password
> in management studio (sql 2005) without using a sql script? Any wizard or
> dialog box?|||Thanks, that works for server logins, but what about database users?
"AndyP" wrote:
> In the Management Studio, open the Object Explorer and expand your Server,
> then Security, then Logins and right-click on the login you want to change. A
> dialog box will open and you can change the password in there, near the top.
> Type in the password in the Password box, and the same password again in the
> Confirm Password box.
>
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "mkiger" wrote:
> > Is there a way to create a login and password, or change a login's password
> > in management studio (sql 2005) without using a sql script? Any wizard or
> > dialog box?|||You can go to the database, then Security, then Users, and add/remove the
users to/from the database and affect their overall database access that way.
You can then grant or revoke permissions at that level. The database user
does not have a password property, but is linked to a login which does have a
password.
--
AndyP,
Sr. Database Administrator,
MCDBA 2003
"mkiger" wrote:
> Thanks, that works for server logins, but what about database users?
> "AndyP" wrote:
> > In the Management Studio, open the Object Explorer and expand your Server,
> > then Security, then Logins and right-click on the login you want to change. A
> > dialog box will open and you can change the password in there, near the top.
> > Type in the password in the Password box, and the same password again in the
> > Confirm Password box.
> >
> >
> > --
> > AndyP,
> > Sr. Database Administrator,
> > MCDBA 2003
> >
> >
> > "mkiger" wrote:
> >
> > > Is there a way to create a login and password, or change a login's password
> > > in management studio (sql 2005) without using a sql script? Any wizard or
> > > dialog box?

creating a login

Is there a way to create a login and password, or change a login's password
in management studio (sql 2005) without using a sql script? Any wizard or
dialog box?In the Management Studio, open the Object Explorer and expand your Server,
then Security, then Logins and right-click on the login you want to change.
A
dialog box will open and you can change the password in there, near the top.
Type in the password in the Password box, and the same password again in the
Confirm Password box.
AndyP,
Sr. Database Administrator,
MCDBA 2003
"mkiger" wrote:

> Is there a way to create a login and password, or change a login's passwor
d
> in management studio (sql 2005) without using a sql script? Any wizard or
> dialog box?|||Thanks, that works for server logins, but what about database users?
"AndyP" wrote:
[vbcol=seagreen]
> In the Management Studio, open the Object Explorer and expand your Server,
> then Security, then Logins and right-click on the login you want to change
. A
> dialog box will open and you can change the password in there, near the to
p.
> Type in the password in the Password box, and the same password again in t
he
> Confirm Password box.
>
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "mkiger" wrote:
>|||You can go to the database, then Security, then Users, and add/remove the
users to/from the database and affect their overall database access that way
.
You can then grant or revoke permissions at that level. The database user
does not have a password property, but is linked to a login which does have
a
password.
AndyP,
Sr. Database Administrator,
MCDBA 2003
"mkiger" wrote:
[vbcol=seagreen]
> Thanks, that works for server logins, but what about database users?
> "AndyP" wrote:
>

creating a folder on the server

Hi,
I have a script that runs on an IIS webserver. The webserver is on a
different domain as the MSSQL server. I have an asp page that uses DSNless
connection to connect to the SQL server and execute stored procedures to
create a database locally. Everything works fine and both the MDF and LOG
files get created. The problem is that I want the files to be created in a
new folder everytime with name passed to it by the asp page.
How can i do this, i can't create it through the asp page because the IIS
server cannot access the MSSQL's servers harddrive.
Is there a way to create a folder locally ?
I was thinking of using a batch file and execute that, but don't know how to
call that.
Any help would be appreciated
Thanx
You can use xp_cmdshell to run internal commands such as MD, bat files and exe files.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"robert" <rob@.ms.com> wrote in message news:%232bE672rFHA.3556@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a script that runs on an IIS webserver. The webserver is on a
> different domain as the MSSQL server. I have an asp page that uses DSNless
> connection to connect to the SQL server and execute stored procedures to
> create a database locally. Everything works fine and both the MDF and LOG
> files get created. The problem is that I want the files to be created in a
> new folder everytime with name passed to it by the asp page.
> How can i do this, i can't create it through the asp page because the IIS
> server cannot access the MSSQL's servers harddrive.
> Is there a way to create a folder locally ?
> I was thinking of using a batch file and execute that, but don't know how to
> call that.
> Any help would be appreciated
> Thanx
>
|||Another alternative is to do this as an ActiveX script task inside a DTS
package, which can be kicked off from your ASP page.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"robert" <rob@.ms.com> wrote in message
news:%232bE672rFHA.3556@.TK2MSFTNGP10.phx.gbl...
Hi,
I have a script that runs on an IIS webserver. The webserver is on a
different domain as the MSSQL server. I have an asp page that uses DSNless
connection to connect to the SQL server and execute stored procedures to
create a database locally. Everything works fine and both the MDF and LOG
files get created. The problem is that I want the files to be created in a
new folder everytime with name passed to it by the asp page.
How can i do this, i can't create it through the asp page because the IIS
server cannot access the MSSQL's servers harddrive.
Is there a way to create a folder locally ?
I was thinking of using a batch file and execute that, but don't know how to
call that.
Any help would be appreciated
Thanx
|||Thanks will try it out.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ekf8gz6rFHA.464@.TK2MSFTNGP15.phx.gbl...
> Another alternative is to do this as an ActiveX script task inside a DTS
> package, which can be kicked off from your ASP page.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "robert" <rob@.ms.com> wrote in message
> news:%232bE672rFHA.3556@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a script that runs on an IIS webserver. The webserver is on a
> different domain as the MSSQL server. I have an asp page that uses DSNless
> connection to connect to the SQL server and execute stored procedures to
> create a database locally. Everything works fine and both the MDF and LOG
> files get created. The problem is that I want the files to be created in a
> new folder everytime with name passed to it by the asp page.
> How can i do this, i can't create it through the asp page because the IIS
> server cannot access the MSSQL's servers harddrive.
> Is there a way to create a folder locally ?
> I was thinking of using a batch file and execute that, but don't know how
> to
> call that.
> Any help would be appreciated
> Thanx
>

Creating a DB script

Somebody knows a tool able to generate DB scripts from a .mdf file?

Management Studio Express.

Right-click on the database and choose 'Script Database As..." and select an option. You can also script individual objects. This does not include data, you'd need to use BCP for data.

Management Studio Express CTP is at http://www.microsoft.com/downloads/details.aspx?familyid=57856cdd-da9b-4ad0-9a8a-f193ae8410ad&displaylang=en.

Regards,

Mike Wachal
SQL Express team

-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1

|||I already tried Managment Studio Express, but for some reason it cannot access my .mdf file that is located at:
C:\Documents and Settings\Neto\Mis documentos\Visual Studio 2005\Projects\ProjectName\Database\myBD.mdf

The tree view that browses the folders won't let me get past 'My Documents'|||

SQL Express runs as the Network Services account, which is a limited access account that doesn't have permissions for a number of things, including User Profile directories.

If this is a one time operation, I would suggest detaching the database and moving it to a location where you can access it (such as the MSSQL\Data directory), attaching it there and then using Management Studio to generate the scripts.

If this is a task you need to do repeatedly, you'll probably need a programatic way to do this. I haven't been able to find a T-SQL method for doing this, maybe someone else knows of one. If not, you can do this from SMO, which you can get an overview of starting at http://msdn2.microsoft.com/en-us/library/ms162557.aspx.

Regards,

Mike Wachal
SQL Express team

-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1

Creating a database from script - permissions problems

We deploy our app via ClickOnce. Rather than shipping the .mdf and .ldf, the app detects whether the database is there and creates it via script if it's not. The script was originally generated from SQL Exrpess Mangement Console, etc.

In the past, we've made the stipulation that the user must have administrator access to the machine. However, we now need to find a workable solution for users who are not administrators on their machine. Currently, we use the following connection string before attempting to run the db creation script:

conn.ConnectionString = "Server=.\\sqlexpress;Integrated Security = true;User Instance=false";

The script fails when trying to execute "CREATE DATABASE [myDatabaseName] ON PRIMARY "

with the error:

"CREATE DATABASE permission denied in database 'Master'"

How can we get around this permissions issue in the most automated way possible?

TIA!

Hi,

you will need to have a user with dbcreator permissions, otherwise you won′t be able to make an entry in the sysdatabases (Creating a database). Can you use any administrative SQL Server login to connect to the database and make this possible ? The connection string has to include the userid as well as the password then.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

creating a database by sql script

Hi

I developed a program. This program will use a SQL server database. How can I create the database using code. I think I need to use sql scipt like this:

create database customers

But where should I write the script and How can I make VB code implement the script.

THanks a lot

You can just execute the sql statement.

e.g.

using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = "create database [db1]";
cmd.CommandType = CommandType.Text;
//execute
cmd.ExecuteNonQuery();
}

|||

Thank you very much.

I used the following code. It worked in the first time. But when I debugged the program for the second time, I had this error " database db1 already exists"

How can I solve this problem?

And can you give me a sql script to create one database with one three-column table?

thanks

Using (cmdGet)

cmdGet.Connection = conGet

cmdGet.CommandText = "create database [db1]"

cmdGet.CommandType = CommandType.Text

cmdGet.ExecuteNonQuery()

End Using

|||

Code Snippet

'create db

cmdGet.CommandText = "if db_id('db1') is null exec('create database [db1]')"

cmdGet.CommandType = CommandType.Text

cmdGet.ExecuteNonQuery()

'create table

cmdGet.CommandText = "use db1; if object_id('tb1') is null exec('create table tb1(col1 int, col2 int, col3 int)')"

cmdGet.ExecuteNonQuery()

Creating A Dataabas Via SQL Script

I have an SQL script file and what it does is it creates a database with tables and stored procedures, the problem that I have is that I dont know how to execute it, I opened it in VS.NET 2003 but there is no option to execute it anyone know how I can accomplish this. thank you all for your time and patience.If you have the SQL Client installed, you can use Query Analyzer. If not, in theory you could use OSQL.exe (for instance, if you have MSDE and do not have the SQL Client installed). Easier would be to install the SQL client, if you can.|||Where can I get a copy of the SQL client? i do have the OSQL.exe but it's command line and I cant seem to find anything in the help file as to how to run database creating procedures. but I do really appreciated the direction that you set me in. thanks.|||The cheapest way (if you are not an MSDN subscriber) is to get the developers edition, about US$40.00 last time I looked.

The osql utility is typically used in these ways:

a.. Users interactively enter Transact-SQL statements in a manner similar
to working on the command prompt. The results are displayed in the command
prompt window.

b.. Users submit an osql job either specifying a single Transact-SQL
statement to execute or pointing the utility to a text file that contains
Transact-SQL statements to execute. The output is usually directed to a text
file, but it also can be displayed in the command prompt window.
The osql utility uses the ODBC database application programming interface
(API). It is a replacement for the isql command prompt utility based on the
DB-Library API. Both utilities are provided with Microsoft SQL ServerT 2000.
The DB-Library API remains at a SQL Server 6.5 level; therefore,
applications that depend on DB-Library, such as isql, do not support some
SQL Server 2000 features. For example, isql cannot access columns defined
with the ntext data type and truncates any char, varchar, nchar, or nvarchar
columns longer than 255 bytes. It also cannot retrieve results as XML
documents. Except for these limitations in isql, both osql and isql support
the same features

Typing OSQL -? gets you this:

E:\Program Files\Microsoft SQL Server\80\Tools\Binn>osql /?
usage: osql [-U login id] [-P password]
[-S server] [-H hostname] [-E trusted connection]
[-d use database name] [-l login timeout] [-t query timeout]
[-h headers] [-s colseparator] [-w columnwidth]
[-a packetsize] [-e echo input] [-I Enable Quoted Identifiers]
[-L list servers] [-c cmdend] [-D ODBC DSN name]
[-q "cmdline query"] [-Q "cmdline query" and exit]
[-n remove numbering] [-m errorlevel]
[-r msgs to stderr] [-V severitylevel]
[-i inputfile] [-o outputfile]
[-p print statistics] [-b On error batch abort]
[-X[1] disable commands [and exit with warning]]
[-O use Old ISQL behavior disables the following]
<EOF> batch processing
Auto console width scaling
Wide messages
default errorlevel is -1 vs 1
[-? show syntax summary]

creating a back up [create and insert statement] for a table in mssql server 2005

hi all,
i have a php application connecting to mssql server 2005. i want to generate sql script for backing up a table with create statement and complete inserts. i dont want to use data publishing wizard. how can i do it.

Thanks

Quote:

Originally Posted by bhakti

hi all,
i have a php application connecting to mssql server 2005. i want to generate sql script for backing up a table with create statement and complete inserts. i dont want to use data publishing wizard. how can i do it.

Thanks

or you can do a SELECT ... INTO NewTableName from MyTable...

Creating .sql script file with data

Hi, how to create a single script file that contains db schema and data?
(Moving db to another machine with sql script file) . I have tried data
export wizard, but it does not export data, but only schema of db.> Hi, how to create a single script file that contains db schema and data?
> (Moving db to another machine with sql script file) . I have tried data
> export wizard, but it does not export data, but only schema of db.
You might want to see ApexSQL Script for this
http://www.apexsql.com/index_ai.htm
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
There are 2 better options to perform this,
1. Detach and Attach
a. SP_detach_db <dbname>
b. Copy the MDF and LDF to destination
c. SP_ATTACH_DB <dbanme>,@.filename1='file.mdf',@.filename2='file.ldf'
Note: After detach please attach the original db again.
2. Backup and restore
1. Backup the database (Backup database dbname to
disk='c:\backup\dbname.bak' with init )
2. COpy the dbname.bak to destination server
3. Restore the database (Restore database dbname
disk='c:\backup\dbname.bak' )
Thanks
Hari
MCDBA
"Jessy" <admin@.zambak.com> wrote in message
news:O0rtNgF$DHA.2520@.TK2MSFTNGP11.phx.gbl...
> Hi, how to create a single script file that contains db schema and data?
> (Moving db to another machine with sql script file) . I have tried data
> export wizard, but it does not export data, but only schema of db.
>

Creating .sql script file with data

Hi, how to create a single script file that contains db schema and data?
(Moving db to another machine with sql script file) . I have tried data
export wizard, but it does not export data, but only schema of db.> Hi, how to create a single script file that contains db schema and data?
> (Moving db to another machine with sql script file) . I have tried data
> export wizard, but it does not export data, but only schema of db.
You might want to see ApexSQL Script for this
http://www.apexsql.com/index_ai.htm
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Create a backup and restore that on to the machine.
Simple and fast.
Mark Baekdal
www.dbghost.com
DB Ghost rocks!
>--Original Message--
>Hi, how to create a single script file that contains db
schema and data?
>(Moving db to another machine with sql script file) . I
have tried data
>export wizard, but it does not export data, but only
schema of db.
>
>.
>|||Hi,
There are 2 better options to perform this,
1. Detach and Attach
a. SP_detach_db <dbname>
b. Copy the MDF and LDF to destination
c. SP_ATTACH_DB <dbanme>,@.filename1='file.mdf',@.filename2='file.ldf'
Note: After detach please attach the original db again.
2. Backup and restore
1. Backup the database (Backup database dbname to
disk='c:\backup\dbname.bak' with init )
2. COpy the dbname.bak to destination server
3. Restore the database (Restore database dbname
disk='c:\backup\dbname.bak' )
Thanks
Hari
MCDBA
"Jessy" <admin@.zambak.com> wrote in message
news:O0rtNgF$DHA.2520@.TK2MSFTNGP11.phx.gbl...
> Hi, how to create a single script file that contains db schema and data?
> (Moving db to another machine with sql script file) . I have tried data
> export wizard, but it does not export data, but only schema of db.
>

Creating "installation" script

Hi all,

I′m newbie with SQL Server.

I need to create a installation script SQL
(creating tables, indexes, permissions, databases).
This script calls other scripts.
In Oracle I use @.script_name.sql, but in SQL Server
I don′t know to do this.

Other question: Is there a way to call the Query Analyzer
in .BAT file, where the .BAT file calls installation scripts SQL.

Thank U very much,

[]′s
Yes sure you can do this, you have to call the equivalent to sqlplus.exe for SQL Server, its OSQL.EXE. The -o Option takes an input file with the statements that you want to execute. Other options are described in detail -S for the server -U for the User -P for the password and so on.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Thank u very much!!!!

[]′s