I am getting a linked server connectivity errors randomly. We have a set of DTS packages to pull data from OLTP (SQL 2000) to reporting server (SQL 2005) through linked server and both instances are on same server. The process is running ok on Acceptance server, and failing on production server. It is throwing the following error randomly, and it is getting fixed for a while after restarting SQL 2000 service.
An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "TCP Provider: The specified network name is no longer available. ". An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "OLE DB provider "SQLNCLI" for linked server "GIS_STG" returned message "Communication link failure".". An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "[DBNETLIB][ConnectionRead (recv()).]General network error. Check your network documentation.".
I have two SQL Server Instances on two servers. One server is my webserver and database server and the other one is just a database server. i have an application that calls a stored procedure located on the webserver/database server that runs a query on the OTHER database server. I use linked tables in my first instance to make the call possible.
Everything was working just fine for months until the database server was restarted and the IP address was changed. The name of the database is the same however and my first SQL Server instance has no problems running queries on the other databases tables. However, when you try to run the application i get the following error:
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection
I have mixed mode authentication selected and my security uses the security context with username=sa and password=sa.
So here's the weird part.
The application will only run correctly when i manually run a SQL command from my webserver's SQL Analyzer on the linked SQL Server. however, after a few minutes, the same error comes back!! so as a temporary fix, i scheduled a dts job to run a simple query on the linked server every two minutes, so the application keeps working! It's almost as if the webserver's sql server forgot that the linked server is there, and by running a simple query in query analyer, the connection gets refreshed and everythings normal again - for about 3 minutes!
I am completely stumped by whats happeneing and appreciate any help. Thank you.
Hi,We have 2 servers. Server1 is a 2000 box, with SP3 (yes I know it isnot up to date). Server2 is a 2005 box, SP2.I set up Server1 (2000) to have a linked server to Server2 (2005). Thereason I did this is because we are using a stored procedure onServer2 to send mail, as we have found that using mail on 2000 doesn'talways work as advertised.When I set up the linked server on the 2000 box, for security I justset it up to use a SQL Server user on the 2005 box. The SQL Serveruser on the 2005 box has permissions to run the stored procedure forsending mail.Here's the weird thing though. When calling the stored procedure onthe 2005 box from the 2000 box, sometimes we get an error that "Thedefault database cannot be opened", and the query does not run on the2005 box. However, it only happens *sometimes*. Other times, the queryruns fine.Since the problem seeemed to be with the default database, I changedthe SQL Server user on 2005 default database to the SAME database thatcontains the stored procedure.However, I just don't understand why it's even TRYING to open thedefault database, since when we called the linked server we are doingso as, and it's referencing the default database in the name:EXEC Server2.DefaultDatabase.dbo.StoredProcedureNameHowever, after changing the user's default database to"DefaultDatabase" as shown above, the query runs fine.Why are we having this problem? That is, if I change the defaultdatabase to something other than "DefaultDatabase", then the querydoesn't run, even though the database name is referenced in the abovequery??Obviously, this is not desireable, because that means we can only runqueries that are in "DefaultDatabase", which may not always be thecase.Thanks much
Hi,I'm using sql server 2000 sp4.I've 2 databases linked, an instance and my local.I'm getting two different errors when trying to update the remote table(local server) from the instance.There is only one row of data in the table with an identity field.1st sql:-UPDATE [local].[database].dbo.NUMBERS SET [f 1]=3This gives me the error:-Server: Msg 8180, Level 16, State 1, Line 1Statement(s) could not be prepared.Server: Msg 170, Level 15, State 1, Line 1Line 1: Incorrect syntax near '1'.If I was to remove the space from [f 1] and use [f1] it would workfine."select [f 1] from [dev001].[fashion Master].dbo.numbers"will return the correct valueAny Ideas ?2nd sql:-UPDATE [local].[database].dbo.NUMBERS SET [field1]=isnull([field1],0)+1This gives me the error:-Server: Msg 7306, Level 16, State 2, Line 1Could not open table '"fashion Master"."dbo"."NUMBERS"' from OLE DBprovider 'SQLOLEDB'. The provider could not support a row lookupposition. The provider indicates that conflicts occurred with otherproperties or requirements.[OLE/DB provider returned message: Multiple-step OLE DB operationgenerated errors. Check each OLE DB status value, if available. No workwas done.]OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowsetreturned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=TrueSTATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUTVALUE=600 STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyIDVALUE=True STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocateVALUE=True STATUS=DBPROPSTATUS_CONFLICTING],[PROPID=DBPROP_IRowsetChange VA...If I was to remove the isnull part, then it will work okAny ideas
I've got a linked server to an attomix database. When i send an erroneous statements to the server an error message is returned to the Message pane is SSMS as below:-
OLE DB provider "MSDASQL" for linked server "noble" returned message "[Noble Systems Corp.][ATOMIX ODBC Driver]Atomix error - (-217)Column (crap) not found in any table in the query.
".Msg 7215, Level 17, State 1, Line 5, Could not execute statement on remote server 'noble'.
I'm trying to store this error using the ERROR_MESSAGE() function but i only get the second statement ('Could not execute statement on remote server 'noble'.') rather than the real error in the first line. The first line statement is displayed in black as a message rather than red for an error.
I'm having an odd problem with some SQL CE statements I'm trying to use.
SELECT [PK Product ID] FROM [tblProduct Header] WHERE ([Description] = 'World''s Greatest 3D Mug - 18th Birthday')
Gives me the error message:
Major Error 0x8007007A, Minor Error 0
> SELECT [PK Product ID] FROM [tblProduct Header] WHERE ([Description] = 'World''s Greatest 3D Mug - 18th Birthday')
The data area passed to a system call is too small.
But this query does not:
SELECT [PK Product ID] FROM [tblProduct Header] WHERE ([Description] = 'Worlds Greatest 3D Mug - 18th Birthday')
and nor does this:
SELECT [PK Product ID] FROM [tblProduct Header] WHERE ([Description] = 'World''s Greatest 3D Mug 18th Birthday')
It's as though SQL CE doesn't like a combination of single quotes and hyphens in the criteria. If I remove the quote, it works, if I put the quote back in and remove the hypen it works. The data I'm trying to look up is:
World's Greatest 3D Mug - 18th Birthday
So I really need to be able to look for text that contains both quotes and hyphens.
I have a script that inserts rows from a linked server. It basically looks like this:
use mydb; go insert into my.table (col1, col2, ...) select col1, col2, ... from LinkedServer.db.my.table;
The DDL for both tables is identical: 550 columns (de-normalized from a data warehouse), of which 548 are varchar(max). The remaining two are varchar(255). (not my design! but unchangeable at the moment).
Running my query raises an error:
Cannot create a row of size 9948 which is greater than the allowable maximum row size of 8060. The statement has been terminated.
Now, inserting rows on the table on the LinkedServer has never been an issue. (Since most columns are varchar(max), SQL has the option to store the data off-row.) The data is there, intact and whole. However, when pulling it into my target server using a LinkedServer (4-part naming), I get the error.
Three pairs of eyes have confirmed that the table definitions are identical on both source and target.
What can be the reason(s) why I can't get data from a linked server using an ODBC datasource that works fast & fine from MS Access?
I have an ODBC connection (System DSN) configured for an Informix ODBC driver. The Test button (belonging to this driver-setup) reports a successfull connection test. Getting data from this database by linking tables in MS Access works fast and easy. But I have tried for many days now to setup a linked server from SQL Server (2005) Creation goes fine, but as soon as I issue a query, (e.g. 'select * from infrem723...remotetable' or using 'openquery') I get the following error: ---
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "infrem723" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "infrem723".
---
Why is Access able to read what "MSDASQL" cannot? I am desperate - can anybody help?Thanks a lot!
We are using SQL Server 2005 (9.0.3054 on Windows 2003 RC2 SP2 with clustering two instances. We are running an instance on both boxes. I have 2 linked servers to the same server on the first box (SQL1C) that use different SQL Logins to connect to the same box but to different databases. One uses the name of the server and one uses the name of the server with a 2 at the end of it. (servername and servername2). The second linked server was added recently and is used very frequently by the new application. The other thing is there is a server with the name servername2. They are SQL boxes but I am not sure what version or what they are running on. If it turns out to be important, I will ask.
Here's what is happening: When we fail over to the second box, up until recently, we had no problems. All linked servers worked well. But since we have added this new linked server, when we fail over, the linked server no longer works and we get an error like from the Cold Fusion application:
[Macromedia][SQLServer JDBC Driver][SQLServer]TCP Provider: An existing connection was forcibly closed by the remote host.
I got a similar error in EMS, but did not copy it to save it. Since this is a production server, I can't really just try things.
We checked to make sure all IPs from box1 and box2 are allowed access to the server and they are not using IP blocking for inside the university domain.
There are no errors in the SQL Logs. I checked the application logs and found no errors for the linked server. The only error I could find and I didn't think it was related but I will include it was this:
Closed event notification conversation endpoint with handle '{4F54167A-F3D0-DC11-97FE-000E0CB2D7CA}', due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8470</Code><Description>Remote service has been dropped.</Description></Error>'.
Any ideas? One of our pplications goes down when the cluster fails over now. Help PLEASE!
ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted 09/24/2007 15:00:00,Hourly Extract From OReSA,Error,0,INHSCTSTTOMVM82,Hourly Extract From OReSA,(Job outcome),,The
job failed. The Job was invoked by Schedule 7 (Hourly). The last step to run was step 1 (OReSA
Extract).,00:00:59,0,0,,,,0 09/24/2007 15:00:01,Hourly Extract From OReSA,Error,1,INHSCTSTTOMVM82,Hourly Extract From OReSA,OReSA
Extract,,Executed as user: INENVts_hia. hod call failed. End Error Error: 2007-09-24 15:00:58.93 Code:
After installing SP2 for SQL Serve 2005, I get strange errors when using either "Estimated Execution Plan" or "Actual Execution Plan".
Using "Estimated Execution Plan" generates this error An error occurred while executing batch. Error message is: Error processing execution plan results. The error message is: There is an error in XML document (1, 3998). Unexpected end of file while parsing has occurred. Line 1, position 3998.
Using "Actual Execution Plan" generates this error An error occurred while executing batch. Error message is: Error processing execution plan results. The error message is: There is an error in XML document (1, 4001). Unexpected end of file has occurred. The following elements are not closed: RelOp, ComputeScalar, RelOp, Update, RelOp, QueryPlan, StmtSimple, Statements, Batch, BatchSequence, ShowPlanXML. Line 1, position 4001.
All I do is testing this solutiondeclare@t table (dt datetime)
insert@t select'02-Jan-2007' union all select'01-Feb-2007' union all select'10-Feb-2007' union all select'28-Feb-2007' union all select'18-Mar-2007'
DECLARE@DaysRange INT, @NumOfCalls INT
SELECT@DaysRange = 35, @NumOfCalls = 4
SELECTt1.dt AS theDate FROM@t AS t1 CROSS JOIN@t AS t2 WHEREt2.dt >= DATEADD(day, DATEDIFF(day, 0, t1.dt), 0) AND t2.dt < DATEADD(day, DATEDIFF(day, 0, t1.dt), @DaysRange) OR t2.dt >= DATEADD(day, DATEDIFF(day, @DaysRange, t1.dt), 1) AND t2.dt < DATEADD(day, DATEDIFF(day, 0, t1.dt), 1) GROUP BYt1.dt HAVINGCOUNT(*) >= @NumOfCallsHas anyone else experienced this?
I have SQL 2000 Server that had a database called ABC and it has been moved to another server on 5-15-2007 I kept ABC database in Read_only mode for few days (just in case) on old server and finally dropped it on 5-20-2007 and I think I forgot to drop the associated logins. I started seeing login failure for user 'xyz' in error logs When I first noticed the login failure error in SQL Server log for login xyz, I deleted xyz login but it did not stop the errors.
I have been trying from then and no luck in identifying the cause/ resolving this issue. I have ran SQL profiler trace and caught the user hostnames, NTusername in few cases and Application Name and contacted the Application owner & user (who are in the trace) to stop windows service/ schedule jobs..anything that is pointing to old server but the bad luck is they are not aware of anything running or pointing to old server. The worst part is the user whose hostnames are shown in the trace have never used ABC database and do not have any idea.
Here is what I found in the profiler trace: TextData LoginName NTUserName HostName ApplicationName Login failed for user 'xyz' xyz AB00007 WAB000007 Microsoft (r) Windows Script Host
Today I have created the xyz login in the server and assigned model database with reader permission to see if log some different error but nothing new (the same login failure error) Finally, I had no solution other than restoring the ABC database back to my old server and set it in to Read_Only mode to stop these errors and Now I see the login 'xyz' firing the query against the database
* Any hint or pointers why this is happening and any possible solution on this?
In migrating our back end database from VFP8 to SQL we use linked servers to do a variety of tasks as well as some customized reporting applications. We are having issues with setting up the linked servers in SQL 2000 to VFP8 tables.
When you set up a linked server, locally it seems to work ok, sometimes we have authentication problems and have to switch back and forth between Windows and SQL which is annoying but manageable.
However when you start using the same SQL instance from other boxes by adding it in Enterprise Manager errors occur.
Error 7303: Could not initialize data source object of OLE DB provider 'vfpoledb.1'.
OLD DB Trace Error [OLE/DB Provider 'vfpoledb.1' IDBInitialize::Initialize returned 0x80040e21].
I have downloaded and installed the VFP8 OLE DB .dll from the Microsoft website but still have this issue. Can anyone shed any light?
I recently updated the datatype of a sproc parameter from bit to tinyint. When I executed the sproc with the updated parameters the sproc appeared to succeed and returned "1 row(s) affected" in the console. However, the update triggered by the sproc did not actually work.
The table column was a bit which only allows 0 or 1 and the sproc was passing a value of 2 so the table was rejecting this value. However, the sproc did not return an error and appeared to return success. So is there a way to configure the database or sproc to return an error message when this type of error occurs?
Hi , On my Desktop i registered Production Server in Enterprise Manager on that Server if i go to SecurityLinked Servers There is another Server is already mapped, when i am trying to see the Tables under that one of the Linked Server i am getting the Error message saying that "Error 17 SQL Server does not exist or access denied"
if i went to Production Server location and if i try to see the tables i am able to see properly, no problems why i am not able to see from my Desk top i am using the sa user while mapping the Production Server on my DESKTOP using (ENTERPRISE MANAGER)
And i check the Client Network Utility in the Alias using Named Pipe only, i changed to TCP/IP still same problem What might the Problem how can i see the Tables in Linked Server from my DESKTOP
I am using Linked Server in SQL Server 2008R2 connecting to a couple of Linked Servers.
I was able to connect Linked Servers, but I cannot point to a specific database in a Linked Server, also, I cannot rename Linked Server's name.
How to point the linked server to a specific database? How to rename the Linked Server?
The following is the code that I am using right now:
USE [master] GO EXEC master.dbo.sp_addlinkedserver    @server = N'Machine123Instance456',    @srvproduct=N'SQL Server' ; GO EXEC sp_addlinkedsrvlogin 'Machine123Instance456', 'false', NULL, 'username', 'password' Â
i m using sql server 2000. My problem is tht no matter what date i enter, sql server always displays it as 1900-1-1. This is happening on all columns which have been specified as datetime. Whts the problem & the solution to the problem?
I get this SQL-sever error in my application. I guess it has something to do with the declaration of namespaces? Does anyone know for sure? Thanks in advance!
The type 'System.Data.Selclient.SqlConnection' exists in both 'c:WINDOWSMicrosoft.NETFrameworkv2.0.50727System.Data.dll' and 'crogramMicrosoft Visual Studio 8Common7IDEPublicAssembliesSystem.Data.SqlClient.dll'
I have a script that creates a database and sets up everything required for merge replication. Merge replication is pull with a single parameterized row filter based on HOST_NAME() and joins on other tables. It works perfectly. I recently decided to drop all my identity columns and use rowguid's instead.
I modified my script by only changing the primary key column definitions, basicaly I went from "[Id] [int] NOT FOR REPLICATION IDENTITY (1,1) NOT NULL PRIMARY KEY CLUSTERED" to "[Id] [uniqueidentifier] ROWGUIDCOL NOT NULL DEFAULT NEWSEQUENTIALID() PRIMARY KEY CLUSTERED".
The script still works fine. Everything is executed succesfully and even the snapshot agent finishes it's job.
But the problem is, that when I want to create a partition I get the following error:
A value for the parameter @host_name was specified, but no articles in the publication use HOST_NAME() for parameterized filtering. (.Net SqlClient Data Provider). Microsoft Sql Server, Error: 20672
If I try to synchronize a subscription then the subscription is created succesfully, but I get an error while synchronizing stating: Merge agent was unable to determine if another subscription exists for the current partition.
And yes, I do have my filters set up 100% correctly.
The funny thing is, if I create the publication via Management Studio, then I don't get that error. But if I create the publication via Management Studio, immediately afterwards generate the script for it, delete the publication and create the publication from the generated script, I get the error again
From this I have concluded that no way can the problem be in my new script... Everything is dandy fine with the server as well, because the original scipt still works flawleslly.
Does anyone have any ideas what I could try to get things working? Has anyone even gotten merge replication set up via script, while database -> uses only rowguids, hostname parameterized filter and joins? Or should I just report this as a bug to Microsoft and go back to my original script, including the identity hell?
PS! I'm using Windows Server 2003 Standard x86 SP2, SQL Server 2005 Standard x86 SP2
I have a parent package that calls child packages inside a For Each container. When I debug/run the parent package (from VS), I get the following error message: Warning: The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
It appears to be failing while executing the child package. However, the logs (via the "progress" tab) for both the parent package and the child package show no errors other than the one listed above (and that shows in the parent package log). The child package appears to validate completely without error (all components are green and no error messages in the log). I turned on SSIS logging to a text file and see nothing in there either.
If I bump up the MaximumErrorCount in the parent package and in the Execute Package Task that calls the child package to 4 (to go one above the error count indicated in the message above), the whole thing executes sucessfully. I don't want to leave the Max Error Count set like this. Is there something I am missing? For example are there errors that do not get logged by default? I get some warnings, do a certain number of warnings equal an error?
I am trying to use a condition in a query with CTE but I got an error.It is simple enough but I just cannot get it work. Any help will be greatly appreciated! Msg 156, Level 15, State 1, Line 8Incorrect syntax near the keyword 'if'. declare @i as int set @i=1; with CTE_A as ( select 'B' )
if @i=1 ---this part get problem select * from CTE_A else select 'B'
I have web app and it is workin perfectly when i hawe SqlExpress started even if my database is in SqlServer (is that maybe that i did try membership provider if so how can i remove membership.) so i dont know how to solve this problem without using SqlExpress.. Can some one pleas help me. "1.0"?>
"true" to insert debugging symbols into the compiled page. Because this affects performance, set this value to true only during development. --> "true">
Hi, I have a SQL Server stored procedure that gets called in the ASP.NET application. For a while it will return results quickly. After an unknown amount of time the stored procedure starts taking forever to execute. If I go into Query Analyzer and execute the same stored procedure using the exact same input parameters the results return quickly. If I then go back into the application and run the code that executes the stored procedure, the results return quickly again. So basically every time the application call to the stored procedure begins to slow, I run that stored procedure in query analyzer and then it runs fine in the application again. Has anyone else experienced anything like this or have any ideas as to why this would happen?
We've been experiencing this strange error from our SQL server for about a month now and i've tried alot of things, visited alot of websites and manufactured a few possible solutions but nothing helps! Its very frustrating. This is the error i get when i try and expand the server tree:
A connection could not be established to [OurServerName]
Reason: SQL Server does not exist or access denied.
ConnectionOpen (Connect())..
Please verify SQL Server is running and check your SQL Server registration properties by clicking on [OurServerName] node) and try again.
What really gets to me is that everything works after i restart the server. Then things will go fine for about a day or two and then the same thing again. This i causing alot of downtime for us. Can anyone please just give me a suggestion? :confused:
We have a strange occurrence in one of our dev Sql server. Our platform is Sql Server 2000 on SP4. Build Number is 8.00.2039
TableA
Sid int, --Non-Clustered Index
Aid int,
Uid int,
€¦..
TableB
Iid int,
Sid int, ---No foreign key constraint to TableA.Sid but logically refers to that column. --Non-Clustered Index
€¦..
Now, we are working on schema changes, now TableB looks like
TableB
Iid int,
Uid int, ---Logically refers to TableA.Uid
€¦.. Sid in TableB is dropped and a new column Uid is added.
And there is no Sid in TableB.
We have an existing SP that refers to TableB.Sid like
Create proc sp_ab
As
Select Uid from TableA where Sid = (Select Sid from TableB where Iid = @Iid)
After making the schema changes listed above, if I try to compile this SP without making any changes to the procedure, it should fail. Surprisingly it passes compilation and also retrieves data using hash joins and doing table scans on TableA avoiding index seeks. This SP used to take less than second, now takes almost 90 seconds to retrieve data. I couldn't replicate this problem with another set of objects.
If I make changes to the SP sp_ab like
Create proc sp_ab
As
Select Uid from TableA where Uid = (Select Uid from TableB where Iid = @Iid), it runs less than a second as it should.
Whats surprising is, if I do 'Select Sid from TableB where Iid = 1 ' in Query analyzer, it throws error referring to invalid column Sid in TableB but couldn't throw this error when the sql is wrapped in a SP. Everything else in the DB and in the db server seems to be ok except this.
If I query Syscolumns table, I couldn't see any references to Sid in TableB after the schema chnages and not sure why the code in SP is not caught during compilation. Any clues.
We have a stored proc that accepts a date and uses that date in a filter. This procedure worked fine for a couple of weeks then started hanging. Basically, this date parameter is used in a where clause.
Our workaround: when we declare a new variable, set it equal to the parameter, then use the declared variable - that fixes it. Take away the declared variable and use the parameter instead and the proc chokes. But the paremeter and the declare variable are both datetime typed. Weird.
We are worried (partly because it worked without hanging for a while) that we are experiencing a larger issue. We are reporting off a database we denormalize and populate nightly with transactional data. A poor man's warehouse if you will, staging the data in a fast reporting format with the prep done each night. This database is wiped and re-populated each night. We have been testing this process with the same source data, so we are not growing our data at all (net result at end of each night is same data as previous day).
I think the source code here is overkill, but I enclosed the proc anyway. The way it is written below, it hangs. Replace these two uses of @AsOf in the proc with @test and the procedure almost instantly returns data. There is a third use of @AsOf that doesn't affect the issue one way or the other. This line is where the replacement fixes the problem:
--Breaks Where (OpenedDt < DateAdd(day, 1, @AsOf) or ClosedDt < DateAdd(day, 1, @AsOf)) and (PlanType = @PlanType or @PlanType is null)
--Fixed Where (OpenedDt < DateAdd(day, 1, @test) or ClosedDt < DateAdd(day, 1, @test)) and (PlanType = @PlanType or @PlanType is null)
If anyone has experienced this or knows some other symptoms to check for, we are all ears. This is scaring us because otherwise we have a working system on our hands and are near a delivery point. Help would be greatly appreciated.
Here is the complete proc...
ALTER PROCEDURE [dbo].[ClaimCountsByLineOfBusiness] -- Add the parameters for the stored procedure here @AsOf datetime, @PlanType varchar(50)
AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
DECLARE @DataFreshness datetime Select @DataFreshness = DataFreshness From metaReportList Where StoredProcedure = 'ClaimCountsByLineOfBusiness'
If @AsOf is null SET @AsOf = Convert(datetime, Convert(varchar(10), GetDate(), 101))
DECLARE @test datetime SET @test = @AsOf
-- Insert statements for procedure here Select PolicyGroup, Lines, SUM(CASE WHEN HasPayment = 1 and OpenClosed <= 0 THEN 1 ELSE 0 END) as ClosedClaimswithPayment, SUM(CASE WHEN HasPayment = 0 and OpenClosed <= 0 THEN 1 ELSE 0 END) as ClosedClaimswithoutPayment, SUM(CASE WHEN OpenClosed > 0 THEN 1 ELSE 0 END) as ClaimsOpen, Count(ClaimSummary.ClaimNumber) as ClaimsReported, AccidentYear, ReportingYear, --Return the value of the parameters in a friendly way (if null) Convert(varchar(10), @Asof, 101) as AsOfParameter, IsNull(@PlanType, 'any value') as PlanTypeParameter, @DataFreshness as DataFreshness
FROM (Select ClaimNumber, Max(OpenedDt) as OpenedDt, HasPayment, PolicyGroup, Lines, AccidentYear, ReportingYear From dbo.factClaimSummary Where (OpenedDt < DateAdd(day, 1, @AsOf) or ClosedDt < DateAdd(day, 1, @AsOf)) and (PlanType = @PlanType or @PlanType is null) Group by ClaimNumber, HasPayment, PolicyGroup, Lines, AccidentYear, ReportingYear) as ClaimSummary
INNER JOIN
(Select ClaimNumber, SUM(CASE WHEN OpenedDt is not null THEN 1 ELSE -1 END) as OpenClosed From dbo.factClaimSummary Group by claimnumber) Status ON ClaimSummary.ClaimNumber = Status.ClaimNumber
Group by PolicyGroup, Lines, AccidentYear, ReportingYear Order by PolicyGroup, Lines END
Can anyone help me solve why my server is locking up.
We're running sql server 2005 ent on a 2 cpu dual core server. With 3gig. Once each the last couple weeks, the machine has hung and the only thing I can find that's suspicious is on the sql server log. It's below. After the excerpt is about 50 more different memory errors. As far as I know there's no ssl configured for log in, it mostly uses private lan cards for security between it and the web/application server.
05/31/2006 14:39:48,spid2s,Unknown,MEMORYCLERK_SQLQUERYEXEC (Total) <nl/> VM Reserved = 0 KB <nl/> VM Committed = 0 KB <nl/> AWE Allocated = 0 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 1000 KB<nl/> MultiPage Allocator = 312 KB 05/31/2006 14:39:48,spid2s,Unknown,MEMORYCLERK_SQLBUFFERPOOL (Total) <nl/> VM Reserved = 4214784 KB <nl/> VM Committed = 4198400 KB <nl/> AWE Allocated = 0 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 0 KB<nl/> MultiPage Allocator = 40 KB 05/31/2006 14:39:48,spid2s,Unknown,MEMORYCLERK_SQLGENERAL (Total) <nl/> VM Reserved = 0 KB <nl/> VM Committed = 0 KB <nl/> AWE Allocated = 0 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 30408 KB<nl/> MultiPage Allocator = 4528 KB 05/31/2006 14:39:48,spid2s,Unknown,Memory node Id = 0 <nl/> VM Reserved = 4261240 KB <nl/> VM Committed = 4244224 KB <nl/> AWE Allocated = 0 KB <nl/> SinglePage Allocator = 2403592 KB<nl/> MultiPage Allocator = 27392 KB 05/31/2006 14:39:48,spid2s,Unknown,Memory Manager <nl/> VM Reserved = 4266872 KB<nl/> VM Committed = 4249768 KB <nl/> AWE Allocated = 0 KB <nl/> Reserved Memory = 1024 KB <nl/> Reserved Memory In Use = 0 KB 05/31/2006 14:39:48,spid2s,Unknown,LazyWriter: warning<c/> no free buffers found. 05/31/2006 14:34:30,Logon,Unknown,The server was unable to load the SSL provider library needed to log in; the connection has been closed. SSL is used to encrypt either the login sequence or all communications<c/> depending on how the administrator has configured the server. See Books Online for information on this error message: 0x2746. [CLIENT: 10.10.10.207] 05/31/2006 14:34:30,Logon,Unknown,Error: 17194<c/> Severity: 16<c/> State: 1. 05/31/2006 14:03:05,Backup,Unknown,Database differential changes were backed up. Database: PRIOS_New<c/> creation date(time): 2006/03/02(07:41:21)<c/> pages dumped: 156468<c/> first LSN: 2317:48292:175<c/> last LSN: 2317:49978:1<c/> full backup LSN: 2315:126242:184<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'D:DiffBackupPRIOS_New_backup_200605311400.bak'}). This is an informational message. No user action is required.
I€™m suffering a queer behaviour when I use BIDS. Concretely, when I open a dtsx from my project (it has 10 packages) many times Sequence Container and Data Flow tasks are invisible. I mean, its lines are not visible at all whereas its titles are. I mean, what you see is just a white box€¦
Then, I€™m gonna Data Flow layer and I have to do double-clik over the tasks and are visible but on Control Flow I don€™t see how to solve.
Curiously in our development and production server such behaviour doesn€™t happen (we are accessing by mean Terminal Server from our workstations)
How odd!. Everything is fine except this.
I want to remark you that such project has been copied from the server, this is, these packages are been built on the server
