Showing posts with label delete. Show all posts
Showing posts with label delete. Show all posts

Wednesday, March 28, 2012

Problems with SQL Task

Hi!

I have a problem running a package and one of its SQL Tasks.

This is the action the task performs:

DELETE FROM Fac_Vendita WHERE Data >= ?

I pick the value from a variable I get from a previous task, and I map it in the new task like this:

Variable name: User::dur Direction: Input Data Type : DBTIMESTAMP Parameter Name: 0

I get this error:

DELETE FROM Fac_Vendita
WHERE Data >= ?" failed with the following error: "Invalid character value for cast specification". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I tried also the other data type, but I get another type of error.... What's the problem?

Try setting the whole thing with a property expression instead - that should help to eliminate these problems.

-Jamie

|||How can I do it?|||

no more help is needed... I found another way to do it

Thanks anyway...

|||i am having the same problem, it would help if you could post your solution.

thanks,

nicolas|||i totally changed the approach, and I built a different statement.... My new SQL states:
DELETE FROM Name_Table WHERE (select .....)
this way it works..... :P

Tuesday, March 20, 2012

Problems with Maintenance Plans and File Deletes

Hi,
I'm hoping that someone can help me out. I am trying to add a
maintenance task to delete files older than say 2 days. When I create
the plan the tast runs but no files are deleted. If I view the TSQL
script created it reads:
EXECUTE master.dbo.xp_delete_file
0,N'G:\sql_back_full',N'bak',N'12/18/2005 16:25:00'
I'm in Australia where the 18th Dec is 18/12/05. Not sure if that is
part of the problem.
Searching for info on msdn and books online yields no info on
xp_delete_file. Searching the MS support forums, google groups etc I
can find lots of messages where users are asking for assistance on how
to use this or why it doesn't work yet no one has an answer.
Considering the amount of money we just laid down for this product I am
absolutely astounded how little testing has gone into this maintenance
plans area.
Any MVPs have any ideas, or any one else found a work around on this.
Anyone know anyone on the Development team who can shed some light on
what this, why it doesn't work out of the box, and what can be done to
fix it?
StewI would like to find out more information on this as well. I will try to dig
a little deeper (again) and see if I can get someone to document this
feature. I will let you know if I find anything. IN the meantime try
running this manually in the query editor and see if it works as expected:
EXECUTE master.dbo.xp_delete_file
0,N'G:\sql_back_full',N'bak',N'20051218 16:25:00'
--
Andrew J. Kelly SQL MVP
<windsurfing_stew@.yahoo.com.au> wrote in message
news:1135056599.840877.305720@.g14g2000cwa.googlegroups.com...
> Hi,
> I'm hoping that someone can help me out. I am trying to add a
> maintenance task to delete files older than say 2 days. When I create
> the plan the tast runs but no files are deleted. If I view the TSQL
> script created it reads:
> EXECUTE master.dbo.xp_delete_file
> 0,N'G:\sql_back_full',N'bak',N'12/18/2005 16:25:00'
> I'm in Australia where the 18th Dec is 18/12/05. Not sure if that is
> part of the problem.
> Searching for info on msdn and books online yields no info on
> xp_delete_file. Searching the MS support forums, google groups etc I
> can find lots of messages where users are asking for assistance on how
> to use this or why it doesn't work yet no one has an answer.
> Considering the amount of money we just laid down for this product I am
> absolutely astounded how little testing has gone into this maintenance
> plans area.
> Any MVPs have any ideas, or any one else found a work around on this.
> Anyone know anyone on the Development team who can shed some light on
> what this, why it doesn't work out of the box, and what can be done to
> fix it?
> Stew
>|||Hi Andrew,
Thanks for your suggestion. I have tried this and unfortunately it
wasn't successful.
If you could 'dig a little deeper' to help on this one I'd be
particularly appreciative.
Looking forward to your response.
Stewart|||Any movement on this. Same problem. This is ridiculous.
"windsurfing_stew@.yahoo.com.au" wrote:
> Hi,
> I'm hoping that someone can help me out. I am trying to add a
> maintenance task to delete files older than say 2 days. When I create
> the plan the tast runs but no files are deleted. If I view the TSQL
> script created it reads:
> EXECUTE master.dbo.xp_delete_file
> 0,N'G:\sql_back_full',N'bak',N'12/18/2005 16:25:00'
> I'm in Australia where the 18th Dec is 18/12/05. Not sure if that is
> part of the problem.
> Searching for info on msdn and books online yields no info on
> xp_delete_file. Searching the MS support forums, google groups etc I
> can find lots of messages where users are asking for assistance on how
> to use this or why it doesn't work yet no one has an answer.
> Considering the amount of money we just laid down for this product I am
> absolutely astounded how little testing has gone into this maintenance
> plans area.
> Any MVPs have any ideas, or any one else found a work around on this.
> Anyone know anyone on the Development team who can shed some light on
> what this, why it doesn't work out of the box, and what can be done to
> fix it?
> Stew
>|||Nothing yet. Unfortunately the timing was wrong due to the holidays. I
think a lot of the SQL Dev team members took a long awaited vacation over
Christmas & New Years. I will ping them again.
--
Andrew J. Kelly SQL MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:C86196B0-95AF-4EE2-B654-A2266D7FBE22@.microsoft.com...
> Any movement on this. Same problem. This is ridiculous.
> "windsurfing_stew@.yahoo.com.au" wrote:
>> Hi,
>> I'm hoping that someone can help me out. I am trying to add a
>> maintenance task to delete files older than say 2 days. When I create
>> the plan the tast runs but no files are deleted. If I view the TSQL
>> script created it reads:
>> EXECUTE master.dbo.xp_delete_file
>> 0,N'G:\sql_back_full',N'bak',N'12/18/2005 16:25:00'
>> I'm in Australia where the 18th Dec is 18/12/05. Not sure if that is
>> part of the problem.
>> Searching for info on msdn and books online yields no info on
>> xp_delete_file. Searching the MS support forums, google groups etc I
>> can find lots of messages where users are asking for assistance on how
>> to use this or why it doesn't work yet no one has an answer.
>> Considering the amount of money we just laid down for this product I am
>> absolutely astounded how little testing has gone into this maintenance
>> plans area.
>> Any MVPs have any ideas, or any one else found a work around on this.
>> Anyone know anyone on the Development team who can shed some light on
>> what this, why it doesn't work out of the box, and what can be done to
>> fix it?
>> Stew
>>|||As far as I can tell, the new "cleanup" task can remove old files in a
single directory, but will not recurse. It took me a while to figure out I
had to specify a file extension and NOT include the leading "." in order to
get it to delete old .trn files. I now have all backups in a single folder,
instead of creating a new folder for each database. I also have two cleanup
tasks created to get rid of old .bak and another for .trn files. It's
functional now, but not evident when setting up.
This is a kludge. I would much rather have separate directories for old
backup files. The cleanup tool needs an option to recurse through
subdirectories when deleting files.
I agree that the backup options are a step backwards for SQL 2005.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23OBaViJEGHA.532@.TK2MSFTNGP15.phx.gbl...
> Nothing yet. Unfortunately the timing was wrong due to the holidays. I
> think a lot of the SQL Dev team members took a long awaited vacation over
> Christmas & New Years. I will ping them again.
> --
> Andrew J. Kelly SQL MVP
>
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:C86196B0-95AF-4EE2-B654-A2266D7FBE22@.microsoft.com...
>> Any movement on this. Same problem. This is ridiculous.
>> "windsurfing_stew@.yahoo.com.au" wrote:
>> Hi,
>> I'm hoping that someone can help me out. I am trying to add a
>> maintenance task to delete files older than say 2 days. When I create
>> the plan the tast runs but no files are deleted. If I view the TSQL
>> script created it reads:
>> EXECUTE master.dbo.xp_delete_file
>> 0,N'G:\sql_back_full',N'bak',N'12/18/2005 16:25:00'
>> I'm in Australia where the 18th Dec is 18/12/05. Not sure if that is
>> part of the problem.
>> Searching for info on msdn and books online yields no info on
>> xp_delete_file. Searching the MS support forums, google groups etc I
>> can find lots of messages where users are asking for assistance on how
>> to use this or why it doesn't work yet no one has an answer.
>> Considering the amount of money we just laid down for this product I am
>> absolutely astounded how little testing has gone into this maintenance
>> plans area.
>> Any MVPs have any ideas, or any one else found a work around on this.
>> Anyone know anyone on the Development team who can shed some light on
>> what this, why it doesn't work out of the box, and what can be done to
>> fix it?
>> Stew
>>
>

