I have about 50,000 data entries to move from MS Access to SQL Server 2005 Express. There is no DTS in the tools. I already have the tables, just need to move the data. Appreciate any and all help.
View 3 RepliesI need to write stored procedure in SQL Server 2000 that moves data from table in SQL Server 2000 to same table in Access.
.mdb file is located on the same computer and I know its location (path).
I have recently upgraded to SQL2014 on Win2012. The Access front end program works fine.
But, previously created Excel reports with built in MS Queries now fail with the above error for users with MS 2013. The queries still work for users still using MS 2007.Â
I also cannot create any new queries and get the same error message. If I log on as myself on the domain to another PC with 2007 installed it works fine, so I don't think it is anything to do with AD groups or permissions.
Does anyone out there know of any utility that can Migrate Queries to SQL Server 7. I have over 400 of them and Dread doing them all by Hand.
Thanks,
-Ray
Hi there everyone, I have written a database system which tracks the performance of working in a shipping company in access. Im now rewriting the system in sqlserver and the only real problem I have found so far is that its difficult to estimate what kind of a server *cpu* & *ram* would be appropraite. The system currently performs transaction on my desktop machine in a second a quickest and 2 at slowest.
There are going to be about 500 users in 3 time zones so there will only really be about 300 max hitting the system in an hour.
I was looking at a Dell Poweredge server with twin P3's and half a Gb or ram would this be a good place to start from?
Any Advice would be great!!!
Steve
Hi All,
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..."
Hi all,
I want to transfer all the data from one of my Ms Access tables to SQL Server table, using C#.
How can i do it?
Thanks in advance,
J.Jasmeeta.
Hi all,
I have an Ms Access table and a MsSql table. I am running a windows service in my localhost where the data from Ms access table will be copied to Ms sql table for every one minute. Before copying the data, the Ms sql table will be flushed inorder to avoid replicates.
Now i want to copy only the latest records updated within 1 min in Ms access table, to Ms sql table.
My Ms access table
Name Id
jas 100
meena 101
viji 102
My Ms sql table
Name Id
jas 100
meena 101
viji 102
After 1 min, say 2 records are added to my Ms access table like,
Name Id
jas 100
meena 101
viji 102
bhuvana 103
pinky 104
Now i want to insert only the latest records from Ms access to Ms sql like,
Name Id
jas 100
meena 101
viji 102
bhuvana 103
pinky 104
how to do this? thanx in advance.
Jasmeeta.
Hi,
I'm new to SQL server, and I would like to achieve below tasks. Kindly
provide brief guildline on how to achieve these:
1.) I have managed to schedule job in SQL server to download the access database from remote server.
2.) Second, I would like to transfer all the data from my acccess database to my SQL tables.
May I know how do I achieve my task no 2???? I believe I need to write some script (procedure) in order to achieve this.
pls advise....
I have been restoring database backup from server1 to Server2 and both database name is same till today. But I had an error today. I verified path, location and access everything is there but no luck to resolve.
CODE:-
USE Master;
GOÂ
SET NOCOUNT ON
-- 1 - Variable declaration
DECLARE @dbName sysname
DECLARE @backupPath NVARCHAR(500)
DECLARE @cmd NVARCHAR(500)
DECLARE @fileList TABLE (backupFile NVARCHAR(255))
DECLARE @lastGEFullBackup NVARCHAR(500)
DECLARE @backupFile NVARCHAR(500)
DECLARE @GEFullPath NVARCHAR(1000)
[code]....
Hello all,I'm a total newbie with SQL Server 2000 and I have a little problem whenmoving a database form Access 2000 to SQL Server 2000.In the Access database, each table has an auto-increment field.After importing the tables in SQL Server, all the auto-increment fieldsare turned into "int" type fields.Does anybody have an explanation for that mystery?Thanks in advance,Yan
View 3 Replies View Related
I am a new in .Net Environment. I am moving from VB to VB.Net and Access DB to SQL Server 2005. Please reply me the following questions bellow.
Access Works
SQL SERVER (SS)?
When I create .MSI file it include ADO library in that executable file and my client install software and don€™t need any kind of file to install and wherever my program install it can be accessed by using following connection string.
Con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "dbPAYROLL_DB.mdb;Persist Security Info=False" Con.Open
What file needed on client€™s PC to access SS on server.
What about connection string change dynamically in client sides
I create relation on Access Relationship Diagram.
Where to create these diagrams either on VB.Net Server Explorer or on Management Studio? And how?
Please answers me this basic questions further I have more question in mind but please first answers me these questions€¦
I want to import the data from specific Access Database and Table to SQL Server, using SQL Script. I am trying to implement the solution as given in this link. URL....Here is the code that I have tried -
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
[code]...
The access database file path is - 'C:SQL ProjectTestDB1001.mdb'.The Table from which I want to import the data is - [Table1001]. but when I run this script, I get this error -9 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "MSAccessConnect" reported an error. Authentication failed.
Secondly I need to make 2 more changes to the code posted above.
1.If some access database .mdb, has got password protection, then how to include the password in the script, so that one does not have to manually feed in the password during data import work.
2. How to limit the data that is to be imported from the table [Table1001] by including a WHERE Clause, like for example - SELECT * FROM [Table1001] WHERE xdate = '2015-9-16 00:00:00.000')
hi,
I have an asp.net application which queries an sql server in some other domain and populates a grid with the results.
i am using sql server authentication and my connection string is as follows:-
Dim connectionString As String = "server=emr01; user id='private'; password='private'; database='NGEMRDev'"
i have also used the following tag in web.config:-
<identity impersonate="true" />
This works fine on my development pc i.e windows xp with sp2 .
but when i tried running the same application in windows 2003 the query gives the following exception:-
Server Error in '/TempDel' Application.
SQL Server does not exist or access denied.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: SQL Server does not exist or access denied.Source Error:
Line 77: dataAdapter.SelectCommand = dbCommand
Line 78: Dim dataSet As System.Data.DataSet = New System.Data.DataSet
Line 79: dataAdapter.Fill(dataSet)
Line 80:
Line 81: Return dataSetSource File: c:inetpubwwwroot empdelemrtempdel.aspx Line: 79 Stack Trace:
[SqlException: SQL Server does not exist or access denied.]
System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +472
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372
System.Data.SqlClient.SqlConnection.Open() +384
System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState) +44
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +304
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +77
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +38
ASP.EmrTempDel_aspx.MyQueryMethod(String username) in c:inetpubwwwroot empdelemrtempdel.aspx:79
ASP.EmrTempDel_aspx.Button2_Click(Object sender, EventArgs e) in c:inetpubwwwroot empdelemrtempdel.aspx:89
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +58
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain() +1292
Version Information: Microsoft .NET Framework Version:1.1.4322.2032; ASP.NET Version:1.1.4322.2032
Please help me as to how do i successfully implement my application in the windows 2003 server. i have given the aspnet user on the win2003 admin rights.
How to move some tables with data & procedures etc from 1 database to another in sql server 2005 express edition.
i did by scripting but i transfer tables and procedures and not data
data is the problem.
tnx
I have to export data from SQL Server 2005 express to Access database. I have done many import/export using DTS package via SQL 2000. I don't have BI installed in my SQL SERVER 2005 Express. I understand that I have to use SSIS for sql server 2005. Any help is greatly appreciated.
View 1 Replies View RelatedI'm having an issue to restoring database from prod to report server. I'm getting following error.
When I did Manually I got first error as below.
Msg 233, Level 20, State 0, Line 0 A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)
This is the second error
Msg 3044, Level 16, State 1, Line 37
Invalid zero-length device name. Reissue thestatement with a valid device name.
Msg 3013, Level 16, State 1, Line 37
RESTORE DATABASE is terminating abnormally.
Msg 5011, Level 14, State 5, Line 45
User does not have permission to alter database 'XeP', the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 45
ALTER DATABASE statement failed.
Script Which I used.
USE Master;
GOÂ
SET NOCOUNT ON
-- 1 - Variable declaration
DECLARE @dbName sysname
DECLARE @backupPath NVARCHAR(500)
[Code] .....
Hello.
View 5 Replies View RelatedI have a Windows Server 2012 R2 2 node cluster with SQL Server 2014 FCI installed. Data files are on a separate Windows Server 2012 R2 file server. Data files share has been permissioned to the SQL Server service and SQL Server Agent service accounts as Full Control. NTFS Permissions are Full Control.
When I try to attach a database
CREATE DATABASE AdventureWorksDW2012
ON (FILENAME = 'apricotmssql_VIOLETMSSQL12.MSSQLSERVERMSSQLDATAAdventureWorksDW2012_Data.mdf')
FOR ATTACHI get this error:
Msg 5120, Level 16, State 101, Line 4
Unable to open the physical file "apricotmssql_VIOLETMSSQL12.MSSQLSERVERMSSQLDATAAdventureWorksDW2012_Data.mdf". Operating system error 5: "5(Access is denied.)".
If I log into the file server (called APRICOT) and look at the NTFS permissions they all look good. I have also reapplied the NTFS permissions from the root folder down.
EDIT
If I log on to one of the nodes in the cluster as the SQL Server service account and navigate to apricotmssql_VIOLETMSSQL12.MSSQLSERVERMSSQLDATA and copy and paste the data file, it works fine.
EDIT2:
If I log on to the file server and Enable Inheritance at the root level, then Replace all child objects with inheritable permission entries from this object, I get this error:
User Account Control settings on all nodes and the file server are set to Never notify
I am trying to move a database which I wrote in SQL Server 2005 to a SQL Server 2000 database.
I'm not sure the best way to do this.......
Can anyone enlighten me?.....
I have a SQL Server 2005 Express database on my local machince called OpenAssess.mdf. The server we host with has a file extension of .mdb. How can I go about getting my database to the server? I tried changing the extension to mdb on the local machine but then it tried opening the database in MS Access and didn't work. I just need to connect to the database in my web pages. Here is my connection string and then the error which is visible at the botton of openassessment.org.
*********************************************connection string*************************************************
OPEN_Conn = "Provider=SQLOLEDB;Data Source=connectionToHostServer;Network Library=DBMSSOCN;Initial Catalog=OpenAssess.mdb;User ID=myuserid;Password=mypassword"
*****************************************************error*********************************************************
Microsoft OLE DB Provider for SQL Server error '80004005'
Cannot open database requested in login 'OpenAssess.mdb'. Login fails.
hi guys
i got few questions. i need to move a databases from old server to new server. old server has 25 user databases total datafile around 300GB. heres the questions.
1. what is the fastest way to move all the data to new server? and what's the best way to do it? (DTS? MDF copy over/attach? bak copy over/restore? other theres other way?)
2. i got alot of job and user need to transfer over. i know DTS can do the user.. how about the jobs?
3. do i need to move the master and msdb over too?
4. beside all 3 questions above... do i miss anything that need to be move too?
Thanks guys!!!!!!!!
Hi,
I have a database that resides on a server that`s become too old. It spans
2 devices that sit on 2 different drive letters - D: and E:.
I bought a new server, installed and configured NT4.0 and SQL6.5.
Initially I restored the database to the new server from a dump .It worked
fine, however all the login information was lost. Then I tried partitioning
the new server the same way as the old one and copying all the DAT files
over. Thus I was hoping to retain the information contained in the MASTER.DAT,
drop the database, repartition to create 1 physical drivewith 1 database device,
and restore from a dump to it.
After copying the DATs, though, SQL Server would not start.
Help Please!
I am not a SQL person. I want to copy/move a SQL datbase from old server to a new server and eventualy bring the old server down. What is the procedure to do it. Thanks in advance.
Navid
Hi all. I know that since you know nothing about my database it will
be a stretch to answer this - but does anyone have a boiler plate
checklist for moving a database, dts packages, agent jobs, etc...from
one server to another? Maybe something that just says I should at
least do x,y,z.
Also if anyone out there has any hard won experience/advice about
gotchas when doing this I would love to hear it. We have outgrown a
server and are installing a new one this week that we need to move
over to.
Thanks!
Sql Server 2000 is running on machine havivng two HDD. The HDD where SQL Server resides is reaching capacity. Only 3Gb is remaining. I want to move SQL Server database to other hard drive so that space problem would not occure. How to do it?
View 1 Replies View Relatedi need help moving a database to a new server.
I have an existing database on a Windows XP Pro box running MS SQL Server Desktop Engine 8.00.
I want to move this database to another machine running Windows Server 2003 Small Business Server w/full SQL Server (new edition).
I can see the database, and the directory structures are almost identical on both machines. But when I simply copy the data over nothing works. Obviously to me I must somehow back up the original database and restore it somehow on the new machine.
Any tips on where I begin? I have done searches in the Microsoft Knowledgebase for articles on how to backup and restore from one type SQL to another with frustrating results. No clear directions.
Thanks!
Our current sql server 7 is overloaded and I have been asked to move some data to a new box which is to be acquired. My query is as follows:
1what should be the ideal spec of a sql server to allow for windows 2000 in future. And how do I go about moving data from one to the other. I am new to sql and this is my first assignment.
Thanks
Eve
Our current sql server 7 is overloaded and I have been asked to move some data to a new box which is to be acquired. My query is as follows:
1. What should be the ideal spec of a sql server to allow for windows 2000 in future.
2. Is there any kind of monitoring agent available to see how heavily loaded the server is and what load each application/database is creating.
3. And how do I go about moving data from one to the other.
4. What do I do at the clients end to ensure they are able to access their database.
I am new to sql and this is my first assignment.
Thanks
Eve
I have a server that will be going off-line in a couple of weeks, and in the meantime, I need to backup everything and move it to a new location. This involves a lot of data and databases.
What I have is 33 databases in MS SQL Server 2005. The target machine is MS SQL Server 2008. Each of these databases has a myriad of stored procedures and compiled functions that will need to be moved as well. Essentially the task is to completely replicate the entire MS SQL environment.
Is there a shortcut to making this happen or am I relegated to backing up each database individually and then restoring them individually?
Access 2k -> SQL Server 2kMy client has an app that is A2k FE with A2k BE. They have asked meto move the BE to SQL Server.I have a bit of experience with SQL Server, and I'm happy withscripting the database etc.However, when it comes time to move the data itself, I have a teensylittle concern.Let's take our typical Customers -> Orders relation, where CustomerIDis the Foreign Key in the Orders table.Let's say I have 4 customers, but I used to have 6. The CustomerID isan AutoNum column, and they are 1, 2, 4, 6. However, when I insertthese records into the matching SQL Server table with an Identitycolumn, they will (presumably) be 1, 2, 3, 4.So what can I do about the matching orders? Is there any alternativeto this, which seeems very long winded.1. Insert the records as above, but copy the OLD Access AutoNumcolumn (called OldCustomerID) into a temporary column in the new SQLServer table for both the Customers and Orders tables.2. Insert the Orders records into the new SQL Server table.3. Run an update query thus:UPDATE OSET CustomerID = C.CustomerIDFROM Orders OINNER JOIN Customers C ON O.OldCustomerID = C.OldCustomerIDCan I do this any easier? For example, is there any way in SQL Serverto maintain the values of the AutoNum field in the Insert but havethem still be Identity fields? I know that I can import the AutoNumdata into a simple int column, and once the import is complete, turnON the identity, but is this reliable? Might I lose any dataintegrity?TIAEdward--The reading group's reading group:http://www.bookgroup.org.uk
View 4 Replies View Related
Hi every one,
I'm very new new at this. I'm try to deploy a report model and got this message. I have no idea what its going on about.
Can anyone help me?
Aku
------------------------------
System.Web.Services.Protocols.SoapException: The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled) ---> Microsoft.ReportingServices.Diagnostics.Utilities.RPCException: The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled) ---> System.Exception: The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled) ---> System.Exception: The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled) ---> System.Exception: The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled) ---> System.Exception: The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled) ---> System.Exception: Bad Data. (Exception from HRESULT: 0x80090005)
--- End of inner exception stack trace ---
--- End of inner exception stack trace ---
--- End of inner exception stack trace ---
--- End of inner exception stack trace ---
--- End of inner exception stack trace ---
at Microsoft.ReportingServices.Diagnostics.DataProtection.ProtectData(Byte[] unprotectedData, String tag)
at Microsoft.ReportingServices.Library.ConnectionManager.ConnectStorage()
at Microsoft.ReportingServices.Library.Storage.NewStandardSqlCommand(String storedProcedureName)
at Microsoft.ReportingServices.Library.DBInterface.GetAllConfigurationInfo()
at Microsoft.ReportingServices.Library.RSService.GetSystemProperties(Property[] requestedProperties)
at Microsoft.ReportingServices.WebServer.ReportingService.GetSystemProperties(Property[] Properties, Property[]& Values)
--- End of inner exception stack trace ---
at Microsoft.ReportingServices.WebServer.ReportingService.GetSystemProperties(Property[] Properties, Property[]& Values) (System.Web.Services)
------------------------------
BUTTONS:
OK
------------------------------
Hi
I have to copy (move) a database from one server to another .
One of the way of doing it is to take backup at source server and restore it at destination server .
I am doing it with the following series of statements .
================
At Source Server
================
USE master
EXEC sp_addumpdevice 'disk', 'DBName_Device',
'Irhahadb01F$mssqlackupDBName_Device.dat'
BACKUP DATABASE DBName TO DBName_device
=====================
At Destination Server
=====================
--Copy file (DBName_Device.Dat) from Source backup folder to Destination Backup folder
USE master
EXEC sp_addumpdevice 'disk', 'DBName_Device',
'Irhadvdb02D$mssql7ackupDBName3_Device.dat'
RESTORE DATABASE DBName
FROM DBName_Device
WITH RECOVERY,
MOVE '<Data File Name without .mdf>' TO
'irhadvdb02D$mssql7data<New Data File Name>.mdf' ,
MOVE '<Log File name without .ldf>' TO
'irhadvdb02D$mssql7data<New Log File Name>.ldf'
Above step works fine for one of the test database which small in size.
But it fails for the large database having Data file (.mdf) as 2.5GB and log file as 1.5GB .It says " not enough space on network drive to restore database" . But fact is that I have 200 GB free space on the server .
Please advice .
Sujit
860 520 7454