Getting stored procedures and permissions from 6.5 to 7.0

I am trying import my database from Sql Server 6.5 to 7.0 and can only
seem to get the tables.
I am using DTS and I can transfer the tables fine, but there doesn't
seem to be an option to move the other objects. There is a third button
that copies data between 7.0 databases, but that is greyed out since I
am coming from a 7.0 database.
There is a query button, but I don't how that would help me.
Thanks,
Tom.> I am trying import my database from Sql Server 6.5 to 7.0 and can only
> seem to get the tables.
> I am using DTS and I can transfer the tables fine, but there doesn't
> seem to be an option to move the other objects. There is a third
> button that copies data between 7.0 databases, but that is greyed out
> since I am coming from a 7.0 database.
You have to script your other objects to T-SQL script, but keep in mind that
your 6.5 stored procedures may need changing to work correctly in 7.0. You
may want to read in addition to above:
INF: Frequently Asked Questions - SQL Server 2000 - Upgrade
http://support.microsoft.com/defaul...b;en-us;q261334
INF: How to Upgrade SQL Server 6.5 and 7.0 to SQL Server 2000 (White Paper)
http://support.microsoft.com/defaul...b;en-us;Q322620
SQL Server Upgrade Recommendations and Best Practices
http://www.edgewoodsolutions.com/re...erUpgrade_1.asp
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Although I suspect one of the articles Sebastion pointed you to contain this
information... You should consider using the database upgrade wizard... IT
should appear under a program group called "Microsoft SQL Server -
Switch"... It will move everything including permissions and logins...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:40622890.4020207@.deltanet.com...
> I am trying import my database from Sql Server 6.5 to 7.0 and can only
> seem to get the tables.
> I am using DTS and I can transfer the tables fine, but there doesn't
> seem to be an option to move the other objects. There is a third button
> that copies data between 7.0 databases, but that is greyed out since I
> am coming from a 7.0 database.
> There is a query button, but I don't how that would help me.
> Thanks,
> Tom.
>|||Wayne Snyder wrote:

> Although I suspect one of the articles Sebastion pointed you to contain th
is
> information... You should consider using the database upgrade wizard... I
T
> should appear under a program group called "Microsoft SQL Server -
> Switch"... It will move everything including permissions and logins...
Found it.
I tried to run this and got the message "The local Sql Server is running
under LocalSystem priviledges, preventing the Upgrade Wizard to connect
to the Export Server". Why is this? How do I get it to run the way the
wizard wants?
Thanks,
Tom.

>

Getting problem in AcquireConnections

Hi All,

I just started looking at the SSIS programming, wanted to created a package having table to table data transfer. My source and destination databases are in Oracle.

I gone throught the code samples and started creating the source component. And the whatever samples i've seen the code i have written (copied) looks correct to me but still getting following error.

The basic questions i have is,

1. Do i need to setup something to start programming in SSIS. I am using 'Microsoft Visual C# Express Edition' for programming.

I have all the dll's in place.

And after compiling the code the exception i got is,

{"Exception from HRESULT: 0xC020801C"} System.Runtime.InteropServices.COMException was caught

Message="Exception from HRESULT: 0xC020801C"

Source="Microsoft.SqlServer.DTSPipelineWrap"

ErrorCode=-1071611876

StackTrace:

at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.AcquireConnections(Object pTransaction)

The Code is as Follows:

public static void CreateSource()

{

Microsoft.SqlServer.Dts.Runtime.Package package = new Microsoft.SqlServer.Dts.Runtime.Package();

Executable e = package.Executables.Add("DTS.Pipeline.1");

Microsoft.SqlServer.Dts.Runtime.TaskHost thMainPipe = e as Microsoft.SqlServer.Dts.Runtime.TaskHost;

MainPipe dataFlow = thMainPipe.InnerObject as MainPipe;

// Add an OLEDB connection manager that is used by the component to the package.

ConnectionManager cm = package.Connections.Add("OLEDB");

cm.Name = "OLEDB ConnectionManager";

cm.ConnectionString = @."Data Source=pqdb9i;User ID=srcDbUserId;Provider=MSDAORA.1;Persist Security Info=False;Integrated Security=SSPI;Auto Translate=False;";

package.DelayValidation = true;

cm.DelayValidation = true;

component = dataFlow.ComponentMetaDataCollection.New();

component.Name = "OLEDBSource";

component.ComponentClassID = "DTSAdapter.OleDbSource.1";

// Get the design time instance of the component.

CManagedComponentWrapper instance = component.Instantiate();

// Initialize the component

instance.ProvideComponentProperties();

// Specify the connection manager.

if (component.RuntimeConnectionCollection.Count > 0)

{

component.RuntimeConnectionCollection[0].ConnectionManagerID = package.Connections[0].ID;

component.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[0]);

}

// Set the custom properties.

//instance.SetComponentProperty("AlwaysUseDefaultCodePage", false);

instance.SetComponentProperty("AccessMode", 2);

instance.SetComponentProperty("SqlCommand", "Select * from srcTable");

// Reinitialize the metadata.

try

{

instance.AcquireConnections(null);

instance.ReinitializeMetaData();

}

catch (Exception ex)

{

Console.WriteLine(ex.Message);

}

Console.WriteLine(component.InputCollection.Count);

}

///End Code

Thanks in advance

-Yuwaraj

Does Oracle use Windows Authentication in your case? If not, where's the password for the database user?|||Hi

I have the same problem, did u find any solution?

Getting error working with Transfer SQL Server Objects Task

I was trying to transfer a SQL Server 2000 database to SQL Server 2005 using SQL Server Objects Task. However, The following error message was encountered:

"[Transfer SQL Server Objects Task] Error: Execution failed with the following error: "Cannot apply value null to property Login: Value cannot be null..".“

can anyone suggest me what is the error here?

Is there any other way possible to achieve this(transfer of database). The objects to be transferred are tables, views, stored procedures, user data types, and user-defined functions. The logins need not to be transferred.

The output we are Getting is:-

Start Debugging:

SSIS package "unittest2.dtsx" starting.

Error: 0xC002F325 at SQL Server-Objekte kopieren, Transfer SQL Server Objects Task: Execution failed with the following error: "Cannot apply value null to property Login: Value cannot be null..".

Task failed: SQL Server-Objekte kopieren

Warning: 0x80019002 at unittest2: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "unittest2.dtsx" finished: Failure.

The program '[4272] unittest2.dtsx: DTS' has exited with code 0 (0x0).

You might try posting this in the SSIS forum.

Getting error working with Transfer SQL Server Objects Task

I was trying to transfer a SQL Server 2000 database to SQL Server 2005 using SQL Server Objects Task. However, The following error message was encountered:

"[Transfer SQL Server Objects Task] Error: Execution failed with the following error: "Cannot apply value null to property Login: Value cannot be null..".“

can anyone suggest me what is the error here?

Is there any other way possible to achieve this(transfer of database). The objects to be transferred are tables, views, stored procedures, user data types, and user-defined functions. The logins need not to be transferred.
The output we are Getting is:-

Start Debugging:

SSIS package "unittest2.dtsx" starting.

Error: 0xC002F325 at SQL Server-Objekte kopieren, Transfer SQL Server Objects Task: Execution failed with the following error: "Cannot apply value null to property Login: Value cannot be null..".

Task failed: SQL Server-Objekte kopieren

Warning: 0x80019002 at unittest2: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "unittest2.dtsx" finished: Failure.

The program '[4272] unittest2.dtsx: DTS' has exited with code 0 (0x0).

I think this is likely better for the management tools forum - they own the transfer objects wizard and tasks ...

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=84&SiteID=1

Donald