Tag Info

Hot answers tagged

7

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 = ...


5

If the SQL server table is modified will it be automatically reflected in the [linked] table and when? Yes, on next refresh or requery. If I edit the linked Access table will it be reflected in the SQL server table? Yes; as before, these updates will be visible on the other end on next refresh or requery. Trusted Connection only controls how the connection ...


4

It looks like the linked SQL Server 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 SQL Server table. Click here for more detailed info.


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 ...


3

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", "", ...


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

Create a user in SQL Server with only the perms you want them to have. Set up the user ODBC string to use this credential Profit :)


3

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 ...


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

You don't have to use Access as a mediator. You can look into SSIS or import utility to import data from .TPS to SQL. You have to select Flat file data source. At the end of the import utility, you will have the option to save the package and then schedule it using SQL Agent job. Refer to : How to Migrate Data From Top Speed Database Files Also, look ...


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

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.


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 ...


2

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

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

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

My answer... From my comments: The issue was that the server was set to "Windows Authentication Mode" only. To fix this I Right click the server - > Properties Click "Security" in the left side of the "Server Properties" dialog Changed server Authentication to "SQL Server and Windows Authentication mode" Clicked "OK" Restarted Associated services. At ...


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

I found the following in the source code: This is version 07.05.0001. For those that don't know or don't recall how the versioning works, the major and minor number track the latest release of PostgreSQL that the driver is expected to work with. The revision number is a bit more complex. The first two digits are the version number, where even ...


2

You should be able to create a Linked Server to almost any ODBC data source. I would suggest trying the Microsoft OLE DB Provider for ODBC Drivers. While you need to enter something in the Product Name field, I believe you can enter anything, such as "Firebird". For Data Source, enter the name of the ODBC System DSN you've previously created. You'll need ...


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

Your Access form would need to delete the linked tables, and recreate them in order to continue to have access to the remote table. An interruption of the network connection would certainly cause the symptoms you report. Having said that, you likely shouldn't be having network issues. I'd try to find the cause of the issue, as well as building in the ...


2

Based on my experience with ccure 800 in particular, you do need to have MS Excel and all of the supporting MS Office components installed on the same PC as the ccure Administration client. The OCX libraries are required to support the export function.


2

If the parameter to the ODBC prepared statement is defined as decimal(18,0), that's how it's transmitted to the server. The conversion is performed by the ODBC driver. AFAIK the driver is not required by the standard to emit a diagnostic when dropping the fractional part on conversion to an integral type. Presumably the user knows what he's doing. ;-) ...


2

For Access you can use: WHERE obs LIKE '*[#]*' There are three wildcard characters in Access that can be used with LIKE operator: `*` for a string of arbitrary length (equivalent ANSI: `%`) `?` for a single character (equivalent ANSI: `_`) `#` for a single numeric digit `[` for escaping If you want to search for one of those wildcards, you have to ...


2

OCX files are part of the ActiveX control. There's something wrong with the application and you need to call the vendor to get it fixed.


1

Are you installing the 32 bit drivers or the 64 bit drivers on Windows 7? I have to install the 32 bit drivers on all of our Windows 7 machines (even those running 64 bit). After that I have to set up ODBC using: c:\windows\syswow64\odbcad32.exe instead of through the control panel. Your issue may very well be different.


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