Tuesday, July 23, 2013

[SQL Server 2008 issues] SQL 2008 R2 Build 10.50.1790.0

[SQL Server 2008 issues] SQL 2008 R2 Build 10.50.1790.0

SQL 2008 R2 Build 10.50.1790.0

Posted: 22 Jul 2013 04:34 AM PDT

MS11-049 is this a windows update. I can get sql to 1777 build. Using cu packs. How do I use the downloaded and installedSecurity Update for SQL Server 2008 R2 RTM (KB2494086 but it doesn't get me to the 1790 build.

How to find ladder in sql query

Posted: 22 Jul 2013 04:58 PM PDT

Hi, I have table like below col1 5 101520 I need result like Col1 Col25 05--910 10--1415 15--1920 20-24 Plz help Thank You

Execution Plan Changes With "TOP 1" And problem with the response time

Posted: 22 Jul 2013 12:15 AM PDT

Hi, I have a view like : [i]alter VIEW View_IdPERSON as select em.EMAIL_VALUE as liste_email ,e.PERSON_id as Liste_DMID ,ed.DATA_BIRTHDATE as Liste_DMDATENAISSANCE ,case when ed.CIVILITY_ID=1 then 'M' else 'F' end AS Liste_DMGENRE ,ed.DATA_FIRSTNAME as Liste_DMPRENOM ,ed.DATA_LASTNAME as Liste_DMNOM ,'' as Liste_DMADRESSEID ,a.ADDRESS_1 as Liste_DMADRESSE1 ,a.ADDRESS_2 as Liste_DMADRESSE2 ,cp.CP_VALUE as Liste_DMCODEPOSTAL ,a.ADDRESS_CITY as Liste_DMVILLE ,ISNULL(a.ADDRESS_COUNTRYISO, ed.DATA_COUNTRYISO) as Liste_DMPAYSISO2 ,ed.DATA_TELEPHONE as Liste_DMTELFIXE ,g.GSM_VALUE as Liste_DMTELMOBILE ,Case when ed.CIVILITY_ID IS null then '*' else CIVILITY_CODE end AS Liste_DMCIVILITE ,ed.DATA_INSCRIPTIONDATE as Liste_DMDATEMEMBRE ,ed.DATA_LOGIN as Liste_DMLOGIN ,ed.DATA_PWD as Liste_DMMOTDEPASSE ,'' as Liste_DMIDMEMBREWEB from dbo.PERSON as e with (nolock, index(IX_PERSON_id_cluster)) INNER JOIN dbo.EMAIL as em with (nolock,index(IX_emailvalue)) on e.EMAIL_ID = em.EMAIL_ID INNER JOIN ( select e.EMAIL_ID, e.PERSON_Lastmodificationdate, MAX(e.PERSON_ID) AS PERSON_ID from dbo.PERSON as e with (nolock) INNER JOIN ( select e.EMAIL_ID, MAX(e.PERSON_Lastmodificationdate) as Lastmodificationdate from dbo.PERSON as e with (nolock) INNER JOIN dbo.EMAIL as em with (nolock,index(IX_emailvalue)) on e.EMAIL_ID = em.EMAIL_ID group by e.EMAIL_ID ) as k on e.EMAIL_ID = k.EMAIL_ID and e.PERSON_Lastmodificationdate = k.Lastmodificationdate group by e.EMAIL_ID, e.PERSON_Lastmodificationdate ) as t on e.PERSON_ID = t.PERSON_ID LEFT OUTER JOIN dbo.Address as a with (nolock) INNER JOIN dbo.CP as cp with (nolock) on (a.CP_id=cp.CP_id) on (e.Address_id=a.Address_id) LEFT OUTER JOIN dbo.DATA as ed with (nolock) LEFT OUTER JOIN dbo.Civility as civ with (nolock) on (ed.Civility_id=civ.civility_id) LEFT OUTER JOIN dbo.Language as l with (nolock) on (ed.Language_id=l.Language_id) on (e.PERSON_id=ed.PERSON_id) left OUTER JOIN dbo.GSM as g with (nolock) on (e.GSM_ID = g.GSM_ID) [/i]when i try to execute this query i have the result in 3 seconds. Select Liste_DMADRESSE1,Liste_DMADRESSE2,Liste_DMADRESSEID,Liste_DMCIVILITE,Liste_DMCODEPOSTAL,Liste_DMDATEMEMBRE,Liste_DMDATENAISSANCE,Liste_DMGENRE,Liste_DMIDMEMBREWEB,Liste_DMLOGIN,Liste_DMMOTDEPASSE,Liste_DMNOM,Liste_DMPAYSISO2,Liste_DMPRENOM,Liste_DMTELFIXE,Liste_DMTELMOBILE,Liste_DMVILLE From View_IdPERSON AS [IDENTITYVIEW] where Liste_EMAIL = 'XXX@XXX.FR' But when i change my query with "top 1" expression i wait at least 5 minutes and i have no result. (Select top 1 Liste_DMADRESSE1,Liste_DMADRESSE2,Liste_DMADRESSEID,Liste_DMCIVILITE,Liste_DMCODEPOSTAL,Liste_DMDATEMEMBRE,Liste_DMDATENAISSANCE,Liste_DMGENRE,Liste_DMIDMEMBREWEB,Liste_DMLOGIN,Liste_DMMOTDEPASSE,Liste_DMNOM,Liste_DMPAYSISO2,Liste_DMPRENOM,Liste_DMTELFIXE,Liste_DMTELMOBILE,Liste_DMVILLE From View_IdPERSON AS [IDENTITYVIEW] where Liste_EMAIL = 'XXX@XXX.FR' )Two queries have different execution plans. I tried to index all columns used, update statistics.... But no succes. HERE ARE THE INDEXES : EMAIL -----IX_emailvalueIX_Email_idPERSON --- IX_PERSON_id_cluster IX_PERSON_ID include(All columns used in the query) IX_PERSON_Lastmodificationdatecivility---CIVILITY_id DATA---IX_PERSON_ID(non clustered) IX_PERSON_ID (includes all columns used) GSM --- GSM_idLanguage---Language_id Address--adsress_idI attached the query plans of two queries. And three large tables(really not so large, 150 000 lines, the largest one) . The others are really small tables not so much data. Do you have an idea? Thanks in advance.

IF EXISTS(SELECT 1 FROM ...) giving unexpected results in a TSQL job step

Posted: 22 Jul 2013 07:08 PM PDT

Weird one SQL 2008 Standard SP2 on Windows 2003The SQL below checks to see if a given job ('Production DB Backup.Check DB Integrity") is running and issues a sp_stop_job command if it is.(I know - you shouldn't be stopping an integrity check etc etc ..)Anyway, here it is belowIF EXISTS ( SELECT 1 FROM msdb.dbo.sysjobs_view job INNER JOIN msdb.dbo.sysjobactivity activity ON (job.job_id = activity.job_id) WHERE run_Requested_date IS NOT NULL AND stop_execution_date IS NULL AND job.NAME LIKE '%Integrity%' ) BEGIN SELECT 'here' EXEC msdb.dbo.sp_stop_job 'Production DB Backup.Check DB Integrity' ENDWorks fine when executed in SSMS - as I'd expect, if the job isn't running it does nothing.Now, when I create a job and put this in as a Transact-SQL step, the job fails as the sp_stop_job gets fired ... even if the job isn't running !It's as thought the IF EXISTS(..) isn't evaluated correctly ?Any thoughts folks ?

SQLPS issue

Posted: 22 Jul 2013 04:28 PM PDT

I have two node windows cluster on which SQL2008 R2 is running. When I execute SQLPS, it throws error [b]"Drive root "E:\" does not exist or it's not a folder."[/b]. If I execute same on other node it throws error for different drive. I know since it is cluster, these drives are owned by respective nodes and wont be available from other nodes. Because of this, I can not execute SQLPS in windows scheduler as well as in SQL Agent job. It is failing before it is processing the PS1 file itself.I dont know there is any switches or command parameter which will bypass this error.Chandu

between sql server and sql server agent

Posted: 22 Jul 2013 05:06 AM PDT

hi soory for stupid question but whats difference between sql server and sql server agent ???arent both windows services .if yes then why can we allocate memory to sql server but not agentdoes lack of memory to OS can cause sql server agent to stop

Sql server query help

Posted: 22 Jul 2013 01:29 PM PDT

Ok I hope I can explain my dilemaI need a sql select statement that will Select part_number from RCTOOL the column part_number has values such as BR643-0034-344-34AS54-54689A77360-3454-34456-4B7374-343-33-32-356-433-11-1In each of these cases ALL we want it to return is the values UP to the last - Desired result set from example above BR643-0034-344AS54A77360-3454-34456B7374-343-33-32-356-433-11Thank you in advance

substring problem causing an error

Posted: 22 Jul 2013 04:45 PM PDT

I have a query in one of my procedures which causes an error.[code]select substring(expirydate,1,(charindex(' ',expirydate) -1)) as expirydate from newtable[/code]this query looks through my expiry date field and looks for the space minus 1 character and extracts the 30/04/2012 from my 30/04/2012 08:14:23 date and time. all is good at this point but I run into errors when the dates in the columns are like this30/04/2012or5/011/2012as there are no spaces so it cannot find the charindex value which I think causes an error[code]Msg 537, Level 16, State 5, Line 1Invalid length parameter passed to the LEFT or SUBSTRING function.[/code]does this make sense and is there a way round it

SSRS 2008 R2 tablix inside tablix text box alignment

Posted: 22 Jul 2013 10:54 AM PDT

Hi,I'm having a problem with borderline alignment between multiple tablix inside one tablix.When I run the report with VS2010 or SSRS 2008 R2 it works fine but when I post it to our report server and run the report, border lines between tablix are off from each other.I've tried a rectangle, tablix inside tablix with nogrow option, and just tablix after tablix but no luck so far.I attached an image.Does anyone know why preview version and actual report are different?I've been searching for clues but couldn't find any. Hope someone here can help me on this issue.thank you.--SQL 2005 has BI section but not under SQL 2008. Let me know if this is not the right place for this question.

do I need to use case explicily

Posted: 22 Jul 2013 06:40 AM PDT

I have a database that imported some students' info from a text file.When importing into the raw table, the studentID is set as varchar(9).Then we select from the raw table into another processing table.In that table the student id is integer.Do I need to do explicitly use cast? - that is cast it into int, or I don't need to since studentIDs are all numbers? It may do implicitly conversion by itself?what is the better practiceThanks

Table partition sql 2008 partion key include on cluster index

Posted: 22 Jul 2013 06:13 AM PDT

Hi,I am new for table partition so please help me to choose right key for cluster index.example:Table (colA,colB,colc,.....)Colc : my partition function is on colc (which is not unique)(cola,colb) --- unique non clustered index, this columns are used mostly in my company for querying data.My question:Can i use [ColA,ColB,Colc(partition key)] these columns as unique composite clustered index to make advantage of partition key or there is any suggestions i.e New composite key : (Cola+colB+Colc) ---- is that fine ?Please reply @ sql.lanka@gmail.com

No history of job execution in a particular instance

Posted: 21 Jul 2013 09:06 PM PDT

Hi,I have a job scheduled to run every 2 hours during the week.The job started to run at 1:15 PM on Friday afternoon as scheduled (confirmed from the steps log and also since the consequent scheduled jobs failed due to "Failed to start job -job already in running state" type of error message)During the weekend, as part of maintenance activity any unprocessed records are moved to a history table and have been moved. However the job activity monitor doesn't even list that the job executed. The last execution it shows is of 11:15 the same morning as per schedule. The file that was supposed to be created at the end of the job is also not created, however the records in the table mean that the job was executed. Cant seem to trace the job history. Help!!

Looping through table

Posted: 22 Jul 2013 05:01 AM PDT

I am simply trying to look through a table select out email(recipient), and combing to columns into one(body and unique) and can't seem to do it.My query will generate 4 emails, but all four contain data from the first row of table. What am i missing?Here is table:CREATE TABLE [dbo].[TestData]( [EMAIL] [text] NULL, [LTNM ] [varchar](100) NULL, [EMAIL] [varchar](100) NULL, [CREATEDBY] [varchar](100) NULL, [BODY] [varchar](800) NULL, [UNIQUE] [varchar](40) NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]data:EMAIL LTNM REC_EMAIL CREATEDBY BODY UNIQUEtest1l@blah.com test1,one 1testl@blah.com bajackson 12345 609329952586HVWBRDWMHPKDtest2@blah.com test2,one 2test@blah.com bajackson 1234 113299503766ZSGCEECCBHQBtest3l@blah.com test3,one 3test@blah.com atholder 123 516373455442CXVEDSOHUTIDtest4@blah.com test4,one 4test@blah.com jppompa 12 973199046577MNMEEUEIICWRDECLARE @count intset @count = 1DECLARE @Recepient_Email VARCHAR(MAX)DECLARE @Body1 VARCHAR(MAX)while (@count <=(select COUNT(*) from TestData)) begin set @Recepient_Email = (select top(1) EMAIL From TESTDATA where @count=@count) set @Body1 = (select top(1) TESTDATA.BODY + '' + TESTDATA.UNIQUE from TESTDATA where @count=@count) EXEC msdb.dbo.sp_send_dbmail @profile_name='DBA', @recipients=@Recepient_Email, @subject = 'This is subject of test Email', @body =@Body1, @body_format = 'HTML' set @count =@count +1 END

Strange Error With Cursor

Posted: 22 Jul 2013 06:50 AM PDT

Hello,I have a cursor that I use to alter stored procedures to fix bugs that has always worked for me until today. Below is the cursor:[code="sql"]RECONFIGUREGOSET NOCOUNT ONDECLARE @dbname varchar(128), @SQL varchar(1000)DECLARE my_cursor CURSOR FOR SELECT name FROM sysdatabases ORDER BY nameOPEN my_cursorFETCH NEXT FROM my_cursor INTO @dbnameWHILE @@FETCH_STATUS=0BEGIN SELECT @SQL = 'USE ' + @dbname + ' IF EXISTS (SELECT * FROM sys.procedures WHERE name = ''example'') BEGIN EXECUTE xp_cmdshell ''OSQL -U -P -d' + @dbname + ' -iC:\example.sql'' END' EXECUTE(@SQL) FETCH NEXT FROM my_cursor INTO @dbnameENDCLOSE my_cursorDEALLOCATE my_cursorGOsp_configure 'xp_cmdshell', '0'RECONFIGURE[/code]Below is the error/s that I am getting:---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22> 23> 24> 25> 26> 27> 28> 29> 30> 31> 32> 33> 34> 35> 36> 37> 38> 39> 40> 41> 42> 43> 44> 45> 46> 47> 48> 49> 50> 51> 52> 53> 54> 55> 56> 57> 58> 59> 60> 61> 62> 63> 64> 65> 66> 67> 68> 69> 70> 71> 72> 73> 74> 75> 76> 77> 78> 79> 80> 81> 82> 83> 84> 85> 86> 87> 88> 89> 90> 91> 92> 93> 94> 95> 96> 97> 98> 99> 100> 101> 102> 103> 104> 105> 106> 107> 108> 109> 110> 111> 112> 113> 114> 115> 116> 117> 118> 119> 120> 121> 122> 123> 124> 125> 126> 127> 128> 129> 130> 131> 132> 133> 134> 135> 136> 137> 138> 139> 140> 141> 142> 143> 144> 145> 146> 147> 148> 149> 150> 151> 152> 153> 154> 155> 156> 157> 158> 159> 160> 161> 162> 163> 164> 165> 166> 167> 168> 169> 170> 171> 172> 173> 174> 175> 176> 177> 178> 179> 180> 181> 182> 183> 184> 185> 186> 187> 188> 189> 190> 191> 192> 193> 194> 195> 196> 197> 198> 199> 200> 201> 202> 203> 204> 205> 206> 207> 208> 209> 210> 211> 212> 213> 214> 215> 216> 217> 218> 219> 220> 221> 222> 223> 224> 225> 226> 227> 228> 229> 230> 231> 232> 233> 234> 235> 236> 237> 238> 239> 240> 241> 242> 243> 244> 245> 246> 247> 248> 249> 250> 251> 252> 253> 254> 255> 256> 257> 258> 259> 260> 261> 262> 263> 264> 265> 266> 267> 268> 269> 270> 271> 272> 273> 274> 275> 276> 277> 278> 279> 280> 281> 282> 283> 284> 285> 286> 287> 288> 289> 290> 291> 292> 293> 294> 295> 296> 297> 298> 299> 300> 301> 302> 303> 304> 305> 306> 307> 308> 309> 310> 311> 312> 313> 314> 315> 316> 317> 318> 319> 320> 321> 322> 323> 324> 325> 326> 327> 328> 329> 330> 331> 332> 333> 334> 335> 336> 337> 338> 339> 340> 341> 342> 343> 344> 345> 346> 347> 348> 349> 350> 351> 352> 353> 354> 355> Msg 102, Level 15, State 1, Server server, Line 1Incorrect syntax near '∩'.Msg 111, Level 15, State 1, Server server, Line 54'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.NULLI have never seen the ∩ before so not sure what that is about. The SQL file I am running is a standard ALTER PROCEDURE script.Thanks

Object cannot be renamed because the object participates in enforced dependencies.

Posted: 22 Jul 2013 04:51 AM PDT

Here is the senario... I have multiple copies of a table across several servers. The problem is that these sometimes get out of sync in the data that is inside them.I have been tasked with replacing these tables with a single copy.I have made the linked server I need, and i have created the view that is the reference i need.I have come across the Titled error when attempting to rename, there are 15-20 stored procs and 5-10 views that use the data. What I want to do is a quick rename, and then a synonym refernce to the view i've created in there. (the view is so developers who use it can still have a reference in the local database w/o having to go to another server to see it and not having to know or care that the table changed)Is there a quick way to acomplish this or do i need to go in and switch the dependencies to utilize the view and THEN switch it over? I can most certainly do this and fairly rapidly, but was wondering if there was a quick way to turn off the dependency check and get what i want done and then re-enable the checks....If not, no issue, i can still solve this.

Ranking Based of Advert Breaks

Posted: 06 Jul 2013 11:43 AM PDT

Hi People,This is a tough one for me. Let me start by explaining what i want to do then posting my code and data for you guys to give me ideas on how i can go about it.I have this table that shows date, time, programme code(i.e. brandflag field) and station that an advert is being captured:[code="sql"]CREATE TABLE [dbo].[TestTable]( [AdDate] [datetime] NULL, [AdTime] [nvarchar](8) NULL, [FK_StationId] [nvarchar](5) NULL, [BrandFLag] [nvarchar](5) NULL) ON [PRIMARY]GOINSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:46:36', N'A1', N'T79')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'20:20:00', N'A1', N'T1004')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'20:43:00', N'A1', N'T1004')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'20:58:22', N'A1', N'T0')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:10:57', N'A1', N'T21')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:12:22', N'A1', N'T21')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:30:51', N'A1', N'T21')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:31:00', N'A1', N'T21')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:32:00', N'A1', N'T21')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:33:00', N'A1', N'T21')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:34:00', N'A1', N'T21')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:34:01', N'A1', N'T21')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:02:57', N'A1', N'T320')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:03:00', N'A1', N'T320')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:03:23', N'A1', N'T320')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:07:59', N'A1', N'T320')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:08:34', N'A1', N'T320')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:15:00', N'A1', N'T320')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:15:01', N'A1', N'T320')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:15:02', N'A1', N'T320')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'23:35:00', N'A1', N'T0')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'06:30:16', N'A10', N'T79')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:00:00', N'A10', N'T0')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:30:05', N'A10', N'T79')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:31:30', N'A10', N'T79')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:32:27', N'A10', N'T79')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:45:45', N'A10', N'T79')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:46:10', N'A10', N'T79')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:48:12', N'A10', N'T79')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'09:01:18', N'A10', N'T0')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'09:02:18', N'A10', N'T1442')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'09:30:31', N'A10', N'T1443')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'10:01:00', N'A10', N'T0')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'15:58:05', N'A10', N'T0')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'16:03:00', N'A10', N'T202')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'16:59:02', N'A10', N'T0')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'16:59:14', N'A10', N'T0')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'17:00:13', N'A10', N'T1154')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'17:31:15', N'A10', N'T219')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'18:00:00', N'A10', N'T1154')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'18:30:37', N'A10', N'T231')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'18:58:40', N'A10', N'T0')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'18:59:47', N'A10', N'T0')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'19:00:00', N'A10', N'T375')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'19:17:46', N'A10', N'T1164')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'19:21:53', N'A10', N'T1164')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'19:31:42', N'A10', N'T1154')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'19:58:22', N'A10', N'T0')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'20:00:00', N'A10', N'T0')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'20:01:53', N'A10', N'T0')[/code]And i have this code that shows the position of each advert per each programme (brandflag):[code="sql"]SELECT AdDate, AdTime, FK_StationId,brandflag, CAST(rank() over(partition by brandflag order by fk_stationid, addate,adtime) AS VARCHAR(10)) + '/ ' + CAST(count(*) over(partition by fk_stationid,brandflag) AS VARCHAR(10)) as PositionFROM dbo.testtableGROUP BY AdDate, AdTime, FK_StationId, brandflagHAVING (AdDate = CONVERT(DATETIME, '2013-01-04 00:00:00', 102))order by fk_stationid, addate, adtime[/code]That gives me this result:[code="sql"]CREATE TABLE [dbo].[TestResult]( [AdDate] [datetime] NULL, [AdTime] [nvarchar](8) NULL, [FK_StationId] [nvarchar](5) NULL, [brandflag] [nvarchar](5) NULL, [Position] [varchar](22) NULL) ON [PRIMARY]GOSET ANSI_PADDING OFFGOINSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:46:36', N'A1', N'T79', N'1/ 1')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'20:20:00', N'A1', N'T1004', N'1/ 2')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'20:43:00', N'A1', N'T1004', N'2/ 2')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'20:58:22', N'A1', N'T0', N'1/ 2')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:10:57', N'A1', N'T21', N'1/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:12:22', N'A1', N'T21', N'2/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:30:51', N'A1', N'T21', N'3/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:31:00', N'A1', N'T21', N'4/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:32:00', N'A1', N'T21', N'5/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:33:00', N'A1', N'T21', N'6/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:34:00', N'A1', N'T21', N'7/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:34:01', N'A1', N'T21', N'8/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:02:57', N'A1', N'T320', N'1/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:03:00', N'A1', N'T320', N'2/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:03:23', N'A1', N'T320', N'3/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:07:59', N'A1', N'T320', N'4/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:08:34', N'A1', N'T320', N'5/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:15:00', N'A1', N'T320', N'6/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:15:01', N'A1', N'T320', N'7/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:15:02', N'A1', N'T320', N'8/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'23:35:00', N'A1', N'T0', N'2/ 2')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'06:30:16', N'A10', N'T79', N'2/ 7')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:00:00', N'A10', N'T0', N'3/ 11')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:30:05', N'A10', N'T79', N'3/ 7')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:31:30', N'A10', N'T79', N'4/ 7')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:32:27', N'A10', N'T79', N'5/ 7')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:45:45', N'A10', N'T79', N'6/ 7')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:46:10', N'A10', N'T79', N'7/ 7')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:48:12', N'A10', N'T79', N'8/ 7')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'09:01:18', N'A10', N'T0', N'4/ 11')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'09:02:18', N'A10', N'T1442', N'1/ 1')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'09:30:31', N'A10', N'T1443', N'1/ 1')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'10:01:00', N'A10', N'T0', N'5/ 11')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'15:58:05', N'A10', N'T0', N'6/ 11')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'16:03:00', N'A10', N'T202', N'1/ 1')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'16:59:02', N'A10', N'T0', N'7/ 11')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'16:59:14', N'A10', N'T0', N'8/ 11')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'17:00:13', N'A10', N'T1154', N'1/ 3')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'17:31:15', N'A10', N'T219', N'1/ 1')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'18:00:00', N'A10', N'T1154', N'2/ 3')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'18:30:37', N'A10', N'T231', N'1/ 1')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'18:58:40', N'A10', N'T0', N'9/ 11')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'18:59:47', N'A10', N'T0', N'10/ 11')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'19:00:00', N'A10', N'T375', N'1/ 1')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'19:17:46', N'A10', N'T1164', N'1/ 2')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'19:21:53', N'A10', N'T1164', N'2/ 2')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'19:31:42', N'A10', N'T1154', N'3/ 3')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'19:58:22', N'A10', N'T0', N'11/ 11')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'20:00:00', N'A10', N'T0', N'12/ 11')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'20:01:53', N'A10', N'T0', N'13/ 11')[/code]Actually, the kind of result i'm looking for is something like breaking the results into separate advert breaks, e.g. when you have a programme, one way of determining the advert breaks in the programme is to look at the closeness of the adverts e.g: looking at the results below, you will notice that the first record i.e '1/8' and '2/8' are close together having the times '21:10:57' and '21:12:22', but for the rest of the timing they start from '21:30:51' through to '21:34:01', i would consider that as the 2nd advert break and consider the first set as the first advert break.I would like to get a result displays in this format e.g. '1/2 of 1' meaning that for the first advert break, the first advert is advert 1 of 2 of the first advert break i.e ('21:10:57') and ('21:12:22') is advert '2/2 of 1' i.e breaking the adverts basedon advert breaks.Then the rest will follow suit e.g. ('21:30:51') will be '1/6 of 2' meaning advert 1 of six adverts in the 2nd break.[code="sql"]INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:10:57', N'A1', N'T21', N'1/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:12:22', N'A1', N'T21', N'2/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:30:51', N'A1', N'T21', N'3/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:31:00', N'A1', N'T21', N'4/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:32:00', N'A1', N'T21', N'5/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:33:00', N'A1', N'T21', N'6/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:34:00', N'A1', N'T21', N'7/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:34:01', N'A1', N'T21', N'8/ 8')[/code]Please if you need any clarification, i will be willing to explain again, i hope there is a solution to this.Thanks very much.Tim

