Access managment in SQL

Hi,
I have just imported my old access database into my new
SQL database and have come over a few problems.
All memo fields have been changed to "ntext" fields. This
is no good for me as I have lots of text in the fields.
When I try and change one of the fileds I get this error:
'creek_products' table
- Unable to modify table.
ADO error: Cannot create a row of size 8129 which is
greater than the allowable maximum of 8060.
The statement has been terminated.
I'm only trying to change one field to nchar because then
it will let me have a lenght of 4000.
Can anyone help me with this?
I need to get the max lenght field type working.
Thakns
I hope you got answer to your question but if not:
In SQL Server every row has a limit in size.
Consider when converting, creating tables first and then pouring data into it.
I am still puzzled what's wrong with ntext data type?
"Luca" wrote:

> Hi,
> I have just imported my old access database into my new
> SQL database and have come over a few problems.
> All memo fields have been changed to "ntext" fields. This
> is no good for me as I have lots of text in the fields.
> When I try and change one of the fileds I get this error:
> 'creek_products' table
> - Unable to modify table.
> ADO error: Cannot create a row of size 8129 which is
> greater than the allowable maximum of 8060.
> The statement has been terminated.
> I'm only trying to change one field to nchar because then
> it will let me have a lenght of 4000.
> Can anyone help me with this?
> I need to get the max lenght field type working.
> Thakns
>
|||Luca wrote:
> Hi,
> I have just imported my old access database into my new
> SQL database and have come over a few problems.
> All memo fields have been changed to "ntext" fields. This
> is no good for me as I have lots of text in the fields.
> When I try and change one of the fileds I get this error:
> 'creek_products' table
> - Unable to modify table.
> ADO error: Cannot create a row of size 8129 which is
> greater than the allowable maximum of 8060.
> The statement has been terminated.
> I'm only trying to change one field to nchar because then
> it will let me have a lenght of 4000.
> Can anyone help me with this?
> I need to get the max lenght field type working.
> Thakns
Why is ntext no good for you? If you have lots of text, as you
mentioned, then ntext or text is what you want. ntext is unicode and
requires twice the storage of text. If you don't require unicode
support, use text.
The nchar(4000) requires SQL Server reserve 4000 bytes for each row.
That will give you terrible performance because only a couple rows will
fit on each SQL Server page (same as nvarchar(4000) would do). There's a
limit of about 8060 total bytes stored within a row (excluding the text
in text and ntext columns). Using text or ntext fixes this by only
requiring a 16 byte pointer in the table. The text is stored elsewhere,
on a different filegroup if you prefer.
Can you explain what problem you feel you are having using ntext?
David G.

Access Link to SQL not updating

I have an SQL database with an old Access Front end.
I have linked the tables into access.
The first 6k or so records are updatable.
All new records are not updatable from Access.
I can update them via the enterprise manager.
I am using an ODBC connection as sa. So I feel that there shouldn't be a
security issue.
The error I get when trying to update newer records is the record locking
msgbox that saves save/copy/drop
But save is grey'd out.
I have an identity field as the primary key so I don't think theres a
problem with the uniqueness of the records.
Is that enough information?
"trevogre" <trevogre@.discussions.microsoft.com> wrote in message
news:7A35E963-030B-4431-822F-FA4C64571FB9@.microsoft.com...
> I have an SQL database with an old Access Front end.
> I have linked the tables into access.
> The first 6k or so records are updatable.
> All new records are not updatable from Access.
> I can update them via the enterprise manager.
> I am using an ODBC connection as sa. So I feel that there shouldn't be a
> security issue.
> The error I get when trying to update newer records is the record locking
> msgbox that saves save/copy/drop
> But save is grey'd out.
> I have an identity field as the primary key so I don't think theres a
> problem with the uniqueness of the records.
> Is that enough information?
Try adding a column to your table of datatype TimeStamp and re-link your
table from Access. Also, be sure to update your copy of Jet with the latest
Jet service pack.
Steve

Access front-end to SQL back-end

