Maintaining Variable After EXEC

Hello,

I am fairly new at stored procedures. I have created some that will
go through a table and return a start date and an end date that is
dependent upon the fiscal period you want, but I then need to use
those dates in another stored procedure to retrieve the information I
need. My stored procedure looks like this.

================================================== ====================

CREATE PROCEDURE dbo.R920ExtTotal
@.MthsBack Decimal OUTPUT
AS

DECLARE @.sSQL AS NVARCHAR(255), @.StartDate as SMALLDATETIME, @.EndDate
as SMALLDATETIME

Exec @.StartDate = GetMthStart @.MthsBack

Exec @.EndDate = GetMthEnd @.MthsBack

SET @.sSQL = 'Select count(extension) as Total From r920f00 Where
([date] BETWEEN "' +
CONVERT(nvarchar, @.StartDate) +
'" and "' +
CONVERT(nvarchar, @.EndDate) +
'")'

Select @.sSQL

EXEC (@.sSQL)

Return
GO

================================================== ===============

The problem is my variables @.StartDate and @.EndDate do not retain
their values after the EXEC statement and revert to 01/01/1900. How
can I get around this problem?

Thanks!!!!

ChipVariables are only available in the scope within which they are declared. If
you need to move values out of a stored procedure, you must use output
parameters for all of them. Also, why are you building dynamic SQL in your
procedure? You can use the variables directly in SQL queries - there is no
need to convert them to strings, and insert them into a SQL string.

On 6 Jan 2004 11:05:01 -0800, cmayan@.lesliecontrols.com (Chip Mayan) wrote:

>Hello,
>I am fairly new at stored procedures. I have created some that will
>go through a table and return a start date and an end date that is
>dependent upon the fiscal period you want, but I then need to use
>those dates in another stored procedure to retrieve the information I
>need. My stored procedure looks like this.
>================================================== ====================
>CREATE PROCEDURE dbo.R920ExtTotal
>@.MthsBack Decimal OUTPUT
>AS
>DECLARE @.sSQL AS NVARCHAR(255), @.StartDate as SMALLDATETIME, @.EndDate
>as SMALLDATETIME
>Exec @.StartDate = GetMthStart @.MthsBack
>Exec @.EndDate = GetMthEnd @.MthsBack
>SET @.sSQL = 'Select count(extension) as Total From r920f00 Where
>([date] BETWEEN "' +
>CONVERT(nvarchar, @.StartDate) +
>'" and "' +
>CONVERT(nvarchar, @.EndDate) +
>'")'
>Select @.sSQL
>EXEC (@.sSQL)
>Return
>GO
>================================================== ===============
>The problem is my variables @.StartDate and @.EndDate do not retain
>their values after the EXEC statement and revert to 01/01/1900. How
>can I get around this problem?
>Thanks!!!!
>Chip|||Chip Mayan (cmayan@.lesliecontrols.com) writes:
> I am fairly new at stored procedures. I have created some that will
> go through a table and return a start date and an end date that is
> dependent upon the fiscal period you want, but I then need to use
> those dates in another stored procedure to retrieve the information I
> need. My stored procedure looks like this.
>================================================== ====================
> CREATE PROCEDURE dbo.R920ExtTotal
> @.MthsBack Decimal OUTPUT
> AS
> DECLARE @.sSQL AS NVARCHAR(255), @.StartDate as SMALLDATETIME, @.EndDate
> as SMALLDATETIME
> Exec @.StartDate = GetMthStart @.MthsBack
> Exec @.EndDate = GetMthEnd @.MthsBack
> SET @.sSQL = 'Select count(extension) as Total From r920f00 Where
> ([date] BETWEEN "' +
> CONVERT(nvarchar, @.StartDate) +
> '" and "' +
> CONVERT(nvarchar, @.EndDate) +
> '")'
> Select @.sSQL
> EXEC (@.sSQL)
> Return
> GO

I'm afraid that there are a couple of errors or strange things in this
procedure.

First: there is absolutely no reason to use dynamic SQL here. Just write:

SELKCT count(extension) AS Total
FROM r920f00
WHERE [date] BETWEEN @.StartDate AND @.EndDate

Second: the calls to set @.StartDate and @.EndDate looks funny. If
GetMthStart and GetMthEnd are user-defined functions it would be alright,
but you indicated that they were stored procedures. The return value from
a stored procedure is always an integer value, so you cannot return a
date here. And I would strongly recommend you to use return values solely
for indication of success/failure (with 0 meaning success, and about
everything else meaning failure.) So you would have to make the output
parameters:

EXEC GetMthStart @.MthsBack, @.StartDate OUTPUT

Third: the @.MthsBack parameter is declared as output, but you never assign
it any value, you only seem to use it for input.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

maintaining unique keys when offline

If you have a "Orders" table that is being sync'd to subscribers that are ocassionaly offline, and the subscribers add rows to their local Orders table. When they go online to sync with the published "Orders" table, how do you handle keeping the "OrderId" field unique?

Example:

Both salespeople sync the following data down:

OrderId Desc

1 Order 1

2 Test Order

Both salespeople go offline and add orders

Salesperson 1 adds:

OrderId Desc

3 Joes Order

Salesperson 2 adds:

OrderId Desc

3 Kathys Order

Now, when they go back online, they both will sync their orders up to the main database and they both have the OrderId of 3.

The main problem with using an int identity as a primary key is that it gets assigned by the database on insert; as you're discovering, assigning it outside of the database creates key collisions.

There are several different approaches you can use. All of them have problems:

1) If the row contains some combination of values that are always unique, use this combination as the primary key.

Problems with this approach: Not always possible. If the table's going to be referenced as part of a foreign-key relationship, you have to replicate all of the parts of the key in the referencing rows.

2) Use a composite primary key with two columns, or a primary key that's a concatenation of two values. One is a token that's unique to each process that's creating records; the second is a sequentially-assigned value that each process is responsible for assigning. In your example, you'd use the salesperson as the token. So you could either make salesperson, order id the primary key, or you could create nvarchar keys like "joe:1".

Problems with this approach: The token has to be invariant, i.e. changing the salesperson on the order can create key collisions. The database can no longer assign primary keys, since the PKs are being assigned offline, outside of the database.

3) Use a primary key of type uniqueidentifier (i.e.a GUID). All GUIDs are unique, so you'll never have a key collision.

