An MS SQL question

[dominicall]

Wasn't sure which forum to put this in but this seemed most appropriate.

I have a stored procedure in my database that I need to alter. Normally that would be easy but I'm not at my office and don't have Enterprise Manager access to my database from where I am - :(

Is it possible to alter a stored procedure remotely using an ADO Command, i.e. in this example I want to add selecting the ContactName as well as the OfficeName
Dim cmdAlterProc, strAlterProc
Set cmdAlterProc = Server.CreateObject("ADODB.Command")
cmdAlterProc.ActiveConnection = strConnection
strAlterProc = "ALTER PROCEDURE spProc (@OfficeID[int]) AS "
strAlterProc = strAlterProc & "SELECT OfficeName, ContactName "
strAlterProc = strAlterProc & "FROM tbl_Offices "
strAlterProc = strAlterProc & "WHERE OfficeID = @OfficeID"
cmdAlterProc.CommandText = strAlterProc
cmdAlterProc.CommandType = adCmdText
cmdAlterProc.Execute
Set cmdAlterProc = Nothing
A simple yes or no answer would be great.

Thanks

Dominic :confused:

[dominicall]

Don't worry - figured it out - it works :D

Dominic

[whammy]

Cool, did you have to make any changes? :)

I don't get much of an opportunity to mess with stored procedures where I work and that's one of the things I am interested in mastering before next year.

[dominicall]

LOL - yep - made the changes remotely no problem.

You can also create and drop SPs that way as well. When I'm thru the current stage of development on my site I'll get some info together for you on SPs and email it to.

I do about 80% of my database stuff with SPs now - it's only if I'm doing tidy updates or inserts - performance improvement is significant.

The only major thing I don't do with SPs is Data Shaping - and I'm not sure it can be done that way anyway.

Dominic :D

[whammy]

Sweet, I have to show you why most of those functions are useless that you posted, dang it.

I only used ONE of them, and that's changing VbCrLf to "<br />".

There were a few custom functions in there, but they were made to change stuff coming out of a database field into HTML, like [BR] or I think maybe (BR).

All of the rest were totally unnecessary if you use Server.HTMLEncode().

But, I have updated all of my functions to use byVal when necessary, since byRef is the default in VBScript, it seems...

http://www.solidscripts.com/downloads/RegEx.txt

P.S. I update this every time I add something, which could be weekly or daily!

:D

[dominicall]

LOL - I kind of knew you were going to say that - sometime I do want to insert double quotes into the database so I can use links in the content and need the double quotes... I vary what use depending on the content...

Is there a way to 'reverse' Server.HTMLEncode when I retrive the content from the database

Dominic :D

[whammy]

See that's the whole problem... you shouldn't alter your data in any way.

The only alteration you should make when inserting or retreiving from a database is to comment out ONE single quote with another... double quotes shouldn't be an issue at all! :)

And actually that has nothing to do with what I was talking about in the last post. :D

[dominicall]

LOL - most of the time I only use the FixQuotes or FixDblQuotes anyway to replace the single/double quotes.

Am currently working across about 200 tables and 600 ADO/Stored Procedure statements so far so can't be too bad - LOL

Am currently confusing myself with 5 level data shaping at the moment - arrggghhhh....

Dominic :D

[whammy]

See you don't need to fix double quotes... and you are using that stuff OUTSIDE of your SQL statements... that's the problem.

I wish I was better at explaining this kind of thing across the 'net.

Basically all the "&amp;" "&quot;" etc. fixes that are in your functions are completely unnecessary, since when displaying a value in HTML, you can use Server.HTMLEncode() to make sure it doesn't blow up in a browser.

Just don't alter the data in ANY way, unless you are displaying it on an HTML page. In which case you might want to use something like:

<input type="text" name="myvariable" value="<% = Server.HTMLEncode("myvariable") %>" />

That way the value of your data has not changed at all... but it will display correctly in a browser. :)

What's cool is you're like a lot of the guys I work with... you've learned some stuff I haven't, and vice versa. :)

That's why the sharing of knowledge totally rules.

And using this, you don't have to alter your database entries in any way except for commenting out single quotes... ONLY IN SQL Statements! ;)

:(