I'm new to SQL Server 2000. I decided to purchase this server because
my old company (UPS) used the server to help expedite queries and
manage on-line reporting. So, now it's up to me to utilize and
understand the new server. I have been using MS Access as my front-
end for several ad-hoc databases, so I'd like to keep utilizing this
method. I want to test my first database using the SQL Server.
I've got everything set up. The IT department has installed the
server software on my computer. Within my Access database, I link in
tables from a database called "Progress" using a Merant driver.
Queries, forms, macros and modules are all pulling from these "linked"
tables. Do I need to link these tables into the SQL Server? How do I
transistion this database over to SQL? I guess I'm confused about the
relationship of how all of this should work.
Right now it looks like this:
PROGRESS .... linked...... MS Access ...... reporting/results
Where does SQL fit in?Hi,
What you need to do is migrate your database from whatever RDBMS you are
using to SQL Server. That means transferring the structure of your
database as well as the data. I don't know what RDBMS you are using so
I can't give you detailed instruction on how to do it. Essentially you
need to put your existing RDBMS out of service in favor of SQL Server.
So you can't link the exsiting "tables into SQL Server". You have to
transfer them completely.
A number tools exist for transferring data to SQL Server including bcp,
and DTS/SSIS.
Jonathan
mssbass wrote:
> I'm new to SQL Server 2000. I decided to purchase this server because
> my old company (UPS) used the server to help expedite queries and
> manage on-line reporting. So, now it's up to me to utilize and
> understand the new server. I have been using MS Access as my front-
> end for several ad-hoc databases, so I'd like to keep utilizing this
> method. I want to test my first database using the SQL Server.
>
> I've got everything set up. The IT department has installed the
> server software on my computer. Within my Access database, I link in
> tables from a database called "Progress" using a Merant driver.
> Queries, forms, macros and modules are all pulling from these "linked"
> tables. Do I need to link these tables into the SQL Server? How do I
> transistion this database over to SQL? I guess I'm confused about the
> relationship of how all of this should work.
>
> Right now it looks like this:
>
> PROGRESS .... linked...... MS Access ...... reporting/results
>
> Where does SQL fit in?
>

Access denied starting SQL Server after joining to new Windows domain

Hi, I made following steps:
1. Remove the SQL Server 2000 computer (W2k3) from old Windows domain
2. The same computer joined to new Windows domain (newdomain)
3. On the Domain Controler (W2k3) I create an domain account
SQLLaunch, which is a member of Domain Users group.
4. On the SQL Server 2003 computer I changed the SQL Server service
startup account to SQLStart@.newdomain account. The same thing made I
with the SQL Server Agent Service. Both the SQL Server Service and SQL
Server Agent start automatically when OS starts.
after these steps I tried to start the SQL Server service, but the
SQL Server did not start. Error message dialog appears as follows:
"Could not start the MSSQLSERVER service on Local Computer
Error 5: Access denied"
After I login to the SQL Server computer using SQLStart@.newdomain
account and try to start the sqlservr.exe in SQL Server home directory
similar error dialog appears:
"Access denied"
first after I made the SQLStart@.newdomain account a member of
the local Administrator group on ther SQL Server computer, everything
is going well.
What can I do to avoid to grant the Administrator's rights to the
SQLStart@.newdomain (in order to
minimize risk of exploits - it is not the right way to grant the
Administrator's rights). The desire is to use a domain account
without any Administration rights. Can anyone help me?
thanks
LiborI don't think that the account needs full admin rights to the server, but it
needs the "log on as a service" rights. When you set the account that runs
the service, the OS will automatically grant this right. I'd expect though
that you need to be logged on with an account that has the rights to grant
this rights to do it. In other words, try to log on the the server as
Administrator (or another account with admin rights) and then set the
account for SQLSERVER and SQLServerAgent service. Then the account should be
granted rights to log on as a service. Then try to restart the server (or
just the service). Remember to remove the SQL acounts from the servers admin
group before you test it.
Regards
Steen
Libor Forejtnik wrote:
> Hi, I made following steps:
> 1. Remove the SQL Server 2000 computer (W2k3) from old Windows domain
> 2. The same computer joined to new Windows domain (newdomain)
> 3. On the Domain Controler (W2k3) I create an domain account
> SQLLaunch, which is a member of Domain Users group.
> 4. On the SQL Server 2003 computer I changed the SQL Server service
> startup account to SQLStart@.newdomain account. The same thing made I
> with the SQL Server Agent Service. Both the SQL Server Service and SQL
> Server Agent start automatically when OS starts.
> after these steps I tried to start the SQL Server service, but the
> SQL Server did not start. Error message dialog appears as follows:
> "Could not start the MSSQLSERVER service on Local Computer
> Error 5: Access denied"
> After I login to the SQL Server computer using SQLStart@.newdomain
> account and try to start the sqlservr.exe in SQL Server home directory
> similar error dialog appears:
> "Access denied"
> first after I made the SQLStart@.newdomain account a member of
> the local Administrator group on ther SQL Server computer, everything
> is going well.
> What can I do to avoid to grant the Administrator's rights to the
> SQLStart@.newdomain (in order to
> minimize risk of exploits - it is not the right way to grant the
> Administrator's rights). The desire is to use a domain account
> without any Administration rights. Can anyone help me?
> thanks
> Libor|||Thank for this usefull advice. There is another way to solve the
problem too. Simply reinstall the server with the option
"registry rebuild". I guess, it makes the same things as You
recomended. In addition, the startup account must be
registered as a SQL Server login.
Unfortunatelly, starting the SQLServerAgent needs to have the startup
account a member of the SQL Server System Administration group.
But this is not a critical for me.
Libor
On Wed, 9 Mar 2005 10:08:37 +0100, "Steen Persson"
<SPE@.REMOVEdatea.dk> wrote:

>I don't think that the account needs full admin rights to the server, but i
t
>needs the "log on as a service" rights. When you set the account that runs
>the service, the OS will automatically grant this right. I'd expect though
>that you need to be logged on with an account that has the rights to grant
>this rights to do it. In other words, try to log on the the server as
>Administrator (or another account with admin rights) and then set the
>account for SQLSERVER and SQLServerAgent service. Then the account should b
e
>granted rights to log on as a service. Then try to restart the server (or
>just the service). Remember to remove the SQL acounts from the servers admi
n
>group before you test it.
>Regards
>Steen
>Libor Forejtnik wrote:
>

Access denied starting SQL Server after joining to new Windows domain

Hi, I made following steps:
1. Remove the SQL Server 2000 computer (W2k3) from old Windows domain
2. The same computer joined to new Windows domain (newdomain)
3. On the Domain Controler (W2k3) I create an domain account
SQLLaunch, which is a member of Domain Users group.
4. On the SQL Server 2003 computer I changed the SQL Server service
startup account to SQLStart@.newdomain account. The same thing made I
with the SQL Server Agent Service. Both the SQL Server Service and SQL
Server Agent start automatically when OS starts.
after these steps I tried to start the SQL Server service, but the
SQL Server did not start. Error message dialog appears as follows:
"Could not start the MSSQLSERVER service on Local Computer
Error 5: Access denied"
After I login to the SQL Server computer using SQLStart@.newdomain
account and try to start the sqlservr.exe in SQL Server home directory
similar error dialog appears:
"Access denied"
first after I made the SQLStart@.newdomain account a member of
the local Administrator group on ther SQL Server computer, everything
is going well.
What can I do to avoid to grant the Administrator's rights to the
SQLStart@.newdomain (in order to
minimize risk of exploits - it is not the right way to grant the
Administrator's rights). The desire is to use a domain account
without any Administration rights. Can anyone help me?
thanks
Libor
I don't think that the account needs full admin rights to the server, but it
needs the "log on as a service" rights. When you set the account that runs
the service, the OS will automatically grant this right. I'd expect though
that you need to be logged on with an account that has the rights to grant
this rights to do it. In other words, try to log on the the server as
Administrator (or another account with admin rights) and then set the
account for SQLSERVER and SQLServerAgent service. Then the account should be
granted rights to log on as a service. Then try to restart the server (or
just the service). Remember to remove the SQL acounts from the servers admin
group before you test it.
Regards
Steen
Libor Forejtnik wrote:
> Hi, I made following steps:
> 1. Remove the SQL Server 2000 computer (W2k3) from old Windows domain
> 2. The same computer joined to new Windows domain (newdomain)
> 3. On the Domain Controler (W2k3) I create an domain account
> SQLLaunch, which is a member of Domain Users group.
> 4. On the SQL Server 2003 computer I changed the SQL Server service
> startup account to SQLStart@.newdomain account. The same thing made I
> with the SQL Server Agent Service. Both the SQL Server Service and SQL
> Server Agent start automatically when OS starts.
> after these steps I tried to start the SQL Server service, but the
> SQL Server did not start. Error message dialog appears as follows:
> "Could not start the MSSQLSERVER service on Local Computer
> Error 5: Access denied"
> After I login to the SQL Server computer using SQLStart@.newdomain
> account and try to start the sqlservr.exe in SQL Server home directory
> similar error dialog appears:
> "Access denied"
> first after I made the SQLStart@.newdomain account a member of
> the local Administrator group on ther SQL Server computer, everything
> is going well.
> What can I do to avoid to grant the Administrator's rights to the
> SQLStart@.newdomain (in order to
> minimize risk of exploits - it is not the right way to grant the
> Administrator's rights). The desire is to use a domain account
> without any Administration rights. Can anyone help me?
> thanks
> Libor
|||Thank for this usefull advice. There is another way to solve the
problem too. Simply reinstall the server with the option
"registry rebuild". I guess, it makes the same things as You
recomended. In addition, the startup account must be
registered as a SQL Server login.
Unfortunatelly, starting the SQLServerAgent needs to have the startup
account a member of the SQL Server System Administration group.
But this is not a critical for me.
Libor
On Wed, 9 Mar 2005 10:08:37 +0100, "Steen Persson"
<SPE@.REMOVEdatea.dk> wrote:

>I don't think that the account needs full admin rights to the server, but it
>needs the "log on as a service" rights. When you set the account that runs
>the service, the OS will automatically grant this right. I'd expect though
>that you need to be logged on with an account that has the rights to grant
>this rights to do it. In other words, try to log on the the server as
>Administrator (or another account with admin rights) and then set the
>account for SQLSERVER and SQLServerAgent service. Then the account should be
>granted rights to log on as a service. Then try to restart the server (or
>just the service). Remember to remove the SQL acounts from the servers admin
>group before you test it.
>Regards
>Steen
>Libor Forejtnik wrote:
>

Access denied starting SQL Server after joining to new Windows domain

Hi, I made following steps:
1. Remove the SQL Server 2000 computer (W2k3) from old Windows domain
2. The same computer joined to new Windows domain (newdomain)
3. On the Domain Controler (W2k3) I create an domain account
SQLLaunch, which is a member of Domain Users group.
4. On the SQL Server 2003 computer I changed the SQL Server service
startup account to SQLStart@.newdomain account. The same thing made I
with the SQL Server Agent Service. Both the SQL Server Service and SQL
Server Agent start automatically when OS starts.
after these steps I tried to start the SQL Server service, but the
SQL Server did not start. Error message dialog appears as follows:
"Could not start the MSSQLSERVER service on Local Computer
Error 5: Access denied"
After I login to the SQL Server computer using SQLStart@.newdomain
account and try to start the sqlservr.exe in SQL Server home directory
similar error dialog appears:
"Access denied"
first after I made the SQLStart@.newdomain account a member of
the local Administrator group on ther SQL Server computer, everything
is going well.
What can I do to avoid to grant the Administrator's rights to the
SQLStart@.newdomain (in order to
minimize risk of exploits - it is not the right way to grant the
Administrator's rights). The desire is to use a domain account
without any Administration rights. Can anyone help me?
thanks
LiborHello Libor,
From your very detailed discription I believe your error is nothing to with
SQL Server, and everything to do with permission on the Server. The reason I
believe this is that firstly the errors your getting 'Access Denied' is not a
SQL Server error, but a Server 2003 error, secondly by putting into your
local administration group you can do it.
This is what I would do to check this out.
NB you will need down time for this...
1. Take the domain id out of the Local Administrators
2. By hand try to access the directories you Server Server lives in,
followed by the Log and data files.
If you get an Access Denied message then you will need to modify the access
rights of the directory to include your new domain account.
Peter
"Cauliflower is nothing but cabbage with a college education."
Mark Twain
"Libor Forejtnik" wrote:
> Hi, I made following steps:
> 1. Remove the SQL Server 2000 computer (W2k3) from old Windows domain
> 2. The same computer joined to new Windows domain (newdomain)
> 3. On the Domain Controler (W2k3) I create an domain account
> SQLLaunch, which is a member of Domain Users group.
> 4. On the SQL Server 2003 computer I changed the SQL Server service
> startup account to SQLStart@.newdomain account. The same thing made I
> with the SQL Server Agent Service. Both the SQL Server Service and SQL
> Server Agent start automatically when OS starts.
> after these steps I tried to start the SQL Server service, but the
> SQL Server did not start. Error message dialog appears as follows:
> "Could not start the MSSQLSERVER service on Local Computer
> Error 5: Access denied"
> After I login to the SQL Server computer using SQLStart@.newdomain
> account and try to start the sqlservr.exe in SQL Server home directory
> similar error dialog appears:
> "Access denied"
> first after I made the SQLStart@.newdomain account a member of
> the local Administrator group on ther SQL Server computer, everything
> is going well.
> What can I do to avoid to grant the Administrator's rights to the
> SQLStart@.newdomain (in order to
> minimize risk of exploits - it is not the right way to grant the
> Administrator's rights). The desire is to use a domain account
> without any Administration rights. Can anyone help me?
> thanks
> Libor
>|||I don't think that the account needs full admin rights to the server, but it
needs the "log on as a service" rights. When you set the account that runs
the service, the OS will automatically grant this right. I'd expect though
that you need to be logged on with an account that has the rights to grant
this rights to do it. In other words, try to log on the the server as
Administrator (or another account with admin rights) and then set the
account for SQLSERVER and SQLServerAgent service. Then the account should be
granted rights to log on as a service. Then try to restart the server (or
just the service). Remember to remove the SQL acounts from the servers admin
group before you test it.
Regards
Steen
Libor Forejtnik wrote:
> Hi, I made following steps:
> 1. Remove the SQL Server 2000 computer (W2k3) from old Windows domain
> 2. The same computer joined to new Windows domain (newdomain)
> 3. On the Domain Controler (W2k3) I create an domain account
> SQLLaunch, which is a member of Domain Users group.
> 4. On the SQL Server 2003 computer I changed the SQL Server service
> startup account to SQLStart@.newdomain account. The same thing made I
> with the SQL Server Agent Service. Both the SQL Server Service and SQL
> Server Agent start automatically when OS starts.
> after these steps I tried to start the SQL Server service, but the
> SQL Server did not start. Error message dialog appears as follows:
> "Could not start the MSSQLSERVER service on Local Computer
> Error 5: Access denied"
> After I login to the SQL Server computer using SQLStart@.newdomain
> account and try to start the sqlservr.exe in SQL Server home directory
> similar error dialog appears:
> "Access denied"
> first after I made the SQLStart@.newdomain account a member of
> the local Administrator group on ther SQL Server computer, everything
> is going well.
> What can I do to avoid to grant the Administrator's rights to the
> SQLStart@.newdomain (in order to
> minimize risk of exploits - it is not the right way to grant the
> Administrator's rights). The desire is to use a domain account
> without any Administration rights. Can anyone help me?
> thanks
> Libor|||Thank for this usefull advice. There is another way to solve the
problem too. Simply reinstall the server with the option
"registry rebuild". I guess, it makes the same things as You
recomended. In addition, the startup account must be
registered as a SQL Server login.
Unfortunatelly, starting the SQLServerAgent needs to have the startup
account a member of the SQL Server System Administration group.
But this is not a critical for me.
Libor
On Wed, 9 Mar 2005 10:08:37 +0100, "Steen Persson"
<SPE@.REMOVEdatea.dk> wrote:
>I don't think that the account needs full admin rights to the server, but it
>needs the "log on as a service" rights. When you set the account that runs
>the service, the OS will automatically grant this right. I'd expect though
>that you need to be logged on with an account that has the rights to grant
>this rights to do it. In other words, try to log on the the server as
>Administrator (or another account with admin rights) and then set the
>account for SQLSERVER and SQLServerAgent service. Then the account should be
>granted rights to log on as a service. Then try to restart the server (or
>just the service). Remember to remove the SQL acounts from the servers admin
>group before you test it.
>Regards
>Steen
>Libor Forejtnik wrote:
>> Hi, I made following steps:
>> 1. Remove the SQL Server 2000 computer (W2k3) from old Windows domain
>> 2. The same computer joined to new Windows domain (newdomain)
>> 3. On the Domain Controler (W2k3) I create an domain account
>> SQLLaunch, which is a member of Domain Users group.
>> 4. On the SQL Server 2003 computer I changed the SQL Server service
>> startup account to SQLStart@.newdomain account. The same thing made I
>> with the SQL Server Agent Service. Both the SQL Server Service and SQL
>> Server Agent start automatically when OS starts.
>> after these steps I tried to start the SQL Server service, but the
>> SQL Server did not start. Error message dialog appears as follows:
>> "Could not start the MSSQLSERVER service on Local Computer
>> Error 5: Access denied"
>> After I login to the SQL Server computer using SQLStart@.newdomain
>> account and try to start the sqlservr.exe in SQL Server home directory
>> similar error dialog appears:
>> "Access denied"
>> first after I made the SQLStart@.newdomain account a member of
>> the local Administrator group on ther SQL Server computer, everything
>> is going well.
>> What can I do to avoid to grant the Administrator's rights to the
>> SQLStart@.newdomain (in order to
>> minimize risk of exploits - it is not the right way to grant the
>> Administrator's rights). The desire is to use a domain account
>> without any Administration rights. Can anyone help me?
>> thanks
>> Libor
>|||Thanks for the advice. I solved the problem by reinstalling the SQL
Server using the "Registry rebuild" option.
Libor
On Tue, 8 Mar 2005 08:41:05 -0800, Peter 'Not Peter The Spate' Nolan
<PeterNotPeterTheSpateNolan@.discussions.microsoft.com> wrote:
>Hello Libor,
>From your very detailed discription I believe your error is nothing to with
>SQL Server, and everything to do with permission on the Server. The reason I
>believe this is that firstly the errors your getting 'Access Denied' is not a
>SQL Server error, but a Server 2003 error, secondly by putting into your
>local administration group you can do it.
>This is what I would do to check this out.
>NB you will need down time for this...
>1. Take the domain id out of the Local Administrators
>2. By hand try to access the directories you Server Server lives in,
>followed by the Log and data files.
>If you get an Access Denied message then you will need to modify the access
>rights of the directory to include your new domain account.
>Peter
>"Cauliflower is nothing but cabbage with a college education."
>Mark Twain
>"Libor Forejtnik" wrote:
>> Hi, I made following steps:
>> 1. Remove the SQL Server 2000 computer (W2k3) from old Windows domain
>> 2. The same computer joined to new Windows domain (newdomain)
>> 3. On the Domain Controler (W2k3) I create an domain account
>> SQLLaunch, which is a member of Domain Users group.
>> 4. On the SQL Server 2003 computer I changed the SQL Server service
>> startup account to SQLStart@.newdomain account. The same thing made I
>> with the SQL Server Agent Service. Both the SQL Server Service and SQL
>> Server Agent start automatically when OS starts.
>> after these steps I tried to start the SQL Server service, but the
>> SQL Server did not start. Error message dialog appears as follows:
>> "Could not start the MSSQLSERVER service on Local Computer
>> Error 5: Access denied"
>> After I login to the SQL Server computer using SQLStart@.newdomain
>> account and try to start the sqlservr.exe in SQL Server home directory
>> similar error dialog appears:
>> "Access denied"
>> first after I made the SQLStart@.newdomain account a member of
>> the local Administrator group on ther SQL Server computer, everything
>> is going well.
>> What can I do to avoid to grant the Administrator's rights to the
>> SQLStart@.newdomain (in order to
>> minimize risk of exploits - it is not the right way to grant the
>> Administrator's rights). The desire is to use a domain account
>> without any Administration rights. Can anyone help me?
>> thanks
>> Libor