dataset object dispose after first use

I have an Execute SQL Task that returns a dataset to variable DfltValData. A dataflow follows that with a script component that access that dataset (read only variable) (see code below) and everything is fine. Now, after that, there's another dataflow with a script component, with the same code as below, trying to access DfltValData. Here is where the problem is, the DfltValData object does not contains any row. Whats happening and how to solve this?

Thanks!

Dim olead As New Data.OleDb.OleDbDataAdapter
Dim dt As New Data.DataTable
Dim row As System.Data.DataRow
olead.Fill(dt, Me.Variables.DfltValData)
For Each row In dt.Rows
.

.

.// read value from row

.HAHAHAHA!! Not to laugh at your problem, but search a bit and you'll find a VERY recent discussion on this very topic.|||http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1406350&SiteID=1|||

Use a MULTICAST if you need to work with same rows again.

There is a code work-around (shown in the forum post Phil referenced), but it's ugly.

|||I don't understand how multicast would work if we have multiple dataflow.|||

Sorry, I didn't read the original post closely enough.

If the basic problem is that you want to reuse the data in another data flow, you could use a multicast in your first data flow, and output the data to a raw file. Then use a raw file source to pull it into your second data flow.

|||

jwelch wrote:

Sorry, I didn't read the original post closely enough.

If the basic problem is that you want to reuse the data in another data flow, you could use a multicast in your first data flow, and output the data to a raw file. Then use a raw file source to pull it into your second data flow.

Exactly. Or simply use a multicast to populate 2 variables in the first place with exactly the same data.

-Jamie

Datareader source/Data flow task property expression problems

Hi all,
I have the June CTP version of Yukon and it's various tools, and I'm having an issue with using property expressions or variable syntax within the SqlCommand string of a datareader source within a data flow task. It seems as if there were issues in past versions of doing this within data flow tasks, but I thought with the June CTP that this was a possibility. The documentation even states that you can do this within the sqlcommand property. Anybody else have this problem? Any solutions?
Thanks,
Adrian CrawfordHello Adrian,
Not sure exactly what you mean by "I'm having an issue with using property expressions or variable syntax" Are you referring to it error-ing out, or are the values not showing up properly?

Maybe the workaround in this post might help you?
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=70082

Jason|||Hey Jason,
Thanks for the reply. It is erroring out and not accepting my sqlcommand when I try to use any dts variables in the query. The datareader source works a little differently in that it doesn't give you the option to parse/build your query like other tasks do. Unfortunately there is not much of an error message to give. I get...
Further changes need to be made before the current settings can be saved to the component. Warnings reported by the component are:
And then nothing is listed. I'm trying to query an Oracle db and use dts vars in the query, but i have a feeling they are not getting parsed before being sent to Oracle.
Adrian
|||Hi Adrian,
Can you share your expression? I'd like to try to reproduce this so I can see why the error message is incorrect.
Also, what is the scope of the variables you are using?

Thanks
Mark|||Mark,
Thanks for your response. I figured out my problem in another recent post here:
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=73466
Basically I found that you can't directly enter variables or expressions in the sqlcommand box, since it will not parse it before being sent. I found the round about way in the above post.
Thanks,
Adrian

Dataming of subsets question

Have not found an active general data mining forum yet so asking here.

I'm new to data mining band have been given the task of setting up a data mining system. The problem is that our setup seems pretty non standard and I'm not sure how to use data mining on it or what my expectations should be. The situation is:

We have a large and growing set of strings which we get requests for (> 100,000). The requests have many, mostly nominal (non-numeric), variables associated with them. We can only handle a subset (probably less than 10,000) or the strings at any one time. We want to use data mining to analyze historic requests so we can figure out which strings we are going to handle under a given set of variables. So given that our variable currently have values X1, ..., Xn, what subset should be use given than a large database of historic string requests.

Anyone know what techniques would work well for this kind of problem? This is a quick and dirty kind of project, no special purpose hardware or expensive software on this one. I've been looking at using rapid miner but not sure that it's a great tool in this case.

thanks in advance,
max

A first ideea is to use Microsoft Clustering Algorithm to cluster your requests and find similare requests- for this situation you don't have to have a predictable attribute.

