Problem importing from Oracle

Hello all, I hoping to get some input on how to get around this odd problem. I use the SSIS Import Wizard to create a package that imports data from an Oracle 9 database into SQL 2005. One of the 'columns' selected from Oracle is actually two floating point columns multiplied together.

ie: SELECT one * two FROM table

In the SSIS wizard the data preview for this shows up as it should, with the column data in floating point form. However after actually importing the data, all the numbers get imported as integers even though they are going into a floating point column.

Any ideas how to get around this?

Thanks

Try explicitly casting them as a float in the SELECT.

Problem importing from Excel

When I try to import data from Excel with the dts wizard I get the following eror message when I select Excel as a data source:

TITLE: SQL Server Import and Export Wizard

An error occurred which the SQL Server Integration Services Wizard was not prepared to handle.

ADDITIONAL INFORMATION:

Exception has been thrown by the target of an invocation. (mscorlib)

The connection type "EXCEL" specified for connection manager "{D4D59FCE-C0A4-4AA2-B374-766665A74159}" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.
({B2F473AA-8E07-40AA-AF01-9AD13DE7B4B8})

The connection type "EXCEL" specified for connection manager "{D4D59FCE-C0A4-4AA2-B374-766665A74159}" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.
({B2F473AA-8E07-40AA-AF01-9AD13DE7B4B8})

BUTTONS:

OK

That is the complete error message as I get it. I used the copy message text feature and pasted it directly here.

I really hope someone can help with this one as it has me completely stumped and also unable to finish an assignment.

If this is a college assignment, consider consulting with your professor or other students. I find in my classes that many students doing an identical assignment will encounter the same software problems.

Additionally you may want to try exporting the data from Excel to a CSV and then importing the CSV file and see if you have better luck there.

Hopefully someone else can chime in some better help specific to your error.

Problem importing Excel data into SSMS

Hi,

When I try to use the Import and Export Wizard to import a good .xls file I keep getting this error:

"Could not find installable ISAM. (Microsoft JET Database Engine)"

It occurs in both SSMS or SSIS. It also occurs with other flat files like .csv.

The OS is XP, w/ office 2003.

here are the full error details:

at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open()
at Microsoft.SqlServer.Dts.DtsWizard.DTSWizard.GetOpenedConnection(WizardInputs wizardInputs, String connEntryName)
at Microsoft.SqlServer.Dts.DtsWizard.Step1.OnLeavePage(LeavePageEventArgs e)

I don't understand why OleDb connection is involved to the Jet when this is SQL2K5 full version.

Please anyone help.

Carl

http://databases.aspfaq.com/database/how-do-i-solve-could-not-find-installable-isam-errors.html for all the issues to resolve.|||

Thanks Satya,

This link is dead. I had previously found this resource and chased down the Google cached version

http://72.14.253.104/search?q=cache:Vyh8_PKhHrwJ:databases.aspfaq.com/database/how-do-i-solve-could-not-find-installable-isam-errors.html+how-do-i-solve-could-not-find-installable-isam-errors.html&hl=en&lr=&strip=1

All it says is to reinstall the MDAC. Well, when I try to do that the system (XP) says there is no need for it as a more recent version is included as part of the OS.

This article describes registry changes to the Jet engine.

http://support.microsoft.com/default.aspx/kb/283881

This machine's registry does not have these keys at all, even though both Excel 2k3 and Access 2k3 work just fine. The fix is to reinstall or repair the Office Pro installation.

I have poor experiences with reinstalling Office 2003. Many customized settings are kept in the registry and wiped out in the reinstall/repair. Before I embark on this journey I don't understand why I even need Office for the operation I am trying to do?

BTW the same error occurred when I tried to import a csv file too, not just an .xls

I wrote a little c# class to convert the file to xml and then standard DAL to "import" the data into SQL Server db. Worked like a charm, but in the future I would still like to be able to do it in SSMS. Most likely on a remote server there would not even be any installation of Office.

Thanks again

Carl

|||See this blog entry too http://blogs.wdevs.com/Gaurang/archive/2005/06/15/5112.aspx and I appreciate your feedback.|||

Satya,

great tip here http://blogs.wdevs.com/Gaurang/archive/2005/06/15/5112.aspx

They talk about extended properties in a connection string.

