Need to copy All oracle tables to SQL Server 2005

I am racking my brain on this one. I am migrating Oracle data to SQL Server and would like an SSIS package that will copy the oracle data from every table to SQL server. I already have the schema set up in SQL and a straight copy of a specified table works fine between the OLEDB source and OLEDB destination. However, when I set up a loop which sets a global variable for the table names, which are identical on both dbs, and try to set the OLEDB Source and Destination OpenRowsetVariable to the global variable, the process fails with these errors with all validation turned off:

[OLE DB Source [18]] Warning: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.

[OLE DB Source [18]] Error: Column "ID" cannot be found at the datasource.

[DTS.Pipeline] Error: component "OLE DB Source" (18) failed the pre-execute phase and returned error code 0xC0202005.

To sum up, I want to set the tables dynamically and have them infer the mappings. The names of the columns are already the exact same on each schema, and the data types are mapped accordingly. I have already verified that the names of the tables have been formatted correctly for each database. If I can't do it this way then how?

You can't change the source at run-time since the Meta-data for the columns is static.

To do a copy like this, you need to have a seperate source and destination for each table. The easiest way to do this is to use the Import/Export wizard in 2005 to generate the package. Select the target database in SSMS, right click it and pick Tasks/Import data to run the wizard. it should be pretty self-explanatory, but, if you have trouble, there is a good section on it in BOL.

|||

swatts777 wrote:

[OLE DB Source [18]] Warning: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.

This warning can easily be fixed by setting AlwaysUseDefaultCodePage=TRUE on the OLE DB Source component.

swatts777 wrote:

To sum up, I want to set the tables dynamically and have them infer the mappings. The names of the columns are already the exact same on each schema, and the data types are mapped accordingly. I have already verified that the names of the tables have been formatted correctly for each database. If I can't do it this way then how?

SSIS won't do this. I'm worried that you might have read some documentation somewhere that led you think that it will. Is that the case?

Like David said, you have to build a data-flow for each table. You could make it easy on yourself and get the import/export wizard to build it for you.

-Jamie

-Jamie

|||

Apparently, my Oracle Provider wasn't registered in SQL, so I did not know I had this option avalable to me. Using Microsoft's OLEDB Provider for Oracle only lets you pull data over using custom queries. I restarted SQL and my oracle provider showed up. WOW! Thanks for all your help, this worked great! I was able to go through each table and map datatypes and everything. Then I saved the whole thing as an SSIS package for use when we go live.

|||

Hi swatts777,

Can you(or anyone else) tell me how to register an Oracle provider as you described above?
i am having the same endless shyte with data types when pulling single values from Oracle.

Anybody please help me

Regards,

Pieter

|||

I think yhe answer is right there...

swatts777 wrote:

I restarted SQL and my oracle provider showed up.

|||

Hi Rafael,

It seems pretty much easy doesn't it, but I'm still not sure if I should have a new version of Oracle installed before I restart my SQL server or install just a driver or what?

Maybe I should change my name to StupidPete?

Regards,

Pieter

|||

Peter,

After installing the Oracle client, you must set up Oracle Net services to point to the correct Oracle database. There are plenty of articles on doing this. You should have installed the Client using Oracle Universal Installer that comes with the client you are using and that you downloaded from Oracle's site. If you have done all of this, then you can restart the machine. That is the best way. If you are using SQL Server 2005, under Server Objects>Linked Servers>Providers you should now see a OraOLEDB.Oracle provider listed. Now you may create your Linked server using this provider instead of MSADORA.

Hope this helps,

Shawn

|||

CleverPete wrote:

Hi Rafael,

It seems pretty much easy doesn't it, but I'm still not sure if I should have a new version of Oracle installed before I restart my SQL server or install just a driver or what?

Maybe I should change my name to StupidPete?

Regards,

Pieter

This is a perfect example of how a flamming thread starts and how they get deleted/locked. I just wanted to point out a detail that you could have miss. BTW, that was quite the answer; just install it and re-start the machine.

Rafael Salas wrote:

I think yhe answer is right there...

swatts777 wrote:

I restarted SQL and my oracle provider showed up.

|||I wasn't really focused on the questions being asked, rather my on experiences in dealing with the problems I faced. I will try to answer questions more precise and pay more attention to the actual questions being posted.|||