Problems with this approach: your identifier won't be usable by humans. Sorting by GUIDs is useless. GUIDs use 32 bytes of storage, as opposed to 4 for int.

4) Use a temporary local key (using either of the above 2 approaches) when creating records on the client, and assign the real key when the row is inserted.

Problems with this approach: The client won't automatically know what the keys are of the rows it just inserted into the database, and will have to re-query the database to get their values.

|||

SQL replication also has the ability to assign identity ranges to subscribers. You might want to look into it as a solution. The only problem is in a high subscriber, high volume scenario is assigning an appropriate range to each subscriber.

I looked into this solution for the company I currently work for.We decided using a GUID was a much better solution.It eliminates the need to monitor the identity ranges.

Maintaining a one-to-one match on related tables

I have a series of tables already containing data. The first table is a list
of employees. The next is a list of team names.
There is a Primary Key for each employee which relates to a foreign key in
the team names.
The employee table would have
pkid
FirstName
LastName
fkOfficeID
department
The team table would have only
fkEmployeeID
TeamName
Now, I know how to generate a SELECT set with a LEFT JOIN and a search for
NULL in the TeamName to generate a list of employees that do not have a team
name assigned to them, however. Not every employee would have a coresponding
record in the team table, only certain offices have teams.
What is the statement that would:
"INSERT and UPDATE a specific team name record in the team table for
each corresponding record in the employee table for any employee who's
"Office ID" is 5 department is Accounting and is missing a record in the
team table."
I could VB my way through this by
Creating the list of all offices that have teams
Creating the SELECT set mentioned above and whittling it down to
those employees in each office who are in Accounting
IF NOT EXISTS-ing my way through the team list and adding the
appopriate team record
But, is there an easier way? Is it a two step process, one to determine the
missing records in the one-to-one relationship where needed and then do the
team table update?
Julianand the vb programmers here wonder why vb makes me pull my hair out... ;)
insert into team (fkEmployeeID, TeamName)
select e.pkid, 'Office 5 Accounting Team 1'
from employee e
where fkofficeid=5
and department='Accounting'
and not exists (select * from team where fkemployeeid=e.pkid)
not sure what you need to update...
stjulian wrote:
> I have a series of tables already containing data. The first table is a li
st
> of employees. The next is a list of team names.
> There is a Primary Key for each employee which relates to a foreign key in
> the team names.
> The employee table would have
> pkid
> FirstName
> LastName
> fkOfficeID
> department
> The team table would have only
> fkEmployeeID
> TeamName
>
> Now, I know how to generate a SELECT set with a LEFT JOIN and a search for
> NULL in the TeamName to generate a list of employees that do not have a te
am
> name assigned to them, however. Not every employee would have a corespondi
ng
> record in the team table, only certain offices have teams.
> What is the statement that would:
> "INSERT and UPDATE a specific team name record in the team table for
> each corresponding record in the employee table for any employee who's
> "Office ID" is 5 department is Accounting and is missing a record in the
> team table."
> I could VB my way through this by
> Creating the list of all offices that have teams
> Creating the SELECT set mentioned above and whittling it down to
> those employees in each office who are in Accounting
> IF NOT EXISTS-ing my way through the team list and adding the
> appopriate team record
> But, is there an easier way? Is it a two step process, one to determine th
e
> missing records in the one-to-one relationship where needed and then do th
e
> team table update?
> Julian
>
>|||I think like a VB programmer, you know, linearly. What I love about you guys
in SQL is your way of looking at data in 3 dimensions. Folding it on itself
(using table aliases).
Should the line be "from employee AS e" ?
I should take a class on query design.
I'll give it a try tomorrow. Keep an eye on this thread.
Julian
"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:eETCybEAGHA.1268@.TK2MSFTNGP11.phx.gbl...
> and the vb programmers here wonder why vb makes me pull my hair out... ;)
> insert into team (fkEmployeeID, TeamName)
> select e.pkid, 'Office 5 Accounting Team 1'
> from employee e
> where fkofficeid=5
> and department='Accounting'
> and not exists (select * from team where fkemployeeid=e.pkid)
>
> not sure what you need to update...
> stjulian wrote:|||Although the issues are 2D, thinking 3D is more impressive. :)
ML
http://milambda.blogspot.com/|||I prefer thinking of it as
VB = "For each x, do this"
SQL = "For all x's, do this"
the AS is optional in aliasing a table or column.
my personal preference is to include AS for column aliases and not for
table aliases.
stjulian wrote:
> I think like a VB programmer, you know, linearly. What I love about you gu
ys
> in SQL is your way of looking at data in 3 dimensions. Folding it on itsel
f
> (using table aliases).
> Should the line be "from employee AS e" ?
> I should take a class on query design.
> I'll give it a try tomorrow. Keep an eye on this thread.
> Julian
>
> "Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
> news:eETCybEAGHA.1268@.TK2MSFTNGP11.phx.gbl...
>

Maintain separate table via trigger vs. indexed view

