Filter & *

Hi I am wandering if it is possible to achieve the following:

I am using Reporting Services and Reporting services I got a report with a text parameter.

I would like the user to introduce AA and find the product AA and when he introduces AA* it finds AA, AAD, AAC...

I am doing the following:

Filter([DIM Product].[Product].ALLMEMBERS,

,IIF(INSTR(@.Product, "*") > 0

,[DIM Product].[Product].currentmember.name = Replace(@.Product, "%", "")

,INSTR([DIM Product].[Product].currentmember.name, @.Product)

)

)

The problem ist that the function Replace does not work!

Does someone know how to do it?

I don't think the problem is with the replace, it looks like you have the logic back-to-front, something like the following should get you closer.

Code Snippet

Filter([DIM Product].[Product].ALLMEMBERS,

,IIF(INSTR(@.Product, "*") > 0

,INSTR([DIM Product].[Product].currentmember.name, Replace(@.Product, "*", "")

,[DIM Product].[Product].currentmember.name = @.Product

)

)

However there is an issue that you may or may not be concerned with - If someone types in "A*B" the expression will strip out the "*" and return anything containing "AB".

Another alternative, if you want more sophisticated behaviour might be to look at using a stored procedure. There is a string match example which I wrote in the Analysis Services Stored Procedure project at www.codeplex.com/ASStoredProcedures . I have one in there that reproduces the equivalent of the t-sql LIKE operator.

FileShareProvider delivery extension credential encoding

I receive the following error when I try to provide a username and password
to the FileShareProvider delivery extension through the data-driven
subscription query:
ReportingServicesService!dbpolling!5d8!07/14/2004-17:20:23::
NotificationPolling processing item 951ca046-a4a7-4657-be85-04512f3e8787
ReportingServicesService!crypto!5d8!07/14/2004-17:20:23:: e ERROR:
DBUnProtectData failed: System.FormatException: Invalid character in a
Base-64 string.
at System.Convert.FromBase64String(String s)
at
Microsoft.ReportingServices.Diagnostics.DataProtection.DBUnprotectData(Strin
g data)
ReportingServicesService!crypto!5d8!07/14/2004-17:20:23:: i INFO: Current
user: secret
ReportingServicesService!notification!5d8!07/14/2004-17:20:23:: e ERROR:
Error occured processing notification. Invalid character in a Base-64
string.
ReportingServicesService!library!5d8!07/14/2004-17:20:23:: Data Driven
Notification for activation id c0eac265-fe46-4de5-9b13-4745be82bd25 was
saved.
ReportingServicesService!library!5d8!07/14/2004-17:20:23:: Status: Invalid
character in a Base-64 string.
ReportingServicesService!notification!5d8!07/14/2004-17:20:23:: Notification
951ca046-a4a7-4657-be85-04512f3e8787 completed. Success: True, Status:
Invalid character in a Base-64 string., DeliveryExtension: Report Server
FileShare, Report: Invoice, Attempt 0
ReportingServicesService!dbpolling!5d8!07/14/2004-17:20:24::
NotificationPolling finished processing item
951ca046-a4a7-4657-be85-04512f3e8787
It seems that the extension is trying to decode the username and password
from the query, but can't since they aren't encoded. If I hard-code the
username and password values in the subscription itself, everything works
fine.
I tried to use
Microsoft.ReportingServices.Diagnostics.DataProtection.DBProtectData to
encode the username and password before storing them in the database, but am
getting the following error:
System.Security.SecurityException: Request for the permission of type
System.Security.Permissions.StrongNameIdentityPermission, mscorlib,
Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
failed..
What do I need to do to make the extension happy?
JayIt's a know issue, the user name and password are not supported by the file
share delivery extension, can you make them not data driven - i.e. constant?
--
Tudor Trufinescu
Dev Lead
Sql Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jay Hackney" <jayh@.nospam.intellinet.com> wrote in message
news:#wIsGe2aEHA.3804@.TK2MSFTNGP10.phx.gbl...
> I receive the following error when I try to provide a username and
password
> to the FileShareProvider delivery extension through the data-driven
> subscription query:
> ReportingServicesService!dbpolling!5d8!07/14/2004-17:20:23::
> NotificationPolling processing item 951ca046-a4a7-4657-be85-04512f3e8787
> ReportingServicesService!crypto!5d8!07/14/2004-17:20:23:: e ERROR:
> DBUnProtectData failed: System.FormatException: Invalid character in a
> Base-64 string.
> at System.Convert.FromBase64String(String s)
> at
>
Microsoft.ReportingServices.Diagnostics.DataProtection.DBUnprotectData(Strin
> g data)
> ReportingServicesService!crypto!5d8!07/14/2004-17:20:23:: i INFO: Current
> user: secret
> ReportingServicesService!notification!5d8!07/14/2004-17:20:23:: e ERROR:
> Error occured processing notification. Invalid character in a Base-64
> string.
> ReportingServicesService!library!5d8!07/14/2004-17:20:23:: Data Driven
> Notification for activation id c0eac265-fe46-4de5-9b13-4745be82bd25 was
> saved.
> ReportingServicesService!library!5d8!07/14/2004-17:20:23:: Status: Invalid
> character in a Base-64 string.
> ReportingServicesService!notification!5d8!07/14/2004-17:20:23::
Notification
> 951ca046-a4a7-4657-be85-04512f3e8787 completed. Success: True, Status:
> Invalid character in a Base-64 string., DeliveryExtension: Report Server
> FileShare, Report: Invoice, Attempt 0
> ReportingServicesService!dbpolling!5d8!07/14/2004-17:20:24::
> NotificationPolling finished processing item
> 951ca046-a4a7-4657-be85-04512f3e8787
> It seems that the extension is trying to decode the username and password
> from the query, but can't since they aren't encoded. If I hard-code the
> username and password values in the subscription itself, everything works
> fine.
> I tried to use
> Microsoft.ReportingServices.Diagnostics.DataProtection.DBProtectData to
> encode the username and password before storing them in the database, but
am
> getting the following error:
> System.Security.SecurityException: Request for the permission of type
> System.Security.Permissions.StrongNameIdentityPermission, mscorlib,
> Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
> failed..
> What do I need to do to make the extension happy?
> Jay
>

fileshare subscription erro

My report subscription got the following error. Before the subscription was
run, I changed the Reporting Service's Windows Service logon account from
local system account to a local user with adm rights.
I'm using developer editions for sql server and reporting service, running
everything from one single development machine of XP Pro.
I'd appreciate any help.
jf
************ log info *********************************
ReportingServicesService!crypto!1600!01/11/2005-17:27:03:: e ERROR:
DBUnProtectData failed: System.Runtime.InteropServices.COMException
(0x80090005): Bad Data.
at System.Runtime.InteropServices.Marshal.ThrowExceptionForHR(Int32
errorCode, IntPtr errorInfo)
at RSManagedCrypto.RSCrypto.DecryptData(Byte[] pCipherText)
at
Microsoft.ReportingServices.Diagnostics.DataProtection.DBUnprotectData(String
data)
ReportingServicesService!crypto!1600!01/11/2005-17:27:03:: i INFO: Current
user: jf\john
ReportingServicesService!notification!1600!01/11/2005-17:27:03:: e ERROR:
Error occured processing notification. Bad Data.
ReportingServicesService!notification!1600!01/11/2005-17:27:03::
Notification 882b52f3-40de-486e-a7ef-4e72e6efd6ea completed. Success: True,
Status: Bad Data., DeliveryExtension: Report Server FileShare, Report:
NWCustomers, Attempt 0
ReportingServicesService!dbpolling!1600!01/11/2005-17:27:03::
NotificationPolling finished processing item
882b52f3-40de-486e-a7ef-4e72e6efd6ea
***************** end of log info ***************************Hi, you can see the documentation (BOL) of reporting services about change
the account using rsconfig.exe
The problem is that the new user would be changed in the RSCONFIG file, but
trougth rsconfig.exe not over file directly
this is the command
RSConfig -c -s Server name -d Reporting Services database name -a
Sql|Windows -u User name -p User password
Best Regards
John Bocachica
Colombia
"John Fant" <JohnFant@.discussions.microsoft.com> wrote in message
news:3CE5CAEF-7553-457A-B072-70BA04EF84B2@.microsoft.com...
> My report subscription got the following error. Before the subscription
> was
> run, I changed the Reporting Service's Windows Service logon account from
> local system account to a local user with adm rights.
> I'm using developer editions for sql server and reporting service, running
> everything from one single development machine of XP Pro.
> I'd appreciate any help.
> jf
> ************ log info *********************************
> ReportingServicesService!crypto!1600!01/11/2005-17:27:03:: e ERROR:
> DBUnProtectData failed: System.Runtime.InteropServices.COMException
> (0x80090005): Bad Data.
> at System.Runtime.InteropServices.Marshal.ThrowExceptionForHR(Int32
> errorCode, IntPtr errorInfo)
> at RSManagedCrypto.RSCrypto.DecryptData(Byte[] pCipherText)
> at
> Microsoft.ReportingServices.Diagnostics.DataProtection.DBUnprotectData(String
> data)
> ReportingServicesService!crypto!1600!01/11/2005-17:27:03:: i INFO: Current
> user: jf\john
> ReportingServicesService!notification!1600!01/11/2005-17:27:03:: e ERROR:
> Error occured processing notification. Bad Data.
> ReportingServicesService!notification!1600!01/11/2005-17:27:03::
> Notification 882b52f3-40de-486e-a7ef-4e72e6efd6ea completed. Success:
> True,
> Status: Bad Data., DeliveryExtension: Report Server FileShare, Report:
> NWCustomers, Attempt 0
> ReportingServicesService!dbpolling!1600!01/11/2005-17:27:03::
> NotificationPolling finished processing item
> 882b52f3-40de-486e-a7ef-4e72e6efd6ea
> ***************** end of log info ***************************

FileIOPermission

I got my custom assembly working fine until I add writing to a log
file. Then I get a FileIOPermission error.
I have the following code in my policy file:
<PermissionSet
class="NamedPermissionSet"
version="1"
Name="ReportHelperFilePermissionSet"
Description="A special permission set that grants read access to my
currency rates file.">
<IPermission
class="FileIOPermission"
version="1"
All="C:\ReportHelper.log"/>
<IPermission
class="SecurityPermission"
version="1"
Flags="Execution, Assertion"/>
</PermissionSet>
<CodeGroup class="UnionCodeGroup"
version="1"
PermissionSetName="ReportHelperFilePermissionSet"
Name="MyNewCodeGroup"
Description="A special code group for my custom assembly.">
<IMembershipCondition
class="UrlMembershipCondition"
version="1"
Url="D:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\bin\ReportHelper.dll"/>
</CodeGroup>
<CodeGroup
class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust"
Name="AWCLibrary">
<IMembershipCondition
class="UrlMembershipCondition"
version="1"
Url="D:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\bin\ReportHelper.dll"/>
</CodeGroup>
I write to my log file as follows:
public static void WriteLogFile(String msg)
{
FileIOPermission perm1 = new
FileIOPermission(FileIOPermissionAccess.Write, @."C:\ReportHelper.log");
perm1.Assert();
//try
//{
FileStream fs = new FileStream(@."C:\ReportHelper.log",
FileMode.OpenOrCreate, FileAccess.ReadWrite);
StreamWriter w = new StreamWriter(fs);
w.BaseStream.Seek(0, SeekOrigin.End);
w.Write("{0} {1} ", DateTime.Now.ToLongTimeString(),
DateTime.Now.ToLongDateString());
w.Write(msg + "\r\n");
w.Flush();
w.Close();
}
What am I missing here? Any help would be appreciated.
Thanks!Check this
http://www.c-sharpcorner.com/Code/2005/June/CustomAssemblyinRS.asp
HTH
If still you face the issue, let me know (bkkrishnan [at] hotmail [dot] com)
Thanks
Balaji
Siwy wrote:
>I got my custom assembly working fine until I add writing to a log
>file. Then I get a FileIOPermission error.
>I have the following code in my policy file:
><PermissionSet
> class="NamedPermissionSet"
> version="1"
> Name="ReportHelperFilePermissionSet"
> Description="A special permission set that grants read access to my
>currency rates file.">
> <IPermission
> class="FileIOPermission"
> version="1"
> All="C:\ReportHelper.log"/>
> <IPermission
> class="SecurityPermission"
> version="1"
> Flags="Execution, Assertion"/>
></PermissionSet>
><CodeGroup class="UnionCodeGroup"
> version="1"
> PermissionSetName="ReportHelperFilePermissionSet"
> Name="MyNewCodeGroup"
> Description="A special code group for my custom assembly.">
> <IMembershipCondition
> class="UrlMembershipCondition"
> version="1"
> Url="D:\Program Files\Microsoft SQL Server\MSSQL\Reporting
>Services\ReportServer\bin\ReportHelper.dll"/>
></CodeGroup>
><CodeGroup
> class="UnionCodeGroup"
> version="1"
> PermissionSetName="FullTrust"
> Name="AWCLibrary">
> <IMembershipCondition
> class="UrlMembershipCondition"
> version="1"
> Url="D:\Program Files\Microsoft SQL Server\MSSQL\Reporting
>Services\ReportServer\bin\ReportHelper.dll"/>
></CodeGroup>
>I write to my log file as follows:
>public static void WriteLogFile(String msg)
> {
> FileIOPermission perm1 = new
>FileIOPermission(FileIOPermissionAccess.Write, @."C:\ReportHelper.log");
> perm1.Assert();
> //try
> //{
> FileStream fs = new FileStream(@."C:\ReportHelper.log",
>FileMode.OpenOrCreate, FileAccess.ReadWrite);
> StreamWriter w = new StreamWriter(fs);
> w.BaseStream.Seek(0, SeekOrigin.End);
> w.Write("{0} {1} ", DateTime.Now.ToLongTimeString(),
> DateTime.Now.ToLongDateString());
> w.Write(msg + "\r\n");
> w.Flush();
> w.Close();
> }
>What am I missing here? Any help would be appreciated.
>Thanks!
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200507/1|||Hi Balaji,
I followed the article and it still doesn't work. I added [assembly:
AllowPartiallyTrustedCallers] to my assembly and removed extra
PermissionSet and CodeGroup sections from my policy file (I left the
"FullTrust" one) and I still get FileIOPermission error.
Thanks,
Kris
BALAJI via SQLMonster.com wrote:
> Check this
> http://www.c-sharpcorner.com/Code/2005/June/CustomAssemblyinRS.asp
> HTH
> If still you face the issue, let me know (bkkrishnan [at] hotmail [dot] com)
> Thanks
> Balaji
>
> Siwy wrote:
> >I got my custom assembly working fine until I add writing to a log
> >file. Then I get a FileIOPermission error.
> >
> >I have the following code in my policy file:
> >
> ><PermissionSet
> > class="NamedPermissionSet"
> > version="1"
> > Name="ReportHelperFilePermissionSet"
> > Description="A special permission set that grants read access to my
> >currency rates file.">
> > <IPermission
> > class="FileIOPermission"
> > version="1"
> > All="C:\ReportHelper.log"/>
> > <IPermission
> > class="SecurityPermission"
> > version="1"
> > Flags="Execution, Assertion"/>
> ></PermissionSet>
> >
> ><CodeGroup class="UnionCodeGroup"
> > version="1"
> > PermissionSetName="ReportHelperFilePermissionSet"
> > Name="MyNewCodeGroup"
> > Description="A special code group for my custom assembly.">
> > <IMembershipCondition
> > class="UrlMembershipCondition"
> > version="1"
> > Url="D:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> >Services\ReportServer\bin\ReportHelper.dll"/>
> ></CodeGroup>
> ><CodeGroup
> > class="UnionCodeGroup"
> > version="1"
> > PermissionSetName="FullTrust"
> > Name="AWCLibrary">
> > <IMembershipCondition
> > class="UrlMembershipCondition"
> > version="1"
> > Url="D:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> >Services\ReportServer\bin\ReportHelper.dll"/>
> ></CodeGroup>
> >
> >I write to my log file as follows:
> >
> >public static void WriteLogFile(String msg)
> > {
> > FileIOPermission perm1 = new
> >FileIOPermission(FileIOPermissionAccess.Write, @."C:\ReportHelper.log");
> > perm1.Assert();
> > //try
> > //{
> > FileStream fs = new FileStream(@."C:\ReportHelper.log",
> >FileMode.OpenOrCreate, FileAccess.ReadWrite);
> > StreamWriter w = new StreamWriter(fs);
> > w.BaseStream.Seek(0, SeekOrigin.End);
> > w.Write("{0} {1} ", DateTime.Now.ToLongTimeString(),
> > DateTime.Now.ToLongDateString());
> > w.Write(msg + "\r\n");
> > w.Flush();
> >
> > w.Close();
> > }
> >
> >What am I missing here? Any help would be appreciated.
> >
> >Thanks!
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200507/1

