Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
marty1976
Starting Member
22 Posts |
 Posted - 2011-07-20 : 10:28:51
|
| Hi all,I'm new here and face something I find quite difficult:I need to read from Excel files, do lots of processing and then return the result as a table.First I wrote a table valued function, but I have to deal with different filenames. Different filenames meant dynamically assigning the filename, but OPENROWSET only accepts string literals. OK, so I have to use dynamic SQL - but that's not allowed in a tvf.So I now rewrote the function to be a procedure, the dynamic OPENROWSET works all right.I call this procedure with 2 input parameters (ID and filename), and need to extract the result (a table) from it.Right now I'm defining a global temp table in which the SP writes its result and then read the result in the client.My problem is: Because of concurrency, I need to make sure the global temp table has a unique name every time this is run.But wow do I do this?Do you have other ideas how to better solve this? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-07-20 : 10:59:20
|
| Why are you using a global temp table instead of a regular temp table?If you can't change to a regular temp table, consider adding a column to the table for SPID or some other token that is unique to the calling session (like a uniqueidentifier paramter) and change the procedure's output to filter on that token.If neither will work you'll have to post your code. |
 |
|
|
marty1976
Starting Member
22 Posts |
Posted - 2011-07-20 : 11:10:27
|
quote:
Originally posted by robvolkWhy are you using a global temp table instead of a regular temp table?
Err, well, I figured that, since a regular temp table is only visible to the current session, using EXEC would open another session and hence I'd be unable to see the temp table in the calling session...But that doesn't seem to be the case? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-07-20 : 11:16:32
|
Nope, if the 2 procedures are called within the same session/connection, the temp table will remain visible to the 2nd procedure:CREATE PROCEDURE one ASSET NOCOUNT ONINSERT #a VALUES(2)EXEC twoGOCREATE PROCEDURE two ASSET NOCOUNT ONINSERT #a VALUES(3)GOCREATE TABLE #a(a INT)INSERT #a VALUES(1)GOSELECT * FROM #a --1 rowEXEC oneSELECT * FROM #a --3 rows--clean upDROP TABLE #a DROP PROCEDURE one,two |
|
|
|
marty1976
Starting Member
22 Posts |
Posted - 2011-07-21 : 10:46:33
|
| Thank you, this really helped me! |
|
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2011-07-21 : 11:38:22
|
| Please post DDL with specs and not narratives or an invented language. We need keys, DRI actions, and constraints. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. Let us know if you can changed the DDL >> First I wrote a table valued function, but I have to deal with different file names. <<Good SQL programmers would not use TVF; they are used by COBOL programers mimicking scratch tapes or OO programmers mimicking methods. >> Different file names meant dynamically assigning the file name, but OPENROWSET only accepts string literals. OK, so I have to use dynamic SQL - but that's not allowed in a TVF. <<SQL programmers hate dynamic code of any kind. It says that the schema and system design is such a mess that we have to “make it up as we go along” instead of having a valid working model fo the Universe of Discourse. >> So I now rewrote the function to be a procedure, the dynamic OPENROWSET works all right. <<>> I call this procedure with 2 input parameters (ID and file name), and need to extract the result (a table) from it. <<RDBMS does not have a magical generic “id”; w3e have the Law of Identity from your freshman logic philosophy course. To be is to be something in particular; to be nothing in particular or everything in general (like “ids”) is to be nothing at all. We need a “<something>_id” and not this disaster. >> Right now I'm defining a global temp table in which the SP writes its result and then read the result in the client. <<Why not hand the result set to the client directly, without the insane overhead of a fake 1950's scratch tape? I miss punch cards and mag tapes the way I miss Polio from my youth :)>> My problem is: Because of concurrency, I need to make sure the global temp table has a unique name every time this is run. <<Oh, you mean a new tape label? No, not how to write SQL. In RDBMS, a table models either a set of entities of the same kind or it models an relationship. Creating a new table every time is an act of creation. In RDBMS, you are God, dropping elephants, squids, automobiles and other things out of the sky. See how your mindset is wrong? --CELKO--Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-07-21 : 14:03:22
|
| Hey Joe, a lot of what you said there was wrong (as usual).And the OP already got what he needed from someone who (1) actually knows what he's talking about and (2) is trying to help.From whence comes this venom and horse crap you constantly spout?If you don't know how, or don't care, to help, please don't reply. Really, your douchebaggery amuses no one but yourself. |
|
|
|
marty1976
Starting Member
22 Posts |
Posted - 2011-07-22 : 04:06:05
|
quote:
Originally posted by jcelko
Please post DDL with specs and not narratives or an invented language. We need keys, DRI actions, and constraints. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. Let us know if you can changed the DDL
Had a bad da, eh? Do you really need to razz someone to feel better?quote:
Originally posted by jcelko>> First I wrote a table valued function, but I have to deal with different file names. <<Good SQL programmers would not use TVF; they are used by COBOL programers mimicking scratch tapes or OO programmers mimicking methods.
Good programmers use what is needed to get the job done.quote:
Originally posted by jcelkoSQL programmers hate dynamic code of any kind. It says that the schema and system design is such a mess that we have to “make it up as we go along” instead of having a valid working model fo the Universe of Discourse.
Did I say I like to use dynamic code? The system I have to interface with is a complete and utter mess, but there's no way to change that. In business you sometimes just have to solve a certain problem and not redesign the entire system...quote:
Originally posted by jcelkoRDBMS does not have a magical generic “id”; w3e have the Law of Identity from your freshman logic philosophy course. To be is to be something in particular; to be nothing in particular or everything in general (like “ids”) is to be nothing at all. We need a “<something>_id” and not this disaster.
25 years ago your disparagement and ignorance would have was shocked me (USENET anyone?). But as years go by, you get used to the stupidity displayed by some people replying to questions posted on the internet...quote:
Originally posted by jcelkoWhy not hand the result set to the client directly, without the insane overhead of a fake 1950's scratch tape? I miss punch cards and mag tapes the way I miss Polio from my youth :)
Because the client is not my choice and there's no other way to interact with it?quote:
Originally posted by jcelkoOh, you mean a new tape label? No, not how to write SQL. In RDBMS, a table models either a set of entities of the same kind or it models an relationship. Creating a new table every time is an act of creation. In RDBMS, you are God, dropping elephants, squids, automobiles and other things out of the sky. See how your mindset is wrong?
Have you ever thought about growing up? At least stop smoking or sniffing whatever it is that makes you talk of being God and dropping elephants. If I were to choose I'd drop a sperm whale - but: Don't panic ;-)Consider what Russel already pointed out. Better yet, print this and stick it to your screen:1) Don't reply if a question is solved2) If you reply, be polite and try to help. Avoid unnecessary comparisons that foreigners might not understand. Don't try to impress people by citing ISO references, people usually hide their uncertainty behind technical jargon. Just be nice :-)Have a nice day and weekend.marty |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-07-22 : 09:15:31
|
| Good post Marty. |
|
|
|
|
|
|
|
|
Passing result table from one SP to another