Problems installing SP4 on MSDE 2000 (SP3)

Hi there,

I have some trouble installing SP4 onto an existing MSDE 2000 (with SP3).
I run setup like described in the readme: "setup /upgradesp sqlrun" => Setup starts and I get: "Product already installed ... "
Im using the MSDE version of the SP4 setup, the MSSQL version Im running is 8.00.760
Ive also tried to use the UPGRADEUSER and UPGRADEPWD arguments, but with the same results.

Any ideas?

Thank you

I guess you attempted couple of times SP4 install.

You need to modify following registry key and rerun setup.

after running successfull you need to change it back to original values.

Registry key is:

Local Machine\software\Microsoft\MSSQLServer\MSSqlServer\CurrentVersion

change 8.00.194 to something else [8.00.888]

change 8.00.761 to something else [8.00.999]

Thanks!

Problems inserting decimal numbers

Hi,
I'm having trouble inserting a decimal numbre in SQL
Server. I have an app built on .Net.
I'm passsing the number "98.5" to a store procedure and
SQL Server is saving the number but like this 985.00.
What seems to be the problem?
The regional settings of the computer at first where:
decimal separator ","
List separator "."
group separator "."
I have changed them to
decimal separator "."
List separator ","
group separator ","
But is looks like its not working....
Please help....Manrique,
we know that SQL Server does not do that, so the answer is to find where the
values gets changed: is it changed by the application? Is it changed within
the stored procedure? I think that's what you need look at. Have the
stored proc return the value of the variable at various stages of the stored
proc, I believe you would see a "whoops" somewhere.
Quentin
"Manrique" <mulloa@.grupo-polymer.com> wrote in message
news:02ee01c38c31$d5624820$a301280a@.phx.gbl...
> Hi,
> I'm having trouble inserting a decimal numbre in SQL
> Server. I have an app built on .Net.
> I'm passsing the number "98.5" to a store procedure and
> SQL Server is saving the number but like this 985.00.
> What seems to be the problem?
> The regional settings of the computer at first where:
> decimal separator ","
> List separator "."
> group separator "."
> I have changed them to
> decimal separator "."
> List separator ","
> group separator ","
> But is looks like its not working....
> Please help....

Problems importing TXT files using DTS

Hello,

I've been trying to import a TXT file into an SQL database and I'm having trouble making it work correctly. It is a ASCII text file with over 100,000 records. The fields vary by the number of characters. This can be 2 characters up to 40 (STATE would be 2 characters, CITY is 32 characters, etc.)

I can import the file with DTS. I go in and select exactly where I want the field breaks to be. Then it imports everything as Characters with column headers of Col001, Col002, Col003, etc. My problem is that I don't want everything as Characters or Col001 etc. I want different column names and columns of data to be INT, NUMERIC(x,x), etc. instead of characters every time. If I change these values to anything than the default in DTS it won't import the data correctly.

Also, I have an SQL script that I wrote for a table where I can create the field lengths, data type etc. the way I want it to look, FWIW. This seems to be going nowhere fast.

What am I doing wrong? Should I be using something else than DTS?

Any suggestions are greatly appreciated.

Thanks,
JByou should create a transform data task and connect the text file to the destination though the task.
create the destination table in the xform task and map the data types to the new destinations.

ps while you are there change the column mappings from x individual column copies to 1 single thread it will increase your performance.|||Thanks for the response. I'm going to look into this today. If anyone has any other suggestions, I'd appreciate them too.|||Ruprect,

Come to find out that yesterday I was trying the exact thing you mentioned in your first post. I examined it a little more thorough this morning. The only thing different that I wasn't using 1 thread.

I have 94 columns worth of data. If I select the first 30 columns it will import the text and insert it into the table with appropriate column headings to perfection. The first 30 columns are all character fields.

At Column 31 the first integer field appears. It will stop and give me an error message. It says this, "... conversion error: Conversion invalid for datatypes on column pair 1 (source column 'Col031' (DBTYPE_STR), destination column 'MARKETVALUE' (DBTYPE_I4). There are all numbers as well, no letters or symbols in these columns.

I'm fairly certain I could convert the column to character and it would work fine. The problem is that I need the column to be an integer (as well as my other numeric columns) to perform various mathematical queries.

Thanks again,
JB|||Yeah, you've got bad data...

I would load the data to a table of a varchars...like you have now

