variable char

Question

variable char

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

peter478 SSCrazy Points: 2939 More actions · Answer 1

Sean Lange (11/10/2014)

peter478 (11/10/2014)
2. concerning global temp table ##amp you are right , but when I change it to #amp I can get output, no feedback (select * from #amp)
with global it works.
That is because your temp table is out of scope. When you declare your temp table in dynamic sql, statements outside the dynamic sql can't see the temp table. Drop the dynamic sql and this issue will fix itself.

for explanation , I use two procedures amp and amp_out.

The first , I use for passing variables from access into amp , and I do select by more conditions.

At the beginning I drop temp table, If you mean this.

The second amp_out procedure, I use just for report in access (select * from #amp), but everything is prepared in the first.

I use this method, because I couldnt get output directly in one procedure(amp).

peter478 SSCrazy Points: 2939 More actions · Answer 2

Evil Kraig F (11/10/2014)
The single or multiple SQL User doesn't matter, just FYI. Most webapps run that way with hundreds of thousands of unique users on the far end.
What's happening with #amp is you need to make sure everything happens in the same batch, or statement. Because you're using dynamic SQL and instantiating multiple unique calls, you're losing your SPID. They need to be made in one huge call if you're going to send it in that way.
I don't recommend sending it in that way.
Build procedures on the SQL server, use passthrough queries to call them, or heck just skip the ADP and do a direct connection to the SQL server similar to web apps. Passthrough queries are really just to make it newbie friendly.

can you post me a link for passthrough queries you mean?

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

What this whole thing looks like is the possibility of SQL Injection. It also looks like a "Catch All" query and the proper way to do it can be found in Gail Shaw's article on the subject. I consider it to be required reading for anyone and everyone in the business.

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 4

peter478 (11/10/2014)
can you post me a link for passthrough queries you mean?

http://support.microsoft.com/kb/303968/

Or simply google: microsoft access passthrough query

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

peter478 SSCrazy Points: 2939 More actions · Answer 5

Evil Kraig F (11/10/2014)
peter478 (11/10/2014)
can you post me a link for passthrough queries you mean?
http://support.microsoft.com/kb/303968/
Or simply google: microsoft access passthrough query

Thanks , but this is not way, I want to use , it is not very user friendly to setup every user...

I want have something more complex, without addition settings on client... for example #amp