I am trying to do the same exact thing, but with a CACHE database, not oracle. My issue is that SSIS will not dynamically map the columns from each new table that I setup in a foreach loop. I do not want to create a separate design time mapping for each table (there are lots of tables). If I cannot figure this out, I will use OPENQUERY with a linked server, and set the OPENQUERY string dynamically in a loop. Unless I misunderstood, you are not dynamically writing to tables with different structures, correct? If so, how did you get that to work?

|||

Dave Dumas wrote:

I am trying to do the same exact thing, but with a CACHE database, not oracle. My issue is that SSIS will not dynamically map the columns from each new table that I setup in a foreach loop.

You're right, it doesn't do this. You can't do what you are attempting unless the columns in all the source tables are the same.

Dave Dumas wrote:

I do not want to create a separate design time mapping for each table (there are lots of tables).

Unfortunately if you want to use data-flows this is your only option.

Dave Dumas wrote:

If I cannot figure this out, I will use OPENQUERY with a linked server, and set the OPENQUERY string dynamically in a loop.

Good idea that. Let us know how it goes.

-Jamie

|||

Thanks Jamie. What I did (since the OPENQUERY syntax does not all variables, only strings), was to write a select statement with dynamic sql as output. I outputted 300 OPENQUERY statements inside of try-catch BEGIN END blocks. This worked great. - Dave

Need to copy All oracle tables to SQL Server 2005

I am racking my brain on this one. I am migrating Oracle data to SQL Server and would like an SSIS package that will copy the oracle data from every table to SQL server. I already have the schema set up in SQL and a straight copy of a specified table works fine between the OLEDB source and OLEDB destination. However, when I set up a loop which sets a global variable for the table names, which are identical on both dbs, and try to set the OLEDB Source and Destination OpenRowsetVariable to the global variable, the process fails with these errors with all validation turned off:

[OLE DB Source [18]] Warning: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.

[OLE DB Source [18]] Error: Column "ID" cannot be found at the datasource.

[DTS.Pipeline] Error: component "OLE DB Source" (18) failed the pre-execute phase and returned error code 0xC0202005.

To sum up, I want to set the tables dynamically and have them infer the mappings. The names of the columns are already the exact same on each schema, and the data types are mapped accordingly. I have already verified that the names of the tables have been formatted correctly for each database. If I can't do it this way then how?

You can't change the source at run-time since the Meta-data for the columns is static.

To do a copy like this, you need to have a seperate source and destination for each table. The easiest way to do this is to use the Import/Export wizard in 2005 to generate the package. Select the target database in SSMS, right click it and pick Tasks/Import data to run the wizard. it should be pretty self-explanatory, but, if you have trouble, there is a good section on it in BOL.

|||

swatts777 wrote:

[OLE DB Source [18]] Warning: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.

This warning can easily be fixed by setting AlwaysUseDefaultCodePage=TRUE on the OLE DB Source component.

swatts777 wrote:

To sum up, I want to set the tables dynamically and have them infer the mappings. The names of the columns are already the exact same on each schema, and the data types are mapped accordingly. I have already verified that the names of the tables have been formatted correctly for each database. If I can't do it this way then how?

SSIS won't do this. I'm worried that you might have read some documentation somewhere that led you think that it will. Is that the case?

Like David said, you have to build a data-flow for each table. You could make it easy on yourself and get the import/export wizard to build it for you.

-Jamie

-Jamie

|||

Apparently, my Oracle Provider wasn't registered in SQL, so I did not know I had this option avalable to me. Using Microsoft's OLEDB Provider for Oracle only lets you pull data over using custom queries. I restarted SQL and my oracle provider showed up. WOW! Thanks for all your help, this worked great! I was able to go through each table and map datatypes and everything. Then I saved the whole thing as an SSIS package for use when we go live.

|||

Hi swatts777,

Can you(or anyone else) tell me how to register an Oracle provider as you described above?
i am having the same endless shyte with data types when pulling single values from Oracle.

Anybody please help me

Regards,

Pieter

|||

I think yhe answer is right there...

swatts777 wrote:

I restarted SQL and my oracle provider showed up.

|||

Hi Rafael,

It seems pretty much easy doesn't it, but I'm still not sure if I should have a new version of Oracle installed before I restart my SQL server or install just a driver or what?

Maybe I should change my name to StupidPete?

Regards,

Pieter

|||

Peter,

After installing the Oracle client, you must set up Oracle Net services to point to the correct Oracle database. There are plenty of articles on doing this. You should have installed the Client using Oracle Universal Installer that comes with the client you are using and that you downloaded from Oracle's site. If you have done all of this, then you can restart the machine. That is the best way. If you are using SQL Server 2005, under Server Objects>Linked Servers>Providers you should now see a OraOLEDB.Oracle provider listed. Now you may create your Linked server using this provider instead of MSADORA.

Hope this helps,

Shawn

|||

CleverPete wrote:

Hi Rafael,

It seems pretty much easy doesn't it, but I'm still not sure if I should have a new version of Oracle installed before I restart my SQL server or install just a driver or what?

Maybe I should change my name to StupidPete?

Regards,

Pieter

This is a perfect example of how a flamming thread starts and how they get deleted/locked. I just wanted to point out a detail that you could have miss. BTW, that was quite the answer; just install it and re-start the machine.

Rafael Salas wrote:

I think yhe answer is right there...

swatts777 wrote:

I restarted SQL and my oracle provider showed up.

|||I wasn't really focused on the questions being asked, rather my on experiences in dealing with the problems I faced. I will try to answer questions more precise and pay more attention to the actual questions being posted.|||

I am trying to do the same exact thing, but with a CACHE database, not oracle. My issue is that SSIS will not dynamically map the columns from each new table that I setup in a foreach loop. I do not want to create a separate design time mapping for each table (there are lots of tables). If I cannot figure this out, I will use OPENQUERY with a linked server, and set the OPENQUERY string dynamically in a loop. Unless I misunderstood, you are not dynamically writing to tables with different structures, correct? If so, how did you get that to work?

|||

Dave Dumas wrote:

I am trying to do the same exact thing, but with a CACHE database, not oracle. My issue is that SSIS will not dynamically map the columns from each new table that I setup in a foreach loop.

You're right, it doesn't do this. You can't do what you are attempting unless the columns in all the source tables are the same.

Dave Dumas wrote:

I do not want to create a separate design time mapping for each table (there are lots of tables).

Unfortunately if you want to use data-flows this is your only option.

Dave Dumas wrote:

If I cannot figure this out, I will use OPENQUERY with a linked server, and set the OPENQUERY string dynamically in a loop.

Good idea that. Let us know how it goes.

-Jamie

|||

Thanks Jamie. What I did (since the OPENQUERY syntax does not all variables, only strings), was to write a select statement with dynamic sql as output. I outputted 300 OPENQUERY statements inside of try-catch BEGIN END blocks. This worked great. - Dave

Need to copy a database in the same server without using backup re

Hi,
I need to copy a database 'management' in the same server and name it as management_mid. Is there any command or script to copy the database without
using the backup restore operation? Thanks in advance.
Use a DTS package.
Bobby Ryzhy
bobby@. name of domain below
http://www.weekendtech.net
On Tue, 6 Jul 2004 15:34:01 -0700, "Jack"
<Jack@.discussions.microsoft.com> wrote:

>Hi,
>I need to copy a database 'management' in the same server and name it as management_mid. Is there any command or script to copy the database without
>using the backup restore operation? Thanks in advance.
|||WHat is wrong with Backup and Restore? You can Restore to a different name
and location. You can also try sp_attach_db.
Andrew J. Kelly SQL MVP
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:4B8B3A20-69CC-46F2-A9C3-3009FE770FEE@.microsoft.com...
> Hi,
> I need to copy a database 'management' in the same server and name it as
management_mid. Is there any command or script to copy the database without
> using the backup restore operation? Thanks in advance.
|||Hi,
Use this below list of commands, But it require a minutes down time for
actual database.
sp_detach_db management
go
copy the MDF and LDF file for management database to a different folder
go
-- attach the original database management
sp_attach_db
'management','physical_mdf_file_with_path','plysic al_ldf_with_path'
go
--attach the new database
sp_attach_db
'management_mid','physical_mdf_file_with_path','pl ysical_ldf_with_path'
Note:
DTS/BCP are also possible , but will take long time. It requires some manual
tasks as well.
Thanks
Hari
MCDBA
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:4B8B3A20-69CC-46F2-A9C3-3009FE770FEE@.microsoft.com...
> Hi,
> I need to copy a database 'management' in the same server and name it as
management_mid. Is there any command or script to copy the database without
> using the backup restore operation? Thanks in advance.
sql

Need to copy a database in the same server without using backup re

Hi,
I need to copy a database 'management' in the same server and name it as management_mid. Is there any command or script to copy the database without
using the backup restore operation? Thanks in advance.Use a DTS package.
Bobby Ryzhy
bobby@. name of domain below
http://www.weekendtech.net
On Tue, 6 Jul 2004 15:34:01 -0700, "Jack"
<Jack@.discussions.microsoft.com> wrote:
>Hi,
>I need to copy a database 'management' in the same server and name it as management_mid. Is there any command or script to copy the database without
>using the backup restore operation? Thanks in advance.|||WHat is wrong with Backup and Restore? You can Restore to a different name
and location. You can also try sp_attach_db.
--
Andrew J. Kelly SQL MVP
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:4B8B3A20-69CC-46F2-A9C3-3009FE770FEE@.microsoft.com...
> Hi,
> I need to copy a database 'management' in the same server and name it as
management_mid. Is there any command or script to copy the database without
> using the backup restore operation? Thanks in advance.|||Hi,
Use this below list of commands, But it require a minutes down time for
actual database.
sp_detach_db management
go
copy the MDF and LDF file for management database to a different folder
go
-- attach the original database management
sp_attach_db
'management','physical_mdf_file_with_path','plysical_ldf_with_path'
go
--attach the new database
sp_attach_db
'management_mid','physical_mdf_file_with_path','plysical_ldf_with_path'
Note:
DTS/BCP are also possible , but will take long time. It requires some manual
tasks as well.
--
Thanks
Hari
MCDBA
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:4B8B3A20-69CC-46F2-A9C3-3009FE770FEE@.microsoft.com...
> Hi,
> I need to copy a database 'management' in the same server and name it as
management_mid. Is there any command or script to copy the database without
> using the backup restore operation? Thanks in advance.

Need to copy a database in the same server without using backup re

Hi,
I need to copy a database 'management' in the same server and name it as man
agement_mid. Is there any command or script to copy the database without
using the backup restore operation? Thanks in advance.Use a DTS package.
Bobby Ryzhy
bobby@. name of domain below
http://www.weekendtech.net
On Tue, 6 Jul 2004 15:34:01 -0700, "Jack"
<Jack@.discussions.microsoft.com> wrote:

>Hi,
>I need to copy a database 'management' in the same server and name it as ma
nagement_mid. Is there any command or script to copy the database without
>using the backup restore operation? Thanks in advance.|||WHat is wrong with Backup and Restore? You can Restore to a different name
and location. You can also try sp_attach_db.
Andrew J. Kelly SQL MVP
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:4B8B3A20-69CC-46F2-A9C3-3009FE770FEE@.microsoft.com...
> Hi,
> I need to copy a database 'management' in the same server and name it as
management_mid. Is there any command or script to copy the database without
> using the backup restore operation? Thanks in advance.|||Hi,
Use this below list of commands, But it require a minutes down time for
actual database.
sp_detach_db management
go
copy the MDF and LDF file for management database to a different folder
go
-- attach the original database management
sp_attach_db
'management','physical_mdf_file_with_pat
h','plysical_ldf_with_path'
go
--attach the new database
sp_attach_db
'management_mid','physical_mdf_file_with
_path','plysical_ldf_with_path'
Note:
DTS/BCP are also possible , but will take long time. It requires some manual
tasks as well.
--
Thanks
Hari
MCDBA
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:4B8B3A20-69CC-46F2-A9C3-3009FE770FEE@.microsoft.com...
> Hi,
> I need to copy a database 'management' in the same server and name it as
management_mid. Is there any command or script to copy the database without
> using the backup restore operation? Thanks in advance.

Need to cleardown a table due to disk space problems

I need to delete about 3 million rows from a table that is part of a merge
publication. I think that I will have to copy out the rows that I want to
keep into a temp table and truncate the table and then copy the rows back in.
My question is how best to go about this? I think that the best way is to go
into the publication properties and uncheck the table on the articles tab.
Then to carry out the same process of copying out the data to be kept,
truncate the table, then move the rows to be kept back in. Then add the
article back into the publication. In order for the article to replicated
after adding it back in would I have to do a snapshot or would it resume by
itself?
Russell,
this sounds OK. However if the publication already has a subscription, you
won't be able to remove the individual article and you'll have to drop the
entire subscription before proceeding. You could drop the subscription,
remove the rows on publisher and subscriber then do a nosync initialization.
HTH
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Need to back up a database and then restore it on another server

I need to back up a production database on one server and then copy it to a test server wherel it will be restored. The problem is that I don't know where to move the file to on the production server before I restore it. Is there a special folder I need to copy the file to? I see a folder named ProgramFiles\Microsoft SQL Server but beyond that I have no idea where to copy the file so that it can be restored?

Can someone help me out please?

After backing up your database to file on your production server, you should move the .bak file to your test server for restore. It does not matter where you put it on the test server as you will be able to browse for it when you actually perform the restore.

need stored proc to append table 1 to table 2

HI just wondering if there is a simple transact statement to use to copy the
contents of table one and append it to a second table. Each table has the
same columns and datatypes, thanks.
Paul G
Software engineer.
Here's the books online example from the INSERT...SELECT topic.
USE pubs
INSERT INTO mybooks
SELECT title_id
, title
, TYPE
FROM titles
WHERE TYPE = 'mod_cook'
|||Paul wrote:
> HI just wondering if there is a simple transact statement to use to
> copy the contents of table one and append it to a second table. Each
> table has the same columns and datatypes, thanks.
In order to have SQL Server create the destination table, use
SELECT...INTO
Select
id,
type,
name
Into
dbo.MyObjects
From
dbo.sysobjects
Where
id < 100
Select * from dbo.MyObjects
Drop Table dbo.MyObjects
David Gugick - SQL Server MVP
Quest Software
|||ok thanks.
Paul G
Software engineer.
"David Gugick" wrote:

> Paul wrote:
> In order to have SQL Server create the destination table, use
> SELECT...INTO
> Select
> id,
> type,
> name
> Into
> dbo.MyObjects
> From
> dbo.sysobjects
> Where
> id < 100
> Select * from dbo.MyObjects
> Drop Table dbo.MyObjects
>
> --
> David Gugick - SQL Server MVP
> Quest Software
>

need stored proc to append table 1 to table 2

HI just wondering if there is a simple transact statement to use to copy the
contents of table one and append it to a second table. Each table has the
same columns and datatypes, thanks.
--
Paul G
Software engineer.Here's the books online example from the INSERT...SELECT topic.
USE pubs
INSERT INTO mybooks
SELECT title_id
, title
, TYPE
FROM titles
WHERE TYPE = 'mod_cook'|||Paul wrote:
> HI just wondering if there is a simple transact statement to use to
> copy the contents of table one and append it to a second table. Each
> table has the same columns and datatypes, thanks.
In order to have SQL Server create the destination table, use
SELECT...INTO
Select
id,
type,
name
Into
dbo.MyObjects
From
dbo.sysobjects
Where
id < 100
Select * from dbo.MyObjects
Drop Table dbo.MyObjects
David Gugick - SQL Server MVP
Quest Software|||ok thanks.
--
Paul G
Software engineer.
"David Gugick" wrote:

> Paul wrote:
> In order to have SQL Server create the destination table, use
> SELECT...INTO
> Select
> id,
> type,
> name
> Into
> dbo.MyObjects
> From
> dbo.sysobjects
> Where
> id < 100
> Select * from dbo.MyObjects
> Drop Table dbo.MyObjects
>
> --
> David Gugick - SQL Server MVP
> Quest Software
>

need stored proc to append table 1 to table 2

HI just wondering if there is a simple transact statement to use to copy the
contents of table one and append it to a second table. Each table has the
same columns and datatypes, thanks.
--
Paul G
Software engineer.Here's the books online example from the INSERT...SELECT topic.
USE pubs
INSERT INTO mybooks
SELECT title_id
, title
, TYPE
FROM titles
WHERE TYPE = 'mod_cook'|||Paul wrote:
> HI just wondering if there is a simple transact statement to use to
> copy the contents of table one and append it to a second table. Each
> table has the same columns and datatypes, thanks.
In order to have SQL Server create the destination table, use
SELECT...INTO
Select
id,
type,
name
Into
dbo.MyObjects
From
dbo.sysobjects
Where
id < 100
Select * from dbo.MyObjects
Drop Table dbo.MyObjects
David Gugick - SQL Server MVP
Quest Software|||ok thanks.
--
Paul G
Software engineer.
"David Gugick" wrote:
> Paul wrote:
> > HI just wondering if there is a simple transact statement to use to
> > copy the contents of table one and append it to a second table. Each
> > table has the same columns and datatypes, thanks.
> In order to have SQL Server create the destination table, use
> SELECT...INTO
> Select
> id,
> type,
> name
> Into
> dbo.MyObjects
> From
> dbo.sysobjects
> Where
> id < 100
> Select * from dbo.MyObjects
> Drop Table dbo.MyObjects
>
> --
> David Gugick - SQL Server MVP
> Quest Software
>

