"use database" not working after "create database"

I didn't think you could use CREATE DATABASE within a transaction.
"ATS" <ATS@.discussions.microsoft.com> wrote in message
news:5F6A2B04-1923-4BDB-B036-FF1B7C445477@.microsoft.com...
> PRB: "use database" not working after "create database"
> Please help,
> I have the following query:
> set XACT_ABORT on
> begin transaction
> create database MY_DB
> use MY_DB
> commit transaction
> The "use" statement fails saying the database does not exists, but I get
no
> error on the "create". And when I go to the server, sure enough the DB is
not
> there, which it should not be if the TX rolled back. So what is wrong? If
the
> "create" is bad, why do I not see an error on it?I do not think you can use the use statement inside a transaction either
"Armando Prato" wrote:

> I didn't think you could use CREATE DATABASE within a transaction.
> "ATS" <ATS@.discussions.microsoft.com> wrote in message
> news:5F6A2B04-1923-4BDB-B036-FF1B7C445477@.microsoft.com...
> no
> not
> the
>
>

"Unable to count the members of the level" error

Hi guys,
I encountering the following error whenever I try to create a new dimension
in the MS Analysis Server.
"Unable to count the members of the 'Area Code' level.Please check your
table joins."
I have check my table joins, all the valid. Also I am using the snowflake
schema.
How to resolve the issue?
Thanks
Akber.
When you process the dimension, the actual SQL that Analysis Server sends to
SQL Server is shown in the processing window. You can copy this and execute
it yourself in Query Analyzer to see if the results you get are what you
expect.
Jacco Schalkwijk
SQL Server MVP
"Akber" <Akber@.discussions.microsoft.com> wrote in message
news:92E7058A-4C42-481E-8179-ABACF327271E@.microsoft.com...
> Hi guys,
> I encountering the following error whenever I try to create a new
> dimension
> in the MS Analysis Server.
> "Unable to count the members of the 'Area Code' level.Please check your
> table joins."
> I have check my table joins, all the valid. Also I am using the snowflake
> schema.
> How to resolve the issue?
> --
> Thanks
> Akber.
|||You might also consider enabling the system-wide processing log file. This
is a best practice with Analysis Services.
To enable it, bring up Analysis Manager. Right-click on the server and
select "Properties..."
Then go to the "Logging" tab. Enable the processing log file and specify
some well known location, e.g. C:\OLAP_Processing.txt
Then all of the SQL Statements, begin/end times, # of records processed,
etc. -- everything that comes out in the processing dialog box -- will also
be written to the system-wide processing log file. This allows you to keep
long-term history of what was processed, when, and by-whom.
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:ugLq4LqhEHA.3548@.TK2MSFTNGP09.phx.gbl...
> When you process the dimension, the actual SQL that Analysis Server sends
to
> SQL Server is shown in the processing window. You can copy this and
execute[vbcol=seagreen]
> it yourself in Query Analyzer to see if the results you get are what you
> expect.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Akber" <Akber@.discussions.microsoft.com> wrote in message
> news:92E7058A-4C42-481E-8179-ABACF327271E@.microsoft.com...
snowflake
>
|||and whats appends if you create the same SQL query in the query analyzer,
does the same query (with the same join) return something?
you can catch the query through the log or through the SQL Server profiler.
also, does your tables have some data ?
another way to understand what AS do is to put youself some value in the
dimension count property of your dimension level (put 10 for example)
then process the dimension and you'll see (in the process dialog box) again
the query used by AS to retreive the database values.
"Akber" <Akber@.discussions.microsoft.com> a crit dans le message de news:
92E7058A-4C42-481E-8179-ABACF327271E@.microsoft.com...
> Hi guys,
> I encountering the following error whenever I try to create a new
> dimension
> in the MS Analysis Server.
> "Unable to count the members of the 'Area Code' level.Please check your
> table joins."
> I have check my table joins, all the valid. Also I am using the snowflake
> schema.
> How to resolve the issue?
> --
> Thanks
> Akber.

"Unable to count the members of the level" error

Hi guys,
I encountering the following error whenever I try to create a new dimension
in the MS Analysis Server.
"Unable to count the members of the 'Area Code' level.Please check your
table joins."
I have check my table joins, all the valid. Also I am using the snowflake
schema.
How to resolve the issue'
Thanks
Akber.When you process the dimension, the actual SQL that Analysis Server sends to
SQL Server is shown in the processing window. You can copy this and execute
it yourself in Query Analyzer to see if the results you get are what you
expect.
Jacco Schalkwijk
SQL Server MVP
"Akber" <Akber@.discussions.microsoft.com> wrote in message
news:92E7058A-4C42-481E-8179-ABACF327271E@.microsoft.com...
> Hi guys,
> I encountering the following error whenever I try to create a new
> dimension
> in the MS Analysis Server.
> "Unable to count the members of the 'Area Code' level.Please check your
> table joins."
> I have check my table joins, all the valid. Also I am using the snowflake
> schema.
> How to resolve the issue'
> --
> Thanks
> Akber.|||You might also consider enabling the system-wide processing log file. This
is a best practice with Analysis Services.
To enable it, bring up Analysis Manager. Right-click on the server and
select "Properties..."
Then go to the "Logging" tab. Enable the processing log file and specify
some well known location, e.g. C:\OLAP_Processing.txt
Then all of the SQL Statements, begin/end times, # of records processed,
etc. -- everything that comes out in the processing dialog box -- will also
be written to the system-wide processing log file. This allows you to keep
long-term history of what was processed, when, and by-whom.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:ugLq4LqhEHA.3548@.TK2MSFTNGP09.phx.gbl...
> When you process the dimension, the actual SQL that Analysis Server sends
to
> SQL Server is shown in the processing window. You can copy this and
execute
> it yourself in Query Analyzer to see if the results you get are what you
> expect.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Akber" <Akber@.discussions.microsoft.com> wrote in message
> news:92E7058A-4C42-481E-8179-ABACF327271E@.microsoft.com...
snowflake[vbcol=seagreen]
>|||and whats appends if you create the same SQL query in the query analyzer,
does the same query (with the same join) return something?
you can catch the query through the log or through the SQL Server profiler.
also, does your tables have some data ?
another way to understand what AS do is to put youself some value in the
dimension count property of your dimension level (put 10 for example)
then process the dimension and you'll see (in the process dialog box) again
the query used by AS to retreive the database values.
"Akber" <Akber@.discussions.microsoft.com> a crit dans le message de news:
92E7058A-4C42-481E-8179-ABACF327271E@.microsoft.com...
> Hi guys,
> I encountering the following error whenever I try to create a new
> dimension
> in the MS Analysis Server.
> "Unable to count the members of the 'Area Code' level.Please check your
> table joins."
> I have check my table joins, all the valid. Also I am using the snowflake
> schema.
> How to resolve the issue'
> --
> Thanks
> Akber.

"Unable To Connect to SQL Server" from Crystal 8.0

Hi All,

I am using Crystal Report 8.0 to create report on MSDE sp3a , but when I try to make connection Natively :
More Data Source->MiCrosoft SQL Server-> [Dialog opens]
then I type the ServerName, UserID and Password.

System hangs for a while, then I get this error :
"Unable To Connect to Sql server MyServer"

BUT IF I create system DSN (ODBC) then create the report, it works.

Why can't I create report Natively on MSDE ??

Thank you very much for any help,
KristWhat are you typing for ServerName? Is this database on a base or named instance? Also, don't it ask you for the database name?|||Hi,
Thanks for your response.

>What are you typing for ServerName?
I type 'XTANTO', it is my computer name, as I can see on service manager.

>Is this database on a base or named instance?
This is a base instance. FYI, this is a test computer, sp many 'install & uninstall' here, previously there is SQL Server personal edition there, but I already uninstalled it.
After that I install 'Client Connectivity only', and there is No way I can uninstall it. Then I install MSDE SP3a. and I get the error.
Does this give us any clue ?

>Also, don't it ask you for the database name?
Only after I type ServerName, UserID and Password correctly.
In this case, it doesn't give me list of database to choose because its error "Unable To Connect to SQL Server"

My last try will be reinstall win 98.
Do you think that will help ?

Thank you for your help,
Krist|||Go to Control Panel/Administrative Tools/Services. There should be a service that starts with MSSQL. Is it started? What is the full name of the service (or all the services that start with MSSQL)?|||Hi,

I am on Win98, there is NO services on control panel.
What else should I try ?

Thank you,
Krist|||Actually, here's a better question. Why are you running this on a Windows 98 machine??? Do a search for SQLScan on http://www.microsoft.com/downloads/details.aspx?FamilyID=9552d43b-04eb-4af9-9e24-6cde4d933600&displaylang=en

Run that on your machine. See what it says the name of the instance is.|||Hi Derrick,
Thanks for keep on helping..

>Actually, here's a better question. Why are you running this on a Windows >98 machine???
We are software developer company, we want to reach as wide market as possible, we want to keep on supporting windows 98.

The docs said Windows 98 is supported. Is there any known issue of MSDE 2000 on WIn 98 ?

Thank you,
Krist|||No that I know of. Did you run that tool on your computer?

"SQL Server does not exist or access denied" PPC

Hi everybody,
I have a great problem with Sql Server connections in Pocket Pc. When I try
to create a connection with an Sql Server 2000 with Visual Basic.NET 2003 (I
have Windows XP), I receive the error : "SQL Server does not exist or access
denied".
My connection string is something like:
Dim conn as new Sqlconnection("Server=NAMESERVER;Initial Catalog=Aname;User
ID=anID;Password=Apwd")
I've tried to make changes like Server=192.168.1..10 or change the user and
the password, or include the Integrated Security=SSPI with no success.
Could you help me, please?
I have found the solution by myself:
in the connection string, change the server name by his IP. Then
go to "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\SVRNETCN.exe"
and then enable TCP/IP (put TCP/IP in the right square).
Accept changes... Voil!
"Antonio Prados" <antonio.prados@.eutisoftware.com> escribi en el mensaje
news:35bsm3F4iukicU1@.individual.net...
> Hi everybody,
> I have a great problem with Sql Server connections in Pocket Pc. When I
try
> to create a connection with an Sql Server 2000 with Visual Basic.NET 2003
(I
> have Windows XP), I receive the error : "SQL Server does not exist or
access
> denied".
> My connection string is something like:
> Dim conn as new Sqlconnection("Server=NAMESERVER;Initial
Catalog=Aname;User
> ID=anID;Password=Apwd")
> I've tried to make changes like Server=192.168.1..10 or change the user
and
> the password, or include the Integrated Security=SSPI with no success.
> Could you help me, please?
>

"SQL Server does not exist or access denied" PPC

Hi everybody,
I have a great problem with Sql Server connections in Pocket Pc. When I try
to create a connection with an Sql Server 2000 with Visual Basic.NET 2003 (I
have Windows XP), I receive the error : "SQL Server does not exist or access
denied".
My connection string is something like:
Dim conn as new Sqlconnection("Server=NAMESERVER;Initial Catalog=Aname;User
ID=anID;Password=Apwd")
I've tried to make changes like Server=192.168.1..10 or change the user and
the password, or include the Integrated Security=SSPI with no success.
Could you help me, please?I have found the solution by myself:
in the connection string, change the server name by his IP. Then
go to "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\SVRNETCN.exe"
and then enable TCP/IP (put TCP/IP in the right square).
Accept changes... Voil!
"Antonio Prados" <antonio.prados@.eutisoftware.com> escribi en el mensaje
news:35bsm3F4iukicU1@.individual.net...
> Hi everybody,
> I have a great problem with Sql Server connections in Pocket Pc. When I
try
> to create a connection with an Sql Server 2000 with Visual Basic.NET 2003
(I
> have Windows XP), I receive the error : "SQL Server does not exist or
access
> denied".
> My connection string is something like:
> Dim conn as new Sqlconnection("Server=NAMESERVER;Initial
Catalog=Aname;User
> ID=anID;Password=Apwd")
> I've tried to make changes like Server=192.168.1..10 or change the user
and
> the password, or include the Integrated Security=SSPI with no success.
> Could you help me, please?
>

"SQL Server does not exist or access denied" error

I'm trying to create to set up connection between my web application on the server and
the database on another box and getting "SQL Server does not exist or access denied" error.
Both the web server and the database machine are Windows 2003 Server, there's no firewall
enabled, there's mixed authentication enabled on the sql server. The machines are not on the
same domain so I'm using SQL Server authentication from web.config. Here's my connection string:
<add key="ConnectionString" value="Server=sqlboxip;User id=sqlaccountname;Pwd=sqlpass;Initial catalog=dbname">
I've also got <identity impersonate="true"> set in web.config.
Had it the connection working at some stage but then with more tweaking and app reinstall
back square one... Please help!</identity></add>Hi,
Are you able to connect to the SQL Server Database using the same credentials from SQL Server (Enterprise Manager or Query Analyzer?)
Thanks.|||Yes, I can connect to it from my dev box in ASP.NET
|||Did you set the connection string of your connection on that page of the app?

I put this in the page load.
this.cnMainConnection.ConnectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionsString"].ToString();

|||Hi,
Ok, can you post the code which you are trying to execute.
Thanks.

"select" statement in a function

CREATE FUNCTION GetPerson (@.SSN integer, @.NamePrefix varchar(10), @.FirstName varchar(30), @.MiddleName varchar(30), @.LastName varchar(40), @.NameSuffix varchar(10), @.HomeID integer, @.MailID integer, @.DOB timestamp, @.Gender varchar(1), @.MaritalStatus varchar(1))
RETURNS integer as
BEGIN
DECLARE @.PersonID integer
set @.PersonID=0
if @.SSN>0 and @.SSN<999999999
Begin
select Min(lngPersonID) AS PersonID from Persons where lngSSN=@.SSN
End
ELSE
if @.SSN is not null
BEGIN
IF @.LastName is not null and @.FirstName is not null and @.MiddleName is not null and @.NamePrefix is not null and @.NameSuffix is not null
Begin
select MIN(lngPersonID) AS PersonID from Persons
where strNamePrefix= @.NamePrefix and strFirstName=@.FirstName
and strMiddleName=@.MiddleName and strLastName=@.LastName
and strNameSuffix=@.NameSuffix and lngSSN=@.SSN
End
ELSE
if @.LastName is not null
BEGIN
select MIN(lngPersonID) as PersonID from Persons
where strLastName=@.LastName
and lngSSN = @.SSN
END
END
return (@.personID)
END

I m having problem with the "Select" function
the error I m getting is
Select statements included within a function cannot return data to a client (error 444)

Cann I use "select" statement in the function? If not what is the alternative?

I reduced the size of the sproc because it is a big one, I donn have any proble with syntax.

ThanksWhy isn't this a stored procedure?|||Brett

It can be, but I guess you answered many of my questions
I still have this question left in me.

Can a stored procedure return a value??
As far as my knowledge goes it returns with a
"select" statement. Is it true?? Is there any way to check the returned value.
I am very much confused with this concept.

Can I make all functions in a sybase database as stored procedures in SQL??

If it doesnot return then I have to make it a function for sure.

Can you please answer these questions?

Thanks|||USE Northwind
GO

CREATE PROC MySproc99 @.HoursToMargarittaville Decimal(15,4) OUTPUT
AS
BEGIN
DECLARE @.Weekend datetime
SELECT @.Weekend = 'Jan 22 2004 17:00:00'
SELECT @.HoursToMargarittaville = DATEDIFF(mi, GetDate(), @.Weekend)/60.00
Return 0
END
GO

DECLARE @.x decimal(15,4), @.rc int
EXEC @.rc = MySproc99 @.x OUTPUT
SELECT @.x, @.rc
GO

DROP PROC mySproc99
GO|||You can use a select function for assignment, but you cannot return the results of a select statement.

Here is your function rewritten. I also cleaned up some of the unnecessary BEGIN/END pairs and used a COALESCE function rather than mutiple IS NULL checks.

CREATE FUNCTION GetPerson (@.SSN integer, @.NamePrefix varchar(10), @.FirstName varchar(30), @.MiddleName varchar(30), @.LastName varchar(40), @.NameSuffix varchar(10), @.HomeID integer, @.MailID integer, @.DOB timestamp, @.Gender varchar(1), @.MaritalStatus varchar(1))
RETURNS integer as
BEGIN
DECLARE @.PersonID integer
set @.PersonID=0
if @.SSN>0 and @.SSN<999999999 set @.PersonID = (select Min(lngPersonID) AS PersonID from Persons where lngSSN=@.SSN)
ELSE if @.SSN is not null
BEGIN
IF COALESCE(@.LastName, @.FirstName, @.MiddleName, @.NamePrefix, @.NameSuffix) is not null
set @.PersonID = (select MIN(lngPersonID) AS PersonID from Persons where strNamePrefix= @.NamePrefix and strFirstName=@.FirstName and strMiddleName=@.MiddleName and strLastName=@.LastName and strNameSuffix=@.NameSuffix and lngSSN=@.SSN)
ELSE if @.LastName is not null set @.PersonID = (select MIN(lngPersonID) as PersonID from Persons where strLastName=@.LastName and lngSSN = @.SSN)
END
return (@.personID)
END

Be aware that I think your logic will fail when comparing NULL values, because NULL values cannot be compared to eachother. For example:
-----------
declare @.NULL1 varchar
declare @.NULL2 varchar
if @.NULL1 = @.NULL2 print 'Success' else print 'Failure'
-----------|||Oops. Looked at your code again and I see that you don't allow nulls for the comparison, so COALESCE is not appropriate. Use this instead:

CREATE FUNCTION GetPerson (@.SSN integer, @.NamePrefix varchar(10), @.FirstName varchar(30), @.MiddleName varchar(30), @.LastName varchar(40), @.NameSuffix varchar(10), @.HomeID integer, @.MailID integer, @.DOB timestamp, @.Gender varchar(1), @.MaritalStatus varchar(1))
RETURNS integer as
BEGIN
DECLARE @.PersonID integer
set @.PersonID=0
if @.SSN>0 and @.SSN<999999999 set @.PersonID = (select Min(lngPersonID) AS PersonID from Persons where lngSSN=@.SSN)
ELSE if @.SSN is not null
BEGIN
IF @.LastName is not null and @.FirstName is not null and @.MiddleName is not null and @.NamePrefix is not null and @.NameSuffix is not null
set @.PersonID = (select MIN(lngPersonID) AS PersonID from Persons where strNamePrefix= @.NamePrefix and strFirstName=@.FirstName and strMiddleName=@.MiddleName and strLastName=@.LastName and strNameSuffix=@.NameSuffix and lngSSN=@.SSN)
ELSE if @.LastName is not null set @.PersonID = (select MIN(lngPersonID) as PersonID from Persons where strLastName=@.LastName and lngSSN = @.SSN)
END
return (@.personID)
END|||Yo, blind dude...

You don't think s/he would be better of with a sproc?

especially in light of a (sounds like massive) sybase to sql server conversion?|||Honestly, (MOO of course) I would use a function here, because I imagine this logic could be called by several different procedures. I've never liked using stored procedures that return scalar OUTPUT. I think the implementation is clunky, so since UDFs were introduced they have been my preferred method.|||MOO indeed...

Want to show me what the udf call would look like?|||see if you can not re-code your function to something like

Create function name (@.param...)
Returns Table

AS

Return Seturn Select ... Your SQL Goes here...

See also BOL, In-Line Functions

The only limitation is that you can have a single select sql phrase inside your function.|||I would call the UDF like this: MOO! MOOOOO! MOO MOO MOO MOO MOO!

Whether it would come when I call, I don't know...

"select into" vs "insert into" question

Hello,

I have read in a number of forums that when using temporary tables, it is best to create the tables first before inserting the records rather than simply executing a "select into" to ensure that locking issues are alleviated.

I have to say that I am one of those developers who has relied on using the "select into" statement - the main reason being that I have almost always found the "select into" statement to be much faster than an "insert into" statement.

Obviously, the number of users running queries simultaneously when scripts that use "select into" statements are executed is a big deciding factor on whether "select into" or "insert into" should be used.

I'd appreciate it if someone can shed more light on other factors that should be taken into consideration when deciding to use "select into" vs "insert into".

Thanks in advance for your time and reply.It used to be that SELECT INTO was a no-no because it was an unlogged transaction, after which database recoverability was questionable. With SQL Server 2000 I do not believe this is still an issue, although I have never found documentation clarifying the matter.

INSERT INTO is slower partly because it verifies unique keys and other data restraints as the data is entered. SELECT INTO does not do this, but if you are selecting from a table that already has these restrictions inforced then it is not an issue.

Also, in many cases a table variable is the fastest method to use. Check into them and see if they might be more appropriate for your application.

"Replicating" SQL user accounts

Good day,
I am wanting to "replicate" user accounts between two SQL 2000 servers automatically. I.E. If I create a user on the first server, I want it automatically created on the second SQL server, if it doen't already exist.
Is there a quick and easy way to do this? If so how? I don't have to use replication, so if there is another way (I.E. DTS, etc) then that would also be great.
Thank you in advance,
Brian
no, you can't do this - the system tables can't be replicated only user
objects.
"Brian" <Brian.Louw@.sbs.siemens.co.za> wrote in message
news:17CDD729-D396-4FAC-80AB-FEE5555C530F@.microsoft.com...
> Good day,
> I am wanting to "replicate" user accounts between two SQL 2000 servers
automatically. I.E. If I create a user on the first server, I want it
automatically created on the second SQL server, if it doen't already exist.
> Is there a quick and easy way to do this? If so how? I don't have to use
replication, so if there is another way (I.E. DTS, etc) then that would also
be great.
> Thank you in advance,
> Brian
|||Brian,
there is no way to automatically do this; replication/triggers can't be used
on system tables, but you can use DTS transfer logins task, or you can
script out the logins using sp_help_revlogin (search for this proc on Google
to get info).
Regards,
Paul Ibison

"Logon failed" when attempting to connect to cube.

Greetings. I am brand new to SRS, so please bear with me. I'm trying to
create a Report Model in SRS 2005 against an Analysis Services 2005 cube. I
am an admin on the AS box. I am using this tutorial:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rptsrvr9/html/8e5d2bd3-48ec-45f3-afee-6d86797c8f28.htm
I set up the data source, then I hit "generate model". I type in my model
name, hit "OK", and get the "Logon failed (rsLogonFailed)" message. I am
using "credentials supplied by the user running the report" dialog box with
the "Use as Windows..." checkbox checked. For good measure I have even added
myself to a cube Role with admin privelages. Alos, I can in fact browse and
manipulate the cube.
Any ideas?
TIA, ChrisRHi
I think the issue here is not the users priviliges in SSAS , rather
the priviliges in SSRS. Try using report manager to configure
permissions for the user in question .
Cheers
Shai
On Nov 20, 12:34 am, ChrisR <Chr...@.discussions.microsoft.com> wrote:
> Greetings. I am brand new to SRS, so please bear with me. I'm trying to
> create a Report Model in SRS 2005 against an Analysis Services 2005 cube. I
> am an admin on the AS box. I am using this tutorial:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rptsrvr9/html/8e5d2bd3-48ec-45f3-afee--6d86797c8f28.htm
> I set up the data source, then I hit "generate model". I type in my model
> name, hit "OK", and get the "Logon failed (rsLogonFailed)" message. I am
> using "credentials supplied by the user running the report" dialog box with
> the "Use as Windows..." checkbox checked. For good measure I have even added
> myself to a cube Role with admin privelages. Alos, I can in fact browse and
> manipulate the cube.
> Any ideas?
> TIA, ChrisR

"error 220: arithmetic overflow" in publication

I am unable to alter or create a publication that uses a particular
table. If I try to create a publication with this table or if I try
to add or remove a column from my existing publication that uses this
table, i get the error message "error 220: arithmetic overflow error
for datatype tinyint, value = -9."
There are no fields of "tinyint" type in this table. In fact, I've
looked at all the tinyint field values throughout the entire datbase
(system tables included) and none of them have -9 for a value.
I am able to add or remove columns from other articles in this
publication.
Anyone know some things I can check? Thanks.
Can you post the schema of the table here?
Are you from Alberta? I was born there
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<thatoneguyfromab@.gmail.com> wrote in message
news:1173891171.439464.193680@.d57g2000hsg.googlegr oups.com...
>I am unable to alter or create a publication that uses a particular
> table. If I try to create a publication with this table or if I try
> to add or remove a column from my existing publication that uses this
> table, i get the error message "error 220: arithmetic overflow error
> for datatype tinyint, value = -9."
> There are no fields of "tinyint" type in this table. In fact, I've
> looked at all the tinyint field values throughout the entire datbase
> (system tables included) and none of them have -9 for a value.
> I am able to add or remove columns from other articles in this
> publication.
> Anyone know some things I can check? Thanks.
>
|||some names have been changed to protect the innocent, i hope this is
useful to you
no, I've never been to Alberta ;)
FieldTypeLengthPrecision
field1money19
field2nvarchar50
field3nvarchar50
field4nvarchar50
field5money19
field6datetime23
field7money19
field8datetime23
field9float53
field10money19
field11money19
field12money19
field13money19
field14nvarchar50
field15numeric18
field16smallint5
field17smallint5
field18money19
field19money19
field20int10
field21money19
field22nvarchar50
field23money19
field24money19
field25nvarchar50
field26nvarchar50
field27money19
field28money19
field29nvarchar50
field30nvarchar50
field31nvarchar50
field32ntext1073741823
field33datetime23
field34datetime23
field35datetime23
field36ntext1073741823
field37float53
field38nvarchar50
field39ntext1073741823
field40nvarchar50
field41ntext1073741823
field42nvarchar50
field43nvarchar50
field44nvarchar50
field45datetime23
field46nvarchar50
field47nvarchar50
field48nvarchar50
field49ntext1073741823
field50money19
field51datetime23
field52float53
field53money19
field54money19
field55money19
field56money19
field57nvarchar50
field58nvarchar50
field59money19
field60smallint5
field61money19
field62smallint5
field63money19
field64money19
field65money19
field66money19
field67money19
field68datetime23
field69float53
field70money19
field71money19
field72money19
field73money19
field74numeric18
field75numeric18
field76money19
field77smallint5
field78money19
field79smallint5
field80money19
field81money19
field82int10
field83money19
field84numeric18
field85numeric18
field86numeric18
field87money19
field88nvarchar50
field89numeric18
field90nvarchar50
field91ntext1073741823
field92ntext1073741823
field93nvarchar50
field94nvarchar50
field95datetime23
field96nvarchar50
field97ntext1073741823
field98datetime23
field99money19
field100money19
field101money19
field102datetime23
field103float53
field104money19
field105money19
field106money19
field107money19
field108numeric18
field109numeric18
field110money19
field111smallint5
field112money19
field113smallint5
field114money19
field115money19
field116int10
field117money19
field118nvarchar50
field119nvarchar50
field120nvarchar50
field121nvarchar50
field122ntext1073741823
field123ntext1073741823
field124nvarchar50
field125nvarchar50
field126ntext1073741823
field127numeric18
field128money19
field129ntext1073741823
field130datetime23
field131ntext1073741823
field132nvarchar50
field133nvarchar50
field134nvarchar50
field135nvarchar50
field136varchar50
field137datetime23
field138nvarchar50
field139nvarchar50
field140nvarchar50
field141ntext1073741823
field142nvarchar50
field143nvarchar50
field144nvarchar50
field145ntext1073741823
field146nvarchar50
field147ntext1073741823
field148ntext1073741823
field149ntext1073741823
field150ntext1073741823
field151datetime23
field152ntext1073741823
field153datetime23
field154datetime23
field155nvarchar50
field156nvarchar50
field157nvarchar50
field158numeric18
field159nvarchar50
field160nvarchar50
field161ntext1073741823
field162int10
field163bit1
field164text2147483647
field165bit1
field166ntext1073741823
field167nvarchar50
field168nvarchar128

"CREATE TABLE" for files *.dbf with a custom float column

How to create dbase file based on SQL-92 which have a FLOAT column like
float(14,2):
=> xxxxxxxxxxxxxx,xx <=
Thanks in advance,
PatriceN(14,2)
"news.microsoft.com" <reprotechnic@.wanadoo.fr> escribi en el mensaje
news:u37grkt#DHA.2072@.TK2MSFTNGP11.phx.gbl...
> How to create dbase file based on SQL-92 which have a FLOAT column like
> float(14,2):
> => xxxxxxxxxxxxxx,xx <=
> Thanks in advance,
> Patrice
>|||this solution doesn't run. it makes a float column of (20,4).
With more precision, i test this with odbc.net from Framework.NET v1.1
"Luis Camacho" <luibrac@.yahoo.com.ar> a crit dans le message de
news:%23VMvv5K$DHA.1036@.TK2MSFTNGP10.phx.gbl...
> N(14,2)
> "news.microsoft.com" <reprotechnic@.wanadoo.fr> escribi en el mensaje
> news:u37grkt#DHA.2072@.TK2MSFTNGP11.phx.gbl...
>|||N(14,2): This does not work and gives a syntax error on command.
AndreB.
"Luis Camacho" <luibrac@.yahoo.com.ar> a crit dans le message de
news:%23VMvv5K$DHA.1036@.TK2MSFTNGP10.phx.gbl...
> N(14,2)
> "news.microsoft.com" <reprotechnic@.wanadoo.fr> escribi en el mensaje
> news:u37grkt#DHA.2072@.TK2MSFTNGP11.phx.gbl...
>

"Closed" database

Hello,
I was wondering if it is possible to create a database that can be
accessed only using a username password combination, even when being
accessed from the Enterprise Manager.
It seems EM can always access all data, however when I distribute the
database, I don't want some tables to be viewed by my users.
Is this possible in SQL Server?
Thanks for any answer.
Best regards,H
i> database, I don't want some tables to be viewed by my users.
> Is this possible in SQL Server?
You cannot hide them, however if the user has EM on his box so register
the sql server with an appropiate permissions , I mean first of all DENY to
view /SELECT... to the specific user
<poostwoud@.gmail.com> wrote in message
news:1149755500.564173.157240@.u72g2000cwu.googlegroups.com...
> Hello,
> I was wondering if it is possible to create a database that can be
> accessed only using a username password combination, even when being
> accessed from the Enterprise Manager.
> It seems EM can always access all data, however when I distribute the
> database, I don't want some tables to be viewed by my users.
> Is this possible in SQL Server?
> Thanks for any answer.
> Best regards,
>|||Thanks for your answer,
However, when the user wants me to use an existing server, the user
already has sa rights most of the time.
Any member of the sysadmin role cannot be denied select rights as far
as I know?
Best regards,
Uri Dimant wrote:[vbcol=seagreen]
> H
> i> database, I don't want some tables to be viewed by my users.
> You cannot hide them, however if the user has EM on his box so register
> the sql server with an appropiate permissions , I mean first of all DENY t
o
> view /SELECT... to the specific user
> <poostwoud@.gmail.com> wrote in message
> news:1149755500.564173.157240@.u72g2000cwu.googlegroups.com...|||> Any member of the sysadmin role cannot be denied select rights as far
> as I know?
True
<poostwoud@.gmail.com> wrote in message
news:1149759179.371876.206990@.i40g2000cwc.googlegroups.com...
> Thanks for your answer,
> However, when the user wants me to use an existing server, the user
> already has sa rights most of the time.
> Any member of the sysadmin role cannot be denied select rights as far
> as I know?
> Best regards,
> Uri Dimant wrote:
>|||In SQL Server 2000, if you need to "hide" data from an sa,
you would need to use some type of third party encryption.
Check the links under field level encryption in the
following FAQ:
http://www.sqlsecurity.com/FAQs/SQL...55/Default.aspx
-Sue
On 8 Jun 2006 02:32:59 -0700, poostwoud@.gmail.com wrote:
[vbcol=seagreen]
>Thanks for your answer,
>However, when the user wants me to use an existing server, the user
>already has sa rights most of the time.
>Any member of the sysadmin role cannot be denied select rights as far
>as I know?
>Best regards,
>Uri Dimant wrote:|||In SQL Server 2000, if you need to "hide" data from an sa,
you would need to use some type of third party encryption.
Check the links under field level encryption in the
following FAQ:
http://www.sqlsecurity.com/FAQs/SQL...55/Default.aspx
-Sue
On 8 Jun 2006 02:32:59 -0700, poostwoud@.gmail.com wrote:
[vbcol=seagreen]
>Thanks for your answer,
>However, when the user wants me to use an existing server, the user
>already has sa rights most of the time.
>Any member of the sysadmin role cannot be denied select rights as far
>as I know?
>Best regards,
>Uri Dimant wrote:

"Case insensitive" index

How to create "case insensitive" index? I mean, how to create a table index that will effectivelly index table by mains that differences between small and capital letters are ignored in query like:
SELECT name,surname WHERE UPPER(surname)=UPPER(?)Dobar dan :)

Check this (http://asktom.oracle.com/pls/ask/f?p=4950:8:5884730836779251727::NO::F4950_P8_DISPL AYID,F4950_P8_CRITERIA:1560806455855,) link.

"Auto Create Statistics" make queries run (really) slower

Hello,
I'm experiencing a strange problem with query performance runing on
SQL2005. The database has 10+ tables, but we need to run really
specific queries in only 1 table with these caracteristics :
- 1 million rows
- we run everyday a few thousands queries on that table, each query
is unique (adhoc plan), and not parameterizable. (we cannot optimize
this)
- rows have a lot of nvarchar data
- all queries use a lot of LIKE / NOT LIKE statement (we cannot find
any work-around to that point, Fulltext is not adequate in that case)
- when LIKE operations are performed on columns, we always create a
duplicate column to optimize some search stuff, like putting
everything in Low Case, using Latin1_General_BIN collation, ...
- we have some indexes on short nvarchar columns, only those where we
use an exact '=' statemen
- we have another index on a float column
- all usefull indexes and statistics are manually created on that
table
- the nvarchar content of the table changes only once a day. It means
we do all optimization (indexes / stats) just after the update, and
there is no change on nvarchar data until the next update (24 hours
later)
I found that when "Auto Create Statistics" is enabled on the database,
that queries are really runing slower :
- "Auto Create Statistics" enabled : 57 min to run all queries
- "Auto Create Statistics" disabled and all auto-created stats
deleted : 7 min to run the same queries
It means that queries are running 8x slower when "Auto Create
Statistics" is enabled!
Another interesting point : just after disabling "Auto Create
Statistics", the queries continue to perform slowly until I manually
delete all statistics created automatically for that table (the one
begining with "_WA_Sys_"). It could mean that it's not a stat creation
issue, but only the existence of that statistics that could change the
query plan. But in both cases, the execution plan for the same query
seems to be exactly the same (same aspect, same costs). I also tried
to enable the Async stats update : no change.
The problem is that for all the other tables in the database, the
"Auto Create Statistics" is a good thing and useful. But not for that
specific table. Two questions :
- Is it possible to disable "Auto Create Statistics" on a specific
table? (I did not find anything about that in the BOL)
- If not, is there another work-around to deal with that kind of
performance drop?
Thanks.We need the query plan with before and after to tell you why. It sounds like
there was an inaccurate estimate which might be fixed with a larger sample
than the default but that is a guess. SQL Server 2005 keeps better stats on
string column and it may be able to do a seek on a covering index in a LIKE
query especially with a larger sample. It just needs to be tested heavily.
--
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
<pinformaticien@.yahoo.fr> wrote in message
news:8384b744-0444-4f91-a1ce-b4d302317302@.13g2000hsb.googlegroups.com...
> Hello,
> I'm experiencing a strange problem with query performance runing on
> SQL2005. The database has 10+ tables, but we need to run really
> specific queries in only 1 table with these caracteristics :
> - 1 million rows
> - we run everyday a few thousands queries on that table, each query
> is unique (adhoc plan), and not parameterizable. (we cannot optimize
> this)
> - rows have a lot of nvarchar data
> - all queries use a lot of LIKE / NOT LIKE statement (we cannot find
> any work-around to that point, Fulltext is not adequate in that case)
> - when LIKE operations are performed on columns, we always create a
> duplicate column to optimize some search stuff, like putting
> everything in Low Case, using Latin1_General_BIN collation, ...
> - we have some indexes on short nvarchar columns, only those where we
> use an exact '=' statemen
> - we have another index on a float column
> - all usefull indexes and statistics are manually created on that
> table
> - the nvarchar content of the table changes only once a day. It means
> we do all optimization (indexes / stats) just after the update, and
> there is no change on nvarchar data until the next update (24 hours
> later)
> I found that when "Auto Create Statistics" is enabled on the database,
> that queries are really runing slower :
> - "Auto Create Statistics" enabled : 57 min to run all queries
> - "Auto Create Statistics" disabled and all auto-created stats
> deleted : 7 min to run the same queries
> It means that queries are running 8x slower when "Auto Create
> Statistics" is enabled!
> Another interesting point : just after disabling "Auto Create
> Statistics", the queries continue to perform slowly until I manually
> delete all statistics created automatically for that table (the one
> begining with "_WA_Sys_"). It could mean that it's not a stat creation
> issue, but only the existence of that statistics that could change the
> query plan. But in both cases, the execution plan for the same query
> seems to be exactly the same (same aspect, same costs). I also tried
> to enable the Async stats update : no change.
> The problem is that for all the other tables in the database, the
> "Auto Create Statistics" is a good thing and useful. But not for that
> specific table. Two questions :
> - Is it possible to disable "Auto Create Statistics" on a specific
> table? (I did not find anything about that in the BOL)
> - If not, is there another work-around to deal with that kind of
> performance drop?
> Thanks.|||For what I've tried, creating then updating statistics on nvarchar
columns with the "WITH FULLSCAN" clause doesn't help. But here are
some interesting results : I setup a test server, and ran 2 times 10
queries, first time with "Auto Create Statistics" enabled, second time
with "Auto Create Statistics" disabled. Between the 2 tests, I deleted
all the automatically created statistics (the one begining with
"_WA_Sys_"), then restarted SQL server service. Here are the results
for the following query
Select * from sys.dm_exec_query_optimizer_info where counter in
('optimizations','elapsed time')
"Auto Create Statistics" enabled
optimizations 11 1
elapsed time 11 2,80751895306448
"Auto Create Statistics" disabled
optimizations 11 1
elapsed time 11 0,0665338534973798
It confirms that all the performance drop goes in optimization time
(2.8 sec average vs 0.07 sec), that finally almost doesn't otimize
anything in my case (it leads to the same execution plan is the same
is both cases). It means I need to find a way to disable / reduce that
optimization time when "Auto Create Statistics" is enabled. Any idea?
Is it possible to disable "Auto Create Statistics" on a specific
table?|||It sounds like you are right. It sounds like optimizer is spending more time
try to compile since there are more options only to come up with the same
plan. You can disable autostats on a particular table with UPDATE STATISTICS
.. WITH NORECOMPUTE.
--
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
<pinformaticien@.yahoo.fr> wrote in message
news:1e199977-1bc2-4614-ad72-036bb3111ce6@.d21g2000prf.googlegroups.com...
> For what I've tried, creating then updating statistics on nvarchar
> columns with the "WITH FULLSCAN" clause doesn't help. But here are
> some interesting results : I setup a test server, and ran 2 times 10
> queries, first time with "Auto Create Statistics" enabled, second time
> with "Auto Create Statistics" disabled. Between the 2 tests, I deleted
> all the automatically created statistics (the one begining with
> "_WA_Sys_"), then restarted SQL server service. Here are the results
> for the following query
> Select * from sys.dm_exec_query_optimizer_info where counter in
> ('optimizations','elapsed time')
> "Auto Create Statistics" enabled
> optimizations 11 1
> elapsed time 11 2,80751895306448
> "Auto Create Statistics" disabled
> optimizations 11 1
> elapsed time 11 0,0665338534973798
> It confirms that all the performance drop goes in optimization time
> (2.8 sec average vs 0.07 sec), that finally almost doesn't otimize
> anything in my case (it leads to the same execution plan is the same
> is both cases). It means I need to find a way to disable / reduce that
> optimization time when "Auto Create Statistics" is enabled. Any idea?
> Is it possible to disable "Auto Create Statistics" on a specific
> table?|||Thanks for the reply. UPDATE STATISTICS ... WITH NORECOMPUTE would
just avoid statistics to be updated. In my case, it's not the stat
update which is problematic, but the existence of the automatically
created statistics (as they badly influence the query optimizer on
that table). One solution could be to move that table on a dedicated
database and turn "Auto Create Statistics" OFF, but we would like to
avoid this solution.
I'm really surprised that SQL Server doesn't allow to disable
automatic creation of statistics on a per table basis. That could be
just really helpful in some cases.|||Ok I think I've got an interesting workaround. As we cannot disable
autocreate statistics for a specific table, the idea is to update
those unwanted stats with two clauses :
- SAMPLE 0 ROWS : to empty the statistics, so that they don't
infuence the query optimizer anymore.
- NORECOMPUTE : to avoid the "auto update stats" option to repopulate
them later
Here is the SQL statement I wrote to do this automatically on SQL 2005
(you just need to set @.dbtname correctly). It's just necessary to run
it from time to time, to ensure that new autocreated stats are
disabled.
The first tests shows exactly the same performance compared to queries
with "auto create stats" disabled.
DECLARE @.dbtname NVARCHAR(255)
SET @.dbtname = 'You_Table_Name_Here'
DECLARE c CURSOR FOR
SELECT name FROM sys.stats WHERE object_id = object_id(@.dbtname) AND
auto_created = 1
DECLARE @.statname NVARCHAR(255)
OPEN c
FETCH next FROM c INTO @.statname
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT @.statname
EXEC ('UPDATE STATISTICS ' + @.dbtname + ' (' + @.statname + ') WITH
SAMPLE 0 ROWS, NORECOMPUTE')
FETCH NEXT FROM c INTO @.statname
END
CLOSE c
DEALLOCATE c|||pinformaticien@.yahoo.fr wrote:
> Ok I think I've got an interesting workaround. As we cannot disable
> autocreate statistics for a specific table, the idea is to update
> those unwanted stats with two clauses :
> - SAMPLE 0 ROWS : to empty the statistics, so that they don't
> infuence the query optimizer anymore.
> - NORECOMPUTE : to avoid the "auto update stats" option to repopulate
> them later
> Here is the SQL statement I wrote to do this automatically on SQL 2005
> (you just need to set @.dbtname correctly). It's just necessary to run
> it from time to time, to ensure that new autocreated stats are
> disabled.
> The first tests shows exactly the same performance compared to queries
> with "auto create stats" disabled.
> DECLARE @.dbtname NVARCHAR(255)
> SET @.dbtname = 'You_Table_Name_Here'
> DECLARE c CURSOR FOR
> SELECT name FROM sys.stats WHERE object_id = object_id(@.dbtname) AND
> auto_created = 1
> DECLARE @.statname NVARCHAR(255)
> OPEN c
> FETCH next FROM c INTO @.statname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> PRINT @.statname
> EXEC ('UPDATE STATISTICS ' + @.dbtname + ' (' + @.statname + ') WITH
> SAMPLE 0 ROWS, NORECOMPUTE')
> FETCH NEXT FROM c INTO @.statname
> END
> CLOSE c
> DEALLOCATE c
Auto create statistics is only invoked if there are no existing
statistics for that column. So another solution to prevent automatic
statistics creation for a particular column is to manually create
statistics before the query is run that triggers the statistics
creation.
It could be as simple as:
create statistics ST_Test on dbo.test(my_column) with sample 0 rows,
norecompute
before running a query like
select * from dbo.test where my_column = 5
--
Gert-Jan

"Auto Create Statistics" make queries run (really) slower

Hello,
I'm experiencing a strange problem with query performance runing on
SQL2005. The database has 10+ tables, but we need to run really
specific queries in only 1 table with these caracteristics :
- 1 million rows
- we run everyday a few thousands queries on that table, each query
is unique (adhoc plan), and not parameterizable. (we cannot optimize
this)
- rows have a lot of nvarchar data
- all queries use a lot of LIKE / NOT LIKE statement (we cannot find
any work-around to that point, Fulltext is not adequate in that case)
- when LIKE operations are performed on columns, we always create a
duplicate column to optimize some search stuff, like putting
everything in Low Case, using Latin1_General_BIN collation, ...
- we have some indexes on short nvarchar columns, only those where we
use an exact '=' statemen
- we have another index on a float column
- all usefull indexes and statistics are manually created on that
table
- the nvarchar content of the table changes only once a day. It means
we do all optimization (indexes / stats) just after the update, and
there is no change on nvarchar data until the next update (24 hours
later)
I found that when "Auto Create Statistics" is enabled on the database,
that queries are really runing slower :
- "Auto Create Statistics" enabled : 57 min to run all queries
- "Auto Create Statistics" disabled and all auto-created stats
deleted : 7 min to run the same queries
It means that queries are running 8x slower when "Auto Create
Statistics" is enabled!
Another interesting point : just after disabling "Auto Create
Statistics", the queries continue to perform slowly until I manually
delete all statistics created automatically for that table (the one
begining with "_WA_Sys_"). It could mean that it's not a stat creation
issue, but only the existence of that statistics that could change the
query plan. But in both cases, the execution plan for the same query
seems to be exactly the same (same aspect, same costs). I also tried
to enable the Async stats update : no change.
The problem is that for all the other tables in the database, the
"Auto Create Statistics" is a good thing and useful. But not for that
specific table. Two questions :
- Is it possible to disable "Auto Create Statistics" on a specific
table? (I did not find anything about that in the BOL)
- If not, is there another work-around to deal with that kind of
performance drop?
Thanks.
We need the query plan with before and after to tell you why. It sounds like
there was an inaccurate estimate which might be fixed with a larger sample
than the default but that is a guess. SQL Server 2005 keeps better stats on
string column and it may be able to do a seek on a covering index in a LIKE
query especially with a larger sample. It just needs to be tested heavily.
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
<pinformaticien@.yahoo.fr> wrote in message
news:8384b744-0444-4f91-a1ce-b4d302317302@.13g2000hsb.googlegroups.com...
> Hello,
> I'm experiencing a strange problem with query performance runing on
> SQL2005. The database has 10+ tables, but we need to run really
> specific queries in only 1 table with these caracteristics :
> - 1 million rows
> - we run everyday a few thousands queries on that table, each query
> is unique (adhoc plan), and not parameterizable. (we cannot optimize
> this)
> - rows have a lot of nvarchar data
> - all queries use a lot of LIKE / NOT LIKE statement (we cannot find
> any work-around to that point, Fulltext is not adequate in that case)
> - when LIKE operations are performed on columns, we always create a
> duplicate column to optimize some search stuff, like putting
> everything in Low Case, using Latin1_General_BIN collation, ...
> - we have some indexes on short nvarchar columns, only those where we
> use an exact '=' statemen
> - we have another index on a float column
> - all usefull indexes and statistics are manually created on that
> table
> - the nvarchar content of the table changes only once a day. It means
> we do all optimization (indexes / stats) just after the update, and
> there is no change on nvarchar data until the next update (24 hours
> later)
> I found that when "Auto Create Statistics" is enabled on the database,
> that queries are really runing slower :
> - "Auto Create Statistics" enabled : 57 min to run all queries
> - "Auto Create Statistics" disabled and all auto-created stats
> deleted : 7 min to run the same queries
> It means that queries are running 8x slower when "Auto Create
> Statistics" is enabled!
> Another interesting point : just after disabling "Auto Create
> Statistics", the queries continue to perform slowly until I manually
> delete all statistics created automatically for that table (the one
> begining with "_WA_Sys_"). It could mean that it's not a stat creation
> issue, but only the existence of that statistics that could change the
> query plan. But in both cases, the execution plan for the same query
> seems to be exactly the same (same aspect, same costs). I also tried
> to enable the Async stats update : no change.
> The problem is that for all the other tables in the database, the
> "Auto Create Statistics" is a good thing and useful. But not for that
> specific table. Two questions :
> - Is it possible to disable "Auto Create Statistics" on a specific
> table? (I did not find anything about that in the BOL)
> - If not, is there another work-around to deal with that kind of
> performance drop?
> Thanks.
|||For what I've tried, creating then updating statistics on nvarchar
columns with the "WITH FULLSCAN" clause doesn't help. But here are
some interesting results : I setup a test server, and ran 2 times 10
queries, first time with "Auto Create Statistics" enabled, second time
with "Auto Create Statistics" disabled. Between the 2 tests, I deleted
all the automatically created statistics (the one begining with
"_WA_Sys_"), then restarted SQL server service. Here are the results
for the following query
Select * from sys.dm_exec_query_optimizer_info where counter in
('optimizations','elapsed time')
"Auto Create Statistics" enabled
optimizations 11 1
elapsed time 11 2,80751895306448
"Auto Create Statistics" disabled
optimizations 11 1
elapsed time 11 0,0665338534973798
It confirms that all the performance drop goes in optimization time
(2.8 sec average vs 0.07 sec), that finally almost doesn't otimize
anything in my case (it leads to the same execution plan is the same
is both cases). It means I need to find a way to disable / reduce that
optimization time when "Auto Create Statistics" is enabled. Any idea?
Is it possible to disable "Auto Create Statistics" on a specific
table?
|||It sounds like you are right. It sounds like optimizer is spending more time
try to compile since there are more options only to come up with the same
plan. You can disable autostats on a particular table with UPDATE STATISTICS
... WITH NORECOMPUTE.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
<pinformaticien@.yahoo.fr> wrote in message
news:1e199977-1bc2-4614-ad72-036bb3111ce6@.d21g2000prf.googlegroups.com...
> For what I've tried, creating then updating statistics on nvarchar
> columns with the "WITH FULLSCAN" clause doesn't help. But here are
> some interesting results : I setup a test server, and ran 2 times 10
> queries, first time with "Auto Create Statistics" enabled, second time
> with "Auto Create Statistics" disabled. Between the 2 tests, I deleted
> all the automatically created statistics (the one begining with
> "_WA_Sys_"), then restarted SQL server service. Here are the results
> for the following query
> Select * from sys.dm_exec_query_optimizer_info where counter in
> ('optimizations','elapsed time')
> "Auto Create Statistics" enabled
> optimizations 11 1
> elapsed time 11 2,80751895306448
> "Auto Create Statistics" disabled
> optimizations 11 1
> elapsed time 11 0,0665338534973798
> It confirms that all the performance drop goes in optimization time
> (2.8 sec average vs 0.07 sec), that finally almost doesn't otimize
> anything in my case (it leads to the same execution plan is the same
> is both cases). It means I need to find a way to disable / reduce that
> optimization time when "Auto Create Statistics" is enabled. Any idea?
> Is it possible to disable "Auto Create Statistics" on a specific
> table?
|||Thanks for the reply. UPDATE STATISTICS ... WITH NORECOMPUTE would
just avoid statistics to be updated. In my case, it's not the stat
update which is problematic, but the existence of the automatically
created statistics (as they badly influence the query optimizer on
that table). One solution could be to move that table on a dedicated
database and turn "Auto Create Statistics" OFF, but we would like to
avoid this solution.
I'm really surprised that SQL Server doesn't allow to disable
automatic creation of statistics on a per table basis. That could be
just really helpful in some cases.
|||Ok I think I've got an interesting workaround. As we cannot disable
autocreate statistics for a specific table, the idea is to update
those unwanted stats with two clauses :
- SAMPLE 0 ROWS : to empty the statistics, so that they don't
infuence the query optimizer anymore.
- NORECOMPUTE : to avoid the "auto update stats" option to repopulate
them later
Here is the SQL statement I wrote to do this automatically on SQL 2005
(you just need to set @.dbtname correctly). It's just necessary to run
it from time to time, to ensure that new autocreated stats are
disabled.
The first tests shows exactly the same performance compared to queries
with "auto create stats" disabled.
DECLARE @.dbtname NVARCHAR(255)
SET @.dbtname = 'You_Table_Name_Here'
DECLARE c CURSOR FOR
SELECT name FROM sys.stats WHERE object_id = object_id(@.dbtname) AND
auto_created = 1
DECLARE @.statname NVARCHAR(255)
OPEN c
FETCH next FROM c INTO @.statname
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT @.statname
EXEC ('UPDATE STATISTICS ' + @.dbtname + ' (' + @.statname + ') WITH
SAMPLE 0 ROWS, NORECOMPUTE')
FETCH NEXT FROM c INTO @.statname
END
CLOSE c
DEALLOCATE c

Database Scripting Question

I just used Enterprise Manager to create a script of my database. My goal is
to take this script and run it on another server to create a copy of the
database. When I ran the script to re-create the database, I got numerous
errors because Enterprise Manager created the script in such a way that
database objects are not created in the correct order. For example, in the
script EM created, it attempts to create a stored procedure which references
a view that hasn't been created yet. Obviously, EM should have scripted the
view first and then the stored proc.
Is there anything I can do about this? Or, is there a tool I can purchase
that scripts objects in the correct order? My goal here is to script my
database and then execute the script and create a database that is a clone
of the original.
Thanks very much.
DBGhost from http://www.innovartis.co.uk/home.aspx claims to be able to
script out the db in the right order.
Andrew J. Kelly SQL MVP
"Amos J. Soma" <amos_j_soma@.yahoo.com> wrote in message
news:Hcmdnb5cILblc8bfRVn-sQ@.buckeye-express.com...
>I just used Enterprise Manager to create a script of my database. My goal
>is to take this script and run it on another server to create a copy of the
>database. When I ran the script to re-create the database, I got numerous
>errors because Enterprise Manager created the script in such a way that
>database objects are not created in the correct order. For example, in the
>script EM created, it attempts to create a stored procedure which
>references a view that hasn't been created yet. Obviously, EM should have
>scripted the view first and then the stored proc.
> Is there anything I can do about this? Or, is there a tool I can purchase
> that scripts objects in the correct order? My goal here is to script my
> database and then execute the script and create a database that is a clone
> of the original.
> Thanks very much.
>
|||It sounds like you depenency records are messed up.
Check this site out, the tools are nice will do what you need.
http://www.wingenious.com/sqltools/index.html
Hello Amos,

> I just used Enterprise Manager to create a script of my database. My
> goal is to take this script and run it on another server to create a
> copy of the database. When I ran the script to re-create the database,
> I got numerous errors because Enterprise Manager created the script in
> such a way that database objects are not created in the correct order.
> For example, in the script EM created, it attempts to create a stored
> procedure which references a view that hasn't been created yet.
> Obviously, EM should have scripted the view first and then the stored
> proc.
> Is there anything I can do about this? Or, is there a tool I can
> purchase that scripts objects in the correct order? My goal here is to
> script my database and then execute the script and create a database
> that is a clone of the original.
> Thanks very much.
>
|||Hello Andrew.
not quite - but almost. The scripter tool will script out the individual
objects and the build component will build a database (give it any name you
like) using these scripts and building it in the correct order and this all
can be recorded into a script to use which is the goal of this discussion.
"Andrew J. Kelly" wrote:

> DBGhost from http://www.innovartis.co.uk/home.aspx claims to be able to
> script out the db in the right order.
> --
> Andrew J. Kelly SQL MVP
>
> "Amos J. Soma" <amos_j_soma@.yahoo.com> wrote in message
> news:Hcmdnb5cILblc8bfRVn-sQ@.buckeye-express.com...
>
>

Database Scripting Question

I just used Enterprise Manager to create a script of my database. My goal is
to take this script and run it on another server to create a copy of the
database. When I ran the script to re-create the database, I got numerous
errors because Enterprise Manager created the script in such a way that
database objects are not created in the correct order. For example, in the
script EM created, it attempts to create a stored procedure which references
a view that hasn't been created yet. Obviously, EM should have scripted the
view first and then the stored proc.
Is there anything I can do about this? Or, is there a tool I can purchase
that scripts objects in the correct order? My goal here is to script my
database and then execute the script and create a database that is a clone
of the original.
Thanks very much.DBGhost from http://www.innovartis.co.uk/home.aspx claims to be able to
script out the db in the right order.
Andrew J. Kelly SQL MVP
"Amos J. Soma" <amos_j_soma@.yahoo.com> wrote in message
news:Hcmdnb5cILblc8bfRVn-sQ@.buckeye-express.com...
>I just used Enterprise Manager to create a script of my database. My goal
>is to take this script and run it on another server to create a copy of the
>database. When I ran the script to re-create the database, I got numerous
>errors because Enterprise Manager created the script in such a way that
>database objects are not created in the correct order. For example, in the
>script EM created, it attempts to create a stored procedure which
>references a view that hasn't been created yet. Obviously, EM should have
>scripted the view first and then the stored proc.
> Is there anything I can do about this? Or, is there a tool I can purchase
> that scripts objects in the correct order? My goal here is to script my
> database and then execute the script and create a database that is a clone
> of the original.
> Thanks very much.
>|||It sounds like you depenency records are messed up.
Check this site out, the tools are nice will do what you need.
http://www.wingenious.com/sqltools/index.html
Hello Amos,

> I just used Enterprise Manager to create a script of my database. My
> goal is to take this script and run it on another server to create a
> copy of the database. When I ran the script to re-create the database,
> I got numerous errors because Enterprise Manager created the script in
> such a way that database objects are not created in the correct order.
> For example, in the script EM created, it attempts to create a stored
> procedure which references a view that hasn't been created yet.
> Obviously, EM should have scripted the view first and then the stored
> proc.
> Is there anything I can do about this? Or, is there a tool I can
> purchase that scripts objects in the correct order? My goal here is to
> script my database and then execute the script and create a database
> that is a clone of the original.
> Thanks very much.
>|||Hello Andrew.
not quite - but almost. The scripter tool will script out the individual
objects and the build component will build a database (give it any name you
like) using these scripts and building it in the correct order and this all
can be recorded into a script to use which is the goal of this discussion.
"Andrew J. Kelly" wrote:

> DBGhost from http://www.innovartis.co.uk/home.aspx claims to be able to
> script out the db in the right order.
> --
> Andrew J. Kelly SQL MVP
>
> "Amos J. Soma" <amos_j_soma@.yahoo.com> wrote in message
> news:Hcmdnb5cILblc8bfRVn-sQ@.buckeye-express.com...
>
>