asking for opinions

Hello,
I want to collect system information (e.g. who have what access to what
databases, how much space has been used by each database, what version of the
service pack installed, etc.) of our SQL 2000 server. I can get on the SQL
server and use the system stored procedures to get those information manually
every time I need it. But I also need to show the information to the
managers and other system admins who don't deal with SQL server very often.
So I'm thinking about showing those information on the web which is only
accessible to a certain group of people. How people usually do it? If
coding is involved, which language (PHP, Perl ,etc) has a better handle of it
in terms of SQL server? I know some people use email notification to send
reports. But I prefer web. By the way, we don't use any reporting software
here, like Crystal Report. Anyway, I'd appreciate any ideas and advice.
Thanks,
BingI've done this at my last three jobs. Build the infrastructure for the info
you want to collect and use linked servers, DTS and a central DB to collect
and store. Summarize it through stored procs that are called via the web.
For ultra critical items, generate email alerts such as the creation of
objects in a locked down environment.
"bing" wrote:
> Hello,
> I want to collect system information (e.g. who have what access to what
> databases, how much space has been used by each database, what version of the
> service pack installed, etc.) of our SQL 2000 server. I can get on the SQL
> server and use the system stored procedures to get those information manually
> every time I need it. But I also need to show the information to the
> managers and other system admins who don't deal with SQL server very often.
> So I'm thinking about showing those information on the web which is only
> accessible to a certain group of people. How people usually do it? If
> coding is involved, which language (PHP, Perl ,etc) has a better handle of it
> in terms of SQL server? I know some people use email notification to send
> reports. But I prefer web. By the way, we don't use any reporting software
> here, like Crystal Report. Anyway, I'd appreciate any ideas and advice.
> Thanks,
> Bingsql

AS400/DB2 different column names in different configurations

Hello!

I am creating a package that will be executed against different AS400/DB2 databases. (I use a Foreach Loop). The AS400/DB2 is configured a little bit differently. This results in that the same column can have different name in different AS400/DB2. For example the column nameA4￡ADG, A4$ADG and A4?ADG is the same column.

In the Data Flow I use DataReader. Does anyone know how to get past this issue without creating one package for each column name?

Best regard

Tina

I'm not an AS400 person (or DB2), so I have no idea if this will work. But, it seems like you could use an expression to set the SQL Statement on the DataReader source, and alias the differently named columns to the same name. To set an expression on the data reader, you have to select the data flow task in the control flow, go to properties, and go to expressions.|||

Hello!

Thank you for your help!

But know I have solved it. I use different Data Flow for each set of column names. Depending on the company that is executed I defined the Precedence Constraint to "Expression and Constraint" and use the Expression:

@.[User::varCompanyName]=="CDHU"

In the Data Reader I have to set ValidateExternalMetadata to False

Best regards,

Tina

Arithmetic overflow error for data type smallint, value = 46080

Hello friends, in this moments I have a problem with my Ms Sql server 2000,
at the enterprise manager I can't see the databases, a message error
"Arithmetic overflow error for data type smallint, value = 46080" appears,
but the server is seems to be still working, what it is?
Regards,
Jess R.
Hi
You may want to run SQL profiler to see what is being run by Enterprise
manager.
It could be similar (but not necessarily the same thing as!) to
http://support.microsoft.com/default...b;en-us;234028
Also check the version of SQL Server http://www.aspfaq.com/show.asp?id=2160.
John
"Jesus Brito" wrote:

> Hello friends, in this moments I have a problem with my Ms Sql server 2000,
> at the enterprise manager I can't see the databases, a message error
> "Arithmetic overflow error for data type smallint, value = 46080" appears,
> but the server is seems to be still working, what it is?
> Regards,
> Jesús R.
>
>

Arithmetic overflow error for data type smallint, value = 46080

Hello friends, in this moments I have a problem with my Ms Sql server 2000,
at the enterprise manager I can't see the databases, a message error
"Arithmetic overflow error for data type smallint, value = 46080" appears,
but the server is seems to be still working, what it is?
Regards,
Jess R.Hi
You may want to run SQL profiler to see what is being run by Enterprise
manager.
It could be similar (but not necessarily the same thing as!) to
http://support.microsoft.com/defaul...kb;en-us;234028
Also check the version of SQL Server http://www.aspfaq.com/show.asp?id=2160.
John
"Jesus Brito" wrote:

