Hello, I am facing a huge problem in my sql server database using access as a front end.The main problem is trying to execute queries "views" ,since they reside on sql server now,and using variables or parameters in reports and forms to filter on this query. Ex. how can the following be implemented using the same query but in sql server? Access ------ SELECT MAT_Charts.YYYYMM FROM MAT_Charts WHERE ((([Area_Code] & "-" & [GROUP_CODE])=[Reports]![MAT_Chart_C1].[MAT_Key])) GROUP BY MAT_Charts.YYYYMM;
It is specifically this statement in which I am interested: [GROUP_CODE])=[Reports]![MAT_Chart_C1].[MAT_Key]))
Hi, I am having 10 tables in my database. Some times users are complianing that they are not getting permission to one particular table. I checked the table and the owner is dbo only. Why it is behaving like this. the users having all permissions on the database. Pls suggest me.
I think I may be going a little bit mad. Last week I created a database on SQL Server. I then created an access database on a different windows server, and set up an ODBC connection on that windows server to my database on SQL Server. I then set up linked tables within Access to point to my tables on the SQL Server. I'm fairly sure that I opened those links at the time and managed to display and update the data from Access. However I have come to this today and when I go into the Access database and attempt to open one of the links I get a connection failed message. When I check the ODBC connection on the windows server it is fine. Does anyone have any ideas on this? I really need this to be working by tomorrow to do a demo, and I'm so convinced it worked last week I am a little bemused!
Many many thanks in advance of your wonderful and prompt replies!!! :)
We have a view looking at all the columns of a table on a SQL server 7.0 db. When we link this view to MSAccess, we are seeing the old data which was there previously in the original table! not the latest data. We recompiled the table and views and also re-linked the view to MSAccess with no help,we still see the old data only.This is happening only for one of the views not for all.Any help??? sa.
We have several people accessing the SQL server 7.0 data thru' MS Access Server has been slow on queries etc.Everything looks good,other than this one finding of MS Access being used for update/select. Is MS Access a potential problem? How to solve the problem of slow response time?
Note: MS Access is running on the client desktops retrieving data from SQL Server and joining data from MS Access to SQL Server.
What kind of permissions do you need to be able to run a job created by another user or sa if you are not the job owner and don't have any sys admin priveldges??
I send you this message to told hwo can i do to convert one database which is in Access97 to SQL Server6.5. I must create a interface in the web which is able to interrogate the database that the reason why i want to do that. Thanks for any help in advance.
I developed a web application with MS Access there is a destop application that works with MS SQL SERVER 2000. The web application is just meant to allow user to view data and very little modification. I need to transfer from MS SQL Server to another MS Access database on another machine for security reasons and then to the internet server. Please can someone help me on this or any better method to go about this
We have a production Access database and SQL Server database. When I created a DTS package to import data from Access and run manually, it works fine. But when I schedule, it doesn't work, saying some one already opened the file or no permission error.
WE CAN'T get permissions for the Access file to the account that is assigned to SQL Server agent. It's internal policy of the company,because that account is used on all the sql servers to run the agent. Is there a work-around to import data from access. We do have a service account which has access to the Acess db.
Hello everybody I need to a solution to my problem asap. My problem is that i have a sql server 2005 script which is working fine and returning me the correct query. However the same script in access does not return the required query. The Sql server script is as follows:
UPDATE Transactions SET Transactions.EndProductDescription = TB.EndDescription FROM Transactions INNER JOIN (SELECT Distinct EndDescription, ProdBatchNo FROM ((SELECT EndProductCode FROM EndProduct WHERE (Product1 IN (SELECT ProductCode FROM Transactions WHERE (ProdBatchNo = '06122007/000002')))) AS TB1 INNER JOIN (SELECT EndProductCode FROM EndProduct WHERE (Product2 IN (SELECT ProductCode FROM Transactions WHERE (ProdBatchNo = '06122007/000002')))) AS TB2 ON TB1.EndProductCode = TB2.EndProductCode) INNER JOIN (SELECT EndProductCode, EndDescription, ProdBatchNo FROM EndProduct, Transactions WHERE (ProdBatchNo = '06122007/000002') AND (Product3 IN (SELECT ProductCode FROM Transactions WHERE (ProdBatchNo = '06122007/000002')))) AS TB3 ON TB2.EndProductCode = TB3.EndProductCode) AS TB ON Transactions.ProdBatchNo = TB.ProdBatchNo WHERE Transactions.EndProductDescription = ''
So when i put this query in access same as it is i do not obtain the required results. Can someone please help me urgently on converting the above sql script into access query?
Hi I am developing a Windows Service product and use SQLCE 3.1 as data store, I found problem, that, when I inserted a record to sqlce data store from within service, I got an hidden window, the name is ms_sqlce_se_notify_wndproc, Could anyone has idea of how to disable this hidden window? Because my product is running as Windows Service, so this hidden window would be a serious security hole.
Aaaaaarrgghh ! (that's better)I am trying to convert a field within my Oracle 9i Database that is oftype BLOB (but this BLOB may contain a combination of clobs/varchars orimages such as gif images, jpg images) to Microsoft SQL Server 2000using Microsoft DTS.On trying to perform this simple conversion I recieved the error "Needto run the object to perform the operation - Exception AccessViolation" from Microsoft DTS and my table that contains this BLOBfield is not converted across.After further investigation I implemented the fixes suggested by theMicrosoft Knowledge Base and "sqldts.com" but still no joy the errorkept occuring.I discovered my modifying the step in the DTS package that handled thistable conversion that contained the BLOB column that when I changed thedata type on my SQL Server target table to VARBINARY and modified thequery so that only the BLOBs that contained clobs/varchars were broughtacross that the error went away.I then proceeded to create another DTS package step that had a querythat only brought across the BLOB column that contained images such asgif images/jpeg images etc. and the error went away and the target typefor the SQL Server target table was set to IMAGE.As the data for this BLOB contains a combination of VARCHARS/CLOBS(concatanted) and also GIF IMAGES/JPEGS in the same source column withOracle 9i I require the same in my target table within SQL server asone column (and I should be able to do that with type IMAGE especiallyas it can store larger objects than VARBINARY but any source BLOBS thatcontain VARCHAR/CLOBs don't seem to want to be loaded as IMAGE theywill only load in to VARBINARY).However judging by my experiences above this doesn't seem to bepossible ?Can anyone help me out with this ?I am on Microsoft SQL Server 2000 Service Pack 4 with latest MDAC(2.8.1).Cheers,Gary
This morning I can not connect to our SQL Server 7.0 whatever from client or server. The error message which I list below:
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++ A connection could not be estabished to server--Timeout expired Please verfy SQL Server is running and check your SQL Server registration properties and try again. ++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++
We use windows NT authentication. We did not do any change on NT. The SQL Server daily schedule job usally stoped at 10:00AM, but today from the Window NT Task Manager, we can see that the SQL Server is still running untill now.
hi, I have settup up sql mail and did the following: 1. created an E-mail account and configured Out look by creating a pop3 mail profile. tested it by sending and receiving mail, that is ook 2. I Created one domain account for MSsqlserver and Sql Agent service. both services use same account and start automatically in the control panel-services 3. I used the profile that I created in outlook to test the sql mail but got an error: Error 22030 : A MAPI error ( error number:273) occurred: MapiLogon Ex Failed due to MAPI Error 273: MAPI Logon Failed
I really do not know what went wrong. I followed the steps from bol and still having a problem. Am I missing something.
I do have a valid email account I do have a valid domain account I tested outlook using the email account and it worked. so why sql server does not recognise MAPI.
My next question, How to configure MAPI in Sql server if what I did was wrong.
Hi, I have 2 windows 2000 server in cluster with sql server 2000 enterprise edition installed. I have activated the Server-Requested Encryption by using the sql server network utility (Force Protocol Encryption). After this, I have stoped sql server service. But I can't start it at this moment. The error is: 19015: The encrypton is required but no available certificat has been found.
I am getting error in sysindexes when i run dbcc checkdb on a production db. the error is Server: Msg 8928, Level 16, State 1, Line 1 please help me to remove this all the options of dbcc checkdb as well as table are not helping me
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
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.
We need to insert data/rows from a SQL Server 2014 database into MS Access database. The problem is, there are so many columns (100+) in the table and there are so many insert transactions of this kind (from different tables) that it is not very easy to write the code in VB.NET that lists all column names.
Both the Access and SQL Server tables have the same number of columns and the equivalent data types, so inserting is not really the problem. It's just that is there a way to do an insert statement in T-SQL that does not name all the columns?
I've been developing desktop client-server and web apps and have used Access and SQL Server Standard most of the time. I'm looking into using SQL CE, and had a few questions that I can't seem to get a clear picture on:
- The documentation for CE says that it supports 256 simultaneous connections and offers the Isolation levels, Transactions, Locking, etc with a 4GB DB. But most people say that CE is strictly a single-user DB and should not be used as a DB Server. Could CE be extended for use as a multi-user DB Server by creating a custom server such as a .NET Remoting Server hosted through a Windows Service (or any other custom host) on a machine whereby the CE DB would run in-process with this server on the machine which would then be accessed by multiple users from multiple machines?? Clients PCs -> Server PC hosting Remoting Service -> ADO.NET -> SQL CE
- and further more can we use Enterprise Services (Serviced Components) to connect to SQL CE and further extend this model to offer a pure high-quality DB Server? Clients PCs -> Server PC hosting Remoting Service -> Enterprise Services -> ADO.NET -> SQL CE
Seems quite doable to me, but I may be wrong..please let me know either ways
The SQL Server is installed on SERVER-A. SERVER-A and SERVER-B are Windows 2003 servers on the same Windows 2003 domain. The SQL Server and SQL Server Agent services are running under the domain account SQLSERVICE. SQLSERVICE is a member of the Domain Admins group. The Domain Admins group is part of the local Administrators group on SERVER-B. The SQLSERVICE account has also explicitly been given Full Control to the folder referenced by \SERVER-BSHARE xp_cmdshell use has been enabled on the SQL Server.
If I run the following command in SQL:
exec master.dbo.xp_cmdshell 'whoami'the following is returned: DOMAINSQLSERVICE If I change the command to access the c: drive instead of a network drive, it executes successfully.
Can anyone shed some light on why I still cannot access any of the files in this folder using xp_cmdshell?
We have an application that requires write settings to reportserver virtual directory for the IUSR account when anonymous is turned on during the install. Once the install is complete, we lock down the IUSR account so that it only has browse access to the virtual directory when enabling anonymous access.
We automate the uninstall and install of our daily builds and I'm trying to figure out if I can automated this process somehow either through command line utility or in vbs.
i am currently working on designing a database for a bank as a school project for my database class. We have to draw up an entity relationship diagram, Sql tables, database size estimate etc. I am currently working on the security portion of the project. I need to list the groups that have access to my application and use a grid format to show access to specific tables.
I am currently working on designing a database for a bank as a school project for my database class. We have to draw up an entity relationship diagram, Sql tables, database size estimate etc. I am currently working on the security portion of the project. I need to list the groups that have access to my application and use a grid format to show access to specific tables.
Role Loans Payments Transactions Accounts Customer Emplo Database Admin SUID SUID SUID SUID SUID SUID Branch Manager SUI SUI SUI SUI SUI SUI Internal Auditor S S S S S S Loan Officer SUID SUI SUI S S Tellers S S S S SU Customers U
Is there any way to get more information for when IAuthorizationExtension::CheckAccess fails to grant access to a report item for the current user? Specifically, it would be useful to know:
1. URL of attempted report 2. IP address of user agent 3. Identity of current user 4. Date/Time of the failed attempt
using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.OleDb; using System.Collections;
namespace TimeTracking.DB { public class sql { OleDbConnection conn;
// //the constructor for this class, set the connectionstring // public sql() { DBConnectionstring ConnectToDB = new DBConnectionstring(); conn = ConnectToDB.MyConnection(); }
// // // public void UpdateEntry(int ID, string Week, string Year, string Date, string Project, string Action, string Time, string Comment) { int m_ID = ID; int m_Week = (Convert.ToInt32(Week)); int m_Year = (Convert.ToInt32(Year)); string m_Date = Date; string m_Project = Project; int m_ProjectID = new int(); string m_Action = Action; int m_ActionID = new int(); Single m_Time = (Convert.ToSingle(Time)); string m_Comment = Comment;
// //get the project ID from the database and store it in m_ProjectID // OleDbCommand SelectProjectID = new OleDbCommand("SELECT tblProject.ProjectID FROM tblProject" + " WHERE (((tblProject.Project) LIKE @Project))", conn);
// //get the action ID from the database and store it in m_ActionID // OleDbCommand SelectActionID = new OleDbCommand("SELECT tblAction.ActionID FROM tblAction" + " WHERE (((tblAction.Action) LIKE @Action))", conn);
finally { //close the connection if (conn != null) { conn.Close(); } } } } }
Code Snippet
The update statement is not working in my application, no error in C# and no error in ms-access. When I paste the update query into the ms-access query tool and replace the parameter values (@....) with real values, is will update the record.
Re: Possible Vista-only security issue opening a TCP connection to SQL2005
Please help. I have a problem that appears to be rare. I'm testing the culmination of a year's work and the error I'm getting is at the most basic level (the SQL connection) while everything else so far is working as coded.
My client is Windows Vista, apparently no firewall (I say apparently as in recent years MS have put more and more facades over the actuality of the operating systems), my server is Windows 2003 Server b3790 SP2 with SQL 2005 Standard 1399.06, again no firewall.
PortQuery 2.0 reckons that the server is listening on port 1433, too, so it doesn't seem like a firewall issue.
Nothing reported in either Event Viewers.
Here's the stack dump:
Unhandled Exception: System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - An attempt was made to access a socket in a way forbidden by its access permissions.) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject) at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at Robot.Data.Support.ExecuteStoredProcReader(String connectionString, String procName, IDictionary`2 parameters) in P:Visual Studio 2005ProjectsRobotRobot.DataSupport.cs:line 44 at Robot.Data.Analytics.FetchableCollection.ExecuteByIYLT(String criterion) in P:Visual Studio 2005ProjectsRobotRobot.Data.AnalyticsFetchableCollection.cs:line 78 at Robot.Data.Analytics.FetchableCollection.Robot.Core.Interfaces.IFetchable.Fetch(String criterion) in P:Visual Studio 2005ProjectsRobotRobot.Data.AnalyticsFetchableCollection.cs:line 49 at Robot.Data.Analytics.FetchableCollection.Fetch(String criterion) in P:Visual Studio 2005ProjectsRobotRobot.Data.AnalyticsFetchableCollection.cs:line 41 at Robot.Data.Analytics.AnalyticsDalc.Prefetch(String criterion, AnalysisTypeEnum analysisType) in P:Visual Studio 2005ProjectsRobotRobot.Data.AnalyticsAnalyticsDalc.cs:line 167 at Robot.Data.Analytics.AnalyticsDalc.FillRankedCollection(RankedCollection& emptyRc) in P:Visual Studio 2005ProjectsRobotRobot.Data.AnalyticsAnalyticsDalc.cs:line 115 at TestHarness.Program.Main(String[] args) in P:Visual Studio 2005ProjectsRobotTestHarnessProgram.cs:line 35
And here's the connection string, which is built using the SqlConnectionStringBuilder:
I have tried an old project which used to connect to the same server using a more basic connection string (it was a project which populated my Research db with fake data) and it also fails. When I ran this project successfully I'm pretty sure it was on an XP client.
I have also ran my TestHarness .exe on another client (WinXP) and it appears to pass this point and raises an exception that I believe is caused at a later point - so it looks like a Vista client issue.
Thanks in advance for your help,
Luke
P.S. Some losely related thoughts; the MSDN forum system still logs me out on Vista when I try to submit a thread, please sort this out! You can't expect your customers to run around deleting cookies. Also, when will you finally deprecate the default Named Pipes!? And finally, having that "does not allow remote connections" error spewed out with all connection errors is a really bad design choice, in my opinion.
