I am trying to mirror a report originally published through another non MS
reporting application which allows multiple selection from a query parameter
list by use of the normal Windows 'Ctrl' or 'Shift' keys in cojunction with
the mouse click.
This method does not appear to function with SQL Reporting Services. The
expanded list automatically closes on selection of one item.
Is there another method of performing multiple selection from a list.This is something which needs to be added reporting services. You can NOT do
a multi selection... About the best thing you can do is allow your user to
enter a delimited list... It's really a good option, but about the only
option right now...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"RW" <RW@.discussions.microsoft.com> wrote in message
news:63B4D8FB-A36B-485B-B251-9D07D810A513@.microsoft.com...
> I am trying to mirror a report originally published through another non MS
> reporting application which allows multiple selection from a query
parameter
> list by use of the normal Windows 'Ctrl' or 'Shift' keys in cojunction
with
> the mouse click.
> This method does not appear to function with SQL Reporting Services. The
> expanded list automatically closes on selection of one item.
> Is there another method of performing multiple selection from a list.
Showing posts with label application. Show all posts
Showing posts with label application. Show all posts
Friday, March 30, 2012
Monday, March 26, 2012
Multiple Instance Memory Utilization
I have a Dell server with 4GB of memory and 4 CPUs with 1.6G each, hosting
one major application database. SQL Server 2000 Standard Edition was
installed on the server, OS is Windows 2000 Server. Currently with single
instance installed, I checked the memory usage using task manager, the wholes
system can only use up to less then 2 GB of memory. The peak is less than 2
GB. The system neven utilizing more than 2 GM of memory. I am in the process
of doing server consolidation and planning to move another major application
database to this dell server. I want to utilize 4 G of memory after I
consolidate the other SQL server to the Dell Server. If I just move the
database to this dell server without creating a new instance, the most memory
we can utilize is 3GB with 3 GB switch set in boot.ini. If we create a new
instance, does that mean we can utilize the other 2 GB of Memory on the
server? Security is not a major concern when using single instance. What are
the Pros and Cons?
Thank you very much for your help.
You need Enterprise edition to use more than 2GB. Windows 2000 server does
not use the /3GB switch other than in a test scenario. Yes you can use two
instances and each can use 2GB but you have to be careful as now you can
lead to a situation where there is little left for the OS. If you are
consolidating then you should look at using Enterprise edition and Win2000
ADV Server (or Win2003) so you can use all 3GB for Sql Server and have a GB
for the OS and any other apps.
Andrew J. Kelly SQL MVP
"royHe" <royHe@.discussions.microsoft.com> wrote in message
news:8B395A5C-26F6-4E71-95B2-D9CE53E62100@.microsoft.com...
>I have a Dell server with 4GB of memory and 4 CPUs with 1.6G each, hosting
> one major application database. SQL Server 2000 Standard Edition was
> installed on the server, OS is Windows 2000 Server. Currently with single
> instance installed, I checked the memory usage using task manager, the
> wholes
> system can only use up to less then 2 GB of memory. The peak is less than
> 2
> GB. The system neven utilizing more than 2 GM of memory. I am in the
> process
> of doing server consolidation and planning to move another major
> application
> database to this dell server. I want to utilize 4 G of memory after I
> consolidate the other SQL server to the Dell Server. If I just move the
> database to this dell server without creating a new instance, the most
> memory
> we can utilize is 3GB with 3 GB switch set in boot.ini. If we create a new
> instance, does that mean we can utilize the other 2 GB of Memory on the
> server? Security is not a major concern when using single instance. What
> are
> the Pros and Cons?
> Thank you very much for your help.
>
|||Thank you very much.
This is a dedicated SQL server. I find out in my situation the OS use very
little memory. Probably If I reserve 256M to 300M for the OS is enough.
Currently the peak for the OS Memory usage is around 250M.
"Andrew J. Kelly" wrote:
> You need Enterprise edition to use more than 2GB. Windows 2000 server does
> not use the /3GB switch other than in a test scenario. Yes you can use two
> instances and each can use 2GB but you have to be careful as now you can
> lead to a situation where there is little left for the OS. If you are
> consolidating then you should look at using Enterprise edition and Win2000
> ADV Server (or Win2003) so you can use all 3GB for Sql Server and have a GB
> for the OS and any other apps.
> --
> Andrew J. Kelly SQL MVP
>
> "royHe" <royHe@.discussions.microsoft.com> wrote in message
> news:8B395A5C-26F6-4E71-95B2-D9CE53E62100@.microsoft.com...
>
>
one major application database. SQL Server 2000 Standard Edition was
installed on the server, OS is Windows 2000 Server. Currently with single
instance installed, I checked the memory usage using task manager, the wholes
system can only use up to less then 2 GB of memory. The peak is less than 2
GB. The system neven utilizing more than 2 GM of memory. I am in the process
of doing server consolidation and planning to move another major application
database to this dell server. I want to utilize 4 G of memory after I
consolidate the other SQL server to the Dell Server. If I just move the
database to this dell server without creating a new instance, the most memory
we can utilize is 3GB with 3 GB switch set in boot.ini. If we create a new
instance, does that mean we can utilize the other 2 GB of Memory on the
server? Security is not a major concern when using single instance. What are
the Pros and Cons?
Thank you very much for your help.
You need Enterprise edition to use more than 2GB. Windows 2000 server does
not use the /3GB switch other than in a test scenario. Yes you can use two
instances and each can use 2GB but you have to be careful as now you can
lead to a situation where there is little left for the OS. If you are
consolidating then you should look at using Enterprise edition and Win2000
ADV Server (or Win2003) so you can use all 3GB for Sql Server and have a GB
for the OS and any other apps.
Andrew J. Kelly SQL MVP
"royHe" <royHe@.discussions.microsoft.com> wrote in message
news:8B395A5C-26F6-4E71-95B2-D9CE53E62100@.microsoft.com...
>I have a Dell server with 4GB of memory and 4 CPUs with 1.6G each, hosting
> one major application database. SQL Server 2000 Standard Edition was
> installed on the server, OS is Windows 2000 Server. Currently with single
> instance installed, I checked the memory usage using task manager, the
> wholes
> system can only use up to less then 2 GB of memory. The peak is less than
> 2
> GB. The system neven utilizing more than 2 GM of memory. I am in the
> process
> of doing server consolidation and planning to move another major
> application
> database to this dell server. I want to utilize 4 G of memory after I
> consolidate the other SQL server to the Dell Server. If I just move the
> database to this dell server without creating a new instance, the most
> memory
> we can utilize is 3GB with 3 GB switch set in boot.ini. If we create a new
> instance, does that mean we can utilize the other 2 GB of Memory on the
> server? Security is not a major concern when using single instance. What
> are
> the Pros and Cons?
> Thank you very much for your help.
>
|||Thank you very much.
This is a dedicated SQL server. I find out in my situation the OS use very
little memory. Probably If I reserve 256M to 300M for the OS is enough.
Currently the peak for the OS Memory usage is around 250M.
"Andrew J. Kelly" wrote:
> You need Enterprise edition to use more than 2GB. Windows 2000 server does
> not use the /3GB switch other than in a test scenario. Yes you can use two
> instances and each can use 2GB but you have to be careful as now you can
> lead to a situation where there is little left for the OS. If you are
> consolidating then you should look at using Enterprise edition and Win2000
> ADV Server (or Win2003) so you can use all 3GB for Sql Server and have a GB
> for the OS and any other apps.
> --
> Andrew J. Kelly SQL MVP
>
> "royHe" <royHe@.discussions.microsoft.com> wrote in message
> news:8B395A5C-26F6-4E71-95B2-D9CE53E62100@.microsoft.com...
>
>
Multiple Instance Memory Utilization
I have a Dell server with 4GB of memory and 4 CPUs with 1.6G each, hosting
one major application database. SQL Server 2000 Standard Edition was
installed on the server, OS is Windows 2000 Server. Currently with single
instance installed, I checked the memory usage using task manager, the wholes
system can only use up to less then 2 GB of memory. The peak is less than 2
GB. The system neven utilizing more than 2 GM of memory. I am in the process
of doing server consolidation and planning to move another major application
database to this dell server. I want to utilize 4 G of memory after I
consolidate the other SQL server to the Dell Server. If I just move the
database to this dell server without creating a new instance, the most memory
we can utilize is 3GB with 3 GB switch set in boot.ini. If we create a new
instance, does that mean we can utilize the other 2 GB of Memory on the
server? Security is not a major concern when using single instance. What are
the Pros and Cons?
Thank you very much for your help.You need Enterprise edition to use more than 2GB. Windows 2000 server does
not use the /3GB switch other than in a test scenario. Yes you can use two
instances and each can use 2GB but you have to be careful as now you can
lead to a situation where there is little left for the OS. If you are
consolidating then you should look at using Enterprise edition and Win2000
ADV Server (or Win2003) so you can use all 3GB for Sql Server and have a GB
for the OS and any other apps.
--
Andrew J. Kelly SQL MVP
"royHe" <royHe@.discussions.microsoft.com> wrote in message
news:8B395A5C-26F6-4E71-95B2-D9CE53E62100@.microsoft.com...
>I have a Dell server with 4GB of memory and 4 CPUs with 1.6G each, hosting
> one major application database. SQL Server 2000 Standard Edition was
> installed on the server, OS is Windows 2000 Server. Currently with single
> instance installed, I checked the memory usage using task manager, the
> wholes
> system can only use up to less then 2 GB of memory. The peak is less than
> 2
> GB. The system neven utilizing more than 2 GM of memory. I am in the
> process
> of doing server consolidation and planning to move another major
> application
> database to this dell server. I want to utilize 4 G of memory after I
> consolidate the other SQL server to the Dell Server. If I just move the
> database to this dell server without creating a new instance, the most
> memory
> we can utilize is 3GB with 3 GB switch set in boot.ini. If we create a new
> instance, does that mean we can utilize the other 2 GB of Memory on the
> server? Security is not a major concern when using single instance. What
> are
> the Pros and Cons?
> Thank you very much for your help.
>|||Thank you very much.
This is a dedicated SQL server. I find out in my situation the OS use very
little memory. Probably If I reserve 256M to 300M for the OS is enough.
Currently the peak for the OS Memory usage is around 250M.
"Andrew J. Kelly" wrote:
> You need Enterprise edition to use more than 2GB. Windows 2000 server does
> not use the /3GB switch other than in a test scenario. Yes you can use two
> instances and each can use 2GB but you have to be careful as now you can
> lead to a situation where there is little left for the OS. If you are
> consolidating then you should look at using Enterprise edition and Win2000
> ADV Server (or Win2003) so you can use all 3GB for Sql Server and have a GB
> for the OS and any other apps.
> --
> Andrew J. Kelly SQL MVP
>
> "royHe" <royHe@.discussions.microsoft.com> wrote in message
> news:8B395A5C-26F6-4E71-95B2-D9CE53E62100@.microsoft.com...
> >I have a Dell server with 4GB of memory and 4 CPUs with 1.6G each, hosting
> > one major application database. SQL Server 2000 Standard Edition was
> > installed on the server, OS is Windows 2000 Server. Currently with single
> > instance installed, I checked the memory usage using task manager, the
> > wholes
> > system can only use up to less then 2 GB of memory. The peak is less than
> > 2
> > GB. The system neven utilizing more than 2 GM of memory. I am in the
> > process
> > of doing server consolidation and planning to move another major
> > application
> > database to this dell server. I want to utilize 4 G of memory after I
> > consolidate the other SQL server to the Dell Server. If I just move the
> > database to this dell server without creating a new instance, the most
> > memory
> > we can utilize is 3GB with 3 GB switch set in boot.ini. If we create a new
> > instance, does that mean we can utilize the other 2 GB of Memory on the
> > server? Security is not a major concern when using single instance. What
> > are
> > the Pros and Cons?
> >
> > Thank you very much for your help.
> >
> >
>
>
one major application database. SQL Server 2000 Standard Edition was
installed on the server, OS is Windows 2000 Server. Currently with single
instance installed, I checked the memory usage using task manager, the wholes
system can only use up to less then 2 GB of memory. The peak is less than 2
GB. The system neven utilizing more than 2 GM of memory. I am in the process
of doing server consolidation and planning to move another major application
database to this dell server. I want to utilize 4 G of memory after I
consolidate the other SQL server to the Dell Server. If I just move the
database to this dell server without creating a new instance, the most memory
we can utilize is 3GB with 3 GB switch set in boot.ini. If we create a new
instance, does that mean we can utilize the other 2 GB of Memory on the
server? Security is not a major concern when using single instance. What are
the Pros and Cons?
Thank you very much for your help.You need Enterprise edition to use more than 2GB. Windows 2000 server does
not use the /3GB switch other than in a test scenario. Yes you can use two
instances and each can use 2GB but you have to be careful as now you can
lead to a situation where there is little left for the OS. If you are
consolidating then you should look at using Enterprise edition and Win2000
ADV Server (or Win2003) so you can use all 3GB for Sql Server and have a GB
for the OS and any other apps.
--
Andrew J. Kelly SQL MVP
"royHe" <royHe@.discussions.microsoft.com> wrote in message
news:8B395A5C-26F6-4E71-95B2-D9CE53E62100@.microsoft.com...
>I have a Dell server with 4GB of memory and 4 CPUs with 1.6G each, hosting
> one major application database. SQL Server 2000 Standard Edition was
> installed on the server, OS is Windows 2000 Server. Currently with single
> instance installed, I checked the memory usage using task manager, the
> wholes
> system can only use up to less then 2 GB of memory. The peak is less than
> 2
> GB. The system neven utilizing more than 2 GM of memory. I am in the
> process
> of doing server consolidation and planning to move another major
> application
> database to this dell server. I want to utilize 4 G of memory after I
> consolidate the other SQL server to the Dell Server. If I just move the
> database to this dell server without creating a new instance, the most
> memory
> we can utilize is 3GB with 3 GB switch set in boot.ini. If we create a new
> instance, does that mean we can utilize the other 2 GB of Memory on the
> server? Security is not a major concern when using single instance. What
> are
> the Pros and Cons?
> Thank you very much for your help.
>|||Thank you very much.
This is a dedicated SQL server. I find out in my situation the OS use very
little memory. Probably If I reserve 256M to 300M for the OS is enough.
Currently the peak for the OS Memory usage is around 250M.
"Andrew J. Kelly" wrote:
> You need Enterprise edition to use more than 2GB. Windows 2000 server does
> not use the /3GB switch other than in a test scenario. Yes you can use two
> instances and each can use 2GB but you have to be careful as now you can
> lead to a situation where there is little left for the OS. If you are
> consolidating then you should look at using Enterprise edition and Win2000
> ADV Server (or Win2003) so you can use all 3GB for Sql Server and have a GB
> for the OS and any other apps.
> --
> Andrew J. Kelly SQL MVP
>
> "royHe" <royHe@.discussions.microsoft.com> wrote in message
> news:8B395A5C-26F6-4E71-95B2-D9CE53E62100@.microsoft.com...
> >I have a Dell server with 4GB of memory and 4 CPUs with 1.6G each, hosting
> > one major application database. SQL Server 2000 Standard Edition was
> > installed on the server, OS is Windows 2000 Server. Currently with single
> > instance installed, I checked the memory usage using task manager, the
> > wholes
> > system can only use up to less then 2 GB of memory. The peak is less than
> > 2
> > GB. The system neven utilizing more than 2 GM of memory. I am in the
> > process
> > of doing server consolidation and planning to move another major
> > application
> > database to this dell server. I want to utilize 4 G of memory after I
> > consolidate the other SQL server to the Dell Server. If I just move the
> > database to this dell server without creating a new instance, the most
> > memory
> > we can utilize is 3GB with 3 GB switch set in boot.ini. If we create a new
> > instance, does that mean we can utilize the other 2 GB of Memory on the
> > server? Security is not a major concern when using single instance. What
> > are
> > the Pros and Cons?
> >
> > Thank you very much for your help.
> >
> >
>
>
Multiple Instance Memory Utilization
I have a Dell server with 4GB of memory and 4 CPUs with 1.6G each, hosting
one major application database. SQL Server 2000 Standard Edition was
installed on the server, OS is Windows 2000 Server. Currently with single
instance installed, I checked the memory usage using task manager, the whole
s
system can only use up to less then 2 GB of memory. The peak is less than 2
GB. The system neven utilizing more than 2 GM of memory. I am in the proces
s
of doing server consolidation and planning to move another major application
database to this dell server. I want to utilize 4 G of memory after I
consolidate the other SQL server to the Dell Server. If I just move the
database to this dell server without creating a new instance, the most memor
y
we can utilize is 3GB with 3 GB switch set in boot.ini. If we create a new
instance, does that mean we can utilize the other 2 GB of Memory on the
server? Security is not a major concern when using single instance. What ar
e
the Pros and Cons?
Thank you very much for your help.You need Enterprise edition to use more than 2GB. Windows 2000 server does
not use the /3GB switch other than in a test scenario. Yes you can use two
instances and each can use 2GB but you have to be careful as now you can
lead to a situation where there is little left for the OS. If you are
consolidating then you should look at using Enterprise edition and Win2000
ADV Server (or Win2003) so you can use all 3GB for Sql Server and have a GB
for the OS and any other apps.
Andrew J. Kelly SQL MVP
"royHe" <royHe@.discussions.microsoft.com> wrote in message
news:8B395A5C-26F6-4E71-95B2-D9CE53E62100@.microsoft.com...
>I have a Dell server with 4GB of memory and 4 CPUs with 1.6G each, hosting
> one major application database. SQL Server 2000 Standard Edition was
> installed on the server, OS is Windows 2000 Server. Currently with single
> instance installed, I checked the memory usage using task manager, the
> wholes
> system can only use up to less then 2 GB of memory. The peak is less than
> 2
> GB. The system neven utilizing more than 2 GM of memory. I am in the
> process
> of doing server consolidation and planning to move another major
> application
> database to this dell server. I want to utilize 4 G of memory after I
> consolidate the other SQL server to the Dell Server. If I just move the
> database to this dell server without creating a new instance, the most
> memory
> we can utilize is 3GB with 3 GB switch set in boot.ini. If we create a new
> instance, does that mean we can utilize the other 2 GB of Memory on the
> server? Security is not a major concern when using single instance. What
> are
> the Pros and Cons?
> Thank you very much for your help.
>|||Thank you very much.
This is a dedicated SQL server. I find out in my situation the OS use very
little memory. Probably If I reserve 256M to 300M for the OS is enough.
Currently the peak for the OS Memory usage is around 250M.
"Andrew J. Kelly" wrote:
> You need Enterprise edition to use more than 2GB. Windows 2000 server do
es
> not use the /3GB switch other than in a test scenario. Yes you can use tw
o
> instances and each can use 2GB but you have to be careful as now you can
> lead to a situation where there is little left for the OS. If you are
> consolidating then you should look at using Enterprise edition and Win2000
> ADV Server (or Win2003) so you can use all 3GB for Sql Server and have a G
B
> for the OS and any other apps.
> --
> Andrew J. Kelly SQL MVP
>
> "royHe" <royHe@.discussions.microsoft.com> wrote in message
> news:8B395A5C-26F6-4E71-95B2-D9CE53E62100@.microsoft.com...
>
>sql
one major application database. SQL Server 2000 Standard Edition was
installed on the server, OS is Windows 2000 Server. Currently with single
instance installed, I checked the memory usage using task manager, the whole
s
system can only use up to less then 2 GB of memory. The peak is less than 2
GB. The system neven utilizing more than 2 GM of memory. I am in the proces
s
of doing server consolidation and planning to move another major application
database to this dell server. I want to utilize 4 G of memory after I
consolidate the other SQL server to the Dell Server. If I just move the
database to this dell server without creating a new instance, the most memor
y
we can utilize is 3GB with 3 GB switch set in boot.ini. If we create a new
instance, does that mean we can utilize the other 2 GB of Memory on the
server? Security is not a major concern when using single instance. What ar
e
the Pros and Cons?
Thank you very much for your help.You need Enterprise edition to use more than 2GB. Windows 2000 server does
not use the /3GB switch other than in a test scenario. Yes you can use two
instances and each can use 2GB but you have to be careful as now you can
lead to a situation where there is little left for the OS. If you are
consolidating then you should look at using Enterprise edition and Win2000
ADV Server (or Win2003) so you can use all 3GB for Sql Server and have a GB
for the OS and any other apps.
Andrew J. Kelly SQL MVP
"royHe" <royHe@.discussions.microsoft.com> wrote in message
news:8B395A5C-26F6-4E71-95B2-D9CE53E62100@.microsoft.com...
>I have a Dell server with 4GB of memory and 4 CPUs with 1.6G each, hosting
> one major application database. SQL Server 2000 Standard Edition was
> installed on the server, OS is Windows 2000 Server. Currently with single
> instance installed, I checked the memory usage using task manager, the
> wholes
> system can only use up to less then 2 GB of memory. The peak is less than
> 2
> GB. The system neven utilizing more than 2 GM of memory. I am in the
> process
> of doing server consolidation and planning to move another major
> application
> database to this dell server. I want to utilize 4 G of memory after I
> consolidate the other SQL server to the Dell Server. If I just move the
> database to this dell server without creating a new instance, the most
> memory
> we can utilize is 3GB with 3 GB switch set in boot.ini. If we create a new
> instance, does that mean we can utilize the other 2 GB of Memory on the
> server? Security is not a major concern when using single instance. What
> are
> the Pros and Cons?
> Thank you very much for your help.
>|||Thank you very much.
This is a dedicated SQL server. I find out in my situation the OS use very
little memory. Probably If I reserve 256M to 300M for the OS is enough.
Currently the peak for the OS Memory usage is around 250M.
"Andrew J. Kelly" wrote:
> You need Enterprise edition to use more than 2GB. Windows 2000 server do
es
> not use the /3GB switch other than in a test scenario. Yes you can use tw
o
> instances and each can use 2GB but you have to be careful as now you can
> lead to a situation where there is little left for the OS. If you are
> consolidating then you should look at using Enterprise edition and Win2000
> ADV Server (or Win2003) so you can use all 3GB for Sql Server and have a G
B
> for the OS and any other apps.
> --
> Andrew J. Kelly SQL MVP
>
> "royHe" <royHe@.discussions.microsoft.com> wrote in message
> news:8B395A5C-26F6-4E71-95B2-D9CE53E62100@.microsoft.com...
>
>sql
Multiple inserts in a single transaction
Hello All,
I have an application where i am doing multiple inserts inside a single transaction. Now to do this I have typically two options. One of them is to start a transaction from my application, use a loop and do inserts one by one and commit it once the last insert is done. This seems good but involves constant round trip from my application server to my database server. Other drawback is that if my applications fails (for instance there is a power failure at client) that there will be waiting involved for other queries as inserts hold exclusive locks.
The other approach is to write a stored procedure which takes xml as input. Now my application will send an xml file this SP and inside the SP using a cursor i will loop through and do the insert inside a single transaction.
Currently following the first strategy, would like to know whether the second strategy make sense and is better than the first one.
Thanks in advance :)
Thanks a lot. Had though on similar lines and now it just adds to my confidence on using the second approach. was just concerned with the way SQL server will handle the xml file|||
By the way i am totally unaware of the set-based inserts from xml. If u can plz elaborate|||
FYI i am using SQL Server 2005|||Hi, I'm doing something similar to this, only I'm currently using the first option Metesh mentioned. I'd like to use the second, but am not sure how to handle xml in my stored procedures, so that I can "directly insert" the values into the tables. Can anyone point me to some good resources? Thanks!|||
I have an application where i am doing multiple inserts inside a single transaction. Now to do this I have typically two options. One of them is to start a transaction from my application, use a loop and do inserts one by one and commit it once the last insert is done. This seems good but involves constant round trip from my application server to my database server. Other drawback is that if my applications fails (for instance there is a power failure at client) that there will be waiting involved for other queries as inserts hold exclusive locks.
The other approach is to write a stored procedure which takes xml as input. Now my application will send an xml file this SP and inside the SP using a cursor i will loop through and do the insert inside a single transaction.
Currently following the first strategy, would like to know whether the second strategy make sense and is better than the first one.
Thanks in advance :)
The second strategy is much improved.
The total time that the data will be locked in the transaction will be shortened by all of the network roundtrips currently experienced. If the process fails, it fails on the server and the TRANSACTION can be immediately rolled back, releasing the locked resources much sooner.
|||Hey Arnie,Thanks a lot. Had though on similar lines and now it just adds to my confidence on using the second approach. was just concerned with the way SQL server will handle the xml file|||
In addition, you might see further performance improvements if (inside the stored procedure) you perform the inserts in a set-based manner rather than by using cursors.
Chris
|||Hy Chris Thanks for the reply.By the way i am totally unaware of the set-based inserts from xml. If u can plz elaborate|||
Are you using SQL Server 2000 or SQL Server 2005?
Also, could you post a sample of your XML and describe how the data contained within the XML maps to your table structure?
Chris
|||Hey Chris thanks for the pointer, I directly inserted the values into the table instead of looping through the cursors. and it worked just as well.FYI i am using SQL Server 2005|||Hi, I'm doing something similar to this, only I'm currently using the first option Metesh mentioned. I'd like to use the second, but am not sure how to handle xml in my stored procedures, so that I can "directly insert" the values into the tables. Can anyone point me to some good resources? Thanks!|||
A good starting point would be the OPENXML topic in SQL Server 2005 BOL:
http://msdn2.microsoft.com/en-us/library/ms186918.aspx
Chris
sqlmultiple insert delays
I am trying to work on a java application that does multiple inserts to the informix database. The data is actually obtained from an online screen. I see that the performance is very poor when ever the user enteres more than 20 records. This is caused because of the 20 inserts performed with in the program. Is there anyway I could do this in one insert.
I know that the bulk insert could be done using INSERT into ..select from.
I cannot use this as I do not have my data in a table.
Please advice.Hello,
I dont know Informix very well, but when you want to insert 20 records in a database, the execution time must be less than 1 second. For example 500-1000 insert on an oracle database will take 1 second (depends on the machine and network and so on). This is the execution time that we reach in various projects.
So - there must be a problem with your application or your network. Did you trace your application ? So that you can see what is happening ?
Greetings
Manfred Peter
(Alligator Company)
http://www.alligatorsql.com
I know that the bulk insert could be done using INSERT into ..select from.
I cannot use this as I do not have my data in a table.
Please advice.Hello,
I dont know Informix very well, but when you want to insert 20 records in a database, the execution time must be less than 1 second. For example 500-1000 insert on an oracle database will take 1 second (depends on the machine and network and so on). This is the execution time that we reach in various projects.
So - there must be a problem with your application or your network. Did you trace your application ? So that you can see what is happening ?
Greetings
Manfred Peter
(Alligator Company)
http://www.alligatorsql.com
Friday, March 23, 2012
Multiple flat files to multiple tables
Hi,
I have searched but not found quite the best way to look at this so far..
I have an application that outputs data to several text files (up to 30). These have commonality by an object name, but then contain completely different column data.
In DTS I had each of the source text file connections going to one OLE DB connection and then individual transform data tasks pointing to the one OLE DB connection.
Looking at SSIS, it would appear that I would need to have one source and one destination for each of these and therefore 30 parallel data flows?
Just wondering if there is a neater way of doing this?
It is a regular data import that happens a few times a day - the text files are named the same as the SQL tables - ie app_userdata.txt goes to app_userdata table.
Hope that explains ok and thanks in advance.
Mike
Since each source file and each destination table has its own schema, I don't know of any better way than what you describe.
Personally I would rather see 30 packages, each with one data flow, rather than one package with 30 data flows, but that's largely a personal preference, not a hard and fast rule or anything...
|||Thanks for the response - hadn't considered a package for each so will take a look at that|||The advantage that I see in the "one package per destination table" approach is that it makes ongoing maintenance much simpler. For smaller projects it seems like overkill, but it serves me well...Multiple FKs of the same PK problem!
Hi SQL folks,
I have a fuzzy problem with my application,
I'm building a small internal messaging system. I have two tables: SysUsers and mail tables.
I need to select both the "mailFrom" and "mailTo" from the mail table - both of them is a uniqueidenifier and are FK from the UserId in the SysUsers table- , and then get the correspoding UserName from the SysUsers table, how can I do that?
Here is my query:
/*Get the UserName of both the Sender and Reciever*/
SELECT mail.messageId, mail.messageSubject, mail.mailFrom, mail.mailTo, SysUsers.UserName
FROM mail INNER JOIN
SysUsers ON mail.mailTo= SysUsers.UserId
AND mail.mailFrom= SysUsers.UserId
/*Get only the current users mails*/
WHERE (SysUsers.UserId = @.UserId)
Hello Jocker,
You have to define two inner join to the same table with different aliasses.
SELECT ... , MailTo.UserName, MailFrom.UserName
FROM mail
INNER JOIN SysUsers AS MailTo ON MailTo.UserId = mail.mailTo
INNER JOIN SysUsers AS MailFrom ON MailFrom.UserId = mail.mailFrom
WHERE (MailTo.UserId = @.UserId OR MailFrom.UserId = @.UserId)
|||You need to alias the Sysusers Table
SELECT mail.messageId, mail.messageSubject, mail.mailFrom, mail.mailTo,
S1.UserName AS UserNameTo,S2.UserName AS UserNameFrom
FROM mail
INNER JOIN SysUsers S1 ON mail.mailTo = S1.UserId
INNER JOIN SysUsers S2 ON mail.mailFrom = S2.UserId
WHERE (S1.UserId = @.UserId)
|||
jeroenm:
Hello Jocker,
You have to define two inner join to the same table with different aliasses.
SELECT ... , MailTo.UserName, MailFrom.UserName
FROM mail
INNER JOIN SysUsers AS MailTo ON MailTo.UserId = mail.mailTo
INNER JOIN SysUsers AS MailFrom ON MailFrom.UserId = mail.mailFrom
WHERE (MailTo.UserId = @.UserId OR MailFrom.UserId = @.UserId)
Hi jepenm,
Thanks for you responce :)
|||
You need to alias the Sysusers Table
SELECT mail.messageId, mail.messageSubject, mail.mailFrom, mail.mailTo,
S1.UserName AS UserNameTo,S2.UserName AS UserNameFrom
FROM mail
INNER JOIN SysUsers S1 ON mail.mailTo = S1.UserId
INNER JOIN SysUsers S2 ON mail.mailFrom = S2.UserIdWHERE (S1.UserId = @.UserId)
Hi TATWORTH,
Thanks for your response
Monday, March 12, 2012
multiple databases for multiple days
I'd like get some opinions on this matter before I go Godzilla in the
next meeting.
My application fills a database during the day, while a webservice
reads from the data. At the end of the day I update statistics,
defrag indexes, do a shrink file and make it read only. Then I create
a new database at midnight and start filling that one. The databases
don't get too large, around 50-100 megs per day. The webservice makes
queries on the older data as well. I choose the multiple database
technique because customers could easily control their data and because
I thought it'd be faster than one large database.
Well, now a sub contractor is cryin' because they need to make
multi-day queries for their reports. They say it'd be too difficult
to do that, and I should just have one db for all of the data. This
is where my newbieness shows. I know there aren't too many specifics
here, but does this request seem absurd? Is it a such a big deal to
do "use db1;select * from table;use db2;select * from table"?Hi
Why separate the data into days? One big DB is a lot easier to manage,
backup and report on than lots of small ones.
This is not the old days of ISAM DB's. One big DB is the way to go.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Johnny Ruin" <schafer.dave@.gmail.com> wrote in message
news:1133479375.134870.146930@.g14g2000cwa.googlegroups.com...
> I'd like get some opinions on this matter before I go Godzilla in the
> next meeting.
> My application fills a database during the day, while a webservice
> reads from the data. At the end of the day I update statistics,
> defrag indexes, do a shrink file and make it read only. Then I create
> a new database at midnight and start filling that one. The databases
> don't get too large, around 50-100 megs per day. The webservice makes
> queries on the older data as well. I choose the multiple database
> technique because customers could easily control their data and because
> I thought it'd be faster than one large database.
> Well, now a sub contractor is cryin' because they need to make
> multi-day queries for their reports. They say it'd be too difficult
> to do that, and I should just have one db for all of the data. This
> is where my newbieness shows. I know there aren't too many specifics
> here, but does this request seem absurd? Is it a such a big deal to
> do "use db1;select * from table;use db2;select * from table"?
>|||Well, I thought the seperate dbs would be faster. What happens in a
year, when the db is 24 gigs? It seems strange to think that it
wouldn't be significantly slower than a 50 meg db.|||Presumably at some point you will be removing the old data -otherwise this
thing will just keep on growing?
If not then in a few years you will have *hundreds* of seperate databases,
then some requirement will come along that means you need to change the db
design, (it will happen) and you will have a real problem keeping them all
in step.
Appropriate indexing can mean that all in one db can perform well.
I suspect the reason they are crying is how is the report meant to know
which dbs to hit - they will have to dynamically build the sql to use each
db in turn, and that list is growing. What happens if the report starts at
11:55 and finishes at 00:05 - a new db has popped into existence in the
middle.
24Gig is not an issue, provided you index correctly, If you ppst the DDL for
the tables together with some typical queries someone is sure to suggest
various options. Also I'd look at the index tuning wizard on frequent
queries. Raw size will only really affect things if you are scanning large
tables.
Mike John
"Johnny Ruin" <schafer.dave@.gmail.com> wrote in message
news:1133493073.595944.279090@.o13g2000cwo.googlegroups.com...
> Well, I thought the seperate dbs would be faster. What happens in a
> year, when the db is 24 gigs? It seems strange to think that it
> wouldn't be significantly slower than a 50 meg db.
>|||Thanks for your comments, fellas. I'll reconsider my aproach.|||Johnny Ruin wrote:
> Well, I thought the seperate dbs would be faster. What happens in a
> year, when the db is 24 gigs? It seems strange to think that it
> wouldn't be significantly slower than a 50 meg db.
24 GB is a small database. At that size, good indexing will certainly
have much more impact on performance than partitioning the data will.
David Portas
SQL Server MVP
--|||So, would you guys see any benefit to doing tableMMddYYYY, or would you
just go with one table period?|||Johnny Ruin wrote:
> So, would you guys see any benefit to doing tableMMddYYYY, or would you
> just go with one table period?
I'd certainly go with one table unless there was proven evidence of
some benefit from partitioning - that probably means for larger data
sets than you are talking about here. I definitely would never go for
partitioning of 50-100MB per table per day. That would just be totally
insane, and probably even damaging to performance - depending on how
the data is used.
David Portas
SQL Server MVP
--|||I would DEFINITELY not use table names with a naming convention of MMddYYYY
as it is ambiguous.
Horizontal partitioning on a daily basis sounds excessive and will (I think)
give you the same reporting problems you face with a seperate db per day.
I would start with the intention of a single table, then IF it causes
problems artificially split it based on a date range, define appropriate
constraints on each newly created table, and a union view across them so the
app still thinks it has one table. That way you are logically maintaining a
single view of the data and hiding your physical tweaks that are only there
for performance reasons.
Mike John
"Johnny Ruin" <schafer.dave@.gmail.com> wrote in message
news:1133535121.678475.286050@.o13g2000cwo.googlegroups.com...
> So, would you guys see any benefit to doing tableMMddYYYY, or would you
> just go with one table period?
>
next meeting.
My application fills a database during the day, while a webservice
reads from the data. At the end of the day I update statistics,
defrag indexes, do a shrink file and make it read only. Then I create
a new database at midnight and start filling that one. The databases
don't get too large, around 50-100 megs per day. The webservice makes
queries on the older data as well. I choose the multiple database
technique because customers could easily control their data and because
I thought it'd be faster than one large database.
Well, now a sub contractor is cryin' because they need to make
multi-day queries for their reports. They say it'd be too difficult
to do that, and I should just have one db for all of the data. This
is where my newbieness shows. I know there aren't too many specifics
here, but does this request seem absurd? Is it a such a big deal to
do "use db1;select * from table;use db2;select * from table"?Hi
Why separate the data into days? One big DB is a lot easier to manage,
backup and report on than lots of small ones.
This is not the old days of ISAM DB's. One big DB is the way to go.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Johnny Ruin" <schafer.dave@.gmail.com> wrote in message
news:1133479375.134870.146930@.g14g2000cwa.googlegroups.com...
> I'd like get some opinions on this matter before I go Godzilla in the
> next meeting.
> My application fills a database during the day, while a webservice
> reads from the data. At the end of the day I update statistics,
> defrag indexes, do a shrink file and make it read only. Then I create
> a new database at midnight and start filling that one. The databases
> don't get too large, around 50-100 megs per day. The webservice makes
> queries on the older data as well. I choose the multiple database
> technique because customers could easily control their data and because
> I thought it'd be faster than one large database.
> Well, now a sub contractor is cryin' because they need to make
> multi-day queries for their reports. They say it'd be too difficult
> to do that, and I should just have one db for all of the data. This
> is where my newbieness shows. I know there aren't too many specifics
> here, but does this request seem absurd? Is it a such a big deal to
> do "use db1;select * from table;use db2;select * from table"?
>|||Well, I thought the seperate dbs would be faster. What happens in a
year, when the db is 24 gigs? It seems strange to think that it
wouldn't be significantly slower than a 50 meg db.|||Presumably at some point you will be removing the old data -otherwise this
thing will just keep on growing?
If not then in a few years you will have *hundreds* of seperate databases,
then some requirement will come along that means you need to change the db
design, (it will happen) and you will have a real problem keeping them all
in step.
Appropriate indexing can mean that all in one db can perform well.
I suspect the reason they are crying is how is the report meant to know
which dbs to hit - they will have to dynamically build the sql to use each
db in turn, and that list is growing. What happens if the report starts at
11:55 and finishes at 00:05 - a new db has popped into existence in the
middle.
24Gig is not an issue, provided you index correctly, If you ppst the DDL for
the tables together with some typical queries someone is sure to suggest
various options. Also I'd look at the index tuning wizard on frequent
queries. Raw size will only really affect things if you are scanning large
tables.
Mike John
"Johnny Ruin" <schafer.dave@.gmail.com> wrote in message
news:1133493073.595944.279090@.o13g2000cwo.googlegroups.com...
> Well, I thought the seperate dbs would be faster. What happens in a
> year, when the db is 24 gigs? It seems strange to think that it
> wouldn't be significantly slower than a 50 meg db.
>|||Thanks for your comments, fellas. I'll reconsider my aproach.|||Johnny Ruin wrote:
> Well, I thought the seperate dbs would be faster. What happens in a
> year, when the db is 24 gigs? It seems strange to think that it
> wouldn't be significantly slower than a 50 meg db.
24 GB is a small database. At that size, good indexing will certainly
have much more impact on performance than partitioning the data will.
David Portas
SQL Server MVP
--|||So, would you guys see any benefit to doing tableMMddYYYY, or would you
just go with one table period?|||Johnny Ruin wrote:
> So, would you guys see any benefit to doing tableMMddYYYY, or would you
> just go with one table period?
I'd certainly go with one table unless there was proven evidence of
some benefit from partitioning - that probably means for larger data
sets than you are talking about here. I definitely would never go for
partitioning of 50-100MB per table per day. That would just be totally
insane, and probably even damaging to performance - depending on how
the data is used.
David Portas
SQL Server MVP
--|||I would DEFINITELY not use table names with a naming convention of MMddYYYY
as it is ambiguous.
Horizontal partitioning on a daily basis sounds excessive and will (I think)
give you the same reporting problems you face with a seperate db per day.
I would start with the intention of a single table, then IF it causes
problems artificially split it based on a date range, define appropriate
constraints on each newly created table, and a union view across them so the
app still thinks it has one table. That way you are logically maintaining a
single view of the data and hiding your physical tweaks that are only there
for performance reasons.
Mike John
"Johnny Ruin" <schafer.dave@.gmail.com> wrote in message
news:1133535121.678475.286050@.o13g2000cwo.googlegroups.com...
> So, would you guys see any benefit to doing tableMMddYYYY, or would you
> just go with one table period?
>
multiple databases
Dear support,
I have a SQL server with multiple databases( say Database A to E) on a same
instance.
This is my scenario:
I have an application, say application A, which read/writes to Database A, b
ut also read-only data from B,C,D and E database. Currently, I have given a
user, say User A, full access right(both read/write) on all these databases
.
If I want to tight up the security by enforcing read/write on the A database
, but read only on the B,C,D,E database for user A by the use of user define
d role, is it possible? This way, any new recruit is hired, I will just add
the role to the new user.
However, I have trouble trying to define a database role, which will give me
options to assign access rights or permissions on multiple databases.
Any suggestions?
Thank you!
MartinHi
If you access the read only database through views, stored procedures or
user defined functions then the owner of the view/stored procedure/UDF will
require the access.
See "Using Ownership Chains" in Books Online:
mk:@.MSITStore:C:\Program%20Files\Microso
ft%20SQL%20Server\80\Tools\Books\adm
insql.chm::/ad_security_4iyb.htm
If you can make the database read only then this will make everything more
secure.
John
"Martin" <anonymous@.discussions.microsoft.com> wrote in message
news:EC7A94A0-5283-475B-9651-2360DA0DF075@.microsoft.com...
same instance.
but also read-only data from B,C,D and E database. Currently, I have given
a user, say User A, full access right(both read/write) on all these
databases.
database, but read only on the B,C,D,E database for user A by the use of
user defined role, is it possible? This way, any new recruit is hired, I
will just add the role to the new user. However, I have trouble trying to
define a database role, which will give me options to assign access rights
or permissions on multiple databases.
I have a SQL server with multiple databases( say Database A to E) on a same
instance.
This is my scenario:
I have an application, say application A, which read/writes to Database A, b
ut also read-only data from B,C,D and E database. Currently, I have given a
user, say User A, full access right(both read/write) on all these databases
.
If I want to tight up the security by enforcing read/write on the A database
, but read only on the B,C,D,E database for user A by the use of user define
d role, is it possible? This way, any new recruit is hired, I will just add
the role to the new user.
However, I have trouble trying to define a database role, which will give me
options to assign access rights or permissions on multiple databases.
Any suggestions?
Thank you!
MartinHi
If you access the read only database through views, stored procedures or
user defined functions then the owner of the view/stored procedure/UDF will
require the access.
See "Using Ownership Chains" in Books Online:
mk:@.MSITStore:C:\Program%20Files\Microso
ft%20SQL%20Server\80\Tools\Books\adm
insql.chm::/ad_security_4iyb.htm
If you can make the database read only then this will make everything more
secure.
John
"Martin" <anonymous@.discussions.microsoft.com> wrote in message
news:EC7A94A0-5283-475B-9651-2360DA0DF075@.microsoft.com...
quote:
> Dear support,
> I have a SQL server with multiple databases( say Database A to E) on a
same instance.
quote:
> This is my scenario:
> I have an application, say application A, which read/writes to Database A,
but also read-only data from B,C,D and E database. Currently, I have given
a user, say User A, full access right(both read/write) on all these
databases.
quote:
> If I want to tight up the security by enforcing read/write on the A
database, but read only on the B,C,D,E database for user A by the use of
user defined role, is it possible? This way, any new recruit is hired, I
will just add the role to the new user. However, I have trouble trying to
define a database role, which will give me options to assign access rights
or permissions on multiple databases.
quote:|||Thanks John!
> Any suggestions?
> Thank you!
> Martin
>
multiple databases
Dear support,
I have a SQL server with multiple databases( say Database A to E) on a same instance.
This is my scenario:
I have an application, say application A, which read/writes to Database A, but also read-only data from B,C,D and E database. Currently, I have given a user, say User A, full access right(both read/write) on all these databases.
If I want to tight up the security by enforcing read/write on the A database, but read only on the B,C,D,E database for user A by the use of user defined role, is it possible? This way, any new recruit is hired, I will just add the role to the new user. However, I have trouble trying to define a database role, which will give me options to assign access rights or permissions on multiple databases.
Any suggestions?
Thank you!
MartinHi
If you access the read only database through views, stored procedures or
user defined functions then the owner of the view/stored procedure/UDF will
require the access.
See "Using Ownership Chains" in Books Online:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\adm
insql.chm::/ad_security_4iyb.htm
If you can make the database read only then this will make everything more
secure.
John
"Martin" <anonymous@.discussions.microsoft.com> wrote in message
news:EC7A94A0-5283-475B-9651-2360DA0DF075@.microsoft.com...
> Dear support,
> I have a SQL server with multiple databases( say Database A to E) on a
same instance.
> This is my scenario:
> I have an application, say application A, which read/writes to Database A,
but also read-only data from B,C,D and E database. Currently, I have given
a user, say User A, full access right(both read/write) on all these
databases.
> If I want to tight up the security by enforcing read/write on the A
database, but read only on the B,C,D,E database for user A by the use of
user defined role, is it possible? This way, any new recruit is hired, I
will just add the role to the new user. However, I have trouble trying to
define a database role, which will give me options to assign access rights
or permissions on multiple databases.
> Any suggestions?
> Thank you!
> Martin
>|||Thanks John!
I have a SQL server with multiple databases( say Database A to E) on a same instance.
This is my scenario:
I have an application, say application A, which read/writes to Database A, but also read-only data from B,C,D and E database. Currently, I have given a user, say User A, full access right(both read/write) on all these databases.
If I want to tight up the security by enforcing read/write on the A database, but read only on the B,C,D,E database for user A by the use of user defined role, is it possible? This way, any new recruit is hired, I will just add the role to the new user. However, I have trouble trying to define a database role, which will give me options to assign access rights or permissions on multiple databases.
Any suggestions?
Thank you!
MartinHi
If you access the read only database through views, stored procedures or
user defined functions then the owner of the view/stored procedure/UDF will
require the access.
See "Using Ownership Chains" in Books Online:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\adm
insql.chm::/ad_security_4iyb.htm
If you can make the database read only then this will make everything more
secure.
John
"Martin" <anonymous@.discussions.microsoft.com> wrote in message
news:EC7A94A0-5283-475B-9651-2360DA0DF075@.microsoft.com...
> Dear support,
> I have a SQL server with multiple databases( say Database A to E) on a
same instance.
> This is my scenario:
> I have an application, say application A, which read/writes to Database A,
but also read-only data from B,C,D and E database. Currently, I have given
a user, say User A, full access right(both read/write) on all these
databases.
> If I want to tight up the security by enforcing read/write on the A
database, but read only on the B,C,D,E database for user A by the use of
user defined role, is it possible? This way, any new recruit is hired, I
will just add the role to the new user. However, I have trouble trying to
define a database role, which will give me options to assign access rights
or permissions on multiple databases.
> Any suggestions?
> Thank you!
> Martin
>|||Thanks John!
Multiple database question
I am planning to develop reports using reporting services. My reports will use data from 3 different SQL Server databases that are application specific on three different cpu's. I'm planning on having a reporting database that is a common repository for data extraction / summarization from the other 3 databases.
I'm trying to understand the best way to approach my design (it's been a while since I've done database work - pretty obvious...):
Use Analysis Services to move the data from the three databases to the reporting database server.
Create some stored procedures that can do this.
I'm not seeing the reporting services as being a "data movement" tool but more of a shell data access tool (correct?).
Other tools someone would suggest.
Thanks and any help would be appreciated.You could also look at replication,DTS, and even log shipping.|||Hi there,
If your (or some) reports need to COMBINE the data from the three databases, you definitely need a common data access like you mentionned. But if you have "Report A" that needs the data from "Database A" and "Report B" from "Database B", that's a breeze.
Each report has it's own datasource setup. That's it!|||tootoo, there's nothing wrong with your solution. However, the snapshot approach might be needed as reporting can sometimes crucify the perf of a database.
Multiple Database hits vs Bulk Data parameters
I was curious to know if it the amount of data sent to the sql server mattered.I am working on a web application and I have three stored procedures that most likely will be called one after the other. Each procedure accepts at least 4 parameters. Instead if I create one stored procedure, then I will be passing at least 12 parameters. Some of the parameters could be quite bulky(at least 1000 characters).So which one is better, 1 stored procedure with 12 parameters or 3 stored procedures with 4 parameters each called one after the other.Thanks
For SQL Server, the cost of parsing parameters can always be ingonred compared with the cost of compiling a store procedure and producing a optimized execution plan for it.
From network I/O aspect, each execution of a stored procedure is considered as a 'batch' (batch in SQL means all data will be processed and returned at one time, which can greatly improve network performance). So if you use 3 stored procedures, you need 3 rounds; while 1 stored procedure need only 1 round.
So generally I suggest you use 1 stored procedure with 12 parameters.
Multiple database access problem
Hi ,
Im developing a web application which would make access to multiple distributive databases. The problem is...the database from all the third parties may have different standard.
For instance...a user wish to retrive a record from many databases, he input "Pentium 4" ....but some database may have different standard, they may have a record with name "P4" instead of "Pentium 4"...so my question is , how should i make it standard ?? thanksEither use translation tables or enforce standard values through the use of foreign keys to static lookup lists.|||Great, would appreciate if you could describe more in detail ...thanks a lot|||A translation table is a table in a central database that contains the datasource, foreignvalue, and commonvalue for all the elements that need to be combined across databases.
Lookup tables are tables within each database that limit the user's choices to a selection of consistent values.
Im developing a web application which would make access to multiple distributive databases. The problem is...the database from all the third parties may have different standard.
For instance...a user wish to retrive a record from many databases, he input "Pentium 4" ....but some database may have different standard, they may have a record with name "P4" instead of "Pentium 4"...so my question is , how should i make it standard ?? thanksEither use translation tables or enforce standard values through the use of foreign keys to static lookup lists.|||Great, would appreciate if you could describe more in detail ...thanks a lot|||A translation table is a table in a central database that contains the datasource, foreignvalue, and commonvalue for all the elements that need to be combined across databases.
Lookup tables are tables within each database that limit the user's choices to a selection of consistent values.
Friday, March 9, 2012
Multiple Customers Same Database
We have a client that has a web application that is accessed by multiple
customers. They are using one database to store all data for all customers.
One potential customer is concerned about storing the data this way. They ar
e
worried that their data could be accessed by other customers.
Our client has asked us to come up with a list of pros and cons of storing
all customers data in the same database. We have brainstormed and came up
with a few. Can any of you offer your thoughts?
--
Thanks,
Dan Jurden
http://www.eps-cs.comDan,
If you have a large number of customers, then maintaining those databases
via Enterprise Manager can be challenging. You also have to deal with
upgrades, such as a schema change across all those databases, as well as the
fact that you'll have to duplicate stored procedures (most likely) across
all the dbs.
On the other hand, for a relatively small number of customers, say less than
20 or so, having them all in separate databases is much much cleaner.
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Dan Jurden" <DanJurden@.discussions.microsoft.com> wrote in message
news:1FFE95EE-645E-4B01-A5FC-D351F1B92467@.microsoft.com...
> We have a client that has a web application that is accessed by multiple
> customers. They are using one database to store all data for all
> customers.
> One potential customer is concerned about storing the data this way. They
> are
> worried that their data could be accessed by other customers.
> Our client has asked us to come up with a list of pros and cons of storing
> all customers data in the same database. We have brainstormed and came up
> with a few. Can any of you offer your thoughts?
> --
> Thanks,
> Dan Jurden
> http://www.eps-cs.com
customers. They are using one database to store all data for all customers.
One potential customer is concerned about storing the data this way. They ar
e
worried that their data could be accessed by other customers.
Our client has asked us to come up with a list of pros and cons of storing
all customers data in the same database. We have brainstormed and came up
with a few. Can any of you offer your thoughts?
--
Thanks,
Dan Jurden
http://www.eps-cs.comDan,
If you have a large number of customers, then maintaining those databases
via Enterprise Manager can be challenging. You also have to deal with
upgrades, such as a schema change across all those databases, as well as the
fact that you'll have to duplicate stored procedures (most likely) across
all the dbs.
On the other hand, for a relatively small number of customers, say less than
20 or so, having them all in separate databases is much much cleaner.
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Dan Jurden" <DanJurden@.discussions.microsoft.com> wrote in message
news:1FFE95EE-645E-4B01-A5FC-D351F1B92467@.microsoft.com...
> We have a client that has a web application that is accessed by multiple
> customers. They are using one database to store all data for all
> customers.
> One potential customer is concerned about storing the data this way. They
> are
> worried that their data could be accessed by other customers.
> Our client has asked us to come up with a list of pros and cons of storing
> all customers data in the same database. We have brainstormed and came up
> with a few. Can any of you offer your thoughts?
> --
> Thanks,
> Dan Jurden
> http://www.eps-cs.com
Multiple Customers Same Database
We have a client that has a web application that is accessed by multiple
customers. They are using one database to store all data for all customers.
One potential customer is concerned about storing the data this way. They are
worried that their data could be accessed by other customers.
Our client has asked us to come up with a list of pros and cons of storing
all customers data in the same database. We have brainstormed and came up
with a few. Can any of you offer your thoughts?
Thanks,
Dan Jurden
http://www.eps-cs.com
Dan,
If you have a large number of customers, then maintaining those databases
via Enterprise Manager can be challenging. You also have to deal with
upgrades, such as a schema change across all those databases, as well as the
fact that you'll have to duplicate stored procedures (most likely) across
all the dbs.
On the other hand, for a relatively small number of customers, say less than
20 or so, having them all in separate databases is much much cleaner.
Hope this helps,
Ron
Ron Talmage
SQL Server MVP
"Dan Jurden" <DanJurden@.discussions.microsoft.com> wrote in message
news:1FFE95EE-645E-4B01-A5FC-D351F1B92467@.microsoft.com...
> We have a client that has a web application that is accessed by multiple
> customers. They are using one database to store all data for all
> customers.
> One potential customer is concerned about storing the data this way. They
> are
> worried that their data could be accessed by other customers.
> Our client has asked us to come up with a list of pros and cons of storing
> all customers data in the same database. We have brainstormed and came up
> with a few. Can any of you offer your thoughts?
> --
> Thanks,
> Dan Jurden
> http://www.eps-cs.com
customers. They are using one database to store all data for all customers.
One potential customer is concerned about storing the data this way. They are
worried that their data could be accessed by other customers.
Our client has asked us to come up with a list of pros and cons of storing
all customers data in the same database. We have brainstormed and came up
with a few. Can any of you offer your thoughts?
Thanks,
Dan Jurden
http://www.eps-cs.com
Dan,
If you have a large number of customers, then maintaining those databases
via Enterprise Manager can be challenging. You also have to deal with
upgrades, such as a schema change across all those databases, as well as the
fact that you'll have to duplicate stored procedures (most likely) across
all the dbs.
On the other hand, for a relatively small number of customers, say less than
20 or so, having them all in separate databases is much much cleaner.
Hope this helps,
Ron
Ron Talmage
SQL Server MVP
"Dan Jurden" <DanJurden@.discussions.microsoft.com> wrote in message
news:1FFE95EE-645E-4B01-A5FC-D351F1B92467@.microsoft.com...
> We have a client that has a web application that is accessed by multiple
> customers. They are using one database to store all data for all
> customers.
> One potential customer is concerned about storing the data this way. They
> are
> worried that their data could be accessed by other customers.
> Our client has asked us to come up with a list of pros and cons of storing
> all customers data in the same database. We have brainstormed and came up
> with a few. Can any of you offer your thoughts?
> --
> Thanks,
> Dan Jurden
> http://www.eps-cs.com
Multiple Customers Same Database
We have a client that has a web application that is accessed by multiple
customers. They are using one database to store all data for all customers.
One potential customer is concerned about storing the data this way. They are
worried that their data could be accessed by other customers.
Our client has asked us to come up with a list of pros and cons of storing
all customers data in the same database. We have brainstormed and came up
with a few. Can any of you offer your thoughts?
--
Thanks,
Dan Jurden
http://www.eps-cs.comDan,
If you have a large number of customers, then maintaining those databases
via Enterprise Manager can be challenging. You also have to deal with
upgrades, such as a schema change across all those databases, as well as the
fact that you'll have to duplicate stored procedures (most likely) across
all the dbs.
On the other hand, for a relatively small number of customers, say less than
20 or so, having them all in separate databases is much much cleaner.
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Dan Jurden" <DanJurden@.discussions.microsoft.com> wrote in message
news:1FFE95EE-645E-4B01-A5FC-D351F1B92467@.microsoft.com...
> We have a client that has a web application that is accessed by multiple
> customers. They are using one database to store all data for all
> customers.
> One potential customer is concerned about storing the data this way. They
> are
> worried that their data could be accessed by other customers.
> Our client has asked us to come up with a list of pros and cons of storing
> all customers data in the same database. We have brainstormed and came up
> with a few. Can any of you offer your thoughts?
> --
> Thanks,
> Dan Jurden
> http://www.eps-cs.com
customers. They are using one database to store all data for all customers.
One potential customer is concerned about storing the data this way. They are
worried that their data could be accessed by other customers.
Our client has asked us to come up with a list of pros and cons of storing
all customers data in the same database. We have brainstormed and came up
with a few. Can any of you offer your thoughts?
--
Thanks,
Dan Jurden
http://www.eps-cs.comDan,
If you have a large number of customers, then maintaining those databases
via Enterprise Manager can be challenging. You also have to deal with
upgrades, such as a schema change across all those databases, as well as the
fact that you'll have to duplicate stored procedures (most likely) across
all the dbs.
On the other hand, for a relatively small number of customers, say less than
20 or so, having them all in separate databases is much much cleaner.
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Dan Jurden" <DanJurden@.discussions.microsoft.com> wrote in message
news:1FFE95EE-645E-4B01-A5FC-D351F1B92467@.microsoft.com...
> We have a client that has a web application that is accessed by multiple
> customers. They are using one database to store all data for all
> customers.
> One potential customer is concerned about storing the data this way. They
> are
> worried that their data could be accessed by other customers.
> Our client has asked us to come up with a list of pros and cons of storing
> all customers data in the same database. We have brainstormed and came up
> with a few. Can any of you offer your thoughts?
> --
> Thanks,
> Dan Jurden
> http://www.eps-cs.com
Saturday, February 25, 2012
Multiple Client on the same ReportServer
Hi !
Once again i have a question 
I was looking in the Report Manager but i couldn't find how to 'hide' a folder from user using the security option. Maybe we haven't configuer ReportServer propely. How can we solve this problem ? Is there a good website that explain how to do this ?I've forgot to ask something else. Currently we have made over 600 reports. All our Clients can use thoses reports but we don't want to allow them to modify them. Is it possible to hide a project from all our Client ?|||Anyone ? I really need to find a solution because we can let a Client have access to reports of another client.
Thanks ! Sorry about my bad English ^_^
Monday, February 20, 2012
multiple application in the same db
Please correct me if I'm wrong, but isn't it bad parctice to place multiple applications in the sam db. My SA wants me to use the same db for all of the apps I develop and then just name the tables accordingly. Can someone please give more insight on why this is bad, and my chances for doing it right the first time will be better. Thanks.There is no one rule to judge this. It all depends on the nature of your application. Are all of them data centric applications? Do they have large batch jobs running in the DB server all the time? Are they just providing UI for users to enter data? If all your applications don't justify to have their own database, I don't think your SA is so far away off on this.|||It also keeps the number of frivolous(sp?) databases down so there isn't a new database every week that needs to be added to the maintenance and backup jobs.
When one of your applications grows into large enough of a project, you'll be able to twist some PHB's arm and get it it's own real database.|||Thanks, I guess I was wrong. I'm always paranoid about nesting muliple objects under one source when multiple people have access. I assumed that the chance for naming errors were very high. Thanks again.
When one of your applications grows into large enough of a project, you'll be able to twist some PHB's arm and get it it's own real database.|||Thanks, I guess I was wrong. I'm always paranoid about nesting muliple objects under one source when multiple people have access. I assumed that the chance for naming errors were very high. Thanks again.
Multiple Ado Connections
Hi, I have a VB application running on SQL Enterprise Edition. Physically there are only 20 users loging in to the software at one time. However i can see more that 12 SPIDs for each user. The developers have been opening a new connection for each form in the application i guess. Is it normal or is there any specific requirement that foces them to do so? Shall i need to change the default WORKER-THREADS option on the server for peformance? Suggestions please!!!
Regards.They should close connections when the retrieving of data is completed
(or updates, or inserts are completed)
one connection per user is the best way to control connection leaks
(when developpers forget to close an unusefull connection)|||Means that they can open multiple connections for retrieving data for multiple forms however the connections must be closed later. What about the max-worker threads. Is there any need to change the settings, for i have more that 300 SPIDs at a time on the server.
Thanx for the kind response.|||programs are almost always "linear"
> open connection
> retrieve data1
> close connection
> open connection
> retrieve data2
> close connection
...
even if you've got 300 users there's rarely 300 connections
opened at the same time because they are closed most of the time
(except if users do big SELECTs on your database connections will
be opened longer)
Originally posted by TALAT
Means that they can open multiple connections for retrieving data for multiple forms however the connections must be closed later. What about the max-worker threads. Is there any need to change the settings, for i have more that 300 SPIDs at a time on the server.
Thanx for the kind response.|||The developers(genius) say that they close the connections in an error handler routine only, i.e., if an exception is thrown; they have written the code to close the connection for that particular form, otherwise not. What a nice coding technique they have adopted.
Regards.|||they could open a connection and leave it opened
but they should reuse this connection
instead of reopening new ones
this could be an other way to control the connection leaks
> start program : open ONE connection
> window1
> window2
> use program
> use program
...
> end program : close THE connection
in this case
Nb users = Nb connections
but if you have for example a max nb of connections at 100
only 100 users can connect at the same time
but with the other solution (open/close unuseful connection)
you can have more users than the max connections (100 in this example)
the users are not connected all the time of the use of the program
and they do not connect at the same time
if the max connections is reached a user just has to wait a little for a
connection to be liberated by an other user|||good luck with your consuming-connections-developper
just say to him that a connection is a very precious thing to waste
many users dreamed of having one free
and db-administrator cannot multiply them indefinitly
If ONE should use a connection
ONE should use it shortly
ONE should think of liberating it rapidly
ONE should carefully close it
ONE should know that ONE is not alone
For MANY use the program
Even if only ONE develops it|||I shall rather force them to stop doing this and to change their coding techiniques. Thanx for guiding and responding so quick. Thanx again!!
Regards.
Regards.They should close connections when the retrieving of data is completed
(or updates, or inserts are completed)
one connection per user is the best way to control connection leaks
(when developpers forget to close an unusefull connection)|||Means that they can open multiple connections for retrieving data for multiple forms however the connections must be closed later. What about the max-worker threads. Is there any need to change the settings, for i have more that 300 SPIDs at a time on the server.
Thanx for the kind response.|||programs are almost always "linear"
> open connection
> retrieve data1
> close connection
> open connection
> retrieve data2
> close connection
...
even if you've got 300 users there's rarely 300 connections
opened at the same time because they are closed most of the time
(except if users do big SELECTs on your database connections will
be opened longer)
Originally posted by TALAT
Means that they can open multiple connections for retrieving data for multiple forms however the connections must be closed later. What about the max-worker threads. Is there any need to change the settings, for i have more that 300 SPIDs at a time on the server.
Thanx for the kind response.|||The developers(genius) say that they close the connections in an error handler routine only, i.e., if an exception is thrown; they have written the code to close the connection for that particular form, otherwise not. What a nice coding technique they have adopted.
Regards.|||they could open a connection and leave it opened
but they should reuse this connection
instead of reopening new ones
this could be an other way to control the connection leaks
> start program : open ONE connection
> window1
> window2
> use program
> use program
...
> end program : close THE connection
in this case
Nb users = Nb connections
but if you have for example a max nb of connections at 100
only 100 users can connect at the same time
but with the other solution (open/close unuseful connection)
you can have more users than the max connections (100 in this example)
the users are not connected all the time of the use of the program
and they do not connect at the same time
if the max connections is reached a user just has to wait a little for a
connection to be liberated by an other user|||good luck with your consuming-connections-developper
just say to him that a connection is a very precious thing to waste
many users dreamed of having one free
and db-administrator cannot multiply them indefinitly
If ONE should use a connection
ONE should use it shortly
ONE should think of liberating it rapidly
ONE should carefully close it
ONE should know that ONE is not alone
For MANY use the program
Even if only ONE develops it|||I shall rather force them to stop doing this and to change their coding techiniques. Thanx for guiding and responding so quick. Thanx again!!
Regards.
Subscribe to:
Posts (Atom)