> Hello friends, in this moments I have a problem with my Ms Sql server 2000
,
> at the enterprise manager I can't see the databases, a message error
> "Arithmetic overflow error for data type smallint, value = 46080" appears,
> but the server is seems to be still working, what it is?
> Regards,
> Jesús R.
>
>

Arithmetic overflow error for data type smallint, value = 46080

Hello friends, in this moments I have a problem with my Ms Sql server 2000,
at the enterprise manager I can't see the databases, a message error
"Arithmetic overflow error for data type smallint, value = 46080" appears,
but the server is seems to be still working, what it is?
Regards,
Jesús R.Hi
You may want to run SQL profiler to see what is being run by Enterprise
manager.
It could be similar (but not necessarily the same thing as!) to
http://support.microsoft.com/default.aspx?scid=kb;en-us;234028
Also check the version of SQL Server http://www.aspfaq.com/show.asp?id=2160.
John
"Jesus Brito" wrote:
> Hello friends, in this moments I have a problem with my Ms Sql server 2000,
> at the enterprise manager I can't see the databases, a message error
> "Arithmetic overflow error for data type smallint, value = 46080" appears,
> but the server is seems to be still working, what it is?
> Regards,
> Jesús R.
>
>

Are the databases created within VWD Express accessible by aspnet_regsql.exe or Management

Hi,

When I attempt to add the membership functionality to a database created within VWD Express, by using the aspnet_regsql.exe application (connecting to ./SQLEXPRESS using Windows authentication), the database I've created within VWD Express doesn't appear within the list of accessible databases. I have the same problem when attempting to access the same database within Management Studio Express.

Does this mean that databases created from within VWD Express don't use the same storage mechanism or paradigm as those already available within Management Studio Express? Or perhaps I need to somehow register the database using Management Studio Express? As a side note, I'm not sure if the "File/Open File" menu option within Management Studio Express holds the answer to my problems, but rather bizarrely, it crashes Management Studio Express when I select it.

Apologies for the rambling - in essence my question is "How can I make databases created within VWD Express appear within aspnet_regsql.exe and/or Management Studio Express?"

Many thanks,

Jon

Hey,

You can attach them. However, I had problems with any database in my documents showing up. But attaching is the process that makes it work. So you will want to attach database from the list of options, and then navigate to the database.

|||

http://forums.asp.net/thread/1456464.aspx

Step 1 to 6 will do what the previous poster said :)

|||

Thanks for your help folks.

So the only problem I have remaining is that (as stated in the referenced post) Management Studio can't see the database in order to attach it unless I move it out of my home directory (presumably a file permissions problem). Ideally I need both VWD and Management Studio to be able to access the database without moving it between two different locations. Which leaves me with two options:

1) Change the permissions on my home directory such that Management Studio can see the MDB/LDB and therefore attach it. However, I'm not sure what permissions I'd need to change.

2) Move the MDB and LDB out of my home directory and to a place where Management Studio can see them (e.g. the standard database directory for SQL Server 2005 Express) and update the path of the database within VWD. However, VWD doesn't appear to allow me to change the path of the database within the database properties in Solution Explorer.

Can anybody advise on how to do either of the above, and/or which approach is preferable?

Many thanks.

|||If you want both to be able to see it, your best bet is to put the database files in the SQL Server directory (where all the other databases are), attach it, and then change your connection string in your project to access the database by IP address instead.

The MDF-in-APP_DATA approach is when you're going to use VWD exclusively to deal with it. Otherwise, go the "good old fashionned way", which is to let SQL Server handle the database fully.|||

Yep, that's what I ended up doing anyway - thanks for the confirmation.

Not sure why they don't just put the MDB/LDB in the SQL Server data directory in the first place...

|||Because if you only use VWD, and your host supports sql server 2005 with attach, you can do XCOPY deployement, while if they put the mdf in the sql server directory, you need to export the database :)

Are seperate databases betters?

Hi. I have been talking with some developers who have built a hosted application supporting multiple customers. Their database approach is to create a new, dedicated database (same schema each time) for every customer that signs-up.

This approach is contrary to typical hosted DB designs that I have delt with -- that is, a single database holding multiple customer information rather than a unique database for each customer.

Does the improved security of a dedicated database out-weigh the additional maintenance requirements?

If anyone has some objective thoughts on this topic, I'd love to hear them.