If I were not doing the connection programmatically but using the SSMS Object explorer, where should I find the connection for the (local) server instance?

Databases -> MyDatabase ->Security ->Users ->dbo
Here there is a link in the lower left to view the connection properties but no extended properties field.

The only place I could find such a field is in
Databases -> System Databases -> master ->Security ->Users -> SERVERNAME\SQLServer2005MSFTEUser$SERVERNAME$MSSQLSERVER

That however is not the db I want to import to. I added an extended property anyway "Excel 8.0;IMEX=1" just for kicks but it didn't do any good.

Any more ideas?

Carl

|||

You can use SP_ADDEXTENDEDPROPERTY as per this http://msdn2.microsoft.com/en-us/library/ms190243.aspx link.

|||

My question was not how to do it programmatically but how to do it using the SSMS GUIs.

I guess the pros do not like using the GUIs too much, and I am starting to see why.

Thanks

Carl

Problem importing data from Interbase using DTS

Hi im trying to import a old Interbase 6 database using MSSQL 2000 DTS
import wizard.
Im using Firebird / Interbase 1.5 ODBC driver as provider for the Interbase.
Everything works perfect if i choose to import one table at a time. But if i
choose 2 or more the wizards excutes the DDL (tables ar created) then it
just locks up. No error, just no progress. I have tried other ODBC provider
but with the exact same result.
Any idea why the DTS Data Iimport wizard locks up?
Thanks in regards
Anders, Denmark
Hi
If the ODBC driver does not return control to DTS, DTS looks like it has
hung.
That indicates a problem with the driver as not with DTS.
This problem does not occur with Oracle, Access or SQL Server drivers.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anders K. Jacobsen [DK]" <none@.at.all> wrote in message
news:uWFBqpE4EHA.208@.TK2MSFTNGP12.phx.gbl...
> Hi im trying to import a old Interbase 6 database using MSSQL 2000 DTS
> import wizard.
> Im using Firebird / Interbase 1.5 ODBC driver as provider for the
Interbase.
> Everything works perfect if i choose to import one table at a time. But if
i
> choose 2 or more the wizards excutes the DDL (tables ar created) then it
> just locks up. No error, just no progress. I have tried other ODBC
provider
> but with the exact same result.
> Any idea why the DTS Data Iimport wizard locks up?
> Thanks in regards
> Anders, Denmark
>
|||> If the ODBC driver does not return control to DTS, DTS looks like it has
> hung.
> That indicates a problem with the driver as not with DTS.
> This problem does not occur with Oracle, Access or SQL Server drivers.
Strange. Do you know of any Interbase ODBC provider wich is "compatible"
with DTS import?
I have tried:
EasySoft ODBC for interbase
XTG ODBC for interbase
FireBird / Interbase ODBC for interbase
IBProvider ODBC for interbase
All with the same result. Creates DDL then hang when starting to convert
data, if one table is choosen it works perfectly.
Suggestions to ODBC drivers?
Anders
|||Hi Anders,
I am using the Gemini Firebird / Interbase ODBC Driver for exactly what you
are trying to do. We're slowly converting all our clients from Interbase to
SQL Server. I use this driver for a DTS package which pulls about 80 tables
from Interbase source into our SQL Server target database. However, I use an
individual Transform Data task for each table. It's a nightmare to maintain
as we evolve the database structure but it runs really nicely, with multiple
TD tasks executing in parallel.
http://www.ibdatabase.com/
https://secure.shareit.com/shareit/c...oductid=148695
Hope this helps,
Joe Geretz
"Anders K. Jacobsen [DK]" <none@.at.all> wrote in message
news:ulIOTdF4EHA.2404@.TK2MSFTNGP14.phx.gbl...
> Strange. Do you know of any Interbase ODBC provider wich is "compatible"
> with DTS import?
> I have tried:
> EasySoft ODBC for interbase
> XTG ODBC for interbase
> FireBird / Interbase ODBC for interbase
> IBProvider ODBC for interbase
> All with the same result. Creates DDL then hang when starting to convert
> data, if one table is choosen it works perfectly.
> Suggestions to ODBC drivers?
> Anders
>
>
|||> I am using the Gemini Firebird / Interbase ODBC Driver for exactly what
> you are trying to do. We're slowly converting all our clients from
> Interbase to SQL Server. I use this driver for a DTS package which pulls
> about 80 tables from Interbase source into our SQL Server target database.
> However, I use an individual Transform Data task for each table. It's a
> nightmare to maintain as we evolve the database structure but it runs
> really nicely, with multiple TD tasks executing in parallel.
We try to create views on the IB database wich match our new tablestructure
seems quite maintainable not sure though. Anyway. I have already tried with
the Gemini ODBC driver...same results. Its real strange. Because everything
else works.
|||Which Task are you using to transfer the data?
It sounds like you're issuing SQL to insert into X select * from Y? This is
conveniently maintained, but you can't do this with the Data Pump. Or can
you?
- Joe Geretz -
"Anders K. Jacobsen [DK]" <none@.at.all> wrote in message
news:%23gg2PBu4EHA.824@.TK2MSFTNGP11.phx.gbl...
> We try to create views on the IB database wich match our new
> tablestructure seems quite maintainable not sure though. Anyway. I have
> already tried with the Gemini ODBC driver...same results. Its real
> strange. Because everything else works.
>
>

