Modify Component Property

Apr 9, 2008

i need some help modifying a custom control flow task...

I am currently working on an export solution, and since some of the consumers of the export files can not handle unicode, i need to change the Codepage of the output based on config file parameters.

I downloaded a custom dataflow component published by MS (please find the link below - 1). It works fine for changing codepages, but it cannot be configured via expressions.
So i decided to modify the component the way MS describes it (link below again - 2):
Thus, added one row to the method "ProvideComponentProperties()":

customProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;

But this changes nothing, i am still not able to use an expression for setting this property. I could need some help on this...

Thanks a lot,

Regards
Jan


Links:1)SQL Server SSIS Sample Component: CodePageConvert:https://www.microsoft.com/downloads/details.aspx?familyid=9e56417e-23d1-4fd3-8d6d-61314faa2de3&displaylang=en2) Design-time Methods of a Data Flow Component:http://technet.microsoft.com/en-us/library/ms135969.aspxComplete Method:

public override void ProvideComponentProperties()
{
base.ProvideComponentProperties();

IDTSCustomPropertyCollection90 customProperties =

this.ComponentMetaData.CustomPropertyCollection;

IDTSCustomProperty90 customProperty = customProperties.New();
customProperty.Name = "CodePage"; // do not localize
customProperty.Value = 850;
customProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY; //My Code


}

View 1 Replies


ADVERTISEMENT

How Do I Modify Union Data Flow Task Property?

Apr 4, 2008

I'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

View 6 Replies View Related

Immediate Validation On Component Property - Is This Possible

May 29, 2008

Can you fire a warning / error validation immediately after a value is entered (user clicks away / clicks different property) in a component property without having to press ok button or switch tabs.

Have tried to call validate in setcomponentproperty but only takes effect when press ok button / switch tabs.

Thanks

View 1 Replies View Related

Script Component - DataType Property Inaccessible

Dec 19, 2007



Hi,

I have a trouble with script component. From time to time, I cannot see available data types of the column I´m just editing, in the Script component editor. The cell in the section Data Type properties, where normally combobox with data types is, is completelly black. This happens both with "old" script components I'm only viewing and new script component I'm creating.

This problem occured for the first time immediately after I migrated from Windows XP to Vista Bussiness 64bit and then several times without any obvious similarity among terms under which this appeared.

I have SP2 for SQL Server and SP1 for VisualStudio 2005 Professional edition installed.

Please, can you help me?

Thanks

Jana

View 11 Replies View Related

Validation Error On Custom Component Property

May 28, 2008

I have set a validation error when changing a property in the advanced editor. Unfortunately this validation error is only displayed when you change tabs or press the ok button in the editor.

I have tried calling the validate method within the setcomponentproperty method but the error only show up when ok button is pressed or tab in editor.

I don't want to throw an exception, is there any way you can show a validation error immediately once the user has clicked away / entered / clicked to another property field after entering a property value in the advanced editor that is incorrect.

Thanks

View 1 Replies View Related

My Own Dataflow Component. How Make A Property With List Of Predefined Values?

Mar 24, 2007

I am creating of may of dataflow component.

How make a property with list of predefined values?

Thanks in advance.

View 3 Replies View Related

Expression Issue With Custom Data Flow Component And Custom Property

Apr 2, 2007

Hi,



I'm trying to enable Expression for a custom property in my custom data flow component.

Here is the code I wrote to declare the custom property:



public override void ProvideComponentProperties()

{


ComponentMetaData.RuntimeConnectionCollection.RemoveAll();

RemoveAllInputsOutputsAndCustomProperties();



IDTSCustomProperty90 prop = ComponentMetaData.CustomPropertyCollection.New();

prop.Name = "MyProperty";

prop.Description = "My property description";

prop.Value = string.Empty;

prop.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;



...

}



In design mode, I can assign an expression to my custom property, but it get evaluated in design mode and not in runtime

