scripting data

Hi,

I need to take data from a SQL Serer 2005 database, and load into a
remote 2000 database. I've already been able to script and create the
database objects (MS SQL Server 2005 has a nifty option which allows
you to scripting for SQL Server 2000 compliance). Now i just need to
get the data in.

Is there a tool or utility out there that i can use to generate insert
statements for all the tables in database?

Thanks much for any advice regarding this.On Feb 13, 6:07 am, "farseer" <fars...@.optonline.netwrote:

Quote:

Originally Posted by

Hi,
>
I need to take data from a SQL Serer 2005 database, and load into a
remote 2000 database. I've already been able to script and create the
database objects (MS SQL Server 2005 has a nifty option which allows
you to scripting for SQL Server 2000 compliance). Now i just need to
get the data in.
>
Is there a tool or utility out there that i can use to generate insert
statements for all the tables in database?
>
Thanks much for any advice regarding this.

Hi,

Yes, there is and it's called DB Ghost (www.dbghost.com). It has a
Data and Schema Scripter (which does what you need) and a host of
other tools that form a complete change management process for SQL
Server.and can all be called from the command line for full process
automation :)

Kind regards,

Malcolm|||farseer (farseer@.optonline.net) writes:

Quote:

Originally Posted by

Is there a tool or utility out there that i can use to generate insert
statements for all the tables in database?

SELECT 'bcp yourdb..' + name + ' out ' + name + '.bcp -n -T'
FROM sys.objects
WHERE type = 'U'

Copy result into a BAT file and run.

The to load:

SELECT 'bcp yourtargetdb..' + name + ' in ' + name + '.bcp -n -T ' +
'-S REMOTSER' +
CASE WHEN EXISTS (SELECT *
FROM sys.columns c
WHERE o.object_id = c.object_id
AND c.is_identity = 1)
THEN ' -E '
ELSE ''
END
FROM sys.objects o
WHERE type = 'U'

I assume that you are not using any data types that are not available
in SQL 2005.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

scripting data

Hi,
I need to take data from a SQL Serer 2005 database, and load into a
remote 2000 database. I've already been able to script and create the
database objects (MS SQL Server 2005 has a nifty option which allows
you to scripting for SQL Server 2000 compliance). Now i just need to
get the data in.
Is there a tool or utility out there that i can use to generate insert
statements for all the tables in database?
Thanks much for any advice regarding this.You could uses SSIS to transfer the data, however if you want to generate
insert scripts then take a look at sp_generate_inserts
http://vyaskn.tripod.com/code.htm#inserts
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"farseer" <farseer@.optonline.net> wrote in message
news:1171346776.733940.21960@.a75g2000cwd.googlegroups.com...
> Hi,
> I need to take data from a SQL Serer 2005 database, and load into a
> remote 2000 database. I've already been able to script and create the
> database objects (MS SQL Server 2005 has a nifty option which allows
> you to scripting for SQL Server 2000 compliance). Now i just need to
> get the data in.
> Is there a tool or utility out there that i can use to generate insert
> statements for all the tables in database?
> Thanks much for any advice regarding this.
>|||I've listed some tools here: http://www.karaszi.com/SQLServer/info_generate_script.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"farseer" <farseer@.optonline.net> wrote in message
news:1171346776.733940.21960@.a75g2000cwd.googlegroups.com...
> Hi,
> I need to take data from a SQL Serer 2005 database, and load into a
> remote 2000 database. I've already been able to script and create the
> database objects (MS SQL Server 2005 has a nifty option which allows
> you to scripting for SQL Server 2000 compliance). Now i just need to
> get the data in.
> Is there a tool or utility out there that i can use to generate insert
> statements for all the tables in database?
> Thanks much for any advice regarding this.
>|||Hi
You are able to create a linked server between both and share the data
"farseer" <farseer@.optonline.net> wrote in message
news:1171346776.733940.21960@.a75g2000cwd.googlegroups.com...
> Hi,
> I need to take data from a SQL Serer 2005 database, and load into a
> remote 2000 database. I've already been able to script and create the
> database objects (MS SQL Server 2005 has a nifty option which allows
> you to scripting for SQL Server 2000 compliance). Now i just need to
> get the data in.
> Is there a tool or utility out there that i can use to generate insert
> statements for all the tables in database?
> Thanks much for any advice regarding this.
>|||Try www.sqlscripter.com to script your data.
Its free.
"farseer" wrote:
> Hi,
> I need to take data from a SQL Serer 2005 database, and load into a
> remote 2000 database. I've already been able to script and create the
> database objects (MS SQL Server 2005 has a nifty option which allows
> you to scripting for SQL Server 2000 compliance). Now i just need to
> get the data in.
> Is there a tool or utility out there that i can use to generate insert
> statements for all the tables in database?
> Thanks much for any advice regarding this.
>

