Need To Create Table(s) To Match This Xml
Dec 6, 2005
Hi,
I am not an expert with SQL2000 and need to create table(s) to match this xml format(if possible):
<?xml version="1.0" encoding="UTF-8" ?>
<MenuData xmlns="http://tempuri.org/MenuDataDataSet.xsd">
<Menu Id="RootMenu">
<Item1 Caption="About TTF FIS" Url="" Target="" Id="AboutItem">
<ChildMenu1 Id="AboutMenu">
<ChildItem Caption="FAQs" Url="Faqs.aspx" Target="_self" Id="FaqsItem" />
</ChildMenu1>
</Item1>
<Item2 Caption="Resource Solutions" Url="" Target="" Id="BizSolItem">
<ChildMenu2 Id="ResourceMenu">
<ChildItemMenu2 Caption="Resource Tracking" Url="" Target="" Id="AccItem">
<ChildMenuChild2 Id="AccMenu">
<ChildItemChild2 Caption="Resource_A" Url="Resource_A.aspx" Target="_self" Id="AItem" />
<ChildItemChild2 Caption="Resource_B" Url="Resource_B.aspx" Target="_self" Id="BItem" />
<ChildItemChild2 Caption="Resource_C" Url="Resource_C.aspx" Target="_self" Id="CItem" />
<ChildItemChild2 Caption="Resource_D" Url="Resource_D.aspx" Target="_self" Id="DItem" />
</ChildMenuChild2>
</ChildItemMenu2>
<ChildItemMenu2 Caption="Engineering" Url="Engineering.aspx" Target="_self" Id="EngItem" />
<ChildItemMenu2 Caption="Design" Url="Design.aspx" Target="_self" Id="MarItem" />
<ChildItemMenu2 Caption="Deployment" Url="Fire.aspx" Target="_self" Id="FireItem" />
</ChildMenu2>
</Item2>
<Item3 Caption="MES Solutions" Url="" Target="" Id="PerSolItem">
<ChildMenu3 Id="PerSolMenu">
<ChildItemMenu3 Caption="MES First Solution" Url="Education.aspx" Target="_self" Id="EduItem" />
<ChildItemMenu3 Caption="MES Second Solution" Url="Household.aspx" Target="_self" Id="HousItem" />
<ChildItemMenu3 Caption="MES Third Solution" Url="PersonalAccidents.aspx" Target="_self" Id="PerAcItem" />
<ChildItemMenu3 Caption="MES Fourth Solution" Url="Protection.aspx" Target="_self" Id="ProtItem" />
<ChildItemMenu3 Caption="MES Fifth Solution" Url="Retirement.aspx" Target="_self" Id="RetItem" />
<ChildItemMenu3 Caption="MES Sixth Solution" Url="Small Commercial Business.aspx" Target="_self"
Id="SmallBizItem" />
<ChildItemMenu3 Caption="MES Seventh Solution" Url="Term Life.aspx" Target="_self" Id="TermItem" />
<ChildItemMenu3 Caption="Others" Url="Yachts.aspx" Target="_self" Id="YacItem" />
</ChildMenu3>
</Item3>
<Item4 Caption="Our Company" Target="" Url="" Id="ComItem">
<ChildMenu4 Id="ComMenu">
<ChildItemMenu4 Caption="Identity" Url="Identity.aspx" Target="_self" Id="IdItem" />
<ChildItemMenu4 Caption="Promise" Url="Promise.aspx" Target="_self" Id="PromItem" />
</ChildMenu4>
</Item4>
</Menu>
</MenuData>
any help appriciated
View 2 Replies
ADVERTISEMENT
Feb 13, 2008
I've been through a number of tutorials on how to enable row-level security based on a userID, but my problem is more complicated and I do not have sufficient understanding of report models to guess.
My security information is defined in a table within my database. It contains a username and an account mask. An account mask maps to 1 or more account codes contained in the other data tables in my report model. A user may have more than one account mask defined for his account.
I understand the concept of directly mapping the logged-in user to a field containing a matching username. Is it possible to do a two-step mapping, so that based on the user ID I can get the account mask(s) and then evaluate which account codes match the mask(s)?
Or is there a different/better way to set this up? Defining SQL roles/groups is not an option, because of some compatibility issues with external systems.
So far, because my security table has no defined relationship with the data tables, I have not even been able to get it into my report model (Would love any suggestions on that one, too.)
I'd appreciate any ideas or suggestions - even if only something to investigate.
Thanks,
Sarah
View 1 Replies
View Related
Jun 6, 2005
Hello,
I am receiving the following error:
Column name or number of supplied values does not match table definition
I am trying to insert values into a temp table, using values from the table I copied the structure from, like this:
SELECT TOP 1 * INTO #tbl_User_Temp FROM tbl_User
TRUNCATE TABLE #tbl_User_Temp
INSERT INTO #tbl_User_Temp EXECUTE UserPersist_GetUserByCriteria @Gender = 'Male', @Culture = 'en-GB'
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.
Thanks,
Greg.
View 2 Replies
View Related
Jun 25, 2015
I have a table in different databases with the same name. My goal is to compare the two tables, and insert a record into the second table where there is not a match in the first table. So far, my query looks like the following:
SELECT [metal] FROM [ProductionDatabase].[dbo].[Metalurgy]
EXCEPT
SELECT [metal] FROM [TestDatabase].[dbo].[Metalurgy]
This gives me a list of records from [Production].[dbo].[Metalurgy] which do not reside in [TestDatabase].[dbo].[Metalurgy]. Now, I need to use that list to insert missing records into [TestDatabase].[dbo].[Metalurgy]. How can I modify the above code to INSERT the missing records?
View 4 Replies
View Related
Oct 28, 2007
Find value match in SQL table
Is there any application that can compare two tables and find the similarities (there is no high rate of exact match on the field values but there are similarities)?
View 2 Replies
View Related
Dec 30, 2004
Does that make sense? I have 2 tables, one is an older version, one newer. The newer version has some additional fields, indexes, etc. I'd love to have a tool to run against them that would generate an ALTER TABLE script to modify the old table so that it matches the new table.
Is there anything like that out there?
View 4 Replies
View Related
Aug 10, 2007
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
=============================
http://www.sqlserverstudy.com
View 5 Replies
View Related
Jun 20, 2000
I am getting an insert error with the following SP. I don't have to pass the CampID because it is an IDENTITY field. The error says "number of supplied values does not match table definition."
Do I pass in the CampID to the SP and allow nulls? Thanks in advance
Nathan
CREATE PROCEDURE sp_CampReg1
@UserNamevarchar(15),
@Passwordvarchar(15),
@CampNamevarchar(50),
@Hostvarchar(50),
@Directorvarchar(25),
@Contactvarchar(25),
@Addressvarchar(30),
@Cityvarchar(25),
@Statevarchar(20),
@Zipvarchar(15),
@Countryvarchar(20) = NULL,
@Phonevarchar(20) = NULL,
@AlternatePhonevarchar(20) = NULL,
@Faxvarchar(20) = NULL,
@ContactEmailvarchar(20),
@AdminEmailvarchar(20),
@URLvarchar(50) = NULL,
@CampTypeint,
@CampProfileText =NULL,
@CampIDintOUTPUT
AS
INSERT INTO TempCampSignup
VALUES
(
@UserName,
@Password,
@CampName,
@Host,
@Director,
@Contact,
@Address,
@City,
@State,
@Zip,
@Country,
@Phone,
@AlternatePhone,
@Fax,
@ContactEmail,
@AdminEmail,
@URL,
@CampType,
@CampProfile
)
SELECT @CampID = @@IDENTITY
View 1 Replies
View Related
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.
And it will give this error 6 times.
I dont know what to do.
Can anybody help me?
View 4 Replies
View Related
Mar 17, 2004
Hi Guys,
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
Any help is greatly appreciated..
thanx in-advance,
TNT:)
View 5 Replies
View Related
Jan 22, 2007
Hi Alex (Kuznetsov) and All,This is to follow up with my last post, "Link two tables using partialword match".How can I UPDATE table using partial word match?How can I write a SQL statement to do so? (I am using MS-Access.)The two tables and the expected result are listed below:Thank you in advance!- Grasshopper -================================================== =====TABLE: tblStreet_Value (Table to be undated)StreetValue2---------------------123 ABC Street124 ABC Street125 ABC Street1 XYZ AVE2 XYZ AVE3 XYZ AVE10 CBS Street11 CBS Street12 CBS Street100 Apple Road101 Apple Road102 Apple RoadTABLE: TblWord_NumberWordNumberValue---------------ABC123NYABC125CACBS11MACBS12TXResultStreet Value2---------------------123 ABC Street NY124 ABC Street125 ABC Street CA1 XYZ AVE2 XYZ AVE3 XYZ AVE10 CBS Street11 CBS Street MA12 CBS Street TX100 Apple Road101 Apple Road102 Apple Road
View 5 Replies
View Related
Oct 8, 2007
Hello,
The illustration below is for a customer dedupification project.
The Source file, containing customer name and address records, is conditionally split based on 7 ranges of substring(city,1,2) to distribute the load across 7 different threads for parallelization. Each customer record in the source file
is looked up against a reference table named Location_Stage for its existence using the Fuzzy Lookup
transformation.
The reference table Location_Stage has around 10 miilion+ records. The source file would normally have around 1 million
records.
I am wondering :
- if it would be possible to partition the Match Index of the reference table (NOT the reference table) into
7 partitions based on 7 ranges of substring(city,1,2) and maintain these partitions on different drives?
- if it is possible to specify a particular partition to be used by a FzLkup transformation?
- if the partitioning approach will improve the performance of the Fuzzy Lookups?
Source File Feed
|
Split data into 7 groups based on substring(city,1,2)
|
------------------------------------------------------------------------------------------------------------------------------------------
| | | | | | |
UnionAll UnionAll UnionAll UnionAll UnionAll UnionAll UnionAll
| | | | | | |
FzLkup FzLkup FzLkup FzLkup FzLkup FzLkup FzLkup
| | | | | | |
Split Split Split Split Split Split Split
| | | | | | |
------------- -------------- -------------- -------------- -------------- -------------- --------------
| | | | | | | | | | | | | |
<- - - - - - - Write the Canonicals and Dupes from each of these splits into database - - - - - - - - ->
Please advice.
Thanks.
View 3 Replies
View Related
Sep 19, 2013
web scrapping from the web details, to populate the price of product match with name in the Table?
View 2 Replies
View Related
Jul 6, 2006
Hi all,
In BOL it says: "The Lookup transformation performs an equi-join between values in the transformation input and values in the reference dataset. Using an equi-join means that each row in the transformation input must match at least one row from the reference dataset. If there is no matching entry in the reference dataset, no join occurs and no values are returned from the reference dataset. This is an error, and the transformation fails, unless it is configured to ignore errors or redirect error rows to the error output. "
I have a lookup transformation which is supposed to find a match on two fields in the reference dataset (a table in my case) but strangely, when I execute my package and the reference table is empty the lookup still finds match for each row of my input dataset.
Does anyone have an idea why? I could'nt find anything about that in BOL.
Sébastien.
View 4 Replies
View Related
Nov 3, 2005
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
View 1 Replies
View Related
Apr 22, 2014
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.
SELECT organisations.organisation_number, organisations.contact_number, organisations.name, organisations.address_number,
organisations.std_code, organisations.telephone, organisations.status, contacts.title, contacts.initials, contacts.forenames,
contacts.surname, contacts.contact_number, contacts.label_name, contact_roles.role, addresses.address, addresses.town, addresses.county,
addresses.postcode, addresses.country, communications.device, communications.notes
[code]....
View 9 Replies
View Related
Mar 1, 2008
Sorry for the confusing subject. Here's what im doing:I have a table of products. Products have N categories andsubcategories. Right now its 4. But there could be more down theline so it needs to be extensible.So ive created a product table. Then a category table that has manycategories of products, of which a product can belong to N number ofthese categories. Finally a ProductCategory "match" table.This is pretty straigth forward. But im getting confused as to how towrite views/sprocs to pull out rows of products that list all theproducts categories as columns in a single query view.For example:lets say productId 1 is Cap'n Crunch cereal. It is in 3 categories:Cereal, Food for Kids, Crunchy food, and Boxed.So we have:Product----------------1 Capn CrunchCategories-----------------1 Cereal2 Food for Kids3 Crunchy food4 BoxedProductCategories------------------1 11 21 31 4How do I go about writing a query that returns a single result set fora view or data set (for use in a GridView control) where I would havethe following result:Product results---------------------------------ProductId ProductName Category 1 Category 2Category 3 Category N ...------------------------------------------------------------------------1 Capn Crunch Cereal Food for Kids Crunchy foodBoxedAm I just thinking about this all wrong? Sure seems like it.Cheers,Will
View 1 Replies
View Related
Feb 2, 2007
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
)
INSERT @Outer VALUES (2, 2, 2, NULL) -- OuterID = 1
INSERT @Outer VALUES (3, 2, 1, NULL) -- OuterID = 2
DECLARE @Inner TABLE (
InnerID int IDENTITY (1, 1) NOT NULL,
MethodID int NULL,
CompID int NULL,
FormID int NULL
)
INSERT @Inner VALUES (2, null, null) -- InnerID 1
INSERT @Inner VALUES (2, null, 1) -- InnerID 2
INSERT @Inner VALUES (2, 2, null) -- InnerID 3
INSERT @Inner VALUES (3, null, null) -- InnerID 4
INSERT @Inner VALUES (3, 2, null) -- InnerID 5
INSERT @Inner VALUES (4, 2, 1) -- InnerID 6
-- 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.
View 6 Replies
View Related
Oct 17, 2013
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),
[code]....
View 3 Replies
View Related
Sep 15, 2015
I have two tables (i.e. Table1 and Table2).
SELECT
* FROM [dbo].[Table1]
Date
id
9/15/2015
[code]...
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:-
declare @Startdate
datetime
DEclare @enddate
datetime
[code]...
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?
View 2 Replies
View Related
Jul 22, 2015
The database has approx. 2500 temporary tables. The temp tables match a pattern such as APTMP... I tried deleting the tables in SSMS with the statement, Delete from Information_Schema.tables where substring(table_name,1,5) = 'APTMP' This returns the error message"Ad hoc updates to system catalogs are not allowed".
What is the correct way to delete a group of tables whose name match a pattern from within SSMS?
View 9 Replies
View Related
Jun 22, 2015
I'm executing a stored procedure but got error :
Msg 213, Level 16, State 1, Procedure ExtSales, Line 182
Column name or number of supplied values does not match table definition.
View 5 Replies
View Related
May 12, 2015
I have two tables, D and C, whose INTERSECT / EXCEPT values I'd like to insert in the following table I've created
CREATE TABLE DinCMatch (
servername varchar(50),
account_name varchar (50),
date_checked datetime DEFAULT getdate()
)
The following stmt won't work...
INSERT INTO DinCMatch
select servername, account_name from D
intersect
select servername, account_name from C
... as the number of supplied values does not match table definition.
View 2 Replies
View Related
May 3, 2004
Hello all,
Please help....
I have a text file which needs to be created into a table (let's call it DataFile table). For now I'm just doing the manual DTS to import the txt into SQL server to create the table, which works. But here's my problem....
I need to extract data from DataFile table, here's my query:
select * from dbo.DataFile
where DF_SC_Case_Nbr not like '0000%';
Then I need to create a new table for the extracted data, let's call it ExtractedDataFile. But I don't know how to create a new table and insert the data I selected above into the new one.
Also, can the extraction and the creation of new table be done in just one stored procedure? or is there any other way of doing all this (including the importation of the text file)?
Any help would be highly appreciated.
Thanks in advance.
View 3 Replies
View Related
Aug 4, 2004
Hi All,
I'm trying to create a proc for granting permission for developer, but I tried many times, still couldn't get successful, someone can help me? The original statement is:
Create PROC dbo.GrantPermission
@user1 varchar(50)
as
Grant create table to @user1
go
Grant create view to @user1
go
Grant create Procedure to @user1
Go
Thanks Guys.
View 14 Replies
View Related
May 20, 2008
the subject pretty much says it all, I want to be able to do the following in in VB.net code):
{[If [table with this name] already exists [in this sql database] then [ don't create another one] else [create it and populate it with these values]}
How would I do this?
View 3 Replies
View Related
Jul 20, 2005
Can I dynamically (from a stored procedure) generatea create table script of all tables in a given database (with defaults etc)a create view script of all viewsa create function script of all functionsa create index script of all indexes.(The result will be 4 scripts)Arno de Jong,The Netherlands.
View 1 Replies
View Related
Jul 20, 2005
I have some code that dynamically creates a database (name is @FullName) andthen creates a table within that database. Is it possible to wrap thesethings into a transaction such that if any one of the following fails, thedatabase "creation" is rolledback. Otherwise, I would try deleting on errordetection, but it could get messy.IF @Error = 0BEGINSET @ExecString = 'CREATE DATABASE ' + @FullNameEXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'CREATE TABLE ' + @FullName + '.[dbo].[Image] ( [ID][int] IDENTITY (1, 1) NOT NULL, [Blob] [image] NULL , [DateAdded] [datetime]NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'ALTER TABLE ' + @FullName + '.[dbo].[Image] WITHNOCHECK ADD CONSTRAINT [PK_Image] PRIMARY KEY CLUSTERED ( [ID] ) ON[PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorEND
View 2 Replies
View Related
May 30, 2015
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.
I have table containing 5 columns.
caseid
referenceid
startdate
enddate
caseopendate
[code]...
View 4 Replies
View Related
Dec 14, 2007
I like to define my procedure parameter type to match a referenced table colum type,
similar to PL/SQL "table.column%type" notation.
That way, when the table column is changes, I would not have to change my stored proc.
Any suggestion?
View 1 Replies
View Related
Nov 21, 2006
For reasons that are not relevant (though I explain them below *), Iwant, for all my users whatever privelige level, an SP which createsand inserts into a temporary table and then another SP which reads anddrops the same temporary table.My users are not able to create dbo tables (eg dbo.tblTest), but arepermitted to create tables under their own user (eg MyUser.tblTest). Ihave found that I can achieve my aim by using code like this . . .SET @SQL = 'CREATE TABLE ' + @MyUserName + '.' + 'tblTest(tstIDDATETIME)'EXEC (@SQL)SET @SQL = 'INSERT INTO ' + @MyUserName + '.' + 'tblTest(tstID) VALUES(GETDATE())'EXEC (@SQL)This becomes exceptionally cumbersome for the complex INSERT & SELECTcode. I'm looking for a simpler way.Simplified down, I am looking for something like this . . .CREATE PROCEDURE dbo.TestInsert ASCREATE TABLE tblTest(tstID DATETIME)INSERT INTO tblTest(tstID) VALUES(GETDATE())GOCREATE PROCEDURE dbo.TestSelect ASSELECT * FROM tblTestDROP TABLE tblTestIn the above example, if the SPs are owned by dbo (as above), CREATETABLE & DROP TABLE use MyUser.tblTest while INSERT & SELECT usedbo.tblTest.If the SPs are owned by the user (eg MyUser.TestInsert), it workscorrectly (MyUser.tblTest is used throughout) but I would have to havea pair of SPs for each user.* I have MS Access ADP front end linked to a SQL Server database. Forreports with complex datasets, it times out. Therefore it suit mypurposes to create a temporary table first and then to open the reportbased on that temporary table.
View 6 Replies
View Related
Aug 29, 2007
which is more efficient...which takes less memory...how is the memory allocation done for both the types.
View 1 Replies
View Related
Mar 24, 2008
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 + ' ')
View 4 Replies
View Related