Thanks,
BillIf this is a "hosted" application for, say, a cadre of retail stores each one of which has its own block of customers, products, sales, etc to deal with, then the stores are the hosting-company's "customers."

Each store expects to see and to deal with only its own set of information, and considers that information to be highly proprietary.

The hosting-company will have, ex minimis, a warehouseman's fiduciary duty with respect to the information, extending not only to the stores but indirectly to their customers. I think that having separate databases, with separate login-IDs etc, is really the only scenario that would work.|||It really depends on the requirements - Do the customers need to have additional isolation(security/fault tolerance/performance) ? How much data will be stored for each customer and what data will be maintained ?|||Thanks for the replies.

[The hosting-company will have, ex minimis, a warehouseman's fiduciary duty with respect to the information, extending not only to the stores but indirectly to their customers...]

Even with a single database, reasonable precautions are taken. All customers have separate logins and distinct logical segregation of data. The approach is roughly analogous to a bank safe with safe boxes holding the possessions of multiple parties.

[It really depends on the requirements - Do the customers need to have additional isolation(security/fault tolerance/performance) ? How much data will be stored for each customer and what data will be maintained ?]

I think if you were to ask any given customer, their knee-jerk reaction would be to say that they want their own database. But other than the fear of co-mingling of data, there are no specific scale or performance reasons that would demand it.

My concern with separate databases is the administrative overhead if you have a thousand customers, youd have a thousand databases. Things like Schema updates, index rebuilds, etc. would become a significant activity.|||My biggest worry would be if someone from company A deletes half their data. After the restore, company B asks "Where is my data from today". Just my .02 USD.|||Originally posted by billdavidheiser
[The hosting-company will have, ex minimis, a warehouseman's fiduciary duty with respect to the information, extending not only to the stores but indirectly to their customers...]

Even with a single database, reasonable precautions are taken. All customers have separate logins and distinct logical segregation of data. The approach is roughly analogous to a bank safe with safe boxes holding the possessions of multiple parties.

[It really depends on the requirements - Do the customers need to have additional isolation(security/fault tolerance/performance) ? How much data will be stored for each customer and what data will be maintained ?]

I think if you were to ask any given customer, their knee-jerk reaction would be to say that they want their own database. But other than the fear of co-mingling of data, there are no specific scale or performance reasons that would demand it.

My concern with separate databases is the administrative overhead if you have a thousand customers, youd have a thousand databases. Things like Schema updates, index rebuilds, etc. would become a significant activity.

As usual, what it really comes down to is: "it depends." Multiple databases make it less likely that the wrong customers' data will be visible but have their own negative implications as well. I think that the best advice, borne out by this little discussion, is that the designer must be keenly aware of the tradeoffs that will inevitably be made with any decision. Every decision is a compromise. No matter how forcibly a particular position may be argued on a forum ;) it is neither "right" nor "wrong."|||Multiple databases have the following advantages:

If one or some customers ask for his or their data backup you can easily do it.

If one or some customers ask for some changes which may be related to the table structures, you can easily handle it.

If one or some customers leave, you can easily backup and delete (drop) his or their databases without cleaning his or their data in one database.|||Originally posted by sundialsvcs
As usual, what it really comes down to is: "it depends." Multiple databases make it less likely that the wrong customers' data will be visible but have their own negative implications as well. I think that the best advice, borne out by this little discussion, is that the designer must be keenly aware of the tradeoffs that will inevitably be made with any decision. Every decision is a compromise. No matter how forcibly a particular position may be argued on a forum ;) it is neither "right" nor "wrong."

["it depends."]

Ah, very helpful. Thank you. Are you an Economics professor by any chance? :)

Seriously though; I understand what you are saying and agree. I was hoping to hear if anyone out there has a real-world experiance with trying to maintain a boat-load of individual customer databases.|||MCrowley brings up a good point which ties into fault tolerance. What happens if the database is suspect or part of it is corrupt - all of your users would be hosed if using 1 database.

What about the amount of data being stored per customer and the frequency of updates/deletes/inserts per customer ? Is the amount of data fixed per customer and do they infrequently change data ?|||The company that I'm working for has individual customer database for each customer. All databases have the same structure except some extra fields for some databases based on the customers requirements. We had some problems which happened to some of databases and the websites related to those databases were affected. In this case, individual customer database shows an advantage.

For the maintenance of the databases, since they have almost same structure you can use a script to do a job for all databases. I think that the advantages are much more than disadvantages.