INSERT INTO Nested Table Error

Jan 9, 2007

I am running the DMX below and I am getting an error we I go to train my structure.  It is probably something stupid but I do not see the problem.  It seems like the SKIP isn't being recognized but I am not sure.  Here is the error:

INSERT INTO error: The '[MSA].[HospitalID]' nested table key column is not bound to an input rowset column.
DMX:
CREATE MINING STRUCTURE [Hospital_Structure] (
 [HospitalID] LONG KEY,
 [SponsorshipTypeID] LONG DISCRETE,
 [GeographicTypeID] LONG DISCRETE,
 [CaseMixIndex] DOUBLE CONTINUOUS,
 [PercentGovtPayers] DOUBLE CONTINUOUS,
 [TotalNumberInpatientCases] LONG CONTINUOUS,
 [MSA] TABLE (
  [HospitalID] LONG KEY,
  [MSAGroupMember] TEXT DISCRETE
 )
);
GO
 
ALTER MINING STRUCTURE [Hospital_Structure]
ADD MINING MODEL [Hospital_Model] (
 [HospitalID],
 [SponsorshipTypeID],
 [GeographicTypeID],
 [CaseMixIndex],
 [PercentGovtPayers],
 [TotalNumberInpatientCases],
 [MSA] (
  [HospitalID],
  [MSAGroupMember]
 )
) USING Microsoft_Clustering;
GO


