Does Sql Server 2005 Installation create a default instance?

Hello everybody!

I have recently installed Sql Server 2005 Enterprise Edition on my PC and I have noticed that it has not installed a default instance. I have tried to do this several times even in some other PC but no default instance is installed.

I can get a default instance if I install Sql Server 2000 but this is not the same I guess. Is there any way to install the default instance via Sql Server 2005?

Do you have already a SQL Server instance installed on the system ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Are u able to install Named Instance? if not Check the OS. Enterprise Edition need Server Version of OS. Otherwise u can only install client component.

Madhu

|||Well, as I mentioned before I have installed Sql Server 2005 in several computers but I can explain you some of the cases:
1. In my computer I had already installed Sql Server 2000 and I had created two other instances except the default instance (LOCAL). Then I installed Sql Server 2005.
As a matter of fact I want to make a web synchronization for a merge replication. But I can not configure the merge replication for web synchronization because the database and its instance seems to be in SQL server 2000. That's why I want an 2005 sql server instance.

2. I have also uninstalled Sql Server 2000 and all of its instances from my computer, and I have reinstalled Sql Server 2005. But no default instance was created.

3. I have also installes Sql Server 2005 in another PC which had no instace installed, but the results were the same.

What should I do?

|||Do you mean I can not create any instance if my OS is Windows XP?
|||

Yes if you are installing Enterprise Edition it will not install Services on Windows XP. You can install Standard or any other Edition

http://www.microsoft.com/sql/editions/enterprise/sysreqs.mspx

Madhu

|||Thank U

Does Sql Server 2005 Installation create a default instance?

Hello everybody!

I have recently installed Sql Server 2005 Enterprise Edition on my PC and I have noticed that it has not installed a default instance. I have tried to do this several times even in some other PC but no default instance is installed.

I can get a default instance if I install Sql Server 2000 but this is not the same I guess. Is there any way to install the default instance via Sql Server 2005?

Do you have already a SQL Server instance installed on the system ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Are u able to install Named Instance? if not Check the OS. Enterprise Edition need Server Version of OS. Otherwise u can only install client component.

Madhu

|||Well, as I mentioned before I have installed Sql Server 2005 in several computers but I can explain you some of the cases:
1. In my computer I had already installed Sql Server 2000 and I had created two other instances except the default instance (LOCAL). Then I installed Sql Server 2005.
As a matter of fact I want to make a web synchronization for a merge replication. But I can not configure the merge replication for web synchronization because the database and its instance seems to be in SQL server 2000. That's why I want an 2005 sql server instance.

2. I have also uninstalled Sql Server 2000 and all of its instances from my computer, and I have reinstalled Sql Server 2005. But no default instance was created.

3. I have also installes Sql Server 2005 in another PC which had no instace installed, but the results were the same.

What should I do?

|||Do you mean I can not create any instance if my OS is Windows XP?
|||

Yes if you are installing Enterprise Edition it will not install Services on Windows XP. You can install Standard or any other Edition

http://www.microsoft.com/sql/editions/enterprise/sysreqs.mspx

Madhu

|||Thank U

Does Sql Server 2005 Installation create a default instance?

Hello everybody!

I have recently installed Sql Server 2005 Enterprise Edition on my PC and I have noticed that it has not installed a default instance. I have tried to do this several times even in some other PC but no default instance is installed.

I can get a default instance if I install Sql Server 2000 but this is not the same I guess. Is there any way to install the default instance via Sql Server 2005?

Do you have already a SQL Server instance installed on the system ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Are u able to install Named Instance? if not Check the OS. Enterprise Edition need Server Version of OS. Otherwise u can only install client component.

Madhu

|||Well, as I mentioned before I have installed Sql Server 2005 in several computers but I can explain you some of the cases:
1. In my computer I had already installed Sql Server 2000 and I had created two other instances except the default instance (LOCAL). Then I installed Sql Server 2005.
As a matter of fact I want to make a web synchronization for a merge replication. But I can not configure the merge replication for web synchronization because the database and its instance seems to be in SQL server 2000. That's why I want an 2005 sql server instance.

2. I have also uninstalled Sql Server 2000 and all of its instances from my computer, and I have reinstalled Sql Server 2005. But no default instance was created.

3. I have also installes Sql Server 2005 in another PC which had no instace installed, but the results were the same.

What should I do?

|||Do you mean I can not create any instance if my OS is Windows XP?
|||

Yes if you are installing Enterprise Edition it will not install Services on Windows XP. You can install Standard or any other Edition

http://www.microsoft.com/sql/editions/enterprise/sysreqs.mspx

Madhu

|||Thank U
sql

Does SQL 2005 encryption on x64 work?

Are there any known issues with EncryptByKey/DecryptByKey on x64 machines?

I have a test script where I create a sample table and encrypt a column and later decrypt it. It works fine on my x86 box. When I run the *exact* same test script on an x64 server I'm getting unprintable characters back on the DecryptByKey. I cannont find anything I'm doing different between the two.

Has anyone seen anything like this before?

We have not seen this problems yet but I will take a look and attempt to repro this. Would it be possible to get a copy of the script you are using (obviously, please remove all private/sensitive info first)?

Thanks,

Sung

|||

Nevermind! I figured it out.

I was doing an INSERT INTO SELECT FROM... and the column I was selecting from, TaxID, had to be converted to Unicode first. So it ended up looking like this:

EncryptByKey(@.KeyGuid, CAST([TaxID] As NVARCHAR(9))) As 'TaxID'

On my x86 machine I had hard-coded the TaxID as N'123456789' and didn't realize at the time that the N was converting it to Unicode for me.

|||

Ah, ok, I was wondering if it had something to do with text encoding as well. Glad to hear it works!

Sung

Does 'SELECT INTO' not work in SQL Mobile?

I'm trying to programmatically create a new table that is a copy of another. The SQL statement I'm making is:

"SELECT * INTO sensor_stream_temp FROM sensor_stream"
and various combinations thereof (such as specifying the columns, etc)

The error I keep getting is:
There was an error parsing the query. [Token line number 1, Token line offset 10, , Token in error = INTO ]

Is it not possible to copy a table using this statement? *sigh*
Thanks in advance for any help.
-Dana
OK, so it looks like "INTO" is not allowed in SQL Mobile; how do I make a copy of a table?

|||

Try this:

INSERT INTO sensor_stream_temp (SELECT * FROM sensor_stream);

Note: Both sensor_stream_temp, sensor_stream MUST be of similar.

Does RS support to change the report size automatically?-URGENT!!!

I will use MSRS to create reports in a .Net Smart Client project later.

For some kinds of report, there are some customized columns which may be displayed or not. I design these reports like this: first create all the columns, then set the "Visibility-Hidden" property of those customized columns using expression which including some report parameters.Thus, these customized columns can be displayed or hidden by setting the value of the report parameters at the runtime. But another problem emerged.The size of report backgroud can not changed automatically along with the length of DataTable.So if there are some columns be hidden, there will be some margin on the right of the report and the Title of report was still in the center of the original report,not changed to the center of the new one. Due to the "Size " property of report and the "Size"&"Location"properties of textbox does not support expression.

Is there any one can give me some solution?

I am not sure whether the current version of MSRS support such kind of requirement?If not,will the MSRS final release comes out on November 7 support it?

Thanks!

Hi!

I also have this problem! I am building subreports that are getting more and more generic, but now I have noticed that the (optionally) hidden rows in the subreport's table is showing up as blank space on the main report. That makes this useful way of making the subreports nothing more than useless.

