Mapping Output Parameter to a variable!

Hi there,

I am working on SSIS package that gets data from SQL 2005 Database and writes that to a flat file. But I need to write the count of records as part of the header.

Here is what i am trying:

The OLE DB Source is calling a stored procedure and returning two things i.e. a resultset and an output parameter. The data access mode is SQL Command.

Code Snippet

EXEC [Get_logins] ?, ?, ? OUTPUT
In the Set Query Parameters dialogbox, all the three patameters are mapped to three different user variables.

What is happening is that the user variable that is mapped to output parameter is never updated. The header property expression is written as follows

Code Snippet

RIGHT("0000000000" + (DT_STR, 10, 1252)@.LoginCount, 10)

I tried to watch the variable in watch window but to no avail. Any guidance if it is bug or I am missing some thing? Any thoughts, how can I accomplish this? I have also tried adding Row Count Transformation but its variable has the same behaviour. If I set the value of @.LoginCount variable to some value, this initially set value is successfully written to the file header.

Thanks

Paraclete

No bug, the OLE DB Source just doesn't support output parameters from stored procedures. The Execute SQL Task does, though. You could execute that one in your control flow and put your resultset in a variable. A script source component can shred the resultset into rows in your Data Flow.

Or you could issue two queries. One to count the rows and put that value in a variable in the Control Flow, and then another one in the Data Flow to produce the rows.
|||

Hi,

Thanks for your response. Yes I can calculate the number of rows in a separate query, but some of the rows may have bad data. So in this case these rows will be ignored or sent to error output i.e. will not be written to the Flat File Destination. So the count taken in a separate query will be incorrect i.e. CountATStart-Errors not the CountAtstart. This may create problem becuase the header has count of records in the file. Any guidance/thoughts are wellcomed.

Thanks,

Paraclete

|||You were probably on the right track with the Row Count transformation, but it won't write to the variable until all the rows have been recieved, at which point you've already written your header. Try putting a Sort component after the Row Count. This will queue up the rows between the Row Count and the Destination and should allow Row Count to set the variable before the header gets created. By the way, how are you writing the header?

Mapping columns in custom Destination component

I am working on a destination component where the columns in the destination are already set. I want to give the user the option to map columns, similar to the way the Excel destination component does it. So, the Available Input Columns could be mapped to Available Destination Columns. The Available Input Columns would come from a connection from some other component (either a source or a transformation component), and the Available Destination Columns would be generated from the Data Source.

Is there a way to do this without creating a custom ui?

Yes, you can get this for free in the advanced UI, if you store your Available Destination Columns to the ExternalMetadataColumnCollection on the destination input and set IsUsed flag on this collection to true.

HTH.

|||Man, I suspected it had to do with the external metadata columns, but I couldn't get them to show in the UI. Setting the IsUsed property to "true" was the answer. Thanks a lot Bob.

Many-To-Many Self-Join

I hope somebody can help me with a rather complicated problem I am having involving triggers and foreign keys.

I am working on a database that manages projects in terms of Tasks and the other Tasks (I call them "Pretasks") that must be completed before the Task in question can be completed. Any Task can be a Pretask for one or more other Tasks, and any Task can have any number of Pretasks, which calls for a many-to-many self-join on my tasks table, tblTask. I handle this with a linking table, tblTaskChain, which contains two fields, CurrentTask and Pretask, each of which contains a TaskID referencing a record in tblTask.

I hit my first snag trying to ensure referential integrity in this setup. I opened tblTaskChain in Enterprise Manager's Design Table screen and added a foreign key relationship between tblTaskChain.CurrentTask and tblTask.TaskID, checking the Cascade Delete checkbox to avoid the creation of orphan records when records are deleted from tblTask. No problem. However, when I attempted to do the same with tblTaskChain.Pretask, I got the following error message:

'tblTask' table saved successfully
'tblTaskChain' table
- Unable to create relationship 'FK_tblTaskChain_tblTask'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Introducing FOREIGN KEY constraint 'FK_tblTaskChain_tblTask' on table 'tblTaskChain' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint. See previous errors.

I found that I could create the FK relationship with cascading deletes on either one of the two fields in tblTaskChain, but not on both. I could do both only if I unchecked the Cascading Delete checkbox on one of them.

Upon consideration, I realized that I needed more than SQL Server's cascading deletes could deliver, anyhow. When a task is deleted, I need all its pretasks and all its pretasks' pretasks to be deleted as well, UNLESS the task to be deleted is also on the pretask chain to some other task. To this end, I deleted the two foreign key constraints and instead added the following triggers to tblTask and tblTaskChain.

CREATE TRIGGER utrTask_del ON [dbo].[tblTask]
FOR DELETE
AS

/**************************************************************************************
2/21/2006
Delete all records from tblTaskChain where CurrentTask or Pretask
equals to TaskID that was deleted

**************************************************************************************/

delete tblTaskChain
where CurrentTask in (
select TaskID
from deleted
)
or Pretask in (
select TaskID
from deleted
)

/*************************************************************************************/

CREATE TRIGGER utrTaskChain_del ON [dbo].[tblTaskChain]
FOR DELETE
AS

/**************************************************************************************
2/21/2006
Delete pretask from tblTask only if it is not a pretask for any additional task

**************************************************************************************/

