|
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
|
|
|
|
|
Yes, I believe you can use these libraries.
AfxLoadLibrary is intended for use within an MFC application or library to load another MFC library which extends MFC classes - what the documentation refers to as an 'MFC Extension DLL'. It simply takes a critical section object before calling LoadLibrary to ensure that certain MFC global structures are not corrupted in a multithreaded program by the code that runs in the extension DLL (basically adding itself to the list of loaded modules).
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Thanks, Mike. You're right. I had missed MSDN article 249235 which explains that the AfxLoadLibrary doc is incorrect in that AfxLoadLibrary only applies to extension DLLs not all MFC DLLs.  Thanks, again. -- Jim
Jim Cutler
|
|
|
|
|
Hello ,
I just finished developing a program using Microsoft Desktop Engine with C# as the front end. Everything is okay on my system. But, on trying to deploy it on a different system it gives an error message that it can open the database.
This is my first time in using the Microsoft Desktop engine. All this while I have been using Access as my database engine and normally what I do after developing a program is to transfer the Database file in Access after installation to the client’s system and everything will be okay. But with the Desktop Engine what I did was to
1) Install it on the client computer
2) Transfer the Database file to the client’s system.
On running it was giving me message that it can’t open it.
I would be glad if anybody can show me how Microsoft Desktop Engine is deployed together with a program developed using it as the database engine.
Thanks in anticipation.
|
|
|
|
|
I don't think you can just up and transfer the database file. Try doing this instead:
osql -U[username] -P[upass] -S[database] -Q "BACKUP DATABASE [database] to DISK='c:\blah.backup'"
Move blah.backup to the new system, then run the same command with statement "RESTORE DATABASE [database] from DISK='c:\blah.backup'"
This should move the data + schema. You'll probably want to run it as sa, so a sample command is:
osql -Usa -Ppassword -SCOMPNAME\DBNAME -Q "BACKUP test01 TO DISK='C:\backup'"
HTH
-earl-
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
