I'm trying to generate some XML directly from MS SQL with the
following code
SELECT1 AS tag
, NULL AS parent
, NULL AS [GoogleCustomizations!1]
, NULL AS [Annotations!2]
, NULL AS [Annotation!3]
, NULL AS [Annotation!3!about]
, NULL AS [Annotation!3!score]
, NULL AS [Label!4]
, NULL AS [Label!4!name]
UNION
SELECT2 AS tag
, 1 AS parent
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
UNION
SELECTTOP 503 AS tag
, 2 AS parent
, NULL
, NULL
, NULL
, 'www.' + domainName
, 1 -- score
, NULL
, NULL
FROM tbl_auDomainName
UNION
SELECT4 AS tag
, 3 AS parent
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, '_cse_ad-o6lgdody'
FOR XML EXPLICIT
this is sanjeev, i have SSIS package, using my c# program i want to add one execute package task to this package's sequence container.
it is creating the new package with out any probelm. but when i opened the package and try to move the newly created exeute package task it is giving the following error.
the element cannot be found in a collection. this error happens when you try to retrieve an element from a collection on a container during the execution of the package
Why does XML Source in MS VS 2005 Integration Services Data Flow Sources XML Source not pick up the root element?
The problem is the following example: <root createddate="2008-01-01"> <elements> ... </elements </root>
The XML Source picks up everything after the first element which is the root element but it does not seam to pick up the root element where there is a need for the createddate attribute.
Is there a soultion for this problem or a workaround?
Hi, I have simple report with RS 2005. I use matrix component. I€™ve added €œSubtotal€? element to the row and It works perfect. But I would like to make some modification to it and I€™m looking for expression of it, but I can€™t find it - there's just "TOTAL". Also On layout page I see text box €œTOTAL€? and that€™s all. Where can I find it? Thank you Tomasz
I have created 2 packages in BIDS, these are both working fine. I am trying to deploy to the server using the Package installation wizard, but I am stopped by an error stating "Could not save package" and "Element not found".
I use an XML source to integrate an XML file into my db. My problem is that in my XML source component, the global element of my XML file does not appear in the Input/Output (The XSD file was generated using the same component). This global element contains attribute which I want to integrate in a table.
RDL/SRS - Is there a way to tag a reference on a data element?
For example, let€™s say that each data element in column 5 is the result of some equation applied to the elements in columns 1 through 4. The field in column 6 is a function applied to the resulting value from column 5. Is there a way to tag a reference on the column 5 data elements, so they can be referenced from column 6 without reapplying the whole function used to derive column 5 in the first place?
i have a situation, where i need to group my information by a certainid, but that information which should already be grouped, i need toorder based on a number in one of the columnsexample:<ex id=2 listorder=1><description>desc 2</description></ex><ex id=1 listorder=2><description>desc 1</description></ex>in my order clause i first put order by the id, and it will group theinformation accordingly, but will put the lowest number id first, notcaring about the listorderbut if i order by the listorder column first, the information isn'tgroupd properlyi don't know if i made myself clear enough?hope someboyd can help methnx in advance
Hi there, I am sorry to be abother but I was wondering if there was someone to tell me what I am doing wrong. Here is my statement. I am getting the following error.
Element SADDR1 is undefined in SESSION
SELECT [clv].[dbo].[UNIT].[HMY] ,[clv].[dbo].[UNITTYPE].[sdesc] ,[clv].[dbo].[UNIT].[SRENT] ,[clv].[dbo].[UNIT].[dtAvailable] , clv.dbo.property.saddr1, clv.dbo.unit.scode FROM [clv].[dbo].[UNIT] inner join clv.dbo.PROPERTY on clv.dbo.property.hmy = clv.dbo.unit.hproperty inner join clv.dbo.UNITTYPE on clv.dbo.UNITTYPE.hmy = clv.dbo.unit.hunittype WHERE saddr1 = #session.saddr1# and ( hunittype='2' OR (hunittype='3') OR (hunittype='4') OR (hunittype='5') OR (hunittype='6') OR (hunittype='7') OR (hunittype='8') OR (hunittype='25') OR (hunittype='26') OR (hunittype='27') OR (hunittype='28') OR (hunittype='29') OR (hunittype='30') OR (hunittype='31') OR (hunittype='32') OR (hunittype='33') OR (hunittype='34') OR (hunittype='35') OR (hunittype='36') OR (hunittype='37') OR (hunittype='39') OR (hunittype='41') OR (hunittype='42') OR (hunittype='44') OR (hunittype='45') OR (hunittype='46') OR (hunittype='47') OR (hunittype='48') OR (hunittype='49') OR (hunittype='50')
'message' Field Data:<xml>....<test>ABC</test>....</xml>How do I query out 'ABC' from this xml field data in a SQL Server?Assume message field is in table 'Persons'
I am getting setup problems with Reporting Services 2005.
When I try to connect through SQL Server Management Studio, the browser in the connect dialog can see the Reporting Services 2005 instance, but when I try to connect, I get an error dialog with the message, "The Report Services instance could not be found (Microsoft.SqlServer.Management.UI.RSClient)"
I manually setup ReportServer and ReportManageer virtual directories in IIS to point to the corresponding directories within the Reporting Services installation. Was I supposed to do this manually? Was the installer supposed to set this up?
Anyway, when I browse to http://localhost/ReportServer/ I get:
* The report server has encountered a configuration error. See the report server log files for more information. (rsServerConfigurationError) o Access to the path 'C:Program FilesMicrosoft SQL ServerMSSQL.4Reporting ServicesReportServerRSReportServer.config' is denied.
When I browse to http://localhost/ReportManager/ I get:
An error occurred while parsing the configuration file. The ReportServerVirtualDirectory element is missing.
Anyone know what I'm doing wrong and what I can do to fix?
Configuration: SQL Server 2005 Enterprise edition on Microsoft Cluster. Machines named Node1, Node2 and NodeV (Virtual Node). Best practices require SSRS be deployeed on an IIS6.0 server. Reporting services was installed on the Web1 machine and pointing back to the database on the NodeV platform. Everything appears to run fine in Reporting Services. However, when I use the ReportViewer control from within a webpage, the basic control appears along with the message "root element is missing". Using the same code, I point the ReportViewer control to a different server Web2 where Sql server standard, IIS and RS are installed and the report renders properly. What am I missing in the configuration of the Web1 box as far as IIS or SSRS are concerned?
A branching element is critical to any process flow. Currently, as far as I know, there;s only a Conditional Split Data Flow Element. There is no direct way I can branch out in a control flow.
In some cases, I could branch by using a conditional operator ?: to either create a dynamic sql string for each patch, or a package name for a Package Execution task and so on.
This approach is not always good enough.
Are there others out there who want a Conditional Control Flow element?
I don't know MS-SQLserver internal system at all. I 've just used Oracle a couple years ago and so in some cases (e.g using TP-monitor MTS or Tuxedo) you can switch off the implicit transaction by using the option AUTOCOMMIT ON/OFF.
How can switch off the implicit transaction system on MS-SQLServer ?
I created a SELECT statement that uses the FOR XML EXPLICIT clause so that SQL Server data can be exported to another system. Format thios system accepts is XML. What should I define within a DTS package so that this Select statement is executed and an XML file is created?
I have an cursor that loops 720 times. Each FETCH does 6 INSERTS. Each FETCH the 6 INSERTs a total of 200-300 records appended.
Would explicit transactions speed up the cursor?
In Oracle, I would keep track of how many times I've looped, and commit every nth time. I would put a catch remainder COMMIT at the end of the procedure.
I've got the IF @@TRANCOUNT > 19 COMMIT TRAN (about 6k records), but say I had <= 19 remaining at the end of the cursor, how do you get those to commit? I tried to put an IF @@TRANCOUNT > 1 COMMIT TRAN, but that didn't work.
What's a good @@TRANCOUNT to commit at in this case?
In the SP below, I get the following error when I run it: Explicit value must be specified for identity column in table 'Tests' when IDENTITY_INSERT is set to ON.
From what I've read of the INSERT method, it says you can add a variable value. So I don;t understand why it won't take mine.
CREATE PROC qaspAddTestRecord AS
DECLARE @aKey bigint DECLARE @aTestSuiteId bigint
exec qaspGetNewID 'Tests', @aKey OUT
SELECT @aTestSuiteId= NewID FROM ID WHERE TableName='TestSuite'
SET IDENTITY_INSERT Tests ON
INSERT INTO Tests (TestSuiteId, TestIDInternal) VALUES (@aTestSuiteId, @aKey)
I'm trying to set the value of the SelectedDatabases collection property of an Update Statistics Task using an expression for the task, basing it on a user variable. Ultimately, I'll set that variable's value from a configuration file at run time and thereby specify the database whose statistics I want to update.
When I set this up in the designer, I get an error at the time I try to save the package:
TITLE: Microsoft Visual Studio ------------------------------
Nonfatal errors occurred while saving the package: Error at Update Statistics Task: The result of the expression "@[User::SelectedDatabase]" on property "SelectedDatabases" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
Error at Update Statistics Task: The result of the expression "@[User::SelectedDatabase]" on property "SelectedDatabases" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
------------------------------ BUTTONS:
OK ------------------------------
I used the expression editor's expression builder to select the user variable, so I assume the syntax is correct. However, I'm suspecting that there's some additional syntax required to store that value in the collection's array. I cannot find any references to any special formats and am wondering if anyone know's how to do this correctly. On the other hand, maybe it is telling me setting this property from an expression is not even allowed. If it's not allowed, why is it in the list of properties to set? In any case, none of this is clear.
Hi All, I receive data in the form of xml file and it has one to many relationships. Below I am giving you the sample node and how it should be parsed. Please suggest whether this is possible in SSIS using XML Task if not pls suggest a fast and efficient method. <Element1> <SubElement1>S1</SubElement1> <SubElement2>S2</SubElement2> .. .. <SubElement20> <Child1>c1</Child1> <Child2>c2</Child2> </SubElement20> <SubElement20> <Child1>c3</Child1> <Child2>c4</Child2> </SubElement20> .. .. <SubElement25> <SubElement25Child1>s25_One</SubElement25Child1> </SubElement25> <SubElement25> <SubElement25Child1>s25_Two</SubElement25Child1> </SubElement25> </Element1> The above one is a sample xml. I should get 8 rows for the above element. There are 2 elements with "SubElement20" and it has 2 childs each. So there are 4 such nodes. The element "SubElement25" occured 2 times. Now we need to multiply these two counts.i.e. 4 * 2 = 8. The relationship in the xml is one to many. After parsing the xml I should get the rows in the following format and it should be one to one mapping only. S1, S2, ....c1,... s25_One S1, S2, ....c1,... s25_Two S1, S2, ....c2,... s25_One S1, S2, ....c2,... s25_Two S1, S2, ....c3,... s25_One S1, S2, ....c3,... s25_Two S1, S2, ....c4,... s25_One S1, S2, ....c4,... s25_Two The options are as follows 1) Writing a custom source component to parse this xml and produces the rows in the specified way. 2) First write a XSLT or Style Sheet to break the above one to many relationships to one to one relationships and create an xml file and then use native SSIS xml parser. 3) Using the Script Task as the source component and do the parsing. But I think it would be difficult to handle and maintain.
If any other suggessions please share. Thanks in advance. Regards Venkat.
declare @inputXml xml set @inputXml='<root> Â <row> Â Â Â <col>start_date</col> Â Â Â <col>1</col> Â Â Â <col>rpton#on#13-May-2015|$|rpton#on#13-May-2015</col>
I have do a sql function for return a list of element from a query send in variable.
When I test the function on self I have no problem.
But when I use the function in a sql query I have problem.
example :
Code SnippetSELECT APPLI_SUPPLIER.N_SUPPLIER_ID, APPLI_SUPPLIER.V_SUPPLIER_LABEL, dbo.APPLI_RETURN_LIST_ITEM('SELECT DISTINCT APPLI_CONSTRUCTION.V_PROCESS_CODE FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL) AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2,3,4,5,6))') AS V_PROCESS_ITEMS FROM APPLI_SUPPLIER INNER JOIN APPLI_SUPPLIER_SKILL ON APPLI_SUPPLIER.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID WHERE (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2, 3, 4, 5, 6))
This is the error :
Server: Msg 557, Level 16, State 2, Procedure APPLI_RETURN_LIST_ITEM, Line 24 Only functions and extended stored procedures can be executed from within a function.
When I do an exec of the function I have this problem :
Code Snippet exec APPLI_RETURN_LIST_ITEM('SELECT DISTINCT APPLI_CONSTRUCTION.V_PROCESS_CODE FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL) AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2,3,4,5,6))')
Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near 'SELECT DISTINCT APPLI_CONSTRUCTION.V_PROCESS_CODE FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL) AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2,3,4,5,6))'
This is the function
Code Snippet CREATE FUNCTION [dbo].[APPLI_RETURN_LIST_ITEM] (@QUERY AS VARCHAR(3900)=null) RETURNS varchar(8000) AS BEGIN -- Insert statements for procedure here declare @v_List_ITEM as varchar(8000) set @v_List_ITEM='' if @QUERY is not null Begin declare @cur_Lect_ITEM CURSOR; declare @FUNCTION AS NVARCHAR(4000); Declare @ITEM as VARCHAR(255); SET @FUNCTION = 'set @mainCursor=cursor for ' + @QUERY + ' for read only open @mainCursor'
EXEC sp_executesql @FUNCTION,N'@mainCursor cursor output', @cur_Lect_ITEM output fetch next from @cur_Lect_ITEM into @ITEM while @@fetch_status=0 begin set @v_List_ITEM=@ITEM + ' ; ' + @v_List_ITEM fetch next from @cur_Lect_ITEM into @ITEM end deallocate @cur_Lect_ITEM SET @v_List_ITEM=REPLACE(REPLACE(@v_List_ITEM, CHAR(13), ''), CHAR(10), '') set @v_List_ITEM=left(@v_List_ITEM,len(@v_List_ITEM)-3) End RETURN @v_List_ITEM END
HiI have to create an XML file based on a SQL SERVER 2005 table.Everything works fine:SELECT 1 as Tag, NULL as Parent, Ex_Id as [Exam!1!Ex_Id], NULL as [Ex_Title!2!!cdata] FROM Exams WHERE ex_State = 'P' UNION ALL SELECT 2 as Tag, 1 as Parent, Ex_Id, ex_Title FROM Exams WHERE ex_State = 'P' order by [Exam!1!Ex_Id], [Ex_Title!2!!cdata] FOR XML EXPLICIT, ROOT('Exams') BUT when i add another column, a text column, I get into trouble: SELECT 1 as Tag, NULL as Parent, Ex_Id as [Exam!1!Ex_Id], NULL as [Ex_Title!2!!cdata], NULL as [Ex_Situation!3!!cdata] FROM Exams WHERE ex_State = 'P' UNION ALL SELECT 2 as Tag, 1 as Parent, Ex_Id, ex_Title, ex_Situation FROM Exams WHERE ex_State = 'P' order by [Exam!1!Ex_Id], [Ex_Title!2!!cdata], [Ex_Situation!3!!cdata] FOR XML EXPLICIT, ROOT('Exams') The problem is clearly the fact that i have to sort on the text column, plus the fact that this column requires the CDATA tag enclosurePlease help me....i'm desperate :(