A second way is to define an objective by selecting one (or more) columns from your requests, let's say Xj and put the question :

What are the links/influences of X1,..., Xn with Xj?

Or create another attribute Xn+1 which same question.

Let see as Microsoft example that is presented in it's tutorials;it have following attributes:

CommuteDistance(X1)

Gender(X2)

HouseOwnerFlag(X3)

MaritalStatus(X4)

...

NumberCarsOwned(Xn)

and a predictible attribute:

BikeBuyer(Xj)

that mean if customer buy or not a bicycle; so in this case the question/objective is in the some way formulated:

What are the links/influences of X1,..., Xn with Xj?

There is a good book presented here

|||

You could try using and classification method with the other fields as input and the target string as output. Then you could rank the likelihood of each string given some input.

Candidate algorithms are

- Naive Bayes

- Logistic Regression

- Neural Nets (slow)

- Association Rules (not traditionally a classification method, but could work in this case)

After creating the model you would do something like

SELECT FLATTENED (SELECT TOP 10000 TargetString FROM PredictHistogram(TargetString) ORDER BY $Probability) FROM MyModel NATURAL PREDICTION JOIN

(SELECT 'Factor1' AS Factor1, 'Factor2' AS Factor2) AS t

HTH

-Jamie

Dataming of subsets question

Have not found an active general data mining forum yet so asking here.

I'm new to data mining band have been given the task of setting up a data mining system. The problem is that our setup seems pretty non standard and I'm not sure how to use data mining on it or what my expectations should be. The situation is:

We have a large and growing set of strings which we get requests for (> 100,000). The requests have many, mostly nominal (non-numeric), variables associated with them. We can only handle a subset (probably less than 10,000) or the strings at any one time. We want to use data mining to analyze historic requests so we can figure out which strings we are going to handle under a given set of variables. So given that our variable currently have values X1, ..., Xn, what subset should be use given than a large database of historic string requests.

Anyone know what techniques would work well for this kind of problem? This is a quick and dirty kind of project, no special purpose hardware or expensive software on this one. I've been looking at using rapid miner but not sure that it's a great tool in this case.

thanks in advance,
max

A first ideea is to use Microsoft Clustering Algorithm to cluster your requests and find similare requests- for this situation you don't have to have a predictable attribute.

A second way is to define an objective by selecting one (or more) columns from your requests, let's say Xj and put the question :

What are the links/influences of X1,..., Xn with Xj?

Or create another attribute Xn+1 which same question.

Let see as Microsoft example that is presented in it's tutorials;it have following attributes:

CommuteDistance(X1)

Gender(X2)

HouseOwnerFlag(X3)

MaritalStatus(X4)

...

NumberCarsOwned(Xn)

and a predictible attribute:

BikeBuyer(Xj)

that mean if customer buy or not a bicycle; so in this case the question/objective is in the some way formulated:

What are the links/influences of X1,..., Xn with Xj?

There is a good book presented here

|||

You could try using and classification method with the other fields as input and the target string as output. Then you could rank the likelihood of each string given some input.

Candidate algorithms are

- Naive Bayes

- Logistic Regression

- Neural Nets (slow)

- Association Rules (not traditionally a classification method, but could work in this case)

After creating the model you would do something like

SELECT FLATTENED (SELECT TOP 10000 TargetString FROM PredictHistogram(TargetString) ORDER BY $Probability) FROM MyModel NATURAL PREDICTION JOIN

(SELECT 'Factor1' AS Factor1, 'Factor2' AS Factor2) AS t

HTH

-Jamie

Dataflow task -> Error Handling

Hi,
In terms of data flow tasks, when say we load text files into databases.

Is it possible to have it in a way so that if a certain record (line in the text file) fails to load due to watever reason, it gets written to another table, but the rest of the records still get loaded?

I try to do so and end up with the whole data flow task failing and it stalls at the record that had the error and doesn't seem to continue forward.

I just used the red arrow (on failure) and put that to another SQL destination object. But yeah that didnt work.

If someone has a better way of doing so, would be awesome if you can share that.

Cheers

You have to set the 'Access mode' property of 'OleDB DEstination' OpenRowset.It should not be fastload.Then set ErrorOuput as Redirect Row. Now you can get your erroneous rows redirected.

This should do.

|||yup...i didnt know about the accessmode property. that did it. thanks
|||Dear all,

I face the same problem but I didn't find the 'Access mode' property of 'OleDB DEstination' that contain OpenRowset its only contain Tabel and View with Fastload. Where I can find that property ?
And what if the error occured in Flat File Source ? can we still using the same solution ? Thanks in advance.

Best Regards,

Hery|||Dear all,

Please help me on this.

Thanks in advance.

Dataflow task -> Error Handling

Hi,
In terms of data flow tasks, when say we load text files into databases.

Is it possible to have it in a way so that if a certain record (line in the text file) fails to load due to watever reason, it gets written to another table, but the rest of the records still get loaded?

I try to do so and end up with the whole data flow task failing and it stalls at the record that had the error and doesn't seem to continue forward.

I just used the red arrow (on failure) and put that to another SQL destination object. But yeah that didnt work.

If someone has a better way of doing so, would be awesome if you can share that.

Cheers

You have to set the 'Access mode' property of 'OleDB DEstination' OpenRowset.It should not be fastload.Then set ErrorOuput as Redirect Row. Now you can get your erroneous rows redirected.

This should do.

|||yup...i didnt know about the accessmode property. that did it. thanks
|||Dear all,

I face the same problem but I didn't find the 'Access mode' property of 'OleDB DEstination' that contain OpenRowset its only contain Tabel and View with Fastload. Where I can find that property ?
And what if the error occured in Flat File Source ? can we still using the same solution ? Thanks in advance.

Best Regards,

Hery|||Dear all,

Please help me on this.

Thanks in advance.

Dataflow task -> Error Handling

Hi,
In terms of data flow tasks, when say we load text files into databases.

Is it possible to have it in a way so that if a certain record (line in the text file) fails to load due to watever reason, it gets written to another table, but the rest of the records still get loaded?

I try to do so and end up with the whole data flow task failing and it stalls at the record that had the error and doesn't seem to continue forward.

I just used the red arrow (on failure) and put that to another SQL destination object. But yeah that didnt work.

If someone has a better way of doing so, would be awesome if you can share that.

Cheers

You have to set the 'Access mode' property of 'OleDB DEstination' OpenRowset.It should not be fastload.Then set ErrorOuput as Redirect Row. Now you can get your erroneous rows redirected.

This should do.

|||yup...i didnt know about the accessmode property. that did it. thanks
|||Dear all,

I face the same problem but I didn't find the 'Access mode' property of 'OleDB DEstination' that contain OpenRowset its only contain Tabel and View with Fastload. Where I can find that property ?
And what if the error occured in Flat File Source ? can we still using the same solution ? Thanks in advance.

Best Regards,

Hery|||Dear all,

Please help me on this.

Thanks in advance.

DataFlow Task & Filters

Hi,

I am getting data from an external source. External data has a column called "Type". I have a variable in my package which contains the list of types as shown below:

Filtered_type_List = 2,4,8,10,11

If this variable(Filtered_type_List) is blank, then I need all the data from the external source and if it is not blank then I only need the records matching to his list. How can I implement this in DataFlow Task?

Thanks

You could do this in an expression. Something like:

"SELECT * FROM MyTable " + (LEN(MySSISVariable) != 0 ? "WHERE MyColumn IN (" + MySSISVariable + ")" : "" )

That expression will (I think) add a WHERE clause if the length of the string inside the variable (which I have called MySSISVariable) is not zero.

HTH

-Jamie

|||

Hi Jamie,

Where should I put this "Select" statement,

1. Source using SQL Command as variable using OLE DB Source or

2. Lookup transformation

Thanks

|||

OLE DB Source. Set it to 'SQL Command from variable' and paste the expression that I provided above into the variable expression. The variable will require EvaluateAsExpression=TRUE.

-Jamie

DataFlow suggestion

I am transfering data from a textfile to sql server.I use a data flow task for trasfering my text files.

Here is what i do.

1.Add text file source

What i want to achieve here is if the text file countains the column name in the first row i should delete them and if it does not contain column name in the first row just transfer it.

how can this be achieved?

2.add one more column to my text file which should contain the status(insert or update).