I have found no way to set the size of the subreport "background" or rectangle smaller than the designed area.

The reporting services seems more and more tied down to me. It has to have much more abilities than it currently has to survive. At the moment automation of Word seems much more flexible, but is client based. Still, in many situations the current RS will not deliver enough to fulfill the requirements. I hope this will change soon.

/Michael

|||The report never shrinks the size of the body so hiding a bunch of columns won't help your report. If you want a title to span the items in a table, put it in the table header, similar to how this matrix report was done: http://blogs.msdn.com/bwelcker/archive/2005/05/11/416720.aspx.|||But I am using a "table" as a way of displaying different checkboxes and texts, as a form, and sometimes one or more of the rows in these (generic) subreports are not to be used and I hide them (that's what makes it more generic).

I am not using it to show a list of information but as a way of showing a dynamic one-page form.

When I hide a row it shows up as approximately half a row of blank space on the main report. In this case the Visiblity setting is useless since there is too much space between subreports on the main report.

Does RS support to change the report size automatically?-URGENT!!!

I will use MSRS to create reports in a .Net Smart Client project later.

For some kinds of report, there are some customized columns which may be displayed or not. I design these reports like this: first create all the columns, then set the "Visibility-Hidden" property of those customized columns using expression which including some report parameters.Thus, these customized columns can be displayed or hidden by setting the value of the report parameters at the runtime. But another problem emerged.The size of report backgroud can not changed automatically along with the length of DataTable.So if there are some columns be hidden, there will be some margin on the right of the report and the Title of report was still in the center of the original report,not changed to the center of the new one. Due to the "Size " property of report and the "Size"&"Location"properties of textbox does not support expression.

Is there any one can give me some solution?

I am not sure whether the current version of MSRS support such kind of requirement?If not,will the MSRS final release comes out on November 7 support it?

Thanks!

Hi!

I also have this problem! I am building subreports that are getting more and more generic, but now I have noticed that the (optionally) hidden rows in the subreport's table is showing up as blank space on the main report. That makes this useful way of making the subreports nothing more than useless.

I have found no way to set the size of the subreport "background" or rectangle smaller than the designed area.

The reporting services seems more and more tied down to me. It has to have much more abilities than it currently has to survive. At the moment automation of Word seems much more flexible, but is client based. Still, in many situations the current RS will not deliver enough to fulfill the requirements. I hope this will change soon.

/Michael

|||The report never shrinks the size of the body so hiding a bunch of columns won't help your report. If you want a title to span the items in a table, put it in the table header, similar to how this matrix report was done: http://blogs.msdn.com/bwelcker/archive/2005/05/11/416720.aspx.|||But I am using a "table" as a way of displaying different checkboxes and texts, as a form, and sometimes one or more of the rows in these (generic) subreports are not to be used and I hide them (that's what makes it more generic).

I am not using it to show a list of information but as a way of showing a dynamic one-page form.

When I hide a row it shows up as approximately half a row of blank space on the main report. In this case the Visiblity setting is useless since there is too much space between subreports on the main report.sql

Does rolling a cluster create down time?

We have about 40 DB's blackening 40 web sites running on a single
SQL2k/Win2003 an A/P cluster. When I roll the cluster from one node to the
next I'm wondering exactly what sort of outage our clients would see. Since
the entire cluster rolls in under a minute would there just be a delay on
the web sites with data requests in response from the web/ODBC side or would
there be an ODBC failure. Also, with regards to active web sessions, would
clients mid transaction on the web pages need to restart their transaction
or would there be a short wait during the SQL roll and then they could
proceed with the transaction (or would they need to start all over again).
I guess I'm wondering if I need to add the 1 minute of roll time into our
annual downtime calculation if the clients see an application
failure/unavailability.
On the roll all client connections are lost and must be restarted. As for
what exactly happens, well that depends on the application. If the rows are
returned locally or if they are stored on the web server. And at what stage
in the process each client was in.
Bottom line, in most cases they either hit F5 (refresh) or never notice.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
"Mike B." <Autobahn97@.hotmail.com> wrote in message
news:eS8jOB3XEHA.3596@.tk2msftngp13.phx.gbl...
> We have about 40 DB's blackening 40 web sites running on a single
> SQL2k/Win2003 an A/P cluster. When I roll the cluster from one node to
the
> next I'm wondering exactly what sort of outage our clients would see.
Since
> the entire cluster rolls in under a minute would there just be a delay on
> the web sites with data requests in response from the web/ODBC side or
would
> there be an ODBC failure. Also, with regards to active web sessions,
would
> clients mid transaction on the web pages need to restart their transaction
> or would there be a short wait during the SQL roll and then they could
> proceed with the transaction (or would they need to start all over again).
> I guess I'm wondering if I need to add the 1 minute of roll time into our
> annual downtime calculation if the clients see an application
> failure/unavailability.
>
|||What the clients see or how it affects their transactions depends solely on
how the client app was written. It must be cluster aware. By that I mean
it must be able to detect a connection failure and reconnect. It must also
be able to resubmit the current transaction as it will be rolled back on the
server side when the cluster comes back up.
Andrew J. Kelly SQL MVP
"Mike B." <Autobahn97@.hotmail.com> wrote in message
news:eS8jOB3XEHA.3596@.tk2msftngp13.phx.gbl...
> We have about 40 DB's blackening 40 web sites running on a single
> SQL2k/Win2003 an A/P cluster. When I roll the cluster from one node to
the
> next I'm wondering exactly what sort of outage our clients would see.
Since
> the entire cluster rolls in under a minute would there just be a delay on
> the web sites with data requests in response from the web/ODBC side or
would
> there be an ODBC failure. Also, with regards to active web sessions,
would
> clients mid transaction on the web pages need to restart their transaction
> or would there be a short wait during the SQL roll and then they could
> proceed with the transaction (or would they need to start all over again).
> I guess I'm wondering if I need to add the 1 minute of roll time into our
> annual downtime calculation if the clients see an application
> failure/unavailability.
>
|||A cluster host transition is exactly like a SQL server stop-start. All
transactions are rolled forward or back at startup. The server is down
during the transition. The client application will see a connection timeout
or a query timeout or a broken connection. Whether the application traps
that or displays that to the end-user depends on the skill of the programmer
who wrote it.
Short answer, a cluster transition is downtime.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Mike B." <Autobahn97@.hotmail.com> wrote in message
news:eS8jOB3XEHA.3596@.tk2msftngp13.phx.gbl...
> We have about 40 DB's blackening 40 web sites running on a single
> SQL2k/Win2003 an A/P cluster. When I roll the cluster from one node to
the
> next I'm wondering exactly what sort of outage our clients would see.
Since
> the entire cluster rolls in under a minute would there just be a delay on
> the web sites with data requests in response from the web/ODBC side or
would
> there be an ODBC failure. Also, with regards to active web sessions,
would
> clients mid transaction on the web pages need to restart their transaction
> or would there be a short wait during the SQL roll and then they could
> proceed with the transaction (or would they need to start all over again).
> I guess I'm wondering if I need to add the 1 minute of roll time into our
> annual downtime calculation if the clients see an application
> failure/unavailability.
>
|||During the failover process, any active connections are broken. For Web browser users, a simple refresh of the Web page should create a new database connection. In a more traditional client/server application, or
one that relies heavily on a middle tier, application designers may want to consider checking to see whether the connection exists, and if not, reconnect. Therefore, whatever the user was working on when the server
went down may not be completed, unless the transaction completes before the server goes down or the transaction is handled within the application.
For more information, see the Knowledge Base article "Q273673 - Virtual Server Client Connections Must be Controlled by Clients" at:
http://support.microsoft.com/?kbid=273673
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx

Does replication setup make some entry in the Remote servers setti

If I create a transactional replication - Push method in SQL 2K standard with
sp4, will replication create entries in the Remote Servers setting in EM?
Currently I found three entries in the Remote Servers listing, server name
for the publishing server name, server name for the subscriber server, and
repl_distrbutor.
If the replication does make these entries, I have no problem with the
repl_distributor being there. But I have problem with the subscriber server
name being there because I want to create a Linked Server with that same name
but I can't since the name is already in use by the remote servers.
Please let me know which one or all of the above names are legit if indeed
repliation put them in there to begin with.
Say if the above entries need to be there, how I should move them to the
Linked Servers setting because the remote servers feature is for backward
compatible reason and linked servers is more dynamic and is the one to
replace it.
Thanks in advance.
Wingman
Remote Servers and Linked Servers are all stored in the sysservers table in
the master database. Different flag values determine what an entry actually
represents. There is even a special entry for the local server name. If
you want or need to use a replication participating server as a linked
server, there is a special work-around to enable that functionality.
PRB: Adding a Linked Server Causes Error 15028
http://support.microsoft.com/kb/274098/en-us
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Wingman" <Wingman@.discussions.microsoft.com> wrote in message
news:DC17EBF5-D4FF-49C6-83A7-7E3E4F8D0FE0@.microsoft.com...
> If I create a transactional replication - Push method in SQL 2K standard
> with
> sp4, will replication create entries in the Remote Servers setting in EM?
> Currently I found three entries in the Remote Servers listing, server name
> for the publishing server name, server name for the subscriber server, and
> repl_distrbutor.
> If the replication does make these entries, I have no problem with the
> repl_distributor being there. But I have problem with the subscriber
> server
> name being there because I want to create a Linked Server with that same
> name
> but I can't since the name is already in use by the remote servers.
> Please let me know which one or all of the above names are legit if indeed
> repliation put them in there to begin with.
> Say if the above entries need to be there, how I should move them to the
> Linked Servers setting because the remote servers feature is for backward
> compatible reason and linked servers is more dynamic and is the one to
> replace it.
> Thanks in advance.
> Wingman
|||Ok, I tried that stored procedure and the server name now shows up in both
linked server and remote servers. And I still can't execute the remote
stored procedure. Here is my observation after reading the link. It said
the status for a remote server of a subscriber supposedly a 69. In order for
it to become a linked server of a subscriber it needs to be 229. Before I
executed the stored procedure, the server name that I want to change its
status is 1093 not 69. After the execution, it added 160 to it to become
1253. Is the 1253 status correct? The initial status number 1093 is not 69,
is it because there are other settings added values to it?
Here are my questions:
1) is it correct that the server name will show up both in the linked
servers and remote servers in EM?
2) Is the 1253 new status correct?
3) the link didn't say I need to do a service restart, what do you think? I
am getting this error when I execute a remote stored procedure:
"Could not execute procedure on remote server 'server1' because SQL Server
is not configured for remote access. Ask your system administrator to
reconfigure SQL Server to allow remote access."
Also, when I try to double click on the 'table' of the linked server, it
said the server doesn't exist or access denied. The SQL login name I
entered in the Linked server properties has the sys admin right and this
account exists in both servers.
Wingman
"Wingman" wrote:

> If I create a transactional replication - Push method in SQL 2K standard with
> sp4, will replication create entries in the Remote Servers setting in EM?
> Currently I found three entries in the Remote Servers listing, server name
> for the publishing server name, server name for the subscriber server, and
> repl_distrbutor.
> If the replication does make these entries, I have no problem with the
> repl_distributor being there. But I have problem with the subscriber server
> name being there because I want to create a Linked Server with that same name
> but I can't since the name is already in use by the remote servers.
> Please let me know which one or all of the above names are legit if indeed
> repliation put them in there to begin with.
> Say if the above entries need to be there, how I should move them to the
> Linked Servers setting because the remote servers feature is for backward
> compatible reason and linked servers is more dynamic and is the one to
> replace it.
> Thanks in advance.
> Wingman
|||Yes, it is correct to see the servers listed in more than one place. The
values you refer to are bit flags held in an integer field. Addition is not
the correct operation, logical OR is the correct operation.
You need to enable each server for remote access, just like it says. Login
to each remote SQL server and execute the following:
exec sp_configure 'remote access', 1
go
reconfigure with override
go
This tells the remote server to accept remote queries.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Wingman" <Wingman@.discussions.microsoft.com> wrote in message
news:59C3CADD-1BCB-45CD-9DB0-2EEEFCADCA6B@.microsoft.com...[vbcol=seagreen]
> Ok, I tried that stored procedure and the server name now shows up in both
> linked server and remote servers. And I still can't execute the remote
> stored procedure. Here is my observation after reading the link. It said
> the status for a remote server of a subscriber supposedly a 69. In order
> for
> it to become a linked server of a subscriber it needs to be 229. Before
> I
> executed the stored procedure, the server name that I want to change its
> status is 1093 not 69. After the execution, it added 160 to it to become
> 1253. Is the 1253 status correct? The initial status number 1093 is not
> 69,
> is it because there are other settings added values to it?
> Here are my questions:
> 1) is it correct that the server name will show up both in the linked
> servers and remote servers in EM?
> 2) Is the 1253 new status correct?
> 3) the link didn't say I need to do a service restart, what do you think?
> I
> am getting this error when I execute a remote stored procedure:
> "Could not execute procedure on remote server 'server1' because SQL Server
> is not configured for remote access. Ask your system administrator to
> reconfigure SQL Server to allow remote access."
> Also, when I try to double click on the 'table' of the linked server, it
> said the server doesn't exist or access denied. The SQL login name I
> entered in the Linked server properties has the sys admin right and this
> account exists in both servers.
>
> Wingman
>
> "Wingman" wrote:

does not match with a table name or alias name used in the query

I am trying to create a trigger on a table. The idea is
that the trigger will stop duplicates being entered but
will allow null values. I am trying to use the inserted
table but am receiving the following error.
The column prefix 'T2' does not match with a table name
or alias name used in the query.
The table and trigger creation script follows.
Thanks in advance.
========= Table
=========
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Class]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[Class]
GO
CREATE TABLE [dbo].[Class] (
[ClassID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (60) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Symbol] [T_STD_SYMBOL] NULL ,
[ClassTypeID] [int] NOT NULL ,
[Description] [varchar] (40) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
========= Trigger
=========
if exists (select 1
from sysobjects
where id = object_id('uqSymbol_class')
and type = 'TR')
drop trigger uqSymbol_class
go
/* Trigger to stop duplicate Symbols being entered in
the Symbol column in the class table. */
create trigger uqSymbol_class on Class for insert, update
as
begin
declare
@.numrows int,
@.errno int,
@.errmsg varchar(255)
select @.numrows = @.@.rowcount
if @.numrows = 0
return
/* Check to see if the inserted updated symbol
already exists */
if update(symbol)
begin
-- Check weather the Symbol value is Null.
Ignore if so.
-- if ((select symbol from inserted) != null)
begin -- IF((SELECT COUNT(*) FROM INSERTED WHERE
Symbol IS NOT NULL) > 0)
if (select count(*)
from Class t1, inserted t2
where t1.symbol = t2.symbol) >1 AND
T2.Symbol IS NOT NULL
begin
select @.errno = 30003,
@.errmsg = 'The Symbol you have
entered or just updated already exists.'
goto error
end
end
-- end
return
/* Error handling */
error:
raiserror @.errno @.errmsg
rollback transaction
end
ThanX :-)You are not giving us any code which uses t2 as an alias... please repost
complete code...
also if there are ever multiple triggers, and one of the other triggers does
a select insert update or delete, then @.@.rowcount in this trigger will not
be correct. It is probably safer to count records in inserted and deleted...
"Jamie" <jamie.downs@.risk.sungard.com> wrote in message
news:0d4101c360b0$976a42e0$a101280a@.phx.gbl...
> I am trying to create a trigger on a table. The idea is
> that the trigger will stop duplicates being entered but
> will allow null values. I am trying to use the inserted
> table but am receiving the following error.
> The column prefix 'T2' does not match with a table name
> or alias name used in the query.
> The table and trigger creation script follows.
> Thanks in advance.
> =========> Table
> =========> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[Class]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[Class]
> GO
> CREATE TABLE [dbo].[Class] (
> [ClassID] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [varchar] (60) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Symbol] [T_STD_SYMBOL] NULL ,
> [ClassTypeID] [int] NOT NULL ,
> [Description] [varchar] (40) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
>
> =========> Trigger
> =========> if exists (select 1
> from sysobjects
> where id = object_id('uqSymbol_class')
> and type = 'TR')
> drop trigger uqSymbol_class
> go
> /* Trigger to stop duplicate Symbols being entered in
> the Symbol column in the class table. */
> create trigger uqSymbol_class on Class for insert, update
> as
> begin
> declare
> @.numrows int,
> @.errno int,
> @.errmsg varchar(255)
> select @.numrows = @.@.rowcount
> if @.numrows = 0
> return
>
> /* Check to see if the inserted updated symbol
> already exists */
> if update(symbol)
> begin
> -- Check weather the Symbol value is Null.
> Ignore if so.
> -- if ((select symbol from inserted) != null)
>
----
--
> ThanX :-)|||You missed out the relevant bit of code.
In fact you don't need a trigger to do this. You can use an indexed view to
enforce uniqueness only for non-NULL values:
CREATE VIEW Symbols
WITH SCHEMABINDING
AS
SELECT symbol
FROM dbo.class
WHERE symbol IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX uclsymbol ON Symbols (symbol)
--
David Portas
--
Please reply only to the newsgroup
--|||Hi Jamie,
Posting messages with attachments usually isn't very useful because most
people do not trust them, won't open them and so don't have enough
information to answer your question. Post everything in plain text instead.
If you are on SQL Server 2000 you don't have to use a trigger to achieve
this, but you can instead create an indexed view to check for duplicates:
CREATE VIEW Class_Symbol_check
AS
SELECT Symbol FROM Class
WHERE Symbol IS NOT NULL
GO
CREATE UNIQUE INDEX ON Class_Symbol_check (Symbol )
GO
Read the topic "Indexed views" in Books online for more information.
The fact that a UNIQUE index can only have one NULL (otherwise you could
just create a unique constraint on the Symbol column in the class table) is
a problem in SQL Server and one I sincerly hope will be addressed in the
next version.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Jamie" <jamie.downs@.risk.sungard.com> wrote in message
news:0d4101c360b0$976a42e0$a101280a@.phx.gbl...
> I am trying to create a trigger on a table. The idea is
> that the trigger will stop duplicates being entered but
> will allow null values. I am trying to use the inserted
> table but am receiving the following error.
> The column prefix 'T2' does not match with a table name
> or alias name used in the query.
> The table and trigger creation script follows.
> Thanks in advance.
> =========> Table
> =========> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[Class]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[Class]
> GO
> CREATE TABLE [dbo].[Class] (
> [ClassID] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [varchar] (60) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Symbol] [T_STD_SYMBOL] NULL ,
> [ClassTypeID] [int] NOT NULL ,
> [Description] [varchar] (40) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
>
> =========> Trigger
> =========> if exists (select 1
> from sysobjects
> where id = object_id('uqSymbol_class')
> and type = 'TR')
> drop trigger uqSymbol_class
> go
> /* Trigger to stop duplicate Symbols being entered in
> the Symbol column in the class table. */
> create trigger uqSymbol_class on Class for insert, update
> as
> begin
> declare
> @.numrows int,
> @.errno int,
> @.errmsg varchar(255)
> select @.numrows = @.@.rowcount
> if @.numrows = 0
> return
>
> /* Check to see if the inserted updated symbol
> already exists */
> if update(symbol)
> begin
> -- Check weather the Symbol value is Null.
> Ignore if so.
> -- if ((select symbol from inserted) != null)
>
----
--
> ThanX :-)|||> a problem in SQL Server and one I sincerly hope will be addressed in the
> next version.
Full SQL92 constraints would solve this problem and more. I hope Yukon will
support the ANSI-style constraints.
--
David Portas
--
Please reply only to the newsgroup
--|||Yeah,
In the all the info I have read and heard about Yukon there is a lot of talk
about CLR support, and only very general remarks about improvements in
T-SQL. I guess the marketing people at Microsoft think that (full ANSI
constraints etc) is "hard core SQL" and too difficult for 95% of the
developers, and they are probably right. Euan Garden (Program manager for
SQL Server) said during a presentation "that people think that Microsoft
will drop support for T-SQL now that there's CLR support, but that's not the
case", so that tells you the level they are targeting. </rant>
Of the new SQL features in Yukon that I know of I like the idea in Yukon
that you can write your own datatypes, I just don't like the idea that you
have to write them in a CLR language instead of being able to declare them
in SQL, although I expect that there will soon be a cottage industry in
datatypes for SQL Server (postcodes, telephone numbers, credit card numbers,
ISBN etc), just like there was with ActiveX controls for VB 6.
But there is little or no information on structural improvement to T-SQL and
further compliance with ANSI standards. Do we get ANSI style constraints?
row constructors? ...?
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:#ZqfnQLYDHA.1004@.TK2MSFTNGP12.phx.gbl...
> > a problem in SQL Server and one I sincerly hope will be addressed in the
> > next version.
> Full SQL92 constraints would solve this problem and more. I hope Yukon
will
> support the ANSI-style constraints.
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
>
>|||Thanks for all your help. I have implemented the view as
the trigger.
Jacco, what did you mean by CLR.
Thanks again.

Does not have permission to register endpoint

I came across with the problem that I cannot create an endpoint on Windows Server 2003 (SP1). It's said:

Msg 7850, Level 16, State 1, Line 2

The user 'yyyyy\xxxx' does not have permission to register endpoint 'zzzzzz' on the specified URL. Please ensure the URL refers to a namespace that is reserved for listening by SQL.

Msg 7807, Level 16, State 1, Line 2

An error ('0x80070005') occurred while attempting to register the endpoint 'SecurityServices'.

My code looks loke this:

IF EXISTS (

SELECT name from sys.http_endpoints

WHERE name = 'zzzzzz'

)

DROP ENDPOINT zzzzzz

GO

CREATE ENDPOINT zzzzzz

STATE = STARTED

AS HTTP (

path='/sql/zzzzzz',

AUTHENTICATION=(INTEGRATED),

PORTS = (CLEAR)

)

FOR SOAP(

WEBMETHOD 'http://servername/' . 'sp1' (NAME = 'dbName.dbo.sp1'),

WEBMETHOD 'http://servername/' . 'sp2' (NAME = 'dbName.dbo.sp2'),

WSDL = DEFAULT,

BATCHES=ENABLED)

GO

-- End of Script --
The script that I use is correct and it works fine on my local machine (Windows XP). The user that I used to crete an endpoint on the server has 'sysadmin' level. The IIS was already turned off. Also I run the script by using the RemoteDesktop to connect to the server (Windows 2003) that has SQL Server 2005.

If anyone has an idea about my problem, please help me !!!

Thank you,
POP

I believe the problem you run into is because when you create an endpoint, you are not executing under the logged in user, but as the account the SQL Server runs under. In your case the account on the remote server most likely have very restricted priviliges. To fix this you need to reserve the namespace you are going to create, before you can actually create it.
To reserve it, you run the un-doc:ed stored procedure sp_reserve_http_namespace. You run it with the macine name (as it will appear in the site param in CREATE ENDPOINT), portnumber and virtual directory name as in the path param in CREATE ENDPOINT.
An example would be:
sp_reserve_http_namespace N'http://Perth:80/emp', which then would have the following CREATE ENPOINT statement:
<<<<<<<<<<<<<<<
create endpoint EmpClass
state = started
as HTTP (
site = 'Perth',
path = '/emp',
authentication = (INTEGRATED),
ports = (CLEAR))
FOR SOAP...
>>>>>>>>>>>>>>>
Hope this helps!!
Niels

|||Thank you so much Niels (again),

It's works !!!!

At first, I try to use >> sp_reserve_http_namespace N'http://Perth:80/emp'
first but it said >> A reservation for this HTTP namespace (http://Perth:80/emp) already exists

But when I look at your code
as HTTP (
site = 'Perth',
path = '/emp',
authentication = (INTEGRATED),
ports = (CLEAR))
FOR SOAP...
I don't have the value of the site (yellow highlighted part). Then I add that part and when I run the code on the server, it works !!!!.

Without that line, I can create an end point on my local computer but not on the server (Cluster Servers). So from now on, I'll have that line all the time.

Thank you again Niels,

POP|||It works. Thank you.

Does not have permission to register endpoint

I came across with the problem that I cannot create an endpoint on Windows Server 2003 (SP1). It's said:

Msg 7850, Level 16, State 1, Line 2

The user 'yyyyy\xxxx' does not have permission to register endpoint 'zzzzzz' on the specified URL. Please ensure the URL refers to a namespace that is reserved for listening by SQL.

Msg 7807, Level 16, State 1, Line 2

An error ('0x80070005') occurred while attempting to register the endpoint 'SecurityServices'.

My code looks loke this:

IF EXISTS (

SELECT name from sys.http_endpoints

WHERE name = 'zzzzzz'

)

DROP ENDPOINT zzzzzz

GO

CREATE ENDPOINT zzzzzz

STATE = STARTED

AS HTTP (

path='/sql/zzzzzz',

AUTHENTICATION=(INTEGRATED),

PORTS = (CLEAR)

)

FOR SOAP(

WEBMETHOD 'http://servername/' . 'sp1' (NAME = 'dbName.dbo.sp1'),

WEBMETHOD 'http://servername/' . 'sp2' (NAME = 'dbName.dbo.sp2'),

WSDL = DEFAULT,

BATCHES=ENABLED)

GO

-- End of Script --
The script that I use is correct and it works fine on my local machine (Windows XP). The user that I used to crete an endpoint on the server has 'sysadmin' level. The IIS was already turned off. Also I run the script by using the RemoteDesktop to connect to the server (Windows 2003) that has SQL Server 2005.

If anyone has an idea about my problem, please help me !!!

Thank you,
POP

I believe the problem you run into is because when you create an endpoint, you are not executing under the logged in user, but as the account the SQL Server runs under. In your case the account on the remote server most likely have very restricted priviliges. To fix this you need to reserve the namespace you are going to create, before you can actually create it.
To reserve it, you run the un-doc:ed stored procedure sp_reserve_http_namespace. You run it with the macine name (as it will appear in the site param in CREATE ENDPOINT), portnumber and virtual directory name as in the path param in CREATE ENDPOINT.
An example would be:
sp_reserve_http_namespace N'http://Perth:80/emp', which then would have the following CREATE ENPOINT statement:
<<<<<<<<<<<<<<<
create endpoint EmpClass
state = started
as HTTP (
site = 'Perth',
path = '/emp',
authentication = (INTEGRATED),
ports = (CLEAR))
FOR SOAP...
>>>>>>>>>>>>>>>
Hope this helps!!
Niels
|||Thank you so much Niels (again),

It's works !!!!

At first, I try to use >> sp_reserve_http_namespace N'http://Perth:80/emp'
first but it said >> A reservation for this HTTP namespace (http://Perth:80/emp) already exists

But when I look at your code
as HTTP (
site = 'Perth',
path = '/emp',
authentication = (INTEGRATED),
ports = (CLEAR))
FOR SOAP...
I don't have the value of the site (yellow highlighted part). Then I add that part and when I run the code on the server, it works !!!!.

Without that line, I can create an end point on my local computer but not on the server (Cluster Servers). So from now on, I'll have that line all the time.

Thank you again Niels,

POP|||It works. Thank you.

Does it require sa to create dts package?

what privilege does the user has to have to create a dts package?By default, anyone with a login can create a Package as the
public role has execute permissions on sp_add_dtspackage
stored procedure in msdb and the guest account is enabled in
msdb by default so it depends on whether you have changed
this or not.
But they don't need to be a sysadmin to create packages.
It's generally controlled through sp_add_dtspackage.
-Sue
On Thu, 24 Feb 2005 09:35:07 -0800, "sabby"
<sabby@.discussions.microsoft.com> wrote:

>what privilege does the user has to have to create a dts package?|||Thank you so much.
"sabby" wrote:

> what privilege does the user has to have to create a dts package?

Does Identity columns... create an index by default

Hi,
If I create an identity column on a sql server table.... does "sql server"
create an index too...
Or do I have to create an index explicitly.
Thanks
Nalaka"Nalaka" <nalaka12@.nospam.nospam> wrote in message
news:OXPawNnGIHA.4476@.TK2MSFTNGP06.phx.gbl...
> Hi,
> If I create an identity column on a sql server table.... does "sql
> server" create an index too...
> Or do I have to create an index explicitly.
> Thanks
> Nalaka
>
>
No it doesn't. Assuming, you want the column to be unique you should create
a PRIMARY KEY or UNIQUE constraint on the column. A constraint does
automatically create an index.
--
David Portas|||Thanks David
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:O23KLQnGIHA.5980@.TK2MSFTNGP04.phx.gbl...
> "Nalaka" <nalaka12@.nospam.nospam> wrote in message
> news:OXPawNnGIHA.4476@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> If I create an identity column on a sql server table.... does "sql
>> server" create an index too...
>> Or do I have to create an index explicitly.
>> Thanks
>> Nalaka
>>
>>
> No it doesn't. Assuming, you want the column to be unique you should
> create a PRIMARY KEY or UNIQUE constraint on the column. A constraint does
> automatically create an index.
> --
> David Portas
>

Does field exist in backup?

I run scripts against my db,

1) Rename table
2) Create new table (original name)
3) Create Indexes
4) Insert into new, Select from backup

My problem is that one table may have more fields than on another db, but I want to run the same script to update the tables.

What I'd like it to do, is in the insert select stuff, I want to put logic to select field from backup if it exists and insert in new.

If it doesnt exist in backup then insert null into new table, instead of having the line blow up cause the field doesnt exist in backup.

Suggestions would be appreciated. Thanks!, MitchIf it's not in the list it will automatically put in nulls...as long as it is nullable...

and you could go crazy...but it might just easier to code the dang thing...

USE Northwind
GO
sp_help Orders
GO

-- The lazy man's way to create a table

SELECT * INTO NewOrders FROM Orders WHERE 1=0
GO

ALTER TABLE NewOrders DROP Column RequiredDate
GO

DECLARE @.x varchar(8000)

SELECT @.x = 'INSERT INTO NewOrders ('

SELECT @.x = @.x + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION = 1

SELECT @.x = @.x + ', '+COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION > 1
ORDER BY ORDINAL_POSITION

SELECT @.x = @.x + ') SELECT '

SELECT @.x = @.x + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION = 1

SELECT @.x = @.x + ', '+COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION > 1
ORDER BY ORDINAL_POSITION

SELECT @.x = @.x + ' FROM Orders'

SELECT @.x

SET IDENTITY_INSERT NewOrders ON

EXEC(@.x)

SET IDENTITY_INSERT NewOrders OFF
GO

SELECT * FROM NewOrders
GO

DROP TABLE NewOrders
GO|||If you could send me a link or something, that would help me understand the logic below that would be awesome. I sort of follow the code below, but I'd like to see step by step what does what.
Thanks for your reply.
Mitch

Originally posted by Brett Kaiser
If it's not in the list it will automatically put in nulls...as long as it is nullable...

and you could go crazy...but it might just easier to code the dang thing...

USE Northwind
GO
sp_help Orders
GO

-- The lazy man's way to create a table

SELECT * INTO NewOrders FROM Orders WHERE 1=0
GO

ALTER TABLE NewOrders DROP Column RequiredDate
GO

DECLARE @.x varchar(8000)

SELECT @.x = 'INSERT INTO NewOrders ('

SELECT @.x = @.x + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION = 1

SELECT @.x = @.x + ', '+COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION > 1
ORDER BY ORDINAL_POSITION

SELECT @.x = @.x + ') SELECT '

SELECT @.x = @.x + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION = 1

SELECT @.x = @.x + ', '+COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION > 1
ORDER BY ORDINAL_POSITION

SELECT @.x = @.x + ' FROM Orders'

SELECT @.x

SET IDENTITY_INSERT NewOrders ON

EXEC(@.x)

SET IDENTITY_INSERT NewOrders OFF
GO

SELECT * FROM NewOrders
GO

DROP TABLE NewOrders
GO|||Mitch,

Just cut and paste the code into a query analyzer window...

Just execute...I already tested it and it runs like a champ...

Does DBCC DBREINDEX update stats?

(Assuming SQL Server 2000, auto create statistics on, auto update
statistics on.)

Does

DBCC DBREINDEX(<tablename>)

update statistics? If yes, are the statistics equivalent to those
that would be produced by:

UPDATE STATISTICS <tablename> WITH FULLSCANDBCC DBREINDEX updates the statistics, while DBCC INDEXDEFRAG does not

http://sqlservercode.blogspot.com/

Does Commit Transaction delete Temporary table?

Hi SQL Guru's,
I create a global temp table in one of my procedures:
select * into ##temp_update
from ( select distinct * from .....
after this I issue a commit transaction, and when I try to select from
the table I created (from my .Net code or Query Analyzer) it is not
there!
select * from ##temp_update
Invalid object name '##temp_update'.
Any idea why?From Books Online:
"Global temporary tables are automatically dropped when the session that
created the table ends and all other tasks have stopped referencing them. Th
e
association between a task and a table is maintained only for the life of a
single Transact-SQL statement. This means that a global temporary table is
dropped at the completion of the last Transact-SQL statement that was
actively referencing the table when the creating session ended."
If the session that creates the global temporary table ends before a new
session that accesses the global temporary table is started, the table is
dropped.
ML|||Yikes!
Good to know that, but bad for my program :)
thanks much
ML wrote:
> From Books Online:
> "Global temporary tables are automatically dropped when the session that
> created the table ends and all other tasks have stopped referencing them.
The
> association between a task and a table is maintained only for the life of
a
> single Transact-SQL statement. This means that a global temporary table is
> dropped at the completion of the last Transact-SQL statement that was
> actively referencing the table when the creating session ended."
> If the session that creates the global temporary table ends before a new
> session that accesses the global temporary table is started, the table is
> dropped.
>
> ML

Does Commit Transaction delete Temporary table?

Hi SQL Guru's,
I create a global temp table in one of my procedures:
select * into ##temp_update
from ( select distinct * from .....
after this I issue a commit transaction, and when I try to select from
the table I created (from my .Net code or Query Analyzer) it is not
there!
select * from ##temp_update
Invalid object name '##temp_update'.
Any idea why?From Books Online:
"Global temporary tables are automatically dropped when the session that
created the table ends and all other tasks have stopped referencing them. Th
e
association between a task and a table is maintained only for the life of a
single Transact-SQL statement. This means that a global temporary table is
dropped at the completion of the last Transact-SQL statement that was
actively referencing the table when the creating session ended."
If the session that creates the global temporary table ends before a new
session that accesses the global temporary table is started, the table is
dropped.
ML|||Yikes!
Good to know that, but bad for my program :)
thanks much
ML wrote:
> From Books Online:
> "Global temporary tables are automatically dropped when the session that
> created the table ends and all other tasks have stopped referencing them.
The
> association between a task and a table is maintained only for the life of
a
> single Transact-SQL statement. This means that a global temporary table is
> dropped at the completion of the last Transact-SQL statement that was
> actively referencing the table when the creating session ended."
> If the session that creates the global temporary table ends before a new
> session that accesses the global temporary table is started, the table is
> dropped.
>
> ML

Does chart support more than 800 points?