scripting data

Hi,
I need to take data from a SQL Serer 2005 database, and load into a
remote 2000 database. I've already been able to script and create the
database objects (MS SQL Server 2005 has a nifty option which allows
you to scripting for SQL Server 2000 compliance). Now i just need to
get the data in.
Is there a tool or utility out there that i can use to generate insert
statements for all the tables in database?
Thanks much for any advice regarding this.
You could uses SSIS to transfer the data, however if you want to generate
insert scripts then take a look at sp_generate_inserts
http://vyaskn.tripod.com/code.htm#inserts
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"farseer" <farseer@.optonline.net> wrote in message
news:1171346776.733940.21960@.a75g2000cwd.googlegro ups.com...
> Hi,
> I need to take data from a SQL Serer 2005 database, and load into a
> remote 2000 database. I've already been able to script and create the
> database objects (MS SQL Server 2005 has a nifty option which allows
> you to scripting for SQL Server 2000 compliance). Now i just need to
> get the data in.
> Is there a tool or utility out there that i can use to generate insert
> statements for all the tables in database?
> Thanks much for any advice regarding this.
>
|||Hi
You are able to create a linked server between both and share the data
"farseer" <farseer@.optonline.net> wrote in message
news:1171346776.733940.21960@.a75g2000cwd.googlegro ups.com...
> Hi,
> I need to take data from a SQL Serer 2005 database, and load into a
> remote 2000 database. I've already been able to script and create the
> database objects (MS SQL Server 2005 has a nifty option which allows
> you to scripting for SQL Server 2000 compliance). Now i just need to
> get the data in.
> Is there a tool or utility out there that i can use to generate insert
> statements for all the tables in database?
> Thanks much for any advice regarding this.
>
|||Try www.sqlscripter.com to script your data.
Its free.
"farseer" wrote:

> Hi,
> I need to take data from a SQL Serer 2005 database, and load into a
> remote 2000 database. I've already been able to script and create the
> database objects (MS SQL Server 2005 has a nifty option which allows
> you to scripting for SQL Server 2000 compliance). Now i just need to
> get the data in.
> Is there a tool or utility out there that i can use to generate insert
> statements for all the tables in database?
> Thanks much for any advice regarding this.
>

Script to peg all CPUs

I'd like to get a script/application that would peg all the CPUs. I just
want to do some load testing, but dont really know how to get my CPUs
pegged. Can someone help ?
I just want to max them out for like a couple of minutes.
And this should work on both 2000/2005
ThanksHi Hassan
Do you want to set the affinity mask using sp_configure? See
http://msdn2.microsoft.com/en-us/library/aa196717(SQL.80).aspx
John
"Hassan" wrote:

> I'd like to get a script/application that would peg all the CPUs. I just
> want to do some load testing, but dont really know how to get my CPUs
> pegged. Can someone help ?
> I just want to max them out for like a couple of minutes.
> And this should work on both 2000/2005
> Thanks
>
>|||No, I dont want to set the affinity mask. I just want to run a SQL that will
have my CPUs at 100% processor time.
This is for some testing..
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:E58A3C20-0813-443F-A773-F4B721363D1A@.microsoft.com...[vbcol=seagreen]
> Hi Hassan
> Do you want to set the affinity mask using sp_configure? See
> http://msdn2.microsoft.com/en-us/library/aa196717(SQL.80).aspx
> John
> "Hassan" wrote:
>|||Hi
I am not sure why SQL Server would have this inbuilt? Try dropping your
indexes and re-running a trace using multiple threads.
John
"Hassan" wrote:

> No, I dont want to set the affinity mask. I just want to run a SQL that wi
ll
> have my CPUs at 100% processor time.
> This is for some testing..
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:E58A3C20-0813-443F-A773-F4B721363D1A@.microsoft.com...
>
>

Script to peg all CPUs

I'd like to get a script/application that would peg all the CPUs. I just
want to do some load testing, but dont really know how to get my CPUs
pegged. Can someone help ?
I just want to max them out for like a couple of minutes.
And this should work on both 2000/2005
Thanks
Hi Hassan
Do you want to set the affinity mask using sp_configure? See
http://msdn2.microsoft.com/en-us/library/aa196717(SQL.80).aspx
John
"Hassan" wrote:

