telophase | (no subject)

(no subject)

ASP/SQl Server gurus?

Any tips/help/links to websites that are good for idiots trying to work out how to implement temporary tables and/or the SQL Server table variable using ASP/VBScript? I can find plenty of info online about temp tables and the table variable, but nothing about how to use them in an ASP script, other than arcane instructions along the lines of "Just use the ADO object." You know, I figure that if I had a clue how, then I wouldn't have needed to ask the question.

And in other news, this new sinus medication I'm trying does indeed get rid of sinus pressure and headache, at the expense of making my heart race every time I get up and walk down the hall, along with a certain lightheadedness. I guess it's the decongestants and *not* the antihistamines that make me all weird when I take cold and allergy medication. Hm. Don't think I'll be taking this again; I don't like feeling like I've just run a mile every time I go to the restroom down the hall.

Flat | Top-Level Comments Only

I assume you've checked MSDN (http://www.msdn.com/).

This is what I couldn't think of earlier, dBforums (http://dbforums.com/). I've posted there once before about some SQL/IBM junk and got a pretty good reply.

Ah, cool, thanks. Will go browse there... :)

Yes. I can find info on temp tables and on table variables there, which is a bit on the confusing side for me[1], but nothing that says how I use ASP to tell SQL server to do it - they're perfectly willing to give me info on how to do it directly in SQL Server, but it seems to assume that I already know how right ASP commands and how to use them. Right now I can do simple SQL statements and I worked out how to use stored procedures, but when I tried to create a temporary table, I couldn't get to it and use another SQL statement to search it and get the right info out of it. (And that's assuming that I even created the thing correctly in the first place, which might be the problem.)

I will admit that it might be in there but I haven't hit upon the right questions to ask or the right combination of keywords to search for. As a librarian I know all too well how that's often the case. :) Anyway, I've been finding PHP documentation and tutorials to be far more accessible that ASP ones - hell, ASP in a Nutshell didn't give me any useful information on working with strings or arrays when I needed it - I ahd to figure that out on my own by cobbling together three different tutorials, none of which worked separately.

It's been a very frustrating few days. ANd I'm not currently being helped by having just sat through a committee meeting where we have to debate bureaucratese and translate everything we want to do to evaluate the library's inner workings into it before we can get to the business of actually *evaluating* it. Arg.

[1] this is the problem, I think, with being largely self-taught and picking up info as I need it - I don't have a base of information to draw on to help me interpret manuals - it's like the O'Reilly books are great for me to use as reference *after* I figure out a lot of the basics, but they might as well be written in Chinese until I get a good deal of it under my belt.

Anyway, I've been finding PHP documentation and tutorials to be far more accessible that ASP ones...

The joy of open source software: software by people, for people. (Or something as saccrine.)

Yupyup. Last year I was hanging out on a web board populated mostly by fourteen-year-old girls teaching themselves Web design because explaining something to someone else is one of the best ways of learning it yourself, and I was delighted and amazed at the number of kids teaching themselves HTML and PHP.

Wish I knew where to send you....at best, I can fumble my way around SQL with very clear instructions....

Decongestants are nasty. They can do a lot of crap to you. I've not known that many people with adverse side effects to antihistamines, but a number of people who've had fun little ditties like yours wtih decongestants.

:) I'm getting better at SQL; I can construct lovely complex queries now. It's just getting the query *to* SQL Server that's the current problem... arg.

I have a friend who's allergic to both antihistamines and decongestants - they give her hives. She has to be pretty sick before she'll take one. I'm still trying to find something that will allow me to breathe and not make me feel goofy and zoning out. Allegra has zero effect on me. Claritin gives me stomach cramps. Benadryl puts me to sleep half the time and wires me half the time and I can't figure out what it's going to do which time. My allergies up here seem to be focusing in my nose, so I may go back to the doc and beg another Flonase prescription.

Flonase is a godsend.

Have you tried Clarinex? I believe it's only by perscription, but it's worked pretty well on me and I had no side effects. Though, honestly, I'd just hit up your doc and ask her what she thinks...assuming they've got the insurance crap straightened out....

Not yet. I may try that. The insurance things *should* be straightened out - someone typed my social security number in wrong, so they had to give me a temporary ID number. I've got the paperwork to send in and claim the doctor's fee minus my copay, and I'll wait and see if that goes through with no problem. If it does, than I know it's been straightened out. :)

I even tried Zyrtec once, but it completely knocks me out, and what I need is something to make my *waking* hours bearable.

I know they make stuff that is straight antihistamines....my old roommate had been on some stuff that she kept thinking wasn't working and it turned out they forgot to tell her she had to take a decongestant with it.... Not a clue what it was though. :D

I suggest create/use your temporary table in a stored proc - and call your SP from the ASP script.

Table variables have v limited scope (ie only within the proc / batch in which they are called) and wont span ASP query calls - they also seem to have extrememly limited transaction support (no surprise there) but you prolly dont really want that if you are using a temp anyway eh.

here's a snippet to get you started


CREATE PROCEDURE myProc

AS

-- temporary table bit
DECLARE @myTemporaryTable TABLE (
    [ID][int],
    [name][nvarchar](50),
    [some_external_reference][int],
    [any_value][float],
    [rank][int]
)

-- lets fill the temp table
insert into @myTemporaryTable (id, name, some_external_reference, any_value)
select id, user_name, xref, number
from another_table
where number > 1234

-- DO SOME CLEVER STUFF WITH TEMPORARY TABLE 

-- and finally return stuff from stored proc
select t.*, u.extra_field from @myTemporaryTable t, other_table u
where t.id = u.id

GO

Sorry forgot to add a bit to call it too

This assumes C# ASP.NET, sure you can figure out how to mangle it for VB or convert it to vanilla ASP style code


...
using (System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection())
			{
				con.ConnectionString = ConfigurationSettings.AppSettings["YourConnectionStringLabel"];
				con.Open();
				SqlCommand cmd = con.CreateCommand();
				cmd.CommandText = "myProc";
				cmd.CommandType = CommandType.StoredProcedure;
							
				SqlDataReader return_data = cmd.ExecuteReader(System.Data.CommandBehavior.Default);
				UserGrid.DataSource = return_data;
				UserGrid.DataBind();

			}...

Flat | Top-Level Comments Only