Determine table names and column names at runtime?

Hi

I was wondering if anyone has an idea of how we could find the table names and column names of the tables in our Sql server database at runtime/dynamically given our connection string? Please let me know.

Thanks.The only advice I can give is this: All your database's objects are stored in the sysobjects table. And then there's syscolumns and sysindexes (shouldn't that be sysindices?).

I would be suprised if there were not free libraries out there that make it easy to get information about sql objects. I don't know of one particularly. Anyone know of one? If not, that might make a cool community project.|||There is a stored procedure sp_tables that returns a list of tables.

There is a stored procedure sp_columns that returns a list of Columns.

These are better to use than the system tables, as they are documented and should not change in a way to break your code. Look them up in Books Online...|||Gravy!

Glad you're here.|||> There is a stored procedure sp_tables that returns a list of tables.

ah, forgot about that. I guess because I usually only use it to do an

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'whatever')

and an SP is no good there.... cheers for reminding me.|||In that case, you could/should use:

IF EXISTS (SELECT table_name FROM INFORMATION_SCHEMA.tables WHERE table_name = 'whatever')

Terri

Detaching and Attaching a database

Hi:
I need to detach the databse to change its location and then attach again.
If this database is a publisher in a replication, will detaching and
attaching affect anything? I don't think so but i just want to confirm it.
Thanks
You must remove all publications from the database and unpublish the
database to detach it.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:98E2311A-E92D-4041-ABDC-0ABA37C39D78@.microsoft.com...
> Hi:
> I need to detach the databse to change its location and then attach again.
> If this database is a publisher in a replication, will detaching and
> attaching affect anything? I don't think so but i just want to confirm
> it.
> Thanks
|||Thanks for replying. Can you please tell me how do I remove all publications
from the database and unpublish the database it? And after I attach it, how
do I put all all the publications back and publish the database again. Can
you please give me some instructions? Actually I am new to replication.
Thanks
Sohail
"Hilary Cotter" wrote:

> You must remove all publications from the database and unpublish the
> database to detach it.
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:98E2311A-E92D-4041-ABDC-0ABA37C39D78@.microsoft.com...
>
>

Detached database can't be attached back

Hi
I dettached a database in order to move it to another drive where I have
more space. Original database had 2 datafiles and 2 log files. A data file
and a log file in c:\program files\microsoft sqlserver\mssql\data, called
crn_data.mdf and crn_log.ldf respectively. The other datafile and log file in
d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
So, I detached the database using EM: disconnected all users and put the DB
(crn) on DBO-only mode, then successfully dettached. The I deleted the ldf in
the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
the other data file and the second log file.
When first tried to attach dastabase through EM it failed to verify all 4
files in the attach dialog. So I copied second log file to original location
with the same old name trying to fool EM. Tried again and it passes
validation and the OK button is enabled, but the process fails: Error 5171
file c:\program files\microsoft sql server\data\crn_log.ldf is not a primary
database file. Could not open new darabase 'crn'. CREATE DATABASE is aborted.
Device activation errror, The physical name 'c:\program files\microsoft sql
server\data\crn_log.ldf ' may be incorrect.
I've also tried:
sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf'
result:
Server: Msg 5171, Level 16, State 2, Line 1
C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
primary database file.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'crn'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
result: Server: Msg 5171, Level 16, State 2, Line 1
C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
primary database file.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'crn'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
'd:\mssql\data\crn_Log2_log.ldf'
result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
'd:\mssql\data\crn_Log2_log.ldf'
I've used the attach/detach process in the past with no problem at all.
Could somebody provide some advice on what could be going on?
Thanks in advance.
Percy
Sounds like you may have corrupted one of the files somehow. Did you take a
FULL backup before detaching? That is a must so you don't run into
situations such as this. Have a look here and see if this helps:
http://www.sqlservercentral.com/scri...p?scriptid=599
Andrew J. Kelly SQL MVP
"Percy Cabello" <Percy Cabello@.discussions.microsoft.com> wrote in message
news:952BA145-8E36-43FC-9010-BE2B0E5E14EA@.microsoft.com...
> Hi
> I dettached a database in order to move it to another drive where I have
> more space. Original database had 2 datafiles and 2 log files. A data file
> and a log file in c:\program files\microsoft sqlserver\mssql\data, called
> crn_data.mdf and crn_log.ldf respectively. The other datafile and log file
> in
> d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
> So, I detached the database using EM: disconnected all users and put the
> DB
> (crn) on DBO-only mode, then successfully dettached. The I deleted the ldf
> in
> the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
> the other data file and the second log file.
> When first tried to attach dastabase through EM it failed to verify all 4
> files in the attach dialog. So I copied second log file to original
> location
> with the same old name trying to fool EM. Tried again and it passes
> validation and the OK button is enabled, but the process fails: Error 5171
> file c:\program files\microsoft sql server\data\crn_log.ldf is not a
> primary
> database file. Could not open new darabase 'crn'. CREATE DATABASE is
> aborted.
> Device activation errror, The physical name 'c:\program files\microsoft
> sql
> server\data\crn_log.ldf ' may be incorrect.
> I've also tried:
> sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf'
> result:
> Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
> result: Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> I've used the attach/detach process in the past with no problem at all.
> Could somebody provide some advice on what could be going on?
> Thanks in advance.
> Percy
|||Are you saying that you deleted one of the log files, and then copied the second log file to the
first log files name and location? If so, you probably need to go the restore route, open a case or
try the last resort posted by Andrew. If not, well, your options are the same...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Percy Cabello" <Percy Cabello@.discussions.microsoft.com> wrote in message
news:952BA145-8E36-43FC-9010-BE2B0E5E14EA@.microsoft.com...
> Hi
> I dettached a database in order to move it to another drive where I have
> more space. Original database had 2 datafiles and 2 log files. A data file
> and a log file in c:\program files\microsoft sqlserver\mssql\data, called
> crn_data.mdf and crn_log.ldf respectively. The other datafile and log file in
> d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
> So, I detached the database using EM: disconnected all users and put the DB
> (crn) on DBO-only mode, then successfully dettached. The I deleted the ldf in
> the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
> the other data file and the second log file.
> When first tried to attach dastabase through EM it failed to verify all 4
> files in the attach dialog. So I copied second log file to original location
> with the same old name trying to fool EM. Tried again and it passes
> validation and the OK button is enabled, but the process fails: Error 5171
> file c:\program files\microsoft sql server\data\crn_log.ldf is not a primary
> database file. Could not open new darabase 'crn'. CREATE DATABASE is aborted.
> Device activation errror, The physical name 'c:\program files\microsoft sql
> server\data\crn_log.ldf ' may be incorrect.
> I've also tried:
> sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf'
> result:
> Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
> result: Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> I've used the attach/detach process in the past with no problem at all.
> Could somebody provide some advice on what could be going on?
> Thanks in advance.
> Percy

Detached database can't be attached back

Hi
I dettached a database in order to move it to another drive where I have
more space. Original database had 2 datafiles and 2 log files. A data file
and a log file in c:\program files\microsoft sqlserver\mssql\data, called
crn_data.mdf and crn_log.ldf respectively. The other datafile and log file i
n
d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
So, I detached the database using EM: disconnected all users and put the DB
(crn) on DBO-only mode, then successfully dettached. The I deleted the ldf i
n
the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
the other data file and the second log file.
When first tried to attach dastabase through EM it failed to verify all 4
files in the attach dialog. So I copied second log file to original location
with the same old name trying to fool EM. Tried again and it passes
validation and the OK button is enabled, but the process fails: Error 5171
file c:\program files\microsoft sql server\data\crn_log.ldf is not a primary
database file. Could not open new darabase 'crn'. CREATE DATABASE is aborted
.
Device activation errror, The physical name 'c:\program files\microsoft sql
server\data\crn_log.ldf ' may be incorrect.
I've also tried:
sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf'
result:
Server: Msg 5171, Level 16, State 2, Line 1
C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
primary database file.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'crn'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
result: Server: Msg 5171, Level 16, State 2, Line 1
C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
primary database file.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'crn'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
'd:\mssql\data\crn_Log2_log.ldf'
result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
'd:\mssql\data\crn_Log2_log.ldf'
I've used the attach/detach process in the past with no problem at all.
Could somebody provide some advice on what could be going on?
Thanks in advance.
PercySounds like you may have corrupted one of the files somehow. Did you take a
FULL backup before detaching? That is a must so you don't run into
situations such as this. Have a look here and see if this helps:
http://www.sqlservercentral.com/scr...sp?scriptid=599
Andrew J. Kelly SQL MVP
"Percy Cabello" <Percy Cabello@.discussions.microsoft.com> wrote in message
news:952BA145-8E36-43FC-9010-BE2B0E5E14EA@.microsoft.com...
> Hi
> I dettached a database in order to move it to another drive where I have
> more space. Original database had 2 datafiles and 2 log files. A data file
> and a log file in c:\program files\microsoft sqlserver\mssql\data, called
> crn_data.mdf and crn_log.ldf respectively. The other datafile and log file
> in
> d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
> So, I detached the database using EM: disconnected all users and put the
> DB
> (crn) on DBO-only mode, then successfully dettached. The I deleted the ldf
> in
> the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
> the other data file and the second log file.
> When first tried to attach dastabase through EM it failed to verify all 4
> files in the attach dialog. So I copied second log file to original
> location
> with the same old name trying to fool EM. Tried again and it passes
> validation and the OK button is enabled, but the process fails: Error 5171
> file c:\program files\microsoft sql server\data\crn_log.ldf is not a
> primary
> database file. Could not open new darabase 'crn'. CREATE DATABASE is
> aborted.
> Device activation errror, The physical name 'c:\program files\microsoft
> sql
> server\data\crn_log.ldf ' may be incorrect.
> I've also tried:
> sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf'
> result:
> Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
> result: Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> I've used the attach/detach process in the past with no problem at all.
> Could somebody provide some advice on what could be going on?
> Thanks in advance.
> Percy|||Are you saying that you deleted one of the log files, and then copied the se
cond log file to the
first log files name and location? If so, you probably need to go the restor
e route, open a case or
try the last resort posted by Andrew. If not, well, your options are the sam
e...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Percy Cabello" <Percy Cabello@.discussions.microsoft.com> wrote in message
news:952BA145-8E36-43FC-9010-BE2B0E5E14EA@.microsoft.com...
> Hi
> I dettached a database in order to move it to another drive where I have
> more space. Original database had 2 datafiles and 2 log files. A data file
> and a log file in c:\program files\microsoft sqlserver\mssql\data, called
> crn_data.mdf and crn_log.ldf respectively. The other datafile and log file
in
> d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
> So, I detached the database using EM: disconnected all users and put the D
B
> (crn) on DBO-only mode, then successfully dettached. The I deleted the ldf
in
> the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
> the other data file and the second log file.
> When first tried to attach dastabase through EM it failed to verify all 4
> files in the attach dialog. So I copied second log file to original locati
on
> with the same old name trying to fool EM. Tried again and it passes
> validation and the OK button is enabled, but the process fails: Error 5171
> file c:\program files\microsoft sql server\data\crn_log.ldf is not a prima
ry
> database file. Could not open new darabase 'crn'. CREATE DATABASE is abort
ed.
> Device activation errror, The physical name 'c:\program files\microsoft sq
l
> server\data\crn_log.ldf ' may be incorrect.
> I've also tried:
> sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf'
> result:
> Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program Files\Microsof
t
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
> result: Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program Files\Microsof
t
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> I've used the attach/detach process in the past with no problem at all.
> Could somebody provide some advice on what could be going on?
> Thanks in advance.
> Percy

