Tuesday, July 23, 2013

[MS SQL Server] Memory Issues on production sql server

[MS SQL Server] Memory Issues on production sql server

Memory Issues on production sql server

Posted: 10 Jul 2013 07:21 AM PDT

Please help me in this situation.Total memory on box : 10 GBSingle node, non clustered production boxMin memory is set to 0Max Memory is set to 8 GBFrom last month the box Memory is constantly at 95%CPU usage is very lowhere is the screen shot from the task manager[img]http://img838.imageshack.us/img838/1104/p77j.png[/img]How to find Is SQL server using the complete 8GB ?Management is asking to reduce the SQL server memory setting to 7GBIs this fine to decrease the memory limit. Please advice.

Need a blocking script with specific details

Posted: 23 Jul 2013 02:23 AM PDT

Hello All, I am looking for a script that will give me these details. 1) spid2) Total number of spids it is blocking directly or indirectly. 3) blocking Query4) Login name who ran the Query. 5) how long the query has been running for. I am able to get all the info except "total numberof SPIDs it is blocking directly or indirectly"Can somebody please help me with this, that will be very helpful. thanks in advance. regardsVB

Linked Server - Works Except With Insert Statement

Posted: 22 Jul 2013 06:11 AM PDT

I am trying to get Job information from ServerB, and populate a Table Variable on ServerA. From ServerA, I am running the following command with a Linked Server to ServerB.If I run this by itself from ServerA, I get the expected results in the query window. And if I insert into a table variable from the local server it works file[code="sql"]EXECUTE [LinkedServer_B].master.dbo.xp_sqlagent_enum_jobs 1,''[/code]But when I try the 2nd block of code inserting into the table variable from the linked server, I get an error.[code="sql"]DECLARE @currently_running_jobs TABLE ( job_id UNIQUEIDENTIFIER NOT NULL ,last_run_date INT NOT NULL ,last_run_time INT NOT NULL ,next_run_date INT NOT NULL ,next_run_time INT NOT NULL ,next_run_schedule_id INT NOT NULL ,requested_to_run INT NOT NULL ,request_source INT NOT NULL ,request_source_id SYSNAME COLLATE database_default NULL ,running INT NOT NULL ,current_step INT NOT NULL ,current_retry_attempt INT NOT NULL ,job_state INT NOT NULL ) -- 0 = Not idle or suspended, 1 = Executing, 2 = Waiting For Thread, 3 = Between Retries, 4 = Idle, 5 = Suspended, [6 = WaitingForStepToFinish], 7 = PerformingCompletionActions --Capture Jobs currently workingINSERT INTO @currently_running_jobsEXECUTE [LinkedServer_B].master.dbo.xp_sqlagent_enum_jobs 1,''[/code]ERROR:[b]OLE DB provider "SQLNCLI10" for linked server "LinkedServer_B" returned message "The partner transaction manager has disabled its support for remote/network transactions.".Msg 7391, Level 16, State 2, Line 21The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "LinkedServer_B" was unable to begin a distributed transaction.[/b]

Can we Create sql server instance through script?

Posted: 12 Jul 2013 04:19 PM PDT

Hi, I am using SQL server 2008 standard edition. Is there a way to create a new server Instance by a script except the default instance.? Thanks.

Cannot backup - Old killed backup still running

Posted: 24 Mar 2013 11:57 PM PDT

Hi I have one DB that on a multi DB server that people cannot connect to. I'm not familar with the DB.I THINK the problem was that an reindex or backup was ongoing when the Server was restarted.If I try and backup the DB now I get this error:System.Data.SqlClient.SqlError: Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed. (Microsoft.SqlServer.Smo)I can find 2 processes which have been killed: KILLED/ROLLBACK status=SUSPENDED. Both have wait time (ms) of a couple of dayskill 85 with statusonlySPID 85: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.How can I remove these so I may get the DB back up and working. Would a SQL restart work..Thanks

Cache Memory

Posted: 22 Jul 2013 04:05 PM PDT

Hi,How to find out Total amount of dynamic memory (in megabytes) that theserver is using for the dynamic SQL cache.
Posted by at No comments:

[Articles] The Agile Cult

[Articles] The Agile Cult

The Agile Cult

Posted: 22 Jul 2013 11:00 PM PDT

Lots of developers have embraced Agile development, and Steve Jones thinks it's a good way to build software. However, it's not necessarily as easy as you might think.
Posted by at No comments:

[SQL 2012] Suggestions for SQL 2012 BI Training

[SQL 2012] Suggestions for SQL 2012 BI Training