I'm using SQL Server 2000 Reporting Services. I want to create a curve using
chart for a series of data.
If the number of points (x axis) is small (less than 800), the curve is
displayed correctly.
But if the number of points is more than 800, the curve cannot come out,
after about 1 minute, in the "Event Viewer" of the server, I find a system
warning:
"A process serving application pool 'DefaultAppPool' suffered a fatal
communication error with the World Wide Web Publishing Service. The process
id was '22656'. The data field contains the error number(8007006d). "
Anyone could give me a solution to fix it? ThanksIn the log file of reporting service, I find an exception of Out of Memory:
w3wp!processing!2afc!2006-6-30-15:38:29:: e ERROR:
System.OutOfMemoryException: Out of memory.
at System.Drawing.Graphics.CheckErrorStatus(Int32 status)
at System.Drawing.Graphics.DrawCurve(Pen pen, PointF[] points, Int32
offset, Int32 numberOfSegments, Single tension)
at Dundas.Charting.WebControl.GdiGraphics.DrawCurve(Pen pen, PointF[]
points, Int32 offset, Int32 numberOfSegments, Single tension)
at Dundas.Charting.WebControl.ChartTypes.LineChart.DrawLine(ChartGraphics
graph, CommonElements common, DataPoint point, Series series, PointF[]
points, Int32 pointIndex, Single tension)
at
Dundas.Charting.WebControl.ChartTypes.LineChart.ProcessChartType(Boolean
selection, ChartGraphics graph, CommonElements common, ChartArea area, Series
seriesToDraw)
at Dundas.Charting.WebControl.ChartTypes.LineChart.Paint(ChartGraphics
graph, CommonElements common, ChartArea area, Series seriesToDraw)
at Dundas.Charting.WebControl.ChartArea.Paint(ChartGraphics graph)
at Dundas.Charting.WebControl.ChartPicture.Paint(Graphics graph, Boolean
cursorsOnly, Boolean svgMode, XmlTextWriter svgTextWriter, String
documentTitle, Boolean resizable, Boolean preserveAspectRatio)
at Dundas.Charting.WebControl.ChartImage.GetImage()
at Dundas.Charting.WebControl.Chart.Save(Stream imageStream)
at
Microsoft.ReportingServices.ReportRendering.DundasChart.GetImage(ImageType
type, Boolean& hasImageMap)
at
Microsoft.ReportingServices.ReportRendering.DundasChart.GetImage(ImageType
type, ChartInstanceInfo instanceInfo, Boolean& hasImageMap)
at Microsoft.ReportingServices.ReportRendering.Chart.GetImage(ImageType
type, Boolean& hasImageMap)
w3wp!processing!2afc!2006-6-30-15:38:29:: e ERROR: Throwing
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: An
error has occurred during rendering of chart chart1. Details: Out of memory.,
;
Info:
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: An
error has occurred during rendering of chart chart1. Details: Out of memory.
--> System.OutOfMemoryException: Out of memory.
at System.Drawing.Graphics.CheckErrorStatus(Int32 status)
at System.Drawing.Graphics.DrawCurve(Pen pen, PointF[] points, Int32
offset, Int32 numberOfSegments, Single tension)
at Dundas.Charting.WebControl.GdiGraphics.DrawCurve(Pen pen, PointF[]
points, Int32 offset, Int32 numberOfSegments, Single tension)
at Dundas.Charting.WebControl.ChartTypes.LineChart.DrawLine(ChartGraphics
graph, CommonElements common, DataPoint point, Series series, PointF[]
points, Int32 pointIndex, Single tension)
at
Dundas.Charting.WebControl.ChartTypes.LineChart.ProcessChartType(Boolean
selection, ChartGraphics graph, CommonElements common, ChartArea area, Series
seriesToDraw)
at Dundas.Charting.WebControl.ChartTypes.LineChart.Paint(ChartGraphics
graph, CommonElements common, ChartArea area, Series seriesToDraw)
at Dundas.Charting.WebControl.ChartArea.Paint(ChartGraphics graph)
at Dundas.Charting.WebControl.ChartPicture.Paint(Graphics graph, Boolean
cursorsOnly, Boolean svgMode, XmlTextWriter svgTextWriter, String
documentTitle, Boolean resizable, Boolean preserveAspectRatio)
at Dundas.Charting.WebControl.ChartImage.GetImage()
at Dundas.Charting.WebControl.Chart.Save(Stream imageStream)
at
Microsoft.ReportingServices.ReportRendering.DundasChart.GetImage(ImageType
type, Boolean& hasImageMap)
at
Microsoft.ReportingServices.ReportRendering.DundasChart.GetImage(ImageType
type, ChartInstanceInfo instanceInfo, Boolean& hasImageMap)
at Microsoft.ReportingServices.ReportRendering.Chart.GetImage(ImageType
type, Boolean& hasImageMap)
-- End of inner exception stack trace --
w3wp!reportrendering!2afc!06/30/2006-15:38:34:: e ERROR: Throwing
Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException:
Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
thrown., ;
Info:
Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException:
Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
thrown. -->
Microsoft.ReportingServices.ReportRendering.ReportRenderingException:
Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
thrown. -->
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: An
error has occurred during rendering of chart chart1. Details: Out of memory.
--> System.OutOfMemoryException: Out of memory.
-- End of inner exception stack trace --
at
Microsoft.ReportingServices.Rendering.HtmlRenderer.RenderingExtensionBase.Render(Report
report, NameValueCollection reportServerParameters, NameValueCollection
deviceInfo, NameValueCollection clientCapabilities,
EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions,
CreateAndRegisterStream createAndRegisterStream)
at
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.a(DateTime A_0,
GetReportChunk A_1, ProcessingContext A_2, RenderingContext A_3,
CreateReportChunk A_4, Boolean& A_5)
-- End of inner exception stack trace --
w3wp!library!2afc!06/30/2006-15:38:34:: i INFO: Initializing
EnableExecutionLogging to 'True' as specified in Server system properties.
w3wp!webserver!2afc!06/30/2006-15:38:49:: e ERROR: Reporting Services error
Microsoft.ReportingServices.Diagnostics.Utilities.RSException: Exception of
type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
thrown. -->
Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException:
Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
thrown. -->
Microsoft.ReportingServices.ReportRendering.ReportRenderingException:
Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
thrown. -->
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: An
error has occurred during rendering of chart chart1. Details: Out of memory.
--> System.OutOfMemoryException: Out of memory.
w3wp!library!7c40!6/30/2006-15:38:51:: i INFO: Cleaned 0 batch records, 0
policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks, 0 running jobs
w3wp!library!2afc!06/30/2006-15:39:09:: e ERROR: Found
System.OutOfMemoryException exception:
Microsoft.ReportingServices.Diagnostics.Utilities.RSException: Exception of
type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
thrown. -->
Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException:
Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
thrown. -->
Microsoft.ReportingServices.ReportRendering.ReportRenderingException:
Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
thrown. -->
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: An
error has occurred during rendering of chart chart1. Details: Out of memory.
--> System.OutOfMemoryException: Out of memory.
w3wp!library!2afc!06/30/2006-15:39:09:: e ERROR: Terminating worker process
How to avoid the out of memory? Thanks
"Jun Yuan" wrote:
> I'm using SQL Server 2000 Reporting Services. I want to create a curve using
> chart for a series of data.
> If the number of points (x axis) is small (less than 800), the curve is
> displayed correctly.
> But if the number of points is more than 800, the curve cannot come out,
> after about 1 minute, in the "Event Viewer" of the server, I find a system
> warning:
> "A process serving application pool 'DefaultAppPool' suffered a fatal
> communication error with the World Wide Web Publishing Service. The process
> id was '22656'. The data field contains the error number(8007006d). "
> Anyone could give me a solution to fix it? Thanks
>

Does auto update stats do anything if auto create stats are turned off

I am using SQL 2000 and wondered if a database is created and auto create
stats would be turned off, but auto update stats are turned on, does auto
update stats have any stats to update ? I am kinda confused ? Assuming we
load some tables with indexes and have stored procedures,etc..and start
modifying data, will update stats have any stats to work with since auto
create stats is off ? Will query plans ever be recompiled ? Will the query
optimiser figure a different execution plan ?
Are statistics ever created ? Do indexes behave like statistics ?
Thank youHi Hassan,
There is a difference between 'Statistics' as an 'object' and 'Statistical
Information' which is the data stored in the row.
When 'Auto Create Stats' Is turned off, no new statistics will be generated
automatically , i.e. no new statistical 'objects' will be created.
A column that already has a 'statistics' created for it, will still have
it's statistics information 'refreshed' or recollected. this option is
controlled by the 'Auto Update Statistics' option.
Statistics are a seperate object from Indexes. Indexes 'have' Statistics
information that is collected by the query optimizer and stored in the index
row in sysindexes.
Statistics are created not only for indexes, but also for unindexed columns
(for example, columns that participate in join conditions) and are also
stored in sysindexes.
This is done automatically by the query optimizer when the 'Auto Create
Statistics' option is on or you could create it yourself by using the CREATE
STATISTICS statement. To update statistics yourself, you can use the UPDATE
STATISTICS statement or the sp_updatestats system SP.
If you disable either DB options and just leave it at that (not creating and
updating stats), the query optimizer will not be able to choose optimal
execution plans.
It will still produce some plan and your queries will be executed but it's
bound to have a negative effect on performance.
I hope it makes some sense :-)
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OQcYPi7YDHA.2032@.TK2MSFTNGP10.phx.gbl...
> I am using SQL 2000 and wondered if a database is created and auto create
> stats would be turned off, but auto update stats are turned on, does auto
> update stats have any stats to update ? I am kinda confused ? Assuming we
> load some tables with indexes and have stored procedures,etc..and start
> modifying data, will update stats have any stats to work with since auto
> create stats is off ? Will query plans ever be recompiled ? Will the query
> optimiser figure a different execution plan ?
> Are statistics ever created ? Do indexes behave like statistics ?
> Thank you
>|||So if I understand correctly, if Auto Create Stats are not turned on ,
'Statistics' will not be created and hence Auto Update Statistics will only
work with the index created 'Statistics' since only indexes are created
assuming we did not Create Statistics manually. Am I correct ?
"Amy" <l.a@.usa.com> wrote in message
news:eQbneiAZDHA.3232@.tk2msftngp13.phx.gbl...
> Hi Hassan,
> There is a difference between 'Statistics' as an 'object' and 'Statistical
> Information' which is the data stored in the row.
> When 'Auto Create Stats' Is turned off, no new statistics will be
generated
> automatically , i.e. no new statistical 'objects' will be created.
> A column that already has a 'statistics' created for it, will still have
> it's statistics information 'refreshed' or recollected. this option is
> controlled by the 'Auto Update Statistics' option.
> Statistics are a seperate object from Indexes. Indexes 'have' Statistics
> information that is collected by the query optimizer and stored in the
index
> row in sysindexes.
> Statistics are created not only for indexes, but also for unindexed
columns
> (for example, columns that participate in join conditions) and are also
> stored in sysindexes.
> This is done automatically by the query optimizer when the 'Auto Create
> Statistics' option is on or you could create it yourself by using the
CREATE
> STATISTICS statement. To update statistics yourself, you can use the
UPDATE
> STATISTICS statement or the sp_updatestats system SP.
> If you disable either DB options and just leave it at that (not creating
and
> updating stats), the query optimizer will not be able to choose optimal
> execution plans.
> It will still produce some plan and your queries will be executed but it's
> bound to have a negative effect on performance.
> I hope it makes some sense :-)
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:OQcYPi7YDHA.2032@.TK2MSFTNGP10.phx.gbl...
> > I am using SQL 2000 and wondered if a database is created and auto
create
> > stats would be turned off, but auto update stats are turned on, does
auto
> > update stats have any stats to update ? I am kinda confused ? Assuming
we
> > load some tables with indexes and have stored procedures,etc..and start
> > modifying data, will update stats have any stats to work with since auto
> > create stats is off ? Will query plans ever be recompiled ? Will the
query
> > optimiser figure a different execution plan ?
> >
> > Are statistics ever created ? Do indexes behave like statistics ?
> >
> > Thank you
> >
> >
>|||AFAIK, Auto update will continue to update all statistics that were created
before turning the option off, including indexes and column statistics.
If you did not execute any queries on the DB prior to turning the auto
create option off, probably no auto statistics were created for columns.
It is recommended that you leave both options 'on'.
The creation process will probaly have a quick 'run in' time. Initially,
there will be no column statistics. once you start issuing queries against
the DB, the query optimizer will create the proper statistics and once it
has 'seen' all your queries, there will be no need to create new ones unless
you change the schema or introduce new queries that use previously unused
columns for joining, searching etc.
The updating process is controlled by SQL server. It uses an algorithm that
takes into account the size of the table and the number of changed rows
since the last statistics collection. It will automatically initiate the
recollection of statistical data when it thinks the old data might be
invalid.
'The cost of this automatic statistical update is minimized by sampling the
data, rather than analyzing all of it.'
Unless your DB experinces very heavy modifications on very large tables and
you have verified for a fact that the statistical update process is causing
performance issues during peak usage and you make sure that the density and
distribution of data does not change in such a way that not updating the
statistics will not cause the optimizer to choose inappropriate plans and
have tested this thoroughly and have prepared some alternatives to creation
and update of statistics during off peak hours etc. etc. then there is
really no need to turn these option off.
Look in BOL under 'statistical information'.
Kalen's 'Inside SQL Server 2000' has an excellent chapter about the query
processor and the way SQL Server handles statistics.
HTH
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eoTHooBZDHA.2020@.TK2MSFTNGP10.phx.gbl...
> So if I understand correctly, if Auto Create Stats are not turned on ,
> 'Statistics' will not be created and hence Auto Update Statistics will
only
> work with the index created 'Statistics' since only indexes are created
> assuming we did not Create Statistics manually. Am I correct ?
> "Amy" <l.a@.usa.com> wrote in message
> news:eQbneiAZDHA.3232@.tk2msftngp13.phx.gbl...
> > Hi Hassan,
> >
> > There is a difference between 'Statistics' as an 'object' and
'Statistical
> > Information' which is the data stored in the row.
> > When 'Auto Create Stats' Is turned off, no new statistics will be
> generated
> > automatically , i.e. no new statistical 'objects' will be created.
> > A column that already has a 'statistics' created for it, will still have
> > it's statistics information 'refreshed' or recollected. this option is
> > controlled by the 'Auto Update Statistics' option.
> >
> > Statistics are a seperate object from Indexes. Indexes 'have' Statistics
> > information that is collected by the query optimizer and stored in the
> index
> > row in sysindexes.
> > Statistics are created not only for indexes, but also for unindexed
> columns
> > (for example, columns that participate in join conditions) and are also
> > stored in sysindexes.
> > This is done automatically by the query optimizer when the 'Auto Create
> > Statistics' option is on or you could create it yourself by using the
> CREATE
> > STATISTICS statement. To update statistics yourself, you can use the
> UPDATE
> > STATISTICS statement or the sp_updatestats system SP.
> >
> > If you disable either DB options and just leave it at that (not creating
> and
> > updating stats), the query optimizer will not be able to choose optimal
> > execution plans.
> > It will still produce some plan and your queries will be executed but
it's
> > bound to have a negative effect on performance.
> >
> > I hope it makes some sense :-)
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:OQcYPi7YDHA.2032@.TK2MSFTNGP10.phx.gbl...
> > > I am using SQL 2000 and wondered if a database is created and auto
> create
> > > stats would be turned off, but auto update stats are turned on, does
> auto
> > > update stats have any stats to update ? I am kinda confused ? Assuming
> we
> > > load some tables with indexes and have stored procedures,etc..and
start
> > > modifying data, will update stats have any stats to work with since
auto
> > > create stats is off ? Will query plans ever be recompiled ? Will the
> query
> > > optimiser figure a different execution plan ?
> > >
> > > Are statistics ever created ? Do indexes behave like statistics ?
> > >
> > > Thank you
> > >
> > >
> >
> >
>