Configure Auto-start option in SQL agent

Is there a way I can set the Auto-start option in SQL Server agent to
true from my program. This can be done through a DOS command, SQL
statement, or anything else I can execute through my program. I know
how to start the agent from my software, I just don't know how to set
it to auto-start.
Any suggestions?You could use the sc.exe command prompt utility (sc config). Check
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/windowsserver2003/proddocs/entserver/sc.asp.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Ray Lavelle" <bostonpartykid@.yahoo.com> wrote in message
news:d8653140.0309061823.6f44952a@.posting.google.com...
> Is there a way I can set the Auto-start option in SQL Server agent to
> true from my program. This can be done through a DOS command, SQL
> statement, or anything else I can execute through my program. I know
> how to start the agent from my software, I just don't know how to set
> it to auto-start.
> Any suggestions?|||Other options include using Service Control Manager e.g.
scm -Silent 1 -Action 7 -Service SQLServerAgent -SvcStartType 2
or from TSQL
exec xp_cmdshell 'scm -Silent 1 -Action 7 -Service
SQLServerAgent -SvcStartType 2'
You could also use SQLDMO from VBScript
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Ray Lavelle" <bostonpartykid@.yahoo.com> wrote in message
news:d8653140.0309061823.6f44952a@.posting.google.com...
Is there a way I can set the Auto-start option in SQL Server agent to
true from my program. This can be done through a DOS command, SQL
statement, or anything else I can execute through my program. I know
how to start the agent from my software, I just don't know how to set
it to auto-start.
Any suggestions?|||That worked great, thanks!!!!
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message news:<uRJvexUdDHA.2508@.tk2msftngp13.phx.gbl>...
> Other options include using Service Control Manager e.g.
> scm -Silent 1 -Action 7 -Service SQLServerAgent -SvcStartType 2
> or from TSQL
> exec xp_cmdshell 'scm -Silent 1 -Action 7 -Service
> SQLServerAgent -SvcStartType 2'
> You could also use SQLDMO from VBScript
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Ray Lavelle" <bostonpartykid@.yahoo.com> wrote in message
> news:d8653140.0309061823.6f44952a@.posting.google.com...
> Is there a way I can set the Auto-start option in SQL Server agent to
> true from my program. This can be done through a DOS command, SQL
> statement, or anything else I can execute through my program. I know
> how to start the agent from my software, I just don't know how to set
> it to auto-start.
> Any suggestions?

Conditional Where Clause

Hi - I am writing a C# program using SQL Server. The form I have is
collecting search criteria for a database. The main 3 fields are Category,
Type, Author.
Any combination of the 3 fields can be used. That is, All 3 fields can be
used to search on, or just 2 or just 1. If the user selects 1 or 2 fields,
I
can't use the 3rd field in the where clause of the query.
How can I create a generic query and pass a string for the "where" clause
instead of creating 7 specific queries for each possible combination of
search criteria.
Thanks,
SarahCREATE PROCEDURE getbook
@.category VARCHAR(10) = NULL,
@.bookType VARCHAR(10) = NULL,
@.author VARCHAR(10) = NULL
AS
SELECT category,
booktype,
author
FROM book
WHERE (category = @.category OR @.category IS NULL )
AND (booktype = @.bookType OR @.bookType IS NULL )
AND (author = @.author OR @.author IS NULL )|||Sarah,
From what you say is the following correct:
You want a Stored Procedure that takes 3 parameters and returns a recordset
based on the passed parameters.
The SELECT statement itself will be static, and both the first two params
will be used if present, and the third [arameter will only be used if neithe
r
of the first two params are present.
Does that sum it up?
Tony
"Sarah Sarah" wrote:

> Hi - I am writing a C# program using SQL Server. The form I have is
> collecting search criteria for a database. The main 3 fields are Category
,
> Type, Author.
> Any combination of the 3 fields can be used. That is, All 3 fields can b
e
> used to search on, or just 2 or just 1. If the user selects 1 or 2 fields
, I
> can't use the 3rd field in the where clause of the query.
> How can I create a generic query and pass a string for the "where" clause
> instead of creating 7 specific queries for each possible combination of
> search criteria.
> Thanks,
> Sarah|||Sarah,
Bearing in mind the mutual exclusivity between params 1,2 and param 3, the
following code will work:::
CREATE STORED PROCEDURE [dbo].[usp_GetSearchResults]
@.Category varchar(100)='',
@.Type varchar(100)='',
@.Author varchar(100)
AS
DECLARE @.sSQL varchar(2000)
SET @.sSQL = ''
IF @.Category ='' AND @.Type =''
BEGIN
SET @.sSQL = @.sSQL + ' SELECT Category, Type, Author '
SET @.sSQL = @.sSQL + ' FROM tblMYTABLE '
SET @.sSQL = @.sSQL + ' WHERE (@.Category='' OR Category=' + CHAR(39) +
@.Category + CHAR(39) + ') '
SET @.sSQL = @.sSQL + ' AND (@.Author ='' OR Author=' + CHAR(39) + @.Author +
CHAR(39) + ') '
END
ELSE
BEGIN
SET @.sSQL = @.sSQL + ' SELECT Category, Type, Author '
SET @.sSQL = @.sSQL + ' FROM tblMYTABLE '
SET @.sSQL = @.sSQL + ' WHERE (@.Category='' OR Category=' + CHAR(39) +
@.Category + CHAR(39) + ') '
SET @.sSQL = @.sSQL + ' AND (@.Type ='' OR Type=' + CHAR(39) + @.Type +
CHAR(39) + ') '
END
EXEC (@.sSQL)
You do not necessarily need the character string to create the select
statement, it does help with debugging though.
Hope it helps,
Tony
"Sarah Sarah" wrote:

> Hi - I am writing a C# program using SQL Server. The form I have is
> collecting search criteria for a database. The main 3 fields are Category
,
> Type, Author.
> Any combination of the 3 fields can be used. That is, All 3 fields can b
e
> used to search on, or just 2 or just 1. If the user selects 1 or 2 fields
, I
> can't use the 3rd field in the where clause of the query.
> How can I create a generic query and pass a string for the "where" clause
> instead of creating 7 specific queries for each possible combination of
> search criteria.
> Thanks,
> Sarah|||KenJ - thanks - this logic will work, but I am getting a syntax error:
"Duplicated parameter names are not allowed"
when I try to do this in Query builder. Any idea what would cause this erro
r.
Thanks,
Sarah
"KenJ" wrote:

> CREATE PROCEDURE getbook
> @.category VARCHAR(10) = NULL,
> @.bookType VARCHAR(10) = NULL,
> @.author VARCHAR(10) = NULL
> AS
> SELECT category,
> booktype,
> author
> FROM book
> WHERE (category = @.category OR @.category IS NULL )
> AND (booktype = @.bookType OR @.bookType IS NULL )
> AND (author = @.author OR @.author IS NULL )
>|||I'm not familiar with query builder. Can you run it in query analyzer?
Here is a sample script that creates a table, loads some dummy data,
runs the procedure with several variations then drops the table and
procedure. I've run it in query analyzer to be sure it works...
USE tempdb
GO
SET nocount ON
GO
CREATE TABLE book (
bookid INT IDENTITY( 1 , 1 ) NOT NULL PRIMARY KEY
, category VARCHAR(10) NULL
, booktype VARCHAR(10) NULL
, author VARCHAR(10) NULL)
GO
INSERT book
VALUES('fiction'
, 'paperback'
, 'twain')
INSERT book
VALUES('fiction'
, 'hardbound'
, 'asimov')
INSERT book
VALUES('fiction'
, 'paperback'
, 'rand')
GO
CREATE PROCEDURE getbook
@.category VARCHAR(10) = NULL
, @.bookType VARCHAR(10) = NULL
, @.author VARCHAR(10) = NULL
AS
SELECT category
, booktype
, author
FROM book
WHERE (category = @.category
OR @.category IS NULL )
AND (booktype = @.bookType
OR @.bookType IS NULL )
AND (author = @.author
OR @.author IS NULL )
GO
-- get all fiction books
EXEC getbook @.category = 'fiction'
-- all fiction books by rand
EXEC getbook @.category = 'fiction' ,
@.author = 'rand'
-- all paperbacks
EXEC getbook @.bookType = 'paperback'
-- returns all books since we don't supply any filter
EXEC getbook
GO
DROP TABLE book
GO
DROP PROCEDURE getbook
GO|||On Wed, 1 Feb 2006 16:50:27 -0800, Sarah Sarah wrote:

>Hi - I am writing a C# program using SQL Server. The form I have is
>collecting search criteria for a database. The main 3 fields are Category,
>Type, Author.
>Any combination of the 3 fields can be used. That is, All 3 fields can be
>used to search on, or just 2 or just 1. If the user selects 1 or 2 fields,
I
>can't use the 3rd field in the where clause of the query.
>How can I create a generic query and pass a string for the "where" clause
>instead of creating 7 specific queries for each possible combination of
>search criteria.
Hi Sarah,
Many ways to skin this cat can be found i Erland Sommarskog's article:
http://www.sommarskog.se/dyn-search.html
Hugo Kornelis, SQL Server MVP|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
It would also help if you would learn that a field and a column nothing
whatsoever alike and that names like "type", "category", etc. are too
vague to be data element names/. Try something like this:
CREATE PROCEDURE GetBook
(@.my_book_category VARCHAR(10) = NULL, -- wild guess
@.my_book_type INTEGER = NULL, -- Dewey Decimal ?
@.my_author_name VARCHAR(25) = NULL)
AS
SELECT book_category, book_type, author_name
FROM Library
WHERE book_category = COALESCE (@.my_book_category, book_category)
AND book_type = COALESCE (@.my_book_type, book_type)
AND author_name = COALESCE (@.my_author_name, author_)name) ;|||> WHERE book_category = COALESCE (@.my_book_category, book_category)
> AND book_type = COALESCE (@.my_book_type, book_type)
> AND author_name = COALESCE (@.my_author_name, author_)name) ;
That would give a tablescan.
Can you imagine how badly that will perform on a table with a few million
rows perhaps 1GB in size.
To do the tablescan everytime a user ran the query SQL Server would have to
read 1GB of data.
Now, multiply that by 10 users, thats 10GB of data SQL Server now needs to
read in order to process all 10 queries.
You are going to need one hell of a big box!
The correct way to do this is to either use IF..ELSE to make the query more
specific depending on which parameters are specified, ie. only put the
parameters specified on the WHERE clause.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138944895.900338.14850@.g47g2000cwa.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
> It would also help if you would learn that a field and a column nothing
> whatsoever alike and that names like "type", "category", etc. are too
> vague to be data element names/. Try something like this:
> CREATE PROCEDURE GetBook
> (@.my_book_category VARCHAR(10) = NULL, -- wild guess
> @.my_book_type INTEGER = NULL, -- Dewey Decimal ?
> @.my_author_name VARCHAR(25) = NULL)
> AS
> SELECT book_category, book_type, author_name
> FROM Library
> WHERE book_category = COALESCE (@.my_book_category, book_category)
> AND book_type = COALESCE (@.my_book_type, book_type)
> AND author_name = COALESCE (@.my_author_name, author_)name) ;
>|||Don't forget Ken that the query below will give you a very general plan so
you'll probably end up doing a table scan.
Check the plan before you decided on the solution.
Much better to use IF ELSE or dynamic SQL and taylor your query to the
parameters passed.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"KenJ" <kenjohnson@.hotmail.com> wrote in message
news:1138899994.839100.3010@.o13g2000cwo.googlegroups.com...
> I'm not familiar with query builder. Can you run it in query analyzer?
> Here is a sample script that creates a table, loads some dummy data,
> runs the procedure with several variations then drops the table and
> procedure. I've run it in query analyzer to be sure it works...
> USE tempdb
> GO
> SET nocount ON
> GO
> CREATE TABLE book (
> bookid INT IDENTITY( 1 , 1 ) NOT NULL PRIMARY KEY
> , category VARCHAR(10) NULL
> , booktype VARCHAR(10) NULL
> , author VARCHAR(10) NULL)
> GO
> INSERT book
> VALUES('fiction'
> , 'paperback'
> , 'twain')
> INSERT book
> VALUES('fiction'
> , 'hardbound'
> , 'asimov')
> INSERT book
> VALUES('fiction'
> , 'paperback'
> , 'rand')
> GO
> CREATE PROCEDURE getbook
> @.category VARCHAR(10) = NULL
> , @.bookType VARCHAR(10) = NULL
> , @.author VARCHAR(10) = NULL
> AS
> SELECT category
> , booktype
> , author
> FROM book
> WHERE (category = @.category
> OR @.category IS NULL )
> AND (booktype = @.bookType
> OR @.bookType IS NULL )
> AND (author = @.author
> OR @.author IS NULL )
> GO
> -- get all fiction books
> EXEC getbook @.category = 'fiction'
> -- all fiction books by rand
> EXEC getbook @.category = 'fiction' ,
> @.author = 'rand'
> -- all paperbacks
> EXEC getbook @.bookType = 'paperback'
> -- returns all books since we don't supply any filter
> EXEC getbook
> GO
> DROP TABLE book
> GO
> DROP PROCEDURE getbook
> GO
>

