Friday, March 30, 2012

Problem with Maintenance Plan to delete files older than 2 days

Hi,
Hoping that someone in the group can shed some light on an issue I'm
having. We are trying to all delete backup files older than say two
days. I've tried a few methods, none of which have worked so far.
If I create a new Maintenance Plan and add a Maintenance Cleanup Task
and set it to delete all files in a given directory of a given
extension older than two days the plan executes successfully (including
the log) however no files are deleted.
If I look at the script produced by clicking the Script button I get:
EXECUTE master.dbo.xp_delete_file
0,N'G:\sql_back_full',N'bak',N'12/18/2005 16:55:21'
Now I'm in Australia and we express 18 Dec as 18/12/2005 rather than
12/18/2005. Not sure if that has anything to do with it.
I have searched google, google groups, msdn and microsoft forums. I
have turned up a handful of references to xp_delete_file. All of these
are to do with users asking how to use it or asking why it doesn't
work. No one has offered any answers.
Given the money we have layed down on this product I'm astounded that
this area hasn't been better tested or supported.
Any MVPs got any ideas, has anyone found a work around or does anyone
know anyone on the Development team who can shed some light on this?
Stew
Create a batch file that deletes everything in a directory (or a vbs
script or whatever)
Use xp_cmdshell to execute the batch file like so
declare @.retcode int
execute @.retcode = master.dbo.xp_cmdshell
"\\machinename\folder\subfolder\test.bat"
select @.retcode retcode
Your batch can take arguments or anything too, whatever works w/ your
installation
execute @.retcode = master.dbo.xp_cmdshell
"\\machinename\folder\subfolder\test.bat " + convert(varchar,
getdate(), 112)
Just remember the execute is relative to the server so permissions all
that need to be set right.
Otherwise there are I'm sure 3rd party freeware utilities (or cheap pay
products) that monitor/delete/move/etc files in specified directories.
I've worked places where these were used.

Problem with Maintenance Plan to delete files older than 2 days

Hi,
Hoping that someone in the group can shed some light on an issue I'm
having. We are trying to all delete backup files older than say two
days. I've tried a few methods, none of which have worked so far.
If I create a new Maintenance Plan and add a Maintenance Cleanup Task
and set it to delete all files in a given directory of a given
extension older than two days the plan executes successfully (including
the log) however no files are deleted.
If I look at the script produced by clicking the Script button I get:
EXECUTE master.dbo.xp_delete_file
0,N'G:\sql_back_full',N'bak',N'12/18/2005 16:55:21'
Now I'm in Australia and we express 18 Dec as 18/12/2005 rather than
12/18/2005. Not sure if that has anything to do with it.
I have searched google, google groups, msdn and microsoft forums. I
have turned up a handful of references to xp_delete_file. All of these
are to do with users asking how to use it or asking why it doesn't
work. No one has offered any answers.
Given the money we have layed down on this product I'm astounded that
this area hasn't been better tested or supported.
Any MVPs got any ideas, has anyone found a work around or does anyone
know anyone on the Development team who can shed some light on this?
StewCreate a batch file that deletes everything in a directory (or a vbs
script or whatever)
Use xp_cmdshell to execute the batch file like so
declare @.retcode int
execute @.retcode = master.dbo.xp_cmdshell
"\\machinename\folder\subfolder\test.bat"
select @.retcode retcode
Your batch can take arguments or anything too, whatever works w/ your
installation
execute @.retcode = master.dbo.xp_cmdshell
"\\machinename\folder\subfolder\test.bat " + convert(varchar,
getdate(), 112)
Just remember the execute is relative to the server so permissions all
that need to be set right.
Otherwise there are I'm sure 3rd party freeware utilities (or cheap pay
products) that monitor/delete/move/etc files in specified directories.
I've worked places where these were used.sql

Problem with Maintenance Plan to delete files older than 2 days

