One Table Link Breaks Every Time
Aug 16, 2005
Using an MS A2K front end running on WinXP and ODBC connections to SQL
Server 7 on Win 2K server; there are about 10 users who access the
front end via Terminal Server and use the same front end; there are
about another 10 users who run a copy of the db on their local machine.
SQL server has 2 databases, with approx. 20 tables between them (one
is for processing ckts from a remote sql server for local use, the
other is a site database).
There is one user that always has to refresh the same single table each
time a release of the db is copied to his local machine. Once the
table is refreshed in link manager, the front end is good to go...until
the the next release is made (about every 2 months--for changes in
business rules). We have gone and checked his WINS and DNS settings
and at this point, the only difference between other users and this one
who can't get the table normally, is that he is using a different brand
machine (he uses a toshiba satellite, all others use a ibm thinkpad).
Since purchasing a new laptop for him is out of the question, does
anyone have a suggestion for correcting this problem?
TIA for the help,
EricO.
View 4 Replies
ADVERTISEMENT
Nov 5, 2007
Hi, we run an app that is linked across a SQL Server 2000 (Server 2003), IIS (Server 2003) and a notes server. Everything has been running sweet till the daylight saving kicked in a week last sunday, the clocks re synced on monday (SQL was 8 mins out) and connection within the app was lost and needed IIS and SQL services restarted. Eventually all was up and running till today, IIS resynced Monday at 3AM but once SQL resynced at 9:45 the app stoped and only restarted after SQL services got restarted and then IIS services (IIS Admin and its 2 associated services) got restarted. Time will resync on both servers at + - 3am next Monday and i am expecting the whole thing to go the same way..... Does anyone know why W32Time would cause SQL network connectivity to 'blip'? This time, the times on both servers matched before and after.
Have checked Event logs on both and nothing on or after the time.
The server syncs fine, the other databases on the server are unaffected.
Thank you
Graeme
View 1 Replies
View Related
Feb 3, 2006
When trying to link to an SQL table in Access 2003, the software appears to be malfunctioning.
The sequence of events is File - Get External Data - Link Tables - Files of Type: ODBC Databases().
The Problem: On two of my computers, the select data source window does not pop up, preventing me from linking to any ODBC data source.
Observations: This function has worked normally in the recent past and works on other computers running Access 2003. One difference between the computers working and non-working computers is Norton Antivirus 2006 (recent upgrade).
Has anyone experienced anything like this? What's going on?
View 8 Replies
View Related
Sep 14, 2007
Greetings all:
I'm having a bit of an issue with a report that I've been working on for the last several days. The report is set up to use a table to print out information grouped by customer, with each customer's information showing up on a new page. This is all fine and well, but it seems that the table is generating page breaks after each row; in other words, since the table has twelve rows (10 group header rows, one details row, and one group footer row), twelve pages get printed, with the data from each row showing up at the top of the page, and nothing else.
The odd thing seems to be this: when I turn off page breaks (initally set to occur at the end of each group), it continues to
insert these breaks, as long as the details row forces the group footer onto another page. This doesn't happen every time the report is generated. Short of getting into the XML that makes up the report definition, I've tried every trick I can think of to get this to stop.
Any suggestions?
Edit: I should also mention that the report previews just fine, but only spits out the 12+ pages when printed or exported.
View 16 Replies
View Related
Aug 9, 2007
Hi,
I'm having problems successfully executing bcp_init through a function pointer retrieved at run time from sqlncli.dll.
The call works when the library is statically linked.
Can someone please point out the problem? The commented out code in green executes successfully when the library is statically linked. Code marked in red fails.
SQLGetDiagRecW does not provide any useful information.
Any help is appreciated.
Regards,
Venkat
typedef SQLRETURN (__stdcall *SQLALLOCHANDLEPROC)
(
SQLSMALLINT HandleType,
SQLHANDLE InputHandle,
SQLHANDLE OutputHandle
);
/// typedef native function SQLSetEnvAttr
typedef SQLRETURN (__stdcall *SQLSETENVATTRPROC)
(
SQLHANDLE EnvironmentHandle,
SQLINTEGER Attribute,
SQLPOINTER Value,
SQLINTEGER StringLength
);
/// typedef native function SQLSetConnectAttrW
typedef SQLRETURN ( __stdcall * SQLSETCONNECTATTRPROCW )
(
SQLHANDLE ConnectionHandle,
SQLINTEGER Attribute,
SQLPOINTER ValuePtr,
SQLINTEGER StringLength
);
typedef SQLRETURN ( __stdcall *SQLCONNECTW )
(
SQLHANDLE ConnectionHandle,
SQLWCHAR * ServerName,
SQLSMALLINT NameLength1,
SQLWCHAR * UserName,
SQLSMALLINT NameLength2,
SQLWCHAR * Authentication,
SQLSMALLINT NameLength3
);
typedef RETCODE ( __stdcall *BCPINIT )
(
SQLHANDLE ConnectionHandle,
const WCHAR* TableName,
const WCHAR* DataFileNae,
const WCHAR* ErrorFileName,
SQLINTEGER Direction
);
main()
{
// Load library and initialize function pointers.
HMODULE hModule = ::LoadLibrary(_T("SQLNCLI.dll"));
SQLALLOCHANDLEPROC fnAlloc = (SQLALLOCHANDLEPROC)::GetProcAddress( hModule, _T("SQLAllocHandle") );
SQLSETENVATTRPROC fnEnvAttSet = (SQLSETENVATTRPROC)::GetProcAddress( hModule, _T("SQLSetEnvAttr") );
SQLSETCONNECTATTRPROCW fnConnectAttSet = (SQLSETCONNECTATTRPROCW)::GetProcAddress( hModule, _T("SQLSetConnectAttrW") );;
SQLCONNECTW fnConnectProc = (SQLCONNECTW)::GetProcAddress( hModule, _T("SQLConnectW") );
BCPINIT fnBCPInit = (BCPINIT)::GetProcAddress( hModule, _T("bcp_initW") );
// Allocate ODBC handles.
SQLHENV m_SQLEnvironment;
SQLHDBC m_SQLConnection;
SQLHSTMT m_SQLStatement;
SQLRETURN iReturn;
iReturn = fnAlloc( SQL_HANDLE_ENV,SQL_NULL_HANDLE,&m_SQLEnvironment);
//iReturn = SQLAllocHandle( SQL_HANDLE_ENV,SQL_NULL_HANDLE,&m_SQLEnvironment);
iReturn = fnEnvAttSet(m_SQLEnvironment,SQL_ATTR_ODBC_VERSION,(SQLPOINTER) SQL_OV_ODBC3,0);
//iReturn = SQLSetEnvAttr(m_SQLEnvironment,SQL_ATTR_ODBC_VERSION,(SQLPOINTER) SQL_OV_ODBC3,0);
iReturn = fnAlloc(SQL_HANDLE_DBC,m_SQLEnvironment,&m_SQLConnection);
//iReturn = SQLAllocHandle(SQL_HANDLE_DBC,m_SQLEnvironment,&m_SQLConnection);
// Set up bulk copy.
int nType = SQL_COPT_SS_BCP;
SQLPOINTER pvAttribute = (SQLPOINTER)SQL_BCP_ON;
SQLINTEGER nValue = SQL_IS_INTEGER;
iReturn = fnConnectAttSet(m_SQLConnection, nType, pvAttribute, nValue );
//iReturn = SQLSetConnectAttrW(m_SQLConnection, nType, pvAttribute, nValue );
// Connect.
BSTR ODBCSource = :: SysAllocString(L"SQL2005");
BSTR UserName = :: SysAllocString(L"sa");
BSTR Password = :: SysAllocString(L"xxx");
iReturn = fnConnectProc(m_SQLConnection, ODBCSource,7,UserName,2,Password,3);
//iReturn = SQLConnectW(m_SQLConnection, ODBCSource,7,UserName,2,Password,3);
// Init bulk copy.
BSTR sTableName = :: SysAllocString(L"test");
BSTR sErrorFile = :: SysAllocString(L"c:\test4.txt");
int nDirection = DB_IN;
RETCODE nRet = fnBCPInit( m_SQLConnection, sTableName, NULL, sErrorFile, nDirection ); // nRet = 0
//RETCODE nRet = bcp_initW( m_SQLConnection, sTableName, NULL, sErrorFile, nDirection ); // nRet = 1
}
View 10 Replies
View Related
Oct 15, 2007
Hello,
Since SQL 2005 Express doesn't appear to let you paste multiple lines of data into a cell -- a task as "basic" as the Commodore 64 -- I'm forced to enter the data for 2 records directly. I'm trying to insert line breaks, which SQL 2000 allowed you to do by simply pressing the Enter key. However, 2005 just moves you to another record. How do I do this extremely simple task?
Many thanks for a speedy reply!
View 6 Replies
View Related
Sep 25, 2006
Hi,
I have some simple views that bind to an Access forms for use in the detail side of Master/Detail forms.
The views are of the style:
SELECT *.OrderDetails, Products.TotalUnitsInStock AS UsefulReferenceInfo
FROM OrderDetails LEFT OUTER JOIN Products ON ..........
Using a database in SQL 2000, opening the view to directly insert values into the OrderDetails columns works perfectly, and Access correctly determines that there is an IDENTITY column that is the Primary Key. Insert works perfectly, form work perfectly.
Using the same SQL script to build a database in SQL 2005 the view doesn't detect that the column is an IDENTITY column so any attempt to insert data fails with the error "Invalid input parameter values. Check the status values for detail." Insert fails, form also doesn't work.
I have tested this in Access 2002, 2003 and 2007 B2TR and all give the same results so it seems to be a breaking change in SQL 2005.
It is easy to see when there is going to be trouble because the tell-tale '(Auto Number)' or more recently '(New)' text is missing from the appropriate identity column.
This is fairly inconvenient and breaks quite a lot of my Master/detail forms that use this design pattern.
Can anybody from the SQL team help shed some light on this for my.
Cheers Simon
View 11 Replies
View Related
Sep 1, 2015
Below the Error Log Reported in SQL Agent (64 bit SQLServer 2008R2 Enterprise Edition Service pack 2 ,32Core CPU Numa Node and 64GB Ram
[298] SQLServer Error: 10054, TCP Provider: An existing connection was forcibly closed by the remote host. [SQLSTATE 08S01]
[298] SQLServer Error: 10054, Communication link failure [SQLSTATE 08S01]
[165] ODBC Error: 0, A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections.
For more information see SQL Server Books Online. [SQLSTATE 08001]
[382] Logon to server '(local)' failed (ConnUpdateStartExecutionDate)
View 2 Replies
View Related
Mar 7, 2007
I have several nested sub-total groups that are all coded to "page break at end". I don't know how to tell RS to suppress the page break if one sub-total immediately follows another. The initial HTML output seems to be smart enough to do this suppression on its own, but when I export to PDF, all the page breaks appear. This causes several pages at the end of the report with one line per page. Perhaps, if I could explicitly suppress these page breaks, the PDF would render properly. How can I do this?
View 1 Replies
View Related
Jun 18, 2007
Hi, all experts here,
Thanks for your kind attention.
I want to use time series algorithm to mine data from my case table and nested table. Case table is Date table, while nested table is the fact table. E.g, I want to predict the monthly sales amount for different region (I have region table related to the fact table), how can I achieve this?
Thanks a lot and I hope it is clear for your help and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
View 6 Replies
View Related
Jun 4, 2007
Hi, all experts here,
Thank you very much for your kind attention.
I am confused on key column of case table and key time column of nested table by using Time Series algorithm.
In my case, the case table structure is as below:
Territory key text (the ID is actually dimrisk_key, in this case, I use the name column binding to combine the Territory column of case table Dimrisks),
While the nested table structure is as below:
Cal_month key time (in this case, actually the ID is dimdate_key, again, I used name column bining property to bind the Cal_month to the ID)
So my question is, as the key column of case table has been set to be Territory, as a result, does the model training still cover all the cases (rows) based on the ID of the table?
Also, in the nested table, as the key time column has been set to Cal_month rather than Dimdate_key of the nested table, as a result, would the single series based on the cal_month?
Hope it is clear for your advices and help.
And I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
View 1 Replies
View Related
Feb 20, 2008
I have created an Access2003 project (existing data) that links to external data. First I connected to a SQL Server 2000 database. Success. Then I tried to set up a Transact SQL data connection to a legacy MDW-secured Access97 database. (A third-party VB6 application goes against it, and we don't have the source code, so we cannot upgrade it.)
The Transact SQL link tests OK but I cannot select any of the tables or queries from the list presented. However, with the same credentials, I can use these same objects in Excel 2003.
When setting up the link in Access2003, I specify JET 4.0 OLE DB Provider, I enter the MDW file on the All tab, a username and a password on the Connection tab where I browse to the MDB file, and specify Shared Deny None on the Advanced tab. When I test the connection, it tests OK ("Test connection succeeded"). Yet on the "Select the Database and Table/Cube which contains the data you want" dialog, "(Default)" appears in the grayed-out dropdown. Then, beneath that dropdown, there is a grid with Name and Description columns. The grid contains query names but the grid is not enabled. The list of queries is this table is grayed out. Neither of the scrollbars works.
BUT... if I use the SAME username and password in Excel2003, and specify the same MDW, there is no problem working with these same database objects in the legacy Access97 database. WHAT IS DIFFERENT ABOUT THE WIZARD IN EXCEL THAT ALLOWS IT TO SUCCEED AND THE WIZARD IN ACCESS THAT CAUSES IT TO FAIL HERE? In Excel, the list of available providers says Microsoft Access Driver, not JET 4.0 OLE DB Provider.
Thanks
TR
View 1 Replies
View Related
Jun 21, 2006
There is a way to create a link from a SQL Server database to a table located on a MSAccess database? I mean like creating links from MSAccess to other databases. The requested table is updated many times/day, and I dont want to import the table each time an update happens.
Thanks,
Richard
View 2 Replies
View Related
Feb 14, 2008
Fit an intere table in same page without page break for save the excel export.
My table has a Group for order my dates.
I need to have the intere table in the same page, i don't care about blank space at the end of the page.
I can't use the page break beacuse i need an excel export in a unique sheet..
I have tested.. every page break..you'll have a different sheet in your excel export
I need something like this
page 1
Zone
1
2
3
4
5
6
7
7
8
9
..
page 2
Zone
1
2
3
4
5
6
7
7
8
9
..
but an unique sheet in the excel export
View 4 Replies
View Related
Aug 31, 2006
Hi, is correct add a relation to Asp.net_User to one another table(example orders)With column relactioned the table users with the table Orders , UserId , UserName , I am a little confused I need help!. Thank you
View 1 Replies
View Related
Apr 28, 2000
I have used the Microsoft code below to create a link to SQL server from access. I am using DAO not ADODB. Someone said that I should use dblib instead of ODBC in the connection string below in order to get the connection to work.
Does anyone know the correct syntax for OLE DB connection string and can it be used with DAO? I am getting an error "ISAM path not found"?? Seems I have wrong connection string syntax?? Please advise.
Thanks again for your help.
-------------------------------------------------------------------------
Sub ClientServerX3()
Dim dbsCurrent as Database
Dim tdRoy as TableDef
Set dbsCurrent = CurrentDb
Set tdfRoy = dbsCurrent.CreateTableDef("Roy")
tdfRoy.Connect = "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers"
***************IS THERE ANYWAY TO USE AN OLEDB (OR OTHER DSN-LESS)
***************CONNECTION HERE? How??
tdfRoy.SourceTableName = "Royalties"
dbsCurrent.TableDefs.Append tdfRoy
End Sub
View 1 Replies
View Related
Feb 15, 2000
Hi,
I'm looking for a possible way to access tables from SAP but in another database. This is not possible using ABAP from SAP. Therefore I wonder if it's possible to define some kind of virtual table in a database which in fact refers to a table in another MSSQL database...
Help and/or tips highly appreciated,
Paul
View 1 Replies
View Related
Apr 20, 2005
Tell me please how can I make a foreing key constraint for a table using from another not all primary key or not all data. For example, I need to link a table "subject" with primary key "dscp_num, depart" using depart as a foreing key. But there is no table with such primary key, there is only table "codif_values" where selecting records by condition "codif_num = 1" gives the relation with necesary primary key to link. Also I need to link "subject" table as a primary key table with the table "ses_curr". But this table doesn't include "depart" field. It includes field "reg_num" that is a primary key in table "students". This table include field "stgroup" as a foreing key for table "groups". The last table includes "speciality" field that is a foreing key for table "specialities". And only this table includes "depart" field. Tell me please how I can make a foreing key constraint for table "ses_curr" by table "subject".
View 3 Replies
View Related
May 11, 2004
Hello All,
I have a question on linking tables with SQL Server. I have SQL Server 2000 with database A on machine A. I have SQL Server 2000 with database B on machine B. I want to link tables from the database on machine A into a database on machine B. I am not quite sure how to do this - I believe this can be done as I have seen it before. Any information and or resources to direct me to would be greatly appreciated.
Thanks
View 10 Replies
View Related
Jun 1, 2004
Hi all,
We have lookup tables we share on 6 database servers. We keep copying these very large tables to every database we use on each server, obviously a great waste of space. We don't mind copying these tables onto each of the servers but we want to be able to link to these tables from each database from within our servers. How is this done? Thanks in advance.
ddave
View 7 Replies
View Related
Jan 16, 2007
I need to append data to the existing Table1 from a .txt file stored in a link e.g. "http://xx00xx0123.abcdef.net/ABC_REPORTS/DATA/Table1.csv"
(Columns in both tables are identical)
This .csv contains a rolling 7 days of stats. which means if it is added every Morning 6 of those Days will have been added before and must be Deleted.
I would like to schedule an automatic procedure to create a temp table in the server every day and a script removing duplicates.
I have a few questions:
*) Can I shedule from the Enterprise Console to read/create table from the above link to do this?
**) I heard I need an SQL agent. If so why and what is it?
***) Is it better to append data and then script removing Duplicates, or is it better to import into a temp table run comparison between the 2, Delete from Temp what is Common and then append whats left of the Temp to the Table1?
****) Please could someone paste a sample of CREATE TABLE from a http link like the one above?
Thanks for your help,
Gezza
View 4 Replies
View Related
Apr 2, 2008
Hi,How can I run a query to figure out what tables and correspondingcolumns link to any column in my table T?Thanks, - Dave
View 1 Replies
View Related
Nov 14, 2003
is it possible to link a table with odbc into sql server?
it would be nice to link an MS Access table into sql server where i could use stored procedures to access the MS Access table.
View 1 Replies
View Related
Apr 25, 2000
I have used to following Microsoft supplied code to create a link to
SQL server from access: (Is there a way to use a DSN-less connection at comment below? If not is there a way to create a DSN using code?
-------------------------------------------------------------------------
Sub ClientServerX3()
Dim dbsCurrent as Database
Dim tdRoy as TableDef
Set dbsCurrent = CurrentDb
Set tdfRoy = dbsCurrent.CreateTableDef("Roy")
tdfRoy.Connect = "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers"
***************IS THERE ANYWAY TO USE AN OLEDB (OR OTHER DSN-LESS)
***************CONNECTION HERE? How??
tdfRoy.SourceTableName = "Royalties"
dbsCurrent.TableDefs.Append tdfRoy
End Sub
Thanks, I really appreciate the help
Sam
View 1 Replies
View Related
Feb 26, 2007
I'm trying to find how to link to an Access table from within SQL Server. I know I have seen it once, but can not remember where I saw it. I'm using SQL Server 2005. TIA,
View 5 Replies
View Related
Dec 26, 2014
I need to take a temporary table that has various times stored in a text field (4:30 pm, 11:00 am, 5:30 pm, etc.), convert it to miltary time then cast it as an integer with an update statement kind of like:
Update myTable set MovieTime = REPLACE(CONVERT(CHAR(5),GETDATE(),108), ':', '')
how this can be done while my temp table is in session?
View 2 Replies
View Related
Jan 29, 2006
How can make link to a table stored in an sql express 2005 database in access 2003 ? the classic method doesen't work. i tried to use odbc databases as file type to link, then build a data source using SQL native clint driver, but in the combo for default database i don't have the database created before, only master,msdb, model, tempdb. If i try to use the attach database filename text box i receive an error "the datadase entered is not valid". Any suggestions ?
Thx
View 4 Replies
View Related
Dec 11, 2007
Hi I need to know if it is possible to write on a table from another database and this using a stored procedure. If yes what is the correct syntax to connect to the other database.
View 1 Replies
View Related
Mar 23, 2008
i'm pretty familiar w/ mysql, but relatively new to ms sql. Since i can do a lot more w/ ms sql, I have been trying to do all tasks in sql queries or scripts, but i'm stumped on this one. I have a table that links two things together. t1 has rows id1 and id2. When a row has these two id's, there is a "link" between them. I'm trying to make a command or script that will see if there is only 2 links for any given id.
for example, it would loop through each row, and run this command:
SELECT COUNT(*) AS Expr1
FROM links
WHERE (Id1 = this_rows_id1) OR (Id2 = this_rows_id1)
and again w/ id2
and then i would be interested in any row that only had a count of 2. is this possible?
View 2 Replies
View Related
Jun 10, 2007
I am designing a database schema where several tables have one-to-many relationships to records in other tables. One way to implement it is to create a link table for each pair of tables that have a relationship:
identity
Table1_ID
Table2_ID
1
12
9
2
12
15
3
18
42With the SQL 2005 support for the XML data type, there is the possibility of storing the IDs in an XML column. The XML stored with a record (equivalent to record 12 from the above example) might look like this:
<Links>
<Table2_LinkType>
<ID>9</ID>
<ID>15</ID>
</Table2_LinkType>
<Table8_LinkType>
. . .
</Table8_LinkType>
</Links>The XML column method has the advantage of not requiring that a separate table be created but does not enforce referential integrity. The link table method has the advantage of allowing constraints to enforce referential integrity but has the disadvantage of requiring the creation of a separate table for each pair of tables having a relationship and joining to an additional table has performance implications. Implementing standard Add, List and Delete operations for the link table method is straightforward. As a test and to familiarize myself with the new XML features, I created Set, List and Delete stored procedures for the XML method. Both methods will work.
In deciding which method to go with are there any other issues I should be considering besides database integrity, complexity and possible performance issues?
From the standpoint of best practices and coding standards is one method preferred over the other?
Here is some additional information. The data set I will be working with has table sizes numbering in the hundreds of thousands. Any given record will only be linked to at most a half-dozen records in any other table.
I searched on-line for information and I was able to find plenty of good articles discussing how to use the new XML data type in SQL. However, I was not able to find any information on when not to use the XML data type over equivalent joined-table methods.
View 1 Replies
View Related
Nov 30, 2005
Hi all,
This is a bit of a general question regarding SQL Server link tables that i hope someone can find the time to answer.
I am looking for the best practice. If I have 3 tables
1) tblCompetition
2) tblTeams
3) tblTeamsInCompetition (this is the link table)
I know the link table should have a related CompetitionID and TeamID,
but should the link table also have a primary key that is an identity
autoincrement by 1 just as you would with the other two tables. In
Access I have never done this, however in SQL Server I have read that
you should do this (but now I can't find the documentation again which
prompts me to ask the question here).
Thanks for taking the time to answer this question.
View 7 Replies
View Related
Sep 3, 2013
I have one table Emp_MAster with two column ID-Sup_Id. I need to create a table where i can store data link this
Id-Hreid
Only difference is that I need to store data in an hierarchy view so Emp 1 is reporting to Emp2 and Emp2 is reportign to Emp3 so in new table I should get
Emp3-Emp2
Emp3-Emp1
Emp2-Emp1
View 2 Replies
View Related
May 20, 2008
I'm a long-time Access developer who is looking to migrate his approach to SQL Server CE 3.5 instead of having all these MDBs. One thing that I do like is using Access to surf through databases; it's a lightweight way to see the data without having to fire up the overhead of Visual Studio etc. Plus there's the QBE grid for easy query design for those of us whose tool-less SQL coding is a bit rusty...
Anyway, I was wondering whether it was possible to "attach" to a SSCE database so you can see it in Access. I've tried but haven't found a way of doing it. Any thoughts? Or is this an intentional lack of capability so we have to use a more robust tool such as Management Studio or Visual Studio?
Thanks in advance!
View 1 Replies
View Related
