Help on Performance with growing database

Hi,
I'm using SQL 2000. I have a simple database as backend for warehouse to
scan the products using access as front end. It includes 2 tables: order and
product.
Since everyday we have about 3000 orders to come in, each order has a few
products. The database is growing fast.
I can see the scanning process is a bit of slow at the front end. So I kept
current year data in both tables, and moved older data (3 years) into
historical tables within the same database.
Now, the active tables are smaller, do I need to do trunk database since I
deleted records from the active tables? Would this make it better? I don't
know if I can do trunk one table instead of the whole database?
If anyone has better idea regarding this case, it will be welcomed.
Thanks
SarahI think you did the right thing by archiving the old data to historical
tables. Partitioning the data like that will decrease I/O.
You could go a step further and place the data on a separate set of physical
disks if you need to access the archive data concurrently with the "active"
data.
I couldn't tell from your description which table the scanning (I'm assuming
you were talking about a "scanner" and not referring to table/index scans)
was accessing. But either way it appears that you're referring to a read
operation. In this case you may want to investigate your indexes to see if
you can find a better index for the query. Use Query Analyzer and look at
the execution plan on the query and verify that it is using the index you
are expecting. Also, you can call SET IO_STATISTICS ON before the query runs
and check the output to look at scans and logical reads. This will help you
determine if your optimization efforts are improving things or making it
worse.
Mark
"Sarah" <sguo@.coopervision.com> wrote in message
news:uBPMh4$rGHA.4264@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I'm using SQL 2000. I have a simple database as backend for warehouse to
> scan the products using access as front end. It includes 2 tables: order
> and product.
> Since everyday we have about 3000 orders to come in, each order has a few
> products. The database is growing fast.
> I can see the scanning process is a bit of slow at the front end. So I
> kept current year data in both tables, and moved older data (3 years) into
> historical tables within the same database.
> Now, the active tables are smaller, do I need to do trunk database since I
> deleted records from the active tables? Would this make it better? I don't
> know if I can do trunk one table instead of the whole database?
> If anyone has better idea regarding this case, it will be welcomed.
> Thanks
> Sarah
>|||Hi Mark,
Thanks for your response. Since our users don't inquiry historical data very
often, can I leave them on the same disk?
Also what I meant scanning is that user uses scanner to collect the barcode
into the system. The user does read and write on both tables.
I'm not sure if "Trunk database" make it better?
Also I found "tempdb" is growing than it should be, is that because I've
been doing queries in Query Analyzer on daily basis? I checked the
properties, allow auto grow at 10%. How can I tell the grow step for
"tempdb"?
Thanks a lot,
Sarah
"Mark Miller" <mark_n_0-s*p..am@.maxpreps.com> wrote in message
news:epNlypAsGHA.4608@.TK2MSFTNGP04.phx.gbl...
>I think you did the right thing by archiving the old data to historical
>tables. Partitioning the data like that will decrease I/O.
> You could go a step further and place the data on a separate set of
> physical disks if you need to access the archive data concurrently with
> the "active" data.
> I couldn't tell from your description which table the scanning (I'm
> assuming you were talking about a "scanner" and not referring to
> table/index scans) was accessing. But either way it appears that you're
> referring to a read operation. In this case you may want to investigate
> your indexes to see if you can find a better index for the query. Use
> Query Analyzer and look at the execution plan on the query and verify that
> it is using the index you are expecting. Also, you can call SET
> IO_STATISTICS ON before the query runs and check the output to look at
> scans and logical reads. This will help you determine if your optimization
> efforts are improving things or making it worse.
> Mark
>
> "Sarah" <sguo@.coopervision.com> wrote in message
> news:uBPMh4$rGHA.4264@.TK2MSFTNGP04.phx.gbl...
>|||Sarah wrote:
> Hi Mark,
> Thanks for your response. Since our users don't inquiry historical data ve
ry
> often, can I leave them on the same disk?
> Also what I meant scanning is that user uses scanner to collect the barcod
e
> into the system. The user does read and write on both tables.
> I'm not sure if "Trunk database" make it better?
> Also I found "tempdb" is growing than it should be, is that because I've
> been doing queries in Query Analyzer on daily basis? I checked the
> properties, allow auto grow at 10%. How can I tell the grow step for
> "tempdb"?
>
It sounds like you have poorly written queries running against the
database. With proper indexes and efficient coding, your tables should
be able to handle literally MILLIONS of rows. High tempdb utilization
indicates the use of temp tables or large ordering/grouping operations.
I would begin here:
http://www.sql-server-performance.c...mance_audit.asp
Use Profiler to identify the query that produces the highest number of
reads over an hour timespan, preferably a busy time of day. Determine
why that query produces the load that it does, and focus on optimizing
it. Repeat this process until your performance has improved.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Sarah,
Yes, you can leave the tables on the same disk. If by "Trunk database" you
want to shrink the database, no
that won't do anything other than reduce the current size of the db once
you've removed the historical data. So
unless disk space is an issue it won't do much for you. However, after
removing so many records from the table you might benefit from a either a
defrag or a rebuild on your indexes.
Tracy makes a valid point as well, you might want run profiler to look for
the offending query. My suggestions before we made under the assumption that
you already knew which query was slow based on the fact that you mentioned
the database only had 2 tables. I assumed that meant you probably didn't
have too many queries to weed through. But you should definately check the
query and verify the execution plan to make sure it is taking advantage of
your indexes. You want to see index seeks. If it is doing table scans check
your query. If you are seeing index scans it could still be the queries but
you'll want to start looking at the index and make sure they are optimal.
Mark