Hi,
Hoping that someone in the group can shed some light on an issue I'm
having. We are trying to all delete backup files older than say two
days. I've tried a few methods, none of which have worked so far.
If I create a new Maintenance Plan and add a Maintenance Cleanup Task
and set it to delete all files in a given directory of a given
extension older than two days the plan executes successfully (including
the log) however no files are deleted.
If I look at the script produced by clicking the Script button I get:
EXECUTE master.dbo.xp_delete_file
0,N'G:\sql_back_full',N'bak',N'12/18/2005 16:55:21'
Now I'm in Australia and we express 18 Dec as 18/12/2005 rather than
12/18/2005. Not sure if that has anything to do with it.
I have searched google, google groups, msdn and microsoft forums. I
have turned up a handful of references to xp_delete_file. All of these
are to do with users asking how to use it or asking why it doesn't
work. No one has offered any answers.
Given the money we have layed down on this product I'm astounded that
this area hasn't been better tested or supported.
Any MVPs got any ideas, has anyone found a work around or does anyone
know anyone on the Development team who can shed some light on this?
StewCreate a batch file that deletes everything in a directory (or a vbs
script or whatever)
Use xp_cmdshell to execute the batch file like so
declare @.retcode int
execute @.retcode = master.dbo.xp_cmdshell
"\\machinename\folder\subfolder\test.bat"
select @.retcode retcode
Your batch can take arguments or anything too, whatever works w/ your
installation
execute @.retcode = master.dbo.xp_cmdshell
"\\machinename\folder\subfolder\test.bat " + convert(varchar,
getdate(), 112)
Just remember the execute is relative to the server so permissions all
that need to be set right.
Otherwise there are I'm sure 3rd party freeware utilities (or cheap pay
products) that monitor/delete/move/etc files in specified directories.
I've worked places where these were used.

Problem with mailing labels and line feed

I am trying to print mailing labels and suppress optional address lines to eliminate white space while maintaining label alignment. This is what I am trying but it does not work.

I create a function that determines the length of a field (i.e. street) and increments a counter if the length is zero. It also takes a second parameter that detremines whether to reset the counter. I then use the function in the visibility each row of the label table. Example iif(Code.LineLen(Fields!Street.Value)=0,True,False). I place this on the visibility of each row except the last as that is City,State,Zip and is required. On this last row the expession I use is

=Fields!city.Value & ", " & Fields!state.Value & " " & Fields!zip.Value & iif(Code.LineCount<5,StrDup(5-Code.LineCount,vbCRLF),""). What this is intended to do is print a carraige return and line feed for every row that did not print. Instead no line feeds occur. I have verified that the Code.LineCount is indeed calculating correctly (I printed the value). I've removed the conditional to make sure it has no issues but again nothing. For clarification the code block I am using is this.

Public LineCount as Integer=0

Function LineLen(byval LineIn as string,byval IsFirst as boolean) as Integer
if IsFirst=True then

LineCount=1
end if
If len(LineIn)>0 then
LineCount=LineCount+1
end if
LineLen=Len(LineIn)
end Function

So if anyone either knows why the line feeds do not occur or a better way to handle this please let me know. Thanks.

Well I partially figured out this issue. Apparently the StrDup function in .Net does not like carraige returns and line feeds. I wrote my own string dup function and now I get the line feeds.

I would still be interesting in a better way of doing this if anyone knows.

Problem with machine that has only reporting services2005 installe

Hello all,
I am new to reporting services.
We have one installation of reporting services with SQL server 2005 and
Analysis services 2005 on one machine, and we have another server with only
Reporting services 2005 installed (a proxy server). I dont have a problem
with the first machine, but the second machine keeps showing this error when
i try to enter the default 'Report service' page:
http://localhost/reports
The XML page cannot be displayed
Cannot view XML input using XSL style sheet. Please correct the error and
then click the Refresh button, or try again later.
----
A name was started with an invalid character. Error processing resource
'http://ar2krepprox/Reports/'. Line 1, Position 2
<%@. Page language="c#" Codebehind="Home.aspx.cs" AutoEventWireup="false"
Inherits="Microsoft.ReportingServices.UI.HomePag...
I am able to deploy reports, but when run them i get this error:
' Your browser does not support scripts or has been configured not to allow
scripts. Click here to view this report without scripts. '
How do i get rid of these errors?
Is there any way to create a data driven subscription through the SQL Server
management studio?
I encountered no installation errors!
Thanks in advance
IshanI found the solution!!
a shout note by luna.dave@.gmail.com was v helpful
"Ishan Bhalla" wrote:
> Hello all,
> I am new to reporting services.
> We have one installation of reporting services with SQL server 2005 and
> Analysis services 2005 on one machine, and we have another server with only
> Reporting services 2005 installed (a proxy server). I dont have a problem
> with the first machine, but the second machine keeps showing this error when
> i try to enter the default 'Report service' page:
> http://localhost/reports
> The XML page cannot be displayed
> Cannot view XML input using XSL style sheet. Please correct the error and
> then click the Refresh button, or try again later.
> ----
> A name was started with an invalid character. Error processing resource
> 'http://ar2krepprox/Reports/'. Line 1, Position 2
> <%@. Page language="c#" Codebehind="Home.aspx.cs" AutoEventWireup="false"
> Inherits="Microsoft.ReportingServices.UI.HomePag...
> I am able to deploy reports, but when run them i get this error:
> ' Your browser does not support scripts or has been configured not to allow
> scripts. Click here to view this report without scripts. '
> How do i get rid of these errors?
> Is there any way to create a data driven subscription through the SQL Server
> management studio?
> I encountered no installation errors!
> Thanks in advance
> Ishan
>|||Within IIS Manager on your RS server check you have Scripts Enabled on the
"Home Directory" tab.
Paul Walsh
"Ishan Bhalla" wrote:
> Hello all,
> I am new to reporting services.
> We have one installation of reporting services with SQL server 2005 and
> Analysis services 2005 on one machine, and we have another server with only
> Reporting services 2005 installed (a proxy server). I dont have a problem
> with the first machine, but the second machine keeps showing this error when
> i try to enter the default 'Report service' page:
> http://localhost/reports
> The XML page cannot be displayed
> Cannot view XML input using XSL style sheet. Please correct the error and
> then click the Refresh button, or try again later.
> ----
> A name was started with an invalid character. Error processing resource
> 'http://ar2krepprox/Reports/'. Line 1, Position 2
> <%@. Page language="c#" Codebehind="Home.aspx.cs" AutoEventWireup="false"
> Inherits="Microsoft.ReportingServices.UI.HomePag...
> I am able to deploy reports, but when run them i get this error:
> ' Your browser does not support scripts or has been configured not to allow
> scripts. Click here to view this report without scripts. '
> How do i get rid of these errors?
> Is there any way to create a data driven subscription through the SQL Server
> management studio?
> I encountered no installation errors!
> Thanks in advance
> Ishan
>