Hello,
I have a table LargeTable in many columns and many rows. However, I also
need to have a small subset of the rows in LargeTable and only data from
small set of columns; call it SmallTable.
SmallTable gets read very often. So basically I've been using trigger on
LargeTable to watch for any change there to populate the SmallTable.
However, I realized that an indexed view can replace SmallTable.
In general, which one would be a better design in term of performance? I
know that indexed view can be more elegant but I'm interested in
performance. thanks!"Zester" <zeze@.nottospam.com> wrote in message
news:OaN3Tf3BIHA.5360@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I have a table LargeTable in many columns and many rows. However, I also
> need to have a small subset of the rows in LargeTable and only data from
> small set of columns; call it SmallTable.
> SmallTable gets read very often. So basically I've been using trigger on
> LargeTable to watch for any change there to populate the SmallTable.
> However, I realized that an indexed view can replace SmallTable.
> In general, which one would be a better design in term of performance? I
> know that indexed view can be more elegant but I'm interested in
> performance. thanks!
>
>
What makes you think this is a candidate for an indexed view? You could
create an ordinary view and put a relevant nonclustered index on the base
table. That way you won't incur the same write overhead that an indexed view
has. In SQL Server 2005 you can also include non-key columns in a
nonclustered index.
--
David Portas|||the LargeTable gets read even more often than the SmallTable and LargeTable
serves the main feature of our product. Writing to it is not often but does
occur - most likely only to the columns that have nothing to do with the
dataset needed for SmallTable. The feature using SmallTable is minor, so we
don't want it to interfere with the main feature that needs LargeTable.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:uKBFF$3BIHA.3564@.TK2MSFTNGP04.phx.gbl...
> "Zester" <zeze@.nottospam.com> wrote in message
> news:OaN3Tf3BIHA.5360@.TK2MSFTNGP03.phx.gbl...
>> Hello,
>> I have a table LargeTable in many columns and many rows. However, I also
>> need to have a small subset of the rows in LargeTable and only data from
>> small set of columns; call it SmallTable.
>> SmallTable gets read very often. So basically I've been using trigger on
>> LargeTable to watch for any change there to populate the SmallTable.
>> However, I realized that an indexed view can replace SmallTable.
>> In general, which one would be a better design in term of performance? I
>> know that indexed view can be more elegant but I'm interested in
>> performance. thanks!
>>
> What makes you think this is a candidate for an indexed view? You could
> create an ordinary view and put a relevant nonclustered index on the base
> table. That way you won't incur the same write overhead that an indexed
> view has. In SQL Server 2005 you can also include non-key columns in a
> nonclustered index.
> --
> David Portas
>

Maintain History

Hi All
I have a table that contains details regarding branch offices and as these
offices regularly change details such as the branch name and address I need
to always have the current details and also maintain a history of historical
details for each branch office. Initially I was think of having a duplicate
table with a datetime column to store when the last change was made and also
a column that contained the details of who changed the branch office details.
I was going to control this through the application but have identified that
there is any additional vendor application that also change details in this
table and the application can not be modified.
Is the best way to maintain a history of branch office details in this
scenario to create a trigger on the table and populate a duplicate table with
the details of the branch office as they where before being modified or is
there a better way to do this?
Thanks
- DavidHi David
There is a simple auditing example (E) in the Create Trigger topic in books
online. There may be an impact on the system if you implement these on all
your tables, in both the amount of storage used and time taken to make
changes. To reduce the performance degredation it is best to keep the trigger
as simple as you can , if any reconciliation is necessary then you can do
that during a quiet period of possibly offline.
Alternatively you can purchase a log reading program, you may want to log at
LogExplorer from Lumigent www.lumigent.com or LogPI which has been aquired
by Goldengate see www.logpi.com
John
"David" wrote:
> Hi All
> I have a table that contains details regarding branch offices and as these
> offices regularly change details such as the branch name and address I need
> to always have the current details and also maintain a history of historical
> details for each branch office. Initially I was think of having a duplicate
> table with a datetime column to store when the last change was made and also
> a column that contained the details of who changed the branch office details.
> I was going to control this through the application but have identified that
> there is any additional vendor application that also change details in this
> table and the application can not be modified.
> Is the best way to maintain a history of branch office details in this
> scenario to create a trigger on the table and populate a duplicate table with
> the details of the branch office as they where before being modified or is
> there a better way to do this?
> Thanks
> - David

Maintain History

Hi All
I have a table that contains details regarding branch offices and as these
offices regularly change details such as the branch name and address I need
to always have the current details and also maintain a history of historical
details for each branch office. Initially I was think of having a duplicate
table with a datetime column to store when the last change was made and also
a column that contained the details of who changed the branch office details
.
I was going to control this through the application but have identified that
there is any additional vendor application that also change details in this
table and the application can not be modified.
Is the best way to maintain a history of branch office details in this
scenario to create a trigger on the table and populate a duplicate table wit
h
the details of the branch office as they where before being modified or is
there a better way to do this?
Thanks
- DavidHi David
There is a simple auditing example (E) in the Create Trigger topic in books
online. There may be an impact on the system if you implement these on all
your tables, in both the amount of storage used and time taken to make
changes. To reduce the performance degredation it is best to keep the trigge
r
as simple as you can , if any reconciliation is necessary then you can do
that during a quiet period of possibly offline.
Alternatively you can purchase a log reading program, you may want to log at
LogExplorer from Lumigent www.lumigent.com or LogPI which has been aquired
by Goldengate see www.logpi.com
John
"David" wrote:

> Hi All
> I have a table that contains details regarding branch offices and as these
> offices regularly change details such as the branch name and address I nee
d
> to always have the current details and also maintain a history of historic
al
> details for each branch office. Initially I was think of having a duplica
te
> table with a datetime column to store when the last change was made and al
so
> a column that contained the details of who changed the branch office detai
ls.
> I was going to control this through the application but have identified t
hat
> there is any additional vendor application that also change details in thi
s
> table and the application can not be modified.
> Is the best way to maintain a history of branch office details in this
> scenario to create a trigger on the table and populate a duplicate table w
ith
> the details of the branch office as they where before being modified or is
> there a better way to do this?
> Thanks
> - David

Main and detail table amount calculations

Hi all,

