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
Hi,
When I execute the following statement:
Select * from table1;
On what basis, SQL server decides the sequence of these rows?
I need to fetch data from a table with very large number of rows. Because of the datasize I need to do this in chunks. I am thinking of passing row counter and fetch N rows at a time. I want to know if there is a need for sorting a table before I apply the above logic or I can rely on default sorting.
Thanks veyr much.
Regards,
Tim
I have a fact table with SalesAmount. I have a dimension with SalesType - either counter or direct.
I want to display a report that shows direct sales by region. No problem. I group on region and filter by direct sales.
However, I also want to show direct sales as a percentage of total sales. I've played around with multiple tables but I can't seem to find a way to display both direct sales and total sales in the same row of a report.
Any suggestions?
Thanks.
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
Recently I was stumped on a problem where I was granting permissions to a user, from within a script that was creating a stored procedure. Then I would check the permissions on the procedure, but the permissions were empty. It turned out that I was missing a "go" statement to seperate the create procedure statement from the grant statement. So that got me to thinking about what other kids of statements must be seperated into seperate batches ? I would thik that anything that is being created must be seperated from any statements that grant or alter permissions, because the items would need to exist first.
comments ?
Hi,
I want to ask a basic question, that is
IN WHAT ORDER A CLUSTERED INDEX SORT THE DATA IN THE COLUMN????
Somewhere in the MSDN library I read the following line:
"A clustered index physically sorts the table's contents in the order of the specified index columns"
But Sorting means it will be in ASCENDING ORDER (ASC) or It will be in DESCENDING ORDER (DESC)
So my question is lets suppose a column on which the cluistered index is defined and it contains character data liek abcd so in wht order it will sort the data alphabetically ASC or DESC
or
If the same above case with integer type of values, if column having integer values then in wht order the data in the table will be sorted.
??????
Thanks..!!!
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.
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.
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()
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
I have this simple full text search query that works perfectly on my own computer using sql server 2005 express, however, on the production server(shared hosting)when I added the first 50+ rows, the full text search works perfect, but as the number of rows increases, the full text search can only see the first50+ rows, but not the new ones. Is there any quick solution for this or it's just a common mistake for developers for not properly indexed columns?Is there a way to re-indexed all rows without loosing data on the live server? search query: SELECT TOP 50 *FROM li_BookmarksWHERE FREETEXT(Keywords,@Keywords)
View 2 Replies View RelatedHi all,I just asked some people to help me out and phone microsoft with thefollowing information, kindly they refused unless we setup a supportcontract with them first, for pre-sales information. (That really doesnot sound like good business sense to me - anyway here is our problem,if anyone could help thanks)."To tell and ask microsoft:We will be setting up a microsoft sql server 2000 instance running on awindows 2003 server.1) We need to check this can run alongside a microsoft 2003 sql server(either workgroup or standard edition), on the same machine. Are thereany .dll clashes if we do this? If there are can we run SQL Server2000, in a virtual machine running windows 2000 professional. (I have alicenced copy we can use for this).2) If we run one instance of 2000, and one of 2003 of the sql servers,can one use the processor licence model, and one use the CAL licencemodel."Thanks for any help, and any idea why they actually force you to usenews groups for pre-sales information?David
View 4 Replies View RelatedHi 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.
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?
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
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 Relatedhello friends,
i am facing a problem in SQL Server 2005 Express Edition. The database created in the SQL Server 2005 Express is when opened in SQL Server 2000, it changes the data type of all the columns of all the table to text type. can anybody help me?
I am developing an asp.net app using visual web developer. I am using a sql server 2005 db for the back end. I have learned that I have to restart my machine if I want to work with the db in SQL Server 2005 Express after working with it in Visual Web Developer.
I am wondering why this is. I am assuming that the db is locked when it is used with VWD. Is this correct? Am I refering to it's status of "locked" correctly? Should I call this something else?
-thanks to anyone that can help
I have a problem with DTS - ActiveXScripting that it cannot initialize the script engine, I have the backward compatibility stuff installed, even tried a repair but still does not work.
Many thanks for any help you can provide on this problem.
Paul
Error Information below:
Date 17/04/2007 13:07:32
Log Job History (Test ActiveX Script)
Step ID 1
Server GBCONV1A002V03
Job Name Test ActiveX Script
Step Name Step 1: Exec TestActiveX script
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: WW004ConSrvSQLAgentA002. ...TSStep_DTSActiveScriptTask_1 DTSRun OnError: DTSStep_DTSActiveScriptTask_1, Error = -2147220487 (800403F9) Error string: ActiveX Scripting was not able to initialize the script execution engine. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 4500 Error Detail Records: Error: -2147220487 (800403F9); Provider Error: 0 (0) Error string: ActiveX Scripting was not able to initialize the script execution engine. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 4500 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 Error: -2147220440 (80040428); Provider Error: 0 (0) Error string: Package failed because Step 'DTSStep_DTSActiveScriptTask_1' failed. Error source: Microsoft Data Transformation Services (DTS) Package Help file: ... Process Exit Code 1. The step failed.
Hi all,
i m using VS 2005 and I have to display records with feature of INSERT / DELETE ITEMS But when i connect to Sql Server Database and select * from columns but here when clicking the "Advance" button , i do not get "Advance Sql generation Option " highlighted. Instead , it is turned off. i.e
The Following options are not highlighting
------ Generate Insert, Update, Delete statements
------ use optimistic concurrency
Plz guide me anyone..... is anything wrong with our VS 2005 software installed?
Bilal
I installed SQL Server 2005 Enterprise, then SP1 and then SP2, Maintenance Plan worked. But if I installed SQL Server 2005 Enterprise and then SP2 directly (skipping SP1), the Check Database Integrity in Maintenance Plan was not working. The error message is as follows:
Executed as user: DomainSqlServiceAccount. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:56:21 AM Could not load package "Maintenance PlansTest Plan" because of error 0xC0014062. Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80004005 (Login failed for user ''. The user is not associated with a trusted SQL Server connection.). The SQL statement that was issued has failed. Source: Started: 10:56:21 AM Finished: 10:56:21 AM Elapsed: 0.047 seconds. The package could not be loaded. The step failed.
Other tasks like Rebuild Index seem to be fine. SP2 is supposed to be inclusive. Does anyone have any ideas on why this is happening.
Thanks
E.G.
Hi, I am trying to edit some data from a SQL2000-datasource in ASP.NET 2.0 and have a problem with a column that has bit-data and is used for selection. SQL2005 works fine when declaring <SelectParameters> <asp:Parameter DefaultValue="TRUE" Name="APL" Type="boolean" /> </SelectParameters>When running this code with SQL2000, there are no error-msgs, but after editing a record the "APL"-column looses its value of 1 and is set to 0. Looks like an issue with type-conversion, we've hit incompatibilities between SQL200 and 2005 with bit/boolean several times before. So, how is this done correctly with SQL2000? (I've tried setting the Type to "int16" -> err. Also setting Defval="1" gave an err) ThanksMichael
View 2 Replies View Related 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!
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
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
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?
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!
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?
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?
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 ?
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 ?
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
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