> I'd like to get a script/application that would peg all the CPUs. I just
> want to do some load testing, but dont really know how to get my CPUs
> pegged. Can someone help ?
> I just want to max them out for like a couple of minutes.
> And this should work on both 2000/2005
> Thanks
>
>
|||No, I dont want to set the affinity mask. I just want to run a SQL that will
have my CPUs at 100% processor time.
This is for some testing..
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:E58A3C20-0813-443F-A773-F4B721363D1A@.microsoft.com...[vbcol=seagreen]
> Hi Hassan
> Do you want to set the affinity mask using sp_configure? See
> http://msdn2.microsoft.com/en-us/library/aa196717(SQL.80).aspx
> John
> "Hassan" wrote:
|||Hi
I am not sure why SQL Server would have this inbuilt? Try dropping your
indexes and re-running a trace using multiple threads.
John
"Hassan" wrote:

> No, I dont want to set the affinity mask. I just want to run a SQL that will
> have my CPUs at 100% processor time.
> This is for some testing..
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:E58A3C20-0813-443F-A773-F4B721363D1A@.microsoft.com...
>
>

Script to peg all CPUs

I'd like to get a script/application that would peg all the CPUs. I just
want to do some load testing, but dont really know how to get my CPUs
pegged. Can someone help ?
I just want to max them out for like a couple of minutes.
And this should work on both 2000/2005
ThanksNo, I dont want to set the affinity mask. I just want to run a SQL that will
have my CPUs at 100% processor time.
This is for some testing..
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:E58A3C20-0813-443F-A773-F4B721363D1A@.microsoft.com...
> Hi Hassan
> Do you want to set the affinity mask using sp_configure? See
> http://msdn2.microsoft.com/en-us/library/aa196717(SQL.80).aspx
> John
> "Hassan" wrote:
>> I'd like to get a script/application that would peg all the CPUs. I just
>> want to do some load testing, but dont really know how to get my CPUs
>> pegged. Can someone help ?
>> I just want to max them out for like a couple of minutes.
>> And this should work on both 2000/2005
>> Thanks
>>

Script to create and load a database

Dear all,
I am very new to SQL Server but must say I find it and the GUI tools
very good.
I am now in a position where I need to supply a client with a script
file to create a database, create tables within it and then load the
tables.
I have a script that will create the tables and also have exported the
contents to comma separated files.
If someone could supply a sample set of script commands that would:
create a standard database
creat a user in it called "foo" and make this user the db owner
create tables owned by foo
load the data from an export file
I would be very grateful.
Also, how would I run such a script.
Thanks in advance
Ed
PS I am willing to learn - if there is a page or manual where I can
find this out myself tell me to RTFM ;-) (But please let me know where
it is!)> If someone could supply a sample set of script commands that would: (...)
> PS I am willing to learn - if there is a page or manual where I can
> find this out myself tell me to RTFM ;-) (But please let me know where
> it is!)
See SQL Server manual - "SQL Books Online" \Microsoft SQL
Server\80\Tools\BOOKS\SQL80.col
Online MSDN resources on SQL2k -
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnanchor/html/sqlserver2000.asp
> Also, how would I run such a script.
In SQL Query Analyzer
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Script to create and load a database

Dear all,
I am very new to SQL Server but must say I find it and the GUI tools
very good.
I am now in a position where I need to supply a client with a script
file to create a database, create tables within it and then load the
tables.
I have a script that will create the tables and also have exported the
contents to comma separated files.
If someone could supply a sample set of script commands that would:
create a standard database
creat a user in it called "foo" and make this user the db owner
create tables owned by foo
load the data from an export file
I would be very grateful.
Also, how would I run such a script.
Thanks in advance
Ed
PS I am willing to learn - if there is a page or manual where I can
find this out myself tell me to RTFM ;-) (But please let me know where
it is!)> If someone could supply a sample set of script commands that would: (...)
> PS I am willing to learn - if there is a page or manual where I can
> find this out myself tell me to RTFM ;-) (But please let me know where
> it is!)
See SQL Server manual - "SQL Books Online" \Microsoft SQL
Server\80\Tools\BOOKS\SQL80.col
Online MSDN resources on SQL2k -
http://msdn.microsoft.com/library/d...
server2000.asp

> Also, how would I run such a script.
In SQL Query Analyzer
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Script files failed to load error

Hi all,

I have a package with a number of script components. All are set "PreCompile=True".

Within a Sequence Container I have a set of five script tasks each followed by an Execute Process Task. Each pair is linked but the five pairs are independent (hope this makes sense).

The first pair executed successfully but the other four all failed with the "Script files failed to load" error.

Anyone have a clue why this might happen or what I might change to solve the problem?

The package is scheduled under SS Agent so I'm currently thinking of taking these steps out of SSIS and putting them into the job. Not ideal but at least I can have some confidence that it will work.

