Does SQL Server support compressed drive?

Hi, all,

I try to start SQL Server, but I got the Error in "ERRORLOG":

Starting up database 'master'.

The file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf" is compressed but does not reside in a read-only database or filegroup. The file must be decompressed.

Error: 5118, Severity: 16, State: 1.

That means, I have to decompress the file "mastlog.ldf", right?

How can I do this?

Thanks in advance!

it means somebody has changed the OS level setting of this folder or it parent folder... In property of the folder

in General Tab Press Advance button and see whether "Compress Contents to Save disk " option is choosed ... if it is there then remove that

Madhu

|||

Thank you! Madhu,

yes, I have done, It works

sql

Does SQL Server support "after" on alter table ?

Hello,
I'm trying to do this:
alter table maquinas add marca varchar(50) after modelo
and I get the following error:
Incorrect syntax near 'after'.
What is going on?
hansNo, you cannot tell SQL Server where to add a column. It gets apended to
the "end". Why is column order important? Yes, Management Studio /
Enterprise Manager can do this, but guess what they do behind the scenes?
Drop the table and re-create it.
"Hans" <goosseno@.gmail.com> wrote in message
news:5b95bb01-499a-410e-b656-cbe926f06263@.m34g2000hsb.googlegroups.com...
> Hello,
> I'm trying to do this:
> alter table maquinas add marca varchar(50) after modelo
> and I get the following error:
> Incorrect syntax near 'after'.
> What is going on?
> hans|||Well, using Management Studio or Enterprise Manager is not that bad if your
table is small.
Ben Nevarez
although using Management Studio or Enterprise Manager is not that bad if
your table is small.
Ben
"Aaron Bertrand [SQL Server MVP]" wrote:
> No, you cannot tell SQL Server where to add a column. It gets apended to
> the "end". Why is column order important? Yes, Management Studio /
> Enterprise Manager can do this, but guess what they do behind the scenes?
> Drop the table and re-create it.
>
> "Hans" <goosseno@.gmail.com> wrote in message
> news:5b95bb01-499a-410e-b656-cbe926f06263@.m34g2000hsb.googlegroups.com...
> > Hello,
> >
> > I'm trying to do this:
> > alter table maquinas add marca varchar(50) after modelo
> >
> > and I get the following error:
> > Incorrect syntax near 'after'.
> >
> > What is going on?
> >
> > hans
>
>

Does Sql Ce 3.5 beta version support remote access in Intranet

I need to connect to a sql ce data file via Intranet .
Does sql ce support that ?
When I attempt to connect that remote file, it show me the error message
"There is a file sharing violation .A different process migth be using this file".

Does this mean , I can not do this .

This means that SQL Compact is a single user, in-process database, so when opening a file on a network share it will acquire an exclusive lock to the database file. For more information, see these blog entries from Jim Wilson:

http://www.pluralsight.com/blogs/jimw/archive/2007/02/19/46151.aspx

|||Good answer .

Thank you ErikEJ

I like this forum.

Does Sql Ce 3.5 beta version support remote access in Intranet

I need to connect to a sql ce data file via Intranet .
Does sql ce support that ?
When I attempt to connect that remote file, it show me the error message
"There is a file sharing violation .A different process migth be using this file".

Does this mean , I can not do this .

This means that SQL Compact is a single user, in-process database, so when opening a file on a network share it will acquire an exclusive lock to the database file. For more information, see these blog entries from Jim Wilson:

http://www.pluralsight.com/blogs/jimw/archive/2007/02/19/46151.aspx

|||Good answer .

Thank you ErikEJ

I like this forum.

Does SP have a max size?

Hi all. I'm getting a weird error in a stored procedure:
Servidor: mensaje 208, nivel 16, estado 6, procedimiento RegistrarTabla,
lnea 65535
El nombre de objeto 'RegistrarTabla' no es vlido.
That's spanish but should be:
Server: message 208, level 16, state 6, procedure 'RegisterTable', line
65535
The object name 'RegisterTable' is not valid.
The procedure does not have so many lines, and the number 65535 makes me
distrust.
Does SP have a maximum size or something?
Regards,
Diego F.Diego
I think you should be concered about am error and not about a line of code.
Copy the stored procedure's code to Query Analyzer and run it. It may space
or empty strings within a stored procedure.
"Diego F." <diegofrNO@.terra.es> wrote in message
news:e1YpxfolFHA.572@.TK2MSFTNGP15.phx.gbl...
> Hi all. I'm getting a weird error in a stored procedure:
> Servidor: mensaje 208, nivel 16, estado 6, procedimiento RegistrarTabla,
> lnea 65535
> El nombre de objeto 'RegistrarTabla' no es vlido.
> That's spanish but should be:
> Server: message 208, level 16, state 6, procedure 'RegisterTable', line
> 65535
> The object name 'RegisterTable' is not valid.
> The procedure does not have so many lines, and the number 65535 makes me
> distrust.
> Does SP have a maximum size or something?
> --
> Regards,
> Diego F.
>
>|||That's QA which gives me that error. Doesn't apply to any line. It's a
strange error.
Regards,
Diego F.
"Uri Dimant" <urid@.iscar.co.il> escribi en el mensaje
news:u3CM8molFHA.1464@.TK2MSFTNGP14.phx.gbl...
> Diego
> I think you should be concered about am error and not about a line of
> code.
> Copy the stored procedure's code to Query Analyzer and run it. It may
> space or empty strings within a stored procedure.
>
> "Diego F." <diegofrNO@.terra.es> wrote in message
> news:e1YpxfolFHA.572@.TK2MSFTNGP15.phx.gbl...
>|||Diego
If you post DDL + samle data we will be able to test and maybe reproduce the
error's behaviour.
"Diego F." <diegofrNO@.terra.es> wrote in message
news:OqS00qolFHA.1968@.TK2MSFTNGP14.phx.gbl...
> That's QA which gives me that error. Doesn't apply to any line. It's a
> strange error.
> --
> Regards,
> Diego F.
>
> "Uri Dimant" <urid@.iscar.co.il> escribi en el mensaje
> news:u3CM8molFHA.1464@.TK2MSFTNGP14.phx.gbl...
>|||Check that u are connected rt database in which 'RegisterTable' is
present|||While we wait for you to send some DDL, let me ask you this: is
'RegistrarTabla' the name of a user table or your procedure?
ML|||65,535 lines? Have you ever considered publishing that stored procedure as a
book?
"Diego F." <diegofrNO@.terra.es> wrote in message
news:e1YpxfolFHA.572@.TK2MSFTNGP15.phx.gbl...
> Hi all. I'm getting a weird error in a stored procedure:
> Servidor: mensaje 208, nivel 16, estado 6, procedimiento RegistrarTabla,
> lnea 65535
> El nombre de objeto 'RegistrarTabla' no es vlido.
> That's spanish but should be:
> Server: message 208, level 16, state 6, procedure 'RegisterTable', line
> 65535
> The object name 'RegisterTable' is not valid.
> The procedure does not have so many lines, and the number 65535 makes me
> distrust.
> Does SP have a maximum size or something?
> --
> Regards,
> Diego F.
>
>|||OK, thank you all. It was a stupid problem. I was altering a SP with a name
changed, so the name of that SP didn't exist at all!.
I'm a bit embarrased :-P
Regards,
Diego F.
"Diego F." <diegofrNO@.terra.es> escribi en el mensaje
news:e1YpxfolFHA.572@.TK2MSFTNGP15.phx.gbl...
> Hi all. I'm getting a weird error in a stored procedure:
> Servidor: mensaje 208, nivel 16, estado 6, procedimiento RegistrarTabla,
> lnea 65535
> El nombre de objeto 'RegistrarTabla' no es vlido.
> That's spanish but should be:
> Server: message 208, level 16, state 6, procedure 'RegisterTable', line
> 65535
> The object name 'RegisterTable' is not valid.
> The procedure does not have so many lines, and the number 65535 makes me
> distrust.
> Does SP have a maximum size or something?
> --
> Regards,
> Diego F.
>
>

Does RAISERROR store values for @@ERROR?

I tried to write some test code that looks like:
DECLARE @.ErrorValue int
sp_addmessage 50001,10,'This is a test error message'
RAISERROR (50001,10,1)
SELECT @.ErrorValue = @.@.ERROR
If @.ErrorValue <> 0 ...etc.
I expected @.ErrorValue to be 50001, since I tried to move @.@.ERROR to a
variable immediately after the line that raised the error. Instead, a zero
value was returned. Why?
If I try to replace that line with:
RAISERROR (-100,10,1)
...which generates a system error 2758 ("RAISERROR could not locate entry
for error -100 in sysmessages"), and @.ErrorValue is set to 2758, so I know
the code is working.
Does RAISERROR not set the value of @.@.ERROR?
Thanks!> I expected @.ErrorValue to be 50001, since I tried to move @.@.ERROR to a
> variable immediately after the line that raised the error. Instead, a zero
> value was returned. Why?
The behavior is described completely in the documentation for raiserror.
You can use a different severity or override the behavior with an option.|||from BOL
"When an error is raised, the error number is placed in the @.@.ERROR
function, which stores the most recently generated error number. @.@.ERROR
is set to 0 by default for messages with a severity from 1 through 10."
Your second RAISERROR itself raised an error, which is what was returned
in @.@.ERROR, so that's not really a valid test.
Joel wrote:
> I tried to write some test code that looks like:
> DECLARE @.ErrorValue int
> sp_addmessage 50001,10,'This is a test error message'
> RAISERROR (50001,10,1)
> SELECT @.ErrorValue = @.@.ERROR
> If @.ErrorValue <> 0 ...etc.
> I expected @.ErrorValue to be 50001, since I tried to move @.@.ERROR to a
> variable immediately after the line that raised the error. Instead, a zero
> value was returned. Why?
> If I try to replace that line with:
> RAISERROR (-100,10,1)
> ...which generates a system error 2758 ("RAISERROR could not locate entry
> for error -100 in sysmessages"), and @.ErrorValue is set to 2758, so I know
> the code is working.
> Does RAISERROR not set the value of @.@.ERROR?
> Thanks!|||Ahh... that worked. I just tried the same code with an error level of 12, an
d
it works fine. Thanks for the help!
"Trey Walpole" wrote:

> from BOL
> "When an error is raised, the error number is placed in the @.@.ERROR
> function, which stores the most recently generated error number. @.@.ERROR
> is set to 0 by default for messages with a severity from 1 through 10."
> Your second RAISERROR itself raised an error, which is what was returned
> in @.@.ERROR, so that's not really a valid test.
> Joel wrote:
>sql

Does personal edition sql server support jdbc connection?

if it does, what are the settings I must do in the server?
since I got the following error when connect :
com.inet.tds.SQLException: Connection refused: connect
java.net.ConnectException: Connection refused: connect
thanksHi
Yes it does. Check your connection string. AFAIK, Integrated Security is not
supported so make sure that you supply a username and password. Check that
the username/password combination work through query analyzer.
The SQL Event log might show an audit failure (if not, turn auditing on for
failures, restart SQL Server and try again).
Regards
Mike
"Friend" wrote:
> if it does, what are the settings I must do in the server?
> since I got the following error when connect :
> com.inet.tds.SQLException: Connection refused: connect
> java.net.ConnectException: Connection refused: connect
> thanks
>

Does personal edition sql server support jdbc connection?

if it does, what are the settings I must do in the server?
since I got the following error when connect :
com.inet.tds.SQLException: Connection refused: connect
java.net.ConnectException: Connection refused: connect
thanks
Hi
Yes it does. Check your connection string. AFAIK, Integrated Security is not
supported so make sure that you supply a username and password. Check that
the username/password combination work through query analyzer.
The SQL Event log might show an audit failure (if not, turn auditing on for
failures, restart SQL Server and try again).
Regards
Mike
"Friend" wrote:

> if it does, what are the settings I must do in the server?
> since I got the following error when connect :
> com.inet.tds.SQLException: Connection refused: connect
> java.net.ConnectException: Connection refused: connect
> thanks
>

Does personal edition sql server support jdbc connection?

if it does, what are the settings I must do in the server?
since I got the following error when connect :
com.inet.tds.SQLException: Connection refused: connect
java.net.ConnectException: Connection refused: connect
thanksHi
Yes it does. Check your connection string. AFAIK, Integrated Security is not
supported so make sure that you supply a username and password. Check that
the username/password combination work through query analyzer.
The SQL Event log might show an audit failure (if not, turn auditing on for
failures, restart SQL Server and try again).
Regards
Mike
"Friend" wrote:

> if it does, what are the settings I must do in the server?
> since I got the following error when connect :
> com.inet.tds.SQLException: Connection refused: connect
> java.net.ConnectException: Connection refused: connect
> thanks
>sql

Does CLNG work in Sql Compact?

Hi,

When I execute following query against to SqlCE 3.0.53, I am getting an error, could someone guide where it is wrong.

SELECT SLCTD_MENUENTRYID FROM GEN_ENTRY WHERE EXAMID = 'EFBC0657145840CF88184BDCE71430EE'

AND TYPEVALUE IN ('SURG_PROC', 'CV_PROC') AND CLNG(SLCTD_MENUENTRYID) < 0

Error:

Major Error 0x80040E14, Minor Error 25921

> SELECT SLCTD_MENUENTRYID FROM GEN_ENTRY WHERE EXAMID = 'EFBC0657145840CF88184BDCE71430EE'

AND TYPEVALUE IN ('SURG_PROC', 'CV_PROC') AND CLNG(SLCTD_MENUENTRYID) < 0

The function is not recognized by SQL Server Compact Edition. [ Name of function = CLNG,Data type (if known) = ]

CLNG is not availabel in SQL CE. For documention on the SQL CE SQL syntax, see http://msdn2.microsoft.com/en-us/library/ms173372.aspx. You could use CONVERT(int, SLCTD_MENUENTRYID) instead.

|||I feel the above CONVERT function always returns non fractional value.

For ex:
select CONVERT(int, 4.7)= 4
select CONVERT(int, 4.4)=4
select CONVERT(int, -4.7) =-4
select CONVERT(int, -4.4) =-4

But where as CLNG function does the following:
select CLNG(4.7)=5
select CLNG(4.4)=4
select CLNG(-4.7)=-5
select CLNG(-4.4)=-4

I feel the following line will work for us:

select CONVERT(int, round(4.7,0)) =5
select CONVERT(int, round(4.4,0)) =4
select CONVERT(int, round(-4.7,0)) =-5
select CONVERT(int, round(-4.4,0)) =-4

Please let me know if I am wrong.

Thanks|||If that works for you, you are of course right. CLNG is not available in SQL CE, but other conversion functions are.

Does CLNG work in Sql Compact?

Hi,

When I execute following query against to SqlCE 3.0.53, I am getting an error, could someone guide where it is wrong.

SELECT SLCTD_MENUENTRYID FROM GEN_ENTRY WHERE EXAMID = 'EFBC0657145840CF88184BDCE71430EE'

AND TYPEVALUE IN ('SURG_PROC', 'CV_PROC') AND CLNG(SLCTD_MENUENTRYID) < 0

Error:

Major Error 0x80040E14, Minor Error 25921

> SELECT SLCTD_MENUENTRYID FROM GEN_ENTRY WHERE EXAMID = 'EFBC0657145840CF88184BDCE71430EE'

AND TYPEVALUE IN ('SURG_PROC', 'CV_PROC') AND CLNG(SLCTD_MENUENTRYID) < 0

The function is not recognized by SQL Server Compact Edition. [ Name of function = CLNG,Data type (if known) = ]

CLNG is not availabel in SQL CE. For documention on the SQL CE SQL syntax, see http://msdn2.microsoft.com/en-us/library/ms173372.aspx. You could use CONVERT(int, SLCTD_MENUENTRYID) instead.

|||I feel the above CONVERT function always returns non fractional value.

For ex:
select CONVERT(int, 4.7)= 4
select CONVERT(int, 4.4)=4
select CONVERT(int, -4.7) =-4
select CONVERT(int, -4.4) =-4

But where as CLNG function does the following:
select CLNG(4.7)=5
select CLNG(4.4)=4
select CLNG(-4.7)=-5
select CLNG(-4.4)=-4

I feel the following line will work for us:

select CONVERT(int, round(4.7,0)) =5
select CONVERT(int, round(4.4,0)) =4
select CONVERT(int, round(-4.7,0)) =-5
select CONVERT(int, round(-4.4,0)) =-4

Please let me know if I am wrong.

Thanks|||If that works for you, you are of course right. CLNG is not available in SQL CE, but other conversion functions are.

Does anyone know what this means? Anyone?

AuthzInitializeContextFromSid: Win32 error: 234
get this when trying to send an email subscription or a file-share
subscription. Running the service as a domain account and made the account a
member of the WAA group.I should probably mention that I love the "there is more information in the
log file" message that you can find....you guessed it...in the log file.
"chicagoclone" wrote:
> AuthzInitializeContextFromSid: Win32 error: 234
> get this when trying to send an email subscription or a file-share
> subscription. Running the service as a domain account and made the account a
> member of the WAA group.|||In fact, I read the log file, it said "there is more information in the log
file".
so I checked the log file, and it said "there is more information in the log
file".
so I checked the log file, and it said "there is more information in the log
file".
so I checked the log file, and it said "there is more information in the log
file".
so I checked the log file, and it said "there is more information in the log
file".
so I checked the log file, and it said "there is more information in the log
file".
so I checked the log file, and it said "there is more information in the log
file".
so I checked the log file, and it said "there is more information in the log
file".
so I checked the log file, and it said "there is more information in the log
file".
so I checked the log file, and it said "there is more information in the log
file".
I eventually got out of this endless loop after about 16 hours of
recursively checking the log file.
"chicagoclone" wrote:
> AuthzInitializeContextFromSid: Win32 error: 234
> get this when trying to send an email subscription or a file-share
> subscription. Running the service as a domain account and made the account a
> member of the WAA group.

Does a transaction automatically rollback on error?

When I write code for a multiple statements transaction do I need to check'if @.@.ERROR > 0 ' after each SELECT, INSERT, DELETE or UPDATE statement so that the'rollback tran' statement can be given, or SQL server will automatically rollback the transaction and we don't need to check for @.ERROR > 0 ?

If you start a transaction (with BEGIN TRANSACTION) you must end it with a COMMIT or ROLLBACK. So, yes, you should check the @.@.ERROR value after each pertinent statement and explicitly ROLLBACK the transaction if there is an error. I am not sure if there were any changes in this area for SQL Server 2005.|||In SQL 2005, you could do:

BEGIN TRY
.....
COMMIT
END TRY
BEGIN CATCH
.....
ROLLBACK
END CATCH

The good thing is you could put multiple SQL statementsin the TRY block. But there are some limitations too. check out books on line.

Does a database need contiguous space?

Although a DB doesn't "need" contiguous space, it generally performs better
if it does have it.
What specifically is the error you get? Does the SQL Server service account
have write permission on that partition?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Mike Chiodo" <MikeChiodo@.discussions.microsoft.com> wrote in message
news:B860E70E-2CC7-4644-A624-C21EB0373BCF@.microsoft.com...
Hello,
We had a problem that we're trying to figure out. We have a database that's
about 3.5GB in size on a partition with about 10GB of free space. The
database appeared normal, however when the client tried to write data to it
we'd receive a write error message.
We recreated the database on a different partition with 40GB free and it
works fine now. However we're still trying to figure out why the original
database failed and there comes the question-does SQL need contiguous free
space to work? The drive may be fairly fragmented and while there is ~10GB
free there may not be much contiguous space.
Everything else with SQL appears normal.
Thanks!Hello,
We had a problem that we're trying to figure out. We have a database that's
about 3.5GB in size on a partition with about 10GB of free space. The
database appeared normal, however when the client tried to write data to it
we'd receive a write error message.
We recreated the database on a different partition with 40GB free and it
works fine now. However we're still trying to figure out why the original
database failed and there comes the question-does SQL need contiguous free
space to work? The drive may be fairly fragmented and while there is ~10GB
free there may not be much contiguous space.
Everything else with SQL appears normal.
Thanks!|||Although a DB doesn't "need" contiguous space, it generally performs better
if it does have it.
What specifically is the error you get? Does the SQL Server service account
have write permission on that partition?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Mike Chiodo" <MikeChiodo@.discussions.microsoft.com> wrote in message
news:B860E70E-2CC7-4644-A624-C21EB0373BCF@.microsoft.com...
Hello,
We had a problem that we're trying to figure out. We have a database that's
about 3.5GB in size on a partition with about 10GB of free space. The
database appeared normal, however when the client tried to write data to it
we'd receive a write error message.
We recreated the database on a different partition with 40GB free and it
works fine now. However we're still trying to figure out why the original
database failed and there comes the question-does SQL need contiguous free
space to work? The drive may be fairly fragmented and while there is ~10GB
free there may not be much contiguous space.
Everything else with SQL appears normal.
Thanks!|||Yes, the SQL server account has permission to write to that partition. As
far as the error message-it was an error generated by the program not a SQL
error.
I suspect the database was simply corrupt but don't know for certain.
Thanks for your answer-it helped clear up a piece of the puzzle.
Mike
"Tom Moreau" wrote:

> Although a DB doesn't "need" contiguous space, it generally performs bette
r
> if it does have it.
> What specifically is the error you get? Does the SQL Server service accou
nt
> have write permission on that partition?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Mike Chiodo" <MikeChiodo@.discussions.microsoft.com> wrote in message
> news:B860E70E-2CC7-4644-A624-C21EB0373BCF@.microsoft.com...
> Hello,
> We had a problem that we're trying to figure out. We have a database that
's
> about 3.5GB in size on a partition with about 10GB of free space. The
> database appeared normal, however when the client tried to write data to i
t
> we'd receive a write error message.
> We recreated the database on a different partition with 40GB free and it
> works fine now. However we're still trying to figure out why the original
> database failed and there comes the question-does SQL need contiguous free
> space to work? The drive may be fairly fragmented and while there is ~10G
B
> free there may not be much contiguous space.
> Everything else with SQL appears normal.
> Thanks!
>|||Yes, the SQL server account has permission to write to that partition. As
far as the error message-it was an error generated by the program not a SQL
error.
I suspect the database was simply corrupt but don't know for certain.
Thanks for your answer-it helped clear up a piece of the puzzle.
Mike
"Tom Moreau" wrote:

> Although a DB doesn't "need" contiguous space, it generally performs bette
r
> if it does have it.
> What specifically is the error you get? Does the SQL Server service accou
nt
> have write permission on that partition?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Mike Chiodo" <MikeChiodo@.discussions.microsoft.com> wrote in message
> news:B860E70E-2CC7-4644-A624-C21EB0373BCF@.microsoft.com...
> Hello,
> We had a problem that we're trying to figure out. We have a database that
's
> about 3.5GB in size on a partition with about 10GB of free space. The
> database appeared normal, however when the client tried to write data to i
t
> we'd receive a write error message.
> We recreated the database on a different partition with 40GB free and it
> works fine now. However we're still trying to figure out why the original
> database failed and there comes the question-does SQL need contiguous free
> space to work? The drive may be fairly fragmented and while there is ~10G
B
> free there may not be much contiguous space.
> Everything else with SQL appears normal.
> Thanks!
>

DOCID Error when upgrading from to SQL 2005 FTK Size

Hi,

How to determine the size of FTK, please help,

Since the DOCID map in SQL Server 2005 is stored in the database, the size requirement on the database side has increased, but has reduced on the full-text catalog side. If a database contains a full-text index, you need to ensure that the file group associated with the base table has enough space to accommodate the additional space requirement for full-text indexes. Use the following formula to estimate the space required.
(2*FTK+ 34bytes) * RC
Where:
FTK = Full-Text Key Size
RC = Row Count of the Table

thanks,

Imran.

Hello, I am going to move this thread over to SQL Server Database Engine, since you are more likely to get help on there. Thanks!|||

Imran,

What specific DOCID error did you get? The below text is directly from the SQL Server 2005 Upgrade Advisor Books Online (BOL) topic "Increase database size to accommodate DOCID map" Did you check the crawl log to identify the error and increase the space of the file group? Note, the craw log is located in the LOG directory on the server where you have installed SQL Server 2005.

Below are two additional references that might be helpful to you depending upon the DOCID error you are getting:

"Additional space requirement for Full-Text Search - Because the document identifier (DOCID) map in SQL Server 2005 is stored in the database, the size requirement on the database side has increased. However, the size requirement on the full-text catalog side has decreased." - from: Breaking Changes to Full-Text Search in SQL Server 2005
http://msdn2.microsoft.com/en-us/library/ms143709.aspx

See Step 10 and screenshot for SQL 2005 upgrade wizard (search on Docid to find it quickly) in:
http://h20331.www2.hp.com/ActiveAnswers/downloads/Migrate_SQL2005_Integrity_1005.pdf

Regards,
John
SQL Full Text Search Blog
http://jtkane.spaces.live.com/

|||I would also like to know how to determine the FTK size....

DOCID Error when upgrading from to SQL 2005 FTK Size

Hi,

How to determine the size of FTK, please help,

Since the DOCID map in SQL Server 2005 is stored in the database, the size requirement on the database side has increased, but has reduced on the full-text catalog side. If a database contains a full-text index, you need to ensure that the file group associated with the base table has enough space to accommodate the additional space requirement for full-text indexes. Use the following formula to estimate the space required.
(2*FTK+ 34bytes) * RC
Where:
FTK = Full-Text Key Size
RC = Row Count of the Table

thanks,

Imran.

Hello, I am going to move this thread over to SQL Server Database Engine, since you are more likely to get help on there. Thanks!|||

Imran,

What specific DOCID error did you get? The below text is directly from the SQL Server 2005 Upgrade Advisor Books Online (BOL) topic "Increase database size to accommodate DOCID map" Did you check the crawl log to identify the error and increase the space of the file group? Note, the craw log is located in the LOG directory on the server where you have installed SQL Server 2005.

Below are two additional references that might be helpful to you depending upon the DOCID error you are getting:

"Additional space requirement for Full-Text Search - Because the document identifier (DOCID) map in SQL Server 2005 is stored in the database, the size requirement on the database side has increased. However, the size requirement on the full-text catalog side has decreased." - from: Breaking Changes to Full-Text Search in SQL Server 2005
http://msdn2.microsoft.com/en-us/library/ms143709.aspx

See Step 10 and screenshot for SQL 2005 upgrade wizard (search on Docid to find it quickly) in:
http://h20331.www2.hp.com/ActiveAnswers/downloads/Migrate_SQL2005_Integrity_1005.pdf

Regards,
John
SQL Full Text Search Blog
http://jtkane.spaces.live.com/

|||I would also like to know how to determine the FTK size....

Do you understand this Error msg (ERROR Msg 512, Level 16, State 1, Line 33)

I have created a procedure which start by fecthing data from DB-X and put in into the temporary memory. what im trying to do now is to take data from temporary memory insert/update DB-Y.

But now I get this ERROR Msg 512, Level 16, State 1, Line 33
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

HERE IS MY PROCEDURE

DECLARE @.MineID int,
@.MineName varchar(80),
@.MineDescription [varchar](80) ,
@.MineLocation varchar(80),
@.Country varchar(80),
@.Northing float,
@.Easting float,
@.Elevation float,
@.Latitude float ,
@.Longitude float,
@.MineLogo varbinary(max)

DECLARE MYCURSOR CURSOR
FOR SELECT * FROM [TLC].[DBO].[MINE]
OPEN MYCURSOR
FETCH NEXT FROM MYCURSOR INTO
@.MineID,
@.MineName ,
@.MineDescription ,
@.MineLocation ,
@.Country ,
@.Northing ,
@.Easting ,
@.Elevation ,
@.Latitude,
@.Longitude,
@.MineLogo
WHILE @.@.FETCH_STATUS = 0

BEGIN

IF @.MineID =(select MineID from [TEST].[dbo].[MINE1])
SET IDENTITY_INSERT [TEST].[dbo].[MINE1] ON
BEGIN UPDATE [TEST].[dbo].[MINE1]
SET MineName = @.MineName
,MineDescription = @.MineDescription
,MineLocation = @.MineLocation
,Country =@.Country
,Northing = @.Northing
,Easting = @.Easting
,Elevation = @.Elevation
,Latitude = @.Latitude
,Longitude = @.Longitude
,MineLogo =@.MineLogo
where MineId = @.MineID

END
IF @.MineID <> (select MineID from [TEST].[dbo].[MINE1])
SET IDENTITY_INSERT [TEST].[dbo].[MINE1] ON
INSERT INTO [TEST].[dbo].[MINE1]
(MineID
,MineName
,MineDescription
,MineLocation
,Country
,Northing
,Easting
,Elevation
,Latitude
,Longitude
,MineLogo )
VALUES (
@.MineID
,@.MineName
,@.MineDescription
,@.MineLocation
,@.Country
,@.Northing
,@.Easting
,@.Elevation
,@.Latitude
,@.Longitude
,@.MineLogo)

FETCH NEXT FROM MYCURSOR INTO

@.MineID,
@.MineName ,
@.MineDescription ,
@.MineLocation ,
@.Country ,
@.Northing ,
@.Easting ,
@.Elevation ,
@.Latitude,
@.Longitude,
@.MineLogo

END
SET IDENTITY_INSERT [TEST].[dbo].[MINE1] OFF
SET IDENTITY_INSERT [TEST].[dbo].[MINE1] OFF
CLOSE MYCURSOR
DEALLOCATE MYCURSORYou don't need cursors for this purpose. Please look at following sample that demonstrates how you should synchronize your data:

create database temp
create database temp1
go
create table temp.dbo.mine(id int identity, name nvarchar(max))
create table temp1.dbo.mine(id int identity, name nvarchar(max))

insert temp.dbo.mine(name) values('UpdatedValue1')
insert temp.dbo.mine(name) values('UpdatedValue2')
insert temp.dbo.mine(name) values('UpdatedValue3')
insert temp.dbo.mine(name) values('UpdatedValue4')
insert temp.dbo.mine(name) values('UpdatedValue5')
insert temp.dbo.mine(name) values('NewValue6')
insert temp1.dbo.mine(name) values('OldValue1')
insert temp1.dbo.mine(name) values('OldValue2')
insert temp1.dbo.mine(name) values('OldValue3')
insert temp1.dbo.mine(name) values('OldValue4')
insert temp1.dbo.mine(name) values('OldValue5')
select * from temp.dbo.mine
select * from temp1.dbo.mine

update temp1.dbo.mine
set
name = Source.name
from temp.dbo.mine Source
where mine.id = Source.id

set identity_insert temp1.dbo.mine on
insert temp1.dbo.mine(id, name)
select id, name
from temp.dbo.mine
where id not in (select id from temp1.dbo.mine)
set identity_insert temp1.dbo.mine off

select * from temp.dbo.mine
select * from temp1.dbo.mine

drop database temp
drop database temp1|||Thank you for your response, But my problem is not resolved yet, What im actually trying to do is to take data from Another DB-TLC to DB-Test. And these two database they are not integrated and we can't intergrate them.So what will happen is: if the data exist it can update else insert a new record. I'm not creating database from scratch.

this error is trigged by these statemement

IF @.BlasterID <> (select BlasterID from Blasters )
INSERT INTO [TEST].[dbo].[Blasters]

BELOW IS THE ERROR
{Msg 512, Level 16, State 1, Line 33
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.}

It seems like SQL doesn't want that , I have also tried the IF NOT EXIST clause but still it doesn't help. Please if you have any ideas agains on how to handle this with SQL please help|||

Quote:

Originally Posted by Sally1053

Thank you for your response, But my problem is not resolved yet, What im actually trying to do is to take data from Another DB-TLC to DB-Test

Have you looked at sample I provided? It demonstrates how to synchronize data from two different databases in more efficient way than you do.
--
Concerning your particular problem with
IF @.BlasterID <> (select BlasterID from Blasters ):
You can compare a variable (@.BlasterID) with resultset (select BlasterID from Blasters) only when resultset contains a single row with single column. In your case resultset contains all rows from Blasters table, not one. Correct solution is:
IF NOT EXISTS (select * from Blasters WHERE BlasterID = @.BlasterID)

Do YOU see the syntax error?

Hey All,
I get error 170 on the select line(syntax) saying there is an error around the '='. To me this looks good, I must be missing something obvious - a second pair of eyes and maybe a working brain would really help me out - Thanks

DECLARE @.CylinderID int
DECLARE @.LocationID int
DECLARE @.CurrentLocID int

SELECT Distinct bl.[Date], i.BottleID = @.CylinderID, i.Loc_ID = @.LocationID
FROM [Bottle Location] bl, inserted i
WHERE bl.BottleID = i.BottleID
AND bl.[Date] = (SELECT max(bl.[DATE])
From [Bottle Location] bl, inserted i
WHERE bl.BottleID = i.BottleID)could you state exactly what are you trying to return here?

SELECT Distinct bl.[Date], i.BottleID = @.CylinderID, i.Loc_ID = @.LocationID

I get the feeling you are trying to return all rows where i.BottleID contains the same value as @.CylinderID and i.Loc_ID contains the same value as @.LocationID...|||Hey - I was trying to assign the value to the variables based on the most recent date in the location table for that cylinder. However, you were right, I wasn't writing it correctly. I re-wrote it and the syntax is good now. I'd still be sitting here though, so thanks for the comments. A second pair of eyes is sometimes the greatest help!|||Glad you got it straightened out!