|
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
|
|
|
|
|
Here is a web app
http://www.microsoft.com/downloads/details.aspx?FamilyID=C039A798-C57A-419E-ACBC-2A332CB7F959&displaylang=en
|
|
|
|
|
Does anyone know how to use Named Commands in ADO from C++? The MSDN shows how to use Named Commands from VB, but there is no mention of C++.
Thanks.
|
|
|
|
|
It probably requires some smart tricks with IDispatch. VB does that kind of thing behind the scenes, but you'll probably find it easier simply to keep a pointer to the command object around.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
select * from mytable where datecreated > (CONVERT(varchar(30),DATEPART(year, GETDATE())) + '-' + CONVERT(varchar(30),DATEPART(month, GETDATE())) + '-' + CONVERT(varchar(30),DATEPART(day, GETDATE())-1)) + ' 12:07:00'
|
|
|
|
|
Change the type of the column, datecreated, to be of date type instead of string or varchar type. The query would then be
select *
from mytable
where datecreated > to_date(to_char(trunc(sysdate-1),'YYYY-MM-DD')|' 12:07:00'),'YYYY-MM-DD HH:MI:SS');
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
|
|
|
|
|
I would agree Chris. Make sure the DateCreated column is a dateTime datatype and the parameter being passed is the same DateTime datatype. Also, you can put an index on the DateCreated column.
Create Index idx1 on myTable (DateCreated)
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)
|
|
|
|
|
Chris Meech wrote:
select * from mytable where datecreated > to_date(to_char(trunc(sysdate-1),'YYYY-MM-DD')|' 12:07:00'),'YYYY-MM-DD HH:MI:SS');
following not recognised by query analyzer/ T-Sql
trunc
to_date
to_char
|
|
|
|
|
Anonymous wrote:
query analyzer/ T-Sql
Sorry. That suggests you are running this on SQL Server. My example was from an Oracle database. TRUNC is just a truncation routine. In this case, the sysdate call returns a current datetime value from which the time value is truncated. TO_DATE, and TO_CHAR are just conversions routines to convert strings to dates and vice versa. There should be similar functions available on SQL Server, I would assume.
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
|
|
|
|
|
How to get 'IRowsetFind' interface in oledb sdk !
dear all:
i will use following code in my program(C++) :
discover 'pICommandProperties->SetProperties(1,rgInitPropSet);' return failed code 'DB_S_ERRORSOCCURRED'
so 'pIRowset->QueryInterface(IID_IRowsetFind,(void **)&pIRowsetFind);' return failed!
dear all, please help me! how to get 'IRowsetFind' interface in oledb sdk , and how to use 'IRowsetFind->FindNextRow()'
InitProperties[0].dwPropertyID=DBPROP_IRowsetScroll;
InitProperties[0].dwOptions=DBPROPOPTIONS_REQUIRED;
InitProperties[0].dwStatus=DBPROPSTATUS_OK;
InitProperties[0].colid=DB_NULLID;
InitProperties[0].vValue.vt=VT_BOOL;
InitProperties[0].vValue.lVal=VARIANT_TRUE;
InitProperties[1].dwPropertyID=DBPROP_IRowsetFind;
InitProperties[1].dwOptions=DBPROPOPTIONS_REQUIRED;
InitProperties[1].dwStatus=DBPROPSTATUS_OK;
InitProperties[1].colid=DB_NULLID;
InitProperties[1].vValue.vt=VT_BOOL;
InitProperties[1].vValue.lVal=VARIANT_TRUE;
rgInitPropSet[0].cProperties=2;
rgInitPropSet[0].guidPropertySet=DBPROPSET_ROWSET;
rgInitPropSet[0].rgProperties=InitProperties;
hr=pICommandText->QueryInterface(IID_ICommandProperties,(void **)&pICommandProperties);
hr=pICommandProperties->SetProperties(1,rgInitPropSet);
if (DB_S_ERRORSOCCURRED==pICommandProperties->SetProperties(1,rgInitPropSet))
cout<< "test";
if(FAILED(hr))
{
cout<< "OLEDB Error !\r\nExecute Failed To Set Rowset Properties";
}
hr = pIRowset->QueryInterface(IID_IRowsetFind,(void **)&pIRowsetFind);
if(FAILED(hr))
{
cout << "Failed to get IID_IRowsetFind interface.\n";
cout << endl;
} //end if
laker
|
|
|
|
|
I am using the Information Schema to retrive a list of tables and their columns:
SELECT TABLE_NAME, COLUMN_NAME,
DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
ORDINAL_POSITION
FROM INFORMATION_SCHEMA.columns
ORDER BY TABLE_NAME, ORDINAL_POSITION
This works fine but I get all the tables when I just want the user tables. This their any way to limit the results to just these tables. Also is their a way to determine what columns are primary keys. What other information schema would be useful?
Thanks for your help in advance.
Brian
|
|
|
|
|
Can I use ATL and MFC in an extended stored procedure (ESP)? In Santash Rao's sample "Tutorial on Extended Stored Procedures for MS SQL Server v7.0", he says that SQL Server uses LoadLibrary to load ESP DLL's. MSDN doc says AfxLoadLibrary (rather than LoadLibrary) must be used to load DLLs using MFC. His sample uses MFC. Is there a bug in his sample (the use of MFC) or is he wrong about SQL Server using LoadLibrary?
Jim Cutler
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
