|
It's a feature that was added in SQL Server 2000. I'd consider making a stored procedure with an output parameter in your case, but it's not going to be something you can use in a SELECT statement. You may be able to still accomplish your design goals in a nice way using a view.
Regards,
Jeff Varszegi
|
|
|
|
|
Thanks, creating a stored procedure is what I ended up doing. Most of the time the single output parameter would do fine. However, on the search screen they what the comma delimited list which worked fine at home on 2K but not on the client's 7.0. I'll take the view into consideration.
Thanks,
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
You're welcome. I thought you might enjoy this article on tricks for passing data around. Before I read that one, I didn't even know about INSERT EXEC, and I've been using SQL Server for some time now.
Regards,
Jeff Varszegi
|
|
|
|
|
Thanks again. I won't be back to the client's site until Monday but it was kind of frustrating today...grrr... Nonetheless, I haven't had a chance to look into the view scenario. But, since I am restricting the user search results to a max of 100 records, I think that the view scenario may be the best solution as this point. Most of the time the output parameter from a query will suffice. The database in the app will be coasting along since everything is cached. That is why I didn't want to do the string concantenation in the app. Here was the function I was using in 2K toc oncantenate the Terminal Port Well Locations (Oil Industry). It obviously failed in SQL 7.0.
CREATE FUNCTION fn_SamplesTerminalPortWellLocationText
(
@SampleID int
)
RETURNS varchar(1000)
AS
BEGIN
DECLARE @RowCount int,
@Debug bit,
@Results varchar(1000)
SELECT @Debug = 0,
@Results = ''
-- Loop thru all of the terminal port and concantenate them together
DECLARE @TerminalPortWellLocation varchar(75)
DECLARE TPWL CURSOR FAST_FORWARD FOR
SELECT tp.TerminalPortWellLocation
FROM tblSamplesSampleTerminalPortWellLocation stp,
tblSamplesTerminalPortWellLocation tp
WHERE stp.TerminalPortWellLocationID = tp.TerminalPortWellLocationID
AND stp.SampleID = @SampleID
AND (stp.Deleted Is Null Or stp.Deleted = 0)
ORDER BY TerminalPortWellLocation
OPEN TPWL
FETCH FROM TPWL
INTO @TerminalPortWellLocation
While @@Fetch_Status = 0
BEGIN
SELECT @Results = @Results + @TerminalPortWellLocation + ', '
FETCH FROM TPWL
INTO @TerminalPortWellLocation
END
-- Close & Deallocate Cursor
Close TPWL
Deallocate TPWL
-- Trim the last comma
-- Return the results
IF (DataLength(@Results) > 2)
SELECT @Results = SubString(@Results, 1, DataLength(@Results) - 2)
RETURN(@Results)
END
I have been using SQL for a while now as well, but hopefully you can help me in this area: Is there an easy way to execute a pivot table in SQL Server. I have completed the task but it is one UGLY procedure. It ended up being a cursor creating columns in a variable to be used in an exec(@variable) statement. Is there an easier way? I can send you the procedure but it is a couple hundred lines of code. Here is the begining of the main ugly query that is executing a string variable.
SELECT tp.OrderBy, tp.PropertyTestID, Replace(tp.PropertyTestName, ',', '') AS PropertyTestName,
SUM(CASE WHEN S.SampleID = 109 THEN ResultDecimal ELSE NULL END) AS [N/A109 03/31/2004],
SUM(CASE WHEN S.SampleID = 108 THEN ResultDecimal ELSE NULL END) AS [N/A108 03/30/2004],
SUM(CASE WHEN S.SampleID = 107 THEN ResultDecimal ELSE NULL END) AS [N/A107 03/17/2004],
SUM(CASE WHEN S.SampleID = 106 THEN ResultDecimal ELSE NULL END) AS [N/A106 03/01/2004],
SUM(CASE WHEN S.SampleID = 105 THEN ResultDecimal ELSE NULL END) AS [N/A105 01/05/2004],
SUM(CASE WHEN S.SampleID = 104 THEN ResultDecimal ELSE NULL END) AS [N/A104 11/17/2003]
FROM tblSamplesPropertyTest tp,
........[the rest of the query blah, blah blah...]
Obviously all subsequent queries from this main query are executed from a string variables as well. Functions are out of the question at this point as well. Is there a better way? Tell me there is...Point me in the right direction.
If you would like the entire procedure, I can email it to you.
Thanks again,
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
Sorry, I can't help you there. This is a pretty frequent thing that comes up on discussion boards, and most of the people seem to resort to doing it your way, so at least you're in good company!
Regards,
Jeff Varszegi
|
|
|
|
|
This doesn't answer your question, but it's an idea about reducing a part of your code in your function "fn_SamplesTerminalPortWellLocationText" (and, may be in the second one, if you're lucky):
-------------------------------------------------
DECLARE @Results varchar(1000) SET @Results = ''
SELECT tp.TerminalPortWellLocation
INTO #tmpTable
FROM tblSamplesSampleTerminalPortWellLocation stp,
tblSamplesTerminalPortWellLocation tp
WHERE stp.TerminalPortWellLocationID = tp.TerminalPortWellLocationID
AND stp.SampleID = @SampleID
AND (stp.Deleted Is Null Or stp.Deleted = 0)
ORDER BY TerminalPortWellLocation
UPDATE #tmpTable
SET @Results = @Results + TerminalPortWellLocation + ', '
SET @Results = left (@Results, len(@Results) - 1)
-------------------------------------------------
NOTE: It's "-1" because the blank after the comma is not considered as a character for the "len" function (SQL Server flavor of SQL ?)
This works pretty well. I took it from somebody else in another forum (I infortunately don't remember who) and use it since.
symagi
|
|
|
|
|
Hello,
I have a W2K service that opens and use connection to MSAcess DB via DSN. This is basically what I do:
[...]
CDatabase* pCDB; // pointer to CDatabase object
pCDB->OpenEx(strDSN,CDatabase::noOdbcDialog);//opens the db connection via DSN
CRecordset * ptrRecordset = new CRecorset();
ptrRecordet->m_pDatabase = pCDB; // points to the already opened db connection
if (!ptrRecordSet->Open(CRecordset::snapshot, SQLquery, CRecordset::none))
// this is what launches the CDBException with m_errCode 1002
any hint?? anybody had this behaviour as well??
Let me say that it works in the Debug version of the service but not in the Release version.
MANY THXX!!!
|
|
|
|
|
Hi,
How do I create a Database in Sql server CE? I installed SQLSCE on my pc and I need to transfer datas on my device.
My device is a Dolphin 9500, with Pocketpc2002 and I just installed on it Isqlw20, with his query analizer..
So: my question is: if i have a DB in access.. MyDB.mdb .. how do i export its tables on SQL server CE..? or:
how do I create a new DB using SQL server CE? ..I created a new virtual directory.. but now?
|
|
|
|
|
HI, I'm writing a program that use ADO to access ms SQLserver, I import msado15.dll in my program.every thing is ok,
but when I use a Connection object's Execute method to execute a stored procedure, and it return a pointer to a
recordset object, the question occered.I use the recordcount propery to get the count of the record in the recordset,
if the stored procedure not use transction, it works good, but if the stored procedure use tansction,the recordcount
that the recordset is -1, and then a exception raised.the connection use static cursor, unspecfied lock, the command
type is unknow,option is -1, can you help me? thx.
|
|
|
|
|
ASP & SQLserver2k & Windows2003
I use ASP to add data to 2 tables
I have 2 tables: Invoice and InvoiceDetail
Invoice contains: InvoiceID, ClientName, InvoiceDate........
InvoiveDetail contains: InvoiceID, Name,Price,Quantity....
I set default of InvoiceID(Invoice table)=newid() so I dont need to insert a new value when I add a new record but I can not get value of newid()
Example:
Invoice for Client "Tom"
I use "INSERT Invoice (ClientName, InvoiceDate) VALUES ('Tom','30/1/2004')"
and SQLserver2k auto create a value for InvoiceID such as {BB10687B-87F7-44E4-9397-8D510BBDFA9C}
and then ..... I WANT TO GET {BB10687B-87F7-44E4-9397-8D510BBDFA9C}
I NEED IT to add detail of this invoice for InvoiceDetail table like:
InvoiceID - Name - Price - Quantity
{BB10687B-87F7-44E4-9397-8D510BBDFA9C} - book - 26.5$ - 5
{BB10687B-87F7-44E4-9397-8D510BBDFA9C} - CD - 700.95$ - 1
I think
1) SELECT InvoiceID FROM Invoice WHERE ClientName='Tom' AND InvoiceDate='30/1/2004'" but this is not good, may be duplicate all value in all column
2) May I have to add a new column in table
3) I have to calculate my InvoiceID and not use newid() function of SQLsrv2k
Please help me to get value of newid() function in SQLserver2k
thanks alot
|
|
|
|
|
From what little testing I have done, I don't thing you will be able to obtain the GUID from the table after an insert like with an auto incrementing or random integer column. With an integer you can use SELECT @ID = @@Idendity. But it doesn't appear to work with the GUID.
DROP TABLE tempdb..TestGUID
DROP TABLE tempdb..TestAutoID
GO
CREATE TABLE tempdb..TestGUID (
ID uniqueidentifier ROWGUIDCOL Not NULL DEFAULT (newid()),
TestValue varchar(50)
)
CREATE TABLE tempdb..TestAutoID (
ID int Identity (1, 1) Not Null,
TestValue varchar(50)
)
GO
INSERT INTO tempdb..TestGUID
( TestValue )
SELECT 'Test GUID'
SELECT @@Identity
INSERT INTO tempdb..TestAutoID
( TestValue )
SELECT 'Test Integer ID'
SELECT @@Identity
SELECT * FROM tempdb..TestGUID
SELECT * FROM tempdb..TestAutoID OutPut
(1 row(s) affected)
----------------------------------------
NULL
(1 row(s) affected)
(1 row(s) affected)
----------------------------------------
1
(1 row(s) affected)
ID TestValue
------------------------------------ --------------------------------------------------
E51F034D-BD69-4A34-9983-A2B1007FBA74 Test GUID
(1 row(s) affected)
ID TestValue
----------- --------------------------------------------------
1 Test Integer ID
(1 row(s) affected) As you can see the @@Identity returns a null for the GUID and the actual value for the integer ID. You may have to obtain the GUID value prior to the insert by placing it in a variable. Then you can use the variable in both the header and detail tables.
Sorry I couldn't be much more help.
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
I want to join 5 tables and in that way get returned a schema of 10 columns that I can fetch with a recordset that I have made in C++ for this reason, but if one or more of the tables are empty (with this ID) the returned schema doesn't get 10 columns (or doesn't come at all depending on how you do it) and so I cant fetch it with my recordset. I want those column empty if the row is not there. I know I can declare variables like: select chvName = '' from Person
but I dont know if it is empty when I do my sql-string without checking that first. Anybody that is good at Transact-SQL?
Robert
|
|
|
|
|
Select A.Column1, B.Column1
From tblA A, tblB B
WHERE A.Column1 *= B.Column1
If B.Column1 does not exist you will return A.Column1's Value and Null for B.Column1's Value.
The *= or =* is the same as an inner or outer join in T-SQL.
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
Thank you very much, now it works!
|
|
|
|
|
*= and =* are the same as LEFT JOIN and RIGHT JOIN, no? And I don't think they're supported any more, Microsoft's reason being that they're not in the SQL-92 standard. I'd counsel a newbie to use left and right joins instead.
Regards,
Jeff Varszegi
|
|
|
|
|
I read in an internal document from our company DBAs saying the "SQL Server 2000 can cache execution plan for any SQL statement if the names of the objects in the statement is fully qualified" (for example, select * from [dbo].MyTable). The document did not say that this is for SQL statements in stored procedures only.
I am not very knowledgeable about SQL servers. My question is, does this mean that I can use dynamic SQL statements and get some of the same benefits as stored procedures?
Thanks.
My articles and software tools
|
|
|
|
|
Yes, SQL Server does cache execution plans for arbitrarily-supplied SQL statements. The reason for requiring full qualification is simply because different users can own objects with the same name.
SQL Server tries to automatically determine which parts of the query are parameters (called auto-parameterization). This allows it to match multiple queries to the same plan.
Using a stored procedure instead means that SQL Server will take fewer resources trying to determine whether a query is a match for a cached query plan, and fewer resources in auto-parameterization (since you've already told it what parameters you have).
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
|
I have a Database project in which I put all my DDL code. I have a database connection for this project connecting to an Oracle database. Unlike when I am using SQLServer, my scripts do not run properly when I right click on a script file and say run.
If I have a simple select * from table then it runs. However the DDL such as:
CREATE TABLE "CMD"."TBL_BLAH" (
"BLAH_ID" VARCHAR2(38) NOT NULL,
"DESCRIPTION" VARCHAR2(50) NOT NULL,
PRIMARY KEY("BLAH_ID")
)
/
I get an error saying:
ORA-00922: missing or invalid option
The same runs on sqlplus.
Ok, so now I removed the / and it owrks. But I have a new problem. The DDL is actually:
CREATE TABLE "CMD"."TBL_BLAH" (
"BLAH_ID" VARCHAR2(38) NOT NULL,
"DESCRIPTION" VARCHAR2(50) NOT NULL,
PRIMARY KEY("BLAH_ID")
)
GRANT SELECT ON "Q"."TBL_BLAH" TO "BLAH_BLAH";
And now I get the same error again. Anyone know how to set these scripts up so that they can be run from the IDE?
Thanks.
|
|
|
|
|
Which IDE do you mean? Assume that you mean Enterprise manager?
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
Ah, I should have made myself clear. I meant the VS .Net IDE. The IDE lets you create Database projects and you can execute DDL from the VS .Net IDE itself. Works really well with SQLServer. With Oracle I am having problems as mentioned in my post.
|
|
|
|
|
Ahhhhh.....don't have an ORA installation here to test, but if manage it, will let you know....
Sorry about that!
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
I have a web-server that has an MSDE database. I'm finding it a bit of a pain to administer the database just using the command line tools.
Can anybody recommend a light-weight visual tool that has the main functionality of SQL Enterprise Manager. (In particular, Users and Roles configuration).
I'd prefer a desktop app rather than a web-hosted app.
Michael
But you know when the truth is told,
That you can get what you want or you can just get old,
Your're going to kick off before you even get halfway through.
When will you realise... Vienna waits for you? - "The Stranger," Billy Joel
|
|
|
|
|
If you have Visual Studio 6, then you get the MSDE for Visual Studio - which includes EM, and QA. If VS.NET, then can do alot of jobs through Server Explorer.
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
RichardGrimmer wrote:
If you have Visual Studio 6, then you get the MSDE for Visual Studio - which includes EM, and QA. If VS.NET, then can do alot of jobs through Server Explorer.
Thanks, but I need something to install on the remote web-server. The machine is locked down and so I can't access it remotely apart from via terminal services.
Michael
But you know when the truth is told,
That you can get what you want or you can just get old,
Your're going to kick off before you even get halfway through.
When will you realise... Vienna waits for you? - "The Stranger," Billy Joel
|
|
|
|
|