Any suggestions would be very welcome.

Cheers,

Andrew

Go into the script tasks, click "Design Script...", when VBA opens close it down again.

This will *hopefully* recompile the binary code and the problem *should* go away.

If not, there may be something else going on here.

-Jamie

|||

Hmmm,

Thanks for the suggestion. I'll give it a try but I suspect it may be a "something else". If I copy the dtsx package to another machine and run it through dtexec the problem doesn't occur.

It's probably more of a concern if this does work as it suggests that within a package there's no guarantee that a given script component is compiled when the package is deployed.

Andrew

|||

Did you ever get a resolution to this problem?

I am having a similiar issue. I am executing several SSIS packages from within a SSIS package Script file (PreCompileScriptIntoBinaryCode=True) based on package names in a table.

What makes my problem similiar is, it is always the 3rd package called that fails with this error. These packages also have Script files (PreCompileScriptIntoBinaryCode=True).

This happens not only when called from Sql Server Agent, but also when run from within BIDS.

|||Anyone figure this out? My script task is failing only when run under SQL Server Agent as a scheduled job.|||

I don't have a resolution but it hasn't happened lately.

Sorry not to be more helpful.

I'm not sure what's going to happen when the package needs changing.

Andrew

|||

In my case, it seemed that I had a stop for debugging within my script. When the package was running on the server it was failing with this error message, no problems when running on the client. So check you don't have any breaks within your script and it may do the trick!.

Panos.

|||

For what it's worth, I had the exact problem. Only failing under Sql Agent with "Script files failed to load".

I took Jamie's advice above, opened my script tasks and I clicked save. Redeployed the package and it worked.

I am running on 64-bit itaniums w/sp1. Also, I am running the job under the 'sa' account.

|||

I had the same problem and followed Jamie suggestion. It worked!!

I had copied sever scripts to my package and had not opened and saved to auto-recompile.

Thanks Jamie!

Script files failed to load error

Hi all,

I have a package with a number of script components. All are set "PreCompile=True".

Within a Sequence Container I have a set of five script tasks each followed by an Execute Process Task. Each pair is linked but the five pairs are independent (hope this makes sense).

The first pair executed successfully but the other four all failed with the "Script files failed to load" error.

Anyone have a clue why this might happen or what I might change to solve the problem?

The package is scheduled under SS Agent so I'm currently thinking of taking these steps out of SSIS and putting them into the job. Not ideal but at least I can have some confidence that it will work.

Any suggestions would be very welcome.

Cheers,

Andrew

Go into the script tasks, click "Design Script...", when VBA opens close it down again.

This will *hopefully* recompile the binary code and the problem *should* go away.

If not, there may be something else going on here.

-Jamie

|||

Hmmm,

Thanks for the suggestion. I'll give it a try but I suspect it may be a "something else". If I copy the dtsx package to another machine and run it through dtexec the problem doesn't occur.

It's probably more of a concern if this does work as it suggests that within a package there's no guarantee that a given script component is compiled when the package is deployed.

Andrew

|||

Did you ever get a resolution to this problem?

I am having a similiar issue. I am executing several SSIS packages from within a SSIS package Script file (PreCompileScriptIntoBinaryCode=True) based on package names in a table.

What makes my problem similiar is, it is always the 3rd package called that fails with this error. These packages also have Script files (PreCompileScriptIntoBinaryCode=True).

This happens not only when called from Sql Server Agent, but also when run from within BIDS.

|||Anyone figure this out? My script task is failing only when run under SQL Server Agent as a scheduled job.|||

I don't have a resolution but it hasn't happened lately.

Sorry not to be more helpful.

I'm not sure what's going to happen when the package needs changing.

Andrew

|||

In my case, it seemed that I had a stop for debugging within my script. When the package was running on the server it was failing with this error message, no problems when running on the client. So check you don't have any breaks within your script and it may do the trick!.

Panos.

|||

For what it's worth, I had the exact problem. Only failing under Sql Agent with "Script files failed to load".

I took Jamie's advice above, opened my script tasks and I clicked save. Redeployed the package and it worked.

I am running on 64-bit itaniums w/sp1. Also, I am running the job under the 'sa' account.

|||

I had the same problem and followed Jamie suggestion. It worked!!

I had copied sever scripts to my package and had not opened and saved to auto-recompile.

Thanks Jamie!

Script files failed to load error

Hi all,

I have a package with a number of script components. All are set "PreCompile=True".

Within a Sequence Container I have a set of five script tasks each followed by an Execute Process Task. Each pair is linked but the five pairs are independent (hope this makes sense).