Help on Performance with growing database

Hi,
I'm using SQL 2000. I have a simple database as backend for warehouse to
scan the products using access as front end. It includes 2 tables: order and
product.
Since everyday we have about 3000 orders to come in, each order has a few
products. The database is growing fast.
I can see the scanning process is a bit of slow at the front end. So I kept
current year data in both tables, and moved older data (3 years) into
historical tables within the same database.
Now, the active tables are smaller, do I need to do trunk database since I
deleted records from the active tables? Would this make it better? I don't
know if I can do trunk one table instead of the whole database?
If anyone has better idea regarding this case, it will be welcomed.
Thanks
SarahI think you did the right thing by archiving the old data to historical
tables. Partitioning the data like that will decrease I/O.
You could go a step further and place the data on a separate set of physical
disks if you need to access the archive data concurrently with the "active"
data.
I couldn't tell from your description which table the scanning (I'm assuming
you were talking about a "scanner" and not referring to table/index scans)
was accessing. But either way it appears that you're referring to a read
operation. In this case you may want to investigate your indexes to see if
you can find a better index for the query. Use Query Analyzer and look at
the execution plan on the query and verify that it is using the index you
are expecting. Also, you can call SET IO_STATISTICS ON before the query runs
and check the output to look at scans and logical reads. This will help you
determine if your optimization efforts are improving things or making it
worse.
Mark
"Sarah" <sguo@.coopervision.com> wrote in message
news:uBPMh4$rGHA.4264@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I'm using SQL 2000. I have a simple database as backend for warehouse to
> scan the products using access as front end. It includes 2 tables: order
> and product.
> Since everyday we have about 3000 orders to come in, each order has a few
> products. The database is growing fast.
> I can see the scanning process is a bit of slow at the front end. So I
> kept current year data in both tables, and moved older data (3 years) into
> historical tables within the same database.
> Now, the active tables are smaller, do I need to do trunk database since I
> deleted records from the active tables? Would this make it better? I don't
> know if I can do trunk one table instead of the whole database?
> If anyone has better idea regarding this case, it will be welcomed.
> Thanks
> Sarah
>|||Hi Mark,
Thanks for your response. Since our users don't inquiry historical data very
often, can I leave them on the same disk?
Also what I meant scanning is that user uses scanner to collect the barcode
into the system. The user does read and write on both tables.
I'm not sure if "Trunk database" make it better?
Also I found "tempdb" is growing than it should be, is that because I've
been doing queries in Query Analyzer on daily basis? I checked the
properties, allow auto grow at 10%. How can I tell the grow step for
"tempdb"?
Thanks a lot,
Sarah
"Mark Miller" <mark_n_0-s*p..am@.maxpreps.com> wrote in message
news:epNlypAsGHA.4608@.TK2MSFTNGP04.phx.gbl...
>I think you did the right thing by archiving the old data to historical
>tables. Partitioning the data like that will decrease I/O.
> You could go a step further and place the data on a separate set of
> physical disks if you need to access the archive data concurrently with
> the "active" data.
> I couldn't tell from your description which table the scanning (I'm
> assuming you were talking about a "scanner" and not referring to
> table/index scans) was accessing. But either way it appears that you're
> referring to a read operation. In this case you may want to investigate
> your indexes to see if you can find a better index for the query. Use
> Query Analyzer and look at the execution plan on the query and verify that
> it is using the index you are expecting. Also, you can call SET
> IO_STATISTICS ON before the query runs and check the output to look at
> scans and logical reads. This will help you determine if your optimization
> efforts are improving things or making it worse.
> Mark
>
> "Sarah" <sguo@.coopervision.com> wrote in message
> news:uBPMh4$rGHA.4264@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> I'm using SQL 2000. I have a simple database as backend for warehouse to
>> scan the products using access as front end. It includes 2 tables: order
>> and product.
>> Since everyday we have about 3000 orders to come in, each order has a few
>> products. The database is growing fast.
>> I can see the scanning process is a bit of slow at the front end. So I
>> kept current year data in both tables, and moved older data (3 years)
>> into historical tables within the same database.
>> Now, the active tables are smaller, do I need to do trunk database since
>> I deleted records from the active tables? Would this make it better? I
>> don't know if I can do trunk one table instead of the whole database?
>> If anyone has better idea regarding this case, it will be welcomed.
>> Thanks
>> Sarah
>|||Sarah wrote:
> Hi Mark,
> Thanks for your response. Since our users don't inquiry historical data very
> often, can I leave them on the same disk?
> Also what I meant scanning is that user uses scanner to collect the barcode
> into the system. The user does read and write on both tables.
> I'm not sure if "Trunk database" make it better?
> Also I found "tempdb" is growing than it should be, is that because I've
> been doing queries in Query Analyzer on daily basis? I checked the
> properties, allow auto grow at 10%. How can I tell the grow step for
> "tempdb"?
>
It sounds like you have poorly written queries running against the
database. With proper indexes and efficient coding, your tables should
be able to handle literally MILLIONS of rows. High tempdb utilization
indicates the use of temp tables or large ordering/grouping operations.
I would begin here:
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Use Profiler to identify the query that produces the highest number of
reads over an hour timespan, preferably a busy time of day. Determine
why that query produces the load that it does, and focus on optimizing
it. Repeat this process until your performance has improved.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Sarah,
Yes, you can leave the tables on the same disk. If by "Trunk database" you
want to shrink the database, no
that won't do anything other than reduce the current size of the db once
you've removed the historical data. So
unless disk space is an issue it won't do much for you. However, after
removing so many records from the table you might benefit from a either a
defrag or a rebuild on your indexes.
Tracy makes a valid point as well, you might want run profiler to look for
the offending query. My suggestions before we made under the assumption that
you already knew which query was slow based on the fact that you mentioned
the database only had 2 tables. I assumed that meant you probably didn't
have too many queries to weed through. But you should definately check the
query and verify the execution plan to make sure it is taking advantage of
your indexes. You want to see index seeks. If it is doing table scans check
your query. If you are seeing index scans it could still be the queries but
you'll want to start looking at the index and make sure they are optimal.
Mark

