Please help I am trying to do an access query using an inner join...I am using an or to seperate the statements after the inner join...Can this not be done? I need a way to pull the records just once and right now it is duplicating them...Any help would be great!
<cfquery name="formWait" datasource="#datasource#">
SELECT distinct demographics.colors, demographics.colors2, formApprasialStateP.rec_ID, formApprasialStateP.keyName,
formApprasialStateP.form_id, formApprasialStateP.teacherID as sid, formApprasialStateP.teacherStatus,
formApprasialStateP.teacherStatus, formApprasialStateP.appraiserStatus, formApprasialStateP.appraiserStatus, formApprasialStateP.cycleYear as
ty, formApprasialStateP.cycle as tdc, formApprasialStateP.saved, formApprasialStateP.status
FROM formApprasialStateP INNER JOIN demographics ON formApprasialStateP.status = demographics.colors or formapprasialstatep.status=demographics.colors2 where teacherStatus = 2 and appraiserStatus = 1 and teacherID=#id#
</cfquery>
My query is pulling to many records becuase of the last join. This table can have multiple registration files for a computer. I just want the latest one or last one insert which is based on dttRegistration. I thought a top 1 select and order by would do it, but now returns no computer names.
I am trying to pull only those records with a maximum upload date for a file upload log.
This table keeps a list of files uploaded by supplierID and will have multiple records from the same supplier but with all different upload dates. Overall there are over 1,000 records for 48 uniqur suppliers.
The field names are: SupplierID, UploadDate, FeedFileName, RecordCount, FeedFileDate, RecordCount, and FeedLoaded.
So for each distinct SupplierID I'd like to grab the line item based on the max feedfiledate for each one - in other words I am trying to get the latest uploaded file information, how many records were in the file, and when it was uploaded... I've tried multiple group by and max clauses but there is something I am missing...
I'm trying to use the NEWID function in dynamic SQL and get an errormessage Incorrect syntax near the keyword 'ORDER'. Looks like I can'tdo an insert with an Order by clause.Here's the code:SELECT @SQLString = N'INSERT INTO TMP_UR_Randoms(Admit_DOCID,Client_ID, SelectDate, SelectType,RecordChosen)'SELECT @SQLString = @SQLString + N'(SELECT TOP ' + @RequFilesST + 'Admit_DOCID, Client_ID, SelectDate, SelectType, RecordChosen FROMFD__UR_Randoms 'SELECT @SQLString = @SQLString + N'WHERE SelectType = ' +@CodeRevTypeSt + ' AND SelectDate = ''' + @TodaySt + '''' + ' ORDERBY NEWID())'execute sp_executesql @SQLStringMy goal is to get a random percentage of records.The full SP follows. In a nutshell - I pull a set of records fromFD__Restart_Prog_Admit into a temporary table called FD__UR_Randoms.I need to retain the set of all records that COULD be eligible forselection. Based on the count of those records, I calculate how manyneed to be pulled - and then need to mark those records as "chosen".I'd just as soon not use the TMP_UR_Randoms table - I went that routebecause I ran into trouble with a #Tmp table in the above SQL.Can anyone help with this? Thanks in advance.Full SQL:CREATE PROCEDURE TP_rURRandomReview @ReviewType varchar(30)--Review type will fill using Crystal Parameter (setting defaults)AS/* 6.06.2007UR Requirements:(1) Initial 4-6 month review: 15% of eligible admissions(eligible via days in program and not yet discharged) must be reviewed4-6 months after admission. This review will be done monthly -meaning we'll have a moving target of names (with overlaps) whichcould be pulled from each month. (Minimum 5 records)(2) Subsequent 6-12 month review: Out of those already reviewed(in #1), we must review 25% of them (minimum of 5 records)(3) Initial 6-12 month review: Exclude any included in 1 or 2 -review 25% of admissions in program from 6-12 months (minimum 5)*/DECLARE @CodeRevType intDECLARE @PriorRec int -- number of records already markedeligible (in case user hits button more than once on same day for sametype of review)DECLARE @CurrRec int --number of eligible admitsDECLARE @RequFiles intDECLARE @SQLString nvarchar(1000)DECLARE @RequFilesSt varchar(100)DECLARE @CodeRevTypeSt char(1)DECLARE @TodayNotime datetimeDECLARE @TodaySt varchar(10)--strip the time off todaySELECT @TodayNotime = DateAdd(day,datediff(day,0,GetDate()),0)--convert the review type to a codeSelect @CodeRevType = Case @ReviewType when 'Initial 4 - 6 Month' then1 when 'Initial 6 - 12 Month' then 2 when 'Subsequent 6 - 12 month'then 3 END--FD__UR_Randoms always gets filled when this is run (unless it waspreviously run)--Check to see if the review was already pulled for this recordSELECT @PriorRec = (Select Count(*) FROM FD__UR_Randoms whereSelectType = @CodeRevType and SelectDate = @TodayNotime)If @PriorRec 0 GOTO ENDThis--************************************STEP A: Populate FD__UR_Randomstable with records that are candidates for review************************If @CodeRevType = 1BEGININSERT INTO FD__UR_Randoms (Admit_DOCID, Client_ID, SelectDate,SelectType,RecordChosen)(SELECT pa.OP__DOCID, pa.Client_ID,Convert(varchar(10),GetDate(),101) as SelectDate, @CodeRevType, 'F'FROM dbo.FD__RESTART_PROG_ADMIT paInner join FD__Client cOn pa.Client_ID = c.Client_IDWHERE Left(c.Fullname,2) <'TT' AND (Date_Discharge IS NULL)AND(DATEDIFF(d, Date_Admission, GETDATE()) 119)AND (DATEDIFF(d, Date_Admission, GETDATE()) <= 211)AND pa.OP__DOCID not in (Select Admit_DOCID from FD__UR_Randomswhere RecordChosen = 'T'))ENDIf @CodeRevType = 2--only want those that were selected in a batch 1 - in program 6-12months; selected for first reviewBEGININSERT INTO FD__UR_Randoms (Admit_DOCID, Client_ID, SelectDate,SelectType,RecordChosen)(SELECT pa.OP__DOCID, pa.Client_ID,Convert(varchar(10),GetDate(),101) as SelectDate, @CodeRevType, 'F'FROM dbo.FD__RESTART_PROG_ADMIT paInner join FD__Client cOn pa.Client_ID = c.Client_IDWHERE Left(c.Fullname,2) <'TT' AND (Date_Discharge IS NULL)AND(DATEDIFF(d, Date_Admission, GETDATE()) 211)AND (DATEDIFF(d, Date_Admission, GETDATE()) < 364)AND pa.OP__DOCID in (Select Admit_DOCID from FD__UR_Randomswhere SelectType = 1 AND RecordChosen= 'T'))ENDIf @CodeRevType = 3--only want those that were not in batch 1 or 2 - in program 6 to 12monthsBEGININSERT INTO FD__UR_Randoms (Admit_DOCID, Client_ID, SelectDate,SelectType,RecordChosen)(SELECT pa.OP__DOCID, pa.Client_ID,Convert(varchar(10),GetDate(),101) as SelectDate, @CodeRevType, 'F'FROM dbo.FD__RESTART_PROG_ADMIT paInner join FD__Client cOn pa.Client_ID = c.Client_IDWHERE Left(c.Fullname,2) <'TT' AND (Date_Discharge IS NULL)AND(DATEDIFF(d, Date_Admission, GETDATE()) 211)AND (DATEDIFF(d, Date_Admission, GETDATE()) < 364)AND pa.OP__DOCID NOT in (Select Admit_DOCID from FD__UR_Randomswhere SelectType < 3 AND RecordChosen= 'T'))ENDSELECT @CurrRec = (Select Count(*) FROM FD__UR_Randoms whereSelectType = @CodeRevType and SelectDate = @TodayNoTime)--*************************************STEP B Pick the necessarypercentage **************************************--if code type = 1, 15% otherwise 25%If @CodeRevType = 1BEGINSELECT @RequFiles = (@CurrRec * .15)ENDELSEBEGINSELECT @RequFiles = (@CurrRec * .25)END--make sure we have at least 5If @RequFiles < 5BEGINSELECT @RequFiles = 5End--*************************************STEP C Randomly select thatmany files**************************************--convert all variables to stringsSELECT @RequFilesSt = Convert(Varchar(100),@RequFiles)SELECT @CodeRevTypeSt = Convert(Char(1),@CodeRevType)SELECT @TodaySt = Convert(VarChar(10),@TodayNoTime,101)SELECT @SQLString = N'INSERT INTO TMP_UR_Randoms(Admit_DOCID,Client_ID, SelectDate, SelectType,RecordChosen)'SELECT @SQLString = @SQLString + N'(SELECT TOP ' + @RequFilesST + 'Admit_DOCID, Client_ID, SelectDate, SelectType, RecordChosen FROMFD__UR_Randoms 'SELECT @SQLString = @SQLString + N'WHERE SelectType = ' +@CodeRevTypeSt + ' AND SelectDate = ''' + @TodaySt + '''' + ' ORDERBY NEWID())'print @SQLStringexecute sp_executesql @SQLStringSELECT * FROM TMP_UR_Randoms/*--This select statement gives me what i want but I need to somehowmark these records and/or move this subset into the temp tableSelect Top @RequFilesFROM FD__UR_RandomsWHERE SelectType = @CodeRevType and SelectDate =Convert(varchar(10),GetDate(),101))ORDER BY NewID()*/ENDTHIS:GO
We are currently moving existing SSIS packages from one server to another. The former server is 32 bit and the new server is 64 bit clustered (not sure if that's relevant or not, though).
One package in particular is giving me a headache. It pulls from an access file to sql server 2005 table. We set up the security as don't save senstive and put the connection strings in a config file. We had to set the original job up to run the package step under a proxy account to get it to work.
If we right click on the package through the SSIS Store interface and execute it runs fine. However when we try to run it through the scheduled job, it fails. I very much appears to be a permissions issue on the proxy account. The person setting up the server is somewhat new to the area of SQL Servers, so they cannot provide much feedback. We're very much in a tweak until it works position, unfortunately.
Here are the errors that I am getting, one from the history of the job, one from the SQL Server logging. Does anyone have a suggestion, and if it is a permissions issue, where to look? I've been at this for days, so I've tried several different approaches. It seems like right now that I almost got it to work, but it seems like the connection to the access db is failing. But if that's the case, why does it run fine through the proxy on the original server (using same account)? I guess that very well could come down to the 64 bit problem (saw this on some other posts). I should mention that I set up the package as Run64BitRuntime = false. Do I need to rethink this as a batch file using DTExec? That was actually our original solution on the former server, but we could never get to work.
Job History Error:
DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Cost_WorkDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2007-03-23 16:19:14.92 Code: 0xC0047017 Source: Data Flow Task DTS.Pipeline Description: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C. End Error Progress: 2007-03-23 16:19:14.92 Source: Data Flow Task Validating: 100% complete End Progress Error: 2007-03-23 16:19:14.94 Code: 0xC004700C Source: Data Flow Task DTS.Pipeline Description: One or more component failed validation. End Error Error: 2007-03-23 16:19:14.94 Code: 0xC0024107 Source: Data Flow Task Description: There were errors during task validation.... The package execution fa... The step failed.
sysdtslog90 error:
SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Cost_WorkDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.
I've been experiencing difficulty with pulling records using a where clause date range. I'm using this:
select * from dbo.ACCTING_TRANSACTION_hISTORY where ath_postype = 'NTC' or ath_postype='NTD' and
ath_postdate >= '2013-01-01 00:00:00' and ath_postdate <= '2013-01-05 23:59:59'
I've also tried variations of this without the time portion of the ath_postdate field (of type datetime) , but it still seems to be pulling records from 2009, etc.
Hi, does anyone know how to retrieve mistmatched records across 2 tables. To clarify, I have table A with 1175 records and Table B with 894 records. The records from table A match exactly some the records in table B.
I want to create another table with the extra 281 records from Table A which does not match that of Table B.
I have tried the query with Select where fields_1.a<>fields_2.b AND fields_2.a<>fields_2.b etc but that doesn't seem to work.
Hi Respected, I am working in .net, and according my client he want to see all record from table uniquely, and does not want to give order also. for doing this i did use DISTINCT, but my problem is when i used DISTINCT the query return sorted records that is why, i need guide which will help to make DISTINCT and physical stored record from the table. thanks. bay regards Rajat.
Hi Friends, I have created the procedure in sql server 2005 for retriving email addresses from table based on date_expiry and concatinating all email addresses in to @tolist as shown below Declare @tolist varchar(8000) set @tolist = ''SELECT @tolist = @tolist + ';' + COALESCE(email, '') FROM awc_register WHERE DATEDIFF(day, date_expiry, GETDATE())='3'print @tolist set @tolist = substring(@tolist, 2, len(@tolist)) and I passed this @tolist to another procedure which should send mails for email addresses present in @tolist variable. Problem is I need to send the mail to each email address separatley.( not bulkly)
I got this piece of code from one of your articles:
select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;DATABASE=c: est.xls', 'Select * from [Book1$]')
Question is, how can I use a fully qualified path for the xls file name if the file is located on the network elsewhere?is that possible with this option?
I am trying to pull information about all the user created stored procedures in a database with a query. I have successfully pulled all the procs with parameters, but I want to result set to also contain the stored procedures without parameters. I can't quite get it. Any help would be appreciated:
SELECT procs.name as ProcName, params.name as ParameterName, types.name as ParamType, params.max_length, params.precision, params.scale, params.is_output FROM sys.procedures procs LEFT OUTER JOIN sys.all_parameters params ON procs.object_id = params.object_id LEFT OUTER JOIN sys.types types ON params.system_type_id = types.system_type_id
WHERE params.user_type_id = types.user_type_id AND procs.is_ms_shipped = 0 ORDER BY procname, params.parameter_id
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
Let's say I have a table of data as per the below..
I'm trying to extract only the green highlighted items..
The rules applied are: Only the latest data concerning all cases, and only 1 line (the latest) per case.
As you can see in the image, I don't want the 2nd,3rd, and 4th record extracted cause they are all superseded by more recent records (identified as they are further in the table).
I've considered using either Distinct or Having? but can't get that to work.. If I could use Distinct but then ensure it's the latest record in the table that would be perfect.
I have a query similar to the following. The intent of this query is to retrieve the top 6 records meeting the specified criteria (LOGTYPENAME = 'Process Status Start' OR LOGTYPENAME = 'Process Status End' ) based on most recent dates. Please keep in mind that I expect to return up to 6 records for each unique LogProcessName. This could be thousands of different LogProcessNames with up to 6 records for each.
1) The table I am executing against currently is very large in size and thus takes a long time to execute against. It would seem there must be a more efficient query to get the results I am looking for? 2) CTE doesn't work on SQL 2000. I need a query that does. 3) I cannot modify the database itself in the process.
;WITH cte AS ( SELECT [LogProcessName], [LogBody], [LogDate], [LogGUID], row_number() OVER(PARTITION BY [LogProcessName] ORDER BY [LogDate] DESC) AS RN FROM [LOGTABLE] WHERE [LogTypeGUID] IN ( SELECT LogTypeGUID FROM LOGTYPE WHERE LogTypeName = 'Process Status Start' OR LogTypeName = 'Process Status End' ) ) SELECT * FROM cte WHERE RN = 1 OR RN = 2 OR RN = 3 OR RN = 4 OR RN = 5 OR RN = 6 ORDER BY [LogProcessName] DESC, [LogDate] DESC
Does anybody else have any idea that would yield the results that I am looking for and take into account items 1-3 above?
I'm currently using the SQL to find records older than todays date in the SSD_SED field. I'm having to update the date manually each day. Is there a way I can automate this?
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 need to take the records from my MS Access DB and migrate them to SQL. I have both db table structures somewhat mapped out and its only about 43 records or lines. The data in Access is all kept in a single table and in SQL I need to have it populate seperate individual tables.
I am facing a big problem in data transfer from Access To SQL. Well the problem is i want to transfer nr about 50 lacs record from access to sql. at my side where sql server is complete install i could not find any problem and it transfer all data in 5 to 7 mins. now problem is at client side we not install entire sql but we install sql runtime which is available with installshied. and whenever i start to transfer data there is another exe called DLLHost.exe is run and it ate near about 70% to 75% Memory of system. well i use sql OPENROWSET statement and as soon as that statement fire at machine where entire sql is not install and only runtime install it starts to eat system memory so instead of transfer all data in 5 to 10 min it take more then 6 hrs bcaz dllhost.exe eat memory. is there any better way to transfer this records more fast pls let me know.
I have a question to ask. I'm new to SQL Server but have been trying to learn it. I have just installed the SQL Server 2000 SP4 and have a dummy Access database that need to be migrated to SQL Server 2000 for a test. I used the DTS wizard provided by the SQL Server 2000. It was successful - no error appears. I have also successfully done the link tables from Access to SQL Server. Now, my question is how do i add/edit/delete some records in SQL Server via Access. I need your help or guidance on this.
I am new to DTS/SSIS and have a couple of questions about using it to solve a problem. We have an application running on SQL Server 2005 where status records are written to a status table. I need to be able to send those records over to a status table in a legacy application running on Access.
Originally, I thought about writing a custom c# stored proc and accessing Access from it and then someone pointed me to DTS/SSIS.
Is there a way to exectute the package based on a trigger event that a row was inserted or updated? If not and I take a scheduled approach (every 3 minutes, etc.) do I have to maintain a column for the records that get processed so they are not picked up again.
In general is using SSIS the approach to take? The overall business requirements are straight forward, but I am not sure if SSIS is overkill for this or not.
How can I get MS ACCES database records from an sql Stored Procedure. Idea is I have a SQL stored procedure, which wants to calculate a few things using some data from an MS access DB. Is it possible or am I talking against any DB principles?
Using SSE 2012 64-bit.I need to insert records from multiple Access Tables into 1 Table in SSE and ensure no duplicates are inserted.This is executing, but is very slow, is there a faster way?
Code: INSERT INTO dbTarget.dbo.tblTarget (All fields) SELECT (All Fields) FROM dbSource.dbo.tblSource WHERE RecordID NOT IN (SELECT RecordID FROM dbTarget.dbo.tblTarget)
hi, I want to Display the image records of each employee of having 10 images of each.the number of records are more then one billion....... please provid me the optimized query that can i used in SP and display in Gridview.
Running SQL 2005 on a dedicated Dual Xeon 8MB RAM AND 6x 160GB HDDS RAID-5. Front end App Access 2002. I have to main concerns. 1. When users are enter data and saving all of a sudden all users get stuck upon saving and requires to reboot the server to operate again. Th eproblem is that this happens 3 to 5 times a day. Also searching records with one word takes very long time sometimes even minutes, but queries having two or more words come up fast. Also single word queries come up with erratic results, sometimes 200 titles and than a second search comes up with 31,000. *** Can anyone come up with any ideas "off the cuff" on how I canstart to resolve these issues. Can these issues be related?
Then I want to run a select in the users table, only with the users that hasn't records in the second table.
In the example, the second table has User_1 as the user who receives the points and the User_2 is the user who give the points. Then I would know what user didn't receive 'points' yet.
I am trying to filter the records in an Access2003 project form using data from a control in a different form. I define the variable in form1 as RegKeyWord. The record source for form2 ends with WHERE CustomerName Like RegKeyWord. I receive the error Invalid column 'RegKeyWord' Can anyone help?
I want to create a view to get records from multiple tables. I have a UserID in all the tables. When I pass UserID to view it should get records from multiple tables. I have a table
UserInfo with as data as UserID=1, FName = John, LName=Abraham and Industry = 2. I have a Industry table with data as ID=1 and Name= Sports, ID =2 and Name= Film.
When I query view where UserID=1 it should return record as
I have a database that is in mssql and I'm using an odbc link to an access database where I want to add records to the mssql table. When I open the linked table in access it does not allow me to add a record. I have created a user account in mssql that has ownership to the database and I use this user in setting up the odbc link.
I am currently working with C and SQL Server 2012. My requirement is to Bulk fetch the records and Insert/Update the same in the other table with some business logic? How do i do this?
