Scripting

I've got SS 2000 on Win 2000 and need to do some o/s scripting to interact
with HP Openview for monitoring purposes. What I want to do is 1) pull the
location of the temp mdf using sp_helpfile and then 2) feed that into
Openview or just get the size directly using sp_spaceused and feed that into
Openview. [I have other scripts I want to run that would use osql.]
So the bottom line is that I need a crash course in vbscript (or any other
scripting language that you'd recommend that can run from the command line
and interact with Openview). How I can get this crash course? Any
books/sites that you'd recommend?Nevermind. I found an old thread of mine.
"CLM" wrote:

> I've got SS 2000 on Win 2000 and need to do some o/s scripting to interact
> with HP Openview for monitoring purposes. What I want to do is 1) pull th
e
> location of the temp mdf using sp_helpfile and then 2) feed that into
> Openview or just get the size directly using sp_spaceused and feed that in
to
> Openview. [I have other scripts I want to run that would use osql.]
> So the bottom line is that I need a crash course in vbscript (or any other
> scripting language that you'd recommend that can run from the command line
> and interact with Openview). How I can get this crash course? Any
> books/sites that you'd recommend?|||Nevermind. I found an old thread of mine.
"CLM" wrote:

> I've got SS 2000 on Win 2000 and need to do some o/s scripting to interact
> with HP Openview for monitoring purposes. What I want to do is 1) pull th
e
> location of the temp mdf using sp_helpfile and then 2) feed that into
> Openview or just get the size directly using sp_spaceused and feed that in
to
> Openview. [I have other scripts I want to run that would use osql.]
> So the bottom line is that I need a crash course in vbscript (or any other
> scripting language that you'd recommend that can run from the command line
> and interact with Openview). How I can get this crash course? Any
> books/sites that you'd recommend?sql

Scripting

I've got SS 2000 on Win 2000 and need to do some o/s scripting to interact
with HP Openview for monitoring purposes. What I want to do is 1) pull the
location of the temp mdf using sp_helpfile and then 2) feed that into
Openview or just get the size directly using sp_spaceused and feed that into
Openview. [I have other scripts I want to run that would use osql.]
So the bottom line is that I need a crash course in vbscript (or any other
scripting language that you'd recommend that can run from the command line
and interact with Openview). How I can get this crash course? Any
books/sites that you'd recommend?Nevermind. I found an old thread of mine.
"CLM" wrote:
> I've got SS 2000 on Win 2000 and need to do some o/s scripting to interact
> with HP Openview for monitoring purposes. What I want to do is 1) pull the
> location of the temp mdf using sp_helpfile and then 2) feed that into
> Openview or just get the size directly using sp_spaceused and feed that into
> Openview. [I have other scripts I want to run that would use osql.]
> So the bottom line is that I need a crash course in vbscript (or any other
> scripting language that you'd recommend that can run from the command line
> and interact with Openview). How I can get this crash course? Any
> books/sites that you'd recommend?

Scripter and Database Mail

I currently have VB.Net program that scripts out all objects in a server for DR purposes. I just realized that I don't have DB Mail included. I have not been able to find a way to use the Scripter for this. Does anyone know if the Scripter can do DB Mail or will I need to resort to writing queries?

Hi greenie,

DB Mail objects cannot be scripted via Scripter class. We're looking into adding this functionality in a future release.

|||Thanks - I would certainly appreciate the functionality given the DB Mail configuration can get fairly complex. I guess my workaround will be to query the tables and output to my recovery file

Scripter and Database Mail

I currently have VB.Net program that scripts out all objects in a server for DR purposes. I just realized that I don't have DB Mail included. I have not been able to find a way to use the Scripter for this. Does anyone know if the Scripter can do DB Mail or will I need to resort to writing queries?

Hi greenie,

DB Mail objects cannot be scripted via Scripter class. We're looking into adding this functionality in a future release.