Here is my expression (a file name based on a date contained in a user variable):



"DB" + (DT_WSTR, 4)YEAR( @[User::varCurrentDate] ) + RIGHT( "0" + (DT_WSTR, 2)MONTH( @[User::varCurrentDate] ), 2 ) + "\" + (DT_WSTR, 4)YEAR( @[User::varCurrentDate] ) + RIGHT( "0" + (DT_WSTR, 2)MONTH( @[User::varCurrentDate] ), 2 ) + ".VER"



@[User::varCurrentDate] is a DateTime variable and is assign to 0 at design time

So the expression is evaluated as: "DB189912189912.VER".



My package contains 2 data flow.

At runtime,

The first one is responsible to set a valid date in @[User::varCurrentDate] variable. (the date is 2007-01-15)

The second one contains my custom data flow component with my custom property that was set to an expression at design time



When my component get executed, my custom property value is still "DB189912189912.VER" and I expected "DB200701200701.VER"



Any idea ?



View 5 Replies View Related

Adding Custom Property To Custom Component

Aug 17, 2005

What I want to accomplish is that at design time the designer can enter a value for some custom property on my custom task and that this value is accessed at executing time.

View 10 Replies View Related

The Component Metadata For Component DataReader Source (1113) Could Not Be Upgraded To The Newer Version Of The Component.

Oct 26, 2007

Hello,

I have a package that has a data lfow task. this task imports data from a db2 database (using the IBM Ole DB provider fro db2) and adds it to sql server database table. This package was created on the server. then though version control (using TFS source control) I check out the package on my local machine. and when I open the package I get the foll 3 errors.

Error 1 Validation error. Import Account Num from BMGP_BDR: DTS.Pipeline: The component metadata for "component "DataReader Source" (1113)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.

Error 2 Error loading BMAG Download Xref Tables - bmag.dtsx: Microsoft.SqlServer.Dts.Pipeline.ComponentVersionMismatchException: The version of component "DataReader Source" (1113) is not compatible with this version of the DataFlow. [[The version or pipeline version or both for the specified component is higher than the current version. This package was probably created on a new version of DTS or the component than is installed on the current PC.]] at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostCheckAndPerformUpgrade(IDTSManagedComponentWrapper90 wrapper, Int32 lPipelineVersion)

Error 3 Error loading BMAG Download Xref Tables - bmag.dtsx: The component metadata for "component "DataReader Source" (1113)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.


Please advice.
Thank you.





View 7 Replies View Related

The Component Metadata For Component DataReader Source Could Not Be Upgraded To The Newer Version Of The Component.

Jan 23, 2007

Hi,

I have a package which reads an Access file from a folder. My connection manager to this file is .NET providers for OledbMicrosoft Jet 4.0 OLE DB Provider.

Package works from my computer. But when I execute it on the server as a SQL Agent job, I get







The component metadata for "component "DataReader Source" (1) could not be upgraded to the newer version of the component. The PerformUpgrade method failed.  

I copied the mdb file to a folder on the server which my packages have no problem reading data from.

My packages run under the same domain account as defined in proxies.

Appreciate a help.

Gulden

 

 

View 4 Replies View Related

Changing Code Page Property Using Property Expression Doesn't Work

Jun 16, 2006

I 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.

View 5 Replies View Related

Value Of A Readonly Property Of Custom Task Is Not Updated In Property Window

Apr 17, 2008

Hi,

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.

View 3 Replies View Related

(URGENT) Cannot Be Written To The Property. The Expression Was Evaluated, But Cannot Be Set On The Property

May 7, 2008

Untill 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)?




View 10 Replies View Related

Use Of A SSIS Variable Of Type “Object� Inside Script Component And Task Component

Mar 16, 2007