Suggestions for SQL 2012 BI Training

Posted: 22 Jul 2013 07:45 AM PDT

We're looking for a training class to get us started with SQL 2012 BI. We hope to find a class with a comprehensive overview on BI. Does anyone have any suggestions? It doesn't need to be a certification class, it's more important to get the overview of BI. I'm hoping a class like that exists.

Stored procedure running manually whereas not executing successfully through SQL JOB:

Posted: 22 Jul 2013 09:03 PM PDT

Hi,I have a job which runs every week which archieves records in to another database. When i run the SP in the query window it takes hardly 15 mins where as when i run the same through JOb it is not completing successfully for more than 15 hrs . So i need to manually stop the job every week. There are two step,first oe 1) Archieve2) Re-index.I checked possible solns and everything is fine such as 1) Permission issues(Job owner is Sysadmin)2)Blocking(No blocking when the Job run's)3)No clash in schedule Jobs'. Can anyone help if possible in this issue.

Master Data Services

Posted: 23 Jul 2013 02:08 AM PDT

Hi allI am trying to add a user in MDS however I just keep getting an error of:No exact match was found for domain\userIf I just put in the user and click the Check Name button it changes it to domain\user but for whatever reason I can't add the user.Any ideas?Thanks

unable to log into SQL Server Management Studio

Posted: 23 Jul 2013 01:24 AM PDT

I literrally just took over a project from a contract that when he left he changed the sa password and the auth process for the SQL server. No one knows the sa password and its now only setup for SQL Auth instead of mixed mode. Is there a way to log into the SQL server adn fix everything? We have several sharepoint databases and other databases on this server that we need to get to and we're unable to do so.[this is 1 of many things the contractor did on his last day that i'm trying to fix and get working again. :( ]

always on solution

Posted: 23 Jul 2013 01:13 AM PDT

In Always on would it be possible to have both an Active/Passive cluster and another Active cluster used for reporting?

SP / TABLE NAME

Posted: 22 Jul 2013 09:59 PM PDT

Hi,Can a table and an SP in the same schema have the same name please?Thanks

Need to see the value of set option NUMERIC_ROUNDABORT for all the sessions

Posted: 22 Jul 2013 04:08 AM PDT

Hi all I have a database that I want to add filtered index to it. While working with filtered index there are some set options that must be used by all the clients. If some of them won't be using the required set options, they won't be able to modify the data in the table that has the filtered index. In order to check if we have any clients that don't use the needed set options, I wanted to have a job that checks the sessions' set options in sys.dm_exec_sessions and if it finds problematic sessions, it will write there details into a log table. The problem is that sys.dm_exec_sessions don't have any information about NUMERIC_ROUNDABORT option. The function sessionproperty can show the setting of NUMERIC_ROUNDABORT only for the session that is running the function. Can anyone tell me how can I find out this setting for all the sessions?Thank you for your help.Adi

Reindexing on 2012 Availability groups

Posted: 22 Jul 2013 06:23 PM PDT

We have databases setup in availability group, recently we had to do an rebuild few indexes as they were fragmented. The index size was 12GB+. Rebuilding index started to consume more log space, I increased the disk space about nearly 50GB and still it was not completed. I understand the log will grow during rebuild of indexes, these changes are hardened on the secondary replica database.I saw blocking issues with HADR_COMMIT_SYNC wait type. Are there any guidelines of rebuilding indexes in the availability group? Please share..Using Online option to rebuild the index

Date time problem

Posted: 22 Jul 2013 10:46 AM PDT

Hi ProfessionalsI have a column within my database table called todays_date this is Datetime and in the format2013-07-23 08:55:59:353is the a way to update or alter the table so it is in the format23/07/2013 onlythanks in advance
Posted by at No comments:

[T-SQL] update script

[T-SQL] update script

update script

Posted: 23 Jul 2013 12:41 AM PDT

hii need to write script like thisBEGIN TRY -------------------------------------------------------------------- -- Add New columns into product -------------------------------------------------------------------- IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N' [product]]') AND type in (N'U')) BEGIN ---------------------------------------------------------------- -- Add New columns into product -------------------------------------------------------------------- BEGIN TRANSACTION ALTER TABLE product ADD Type varchar(25) NULL ALTER TABLE product ADD ToDisplay bit NULL --BEGIN TRANSACTION INSERT INTO product VALUES (6,'Not Started',NULL,NULL,0,'Clinical',0) INSERT INTO product VALUES (7,'In Progress',NULL,NULL,0,'Clinical',1) INSERT INTO product VALUES (8,'Complete - Resolved',NULL,NULL,0,'Clinical',1) INSERT INTO product VALUES (9,'Complete - Unresolved',NULL,NULL,0,'Clinical',1) INSERT INTO product VALUES (10,'Closed - Member/Client Declined.',NULL,NULL,0,'Clinical',1) ------------------------------------------------------------------- ------------- Update values for Type and Display ------------------------------------------------------------------------- UPDATE product SET Type = 'HA',ToDisplay =1 where ID IN(1,2,3,4,5) ALTER TABLE product ALTER COLUMN TYPE VARCHAR(25) NOT NULL alter table product alter column ToDisplay bit not null commit trnasaction END ELSE BEGIN PRINT ' product table does not exists' ENDEND TRY---------------------------------------------------------------------- Error Handling for all logic--------------------------------------------------------------------BEGIN CATCH END CATCH giving me error invalid column name 'type' invalid column name 'Todisplay'is there any way i can do all this in 1 script?