how can this be done?

3.before transfering data ot destination i want to know if the record exists if exists i just want to update it instead of insert.and if new record i want to insert it .and the status in the above new column need to change.

please help...

Not sure without looking at your data, but look into using the conditional split transformation to perform your logic checks. Use a derived column transformation to add a column.|||

sureshv wrote:

1.Add text file source

What i want to achieve here is if the text file countains the column name in the first row i should delete them and if it does not contain column name in the first row just transfer it.

how can this be achieved?

There is a property on the flat file connection manager that you can configure for that (ColumnsNamesInFirstDatarow).

sureshv wrote:

2.add one more column to my text file which should contain the status(insert or update).

how can this be done?

Use a derived column to add extra columns to data flow pipeline

sureshv wrote:

3.before transfering data ot destination i want to know if the record exists if exists i just want to update it instead of insert.and if new record i want to insert it .and the status in the above new column need to change.

There are tons of threads discussing that issue; the most popular is to use Lookup transform against the destination table; if a match occurs means the row already exists; otherwise it does not.

|||

In the first case.

Data is just a text file with column names in the first row.

eg:

name address telno

xxx yyy zzz

xxx sdfdsf sdfsf

But i dont want the column names coming from text file. instead i want to have my own column names(which i can and know to create)

If i transter data as in the text file the column names would appear in the first row.just to get rid of it i want to remove column name from text file.

In second case:

I use derived column to add a column,and conditional split for bad data.

My point is in the new column how should i insert the status(insert) or )update.

what should i use.

|||

There is a property on the flat file connection manager that you can configure for that (ColumnsNamesInFirstDatarow).

I dont want to use this i can have and want to have my own column names.WHich can be done and i know about it.

please look at my reply earlier

|||

sureshv wrote:

My point is in the new column how should i insert the status(insert) or )update.

see if this helps:

http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

you could add a derived column transform at after each lookup transform and add there a column with the expected value(insert/update)...

Dataflow error in lookup task : "Object was open.".

I Can't reproduce the error if I run the package stand-alone.

I'm using the same lookup call (same table, etc.) in 2 packages that are running in parallel (called by a parent package).

[LKP_UnderwriterId [72283]] Error: An OLE DB error has occurred. Error code: 0x80040E05. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E05 Description: "Object was open.".

Anyone seen this one?

Googling threw this up: http://www.sharepointu.com/forums/m_23343/tm.htm

Could it be that something isn't installed properly on the machine on which you are running the package stand-alone (N.B. Can you clarify what you mean by "stand-alone"?)

-Jamie

|||If the package is run serially (so no other packages are running using a lookup against the same table) or run in the debugger, then no error occurs.

DataFlow EngineThreads Error

Hi,
I have a SSIS package which is scheduled to run via SQL Agent. I have a data flow task within the SSIS Package which has a single source adapter and single destination adapter & lookup transformations.

The # of engine threads for the task is 5 (default). The max concurrent executables on the package is 20. The packge contains a whole lot of execute sql tasks etc.

The package after few (3-4) successful runs started to give following error for the data flow:

"The Data Flow task engine failed at startup because it cannot create one or more required threads"
This could be seen from the log files.
OR
"Data Flow task failed to create a required thread and cannot begin running. The usually occurs when there is an out-of-memory state."

Even if concurrent exectuables are set to -1 it is same.

Now the Package keeps on failing. What is the cause of this problem. I have not run, but I am sure once run through BIDS it would work fine.

Any hints on the same? This has put us in a trouble spot.

Server Config:
4 - WAY, 16 GB RAM, enough disk!

Thanks,
Gaurav

Hi Gaurav,

I am also facing this same problem, after few sucessful run the package is returning above error now.

that is :

[DTS.Pipeline] Error: The Data Flow task failed to create a required thread and cannot begin running. The usually occurs when there is an out-of-memory state.

Any solution you have got? Please share it with me.

Thanks a lot.

Swarna,

|||

What service pack level are you running?

|||

Hi,

Thanks for your reply, I am running with SP2. Here the problem is i can not run the package at all, even in BIDS i am getting the above error.

Thanks.

Regards,

Swarna.

|||

Has anybody got the solution for this?