The first pair executed successfully but the other four all failed with the "Script files failed to load" error.

Anyone have a clue why this might happen or what I might change to solve the problem?

The package is scheduled under SS Agent so I'm currently thinking of taking these steps out of SSIS and putting them into the job. Not ideal but at least I can have some confidence that it will work.

Any suggestions would be very welcome.

Cheers,

Andrew

Go into the script tasks, click "Design Script...", when VBA opens close it down again.

This will *hopefully* recompile the binary code and the problem *should* go away.

If not, there may be something else going on here.

-Jamie

|||

Hmmm,

Thanks for the suggestion. I'll give it a try but I suspect it may be a "something else". If I copy the dtsx package to another machine and run it through dtexec the problem doesn't occur.

It's probably more of a concern if this does work as it suggests that within a package there's no guarantee that a given script component is compiled when the package is deployed.

Andrew

|||

Did you ever get a resolution to this problem?

I am having a similiar issue. I am executing several SSIS packages from within a SSIS package Script file (PreCompileScriptIntoBinaryCode=True) based on package names in a table.

What makes my problem similiar is, it is always the 3rd package called that fails with this error. These packages also have Script files (PreCompileScriptIntoBinaryCode=True).

This happens not only when called from Sql Server Agent, but also when run from within BIDS.

|||Anyone figure this out? My script task is failing only when run under SQL Server Agent as a scheduled job.|||

I don't have a resolution but it hasn't happened lately.

Sorry not to be more helpful.

I'm not sure what's going to happen when the package needs changing.

Andrew

|||

In my case, it seemed that I had a stop for debugging within my script. When the package was running on the server it was failing with this error message, no problems when running on the client. So check you don't have any breaks within your script and it may do the trick!.

Panos.

|||

For what it's worth, I had the exact problem. Only failing under Sql Agent with "Script files failed to load".

I took Jamie's advice above, opened my script tasks and I clicked save. Redeployed the package and it worked.

I am running on 64-bit itaniums w/sp1. Also, I am running the job under the 'sa' account.

|||

I had the same problem and followed Jamie suggestion. It worked!!

I had copied sever scripts to my package and had not opened and saved to auto-recompile.

Thanks Jamie!

Script files failed to load error

Hi all,

I have a package with a number of script components. All are set "PreCompile=True".

Within a Sequence Container I have a set of five script tasks each followed by an Execute Process Task. Each pair is linked but the five pairs are independent (hope this makes sense).

The first pair executed successfully but the other four all failed with the "Script files failed to load" error.

Anyone have a clue why this might happen or what I might change to solve the problem?

The package is scheduled under SS Agent so I'm currently thinking of taking these steps out of SSIS and putting them into the job. Not ideal but at least I can have some confidence that it will work.

Any suggestions would be very welcome.

Cheers,

Andrew

Go into the script tasks, click "Design Script...", when VBA opens close it down again.

This will *hopefully* recompile the binary code and the problem *should* go away.

If not, there may be something else going on here.

-Jamie

|||

Hmmm,

Thanks for the suggestion. I'll give it a try but I suspect it may be a "something else". If I copy the dtsx package to another machine and run it through dtexec the problem doesn't occur.

It's probably more of a concern if this does work as it suggests that within a package there's no guarantee that a given script component is compiled when the package is deployed.

Andrew

|||

Did you ever get a resolution to this problem?

I am having a similiar issue. I am executing several SSIS packages from within a SSIS package Script file (PreCompileScriptIntoBinaryCode=True) based on package names in a table.

What makes my problem similiar is, it is always the 3rd package called that fails with this error. These packages also have Script files (PreCompileScriptIntoBinaryCode=True).

This happens not only when called from Sql Server Agent, but also when run from within BIDS.

|||Anyone figure this out? My script task is failing only when run under SQL Server Agent as a scheduled job.|||

I don't have a resolution but it hasn't happened lately.

Sorry not to be more helpful.

I'm not sure what's going to happen when the package needs changing.

Andrew

|||

In my case, it seemed that I had a stop for debugging within my script. When the package was running on the server it was failing with this error message, no problems when running on the client. So check you don't have any breaks within your script and it may do the trick!.

Panos.

|||

For what it's worth, I had the exact problem. Only failing under Sql Agent with "Script files failed to load".

I took Jamie's advice above, opened my script tasks and I clicked save. Redeployed the package and it worked.

I am running on 64-bit itaniums w/sp1. Also, I am running the job under the 'sa' account.

|||

I had the same problem and followed Jamie suggestion. It worked!!

I had copied sever scripts to my package and had not opened and saved to auto-recompile.

Thanks Jamie!