problem editing dataset mdx query sql 2005

Hi all,
I have created a report with the report wizard. I used a cube as a
datasource.
After creating I also run the report without any problems.
However when I go to the data tab to edit my dataset.
The dataset becomes empty and my reports is getting errors like
"Report item expressions can only refer to fields within the current
data set scope or, if inside an aggregate, the specified data set
scope"
What is the problem, why can't I change a dataset based on analysis
services, i don't have any problems with datasets based on normal
databases.In Short:
WORKAROUND: If you have created your report using the Report Wizard to start
the MDX Query Builder, open the .rdl file as XML. Find the section starting
with <rd:MdxQuery> and ending with </rd:MdxQuery> and delete all spaces and
newlines between elements (between closing > and opening <) so the whole
complex element is inline. You will be able to continue working with the
report normally.
1) The problem arises when you use the Report Wizard (as GaryV indicated
before me)
2) If you compare the XML generated by the Query Builder when using the
Report Wizard and Add existing item... you will see
a) the complex element <rd:MdxQuery> from the Wizard path is nicely
formatted with spaces and newlines while
b) the Query Builder used directly puts the whole long
<rd:MdxQuery></rd:MdxQuery> "subdocument" inline
c) the proof is in the pudding - if you delete the formatting characters (I
ssupect the inlines are key) you can tab to Data and edit the dataset w/o
problems
See thread " VS crashes when ..."
"Anke" <anke_ruigrok@.zonnet.nl> schrieb im Newsbeitrag
news:1134563578.098538.277750@.g49g2000cwa.googlegroups.com...
> Hi all,
> I have created a report with the report wizard. I used a cube as a
> datasource.
> After creating I also run the report without any problems.
> However when I go to the data tab to edit my dataset.
> The dataset becomes empty and my reports is getting errors like
> "Report item expressions can only refer to fields within the current
> data set scope or, if inside an aggregate, the specified data set
> scope"
> What is the problem, why can't I change a dataset based on analysis
> services, i don't have any problems with datasets based on normal
> databases.
>|||Thanx for you're help, it works fine for me!
Ralph Watermann schreef:
> In Short:
> WORKAROUND: If you have created your report using the Report Wizard to start
> the MDX Query Builder, open the .rdl file as XML. Find the section starting
> with <rd:MdxQuery> and ending with </rd:MdxQuery> and delete all spaces and
> newlines between elements (between closing > and opening <) so the whole
> complex element is inline. You will be able to continue working with the
> report normally.
> 1) The problem arises when you use the Report Wizard (as GaryV indicated
> before me)
> 2) If you compare the XML generated by the Query Builder when using the
> Report Wizard and Add existing item... you will see
> a) the complex element <rd:MdxQuery> from the Wizard path is nicely
> formatted with spaces and newlines while
> b) the Query Builder used directly puts the whole long
> <rd:MdxQuery></rd:MdxQuery> "subdocument" inline
> c) the proof is in the pudding - if you delete the formatting characters (I
> ssupect the inlines are key) you can tab to Data and edit the dataset w/o
> problems
>
> See thread " VS crashes when ..."
> "Anke" <anke_ruigrok@.zonnet.nl> schrieb im Newsbeitrag
> news:1134563578.098538.277750@.g49g2000cwa.googlegroups.com...
> > Hi all,
> >
> > I have created a report with the report wizard. I used a cube as a
> > datasource.
> > After creating I also run the report without any problems.
> > However when I go to the data tab to edit my dataset.
> > The dataset becomes empty and my reports is getting errors like
> >
> > "Report item expressions can only refer to fields within the current
> > data set scope or, if inside an aggregate, the specified data set
> > scope"
> >
> > What is the problem, why can't I change a dataset based on analysis
> > services, i don't have any problems with datasets based on normal
> > databases.
> >|||I mean thanx for your help............