-- Table variable to hold list of pretasks
declare @.OtherCurrent table(
PretaskID int
)

-- List deleted TaskID's that are also pretasks for other CurrentTasks
-- besides the one in the deleted record
insert @.OtherCurrent(PretaskID)
select distinct C.Pretask
from tblTaskChain C
inner join deleted d
on C.Pretask = d.Pretask
where C.CurrentTask <> d.CurrentTask

-- delete all Tasks that were among those deleted
-- but not among those that are pretasks for additional CurrentTasks
delete tblTask
where TaskID in (
select Pretask
from deleted
)
and TaskID not in (
select PretaskID
from @.OtherCurrent
)

/*************************************************************************************/

I tested this by attempting to delete a task from tblTask which I knew had exactly one pretask which, in turn, had no pretasks. The record was not deleted, and I received the following error message.

Server: Msg 217, Level 16, State 1, Procedure utrTaskChain_del, Line 28
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Similarly, when I attempted to delete a record from tblTaskChain, I got:

Server: Msg 217, Level 16, State 1, Procedure utrTask_del, Line 12
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

And that's where things stand. I didn't mean to write a novel here. This has been a long message, but I hope I have made the problem clear. Someone must have faced something similar; can you help?

Many thanks.

Sheldon Penner

is it possible for you to change the database design? I would suggest the following structure

TaskID PreTaskID MasterTaskID

1 1 1

2 1 1

3 2 1

In this case you can identify all records where MasterTaskID is 1.

Does this help?

|||Alternatively, you could store pretasks in a different table and just join with it|||

Thank you for your suggestion, Brokenrulz, but no, it doesn't help.

If I understand you correctly, MasterTaskID would represent the "root" task of which all the others are branches. This might be useful if I wanted to delete an entire tree of tasks, but that is rarely the case.

In my database, each "root task" (i.e., each task that is not a pretask for any other task) may have any number of pretasks and each of those pretasks may also have any number of pretasks, etc. If any task in the tree is deleted, I need the pretask chain leading up to that task only to be deleted as well. Tasks that lead to the root by some other chain must not be deleted, nor tasks that are also part of a chain leading to some other root. I need to be able to cut any branch off the tree, leaving all other branches and the root intact.

|||Some sample data in table form would help the understanding.|||

Here is some sample data:

tblTask
TaskID TaskName
-- -
1 Build house
2 Buy lot
3 Get lumber
4 Hire contractor
5 Pay gov't fees
6 Contact broker
7 Check World Wide Web for brokers and contractors
8 Check newspapers for Broker ads
9 Check phone book for brokers and contractors
10 Research gov't fees
11 Ask broker for info re gov't fees
12 Search web for info re gov't fees
15 Have contractor obtain lumber
17 Ask friends for personal referrals
18 Get bids from interested contractors
19 Check newspapers for lots for sale
86 Write program to evaluate bids
93 Research bid evalaution techniques

tblTaskChain
CurrentTask PreTask
-- --
1 2
1 3
1 4
1 5
1 86
2 6
2 8
2 19
3 15
4 9
4 17
4 18
5 10
5 11
5 12
6 7
6 8
6 9
10 11
10 12
11 6
15 4
18 7
18 9
18 17
86 93

|||

if the database is sql 2005, you can use recursive queries ot get your require data The sample below gives all Pretask that are for CurrentTask ID 10.

WITH CTE(CurrentTask,PreTask)

AS

(

SELECT T1.CurrentTask,T1.PreTask

FROM tblTaskChain T1

WHERE T1.CurrentTask=10

UNION ALL

SELECT T2.CurrentTask,T2.PreTask

FROM tblTaskChain T2

INNER JOIN CTE

ON

T2.CurrentTask=CTE.PreTask

)

SELECT CurrentTask,PreTask FROM CTE

|||If I understand this right, it is a hard problem, but let me try to understand.

Suppose this is your task chain:

CurrentTask PreTask
-- --
1 2
2 3
3 4
4 5
5 6

If someone tries to delete task #3, you should delete all tasks, right?

Now suppose this is your task chain (same as above with two additional entries)

CurrentTask PreTask
-- --
1 2
2 3
3 4
4 5
5 6
7 6
1 7

Again, suppose someone tries to delete task #3. What should you delete? You might start by deleting its only pretask (#4), and then delete #5 (because it is the only pretask for #4, which you just deleted). Then you go to delete #6, because it is the only pretask for #5. But you can't delete task #6, because it is a pretask for something other than #5 (#7).

How far do you roll this back? Can you delete #3 at all? If the last row (1, 7) were missing, would you still be unable to delete #7?

Now suppose this is your task chain:

CurrentTask PreTask
-- --
1 2
2 3
3 4
4 5
5 6
7 6
4 8
8 7

Now is it ok to delete 3, 4, 5, 6, 7, and 8?

My best guess at a rule is this. If task X is to be deleted, identify all tasks that depend on X through a direct chain of tasks, and call this set D(X) (include X in this set). Now consider X together with all all its pretasks, prepretasks, etc., and call this set P(X). In other words, D(X) is the directly dependent set of tasks, and P(X) is the prerequisite set of tasks.

