Hot answers tagged odbc
6
It sounds like you have a "One True Lookup Table" (OTLT) anti-pattern and you are mixing entities in this table. You've found why it isn't a good idea:
can't have filtered foriegn keys
can't FK to constants
can't have multiple parents
Your sample code above is confusing (you have multiple parents for the same Code column) so I'll give you what I ...
5
You have a working query, but you are selecting:
FROM CF30, EC01, OC02, OM01U1, RS2101F
With no explicit joins and only one implicit join:
WHERE OM01U1.OM01015 = RS2101F.OUTNUM
This is going to lead to problems. Can you find which fields (columns) match to which in each table? You could then say:
FROM OM01U1
INNER JOIN RS2101F
ON OM01U1.OM01015 = ...
4
It looks like the linked MSSQL table does not have a unique index on in. MS Access uses the MS Jet DB engine that is designed around a keyset model. Actions like inserts, updates, etc, uses this keyset. This may be missing from your linked MSSQL table. Click here for more detailed info.
3
You can use a windowing function to partition the data. If I knew your data better I'd rewrite it without a subselect, but the wall of text is making my head hurt ;)
Here you go:
SELECT * FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY OM01U1.OM01015 ORDER BY EC01_LASMTCDAT DESC) as r,
OM01U1.OM01041 as Loc,
OM01U1.OM01015 AS Outlet,
-- SNIP --
-- SNIP --
...
3
Encoding? Do you mean collation? The collation only affects things like equality tests, sort order, etc. They don't affect the allowed encodings for column values.
If you want to store unicode text, you should make sure your columns are among
NCHAR
NVARCHAR
NTEXT (2-byte characters)
rather than
CHAR
VARCHAR
TEXT (1-byte characters).
3
The only way to get complete control over the query for ODBC is to create them as pass-through queries.
In that mode, Access will not touch them and the SQL will be passed verbatim to the server (so your queries will need to be written in that server's particular SQL syntax or you'll get errors).
In ODBC mode, Access will decompose queries that are bound ...
2
I have seen this before. According to the Sybase ASE Documentation:
Adaptive Server Enterprise implements dynamic SQL using temporary stored procedures. A temporary stored procedure is created when a SQL statement is prepared, and destroyed when that prepared statement is deallocated...[a]s a consequence of this implementation, an application accessing ...
2
SQL Server always returns NULLs in this case. I know of no way to globally (through server settings or connection strings) cause it to return empty strings or zeroes instead of NULLs. And I do not think it is a database library issue--using different drivers won't change a thing. This is just the way SQL Server behaves.
If you want to convert NULLs to ...
2
ODBC is a client tool and does not affect the way that Postgresql verifies access. I don't know if its any different for a postgresql server on windows, but in linux, there is a file called pg_hba.conf. This file is usually located in the data directory of the server install. In Linux, you typically find it in /var/lib/pgsql/data/.
This file allows you ...
2
You are joining 5 files, but have only one condition that limits one of those joins.
WHERE OM01U1.OM01015 = RS2101F.OUTNUM
This condition should go into your join specs
FROM OM01U1
JOIN RS2101F ON OM01U1.OM01015 = RS2101F.OUTNUM
JOIN CF30 ON _____ = ______
JOIN EC01 ON _____ = ______
JOIN OC02 ON _____ = ______
If some of these ...
2
Good tool for simulating many threads doing INSERTS UPDATES SELECTS and DELETES on an ODBC database?
The IT shop I work for "bleeds blue" (ie, sticks mostly to IBM tooling), so the performance software we use is IBM's Rational Performance Tester (RPT). IBM's stuff isn't (usually) free or cheap, but RPT has helped us to populate our databases through our applications (mostly because the relationships among the tables is too complex to do nicely via script).
...
2
For most applications hitting SQL Server 2008 R2, you would be fine going either way, I think.
OLEDB support is being removed after SQL Server 2012 but until it is, you'd likely be fine either way based on your standards and your needs. That said, I'd look to future supportability and consider going ODBC with the Native Client.
I would say a more important ...
2
Internally, Excel stores dates as an integer value. I've run into this before and solved it both in SSIS and straight TSQL
http://stackoverflow.com/questions/9085928/reading-in-date-column-if-first-row-isnt-a-date-with-ssis-excel-data-source/9086831#9086831
In your case, you might need to make a computed column that actually applies the formula, assuming ...
2
You have created a linked server, not a linked database, so there is no list of databases to select from. You need to either use a "four-part name" or use the OPENQUERY() function.
Four-part names describe an object on another server. Usually a four-part name would look like "servername.databasename.schemaname.tablename", but for Oracle you apparently need ...
2
To help you troubleshoot this, here is a very important piece of information to keep in mind:
Locks, in MySQL, whether they are on rows, tables, advisory/named locks, and even the global FLUSH TABLES WITH READ LOCK are held by continued existence of the specific connection thread that obtained the lock. When I say "connection thread," in this context, I ...
1
I maintain an Access 2003 application that connects to a pair of SQL Server 2005 servers setup with mirroring. I use the following connection string which allows the application to connect to the mirror after an application restart:
Dim ws As Workspace
Dim db As Database
Dim a As Long
Set ws = DBEngine.CreateWorkspace("MyODBCWorkspace", "admin", "", ...
1
I finally could fix my problem by adding a field named TIMESTAMP of type timestamp and default value CURRENT_TIMESTAMP and attribute = on update CURRENT_TIMESTAMP.
Then i updated all records and set TIMESTAMP=NOW().
For now i did not get any error again.
1
When you are overwhelmed with a long complex query, involving so many pieces, it is often a good strategy to break it down into smaller parts, get them working, then build it back up, in small stages. Focus first on a "mind-sized bite". Don't bite off more than you can chew in a single mouthful. Start simple, and add things back in layers.
You probably ...
1
Following on from chat in "The Heap"...
Visio can reverse engineer a database. However, I assume thus needs to read metadata or system tables to get everything (keys, DRI, datatypes the works).
This old MSDN article may help "Migrating Btrieve Applications to MS SQL Server 7.0" too
1
The latest full client will work fine with 10g.
I have experienced similar problems using anything other than the full client, even though according to Oracle the others should work.
Just make sure that you completely uninstall all elements of the previous installs as having multiple clients/versions can create another set of problems.
1
Thanks to Jimbo and Shark's comments I was able to fix my problem by adjusting the permission for the tables I wanted to see. I did not have SELECT permission on the tables so I was not able to see them. Once I added the SELECT permission to the tables I was then able to see my data perfectly.
1
You can ask the Database Administrator for the environment what your SQL Server Login details are for using SQL Server Authentication.
Alternatively, you could connect to the SQL Server Instance via RDP and Windows Authentication and then create a new SQL Server Login for you to use remotely.
To confirm, you cannot use Windows authentication remotely (from ...
1
To use an Excel file as a source you need to define a named range containing the cells with data (that's Step 1 of the instruction you referenced in the question).
All the named ranges found in the file will be shown in the Select tables pane. After you select a table (i.e. a range) and click OK, a new source will be created.
You can also consider ...
1
Not sure why you think you need to change this at the driver level. Can't you just change your query to the following, which should work fine on both SQL Server and DB2:
SELECT OrderID, COALESCE(CustomerName, '') AS CustomerName
FROM OrderTable
LEFT JOIN CustomerTable
ON OrderCustomerID = CustomerID
(Though you may want to properly qualify your ...
1
Recently some users have been connecting to this database via ODBC and
manipulating data via Microsoft Access. This has led to a number of
accidental deletions of records and all sorts of troubles, so we wish
to stop this from happening.
Tell the users to stop doing this.
If they do not comply, change their accounts to read-only. (using proper ...
Only top voted, non community-wiki answers of a minimum length are eligible