I have a package which was running perfectly and suddenly it blows up with this memory error?

Please can anybody share the solution?

Thanks,

Prash.

|||

Well My pakage is having too many lookups.

When I incresed the cache memory from 5mb(default) to 30mb it stared running again..

so I think it can be one of the solutions

prash

|||

Which cache you are refering? any package setting?

Swarna.

|||I believe he is referring to the properties on the Advanced tab of the lookup - under Enable Memory Restriction.

|||yes u are rite!!|||

For my case, i am not using so many look ups, but still i am facing the same problem. Any idea?

Thanks,

Swarna.

|||in fact my package was running well when there were about million resords fetched in lookup. but as the records incresed to 2.5 millions package suddenly it stopped and i was kicked out. i saw progress of package . actually debugger was showing that all stages are fetchin number of records but still my data flow was showing yellow color with the message as package finished execution. I was really confused. but some how I incresed the cache memory of every loookup in dataflow and it stared executing normally....|||

Swarna wrote:

For my case, i am not using so many look ups, but still i am facing the same problem. Any idea?

Thanks,

Swarna.

For the lookups that you do have, is memory restriction enabled?

|||swarna can u check debugger and see wether its showing any info about records being fetched in various stages?|||

Hope I'm not too late in giving my feedback here. The issue that we have faced here is where there are not too many lookups object, but it keeps giving the same error message.

What I have tested was to optimize the usage of the Lookup object by not selecting the "Use a table or a view:" selection. Instead, I chose the "Use Results of an SQL Query:" selection. I had only 3 lookup objects, 2 of which are looking up to a table with less than 10000 records and less than 8 columns in average. But the other table that we did the lookup has about 2 million worth of records, with about 10 - 12 columns.

I did the changes for all of the lookup object and some how everything was working again. I think it would be the same issue with the memory restrictions as well.

DataFlow EngineThreads Error

Hi,
I have a SSIS package which is scheduled to run via SQL Agent. I have a data flow task within the SSIS Package which has a single source adapter and single destination adapter & lookup transformations.

The # of engine threads for the task is 5 (default). The max concurrent executables on the package is 20. The packge contains a whole lot of execute sql tasks etc.

The package after few (3-4) successful runs started to give following error for the data flow:

"The Data Flow task engine failed at startup because it cannot create one or more required threads"
This could be seen from the log files.
OR
"Data Flow task failed to create a required thread and cannot begin running. The usually occurs when there is an out-of-memory state."

Even if concurrent exectuables are set to -1 it is same.

Now the Package keeps on failing. What is the cause of this problem. I have not run, but I am sure once run through BIDS it would work fine.

Any hints on the same? This has put us in a trouble spot.

Server Config:
4 - WAY, 16 GB RAM, enough disk!

Thanks,
Gaurav

Hi Gaurav,

I am also facing this same problem, after few sucessful run the package is returning above error now.

that is :

[DTS.Pipeline] Error: The Data Flow task failed to create a required thread and cannot begin running. The usually occurs when there is an out-of-memory state.

Any solution you have got? Please share it with me.

Thanks a lot.

Swarna,

|||

What service pack level are you running?

|||

Hi,

Thanks for your reply, I am running with SP2. Here the problem is i can not run the package at all, even in BIDS i am getting the above error.

Thanks.

Regards,

Swarna.

|||

Has anybody got the solution for this?

I have a package which was running perfectly and suddenly it blows up with this memory error?

Please can anybody share the solution?

Thanks,

Prash.

|||

Well My pakage is having too many lookups.

When I incresed the cache memory from 5mb(default) to 30mb it stared running again..

so I think it can be one of the solutions

prash

|||

Which cache you are refering? any package setting?

Swarna.

|||I believe he is referring to the properties on the Advanced tab of the lookup - under Enable Memory Restriction.|||yes u are rite!!|||

For my case, i am not using so many look ups, but still i am facing the same problem. Any idea?

Thanks,

Swarna.

|||in fact my package was running well when there were about million resords fetched in lookup. but as the records incresed to 2.5 millions package suddenly it stopped and i was kicked out. i saw progress of package . actually debugger was showing that all stages are fetchin number of records but still my data flow was showing yellow color with the message as package finished execution. I was really confused. but some how I incresed the cache memory of every loookup in dataflow and it stared executing normally....|||

