hi,
I fired the replication command sp_replcmds on my publisher and it returned the fillowing output.
"Another log reader is replicating the database."
can anybody out there explain why is that so and how can i get the output.
Thanks in advance
Jacx
Dear Friends, I've created one table, with the following attributes.
everything is ok, but while retrieving data, the question numbers should be starting from 1,2.......... but i'm not getting that result. please suggest me to the output like that.
--create table question_code_level (qno int,code varchar(10),level varchar(10),question varchar(500),option1 varchar(200),option2 varchar(200),option3 varchar(200),option4 varchar(200),option5 varchar(200))
select * from question_code_level
1htmlbeginerwhat is clr?somethingsomethingsomethingsomethingsomething
2c#beginerwhat is clr?somethingsomethingsomethingsomethingsomething
3c#expertwhat is clr?somethingsomethingsomethingsomethingsomething
4c#intermediawhat is clr?somethingsomethingsomethingsomethingsomething
5c#beginerwhat is clr?somethingsomethingsomethingsomethingsomething
6vbbeginerwhat is clr?somethingsomethingsomethingsomethingsomething
7htmlexpertwhat is clr?somethingsomethingsomethingsomethingsomething
8sqlserverintermediawhat is clr?somethingsomethingsomethingsomethingsomething
10oraclebeginerwhat is clr?somethingsomethingsomethingsomethingsomething
11javabeginerwhat is clr?somethingsomethingsomethingsomethingsomething
12javaexpertwhat is clr?somethingsomethingsomethingsomethingsomething
13sqlserverbeginerwhat is clr?somethingsomethingsomethingsomethingsomething
--select * from question_code_level where code='c#' and level='beginer'
2c#beginerwhat is clr?somethingsomethingsomethingsomethingsomething
5c#beginerwhat is clr?somethingsomethingsomethingsomethingsomething
Vinod
Gopinath writes "Let us consider we have the following data in a table,
Column1 Column2
John orange
John apple
John Grape
Steve orange
Steve watermelon
Steve pineapple
in the above table, i want to query and get output in the below format
Column1 Column2
John orange
apple
Grape
Steve orange
watermelon
pineapple
is it possible? if yes, kindly let me know the query.
Thanks in advance.
Regards,
Gopi."
I am uploading input sample data and desired output data, can get the desire output.
View 4 Replies View RelatedSee DDL and sample data below. What would be the easiest way to get the desired output without hard coding the values? Data in both tables may change over time.
DECLARE @num AS TABLE (
Id INT IDENTITY(1, 1)
,Price MONEY
)
DECLARE @range AS TABLE (
Id INT IDENTITY(1, 1)
,Rng MONEY
[code]....
I am working on SQL Server 2000 SP4
I am getting a very strange error on one of my instances;
The process could not execute 'sp_replcmds' on #SERVERNAME#
Replication failure. File 'T:shilohsqltdbmssrvreplsrc
eplicat.cpp',
line 1677.
(Source: #SERVERNAME# (Data source); Error number: 18759)
In microsoft http://support.microsoft.com/kb/308865 it tells you to apply
SQL SP4, which i have done and re did just incase it wasn't applied properly,
it made no difference to the situations. Ive restarted the intance and the
server and still to no avail.
Last month we did have a situation where our master database went suspect
and this is the first time anything has had to be replicated on that server
since then. The publisher and subscriber database are on the same server.
Thanks.
Was this post helpful to you?
Why should I rate a post?
I know this one has shown up here before, and I have tried the various things to get past this error and they are not working for this situation. First of all I have tried setting the ReadBatchSize to 1 and secondly I have made the QueryTimeout paramter 2000 in case that were the problem.
In looking at the Error Details within the Log Reader Agent, the following is the message:
Error Message: "The process could not execute 'sp_replcmds'"
Error Details: "An invalid datetime value was encountered. Value exceeds the year 9999."
I can not seem to find any additional information based on the Error Details. If any of you has a suggestion, I would love to hear them.
I am on SQL 2000, SP3. Windows 2000 Server, SP4.
Hi All
I have set up transactional replication between our production and reporting server boxes and getting the following error messages too oftenly.
Anybody having any idea to reslove the issue would be of great help.
Error messages:
The process could not execute 'sp_replcmds' on. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011) Get help: http://help/MSSQL_REPL20011
A system assertion check has failed. Check the SQL Server error log for details (Source: MSSQLServer, Error number: 3624)
Get help: http://help/3624
The process could not execute 'sp_replcmds' on 'STRATASQL4'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
Get help: http://help/MSSQL_REPL22037
Early Thanks,
Salman Shehbaz.
Hello,
I currently have a Transactional Log reader agent failing with the below error:
The process could not execute 'sp_replcmds'
Error: 14151, Severity: 18, State: 1
SQL Server Assertion: File: <logscan.cpp>, line=2223
Failed Assertion = 'm_noOfScAlloc == 0'.
Stack Signature for the dump is 0x24642FE5
Error: 3624, Severity: 20, State: 1.
SQL Server Assertion: File: <logscan.cpp>, line=1985
Failed Assertion = 'startLSN >= m_curLSN'.
Stack Signature for the dump is 0xD7150BD4
Now, I understand that SP4 is supposed to fix a similar issue. SP4 has been installed and the errors keep happening. I do notice that the hot fix mentions different line numbers than the above errors. Does anyone know if this is a new bug? If not can someone explain the fixes to me, thanks,
Tech Drone.
I have a question with a row number. Below I have 2 columns that I want to Put a row number with.
ID. Quantity DesiredRowNumber
123 1. 1
123. 1 1
123. 1. 1
123. 3 2
123. 3. 2
[code]...
How would I get the desired row number.
Hi all,
The query below, as it stands, does a search for strings like 'Fema', that exist in the word 'Female', and returns 'female'. Now, if the word 'Acc' stands on it's own, I want to know how to return it as 'Accessories' in the result set. And yes, I've tried the obvious (When 'Acc' Then 'Accessories') but that returns a Null instead of 'Accessories'!!
Here's the query:
SELECT CategoryID, Category, CASE substring(category, 1, 4)
when 'fema' then 'female' WHEN 'Male' THEN 'Male' WHEN 'Gift' THEN 'Female' END AS CategoryGroup
FROM dbo.InventoryCategory
Thanks all,
Jaybee.
Hello,
I am trying to get results in one row from the following function but all records does not come from the following function.
I have 9 records of the same empid but results not showing all records. Can anybody help me to get all records.
CREATE FUNCTION dbo.GetBenefString5
(
@Empid INT
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @ret VARCHAR(8000)
SELECT @ret = ''
SELECT
@ret = @ret + CASE WHEN LEN(@ret) > 0 THEN ',' ELSE '' END + FName + ' ' + Benefittype + ' ' + BenefitPercentage
From Beneficiary
Where Empid = @Empid
RETURN @ret
END
SELECT
Empid,
dbo.GetBenefString5(Empid)
FROM pf25eaton_work.dbo.eaton_chr_benef_05052008
where EmployeeNumber='4500498'
GROUP BY Empid
Result from the above function query:
Empid ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1773 CHARLENE OLIF 33 ,CHARLENE EADD 33 ,CHARLENE ELIF 33 ,TIMOTHY EADD 33 ,TIMOTHY
(
Records are in the table:
FName Benefittype BenefitPercentage
---------------------------------------- ----------- -----------------
CHARLENE OLIF 33
CHARLENE EADD 33
CHARLENE ELIF 33
TIMOTHY EADD 33
TIMOTHY ELIF 33
BRADLEY ELIF 33
TIMOTHY OLIF 33
BRADLEY OLIF 33
BRADLEY EADD 33
Desired Result in one row:
Charlene OLIF 33, Charlene EADD 33, Charlene ELIF 33, TIMOTHY OLIF 33, TIMOTHY EADD 33, TIMOTHY ELIF 33, BRADLEY OLIF 33, BRADLEY EADD 33, BRADLEY ELIF 33
I have a right join query where i have a table where all employees attendance entry are registered and another table where pay master table where al employees details.
I need a query where i get all employee names from right side (pay master table) and the employees in out timings for all employees who punched or not punched their card.
means for selected date i need all the employees details suppose 10 employees who punched or not with empty values even he resigned.
I used right or left join it gives all dates value but when i select particular date i get only the punched details.
Hi,
I have a query that I am trying to run, but I discovered I am not getting the desired results.
This query here goes across the tables as seen below. One thing that I notice about this query as I am trying to figure out the source of the problem is that it is bringing back the same results even when I change the @userID parameter.
As long as the @userID I pass exists in "tblUserDetails" then we get results back. When it doesnt exist we dont get any records.
Basically to explain whats going on. I am passing the userID of the person who has "photocomments"
Since the table "tblPhotoComments" doesnt have a column that represents who the photocomment was for, we must join
tblExtraPhotos.counterID = tblPhotoComments.photoCounterID
This way we can get details of the photo, including the owner (tblextraphotos.userID)
Any help is much appreciated..
thanks again,
mike123
CREATE PROCEDURE [dbo].[select_Photocomments_ManageMy]
(
@userID int
)
AS SET NOCOUNT ON
SELECT TOP 500 photoCommentID, PC.active, comment, commentFromID,
UD1.nameOnline as commentFrom_NameOnline, PC.commentDate, PC.photoCounterID
FROM tblPhotoComments PC
JOIN tblUserDetails UD1 on PC.CommentFromID = UD1.userID
JOIN tblExtraPhotos EP on EP.counterID = PC.photoCounterID
JOIN tblUserDetails UD2 on EP.userID = UD1.userID
WHERE UD2.userID = @userID AND deletedByRecipient = 0
ORDER by PC.commentDate DESC
CREATE TABLE [dbo].[tblUserDetails](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[NameOnline] [varchar](15) NULL
)
GO
CREATE TABLE [dbo].[tblExtraPhotos](
[counterID] [int] IDENTITY(1,1) NOT NULL,
[photoID] [tinyint] NOT NULL,
[userID] [int] NOT NULL,
[photoDate] [smalldatetime] NOT NULL,
[caption] [varchar](50) NULL,
[status] [tinyint] NOT NULL
GO
CREATE TABLE [dbo].[tblPhotoComments](
[photoCommentID] [int] IDENTITY(1,1) NOT NULL,
[photoCounterID] [int] NOT NULL,
[CommentFromID] [int] NOT NULL,
[Comment] [varchar](1000) NOT NULL,
[commentDate] [smalldatetime] NOT NULL,
[Active] [tinyint] NOT NULL,
[deletedbySender] [tinyint] NOT NULL,
[deletedbyRecipient] [int] NOT NULL,
[IP] [varchar](15) NOT NULL
) ON [PRIMARY]
GO
Query:
Select convert(CHAR(45),surname+','+fname) Name from beneficiary
Output from the above query:
Name
---------------------------------------------
BICKFORD ,ROSA
KOCH ,ERIC
I am desired Results as follows: Please help
Name
---------------
BICKFORD,ROSA
KOCH,ERIC
Hi all,I have a SQL job where I do the following -I check for new rows in my Table "DumpResults", every now and then and get the new rows to be inserted into table "CleanTable". I use OPENXML() to get the new data to be inserted but for some reason I don't get the right data through OPENXML() - DECLARE @intDoc INTDECLARE @xmlDoc VARCHAR(8000)IF(SELECT COUNT(*) FROM DumpResults WHERE DumpResults.C1 NOT IN (SELECT CleanTable.C1 FROM CleanTable)) > 0BEGINSET @xmlDoc = (SELECT * FROM DumpResults WHERE DumpResults.C1 NOT IN (SELECT CleanTable.C1 FROM CleanTable) FOR XML RAW) SET @xmlDoc = '<TABLE>' + @xmlDoc + '</TABLE>'
PRINT @xmlDocEXEC sp_xml_preparedocument @intDoc OUTPUT, @xmlDoc--INSERT INTO CleanTable(C1, C2, C3, C4, C5)
SELECT C1, C2, C3, C4, C5, C6 FROM OPENXML(@intDoc,'/row',1)WITH (C1 INT, C2 CHAR(3), C3 CHAR(3) , C4 FLOAT, C5 INT)EXEC sp_xml_removedocument @intDoc
END
ELSE Output that I get is - <TABLE><row C1="1" C2="AAA" C3="BBB" C4="1.000000000000000e+000" C5="2"/></TABLE>(0 row(s) affected) SO "PRINT @xmlDoc" is returning back the xml data (new results) it collected from the "DumpResults" table which isn't there in "CleanTable" but the "Select... FROM OPENXML(...)" doesn't return any result. why so? If anyone knows please reply If anyone has any better method to do it, inputs are welcome. Thanks
I am trying to select records based upon last name
WHERE (Last_Name BETWEEN 'A%' AND 'C%')
When I run this I get only last names starting with A and B--no C. ?!?
This is confusing to me....I ran it with lastname >= 'A%' and lastname <='C%'and it returned only names starting with B. Why does SQL ignore the "="
I hope this isnt obvious :/
I have a problem...I have two tables, patient_tran and patient_plan. Patient_tran has case_entry_date and patient_id,patient_plan has patient_id, plan_Id, plan_eff_date and plan_term_date. I need to join these two tables and get the patient_id, plan_id but the plan_id should be the plan_id where the entry_date falls between plan_eff_date and plan_term_date. If doesn't match the criteria then pick up the plan_id where the plan_term_date is null, if there's no null plan_term_date then pick up the plan_id with the most recent plan_term_date. In patient_plan table, there's could be more than one plan per patient. How can I do this? Can anyone please help, will be most appreciated.
Thanks in advance!!
Hi,
I want to write a query that would display data from the database on the basis of ethnicity in the last 3 years. Not only that, but I also want to see the number of starters, retention and achievement
e.g.
Year 05 Year06 Year 07
Ethnicity 1 Starters
Retention
Achievement
Ethnicity 2 Starters
Retention
Achievement
The problem is that there are multiple ethnicities and I cannot write separate queries for each of them and there are 3 years and I cannot write separate queries for them either. E.g. if i have 5 ethnicities and 3 years, in current situation, I am ending up with writing 15 queries for each of them because I can't write a cross tab query for these.
When I do write the query as follows:
create table #temp_et (
[Year]int NOT NULL,
[Ethnicity] varchar(20),
[Starts]float(4),
[Success]float(4),
[Retention]float(4),
[Achievement]float(4)
)
--Year 05/06
insert into #temp_et
select
CAST(LEFT(pg_expendyrid,2) AS int)[Year],
pg_ethnicgroupname[Ethinicity],
sum([pvstart]) [Starts],
(case when sum(pvstart) = 0 THEN 0 Else convert(decimal(6,3),(sum(pvach)*1.00)/(sum(pvstart)*1.00)*100)end)[Success],
(case when sum(pvstart) = 0 THEN 0 Else convert(decimal(6,3),(sum(pvcomp)*1.00)/(sum(pvstart)*1.00)*100)end)[Retention],
(case when sum(pvstart) = 0 THEN 0 Else convert(decimal(6,3),(sum(pvach)*1.00)/(sum(pvcomp)*1.00)*100)end)[Achievement]
--into temp_et
from pvmd
where pg_expendyrid = '05/06'
group by
pg_ethnicgroupname,
pg_expendyrid
order by
pg_ethnicgroupname
--Year 06/07
insert into #temp_et
select
CAST(LEFT(pg_expendyrid,2) AS int)[Year],
pg_ethnicgroupname[Ethinicity],
sum([pvstart]) [Starts],
(case when sum(pvstart) = 0 THEN 0 Else convert(decimal(6,3),(sum(pvach)*1.00)/(sum(pvstart)*1.00)*100)end)[Success],
(case when sum(pvstart) = 0 THEN 0 Else convert(decimal(6,3),(sum(pvcomp)*1.00)/(sum(pvstart)*1.00)*100)end)[Retention],
(case when sum(pvstart) = 0 THEN 0 Else convert(decimal(6,3),(sum(pvach)*1.00)/(sum(pvcomp)*1.00)*100)end)[Achievement]
--into temp_et
from pvmd
where pg_expendyrid = '06/07'
group by
pg_ethnicgroupname,
pg_expendyrid
order by
pg_ethnicgroupname
--Year 07/08
insert into #temp_et
select
CAST(LEFT(pg_expendyrid,2) AS int)[Year],
pg_ethnicgroupname[Ethinicity],
sum(pvstart) [Starts],
(case when sum(pvstart) = 0 THEN 0 Else convert(decimal(6,3),(sum(pvach)*1.00)/(sum(pvstart)*1.00)*100)end)[Success],
(case when sum(pvstart) = 0 THEN 0 ELSE convert(decimal(6,3),(sum(pvcomp)*1.00)/(sum(pvstart)*1.00)*100)end)[Retention],
(case when sum(pvcomp) = 0 THEN 0 ELSE convert(decimal(6,3),(sum(pvach)*1.00)/(sum(pvcomp)*1.00)*100)end)[Achievement]
--into temp_et
from pvmd
where pg_expendyrid = '07/08'--Expected end year
group by
pg_ethnicgroupname,
pg_expendyrid
order by
pg_ethnicgroupname
SELECT
(CASE [Year] WHEN 05 THEN [Starts] ELSE 0 END) AS [Starts],
(CASE [Year] WHEN 05 THEN [Retention] ELSE 0 END) AS [Retention],
(CASE [Year] WHEN 05 THEN [Achievement] ELSE 0 END) AS [Achievement],
(CASE [Year] WHEN 06 THEN [Starts] ELSE 0 END) AS [Starts],
(CASE [Year] WHEN 06 THEN [Retention] ELSE 0 END) AS [Retention],
(CASE [Year] WHEN 06 THEN [Achievement] ELSE 0 END) AS [Achievement],
(CASE [Year] WHEN 07 THEN [Starts] ELSE 0 END) AS [Starts],
(CASE [Year] WHEN 07 THEN [Retention] ELSE 0 END) AS [Retention],
(CASE [Year] WHEN 07 THEN [Achievement] ELSE 0 END) AS [Achievement]
from #temp_et
This is the closest I could get to the output as in the end I want to put it into a report in Visual Studio .Net i.e. Linking that report through a stored procedure in SQL Server. If I copy all this code to a SP, and dont put the data in a temporary table, the SP will only return the top rows and not the data from year 06/07 and 07/08.
All i can think of is creating a crosstab query, which I cant. So help would be much appreciated.
P.S. The data for Ethnic Group is coming from a view and that is joined which I forgot to mention here in the code.
Hi Guys,
Below is example of the current structure of table1 I have at run time:
------------------------
GroupName Resourcename Week1 Week2 ..cont.. dynamically
Associates A1 0 80 ......
Assocaites A2 20 40 ......
Associates A3 50 100 ......
Principal P1 20 100 ......
Principal P2 0 0 ......
Principal P3 0 100 ......
------------------------
I want to change the above to something like below table2:
---------------------------
GroupName Status Week1 Week2 ....cont
Associates Assigned 2 3
Associates NotAssigned 1 0
Principal Assigned 1 1
Principal NotAssigned 2 1
---------------------------
I will try to explain how I am deriving table2 from table1. I have to count the number of Resource name against each Groupname for a particular week column(Weeki i 1 to n dynamic) where value of Week column is 0, then use this numbber against NotAssigned and the complementary number to be stored as Assigned.
The table formaating is lost in HTML view but just consider any gaps between fields as next column value.
Am I clear in what i am asking , if not please ask me.
Any help will be highly appreciated.
In order to ask my question on a SQL query I will use a simplified version of a table I was struggling with...
¦¦¦¦SHOP¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦CARS
KILBURN MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦Mercedes
KILBURN MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦BMW
KILBURN MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦FIAT
BRIXTON AUTOMOBILE¦¦¦¦¦¦¦¦¦¦¦¦¦¦Mercedes
BRIXTON AUTOMOBILE¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦BMW
WEST HAMPSTEAD CARS¦¦¦¦¦¦¦¦¦¦¦¦¦Mercedes
CAMDEM MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦FIAT
NORTHERN LONDON CAR-STORE¦¦¦¦¦¦¦¦¦¦¦BMW
NORTHERN LONDON CAR-STORE¦¦¦¦¦¦¦¦¦¦¦FIAT
So my question is: how can I select only SHOPs which have no CARS variety (e.g. a variety =1)?
In other words I am looking for a Query that would give me this outcome:
¦¦¦¦SHOP¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦CARS
WEST HAMPSTEAD CARS¦¦¦¦¦¦¦¦¦¦¦¦¦Mercedes
CAMDEM MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦FIAT
...because WEST HAMPSTEAD CARS, selling Mercedes only, has a CARS variety equal to 1 as well as Camdem Motors which sells only FIAT.
I tried with this query:
SELECT DISTINCT SHOP, CARS
FROM CAR_SELLERS
GROUP BY SHOP, CARS
HAVING COUNT(CARS) = 1
But it doesn't work.
In addition I also would like to know for example how to create a similar query for a different desired CARS variety (e.g. 2, 3...)
How to do the tabs or attach images in the Post New Topic Message box... I wanted to attach a screen shot of the table but I was not able to and so, in order to post my question, I prepared a simplified version of the table plus I had to do the tabs/spaces manually with the symbol (¦)
hi all, i wasnt quite sure where to look to answer my particular problem so i am posting up this thread in the hopes that someone can point me in the right direction. in my report, i am showing sales figures for an area. i added a table to display sales from this year, sales from last year, and then the comparable percentage('this' divided by 'last' and then minus one). to account for some ppl who didnt have sales last year, i was able to use an IIF expression to return "N/A" in the textbox. my problem is i want the compared percentage to show for the area total, but not including ppl who were an 'N/A'. i am assuming that some sort of expression will be needed for the area total row, i.e. i want the area total to sum up this year and last year and then get the percentage, but i need to filter out the individuals who didnt have data for last year.
as an example
nsty nsly percentage
total: 15 7 X
A: 5 4 25%
B: 5 3 66%
C: 5 0 N/A
the percentage for total, X, should be (10/7)-1, with p0 as the format; right now it is summing rows A, B, and C.. how can i exclude row C from the calculation?
am i on the right track by researching filters and expressions, or is this a matter for the query
thanks in advance for any help
I have the following SELECT statement attached to a dropdown box:
SELECT [Workshop] FROM [Workshops] WHERE Workshop <> (SELECT Workshop FROM Workshop_Registration WHERE FullName = @FullName AND Completed = @Completed) ORDER BY [Workshop]
I am trying to get all workshops (50 or more) from the WORKSHOPS table that the logged in user is not already registered for. This works perfectly as long as the student is registered for at least 1 class. It populates the dropdown with all of the other classes. If they aren't registered for a class then it doesn't list any classes. The problem is definitely the subquery, but how do I make it to where if the subquery doesn't return any results (student not registered for anything), I get all of the workshops in the dropdown? Any help is appreciated!
MikeD
Hello,I have a problem the scenario is :I have data in an excel file and now I am reading data from that file and insert that data into sql database. this is well.but the problem is that I have few fields with date time data in excel sheet. In my database I have varchar type data type for these data columns.I want to read these data columns from the excel sheet and insert only time into the data base.how can I do this I am using the following line of code for selelcting only time from the excel file. string qry = "Select CONVERT(CHAR(5),datetime,114) from [" + objStr[0] + "];";this gives me error message.help me to read the data from excel file and insert it into the sql table in desired format. Thanks in advance, junior
View 1 Replies View RelatedDear Friend,
How to select a desired sequence of records without where condition?
For example I want to select the first 100 records I am using
SELECT TOP 100 * FROM EMPLYEETB;
Like this I want to select the records starts from 101 to 200.
Is it possible? If yes how?
Thanks in advance.
Lovingly,
Meyyarasu
I know of several methods to remove duplicate records but I recently encountered a unique situation where some duplicate records were actually acceptable.
Here is my situation:
I have a table that contains records of individuals who have children so 1 person can have 3 children with different birthdates; but there is also a field that has a specified language. The challenge arises when an individual may have only 1 child but has entered a single record twice. Once with a specifed language and again without which produces a default value of UNKNOWN.
I need to be able to remove this record without affecting records that may have a record entered twice as well; having 2 children for instance but they may also have a specified language in one record but a default value of UNKNOWN for their second record.
So I can't eliminate the unwanted duplicates by filtering out records that have UNKNOWN because I would also remove individuals that I need.
EX:
firstN | lastN | address | lang | childs birthdate
John Doe 210 Somewhere Ave ENG 1993-10-09
John Doe 210 Somewhere Ave UNK 1993-10-09
Jane Doe 210 Anywhere Ave ENG 1969-12-23
Jane Doe 210 Anywhere Ave UNK 1958-04-15
How could you remove the duplicate for John in this example without affecting Janes duplicate record which is actually ok because she apparently has 2 children with different birthdates whereas John's duplicate record is obviously created because it was entered twice; once without entering
a language and the second time specifying the language?
I have tried a number of things short of creating a cursor which isn't really the best way to resolve this issue since there are millions of rows.
Anyone out there have any input that be helpful? Or has anyone ever had this similar issue? I would be interested in knowing how you addressed the problem.
Any help is appreciated. :)
I have to use a Group By in Sub query that will give me my desired results.
SELECT count(hc), CS, S_W, (SELECT COUNT(Reg_Temp)
FROM CS_S
WHERE Reg_Temp = "Reg") as [RegOnly]
FROM CS_S
GROUP BY S_W, CS
ORDER By CS, S_W
The query I have above counts ALL the Reg, I'd want it to give only the count that I'd get by using Group By clause i.e. S_W and CS.
I have a column that contains the follwoing string I need to compare.
ek/df/cv/
ek/df/cv/f
All fields bfore the third / are not fixed but behind the third/ is eiter nothing or one letter I need a function to extract all the fields before the third / to compare if they are equal.
I can't do it by using the combination of Substring() and charindex() and Len()
[Microsoft follow-up]
Hello,
I think I've found a bug.
I have an expression set on the 'Disable' property of a data-flow task. When the expression evaluates to true the data-flow still executes. That seems wrong to me.
I ahve a demo package here: http://folders.live.com/self.aspx/VQ9oHa1TJjc/Public/SSIS%20Stuff/ExamplePackages/20070717%20Expression%20on%20Disable%20property%20has%20no%20effect/Package1.zip
Please reply letting me know whether this is indeed a bug or not.
I'm on SP1 by the way.
Regards
Jamie
Can we bulk insert only the desired column from a flat file to a table?
I am using SSIS to bulk insert from a file with more than 200 columns. I am trying to find a way I can bulk insert them to multiples table through SSIS.
The one way I can think is pre map the columns from the file to the destination tables. Build numerous Bulk Insert tasks to achieve that. But not sure if SSIS will let me do that.
While using Aggregate Transformation to group one column,the rows of output sometimes larger than the rows returned by a T-SQL statement via SSMS.
For example,the output of the Aggregate Transformation may be 960216 ,but the
'Select Count(Orderid) From ... Group By ***' T-SQL Statement returns 96018*.
I'm sure the Group By of the Aggregate Transformation is right!
But ,when I set the "keyscale" property of the transformation,the results match!
In my opinion,the "keyscale" property will jsut affects the performance of the transformaiton,but not the result of the transformation.
Thanks for your advice.
Inside some TSQL programmable object (a SP/or a query in Management Studio)I have a parameter containing the name of a StoreProcedure+The required Argument for these SP. (for example it's between the brackets [])
EX1 : @SPToCall : [sp_ChooseTypeOfResult 'Water type']
EX2 : @SPToCall : [sp_ChooseTypeOfXMLResult 'TABLE type', 'NODE XML']
EX3 : @SPToCall : [sp_GetSomeResult]
I can't change thoses SP, (and i don't have a nice output param to cach, as i would need to change the SP Definition)All these SP 'return' a 'select' of 1 record the same datatype ie: NVARCHAR. Unfortunately there is no output param (it would have been so easy otherwise. So I am working on something like this but I 'can't find anything working
DECLARE @myFinalVarFilledWithCachedOutput
NVARCHAR(MAX);
DECLARE @SPToCall NVARCHAR(MAX) = N'sp_ChooseTypeOfXMLResult
''TABLE type'', ''NODE XML'';'
DECLARE @paramsDefintion = N'@CatchedOutput NVARCHAR(MAX) OUTPUT'
[code]...
Hi
I have Lookup task to determine if source data should be updated to or insert to the customer table. After Lookup task, the Error Output pipeline will redirect to insert new data to the table and the Output pipeline will update customer table. But these two tasks will be processing at the same time which causes stall on the process. Never end.....
The job is similiart to what Slow Changing Dimention does but it won't update the table at the same time.
What can I do to avoid such situation?
Thanks in advance,
JD