FileGrowth changes after the execution of DBCC SHRINKDATABASE

Using sp_helpfile to my database (SQL Server 2005,SP1) I get the following results

Data File size=5103616 KB, maxsize=Unlimited, growth=204800 KB

Log File size=504 KB, maxsize=Unlimited, growth=204800 KB

I execute a maintenance plan having only a Shrink Database Task. This task produces the following t-sql

USE [myDB]
GO
DBCC SHRINKDATABASE(N'myDB', 10, TRUNCATEONLY)

Now, executing sp_helpfile I get the same result but if i restart sql server I realize that the Data file's growth changes to growth=25600% (there is no other action or job using my Database)

What could cause this?

Thanks in advance

I've just learned this is a known bug.
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177

FileGrowth changes after the execution of DBCC SHRINKDATABASE

Using sp_helpfile to my database (SQL Server 2005,SP1) I get the following results

Data File size=5103616 KB, maxsize=Unlimited, growth=204800 KB

Log File size=504 KB, maxsize=Unlimited, growth=204800 KB

I execute a maintenance plan having only a Shrink Database Task. This task produces the following t-sql

USE [myDB]
GO
DBCC SHRINKDATABASE(N'myDB', 10, TRUNCATEONLY)

Now, executing sp_helpfile I get the same result but if i restart sql server I realize that the Data file's growth changes to growth=25600% (there is no other action or job using my Database)