Now check to see if P(D(X)) = P(X) union D(X). If so, mark the dependents of X for deletion. Next, check to see if D(P(X)) = P(X) union D(X). If so, mark the prerequisites of X for deletion. Then delete any marked tasks.

If this is right, I don't see how to handle this without some effort. I have some ideas, but none of them is easy. Hopefully you have some business rules that restrict the generality of the problem and make it easier to handle.

Steve Kass
Drew University
|||

When a task is deleted, I need all tasks on the task chain leading to it to be deleted, unless a particular task is also part of the task chain for a different task, in which case it stays.

The principle is: If we are not going to do A after all, there's no point in keeping the tasks that must be completed only so that we can do A.

When I initially submitted my question, I was hoping for some comment on the two triggers I included in their entirety. When I read them, they appear to be correct, but for reasons I have not yet been able to figure out, they cause the errors I described.

|||

Unfortunately, I am using SQL Server 2000, not 2005.

I was hoping somebody would comment on the two triggers I included in their entirety in my first posting. They appear to address the problem exactly, but they cause the errors I described in that posting.

|||

Sheldon,

The error message "Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)." means that you've blown the recursion stack. You can't have a procedure call itself more than 32 times before SQL Server stops the procedure.

I think that the problem here is that your delete triggers always runs a delete against the other table, which then always runs a delete against the table that initiated the trigger. Here's a sort of "pseudocode" example that I think might make clear what's going on.

/*This is the outside statement that starts the ball rolling.*/
delete tblTaskChain where <some criteria>

/* The delete triggers the following. */
utrTaskChain_del:
<fill OtherCurrent table>
delete tblTask <some criteria>

/* That delete sets off the trigger on the other table. */
utrTask_del:
delete tblTaskChain where <some criteria>

/* Which sets off the trigger on the first table. */
utrTaskChain_del:
<fill OtherCurrent table>
delete tblTask <some criteria>

/* Even though the delete didn't actually remove anything from the table this time, the delete was still called, so the trigger gets set off. */
utrTask_del:
delete tblTaskChain where <some criteria>

/* And as you can see, it doesn't stop. */

I don't see where the recursion will ever stop. So what happens is that the code goes back and forth 32 times and then on the 33rd SQL Server says that this has gone over the limit and throws the error message you saw.

A solution is to check to see whether or not you need to delete before you actually call it. That way when you get to the end of a branch to delete you can be sure that it will terminate.

If you want more information about recursion in SQL Server, I found this site to be useful: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro03/html/sp03i8.asp

Nick Stipanovich
SQL Server Build Team

|||

Thank you, Nick.

Do understand correctly that the Delete event gets fired even if no records were actually deleted?

Suppose I preceded the delete statements in the two triggers with:

declare @.cnt int
set @.cnt = count(*) from deleted
if @.cnt > 0 BEGIN
...

Would that do the job? (I'll know before you reply to this because I'm gonna try it right now!)

Thanks again.

Sheldon

Many to Many to Many

In my DB I am working on, I have 4 many-to-many relationships.
However, I now need to join two of those together to create a
many-to-many-to-many relationship.
Is this possible? I have tried what i thought was correct (creating another
join table) and got erroneous results to say the least.
better to post ddl or give more real details
>--Original Message--
>In my DB I am working on, I have 4 many-to-many
relationships.
>However, I now need to join two of those together to
create a
>many-to-many-to-many relationship.
>Is this possible? I have tried what i thought was
correct (creating another
>join table) and got erroneous results to say the least.
>
>.
>
sql

Many to Many to Many

In my DB I am working on, I have 4 many-to-many relationships.
However, I now need to join two of those together to create a
many-to-many-to-many relationship.
Is this possible? I have tried what i thought was correct (creating another
join table) and got erroneous results to say the least.better to post ddl or give more real details
>--Original Message--
>In my DB I am working on, I have 4 many-to-many
relationships.
>However, I now need to join two of those together to
create a
>many-to-many-to-many relationship.
>Is this possible? I have tried what i thought was
correct (creating another
>join table) and got erroneous results to say the least.
>
>.
>

Mantanace Plan not deleting old files

I have a maintanace Plan establishe that is working excep
for on nagging item. The plan does both full backups and
transaction log backups each day. It is supposed to
delete files older than 3 days old but does not. I have
to manually go and clear the old files on a regular basis
to keep from filling up the disk. I have not seen any
error or know what to look for to see if something is
wrong. I thought that if a full backup was done that it
emptyed the transaction logs but that dies not appear to
be the case either and is why I have the transition logs
in the mainance plan. Any information on how to get the
old fikes to delete?
Here is a good summary of the normal issues related to this by Bill from MS:
http://support.microsoft.com/default...;en-us;Q303292
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Andrew J. Kelly SQL MVP
"Jim Abel" <jim.abel@.lmco.com> wrote in message
news:177f01c49cbe$504699e0$a301280a@.phx.gbl...
> I have a maintanace Plan establishe that is working excep
> for on nagging item. The plan does both full backups and
> transaction log backups each day. It is supposed to
> delete files older than 3 days old but does not. I have
> to manually go and clear the old files on a regular basis
> to keep from filling up the disk. I have not seen any
> error or know what to look for to see if something is
> wrong. I thought that if a full backup was done that it
> emptyed the transaction logs but that dies not appear to
> be the case either and is why I have the transition logs
> in the mainance plan. Any information on how to get the
> old fikes to delete?
|||i had the same problem. RIght-click on the maintenance plan and look at the
job history for any errors.
The problem I had was someone set up a maintenance plan to backup ALL
databases and to do transaction lo backups periodically. The trouble with
that is the system DBs (and any user DBs that are not set to FULL recovery
mode) cannot have Transaction log backups performed on them. So the Backups
were running, but the delete step was not running because the transaction log
backup step failed for some of the DBs.
hope that helps
"Andrew J. Kelly" wrote:

