Hi, is there a software that can dump the SQL result into a file or to another database? I am using the MSSQL Server and I am accessing it remotely. Is there a program where I can issue an SQL statement and then I can dump the result into a file (in SQL inserts or just plain CSV).
Is there a free version?
Thanks!if you are using thr qery analyzer go to query-->results to file|||Hi, is there a software that can dump the SQL result into a file or to another database? I am using the MSSQL Server and I am accessing it remotely. Is there a program where I can issue an SQL statement and then I can dump the result into a file (in SQL inserts or just plain CSV).
Is there a free version?
Thanks!
You can try this -
From command prompt:
osql.exe -S YourServerName -U sa -P secretcode -Q "EXEC sp_who2" -o "E:\output.txt"
From T-SQL:
EXEC master..xp_cmdshell 'osql.exe -S YourServerName -U sa -P secretcode -Q "EXEC sp_who2" -o "E:\output.txt"'
Showing posts with label dumping. Show all posts
Showing posts with label dumping. Show all posts
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 sql server
BACKUP DATABASE?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"John" <John@.nospam.infovis.co.uk> wrote in message news:eoAZqSDhHHA.4552@.TK2MSFTNGP04.phx.gbl...
> Hi
> Is there a way to dump all the sql server data into some sort of file that
> can be imported into a local sql server later? I have used sql server dumper
> but it is not very stable and crashes during sump.
> Thanks
> Regards
>
Hi John
"John" wrote:
> Remote, does not work for some reason possibly insufficient rights.
>
If you want to post the command you are using it may help? Also the full
error message would be useful. If you want the backup to be placed on a
different server you can use a UNC path. Make sure that you also have
directory permissions as well as server/database permissions sysadmin,
db_owner or db_backupoperator. See BACKUP in Books Online for more.
Databases can also be detached and attached, it is usually best to attach
the database to an instance at the same service pack/hotfix number, but SQL
2000 databases can be attached to SQL 2005 instances, although you may have
to change incompatibilities if you want to use the latest compatibility mode.
If you are changing instance you will need to script logins and possibly
resolved orphaned users see
http://support.microsoft.com/default.aspx/kb/314546 for more.
John
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:eQl9syDhHHA.392@.TK2MSFTNGP06.phx.gbl...
>
>
|||Can't you backup the database to a file local on the SQL Server machine and then FTP the file to
your machine?
Other methods include script out all the objects and data and then re-create the objects locally and
then import that data. I doubt you will find something as such as rock solid as backup. I've listed
some tools to generate scripts and some with data at
http://www.karaszi.com/SQLServer/info_generate_script.asp.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%234nMS%23DhHHA.4692@.TK2MSFTNGP04.phx.gbl...
> Remote, does not work for some reason possibly insufficient rights.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:eQl9syDhHHA.392@.TK2MSFTNGP06.phx.gbl...
>
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"John" <John@.nospam.infovis.co.uk> wrote in message news:eoAZqSDhHHA.4552@.TK2MSFTNGP04.phx.gbl...
> Hi
> Is there a way to dump all the sql server data into some sort of file that
> can be imported into a local sql server later? I have used sql server dumper
> but it is not very stable and crashes during sump.
> Thanks
> Regards
>
Hi John
"John" wrote:
> Remote, does not work for some reason possibly insufficient rights.
>
If you want to post the command you are using it may help? Also the full
error message would be useful. If you want the backup to be placed on a
different server you can use a UNC path. Make sure that you also have
directory permissions as well as server/database permissions sysadmin,
db_owner or db_backupoperator. See BACKUP in Books Online for more.
Databases can also be detached and attached, it is usually best to attach
the database to an instance at the same service pack/hotfix number, but SQL
2000 databases can be attached to SQL 2005 instances, although you may have
to change incompatibilities if you want to use the latest compatibility mode.
If you are changing instance you will need to script logins and possibly
resolved orphaned users see
http://support.microsoft.com/default.aspx/kb/314546 for more.
John
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:eQl9syDhHHA.392@.TK2MSFTNGP06.phx.gbl...
>
>
|||Can't you backup the database to a file local on the SQL Server machine and then FTP the file to
your machine?
Other methods include script out all the objects and data and then re-create the objects locally and
then import that data. I doubt you will find something as such as rock solid as backup. I've listed
some tools to generate scripts and some with data at
http://www.karaszi.com/SQLServer/info_generate_script.asp.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%234nMS%23DhHHA.4692@.TK2MSFTNGP04.phx.gbl...
> Remote, does not work for some reason possibly insufficient rights.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:eQl9syDhHHA.392@.TK2MSFTNGP06.phx.gbl...
>
Dumping sql server
Hi
Is there a way to dump all the sql server data into some sort of file that
can be imported into a local sql server later? I have used sql server dumper
but it is not very stable and crashes during sump.
Thanks
RegardsBACKUP DATABASE?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"John" <John@.nospam.infovis.co.uk> wrote in message news:eoAZqSDhHHA.4552@.TK2MSFTNGP04.phx.gbl...
> Hi
> Is there a way to dump all the sql server data into some sort of file that
> can be imported into a local sql server later? I have used sql server dumper
> but it is not very stable and crashes during sump.
> Thanks
> Regards
>|||Remote, does not work for some reason possibly insufficient rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eQl9syDhHHA.392@.TK2MSFTNGP06.phx.gbl...
> BACKUP DATABASE?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:eoAZqSDhHHA.4552@.TK2MSFTNGP04.phx.gbl...
>> Hi
>> Is there a way to dump all the sql server data into some sort of file
>> that can be imported into a local sql server later? I have used sql
>> server dumper but it is not very stable and crashes during sump.
>> Thanks
>> Regards|||Hi John
"John" wrote:
> Remote, does not work for some reason possibly insufficient rights.
>
If you want to post the command you are using it may help? Also the full
error message would be useful. If you want the backup to be placed on a
different server you can use a UNC path. Make sure that you also have
directory permissions as well as server/database permissions sysadmin,
db_owner or db_backupoperator. See BACKUP in Books Online for more.
Databases can also be detached and attached, it is usually best to attach
the database to an instance at the same service pack/hotfix number, but SQL
2000 databases can be attached to SQL 2005 instances, although you may have
to change incompatibilities if you want to use the latest compatibility mode.
If you are changing instance you will need to script logins and possibly
resolved orphaned users see
http://support.microsoft.com/default.aspx/kb/314546 for more.
John
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:eQl9syDhHHA.392@.TK2MSFTNGP06.phx.gbl...
> > BACKUP DATABASE?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://sqlblog.com/blogs/tibor_karaszi
> >
> >
> > "John" <John@.nospam.infovis.co.uk> wrote in message
> > news:eoAZqSDhHHA.4552@.TK2MSFTNGP04.phx.gbl...
> >> Hi
> >>
> >> Is there a way to dump all the sql server data into some sort of file
> >> that can be imported into a local sql server later? I have used sql
> >> server dumper but it is not very stable and crashes during sump.
> >>
> >> Thanks
> >>
> >> Regards
>
>|||Can't you backup the database to a file local on the SQL Server machine and then FTP the file to
your machine?
Other methods include script out all the objects and data and then re-create the objects locally and
then import that data. I doubt you will find something as such as rock solid as backup. I've listed
some tools to generate scripts and some with data at
http://www.karaszi.com/SQLServer/info_generate_script.asp.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%234nMS%23DhHHA.4692@.TK2MSFTNGP04.phx.gbl...
> Remote, does not work for some reason possibly insufficient rights.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:eQl9syDhHHA.392@.TK2MSFTNGP06.phx.gbl...
>> BACKUP DATABASE?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "John" <John@.nospam.infovis.co.uk> wrote in message news:eoAZqSDhHHA.4552@.TK2MSFTNGP04.phx.gbl...
>> Hi
>> Is there a way to dump all the sql server data into some sort of file that can be imported into
>> a local sql server later? I have used sql server dumper but it is not very stable and crashes
>> during sump.
>> Thanks
>> Regards
>
Is there a way to dump all the sql server data into some sort of file that
can be imported into a local sql server later? I have used sql server dumper
but it is not very stable and crashes during sump.
Thanks
RegardsBACKUP DATABASE?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"John" <John@.nospam.infovis.co.uk> wrote in message news:eoAZqSDhHHA.4552@.TK2MSFTNGP04.phx.gbl...
> Hi
> Is there a way to dump all the sql server data into some sort of file that
> can be imported into a local sql server later? I have used sql server dumper
> but it is not very stable and crashes during sump.
> Thanks
> Regards
>|||Remote, does not work for some reason possibly insufficient rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eQl9syDhHHA.392@.TK2MSFTNGP06.phx.gbl...
> BACKUP DATABASE?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:eoAZqSDhHHA.4552@.TK2MSFTNGP04.phx.gbl...
>> Hi
>> Is there a way to dump all the sql server data into some sort of file
>> that can be imported into a local sql server later? I have used sql
>> server dumper but it is not very stable and crashes during sump.
>> Thanks
>> Regards|||Hi John
"John" wrote:
> Remote, does not work for some reason possibly insufficient rights.
>
If you want to post the command you are using it may help? Also the full
error message would be useful. If you want the backup to be placed on a
different server you can use a UNC path. Make sure that you also have
directory permissions as well as server/database permissions sysadmin,
db_owner or db_backupoperator. See BACKUP in Books Online for more.
Databases can also be detached and attached, it is usually best to attach
the database to an instance at the same service pack/hotfix number, but SQL
2000 databases can be attached to SQL 2005 instances, although you may have
to change incompatibilities if you want to use the latest compatibility mode.
If you are changing instance you will need to script logins and possibly
resolved orphaned users see
http://support.microsoft.com/default.aspx/kb/314546 for more.
John
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:eQl9syDhHHA.392@.TK2MSFTNGP06.phx.gbl...
> > BACKUP DATABASE?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://sqlblog.com/blogs/tibor_karaszi
> >
> >
> > "John" <John@.nospam.infovis.co.uk> wrote in message
> > news:eoAZqSDhHHA.4552@.TK2MSFTNGP04.phx.gbl...
> >> Hi
> >>
> >> Is there a way to dump all the sql server data into some sort of file
> >> that can be imported into a local sql server later? I have used sql
> >> server dumper but it is not very stable and crashes during sump.
> >>
> >> Thanks
> >>
> >> Regards
>
>|||Can't you backup the database to a file local on the SQL Server machine and then FTP the file to
your machine?
Other methods include script out all the objects and data and then re-create the objects locally and
then import that data. I doubt you will find something as such as rock solid as backup. I've listed
some tools to generate scripts and some with data at
http://www.karaszi.com/SQLServer/info_generate_script.asp.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%234nMS%23DhHHA.4692@.TK2MSFTNGP04.phx.gbl...
> Remote, does not work for some reason possibly insufficient rights.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:eQl9syDhHHA.392@.TK2MSFTNGP06.phx.gbl...
>> BACKUP DATABASE?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "John" <John@.nospam.infovis.co.uk> wrote in message news:eoAZqSDhHHA.4552@.TK2MSFTNGP04.phx.gbl...
>> Hi
>> Is there a way to dump all the sql server data into some sort of file that can be imported into
>> a local sql server later? I have used sql server dumper but it is not very stable and crashes
>> during sump.
>> Thanks
>> Regards
>
Dumping sql server
Hi
Is there a way to dump all the sql server data into some sort of file that
can be imported into a local sql server later? I have used sql server dumper
but it is not very stable and crashes during sump.
Thanks
RegardsBACKUP DATABASE?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"John" <John@.nospam.infovis.co.uk> wrote in message news:eoAZqSDhHHA.4552@.TK2MSFTNGP04.phx.g
bl...
> Hi
> Is there a way to dump all the sql server data into some sort of file that
> can be imported into a local sql server later? I have used sql server dump
er
> but it is not very stable and crashes during sump.
> Thanks
> Regards
>|||Remote, does not work for some reason possibly insufficient rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eQl9syDhHHA.392@.TK2MSFTNGP06.phx.gbl...[vbcol=seagreen]
> BACKUP DATABASE?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:eoAZqSDhHHA.4552@.TK2MSFTNGP04.phx.gbl...|||Hi John
"John" wrote:
> Remote, does not work for some reason possibly insufficient rights.
>
If you want to post the command you are using it may help? Also the full
error message would be useful. If you want the backup to be placed on a
different server you can use a UNC path. Make sure that you also have
directory permissions as well as server/database permissions sysadmin,
db_owner or db_backupoperator. See BACKUP in Books Online for more.
Databases can also be detached and attached, it is usually best to attach
the database to an instance at the same service pack/hotfix number, but SQL
2000 databases can be attached to SQL 2005 instances, although you may have
to change incompatibilities if you want to use the latest compatibility mode
.
If you are changing instance you will need to script logins and possibly
resolved orphaned users see
http://support.microsoft.com/default.aspx/kb/314546 for more.
John
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:eQl9syDhHHA.392@.TK2MSFTNGP06.phx.gbl...
>
>|||Can't you backup the database to a file local on the SQL Server machine and
then FTP the file to
your machine?
Other methods include script out all the objects and data and then re-create
the objects locally and
then import that data. I doubt you will find something as such as rock solid
as backup. I've listed
some tools to generate scripts and some with data at
http://www.karaszi.com/SQLServer/in...ate_script.asp.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%234nMS%23DhHHA.4692@.TK2MSFTNGP04.phx.gbl...
> Remote, does not work for some reason possibly insufficient rights.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:eQl9syDhHHA.392@.TK2MSFTNGP06.phx.gbl...
>sql
Is there a way to dump all the sql server data into some sort of file that
can be imported into a local sql server later? I have used sql server dumper
but it is not very stable and crashes during sump.
Thanks
RegardsBACKUP DATABASE?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"John" <John@.nospam.infovis.co.uk> wrote in message news:eoAZqSDhHHA.4552@.TK2MSFTNGP04.phx.g
bl...
> Hi
> Is there a way to dump all the sql server data into some sort of file that
> can be imported into a local sql server later? I have used sql server dump
er
> but it is not very stable and crashes during sump.
> Thanks
> Regards
>|||Remote, does not work for some reason possibly insufficient rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eQl9syDhHHA.392@.TK2MSFTNGP06.phx.gbl...[vbcol=seagreen]
> BACKUP DATABASE?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:eoAZqSDhHHA.4552@.TK2MSFTNGP04.phx.gbl...|||Hi John
"John" wrote:
> Remote, does not work for some reason possibly insufficient rights.
>
If you want to post the command you are using it may help? Also the full
error message would be useful. If you want the backup to be placed on a
different server you can use a UNC path. Make sure that you also have
directory permissions as well as server/database permissions sysadmin,
db_owner or db_backupoperator. See BACKUP in Books Online for more.
Databases can also be detached and attached, it is usually best to attach
the database to an instance at the same service pack/hotfix number, but SQL
2000 databases can be attached to SQL 2005 instances, although you may have
to change incompatibilities if you want to use the latest compatibility mode
.
If you are changing instance you will need to script logins and possibly
resolved orphaned users see
http://support.microsoft.com/default.aspx/kb/314546 for more.
John
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:eQl9syDhHHA.392@.TK2MSFTNGP06.phx.gbl...
>
>|||Can't you backup the database to a file local on the SQL Server machine and
then FTP the file to
your machine?
Other methods include script out all the objects and data and then re-create
the objects locally and
then import that data. I doubt you will find something as such as rock solid
as backup. I've listed
some tools to generate scripts and some with data at
http://www.karaszi.com/SQLServer/in...ate_script.asp.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%234nMS%23DhHHA.4692@.TK2MSFTNGP04.phx.gbl...
> Remote, does not work for some reason possibly insufficient rights.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:eQl9syDhHHA.392@.TK2MSFTNGP06.phx.gbl...
>sql
Dumping SQL database
My site is hosted with 1and1. They tell me I need to dump my SQL database before uploading it to their site. I have never done this before. I have looked in the online help and have asked them for help, but nothing seems to work. I've tried queries like the following:
DUMP DATABASE dbname TO DISK = 'C:\folder\filename'
It tells me it doesn't recognize "dbname", which is the name of the MDF file (without the ".mdf" extension). So I'm not sure what I should be using for database name.
I'm using Visual Studio 2005 Professional. Can someone help me with dumping my database? Thanks!
you can either Detach a database in which case you should beable to copy the mdf to a new location then reattach.
or you can backup a database to a file and then you copy the .bak file to the new location and restore the database.
this link would indicate that 1and1 want you to backup your database http://msdn2.microsoft.com/en-us/library/ms187315.aspx
its pretty easy to backup a database through the SQL Server IDE... and this link gives a detailed script that will backup all your databases...
http://www.sqlmag.com/Articles/ArticleID/46560/46560.html?Ad=1
Dumping Sql after an exception
Does anyone have a code snippet for dumping the actual sql a command would be running?
What do you mean by "dumping" ? You can kill the session..
|||No I mean like getting the actual SQL it was trying to execute.
|||If you know the spid you can do DBCC INPUTBUFFER(spid) to see what the spid is executing.
Dumping rows of 2 columns of a table to a different table with different no of columns
Hi,
I have two tables
Table Source
{
category varchar(20),
LastUpdate datetime
}
Table Destination
{
SubjectId varchar(20),
SubjectDate datetime,
category varchar(20),
LastUpdate datetime
}
Please note that the number columns are different in each table.
I wanted to dump the data of Source table to Destination table. I meant to say that the rows of 2 columns in Source table to last 2 rows of Destination table.
And also my oreder of the columns in Destination table will vary. So i need to a way to dynamically insert the data in bulk. but i will know the column names for sure before inserting.
Is there anyway to bulk insert into these columns.
Your quick response will be appreciated
~Mohan Babu
It is a very simple solution:1. create a view on destination
CREATE VIEW [viewDestination]
AS
SELECT category, LastUpdate
FROM dbo.Destination
2. Then
insert into dbo.viewDestination
select * from Source
doesn't matter the order in Destination table.|||Thanks for the reply.But i don't want to create a view on this as the number of coulmns on the table will be changed dynamically. Can you please suggest any other way
Dumping rows of 2 columns of a table to a different table with different no of columns
Hi,
I have two tables
Table Source
{
category varchar(20),
LastUpdate datetime
}
Table Destination
{
SubjectId varchar(20),
SubjectDate datetime,
category varchar(20),
LastUpdate datetime
}
Please note that the number columns are different in each table.
I wanted to dump the data of Source table to Destination table. I meant to say that the rows of 2 columns in Source table to last 2 rows of Destination table.
And also my oreder of the columns in Destination table will vary. So i need to a way to dynamically insert the data in bulk. but i will know the column names for sure before inserting.
Is there anyway to bulk insert into these columns.
Your quick response will be appreciated
~Mohan Babu
It is a very simple solution:1. create a view on destination
CREATE VIEW [viewDestination]
AS
SELECT category, LastUpdate
FROM dbo.Destination
2. Then
insert into dbo.viewDestination
select * from Source
doesn't matter the order in Destination table.|||Thanks for the reply.But i don't want to create a view on this as the number of coulmns on the table will be changed dynamically. Can you please suggest any other way
Dumping incremental changes to textfile
I was wondering if there is a way to schedule a tast that will dump a
fixed width text file of all the new entries in a table. So if I had
a table with like
fixed width text file of all the new entries in a table. So if I had
a table with like
username - varchar(20)
created - smalldatetime
I could get a weekly feed each week of all the new users in a text
file. I know I could write a script that would go through and do this
by looking at the time stamp, and the last time that the file
previously ran and get the new dates but I was hoping there was a
built way to do this. Or perhaps a more elegant solution.
Thanks,
Charlie"charliek" <charlie_knudsen@.hotmail.com> wrote in message
news:964kpvc52gfj8mp7vf05oktlquaukcfgdm@.4ax.com...
> I was wondering if there is a way to schedule a tast that will dump a
> fixed width text file of all the new entries in a table. So if I had
> a table with like
> username - varchar(20)
> created - smalldatetime
> I could get a weekly feed each week of all the new users in a text
> file. I know I could write a script that would go through and do this
> by looking at the time stamp, and the last time that the file
> previously ran and get the new dates but I was hoping there was a
> built way to do this. Or perhaps a more elegant solution.
> Thanks,
> Charlie
Using a script is fine, or you could look at BCP or DTS as well. There's no
automatic functionality for this, so you'll have to set something up
yourself.
Simon|||On Sat, 25 Oct 2003 14:38:12 +0200, "Simon Hayes" <sql@.hayes.ch>
wrote:
>"charliek" <charlie_knudsen@.hotmail.com> wrote in message
>news:964kpvc52gfj8mp7vf05oktlquaukcfgdm@.4ax.com...
>> I was wondering if there is a way to schedule a tast that will dump a
>> fixed width text file of all the new entries in a table. So if I had
>> a table with like
>>
>> username - varchar(20)
>> created - smalldatetime
>>
>> I could get a weekly feed each week of all the new users in a text
>> file. I know I could write a script that would go through and do this
>> by looking at the time stamp, and the last time that the file
>> previously ran and get the new dates but I was hoping there was a
>> built way to do this. Or perhaps a more elegant solution.
>>
>> Thanks,
>>
>> Charlie
>Using a script is fine, or you could look at BCP or DTS as well. There's no
>automatic functionality for this, so you'll have to set something up
>yourself.
>Simon
Thanks for the info. I thought that this may be the case, however I
wanted to make sure that I was not overlooking a easy or more
efficient solution.
Thanks,
Charliesql
dumping data from database
Hi:)
I need to dump data from database in to *.sql file. I try it using DUMP
DATABASE but it not work that I need it.
I need to dump data from database in to *.sql file. I try it using DUMP
DATABASE but it not work that I need it.
So, how can I dump data from database in to *.sql file and then run it
using: osql -E -i nameOfDumpedDatabase.sql
Thanks for help, greetings, gregory :)"rozrabiak" <brak@.maila.pl> wrote in message
news:c6r83b$5ra$1@.news.onet.pl...
> Hi:)
> I need to dump data from database in to *.sql file. I try it using DUMP
> DATABASE but it not work that I need it.
> So, how can I dump data from database in to *.sql file and then run it
> using: osql -E -i nameOfDumpedDatabase.sql
>
> Thanks for help, greetings, gregory :)
You can use bcp.exe or DTS to export data to a file and load it again, or if
you prefer INSERT statements then see here:
http://vyaskn.tripod.com/code.htm#inserts
If you want everything (objects and data), then BACKUP and RESTORE is often
the easiest way.
Simon|||rozrabiak (brak@.maila.pl) writes:
> I need to dump data from database in to *.sql file. I try it using DUMP
> DATABASE but it not work that I need it.
> So, how can I dump data from database in to *.sql file and then run it
> using: osql -E -i nameOfDumpedDatabase.sql
In Enterprise Manager, find the database, right-click, select All Tasks,
and in that submenu you find scripting options.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94DAF1247AB58Yazorman@.127.0.0.1>...
> rozrabiak (brak@.maila.pl) writes:
> > I need to dump data from database in to *.sql file. I try it using DUMP
> > DATABASE but it not work that I need it.
> > So, how can I dump data from database in to *.sql file and then run it
> > using: osql -E -i nameOfDumpedDatabase.sql
> In Enterprise Manager, find the database, right-click, select All Tasks,
> and in that submenu you find scripting options.
There is a Java program called Super*SQL which will unload any tables
you have into an ASCII file that you can then run or load into a
different database. For more information visit www.sqlmagic.com.|||rozrabiak <brak@.maila.pl> wrote in message news:<c6r83b$5ra$1@.news.onet.pl>...
> Hi:)
> I need to dump data from database in to *.sql file. I try it using DUMP
> DATABASE but it not work that I need it.
Hi, you could also look at this article which describes a Java utility
to dump both data and schema information to an SQL file.
http://isocra.com/articles/article.php?id=java_db_dump
We wrote it because we needed to synchronize two remote SQLServer
databases as well as a MySQL development database.
In fact it's pretty easy to integrate it into a JSP to give the same
sort of dumping functionality as phpMyAdmin but for SQLServer.
dumping data from database
Hi:)
I need to dump data from database in to *.sql file. I try it using DUMP
DATABASE but it not work that I need it.
I need to dump data from database in to *.sql file. I try it using DUMP
DATABASE but it not work that I need it.
So, how can I dump data from database in to *.sql file and then run it
using: osql -E -i nameOfDumpedDatabase.sql
Thanks for help, greetings, gregory :)"rozrabiak" <brak@.maila.pl> wrote in message
news:c6r83b$5ra$1@.news.onet.pl...
> Hi:)
> I need to dump data from database in to *.sql file. I try it using DUMP
> DATABASE but it not work that I need it.
> So, how can I dump data from database in to *.sql file and then run it
> using: osql -E -i nameOfDumpedDatabase.sql
>
> Thanks for help, greetings, gregory :)
You can use bcp.exe or DTS to export data to a file and load it again, or if
you prefer INSERT statements then see here:
http://vyaskn.tripod.com/code.htm#inserts
If you want everything (objects and data), then BACKUP and RESTORE is often
the easiest way.
Simon|||rozrabiak (brak@.maila.pl) writes:
> I need to dump data from database in to *.sql file. I try it using DUMP
> DATABASE but it not work that I need it.
> So, how can I dump data from database in to *.sql file and then run it
> using: osql -E -i nameOfDumpedDatabase.sql
In Enterprise Manager, find the database, right-click, select All Tasks,
and in that submenu you find scripting options.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94DAF1247AB58Yazorman@.127.0.0.1>...
> rozrabiak (brak@.maila.pl) writes:
> > I need to dump data from database in to *.sql file. I try it using DUMP
> > DATABASE but it not work that I need it.
> > So, how can I dump data from database in to *.sql file and then run it
> > using: osql -E -i nameOfDumpedDatabase.sql
> In Enterprise Manager, find the database, right-click, select All Tasks,
> and in that submenu you find scripting options.
There is a Java program called Super*SQL which will unload any tables
you have into an ASCII file that you can then run or load into a
different database. For more information visit www.sqlmagic.com.
Dumping Data from a XML file to DB Tables
Hi All ,
I have an XML file saved in my machine at c:\MyXML.xml . I have two tables in my DB as Table1 ( [col1],[col2]) & Table2()[col1],[col2] . I have to dump the data from this xml file to these two tables in one shot .The format of the XML file MyXML.xml is something like this :
<?xml version = "1.0">
-<Report xmlns = " MyReport".......... >
- <table1>
- <Data>
<Record col1 = "A" col2 = "B" / >
<Record col1 = "C" col2 = "D" / >
<Record col1 = "E" col2 = "F" / >
</Data>
</table1>
- <table2>
- <Data>
<Record col1 = "A" col2 = "B" / >
<Record col1 = "C" col2 = "D" / >
<Record col1 = "E" col2 = "F" / >
</Data>
</table2>
</Report>
Plz suggest me some possible ways to get this done . I m using sql 2005.
Thanks in advance for the help.
Here is a complete example, you can use the OPENXML statement after the stored procedure sp_xml_preparedocument has parsed the XML:
Code Snippet
CREATE TABLE table1 (
col1 nvarchar(10),
col2 nvarchar(10)
);
CREATE TABLE table2 (
col1 nvarchar(10),
col2 nvarchar(10)
);
DECLARE @.xmlDocument xml;
SET @.xmlDocument = '<Report xmlns="MyReport">
<table1>
<Data>
<Record col1 = "A" col2 = "B" />
<Record col1 = "C" col2 = "D" />
<Record col1 = "E" col2 = "F" />
</Data>
</table1>
<table2>
<Data>
<Record col1 = "A" col2 = "B" />
<Record col1 = "C" col2 = "D" />
<Record col1 = "E" col2 = "F" />
</Data>
</table2>
</Report>';
DECLARE @.docHandle int;
EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDocument, N'<Report xmlns:ns1="MyReport"/>';
INSERT INTO table1
SELECT *
FROM OPENXML (@.docHandle, N'/ns1:Report/ns1:table1/ns1:Data/ns1:Record', 0)
WITH table1;
INSERT INTO table2
SELECT *
FROM OPENXML (@.docHandle, N'/ns1:Report/ns1:table2/ns1:Data/ns1:Record', 0)
WITH table2;
EXEC sp_xml_removedocument @.docHandle;
SELECT col1, col2 FROM table1;
SELECT col1, col2 FROM table2
dumping backups to share in different domain
Hi,
I have a sql server running in domain abc.com. de sqlserver and the sqlagent
services are running on their own useraccounts SqlServerServ@.abc.com and
SqlAgentServ@.abc.com. This server is in our own server room which we use to
host servers on our own domain 123.com.
the following task needs to be done:
make backups of the databases in sqlserver.abc.com to the file share on
fileserver.123.com by creating jobs in sqlagent.
What can not be done:
Make a trust between abc.com and 123.com since both companies don't trust
each other.
extra info.
sqlserver.abc.com
sql server 2000 standard SP3 running on
windows 2000 Standard edition (patches unknown)
this box happens to be the DC for abc.com as well (it's a test enviorment)
fileserver.123.com
windows 2000 standard edition (patches unknown)
this box is not the DC for 123.com
a share is create \\fileserver\DBbackups
and a user called SQLbackup@.123.com which has full rights to this share
What is the best way to go arround:
1) try and make the backups work using SQLbackup@.123.com
2) or try and give SQLAgentServ@.abc.com access to the fileshare on
fileserver.123.com
Is any of these two option possible at all?
are there better solutions with this setup?(we cannot change the domain
setup and memberships, and we cannot at extra boxes)
Kind regards
Edward Dortland
please reply to newsgroup ONLYSuggestions:
Install FTP server on fileserver.123.com, then
1. Do local backup on abc.com
2. Transfer these backups to fileserver.123.com using -s:filename option.
OR try this one (not sure if it will work).
1. Create LOCAL account SQLAgentServ on both computers with same password.
From Log shipping:
"Local Network Account
You can use SQL Server to start under a locally-created network account. In
the situation where there is network access required by a SQL Server
process, which is the case if you have configured SQL Server to use log
shipping, you can use network pass-through security. With pass-through
security, all machines that will be accessed by SQL Server must have the
same network account with the same password and appropriate permissions,
configured locally. Additionally, when the SQL Server process requests
resources from the second computer, traditional network security is bypassed
if the same account (under which the requesting SQL Server service is
started) exists with the same password. As long the account on the second
computer is configured with enough permission to carry out the task that is
requested by calling SQL Server, the task will be successful. "
2. Try to do backup.
HTH
Igor Raytsin
"Edward Dortland" <edwardNOSPAMMMM@.solsol.nl> wrote in message
news:104jovuket4qk0f@.corp.supernews.com...
> Hi,
> I have a sql server running in domain abc.com. de sqlserver and the
sqlagent
> services are running on their own useraccounts SqlServerServ@.abc.com and
> SqlAgentServ@.abc.com. This server is in our own server room which we use
to
> host servers on our own domain 123.com.
> the following task needs to be done:
> make backups of the databases in sqlserver.abc.com to the file share on
> fileserver.123.com by creating jobs in sqlagent.
> What can not be done:
> Make a trust between abc.com and 123.com since both companies don't trust
> each other.
> extra info.
> sqlserver.abc.com
> sql server 2000 standard SP3 running on
> Windows 2000 Standard edition (patches unknown)
> this box happens to be the DC for abc.com as well (it's a test enviorment)
> fileserver.123.com
> Windows 2000 standard edition (patches unknown)
> this box is not the DC for 123.com
> a share is create \\fileserver\DBbackups
> and a user called SQLbackup@.123.com which has full rights to this share
> What is the best way to go arround:
> 1) try and make the backups work using SQLbackup@.123.com
> 2) or try and give SQLAgentServ@.abc.com access to the fileshare on
> fileserver.123.com
> Is any of these two option possible at all?
> are there better solutions with this setup?(we cannot change the domain
> setup and memberships, and we cannot at extra boxes)
> Kind regards
>
> Edward Dortland
> please reply to newsgroup ONLY
>
>
I have a sql server running in domain abc.com. de sqlserver and the sqlagent
services are running on their own useraccounts SqlServerServ@.abc.com and
SqlAgentServ@.abc.com. This server is in our own server room which we use to
host servers on our own domain 123.com.
the following task needs to be done:
make backups of the databases in sqlserver.abc.com to the file share on
fileserver.123.com by creating jobs in sqlagent.
What can not be done:
Make a trust between abc.com and 123.com since both companies don't trust
each other.
extra info.
sqlserver.abc.com
sql server 2000 standard SP3 running on
windows 2000 Standard edition (patches unknown)
this box happens to be the DC for abc.com as well (it's a test enviorment)
fileserver.123.com
windows 2000 standard edition (patches unknown)
this box is not the DC for 123.com
a share is create \\fileserver\DBbackups
and a user called SQLbackup@.123.com which has full rights to this share
What is the best way to go arround:
1) try and make the backups work using SQLbackup@.123.com
2) or try and give SQLAgentServ@.abc.com access to the fileshare on
fileserver.123.com
Is any of these two option possible at all?
are there better solutions with this setup?(we cannot change the domain
setup and memberships, and we cannot at extra boxes)
Kind regards
Edward Dortland
please reply to newsgroup ONLYSuggestions:
Install FTP server on fileserver.123.com, then
1. Do local backup on abc.com
2. Transfer these backups to fileserver.123.com using -s:filename option.
OR try this one (not sure if it will work).
1. Create LOCAL account SQLAgentServ on both computers with same password.
From Log shipping:
"Local Network Account
You can use SQL Server to start under a locally-created network account. In
the situation where there is network access required by a SQL Server
process, which is the case if you have configured SQL Server to use log
shipping, you can use network pass-through security. With pass-through
security, all machines that will be accessed by SQL Server must have the
same network account with the same password and appropriate permissions,
configured locally. Additionally, when the SQL Server process requests
resources from the second computer, traditional network security is bypassed
if the same account (under which the requesting SQL Server service is
started) exists with the same password. As long the account on the second
computer is configured with enough permission to carry out the task that is
requested by calling SQL Server, the task will be successful. "
2. Try to do backup.
HTH
Igor Raytsin
"Edward Dortland" <edwardNOSPAMMMM@.solsol.nl> wrote in message
news:104jovuket4qk0f@.corp.supernews.com...
> Hi,
> I have a sql server running in domain abc.com. de sqlserver and the
sqlagent
> services are running on their own useraccounts SqlServerServ@.abc.com and
> SqlAgentServ@.abc.com. This server is in our own server room which we use
to
> host servers on our own domain 123.com.
> the following task needs to be done:
> make backups of the databases in sqlserver.abc.com to the file share on
> fileserver.123.com by creating jobs in sqlagent.
> What can not be done:
> Make a trust between abc.com and 123.com since both companies don't trust
> each other.
> extra info.
> sqlserver.abc.com
> sql server 2000 standard SP3 running on
> Windows 2000 Standard edition (patches unknown)
> this box happens to be the DC for abc.com as well (it's a test enviorment)
> fileserver.123.com
> Windows 2000 standard edition (patches unknown)
> this box is not the DC for 123.com
> a share is create \\fileserver\DBbackups
> and a user called SQLbackup@.123.com which has full rights to this share
> What is the best way to go arround:
> 1) try and make the backups work using SQLbackup@.123.com
> 2) or try and give SQLAgentServ@.abc.com access to the fileshare on
> fileserver.123.com
> Is any of these two option possible at all?
> are there better solutions with this setup?(we cannot change the domain
> setup and memberships, and we cannot at extra boxes)
> Kind regards
>
> Edward Dortland
> please reply to newsgroup ONLY
>
>
dumping backups to share in different domain
Hi,
I have a sql server running in domain abc.com. de sqlserver and the sqlagent
services are running on their own useraccounts SqlServerServ@.abc.com and
SqlAgentServ@.abc.com. This server is in our own server room which we use to
host servers on our own domain 123.com.
the following task needs to be done:
make backups of the databases in sqlserver.abc.com to the file share on
fileserver.123.com by creating jobs in sqlagent.
What can not be done:
Make a trust between abc.com and 123.com since both companies don't trust
each other.
extra info.
sqlserver.abc.com
sql server 2000 standard SP3 running on
windows 2000 Standard edition (patches unknown)
this box happens to be the DC for abc.com as well (it's a test enviorment)
fileserver.123.com
windows 2000 standard edition (patches unknown)
this box is not the DC for 123.com
a share is create \\fileserver\DBbackups
and a user called SQLbackup@.123.com which has full rights to this share
What is the best way to go arround:
1) try and make the backups work using SQLbackup@.123.com
2) or try and give SQLAgentServ@.abc.com access to the fileshare on
fileserver.123.com
Is any of these two option possible at all?
are there better solutions with this setup?(we cannot change the domain
setup and memberships, and we cannot at extra boxes)
Kind regards
Edward Dortland
please reply to newsgroup ONLYSuggestions:
Install FTP server on fileserver.123.com, then
1. Do local backup on abc.com
2. Transfer these backups to fileserver.123.com using -s:filename option.
OR try this one (not sure if it will work).
1. Create LOCAL account SQLAgentServ on both computers with same password.
From Log shipping:
"Local Network Account
You can use SQL Server to start under a locally-created network account. In
the situation where there is network access required by a SQL Server
process, which is the case if you have configured SQL Server to use log
shipping, you can use network pass-through security. With pass-through
security, all machines that will be accessed by SQL Server must have the
same network account with the same password and appropriate permissions,
configured locally. Additionally, when the SQL Server process requests
resources from the second computer, traditional network security is bypassed
if the same account (under which the requesting SQL Server service is
started) exists with the same password. As long the account on the second
computer is configured with enough permission to carry out the task that is
requested by calling SQL Server, the task will be successful. "
2. Try to do backup.
HTH
Igor Raytsin
"Edward Dortland" <edwardNOSPAMMMM@.solsol.nl> wrote in message
news:104jovuket4qk0f@.corp.supernews.com...
> Hi,
> I have a sql server running in domain abc.com. de sqlserver and the
sqlagent
> services are running on their own useraccounts SqlServerServ@.abc.com and
> SqlAgentServ@.abc.com. This server is in our own server room which we use
to
> host servers on our own domain 123.com.
> the following task needs to be done:
> make backups of the databases in sqlserver.abc.com to the file share on
> fileserver.123.com by creating jobs in sqlagent.
> What can not be done:
> Make a trust between abc.com and 123.com since both companies don't trust
> each other.
> extra info.
> sqlserver.abc.com
> sql server 2000 standard SP3 running on
> windows 2000 Standard edition (patches unknown)
> this box happens to be the DC for abc.com as well (it's a test enviorment)
> fileserver.123.com
> windows 2000 standard edition (patches unknown)
> this box is not the DC for 123.com
> a share is create \\fileserver\DBbackups
> and a user called SQLbackup@.123.com which has full rights to this share
> What is the best way to go arround:
> 1) try and make the backups work using SQLbackup@.123.com
> 2) or try and give SQLAgentServ@.abc.com access to the fileshare on
> fileserver.123.com
> Is any of these two option possible at all?
> are there better solutions with this setup?(we cannot change the domain
> setup and memberships, and we cannot at extra boxes)
> Kind regards
>
> Edward Dortland
> please reply to newsgroup ONLY
>
>sql
I have a sql server running in domain abc.com. de sqlserver and the sqlagent
services are running on their own useraccounts SqlServerServ@.abc.com and
SqlAgentServ@.abc.com. This server is in our own server room which we use to
host servers on our own domain 123.com.
the following task needs to be done:
make backups of the databases in sqlserver.abc.com to the file share on
fileserver.123.com by creating jobs in sqlagent.
What can not be done:
Make a trust between abc.com and 123.com since both companies don't trust
each other.
extra info.
sqlserver.abc.com
sql server 2000 standard SP3 running on
windows 2000 Standard edition (patches unknown)
this box happens to be the DC for abc.com as well (it's a test enviorment)
fileserver.123.com
windows 2000 standard edition (patches unknown)
this box is not the DC for 123.com
a share is create \\fileserver\DBbackups
and a user called SQLbackup@.123.com which has full rights to this share
What is the best way to go arround:
1) try and make the backups work using SQLbackup@.123.com
2) or try and give SQLAgentServ@.abc.com access to the fileshare on
fileserver.123.com
Is any of these two option possible at all?
are there better solutions with this setup?(we cannot change the domain
setup and memberships, and we cannot at extra boxes)
Kind regards
Edward Dortland
please reply to newsgroup ONLYSuggestions:
Install FTP server on fileserver.123.com, then
1. Do local backup on abc.com
2. Transfer these backups to fileserver.123.com using -s:filename option.
OR try this one (not sure if it will work).
1. Create LOCAL account SQLAgentServ on both computers with same password.
From Log shipping:
"Local Network Account
You can use SQL Server to start under a locally-created network account. In
the situation where there is network access required by a SQL Server
process, which is the case if you have configured SQL Server to use log
shipping, you can use network pass-through security. With pass-through
security, all machines that will be accessed by SQL Server must have the
same network account with the same password and appropriate permissions,
configured locally. Additionally, when the SQL Server process requests
resources from the second computer, traditional network security is bypassed
if the same account (under which the requesting SQL Server service is
started) exists with the same password. As long the account on the second
computer is configured with enough permission to carry out the task that is
requested by calling SQL Server, the task will be successful. "
2. Try to do backup.
HTH
Igor Raytsin
"Edward Dortland" <edwardNOSPAMMMM@.solsol.nl> wrote in message
news:104jovuket4qk0f@.corp.supernews.com...
> Hi,
> I have a sql server running in domain abc.com. de sqlserver and the
sqlagent
> services are running on their own useraccounts SqlServerServ@.abc.com and
> SqlAgentServ@.abc.com. This server is in our own server room which we use
to
> host servers on our own domain 123.com.
> the following task needs to be done:
> make backups of the databases in sqlserver.abc.com to the file share on
> fileserver.123.com by creating jobs in sqlagent.
> What can not be done:
> Make a trust between abc.com and 123.com since both companies don't trust
> each other.
> extra info.
> sqlserver.abc.com
> sql server 2000 standard SP3 running on
> windows 2000 Standard edition (patches unknown)
> this box happens to be the DC for abc.com as well (it's a test enviorment)
> fileserver.123.com
> windows 2000 standard edition (patches unknown)
> this box is not the DC for 123.com
> a share is create \\fileserver\DBbackups
> and a user called SQLbackup@.123.com which has full rights to this share
> What is the best way to go arround:
> 1) try and make the backups work using SQLbackup@.123.com
> 2) or try and give SQLAgentServ@.abc.com access to the fileshare on
> fileserver.123.com
> Is any of these two option possible at all?
> are there better solutions with this setup?(we cannot change the domain
> setup and memberships, and we cannot at extra boxes)
> Kind regards
>
> Edward Dortland
> please reply to newsgroup ONLY
>
>sql
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
Subscribe to:
Posts (Atom)