Help on joining tables

Say I have three tables (just using Access for this one).

- inTheNews
- pressReleases
- events

Each for the most part, has identical columns. But, each also has one or two that are unique.

The primary key (unique identifier) in each is set to AutoNumber.

Is there a way to set it up so that the AutoNumber recognizes the numbers from the other tables and doesn't produce numbers identical to records in those tables. I would like to make sure each record in each of these tables has a unique identifier -- unique to all three tables.

Does that make sense?

Thanks.There isn't a well defined way to do this, although there are several different approaches that work reasonably well.

I think that the best way to handle this kind of problem is to create a "parent" table that has the common columns, with an autonumber as its primary key. Then create three sub-tables that have the unique columns for each of the sub-types, and create a (foreign key) relationship between these tables and the parent table.

This reduces redundancy, and it makes it easier to be sure that the autonumber values are only used by one of the sub-types. It isn't perfect, but it is probably the best you can do "out of the box".

-PatP

help on insert a record on sql server with identity column as key

Hi, All:
Please help. I use sql server as back end and Access 2003 as front end
(everything is DAO).
A table on SQL server has an identity column as the key.
We have trouble on adding records to this table using the following SQL.
strSQL = "INSERT INTO myTableOnSQLServer (A, B, C, D, E) SELECT A, B, C, D,
E FROM myTableonAccessLocal"
db.execute strSQL
The schema of the table "myTableOnSQLServer" and the schema of the table
"myTableonAccessLocal" are all the same except that the "myTableOnSQLServer"
has an identity column (ID). The key of the "myTableOnSQLServer" is "ID" and
the table "myTableonAccessLocal" does not have a key.
When we try to run the query, it gives errors indicating the key is violated
or missing.
Should I figure out the autonumber for it first and then add to the SQL
server table?
Many thanks,
HS
"Hongyu Sun" wrote:

