Data Access To Large Tables In Sql 2005
Mar 1, 2007
hi all,
i have a large table in sql server 2005 (it has about 6 columns and 10 million records).
i need to work in a linear way on all the records (i know it sounds dumb but i need to work on all records).
now, obviously when trying to work on this table sql server get stuck for timeout or something like that...
i've noticed that a simple function like "select top 100 * from ExportTable" still works.
is there any way to have sql send me the data when it access it so that i'll still be able to proccess it on the same time, i basically work using dataset so that fixing the timeout wont be helpfull since windows probably wont allow me to load this amount of data into memory.
can any1 help?
Z
View 1 Replies
ADVERTISEMENT
Dec 17, 2007
I am on a project to develope an route finding system that search for the optimal route to stick with for users of the system. The current version that i've done and successfully run is using normal database access in MS SQL 2005. I stored nodes information in the database and the application will query them using normal "select" clauses and return a datatable object to the application. The result is rather slow cause by the multiple access to database server to query. The application used 8 second to look for a short route withour cosidering lots of calculation of traffic information that i will use later. Any comments on the architecture or approach to switch my algo to T-SQl?
View 5 Replies
View Related
Jan 31, 2008
hi,
I want to Display the image records of each employee of having 10 images of each.the number of records are more then one billion.......
please provid me the optimized query that can i used in SP and display in Gridview.
View 7 Replies
View Related
Feb 14, 2008
Writing to tables created by regular users on MSSQL2005
I have users creating tables through an application, I gave them ddl_admin, datareader, datawriter. They can create tables but cannot insert/update data (to their own tables), I cannot insert data either using Access or any other application to those tables created by them (under dbo schema) Is there something I am missing with permissions? Thank you very much
View 3 Replies
View Related
Sep 1, 2006
I have an interesting challenge. we are not allowed to allow users direct access to data in our SQL Server. Audit requires us to take the data out of our production server and pass it to the user. my situation is i have a table in SQl with over 100,000 records and growing. i want to pass that to an access data base. i am utilizing DTS and a data transform.
i s there a better way? the package is running slow and even appears to freeze. i see this amount of data as growing as well.
Don S
View 1 Replies
View Related
Jan 24, 2008
I'm new to my company, although not new to SQL 2005 and I found something interesting. I don't have an ERD yet, and so I was asking a co-worker what table some data was in, they told me a table that is NOT in SQL Server 2005's list of tables, views or synonyms.
I thought that was strange, and so I searched over and over again and still I couldn't find it. Then I did a select statement the table that Access thinks exists and SQL Server does not show and to my shock, the select statement pulled in data!
So how did this happen? How can I find the object in SSMS folder listing of tables/views or whatever and what am I overlooking?
Thanks,
Keith
View 4 Replies
View Related
May 10, 2014
In a Library Management database we have these tables
1) Document ( DocNo , Doc_type , permalink,inDate)
2)Title(id, DocNo,Main_Title, Other_Title)
3)Author(id , Author_Name , Author_Family,Type--Like:main author , translator ,....)
4)Publisher(id,DocNo , Name,Publisedate,address)
5)Subject(id,DocNo,Subject)
6)Description(id,DocNo,ISBN,description)--one document may have some ISBN,etc
In document table I have 500,000 records.
I want to search a word in these tables ,for example i want to search 'Computer' ,this word may be in subject or title or description and etc. How can I do this with best performance?
View 3 Replies
View Related
Jul 20, 2005
Hello,Currently we have a database, and it is our desire for it to be ableto store millions of records. The data in the table can be divided upby client, and it stores nothing but about 7 integers.| table || id | clientId | int1 | int2 | int 3 | ... |Right now, our benchmarks indicate a drastic increase in performanceif we divide the data into different tables. For example,table_clientA, table_clientB, table_clientC, despite the fact thetables contain the exact same columns. This however does not seem veryclean or elegant to me, and rather illogical since a database existsas a single file on the harddrive.| table_clientA || id | clientId | int1 | int2 | int 3 | ...| table_clientB || id | clientId | int1 | int2 | int 3 | ...| table_clientC || id | clientId | int1 | int2 | int 3 | ...Is there anyway to duplicate this increase in database performancegained by splitting the table, perhaps by using a certain type ofindex?Thanks,Jeff BrubakerSoftware Developer
View 4 Replies
View Related
Jul 20, 2005
I have a combo box where users select the customer name and can eithergo to the customer's info or open a list of the customer's orders.The RowSource for the combo box was a simple pass-through query:SELECT DISTINCT [Customer ID], [Company Name], [contact name],City,Region FROM Customers ORDER BY Customers.[Company Name];This was working fine until a couple of weeks ago. Now wheneversomeone has the form open, this statement locks the entire Customerstable.I thought a pass-through query was read-only, so how does this do atable lock?I changed the code to an unbound rowsource that asks for input of thefirst few characters first, then uses this SQL statement as therowsource:SELECT [Customer ID], [Company Name], [contact name],City, Region Fromdbo_Customers WHERE [Company Name] like '" & txtInput & "*' ORDER BY[Company Name];This helps, but if someone types only one letter, it could still bepulling a few thousand records and cause a table lock.What is the best way to populate a large combo box? I have too muchdata for the ADODB recordset to use the .AddItem methodI was trying to figure out how to use an ADODB connection, so that Ican make it read-only to eliminate the locking, but I'm striking outon my own.Any ideas would be appreciated.Roy(Using Access 2003 MDB with SQL Server 2000 back end)
View 2 Replies
View Related
Nov 23, 2006
SQL Server 2005 Standard Edition act as publisher and distributor.
All subscribers are SQL Server 2005 Express Edition.
According to 2005 book online, "MSMerge_History table exists in distribution database". Howevey I found this table in Subscriber database which is in SQL Server 2005 Express Edition.
The problem is this table (MSMerge_History) and other two tables (MSMerge_genhistory, MSRepl_errors) are quite large. We want to keep SQL Server 2005 Express database as small as possible so we can put more data into it.
Is there anyway (Manually or automatically) to clean those tables in SQL Server 2005 Express? Please help.
View 3 Replies
View Related
Jul 14, 2007
Hi I've followed a tutorial on how to write and read varbinary(max) data to and from a database. But when i try to read the data i get the error that the data would be truncated, but only when the varbinary(max) is greater then 8kB. I've used a system stored procedure (sp_tableoption) to set the table that holds the data to store data outside rows. To select the data i'm using a stored procedure: SELECT imageData , MIMEType FROM Pictures WHERE (imageTitle = @imageTitle) And then using an .aspx page to Response.Write the data:Using conn As New sql.SqlConnection conn.ConnectionString = ConfigurationManager.ConnectionStrings("myConnectionString").ToString Dim getLogoCommand As New sql.SqlCommand getLogoCommand.CommandType = Data.CommandType.StoredProcedure getLogoCommand.CommandText = "GetPicture" getLogoCommand.Connection = conn Dim imageTitleParameter As New sql.SqlParameter("@imageTitle", Data.SqlDbType.NVarChar, 200) imageTitleParameter.Value = Request("imageTitle") imageTitleParameter.Direction = Data.ParameterDirection.Input getLogoCommand.Parameters.Add(imageTitleParameter) conn.Open() Using logoReader As sql.SqlDataReader = getLogoCommand.ExecuteReader logoReader.Read() If logoReader.HasRows = True Then Response.Clear() Response.ContentType = logoReader("MIMEtype").ToString() Response.BinaryWrite(logoReader("imageData")) End If End Using conn.Close() End Using Can anyone please help me with this?!
View 2 Replies
View Related
Oct 21, 2007
HiI have a VB.net web page which generates a datatable of values (3 columns and on average about 1000-3000 rows).What is the best way to get this data table into an SQL Server? I can create a table on SQL Server no problem but I've found simply looping through the datatable and doing 1000-3000 insert statements is slow (a few seconds). I'd like to make this as streamlined as possible so was wondering is there is a native way to insert all records in a batch via ADO.net or something.Any ideas?ThanksEd
View 1 Replies
View Related
Feb 4, 2006
I'm trying to move my current use of an sql 2000 db to sql 2005.
I need to update a table definition (to change a field to an Identity)
I'm getting a dialog box (in SQL server management studio) on save saying :
'xxxx' table
- Saving Definition Changes to tables with large amounts of data could
take a considerable amount of time. While changes are being
saved, table data will not be accessible.
I press 'Yes' to the dialog box.
After 35 seconds, I get another dialog box saying:
'xxxx' table
- Unable to modify table.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Well, the server is responding and I can query that talbe and other, I
can add/delete rows to other columns. I can modify other
(smaller) tables.
Any ideas where I can change this timeout?
Daniel
View 10 Replies
View Related
Apr 17, 2007
Hello!
I have a application where users are supposed to edit data from a table in a datagrid.
I want to use a dropdownlist to let the user choose a table to edit.
The users are members of different windows-goupes and different users have only access to edit there own tables. Let's say that they own data in different tables.
Is it possible to use windows authentication to find out what tables to show in the dropdownlist?
If it is, how do I get the names of the tables?
Best regards
Per
View 3 Replies
View Related
May 22, 2008
I am trying to add a linked table to my server, it is an access table.
Here is the code i am using but i get an error:
EXEC sp_addlinkedserver
@server = 'AITdb_be2',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'Access',
@datasrc = '\kcapp03deptsCommonEIQAIT DBTestAITdb_be.mdb'
GO
-- Set up login mapping using current user's security context
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'AITdb_be2',
@useself = 'false',
@rmtuser = 'Admin',
@rmtpassword = ''
GO
-- List the tables on the linked server
EXEC sp_tables_ex 'AITdb_be2'
GO
ERROR:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "AITdb_be2" returned message "The Microsoft Jet database engine cannot open the file '\kcapp03deptsCommonEIQAIT DBTestAITdb_be.mdb'. It is already opened exclusively by another user, or you need permission to view its data.".
Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "AITdb_be2".
View 3 Replies
View Related
Feb 1, 2006
Is it possible to import an MS-Access database (or table) into a new SQL Server 2005 Express database? If so, how is it done?
View 1 Replies
View Related
May 10, 2006
Looks like the Office 2003 MS Access has no support for creating a linked server and implement design changes. While you could link to the SQL 2005 tables, which show up as queries in MS Access 2003, any further use of the linked queries are not supported unless the Access 2003 version came out after the SQL 2005 release. What kind of an update is needed, or fix is needed so that Access projects can be developed?
View 6 Replies
View Related
Aug 21, 2006
Hi,
I have an app that creates and attaches tables from SQL Server into an Access mdb using an ODBC link from VB6 using DAO 3.51 on Windows 2000. Recently I upgraded to SQL Server Express edition and tested it. It worked fine against the Express Edition. So, I installed SQL Server 2005 Standard Edition on our Server hoping that they operate identically. How wrong one can be! the same code that worked against Express Edition does not work on Standard Edition.
It is strange that the App works for about 5 seconds which attaches about 38 tables and after that the connection drops and I have a hell of a time to get the rest of the tables linked. The error that comes up is:
Connection failed:
SQLState: '01000'
SQL Server Error: 10060
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 11
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]General network error. Check your network documentation.
The PC that I am using to connect is in a different domain to the Server where the SQL Server database resides. I thought that domains might be the reason. But even joining the PC to the same domain as the server still does not work and behaves the same. So I do not think it is a domain related issue.
I have also looked at the SQL Server and database configurtaions. I cannot see anywhere any specific time out of around 5 seconds. Even the time out through the SQL Server ODBC driver is set to 30 seconds by default as far as I know. So, it looks like there is a time out somewhere but I am absolutely confused as to where this 5 second time out is comming from.
I also thought permissions on the database or tables might be the issue but after looking at the database and table permissions, they all look OK. The user has prettymuch the same access as the SA on the database.
Has anybody came across this problem? If so, I appreciate if someboday can give me a hint as to where I can start.
Thanks,
View 3 Replies
View Related
Dec 3, 2001
I don't know if there is a fundamental problem with what I am trying to do, or am I just having problems setting it up correctly:
I have a SQL server multi-user database. I want my users to connect to this databsase via Access 2000 Data Project. No problem there. The database consists of one main table and several views (based on the office branch that the user works from). For example there is a Chicago view, an Atlanta view, etc. that all extract different records from the same underlying table. I need my users to have FULL ACCESS (select, update, delete) to their respective VIEWS, but they cannot have access to the underlying table. I've tried several configurations and I'm beginning to think that this may not be possible... is that the case?
If it is not possible to grant access to views but not the underlying table, then what are my other options? The objective is to have a multi-user table that each user "owns a piece of" without being able to see the tables or records belonging to their peers. Do I need to setup a table for every office, and somehow link those tables into one main table? How would I avoid duplicate records being entered into the separate tables? Any help would be GREATLY appreciated, as this problem has had me stumped for weeks.
View 2 Replies
View Related
Jul 20, 2005
HI, can i export all tables and data into a ms access file? thanks:)
View 2 Replies
View Related
Sep 11, 2015
I want to knew the sql query to return differences between two tables from two different database.
View 3 Replies
View Related
Apr 21, 2006
Hi,
I've just installed SQL Server 2005 Express Advanced and I haven't found a way to import Microsoft Access 2003 files. Is there a way to do it?
Thanks in advance,
Sergio Oliveira
View 1 Replies
View Related
Jun 27, 2007
Hi,
I am using MS Access 2003 SP2 to maintain some data tables. I use SSIS to transfer them to SQL Server 2005, Enterprise Edition.
When I run the SSIS package from within Visual Studio 2005, the package runs without error.
When I try to run the same SSIS package by double-clicking on it in my File System (which invokes the Execute Package Utility, Version: 1.0) none of the tables get copied. Instead all I receive is a message for each table,
Error: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "Data Conversion 1" (49).
The only data conversion I perform is double-byte characters to single-byte characters.
Bob Bojanic, MSFT, made a few suggestions about this in another thread -- but I have created this new thread to help focus on this specific issue. In particular, he asked if we have installed the complete SSIS support for SQL Server 2005, Enterprise Edition, and my network support and database support staff assure me that such complete SSIS support was installed.
Are others having this problem?
Dan
(I just took a look at some of the transformations in the Data Conversion task, and many of them are using an Output Alias identical to the Input Column name. Might that be causing the problem? I will try changing the Output Alias for some tables and see if they then transfer correctly. The "identical name" Output Alias values were created by the Migration Wizard for a DTS 2000 package.)
View 10 Replies
View Related
Apr 29, 2015
Currently we have one customer database containing various tables. As part of requirements for a new client, we need to manage their data in a totally separate database. The tables and structure are exactly the same but we would be loading data into a separate database.
I am looking for a way to combine tables with the same name in each database when I run queries, rather than having to query each database separately. Currently we actually have many queries set up in MS Access which use an ODBC link to query the data off SQL server. I am aware it is possible to apply a UNION SELECT in Access from 2 separate ODBC connections, but this is extremely slow.So my initial question is - is there a way to provide access to the tables from both databases over the same ODBC link? If this cannot be done over ODBC I guess we can consider more "modern" methods, but ideally we want to keep this in MS Access as that is where our existing queries are based. I was hoping that some kind of view can be treated as an ODBC connection.I mentioned ideally we want to keep the reporting queries in MS Access.
View 6 Replies
View Related
Sep 10, 2015
Till recently we were using the following code to retreive schema information of columns of tables
Dim schemaTable = connection.GetOleDbSchemaTable( _
System.Data.OleDb.OleDbSchemaGuid.Columns, _
New Object() {Nothing, Nothing, tableName, Nothing})
Now instead of getting the name of table (which i was using as param for filtering) i'm going to receive a sql-query. Now my question is if I were to get a query like the following :
SELECT
[EmployeeID],
[Title] + ' ' + [LastName] + ' ' + [FirstName] AS FullName,
[BirthDate],
[Address],
[City] + ', ' + [Region] + ', ' + [Country] + ' - ' + [PostalCode] AS FullAddress
FROM [dbo].[Employees]
Then how can I retrieve the schema information of only the columns present in the query.
(Its possible that i might get a query with multiple tables with joints)...
View 3 Replies
View Related
Sep 1, 2015
I want to create a view to get records from multiple tables. I have a UserID in all the tables. When I pass UserID to view it should get records from multiple tables. I have a table
UserInfo with as data as
UserID=1, FName = John,
LName=Abraham and Industry = 2. I have a
Industry table with data as
ID=1 and Name= Sports,
ID =2 and Name= Film.
When I query view where UserID=1 it should return record as
FName =John,
LName = Abraham and
Industry= Film
How to write query?
View 2 Replies
View Related
Feb 5, 2007
is there a step by step paper to get there? here is what i need to consider. I Iwill have many customers that will need their own set of records and access pages "branded for their company" each customer will have many clients. I am hosting this application on a windows 2003 server with SQL 2005 server enterprise.
I am using windows authentication, I have created a username in windows, then i added the windows user in SQL management studio in security, granted "DB Read" and "DB write" and again under the database security tab. still from the web authentication fails. i must be nissing a step or two?
I expect to set up a username for each database as i setup new customers.
View 1 Replies
View Related
Sep 10, 2007
I have one column in SQL Server 2005 of data type VARCHAR(4000).
I have imported sql Server 2005 database data into one mdb file.After importing a data into the mdb file, above column
data type converted into the memo type in the Access database.
now when I am trying to import a data from this MS Access File(db1.mdb) into the another SQL Server 2005 database, got the error of Unicode Converting a memo data type conversion in Export/Import data wizard.
Could you please let me know what is the reason?
I know that memo data type does not supported into the SQl Server 2005.
I am with SQL Server 2005 Standard Edition with SP2.
Please help me to understans this issue correctly?
View 4 Replies
View Related
Jul 12, 2001
We currently have a data warehouse running on SQL 7.0, SP2. One of our primary fact tables is now well over 155 million rows in it. The table is not very wide, as it only contains 17 columns, most of which are defined as integers. The entire database is only 20 GB.
The issue is that the loads from the staging table to this fact table have significantly deteriorated over the last month or so, dropping from over 400 transactions per second to around 85. We drop all the indexes on the fact table before we load the data into it.
Are there issues with a manageable table size in SQL 7.0 that we need to be concerned about? And should we consider partitioning the table into several smaller tables and join them with a "union all" view?
I really need to get this performance issue resolved, as our IT support vendor is pushing us to port the data warehouse to UDB because they tell us that SQL server is not scalable enough to handle this volume of data.
Thanks for any help you can provide.
George M. Parker
View 6 Replies
View Related
Aug 10, 2000
Hi,
How can i partition the large tables so that the insert and updates which iam doing on the tables take less time.
I want to know how can i partition large tables and if i do that how is that the performance is going to be increased.
Thanks.
View 1 Replies
View Related
Mar 13, 2001
How can I find largest 5 or 10 tables in a database?
Thanks in advance
Chan
View 2 Replies
View Related
Jun 21, 2007
Hello, All:
I have many, many Access databases that are roughly 1.5GB-3GBs each and they have millions of records. Each MS Access Database file corresponds to one Database in SQL server. I'm trying to simply transform the data as it is in Access to MS SQL 2005.
I'm using the 64 bit version of Windows Server 2003 and the 64 bit version of SQL 2005. The server is running four dual core AMD Operton processors and has 8GB of RAM with a 1TB RAID 5 configuration. I think the hardware should be sufficient but the SQL Server Import and Export Wizard can't seem to handle the large number of tables/records. If I do one table at a time, it works well; however, it produces the following error message whenever I try to import the entire database:
Pre-execute (Error)
Messages
Error 0xc0202009: {5A5BF7AD-E86B-4316-AD43-1912358C56F4}: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unspecified error".
(SQL Server Import and Export Wizard)
Error 0xc020801c: Data Flow Task: The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009.
(SQL Server Import and Export Wizard)
Error 0xc004701a: Data Flow Task: component "Source 64 - District Corporal Punishment Class" (5743) failed the pre-execute phase and returned error code 0xC020801C.
(SQL Server Import and Export Wizard)
Any ideas would be much appreciated!
Thank you,
Cody
View 1 Replies
View Related
Jan 12, 2004
Hi,
My client has a rather large database with some very large reports. Some of the reports have around 20 sub-reports a piece. We have decided to move the client's application to a .NET web application and would migrate them to SQL Server 2000.
The only problem is now, designing the reports. I have tried doing what Microsoft says (converting to stored procedures and views) but I keep getting syntax errors on the SQL side of things when I cut and paste.
For example, the following code is taken from Access :
SELECT tblProjects.fldCountry, tblProjects.fldDescription, tblOrganizations.fldAcronym, tblProjects.fldProjID, Max(tblProjYears.fldStartDate) AS MaxOffldStartDate, Max(tblProjYears.fldEndDate) AS MaxOffldEndDate, qryProjLocsWithFEData.fldProjPeriodID
FROM (tblProjects INNER JOIN tblOrganizations ON tblProjects.fldOrgID = tblOrganizations.fldOrgID) INNER JOIN (tblProjYears INNER JOIN qryProjLocsWithFEData ON tblProjYears.fldProjPeriodID = qryProjLocsWithFEData.fldProjPeriodID) ON tblProjects.fldProjID = tblProjYears.fldProjID
GROUP BY tblProjects.fldCountry, tblProjects.fldDescription, tblOrganizations.fldAcronym, tblProjects.fldProjID, qryProjLocsWithFEData.fldProjPeriodID
ORDER BY tblProjects.fldCountry, tblOrganizations.fldAcronym, tblProjects.fldProjID;
But when I try that in SQL Query Analyzer i get the error : The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
I'm pretty sure it's on the tblProjects.fldDescription Group By, but if I leave it out, it still throws an error. Anybody have any ideas?
Thanks
View 5 Replies
View Related