Swarna wrote:

For my case, i am not using so many look ups, but still i am facing the same problem. Any idea?

Thanks,

Swarna.

For the lookups that you do have, is memory restriction enabled?

|||swarna can u check debugger and see wether its showing any info about records being fetched in various stages?|||

Hope I'm not too late in giving my feedback here. The issue that we have faced here is where there are not too many lookups object, but it keeps giving the same error message.

What I have tested was to optimize the usage of the Lookup object by not selecting the "Use a table or a view:" selection. Instead, I chose the "Use Results of an SQL Query:" selection. I had only 3 lookup objects, 2 of which are looking up to a table with less than 10000 records and less than 8 columns in average. But the other table that we did the lookup has about 2 million worth of records, with about 10 - 12 columns.

I did the changes for all of the lookup object and some how everything was working again. I think it would be the same issue with the memory restrictions as well.

DataFlow EngineThreads Error

Hi,
I have a SSIS package which is scheduled to run via SQL Agent. I have a data flow task within the SSIS Package which has a single source adapter and single destination adapter & lookup transformations.

The # of engine threads for the task is 5 (default). The max concurrent executables on the package is 20. The packge contains a whole lot of execute sql tasks etc.

The package after few (3-4) successful runs started to give following error for the data flow:

"The Data Flow task engine failed at startup because it cannot create one or more required threads"
This could be seen from the log files.
OR
"Data Flow task failed to create a required thread and cannot begin running. The usually occurs when there is an out-of-memory state."

Even if concurrent exectuables are set to -1 it is same.

Now the Package keeps on failing. What is the cause of this problem. I have not run, but I am sure once run through BIDS it would work fine.

Any hints on the same? This has put us in a trouble spot.

Server Config:
4 - WAY, 16 GB RAM, enough disk!

Thanks,
Gaurav

Hi Gaurav,

I am also facing this same problem, after few sucessful run the package is returning above error now.

that is :

[DTS.Pipeline] Error: The Data Flow task failed to create a required thread and cannot begin running. The usually occurs when there is an out-of-memory state.

Any solution you have got? Please share it with me.

Thanks a lot.

Swarna,

|||

What service pack level are you running?

|||

Hi,

Thanks for your reply, I am running with SP2. Here the problem is i can not run the package at all, even in BIDS i am getting the above error.

Thanks.

Regards,

Swarna.

|||

Has anybody got the solution for this?

I have a package which was running perfectly and suddenly it blows up with this memory error?

Please can anybody share the solution?

Thanks,

Prash.

|||

Well My pakage is having too many lookups.

When I incresed the cache memory from 5mb(default) to 30mb it stared running again..

so I think it can be one of the solutions

prash

|||

Which cache you are refering? any package setting?

Swarna.

|||I believe he is referring to the properties on the Advanced tab of the lookup - under Enable Memory Restriction.

|||yes u are rite!!|||

For my case, i am not using so many look ups, but still i am facing the same problem. Any idea?

Thanks,

Swarna.

|||in fact my package was running well when there were about million resords fetched in lookup. but as the records incresed to 2.5 millions package suddenly it stopped and i was kicked out. i saw progress of package . actually debugger was showing that all stages are fetchin number of records but still my data flow was showing yellow color with the message as package finished execution. I was really confused. but some how I incresed the cache memory of every loookup in dataflow and it stared executing normally....|||

Swarna wrote:

For my case, i am not using so many look ups, but still i am facing the same problem. Any idea?

Thanks,

Swarna.

For the lookups that you do have, is memory restriction enabled?

|||swarna can u check debugger and see wether its showing any info about records being fetched in various stages?

Databases missing from Backup Task of Maintenance Plan

I'm moving databases from SQL7 to another server with SQL2005.

I have created new databases in SQL2005 via a restore from backup files created in the old server. Now I want to back up these new databases but they don't show in the Backup Task of a new or existing Maintenance Plan in Management Studio. I can backup them manually though. Any ideas?

I assume you have restored this database to a local 2005 server. If this is the case, they should show up. Maintenance plans can not make connections to SQL 7. Can you see this database via Management Studio?|||