> Hi, All:
> Please help. I use sql server as back end and Access 2003 as front end
> (everything is DAO).
> A table on SQL server has an identity column as the key.
> We have trouble on adding records to this table using the following SQL.
> strSQL = "INSERT INTO myTableOnSQLServer (A, B, C, D, E) SELECT A, B, C, D,
> E FROM myTableonAccessLocal"
> db.execute strSQL
> The schema of the table "myTableOnSQLServer" and the schema of the table
> "myTableonAccessLocal" are all the same except that the "myTableOnSQLServer"
> has an identity column (ID). The key of the "myTableOnSQLServer" is "ID" and
> the table "myTableonAccessLocal" does not have a key.
> When we try to run the query, it gives errors indicating the key is violated
> or missing.
> Should I figure out the autonumber for it first and then add to the SQL
> server table?
> Many thanks,
> HS
As a common an identity column generates values by itself. If you need to
insert values into an identity column use this command:
SET IDENTITY_INSERT myTableOnSQLServer ON
After the insert has been completed issue the following statement:
SET IDENTITY_INSERT myTableOnSQLServer OFF
Good luck

help on insert a record on sql server with identity column as key

Hi, All:
Please help. I use sql server as back end and Access 2003 as front end
(everything is DAO).
A table on SQL server has an identity column as the key.
We have trouble on adding records to this table using the following SQL.
strSQL = "INSERT INTO myTableOnSQLServer (A, B, C, D, E) SELECT A, B, C, D,
E FROM myTableonAccessLocal"
db.execute strSQL
The schema of the table "myTableOnSQLServer" and the schema of the table
"myTableonAccessLocal" are all the same except that the "myTableOnSQLServer"
has an identity column (ID). The key of the "myTableOnSQLServer" is "ID" and
the table "myTableonAccessLocal" does not have a key.
When we try to run the query, it gives errors indicating the key is violated
or missing.
Should I figure out the autonumber for it first and then add to the SQL
server table?
Many thanks,
HS"Hongyu Sun" wrote:

> Hi, All:
> Please help. I use sql server as back end and Access 2003 as front end
> (everything is DAO).
> A table on SQL server has an identity column as the key.
> We have trouble on adding records to this table using the following SQL.
> strSQL = "INSERT INTO myTableOnSQLServer (A, B, C, D, E) SELECT A, B, C, D
,
> E FROM myTableonAccessLocal"
> db.execute strSQL
> The schema of the table "myTableOnSQLServer" and the schema of the table
> "myTableonAccessLocal" are all the same except that the "myTableOnSQLServe
r"
> has an identity column (ID). The key of the "myTableOnSQLServer" is "ID" a
nd
> the table "myTableonAccessLocal" does not have a key.
> When we try to run the query, it gives errors indicating the key is violat
ed
> or missing.
> Should I figure out the autonumber for it first and then add to the SQL
> server table?
> Many thanks,
> HS
As a common an identity column generates values by itself. If you need to
insert values into an identity column use this command:
SET IDENTITY_INSERT myTableOnSQLServer ON
After the insert has been completed issue the following statement:
SET IDENTITY_INSERT myTableOnSQLServer OFF
Good lucksql

Help on data query

I have three really small tables, which gave me a lot of headache.
They reside in a MS Access database and a I am accessing them through
ADO.NET(VB).
Can't figure out how to make a "Select" query on them.
These are the tables:
1St - "Transactions"
TransactionID - primary key, autogen
TransactionNum - alphanumeric
TransactionDate - short date
Amount - number
CurrencyID - number - foreign key to Currencies
ForeginCurrencyAmount - number
CurrencyID - number - foreign key to Currencies
SenderName - text
SenderCountryID - number - foreign key to Countries
ReceiverName - text
ReceiverCountryID - number - foreign key to Countries
2nd table - "Currencies":
CurrencyID - primary key, autogen
CurrencyName - text
3rd table - "Countries":
CountryID - primary key, autogen
CountryName - Text
I need a query which shows the following:
TransactionDate
Amount
CurrencyName(for Amount)
ForeginCurrencyAmount
CurrencyName (for Foregin Amount)
CountryName (for Sender)
CountryName (for Receiver)
Any help will be greatly appreciatedPlease post proper DDL, so that we can create your objects. Some sample data
and expected results might also be of great help.
http://www.aspfaq.com/etiquette.asp?id=5006
ML
http://milambda.blogspot.com/|||Please see www.aspfaq.com/5006 for details on how you can post relevant
information for such problems.
Anith

Help obtain a window of rows from a table

Hi,
I have a client program in vb.net that access a SQL server database. Each
time the client program need some data it retrieve the whole table, so it is
pretty slow.
I wonder if it is possible, for the client, to retrieve only a window of
rows around the actual value he is using. That is, if he is actually in row
4000 he will retrieve from row 3000 to 5000 but not the complete table. If
this is possible what I need is something like:
1) The client send SQL-Server a string with the actual ordering and the ID
of the actual row.
2) SQL-Server order the table following the order specified in the string
send by the client.
3) Using the ordered table SQL-Server "find" the ID of the actual row.
4) SQL-Server return a number of rows before and after the Id of the actual
row (no idea how to do this).
Any help.
Thanks,
JamesHi James,
Yes - its basically paging.
The basics are this...
declare @.results table (
idrow int not null identity,
yourresultcol1...
yourresultcol2...
)
insert @.results ( yourresultscol1, yourresultscol2 )
select yourresultscol1, yourresultscol2
from table...
where ...
order by ...
select *
from @.results
where idrow between @.start and @.finish
I know its not a complete working example but does that give you enough
idea?
Tony
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"James" <info@.pricetech.es> wrote in message
news:OTkWPANHGHA.1628@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a client program in vb.net that access a SQL server database. Each
> time the client program need some data it retrieve the whole table, so it
> is pretty slow.
> I wonder if it is possible, for the client, to retrieve only a window of
> rows around the actual value he is using. That is, if he is actually in
> row 4000 he will retrieve from row 3000 to 5000 but not the complete
> table. If this is possible what I need is something like:
> 1) The client send SQL-Server a string with the actual ordering and the ID
> of the actual row.
> 2) SQL-Server order the table following the order specified in the string
> send by the client.
> 3) Using the ordered table SQL-Server "find" the ID of the actual row.
> 4) SQL-Server return a number of rows before and after the Id of the
> actual row (no idea how to do this).
> Any help.
> Thanks,
> James
>
>|||Sorry, i forgot to mention, in SQL 2005 its a whole lot easier.
We have the rownumber() function and cte that does it all for us - there are
some really useful examples in bol.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:uhnuUbNHGHA.1124@.TK2MSFTNGP10.phx.gbl...
> Hi James,
> Yes - its basically paging.
> The basics are this...
> declare @.results table (
> idrow int not null identity,
> yourresultcol1...
> yourresultcol2...
> )
> insert @.results ( yourresultscol1, yourresultscol2 )
> select yourresultscol1, yourresultscol2
> from table...
> where ...
> order by ...
> select *
> from @.results
> where idrow between @.start and @.finish
> I know its not a complete working example but does that give you enough
> idea?
> Tony
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "James" <info@.pricetech.es> wrote in message
> news:OTkWPANHGHA.1628@.TK2MSFTNGP12.phx.gbl...
>|||James
I think Tom Moreau had already answered the same or almost the same question
a few days ago. Pls search on internet
"James" <info@.pricetech.es> wrote in message
news:OTkWPANHGHA.1628@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a client program in vb.net that access a SQL server database. Each
> time the client program need some data it retrieve the whole table, so it
> is pretty slow.
> I wonder if it is possible, for the client, to retrieve only a window of
> rows around the actual value he is using. That is, if he is actually in
> row 4000 he will retrieve from row 3000 to 5000 but not the complete
> table. If this is possible what I need is something like:
> 1) The client send SQL-Server a string with the actual ordering and the ID
> of the actual row.
> 2) SQL-Server order the table following the order specified in the string
> send by the client.
> 3) Using the ordered table SQL-Server "find" the ID of the actual row.
> 4) SQL-Server return a number of rows before and after the Id of the
> actual row (no idea how to do this).
> Any help.
> Thanks,
> James
>
>|||James wrote:
> Hi,
> I have a client program in vb.net that access a SQL server database. Each
> time the client program need some data it retrieve the whole table, so it
is
> pretty slow.
> I wonder if it is possible, for the client, to retrieve only a window of
> rows around the actual value he is using. That is, if he is actually in ro
w
> 4000 he will retrieve from row 3000 to 5000 but not the complete table. If
> this is possible what I need is something like:
> 1) The client send SQL-Server a string with the actual ordering and the ID
> of the actual row.
> 2) SQL-Server order the table following the order specified in the string
> send by the client.
> 3) Using the ordered table SQL-Server "find" the ID of the actual row.
> 4) SQL-Server return a number of rows before and after the Id of the actua
l
> row (no idea how to do this).
> Any help.
> Thanks,
> James
Take a look at:
http://www.aspfaq.com/show.asp?id=2120
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Help needed: Granting Create table permisions on specific Schema Options