What could cause this?

Thanks in advance

Probably your transaction log file still contains uncommitted transactions. Execute a BACKUP LOG dbname WITH TRUNCATE_ONLY before you run the shrink command|||

Thank you for your answer but it didnt work :(

I execute backup log , I execute sp_helpfile and the data file's growth has the normal size. But when I restart SQL Server , the data file's growth becomes equal to 102400%

I cant really undestand what may cause this ....

Any ideas?

|||

This problem disappears if I choose growth=10% and not growth in MB

Restarting SQL Server , the growth remains 10%.

It seems that the growth in MB makes the problems. But I don't prefer to have growth in %.

Any ideas?

Filegroup with no file, not possible to remove it!

Hi All,
I have a problem with one of my filegroup. Every files have been removed,
but when I try to remove the filegroup, I have the following error:
Msg 5042, Level 16, State 8, Line 1
The filegroup 'TEXT' cannot be removed because it is not empty.
When I run the following query, to see which filegroup have no
allocation_unit:
SELECT * FROM sys.filegroups f
WHERE NOT EXISTS (SELECT * FROM sys.allocation_units i WHERE
i.data_space_id = f.data_space_id)
The Filegroup named 'TEXT' is returned.
Does anyone have an idea to know why I cannot remove this filegroup?
Regards,
JeremyAs a sanity check, verify that there are no remaining files in the
filegroup:
EXEC sp_helpfilegroup 'TEXT'
or
SELECT *
FROM sys.data_spaces AS ds
JOIN sys.database_files AS df ON
df.data_space_id = ds.data_space_id
WHERE ds.NAME = 'TEXT'
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeremy Calles" <Jeremy Calles@.discussions.microsoft.com> wrote in message
news:C24E794B-0241-495A-8E87-3F57F9BB4E67@.microsoft.com...
> Hi All,
> I have a problem with one of my filegroup. Every files have been removed,
> but when I try to remove the filegroup, I have the following error:
> Msg 5042, Level 16, State 8, Line 1
> The filegroup 'TEXT' cannot be removed because it is not empty.
> When I run the following query, to see which filegroup have no
> allocation_unit:
> SELECT * FROM sys.filegroups f
> WHERE NOT EXISTS (SELECT * FROM sys.allocation_units i WHERE
> i.data_space_id = f.data_space_id)
> The Filegroup named 'TEXT' is returned.
> Does anyone have an idea to know why I cannot remove this filegroup?
> Regards,
> Jeremy|||Hi Dan,
Thanks for your message, but I have already checked that no files are
assigned to this filegroup. And of course, there are no files.
So this is why it seems really strange to me.
I don't know what could it be.
Regards,
Jeremy|||Jeremy
DBCC SHRINKFILE command has an EMPTYFILE parameter.Specify a file that is
on filegroup and run it
"Jeremy Calles" <JeremyCalles@.discussions.microsoft.com> wrote in message
news:A3D24B42-5CB4-4E27-AD85-15DCB1BDA701@.microsoft.com...
> Hi Dan,
> Thanks for your message, but I have already checked that no files are
> assigned to this filegroup. And of course, there are no files.
> So this is why it seems really strange to me.
> I don't know what could it be.
> Regards,
> Jeremy|||Hi Uri,
As I have said in my first post: There is no files in this filegroups.
I have removed all files using DBCC SHRINKFILE
When I execute "EXEC sp_helpfilegroup 'TEXT'"I have no result.
This is why I have posted in this filegroup. Cause a filegroup without any
files attached cannot be removed.
Regards,
Jeremy|||Sorry, did not read it properly, how did you try to remove it ? by EM or
ALTER DATABASE... in QA?
"Jeremy Calles" <JeremyCalles@.discussions.microsoft.com> wrote in message
news:D418E95E-EE3A-4049-BFDB-8432D31000F7@.microsoft.com...
> Hi Uri,
> As I have said in my first post: There is no files in this filegroups.
> I have removed all files using DBCC SHRINKFILE
> When I execute "EXEC sp_helpfilegroup 'TEXT'"I have no result.
> This is why I have posted in this filegroup. Cause a filegroup without any
> files attached cannot be removed.
> Regards,
> Jeremy
>
>|||You're welcome, Uri.
I'm doing ALTER DATABASE XXX REMOVE FILEGROUP [TEXT] which has worked fo
r
many filegroups before, and I have the following error:
Msg 5042, Level 16, State 8, Line 1
The filegroup 'TEXT' cannot be removed because it is not empty.
Regards,
Jeremy|||I haven't seen this problem before. Can you post the exact steps you
performed the remove the files?
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeremy Calles" <JeremyCalles@.discussions.microsoft.com> wrote in message
news:A3D24B42-5CB4-4E27-AD85-15DCB1BDA701@.microsoft.com...
> Hi Dan,
> Thanks for your message, but I have already checked that no files are
> assigned to this filegroup. And of course, there are no files.
> So this is why it seems really strange to me.
> I don't know what could it be.
> Regards,
> Jeremy|||Hi Dan,
I have done the following:
- First extract the name of files attached to [TEXT] filegroup:
SELECT name FROM sys.database_files WHERE data_space_id =
FILEGROUP_ID('TEXT')
-For each file, I have done:
DBCC SHRINKFILE(XXXXXXX_01,EMPTYFILE)
ALTER DATABASE XXX REMOVE FILE XXXXXXX_01
-When all files have been remove, I do the following on filegroup:
ALTER DATABASE XXX REMOVE FILEGROUP [Text]
I have done the following many times, and it used to works on all
filegroups, but not on this one.
Regards,
Jeremy|||How did you move existing objects from filegroup? I just want to be sure
I'm not missing something.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeremy Calles" <JeremyCalles@.discussions.microsoft.com> wrote in message
news:5E8B5C25-C8B6-4EEC-8A03-8CB2042439CD@.microsoft.com...
> Hi Dan,
> I have done the following:
> - First extract the name of files attached to [TEXT] filegroup:
> SELECT name FROM sys.database_files WHERE data_space_id =
> FILEGROUP_ID('TEXT')
> -For each file, I have done:
> DBCC SHRINKFILE(XXXXXXX_01,EMPTYFILE)
> ALTER DATABASE XXX REMOVE FILE XXXXXXX_01
> -When all files have been remove, I do the following on filegroup:
> ALTER DATABASE XXX REMOVE FILEGROUP [Text]
> I have done the following many times, and it used to works on all
> filegroups, but not on this one.
> Regards,
> Jeremy

Filegroup with no file, not possible to remove it!

Hi All,
I have a problem with one of my filegroup. Every files have been removed,
but when I try to remove the filegroup, I have the following error:
Msg 5042, Level 16, State 8, Line 1
The filegroup 'TEXT' cannot be removed because it is not empty.
When I run the following query, to see which filegroup have no
allocation_unit:
SELECT * FROM sys.filegroups f
WHERE NOT EXISTS (SELECT * FROM sys.allocation_units i WHERE
i.data_space_id = f.data_space_id)
The Filegroup named 'TEXT' is returned.
Does anyone have an idea to know why I cannot remove this filegroup?
Regards,
JeremyAs a sanity check, verify that there are no remaining files in the
filegroup:
EXEC sp_helpfilegroup 'TEXT'
or
SELECT *
FROM sys.data_spaces AS ds
JOIN sys.database_files AS df ON
df.data_space_id = ds.data_space_id
WHERE ds.NAME = 'TEXT'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeremy Calles" <Jeremy Calles@.discussions.microsoft.com> wrote in message
news:C24E794B-0241-495A-8E87-3F57F9BB4E67@.microsoft.com...
> Hi All,
> I have a problem with one of my filegroup. Every files have been removed,
> but when I try to remove the filegroup, I have the following error:
> Msg 5042, Level 16, State 8, Line 1
> The filegroup 'TEXT' cannot be removed because it is not empty.
> When I run the following query, to see which filegroup have no
> allocation_unit:
> SELECT * FROM sys.filegroups f
> WHERE NOT EXISTS (SELECT * FROM sys.allocation_units i WHERE
> i.data_space_id = f.data_space_id)
> The Filegroup named 'TEXT' is returned.
> Does anyone have an idea to know why I cannot remove this filegroup?
> Regards,
> Jeremy|||Hi Dan,
Thanks for your message, but I have already checked that no files are
assigned to this filegroup. And of course, there are no files.
So this is why it seems really strange to me.
I don't know what could it be.
Regards,
Jeremy|||Jeremy
DBCC SHRINKFILE command has an EMPTYFILE parameter.Specify a file that is
on filegroup and run it
"Jeremy Calles" <JeremyCalles@.discussions.microsoft.com> wrote in message
news:A3D24B42-5CB4-4E27-AD85-15DCB1BDA701@.microsoft.com...
> Hi Dan,
> Thanks for your message, but I have already checked that no files are
> assigned to this filegroup. And of course, there are no files.
> So this is why it seems really strange to me.
> I don't know what could it be.
> Regards,
> Jeremy|||Hi Uri,
As I have said in my first post: There is no files in this filegroups.
I have removed all files using DBCC SHRINKFILE
When I execute "EXEC sp_helpfilegroup 'TEXT'"I have no result.
This is why I have posted in this filegroup. Cause a filegroup without any
files attached cannot be removed.
Regards,
Jeremy|||Sorry, did not read it properly, how did you try to remove it ? by EM or
ALTER DATABASE... in QA?
"Jeremy Calles" <JeremyCalles@.discussions.microsoft.com> wrote in message
news:D418E95E-EE3A-4049-BFDB-8432D31000F7@.microsoft.com...
> Hi Uri,
> As I have said in my first post: There is no files in this filegroups.
> I have removed all files using DBCC SHRINKFILE
> When I execute "EXEC sp_helpfilegroup 'TEXT'"I have no result.
> This is why I have posted in this filegroup. Cause a filegroup without any
> files attached cannot be removed.
> Regards,
> Jeremy
>
>|||You're welcome, Uri.
I'm doing ALTER DATABASE XXX REMOVE FILEGROUP [TEXT] which has worked for
many filegroups before, and I have the following error:
Msg 5042, Level 16, State 8, Line 1
The filegroup 'TEXT' cannot be removed because it is not empty.
Regards,
Jeremy|||I haven't seen this problem before. Can you post the exact steps you
performed the remove the files?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeremy Calles" <JeremyCalles@.discussions.microsoft.com> wrote in message
news:A3D24B42-5CB4-4E27-AD85-15DCB1BDA701@.microsoft.com...
> Hi Dan,
> Thanks for your message, but I have already checked that no files are
> assigned to this filegroup. And of course, there are no files.
> So this is why it seems really strange to me.
> I don't know what could it be.
> Regards,
> Jeremy|||Hi Dan,
I have done the following:
- First extract the name of files attached to [TEXT] filegroup:
SELECT name FROM sys.database_files WHERE data_space_id =FILEGROUP_ID('TEXT')
-For each file, I have done:
DBCC SHRINKFILE(XXXXXXX_01,EMPTYFILE)
ALTER DATABASE XXX REMOVE FILE XXXXXXX_01
-When all files have been remove, I do the following on filegroup:
ALTER DATABASE XXX REMOVE FILEGROUP [Text]
I have done the following many times, and it used to works on all
filegroups, but not on this one.
Regards,
Jeremy|||How did you move existing objects from filegroup? I just want to be sure
I'm not missing something.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeremy Calles" <JeremyCalles@.discussions.microsoft.com> wrote in message
news:5E8B5C25-C8B6-4EEC-8A03-8CB2042439CD@.microsoft.com...
> Hi Dan,
> I have done the following:
> - First extract the name of files attached to [TEXT] filegroup:
> SELECT name FROM sys.database_files WHERE data_space_id => FILEGROUP_ID('TEXT')
> -For each file, I have done:
> DBCC SHRINKFILE(XXXXXXX_01,EMPTYFILE)
> ALTER DATABASE XXX REMOVE FILE XXXXXXX_01
> -When all files have been remove, I do the following on filegroup:
> ALTER DATABASE XXX REMOVE FILEGROUP [Text]
> I have done the following many times, and it used to works on all
> filegroups, but not on this one.
> Regards,
> Jeremy|||I have dropped a lot of objects, but the one I need to move, I have use the
syntax below:
CREATE CLUSTERED INDEX [CIdxTemp] ON [dbo].[YYYY]
(
[PK] ASC
)WITH (DROP_EXISTING = ON) ON [OtherFilegroup]
Regards,
Jeremy|||With no files in the filegroup, the error message is obviously erroneous.
Unless someone else jumps in, I suggest you open a support case.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeremy Calles" <JeremyCalles@.discussions.microsoft.com> wrote in message
news:EBACBC50-2B8B-46F8-9FE1-E161470A8A75@.microsoft.com...
>I have dropped a lot of objects, but the one I need to move, I have use the
> syntax below:
> CREATE CLUSTERED INDEX [CIdxTemp] ON [dbo].[YYYY]
> (
> [PK] ASC
> )WITH (DROP_EXISTING = ON) ON [OtherFilegroup]
> Regards,
> Jeremy
>

Filegroup is full

Hi.
I've a msde database and I'm getting the following error every minute
Error 1105, Severity: 17, ErrState: 2
Could not allocate space for object 'ProductSettings' in database
'epo_Srv-ms-avd' because the 'Primary' filegroup is full
I've cleanup the database but the error keeps on.
I need urgent help!!!
Thank you very much
Hi Eduardo,
How big is the database? Any chance it's up to 2G (ie the limit)?
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Eduardo Crespo" <crespo_santamaria@.NOSPAMhotmail.com> wrote in message
news:OoFIRcmuEHA.1992@.TK2MSFTNGP10.phx.gbl...
> Hi.
> I've a msde database and I'm getting the following error every minute
> Error 1105, Severity: 17, ErrState: 2
> Could not allocate space for object 'ProductSettings' in database
> 'epo_Srv-ms-avd' because the 'Primary' filegroup is full
> I've cleanup the database but the error keeps on.
> I need urgent help!!!
> Thank you very much
>
>
|||hi Eduardo,
"Eduardo Crespo" <crespo_santamaria@.NOSPAMhotmail.com> ha scritto nel
messaggio news:OoFIRcmuEHA.1992@.TK2MSFTNGP10.phx.gbl
> Hi.
> I've a msde database and I'm getting the following error every minute
> Error 1105, Severity: 17, ErrState: 2
> Could not allocate space for object 'ProductSettings' in database
> 'epo_Srv-ms-avd' because the 'Primary' filegroup is full
> I've cleanup the database but the error keeps on.
> I need urgent help!!!
> Thank you very much
in addition to Greg's answer, try executing
EXEC sp_helpdb 'your_db_name'
to see, in the 2nd resultset, if the datafile has limited file maxsize
option set or it can growth till 2gb limit...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thank you very much. Database size is 2 GB
"Eduardo Crespo" <crespo_santamaria@.NOSPAMhotmail.com> escribi en el
mensaje news:OoFIRcmuEHA.1992@.TK2MSFTNGP10.phx.gbl...
> Hi.
> I've a msde database and I'm getting the following error every minute
> Error 1105, Severity: 17, ErrState: 2
> Could not allocate space for object 'ProductSettings' in database
> 'epo_Srv-ms-avd' because the 'Primary' filegroup is full
> I've cleanup the database but the error keeps on.
> I need urgent help!!!
> Thank you very much
>
>

Filegroup backups/restore design problems

At the design stage of an application using SQL server 7 I assumed the
following could be done:
- Separate monthly buckets of data into separate filegroups.
- Flag current filegroups as readonly when the new month rollsover.
- design was proposed in this way to minimise backups of the entire
database
of which most will be read-only. Was I completely wrong ?
Backup:
I assumed we could backup the primary, active (current) filegroups and
the previous filegroup.
I assumed a complete db could be rebuilt using the primary and active
filegroup backup.
Is this possible ?
Is there an option on FULL DB BACKUPS to ignore read-only filegroups
and therefore facilitate restoration of a smaller db with only the
primary and active filegroups?
Archiving:
When using this approach - and imagining 2 years of filegroups -
Suppose I would drop one of the tables ?
Could you restore this archived filegroup to the current db ?
thanks
billbill
If you don't place the table on a separated physical disks you won't get a
perfomance benefit.
Did you consider to use a full backup database a week period and backup log
file a on an hour period?
"bill k" <bkatelis@.yahoo.com> wrote in message
news:b11556a0.0408020050.63e48688@.posting.google.com...
> At the design stage of an application using SQL server 7 I assumed the
> following could be done:
> - Separate monthly buckets of data into separate filegroups.
> - Flag current filegroups as readonly when the new month rollsover.
> - design was proposed in this way to minimise backups of the entire
> database
> of which most will be read-only. Was I completely wrong ?
> Backup:
> I assumed we could backup the primary, active (current) filegroups and
> the previous filegroup.
> I assumed a complete db could be rebuilt using the primary and active
> filegroup backup.
> Is this possible ?
> Is there an option on FULL DB BACKUPS to ignore read-only filegroups
> and therefore facilitate restoration of a smaller db with only the
> primary and active filegroups?
> Archiving:
> When using this approach - and imagining 2 years of filegroups -
> Suppose I would drop one of the tables ?
> Could you restore this archived filegroup to the current db ?
>
> thanks
> bill|||see inline
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"bill k" <bkatelis@.yahoo.com> wrote in message
news:b11556a0.0408020050.63e48688@.posting.google.com...
> At the design stage of an application using SQL server 7 I assumed the
> following could be done:
> - Separate monthly buckets of data into separate filegroups.
> - Flag current filegroups as readonly when the new month rollsover.
> - design was proposed in this way to minimise backups of the entire
> database
> of which most will be read-only. Was I completely wrong ?
IF you are talking about 1 filegroup per month, you need to be aware that
the max # of filegroups per database is 256. Additionally, you would have to
name each of the partition tables differently, a single table can not span
multiple filegroups. Each filegroup can be marked as read-only... The idea
you have is correct however, rolling historical data into another filegroup
which doesn't need to be backed up as frequently is one of the primary
usages for filegroups... Maybe you roll everything up into annual
filegroups instead of monthly...
> Backup:
> I assumed we could backup the primary, active (current) filegroups and
> the previous filegroup.
> I assumed a complete db could be rebuilt using the primary and active
> filegroup backup.
> Is this possible ?
You may restore ANY filegroup individually... What you might do , is put
the historical filegroups on a separate raid array from the current
information. Then back the historical stuff up monthly when it changes, and
keep the backups for (maybe) 3 months.
> Is there an option on FULL DB BACKUPS to ignore read-only filegroups
> and therefore facilitate restoration of a smaller db with only the
> primary and active filegroups?
No there isn't... Your full database backup will have to specify the
filegroups you wish to backup... I would still do a REAL full database
backup occasionally.
> Archiving:
> When using this approach - and imagining 2 years of filegroups -
> Suppose I would drop one of the tables ?
> Could you restore this archived filegroup to the current db ?
You may restore any filegroup independently, but everthing in the filegroup
will be restored... You may also do a partial restore to another database,
then pull only the tables you need back into the production database..
>
> thanks
> bill
Your thinking is sound... But before you go production make sure you do
testing to ensure your plan works as you intend...|||Wayne,
I appreciate your comments.
As for the 1 table per filegroup - yes I am doing that and am aware of
the 256 limit. And yes my table are named differently and they do not
span filegroups - they are created dynamically in their own filegroup
and with a yyyymm extension on each.
Could I please ask you to clarify further my understanding.
You mention that you can do a partial restore but from my
reading/understanding this can only be done on a full db backup.
To recreate my db as proposed I understood that I would need to
1. restore primary filegroup
2. restore active filegroup
3. restore read-only filegroups
4. restore ALL transaction log backups since the last read-only
filegroup backup.
Is this correct ?
And can I do the following on a completely new server:
1. restore primary filegroup
2. restore active filegroup
3. restore ALL transaction log backups since the last primary/active
filegroup backup.
thanks
bill
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message news:<uD0$2vIeEHA.592@.TK2MSFTNGP11.phx.gbl>...
> see inline
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "bill k" <bkatelis@.yahoo.com> wrote in message
> news:b11556a0.0408020050.63e48688@.posting.google.com...
> > At the design stage of an application using SQL server 7 I assumed the
> > following could be done:
> > - Separate monthly buckets of data into separate filegroups.
> > - Flag current filegroups as readonly when the new month rollsover.
> > - design was proposed in this way to minimise backups of the entire
> > database
> > of which most will be read-only. Was I completely wrong ?
> IF you are talking about 1 filegroup per month, you need to be aware that
> the max # of filegroups per database is 256. Additionally, you would have to
> name each of the partition tables differently, a single table can not span
> multiple filegroups. Each filegroup can be marked as read-only... The idea
> you have is correct however, rolling historical data into another filegroup
> which doesn't need to be backed up as frequently is one of the primary
> usages for filegroups... Maybe you roll everything up into annual
> filegroups instead of monthly...
> >
> > Backup:
> > I assumed we could backup the primary, active (current) filegroups and
> > the previous filegroup.
> > I assumed a complete db could be rebuilt using the primary and active
> > filegroup backup.
> > Is this possible ?
> You may restore ANY filegroup individually... What you might do , is put
> the historical filegroups on a separate raid array from the current
> information. Then back the historical stuff up monthly when it changes, and
> keep the backups for (maybe) 3 months.
>
> > Is there an option on FULL DB BACKUPS to ignore read-only filegroups
> > and therefore facilitate restoration of a smaller db with only the
> > primary and active filegroups?
> No there isn't... Your full database backup will have to specify the
> filegroups you wish to backup... I would still do a REAL full database
> backup occasionally.
> >
> > Archiving:
> > When using this approach - and imagining 2 years of filegroups -
> > Suppose I would drop one of the tables ?
> > Could you restore this archived filegroup to the current db ?
> You may restore any filegroup independently, but everthing in the filegroup
> will be restored... You may also do a partial restore to another database,
> then pull only the tables you need back into the production database..
> >
> >
> > thanks
> > bill
> Your thinking is sound... But before you go production make sure you do
> testing to ensure your plan works as you intend...

File Upload Help

Hi, I am trying to upload a file to database.

I have used the following code, every loads good to the database apart from the image, does it go anywhere?

I have created a column in the table called 'FileUploadAdvert' and made it an image?

Any ideas where I'm going wrong?

Thanks

Gordon

ProtectedSub btnAdvertSubmit_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles btnAdvertSubmit.Click

Dim dashDataSourceAsNew SqlDataSource()dashDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("DashConnectionString1").ToString()

dashDataSource.InsertCommandType = SqlDataSourceCommandType.Text

dashDataSource.InsertCommand ="INSERT INTO tblAdvert (AdvertOwner, AdvertName, TopLeftH, TopLeftV, Height, Width, ToolTip, WebLink, AcceptTerms, DateTimeStamp, IPAddress) VALUES (@.AdvertOwner, @.AdvertName, @.TopLeftH, @.TopLeftV, @.Height, @.Width, @.ToolTip, @.WebLink, @.AcceptTerms, @.DateTimeStamp, @.IPAddress)"

dashDataSource.InsertParameters.Add("AdvertOwner", txtName.Text)

dashDataSource.InsertParameters.Add("AdvertName", txtCompName.Text)

dashDataSource.InsertParameters.Add("TopLeftH", DropDownAccross.Text)

dashDataSource.InsertParameters.Add("TopLeftV", DropDownDown.Text)

dashDataSource.InsertParameters.Add("Height", DropDownHeight.Text)

dashDataSource.InsertParameters.Add("Width", DropDownWidth.Text)

dashDataSource.InsertParameters.Add("ToolTip", txtOver.Text)

dashDataSource.InsertParameters.Add("Weblink", txtURL.Text)

dashDataSource.InsertParameters.Add("AcceptTerms", CheckBoxAgree.Checked)

dashDataSource.InsertParameters.Add("IPAddress", Request.UserHostAddress.ToString)

dashDataSource.InsertParameters.Add("DateTimeStamp", DateTime.Now)

IfNot FileUploadAdvert.PostedFileIsNothingThen

Dim filepathAsString = FileUploadAdvert.PostedFile.FileName

Dim patAsString ="\\(?:.+)\\(.+)\.(.+)"

Dim rAs Regex =New Regex(pat)

'run

Dim mAs Match = r.Match(filepath)

Dim file_extAsString = m.Groups(2).Captures(0).ToString()

Dim filenameAsString = m.Groups(1).Captures(0).ToString()

Dim fileAsString = filename &"." & file_ext

'save the file to the server

FileUploadAdvert.PostedFile.SaveAs(Server.MapPath(".\") & file)

lblStatus.Text ="File Saved to: " & Server.MapPath(".\") & file

EndIf

Dim rowsAffectedAsInteger = 0

Try

rowsAffected = dashDataSource.Insert()

Catch exAs Exception

Server.Transfer("Register_Fail.aspx")

Finally

dashDataSource =Nothing

EndTry

If rowsAffected <> 1Then

Server.Transfer("Register_Fail.aspx")

Else

Server.Transfer("Register_Complete.aspx")

EndIf

EndSub

Looking at your code, all you have done is saved the uploaded file onto theweb server's file system.

You have not saved it into the database.

Ifyou want to save the actual image to your database you will need to geta byte array from the image, then update your insert statement toinclude the image and pass in the byte array as a binary type inputparameter.

Or the other way is just to store the file path of where you saved the image on your file system to your database.

|||

Any Idea how I do either of those? I've hit a bit of a dead end!

File System Task Error

Hi,
I am using the 'File System Task ' to create a directory structure (e.g ..\DB; ..\DB\LOG; ..\DB\BACKUP; )
I set following properties for the single tasks: UseDirectoryIfExists = True; Operation = Create Directory;

The task works fine before installing SP1 on the server. Now it creates an ERROR if the directory already exists and it is not empty.

SSIS package "testcreatedirectory.dtsx" starting.

Warning: 0xC002915A at Create DB Directory, File System Task: The Directory already exists.

Error: 0xC002F304 at Create DB Directory, File System Task: An error occurred with the following error message: "Das Verzeichnis ist nicht leer.". (The Directory is not empty.)

Task failed: Create DB Directory

Warning: 0x80019002 at Create Directorys: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

Warning: 0x80019002 at testcreatedirectory: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "testcreatedirectory.dtsx" finished: Failure.

Does anyone know if this is a known bug in SP1 or maybe its a feature and if there already exists a solution (maybe I have to set additional properties I have not discovered as yet).

Thanks in advance
Holger

I have just ran into the same problem after applying Service Pack 1. Have you resolved it yet?|||i don't know if this will help with your particular situation, but hotfix packages for sql server 2005 sp1 were recently released: http://support.microsoft.com/kb/918222/en-us|||I installed the SSIS hotfix but I still get the same results. I guess I need to check on Microsoft Bug list site to see if it's been posted.|||I also tried the hotfix (last week) - with the same result . Have you checked the Microsoft Bug list?|||

I don't know if there is a proper fix for this but you might want to use a item loop container to loop over the paths that you want created and then just have two tasks in there. One task would be a script task to check the existance and the other to create if it does not.

This would achieve the same result as the former single task.

Fred

|||

Has anyone been able to solve this problem without an unofficial workaround?
All my packages uses this task to ease logging and currently they are all failing.

|||

No, I have not resolved the issue yet. The only soloution I have are work arounds.

|||

Could some body help me with this same issue. How do we do the work around method.

The first time i run it works fine and creates some file inside but when i execute the pacakge for the second time. It throughs up error saying that the directory is not empty. If i go and delete the files manually it write the files corretly. i have the File system task with UseIFDirectoryExists = True and operation = create directory.

Any help,

Thanks,

JA

|||I had the same issue, but realized that within the constraints of the task there was no "real" solution. I set the "Force Execution Result" property of the File Systme Object (FSO) Task to "Success". This solved the issue. It would still create a directory if it didn't exist and leave it if it didn't. Then I used another FSO task to move the files I wanted to rename to the new directory. So in this case I used the move option to move and rename. I often use the move option to rename things, if the rename won't work. To tell you the truth the move option might do what you wan't as well. I can't tell because I used both, first the create, then the move.|||

So you had the same the FSO with ForceExecutionResult = success and usedirectoryif exists = true and operation = create Directory. It does not work for me. Even if i use next step to move files to a new directory. you have to create the directory in first place and then move and then rename or delete. this is what i get.

Source: Creating Directoy Folder
Description: The Directory already exists.
End Warning
Error: 2006-09-08 12:21:31.59
Code: 0xC002F304
Source: Creating Directoy Folder
Description: An error occurred with the following error message: "The directory is not empty.

Any idea about this error

Thanks,

JA

|||Like I said, there is no "real" solution with the task. It just doesn't do what it should. What I offered was a temporary fix to have the task move on regardless. The only other fix, as others have mentioned is doing it in a script, utilizing the File System Object. It seems to be a bug.

File System Task Error

Hi,
I am using the 'File System Task ' to create a directory structure (e.g ..\DB; ..\DB\LOG; ..\DB\BACKUP; )
I set following properties for the single tasks: UseDirectoryIfExists = True; Operation = Create Directory;

The task works fine before installing SP1 on the server. Now it creates an ERROR if the directory already exists and it is not empty.

SSIS package "testcreatedirectory.dtsx" starting.

Warning: 0xC002915A at Create DB Directory, File System Task: The Directory already exists.

Error: 0xC002F304 at Create DB Directory, File System Task: An error occurred with the following error message: "Das Verzeichnis ist nicht leer.". (The Directory is not empty.)

Task failed: Create DB Directory

Warning: 0x80019002 at Create Directorys: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

Warning: 0x80019002 at testcreatedirectory: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "testcreatedirectory.dtsx" finished: Failure.

Does anyone know if this is a known bug in SP1 or maybe its a feature and if there already exists a solution (maybe I have to set additional properties I have not discovered as yet).

Thanks in advance
Holger

I have just ran into the same problem after applying Service Pack 1. Have you resolved it yet?|||i don't know if this will help with your particular situation, but hotfix packages for sql server 2005 sp1 were recently released: http://support.microsoft.com/kb/918222/en-us|||I installed the SSIS hotfix but I still get the same results. I guess I need to check on Microsoft Bug list site to see if it's been posted.|||I also tried the hotfix (last week) - with the same result . Have you checked the Microsoft Bug list?|||

I don't know if there is a proper fix for this but you might want to use a item loop container to loop over the paths that you want created and then just have two tasks in there. One task would be a script task to check the existance and the other to create if it does not.

This would achieve the same result as the former single task.

Fred

|||

Has anyone been able to solve this problem without an unofficial workaround?
All my packages uses this task to ease logging and currently they are all failing.

|||

No, I have not resolved the issue yet. The only soloution I have are work arounds.

|||

Could some body help me with this same issue. How do we do the work around method.

The first time i run it works fine and creates some file inside but when i execute the pacakge for the second time. It throughs up error saying that the directory is not empty. If i go and delete the files manually it write the files corretly. i have the File system task with UseIFDirectoryExists = True and operation = create directory.

Any help,

Thanks,

JA

|||I had the same issue, but realized that within the constraints of the task there was no "real" solution. I set the "Force Execution Result" property of the File Systme Object (FSO) Task to "Success". This solved the issue. It would still create a directory if it didn't exist and leave it if it didn't. Then I used another FSO task to move the files I wanted to rename to the new directory. So in this case I used the move option to move and rename. I often use the move option to rename things, if the rename won't work. To tell you the truth the move option might do what you wan't as well. I can't tell because I used both, first the create, then the move.|||

So you had the same the FSO with ForceExecutionResult = success and usedirectoryif exists = true and operation = create Directory. It does not work for me. Even if i use next step to move files to a new directory. you have to create the directory in first place and then move and then rename or delete. this is what i get.

Source: Creating Directoy Folder
Description: The Directory already exists.
End Warning
Error: 2006-09-08 12:21:31.59
Code: 0xC002F304
Source: Creating Directoy Folder
Description: An error occurred with the following error message: "The directory is not empty.

Any idea about this error

Thanks,

JA

|||Like I said, there is no "real" solution with the task. It just doesn't do what it should. What I offered was a temporary fix to have the task move on regardless. The only other fix, as others have mentioned is doing it in a script, utilizing the File System Object. It seems to be a bug.

File System Task Error

Hi,
I am using the 'File System Task ' to create a directory structure (e.g ..\DB; ..\DB\LOG; ..\DB\BACKUP; )
I set following properties for the single tasks: UseDirectoryIfExists = True; Operation = Create Directory;

The task works fine before installing SP1 on the server. Now it creates an ERROR if the directory already exists and it is not empty.

SSIS package "testcreatedirectory.dtsx" starting.

Warning: 0xC002915A at Create DB Directory, File System Task: The Directory already exists.

Error: 0xC002F304 at Create DB Directory, File System Task: An error occurred with the following error message: "Das Verzeichnis ist nicht leer.". (The Directory is not empty.)

Task failed: Create DB Directory

Warning: 0x80019002 at Create Directorys: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

Warning: 0x80019002 at testcreatedirectory: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "testcreatedirectory.dtsx" finished: Failure.

Does anyone know if this is a known bug in SP1 or maybe its a feature and if there already exists a solution (maybe I have to set additional properties I have not discovered as yet).

Thanks in advance
Holger

I have just ran into the same problem after applying Service Pack 1. Have you resolved it yet?|||i don't know if this will help with your particular situation, but hotfix packages for sql server 2005 sp1 were recently released: http://support.microsoft.com/kb/918222/en-us|||I installed the SSIS hotfix but I still get the same results. I guess I need to check on Microsoft Bug list site to see if it's been posted.|||I also tried the hotfix (last week) - with the same result . Have you checked the Microsoft Bug list?|||

I don't know if there is a proper fix for this but you might want to use a item loop container to loop over the paths that you want created and then just have two tasks in there. One task would be a script task to check the existance and the other to create if it does not.

This would achieve the same result as the former single task.

Fred

|||

Has anyone been able to solve this problem without an unofficial workaround?
All my packages uses this task to ease logging and currently they are all failing.

|||

No, I have not resolved the issue yet. The only soloution I have are work arounds.

|||

Could some body help me with this same issue. How do we do the work around method.

The first time i run it works fine and creates some file inside but when i execute the pacakge for the second time. It throughs up error saying that the directory is not empty. If i go and delete the files manually it write the files corretly. i have the File system task with UseIFDirectoryExists = True and operation = create directory.

Any help,

Thanks,

JA

|||I had the same issue, but realized that within the constraints of the task there was no "real" solution. I set the "Force Execution Result" property of the File Systme Object (FSO) Task to "Success". This solved the issue. It would still create a directory if it didn't exist and leave it if it didn't. Then I used another FSO task to move the files I wanted to rename to the new directory. So in this case I used the move option to move and rename. I often use the move option to rename things, if the rename won't work. To tell you the truth the move option might do what you wan't as well. I can't tell because I used both, first the create, then the move.|||

So you had the same the FSO with ForceExecutionResult = success and usedirectoryif exists = true and operation = create Directory. It does not work for me. Even if i use next step to move files to a new directory. you have to create the directory in first place and then move and then rename or delete. this is what i get.

Source: Creating Directoy Folder
Description: The Directory already exists.
End Warning
Error: 2006-09-08 12:21:31.59
Code: 0xC002F304
Source: Creating Directoy Folder
Description: An error occurred with the following error message: "The directory is not empty.

Any idea about this error

Thanks,

JA

|||Like I said, there is no "real" solution with the task. It just doesn't do what it should. What I offered was a temporary fix to have the task move on regardless. The only other fix, as others have mentioned is doing it in a script, utilizing the File System Object. It seems to be a bug.

File System Error: The record ID is incorrect

Hi,

We have deployed SQL Server 2005 with Analysis Services and Reporting Services.

When running a report, we get the following error:

An error has occurred during report processing.

Cannot read the next data row for the data set DM_GASTOS.

File system error:

The record ID is incorrect. Physical file: . Logical file: .

The error occurs both with a local user account and domain user account. The user account is member of a role which has Cell Data Security restriction in Analysis Services. When other member run the report, the error goes away.

Anybody has seen this before? Any solutions?

Cheers

This looks like a data corruption issue. Try fully re-processing your entire database.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi. I'm encountering the same problem. I have re-processed my database but I still get the error message when I generate my report. Any other suggestions?

Thanks

|||

I'm having the same problem.

I have the same OLAP data base in 3 servers, and the error is the same.

Any other suggestions?

File System Error: The record ID is incorrect

Hi,

We have deployed SQL Server 2005 with Analysis Services and Reporting Services.

When running a report, we get the following error:

An error has occurred during report processing.

Cannot read the next data row for the data set DM_GASTOS.

File system error:

The record ID is incorrect. Physical file: . Logical file: .

The error occurs both with a local user account and domain user account. The user account is member of a role which has Cell Data Security restriction in Analysis Services. When other member run the report, the error goes away.

Anybody has seen this before? Any solutions?

Cheers

This looks like a data corruption issue. Try fully re-processing your entire database.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi. I'm encountering the same problem. I have re-processed my database but I still get the error message when I generate my report. Any other suggestions?

Thanks

|||

I'm having the same problem.

I have the same OLAP data base in 3 servers, and the error is the same.

Any other suggestions?

File System Error: The record ID is incorrect

Hi,

We have deployed SQL Server 2005 with Analysis Services and Reporting Services.

When running a report, we get the following error:

An error has occurred during report processing.

Cannot read the next data row for the data set DM_GASTOS.

File system error:

The record ID is incorrect. Physical file: . Logical file: .

The error occurs both with a local user account and domain user account. The user account is member of a role which has Cell Data Security restriction in Analysis Services. When other member run the report, the error goes away.

Anybody has seen this before? Any solutions?

Cheers

This looks like a data corruption issue. Try fully re-processing your entire database.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi. I'm encountering the same problem. I have re-processed my database but I still get the error message when I generate my report. Any other suggestions?

Thanks

|||

I'm having the same problem.

I have the same OLAP data base in 3 servers, and the error is the same.

Any other suggestions?

File system error: The following file is corrupted Analysis Services

Hi i got this error while deploying Analysis Services project.

Error 1 File system error: The following file is corrupted: Physical file: \\?\C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\Analysis Services Tutorial.0.db\Customer.0.dim\5.English Education.(All).sstore. Logical file . Errors in the metadata manager. An error occurred when loading the Customer dimension, from the file, '\\?\C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\Analysis Services Tutorial.0.db\Customer.5.dim.xml'. Errors in the metadata manager. An error occurred when loading the Analysis Services Tutorial cube, from the file, '\\?\C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\Analysis Services Tutorial.0.db\Adventure Works DW.9.cub.xml'. 0 0

pls help to resolve this problem... thanks in advance.

kapil

file or filegroup not online? "sysft_ftcat_documentindex"

Just upgraded from SQL 2000 to 2005 and I created a backup maintenance plan
but I am getting the following error everytime the maintenance plan runs:
Error number: -1073548784
Executing the query "BACKUP DATABASE [Clarke_MSCRM] TO DISK = N'C:\\Pro
gram
Files\\Microsoft SQL
Server\\MSSQL\\BACKUP\\Clarke_MSCRM\\Cla
rke_MSCRM_backup_200607270400.bak'
WITH NOFORMAT, NOINIT, NAME = N'Clarke_MSCRM_backup_20060727040005', SKIP,
REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "The backup of the file or filegroup
"sysft_ftcat_documentindex" is not permitted because it is not online. BACKU
P
can be performed by using the FILEGROUP or FILE clauses to restrict the
selection to include only online data.
BACKUP DATABASE is terminating abnormally.". Possible failure reasons:
Problems with the query, "ResultSet" property not set correctly, parameters
not set correctly, or connection not established correctly.
I'm not sure where to start looking.Try rebuilding or repopulating your full-text catalog. This is included in 2
005 backup and is most
probably your problem.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Matt M" <MattM@.discussions.microsoft.com> wrote in message
news:0222672A-806B-4EA0-BC3C-957050488734@.microsoft.com...
> Just upgraded from SQL 2000 to 2005 and I created a backup maintenance pla
n
> but I am getting the following error everytime the maintenance plan runs:
> Error number: -1073548784
> Executing the query "BACKUP DATABASE [Clarke_MSCRM] TO DISK = N'C:\\P
rogram
> Files\\Microsoft SQL
> Server\\MSSQL\\BACKUP\\Clarke_MSCRM\\Cla
rke_MSCRM_backup_200607270400.bak'
> WITH NOFORMAT, NOINIT, NAME = N'Clarke_MSCRM_backup_20060727040005', SKIP
,
> REWIND, NOUNLOAD, STATS = 10
> " failed with the following error: "The backup of the file or filegroup
> "sysft_ftcat_documentindex" is not permitted because it is not online. BAC
KUP
> can be performed by using the FILEGROUP or FILE clauses to restrict the
> selection to include only online data.
> BACKUP DATABASE is terminating abnormally.". Possible failure reasons:
> Problems with the query, "ResultSet" property not set correctly, parameter
s
> not set correctly, or connection not established correctly.
>
> I'm not sure where to start looking.
>|||I tried the Rebuild Index task and that didnt seem to work. How would I go
about repopulating?
"Tibor Karaszi" wrote:

> Try rebuilding or repopulating your full-text catalog. This is included in
2005 backup and is most
> probably your problem.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Matt M" <MattM@.discussions.microsoft.com> wrote in message
> news:0222672A-806B-4EA0-BC3C-957050488734@.microsoft.com...
>|||I'm not talking about regular indexes, I'm talking about full text indexes.
I don't do much
fulltext, so try at microsoft.public.sqlserver.fulltext.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Matt M" <MattM@.discussions.microsoft.com> wrote in message
news:82F9483C-93C4-48E9-B65B-D002AFF24700@.microsoft.com...[vbcol=seagreen]
>I tried the Rebuild Index task and that didnt seem to work. How would I go
> about repopulating?
> "Tibor Karaszi" wrote:
>|||I realized I misunderstood you after I posted. I looked into it and found
where to rebuild full text and it is running on a test box right now (Its a
slow machine so its taking a long time) I will try in production after hours
.
"Tibor Karaszi" wrote:

> I'm not talking about regular indexes, I'm talking about full text indexes
. I don't do much
> fulltext, so try at microsoft.public.sqlserver.fulltext.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Matt M" <MattM@.discussions.microsoft.com> wrote in message
> news:82F9483C-93C4-48E9-B65B-D002AFF24700@.microsoft.com...
>

file or filegroup not online? "sysft_ftcat_documentindex"

Just upgraded from SQL 2000 to 2005 and I created a backup maintenance plan
but I am getting the following error everytime the maintenance plan runs:
Error number: -1073548784
Executing the query "BACKUP DATABASE [Clarke_MSCRM] TO DISK = N'C:\\Program
Files\\Microsoft SQL
Server\\MSSQL\\BACKUP\\Clarke_MSCRM\\Clarke_MSCRM_backup_200607270400.bak'
WITH NOFORMAT, NOINIT, NAME = N'Clarke_MSCRM_backup_20060727040005', SKIP,
REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "The backup of the file or filegroup
"sysft_ftcat_documentindex" is not permitted because it is not online. BACKUP
can be performed by using the FILEGROUP or FILE clauses to restrict the
selection to include only online data.
BACKUP DATABASE is terminating abnormally.". Possible failure reasons:
Problems with the query, "ResultSet" property not set correctly, parameters
not set correctly, or connection not established correctly.
I'm not sure where to start looking.Try rebuilding or repopulating your full-text catalog. This is included in 2005 backup and is most
probably your problem.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Matt M" <MattM@.discussions.microsoft.com> wrote in message
news:0222672A-806B-4EA0-BC3C-957050488734@.microsoft.com...
> Just upgraded from SQL 2000 to 2005 and I created a backup maintenance plan
> but I am getting the following error everytime the maintenance plan runs:
> Error number: -1073548784
> Executing the query "BACKUP DATABASE [Clarke_MSCRM] TO DISK = N'C:\\Program
> Files\\Microsoft SQL
> Server\\MSSQL\\BACKUP\\Clarke_MSCRM\\Clarke_MSCRM_backup_200607270400.bak'
> WITH NOFORMAT, NOINIT, NAME = N'Clarke_MSCRM_backup_20060727040005', SKIP,
> REWIND, NOUNLOAD, STATS = 10
> " failed with the following error: "The backup of the file or filegroup
> "sysft_ftcat_documentindex" is not permitted because it is not online. BACKUP
> can be performed by using the FILEGROUP or FILE clauses to restrict the
> selection to include only online data.
> BACKUP DATABASE is terminating abnormally.". Possible failure reasons:
> Problems with the query, "ResultSet" property not set correctly, parameters
> not set correctly, or connection not established correctly.
>
> I'm not sure where to start looking.
>|||I tried the Rebuild Index task and that didnt seem to work. How would I go
about repopulating?
"Tibor Karaszi" wrote:
> Try rebuilding or repopulating your full-text catalog. This is included in 2005 backup and is most
> probably your problem.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Matt M" <MattM@.discussions.microsoft.com> wrote in message
> news:0222672A-806B-4EA0-BC3C-957050488734@.microsoft.com...
> > Just upgraded from SQL 2000 to 2005 and I created a backup maintenance plan
> > but I am getting the following error everytime the maintenance plan runs:
> >
> > Error number: -1073548784
> >
> > Executing the query "BACKUP DATABASE [Clarke_MSCRM] TO DISK = N'C:\\Program
> > Files\\Microsoft SQL
> > Server\\MSSQL\\BACKUP\\Clarke_MSCRM\\Clarke_MSCRM_backup_200607270400.bak'
> > WITH NOFORMAT, NOINIT, NAME = N'Clarke_MSCRM_backup_20060727040005', SKIP,
> > REWIND, NOUNLOAD, STATS = 10
> > " failed with the following error: "The backup of the file or filegroup
> > "sysft_ftcat_documentindex" is not permitted because it is not online. BACKUP
> > can be performed by using the FILEGROUP or FILE clauses to restrict the
> > selection to include only online data.
> > BACKUP DATABASE is terminating abnormally.". Possible failure reasons:
> > Problems with the query, "ResultSet" property not set correctly, parameters
> > not set correctly, or connection not established correctly.
> >
> >
> > I'm not sure where to start looking.
> >
> >
>|||I'm not talking about regular indexes, I'm talking about full text indexes. I don't do much
fulltext, so try at microsoft.public.sqlserver.fulltext.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Matt M" <MattM@.discussions.microsoft.com> wrote in message
news:82F9483C-93C4-48E9-B65B-D002AFF24700@.microsoft.com...
>I tried the Rebuild Index task and that didnt seem to work. How would I go
> about repopulating?
> "Tibor Karaszi" wrote:
>> Try rebuilding or repopulating your full-text catalog. This is included in 2005 backup and is
>> most
>> probably your problem.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Matt M" <MattM@.discussions.microsoft.com> wrote in message
>> news:0222672A-806B-4EA0-BC3C-957050488734@.microsoft.com...
>> > Just upgraded from SQL 2000 to 2005 and I created a backup maintenance plan
>> > but I am getting the following error everytime the maintenance plan runs:
>> >
>> > Error number: -1073548784
>> >
>> > Executing the query "BACKUP DATABASE [Clarke_MSCRM] TO DISK = N'C:\\Program
>> > Files\\Microsoft SQL
>> > Server\\MSSQL\\BACKUP\\Clarke_MSCRM\\Clarke_MSCRM_backup_200607270400.bak'
>> > WITH NOFORMAT, NOINIT, NAME = N'Clarke_MSCRM_backup_20060727040005', SKIP,
>> > REWIND, NOUNLOAD, STATS = 10
>> > " failed with the following error: "The backup of the file or filegroup
>> > "sysft_ftcat_documentindex" is not permitted because it is not online. BACKUP
>> > can be performed by using the FILEGROUP or FILE clauses to restrict the
>> > selection to include only online data.
>> > BACKUP DATABASE is terminating abnormally.". Possible failure reasons:
>> > Problems with the query, "ResultSet" property not set correctly, parameters
>> > not set correctly, or connection not established correctly.
>> >
>> >
>> > I'm not sure where to start looking.
>> >
>> >
>>|||I realized I misunderstood you after I posted. I looked into it and found
where to rebuild full text and it is running on a test box right now (Its a
slow machine so its taking a long time) I will try in production after hours.
"Tibor Karaszi" wrote:
> I'm not talking about regular indexes, I'm talking about full text indexes. I don't do much
> fulltext, so try at microsoft.public.sqlserver.fulltext.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Matt M" <MattM@.discussions.microsoft.com> wrote in message
> news:82F9483C-93C4-48E9-B65B-D002AFF24700@.microsoft.com...
> >I tried the Rebuild Index task and that didnt seem to work. How would I go
> > about repopulating?
> >
> > "Tibor Karaszi" wrote:
> >
> >> Try rebuilding or repopulating your full-text catalog. This is included in 2005 backup and is
> >> most
> >> probably your problem.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Matt M" <MattM@.discussions.microsoft.com> wrote in message
> >> news:0222672A-806B-4EA0-BC3C-957050488734@.microsoft.com...
> >> > Just upgraded from SQL 2000 to 2005 and I created a backup maintenance plan
> >> > but I am getting the following error everytime the maintenance plan runs:
> >> >
> >> > Error number: -1073548784
> >> >
> >> > Executing the query "BACKUP DATABASE [Clarke_MSCRM] TO DISK = N'C:\\Program
> >> > Files\\Microsoft SQL
> >> > Server\\MSSQL\\BACKUP\\Clarke_MSCRM\\Clarke_MSCRM_backup_200607270400.bak'
> >> > WITH NOFORMAT, NOINIT, NAME = N'Clarke_MSCRM_backup_20060727040005', SKIP,
> >> > REWIND, NOUNLOAD, STATS = 10
> >> > " failed with the following error: "The backup of the file or filegroup
> >> > "sysft_ftcat_documentindex" is not permitted because it is not online. BACKUP
> >> > can be performed by using the FILEGROUP or FILE clauses to restrict the
> >> > selection to include only online data.
> >> > BACKUP DATABASE is terminating abnormally.". Possible failure reasons:
> >> > Problems with the query, "ResultSet" property not set correctly, parameters
> >> > not set correctly, or connection not established correctly.
> >> >
> >> >
> >> > I'm not sure where to start looking.
> >> >
> >> >
> >>
> >>
>