Think of it as a staging table

Then audit the data...

For example

Col030 should be a date

Find out all the rows with bad dates

SELECT * FROM myTable99 WHERE ISDATE(Col030)=0

Will show you rows sql doesn't consider a date...

Same with numerics

ISNUMERIC(col1)=0

You won't be able to load those rows to your final destination table

either fix the input file, or ignore the rows...

in any case, go to who ever gave you the file and say...see here...this is garbage...fix it...

and wait a week while they try to figure out what to do...

Golf, Tennis, Long lunches...whatever you want...|||Brett,

You are correct it was bad data. That should have been the first place I looked.

I went into Query Analyzer and ran the query you posted on the columns of data that I wanted to turn into numeric data. 5 out of 32 potential columns had issues. It seems that some of the fields that I wanted to turn into numeric values had blank values. I guess you can't convert a blank character <NULL?> into a blank number.

Anyways, the problem is solved.

Thanks,
JB

Problems creating format file.

sql2k sp4

Howdy all. I havent used format files inside BCP in several years and am having trouble creating one now.

declare @.exec varchar(1026)
set @.exec = 'bcp faa_ivr.dbo.primary_informant format -SboxName\instanceName -c -T -f\\destination\FAAIVR\primary_informant_format.txt '
exec master..xp_cmdshell @.exec

output
----------------------------------------------------------------------------
SQLState = 08001, NativeError = 17
Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied.
SQLState = 01000, NativeError = 2
Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Connect()).
NULL

(5 row(s) affected)

I've tried brackets ([])around the box/ instance name. I've tried using the FQDN. I tried the SA account instead of WINNT authentication. All ideas are appreciated.it might be that the service account doesn't have permission to the location that the file is at. copy it to the server|||The file doesnt exist yet, Im trying to create it. I've also tried to create it locally, and that didn't work.|||I like to create my own

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[isp_GenFormatCards]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[isp_GenFormatCards]
GO

CREATE PROC isp_GenFormatCards
AS
DECLARE FormatCard CURSOR FOR
SELECT FORMAT_CARD, TABLE_NAME, TABLE_SCHEMA FROM (
/*
SELECT '--' + TABLE_NAME AS FORMAT_CARD
, TABLE_NAME, null AS COLUMN_NAME, 0 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_TYPE = 'BASE TABLE'
UNION ALL
*/
SELECT '7.0' AS FORMAT_CARD
, TABLE_NAME, TABLE_SCHEMA, null AS COLUMN_NAME, 1 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_TYPE = 'BASE TABLE'
UNION ALL
SELECT CONVERT(varchar(5),MAX(ORDINAL_POSITION)) AS FORMAT_CARD
, c.TABLE_NAME, c.TABLE_SCHEMA, null AS COLUMN_NAME, 2 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Columns c
INNER JOIN INFORMATION_SCHEMA.Tables t
ON c.TABLE_NAME = t.TABLE_NAME
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND TABLE_TYPE = 'BASE TABLE'
GROUP BY c.TABLE_NAME, c.TABLE_SCHEMA
UNION ALL
SELECT CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+'SQLC HAR'+CHAR(9)+'0'+CHAR(9)
+ CONVERT(varchar(5),
CASE WHEN DATA_TYPE IN ('char','varchar','nchar','nvarchar') THEN CHARACTER_MAXIMUM_LENGTH
WHEN DATA_TYPE = 'int' THEN 14
WHEN DATA_TYPE = 'smallint' THEN 7
WHEN DATA_TYPE = 'tinyint' THEN 3
WHEN DATA_TYPE = 'bit' THEN 1
WHEN DATA_TYPE IN ('text','image') THEN 0
ELSE 26
END)
+ CHAR(9)+'""'+CHAR(9)+CONVERT(varchar(3),ORDINAL_POSITION)+CHA R(9)+COLUMN_NAME AS FORMAT_CARD
, c.TABLE_NAME, c.TABLE_SCHEMA, null AS COLUMN_NAME, 3 AS SQLGroup, ORDINAL_POSITION AS RowGrouping
FROM INFORMATION_SCHEMA.Columns c
INNER JOIN INFORMATION_SCHEMA.Tables t
ON c.TABLE_NAME = t.TABLE_NAME
AND c.table_schema = t.table_schema
AND TABLE_TYPE = 'BASE TABLE'
WHERE ORDINAL_POSITION < (SELECT MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.Columns i
WHERE i.TABLE_NAME = c.TABLE_NAME)
UNION ALL
SELECT CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+'SQLC HAR'+CHAR(9)+'0'+CHAR(9)+CONVERT(VARCHAR(5),
CASE WHEN DATA_TYPE IN ('char','varchar','nchar','nvarchar') THEN CHARACTER_MAXIMUM_LENGTH
WHEN DATA_TYPE = 'int' THEN 14
WHEN DATA_TYPE = 'smallint' THEN 7
WHEN DATA_TYPE = 'tinyint' THEN 3
WHEN DATA_TYPE = 'bit' THEN 1
WHEN DATA_TYPE IN ('text','image') THEN 0
ELSE 26
END)
+ char(9)+'"\r\n"'+char(9)+CONVERT(varchar(3),ORDINAL_POSITION)+CHA R(9)+COLUMN_NAME AS FORMAT_CARD
, c.TABLE_NAME, c.TABLE_SCHEMA, null AS COLUMN_NAME, 4 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Columns c
INNER JOIN INFORMATION_SCHEMA.Tables t
ON c.TABLE_NAME = t.TABLE_NAME
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND TABLE_TYPE = 'BASE TABLE'
WHERE ORDINAL_POSITION = (SELECT MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.Columns i
WHERE i.TABLE_NAME = c.TABLE_NAME)
)AS XXX
ORDER BY TABLE_NAME, COLUMN_NAME, SQLGroup, RowGrouping


DECLARE @.Card varchar(200), @.TABLE_NAME sysname, @.TABLE_SCHEMA sysname, @.cmd varchar(200), @.x char(2), @.Command_String varchar(8000)
, @.TABLE_NAME_OLD sysname, @.TABLE_SCHEMA_OLD sysname

SELECT @.x = '> ', @.TABLE_NAME_OLD = '', @.TABLE_SCHEMA_OLD = ''

OPEN FormatCard

FETCH NEXT FROM FormatCard INTO @.Card, @.TABLE_NAME, @.TABLE_SCHEMA

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.x = '>>'
IF @.TABLE_SCHEMA+@.TABLE_NAME <> @.TABLE_SCHEMA_OLD+@.TABLE_NAME_OLD
BEGIN
SELECT @.TABLE_SCHEMA_OLD = @.TABLE_SCHEMA
, @.TABLE_NAME_OLD = @.TABLE_NAME
, @.x = '> '
END

SET @.cmd = 'echo ' + @.Card + ' '+ @.x +' d:\Data\Tax\Format\'+@.TABLE_SCHEMA+'_'+@.TABLE_NAME +'.fmt'
SET @.Command_string = 'EXEC master..xp_cmdshell ''' + @.cmd + ''', NO_OUTPUT'
PRINT @.Command_String
Exec(@.Command_String)

FETCH NEXT FROM FormatCard INTO @.Card, @.TABLE_NAME, @.TABLE_SCHEMA
END

CLOSE FormatCard
DEALLOCATE FormatCard

GO

--master..xp_cmdshell 'dir d:\Data\Tax\Format\*.*'|||Your a crazy mo fo.

problem(Unable to add data connection)

Im having trouble creating a new connection through Visual Studio Web Developer. When I try to add a new connection all it gives me is an error-

Unable to add data connection
The DataConnectionDialog could not be intialized

When I test my page it seems alright going to the localhost but when I try to add a connection it wont let me.

What could be the cause?

And how can I fix this problem?

I want to connect to the sql server on my computer.. I cant because of the error above

Hi Leo,

I'm going to claim "not my technology" and send you to another forum. The place that is specifically about SQL Express in VWD is at http://forums.asp.net/54/ShowForum.aspx and I think you'll get a better answer there.

Regards,

Mike Wachal
SQL Express team

-
Mark the best posts as Answers!

Problem with xp_sendmail and @query parameter

I am having a bit of trouble with a new SQLServer 2000 installation. I am
in the process of swapping out a server (sql sp3) and replacing it with a
new server (sql sp3a). Everything went smoothly until I hit a brick wall
with xp_sendmail.
The problem is when xp_sendmail tries to attach a query (with the @.query
parameter), and the SQL for the query runs on a linked server. Under the
old server, this was never a problem. But now I receive the following error
message:
ODBC error 7410 (42000) Remote access not allowed for Windows NT user
activated by SETUSER.
Also, here is the actual sql stmt which is running:
exec xp_sendmail @.recipients='bob@.msn.com', @.message='This is a
test',@.subject='Test 1', @.attach_results=true,@.query='select * from
server1.sales.dbo.branches', @.no_header=true, @.ansi_attachment=TRUE
The links to the other server are identical on both the old and new
machines. Any assistance would be greatly appreciated.
Thanks,
BobMost probably the job is owned by someone who isn't sysadmin, and some stuff
aren't allowed to do when the jobs owner isn't sysadmin (stuff at the server
level, as Agent uses SETUSER to emulate the jobowner's user context *in that
particular database*).
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Bob R." <bobr2@.earthlink.net> wrote in message
news:uoc7zPUrDHA.1488@.TK2MSFTNGP12.phx.gbl...
> I am having a bit of trouble with a new SQLServer 2000 installation. I am
> in the process of swapping out a server (sql sp3) and replacing it with a
> new server (sql sp3a). Everything went smoothly until I hit a brick wall
> with xp_sendmail.
> The problem is when xp_sendmail tries to attach a query (with the @.query
> parameter), and the SQL for the query runs on a linked server. Under the
> old server, this was never a problem. But now I receive the following
error
> message:
> ODBC error 7410 (42000) Remote access not allowed for Windows NT user
> activated by SETUSER.
> Also, here is the actual sql stmt which is running:
> exec xp_sendmail @.recipients='bob@.msn.com', @.message='This is a
> test',@.subject='Test 1', @.attach_results=true,@.query='select * from
> server1.sales.dbo.branches', @.no_header=true, @.ansi_attachment=TRUE
> The links to the other server are identical on both the old and new
> machines. Any assistance would be greatly appreciated.
> Thanks,
> Bob
>|||Thanks, but this is not a job. The problem happens in Query Analyzer. If I
run the same SQL Stmt against the old server in Query Analyzer, it works
fine.
-Bob
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:eg$aOOVrDHA.2772@.TK2MSFTNGP10.phx.gbl...
> Most probably the job is owned by someone who isn't sysadmin, and some
stuff
> aren't allowed to do when the jobs owner isn't sysadmin (stuff at the
server
> level, as Agent uses SETUSER to emulate the jobowner's user context *in
that
> particular database*).
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Bob R." <bobr2@.earthlink.net> wrote in message
> news:uoc7zPUrDHA.1488@.TK2MSFTNGP12.phx.gbl...
> > I am having a bit of trouble with a new SQLServer 2000 installation. I
am
> > in the process of swapping out a server (sql sp3) and replacing it with
a
> > new server (sql sp3a). Everything went smoothly until I hit a brick
wall
> > with xp_sendmail.
> >
> > The problem is when xp_sendmail tries to attach a query (with the @.query
> > parameter), and the SQL for the query runs on a linked server. Under
the
> > old server, this was never a problem. But now I receive the following
> error
> > message:
> >
> > ODBC error 7410 (42000) Remote access not allowed for Windows NT user
> > activated by SETUSER.
> >
> > Also, here is the actual sql stmt which is running:
> >
> > exec xp_sendmail @.recipients='bob@.msn.com', @.message='This is a
> > test',@.subject='Test 1', @.attach_results=true,@.query='select * from
> > server1.sales.dbo.branches', @.no_header=true, @.ansi_attachment=TRUE
> >
> > The links to the other server are identical on both the old and new
> > machines. Any assistance would be greatly appreciated.
> >
> > Thanks,
> >
> > Bob
> >
> >
>|||Perhaps one is executed to text and the other to grid. Grid is faster, and possibly also the text
handling can have been improved in some sp of QA (not sure about this, though). Also, does SET
NOCOUNT ON help.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Bob R." <bobr2@.earthlink.net> wrote in message news:uL3%23d3drDHA.1948@.TK2MSFTNGP12.phx.gbl...
> Thanks, but this is not a job. The problem happens in Query Analyzer. If I
> run the same SQL Stmt against the old server in Query Analyzer, it works
> fine.
> -Bob
>
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:eg$aOOVrDHA.2772@.TK2MSFTNGP10.phx.gbl...
> > Most probably the job is owned by someone who isn't sysadmin, and some
> stuff
> > aren't allowed to do when the jobs owner isn't sysadmin (stuff at the
> server
> > level, as Agent uses SETUSER to emulate the jobowner's user context *in
> that
> > particular database*).
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> >
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Bob R." <bobr2@.earthlink.net> wrote in message
> > news:uoc7zPUrDHA.1488@.TK2MSFTNGP12.phx.gbl...
> > > I am having a bit of trouble with a new SQLServer 2000 installation. I
> am
> > > in the process of swapping out a server (sql sp3) and replacing it with
> a
> > > new server (sql sp3a). Everything went smoothly until I hit a brick
> wall
> > > with xp_sendmail.
> > >
> > > The problem is when xp_sendmail tries to attach a query (with the @.query
> > > parameter), and the SQL for the query runs on a linked server. Under
> the
> > > old server, this was never a problem. But now I receive the following
> > error
> > > message:
> > >
> > > ODBC error 7410 (42000) Remote access not allowed for Windows NT user
> > > activated by SETUSER.
> > >
> > > Also, here is the actual sql stmt which is running:
> > >
> > > exec xp_sendmail @.recipients='bob@.msn.com', @.message='This is a
> > > test',@.subject='Test 1', @.attach_results=true,@.query='select * from
> > > server1.sales.dbo.branches', @.no_header=true, @.ansi_attachment=TRUE
> > >
> > > The links to the other server are identical on both the old and new
> > > machines. Any assistance would be greatly appreciated.
> > >
> > > Thanks,
> > >
> > > Bob
> > >
> > >
> >
> >
>|||I ran a trace on both versions, and I can see that the new version (sp3a) is
running "sp_setuserbylogid" before executing the query. The older machine
is on sp3, and does not do this. I looked at the dll for xp_sendmail
between both machines, and it looks like sp3a updated sqlmap70.dll. (This
is not mentioned anywhere in the release notes) I swapped out the dll for
the one from sp3, and everything works fine. However, since I could find no
documentation concerning changes to xp_sendmail in sp3a, I am not sure if
this is a good idea.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:%236bGUserDHA.372@.TK2MSFTNGP11.phx.gbl...
> Perhaps one is executed to text and the other to grid. Grid is faster, and
possibly also the text
> handling can have been improved in some sp of QA (not sure about this,
though). Also, does SET
> NOCOUNT ON help.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Bob R." <bobr2@.earthlink.net> wrote in message
news:uL3%23d3drDHA.1948@.TK2MSFTNGP12.phx.gbl...
> > Thanks, but this is not a job. The problem happens in Query Analyzer.
If I
> > run the same SQL Stmt against the old server in Query Analyzer, it works
> > fine.
> >
> > -Bob
> >
> >
> > "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > wrote in message news:eg$aOOVrDHA.2772@.TK2MSFTNGP10.phx.gbl...
> > > Most probably the job is owned by someone who isn't sysadmin, and some
> > stuff
> > > aren't allowed to do when the jobs owner isn't sysadmin (stuff at the
> > server
> > > level, as Agent uses SETUSER to emulate the jobowner's user context
*in
> > that
> > > particular database*).
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at:
> > >
> >
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > >
> > >
> > > "Bob R." <bobr2@.earthlink.net> wrote in message
> > > news:uoc7zPUrDHA.1488@.TK2MSFTNGP12.phx.gbl...
> > > > I am having a bit of trouble with a new SQLServer 2000 installation.
I
> > am
> > > > in the process of swapping out a server (sql sp3) and replacing it
with
> > a
> > > > new server (sql sp3a). Everything went smoothly until I hit a brick
> > wall
> > > > with xp_sendmail.
> > > >
> > > > The problem is when xp_sendmail tries to attach a query (with the
@.query
> > > > parameter), and the SQL for the query runs on a linked server.
Under
> > the
> > > > old server, this was never a problem. But now I receive the
following
> > > error
> > > > message:
> > > >
> > > > ODBC error 7410 (42000) Remote access not allowed for Windows NT
user
> > > > activated by SETUSER.
> > > >
> > > > Also, here is the actual sql stmt which is running:
> > > >
> > > > exec xp_sendmail @.recipients='bob@.msn.com', @.message='This is a
> > > > test',@.subject='Test 1', @.attach_results=true,@.query='select * from
> > > > server1.sales.dbo.branches', @.no_header=true, @.ansi_attachment=TRUE
> > > >
> > > > The links to the other server are identical on both the old and new
> > > > machines. Any assistance would be greatly appreciated.
> > > >
> > > > Thanks,
> > > >
> > > > Bob
> > > >
> > > >
> > >
> > >
> >
> >
>

Problem with valid owner

I have just downloaded the MS SQL Server Management Studio Express, and are following a tutorial on www.learnvisualstudio.net

Now I am having trouble with some owner information. The message says :

"Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTORIZATION statement to set the database owner to a valid login, then add the database diagram support objects"

I should mention that my WIN XP username is "Bj?rn" with the Norwegian letter "?" in the middle. I suspect it to have a problem with that letter?

Any better suggestions?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=120849&SiteID=1

When the database does not have a valid owner, the database dialog displays the owner as the logged in user. (This issue will be fixed in SP1.)

The "no valid owner" issue usually comes up when databases are owned by SQL-authentication logins and are upgraded, detached/attached to another server, or restored from backup to another server. The SID (a large number) doesn't match any existing login on the new server, so the owner name remains whatever it was on the old server, but it is marked invalid. Explicitly setting the owner to a valid principal on the server solves the problem.

There is also a known issue when the UI tries to install the database support objects on databases where the compatibility level is set to 80 (SQL Server 2000). The installation fails and the UI incorrectly reports that the database has no valid owner. Setting compatibility level to 90 (SQL Server 2005) before installing the diagram support objects solves this problem. (This issue will also be fixed in SP1.)

HTH

|||

Thank you for taking the time to help me.

The compatibility level was already set to 90, so that didn't solve the problem.

When I tried to set a new owner, I got this message:

TITLE: Microsoft SQL Server Management Studio Express

Set owner failed for Database 'MyCompany'. (Microsoft.SqlServer.Express.Smo

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

An entity of type database cannot be owned by a role, a group, an approle, or by principals mapped to certificates or asymmetric keys. (Microsoft SQL Server, Error: 15353)

|||

Hi,

Do you fin a solution ? I have the same problem ...

Thanks

|||Yes. I think I changed the owner to sa (server admin I think it is)

Problem with using a text data type.

I am using a 'text' data type in a table and am having trouble adding data to it. I'm using an MS Access ADP project front end for my forms. I want to copy and paste from a text editor such as NotePad to a 'text' data type field in a form. There appears to be a limit that the field will accept which is much below the 2GB size that is allowed for the field. I get the following error message: "The text is too long to be edited." Once I get this error I can add characters from the keyboard but eventually the field will not accept any more characters. Typing or pasting does nothing. I can delete. So I've hit a limit but I'm not sure if it is in Access or SQL Server.

Any help would be appreciated.

Dennis GeasanGo to design view on the form. Right click on your text box. Look at the properties. I believe there's a property there to set the max length.|||No such property exists on a 'text' data type field. I did some further testing. The field will take up to 64000 characters and then quit accepting any more. It looks like I will have to use special SQL commands to add additional data to this field. It does not behave the same as an MS Access Memo field and directly allow a large amount of text to be added. Further research indicates the SQL Server 'text' data type is a linked list and I will have to start working with pointers to be able to add more data. Ugly.

Dennis Geasan

Problem with updating column in table - transaction log full

Hello,
I had trouble performing an update of one column of a table.
The column is of Bit data type and the table has 60350 rows.
When I run the following query in the Query Analyzer:
update [Specimen CV] set HPV = 0
I get a message "The log file for database DBNAME is full. Back up the
transaction log for the database to free up space"
I backed up both the DB and the log, truncated as well as increased the size
of the log file, first to 100MB.
I even set the DB to simple recovery mode. Still, I got the same message.
Finally, when I set the size to 1 GB did the update run.
After the update ran I checked the Log Space used and it was 12.6%
Does really a simple query like that on a table with only 60K records
require 126 MB of log space?
Any comment would be appreciated.
Ragnar
On Mar 7, 7:54 pm, "Ragnar Midtskogen" <ragnar...@.newsgroups.com>
wrote:
> Hello,
> I had trouble performing an update of one column of a table.
> The column is of Bit data type and the table has 60350 rows.
> When I run the following query in the Query Analyzer:
> update [Specimen CV] set HPV = 0
> I get a message "The log file for database DBNAME is full. Back up the
> transaction log for the database to free up space"
> I backed up both the DB and the log, truncated as well as increased the size
> of the log file, first to 100MB.
> I even set the DB to simple recovery mode. Still, I got the same message.
> Finally, when I set the size to 1 GB did the update run.
> After the update ran I checked the Log Space used and it was 12.6%
> Does really a simple query like that on a table with only 60K records
> require 126 MB of log space?
> Any comment would be appreciated.
> Ragnar
Are there triggers on this table? Your single update statement, while
seemingly simple, is creating an implicit transaction, and any change
that the update produces, directly or indirectly, is captured within
that transaction. The log file, which stores the "undo" information
for all of these changes, must be large enough to hold everything that
the update statement is changing, within the table being updated, AND
any other tables that are being affected by triggers on that table.
|||Thank you Tracy,
There are no triggers on any of the tables.
I guess this was not an indication of anything wrong.
By the way, I seem to remember that, depending on some settings, the log
will grow if needed, but I have been digging through the SQL Server Books
Online without finding anything about this.
I have seen a few cases in our company where someone has set up an SQL
Server DB without setting up regular backups, where the log just kept
growing. In one case the log used up all the available space on the disk and
effectively shut down the computer, not enough room for the swap file.
I think there is a way to control the growth of the log file, but I have not
found out how to do this.
Any pointers would be appreciated.
Ragnar

Problem with updating column in table - transaction log full

Hello,
I had trouble performing an update of one column of a table.
The column is of Bit data type and the table has 60350 rows.
When I run the following query in the Query Analyzer:
update [Specimen CV] set HPV = 0
I get a message "The log file for database DBNAME is full. Back up the
transaction log for the database to free up space"
I backed up both the DB and the log, truncated as well as increased the size
of the log file, first to 100MB.
I even set the DB to simple recovery mode. Still, I got the same message.
Finally, when I set the size to 1 GB did the update run.
After the update ran I checked the Log Space used and it was 12.6%
Does really a simple query like that on a table with only 60K records
require 126 MB of log space?
Any comment would be appreciated.
RagnarOn Mar 7, 7:54 pm, "Ragnar Midtskogen" <ragnar...@.newsgroups.com>
wrote:
> Hello,
> I had trouble performing an update of one column of a table.
> The column is of Bit data type and the table has 60350 rows.
> When I run the following query in the Query Analyzer:
> update [Specimen CV] set HPV = 0
> I get a message "The log file for database DBNAME is full. Back up the
> transaction log for the database to free up space"
> I backed up both the DB and the log, truncated as well as increased the si
ze
> of the log file, first to 100MB.
> I even set the DB to simple recovery mode. Still, I got the same message.
> Finally, when I set the size to 1 GB did the update run.
> After the update ran I checked the Log Space used and it was 12.6%
> Does really a simple query like that on a table with only 60K records
> require 126 MB of log space?
> Any comment would be appreciated.
> Ragnar
Are there triggers on this table? Your single update statement, while
seemingly simple, is creating an implicit transaction, and any change
that the update produces, directly or indirectly, is captured within
that transaction. The log file, which stores the "undo" information
for all of these changes, must be large enough to hold everything that
the update statement is changing, within the table being updated, AND
any other tables that are being affected by triggers on that table.|||Thank you Tracy,
There are no triggers on any of the tables.
I guess this was not an indication of anything wrong.
By the way, I seem to remember that, depending on some settings, the log
will grow if needed, but I have been digging through the SQL Server Books
Online without finding anything about this.
I have seen a few cases in our company where someone has set up an SQL
Server DB without setting up regular backups, where the log just kept
growing. In one case the log used up all the available space on the disk and
effectively shut down the computer, not enough room for the swap file.
I think there is a way to control the growth of the log file, but I have not
found out how to do this.
Any pointers would be appreciated.
Ragnar|||How much space is used for the log is dictated by how much log records you g
enerate and how often
you empty the log. To empty the log, you have basically two options:
Have the database in simple recovery mode. Now SQL Server will empty the log
automatically (every
time a checkpoint occurs).
Have it in full recovery mode and do regular transaction log backups.
I suggest you read a bit about recovery models and backup to get more insigh
t into this. Also, you
might want to check out (related reading):
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
http://sqlblog.com/blogs/tibor_kara...>
rinking.aspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:e8F6%23RaYHHA.3824@.TK2MSFTNGP02.phx.gbl...
> Thank you Tracy,
> There are no triggers on any of the tables.
> I guess this was not an indication of anything wrong.
> By the way, I seem to remember that, depending on some settings, the log
will grow if needed, but
> I have been digging through the SQL Server Books Online without finding an
ything about this.
> I have seen a few cases in our company where someone has set up an SQL Ser
ver DB without setting
> up regular backups, where the log just kept growing. In one case the log u
sed up all the available
> space on the disk and effectively shut down the computer, not enough room
for the swap file.
> I think there is a way to control the growth of the log file, but I have n
ot found out how to do
> this.
> Any pointers would be appreciated.
> Ragnar
>

Problem with updating column in table - transaction log full

Hello,
I had trouble performing an update of one column of a table.
The column is of Bit data type and the table has 60350 rows.
When I run the following query in the Query Analyzer:
update [Specimen CV] set HPV = 0
I get a message "The log file for database DBNAME is full. Back up the
transaction log for the database to free up space"
I backed up both the DB and the log, truncated as well as increased the size
of the log file, first to 100MB.
I even set the DB to simple recovery mode. Still, I got the same message.
Finally, when I set the size to 1 GB did the update run.
After the update ran I checked the Log Space used and it was 12.6%
Does really a simple query like that on a table with only 60K records
require 126 MB of log space?
Any comment would be appreciated.
RagnarOn Mar 7, 7:54 pm, "Ragnar Midtskogen" <ragnar...@.newsgroups.com>
wrote:
> Hello,
> I had trouble performing an update of one column of a table.
> The column is of Bit data type and the table has 60350 rows.
> When I run the following query in the Query Analyzer:
> update [Specimen CV] set HPV = 0
> I get a message "The log file for database DBNAME is full. Back up the
> transaction log for the database to free up space"
> I backed up both the DB and the log, truncated as well as increased the size
> of the log file, first to 100MB.
> I even set the DB to simple recovery mode. Still, I got the same message.
> Finally, when I set the size to 1 GB did the update run.
> After the update ran I checked the Log Space used and it was 12.6%
> Does really a simple query like that on a table with only 60K records
> require 126 MB of log space?
> Any comment would be appreciated.
> Ragnar
Are there triggers on this table? Your single update statement, while
seemingly simple, is creating an implicit transaction, and any change
that the update produces, directly or indirectly, is captured within
that transaction. The log file, which stores the "undo" information
for all of these changes, must be large enough to hold everything that
the update statement is changing, within the table being updated, AND
any other tables that are being affected by triggers on that table.|||Thank you Tracy,
There are no triggers on any of the tables.
I guess this was not an indication of anything wrong.
By the way, I seem to remember that, depending on some settings, the log
will grow if needed, but I have been digging through the SQL Server Books
Online without finding anything about this.
I have seen a few cases in our company where someone has set up an SQL
Server DB without setting up regular backups, where the log just kept
growing. In one case the log used up all the available space on the disk and
effectively shut down the computer, not enough room for the swap file.
I think there is a way to control the growth of the log file, but I have not
found out how to do this.
Any pointers would be appreciated.
Ragnar|||How much space is used for the log is dictated by how much log records you generate and how often
you empty the log. To empty the log, you have basically two options:
Have the database in simple recovery mode. Now SQL Server will empty the log automatically (every
time a checkpoint occurs).
Have it in full recovery mode and do regular transaction log backups.
I suggest you read a bit about recovery models and backup to get more insight into this. Also, you
might want to check out (related reading):
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
http://sqlblog.com/blogs/tibor_karaszi/archive/2007/02/25/leaking-roof-and-file-shrinking.aspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:e8F6%23RaYHHA.3824@.TK2MSFTNGP02.phx.gbl...
> Thank you Tracy,
> There are no triggers on any of the tables.
> I guess this was not an indication of anything wrong.
> By the way, I seem to remember that, depending on some settings, the log will grow if needed, but
> I have been digging through the SQL Server Books Online without finding anything about this.
> I have seen a few cases in our company where someone has set up an SQL Server DB without setting
> up regular backups, where the log just kept growing. In one case the log used up all the available
> space on the disk and effectively shut down the computer, not enough room for the swap file.
> I think there is a way to control the growth of the log file, but I have not found out how to do
> this.
> Any pointers would be appreciated.
> Ragnar
>