SP vs Identical T-SQL - different results

Posted: 22 Jul 2013 09:11 PM PDT

Guys,I've got an SP which takes two date parameters (a max and min), if I execute the SP I get X rows, if I script the SP to a new window, remove the SP stuff, declare and set the date parameters identically to when running the SP I get a totally different results set!Looking at the two results sets they do both have data over the same date range so it isn't an odd date formatting issue or something (besides, I'm using the same format in both cases).I've tried to re-compile the SP, recreate with a different name, even run on a test database on a separate server and I still get the same results. I passed this to a colleague for a sanity check, he hard coded hte dates into the SP - running the SP (now no params) and the code still produces different results.By different I'm talking 90 odd rows vs over 1000 rows.Is there any way to see if the sys objects has got confused or something? - As a side note, it's a proc used for an SSRS report that's been in use for many months (stretching into years) without an issue, and there haven't been any changes to it.Any help much appreciated - I'm going crazy here!

search values in a temp table with like operator

Posted: 22 Jul 2013 01:57 AM PDT

Hi, I have a SQL proc with a search-parameter (given as coma separated values) which should return all data of a table in which one of the search criteria is valid to any field of the table. At the moment I compare with '=' but I have to change this into like operator. No idea how I can handle this. Help would be highly appreciated!Thanks!!!Sue[code="sql"]create table tblclient( ID int identity(1,1) primary key, Firstname varchar(50), Lastname varchar(50), birthdate smalldatetime)goinsert into tblclientvalues('John','Singer','01.04.1980'),('Mary','Smith','21.06.1975'),('Marylou','Singersmith','11.03.1987'),('Carl','Smith','11.03.1987')gocreate proc pSearch @searchparam varchar(400) -- values will be entered like this - seperated by comma: John, Mary, 23.10.1980as set @searchparam = @searchparam + ',' declare @value varchar(100) set @value = '' -- create temp table to enter search values separated in rows create table #search(value varchar(100)) -- insert each value into #search while charindex(',',@searchparam)> 0 begin set @value = left(@searchparam, charindex(',',@searchparam)-1) set @searchparam = ltrim(right(@searchparam,len(@searchparam) - charindex(',',@searchparam))) insert into #search values(@value) end -- return all clients which referes to either one of the search criterias with like operator select id, firstname, lastname, birthdate from tblclient where firstname in(select value from #search) or lastname in(select value from #search) or convert(varchar(15),birthdate,104) in(select value from #search) goexec pSearch 'Mary' -- should return mary and marylouexec pSearch 'Mary, Smit' -- should return mary, marylou and carlexec pSearch 'Mary, 11.03.1987' -- should return mary,marylou and carl[/code]

Convert given Date to various formats

Posted: 22 Jul 2013 05:12 PM PDT

I have declare Start Date in below format.Declare @StartDate date = '2013-01-01'I want get output for below 2 col's as follows DateKey(int) = 01012013 Date(date) = '01-01-2013'INT, Date are datatypes here

TSQL Business Rule Implementation Between Two Tables

Posted: 22 Jul 2013 05:17 AM PDT

Hello,I am writing a report in SSRS and at first thought I would implement these biz rules at the report level but finding it inferior to the task. So now I can either implement as custom code (vb.net) in SSRS or at TSQL level which would be my preference.So the majority of the business rule I have already satisfied. It is this last step that has me stumped. To make it simple I have provided a sample table that represents the data I need to work with.[code="other"]DECLARE @TeamTable TABLE( TeamID VARCHAR(3), AssignedTask INT)INSERT INTO @TeamTable VALUES ('AAA', 12)INSERT INTO @TeamTable VALUES ('BBB', 45)INSERT INTO @TeamTable VALUES ('CCC', 67)INSERT INTO @TeamTable VALUES ('DDD', 11)INSERT INTO @TeamTable VALUES ('EEE', 12)INSERT INTO @TeamTable VALUES ('FFF', 10)INSERT INTO @TeamTable VALUES ('GGG', 11)INSERT INTO @TeamTable VALUES ('HHH', 6)INSERT INTO @TeamTable VALUES ('III', 3)INSERT INTO @TeamTable VALUES ('JJJ', 11)INSERT INTO @TeamTable VALUES ('KKK', 0)INSERT INTO @TeamTable VALUES ('LLL', 4)INSERT INTO @TeamTable VALUES ('MMM', 12)INSERT INTO @TeamTable VALUES ('NNN', 1)INSERT INTO @TeamTable VALUES ('OOO', 0)INSERT INTO @TeamTable VALUES ('PPP', 12)INSERT INTO @TeamTable VALUES ('QQQ', 12)INSERT INTO @TeamTable VALUES ('RRR', 0)[/code]This query get's the data in a manner that accurately represents my dataset:[code="other"]SELECT TT.TeamID, TT.AssignedTask, RANK() OVER (ORDER BY TT.AssignedTask DESC) AS 'Rank'FROM @TeamTable TTWHERE AssignedTask > 0[/code]Now here is the trick. I have to assign a weighted value to each team based upon the number of teams that have 1 or more task assigned and I don't know what to do next. So in my sample data you'll see that teams KKK, OOO, RRR have zero so 15 is our top point value and I get that using:[code="other"]DECLARE @RankMax TINYINTSET @RankMax = @@ROWCOUNTSELECT @RankMax[/code] So team CCC gets 15 points because they are top dog and it scales down.....with a twist. BBB gets 14.AAA, EEE, MMM, PPP, QQQ all tied. So the next set of points are added then averaged and everyone gets the average score.So 5 teams tied thus 13,12,11,10,9 are added together to equal 55 divided by 5 means each team gets 11 points.Then we move down to JJJ, GGG, DDD another tie. 8 + 7 + 6=21 and 21/3 = 7The rest go down to zero. So the final recordset would look like this:TeamID | AssignTask | Rank |CCC 67 15BBB 45 14AAA 12 11EEE 12 11MMM 12 11PPP 12 11QQQ 12 11JJJ 11 7GGG 11 7DDD 11 7FFF 10 5HHH 6 4LLL 4 3III 3 2NNN 1 1Not looking for anyone to do my homework just a pointer or two in how to approach the problem would be much appreciated.Thank You

Job shows communication link failure when VPN connects

Posted: 22 Jul 2013 10:34 PM PDT

Hi friends, I am running a job in SQL server which shows communication link failure when the machine connects to VPNCan you explain why this happens as the SQL server is on the same machine Thanks,Anish

Basic about clustered index !

Posted: 22 Jul 2013 02:44 PM PDT

Is the same space used by Clustered index and the column on which the primary key is declared?

Finding unequal column values with multiple column comparison

Posted: 22 Jul 2013 05:10 PM PDT

Hello All,I need your help in writing a query for below scenario,Lets assume there are two tables,Table A has 4 coulmns and Table B has 4 coulms[u][b]Table A[/b] Definition and Value[/u]Col1 Col2 Col3 Col41 2 3 A1 2 3 B1 2 3 C[u][b]Table B[/b] Definition and Value[/u]Col1 Col2 Col3 Col41 2 3 B1 2 3 AIf I write a inner join on Table A and B to compare like a.col1 = b.col1 and a.col2 = b.col2 and a.col3 = b.col3 and a.col4 != b.col4It is not fetching the odd record in Table A which is having value 'C' in Col4, though it will fetch all the records in Table A.Ideally I should get all the records where all the three columns (col1, col2, col3) in Table A and B are same and though Col4 is different (regardless of order of the record).Is it possible to write a TSQL -Query ?Thanks in advanceKJ

split a string

Posted: 03 Jul 2013 06:19 AM PDT

If I have a street address that I would like to split street from Apt number, how could I do that?for example now I have fullstreet ='100 NW 25 St APT# 303'Now I would like to split fullstreet into two columns Street = '100 NW 25 St ' and Apartment = #303what is the syntax?Thanks much
Posted by at No comments:

[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:
Subscribe to: Comments (Atom)

Search This Blog