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]
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
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.
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 + ' ')
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'.
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
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 .
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
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
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
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... :(
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.
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
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!
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.
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.
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?
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.
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
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.
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)
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 )
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
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'
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]]]
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
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.
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.