Problems with Maintenance Plans and File Deletes

Hi,
I'm hoping that someone can help me out. I am trying to add a
maintenance task to delete files older than say 2 days. When I create
the plan the tast runs but no files are deleted. If I view the TSQL
script created it reads:
EXECUTE master.dbo.xp_delete_file
0,N'G:\sql_back_full',N'bak',N'12/18/2005 16:25:00'
I'm in Australia where the 18th Dec is 18/12/05. Not sure if that is
part of the problem.
Searching for info on msdn and books online yields no info on
xp_delete_file. Searching the MS support forums, google groups etc I
can find lots of messages where users are asking for assistance on how
to use this or why it doesn't work yet no one has an answer.
Considering the amount of money we just laid down for this product I am
absolutely astounded how little testing has gone into this maintenance
plans area.
Any MVPs have any ideas, or any one else found a work around on this.
Anyone know anyone on the Development team who can shed some light on
what this, why it doesn't work out of the box, and what can be done to
fix it?
StewI would like to find out more information on this as well. I will try to dig
a little deeper (again) and see if I can get someone to document this
feature. I will let you know if I find anything. IN the meantime try
running this manually in the query editor and see if it works as expected:
EXECUTE master.dbo.xp_delete_file
0,N'G:\sql_back_full',N'bak',N'20051218 16:25:00'
Andrew J. Kelly SQL MVP
<windsurfing_stew@.yahoo.com.au> wrote in message
news:1135056599.840877.305720@.g14g2000cwa.googlegroups.com...
> Hi,
> I'm hoping that someone can help me out. I am trying to add a
> maintenance task to delete files older than say 2 days. When I create
> the plan the tast runs but no files are deleted. If I view the TSQL
> script created it reads:
> EXECUTE master.dbo.xp_delete_file
> 0,N'G:\sql_back_full',N'bak',N'12/18/2005 16:25:00'
> I'm in Australia where the 18th Dec is 18/12/05. Not sure if that is
> part of the problem.
> Searching for info on msdn and books online yields no info on
> xp_delete_file. Searching the MS support forums, google groups etc I
> can find lots of messages where users are asking for assistance on how
> to use this or why it doesn't work yet no one has an answer.
> Considering the amount of money we just laid down for this product I am
> absolutely astounded how little testing has gone into this maintenance
> plans area.
> Any MVPs have any ideas, or any one else found a work around on this.
> Anyone know anyone on the Development team who can shed some light on
> what this, why it doesn't work out of the box, and what can be done to
> fix it?
> Stew
>|||Hi Andrew,
Thanks for your suggestion. I have tried this and unfortunately it
wasn't successful.
If you could 'dig a little deeper' to help on this one I'd be
particularly appreciative.
Looking forward to your response.
Stewart|||Any movement on this. Same problem. This is ridiculous.
"windsurfing_stew@.yahoo.com.au" wrote:

