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?
I have a strange performance question hopefully someone can clarifyfor me. I take a production database and make a copy of it, calledtest, on the same instance on the same server both running at the sametime. All the same things are running for each database and no one isusing each database but me. From Query Analyzer I run a SQL againstproduction three times in a row and it takes 1 minute 40 seconds onthe last/best run. I then run the same SQL against the test copy andrun it three times in a row and the last/best time is 12 seconds. Cananyone explain this behavior? If so, I hope this points to something Ican do to my production database to get the same performance.Thanks,MGB
Hi, I have a strange performance issue. I have the following query which takes 40+ seconds to run. SELECT Count(x) FROM view WHERE x = 347 AND y = 10056 AND z = 2 AND w = '01' But if i switch it to below, the query returns the one result quickly (1 second).SELECT x FROM view WHERE x = 347 AND y = 10056 AND z = 2 AND w = '01' If the view is returning results quickly, why is it so much trouble for SQL to run the aggregate function on the results?Any help is appreciated. -Brian
Hi,I have a really interesting one for you guys...SQL Server 2000 sp3 on Windows Server 2003If I run this query:declare@find varchar(50)SET @find = 'TTLD0006423203'SELECT TOP 250ConsignmentID,c.CreatedFROM tblConsignment c WITH (NOLOCK)WHERE c.ConNoteNum LIKE @find + '%'ORDER BY c.Created DESCIt takes 5 - 7 seconds with an Index Scan on the Consignment TableHOWEVER, if I run either of the next two queries below they are instant(under 1 second) with no scan only an Index Seek ..declare@find2 varchar(50),@SQL nvarchar(4000)SET @find2 = 'TTLD0006423203'SET @SQL = 'SELECT TOP 250ConsignmentID,c.CreatedFROM Tranzbranch_archive.dbo.tblConsignment c WITH (NOLOCK)LEFT JOIN tblCustomer cu WITH (NOLOCK) ON c.FreightPayerCode =cu.CustCodeWHERE c.ConNoteNum LIKE ''' + @find2 + '%''ORDER BY c.Created DESC'execute sp_executesql @stmt = @SQLORSELECT TOP 250ConsignmentID,c.CreatedFROM tranzbranch_archive.dbo.tblConsignment c WITH (NOLOCK)LEFT JOIN tblCustomer cu WITH (NOLOCK) ON c.FreightPayerCode =cu.CustCodeWHERE c.ConNoteNum LIKE 'ttld0006423203%'ORDER BY c.Created DESCCan you please help me as this is causing Huge issues in our Live systemand I really don't want to rewrite 400+ stored procedures!!!!Thank you thank you thank you in advance....:-)Auday*** Sent via Developersdex http://www.developersdex.com ***
I got a strange performance issue with my existing application. The application run fine for a period of time. However, it got timeout error every time now when the number of records have been grown to a certain size.
The program uses Typed DataSet to access SQL Server 2005 database. The connection is made over a VPN. The same program and SQL run in a LAN environment performs not too bad. However, when it is run over the VPN, the CPU and I/O usage jumped to very high numbers. In the SQL Profiler, I found that the duration of execution is less than 20ms in LAN environment while the VPN will give me a figure around 30000ms (This means timeout).
I don't want to simply increase the timeout of my connection and command in order to solve this problem temporary. What's the actual cause of such huge difference in the performance?
I'm using a SQL statement with a CTE to recursively get all node-ids from a tree beginning with a root node. In a select statement I'm using this CTE to get in a SELECT ... WHERE nodeID IN (SELECT ID FROM CTE_Nodes) statement data that is according to the nodes related to the root-node.
In our ASP.NET 2.0 application these statements are very slow or time out with an Exception. When I'm executing the same statement in a Management Studio the statement executes in less than one second.
BTW, we're using SQL Server 2005 Standard Ed. (9.0.3050 + 9.0.3054) in SQL Server 2000 compatibility mode with SQL Authentication.
I'm a bit frustrated, because the statements are the same.
Thanks in advance,
Klaus
Update:
BTW, the SQL Server is on a server machine and the Management Studio and the ASP.NET application on my developer machine. For data access we're using Enterprise Library 2.0 with System.Data.SqlClient.
Which possibilities do we have to trace this issue? Please help.
Jezemine, No, the number of reads is approximately the same. I can also confirm the disk read speed is the same on the test vs. production server. Update stats is run regularly on the production server - as I test, I ran sp_updatestats and then immediately ran the query a few times but it didn't affect the duration. Apart from the durations in the profiler traces, I can't see any differences. Clearly, something is causing the increased duration on the prod server but I don't know where to look to find it. It's definitely within SQL Server 2000.
Hello!I have this piece of SQL code:UPDATE aSET Field1 = c.Field1FROM aINNER JOIN b ON a.GUID1 = b.GUID1INNER JOIN c ON b.GUID2 = c.GUID2WHERE c.Type = 1AND @date BETWEEN b.DateFrom AND b.DateToThis query takes hours to complete.Now while trying to find out what's causing the poor performance (itsurely looks simple enough!) I've rewritten it to use temp tables:SELECT a.GUID1, a.Field1, c.Type, b.DateFrom, b.DateTo INTO #temptableFROM aINNER JOIN b ON a.GUID1 = b.GUID1INNER JOIN c ON b.GUID2 = c.GUID2WHERE c.Type = 1AND @date BETWEEN b.DateFrom AND b.DateToUPDATE a SET Field1 = subsel.Field1FROM (SELECT * FROM #temptable) AS subselWHERE subsel.GUID1 = a.GUID1Now it completes in 10 seconds.My question is why? Am I wrong in saying that the two batches aboveproduce same results? Is there something I've missed about the UPDATEFROM syntax? Why would the first query perform THAT poorly?Table sizes:a: 24k rowsb: 268k rowsc: 260k rowsGUIDs are of type uniqueidentifier.Any answers appreciated!Regards,// Richard
Not sure if this question belongs in this area or the server area but I'll start here. Here is my problem. We have an C/C++ app that was originally written for SQL 2000 and uses DBLibrary. We have converted it to SQL 2005 and are using ODBC/Native client to access the SQL 2005 database. This all works great. So we were doing some performance testing and we noticed that our update performance seems slower in the SQL 2005/ODBC case than it did in the SQL 2000/DBLibrary case. Inserts and queries all perform great, in fact the inserts are significantly faster in the SQL 2005/ODBC case which is good. We are using Array inserts/updates/queries wherever possible as this is faster obviously. In our update case, it takes 1.14 seconds to update 2000 rows in table in the SQL 2005/ODBC case, while SQL 2000/DBLibrary case takes .39 seconds to the exact same thing. The table in question is a 12 column table with all integer columns, with an index on the first three columns.
So my question is this. Is there something different about Array Updates in SQL 2005 ? I've looked thru the list of hot fixes available since SQL 2005 SP2 and haven't seen anything that directly mentions Updates so I'm hesitant to go off and start applying the hot fixes to our server to see if the behavior changes. It seems strange to me that Array Inserts would be very fast, but Updates wouldn't be. I've checked the ODBC Data Source and we aren't doing anything fancy there. I'm not actually even sure if this problem is client side or server side as I said earlier.
If anyone has any ideas or thoughts that would be great since this is really bugging me.
Here is a sample of what our C code is doing. This is simplied and I've removed a lot of our own code but these are the SQL calls that we are making in order so maybe can see something wrong I'm doing.
//----Sample Code ------------------------------------------------------------------------------------- pSQL = "Update bob set VV=? where VI=?" // not done this way actually in our code but just to show the update text
status = SQLPrepare ( hS, (UCHAR *)pSQL, SQL_NTS );
// Called twice with nCol = 1 and then with nCol = 2 status = SQLSetParam ( hS, abs(nCol), cType, // cType = 5 = SQL_C_LONG sqlType, // sqlType = 4 = SQL_INTEGER 38, // size needed in case the column is numeric or decimal 0, p16Data, // Pointer to my array of data NULL );
I have a simple stored procedure that send a message on a conversation:
Code Snippet
CREATE PROCEDURE [Vxml].[sp_SendMessageToWarehouse] -- Add the parameters for the stored procedure here @Message XML, @EntityId uniqueidentifier, @ConversationHandle uniqueidentifier OUTPUT AS BEGIN BEGIN TRY ;SEND ON CONVERSATION @ConversationHandle MESSAGE TYPE VxmlEngineXMLMessage(@Message); END TRY BEGIN CATCH INSERT INTO [Vxml].SBError (ErrorMsg) VALUES (N'Error <' + ERROR_MESSAGE() + N'> for entity <' + cast(@EntityId as NVARCHAR(max)) + N'> on conversation <)' + cast(@ConversationHandle as NVARCHAR(max)) + N'> with body ' + cast(@Message as NVARCHAR(max))) END CATCH; END
This completes in less than 100ms (avg 30ms) except once in about 10000 executes (only one simultaneous execute), then this takes about 3 seconds to complete.
When performing the same test and calling the stored procedure simultaneously from 5 to 20 threads, we see up to 3 such spikes (in 10000 executes) taking 2 to 4 seconds.
Conversations are created before the test and closed after. No ODBC errors occur and the SBError table is empty.This is run on an SQLEXPRESS the conversation target is a SQL Server 2005 (standard edition)
Can anyone explain these strange performance spikes ?
Hello everyone, I am hoping someone can help me with this problem. Iwill say up front that I am not a SQL Server DBA, I am a developer. Ihave an application that sends about 25 simultaneous queries to a SQLServer 2000 Standard Edition SP4 running on Windows 2000 Server with2.5 GB of memory. About 11 of these queries are over views (all overthe same table) and these queries are all done from JDBC but I am notsure that matters. Anyway, initially I had no problem with thesequeries on the tables and the views with about 4 years of information(I don't know how many rows off hand). Then we changed the tables toreplicated tables from another server and that increased the amount ofdata to 15 years worth and also required a simple inner join on 2columns to another table for those views.Now here is the issue. After times of inactivity or other times duringthe day with enough time between my test query run I get what lookslike blocking behavior on the queries to the views (remember these allgo to the same tables). I run my 25 queries and the 11 view queriesall take about 120 seconds each to return (they all are withinmilliseconds of each other like they all sat there and then werereleased for processing at the same time). The rest of the queries arefine. Now if I turn around and immediately run the 25 queries again,they all come back in a few seconds which is the normal amount of time.Also, if I run a query on one of views first (just one) and then runthe 25 queries they all come back in a few seconds as well.This tells me that some caching must be involved since the times are sodifferent between identical queries but I would expect that one of thequeries would cache and thus take longer but the other 10 would befast, not all block for 2 minutes. What is more puzzling is that thisbehavior didn't occur before where now the only differences are:1) 3 times more data (but that shouldn't cause a difference from 3seconds to 120 and all tables have been through the index wizard with aSQL trace file to recommend indexes)2) There is now a join between 2 tables where there wasn't before3) The tables are replicated throughout the day.I would appreciate any insight into this problem as 120 seconds is waytoo long to wait. Thanks in Advance.Chris
I have the following queries that run on a view called EntrySummary:
1)
exec sp_executesql N'SELECT COUNT (1) FROM [dbo].[EntrySummary] WHERE [EntrySummary].[SubmissionStatusID] = @SubmissionStatusID0 AND [EntrySummary].[CreatedBy] = @CreatedBy1',N'@SubmissionStatusID0 int,@CreatedBy1 nvarchar(20)',@SubmissionStatusID0=4,@CreatedBy1=N'domainaperson'
2) exec sp_executesql N'SELECT COUNT (1) FROM [dbo].[EntrySummary] WHERE [EntrySummary].[CreatedBy] = @CreatedBy1 AND [EntrySummary].[SubmissionStatusID] = @SubmissionStatusID0',N'@SubmissionStatusID0 int,@CreatedBy1 nvarchar(20)',@SubmissionStatusID0=4,@CreatedBy1=N'domainaperson'
(The only difference between the two queries being the order of the where clauses)
Both return the correct answer (4144), but Query 2 takes 10-15 seconds whereas Query 1 takes < second. If the same query is resubmitted several times, this doesn't affect the times.
(The vast majority of the records have a status of 4, but only about 3% will be created by the person).
Replacing Count(1) with count(*) makes both queries return quickly.
Is sp_executesql creating poor execution plans ? Can anyone explain this behaviour?
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 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">
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 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€™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
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.
I'm a wee bit of a newbie concerning DTS and have inherited a db with a DTS containing a Copy SQL Server Objects task set to run nightly. Essentially, it does an informal backup of some core data.
Recently, I was notified that one of the tables it copies over is now empty on the Destination db. The DTS shows that it runs successfully with no errors logged, the table in question IS selected to be copied from the Source database, there IS data in the Source database table, and every other table in the Destination database is populated appropriately.
Any ideas on what would cause this one table to be empty without generating any errors?
Wondering if anyone can help with a strange exception thrown while using the SqlBulkCopy class. I am using the class to transfer records from a DataTable in memory (approx 11,000 rows) into a SQL Server 2005 table.
Initially, the WriteToServer method was timing out a la KB913177 (http://support.microsoft.com/default.aspx/kb/913177), however I downloaded the hotfix, which eliminated this issue.
Now, I get a new exception thrown, as follows:
System.Data.SqlClient.SqlException: OLE DB provider 'STREAM' for linked server '(null)' returned invalid data for column '[!BulkInsert].Power_Avg'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal() at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount) at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState) at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table)
I can't see anything wrong with the data I have. The column "Power_Avg" is of type "float". When forming the DataTable, I cast my data to float expcilitly in C#.
Other things to know:
I am using SQL Server Express (2005) This same code works fine with SQL Server 2000 (MSDE) My code makes all modifications inside a single transaction of type "Snapshot" (I have activated SNAPSHOT READ COMMITTED in the database) I have not activated MARS in the connection
A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=233&LinkId=20476
When I consult the error log I see:
Could not connect because the maximum number of '4' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: <local machine>] 2007-01-30 04:53:14.93 Logon Error: 17809, Severity: 20, State: 3.
The log error message doesn't make any sense because there aren't 4 user connections.
After I received the error message, mgmt studio still lets me access the database make changes, but I cannot connect via my application. Any ideas? Thanks!
Hi There,I am having a strange problem with my identity column...... 1). I have a table of Products that have an identity column auto-incremented by 1. 2). I have my asp program working quite well in which the Data entry operators are adding the products into my database..... and they do not have any interface through which they can delete products........ 3). My Database is running at Web server(MS SQL Server)My problem is that when i cehcked my database.... there were around 1000 records but the auto increment number have reached to 1500. and when i checked in details then i saw that Auto number column is being skipped certain numbers..... like one entry is 1478 then the next one comes to be 1482..... and 1508 to 1516........ Its happening alot of times and it seems that SQL Server is skipping some numbers............Since it is Auto-Number so i do not have control over it through my code.... So i think the coding might not be the problem...... I have set Identity seed as well as Identity Increment both to 1.Is there any thing that you can suggest me to do??(Thanx)
we are working on a web application created in ASP & SQL 2000 environment. In the code , we have a single connectionstring to connect database. The application was running fine for the past 3 years, but all of a sudden we are getting "timeout expired" error only in someparts of the application but not on all database access. The code which raises this error is a bit large which need to look into table of 8k recs with more conditions. The same sql query took 1:01 minutes to execute in query qnqlyser. I changes the script timeout to 900secs, I herd we need to upgrade MDAC or we need to change connection timeout or we need to cahnge the code.
we cannot change the query, it is the most possible way we could get that.
Hi I've got a sql server 2000 database that when running is runnign fine. About 9 months ago I altered one of the stored procedures and ever since then when the machine is rebooted the stored procedure is "reverted" back to the old sproc... ??? is there any way I can recrete a sproc in a job that runs every day?? why would it be doing this?