Having some trouble getting my head around setting access to specific
schemas- here's my problem:

I've created a specific schema that I only want certain users to
control

Problem: Even though I give them full access...the cannot create
tables under that schema...my code is below (flyer is the schema,
eflyerAdmin is the role, and eflyer is the user):

GRANT
ALTER,
CONTROL,
DELETE,
EXECUTE,
INSERT,
REFERENCES,
SELECT,
TAKE OWNERSHIP,
UPDATE,
VIEW DEFINITION
ON SCHEMA::flyer
TO eflyerAdmin
GO
-- Add an existing user to the role
EXEC sp_addrolemember N'eflyerAdmin', N'eflyer'gdev (paul.afamdi.okeke@.gmail.com) writes:

Quote:

Originally Posted by

Having some trouble getting my head around setting access to specific
schemas- here's my problem:
>
I've created a specific schema that I only want certain users to
control
>
>
Problem: Even though I give them full access...the cannot create
tables under that schema...my code is below (flyer is the schema,
eflyerAdmin is the role, and eflyer is the user):
>
GRANT
ALTER,
CONTROL,
DELETE,
EXECUTE,
INSERT,
REFERENCES,
SELECT,
TAKE OWNERSHIP,
UPDATE,
VIEW DEFINITION
ON SCHEMA::flyer
TO eflyerAdmin
GO
-- Add an existing user to the role
EXEC sp_addrolemember N'eflyerAdmin', N'eflyer'

You also need:

GRANT CREATE TABLE TO eflyerAdmin

And it's sufficient to grant CONTROL on the schema, since CONTROL implies
the rest.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Help needed with SQL Server.

Hi,
Whenever i try to connect to my localhost i get an error message telling me that it doesn't exist or access is denied ... This is strange because i had no problems with it up until yesterday ... I can't connect to my Localhost through SQL Server Service Manager ... When i go into Enteprise Manager i can't open localhost or view anything in it ... It just keeps telling me that it doesn't exist or access is denied ...
Thanks,
Jack.Try (local). Localhost is IIS|||I have tried local and that doesn't work . any ideas??

Help Needed ! , Data Migration from MS Access2003 to SQL Express 2005

Hi ,

I have a requirement to migrate the data from an existing MS Access database to a newly designed SQL Express 2005 database . Need less to say the table structures in both are totally different.I would like to know how can i handle a scenerio where i want to map table A in access to table B in SQL express (the schema of both different and the number of columns can vary too) , how do i migrate the data from table A in Access to Table B in SQL express using SSMA?

Also i would appreciate if some one can tell me is SSMA the right tool for this , or should i use the upsizing wizard of MS Access.If there is no change in schema between source and destination databases (more of upsizing) then the process is pretty straight forward , The constraint here is that the data needs to be migrated to a new schema where the column names and number of columns can vary between the source table and destination table.. I just need to migrate data only and no other objects.

Need Help!

Thanks

Mahesh

Hi Mahesh,

SSMA is a right tool for migration

http://www.microsoft.com/downloads/details.aspx?familyid=D842F8B4-C914-4AC7-B2F3-D25FFF4E24FB&displaylang=en and refer discussion at TechNet for troubleshooting http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?dg=microsoft.public.access.sqlupsizing&cat=en_us_FB980B1A-C148-9C19-560F-548B843A67AB&lang=en&cr=us

and refer this thread too http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1033679&SiteID=1

Hemantgiri S. Goswami

HELP MS Access moving to SQL and dont know how

I have been using MS Access for a long time. The last couple of months my sites have really picked up in business (good rankings) and we keep crashing the server (No sites on the server that use MS Access will run) We get the error

System.Data.OleDb.OleDbException: Unspecified error at System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr) at System.Data.OleDb.OleDbConnection.InitializeProvider()
at System.Data.OleDb.OleDbConnection.Open()
at DreamweaverCtrls.DataSet.DoInit()

Also I am finding out from some of my customers that their sites they can't use at times because of the error but the server hasn't crashed yet because others are up. Well I believe we are exceeding MS Access very badly because it crashes the server daily now and sometimes more then once a day.

So I am going to start moving my MS Access database to SQL but have NO CLUE how where to start etc or use SQL correctly. If people can point me in the right direction that would be very helpful. Everything I find online is people needing help but no solutions that work for me.

My new server is a windows server running ASP.Net 2.0 and to edit my SQL I have "ASP.Net Enterprise Manager" and Plesk for the server.

1. A good book to follow and easy to follow for SQL (connections / how tos) or website

2. Good examples or book on "ASP.Net Enterprise Manager"

3. How to convert access databases into SQL easy (some have over 50,000 listings)

4. how to convert Tab files into SQL easy.

Thank you so much for any help. Also I am using Dreamweaver for most of the edditing and connections. I know its not your favorite but I don't have the time to write everything by hand and it works good for what I do on the websites.

Thanks again,

Rusty

Rusty,

To easily convert your Access tables to SQL Express, set up an ODBC System DSN for the SQL Server. Then when in Access, right click on the tables, one at a time, and choose export. From there choose the ODBC option and select the system DSN you just set up. Your tables should transfer automatically.

Of course I am assuming you have SQL Express setup already.

|||

I have it setup but I can't get it connected by dreamweaver. I get the error

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied."

I haven't found anything on exactly how to get it connected yet and still doing the trial and error thing.. LOL Now my access 2003 works perfect my server on the server online but no luck with SQL yet

Help me with this error wmi task

i set up a WMI Reader Task which works perfectly fine.

But when i Use WMI event watcher i get this error.

if the problem is with access rights how do i need to set or how can i figure out if i have rights..

[WMI Event Watcher Task] Error: Watching for the Wql query caused the following system exception: "Invalid class". Check the query for errors or WMI connection for access rights/permissions.

What is the WQL query you are trying to execute and on what OS/architechture? Some WMI classes are available on Vista, Windows 2003, and not on XP, and vice versa.

Help me out

Hi all,

When you are Integrating data from Access to SQL SERVER 2005 suing SSIS. and get such an error, how do you go about it?

Ronald

This is the error

Error: 0xC0202009 at Data Flow Task, Project [40]: An OLE DB error has occurred. Error code: 0x80040E14.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 3, column 1. The destination column (ProjectId) is defined as NOT NULL.".

Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.

Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.

Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "Project" (40)" wrote 4 rows.

Warning: 0x80019002 at Data Flow Task: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

Task failed: Data Flow Task

Warning: 0x80019002 at Project: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "Project.dtsx" finished: Failure.

I have already told you here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=481569&SiteID=1

Please will you do us all a favour and not post the same thread 4 times!

-Jamie

Help me out

Hi all,

When you are Integrating data from Access to SQL SERVER 2005 suing SSIS. and get such an error, how do you go about it?

Ronald

I have already told you here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=481569&SiteID=1

Please will you do us all a favour and not post the same thread 4 times!

-Jamie