I have a 2 main tables in my system. One is main table has ticket information location and so on. And detail table which hold all actions on that record with multiple money fields (its around 5 fields). it goes trough some crazy calculations on the detail table and get 5 results for each ticket. And this calculation is done everytime the ticket is looked at.
There is around 10 detail records per 1 main record. and the main table is around 7 mil. records

Now i got 2 options. I can create a trigger that does the calculation and update the main table show the results from there. This is going to affect all updates deletes and inserts.

OR

I can leave as it is so only when they view the ticket it does the calculation. Dont ask me how often they view it i dont have a clue.( if there is any suggestion how to get the count i could try to do it. )
:confused:
Suggestions

(My attitude is going towards the trigger way but 100 percent sure)I can leave as it is so only when they view the ticket it does the calculation.
Yes, you really only want to calculate results from values that are stored in a database and return those results to the client app/report/whatever. Storing calculated values is usually a bad idea. If I were you, I'd go with a third table that details each money field. So, you have the main table, the table with ticket details, then have another table with money details for the ticket details.

eg. Fields:
TicketID, DetailID, MoneyTypeID, Value
-----------
1,1,1,10.00
1,2,1,30.00
1,2,2,1.00
1,2,3,10.50|||you got the question wrong i really dont have too much of changing the data structure is not an big option since the system is already built. I dont have that much options of adding another detailed money table. I need is to decide if i should use triggers and the main table for the calculations or do the calculations on every view Thanks.|||Dont ask me how often they view it i dont have a clue.( if there is any suggestion how to get the count i could try to do it. )
:confused:
How often do they view it? Oh right right - I wasn't to ask :)

If they are accessing this via a sproc then the easy method is log every execution (in fact do this for every sproc) to a table and then query your table.

If not then you could run a profiler trace (http://www.developer.com/db/article.php/3482216) save the results to a table or to a trace file (trace file is more efficient and you can dump the results in a table once the trace is over) and query the table. You would be querying the TextData column for the SQL:BatchCompleted event.

HTH|||sadly on the server there is a wierd problem. Trace is not working. and lets say for how often they look up a ticket is, insanely often. probably the data is viewed alot more often then it is updated. I hope that make sense to help awnser the main part of the question. Thanks

mail que

hi,
we are having a trigger for update on a table.
this trigger fires the xp_smtp mail session proc.
the problem is that when there are many updates in a short while. (20
updates in a minute) some of the email don't arrive.
the updates are done one by one from a single location.
can it be that the server is too slow, or the proc is too slow to deal with
all the updates?
is there a way to wourkaroun it?
thanks,
prem
Hi
It is best not to call external process from within a trigger as it may hold
resources, is prone to failure and most times you can not handle the error
generated and your batch is rolled back.
Rather, in the trigger, write a row to another table, and have an exernal
process poll the table and send the e-mails.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"prem" wrote:

> hi,
> we are having a trigger for update on a table.
> this trigger fires the xp_smtp mail session proc.
> the problem is that when there are many updates in a short while. (20
> updates in a minute) some of the email don't arrive.
> the updates are done one by one from a single location.
> can it be that the server is too slow, or the proc is too slow to deal with
> all the updates?
> is there a way to wourkaroun it?
> thanks,
> prem
>
>

mail que

hi,
we are having a trigger for update on a table.
this trigger fires the xp_smtp mail session proc.
the problem is that when there are many updates in a short while. (20
updates in a minute) some of the email don't arrive.
the updates are done one by one from a single location.
can it be that the server is too slow, or the proc is too slow to deal with
all the updates?
is there a way to wourkaroun it?
thanks,
premHi
It is best not to call external process from within a trigger as it may hold
resources, is prone to failure and most times you can not handle the error
generated and your batch is rolled back.
Rather, in the trigger, write a row to another table, and have an exernal
process poll the table and send the e-mails.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"prem" wrote:

> hi,
> we are having a trigger for update on a table.
> this trigger fires the xp_smtp mail session proc.
> the problem is that when there are many updates in a short while. (20
> updates in a minute) some of the email don't arrive.
> the updates are done one by one from a single location.
> can it be that the server is too slow, or the proc is too slow to deal wit
h
> all the updates?
> is there a way to wourkaroun it?
> thanks,
> prem
>
>

mail que

hi,
we are having a trigger for update on a table.
this trigger fires the xp_smtp mail session proc.
the problem is that when there are many updates in a short while. (20
updates in a minute) some of the email don't arrive.
the updates are done one by one from a single location.
can it be that the server is too slow, or the proc is too slow to deal with
all the updates?
is there a way to wourkaroun it?
thanks,
premHi
It is best not to call external process from within a trigger as it may hold
resources, is prone to failure and most times you can not handle the error
generated and your batch is rolled back.
Rather, in the trigger, write a row to another table, and have an exernal
process poll the table and send the e-mails.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"prem" wrote:
> hi,
> we are having a trigger for update on a table.
> this trigger fires the xp_smtp mail session proc.
> the problem is that when there are many updates in a short while. (20
> updates in a minute) some of the email don't arrive.
> the updates are done one by one from a single location.
> can it be that the server is too slow, or the proc is too slow to deal with
> all the updates?
> is there a way to wourkaroun it?
> thanks,
> prem
>
>

Machine hangs after SQL QA query

I tried to delete some records from a table that has 10 million records
(delete <table> where xxx= 'yyy'). This does not have any matching index. So
it used up all the memory (500 MB) ,and the swap(virtual mem), and the whole
machine hanged.
Then today, I stopped the query, and waited for hours...
When I rebooted, it took hours to come up, and the sqlsrvr runs with 50% CPU
and huge memory (both). I believe its still rolling back the 'delete'
transactions.
Is there any way to clean up the resource, and start using the machine in a
normal way? (Im also in the process of increasing the memory to atleast 2GB).
Pl. note that I may not be able to open the EM or QA due to the underlying
heavy load/recovery process(?)...
Thanks a lot
Philipus
Message posted via http://www.droptable.com
Additional Info:
This host has AntiVirus enabled. But I dont think the MDB etc. are excluded
from scanning. But I dont think the QA query mentioned would have anything to
do with AV scan! Does it? What kind of configuration does the AV needs so it
both does its work properly, but at the same time does not hinder my SQL
server work?
-philipus
Philipus wrote:
>I tried to delete some records from a table that has 10 million records
>(delete <table> where xxx= 'yyy'). This does not have any matching index. So
>it used up all the memory (500 MB) ,and the swap(virtual mem), and the whole
>machine hanged.
>Then today, I stopped the query, and waited for hours...
>When I rebooted, it took hours to come up, and the sqlsrvr runs with 50% CPU
>and huge memory (both). I believe its still rolling back the 'delete'
>transactions.
>Is there any way to clean up the resource, and start using the machine in a
>normal way? (Im also in the process of increasing the memory to atleast 2GB).
>Pl. note that I may not be able to open the EM or QA due to the underlying
>heavy load/recovery process(?)...
>Thanks a lot
>Philipus
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200507/1
|||Can you open the SQL Server error log from the windows explorer? If the
database is still recovering you will see messages related to that in the
error log. I think you should just let the database recover.
Do you happen to have a backup that is good enough to replace the current
database?
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Philipus via droptable.com" <forum@.droptable.com> wrote in message
news:51A7C2ADFB58F@.droptable.com...
I tried to delete some records from a table that has 10 million records
(delete <table> where xxx= 'yyy'). This does not have any matching index. So
it used up all the memory (500 MB) ,and the swap(virtual mem), and the whole
machine hanged.
Then today, I stopped the query, and waited for hours...
When I rebooted, it took hours to come up, and the sqlsrvr runs with 50% CPU
and huge memory (both). I believe its still rolling back the 'delete'
transactions.
Is there any way to clean up the resource, and start using the machine in a
normal way? (Im also in the process of increasing the memory to atleast
2GB).
Pl. note that I may not be able to open the EM or QA due to the underlying
heavy load/recovery process(?)...
Thanks a lot
Philipus
Message posted via http://www.droptable.com
|||Since I was not able to do anything in the machine, I just left it for
another day, and it came up after clearing the rollback. Now its normal. The
error log has a huge list of ' roll back...recovering...'
Thanks reddy!
philipus
Narayana Vyas Kondreddi wrote:
>Can you open the SQL Server error log from the windows explorer? If the
>database is still recovering you will see messages related to that in the
>error log. I think you should just let the database recover.
>Do you happen to have a backup that is good enough to replace the current
>database?
>I tried to delete some records from a table that has 10 million records
>(delete <table> where xxx= 'yyy'). This does not have any matching index. So
>it used up all the memory (500 MB) ,and the swap(virtual mem), and the whole
>machine hanged.
>Then today, I stopped the query, and waited for hours...
>When I rebooted, it took hours to come up, and the sqlsrvr runs with 50% CPU
>and huge memory (both). I believe its still rolling back the 'delete'
>transactions.
>Is there any way to clean up the resource, and start using the machine in a
>normal way? (Im also in the process of increasing the memory to atleast
>2GB).
>Pl. note that I may not be able to open the EM or QA due to the underlying
>heavy load/recovery process(?)...
>Thanks a lot
>Philipus
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200507/1

Machine hangs after SQL QA query

I tried to delete some records from a table that has 10 million records
(delete <table> where xxx= 'yyy'). This does not have any matching index. So
it used up all the memory (500 MB) ,and the swap(virtual mem), and the whole
machine hanged.
Then today, I stopped the query, and waited for hours...
When I rebooted, it took hours to come up, and the sqlsrvr runs with 50% CPU
and huge memory (both). I believe its still rolling back the 'delete'
transactions.
Is there any way to clean up the resource, and start using the machine in a
normal way? (Im also in the process of increasing the memory to atleast 2GB).
Pl. note that I may not be able to open the EM or QA due to the underlying
heavy load/recovery process(?)...
Thanks a lot
Philipus
--
Message posted via http://www.sqlmonster.comAdditional Info:
This host has AntiVirus enabled. But I dont think the MDB etc. are excluded
from scanning. But I dont think the QA query mentioned would have anything to
do with AV scan! Does it? What kind of configuration does the AV needs so it
both does its work properly, but at the same time does not hinder my SQL
server work?
-philipus
Philipus wrote:
>I tried to delete some records from a table that has 10 million records
>(delete <table> where xxx= 'yyy'). This does not have any matching index. So
>it used up all the memory (500 MB) ,and the swap(virtual mem), and the whole
>machine hanged.
>Then today, I stopped the query, and waited for hours...
>When I rebooted, it took hours to come up, and the sqlsrvr runs with 50% CPU
>and huge memory (both). I believe its still rolling back the 'delete'
>transactions.
>Is there any way to clean up the resource, and start using the machine in a
>normal way? (Im also in the process of increasing the memory to atleast 2GB).
>Pl. note that I may not be able to open the EM or QA due to the underlying
>heavy load/recovery process(?)...
>Thanks a lot
>Philipus
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200507/1|||Can you open the SQL Server error log from the windows explorer? If the
database is still recovering you will see messages related to that in the
error log. I think you should just let the database recover.
Do you happen to have a backup that is good enough to replace the current
database?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Philipus via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:51A7C2ADFB58F@.SQLMonster.com...
I tried to delete some records from a table that has 10 million records
(delete <table> where xxx= 'yyy'). This does not have any matching index. So
it used up all the memory (500 MB) ,and the swap(virtual mem), and the whole
machine hanged.
Then today, I stopped the query, and waited for hours...
When I rebooted, it took hours to come up, and the sqlsrvr runs with 50% CPU
and huge memory (both). I believe its still rolling back the 'delete'
transactions.
Is there any way to clean up the resource, and start using the machine in a
normal way? (Im also in the process of increasing the memory to atleast
2GB).
Pl. note that I may not be able to open the EM or QA due to the underlying
heavy load/recovery process(?)...
Thanks a lot
Philipus
Message posted via http://www.sqlmonster.com|||Since I was not able to do anything in the machine, I just left it for
another day, and it came up after clearing the rollback. Now its normal. The
error log has a huge list of ' roll back...recovering...'
Thanks reddy!
philipus
Narayana Vyas Kondreddi wrote:
>Can you open the SQL Server error log from the windows explorer? If the
>database is still recovering you will see messages related to that in the
>error log. I think you should just let the database recover.
>Do you happen to have a backup that is good enough to replace the current
>database?
>I tried to delete some records from a table that has 10 million records
>(delete <table> where xxx= 'yyy'). This does not have any matching index. So
>it used up all the memory (500 MB) ,and the swap(virtual mem), and the whole
>machine hanged.
>Then today, I stopped the query, and waited for hours...
>When I rebooted, it took hours to come up, and the sqlsrvr runs with 50% CPU
>and huge memory (both). I believe its still rolling back the 'delete'
>transactions.
>Is there any way to clean up the resource, and start using the machine in a
>normal way? (Im also in the process of increasing the memory to atleast
>2GB).
>Pl. note that I may not be able to open the EM or QA due to the underlying
>heavy load/recovery process(?)...
>Thanks a lot
>Philipus
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200507/1

Machine hangs after SQL QA query

I tried to delete some records from a table that has 10 million records
(delete <table> where xxx= 'yyy'). This does not have any matching index. So
it used up all the memory (500 MB) ,and the swap(virtual mem), and the whole
machine hanged.
Then today, I stopped the query, and waited for hours...
When I rebooted, it took hours to come up, and the sqlsrvr runs with 50% CPU
and huge memory (both). I believe its still rolling back the 'delete'
transactions.
Is there any way to clean up the resource, and start using the machine in a
normal way? (Im also in the process of increasing the memory to atleast 2GB)
.
Pl. note that I may not be able to open the EM or QA due to the underlying
heavy load/recovery process(?)...
Thanks a lot
Philipus
Message posted via http://www.droptable.comAdditional Info:
This host has AntiVirus enabled. But I dont think the MDB etc. are excluded
from scanning. But I dont think the QA query mentioned would have anything t
o
do with AV scan! Does it? What kind of configuration does the AV needs so i
t
both does its work properly, but at the same time does not hinder my SQL
server work?
-philipus
Philipus wrote:
>I tried to delete some records from a table that has 10 million records
>(delete <table> where xxx= 'yyy'). This does not have any matching index. S
o
>it used up all the memory (500 MB) ,and the swap(virtual mem), and the whol
e
>machine hanged.
>Then today, I stopped the query, and waited for hours...
>When I rebooted, it took hours to come up, and the sqlsrvr runs with 50% CP
U
>and huge memory (both). I believe its still rolling back the 'delete'
>transactions.
>Is there any way to clean up the resource, and start using the machine in a
>normal way? (Im also in the process of increasing the memory to atleast 2GB
).
>Pl. note that I may not be able to open the EM or QA due to the underlying
>heavy load/recovery process(?)...
>Thanks a lot
>Philipus
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200507/1|||Can you open the SQL Server error log from the windows explorer? If the
database is still recovering you will see messages related to that in the
error log. I think you should just let the database recover.
Do you happen to have a backup that is good enough to replace the current
database?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Philipus via droptable.com" <forum@.droptable.com> wrote in message
news:51A7C2ADFB58F@.droptable.com...
I tried to delete some records from a table that has 10 million records
(delete <table> where xxx= 'yyy'). This does not have any matching index. So
it used up all the memory (500 MB) ,and the swap(virtual mem), and the whole
machine hanged.
Then today, I stopped the query, and waited for hours...
When I rebooted, it took hours to come up, and the sqlsrvr runs with 50% CPU
and huge memory (both). I believe its still rolling back the 'delete'
transactions.
Is there any way to clean up the resource, and start using the machine in a
normal way? (Im also in the process of increasing the memory to atleast
2GB).
Pl. note that I may not be able to open the EM or QA due to the underlying
heavy load/recovery process(?)...
Thanks a lot
Philipus
Message posted via http://www.droptable.com|||Since I was not able to do anything in the machine, I just left it for
another day, and it came up after clearing the rollback. Now its normal. The
error log has a huge list of ' roll back...recovering...'
Thanks reddy!
philipus
Narayana Vyas Kondreddi wrote:
>Can you open the SQL Server error log from the windows explorer? If the
>database is still recovering you will see messages related to that in the
>error log. I think you should just let the database recover.
>Do you happen to have a backup that is good enough to replace the current
>database?
>I tried to delete some records from a table that has 10 million records
>(delete <table> where xxx= 'yyy'). This does not have any matching index. S
o
>it used up all the memory (500 MB) ,and the swap(virtual mem), and the whol
e
>machine hanged.
>Then today, I stopped the query, and waited for hours...
>When I rebooted, it took hours to come up, and the sqlsrvr runs with 50% CP
U
>and huge memory (both). I believe its still rolling back the 'delete'
>transactions.
>Is there any way to clean up the resource, and start using the machine in a
>normal way? (Im also in the process of increasing the memory to atleast
>2GB).
>Pl. note that I may not be able to open the EM or QA due to the underlying
>heavy load/recovery process(?)...
>Thanks a lot
>Philipus
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200507/1

MAC used as search index for Encrypted data: how secure?

I have read recommendations about searching encrypted data. Typically, they involve creating a MAC (message authentication code) table. One of the elements of that table is a HASH of the encrypted data (plus a Mac key) that is used as an index for searching. Is that HASH as secure as the encrypted data itself, or is this approach less secure? If it is less secure, then may I assume that approach is the only feasible way to search data encrypted by nondeterministic algorithms?

TIA,

Barkingdog

Is the hash as secure as the encrypted data? This is a difficult question, because the answer depends on what you are trying to secure against, and what algorithms you are using, and what vulnerabilities they develop over time. One thing that a hash would disclose, and that encryption would normally not disclose, is data identity. That is, you can encrypt the same piece of data and you won't be able to determine from the resulting blobs whether they are corresponding to identical data, but if you hash the data, the result will be identical.

Strictly speaking, the only way to search encrypted data (and I mean non-deterministic, although I should not need to specify this because encryption is always intended to be a non-deterministic operation) is to decrypt all data and search through the decrypted text. Using hashes is a workaround that allows you to search hashes instead of the encrypted data, to answer an equality search. You can probably devise other alternative search schemes, depending on what searches you want to allow and how much information you are willing to give away.

Thanks
Laurentiu

|||

>>>Using hashes is a workaround

Yes, I agree with that. I'm just concerned that the creation of hashes (out of practical necessity) results in a workable solution because the hash codes are easier to "crack" than the encrypted data itself. Maybe "cracking" the hash can lead to cracking the encrypted data?

Barkingdog.

|||

Strictly speaking, yes it is possible to user a rainbow attack against a pure hash on the data we are trying to protect, especially if the domain of the plaintext is finite and well defined (i.e. SSN, CCN, etc.). For example, I can easily create an offline rainbow table with all possible hashes (SHA1, MD5, etc.) for every possible SSN, once I find your hashed value I just need to find the corresponding value on the rainbow table.

This is the reason why I was suggesting using an HMAC, that way the domain of the plaintext is different and creating a dictionary is far more expensive that the example above. You are still giving away some information to the potential attacker, but the difference is that now there is a random bag of bits of arbitrary length (the HMAC key) combined with the data we want to protect, that way the hash input does not belong to the same domain as the original plaintext.

Assuming that the key is truly random, large enough and well protected, the previous attack is rendered useless, and to the best of our knowledge the attacker will need to brute force all possibilities (i.e. hash( ‘111-111-111’ + 0x00…001 ), hash( ‘111-111-111’ + 0x00…002 ) … hash( ‘999-999-999’ + 0xFF…FFF ), as you can see this is far more expensive, and the larger the key, the better.

Is this better or worse than trying to brute force a symmetric key? That is a really difficult question as Laurentiu mentioned. Hashes are less expensive (computational wise) than decryptions, but there are too many factors to consider (domain of your plaintext, domain of the key, reusing keys, new attack methods being discovered, etc.).

The best option if you want to create an index over the encrypted data may be to create a completely new identifier for it, i.e. a customer ID that is completely unrelated to the data being protected, but we understand this option is not always possible due to business reasons.

I would recommend defining what assets you are trying to protect and against what kind of threats. From there you need to evaluate what options you have to protect against such threats, as well as what mechanisms (preferably use defense in depth) you have to prevent, detect and halt a possible attack, as well as what are the steps to follow after the situation is back under control.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

my data base table is as follow

Hi, my data base table is as follows

PersonID City Sex 1 New York M 2 Boston M 3 Seatle F

and i need to make a report as shown below:

Male

Female Total % Male City



New York 5 8 13 38% Boston 20 35 55 36% Seatle 10 6 16 63%

I'm assuming i need to use a matrix, but how can i get a total and a % column

You can use a standard table for this. To get the total just use an expression to add male and female together. To get the percentage, once again use an expression to evaluate (male/total)*100.

|||Hi sluggy

I am new to reporting services and about to start creating some (matrix) reports. I am interested in your comment that the example above can be created using a tabular report.

I wonder if you could help me out on this scenario:
Say I have a db table with sales for a particular year. (i.e 2003 and 2004). Sol the database table is: field1 = Sales, field2 = Date ( assume the years are static )
Using a tabular report is it possible to calculate the sums for each...? This effectivly what a matrix report would do very well as it allows grouping by columns.

Any response would be greatly appreciated.

Regards,
Neil
|||You could still use a tabular report for eachyear. Matrix reports are when you have information for rows, columns, details (and page header which is optional). In your case, you just have Rows (which will be Date) and Sales (which is column name) and SUM(Sales) group by year (which will be your table details)|||

NeilSt wrote:

Using a tabular report is it possible to calculate the sums for each...?

It sure is - there are many posts on this forum explaining how to do a total row on a table. Totalling is done at the group level, so you can have subtotals within grand totals.

NeilSt wrote:

This effectivly what a matrix report would do very well as it allows grouping by columns.

A matrix is more for when you have a dynamic number of columns. In your prior example, you could use a matrix if Male, Female, Male percentage and Total were on the rows and you had a column grouping on state. But it is just as easy to do it as a table, with Male/Female/etc being the columns and the states being the rows.

|||Hi

The answer to my post seems to assume that the Years field would be a row. I am saying... Row = sales and column = 2003 and 2004. Its more a column group that is required no ? if so how are they done in tabular reports ?

Maybe im missing something...

thanks for the help again.

Neil

Example:
2003 | 2004 | Total

Sales 100 | 50 | 150
now to get the total is fine i guess.. but sales is made up of a number of sales within the year. So its effectivly a sum of all sales in 2003... thats what im trying to work out how to do.

|||

Neil,

You can sum up your sales for all available years in the database in the SQL query itself like this:

(if you have stored the year in a separate column in the table)

SELECT SUM(Sales) AS Sales, Year

FROM SalesTable

GROUP BY Year

(if you have not stored the year in a separate column in the table but in a datetime column, us this)

SELECT SUM(Sales) AS Sales, YEAR(SalesDate) AS Year

FROM SalesTable

GROUP BY YEAR(SalesDate)

and in your matrix, add a row group on Sales field and column group on Year field.

Shyam

|||Shyam,

Yes that is possible, I think when I first responded to this thread, I was mor einterested to know if its possible to do column grouping in tabular reports...? It seems that is what all this comes down to..

Thanks for your help.

Neil
|||Maybe someone can help me decide which style of report makes sense for this mockup of a report:
Columns: Jan - Dec
Rows: City

So lets say we can to find the number of sales for a city form Jan to Dec...
So looking something like,

Jan | Feb | March | April | May | June | Mid Totals | July |August etc... | Totals for full year

nyc: 1 2 3 3 3 3 15 3 4 x
nj: 1 1 1 1 1 1 6 1 1 x
la: 2 2 2 2 2 2 12 2 5 x

So which is the best report type to use.. ? Matrix supports column grouping ... tabular only supports row grouping from what I can tell...

each record in the db contains a month , sale value and city...

I leave it up to you all to let me know which one is best suited (tabular/matrix)

Neil
|||

Of course Matrix is suitable. But your report layout woulc make more sense if you could have an additional ROW grouping on Year besides City .

Shyam

|||Hey man.

Thanks again, maybe you can explain to me how one can work out the Total for years column...
which calculates the sum of each row.. This is a limitation of the tabular report (no column grouping) ?

Maybe you could point me in the right direction for this situation: Say i have under each row group, subtotals. But for every detail textbox I check the month of that record and only display it in the appropriate column (jan or feb etc..) how can I get the row group sub total to produce a result for each month.. and not a subtotal of the row group (city), I guess this can be done using a SQL query but im wondering if RS can provide some support for it...

I require this table to have static columns and therefore using a matrix seems too limited.. And there are these small issues stopping me from knowing if its possible to implement using a tabular report...

Neil
|||

- Removed -

|||WOAH!

that is one heck of a thread jack. so anyone have an answer for my original question?
|||

I'm sorry, I didn't intend to thread-jack... I thought we were talking about similar things.

My apologies.

|||sorry

Lowest Marks

I have the following 3 tables.
Student Table : S_ID, FNAME, LNAME
UNIT : U_ID, UNITNAME
Marks Table : S_ID, U_ID, YEAR, MARKS
A student can enrol in multiple units of study each year. At enrolment by default their marks is zero.

Given a certain year we need to output the lowest marks obtained by a student in each unit. Students with zero marks should be excluded.
The SQL query shall output the following :
FNAME, LNAME, UNITNAME, MARKS
Example find the lowest marks obtained for all units in 2002. (The unitname can only appear once in the result).

Can the above query be performed in a single select statement? If yes how ? If no what is the alternate?Hi
Kindly use the following query to get the required results.

select t1.sid, t1.fname, t1.u_id, l2.u_name, t1.marks from (select l1.sid, l1.fname, l3.u_id, l3.marks from L1, L3 where L1.sid=l3.sid and l3.marks!=0) t1, l2 where t1.u_id=l2.u_id;

Thanx and Regards
Aruneesh|||Everything that you wanted is coming packaged in just one query.
Hope it works for you fine.|||I suppose the question was not clear therefore the reply by aruneeshsalhotr did not answer the qestion. I will try & simplify with an example.

Student Table : S_ID, FNAME, LNAME
Student Data :
001, Jack, Russel
002, Mark, Benny
003, John, Wayne

Unit Table : U_ID, UNITNAME
Unit Data :
MA, Maths
EN, English
SC, Science

Marks Table : S_ID, U_ID, YEAR, MARKS
Marks Data :
001, MA, 2002, 80
001, EN, 2002, 60
001, SC, 2002, 0
002, MA, 2002, 50
002, EN, 2002, 70
002, SC, 2002, 60
003, MA, 2002, 0
003, EN, 2002, 0
003, SC, 2002, 55
003, MA, 2003, 50
003, EN, 2003, 70
001, SC, 2003, 50

The Output required for the query -> find the lowest marks obtained for all units in 2002 is as follows:
FNAME, LNAME, UNITNAME, MARKS
Jack, Russel, English, 60
Mark, Benny, Maths, 50
John, Wayne, Science, 55
In the above result all those with 0 marks have been eliminated.

Is it possible to have a all in one query for the above output?

Lowest Cost

OK, this should be an easy one but my brain isn't quite working right now.

I have a table, we'll call Table1 like so:

ProductID Supplier Cost
12345 A 14.50
12345 B 13.49
12345 C 12.00
43222 A 15.00
43222 B 15.21
43222 C 13.99
12312 B 14.00
15421 A 21.99
15421 C 20.00

And I want to Get the name of the Supplier with the Lowest cost,
I know I can go like:

SELECT ProductID, MIN(Cost) FROM Table1 GROUP BY ProductID

and get the lowest cost, but what would be the most effiecent way to get all three fields returned by the query? I need the ProductID, Supplier and Cost.

Thanks,

select d.productid, t.supplier, d.cost
from table1 t
join
(
select productid, min(cost) cost
from table1
group by productid
) d on d.productid = t.productid and d.cost = t.cost

|||Thanks, that was what I was looking for. I'm still getting duplicate records if there are two suppliers with the same lowest cost for a product. But thats something I can work out.

Thanks.|||Just throw a DISTINCT on the outer query|||The extra distinct wont eliminate multiple suppliers with matching lowest prices

try adding a Top 1 to the outer query|||>>Just throw a DISTINCT on the outer query

>>>The extra distinct wont eliminate multiple suppliers with matching lowest prices
try adding a Top 1 to the outer query

You are correct mbanavige, I misread the problem to be the same supplier with multiple min(costs).

Though, TOP 1 will not do it either

DeoDev, I am having a hard time understanding why supplier has any relevance then in this resultset??|||Its for a ordering system, where it will automatically order products that are out of stock from the supplier who has the lowest price, so I need to know who has the lowest price so I can place the order with them, and I'd prefer to return the information in one table.

But with having two suppliers with the same lowest cost, It doesn't matter which one to order from out of the two. I think I have to do a little bit more thinking on the design of this function.|||I see.

Though I would hate to be the supplier whose name doesn't get returned in this result
:)