Database

Encoding problem using ADO Stream

5-Nov-03 8: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 12:49

andyharman

5-Nov-03 12: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 3:59

Firas Rashid

4-Nov-03 3: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 22:58

Jo Fredrickson

3-Nov-03 22: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 12:35

andyharman

5-Nov-03 12: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 15:59

Jo Fredrickson

5-Nov-03 15:59

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

Joanne

MSADO 2.5

Kant3-Nov-03 16:46

Kant

3-Nov-03 16: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 2:51

Mike Dimmick

5-Nov-03 2: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 6:01

Kant

5-Nov-03 6: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 13:53

devvvy

3-Nov-03 13: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 15:28

Trouble Running Sql Script from Code

3-Nov-03 15: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 15:30

devvvy

3-Nov-03 15:30

Thankyou master!

norm

perlmunger3-Nov-03 12:42

3-Nov-03 12: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 15:54

3-Nov-03 15: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 4:58

4-Nov-03 4: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 5:03

4-Nov-03 5: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 5:54

4-Nov-03 5:54

I see what you are saying. What I am doing is opening a .sql file that I created using the "Generate SQL Script" command from Enterprise Manager. I script all of it to a single file and then read the entire contents into a string in my application. I am then setting the command text of my sql command to that string and running ExecuteNonQuery. How would I separate the create view calls out? Is there any way other than actually parsing the script or puting each create view into its own file?

Thanks.

-Matt

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

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

Jeff Varszegi4-Nov-03 6:38

4-Nov-03 6:38

Well, you could always copy the statements into your source code. I can't think of any other way. It's an interesting situation.

Regards,

Jeff Varszegi

perlmunger4-Nov-03 7:55