problem creating trans. replication /sp_MShelp_replication_table

I'm using the wizard to create a trans. replication. When it tries to
retrieve the tables to publish
I get this error:
New Publication Wizard encountered one or more errors while retrieving
the list of articles on 'production'. The list of articles may not be
complete.
Invalid column name 'options'. (Microsoft SQL Server, Error: 207)
The details of the error refer to sp_MShelp_replication_table.
When I run that stored proc I get:
Msg 207, Level 16, State 1, Procedure sp_MShelp_replication_table, Line
59
Invalid column name 'options'.
What could be the issue?
-Phil
I found the problem. The database was originally a SQL2000 db that was
being replicated. It still had some tables like sysarticles. I
dropped those tables and it's fine now.
|||The reserved replication keywords are :
If your publishing table has column names reserved for merge processing, you
will not be able to generate an initial snapshot because of duplicate column
names. Reserved column names are:
a.. reason_code
b.. source_object
c.. reason_text
d.. Pubid
e.. conflict_type
f.. origin_datasource
g.. tablenick
h.. create_time
Option is a reserved sql keyword. Could you post the schema for this table
here?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Phil Cruz" <philcruz@.gmail.com> wrote in message
news:1144171822.881370.203540@.z34g2000cwc.googlegr oups.com...
> I'm using the wizard to create a trans. replication. When it tries to
> retrieve the tables to publish
> I get this error:
> New Publication Wizard encountered one or more errors while retrieving
> the list of articles on 'production'. The list of articles may not be
> complete.
> Invalid column name 'options'. (Microsoft SQL Server, Error: 207)
> The details of the error refer to sp_MShelp_replication_table.
> When I run that stored proc I get:
> Msg 207, Level 16, State 1, Procedure sp_MShelp_replication_table, Line
> 59
> Invalid column name 'options'.
>
> What could be the issue?
> -Phil
>

Problem creating a Maintenance Plan

