Thursday, March 29, 2012
Filter Help Needed
Need some help with Filters in SQL Reporting 2000.
Have a table report setup with a DS going to a Store Proc. need to add a
filter that is a bit complex. Don't know which filter section I should be
putting this in but in all cases I've run into errors.
Basically when the data is brought back from my Store Proc, I need to filter
out the data according to the following:
IF CBalance < 0
THEN ((-1*TTLBalance) > ((-1*CBAlance)*0.25))
ELSE (TTLBalance > (CBalance*0.25))
I keep getting this error that the filter comparison fails and I should
check the data Types returned by the filter expression.
Please help...you might want to cast your fields as varchar in your query. This has
been the answer to many filter comparison problems I have had in the
past.
Eric wrote:
> Good Day to All,
> Need some help with Filters in SQL Reporting 2000.
> Have a table report setup with a DS going to a Store Proc. need to add a
> filter that is a bit complex. Don't know which filter section I should be
> putting this in but in all cases I've run into errors.
> Basically when the data is brought back from my Store Proc, I need to filter
> out the data according to the following:
> IF CBalance < 0
> THEN ((-1*TTLBalance) > ((-1*CBAlance)*0.25))
> ELSE (TTLBalance > (CBalance*0.25))
> I keep getting this error that the filter comparison fails and I should
> check the data Types returned by the filter expression.
> Please help...|||Thanks Topher that was it. Odd the filter section can't handle comparisons
other then varchar / string like values. I wonder if this carried over into
the newer version.
Thanks again!!!
"Topher" wrote:
> you might want to cast your fields as varchar in your query. This has
> been the answer to many filter comparison problems I have had in the
> past.
>
> Eric wrote:
> > Good Day to All,
> > Need some help with Filters in SQL Reporting 2000.
> > Have a table report setup with a DS going to a Store Proc. need to add a
> > filter that is a bit complex. Don't know which filter section I should be
> > putting this in but in all cases I've run into errors.
> >
> > Basically when the data is brought back from my Store Proc, I need to filter
> > out the data according to the following:
> > IF CBalance < 0
> > THEN ((-1*TTLBalance) > ((-1*CBAlance)*0.25))
> > ELSE (TTLBalance > (CBalance*0.25))
> >
> > I keep getting this error that the filter comparison fails and I should
> > check the data Types returned by the filter expression.
> >
> > Please help...
>
Filter clause
for replication. In SQL 2000, we had a lot of the filters setup using
an OR statement in the filterclause (ie. a.company = b.company or
a.subcompany = b.company), and had no problems with adding the filters.
In SQL 2005, creating these filters takes forever, if created at all.
Has anyone out there seen a problem like this?
Any help would be appreciated.
Thanks,
Amy Marshall
Can you define "takes forever" as well as "if created at all"? Are they not
being created? Are you receiving errors? What is happening?
As far as creating these en mass. if you are using the GUI and are stuck in
that world, then plan on spending a few days clicking through and setting
this stuff up. Instead, you can very easily setup the base replication
configuration, generate a script, and then add all of the filters into the
script, in a fraction of the time it takes to use the GUI.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
<marshallae@.bowater.com> wrote in message
news:1135868828.946674.289840@.g14g2000cwa.googlegr oups.com...
> We are moving to SQL 2005 Standard Edition are use numerouse filters
> for replication. In SQL 2000, we had a lot of the filters setup using
> an OR statement in the filterclause (ie. a.company = b.company or
> a.subcompany = b.company), and had no problems with adding the filters.
> In SQL 2005, creating these filters takes forever, if created at all.
> Has anyone out there seen a problem like this?
> Any help would be appreciated.
> Thanks,
> Amy Marshall
>
|||How many tables? Did you select the option to automatically generate
filters? This is very lengthy for large related tables in both SQL 2000 and
SQL 2005.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<marshallae@.bowater.com> wrote in message
news:1135868828.946674.289840@.g14g2000cwa.googlegr oups.com...
> We are moving to SQL 2005 Standard Edition are use numerouse filters
> for replication. In SQL 2000, we had a lot of the filters setup using
> an OR statement in the filterclause (ie. a.company = b.company or
> a.subcompany = b.company), and had no problems with adding the filters.
> In SQL 2005, creating these filters takes forever, if created at all.
> Has anyone out there seen a problem like this?
> Any help would be appreciated.
> Thanks,
> Amy Marshall
>
|||I have about 10 tables that use the 'OR' filter that links to one
table. What I did was script the package from SQL 2000 and ran it in
Query Analyzer on SQL 2005. If I take out the 'OR' statement, then the
filter will be created in less than a second. With the 'OR' statement,
I usually end up cancelling it after 5-10 minutes for each table with
that filter. (We were testing with the CTP Sept. version, and did not
have this issue...Could it be Standard vs. Enterprise?)
Thanks,
Amy
|||No, the edition doesn't matter. I can't reproduce this on the RTM bits.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
<marshallae@.bowater.com> wrote in message
news:1136292153.538075.219420@.g14g2000cwa.googlegr oups.com...
>I have about 10 tables that use the 'OR' filter that links to one
> table. What I did was script the package from SQL 2000 and ran it in
> Query Analyzer on SQL 2005. If I take out the 'OR' statement, then the
> filter will be created in less than a second. With the 'OR' statement,
> I usually end up cancelling it after 5-10 minutes for each table with
> that filter. (We were testing with the CTP Sept. version, and did not
> have this issue...Could it be Standard vs. Enterprise?)
> Thanks,
> Amy
>
Friday, March 23, 2012
Fileshare Data Driven subscription - Setting up report manager fil
PDFs to specific folders in Report Manager. I have setup a fileshare to which
I am pushing reports, but I am having trouble relating specific Report
Manager folders to the fileshares and there is little documentation about
this. Is there a simple way to push PDFs to Report Manager folders via
fileshare subscriptions?
Also, on a data driven subscription, I am setting up the table that will
read the specifications for the data driven subscription. How do a refer to
the PDF rendering method in the table - as "PDF" or " Adobe (PDF) file" or
something else? Also, what is the bit I should use to increment the file if
it already exists? The documentation on data driven subsciptions is really
not adequate to answer these questions.Incidentally, this question relates to Reporting Services 2005
"Mark" wrote:
> I am trying set up a data driven subscription that will render reports as
> PDFs to specific folders in Report Manager. I have setup a fileshare to which
> I am pushing reports, but I am having trouble relating specific Report
> Manager folders to the fileshares and there is little documentation about
> this. Is there a simple way to push PDFs to Report Manager folders via
> fileshare subscriptions?
> Also, on a data driven subscription, I am setting up the table that will
> read the specifications for the data driven subscription. How do a refer to
> the PDF rendering method in the table - as "PDF" or " Adobe (PDF) file" or
> something else? Also, what is the bit I should use to increment the file if
> it already exists? The documentation on data driven subsciptions is really
> not adequate to answer these questions.
>|||Hi Mark,
Not sure I understand third question, but you must provide a different
FileName paramter to the DDS for each report. Whatever logic you want.
Answer to second question is: PDF
As to the first question - I don't think that is possible. DDS can
output to fileshares, but the folders and files in those folders in RM
are controlledby the RS catalog and not accessible outside RM.
As a quick test, I uploaded a PDF file using RM and then searched the
RS Server for the pdf and it was not found. (So RS must be storing it
away somewhere on the server in a secured spot).
Are you sure you really want to do that anyway? The folders may get a
little crowded if you start storing PDF's there. You can, as another
option, store data with the report by using Snapshots.
If you do find a solution for stroing PDF's though, please post it!!
As a workaround, maybe|||Matt - Regarding your last comment, can I arrange the snapshots for the
reports into different folders? My understanding is that you can create a new
snapshot under the history tab for each report, but you cannot move the
history "snapshots" to other folders. Or can you?
"Matt" wrote:
> Hi Mark,
> Not sure I understand third question, but you must provide a different
> FileName paramter to the DDS for each report. Whatever logic you want.
>
> Answer to second question is: PDF
> As to the first question - I don't think that is possible. DDS can
> output to fileshares, but the folders and files in those folders in RM
> are controlledby the RS catalog and not accessible outside RM.
> As a quick test, I uploaded a PDF file using RM and then searched the
> RS Server for the pdf and it was not found. (So RS must be storing it
> away somewhere on the server in a secured spot).
> Are you sure you really want to do that anyway? The folders may get a
> little crowded if you start storing PDF's there. You can, as another
> option, store data with the report by using Snapshots.
> If you do find a solution for stroing PDF's though, please post it!!
> As a workaround, maybe
>
Wednesday, March 21, 2012
Filegroups question
Currently the setup is a single filegroup sql machine
with each company as a new database in a single system.
Would breaking the companies into thier own filegroups
be benefitial?
or perhaps creating a instance of SQL for each company be a better approach?
thoughts / ideas would be appreciated
thanks
tonyWould breaking the companies into thier own filegroups
be benefitial?
Only if those filegroups reside on different actual drives, you might gain the performance benefit of different companies not impacting eachother performance.
or perhaps creating a instance of SQL for each company be a better approach?
Only if you have given the customer sysadmin or someother sql server wide access which I would sincerely discourage. That way the differrent customers could not access eachothers stuff.|||Thanks for the reply
I appreciate it!
take care
tony
Monday, March 19, 2012
Filegroups and multi-processor threading improvements
parallel I/O is a mute issue if you setup a proper disk sub-system, at least
for small to medium sized databases.
But, it seems like everyone is skimming over the SMP benefits of filegroups,
if they exist. I came across one other posting referring to the number of
threads that SQL 2000 will use per filegroup.
Let me see if I understand this correctly? SQL 2000 will issue one thread
per filegroup for reads and writes. Thus if you have one large database on
a RAID 10 setup, I/O will be limited to one CPU thread. Granted disk I/O
should thread across both CPU's at the RAID controller level. If you create
two PRIMARY filegroups for the database, then you SQL 2000 will dish out two
threads for disk I/O thus using two of your CPU's. These will both be
issued to the RAID controller which should thread out your disk I/O once
again. So, if my understanding is correct, will there be a performance gain
by creating one filegroup per CPU?
Since in my case, we have two sets of disks RAID 10 and RAID 0, we already
our optimizing our disk I/O with tempbd and logs on the RAID 0. I know,
RAID 0 for logs, but this is a read-only SQL server so logs are irrelevant.
This also limits us from doing any filegroup optimizations such as placing
the indexes on another disk sub-system. So the only performance benefit we
are looking at is potential better SMP handling of disk I/O.
Thank You,
Kevin HammondHi Kevin,
I'm afraid your understanding is completely incorrect.
You do not have to have more files to get better IO performance, except when
using backup/restore. Multiple files are generally used for easier
management.
The access methods code is totally oblivious to the number of files in all
respects. Likewise, lazy-writing and checkpointing are both indifferent to
the number of files. SQL Server can have hundreds of outstanding read and/or
writes per file, not just one. More file does not equal more IO.
Please let me know if you have any further questions.
Thanks and regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kevin Hammond" <kghammond@.nrscorp.com> wrote in message
news:c8ak86$6m9$1@.grandcanyon.binc.net...
> I have read extensively on filegroups vs. RAID 5, 10 etc. Seems like
> parallel I/O is a mute issue if you setup a proper disk sub-system, at
least
> for small to medium sized databases.
> But, it seems like everyone is skimming over the SMP benefits of
filegroups,
> if they exist. I came across one other posting referring to the number of
> threads that SQL 2000 will use per filegroup.
> Let me see if I understand this correctly? SQL 2000 will issue one thread
> per filegroup for reads and writes. Thus if you have one large database
on
> a RAID 10 setup, I/O will be limited to one CPU thread. Granted disk I/O
> should thread across both CPU's at the RAID controller level. If you
create
> two PRIMARY filegroups for the database, then you SQL 2000 will dish out
two
> threads for disk I/O thus using two of your CPU's. These will both be
> issued to the RAID controller which should thread out your disk I/O once
> again. So, if my understanding is correct, will there be a performance
gain
> by creating one filegroup per CPU?
> Since in my case, we have two sets of disks RAID 10 and RAID 0, we already
> our optimizing our disk I/O with tempbd and logs on the RAID 0. I know,
> RAID 0 for logs, but this is a read-only SQL server so logs are
irrelevant.
> This also limits us from doing any filegroup optimizations such as placing
> the indexes on another disk sub-system. So the only performance benefit
we
> are looking at is potential better SMP handling of disk I/O.
> Thank You,
> Kevin Hammond
>
Filegroups and multi-processor threading improvements
parallel I/O is a mute issue if you setup a proper disk sub-system, at least
for small to medium sized databases.
But, it seems like everyone is skimming over the SMP benefits of filegroups,
if they exist. I came across one other posting referring to the number of
threads that SQL 2000 will use per filegroup.
Let me see if I understand this correctly? SQL 2000 will issue one thread
per filegroup for reads and writes. Thus if you have one large database on
a RAID 10 setup, I/O will be limited to one CPU thread. Granted disk I/O
should thread across both CPU's at the RAID controller level. If you create
two PRIMARY filegroups for the database, then you SQL 2000 will dish out two
threads for disk I/O thus using two of your CPU's. These will both be
issued to the RAID controller which should thread out your disk I/O once
again. So, if my understanding is correct, will there be a performance gain
by creating one filegroup per CPU?
Since in my case, we have two sets of disks RAID 10 and RAID 0, we already
our optimizing our disk I/O with tempbd and logs on the RAID 0. I know,
RAID 0 for logs, but this is a read-only SQL server so logs are irrelevant.
This also limits us from doing any filegroup optimizations such as placing
the indexes on another disk sub-system. So the only performance benefit we
are looking at is potential better SMP handling of disk I/O.
Thank You,
Kevin HammondHi Kevin,
I'm afraid your understanding is completely incorrect.
You do not have to have more files to get better IO performance, except when
using backup/restore. Multiple files are generally used for easier
management.
The access methods code is totally oblivious to the number of files in all
respects. Likewise, lazy-writing and checkpointing are both indifferent to
the number of files. SQL Server can have hundreds of outstanding read and/or
writes per file, not just one. More file does not equal more IO.
Please let me know if you have any further questions.
Thanks and regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kevin Hammond" <kghammond@.nrscorp.com> wrote in message
news:c8ak86$6m9$1@.grandcanyon.binc.net...
> I have read extensively on filegroups vs. RAID 5, 10 etc. Seems like
> parallel I/O is a mute issue if you setup a proper disk sub-system, at
least
> for small to medium sized databases.
> But, it seems like everyone is skimming over the SMP benefits of
filegroups,
> if they exist. I came across one other posting referring to the number of
> threads that SQL 2000 will use per filegroup.
> Let me see if I understand this correctly? SQL 2000 will issue one thread
> per filegroup for reads and writes. Thus if you have one large database
on
> a RAID 10 setup, I/O will be limited to one CPU thread. Granted disk I/O
> should thread across both CPU's at the RAID controller level. If you
create
> two PRIMARY filegroups for the database, then you SQL 2000 will dish out
two
> threads for disk I/O thus using two of your CPU's. These will both be
> issued to the RAID controller which should thread out your disk I/O once
> again. So, if my understanding is correct, will there be a performance
gain
> by creating one filegroup per CPU?
> Since in my case, we have two sets of disks RAID 10 and RAID 0, we already
> our optimizing our disk I/O with tempbd and logs on the RAID 0. I know,
> RAID 0 for logs, but this is a read-only SQL server so logs are
irrelevant.
> This also limits us from doing any filegroup optimizations such as placing
> the indexes on another disk sub-system. So the only performance benefit
we
> are looking at is potential better SMP handling of disk I/O.
> Thank You,
> Kevin Hammond
>
Filegroups and multi-processor threading improvements
parallel I/O is a mute issue if you setup a proper disk sub-system, at least
for small to medium sized databases.
But, it seems like everyone is skimming over the SMP benefits of filegroups,
if they exist. I came across one other posting referring to the number of
threads that SQL 2000 will use per filegroup.
Let me see if I understand this correctly? SQL 2000 will issue one thread
per filegroup for reads and writes. Thus if you have one large database on
a RAID 10 setup, I/O will be limited to one CPU thread. Granted disk I/O
should thread across both CPU's at the RAID controller level. If you create
two PRIMARY filegroups for the database, then you SQL 2000 will dish out two
threads for disk I/O thus using two of your CPU's. These will both be
issued to the RAID controller which should thread out your disk I/O once
again. So, if my understanding is correct, will there be a performance gain
by creating one filegroup per CPU?
Since in my case, we have two sets of disks RAID 10 and RAID 0, we already
our optimizing our disk I/O with tempbd and logs on the RAID 0. I know,
RAID 0 for logs, but this is a read-only SQL server so logs are irrelevant.
This also limits us from doing any filegroup optimizations such as placing
the indexes on another disk sub-system. So the only performance benefit we
are looking at is potential better SMP handling of disk I/O.
Thank You,
Kevin Hammond
Hi Kevin,
I'm afraid your understanding is completely incorrect.
You do not have to have more files to get better IO performance, except when
using backup/restore. Multiple files are generally used for easier
management.
The access methods code is totally oblivious to the number of files in all
respects. Likewise, lazy-writing and checkpointing are both indifferent to
the number of files. SQL Server can have hundreds of outstanding read and/or
writes per file, not just one. More file does not equal more IO.
Please let me know if you have any further questions.
Thanks and regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kevin Hammond" <kghammond@.nrscorp.com> wrote in message
news:c8ak86$6m9$1@.grandcanyon.binc.net...
> I have read extensively on filegroups vs. RAID 5, 10 etc. Seems like
> parallel I/O is a mute issue if you setup a proper disk sub-system, at
least
> for small to medium sized databases.
> But, it seems like everyone is skimming over the SMP benefits of
filegroups,
> if they exist. I came across one other posting referring to the number of
> threads that SQL 2000 will use per filegroup.
> Let me see if I understand this correctly? SQL 2000 will issue one thread
> per filegroup for reads and writes. Thus if you have one large database
on
> a RAID 10 setup, I/O will be limited to one CPU thread. Granted disk I/O
> should thread across both CPU's at the RAID controller level. If you
create
> two PRIMARY filegroups for the database, then you SQL 2000 will dish out
two
> threads for disk I/O thus using two of your CPU's. These will both be
> issued to the RAID controller which should thread out your disk I/O once
> again. So, if my understanding is correct, will there be a performance
gain
> by creating one filegroup per CPU?
> Since in my case, we have two sets of disks RAID 10 and RAID 0, we already
> our optimizing our disk I/O with tempbd and logs on the RAID 0. I know,
> RAID 0 for logs, but this is a read-only SQL server so logs are
irrelevant.
> This also limits us from doing any filegroup optimizations such as placing
> the indexes on another disk sub-system. So the only performance benefit
we
> are looking at is potential better SMP handling of disk I/O.
> Thank You,
> Kevin Hammond
>
Friday, March 9, 2012
Filegroup - data file question
data files.
We have a 60GB database. What would be the setup on a RAID with 5 disks?My question wasn't on which RAID to select. I understand that portion. My
question was filegroups and data files for my database?
I want to know the purpose of file groups when creating a database.
thanks for the help though.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OpxNjNrfDHA.2352@.TK2MSFTNGP12.phx.gbl...
> You still didn't say anything as to how you will use it, how many
> transaction etc. 50% writes is nothing to sneeze at but a lot depends on
> how large the updates are and how many per second. If your only doing a
> handful a second then it won't matter as much as if your doing 1000 a sec.
> A Raid 5 might get by on the lower end but if you are going to heavily use
> the system then you might want to consider a RAID 0+1 or 1+0.
Unfortunately
> you only have 5 disks and that would leave just a 4 disk Raid with only 2
> usable as far as size goes. You didn't say how large your drives are but
if
> they are only 36GB or so then this would not be enough space for a Raid
0+1
> or 1+0. Your only option may be to use Raid 5.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Richard King (BSM)" <google@.walkersca.com> wrote in message
> news:OcausAnfDHA.956@.TK2MSFTNGP09.phx.gbl...
> > 70%-50% Reads advantage
> >
> > OS will be on own drive as well as logs.
> >
> >
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:OpatO9lfDHA.2152@.tk2msftngp13.phx.gbl...
> > > How are you going to use it? How many transactions, writes vs reads,
> > large
> > > or small selects etc. Will the logs be on a separate drive array? Is
> the
> > > OS on a separate array as well?
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "Richard King (SCA)" <google@.walkersca.com> wrote in message
> > > news:OyaVo9kfDHA.3284@.tk2msftngp13.phx.gbl...
> > > > We just recieved a RAID setup and i would like to know the best
setup
> > for
> > > my
> > > > data files.
> > > >
> > > > We have a 60GB database. What would be the setup on a RAID with 5
> > disks?
> > > >
> > > >
> > >
> > >
> >
> >
>|||OK. Filegroups are mainly used to segregate data for one of two reasons.
One is for backup reasons. If you have a very large database you can do
backups at the filegroup level so you don't have to do it all at once.
Usually for terabyte range dbs. Another is that it allows you to place
database objects (tables and indexes) on different drives or drive array's.
In your case you will only have 1 array for the data and the db won't be
that large. You probably won't gain much by having multiple filegroups.
Again on larger db's and very heavy usage one it may be beneficial to have
multiple files to allow lots of parallel threads to read them at once. But
you generally need the higher level hardware to take advantage of that.
BooksOnline has some more details on Files and Filegroups that you may want
to take a look at.
--
Andrew J. Kelly
SQL Server MVP
"Richard King (BSM)" <google@.walkersca.com> wrote in message
news:OYe6MrwfDHA.616@.TK2MSFTNGP11.phx.gbl...
> My question wasn't on which RAID to select. I understand that portion.
My
> question was filegroups and data files for my database?
> I want to know the purpose of file groups when creating a database.
> thanks for the help though.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OpxNjNrfDHA.2352@.TK2MSFTNGP12.phx.gbl...
> > You still didn't say anything as to how you will use it, how many
> > transaction etc. 50% writes is nothing to sneeze at but a lot depends
on
> > how large the updates are and how many per second. If your only doing a
> > handful a second then it won't matter as much as if your doing 1000 a
sec.
> > A Raid 5 might get by on the lower end but if you are going to heavily
use
> > the system then you might want to consider a RAID 0+1 or 1+0.
> Unfortunately
> > you only have 5 disks and that would leave just a 4 disk Raid with only
2
> > usable as far as size goes. You didn't say how large your drives are
but
> if
> > they are only 36GB or so then this would not be enough space for a Raid
> 0+1
> > or 1+0. Your only option may be to use Raid 5.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Richard King (BSM)" <google@.walkersca.com> wrote in message
> > news:OcausAnfDHA.956@.TK2MSFTNGP09.phx.gbl...
> > > 70%-50% Reads advantage
> > >
> > > OS will be on own drive as well as logs.
> > >
> > >
> > >
> > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > news:OpatO9lfDHA.2152@.tk2msftngp13.phx.gbl...
> > > > How are you going to use it? How many transactions, writes vs
reads,
> > > large
> > > > or small selects etc. Will the logs be on a separate drive array?
Is
> > the
> > > > OS on a separate array as well?
> > > >
> > > > --
> > > >
> > > > Andrew J. Kelly
> > > > SQL Server MVP
> > > >
> > > >
> > > > "Richard King (SCA)" <google@.walkersca.com> wrote in message
> > > > news:OyaVo9kfDHA.3284@.tk2msftngp13.phx.gbl...
> > > > > We just recieved a RAID setup and i would like to know the best
> setup
> > > for
> > > > my
> > > > > data files.
> > > > >
> > > > > We have a 60GB database. What would be the setup on a RAID with 5
> > > disks?
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
Wednesday, March 7, 2012
File system rights
I have setup a test IIS box and would like to use that as my development platform. I have installed sql express 2005 for my database on the box as well.
I can setup new pages on the file server just fine.. But when I try and setup a login page and use the asp.net configuration web page to setup security which create a user DB it will not let me create it...
I get the following:
The following message may help in diagnosing the problem:An error occurred during the execution of the SQL file 'InstallCommon.sql'. The SQL error number is 5110 and the SqlException message is: The file "J:\INETPUB\NBSRECAP\APP_DATA\ASPNETDB_TMP.MDF" is on a network path that is not supported for database files. CREATE DATABASE failed. Some file names listed could not be created. Check related errors. Creating the ASPNETDB_4a9331c5903148ca92e3ffbee7d29976 database...
Can someone let me know.
Are you trying to create a database on a network path? This is not supported by default, however you can open a trace flag to allow this:dbcc traceon(1807,-1)
go
create database testUNC on (name='testUNC_data',
filename='\\iori\xxx\testUNC.mdf')
log on (name='testUNC_log',
filename='\\iori\xxx\testUNC.ldf')
Sunday, February 26, 2012
file share subscription problem
file system, but it is failing with Logon failure: unknown user name
or bad password --> System.Runtime.InteropServices.COMException
(0x8007052E):
I'm supplying the correct username and password to access the
directory, and I'm specifying the unc path for the AS/400 correctly
(\\myas400\root\qdls\ftp). The reason I know this is because I am
able to go into Windows Explorer and type in the exact same UNC path,
then supply the username and password when prompted, and I can get in
just fine. I can also copy files back and forth, etc.
Any ideas? This looks like a possible bug to me. My guess is that
nobody's tried rendering a file share subscription to an AS/400
before, so it's probably never been tested.
I guess as a workaround I can render the report to a microsoft file
system and then ftp it over to the AS/400, but I really think this
should work as advertised. That's a lot of extra hoop jumping.This sounds like a security permission issue. Can you try the following: the
RS windows service is running under a certain account. Make sure this
account also has permissions to access the AS/400 network share.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"dcp" <dcp12345678@.yahoo.com> wrote in message
news:1b8617a8.0501041353.60da6aa0@.posting.google.com...
> I've setup a file share subscription to render a report to an AS/400
> file system, but it is failing with Logon failure: unknown user name
> or bad password --> System.Runtime.InteropServices.COMException
> (0x8007052E):
> I'm supplying the correct username and password to access the
> directory, and I'm specifying the unc path for the AS/400 correctly
> (\\myas400\root\qdls\ftp). The reason I know this is because I am
> able to go into Windows Explorer and type in the exact same UNC path,
> then supply the username and password when prompted, and I can get in
> just fine. I can also copy files back and forth, etc.
> Any ideas? This looks like a possible bug to me. My guess is that
> nobody's tried rendering a file share subscription to an AS/400
> before, so it's probably never been tested.
> I guess as a workaround I can render the report to a microsoft file
> system and then ftp it over to the AS/400, but I really think this
> should work as advertised. That's a lot of extra hoop jumping.
File Rotation
I currently have a job setup to export a view from a MS
SQL 2000 database to a CSV file on a network drive..
Let's say the filename is: filename.csv
The job runs every 10minutes, and I need it to save to
different file names for 7 days, then start back..
day 1: filename1.csv ..10minutes later would be
filename2.csv etc...
After 7 days it would go back to filename1.csv..
I hope I'm being clear here but I'm sure someone gets the
idea.. anyone has a tip?Guillaume
How about using the day of the week as part of the
filename and overwriting if the file name exists. ie
filename-mon.csv, filename-tue.csv etc or you could just
set up a small (one row, one column) table on your
database where you keep a number or some other identifier
to append to the file name. If you start with '1' as your
initial value, add 1 each day and when it gets to 8 reset
to 1. Filename1.csv, filename2.csv etc
Hope this helps
Regards
John