How to create database on remote machine using osql

Hi All,
I am using OSQL to create database.
When I am using it to create the database localy it works great when I
use the -s switch (target manchine) the call ended successfuly only the
database is created om my PC and not on the remote one.
Does anyone know why?
Thanks in advance Oren
What is your call, that should work also remotely ?
HTH, Jens Suessmeyer.
|||Do you have proper permissions on the remote machine? Can you post the oSql
call?
Andrew J. Kelly SQL MVP
<orencs@.gmail.com> wrote in message
news:1139142162.951389.181080@.o13g2000cwo.googlegr oups.com...
> Hi All,
> I am using OSQL to create database.
> When I am using it to create the database localy it works great when I
> use the -s switch (target manchine) the call ended successfuly only the
> database is created om my PC and not on the remote one.
> Does anyone know why?
>
> Thanks in advance Oren
>
|||I'm not sure if it's a typo but the way to run a command on a different
server is to use the -S parameter. -s sets the column separator which is
probably not what you want.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<orencs@.gmail.com> wrote in message
news:1139142162.951389.181080@.o13g2000cwo.googlegr oups.com...
> Hi All,
> I am using OSQL to create database.
> When I am using it to create the database localy it works great when I
> use the -s switch (target manchine) the call ended successfuly only the
> database is created om my PC and not on the remote one.
> Does anyone know why?
>
> Thanks in advance Oren
>
|||Sorry, my mistake for not providing enough information.
I am running the following:
A Batch file
osql -U sa -P poqi -i "1install.sql" -o "install.res"
The install.sql file looks like this:
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name =
N'MyDB')
DROP DATABASE [MyDB]
GO
CREATE DATABASE [MyDB]
GO
use [MyDB]
GO
...Then I create the tables SP etc...
The above works great when I want to create the database on my
computer.
My problem is that I need to run the scripts in PC A and install the
database on PC B.
The batch and sql file are in PC A.
I don't know how to do that.
I have tried the -s switch for the OSQL command.
I have change the batch file like this:
osql -S 100.100.100.11 -U sa -P poqi -i "1install.sql" -o
"install.res"
100.100.100.11 - is the IP of PC B.
I don't receive any error.
The install.res file indicate that everything went well and in fact it
did only the OSQL command disregard the -s switch and create the DB on
PC A.
Any ideas?
Roger Wolter[MSFT] wrote:[vbcol=seagreen]
> I'm not sure if it's a typo but the way to run a command on a different
> server is to use the -S parameter. -s sets the column separator which is
> probably not what you want.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> <orencs@.gmail.com> wrote in message
> news:1139142162.951389.181080@.o13g2000cwo.googlegr oups.com...
|||Are you sure that 100.100.100.11 is in fact the remote computer and not the
local one? You might want to start with a simpler and safer test script
until you know it is talking to the right server. Maybe just select
@.@.SERVERNAME?
Andrew J. Kelly SQL MVP
<orencs@.gmail.com> wrote in message
news:1139294796.871063.293590@.f14g2000cwb.googlegr oups.com...
> Sorry, my mistake for not providing enough information.
> I am running the following:
> A Batch file
> osql -U sa -P poqi -i "1install.sql" -o "install.res"
>
> The install.sql file looks like this:
> IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name =
> N'MyDB')
> DROP DATABASE [MyDB]
> GO
> CREATE DATABASE [MyDB]
> GO
> use [MyDB]
> GO
> ...Then I create the tables SP etc...
> The above works great when I want to create the database on my
> computer.
> My problem is that I need to run the scripts in PC A and install the
> database on PC B.
> The batch and sql file are in PC A.
> I don't know how to do that.
> I have tried the -s switch for the OSQL command.
> I have change the batch file like this:
> osql -S 100.100.100.11 -U sa -P poqi -i "1install.sql" -o
> "install.res"
> 100.100.100.11 - is the IP of PC B.
> I don't receive any error.
> The install.res file indicate that everything went well and in fact it
> did only the OSQL command disregard the -s switch and create the DB on
> PC A.
> Any ideas?
>
>
>
>
>
> Roger Wolter[MSFT] wrote:
>
|||HI Andrew
yes I am sure this is the correct server IP.
It looks like the -s swtich does not work
Oren.
|||I don't know of any bugs with the -S. I (and many others) have been using
this for years without a problem like this that I know of. Is there any
chance that EM is pointing to the wrong machine? Maybe oSql is creating it
on the correct machine and EM is pointing to that one when you think it is
pointing to the other? If not I am not sure what to tell you as the -S
should work. Again as Roger stated it must be a Capital S. You just listed
it as a lower case s.
Andrew J. Kelly SQL MVP
<orencs@.gmail.com> wrote in message
news:1139401703.086471.286520@.g44g2000cwa.googlegr oups.com...
> HI Andrew
> yes I am sure this is the correct server IP.
> It looks like the -s swtich does not work
> Oren.
>

How to create database on remote machine using osql

Hi All,
I am using OSQL to create database.
When I am using it to create the database localy it works great when I
use the -s switch (target manchine) the call ended successfuly only the
database is created om my PC and not on the remote one.
Does anyone know why?
Thanks in advance OrenWhat is your call, that should work also remotely ?
HTH, Jens Suessmeyer.|||Do you have proper permissions on the remote machine? Can you post the oSql
call?
Andrew J. Kelly SQL MVP
<orencs@.gmail.com> wrote in message
news:1139142162.951389.181080@.o13g2000cwo.googlegroups.com...
> Hi All,
> I am using OSQL to create database.
> When I am using it to create the database localy it works great when I
> use the -s switch (target manchine) the call ended successfuly only the
> database is created om my PC and not on the remote one.
> Does anyone know why?
>
> Thanks in advance Oren
>|||I'm not sure if it's a typo but the way to run a command on a different
server is to use the -S parameter. -s sets the column separator which is
probably not what you want.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<orencs@.gmail.com> wrote in message
news:1139142162.951389.181080@.o13g2000cwo.googlegroups.com...
> Hi All,
> I am using OSQL to create database.
> When I am using it to create the database localy it works great when I
> use the -s switch (target manchine) the call ended successfuly only the
> database is created om my PC and not on the remote one.
> Does anyone know why?
>
> Thanks in advance Oren
>|||Sorry, my mistake for not providing enough information.
I am running the following:
A Batch file
osql -U sa -P poqi -i "1install.sql" -o "install.res"
The install.sql file looks like this:
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name =
N'MyDB')
DROP DATABASE [MyDB]
GO
CREATE DATABASE [MyDB]
GO
use [MyDB]
GO
...Then I create the tables SP etc...
The above works great when I want to create the database on my
computer.
My problem is that I need to run the scripts in PC A and install the
database on PC B.
The batch and sql file are in PC A.
I don't know how to do that.
I have tried the -s switch for the OSQL command.
I have change the batch file like this:
osql -S 100.100.100.11 -U sa -P poqi -i "1install.sql" -o
"install.res"
100.100.100.11 - is the IP of PC B.
I don't receive any error.
The install.res file indicate that everything went well and in fact it
did only the OSQL command disregard the -s switch and create the DB on
PC A.
Any ideas?
Roger Wolter[MSFT] wrote:[vbcol=seagreen]
> I'm not sure if it's a typo but the way to run a command on a different
> server is to use the -S parameter. -s sets the column separator which is
> probably not what you want.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> <orencs@.gmail.com> wrote in message
> news:1139142162.951389.181080@.o13g2000cwo.googlegroups.com...|||Are you sure that 100.100.100.11 is in fact the remote computer and not the
local one? You might want to start with a simpler and safer test script
until you know it is talking to the right server. Maybe just select
@.@.SERVERNAME?
Andrew J. Kelly SQL MVP
<orencs@.gmail.com> wrote in message
news:1139294796.871063.293590@.f14g2000cwb.googlegroups.com...
> Sorry, my mistake for not providing enough information.
> I am running the following:
> A Batch file
> osql -U sa -P poqi -i "1install.sql" -o "install.res"
>
> The install.sql file looks like this:
> IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name =
> N'MyDB')
> DROP DATABASE [MyDB]
> GO
> CREATE DATABASE [MyDB]
> GO
> use [MyDB]
> GO
> ...Then I create the tables SP etc...
> The above works great when I want to create the database on my
> computer.
> My problem is that I need to run the scripts in PC A and install the
> database on PC B.
> The batch and sql file are in PC A.
> I don't know how to do that.
> I have tried the -s switch for the OSQL command.
> I have change the batch file like this:
> osql -S 100.100.100.11 -U sa -P poqi -i "1install.sql" -o
> "install.res"
> 100.100.100.11 - is the IP of PC B.
> I don't receive any error.
> The install.res file indicate that everything went well and in fact it
> did only the OSQL command disregard the -s switch and create the DB on
> PC A.
> Any ideas?
>
>
>
>
>
> Roger Wolter[MSFT] wrote:
>|||HI Andrew
yes I am sure this is the correct server IP.
It looks like the -s swtich does not work
Oren.|||I don't know of any bugs with the -S. I (and many others) have been using
this for years without a problem like this that I know of. Is there any
chance that EM is pointing to the wrong machine? Maybe oSql is creating it
on the correct machine and EM is pointing to that one when you think it is
pointing to the other? If not I am not sure what to tell you as the -S
should work. Again as Roger stated it must be a Capital S. You just listed
it as a lower case s.
Andrew J. Kelly SQL MVP
<orencs@.gmail.com> wrote in message
news:1139401703.086471.286520@.g44g2000cwa.googlegroups.com...
> HI Andrew
> yes I am sure this is the correct server IP.
> It looks like the -s swtich does not work
> Oren.
>sql

