Article - Delete All Data Property
Sep 14, 2005Hi There
View 7 Replies
ADVERTISEMENT
Replication :: Article X Failed Data Validation (rowcount Only)
Jun 6, 2015I've created a merge replication a few days ago. it works correctly, but today when click synchronize publications, some of then encounter this error :
Data validation failed for one or more articles. When troubleshooting, check the output log files for any errors that may be preventing data from being synchronized properly. Note that when error compensation or delete tracking functionalities are disabled for an article, non-convergence can occur. (Source: MSSQL_REPL, Error number:
[Code] ....
Master Data Services :: Hard Delete All Soft Delete Records (members) In Database
May 19, 2012I am using Master Data Service for couple of months now. I can load, update, merge and soft delete data in MDS. Occasionally we even have to hard delete data from MDS. If we keep on soft deleting records in a MDS table eventually there will be huge number of soft deleted records. Is there an easy way to hard delete all the soft deleted records from all MDS tables in a specific Model.
View 18 Replies View RelatedChanging Code Page Property Using Property Expression Doesn't Work
Jun 16, 2006I am having problems exporting data into a flat file using specific code page. My application has a variable "User::CodePage" that stores code page value (936, 950, 1252, etc) based on the data source. This variable is assigned to the CodePage property of desitnation file connection using Property expression.
But, when I execute the package, the CodePage property of the Destination file connection defaults to the initial value that was set for "User:CodePage" variable in design mode. I checked the value within the variable during runtime and it changes correctly for each data source. But, the property of the destinatin file connection doesn't change and results in an error.
[Flat File Destination [473]] Error: Data conversion failed. The data conversion for column "Column01" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
[DTS.Pipeline] Error: The ProcessInput method on component "Flat File Destination" (473) failed with error code 0xC02020A0. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
If I manually update the variable with correct code page and re-run the ETL, everything works fine. Just that it doesn't work during run-time mode.
Can someone please help me resolve this.
Thanks much.
Value Of A Readonly Property Of Custom Task Is Not Updated In Property Window
Apr 17, 2008Hi,
I developed a simple custom control flow component which has several read/write properties and one readonly property (lets call it ROP) whichs Get method simple returns the value of a private variable (VAR as string). In the Execute method the VAR has a value assigened. When I put the value of ROP or VAR into MsgBox I can see the correct value. However when I execute the component I can not see the value of the ROP in the property window. I see the property but its value is empty string. For example when I put a breakpoint to postexecute or check the property before click OK in a MsgBox I would expect that the property value would be updated in SSIS as well. Is there a way how to display correct values of custom tasks properties in property window?
Thanks for any hints.
(URGENT) Cannot Be Written To The Property. The Expression Was Evaluated, But Cannot Be Set On The Property
May 7, 2008Untill recently I had a smooth running SSIS package,but suddenly it throws error syaing
"OnError,,,,,,,The result of the expression
"@[User:trTextFileImpDirectory] +"SomeTextStringHere"+ @[User:trANTTextFileName] +(DT_STR,30,1252) @[User:taging_Date_Key]+ "SomeTextStringHere"
" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property."
I have child SSIS package running under a parent package (through execute package task)
I have few flat file connection managers in child package for text file import , in which I am building text file path dynamically at run time by assigning an expression in connection string property of connection manager.
The Expression is as follows
"@[User:trTextFileImpDirectory] +"SomeTextStringHere."+ @[User:trANTTextFileName] +(DT_STR,30,1252) @[User:taging_Date_Key]+ +"SomeTextStringHere"
Where @[User:trTextFileImpDirectory] is a variable which contains path of directory containg text
files.Value in this variable is assigned at runtime from parent package's variable,which in turns fetch
value from a configuration file on local server.
With my current configuration this path has been configured to some other server's directory over network ( I.e my package picks text files from some other servers folder over network)
While
"Some string here"+ @[User:trANTTextFileName]" part of file name string.
(DT_STR,30,1252) @[User:taging_Date_Key] Contain the date of processing ,value in this variable is also picked up at run time from parent package variable.
1) So can someone give me some insight into possible reason of failures.
2) Is it possible that problem arises if directory (from which I m picking text files) is assigned password or is there exist some problem in accessing forlders over network ?
3) Or there can be some problem in package configuration at design time( I.e where I m assigning value in variable from parent package vriables)?
'System.Data.DataRowView' Does Not Contain A Property With The Name 'xy'
Mar 11, 2007my environment: VS2005, SQLExpress with SP1, WXP with SPs,
the error msg I get is: 'System.Data.DataRowView' does not contain a property with the name 'change'.
The base for all this is the 'MS Club Site' sample code downloaded from ASP.NET!
After adding a new data field "change" to my DB I changed the StoredProcedure "PagedUpcommingEventList" (statement 31) and in the 'Repeater' I added statement 32 to reflect this change (see code below). When calling 'Configure Data Source' on the SQLDataSource I can read the DB (using the StoredProcedure) but I never see the new field 'change'! I certainly checked for additional StoredProcedures with the same name: "PagedUpcommingEventList" --- I definitely have only one in my project.PS: In another .aspx file using a SQLDataSource with a DataGrid; the field is showing up correctly!
I spent days in the area of 'caching' without any success; thought the StoredProcedure is always called from the 'cache'! .... Thank you for your time .... any idea is appreciated!
ed 1 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ClubSiteDB %>"
2 SelectCommand="PagedUpcommingEventList" SelectCommandType="StoredProcedure" OnSelected="SqlDataSource1_Selected" CacheDuration="1">
3 <SelectParameters>
4 <asp:Parameter Name="pageCount" Direction="ReturnValue" Type="Int32" />
5 <asp:ControlParameter Name="pageNum" ControlID="pn1" PropertyName="SelectedPage" />
6 <asp:Parameter DefaultValue="5" Name="pageSize" Type="Int32" />
7 </SelectParameters>
8 </asp:SqlDataSource> 1 <asp:Repeater ID="Repeater1" runat="server" DataSourceID="SqlDataSource1">
2 <ItemTemplate>
3 <div class="listitem">
4 <%-- class="thumbnail">
5 <a href='<%# "Events_view.aspx?Eventid=" &Cstr( Eval("ID"))%>'>
6 <Club:ImageThumbnail ID="ImageThumbnail1" runat="server" PhotoID='<%# Eval("photo") %>'
7 NoPhotoImg="images/calendar.jpg" />
8 </a>
9 </div>--%>
10 <asp:panel ID=panel2 runat=server cssclass="editbuttons" Visible='<%#Isadmin %>'>
11 <Club:RolloverLink ID="EditBtn" runat="server" Text="Edit" NavigateURL='<%# "Events_Edit.aspx?Action=Edit&id=" & cstr(Eval("id")) %>' />
12 <Club:RolloverLink ID="RemoveBtn" runat="server" Text="Remove" NavigateURL='<%# "Events_Edit.aspx?Action=Remove&id=" & cstr(Eval("id")) %>' />
13 </asp:panel>
14 <%-->
15 <asp:Label ID="itemdateLabel" runat="server" Text='<%# Eval("starttime","{0:d}") %>' />
16 <a href='<%# "Events_view.aspx?Eventid=" &Cstr( Eval("ID"))%>'>
17 <asp:Label ID="titleLabel" runat="server" Text='<%# Eval("title") %>' />
18 </a>
19 </h3>
20 <p>
21 <asp:Label ID="descriptionLabel" runat="server" Text='<%# truncate(CStr(Eval("description"))) %>' />
22 <a href='<%# "Events_view.aspx?Eventid=" &Cstr( Eval("ID"))%>'>read more »</a></p>
23 <div class="clearlist">
24 </div>--%>
25 <h3>
26 <asp:Label ID="Label1" runat="server" Text='<%# Eval("starttime","{0:d}") %>' />
27 <a href='<%# "Events_view.aspx?Eventid=" &Cstr( Eval("ID"))%>'>
28 <asp:Label ID="Label2" runat="server" Text='<%# Eval("title") %>' />
29 </a>
30 </h3>
31 <asp:Label ID="Label3" runat="server" Text='<%# truncate(CStr(Eval("description"))) %>' />
32 <asp:Label ID="changeLabel" runat="server" Text='<%# Eval("change") %>' style="position:relative ; LEFT:300px" Font-Names="Arial" Font-Size="Medium"/>
33 <div class="clearlist"></div>
34 <p></p>
35 </div>
36 </ItemTemplate>
37 </asp:Repeater> 1 CREATE PROCEDURE PagedUpcommingEventList
2 (
3 @pageNum int = 1,
4 @pageSize int = 10
5 )
6
7 AS
8 declare @rows int
9 declare @keydate datetime
10 declare @keyid int
11 declare @rowCount float /* yes we need a float for the math */
12
13 if @pageNum = 1
14 begin
15 set @keydate= GETDATE()
16 set @keyid=0
17 end
18 else
19 BEGIN
20 /* get the values for the date and row */
21 select @rows = (@pageNum-1) * @pageSize
22 SET ROWCOUNT @rows
23 select @keydate=starttime, @keyid=id from Events
24 WHERE Events.starttime > GetDATE()
25 ORDER BY starttime asc, id asc
26 END
27
28 select @rowCount=count(*) from Events WHERE Events.starttime > GetDATE()
29
30 SET ROWCOUNT @pageSize
31 SELECT Events.id, Events.starttime, Events.title, Events.description, Events.photo, Events.change,
32 Locations.title AS locationname
33 FROM Events LEFT OUTER JOIN Locations ON Events.location = Locations.id
34 WHERE (Events.starttime > @keydate OR
35 (Events.starttime = @keydate) AND (events.id > @keyid))
36 ORDER BY Events.starttime asc, Events.id asc
37 RETURN CEILING(@rowCount/@pageSize)
38 GO
DataBinding: 'System.Data.DataRowView' Does Not Contain A Property With The Name
Feb 16, 2007Here's a new one I came across that stumped me for a bit.
DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'xxxx'.
It appears that one of my dropdown menus was referencing the old SqlDataSource1for its data source id.
I had already changed the name of the of the id to something else though.
To fix it, I just updated the reference to the new name.
Thought I'd share, just in case
'System.Data.DataRowView' Does Not Contain A Property With The Name 'TOID'.
Jun 12, 2007myCODE ; public void FullnameOfOwner()
{
GATEWAY.DSMESAJTableAdapters.MESAJMERKEZI_GELEN_MESAJLAR_EMLAKCIADITableAdapter adapEmlakciAdi =
new GATEWAY.DSMESAJTableAdapters.MESAJMERKEZI_GELEN_MESAJLAR_EMLAKCIADITableAdapter();GATEWAY.DSMESAJ.MESAJMERKEZI_GELEN_MESAJLAR_EMLAKCIADIDataTable dtGelenMesajlar = new GATEWAY.DSMESAJ.MESAJMERKEZI_GELEN_MESAJLAR_EMLAKCIADIDataTable();
adapEmlakciAdi.Fill(dtGelenMesajlar, OwnerID);
grdGelen.DataSource = dtGelenMesajlar;
grdGelen.DataBind();
}
public void MessagesfromOWNER()
{GATEWAY.DSMESAJTableAdapters.MESAJMERKEZI_GELEN_MESAJLARTableAdapter adapGelenMesajlar =
new GATEWAY.DSMESAJTableAdapters.MESAJMERKEZI_GELEN_MESAJLARTableAdapter();GATEWAY.DSMESAJ.MESAJMERKEZI_GELEN_MESAJLARDataTable dtGelenMesajlar =
new GATEWAY.DSMESAJ.MESAJMERKEZI_GELEN_MESAJLARDataTable();adapGelenMesajlar.Fill(dtGelenMesajlar, (int)OwnerID,(int)UserID,"EMLAKÇI");
grdGelen.DataSource = dtGelenMesajlar;
grdGelen.DataBind();
}
protected void grdGelen_PreRender(object sender, EventArgs e)
{
MessagesfromOWNER();
FullnameOfOwner();
}
MyDatabaseQuery
SELECT _NAME, FROMID, TOID, _DESCRIPTION, _CATEGORY, _OWNERID, _LASTMODIFYDATE, _ID, FROMTYPEFROM MESSAGEWHERE (TOID = @TOID) AND (FROMID = @FROMID) AND (FROMTYPE = @EMLAKCI) for Message
SELECT FIRSTNAME + ' ' + LASTNAME AS FULLNAME, USERIDFROM [USER]WHERE (USERID = @USERID) for fullname
so
i have there columns so i write grdGelen columns[0] >_NAME , columns[1] > LASTMODIFYDATE Columns[2] > FULLNAME AND DATAKEYNAMES TOID,FROMID,FROMTYPE is of MY GRİDVİEW
i have a problem on dataset. i create two tableadapters and datatables because i must take from different datase fullname so it is showing this mistake "'System.Data.DataRowView' does not contain a property with the name 'TOID'."
what can i do ?
Data Access Error - ConnectionString Property
Mar 29, 2006Hi guys,
I'm getting this error:System.InvalidOperationException: The ConnectionString property has not been initialized
when I'm trying to connect to an SQL database using the following code:
Dim mySelectQuery As String = "SELECT [tabl_id] FROM [myTable]"Dim myConnection As New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("myConnString"))Dim myCommand As New SqlCommand(mySelectQuery, myConnection)myConnection.Open()Dim myReader As SqlDataReadermyReader = myCommand.ExecuteReader()While myReader.Read() Response.Write(myReader("tabl_id") & "<br/>")End WhilemyConnection.Close()
and in web.config:
<appSettings/> <connectionStrings> <add name="myConnString" connectionString="Data Source=100.100.100.100;Initial Catalog=myDatabase;User ID=me;Password=password" providerName="System.Data.SqlClient"/> </connectionStrings>
If I place the actual connection string inside instead of trying to reference the web.config setting it works, but using 'System.Configuration.ConfigurationManager.AppSettings' returns the error. Is there something I'm doing wrong?
DTS 'Transform Data Task Property' GUI Window
Apr 15, 2005I am importing data from xls file to a db table with a dts. In the time of the dts creation I am using 'Transform Data Task Properties' GUI window to map incoming xls fields (source) to the table columns (destination).
Question: Is there any way to invoke the 'Transform Data Task Property' GUI window in dts runtime and use it to change the mapping dynamically in the run time?
Thanks, Vadim.
DataBinding: 'System.Data.DataRowView' Does Not Contain A Property With The Name 'RMID'.
Aug 11, 2006I m trying to UPDATE database using FormView and SqlDataSource and here is my code:
<asp:SqlDataSource ID="sqlDS1" runat="server" ConnectionString="<%$ ConnectionStrings:myDB %>"UpdateCommand="UPDATE [Consultants] SET [firstName]=@FIRSTNAME,[lastName]=@LASTNAME,[skillCategoryID]=@SKILLCATID,[resourceManagerID]=@RMID,[AMgroupID]=@AMGROUPID,[SkillSet]=@SKILLSET,[statusID]=@STATUSID,[location]=@LOCATION,[comments]=@COMMENTS,[profile]=@PROFILE,[isAvailable]=@ISAVAILABLE,[dateModified]=getdate(),[focusID]=1 WHERE ([id]=@ID)">
<UpdateParameters>
<asp:FormParameter FormField="txtFName" Name="FIRSTNAME" />
<asp:FormParameter FormField="txtLName" Name="LASTNAME" />
<asp:FormParameter FormField="ddlSkillCat" Name="SKILLCATID" />
<asp:FormParameter FormField="ddlRM" Name="RMID" />
<asp:FormParameter FormField="ddlAMGroup" Name="AMGROUPID" />
<asp:FormParameter FormField="txtSkillset" Name="SKILLSET" />
<asp:FormParameter FormField="ddlStatus" Name="STATUSID" Type="Int16" />
<asp:FormParameter FormField="txtLocation" Name="LOCATION" />
<asp:FormParameter FormField="txtComments" Name="COMMENTS"/>
<asp:FormParameter FormField="txtProfile" Name="PROFILE" />
<asp:FormParameter FormField="cbAvailable" Name="ISAVAILABLE" />
<asp:QueryStringParameter Type="Int32"
Name="ID"
QueryStringField="id" />
</UpdateParameters>
</asp:SqlDataSource>
//*******************************************************************
<asp:FormView DefaultMode="Edit" ID="FormView1" runat="server" DataSourceID="sqlDS1" DataKeyNames="id">
<EditItemTemplate>
<tr>
<td class="blacktextbold">
Resource Manager:
</td>
<td class="blacktext">
<asp:DropDownList ID="ddlRM" CssClass="blacktext" runat="server" DataSource="<%#GetRM()%>" DataTextField="RMName" DataValueField="userID" SelectedValue="<%#Bind('RMID')%>" AppendDataBoundItems="true">
<asp:ListItem Value="-1">--Select RM--</asp:ListItem>
</asp:DropDownList>
<asp:CustomValidator ID="cvRM" runat="server" ValidationGroup="gpInsert" ControlToValidate="ddlRM" ClientValidationFunction="validateDropdown" OnServerValidate="servervalidateDropdown" Display="Dynamic" ErrorMessage="Required Field" CssClass="redtextsmallbold" />
</td>
</tr>
//********************************************************
here is my GetRM() function:
protected DataSet GetRM()
{
string strConnection = ConfigurationManager.ConnectionStrings["myDB"].ToString();
SqlConnection objConnection = new SqlConnection(strConnection);
String sqlSkillCats = "SELECT Roles.roleID, Roles.roleName, UsersInRoles.userID, UsersInRoles.roleID AS Expr1, Users.firstName + ' ' + Users.lastName AS RMName, Users.id AS Expr2" +
" FROM Users INNER JOIN" +
" UsersInRoles ON Users.id = UsersInRoles.userID CROSS JOIN" +
" Roles" +
" WHERE (Roles.roleName = 'accountmanager') AND (UsersInRoles.roleID = Roles.roleID) ORDER BY Users.firstName";
SqlDataAdapter objAdapter = new SqlDataAdapter(sqlSkillCats, objConnection);
objConnection.Open();
//ddlDataSet = new DataSet();
try
{
objAdapter.Fill(dsConsultantRM, "ConsultantRM");
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
objConnection.Close();
return dsConsultantRM;
}//******************************************************************
i get this error when i load the page:DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'RMID'.
basically i m trying to UPDATE database using the value selected in the DropDownList, can anyone tell me whats wrong here...PLZ HELP!
DataBinding: 'System.Data.DataRowView' Does Not Contain A Property With The Name 'file_ID'.
Sep 16, 2006HAI guys,i am suneel.i have a problem..please help me..i am binding my datalist.i have set my dtakey field to the primary key of teh database.i am not displaying the primary key(file_id).but i am displaying the other fields in that table.i have set databinder.Eval(container,dataitem."file_name").i have put one delete button in the item template...if i want to build now...i am getting an error.....DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'file_ID'.wat i have to do..please get me the reply..
View 2 Replies View RelatedDataBinding: 'System.Data.DataRowView' Does Not Contain A Property With The Name 'Component_Profile_ID'
Mar 22, 2007Hi,Seems like a lot of people are having a similiar problem that I am having right now, but I am not able to find the solution to it. On the Page_load event, the gridview does display the data from database. When I click a button to insert the same data but different Waste_Profile_Num value, it gives me the databinding error. Component_Profile_ID is declared as an Identity and it is the primary key to the table. Anyway help???Ok, the following are code that I have: 1 <asp:SqlDataSource ID="sqlDSMaterialComposition" runat="server" ConnectionString="<%$ ConnectionStrings:HAZConnectionString %>"
2 SelectCommand="SELECT dbo.Component_Profile.Component_Profile_ID AS Component_Profile_ID, dbo.Component.Component, dbo.Component_Profile.Concentration, dbo.Component_Profile.Range, dbo.Component_Profile.Waste_Profile_Num, dbo.Component.Component_ID FROM dbo.Component INNER JOIN dbo.Component_Profile ON dbo.Component.Component_ID = dbo.Component_Profile.Component_ID WHERE (dbo.Component_Profile.Waste_Profile_Num = @Waste_Profile_Num)">
3 <SelectParameters>
4 <asp:SessionParameter Name="Waste_Profile_Num" SessionField="Waste_Profile_Num" Type="Int32" />
5 </SelectParameters>
6 </asp:SqlDataSource>
7
8
9 <asp:GridView ID="GridViewMaterialComposition"
10 runat="server"
11 DataKeyNames="Component_Profile_ID,Component_ID"
12 AutoGenerateColumns="False" ShowFooter="true">
13 <Columns>
14 <asp:BoundField DataField="Component" HeaderText="Component" SortExpression="Component" FooterText="Total"/>
15 <asp:TemplateField HeaderText="Concentration" FooterStyle-Font-Bold="true">
16 <ItemTemplate>
17 <%# SumConcentration(decimal.Parse(Eval("Concentration").ToString())).tostring("N2") %>
18 </ItemTemplate>
19 <FooterTemplate>
20 <%# GetConcentration().tostring("N2") %>
21 </FooterTemplate>
22 </asp:TemplateField>
23 <asp:BoundField DataField="Range" HeaderText="Range" SortExpression="Range" />
24 <asp:BoundField DataField="Component_Profile_ID" HeaderText="Component_Profile_ID" ReadOnly="True" SortExpression="Component_Profile_ID" Visible="true" />
25 <asp:BoundField DataField="Component_ID" HeaderText="Component_ID" ReadOnly="True" SortExpression="Component_ID" Visible="true"/>
26 <asp:BoundField DataField="Waste_Profile_Num" HeaderText="Waste_Profile_Num" ReadOnly="true" SortExpression="Waste_Profile_Num" Visible="true" />
27 <asp:CommandField ShowEditButton="true" />
28 <asp:TemplateField Visible="false">
29 <ItemTemplate>
30 <asp:Label ID="lblComponentProfileID" runat="Server" Text='<% # Eval("Component_Profile_ID") %>'></asp:Label>
31 <asp:Label ID="lblComponentID" runat="Server" Text='<% # Eval("Component_ID") %>'></asp:Label>
32 </ItemTemplate>
33 </asp:TemplateField>
34 </Columns>
35 </asp:GridView>The following are the code-behind: 1 sqlSelect = "SELECT Range, Concentration, Component_ID FROM Component_Profile WHERE (Component_Profile.Waste_Profile_Num = " & previousWasteProfileNum & ")"
2 sqlDSMaterialComposition.SelectCommand = sqlSelect
3 Try
4 Dim dvComposition As Data.DataView = CType(sqlDSMaterialComposition.Select(DataSourceSelectArguments.Empty), Data.DataView)
5 For Each dr As Data.DataRow In dvComposition.Table.Rows
6 insertSql = "INSERT INTO Component_Profile ([Range], [Concentration], [Component_ID], [Waste_Profile_Num]) "
7 insertSql &= "VALUES (" & dr("Range").ToString.Trim & ", " & dr("Concentration").ToString.Trim & ", " & dr("Component_ID").ToString.Trim & ", " & CInt(Session("Waste_Profile_Num").ToString()) & ")"
8 sqlDSMaterialComposition.InsertCommand = insertSql
9 sqlDSMaterialComposition.Insert()
10 Next
11 Catch ex As Exception
12
13 End Try
A Field Or Property With The Name 'Jan' Was Not Found On The Selected Data Source.
Jul 31, 2007I have this Stored Procedure:
Create PROCEDURE ListEventas If MONTH(GetDate()) <= 6 Begin SELECT EventTitle, EventDuration, (CASE WHEN MONTH(StartDate) = 1 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Jan', (CASE WHEN MONTH(StartDate) = 2 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Feb', (CASE WHEN MONTH(StartDate) = 3 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Mar', (CASE WHEN MONTH(StartDate) = 4 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Apr', (CASE WHEN MONTH(StartDate) = 5 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'May', (CASE WHEN MONTH(StartDate) = 6 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Jun'FROM dbo.tblEvent INNER JOIN dbo.tbl ON (tblEvent.EventID = tblEventdate.EventID)WHERE YEAR(StartDate) = Year(GetDate())group by EventTitle, EventDuration,StartDate,EndDateEnd Else BeginSELECT EventTitle, EventDuration,(CASE WHEN MONTH(StartDate) = 7 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Jul', (CASE WHEN MONTH(StartDate) = 8 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Aug', (CASE WHEN MONTH(StartDate) = 9 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Sep', (CASE WHEN MONTH(StartDate) = 10 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Oct', (CASE WHEN MONTH(StartDate) = 11 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Nov', (CASE WHEN MONTH(StartDate) = 12 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Dec'FROM dbo.tblEvent INNER JOIN dbo.tbl ON (tblEvent.EventID = tblEventdate.EventID)WHERE YEAR(StartDate) = Year(GetDate())group by EventTitle, EventDuration,StartDate,EndDateEnd
When I execute it in the SQLExpress, the result returned as expected. But when I bind to Gridview I got this error:
A field or property with the name 'Jan' was not found on the selected data source.
How do I solve this?
I am using drag and drop SQLDatasource to call the Stored Proc and VB.net is the language. Thanks
How Do I Modify Union Data Flow Task Property?
Apr 4, 2008I've created a Union All data flow task which unions string fields from a couple of queries. I found that I needed to increase the length of the string field in the queries. Increasing the length causes an error in my Union All task. I can't seem to find a way to change the property of the fields in the Union All task. Most of the other data flow tasks have an advanced editor where I can change the length or type of the fields on the input and or output. I can't seem to find this for the Union All task.
Is there any way short of deleting the Output column and recreating it? (I like the order of my original output columns)
Thanks,
Bill Webster
Using Disable Property For Data Flow Task Dynamically ?
Mar 18, 2008hello guys,
I am having trouble with using disable property in the expression for data flow task. Here is the issue as explained below-
lets say i have 3 tables TableA, TableB, TableC from which i need to export data. So i create a table (TableList) where I save these table names and a unique id to these tables. e.g.
TableList will have-
TableName TableID
TableA 1
TableB 2
TableC 3
in the ssis package select these tableNames & Ids from tableList in Execute SQL Task. And assign the result set to a variable object (@TableList.
Then i use For Each Loop Container (For Each ADO Enumerator) , to loop through these tablesnames & iDs
Inside this loop container, i define three data flow tasks one for each table. So i have DataFlowTaskA (For TableA), DataFlowTaskB(For TableB), DataFlowTaskC (For TableC).
Now for a given table selected in the iteration, only the corresponding DataFlow Task should be exeuted. e.g. For the 1st iteration, if TableA is selected then only DataFlowTaskA should be executed and DataFlowTaskB& C should be skipped.
In order to achieve this, I am using a 3 variable @FlagA, @FlagB, @FlagC (type Boolean) one for each Table. and use the value of these flags for the "Disable" property of the data flow task (so @FlagA will be used for Disable property in the Expression for Data FlowTaskA, and so on..)
SotThe First Step inside the Loop, I use Script Task. (Input for the script task: read variable is @TableID and Read/Write varaibles are these 3 flags)
In this script task, I initialize these flags to true or false appropriately. So this is what i do
If (Dts.Variables("TableID").Value.ToString = "1") Then
Dts.Variables("@FlagA").Value = False
Else
Dts.Variables("@FlagA").Value = True
End If
If (Dts.Variables("TableID").Value.ToString = "2") Then
Dts.Variables("@FlagB").Value = False
Else
Dts.Variables("@FlagB").Value = True
End If
So in the 1st iteration, (if TableA comes) @FlagA=False and B&C will be True.
So the Disable property for DataFlowTask will be set false and for others it will be set to True. Thus, only DataFlowTaskA will be executed.
And this action should be repeated for each input table. this is the logic.
However only for the 1st iteration(say TableA is selected) it behaves as above. i.e. DataFlowTaskA is executed and DataFlowTaskB & C are skipped. But in the 2nd iteration(say TableB is selected) , it again executes DataFlowTaskA and doesnt exeute B & C (where it should have executed B & skipped A&C).
I do set daelay validation to true for all these but it still it doesnt working as expected. Even I checked the values for all the flags for each iteration and they seem to get the correct values. But somehow Diable propery in the expression not behaving as it should.
Am i missing anything. Do i need to set any other property to make this work.
I apprecite any help.
Thanks
Engine Thread Property Of Data Flow Task
Apr 3, 2006Hi guys,
The default Engine Thread property of a data flow task is set to 5, is this the best setting? what if I would like to run complex data flow tasks on multi-processor machines, should I increase the engine thread? If so, then what is the recommended Engine Thread number for running complex data flow tasks in a multi processor system?
Even if i am running simple data flow tasks on a multi processor machine, should I change the engine thread?
Thanks!
Kervy
Data Warehousing :: Hiding Text Box Based On Table Property
Oct 16, 2015I am putting together an invoice for my company. I have a text box describing each section of the invoice, followed by a table to list out the charges. I am using multiple tables based on what type of charge the client is receiving.
I would like to hide each section if there are no items purchased of that type. I can do this with the table using the expression "=CountRows() < 1", but I do not know how to refer to that table (call it Tablix1 for the sake of discussion) for the text box. I've tried using a ReportItems function as my basis, without success.
Export To Excel, CSV, XML With Expression In Hidden Property Omits Data
Jun 7, 2007I have a matrix table with a rectangle in the data cell. The rectangle has an image and textbox. The textbox has an expression in it's Hidden property based on the column name. The report renders fine on screen. When the report is exported to Excel, CSV, XML the textbox contents are not output (the images display as expected). I've tried setting the DataElementOutput to Output/Yes with no success. Exporting to TIFF, PDF, Web Archive/MTHML is fine.
Here is a sample RDL which exhibits the issue:
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="Gemini50DataSource">
<DataSourceReference>Gemini50DataSource</DataSourceReference>
<rdataSourceID>bb03313c-48a4-4e40-af99-ed584847ca20</rdataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<ReportParameters>
<ReportParameter Name="ImagePath">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>c:</Value>
</Values>
</DefaultValue>
<Prompt>Image Path</Prompt>
</ReportParameter>
</ReportParameters>
<rdrawGrid>true</rdrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rdnapToGrid>true</rdnapToGrid>
<Body>
<ReportItems>
<Matrix Name="matrix1">
<MatrixColumns>
<MatrixColumn>
<Width>3.75in</Width>
</MatrixColumn>
</MatrixColumns>
<RowGroupings>
<RowGrouping>
<Width>1.75in</Width>
<DynamicRows>
<ReportItems>
<Textbox Name="PicIndex">
<rdefaultName>PicIndex</rdefaultName>
<ZIndex>1</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!PicIndex.Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_PicIndex">
<GroupExpressions>
<GroupExpression>=Fields!PicIndex.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicRows>
</RowGrouping>
</RowGroupings>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<ReportItems>
<Textbox Name="ColumnName">
<rdefaultName>ColumnName</rdefaultName>
<ZIndex>2</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BackgroundColor>LightBlue</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!ColumnName.Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_ColumnName">
<GroupExpressions>
<GroupExpression>=Fields!ColumnName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicColumns>
<Height>0.25in</Height>
</ColumnGrouping>
</ColumnGroupings>
<DataSetName>DataSet2</DataSetName>
<Width>5.5in</Width>
<Corner>
<ReportItems>
<Textbox Name="textbox1">
<rdefaultName>textbox1</rdefaultName>
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</Corner>
<Height>0.55208in</Height>
<MatrixRows>
<MatrixRow>
<Height>0.30208in</Height>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Rectangle Name="rectangle1">
<ReportItems>
<Image Name="image1">
<Sizing>AutoSize</Sizing>
<Left>0.25in</Left>
<MIMEType />
<ZIndex>1</ZIndex>
<Visibility>
<Hidden>=IIF(First(Fields!ColumnName.Value = "image"), Len(Fields!CellValue.Value)=0, true)</Hidden>
</Visibility>
<Width>0.3in</Width>
<Source>External</Source>
<Style />
<Value>="file:" + Parameters!ImagePath.Value + Fields!CellValue.Value</Value>
</Image>
<Textbox Name="textbox2">
<Left>1.625in</Left>
<DataElementOutput>Output</DataElementOutput>
<rdefaultName>textbox2</rdefaultName>
<Visibility>
<Hidden>=IIF(Fields!ColumnName.Value <> "image", False, True)</Hidden>
</Visibility>
<Width>1.875in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BackgroundColor>PeachPuff</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>=Fields!CellValue.Value</Value>
</Textbox>
</ReportItems>
<Visibility>
<Hidden>=IIF(True, False, True)</Hidden>
</Visibility>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<BackgroundColor>LightGrey</BackgroundColor>
</Style>
</Rectangle>
</ReportItems>
</MatrixCell>
</MatrixCells>
</MatrixRow>
</MatrixRows>
</Matrix>
</ReportItems>
<Height>0.625in</Height>
</Body>
<rd:ReportID>a944d20c-558a-4805-9d4c-aecc9757f678</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet2">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>SELECT 1 as PicIndex, 'image' as ColumnName, 'image1.jpg' as CellValue
union
SELECT 2,'image','image2.jpg'
union
SELECT 3,'image','image3.jpg'
union
SELECT 4,'image',null
union
SELECT 5,'something else',null
union
SELECT 6,'another column', 'display my text!'</CommandText>
<DataSourceName>Gemini50DataSource</DataSourceName>
</Query>
<Fields>
<Field Name="PicIndex">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>PicIndex</DataField>
</Field>
<Field Name="ColumnName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ColumnName</DataField>
</Field>
<Field Name="CellValue">
<rd:TypeName>System.String</rd:TypeName>
<DataField>CellValue</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>5.625in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>
Referencing One Item's Hidden Property In Order To Set Another's Hidden Property
Feb 15, 2007Hello,
I have a group I'll call G4.
The header table row for G4 contains 3 textboxes containing the sums of the contents within G4. The header table row for G4 is visible while it's contents, including the G4 footer table row, is kept invisible until the report user drills down into the group.
When the report user drills down into G4 the footer table row becomes visible and the sums of the contents of the group are displayed for a second time.
At this point I want the sums in the header to be set to invisible when the sums in the footer are made visible by the drilldown.
When I try to reference the hidden property of textbox66 in the G4 footer in order to set the hidden property of header textbox57 in the G4 header I get to this point...
=IIF(reportitems!textbox66.
When it fails to give me an option of choosing the .Hidden property and instead only gives me a .Value.
If I complete the IIF statement manually so that it spells out .....
=IIF(ReportItems!Textbox66.Hidden = False, True, False)
...the report chokes on it.
So my question is, how do I reference the hidden property of one or more textboxes in a group to use as condition checks to set the hidden property of another textbox in that same group?
Thank you for any help you can provide. We are only now beginning to implement reporting services and I have not yet had the chance to research this in greater detail for lack of time.
Delete Rows With Duplicate Column Data But Unique Row Data
May 25, 2000Hello,
This probably has been addressed before but I was unable to get the search to work properly on this site.
I am needing a script/way of deleting all rows from a DB with the exception of one record left for each row that has duplicate column data. Example :
Row 1
Field1 = 12345 Field2 =xxxxx Field 3=yyyyy Field4=zzzzz etc.
Row 2
Field1 = 12345 Field2 =zzzzzz Field 3=xxxxxx Field4=yyyyyy etc.
Row3
Field1 = 12345 Field2 =20202 Field 3=11111 Field4=zzzzz etc.
Row 4
Field1 = 54321 Field2 =xxxxx Field 3=yyyyy Field4=zzzzz etc.
Etc. Etc.
I want to be able to find the duplicates for Field1 and then delete all but 1 of those rows.( I don't care which one I keep just so only one is left.) The data in the other fields may or may not be unique.
I know how to find the duplicates it's just the deleting part I am having problems with. Any help would be much appreciated. Thanks,
Kerry
Article
Jan 9, 2004Tech firms defend moving U.S. jobs overseas (http://www.msnbc.msn.com/default.aspx?id=3899043&p1=0)
Any thoughts or comments?
MS Kb Article 825019 And Beyond
Aug 8, 2006I am experiencing the behaviour described in this kb article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;825019
but I am reliably informed that both the sql 2000 boxes which are linked have been patched to sp4.
Has anyone had any experience of this issue recurring post patching?
How To Add New Article To Publication?
Dec 23, 2004Hi,
I'm use MSSQL2000 SP3 with replication.
I want to know that Can i add new article to existing publication by
Enterprice Manager ?
someone told me that we can do that in older version but not now,right? :confused:
How To Ask A Question...(MS KB Article)
Feb 2, 2006MS has post a KB article on how to post a question on a online forum...
http://support.microsoft.com/kb/q555375
At first I though this was hilarious, but there is actually some good information in here that is applicable on this forum as well as anywhere else. Perhaps Brett could add this as a sticky?
Regards,
hmscott
Add New Article To The Publisher
Jun 19, 2008Dear All,
i'm in the transactional replication environment. we need to add one new table to the publisher. it is sql server 2005 environment. please explain me the steps
and in another table, i need to change the data type of a table.
please guide me
Arnav
Even you learn 1%, Learn it with 100% confidence.
Article About Replication
Oct 31, 2007hi
i'm new to replication. i'm looking for a full article that explains Replication good, can u take a link to that article ?
thanks
Sp_dropsubscription, Article
Sep 26, 2005Hi There
View 13 Replies View RelatedUnpublishing An Article
Mar 8, 2006Hi:
If I want to unpublish an article thru Enterprise Manager, it does not allow me to uncheck it at publication properties. It only allows me to uncheck articles if I delete the subscription first. Does anybody know how tp do this in Enterprise Manager without deleting the subscription?
Thanks
Same Article In Different Publications
Sep 23, 2005Hi!
View 1 Replies View RelatedAdd Article To Transactional Replication?
Oct 14, 2014Here's what my script boils down to when adding a new view (admin.replication_test):
Code:
EXEC sp_addarticle
@publication = N'<publication name>'
, @article = N'replication_test'
, @source_owner = N'admin'
, @source_object = N'replication_test'
, @type = N'view schema only'
;
If I check the GUI, the article appears to be ticked in the publication.
Generate a new snapshot.
View not found in replicated database!
Article On Advantages Of Sql Server Over VFP
Oct 9, 2007hi guys,
im new to sql server...
i am currently studying sql server 2000
i know this subject has been asked before...
i would just like to ask if you know some related articles regarding advantages of using sqlserver over vfp dbf/dbc?
i am going to discuss it to my class.
thanks very much!
any help is very much appreciated!
Joel