SQL 2005 X64 Working Set Error
Apr 7, 2008
I have the same error occuring each day at the same exact time on my mirror server. I looked online, and there are tons of references online to people having this issue, but not so many with resolutions. The server is identical to its Primary server in every aspect. It has the same patches, same exact hardware, it was originally going to be a cluster until some other issues turned it into a mirror set, and the Primary doesn't have this issue, just the mirror which is why it doesn't make sense.
Everyday between 17:56:30 and 17:58:30 I get this error
Date 4/6/2008 5:57:56 PM
Log SQL Server (Current - 4/7/2008 12:00:00 AM)
Source spid1s
Message
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 2361601 seconds. Working set (KB): 147436, committed (KB): 461280, memory utilization: 31%.
There is no plausible reason for this to occur on this particular server and not on the Primary which is doing all the work. I read the blog on PSS about this, as well as some of the MVP blogs. Is there anything else I can look at for this? Some of the existing references I have looked at are below.
http://msmvps.com/blogs/omar/archive/2007/09/19/a-significant-part-of-sql-server-process-memory-has-been-paged-out-this-may-result-in-performance-degradation.aspx
http://blogs.msdn.com/psssql/archive/2007/05/31/the-sql-server-working-set-message.aspx
http://support.microsoft.com/kb/918483/en-us
No matter what I do the problem persists. I gave up on this a while back since it was a mirror only, and I was busy working on other projects, but now that I have a bit of time to look at it again I am going to. Any one got any ideas?
View 8 Replies
ADVERTISEMENT
Mar 3, 2006
hi
I had a view in which I did something like this
isnull(fld,val) as 'alias'
when I assign a value to this in the client (vb 6.0) it works ok in sql2000 but fails in 2005.
When I change the query to fld as 'alias' then it works ok in sql 2005 .
why ?? I still have sql 2000 (8.0) compatability.
Also some queries which are pretty badly written run on sql 2000 but dont run at all in sql 2005 ???
any clues or answers ?? it is some configuration issue ?
Thanks in advance.
View 5 Replies
View Related
Feb 2, 2007
I am update/inserting records threw a web form in vb.net. I need to insert 'NULL' into my microsoft sql server database. I am not talking about the below line of code where website is the name of my paramater. If i do that it will just place a blank into that field in the database. If i dont enter anything into that textbox I want it to to say NULL in that field. So if I go into the actual table in the SQL Server Management Studio and look at the website field of the recored I just added or updated and did not type anything into the web site textbox it needs to say NULL. I also tried the second line of code but that places a single quote in front and behind NULL. So the field will have the value 'NULL'.
website.value = txtwebsite.text.tostirng
if txtwebsite.text ="" then
website.value ="NULL"
end if
The reason why I need the NULL there is because I bind the website filed to a hyperlink template in a gridview. The actual text of the hyperlink is bound to PAYER which is a name of a insurance payer but the navigateto is bound to the website field. If i do not enter any data into the website field and it stays as NULL, then when my gridview is loaded, payers that dont have a website will not be underlined and user wont have the option to click on them since there is no value for the navigateto. But if i use my form to update/add a payer and leave the website textbox blank in puts a blank into that field in my database and when it loads that new row into the gridview the PAYER is underlined and u can click on it but it will just take u to the web site is unavailable page. So is there anyway to actaull have NULL placed into a filed and not just blank space
View 3 Replies
View Related
Dec 15, 2006
dear friends,
i started using Asp.net for developing webparts, web parts automatically connect to a database to be created and saved in the database.
i had already Sql Server 2005 Express Edition installed so my webpart page ran and automatically created the database "AspNetDb" in the "App_Data" folder.
but when i uninstalled Sql Server 2005 Express Edition and then installed the Sql Server 2005 Enterprise Edition, it gave me the following Error 26 as below :
--------------------------------------------------------- Beginning of Error Message --------------------------------------------------
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. SQLExpress database file auto-creation error:
The connection string specifies a local Sql Server Express instance using a database location within the applications App_Data directory. The provider attempted to automatically create the application services database because the provider determined that the database does not exist. The following configuration requirements are necessary to successfully check for existence of the application services database and automatically create the application services database:
If the applications App_Data directory does not already exist, the web server account must have read and write access to the applications directory. This is necessary because the web server account will automatically create the App_Data directory if it does not already exist.
If the applications App_Data directory already exists, the web server account only requires read and write access to the applications App_Data directory. This is necessary because the web server account will attempt to verify that the Sql Server Express database already exists within the applications App_Data directory. Revoking read access on the App_Data directory from the web server account will prevent the provider from correctly determining if the Sql Server Express database already exists. This will cause an error when the provider attempts to create a duplicate of an already existing database. Write access is required because the web server accounts credentials are used when creating the new database.
Sql Server Express must be installed on the machine.
The process identity for the web server account must have a local user profile. See the readme document for details on how to create a local user profile for both machine and domain accounts.
--------------------------------------------------------- End of Error Message --------------------------------------------------
then when i checked the Machine.config file, i found this....
<connectionStrings> <add name="LocalSqlServer" connectionString="data source=.SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient" /> </connectionStrings>
then i thought it's becoz, of the datasource thing that the Machine.Config file supports by default the default free edition of Sql Server 2005 which is the Sql Server 2005 Express Edition.
but friend in my company we use Sql Server 2005 Enterprise Edition, so can anybody pls guide me and get me out of this soup on helping me to use Sql Server 2005 Enterprise Edition by modifying either 1) Machine.Config File and/or 2) Web.Config file and/or 3) any other way, but please help i want to do this, please......
regards and thanks
Gurjit Singh
View 2 Replies
View Related
Apr 28, 2007
I did a backup using SQL Server 2005 Express on 1 machine, and when I tried to restore to SQL Server 2005 Developer Edition (included with VS 2005 Pro), it gives me the following error:
TITLE: Microsoft SQL Server Management Studio------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------ADDITIONAL INFORMATION:
The backed-up database has on-disk structure version 611. The server supports version 539 and cannot restore or upgrade this database.RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server, Error: 3169)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.2039&EvtSrc=MSSQLServer&EvtID=3169&LinkId=20476
------------------------------BUTTONS:
OK------------------------------
I looked at the link and it is talking about SQL Server 7 and 2000. I read a few posts about not being able to restore 2005 to 2000, but I have 2005 on both machines. I'm not sure the best way to check, but I know I installed the SQL Server 2005 Developer edition to the new machine recently. Any ideas?
Thanks!
View 1 Replies
View Related
Feb 9, 2007
Hi
Should ASP.Net 1.x work togheter with sql 2005 without problems? I have try to open a web project but I got the following error:
Cannot open database "crm" requested by the login. The login failed. Login failed for user 'crmuser'.
This crmuser is "promoted" to owner of the crm base, but still I got the problem.
I upgrade this web project to ASP.Net 2.0 and I don't have the login problems, that's why I'm wondring.
Hope someone can answear me on this question. Thanks!
Jan
View 3 Replies
View Related
May 13, 2008
I have the code below to text. Unfortunately, the result I keep getting when I run my query does not make sense to me. Can anyone please help me ? Thanks in advance
DECLARE @TOtalPoints AS INTEGER
DECLARE @TOtalPointsFinal AS INTEGER
SET @TOtalPoints = CONVERT( INTEGER, (SELECT COALESCE(SUM(AMOUNT),0)
FROM Transactions WHERE USERID = 4337011
AND Amount < 20 AND UPPER([Status]) ='SUCCESS' ))
SELECT @TOtalPointsFinal = @TotalPoints%(CONVERT(INTEGER, 20))
PRINT @TOtalPoints
PRINT @TOtalPointsFinal
QUERY OUTPUT MESSAGE
17
17
View 3 Replies
View Related
Jun 1, 2007
When I'm in query analyzer writing a stored procedure, if I make a mistake it will frequently go on and create the procedure without any warnings...I am on 9.00.3042.00, developer edition when this happens.
i.e., i had a stored procedure that had input parameters, and I declared a parameter @xyz nvarchar=NULL. It created the stored procedure anyway, and parsed it correctly as well. In fact, clicking the little 'parse' checkmark doesn't appear to do anything at all...
I didn't find out about my error until I ran the script in a 2000 database.
Is this a known defect? Will there be a patch? Is there a setting I don't yet know about to fix this?
View 2 Replies
View Related
Jan 29, 2007
When supporting interactive grouping in reports using an execution snapshot, In 2000 I used:
<UsedInQuery>False</UsedInQuery> in the RDL below the appopriate Report Paramater names as well as using stored procedure based reports with Prompts set to certain paramaters. These prompted parameters would still show up after an excecution snapshot was run with default parameters. The UsedInQuery was just used to determine if it would rerun vs database or not.
I'm trying to do the same in 2005 but can't seem to get it to work anymore.
Per books online:
"The UsedInQuery element must contain one of the following string values:
True
False
Auto
You use the UsedInQuery to determine whether queries containing parameters that are associated with the report parameter are executed if the report parameter changes. This impacts whether the parameter can be changed in snapshot reports. Setting the value to Auto indicates that the report server attempts to detect whether the parameter is used in a query. "
Any suggestions on how to get interactive grouping to work with execution snapshots based on stored procedure reports in 2005? In 2005, the prompt Parameters are showing up as not enabled even though I've used false for UsedInQuery when I base the report from stored proc. It looks like they may work if the report is not based off of a store proc. I'll continue to test the query side to confirm the problem is only with stored proc based reports. Thanks!
View 2 Replies
View Related
Aug 22, 2000
Someone asked about this and I was wondering if anyone has any idea.
Thanks in advance.
>>Yesterday in my Windows NT 4.0 Event Viewer I got a message and then I went to the the SQL Server logs and did not find and errors. Microsoft's web site did not have any insite on this issue so I need your help.
>>
>>
>>Category: Kernel
>>17122:
>>initdata: Warning: could not set working size to 259200KB.
>>
>>Please help this seems to be causing some ODBC connection problems to the databases.
View 1 Replies
View Related
Jul 5, 2006
Hi There
I am having a little trouble with something very simple:
I have a stored procedure with the following sql in it:
UPDATE mfsWS_CustNewCustomer SET Result_Id = 0 WHERE QueryGUID = @Guid
IF (@@ERROR <> 0)
BEGIN
RAISERROR ('Error setting Result_Id', 16, 1)
UPDATE mfsWS_CustNewCustomer SET Result_Id = 1 WHERE QueryGUID = @Guid
RETURN
END
To test if this works i SET the Result_Id = 'X' this is a smallint column so this should not work.
My issue is that all i get is the error:
cannot convert char to numeric etc etc
However the @@ERROR logic is not executed , my error is not raised and the update does nto happen, it si as though the entire sp aborts at the point where i try do the bogus update.
Is there something i should SET at the beginning of my sp or something ? How do i ensure that any error will be passed to the logic that checks @@ERROR and performs the appropriate actions ? This is SS2000, so i cannot use try catch.
Thanx
View 10 Replies
View Related
Aug 23, 2007
I wrote some views in sql2000 and all was well. I upgraded to sql 2005 and non of the order by clauses work when I run the views directly. If I open the view up in desing mode and run them, they work.
Any Ideas?
Thanks,
Rick
View 9 Replies
View Related
Oct 26, 2007
I have tried the following, the update part i snot working. Any idea why? alter PROCEDURE dbo.SP_FeaturedClassifieds
@PageIndex INT,
@NumRows INT,
@FeaturedClassifiedsCount INT OUTPUT
AS
BEGIN
select @FeaturedClassifiedsCount = (Select Count(*) From classifieds_Ads Where AdStatus=100 And Adlevel=50 )
Declare @startRowIndex INT;
Set @startRowIndex = (@PageIndex * @NumRows) + 1;
With FeaturedClassifieds as (Select ROW_NUMBER() OVER (Order By FeaturedDisplayedCount * (1-(Weight-1)/100) ASC) as
Row, Id, PreviewImageId, Title, DateCreated, FeaturedDisplayedCountFrom
classifieds_Ads
WhereAdStatus=100 And AdLevel=50
)
SelectId, PreviewImageId, Title, DateCreated, FeaturedDisplayedCount
From
FeaturedClassifieds
Where
Row between@startRowIndex And @startRowIndex+@NumRows-1
Update FeaturedClassifiedsSET FeaturedDisplayedCount = FeaturedDisplayedCount+1
Where
Row between
@startRowIndex And @startRowIndex+@NumRows-1
END
I have tried function too for this, but function can not update table I guess.... Can I call stored procedure for each column? How?
I thought the code above would work? What am I missing?
View 3 Replies
View Related
Nov 20, 2007
I'm trying to add records to a table that includes datetime value using INSERT query.
No matter what date value I'm giving the query, the value saved is 1/1/1900 00:00 .
Also, I'm interested in saving only the short date, without time. I thought changing the type from datetime to smalldatetime would do the job, but it didn't help.
View 7 Replies
View Related
Dec 29, 2007
The auto stats not working
I have both Auto Update Statistics and Auto Update Statistics Asynchronously set to True
Created a little test table.
USE [TEST]
GO
/****** Object: Table [dbo].[CUSTOMER] Script Date: 12/29/2007 10:42:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CUSTOMER](
[Customer_Id] [nchar](10) NOT NULL,
[Customer_Name] [nvarchar](1000) NULL,
[Customer_Address] [nvarchar](1000) NULL,
[Customer_Address1] [nchar](1000) NULL,
CONSTRAINT [PK_CUSTOMER] PRIMARY KEY CLUSTERED
(
[Customer_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
--Created an insert table
DECLARE @COUNT INT
SET @COUNT = 1
WHILE @COUNT <= 1000
begin
insert into CUSTOMER (CUSTOMER_ID, CUSTOMER_NAME)
VALUES (@COUNT, '12345678901234567890')
SET @COUNT = @COUNT + 1
END
I then look at Tables then statistics the statistics are empty so i fire update statistics and see 1000 rows in here.
I run again the insert script
DECLARE @COUNT INT
SET @COUNT = 1001
WHILE @COUNT <= 2000
begin
insert into CUSTOMER (CUSTOMER_ID, CUSTOMER_NAME)
VALUES (@COUNT, '12345678901234567890')
SET @COUNT = @COUNT + 1
END
Look again at statistics it does not have 2000 rows in here.
If i do select * from CUSTOMER where CUSTOMER_ID = '2000' then go checks statictics it works.
I was under the impression that when you do insert or delete, update then the statistics are fired.
The sys.sysindexes rowmodctr shows the 1000 rows.
I checked the conditions that sql fires if the no of rows int able > 6 and < 500 then updates when 500 mods made.
Also if row > 500 auto update done when 500 = 20% are added
So both are met.
Anyone other any other suggestions about the auto stats ?
View 7 Replies
View Related
Feb 10, 2008
Hi
I have a VB project in VS 2005 Express that uses SQL 2005. I use Click Once to deploy the project. The s/w installs and installs the SQL 2005 prerequisit on XP and Vista machines without problem.
Problem: As soon as the apps tries to use SQL 2005 on a Vista machine it says sqlservr.exe has stopped working and has to close. XP machines work like a dream.
Any ideas please? Any help would be welcomed.
Dim sql As String = "CREATE DATABASE " + dataBaseName + " ON (NAME = " + dataBaseName + ", FILENAME = '" _
+ UKHRdataFolder + "" + dataBaseName + ".mdf')"
Dim myCommand As New SqlCommand
Dim myAdapter As New SqlDataAdapter
Dim myData As New System.Data.DataTable
Dim conn As New SqlConnection
conn.ConnectionString = _
"Data Source=.SQLEXPRESS;AttachDbFilename=;Integrated Security=True;Connect Timeout=30;User Instance=True"
myCommand.Connection = conn
Try
conn.Open()
Try
myCommand.Connection = conn
myCommand.CommandText = sql
myCommand.ExecuteNonQuery()
Catch myerror As SqlException
End Try
Catch myerror As SqlException
Finally
If conn.State <> ConnectionState.Closed Then conn.Close()
End Try
conn.Close()
conn.Dispose()
View 13 Replies
View Related
Apr 19, 2006
Hi,
I've been trying to get my merge replication to work with a sql ce 2.0 on
sql server 2005, but it keeps generating shapshot scripts my pocket pc can't
execute.
Example of my table.sch file in my snapshot folder:
drop Table [dbo].[Application]
go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Application](
[AppID] [nvarchar](20) NOT NULL,
[AddOnInfo] [nvarchar](50) NULL,
[MaxClients] [int] NULL,
[AppName] [nvarchar](255) NULL,
[NbrDaysHistory] [int] NOT NULL CONSTRAINT [DF_Application_NbrDaysHistory]
DEFAULT (10),
[NbrDaysFuture] [int] NOT NULL CONSTRAINT [DF_Application_NbrDaysFuture]
DEFAULT (10),
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT
[DF__Applicati__rowgu__4C364F0E] DEFAULT (newid())
)
GO
SET ANSI_NULLS ON
go
SET QUOTED_IDENTIFIER ON
go
ALTER TABLE [dbo].[Application] ADD CONSTRAINT [Application_PK] PRIMARY KEY
CLUSTERED
(
[AppID]
)
GO
I've selected that I was setting up a merge replication that needs to be
compatible with sql ce. But it doesn't seem to do this...
I've tried to set the compatibility level to 80SP3 manually but it remains
80RTM...
I've tried everything.....
Does anyone have any ideas what could be causing this?
If I remove these lines from the sch file everything works jsut fine:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
go
SET QUOTED_IDENTIFIER ON
go
ALTER TABLE [dbo].[Application] ADD CONSTRAINT [Application_PK] PRIMARY KEY
CLUSTERED
(
[AppID]
)
GO
Even if I make a publication for sql mobile 2005 it generates the same
script.... It's almost as if SQL 2005 is ignoring my settings...
Thanks,
Desperate Natasja
View 12 Replies
View Related
Dec 29, 2007
The auto stats not working
I have both Auto Update Statistics and Auto Update Statistics Asynchronously set to True
USE [TEST]
GO
/****** Object: Table [dbo].[CUSTOMER] Script Date: 12/29/2007 10:42:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CUSTOMER](
[Customer_Id] [nchar](10) NOT NULL,
[Customer_Name] [nvarchar](1000) NULL,
[Customer_Address] [nvarchar](1000) NULL,
[Customer_Address1] [nchar](1000) NULL,
CONSTRAINT [PK_CUSTOMER] PRIMARY KEY CLUSTERED
(
[Customer_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
--Created an insert table
DECLARE @COUNT INT
SET @COUNT = 1
WHILE @COUNT <= 1000
begin
insert into CUSTOMER (CUSTOMER_ID, CUSTOMER_NAME)
VALUES (@COUNT, '12345678901234567890')
SET @COUNT = @COUNT + 1
END
Look at Tables then statistics the statistics are empty so i fire update statistics and see 1000 rows in here.
I run again the insert script
DECLARE @COUNT INT
SET @COUNT = 1001
WHILE @COUNT <= 2000
begin
insert into CUSTOMER (CUSTOMER_ID, CUSTOMER_NAME)
VALUES (@COUNT, '12345678901234567890')
SET @COUNT = @COUNT + 1
END
Look again at statistics it not firing.
If i do select * from CUSTOMER where CUSTOMER_ID = '2000' then go checks statictics it works.
I was under the impression that when you do insert or delete, update then the statistics are fired.
The sys.sysindexes rowmodctr shows the 1000 rows.
I checked the conditions that sql fires if the no of rows int able > 6 and < 500 then updates when 500 mods made.
Also if row > 500 auto update done when 500 = 20% are added
So both are met.
Anyone other any other suggestions about the auto stats ?
View 2 Replies
View Related
Jan 11, 2001
Our stored procedure error handling is not working now but did before.
When a severity level 16 (Miscellaneous user error) occurs the batch (stored procedure) exits without processing the next statement which is the error check routine. This is a simple error processing routine and used to work. What could have changed?
I have searched books online and have found nothing on abnormal termination of a SQL batch. I would appreciate some help here.
Loren
View 3 Replies
View Related
Aug 27, 2007
Hi,
I have the following code and output
select distinct week ,sum(itemvalue) as itemvalue from (Select ATP,
WEEK=
CASE
WHEN (datepart(Dd, ATP) < 7 AND datename(Month,ATP)='JANUARY')
OR
( datepart(Dd, ATP) < 8 AND datename(Month,ATP)='JULY')
OR
( datepart(Dd, ATP) < 7 AND datename(Month,ATP)='OCTOBER')
THEN '1'
WHEN (datepart(Dd, ATP) < 14 AND datename(Month,ATP)='JANUARY')
OR
( datepart(Dd, ATP) < 15 AND datename(Month,ATP)='JULY')
OR
( datepart(Dd, ATP) < 14 AND datename(Month,ATP)='OCTOBER')
THEN '2'
WHEN (datepart(Dd, ATP) < 21 AND datename(Month,ATP)='JANUARY')
OR
( datepart(Dd, ATP) < 22 AND datename(Month,ATP)='JULY')
OR
( datepart(Dd, ATP) < 21 AND datename(Month,ATP)='OCTOBER')
THEN '3'
WHEN (datepart(Dd, ATP) < 28 AND datename(Month,ATP)='JANUARY')
OR
( datepart(Dd, ATP) < 29 AND datename(Month,ATP)='JULY')
OR
( datepart(Dd, ATP) < 28 AND datename(Month,ATP)='OCTOBER')
THEN '4'
WHEN ((datepart(Dd, ATP) IN (29,30,31)) AND datename(Month,ATP)='JANUARY')
OR
( (datepart(Dd, ATP) IN (29,30,31)) AND datename(Month,ATP)='JULY')
OR
( (datepart(Dd, ATP) IN (28,29,30,31)) AND datename(Month,ATP)='OCTOBER')
THEN '5'
WHEN (datepart(Dd, ATP) < 4 AND datename(Month,ATP)='FEBRUARY')
( datepart(Dd, ATP) < 5 AND datename(Month,ATP)='AUGUST')
OR
( datepart(Dd, ATP) < 4 AND datename(Month,ATP)='NOVEMBER')
THEN '5'
WHEN (datepart(Dd, ATP) <11 AND datename(Month,ATP)='FEBRUARY')
OR
( datepart(Dd, ATP) < 13 AND datename(Month,ATP)='MAY')
OR
( datepart(Dd, ATP) < 12 AND datename(Month,ATP)='AUGUST')
OR
( datepart(Dd, ATP) < 11 AND datename(Month,ATP)='NOVEMBER')
THEN '6'
WHEN (datepart(Dd, ATP) < 18 AND datename(Month,ATP)='FEBRUARY')
OR
( datepart(Dd, ATP) < 19 AND datename(Month,ATP)='AUGUST')
OR
( datepart(Dd, ATP) < 18 AND datename(Month,ATP)='NOVEMBER')
THEN '7'
WHEN (datepart(Dd, ATP) < 25 AND datename(Month,ATP)='FEBRUARY')
OR
( datepart(Dd, ATP) < 27 AND datename(Month,ATP)='MAY')
OR
( datepart(Dd, ATP) < 31 AND datename(Month,ATP)='AUGUST')
OR
( datepart(Dd, ATP) < 25 AND datename(Month,ATP)='NOVEMBER')
THEN '8'
WHEN ((datepart(Dd, ATP) IN (25,26,27,28)) AND datename(Month,ATP)='FEBRUARY')
OR
( (datepart(Dd, ATP) IN (27,28,29,30,31)) AND datename(Month,ATP)='MAY')
OR
( (datepart(Dd, ATP) IN (25,26,27,28,29,30)) AND datename(Month,ATP)='NOVEMBER')
OR
( (datepart(Dd, ATP) <2) AND datename(Month,ATP)='DECEMBER')
THEN '9'
WHEN (datepart(Dd, ATP) < 11 AND datename(Month,ATP)='MARCH')
OR
( datepart(Dd, ATP) < 9 AND datename(Month,ATP)='SEPTEMBER')
OR
( datepart(Dd, ATP) < 9 AND datename(Month,ATP)='DECEMBER')
THEN '10'
WHEN (datepart(Dd, ATP) < 18 AND datename(Month,ATP)='MARCH')
OR
( datepart(Dd, ATP) < 16 AND datename(Month,ATP)='SEPTEMBER')
OR
( datepart(Dd, ATP) < 16 AND datename(Month,ATP)='DECEMBER')
THEN '11'
WHEN (datepart(Dd, ATP) < 25 AND datename(Month,ATP)='MARCH')
OR
( datepart(Dd, ATP) < 23 AND datename(Month,ATP)='SEPTEMBER')
OR
( datepart(Dd, ATP) < 23 AND datename(Month,ATP)='DECEMBER')
THEN '12'
WHEN (datepart(Dd, ATP) > 24 AND datename(Month,ATP)='MARCH')
OR
( datepart(Dd, ATP) > 22 AND datename(Month,ATP)='SEPTEMBER')
OR
( datepart(Dd, ATP) < 30 AND datename(Month,ATP)='DECEMBER')
THEN '13'
ELSE 'BEYOND'
END , SUM(ITEMVALUE) as ITEMVALUE
FROM tOPENLINE_MODIFIED
LEFT OUTER JOIN
tZCHANNEL ON tOPENLINE_MODIFIED.ZCHANNEL = tZCHANNEL.ZCHANNEL
WHERE RequestQtr in ('Q4')
and tOPENLINE_MODIFIED.ATP >= '7/01/07'
and tOPENLINE_MODIFIED.ORDERTYPE in ('OR','ZBOS','ZECM','ZOR','ZOB','ZEXP')
and dbo.tZCHANNEL.ZCHANNEL in ('D','I','01', '02', '06', '07', '10')
and tOPENLINE_MODIFIED.ACCTASSIGNGRP in ('01','02')
AND tOPENLINE_MODIFIED.SOLD2NAME NOT LIKE ('%celestica%')
AND tOPENLINE_MODIFIED.SOLD2NAME NOT LIKE ('%giant%')
and tOPENLINE_MODIFIED.PLANT IN ('COF1', 'I405', 'I375', 'IOM4', 'IOM5', 'I316')
GROUP BY ATP)as A
GROUP BY week
output:
week itemvalue
------ ---------------------
1 1214003.60
10 9257193.45
11 12095432.11
12 11429629.08
13 7315751.08
2 1052337.53
3 951038.10
4 274769.21
5 465278.37
6 78003.67
7 607681.02
8 9042948.17
9 2255545.25
but i need the output as
week
1
2
3
4
5
6
7
8
9
10
11
12
13
iam not able to achieve this after trying so many times.Please help me on this.
Thanks,
SVGP
View 6 Replies
View Related
Jun 1, 2007
I have a DTS package (stored in a .dts file) that was created in SQL Server 2000 and I am trying to work with it in SQL 2005. When I try to import it into the Legacy > Data Transformation Services node in Management Studio, I get a window that reads "Class Not Registered (Microsoft Data Transformation Services (DTS) Package)". I get the same error if I try to just execute the package itself from a query window.
I have Legacy Support installed, and I also have Integration Services installed.
Any ideas? We have three installations of SQL 2005 and I am getting the same error on all of them.
View 1 Replies
View Related
Nov 21, 2006
Hi, I'm fairly new to the asp.net enviroment.. I have a project in asp.net and I need to upgraded to asp.net 2.0... I just did that, and the few errors that I got are resolved already, the only error that I have not been able to figure out is when I tried to retrieve data from a database I get the following error:
An error has ocurred while establishsing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.(provider:Named Pipes Provider, error:40- could not open a connection to SQL Server) and it points to the following line:
Dim ds As DataSet = Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(ConfigurationManager.AppSettings(GlobalD.CfgKeyConnString), CommandType.StoredProcedure, "PG_LST_MENU_BY_SUBJECT")
As I said before my project with the previous version works fine, and this is the only part that is giving me trouble...
thank you
View 1 Replies
View Related
Jun 18, 2008
Hi,
I'm just moving over one of our databases from SQL2000 to 2005. Everything is working fine, and all web sites are working, however I just can't seem to get xp_cmdshell to function on the 2005 server.
I have enabled xp_cmdshell, and when I run a stored procedure, which writes the contents of one column to a text file, it says "invalid object name". This table is definitely there, and is actually updated earlier on in the same SP, so permissions for that are fine.
This is the line causing the problem (if I take it out, the rest of the SP works no problems):
--write the text file
EXEC master.sys.xp_cmdshell 'bcp "SELECT newslettertext FROM mydatabase.dbo.newsletters" queryout D:ewslettertext extbody.txt -U -P -c'
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'mydatabase.dbo.newsletters'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
NULL
Could it be to do with permissions on the master.sys.xp_cmdshell? If so, wouldn't it give me a "permission denied" error?
Any help would be greatly appreciated!
Thanks
View 6 Replies
View Related
Apr 30, 2007
Hi all,
I can't get SQL Server 2005 Enterprise Edition working on my notebook with Windows Vista Business Edition. I installed SQL Server 2005 Enterprise Edition and then i ran SP2 for SQL Server EE.
But now when i a am trying to connect to any server from SQL SERVER MANAGEMENT STUDIO i can't see any Server in Server Name list. When I click on Browse for more, I don't see any server. I am able to get Network servers.
While installing the SQL SERVER all the features were disabled as it is, then i went to advanced and selected all the options.
Any suggestions are apperiacted in advance.
Regards,
Syed Abbas Naqvi
Sr. Software Developer.
View 6 Replies
View Related
Aug 3, 2007
My small business has about 21GIGs of data in our database. This entire week we've been running absolutely slow. Posting transactions have been taking up to 30+ seconds when they're normally instant.
We ran the profiler and found that certain stored procedures were taking an absolutely long time to run. We checked for fragmentation, indexing, etc. Keep in mind our hardware was constantly pegged at 80%+ all the time. We have about 60 users connected to our server at any given time.
After much effort to no avail, we finally resorted to just restarting the software. Some have even suggested powering down and rebooting our hardware, but I halted that. We stopped and restarted the server via the management studio and now everything is just peachy. We're funning super fast and smooth.
My concern is that we're just bandaid-ing the problem and not resolving it; so eventually we're going to be faced with this problem again. Even though restarting the server is quite easy and not too effective on our downtime, personally, I'd like to know what the real issue is. Isn't sql server 2005 a self-tuning software to begin with?
Has anyone experienced this and share some insights on what they did and didn't do to resolve this quirk?
View 3 Replies
View Related
Feb 19, 2007
I have modified the Security Settings>Account Policies>Password Policy on my PC as follows:
Minimum password length: 10 characters
Password must meet complexity requirements: Enabled
However, when creating a login within SQL Server 2005, which does not adhere to the amended policy, this does not seem to be taken into account:
CREATE LOGIN testlogin WITH PASSWORD='a',
DEFAULT_DATABASE=[master],
DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=ON, CHECK_POLICY=ON
have tried restarting the SQL Server and rebooting the PC, but this does not solve the problem.
View 7 Replies
View Related
May 24, 2006
We tested this new feature ("Keep Alive" for orphant connections to automatically close) with no success - neither the standard properties nor slightly changed properties worked.
We tested like this:
SQL Server 2005 - ADO.NET Client
The client established an explicit lock on one row at one database. Afterwards we disconnected the client by pulling out the network-cable. We waited about 35 sec for the sessios to close - but nothing happend; we waited another minute but nothing changed. SQL Server Management Studio and the command line "netstat" told us that the connections are alive ... so what went wrong?
Did we miss something?
Our goal is, that such "orphant connections" get cleaned up and also their inked locks on the database.
BTW we installed the sp1 before all the tests started!
please help -
.agony
View 6 Replies
View Related
Apr 25, 2008
Hello to all,
if have a problem with a SELECT query that works very fine on SQL Server 2000 but not on 2005. I've transfered my db by creating a full backup and restoring the db on 2005. The db is working except this problem.
When i start executing it doesn't finish. I waited a couple of minutes. On 2000 it only takes about 6 seconds to run.
Here it is:
Code Snippet
SELECT * FROM PPS_TERMbesttmpwhere PPS_TermBestTmp.BestNr + CONVERT(varchar(30),CAST(REPLACE(PPS_TermBestTmp.Pos1, ',', '.') AS float),2)
NOT IN (SELECT PPS_TermBest.BestNr + CONVERT(varchar(30), PPS_TermBest.Pos1,2) FROM PPS_TermBest)
Any ideas?
Thanx
Alex
View 7 Replies
View Related
Feb 1, 2006
Prior to our move to 2005...permissions were granted to developers by adding them to the following fixed database roles...db_ddladmin, db_datareader, db_datawriter, and db_securityadmin. They created their objects using 'dbo' as the owner.
After upgrading to 2005, suddently they are having difficulty accessing their objects with this same security. Do they need permissions on the dbo schema?
View 7 Replies
View Related
Feb 12, 2007
When I click Download, it show NOT FOUND
What happened ?
View 1 Replies
View Related
Jan 3, 2008
Hi All,
Been doing some testing with SQLCMD and variables for installation scripts, enclosed below is some source code.
The one problem, I've been getting is the following error:
Incorrect syntax near 'certification'
This has been tested only in SSMS utilising the run in SQLCMD mode.
This error only occurs on the SQLCMD variable replacement, where it replaces $(Cert_Schema) for the word Certification.
Running the CREATE Synonym with the word Certification hard-coded into the script works.
Any ideas?
Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
Best Regards,
D
USE [Testing]
GO
/****** Object: Table [certification].[Table_1] Script Date: 01/03/2008 11:22:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [certification].[Table_1](
[PKId] [smallint] NOT NULL,
[Name] [nchar](10) NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
[PKId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
--- SQLCMD Script below:
-- line below should read, colon setvar cert_schema 'certification'
etvar Cert_Schema 'certification'
-- The line below fails to work for some reason
CREATE SYNONYM crt_asa FOR $(Cert_Schema).Table_1
DROP SYNONYM crt_asa
-- Hard-coded equivilant line works
CREATE SYNONYM crt_asa FOR certification.Table_1
DROP SYNONYM crt_asa
View 3 Replies
View Related
Sep 18, 2006
I'm not experienced with SQL Server 2005.I've got a live copy of my database running on the web server.I've got another copy running on my desktop that I make changes to.Whats the best way to sync between the two so:1. I don't have to take the application offline2. I don't loose any data from the live database3. But I can implement changes to tables and/or add new tables
View 1 Replies
View Related
Jul 20, 2007
Hi everyone, I have an odd problem. I have a generic upload/download ASP.net page that allows the upload and download of and type of file. I have so far tested the following file types:
XLS, MDB, JPG, DLL, EXE, PDF, TXT, SWF, and GIF
All of these upload and download fine, EXCEPT PDF's. I have tried 4 different PDF's and all open prior to upload/download, but after uploading and downloading, I get the following (from Adobe Reader) error upon trying to open:
"There was an error opening this document. The file is damaged and could not be repaired"
Here's my current code:
9 Protected Sub ItemCommand_Click(ByVal sender As System.Object, ByVal e As RepeaterCommandEventArgs)10 11 If e.CommandName = "open" Then12 Dim sqlConn As New SqlConnection(ConfigurationManager.ConnectionStrings("data_partsbranding").ConnectionString)13 Dim sSQL As New StringBuilder14 Dim sqlCmd As SqlCommand15 Dim sqlReader As SqlDataReader16 Dim byteArray(UploadedFile.PostedFile.InputStream.Length) As Byte17 18 sSQL.Append(" SELECT * ")19 sSQL.Append(" FROM [survey_document] ")20 sSQL.Append(" WHERE [sd_document_code] = @sd_document_code ")21 22 sqlCmd = New SqlCommand(sSQL.ToString, sqlConn)23 24 sqlCmd.Parameters.AddWithValue("@sd_document_code", e.CommandArgument)25 26 sqlConn.Open()27 sqlReader = sqlCmd.ExecuteReader28 29 While sqlReader.Read30 31 Response.ContentType = sqlReader("sd_mime_type").ToString()32 Response.BinaryWrite(sqlReader("sd_document"))33 Response.AddHeader("Content-Disposition", "attachment;filename=" & sqlReader("sd_file_name").ToString())34 End While35 36 sqlReader.Close()37 sqlConn.Close()38 39 End If40 41 End Sub42 43 Protected Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click44 45 'Make sure a file has been successfully uploaded46 If UploadedFile.HasFile Then47 48 'Connect to the database and insert a new record into Products49 Dim sqlConn As New SqlConnection(ConfigurationManager.ConnectionStrings("data_partsbranding").ConnectionString)50 Dim sSQL As New StringBuilder51 Dim sqlCmd As SqlCommand52 Dim byteArray(UploadedFile.PostedFile.InputStream.Length - 1) As Byte53 54 'Read the files binary data into a Byte array55 UploadedFile.PostedFile.InputStream.Read(byteArray, 0, byteArray.Length)56 57 sSQL.Append(" INSERT INTO [survey_document] ")58 sSQL.Append(" ( sd_document ")59 sSQL.Append(" , sd_title ")60 sSQL.Append(" , sd_file_name ")61 sSQL.Append(" , sd_upload_date ")62 sSQL.Append(" , sd_mime_type ")63 sSQL.Append(" ) VALUES ( @sd_document ")64 sSQL.Append(" , @sd_title ")65 sSQL.Append(" , @sd_file_name ")66 sSQL.Append(" , @sd_upload_date ")67 sSQL.Append(" , @sd_mime_type ")68 sSQL.Append(" ) ")69 70 sqlCmd = New SqlCommand(sSQL.ToString, sqlConn)71 72 sqlCmd.Parameters.AddWithValue("@sd_title", FileTitle.Text.Trim())73 sqlCmd.Parameters.AddWithValue("@sd_mime_type", UploadedFile.PostedFile.ContentType)74 sqlCmd.Parameters.AddWithValue("@sd_file_name", System.IO.Path.GetFileName(UploadedFile.PostedFile.FileName))75 sqlCmd.Parameters.AddWithValue("@sd_upload_date", Now)76 sqlCmd.Parameters.AddWithValue("@sd_document", byteArray)77 78 sqlConn.Open()79 sqlCmd.ExecuteNonQuery()80 sqlConn.Close()81 82 Else83 84 'Either the file upload failed or no file was selected85 86 End If87 88 End SubI have also tried the following code as a result of searching far and wide, trying other peoples methods:
1 Protected Sub ItemCommand_Click(ByVal sender As System.Object, ByVal e As RepeaterCommandEventArgs)
2
3 If e.CommandName = "open" Then
4 Dim sqlConn As New SqlConnection(ConfigurationManager.ConnectionStrings("data_partsbranding").ConnectionString)
5 Dim sSQL As New StringBuilder
6 Dim sqlCmd As SqlCommand
7 Dim sqlReader As SqlDataReader
8 Dim byteArray(UploadedFile.PostedFile.InputStream.Length) As Byte
9
10 sSQL.Append(" SELECT * ")
11 sSQL.Append(" FROM [survey_document] ")
12 sSQL.Append(" WHERE [sd_document_code] = @sd_document_code ")
13
14 sqlCmd = New SqlCommand(sSQL.ToString, sqlConn)
15
16 sqlCmd.Parameters.AddWithValue("@sd_document_code", e.CommandArgument)
17
18 sqlConn.Open()
19 sqlReader = sqlCmd.ExecuteReader
20
21 While sqlReader.Read
22
23 Dim buffer() As Byte = sqlReader("sd_document")
24 Dim blen As Integer = CType(sqlReader("sd_document"), Byte()).Length
25
26 Response.ContentType = sqlReader("sd_mime_type").ToString()
27 Response.OutputStream.Write(buffer, 0, blen,)
28 Response.AddHeader("Content-Disposition", "attachment;filename=" & sqlReader("sd_file_name").ToString())
29 End While
30
31 sqlReader.Close()
32 sqlConn.Close()
33
34 End If
35
36 End Sub
37
38 Protected Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click
39
40 'Make sure a file has been successfully uploaded
41 If UploadedFile.HasFile Then
42
43 'Connect to the database and insert a new record into Products
44 Dim sqlConn As New SqlConnection(ConfigurationManager.ConnectionStrings("data_partsbranding").ConnectionString)
45 Dim sSQL As New StringBuilder
46 Dim sqlCmd As SqlCommand
47 Dim byteArray(UploadedFile.PostedFile.InputStream.Length - 1) As Byte
48
49 'Read the files binary data into a Byte array
50 UploadedFile.PostedFile.InputStream.Read(byteArray, 0, byteArray.Length)
51
52 sSQL.Append(" INSERT INTO [survey_document] ")
53 sSQL.Append(" ( sd_document ")
54 sSQL.Append(" , sd_title ")
55 sSQL.Append(" , sd_file_name ")
56 sSQL.Append(" , sd_upload_date ")
57 sSQL.Append(" , sd_mime_type ")
58 sSQL.Append(" ) VALUES ( @sd_document ")
59 sSQL.Append(" , @sd_title ")
60 sSQL.Append(" , @sd_file_name ")
61 sSQL.Append(" , @sd_upload_date ")
62 sSQL.Append(" , @sd_mime_type ")
63 sSQL.Append(" ) ")
64
65 sqlCmd = New SqlCommand(sSQL.ToString, sqlConn)
66
67 'sqlCmd.Parameters.AddWithValue("@sd_title", PictureTitle.Text.Trim())
68 'sqlCmd.Parameters.AddWithValue("@sd_mime_type", UploadedFile.PostedFile.ContentType)
69 'sqlCmd.Parameters.AddWithValue("@sd_file_name", System.IO.Path.GetFileName(UploadedFile.PostedFile.FileName))
70 'sqlCmd.Parameters.AddWithValue("@sd_upload_date", Now)
71 'sqlCmd.Parameters.AddWithValue("@sd_document", byteArray)
72
73 sqlCmd.Parameters.Add(New SqlParameter("@sd_mime_type", SqlDbType.VarChar))
74 sqlCmd.Parameters.Add(New SqlParameter("@sd_title", SqlDbType.VarChar))
75 sqlCmd.Parameters.Add(New SqlParameter("@sd_upload_date", SqlDbType.DateTime))
76
77 sqlCmd.Parameters.Add(New SqlParameter("@sd_file_name", SqlDbType.VarChar))
78
79 sqlCmd.Parameters.Add(New SqlParameter("@sd_document", SqlDbType.Image))
80
81 Dim bArray(UploadedFile.PostedFile.ContentLength - 1) As Byte
82
83
84
85
86
87
88 UploadedFile.PostedFile.InputStream.Read(bArray, 0, UploadedFile.PostedFile.ContentLength)
89
90 sqlCmd.Parameters("@sd_mime_type").Value = UploadedFile.PostedFile.ContentType
91 sqlCmd.Parameters("@sd_title").Value = PictureTitle.Text.Trim()
92 sqlCmd.Parameters("@sd_upload_date").Value = Now
93
94 sqlCmd.Parameters("@sd_file_name").Value = System.IO.Path.GetFileName(UploadedFile.PostedFile.FileName).ToLower
95
96 sqlCmd.Parameters("@sd_document").Value = bArray
97
98
99 sqlConn.Open()
100 sqlCmd.ExecuteNonQuery()
101 sqlConn.Close()
102
103 Else
104
105 'Either the file upload failed or no file was selected
106
107 End If
108
109 End Sub
110
This method atleast gives a different error:
"Adobe Reader could not open '132-171510.pdf' because it is either not a supported file type or the file has been damaged (for examplc, it was sent as an email attachment and wasn't correctly decoded)."
Please help, it would be appreciated. Thanks
View 5 Replies
View Related