detach db failed due to ghost cleanup

hi
i'm trying to detach a database. However i cant as the db is in use. I can
kill any user processes but not the non-user process 'ghost cleanup'. Becaus
e
of this the db is in use and the detach cannot occur.
I realise what the process 'ghost cleanup' is there for - but surely it
shouldn't be in use permanently? (db options such as shrink db are not set o
n
this db). There any safe way to disable'Try putting the database in single user mode by using the ALTER DATABSE with
SET SINGLE_USER mode option and then try detaching.
"Dunxz" wrote:

> hi
> i'm trying to detach a database. However i cant as the db is in use. I can
> kill any user processes but not the non-user process 'ghost cleanup'. Beca
use
> of this the db is in use and the detach cannot occur.
> I realise what the process 'ghost cleanup' is there for - but surely it
> shouldn't be in use permanently? (db options such as shrink db are not set
on
> this db). There any safe way to disable'
>|||Thanks
"Pragya" wrote:
[vbcol=seagreen]
> Try putting the database in single user mode by using the ALTER DATABSE wi
th
> SET SINGLE_USER mode option and then try detaching.
>
> "Dunxz" wrote:
>

desinstall MS SQL

Hi
I am installing Crystal entreprise, it finds MS SQL Server installed
and ask me my login and password. I found directory MS SQL in program
files but i couldnt find uninstall programm. So I delete directory in
MS SQL in program files. But Crystal Entreprise find again MS SQL on my
machine.
Could you help me please ?
Thanks

PS: Sorry for my french postSo first deleting the SQL Server directories is a problem, you should
deinstall that, if you cant find the uninstall it could be that you
dont have the appropiate permissions to execute the deinstaller (?!).

Is there a service running with the name MSSQLSERVER***** ?

HTH, Jens Suessmeyer|||(minigitoo@.aol.com) writes:
> I am installing Crystal entreprise, it finds MS SQL Server installed
> and ask me my login and password. I found directory MS SQL in program
> files but i couldnt find uninstall programm. So I delete directory in
> MS SQL in program files. But Crystal Entreprise find again MS SQL on my
> machine.

Not really sure why you would uninstall SQL Server just because you
install Crystal Enterprise. Anyway, use Add/Remove Programs in the
control panel to uninstall SQL Server. Just deleting the files does not
help you, as Crystal - as any other civilized piece of software looks in
the registry.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96CFF0C3CF1C2Yazorman@.127.0.0.1...
> (minigitoo@.aol.com) writes:
> > I am installing Crystal entreprise, it finds MS SQL Server installed
> > and ask me my login and password. I found directory MS SQL in program
> > files but i couldnt find uninstall programm. So I delete directory in
> > MS SQL in program files. But Crystal Entreprise find again MS SQL on my
> > machine.
> Not really sure why you would uninstall SQL Server just because you
> install Crystal Enterprise. Anyway, use Add/Remove Programs in the
> control panel to uninstall SQL Server. Just deleting the files does not
> help you, as Crystal - as any other civilized piece of software looks in
> the registry.

And of course at this point if the original poster has no backup of that
directory, there's no easy way to uninstall the program since as I recall,
the uninstl script is normally there.

>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Greg D. Moore (Strider) (mooregr_deleteth1s@.greenms.com) writes:
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns96CFF0C3CF1C2Yazorman@.127.0.0.1...
>> (minigitoo@.aol.com) writes:
>> > I am installing Crystal entreprise, it finds MS SQL Server installed
>> > and ask me my login and password. I found directory MS SQL in program
>> > files but i couldnt find uninstall programm. So I delete directory in
>> > MS SQL in program files. But Crystal Entreprise find again MS SQL on my
>> > machine.
>>
>> Not really sure why you would uninstall SQL Server just because you
>> install Crystal Enterprise. Anyway, use Add/Remove Programs in the
>> control panel to uninstall SQL Server. Just deleting the files does not
>> help you, as Crystal - as any other civilized piece of software looks in
>> the registry.
> And of course at this point if the original poster has no backup of that
> directory, there's no easy way to uninstall the program since as I recall,
> the uninstl script is normally there.

Oops! I didn't think of that. I hope he still has his installation media,
so he can repair his installation to uninstall it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96D06BE298745Yazorman@.127.0.0.1...
> Oops! I didn't think of that. I hope he still has his installation media,
> so he can repair his installation to uninstall it.

To paraphrase a quote from 30+ years ago...

"We had to install the program to uninstall it".

Very zen actually.

Hmm.. Zen and the Art of Computer Maintenance.

> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> Greg D. Moore (Strider) (mooregr_deleteth1s@.greenms.com) writes:
>>"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
>>news:Xns96CFF0C3CF1C2Yazorman@.127.0.0.1...
>>
>>>(minigitoo@.aol.com) writes:
>>>
>>>>I am installing Crystal entreprise, it finds MS SQL Server installed
>>>>and ask me my login and password. I found directory MS SQL in program
>>>>files but i couldnt find uninstall programm. So I delete directory in
>>>>MS SQL in program files. But Crystal Entreprise find again MS SQL on my
>>>>machine.
>>>
>>>Not really sure why you would uninstall SQL Server just because you
>>>install Crystal Enterprise. Anyway, use Add/Remove Programs in the
>>>control panel to uninstall SQL Server. Just deleting the files does not
>>>help you, as Crystal - as any other civilized piece of software looks in
>>>the registry.
>>
>>And of course at this point if the original poster has no backup of that
>>directory, there's no easy way to uninstall the program since as I recall,
>>the uninstl script is normally there.
>
> Oops! I didn't think of that. I hope he still has his installation media,
> so he can repair his installation to uninstall it.

This has some good information on how to manually uninstall SQL Server 2000:
http://support.microsoft.com/defaul...kb;en-us;290991|||thanks

it changes of other one who prefers to send post without intersest, for
giving their "very interesting" think !

designer wanted

Hi!
I'm looking for (preferably free) a tool that is useful in graphical
representation of DB (something like Oracle Designer). Could you give me
some hints where to find one?
Thanx
YaroVisio is pretty good for any type of UML design as well as E-R Diagramming.
It can even generate classes for you as well as the SQL needed to create the
objects.
Its' relatively inexpensive as far as designers go.
Rick Sawtell
"Jarosaw Tajcher" <ziomuch@.wp.pl> wrote in message
news:cdrpao$q7e$1@.nemesis.news.tpi.pl...
> Hi!
> I'm looking for (preferably free) a tool that is useful in graphical
> representation of DB (something like Oracle Designer). Could you give me
> some hints where to find one?
> Thanx
> Yaro
>|||The SQL Server (assuming you have already paid for it) comes with a diagramm
ing tool. Also, MS Access (in case you aready have a copy of it) has a "rel
ationship" tool which might be useful for your project (you can import prett
y much any data into MS Acc
ess).
Not exactly, what you asked for, but you might already have the tool you are
looking for...in your hands...
Tea C.
"Rick Sawtell" wrote:

> visio is pretty good for any type of UML design as well as E-R Diagramming
.
> It can even generate classes for you as well as the SQL needed to create t
he
> objects.
> Its' relatively inexpensive as far as designers go.
>
> Rick Sawtell
>
> "Jaros3aw Tajcher" <ziomuch@.wp.pl> wrote in message
> news:cdrpao$q7e$1@.nemesis.news.tpi.pl...
>
>|||There is no inexpensive tool for data modelling. While the SQL Server,
Access and Visio, may work they all have drawbacks.
One key feature is the ability to create a database or at least tables
in a database directly from a diagram and to have changes in the
diagram relect in the tables and relationships.
Anothe key feature of a tool is the ability to store elements of the
model ina repository for future re-use. Any experience developer would
love to carry around a file of tables used on other projects as a
short cut to designing the current project.
Lastly, you should look for the data and OODB (UML) modeling symbols
for visio that may now exist, some for no fee, if your goal is OODBs.
Lou Arnold
On Sat, 24 Jul 2004 19:06:02 -0700, "examnotes"
<smece1972@.yahoo.com.nospam> wrote:

>The SQL Server (assuming you have already paid for it) comes with a diagramming too
l. Also, MS Access (in case you aready have a copy of it) has a "relationship" tool
which might be useful for your project (you can import pretty much any data into MS
Ac
cess).[vbcol=seagreen]
>Not exactly, what you asked for, but you might already have the tool you ar
e looking for...in your hands...
>--
>Tea C.
>
>"Rick Sawtell" wrote:
>

Design Question re images

Hi

I have a table of people, and for some of them I want to store photo's, is it better to store the phots in a separate table or just add a column to the people table? I'm think about 60% will have photo's.

What is the best way to add a photo to a table?

Hi Graham,

The best practice is to create the image column in a seperate table and have a link column in the primary table.However the design is based on the retrieval of the table data,If your people data is always retrieved with image and no where else it is used in join with other tables it can be part of your main table itself

Regards,

Samsudeen B

|||

First why you want to store those Photos in Database. Consider to strore those on file system and store the File path on your table. It is very cheap to store on the filesystem(memory, retrival, storage & manipulations)..

If you want to store the Photos in database you need to store those in different table as Master Table. You can have those refrences on the detailed table. It is not a bad idea to keep the Image properties on the same (where the photos stored) table, like photo name, photo file ext, photo size etc..

|||Thank you, I had considered storing the filepaths (presumably nvarchar(260) is the best), but I thought the reason image type existed was because it was better to store images within the database, and I thought it might be more secure. But I can easliy fix security.|||

I know lot of people misunderstand with the name. Image is one of the Binary datatype and you can store any binary data like Word Docs, Excel and other binary files (image too). In Sql Server VarBinary(Max) is introduced.

|||

What's the advantage of storing other document types, rather than storing file paths?

Is it a security issue? Surely file permissons can fix that, or is it to do with replication, and distributed databases (not an issue for my needs)?

Design Question

Hi

I am trying to implement a invtentory control system and would like
some advice on the best design for it.

The system will have to main tables Product and Stock which will look
as follows

Product
---
ProductId
PartNumber
Description

Stock
--
StockId
ProductId
SerialNumber
RecievedDate
OrderNo
ShipmentNo

In ths stock table the RecivedDate Signifies when the product
recieved, The OrderNo signifies the whether the item has been sold and
the shipmentNo represents whethert tiem has been shipped.

I want to produce an SQL query which basically looks like this

StockList
---
PartNumber
Description
QtyInStock (StockItems not sold or shipped)
QtySold (StockItems Sold)
QtyShipped (StockItems Sold and shipped)

I cant seem to work out what the query would look like for this.
Has anyone got anytips, or alternative ideas/designs"Boogieboy" <iamthecow@.hotmail.com> wrote in message news:67ae4f66.0409220519.6d99eb62@.posting.google.c om...
> Hi
> I am trying to implement a invtentory control system and would like
> some advice on the best design for it.
<snip>
> I want to produce an SQL query which basically looks like this
> StockList
> ---
> PartNumber
> Description
> QtyInStock (StockItems not sold or shipped)
> QtySold (StockItems Sold)
> QtyShipped (StockItems Sold and shipped)
> I cant seem to work out what the query would look like for this.
> Has anyone got anytips, or alternative ideas/designs

Tip: Summing 1s and 0s is essentially the same as counting...

How about this?

Select
PartNumber,
Description,
SUM( CASE
WHEN OrderNo is NULL and ShipmentNo is NULL
then 1 else 0 END) as QtyInStock,
SUM( CASE
WHEN OrderNo is NOT NULL and ShipmentNo is NULL
then 1 else 0 END) as QtySold,
SUM( CASE
WHEN OrderNo is NOT NULL and ShipmentNo is NOT NULL
then 1 else 0 end) as QtyShipped
FROM
Product
JOIN Stock
ON Product.ProductID = Stock.ProductID
GROUP BY
PartNumber,
Description
ORDER BY
PartNumber ;

--
Paul Horan[TeamSybase] www.teamsybase.com
Sr. Architect
VCI Springfield, Mass
www.vcisolutions.com|||>> Has anyone got any tips, or alternative ideas/designs <<

inventory, orders and shipments are all logically different things, I
would put them in separate tables.

Design opinion?

Hi
I am starting the task of creating a data warehouse for my company's OLTP
system and we would like to offload reporting to a reporting schema at a
data warehouse. We have 2 kinds of reports: operational (used daily and
need data just about real time) and analytical (used to determine sales
trends, etc).
For the analytical reports, I think we can just do a nightly ETL load since
the data doesn't need to be too recent. However, I was thinking of using
replication to replicate the table data used in the operational reports,
capturing the data in stored procedures as they come across the pipe and
then performing ETL within the stored procs. That way I think we can have
real time data available for reporting.
Does anyone have an opinion on this scheme?
Thank youI've done this kind of configuration before on SQL 2K. Transactional
replication is about the best you can do since log shipping takes the DB
offline. We preferred to pause replication during the staging portion of
the ETL cycle to avoid blocking and dead locking. This also gave us a
consistent point in time snap shot of the source to work from. Using TSQL
as an ETL tool is fast but has limitations around data transformation and no
access to the bulk load api.
Good luck,
Danny
"Dodo Lurker" <none@.noemailplease> wrote in message
news:BIKdneofYum6-2reRVn-pQ@.comcast.com...
> Hi
> I am starting the task of creating a data warehouse for my company's OLTP
> system and we would like to offload reporting to a reporting schema at a
> data warehouse. We have 2 kinds of reports: operational (used daily and
> need data just about real time) and analytical (used to determine sales
> trends, etc).
> For the analytical reports, I think we can just do a nightly ETL load
> since
> the data doesn't need to be too recent. However, I was thinking of
> using
> replication to replicate the table data used in the operational reports,
> capturing the data in stored procedures as they come across the pipe and
> then performing ETL within the stored procs. That way I think we can have
> real time data available for reporting.
> Does anyone have an opinion on this scheme?
> Thank you
>|||Hello,
Timing is everything.
I would work on getting your data warehouse overnight processes
completed.
In a short period of time new replication options will be available in
SQL Server 2005.
Even the existing options may meet your needs, but with the right
timing you may
have better options available to you.|||Depending on what method you use to migrate data to the warehouse (and also
the volitility of the OLTP data), this may impact the performance of the
OLTP system. Also, once the user expects analysis in real time, any
interruption will be perceived as a system malfunction, and thus your
maintenance requirements and responsilbility have increased. Do they really
need real time analysis? Also, what type of analysis; OLAP?
"Dodo Lurker" <none@.noemailplease> wrote in message
news:BIKdneofYum6-2reRVn-pQ@.comcast.com...
> Hi
> I am starting the task of creating a data warehouse for my company's OLTP
> system and we would like to offload reporting to a reporting schema at a
> data warehouse. We have 2 kinds of reports: operational (used daily and
> need data just about real time) and analytical (used to determine sales
> trends, etc).
> For the analytical reports, I think we can just do a nightly ETL load
> since
> the data doesn't need to be too recent. However, I was thinking of
> using
> replication to replicate the table data used in the operational reports,
> capturing the data in stored procedures as they come across the pipe and
> then performing ETL within the stored procs. That way I think we can have
> real time data available for reporting.
> Does anyone have an opinion on this scheme?
> Thank you
>

Design opinion?

