I am trying to stablish a shared data source to an Access database for my reporting services. Is this possible? I only see connection type Microsoft SQL Server as Connection Type.. OLEDB is not recognized. How can I achieve this?
View 1 RepliesHi all,
For a flexible import in our SQL database (SQL 2000 on a Windows 2003 server) we use OPENROWSET. In our development on a local instance of SQL Server, we have no problems with a connection to Accesss. However, when we deploy the same code with the same Access database to our test server we get an error indicating to use a Linked Server.
We set all neccessary parameters for allowing ad hoc queries and encounter no problems with queries to different SQL Servers and Oracle databases. Also, when we execute the query to the Access database on a local disk from a job, it works fine. Run the same query to an Access database on a fileshare from a job results in an error.
How come the OPENROWSET selection cannot be run from the query analyser or to an Access database on a fileshare? What security settings are blokking?
We use the following query:
SELECT top 10 *
FROM
OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
'C:Testdata est.mdb';
'admin';'',Table1)
Thanks for all your help,
Nils
I'm a long-time Access developer who is looking to migrate his approach to SQL Server CE 3.5 instead of having all these MDBs. One thing that I do like is using Access to surf through databases; it's a lightweight way to see the data without having to fire up the overhead of Visual Studio etc. Plus there's the QBE grid for easy query design for those of us whose tool-less SQL coding is a bit rusty...
Anyway, I was wondering whether it was possible to "attach" to a SSCE database so you can see it in Access. I've tried but haven't found a way of doing it. Any thoughts? Or is this an intentional lack of capability so we have to use a more robust tool such as Management Studio or Visual Studio?
Thanks in advance!
I want to use SQL Server to query an Access Database with about 40,000 rows of data. If possible, I don't want to upsize the database because others need acess to it in the ACC97 format. Is there a way to use ODBC to connect to the ACC97 database so that I can use the SQL query capability of SQL server to query the database.
I know access allows you to write some SQL queries but I need the power of the SQL server and now it is a matter of curiosity because I've been searching for this answer for about 8 hours.
Hi,
I have been trying to connect to access database from SQL Server 7.0.
This machine is having 7.0 as a default instance and 2000 as a named instance.
Also the machine doesn't have access installed and Microsoft.Jet.4.0 is of version SP8 for Windows 2000
The access database is password protected.
I have tried all, OPENROWSET, OPENDATASOURCE, linked server, and ODBC.
These are some of the commands which I have tried but gives following error.
*******************************************
SELECT a.*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'F:Geerimainilling.mdb';'bhagath';'bhagath', Employee)
AS a
Server: Msg 7303, Level 16, State 2, Line 1
Could not initialize data source object of OLE DB provider 'Microsoft.Jet.OLEDB.4.0'.
[OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]
**********************************************
SELECT * FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'DataSource="F:Geerimainilling.mdb";
User ID=bhagath;Password=bhagath;Jet OLEDB:SystemDatabase="c:WINNTsystem32System.mdw"')...Employee
Server: Msg 7303, Level 16, State 2, Line 1
Could not initialize data source object of OLE DB provider 'Microsoft.Jet.OLEDB.4.0'.
[OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]
**********************************************
SELECT *
FROM OPENROWSET('MSDASQL',
'Driver={Microsoft Access Driver
(*.mdb)};Dbq=F:Geerimainilling.mdb;Uid=bhagath; pwd=bhagath','SELECT *
FROM Employee')
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified]
**********************************************
I have tried all possible combinations but most of the time I come up with Error 7399 or 7303
In case if anyone has some other syntax and successful with that, please let me know
Regards
Jay
I have been tinkering with connections to SQL Server 2000 from Access 2000 for a couple of days without much luck.
My most recent attempt is the following code to the click event of a command button on a form. Most of this code was acquired from other places on the net.
Dim oConn As ADODB.Connection
Set oConn = New ADODB.Connection
oConn.Open "Provider=sqloledb;" & _
"Data Source=Hed001;" & _
"Initial Catalog=My_Views;" & _
"User Id=;vel007" & _
"Password=driver"
Dim oRS As ADODB.Recordset
Set oRS = New ADODB.Recordset
oRS.Open "Select * from VOrder_Header", oConn
Amazingly, it does not error out. But what do I do next to see the records in VOrder_Header?
Just a newb blazing at trail here in the unknown...
I have been using Access 2000 for quite some time and am trying move the application to use SQL Server 2005 Express. Is there any way to connect directly to an Access MDB using SQL Server 2005 Express, or can it only connect to an MDF?
View 3 Replies View RelatedIs there any way that I can connect an Access 200? front end through .NET?
View 1 Replies View RelatedI have an Access 97 database that I connect to a SQL Server 2000through ODBC. I have to manually set up the ODBC connection on eachuser's machine if I want them to be able to use the application though.Is there a way to store the connection string within the Access DB soI don't have to touch the ODBC settings on each user's machine? Or isthere a different solution that I'm missing? There are way too manyfor me tosetup. Thanks in advance.
View 1 Replies View RelatedMost of our users are using MS Access project to work with our data in SQL Server 2005. The problem we have is that in order for them to access the tables they need to be given db_datareader and db_datawriter permission on the database. Our goal is not to give access to the tables themselves but to views. Therefore, we've created several views and placed them into a particular schema. Now we want to give access to that schema, but when we assign select, delete, insert, and update to that schema user in ms access project aren't able to view these tables. When we give them db_datareader permission they are able to see the views but in parentencies does not show the correct schema; therefore, when you try to open that view it errors out saying it doesn't exist. Also since they now have db_datareader they are also able to access the other view and tables in the database.
What we are looking to do is give our Access Project users the permission to link to SQL Server 2005 views by schema only.
Hello,
Only the server administrator user can connect to SSIS from remote workstations. All other users encounter "Access denied". What security settings are necessary to permit regular user access to SSIS? Firewalling is not an issue. SSIS is running and works fully for the server administrator user. All users can manage the Database with SSMS just fine; only SSIS cannot be accessed by regular users.
Server Configuration Notes:
Windows Server 2003
SQL Server 2005 Standard
Workstation Configuraiton Notes:
Windows XP Professional
SSMS (installed from the SS2005 media)
Thank you.
I have set up an MSDE SQL Server in my Win XP Pro PC and am able to connect to it on the host PC, and over my LAN. However, when I try to connect to it over the Internet my connection is refused with the following message:SQL server does not exist or access denied. ConnectionOpen (Connect())Some background:I have router/NAT firewall and have opened port 1433 (I have even tried a DMZ to the SQL Server machine). The Router is definitely going to the SQL Server PC! I am fairly certain that the connection string is correct.Any help would be most gratefully received.
View 7 Replies View RelatedAs a database developer, I have so many databases that I "own" scattered across various servers that it''s getting difficult to remember where all of my databases reside. It doesn't work that the DBAs have taken to some very hard to remember server naming conventions.
I was going to create aliases via the configuration manager, but it turns out the DBAs overwrite my entries each night with THEIR aliases and they won't add any for my use.
So I decided to simply add some records to my host file so that instead of having to connect to "SERVER-AD_DADF-DAFDASS" I can just use "CustomerA".
This solution seems to work until I tried to access a linked server. If I connect to the main server via it's actual name, I can hit the remote/linked server with no issue. However, if I connect to the main server using it's alias, connection to the remote/linked server fails with: Login failed for user 'NT AUTHORITYANONYMOUS LOGON'.
The linked server is set up to use current security context and I'm not logging into the alias any differently that I do when using the actual server name.
First time I've tried doing this - I have SQL Server 2012 installed on my local machine with an instance running (which was set up under an administrator account, not mine, which has no admin rights), and I'm trying to create a linked table from Access (also on the local machine) to a table on the server.
I tried creating a DSN using Windows Authentication, using the server name which is the same as my computer name, and got the error "Error 18452, Login failed. The login is from an untrusted domain and cannot be used with Windows authentication".
I then created a SQL authentication user ID and password, and tried the same thing using that instead, and got "Login failed for user <myusername>"
Is there some particular setting on the local instance I need to change to allow this kind of connection, or do I need to use something other than the server name to connect, such as an IP address? Remember that I have no admin access on this machine so any solution would have to avoid requiring that.
HI All, forgive me but I am new to SQL Server.
My question is in 2 parts. THe first - Could anyone point me to a tutorial or give me a quick pointer? I want to View my Access 2003 datatables through Server MAngement Studio. I cannot however work this out or find articles on it. Is it possible? Are there any wlakthroughs?
The second Part - Eventually I want to create an SQL database, which is comprised of tables which use (point to) MS ACCESS as there datasource. An unchangeable existing piece of software uses Access as the UI, so I want to leave my data in access, and just set up some pointers from the SQL database tables to read in data from these access tables as when required.
Is that a clear question? Am I making any sense?? PLease let me know as I am scratching my head a lot at the moment!!
Cheers guys,
Anthony
The application pool is running under my account.
Authentication for web site: Windows Authentication is enabled, everything else is disabled.
Authentication for the server: Windows Authentication is enabled, everything else is disabled.
I am an admin on both the server and the SQL instance.
SQL Server version = Microsoft SQL Server 2012 - 11.0.5343.0 (X64)
I am the only record in [mdm].[tblUser]
I have a windows 2008 with SQL Server 2008 R2 VM on Azure. I am trying to connect to the SQL server for the first time using SSMS, but have not been able it. I have a VPN tunnel, so I am connecting using Windows authentication. The error I get back from SSMS is:
Login failed for user 'domainusername'. (Microsoft SQL Server, Error: 18456).In the event viewer I see this error message: Login failed for user 'domainusername'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]
I have done the following:
- created an endpoint for port 1433
- opened port 1433 in the firewall
- Ran the MSSQLSERVER service as the build-in users Network Services, Local System, and Local Service, and as a local and domain administrator, with the same exact result each time.
- I get the same result trying to connect locally or remotely.
- I get the same result trying to connect using sqlcmd.
I am using SQL 2000 (Standard Edition, SP4) and have created a linked server to access a Access MDB file. When I run a simple query against that server, I get the error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: System resource exceeded.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' ICommandText::Execute returned 0x80004005: ].
I am able to connect to this same Access MDB file from another SQL server (SQL 2000 Enterprise Edition, SP4). I have compared the two servers and they both have the same version of MDAC (2.8) and the same version of the Jet OLE DB Provider.
Any idea why this would work from one server and not from the other. Also, I was able to perform the same queries from the first server a week back.
Thanks in advance for you help.
Amir
We have been asked to look into using stored procedures with SQL Server 7.0 as a way to speed up a clients site. 99% of all the articles I have read along with all the books all say Stored Procedure should be used whenever possible as opposed to putting the SQL in your ASP script. However one of my colleagues has been speaking to Microsoft and they said that that they were surprised that our client wanted to use Stored Procedures as this was the old method of database access and that now he should really consider using COM objects for data access as itis much faster. Has anyone got any views on this or know of any good aticles regarding this matter ?
View 1 Replies View RelatedHi all,
In the VB 2005 Express, I can get the SqlConnection and ConnectionString of a Database "shcDB" in the Object Explorer of SQL Server Management Studio Express (SSMSE) by the following set of code:
///--CallshcSpAdoNetVB2005.vb--////
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form1
Public Sub InsertNewFriend()
Dim connectionString As String = "Data Source=.SQLEXPRESS;Initial Catalog=shcDB;Integrated Security=SSPI;"
Dim connection As SqlConnection = New SqlConnection(connectionString)
Try
connection.Open()
Dim command As SqlCommand = New SqlCommand("sp_insertNewRecord", connection)
command.CommandType = CommandType.StoredProcedure
.......................................
etc.
///////////////////////////////////////////////////////
If the Database "shcDB" and the Stored Procedure "sp_inertNewRecord" are in the Database Explorer of VB 2005 Express, I plan to use "Data Source=local" in the following code statements to get the SqlConnection and ConnectionString:
.........................
........................
Dim connectionString As String = "Data Source=local;Initial Catalog=shcDB;Integrated Security=SSPI;"
Dim connection As SqlConnection = New SqlConnection(connectionString)
Try
connection.Open()
Dim command As SqlCommand = New SqlCommand("sp_insertNewRecord", connection)
command.CommandType = CommandType.StoredProcedure
........................
etc.
Is the "Data Source=local" statement right for this case? If not, what is the right code statement for my case?
Please help and advise.
Thanks,
Scott Chang
Hi all,I come up with a problem aleady discussed in some posts especially in the post http://forums.asp.net/t/1235761.aspx. but I got in not finally solved.The main problem is connecting to a database twice which causes errors. So I think this might be the right place to ask my questions. Here ist the problem in short:I'm using VWD on XP-Professional with SQL-Server Express and Reporting Services Express. All with Windwos Integrated Security and User Instance (SQLExpress). In my web-application I had for all Datasources the connect string:Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|mydatabase.mdf;Integrated Security=True;User Instance=True This works fine so far. I also have Reports designed with the Report designer using the same connection string. I can preview the reports and I deployd thre reports successfully. I want to call these report using a report viewer web control.Now the problem:when I call the remote remote report from my web-application, the database cannot be opened because an other process is using the database (see post mentioned above). It seems that this is a problem with attaching the database twice: from the sql-server and from the report-server.So I used now a new connection string without "attaching" the database (in vwd and in report designer):Data Source=.SQLExpress;Initial Catalog=C:inetpubwwwrootmyappAPP_DATAmydatabase;Integrated Security=True Now everything works...but ... only on my machine. On the production machine this does not work. I wonder anyway, why its working on my machine, because I never "attach" the database (there is no connection string with AttachDbFilename and I have not opend the developer and have not opened the sql management studio).On the production machine the concurrent (SQL-Server and Report-Server) connection to database does not work with either connection strings, no matter if i attach the database with sql server manager or not).This all drives me crazy for days now. All I want is to use SQL-server and Reporting- Services with its nice features and not repairing my tools. I thought I have a standard Installation, nothing special, but the standard obviously makes problems.Here finally my question:Does this environment ( web-application with remote reports) with Express edition cause normally no problems?Can I "attach" a database only once? How should I connect with my web application that uses the sql-server connections and also the report-server-connections (attaching twice does not work).What connection strings to use and when (AttachDbFilename or Initial Catalog).Are these problems specific to the Express Editions? I thank you in advance for any help Dieter
View 2 Replies View RelatedI 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 Related
I need to determine the following about the current authenticated Windows domain user who is trying to access a SQL Server via a trusted connection.
1 Has the current user been granted login access to the trusted SQL Server?
2 Has the current user been granted access to a specific database?
3 Is the current user a member of a specific database role such as (DB_ROLE_ADMINISTRATORS)?
Thanks,
Sean
I am very new to web development using Microsoft Visual Studio. (my primary expertise is Framemaker 2003). I am currently going through your ASP.NET 2.0 videos and reproducing the lesson content on my development system. I have Microsoft Visual Studio 2005 Professional Edition. I also have Microsoft SQL Server 2005, Microsoft SQL Server 2000, Microsoft .NET Framework SDK v1.1 and Microsoft .NET Framework SDK v2.0.
I seem to be having difficulties with the SQL interface because when I try to use the Property object from Lesson04, I get System.Web.HttpExceptioon {"Unable to connect to SQL Server database."}. When I subsequently tried to Add New Item - SQL Database from Lesson08, I get a Microsoft Visual Studio error box "Connections to SQL Server files (*.mdf) require SQL Server Express 2005 to function properly. Please verify the installation of the component or download from the URL: http://go.microsoft.com/fwlink/?LinkId=49251"
Any ideas? I know the videos are based on use of the Microsoft Visual Studio Express -- are there significant configuration differences between it and Microsoft Visual Studio Professional that would make these lessons incompatible?
Using web dev and sql 2005 express
I have published web app and database to my server but can,t get connection string to connect with the database.
---------------------------------------------------------------------
Login failed for user ''. The user is not associated with a trusted SQL Server connection.
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: Login failed for user ''. The user is not associated with a trusted SQL Server connection.
---------------------------------------------------------------------
I have created a user in the logins in the sql server security node and give him every permission
going.
I have also created a login in the security node for the database..... and given him all permissions
But I still can,t get the connection string from the web app to connect to the database?
Where am I going wrong
Hi all,
I am new to this field. I am trying to Connect to the database using the following code. The code does not give an error but gives an empty grid view.
Can somebody figure out the mistake?protected void Page_Load(object sender, EventArgs e)
{if (!IsPostBack)
BindGridView();
}void BindGridView()
{
System.Data.SqlClient.SqlConnection sqlconnect = new System.Data.SqlClient.SqlConnection("");//added connection string here
String command = "SELECT * from table";System.Data.SqlClient.SqlCommand sqlcommand = new System.Data.SqlClient.SqlCommand();
sqlcommand.CommandText = command;
sqlcommand.Connection = sqlconnect;System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter();
da.SelectCommand = sqlcommand;DataSet ds = new DataSet();
sqlconnect.Open();
sqlcommand.ExecuteNonQuery();
da.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
sqlconnect.Close();
}
I have a file called upload.vb and I am having a lot of trouble figuring out how to connect to a database that I have created in my App_Data folder called myDatabase.mdf. Here is the code on for upload.vb Imports System
Imports System.Data
Imports System.Configuration
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls
Imports System.IO
Imports System.Data.SqlClient
''' <summary>
''' Upload handler for uploading files.
''' </summary>
'''
Public Class Upload
Implements IHttpHandler, IReadOnlySessionState
Public Sub New()
End Sub
#Region "IHttpHandler Members"
Public ReadOnly Property IsReusable() As Boolean Implements System.Web.IHttpHandler.IsReusable
Get
Return True
End Get
End Property
Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
If context.Request.Files.Count > 0 Then
' get the applications path
Dim tempFile As String = context.Request.PhysicalApplicationPath
' loop through all the uploaded files
Dim j As Integer = 0
While j < context.Request.Files.Count
' get the current file
Dim uploadFile As HttpPostedFile = context.Request.Files(j)
' if there was a file uploded
If uploadFile.ContentLength > 0 Then
uploadFile.SaveAs(String.Format("{0}{1}{2}", tempFile, "Uploads", uploadFile.FileName))
End If
System.Math.Max(System.Threading.Interlocked.Increment(j), j - 1)
End While
End If
' Used as a fix for a bug in mac flash player that makes the
' onComplete event not fire
HttpContext.Current.Response.Write(" ")
End Sub
#End Region
End Class Everytime I attempt to create a database connection I get all kinds Compiler errors. If anybody can help me understand how to connect to my database from this page I would greatly appreciate it. Thanks.
I am new to the visual web development program; however, I have created many applications using Visual Studio Express in Visual Basic. I am trying to figure out how to:1. Define a connection string in VB2. Open a current Connection3. Define a SqlStatement4. Retrieve the information5. Display results in textboxI am basically trying to get a login system set up so that a user can type in his name and password and is then directed to a secure page.When I create a new application and want to do this I would use the following code: Dim cnUsers As New SqlClient.SqlconnectionDim cnUsersString As String = "CONNECTION STRING"Dim UserCommand As NEw SqlClient.SqlCommand = "Select chrUsers, chrPassword FROM tblUsers WHERE chrUsers = '" & txtUserName.Text & "' AND chrPassword = '" & txtPassword.Text & "'" cnUsers.ConnectionString = cbUsersStringcnUsers.Open()UserCommand.Connection = cnUserstxtResults.Text = cnUsers.ExecuteNonQuery()cnUsers.Close() That is a very rough draft of what I would code it as. If you have any suggestions please let me know! - NUStudent-
View 2 Replies View RelatedI have an SQL database setup but i don't know how to connect to it to set up a table. I have a vague idea about connectionstrings and such. I have a server name, a database name, a user id and a password. I would like to set it up so i can just enter the SQL commands and create a table and enter data. But first I need to connect to the database. I
View 1 Replies View RelatedI am moving my site from a shared environment to a dedicated server and SQL is on the same box. How do I connect to my database if it's on the same box?
View 4 Replies View RelatedHi all,
Please help me with the following problem because I am running around in circles.
I have this problem getting a forum online.
Since it is a database related problem which I have been trying to solve at the ClubSite forum I am taking the liberty to come to this forum with this problem.
This is the story:
I have this separate SQL database dotForum2004.mdf in App_data.
After trial and error got it working locally.
But when I put it online at my webserver (On a Stand-alone PC at my jobsite) I get an application error.(From the Club Site application)
This is the connection key which works fine locally. Is there something obvious you can point me at?
<add key="forumDSN" value="Provider=SQLOLEDB; Server=.SQLExpress;AttachDbFilename=|DataDirectory|dotForum2004.mdf;Trusted_Connection=Yes;" />
Thanks in advance,
Lex
For an assignment at school, I need to build an ASPX web page that looks up books in a database. This entails the use of a SQL database, and I am having issues trying to connect to the database.
here is a pic of the error I am getting:
I am using Visual WebDeveloper 2005 Express to do this in, and I can't find any options that pertain to the error above. I looked in the SQL configuration tools that I have installed, and I can't seem to find the correct options that I need.
Does anyone know how to resolve this? If you can provide direction on this, that would be great.