Database

Problem in using OleDbDataReader

7-Nov-03 5:38

Here is a basic definition of SQLDMO that I found here: http://www.expresscomputeronline.com/20030616/techspace1.shtml

"DMO is a set of objects that represents the SQL Server infrastructure. This is a very rich and complex object model. It is not intended to provide data handling, which is best done by traditional SQL commands.

DMO is designed for administration of SQL Server using a component object model approach. In fact, using DMO, you can do things that are impossible with Enterprise Manager. Moreover, it is often much simpler to write a DMO based code rather than trying to find equivalent Transact SQL syntax for achieving certain administrative tasks."

Things I've used it for include:
- Listing available SQL Servers
- Listing available databases in a given server
- Programmatically "walking" a set of database tables for generating database objects based on those table

(just to name a few). It is a very useful component. I found a link here http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q287/7/37.ASP&NoWebContent=1 that demonstrates listing available SQL servers using C++. I'm sure there are many more examples out there, so you should just do a google search for the term "C++ SQLDMO" or something like that. There tend to be more examples out there written in VB or C#, however, the same component can be referenced from C++.

Let me know if you need any further clarification.

-Matt

------------------------------------------

The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall

sivaramakrishna5-Nov-03 22:14

sivaramakrishna

5-Nov-03 22:14

I have two tables called master and child tabels.For each record in the master table i have to get the information in corresponding child table.I am using the same connection.SO, when i am trying to get information from child without closing the DbReader in Master table(I need master DbReader also.So, I am intensionally not closing it) it is giving me error that the connection is already in use.

Re: Problem in using OleDbDataReader

Mike Dimmick6-Nov-03 2:32

Mike Dimmick

6-Nov-03 2:32

In ADO.NET, you can only have one command executing against a connection at a time. The DataReader has exclusive use of a connection while it's open.

You can do one of two things: you can either open a second connection to execute the 'child' commands, or you can use a DataSet to store the results of the two commands. The first option is very wasteful of resources. The second option allows you to set up DataRelation objects which allow you to navigate the child rows using the DataRow object's GetChildRows method.

If you plan to do the second, you might as well ask for both tables in the same query batch, generating two result sets.

You could also perform a join on the server side, depending on what you actually need to do with the data.

Re: Problem in using OleDbDataReader

sivaramakrishna7-Nov-03 19:06

sivaramakrishna

7-Nov-03 19:06

Thanks. I solved the problem using the Datasets. But I have another problem . I want to read BLOB from Database. When i am using DataReader I have a function Called GetBytes which will serve my purpose. But now i am using DataSet.Can u tell me how can i Read the BLOB from a DataSet?

dbo

Hugo Hallman5-Nov-03 12:19

Hugo Hallman

5-Nov-03 12:19

How to change the user assocuiated with dbo.
The problem is that when users enter stuff personal to them and I use the default USER (without quotes) it enters correctly DOMAIN\USERNAME, but my records insert just "dbo".
I thought that changing the owner to the admin account this would be solved.

SQL!

Silly Boy5-Nov-03 6:18

Silly Boy

5-Nov-03 6:18

When I insert a date value into Database, I can use either “Insert into TableA values(‘” + date + “’)”, or “Insert into TableA values(#” + date + “#)”.
Could you show me the difference of two SQL above?
Thanks!

xyz

Re: SQL!

Jeff Varszegi5-Nov-03 7:34

Encoding problem using ADO Stream

5-Nov-03 7:34

You can't do this in SQL Server, at least with Transact-SQL. Here's a test I just ran:

CREATE TABLE #test (
   dt DATETIME NULL
)

INSERT INTO #test VALUES ('2003-01-01')
INSERT INTO #test VALUES (#2003-01-01#)

On the second value, I get this error:

Server: Msg 128, Level 15, State 1, Line 1
The name '#2003' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

What language and database are you using?

Thank you.

Jeff Varszegi

Re: SQL!

andyharman5-Nov-03 11:49

andyharman

5-Nov-03 11:49

I believe that the #01-02-2003# version is a Microsoft extension that assumes that the date uses MM-DD-YYYY format (i.e. US date format). The other format uses the default country-specific format for the computer.

