Change Increment Value for existing Identity Column

Hi,
How to Change Increment Value for existing Identity Column (MS SQL2000) ?

I know how to change the seed :
DBCC CHECKIDENT (activity, RESEED,4233596)

but I need the future id generated with step 2
4233596
4233598
4233600
I would like to do it using T-sql because I will need to do it every day after syncronising with another SQL server .

Thanks,
NataliaYou have to drop and recreate the table.sql

Change default DATEFIRST in SQL2000

Hi,
How do I change sql server default DATEFIRST = 7 setting for SQL 2000 to
DATEFIRST = 1?
I am asing to do it globally instead of doing SET DATEFIRST = 1 in sp.
Thank you in advance.
ShaileshHi Shailesh, the SET commands are used at session level not server level.
DATEFIRST inherits it setting from SET LANGUAGE, this in turn gets it from
the language specified for the login. The defualt language for each login
can be changed via sp_configure.
Changing the language for each login may acheive what you're after, however
i would just use SET DATEFIRST in your procs / batches
HTH. Ryan
"Shailesh Patel" <shailesh@.urnerbarry.com> wrote in message
news:ucADm1UFGHA.516@.TK2MSFTNGP15.phx.gbl...
> Hi,
> How do I change sql server default DATEFIRST = 7 setting for SQL 2000 to
> DATEFIRST = 1?
> I am asing to do it globally instead of doing SET DATEFIRST = 1 in sp.
> Thank you in advance.
> Shailesh
>sql

Change default data folder for MSSQL2000

Please can anyone tell me how I can change the default
location of the data folder for SQL2000?
For example it's currently
C:\Program Files\Microsoft SQL Server\MSSQL\Data
I want to change it so that each time a user creates a
database or a backup, the default location for the file to
be created is:
D:\MSSQL\
Can you help?
Thank youFrom Enterprise Mangler, right click the server name, select PROPERTIES.
Choose the Database Settings tab. Near the bottom are the fields for
default database and log file locations.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Val" <anonymous@.discussions.microsoft.com> wrote in message
news:062501c3fae3$6c570bb0$a501280a@.phx.gbl...
> Please can anyone tell me how I can change the default
> location of the data folder for SQL2000?
> For example it's currently
> C:\Program Files\Microsoft SQL Server\MSSQL\Data
> I want to change it so that each time a user creates a
> database or a backup, the default location for the file to
> be created is:
> D:\MSSQL\
> Can you help?
> Thank you|||I found it - apologies
Knowledge base 272705
To change:
Right click your instance, SQL Server Properties, Database
settings tab, enter the location you want eg
D:\MSSQL
If only they were all so easy.|||Val
The default data directory is determined at the time SQL Server was
installed.
create database test
on
primary ( name=test,
filename='d:\my_test.mdf',
size=100mb,
maxsize=200,
filegrowth=20)
LOG on
(
name=test1,
filename='d:\my_test.ldf',
size=100mb,
maxsize=200,
filegrowth )
"Val" <anonymous@.discussions.microsoft.com> wrote in message
news:062501c3fae3$6c570bb0$a501280a@.phx.gbl...
> Please can anyone tell me how I can change the default
> location of the data folder for SQL2000?
> For example it's currently
> C:\Program Files\Microsoft SQL Server\MSSQL\Data
> I want to change it so that each time a user creates a
> database or a backup, the default location for the file to
> be created is:
> D:\MSSQL\
> Can you help?
> Thank you|||there is some sort of bug when you try and do this - you
cannot save the default location all the time.
>--Original Message--
>From Enterprise Mangler, right click the server name,
select PROPERTIES.
>Choose the Database Settings tab. Near the bottom are
the fields for
>default database and log file locations.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
>"Val" <anonymous@.discussions.microsoft.com> wrote in
message
>news:062501c3fae3$6c570bb0$a501280a@.phx.gbl...
>> Please can anyone tell me how I can change the default
>> location of the data folder for SQL2000?
>> For example it's currently
>> C:\Program Files\Microsoft SQL Server\MSSQL\Data
>> I want to change it so that each time a user creates a
>> database or a backup, the default location for the
file to
>> be created is:
>> D:\MSSQL\
>> Can you help?
>> Thank you
>
>.
>

Change CSV file to XLS

Hi,

I want to automate my performance monitoring Reports(sql2000). So I need to import performance monitor output log file .CSV to a table. I feel converting this .CSV to .XLS is good way to importing the log thru DTS. Is there any command/ script to change the file type .CSV to .XLS, so that i can include this one into DTS Step.

thanksOriginally posted by krishna
Hi,

I want to automate my performance monitoring Reports(sql2000). So I need to import performance monitor output log file .CSV to a table. I feel converting this .CSV to .XLS is good way to importing the log thru DTS. Is there any command/ script to change the file type .CSV to .XLS, so that i can include this one into DTS Step.

thanks

Why can you not import the csv file? What advantages does importing a .XLS file provide?|||because, it is easy to import excel file than csv format.|||DTS can handle .CSV files just as easy as it can handle .XLS files that is why I was askng for advantages. Sorry I couldn't help.

Originally posted by krishna
because, it is easy to import excel file than csv format.|||I don't see any issues in handling .CSV or .XLS by Excel and SQL server DTS.|||what datasouce i should use ? Microsoft Text-Treiber(*.txt,*.csv)?
If i use this one as datasource for to import CSV file into table it is not converting datetime column. I was thinking of using Microsoft Excel for the datasource thats why i asked about converting csv to xls

CDONTS on sql2000 ?

I need to send email in html format, but xp_sendmail
doesnot support it.
Does anyone know hot to use cdonts on sql2000 ?
Thx.On Tue, 4 Nov 2003 16:59:28 -0800, "Kresna Rudy Kurniawan"
<kresna_rk@.yahoo.com> wrote:
>I need to send email in html format, but xp_sendmail
>doesnot support it.
>Does anyone know hot to use cdonts on sql2000 ?
Instead of CDONTS, use CDOSYS on Windows 2000.
http://msdn.microsoft.com/library/en-us/dnanchor/html/collabdataobjects.asp
--
Ross McKay, WebAware Pty Ltd
"Words can only hurt if you try to read them. Don't play their game" - Zoolander|||> Does anyone know hot to use cdonts on sql2000 ?
CDONTS is deprecated.
Use CDO.Message or, better yet, xp_smtp_sendmail.
http://www.aspfaq.com/2403|||You can use XPSMTP instead, see http://SQLDev.Net/xp/xpsmtp.htm
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2003 All rights reserved.
"Kresna Rudy Kurniawan" <kresna_rk@.yahoo.com> wrote in message
news:05c201c3a338$109174d0$a401280a@.phx.gbl...
> I need to send email in html format, but xp_sendmail
> doesnot support it.
> Does anyone know hot to use cdonts on sql2000 ?
> Thx.

Catalog Population Fails on Cluster

I have an installation that move their database to a cluster server.
SQL2000 has SP3 applied.
We created a new FTS against a single table, single row.
The table has 500,000 rows of data.
When I select "Start Full Population" the task completes immediately and the
count is one.
The edited text from the Application Log follows:
Event Type: Information
Event Source: Microsoft Search
Event Category: Gatherer
Event ID: 3019
Date: 12/14/2004
Time: 4:10:34 PM
User: N/A
Computer: NT-TWDB2
Description:
The crawl on project <SQLServer SQL0000500005> has started.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
Event Type: Warning
Event Source: Microsoft Search
Event Category: Gatherer
Event ID: 3035
Date: 12/14/2004
Time: 4:10:35 PM
User: N/A
Computer: NT-TWDB2
Description:
One or more warnings or errors for Gatherer project <SQLServer
SQL0000500005> were logged to file <S:\Program Files\Microsoft SQL
Server\MSSQL\FTData\SQLServer\GatherLogs\SQL000050 0005.1.gthr>. If you are
interested in these messages, please, look at the file using the gatherer
log query object (gthrlog.vbs, log viewer web page).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
Event Type: Warning
Event Source: Microsoft Search
Event Category: Gatherer
Event ID: 3036
Date: 12/14/2004
Time: 4:10:35 PM
User: N/A
Computer: NT-TWDB2
Description:
The crawl seed <MSSQL75://SQLServer/318258d2> in project <SQLServer
SQL0000500005> cannot be accessed. Error: 8007007f - The specified procedure
could not be found. .
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
Event Type: Warning
Event Source: Microsoft Search
Event Category: Gatherer
Event ID: 3024
Date: 12/14/2004
Time: 4:10:35 PM
User: N/A
Computer: NT-TWDB2
Description:
The crawl for project <SQLServer SQL0000500005> could not be started,
because no crawl seeds could be accessed. Fix the errors and try the crawl
again.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
Event Type: Information
Event Source: Microsoft Search
Event Category: Gatherer
Event ID: 3018
Date: 12/14/2004
Time: 4:10:36 PM
User: N/A
Computer: NT-TWDB2
Description:
The end of crawl for project <SQLServer SQL0000500005> has been detected.
The Gatherer successfully processed 0 documents totaling 0K. It failed to
filter 1 documents. 0 URLs could not be reached or were denied access.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
I followed the recommendations of
http://support.microsoft.com/default...;en-us;817301,
PRB: Microsoft Search Full-Text Catalog Population Fails When You Upgrade
SQL Server 2000 Failover Cluster to SQL Server 2000 Service Pack 3
I registered every dll in the Bin folder.
This did not help.
Where do I go from here?
Binder,
First of all, thank you for providing the "Microsoft Search" source events
from your server's Application event log as this is very helpful info in
troubleshooting this SQL FTS issue in a clustered environment!
The key error here is "Error: 8007007f - The specified procedure could not
be found." and the two following KB articles may be appropriate to this
problem, but the error reference in KB article Q304282 - "80070002 - The
system cannot find the file specified" is close, but not exactly the same
error (8007007f ). Reviewing past postings in this newsgroup, I found one
with your error and a past poster suggested this can be resolved via
re-running the SearchStp.exe program from SP3 file. Another suggestion was
to change the (fulltext) dependency to the SQL Agent - in a clean install -
this stops all errors from occurring. Below are the KB articles:
Q304282 PRB: Full Text Resource Fails to Come Online on a Cluster with
"Event ID 1069 :
SQL Cluster Resource 'Full Text' failed" Error Message
http://support.microsoft.com/default...304282&sd=tech
812666 How to recover a failed full-text search resource on a clustered
instance of SQL Server 2000
http://support.microsoft.com/default.aspx?kbid=812666
Hope this helps!
John
"Binder" <rgondzur@.NO_SPAM_aicsoft.com> wrote in message
news:uk6Hp734EHA.3908@.TK2MSFTNGP12.phx.gbl...
> I have an installation that move their database to a cluster server.
> SQL2000 has SP3 applied.
> We created a new FTS against a single table, single row.
> The table has 500,000 rows of data.
> When I select "Start Full Population" the task completes immediately and
the
> count is one.
> The edited text from the Application Log follows:
>
> Event Type: Information
> Event Source: Microsoft Search
> Event Category: Gatherer
> Event ID: 3019
> Date: 12/14/2004
> Time: 4:10:34 PM
> User: N/A
> Computer: NT-TWDB2
> Description:
> The crawl on project <SQLServer SQL0000500005> has started.
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> Event Type: Warning
> Event Source: Microsoft Search
> Event Category: Gatherer
> Event ID: 3035
> Date: 12/14/2004
> Time: 4:10:35 PM
> User: N/A
> Computer: NT-TWDB2
> Description:
> One or more warnings or errors for Gatherer project <SQLServer
> SQL0000500005> were logged to file <S:\Program Files\Microsoft SQL
> Server\MSSQL\FTData\SQLServer\GatherLogs\SQL000050 0005.1.gthr>. If you are
> interested in these messages, please, look at the file using the gatherer
> log query object (gthrlog.vbs, log viewer web page).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> Event Type: Warning
> Event Source: Microsoft Search
> Event Category: Gatherer
> Event ID: 3036
> Date: 12/14/2004
> Time: 4:10:35 PM
> User: N/A
> Computer: NT-TWDB2
> Description:
> The crawl seed <MSSQL75://SQLServer/318258d2> in project <SQLServer
> SQL0000500005> cannot be accessed. Error: 8007007f - The specified
procedure
> could not be found. .
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> Event Type: Warning
> Event Source: Microsoft Search
> Event Category: Gatherer
> Event ID: 3024
> Date: 12/14/2004
> Time: 4:10:35 PM
> User: N/A
> Computer: NT-TWDB2
> Description:
> The crawl for project <SQLServer SQL0000500005> could not be started,
> because no crawl seeds could be accessed. Fix the errors and try the crawl
> again.
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> Event Type: Information
> Event Source: Microsoft Search
> Event Category: Gatherer
> Event ID: 3018
> Date: 12/14/2004
> Time: 4:10:36 PM
> User: N/A
> Computer: NT-TWDB2
> Description:
> The end of crawl for project <SQLServer SQL0000500005> has been detected.
> The Gatherer successfully processed 0 documents totaling 0K. It failed to
> filter 1 documents. 0 URLs could not be reached or were denied access.
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
>
> I followed the recommendations of
> http://support.microsoft.com/default...;en-us;817301,
> PRB: Microsoft Search Full-Text Catalog Population Fails When You Upgrade
> SQL Server 2000 Failover Cluster to SQL Server 2000 Service Pack 3
> I registered every dll in the Bin folder.
> This did not help.
> Where do I go from here?
>
>
|||John,
If the original database was backed up from another server then restored to
this cluster machine, is the FTData directory
copied from the original machine also? Does a restore affect the FTData
directory?
I believe they installed SQL to the cluster with FTS, then did a restore
from the other machine's backup.
Wouldn't the FTData folder be created when FTS was installed with the
initial SQL setup?
If this is the case, would the issues you reference concerning the FTData
folder and its files be a possible source of the problem?
Rg
"John Kane" <jt-kane@.comcast.net> wrote in message
news:ehECYk44EHA.1976@.TK2MSFTNGP09.phx.gbl...
> Binder,
> First of all, thank you for providing the "Microsoft Search" source events
> from your server's Application event log as this is very helpful info in
> troubleshooting this SQL FTS issue in a clustered environment!
> The key error here is "Error: 8007007f - The specified procedure could not
> be found." and the two following KB articles may be appropriate to this
> problem, but the error reference in KB article Q304282 - "80070002 - The
> system cannot find the file specified" is close, but not exactly the same
> error (8007007f ). Reviewing past postings in this newsgroup, I found one
> with your error and a past poster suggested this can be resolved via
> re-running the SearchStp.exe program from SP3 file. Another suggestion was
> to change the (fulltext) dependency to the SQL Agent - in a clean
install -[vbcol=seagreen]
> this stops all errors from occurring. Below are the KB articles:
> Q304282 PRB: Full Text Resource Fails to Come Online on a Cluster with
> "Event ID 1069 :
> SQL Cluster Resource 'Full Text' failed" Error Message
> http://support.microsoft.com/default...304282&sd=tech
> 812666 How to recover a failed full-text search resource on a clustered
> instance of SQL Server 2000
> http://support.microsoft.com/default.aspx?kbid=812666
> Hope this helps!
> John
>
>
> "Binder" <rgondzur@.NO_SPAM_aicsoft.com> wrote in message
> news:uk6Hp734EHA.3908@.TK2MSFTNGP12.phx.gbl...
> the
are[vbcol=seagreen]
gatherer[vbcol=seagreen]
> procedure
crawl[vbcol=seagreen]
detected.[vbcol=seagreen]
to[vbcol=seagreen]
Upgrade
>
|||Binder,
No, the FTData directory (and files under it) are not backed up with a
database backup for SQL Server 2000 and SQL Server 7.0, but this will be a
new feature in SQL Server 2005 (Yukon). However, the FTData directory is
installed by default by SQL 2000 Enterprise Edition, but not necessarily at
the same location as the original database, i.e.., different installation
paths.
If a restore was done to the same database, then an internal system table
(sysfulltextcatalogs) may not have the correct path (a value of null is
normal), as if the source server's location of the FT Catalog (may be
different from the FTData directory) is different from the destination
server's location of the FT Catalog then this may be the cause of the error.
Note, for clustered environments, the FT Catalog can only exist on the
shared drive and if the source server's drive letter for the FT Catalog is
different, then this can be correct in the sysfulltextcatalogs table. If
this is the problem, when you create a new FT Catalog in this database it
will fail with an error. Could you try this?
Thanks,
John
"Binder" <rgondzur@.NO_SPAM_aicsoft.com> wrote in message
news:uuMVoF54EHA.2624@.TK2MSFTNGP11.phx.gbl...
> John,
> If the original database was backed up from another server then restored
to[vbcol=seagreen]
> this cluster machine, is the FTData directory
> copied from the original machine also? Does a restore affect the FTData
> directory?
> I believe they installed SQL to the cluster with FTS, then did a restore
> from the other machine's backup.
> Wouldn't the FTData folder be created when FTS was installed with the
> initial SQL setup?
> If this is the case, would the issues you reference concerning the FTData
> folder and its files be a possible source of the problem?
>
> Rg
>
>
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:ehECYk44EHA.1976@.TK2MSFTNGP09.phx.gbl...
events[vbcol=seagreen]
not[vbcol=seagreen]
same[vbcol=seagreen]
one[vbcol=seagreen]
was[vbcol=seagreen]
> install -
and
> are
> gatherer
> crawl
> detected.
> to
> Upgrade
>

Cast - differences between SQL2000 and SQL2005

I came across a problem when migrating from SQL2000 to SQL2005.

Table1 - large table with multiple columns including columns (X21 varchar(50), Start datetime, Complete datetime)

View1 - Only returns integer values into the dwpId column

SELECT X21 AS dwpId, Start AS startDate, Complete AS endDate
FROM Table1
WHERE (X21 IS NOT NULL) AND (X21 LIKE '[0-9]%') AND
(ProjectID NOT LIKE '%_WI')

View2

SELECT cast(dwpId as int) as dwpId, startDate, endDate
FROM View1

In SQL2005, when selecting values from View2 an error is returned indicating that the statement failed when converting a varchar value to a data type int. In SQL2000, this same statement would return the appropriate rows from the view.

I dont think this is a problem with cast..

Problem is with your data, see the Select statement for Creation of view1,

If the data contains any non numerics also, It will select ex: '123A'

Modify your Select Query to fetch only Integers like this

SELECT X21 AS dwpId, Start AS startDate, Complete AS endDate
FROM Table1
WHERE (X21 IS NOT NULL) AND ISNUMERIC(X21)=1 AND
(ProjectID NOT LIKE '%_WI')

|||

IsNumeric does not guarantee that the data can be converted to an integer. IsNumeric simply gurantees that the value can be converted to 'some type' of numeric data type. There is a trick that you can use with IsNumeric to assure that you have an integer.

The following can be copy/pasted to a query analyzer window and run so that you can see the difference.

Declare @.Temp Table(Data VarChar(20))

Insert Into @.Temp Values('1')

Insert Into @.Temp Values('1.3')

Insert Into @.Temp Values('1e4')

Insert Into @.Temp Values('2d3')

Insert Into @.Temp Values('$43.3')

Insert Into @.Temp Values('abc')

Select Data, IsNumeric(Data), IsNumeric(Data + '.0e0')

From @.Temp

Only the first value is an actual integer. By adding .0e0 to the string before checking for IsNumeric, you are guaranteed to have a valid integer.

I recommend changing your query. .. And IsNumeric(X21 + '.0e0') = 1 and ...

|||

Good Suggestion, IsNumeric returns 1, If the data contains valid integer, floating point number, money or decimal type

Change the Query ,according to the mastros suggestion

|||

The problem is that SQL Server 2005 is more aggressive in terms of evaluating expressions in your query and moving them to different stages of the query plan. This might result in conversion error like in your case if the CAST gets computed before the WHERE clause checks. So there is no guarantee that the expressions in the WHERE clause will be computed first. This was true even in SQL Server 2000 except that you probably never hit it for your schema/data set. You can get the same error there also if the query plan changes.

To resolve the problem, you need to either correct your data model to represent the values correctly. Use float if your data is float - don't mix values from different domains. Or you will have to use CASE in the SELECT list to avoid the conversion problem. Note that using CASE expression is the only way to control order of execution of various expressions. See link below for more details (search for unsafe expressions):

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

To summarize you have two solutions:

1. Fix your data model / schema so you represent the values in their proper domain (not float values in varchar and mixing various values in string)

2. Or modify your SELECT in the 2nd view to:

SELECT cast(CASE WHEN dwpId LIKE '[0-9]%' THEN dwpId END as int) as dwpId, startDate, endDate

FROM View1

Note that even above check is not entirely correct because not all values that have just numeric digits can be successfully converted to int. You might get overflow errors for example. You could use ISNUMERIC but that checks for integer, numeric, and money conversions so it will let more data through. So it is best you correct your schema to avoid all these issues.