Rob,

Yes, that's correct. I restored the databases to another server running SQL2005. None of the databases show up in any task of Management Studio's Maintenance Plans (eg Backup, Shrink, ReOrg)

-Bill

|||

Found the problem - the compatibility level was set to SQL Server 7.0 by default ( under database properties->options). As soon as I changed the compatiblity level to SQL Server 2000, the databases showed up in the Maintenance Plan tasks.

Thank you for your responses.

Databases

I have been assigned the task of DBA for our company's
payroll system running on SQL server 2k. My question is
this - In the maintenance plan, database backups,
transaction backups, etc...under properties it is backing
up 'master', everything is fine. However, our database
is 'WFT' shouldn't the database be set to WFT instead
of 'master'? Or does master contain all databases? I just
want to be sure we are getting the proper backup. Also
the job has the No Truncate command which causes the
event 17055. Can this command be omitted and everything
still be OK?
Thanks in advance.
DarrylThe link here should help you
http://msdn.microsoft.com/library/d...>
_03_378k.asp
I suggest making 2 maintenance plans. I would do one for your System
Databases and select All System Databases in the wizard. Then do another
for All User Databases. For Backups I also have the wizard put all backups
in their own subdirectories. This makes viewing easier and you can easily
see what is going on.
Jeff Duncan
MCDBA, MCSE+I
"Darryl" <anonymous@.discussions.microsoft.com> wrote in message
news:824801c431db$326c6560$a001280a@.phx.gbl...
> I have been assigned the task of DBA for our company's
> payroll system running on SQL server 2k. My question is
> this - In the maintenance plan, database backups,
> transaction backups, etc...under properties it is backing
> up 'master', everything is fine. However, our database
> is 'WFT' shouldn't the database be set to WFT instead
> of 'master'? Or does master contain all databases? I just
> want to be sure we are getting the proper backup. Also
> the job has the No Truncate command which causes the
> event 17055. Can this command be omitted and everything
> still be OK?
> Thanks in advance.
> Darryl|||in addition to the answers you have been given, might I suggest you ask your
company to put a little investment into training for you (I am assuming that
a payroll system is quite important?!)
--
Br,
Mark Broadbent
mcdba , mcse+i
=============
"Darryl" <anonymous@.discussions.microsoft.com> wrote in message
news:824801c431db$326c6560$a001280a@.phx.gbl...
> I have been assigned the task of DBA for our company's
> payroll system running on SQL server 2k. My question is
> this - In the maintenance plan, database backups,
> transaction backups, etc...under properties it is backing
> up 'master', everything is fine. However, our database
> is 'WFT' shouldn't the database be set to WFT instead
> of 'master'? Or does master contain all databases? I just
> want to be sure we are getting the proper backup. Also
> the job has the No Truncate command which causes the
> event 17055. Can this command be omitted and everything
> still be OK?
> Thanks in advance.
> Darryl

Databases

I have been assigned the task of DBA for our company's
payroll system running on SQL server 2k. My question is
this - In the maintenance plan, database backups,
transaction backups, etc...under properties it is backing
up 'master', everything is fine. However, our database
is 'WFT' shouldn't the database be set to WFT instead
of 'master'? Or does master contain all databases? I just
want to be sure we are getting the proper backup. Also
the job has the No Truncate command which causes the
event 17055. Can this command be omitted and everything
still be OK?
Thanks in advance.
Darryl
The link here should help you
http://msdn.microsoft.com/library/de...es_03_378k.asp
I suggest making 2 maintenance plans. I would do one for your System
Databases and select All System Databases in the wizard. Then do another
for All User Databases. For Backups I also have the wizard put all backups
in their own subdirectories. This makes viewing easier and you can easily
see what is going on.
Jeff Duncan
MCDBA, MCSE+I
"Darryl" <anonymous@.discussions.microsoft.com> wrote in message
news:824801c431db$326c6560$a001280a@.phx.gbl...
> I have been assigned the task of DBA for our company's
> payroll system running on SQL server 2k. My question is
> this - In the maintenance plan, database backups,
> transaction backups, etc...under properties it is backing
> up 'master', everything is fine. However, our database
> is 'WFT' shouldn't the database be set to WFT instead
> of 'master'? Or does master contain all databases? I just
> want to be sure we are getting the proper backup. Also
> the job has the No Truncate command which causes the
> event 17055. Can this command be omitted and everything
> still be OK?
> Thanks in advance.
> Darryl
|||in addition to the answers you have been given, might I suggest you ask your
company to put a little investment into training for you (I am assuming that
a payroll system is quite important?!)
--
Br,
Mark Broadbent
mcdba , mcse+i
=============
"Darryl" <anonymous@.discussions.microsoft.com> wrote in message
news:824801c431db$326c6560$a001280a@.phx.gbl...
> I have been assigned the task of DBA for our company's
> payroll system running on SQL server 2k. My question is
> this - In the maintenance plan, database backups,
> transaction backups, etc...under properties it is backing
> up 'master', everything is fine. However, our database
> is 'WFT' shouldn't the database be set to WFT instead
> of 'master'? Or does master contain all databases? I just
> want to be sure we are getting the proper backup. Also
> the job has the No Truncate command which causes the
> event 17055. Can this command be omitted and everything
> still be OK?
> Thanks in advance.
> Darryl