|||Thanks - I would certainly appreciate the functionality given the DB Mail configuration can get fairly complex. I guess my workaround will be to query the tables and output to my recovery filesql

Scrambling Data in a table

We have a bunch of data that includes things like DNS names and host names. For testing purposes, we want to give people access to our data but we do not want them to see the real names of the items I mentioned prior. What I would like to do is a letter replacement for each character in the name, thus A could become T, B could become U, etc. It's not a big deal if the end user can unscrable these, they probably won't anyways and it's not a big deal if they do, our client just wants something to make it not so obvious.

Is this possible to do through a SQL query and if so, can you give my pointers on how to accomplish this?

Otherwise I will probably use a VB solution, but it would be similar if I could just write a SQL query that I could send to anyone that needs to do this.

you can use SQL to replace things in select statements

read this :http://www.java2s.com/Code/SQL/Function/CallREPLACEfunctioninselectclause.htm

hth,
mcm

|||

U can use Replace function

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

|||

You can create an SP which applies some encoding logic over the supplied value and one SP which does exactly the reverse of it. The encoding and decoding logic can absolutely be your ( which ever way you like it to be ). Generally you can play with the ascii value of the character. One caution that should be exercised is that your encode and decode logic must be strong and tested enough for you can't bare wrong values when encoding or decoding.

Hope this will help.

|||

I see how I can use replace to replace one letter, but how would I go about replacing all the letters? Some of my tables have thousands of records, if I have to run a select and then update query for every letter, it's going to take a LONG time.

|||

Hi,

but how would I go about replacing all the letters? Some of my tables have thousands of records, if I have to run a select and then update query for every letter, it's going to take a LONG time.

It seems that you have misunderstood the community members' suggestions. What you want to do is just to hide the real information since you don't want the information to be seen by the users. So we replacing all the records in database would not be a good practice, and actually it's not necessary. As mentioned by others, you have two ways to achieve that.

First, write the "replace" logic in your application level, not in the database level. When you have retrieve a real data from the database, you should replace the specific value against the data before displaying.

Another way is, you can create a stored procedure, after you have select the result set, just use SELECT REPLACE (Ep1,Ep2,Ep3) to replace the char in the result.

As for the speed, the replace operation against a variable would not take much time.

Thanks.

|||

I could do this in the application level, but i've been asked to do it in the database, so that's what I'm trying to do.

And I want to replace the data in the database... I see how the replace function works to replace one character at a time, but when you run your select query, you seem to only have the option to replace one letter at a time. I need to replace 26 letters and 10 numbers, which is what I'm not sure how to do.

SELECT REPLACE(name, "a", "x")

That would replace one character... I have to do it 35 more times for all the different letters and numbers, and then I have to acctually update the database with the new values. And then I have to do it on multiple fields too.

|||

UPDATE YourTable

SET YourField=REPLACE(REPLACE(REPLACE(YourField,"a","x"),"b","y"),"c","z")

Of course, that'll get really nasty with 36 of them, and you will run into problems because if you first replace a with x, when you get to replacing x with whatever, now you are replacing the old x and the new x with something. I would suggest writing a user defined function (UDF) to do the conversion for you. Then you can just:

UPDATE YourTable

SET YourField=dbo.Scramble(YourField)

|||

Kriswd40:

It's not a big deal if the end user can unscrable these, they probably won't anyways and it's not a big deal if they do, our client just wants something to make it not so obvious.

With due respect to your client, that makes no sense at all. Either you hide the information or you don't. Making it "not obvious" is ridiculous. And what's the point of making it available in develpment if they can't read it, anyway?

|||

It's not all fields that I'm changing, just IP Addresses, DNS entries, serial numbers, ect so the data is still very usable. It's not live data, periodically we are given a backup of the data we can use for developement but the client no longer wants us to have access to some of the sensative information in this data. The best thing to do would be to completely encrypt/randomize these fields so they cannot be recovered, but at this time we do nothing so at least this is a step in the right direction.