Need SQL 7 Enterprise Manager

Does anyone know where I can get a copy of Enterprise Manager? I have the trial copy of 2000, but I need to administer data on a SQL 7 server, and I cannot create stored procedures, add users, or create DTS packages in 2000 that will work on SQL 7.
Thanks!You should install client tools for ver.7 to accomplish that task. May borrow CD from a friend or SQL homepage and go thru support for a valid link.|||SQL 2000 Client side tools will admin both SQL 2k and SQL 7...

AND you can buy a developer addition for US$49 directly from M$...

It's a full blow Enterprise edition as well, including the tools...

Good Luck|||I can sell you one. $899 with a professional discount ;)|||If you create DTS packages using SQL 2K EM it will not work on SQL 7 server. You must have similar MDAC when you design DTS.|||And more accurately, you must have the same version and SP level (MDAC is controlled by service pack on both client and server.)|||Originally posted by Satya
If you create DTS packages using SQL 2K EM it will not work on SQL 7 server. You must have similar MDAC when you design DTS.

What are you guys talking about?

I just did it...do you mean as a scheduled package?

I'm confused...|||Brett,

You CAN create and interactively execute a package using SQL2K client tools on any server. When you do it on SQL7, no server-initiated execution of this package will succeed. If you do it on a SQL2K server with lower SP than your client's machine, the result will be the same as with SQL7. The only time it will yield a successful execution will be when the server's SP level is the same or higher than your client's.

...man, I need a cigarette to catch my breath here...|||Marlboro Red?

Here ya go....|||Originally posted by Satya
You should install client tools for ver.7 to accomplish that task. May borrow CD from a friend or SQL homepage and go thru support for a valid link.
Unfortunately, I can't find anyone who has the CD. I looked on MS website, but couldn't seem to get to anything about SQL 7. Where is the SQL homepage?
Thanks for your help.|||Originally posted by rdjabarov
Brett,

You CAN create and interactively execute a package using SQL2K client tools on any server. When you do it on SQL7, no server-initiated execution of this package will succeed. If you do it on a SQL2K server with lower SP than your client's machine, the result will be the same as with SQL7. The only time it will yield a successful execution will be when the server's SP level is the same or higher than your client's.

...man, I need a cigarette to catch my breath here...

Brett,
This is what I found out - you can create and execute the package manually using EM 2K, but it will not run on a 7.0 scheduler. Also, you can't get in to add users, which, at this point is what I really need to do. And if you open a package you created in 7.0 and save it from 2K, it won't work anymore.|||Whereabouts you're based, I can lend you Ver.7 tools.

Originally posted by dmuller
Unfortunately, I can't find anyone who has the CD. I looked on MS website, but couldn't seem to get to anything about SQL 7. Where is the SQL homepage?
Thanks for your help.|||Hey Satya, you're ruining my perfect sale opportunity, I need money for Nicorette!!!|||Originally posted by rdjabarov
Hey Satya, you're ruining my perfect sale opportunity, I need money for Nicorette!!!

:eek: :eek: Don't worry... I will not :p|||Originally posted by Satya
Whereabouts you're based, I can lend you Ver.7 tools.
Riverside, CA|||Originally posted by rdjabarov
I can sell you one. $899 with a professional discount ;)

Be rational... and help others...

Need script that can shrink copy of DB to fit on a notebook

I need a script that will take a 40GB 300+ table database and shrink it to the 1st 1000 rows in each table and delete security tables like tblchargecard. Want to get size to about 1gb to fit on a notebook for development. Any suggestions would be appreciated.declare a table variable with two columns, table_name and Table_rowcount.

From a join between sysindexes and sysobjetcs table, get the table names and their respective rowcounts into this table variable.

update the table_rowcount columns with table_rowcount-1000

write a script to automatically generate delete statements for each table, each delete statement being preceded by set rowcount table_rowcount and followed by set rowcount 0 statement.

run this generated script.|||Creative, but I think that will crash if you have relational integrity established, and especially if you are using cascading deletes.

If your database does have cascading deletes, (as it should) then just delete everything but, say, every 10th record, out of the highest level tables in the schema. (You can use something like WHERE Right(PrimaryKey, 1) <> 0 if you have numeric keys, for instance.) Do this in a copy of the database, of course!

As far as "delete security tables like tblchargecard", you'll have to specify those in your script.|||Thanks for the help from both of you. will give this a try.

need sample code in c# to connect to sql database

hi,
i learn by practice and i would like an asp code using c# that connects to sql database can retrives information from a table.
can you please copy paste me some code or give me exact link a?

thank youHere is a tutorial.

Need Opinions on creating a reporting database more efficiently

Situation:

SQL Server 2000.

At my new employer they have a production database on one server and a copy of it that is set to read only on another server which is used for reporting.

#1

They have an SQL Server Agent job on the production server that: (2 times a day)

1. Backs up the production database
2. Copies the backup file to a directory on the reporting server. (Its pretty big and can take time if there are problems with the LAN)

#2

They have an SQL Server Agent job on the Reporting server that: (scheduled to run 2 hours or so after the job on server 1 has run…they figured that it would be a safe bet that the backup and copy process of the first job would be done by then)

1. Breaks the user connections to the reporting database
2. Performs a restore on the reporting database using the backup file that was copied to the holding directory by the production job.
3. Sets some permissions for various users.
4. Sets the reporting database to READ ONLY.

What I would like to do is find a more efficient way to create this reporting database, I have started doing research into DTS methods but would like some opinions from more experienced users.

Thank You,

Wade

What are you requirements?

Right now there is a big delay, is that problem? Do you need more realtime access? Have you looked at replication? Is it a problem that you have to kick users out? etc etc

Need I Remove SQL2k5 Trial to install a licensed copy

I thought I read somewhere that the SQL2k5 trial copy could be licensed at
or before the 180 days expired, but I can't find it. Can some one please
confirm, If I install SQL2k5 Enterprise Trial copy. Can I license that
copy, or do I need to uninstall and reinstall the licensed copy?http://www.microsoft.com/sql/howtobuy/faq.mspx
Thanks & Rate the Postings.
-Ravi-
"WANNABE" wrote:

> I thought I read somewhere that the SQL2k5 trial copy could be licensed at
> or before the 180 days expired, but I can't find it. Can some one please
> confirm, If I install SQL2k5 Enterprise Trial copy. Can I license that
> copy, or do I need to uninstall and reinstall the licensed copy?
>
>|||have you try upgrading from trial to FULL Version..
Santos Martinez,MCSE, MCDBA, MCTS
IT Community Staff of Puerto Rico
http://www.itcspr.org
"Ravi" wrote:
[vbcol=seagreen]
> http://www.microsoft.com/sql/howtobuy/faq.mspx
> --
> Thanks & Rate the Postings.
> -Ravi-
>
> "WANNABE" wrote:
>

Need I Remove SQL2k5 Trial to install a licensed copy

I thought I read somewhere that the SQL2k5 trial copy could be licensed at
or before the 180 days expired, but I can't find it. Can some one please
confirm, If I install SQL2k5 Enterprise Trial copy. Can I license that
copy, or do I need to uninstall and reinstall the licensed copy?http://www.microsoft.com/sql/howtobuy/faq.mspx
--
Thanks & Rate the Postings.
-Ravi-
"WANNABE" wrote:
> I thought I read somewhere that the SQL2k5 trial copy could be licensed at
> or before the 180 days expired, but I can't find it. Can some one please
> confirm, If I install SQL2k5 Enterprise Trial copy. Can I license that
> copy, or do I need to uninstall and reinstall the licensed copy?
>
>|||have you try upgrading from trial to FULL Version..
Santos Martinez,MCSE, MCDBA, MCTS
IT Community Staff of Puerto Rico
http://www.itcspr.org
"Ravi" wrote:
> http://www.microsoft.com/sql/howtobuy/faq.mspx
> --
> Thanks & Rate the Postings.
> -Ravi-
>
> "WANNABE" wrote:
> > I thought I read somewhere that the SQL2k5 trial copy could be licensed at
> > or before the 180 days expired, but I can't find it. Can some one please
> > confirm, If I install SQL2k5 Enterprise Trial copy. Can I license that
> > copy, or do I need to uninstall and reinstall the licensed copy?
> >
> >
> >