How To Solve Conflicting Component ID's
Feb 2, 2007
Hello,
I have 3 packages in one solution:
package A is the main package and calls packages B and C using 2 "Execute Package Task"s.
package B is a package constructed from scratch.
package C is a package started by file copying the package B .dtsx file in Windows Explorer and then adding/removing/renaming content.
If I run packages B and C separately they work fine.
If I run package A, package B is started and finishes correctly. Then package C is started and stops somewhere in the middle.
I guess this is because some of the component id's within package C are identical to those in package B ( I checked in the dtsx packages using Notepad. ) and somehow SSIS remembers the precedence constraints of package B and stops executing the rest of C. But I am not sure. And I do not know how to solve this without a risk of losing two days of work.
Any advice/suggestions ?
TIA
Jan Vandepitte
View 6 Replies
ADVERTISEMENT
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
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
May 18, 2004
Hello All,
I work for a company that packages MSDE and MSSQL 8.0 because our software requires storing client information in a database. The problem is that we've found that if the our users have another DB engine installed (BDE or Paradox usually), our program has trouble using MSDE to access the database and thus making our software inaccessible. It behaves as if this other database engine locks on our database before MSDE can. I'm basically looking for help on either how to uninstall these other db engines or how to get around this problem so that both MSSQL and other db engines can co-exist in one system.
View 12 Replies
View Related
Jan 16, 2008
I have a server with sql 2000 and 2005 installed on it. When I try to connect to the msde 2000 database with an app or with managment studio express I get the following error
"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476"
But When I go through dos and type osql -S(local)database -E
it connects to the correct msde instance. How can I fix this with out uninstalling sql server 2005????
View 6 Replies
View Related
Jan 17, 2008
I have a server with sql 2000 and 2005 installed on it. When I try to connect to the msde 2000 database with an app or with managment studio express I get the following error
"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476"
But When I go through dos and type osql -S(local)database -E
it connects to the correct msde instance. How can I fix this with out uninstalling sql server 2005????
View 3 Replies
View Related
May 10, 2006
I need p.phonenumber in there but then it messes up my DISTINCT ON m.number because I end up with dup m.numbers if I run just the inner select statement during testing.
But then I need the phonenumber in that subquery so that I can do list.phonenumber
ahh!
UPDATE Master
SET master.homephone = list.phonenumber
FROM MASTER m
INNER JOIN
(SELECT DISTINCT p.number, topphone.phonenumber
FROM phones p
INNER JOIN (SELECT top 1 phonenumber from phones) as topphone ON topphone.number = p.number
WHERE p.phonetypeID = 1
AND ISNULL(p.good, 0) <> 0
AND LEN(p.phonenumber) = 10
) AS list ON m.Number = list.Number
WHERE m.homephone IS NULL OR m.homephone = ''
Error:
Msg 156, Level 15, State 1, Procedure Skiaa, Line 66
Incorrect syntax near the keyword 'top'.
Msg 156, Level 15, State 1, Procedure Skiaa, Line 88
Incorrect syntax near the keyword 'top'.
I want to update m.homephone with the p.phonenumber based in the filtering critiria I have specified but this simple task is turning out to be a pain.
View 1 Replies
View Related
Dec 12, 2007
Hi all,
I need some advice on this scenario.
I have to set a Disaster Recovery (DR) server from my production Server( Most probably, i will be using Database Mirroring for my DR Server). Right now, the production is log shipped to another server for reporting every 15 minutes.
So, for setting up Database Mirroring onto my DR Server, i can take Full Backup from production without damaging log Shipping.
But, for setting up Database Mirroring, we need to restore most recent Transaction log backup with norecovery ( Correct me if i am wrong).
But, I cannot take log back because the prodcution database is log shipped for Reporting needs. If taken, log chain breaks.
Can anyone please suggest some work around for setting up the DR Server with minimal downtime.
Thanks for all your help.
View 5 Replies
View Related
Jan 15, 2007
Hi,I basically have two tables with the same structure. One is an archiveof the other (backup). I want to essentially insert the data in to theother.I use:INSERT INTO table ( column, column .... )SELECT * FROM table2Now, table2 has a rule on various columns:@CHARACTER IN ('Y','N')but the column allows nulls, in the design view is says so anyway.When I run this query I get:A column insert or update conflicts with a rule imposed by a previousCREATE RULE statement. The statement was terminated. The conflictoccurred in database 'database', table 'table', column 'column'.The statement has been terminated.Obviously, I've changed the names of everything.The only data in those columns which could possibly conflict with therule is the NULL value. Any ideas why this doesn't work?Thanks.
View 8 Replies
View Related
Jul 14, 2006
Hi,
Using Merge replication, I have a table that is filtered using the HOST_NAME() function. The filter also makes use of a function (as the HOST_NAME() is overriden to return some complex data).
Everything replicates and filters just fine. but when I add a join filter on a different table (the join filter is a simple foreign key join) I get the following error when the snapshot agent is run:
Message: Conflicting locking hints are specified for table "fn_GetIDList". This may be caused by a conflicting hint specified for a view.
Command Text: sp_MSsetup_partition_groups
Parameters: @publication = test1
fn_GetIDList is the function used in the original filter.
Thanks for any help
Graham
View 6 Replies
View Related
Jun 15, 2015
I am working on an app that getting quite a few deadlocks due to delete statements. Â I have turned on the sql trace flags and pulled the offending delete statements out of the ERRORLOG and trying to mesh those up with the indexes defined on the table, etc. looking to see if there is anything that can be done strictly from the db side (no app code change) to reduce/eliminate these deadlocks. Â I have ran some tests/played around with RCSI and even disabling lock escalation but neither have improved my results.
What I have done is to search the errorlog file for DELETE FROM Tablename, output those matching lines, then sort of normalize the literal values to # or XYZ, open in Notepad++, removed trailing whitespace + dups and sort to come up with these results for the unique list of offending T-SQL statements (a LOT easier to read in text editor so sending screen cap. Â
Open this url in new tab [URL] ....
View 7 Replies
View Related
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
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
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
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
Oct 14, 2015
I have four tables with relationships as shown. They have a circular relationship and so one of the relationships is forced to be inactive.
  Operation (Commodity, OperationKey)  ==========>Â