In a Data Flow, I have the necessity to use a SSIS variable of type €œObject€? inside Script Component and assign to it the content of 'n' variables of string type.
On exiting from the script the variable of type object should contain something like in the following lines:
AAAAAAAAAAAAAAAAAAAAAAAAAAAAA
BBBBBBBBBBBBBBBBBBBBBBBBBBBBB
CCCCCCCCCCCCCCCCCCCCCCCCCCCCC
DDDDDDDDDDDDDDDDDDDDDDDDDDDDD
€¦€¦€¦€¦€¦€¦€¦.
€¦€¦€¦€¦€¦€¦€¦.
On exiting from the data flow I will use the variable of type Object in a Script Task, by reading each element in a cyclic fashion.
Is there anyone who have experienced something like this? Could anyone provide any example of that?
Thanks in advance!

View 3 Replies View Related

A Custom Component For Use As A VIEW In SSIS- Is It Possible To Create One MERGE Like Component With More Than 2 Inputs

Aug 13, 2007

Hi all
I'm into a project which uses a lot of views for joining 2 or more tables. Using the MERGE component in SSIS will be a huge effort coz it only has 2 inputs and I gotta SORT the input too.
Isnt it possible to have a VIEW like component that joins more than 2 tables and DOESNT need sorting??
(I've thought about creating views in database engine but it breaks my data floe in SSIS and is'nt a practical solution)

View 4 Replies View Related

Reference To Preceeding Component From Custom Dataflow Transformation Component

Mar 30, 2006

I am writing a custom dataflow transformation component and I need to get the name of the preceeding component.

I have been trying to find a way to get a reference to the Package object, MainPipe object or IDTSPath90 object (connecting to the IDTSInput90 of my component) from my component because I think from there I can get to the information I want.

Does anyone have any suggestions?

TIA . . . Ed

View 7 Replies View Related

Serious Script Component Bug - Clears Out All Code Inside Component

Nov 27, 2007



No idea where this bug crept in from. Have been using SSIS for 1.5 years now without hitting this problem.

I had a script component opening an XML document and parsing it using XPATH. I added some code that uses StreamReader / Streamwriter (closing one stream before starting the other). The code works without issue in my C# app.

And it ran without issue 2-3 times in SSIS. Then suddenly after running my package again, the script component says it completes successfully, yet nothing happens. I set a breakpoint on the first line of code - it never hits it. I add a msgbox as the first line of code - and it never displays.

I then close my package / exit out of ssis ... and then re-open it. When i open my script component, all of my code is GONE. All references that I added are gone.

I tried adding the streamreader/writer process to a dll I created from my c# app ... and added the DLL to the package -- same result.

I can reproduce this on 2 different computers.

Anyone experience this problem ? Any idea how to stop it ? Or debug it ?


Here is a slimmed down code sample of what causes the error :


Public Class ScriptMain
Public Sub Main()
Try
Dim xmlDoc As New XmlDocument
xmlDoc.Load("c:ulkasync_86281519_20070628045850225_4.xml")
MsgBox("xmlLoaded") --this doesn't display once the package starts "acting up"
Catch ex As Exception
MsgBox(ex.Message)
UpdateXML("c:ulkasync_86281519_20070628045850225_4.xml", ex.Message)
End Try
Dts.TaskResult = Dts.Results.Success
End Sub
Private Sub UpdateXML(ByVal fileName As String, ByVal message As String)
Try
Dim invalidChar As String = message.Trim().Substring(message.Trim().IndexOf("0x"), 4)
Dim rd As StreamReader = New StreamReader(fileName)
Dim xml As String = rd.ReadToEnd()
Xml = Xml.Replace(invalidChar, String.Empty)
xml = xml.Replace("", String.Empty)
xml = xml.Replace("<![CDATA[<![CDATA[", "<![CDATA[")
xml = xml.Replace("]]>]]>", "]]>")
MsgBox("replaced")
rd.Close()
Dim wr As StreamWriter = New StreamWriter(fileName)
wr.Write(xml)
wr.Close()
Dim xdoc As XmlDocument = New XmlDocument()
xdoc.Load(fileName)
Catch ex As Exception
UpdateXML(fileName, ex.Message)
End Try
End Sub
End Class

