I have been wacthing several webcasts, reading blogs and articles and understanding what BI is, especillay ETL and Analysis Services, but somehow have not been able to find a solution to my problem. I have been able to understand most of the Analysis Services part of SQL Server 2005, but need help with taking data from MS Access to SQL Server to create a cube.
I have an MS Access 2003 DB with 4 tables which holds all the data from which I want to generate BI. I have just installed SQL Server 2005 Developer Edition.
Below are my questions:
1. Do I need to first make a new DB in SQL Server 2005 with a star schema and then transfer all the data from Access, or will SQL make a star schema for me when I transfer the data from Access.
2. How does one clean data during the ETL transfer process from Access to SQL. Do SSIS allow the user to do this? How?
I am trying to connect through ODBC connectivity, but it will not allow me to do so. I have investigated this matter. It leads me back to the server, because as I was configuring my client side database. It kept asking for the DSN(datasource name), but I was unable to choose one because there wasn't one to choose. Which is my current dilemma, How can I do this and have it available to choose from the server to satisfy the Access database?
I went to the domain where the software resides but I don't know what steps to take? I also found an interesting piece on microsoft about Kerberos, but I can't follow along according to the instructions it has. I have Access 2003 & SQL SERVER 2005, HELP...!
Basically, this is right off the heels of the install. I setup the server without the connectivity, but it is running the current configuration.
I am trying to import a database from Access to SQL Server 2005 and it keeps failing. I don;t want to use anything but the simple screens Microsoft provides for Import/export. Any ideas on how I can make this happened?
- Prepare for Execute (Error)
Messages
Error 0xc0202009: {8A98B034-459D-4200-AD18-555244A05373}: SSIS Error Code DTS_E_OLEDBERROR. 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: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. (SQL Server Import and Export Wizard)
Error 0xc004701a: Data Flow Task: component "Source 64 - DP_ROUTE_DRIVER" (6918) failed the pre-execute phase and returned error code 0xC020801C. (SQL Server Import and Export Wizard)
I'm able to connect to SQL Server 2005 express using SQL Native and see the data and create reports in MS Access 2003. But I cannot change the data, is there an option or permission I forgot? I seen multiple posts that MS Access is not the best tool for doing simple reports. What would be better? I don't want to create a Web Page etc, InfoPath?
Also, I would like to stick with SQL Server Express...
I'm trying to link SQL Server 2000 sp3 to SQL 2005 64 bit. I keep getting Error 17 sql server does not exist or access denined. I tried adding remote user mapping and chaning the linked server properties to "Be made using this security context" without any success. Any help is appreciated.
I have deployed some reports on the reporting server, i have noticed that every one in the domain in which the reports are published can access the reports, except one user. when he tries to access the report he got the following error message or the blank report.
"An error occured during report processing"
"can no read the next data row for the data set dataset1".
Although this user has all the roles on the reporiting server kike browser,content manager etc.
I am trying to read the databases attached to the Sql Server 2005.... my code works fine for Sql Server 2000 but when i try it with Sql Server 2005 it gives me error that "Under Current Settings SQL Server 2005 does not allow remote connections".
Can anybody tell me where to change these settings... i tried my best to find it but :(....
I'm a newbie to SQL Server, trying to make the leap from Access because I'm starting out grow it for my needs. I have a lot of automation stuff in Access where it does a query/code driven process for us via a macro. Sometimes the output is a report, or a form with assembled data that we use. I'm wondering if SQL Server 2005 is capable of doing automation of processes like these as well. Even if it's not necessarily a macro, something I can program, compile and have use off of my SQL Server database to do the tasks that the Access macro did.
In client having existing system is MS Access. So I want to migration MS Access system to SQL server 2005.
Let suppose there MS Access one table having 10 fields.
That table data i need migrate in system (sql server 2005), but the field name are different & sql server few fields are extra suppose 15 fields or vise versa.
So can any one help me out, how to migrate the MS access to sql server system?
Please give me if any one the script or step do this.
I'm new to SQL server. Basically, I am trying to make my Access database accessible on line to my users, so I am transfering it into SQL Server and then writing a web site to use the connection to the database.
My questions are as follows:
1) How can I easily move the structure of the tables (relationships too, but content is not important) from Access into SQL?
2) I have the following query in Access which I don't believe will work in SQL because of the Sum funtion... How can I change this to work in SQL?:
SELECT DISTINCTROW Player.TeamID, Player.PlayerID, Player.SFD, Sum(Payments.Amount) AS Payment, [SFD]-[Payment] AS Due FROM Player INNER JOIN Payments ON Player.PlayerID = Payments.PlayerID GROUP BY Player.TeamID, Player.PlayerID, Player.SFD;
All help will be greatfully received... thanks
"In the face of adversity, I stand on the shoulders of giants..."
If I give the user access dbo on 1 of the database, when he logon using SQL Server Management Studio for running query etc, can he only see his database instead of other as well system database?
Is there any best practise in term of setting the SQL client for 2005?
I am new to databases and need some info that I'm having trouble researching. I just migrated a access database to SQL server 2005 using SQL Server Migration assistant (SSMA). I wondered how SSMA handles NULL values? Also how it treats differences between Access and SQL? Any help would be appreciated.
I am trying to convert an Access database to SQL Server 2005. I have gotten the tables imported into SS2005 but how can I get all my queries imported in? Or is it not possible? Thanks.
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?
I'm having weird issues with connecting to MS SQL Server. Sometimes it can establish a connection and sometimes it can't. This is both with ODBC and with .net code. This happens on multiple computers. I.e. one minute you can connect, the next you can't.
I have a SQL 2000 server which connects fine every time. So I assume the problem is with the upgraded MS SQL Server. I want to be able to resolve this issue before upgrading the main server to SQL 2005.
I am working on a software system which create a database in SQL Server 2005 and then operate on this database. I want the database to be access only from the software system and not from the Microsoft SQL Server Management Studio. How can I achieve it?
I have a problem with connecting to our SQL Server 2005 through Access 2007. I get a msg "ODBC-- failed. [Microsoft][ODBC SQL Server Driver][SQL Server] SELECT permission denied on object 'clients', database 'Contacts', schema 'dbo' (#229)" We have moved some of our tables from Access over to SQL Server 2005. I recently got Access 2007 and I'm the only one at the office that has this problem (everyone else uses Access 2003 or older). I can link tables to SQL Server but I can't open them and I can't open Forms or Reports that has those tables as data source. I can open other objects in Access that are not linked to SQL Server.
I am upsizing a number of Access 2003 databases to SQL Server 2005 with Access Project front ends. Access Tables all upsize OK, some Queries upsize to SQL Views, some to SQL Functions and some get skipped completely. I have fixed up data type issues with Yes/No and Date/Time fields.
I would like all my Access Queries to be SQL Views. Some queries may have calculated fields and I suspect this is one of the issues. The only log in the report states 'Query "QueryName" failed to upsize'. Where should I start looking.
I have a database in MSACCESS 2003 that is 680MB (after compacting) and contains 41 tables.
I tried to import these tables to SQL Server 2005 (express edition) using the :"upsizing wizard" provided in MSACCESS. The wizard works fine but all the tables are not being imported to SQL Server (only 19 tables are getting imported) and the size of the database in SQL Server (the ".mdf file") is 1.03 GB (I have tried using the "shrink" option also) . Further, when I try to open one of the tables (which has 625295 rows and 90 columns) , it takes lot of time to display all records.
So, my problem is do I need to set any options for any of these above issues, I tried to look for any information regarding this but was unsuccessful.
Any help on this will be very much helpful for me!
How can I access my Microsoft SQL Server 2005 through internet?
For example, I set up a Microsoft SQL Server in one of computers of my company, and every computer in my company has its own IP .They are in the same workgroup. Now, there is no problem with database access with another computer in my company.
Can anyone tell my how to access it through a computer out of my company?
We have just upgraded to SQL Server 2005 from 2000. We have an MS Access application that connects and links tables from the new SQL Server database. Users are getting errors when creating records for the first time on the subscriber databases. We have reseeded the primary keys on all the tables to no avail. The only thing we can think of that may be a problem is the version of ADO that we're using as a reference in the MS Access application. We have a reference to ADO 2.5 and don't know whether we should be using 2.8. Any suggestions? Thank you.
Trying to convert my access DB to SQL using 05 express using the upsizing wizard but receiving "SQL Server does not exist or access is denied" [microsoft][ODBC SQL Server Driver][Shared Memory] ConnectionOpen(connect())
I'm brand new to this. My site is currenty running cold fusion with MS Access on a remote hosting server that doesn't allow me to update the DB. They suggested migrating to SQL with CF so I can make changes without bring down the entire server.
I'm trying to make the conversion on my local machine running XP pro. I have just installed the .net framework and SQL server 2005 express. Do I need a different version?
I have just acquired my first root server (Microsoft Windows Server 2003) to run a lightly used ASP application. I installed SQL Server Express 2005 on the server and the application works just fine. The problem is that I cannot access the SQL database on the server from my local computer that is running SQL Server 2000 Developer.
Please note the following: 1) I can access the server database (2005 Express) from a computer that is running SQL 2005 developer. 2) I can access other servers that are running non-EXPRESS versions of SQL 2005 from my computer (which is running SQL 2000)
Thus my question is: Is there some kind of limitation or required setting in SQL Server Express to allow remote connections from SQL 2000?
Remember - I CAN access the database remotely using SQL 2005, just not SQL 2000.
I am using SQL Query Analyzer to try to access the database from my local computer using the same connection parameters that work on my SQL 2005 computer in Management Studio.
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".
Hello! I run an application with a SQL Server 2005 Express edition.
I read under
Start menu --> all programs --> Microsoft SQL Server Express --> Configuration Tools --> SQL Server 2005 Surface Area Configuration --> Surface Area Configuration for Services and Connections --> Database Engine --> Remote Connections
That: "By default, SQL SERver 2005 Express, Evaluation and Developer editions allow local client connetctions only. Enterprise, Standard and Workgroup editions also listens for remote client connections over TCP/IP. Use the options below to change the protocols on which SQL Server listens for incoming client connections. TCP/UP is preferred over named pipes because it requeres fewer ports to be opened across the firewall."
Here I chose the option "Local and remote connections only, using TCP/IP only".
But still, does this mean that other users can't connect to my database since I'm running the Express edition?
If that's the case, could this be changed by using mySQL instead?
Is it hard to transfer a MS SQL Server .mdf database file into a new mySQL database?
I have a sql server 2005 db. Is there anyway I can link access to be used as a frontend by my client for viewing the data? is this possible through linked table manager?
Probably this question has been asked hundreds of times and yet netsearch has not generated satisfactory enough answer, at least, to me.And OK, let's assume your organization has more than 200 employees,just one measure to indicate that it's not small and data processingneeds are quite extensive (for both OLTP and OLAP).We've heard so much about concurrency support, stability andperformance. Are there any real persuasive paper out there to talkabout it? Now, let me also put it in another perspective, say, you'rea Microsoft sql server sales guy or gal for that matter for newaccounts. What you got?Thanks.
I have about 20 Ms Access Databases to need to be upload onto a SQL Server2005.Currently the are on a network/server residing on a specific drive. The goalis to keep the user face the same (ms access) and put all of the tables ontoa SQL Server 2005 BE, and yet make those databases accessible to open/viewthrough Ms Sharepoint.What is the simple (properties) way to grab all of those db's = 20 and uploadto server?With out having to write code.Pull all db's over to SQL then store the Ms Access databases on Sharepointwhere the user face remains the same and the tables are on the server and thedb's are accessible now through the company Intranet.--Message posted via http://www.sqlmonster.com
(Appologies if this group isn't the best place for this post)Is it possible to use DAO 3.6 to access binary data (varbinary(max)) inSql Server 2005? I have images and sound in a Sql 2005 DB that I needto retrieve (and write) with DAO (ADO and ADO.Net are not options asthis is legacy code that can't be changed).Thx,Marcus
