There is a company, Idera http://idera.com/Products/SQLconfig/ that produces
a product called SQLconfig. Among other things, it monitors database server
hardware, operating system (OS), SQL Server instance and database
configuration, providing both automated change notification and comprehensiv
e
reporting on changes in a SQL Server environment
Does anyone know of any VBS or T-SQL that can be used instead? Any info,
links would be appreciated to get started on this project. Thank you.Well www.red-gate.com has a product with a development kit that gives you an
API to do these kinds of things programmatically.
Andrew J. Kelly SQL MVP
"SQLcat" <SQLcat@.discussions.microsoft.com> wrote in message
news:362547A2-43E0-409B-9E82-45D9428E2D9F@.microsoft.com...
> There is a company, Idera http://idera.com/Products/SQLconfig/ that
> produces
> a product called SQLconfig. Among other things, it monitors database
> server
> hardware, operating system (OS), SQL Server instance and database
> configuration, providing both automated change notification and
> comprehensive
> reporting on changes in a SQL Server environment
> Does anyone know of any VBS or T-SQL that can be used instead? Any info,
> links would be appreciated to get started on this project. Thank you.|||I'd also like to get away from trial versions of software. I'd like to be
able to do this in house.
"Andrew J. Kelly" wrote:
> Well www.red-gate.com has a product with a development kit that gives you
an
> API to do these kinds of things programmatically.
> --
> Andrew J. Kelly SQL MVP
>
> "SQLcat" <SQLcat@.discussions.microsoft.com> wrote in message
> news:362547A2-43E0-409B-9E82-45D9428E2D9F@.microsoft.com...
>
>|||Then why not buy the tool? It's only a few hundred dollars. You will waste
that much in your own time in a few hours.
Andrew J. Kelly SQL MVP
"SQLcat" <SQLcat@.discussions.microsoft.com> wrote in message
news:2F604881-2008-4A83-A83D-1F8FC7A2DF52@.microsoft.com...
> I'd also like to get away from trial versions of software. I'd like to be
> able to do this in house.
> "Andrew J. Kelly" wrote:
>|||a few hours I have....
"Andrew J. Kelly" wrote:
> Then why not buy the tool? It's only a few hundred dollars. You will was
te
> that much in your own time in a few hours.
> --
> Andrew J. Kelly SQL MVP
>
> "SQLcat" <SQLcat@.discussions.microsoft.com> wrote in message
> news:2F604881-2008-4A83-A83D-1F8FC7A2DF52@.microsoft.com...
>
>|||So if you are doing this for fun rather than profit why are you asking
for us to show you ... :-)
You'll perhaps want to look at WMI and DMO for this. DMO gives access
to SQL Server objects and allows you to script them. However, DMO is
superceded by SMO in 2005 so now probably isn't a good time to embark
on such a project. One reason why most people don't write system
management tools is because of the cost of keeping up with the
technology.
David Portas
SQL Server MVP
--|||I'll look into the DMO route...we're not going to SQL 2005 anytime soon, so
hopefully SMO won't be an issue for some time. This will "profit" my compan
y
and my ability to keep on top of changes as they occur....good and bad.
Just Google SQL DMO and go from there?
"David Portas" wrote:
> So if you are doing this for fun rather than profit why are you asking
> for us to show you ... :-)
> You'll perhaps want to look at WMI and DMO for this. DMO gives access
> to SQL Server objects and allows you to script them. However, DMO is
> superceded by SMO in 2005 so now probably isn't a good time to embark
> on such a project. One reason why most people don't write system
> management tools is because of the cost of keeping up with the
> technology.
> --
> David Portas
> SQL Server MVP
> --
>
Showing posts with label product. Show all posts
Showing posts with label product. Show all posts
Thursday, March 8, 2012
Monday, February 13, 2012
Auto Number PROBLEM
I have a table namely Product with the following identity modifiers:-
Column- "Num"
Identity-Yes
Identity Seed-1
Identity Increment-1
My problem is everytime i delete a row in the table Product, the Num column after the deleted row will not automatically replace the deleted Num column's data.
Let say:
Num Name
30 Apple
31 Orange
32 Pineapple
If I delete row with Num 31, the Num column after the deleted row will not change to 31 but remain 32.
Num Name
30 Apple
32 Pineapple
What can I do to ensure that the Num column (Num 32) will change to Num 31?
Thank you.what you could do is think very hard about what you seem to want to do
suppose you have an Orders table, which contains customer orders for all products you've sold
if you renumber the products in the products table, you must therefore renumber the Orders table too
this could shut your database down for a few hours
and why? what is the benefit?
autonumbers should never be reassigned
it's not as though you're going to run out of numbers
you could realistically set your identity seed at 183,527,426 and your increment at 3,743 and you would still not run out of numbers for several centuries|||you can add a new column to your table
RowNumber
Id Fruit RowNumber
31 Apple 100
32 Banana 101
33 Pineapple 102
If you add a new fruit
RowNumber = Count(*) of the table
If you delete a fruit
you update RowNumber=RowNumber-1 for all the fruits over the
RowNumber of the deleted fruit
If you delete the Banana
Update Table Fruits Set RowNumber=RowNumber-1
Where RowNumber>(Select RowNumber From Fruits Where Id=32)
Delete Fruits Where Id=32|||But some would say this is not a GREAT thing to do
'cause it's not performant.
But it will work|||i would say it is not a great thing to do because of (a) the impact it has on related tables, and (b) the total absence of ROI (http://searchwebservices.techtarget.com/sDefinition/0,,sid26_gci214270,00.html)
also, not only do you have to update the fruits table, you also have to update the desserts table, the pie table, and any other table which has foreign keys to the fruit table
and i repeat: what exactly is the benefit of doing it?
if it is important that there be no gap in numbers, then i strongly suggest that an autonumber is the wrong design choice|||in my case there is no impact on other tables
because I don't touch the ID number
but the new independant column ROWNUMBER !!!
Column- "Num"
Identity-Yes
Identity Seed-1
Identity Increment-1
My problem is everytime i delete a row in the table Product, the Num column after the deleted row will not automatically replace the deleted Num column's data.
Let say:
Num Name
30 Apple
31 Orange
32 Pineapple
If I delete row with Num 31, the Num column after the deleted row will not change to 31 but remain 32.
Num Name
30 Apple
32 Pineapple
What can I do to ensure that the Num column (Num 32) will change to Num 31?
Thank you.what you could do is think very hard about what you seem to want to do
suppose you have an Orders table, which contains customer orders for all products you've sold
if you renumber the products in the products table, you must therefore renumber the Orders table too
this could shut your database down for a few hours
and why? what is the benefit?
autonumbers should never be reassigned
it's not as though you're going to run out of numbers
you could realistically set your identity seed at 183,527,426 and your increment at 3,743 and you would still not run out of numbers for several centuries|||you can add a new column to your table
RowNumber
Id Fruit RowNumber
31 Apple 100
32 Banana 101
33 Pineapple 102
If you add a new fruit
RowNumber = Count(*) of the table
If you delete a fruit
you update RowNumber=RowNumber-1 for all the fruits over the
RowNumber of the deleted fruit
If you delete the Banana
Update Table Fruits Set RowNumber=RowNumber-1
Where RowNumber>(Select RowNumber From Fruits Where Id=32)
Delete Fruits Where Id=32|||But some would say this is not a GREAT thing to do
'cause it's not performant.
But it will work|||i would say it is not a great thing to do because of (a) the impact it has on related tables, and (b) the total absence of ROI (http://searchwebservices.techtarget.com/sDefinition/0,,sid26_gci214270,00.html)
also, not only do you have to update the fruits table, you also have to update the desserts table, the pie table, and any other table which has foreign keys to the fruit table
and i repeat: what exactly is the benefit of doing it?
if it is important that there be no gap in numbers, then i strongly suggest that an autonumber is the wrong design choice|||in my case there is no impact on other tables
because I don't touch the ID number
but the new independant column ROWNUMBER !!!
Subscribe to:
Posts (Atom)