Commodity (Commodity)
                     /                                                                       Â
/
                      |                                                                        Â
|
  Advice (OperationKey)      ====== (inactive)=======>
Operation Commmodity (Commodity, OperationKey)
I have the following measure:
Advice No. Commodity:=CALCULATE (
COUNTROWS ( 'Advice' ),
USERELATIONSHIP(Advice[OperationKey],'Operation Commodity'[OperationKey]),
operation
)
However, the userelationship function does not override the active relationship between Operation & Advice and so the measure is limited to Advices directly filtered by the Operation table.
If I delete the relationship between Operation and Advice, then the measure works as expected i.e. Operation indirectly filters Operation Commodity which filters Advice.
View 9 Replies
View Related
Jan 3, 2007
I get this error when i try to connect with my sql,
I created my SQL with MS SQL 2005 Workgroup,
the error is:
Server Error in '/' Application.
The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Configuration.Provider.ProviderException: The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version.
View 2 Replies
View Related
Mar 25, 2006
When i used MySql in my appliaction its working well, but now i shifted my web application on SQL Server2000 , its gives problem.
It gives this error at runtime...
The specified module could not be found.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.IO.FileNotFoundException: The specified module could not be found.Source Error:
Line 79: SqlCommand OcCom=new SqlCommand(Query,OdCon);
Line 80: SqlDataReader OdDat;
Line 81: OdCon.Open();
Line 82: OdDat=OcCom.ExecuteReader(CommandBehavior.CloseConnection);
Line 83: return OdDat;
i read ths stuff from all over and reinstall the Visual studio .NET2003 again . but still some is not solved.
So Pls help Me!!!!!!!!!!!!!!
Thanx
Manish
View 3 Replies
View Related
Sep 5, 2001
Lets say I want to delete a record in tableA. tableA has 5 foreign key tables.
Before I delete the row in tableA, I want to make sure I dont get any foreign key violation error. I dont want to make a cascading delete, since the record should not be deleted if it has child records.
Of course I could check all those tables before the delete. Anybody know of a better approach?
Regards,
Stefan
View 1 Replies
View Related
Jan 23, 2004
hi
I have a question:
why when we call a stored procedure in delphi
that the return in Null (Example Not Match data with our Select)
Error occured?
Please help me .
thanks alot.bye.
View 2 Replies
View Related
Jan 1, 2008
Hi! i need to know which tables in my database are not used by any stored procedure in my database.i need ur help.
View 2 Replies
View Related
Jan 10, 2008
hi,
i have a doubt whether constraints(for eg:default constraint ) can be applied on table variable or not. plz let me know. iam presenting the table variable which iam using.
declare @t table (a int constraint d default 1 ,b varchar(10) constraint a default 'india' )
insert into @t values ( 1,'ic')
thanks
View 2 Replies
View Related
Mar 5, 2008
hi,
I need to get values in a column of different rows to be comma seperated.
example:
table a
col1 col2 col3
1 a b
2 c d
3 e f
I want output like this
col1 col2 col3
1,2,3 a,c,e b,d,f
Thanks in advance.........
View 7 Replies
View Related
Mar 24, 2008
Help me out how to solve this requirement
Table_1
Col1Col2Col3Col4
1101Usr1WRK1
2101Usr2WRK1
Table_2
Col1Msg
101User %1 Logged on %2
Expected Output
User Usr1 logged on WRK1
User Usr2 logged on WRK1
View 2 Replies
View Related
Mar 2, 2007
I'm a beginer!
I host my web on a hosting service provider. But when I access an error occur:
Server Error in '/' Application.
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[SqlException (0x80131904): An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734867
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) +820
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +130
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
System.Web.UI.WebControls.DetailsView.DataBind() +4
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
System.Web.UI.WebControls.DetailsView.EnsureDataBound() +181
System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69
System.Web.UI.Control.EnsureChildControls() +87
System.Web.UI.Control.PreRenderRecursiveInternal() +41
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210
View 1 Replies
View Related
Oct 1, 2007
Hi,there are three tables:1) department---------------ID (primary key)name2) reports----------ID (p. key)depIDuserID3) users--------ID (p.key)nameI want to get in one query how many distinct users have made a report for each department.E.g.: table reports may look like this:ID depID userID1 1 12 1 13 1 34 3 65 4 8This gives:for dep 1: 2 distinct usersfor dep 2: 0for dep 3: 1 distinct userfor dep 4: 1 distinct userThanks for helpTartuffe
View 5 Replies
View Related
Nov 12, 2007
Hi, I am trying to update my table... but when i try to save my query.. i am getting the following error
Column 'SourceDBF.DEFPCT' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. This is my sproc...
UPDATE
Statements..ParticipantSourceSummary
SET
DeferralPct = DEFPCT,
BeginingBal = BEGBAL,
Deposits = DEPOSITS,
Withdraw = WITHDRAW,
GainLoss = GAINLOSS,
Others = OTHER,
EndingBal = ENDBALANCE,
VestPercent = VESTPCT,
VestBal = VESTBALANC
FROM
Statements..ParticipantSourceSummary ps
Join (
SELECT
p.ParticipantId,
cp.PlanId,
cs.SourceId,
@PeriodId PeriodId,
s.DEFPCT,
s.BEGBAL,
s.DEPOSITS,
s.WITHDRAW,
s.GAINLOSS,
s.OTHER,
s.ENDBALANCE,
s.VESTPCT,
s.VESTBALANC
FROM
SourceDBF s
INNER JOIN Statements..ClientPlan cp
ON s.PLAN_NUM = cp.ClientPlanId
INNER JOIN Statements..ClientSources cs
ON s.SOURCE_NUM = cs.ClientSourceId and cs.ClientId = @ClientId
Inner Join Statements..Participant p
on s.PART_ID = p.SSN
Where
s.Import = 1
GROUP BY
p.ParticipantId,
cp.PlanId,
cs.SourceId
) s
ONps.ParticipantId = s.ParticipantId
AND
ps.PlanId = s.PlanId
AND
ps.SourceId = s.SourceId
AND
ps.PeriodId = s.PeriodId any help will be appreciated.RegardsKaren
View 1 Replies
View Related
May 15, 2008
Hi all
i have a problem in my stored procedureUSE [eLocalize_System]
GO
/****** Object: StoredProcedure [dbo].[Insert_Branch] Script Date: 05/15/2008 16:03:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================ALTER PROCEDURE [dbo].[Insert_Branch]
(
@Building_No int
,@Street_Name nvarchar(50)
,@City nvarchar(50)
,@Postal_Code nvarchar(50)
,@Telephone nvarchar(50)
,@Fax nvarchar(50)
,@Client_Id int
,@Country_Id int
,@Row int output)
AS
Declare
@BranchCode int,
@Branch_Name nvarchar(50)
set @BranchCode=isnull((select Max(BranchCode) from dbo.Branch where Client_Id=(select Id from dbo.Client where Id=@Client_Id)),0);
set @BranchCode=@BranchCode+1
set @Branch_Name=(select City from dbo.Branch)+'+'+(select Name from dbo.Country where Id=(select Country_Id from dbo.Branch))INSERT INTO [eLocalize_System].[dbo].[Branch]
([Branch_Name],[BranchCode]
,[Building_No],[Street_Name]
,[City],[Postal_Code]
,[Telephone],[Fax]
,[Client_Id]
,[Country_Id])
VALUES
(@Branch_Name,@BranchCode ,
@Building_No ,@Street_Name
,@City ,@Postal_Code
,@Telephone ,@Fax
,@Client_Id
,@Country_Id)
set @Row = (select scope_identity())
return @Row
i want Branch_Name = City + Country
and how i do it if there is any update
Thanks for help
View 1 Replies
View Related
Jul 26, 2000
Subscriptions were created successfully at the following Subscribers:
SERVER330
SQL Server Enterprise Manager could not start the service 'MSDTC' on server 'SERVER330'.
5 - (Access is denied)
The subscription at Subscriber 'SERVER330' cannot be initialized immediately because the snapshot for this publication is not yet available. To initialize the subscription, start the Distribution Agent after the snapshot is available.
View 1 Replies
View Related
Jun 8, 2007
Hello friends,
I want to find only fifth row from a table please give me better solution.
thanks in adavance
regards
Harsh
View 4 Replies
View Related
May 13, 2006
could some one help in giveing solution for this tasks..
[CODE]
Task 1 : Get the first day and last day of the week
Ex: input : '04/12/2006'
Required ouput :
start_date End_date
04/09/200604/15/2006
Note :1. start_date should be sunday
2. end_date should be saturday
Task 2 -- Find the product sales week wise
Find the product sales week wise
Output Required:
gasstationID productClass week1 week2 week3 week4
-------------------- ------------ ----------- ----------- ----------- -----------
111 DSL 1 0 0 0
111 GAS 2 0 1 1
222 DSL 1 0 1 1
222 GAS 1 0 0 0
create table trial_sales(
gasstationIDbigint,
salesDatesmalldatetime,
gallonsbigint,
productClass varchar(4))
insert into trial_sales values(111,'11/01/2005',10000,'GAS')
insert into trial_sales values(111,'11/02/2005',1000,'GAS')
insert into trial_sales values(111,'11/02/2005',20000,'DSL')
insert into trial_sales values(222,'11/01/2005',10000,'GAS')
insert into trial_sales values(222,'11/02/2005',10000,'DSL')
insert into trial_sales values(111,'11/15/2005',1000,'GAS')
insert into trial_sales values(222,'11/15/2005',10000,'DSL')
insert into trial_sales values(111,'11/25/2005',1000,'GAS')
insert into trial_sales values(222,'11/25/2005',10000,'DSL')
Task 3 Display unique values
Display unique values in
create table t (colA varchar(25))
insert into t values('apple')
insert into t values('bat')
insert into t values('car')
ouput required:
a
b
c
e
l
p
r
t
Task : 4
I have a table in the database I am working with with the following columns:
(I have simplified this down for the sake of clarity)
int p1
int p2
int p3
int s1
date d1
string s1
There is no primary key and the table more resembles a log than anything.
e.g.
p1 p2 p3 s1 d1 s1
1 12 14 100 23May comment 2
1 12 14 800 22May comment 1
1 12 14 300 24May comment 3
1 12 15 100 22May comment 2
1 12 15 800 21May comment 1
2 12 15 100 23May comment 1
What I want to do is exttract the rows from the table where the combination
of p1 p2 and p3 is distinct/unique and the date is the most recent one for
all combinations of p1, p2, p3. So from the table above I would end up with
the resultset:
p1 p2 p3 s1 d1 s1
1 12 14 300 24May comment 3
1 12 15 100 22May comment 2
2 12 15 100 23May comment 1
Task 5
say i have the following data...
uId(int PK), productcode(int), quantity(int)
1,12,5
2,12,3
3,12,5
4,11,9
....and i have an order for productcode 12 with quantity of 6 products.
i need to select only enough rows to reach a quantity of 6.
in this case, i would want the first two rows which would give me a sum of
8.
how can i do this without looping through each record, getting the quantity
and exiting the loop when i have a sum >= 6?
Task 6
I have two tables as below, TABLE1 and TABLE2.
TABLE 1: Base
IDPNamePPrice
-------------------------------------
1A30
2B20
TABLE 2: History
IDLdateAmount
-------------------------------------
12005/8/750
The ID of TALBE1 is the primary key and the ID of TABLE2 is the foreign key.
What's the right T-SQL JOIN statement when I pass the date of 2005/8/7,
it will return the result as below:
LdatePNameAmount
------------------------------------
2005/8/7A50
2005/8/7Bnull
and when I pass the date of 2005/8/8, it will return the result as below:
LdatePNameAmount
------------------------------------
2005/8/8Anull
2005/8/8Bnull
Task : 7
Reverse the Name
create table sample_judge(
judge_namevarchar(50))
go
insert into sample_judge values ('J Smith')
insert into sample_judge values ('M Samuel')
insert into sample_judge values ('S Shan')
insert into sample_judge values ('K Anand')
insert into sample_judge values ('K K K K Anand')
insert into sample_judge values ('K R Shanth Anand')
insert into sample_judge values ('K R Shanth Anand Kumara Velu')
go
Expected Output :
Smith J
Samuel M
Shan S
Anand K
Anand K K K K
Shanth Anand K R
Shanth Anand Kumara Velu K R
[CODE]
thanks in adv
chakri
View 5 Replies
View Related
Feb 5, 2004
chiranjeevi
raveendra
sreenivasarao
koteswar
manoj
sangeetharanika
Here my query is
in the above names i want to find out the persons whoz names consist of exact 2 a's
eg output:
raveendra
assume that the names are ename and table name is emp.
Thanks in advance
Plz mail this qury to chiru_y2k@yahoo.com
Chiranjeevi
View 2 Replies
View Related
Feb 9, 2004
Hi,
I have two database servers and in this one is consists of tables and another one is for stored procedures ...
Here is my proble...
I want to access the stored procedures from my application(.net) ...
How to do...
Thanks in Advance
Chiru
View 1 Replies
View Related
