Hi
Is there a way for duplicating tables in two databases?
I have a db1 with 3 tables, and I would like to create 3 similar tables in
my db2 (same structure).
Is there a tool to make this easy?The easiest way, IMO, is just to script the tables using Query Analyzer and
create them in the new database. Right-click on the table in the Object
Browser, select Script Object to New Window As, and click Create. Now just
change databases and apply the script.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Gonzalo Torres" <condormix2001@.yahoo.com.mx> wrote in message
news:%23pD0Ogc8EHA.3476@.TK2MSFTNGP15.phx.gbl...
> Hi
> Is there a way for duplicating tables in two databases?
> I have a db1 with 3 tables, and I would like to create 3 similar tables in
> my db2 (same structure).
> Is there a tool to make this easy?
>|||If you wish to include indexes, constraints, triggers, etc, use SQL
Enterprise Manager, Right Click your database ->All tasks_>Generate SQL
Script
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Gonzalo Torres" <condormix2001@.yahoo.com.mx> wrote in message
news:%23pD0Ogc8EHA.3476@.TK2MSFTNGP15.phx.gbl...
> Hi
> Is there a way for duplicating tables in two databases?
> I have a db1 with 3 tables, and I would like to create 3 similar tables in
> my db2 (same structure).
> Is there a tool to make this easy?
>|||Hi Gonzalo,
Simplest way is using SELECT INTO (check BOL). This will create table
(without index, constraints, foreign keys...) with the data.
Other methods are:
(a) Using BCP tool
(b) Script the table, apply it and then use INSERT INTO
--
Thanks
GYK
Showing posts with label databases. Show all posts
Showing posts with label databases. Show all posts
Thursday, March 29, 2012
Wednesday, March 21, 2012
Dumping SQL Server Variables
In Oracle and other databases there is a way to dump all the values that hav
e
been set using the SET command. For example, SET ROWCOUNT.
Is there a way to do this in SQL Server?
The reason I ask is because somewhere in a stream of some 1000 sql files
someone using some SET parameters that are scewing up some down stream files
.
In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a select
insert from one table into another and only 1995 rows are being inserted whe
n
we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
want to find out where along the way things are getting screwed up.
Searching for SET ROWCOUNT has not yielded any result.
What I would like to do is dump all the SET variables to a file or screen or
someplace before the problem file runs.
As an FYI, the files are all being executed via SQL-DMO but I do not believe
there any internal SQL-DMO limitationsI don't think there's a way to get this value - it's a property of the
session that doesn't seem to be stored in any table. You can trace the
workload with SQL Profiler and look for SET ROWCOUNT.
Steve Kass
Drew University
enzo_maini@.dotnetfan.net wrote:
>In Oracle and other databases there is a way to dump all the values that ha
ve
>been set using the SET command. For example, SET ROWCOUNT.
>Is there a way to do this in SQL Server?
>The reason I ask is because somewhere in a stream of some 1000 sql files
>someone using some SET parameters that are scewing up some down stream file
s.
> In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a sele
ct
>insert from one table into another and only 1995 rows are being inserted wh
en
>we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
>want to find out where along the way things are getting screwed up.
>Searching for SET ROWCOUNT has not yielded any result.
>What I would like to do is dump all the SET variables to a file or screen o
r
>someplace before the problem file runs.
>As an FYI, the files are all being executed via SQL-DMO but I do not believ
e
>there any internal SQL-DMO limitations
>|||Hi Enzo
DBCC USEROPTIONS will show you the settings (including the SET ROWCOUNT
value) for the current connection.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"enzo_maini@.dotnetfan.net"
<enzo_maini@.dotnetfan.net@.discussions.microsoft.com> wrote in message
news:074E8B77-B9A9-4959-A962-CD09660B7793@.microsoft.com...
> In Oracle and other databases there is a way to dump all the values that
> have
> been set using the SET command. For example, SET ROWCOUNT.
> Is there a way to do this in SQL Server?
> The reason I ask is because somewhere in a stream of some 1000 sql files
> someone using some SET parameters that are scewing up some down stream
> files.
> In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a
> select
> insert from one table into another and only 1995 rows are being inserted
> when
> we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
> want to find out where along the way things are getting screwed up.
> Searching for SET ROWCOUNT has not yielded any result.
> What I would like to do is dump all the SET variables to a file or screen
> or
> someplace before the problem file runs.
> As an FYI, the files are all being executed via SQL-DMO but I do not
> believe
> there any internal SQL-DMO limitations|||Thanks for the correction, Kalen!
SK
Kalen Delaney wrote:
>Hi Enzo
>DBCC USEROPTIONS will show you the settings (including the SET ROWCOUNT
>value) for the current connection.
>
>sql
e
been set using the SET command. For example, SET ROWCOUNT.
Is there a way to do this in SQL Server?
The reason I ask is because somewhere in a stream of some 1000 sql files
someone using some SET parameters that are scewing up some down stream files
.
In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a select
insert from one table into another and only 1995 rows are being inserted whe
n
we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
want to find out where along the way things are getting screwed up.
Searching for SET ROWCOUNT has not yielded any result.
What I would like to do is dump all the SET variables to a file or screen or
someplace before the problem file runs.
As an FYI, the files are all being executed via SQL-DMO but I do not believe
there any internal SQL-DMO limitationsI don't think there's a way to get this value - it's a property of the
session that doesn't seem to be stored in any table. You can trace the
workload with SQL Profiler and look for SET ROWCOUNT.
Steve Kass
Drew University
enzo_maini@.dotnetfan.net wrote:
>In Oracle and other databases there is a way to dump all the values that ha
ve
>been set using the SET command. For example, SET ROWCOUNT.
>Is there a way to do this in SQL Server?
>The reason I ask is because somewhere in a stream of some 1000 sql files
>someone using some SET parameters that are scewing up some down stream file
s.
> In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a sele
ct
>insert from one table into another and only 1995 rows are being inserted wh
en
>we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
>want to find out where along the way things are getting screwed up.
>Searching for SET ROWCOUNT has not yielded any result.
>What I would like to do is dump all the SET variables to a file or screen o
r
>someplace before the problem file runs.
>As an FYI, the files are all being executed via SQL-DMO but I do not believ
e
>there any internal SQL-DMO limitations
>|||Hi Enzo
DBCC USEROPTIONS will show you the settings (including the SET ROWCOUNT
value) for the current connection.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"enzo_maini@.dotnetfan.net"
<enzo_maini@.dotnetfan.net@.discussions.microsoft.com> wrote in message
news:074E8B77-B9A9-4959-A962-CD09660B7793@.microsoft.com...
> In Oracle and other databases there is a way to dump all the values that
> have
> been set using the SET command. For example, SET ROWCOUNT.
> Is there a way to do this in SQL Server?
> The reason I ask is because somewhere in a stream of some 1000 sql files
> someone using some SET parameters that are scewing up some down stream
> files.
> In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a
> select
> insert from one table into another and only 1995 rows are being inserted
> when
> we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
> want to find out where along the way things are getting screwed up.
> Searching for SET ROWCOUNT has not yielded any result.
> What I would like to do is dump all the SET variables to a file or screen
> or
> someplace before the problem file runs.
> As an FYI, the files are all being executed via SQL-DMO but I do not
> believe
> there any internal SQL-DMO limitations|||Thanks for the correction, Kalen!
SK
Kalen Delaney wrote:
>Hi Enzo
>DBCC USEROPTIONS will show you the settings (including the SET ROWCOUNT
>value) for the current connection.
>
>sql
Dumping SQL Server Variables
In Oracle and other databases there is a way to dump all the values that have
been set using the SET command. For example, SET ROWCOUNT.
Is there a way to do this in SQL Server?
The reason I ask is because somewhere in a stream of some 1000 sql files
someone using some SET parameters that are scewing up some down stream files.
In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a select
insert from one table into another and only 1995 rows are being inserted when
we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
want to find out where along the way things are getting screwed up.
Searching for SET ROWCOUNT has not yielded any result.
What I would like to do is dump all the SET variables to a file or screen or
someplace before the problem file runs.
As an FYI, the files are all being executed via SQL-DMO but I do not believe
there any internal SQL-DMO limitations
I don't think there's a way to get this value - it's a property of the
session that doesn't seem to be stored in any table. You can trace the
workload with SQL Profiler and look for SET ROWCOUNT.
Steve Kass
Drew University
enzo_maini@.dotnetfan.net wrote:
>In Oracle and other databases there is a way to dump all the values that have
>been set using the SET command. For example, SET ROWCOUNT.
>Is there a way to do this in SQL Server?
>The reason I ask is because somewhere in a stream of some 1000 sql files
>someone using some SET parameters that are scewing up some down stream files.
> In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a select
>insert from one table into another and only 1995 rows are being inserted when
>we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
>want to find out where along the way things are getting screwed up.
>Searching for SET ROWCOUNT has not yielded any result.
>What I would like to do is dump all the SET variables to a file or screen or
>someplace before the problem file runs.
>As an FYI, the files are all being executed via SQL-DMO but I do not believe
>there any internal SQL-DMO limitations
>
|||Hi Enzo
DBCC USEROPTIONS will show you the settings (including the SET ROWCOUNT
value) for the current connection.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"enzo_maini@.dotnetfan.net"
<enzo_maini@.dotnetfan.net@.discussions.microsoft.co m> wrote in message
news:074E8B77-B9A9-4959-A962-CD09660B7793@.microsoft.com...
> In Oracle and other databases there is a way to dump all the values that
> have
> been set using the SET command. For example, SET ROWCOUNT.
> Is there a way to do this in SQL Server?
> The reason I ask is because somewhere in a stream of some 1000 sql files
> someone using some SET parameters that are scewing up some down stream
> files.
> In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a
> select
> insert from one table into another and only 1995 rows are being inserted
> when
> we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
> want to find out where along the way things are getting screwed up.
> Searching for SET ROWCOUNT has not yielded any result.
> What I would like to do is dump all the SET variables to a file or screen
> or
> someplace before the problem file runs.
> As an FYI, the files are all being executed via SQL-DMO but I do not
> believe
> there any internal SQL-DMO limitations
|||Thanks for the correction, Kalen!
SK
Kalen Delaney wrote:
>Hi Enzo
>DBCC USEROPTIONS will show you the settings (including the SET ROWCOUNT
>value) for the current connection.
>
>
been set using the SET command. For example, SET ROWCOUNT.
Is there a way to do this in SQL Server?
The reason I ask is because somewhere in a stream of some 1000 sql files
someone using some SET parameters that are scewing up some down stream files.
In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a select
insert from one table into another and only 1995 rows are being inserted when
we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
want to find out where along the way things are getting screwed up.
Searching for SET ROWCOUNT has not yielded any result.
What I would like to do is dump all the SET variables to a file or screen or
someplace before the problem file runs.
As an FYI, the files are all being executed via SQL-DMO but I do not believe
there any internal SQL-DMO limitations
I don't think there's a way to get this value - it's a property of the
session that doesn't seem to be stored in any table. You can trace the
workload with SQL Profiler and look for SET ROWCOUNT.
Steve Kass
Drew University
enzo_maini@.dotnetfan.net wrote:
>In Oracle and other databases there is a way to dump all the values that have
>been set using the SET command. For example, SET ROWCOUNT.
>Is there a way to do this in SQL Server?
>The reason I ask is because somewhere in a stream of some 1000 sql files
>someone using some SET parameters that are scewing up some down stream files.
> In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a select
>insert from one table into another and only 1995 rows are being inserted when
>we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
>want to find out where along the way things are getting screwed up.
>Searching for SET ROWCOUNT has not yielded any result.
>What I would like to do is dump all the SET variables to a file or screen or
>someplace before the problem file runs.
>As an FYI, the files are all being executed via SQL-DMO but I do not believe
>there any internal SQL-DMO limitations
>
|||Hi Enzo
DBCC USEROPTIONS will show you the settings (including the SET ROWCOUNT
value) for the current connection.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"enzo_maini@.dotnetfan.net"
<enzo_maini@.dotnetfan.net@.discussions.microsoft.co m> wrote in message
news:074E8B77-B9A9-4959-A962-CD09660B7793@.microsoft.com...
> In Oracle and other databases there is a way to dump all the values that
> have
> been set using the SET command. For example, SET ROWCOUNT.
> Is there a way to do this in SQL Server?
> The reason I ask is because somewhere in a stream of some 1000 sql files
> someone using some SET parameters that are scewing up some down stream
> files.
> In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a
> select
> insert from one table into another and only 1995 rows are being inserted
> when
> we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
> want to find out where along the way things are getting screwed up.
> Searching for SET ROWCOUNT has not yielded any result.
> What I would like to do is dump all the SET variables to a file or screen
> or
> someplace before the problem file runs.
> As an FYI, the files are all being executed via SQL-DMO but I do not
> believe
> there any internal SQL-DMO limitations
|||Thanks for the correction, Kalen!
SK
Kalen Delaney wrote:
>Hi Enzo
>DBCC USEROPTIONS will show you the settings (including the SET ROWCOUNT
>value) for the current connection.
>
>
Dumping SQL Server Variables
In Oracle and other databases there is a way to dump all the values that have
been set using the SET command. For example, SET ROWCOUNT.
Is there a way to do this in SQL Server?
The reason I ask is because somewhere in a stream of some 1000 sql files
someone using some SET parameters that are scewing up some down stream files.
In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a select
insert from one table into another and only 1995 rows are being inserted when
we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
want to find out where along the way things are getting screwed up.
Searching for SET ROWCOUNT has not yielded any result.
What I would like to do is dump all the SET variables to a file or screen or
someplace before the problem file runs.
As an FYI, the files are all being executed via SQL-DMO but I do not believe
there any internal SQL-DMO limitationsI don't think there's a way to get this value - it's a property of the
session that doesn't seem to be stored in any table. You can trace the
workload with SQL Profiler and look for SET ROWCOUNT.
Steve Kass
Drew University
enzo_maini@.dotnetfan.net wrote:
>In Oracle and other databases there is a way to dump all the values that have
>been set using the SET command. For example, SET ROWCOUNT.
>Is there a way to do this in SQL Server?
>The reason I ask is because somewhere in a stream of some 1000 sql files
>someone using some SET parameters that are scewing up some down stream files.
> In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a select
>insert from one table into another and only 1995 rows are being inserted when
>we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
>want to find out where along the way things are getting screwed up.
>Searching for SET ROWCOUNT has not yielded any result.
>What I would like to do is dump all the SET variables to a file or screen or
>someplace before the problem file runs.
>As an FYI, the files are all being executed via SQL-DMO but I do not believe
>there any internal SQL-DMO limitations
>|||Hi Enzo
DBCC USEROPTIONS will show you the settings (including the SET ROWCOUNT
value) for the current connection.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"enzo_maini@.dotnetfan.net"
<enzo_maini@.dotnetfan.net@.discussions.microsoft.com> wrote in message
news:074E8B77-B9A9-4959-A962-CD09660B7793@.microsoft.com...
> In Oracle and other databases there is a way to dump all the values that
> have
> been set using the SET command. For example, SET ROWCOUNT.
> Is there a way to do this in SQL Server?
> The reason I ask is because somewhere in a stream of some 1000 sql files
> someone using some SET parameters that are scewing up some down stream
> files.
> In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a
> select
> insert from one table into another and only 1995 rows are being inserted
> when
> we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
> want to find out where along the way things are getting screwed up.
> Searching for SET ROWCOUNT has not yielded any result.
> What I would like to do is dump all the SET variables to a file or screen
> or
> someplace before the problem file runs.
> As an FYI, the files are all being executed via SQL-DMO but I do not
> believe
> there any internal SQL-DMO limitations|||Thanks for the correction, Kalen!
SK
Kalen Delaney wrote:
>Hi Enzo
>DBCC USEROPTIONS will show you the settings (including the SET ROWCOUNT
>value) for the current connection.
>
>
been set using the SET command. For example, SET ROWCOUNT.
Is there a way to do this in SQL Server?
The reason I ask is because somewhere in a stream of some 1000 sql files
someone using some SET parameters that are scewing up some down stream files.
In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a select
insert from one table into another and only 1995 rows are being inserted when
we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
want to find out where along the way things are getting screwed up.
Searching for SET ROWCOUNT has not yielded any result.
What I would like to do is dump all the SET variables to a file or screen or
someplace before the problem file runs.
As an FYI, the files are all being executed via SQL-DMO but I do not believe
there any internal SQL-DMO limitationsI don't think there's a way to get this value - it's a property of the
session that doesn't seem to be stored in any table. You can trace the
workload with SQL Profiler and look for SET ROWCOUNT.
Steve Kass
Drew University
enzo_maini@.dotnetfan.net wrote:
>In Oracle and other databases there is a way to dump all the values that have
>been set using the SET command. For example, SET ROWCOUNT.
>Is there a way to do this in SQL Server?
>The reason I ask is because somewhere in a stream of some 1000 sql files
>someone using some SET parameters that are scewing up some down stream files.
> In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a select
>insert from one table into another and only 1995 rows are being inserted when
>we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
>want to find out where along the way things are getting screwed up.
>Searching for SET ROWCOUNT has not yielded any result.
>What I would like to do is dump all the SET variables to a file or screen or
>someplace before the problem file runs.
>As an FYI, the files are all being executed via SQL-DMO but I do not believe
>there any internal SQL-DMO limitations
>|||Hi Enzo
DBCC USEROPTIONS will show you the settings (including the SET ROWCOUNT
value) for the current connection.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"enzo_maini@.dotnetfan.net"
<enzo_maini@.dotnetfan.net@.discussions.microsoft.com> wrote in message
news:074E8B77-B9A9-4959-A962-CD09660B7793@.microsoft.com...
> In Oracle and other databases there is a way to dump all the values that
> have
> been set using the SET command. For example, SET ROWCOUNT.
> Is there a way to do this in SQL Server?
> The reason I ask is because somewhere in a stream of some 1000 sql files
> someone using some SET parameters that are scewing up some down stream
> files.
> In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a
> select
> insert from one table into another and only 1995 rows are being inserted
> when
> we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
> want to find out where along the way things are getting screwed up.
> Searching for SET ROWCOUNT has not yielded any result.
> What I would like to do is dump all the SET variables to a file or screen
> or
> someplace before the problem file runs.
> As an FYI, the files are all being executed via SQL-DMO but I do not
> believe
> there any internal SQL-DMO limitations|||Thanks for the correction, Kalen!
SK
Kalen Delaney wrote:
>Hi Enzo
>DBCC USEROPTIONS will show you the settings (including the SET ROWCOUNT
>value) for the current connection.
>
>
Dumping all SQL queries.
Hi - is it possible to dump the query text of every query run
against a particular database, or all databases, in an SQL Server
2000 installation? We've an issue whereby a 3rd. party application
running queries over ODBC sometimes claims that gobbledegook is
returned, and it would be very useful to identify the query that's
being claimed is the problem. I suspect that it's an application
issue rather than a SQL Server one.
against a particular database, or all databases, in an SQL Server
2000 installation? We've an issue whereby a 3rd. party application
running queries over ODBC sometimes claims that gobbledegook is
returned, and it would be very useful to identify the query that's
being claimed is the problem. I suspect that it's an application
issue rather than a SQL Server one.
__________________________________________________ ___________
Are you Catholic ?
http://www.CatholicEmail.com
100s of FREE email addresses -->
http://www.UltimateEmail.com
Send an Online Greeting Card http://www.UltimateEcards.com"sqlserver yeahbaby" <Use-Author-Address-Header@.[127.1]> wrote in message
news:20031104161149.53F073965@.sitemail.everyone.ne t...
> Hi - is it possible to dump the query text of every query run
> against a particular database, or all databases, in an SQL Server
> 2000 installation? We've an issue whereby a 3rd. party application
> running queries over ODBC sometimes claims that gobbledegook is
> returned, and it would be very useful to identify the query that's
> being claimed is the problem. I suspect that it's an application
> issue rather than a SQL Server one.
Look at profiler.
> __________________________________________________ ___________
> Are you Catholic ?
> http://www.CatholicEmail.com
> 100s of FREE email addresses -->
> http://www.UltimateEmail.com
> Send an Online Greeting Card http://www.UltimateEcards.com
Sunday, March 11, 2012
Dumb Question
This is a dumb question but I need to ask. Does SQL Server
2000 MSDE version comes with 'pubs' and 'Northwind'
databases '
Thanks.Hi Dan,
No, They will not be installed on setup. You can get the install scripts
from C:\Program Files\Microsoft SQL Server\MSSQL\Install.
They file names are:
instpubs.sql
instnwnd.sql
Otherwise get the latest scripts from microsoft site
http://www.microsoft.com/downloads/...&displaylang=en
Use OSQL frommd prompt to execute the script to create database and objects.
Thanks
Hari
SQL Server MVP
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:113c01c53c39$182a3970$a401280a@.phx.gbl...
> This is a dumb question but I need to ask. Does SQL Server
> 2000 MSDE version comes with 'pubs' and 'Northwind'
> databases '
> Thanks.|||Thanks.........
>--Original Message--
>Hi Dan,
>No, They will not be installed on setup. You can get the
install scripts
>from C:\Program Files\Microsoft SQL Server\MSSQL\Install.
>They file names are:
>instpubs.sql
>instnwnd.sql
>Otherwise get the latest scripts from microsoft site
>http://www.microsoft.com/downloads/details.aspx?
FamilyId=06616212-0356-46A0-8DA2-
EEBC53A68034&displaylang=en
>Use OSQL frommd prompt to execute the script to create
database and objects.
>Thanks
>Hari
>SQL Server MVP
>
>"Dan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:113c01c53c39$182a3970$a401280a@.phx.gbl...
Server[vbcol=seagreen]
>
>.
>
2000 MSDE version comes with 'pubs' and 'Northwind'
databases '
Thanks.Hi Dan,
No, They will not be installed on setup. You can get the install scripts
from C:\Program Files\Microsoft SQL Server\MSSQL\Install.
They file names are:
instpubs.sql
instnwnd.sql
Otherwise get the latest scripts from microsoft site
http://www.microsoft.com/downloads/...&displaylang=en
Use OSQL frommd prompt to execute the script to create database and objects.
Thanks
Hari
SQL Server MVP
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:113c01c53c39$182a3970$a401280a@.phx.gbl...
> This is a dumb question but I need to ask. Does SQL Server
> 2000 MSDE version comes with 'pubs' and 'Northwind'
> databases '
> Thanks.|||Thanks.........
>--Original Message--
>Hi Dan,
>No, They will not be installed on setup. You can get the
install scripts
>from C:\Program Files\Microsoft SQL Server\MSSQL\Install.
>They file names are:
>instpubs.sql
>instnwnd.sql
>Otherwise get the latest scripts from microsoft site
>http://www.microsoft.com/downloads/details.aspx?
FamilyId=06616212-0356-46A0-8DA2-
EEBC53A68034&displaylang=en
>Use OSQL frommd prompt to execute the script to create
database and objects.
>Thanks
>Hari
>SQL Server MVP
>
>"Dan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:113c01c53c39$182a3970$a401280a@.phx.gbl...
Server[vbcol=seagreen]
>
>.
>
Dumb Question
This is a dumb question but I need to ask. Does SQL Server
2000 MSDE version comes with 'pubs' and 'Northwind'
databases '
Thanks.Hi Dan,
No, They will not be installed on setup. You can get the install scripts
from C:\Program Files\Microsoft SQL Server\MSSQL\Install.
They file names are:
instpubs.sql
instnwnd.sql
Otherwise get the latest scripts from microsoft site
http://www.microsoft.com/downloads/details.aspx?FamilyId=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en
Use OSQL frommd prompt to execute the script to create database and objects.
Thanks
Hari
SQL Server MVP
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:113c01c53c39$182a3970$a401280a@.phx.gbl...
> This is a dumb question but I need to ask. Does SQL Server
> 2000 MSDE version comes with 'pubs' and 'Northwind'
> databases '
> Thanks.|||Thanks.........
>--Original Message--
>Hi Dan,
>No, They will not be installed on setup. You can get the
install scripts
>from C:\Program Files\Microsoft SQL Server\MSSQL\Install.
>They file names are:
>instpubs.sql
>instnwnd.sql
>Otherwise get the latest scripts from microsoft site
>http://www.microsoft.com/downloads/details.aspx?
FamilyId=06616212-0356-46A0-8DA2-
EEBC53A68034&displaylang=en
>Use OSQL frommd prompt to execute the script to create
database and objects.
>Thanks
>Hari
>SQL Server MVP
>
>"Dan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:113c01c53c39$182a3970$a401280a@.phx.gbl...
>> This is a dumb question but I need to ask. Does SQL
Server
>> 2000 MSDE version comes with 'pubs' and 'Northwind'
>> databases '
>> Thanks.
>
>.
>
2000 MSDE version comes with 'pubs' and 'Northwind'
databases '
Thanks.Hi Dan,
No, They will not be installed on setup. You can get the install scripts
from C:\Program Files\Microsoft SQL Server\MSSQL\Install.
They file names are:
instpubs.sql
instnwnd.sql
Otherwise get the latest scripts from microsoft site
http://www.microsoft.com/downloads/details.aspx?FamilyId=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en
Use OSQL frommd prompt to execute the script to create database and objects.
Thanks
Hari
SQL Server MVP
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:113c01c53c39$182a3970$a401280a@.phx.gbl...
> This is a dumb question but I need to ask. Does SQL Server
> 2000 MSDE version comes with 'pubs' and 'Northwind'
> databases '
> Thanks.|||Thanks.........
>--Original Message--
>Hi Dan,
>No, They will not be installed on setup. You can get the
install scripts
>from C:\Program Files\Microsoft SQL Server\MSSQL\Install.
>They file names are:
>instpubs.sql
>instnwnd.sql
>Otherwise get the latest scripts from microsoft site
>http://www.microsoft.com/downloads/details.aspx?
FamilyId=06616212-0356-46A0-8DA2-
EEBC53A68034&displaylang=en
>Use OSQL frommd prompt to execute the script to create
database and objects.
>Thanks
>Hari
>SQL Server MVP
>
>"Dan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:113c01c53c39$182a3970$a401280a@.phx.gbl...
>> This is a dumb question but I need to ask. Does SQL
Server
>> 2000 MSDE version comes with 'pubs' and 'Northwind'
>> databases '
>> Thanks.
>
>.
>
Duble \\ in physical file name
I recently reviewd our Veritas backup jobs and found several databases
failing on backup. I then went through the native SQL Backup processes and
found that these databases were indeed backing up to disk, but not thru the
Veritas client. After further review all of these database all had one thing
in common. They all had E:\PathName\\FileName.MDF in their filenames.
(Notice the double \\) I corrected this by detaching and reattaching with
move. What I wanna know is if anyone noticed this problem and what is
creating it?
Thanx for your help,
Raoul B. LaoyanGenerally it's Enterprise Manager. If the Default Data or Default Log
directory as viewed in Server Properties in EM have a trailing backslash
then new databases created without specifying filenames will have a \\ in
their path. To resolve this simply remove the trailing backslash from the
default data and log paths.
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Raoul Laoyan" <RaoulLaoyan@.discussions.microsoft.com> wrote in message
news:A049D4BF-0FBA-49BA-B031-CF6B21021B2A@.microsoft.com...
>I recently reviewd our Veritas backup jobs and found several databases
> failing on backup. I then went through the native SQL Backup processes
> and
> found that these databases were indeed backing up to disk, but not thru
> the
> Veritas client. After further review all of these database all had one
> thing
> in common. They all had E:\PathName\\FileName.MDF in their filenames.
> (Notice the double \\) I corrected this by detaching and reattaching with
> move. What I wanna know is if anyone noticed this problem and what is
> creating it?
> Thanx for your help,
> Raoul B. Laoyan
failing on backup. I then went through the native SQL Backup processes and
found that these databases were indeed backing up to disk, but not thru the
Veritas client. After further review all of these database all had one thing
in common. They all had E:\PathName\\FileName.MDF in their filenames.
(Notice the double \\) I corrected this by detaching and reattaching with
move. What I wanna know is if anyone noticed this problem and what is
creating it?
Thanx for your help,
Raoul B. LaoyanGenerally it's Enterprise Manager. If the Default Data or Default Log
directory as viewed in Server Properties in EM have a trailing backslash
then new databases created without specifying filenames will have a \\ in
their path. To resolve this simply remove the trailing backslash from the
default data and log paths.
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Raoul Laoyan" <RaoulLaoyan@.discussions.microsoft.com> wrote in message
news:A049D4BF-0FBA-49BA-B031-CF6B21021B2A@.microsoft.com...
>I recently reviewd our Veritas backup jobs and found several databases
> failing on backup. I then went through the native SQL Backup processes
> and
> found that these databases were indeed backing up to disk, but not thru
> the
> Veritas client. After further review all of these database all had one
> thing
> in common. They all had E:\PathName\\FileName.MDF in their filenames.
> (Notice the double \\) I corrected this by detaching and reattaching with
> move. What I wanna know is if anyone noticed this problem and what is
> creating it?
> Thanx for your help,
> Raoul B. Laoyan
Wednesday, March 7, 2012
Dual connection?
I do all my messing around with databases on my Windows
XPP partition and have a small installation of server 2K3
that I want to use as a test server. Can I set it up so
that I can connect to the same installation from both
systems; edit from XPP and use in 2K3 for my dynamic
webpages?
If this is a really simple question that I am just
missing somwhere in documantation just point the way home.
-- Matt WallaceCan you explain your question more clear?|||Hello,
Yes you can you just need to have two separate connection objects.
I hope this helped
Greg O
Save HOURS documenting your SQL Server! -- FREE Trial!
http://www.ag-software.com/ags_scribe_index.aspx
Crosstab Queries without OLAP -- 30 day free Trial!
http://www.ag-software.com/xp_ags_crosstab.aspx
"matt" <harmonicace@.hotmail.com> wrote in message
news:acbe01c40737$cb9c4b70$a401280a@.phx.gbl...
> I do all my messing around with databases on my Windows
> XPP partition and have a small installation of server 2K3
> that I want to use as a test server. Can I set it up so
> that I can connect to the same installation from both
> systems; edit from XPP and use in 2K3 for my dynamic
> webpages?
> If this is a really simple question that I am just
> missing somwhere in documantation just point the way home.
> -- Matt Wallace|||I guess I was a little ambiguous there
I have XPP installed on C: then I have 2K3 installed on H.
What I want to do is intall SQL Server on both OSes and
have them share a database on another partition. Or is it
possible for me to install it on the third partition and
have both OSes recognize that installation and its settings
etc. so that I can access it no matter which OS I'm booted
into.
Is that clearer?
thanks.
-- Matt
>--Original Message--
>Can you explain your question more clear?
>
>.
>|||Forgive my ignorance but I'm new to SQL and MS's SQL
Server. How would I go about doing that?
>--Original Message--
>Hello,
>Yes you can you just need to have two separate connection
objects.
>--
>I hope this helped
>Greg O
>Save HOURS documenting your SQL Server! -- FREE Trial!
>http://www.ag-software.com/ags_scribe_index.aspx
>Crosstab Queries without OLAP -- 30 day free Trial!
>http://www.ag-software.com/xp_ags_crosstab.aspx
>|||That is clear
If you see database files from both instalations you must install SQL Server
and attach these files in both instalation.
Bojidar Alexandrov
<anonymous@.discussions.microsoft.com> wrote in message
news:82f601c40780$ecffec60$a601280a@.phx.gbl...
> I guess I was a little ambiguous there
> I have XPP installed on C: then I have 2K3 installed on H.
> What I want to do is intall SQL Server on both OSes and
> have them share a database on another partition. Or is it
> possible for me to install it on the third partition and
> have both OSes recognize that installation and its settings
> etc. so that I can access it no matter which OS I'm booted
> into.
> Is that clearer?
> thanks.
> -- Matt
>|||Hi Matt,
Reading your other post I understand more of what you want to do now.
First you need to install SQL server on both installations.
Then you need to detach the User databases (Using EM) before you switch to
the other OS once on the other OS then attach the databases. It's important
to do that because it ensures the DB are in good working order.
I hope this helped
Greg O
Save HOURS documenting your SQL Server! -- FREE Trial!
http://www.ag-software.com/ags_scribe_index.aspx
Crosstab Queries without OLAP -- 30 day free Trial!
http://www.ag-software.com/xp_ags_crosstab.aspx
"matt" <harmonciace@.hotmail.com> wrote in message
news:b0f301c40781$4da4d440$a501280a@.phx.gbl...
> Forgive my ignorance but I'm new to SQL and MS's SQL
> Server. How would I go about doing that?
>
> objects.
XPP partition and have a small installation of server 2K3
that I want to use as a test server. Can I set it up so
that I can connect to the same installation from both
systems; edit from XPP and use in 2K3 for my dynamic
webpages?
If this is a really simple question that I am just
missing somwhere in documantation just point the way home.
-- Matt WallaceCan you explain your question more clear?|||Hello,
Yes you can you just need to have two separate connection objects.
I hope this helped
Greg O
Save HOURS documenting your SQL Server! -- FREE Trial!
http://www.ag-software.com/ags_scribe_index.aspx
Crosstab Queries without OLAP -- 30 day free Trial!
http://www.ag-software.com/xp_ags_crosstab.aspx
"matt" <harmonicace@.hotmail.com> wrote in message
news:acbe01c40737$cb9c4b70$a401280a@.phx.gbl...
> I do all my messing around with databases on my Windows
> XPP partition and have a small installation of server 2K3
> that I want to use as a test server. Can I set it up so
> that I can connect to the same installation from both
> systems; edit from XPP and use in 2K3 for my dynamic
> webpages?
> If this is a really simple question that I am just
> missing somwhere in documantation just point the way home.
> -- Matt Wallace|||I guess I was a little ambiguous there
I have XPP installed on C: then I have 2K3 installed on H.
What I want to do is intall SQL Server on both OSes and
have them share a database on another partition. Or is it
possible for me to install it on the third partition and
have both OSes recognize that installation and its settings
etc. so that I can access it no matter which OS I'm booted
into.
Is that clearer?
thanks.
-- Matt
>--Original Message--
>Can you explain your question more clear?
>
>.
>|||Forgive my ignorance but I'm new to SQL and MS's SQL
Server. How would I go about doing that?
>--Original Message--
>Hello,
>Yes you can you just need to have two separate connection
objects.
>--
>I hope this helped
>Greg O
>Save HOURS documenting your SQL Server! -- FREE Trial!
>http://www.ag-software.com/ags_scribe_index.aspx
>Crosstab Queries without OLAP -- 30 day free Trial!
>http://www.ag-software.com/xp_ags_crosstab.aspx
>|||That is clear
If you see database files from both instalations you must install SQL Server
and attach these files in both instalation.
Bojidar Alexandrov
<anonymous@.discussions.microsoft.com> wrote in message
news:82f601c40780$ecffec60$a601280a@.phx.gbl...
> I guess I was a little ambiguous there
> I have XPP installed on C: then I have 2K3 installed on H.
> What I want to do is intall SQL Server on both OSes and
> have them share a database on another partition. Or is it
> possible for me to install it on the third partition and
> have both OSes recognize that installation and its settings
> etc. so that I can access it no matter which OS I'm booted
> into.
> Is that clearer?
> thanks.
> -- Matt
>|||Hi Matt,
Reading your other post I understand more of what you want to do now.
First you need to install SQL server on both installations.
Then you need to detach the User databases (Using EM) before you switch to
the other OS once on the other OS then attach the databases. It's important
to do that because it ensures the DB are in good working order.
I hope this helped
Greg O
Save HOURS documenting your SQL Server! -- FREE Trial!
http://www.ag-software.com/ags_scribe_index.aspx
Crosstab Queries without OLAP -- 30 day free Trial!
http://www.ag-software.com/xp_ags_crosstab.aspx
"matt" <harmonciace@.hotmail.com> wrote in message
news:b0f301c40781$4da4d440$a501280a@.phx.gbl...
> Forgive my ignorance but I'm new to SQL and MS's SQL
> Server. How would I go about doing that?
>
> objects.
Friday, February 24, 2012
DTS: single SELECT from 2 databases possible?
Just wondering, in DTS, can I run a SELECT script that selects from 2 different databases (both on the same server)?
I can run this in Sql Query Analyzer, but in a DTS, it doesnt accept my database name prefixes:
SELECT
a.something
FROM
DB_ONE.dbo.product a, DB_ONE.dbo.mp_brand b, DB_TWO.dbo.lk_pcat_cutover c
WHERE
a.PCat <> c.Pcat
(where DB_ONE and DB_TWO are the 2 different db names)
I have one connection to DB_ONE... does this mean I cant access DB_TWO when using this connection? I want to try and avoid using a temporay table for storing DB_TWO's data in DB_ONE... Is this possible?
Thanks,
Andre
Dont know if this is the one and only answer, but by changing the datasource to the MASTER db, it then allows me to use my original SQL.DTS: migrating many, multiple, new databases regularly
I am looking for some advice on best ways to use DTS between our various test database servers.
This is useful. However, just as I mentioned in my previous post
-I purely need to copy the test data from my 200 tables that the application has created to or fro Oracle server/SQL Server; as the requirement is for
-copying many such databases and not just one database
-the application should be able to run independently on each server. as we not not bother about the server, we need a solution so that we do not need to create test data twice on each database SQL and Oracle databases.
-this is going to again be regular thing that I should be able to do as I will need to create new type of test data move it to other type of server (may it be Oracle or SQL) and test the application on both these databases.
Secondly, I have tried DTS - and what I need is a way to be able to map the tables. The wizard does not recognize the same table names with different cases. like A_Table and A_TABLE(oracle's table)|||Satya, Thanks for the links. They look very useful.
I need to test an application against MS SQL Server 2000/2005 and Oracle 9i/10g. This includes creating new databases,logins etc on each server. And also means I need to recreate all my test data again at least twice; on Oracle and MS SQL Server(i.e I can backup and restore my databases between MS SQL and Oracle versions). I am looking to for a better way to achieve this and am here looking for some advice on the best way.
DTS (tried only on MS SQL 2000 so far) does seem to help however, one problem I have come across is mapping tables. The application creates little over 200 tables, although the schema remains over all remains identical, but with slight differences. E.g different name formats. For instance. a table on SQL2000 is called "Table" whereas Oracle calls is "TABLE". This means, on the DTS wizard I need to drop down and select the correct table name for each table.
What could be other possible and efficient ways to achieve this? Also, I need to do this for around 10 multiple databases and not just one database. I will also need to be doing this frequently, if not regularly as I will be creating new test databases and test data.
Any inputs will be appreciated. Thank you.
i think DTS or SSIS of sql server 2005 (the true ETL Tool with much more advanced features than DTS) can handle this problem. If you want to create your own automated script then you can configure LinkedServer from your staging server and fetch the data.
Madhu
|||Thanks for the reply Madhu.I am quite inexperienced and still learning a lot of things. I have only used DTS a few times. SSIS? Havent really heard of it before!
If I was to use DTS to transfer data from my MS SQL200 database to Oracle 9i/10g and SQL2005, what would be an easy way to map these 200 tables rather than going and selecting the correct table from destination database?
Thanks once again.|||
if DTS is the ETL tool of SQL Server 2000 , SSIS is enhanced , Feature rich ETL tool in SQL Server 2005. Here only name is not changed, there are lot of enhancement as far as the features are concerned. If you wnat to transfer the data from SQL Server 2000 to 2005 then you must create SSIS not DTS. I am not sure about transporing to oracle. But you can configure Linkedserver to oracle/sql server and you can create synonyms in sql server 2005 which will be as good as you are accessing the object from your own database.
Madhu
|||N
There is alot information available on the SQL Server books online about SSIS,make sure you have downloaded the latest update from Microsoft.
Look at this blog http://sqljunkies.com/WebLog/donald_farmer/archive/2005/03/13/8819.aspx that helps.
http://www.developer.com/db/article.php/3635316
http://msdn2.microsoft.com/en-us/sql/aa336312.aspx
|||Thanks again Madhu.This is useful. However, just as I mentioned in my previous post
-I purely need to copy the test data from my 200 tables that the application has created to or fro Oracle server/SQL Server; as the requirement is for
-copying many such databases and not just one database
-the application should be able to run independently on each server. as we not not bother about the server, we need a solution so that we do not need to create test data twice on each database SQL and Oracle databases.
-this is going to again be regular thing that I should be able to do as I will need to create new type of test data move it to other type of server (may it be Oracle or SQL) and test the application on both these databases.
Secondly, I have tried DTS - and what I need is a way to be able to map the tables. The wizard does not recognize the same table names with different cases. like A_Table and A_TABLE(oracle's table)|||Satya, Thanks for the links. They look very useful.
DTS: How to connect to hundreds of DBs dynamically
I need to release a few triggers and procedures to hundreds of databases
with the same structures.
I try the Dynamic Property in DTS, create an "Execution Task", add a
connection property with a source type as "Query", and the query is like
"select the database name from master..sysdatabases where name not in
('master','model','temp')".
Unfortunately, the package runs only on the first DB in the query.
How can I make it run on each of all databases?
Thank you.Create a connection to the server,
create an "Execute SQL Task" with your databases query in it -
in the "Output parameters" tab of the sql task use a rowset output
then loop through the values in an ActiveX script to perform your jobs.
http://www.sqldts.com/default.aspx?298
"Jluo" <jluost1@.yahoo.com> wrote in message
news:4224B876.5000304@.yahoo.com...
> I need to release a few triggers and procedures to hundreds of databases
> with the same structures.
> I try the Dynamic Property in DTS, create an "Execution Task", add a
> connection property with a source type as "Query", and the query is like
> "select the database name from master..sysdatabases where name not in
> ('master','model','temp')".
> Unfortunately, the package runs only on the first DB in the query.
> How can I make it run on each of all databases?
> Thank you.
>
with the same structures.
I try the Dynamic Property in DTS, create an "Execution Task", add a
connection property with a source type as "Query", and the query is like
"select the database name from master..sysdatabases where name not in
('master','model','temp')".
Unfortunately, the package runs only on the first DB in the query.
How can I make it run on each of all databases?
Thank you.Create a connection to the server,
create an "Execute SQL Task" with your databases query in it -
in the "Output parameters" tab of the sql task use a rowset output
then loop through the values in an ActiveX script to perform your jobs.
http://www.sqldts.com/default.aspx?298
"Jluo" <jluost1@.yahoo.com> wrote in message
news:4224B876.5000304@.yahoo.com...
> I need to release a few triggers and procedures to hundreds of databases
> with the same structures.
> I try the Dynamic Property in DTS, create an "Execution Task", add a
> connection property with a source type as "Query", and the query is like
> "select the database name from master..sysdatabases where name not in
> ('master','model','temp')".
> Unfortunately, the package runs only on the first DB in the query.
> How can I make it run on each of all databases?
> Thank you.
>
Subscribe to:
Posts (Atom)