> Here is a good summary of the normal issues related to this by Bill from MS:
>
> http://support.microsoft.com/default...;en-us;Q303292
> This is likely to be either a permissions problem or a sharing violation
> problem. The maintenance plan is run as a job, and jobs are run by the
> SQLServerAgent service.
> Permissions:
> 1. Determine the startup account for the SQLServerAgent service
> (Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
> account is the security context for jobs, and thus the maintenance plan.
> 2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
> account) then skip step 3.
> 3. On that box, log onto NT as that account. Using Explorer, attempt to
> delete an expired backup. If that succeeds then go to Sharing Violation
> section.
> 4. Log onto NT with an account that is an administrator and use Explorer to
> look at the Properties|Security of the folder (where the backups reside)
> and ensure the SQLServerAgent startup account has Full Control. If the
> SQLServerAgent startup account is LocalSystem, then the account to consider
> is SYSTEM.
> 5. In NT, if an account is a member of an NT group, and if that group has
> Access is Denied, then that account will have Access is Denied, even if
> that account is also a member of the Administrators group. Thus you may
> need to check group permissions (if the Startup Account is a member of a
> group).
> 6. Keep in mind that permissions (by default) are inherited from a parent
> folder. Thus, if the backups are stored in C:\bak, and if someone had
> denied permission to the SQLServerAgent startup account for C:\, then
> C:\bak will inherit access is denied.
> Sharing violation:
> This is likely to be rooted in a timing issue, with the most likely cause
> being another scheduled process (such as NT Backup or Anti-Virus software)
> having the backup file open at the time when the SQLServerAgent (i.e., the
> maintenance plan job) tried to delete it.
> 1. Download filemon and handle from www.sysinternals.com.
> 2. I am not sure whether filemon can be scheduled, or you might be able to
> use NT scheduling services to start filemon just before the maintenance
> plan job is started, but the filemon log can become very large, so it would
> be best to start it some short time before the maintenance plan starts.
> 3. Inspect the filemon log for another process that has that backup file
> open (if your lucky enough to have started filemon before this other
> process grabs the backup folder), and inspect the log for the results when
> the SQLServerAgent agent attempts to open that same file.
> 4. Schedule the job or that other process to do their work at different
> times.
> 5. You can use the handle utility if you are around at the time when the
> job is scheduled to run.
> If the backup files are going to a \\share or a mapped drive (as opposed to
> local drive), then you will need to modify the above (with respect to where
> the tests and utilities are run).
> Finally, inspection of the maintenance plan's history report might be
> useful.
> Thanks,
> Bill Hollinshead
> Microsoft, SQL Server
>
> --
> Andrew J. Kelly SQL MVP
>
> "Jim Abel" <jim.abel@.lmco.com> wrote in message
> news:177f01c49cbe$504699e0$a301280a@.phx.gbl...
>
>

Mantanace Plan not deleting old files

I have a maintanace Plan establishe that is working excep
for on nagging item. The plan does both full backups and
transaction log backups each day. It is supposed to
delete files older than 3 days old but does not. I have
to manually go and clear the old files on a regular basis
to keep from filling up the disk. I have not seen any
error or know what to look for to see if something is
wrong. I thought that if a full backup was done that it
emptyed the transaction logs but that dies not appear to
be the case either and is why I have the transition logs
in the mainance plan. Any information on how to get the
old fikes to delete?Here is a good summary of the normal issues related to this by Bill from MS:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q303292
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Andrew J. Kelly SQL MVP
"Jim Abel" <jim.abel@.lmco.com> wrote in message
news:177f01c49cbe$504699e0$a301280a@.phx.gbl...
> I have a maintanace Plan establishe that is working excep
> for on nagging item. The plan does both full backups and
> transaction log backups each day. It is supposed to
> delete files older than 3 days old but does not. I have
> to manually go and clear the old files on a regular basis
> to keep from filling up the disk. I have not seen any
> error or know what to look for to see if something is
> wrong. I thought that if a full backup was done that it
> emptyed the transaction logs but that dies not appear to
> be the case either and is why I have the transition logs
> in the mainance plan. Any information on how to get the
> old fikes to delete?|||i had the same problem. RIght-click on the maintenance plan and look at the
job history for any errors.
The problem I had was someone set up a maintenance plan to backup ALL
databases and to do transaction lo backups periodically. The trouble with
that is the system DBs (and any user DBs that are not set to FULL recovery
mode) cannot have Transaction log backups performed on them. So the Backups
were running, but the delete step was not running because the transaction log
backup step failed for some of the DBs.
hope that helps
"Andrew J. Kelly" wrote:
> Here is a good summary of the normal issues related to this by Bill from MS:
>
> http://support.microsoft.com/default.aspx?scid=kb;en-us;Q303292
> This is likely to be either a permissions problem or a sharing violation
> problem. The maintenance plan is run as a job, and jobs are run by the
> SQLServerAgent service.
> Permissions:
> 1. Determine the startup account for the SQLServerAgent service
> (Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
> account is the security context for jobs, and thus the maintenance plan.
> 2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
> account) then skip step 3.
> 3. On that box, log onto NT as that account. Using Explorer, attempt to
> delete an expired backup. If that succeeds then go to Sharing Violation
> section.
> 4. Log onto NT with an account that is an administrator and use Explorer to
> look at the Properties|Security of the folder (where the backups reside)
> and ensure the SQLServerAgent startup account has Full Control. If the
> SQLServerAgent startup account is LocalSystem, then the account to consider
> is SYSTEM.
> 5. In NT, if an account is a member of an NT group, and if that group has
> Access is Denied, then that account will have Access is Denied, even if
> that account is also a member of the Administrators group. Thus you may
> need to check group permissions (if the Startup Account is a member of a
> group).
> 6. Keep in mind that permissions (by default) are inherited from a parent
> folder. Thus, if the backups are stored in C:\bak, and if someone had
> denied permission to the SQLServerAgent startup account for C:\, then
> C:\bak will inherit access is denied.
> Sharing violation:
> This is likely to be rooted in a timing issue, with the most likely cause
> being another scheduled process (such as NT Backup or Anti-Virus software)
> having the backup file open at the time when the SQLServerAgent (i.e., the
> maintenance plan job) tried to delete it.
> 1. Download filemon and handle from www.sysinternals.com.
> 2. I am not sure whether filemon can be scheduled, or you might be able to
> use NT scheduling services to start filemon just before the maintenance
> plan job is started, but the filemon log can become very large, so it would
> be best to start it some short time before the maintenance plan starts.
> 3. Inspect the filemon log for another process that has that backup file
> open (if your lucky enough to have started filemon before this other
> process grabs the backup folder), and inspect the log for the results when
> the SQLServerAgent agent attempts to open that same file.
> 4. Schedule the job or that other process to do their work at different
> times.
> 5. You can use the handle utility if you are around at the time when the
> job is scheduled to run.
> If the backup files are going to a \\share or a mapped drive (as opposed to
> local drive), then you will need to modify the above (with respect to where
> the tests and utilities are run).
> Finally, inspection of the maintenance plan's history report might be
> useful.
> Thanks,
> Bill Hollinshead
> Microsoft, SQL Server
>
> --
> Andrew J. Kelly SQL MVP
>
> "Jim Abel" <jim.abel@.lmco.com> wrote in message
> news:177f01c49cbe$504699e0$a301280a@.phx.gbl...
> > I have a maintanace Plan establishe that is working excep
> > for on nagging item. The plan does both full backups and
> > transaction log backups each day. It is supposed to
> > delete files older than 3 days old but does not. I have
> > to manually go and clear the old files on a regular basis
> > to keep from filling up the disk. I have not seen any
> > error or know what to look for to see if something is
> > wrong. I thought that if a full backup was done that it
> > emptyed the transaction logs but that dies not appear to
> > be the case either and is why I have the transition logs
> > in the mainance plan. Any information on how to get the
> > old fikes to delete?
>
>

Manipulating Namespace Attributes

