TSQL From An Access SQL Statement
Good morning one and all,
I have some queries that were written in access that I need to port into SQL 7, the whole process is boring and mundane. Does any1 know of a translator (i.e. access sql to t-sql) or a reference to the differences between access SQL and t-Sql.
Any and all help appreciated,
Thanx Gurmi
View Complete Forum Thread with Replies
Related Forum Messages:
TSQL - Use ORDER BY Statement Without Insertin The Field Name Into The SELECT Statement
Hi guys, I have the query below (running okay): Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' FROM myTables WHERE Conditions are true ORDER BY Field01 The results are just as I need: Field01 Field02 ------------- ---------------------- 192473 8461760 192474 22810 Because other reasons. I need to modify that query to: Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' INTO AuxiliaryTable FROM myTables WHERE Conditions are true ORDER BY Field01 SELECT DISTINCT [Field02] FROM AuxTable The the results are: Field02 ---------------------- 22810 8461760 And what I need is (without showing any other field): Field02 ---------------------- 8461760 22810 Is there any good suggestion? Thanks in advance for any help, Aldo.
View Replies !
I Need Help With This Tsql Statement
Every time I try this statement I keep getting a syntext error near count I must be over looking something can some one help me with this. SELECT 'Quarter 1' as 'qtr' count(jobid) as 'transcount', count(distinct job.patientid) as 'patientcount', sum(job.LANGUAGE_TCOST) as 'lcost', Sum(job.LANGUAGE_DISC_COST) as 'dlcost', avg(LANGUAGE_DISC) as 'avgLDisc', (sum(job.LANGUAGE_TCOST) + sum(job.LANGUAGE_DISC_COST)) as 'LGrossAmtBilled', (sum(LANGUAGE_TCOST) / count(distinct job.patientid)) as 'PatAvgL', (sum(LANGUAGE_TCOST) / count(jobid)) as 'RefAvgL', sum(LANGUAGE_DISC) as 'avgPercentDiscL', JOB.JURISDICTION, PAYER.PAY_COMPANY, PAYER.PAY_CITY, PAYER.PAY_STATE, PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE INVOICE_AR.INVOICE_DATE AS EXPR1, INVOICE_AR.AMOUNT_DUE FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYER.ID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS WHERE (INVOICE_AR.AMOUNT_DUE > 0)AND (INVOICE-AR.INVOICE_DATE BETWEEN @startdate and @enddate)AND (MONTH(INVOICE_AR.INVOICE_DATE) IN (1,2,3))AND (PAYER.PAYCOMPANY like '%' + @Company + '%') Group By JOB.JURISDICTION PAYER.PAY_COMPANY PAYER.PAY_CITY PAYER.PAY_STATE PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE, INVOICE_AR.INVOICE_DATE, INVOICE_AR.AMOUNT_DUE UNION ALL SELECT 'Quarter 2' as 'qtr' count(jobid) as 'transcount', count(distinct job.patientid) as 'patientcount', sum(job.LANGUAGE_TCOST) as 'lcost', Sum(job.LANGUAGE_DISC_COST) as 'dlcost', avg(LANGUAGE_DISC) as 'avgLDisc', (sum(job.LANGUAGE_TCOST) + sum(job.LANGUAGE_DISC_COST)) as 'LGrossAmtBilled', (sum(LANGUAGE_TCOST) / count(distinct job.patientid)) as 'PatAvgL', (sum(LANGUAGE_TCOST) / count(jobid)) as 'RefAvgL', sum(LANGUAGE_DISC) as 'avgPercentDiscL', JOB.JURISDICTION, PAYER.PAY_COMPANY, PAYER.PAY_CITY, PAYER.PAY_STATE, PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE INVOICE_AR.INVOICE_DATE AS EXPR1, INVOICE_AR.AMOUNT_DUE FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYER.ID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS WHERE (INVOICE_AR.AMOUNT_DUE > 0)AND (INVOICE-AR.INVOICE_DATE BETWEEN @startdate and @enddate)AND (MONTH(INVOICE_AR.INVOICE_DATE) IN (4,5,6))AND (PAYER.PAYCOMPANY like '%' + @Company + '%') Group By JOB.JURISDICTION PAYER.PAY_COMPANY PAYER.PAY_CITY PAYER.PAY_STATE PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE, INVOICE_AR.INVOICE_DATE, INVOICE_AR.AMOUNT_DUE UNION ALL SELECT 'Quarter 3' as 'qtr' count(jobid) as 'transcount', count(distinct job.patientid) as 'patientcount', sum(job.LANGUAGE_TCOST) as 'lcost', Sum(job.LANGUAGE_DISC_COST) as 'dlcost', avg(LANGUAGE_DISC) as 'avgLDisc', (sum(job.LANGUAGE_TCOST) + sum(job.LANGUAGE_DISC_COST)) as 'LGrossAmtBilled', (sum(LANGUAGE_TCOST) / count(distinct job.patientid)) as 'PatAvgL', (sum(LANGUAGE_TCOST) / count(jobid)) as 'RefAvgL', sum(LANGUAGE_DISC) as 'avgPercentDiscL', JOB.JURISDICTION, PAYER.PAY_COMPANY, PAYER.PAY_CITY, PAYER.PAY_STATE, PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE INVOICE_AR.INVOICE_DATE AS EXPR1, INVOICE_AR.AMOUNT_DUE FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYER.ID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS WHERE (INVOICE_AR.AMOUNT_DUE > 0)AND (INVOICE-AR.INVOICE_DATE BETWEEN @startdate and @enddate)AND (MONTH(INVOICE_AR.INVOICE_DATE) IN (7,8,9))AND (PAYER.PAYCOMPANY like '%' + @Company + '%') Group By JOB.JURISDICTION PAYER.PAY_COMPANY PAYER.PAY_CITY PAYER.PAY_STATE PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE, INVOICE_AR.INVOICE_DATE, INVOICE_AR.AMOUNT_DUE UNION ALL SELECT 'Quarter 4' as 'qtr' count(jobid) as 'transcount', count(distinct job.patientid) as 'patientcount', sum(job.LANGUAGE_TCOST) as 'lcost', Sum(job.LANGUAGE_DISC_COST) as 'dlcost', avg(LANGUAGE_DISC) as 'avgLDisc', (sum(job.LANGUAGE_TCOST) + sum(job.LANGUAGE_DISC_COST)) as 'LGrossAmtBilled', (sum(LANGUAGE_TCOST) / count(distinct job.patientid)) as 'PatAvgL', (sum(LANGUAGE_TCOST) / count(jobid)) as 'RefAvgL', sum(LANGUAGE_DISC) as 'avgPercentDiscL', JOB.JURISDICTION, PAYER.PAY_COMPANY, PAYER.PAY_CITY, PAYER.PAY_STATE, PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE INVOICE_AR.INVOICE_DATE AS EXPR1, INVOICE_AR.AMOUNT_DUE FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYER.ID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS WHERE (INVOICE_AR.AMOUNT_DUE > 0)AND (INVOICE-AR.INVOICE_DATE BETWEEN @startdate and @enddate)AND (MONTH(INVOICE_AR.INVOICE_DATE) IN (10,11,12))AND (PAYER.PAYCOMPANY like '%' + @Company + '%') Group By JOB.JURISDICTION PAYER.PAY_cOMPANY PAYER.PAY_CITY PAYER.PAY_STATE PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE, INVOICE_AR.INVOICE_DATE, INVOICE_AR.AMOUNT_DUE Order By 'QTR' asc
View Replies !
TSQL - WITH Statement
Hi guys, I need help with this one... Iam Trying to understand how to use the statement WITH I am running the code below, but getting error. note: I have SQL SERVER 2005 in my PC, but retrieving data from the SQL SERVER 2000 (in the server) Thanks in advance, Aldo. Code Snippet WITH MyCTE (FILTER, SORTGROUP) AS ( SELECT ACCOUNTS.FILTER, ACCOUNTS.SORTGROUP FROM ACCOUNTS ) SELECT * FROM MyCTE AS CTE_01; Error Messages: Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'WITH'.
View Replies !
How Can I Use Variables In This TSQL Statement
Hi all,I would like to replace the default directory location (c: emp) and thefilename (emails.csv) with variables like @FileDir and @FileName in thestatement below.SELECT @cnt = COUNT(*) FROM OpenRowset('MSDASQL', 'Driver={Microsoft TextDriver (*.txt; *.csv)}; DefaultDir=c: emp;','select * from "emails.csv"')However, my attempts have not been successful.Any ideas appreciated, and TIA.Greg
View Replies !
What Is Wrong With This TSQL Statement
hello, I have wriiten this query to select all records from those tables in a database that have "to_be_transffered" column where this "to_be_transferred_column" is not null --code EXEC sp_MSForEachTable 'IF EXISTS(SELECT c.table_name, c.column_name FROM information_schema.columns c INNER JOIN information_schema.tables t ON c.table_name = t.table_name WHERE c.table_name = SUBSTRING(''?'', (CHARINDEX(''['',''?'', 2) + 1), ((LEN(''?'') - (CHARINDEX(''['',''?'', 2) + 1)))) AND c.column_name = ''to_be_transferred'' AND t.table_type = ''BASE TABLE'' ) BEGIN SELECT * FROM ? WHERE to_be_transferred IS NOT NULL END' But I am getting the following error Msg 207, Level 16, State 3, Line 12 Invalid column name 'to_be_transferred' I thought the if statement filters all those tables with the specified column and do select statement fot those tables only what did I do wrong, any suggestionm? Thanks
View Replies !
Tsql Case Statement
Hi, Here is the scenario. I want to add last year sale dollars in accordance with current period in exsiting fact table. And below is the syntax. Syntax: ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- select a. store_key, a.fisc_date_key, sum(a.net_sale_Dollars) as sale_TY , sum ( b.net_sale_dollars ) as sale_LY , a.division_name, a.department_number fromFact 1 as a ,Fact 1 as b Whereb.fisc_date_key = (a.fisc_date_key -364) and a.division_name=b.division_name and a.department_number =b.department_number and a.store_key = b.store_key group by a.division_name, a.department_number, a.fisc_date_key, a.store_key ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- The current table from this query is showing like: store_key date_key sale_TY sales_LY div dept ------------------------------------------------------------------------------------------------ 1 1 30 20 ABC 1 2 1 20 20 ABC 3 But, if we assume that in the current date, dept = 2 has a sale amount, and in parallel year if dept=2 does not have any sale then this information was excluded. The structure of table that I want to create must look like: store_key date_key sale_TY sales_LY div dept ------------------------------------------------------------------------------------------------ 1 1 30 20 ABC 1 2 1 20 20 ABC 3 2 1 15 0 ABC 2 >>>> want to put 0 value where only one side ( current or parrallel period) has sales info. So, I'm thinking the case statement like: Case statement logic like: ------------------------------------------------------------ if a. dept not exist in b.dept then Sale TY -> a.net_sale_dollars Sale LY -> 0 if b.dept not exist in a.dept then sale TY -> 0 sale LY -> b. net_sale_dollars ------------------------------------------------------------- below is the syntax which doesn't work (it's wrong): Syntax: ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- select a. store_key, a.fisc_date_key, sum(case when a.department_number = b.department_number then a.net_sale_dollars else case when a.department_number NOT IN (b.department_number)then a.net_sale_dollars else null end) as sale_TY , sum ( case when b.department_number =a.department_number then b.net_sale_dollars else case when a.department_number NOT IN (b.department_number) as sale_LY, a.division_name, a.department_number fromFact 1 as a Fact 1 as b Whereb.fisc_date_key = (a.fisc_date_key -364) and a.division_name=b.division_name and a.department_number =b.department_number and a.store_key = b.store_key group by a.division_name, a.department_number, a.fisc_date_key, a.store_key , ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Is it possible to create these kind of structure? Please give me some comments. Thanks.
View Replies !
TSQL: Conditional Union Statement
Is it possible to have a conditional union statement in a stored proc?Here's an example on the northwind database. If says there's a syntaxerror near the UNION statement. Looks like it doesn't like having theBEGIN directly in front of it.Is the only solution to create a dynamic sql string then call exec onit?Any help appreciated.Tom.CREATE PROCEDURE usp_test(@both int = 1)ASSET NOCOUNT ONSELECT * FROM territories WHERE regionid = 1IF @both = 1BEGINUNIONSELECT * FROM territories WHERE regionid = 2ENDGO
View Replies !
TSQL: I Want To Use A SELECT Statement With COUNT(*) AS 'name' And ORDER BY 'name'
I am very new to Transact-SQL programming and don't have a programmingbackground and was hoping that someone could point me in the rightdirection. I have a SELECT statement SELECT FIXID, COUNT(*) AS IOIsand want to ORDER BY 'IOI's'. I have been combing through the BOL, butI don't even know what topic/heading this would fall under.USE INDIISELECT FIXID, COUNT(*) AS IOIsFROM[dbo].[IOI_2005_03_03]GROUP BY FIXIDORDER BY FIXIDI know that it is a simple question, but perhaps someone could assistme.Thanks,
View Replies !
Help With TSQL Statement To Check For Table Size
I have to deal with an environment where the developers create and modify tables at will, and the tool they use does not check if the table created is over the 8060 limit for a table. I have a sql statement below which checks the size for me, but I can't figure out how to put a where clause on it to check for the size being over 8060. I i have over 1000 tables to check and was going to write a cursor routine to check it and print it, but I need te where clause to work.Any help appreciated select Name=left(c.name,20), c.prec, scale = ISNULL(c.scale,0), t.name from syscolumns c inner join systypes t on c.xtype = t.xtype where id in (select id from sysobjects where name = 'mh_demographic2_') order by c.colid COMPUTE sum(c.prec)
View Replies !
MS Access SQL Migration To MS SQL Server TSQL
got some MS Access SQL Code that needs converting into TSQL: SELECT dbo_qryMyServices.FormsServiceID, dbo_qryMyServices.ServiceName, Sum(IIf(IsNull([CompletionDate]),0,1)) AS Completed, Count([pkServiceID])-Sum(IIf(IsNull([CompletionDate]),0,1)) AS Uncompleted, Count(dbo_MyServiceRequests.pkServiceID) AS TotalCount FROM dbo_qryMyServices LEFT JOIN dbo_MyServiceRequests ON dbo_qryMyServices.FormsServiceID = dbo_MyServiceRequests.PostType GROUP BY dbo_qryMyServices.FormsServiceID, dbo_qryMyServices.ServiceName ORDER BY dbo_qryMyServices.ServiceName; because it's Access and got VBA stuff in it - IIF and ISNULL, I can't figure out how to make it work in proper SQL. Any help would be most appreciated!
View Replies !
How To Access Global Variable In DTS And Use In TSQL
I have a VBscript below which works fine, which creates a unique file name for a text source and creates it just fine. What I need is to use that file name in an insert statement in TSQL to load a record to a transaction table. How do I utilize this variable to do this? Thanks Function Main() Dim oConn, sFilename ' Filename format - exyymmdd.log sFilename = "ex" & Right(Year(Now()), 2) If Month(Now()) < 10 Then sFilename = sFilename & "0" & _ Month(Now()) Else sFilename = sFilename & Month(Now()) If Day(Now()) < 10 Then sFilename = sFilename & _ "0" & Day(Now()) Else sFilename = sFilename & Day(Now()) sFilename = DTSGlobalVariables("LogFilePath").Value & _ sFilename & ".log" Set oConn = DTSGlobalVariables.Parent.Connections("Text File (Destination)") oConn.DataSource = sFilename ' oConn.DataTarget = sFilename Set oConn = Nothing Main = DTSTaskExecResult_Success End Function
View Replies !
Converting Access DB Code To Tsql--Help!!!!!
We are migrating an access97 database to sql server7.0. there are queries in access which need to be made into tsql queries.And what are the steps to convert access97 to sql7.0 and how do i migrate memo fields to sql7. Is there some method to convert or tool that does that...any help is welcome and thanks in advance.
View Replies !
Help Required Converting From MS Access SQL To TSQL
Hello, I am fairly new to SQL Server so I apologise if this is the wrong forum. I have a Sales analysis table in a SQL Server 2000 database. The table is populated from various sources in our ERP system. via a DTS package For our French branch sales unit of measure is eachs (EA) for actuals, but the primary UOM and our forecast data is normally in cartons. I have a product master table which defines primary unit of measure, and a unit of measure conversion table. So if I wanted to convert the data all to the primary measure I would write the below in Access: UPDATE (tblSalesReport INNER JOIN tblItemMasterERP ON tblSalesReport.fldProductNo = tblItemMasterERP.fldProductNo) INNER JOIN tblUOMConvertERP ON (tblItemMasterERP.fldShortItemNo = tblUOMConvertERP.fldItemNo) AND (tblItemMasterERP.fldPrimaryUOM = tblUOMConvertERP.fldUOM1) SET tblSalesReport.fldUOM = [tblItemMasterERP]![fldPrimaryUOM], tblSalesReport.fldQuantity = [tblSalesReport]![fldQuantity]/[tblUOMConvertERP]![fldConvFactor] WHERE (((tblSalesReport.fldCompany)="00007") AND ((tblUOMConvertERP.fldUOM2)=[tblSalesReport]![fldUOM]) AND (([tblSalesReport]![fldUOM])<>[tblItemMasterERP]![fldPrimaryUOM])); I have found that in the DTS I can add an SQL task, but it seems to only allow UPDATE if there are no joined tables. I found the same thing in Stored Procedures, the SQL designer would only allow me to use one table. I guess I am looking in the wrong places. Can anyone point me in the right direction to incorporate the above sql (or equivolent) into our DTS package. Unfortunately the company decided to dispense with the services of the person who designed the package.
View Replies !
TSQL Statement Extracting Data From One Table Through Another Table
Hi, I have 2 tables, MembersTemp and Organisations I'm trying to extract the organisation Name from the organisations table but am unsure of the sql statement to do this. Initiallt I only have the ExecID for the MembersTemp table MembersType table: ExecID 3013 OrganisationID 4550 Organisation table: ID 4550 (PK) Name "Microboff" Any ideas??
View Replies !
First Statement From Access To SQL
I'm trying to convert this from Access to SQL: SELECT ORDER_DETAIL.ORDER_NUMBER, Substring([ITEM_NUMBER],2,7) AS ITEMNO, First(ORDER_DETAIL.ITEM_DESC) AS FirstOfITEM_DESC FROM ORDER_DETAIL But it won't accept the First statement, specifically "First" is not recognized. First(ORDER_DETAIL.ITEM_DESC) AS FirstOfITEM_DESC Does anyone know a way around?
View Replies !
Equivalent Tsql For Sql Server 2000 Is Needed [from Sql Server 2005 Only Tsql]
Can anyone please give me the equivalent tsql for sql server 2000 for the following two queries which works fine in sql server 2005 1 -- Full Table Structure select t.object_id, t.name as 'tablename', c.name as 'columnname', y.name as 'typename', case y.namewhen 'varchar' then convert(varchar, c.max_length)when 'decimal' then convert(varchar, c.precision) + ', ' + convert(varchar, c.scale)else ''end attrib,y.*from sys.tables t, sys.columns c, sys.types ywhere t.object_id = c.object_idand t.name not in ('sysdiagrams')and c.system_type_id = y.system_type_idand c.system_type_id = y.user_type_idorder by t.name, c.column_id 2 -- PK and Index select t.name as 'tablename', i.name as 'indexname', c.name as 'columnname' , i.is_unique, i.is_primary_key, ic.is_descending_keyfrom sys.indexes i, sys.tables t, sys.index_columns ic, sys.columns cwhere t.object_id = i.object_idand t.object_id = ic.object_idand t.object_id = c.object_idand i.index_id = ic.index_idand c.column_id = ic.column_idand t.name not in ('sysdiagrams')order by t.name, i.index_id, ic.index_column_id This sql is extracting some sort of the information about the structure of the sql server database[2005] I need a sql whihc will return the same result for sql server 2000
View Replies !
Convert Access IIF To Sql In Order By Statement
ive been having a lot of trouble converting this last line from my access database to sql server. i was hoping someone could help order by Name, IIF(Risk.Calc like H*, 1, IIF (Risk.Calc like M*,2,3)), Risk.Chance*risk.cons DESC, risk.title any suggestions?
View Replies !
SQL Between Dates Statement In Access Database
I have following SQL statement which I run on an Access database. ( Program written in VB.NET ) '--------- SELECT c.* FROM Communications AS c WHERE CommDate Between #1/4/2005# And #5/4/2005#; '------------ I've put dummy values in the database for dates between 1st and 8th of april... And running this query suprisingly gives back ALL rows in the table... Also the entries for the 6th, 7th and 8th of april... In fact it just gives back the WHOLE table, no matter which values I add... If I use '1/4/2005' (not with #) then I get an error back. Anybody an Idea how to make a BETWEEN statement work in an access database? Thanks, Frederik
View Replies !
SqlDataReader Access - 'using' Statement Vs 'CommandBehaviour.closeConnection'
Can someone explain this to me?Whats the difference in "using" statement of C# Vs "SqlDataReader rdr = cmd.ExecuteReader(CommandBehaviour.closeConnection)"????i thought both closes all the resources both "connection" as well "underlying datareader"but i have come to believe that CommandBehaviour.closeConnection leaves the 'datareader' open and closes the connection ONLY.Here is why..----------i have the c# code in a class where i am creating a connection,executing stored procedure and returning the datareader.I am making use of 'using' statement something like this here public class DB{public SqlDataReader getReader(){ using (SqlConnection conn1 = new SqlConnection(param1)) { SqlCommand cmd1 = new SqlCommand(sproc, conn1); cmd1.CommandType = CommandType.StoredProcedure; conn1Open(); SqlDataReader rdr = cmd1.ExecuteReader(); return rdrMIS; }} -this datareader is used in the function in a main code something like this and assign it to Dropdown list(just outlining the code) function abc(){ DB db1 = new db1() ddl1.datasource = db1.getReader();ddl1.DataTextField = "val1";ddl1DataValueField = "val2";ddl1.DataBind(); } this code gives me the error "Invalid attempt to FieldCount when reader is closed" - I think this is because the 'using' statment above,is closing the connection as well as datareader,so its not available in the calling function -however if i remove the 'using' statment and change the line SqlDataReader rdr = cmd.ExecuteReader(CommandBehaviour.closeConnection) instead ;It works fine.Why?Is it because CommandBehaviour.closeConnection closes the connection but datareader is OPEN and still available to the function?If so,do i need to close the reader explicitly in my function?..i never did it and it works fine.. - i like to use 'using' statement ..it is clean and makes sure resource disposal is properly taken care of.But i would still like to use datareader and not dataset.Also i want to handle all the database related task such as opening,closing of connection,datareader in a class and not in my calling function. Whats the best way to handle all this concern. Thanks for your help.
View Replies !
Convert Ms Access Sql Staement To Oracle Sql Statement
Hi,I currently have a ms access update query that runsperfectly well and quicly in access however I now need to add this queryto convert this qeryu to oracles equivelant sql syntax and add it to the endof an oracle sql script.Unfortunately Im not having much success although i seem to be able toconvert it to a working oracle sql. it takes hours to run the statement inoracle where as in access it runs in secondsany help is appreciated.Ms Access sql :UPDATE (PRO_STY_TPRICES INNER JOIN PRO_STYLE_COLOURS ON PRO_STY_TPRICES.STY_ID = PRO_STYLE_COLOURS.STY_ID) INNER JOIN PRO_TST_RV3X_RPT_WRK ON(PRO_STYLE_COLOURS.SEASON = PRO_TST_RV3X_RPT_WRK.SEASON) AND(PRO_STYLE_COLOURS.STY_NUM = PRO_TST_RV3X_RPT_WRK.STY_NUM) AND(PRO_STYLE_COLOURS.STY_QUAL = PRO_TST_RV3X_RPT_WRK.STY_QUAL) AND(PRO_STYLE_COLOURS.BF_MAT_CHAR_VAL = PRO_TST_RV3X_RPT_WRK.BF_MAT_CHAR_VAL)SET PRO_TST_RV3X_RPT_WRK.MKD_DATE = pro_sty_tprices.new_active_date,PRO_TST_RV3X_RPT_WRK.MKD_PRICE = pro_sty_tprices.new_tpriceWHERE (((PRO_STY_TPRICES.NEW_ACTIVE_DATE) Is Not Null));Oracle SQL :update pro.tst_rv3x_rpt_wrk xset(x.mkd_date, x.mkd_price) =(Select a.new_active_date, a.new_tpricefrom pro.sty_tprices a, pro.style_colours bwhere a.sty_id=b.sty_idand b.bf_mat_char_val = x.bf_mat_char_valand b.season = x.seasonand b.sty_num = x.sty_numand b.sty_qual = x.sty_qualand a.new_active_date is not null)
View Replies !
Select Top N... Statement Fail Deu To Access Violation
Hi, all. The problem for all tables in a database select * from a_table - do work OK select TOP 1 * from a_table fails with SQL State 4200 Error 170 (Syntax error) 1. the query worked yesterday. Yesterday I've changed permissions for PUBLIC. 2. the same query (select TOP 1 *...) works fine in another database on the same server. 3. Note: select * from... works
View Replies !
UNION Statement That Works In SQL Server But Not Access
Hi, Is there a way I can get this select Union statement to work in Access. SELECT '' AS Router UNION SELECT DISTINCT Router FROM IPVPNRouterUpgradeCharges WHERE SchemeID = 12 AND Router <> 'IPVPN Lite' AND Router <> 'VPN Bridge' AND Router <> 'IPVPN Aggregated Bandwidth' ORDER By Router I get this message in Access: Query input must contain at least input of query Thanks for any help Chris
View Replies !
SQL Server Blocked Access To STATEMENT 'OpenRowset/OpenDatasource'
Hello, I am trying to transfer data from an Access Database to a SQL Database and the error I get is below. I have enabled Ad Hoc Distributed Queries and the OpenRowSet/DataSource options on the SQL server, yet I still get the error. Also below is my coding. Thanks. SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online. sqlConn.Open() sqlCmd = New SqlCommand("SELECT * INTO tbl_partstable FROM " & _ "OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', " & _ "'Data Source=c:inetpubwwwrootPartsDBApp_Datawai_app.mdb')" & _ "...PNINFO", sqlConn) sqlCmd.ExecuteNonQuery() sqlConn.Close()
View Replies !
MS Access Vs. Transact-SQL – Conditional SQL Select Statement Problem
Hi All, I am trying to write a Transact-SQL statement and am having no success. I have a customers table that has some duplicate Customer Numbers in it, however the records do have a unique Reference Number. I am trying select all records that match a list of Customer Numbers but if there are more than 1 matching Customer Number I only want the one with the largest Reference Number. My BASIC Select Statement is: SELECT Cust_Ref_No, Customer_No, Customer_Name, Address1, Address2, Suburb, State, Postcode, Phone FROM Customers WHERE Customer_No IN (SELECT Customer_No FROM temp_Customers) Much to my HORROR I found that what I am trying to do is simple in MS Access using the “First” Function – Such as below: SELECT First(Cust_Ref_No) AS Cust_Ref_No, Customer_No FROM Customers GROUP BY Customer_No ORDER BY First(Cust_Ref_No), Customer_No; There appears to be no “First” Function in Transact-SQL. Is someone able to show me how to achieve the same results in Transact-SQL as I can get with MS Access (I’d HATE to think that MS Access has more functionality in querying data than SQL Server in any way at all)? P.S. I really need to run the select statement as one step rather than splitting it up into parts. Regards, Trog28
View Replies !
HOW? Access Results Of Computed SQL Statement In A Data Flow
The SQL computed is complex enough that I can't see a way to make it a parameterized query. The obvious approach seems to be to compute the SQL in a CONTROL FLOW SCRIPT TASK and then use it to load a variable to set the VARIABLE SOURCE of a CONTROL FLOW EXECUTE SQL TASK. I see that I can return a resultset to a variable. But getting the rows of the results into a dataflow is not obvious. I have heard mentione that a Derived Column can do this. I can see using a dummy SCRIPT COMPONENT as DATA SOURCE with nothing in it to then drop into DERIVED COLUMN. But when setting up DERIVED COLUMN I don't see how to pull the columns out of the RESULTSET variable. If it makes a difference I think the columns of the resultset will always be the same in this scenario. Maybe this is totally the wrong approach? Any clues would be appreciated.
View Replies !
How Do I Access A Package Variable In On OLEDB SQL Statement (Execute SQL Task)
Hello all, I have a package that picks up a file from an FTP site and loads the file into a database untouched. That is, it loads the file as a binary object. The filename changes each time in comes in (it has the date included in the filename). First I determine the expected file name and store that in a package level variable (FileName). I use that variable in my FTP Task to grab the file and move a copy of it to a local box. The next step is to load that file into my database via an Execute SQL Task using an OpenRowSet insert. The statement should look like this: Into File_Import (Event_Schedule_History_Id, FTP_Document) (@ESH_ID, * FROM OPENROWSET(BULK '<C:filename>' , SINGLE_BLOB) AS FTP_Document ' Ideally, I would like to replace <C:filename> with my package level variable. When I attempt to do that however and place a parameter marker (?) in it's place it doesn't get replaced/inturpreted with the parameter value. I have only had success with parameter markers when calling a stored proc from an Execute SQL Task and using a parameter marker for one of the SP parameters. Can I use a variable in this way? Anyone had any luck with this? Thanks, Dave
View Replies !
UPDATE SQL Statement In Excel VBA Editor To Update Access Database - ADO - SQL
Hello,I am trying to update records in my database from excel data using vbaeditor within excel.In order to launch a query, I use SQL langage in ADO as follwing:------------------------------------------------------------Dim adoConn As ADODB.ConnectionDim adoRs As ADODB.RecordsetDim sConn As StringDim sSql As StringDim sOutput As StringsConn = "DSN=MS Access Database;" & _"DBQ=MyDatabasePath;" & _"DefaultDir=MyPathDirectory;" & _"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" &_"PWD=xxxxxx;UID=admin;"ID, A, B C.. are my table fieldssSql = "SELECT ID, `A`, B, `C being a date`, D, E, `F`, `H`, I, J,`K`, L" & _" FROM MyTblName" & _" WHERE (`A`='MyA')" & _" AND (`C`>{ts '" & Format(Date, "yyyy-mm-dd hh:mm:ss") & "'})"& _" ORDER BY `C` DESC"Set adoConn = New ADODB.ConnectionadoConn.Open sConnSet adoRs = New ADODB.RecordsetadoRs.Open Source:=sSql, _ActiveConnection:=adoConnadoRs.MoveFirstSheets("Sheet1").Range("a2").CopyFromRecordset adoRsSet adoRs = NothingSet adoConn = Nothing---------------------------------------------------------------Does Anyone know How I can use the UPDATE, DELETE INSERT SQL statementsin this environement? Copying SQL statements from access does not workas I would have to reference Access Object in my project which I do notwant if I can avoid. Ideally I would like to use only ADO system andSQL approach.Thank you very muchNono
View Replies !
Granting Access For Intranet Access And External Access
Hi.. I have been given this problem to troubleshoot but I am not very familiar with the Internet Infomation Services (IIS) or firewall setting so if someone can guide me whould be great. We have this log in asp website which can be accessed through Intranet and I have tested there is no problem. But this client needs to log in to our site from her company but she cant log in. From our company when log in using her account using the intranet link, I can logged in. However I cannot log in from external link which our client uses to log in from her company. So I tested that external link from my home which is the different isp from my office and it works. So far I have asked her to try to log in in different computers in her office and told her to make sure her browser to accept cookies. She still cannot log in. Now what should I do. I think I need to check our firewall to see why I cannot log in to the external link from our company and why she cant log in from her company. Where can I check to see her company ip address could be denied?I think i need to check in ISA and IIS setting but I dont know how. Please help me. thank you in advance.
View Replies !
Multiple Tables Used In Select Statement Makes My Update Statement Not Work?
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
View Replies !
Using Conditional Statement In Stored Prcodure To Build Select Statement
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if
View Replies !
TSQL Or SQL CLR?
Hello Friends, I am not sure if this is the right place to post this question but if not please suggest me when it can be posted. I have been thinking of writing Stored procs in SQL CLR/ changing all of my Stored Procs to SQL CLR. Is there any thing that I need to keep in mind about the size of the sotred proc (like calculation intensive) before I do that? I mean can even change a TSQL stored proc which is relatively small Stored Proc, that simply said Select * from Customers to SQL CLR? or the SQL CLR does only useful/makes difference with calculation intensive stored procs and big stored procs? When I talked to our Architects they said every small sized stored proc can be written using SQL CLR and more over forget about the classic TSQL stored procs and get used to write SQL CLR when ever writing any database related stuff. And also there are so many articles that discussed about the advantages of SQL CLR over the TSQL but I would appreciate if some one could put few bulletted points why do you think SQL CLR is more powerful. Please advise.Thanks in advance,-L
View Replies !
TSQL Please Help
Hi I am creating some dynamic sql by passing variouse parametrs to my Stored Procedure. One of my Parameters is string has lots of values seperated by a comma to help build an 'IN' statement. SET @SQL = 'SELECT * FROM areas'SET @p1 = '10,20'If @p1 IS NOT NULLBEGINSET @sSQL = @sSQL + ' WHERE (Areas IN (''' + Replace(@p1,',',''',''') + '''))'END The above query runs perfecly well in Query Analyser, however when I put it into my ASP.NET application I get an error of "Error converting data type varchar to numeric." So I think I need to do some sort of casting or Converting but im not sure how to do it. Or do I need to use a INSTRING? I did manage to work out a method by using the follwoing SELECT * FROM Areas WHERE PATINDEX('%,' + CAST(ArType AS VARCHAR) + ',%',',' + @p1 + ',') > 0 But I cant seem to convert the above line into coherent dynamic statement. My feeble attempt is below but I keep getting errors SET @sql = @sql + ' WHERE PATINDEX(''%,'' + CAST(ArType AS VARCHAR) + '',%'','','' + @p1 + '','') > 0' IM strugging to understand all the '''. My TSQL is pretty basic, any help would be much appreciated Many thanks in advance
View Replies !
TSQL Help
A stored procedure was running slowly so I took the code, removed thesubselect and included a join, then took the max and included as partof a correlated subquery.The result is below, however, this is no improvement over the original.An advice would be greatly appreciated.SELECT FSALT.FUNDING_LINE_TYPE_ID,A.PAYMENT_PERIOD_ID,A.CASH AS CASH,A.VOLUME AS VOLUMEFROM ACTUALS AINNER JOIN (SELECT MAX(COLLECTION_PAYMENT_PERIOD_*ID) AS CPP FROMACTUALS ACT WHERE COLLECTION_PAYMENT_PERIOD_ID<=*456) AS O ON O.CPP =A.COLLECTION_PAYMENT_PERIOD_IDINNER JOIN FS_ACTUAL_LINE_TYPES FSALT ON FSALT.FS_ACTUAL_LINE_TYPE_ID =A.FS_ACTUAL_LINE_TYPE_IDINNER JOIN PAYMENT_PERIODS PP ON PP.PAYMENT_PERIOD_ID =A.PAYMENT_PERIOD_IDWHEREA.ORG_ID=24771AND A.LSC_ORG_ID=5816AND PP.FUNDING_STREAM_ID=5AND PP.FUNDING_PERIOD_ID=6GROUP BYFSALT.FUNDING_LINE_TYPE_ID,A.PAYMENT_PERIOD_ID, A.CASH, A.VOLUME
View Replies !
TSQL Help
I need help with a query that I want to incorporate into a scheduled DTS within SQL Server 2000. I have a process that brings in records from an Excel file, compares that day's records with any related information already stored in the database, then saves a table of joined records. This leaves me with any matches to the data. I need to filter out all but the most current record, which would be identified by a column named [DATE]. Ex: Table tblMatchedRecords M BLah Blah2 [DATE] 1 this that 20040101 2 this them 20040102 3 this that 20040630 In this example, I would like to have the record with [DATE] = 20040630 kept, and the others deleted. All columns do not always have to match exactly. I want to be able to specify which fields that need to match and pull only the latest date. Make sense? Can anyone offer any suggestions?
View Replies !
TSQL Help
I need help with a query that I want to incorporate into a scheduled DTS within SQL Server 2000. I have a process that brings in records from an Excel file, compares that day's records with any related information already stored in the database, then saves a table of joined records. This leaves me with any matches to the data. I need to filter out all but the most current record, which would be identified by a column named [DATE]. Ex: Table tblMatchedRecords M BLah Blah2 [DATE] 1 this that 20040101 2 this them 20040102 3 this that 20040630 In this example, I would like to have the record with [DATE] = 20040630 kept, and the others deleted. All columns do not always have to match exactly. I want to be able to specify which fields that need to match and pull only the latest date. Make sense? Can anyone offer any suggestions?
View Replies !
Tsql Help
I have a few variables declared @amount=100000 (any big int) @pamount=100000 @a1,@a2 - thorugh @25 -- each a bigint @p1,@p2,@p3 @id - int if @id is an odd number I need to randomly change @amount-@a1 (randomly to @a1 - @a25 so it ends up being any of 25 values) and then @pamount randomly= to either @p1,@p2 or @p3 Can someone help me do the sql for this? it will be within my stored procedure
View Replies !
|
TRACKER
