XML Data Task XPath No Results Returned
Oct 22, 2007
Hi,
I'm trying to use the XML Task XPath operation for the first time but having some problems. I'm using the following settings:
Source type: File connection
SaveOperationResult: True (file destination)
SecondOperandType: Direct Input
PutResultInOneNode: False
XPathOperation: Values
The XML file I'm testing has the following stucture:
<?xml version="1.0" standalone="yes"?>
<BackupDataSet xmlns="http://tempuri.org/BackupDataSet.xsd">
<Device>
<DeviceID>6356452a-e7a6-42e1-895a-d4ade62210d5</DeviceID>
<UserID>1533c44f-c263-db11-9db3-000e9bd9f98d</UserID>
</Device>
</BackupDataSet>
When I set the SecondOperand to /* the output is a concatenated text string of DeviceID and UserID values. I'm trying to get just the DeviceID but perhaps my understanding of XPath syntax is wrong. I've tried setting the SecondOperand to the following:
//Device/DeviceID
/BackupDataSet/Device/DeviceID
//DeviceID
All of these return no results. What am I doing wrong?
Regards,
Greg
View 3 Replies
ADVERTISEMENT
Jul 14, 2015
I am trying to use a XML task in SSIS to get a session ID value from this XML:
<?xml version="1.0" encoding="utf-16"?>
<AuthenticationResult xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<authenticated xmlns="[link]">true</authenticated>
<sessionId xmlns="[link]">0000-0000000-00000000000-000000</sessionId>
[Code] ....
However I cant get the task to return the Session ID
Task setup:
Operation Type: XPATH
SourceType: File connection
Source: testfile.xml
SaveOperationResult: True
DestinationType: Variable
Destination: User::SessionID
OverwriteDestination: True
SecondOperandType: Direct input
SecondOperand: //sessionId
Namespaces: (Collection)
PutResultsInOneNode: False
XPathOperation: Values
No namespaces are specified.
View 8 Replies
View Related
Aug 22, 2006
I have a query that returns results based on information in several tables. The problem I am having is that is there are no records in the one table it doesn't return any information at all. This table may not have any information initially for the employees so I need to show results whether or not there is anything in this one table.
Here is my select statement:
SELECT employee.emp_id, DATEDIFF(mm, employee.emp_begin_accrual, GETDATE()) * employee.emp_accrual_rate -
(SELECT SUM(request_duration) AS daystaken
FROM request) AS daysleft, employee.emp_lname + ', ' + employee.emp_fname + ' ' + employee.emp_minitial + '.' AS emp_name,
department.department_name, location.location_name
FROM employee INNER JOIN
request AS request_1 ON employee.emp_id = request_1.emp_id INNER JOIN
department ON employee.emp_department = department.department_id INNER JOIN
location ON department.department_location = location.location_id
GROUP BY employee.emp_id, employee.emp_begin_accrual, employee.emp_accrual_rate, employee.emp_fname, employee.emp_minitial,
employee.emp_lname, department.department_name, location.location_name
ORDER BY location.location_name, department.department_name, employee.emp_lname
The section below is the part that may or may not contain information:
SELECT (SELECT SUM(request_duration) AS daystaken
FROM request) AS daysleft
So I need it to return results whether this sub query has results or not. Any help would be greatly appreciated!!!
TIA
View 3 Replies
View Related
Jun 20, 2006
I have a strange problem. I have some code that executes a sql query. If I run the query in SQL server query analyzer, I get a set of data returned for me as expected. This is the query listed on lines 3 and 4. I just manually type it into query analyzer.
Yet when I run the same query in my code, the result set is slightly different because it is missing some data. I am confused as to what is going on here. Basically to examine the sql result set returned, I write it out to an XML file. (See line 16).
Why the data returned is different, I have no idea. Also writing it out to an XML file is the only way I can look at the data. Otherwise looking at it in the debugger is impossible, with the hundreds of tree nodes returned.
If someone is able to help me figure this out, I would appreciate it.
1. public DataSet GetMarketList(string region, string marketRegion)2. {3. string sql = @"SELECT a.RealEstMarket FROM MarketMap a, RegionMap b " + 4."WHERE a.RegionCode = b.RegionCode"; 5. DataSet dsMarketList = new DataSet();6. SqlConnection sqlConn = new SqlConnection(intranetConnStr); 7. SqlCommand cmd = new SqlCommand(sql,sqlConn);8. sqlConn.Open();9. SqlDataAdapter adapter = new SqlDataAdapter(cmd); 10. try11. {12. adapter.Fill(dsMarketList);
13. String bling = adapter.SelectCommand.CommandText;//BRG 14. dsMarketList.DataSetName="RegionMarket"; 15. dsMarketList.Tables[0].TableName = "MarketList"; 16. dsMarketList.WriteXml(Server.MapPath ("myXMLFile.xml" )); // The data written to 17. myXMLFile.xml is not the same data that is returned when I run the query on line 3&4 18. // from the SQL query 19. } 20. catch(Exception e) 21. { 22. // Handle the exception (Code not shown)
View 2 Replies
View Related
Nov 2, 2006
I'm importing a large csv file two different ways - one with Bulk Import Task and the other way with the Data Flow Task (flat file source -> OLE DB destination).
With the Bulk Import Task I'm putting all the csv rows in one column. With the Data Flow Task I'm mapping each csv value to it's own column in the SQL table.
I used two different flat file sources and got the following:
Flat file 1: Bulk Import Task = 12,649,499 rows; Data Flow Task = 4,215,817 rows
Flat file 2: Bulk Import Task = 3,403,254 rows; Data Flow Task = 1,134,359 rows
Anyone have any guess as to why this is happening?
View 9 Replies
View Related
Oct 16, 2007
Hi,
I've got what is probably a simple question, but for the life of me I cannot figure it out and it is starting to do my head in . I have the following query:-
Select e.intEmployeeID AS ID, e.txtFirstName AS [First Name], e.txtLastName As [Last Name], e.txtMobile As [Mobile], i.txtWorkEmail as from EMPLOYEE AS e INNER JOIN EMPLOYEE_INFORMATION AS i ON i.intEmployeeID = e.intEmployeeID WHERE (((e.blnActive = -1) AND (e.txtFirstName Like '%b%')) OR ((e.blnActive = -1) AND (e.txtLastName Like '%b%')) ) ORDER By e.txtFirstName
In my database, the field blnActive is a BIT field. When I try to run this query, I get no results returned. If I remove the (e.blnActive = -1) WHERE statement segment, I get the correct results. The field blnActive contains -1 for every record but I cannot work out why it will not return these records .... aaarrrrggghhhh
Thanks to those who feel my pain !!
View 2 Replies
View Related
Oct 16, 2006
Hi all - i'm trying to put together my first .Net web page (have switched from Dreamweaver to VWD - VWD keeps swapping my tab-indents for spaces, and none of the options stop it!).Here's a table that i'm trying to query: ItemID | ReviewRating | ReviewRatingOutOfAs i'm sure you've guessed, it's a reviews table, where there can be several records with the same ItemID and different (or the same) ReviewRating and ReviewRatingOutOf's. As the reviews are collected from lots of sources, the ReviewRatingOutOf will change (one review might be 3/5, while the next, for the same ItemID, could be 8/10, etc). Now, what i'm trying to do is return a list of ItemID's ordered by their RATIO (which is the sum of each ItemID's ReviewRating's divided by the sum of each ItemID's ReviewRatingsOutOf's - in other words, average score). My first guess was this:"SELECT DISTINCT ItemID FROM Reviews ORDER BY SUM(ReviewRating)/SUM(ReviewRatingOutOf)" - unfortunately that doesn't work (problems with the SUM aggregate functions, and overflow errors, whatever they are). Now, this string works: "SELECT ItemID FROM Reviews GROUP BY ItemID ORDER BY SUM(ReviewRating)" - right now, that just adds up the ReviewRatings, so an item with 10 reviews that only got awarded 1/5, 1/10, 1/8, etc (all 1's, therefore achieving a combined ReviewRating of 10 out of a very much higher ReviewRatingOutOf), would appear higher than an item with 1 review that got 5/5. Making the string into this: "SELECT ItemID FROM Reviews GROUP BY ItemID ORDER BY SUM(ReviewRating)/SUM(ReviewRatingOutOf)" (which is what I need), unfortunately gives me errors...Anyone have any ideas? Is there possibly a way to simply read all the distinct ItemID's with SQL, then get the two SUM's for each ItemID, then calculate the ratio of the two SUM's, and stick the ItemID's and the ratio into some sort of array, and have C# order the array for me, based on the ratio? I'd appreciate an example of that if possible, as i'm a complete C# beginner :-)Thanks in advance!
View 7 Replies
View Related
Sep 2, 2004
I am doing some SELECT queries on my database through ASP, but for example, I only want to return the 50 most recent entries that match the criteria. Is there any easy way to limit the number of results returned?
View 1 Replies
View Related
Jan 24, 2012
I am getting the following results from my query that contains a subquery, but I don't understand why the values in the [Total Volume M_Active_Previous] are being repeated with the same value. I should be getting different values returned for each row like in the [Total Volume M_Active] column.
Why the values are all the same and how I can fix this?
DayNoDayTotal Volume M_ActiveTotal Volume M_Active_Previous
6 Friday11161 72491
7 Saturday5687 72491
2 Monday14354 72491
1 Sunday3966 72491
4 Wednesday12340 72491
3 Tuesday12018 72491
5 Thursday11833 72491
Here is the SQL I'm using.
Code:
DECLARE @StartDate datetime, @EndDate datetime, @Prev_StartDate datetime, @Prev_EndDate datetime
SET @StartDate = '2011-03-13 00:00:00.000'
SET @EndDate = '2011-03-19 23:59:59.999'
SET @Prev_StartDate = '2011-03-06 00:00:00.000'
SET @Prev_EndDate = '2011-03-12 23:59:59.999'
[Code] ....
View 5 Replies
View Related
May 17, 2007
Iam using front page to dispalay my results.
At the bottom it shows me 1/10 i.e 1st page of 10 pages.
but what do i do if i want it to be shown as 1-10 out of 100 (if each page contains 10 results).
or it would be really good if i get count of both no. of recors as well as no. of pages.
View 2 Replies
View Related
Jan 8, 2004
I am trying to pull results from an SQL Server DB into an dataset where a particular field (SMALLDATETIME) is within a particular date range. The code I was using worked fine when the database was in Access. I have made several changes already but am still getting 0 results returned when there is data that should be returned.
I was using in Access:
Dim StrSQL = "SELECT ID FROM myTable WHERE myDateField>=#" & startDate & "# AND myDateField<=#" & stopDate & "# ORDER BY ID"
I have changed this for SQL Server to:
Dim StrSQL = "SELECT ID FROM myTable WHERE myDateField>='01/01/2003 00:00:01' AND myDateField<='01/01/2004 23:59:59' ORDER BY ID"
But I am always returned 0 results even if the date range should return plenty of data. I have also tried using the BETWEEN structure with the same result.
Is there a particular format for the date I am comparing with?
Am I missing something else in my query?
The connection / permissions and everything else are correct as I can read and write data to the database in numerous other pages. It is just this date comparison that is not working.
Many thanks for any help or comments you can provide.
View 2 Replies
View Related
Sep 10, 2015
I have two tables A and B, A has 8000 and B has 8122 records. I want to see what records are missing. I tried EXCEPT and it returned zero rows. I used where non exists also still no records.
View 5 Replies
View Related
Sep 10, 2007
I have the follow table.
/****** Object: Table [dbo].[deletethisTempOut] Script Date: 09/10/2007 09:20:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[deletethisTempOut](
[ThemeName] [varchar](60) NULL,
[intLocationCount] [int] NULL,
[dblRepValueA] [float] NULL,
[dblRepValueB] [float] NULL,
[dblRepValueC] [float] NULL,
[dblRepValueD] [float] NULL,
[dblTotalRepValue] [float] NULL,
[dblLimit1] [float] NULL,
[dblLimit2] [float] NULL,
[dblLimit3] [float] NULL,
[dblLimit4] [float] NULL,
[dblTotalLimit] [float] NULL,
[fltEmployeecount] [float] NULL,
[intAreaLevel1] [tinyint] NOT NULL,
[strFullName] [varchar](13) NOT NULL,
[strAreaLevel2] [varchar](20) NOT NULL,
[strAreaLevel3] [varchar](20) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
If I use the following SQL:
SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,
dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3
FROM deletethisTempOut
ORDER BY strAreaLevel2, strAreaLevel3
GET Following correct results:
Adair
284
899989594
0
574857716
190479902
1665327212
0
0
0
0
1665327212
0
1
United States
1
1
IF I use the following SQL I get the wrong results:
SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,
dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3
FROM deletethisTempOut
ORDER BY ThemeName
WRONG results:
Adair
74
81733110
0
49616018
24671651
156020779
50510500
0
0
0
203870779
0
1
United States
50
1
Adair
437
1468698657
0
495479839
353202768
2317381264
12984266
0
0
0
2315676030
0
1
United States
25
1
Adair
1813
20309722045
0
6597005374
4253819645
31160547064
43636703
0
0
0
31135010742
0
1
United States
11
1
Adair
606
439581417
0
331746662
132240332
903568411
0
0
0
0
903568411
0
1
United States
45
1
Adair
236
350256381
0
524269553
504973831
1379499765
4080368
0
0
0
1380473415
0
1
United States
23
1etc.....
View 6 Replies
View Related
Oct 18, 2006
Stored Procedure ProblemThis is probably a simple problem but i would appreciate some help.I have a stored procedure that takes the order date time plus other information fromvarious tables but the information is being returned double:ie 4 rows are being returned instead of two. Can anyone see where i am going wrong?Many thanksMartinThis is the stored procedureALTER PROCEDURE dbo.SP_RetrieveOrdersASSELECT distinct OD.DateCreated, O.OrderID,O.UserID,O.OrderTotal,O.Sent,O.Delivered,O.Paid,C.CustomerNameFROM Orders As O,Customers As C,OrderDetails as ODWHERE O.UserID=C.UserID And O.OrderTotal >0 RETURNThese are the results that are returnedDateCreated OrderID UserID OrderTotal Sent Delivered Paid CustomerName ----------------------- ----------- ------------------------------------ ---------------- ------ --------- ------ --------------- 18/10/2006 14:49:00 41 7A2E2B9B-57FA-4329-B4BB-D7ED965AA183 500 <NULL> <NULL> <NULL> bill 18/10/2006 14:49:00 42 7A2E2B9B-57FA-4329-B4BB-D7ED965AA183 590 <NULL> <NULL> <NULL> bill 18/10/2006 15:05:00 41 7A2E2B9B-57FA-4329-B4BB-D7ED965AA183 500 <NULL> <NULL> <NULL> bill 18/10/2006 15:05:00 42 7A2E2B9B-57FA-4329-B4BB-D7ED965AA183 590 <NULL> <NULL> <NULL> bill No rows affected.(4 row(s) returned)If I leave OD.DateCreated ie use ALTER PROCEDURE dbo.SP_RetrieveOrdersASSELECT distinct O.OrderID,O.UserID,O.OrderTotal,O.Sent,O.Delivered,O.Paid,C.CustomerNameFROM Orders As O,Customers As C,OrderDetails as ODWHERE O.UserID=C.UserID And O.OrderTotal >0 RETURNthen there is no problem. I get:41 7A2E2B9B-57FA-4329-B4BB-D7ED965AA183 500 <NULL> <NULL> <NULL> bill 42 7A2E2B9B-57FA-4329-B4BB-D7ED965AA183 590 <NULL> <NULL> <NULL> bill No rows affected.(2 row(s) returned)
View 2 Replies
View Related
Nov 6, 2006
Hi,
I need to write a select query that will run all returned results through a separate stored procedure before returning them to me.
Something like....
SELECT TOP 10 userid,url,first name FROM USERS ORDER by NEWID()
......and run all the selected userids through a stored procedure like.....
CREATE PROCEDURE AddUserToLog (@UserID int ) AS INSERT INTO SelectedUsers (UserID,SelectedOn) VALUES (@UserID,getdate())
Can anyone help me to get these working togethsr in the same qurey?
Thanks
View 7 Replies
View Related
Jul 10, 2007
Hi, I was wondering if any SQL Server gurus out there could help me...I
have a table which contains text resources for my application. The text
resources are multi-lingual so I've read that if I add a html language
indicator meta tag e.g.<META NAME="MS.LOCALE" CONTENT="ES">and
store the text in a varbinary column with a supporting Document Type
column containing ".html" of varchar(5) then the full text index
service should be intelligent about the language word breakers it
applies when indexing the text. (I hope this is correct technique for
best multi-lingual support in a single table?)However, when I come to query this data the results always return 0 rows (no errors are encountered). e.g.DECLARE @SearchWord nvarchar(256)SET @SearchWord = 'search' -- Yes, this word is definitely present in my resources.SELECT * FROM Resource WHERE CONTAINS(Document, @SearchWord)I'm a little puzzled as Full Text search is working fine on another table that employs an nvarchar column.Any pointers / suggestions would be greatly appreciated. Cheers,Gavin.
View 1 Replies
View Related
Jul 10, 2007
Hi, I was wondering if any SQL Server gurus out there could help me...
I have a table which contains text resources for my application. The text resources are multi-lingual so I've read that if I add a html language indicator meta tag e.g.
<META NAME="MS.LOCALE" CONTENT="ES">
and store the text in a varbinary column with a supporting Document Type column containing ".html" of varchar(5) then the full text index service should be intelligent about the language word breakers it applies when indexing the text. (I hope this is correct technique for best multi-lingual support in a single table?)
However, when I come to query this data the results always return 0 rows (no errors are encountered). e.g.
DECLARE @SearchWord nvarchar(256)
SET @SearchWord = 'search' -- Yes, this word is definitely present in my resources.
SELECT * FROM Resource WHERE CONTAINS(Document, @SearchWord)
I'm a little puzzled as Full Text search is working fine on another table that employs an nvarchar column.
Any pointers / suggestions would be greatly appreciated. Cheers,
Gavin.
View 1 Replies
View Related
Feb 13, 2004
Hi, have configured an ODBC linked server for an Adaptive Server Anywhere (ASA6.0) database.
I have to write a function (not a procedure) that receives a number (@Code) and returns 1 if it was found on a table in the linked server, or 0 if not. Looks very simple...
One problem, is that the queries on a linked-server must be made through the OPENQUERY statement, which doesen't support dynamic parameters. I've solved this making the whole query a string, and executing it, something like this:
SET @SQL='SELECT * FROM OPENQUERY(CAT_ASA, ''SELECT code FROM countries WHERE code=' + @Code + ''')'
EXEC sp_executesql @SQL
(CAT_ASA is the linked-server's name)
Then, i would use @@ROWCOUNT to determine if the code exists or not. But before this, a problem appears: sp_executesql is not allowed within a function (only extended procedures are allowed).
Does somebody know how to make what i want?? I prefer to avoid using temporary tables.
Thanks!
View 3 Replies
View Related
Mar 29, 2007
Hi,
am trying to do something which I thought would be simple to do in SSIS, several hours am still struggling with it. Not sure if this a bug or a restriction of the product. Or if im hitting some kind of compatability issue because im trying to get to a Oracle database.
Have a sql task which passes in a parameter, I then query my Oracle database and am trying the result (single row) into another variable.
Variable:
Variable Name = Subsystem
Scope= Package
Value = pgc
Data Type = string
SQL:
SELECT SUBSYSTEM_DS AS SUBSYSTEM_DS FROM SYS_SUBSYSTEM WHERE SUBSYSTEM_ID = ?
Have also tried:
SELECT SUBSYSTEM_DS AS SUBSYSTEM_DS FROM SYS_SUBSYSTEM WHERE SUBSYSTEM_ID = ?0
Result Set = Single Row
Parameter Mapping:
VariableName = User:ubsystem
Direction = Input
Data Type=Varchar
Parameter Name= 0
Parameter Size= -1 (have also tried 3 - length of variable)
Oracle Table:
SQL> desc sys_subsystem
Name Null? Type
----------------------------------------- -------- ----------------------------
SUBSYSTEM_ID NOT NULL CHAR(3)
SUBSYSTEM_DS NOT NULL VARCHAR2(40)
....
....
...
The Error:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "SubsystemName": "Single Row result set is specified, but no rows were returned.".
I have another SQL Task that performs an update on this same table and I also pass in the same variable but it works?
SQL:
UPDATE sys_subsystem
SET as_process_fg = 'X'
WHERE subsystem_id = ?0
The parameter mappings are the same as above.
Any assistance here would be much appreciated.
Thanks
Mick
View 5 Replies
View Related
Jun 9, 2014
I'm trying to go deeper into SQL Server's XML support. When looking at a recent forum post, I got curious to see if I could achieve the same result with XPath and FLOWR. Here's the data I'm working with:
create table #data (myXMLData xml)
insert into #data(myXMLData) values (
'<?xml version="1.0" encoding="utf-8"?>
<order>
<Product Index="e1e2c499-f9...etc...." ProductID="12" >
<Attribute Name="Paper...etc..." />
[Code] ....
Here's the working XPath query and results:
select data.mynodes.value('@Name', 'varchar(50)') Name
from #data
cross apply myXMLData.nodes('/order/Product/Drops/Drop[@Number="1"]/Area') data(mynodes)
Results:
Name
43001PBOX
[Code] .....
Here's my first attempt at a FLWOR expression. Note that doesn't produce the same results.
select myXmlData.query('
for $drop in /order/Product/Drops/Drop
for $Name in $drop/Area/@Name
where $drop/@Number=1
return data($Name)
')
from #data
Results:
43001PBOX 43001R001 43001R002 43023PBOX 43023R001 43023R002 43031PBOX 43031R001
How to build a FLOWR-based query that produces the same result as the pure XPath one?
View 1 Replies
View Related
Nov 20, 2015
I have an XML field with some data that looks about like this:
<steps>
<step userName="abc"></step>
<step userName="abc"></step>
<step userName="saf2"></step>
<step userName="abc"></step>
</steps>
I have a query something like this:
SELECT XmlField.value('(//step/@userName)[1]', 'VARCHAR(100)') AS userName
FROM theTable
I would like to have some kind of indicator in my TSQL results of the number of each <step> node. For instance, saf2 would be a 3. I think this is similar to a ROW_NUMBER() kind of function in normal table data.
Is there a good way to get a 1, 2, 3, and 4 value for this sample data?
View 2 Replies
View Related
Jun 12, 2008
Hi, I am seeking a hopefully easy solution to spit back an error message when a user receives no results from a SQL server db with no results. My code looks like this What is in bold is the relevant subroutine for this problem I'm having. Partial Class collegedb_Default Inherits System.Web.UI.Page Protected Sub submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submit.Click SqlDataSource1.SelectCommand = "SELECT * FROM [college_db] WHERE [name] like '%" & textbox1.Text & "%'" SqlDataSource1.DataBind() If (SqlDataSource1 = System.DBNull) Then no_match.Text = "Your search returned no results, try looking manually." End If End Sub Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load SqlDataSource1.SelectCommand = "SELECT * FROM [college_db] ORDER BY [name]" SqlDataSource1.DataBind() End Sub Protected Sub reset_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles reset.Click SqlDataSource1.SelectCommand = "SELECT * FROM [college_db] ORDER BY [name]" SqlDataSource1.DataBind() End SubEnd Class I'm probably doing this completely wrong, I'm a .net newb. Any help would be appreciated. Basically I have GridView spitting out info from a db upon Page Load, but i also have a search bar above that. The search function works, but when it returns nothing, I want an error message to be displayed. I have a label setup called "no_match" but I'm getting compiler errors. Also, next to the submit button, I also have another button (Protected sub reset) that I was hoping to be able to return all results back on the page, similar to if a user is just loading the page fresh. I'd think that my logic would be OK, by just repeating the source code from page_load, but that doens't work.The button just does nothing. One final question, unrelated. After I set this default.aspx page up, sorting by number on the bottom of gridview, ie. 1,2,3,4, etc, worked fine. But now that paging feature, as long with the sorting headers, don't work! I do notice on the status bar in the browser, that I receive an error that says, "error on page...and it referers to javascript:_doPostBack('GridView1, etc etc)...I have no clue why this happened. Any help would be appreciated, thanks!
View 2 Replies
View Related
Apr 1, 2007
hi, like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right? so, is there something that i can use to hold those records so that i can do the delete and update just on those records and don't need to query twice? or is there a way to do that in one go ?thanks in advance!
View 1 Replies
View Related
Sep 15, 2006
Hi All, we got a problem that I hope you guys can help us with.
We are trying to extract data from a SQL database in XML format to a variable (string) using an 'Execute SQL Task'
In the task we are using an ADO.NET connection to the database which is fine and via direct input we are using this query.
SELECT
title,
initials,
surname,
username,
gender,
emailaddress,
addressline1,
addressline2,
addressline3,
town,
postcode,
country,
emailcontact,
postalcontact,
telephone
FROM
CRMCustRecords
FOR XML path('customer'), root('CRMData'), ELEMENTS XSINIL
In SSMS this is fine and returns an ok (to my eyes anyway!) XML resultset.
<CRMData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<customer>
<title>xxx</title>
<initials>xxxxxxx</initials>
<surname>xxxxxx</surname>
<username>xxxxxx</username>
<gender>x</gender>
<emailaddress>xxxx@xxxxxxx.xx.xx</emailaddress>
<addressline1>xxxxx</addressline1>
<addressline2 xsi:nil="true" />
<addressline3 xsi:nil="true" />
<town>xxxx</town>
<postcode>xxxxxxxxx</postcode>
<country>xx</country>
<emailcontact>x</emailcontact>
<postalcontact>x</postalcontact>
<telephone>99999999999</telephone>
</customer>
<customer>
</CRMData>
However the SSIS task (running exactly the same query) is returning
<ROOT>
<CRMData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<customer>
<title>xxx</title>
<initials>xxxxxxx</initials>
<surname>xxxxx</surname>
<username>xxxxxxxx</username>
<gender>x</gender>
<emailaddress>xxxxx@xxxxxxxxxx.xx.xx</emailaddress>
<addressline1>xxxxxxxxxx</addressline1>
<addressline2 xsi:nil="true" />
<addressline3 xsi:nil="true" />
<town>xxxxx</town>
<postcode>xxxxxxxx</postcode>
<country>xx</country>
<emailcontact>x</emailcontact>
<postalcontact>x</postalcontact>
<telephone>9999999999</telephone>
</customer>
</CRMData>
</ROOT>
I setup the query as a stored procedure and changed the SSIS accordingly. Running the sp from within SSMS was fine but the task still returned the wierd results.
This is completely unexpected and giving us headaches. It doesn't even look like well-formed XML to my eyes and won't display in IE without showing problems! Where are the eroneous /'s and ROOT nodes coming from?
I can't imagine we are the only people to have run into this and I'm sure we aren't doing something quite right - just stuck as to what we're doing wrong.
Hopefully I've provided enough info. If not just ask.
Thanks for any and all input
Pat
View 3 Replies
View Related
Aug 7, 2007
Hi,
I'm trying to use an execute SQL task with a simple select to get results and scan them.
I have to create a variable for each column to get results? or may i create something like a resultset variable?
Does this task return only one row and i have to loop manually? (maybe on al script task..) or can i get all returned data on a result set to be the input for next step?
Thanks!
View 6 Replies
View Related
Aug 15, 2006
How do I pass the results of a sql 'FOR XML' query to an XML Task? I tried using a user variable of type string. In my Exec SQL Task on the General tab I set the ResultType to XML. Then on the Result Set tab I map it (Result 0) to my user variable (string). Then in the XML Task I use that variable as the Source. However, it fails in the Exec Sql Task with error
"End tag 'CustomJob秱孒秱怈пField' does not match the start tag 'CustomJobField'
It looks like some extraneous chars are getting inserted into my tags?
I also tried setting the ResultType to SingleRow but that failed with error:
"The type of the value being assigned to variable "User::ForXmlResult" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ".
Any ideas? Thanks.
View 2 Replies
View Related
Dec 19, 2007
Hi All,
I'm currently using an Execute SQL Task to return XML data from a query into an SSIS string variable. In my FOR XML clause in SQL I'm specifying a certain name for my root tag, called "Accounts". This works great in Management Studio, however, the Execute SQL Task appends a <ROOT> and </ROOT> tag to the start and end of the string, so now it looks like:
<ROOT><Accounts>...all my elements...</Accounts></ROOT>
I'd like to remove the ROOT tags so that the <Accounts> tags are actually the root for this doc. What would be the best way to remove the ROOT tags from the SSIS string variable?
Thanks!
View 9 Replies
View Related
Sep 14, 2007
In sql mgt studio, I can save the query results to a file. How would I do this in an SSIS package?
View 2 Replies
View Related
Jul 22, 2015
I have a table with email addresses and CC_Flag.
Email | CC_Flag
xxxx 0
yyyy 1
zzzz 1
Using Task SQL, I am trying to pass these email addresses to two separate variables - To_field, Cc_field on basis of the CC_Flag..Is it possible to fill two variables from a single SQl Task,.
View 4 Replies
View Related
Jun 13, 2006
Is it possible to skip all steps following the script task results (Step 1) in a For Each container. I am iterating thru all the files in a For Each container and parsing a few lines of the file and based on the result I want to force the For Each loop to get to the next file instead of executing the next steps. Is it possible to force the for each loop to get the to the next file if the test criteria in the very first step (Script Task) fails. Any inputs will be much appreciated.
THanks,
MShah
View 1 Replies
View Related
Jul 20, 2005
I am working on a personal project and am drawing a complete blank(too much celebrating last night?) on the SQL term that is used toeliminate multiples of like data when it is returned from thedatabase.ie, instead of ....redblueredgreenit would return ...redbluegreenSorry for the trouble and thanks.
View 2 Replies
View Related
Jan 6, 2008
In the following procedure i write the results to a temp table called #temp1I now want to count the results of #temp1, if the count of #temp1 = 0
I want to insert 'No Records Found' into #temp.ERRORMSG else return what is in the table
any idea on how to do this?
ALTER PROC [dbo].[SPU_RPT_Savings_AnomalyDispatches] 40,'04/01/07|06/30/07'
@PropertyID varchar(4000),
@DropDown varchar(50)
AS
SELECT Client.CLIENT, Client.CLIENTID, ErrorEmailLog.ID, ErrorEmailLog.SITEID, ErrorEmailLog.PROPID, ErrorEmailLog.DISTINCTERRORS,
ErrorEmailLog.ERRORMSG, ErrorEmailLog.ERRORDATETIME, ErrorEmailLog.EMAILRECIPIENTS, Property.PROPERTY, Property.STREET,
Property.CITY, Property.STATE, Property.ZIP, Property.PHONE
INTO #TEMP1
FROM ErrorEmailLog INNER JOIN
Property ON ErrorEmailLog.PROPID = Property.PROPID INNER JOIN
Client ON Property.CLIENTID = Client.CLIENTID
WHERE (ErrorEmailLog.ERRORDATETIME BETWEEN SUBSTRING(CONVERT(VARCHAR(12), @DropDown), 0, 9)
View 3 Replies
View Related
May 4, 2006
Hi,
I am trying to create a simple BI Application for SSIS. In Visual Studio 2005 I just get a Data Flow Task from the toolbar and add it to the project. When I double click it I get the following error:
The task with the name "Data Flow Task" and the creation name "DTS.Pipeline.1" is not registered for use on this computer.
Then when I try to delete it it gives this other error:
Cannot remove the specified item because it was not found in the specified Collection.
I am creating this application in an administrator account in this computer, so I doubt the problem is related to permissions. I am running SQL Server 2005 and Visual Studio 2005 in WinXP Tablet PC Edition.
Any suggestions why this is happening and how to fix it?
View 17 Replies
View Related