View 4 Replies View Related

Referencing One Item's Hidden Property In Order To Set Another's Hidden Property

Feb 15, 2007

Hello,

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.



View 1 Replies View Related

How To Allow Non-sa To Modify A Job?

Jan 15, 2008

Is there a way to allow non-sysadmin to modify jobs they don't own from the management studio interface?

Even the most privileged SQLAgentOperatorRole does not allow this...

Thanks

View 10 Replies View Related

Need To Modify This SQL...

Oct 22, 2007

The following was not written by me but I need to modify it and I am definitely no expert in SQL. It returns a list of files that a user is allowed to access based on whether or not they belong to a specific user group and that group has been granted access to the file.

Here is the SQL as it is currently:

SELECT a.ID, a.Title, a.Description, a.FileName, a.DateCreated, a.DateModified, u.UserName, a.UserID, COUNT(ga.AnswerFileID) AS ShareCount

FROM
User AS u INNER JOIN

AnswerFile AS a LEFT OUTER JOIN

GroupAnswerFile AS ga LEFT OUTER JOIN

GroupUser AS gu
ON ga.GroupID = gu.GroupID ON a.ID = ga.AnswerFileID ON u.ID = a.UserID

GROUP BY a.ID, a.Title, a.Description, a.FileName, a.DateCreated, a.DateModified, a.UserID, u.UserName, gu.UserID

HAVING (gu.UserID = 2)

UNION

SELECT a.ID, a.Title, a.Description, a.FileName, a.DateCreated, a.DateModified, u.UserName, a.UserID, 0 AS ShareCount

FROM AnswerFile AS a INNER JOIN User AS u ON a.UserID = u.ID

WHERE (a.UserID = 2) AND (a.ID NOT IN SELECT AnswerFileID FROM GroupAnswerFile))

I need to modify this code to add an additional check. I have added a file that allows specific users to be granted access to files. It is called AnswerFileUser and contains AnswerFileID (the file) and UserID (the user that has been given access). I need the query to return as part of the results those files where a user has been given access regardless of their group or group access. I hope this all makes sense. Thanks for your help!

Oops - this is SQL 2005...

View 1 Replies View Related

Can We Modify Views

Jan 2, 2007

give me answer and what's the difference between view and stored procedure

View 2 Replies View Related

Modify The Timeout Value

May 18, 2007

How do you change the time out value for a specific connection? I'm having problems when I connect to SQL Server from my website and I want to test if this would help.

View 2 Replies View Related

How To Modify Data?

Jun 18, 2007

How do I modify data in a sqlDataSource object (like inserting records, editing, deleting) similiary to how I modify data in a GradView using a DataList or repeater? I want to be able to have my own EditTemplate and be able to edit say four fields while I'm only viewing like two of them in the datalist/repeater.
<asp:DataList ID="DataList1" runat="server" DataKeyField="GameId"
DataSourceID="SqlDataSource1" RepeatColumns="1">
<ItemTemplate>
<%# Eval("field1") %><%# Eval("field2") %>....
</ItemTemplate>
<EditTemplate>......</EditTemplate>

</asp:DataList>
 
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>"
SelectCommand="SELECT ...."></asp:Sql....>
 
When I'm trying to add a edit-button (<asp:Button runat="Server" ID="EditButton" Text="Edit" CommandName="Edit" />) and click it nothing happens....What is missing?

View 1 Replies View Related

HOw To Modify DTS Package?

Jul 10, 2007

I have a DTS was created by another programmer which is for move online Database to a offline Database
and now I have to modify it
the DTS package
have 1 Source DB
and 63 distinateDB
between SourceDB and DistinateDB are "Black Line"
and almost of DistinateDB have a green line between a  Task ( excute SQL work)
 -- some of SQL work are  Delete Table and some are Truncate Table