How to create a new SQL2005 DB?

Hi folks,

i have VSTS installed and TFS beta refresh 3. all is working fine.

How can i use Visual Studio 2005 to create a new database on the remote server? i can connecto to it fine using the 'server explorer' tab .. but of course it only lists databases that currently exist. How can i create a new one?

lastly, i have no installed sql2005 express edition or whatever it is called LOCALLY on my machine. the reason i didn't do this is becuase i was hoping to use the enterprise sql 2005 server.

If your are using the SQLExpress version best thing to do is download and install the SQLExpress Management Studio CTP from microsoft. It basically gives you the same functionality that is in the full SQL2005 versions.

After running the studio and connecting to the database engine you can navigate to the databases node, right click, new, Database then begin configuring all your tables, constraints and security.

The management studio CTP can be had herehttp://www.microsoft.com/downloads/details.aspx?familyid=82AFBD59-57A4-455E-A2D6-1D4C98D40F6E&displaylang=en

|||

Ahh! so this Management Studio is the equivalent to the SQL CLIENT TOOLS for our sql server 2000's?

is there a Management Studio for sql2005? is it found on the sql2005 cd or do we download it?

|||The SQL Management Studio is installed by default in all versions of SQL2005, Eval, Std, Dev and Enterprise. it can be found in Start--All Programs--Microsoft SQL Server 2005--SQL Server Management Studio. The CTP version can be used to connect to any SQL2005 instance on the network. Just choose the Server/Instance from the drop down list in the Connect to Database engine dialogue or manually type the server/instance name.|||

ahh, interesting.

i installed Team Foundation Server beta refresh 3. Part of that is the SQL2005 install. I installed my SQL2005 version using the MS provided UNATTENDED INSTALL file. This file must have excluded the management studio! damn :(

that said, what about clients trying to manage a sql server, where the sqlserver is remote. Are there client tools on the SQL2005 CD?

How to copy tables from local machine to remote SQL server

We currently have a PPTP connection set up for our developers to
access our development SQL server through a VPN tunnel. When they
need to copy tables up to the dev SQL from their local machine they
simply do a DTS copy.

However, we are now moving to a thin client solution where they will
be working on a terminal server. They will have access to the
development SQL servers and SQL tools such as EM and QA. However,
they will not have access to their local SQL server and, therefore,
will not be able to directly perform DTS copies. We have explored
several possibilities such as exporting tables to a .csv or .mdb file
and then importing them on the development SQL server but this is not
ideal because things are lost in that process (e.g. primary keys,
field names, data types, etc.)

My question is this: Is there a way to export and then import SQL
tables without losing dependent objects such as primary keys and data
types in the process? If any of you are working with a similar
situation I would really like to hear how your remote users copy
objects from their remote location to your SQL servers. Thanks!

Ryan

--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/General-Dis...pict211310.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=722630I'm not sure how your developers will get their files to the server
(FTP?), but in any case they can easily create object definition
scripts from Enterprise Manager or Query Analyzer.

Assuming they're maintaining their SQL code in a source control system,
then they will be creating these scripts anyway. So they could recreate
(or perhaps just ALTER) a table with a script, then reload the data
from a csv file. Or perhaps make a copy of the original table, modify
it, insert the data from the copy, and then drop the copy.

If this doesn't help, you might want to clarify what the issue is -
problems creating scripts, running them etc.

Simon|||"Simon Hayes" wrote:
> I'm not sure how your developers will get their files to the
> server
> (FTP?), but in any case they can easily create object
> definition
> scripts from Enterprise Manager or Query Analyzer.
> Assuming they're maintaining their SQL code in a source
> control system,
> then they will be creating these scripts anyway. So they could
> recreate
> (or perhaps just ALTER) a table with a script, then reload the
> data
> from a csv file. Or perhaps make a copy of the original table,
> modify
> it, insert the data from the copy, and then drop the copy.
> If this doesn't help, you might want to clarify what the issue
> is -
> problems creating scripts, running them etc.
> Simon

Thanks for your reply.

The developers can copy files from there local hard drives via their
terminal server session. The problem is that they cannot use DTS to
copy SQL objects because there are firewalls between their local
machine and the terminal server.

It would be possible for them to script the table in EM and then use
bcp to insert the data but I was hoping for a simpler solution with
fewer steps involved. Some of the developers are not so SQL saavy and
Im afraid that we will get stuck supporting them. The extra time
spent on these activities will also give them an excuse to bill us for
more hours (they are contractors).

I have been testing the export of data to .csv and .mdb files and it
is not always straightforward to do. If anyone has any experience
with this your advice is very welcome. Thanks!

--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/General-Dis...pict211310.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=723562|||Here's a workaround solution if you'd like.

1. Create a backup of the database you want to send to the SQL Server.
Let's say your DBName is Performer.
2. Restore as another database...Performer1
3. Delete the tables/other objects you don't want to move up.
4. Do another backup of Performer1...maybe into Performer1.bak
5. Upload it to Production.
6. Restore as Performer1.
7. DTS the tables over or if you simply need to move the data, DTS only
the data to the existing tables.

...and 8. Good luck :o)

