|
This stuff is still going to depend upon Oracle client being installed. When you install Oracle client, it typically will walk through an initial network setup of a remote database. During this setup, you will need to provide the local name that defines the remote database. It is this local name that you provide as 'NameOfDatabase'. It is not a file name or anything like that.
I have used this code to connect to both Oracle 8 and Oracle 9 databases. While I haven't tried specifically with an Oracle 7 database, I would expect that it would still connect okay.
Chris Meech
We're more like a hobbiest in a Home Depot drooling at all the shiny power tools, rather than a craftsman that makes the chair to an exacting level of comfort by measuring the customer's butt. Marc Clifton
VB is like a toolbox, in the hands of a craftsman, you can end up with some amazing stuff, but without the skills to use it right you end up with Homer Simpson's attempt at building a barbeque or his attempt at a Spice rack. Michael P. Butler
|
|
|
|
|
Talking about oracle versions , is there a major difference in operation between Oracle 8 and oracle 9 . If I am going to start learning oracle fresh , is it better to start with 8 or 9 .
We will either find a way or make one !
Hanipal
Dean
|
|
|
|
|
I don't think it will matter which version you end working with in order to learn Oracle. My recommendation though would be to take a couple of hands-on training courses. The first would either be a beginner or intermediate course in how to use SQL. Then after you have become comfortable with that knowledge and perhaps even been programming, you should step up and take a database administration course. I've always felt that my best understanding of the how/what/why Oracle does, was a result of an administration course. Good Luck to you.
Chris Meech
We're more like a hobbiest in a Home Depot drooling at all the shiny power tools, rather than a craftsman that makes the chair to an exacting level of comfort by measuring the customer's butt. Marc Clifton
VB is like a toolbox, in the hands of a craftsman, you can end up with some amazing stuff, but without the skills to use it right you end up with Homer Simpson's attempt at building a barbeque or his attempt at a Spice rack. Michael P. Butler
|
|
|
|
|
This is a wierd one, 'cuz it was working just fine yesterday, and I think it has to do with _bstr_t conversions, but ....
The text in subject is that which is shown in a debug assertion error message box displayed when the m_pConn->Open(...) method shown below is called. When this line of code is executed, I am converting a CString (connectionString) to a _bstr_t. The full text of the error message is "Debug Assertion Failed! dbgdel.cpp Line: 52 _BLOCK_TYPE_IS_VALID". Can anyone shed some light on this error?
_bstr_t tablesNames;
_ConnectionPtr m_pConn;
m_pConn.CreateInstance (__uuidof(Connection));
CString connectionString;
if (usingAccess_1)
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source = " + accessDBPath2;
else
connectionString = "Provider=SQLOLEDB;Data Source=" + mvwin32SQLsrvName + ",1433;Network Library=DBMSSOCN;Initial Catalog=" + mvwin32SQLsrvDB + ";" + "Trusted_Connection=Yes;";
m_pConn->Open( _bstr_t( connectionString ), _bstr_t( "" ), _bstr_t( "" ), adConnectUnspecified );
-dotBomb
|
|
|
|
|
I have a function created in SQL Server 2000 that returns a comma delimited list of text values from a 1 to many table. At the client's site they use SQL Server 7.0. Can you compile Functions in SQL 7.0 or is this a new feature in 2000? If you can how do you do it? I am having difficulty compiling.
CREATE FUNCTION fn_SamplesTerminalPortWellLocationText
(
@SampleID int
)
RETURNS varchar(1000)
AS .... Error Message
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'FUNCTION'. 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)
|
|
|
|
|
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
|
|
|
|
|