and now I have to add some limits for the DTS
1. First, move all the  Data From online Database to Offline Database
2. to Delete Data from online DB  , Online Database will only keep Data for 6 months ( older than 6 months have to delete except table A and table B)
 can you help me? or tell me where have a reference information I can take a look... have no idea how to modify this DTS package..
 
thank you very much
 

View 3 Replies View Related

Can You Please Help Me To Modify My Sql Query?

Oct 30, 2007

my column UPdateTime , Type DateTime
 
UPdateTime
---------------------
2007-04-18 00:00:00.0002007-08-17 00:00:00.0002007-09-05 00:00:00.0002007-09-05 00:00:00.000
 
 
I write a Sql Query for select  UPdateTime between 2007/09/01~ 2007/09/30
select * from ZT_Master where TDateTime < DateAdd(Month,-1,GetDate())
the result return all the column.. why?
 
thank you

View 5 Replies View Related

Need To Modify Sql Query

Mar 12, 2008

I have the following query that produces this:
1 0 Arts & Entertainment6 1 Arts & EntertainmentDance2 1 Arts & EntertainmentMovies9 0 Automotive10 0 Business11 0 Cancer12 0 Communications3 0 Computers13 3 ComputersE-Learning4 3 ComputersHardware14 3 ComputersJava16 3 ComputersLink Popularity17 3 ComputersMicrosoft.net15 3 ComputersRSS5 3 ComputersSoftware7 0 Real Estate8 7 Real EstateFinance
 Here is the query:
SELECT tblArticleCategory.ACategoryID, tblArticleCategory.AParentID, ISNULL(Parent.ParentCategory + '', '') + ISNULL(tblArticleCategory.ACategoryname, '') AS ACategory
FROM tblArticleCategory FULL OUTER JOIN(SELECT ACategoryName AS ParentCategory, ACategoryID
FROM tblArticleCategory AS tblArticleCategory_1
WHERE (AParentID =0) AND (AActive = 1)) AS Parent ON Parent.ACategoryID = tblArticleCategory.AParentID
WHERE (tblArticleCategory.AActive = 1)
ORDER BY ACategory
How do I modify this so that just the category name is returned and not the parent cetegory.  In other words, everything shows up just once. Arts and Entertainment, once, then on the next line Dance, then Music then the next parent category which is automotive.
Here is the table:
ACategoryID int UncheckedACategoryName nvarchar(150) CheckedADescription nvarchar(300) CheckedAParentID int CheckedAActive bit Checked  Unchecked

View 6 Replies View Related

Modify The SQL Of A SQLDatasource

Apr 5, 2006

When you first come into the web page I want to exam the querystring and the modify the SQL Statement accordingly in a SQLDatasource.What is the right event of the SQLDataSource to modify the SQLDataSource.SelectCommand = MySQLString property?Thanks

View 3 Replies View Related

Modify Publication In SQL 7.0 . Please Help !!

May 14, 2002

Hello ,

I have a problem with a publication which is a transactional replication on SQL Server 7.0 .
I want to add stored procedure to the existing publication along with the tables . But the Enterprise manager does not allow to select or check the box
against the required stored procedure although it displays when i right click on the publication and select proeprties .

Can i have some help in this regard ? Does anybody knows to add a stored procedure by means of a query .

Is there any other way to modify publication in SQL Server 7.0 although it is possible in SQL Server 2000 .?

Many thanks in advance.
Sandra .

View 1 Replies View Related

Help Need To Modify Procedure

Jul 20, 2002

Have a script that should extract data if :-

1) Mothly :-
-payday is equal to @today
-pay frequency is = 1
-payday between 1-31

2) Weekly
-payday is between 1-5 ( 1 =Monday,2 =Tuesday,3=Weds,4=Thurs,5=Friday)
-payfrequency is = 2

3) Fortnightly
-after two weeks
-payfrequency =3


How do l modify this procedure to be able to extract using the listed conditions ? Need help