INSERT INTO MINING STRUCTURE [Hospital_Structure]
(
 [HospitalID],
 [SponsorshipTypeID],
 [GeographicTypeID],
 [CaseMixIndex],
 [PercentGovtPayers],
 [TotalNumberInpatientCases],
 [MSA] (SKIP, [MSAGroupMember])
)
SHAPE { 
  OPENQUERY([localhost],'
 SELECT
  [HospitalID],
  [SponsorshipTypeID],
  [GeographicTypeID],
  [CaseMixIndex],
  [PercentGovtPayers],
  [TotalNumberInpatientCases]
 FROM
  [dm].[vw_HospitalClustering_Inputs]
 ORDER BY
  [HospitalID]') }
APPEND
(
  {OPENQUERY([localhost],'
 SELECT
  [HospitalID],
  [MSAGroupMember]
 FROM
  [dm].[vw_HospitalClustering_InputsNested]
 ORDER BY
  [HospitalID],
  [MSAGroupMember]')
}
RELATE [HospitalID] TO [HospitalID]
) AS [MSA]

Thanks in advance

View 1 Replies


ADVERTISEMENT

ERROR:- An INSERT EXEC Statement Cannot Be Nested.

May 3, 2004

HI,

WELL WE HAVE BEEN TRYING TO AUTOMATE A PROCEDURE OUT HERE,AND WE ARE TRYING TO CONVERT MOST OF THE THINGS INTO PROCEDURES.

BUT WE ARE GETTING A FEW HICCUPS. PLS HELP

THIS IS HOW IT GOES :-

CREATE PROCEDURE MY_PROC1
AS
BEGIN
ST1 .........;
ST2..........;
END


CREATE PROCEDURE MY_PROC2
AS
BEGIN

CREATE TABLE #TMP2
(COL1 DATATYPE
COL2 DATATYPE)

INSERT INTO #TMP2
EXEC MY_PROC1

ST1 .........;
ST2..........;

END

THIS PROCEDURE TOO RUNS WELL ,AFTER TAKING THE DATA FROM THE FIRST PROC IT MANIPUATES THE DATA ACCORDING TO THE CRITERIA SPECIFIED

NO PROBLEM TILL NOW.......

BUT,

CREATE PROCEDURE MY_PROC3
AS
BEGIN

CREATE TABLE #TMP3
(COL1 DATATYPE
COL2 DATATYPE)

INSERT INTO #TMP3
EXEC MY_PROC2

ST1 .........;
ST2..........;

END

THEN IT GIVES AN ERROR AS :-

"An INSERT EXEC statement cannot be nested."

CAN'T WE , FROM A PROCEDURE CALL A PROCEDURE WHICH CALLS A PROCEDURE........

WHAT IS THE NESTING LEVEL OF A PROCEDURE ?

IS THERE ANY WAY AROUND IT OR CAN IT BE DONE BY CHANGING SOME SETTINGS ?

PLS HELP ME OUT IN THIS

THANKS

View 13 Replies View Related

An INSERT EXEC Statement Cannot Be Nested Error.

Nov 28, 2005

Hi,all,When I use following sql, an error occurs:insert into #tmprepEXECUTE proc_stat @start,@endThere is a "select * from #tmp " in stored procedure proc_stat, and theerror message is :Server: Msg 8164, Level 16, State 1, Procedure proc_stat, Line 42An INSERT EXEC statement cannot be nested.What's the metter? Any help is greatly appreciated. Thanks

View 2 Replies View Related

Error About Nested Table Data Binding

Dec 10, 2007

Hi,

Referring to the book "Data Mining with SQL Server 2005" written by ZhaoHui Tang, I created a Mining Structure and a Mining Model with the AMO API after creating Database and Data Access Objects(referred to code lists from 14-1 to 14-6). I added Nested Table by creating a table column and added a key column to the nested table, while the error showed that in my structure the column of the nested table didn't include effective data bindings when processing.

Thanks for any suggestion!

View 3 Replies View Related

OPENROWSET (INSERT) Insert Error: Column Name Or Number Of Supplied Values Does Not Match Table Definition.

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

SSMS Express: Create TABLE && INSERT Data Into Table - Error Msgs 102 && 156

May 18, 2006

Hi all,

I have SQL Server Management Studio Express (SSMS Express) and SQL Server 2005 Express (SS Express) installed in my Windows XP Pro PC that is on Microsoft Windows NT 4 LAN System. My Computer Administrator grants me the Administror Privilege to use my PC. I tried to use SQLQuery.sql (see the code below) to create a table "LabResults" and insert 20 data (values) into the table. I got Error Messages 102 and 156 when I did "Parse" or "Execute". This is my first time to apply the data type 'decimal' and the "VALUES" into the table. I do not know what is wrong with the 'decimal' and how to add the "VALUES": (1) Do I put the precision and scale of the decimal wrong? (2) Do I have to use "GO" after each "VALUES"? Please help and advise.

Thanks in advance,

Scott Chang

///////////--SQLQueryCroomLabData.sql--///////////////////////////
USE MyDatabase
GO
CREATE TABLE dbo.LabResults
(SampleID int PRIMARY KEY NOT NULL,
SampleName varchar(25) NOT NULL,
AnalyteName varchar(25) NOT NULL,
Concentration decimal(6.2) NULL)
GO
--Inserting data into a table
INSERT dbo.LabResults (SampleID, SampleName, AnalyteName, Concentration)
VALUES (1, 'MW2', 'Acetone', 1.00)
VALUES (2, 'MW2', 'Dichloroethene', 1.00)
VALUES (3, 'MW2', 'Trichloroethene', 20.00)
VALUES (4, 'MW2', 'Chloroform', 1.00)
VALUES (5, 'MW2', 'Methylene Chloride', 1.00)
VALUES (6, 'MW6S', 'Acetone', 1.00)
VALUES (7, 'MW6S', 'Dichloroethene', 1.00)
VALUES (8, 'MW6S', 'Trichloroethene', 1.00)
VALUES (9, 'MW6S', 'Chloroform', 1.00)
VALUES (10, 'MW6S', 'Methylene Chloride', 1.00
VALUES (11, 'MW7', 'Acetone', 1.00)
VALUES (12, 'MW7', 'Dichloroethene', 1.00)
VALUES (13, 'MW7', 'Trichloroethene', 1.00)
VALUES (14, 'MW7', 'Chloroform', 1.00)
VALUES (15, 'MW7', 'Methylene Chloride', 1.00
VALUES (16, 'TripBlank', 'Acetone', 1.00)
VALUES (17, 'TripBlank', 'Dichloroethene', 1.00)
VALUES (18, 'TripBlank', 'Trichloroethene', 1.00)
VALUES (19, 'TripBlank', 'Chloroform', 0.76)
VALUES (20, 'TripBlank', 'Methylene Chloride', 0.51)
GO
//////////Parse///////////
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '6.2'.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'VALUES'.
////////////////Execute////////////////////
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '6.2'.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'VALUES'.

View 7 Replies View Related

Nested Executes In Insert Statements

Mar 19, 2001

Is this a limitation of SQL server. I am running a quite complex sp that I wrote which uses exec to execute an SQL string. Running the SP produces the desired results but if I try to use this sp with an insert statement then I get an error message that exec cannot be nested in an insert statement.....any help would be appreciated

View 2 Replies View Related

Problem With Insert Exec In Nested SP

Jul 24, 2006

Hello,I want to share my experiences about using insert into exec which mayhelp others .Using SQL Server 2000, SP3 .Two Proceduers - Parent SP caliing a Child SP (nested ) . No Explicittransactions .I have defined a # table in Parent SP and calling a Child SP like thisinsert into #temp exec childsp ......Child SP has Select * from local # temp table ( local to child SP) as the last statement .When number of records are less ( around 1000 - 5000) Parent SPexecutes but slow .When the Child SP returns higher number of rows ( 1,00,000 or more )the SP will be running for hours with out completion .Although executing the child SP , with exec ChildSP .... with sameparameters it is completed in 2 mins for 3,00,000 rows .Resolution : - Define a temp table (say #tempChild ) in the Parent SP..In the Child SP instead of select * replace with insert into#tempChild select * from ...Also note that this problem is not noticed in SQL 2000 Server with SP4..This may be due to SP executing in implicit transactions .

View 1 Replies View Related

An INSERT EXEC Statement Cannot Be Nested.

Sep 19, 2007

I try to select a store procedure in SqlExpress2005 which inside store procedure execute another store procedure,
When I select it but it prompt error messages "An INSERT EXEC statement cannot be nested.".
In Fire bird /Interbase store procedure we can nested. Below are the code;
declare @dtReturnData Table(doccode nvarchar(20), docdate datetime, debtoraccount nvarchar(20))
Insert Into @dtReturnData
Exec GetPickingList 'DO', 0, 37256, 'N', 'N', 'YES'
Select doccode, docdate, debtoraccount
From @dtReturnData
Inside the GetPickList It will do like this, but most of the code I not included;
ALTER PROCEDURE GETPICKINGLIST
@doctype nvarchar(2),
@datefrom datetime,
@dateto datetime,
@includegrn char(1),
@includesa char(1),
@includedata nvarchar(5)
AS
BEGIN
declare @dtReturnData Table(doccode nvarchar(20),
docdate datetime,
debtoraccount nvarchar(20))
IF (@DOCTYPE = 'SI')
BEGIN
Insert Into @dtSALESINVOICEREGISTER
Exec SALESINVOICEREGISTER @DateFrom, @DateTo, @IncludeGRN, @IncludeSA, @IncludeData
END
ELSE
BEGIN
Insert Into @dtDELIVERYORDERREGISTER
Exec DELIVERYORDERREGISTER @DateFrom, @DateTo, @IncludeGRN, @IncludeSA, @IncludeData
END
Select doccode,docdate,debtoraccount From @dtReturnData
END

So how can I select a nested store procedure? can someone help me

View 1 Replies View Related

Problem With Nested INSERT EXEC

Jun 16, 2006

Hi All,
I am having a problem with nested insert exec. Say for example I have three stored procedure procA, procB and procC.

I am executing procedure procC in procB; I am storing the values returned by procC in a tempTable. The code is

INSERT INTO #Temp
EXEC procC

And in procA, I am executing procB, and the values returned by procB are stored in another temp table. The code is

INSERT INTO #TempOne
EXEC procC


When I execute the procA, I am getting error as

An INSERT EXEC statement cannot be nested.


My requirement is like this, please give me a solution.

Thanks

View 5 Replies View Related

INSERT EXEC Statement Cannot Be Nested

Apr 12, 2006

i have a 3 or 4 cursors, and in the inner cursor i am inserting into a table from a sproc. i keep getting the error



An INSERT EXEC statement cannot be nested.



heres the actual insert code:



set @SQLString = 'EXEC ScoresGetlines '+cast(@customerID as char(10))+',' + cast(@programId as char(10))+',' + '"'+ @period +'",NULL,NULL,0'

INSERT INTO reportData

exec (@sqlString)



ive tried just a simple :

insert into reportdata

exec scoreGetLines @customerId,@programID...........



that still doesnt work. same error. how can this be sorted

View 13 Replies View Related

Nested Insert Exec Statement

Feb 7, 2008

I am using the following statement in a SP.

EXECUTE (' INSERT INTO #OutPut

EXEC h_DailyDividend
@TickerTable = '+@TickerTableName+',
@DateTable = '+@DateTableName+',
@Units = '+@Units

)

AND IN h_DailyDividend I am using the following statement.

EXECUTE (' INSERT INTO #TickerTable

EXEC h_SecMstr_SecMap_TQAExch_Info

@IDList = '+@TickerTable+',

@IsTable = 1,

@Type = 0,

@OutPutFormat = 0,

@VenType = 14 ')

And i am getting the following error.

Message: An INSERT EXEC statement cannot be nested.

Can any body help me out how to solve this problem.

Regards
Sulaman

View 4 Replies View Related

Insert Query With Nested Select And Parameter

Jul 20, 2005

hey there, i'm trying to move one record from one table to the next,so i'm doing this by doing an insert into table, then delete from theprevious table. Only thin g is on the insert i want to include aparameter. Can't work how to do this. Do you think i need to doanother update query and insert the parameter that way, after theinsert is done?ALTER PROCEDURE dbo.ReturnLoan(@strBarcode varchar(100),@strLibrary varchar(100),@dateReturned datetime)ASINSERT INTO Loan_History(Barcode,UserID,Date_Borrowed,Library)Select Barcode, UserID, Date_Borrowed, Library FROM Loans WHEREBarcode = @strBarcodeAND Library = @strLibrary;DELETE FROM LoansWHERE Barcode = @strBarcodeAND Library = @strLibrary ;RETURN

View 1 Replies View Related

Error In Insert To Sql Table

May 11, 2008

hi all when i try to insert a record to sql table i have this error
ncorrect syntax near 'japan'.
the code part iscmdInsert = New SqlCommand("insert into empbill values(" & Val(eno.Text) & ","  & contry1.Text , db)
cmdInsert.ExecuteNonQuery()
the value i entered is 123.japan
i have 2 field in  empbill (emp,cont)
what happend
 

View 2 Replies View Related

Table Insert .... Error

Jun 5, 2002

Hi - I am trying to do the following simple insert that fails...

insert into tbloutgoingobms_hold
select aOBMMessageID, nGlobalCaseID, nOBMailerID,'Food','05-JUN-2002'
from tbloutgoingobms

The table that I am inserting into is a duplicate of the table I am selecting from plus I added two new columns - thus I am inserting constant values into those two new columns: 'Food','05-JUN-2002' . This insert works in Oracle but when I run it on SQL*Server I get the following error: An explicit value for the identity column in table 'tbloutgoingobms_hold' can only be specified when a column list is used and IDENTITY_INSERT is ON.

I used the command: SET IDENTITY_INSERT tbloutgoingobms_hold ON

The column that the 'Food' constant is going into is a varchar(10) and the date column is a datetime column.

Still no luck.... Help please - this cannot be such a difficult thing to do... :(

Thanks is advance,
Nancy

View 3 Replies View Related

Why I Got The Message As You Need To Create The Many-to-one Relationship Between The Case Table And The Nested Table?

May 30, 2007

Hi, all experts here,



Thank you very much for your kind attention.



I am trying to create a new mining structure with case table and nested table, the case table (fact table) has alread defined the relationships with the nested table(dimension table), and I can see their relationship from the data source view. But why the wizard for creating the new mining structure showed that message? Why is that? And what could I try to fix it?

Hope it is clear for your help.

Thanks a lot for your kind advices and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

View 4 Replies View Related

Error Trying To Insert Into Table Using XML Param.

May 9, 2008

Hi all,

I also have the same error, I am trying to do two things in my Stored Proc.

1) - Insert a parent record.
2) - Insert 1 or many records in the child table using the parent ID

The child records are being passed as a XML param.

I am also getting the
'Subqueries are not allowed in this context. Only scalar expressions are allowed.'
error when I try to create the procedure.


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:pryder
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE TestProc
-- Add the parameters for the stored procedure here
@Name String ,
@bings XML
AS
BEGIN transaction
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @NewID as int
declare @err as int
-- Insert statements for procedure here
INSERT INTO PARENT
(Name)
VALUES (@Name)
SELECT @err = @@error
if @err <> 0
begin
rollback transaction
return @err
end


SELECT SCOPE_IDENTITY = @NewID


INSERT INTO Child
(ParentID,
name)

Values

((
SELECT @NewID, ParamValues.ID.value('.','VARCHAR(MAX)')
FROM @bings.nodes('bings/group') as ParamValues(ID)
))


SELECT @err = @@error
if @err <> 0 begin rollback transaction return @err end


commit transaction
return @@error
GO

View 7 Replies View Related

Insert Into Table-Primary Key Error

Jul 20, 2005

I'm trying to do multiple insert statements. The table looks likethis:CREATE TABLE $table (CNTY_CNTRY_CD char(3),ST char(2),CNTY_CNTRY_DESCR varchar(50),CNTY_CNTRY_IND char(1),HOME_CNTRY_IND char(1),CONSTRAINT cnty_key PRIMARY KEY (CNTY_CNTRY_CD, ST))I'm using 2 fields for the primary key constraintMy insert statement looks like this:INSERT INTO $table(CNTY_CNTRY_CD,ST,CNTY_CNTRY_DESCR)VALUES(?,?,?)I've been through the list of values and none have both the sameCNTY_CNTRY_CD and ST and yet, this is the error message I'm getting:DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver][SQLServer]Violation of PRIMARY KEY constraint 'cnty_key'. Cannot insert duplicatekey in object 'event_CNTY_CNTRY_CD'. (SQL-23000)[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has beenterminated.. (SQL-01000)(DBD: st_execute/SQLExecute err=-1)Why is it looking for unique in just the one column instead ofreferencing both? What do I need to do to get this to work? Help!

View 1 Replies View Related

Insert Error On Existing Table

Oct 10, 2006

I am having the below issue.

Duplicate Key Values

When inserting records into a table created through a RDA Pull(), many users experience duplicate key violations. One reason for this is Identity columns. SQL Server CE RDA does not set the seed on the Identity columns when a table is pulled.

Source: Microsoft SQL Server 2000 Windows CE Edition

Native Error: 25016

HR: DB_E_INTEGRITYVIOLATION

Description: Value violated the integrity constraints for a column or table.

Interface defining error: IID_IRowsetChange

Param = 0

Param = 0

Param = 0

Param =

Param =

Param =

This error can be returned when the user attempts to insert a row with an automatically incremented Identity column. With RDA, this usually occurs when a user pulls rows from the server and attempts to insert new rows before setting the seed and increment values for the Identity column. By default, the seed and increment values are both 1.

To correct this error, set the seed to the next highest number after the table is pulled, before allowing users to enter data.



What do they mean by setting the seed to the next highest level? I this the seed to the GUID row in the pulled table? How do you correctly do this with the ALTER table statement? The database that I am pulling has 21 tables, and would be a pain to have to do this. Does anyone have any other ideas as to why this won't work properly. If I clear out the data from the tables on the publishing server, I can add data to the pull as long as I want until I do another pull later. After I do that, I keep getting the above issue.

View 1 Replies View Related

Question On Case Table And Nested Table

Jun 5, 2007

Hi, all here,



As we are allowed to select one table as both case table and nested table, however what is the benefit of using one table as both case table and nested table? Thanks in advance for your advices.

I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

View 3 Replies View Related

When To Use A Nested Table?

Jul 19, 2007



Why would someone choose to model a problem with a case and nested table versus just using a case tables with multiple rows?

For example, a simple customer and product scenario where you could say:



Mining Model DT_CustProd
(
[Id] ,
[Gender] ,
[Age]
[Products] Predict
(
[ProductName] ,
[Quantity]
)
)



But you could also model as:



Mining Model DT_CustProd
(
[Id] ,
[Gender] ,
[Age]
[ProductName] Predict,
[Quantity] Predict
)



and just insert multiple rows per Id/Gender/Age? Is there any rule of thumb to use here when deciding to model with a nested table?


I understand that a nested table essentially pivots the data (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=408840&SiteID=1) and the data becomes an attribute, but how do you know if you want that, or you want the data as a value of an attribute?

View 3 Replies View Related

Why Use Nested Table?

May 17, 2006

Hi, all here,

I have a question about nested table use. I dont quite clearly see through why the nested table is very much necessary? I mean if we need information from both the current case table and nested table for mining models, we can just join both tables?

Thanks a lot in advance for any guidance and help.

View 4 Replies View Related

Nested Nodes In XML From A Table

Dec 6, 2005

Dear all,I have table called CATEGORY, which is defined as follows:CREATE TABLE CATEGORY(CATEGORY_ID INTEGER IDENTITY(1,1) NOT NULL,CATEGORY_NAME VARCHAR(40) NOT NULL CONSTRAINTUC__CATEGORY__CATEGORY_NAME UNIQUE,PARENT_CATEGORY_ID INTEGER,CATEGORY_ICON IMAGE,DEPTH INTEGER,CONSTRAINT PK__CATEGORY PRIMARY KEY (CATEGORY_ID))Supposly, the following snap shot was taken later:================================================== ============| CATEGORY_ID | CATEGORY_NAME | PARENT_CATEGORY_ID | DEPTH |================================================== ============| 1 | PC | NULL | 1 |--------------------------------------------------------------| 2 | Networks | 1 | 2 |--------------------------------------------------------------| 3 | Audio | 1 | 2 |--------------------------------------------------------------| 4 | Video | 1 | 2 |--------------------------------------------------------------| 5 | TV Cards | 4 | 3 |--------------------------------------------------------------| 6 | Graphics Cards | 4 | 3 |--------------------------------------------------------------| 7 | AGP | 6 | 4 |--------------------------------------------------------------| 8 | PCI | 6 | 4 |--------------------------------------------------------------| 9 | Input Devices | 1 | 2 |--------------------------------------------------------------and I'd like to create out of this hierarchy the following desired XMLfile:<?xml version="1.0" encoding="utf-8" ?><Hardware><Catgeory name="PC" id="1"><Catgeory name="Networks" id="2" /><Catgeory name="Audio" id="3" /><Catgeory name="Video" id="4"><Catgeory name="TV Cards" id="5" /><Catgeory name="Graphics Cards" id="6"><Catgeory name="AGP" id="7" /><Catgeory name="PCI" id="8" /></Category></Category><Catgeory name="Input Devices" id="9" /></Catgeory></Hardware>The reason for this file is that it will be a datasource of theTreeView Control new in asp.net 2.0.Now, programmateiclally using C#.net i started using the XmlDocument,XmlTextWriter and XmlTextReader Namespaces and started using susingrecurrsion to genearete this desired XML file out of the records in thesnapshot, but ...Is there an easy way of doing this using SqlServer 2005 with the newdatatype XML?*Any hint would also be ok*Best regards

View 12 Replies View Related

I'm Having Issues With Nested Table...

Mar 1, 2007

Hey gang,

I'm having some issues with nested table. This is my setup. [ProductTable] is the case table, and [CustomersTable] is a nested table. I'm trying to organize my algorithms around products.

[ProductTable]<---[CustomersTable]

[ProductTable] table only has product ID, and it is key.

[CustomersTable] table has variety of customer attributes (productID, customerID, location, demographics...) and CustomerRevenue is predict_only. ProductName is the key for the nested table.



I keep getting this error when I'm processing the mining models (Logical Regression and Neural Net).

Error (Data mining): In mining model, Estimate Neural Net, the algorithm does not allow table column as predictable.

Error (Data mining): Error validating attribute for the 'Estimate Neural Net' mining model.



When using Decision Tree, it processes OK, but the result is totally wrong. The model is empty.

Any ideas?

-Young K.

P.S. I'm trying to great a single model for multiple products. This is a label saving device that I'm trying. If this doens't work, I'll have to create a model for each product.

View 5 Replies View Related

Error While Executing Nested DTS Packages...

Jul 20, 2005

HelloWe ran into a peculiar problem. We copied all of our DTS packagesfrom one server to another server (both of which run SQL 2000 and areon the same Service Pack 3/3a). When we execute an embedded DTSpackage from within anothe DTS package, we get the following error:Error Title: Server BusyError Description: This action cannot be completed because the otheprogram is busy. Choose 'Switch To' to activate the busy program andcorrect the problem.Buttons Available: Swith To..., RetryButtons Disabled: CancelDoes any one know about this error and what to do about it?ThanksJagannathan Santhanam (Jags)

View 1 Replies View Related

Nested Table Concept In SQL Server

May 8, 2004

Hi all,

What is the equivalent for Oracle's nested table concept in SQL Server ?
Is there anything like TABLE( ) function to select from nested table as in Oracle ?

Eg in Oracle :

SELECT t.* FROM TABLE(nested_table_datatype) t;

( like the above query used in Oracle PL/SQL and 'nested_table_datatype' is a table datatype created in Oracle using 'create type ...' syntax )

Please give the equivalent for above...

Thanks,
Sam

View 2 Replies View Related

Nested Select, And Table Joint

Sep 14, 2006

Hi Guys

Am new to sql, and I wold appreciate help with optimising the folloing example. The result of the example should be to list a result with details of the Column names:

OPBal| Receipt| IssTrns| Transfer| ClBal

SELECT dbo.inventory.location, dbo.inventory.itemnum,
(select sum(dbo.matrectrans.linecost) where dbo.matrectrans.issuetype LIKE 'RECEIPT' ) As Receipt,
( select sum(dbo.matrectrans.linecost)where dbo.matrectrans.issuetype LIKE 'TRANSFER' ) As Transfer,
( select(dbo.IST_ITEMDETAIL.curbal*dbo.IST_ITEMDETAIL.avgcost)where dbo.IST_ITEMDETAIL.logdate='2006-07-20' ) As OpBal,
( select (IST_ITEMDETAIL.curbal*IST_ITEMDETAIL.avgcost)where IST_ITEMDETAIL.logdate='2006-08-21' ) As ClBal,
( sum(matusetrans.linecost) ) As IssTrnf
FROM dbo.matrectrans, dbo.matusetrans, dbo.IST_ITEMDETAIL , ( dbo.inventory inner JOIN dbo.item
ON dbo.inventory.itemnum = dbo.item.itemnum AND dbo.inventory.orgid = dbo.item.orgid )

WHERE dbo.inventory.location = dbo.matusetrans.storeloc
AND dbo.inventory.itemnum = dbo.matrectrans.itemnum AND dbo.inventory.siteid = dbo.matrectrans.siteid

OR dbo.inventory.location = dbo.matrectrans.tostoreloc AND dbo.inventory.itemnum = dbo.matusetrans.itemnum
AND dbo.inventory.siteid = dbo.matusetrans.siteid OR dbo.inventory.location = dbo.matrectrans.fromstoreloc

OR
dbo.inventory.location = dbo.ist_itemdetail.location AND dbo.inventory.itemnum = dbo.ist_itemdetail.itemnum
GROUP BY dbo.inventory.location, dbo.inventory.itemnum,dbo.matrectrans.issuetype,(dbo.IST_ITEMDETAIL.curbal*dbo.IST_ITEMDETAIL.avgcost),
dbo.IST_ITEMDETAIL.logdate,dbo.IST_ITEMDETAIL.curbal,dbo.IST_ITEMDETAIL.avgcost

View 20 Replies View Related

Temp Table Or Nested Select

Jan 30, 2007

hi all,
i have speed issue on displaying 4k line of records using temp table.. before this it works fine and fast.. but maybe when i starts joining group by it loads slower.

SELECT DISTINCT customlotno, itemid, ItemName, Ownership, TotalCTNInPlt, TotalCarton, sum(CartonPcs) AS CartonPcs, StorageID, StorageStatus ,OriginUOM, PickQtyUOM, WhsID, WhsName, LocID, Zone, Expirydate, recvDate
INTO #ByItemID
FROM (
SELECT * FROM tblItemdetail
)AS L1
GROUP BY customlotno, itemid, ItemName, ownership, TotalCTNInPlt, TotalCarton, StorageID, StorageStatus ,OriginUOM, PickQtyUOM, WhsID, WhsName, LocID, Zone, Expirydate, recvDate

SELECT *
FROM #ByItemID
ORDER BY CustomLotNo

DROP TABLE #ByItemID

----------------------------
or maybe just use something like nested SELECT like this, but cannot work:-

select customlotno, itemid, locid(

select * from tblitemdetail
where customlotno='IN28606000'

) AS T
GROUP BY customlotno, itemid, locid


~~~Focus on problem, not solution~~~

View 12 Replies View Related

Prediction Join To MDX With Nested Table

Aug 2, 2006

If your prediction join is to a SQL datasource, you can easily write a SQL query which returns a nested table like:

SELECT
Predict([Subcategories],2) as [Subcategories]
FROM
[SubcategoryAssociations]
NATURAL PREDICTION JOIN
(SELECT
(SELECT 'Road Bikes' AS Subcategory
UNION SELECT 'Jerseys' AS Subcategory
) AS Subcategories
) AS t

What about if your datasource is a cube? Is there some special MDX syntax similar to the SQL syntax above? Or do you have to utilize the SHAPE/APPEND syntax as follows?

SELECT t.*, $Cluster as ClusterName
FROM [MyModel]
PREDICTION JOIN
SHAPE {
select [Measures].[My Measure] on 0,
[My Dimension].[My Attribute].[My Attribute].Members on 1
from MyCube
}
APPEND (
{
select [Measures].[Another Measure] on 0,
NON EMPTY [My Dimension].[My Attribute].[My Attribute].Members
*[Product].[Product].[Product].Members on 1
from MyCube
}
RELATE [[My Dimension]].[My Attribute]].[My Attribute]].[MEMBER_CAPTION]]]
TO [[My Dimension]].[My Attribute]].[My Attribute]].[MEMBER_CAPTION]]]
)
AS [My Nested Table] AS t
ON [MyModel].[Product].[Product] = t.[My Nested Table].[[Product]].[Product]].[Product]].[MEMBER_CAPTION]]]