In England, I commonly see PCs that are configured with control-panel defaults of "MM-DD-YYYY" or "DD-MM-YYYY" - so if you are using MS-Access and are hard-coding dates then the hash-version is probably the safer way to go. With SQL-Server, I always use the ISO "YYYY-MM-DD" format.

If you are executing the insert statement using an ADO command then I normally add parameters to the command-object because that handles casting from the original data-type, and it prevents SQL-injection attacks.

Andy

Firas Rashid4-Nov-03 2:59

Firas Rashid

4-Nov-03 2:59

Hi all,

I have xml document with sql query that get results from SQL Server 2000, data returned can contain verious languages -Russian in my case- when i access the xml document directly from within IE it returns the results fine with Russian data encoded correctly.

Now i want to use ADO Stream object to access and execute the same xml document, the Russian data returned as question marks (?).

I tried to set the Charset proprty of Stream object to many values, including UTF-16, ISO-8859-1, Windows-1251 ... but nothing work, i tried also to set enocding property of the xml document to same values above, also same problem.

What should i do? also if database contains Indian or Japanese data, whats the solution?

Thanx in advance.

Alternative SQL Queries based on Table Data

Jo Fredrickson3-Nov-03 21:58

Jo Fredrickson

3-Nov-03 21:58

Hi,

I am using ADO to query my Access database and need to run one sql query that conditionally issues one of two complex select statements depending upon the value of a particular field in a table. The two select statements query different tables. I'm trying to use something like:

SELECT IIF((SELECT PropertyValue FROM PropertyTable WHERE PropertyCode=10)=0,
(SELECT ...), (SELECT ...))

But this only lets me return one column, whereas my select statements need to return many columns. The CASE statement seems to have the same limitation.

Any advice would really help! Smile | :)

Joanne

Re: Alternative SQL Queries based on Table Data

andyharman5-Nov-03 11:35

andyharman

5-Nov-03 11:35

Sub-selects can only ever return a single value if used in the way that you have tried. If you absolutely need to meet your requirements with a single SQL statement then you may try something like:

SELECT ...
WHERE (SELECT PropertyValue FROM PropertyTable
    WHERE PropertyCode=10)=0)
UNION ALL
SELECT ...
WHERE (SELECT PropertyValue FROM PropertyTable
    WHERE PropertyCode=10)<>0)
ORDER BY ...

The first select returns a resultset if property 10 does not exist, otherwise the second select returns its resultset. Note that both resultsets must return the same number of columns, with the same datatypes.

Hope this helps.
Andy

Re: Alternative SQL Queries based on Table Data

Jo Fredrickson5-Nov-03 14:59

Jo Fredrickson

5-Nov-03 14:59

Thanks Andy, this does exactly what I was trying to do.
Works like a charm. Smile | :)

Joanne

MSADO 2.5

Kant3-Nov-03 15:46

Kant

3-Nov-03 15:46

I get the following error when I compile a project.

C1083: Cannot open type library file: 'C:\\Program Files\\Common Fi les\\SYSTEM\\ADO\\msado25.tlb': No such file or directory<br />
Error executing cl.exe.

I got MSADO20.tlb and MSADO21.tlb in my machine, but not MSADO25.tlb

Is this right location[^] to download MSADO2.5?

As per MSDN, 2.8[^] is the latest version.

What's the diff between MSADO 2.0, 2.1, 2.5 and 2.8?

TIA

"Whidbey"..."Orcas"...Roadmap
This signature was created by "Code Project Quoter".

Re: MSADO 2.5

Mike Dimmick5-Nov-03 1:51

Mike Dimmick

5-Nov-03 1:51

If you're running Windows 2000 and haven't obtained MDAC 2.6, the type libraries for version 2.5 are kept in msado15.dll. The latest versions are always found in msado15.dll; the .tlb files are shipped with each new version of MDAC in order to target the previous versions.

For example, I have MDAC 2.7 SP1 installed on this Win2k machine, so I have msado20.tlb, msado21.tlb, msado25.tlb and msado26.tlb.

If you're using SQL Server 2000, you should install at least MDAC 2.6; since SQL Server 2000 should be at minimum SP2, you should have MDAC 2.6 SP1.

You can check your version of MDAC using MDAC Component Checker[^].

Re: MSADO 2.5

Kant5-Nov-03 5:01

Kant

5-Nov-03 5:01

Mike Dimmick wrote:
the type libraries for version 2.5 are kept in msado15.dll.

