The Column Prefix 'MS1' Does Not Match With A Table Name Or Alias Name Used In The...
Aug 29, 2007
I have an Access database, that is in connection with sql server.
On my computer with access2007 i have no problems with viewing tables and stuff.
But on other computers with access2003 it gives an error when i use this query:
INSERT INTO [tbl_sap-staffel] ( ItemCode, CardCode, [Amount-0], [Price-0], [Amount-1], [Price-1], [Amount-2], [Price-2] )
SELECT [qry_sap-spp-0].ItemCode, [qry_sap-spp-0].CardCode, [qry_sap-spp-0].Amount, [qry_sap-spp-0].Price, [qry_sap-spp-1].Amount, [qry_sap-spp-1].Price, [qry_sap-spp-2].Amount, [qry_sap-spp-2].Price
FROM ([qry_sap-spp-0] LEFT JOIN [qry_sap-spp-1] ON ([qry_sap-spp-0].ItemCode = [qry_sap-spp-1].ItemCode) AND ([qry_sap-spp-0].CardCode = [qry_sap-spp-1].CardCode)) LEFT JOIN [qry_sap-spp-2] ON ([qry_sap-spp-1].ItemCode = [qry_sap-spp-2].ItemCode) AND ([qry_sap-spp-1].CardCode = [qry_sap-spp-2].CardCode);
It says:
ODBC call failed
[Microsoft][ODBC SQL Server Driver][SQL Server]
The column prefix 'MS1' does not match with a table name or alias name used in the query.
The column prefix 'MS2' does not match with a table name or alias name used in the query.
I have a program that connects to SQLServer 2000 through ADO connection.
the program executes the following query:
SELECT ax.AccNo, (SELECT Accounts.ProductCode FROM Accounts WHERE h.ID=Accounts.ID) As Product FROM dbo.History h LEFT OUTER JOIN dbo.AccXRef ax ON h.ID= ax.ID LEFT OUTER JOIN dbo.States ON h.[HistoryItemsub-Type] = dbo.States.Type LEFT OUTER JOIN dbo.CustXRef cx ON h.CustomerNo = cx.CustomerNo WHERE HistoryItemDate <= getdate() ORDER BY HistoryItemDate ASC
This query works in th program and in Query Analyer on my machine. However, On a different Machine (and different SQLServer) the query works in Query Analyser but does not work in the program, the following exception is thrown:
The column prefix 'h' does not match with a table name or alias name used in the query
Can someone please answer a problem that I've run into. I know that it's probably something stupid. I keep getting this error:Server: Msg 107, Level 16, State 3, Line 1The column prefix 'vFirstTimeEntered' does not match with a table name or alias name used in the query.Here is my query:-----------------------------------------------------------------Update TimeSheetSectionSet TimesheetSection.SECSTARTDT = vFirstTimeEntered.schlstuidWhere timesheetsection.schlstuid = vFirstTimeEntered.schlstuid AND timesheetsection.sectionid = vFirstTimeEntered.sectionid AND Timesheetsection.secstartdt < '2005-08-01'------------------------------------------------------------------vFirstTimeEntered is a view that I created.Do I need a sub query? I know that if this was a select query I'd need to put vFirstTimeEntered in the FROM part but I don't know where it should go here.Thanks for any assistance.Scott
I get the error "The column prefix 'contacts' does not match with a table name or alias used in the query".I am trying to obtain all fields from the communications table whether it is used or NULL.
I have data in a table (@Outer) that I am matching to a lookup table (@Inner) which contains multiple "matches" where nulls can match any value. By sorting the inner table and grabbing the top record, I find the "best" match. I know the sort and the null matches work but I don't understand why the correlated sub query below doesn't understand that the OJ prefix refers to the outer table.DECLARE @Outer TABLE ( OuterID int IDENTITY (1, 1) NOT NULL, MethodID int NULL, CompID int NULL, FormID int NULL, InnerID int NULL )
-- UPDATE Outer Table with best match from Inner table UPDATE @Outer SET InnerID = IJ.InnerID FROM @Outer OJ INNER JOIN ( SELECT TOP 1 I.* FROM @Inner I WHERE IsNull(I.MethodID, OJ.MethodID) = OJ.MethodID AND IsNull(I.CompID, OJ.CompID) = OJ.CompID AND IsNull(I.FormID, OJ.FormID) = OJ.FormID ORDER BY I.MethodID DESC, I.CompID DESC, I.FormID DESC ) IJ ON OJ.MethodID = IsNull(IJ.MethodID, OJ.MethodID) AND OJ.CompID = IsNull(IJ.CompID, OJ.CompID) AND OJ.FormID = IsNull(IJ.FormID, OJ.FormID) SELECT * FROM @Outer The result should be OuterID 1 matched to Inner ID 3 and OuterID 2 matched to Inner ID 5. Can anyone help me? Thanks in advance.
column prefix 'a' does not match with a table name. Any ideas? This happens when i run the DTS package.
SELECT'Dublin' + SPACE(1) + CONVERT(VARCHAR(10), RTRIM(FS6.QB@SCS)) + REPLICATE(SPACE(1), 10 - LEN(CONVERT(VARCHAR(10),RTRIM(FS6.QB@SCS)))) + CONVERT(VARCHAR(10),a.IHIVDT,20) + CASE WHEN RM.RMRSSC IN ('01', '03', '04', '05', '14', '28', '34', '35', '36') AND RS.RSRSCL = 'FG' THEN 'CLOSURE' + SPACE(3) + 'TAPE' + SPACE(21) WHEN RM.RMRSSC IN ('02') AND RS.RSRSCL = 'FG' THEN 'CLOSURE' + SPACE(3) + 'ADHESIVE' + SPACE(17) WHEN RM.RMRSSC IN ('06', '07') AND RS.RSRSCL = 'FG' THEN 'UNITIZING' + SPACE(1) + 'STRETCH' + SPACE(18) WHEN RM.RMRSSC IN ('08') AND RS.RSRSCL = 'FG' THEN 'DISPLAY' + SPACE(3) + 'SHRINK' + SPACE(19) WHEN RM.RMRSSC IN ('09', '10', '15', '39', '40') AND RS.RSRSCL = 'FG' THEN 'EQUIPMENT' + SPACE(1) + SPACE(25) WHEN RM.RMRSSC IN ('19', '22', '29', '30') AND RS.RSRSCL = 'FG' THEN 'SPECIALTY' + SPACE(1) + SPACE(25) WHEN RM.RMRSSC IN ('37') AND RS.RSRSCL = 'FG' THEN 'CUSHIONING' + 'MAILERS' + SPACE(18) WHEN RM.RMRSSC IN ('38') AND RS.RSRSCL = 'FG' THEN 'CUSHIONING' + 'BUBBLE' + SPACE(19) WHEN RM.RMRSSC IN ('11') AND RS.RSRSCL = 'RM' THEN 'SPECIALTY' + SPACE(1) + SPACE(25) WHEN RM.RMRSSC IN ('14', '17') AND RS.RSRSCL = 'RM' THEN 'EQUIPMENT' + SPACE(1) + SPACE(25) WHEN RM.RMRSSC IN ('12', '13', '15', '16', '51') AND RS.RSRSCL = 'RM' THEN 'SPARE PART' + SPACE(25) WHEN RM.RMRSSC IN ('08', '09') AND RS.RSRSCL = 'WP' THEN 'EQUIPMENT' + SPACE(1) + SPACE(25) ELSE SPACE(35) END + CONVERT(VARCHAR(50), RTRIM(RM.RMDESC)) + REPLICATE(SPACE(1), 50 - LEN(CONVERT(VARCHAR(50), RTRIM(RM.RMDESC)))) + CASE WHEN RM.RMRSSC IN ('06','07') AND RS.RSRSCL = 'FG' THEN CASE WHEN RM.RMPRCL IN ('134', '135', '137', '159', '1073') THEN 'STRETCH-MACHINE' + SPACE(10) ELSE 'EXCLUDED' + SPACE(17) END WHEN RM.RMRSSC IN ('01') AND RS.RSRSCL = 'FG' THEN CASE WHEN RM.RMPRCL IN ('173', '175') THEN 'HOT MELT HAND ROLL' + SPACE(7) WHEN RM.RMPRCL IN ('174', '174L') THEN 'CARTON SEALING' + SPACE(11) ELSE 'EXCLUDED' + SPACE(17) END WHEN RM.RMRSSC IN ('05', '28') AND RS.RSRSCL = 'FG' THEN 'CARTON SEALING' + SPACE(11) WHEN RM.RMRSSC IN ('03', '04', '14', '34', '35') AND RS.RSRSCL = 'FG' THEN 'INDUSTRIAL TAPES' + SPACE(9) ELSE 'EXCLUDED' + SPACE(17) END + CASE WHEN RM.RMRSSC IN ('06','07') AND RS.RSRSCL = 'FG' THEN CASE WHEN RM.RMPRCL IN ('134', '135', '137', '159', '1073') THEN 'STRETCH-MACHINE' + SPACE(10) ELSE 'EXCLUDED' + SPACE(17) END WHEN RM.RMRSSC IN ('01') AND RS.RSRSCL = 'FG' THEN CASE WHEN RM.RMPRCL IN ('173', '175') THEN 'HOT MELT HAND ROLL' + SPACE(7) WHEN RM.RMPRCL IN ('174', '174L') THEN 'HOT MELT MACHINE ROLL' + SPACE(4) ELSE 'EXCLUDED' + SPACE(17) END WHEN RM.RMRSSC IN ('03', '04', '14', '34', '35') AND RS.RSRSCL = 'FG' THEN 'INDUSTRIAL TAPES' + SPACE(9) WHEN RM.RMRSSC IN ('05') AND RS.RSRSCL = 'FG' THEN 'WATER ACTIVATED' + SPACE(10) WHEN RM.RMRSSC IN ('28') AND RS.RSRSCL = 'FG' THEN 'NATURAL RUBBER' + SPACE(11) ELSE 'EXCLUDED' + SPACE(17) END + CASE WHEN RM.RMRSSC IN ('06','07') AND RM.RMPRCL IN ('134', '135', '137', '159', '1073') AND RS.RSRSCL = 'FG' THEN CASE WHEN a.IHCRMM = 0 THEN CASE WHEN (IV.IVQYSD * RM.RMNETW)IS NULL THEN '000000000000000' WHEN (IV.IVQYSD * RM.RMNETW) > 0 THEN '+' + REPLICATE('0', 23 - LEN(IV.IVQYSD * RM.RMNETW)) + SUBSTRING((CONVERT(VARCHAR(20), IV.IVQYSD * RM.RMNETW)), 1, LEN(IV.IVQYSD * RM.RMNETW) - 13) + SUBSTRING((CONVERT(VARCHAR(20), IV.IVQYSD * RM.RMNETW)), LEN(IV.IVQYSD * RM.RMNETW) - 11, 4) WHEN (IV.IVQYSD * RM.RMNETW) = 0 THEN '000000000000000' END WHEN a.IHCRMM IN (1,3) THEN '000000000000000' WHEN a.IHCRMM = 2 THEN CASE WHEN (IV.IVQYSD * RM.RMNETW)IS NULL THEN '000000000000000' WHEN (IV.IVQYSD * RM.RMNETW) > 0 THEN '-' + REPLICATE('0', 23 - LEN(IV.IVQYSD * RM.RMNETW)) + SUBSTRING((CONVERT(VARCHAR(20), IV.IVQYSD * RM.RMNETW)), 1, LEN(IV.IVQYSD * RM.RMNETW) - 13) + SUBSTRING((CONVERT(VARCHAR(20), IV.IVQYSD * RM.RMNETW)), LEN(IV.IVQYSD * RM.RMNETW) - 11, 4) WHEN (IV.IVQYSD * RM.RMNETW) = 0 THEN '000000000000000' END ELSE '000000000000000' END WHEN RM.RMRSSC IN ('01','03', '04', '05', '14', '28', '34', '35') AND RS.RSRSCL = 'FG' THEN CASE WHEN a.IHCRMM = 0 THEN CASE WHEN LTRIM(RTRIM(IV.IVUMSR)) IS NULL THEN '000000000000000' WHEN LTRIM(RTRIM(IV.IVUMSR)) IS NOT NULL THEN '+' + REPLICATE('0',17-LEN(CONVERT(VARCHAR(15),CONVERT(NUMERIC(13,6),(SELECT UC.UMCONF FROM IPGDB.DBO.UMCONV UC WHERE UC.UMRESR = IV.IVRESR AND UC.UMFR = IV.IVUMSR AND UC.UMTO = 'M2' AND UMWHSE = '') * IV.IVQYSD)))) + SUBSTRING(CONVERT(VARCHAR(15),CONVERT(NUMERIC(13,6),(SELECT UC.UMCONF FROM IPGDB.DBO.UMCONV UC WHERE UC.UMRESR = IV.IVRESR AND UC.UMFR = IV.IVUMSR AND UC.UMTO = 'M2' AND UMWHSE = '') * IV.IVQYSD)), 1, LEN((SELECT UC.UMCONF FROM IPGDB.DBO.UMCONV UC WHERE UC.UMRESR = IV.IVRESR AND UC.UMFR = IV.IVUMSR AND UC.UMTO = 'M2' AND UMWHSE = '') * IV.IVQYSD) - 13) + SUBSTRING(CONVERT(VARCHAR(15),CONVERT(NUMERIC(13,6),(SELECT UC.UMCONF FROM IPGDB.DBO.UMCONV UC WHERE UC.UMRESR = IV.IVRESR AND UC.UMFR = IV.IVUMSR AND UC.UMTO = 'M2' AND UMWHSE = '') * IV.IVQYSD)), LEN((SELECT UC.UMCONF FROM IPGDB.DBO.UMCONV UC WHERE UC.UMRESR = IV.IVRESR AND UC.UMFR = IV.IVUMSR AND UC.UMTO = 'M2' AND UMWHSE = '') * IV.IVQYSD) - 11, 4) ELSE '000000000000000' END WHEN a.IHCRMM IN (1,3) THEN '000000000000000' WHEN a.IHCRMM = 2 THEN CASE WHEN LTRIM(RTRIM(IV.IVUMSR)) IS NULL THEN '000000000000000' WHEN LTRIM(RTRIM(IV.IVUMSR)) IS NOT NULL THEN '-' + REPLICATE('0',17-LEN(CONVERT(VARCHAR(15),CONVERT(NUMERIC(13,6),(SELECT UC.UMCONF FROM IPGDB.DBO.UMCONV UC WHERE UC.UMRESR = IV.IVRESR AND UC.UMFR = IV.IVUMSR AND UC.UMTO = 'M2' AND UMWHSE = '') * IV.IVQYSD)))) + SUBSTRING(CONVERT(VARCHAR(15),CONVERT(NUMERIC(13,6),(SELECT UC.UMCONF FROM IPGDB.DBO.UMCONV UC WHERE UC.UMRESR = IV.IVRESR AND UC.UMFR = IV.IVUMSR AND UC.UMTO = 'M2' AND UMWHSE = '') * IV.IVQYSD)), 1, LEN((SELECT UC.UMCONF FROM IPGDB.DBO.UMCONV UC WHERE UC.UMRESR = IV.IVRESR AND UC.UMFR = IV.IVUMSR AND UC.UMTO = 'M2' AND UMWHSE = '') * IV.IVQYSD) - 13) + SUBSTRING(CONVERT(VARCHAR(15),CONVERT(NUMERIC(13,6),(SELECT UC.UMCONF FROM IPGDB.DBO.UMCONV UC WHERE UC.UMRESR = IV.IVRESR AND UC.UMFR = IV.IVUMSR AND UC.UMTO = 'M2' AND UMWHSE = '') * IV.IVQYSD)), LEN((SELECT UC.UMCONF FROM IPGDB.DBO.UMCONV UC WHERE UC.UMRESR = IV.IVRESR AND UC.UMFR = IV.IVUMSR AND UC.UMTO = 'M2' AND UMWHSE = '') * IV.IVQYSD) - 11, 4) ELSE '000000000000000' END ELSE SPACE(15) END ELSE SPACE(15) END + CASE WHEN a.IHCRMM NOT IN (1,2) THEN CASE WHEN IVSALT - (IVDIST + IVADIT + IVODIT + IVOADT) IS NULL THEN SPACE(13) WHEN IVSALT - (IVDIST + IVADIT + IVODIT + IVOADT) > 0 THEN '+' + REPLICATE('0', 13 - LEN(IVSALT - (IVDIST + IVADIT + IVODIT + IVOADT))) + SUBSTRING((CONVERT(VARCHAR(13), IVSALT - (IVDIST + IVADIT + IVODIT + IVOADT))), 1, LEN(IVSALT - (IVDIST + IVADIT + IVODIT + IVOADT)) - 3) + RIGHT(CONVERT(VARCHAR(13), (IVSALT - (IVDIST + IVADIT + IVODIT + IVOADT))),2) WHEN IVSALT - (IVDIST + IVADIT + IVODIT + IVOADT) = 0 THEN '0000000000000' ELSE SPACE(13) END ELSE CASE WHEN IVSALT - (IVDIST + IVADIT + IVODIT + IVOADT) IS NULL THEN SPACE(13) WHEN IVSALT - (IVDIST + IVADIT + IVODIT + IVOADT) > 0 THEN '-' + REPLICATE('0', 13 - LEN(IVSALT - (IVDIST + IVADIT + IVODIT + IVOADT))) + SUBSTRING((CONVERT(VARCHAR(13), IVSALT - (IVDIST + IVADIT + IVODIT + IVOADT))), 1, LEN(IVSALT - (IVDIST + IVADIT + IVODIT + IVOADT)) - 3) + RIGHT(CONVERT(VARCHAR(13), (IVSALT - (IVDIST + IVADIT + IVODIT + IVOADT))),2) WHEN IVSALT - (IVDIST + IVADIT + IVODIT + IVOADT) = 0 THEN '0000000000000' ELSE SPACE(13) END END + CASE WHEN a.IHCRMM IN (0) THEN CASE WHEN IV.IVQYSD IS NULL THEN '00000000000' WHEN IV.IVQYSD > 0 THEN '+' + REPLICATE('0', 10 - LEN(CONVERT(INT,IV.IVQYSD))) + CONVERT(VARCHAR(11),CONVERT(INT,IV.IVQYSD)) ELSE '-' + REPLICATE('0', 10 - LEN(CONVERT(INT,IV.IVQYSD))) + CONVERT(VARCHAR(11),CONVERT(INT,IV.IVQYSD)) END WHEN a.IHCRMM IN (1,3) THEN '00000000000' WHEN a.IHCRMM IN (2) THEN CASE WHEN IV.IVQYSD IS NULL THEN '00000000000' WHEN IV.IVQYSD > 0 THEN '-' + REPLICATE('0', 10 - LEN(CONVERT(INT,IV.IVQYSD))) + CONVERT(VARCHAR(11),CONVERT(INT,IV.IVQYSD)) ELSE '-' + REPLICATE('0', 10 - LEN(CONVERT(INT,IV.IVQYSD))) + CONVERT(VARCHAR(11),CONVERT(INT,IV.IVQYSD)) END ELSE '00000000000' END as 'XPEDXRESULTS' FROMIPGDB.dbo.INVHDR AS a LEFT OUTER JOIN IPGDB.dbo.FSA06 AS FS6 ON a.IHSOCS = FS6.QB@CUS AND a.IHICON = FS6.QB@CMP LEFT OUTER JOIN IPGDB.DBO.INVDET AS IV ON a.IHICON = IV.IVICON AND a.IHINVA = IV.IVINVA LEFT OUTER JOIN IPGDB.DBO.RESMST AS RM ON RM.RMRESC = IV.IVRESR LEFT OUTER JOIN IPGDB.DBO.RESUBC AS RS ON RS.RSRSSC = RM.RMRSSC AND RS.RSRSCL = RM.RMRSCL WHEREFS6.QB@BGP = '133' AND MONTH(a.IHIVDT) = ? AND YEAR(a.IHIVDT) = ? AND IV.IVRESR IS NOT NULL
SQL 2000 I am testing a query for use in Crystal Reports. It was copied from an existing query with the necessary adjustments. The first part of it works correctly;
SELECT NA.* into #cl_temp FROM OLT.dbo.NACBTR NA WHERE NA.CourseCode in ('RGF00001','RGF00002','RGF00005','RGF00006', 'RGF00038','RGF00039','RGF00040','RGF00041','RGF00042','RGF00043') And NA.completedDate >= '01/01/2006' and NOT EXISTS (SELECT * FROM hrdw.dbo.E_View EV WHERE NA.ssn = EV.ssn)
but when I add the second line;
select #cl_temp.*, ISNULL((select 1 from #cl_temp where #cl_temp.coursecode = 'RGF00001'),0) as fire_yes into #oshasafety_temp
I receive the error message: The column prefix '#cl_temp' does not match with a table name or alias name used in the query.
I have an Access database, that is in connection with sql server. On my computer with access2007 i have no problems with viewing tables and stuff.
But on other computers with access2003 it gives an error when i use this query:
INSERT INTO [tbl_sap-staffel] ( ItemCode, CardCode, [Amount-0], [Price-0], [Amount-1], [Price-1], [Amount-2], [Price-2] ) SELECT [qry_sap-spp-0].ItemCode, [qry_sap-spp-0].CardCode, [qry_sap-spp-0].Amount, [qry_sap-spp-0].Price, [qry_sap-spp-1].Amount, [qry_sap-spp-1].Price, [qry_sap-spp-2].Amount, [qry_sap-spp-2].Price FROM ([qry_sap-spp-0] LEFT JOIN [qry_sap-spp-1] ON ([qry_sap-spp-0].ItemCode = [qry_sap-spp-1].ItemCode) AND ([qry_sap-spp-0].CardCode = [qry_sap-spp-1].CardCode)) LEFT JOIN [qry_sap-spp-2] ON ([qry_sap-spp-1].ItemCode = [qry_sap-spp-2].ItemCode) AND ([qry_sap-spp-1].CardCode = [qry_sap-spp-2].CardCode);
It says:quote: ODBC call failed [Microsoft][ODBC SQL Server Driver][SQL Server] The column prefix 'MS1' does not match with a table name or alias name used in the query. The column prefix 'MS2' does not match with a table name or alias name used in the query.
I get the error below from the following SQL. This SQL worked until I tried adding a third table "ship_to_salesrep". If I comment-out the third join and the last condition it works.
I have tried putting the "r" table in the FROM statement and still it does not work. Everything looks right to me -- what am I doing wrong?
Server: Msg 107, Level 16, State 2, Line 2 The column prefix 'h' does not match with a table name or alias name used in the query.
SELECT distinct m.inv_mast_uid, CONVERT(DECIMAL(10,4),0.00) as 'inv_cost', CONVERT(DECIMAL(10,4),0.00) as 'oe_cost', l.invoice_line_type, s.cost, h.invoice_no, h.order_no, h.order_date, h.invoice_date, h.customer_id, h.ship_to_id, h.ship2_name, h.ship2_address2, h.ship2_city, h.ship2_state, h.ship2_postal_code, h.terms_desc, h.po_no, h.salesrep_id, h.salesrep_name, h.period, h.year_for_period, h.ship_date, h.total_amount, h.amount_paid, h.terms_taken, h.allowed, h.paid_in_full_flag, h.last_maintained_by, h.printed, h.printed_date, h.shipping_cost, h.invoice_reference_no, h.invoice_adjustment_type, h.memo_amount, h.bad_debt_amount, h.invoice_class, h.period_fully_paid, h.year_fully_paid, h.approved, h.other_charge_amount, h.tax_amount, h.original_document_type, h.date_paid, h.print_flag, h.print_date, h.customer_id_number, h.date_created, h.date_last_modified, h.consolidated, h.sold_to_ah_uid, h.sold_to_customer_id, h.invoice_batch_uid, h.sales_location_id, h.source_type_cd, l.qty_requested, l.qty_shipped, l.unit_of_measure, l.item_id, l.item_desc, l.unit_price, l.extended_price, l.gl_revenue_account_no, l.gl_salse_tax_account_no, l.pricing_quantity, l.line_no, l.sales_cost, l.commission_cost, l.other_cost, l.other_charge_item, l.exceptional_sales, l.pricing_unit, l.invoice_line_uid, l.invoice_line_uid_parent into jch1.dbo.sales_history_invoices FROM invoice_hdr h, invoice_line l left join inv_mast m on l.item_id = m.item_id left join inventory_supplier s on m.inv_mast_uid = s.inv_mast_uid left join ship_to_salesrep r on h.ship_to_id = r.ship_to_id WHERE l.invoice_no = h.invoice_no and h.invoice_date >= '2006-07-01' and h.invoice_date < '2006-08-01' and l.invoice_line_type = 0 and m.inv_mast_uid is not NULL and r.primary_salesrep_flag = 'Y';
For our ETL process, we maintain a TransformationList table that has the source view and the destination table. Data is copied from the view into the table (INSERT INTO). I am trying to find column names in the Views that are not column names in the associated Table.
In the below example, want to end up with three records:
I have it almost working, except that there is a table, ChangeColPrefix table, that is used by the ETL process to change some of the view's column name prefixes. Some of the source views have column names with prefixes that do not match the destination table column names. Say view SouthBase has all the column names prefixed with SB - like SBAcct, SBName. And the Destination table of Area District has ADAcct, ADName. There would be a row in the ChangeColPrefix for SouthBase, SB, AD, 1, 2 that would be used by the ETL process to create the INSERT INTO Area District From SouthBase.
I need to use this ChangeColPreifx to find my unmatching columns between my source views and destination tables. With out that table SBAcct and SBName from SouthBase will not appear to match the columns of ADAcct and ADName, but they do match.
I want to end up with these three records as non-matching:
View1, Column4 View2, Column4 View2, Column5
View1 has Salumn2 and View2 has Salumn5, and they must be changed to Column2 and Column5 as per the ChangeColPrefix table before running the Select from INFORMATION_SCHEMA.COLUMNS EXCEPT Select from INFORMATION_SCHEMA.COLUMNS looking for unmatched columns.
/***** Set Up Test Data *****/ -- Create 2 test views IF EXISTS(SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[View1]')) DROP VIEW dbo.[View1] GO CREATE VIEW View1 AS SELECT '1' AS Column1 , '2' AS Salumn2 , '4' AS Column4;
The SP UserPersist_GetByCriteria does a "SELECT * FROM tbl_User WHERE gender = @Gender AND culture = @Culture", so why am I receiving this error when both tables have the same structure?
The error is being reported as coming from UserPersist_GetByCriteria on the "SELECT * FROM tbl_User" line.
I have a table names Alert_Event and a new column named BSP_Phone has been added to the table. I am trying to set NULL values to the column and I get the below error. I am setting null values in the bolded text in the query.
Error Message:
Msg 213, Level 16, State 1, Procedure SaveBSPOutageInfo, Line 22 Column name or number of supplied values does not match table definition.USE [gg] GO
/****** Object: StoredProcedure [dbo].[SaveBSPOutageInfo] Script Date: 10/17/2013 19:01:20 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[SaveBSPOutageInfo] @eventCreatedDate DATETIME, @eventOrigin varchar(10),
Table2 has three columns (i.e. Date, Count and Rule Type). Column “Rule Type “has a default value which is “XYZ”..Now I want to insert Data from Table1 to Table2. I am using following query:-
Column name or number of supplied values does not match table definition.I am using SQL 2012. I understand Table1 has 2 Columns but Table2 has 3 Columns. Is there anyway, I can move data from source table to Destination table where Destination Table has more number of Columns?
I want to compare two columns in the same table called start date and end date for one clientId.if clientId is having continuous refenceid and sartdate and enddate of reference that I don't need any caseopendate but if clientID has new reference id and it's start date is not continuous to its previous reference id then I need to set that start date as caseopendate.
Is there a way to avoid entering column names in the excel template for me to create an excel file froma dynamic excel using openrowset. I have teh following code but it works fien when column names are given ahead of time. If I remove the column names from the template and just to Select * from the table and Select * from sheet1 then it tells me that column names donot match. Server: Msg 213, Level 16, State 5, Line 1Insert Error: Column name or number of supplied values does not match table definition. here is my code... SET @sql1='select * from table1'SET @sql2='select * from table2' IF @File_Name = '' Select @fn = 'C:Test1.xls' ELSE Select @fn = 'C:' + @File_Name + '.xls' -- FileCopy command string formation SELECT @Cmd = 'Copy C:TestTemplate1.xls ' + @fn -- FielCopy command execution through Shell Command EXEC MASTER..XP_CMDSHELL @cmd, NO_OUTPUT -- Mentioning the OLEDB Rpovider and excel destination filename set @provider = 'Microsoft.Jet.OLEDB.4.0' set @ExcelString = 'Excel 8.0;HDR=yes;Database=' + @fn exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [Sheet1$]'') '+ @sql1 + '') exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [Sheet2$]'') '+ @sql2 + ' ')
ALTER procedure [dbo].[MyPro](@StartRowIndex int,@MaximumRows int) As Begin Declare @Sel Nvarchar(2000)set @Sel=N'Select *,Row_number() over(order by myId) as ROWNUM from MyFirstTable Where ROWNUM Between ' + convert(nvarchar(15),@StartRowIndex) + ' and ('+ convert(nvarchar(15),@StartRowIndex) + '+' + convert(nvarchar(15),@MaximumRows) + ')-1' print @Sel Exec Sp_executesql @Sel End
--Execute Mypro 1,4 --->>Here I Executed Error Select *,Row_number() over(order by myId) as ROWNUM from MyFirstTable Where ROWNUM Between 1 and (1+4)-1 Msg 207, Level 16, State 1, Line 1 Invalid column name 'ROWNUM'. Msg 207, Level 16, State 1, Line 1 Invalid column name 'ROWNUM Procedure successfully created but giving error while Excuting'. Please anybody give reply Thanks
Using SQL Server 2000. How can I refer to one alias in another column?E.g., (this a contrived example but you get the idea)SELECT time, distance, (distance / time) AS speed, (speed / time) AS acceleration FROM dataNote how the speed alias is used in the definition of acceleration alias but this doesn't seem to work.
in Access 2000 and cannot find the table on my table list, it's not on my enterprise manager either. My guess is that maybe it has something to do with the prefix #? So I started searching for "Create table #" on google and dbforums and cannot find anything. Can someone tell me what's special about having a #prefix on a table and where can I find more information about it?
Hi,I would like to know details about the table name starts with prefixin sql server 2000.Actually i'm working on existing code.The existing code insert a record into a table, but the table name inthe code and table name in database are differentTable name in database : tbl_mmm_oxIn coding they are using table name as mmm only, the records areproperly inserted into mmm tableis it possible?The sample code is like that(using ado object)oCmd.CommandText = "INSERT INTO mmm (no,name) values (1,"mm")"The above code is perfectly working and inserting record intotbl_mmm_ox.Could anybody explain how is it possible?whether we can leave the prefix(tbl) and suffix(ox) and sql servertake care of this?Thanks & Regards,Mani
I can run a select to retrieve data using a prefix 'a' for the specific table involved. However when I try to run a delete using the same criteria it fails telling me
Msg 102, Level 15, State 1,.......Line 1
Incorrect syntax near 'a'
The Select statement looks like:
select count(*) from schema.table a where a.customer_id=1234
The Delete looks like:
delete from schema.table a where a.customer_id=1234
What am I doing wrong here? and how can I prefix the table, because the command I want to run is much more complicated than the example above and it needs the prefix
I have two linked tables from two different databases, there is a column "product" on each table however the product on one table has a Prefix so not a direct match. How can I join these tables ? In the query I have used product2: Replace([scheme_pos.product],"-B","") then tried Joining on product2 but it says JOIN not supported.
I was wondering if there is a way that you can set the alias name of a column to a value that resides in another table instead of the alias being a static value that you type in.
If anyone has any ideas on ho i can accomplish this i would greatly appreciate it.
I'm using SQL Server 2005 and are having some troubble with sorting a paged result set. I'm using the OVER Clause to achieve the sorting and paging and have the following query:1 WITH ProjectList AS 2 ( 3 SELECT 4 Id, 5 Name, 6 Created, 7 (SELECT COUNT(*) FROM UserProjects WHERE ProjectId = p.Id) AS NumberOfUsers, 8 ROW_NUMBER() OVER (ORDER BY Id) AS 'RowNumber' 9 FROM Projects p 10 ) 11 SELECT * 12 FROM ProjectList 13 WHERE RowNumber BETWEEN 50 AND 60;
This works fine, and give me the results i want. The problem occurs when I want to sort by "NumberOfUsers" which is the results of a sub query.When i say "ORDER BY NumberOfUsers" instead of Id on line 8, I get the following error: Msg 207, Level 16, State 1, Line 10Invalid column name 'NumberOfUsers'. I read this in the documentation: When used in the context of a ranking window function, <ORDER BY Clause> can only refer to columns made available by the FROM clause. An integer cannot be specified to represent the position of the name or alias of a column in the select list. <ORDER BY Clause> cannot be used with aggregate window functions. So this means that what I'm trying to do is not possible. How can I then sort by NumberOfUsers? Is there any other way to achieve this
Hi All, I am currently transferring my Access application to SQL Server. Access allows you to declare and use aliases in the query at the same time.
e.g. Select field1 as Alias1, field2 as Alias2, Alias1 & " " & Alias2 as Alias3 from table1;
In Access the above query will execute perfectly, no problem. However in SQL Server, if you try to run the same query it will give an error "Invalid column name Alias1" meaning that SQL Server is searching for Alias1 as a field in the table, not as an alias from the query.
My question is does SQL Server have a facility to declare and use alias directly as in Access and if no, is there a workaround?
Is there a way to use a column alias in an another calculation within thesame query? Since I am using some long and complex logic to compute total1and total2, I don't want to repeat the same logic to compute the ratio ofthose two columns. I know that I can do a nested query, but that seems toolengthy as well since I actually have many, many columns.selecttotal1 = sum(case(long complex logic)),total2 = sum(case(another long complex logic)),ratio = total1/total2