> Hi,
> I'm hoping that someone can help me out. I am trying to add a
> maintenance task to delete files older than say 2 days. When I create
> the plan the tast runs but no files are deleted. If I view the TSQL
> script created it reads:
> EXECUTE master.dbo.xp_delete_file
> 0,N'G:\sql_back_full',N'bak',N'12/18/2005 16:25:00'
> I'm in Australia where the 18th Dec is 18/12/05. Not sure if that is
> part of the problem.
> Searching for info on msdn and books online yields no info on
> xp_delete_file. Searching the MS support forums, google groups etc I
> can find lots of messages where users are asking for assistance on how
> to use this or why it doesn't work yet no one has an answer.
> Considering the amount of money we just laid down for this product I am
> absolutely astounded how little testing has gone into this maintenance
> plans area.
> Any MVPs have any ideas, or any one else found a work around on this.
> Anyone know anyone on the Development team who can shed some light on
> what this, why it doesn't work out of the box, and what can be done to
> fix it?
> Stew
>|||Nothing yet. Unfortunately the timing was wrong due to the holidays. I
think a lot of the SQL Dev team members took a long awaited vacation over
Christmas & New Years. I will ping them again.
Andrew J. Kelly SQL MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:C86196B0-95AF-4EE2-B654-A2266D7FBE22@.microsoft.com...[vbcol=seagreen]
> Any movement on this. Same problem. This is ridiculous.
> "windsurfing_stew@.yahoo.com.au" wrote:
>|||As far as I can tell, the new "cleanup" task can remove old files in a
single directory, but will not recurse. It took me a while to figure out I
had to specify a file extension and NOT include the leading "." in order to
get it to delete old .trn files. I now have all backups in a single folder,
instead of creating a new folder for each database. I also have two cleanup
tasks created to get rid of old .bak and another for .trn files. It's
functional now, but not evident when setting up.
This is a kludge. I would much rather have separate directories for old
backup files. The cleanup tool needs an option to recurse through
subdirectories when deleting files.
I agree that the backup options are a step backwards for SQL 2005.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23OBaViJEGHA.532@.TK2MSFTNGP15.phx.gbl...
> Nothing yet. Unfortunately the timing was wrong due to the holidays. I
> think a lot of the SQL Dev team members took a long awaited vacation over
> Christmas & New Years. I will ping them again.
> --
> Andrew J. Kelly SQL MVP
>
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:C86196B0-95AF-4EE2-B654-A2266D7FBE22@.microsoft.com...
>

