surface some data stored in an Access database via SQL Server, ultimately to be consumed by SharePoint.
I understand how to push existing tables to SQL Server as new tables. In my case, what I am hoping to do is to link an Access query with a table in SQL Server, and have that table be updated each time the query is run (or even each time the database is updated).
Hi,
I'm posting this in the Setup forum as I think the problem relates to my SQL Server Configuration. Being new to server management pls ask for more info if I don't explain this clearly enough, I'm running out of idea.
Here's the set up:
Windows Server 2003 Web Edition SP2
SQL Server 2005 Enterprise Edition SP1
When I try to run apps I get the following message:
SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
I've found some great notes on the TechNet forums explaining this error and how to resolve it by configuring the Surface Area Tool... remote connections etc
But, this is where I draw a blank as I cannot access the Surafce Area Configuartion for Services and Connections because certain components are not installed, or I do not have admin rights. I definitly have the admin rights, but have no idea where to start looking for aforementioned components.
During the SQL Server install I recevied the warning:
SQL Service Edition System Compatibility - Some components of this edition of SQL Server are not supported on this Op system.
It sounds pretty generic and doesn't offer anymore details, but could this be effecting it?
I've also noticed that when I open SQL Server Configuration Manager and click on SQL Server 2005 Services nothing appears in the right-hand panel??? I'm sure there should be???
I've been all over TechNet & Books online and cannot find any reference for what I'm experiencing.
Any help or guidance will be greatly appreciated!
Thanks!
I get this problem when tryoing to connect to the surface area configuration for services & connections
As well as for features.
However I can connect via another computer and change the options accordingly but I just can't do it locally from the server ?
Does any one know why please, the links in the message don't help ?
===================================
An exception occurred in SMO while trying to manage a service. (Microsoft.SqlServer.Smo)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.InnerWmiException&LinkId=20476
------------------------------
Program Location:
at Microsoft.SqlServer.Management.Smo.Wmi.WMIProxy.ExecProcess(Object request)
at Microsoft.SqlServer.Management.Smo.Wmi.WMIProxy.ProcessRequest(Request req)
at Microsoft.SqlServer.Management.Smo.Wmi.WmiSmoObject.EnumChildren(String childTypeName, WmiCollectionBase coll)
at Microsoft.SqlServer.Management.Smo.Wmi.ServiceCollection.InitializeChildCollection()
at Microsoft.SqlServer.Management.Smo.Wmi.ServiceCollection.get_Count()
at Microsoft.SqlSac.MainPanel.FormFeatures..ctor(String machineName, Form callingForm)
===================================
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
Program Location:
at Microsoft.SqlServer.Management.Smo.Enumerator.Process(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.Smo.Wmi.WMIProxy.ExecProcess(Object request)
===================================
------------------------------
Program Location:
at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo)
at System.Management.ManagementObjectCollection.ManagementObjectEnumerator.MoveNext()
at Microsoft.SqlServer.Management.Smo.Wmi.WmiBase.BuildDataTable(ManagementObjectCollection listManagementObject)
at Microsoft.SqlServer.Management.Smo.Wmi.WmiBase.BuildResult(ManagementObjectCollection listManagementObject)
at Microsoft.SqlServer.Management.Smo.Wmi.WmiBase.GetData(EnumResult erParent)
at Microsoft.SqlServer.Management.Smo.Environment.GetData()
at Microsoft.SqlServer.Management.Smo.Environment.GetData(Request req, Object ci)
at Microsoft.SqlServer.Management.Smo.Enumerator.GetData(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.Smo.Enumerator.Process(Object connectionInfo, Request request)
Hmm A tad strange.
I want to send mails from SQL Server 2005 Management Studio Express for which I referred to the article at http://www.databasejournal.com/features/mssql/article.php/3626056. I could successfully do the steps from step 1 to step 6 listed at the above URL. In fact, when I sent a test mail (as cited in step 6), SQL Server displays the message Mail queued though the mail didn't get delivered. The mail server address I am using is smtp.mail.yahoo.co.uk.
But when I expand the Management node in the Management Studio Express GUI, I don't find Database Mail as a child node. In fact, there are only 2 child nodes under the Management node - SQL Server Logs & Activity Monitor.
Why isn't Database Mail listed under the Management node? What am I missing here?
Also when I open Surface Area Configuration & then click Surface Area Configuration for Services and Connections, the following error gets generated:
Computer localhost does not exist on the network, or the computer cannot be configured remotely. Verify that the remote computer has the required Windows Management Instrumentation components and then try again. (SQLSAC)
Additional Information:
The type initializer 'System.Management.ManagementPath' threw an exception. (System.Management)
Unable to cast COM object of type 'System.Management.WbemDefPath' to interface type 'System.Management.IWbemPath'. The operation failed because the QueryInterface call on the COM component for the interface with IID '(38C15AF2-736C-477E-9E51-238AF8667DCC)' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)). (System.Management)
When I click Surface Area Configuration for Features, it generates the error cited above but from the first bulleted list.
How do I overcome this error? I confirmed that Windows Management Instrumentation is running by opening Services from Control Panel ---> Administrative Tools.
I am on WinXP Pro SP2. Please note that I also have SQL Server 7.0 installed in my machine which is a standalone m/c.
Thanks
The operation system is windows xp, and before i installed the SQL SERVER2005 the SQL SERVER2000 had installed.
the course of installing the SQL SERVER2005 has no error at all.
but i can not find the Database Engine under MSSQLSERVER from surface area configuration
==============
thank you in advance for any help
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.
Hello.
View 5 Replies View RelatedI have two databases DB1 and DB2 DB1 has a source table named 'Source' I have created a login 'Test_user' in DB2 with Public access. I have also created a view named 'Test_view' in DB2 which references data from DB1.dbo.Source
How can I do the following: AS A Test_user
SELECT * FROM DB2.dbo.Test_view --Should work
SELECT * FROM DB1.dbo.Source --Should Not work
We have a SQL database that uses Active Directory with Windows Authentication. Can users that are members of the Active Directory group that has read/write access to the SQL database create ODBC connections to access the database directly and update the data? They dont have individual logins on the server. They are only members of the Active Directory group that has a login?
View 1 Replies View RelatedWe have a daily process, which copies millions of rows of data from one DB to another over Linked Server. Just checking on the best practise, are there more efficient ways than the Linked server to copy millions of rows of data from one DB to another? I checked bulk insert but that transfers the data from the file to DB not DB to DB.
View 6 Replies View RelatedHello,
I have a quick question regarding getting data from a SQL database but I am slightly confused as to the best way to handle it.
Basically on of the projects I am working on I need to send data to another company, there are several calls required to the database to bring back various options for changing the questions asked on the front end.
Up to now all that has happened is there has been a mutual agreement between myself and this other company and they have just had access to the database to call a series of stored procs which I have written for them to access the data. Recently however the situation has changed and my client wants me stop them accessing the database however they still need to recieve the information from the database they recieve now.
What will be the best way to handle this? My knowledge of SQL is farily limited presently and I am only ust getting into learning a lot more about it.
Any help and advice would be greatly appreciated.
Kind regards,
Lee
Transfer tables with Data from one database to another one on a same server?
View 10 Replies View RelatedI have a requirement to
a. Read data from Different CSV files.
b. insert and update data to Data base in multiple table using joins.
This execution runs for 1-2 hours.I can use C# with Ado.net, but only concern I see is if in between execution fails due to some connection or other error. All insert data has to be cleaned up again.I feel writing and Store procedure inside transaction, which will take path's for CSV file as input and insert data in database. using transaction we will have flexibility rollback to original state.
I want change all field in database to new datatype.I want change data from Small Integer to Integer but there are the relation in each table.
View 3 Replies View RelatedI would like to start using SQL, how would I convert my data in MS Access over to an SQL Server Database?
View 2 Replies View RelatedI have 5 tables in Academy database. Like
Academy details tables, Academy Students table, Academy Student Parents table, Academy class Section Table, Academy Staff table.
I have created the tables and data into the database. And Now I need to prepare a Excel sheet to upload data into the these tables. How to prepare the Xlsx sheet and how to upload into database without using SSIS package.
I have an access app that has two separate databases the data and app. I would like to convert the data database to SQL 7 when it is released. Does anyone know if there will be any 'wizard' like assistance offered to do this with Access tables. In addition once I have done this how will the app run on against the SQL database? I know to take full advantage of SQL we will need to redesign and recode some of the app queries but will the initial app work on top of the SQL as is?
Any assistance is welcome.
Client is running X- version of application and corresponding database size is huge. Now client's vendor is releasing Y-version of same application with many database schema changes (like new tables added, new columns added, renamed existing columns and etc) To upgrade to the Y-version, vendor is suggesting to my client that down the system and do the upgrade for application/database to Y-version. We are sure that this process will take days together to upgrade to the Y-version. My client is not ready to down the system for that long. So we are trying to find the solution with minimal down time.The approach we are thinking is,
1) Create the replicated database to another server (server2) from production server(server1) using golden gate with X-version
2) Create new tables/schema updated tables from Y-version database on same server1. Here for Updated schema tables we are planning to use the name <table_name_Y_version> as the same table name exists in X-version.
3)With above 2 steps, golden gate replicate the changes from production to server1 and server1 will have the new Y-version table schema (with different concatenate name ' _Y_version'). BTW , there is no affect for the production
4) At this stage we are planning to find best approach, to fill the '<table_name>_Y_version' from X-version tables. two challenges here a) all data needs to be moved to Y-version tables b) they have to sync data in real time.
we thought of going to
a) ssis package to pump the data to Y-version tables, but real time data will not sync.
b) trigger based technique, previous experience said, lot of load
c) thinking about sql replication.
What is they best way to get data from a locked Access database into SQL Server? Linked Server, DTS Package, or should I just directly access it from my App?
Thanks.
Does anyone out there have a good example of a VB Application for importing data from an Access DB into a SQL Server DB using DTS. Any help with this would be greatly appreciated.
Thanks,
Dana
LD
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 Replies View RelatedHi,
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 am trying to delete the row with the minimum date. First I read in the minimum date using:
OleDbCommand myCommand = new OleDbCommand("SELECT MIN(Date) FROM 30_Day_Data", myConnection);
myCommand.ExecuteNonQuery();
OleDbDataReader myReader = myCommand.ExecuteReader();
This then gives me a date in the format "1/2/2008 12:09:33 AM". Since that is the minimum date, I need that row deleted, so I use:
OleDbCommand myDeleteCommand = new OleDbCommand("DELETE FROM 30_Day_Data WHERE Date='" + "myReader().GetValue(0).ToString()" + "'", myConnection);
myDeleteCommand.ExecuteNonQuery();
This gives me an error saying "Data type mismatch in criteria expression". I have no idea how to format the date so that it can get deleted. I have tried nearly everything with no success. Any help is appriciated.
Thanks!
Ross
Hi,
I'm new to VS2005 (vb.net) and here my situation
I have form with a dataset1 (tbl1, tbl2, tbl3, tbl4) pulling data from a Access db. and showing it on the form1(databound)
I need to write what is on form1 to the empty dataset2 in SQL 2005 db
I have created a new DB in SQL 2005 with a Table SQL1 which has the same fields as on form1. Please can some one show me how do I do this. Please
Thanks in advance for your response.
-NM
How can I store my database on the internet and then access it's data (from a Visual Basic Express Windows Application)?
Where on my server do I upload it to?
How do I keep it secure?
Can anyone reccomend any good articles, etc, please?
Thank you for your help.
Sam
I hope I am giving the right information here to get help with this.
I am trying to get information from two tables that are in an access database over to sql 2005. I did this via dts and saved the import options as a file, but when I run this as a job I get the following error: Date 2/27/2008 11:14:08 AM
Log Job History (CombinationTable1)
Step ID 0
Server D86J0PD1
Job Name CombinationTable1
Step Name (Job outcome)
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
The job failed. The Job was invoked by User BIZCHAIRDarleneMurphy. The last step to run was step 1 (CombinationTable1).
I have also tried to run this in the following way:
USE Combination_Table
CREATE TABLE GG Products
AS
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'W:MASTER DOCUMENTSDatabasesChannel_Advisor_Feed.mdb';'admin';'', GG Products)
go
Here I get errors regarding nulls and if I pointed to the correct database, which I have.
Any help with this problem and I hope I have given good information in order to get help.
Thank you
Dee
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]....
Hi,
My task is simple, I want to use the execute sql task editor to grab a value from a database in Access and put it in a variable. The connection is via ODBC and the access database is protected by a password.
I've done all the preliminary stuff such as running profiler to make sure that the package is getting the call to the database, setting up the ResultSet to be "single row" in the general tab, mapped the Result Set correctly, but nothing works. I get the same error every time.
This is my sql command:
select count(FingerPrintID) as FingerPrint
from Employee
Result Set is set up like this:
Result Name: FingerPrint ; Variable Name: User:: varDataset
Here is the error I get:
Error: 0xC002F309 at Execute SQL Task, Execute SQL Task: An error occurred while assigning a value to variable "varDataset": "Value does not fall within the expected range.".
My variable is set up as a Int16.
Please help!!!!!
If you could provide step by step example's that would really make my day.
Thanks,
Lawrence
I'm trying to re-write my database to de-couple the interface (MS Access) from the SQL Backend. As a result, I'm going to write a number of Stored Procedures to replace the MS Access code. My first attempt worked on a small sample, however, trying to move this on to a real table hasn't worked (I've amended the SP and code to try and get it to work on 2 fields, rather than the full 20 plus).It works in SQL Management console (supply a Client ID, it returns all the client details), but does not return anything (recordset closed) when trying to access via VBA code.The Stored procedure is:-
USE [VMSProd]
GO
/****** Object: StoredProcedure [Clients].[vms_Get_Specified_Client] Script Date: 22/09/2015 16:29:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[code]....
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 RelatedHow do I grant a user permissions to only one table in a database. How would it affect him using our Main App which is NAV with regards to his user's permission in NAV
View 2 Replies View RelatedHi,
How would I set permission for SQL Server 2005 "User A" to prevent access to System and other user databases, also How to hide the databases that "User A" has no rights to. I mean, when User A logs in, All other user databases are not visible to him/her.
Thanks,
I am using C# in Visual Studio 2008 and remote database as sql server 2008 R2. I want to read remote database table's field value and i have to move that read value to string variable. how to do it.
And my code is :
string sql = "Select fldinput from tmessage_temp where fldTo=IDENT_CURRENT('tmessage_temp')";
SqlCommand exesql = new SqlCommand(sql, cn);
exesql.CommandType = CommandType.Text;
SqlDataReader rd1 = default(SqlDataReader);
rd1 = exesql.ExecuteReader();