|
I would like to access the data to SQL server on a remote machine. Are there any issues when connecting via an IP address vs. writing my own server and marshalling the queries through it. I would prefer to connect directly so I have all the power of recordsets locally in my application.
Some of the issues I would like to know about are:
1) Security - Does the data come in a raw format with the schema and everything?
2) Performance - What kind of overhead does the transfer make?
3) Usability - Is it really as easy as connecting to SQL server on a local machine?
4) Anything else I should know
Cheers,
Clint
|
|
|
|
|
1. Unless you enable protocol encryption the data is transferred a a binary stream (TDS- Tabular Data Services) protocol for the marshalling - SQL proprietary format. The Schema will also be there if it would have been there on a local call.
2. Performance is better than marshalling datasets, but will depend on the speed and available bandwidth of the network connection.
3.Security is more of an issue, and will depend on how you set up SQL. Best is NT authentication(Trusted_connection=yes in connection string), but this requires that the client either be in the same NT domain, or that the user on the client have an account on the server with identical username and password and that account has necessay priveleges to talk to the db in question.
4. Use TCPIP net protocol for best performance (Network=dbmssocn in connection string). Design the application to minimize round trips to the server. Use connection pooling on the client. Use stored procedures werever possible.
Read the SQL dos & donts article All of the advice applies...
Good luck
Rob
Trust NoOne...
|
|
|
|
|
Does anybody know how to force the SQL Server OLEDB provider to output narrow characters instead of wide ones? I need to test a couple of things.. 
I imagine it's a matter of tweaking the connection string/properties, but I just can't find it in the MSDN (as usual  ).
--
standing so tall, the ground behind
no trespassers, on every floor
a garden swing, and another door
she makes it clear, that everything is hers
A place of abode, not far from here, Ms. Van de Veer
|
|
|
|
|
Here is a resultset:
ID Name Value
---------------------------------
101 Name1 Value1
101 Name2 Value2
101 Name3 Value3
101 Name4 Value4
101 Name5 Value5
101 Name6 Value6
Could anyone tell me a way that could transform the above resultset in something like next one? (I'd prefer an SQL approach / not cursors and so on... but i appreciate them anyway).
Name1 Name2 Name3 Name4 Name5 Name6
----------------------------------------------
Value1 Value2 Value3 Value4 Value5 Value6
Is there any solution that don't use OLAP or extended procedures?
ThanX,
In MSAccess things are simple (see Crosstab query) - but in SQL Server 7 I don't know how to do it.
|
|
|
|
|
acosmin wrote:
Is there any solution that don't use OLAP or extended procedures?
Absolutely. Ok, for my 2 cents worth I think you need to look at what you are asking. An SQL statement is returning a recordset to you, you however just want to modify the display. While manytimes "modifying" the display of a recordset can be easily done within the SQL statement itself, manytimes this is not the route to follow. What I am getting to is if you iterate through the recordset and throw the values into an array structure you can easily display your output as you want (from the array) it without modifying the SQL statement at all. What language are working with? Does this help at all?
Nick Parker
May your glass be ever full.
May the roof over your head be always strong.
And may you be in heaven half an hour before the devil knows you’re dead. - Irish Blessing
|
|
|
|
|
Here is a resultset:
ID Name Value
---------------------------------
101 Name1 Value1
101 Name2 Value2
101 Name3 Value3
101 Name4 Value4
101 Name5 Value5
101 Name6 Value6
could anyoane tell me a way that could transform the above resultset in something like next one? (I'd prefer an SQL approach / not cursors and so on... but i appreciate them anyway)
Name1 Name2 Name3 Name4 Name5 Name6
----------------------------------------------
Value1 Value2 Value3 Value4 Value5 Value6
ThanX,
In MSAccess things are simple but in SQL Server 7 I don't know how to do it.
|
|
|
|
|
Hi
How does one differenciate between say two Routines when querying the INFORMATION_SCHEMA ? It doesnt seem to return a column with that value...
Thanx
"I dont have a life, I have a program." Also, I won't support any software without the LeppieRules variable.
|
|
|
|
|
Greetings,
I recently created a table which has a datetime varible which stores the date and time such as this "11/18/2002 1:30:33 PM".
But now I want to have milliseconds stored in this as well, such as shown below.
"11/18/2002 1:30:33.33 PM".
By default it does not allow me to add the ".33", so I am assuming it is just a formating setting when I create my table, but I am not sure how to do this? Any suggestions on how to do this?
Here is a sample section of my code of how I create my table with the date, and how I do insertion of the data. I am using sql server 2000, and ADO via Visual C++ 6.
CREATE TABLE MyTestSql
(
MyTestDate datetime
}
INSERT INTO MyTestSql(MyTestDate)
VALUES('11/18/2002 1:30:33 PM')
Thanks in advance for help?
|
|
|
|
|
Greetings,
As usual if you work on something, right after you make the post you find the solution to your problem.
It seems that microsoft sql server rounds up the time value's millisecond, so that it doesn't show up in the database. It had nothing to do with formating the date and time.
Here is a link that explains a solution on how to get your milliseconds back.
http://support.microsoft.com/default.aspx?scid=KB;en-us;q186265
|
|
|
|
|
Hai all,
We have a sql server table (which contains a Guid col + others). For processing the data in this table (about 0.1 million records a day), we move the data to access db on the user's desktop.
Currently we are using ADO to access the sql server and update the local access db (using batch update). It takes about 10-15 minutes for the whole operation. Is there a faster way to do this ? We have a requirement to move records from SQL to access based on certain filter criteria .
Any pointers will be greatly appreciated
|
|
|
|
|
Have you looked at DTS? I'm not sure if it'll be any faster but I use it a lot to move data from one place to another.
Michael
Life’s not a song.
Life isn’t bliss.
Life is just this.
It’s living. -- Buffy the Vampire Slayer: Once more, with feeling
|
|
|
|
|
Ravish wrote:
Currently we are using ADO to access the sql server and update the local access db (using batch update). It takes about 10-15 minutes for the whole operation. Is there a faster way to do this ? We have a requirement to move records from SQL to access based on certain filter criteria .
As Michael suggest, use DTS. It will work (with filtering and all sorts of other options) but you won't have any fun. DTS is painful IMO.
Paul Watson
Bluegrass
Cape Town, South Africa
Colin Davies wrote:
...can you imagine a John Simmons stalker !
|
|
|
|
|
I am trying to (re)write a knowledgebase, cuz it sux a$$.
Anyways...I have a keywords field in the database and the user enters keywords into an input field.
The code constructs a query which looks something like this:
SELECT * FROM kb_articles WHERE Keywords LIKE '%Show%' OR Keywords LIKE '%Me%' OR Keywords LIKE '%the%' OR Keywords LIKE '%money%'
I'm not a whiz at SQL so i'll explain myself here a little incase my syntax if off or something...
Obviously i'm selecting records from a table called kb_articles which has the following structure:
PKID | Question | Answer | Keywords |
Keywords and Answer should be searched for potential results. As far as I can tell, the above won't work (i'm using mySQL BTW) because LIKE chokes on multiple words.
Example?
Question entry page:
Question: How can I make lots of money?
Keywords: Show me the money
The script then parses Keywords and Questions into individual array elements (seems logical) and those items are what get compared to each record inside kb_articles keywords field. Only the keywords field can also have multiple keywords and these aren't parsed into indy words, so basically what's happeneing is this:
kb_articles.keywords = "How, To, Show, The, Make, Money"
SELECT * FROM kb_articles WHERE keywords LIKE '%Show%'....
LIKE i think chokes on multiple words inside the database keyword field...
So my question is this...Is there a way I can use SQL regex to compare I have no idea about anything REGEX...  or am I going to have to execute multiple queries constructed using PHP...?
Thanks in advance
"An expert is someone who has made all the mistakes in his or her field" - Niels Bohr
|
|
|
|
|
Sorry ,I can't understand what say.
In the example that you say,what does the query return?
Mazy
"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits
|
|
|
|
|
That SELECT statement looks like it should work but you will have to put more LIKEs in there if you want a better search. For example, you may want to use LIKE '%Show me the money%' or LIKE '%Show%me%the%money%' or LIKE '%show%me%' etc.
Jason Henderson
start page ; articles
henderson is coming
henderson is an opponent's worst nightmare
* googlism *
|
|
|
|
|
Well,
I can't find out what kind of database uses .idx and .dat files for storage of data. I have tried several ODBC drivers but they won't work.
I hope someone has an answer to these questions,
thanx in advance,
Robert Smit
|
|
|
|
|
I've come across several in-house databases that have used those extensions, I've not come across a commercial one with these extensions. Most tend to be of the flat-file variety which usually means no ODBC support.
I have used those extensions for a flat-file database in the past but I doubt you'll be trying to connect to that 
Michael
Life’s not a song.
Life isn’t bliss.
Life is just this.
It’s living. -- Buffy the Vampire Slayer: Once more, with feeling
|
|
|
|
|
Micheal,
Thank you for your answer I think you could be right. It is only a small program that stores data to those idx and dat files. The database is used only for storing pupil en group results for math exercises. So it is not a complicated DB with a lot of stored procedures.
My question to you is how you read and write to those files in C++.
Robert
|
|
|
|
|
Without the file structures you'll have a hard task.
In the good old days when I used to try and integrate with Pegasus Accounts who used a similar system, I usually saved a record into the database using their app and then hex-edited the file to figure out what had been updated and changed.
Michael
Life’s not a song.
Life isn’t bliss.
Life is just this.
It’s living. -- Buffy the Vampire Slayer: Once more, with feeling
|
|
|
|
|
Hello,
like subject says, is there something more than setting a password for the database that I can do to make sure that people don't open an MDB file and mess with it?
Thanks!
---------------
Concentrating on Ideas
http://www.edovia.com
|
|
|
|
|
Hey LukeV,
You may choose to open an MDB in exclusive mode and set password to it. So that each time the MDB file is opened, it will ask password.
Deepak Kumar Vasudevan
http://deepak.portland.co.uk/
|
|
|
|
|
Hi.
I've got a basic ADO AddNew call, running in a loop, against Access 2002. Running on VC++6, Win2K, MDAC 6 sp2. See code below.
It seems to use memory constantly, even when the recordset object is released. It uses approx 10MB per 100,000 rows written. My system needs to be on line and writing data for a week at a time so this is going to be a problem for me. Any ideas?
Ta.
Gizz.
The following function is called from another big loop...
void fn()
{
using namespace ADODB;
_RecordsetPtr pRst = NULL;
_ConnectionPtr pConnection = NULL;
_bstr_t strCnn("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\data.MDB");
try
{
TESTHR(pConnection.CreateInstance(__uuidof(Connection)));
pConnection->Open(strCnn,"","",adConnectUnspecified);
TESTHR(pRst.CreateInstance(__uuidof(Recordset)));
pRst->Open("tblMyTable",_variant_t((IDispatch *) pConnection, true),
adOpenKeyset,adLockOptimistic,adCmdTable);
_variant_t strGBID("GBID");
_variant_t strTSID("TimestampID");
static long i = 0;
static long imax = 1000;
for (;i < imax; i++)
{
pRst->AddNew();
{_variant_t vtFld;
vtFld.vt = VT_I4;
vtFld.lVal = i;
pRst->Fields->GetItem(strGBID)->Value = vtFld;}
{_variant_t vtFld;
vtFld.vt = VT_I4;
vtFld.lVal = i+1;
pRst->Fields->GetItem(strTSID)->Value = vtFld;}
pRst->Update();
}
imax += 1000;
pRst->Close();
pConnection->Close();
pConnection.Release();
pRst.Release();
}
catch(_com_error &)
{}
}
|
|
|
|
|
I don't know where is the problem,But about pConnection.Release(); and pRst.Release(); I have to say you don't need them because you use these inrefaces as an pointer and they will release automaticlly. But you problem MAYBE because of access,I know it has limitation on number of records,and after you insert 100,000 rows in a table in crash or ....You didn't which line cause memory leak!
Mazy
"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits
|
|
|
|
|
Hi,
I am new to database programming in c++ and come across some quest. which i can't find in books.
I am programming in c++ mfc platform, using ADO to access to the sql database.
However, my database is using MS windows 2000 Terminal Services Web Connection
and i don't know how to link to the database. In Short, how is the connection string parameters to be like.
Thanks
|
|
|
|
|
Creating a database this way:
if( database.OpenEx(sSql,CDatabase::noOdbcDialog) )
{
sSql.Format( "CREATE TABLE Sounding ([Pressure (mb)] FLOAT,[Height(m)] FLOAT)");
database.ExecuteSQL(sSql);
sSql.Format("INSERT INTO Sounding VALUES('%f','%f')",mPressure,mHeight);
database.ExecuteSQL(sSql);
....
If mHeight is 17.60 it is inserted 17600000, if I change to '%.f' it is inserted 17, and if '%.2f' 1760. What is the solution to insert the right decimals?
Thanks.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
