Saturday, June 29, 2013

[SQL Server 2008 issues] sqlcmd

[SQL Server 2008 issues] sqlcmd

sqlcmd

Posted: 28 Jun 2013 06:42 PM PDT

Hi All,I am a newbie to sqlserver.I recently came across "sqlcmd"utility feature while surfing.So here is my doubt.I connect to a server using some credentials.I do it like --->Right click on ssms--->Run as different user--->give some credentials(domain/password)How can I do the same using command prompt..When do we actually use "sqlcmd"feature.Please help me on this.It would be helpful

Replication Issue: Multiple Publishers, One Subscriber, Same Schema..Please Help!

Posted: 28 Jun 2013 01:18 AM PDT

Hello,Am relatively new to Replications as such, the scenario I am dealing with is like as follows:1. I am having multiple Databases say 10 for e.g. having same structure(exactly same objects tables,sp's etc) as such. Thus in each Database there is this one table with same schema say Friends.2. Table Friends has following columns : Column Name | Type ID | int(pk,not null) FirstName | varchar(100, not null) MiddleName | varchar(100, not null) LastName | varchar(100, not null) IDProblem | int(not null)3. I want to replicate this Friends table from all the 10 Databases to one central Subscriber.4. I am using Transactional Publication to achieve the same, while creating the Publication for each Database, I change the article properties and set the Destination Object Name relevant to the Database and I also change the procedure names that it will render accordingly to prevent them from being overwritten. Thus for 10 databases I replicate the Friends table into 10 different tables in the subscriber and I have 10 sets of those msins,msupdate,msdelete procedures respectively. The whole reason for doing this is to know from which Database I actually get the data5. I set an After Insert trigger on each of these Tables at the subscriber and there I actually insert all these columns into a FinalFreinds table along with the Database name from where it came hardcoded according to the table name.6. I have mentioned this IDProblem column of type int in the schema above, which is replicated fine alright in each individual table, but the after trigger messes up with the column, what happens is sometimes I get the correct value for that column, and most other times it inserts 0 there, however the value in the replicated table was a non zero.7. Means I get a value of 10 in the IDProblem column once, trigger fires, inserts everything fine including this value 10, and the immediate next record which gets replicated also has the value 10, my trigger inserts proper values for all other columns but this column although it was 10 again, the trigger inserts 0. This happens randomly, sometimes the correct value is inserted and most of the times 0 is inserted.So now I have to solve this mess, firstly I would like to ask that can't I replicate all in one table at the subscriber, with one additional column DatabaseName which will tell me from which database it is coming from, this will solve all my problem as this is the only reason why I am having the triggers on each individual table.If not this, then how can I fix the trigger, for it is not the case that trigger isn't working, it works for rest of the columns everytime, and for this IDProblem column sometimes, so I cannot figure out what is going wrong.Please help me as I need to close this issue ASAP...:crying:

Error with stuff function; its cutting of my commands.

Posted: 28 Jun 2013 08:55 AM PDT

Hi, I am having an issue with the stuff function. I want to concatenate the strings but some the last string is being cut off and I know there is to be more commands than what is in the output screen. Here is my code. Im basically checking a database file to run updates deletes and inserts on another database file.Im using SQL Server 2008.The database file that contains the instructions to run the commands is CY1Errors. The other database files that are to be affected by the EDUCATION_CY1FOURTEEN_ALL_CY1HH_LISTING_CY1HH_ALL_CY1[code="sql"]Declare @SQL varchar(max)--First is the updatesselect @SQL = stuff (( select 'Update dbo.' + case when ERRORMESSAGE LIKE 'ED%' and CorrectedValue <> 'NULL' then 'EDUCATION_CY1' when ERRORMESSAGE LIKE 'O14%' and CorrectedValue <> 'NULL' then 'FOURTEEN_ALL_CY1' when ERRORMESSAGE LIKE 'HL%' and CorrectedValue <> 'NULL' then 'HH_LISTING_CY1' when ERRORMESSAGE LIKE 'HL%'and CorrectedValue <> 'NULL' then 'HH_ALL_CY1' end + ' set ' + ColumnsToFix + ' = ''' + CorrectedValue + ''' where zBarcode = ' + cast(zBarcode as varchar(20)) + ' and PERSON_NUMBER = ' + cast(Person_No as varchar(20)) + ';'+ CHAR(13) as MyVal from CY1Errors where Command = 'UPDATE' for XML PATH(''), type).value('.','varchar(max)'), 1, 0, '')--select @SQL--First is the updatesselect @SQL = @SQL + stuff (( select 'Update dbo.' + case when ERRORMESSAGE LIKE 'ED%' then 'EDUCATION_CY1' when ERRORMESSAGE LIKE 'O14%' then 'FOURTEEN_ALL_CY1' when ERRORMESSAGE LIKE 'HL%' then 'HH_LISTING_CY1' when ERRORMESSAGE LIKE 'HL%' then 'HH_ALL_CY1' end + ' set ' + ColumnsToFix + ' = NULL' + + ' where zBarcode = ' + cast(zBarcode as varchar(20)) + ' and PERSON_NUMBER = ' + cast(Person_No as varchar(20)) + ';'+ CHAR(13) as MyVal from CY1Errors where Command = 'UPDATE' and CorrectedValue = 'NULL' for XML PATH(''), type).value('.','varchar(max)'), 1, 0, '')--select @SQL--Now we append the deletesselect @SQL = @SQL + stuff (( select 'DELETE dbo.' + case when ERRORMESSAGE LIKE 'ED%' then 'EDUCATION_CY1' when ERRORMESSAGE LIKE 'O14%' then 'FOURTEEN_ALL_CY1' when ERRORMESSAGE LIKE 'HL%' then 'HH_LISTING_CY1' when ERRORMESSAGE LIKE 'HL%' then 'HH_ALL_CY1' end + ' where zBarcode = ' + cast(zBarcode as varchar(20)) + ' and PERSON_NUMBER = ' + cast(Person_No as varchar(20)) + ';'+ CHAR(13) as MyVal from CY1Errors where Command = 'DELETE' for XML PATH(''), type).value('.','varchar(max)'), 1, 0, '')--Now we append the insertsselect @SQL = @SQL + stuff (( select 'INSERT INTO dbo.' + case when ERRORMESSAGE LIKE 'ED%' then 'EDUCATION_CY1' + +' VALUES('+ coalesce(CAST(ED_THREE AS varchar(10)),'99999') + ',' + coalesce(CAST(ED_Four AS varchar(10)),'99999') + ',' + coalesce(CAST(ED_Five AS varchar(10)),'99999') + ',' + coalesce(CAST(ED_Six AS varchar(10)),'99999')+ ',' + coalesce(CAST(Person_No AS varchar(7)),'99999') + ',' + coalesce(CAST(zBarcode AS varchar(15)),'99999')+ ')' /*when ERRORMESSAGE LIKE 'O14%' then 'FOURTEEN_ALL_CY1' + ' VALUES('+ coalesce(CAST(BatchNo AS varchar(7)),'99999') + ' , ' + coalesce(CAST(zBarcode AS varchar(15)),'99999') + ' , ' + coalesce(CAST(HHBarcode AS varchar(7)),'99999') + ' , ''' + coalesce(CAST(Person_No AS varchar(7)),'99999') + ''' , ' + coalesce(CAST(District AS varchar(7)),'99999') + ' , '+ coalesce(CAST(URBAN_RURAL AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Cluster AS varchar(7)),'99999') + ' , ' + coalesce(CAST(HHNUM AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Final_Result_Code AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_5 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_5_KNS AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_7 AS varchar(7)),'99999') + ' , '''+ coalesce(CAST(Q1_8 AS varchar(7)),'99999') + ''' , '+ coalesce(CAST(Q1_9 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_9_2 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_10a AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_10b AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_10c AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_10_Total AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_11 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_12 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_13 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_14 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_15 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_15a AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_16 AS varchar(7)),'99999') + ' , ''' + coalesce(CAST(Q1_17 AS varchar(7)),'99999') + ''' , ' + coalesce(CAST(Q1_18 AS varchar(7)),'99999') + ' , '+ coalesce(CAST(Q1_19 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_20 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_21 AS varchar(7)),'99999') + ' , ''' + coalesce(CAST(Q1_22 AS varchar(7)),'99999')+ ''' , ''' + coalesce(CAST(Q1_23 AS varchar(7)),'99999') + ''' , ' + coalesce(CAST(Q1_24 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_25 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_26 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_27 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_28_MAIN AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_28_OTHER AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_28_PREVIOUS AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_29_SEASONAL AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_29_YEARROUND AS varchar(7)),'99999') + ' , '''+ coalesce(CAST(Q1_30_MAIN AS varchar(7)),'99999') +''' , ''' + coalesce(CAST(Q1_30_OTHER AS varchar(7)),'99999') + ''' , ''' + coalesce(CAST(Q1_30_PREVIOUS AS varchar(7)),'99999')+ ''' , ''' + coalesce(CAST(Q1_31_MAIN AS varchar(7)),'99999') + ''' , ''' + coalesce(CAST(Q1_31_OTHER AS varchar(7)),'99999') + ''' , '''+ coalesce(CAST(Q1_31_Previous_AJ AS varchar(7)),'99999')+ ''' , ' + coalesce(CAST(Q1_32_MAIN AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_32_OTHER AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_32_TOTAL AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_33_MAIN AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_33_OTHER AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_33_TOTAL AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_35 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_37 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_38 AS varchar(7)),'99999') + ' , ''' + coalesce(CAST(Q1_39 AS varchar(7)),'99999') + ''' , ''' + coalesce(CAST(Q1_40 AS varchar(7)),'99999') + ''' , ' + coalesce(CAST(Q1_41 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_41_DKNS AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_41a AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_41b AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_41b_DKNS AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_42 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_42_DKNS AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_43 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_44 AS varchar(7)),'99999') + ')' */ when ERRORMESSAGE LIKE 'HL%' then 'HH_LISTING_CY1'+ ' VALUES('''+ coalesce(CAST(Person_No AS varchar(7)),'99999') + ''' , ' + coalesce(CAST(HL_Three AS varchar (10)),'99999') + ' , ' + coalesce(CAST(HL_Four AS varchar (10)),'99999') + ' , ' + coalesce(CAST(HL_Five AS varchar(10)),'99999') + ' , '+ coalesce(CAST(HL_Six AS varchar(10)),'99999')+ ' , ' + coalesce(CAST(HL_Seven AS varchar (10)),'99999') + ' , ' + coalesce(CAST(HL_SevenB AS varchar (10)),'99999') + ' , ' + coalesce(CAST(zBarcode AS varchar(15)),'99999') + ' )' when ERRORMESSAGE LIKE 'HL%' then 'HH_ALL_CY1'+ ' VALUES('+ coalesce(CAST(BatchNo AS varchar(8)),'99999') + ' , ' + coalesce(CAST(Cluster AS varchar (10)),'99999') + ' , ' + coalesce(CAST(HHNUM AS varchar(10)),'99999')+ ' , ' + coalesce(CAST(zBarcode AS varchar(15)),'99999') + ' , ' + coalesce(CAST(District AS varchar(10)),'99999')+ ' , ' + coalesce(CAST(URBAN_RURAL AS varchar(10)),'99999') + ' , ' + coalesce(CAST(Final_Result_Code AS varchar(10)),'99999') + ' , ' + coalesce(CAST(Person_No AS varchar(7)),'99999') + ' )' end + ';' + CHAR(13) as MyVal from CY1Errors where Command = 'INSERT' for XML PATH(''), type).value('.','varchar(max)'), 1, 0, '') ;print @SQL--select @SQLexec (@SQL)[/code]In the output screen the following is shown[code="plain"]Update dbo.EDUCATION_CY1 set ED_THREE = NULL where zBarcode = 133593 and PERSON_NUMBER = 5;Update dbo.EDUCATION_CY1 set ED_THREE = NULL where zBarcode = 135443 and PERSON_NUMBER = 2;Update dbo.EDUCATION_CY1 set ED_THREE = NULL where zBarcode = 135153 and PERSON_NUMBER = 4;Update dbo.EDUCATION_CY1 set ED_FOUR = NULL where zBarcode = 135153 and PERSON_NUMBER = 4;Update dbo.EDUCATION_CY1 set ED_FIVE = NULL where zBarcode = 135153 and PERSON_NUMBER = 4;Update dbo.EDUCATION_CY1 set ED_THREE = NULL where zBarcode = 135092 and PERSON_NUMBER = 7;Update dbo.EDUCATION_CY1 set ED_FOUR = NULL where zBarcode = 135092 and PERSON_NUMBER = 7;Update dbo.EDUCATION_CY1 set ED_FIVE = NULL where zBarcode = 135092 and PERSON_NUMBER = 7;Update dbo.EDUCATION_CY1 set ED_THREE = NULL where zBarcode = 135092 and PERSON_NUMBER = 8;Update dbo.EDUCATION_CY1 set ED_FOUR = NULL where zBarcode = 135092 and PERSON_NUMBER = 8;Update dbo.EDUCATION_CY1 set ED_FIVE = NULL where zBarcode = 135092 and PERSON_NUMBER = 8;Update dbo.EDUCATION_CY1 set ED_THREE = NULL where zBarcode = 134996 and PERSON_NUMBER = 3;Update dbo.EDUCATION_CY1 set ED_FOUR = NULL where zBarcode = 134996 and PERSON_NUMBER = 3;Update dbo.EDUCATION_CY1 set ED_FIVE = NULL where zBarcode = 134996 and PERSON_NUMBER = 3;Update dbo.EDUCATION_CY1 set ED_THREE = NULL where zBarcode = 134095 and PERSON_NUMBER = 3;Update dbo.EDUCATION_CY1 set ED_FOUR = NULL where zBarcode = 134095 and PERSON_NUMBER = 3;Update dbo.EDUCATION_CY1 set ED_FIVE = NULL where zBarcode = 134095 and PERSON_NUMBER = 3;Update dbo.EDUCATION_CY1 set ED_THREE = NULL where zBarcode = 134095 and PERSON_NUMBER = 4;Update dbo.EDUCATION_CY1 set ED_FOUR = NULL where zBarcode = 134095 and PERSON_NUMBER = 4;Update dbo.EDUCATION_CY1 set ED_FIVE = NULL where zBarcode = 134095 and PERSON_NUMBER = 4;Update dbo.EDUCATION_CY1 set ED_THREE = NULL where zBarcode = 134095 and PERSON_NUMBER = 5;Update dbo.EDUCATION_CY1 set ED_FOUR = NULL where zBarcode = 134095 and PERSON_NUMBER = 5;Update dbo.EDUCATION_CY1 set ED_FIVE = NULL where zBarcode = 134095 and PERSON_NUMBER = 5;Update dbo.EDUCATION_CY1 set ED_THREE = NULL where zBarcode = 134095 and PERSON_NUMBER = 6;Update dbo.EDUCATION_CY1 set ED_FOUR = NULL where zBarcode = 134095 and PERSON_NUMBER = 6;Update dbo.EDUCATION_CY1 set ED_FIVE = NULL where zBarcode = 134095 and PERSON_NUMBER = 6;Update dbo.EDUCATION_CY1 set ED_THREE = NULL where zBarcode = 134095 and PERSON_NUMBER = 7;Update dbo.EDUCATION_CY1 set ED_FOUR = NULL where zBarcode = 134095 and PERSON_NUMBER = 7;Update dbo.EDUCATION_CY1 set ED_FIVE = NULL where zBarcode = 134095 and PERSON_NUMBER = 7;Update dbo.EDUCATION_CY1 set ED_THREE = NULL where zBarcode = 134095 and PERSON_NUMBER = 8;Update dbo.EDUCATION_CY1 set ED_FOUR = NULL where zBarcode = 134095 and PERSON_NUMBER = 8;Update dbo.EDUCATION_CY1 set ED_FIVE = NULL where zBarcode = 134095 and PERSON_NUMBER = 8;Update dbo.EDUCATION_CY1 set ED_THREE = NULL where zBarcode = 133777 and PERSON_NUMBER = 3;Update dbo.EDUCATION_CY1 set ED_FOUR = NULL where zBarcode = 133777 and PERSON_NUMBER = 3;Update dbo.EDUCATION_CY1 set ED_FIVE = NULL where zBarcode = 133777 and PERSON_NUMBER = 3;Update dbo.EDUCATION_CY1 set ED_THREE = NULL where zBarcode = 133821 and PERSON_NUMBER = 3;Update dbo.EDUCATION_CY1 set ED_THREE = NULL where zBarcode = 135153 and PERSON_NUMBER = 4;Update dbo.EDUCATION_CY1 set ED_FOUR = NULL where zBarcode = 135153 and PERSON_NUMBER = 4;Update dbo.EDUCATION_CY1 set ED_FIVE = NULL where zBarcode = 135153 and PERSON_NUMBER = 4;Update dbo.EDUCATION_CY1 set ED_THREE = NULL where zBarcode = 135092 and PERSON_NUMBER = 7;Update dbo.EDUCATION_CY1 set ED_FOUR = NULL where zBarcode = 135092 and PERSON_NUMBER = 7;Update dbo.EDUCATION_CY1 set ED_FIVE = NULL where zBarcode = 135092 and PERSON_NUMBER = 7;Update dbo.EDUCATION_CY1 set ED_THREE = NULL where zBarcode = 135092 and PERSON_NUMBER = 8;Update dbo.EDUCATION_CY1 set ED_FOUR = NULL where zBarcode = 135092 and PERSON_NUMBER = 8;Update dbo.EDUCATION_CY1 set ED_FIVE = NULL where zBarcode = 135092 and PERSON_NUMBER = 8;Update dbo.EDUCATION_CY1 set ED_THREE = NULL where zBarcode = 134996 and PERSON_NUMBER = 3;Update dbo.EDUCATION_CY1 set ED_FOUR = NULL where zBarcode = 134996 and PERSON_NUMBER = 3;Update dbo.EDUCATION_CY1 set ED_FIVE = NULL where zBarcode = 134996 and PERSON_NUMBER = 3;Update dbo.EDUCATION_CY1 set ED_THREE = NULL where zBarcode = 134095 and PERSON_NUMBER = 3;Update dbo.EDUCATION_CY1 set ED_THREE = NULL where zBarcode = 134095 and PERSON_NUMBER = 4;Update dbo.EDUCATION_CY1 set ED_THREE = NULL where zBarcode = 134095 and PERSON_NUMBER = 5;Update dbo.EDUCATION_CY1 set ED_THREE = NULL where zBarcode = 134095 and PERSON_NUMBER = 6;Update dbo.EDUCATION_CY1 set ED_THREE = NULL where zBarcode = 134095 and PERSON_NUMBER = 7;Update dbo.EDUCATION_CY1 set ED_THREE = NULL where zBarcode = 134095 and PERSON_NUMBER = 8;Update dbo.EDUCATION_CY1 set ED_THREE = NULL where zBarcode = 133777 and PERSON_NUMBER = 3;Update dbo.EDUCATION_CY1 set ED_THREE = NULL where zBarcode = 133821 and PERSON_NUMBER = 3;Update dbo.HH_LISTING_CY1 set HL_FIVE = NULL where zBarcode = 133289 and PERSON_NUMBER = 1;Update dbo.HH_LISTING_CY1 set HL_FIVE = NULL where zBarcode = 133289 and PERSON_NUMBER = 2;Update dbo.HH_LISTING_CY1 set HL_FIVE = NULL where zBarcode = 133289 and PERSON_NUMBER = 3;Update dbo.HH_LISTING_CY1 set HL_FIVE = NULL where zBarcode = 133289 and PERSON_NUMBER = 4;Update dbo.HH_LISTING_CY1 set HL_FIVE = NULL where zBarcode = 133289 and PERSON_NUMBER = 5;Update dbo.HH_LISTING_CY1 set HL_FIVE = NULL where zBarcode = 133289 and PERSON_NUMBER = 6;Update dbo.HH_LISTING_CY1 set HL_FIVE = NULL where zBarcode = 133418 and PERSON_NUMBER = 1;Update dbo.HH_LISTING_CY1 set HL_FIVE = NULL where zBarcode = 133418 and PERSON_NUMBER = 2;Update dbo.HH_LISTING_CY1 set HL_FIVE = NULL where zBarcode = 133418 and PERSON_NUMBER = 3;Update dbo.HH_LISTING_CY1 set HL_FIVE = NULL where zBarcode = 133418 and PERSON_NUMBER = 4;Update dbo.HH_LISTING_CY1 set HL_FIVE = NULL where zBarcode = 133418 and PERSON_NUMBER = 5;Update dbo.HH_LISTING_CY1 set HL_FIVE = NULL where zBarcode = 133340 and PERSON_NUMBER = 1;Update dbo.HH_LISTING_CY1 set HL_FIVE = NULL where zBarcode = 133340 and PERSON_NUMBER = 2;Update dbo.HH_LISTING_CY1 set HL_FIVE = NULL where zBarcode = 133340 and PERSON_NUMBER = 3;Update dbo.HH_LISTING_CY1 set HL_FIVE = NULL where zBarcode = 133258 and PERSON_NUMBER = 5;Update dbo.HH_LISTING_CY1 set HL_SEVEN = NULL where zBarcode = 133289 and PERSON_NUMBER = 4;Update dbo.HH_LISTING_CY1 set HL_SEVEN = NULL where zBarcode = 133289 and PERSON_NUMBER = 3;Update dbo.HH_LISTING_CY1 set HL_THREE = NULL where zBarcode = 133920 and PERSON_NUMBER = 1;Update dbo.HH_LISTING_CY1 set HL_THREE = NULL where zBarcode = 133920 and PERSON_NUMBER = 2;Update dbo.HH_LISTING_CY1 set HL_THREE = NULL where zBarcode = 133920 and PERSON_NUMBER = 3;Update dbo.HH_LISTING_CY1 set HL_SEVEN = NULL where zBarcode = 133289 and PERSON_NUMBER = 4;Update dbo.HH_LISTING_CY1 set HL_SEVEN = NULL where zBarcode = 133289 and PERSON_NUMBER = 3;Update dbo.HH_LISTING_CY1 set HL_SEVEN = NULL where zBarcode = 133708 and PERSON_NUMBER = 2;Update dbo.HH_LISTING_CY1 set HL_THREE = NULL where zBarcode = 133708 and PERSON_NUMBER = 2;Update dbo.HH_LISTING_CY1 set HL_SEVEN = NULL where zBarcode = 134040 and PERSON_NUMBER = 2;Update dbo.HH_LISTING_CY1 set HL_THREE = NULL where zBarcode = 134040 and PERSON_NUMBER = 2;Update dbo.HH_LISTING_CY1 set HL_SEVEN = NULL where zBarcode = 133715 and PERSON_NUMBER = 2;Update dbo.HH_LISTING_CY1 set HL_THREE = NULL where zBarcode = 133715 and PERSON_NUMBER = 2;Update dbo.HH_LISTING_CY1 set HL_SEVEN = NULL where zBarcode = 134828 and PERSON_NUMBER = 2;Update dbo.HH_LISTING_CY1 set HL_SEVEN = NULL where zBarcode = 134828 and PERSON_NUMBER = 1;Update dbo.FOURTE[/code]However when I check there are rows missing so I know somehow the stuff is cutting off the statements.Please any assistance will be appreciated. I am really stuck.

Massive slowdown in SELECT statement in Cursor from SQL 2000 to 2008 -- need help!

Posted: 28 Jun 2013 04:46 AM PDT

I have a SELECT statement in a cursor in a stored procedure that ran extremely fast in SQL Server 2000. I just migrated our database to SQL Server 2008 R2 (SP2) for a customer, and the same SELECT now takes fifty times longer to execute. I have logging that measure this performance, which is how I found out. The database on the new 2008 R2 server is set to 2008 compatibility. The slow performance occurs in both 2000 and 2008 compatibility mode. Some notes: I need to keep the SELECT statement inside the cursor. It calculates the number of cross sales for an employee in a given day, which is then used to figure out what incentive earnings they get for the cross sales. The SVAccountsHistory table has three million rows; SVCrossSales has 16,000; SVSales has 74,000; SVAccounts has 90,000. The joins between table use the primary keys in these tables. I have a number of other SELECT statements that run just fine for the incentive calculations that I do elsewhere in the stored procedure, so it's just this SELECT in particular. What I'm guessing is the problem in SQL 2008 is that it doesn't like the DATEDIFF compare or the COUNT(SELECT DISTINCT xxx) calculation. It's extremely maddening, I have to say. I'm at my wit's end, and desperately need to figure out what SQL 2008 is choking on in comparison to SQL 2000. Thank you for your help!Here's the SELECT statement:[quote]SELECT @tmpCalcValue1 = COUNT(DISTINCT SVCrossSalesHistory.SalesNum) * @EmpRuleValue1FROM SVCrossSalesHistory INNER JOIN SVSales ON ( SVCrossSalesHistory.INum = SVSales.INum AND SVCrossSalesHistory.SalesNum = SVSales.SalesNum AND SVSales.SaleDate = @tmpDate AND -- Go back to the day of consideration SVSales.OrgNum = @OrgNum AND -- Selected emp SVSales.DeleteFlg = 'N') INNER JOIN SVGoalProdXref ON ( SVSales.INum = SVGoalProdXref.INum AND SVSales.ProdNum = SVGoalProdXref.ProdNum AND SVGoalProdXref.GoalNum = @GoalNum AND -- Go against all the prods for the selected goal SVGoalProdXref.DeleteFlg = 'N') INNER JOIN SVAccounts ON ( SVSales.INum = SVAccounts.INum AND SVSales.FullAcctNum = SVAccounts.FullAcctNum AND ( -- If the account was closed, determine if the minimum # active days was met during the life of the account DATEDIFF(DD,OpenDate, CASE WHEN CloseDate = '1/1/1900' THEN NULL ELSE CloseDate END) > @EmpRuleValue2 OR -- @EmpRuleValue2 = 'x # days active' DATEDIFF(DD,OpenDate, CASE WHEN CloseDate = '1/1/1900' THEN NULL ELSE CloseDate END) IS NULL)) INNER JOIN SVAccountsHistory ON ( SVAccounts.INum = SVAccountsHistory.INum AND SVAccounts.FullAcctNum = SVAccountsHistory.FullAcctNum AND SVAccountsHistory.HistoryDate = @StartTime AND -- Today SVAccountsHistory.Balance > ISNULL(@EmpRuleValue5,0)) -- '$' value in 'balance > $'WHERE SVCrossSalesHistory.INum = @INum AND SVCrossSalesHistory.CSFlg = 'Y' AND -- Must be a cross sale SVCrossSalesHistory.IsNewCustFlg = 'Y' -- Consider new customers only[/quote]

Group every X rows up to Y

Posted: 27 Jun 2013 09:24 PM PDT

Dear SQL Experts,I need to housekeep a large table basically composed by an ID and a creation date (not timestamp) columns. However, I don't have direct write permissions on the database, so I need to use an batch job program to do it. This program accepts as a parameter the maximum retention days (i.e., it will keep all data from today minus X days and delete everything older).StoreID; Date123; 01/18/2013124; 01/18/2013125; 01/18/2013126; 01/19/2013127; 01/19/2013128; 01/19/2013...The application server has memory limitation, so I need to set the retention days parameter gradually. I ran some tests that showed that the batch job can load and delete 250,000 rows.Therefore, I need to know how I could group the rows in groups up to 250,000 and know the finish (max) day of each group. Could you please help me?Thanks in advance.Best Regards,Darthurfle

Named Instance SQL2008R2

Posted: 28 Jun 2013 07:37 AM PDT

I am trying to install SQL2008R2 enterprize version on server 2. Can I use the existing Instance name ( suppose INST01 is already exist in Server1) Because when I am trying to use the same instance during installation I am getting this error 'access denied' when the user has the local admin access.

Using RAISERROR with Dynamic SQL

Posted: 28 Jun 2013 04:16 AM PDT

Can anyone provide me a short example of how to implement this? I am not able to do this and need to.Any and all help will be greatly appreciated!! Back to Google!

Same two select statments show different sort order on a result

Posted: 28 Jun 2013 06:21 AM PDT

Hello thereI have same query for running on two different database servers with identical views, the views have same datawhen I execute the query, on one server it returns the first column on ascending order(A_Id). the same query returns unsorted on a different server on the same viewSelect count(distinct Acc_nt.A_Id) as AliasOne, Acc_nt.A_ID as AliasTwo From Acc_nt Where Acc_nt.StateNum=1 and caseyear=2013 group by Acc_nt.AccIDAcc_nt is Viewwhat do you think is the problem.

Restore a msdb database to a different server - sql server 2008 R2

Posted: 28 Jun 2013 05:33 AM PDT

Hi, We are currently setting up a new test environment which will be a exact copy of our production server. is it possible to take a backup of msdb database from the source server and restore it to the new destination server? I am thinking of this to import all the jobs from old server to the new one. To my knowledge this can be done as I will have same versions of SQL servers. can somebody help me to understand it better:-).

Some input needed on developing a process...

Posted: 28 Jun 2013 02:53 AM PDT

We have a lot of applications running in the background that constantly send the request to SQL tables in SQL server 2008, then we also have about 100 users accessing a web application that has the same database as the backend.I would like to have some input on developing a process queue system based on CPU usage for the application request. For .e.g. an application A that uses intensive query checks the CPU usage and if the usage is hight it will not run, something like that.Any input would be greatly appreicated.

SQL 2008 - Migrating job schedules

Posted: 27 Jun 2013 10:29 PM PDT

How to move job schedules from sql 2000/sql2005 to sql 2008 server. I have moved the jobs (sysjobs).

SQLServer Replication The process could not connect to Distributor .

Posted: 26 Jun 2013 06:31 PM PDT

Good Day. We configured Pull Replication and I checked the number of rows being stored in the replicated tables and they are the numbers match. But when I look at the properties of the subscriptions , the abovementioned message is dispalyed. We use SQL Server Security . I was even able to sign onto the Distributor database using the same account. The account has all the required permissions and there is no entry in SQL's log that there are failed logins . Any ideas ?

Replicate pending transactions manually

Posted: 25 Jun 2013 09:27 PM PDT

Hi all, I have configured the transactional replication between two servers which is different locations, it was working fine till now, but becuase of connectivity issue the replication is stopped and the new transactions are not getting updated on subscriber, Is there any way I can manually replicate the pending transactions to subscriber, I can access this server by remote desktop / team viewer.Is there any option available that I can transfer the pending transaction into a file, and upload it on subscriber ?Please help...Thanks

Partitioning Existing table with Non clustered index on Date column

Posted: 25 Jun 2013 05:40 PM PDT

Hi All, I have one table with 10 lacks records. I partitioned that table on CreatedDate column with non clustered index( i am not removing clustered index on ID column, It is as part of primary key).It is inserting to data into relevant partition only. But i am verifying is that table partitioned or not by using below steps, in object Explorer-Database-->TestDB-->tables-->select partitioned table and Right click on table select properties --Storage [b]File Group= PrimaryTable Partitioned = False[/b]If create Partitioned with Clustered index , it is showing correctly [b] Table Partitioned = True[/b] But i am creating with non clustered.Can any one explainIs it table partitioned or not? and how to know data is coming from which partition (with out using ($partition)below are example table partition script which i followed steps for original table.CREATE TABLE tblPartition(ID int primary key identity(1,1),Name varchar(30),CreatedDate Datetime)insert into tblPartition(Name,CreatedDate)SELECT 'Name1','2013-05-26 13:53:47.650'union allselect 'Name2','2013-05-26 13:53:47.650'union allSELECT 'Name1','2013-06-26 13:53:47.650'union allselect 'Name2','2013-06-26 13:53:47.650'union allSELECT 'Name1','2013-07-26 13:53:47.650'union allselect 'Name2','2013-07-26 13:53:47.650'goCREATE PARTITION FUNCTION [PartitionFunction](datetime) AS RANGE RIGHT FOR VALUES (N'2013-05-31 23:59:59', N'2013-06-30 23:59:59', N'2013-07-31 23:59:59')CREATE PARTITION SCHEME [PartitionScheme] AS PARTITION [PartitionFunction] TO ([FGNdf10], [FGNdf11], [FGNdf12], [PRIMARY])CREATE NONCLUSTERED INDEX [IX_PartitionScheme_CreatedDate] ON [dbo].[tblPartition]( [CreatedDate])WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PartitionScheme]([CreatedDate])goselect * from tblPartitionwhere $partition.PartitionFunction(CreatedDate)=1goThanks,PRR

Index Rebuild Failed - Need More Information

Posted: 24 Jun 2013 12:24 AM PDT

I have an Index Rebuild that has been failing. It fails on the same table/index everytime. Looking at this history of the index job, I see entries like this, "Rebuild - [PK_CCX] [SQLSTATE 01000] (Message 0)" and then the very last entry is, "Rebuild - [PK_CCW_TOX] [SQLSTAT... The step failed." I am needing to find more information on this failure. I have checked the SQL Agent Error Logs and there is not a whole lot there, as in maybe 25 entries total. I then try the SQL Server Error Logs and search for the index and nothing comes back.I have queried the table to see if there is some data out of place for these two columns that make up the PK and everything seems correct. To add to this, the table is not very big (almost makes me wonder why it needs to be rebuild, but I can worry about that once I get this corrected).Can anyone point me in the right direction on where I can look to get more information on why this Index Rebuild is failing?Any and all advice on this subject will be greatly appreciated...Thank You.

DMV sys.dm_db_missing_index_group_stats returns no rows

Posted: 05 Oct 2011 10:31 AM PDT

Hi All,I am using a script to review missing indexes on various SQL Servers.Most of the time, it works just fine.Sometimes, however, certain DMVs that are part of the script, just don't return any rows.Usually, the main issue is sys.dm_db_missing_index_group_stats.1. I know that restarting the service clears DMVs - that's not it :-)2. I know I need the VIEW SERVER STATE permission - that's not it either :-)3. I know I need actual missing indexes:select * from sys.dm_db_missing_index_detailsreturns 573 rows, yet select TOP 1 * from sys.dm_db_missing_index_group_stats returns 0 rows.This renders all the various missing index scripts useless, because the JOIN doesn't work, and so the script doesn't return any results.I keep running into servers with this issue, and so far, only restarting the service has resolved it. Data collection starts from scratch, and within just a few minutes, I get data in the sys.dm_db_missing_index_group_stats. Check back a few days, weeks or sometimes months later, and the DMV again returns no results - same user, same DB, etc.I have Googled the heck out of this issue and can't find a solution. It is frustrating, since missing indexes are often a major issue of the application I am troubleshooting on many different SQL Servers.Any help would be greatly appreciated :-)

Can use clr function in select but not in update

Posted: 27 Jun 2013 08:33 PM PDT

I have a complicated clr function that does a formula calculation. The variable values of these formulas comes from different databases and that is the reason for the clr function.Now, with the following T_Sql:SELECT fncFormula(1, 2013, 6, 28) as ValueI get a result within 1 second.When I want to update a table with that result or just dump the result into a temp table, it takes about 2 minutes and then returns a value of 0.Now it seems that sql do not trust the result of the clr function, but what is the use if I can't use the value?The clr permission level is set to External and the database property Trustworty is on.Is there something else I am missing here?

Inserting Results from SPROC into table

Posted: 28 Jun 2013 12:55 AM PDT

I was able to finally develop this SPROC that runs fine and returns the results but i would like to add an INSERT INTO clause that will load the values into a table that i could use for additional querying and joins.Thoughts?DECLARE @Start_Date DATETIME = NULLDECLARE @Part_Type_MP VARCHAR(1000) = ''SET @Part_Type_MP = ',' + @Part_Type_MP + ','SET @Start_Date = ISNULL(@Start_Date, DATEADD(yy, 50, GETDATE()))SET @Start_Date = dateadd(ms,-3,dateadd(day,1,DATEADD(dd, DATEDIFF(dd,0,@Start_Date), 0)))DECLARE @sqlQuery NVARCHAR(MAX),@finalQuery NVARCHAR(MAX),@q CHAR(1)=''''SET @sqlQuery = N'SELECT ' + 'PLK.Part_no, ' +'PLK.line_item_key, ' + 'PLK.unit_price ' +'FROM ' +'(SELECT p.part_no, pli.unit_price, MAX(PLI.Line_Item_Key) AS [Line_Item_Key] ' + 'FROM Purchasing_v_Line_Item_e AS PLI ' + 'JOIN Part_v_Part_e AS P ' + 'ON p.plexus_customer_no = pli.plexus_customer_no ' + 'AND p.part_key = pli.part_key ' + 'WHERE pli.add_date <= ' + @q + @q + CONVERT(VARCHAR(30), @Start_Date,120)+ @q + @q + ' AND (' + @q + @q + @Part_Type_MP + @q + @q + ' = '''',,'''' OR (CHARINDEX('''','''' + CAST(P.Part_Type as VARCHAR(50)) + '''','''', ' + @q + @q + @Part_Type_MP + @q + @q + ') >0))' + 'GROUP by p.part_no, pli.unit_price ' + ') AS PLK'-- SET @finalQuery = N'INSERT INTO dbo.tblActualPrice ' + CHAR(10)SET @finalQuery = N'SELECT ' +'part_no AS Part, ' +'unit_price AS ActualCost ' +'FROM OPENQUERY (PLEXREPORTSERVER, ''' + @SQLQuery + ''')'EXEC (@finalQuery)

TSQL Challenge

Posted: 27 Jun 2013 08:39 PM PDT

[b]Taken from TSQL Challenge[/b]The challenge idea is taken from a problem discussed in the MSDN TSQL forum. The challenge is to find the Islands(gaps) in sequential dates. You need to write a query to identify continuous intervals from the start date and end date.For example,01/01/2012 - 01/17/201201/18/2010 - 02/20/2012The above two intervals should be considered as 01/01/2012 - 02/20/2012[b]Rules [/b]The output should be ordered by PatientID, AdmissionDate.For any patient there will be no overlapping date intervals.[b]Sample data[/b]There is a table which maintains the Patient admission and discharge information. Each admission comes as one new record. But when there is a continuous internal, you should show them as a single row in output[code="plain"]PatientID AdmissionDate DischargeDate Cost--------- ------------- ------------- -------709 2011-07-27 2011-07-31 450.00709 2011-08-01 2011-08-23 2070.00709 2011-08-31 2011-08-31 90.00709 2011-09-01 2011-09-14 1260.00709 2011-12-01 2011-12-31 2790.001624 2011-06-07 2011-06-28 1980.001624 2011-06-29 2011-07-31 2970.001624 2011-08-01 2011-08-02 180.00[/code][b]Expected Results[/b][code="plain"]PatientID AdmissionDate DischargeDate Cost--------- ------------- ------------- -------709 2011-07-27 2011-08-23 2520.00709 2011-08-31 2011-09-14 1350.00709 2011-12-01 2011-12-31 2790.001624 2011-06-07 2011-08-02 5130.00[/code][b]Sample Script[/b][code="sql"] IF OBJECT_ID('TC79','U') IS NOT NULL BEGIN DROP TABLE TC79ENDGOCREATE TABLE TC79( PatientID INT, AdmissionDate DATETIME, DischargeDate DATETIME, Cost MONEY)GOINSERT INTO TC79(PatientID,AdmissionDate,DischargeDate,Cost)SELECT 709,'2011-07-27','2011-07-31',450.00 UNION ALLSELECT 709,'2011-08-01','2011-08-23',2070.00 UNION ALLSELECT 709,'2011-08-31','2011-08-31',90.00 UNION ALLSELECT 709,'2011-09-01','2011-09-14',1260.00 UNION ALLSELECT 709,'2011-12-01','2011-12-31',2790.00 UNION ALLSELECT 1624,'2011-06-07','2011-06-28',1980.00 UNION ALLSELECT 1624,'2011-06-29','2011-07-31',2970.00 UNION ALLSELECT 1624,'2011-08-01','2011-08-02',180.00SELECT * FROM TC79GO[/code]

Need SQL Function ?

Posted: 27 Jun 2013 07:33 PM PDT

Dear all,Hope things are going well at your end.I need sql function for getting the following Scenarios:Input parameter : ShiftDateNeed to create function for Senario 11st day of the month – Till Date(Output:01-06-2013,02-06-2013,...28-06-2013)Senario 21st day of the week – Till Day(week start date and end date)(23-06-2013,29-06-2013)Senario 31st day of the year – Till Date(from 01-01-2013,02-01-2013,...01-02-2013,....28-06-2013)please help on this?

Updating huge table

Posted: 27 Jun 2013 10:24 PM PDT

Hi All,I have two tables i.e VoiceData, VoiceData_History , below are rows Count of the tables. VoiceData --230 millions (23 core)VoiceData_History --8 Millions (80 lacks)Now i want to update Archive_id column in VoiceData table based on VoiceData_History table below the update statement for updating the record Update VoiceData set Archive_id =VDH.Archive_id from VoiceData VD join VoiceData_History VDH onVD.ID=VDH.IDI have[b] "non clustered index on Archive_id "[/b] column and Clustered index on ID column in both tableNote: In both table Matched Data is 80 millions[b]What is best way for updating the those records. If i run above statement , it locking the data table.and it is production DB[/b]

Get min and max dates based on end flag

Posted: 26 Jun 2013 04:52 AM PDT

HiI have a result set which produces EndFlag for an activity.The resultset is like below.CustNo, ActivityStart, ActivityEndDate, ActivityEndActivityEnd is derived based on current ActivityEndDate and next row's ActivityStartDate is > 7 hours ActivityEnd = 1Now, I need to generate final resultset with CustNo, MIN(ActivityStartDate), MAX(ActivityEndDate) for each ActivityEnd = 1Means if the ActivityEnd = 1 then we need to MIN(ActivityStart) where ActivityStartDate <= ActivityEndDate of ActivityEnd = 1I am struggling to get this done.Can anyone give an exampleSample Resultset and expected output as below.CustNo ActivityStartDate ActivityEndDate DiffHours ActivityEnd42 12/21/2006 11:35 12/21/2006 13:40 1 042 12/21/2006 14:10 12/21/2006 16:30 1 042 12/21/2006 17:00 12/21/2006 18:15 0 042 12/21/2006 18:45 12/21/2006 20:00 33 142 12/23/2006 07:00 12/23/2006 10:00 0 042 12/23/2006 10:30 12/23/2006 13:35 15 142 12/24/2006 07:00 12/24/2006 10:00 0 042 12/24/2006 10:30 12/24/2006 13:35 1 042 12/24/2006 14:30 12/24/2006 19:30 5 042 12/25/2006 00:00 12/26/2006 00:00 0 1Need output asCustNo ActivityStartDate ActivityEndDate42 12/21/2006 11:35 12/21/2006 20:0042 12/23/2006 07:00 12/23/2006 13:3542 12/24/2006 07:00 12/26/2006 00:00
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog