Query Design in SQL 2000

Hello everyone,
I need to convert Access 2000 dbs (just tables) to SQL
2000. I am in the process of learning how to work with
tables (import,delete,append,update..etc) I am new at SQL
and like many of us, with little or none time to spend
trying to figure out the online help. So I decided to
write to you asking for help. A few basic but important
questions:
Is there a feature in SQL that allows me to graphically
manipulate tables? Just like with queries in Access...or
do I need to learn SQL language to do so?
How do I set the relations among my tables?
Where do I look for help to write DPS packages to import
csv files and update my tables?
Once I am done moving everything to SQL..is it worth it
to link this tables to Access and keep working with it as
usual?...or it would be detrimental in terms of speed?
The only reason I upgrade to SQL is because my dbs grew
too much for Access to handle and I need to build a Data
Warehouse.
I thank in advance to anybody who can answer all or any
of my questions...or maybe just guide me a little.
Gustavo
(ex-Access-wizard now SQL-dumb)
For imports/export Access > SQL Server,
look into the DTS (data transfer services) in
the manager under Tools,
For Access like Tables, Right click on your
table and "Design"
Quick relationships can be made Access-like
by putting your tables into a "Diagram" and
you can link tables together.
It is amazing how little SQL you really have
to know to use SQL server. It is almost like
Access but all grown up...
I'm sure you'll get other tips/tricks from this
message.
Bob M.
"Gustavo" <anonymous@.discussions.microsoft.com> wrote in message
news:282ed01c46396$28707780$a301280a@.phx.gbl...
> Hello everyone,
> I need to convert Access 2000 dbs (just tables) to SQL
> 2000. I am in the process of learning how to work with
> tables (import,delete,append,update..etc) I am new at SQL
> and like many of us, with little or none time to spend
> trying to figure out the online help. So I decided to
> write to you asking for help. A few basic but important
> questions:
> Is there a feature in SQL that allows me to graphically
> manipulate tables? Just like with queries in Access...or
> do I need to learn SQL language to do so?
> How do I set the relations among my tables?
> Where do I look for help to write DPS packages to import
> csv files and update my tables?
> Once I am done moving everything to SQL..is it worth it
> to link this tables to Access and keep working with it as
> usual?...or it would be detrimental in terms of speed?
> The only reason I upgrade to SQL is because my dbs grew
> too much for Access to handle and I need to build a Data
> Warehouse.
> I thank in advance to anybody who can answer all or any
> of my questions...or maybe just guide me a little.
> Gustavo
> (ex-Access-wizard now SQL-dumb)
>
|||Thanks a lot for the tips Bob. It certainly is the Access
big brother
>--Original Message--
>For imports/export Access > SQL Server,
>look into the DTS (data transfer services) in
>the manager under Tools,
>For Access like Tables, Right click on your
>table and "Design"
>Quick relationships can be made Access-like
>by putting your tables into a "Diagram" and
>you can link tables together.
>It is amazing how little SQL you really have
>to know to use SQL server. It is almost like
>Access but all grown up...
>I'm sure you'll get other tips/tricks from this
>message.
>Bob M.
>
>"Gustavo" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:282ed01c46396$28707780$a301280a@.phx.gbl...
SQL[vbcol=seagreen]
Access...or[vbcol=seagreen]
import[vbcol=seagreen]
as[vbcol=seagreen]
Data
>
>.
>

Query blocked by negative spid

I'm running SQL 2K SP3, and I have a query that hangs and when I check the
sysprocesses table, the process is being blocked by spid -2. Also, when I
run sp_lock, there are several locks on the table I'm querying that have a
spid of -2. There are no errors in the errorlog.
There is no process with a spid = -2, so I don't have anything to kill. How
can I get rid of this without rebooting the SQL Server?
>
> I'm running SQL 2K SP3, and I have a query that hangs and when I check
the
> sysprocesses table, the process is being blocked by spid -2. Also, when
I
> run sp_lock, there are several locks on the table I'm querying that have
a
> spid of -2. There are no errors in the errorlog.
> There is no process with a spid = -2, so I don't have anything to kill.
How
> can I get rid of this without rebooting the SQL Server?
From SQL Server Books Online:
In SQL Server 2000, all orphaned DTC transactions are assigned the SPID
value of '-2'. Orphaned DTC transactions are distributed transactions that
are not associated with any SPID. Thus, when an orphaned transaction is
blocking another process, this orphaned distributed transaction can be
identified by its distinctive '-2' SPID value. For more information, see
KILL.
UOW
Identifies the Unit of Work ID (UOW) of the DTC transaction. UOW is a
character string that may be obtained from the syslockinfo table, which
gives the UOW for every lock held by a DTC transaction. UOW also may be
obtained from the error log or through the DTC monitor. For more
information on monitoring distributed transactions, see the MS DTC user
manual.
Use KILL UOW to terminate orphaned DTC transactions, which are not
associated with any real SPID and instead are associated artificially with
SPID = '-2'. For more information on SPID = '-2', see the Remarks section
later in this topic.
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