Problems with Maintenance Plans and File Deletes

Hi,
I'm hoping that someone can help me out. I am trying to add a
maintenance task to delete files older than say 2 days. When I create
the plan the tast runs but no files are deleted. If I view the TSQL
script created it reads:
EXECUTE master.dbo.xp_delete_file
0,N'G:\sql_back_full',N'bak',N'12/18/2005 16:25:00'
I'm in Australia where the 18th Dec is 18/12/05. Not sure if that is
part of the problem.
Searching for info on msdn and books online yields no info on
xp_delete_file. Searching the MS support forums, google groups etc I
can find lots of messages where users are asking for assistance on how
to use this or why it doesn't work yet no one has an answer.
Considering the amount of money we just laid down for this product I am
absolutely astounded how little testing has gone into this maintenance
plans area.
Any MVPs have any ideas, or any one else found a work around on this.
Anyone know anyone on the Development team who can shed some light on
what this, why it doesn't work out of the box, and what can be done to
fix it?
Stew
I would like to find out more information on this as well. I will try to dig
a little deeper (again) and see if I can get someone to document this
feature. I will let you know if I find anything. IN the meantime try
running this manually in the query editor and see if it works as expected:
EXECUTE master.dbo.xp_delete_file
0,N'G:\sql_back_full',N'bak',N'20051218 16:25:00'
Andrew J. Kelly SQL MVP
<windsurfing_stew@.yahoo.com.au> wrote in message
news:1135056599.840877.305720@.g14g2000cwa.googlegr oups.com...
> Hi,
> I'm hoping that someone can help me out. I am trying to add a
> maintenance task to delete files older than say 2 days. When I create
> the plan the tast runs but no files are deleted. If I view the TSQL
> script created it reads:
> EXECUTE master.dbo.xp_delete_file
> 0,N'G:\sql_back_full',N'bak',N'12/18/2005 16:25:00'
> I'm in Australia where the 18th Dec is 18/12/05. Not sure if that is
> part of the problem.
> Searching for info on msdn and books online yields no info on
> xp_delete_file. Searching the MS support forums, google groups etc I
> can find lots of messages where users are asking for assistance on how
> to use this or why it doesn't work yet no one has an answer.
> Considering the amount of money we just laid down for this product I am
> absolutely astounded how little testing has gone into this maintenance
> plans area.
> Any MVPs have any ideas, or any one else found a work around on this.
> Anyone know anyone on the Development team who can shed some light on
> what this, why it doesn't work out of the box, and what can be done to
> fix it?
> Stew
>
|||Hi Andrew,
Thanks for your suggestion. I have tried this and unfortunately it
wasn't successful.
If you could 'dig a little deeper' to help on this one I'd be
particularly appreciative.
Looking forward to your response.
Stewart
|||Any movement on this. Same problem. This is ridiculous.
"windsurfing_stew@.yahoo.com.au" wrote:

