|
I'm connecting to a database via DSNs. I want to figure out if I'm woring with MSSQL, Oracle or Access. Using ADO or MCF ODBC classes. How?!
|
|
|
|
|
|
I think you need to use SQLGetInfo once you've opened the connection, passing the SQL_DRIVER_NAME value for the InfoType parameter. That's based on my reading of the documentation.
You could also try SQL_DATABASE_NAME or SQL_DBMS_NAME.
When using ADO with a DSN, you're actually using the MSDASQL provider, which is basically a translation layer on top of ODBC. It may be possible to retrieve the underlying ODBC connection handle by looking at the Properties collection of the ADO Connection object.
|
|
|
|
|
Hi!
I am trying to drop a login but it owns a few databases. I know about the sp_changedbowner sproc, but I'd like to know if there's a way to remove the ownership per user (for all dbs)... for example: sp_removeownership 'login'.
I need to do this before dropping a login that owns some databases. Database is MS-SQL.
Thanks!
Carl
|
|
|
|
|
 I am creating an application for a local education company that is basically an off line tutorial application.
They eventually wish to download the application and start up files then use explorer to pull more files in as the students progress, but for the time being a simple distributed application on their network locally.
I have created a simple MFC single doc application, and the view class is based on HTMLview, this allow me to load HTML files into the app easily.
I have decided to create the pages to be loaded by using a script language (haven't decided which one yet) within HTML doc's.
My question is can i use SQL to store and retrieve HTML files, My idea was to load the files into an SQL database, then use a simple CDatabse class or maybe ADO to load and unload the files, probably ADo since it is more flexible. if i can, then this is a great advantage since the target network has SQL.
Am i trying to be too ambitious, is there an easier way to manage these files from a central location using windows, the pages need to be loaded in sequence.
Any responses are greatly appreciated, i do not wish to code from scratch
Thanks to all whom respond.
Joe.A
|
|
|
|
|
You can use ado to store the files in a LongText (Memo for Access) or LongBinary field, but the retrieve performance is likely to be poor. A better idea would be to put all the files in a known directory structure, then store the file path in SQL. You could fetch the sequential list of paths quite quickly, then open the appropriate file. This would also simply file maintenace, as modifications to a document would be reflected 'instantly' in the app with no SQL update needed. Changing the sequence would require just modifying a sequence field in the record.
for example: the table could be doc_id int (identity,1,1) , Doc_seq int,
Doc_Name Varchar(255), Doc_Path Varchar(255). I would initially make the doc_seq values increment by 10 or so (leave space for insertions into the sequence. Retrieve with "order by doc_seq" ....
Genius may have its limitations, but stupidity is not thus handicapped. - Elbert Hubbard
|
|
|
|
|
Hi
How we can create a new database for example access database that does not exist before by ADO.NET?
the code must create files that needed for database (in this example .mdb file)
and I want to know how create a new table in database and update database.
Regards' Amir Jalaly
|
|
|
|
|
The easiest way for Access is to simply copy an empty access database (or a "template" with your data structures but no data) using filecopy. ADO.Net doesn't support "create Database" for Acess (actually it is the underlying oledb provider that doesnt support this), and the ADOX "create catolog" stuff isn't there either. To create a new tqable, juse use and execute query (OledbCommand.Execute or OledbConnection.Execute) like "Create Table TableName
(Field1 Int Null, field2 Varchar(255), field3 Double)" If you need spaces (or keywords) in table/field names, then enclose them in sqare brackets [Field and Space] int,...
Genius may have its limitations, but stupidity is not thus handicapped. - Elbert Hubbard
|
|
|
|
|
When I run this query I get 2 row back with different dates:
SELECT Sdate1,EDate1,SDate2,Edate2
FROM norm,elig
WHERE norm.Number=elig.Number
and norm.Gnumber=elig.gnumber and (norm.Number = '15')
I would like to get only one row back with Sdate1,Edate2.
How do I do this?
|
|
|
|
|
Without knowing anything about your data the following is the best I can manage:
SELECT TOP 1 Sdate1,Edate2
FROM norm,elig
WHERE norm.Number=elig.Number
and norm.Gnumber=elig.gnumber and (norm.Number = '15')
--Colin Mackay--
"In the confrontation between the stream and the rock, the stream always wins - not through strength but perseverance." (H. Jackson Brown)
|
|
|
|
|
Hi,
I need your help.....I want to add numbers stored in the DB and display them on the ASP page.
My Access DB looks like this:
WeekNo Course1 Course2 Course3 Course4
1 2 4 5 1
2 4 6 7 9
I want users to get thier totals for differnt weeks and get their results displayed on the screen via an ASP page.
What I have at the moment is: Users are able to Enter their data and View it then are able to update it. I need code that can get me started.
Thanks in advance.
Petunia
|
|
|
|
|
I do not know how many people out there need something like this, but I recently had use for it with my new dedicate server across the country. They have an MSDE installed database on there but it was not configured for remote connection. To make matters worse, they block port the SQL port to protect from viruses.
Well, I had to reconfigure it so that I could connect to it with Enterprise Manager (since I am a spoiled brat). I found this freeware tool that makes it an easy matter to configure your server along with other features. It is not Enterprise Manager, but it is a lifesaver:
http://www.asql.biz/DbaMgr.shtm[^]
It also has the ability to generate an Insert script for your data in a table. That can be handy when you want to move a table of data quickly or genrate a script to prepopulate a new database.
Rocky <><
www.GotTheAnswerToSpam.com
|
|
|
|
|
I am trying to find a method to return two results sets from a single query that can be handing by one call to the SqlDataAdapter. I am using paged data and I need to return a table with the current page of rows along with the total count of rows that would match (not limited to the page being returned).
The stored procedure I am using has selection for the current page of rows and then does another select for the record count. I have not been able to get the data adapter to populate another table in the dataset with the record count.
Any idea?
Rocky <><
www.GotTheAnswerToSpam.com
|
|
|
|
|
Using multiple SELECTs with a DataAdapter yields multiple DataTables in a DataSet that you pass to DataAdapter.Fill. If you want any of the DataTables named, you must use a DataTableMapping which can be assigned to the DataAdapter.
When you use a SqlCommand, you can get multiple result sets by calling SqlCommand.ExecuteReader with either no parameters or CommandBehavior.Default, so long as CommandBehavior.SingleResult is not passed. Then you loop over SqlDataReader.NextResult() and inside loop over SqlDataReader.Read().
-----BEGIN GEEK CODE BLOCK-----
Version: 3.21
GCS/G/MU d- s: a- C++++ UL@ P++(+++) L+(--) E--- W+++ N++ o+ K? w++++ O- M(+) V? PS-- PE Y++ PGP++ t++@ 5 X+++ R+@ tv+ b(-)>b++ DI++++ D+ G e++>+++ h---* r+++ y+++
-----END GEEK CODE BLOCK-----
|
|
|
|
|
I must be doing something wrong. It does not seem to pick up the second result set. The stored procedure returns two result sets as verified in query analyser but when I feed to to the sda which has been generated in the visual designer and supplied with a typed dataset, it only has one table in it.
Rocky <><
www.GotTheAnswerToSpam.com
|
|
|
|
|
If you're using a strongly-typed DataSet, then you have named DataTables and must use a DataTableMapping on your SqlDataAdapter like I mentioned previously. Both tables must be mapped accordingly.
-----BEGIN GEEK CODE BLOCK-----
Version: 3.21
GCS/G/MU d- s: a- C++++ UL@ P++(+++) L+(--) E--- W+++ N++ o+ K? w++++ O- M(+) V? PS-- PE Y++ PGP++ t++@ 5 X+++ R+@ tv+ b(-)>b++ DI++++ D+ G e++>+++ h---* r+++ y+++
-----END GEEK CODE BLOCK-----
|
|
|
|
|
Heath Stewart wrote:
must use a DataTableMapping on your SqlDataAdapter
Actually, after looking at the results, the dataset does not get two tables, only one. Not sure what could be causing that. I can run it in QA and the sp returns two result sets. Will have to dig further.
Rocky <><
www.GotTheAnswerToSpam.com
|
|
|
|
|
It can, actually. The trick is to set up your tables mappings by using a strongly-typed DataSet that you already made and using the SqlDataAdapter's designer. In any case, dragging-n-dropping controls and using designers makes for poor coding - getting your hands dirty in code is what it's all about. Heck, after I designer my initial layout in the forms designer I usually end up breaking the designer support because I completely rearrange the code for faster load times, localization, and many other things.
Besides, table mappings are fairly easy to code. There's not much there, just table-table pairs, and each of those has column-column pairs. Hand-coding a SqlCommand with various parameters (including outputs) is much more tedious.
-----BEGIN GEEK CODE BLOCK-----
Version: 3.21
GCS/G/MU d- s: a- C++++ UL@ P++(+++) L+(--) E--- W+++ N++ o+ K? w++++ O- M(+) V? PS-- PE Y++ PGP++ t++@ 5 X+++ R+@ tv+ b(-)>b++ DI++++ D+ G e++>+++ h---* r+++ y+++
-----END GEEK CODE BLOCK-----
|
|
|
|
|
Sorry for wasting you time. It is due to a very bad programming habbit I have gotten into lately where I wrap base code in a try-catch to nullify the error until a later time when I get the error logging installed at that level. Yep, it was blowing an error in the base classes of my data manager that I did not notice since I cancelled them 
Over all though, it has been a learning experience for multiple result sets. Found tha the visual designer in VS.NET handles them pretty easily while configuring your field mappings as long as it can parse your query properly. I used a temp table inside the stored procedure and the designer errored out and would not let me go further.
Anyway, now that I have had a little pain in this area, I am sure to remember it for the future and will probably work more with passing back statistical information in a second result set. Works well.
Thanks!
Rocky <><
www.GotTheAnswerToSpam.com
|
|
|
|
|
Hi. I have 2 tables, prod and inv. These two tables are joined by invProductID (the primary is in prod). So my question is how do I make a sp too insert data into both these tables? I have just started working with SQL, so am not sure on this. Because, in order to insert into table inv, I will need the primary key value from prod.
Is there a way to do this, or do you have to take on a transactional approach? Or, if I do it in 2 sp's, how do I return the invProdutID from products, to use in inv.
Hope this makes sense.
|
|
|
|
|
I'm assuming SQL Server 2000, and that you have no usable key data (for example, it's possible for every field in two different records to be identical, apart from a key field).
If you're generating your own primary keys, there shouldn't be an issue. What you'd normally do is keep some form of incrementing identifier in a separate table. You'd read the next value into a variable, update the stored value, then use that key information in both places.
If you're using automatic numbering (using an IDENTITY column), you can retrieve the last inserted identifier using the SCOPE_IDENTITY() function. You need to retrieve this straight after the first INSERT, otherwise it may not be the correct identifier.
Depending on your requirements, you may want to put both INSERTs inside a transaction. Do this if you want to be sure that either both, or neither, INSERT completes. Be aware that if an error occurs, the transaction is not automatically rolled back. See SQL Server Transactions and Error Handling[^] in the Articles section.
|
|
|
|
|
Apologies if this is a blonde question but does anyone know of a tool that specifically monitors bandwidth used by MSSQL server? i.e. We have a SQL database server box and we want to monitor and tally all calls to the SQL port.
Our sys admin tells me that the normal bandwidth monitoring software he is using cannot track SQL usage even though in my mind it is just another port.
regards,
Paul Watson
Bluegrass
South Africa
Brian Welsch wrote:
"blah blah blah, maybe a potato?" while translating my Afrikaans.
Crikey! ain't life grand?
|
|
|
|
|
I guess you could use packet sniffing software such as WinPCap.
Have you tried the SQL Profiler? I know you can set a number of filter options and log them to a file.
Rocky <><
www.GotTheAnswerToSpam.com
|
|
|
|
|
This may be a silly question but I'm a bit stuck and am asking for clever ideas 
When I insert text into the DB i use a function called PrepSQL which just replaces ' with ''. All very well, but I now have a problem when the content is edited. Each individual ' get replaced so I end up with "it'''''''''''''s". Any ideas how to check for this and resolve it 
|
|
|
|
|
Use parameters on your command objects (ADO) or on your SQLExecute (ODBC). If you do that, you shouldn't need any reserved character escaping codes - and your statements may run faster too.
SQL string construction is basically brain-dead, unless you have no alternative, or you want to keep your code database independent. Even then, parameters are usually a better choice.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