what lock pages in memory option does.

Posted: 22 Jul 2013 05:08 AM PDT

Hi i want to know what lock pages in memory option does.i donot gets it wording " Windows not to swap out SQL Server memory to disk ".can some explain in lay men`s termsby "sql server memory" does it means RAM and by disk does it means secondary storage (or harddrives). And when it should be used

Error converting data type varchar to numeric

Posted: 22 Jul 2013 05:05 AM PDT

Hi All,Im not the greatest at SQL but Im getting better. I do not understand why I get the following error in my simple select query. I get the (Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to numeric.) [code="sql"]SELECT ed.[Employee Name], bd.SORTUSER_EMPID, bd.SORTDTTMFROM [BOXES].[dbo].[BOXDETAILS_ORL] bdJOIN [Employee].[dbo].[Employee Data] ed ON bd.SORTUSER_EMPID = ed.[Employee ID]WHERE SORTDTTM >= dateadd(day,-30,getdate())[/code]

Help with temp table based on previous row value

Posted: 22 Jul 2013 03:04 AM PDT

[Edited to drastically simplify question] Please see attached. Thanks!

Moving clustered indexes to new filegroup

Posted: 22 Jul 2013 02:02 AM PDT

I am rebuilding the clustered index of a table to a new filegroup using DROP EXISTING in order to move the data to the new filegroup.Does this rebuild the non-clustered indexes of that table on the new filegroup as well?Thanks!

Order By trouble

Posted: 22 Jul 2013 02:46 AM PDT

Hi geniuses,I need to order some data and when Value = Null -> 'Not Defined'.Next I need the Value 'Not Defined' to be odered in the first row.[code="sql"]INSERT INTO #mytable (ID, Value) SELECT '4','First' UNION ALL SELECT '37','Second'UNION ALL SELECT '44','Second' UNION ALL SELECT '54','Third' UNION ALL SELECT '55','' UNION ALL SELECT '81','Fifth' UNION ALL SELECT '86','' UNION ALL SELECT '96',''[/code] I tried something like: [code="sql"]Select ISNULL(Value, 'Not Defined') as Value from #mytable UNION Select Value AS Value FROM #mytable Order by Value[/code]Thanks in advance

Create a view with a self populating column

Posted: 22 Jul 2013 02:16 AM PDT

Have a strange request from the apps folks. Here is what they want:We have three SQL Server databases with the same table and columns but the data within the tables belongs to different company/entities. They are going to pull out that data and put it into an Oracle database but once they get it into the Oracle database there is no way to know which rows came from which database. So, they want to create a view on these specific tables and have a self populating column in the view that has the database name in it. Then the Oracle DB will have the name DB_Origin and will then be populated with that name so they will be able to tell which SQL Server DB that data came from. How would I construct that view?As an Example this is what the table looks like in Database Name WEN01:DateItemSalesView would be:DateItemSalesWEN01 hardcoded

SQL Server 2008 Replication. ½,¼,¾ (fraction signs) have stopped replicating properly in a varchar field. They are now getting corrupt. (e.g. 108¾ at publisher and 108ó at subscriber)

Posted: 21 Jul 2013 08:29 PM PDT

Re: SQL Server 2008 Replication. ½,¼,¾ (fraction signs) have stopped replicating properly in a varchar field. They are now getting corrupt. (e.g. 108¾ at publisher and 108ó at subscriber)Field is defined as....Publisher = [V_MYFIELD] [varchar](50) COLLATE SQL_Latin1_General_CP850_BIN NULLSubscriber = [V_MYFIELD] [varchar](50) COLLATE SQL_Latin1_General_CP850_BIN NULLData is being written to Publisher correctly.Problem has been occurring from 20th July 2013. Time unknown.I have an 2 hourly replication check. This picked up 2 tables with this same issue of the ½,¼,¾ signs not being replicated properly at the subscriber end.Replication reports no errors. It thinks everything has replicated fine.I know I have a separate issue with the distributor database. It has 144 million rows in [distribution].[dbo].[MSrepl_commands] table. I am in the process of working out the steps involved in fixing this via other forum pages. Is this why I might be getting this fraction sign issue? due the size of this database?Could anyone help with a list of things I can try to find out the "Root Cause"?I will of course to continue to investigate but it is always helpful to find out what people think. I have tried to find this topic area using Google but there is nothing 100% about this issue.Thanks,Regards,Richard.NOTE : This is a transaction replication setup.Posted 22nd July 2013 10:28hrs BST (GMT +1)

Date convertion

Posted: 21 Jul 2013 10:58 PM PDT

Hi,My table have a column with date format 'Jun 15 2010 12:00:00:000AM'But i need that column name with the format of 2010-06-15 so what i will do Plz help me...

Log file

Posted: 21 Jul 2013 09:37 PM PDT

what is the difference between the database logfile and the database configured under replication ?

DATEADD Requirement

Posted: 21 Jul 2013 09:24 PM PDT

I have a requirement like... Add a week to the initial date provided till end of current month of initial date... Once the date cross the current month then it goes in month calculation. Sample Outputstart date end date01-07-2013 08-07-201308-07-2013 15-07-201315-07-2013 22-07-201322-07-2013 29-07-201329-07-2013 01-08-201301-08-2013 01-09-201301-09-2013 01-10-201301-11-2013 01-12-2013SELECT @date_start = '20130701', @nb_period = 12, @nb_unit =1, @i = 1TRUNCATE TABLE #datesSELECT @date_end = CONVERT(VARCHAR(25),DATEADD(WEEK,@nb_unit,@date_start ),101)INSERT INTO #dates (date_debut,date_end ) VALUES (@date_debut,@date_end )WHILE (@i < @nb_period)BEGIN SELECT @date_start = @date_end SELECT @date_end = CONVERT(VARCHAR(25),DATEADD(WEEK,@nb_unit,@date_start ),101) INSERT INTO #dates (date_start ,date_end ) VALUES (@date_start ,@date_end )SELECT @i = @i +1ENDSELECT * FROM #datesI am not able to get the monthly flow once the week finish in current month. (Line no 5 onwards).Please advice.

MSDTC

Posted: 21 Jul 2013 08:19 PM PDT

what is MSDTC and the purpose in sql and is it necessary to have MSDTC on sql cluster ?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog