Hot answers tagged

8

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


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

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.


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


5

The cause of this problem most likely is the primary key of the table in DB2. Access can not handle some data types as primary keys well. Notoriously bad for PKs are all floating point numeric types. They may be rounded differently on the server and in Access. If Access uses a floating point value to query a single record from the server, there will be no ...


4

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.


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

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


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

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 should be able to get away with this by creating views in SQL Server that are effectively wrappers for tables in the linked server. You will of course have to deal with the extra layer of security configuration for the linked server, thus ensuring that the Access users are connecting to the external data with the proper credentials (whether it be ...


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

In the cCURE 800 Install guide there is a requsite set of software and patches for the OS that need be installed. Including Office XP Web Components and Some patches ( Listed Below) "for Journal Replay and other reports to work" install =Microsoft Office XP Web Components= - [NOTE : f you have office 2003, Access 2003, or Frontpage 2003 installed on your ...


3

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.


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

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

I haven't done a lot of PS so not sure what the error is really saying but... it looks like you are trying to fill a local table with the output from the select *? If that is true, I am wondering if PS created a temp table with a number/int column type for rownum but the rownum value in row 840 or 841 is something other than a number/int. Or something ...


2

Based on the current release driver release compatibility matrix, found here, if you want to support Netezza NPS v6.0, v6.1, and v7.2 with the same ODBC driver, you should pick the most recent patch of the 7.1 ODBC driver. If you don't need to have the same driver support all three server levels, then you would likely be better off going with the most ...


2

The Instant Client is easier to install and more lightweight. It doesn't even need to be installed, you can just unzip it and use it. You don't need an Oracle Home either. On the other hand, it does not support deprecated features like Oracle Names. If you don't need any feature the instant client omits, I suggest you use it. It looks like you are covered ...


2

The public link below gives an overview of the four installation type options for the 11gR2 client: http://docs.oracle.com/cd/E11882_01/install.112/e24322/install_overview.htm#LACLI1245 Instant Client: Enables you to install only the shared libraries required by Oracle Call Interface (OCI), Oracle C++ Call Interface (OCCI), Pro*C, or Java database ...


2

Like you already found out, there is no (predefined) order in a result set unless you specify it in the ORDER BY clause. Your only option would be, to rename the table and create a view on it. RENAME TABLE your_table TO another_table_name; CREATE VIEW your_table AS SELECT * FROM another_table_name ORDER BY id DESC; Test of course heavily if you run into ...


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

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

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

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

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

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



Only top voted, non community-wiki answers of a minimum length are eligible