> Hi,
> I'm hoping that someone can help me out. I am trying to add a
> maintenance task to delete files older than say 2 days. When I create
> the plan the tast runs but no files are deleted. If I view the TSQL
> script created it reads:
> EXECUTE master.dbo.xp_delete_file
> 0,N'G:\sql_back_full',N'bak',N'12/18/2005 16:25:00'
> I'm in Australia where the 18th Dec is 18/12/05. Not sure if that is
> part of the problem.
> Searching for info on msdn and books online yields no info on
> xp_delete_file. Searching the MS support forums, google groups etc I
> can find lots of messages where users are asking for assistance on how
> to use this or why it doesn't work yet no one has an answer.
> Considering the amount of money we just laid down for this product I am
> absolutely astounded how little testing has gone into this maintenance
> plans area.
> Any MVPs have any ideas, or any one else found a work around on this.
> Anyone know anyone on the Development team who can shed some light on
> what this, why it doesn't work out of the box, and what can be done to
> fix it?
> Stew
>
|||Nothing yet. Unfortunately the timing was wrong due to the holidays. I
think a lot of the SQL Dev team members took a long awaited vacation over
Christmas & New Years. I will ping them again.
Andrew J. Kelly SQL MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:C86196B0-95AF-4EE2-B654-A2266D7FBE22@.microsoft.com...[vbcol=seagreen]
> Any movement on this. Same problem. This is ridiculous.
> "windsurfing_stew@.yahoo.com.au" wrote:
|||As far as I can tell, the new "cleanup" task can remove old files in a
single directory, but will not recurse. It took me a while to figure out I
had to specify a file extension and NOT include the leading "." in order to
get it to delete old .trn files. I now have all backups in a single folder,
instead of creating a new folder for each database. I also have two cleanup
tasks created to get rid of old .bak and another for .trn files. It's
functional now, but not evident when setting up.
This is a kludge. I would much rather have separate directories for old
backup files. The cleanup tool needs an option to recurse through
subdirectories when deleting files.
I agree that the backup options are a step backwards for SQL 2005.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23OBaViJEGHA.532@.TK2MSFTNGP15.phx.gbl...
> Nothing yet. Unfortunately the timing was wrong due to the holidays. I
> think a lot of the SQL Dev team members took a long awaited vacation over
> Christmas & New Years. I will ping them again.
> --
> Andrew J. Kelly SQL MVP
>
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:C86196B0-95AF-4EE2-B654-A2266D7FBE22@.microsoft.com...
>

Friday, March 9, 2012

Problems with Delete statement

Hi,
I have the table: R1(A, B, C)
Primary Key (A, B)
Foreign Key (A)
I'm trying to delete a row using the query: delete from R1 where A = 123;
However, Oracle freezes on me. Please help. Thanks.A foreign key must reference the entire primary key or unique set of columns, thus foreign key (A) in your below table must indicate that (A) refers to a column in another table that i'll call R.

R(A) primary key (A)
R1 (A, B, C) primary key (A, B)
foreign key (A) references R(A)

By default deleting the value of A will have no effect, as A in this case is a child. An error would arise if the primary key of R1 was reference as a foreign key elsewhere as A is part of the parent key.

However given the above simple relations,

delete from R1 where a = x produces no error.

Thus, the problem is not constraint related with default constraint options.

Saturday, February 25, 2012

Problems with an XML column....?

I have a table with an XML column that stores report definitions. For development purposes I have to delete and reinsert these reports on a fairly regular basis (300+ reports). When I do so, I notice the following: The size of the data file increases by roughly 30MB. The file I am loading the XML from (thru an SSIS package) is only 4MB. If I shrink the database, I can get rid of 20MB, but I still net a 10MB increase. The package gets run frequently. Right now my database is at about 250MB when it should be about 40-50MB. I am trying to avoid having to drop all constraints, truncating the table, and reapplying the constraints. If I disable the step in my package that deals with the XML column, I see no change in the database. It appears to be the DELETE that causes the most damage, as I have tested the package on purely an insert-basis and on a delete-insert-basis. Has anyone seen behavior like this?Since no one has responded in over a month, I am going to close this one out myself. I ended up dropping all of the constraints, truncating, and reapplying the constraints. I still have some growth in my data files but it has slowed considerably. I read that empty pages could be left behind by a DELETE statement, but I have never since file growth like this. Perhaps, SP1 addresses this. I don't know.