View 7 Replies View Related

How To Count No Of Items In Nested Table

Aug 28, 2007

I have the following table
Region Table
ID
ParentID
RegionName

RestaurantTable
ID
RestaurantName
RegionID

What i tried to do is count the number of restaurants by specific regionname. My current query is
Select RegionID, RegionName, count(*) as RestaurantNo
From Region Inner Join Restaurant
On Region.ID = Restaurant.RegionID

However I only get the results below
RegionID RegionName RestaurantNO
1 A1 0
2 A1.1 2
3 A1.2 1
4 A1.3 0

Where A1.1 , A1.2, and A1.3 are children of A1 in Region table
The result is not correct due to A1 should have 3 in RestaurantNo due to it contains A1.1 , A1.2 and A1.3
Could anyone help me to solve this problem.
Thank you




View 6 Replies View Related

Nested Trigger Error When Doing A Select On View?

Jan 9, 2013

In a SQL db we have we get the following error when just doing a simple select query against the view. Msg 217, Level 16, State 1...Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

No changes have been made to triggers or stored procedures recently and all was good prior to that.I understand that if my triggers loop this error will occur. But the select query does not fire any triggers functions or any other items. and the select worked with no issues last week.

Code:
SELECT TOP (100) PERCENT O.EID, O.OStart, O.OEnd, O.OID, T.Title, P.PStatus AS PS, dbo.CalcAge(O.OStart, ISNULL(O.OEnd, CURRENT_TIMESTAMP)) AS ODuration, O.PID,
O.Residence, O.b55, O.SplitItem, O.PeakStaff, O.ResidenceSub, O.Negotiator, O.Supervisor, O.TimeType, O.BreakPM, O.WorkEnd, O.Lunch, O.BreakAM, O.WorkBegin,