Hi
I am starting the task of creating a data warehouse for my company's OLTP
system and we would like to offload reporting to a reporting schema at a
data warehouse. We have 2 kinds of reports: operational (used daily and
need data just about real time) and analytical (used to determine sales
trends, etc).
For the analytical reports, I think we can just do a nightly ETL load since
the data doesn't need to be too recent. However, I was thinking of using
replication to replicate the table data used in the operational reports,
capturing the data in stored procedures as they come across the pipe and
then performing ETL within the stored procs. That way I think we can have
real time data available for reporting.
Does anyone have an opinion on this scheme?
Thank you
I've done this kind of configuration before on SQL 2K. Transactional
replication is about the best you can do since log shipping takes the DB
offline. We preferred to pause replication during the staging portion of
the ETL cycle to avoid blocking and dead locking. This also gave us a
consistent point in time snap shot of the source to work from. Using TSQL
as an ETL tool is fast but has limitations around data transformation and no
access to the bulk load api.
Good luck,
Danny
"Dodo Lurker" <none@.noemailplease> wrote in message
news:BIKdneofYum6-2reRVn-pQ@.comcast.com...
> Hi
> I am starting the task of creating a data warehouse for my company's OLTP
> system and we would like to offload reporting to a reporting schema at a
> data warehouse. We have 2 kinds of reports: operational (used daily and
> need data just about real time) and analytical (used to determine sales
> trends, etc).
> For the analytical reports, I think we can just do a nightly ETL load
> since
> the data doesn't need to be too recent. However, I was thinking of
> using
> replication to replicate the table data used in the operational reports,
> capturing the data in stored procedures as they come across the pipe and
> then performing ETL within the stored procs. That way I think we can have
> real time data available for reporting.
> Does anyone have an opinion on this scheme?
> Thank you
>
|||Hello,
Timing is everything.
I would work on getting your data warehouse overnight processes
completed.
In a short period of time new replication options will be available in
SQL Server 2005.
Even the existing options may meet your needs, but with the right
timing you may
have better options available to you.
|||Depending on what method you use to migrate data to the warehouse (and also
the volitility of the OLTP data), this may impact the performance of the
OLTP system. Also, once the user expects analysis in real time, any
interruption will be perceived as a system malfunction, and thus your
maintenance requirements and responsilbility have increased. Do they really
need real time analysis? Also, what type of analysis; OLAP?
"Dodo Lurker" <none@.noemailplease> wrote in message
news:BIKdneofYum6-2reRVn-pQ@.comcast.com...
> Hi
> I am starting the task of creating a data warehouse for my company's OLTP
> system and we would like to offload reporting to a reporting schema at a
> data warehouse. We have 2 kinds of reports: operational (used daily and
> need data just about real time) and analytical (used to determine sales
> trends, etc).
> For the analytical reports, I think we can just do a nightly ETL load
> since
> the data doesn't need to be too recent. However, I was thinking of
> using
> replication to replicate the table data used in the operational reports,
> capturing the data in stored procedures as they come across the pipe and
> then performing ETL within the stored procs. That way I think we can have
> real time data available for reporting.
> Does anyone have an opinion on this scheme?
> Thank you
>

Design issue

Hi

I need some design info. Say that I need to create an educational lab
for databases with SQL Server. I need all the users to be able to
execute common SQL commands ( create - alter drop table , insert ,
update , delete ) and each user have his own space ( users not able to
see others data and a limit say of 2 MB) . The most obvious solution
is to create ( if I have 100 users ) , 100 login account ( SQL Server
Accounts ) , then create 100 databases , and assign each user to one
of this account with database role db_datawriter. Is this the best
solution or there is another way to do it ?

Thanks"db_happy" <database@.freemail.gr> wrote in message
news:b625e551.0407110220.540e1260@.posting.google.c om...
> Hi
> I need some design info. Say that I need to create an educational lab
> for databases with SQL Server. I need all the users to be able to
> execute common SQL commands ( create - alter - drop table , insert ,
> update , delete ) and each user have his own space ( users not able to
> see others data and a limit say of 2 MB) . The most obvious solution
> is to create ( if I have 100 users ) , 100 login account ( SQL Server
> Accounts ) , then create 100 databases , and assign each user to one
> of this account with database role db_datawriter. Is this the best
> solution or there is another way to do it ?
> Thanks

If you need to limit the users' space, then individual databases are
probably the only way to go, since there's no way to manage space quotas
within a single database. (At least, there's no standard way without using a
third party tool or building your own quota tools.) Note that db_datawriter
cannot create objects, so you would need to add the user to db_ddladmin
also, or create a custom role with the permissions set you want.

The downside of this approach is management of the large number of databases
and the associated files, backups etc. However, if you always use standard
scripts for creating/dropping each database, leverage Windows groups for
authentication (if possible), and perhaps use SQL maintenance plans for
backups, then you should be able to keep the work required to a minimum.

Simon|||Hi

You could buy each a copy of developer edition and let them run it on their
own PCs! A cheaper solution would be to use MSDE.

John

"db_happy" <database@.freemail.gr> wrote in message
news:b625e551.0407110220.540e1260@.posting.google.c om...
> Hi
> I need some design info. Say that I need to create an educational lab
> for databases with SQL Server. I need all the users to be able to
> execute common SQL commands ( create - alter - drop table , insert ,
> update , delete ) and each user have his own space ( users not able to
> see others data and a limit say of 2 MB) . The most obvious solution
> is to create ( if I have 100 users ) , 100 login account ( SQL Server
> Accounts ) , then create 100 databases , and assign each user to one
> of this account with database role db_datawriter. Is this the best
> solution or there is another way to do it ?
> Thanks|||On Sun, 11 Jul 2004 17:39:20 GMT, John Bell wrote:

> Hi
> You could buy each a copy of developer edition and let them run it on their
> own PCs! A cheaper solution would be to use MSDE.
> John
> "db_happy" <database@.freemail.gr> wrote in message
> news:b625e551.0407110220.540e1260@.posting.google.c om...
>> Hi
>>
>> I need some design info. Say that I need to create an educational lab
>> for databases with SQL Server. I need all the users to be able to
>> execute common SQL commands ( create - alter - drop table , insert ,
>> update , delete ) and each user have his own space ( users not able to
>> see others data and a limit say of 2 MB) . The most obvious solution
>> is to create ( if I have 100 users ) , 100 login account ( SQL Server
>> Accounts ) , then create 100 databases , and assign each user to one
>> of this account with database role db_datawriter. Is this the best
>> solution or there is another way to do it ?
>>
>> Thanks

Design flaw - Too slow lock escalation

Hi!
I seem to have a problem opposite to everyone else.
Lock escalation does not occur.
My client runs a DTS-job with some 30 steps every night.
Randomly some of the the steps fail with:
Error 1204 , Severity Level 19 , Message Text ,The SQL Server cannot obtain
a LOCK resource at this time.
When the failing steps are rerun at daytime they "never" fails on 1204.
I have been trying to pin-point a locking culprit for some time but haven't
found any.
On Friday night I followed the DTS-job closely, running sp_who2 and sp_lock
at short intervals
and also had a perfmon running. When the 1204 at last occured after midnight
I found the following:
The transaction is a
Insert into BIG_TABLE
select * from WORK_Table
Most indexes, but not all are dropped from BIG_Table and later recreated.
Sp_who2 showed that the insert was parallelled in 5 threads, (6 cpu
server)
and within a few seconds these 5 threads had created +1GB of Key locks, and
shortly Error 1204 occured and the transaction was rollbacked.
The DTS-job was the only active transaction in the server so there was no
competition for resources.
My theory is that the lock monitor, (that I presume handles lock
escalation), sleeps too long sometimes,
and fails to do the lock escalation. In this case the server is quite
powerful and has good fibre access the SAN-disk,
and when CPU and I-O is freely available at nighttime lock consumimg is too
fast. At daytime when there is competition for both CPU and I-O,
the lock-creating process is not fast enough to eat all available memory.
Please enlighten me if I'm wrong.
We have circumvented the problem by adding a WITH (TABLOCK) to the insert
statement,
but this is undesirable, if we for other reasons have to rerun a jobstep at
daytime.
Is this a known problem or as designed?
Should I go into the tedious process of raising a support case with MS?
By the way:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
\dg
Dan van Ginhoven
Sweden
Is this an all or nothing process? By this I mean that can it be done in
smaller batches of say 10K rows at a time? Usually this can be done and
will limit the number of locks to ~10K. If not then a Table level lock may
be best. The most likely reason why the lock is not escalating to a tablock
is due to another lock on that table at some level. Even a single shared
row lock will stop the escalation process. So if users are selecting from
the table it may not escalate on it's own unless there is a lull in the
selects.
Andrew J. Kelly SQL MVP
"Dan van Ginhoven" <danfan46@.hotmail.com> wrote in message
news:shR6f.148931$dP1.506911@.newsc.telia.net...
> Hi!
> I seem to have a problem opposite to everyone else.
> Lock escalation does not occur.
> My client runs a DTS-job with some 30 steps every night.
> Randomly some of the the steps fail with:
> Error 1204 , Severity Level 19 , Message Text ,The SQL Server cannot
> obtain
> a LOCK resource at this time.
> When the failing steps are rerun at daytime they "never" fails on 1204.
> I have been trying to pin-point a locking culprit for some time but
> haven't
> found any.
> On Friday night I followed the DTS-job closely, running sp_who2 and
> sp_lock
> at short intervals
> and also had a perfmon running. When the 1204 at last occured after
> midnight
> I found the following:
> The transaction is a
> Insert into BIG_TABLE
> select * from WORK_Table
> Most indexes, but not all are dropped from BIG_Table and later recreated.
> Sp_who2 showed that the insert was parallelled in 5 threads, (6 cpu
> server)
> and within a few seconds these 5 threads had created +1GB of Key locks,
> and
> shortly Error 1204 occured and the transaction was rollbacked.
> The DTS-job was the only active transaction in the server so there was no
> competition for resources.
> My theory is that the lock monitor, (that I presume handles lock
> escalation), sleeps too long sometimes,
> and fails to do the lock escalation. In this case the server is quite
> powerful and has good fibre access the SAN-disk,
> and when CPU and I-O is freely available at nighttime lock consumimg is
> too
> fast. At daytime when there is competition for both CPU and I-O,
> the lock-creating process is not fast enough to eat all available memory.
> Please enlighten me if I'm wrong.
> We have circumvented the problem by adding a WITH (TABLOCK) to the
> insert
> statement,
> but this is undesirable, if we for other reasons have to rerun a jobstep
> at
> daytime.
> Is this a known problem or as designed?
> Should I go into the tedious process of raising a support case with MS?
> By the way:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Edition on Windows NT 5.2 (Build 3790: )
> \dg
> Dan van Ginhoven
> Sweden
>

Design flaw - Too slow lock escalation

Hi!
I seem to have a problem opposite to everyone else.
Lock escalation does not occur.
My client runs a DTS-job with some 30 steps every night.
Randomly some of the the steps fail with:
Error 1204 , Severity Level 19 , Message Text ,The SQL Server cannot obtain
a LOCK resource at this time.
When the failing steps are rerun at daytime they "never" fails on 1204.
I have been trying to pin-point a locking culprit for some time but haven't
found any.
On Friday night I followed the DTS-job closely, running sp_who2 and sp_lock
at short intervals
and also had a perfmon running. When the 1204 at last occured after midnight
I found the following:
The transaction is a
Insert into BIG_TABLE
select * from WORK_Table
Most indexes, but not all are dropped from BIG_Table and later recreated.
Sp_who2 showed that the insert was parallelled in 5 threads, (6 cpu
server)
and within a few seconds these 5 threads had created +1GB of Key locks, and
shortly Error 1204 occured and the transaction was rollbacked.
The DTS-job was the only active transaction in the server so there was no
competition for resources.
My theory is that the lock monitor, (that I presume handles lock
escalation), sleeps too long sometimes,
and fails to do the lock escalation. In this case the server is quite
powerful and has good fibre access the SAN-disk,
and when CPU and I-O is freely available at nighttime lock consumimg is too
fast. At daytime when there is competition for both CPU and I-O,
the lock-creating process is not fast enough to eat all available memory.
Please enlighten me if I'm wrong.
We have circumvented the problem by adding a WITH (TABLOCK) to the insert
statement,
but this is undesirable, if we for other reasons have to rerun a jobstep at
daytime.
Is this a known problem or as designed?
Should I go into the tedious process of raising a support case with MS?
By the way:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
\dg
---
Dan van Ginhoven
SwedenIs this an all or nothing process? By this I mean that can it be done in
smaller batches of say 10K rows at a time? Usually this can be done and
will limit the number of locks to ~10K. If not then a Table level lock may
be best. The most likely reason why the lock is not escalating to a tablock
is due to another lock on that table at some level. Even a single shared
row lock will stop the escalation process. So if users are selecting from
the table it may not escalate on it's own unless there is a lull in the
selects.
Andrew J. Kelly SQL MVP
"Dan van Ginhoven" <danfan46@.hotmail.com> wrote in message
news:shR6f.148931$dP1.506911@.newsc.telia.net...
> Hi!
> I seem to have a problem opposite to everyone else.
> Lock escalation does not occur.
> My client runs a DTS-job with some 30 steps every night.
> Randomly some of the the steps fail with:
> Error 1204 , Severity Level 19 , Message Text ,The SQL Server cannot
> obtain
> a LOCK resource at this time.
> When the failing steps are rerun at daytime they "never" fails on 1204.
> I have been trying to pin-point a locking culprit for some time but
> haven't
> found any.
> On Friday night I followed the DTS-job closely, running sp_who2 and
> sp_lock
> at short intervals
> and also had a perfmon running. When the 1204 at last occured after
> midnight
> I found the following:
> The transaction is a
> Insert into BIG_TABLE
> select * from WORK_Table
> Most indexes, but not all are dropped from BIG_Table and later recreated.
> Sp_who2 showed that the insert was parallelled in 5 threads, (6 cpu
> server)
> and within a few seconds these 5 threads had created +1GB of Key locks,
> and
> shortly Error 1204 occured and the transaction was rollbacked.
> The DTS-job was the only active transaction in the server so there was no
> competition for resources.
> My theory is that the lock monitor, (that I presume handles lock
> escalation), sleeps too long sometimes,
> and fails to do the lock escalation. In this case the server is quite
> powerful and has good fibre access the SAN-disk,
> and when CPU and I-O is freely available at nighttime lock consumimg is
> too
> fast. At daytime when there is competition for both CPU and I-O,
> the lock-creating process is not fast enough to eat all available memory.
> Please enlighten me if I'm wrong.
> We have circumvented the problem by adding a WITH (TABLOCK) to the
> insert
> statement,
> but this is undesirable, if we for other reasons have to rerun a jobstep
> at
> daytime.
> Is this a known problem or as designed?
> Should I go into the tedious process of raising a support case with MS?
> By the way:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Edition on Windows NT 5.2 (Build 3790: )
> \dg
> ---
> Dan van Ginhoven
> Sweden
>

Design and Performance question

Hi
I'm working now on a project that is Database oriented, and this
Database is so big and has millions of records, my question is:
Where must I put the business logic?
1- Client side: where client is responsible for the business logic.
2- Server Side: exceute the business logic in the server using stored
procedures and triggers...
3- Hybrid schema load balancing on the two sides.
I'm using MS.SQL Server2000
I have many of very complex queries, and many of complex views.
and finally can I depend on Jobs in MS.SQL Server in some tasks with
out impact the perfermance down.
Please reply ASAP
thanks a lot.I try to put it where it is best suited.
Anything set based which acts on a large amount of data should be in stored
procs or performance will suffer.
Always interface with the db via SPs as you can monitor and correct problems
and change the database tructure without changing anything outside the db.
Anything not set based is up to you and may just depend on the avalability
of development expertise.
You can rely on jobs. Whether they mipact anything depends on what they do.
"Yousef Absee" wrote:

> Hi
> I'm working now on a project that is Database oriented, and this
> Database is so big and has millions of records, my question is:
> Where must I put the business logic?
> 1- Client side: where client is responsible for the business logic.
> 2- Server Side: exceute the business logic in the server using stored
> procedures and triggers...
> 3- Hybrid schema load balancing on the two sides.
> I'm using MS.SQL Server2000
> I have many of very complex queries, and many of complex views.
> and finally can I depend on Jobs in MS.SQL Server in some tasks with
> out impact the perfermance down.
> Please reply ASAP
> thanks a lot.
>|||"Yousef Absee" <youstec@.gmail.com> wrote in message
news:1131554866.394769.115640@.g47g2000cwa.googlegroups.com...
> Where must I put the business logic?
> 1- Client side: where client is responsible for the business logic.
In the bizClasses that are called by the GUI and directly interact with the
db. If that is via metaData it's even better.

> 2- Server Side: exceute the business logic in the server using stored
> procedures and triggers...
Well sure, These rules may not be business rules but basic data handeling
instead. Triggers that generate data in other tables for necessary reasons
is a good thing.

> 3- Hybrid schema load balancing on the two sides.
> I'm using MS.SQL Server2000
> I have many of very complex queries, and many of complex views.
> and finally can I depend on Jobs in MS.SQL Server in some tasks with
> out impact the perfermance down.
Depends on what the job is to do and what is happening at the same time?