Confused | :confused:

I thought version 2.5 will be kept in msado25.dll

The error says the following file not found

'C:\\Program Files\\Common Files\\SYSTEM\\ADO\\msado25.tlb'

Mike Dimmick wrote:
since SQL Server 2000 should be at minimum SP2, you should have MDAC 2.6 SP1.

I don't have the SQL Server 2000 right now but I will be installing it soon. Will this installation takes care of MDAC 2.6?

Mike Dimmick wrote:
MDAC using MDAC Component Checker[^].

Thanks for the link. From this tool, I got MDAC 2.1 SP2 and MDAC 2.5 SP3..

"Whidbey"..."Orcas"...Roadmap
This signature was created by "Code Project Quoter".

FULL LEFT/RIGHT JOIN

devvvy3-Nov-03 12:53

devvvy

3-Nov-03 12:53

What's the difference between:

FULL LEFT/RIGHT JOIN and just LEFT/RIGHT JOIN?

What does the word "FULL" tells the query analyzer? THanks.

norm

Re: FULL LEFT/RIGHT JOIN

Jeff Varszegi3-Nov-03 14:28

Trouble Running Sql Script from Code

3-Nov-03 14:28

I don't think there is such a thing as a FULL LEFT or FULL RIGHT join. There is, however, such a thing as a FULL OUTER JOIN-- is that what you're talking about? There are also LEFT OUTER JOIN and RIGHT OUTER JOIN. I can see how you could've become confused. Here's the skinny:

A LEFT OUTER JOIN is the same as a LEFT JOIN.
A RIGHT OUTER JOIN is the same as a RIGHT JOIN.
A FULL OUTER JOIN is the same as a FULL JOIN.

A LEFT JOIN is all the rows from the left part of a join, with NULL values being permitted on the right if there's no match.

A RIGHT JOIN is all the rows from the right part of a join, with NULL values being permitted on the left if there's no match.

A FULL JOIN is the union of a LEFT JOIN and a RIGHT JOIN; you're guaranteed that all possible rows from both the left and right will be present, with NULL values filling in the blanks on the other side as necessary.

Does this help?

Regards,

Jeff Varszegi

Re: FULL LEFT/RIGHT JOIN

devvvy3-Nov-03 14:30

devvvy

3-Nov-03 14:30

Thankyou master!

norm

perlmunger3-Nov-03 11:42

3-Nov-03 11:42

I have created a deployment application that has the user provide db credentials. It then creates the database and then runs a .sql script to create all of the db objects. The script runs fine in Query Analyzer, but won't work in my deployment project. I get the following error message:

'CREATE VIEW' must be the first statement in a query batch.
'CREATE VIEW' must be the first statement in a query batch.
'CREATE VIEW' must be the first statement in a query batch.
'CREATE VIEW' must be the first statement in a query batch.
'CREATE VIEW' must be the first statement in a query batch.

What does this mean and why do I only get it in my deployment app and not in query analyzer?

Thanks.

-Matt

------------------------------------------

The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall

Jeff Varszegi3-Nov-03 14:54

3-Nov-03 14:54

That's a great signature quote.

Try putting GO on its own line after each CREATE VIEW. Did this solve your problem? How is this script being run, anyway?

Thank you.

Jeff Varszegi

perlmunger4-Nov-03 3:58

4-Nov-03 3:58

From what I understand, "GO" isn't part of the T-SQL language. Frankly, if that's the case, I'm not sure what in the world it's there for except as a delimiter (maybe?). I've tried to run the script with GO(s) in and I get a syntax error at the first one it comes to. I then just ignore all of the GO(s) when I'm reading the script and I get the error I mentioned in my first message.

Thanks for your help.

-Matt

------------------------------------------

The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall

Jeff Varszegi4-Nov-03 4:03

4-Nov-03 4:03

Well, part of the problem is that I don't know what you mean by "running a script". I mean, are you using osql or something? If not, you will probably have to separate each one of the CREATE statements, etc. into a separate call; each one has to be in a separate "batch". In some contexts, you separate batches with GO; in others, you just execute them separately. Are you just executing the .sql file using a shell command or something? Try separating each CREATE statement into a separate file-- what happens?

Thank you.

Jeff Varszegi

perlmunger4-Nov-03 4:54