problem with lookup ,is this a bug?

All,

I’m having problem with lookup transformation using Advanced TAB, it seems TOP 1 in a query doesn’t work and lookup cache some values no matter what. Below are the details if someone likes to try it out.

-This is the source table

CREATE TABLE [dbo].[DEPT_temp2](

[DEPTNO] [int] NOT NULL,

[NUM_Id] [int] NULL,

[date_id] [int] NULL

) ON [PRIMARY]

INSERT INTO DEPT_temp2 ([DEPTNO],[NUM_Id],[date_id]) VALUES (1,111,11111);

INSERT INTO DEPT_temp2 ([DEPTNO],[NUM_Id],[date_id]) VALUES (1,222,22222);

INSERT INTO DEPT_temp2 ([DEPTNO],[NUM_Id],[date_id]) VALUES (1,333,33333);

INSERT INTO DEPT_temp2 ([DEPTNO],[NUM_Id],[date_id]) VALUES (1,444,44444);

INSERT INTO DEPT_temp2 ([DEPTNO],[NUM_Id],[date_id]) VALUES (1,555,55555);

--This is the destination table

CREATE TABLE [dbo].[DEPT_temp2_end](

[DEPTNO] [int] NOT NULL,

[NUM_Id] [int] NULL,

[NEW] [int] NULL,

[date_id] [int] NULL

) ON [PRIMARY]

in the LOOKUP transformation

OLE DB source-àSQL command:

select * from DEPT_temp2

In LOOKUP transformation à Reference Table tab à Use a result ofa sql query

select deptno, num_id as new

from DEPT_temp2

à Columns TAB

Linked the deptno, num_id from the input to lookup column deptno, New

--à Advanced TAB --à Checked Enable memory restriction -à Modify the SQL statement

selecttop 1 *from

(select deptno, num_id as new

from DEPT_temp2) as refTable

where [refTable].[deptno] = ? and [refTable].[new] > ?

In the property of the lookup, cache type is none( I tried partial also) and enable cache is unchecked. I got the same result

Here is the result from the lookup tran

Deptnonum_id newdate_id

111122211111

122222222222

133344433333

144444444444

1555NULL55555

But expected result should be

Deptnonum_id newdate_id

111122211111

122233322222

133344433333

144455544444

1555NULL55555

What I’m missing here? Is this a bug?

Hi,

I know that when you are using memory restrictions some lookups still have caching enabled in the properties tab. Apart from the SQL used I cannot see anything wrong.

Normally I do not use a select top 1 because as far as I know, the first result found in the lookup is used.

select deptno, num_id as new from DEPT_temp2 where [deptno] = ? and [num_id] > ? order by num_id

Naturally the hash table created by the lookup when using partial or full caching disabled the possibility of using greater or smaller than. I have once solved an issue like this one using a view that handled the complex greater than statement. I simply could use the id as lookup to the view and full caching.

Problem With LookUp

Iam trying to make a look up of 'ColumnA' with 'columnB' of another table .But 'columnB' has some duplicate vlaues..so lookup transmition is giving an error.

Is there any solution for this

Thanks
Niru
Findout out the solution...using Sqlquery as OLEDB Source instead of Table.This solved the thing.

Thanks
Niru
sql