Hope this helps,

-Umar Farooq.|||1.DTS out to disk the table(s) you need (using DTS for this is much less
prone to error than BCP)
2. Upload files to production
3. DTS Back in (once again DTS is easier than BCP)

To avoid foreign key confilicts you need to DTS any parent data in before
child data (if you need to move new tables you will need to script and copy
them and recreate at other end before loading data)

You can make the task completely automatic by doing steps 1 and 2 in a DTS
package locally, then do step 3 on production and pole for the files, only
upload when they are there an then delete them (or move them) when the
import is complete.

This will mean that your contractors do nothing or just initiate an automate
process.

Hope this helps
Julian

"Umar Farooq" <UmarAlFarooq@.gmail.com> wrote in message
news:1112753008.047280.36140@.o13g2000cwo.googlegro ups.com...
> Here's a workaround solution if you'd like.
> 1. Create a backup of the database you want to send to the SQL Server.
> Let's say your DBName is Performer.
> 2. Restore as another database...Performer1
> 3. Delete the tables/other objects you don't want to move up.
> 4. Do another backup of Performer1...maybe into Performer1.bak
> 5. Upload it to Production.
> 6. Restore as Performer1.
> 7. DTS the tables over or if you simply need to move the data, DTS only
> the data to the existing tables.
> ...and 8. Good luck :o)
> Hope this helps,
> -Umar Farooq.

How to copy sql express tables & stored procedures into remote full sql server 2005

Hi all,

I am using Visual web developper 2005 with sql server express 2005 and i have also sql server management studio express. it's all free now .

my web site is ready

I didn't have problem to upload my site to my hoster.

Now I want to upload all my tables and my stored procedure create locally with VWD express

How can i do it ?

NB: I know i can't design DB (create/modify tables and stored proc) with express edition

thank's for your help

Hi all,

I have my all tables and stored procedures on the remote server and my site is working well but it was very hard to.

let me resume SSMSE steps for tables:

1. Connect to local server

2. Connect to remote server

3. Create Sql file by using "Script out Create " command on the local table

4. Change connection from local to remote

5. execute your sq file to create table on the remote DB

6. Open table on remote DB

7. Open table on local DB

8. use copy/paste to copy rows from local to remote

you must do it for all table including aspnet tables when you use features like menbership, roles profiles and personalization

for stored procedures no steps 6 7 8 and you must change the name of the databases on thesql file

How to copy local sql express DB to remote full sql server 2005

Hi guys,

I am using Visual wev developper 2005 with sql server express 2005 and i have also sql server management studio express. it's all free now .

my web site is ready

I didn't have problem to upload my site to my hoster.

Now I want to upload all my tables and my stored procedure create locally with VWD express

How can i do it ?

thank's for your help

Hi all,

I have my all table on the remote server but it was very hard to.

let me resume SSMSE steps:

1. Connect to local server

2. Connect to remote server

3. Create Sql file by using "Script out Create " command on the local table

4. Change connection from local to remote

5. execute your sq file to create table on the remote DB

6. Open table on remote DB

7. Open table on local DB

8. use copy/paste to copy rows from local to remote

you must do it for all table including aspnet tables when you use features like menbership, roles profiles and personalization