Why Would Tables Pulled In From ODBC In Access Be Different Than Tables In SQL 2005 Tables?
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 Complete Forum Thread with Replies
Related Forum Messages:
Solution!-Create Access/Jet DB, Tables, Delete Tables, Compact Database
From Newbie to Newbie, Add reference to: 'Microsoft ActiveX Data Objects 2.8 Library 'Microsoft ADO Ext.2.8 for DDL and Security 'Microsoft Jet and Replication Objects 2.6 Library -------------------------------------------------------- Imports System.IO Imports System.IO.File Code Snippet 'BACKUP DATABASE Public Shared Sub Restart() End Sub 'You have to have a BackUps folder included into your release! Private Sub BackUpDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BackUpDB.Click Dim addtimestamp As String Dim f As String Dim z As String Dim g As String Dim Dialogbox1 As New Backupinfo addtimestamp = Format(Now(), "_MMddyy_HHmm") z = "C:Program FilesVSoftAppMissNewAppDB.mdb" g = addtimestamp + ".mdb" 'Add timestamp and .mdb endging to NewAppDB f = "C:Program FilesVSoftAppMissBackUpsNewAppDB" & g & "" Try File.Copy(z, f) Catch ex As System.Exception System.Windows.Forms.MessageBox.Show(ex.Message) End Try MsgBox("Backup completed succesfully.") If Dialogbox1.ShowDialog = Windows.Forms.DialogResult.OK Then End If End Sub Code Snippet 'RESTORE DATABASE Private Sub RestoreDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RestoreDB.Click Dim Filename As String Dim Restart1 As New RestoreRestart Dim overwrite As Boolean overwrite = True Dim xi As String With OpenFileDialog1 .Filter = "Database files (*.mdb)|*.mdb|" & "All files|*.*" If .ShowDialog() = Windows.Forms.DialogResult.OK Then Filename = .FileName 'Strips restored database from the timestamp xi = "C:Program FilesVSoftAppMissNewAppDB.mdb" File.Copy(Filename, xi, overwrite) End If End With 'Notify user MsgBox("Data restored successfully") Restart() If Restart1.ShowDialog = Windows.Forms.DialogResult.OK Then Application.Restart() End If End Sub Code Snippet 'CREATE NEW DATABASE Private Sub CreateNewDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CreateNewDB.Click Dim L As New DatabaseEraseWarning Dim Cat As ADOX.Catalog Cat = New ADOX.Catalog Dim Restart2 As New NewDBRestart If File.Exists("C:Program FilesVSoftAppMissNewAppDB.mdb") Then If L.ShowDialog() = Windows.Forms.DialogResult.Cancel Then Exit Sub Else File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb") End If End If Cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb; Jet OLEDB:Engine Type=5") Dim Cn As ADODB.Connection 'Dim Cat As ADOX.Catalog Dim Tablename As ADOX.Table 'Taylor these according to your need - add so many column as you need. Dim col As ADOX.Column = New ADOX.Column Dim col1 As ADOX.Column = New ADOX.Column Dim col2 As ADOX.Column = New ADOX.Column Dim col3 As ADOX.Column = New ADOX.Column Dim col4 As ADOX.Column = New ADOX.Column Dim col5 As ADOX.Column = New ADOX.Column Dim col6 As ADOX.Column = New ADOX.Column Dim col7 As ADOX.Column = New ADOX.Column Dim col8 As ADOX.Column = New ADOX.Column Cn = New ADODB.Connection Cat = New ADOX.Catalog Tablename = New ADOX.Table 'Open the connection Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb;Jet OLEDB:Engine Type=5") 'Open the Catalog Cat.ActiveConnection = Cn 'Create the table (you can name it anyway you want) Tablename.Name = "Table1" 'Taylor according to your need - add so many column as you need. Watch for the DataType! col.Name = "ID" col.Type = ADOX.DataTypeEnum.adInteger col1.Name = "MA" col1.Type = ADOX.DataTypeEnum.adInteger col1.Attributes = ADOX.ColumnAttributesEnum.adColNullable col2.Name = "FName" col2.Type = ADOX.DataTypeEnum.adVarWChar col2.Attributes = ADOX.ColumnAttributesEnum.adColNullable col3.Name = "LName" col3.Type = ADOX.DataTypeEnum.adVarWChar col3.Attributes = ADOX.ColumnAttributesEnum.adColNullable col4.Name = "DOB" col4.Type = ADOX.DataTypeEnum.adDate col4.Attributes = ADOX.ColumnAttributesEnum.adColNullable col5.Name = "Gender" col5.Type = ADOX.DataTypeEnum.adVarWChar col5.Attributes = ADOX.ColumnAttributesEnum.adColNullable col6.Name = "Phone1" col6.Type = ADOX.DataTypeEnum.adVarWChar col6.Attributes = ADOX.ColumnAttributesEnum.adColNullable col7.Name = "Phone2" col7.Type = ADOX.DataTypeEnum.adVarWChar col7.Attributes = ADOX.ColumnAttributesEnum.adColNullable col8.Name = "Notes" col8.Type = ADOX.DataTypeEnum.adVarWChar col8.Attributes = ADOX.ColumnAttributesEnum.adColNullable Tablename.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "ID") 'You have to append all your columns you have created above Tablename.Columns.Append(col) Tablename.Columns.Append(col1) Tablename.Columns.Append(col2) Tablename.Columns.Append(col3) Tablename.Columns.Append(col4) Tablename.Columns.Append(col5) Tablename.Columns.Append(col6) Tablename.Columns.Append(col7) Tablename.Columns.Append(col8) 'Append the newly created table to the Tables Collection Cat.Tables.Append(Tablename) 'User notification ) MsgBox("A new empty database was created successfully") 'clean up objects Tablename = Nothing Cat = Nothing Cn.Close() Cn = Nothing 'Restart application If Restart2.ShowDialog() = Windows.Forms.DialogResult.OK Then Application.Restart() End If End Sub Code Snippet 'COMPACT DATABASE Private Sub CompactDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CompactDB.Click Dim JRO As JRO.JetEngine JRO = New JRO.JetEngine 'The first source is the original, the second is the compacted database under an other name. JRO.CompactDatabase("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb; Jet OLEDB:Engine Type=5", "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:Program FilesVSoftAppMissNewAppDBComp.mdb; JetOLEDB:Engine Type=5") 'Original (not compacted database is deleted) File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb") 'Compacted database is renamed to the original databas's neme. Rename("C:Program FilesVSoftAppMissNewAppDBComp.mdb", "C:Program FilesVSoftAppMissNewAppDB.mdb") 'User notification MsgBox("The database was compacted successfully") End Sub End Class
View Replies !
Updating Tables In SQL Server 7 Based On Access 97 Tables
Let me start by saying I'm very new to SQL Server... I've upsized my Access Database to SQL Server successfully, but need to keep updating my SQL Server Database with data in my Access 97 database. For example, a table in my Access Database is updated on a regular basis and at certain times, we want to upload that information to our SQL Server database. How can I easily overwrite data in a SQL Server table with data imported from Access 97? I keep getting error messages about the primary/foreign keys... Any help would be greatly appreciated. Glenn
View Replies !
Import Into Access Tables Using ODBC
Hi, I want to import into an MDB table a csv file. I'm trying to use the bulk copy table. my function is: SQL = "SELECT * INTO [my_table] FROM [ODBC;Driver=Micrsoft text driver (*.txt; *csv) ;Dbq=c:\;Extensions=asc,csv,tab,txt;].table.csv" db.OpenEx( "Driver=Microsoft Access Driver .mdb);DBQ=c:\access.mdb;", CDatabase::noOdbcDialog ); db.ExecuteSQL( SQL ); when i run this function i get an error : "You cannot use ODBC to import from, export to, or link an external Microsoft Jet or ISAM database table to your database" when i try to import in the same way a dbf file (insted the csv file) with VFP it's working well. what seems to be the problem? how can i fix it? or if some one know how can i import a large csv file into access DB in an efficient diffrent way? thanks ishay
View Replies !
Linked Tables W/ ODBC From SQLServer 6.5 To Access
Background of what I am doing: I am linking the tables from SQLServer 6.5 into Access through ODBC using translator code page 1252 selected in the configuration of my datasource. The tables that I need to update is simple: delete the old data and insert or append with the new data. My ODBC connection is failing when I try to insert a string (datatype memo) from Access into SQLServer. I determined the length of one string to be 1829 characters long & the other to be 2044 as a sample. The table has 3 fields, Id & type which should not be giving me any greef but the last field (memo datatype) is giving me problems and causing the ODBC failure. Also, please note that in SQLServer the last field is Text(16). I'm new to this field so whoever responds to this message, please keep your answer as simple but detailed as possible. Thanks in advance!!!
View Replies !
MS ACCESS Query Returns Empty Recordset From Odbc Linked Sql Server Tables
Hi,I'm developing a asp.net application with a MS Access 2000 database (for reporting purposes etc.) using an ODBC link. Some tables of it are linked (another ODBC link) to a SQL Server 2005 database. I need to show some records in my application coming from a MS Access query running on LINKED tables (not imported tables) from the SQL database.Problem: when I IMPORT the tables, recordset is complete, when I LINK the tables, the recordset is empty (no records, only colum heads, no error message). Probably this is caused by the differences in SQL syntax (thank you Bill ;-)).This is my query in Access:SELECT IIf([dbo_cicntp.cnt_email]="",LCase([dbo_cicmpy.cmp_e_mail]),LCase([dbo_cicntp.cnt_email])) AS EMAIL, UCase(CStr([dbo_cicmpy.ID])) AS CODE, dbo_cicmpy.cmp_name AS BEDRIJF, [cnt_l_name] & " " & [cnt_f_name] AS FAMILIENAAM, dbo_cicntp.Gender AS MVJ, IIf(UCase([dbo_cicntp.taalcode])="NL","NL",IIf(UCase([dbo_cicntp.taalcode])="FR","F","E")) AS TAAL, IIf([dbo_cicntp].[active_y]=1,True,False) AS ACTIEF, dbo_cicmpy.SubSector AS VESTIG, "CATEGORIE" AS CAT, dbo_cicntp.cnt_f_tel AS TEL, Trim([dbo_cicmpy.cmp_fadd1] & " " & [dbo_cicmpy.cmp_fadd2] & " " & [dbo_cicmpy.cmp_fadd3]) AS STRAAT, dbo_cicmpy.cmp_fpc AS POSTNR, dbo_cicmpy.cmp_fcity AS GEMEENTE, dbo_cicmpy.cmp_fctry AS LAND, etc...FROM (dbo_cicmpy INNER JOIN dbo_cicntp ON dbo_cicmpy.cnt_id = dbo_cicntp.cnt_id) INNER JOIN dbo_pred ON dbo_cicntp.predcode = dbo_pred.predcodeWHERE (((dbo_cicmpy.DivisionCreditorID) Is Null));Is there any solution?Could this be a solution: make an first query to the SQL database just to gather the "clean" fields (I mean, without IIFs, Lcases, Ucases and other functions unknown by SQL Server I use now) and then adapt my query here above mentioned, and apply my IIF, Lcases and Ucases on the recordset returned by my first query?Thanks a lot for your time.Cl.
View Replies !
Track The Changes To Normalised Tables And Update The Denormalised Tables Depending On The Changes To Normalised Tables
We have 20 -30 normalized tables in our dartabase . Also we have 4tables where we store the calculated data fron those normalised tables.The Reason we have these 4 denormalised tables is when we try to dothe calcultion on the fly, our site becomes very slow. So We haveprecalculated and stored it in 4 tables.The Process we use to do the precalcultion, will get do thecalculation and and store it in a temp table. It will compare the thetemp with denormalised tables and insert new rows , delte the old oneans update if any changes.This process take about 20 mins - 60mins. Ittakes long time because in this process we first do the calculationregardless of changes and then do a compare to see what are changed andremove if any rows are deleted, and insert new rowsand update thechanges.Now we like to capture the rows/columns changed in the normalisedtables and do only those chages to the denormalised table , which weare hoping will reduce the processing time by atleast 50%WE have upgraded to SQL SERVER 2005.So We like to use the newtechnology for this process.I have to design the a model to capture the changes and updated onlythose changes.I have the list of normalised tables and te columns which will affectthe end results.I thought of using Triggers or OUTPUT clause to capture the changes.Please help me with the any ideas how to design the new process
View Replies !
Access To Tables SQL Server 2005
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 Replies !
Data Access To Large Tables In Sql 2005
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 Replies !
Linking Access Tables Into SQL Server 2005
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 Replies !
Cannot Link To SQL Tables Using ODBC Link Table In Access 2003
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 Replies !
This Is An Access Project Linking Tables On SQL 2005 Server.
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 Replies !
Connection Fails In Linking SQL Server 2005 Tables Into Access Using DAO 3.51
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 Replies !
Transferring MS Access Tables To SQL Server 2005: PRODUCT LEVEL TOO LOW Error
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 Replies !
Can I Export Tables So That Existing Tables In Destination Database Will Be Modified?
I'm working on an ASP.Net project where I want to test code on a localmachine using a local database as a back-end, and then export it tothe production machine where it uses the hosting provider's SQL Serverdatabase on the back-end. Is there a way to export tables from oneSQL Server database to another in such a way that if a table alreadyexists in the destination database, it will be updated to reflect thechanges to the local table, without existing data in the destinationtable being lost? e.g. suppose I change some tables in my localdatabase by adding new fields. Can I "export" these changes to thedestination database so that the new fields will be added to thedestination tables (and filled in with default values), without losingdata in the destination tables?If I run the DTS Import/Export Wizard that comes with SQL Server andchoose "Copy table(s) and view(s) from the source database" and choosethe tables I want to copy, there is apparently no option *not* to copythe data, and since I don't want to copy the data, that choice doesn'twork. If instead of "Copy table(s) and view(s) from the sourcedatabase", I choose "Copy objects and data between SQL Serverdatabases", then on the following options I can uncheck the "CopyData" box to prevent data being copied. But for the "CreateDestination Objects" choices, I have to uncheck "Drop destinationobjects first" since I don't want to lose the existing data. But whenI uncheck that and try to do the copy, I get collisions between theproperties of the local table and the existing destination table,e.g.:"Table 'wbuser' already has a primary key defined on it."Is there no way to do what I want using the DTS Import/Export Wizard?Can it be done some other way?-Bennett
View Replies !
Saving Tables That Are Generated By Queries As HTML File Or Sub-tables
I have a trade data tables (about 10) and I need to retrieve information based on input parameters. Each table has about 3-4 million rows. The table has columns like Commodity, Unit, Quantity, Value, Month, Country A typical query I use to select data is "Select top 10 commodity , sum(value), sum(quantity) , column4, column5, column6 from table where month=xx and country=xxxx" The column4 = (column2)/(total sum of value) and column 5=(column3)/(total sum of quantity). Column6=column5/column4. It takes about 3-4 minutes for the query to complete and its a lot of time specially since I need to pull this information from a webpage. I wanted to know if there is an alternate way to pull the data from server ? I mean can I write a script that creates tables for all the input combinations i.e month x country (12x228) and save them in table (subtable-table) with a naming convention so from the web I can just pull the table with input parameters mapped to name convention and not running any runtime queries on database ?? OR Can I write a script that creates a html files for each table for all input combinations save them ? OR Is there exists any other solution ?
View Replies !
Dynamic Tables Names And Temporary Tables Options
Firstly I consider myself quite an experienced SQL Server user, andamnow using SQL Server 2005 Express for the main backend of mysoftware.My problem is thus: The boss needs to run reports; I have designedthese reports as SQL procedures, to be executed through an ASPapplication. Basic, and even medium sized (10,000+ records) reportingrun at an acceptable speed, but for anything larger, IIS timeouts andquery timeouts often cause problems.I subsequently came up with the idea that I could reduce processingtimes by up to two-thirds by writing information from eachcalculationstage to a number of tables as the reporting procedure runs..ie. stage 1, write to table xxx1,stage 2 reads table xxx1 and writes to table xxx2,stage 3 reads table xxx2 and writes to table xxx3,etc, etc, etcprocedure read final table, and outputs information.This works wonderfully, EXCEPT that two people can't run the samereport at the same time, because as one procedure creates and writesto table xxx2, the other procedure tries to drop the table, or read atable that has already been dropped....Does anyone have any suggestions about how to get around thisproblem?I have thought about generating the table names dynamically using'sp_execute', but the statement I need to run is far too long(apparently there is a maximum length you can pass to it), and evenbreaking it down into sub-procedures is soooooooooooooooo timeconsuming and inefficient having to format statements as strings(replacing quotes and so on)How can I use multiple tables, or indeed process HUGE procedures,withdynamic table names, or temporary tables?All answers/suggestions/questions gratefully received.Thanks
View Replies !
Insert Records From Foxpro Tables To SQL Server Tables
Hi, Currently, I'm using the following steps to migrate millions of records from Foxpro tables to SQL Server tables: 1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables in a dummy database. All the SQL tables have the same columns as the Foxpro tables. 2. Manipulate the data in the SQL tables of the dummy database and save the manipulated data into the SQL tables of the real database where the tables may have different structure from the corresponding Foxpro tables. I only know the following ways to import Foxpro data into SQL Server: #1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables #2. Transfer Foxpro records to .dat files and then Bulk Insert to SQL Server tables #3. DTS Foxpro records directly to SQL Server tables I'm thinking whether the following choices will be better than the current way: 1st choice: Change step 1 to use #2 instead of #1 2nd choice: Change step 1 to use #3 instead of #1 3rd choice: Use #3 plus manipulating in DTS to replace step 1 and step 2 Thank you for any suggestion.
View Replies !
Temp. Tables / Variables / Process Keyed Tables ?
I have 3 Checkbox list panels that query the DB for the items. Panel nº 2 and 3 need to know selection on panel nº 1. Panels have multiple item selection. Multiple users may use this at the same time and I wanted to have a full separation between the application and the DB. The ASP.net application always uses Stored Procedures to access the DB. Whats the best course of action? Using a permanent 'temp' table on the SQL server? Accomplish everything on the client side? [Web application being built on ASP.net 3.5 (IIS7) connected to SQL Server 2005)
View Replies !
How To Create Multiple Tables On The Fly So That Every User Each Has His/her Own Set Of Tables?
Hello all, Being still a relative newcomer to SQL Server (people may say I'm trying to take on too much being somewhat inexperienced once they read about the problem I'm trying to tackle, but alas...) I'm running into the following problem: I need to create tables in my user database on the fly (using Stored Procedures) so that each table can be created many times in the database but only once for every user. The tables should be named something like "username.Table1", "username.Table2" etc. as opposed to "dbo.Table1". I then want to use the stored procedure from .NET/C# in my web application, so that i can create the complete set of usertables for each of my clients. Now, I tackled the stored procedure part (that is, it creates all the tables I need with all the parameters I want) and am able to use it from my web application (which took some time to learn but proved quite easy to do), but I cannot seem to get it coupled to the current user (instead of the dbo). Every time I trie, the tables are created as dbo.Table1 and when I try to create a new set, it gives a warning ("table with name such and so already exists..."). I made sure to log in as an authenticated user (using forms authentication) before trying to create the tables but this gives the aforementioned result. What am I doing wrong? I use Visual Web Developer Express, SQL Server 2005 Express and IIS version 5.1 Please help :-D Greetingz, DJ Roelfsema
View Replies !
MSDE And ODBC Tables
I have a MSDE database and I need to delete the contents of the exitingtables and then import new data on a scheduled basis from an ODBC datasource (preferable through a system DSN). This was easy to do in SQL2000 Enterprise given the DTS tools and then just scheduling a job thruthe agent.Is there an example of how I could do this just using scripts and MSDE(like a stored proc)? It looks like I have the agent still in MSDE touse.Help appreciated.Thanks,Frank*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View Replies !
ODBC Linked Tables
In order to use Access 97 as your frontend and SQL 7 as your backend on a network (where the frontend is located on the same computer as the backend and people just map to Access frontend) where security for the data is recognized by your domain login account and what domain group you belong to..do you have to have the same System DSN on everyone's computer for linking? I am still having problems with people linking to the SQL server through the network and only my account is working correctly from my desktop. Everyone else who tries to link gets an ODBC failure error message when trying to open one of the linked tables. I have heard from one person that you must have the same system DSN on every client's computer but to me that makes no sense as I do not have the DSN listed on my individual computer...I am just going through the network as myself..not as an admin..and getting directly to the data. Everyone else can get to the shared folder on the server but cannot get to the linked tables. Any ideas?
View Replies !
ODBC Connection - Lost Tables
I have a SQL Server database that I connect a front end to using an ODBC connection. Our LAN folks upgraded the server recently and now I can no longer see any of the tables through the ODBC connection that the user used for login has permission in SQL Server Enterprise Manager to see - throught the ODBC connection the user can only see things like: dbo.spt_datatype_info dbo.spt_datatype_info_ext dbo.spt_fallback_db dbo.spt_fallback_dev dbo.spt_fallback_usg . . . INFORMATION_SCHEMA.CHECK_CONSTRAINTS INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE etc. I've tried deleting the user for the connection and re-establishing it with owner permissions. I've tried deleting the dsn and re-establishing that as well but nothing so far. Please help!!!
View Replies !
ODBC Linked Tables Go Read Only
Hi, I have an Access database Front End which use SQL server as a Back End. The two are connected using ODBC. Occasionally, some of the linked tables in Access go read only. I can't add or edit records. The only way I know to get round the problem is to delete the link and re-create it. Refreshing the link does not work. Can anybody suggest why this would happen, and the best way to fix it when it does occur? Edited 12:10 06/14/07 Some extra info. At the same time it goes read only, the link loses it's primary key. Thanks Colin
View Replies !
ODBC Readonly (viewonly) Linked Tables....
Hi,I have a prod database (main bread & bread DB) and have a small accessdatabase that sales team is using... I created a login inside SQL 2000and gave db_read permission and SELECT permissions... and public.Role of public is EXEC store procs and view some systables...I linked those tables that I gave permissions through ODBC link intoaccess db with the user that I created inside SQL as readonly user...but to my surprise when I ran a insert query from access on SQLtables... I was able to update and insert data... if my ODBC link isonly for readonly.. why was I able to unpdate & insert data into SQLtable?I don't want to give write/update/insert permissions for SQL tables tosales team... or anyone outside SQL 2000 server DB.Is there any easy way to create viewonly users inside SQL?I created users like this: security -logins -new login...select none serverrole.. db access (bread & butter DB)Thank you,hj******Pardon my English...
View Replies !
Local Temp Tables And OLEDB/ODBC
I have recently switched from ODBC to OLEDB and I have noticed a difference in the way these two handle local temp tables. With ODBC the scope of the local temp tables is the connection in which they were created (unless they are created in a stored proc, in which case the scope is the stored proc). That means you can create the temp table in one statement, fill it with data in another and retrieve the data in the third, and everything works fine as long as these statements are bound to the same connection. I have used this in a few places in my applications as a way to store connection specific data and a way to simplify some other things. But now I have a problem. In OLEDB the scope of the temp tables is the session it was created in and that is not good for me since I can not store connection specific data in temp tables any more. Any ideas? Thank you!
View Replies !
Import Tables Through ODBC Connection Into SQL Express
I'm trying to import data from an Oracle database into SQL Express. Basically I have a database that's local to my machine (C# front end with a SQL express back end) but I need to tie in some data from an Oracle database. The reason I'm trying to import it instead of just using a connection in C# to hit it is that I need to be able to access the tables while I'm offline so I need a local copy. I couldn't find any references on doing it through stored procedures or anything like that. Any assistance would be greatly appreciated. thanks in advance.
View Replies !
HIDE System Tables From ODBC Connection / User
I had a question about some of the tables that show up when you link through a ODBC datasource using Access to link to SQL tables on SQL Server. Would it affect anything like merge replication, select statements etc, if the administrator were to hide tables with the following naming: conflict_TABLENAME (merge replication participant tables). ctsv_######### dtproperties MSmerge_contents through MSmergesubscription_properties syncobj_0x000000 sysalternates through syssegments tsvw_####### INFORMATION_SCHEMA.CHECK_CONSTRAINTS through INFORMATION_SCHEMA.VIEWS
View Replies !
Need To Hide Irrelevant Tables/views From ODBC Connection
hi, I maked ODBC for SQL erver data base and linked with Access database which is working good but when I check ODBC link in access it shows alot of other stuff and all tables related to that database . Is there any way to remove irrelevant tables/views etc from that ODBC - thanks for help AA
View Replies !
Smalller Tables From Larger Tables
hi. I am very new to databases. At the moment I have one big database that has column titles such as author, book title, rating, isbn, publisher, publish date, copyright date, graphical image review, availability, genre. I want to break this large table down into say four smaller relational tables. Partly because it is so large and partly to make it easier to bind the data to the web site. I also want to keep the large table. How can i do this? I use sql server management express. The database is on my hosts database. I also use VWD 2005 express edition. thanks for your time nick
View Replies !
Database Tables And Lookup Up Tables?
Ok say I would like to build a table for of the following questions(say 6 questions for the sake of argument): Do I just stored the index of the radiobuttonlist. What are some resources that I could look at. Should I make a look up table. 5) If money were no object, I would live . . . Prefer not to say On a tropical island In a New York penthouse In an English castle On a Texas ranch In a Malibu beach house In a mountain retreat (Selected) On the moon None of the above 1 ---> 2 ---> 3 ---> 4 ---> 5) --->6 This is the question we are looking at. 6 ---> How should I create the database table for the above example.
View Replies !
Multiple Datasets And Tables With In Tables
I have a report that is sorted by User that shows the selected months details. eg: User1 Details Summary for User1 User2 Details Summary for User2 ... This works fine. I am trying to add for each users summaries for the previous month. I was thinking to call a second dataset using the same query, but for the previous month. I would then create a table with the results I want and imbed it into the first table. eg: User1 Details Summary for User1 Previous months summary for User1 User 2 Details Summary for User2 Previous months summary for User2 Problem: The second instance of the query requires the User to be passed. I do not know how to do that. Also, is this even possible? Thanx
View Replies !
Joining Tables - But Data Not Always In All Tables
I have created 3 views, which I then want to join to produce an overall result. The first view returns customer details, along with payment information. The next two views return values only when the customer has purchased extras outside our standard product i.e. if there is no purchase of an extra, then nothing is written to the extra's table. When I join the views together they only return values where data has been matched in all 3 views i.e. extra's have been purchased. Any data that did not match in all 3 view (i.e. no extra's purchased) is either ignored or dropped from the results. So I need my script to return all values even if no data exists in the two extra views. My scripts are as follows: Main View SELECT CUSTOMER_POLICY_DETAILS.POLICY_DETAILS_ID, CUSTOMER_POLICY_DETAILS.HISTORY_ID, CUSTOMER_POLICY_DETAILS.AUTHORISATIONUSER, CUSTOMER_POLICY_DETAILS.AUTHORISATIONDATE, ACCOUNTS_TRANSACTION.TRANSACTION_CODE_ID, CUSTOMER_INSURED_PARTY.SURNAME, SYSTEM_INSURER.INSURER_DEBUG, SYSTEM_SCHEME_NAME.SCHEMENAME, CUSTOMER_POLICY_DETAILS.POLICYNUMBER, --TotalPayable IsNull(SUM(CASE LIST_TRAN_BREAKDOWN_TYPE.IncludeInTotal WHEN 1 THEN ACCOUNTS_TRAN_BREAKDOWN.AMOUNT ELSE 0 END), 0) AS TotalPayable, --NetPremium IsNull(SUM(CASE ACCOUNTS_TRAN_BREAKDOWN.Tran_Breakdown_Type_ID WHEN 'NET' THEN ACCOUNTS_TRAN_BREAKDOWN.AMOUNT ELSE 0 END), 0) AS NetPremium, --IPT IsNull(SUM(CASE WHEN SubString(ACCOUNTS_TRAN_BREAKDOWN.Premium_Section_ID, 1, 3) = 'TAX' THEN ACCOUNTS_TRAN_BREAKDOWN.AMOUNT ELSE 0 END), 0) AS IPT, --Fee IsNull(SUM(CASE ACCOUNTS_TRAN_BREAKDOWN.Tran_Breakdown_Type_ID WHEN 'FEE' THEN ACCOUNTS_TRAN_BREAKDOWN.AMOUNT ELSE 0 END), 0) AS Fee, --TotalCommission IsNull(SUM(CASE WHEN SubString(ACCOUNTS_TRAN_BREAKDOWN.Tran_Breakdown_Type_ID, 4, 4) = 'COMM' THEN ACCOUNTS_TRAN_BREAKDOWN.AMOUNT ELSE 0 END), 0) AS TotalCommission FROM ACCOUNTS_CLIENT_TRAN_LINK INNER JOIN ACCOUNTS_TRANSACTION ON ACCOUNTS_CLIENT_TRAN_LINK.TRANSACTION_ID = ACCOUNTS_TRANSACTION.TRANSACTION_ID INNER JOIN ACCOUNTS_TRAN_BREAKDOWN ON ACCOUNTS_TRANSACTION.TRANSACTION_ID = ACCOUNTS_TRAN_BREAKDOWN.TRANSACTION_ID INNER JOIN LIST_TRAN_BREAKDOWN_TYPE ON ACCOUNTS_TRAN_BREAKDOWN.TRAN_BREAKDOWN_TYPE_ID = LIST_TRAN_BREAKDOWN_TYPE.TRAN_BREAKDOWN_TYPE_ID INNER JOIN CUSTOMER_POLICY_DETAILS ON CUSTOMER_POLICY_DETAILS.POLICY_DETAILS_ID = ACCOUNTS_CLIENT_TRAN_LINK.POLICY_DETAILS_ID AND CUSTOMER_POLICY_DETAILS.HISTORY_ID = ACCOUNTS_CLIENT_TRAN_LINK.POLICY_DETAILS_HISTORY_ID INNER JOIN SYSTEM_INSURER ON CUSTOMER_POLICY_DETAILS.INSURER_ID = SYSTEM_INSURER.INSURER_ID INNER JOIN SYSTEM_SCHEME_NAME ON CUSTOMER_POLICY_DETAILS.SCHEMETABLE_ID = SYSTEM_SCHEME_NAME.SCHEMETABLE_ID INNER JOIN CUSTOMER_INSURED_PARTY ON ACCOUNTS_CLIENT_TRAN_LINK.INSURED_PARTY_HISTORY_ID = CUSTOMER_INSURED_PARTY.HISTORY_ID AND ACCOUNTS_CLIENT_TRAN_LINK.INSURED_PARTY_ID = CUSTOMER_INSURED_PARTY.INSURED_PARTY_ID WHERE CUSTOMER_POLICY_DETAILS.AUTHORISATIONDATE = '2007-08-17' AND ACCOUNTS_TRANSACTION.TRANSACTION_CODE_ID <> 'PAY' GROUP BY CUSTOMER_POLICY_DETAILS.POLICY_DETAILS_ID, CUSTOMER_POLICY_DETAILS.HISTORY_ID, CUSTOMER_POLICY_DETAILS.AUTHORISATIONUSER, CUSTOMER_POLICY_DETAILS.AUTHORISATIONDATE, ACCOUNTS_TRANSACTION.TRANSACTION_CODE_ID, CUSTOMER_INSURED_PARTY.SURNAME, SYSTEM_INSURER.INSURER_DEBUG, SYSTEM_SCHEME_NAME.SCHEMENAME, ACCOUNTS_TRANSACTION.Transaction_ID, CUSTOMER_POLICY_DETAILS.POLICYNUMBER Add on View 1 CREATE VIEW TOPCARDPA AS select policy_details_id, History_id, Selected from customer_addon where product_addon_id = 'TRPCAE01' Add on View 2 CREATE VIEW TOPCARDRESC AS select policy_details_id, History_id, Selected from customer_addon where product_addon_id = 'HICRESC01' Join Result Script SELECT TOPCARD.AUTHORISATIONUSER, TOPCARD.AUTHORISATIONDATE, TOPCARD.TRANSACTION_CODE_ID, TOPCARD.SURNAME, TOPCARD.INSURER_DEBUG, TOPCARD.SCHEMENAME, TOPCARD.POLICYNUMBER, TOPCARD.TotalPayable, TOPCARD.NetPremium, TOPCARD.IPT, TOPCARD.Fee, TOPCARD.TotalCommission, TOPCARDPA.SELECTED, TOPCARDRESC.SELECTED FROM dbo.TOPCARD TOPCARD INNER JOIN dbo.TOPCARDPA TOPCARDPA ON TOPCARD.POLICY_DETAILS_ID = TOPCARDPA.POLICY_DETAILS_ID AND TOPCARD.HISTORY_ID = TOPCARDPA.HISTORY_ID INNER JOIN dbo.TOPCARDRESC TOPCARDRESC ON TOPCARD.POLICY_DETAILS_ID = TOPCARDRESC.POLICY_DETAILS_ID AND TOPCARD.HISTORY_ID = TOPCARDRESC.HISTORY_ID I have included all the scripts I have used, as others may find them useful, in addition to anyone that is able to provide me with some assistance. Thanks in advance for for the help.
View Replies !
Reference Tables Or Lookup Tables
I have facing a design problem and unable to justify which design to choose for my data model. Usually, what we have is like data tables and reference tables to store data in those data tables. My database has tables with 20-30 columns in them. And most of them (though not all of them), stores data from some reference tables. Meaning each column has an associated reference table where it stores possible list of values for that particular column. Sort of data domain for that column. FYI, My database is related to medical field. For example, A table that has a varchar(40) column called "Differentiation". It can only store values from following list: - Undifferentiated - Moderate - Poor - Poor - Moderate - Moderate - well Now, to implement this, simple solution would be to have a reference table where I can store all these possible values...And then have just a reference of each data item into my "Differentiation" column in the table. This is simplest and probably the best solution for such thing and i can also have referential integrity implemented for this. But now if we look at the bigger picture, my database is growing and I have about 80 tables which I need to create where most of the columns will have different reference tables like I mentioned above. Approximate number of reference tables is 300 tables. All the reference table will have same structure, with different values for different columns. Now, what seems to me is, because the table structure is same for every column, rather than having 300 different tables, I can only have 2 tables, where I can put all these reference values into these 2 tables. Like, Table 1 : This table can have name of the reference table like "differentiationlist" etc. Table 2: It has reference to the reference table list in Table 1 discussed above and all the values that are part of that reference table can go in this table with its reference. But problem with this is, because all the reference tables are in these two tables, I don't know how to implement referential integrity in this design. Does anyone have any idea or solution for situation like this? Thanks, Ujjaval
View Replies !
How To Basically Copy Tables With New Names Rather Than Create Similar Tables From Similar Manual Input.
I have a table that I am basically reduplicating a couple of times for each part of this database that I want to create.Each table basically has the same data: The tables will be called motherTable, fatherTable, sonTable, daughterTable and so on.I am pretty much using the following in each column: UserID, MotherID(or FatherID or SonID, etc., etc. and so on for each unique table), FirstName, LastName, MiddleName, BirthPlace, Photo, Age.I don't see an option to copy a table and just modify the second ID part and rename that table accordingly.How can I make this an easier way of creating these similar tables without retyping all these columns over and over again?Thanks in advance.
View Replies !
Query To Select Multiple Tables In Dataset Through ODBC Connection
Hi there, I have a MS Access database (mdb) containing the following tables: Crime Criminal CrimeCommitted Hideout CriminalType The Criminal table contains information about each criminal and the CrimeCommitted table contains information about the specific crimes. I've written the following query to return only the latest crime committed by each criminal: Code Snippet SELECT Criminal.CriminalID, Criminal.Firstname, Criminal.Lastname, Criminal.Nickname, Criminal.Gender, Criminal.DOB, Criminal.Eyes, Criminal.Complexion, Criminal.Weight, Criminal.Height, Criminal.Build, Criminal.Scars, Criminal.Occupation, Criminal.CrimeOrgID, Criminal.IQ, Criminal.Hideout, Criminal.CriminalType, Max(CrimeComitted.Date) AS Last_Crime_Comitted FROM Criminal INNER JOIN CrimeComitted ON Criminal.CriminalID=CrimeComitted.CriminalID GROUP BY Criminal.CriminalID, Criminal.Firstname, Criminal.Lastname, Criminal.Nickname, Criminal.Gender, Criminal.DOB, Criminal.Eyes, Criminal.Complexion, Criminal.Weight, Criminal.Height, Criminal.Build, Criminal.Scars, Criminal.Occupation, Criminal.CrimeOrgID, Criminal.IQ, Criminal.Hideout, Criminal.CriminalType; This query works fine for obtaining the Criminal table data, but once i've include CrimeCommitted.Country in the SELECT statement, the data returned contained all the crimes committed by each criminal (i just need the latest crime). The query doesn't work when another table, other than Criminal, is selected. How can i obtain the columns in the CrimeCommitted table in this query?
View Replies !
Import Access Tables (set Up As Pass-through Table Types To Oracle )--OLE DB Connection To Access Cannot See Them
Access Connection create a new Connection Manager by right-clicking in the Connection Managers section of the design area of the screen. Select New OLE DB Connection to bring up the Configure OLE DB Connection Manager dialog box. Click New to open the Connection Manager. In the Provider drop-down list, choose the Microsoft Jet 4.0 OLE DB Provider and click OK. Browse to the Access database file and connection set up---all good!!! Dataflow task Add an OLE DB Source component Double-click the icon to open the OLE DB Source Editor. Set the OLE DB Connection Manager property to the Connection Manager that I created . Select Table from the Data Access Mode drop-down list. I cannot see the tables set up as set up as pass-through table types to a Oracle 9i db Any ideas please help thanks in advance Dave
View Replies !
Bad Performance In Queries With Jet4.0 And Linked ODBC-tables To SQL-Server 2000
I changed from Access97 to AccessXP and I have immense performanceproblems.Details:- Access XP MDB with Jet 4.0 ( no ADP-Project )- Linked Tables to SQL-Server 2000 over ODBCI used the SQL Profile to watch the T-SQL-Command which Access ( whocreates the commands?) creates and noticed:1) some Jet-SQL commands with JOINS and Where-Statements aretranslated very well, using sp_prepexe and sp_execute, including thesimilar SQL-Statement as in JET.2) other Jet-SQL commands with JOINS and Where-Statements aretranslated very bad, because the Join wasn´t sent as a join, Accesscollects the data of the individual tables seperately.Access sends much to much data over the network, it is a disaster!3) in Access97 the same command was interpreted wellCould it be possible the Access uses a wrong protocol-stack, perhapsJet to OLEDB, OLEDB to ODBC, ODBC to SQL-Server orJet to ODBC, ODBC to OLEDB and OLEDB to SQL-Server instead ofJet to ODBC and ODBC direct to SQL-ServerDoes anyone knows anything about:- Command-Interpreter of JetODBC, Parameters, how to influence thecommand-interpreter- Protocol-Stack of a Jet4.0 / ODBC / SQL-Server applicationThanks , Andreas
View Replies !
Trouble Using ADOX To Create Linked Tables In Jet Database From An ODBC Datasource
Hai, I am using ADOX to create linked tables in a jet database from an ODBC datasource. The tables in the ODBC data source does not have a primary key. so I am only able to create read only linked tables.But I want to update the records also. I tried adding a primary key column to the linked table while creating the link. but I am getting an error while adding the table to the catalog. The error message is "Invalid Argument". I use the following code for creating the linked table Sub CreateLinkedTable(ByVal strTargetDB As String, ByVal strProviderString As String, ByVal strSourceTbl As String, ByVal strLinkTblName As String) Dim catDB As ADOX.Catalog Dim tblLink As ADOX._Table Dim ADOConnection As New ADODB.Connection ADOConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strTargetDB & ";User Id=admin;Password=;") catDB = New ADOX.Catalog catDB.ActiveConnection = ADOConnection tblLink = New ADOX.Table With tblLink ' Name the new Table and set its ParentCatalog property ' to the open Catalog to allow access to the Properties ' collection. .Name = strLinkTblName .ParentCatalog = catDB ' Set the properties to create the link. Dim adoxPro As ADOX.Property adoxPro = .Properties("Jet OLEDB:Create Link") adoxPro.Value = True adoxPro = .Properties("Jet OLEDB:Link Provider String") adoxPro.Value = strProviderString adoxPro = .Properties("Jet OLEDB:Remote Table Name") adoxPro.Value = strSourceTbl End With 'Adding primary key, '***** the source column name is "Code" ****** tblLink.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "Code") 'Append the table to the Tables collection. '******The exception occurs on the following line*********** catDB.Tables.Append(tblLink) 'Append the primary index to table. catDB = Nothing End Sub If I avoid the line for adding the primary key,everything works fine,but the table ctreated is readonly. Thanks in advance Sudeep T S
View Replies !
Access 2007 Linked Tables (vs Access 2003)
We migrated a MS Access 2003 mdb into MS Access 2007. The mdb has linked tables to SQL Server via a DSN and utilizes a mdw file. In 2003, the username/password is "passed" to SQL Server, so the UID/PWD that is used for opening the mdb, is used in SQL Server. Opening the same file in 2007 using the same mdw, gives a secondary login on SQL Server. Is there a way to have MS Access 2007 pass the UID/PWD to SQL Server on linked tables, the same way that 2003 does? Thanks!
View Replies !
Import Access Tables Into SQL
Still really new at all this, but learning lots thanks to this forum. I was wondering - is there a way to import Access tables into my SQL Server 2005 ? (The Data and the Table Design?)
View Replies !
|
TRACKER