CREATE Procedure Collections_Cats
AS
BEGIN

Declare @today int
Set @today = (SELECT Day(GETDATE()))

DECLARE Collections_Cats_Cursor
CURSOR
FOR
SELECT distinct
n.loan_No AS Loan_No,
n.customer_No AS Customer_No,
c.first_name AS First_name,
c.second_name AS Second_name,
c.surname AS Surname,
c.initials AS Initials,
b.Bank_name AS Bank_name,
br.branch_code AS Branch_code,
d.bank_acc_type AS Bank_acc_type,
pay_sheet.pay_frequency AS Pay_Frequency,
n.monthly_Payment AS monthly_Payment,
pay_sheet.payday AS payday
FROM Transaction_Record tr

INNER JOIN
Loan n ON tr.loan_No = n.loan_No
INNER JOIN
Customer c ON n.customer_No = c.customer_no
INNER JOIN
Bank_detail d ON c.customer_no = d.customer_no
INNER JOIN
Branch br ON d.Branch = br.Branch
INNER JOIN
Bank b ON br.Bank = b.Bank
INNER JOIN
pay_sheet ON c.customer_no = pay_sheet.customer_no
WHERE Pay_sheet.Payday = @today AND pay_sheet.pay_frequency =1

Order by l.loan_No

OPEN Collections_Cats_Cursor

-- Perform the first fetch.
FETCH NEXT FROM Collections_Cats_Cursor

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM Collections_Cats_Cursor
END

CLOSE Collections_Cats_Cursor
DEALLOCATE Collections_Cats_Cursor
END;
GO

View 1 Replies View Related

Modify Replication Job

Apr 20, 2004

Hi,
sql server 7

i have a replication job going on for one particular table from one server to another
now i have added two new columns in source and replication works fine.
once i add these two new columns in destination the replication is failing.

i went to properties of the publisher and select the filter columns where i can find all the columns checked except the two new columns when i try to select these two its not responding pls help me in this.

pls help me in modifying this replication.

TIA
hastal

View 2 Replies View Related

Object Last Modify

Apr 16, 2008

Hi Guys,

I am using Sql server 2005, i am trying to find out all the tables that modified recently. i cant find any last modified date!!.

My situation is; i have altered so many tables, usually when i do alter i save the sql file for doing the same alteration in the production database. but somehow i lost the scripts and i am not sure what are the tables that i have modified and what is new.

i dont want to overwrite the whole database.

can anybody know how to get only the difference.

thanks

View 2 Replies View Related

Modify Trigger

Jul 26, 2007

I have converted Access database to sql express. Access Database had AutoNumber FIelds for which trigger was generated by Upsizing wizard.

Now when I import data from client the autonumber field value changes because trigger is fired which distroys all links

I want to modify trigger so that it generates new number only when it is not supplied in a insert command.

Please help. Code is given below. Also suggest how to save because when I use save, it asks for a new .sql file name and a new file is generated instead of modifying the same trigger



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[T_AcControlLimit_ITrig] ON [dbo].[AcControlLimit] FOR INSERT AS
SET NOCOUNT ON
DECLARE @randc int, @newc int /* FOR AUTONUMBER-EMULATION CODE */
/* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'IntAcControlCode' */
SELECT @randc = (SELECT convert(int, rand() * power(2, 30)))
SELECT @newc = (SELECT IntAcControlCode FROM inserted)
UPDATE AcControlLimit SET IntAcControlCode = @randc WHERE IntAcControlCode = @newc

View 1 Replies View Related

Modify Index

Jul 20, 2005

Hi all,I have been left to modify an old unique index, which no longer works,as the two fields, which were used, are no longer unique. I’ve found twoothers that are unique. But as I’m new to this I need some advice first.What happens if one table inserts from another (the second containingthe index). However if there is a duplicate will the records which arenot duplicates be inserted? How could I insure this?ThanksSam*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 4 Replies View Related







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