Hello All:
I have a fair amount of SQL 2000 experience and recentlty have begun
working with SQL 2005. SPecificall, I am working with the XML data
type in the following scenario:
-A third party vendor provides us with a data source containing a
column of datatype XML. I would like to search through the nodes of
the XML for specific elements and attributes. The problem is that some
of the XML fields contain namespaces (meaning that my xQuery must
declare the namespace as well), and some of the values in this same XML
column do not have namespaces declared. I have no control over the
data sent to me, but what I would like to do is remove the namespace
attribute where it exists. I have tried the modify method, but it does
not work on the xmlns attribute.
I can transform the field to nText and then parse this out, but I would
perfer to avoid any extra steps since it is a large quantity of data
and some of these xml fields are quite large.
I would be grateful for any advice on how to handle this.
Thanks in advance.
Rich FHave you tried to search using a namespace wildcard?
Like /*.foo/*:attr
namespace declarations are not exposed as attribute and cannot easily be
changed. If you need to change arbitrary namespaces to a specific namespace
you should probably write an XSLT transform.
Best regards
Michael
<eastegg_1970@.yahoo.com> wrote in message
news:1164911773.276725.95190@.j72g2000cwa.googlegroups.com...
> Hello All:
> I have a fair amount of SQL 2000 experience and recentlty have begun
> working with SQL 2005. SPecificall, I am working with the XML data
> type in the following scenario:
> -A third party vendor provides us with a data source containing a
> column of datatype XML. I would like to search through the nodes of
> the XML for specific elements and attributes. The problem is that some
> of the XML fields contain namespaces (meaning that my xQuery must
> declare the namespace as well), and some of the values in this same XML
> column do not have namespaces declared. I have no control over the
> data sent to me, but what I would like to do is remove the namespace
> attribute where it exists. I have tried the modify method, but it does
> not work on the xmlns attribute.
> I can transform the field to nText and then parse this out, but I would
> perfer to avoid any extra steps since it is a large quantity of data
> and some of these xml fields are quite large.
> I would be grateful for any advice on how to handle this.
> Thanks in advance.
> Rich F
>

Manipulating Namespace Attributes

Hello All:
I have a fair amount of SQL 2000 experience and recentlty have begun
working with SQL 2005. SPecificall, I am working with the XML data
type in the following scenario:
-A third party vendor provides us with a data source containing a
column of datatype XML. I would like to search through the nodes of
the XML for specific elements and attributes. The problem is that some
of the XML fields contain namespaces (meaning that my xQuery must
declare the namespace as well), and some of the values in this same XML
column do not have namespaces declared. I have no control over the
data sent to me, but what I would like to do is remove the namespace
attribute where it exists. I have tried the modify method, but it does
not work on the xmlns attribute.
I can transform the field to nText and then parse this out, but I would
perfer to avoid any extra steps since it is a large quantity of data
and some of these xml fields are quite large.
I would be grateful for any advice on how to handle this.
Thanks in advance.
Rich F
Have you tried to search using a namespace wildcard?
Like /*.foo/*:attr
namespace declarations are not exposed as attribute and cannot easily be
changed. If you need to change arbitrary namespaces to a specific namespace
you should probably write an XSLT transform.
Best regards
Michael
<eastegg_1970@.yahoo.com> wrote in message
news:1164911773.276725.95190@.j72g2000cwa.googlegro ups.com...
> Hello All:
> I have a fair amount of SQL 2000 experience and recentlty have begun
> working with SQL 2005. SPecificall, I am working with the XML data
> type in the following scenario:
> -A third party vendor provides us with a data source containing a
> column of datatype XML. I would like to search through the nodes of
> the XML for specific elements and attributes. The problem is that some
> of the XML fields contain namespaces (meaning that my xQuery must
> declare the namespace as well), and some of the values in this same XML
> column do not have namespaces declared. I have no control over the
> data sent to me, but what I would like to do is remove the namespace
> attribute where it exists. I have tried the modify method, but it does
> not work on the xmlns attribute.
> I can transform the field to nText and then parse this out, but I would
> perfer to avoid any extra steps since it is a large quantity of data
> and some of these xml fields are quite large.
> I would be grateful for any advice on how to handle this.
> Thanks in advance.
> Rich F
>

Manintenance Plan

I've created a maintenance plan and getting the following error message in the event log. This has been working for a while problem happend a week a ago, no changes to the system. Novice SQL user, any help on this appreciated.

Event ID 208. SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan 'RSS Pro2000 DBMP'' (0xFE3D7C9C154F9E48A4AA953C88D9F97E) - Status: Failed - Invoked on: 2007-03-19 13:28:00 - Message: The job failed. The Job was invoked by Schedule 13 (Schedule 1). The last step to run was step 1 (Step 1).

Are there any changes to the recovery model during this time of execution?

Also check the password or any information pertaining to SQLAgent account used here.

http://www.sqlservercentral.com/columnists/aingold/workingaround2005maintenanceplans.asp fyi.

|||No changes made. SQLAgent using 'Local System' a/c|||

Try to execute the job as manually with your account credential.

Do you have any other databases scheduled in this database?

if so are they getting same error?

|||The Db's are being backed up (there's 4 in total). It's only the transaction log that are not running.I've changed the a/c to 'administrator' and ran another transaction log and getting the same message.|||Have you applied the service pack or any changes to the server recentlY?

managing the transaction log

Hello!

I'm working with an SQL database that someone else has set up and this is a learning experience for me.

I understand what the transaction log is and a little about it.

What i would like to do is shrink it because it is full. If i use the wizard to truncate or shrink data it never seems to work. I have created a second log file but the server doesn't seem to use it. Increasing the log size does nothing also. DARN!

What is the best way to dump the old data?

thanks in advance?

RIMQ1 [i would like to do is shrink it because it is full]?

A1 Note: one cannot shrink a 'Full' log beyond an active VLF; moreover, one must either dump / back up the contents of a transaction log to a transaction log backup *.trn file (or truncate it) before DBCC ShrinkFile can shrink the file to any smaller size.
Frequently dumping / backing up your (production database) transaction logs to transaction log backup *.trn files will provide the means of point in time recoverability; and also keep the overall DB log size managable. (Typically, production user DBs should be using the Full backup recovery model.)

General production guidelines include:
i The use of DBCC ShrinkFile, (and / or enable autoshrink if appropriate).
ii Identify any long running transactions that may be filling up your DB Log rewrite them to be efficient.
iii Dump the DB transaction log to transaction log backup *.trn files as appropriate for the production environmen.t

DBCC ShrinkFile advantages:
* it is safe
* it may be safely used even if your DB has multiple log files (add several additional log files to your DB, then rigorously test your method)
* ordinary users may work in the DB while its files are being shrunk

Use MyDB
Go
DBCC ShrinkFile ([MyDB_Log], 1, TruncateOnly)
Go|||This was address a couple of weeks ago - check out the link:

link (http://dbforums.com/showthread.php?s=&threadid=546372)

Managing Scheduled Jobs from Management Studio Express

I'm working on a backup solution for my company. Right now we have three servers in three locations running SQL Server 2000. For testing purposes, I created a scheduled job on one of these servers. It worked fine but I'd like to tweak the job some, tinker with the timing and save locations. I'm using Management Studio Express on my laptop to remotely work with these databases but I can't seem to find a decent way to work with existing jobs. Am I missing something or does SSMSE lack the "manage jobs" functionality?

SSMSE only exposes functionality that is available in SQL Express. Since Express doesn't include Agent, SSMSE doesn't include the ability to manage Agent jobs.

The SQL 2005 Feature Pack includes some DTS 2000 add-in that may meet your needs. I've not worked with them, but they may work for you.

Regards,

Mike Wachal
SQL Express team

-
Mark the best posts as Answers!

Managing MSDE and SQL Express...

Hi all,
I'm a newbie so I won't mind if you roll your eyes as you read this...
I have sqlExpress installed on my machine and its working beautifully.
I have used SqlExpress and msde in the past for web development with
great success, but I have always had an issue managing the databases in
Visual Studio.net or VisualWebDev2k5 in as much as its cumbersome and
hard to manage not to mention slower.
Is there any sort of stand-alone interface - like SQL enterprise
manager -- to help with this?
Thanks (in advance) for the help.
hi,
tom.herz@.gmail.com wrote:
> Hi all,
> I'm a newbie so I won't mind if you roll your eyes as you read
> this...
>
> I have sqlExpress installed on my machine and its working beautifully.
> I have used SqlExpress and msde in the past for web development with
> great success, but I have always had an issue managing the databases
> in Visual Studio.net or VisualWebDev2k5 in as much as its cumbersome
> and hard to manage not to mention slower.
> Is there any sort of stand-alone interface - like SQL enterprise
> manager -- to help with this?
> Thanks (in advance) for the help.
please have a look at SQL Server Management Studio Express, currently
available as CTP from
http://www.microsoft.com/downloads/d...displaylang=en
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||all,
> I'm a newbie so I won't mind if you roll your eyes as you read this...
>
> I have sqlExpress installed on my machine and its working beautifully.
> I have used SqlExpress and msde in the past for web development with
> great success, but I have always had an issue managing the databases in
> Visual Studio.net or VisualWebDev2k5 in as much as its cumbersome and
> hard to manage not to mention slower.
> Is there any sort of stand-alone interface - like SQL enterprise
> manager -- to help with this?
Check out Database Workbench at www.upscene.com
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com
|||Thanks. I've looked at both the options and this is what I needed. I
appreciate your time.
Have a great day - you all have certainly done your good deed!!

Managing Distributed Transactions with ADO.NET 2.0 using TransactionScope gives error mess

Hi,

I am working on vs2005 with sql server 2000. I have used TransactionScope class.

Example Reference:

http://www.c-sharpcorner.com/UploadFile/mosessaur/TransactionScope04142006103850AM/TransactionScope.aspx

The code is given below.

using System.Transactions;

protected void Page_Load(object sender, EventArgs e)
{

System.Transactions.TransactionOptions transOption = new System.Transactions.TransactionOptions();
transOption.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
transOption.Timeout = new TimeSpan(0, 2, 0);

using (System.Transactions.TransactionScope tranScope = new System.Transactions.TransactionScope(TransactionScopeOption.Required,transOption))
{
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["nwConnString"].ConnectionString))
{
int i;
con.Open();
SqlCommand cmd = new SqlCommand("update products set unitsinstock=100 where productid=1", con);
i = cmd.ExecuteNonQuery();
if (i > 0)
{
using (SqlConnection conInner = new SqlConnection(ConfigurationManager.ConnectionStrings["pubsConnString"].ConnectionString))
{
conInner.Open();
SqlCommand cmdInner = new SqlCommand("update Salary set sal=5000 where eno=1", conInner);
i = cmdInner.ExecuteNonQuery();
if (i > 0)
{
tranScope.Complete(); // this statement commits the executed query.
}
}
}
}
// Dispose TransactionScope object, to commit or rollback transaction.
}

}

It gives error like

"The partner transaction manager has disabled its support for remote/network transactions. (Exception from HRESULT: 0x8004D025)"

The database I have used is northwind database and pubs database which is by default in sql server 2000.

So, Kindly let me know how to proceed further.

Thanks in advance,

Arun.

Hi,

From your description, it seems that you met the "The partner transaction manager has disabled" error when you want to run the distributed transaction in your project, right?

Generally, the cause of the issue is that you didn't set the transaction service properly. You may following the steps below:

First verify the "Distribute Transaction Coordinator" Service is
running on both database server computer and client computers
1. Go to "Administrative Tools > Services"
2. Turn on the "Distribute Transaction Coordinator" Service if it is not running

If it is running and client application is not on the same computer as
the database server, on the computer running database server
1. Go to "Administrative Tools > Component Services"
2. On the left navigation tree, go to "Component Services > Computers
> My Computer" (you may need to double click and wait as some nodes
need time to expand)
3. Right click on "My Computer", select "Properties"
4. Select "MSDTC" tab
5. Click "Security Configuration"
6. Make sure you check "Network DTC Access", "Allow Remote Client",
"Allow Inbound/Outbound", "Enable TIP" (Some option may not be
necessary, have a try to get your configuration)
7. The service will restart
8. BUT YOU MAY NEED TO REBOOT YOUR SERVER IF IT STILL DOESN'T WORK
(This is the thing drove me crazy before)

On your client computer use the same above procedure to open the
"Security Configuration" setting, make sure you check "Network DTC
Access", "Allow Inbound/Outbound" option, restart service and computer
if necessary.

On you SQL server service manager, click "Service" dropdown, select
"Distribute Transaction Coordinator", it should be also running on
your server computer.

Quoted from community members of MSDN:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=230390&SiteID=1

Thanks.

Management Studio vs. Management Studio Express Edition

I am back doing development after a three year hiatus. Previously I was working with SQL Server 2000 and Enterprise Manager and I need to get up to speed quickly on version 2005.

The computer I am using already has Management Studio Express installed but we have full licensed copies of version 2005 enterprise edition available. I have two questions:

Are there features available in the full version of Management Studio that would compel me to switch from the Express Edition?

If so, how in the heck do I make the switch. Is it as easy as uninstalling the Express Edition and then installing the full version from the 2005 enterprise edition CD?

I already tried installing from the enterprise CD and got the error message cited in other threads about being blocked so I want to make sure I do this right.

Thanks very much for your help and I apologize for the newbie question...

Yes, there is functionality available in the full version of SSMS that is not available with the Express version.

And removing SSMSE and installing SSMS 'should' work just fine.

|||Thanks for the reply. It required a couple of tries to get rid of Management Studio Express edition because of the dependent applications it installs, but it looks like I got it out finally and was able to do the installations of the full version Management Studio from the Enterprise Edition CD. Thanks for your help!

Management Studio startup error: splash screen then nothing

I have got sql 2005 installed and the management studio has stopped working.
Now when I try and open it up, I see the splash screen for a split second and
then nothing.
I tried uninstalling BOL (july 2006) which some posts suggested but it still
doesn't work.
Any ideas?
After the splash screen goes away, do you see sqlWb.exe in Task Manager's
process list?
"adolf garlic" <adolfgarlic@.discussions.microsoft.com> wrote in message
news:0AAA71A7-C4C2-447F-81D1-727DC5C475B0@.microsoft.com...
>I have got sql 2005 installed and the management studio has stopped
>working.
> Now when I try and open it up, I see the splash screen for a split second
> and
> then nothing.
> I tried uninstalling BOL (july 2006) which some posts suggested but it
> still
> doesn't work.
> Any ideas?
|||No.
"Aaron Bertrand [SQL Server MVP]" wrote:

> After the splash screen goes away, do you see sqlWb.exe in Task Manager's
> process list?
>
>
> "adolf garlic" <adolfgarlic@.discussions.microsoft.com> wrote in message
> news:0AAA71A7-C4C2-447F-81D1-727DC5C475B0@.microsoft.com...
>
>
|||Okay, anything in the event log? Do you have error reporting enabled? Are
you running as a local administrator, or a less privileged account?

> No.
|||There is nothing in the event log (warnings or errors) that looks sql related.
I'll see if I can switch on error reporting (if it isn't already on)
I am running as local admin.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Okay, anything in the event log? Do you have error reporting enabled? Are
> you running as a local administrator, or a less privileged account?
>
>
>
|||adolf garlic (adolfgarlic@.discussions.microsoft.com) writes:
> There is nothing in the event log (warnings or errors) that looks sql
> related. I'll see if I can switch on error reporting (if it isn't
> already on) I am running as local admin.
Did you ever have the Express version of Mgmt Studio on your machine?
The Express version and the full version don't go well together.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||I did have the express version of SQL Server with advanced services but
uninstalled it before embarking on the normal version.
"Erland Sommarskog" wrote:

> adolf garlic (adolfgarlic@.discussions.microsoft.com) writes:
> Did you ever have the Express version of Mgmt Studio on your machine?
> The Express version and the full version don't go well together.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
|||adolf garlic (adolfgarlic@.discussions.microsoft.com) writes:
> I did have the express version of SQL Server with advanced services but
> uninstalled it before embarking on the normal version.
And "embarking" means that you installed the normal version?
Clutching at straws, you could try uninstall the tools and then reinstall.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Yup. The whole nutbag (analysis services, reporting services, ssis, sql)
except notification services.
I'll try uninstalling the tools and reinstalling (I think I already tried
this).
Thanks for everyone's suggestions so far
"Erland Sommarskog" wrote:

> adolf garlic (adolfgarlic@.discussions.microsoft.com) writes:
> And "embarking" means that you installed the normal version?
> Clutching at straws, you could try uninstall the tools and then reinstall.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
|||Add remove programs
Remove client tools
reboot
add remove programs
sql server 2005
add new component
navigate to setup.exe
add all client tool components, sample dbs etc
it works
what a pain, this doesn't make sense (working, stops working, working...)
just hope it doesn't happen again
"adolf garlic" wrote:
[vbcol=seagreen]
> Yup. The whole nutbag (analysis services, reporting services, ssis, sql)
> except notification services.
> I'll try uninstalling the tools and reinstalling (I think I already tried
> this).
> Thanks for everyone's suggestions so far
> "Erland Sommarskog" wrote: