I would like to call this stored procedure, but I am unable to pass
parameters to the @Start and @End.
Is thier a way to pass parameters to a pass through query from MS
Access?
SELECT COUNT(dbo.tblPersActionHistory.PersActionID) AS [Total Ct],
[dbo].fn_FindStartPayPeriod(dbo.tblPersActionHistory.Pe rsActionID, 2)
AS [Signed PP]
FROM dbo.tblPersActionLog INNER JOIN
dbo.tblPersActionHistory ON
dbo.tblPersActionLog.PersActionID =
dbo.tblPersActionHistory.PersActionID
WHERE (dbo.tblPersActionLog.StatusID BETWEEN 4 AND 7) AND
(dbo.tblPersActionLog.Rejected = 0) AND
(dbo.tblPersActionLog.IsPayAction = 0) AND
(dbo.tblPersActionHistory.ActionTypeID = 5) AND
(dbo.fn_IsParACorrection(dbo.tblPersActionHistory. PersActionID) = 0)
AND
([dbo].fn_ParNotException(dbo.tblPersActionHistory.PersA ctionID) = 1)
AND (dbo.tblPersActionHistory.ItemDTG >= @StartDate) AND
(dbo.tblPersActionHistory.ItemDTG <= @EndDate)
GROUP BY
[dbo].fn_FindStartPayPeriod(dbo.tblPersActionHistory.Pe rsActionID, 2)
Recently we started using Pass throughs to perofmr large inserts, however we have started to notice that some of these pass throughs are executing twice, and therefore duplicating data.
Is this a known bug, and if not has anyone got any advice on what could be causing it?
We're connecting from Access 2002 (SP6) to SQL Server 2000 Enterprise.
The conenction string / code is as follows :-
Dim cmd As ADODB.Command Set cmd = New ADODB.Command Set cmd.ActiveConnection = CurrentProject.Connection cmd.Properties("Jet OLEDBDBC Pass-Through Statement") = True cmd.Properties _ ("Jet OLEDB*** Through Query Connect String") = _ "ODBC;DSN=" & myDatabaseShort & ";DATABASE=" & myDatabase & ";UID=sa;PWD=" & Left(myDatabaseShort, 4) & ";"
It connects fine. My sql string is a straightforward Insert statement that only executes once via SQL Query Analyzer.
I'm calling the pass through using the following lines of code:-
cmd.CommandText = mysql cmd.Execute
Can anyone see anything obvious that I'm doing wrong, or is this a known issue?
One of our developers has a Microsoft Access 2000 database that runs queries that compare the Access db data to a SQL Server database. He uses pass through queries to get the data from SQL Server.
We're finding that the Access query runs quickly against our test server, even with copies of production data, but when we try the same query against our production server, the CPU on the local computer running Access is pegged and the query takes up to 10 minutes to run.
First I verified that the SQL Server structures between test and production were identical, including indexes. I checked index fragmentation, and productions indexes are less fragmented than tests. Again, test and production currently have the identical data.
I've run a profiler trace on our production SQL Server 2000 server, and I see the RPC for the query from Access running almost instantaneously.
Any ideas on what might be the cause of the difference in speed between test and production SQL Server servers, or any suggestions on other things I could look at/tools I could use to troubleshoot this issue further?
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
Environment - Windows 2003 standard server SP1, SQL 2000 SP4 (installed mixed mode)
Goal: To have Crystal reports run via odbc dsn using windows authentication.
Big Honkin' Problem: I should say that if the users are local admins on this server then the windows authentication works just great, if not a local admin then it doesn't work. SQL gives an error access denied. (this shows in the logs btw.. to the end user crystal displays a generic database error) Since windows authentication failed, I then decided to use the dbo and switch to SQL auth. This dbo did not have a password and everything ran great. Then the IT dept. asked me to give the user a password, so I did. Then everytime Crystal opened, a window would appear asking the user to type the database , user and password. In Windows everytime this was done the crystal control files would change ownership to whomever entered the information making the report useless to any other user until I manually went into the file properties and changed it back. I develop these reports thru an VPN connection and on my desktop I said phooey on re-entering the password 50 zillion times while developing so I opened the dsn and put the PWD line in and it ran great. So we figured why not put this on the server - the password does not pass thru to SQL, even as admin user. The final problem that came up in relation to this is that I then created another user in SQL with no password so the reports would run. I developed a report that takes the current user credentials and passes it to the report via dde link. On my machine running over the VPN and launching the database program (GoldMine) over the WAN the report did everything it was suppose to. I high-fived myself and installed it on the server where the users would launch it and the dang thing wont pass the credentials over to the report.
I know there is not much to SQL security - it's either SQL or mixed mode. I have it on mixed mode and I know for fact it runs because Goldmine runs. I have to have some sort of security 'thang' in Windows 2003 somewhere that doesn't allow the passord to move and SQL only sees a blank password.
If any body can give me any help - I would so greatly appreciate it.
I am trying to do the following. I am capturing the logged in username from Windows Authentication then stripping the domain name using the following codeProtected Sub SqlDataSource1_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles SqlDataSource1.Selecting Dim Bareusername e.Command.Parameters("@strusername").Value = My.User.Name Bareusername = My.User.Name.Replace("MyDomain", "") e.Command.Parameters("@LoginName").Value = Bareusername End Sub I use the derived value as the where clause in a select query Select * from Master Where AccountName = @LoginName. This allows a user to only see their own information in DetailsView1. This is working as desired. I have another column in the Master table named ID. This ID is the key that I need to reference to allow users to see and edit data in other tables. So I would like to be able to do something like Select ID from Master where AccountName = @LoginName and be able to pass this value to a Select statement against another table(Awards) for a DetailsView2 on the same page. Alternatively the value that I am trying to query is in the first row of DetailsView1. Is it possible to access the value in that field and pass it in the query for DetailsView2? This would be simpler if possible.I am not sure how to go about this and would appreciate any guidance that you could provide. I have found a lot on the web regarding this based on a selected value but not through the method that I am trying to use. Thanks in advance,Ken
I have the following function that is run when a person enters a value, "Cell", in an access form. I am trying to execute the pass through query in SQL Server but i keep getting the following message:
"The microsoft jet database engine cannot find the input table or query 'CellDefs'. Make sure it exists and its name is spelt correctly."
There is nothing wrong with the table name. I cant understand what the problem is ?????
Can anyone tell me how to execute a query that passes a parameter from access to Sql server.
Thanks, Ruaidhri
Public Function GetData(Cell As String) Dim dbsCurrent As Database Dim qd1 As QueryDef Dim SQL As String Dim rs As Recordset Dim qdf As QueryDef Set dbsCurrent = CurrentDb
Set qd1 = dbsCurrent.CreateQueryDef("") With qd1 .Connect = "ODBC;DSN=MISChief;SERVER=Janeway;UID=sa;PWD=;DATA BASE=MIS" SQL = "" SQL = SQL + "INSERT INTO TableX SELECT A.Name AS CellCode, C.FinderNumber" SQL = SQL + "FROM (CellDefs AS A INNER JOIN Calls AS B ON " SQL = SQL + "A.CellDef_id = B.CellDef_id) INNER JOIN Finders AS C ON " SQL = SQL + "B.Finders_id = C.Finders_id " SQL = SQL + "WHERE A.Name LIKE '" & Cell & "' " SQL = SQL + "ORDER BY C.FinderNumber" .ReturnsRecords = False .ODBCTimeout = 2400
dbsCurrent.Execute (SQL) End With dbsCurrent.Close End Function
suppose i get date from MSsql how do i pass that to dos command is there a way so i can maipulate date and pass on to dos command to run at certain time
just getting started with my first db driven web project... I am using a MySql database with membership and roles and got that working fine. I have tables containing details of courses that users are enrolled on etc. and want to display a list of courses for the user that is signed in, so he can continue lessons on the one of his choice. How do I pass the users name to the database query for a DataList control. So far I have tried lots of variations of the following: <asp:SqlDataSource ID="dsCourses" runat="server" ConnectionString="<%$ ConnectionStrings:xxx %>" ProviderName="<%$ ConnectionStrings:xxx.ProviderName %>" SelectCommand="SELECT c.CourseName FROM courses c, enrolments e WHERE c.CourseID=e.CourseID AND e.Username='<% =User.Identity.Name %>'"></asp:SqlDataSource> <asp:DataList ID="DataList1" runat="server" DataSourceID="dsCourses"> <ItemTemplate> <asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl="Lesson.aspx">'<%# Eval("CourseName") %>'</asp:HyperLink><br /> </ItemTemplate></asp:DataList> </p> However, the <% =User.Identity.Name %> doesn't work here (but it does elsewhere on the page) - not sure why?? The code works fine if I hard code a user name into the select statement. Suggestions pleeeeeese!!
I have an Access 2003 front end with a SQL Server 2005 Express backend. I was thinking of using pass thru queries as row sources for some combo boxes such as states/countries for addresses. My question is do pass thru queries, when used as a row source, keep a connection to the DB server? Or do they get the data, disconnect and populate the control?
I realize I could populate the controls with code, but this seems less hassle and will overcome the ValueList size limit if needed.
I don't know how to pass parameters required (dates) to a query1, if I'm using a subquery (query2) which is using the results of query1, but I'm not showing that field on that subquery (query2)
Example
table1 id - autonumeric id_user - id from user dates - date of register
table2 id - user id name - user name
query1 SELECT Table1.id_user, Count(Table1.id_user) AS CuentaDeid_user FROM Table1 WHERE (((Table1.datess) Between [begining] And [ending])) GROUP BY Table1.id_user ORDER BY Table1.id_user;
subquery (query2) SELECT query1.id_user, query1.CuentaDeid_user, Table2.name FROM query1 LEFT JOIN Table2 ON query1.id_user = Table2.id;
This is just an example, the think is that I want to know that if it's possible to pass the parameters requested in query1 from the SQL of the subquery (query2)?
I am sure this has been asked a thousand times before in a thousand different ways, but here goes...
I am in need of the ability to prompt users for a From Date and To Date to insert into a WHERE clause in SQL queries. What is the easiest least painful way of accomplishing this? A great many of our customers utilized SQL Server Express or MSDE, so they do not have the full gambit of SQL tools handy. Believe me when I say that I have searched high and low across the internet for this solution. I was hoping I might be able to accomplish this through VB Script or J Script. Or, anything reasonable.
How do I pass the results of a sql 'FOR XML' query to an XML Task? I tried using a user variable of type string. In my Exec SQL Task on the General tab I set the ResultType to XML. Then on the Result Set tab I map it (Result 0) to my user variable (string). Then in the XML Task I use that variable as the Source. However, it fails in the Exec Sql Task with error "End tag 'CustomJob秱孒秱怈пField' does not match the start tag 'CustomJobField'
It looks like some extraneous chars are getting inserted into my tags? I also tried setting the ResultType to SingleRow but that failed with error:
"The type of the value being assigned to variable "User::ForXmlResult" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ".
Im new to SSRS, basically I have created and deployed a report to my reporting server.
Now I have a query parameter on my report. From my application, I can access the report manager, and here it asks me for my variable. I'm not sure if my question is a vb.net one, but I was wondering if anyone knew how I could pass my query paramater to SSRS at runtime. Basically it would be easier for my users if they could just see the report they are interested in, rather than having to type the variable in to be able to see the report.
how to pass array(query parameter) into stored procudure. with this array i need to retrive data,with another array i have to retrive another set of data. eg: suppose @param1 contains chapter1,Chapter2,Chapter3, @param2 contains unit1.1,unit2.1,unit2.2,unit3.1,unit3.6 how can i do in stored procedure
Im new to SSRS, basically I have created and deployed a report to my reporting server.
Now I have a query parameter on my report. From my application, I can access the report manager, and here it asks me for my variable. I'm not sure if my question is a vb.net one, but I was wondering if anyone knew how I could pass my query paramater to SSRS at runtime. Basically it would be easier for my users if they could just see the report they are interested in, rather than having to type the variable in to be able to see the report.
I'm having problem with an OpenQuery statement in stored procedure, which should be run on FoxPro linked server. I'm getting either an error messages or not the result I expected. I know that the problem is with WHERE part, without this part it works.
Here is the code: ------------------------------------- DECLARE @LastDate datetime SELECT @LastDate = MAX(DateChaged) FROM tblPersonel WHERE ACTIVE IN( 'A', 'T')
1. I tried: SELECT * FROM OPENQUERY(FoxProLink, 'SELECT ACTIVE, EmpNumber FROM tblPersonel WHERE DateChanged >=''+@LastDate+''')
This line gives me an error msg:
Could not execute query against OLE DB provider 'MSDASQL'. [OLE/DB provider returned message: [Microsoft][ODBC Visual FoxPro Driver]Operator/operand type mismatch.]
2. I tried to use CTOD() - FOXPRO function to convert character to date.
SELECT * FROM OPENQUERY(FoxProLink, 'SELECT ACTIVE, EmpNumber FROM tblPersonel WHERE DateChanged >=CTOD(''+@LastDate+'')')
-this doesn't give any error, but doesn't limit the result set as it should.
We already used Oracle Datasatage Server the following Query statement for Source and Lookup.here there is parameter maping in the SQl Statement . How can achive in SSIS the Folowing Querystatment?
Query 1: (source View Query) SELECT V_RDP_GOLD_PRICE.GDR_PRODUCT_ID, V_RDP_GOLD_PRICE.ASSET_TYPE, V_RDP_GOLD_PRICE.PREFERENCE_SEQ, V_RDP_GOLD_PRICE.RDP_PRICE_SOURCE, TO_CHAR(V_RDP_GOLD_PRICE.PRICE_DATE_TIME,'YYYY-MM-DD HH24:MI:SS'), TO_CHAR(V_RDP_GOLD_PRICE.REPORT_DATE,'YYYY-MM-DD HH24:MI:SS'), V_RDP_GOLD_PRICE.SOURCE_SYSTEM_ID FROM V_RDP_GOLD_PRICE V_RDP_GOLD_PRICE WHERE REPORT_DATE = (select max(report_date) from V_RDP_GOLD_PRICE where source_system_id = 'RM' )
Query 2: (look up )
SELECT GDR_PRODUCT_ID, TO_CHAR(MAX(PRICE_DATE_TIME),'YYYY-MM-DD HH24:MI:SS') , TO_CHAR(REPORT_DATE,'YYYY-MM-DD HH24:MI:SS') FROM V_RDP_GOLD_PRICE where GDR_PRODUCT_ID = :1 and report_date = TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS') AND PRICE_DATE_TIME BETWEEN TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS') - 7) AND TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS') GROUP BY GDR_PRODUCT_ID, TO_CHAR(REPORT_DATE,'YYYY-MM-DD HH24:MI:SS')
please anyone give the sample control flow and how to pass the parameter?
I have a function in VBA (Access) that passes a sql string into a passthrough query that is server side executed
I changed the inner joins and left joins to where statements but this particular sql string crashes with the titled error folowed by the multi-part identifier "task_backup.activityid" could not be bound (#4104) and then it repeats that one more time in the same message
Which i think the second "sub message" is for the null criteria
INSERT INTO NewTasksBeingAdded ( [Activity ID], [WBS Code], [Activity Name], Area, Line) SELECT [TASK Excel Data].[Activity ID],[TASK Excel Data].[WBS Code], [TASK Excel Data].[Activity Name], [TASK Excel Data].Area, [TASK Excel Data].Line FROM [TASK Excel Data] where [TASK Excel Data].[Activity ID] = TASK_BackUP.[Activity ID] and TASK_BackUP.[Activity ID] Is Null
I am trying to execute an SSIS package from an MS Access 2003 database that imports a table from the Access database into a target table in SQL 2005. I saved the package in SQL 2005 and tested it out. If I run it from the Management Studio Console with Run->Execute ... everything works just fine. However, if I try to run it using the following line command "Exec master.dbo.xp_cmdshell 'DTExec /SER DATAFORCE /DTS SQL2005TestPackage /CHECKPOINTING OFF /REPORTING V'" the execution will always fail when the Access database is open (shared mode). The connection manager looks like this: "Data Source=E:Test.mdb;Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Jet OLEDB:Global Bulk Transactions=1". The error is listed below:
Code: 0xC0202009 Source: NewPackage Connection manager "SourceConnectionOLEDB" Description: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Could not use ''; file already in use.".
Is it possible to use a parameter in a ldap query using the ADsDSOObject provider? I keep getting an error "The ICommandWithParameters interface is not supported by the "ADSDSOObject" provider". Command parameters are unsupported with the current provider.
I don't have a linked server on my DB server to Active Directory so I'm just querying in the SSRS report design. Here is my query for my dataset. If I hard code an example it works. Just doesn't work when I pass a parameter. I've tried making it an expression (= sign), Tried several syntax's, Tried everything I can think of. Is this possible? or do I just need to push for a linked server?
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.
I am writing a stored procedure and have a query where I create a variable from other table
Declare @Sem varchar (12) Null @Decision varchar(1) Null Select emplid,name, Semester Decision1=(select * from tbldecision where reader=1) Decision2=(select * from tbldecision where reader=2) Where Semester=@Sem And Decision1=@Decision
But I am getting error for Decision1 , Decision2. How can I do that.
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]))
Hello, I have the following query in Access 2000 that I need to convertto SQL 2000:UPDATE tblShoes, tblBoxesSET tblShoes.Laces1 = NullWHERE (((tblShoes.ShoesID)=Int([tblBoxes].[ShoesID])) AND((tblBoxes.Code8)="A" Or (tblBoxes.Code8)="B"))WITH OWNERACCESS OPTION;The ShoesID in the tblShoes table is an autonumber, however the recordsin the tblBoxes have the ShoesID converted to text.This query runs ok in Access, but when I try to run it in the SQLServer 2000 Query Analizer I get errors because of the comma in the"UPDATE tblShoes, tblBoxes" part. I only need to update the tblShoesfield named Laces1 to NULL for every record matching the ones in thetblBoxes that are marked with an "A" or an "B" in the tblBoxes.Code8field.Any help would be greatly appreciated.JR
I have the following query created in MS Access that I would like to convert to MS SQL Server, but I can't get the IF logic to work properly, any help you can provide would be greatly appreciated.
Here is what I have, but I'm not confident it is correct: CASE WHEN (PTicketNum = '0' OR PTicketNum IS NULL) THEN 'M999Z' else PTicketNum END AS Ticket
So if the user enters LocationCode 1, then he should only get G2 back.
I've started by trying to do a count query:
Code:
SELECT tblEquipmentGroups.GroupTagNumber, tblEquipment.LocationCode FROM tblEquipmentGroups, tblEquipment, tblLookUpGroups WHERE tblEquipmentGroups.GroupTagNumber = tblLookUpGroups.GroupTagNumber AND tblEquipment.TagNumber = tblLookUpGroups.TagNumber GROUP BY tblEquipmentGroups.GroupTagNumber, tblEquipment.LocationCode
But really I'm completely stuck. Any help/suggestions would be appreciated! Thanks, BB