I'm having deadlock problems with SQL Server 2000. I'm a .NET developer, and I'm far away of being a DBA, so I frequently have problems with bad database design and queries (I'm gonna have to work these skills...)
Maybe it's a silly problem, but here it is:
I have an application that inserts data from files into the database (these files contain some web page hits). This application is multi-threaded, i.e., I can have fifteen threads at the same time trying to insert the data into the database.
My table is looks like this:
year (smallint)
month (tinyint)
day (tinyint)
field1 (varchar - 10)
field2 (tinyint)
hits (int)
My application was working well until I start to use the .NET TransactionScope.
In my application I create a transaction, and then I go through the file and for each line I call a procedure.
The procedure has the job of verify if there's already a record for that "page", and if it has, update the "hits" field. If not, insert a new row.
I don't know what to do about it. Maybe it's all about reestructuring the procedure, the table, the indices, but I really don't know what to do.
The table has only three indices: one for field1, another for field2 and another for the fields year and month...
Any help will be very appreciated.
Thanks in advance.
I am having deadlock problem when I have a lot of visitors on my ASP.NET website at the same time. I am using NetTiers templates to generate C# classes for accessing DB layer and problem is in my custom Store Procedure.
I have Article table and ArticleLanguage table. One record from Article (Id, Position, StatusId) table is the same for all languages and in ArticleLanguage (Id, LanguageId, ArticleId, Name) table I have only article names for every language. I then physically created ArticleListing "ghost" table that have all fields from these two tables and this listing table is used for displaying articles in grid and this table is filled with my custom Store Procedure. It must not be created in memory (temporary table) because NetTiers must generate Entity for it (I got TList collection). And there I have a SQL problem, because in my Store Procedure first command is DELETE FROM ArticleListing (I also tried this trick with GUID, it didn't help me much - you will see in script bellow) and then I do INSERT FROM Article INTO ArticleListing... and then UPDATE ArticleListing FROM ArticleLanguage... When there is a lot of users they call this store procedure and deadLocks occured very often - I suppose because of deleting and inserting into this "ghost" table... I am sending you a scripts in order to know exactly what I am doing and please help me with advice is it possible to change Store Procedure to avoid deadlock?
Code:
CREATE TABLE dbo.Article( Id int IDENTITY(1,1) NOT NULL, Position int NULL, StatusId int NULL CONSTRAINT PK_Article PRIMARY KEY CLUSTERED ( Id ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY ) ON PRIMARY
CREATE TABLE dbo.ArticleLanguage( Id int IDENTITY(1,1) NOT NULL, LanguageId int NULL, ArticleId int NULL, Name varchar(100) NULL CONSTRAINT PK_ArticleLanguage PRIMARY KEY CLUSTERED ( Id ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY ) ON PRIMARY GO SET ANSI_PADDING OFF GO ALTER TABLE dbo.ArticleLanguage WITH CHECK ADD CONSTRAINT FK_ArticleLanguage_Article FOREIGN KEY(ArticleId) REFERENCES dbo.Article (Id) GO ALTER TABLE dbo.ArticleLanguage CHECK CONSTRAINT FK_ArticleLanguage_Article
CREATE TABLE dbo.ArticleListing( Id int IDENTITY(1,1) NOT NULL, TransactionGuid varchar(40) NULL, TransactionDate datetime NULL CONSTRAINT DF_ArticleListing_TransactionDate DEFAULT (getdate()), LanguageId int NULL, ArticleId int NULL, ArticleLanguageId int NULL, Name varchar(100) NULL, Position int NULL, StatusId int NULL CONSTRAINT PK_ArticleListing PRIMARY KEY CLUSTERED ( Id ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY ) ON PRIMARY
GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Description: Returns Article and ArticleLanguage listing in one table -- ============================================= CREATE PROCEDURE dbo._ArticleListing_GetListing ( @LanguageId int, @ArticleId int = null, @StatusId int = null ) AS BEGIN
--Delete old records DELETE FROM ArticleListing WHERE TransactionDate < DATEADD(minute, -2, GETDATE())
--Create new GUID for this transaction DECLARE @TransactionGuid varchar(40) SET @TransactionGuid = NEWID()
--Insert from Article INSERT INTO ArticleListing ( TransactionGuid, ArticleId, Position, StatusId ) SELECT @TransactionGuid, Id, Position, StatusId FROM Article WHERE (@ArticleId IS NULL OR Id = @ArticleId) AND (@StatusId IS NULL OR StatusId = @StatusId)
--Update from ArticleLanguage UPDATE ArticleListing SET ArticleListing.LanguageId = ArticleLanguage.LanguageId, ArticleListing.ArticleLanguageId = ArticleLanguage.Id, ArticleListing.Name = ArticleLanguage.Name FROM ArticleLanguage LEFT JOIN ArticleListing ON ArticleListing.ArticleId = ArticleLanguage.ArticleId WHERE TransactionGuid = @TransactionGuid AND ArticleListing.ArticleId = ArticleLanguage.ArticleId AND ArticleLanguage.LanguageId = @LanguageId AND (@ArticleLanguageId IS NULL OR ArticleLanguage.Id = @ArticleLanguageId)
--Delete not valid records DELETE FROM ArticleListing WHERE TransactionGuid = @TransactionGuid AND (LanguageId IS NULL OR ArticleLanguageId IS NULL)
SELECT Id, TransactionGuid, TransactionDate, LanguageId, ArticleId, ArticleLanguageId, Name, Position, StatusId FROM ArticleListing WHERE TransactionGuid = @TransactionGuid ORDER BY Position END
and exception...
Code:
Message: Transaction (Process ID 166) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
StackTrace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteReader(DbCommand command, CommandBehavior cmdBehavior) at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteReader(DbCommand command) at MyProject.Data.Utility.ExecuteReader(Database database, DbCommand dbCommand) at MyProject.Data.SqlClient.SqlArticleListingProviderBase.GetListing(TransactionManager transactionManager, Int32 start, Int32 pageLength, Nullable`1 languageId, Nullable`1 articleId, Nullable`1 statusId) at MyProject.Data.Bases.ArticleListingProviderBaseCore.GetListing(Nullable`1 languageId, Nullable`1 articleId, Nullable`1 statusId) at UserControls_ArticleGrid.Page_Load(Object sender, EventArgs e) at System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) at System.Web.UI.Control.OnLoad(EventArgs e) at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
I used veritas to backup MsSQL 2000 database server but I often encounter backup fail error due to the deadlock in the database. May I know how to reduce this problem?
I heard there is a method to control the database deadlock timeout. If we set a lock timeoutvalue, will it reduce the chance of deadlock to happen? Is there any disadvantage of doing that?
If I were not wrong , we can set the lock_timeout value using the syntax "SET LOCK_TIMEOUT milliseconds" Is that true? But Do we have to have to run this command for every database? What is the normal milliseconds value?
Is there a way to send out an email woth deadlock information (victim query, winner query, process id's and resources on which the deadlock occurred) as soon as a deadlock occurs in a database or at instance level?I currently has trace flag 1222 turned on. And also created an alert that send me an email whenever a deadlock occurs. but it just says that a deadlock occurred and I log into sql server error log and review the information.
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.
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
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?
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.
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')
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
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
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
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
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.
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.
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:
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?
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:
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
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) ...
Now I want to execute a sql script when ever I will run this script It will not only transfer data between sql server 2000 and dbase file but Also update the corresponding population value in dbf table, which has same building value In sql server table
1) bothe numeric and decemal are equl. Why we were using both. we can go throgh one of them. Both are under same exact numeric category. In which situation we go for decimal in which situation we go for nummerics?
I want a single query: if the employee role is manager i want to inner join with managers_salaryRange Table. if the employee role is employee then i want to inner join with emp_salaryrange table.
please give me single sql query to solve my problem
I want to show records as per date from Daily_Time_Entry table and detail data to be displayed as follows
dl_iddt_id project_type project_id time_st owner_rep_id owner_name 103/23/08 AB Corporate 11.203tmw 2 03/23/08 AB Corporate23.252hologa 303/21/08 AB Corporate 12.253tmw 403/23/08 AB Plant14.351henry 503/23/08 AB Plant24.502hologa 603/23/08 Other Project13.242hologa 703/23/08 Other Project24.353tmw
Also in project_type AB Corporate for AB_Corporate_Project, AB Plant for AB_Plant_Project , Other Project for Other_Project
I write query for this is as follows
Select dl. dl_id,dl.dt_id,dl.project_type,dl. project_id,dl.time_st,ac. owner_rep_id,ab. owner_rep_id,ow. owner_rep_id,ow. owner_name From Daily_Time_Entry dl left outer join AB_Corporate_Project ac on dl. project_id = ac. ab_crp_id and dl. project_type=’ AB Corporate’ left outer join AB_Plant_Project ab on dl. project_id =ab. ab_plant_id and dl. project_type=’ AB Plant’ left outer join Other_Project op on dl. project_id = op. other_proj_id and dl. project_type=’ Other Project’ inner join Owner_Rep ow on (ow. owner_rep_id = ac. owner_rep_id) or(ow. owner_rep_id = ab. owner_rep_id)or (ow. owner_rep_id = op.owner_rep_id)
So how can I write query to show output as follows or any other way or how to create Sql cursors for to show output Plz help to solve this query to show output as specified.
hi friends i need help in this sql queryi have table like,id fid__ _____autonumber textand i am storing values likeid fid___________________________________1 1,2,3,4,52 11,12,13,14,15now to find values i am using querysql = SELECT * FROM test12 WHERE `fid` LIKE ('%1%')only problem in this query is it is selecting 1 and 11 and i requireonly 1 as i am giving one in %1%now any one have answer of this question then plz plz tell me ........
i am beginer in vc++.i want help in the following questions.How can i create database program using sql server through codings.Ifwe want DSN name it also create through codings. if u have any easysample program please sent the following mail id.Join Bytes!
