I have a crystal report that I have been asked to modify for a new process.
This report will take a look at our scheduling system and display which Dies are needed for each order. The Problem I am running into is I need a way to be able to denote that a Die is at another operation if the die number is previously listed in the report. This would be fine if I could sort by Die number, but the report has to be sorted by time so our Die room staff knows when a die is due to a machine and are able to just run through the list without having to search through the report for the next order.
This report is currently grouped by run date (so we can keep shifts straight) and sorted by Run Start Time and then by machine number.
This is the order is has to be in, and I cannot deviate from that.
Attached is an example of the current layout of the report
My issue is that I need some way to Denote that the die for the last order 278381-1 is at another machine (the First 2 orders 278385-1) so our Die Room staff are not searching for the Die on Rack 51 when they go to get the dies for the the last order.
I have thought long and hard about this, and I cannot figure out a way to get through this issue without reordering the report, which I cant do.
Any assistance or direction would be greatly appreciatedProblem has been resolved
The solution involved creating a Sub-Report and filtering out the extra records that did not match and then adding a Formatting to the Field to denote a Duplicate record.
Showing posts with label multiple. Show all posts
Showing posts with label multiple. Show all posts
Friday, March 30, 2012
Mapping table problem
HI guys
I need to put multiple coniditions on my table based on some combinations.
Here is my main table-
Here is my mapping table-
I have to add an additional column called 'percentage' in my main table by refering the mapping table.
For ex for a particular practitioner, Locum flag is 'Yes', nocount is 1 and yes count is 1, then percentage should be '0.25'
How should I do this?
So, when you are adding data to your main table, why not just add a chunk of code that would calculate what the percentage would be and keep the mapping table 'on paper' ? (And then store the result)|||hi Tas_CRO
How do I add that chunk of code to my main table?
That is probably I need to know
Thanks
Mapping table problem
HI guys
I need to put multiple coniditions on my table based on some combinations.
Here is my main table-
Here is my mapping table-
I have to add an additional column called 'percentage' in my main table by refering the mapping table.
For ex for a particular practitioner, Locum flag is 'Yes', nocount is 1 and yes count is 1, then percentage should be '0.25'
How should I do this?
So, when you are adding data to your main table, why not just add a chunk of code that would calculate what the percentage would be and keep the mapping table 'on paper' ? (And then store the result)|||
hi Tas_CRO
How do I add that chunk of code to my main table?
That is probably I need to know
Thanks
Wednesday, March 28, 2012
Mapping multiple elements to the same table
I have a huge 1+GB xml file that I'd like to bulk load into a single staging
table in SQL Server. The xml file has subelements that need to be loaded
into the same table as the parent element. For example:
<Customer>
<Address>
</Address>
<ContactInfo>
</ContactInfo>
</Customer>
I cannot seem to get an XSD that will load all the data into 1 table. Also
because of the size of the XML file, performing an XSLT transformation on the
file is not possible. What are my options? Is it possible to map
subelements to the same table as the containing element?
Could you map them to two different views of the same table?
Note also, that the mapping kind of expects that you give a relationship
annotation when you map parents and children. What is the error message or
behaviour that you are getting?
Best regards
Michael
"Mark Weber" <Mark Weber@.discussions.microsoft.com> wrote in message
news:14D8B400-733C-4080-8888-BD3551C6AD09@.microsoft.com...
>I have a huge 1+GB xml file that I'd like to bulk load into a single
>staging
> table in SQL Server. The xml file has subelements that need to be loaded
> into the same table as the parent element. For example:
> <Customer>
> <Address>
> </Address>
> <ContactInfo>
> </ContactInfo>
> </Customer>
> I cannot seem to get an XSD that will load all the data into 1 table.
> Also
> because of the size of the XML file, performing an XSLT transformation on
> the
> file is not possible. What are my options? Is it possible to map
> subelements to the same table as the containing element?
table in SQL Server. The xml file has subelements that need to be loaded
into the same table as the parent element. For example:
<Customer>
<Address>
</Address>
<ContactInfo>
</ContactInfo>
</Customer>
I cannot seem to get an XSD that will load all the data into 1 table. Also
because of the size of the XML file, performing an XSLT transformation on the
file is not possible. What are my options? Is it possible to map
subelements to the same table as the containing element?
Could you map them to two different views of the same table?
Note also, that the mapping kind of expects that you give a relationship
annotation when you map parents and children. What is the error message or
behaviour that you are getting?
Best regards
Michael
"Mark Weber" <Mark Weber@.discussions.microsoft.com> wrote in message
news:14D8B400-733C-4080-8888-BD3551C6AD09@.microsoft.com...
>I have a huge 1+GB xml file that I'd like to bulk load into a single
>staging
> table in SQL Server. The xml file has subelements that need to be loaded
> into the same table as the parent element. For example:
> <Customer>
> <Address>
> </Address>
> <ContactInfo>
> </ContactInfo>
> </Customer>
> I cannot seem to get an XSD that will load all the data into 1 table.
> Also
> because of the size of the XML file, performing an XSLT transformation on
> the
> file is not possible. What are my options? Is it possible to map
> subelements to the same table as the containing element?
Mapping multiple elements to the same table
I have a huge 1+GB xml file that I'd like to bulk load into a single staging
table in SQL Server. The xml file has subelements that need to be loaded
into the same table as the parent element. For example:
<Customer>
<Address>
</Address>
<ContactInfo>
</ContactInfo>
</Customer>
I cannot seem to get an XSD that will load all the data into 1 table. Also
because of the size of the XML file, performing an XSLT transformation on th
e
file is not possible. What are my options? Is it possible to map
subelements to the same table as the containing element?Could you map them to two different views of the same table?
Note also, that the mapping kind of expects that you give a relationship
annotation when you map parents and children. What is the error message or
behaviour that you are getting?
Best regards
Michael
"Mark Weber" <Mark Weber@.discussions.microsoft.com> wrote in message
news:14D8B400-733C-4080-8888-BD3551C6AD09@.microsoft.com...
>I have a huge 1+GB xml file that I'd like to bulk load into a single
>staging
> table in SQL Server. The xml file has subelements that need to be loaded
> into the same table as the parent element. For example:
> <Customer>
> <Address>
> </Address>
> <ContactInfo>
> </ContactInfo>
> </Customer>
> I cannot seem to get an XSD that will load all the data into 1 table.
> Also
> because of the size of the XML file, performing an XSLT transformation on
> the
> file is not possible. What are my options? Is it possible to map
> subelements to the same table as the containing element?sql
table in SQL Server. The xml file has subelements that need to be loaded
into the same table as the parent element. For example:
<Customer>
<Address>
</Address>
<ContactInfo>
</ContactInfo>
</Customer>
I cannot seem to get an XSD that will load all the data into 1 table. Also
because of the size of the XML file, performing an XSLT transformation on th
e
file is not possible. What are my options? Is it possible to map
subelements to the same table as the containing element?Could you map them to two different views of the same table?
Note also, that the mapping kind of expects that you give a relationship
annotation when you map parents and children. What is the error message or
behaviour that you are getting?
Best regards
Michael
"Mark Weber" <Mark Weber@.discussions.microsoft.com> wrote in message
news:14D8B400-733C-4080-8888-BD3551C6AD09@.microsoft.com...
>I have a huge 1+GB xml file that I'd like to bulk load into a single
>staging
> table in SQL Server. The xml file has subelements that need to be loaded
> into the same table as the parent element. For example:
> <Customer>
> <Address>
> </Address>
> <ContactInfo>
> </ContactInfo>
> </Customer>
> I cannot seem to get an XSD that will load all the data into 1 table.
> Also
> because of the size of the XML file, performing an XSLT transformation on
> the
> file is not possible. What are my options? Is it possible to map
> subelements to the same table as the containing element?sql
Monday, March 26, 2012
Map One generic Input column to multiple Destination column
I have a stored proc I am updating in an OLEDB Command from the results of a Transform Script Component. The Stored Proc has over 65 input parameters, most of them have a NULL passed in. I dont want to create output columns in the Transform Script Component for all of them to map them from the "Available Input Columns" to "Available Destination Columns".
I want to create 3 or 4 generic Output columns for their data type - say IntegerOutput (datatype Int), DateTimeOut (datatype datetime) and so on. The I want to map these generic columns in the OLEDB Command as Available Input Columns" to multiple "Available Destination Columns" - wherever the datatype matches the input column.
But the OLEDB Command Column Mappings let me map One to One only. This will create a huge and unnecessary workload for me to develop and maintain - when I tell you I have 3 such stored procedures, all of whose interfaces are exactly same and for which I can create similar Output columns in the Transform Script Component.
So how do I go about doing this the smart way?
thanks in advance!
Hi,
You can use "Copy Column" transformation component to copy one input column to multiple output columns. If you have to perform some computing between original and new columns, you can use "Derive Column" transformation.
Jean-Pierre Riehl
http://blog.djeepy1.net
http://www.bewise.fr
|||Sorry not very elegant, this is more work than creating all the output columns one by one. I want to create one DataType_NULL Column which I want to reuse to map to the destination columns.|||For what you are describing I would probably just call the stored procedure from inside the script component. As you have seen, the OLE Command doesn't really support this, so script, Copy Column, or Derived Column are the only way to do this that I am aware of.
Wednesday, March 21, 2012
Many databases on one or multiple SQL2000 servers
Hi there. We currently have an application which requires separate database
s
for each "project" we work on. We are in the process of replacing our 2 SQL
2000 servers and are unsure whether to purchase 1 or 2 servers. Previously,
we implemented with 2 servers due to performance reasons. That was back on
SQL 7 though.
We have approximately 400 databases which range in size from .5GB to about
3GB. These cannot be collapsed into fewer databases. SQL2005 is not an
option at this time (gotta love software companies that don't keep up with
the times!
So, do I get 2 servers or a single (larger in processor and memory) server?
--John J. Berlo
Lend Lease Corportation
Collaboration TechnologiesHi John,
I have found that there are more variables involved in a decision such as
this. How many users, the number of concurrent queries, the design of the
queries, etc., all play a big factor in how much processing power will be
required.
The number of databases is higher than normal, but not unheard of. Our
accounting system currently requires us to operate a server with over 900
databases. I am running this on a single server without issue. The
databases, though numerous, are well designed and support our 200 users with
an acceptable level of performance. The hardware is a 4-way server with 6GB
of RAM and SQL 2000 Enterprise Edition. The disk array is an IBM SAN via 2G
B
fiber.
My guess would be that disk and RAM could be your biggest true enemy in such
an implementation. If you can buy a decent server with 6GB or so of RAM and
a good disk array with quite a few spindles, say 20, you should be able to
support up to 200 - 300 users fairly well.
Hope this helps... also remember, there are a lot of variables and this is
just my "professional" opinion based on the limited information you gave us.
Bryan
"John J. Berlo" wrote:
> Hi there. We currently have an application which requires separate databa
ses
> for each "project" we work on. We are in the process of replacing our 2 S
QL
> 2000 servers and are unsure whether to purchase 1 or 2 servers. Previousl
y,
> we implemented with 2 servers due to performance reasons. That was back o
n
> SQL 7 though.
> We have approximately 400 databases which range in size from .5GB to about
> 3GB. These cannot be collapsed into fewer databases. SQL2005 is not an
> option at this time (gotta love software companies that don't keep up with
> the times!
> So, do I get 2 servers or a single (larger in processor and memory) server
?
> --John J. Berlo
> Lend Lease Corportation
> Collaboration Technologies|||Brian,
Thanks for the information. On our current servers, we are seeing
approximately 650 concurrent users. Not an enourmous amount, but one that
concerns us whether a single server is the best way to go.
If money was no object, I would do a 2-server active-active cluster, but SQL
Enterprise is a large jump in price than standard. So, the situation as I
see it leaves us with 2 choices:
1) Move to a single 2003 Server Enterprise server with 6-8GB RAM and 2-dual
core processors
-or-
2) Stay on 2 2003 Server standard servers with 4GB RAM and a single
dual-core processor
(both with SQL standard licenses and hooked to a very robust SAN).
Thoughts?
--John J. Berlo
Lend Lease Corportation
Collaboration Technologies
"Bryan Ivie" wrote:
[vbcol=seagreen]
> Hi John,
> I have found that there are more variables involved in a decision such as
> this. How many users, the number of concurrent queries, the design of the
> queries, etc., all play a big factor in how much processing power will be
> required.
> The number of databases is higher than normal, but not unheard of. Our
> accounting system currently requires us to operate a server with over 900
> databases. I am running this on a single server without issue. The
> databases, though numerous, are well designed and support our 200 users wi
th
> an acceptable level of performance. The hardware is a 4-way server with 6
GB
> of RAM and SQL 2000 Enterprise Edition. The disk array is an IBM SAN via
2GB
> fiber.
> My guess would be that disk and RAM could be your biggest true enemy in su
ch
> an implementation. If you can buy a decent server with 6GB or so of RAM a
nd
> a good disk array with quite a few spindles, say 20, you should be able to
> support up to 200 - 300 users fairly well.
> Hope this helps... also remember, there are a lot of variables and this i
s
> just my "professional" opinion based on the limited information you gave u
s.
> Bryan
>
> "John J. Berlo" wrote:
>
s
for each "project" we work on. We are in the process of replacing our 2 SQL
2000 servers and are unsure whether to purchase 1 or 2 servers. Previously,
we implemented with 2 servers due to performance reasons. That was back on
SQL 7 though.
We have approximately 400 databases which range in size from .5GB to about
3GB. These cannot be collapsed into fewer databases. SQL2005 is not an
option at this time (gotta love software companies that don't keep up with
the times!
So, do I get 2 servers or a single (larger in processor and memory) server?
--John J. Berlo
Lend Lease Corportation
Collaboration TechnologiesHi John,
I have found that there are more variables involved in a decision such as
this. How many users, the number of concurrent queries, the design of the
queries, etc., all play a big factor in how much processing power will be
required.
The number of databases is higher than normal, but not unheard of. Our
accounting system currently requires us to operate a server with over 900
databases. I am running this on a single server without issue. The
databases, though numerous, are well designed and support our 200 users with
an acceptable level of performance. The hardware is a 4-way server with 6GB
of RAM and SQL 2000 Enterprise Edition. The disk array is an IBM SAN via 2G
B
fiber.
My guess would be that disk and RAM could be your biggest true enemy in such
an implementation. If you can buy a decent server with 6GB or so of RAM and
a good disk array with quite a few spindles, say 20, you should be able to
support up to 200 - 300 users fairly well.
Hope this helps... also remember, there are a lot of variables and this is
just my "professional" opinion based on the limited information you gave us.
Bryan
"John J. Berlo" wrote:
> Hi there. We currently have an application which requires separate databa
ses
> for each "project" we work on. We are in the process of replacing our 2 S
QL
> 2000 servers and are unsure whether to purchase 1 or 2 servers. Previousl
y,
> we implemented with 2 servers due to performance reasons. That was back o
n
> SQL 7 though.
> We have approximately 400 databases which range in size from .5GB to about
> 3GB. These cannot be collapsed into fewer databases. SQL2005 is not an
> option at this time (gotta love software companies that don't keep up with
> the times!
> So, do I get 2 servers or a single (larger in processor and memory) server
?
> --John J. Berlo
> Lend Lease Corportation
> Collaboration Technologies|||Brian,
Thanks for the information. On our current servers, we are seeing
approximately 650 concurrent users. Not an enourmous amount, but one that
concerns us whether a single server is the best way to go.
If money was no object, I would do a 2-server active-active cluster, but SQL
Enterprise is a large jump in price than standard. So, the situation as I
see it leaves us with 2 choices:
1) Move to a single 2003 Server Enterprise server with 6-8GB RAM and 2-dual
core processors
-or-
2) Stay on 2 2003 Server standard servers with 4GB RAM and a single
dual-core processor
(both with SQL standard licenses and hooked to a very robust SAN).
Thoughts?
--John J. Berlo
Lend Lease Corportation
Collaboration Technologies
"Bryan Ivie" wrote:
[vbcol=seagreen]
> Hi John,
> I have found that there are more variables involved in a decision such as
> this. How many users, the number of concurrent queries, the design of the
> queries, etc., all play a big factor in how much processing power will be
> required.
> The number of databases is higher than normal, but not unheard of. Our
> accounting system currently requires us to operate a server with over 900
> databases. I am running this on a single server without issue. The
> databases, though numerous, are well designed and support our 200 users wi
th
> an acceptable level of performance. The hardware is a 4-way server with 6
GB
> of RAM and SQL 2000 Enterprise Edition. The disk array is an IBM SAN via
2GB
> fiber.
> My guess would be that disk and RAM could be your biggest true enemy in su
ch
> an implementation. If you can buy a decent server with 6GB or so of RAM a
nd
> a good disk array with quite a few spindles, say 20, you should be able to
> support up to 200 - 300 users fairly well.
> Hope this helps... also remember, there are a lot of variables and this i
s
> just my "professional" opinion based on the limited information you gave u
s.
> Bryan
>
> "John J. Berlo" wrote:
>
Many databases on one or multiple SQL2000 servers
Hi there. We currently have an application which requires separate databases
for each "project" we work on. We are in the process of replacing our 2 SQL
2000 servers and are unsure whether to purchase 1 or 2 servers. Previously,
we implemented with 2 servers due to performance reasons. That was back on
SQL 7 though.
We have approximately 400 databases which range in size from .5GB to about
3GB. These cannot be collapsed into fewer databases. SQL2005 is not an
option at this time (gotta love software companies that don't keep up with
the times! :)
So, do I get 2 servers or a single (larger in processor and memory) server?
--John J. Berlo
Lend Lease Corportation
Collaboration TechnologiesHi John,
I have found that there are more variables involved in a decision such as
this. How many users, the number of concurrent queries, the design of the
queries, etc., all play a big factor in how much processing power will be
required.
The number of databases is higher than normal, but not unheard of. Our
accounting system currently requires us to operate a server with over 900
databases. I am running this on a single server without issue. The
databases, though numerous, are well designed and support our 200 users with
an acceptable level of performance. The hardware is a 4-way server with 6GB
of RAM and SQL 2000 Enterprise Edition. The disk array is an IBM SAN via 2GB
fiber.
My guess would be that disk and RAM could be your biggest true enemy in such
an implementation. If you can buy a decent server with 6GB or so of RAM and
a good disk array with quite a few spindles, say 20, you should be able to
support up to 200 - 300 users fairly well.
Hope this helps... also remember, there are a lot of variables and this is
just my "professional" opinion based on the limited information you gave us.
Bryan
"John J. Berlo" wrote:
> Hi there. We currently have an application which requires separate databases
> for each "project" we work on. We are in the process of replacing our 2 SQL
> 2000 servers and are unsure whether to purchase 1 or 2 servers. Previously,
> we implemented with 2 servers due to performance reasons. That was back on
> SQL 7 though.
> We have approximately 400 databases which range in size from .5GB to about
> 3GB. These cannot be collapsed into fewer databases. SQL2005 is not an
> option at this time (gotta love software companies that don't keep up with
> the times! :)
> So, do I get 2 servers or a single (larger in processor and memory) server?
> --John J. Berlo
> Lend Lease Corportation
> Collaboration Technologies|||Brian,
Thanks for the information. On our current servers, we are seeing
approximately 650 concurrent users. Not an enourmous amount, but one that
concerns us whether a single server is the best way to go.
If money was no object, I would do a 2-server active-active cluster, but SQL
Enterprise is a large jump in price than standard. So, the situation as I
see it leaves us with 2 choices:
1) Move to a single 2003 Server Enterprise server with 6-8GB RAM and 2-dual
core processors
-or-
2) Stay on 2 2003 Server standard servers with 4GB RAM and a single
dual-core processor
(both with SQL standard licenses and hooked to a very robust SAN).
Thoughts?
--John J. Berlo
Lend Lease Corportation
Collaboration Technologies
"Bryan Ivie" wrote:
> Hi John,
> I have found that there are more variables involved in a decision such as
> this. How many users, the number of concurrent queries, the design of the
> queries, etc., all play a big factor in how much processing power will be
> required.
> The number of databases is higher than normal, but not unheard of. Our
> accounting system currently requires us to operate a server with over 900
> databases. I am running this on a single server without issue. The
> databases, though numerous, are well designed and support our 200 users with
> an acceptable level of performance. The hardware is a 4-way server with 6GB
> of RAM and SQL 2000 Enterprise Edition. The disk array is an IBM SAN via 2GB
> fiber.
> My guess would be that disk and RAM could be your biggest true enemy in such
> an implementation. If you can buy a decent server with 6GB or so of RAM and
> a good disk array with quite a few spindles, say 20, you should be able to
> support up to 200 - 300 users fairly well.
> Hope this helps... also remember, there are a lot of variables and this is
> just my "professional" opinion based on the limited information you gave us.
> Bryan
>
> "John J. Berlo" wrote:
> > Hi there. We currently have an application which requires separate databases
> > for each "project" we work on. We are in the process of replacing our 2 SQL
> > 2000 servers and are unsure whether to purchase 1 or 2 servers. Previously,
> > we implemented with 2 servers due to performance reasons. That was back on
> > SQL 7 though.
> >
> > We have approximately 400 databases which range in size from .5GB to about
> > 3GB. These cannot be collapsed into fewer databases. SQL2005 is not an
> > option at this time (gotta love software companies that don't keep up with
> > the times! :)
> >
> > So, do I get 2 servers or a single (larger in processor and memory) server?
> >
> > --John J. Berlo
> > Lend Lease Corportation
> > Collaboration Technologies
for each "project" we work on. We are in the process of replacing our 2 SQL
2000 servers and are unsure whether to purchase 1 or 2 servers. Previously,
we implemented with 2 servers due to performance reasons. That was back on
SQL 7 though.
We have approximately 400 databases which range in size from .5GB to about
3GB. These cannot be collapsed into fewer databases. SQL2005 is not an
option at this time (gotta love software companies that don't keep up with
the times! :)
So, do I get 2 servers or a single (larger in processor and memory) server?
--John J. Berlo
Lend Lease Corportation
Collaboration TechnologiesHi John,
I have found that there are more variables involved in a decision such as
this. How many users, the number of concurrent queries, the design of the
queries, etc., all play a big factor in how much processing power will be
required.
The number of databases is higher than normal, but not unheard of. Our
accounting system currently requires us to operate a server with over 900
databases. I am running this on a single server without issue. The
databases, though numerous, are well designed and support our 200 users with
an acceptable level of performance. The hardware is a 4-way server with 6GB
of RAM and SQL 2000 Enterprise Edition. The disk array is an IBM SAN via 2GB
fiber.
My guess would be that disk and RAM could be your biggest true enemy in such
an implementation. If you can buy a decent server with 6GB or so of RAM and
a good disk array with quite a few spindles, say 20, you should be able to
support up to 200 - 300 users fairly well.
Hope this helps... also remember, there are a lot of variables and this is
just my "professional" opinion based on the limited information you gave us.
Bryan
"John J. Berlo" wrote:
> Hi there. We currently have an application which requires separate databases
> for each "project" we work on. We are in the process of replacing our 2 SQL
> 2000 servers and are unsure whether to purchase 1 or 2 servers. Previously,
> we implemented with 2 servers due to performance reasons. That was back on
> SQL 7 though.
> We have approximately 400 databases which range in size from .5GB to about
> 3GB. These cannot be collapsed into fewer databases. SQL2005 is not an
> option at this time (gotta love software companies that don't keep up with
> the times! :)
> So, do I get 2 servers or a single (larger in processor and memory) server?
> --John J. Berlo
> Lend Lease Corportation
> Collaboration Technologies|||Brian,
Thanks for the information. On our current servers, we are seeing
approximately 650 concurrent users. Not an enourmous amount, but one that
concerns us whether a single server is the best way to go.
If money was no object, I would do a 2-server active-active cluster, but SQL
Enterprise is a large jump in price than standard. So, the situation as I
see it leaves us with 2 choices:
1) Move to a single 2003 Server Enterprise server with 6-8GB RAM and 2-dual
core processors
-or-
2) Stay on 2 2003 Server standard servers with 4GB RAM and a single
dual-core processor
(both with SQL standard licenses and hooked to a very robust SAN).
Thoughts?
--John J. Berlo
Lend Lease Corportation
Collaboration Technologies
"Bryan Ivie" wrote:
> Hi John,
> I have found that there are more variables involved in a decision such as
> this. How many users, the number of concurrent queries, the design of the
> queries, etc., all play a big factor in how much processing power will be
> required.
> The number of databases is higher than normal, but not unheard of. Our
> accounting system currently requires us to operate a server with over 900
> databases. I am running this on a single server without issue. The
> databases, though numerous, are well designed and support our 200 users with
> an acceptable level of performance. The hardware is a 4-way server with 6GB
> of RAM and SQL 2000 Enterprise Edition. The disk array is an IBM SAN via 2GB
> fiber.
> My guess would be that disk and RAM could be your biggest true enemy in such
> an implementation. If you can buy a decent server with 6GB or so of RAM and
> a good disk array with quite a few spindles, say 20, you should be able to
> support up to 200 - 300 users fairly well.
> Hope this helps... also remember, there are a lot of variables and this is
> just my "professional" opinion based on the limited information you gave us.
> Bryan
>
> "John J. Berlo" wrote:
> > Hi there. We currently have an application which requires separate databases
> > for each "project" we work on. We are in the process of replacing our 2 SQL
> > 2000 servers and are unsure whether to purchase 1 or 2 servers. Previously,
> > we implemented with 2 servers due to performance reasons. That was back on
> > SQL 7 though.
> >
> > We have approximately 400 databases which range in size from .5GB to about
> > 3GB. These cannot be collapsed into fewer databases. SQL2005 is not an
> > option at this time (gotta love software companies that don't keep up with
> > the times! :)
> >
> > So, do I get 2 servers or a single (larger in processor and memory) server?
> >
> > --John J. Berlo
> > Lend Lease Corportation
> > Collaboration Technologies
Many data files, SAME Filegroup, SAME drive
Hello. I'm looking for any pros/cons to having multiple
files, SAME filegroup, SAME drive... Say you have a
database, where 4 data files reside on the one PRIMARY
Filegroup.
my_db_Data_1.MDF
my_db_Data_2.NDF
my_db_Data_3.NDF
my_db_Data_4.NDF
When you do an action like BULK INSERT a large amount of
data to a table, SQL Server will scatter that table's data
across the 4 data files.
I understand the reasons one might WANT to have 4 files on
teh same drive, like if they want to copy smaller data
files around, etc... But, performance-wise, I'd think it
would be at LEAST somewhat slower writing to 4 files, then
writing to ONE file on the same drive. Any ideas on that?
Also, wouldn't you have to deal with 4 shrinkings instead
of just one?
THanks, BruceBruce
What is a perfomance gain do you get by putting all files on the same
physical disk and the same filegroup?
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:01b801c34bc6$55421db0$a401280a@.phx.gbl...
> Hello. I'm looking for any pros/cons to having multiple
> files, SAME filegroup, SAME drive... Say you have a
> database, where 4 data files reside on the one PRIMARY
> Filegroup.
> my_db_Data_1.MDF
> my_db_Data_2.NDF
> my_db_Data_3.NDF
> my_db_Data_4.NDF
> When you do an action like BULK INSERT a large amount of
> data to a table, SQL Server will scatter that table's data
> across the 4 data files.
> I understand the reasons one might WANT to have 4 files on
> teh same drive, like if they want to copy smaller data
> files around, etc... But, performance-wise, I'd think it
> would be at LEAST somewhat slower writing to 4 files, then
> writing to ONE file on the same drive. Any ideas on that?
> Also, wouldn't you have to deal with 4 shrinkings instead
> of just one?
> THanks, Brucesql
files, SAME filegroup, SAME drive... Say you have a
database, where 4 data files reside on the one PRIMARY
Filegroup.
my_db_Data_1.MDF
my_db_Data_2.NDF
my_db_Data_3.NDF
my_db_Data_4.NDF
When you do an action like BULK INSERT a large amount of
data to a table, SQL Server will scatter that table's data
across the 4 data files.
I understand the reasons one might WANT to have 4 files on
teh same drive, like if they want to copy smaller data
files around, etc... But, performance-wise, I'd think it
would be at LEAST somewhat slower writing to 4 files, then
writing to ONE file on the same drive. Any ideas on that?
Also, wouldn't you have to deal with 4 shrinkings instead
of just one?
THanks, BruceBruce
What is a perfomance gain do you get by putting all files on the same
physical disk and the same filegroup?
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:01b801c34bc6$55421db0$a401280a@.phx.gbl...
> Hello. I'm looking for any pros/cons to having multiple
> files, SAME filegroup, SAME drive... Say you have a
> database, where 4 data files reside on the one PRIMARY
> Filegroup.
> my_db_Data_1.MDF
> my_db_Data_2.NDF
> my_db_Data_3.NDF
> my_db_Data_4.NDF
> When you do an action like BULK INSERT a large amount of
> data to a table, SQL Server will scatter that table's data
> across the 4 data files.
> I understand the reasons one might WANT to have 4 files on
> teh same drive, like if they want to copy smaller data
> files around, etc... But, performance-wise, I'd think it
> would be at LEAST somewhat slower writing to 4 files, then
> writing to ONE file on the same drive. Any ideas on that?
> Also, wouldn't you have to deal with 4 shrinkings instead
> of just one?
> THanks, Brucesql
Friday, March 9, 2012
managing the same database in multiple places
I am not a DBA, somewhat forced into the role of pseudo-DBA. My situation
is this - I have a production database on a server. Occasionally, I need to
have an exact duplicate of this production database on my laptop. What I do
in this case is stop the SQLSERVER service, make a physical copy of the mdf
and log files, then copy those files over to my laptop and attach database.
Subsequent to that, I just transparently replace the folder without
attaching, and usually the server is none the wiser and everything works
fine.
However, a recurring problem I have is that logins and users don't seem to
match up with each other. I have a login 'test' and a user within my
database 'test'. Sometimes when I do this manual copy, it works without a
hitch. Other times, it tells me that access is denied for 'test' user. I
go into the Security folder on EM and open the login and sure enough, it
doesn't have my database checked under the "Database Access" tab. When I
check the database to give the login access, it gives me an "Error 21002:
[SQL-DMO]User 'test' already exists.
So two questions:
First, how do I resolve this specific problem with logins and users not
matching up short of deleting them and recreating them and having to
rescript the permissions from my server to my laptop?
Second, I'm sure the way I'm copying the files manually, having to stop the
server and all is a pretty kludgy way of doing it. Is there a better way to
duplicate a database from my server to my laptop which won't incur this
login/user communication problem (not including replication)?(1) If the logins are SQL Server logins, you should be able to use =sp_change_users_login. You can read up on it within Books Online.
(2) The Transact-SQL commands BACKUP and RESTORE allow you to backup a =database to a file and then restore the database from that file without =shutting down the server. There is much documentation on these topics =within Books Online as well.
-- Keith
"James" <capricorn@.nospam.com> wrote in message =news:%23S7onQCjDHA.2544@.TK2MSFTNGP11.phx.gbl...
> I am not a DBA, somewhat forced into the role of pseudo-DBA. My =situation
> is this - I have a production database on a server. Occasionally, I =need to
> have an exact duplicate of this production database on my laptop. =What I do
> in this case is stop the SQLSERVER service, make a physical copy of =the mdf
> and log files, then copy those files over to my laptop and attach =database.
> Subsequent to that, I just transparently replace the folder without
> attaching, and usually the server is none the wiser and everything =works
> fine.
> > However, a recurring problem I have is that logins and users don't =seem to
> match up with each other. I have a login 'test' and a user within my
> database 'test'. Sometimes when I do this manual copy, it works =without a
> hitch. Other times, it tells me that access is denied for 'test' =user. I
> go into the Security folder on EM and open the login and sure enough, =it
> doesn't have my database checked under the "Database Access" tab. =When I
> check the database to give the login access, it gives me an "Error =21002:
> [SQL-DMO]User 'test' already exists.
> > So two questions:
> First, how do I resolve this specific problem with logins and users =not
> matching up short of deleting them and recreating them and having to
> rescript the permissions from my server to my laptop?
> > Second, I'm sure the way I'm copying the files manually, having to =stop the
> server and all is a pretty kludgy way of doing it. Is there a better =way to
> duplicate a database from my server to my laptop which won't incur =this
> login/user communication problem (not including replication)?
> > >|||Keith,
Thanks for pointing me in the right direction. I happen to have figured out
issue 1 just after I posted the question. sp_change_users_login
'Update_One', 'test', 'test' did the trick.
I am also aware of backup and restore, but I haven't quite figured out how
to restore from a network drive location even after looking through the
books online. It says to use DISK if the location is a redirected drive,
but I keep getting a Device Offline error. I also can't figure out the
syntax for backing up to a remote folder as I can't get it to add the
device, or at least it won't recognize it. I try using paths such as
'\\servername\d$\backup' but it doesn't see these. Can you suggest a sample
syntax for how I can backup and restore to network locations? Do I have to
map the drives before I can use them as devices?
Thanks,
James
"Keith Kratochvil" <sqlguy@.comcast.net> wrote in message
news:%23S1hPXCjDHA.1940@.TK2MSFTNGP09.phx.gbl...
(1) If the logins are SQL Server logins, you should be able to use
sp_change_users_login. You can read up on it within Books Online.
(2) The Transact-SQL commands BACKUP and RESTORE allow you to backup a
database to a file and then restore the database from that file without
shutting down the server. There is much documentation on these topics
within Books Online as well.
--
Keith
"James" <capricorn@.nospam.com> wrote in message
news:%23S7onQCjDHA.2544@.TK2MSFTNGP11.phx.gbl...
> I am not a DBA, somewhat forced into the role of pseudo-DBA. My situation
> is this - I have a production database on a server. Occasionally, I need
to
> have an exact duplicate of this production database on my laptop. What I
do
> in this case is stop the SQLSERVER service, make a physical copy of the
mdf
> and log files, then copy those files over to my laptop and attach
database.
> Subsequent to that, I just transparently replace the folder without
> attaching, and usually the server is none the wiser and everything works
> fine.
> However, a recurring problem I have is that logins and users don't seem to
> match up with each other. I have a login 'test' and a user within my
> database 'test'. Sometimes when I do this manual copy, it works without a
> hitch. Other times, it tells me that access is denied for 'test' user. I
> go into the Security folder on EM and open the login and sure enough, it
> doesn't have my database checked under the "Database Access" tab. When I
> check the database to give the login access, it gives me an "Error 21002:
> [SQL-DMO]User 'test' already exists.
> So two questions:
> First, how do I resolve this specific problem with logins and users not
> matching up short of deleting them and recreating them and having to
> rescript the permissions from my server to my laptop?
> Second, I'm sure the way I'm copying the files manually, having to stop
the
> server and all is a pretty kludgy way of doing it. Is there a better way
to
> duplicate a database from my server to my laptop which won't incur this
> login/user communication problem (not including replication)?
>
>|||Hi James,
Thanks for Keith's help. To backup up a database to a remote folder and
restore it, please try to perform the following article.
1. Share a folder on the remote machine.
2. Perform these codes using Query Analyzer on your original instance:
-- Create a logical backup device for the full EXCHANGETEST1 backup.
EXEC sp_addumpdevice 'disk', 'EXCHANGETEST1',
'\\<RemoteMachineName>\<ShareFolderName>\EXCHANGETEST1.dat'
-- Back up the full EXCHANGETEXT1 database.
BACKUP DATABASE EXCHANGETEST1 TO EXCHANGETEST1
3. Perform these codes using Query Analyzer on the remote instance to
restore the database EXCHANGETEST1.
--Restore the EXCHANGETEXT1 database.
RESTORE DATABASE EXCHANGETEST1 FROM
DISK='\\<RemoteMachineName>\<ShareFolderName>\EXCHANGETEST1.dat'
WITH RECOVERY,
MOVE 'exchangetest1_data'TO 'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\exchangetest1_data.mdf',
MOVE 'exchangetest1_log'TO 'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\exchangetest1_log.ldf'
It works on my side. Does it work on your machine? Please feel free to let
me know if this solves your problem or if you would like further assistance.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||It is important to note that the account that SQL Server (and SQL Server =Agent) runs under has permissions to the share. If the account has the =appropriate permissions, it is also possible to backup to an admin share =(example: \\Machine\c$)
You can backup directly to the share/admin share with the backup command =(without adding the backup device):
BACKUP DATABASE master TO DISK =3D '\\SomeOtherMachine\c$\master.bak' =WITH INIT
-- Keith
"Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message =news:wrUppOLjDHA.1544@.cpmsftngxa06.phx.gbl...
> Hi James,
> > Thanks for Keith's help. To backup up a database to a remote folder =and > restore it, please try to perform the following article. > > 1. Share a folder on the remote machine.
> > 2. Perform these codes using Query Analyzer on your original instance:
> > -- Create a logical backup device for the full EXCHANGETEST1 backup.
> > EXEC sp_addumpdevice 'disk', 'EXCHANGETEST1', > '\\<RemoteMachineName>\<ShareFolderName>\EXCHANGETEST1.dat'
> > -- Back up the full EXCHANGETEXT1 database.
> BACKUP DATABASE EXCHANGETEST1 TO EXCHANGETEST1
> > 3. Perform these codes using Query Analyzer on the remote instance to > restore the database EXCHANGETEST1.
> > --Restore the EXCHANGETEXT1 database.
> > RESTORE DATABASE EXCHANGETEST1 FROM > DISK=3D'\\<RemoteMachineName>\<ShareFolderName>\EXCHANGETEST1.dat'
> WITH RECOVERY,
> MOVE 'exchangetest1_data'TO 'c:\Program Files\Microsoft SQL > Server\MSSQL\Data\exchangetest1_data.mdf', > MOVE 'exchangetest1_log'TO 'c:\Program Files\Microsoft SQL > Server\MSSQL\Data\exchangetest1_log.ldf'
> > It works on my side. Does it work on your machine? Please feel free to =let > me know if this solves your problem or if you would like further =assistance.
> > Regards, > > Michael Shao
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no =rights.
>
is this - I have a production database on a server. Occasionally, I need to
have an exact duplicate of this production database on my laptop. What I do
in this case is stop the SQLSERVER service, make a physical copy of the mdf
and log files, then copy those files over to my laptop and attach database.
Subsequent to that, I just transparently replace the folder without
attaching, and usually the server is none the wiser and everything works
fine.
However, a recurring problem I have is that logins and users don't seem to
match up with each other. I have a login 'test' and a user within my
database 'test'. Sometimes when I do this manual copy, it works without a
hitch. Other times, it tells me that access is denied for 'test' user. I
go into the Security folder on EM and open the login and sure enough, it
doesn't have my database checked under the "Database Access" tab. When I
check the database to give the login access, it gives me an "Error 21002:
[SQL-DMO]User 'test' already exists.
So two questions:
First, how do I resolve this specific problem with logins and users not
matching up short of deleting them and recreating them and having to
rescript the permissions from my server to my laptop?
Second, I'm sure the way I'm copying the files manually, having to stop the
server and all is a pretty kludgy way of doing it. Is there a better way to
duplicate a database from my server to my laptop which won't incur this
login/user communication problem (not including replication)?(1) If the logins are SQL Server logins, you should be able to use =sp_change_users_login. You can read up on it within Books Online.
(2) The Transact-SQL commands BACKUP and RESTORE allow you to backup a =database to a file and then restore the database from that file without =shutting down the server. There is much documentation on these topics =within Books Online as well.
-- Keith
"James" <capricorn@.nospam.com> wrote in message =news:%23S7onQCjDHA.2544@.TK2MSFTNGP11.phx.gbl...
> I am not a DBA, somewhat forced into the role of pseudo-DBA. My =situation
> is this - I have a production database on a server. Occasionally, I =need to
> have an exact duplicate of this production database on my laptop. =What I do
> in this case is stop the SQLSERVER service, make a physical copy of =the mdf
> and log files, then copy those files over to my laptop and attach =database.
> Subsequent to that, I just transparently replace the folder without
> attaching, and usually the server is none the wiser and everything =works
> fine.
> > However, a recurring problem I have is that logins and users don't =seem to
> match up with each other. I have a login 'test' and a user within my
> database 'test'. Sometimes when I do this manual copy, it works =without a
> hitch. Other times, it tells me that access is denied for 'test' =user. I
> go into the Security folder on EM and open the login and sure enough, =it
> doesn't have my database checked under the "Database Access" tab. =When I
> check the database to give the login access, it gives me an "Error =21002:
> [SQL-DMO]User 'test' already exists.
> > So two questions:
> First, how do I resolve this specific problem with logins and users =not
> matching up short of deleting them and recreating them and having to
> rescript the permissions from my server to my laptop?
> > Second, I'm sure the way I'm copying the files manually, having to =stop the
> server and all is a pretty kludgy way of doing it. Is there a better =way to
> duplicate a database from my server to my laptop which won't incur =this
> login/user communication problem (not including replication)?
> > >|||Keith,
Thanks for pointing me in the right direction. I happen to have figured out
issue 1 just after I posted the question. sp_change_users_login
'Update_One', 'test', 'test' did the trick.
I am also aware of backup and restore, but I haven't quite figured out how
to restore from a network drive location even after looking through the
books online. It says to use DISK if the location is a redirected drive,
but I keep getting a Device Offline error. I also can't figure out the
syntax for backing up to a remote folder as I can't get it to add the
device, or at least it won't recognize it. I try using paths such as
'\\servername\d$\backup' but it doesn't see these. Can you suggest a sample
syntax for how I can backup and restore to network locations? Do I have to
map the drives before I can use them as devices?
Thanks,
James
"Keith Kratochvil" <sqlguy@.comcast.net> wrote in message
news:%23S1hPXCjDHA.1940@.TK2MSFTNGP09.phx.gbl...
(1) If the logins are SQL Server logins, you should be able to use
sp_change_users_login. You can read up on it within Books Online.
(2) The Transact-SQL commands BACKUP and RESTORE allow you to backup a
database to a file and then restore the database from that file without
shutting down the server. There is much documentation on these topics
within Books Online as well.
--
Keith
"James" <capricorn@.nospam.com> wrote in message
news:%23S7onQCjDHA.2544@.TK2MSFTNGP11.phx.gbl...
> I am not a DBA, somewhat forced into the role of pseudo-DBA. My situation
> is this - I have a production database on a server. Occasionally, I need
to
> have an exact duplicate of this production database on my laptop. What I
do
> in this case is stop the SQLSERVER service, make a physical copy of the
mdf
> and log files, then copy those files over to my laptop and attach
database.
> Subsequent to that, I just transparently replace the folder without
> attaching, and usually the server is none the wiser and everything works
> fine.
> However, a recurring problem I have is that logins and users don't seem to
> match up with each other. I have a login 'test' and a user within my
> database 'test'. Sometimes when I do this manual copy, it works without a
> hitch. Other times, it tells me that access is denied for 'test' user. I
> go into the Security folder on EM and open the login and sure enough, it
> doesn't have my database checked under the "Database Access" tab. When I
> check the database to give the login access, it gives me an "Error 21002:
> [SQL-DMO]User 'test' already exists.
> So two questions:
> First, how do I resolve this specific problem with logins and users not
> matching up short of deleting them and recreating them and having to
> rescript the permissions from my server to my laptop?
> Second, I'm sure the way I'm copying the files manually, having to stop
the
> server and all is a pretty kludgy way of doing it. Is there a better way
to
> duplicate a database from my server to my laptop which won't incur this
> login/user communication problem (not including replication)?
>
>|||Hi James,
Thanks for Keith's help. To backup up a database to a remote folder and
restore it, please try to perform the following article.
1. Share a folder on the remote machine.
2. Perform these codes using Query Analyzer on your original instance:
-- Create a logical backup device for the full EXCHANGETEST1 backup.
EXEC sp_addumpdevice 'disk', 'EXCHANGETEST1',
'\\<RemoteMachineName>\<ShareFolderName>\EXCHANGETEST1.dat'
-- Back up the full EXCHANGETEXT1 database.
BACKUP DATABASE EXCHANGETEST1 TO EXCHANGETEST1
3. Perform these codes using Query Analyzer on the remote instance to
restore the database EXCHANGETEST1.
--Restore the EXCHANGETEXT1 database.
RESTORE DATABASE EXCHANGETEST1 FROM
DISK='\\<RemoteMachineName>\<ShareFolderName>\EXCHANGETEST1.dat'
WITH RECOVERY,
MOVE 'exchangetest1_data'TO 'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\exchangetest1_data.mdf',
MOVE 'exchangetest1_log'TO 'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\exchangetest1_log.ldf'
It works on my side. Does it work on your machine? Please feel free to let
me know if this solves your problem or if you would like further assistance.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||It is important to note that the account that SQL Server (and SQL Server =Agent) runs under has permissions to the share. If the account has the =appropriate permissions, it is also possible to backup to an admin share =(example: \\Machine\c$)
You can backup directly to the share/admin share with the backup command =(without adding the backup device):
BACKUP DATABASE master TO DISK =3D '\\SomeOtherMachine\c$\master.bak' =WITH INIT
-- Keith
"Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message =news:wrUppOLjDHA.1544@.cpmsftngxa06.phx.gbl...
> Hi James,
> > Thanks for Keith's help. To backup up a database to a remote folder =and > restore it, please try to perform the following article. > > 1. Share a folder on the remote machine.
> > 2. Perform these codes using Query Analyzer on your original instance:
> > -- Create a logical backup device for the full EXCHANGETEST1 backup.
> > EXEC sp_addumpdevice 'disk', 'EXCHANGETEST1', > '\\<RemoteMachineName>\<ShareFolderName>\EXCHANGETEST1.dat'
> > -- Back up the full EXCHANGETEXT1 database.
> BACKUP DATABASE EXCHANGETEST1 TO EXCHANGETEST1
> > 3. Perform these codes using Query Analyzer on the remote instance to > restore the database EXCHANGETEST1.
> > --Restore the EXCHANGETEXT1 database.
> > RESTORE DATABASE EXCHANGETEST1 FROM > DISK=3D'\\<RemoteMachineName>\<ShareFolderName>\EXCHANGETEST1.dat'
> WITH RECOVERY,
> MOVE 'exchangetest1_data'TO 'c:\Program Files\Microsoft SQL > Server\MSSQL\Data\exchangetest1_data.mdf', > MOVE 'exchangetest1_log'TO 'c:\Program Files\Microsoft SQL > Server\MSSQL\Data\exchangetest1_log.ldf'
> > It works on my side. Does it work on your machine? Please feel free to =let > me know if this solves your problem or if you would like further =assistance.
> > Regards, > > Michael Shao
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no =rights.
>
Wednesday, March 7, 2012
Managing multiple SQL Server databases
Hi,
I have to manage many SQL Server databases on several servers. How can I manage the jobs, backups and the space on the disc without going to each and every server and database and job? Is there any script to run this? It will be very helpful if you can provide me the sample script or point me to any web site where I can get the info/script for this. Thanks in advance...
My preferable way to do this is to create the job on the server, script it out, parameterize it and deploy it with changed parameters on the other servers, this is also very helpful as you can tweak the paths to the log files, as they can differ from server to server as well as in the drive location as in the path.HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||
When running scripts to manage the system you are going to have to run them on the server they are needed on... Even if they are stored on the one server. I would start by looking at Linked Servers so that you can run your queries and scripts on the remote machines from the one server. In regards to monitoring the servers you could also look at MOM (Microsoft Operations Manager) and have the agents installed on each of your SQL Servers. This way the MOM System can monitor the OS Level parameters as well as some of the SQL Systems (Using the SQL Management Packs). At the same time you can also create custom scripts and tasks that MOM Can run that can be targeted to use the different servers.
With SSIS You should be able to also confugre your SQL Jobs to run from the one server and execute the different commands on the different servers.
|||For Multiserver administration u need to create
Master server
Target Server
Enlist Traget server ................
See http://msdn2.microsoft.com/en-us/library/ms191305.aspx
I hope this helps
|||Thank you Jens, Glenn and admindba for your valuable inputs...Managing Multiple Excel incoming files?
Managing Multiple Excel incoming files?
I have this situation. I have a client which have several locations they
work primary with excel forms and they are thinking in doing reports with
them. What they want is to import those Excel files into SQL Server for
later using crystal reports or maybe asp.net for reporting. Those files
will come every week from diferent locations then go to SQL Server and then
they should be stored in a folder in the server for backup. So this means
that i have to deal with the importing but also with the file naming. This
is because they want the reports to be date based. So i have to ask them to
include the date as part of the file name or changing the file name after
procesing the file if I want to store them all in the same folder after
procesing.
So the real question is how do I deal with multiple file in regular basis
bye changing the name of the file or enforcing they send me the file with
the date already included. If the recomendation is the second one how can i
tell the system to change the files after the procesing?
Is there any easy way or tool to achive this? is there any way i can
configure DTS to do that?
EliezerThe easiest way would probably be to use a script task in DTS and us the
File System Object to manipulate the files. It can be done from TSQL but its
pretty kludgy. Using FSO from DTS will allow you a lot more flexibility and
control.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Eliezer Figueroa" <efigueroa@.propoint.com.do> wrote in message
news:Xns9479AA48BF55Aefigueroapropointco
m@.216.196.97.132...
then
to
i
I have this situation. I have a client which have several locations they
work primary with excel forms and they are thinking in doing reports with
them. What they want is to import those Excel files into SQL Server for
later using crystal reports or maybe asp.net for reporting. Those files
will come every week from diferent locations then go to SQL Server and then
they should be stored in a folder in the server for backup. So this means
that i have to deal with the importing but also with the file naming. This
is because they want the reports to be date based. So i have to ask them to
include the date as part of the file name or changing the file name after
procesing the file if I want to store them all in the same folder after
procesing.
So the real question is how do I deal with multiple file in regular basis
bye changing the name of the file or enforcing they send me the file with
the date already included. If the recomendation is the second one how can i
tell the system to change the files after the procesing?
Is there any easy way or tool to achive this? is there any way i can
configure DTS to do that?
EliezerThe easiest way would probably be to use a script task in DTS and us the
File System Object to manipulate the files. It can be done from TSQL but its
pretty kludgy. Using FSO from DTS will allow you a lot more flexibility and
control.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Eliezer Figueroa" <efigueroa@.propoint.com.do> wrote in message
news:Xns9479AA48BF55Aefigueroapropointco
m@.216.196.97.132...
quote:
> Managing Multiple Excel incoming files?
> I have this situation. I have a client which have several locations they
> work primary with excel forms and they are thinking in doing reports with
> them. What they want is to import those Excel files into SQL Server for
> later using crystal reports or maybe asp.net for reporting. Those files
> will come every week from diferent locations then go to SQL Server and
then
quote:
> they should be stored in a folder in the server for backup. So this means
> that i have to deal with the importing but also with the file naming. This
> is because they want the reports to be date based. So i have to ask them
to
quote:
> include the date as part of the file name or changing the file name after
> procesing the file if I want to store them all in the same folder after
> procesing.
> So the real question is how do I deal with multiple file in regular basis
> bye changing the name of the file or enforcing they send me the file with
> the date already included. If the recomendation is the second one how can
i
quote:
> tell the system to change the files after the procesing?
> Is there any easy way or tool to achive this? is there any way i can
> configure DTS to do that?
>
> Eliezer
Managing Multiple Excel incoming files?
Managing Multiple Excel incoming files?
I have this situation. I have a client which have several locations they
work primary with excel forms and they are thinking in doing reports with
them. What they want is to import those Excel files into SQL Server for
later using crystal reports or maybe asp.net for reporting. Those files
will come every week from diferent locations then go to SQL Server and then
they should be stored in a folder in the server for backup. So this means
that i have to deal with the importing but also with the file naming. This
is because they want the reports to be date based. So i have to ask them to
include the date as part of the file name or changing the file name after
procesing the file if I want to store them all in the same folder after
procesing.
So the real question is how do I deal with multiple file in regular basis
bye changing the name of the file or enforcing they send me the file with
the date already included. If the recomendation is the second one how can i
tell the system to change the files after the procesing?
Is there any easy way or tool to achive this? is there any way i can
configure DTS to do that?
EliezerThe easiest way would probably be to use a script task in DTS and us the
File System Object to manipulate the files. It can be done from TSQL but its
pretty kludgy. Using FSO from DTS will allow you a lot more flexibility and
control.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Eliezer Figueroa" <efigueroa@.propoint.com.do> wrote in message
news:Xns9479AA48BF55Aefigueroapropointcom@.216.196.97.132...
> Managing Multiple Excel incoming files?
> I have this situation. I have a client which have several locations they
> work primary with excel forms and they are thinking in doing reports with
> them. What they want is to import those Excel files into SQL Server for
> later using crystal reports or maybe asp.net for reporting. Those files
> will come every week from diferent locations then go to SQL Server and
then
> they should be stored in a folder in the server for backup. So this means
> that i have to deal with the importing but also with the file naming. This
> is because they want the reports to be date based. So i have to ask them
to
> include the date as part of the file name or changing the file name after
> procesing the file if I want to store them all in the same folder after
> procesing.
> So the real question is how do I deal with multiple file in regular basis
> bye changing the name of the file or enforcing they send me the file with
> the date already included. If the recomendation is the second one how can
i
> tell the system to change the files after the procesing?
> Is there any easy way or tool to achive this? is there any way i can
> configure DTS to do that?
>
> Eliezer
I have this situation. I have a client which have several locations they
work primary with excel forms and they are thinking in doing reports with
them. What they want is to import those Excel files into SQL Server for
later using crystal reports or maybe asp.net for reporting. Those files
will come every week from diferent locations then go to SQL Server and then
they should be stored in a folder in the server for backup. So this means
that i have to deal with the importing but also with the file naming. This
is because they want the reports to be date based. So i have to ask them to
include the date as part of the file name or changing the file name after
procesing the file if I want to store them all in the same folder after
procesing.
So the real question is how do I deal with multiple file in regular basis
bye changing the name of the file or enforcing they send me the file with
the date already included. If the recomendation is the second one how can i
tell the system to change the files after the procesing?
Is there any easy way or tool to achive this? is there any way i can
configure DTS to do that?
EliezerThe easiest way would probably be to use a script task in DTS and us the
File System Object to manipulate the files. It can be done from TSQL but its
pretty kludgy. Using FSO from DTS will allow you a lot more flexibility and
control.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Eliezer Figueroa" <efigueroa@.propoint.com.do> wrote in message
news:Xns9479AA48BF55Aefigueroapropointcom@.216.196.97.132...
> Managing Multiple Excel incoming files?
> I have this situation. I have a client which have several locations they
> work primary with excel forms and they are thinking in doing reports with
> them. What they want is to import those Excel files into SQL Server for
> later using crystal reports or maybe asp.net for reporting. Those files
> will come every week from diferent locations then go to SQL Server and
then
> they should be stored in a folder in the server for backup. So this means
> that i have to deal with the importing but also with the file naming. This
> is because they want the reports to be date based. So i have to ask them
to
> include the date as part of the file name or changing the file name after
> procesing the file if I want to store them all in the same folder after
> procesing.
> So the real question is how do I deal with multiple file in regular basis
> bye changing the name of the file or enforcing they send me the file with
> the date already included. If the recomendation is the second one how can
i
> tell the system to change the files after the procesing?
> Is there any easy way or tool to achive this? is there any way i can
> configure DTS to do that?
>
> Eliezer
Subscribe to:
Posts (Atom)