When trying to create a maintenance plan using the wizard to backup the
system databases in SQL 2005 the following error is displayed.
How can I find what is causing this problem with a simple maintenance
plan?
Maintenance Plan Wizard Progress
- Creating maintenance plan "SystemDBs Backup" (Error)
Messages
* Create maintenance plan failed.
ADDITIONAL INFORMATION:
Create failed for JobStep 'Subplan'.
(Microsoft.SqlServer.MaintenancePlanTasks)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&Ev tID=Create+JobStep&LinkId=20476[/url]
An exception occurred while executing a Transact-SQL statement or
batch. (Microsoft.SqlServer.ConnectionInfo)
The specified '@.subsystem' is invalid (valid values are returned by
sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476[ /url]
- Adding tasks to the maintenance plan (Stopped)
- Adding scheduling options (Stopped)
- Adding reporting options (Stopped)
- Saving maintenance plan "SystemDBs Backup" (Stopped)
Integration Services are not installed on this server but other servers
without it the plans can be created.
Tibor Karaszi wrote:[vbcol=seagreen]
> Did you install Integration Services?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <robin9876@.hotmail.com> wrote in message
> news:1165336377.424898.184600@.16g2000cwy.googlegro ups.com...

Problem creating a Maintenance Plan

When trying to create a maintenance plan using the wizard to backup the
system databases in SQL 2005 the following error is displayed.
How can I find what is causing this problem with a simple maintenance
plan?
Maintenance Plan Wizard Progress
- Creating maintenance plan "SystemDBs Backup" (Error)
Messages
* Create maintenance plan failed.
--
ADDITIONAL INFORMATION:
Create failed for JobStep 'Subplan'.
(Microsoft.SqlServer.MaintenancePlanTasks)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+JobStep&LinkId=20476
--
An exception occurred while executing a Transact-SQL statement or
batch. (Microsoft.SqlServer.ConnectionInfo)
--
The specified '@.subsystem' is invalid (valid values are returned by
sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476
- Adding tasks to the maintenance plan (Stopped)
- Adding scheduling options (Stopped)
- Adding reporting options (Stopped)
- Saving maintenance plan "SystemDBs Backup" (Stopped)Did you install Integration Services?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<robin9876@.hotmail.com> wrote in message
news:1165336377.424898.184600@.16g2000cwy.googlegroups.com...
> When trying to create a maintenance plan using the wizard to backup the
> system databases in SQL 2005 the following error is displayed.
> How can I find what is causing this problem with a simple maintenance
> plan?
>
> Maintenance Plan Wizard Progress
> - Creating maintenance plan "SystemDBs Backup" (Error)
> Messages
> * Create maintenance plan failed.
> --
> ADDITIONAL INFORMATION:
> Create failed for JobStep 'Subplan'.
> (Microsoft.SqlServer.MaintenancePlanTasks)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+JobStep&LinkId=20476
> --
> An exception occurred while executing a Transact-SQL statement or
> batch. (Microsoft.SqlServer.ConnectionInfo)
> --
> The specified '@.subsystem' is invalid (valid values are returned by
> sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476
>
> - Adding tasks to the maintenance plan (Stopped)
> - Adding scheduling options (Stopped)
> - Adding reporting options (Stopped)
> - Saving maintenance plan "SystemDBs Backup" (Stopped)
>|||Integration Services are not installed on this server but other servers
without it the plans can be created.
Tibor Karaszi wrote:
> Did you install Integration Services?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <robin9876@.hotmail.com> wrote in message
> news:1165336377.424898.184600@.16g2000cwy.googlegroups.com...
> > When trying to create a maintenance plan using the wizard to backup the
> > system databases in SQL 2005 the following error is displayed.
> >
> > How can I find what is causing this problem with a simple maintenance
> > plan?
> >
> >
> > Maintenance Plan Wizard Progress
> >
> > - Creating maintenance plan "SystemDBs Backup" (Error)
> > Messages
> > * Create maintenance plan failed.
> >
> > --
> > ADDITIONAL INFORMATION:
> >
> > Create failed for JobStep 'Subplan'.
> > (Microsoft.SqlServer.MaintenancePlanTasks)
> >
> > For help, click:
> > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+JobStep&LinkId=20476
> >
> > --
> >
> > An exception occurred while executing a Transact-SQL statement or
> > batch. (Microsoft.SqlServer.ConnectionInfo)
> >
> > --
> >
> > The specified '@.subsystem' is invalid (valid values are returned by
> > sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
> >
> > For help, click:
> > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476
> >
> >
> > - Adding tasks to the maintenance plan (Stopped)
> >
> > - Adding scheduling options (Stopped)
> >
> > - Adding reporting options (Stopped)
> >
> > - Saving maintenance plan "SystemDBs Backup" (Stopped)
> >|||I see... Well, to the best of my knowledge, you need SSIS to execute an MP (in 2005), since an MP is
an SSIS package.
I've heard that you won't need this with sp2, but sp2 is still only in CTP.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<robin9876@.hotmail.com> wrote in message
news:1165355311.079516.289340@.j72g2000cwa.googlegroups.com...
> Integration Services are not installed on this server but other servers
> without it the plans can be created.
> Tibor Karaszi wrote:
>> Did you install Integration Services?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> <robin9876@.hotmail.com> wrote in message
>> news:1165336377.424898.184600@.16g2000cwy.googlegroups.com...
>> > When trying to create a maintenance plan using the wizard to backup the
>> > system databases in SQL 2005 the following error is displayed.
>> >
>> > How can I find what is causing this problem with a simple maintenance
>> > plan?
>> >
>> >
>> > Maintenance Plan Wizard Progress
>> >
>> > - Creating maintenance plan "SystemDBs Backup" (Error)
>> > Messages
>> > * Create maintenance plan failed.
>> >
>> > --
>> > ADDITIONAL INFORMATION:
>> >
>> > Create failed for JobStep 'Subplan'.
>> > (Microsoft.SqlServer.MaintenancePlanTasks)
>> >
>> > For help, click:
>> > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+JobStep&LinkId=20476
>> >
>> > --
>> >
>> > An exception occurred while executing a Transact-SQL statement or
>> > batch. (Microsoft.SqlServer.ConnectionInfo)
>> >
>> > --
>> >
>> > The specified '@.subsystem' is invalid (valid values are returned by
>> > sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
>> >
>> > For help, click:
>> > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476
>> >
>> >
>> > - Adding tasks to the maintenance plan (Stopped)
>> >
>> > - Adding scheduling options (Stopped)
>> >
>> > - Adding reporting options (Stopped)
>> >
>> > - Saving maintenance plan "SystemDBs Backup" (Stopped)
>> >
>

Problem creating a Maintenance Plan

When trying to create a maintenance plan using the wizard to backup the
system databases in SQL 2005 the following error is displayed.
How can I find what is causing this problem with a simple maintenance
plan?
Maintenance Plan Wizard Progress
- Creating maintenance plan "SystemDBs Backup" (Error)
Messages
* Create maintenance plan failed.
ADDITIONAL INFORMATION:
Create failed for JobStep 'Subplan'.
(Microsoft.SqlServer.MaintenancePlanTasks)
For help, click:
http://go.microsoft.com/fwlink?Prod...ep&LinkId=20476
An exception occurred while executing a Transact-SQL statement or
batch. (Microsoft.SqlServer.ConnectionInfo)
The specified '@.subsystem' is invalid (valid values are returned by
sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
For help, click:
http://go.microsoft.com/fwlink?Prod...34&LinkId=20476
- Adding tasks to the maintenance plan (Stopped)
- Adding scheduling options (Stopped)
- Adding reporting options (Stopped)
- Saving maintenance plan "SystemDBs Backup" (Stopped)Did you install Integration Services?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<robin9876@.hotmail.com> wrote in message
news:1165336377.424898.184600@.16g2000cwy.googlegroups.com...
> When trying to create a maintenance plan using the wizard to backup the
> system databases in SQL 2005 the following error is displayed.
> How can I find what is causing this problem with a simple maintenance
> plan?
>
> Maintenance Plan Wizard Progress
> - Creating maintenance plan "SystemDBs Backup" (Error)
> Messages
> * Create maintenance plan failed.
> --
> ADDITIONAL INFORMATION:
> Create failed for JobStep 'Subplan'.
> (Microsoft.SqlServer.MaintenancePlanTasks)
> For help, click:
> http://go.microsoft.com/fwlink?Prod...ep&LinkId=20476
> --
> An exception occurred while executing a Transact-SQL statement or
> batch. (Microsoft.SqlServer.ConnectionInfo)
> --
> The specified '@.subsystem' is invalid (valid values are returned by
> sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
> For help, click:
> http://go.microsoft.com/fwlink?Prod...34&LinkId=20476
>
> - Adding tasks to the maintenance plan (Stopped)
> - Adding scheduling options (Stopped)
> - Adding reporting options (Stopped)
> - Saving maintenance plan "SystemDBs Backup" (Stopped)
>|||Integration Services are not installed on this server but other servers
without it the plans can be created.
Tibor Karaszi wrote:[vbcol=seagreen]
> Did you install Integration Services?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <robin9876@.hotmail.com> wrote in message
> news:1165336377.424898.184600@.16g2000cwy.googlegroups.com...|||I see... Well, to the best of my knowledge, you need SSIS to execute an MP (
in 2005), since an MP is
an SSIS package.
I've heard that you won't need this with sp2, but sp2 is still only in CTP.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<robin9876@.hotmail.com> wrote in message
news:1165355311.079516.289340@.j72g2000cwa.googlegroups.com...
> Integration Services are not installed on this server but other servers
> without it the plans can be created.
> Tibor Karaszi wrote:
>

Problem copying table

We upgraded to SQL Server 2005 & I'm having trouble with the Import and Export Wizard in the Management Studio. We periodically need to export some data to a different database to save it while we update the 'real' database which basically starts it over with empty tables. Under SQL Server 2000 that wasn't a problem. Under 2005 it is.

The database comes from a vendor and nearly every table in it has a timestamp column & when I try to import/export the rows I get a Validation Error: Error 0xc0202048: Data Flow Task: Attempting insertion into the row version column "timestamp". Cannot insert into a row version column.

Now I can write a query that omits the timestamp column & the import/export works perfectly-but a couple of the tables have over a hundred fields! (Oh, what I'd give for an 'except' analog to the * selection.)

Any suggestion how to get around this? Thanks.You cannot insert specific values to a column when the datatype is timestamp. Depending on the source of your data, you most likely want to change the datatype of the destination column to either binary(8), varbinary(8) or datetime. More information about timestamp columns in SQL Server is available in BOL (http://msdn2.microsoft.com/en-us/library/ms182776.aspx).|||You know, I thought my question was clear. I know what the problem is, what I don't know is how to solve it-and since the app comes from a vendor changing it myself isn't an option. (Is it really an option for you, roac, or do you just not work with applications you don't develop yourself?)

FWIW I submitted a request to the vendor to remove/change the timestamp column-no response, yet, which is why I'm looking for a solution that I can implement.

So far it looks like I'm stuck-either wait for the vendor to make the change or start writing queries. I figure at least two solid days' work to write the queries, and then there's testing & keeping them updated.|||Ah, I see now, I obviously overlooked a few details :)

Hopefully you have a test-environment where you can try the following procedure:

1. Backup the database
2. Restore on alternate location
3. Use ApexSQL Diff (http://www.apexsql.com/sql_tools_diff.asp) to script changes to structure
4. Restore database where backup was taken
5. Run script to apply changes in structure.

As far as I can see this approach should work. If the database is not deleted and recreated during upgrade, and you are using Enterprise Edition, you could use a Database Snapshot instead of backups.|||Wow, Calvin. That was a pretty rude response to someone who honestly was trying to help you.
I have a script that might assist you in writing queries for these lengthy tables, but I hesitate to post it because it may not be exactly what you want.|||roac, I'll take a look at ApexSQL. Right off I don't see how it will help as your description sounds like a way to change the timestamp column and what I need is a way to backup/restore all the data except the timestamp column. But maybe ApexSQL will let me do that-I'll take a look & thanks.

Blindman, I'd be happy to take a look at your script. Thanks.|||ApexSQL diff is a tool for scripting the code neccessary to change one version of a data structure to another. So, you can create a script that change the data structure of the data that you already have to the version created by the application's upgrade.|||Attached is a script for creating formatted lists of column names for easy cut-n-paste into code. Make sure when you run it that you have QA set to output results in text mode.

Problem copying DTS jobs with Database Copy Wizard

I am using the Database Copy Wizard to copy a database from one server
to the other. Here is the server information:
Source Server: Windows 2000 Server SP4, SQL Server 2000 SP4
Destination Server: Windows 2003 Enterprise Edition SP1, SQL Server
2000 SP4
I initiate the DCW from the Source server and use the sa account to
authenticate on both servers. I need to copy all logins, jobs, etc..
so I check the appropriate boxes for this. The copy completes
successfully.
When I log onto the destination server, the database is there, logins
are there, but I don't see anything under Data Transformation Services
- Local Packages.
Do I need to turn something on/off because of the different operating
systems? I have done this successfully several times before - the
only difference this time is the OS.
Thank you!
KarriB
I dont know Exactly What u mena when you said you have copied DTS with
Database copy wizard.
Database Copy Wizard is Implemented as a DTS by Microsoft.
you can only Copy Databases or Logins/SP or Jobs etc but not DTS
Packages.
When we RUn this Wizard, it can be Run Immideatly or Scheduled for
later RUN. (option in the Last Screens of the Wizard)
If you mean you want to save this DTS, you cna do So by specifying so
on the last screens.
Maninder
MCDBA
On Jan 30, 1:00 pm, zell...@.avaya.com wrote:
> I am using the Database Copy Wizard to copy a database from one server
> to the other. Here is the server information:
> Source Server: Windows 2000 Server SP4, SQL Server 2000 SP4
> Destination Server: Windows 2003 Enterprise Edition SP1, SQL Server
> 2000 SP4
> I initiate the DCW from the Source server and use the sa account to
> authenticate on both servers. I need to copy all logins, jobs, etc..
> so I check the appropriate boxes for this. The copy completes
> successfully.
> When I log onto the destination server, the database is there, logins
> are there, but I don't see anything under Data Transformation Services
> - Local Packages.
> Do I need to turn something on/off because of the different operating
> systems? I have done this successfully several times before - the
> only difference this time is the OS.
> Thank you!
> KarriB

Problem copying DTS jobs with Database Copy Wizard

I am using the Database Copy Wizard to copy a database from one server
to the other. Here is the server information:
Source Server: Windows 2000 Server SP4, SQL Server 2000 SP4
Destination Server: Windows 2003 Enterprise Edition SP1, SQL Server
2000 SP4
I initiate the DCW from the Source server and use the sa account to
authenticate on both servers. I need to copy all logins, jobs, etc..
so I check the appropriate boxes for this. The copy completes
successfully.
When I log onto the destination server, the database is there, logins
are there, but I don't see anything under Data Transformation Services
- Local Packages.
Do I need to turn something on/off because of the different operating
systems? I have done this successfully several times before - the
only difference this time is the OS.
Thank you!
KarriBI dont know Exactly What u mena when you said you have copied DTS with
Database copy wizard.
Database Copy Wizard is Implemented as a DTS by Microsoft.
you can only Copy Databases or Logins/SP or Jobs etc but not DTS
Packages.
When we RUn this Wizard, it can be Run Immideatly or Scheduled for
later RUN. (option in the Last Screens of the Wizard)
If you mean you want to save this DTS, you cna do So by specifying so
on the last screens.
Maninder
MCDBA
On Jan 30, 1:00 pm, zell...@.avaya.com wrote:
> I am using the Database Copy Wizard to copy a database from one server
> to the other. Here is the server information:
> Source Server: Windows 2000 Server SP4, SQL Server 2000 SP4
> Destination Server: Windows 2003 Enterprise Edition SP1, SQL Server
> 2000 SP4
> I initiate the DCW from the Source server and use the sa account to
> authenticate on both servers. I need to copy all logins, jobs, etc..
> so I check the appropriate boxes for this. The copy completes
> successfully.
> When I log onto the destination server, the database is there, logins
> are there, but I don't see anything under Data Transformation Services
> - Local Packages.
> Do I need to turn something on/off because of the different operating
> systems? I have done this successfully several times before - the
> only difference this time is the OS.
> Thank you!
> KarriB

Problem copying DTS jobs with Database Copy Wizard

I am using the Database Copy Wizard to copy a database from one server
to the other. Here is the server information:
Source Server: Windows 2000 Server SP4, SQL Server 2000 SP4
Destination Server: Windows 2003 Enterprise Edition SP1, SQL Server
2000 SP4
I initiate the DCW from the Source server and use the sa account to
authenticate on both servers. I need to copy all logins, jobs, etc..
so I check the appropriate boxes for this. The copy completes
successfully.
When I log onto the destination server, the database is there, logins
are there, but I don't see anything under Data Transformation Services
- Local Packages.
Do I need to turn something on/off because of the different operating
systems? I have done this successfully several times before - the
only difference this time is the OS.
Thank you!
KarriBI dont know Exactly What u mena when you said you have copied DTS with
Database copy wizard.
Database Copy Wizard is Implemented as a DTS by Microsoft.
you can only Copy Databases or Logins/SP or Jobs etc but not DTS
Packages.
When we RUn this Wizard, it can be Run Immideatly or Scheduled for
later RUN. (option in the Last Screens of the Wizard)
If you mean you want to save this DTS, you cna do So by specifying so
on the last screens.
Maninder
MCDBA
On Jan 30, 1:00 pm, zell...@.avaya.com wrote:
> I am using the Database Copy Wizard to copy a database from one server
> to the other. Here is the server information:
> Source Server: Windows 2000 Server SP4, SQL Server 2000 SP4
> Destination Server: Windows 2003 Enterprise Edition SP1, SQL Server
> 2000 SP4
> I initiate the DCW from the Source server and use the sa account to
> authenticate on both servers. I need to copy all logins, jobs, etc..
> so I check the appropriate boxes for this. The copy completes
> successfully.
> When I log onto the destination server, the database is there, logins
> are there, but I don't see anything under Data Transformation Services
> - Local Packages.
> Do I need to turn something on/off because of the different operating
> systems? I have done this successfully several times before - the
> only difference this time is the OS.
> Thank you!
> KarriB