This Is More Of An Access Question, But I Thought I'd Try..
Feb 3, 2004
I have a hunch this one is going to be a resounding "no", but I thought I'd try anyways.
I have a report that uses a user defined date range many, many times throughout the datasource. Ideally, I would like to pass a query declaring and setting variables and let sql server (2000) sort out the dirty work. Essentially I'm working on something that would look like this:
DECLARE @sDate AS DATETIME
DECLARE @eDate AS DATETIME
SET @sDate = 'this string gets constructed during the On Open event of a report
SET @eDate = 'same thing here
SELECT lotsOfStuff, (SELECT oneOfManySubSelects FROM t2 WHERE t2.field BETWEEN @sDate AND @eDate)
FROM somewhere
WHERE somefield BETWEEN @sDate AND @eDate
I have some five subselects that are dependent on this daterange. I can construct the entire string purely in VB, but it's messy and rather tedious. Ideally I'd like to set the variable ONCE at runtime and be done with it. This way, I keep a full record source that calls @sDate and @eDate. Then I simply set the variables and insert them before the query.
The problem is Access doesn't seem to know how to pass the query without trying to parse the variables itself. So it gets mad that @sDate and @eDate haven't been defined for each occurance. I'm looking for a way to make access ignore the fact that there are variables in the query, and pass it as-is to the sql server.
Thoughts?
View 3 Replies
ADVERTISEMENT
Nov 21, 2005
SELECT e.LastName + ',' + e.FirstName + ' - ' + e.EmployeeID AS ListBoxText, e.EmployeeID, e.LastName + ',' + e.FirstName AS FullNameFROM Employee e INNER JOIN EmployeeEval ev ON ev.PeriodID = @Period WHERE (ev.Approved = 0) AND (e.DeptID = @deptID)GOI want to select everyone from the employee table in a dept (determined by DDL) who has either a) had a review and not been approved yet - or b) has not had a review yet ---- all employees are in the employee table -- and all reviews are placed in the employeeeval table.
View 4 Replies
View Related
Apr 8, 2008
So I've been working on this project and I've finally gotten into the program stages. However, I've realised some things that I need to change to some of my stored procedures so that they work the way I want them to inside the program. Anyways I have this stored procedure called "Delete_Minors" and what it originally did was delete any class found in this table called MinorRequiredClasses that matched that minor and was not considered complete. I realised that this would be a bad decision to delete classes in a minor and not consider any other majors/minors a user might have that also require those classes...SO I thought I might be able to come up with a query but it doesn't seem to work. Below are the tables the query/stored procedure deals with as well as the old procedure and the new one (that I attempted to work the way i wanted it to).
Code Snippet
DECLARE @studid int
DECLARE @minorid varchar(50)
SET @studid = 0
SET @minorid = 'Computer Science'
IF (SELECT COUNT(*) FROM Student_Minors sMinors WHERE sMinors.MinorID = @minorid AND sMinors.StudentID = @studid) > 0
BEGIN
DELETE FROM Student_Classes
WHERE StudentID = @studid AND Completed = 0
AND ClassID IN (SELECT minReqC.ClassID
FROM MinorRequiredClasses minReqC
WHERE minReqC.MinorID = @minorid)
AND ClassID NOT IN (SELECT majReqC.ClassID
FROM MajorRequiredClasses majReqC
WHERE majRecC.MajorDisciplineID IN (SELECT sMajors.MajorDisciplineID
FROM Student_Majors sMajors
WHERE sMajors.StudentID = @studid))
DELETE FROM Student_Minors
WHERE StudentID = @studid AND MinorID = @minorid
END
USE [C:COLLEGE ACADEMIC TRACKERCOLLEGE ACADEMIC TRACKERCOLLEGE.MDF]
GO
/****** Object: Table [dbo].[MinorRequiredClasses] Script Date: 04/07/2008 22:49:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MinorRequiredClasses](
[MinorClassID] [int] IDENTITY(0,1) NOT NULL,
[MinorID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ClassID] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_MinorRequiredClasses] PRIMARY KEY CLUSTERED
(
[MinorClassID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MinorRequiredClasses] WITH CHECK ADD CONSTRAINT [FK_MinorRequiredClasses_ClassID] FOREIGN KEY([ClassID])
REFERENCES [dbo].[Classes] ([ClassID])
GO
ALTER TABLE [dbo].[MinorRequiredClasses] CHECK CONSTRAINT [FK_MinorRequiredClasses_ClassID]
GO
ALTER TABLE [dbo].[MinorRequiredClasses] WITH CHECK ADD CONSTRAINT [FK_MinorRequiredClasses_MinorName] FOREIGN KEY([MinorID])
REFERENCES [dbo].[Minors] ([MinorID])
GO
ALTER TABLE [dbo].[MinorRequiredClasses] CHECK CONSTRAINT [FK_MinorRequiredClasses_MinorName]
USE [C:COLLEGE ACADEMIC TRACKERCOLLEGE ACADEMIC TRACKERCOLLEGE.MDF]
GO
/****** Object: Table [dbo].[MinorRequiredClasses] Script Date: 04/07/2008 22:49:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MinorRequiredClasses](
[MinorClassID] [int] IDENTITY(0,1) NOT NULL,
[MinorID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ClassID] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_MinorRequiredClasses] PRIMARY KEY CLUSTERED
(
[MinorClassID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MinorRequiredClasses] WITH CHECK ADD CONSTRAINT [FK_MinorRequiredClasses_ClassID] FOREIGN KEY([ClassID])
REFERENCES [dbo].[Classes] ([ClassID])
GO
ALTER TABLE [dbo].[MinorRequiredClasses] CHECK CONSTRAINT [FK_MinorRequiredClasses_ClassID]
GO
ALTER TABLE [dbo].[MinorRequiredClasses] WITH CHECK ADD CONSTRAINT [FK_MinorRequiredClasses_MinorName] FOREIGN KEY([MinorID])
REFERENCES [dbo].[Minors] ([MinorID])
GO
ALTER TABLE [dbo].[MinorRequiredClasses] CHECK CONSTRAINT [FK_MinorRequiredClasses_MinorName]
USE [C:COLLEGE ACADEMIC TRACKERCOLLEGE ACADEMIC TRACKERCOLLEGE.MDF]
GO
/****** Object: Table [dbo].[MajorRequiredClasses] Script Date: 04/07/2008 22:50:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MajorRequiredClasses](
[MajorClassID] [int] IDENTITY(0,1) NOT NULL,
[MajorDisciplineID] [int] NULL,
[ClassID] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_MajorRequiredClasses] PRIMARY KEY CLUSTERED
(
[MajorClassID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MajorRequiredClasses] WITH CHECK ADD CONSTRAINT [FK_MajorRequiredClasses_ClassID] FOREIGN KEY([ClassID])
REFERENCES [dbo].[Classes] ([ClassID])
GO
ALTER TABLE [dbo].[MajorRequiredClasses] CHECK CONSTRAINT [FK_MajorRequiredClasses_ClassID]
GO
ALTER TABLE [dbo].[MajorRequiredClasses] WITH CHECK ADD CONSTRAINT [FK_MajorRequiredClasses_MajorDisciplineID] FOREIGN KEY([MajorDisciplineID])
REFERENCES [dbo].[MajorDisciplines] ([MajorDisciplineID])
GO
ALTER TABLE [dbo].[MajorRequiredClasses] CHECK CONSTRAINT [FK_MajorRequiredClasses_MajorDisciplineID]
OLD QUERY
DECLARE @studid int
DECLARE @minorid varchar(50)
SET @studid = 0
SET @minorid = "Computer Science"
DELETE FROM Student_Classes
WHERE StudentID = @studid AND Completed = 0
AND ClassID IN (SELECT minReqC.ClassID
FROM MinorRequiredClasses minReqC
WHERE minReqC.MinorID = @minorid)
NEW QUERY
DECLARE @studid int
DECLARE @minorid varchar(50)
SET @studid = 0
SET @minorid = 'Computer Science'
DELETE FROM Student_Classes
WHERE StudentID = @studid AND Completed = 0
AND ClassID IN (SELECT minReqC.ClassID
FROM MinorRequiredClasses minReqC
WHERE minReqC.MinorID = @minorid)
AND ClassID NOT IN (SELECT majReqC.ClassID
FROM MajorRequiredClasses majReqC
WHERE majRecC.MajorDisciplineID IN (SELECT sMajors.MajorDisciplineID
FROM Student_Majors sMajors
WHERE sMajors.StudentID = @studid))
Let me know if you need more information...Hope someone can help!
View 4 Replies
View Related
Jul 14, 2006
I had originally thought a full outer join would solve this, but alas I'm a roadblock. Perhaps it's because it's a Friday!
The goal is to create the most efficient query that will display results of the daily calls and sales as
this:
Date EmpID Calls Sales
7/1/2006 1 20 5
7/1/2006 2 25 7
7/1/2006 3 NULL 1
7/1/2006 4 10 NULL
The problem is a simple full outer join ends up ignoring EmpID 3 who has no Calls in t1, but I still want that row displayed in the results. Any ideas? TIA
create table t1 (Date smalldatetime, EmpID int, Calls int)
create table t2 (Date smalldatetime, EmpID int, Sales int)
insert into t1
values ('7/1/2006', 1, 20)
insert into t1
values ('7/1/2006', 2, 25)
insert into t1
values ('7/1/2006', 4, 10)
insert into t2
values ('7/1/2006', 1, 5)
insert into t2
values ('7/1/2006', 2, 7)
insert into t2
values ('7/1/2006', 3, 1)
View 5 Replies
View Related
Feb 7, 2004
HI FRIENDS,
IS THERE ANY PERFORMANCE IMPACT WHEN I USE "CHAR" AS A DATA TYPE INSTEAD OF USING "INT" FOR RETRIEVING DATA OR FOR SOME COMPLEX QUERY.
THANKS
WITH BEST REGARDS,
DHIRAJ
View 3 Replies
View Related
Oct 6, 2006
I assume that MS has a directive never to change the format of SSIS raw files...
However, what I'd like to know is that when I'm planning long-term systems where I've got backups of data (staging, logging, whatever) using raw files, can I be assured that future versions of SSIS will be able to read those raw files?
I assume a certain level of backwards compatibility, however, I'm just curious if I should think about building processes into my projects that would factor that in and rebuild raw files everytime a new/major release of SSIS comes out.
Phil
View 5 Replies
View Related
Nov 15, 2007
So, Jimmy G helped me out with it in showing a little bit how to do it. SqlCommand command = new SqlCommand(, object>)
SqlParameter param = new SqlParameter();
param.ParameterName = "@return";
param.Direction = ParameterDirection.ReturnValue;
command.Parameters.Add(param);
command.ExecuteNonQuery();
//get the return value
int val = int.Parse(command.Parameters[0].ToString());
Where I get lost is in the declaring of a new sqlcommand and sqlparameter. Can you please spell out where to use this and if I need to change my SQLdataSource. I currently was trying to use it in the OnClick of a button. What I had did the following
Protected Sub CreateIssue_Click(ByVal sender As Object, ByVal e As System.EventArgs) dim returnValue as integer 'how do I get a return value from the stored procedure executed in 'insertissue.insert() here to a variable? InsertIssue.Insert() Response.Redirect("/addarticletoissue") End Sub
again, thank you for your help and patience with such a beginner =)
View 2 Replies
View Related
Dec 29, 2003
I have a production 60GB database set to Full Recovery and every 15 minutes I am log shipping to a Stand by Server .
During the production hours there are no problems but at night when I run DBCC DBREINDEX, the log grows to 22GB and because of this I have a problem sending this over the network to the stand by server.
I tried changing the recovery model to Bulk_Logged but the there is no difference in log file backup size.
AnyIdea
View 1 Replies
View Related
Sep 11, 2007
Two tables:FruitfruitID, fruitNameBasketbuyerID, fruitID(ie. we can see which buyer has what fruit in their basket)I simply want to display all available fruit and whether or not it'sin a specific persons' basket.SELECT Fruit.fruitID, Fruit.fruitName, IsNull(buyerID, 0)FROM Fruit INNER JOIN Basket ON Fruit.fruitID = Basket.fruitIDWHERE Basket.buyerID = 12but this just gives me what's in buyer 12s' basket.What am I doing wrong? Am I a basket case...
View 2 Replies
View Related
Jul 20, 2005
It is my understanding that Views cannot have parameters. Also thatstored procedures can not be queried. My problem is this:I want to select the rows that match a certain parameter.From that I want to select the most current 20 rows (there is a datefield).From that I want to select the lowest 10 rows based on a numericfield.Finally I want that to be input to a report and some calculations.What this basically is the selection for USGA Golf Handicap Index. Itis the most current 20 rounds of golf by a golfer, then the best 10 ofthose 20 and then finally the calculation.Any help would be appreciated.
View 3 Replies
View Related
Oct 22, 2007
Hi
I have a table which has the column [itemNumber] Which contains numbers from 000 to 999. I have another table which has the UPC data for given items
I am trying to get results from my query that will show me every number in the itemNumberSet table that does not already exist (in the substring) of the UPCcode column.
By using the query below i am able to retrieve the opposite, and it works by returning results that do exist in the UPCcode column. But I cannot seem to get it to do the opposite which is what i am after. I figured it would be as simple as using NOT IN but that returned 0 results.
SELECT itemNumber FROM itemNumberSet
WHERE itemNumber IN (select SUBSTRING(UPCcode, 9, 3) FROM itemUPCtable)
ORDER BY itemNumber
Thanks for any suggestions you might have.
J
View 3 Replies
View Related
Mar 17, 2008
Access Connection
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
Any ideas please help
thanks in advance
Dave
View 2 Replies
View Related
Jun 10, 2015
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.
View 6 Replies
View Related
Nov 12, 2015
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?
View 3 Replies
View Related
Oct 30, 2007
Hi guys,
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
Thanks,
CP
View 3 Replies
View Related
Jan 14, 2007
When running the following statement in SQL 2005, I get the error message "Access is denied":
exec master.dbo.xp_cmdshell 'TYPE \SERVER-BSHAREFILE.TXT'
The following are true about the network:
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?
Tim
View 5 Replies
View Related
Feb 22, 2008
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.
Your help is greatly appreciated...
Eric
View 1 Replies
View Related
Jun 23, 2014
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
View 1 Replies
View Related
Mar 25, 2008
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
View 6 Replies
View Related
Sep 10, 2015
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
ssrs2014
View 7 Replies
View Related
Apr 18, 2007
Hi,
I have an application that uses following code:
Code Snippet
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);
SelectProjectID.Parameters.AddWithValue("@Project", m_Project);
try
{
//open the connection
conn.Open();
OleDbDataReader Dataset = SelectProjectID.ExecuteReader();
while (Dataset.Read())
{
m_ProjectID = (int)Dataset["ProjectID"];
}
Dataset.Close();
}
//Some usual exception handling
catch (OleDbException e)
{
throw (e);
}
//
//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);
SelectActionID.Parameters.AddWithValue("@Action", m_Action);
try
{
OleDbDataReader Dataset = SelectActionID.ExecuteReader();
while (Dataset.Read())
{
m_ActionID = (int)Dataset["ActionID"];
}
Dataset.Close();
}
//Some usual exception handling
catch (OleDbException e)
{
throw (e);
}
//
//
//
OleDbCommand Query = new OleDbCommand("UPDATE [tblEntry] SET [tblEntry].[Weeknumber] = @Week,"
+ " [tblEntry].[Year] = @Year, [tblEntry].[Date] = @Date, [tblEntry].[Project] = @ProjectID, [tblEntry].[Action] = @ActionID,"
+ " [tblEntry].[Hours Spent] = @Time, [tblEntry].[Comments] = @Comment WHERE (([tblEntry].[ID]) = @ID)", conn);
Query.Parameters.AddWithValue("@ID", m_ID);
Query.Parameters.AddWithValue("@Week", m_Week);
Query.Parameters.AddWithValue("@Year", m_Year);
Query.Parameters.AddWithValue("@Date", m_Date);
Query.Parameters.AddWithValue("@ProjectID", m_ProjectID);
Query.Parameters.AddWithValue("@ActionID", m_ActionID);
Query.Parameters.AddWithValue("@Time", m_Time);
Query.Parameters.AddWithValue("@Comment", m_Comment);
try
{
Query.ExecuteNonQuery();
}
//Some usual exception handling
catch (OleDbException e)
{
throw (e);
}
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.
What am I overseeing here?
--Pascal
View 13 Replies
View Related
Jan 6, 2008
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:
"Data Source=192.168.2.2;
Initial Catalog=Research;
Integrated Security=False;
Persist Security Info=False;
User ID=usr_research_dalc_www02;
Password=usr_research_dalc_www02;
Pooling=True;
MultipleActiveResultSets=True;
Network Library=dbmssocn;
Application Name="Analytics Data Access Layer""
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.
View 4 Replies
View Related
Nov 28, 2006
Hi,
I am developing an application that uses Access database (mdb file) to store the user data. The user of this application is not interested in the database file (to view in MS Access Environment). Does the user machine requires MS Access installation to run my application or just some couple of dlls (OleDB driver, Access DB Engine,..) should be enough to run my application?
Thanks,
Rao
View 3 Replies
View Related
Aug 18, 2015
I have trying to execute the Sendmail task in my development envinorment i face this error..I have given the clear details error message below,Please have a look.
[Send Mail Task] Error: An error occurred with the following error message: "Failure sending mail. System.Net.WebException: Unable to connect to the remote server System.Net.Sockets.SocketException: An attempt was made to access a socket in a way forbidden by its access permissions ".Alternatively i use the SMTP connection to create the subscription as well,its working properly.
Here we are accessing SMTP connection manager as Virtually.Here in my client network we are using Macafee Anti virus ,We have excluded the Rsconfigration file in the excluded list.I dont know why this problem occures again using Sendmailtask in ssis?
View 2 Replies
View Related
Jan 12, 2000
Hi Everyone,
I have set up a link from ACCESS to a SQL 7.0 database using ODBC (File DSN saved on a shared DRIVE). The link works well only from the workstation where the link was created. But How can I create a link so a group of users can view the linked table in ACCESS without type a password? Any suggestion is appreciated.
Lunjun
View 2 Replies
View Related
Nov 29, 2006
Is there a way to specify a MS Access table (or query object) in the select query of SQL Server.
Ex.:
MSAccessTable (in file.mdb)
col1
col2
a1
a2
b1
b2
SQL query in SQL Server:
SELECT col1, col2 into SqlTable from [file.mdb].MSAccessTable;
Thanks,
View 3 Replies
View Related
May 15, 2007
We migrated a MS Access 2003 mdb into MS Access 2007. The mdb has linked tables to SQL Server via a DSN and utilizes a mdw file. In 2003, the username/password is "passed" to SQL Server, so the UID/PWD that is used for opening the mdb, is used in SQL Server.
Opening the same file in 2007 using the same mdw, gives a secondary login on SQL Server.
Is there a way to have MS Access 2007 pass the UID/PWD to SQL Server on linked tables, the same way that 2003 does?
Thanks!
View 1 Replies
View Related
Apr 22, 2015
When i am trying to start our hospital software based on SQL server 2000, it shows Following Error.Search Condition is not valid, (DBNETLIB) Connection Open (connect()). SQL server does not exist or excess denied. Due to Fetch data.I run our software in Windows 8.1, while it smothly runs in previous version of Windows XP and 7.
View 2 Replies
View Related
Oct 31, 2005
Hello allWould it be possible to migrate the MS Access 2000 to MS SQL Server2000?My application is using MS Access 2000 as database and as userinterface such as forms. Now, I want to migrate the backend databasefrom MS Access 2000 to MS SQL Server 2000. However, I want to keep theMS Access 2000 interface. Would it be possible?If I migrate the MS Access to SQL Server, would the queries, back-endVBA, macro, tables and forms be affected? Do I need to change the MSAccess data type to SQL server supported data type?Which tool I can use to do the migration? Upsizing wizard or exportingthe Access database and then importing it to the SQL server?Thanks in advanceCheersBon
View 4 Replies
View Related
Dec 18, 2006
I am trying to connect through ODBC connectivity, but it will not allow me to do so. I have investigated this matter. It leads me back to the server, because as I was configuring my client side database. It kept asking for the DSN(datasource name), but I was unable to choose one because there wasn't one to choose. Which is my current dilemma, How can I do this and have it available to choose from the server to satisfy the Access database?
I went to the domain where the software resides but I don't know what steps to take? I also found an interesting piece on microsoft about Kerberos, but I can't follow along according to the instructions it has. I have Access 2003 & SQL SERVER 2005, HELP...!
Basically, this is right off the heels of the install. I setup the server without the connectivity, but it is running the current configuration.
View 7 Replies
View Related
Feb 5, 2007
I developed a database with Access 2003 and everything was working good until my tech came in and reformated my hard drive and install a new Ghost image that met our company standards.
Now I cannot go in and make any changes to any of the tables, queries and forms. All of this started when a new Ghost image was installed on my pc.
The message I get when I try to open my database is "You do not have permission to run "tblSwitchboard." I get the same error message when I try to do anything at all on the database.
I am at a loss as to what to do. Please help.
View 1 Replies
View Related
May 11, 2007
I am receiving the following errors while trying to import data from an access database.
[Destination 2 - Amc_C_ARRV [400]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification".
[Destination 2 - Amc_C_ARRV [400]] Error: There was an error with input column "tSympOnsetDate" (494) on input "Destination Input" (413). The column status returned was: "Conversion failed because the data value overflowed the specified type.".
[Destination 2 - Amc_C_ARRV [400]] Error: The "input "Destination Input" (413)" failed because error code 0xC020907A occurred, and the error row disposition on "input "Destination Input" (413)" specifies failure on error. An error occurred on the specified object of the specified component.
The source database has "invalid" dates such as "6/4/199" and "12/23/1899". I am unable to change the data in the source database and have been instructed to load the values as nulls in the SQL destination tables. I tried to do a conditional split using a "ISDATE()" function but that is not a valid function for conditional splits.
What are my options for validating the incoming dates are correct or nulling the dates out that aren't
Thank you in advance,
Tom E.
View 11 Replies
View Related