Query blocked by negative spid

I'm running SQL 2K SP3, and I have a query that hangs and when I check the
sysprocesses table, the process is being blocked by spid -2. Also, when I
run sp_lock, there are several locks on the table I'm querying that have a
spid of -2. There are no errors in the errorlog.
There is no process with a spid = -2, so I don't have anything to kill. How
can I get rid of this without rebooting the SQL Server?>
> I'm running SQL 2K SP3, and I have a query that hangs and when I check
the
> sysprocesses table, the process is being blocked by spid -2. Also, when
I
> run sp_lock, there are several locks on the table I'm querying that have
a
> spid of -2. There are no errors in the errorlog.
> There is no process with a spid = -2, so I don't have anything to kill.
How
> can I get rid of this without rebooting the SQL Server?
--
From SQL Server Books Online:
In SQL Server 2000, all orphaned DTC transactions are assigned the SPID
value of '-2'. Orphaned DTC transactions are distributed transactions that
are not associated with any SPID. Thus, when an orphaned transaction is
blocking another process, this orphaned distributed transaction can be
identified by its distinctive '-2' SPID value. For more information, see
KILL.
UOW
Identifies the Unit of Work ID (UOW) of the DTC transaction. UOW is a
character string that may be obtained from the syslockinfo table, which
gives the UOW for every lock held by a DTC transaction. UOW also may be
obtained from the error log or through the DTC monitor. For more
information on monitoring distributed transactions, see the MS DTC user
manual.
Use KILL UOW to terminate orphaned DTC transactions, which are not
associated with any real SPID and instead are associated artificially with
SPID = '-2'. For more information on SPID = '-2', see the Remarks section
later in this topic.
Hope this helps,
Eric Cárdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

Query blocked by negative spid

I'm running SQL 2K SP3, and I have a query that hangs and when I check the
sysprocesses table, the process is being blocked by spid -2. Also, when I
run sp_lock, there are several locks on the table I'm querying that have a
spid of -2. There are no errors in the errorlog.
There is no process with a spid = -2, so I don't have anything to kill. How
can I get rid of this without rebooting the SQL Server?>
> I'm running SQL 2K SP3, and I have a query that hangs and when I check
the
> sysprocesses table, the process is being blocked by spid -2. Also, when
I
> run sp_lock, there are several locks on the table I'm querying that have
a
> spid of -2. There are no errors in the errorlog.
> There is no process with a spid = -2, so I don't have anything to kill.
How
> can I get rid of this without rebooting the SQL Server?
--
From SQL Server Books Online:
In SQL Server 2000, all orphaned DTC transactions are assigned the SPID
value of '-2'. Orphaned DTC transactions are distributed transactions that
are not associated with any SPID. Thus, when an orphaned transaction is
blocking another process, this orphaned distributed transaction can be
identified by its distinctive '-2' SPID value. For more information, see
KILL.
UOW
Identifies the Unit of Work ID (UOW) of the DTC transaction. UOW is a
character string that may be obtained from the syslockinfo table, which
gives the UOW for every lock held by a DTC transaction. UOW also may be
obtained from the error log or through the DTC monitor. For more
information on monitoring distributed transactions, see the MS DTC user
manual.
Use KILL UOW to terminate orphaned DTC transactions, which are not
associated with any real SPID and instead are associated artificially with
SPID = '-2'. For more information on SPID = '-2', see the Remarks section
later in this topic.
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

Query and process performace with incremental update

hello all,

we are working on a project with a large scale of data (around 1000 rows per second).
we built a cube on this fact table.

this table will hold at most 90M rows.

we need the data in the cube to be "real time", that mean, up to date.

we are doing it by proactive caching- incremental update.

we also need a very good query performance.

that's why the storage mode is set to MOLAP.

we still get a low performace from the cube process and and the querys.

any suggestions how to solve this issues?

Thanks in advance,

Shy Engelberg - Certagon.

You might be seeing the results of the meta data locking (see http://geekswithblogs.net/darrengosbell/archive/2007/04/24/SSAS-Processing-ForceCommitTimeout-and-quotthe-operation-has-been-cancelledquot.aspx) If you are processing the cube very frequently. You probably need to profile the server to gather as much information as you can to figure out where the issues are.

Is it on the source system - selected only new records?

Is the system CPU, IO or memory bound?

Are you using partitions to isolate the processing to a smaller subset of the data?

|||

The SSAS 2005 Performance Guide is a good reference for these kinds of issues ( http://download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b2011c/SSAS2005PerfGuide.doc).

I agree with Darren's idea of identifying whether the problem is occuring in retreiving source data records or in assembling the MOLAP structures. And partitioning may also be beneficial if you can isolate updates to a smaller partition.

You may also want to consider using HOLAP. HOLAP will give you excellent query performance for most queries with shorter processing times.

Bryan