Databases

I have been assigned the task of DBA for our company's
payroll system running on SQL server 2k. My question is
this - In the maintenance plan, database backups,
transaction backups, etc...under properties it is backing
up 'master', everything is fine. However, our database
is 'WFT' shouldn't the database be set to WFT instead
of 'master'? Or does master contain all databases? I just
want to be sure we are getting the proper backup. Also
the job has the No Truncate command which causes the
event 17055. Can this command be omitted and everything
still be OK?
Thanks in advance.
DarrylThe link here should help you
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_03_378k.asp
I suggest making 2 maintenance plans. I would do one for your System
Databases and select All System Databases in the wizard. Then do another
for All User Databases. For Backups I also have the wizard put all backups
in their own subdirectories. This makes viewing easier and you can easily
see what is going on.
--
Jeff Duncan
MCDBA, MCSE+I
"Darryl" <anonymous@.discussions.microsoft.com> wrote in message
news:824801c431db$326c6560$a001280a@.phx.gbl...
> I have been assigned the task of DBA for our company's
> payroll system running on SQL server 2k. My question is
> this - In the maintenance plan, database backups,
> transaction backups, etc...under properties it is backing
> up 'master', everything is fine. However, our database
> is 'WFT' shouldn't the database be set to WFT instead
> of 'master'? Or does master contain all databases? I just
> want to be sure we are getting the proper backup. Also
> the job has the No Truncate command which causes the
> event 17055. Can this command be omitted and everything
> still be OK?
> Thanks in advance.
> Darryl|||Darryl,
this is a simplification, but a typical plan is to do a
full back up of each of your user databases once a day and
much more frequent transaction log backups (eg every 30
mins). The log backups should be truncated, apart from in
the case of the 'live' log for disaster recovery.
The system databases should also be backed up and their
frequency will depend on how often you add logins, jobs
etc, but perhaps you might start with once a day.
All of this could be done using maintenance plans. Some of
the alternative options are not available in maintenance
plans (eg differential backups), but for this strategy it
is OK.
In books on line (BOL) there are plenty of details
explaining the various posibilities and setup techniques
(see "Backing Up and Restoring Databases").
HTH
Paul Ibison|||in addition to the answers you have been given, might I suggest you ask your
company to put a little investment into training for you (I am assuming that
a payroll system is quite important?!)
--
--
Br,
Mark Broadbent
mcdba , mcse+i
============="Darryl" <anonymous@.discussions.microsoft.com> wrote in message
news:824801c431db$326c6560$a001280a@.phx.gbl...
> I have been assigned the task of DBA for our company's
> payroll system running on SQL server 2k. My question is
> this - In the maintenance plan, database backups,
> transaction backups, etc...under properties it is backing
> up 'master', everything is fine. However, our database
> is 'WFT' shouldn't the database be set to WFT instead
> of 'master'? Or does master contain all databases? I just
> want to be sure we are getting the proper backup. Also
> the job has the No Truncate command which causes the
> event 17055. Can this command be omitted and everything
> still be OK?
> Thanks in advance.
> Darryl