[code]....

View 4 Replies View Related

Internal SQL Server Error For Nested Query

May 29, 2008

Hi,

I have a following query:


select cu.Currencies_ShortName

from dbo.Currencies cu
inner join
(
select p1.Pairs_Shortname as f1, right(p1.Pairs_Shortname,3) as Currency1,

/*v1*/ -- max(SpotBid) as SpotBid, max(SpotAsk) as SpotAsk

/*v2*/ SpotBid, SpotAsk

from dbo.Pairs p1 inner join dbo.PairsQuotes pq1

on p1.Pairs_Id=pq1.Pairs_Id
and pq1.PriceDate=(select max(PriceDate) from dbo.PairsQuotes)

where p1.Pairs_Shortname like 'EUR%'

/*v1*/ -- group by p1.Pairs_Shortname, right(p1.Pairs_Shortname,3)

) as sr
on cu.Currencies_ShortName=sr.Currency1


which would work like /*v2*/, but not like /*v1*/ - there would be a message:
Server: Msg 8624, Level 16, State 3, Line 1
Internal SQL Server error.

Is Group By not manageable in a nested query, or is it some other problem?

thx
Katarina

View 5 Replies View Related

Linked Server Nested Transaction Error

Oct 10, 2007

I have a problem in running this query on linked servers, which has distributed partitioned view.

SQLTransaction transaction;... public void DeleteAllData() { try { connection.Open(); transaction = connection.BeginTransaction(); { Console.WriteLine("Deleting All Data..."); string cmdText = ""; cmd = new SqlCommand(cmdText, connection, transaction); cmd.CommandText = "delete from NEW_ORDER where NO_O_ID > 0"; cmd.ExecuteNonQuery(); cmd.CommandText = "delete from ORDER_LINE where OL_NUMBER > 0"; cmd.ExecuteNonQuery();
transaction.Commit();
Console.WriteLine("Sucessful"); } } catch (Exception e) { Console.WriteLine("Failed in Method DeleteAllData"); throw e; } finally { connection.Close(); } }

The error returned was:

Failed in Method DeleteAllDataSQL Exception caught: Unable to start a nested transaction for OLE DB provider "SQLNCLI" for linked server "SERVER_B". A nested transaction was required because the XACT_ABORT option was set to OFF.OLE DB provider "SQLNCLI" for linked server "SERVER_B" returned message "Cannotstart more transactions on this session.".

I realized that this problem may be eliminated if I'm using stored procedures on the database, and SET XACT_ABORT ON before any transaction.

Please could anyone help me.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved