Saturday, June 1, 2013

[SQL Server 2008 issues] calculate Excess/Short

[SQL Server 2008 issues] calculate Excess/Short

calculate Excess/Short

Posted: 31 May 2013 05:56 PM PDT

i have a problem kindly help me out.this type of data i want:Date------------Time in----Time out-----Spent time-------excess/short2013-01-01-------09:14-----19:06-----------09:52-------------00:522013-01-02-------09:52-----18:36-----------08:44-------------00:162013-01-03-------09:15-----18:56-----------09:41-------------00:41excess short comes from spent time mean a employee spent 9 hours 52 minute then its excess 00:52 minutes and if employee spent 8 hours 44 minutes then its short 00:16 minutes we have 9 to 6 hours timing shift.

How can we export Database Diagram into image/picture format?

Posted: 22 Apr 2010 12:07 AM PDT

I have created database diargram successfully. Now I want to export created database diagram into jpeg file or any suitable format that should allow user to view correctly.Can anyone tell me how to do this?Thanks in AdvanceVinayak.

Selecting a Value in a column based on value in another column.

Posted: 31 May 2013 04:33 PM PDT

Hi Experts,I have 2 columns as below --------------------------------taxid tax_Combination--------------------------------Bed+Vat ! VatBed+Vat ! CessBed+Vat ! BEDVAT ! VatCST ! CSTfrom a query i will get the Combinations like "Vat,Cess,BED", now i need to write a query where i have to pass these combinations and get the Taxid = 'Bed+Vat' alone.but when i us "IN" it returns 'Bed+Vat' and 'Vat' also.but i need to get the taxid for which the combination is exactly what i have given so it should be 'Bed+Vat' alone please help me.Thank you.

literature recommendations please

Posted: 31 May 2013 09:27 AM PDT

Can anyone recommend a good, complete tutorial for a newbie on how to install SQL Server 2008 Express with Advanced Services, including SQL Full-test Filter Daemon Launcher?If it will help, please see my thread "SQL Server can't connect to database" athttp://www.sqlservercentral.com/Forums/Topic1455724-391-1.aspxAnd while we're on the subject, can anyone recommend a good, complete textbook on SQL Server 2008?Thanks for any help anyone can give.

Patching runs DBCC checkdb

Posted: 31 May 2013 08:07 AM PDT

Hi Team,I have a cluster environment and I am going to apply the patches in the passive first, reboot passive and failover from the resources from the active to passive. When i do the failover, does SQL Server run DBCC Checkdb on the passive node before bringing the databases online?Thanks,Karthik R

Case Statement in Where Clause

Posted: 31 May 2013 08:15 AM PDT

I have a table with column A. Column A has values 1 or 0. The values represent 2 different outcomes. 1 is cats and 0 is dogs. I want to be able to use the column in a where clause as part of a stored procedure. The parameters are @cats and @dogs. The user can select either @cats or @dogs, or they can select both. If the don't make a selection I want to return both.If (@cats is 1 and @dogs is 0) then 'all cats' if (@cats is 0 and @dogs is 1) then 'all dogs' else 'all cats and all dogs'

SSIS - What is the best practice for creating a fixed length flat file.

Posted: 29 May 2013 01:36 AM PDT

I am running into the problem of SSIS putting the CRLF in random places at the end of the row. If we assume I am going to start from scratch, can I fix this issue in the Stored Procedure I use in my OLE source, or can I fix it on the SSIS side?I did see the following at MSDN but it did not make any sense to me since I don't see where to do this in the connection manager.[i][b]To make this work so that each row is exactly the same length, you may need to add a zero length column as the last one in the connection manager, and set the row delimiter on that one.[/b][/i]I did read as possibly using the advanced editor on the OLE adapter and adding a column there but how do I create a zero length column. If I send the header in the first row it is going to be at least 1 character.Any help would be greatly appreciated.FYI, I am using ragged right.

need help on sql script

Posted: 31 May 2013 06:03 AM PDT

HiI have done the script below but when I execute it on sql server 2008 an error comes up. I'm having hard time to figure it out. Please any help would be very appreciate. Thank youHere is the script:use mastergocreate database SouthCommunityCollegegouse SouthCommunityCollegegocreate table Semester(semesterID int primary key, semesterName varchar (30))create table Section(sectionID int primary key, sectionName varchar (30))create table Course(courseID int primary key, sectionID int, semesterID int,courseTitle varchar (30), courseCredits varchar (30),foreign key (semesterID) references Semester (semesterID),foreign key (sectionID) references Section (sectionID))create table Student(studentID int primary key, studentName varchar (30),studentAddress varchar (50), studentPhone int,studentEmail varchar (20))create table StudentAccount(studentID int, sectionID int,courseID int, instructorID int, grades varchar (30),constraint pk_StudentAccount primary key (studentID, sectionID),foreign key (courseID) references Course (courseID),foreign key (instructorID) references Instructor (instructorID))create table Instructor(instructorID int primary key, instructorName varchar (30),instructorPhone int, instructorOffice varchar (30), instructorEmail varchar (20))create table InstructorCourse(instructorID int primary key, courseID int)Here is the errorMsg 1767, Level 16, State 0, Line 19Foreign key 'FK__StudentAc__instr__117F9D94' references invalid table 'Instructor'.Msg 1750, Level 16, State 0, Line 19Could not create constraint. See previous errors.

Error passing parameter to SSIS package from Stored Proc

Posted: 31 May 2013 02:33 AM PDT

I am getting the error "Argument ""\Package\DataFlowTask.Variables[User::SvcDate].Properties[Value];"" for option "set" is not valid." when i attempt to pass a parameter to a variable within a SSIS package. The variable SvcDate is declared as datatype string within the package. The scope of the variable is defined at the Data Flow Task level. When I execute the package without the /SET parameter and set a default value for the variable within the package, it runs fine.I am new to SSIS and any help much appreciated!Thanks,BrianHere is my code:DECLARE @SQLQuery AS VARCHAR(2000),@SvcDate as varchar(10)DECLARE @ServerName Varchar(200) SET @SvcDate = '2120430'SET @SQLQuery = 'DTExec "/DECRYPT TBRIAN2" /FILE ^"C:\MCG\SSIS\Medmas Import.dtsx^" /SET \Package\DataFlowTask.Variables[User::SvcDate].Properties[Value]; '''+@SvcDate+''' ';EXEC master..xp_cmdshell @SQLQuery

Do you know of any Enterprise Database Inventory Management Systems?

Posted: 29 May 2013 05:07 AM PDT

Hello all,For the past half a decade, I as well as former and current colleagues have been custom developing an enterprise solution for inventory management as it relates to the SQL Server world, pretty much as a side task. This includes but is not limited to Servers, Instances, Databases, Applications, Windows Configurations, SQL Configurations, Database Sizes, Extended Properties, SQL Job History and quite a bit more. It's pretty awesome and serves its purpose; however time is never with us and we seem to never have enough time to dedicate towards ongoing enhancements and reporting. As we all know, daily life and operational tasks always takes priority over the bigger fun stuff! :(With the above said, I'm curious if anyone knows of, or can recommend any vendor based software out there that could possibly replace/enhance what we have. We have a massive SQL Server infrastructure and there is such a wealth of data out there that we truly need a robust and scalable application with trending/predictive reporting.Would appreciate any feedback.Thanks

How we can insert a sign of flag in the record based on condition.?

Posted: 31 May 2013 03:31 AM PDT

I want to insert a sign or change the color of particular record based on result of the condition.for an example if age is more than >45 then all the records in the table which are more than 45 will have some kind of sign or their color shoul get change.Please help.thanks.

List of queries using MAXDOP

Posted: 29 May 2013 09:47 AM PDT

Gurus, currently, in our environment we configured our sql servers for MAXDOP of 4. here is our config:4 CPUS - each 8 Core - 16 logical processorsCan you please help me determine if it is possible to find which queries are utilizing MAXDOP for query processing?ThanksJagan K

Different actual execution plan for same query

Posted: 31 May 2013 01:35 AM PDT

I have a query that I execute with a recompile hint with sp_executesql. This query performs rather ok. Then I comment the recompile hint and add some text making the query text unique thus forcing recompilation of the plan. The actual execution plan looks completely different then and the query is much slower. The number of estimated rows is very low which might be the symptom of the root cause. Also the io statistics are completely different. The query is run in transaction isolation level snapshot. We have seen some high read/write stalls on the tempdb file but I cannot imagine any problems on the tempdb causing other execution plans. Also all the statistics on the database were updated 8 hours ago. We see this phenomenon on and off. Sometimes the query is fast and sometimes slow. Recompile sometimes gives a boost but not always. Parameter sniffing was a first candidate explanation but with the latest test seems unlikely. The query has a lot of parameters which is due to the way our software handles queries. What influences the execution plan other than statistics? I have attached exec plans of fast and slow for completeness but my first goal is to have some lead for follow up research as we are

How to compare dates in 2 rows based on value in other column

Posted: 31 May 2013 12:37 AM PDT

I have a table with studentId, decision and FinishdateWhere studentid is the primary key and there could be none decision earlier and the aacept/deny later or vice-versa for a student.So, the decision could be accept/deny or None when there is no decision.I have to select the row of data when there is a decision (that is where decision is other than 'None' )and finish date is most recent for a studentid.How do I do that?Thanks, blyzzard

locked out of local SQL Server

Posted: 31 May 2013 01:02 AM PDT

Somehow my active directory account disappeared over the weekend. After getting the account back (same name), I could not login to my local SQL Server (on my PC). It's 2008R2, and I'm using XP.No one knows the sa password, but I normally login using Windows Authentication. Do I need to reinstall, or is there any other way to get into it?

How to Allocate the recieved amount to different expenses

Posted: 27 May 2013 05:06 PM PDT

Create Table #Temp( Number Int, Totalcoll Numeric(12,2), Maintainance_Due Numeric(12,2), Maintainance_Coll Numeric(12,2), Expense_Due Numeric(12,2), Expense_Coll Numeric(12,2))Insert Into #TempValues (1,0,500,0,400,0), (1,900,0,0,0,0), (1,0,200,0,800,0), (1,1000,0,0,0,0) Select * From #Temp /* With reference to above data, my Requirement is, For 1st row, there is Maintainance_Due = 500 and Expense_Due = 400 then In Second row,I want Under Maintainance_Coll should be 500 and Expense_Coll = 400 (This is TotalColl of 900 is allcated to Maintainance_Coll since Maintainance_Due = 500 and Expense_Due = 400) Same as this in 3rd row there is Maintainance_Due = 200 and Expense_Due = 800 So,In fourth row,I want Under Maintainance_Coll should be 200 and Expense_Coll = 800 (This is TotalColl of 100 is allcated to Maintainance_Coll since Maintainance_Due = 200 and Expense_Due = 800) */Please Help in this rewuirement as I required this in urgent and I am stucked in this.Please.........Thanks in Advance!!!!!!!!!!!!!!

Quick Replication question

Posted: 31 May 2013 01:03 AM PDT

Hey guys,Quick question: Will running the distribution job for snapshot replication lock the Publisher tables?Details: I have 2 SQL Server 2008 servers using Snapshot replication...I set this up 2 days ago and it has failed the past 2 nights...Last night when it failed, it completed the snapshot but the distribution job failed due to having a sorted timestamp column in one of the tables...I did some research and found a work around for that error that includes adding –UseInprocLoader at the end of the bcp command... So my question is: I know that I cant run the snapshot agent during peak hours due to putting a table lock on all the tables, but since the snapshot job completed, I was wondering if I can add that command and re run the distribution agent during peak hours? Not worried about it locking at the Subscriber level, but absolutely cannot run it if it will put a lock on at the Publisher level!Thank you in advance!

STUFF (concatenate rows into column)

Posted: 30 May 2013 09:07 PM PDT

Hey,I've used STUFF successfully before but I can't get it quite right this time as I'm using 9 columns.Take a resultset of;Col1 | Col2 | Col3 | Col4 | Col5 | Col6 | Col7 | Col8 | Col9ABC | DEF | GHI | JKL | MNO | PQR | STU | VWX | YZABC | DEF | GHI | JKL | 123 | PQR | STU | VWX | YZSo both rows are the same except Col5. What I want is this;Col1 | Col2 | Col3 | Col4 | Col5 | Col6 | Col7 | Col8 | Col9ABC | DEF | GHI | JKL | MNO; 123 | PQR | STU | VWX | YZObviously the STUFF needs to be ignored if there aren't multiple OWNERS.Col5 relates to the OWNER line (N_O columns) here;[code="sql"]SELECT DISTINCT C.IRN, CONVERT(DATE, DD.EVENTDUEDATE), ISNULL(DD_EC.EVENTDESCRIPTION, DD_EV.EVENTDESCRIPTION) + CASE WHEN DD.EVENTTEXT IS NOT NULL THEN ' (' + DD.EVENTTEXT + ')' ELSE '' END, ISNULL(dbo.fn_FormatName(N_I.NAME, N_I.FIRSTNAME, N_I.TITLE, NULL), 'UNKNOWN INSTRUCTOR'), ISNULL(dbo.fn_FormatName(N_O.NAME, N_O.FIRSTNAME, N_O.TITLE, NULL), 'UNKNOWN OWNER'), ISNULL(C.CURRENTOFFICIALNO, 'UNKNOWN'), DDI.IMPORTANCEDESC, ISNULL(N_E.SEARCHKEY2, '0'), ISNULL(N_S.SEARCHKEY2, '0')FROM CASES C WITH (NOLOCK) JOIN CASEEVENT DD WITH (NOLOCK) ON (DD.CASEID = C.CASEID) JOIN OPENACTION DD_OA WITH (NOLOCK) ON (DD_OA.CASEID = C.CASEID) JOIN EVENTCONTROL DD_EC WITH (NOLOCK) ON (DD_EC.CRITERIANO = DD_OA.CRITERIANO AND DD_EC.EVENTNO = DD.EVENTNO) JOIN EVENTS DD_EV WITH (NOLOCK) ON (DD_EV.EVENTNO = DD.EVENTNO) JOIN CASENAME CN_I WITH (NOLOCK) ON (CN_I.CASEID = C.CASEID AND CN_I.NAMETYPE = 'I') JOIN NAME N_I WITH (NOLOCK) ON (N_I.NAMENO = CN_I.NAMENO) LEFT JOIN CASENAME CN_O WITH (NOLOCK) ON (CN_O.CASEID = C.CASEID AND CN_O.NAMETYPE = 'O') LEFT JOIN NAME N_O WITH (NOLOCK) ON (N_O.NAMENO = CN_O.NAMENO) LEFT JOIN CASENAME CN_E WITH (NOLOCK) ON (CN_E.CASEID = C.CASEID AND CN_E.NAMETYPE = 'EMP') LEFT JOIN NAME N_E WITH (NOLOCK) ON (N_E.NAMENO = CN_E.NAMENO) LEFT JOIN CASENAME CN_S WITH (NOLOCK) ON (CN_S.CASEID = C.CASEID AND CN_S.NAMETYPE = 'SIG') LEFT JOIN NAME N_S WITH (NOLOCK) ON (N_S.NAMENO = CN_S.NAMENO) JOIN IMPORTANCE DDI ON DDI.IMPORTANCELEVEL = COALESCE(DD_EC.IMPORTANCELEVEL, DD_EV.IMPORTANCELEVEL, 9)WHERE (DD_OA.ACTION = DD_EV.CONTROLLINGACTION OR (DD_EV.CONTROLLINGACTION IS NULL AND DD_EC.CRITERIANO = ISNULL(DD.CREATEDBYCRITERIA, DD_OA.CRITERIANO)) OR DD_OA.CASEID IS NULL) AND ISNULL(DD.OCCURREDFLAG, 0) = 0 AND DD.EVENTDUEDATE IS NOT NULL AND C.IRN = 'P42951EP' AND EXISTS (SELECT 1 FROM OPENACTION OAX WITH (NOLOCK) JOIN EVENTCONTROL ECX WITH (NOLOCK) ON (ECX.CRITERIANO = OAX.CRITERIANO AND ECX.EVENTNO = DD.EVENTNO) JOIN ACTIONS AX WITH (NOLOCK) ON (AX.ACTION = OAX.ACTION) WHERE OAX.CASEID = C.CASEID AND OAX.ACTION = CASE WHEN (DD.EVENTNO = -11) THEN 'RN' ELSE ISNULL(DD_EV.CONTROLLINGACTION, OAX.ACTION) END AND OAX.POLICEEVENTS = 1 AND OAX.ACTION <> '~2' AND OAX.CYCLE = CASE WHEN (AX.NUMCYCLESALLOWED > 1) THEN DD.CYCLE ELSE 1 END AND ISNULL(AX.ACTIONTYPEFLAG, 0) <> 1) AND (DD.EVENTDUEDATE < DATEADD(MONTH, 1, GETDATE()));[/code]

Migrating An SSRS Database from one server to another, trying to remove jobs from old server

Posted: 31 May 2013 12:49 AM PDT

So our reporting database has been on a test server for way too long. I backed it up, and restored it to a production server. I also backed up and restored the ReportServer and ReportServerTempDB databases, as I was trying to get all of my SSRS reports and subscriptions moved over as well. Under Sql Server Agent, I have many jobs that are just named with a guid, and then I have my simple named jobs (SSIS jobs), like Daily, Weekly, etc. It appears that the ones named with a guid are mainly from the test server SSRS installation, and they appear to be running. I decided that I wanted to remove them all, and add my subscriptions locally. In Management Studio, I right clicked on the jobs, and deleted them. This morning, all of the jobs are back! The guids match the ones that exist on the test server. Are there some simple steps I can follow to either remove these jobs, or is there an easy way to initialize the report server, so that I can then re-create the jobs and subscriptions I need?Please keep in mind that I'm actually a developer, not a dba, even though I'm the closest thing to a dba that we have! :-D

issues with setting up Publisher in SQL2008

Posted: 30 May 2013 11:51 PM PDT

I have a two server configuration where the distribution db is on another server and the publisher is on a second server. I have setup the distribution db just fine (SQL2008R2 SP2) and when I configure the Publisher on SQL2008 SP3 it works but when the replication starts it gets a failed login.....The login is right and the password is correct. I don't understand why this thinks the password is incorrect. In the Publication I chose the option of RUn under SQL Server Agent Service Account so I didn't have to supply the password.... MessageReplication-Replication Snapshot Subsystem: agent failed. The replication agent had encountered an exception.Source: ReplicationException Type: Microsoft.SqlServer.Replication.ConnectionFailureExceptionException Message: Login failed for userMessage Code: 18456Replication-Replication Snapshot Subsystem: agent failed. Unable to start execution of step 2 (reason: Error authenticating proxy system error: Logon failure: unknown user name or bad password.). The step failed.

Can this be sorted without temporary tables

Posted: 30 May 2013 08:51 PM PDT

Two tables - one containing (team) Leaders and one containing (team) members[code="sql"]CREATE TABLE #tblLeaders ( LeaderID int, Leader varchar(50) );INSERT INTO #tblLeaders (LeaderID, Leader)SELECT 1, 'Pete' UNION ALLSELECT 2, 'Jim' UNION ALLSELECT 3, 'Arthur'CREATE TABLE #tblMembers ( MemberID int, LeaderID int, Member varchar(50) ); INSERT INTO #tblMembers (MemberID, LeaderID, Member)SELECT 1, 1, 'Frank' UNION ALLSELECT 2, 1, 'Martha' UNION ALLSELECT 3, 2, 'Betty' UNION ALLSELECT 4, 2, 'Mary' UNION ALLSELECT 5, 3, 'Jerry' UNION ALLSELECT 6, 3, 'Marcia'SELECT * FROM #tblLeadersSELECT * FROM #tblMembersSELECT *FROM #tblLeadersINNER JOIN #tblMembers on #tblLeaders.LeaderID = #tblMembers.LeaderIDORDER BY Leader, MemberDROP TABLE #tblLeadersDROP TABLE #tblMembers[/code]The code above gives me:Leader | MemberArthur - JerryArthur - MarciaJim - BettyJim - MaryPete - FrankPete - MarthaWhich is the Leaders in alphabetical order with their Members in alphabetical orderBut, what I need is data that comes back like this:ArthurJerryMarciaJimBettyMaryPeteFrankMartha... which is - a row for each team leader, then their team members but still with the Team Leaders appearing alphabetically and with their Team Members appearing below them but sorted alphabetically.I can do this by putting the team leaders in a cursor, looping through it, putting them in a temp table, finding their team members and putting them in the temp table etc.Is there a cursor free way of doing this? Thanks for any help.

Linked Server error on Mirror DB

Posted: 30 May 2013 10:44 PM PDT

Hi all - we are executing some code below to check if the instance is the principle and then run some code if it is.[code="sql"]IF exists(SELECT TOP 1 * FROM [BENTLEY].MASTER.sys.DATABASE_MIRRORINGWHERE mirroring_role_desc = 'PRINCIPAL') BEGIN insert into [sql1].[web].[dbo].test (id,object,compid,type,createdtime,updatedtime,requeststatus) SELECT * FROM [BENTLEY].[zane].[dbo].[test] WHERE requestStatus = 1 ENDELSE begin insert into [sql1].[web].[dbo].test (id,object,compid,type,createdtime,updatedtime,requeststatus) SELECT * FROM [FERRARI].[zane].[dbo].[test] WHERE requestStatus = 1 end[/code]However we always get the error below even though BENTLEY is the PRINCIPLE. The code above looks to be trying to validate the FERRARI linked server before the code even runs. Any ideas how to get this to work?ThanksOLE DB provider "SQLNCLI10" for linked server "FERRARI" returned message "Unspecified error".Msg 954, Level 14, State 1, Line 1The database "Zane" cannot be opened. It is acting as a mirror database.

Sql 2008 to 2008 R2 in-place upgrade failed and now have an "inactive" instance?

Posted: 31 May 2013 12:20 AM PDT

I attempted an upgrade yesterday which failed due to a ReportingServices error. I uninstalled ReportingServices since it was not being used and ran the R2 setup again but it says no features available to upgrade. The product discovery output is showing inactive instances. I am not sure how to uninstall this orphaned instance as I don't want to mistakenly remove the working instance.Has anyone experienced this and know how to properly get the upgrade running again?Product Instance Instance ID Feature Sql Server 2008 MSSQLSERVER MSSQL10.MSSQLSERVER Database Engine Services Sql Server 2008 MSSQLSERVER MSSQL10.MSSQLSERVER SQL Server Replication Sql Server 2008 MSSQLSERVER MSSQL10.MSSQLSERVER Full-Text Search Sql Server 2008 MSSQLSERVER MSAS10.MSSQLSERVER Analysis Services Sql Server 2008 MSSQLSERVER MSRS10.MSSQLSERVER Reporting Services Sql Server 2008 R2 MSSQLSERVER.INACTIVE MSSQL10_50.MSSQLSERVER Database Engine Services Sql Server 2008 R2 MSSQLSERVER.INACTIVE MSSQL10_50.MSSQLSERVER SQL Server Replication Sql Server 2008 R2 MSSQLSERVER.INACTIVE MSSQL10_50.MSSQLSERVER Full-Text Search Sql Server 2008 R2 MSSQLSERVER.INACTIVE MSAS10_50.MSSQLSERVER Analysis Services Sql Server 2008 R2 Management Tools - Basic Sql Server 2008 R2 Management Tools - Complete Sql Server 2008 R2 Client Tools Connectivity Sql Server 2008 R2 Client Tools Backwards Compatibility Sql Server 2008 R2 Client Tools SDK Sql Server 2008 R2 Integration Services Programs Installed:Microsoft SQL Server 2005 Backward compatibility Microsoft SQL Server 2008 (64-bit)Microsoft SQL Server 2008 R2 (64-bit)Microsoft SQL Server 2008 R2 Books OnlineMicrosoft SQL Server 2008 R2 Native ClientMicrosoft SQL Server 2008 R2 PoliciesMicrosoft SQL Server 2008 R2 Setup (English)Microsoft SQL Server 2008 Setup Support Files

SA account question

Posted: 30 May 2013 09:47 PM PDT

HiI have been tasked with 'the removal of the sa account in any environment'I have ran sp_blitz and it has come back with some helpful info.The results show a number of Dev's who currently have sysadmin level privilages and other logins such as 'replicationmaster', 'sqlserveragent','mssqlserver' with sysadmin. If i disable the sa account this will not effect these logins will it ? Therefore what is the difference between the actual sa account and logins that are assigned into the sysadmins - which is the same as the sa right ?

Refresh Test Environment

Posted: 30 May 2013 09:47 PM PDT

We have to refresh (restore) a number of databases at a time from our Production System to our Test Environment and I do not want to use either SSIS or the Maintencance Plan. I looked on the web for suitable scripts , but could not find any . Any ideas ? I don't want to write the restore script for either every database or have to change the backup file names manually.Regards.Lian

Greater/Less than on text column

Posted: 29 May 2013 01:48 AM PDT

Hey,I have a column (INVOICENO) which is nvarchar(12). It's this datatype as an invoice could be a credit note, so xxCN, or debit note so xxDN.All other values are just integers, starting a 1. How would I go about showing only say invoices 10-20, including anything CN, DN etc.1011DN121314151617CN18DN1920etc... not my application (3rd party) so cannot change datatype.Not hopeful, but thanks!

Linked server on SQL server 2008R2 connecting to SQL 2005 stopped working

Posted: 29 May 2013 10:42 PM PDT

Hi Got a linked server on SQL Server 2008R2 connecting to an SQL server 2005 that was working fine but now it stopped working.Both instances are clustered.Test of linked server gives this reply:TITLE: Microsoft SQL Server Management Studio ------------------------------ The test connection to the linked server failed. ------------------------------ ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ TCP Provider: An existing connection was forcibly closed by the remote host. Client unable to establish connection because an error was encountered during handshakes before login. Common causes include client attempting to connect to an unsupported version of SQL Server, server too busy to accept new connections or a resource limitation (memory or maximum allowed connections) on the server. OLE DB provider "SQLNCLI10" for linked server "Sxxxxx18\Sxxxxx18" returned message "Client unable to establish connection due to prelogin failure". OLE DB provider "SQLNCLI10" for linked server "Sxxxxx18\Sxxxxx18" returned message "Client unable to establish connection". (Microsoft SQL Server, Error: 10054) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.2500&EvtSrc=MSSQLServer&EvtID=10054&LinkId=20476 Linked server type is "sql server" and sql login for the linked server are verified ok.(connected SSMS)We have tried different scenarios to locate the problem but have not manage to do that, other than problem seems to be located at Sxxxxx18\Sxxxxx18Below test works fineConnecting by SSMS from physical box Syyyyy32 (Windows 2008), (Sxxxxx32\Sxxxxx32) 10.50.2500 to Sxxxxx18\Sxxxxx18 works fine.Create a linked server on Sxxxxx32\Sxxxxx32 to Sxxxxx17\Sxxxxx17 (9.0.5000) (physical node Syyyyy21 (Windows 2003), same physical node as for Sxxxxx18\Sxxxxx18) works fine.Create a linked server on Sxxxxx18\Sxxxxx18 (9.0.5000) to Sxxxxx32\Sxxxxx32 (1050.2500) works fine (reversed way of failing linked server) A Linked server on Sxxxxx90\Sxxxxx90 (9.0.5057) to Sxxxxx18\Sxxxxx18 (9.9.5000) works fineTest below are throwing same error as well:Trying to create new Linked server on Sxxxxx32\Sxxxxx32 (10.50.2500) to Sxxxxx18\Sxxxxx18 throw same errormessage as existing linked server when tested. Trying to create a linked server from another server, with version 10.50.4000 (Sxxxxx23) to Sxxxxx18\Sxxxxx18 (9.0.5000) throws same error.Physical node Syyyyy21 (Sxxxxx18\Sxxxxx18) has about 18 GB free memory and Sxxxxx18\Sxxxxx18 sqlsvr.exe using approx 300 MB and memorysetting for allows up to 3GBCpu is on approx 20-30%No newly applied patches that I can found.Anyone that got a clue on what it could be?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog