sql server tools

Tuesday, March 27, 2012

Are Blank String and String with only Space characters equal to each other? They should not be!!

I have a table called Table_1

Table_1
--
a varchar(10),
b varchar(20)

and Column a is the Primary Key.

INSERT INTO Table_1
(a ,b)
VALUES
('' , 'BLANK STRING')

INSERT INTO Table_1
(a ,b)
VALUES
(' ' ,'4 SPACES')

When i execute this Insertions, it inserts the First Record but on the Second one it gives Violation of Uniqueness

(1 row(s) affected)
Msg 2627, Level 14, State 1, Line 9
Violation of PRIMARY KEY constraint 'PK_Table_1'. Cannot insert duplicate key in object 'dbo.Table_1'.
The statement has been terminated.

My Questions is that; HOW can a Blank String or a String with 4(or any number) space charcters in it can be EQUAL? In my opinion they should not be regarded as the same! Is there a solution to this issue? That's not the case in Oracle Database.

Thanks for your help!

Regards,
Cem

According to ANSI standards (or is it SQL Server's implementation of ANSI...), anyway, for sorting purposes, leading blanks are ignored. (And Primary Keys are 'sorted'...)

Try these two statements, the first will provide the 'right' answer, and the second will provide the 'real' answer.

SELECT len( (replicate( ' ', 4 )) )
SELECT datalength( (replicate( ' ', 4 )) )

|||

You should check the setting of SET ANSI_PADDING. If you want behavior such as you find in ORACLE, you will need SET ANSI_PADDING ON. That way trailing blanks will not be trimmed from your VARCHAR insert statements.

http://msdn2.microsoft.com/en-us/library/ms187403.aspx

Dan

Are Bit Flags Bad?

Is the process of using integer data types to represent multiple values via the use of bit flags bad practice? It seems to go against the rules of normalization in a single field can represent multiple values. On the other hand that since these values can be tested for via bitwise operations, that it's not entirely bad.

Any insight would be appreciated.

ThanksI've used integers to store multiple bit states on occasion and found it very usefull.

For instance, you can have multiple boolean security states, and if you think of them as separate boolean values then separate bit fields would seem appropriate. But is you think of them as a single value describing the overall state of the security setting, then you aren't really violating any principles by storing them as a single field.

At a practical level, SQL Server will combine up to eight bit fields and store them internally as a single byte anyway.

Normalization is good practice because it leads to efficient and functional design. But if you avoid an instance where violating it would actually improve an application, then you are letting the cart lead the horse. Normalization is principle, not dogma.|||there is a multitude of examples where M$ itself used...smallint...as a datatype for this type of scenarios. i think int would be an overkill, while following the trend would save you storage space while doing the job.

are backups not successful?

Hello:
A client of mine is using SQL Server 2000 and has constructed a database
maintenance job to do "Complete Backup" and "Transaction Log Backup".
In another thread, I had asked why the job was not deleting its own backups
files as the job is configured to delete backups after one day.
Someone had replied and said that the deletions will not take effect until
the backup job completes successfully.
Since the backup files are not deleting, does that mean that the backup job
is not being successful?
If that's the case, then, I don't know what the client is doing wrong. It's
not hard to set up a backup job in the database maintenance plan.
I mean, does the SQL Server Service need to be stopped in order for these
backups to take place successfully?
Thanks!
childofthe1980s
Hi,
Before i suggest u any thing just check the maintinance plan log file.
It is not true that backup will not delete till the next backup is
successful.
Check maintinance plan again and make sure that u had selected one
day(delete old files)
check the path on the backup.
hope this helps
from
Killer

are backups not successful?

Hello:
A client of mine is using SQL Server 2000 and has constructed a database
maintenance job to do "Complete Backup" and "Transaction Log Backup".
In another thread, I had asked why the job was not deleting its own backups
files as the job is configured to delete backups after one day.
Someone had replied and said that the deletions will not take effect until
the backup job completes successfully.
Since the backup files are not deleting, does that mean that the backup job
is not being successful?
If that's the case, then, I don't know what the client is doing wrong. It's
not hard to set up a backup job in the database maintenance plan.
I mean, does the SQL Server Service need to be stopped in order for these
backups to take place successfully?
Thanks!
childofthe1980sHi,
Before i suggest u any thing just check the maintinance plan log file.
It is not true that backup will not delete till the next backup is
successful.
Check maintinance plan again and make sure that u had selected one
day(delete old files)
check the path on the backup.
hope this helps
from
Killersql

are backups not successful?

Hello:
A client of mine is using SQL Server 2000 and has constructed a database
maintenance job to do "Complete Backup" and "Transaction Log Backup".
In another thread, I had asked why the job was not deleting its own backups
files as the job is configured to delete backups after one day.
Someone had replied and said that the deletions will not take effect until
the backup job completes successfully.
Since the backup files are not deleting, does that mean that the backup job
is not being successful?
If that's the case, then, I don't know what the client is doing wrong. It's
not hard to set up a backup job in the database maintenance plan.
I mean, does the SQL Server Service need to be stopped in order for these
backups to take place successfully?
Thanks!
childofthe1980sHi,
Before i suggest u any thing just check the maintinance plan log file.
--
It is not true that backup will not delete till the next backup is
successful.
Check maintinance plan again and make sure that u had selected one
day(delete old files)
check the path on the backup.
hope this helps
from
Killer

are all SQL 2000 Server Wizard included in SQL 2000 Server Standard Edition

Hi,
right now we use the SQL 2000 Server Developer Edition to
make tests, but we would like to buy SQL 2000 Server. Does
the SQL 2000 Server STandard Edition include all the
WIZARDS that are also included with the Developer or
Enterprise Edition (when I highlight a database in
Enterprise Manager, go to "Tools" and "Wizards").
Can anyone help?
Thanks for reply.
BodoAll the wizards are included in Standard Edition as well. The differences
between Standard and Enterprise Edition are mostly in the high-end
performance and availabilty areas, like clustered servers, indexed views
etc.
For an overview see the topic "Features Supported by the Editions of SQL
Server 2000" in Books Online.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"bodo" <bodobecker@.hotmail.com> wrote in message
news:0b6501c36d6b$b90a15c0$a601280a@.phx.gbl...
> Hi,
> right now we use the SQL 2000 Server Developer Edition to
> make tests, but we would like to buy SQL 2000 Server. Does
> the SQL 2000 Server STandard Edition include all the
> WIZARDS that are also included with the Developer or
> Enterprise Edition (when I highlight a database in
> Enterprise Manager, go to "Tools" and "Wizards").
> Can anyone help?
> Thanks for reply.
> Bodo|||Hello Bodo !
Differences could be seen here:
http://www.microsoft.com/sql/evaluation/features/choosing.asp
HTH, Jens Süßmeyer.|||Hi Jacco,
thanks for your help!
Brgds Bodo
>--Original Message--
>All the wizards are included in Standard Edition as well.
The differences
>between Standard and Enterprise Edition are mostly in the
high-end
>performance and availabilty areas, like clustered
servers, indexed views
>etc.
>For an overview see the topic "Features Supported by the
Editions of SQL
>Server 2000" in Books Online.
>--
>Jacco Schalkwijk MCDBA, MCSD, MCSE
>Database Administrator
>Eurostop Ltd.
>
>"bodo" <bodobecker@.hotmail.com> wrote in message
>news:0b6501c36d6b$b90a15c0$a601280a@.phx.gbl...
>> Hi,
>> right now we use the SQL 2000 Server Developer Edition
to
>> make tests, but we would like to buy SQL 2000 Server.
Does
>> the SQL 2000 Server STandard Edition include all the
>> WIZARDS that are also included with the Developer or
>> Enterprise Edition (when I highlight a database in
>> Enterprise Manager, go to "Tools" and "Wizards").
>> Can anyone help?
>> Thanks for reply.
>> Bodo
>
>.
>

Are all indexes created on subscriber ?

Using trans replication and I believe by default all the clustered and non
clustered indexes are copied to the subscriber .. Can someone confirm ?
Hassan,
the settings on the snapshot properties tab of the article properties
determines which indexes are copied over. If any are created afterward
initialization, they'll not be replicated, so sp_addscriptexec can be used
for these.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Are all DSN's really ODBC?

I'm still trying to learn the alphabet soup of MS's data access methods, but
one thing I just noticed is that the Data Sources control panel is called
"Data Sources (ODBC)". Is this accurate? If so, should I *not* use DSN's in
my code? I am under the impression that ODBC is considerably slower than
OLEDB or other technologies (although what the differences are I don't really
know).
For instance, I have my code literally sprinkled with little ADO lookups
that use a single connection string...
"ODBC;DATABASE=OurDB;DSN=SQL Server"
Removing the "ODBC" does nothing, which I assume is because it's not a
key/value pair. But it still uses a DSN to look up the connection, so does
this mean every query I run using this connection string uses ODBC?
If so, should I really expect any sort of real-world performance boost if I
use a new string, one like...
"Provider=sqloledb;DATABASE=OurDB;DSN=SQL Server"
Is there an even better provider to use?
It's all so confusing!!
Maury
Why use DSN's at all? Go DSN-less. You just need to tack a few things onto
the connection string.
Head to www.connectionstrings.com for more info.
Keith
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:3486DEAD-166D-43C5-A2C7-7B37F66EC392@.microsoft.com...
> I'm still trying to learn the alphabet soup of MS's data access methods,
> but
> one thing I just noticed is that the Data Sources control panel is called
> "Data Sources (ODBC)". Is this accurate? If so, should I *not* use DSN's
> in
> my code? I am under the impression that ODBC is considerably slower than
> OLEDB or other technologies (although what the differences are I don't
> really
> know).
> For instance, I have my code literally sprinkled with little ADO lookups
> that use a single connection string...
> "ODBC;DATABASE=OurDB;DSN=SQL Server"
> Removing the "ODBC" does nothing, which I assume is because it's not a
> key/value pair. But it still uses a DSN to look up the connection, so does
> this mean every query I run using this connection string uses ODBC?
> If so, should I really expect any sort of real-world performance boost if
> I
> use a new string, one like...
> "Provider=sqloledb;DATABASE=OurDB;DSN=SQL Server"
> Is there an even better provider to use?
> It's all so confusing!!
> Maury
|||"Keith Kratochvil" wrote:

> Why use DSN's at all?
That's what I'm asking.
Maury
|||yes.
Choose an OLEDB Provider and do not use a DSN.
OLEDB is going to be faster particularly when you choose the native provider
for your target platform (Example..."SQL Server")
Greg Jackson
PDX, Oregon
|||In addition tot he other comments:
ODBC is a programming API. A low-level API. There exists higher level abstractions such as the RDO
object model on top of ODBC. ODBC uses a driver to connect to the DBMS.
OLEDB is a programming API. A low-level API. There exists higher level abstractions such as the ADO
object model on top of OLEDB. OLEDB uses a driver to connect to the DBMS. There exists an OLEDB
driver which sits on top of ODBC. This is probably what you are seeing.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in message
news:3486DEAD-166D-43C5-A2C7-7B37F66EC392@.microsoft.com...
> I'm still trying to learn the alphabet soup of MS's data access methods, but
> one thing I just noticed is that the Data Sources control panel is called
> "Data Sources (ODBC)". Is this accurate? If so, should I *not* use DSN's in
> my code? I am under the impression that ODBC is considerably slower than
> OLEDB or other technologies (although what the differences are I don't really
> know).
> For instance, I have my code literally sprinkled with little ADO lookups
> that use a single connection string...
> "ODBC;DATABASE=OurDB;DSN=SQL Server"
> Removing the "ODBC" does nothing, which I assume is because it's not a
> key/value pair. But it still uses a DSN to look up the connection, so does
> this mean every query I run using this connection string uses ODBC?
> If so, should I really expect any sort of real-world performance boost if I
> use a new string, one like...
> "Provider=sqloledb;DATABASE=OurDB;DSN=SQL Server"
> Is there an even better provider to use?
> It's all so confusing!!
> Maury

Are all DSN's really ODBC?

I'm still trying to learn the alphabet soup of MS's data access methods, but
one thing I just noticed is that the Data Sources control panel is called
"Data Sources (ODBC)". Is this accurate? If so, should I *not* use DSN's in
my code? I am under the impression that ODBC is considerably slower than
OLEDB or other technologies (although what the differences are I don't reall
y
know).
For instance, I have my code literally sprinkled with little ADO lookups
that use a single connection string...
"ODBC;DATABASE=OurDB;DSN=SQL Server"
Removing the "ODBC" does nothing, which I assume is because it's not a
key/value pair. But it still uses a DSN to look up the connection, so does
this mean every query I run using this connection string uses ODBC?
If so, should I really expect any sort of real-world performance boost if I
use a new string, one like...
" Provider=sqloledb;DATABASE=OurDB;DSN=SQL
Server"
Is there an even better provider to use?
It's all so confusing!!
MauryWhy use DSN's at all? Go DSN-less. You just need to tack a few things onto
the connection string.
Head to www.connectionstrings.com for more info.
Keith
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:3486DEAD-166D-43C5-A2C7-7B37F66EC392@.microsoft.com...
> I'm still trying to learn the alphabet soup of MS's data access methods,
> but
> one thing I just noticed is that the Data Sources control panel is called
> "Data Sources (ODBC)". Is this accurate? If so, should I *not* use DSN's
> in
> my code? I am under the impression that ODBC is considerably slower than
> OLEDB or other technologies (although what the differences are I don't
> really
> know).
> For instance, I have my code literally sprinkled with little ADO lookups
> that use a single connection string...
> "ODBC;DATABASE=OurDB;DSN=SQL Server"
> Removing the "ODBC" does nothing, which I assume is because it's not a
> key/value pair. But it still uses a DSN to look up the connection, so does
> this mean every query I run using this connection string uses ODBC?
> If so, should I really expect any sort of real-world performance boost if
> I
> use a new string, one like...
> " Provider=sqloledb;DATABASE=OurDB;DSN=SQL
Server"
> Is there an even better provider to use?
> It's all so confusing!!
> Maury|||"Keith Kratochvil" wrote:

> Why use DSN's at all?
That's what I'm asking.
Maury|||yes.
Choose an OLEDB Provider and do not use a DSN.
OLEDB is going to be faster particularly when you choose the native provider
for your target platform (Example..."SQL Server")
Greg Jackson
PDX, Oregon|||In addition tot he other comments:
ODBC is a programming API. A low-level API. There exists higher level abstra
ctions such as the RDO
object model on top of ODBC. ODBC uses a driver to connect to the DBMS.
OLEDB is a programming API. A low-level API. There exists higher level abstr
actions such as the ADO
object model on top of OLEDB. OLEDB uses a driver to connect to the DBMS. Th
ere exists an OLEDB
driver which sits on top of ODBC. This is probably what you are seeing.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in messag
e
news:3486DEAD-166D-43C5-A2C7-7B37F66EC392@.microsoft.com...
> I'm still trying to learn the alphabet soup of MS's data access methods, b
ut
> one thing I just noticed is that the Data Sources control panel is called
> "Data Sources (ODBC)". Is this accurate? If so, should I *not* use DSN's i
n
> my code? I am under the impression that ODBC is considerably slower than
> OLEDB or other technologies (although what the differences are I don't rea
lly
> know).
> For instance, I have my code literally sprinkled with little ADO lookups
> that use a single connection string...
> "ODBC;DATABASE=OurDB;DSN=SQL Server"
> Removing the "ODBC" does nothing, which I assume is because it's not a
> key/value pair. But it still uses a DSN to look up the connection, so does
> this mean every query I run using this connection string uses ODBC?
> If so, should I really expect any sort of real-world performance boost if
I
> use a new string, one like...
> " Provider=sqloledb;DATABASE=OurDB;DSN=SQL
Server"
> Is there an even better provider to use?
> It's all so confusing!!
> Maurysql

Are all DSN's really ODBC?

I'm still trying to learn the alphabet soup of MS's data access methods, but
one thing I just noticed is that the Data Sources control panel is called
"Data Sources (ODBC)". Is this accurate? If so, should I *not* use DSN's in
my code? I am under the impression that ODBC is considerably slower than
OLEDB or other technologies (although what the differences are I don't really
know).
For instance, I have my code literally sprinkled with little ADO lookups
that use a single connection string...
"ODBC;DATABASE=OurDB;DSN=SQL Server"
Removing the "ODBC" does nothing, which I assume is because it's not a
key/value pair. But it still uses a DSN to look up the connection, so does
this mean every query I run using this connection string uses ODBC?
If so, should I really expect any sort of real-world performance boost if I
use a new string, one like...
"Provider=sqloledb;DATABASE=OurDB;DSN=SQL Server"
Is there an even better provider to use?
It's all so confusing!!
MauryWhy use DSN's at all? Go DSN-less. You just need to tack a few things onto
the connection string.
Head to www.connectionstrings.com for more info.
--
Keith
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:3486DEAD-166D-43C5-A2C7-7B37F66EC392@.microsoft.com...
> I'm still trying to learn the alphabet soup of MS's data access methods,
> but
> one thing I just noticed is that the Data Sources control panel is called
> "Data Sources (ODBC)". Is this accurate? If so, should I *not* use DSN's
> in
> my code? I am under the impression that ODBC is considerably slower than
> OLEDB or other technologies (although what the differences are I don't
> really
> know).
> For instance, I have my code literally sprinkled with little ADO lookups
> that use a single connection string...
> "ODBC;DATABASE=OurDB;DSN=SQL Server"
> Removing the "ODBC" does nothing, which I assume is because it's not a
> key/value pair. But it still uses a DSN to look up the connection, so does
> this mean every query I run using this connection string uses ODBC?
> If so, should I really expect any sort of real-world performance boost if
> I
> use a new string, one like...
> "Provider=sqloledb;DATABASE=OurDB;DSN=SQL Server"
> Is there an even better provider to use?
> It's all so confusing!!
> Maury|||"Keith Kratochvil" wrote:
> Why use DSN's at all?
That's what I'm asking.
Maury|||yes.
Choose an OLEDB Provider and do not use a DSN.
OLEDB is going to be faster particularly when you choose the native provider
for your target platform (Example..."SQL Server")
Greg Jackson
PDX, Oregon|||In addition tot he other comments:
ODBC is a programming API. A low-level API. There exists higher level abstractions such as the RDO
object model on top of ODBC. ODBC uses a driver to connect to the DBMS.
OLEDB is a programming API. A low-level API. There exists higher level abstractions such as the ADO
object model on top of OLEDB. OLEDB uses a driver to connect to the DBMS. There exists an OLEDB
driver which sits on top of ODBC. This is probably what you are seeing.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in message
news:3486DEAD-166D-43C5-A2C7-7B37F66EC392@.microsoft.com...
> I'm still trying to learn the alphabet soup of MS's data access methods, but
> one thing I just noticed is that the Data Sources control panel is called
> "Data Sources (ODBC)". Is this accurate? If so, should I *not* use DSN's in
> my code? I am under the impression that ODBC is considerably slower than
> OLEDB or other technologies (although what the differences are I don't really
> know).
> For instance, I have my code literally sprinkled with little ADO lookups
> that use a single connection string...
> "ODBC;DATABASE=OurDB;DSN=SQL Server"
> Removing the "ODBC" does nothing, which I assume is because it's not a
> key/value pair. But it still uses a DSN to look up the connection, so does
> this mean every query I run using this connection string uses ODBC?
> If so, should I really expect any sort of real-world performance boost if I
> use a new string, one like...
> "Provider=sqloledb;DATABASE=OurDB;DSN=SQL Server"
> Is there an even better provider to use?
> It's all so confusing!!
> Maury

are after triggers really after?

The other day when I was preparing for Microsoft exam 70-229 and I was reading up on triggers and I noticed that the documentation states that the default behavoir of triggers is to fire after the update\insert\delete transaction on the table that the trigger is created on. However, I have noticed that this is not totally accurate. If you setup a trace in profiler with the with all of the xxxstarted and xxxcompleted events for the T-SQL and stored procedure event classes you will notice that the originating transaction starts, then the trigger starts, then the trigger completes and then the originating transaction completes.

So after thinking about this for a while, I have to come to the conclusion that sql server must consider the trigger as part of the orignating transaction, and must therefore complete the trigger before the originating transaction commits. With this in mind, should'nt the default behavoir of triggers be considered "during" and not "after".I believe they fire after the update\insert\delete, but before the commit. They certainly occur during the transaction, because an error in a poorly written trigger can roll back the whole thing.|||I believe they fire after the update\insert\delete, but before the commit. They certainly occur during the transaction, because an error in a poorly written trigger can roll back the whole thing.

I think both statements are accurate. What differentiates an 'after' trigger from a before (or INSTEAD OF) trigger is that it (the INSTEAD OF trigger) executes before any transaction is opened on the underlying table/view. Perhaps a blinding statement of the obvious, but as often the case in semantics, the BEFORE trigger needed an 'opposite' and that opposite became 'after'.

Regards,

hmscott

Are _WA_Sys_ statistics indexes an indicator of what should be indexed?

I know these are just statistics placeholders, but I am wondering if
they might be a good hint into what fields should be indexed. Any
comments?
Example:
_WA_Sys_DisplayName_0B27A5C0 - Should I index DisplayName?
_WA_Sys_CreatedDate_0B27A5C0 - Should I index CreatedDate?
_WA_Sys_CreatedBy_0B27A5C0 - Should I index CreatedBy?Hi Joshua,
Auto generated statistics on a column are indeed a good indicator that the
column might have to be indexed. Keep in mind though that statistics are
created the first time the column is accessed as part of a where clause,
join, group by, order by etc, and that that may also be the last time. In
other words, check that the column is indeed used more or less regularly
before you start creating indexes everywhere.
Jacco Schalkwijk
SQL Server MVP
"Joshua Guttman" <joshuaguttman@.excite.com> wrote in message
news:5a7a8917.0401291329.57833b2@.posting.google.com...

quote:

> I know these are just statistics placeholders, but I am wondering if
> they might be a good hint into what fields should be indexed. Any
> comments?
> Example:
> _WA_Sys_DisplayName_0B27A5C0 - Should I index DisplayName?
> _WA_Sys_CreatedDate_0B27A5C0 - Should I index CreatedDate?
> _WA_Sys_CreatedBy_0B27A5C0 - Should I index CreatedBy?

Are _WA_Sys_ statistics indexes an indicator of what should be indexed?

I know these are just statistics placeholders, but I am wondering if
they might be a good hint into what fields should be indexed. Any
comments?
Example:
_WA_Sys_DisplayName_0B27A5C0 - Should I index DisplayName?
_WA_Sys_CreatedDate_0B27A5C0 - Should I index CreatedDate?
_WA_Sys_CreatedBy_0B27A5C0 - Should I index CreatedBy?Hi Joshua,
Auto generated statistics on a column are indeed a good indicator that the
column might have to be indexed. Keep in mind though that statistics are
created the first time the column is accessed as part of a where clause,
join, group by, order by etc, and that that may also be the last time. In
other words, check that the column is indeed used more or less regularly
before you start creating indexes everywhere.
--
Jacco Schalkwijk
SQL Server MVP
"Joshua Guttman" <joshuaguttman@.excite.com> wrote in message
news:5a7a8917.0401291329.57833b2@.posting.google.com...
> I know these are just statistics placeholders, but I am wondering if
> they might be a good hint into what fields should be indexed. Any
> comments?
> Example:
> _WA_Sys_DisplayName_0B27A5C0 - Should I index DisplayName?
> _WA_Sys_CreatedDate_0B27A5C0 - Should I index CreatedDate?
> _WA_Sys_CreatedBy_0B27A5C0 - Should I index CreatedBy?

ArcServ and SQl Server 2000

Hi,

I am trying to create a backup agent for a SQl Server. I have given the information for the following: instance, AUTHENTICATION, USERNAME, PASSWORD, CONFIRM PASSWORD.

I keep getting the error message, enter valid instance, even though i am entering in the instance.

any ideas?

Is this a named instance? Do you have any special characters like dashes in the server name?

-Sue

|||Problem solved ? If yes, post how you solved it, or if the hint from Sue helped you′.

Jens K. Suessmeyer

http://www.sqlserver2005.de

sql

Archiving very slow, help needed please

Hey guys,
The vendor gave us an archiving tool for our huge tables. We gave them a
production copy of our tables. They tested their tool against their own
server and told us that it takes 2 seconds to insert each record. I know
this is already bad. But today, I tested it against our own test server.
They also gave us instruction on what to do first before running the
archiving tool. Well anyways, after running the archiving tool, it took 20
seconds to insert 1 record. That's totally bad!
I would like to know if you will be able to help me guys identify the
problem by just looking at this links.
http://restricted.dyndns.org/executionplan.txt
http://restricted.dyndns.org/execplan1.gif
http://restricted.dyndns.org/execplan2.gif
http://restricted.dyndns.org/execplan3.gif
The INSERT statement that you will see there consumed 10 seconds of CPU and
30 seconds of Duration.
Are there any other statements that I can execute against my captured
profiler table that can us troubleshoot?
Any help will be greatly appreciated.
Thanks.
V1rt>> told us that it takes 2 seconds to insert each record
well....
Go back to the vendor (I assume you haven't paid them yet) and tell them
this isn't acceptable.
How many records do you have to deal with - even at 2 secs per record?
"Neil" wrote:
> Hey guys,
> The vendor gave us an archiving tool for our huge tables. We gave them a
> production copy of our tables. They tested their tool against their own
> server and told us that it takes 2 seconds to insert each record. I know
> this is already bad. But today, I tested it against our own test server.
> They also gave us instruction on what to do first before running the
> archiving tool. Well anyways, after running the archiving tool, it took 20
> seconds to insert 1 record. That's totally bad!
> I would like to know if you will be able to help me guys identify the
> problem by just looking at this links.
> http://restricted.dyndns.org/executionplan.txt
> http://restricted.dyndns.org/execplan1.gif
> http://restricted.dyndns.org/execplan2.gif
> http://restricted.dyndns.org/execplan3.gif
> The INSERT statement that you will see there consumed 10 seconds of CPU and
> 30 seconds of Duration.
> Are there any other statements that I can execute against my captured
> profiler table that can us troubleshoot?
> Any help will be greatly appreciated.
> Thanks.
> V1rt
>
>|||It looks like you are filtering the source table, Enclosure. Nothing wrong
with that. However, then you are joining this to the destination table.
Why?
An archive table usually doesn't contain the data in it yet. Are you
joining to make sure that you don't attempt to archive data that's already
been copied?
If so, there are better ways to write this. If you are INSERTing into a
table, not an update, there is rarely a need to join the destination to the
source. This will only slow you down because an INSERT will usually involve
updating the joined columns.
Try something more like this:
BEGIN TRANSACTION
INSERT INTO TableDestination
(Column1, Column2, ..., ColumnN)
SELECT Column1, Column2, ..., ColumnN
FROM TableSource
WHERE TableSource.ColumnX = ExpressionX
IF @.@.ERROR <> 0 BEGIN
ROLLBACK TRANSACTION
RETURN
END
DELETE FROM s
FROM TableDestination AS d
INNER JOIN TableSource AS s
ON d.Key1 = s.Key1
AND d.Key2 = s.Key2
AND...
AND d.KeyN = s.KeyN
IF @.@.ERROR <> 0 BEGIN
ROLLBACK TRANSACTION
RETURN
END
COMMIT TRANSACTION
Sincerely,
Anthony Thomas
"Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
news:B085D060-8557-4C9E-B632-0C320BE1CE98@.microsoft.com...
> >> told us that it takes 2 seconds to insert each record
> well....
> Go back to the vendor (I assume you haven't paid them yet) and tell them
> this isn't acceptable.
> How many records do you have to deal with - even at 2 secs per record?
>
> "Neil" wrote:
> > Hey guys,
> >
> > The vendor gave us an archiving tool for our huge tables. We gave them a
> > production copy of our tables. They tested their tool against their own
> > server and told us that it takes 2 seconds to insert each record. I know
> > this is already bad. But today, I tested it against our own test server.
> > They also gave us instruction on what to do first before running the
> > archiving tool. Well anyways, after running the archiving tool, it took
20
> > seconds to insert 1 record. That's totally bad!
> >
> > I would like to know if you will be able to help me guys identify the
> > problem by just looking at this links.
> >
> > http://restricted.dyndns.org/executionplan.txt
> > http://restricted.dyndns.org/execplan1.gif
> > http://restricted.dyndns.org/execplan2.gif
> > http://restricted.dyndns.org/execplan3.gif
> >
> > The INSERT statement that you will see there consumed 10 seconds of CPU
and
> > 30 seconds of Duration.
> >
> > Are there any other statements that I can execute against my captured
> > profiler table that can us troubleshoot?
> >
> > Any help will be greatly appreciated.
> >
> > Thanks.
> >
> > V1rt
> >
> >
> >|||Hi Anthony,
Thanks for the awesome reply. Am I correct that the destination table that
is being joint is the tablefieldaudit? That's what I saw in the INSERT
statement.
Below is what I captured using Profiler. I saw tons of this running for 20+
seconds each. :(
INSERT INTO
TABLEFIELDAUDIT(TABLENAME,FIELDNAME,FIELDVALUE,CHANGEDATE,KEYVALUE,USERID,SUBKEY1,
SUBKEY2)
SELECT TABLENAME,FIELDNAME,FIELDVALUE,CHANGEDATE,E.RECORDID AS
KEYVALUE,USERID,SUBKEY1, SUBKEY2
FROM TABLEFIELDAUDIT
INNER JOIN ENCLOSURE E
ON TABLEFIELDAUDIT.SUBKEY1=E.BARCODE AND
TABLEFIELDAUDIT.SUBKEY2=E.ENCLOSURENUMBER
WHERE TABLENAME='ENCLOSURE' AND SUBKEY1='00010690'
Thanks again,
Neil
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
news:OLmPhvtyEHA.2568@.TK2MSFTNGP11.phx.gbl...
> It looks like you are filtering the source table, Enclosure. Nothing
> wrong
> with that. However, then you are joining this to the destination table.
> Why?
> An archive table usually doesn't contain the data in it yet. Are you
> joining to make sure that you don't attempt to archive data that's already
> been copied?
> If so, there are better ways to write this. If you are INSERTing into a
> table, not an update, there is rarely a need to join the destination to
> the
> source. This will only slow you down because an INSERT will usually
> involve
> updating the joined columns.
> Try something more like this:
> BEGIN TRANSACTION
> INSERT INTO TableDestination
> (Column1, Column2, ..., ColumnN)
> SELECT Column1, Column2, ..., ColumnN
> FROM TableSource
> WHERE TableSource.ColumnX = ExpressionX
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK TRANSACTION
> RETURN
> END
> DELETE FROM s
> FROM TableDestination AS d
> INNER JOIN TableSource AS s
> ON d.Key1 = s.Key1
> AND d.Key2 = s.Key2
> AND...
> AND d.KeyN = s.KeyN
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK TRANSACTION
> RETURN
> END
> COMMIT TRANSACTION
> Sincerely,
>
> Anthony Thomas
>
> --
> "Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
> news:B085D060-8557-4C9E-B632-0C320BE1CE98@.microsoft.com...
>> >> told us that it takes 2 seconds to insert each record
>> well....
>> Go back to the vendor (I assume you haven't paid them yet) and tell them
>> this isn't acceptable.
>> How many records do you have to deal with - even at 2 secs per record?
>>
>> "Neil" wrote:
>> > Hey guys,
>> >
>> > The vendor gave us an archiving tool for our huge tables. We gave them
>> > a
>> > production copy of our tables. They tested their tool against their own
>> > server and told us that it takes 2 seconds to insert each record. I
>> > know
>> > this is already bad. But today, I tested it against our own test
>> > server.
>> > They also gave us instruction on what to do first before running the
>> > archiving tool. Well anyways, after running the archiving tool, it took
> 20
>> > seconds to insert 1 record. That's totally bad!
>> >
>> > I would like to know if you will be able to help me guys identify the
>> > problem by just looking at this links.
>> >
>> > http://restricted.dyndns.org/executionplan.txt
>> > http://restricted.dyndns.org/execplan1.gif
>> > http://restricted.dyndns.org/execplan2.gif
>> > http://restricted.dyndns.org/execplan3.gif
>> >
>> > The INSERT statement that you will see there consumed 10 seconds of CPU
> and
>> > 30 seconds of Duration.
>> >
>> > Are there any other statements that I can execute against my captured
>> > profiler table that can us troubleshoot?
>> >
>> > Any help will be greatly appreciated.
>> >
>> > Thanks.
>> >
>> > V1rt
>> >
>> >
>> >
>

Archiving very slow, help needed please

INSERT INTO
TABLEFIELDAUDIT(TABLENAME,FIELDNAME,FIELDVALUE,CHA NGEDATE,KEYVALUE,USERID,SUBKEY1,
SUBKEY2)
SELECT TABLENAME,FIELDNAME,FIELDVALUE,CHANGEDATE,E.RECORD ID AS
KEYVALUE,USERID,SUBKEY1, SUBKEY2
FROM TABLEFIELDAUDIT
INNER JOIN ENCLOSURE E
ON TABLEFIELDAUDIT.SUBKEY1=E.BARCODE AND
TABLEFIELDAUDIT.SUBKEY2=E.ENCLOSURENUMBER
WHERE TABLENAME='ENCLOSURE' AND SUBKEY1='00010690'
Thanks again,
Neil
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
news:OLmPhvtyEHA.2568@.TK2MSFTNGP11.phx.gbl...
> It looks like you are filtering the source table, Enclosure. Nothing
> wrong
> with that. However, then you are joining this to the destination table.
> Why?
> An archive table usually doesn't contain the data in it yet. Are you
> joining to make sure that you don't attempt to archive data that's already
> been copied?
> If so, there are better ways to write this. If you are INSERTing into a
> table, not an update, there is rarely a need to join the destination to
> the
> source. This will only slow you down because an INSERT will usually
> involve
> updating the joined columns.
> Try something more like this:
> BEGIN TRANSACTION
> INSERT INTO TableDestination
> (Column1, Column2, ..., ColumnN)
> SELECT Column1, Column2, ..., ColumnN
> FROM TableSource
> WHERE TableSource.ColumnX = ExpressionX
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK TRANSACTION
> RETURN
> END
> DELETE FROM s
> FROM TableDestination AS d
> INNER JOIN TableSource AS s
> ON d.Key1 = s.Key1
> AND d.Key2 = s.Key2
> AND...
> AND d.KeyN = s.KeyN
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK TRANSACTION
> RETURN
> END
> COMMIT TRANSACTION
> Sincerely,
>
> Anthony Thomas
>
> --
> "Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
> news:B085D060-8557-4C9E-B632-0C320BE1CE98@.microsoft.com...
> 20
> and
>

Archiving very slow, help needed please

INSERT INTO
TABLEFIELDAUDIT(TABLENAME,FIELDNAME,FIEL
DVALUE,CHANGEDATE,KEYVALUE,USERID,SU
BKEY1,
SUBKEY2)
SELECT TABLENAME,FIELDNAME,FIELDVALUE,CHANGEDAT
E,E.RECORDID AS
KEYVALUE,USERID,SUBKEY1, SUBKEY2
FROM TABLEFIELDAUDIT
INNER JOIN ENCLOSURE E
ON TABLEFIELDAUDIT.SUBKEY1=E.BARCODE AND
TABLEFIELDAUDIT.SUBKEY2=E.ENCLOSURENUMBER
WHERE TABLENAME='ENCLOSURE' AND SUBKEY1='00010690'
Thanks again,
Neil
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
news:OLmPhvtyEHA.2568@.TK2MSFTNGP11.phx.gbl...
> It looks like you are filtering the source table, Enclosure. Nothing
> wrong
> with that. However, then you are joining this to the destination table.
> Why?
> An archive table usually doesn't contain the data in it yet. Are you
> joining to make sure that you don't attempt to archive data that's already
> been copied?
> If so, there are better ways to write this. If you are INSERTing into a
> table, not an update, there is rarely a need to join the destination to
> the
> source. This will only slow you down because an INSERT will usually
> involve
> updating the joined columns.
> Try something more like this:
> BEGIN TRANSACTION
> INSERT INTO TableDestination
> (Column1, Column2, ..., ColumnN)
> SELECT Column1, Column2, ..., ColumnN
> FROM TableSource
> WHERE TableSource.ColumnX = ExpressionX
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK TRANSACTION
> RETURN
> END
> DELETE FROM s
> FROM TableDestination AS d
> INNER JOIN TableSource AS s
> ON d.Key1 = s.Key1
> AND d.Key2 = s.Key2
> AND...
> AND d.KeyN = s.KeyN
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK TRANSACTION
> RETURN
> END
> COMMIT TRANSACTION
> Sincerely,
>
> Anthony Thomas
>
> --
> "Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
> news:B085D060-8557-4C9E-B632-0C320BE1CE98@.microsoft.com...
> 20
> and
>

archiving tables

We have some fast growing tables in our SQL Server 2000 db
which content 13 million records already. So, we going to
periodically archive them base on datetime field and allow
user to retrieve archive data via our application by
checking corresponding checkbox. The problem is that, we
do not know what to do with FK's. How to re-create FK's
after archiving to restore referal integrity.
Is there some standard solution?
Any comments will be appreciated.
Merry chistmas and happy new year to everybody.Vitalik
http://vyaskn.tripod.com/sql_archive_data.htm
"vitaliyk" <vitaliykrasner@.hotmail.com> wrote in message
news:08ee01c3c964$5faa1340$a401280a@.phx.gbl...
> We have some fast growing tables in our SQL Server 2000 db
> which content 13 million records already. So, we going to
> periodically archive them base on datetime field and allow
> user to retrieve archive data via our application by
> checking corresponding checkbox. The problem is that, we
> do not know what to do with FK's. How to re-create FK's
> after archiving to restore referal integrity.
> Is there some standard solution?
> Any comments will be appreciated.
> Merry chistmas and happy new year to everybody.

Archiving Problem

Hi!
The SQL Server Express run several versions of an Archive file, such as
Archive #1, etc. under 'SQL Server Logs'.
What is generating these archives? Also, How can I see what has been
archived? Can I stop the server from doing this or needed for to operate?
Thank you very much!
-BahmanHI
"Bahman" wrote:
> Hi!
> The SQL Server Express run several versions of an Archive file, such as
> Archive #1, etc. under 'SQL Server Logs'.
> What is generating these archives? Also, How can I see what has been
> archived? Can I stop the server from doing this or needed for to operate?
> Thank you very much!
> -Bahman
>
These are old versions of the ERRORLOG file. A new ERRORLOG is created when
you restart SQL Server or run sp_cycle_errorlog. The file will be ERRORLOG.1
ERRORLOG.2 etc.. in the LOGS directory. The Displayed name in SSMS and EM
will have the date and time that the log goes up to. More information is in
Books online.
John|||John,
Thanks a million. I guess the word archive was throwing me off. I was
looking in the wrong place. Somehow I thought it is archiving, so that I can
restore from it. But no info on that anywhere. Thank you very much for your
help.
-Bahman
"John Bell" wrote:
> HI
> "Bahman" wrote:
> > Hi!
> >
> > The SQL Server Express run several versions of an Archive file, such as
> > Archive #1, etc. under 'SQL Server Logs'.
> >
> > What is generating these archives? Also, How can I see what has been
> > archived? Can I stop the server from doing this or needed for to operate?
> >
> > Thank you very much!
> >
> > -Bahman
> >
> These are old versions of the ERRORLOG file. A new ERRORLOG is created when
> you restart SQL Server or run sp_cycle_errorlog. The file will be ERRORLOG.1
> ERRORLOG.2 etc.. in the LOGS directory. The Displayed name in SSMS and EM
> will have the date and time that the log goes up to. More information is in
> Books online.
> John
>sql

Archiving Problem

Hi!
The SQL Server Express run several versions of an Archive file, such as
Archive #1, etc. under 'SQL Server Logs'.
What is generating these archives? Also, How can I see what has been
archived? Can I stop the server from doing this or needed for to operate?
Thank you very much!
-Bahman
HI
"Bahman" wrote:

> Hi!
> The SQL Server Express run several versions of an Archive file, such as
> Archive #1, etc. under 'SQL Server Logs'.
> What is generating these archives? Also, How can I see what has been
> archived? Can I stop the server from doing this or needed for to operate?
> Thank you very much!
> -Bahman
>
These are old versions of the ERRORLOG file. A new ERRORLOG is created when
you restart SQL Server or run sp_cycle_errorlog. The file will be ERRORLOG.1
ERRORLOG.2 etc.. in the LOGS directory. The Displayed name in SSMS and EM
will have the date and time that the log goes up to. More information is in
Books online.
John
|||John,
Thanks a million. I guess the word archive was throwing me off. I was
looking in the wrong place. Somehow I thought it is archiving, so that I can
restore from it. But no info on that anywhere. Thank you very much for your
help.
-Bahman
"John Bell" wrote:

> HI
> "Bahman" wrote:
>
> These are old versions of the ERRORLOG file. A new ERRORLOG is created when
> you restart SQL Server or run sp_cycle_errorlog. The file will be ERRORLOG.1
> ERRORLOG.2 etc.. in the LOGS directory. The Displayed name in SSMS and EM
> will have the date and time that the log goes up to. More information is in
> Books online.
> John
>

Tuesday, March 27, 2012

Are Blank String and String with only Space characters equal to each other? They should not be!!

Are Bit Flags Bad?

are backups not successful?

are backups not successful?

are backups not successful?

are all SQL 2000 Server Wizard included in SQL 2000 Server Standard Edition

Are all indexes created on subscriber ?

Are all DSN's really ODBC?

Are all DSN's really ODBC?

Are all DSN's really ODBC?

are after triggers really after?

Are _WA_Sys_ statistics indexes an indicator of what should be indexed?

Are _WA_Sys_ statistics indexes an indicator of what should be indexed?

ArcServ and SQl Server 2000

Archiving very slow, help needed please

Archiving very slow, help needed please

Archiving very slow, help needed please

archiving tables

Archiving Problem

Archiving Problem

sql server tools

Blog Archive

About Me