Concurrent queries

I have an accounting program that is runing very slow. The
only error that shows in the errorlog is "spid12 This SQL
Server has been optimized for 8 concurrent queries. this
limit has been exceeded by 12 queries and performance may
be adversely affected." What dose this mean? the program
runs on SQL Server 2000.
Thaks,Daniel,
It means that you are running either MSDE or SQL2000 personal engine and
this behavior is by design.They are not meant for heavy workload and you may
want to upgrade to a retail edition.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Daniel Barnaby" <dbarnaby@.beltonschools.org> wrote in message
news:658101c37d52$ff644ee0$a601280a@.phx.gbl...
> I have an accounting program that is runing very slow. The
> only error that shows in the errorlog is "spid12 This SQL
> Server has been optimized for 8 concurrent queries. this
> limit has been exceeded by 12 queries and performance may
> be adversely affected." What dose this mean? the program
> runs on SQL Server 2000.
> Thaks,

Concurrent executions of "fn_trace_gettable" function are failing.

Hi,
While executing
select * from fn_trace_gettable('C:\Program Files\Microsoft SQL
Server\MSSQL.10\MSSQL\DATA\log.trc', default);
query simulateneously from multiple clients, only one
of them succeeds and all others give the following error message:
File 'C:\Program Files\Microsoft SQL Server\MSSQL.10\MSSQL\DATA\log.trc'
either does not exist or is not a recognizable trace file. Or there was an
error opening the file.
Seems like the "fn_trace_gettable" function is locking the trace log file
internally during it's execution. I do not have any alternative other than
executing the query simulateously from multiple clients. Can anyone suggest
a workaround for this problem?
Thanks,
NileshI haven't tested this myself, but definitely seems like a locking issue on
the file, and can see why that would be the case.
Can you load the file into one table first, and then use DTS to parallelly
copy that table to multiple locations?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Nilesh" <nilesh.oswal@.gmail.com> wrote in message
news:%23rXnKvzjEHA.3664@.TK2MSFTNGP12.phx.gbl...
Hi,
While executing
select * from fn_trace_gettable('C:\Program Files\Microsoft SQL
Server\MSSQL.10\MSSQL\DATA\log.trc', default);
query simulateneously from multiple clients, only one
of them succeeds and all others give the following error message:
File 'C:\Program Files\Microsoft SQL Server\MSSQL.10\MSSQL\DATA\log.trc'
either does not exist or is not a recognizable trace file. Or there was an
error opening the file.
Seems like the "fn_trace_gettable" function is locking the trace log file
internally during it's execution. I do not have any alternative other than
executing the query simulateously from multiple clients. Can anyone suggest
a workaround for this problem?
Thanks,
Nilesh|||Narayana,
I am firing this query from two datasets for generating a report (to be
deployed on SQL Reporting server). Unable to think of any workaround for
that. Any help would be greatly appreciated.
thanks,
Nilesh
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:eOsdGr1jEHA.384@.TK2MSFTNGP10.phx.gbl...
> I haven't tested this myself, but definitely seems like a locking issue on
> the file, and can see why that would be the case.
> Can you load the file into one table first, and then use DTS to parallelly
> copy that table to multiple locations?
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "Nilesh" <nilesh.oswal@.gmail.com> wrote in message
> news:%23rXnKvzjEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Hi,
> While executing
> select * from fn_trace_gettable('C:\Program Files\Microsoft SQL
> Server\MSSQL.10\MSSQL\DATA\log.trc', default);
> query simulateneously from multiple clients, only one
> of them succeeds and all others give the following error message:
> File 'C:\Program Files\Microsoft SQL Server\MSSQL.10\MSSQL\DATA\log.trc'
> either does not exist or is not a recognizable trace file. Or there was an
> error opening the file.
> Seems like the "fn_trace_gettable" function is locking the trace log file
> internally during it's execution. I do not have any alternative other than
> executing the query simulateously from multiple clients. Can anyone
suggest
> a workaround for this problem?
> Thanks,
> Nilesh
>
>

Concurrent executions of "fn_trace_gettable" function are failing.

Hi,
While executing
select * from fn_trace_gettable('C:\Program Files\Microsoft SQL
Server\MSSQL.10\MSSQL\DATA\log.trc', default);
query simulateneously from multiple clients, only one
of them succeeds and all others give the following error message:
File 'C:\Program Files\Microsoft SQL Server\MSSQL.10\MSSQL\DATA\log.trc'
either does not exist or is not a recognizable trace file. Or there was an
error opening the file.
Seems like the "fn_trace_gettable" function is locking the trace log file
internally during it's execution. I do not have any alternative other than
executing the query simulateously from multiple clients. Can anyone suggest
a workaround for this problem?
Thanks,
NileshI haven't tested this myself, but definitely seems like a locking issue on
the file, and can see why that would be the case.
Can you load the file into one table first, and then use DTS to parallelly
copy that table to multiple locations?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Nilesh" <nilesh.oswal@.gmail.com> wrote in message
news:%23rXnKvzjEHA.3664@.TK2MSFTNGP12.phx.gbl...
Hi,
While executing
select * from fn_trace_gettable('C:\Program Files\Microsoft SQL
Server\MSSQL.10\MSSQL\DATA\log.trc', default);
query simulateneously from multiple clients, only one
of them succeeds and all others give the following error message:
File 'C:\Program Files\Microsoft SQL Server\MSSQL.10\MSSQL\DATA\log.trc'
either does not exist or is not a recognizable trace file. Or there was an
error opening the file.
Seems like the "fn_trace_gettable" function is locking the trace log file
internally during it's execution. I do not have any alternative other than
executing the query simulateously from multiple clients. Can anyone suggest
a workaround for this problem?
Thanks,
Nilesh|||Narayana,
I am firing this query from two datasets for generating a report (to be
deployed on SQL Reporting server). Unable to think of any workaround for
that. Any help would be greatly appreciated.
thanks,
Nilesh
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:eOsdGr1jEHA.384@.TK2MSFTNGP10.phx.gbl...
> I haven't tested this myself, but definitely seems like a locking issue on
> the file, and can see why that would be the case.
> Can you load the file into one table first, and then use DTS to parallelly
> copy that table to multiple locations?
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "Nilesh" <nilesh.oswal@.gmail.com> wrote in message
> news:%23rXnKvzjEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Hi,
> While executing
> select * from fn_trace_gettable('C:\Program Files\Microsoft SQL
> Server\MSSQL.10\MSSQL\DATA\log.trc', default);
> query simulateneously from multiple clients, only one
> of them succeeds and all others give the following error message:
> File 'C:\Program Files\Microsoft SQL Server\MSSQL.10\MSSQL\DATA\log.trc'
> either does not exist or is not a recognizable trace file. Or there was an
> error opening the file.
> Seems like the "fn_trace_gettable" function is locking the trace log file
> internally during it's execution. I do not have any alternative other than
> executing the query simulateously from multiple clients. Can anyone
suggest
> a workaround for this problem?
> Thanks,
> Nilesh
>
>

Concurrent executions of "fn_trace_gettable" function are failing.

Hi,
While executing
select * from fn_trace_gettable('C:\Program Files\Microsoft SQL
Server\MSSQL.10\MSSQL\DATA\log.trc', default);
query simulateneously from multiple clients, only one
of them succeeds and all others give the following error message:
File 'C:\Program Files\Microsoft SQL Server\MSSQL.10\MSSQL\DATA\log.trc'
either does not exist or is not a recognizable trace file. Or there was an
error opening the file.
Seems like the "fn_trace_gettable" function is locking the trace log file
internally during it's execution. I do not have any alternative other than
executing the query simulateously from multiple clients. Can anyone suggest
a workaround for this problem?
Thanks,
Nilesh
I haven't tested this myself, but definitely seems like a locking issue on
the file, and can see why that would be the case.
Can you load the file into one table first, and then use DTS to parallelly
copy that table to multiple locations?
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Nilesh" <nilesh.oswal@.gmail.com> wrote in message
news:%23rXnKvzjEHA.3664@.TK2MSFTNGP12.phx.gbl...
Hi,
While executing
select * from fn_trace_gettable('C:\Program Files\Microsoft SQL
Server\MSSQL.10\MSSQL\DATA\log.trc', default);
query simulateneously from multiple clients, only one
of them succeeds and all others give the following error message:
File 'C:\Program Files\Microsoft SQL Server\MSSQL.10\MSSQL\DATA\log.trc'
either does not exist or is not a recognizable trace file. Or there was an
error opening the file.
Seems like the "fn_trace_gettable" function is locking the trace log file
internally during it's execution. I do not have any alternative other than
executing the query simulateously from multiple clients. Can anyone suggest
a workaround for this problem?
Thanks,
Nilesh
|||Narayana,
I am firing this query from two datasets for generating a report (to be
deployed on SQL Reporting server). Unable to think of any workaround for
that. Any help would be greatly appreciated.
thanks,
Nilesh
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:eOsdGr1jEHA.384@.TK2MSFTNGP10.phx.gbl...
> I haven't tested this myself, but definitely seems like a locking issue on
> the file, and can see why that would be the case.
> Can you load the file into one table first, and then use DTS to parallelly
> copy that table to multiple locations?
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "Nilesh" <nilesh.oswal@.gmail.com> wrote in message
> news:%23rXnKvzjEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Hi,
> While executing
> select * from fn_trace_gettable('C:\Program Files\Microsoft SQL
> Server\MSSQL.10\MSSQL\DATA\log.trc', default);
> query simulateneously from multiple clients, only one
> of them succeeds and all others give the following error message:
> File 'C:\Program Files\Microsoft SQL Server\MSSQL.10\MSSQL\DATA\log.trc'
> either does not exist or is not a recognizable trace file. Or there was an
> error opening the file.
> Seems like the "fn_trace_gettable" function is locking the trace log file
> internally during it's execution. I do not have any alternative other than
> executing the query simulateously from multiple clients. Can anyone
suggest
> a workaround for this problem?
> Thanks,
> Nilesh
>
>

Conceptual and speed question

Conceptual and speed question
Hi,
My program seems to slow down drastically because as I fill my array and
table with many values, the program suffers tremendously. The first thing my
program does is to search the jagged array to try to find an element in that
array. If it does not find that element in that array, then it adds another
element and that is the problem. Once I have many elements in that array, it
takes a long time to do a search. Furthermore, not only do I add an element
to that array if it does not find it, but I also add it to my table and as
my table gets bigger, it also slows everything down drastically.
What I thought may be possible is to create many arrays instead of only 1
array and that would speed up the search. The problem is that I do not know
what the size of each array will be at the beginning as some of the arrays
might be much larger than others. Is there a way to declare arrays and then
to dynamically increase their size when needed, and if it is possible would
this hamper the performance because my arrays will be quite large. I also
thought of maybe sorting the array, but that would take too long. Is there a
way to index an array to speed up the search?
As for my table in the database, I was wandering if I created many tables
would that also help speed up the process or will it not make a difference
because it is in the same database. And if that is the case that it does not
make a difference in speed because it is in the same database, then can I
save it to a different database? Another possibility I thought of is to save
the rows to another database and delete the rows in original database after
a certain number of rows have been added.
TIA
Roy"Roy Gourgi" <royng@.videotron.ca> wrote in message
news:qKrff.23031$AZ3.235633@.wagner.videotron.net...
> Conceptual and speed question
>
> Hi,
>
> My program seems to slow down drastically because as I fill my array and
> table with many values, the program suffers tremendously. The first thing
> my program does is to search the jagged array to try to find an element in
> that array. If it does not find that element in that array, then it adds
> another element and that is the problem. Once I have many elements in that
> array, it takes a long time to do a search. Furthermore, not only do I add
> an element to that array if it does not find it, but I also add it to my
> table and as my table gets bigger, it also slows everything down
> drastically.
>
> What I thought may be possible is to create many arrays instead of only 1
> array and that would speed up the search. The problem is that I do not
> know what the size of each array will be at the beginning as some of the
> arrays might be much larger than others. Is there a way to declare arrays
> and then to dynamically increase their size when needed, and if it is
> possible would this hamper the performance because my arrays will be quite
> large. I also thought of maybe sorting the array, but that would take too
> long. Is there a way to index an array to speed up the search?
>
> As for my table in the database, I was wandering if I created many tables
> would that also help speed up the process or will it not make a difference
> because it is in the same database. And if that is the case that it does
> not make a difference in speed because it is in the same database, then
> can I save it to a different database? Another possibility I thought of is
> to save the rows to another database and delete the rows in original
> database after a certain number of rows have been added.
>
> TIA
> Roy
>
I'll skip the question about arrays. There are no arrays in SQL Server and
you didn't mention what other product you might be using.
On the database side you should first be considering what indexes might
support your application better. Indexes are likely to have much more impact
on performance than splitting the table up.
David Portas
SQL Server MVP
--|||Array, what programming language are you using? This group is for SQL Server
T-SQL.
Loading a large resultset into an application array is very slow, especially
if you are using something like ReDim to increase the size of the array each
time you loop through a client side cursor. Whatever, you are doing with an
array, consider just using an ADO recordset.
"Roy Gourgi" <royng@.videotron.ca> wrote in message
news:qKrff.23031$AZ3.235633@.wagner.videotron.net...
> Conceptual and speed question
>
> Hi,
>
> My program seems to slow down drastically because as I fill my array and
> table with many values, the program suffers tremendously. The first thing
> my program does is to search the jagged array to try to find an element in
> that array. If it does not find that element in that array, then it adds
> another element and that is the problem. Once I have many elements in that
> array, it takes a long time to do a search. Furthermore, not only do I add
> an element to that array if it does not find it, but I also add it to my
> table and as my table gets bigger, it also slows everything down
> drastically.
>
> What I thought may be possible is to create many arrays instead of only 1
> array and that would speed up the search. The problem is that I do not
> know what the size of each array will be at the beginning as some of the
> arrays might be much larger than others. Is there a way to declare arrays
> and then to dynamically increase their size when needed, and if it is
> possible would this hamper the performance because my arrays will be quite
> large. I also thought of maybe sorting the array, but that would take too
> long. Is there a way to index an array to speed up the search?
>
> As for my table in the database, I was wandering if I created many tables
> would that also help speed up the process or will it not make a difference
> because it is in the same database. And if that is the case that it does
> not make a difference in speed because it is in the same database, then
> can I save it to a different database? Another possibility I thought of is
> to save the rows to another database and delete the rows in original
> database after a certain number of rows have been added.
>
> TIA
> Roy
>|||Hi,
Sorry, I am using C# and SQL Server 2005 Express edition.

> On the database side you should first be considering what indexes might
> support your application better. Indexes are likely to have much more
> impact on performance than splitting the table up.
What do you mean by indexes. I am not searching the database, as I am only
adding a row sequentially. The only search that I am doing is in my jagged
array and if the search fails to find the element in the array, then it is
added to the array and then to the table. So you see, I am just using the
table as a permanent repository, the search is conducted in the database.
The problem is that I have millions of rows to fill and as the table gets
larger, it slows down drastically.
Any suggestions.
Thanks
Roy
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:xJadnQanRPrN0ePeRVnyjA@.giganews.com...
> "Roy Gourgi" <royng@.videotron.ca> wrote in message
> news:qKrff.23031$AZ3.235633@.wagner.videotron.net...
> I'll skip the question about arrays. There are no arrays in SQL Server and
> you didn't mention what other product you might be using.
> On the database side you should first be considering what indexes might
> support your application better. Indexes are likely to have much more
> impact on performance than splitting the table up.
> --
> David Portas
> SQL Server MVP
> --
>|||Sorry there is an error, as this:
So you see, I am just using the table as a permanent repository, the search
is conducted in the database.
should be:
So you see, I am just using the table as a permanent repository, the search
is conducted in the array.
Roy
"Roy Gourgi" <royng@.videotron.ca> wrote in message
news:ufsff.23045$AZ3.243450@.wagner.videotron.net...
> Hi,
> Sorry, I am using C# and SQL Server 2005 Express edition.
>
> What do you mean by indexes. I am not searching the database, as I am only
> adding a row sequentially. The only search that I am doing is in my jagged
> array and if the search fails to find the element in the array, then it is
> added to the array and then to the table. So you see, I am just using the
> table as a permanent repository, the search is conducted in the database.
> The problem is that I have millions of rows to fill and as the table gets
> larger, it slows down drastically.
> Any suggestions.
> Thanks
> Roy
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:xJadnQanRPrN0ePeRVnyjA@.giganews.com...
>|||"Roy Gourgi" <royng@.videotron.ca> wrote in message
news:ufsff.23045$AZ3.243450@.wagner.videotron.net...
> Hi,
> Sorry, I am using C# and SQL Server 2005 Express edition.
>
> What do you mean by indexes. I am not searching the database, as I am only
> adding a row sequentially. The only search that I am doing is in my jagged
> array and if the search fails to find the element in the array, then it is
> added to the array and then to the table. So you see, I am just using the
> table as a permanent repository, the search is conducted in the database.
> The problem is that I have millions of rows to fill and as the table gets
> larger, it slows down drastically.
> Any suggestions.
> Thanks
> Roy
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:xJadnQanRPrN0ePeRVnyjA@.giganews.com...
>
If you really don't know what indexes are then you have a LOT to learn. I
suggest you purchase a book or take a course in that case. Newsgroups are
not the place for a tutorial.
How do you explain: "I am not searching the database, as I am only adding a
row sequentially" followed by: "the search is conducted in the database"? So
are you searching or not? Common sense says that if you are generating
millions of rows then you will want to search them at some point. For that
indexes will be essential. However, generating large quantities of data on a
desktop spec machine (if that is in fact what you are doing with SQL
Express) is likely to result in a lot of RAM paging to disk. SQL Server
works best with lots of RAM to itself.
David Portas
SQL Server MVP
--|||"Roy Gourgi" <royng@.videotron.ca> wrote in message
news:phsff.23046$AZ3.243820@.wagner.videotron.net...
> Sorry there is an error, as this:
> So you see, I am just using the table as a permanent repository, the
> search is conducted in the database.
> should be:
> So you see, I am just using the table as a permanent repository, the
> search is conducted in the array.
> Roy
> "Roy Gourgi" <royng@.videotron.ca> wrote in message
> news:ufsff.23045$AZ3.243450@.wagner.videotron.net...
>
You mean you persist the entire set of data in the array AND in the
database? That's going to be extremely inefficient. On a single box both
will compete for resources. Search in the DATABASE - that's what it's for.
If you just want a bit bucket then write to a file not to SQL Server.
David Portas
SQL Server MVP
--|||Hi,
If you saw my next post I said that I made an error and that it should have
been the search is conducted in the array and not database.
I will take your advice and try adding some more memory, as I think that may
be one of the problems.
Thanks
Roy

> If you really don't know what indexes are then you have a LOT to learn. I
> suggest you purchase a book or take a course in that case. Newsgroups are
> not the place for a tutorial.
> How do you explain: "I am not searching the database, as I am only adding
> a row sequentially" followed by: "the search is conducted in the
> database"? So are you searching or not? Common sense says that if you are
> generating millions of rows then you will want to search them at some
> point. For that indexes will be essential. However, generating large
> quantities of data on a desktop spec machine (if that is in fact what you
> are doing with SQL Express) is likely to result in a lot of RAM paging to
> disk. SQL Server works best with lots of RAM to itself.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:EdidnYWlGfGpzuPeRVnyrg@.giganews.com...
> "Roy Gourgi" <royng@.videotron.ca> wrote in message
> news:ufsff.23045$AZ3.243450@.wagner.videotron.net...
> If you really don't know what indexes are then you have a LOT to learn. I
> suggest you purchase a book or take a course in that case. Newsgroups are
> not the place for a tutorial.
> How do you explain: "I am not searching the database, as I am only adding
> a row sequentially" followed by: "the search is conducted in the
> database"? So are you searching or not? Common sense says that if you are
> generating millions of rows then you will want to search them at some
> point. For that indexes will be essential. However, generating large
> quantities of data on a desktop spec machine (if that is in fact what you
> are doing with SQL Express) is likely to result in a lot of RAM paging to
> disk. SQL Server works best with lots of RAM to itself.
> --
> David Portas
> SQL Server MVP
> --
>|||Hi,
I only search the value in the array first. If the value is not found, then
I added to the array and then to the table. So you see I need the search the
array to determine whether I have to add it to the table or not.
I thought that it would be much slower if I searched the database rather
than the array, correct me if I am wrong. Furthermore, you say that if I
only want to save it then I should use a normal text file. Would that make
it faster than saving it to a database.
Thanks
Roy

> You mean you persist the entire set of data in the array AND in the
> database? That's going to be extremely inefficient. On a single box both
> will compete for resources. Search in the DATABASE - that's what it's for.
> If you just want a bit bucket then write to a file not to SQL Server.
> --
> David Portas
> SQL Server MVP
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:D-2dnUa4HoOdyePeRVnyuA@.giganews.com...
> "Roy Gourgi" <royng@.videotron.ca> wrote in message
> news:phsff.23046$AZ3.243820@.wagner.videotron.net...
> You mean you persist the entire set of data in the array AND in the
> database? That's going to be extremely inefficient. On a single box both
> will compete for resources. Search in the DATABASE - that's what it's for.
> If you just want a bit bucket then write to a file not to SQL Server.
> --
> David Portas
> SQL Server MVP
> --
>|||"Roy Gourgi" <royng@.videotron.ca> wrote in message
news:gDsff.23050$AZ3.249690@.wagner.videotron.net...
> Hi,
> I only search the value in the array first. If the value is not found,
> then I added to the array and then to the table. So you see I need the
> search the array to determine whether I have to add it to the table or
> not.
> I thought that it would be much slower if I searched the database rather
> than the array, correct me if I am wrong. Furthermore, you say that if I
> only want to save it then I should use a normal text file. Would that make
> it faster than saving it to a database.
> Thanks
> Roy
>
>
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:D-2dnUa4HoOdyePeRVnyuA@.giganews.com...
>

> I only search the value in the array first. If the value is not found,
> then I added to the array and then to the table. So you see I need the
> search the array to determine whether I have to add it to the table or
> not.
>
You can do that in one operation if you use the database:
INSERT INTO your_table (x, y, ...)
SELECT ?, ?, ...
WHERE NOT EXISTS
(SELECT *
FROM your_table
WHERE x = ?
AND y = ?) ;
If x and y are unique and indexed then this is very efficient.

> I thought that it would be much slower if I searched the database rather
> than the array, correct me if I am wrong. Furthermore, you say that if I
> only want to save it then I should use a normal text file. Would that make
> it faster than saving it to a database.
SQL Server is an extremely efficient way to search and process large
quantities of data. What is inefficient is to keep retrieving the data for
processing outside the database and then to persist large data sets outside
the database as well. Why can't you implement whatever your array is doing
in the database itself? Of course I can't be certain that it will be more
efficient that way but I am pretty certain that there must be a more
efficient method than what you are describing.
David Portas
SQL Server MVP
--