A UNION made in heaven, or hades?

Hi all, Hope everyone is well, my poor Steelers got chomped and spit, and I've been spending lots of time today eating my words *sigh*.

However, in between all the crow-chomping, I have run into a problem I think I am too closely involved with to see around.

I have two identical tables in two different databases. They contain (for simplicity's sake) a symbol, and a ranking for that symbol. The two tables should be the same, but are sometimes not, so I am trying to figure out a way to select from the tables in such a way that I can say:

"Symbol xxx is ranked nn in table t1, but is ranked mm in table t2"

Perhaps I am getting too fancy, but thought I could do it in a single select.

here's what I have so far:
CREATE TABLE tMyPicks (
sym VARCHAR(5) NOT NULL
, rank INT NOT NULL
)

CREATE TABLE tUrPicks (
sym VARCHAR(5) NOT NULL
, rank INT NOT NULL
)

INSERT INTO tMyPicks (sym, rank)
SELECT 'BFA', 1
UNION SELECT 'BFB', 2
UNION SELECT 'BFC', 3
UNION SELECT 'BFD', 4
UNION SELECT 'IMA', 5
UNION SELECT 'ICU', 6
UNION SELECT 'SOB', 7
UNION SELECT 'SORU', 8
UNION SELECT 'HERE', 9

INSERT INTO tUrPicks (sym, rank)
SELECT 'BFA', 1
UNION SELECT 'BFB', 2
UNION SELECT 'BFC', 3
UNION SELECT 'BFD', 5
UNION SELECT 'IMA', 7
UNION SELECT 'ICU', 6
UNION SELECT 'SOB', 8
UNION SELECT 'SORU', 4
UNION SELECT 'NHERE', 9

select sym, rank
from ( select sym, rank from tMyPicks
union all
select sym, rank from tUrPicks) AS MyUnionTable
group by sym, rank
having count(*) <> 2
order by sym

DROP TABLE tMyPicks
DROP TABLE tUrPicks

This results in output that is a step away from what I want...that is, it at least identifes the individual symbols (and the associated ranking) that are NOT the same in the two tables.

IF there was a way to show which table the output of my union came from, I would be good to go, and thought I could add a literal to the select lists from each table in the UNION, but that destroys my group by clause.

Any thoughts? I suspect I will have to go away from my use of the UNION, but when I try using a join, I still have a problem with the grouping logic.

I suspect I am trying to be TOO dang "fancy" but at the moment I think I am too close to the forest to see the trees. :(I'd suggest:SELECT Coalesce(m.sym, u.sym) AS sym, m.rank AS myRank, u.rank AS urRank
FROM tMyPicks AS m
FULL OUTER JOIN tUrPicks AS u
ON (u.sym = m.sym)
WHERE m.rank <> u.rank
ORDER BY 1
-PatP|||How about a slightly different tack...

select you.sym, you.rank , me.sym, me.rank
from tMyPicks me full outer join tUrPicks you on me.sym = you.sym
where (you.sym is null or me.sym is null)
or you.rank <> me.rank
order by you.sym

Sniped for using longer table synonyms ;-)|||Sniped for using longer table synonyms ;-)It's a rough neighborhood, what can I say?

-PatP|||YESSSSS!!!

You guys RULE!!! I KNEW I was making it too hard...and there you are, Pat...allowing me an opportunity to use my favorite function, COALESCE!!! *LOL*

Thanks much guys...I sincerely (as always) appreciate your time!!!|||Dang...so close...Actually, I left out an important aspect of my problem...one that throws a monkey wrench into the whole deal.

Alas, There can also be situations (typically at the lower end of the ranked list) in which the symbol is NOT in BOTH of the two tables. That's where my UNION was helpful...

That complicates the join, I know...if anyone has a quick adjustment, that would be much appreciated, otherwise I'll keep playing around with things. I adjusted the original post to show the data as it could be out there...

I did notice that my test data in my code above did not address this situation either...thank goodness for the ability to test against the live data ;)|||symbol not in both tables is covered by FULL OUTER JOIN

MCrowley's WHERE clause handles it well|||Then I'd use what is basically MCrowley's solution, something like:SELECT Coalesce(m.sym, u.sym) AS sym, m.rank AS myRank, u.rank AS urRank
FROM tMyPicks AS m
FULL OUTER JOIN tUrPicks AS u
ON (u.sym = m.sym)
WHERE m.rank <> u.rank
OR m.sym IS NULL
OR u.sym IS NULL
ORDER BY 1-PatP|||Wow, and I still get to use COALESCE *LOL*

Seriously, thanks again for your help. It seems so plain and obvious once someone else writes it down (oh, and that "figures it out" part too. ;) )

Thanks anyway...perhaps someday I'll actually KNOW what I THINK I know.|||"Thanks anyway...perhaps someday I'll actually KNOW what I THINK I know."

Be aware that such a situation would result in your immediate and permanent banishment from the forum. :)|||It's a rough neighborhood, what can I say?

And I would not have it any other way.

The thought occurs to me, though, that the data should probably all be in a single table of picks. Then it would be easier to get a third, fourth, or fifth set of picks in the database. The query would change a little bit to something like:

SELECT Coalesce(m.sym, u.sym) AS sym, m.rank AS myRank, u.rank AS urRank
FROM (select * from picks where picker = 'me') AS m
FULL OUTER JOIN (select * from picks where picker = 'you') AS u
ON (u.sym = m.sym)
WHERE m.rank <> u.rank
OR m.sym IS NULL
OR u.sym IS NULL
ORDER BY 1|||Well, I would also take that tack if'n it was up to me, and it fit with the plan ;) However, what y'all did not know (because, as you know, knowledge is power, and I only let on what I dared to let on...) is that I am just writing a stored proc that I can use to verify daily processing results in two different, but identical databases on different servers.

We have a production system that uses the Poor Man's Redundancy scheme...that is, two servers that, under the best of situations, contain databases that are *coff, coff* MIRRORS of each other, but are completely independent. Both start off with the same data in "identical" databases, and theoretically, after importing the "same" data from the "same" source each day, process independently (using the "same" stored procedures) and should, theoretically, arrive at the end of the processing day with exactly the same data in the respective databases.

That said, all the quotes should tell ya that it doesn't always happen that way. Sometimes an FTP or fails, and imported data isn't "identically" imported to each half of the mirrored system. What I am doing is trying to write a quick and dirty proc that checks one part of the end-result on each server, then compares the stock rankings to make sure both systems arrived at the same results at the end of the day.

If nothing else, this allows me to jump on the problem BEFORE the data gets out to the end users, and resolve any system/data issues that cause a disagreement between the two servers.

*phew* That said, I also neglected to show in my original code posting that there is also a DATE aspect of the select, so I had to play around with the code you kind gentlement provided yesterday in order to take that into account.

Here, for the sake of posterity (or is that posteriority?), is what I ended up with:CREATE TABLE tMyPicks (
myDate smalldatetime NOT NULL,
sym VARCHAR(6) NOT NULL,
rank INT NOT NULL
)

CREATE TABLE tUrPicks (
myDate smalldatetime NOT NULL,
sym VARCHAR(6) NOT NULL,
rank INT NOT NULL
)

INSERT INTO tMyPicks (mydate, sym, rank)
SELECT CONVERT(varchar(10), getdate(), 101), 'BFA', 1
UNION SELECT CONVERT(varchar(10), getdate(), 101),'BFB', 2
UNION SELECT CONVERT(varchar(10), getdate(), 101),'BFC', 3
UNION SELECT CONVERT(varchar(10), getdate(), 101),'BFD', 4
UNION SELECT CONVERT(varchar(10), getdate(), 101),'IMA', 5
UNION SELECT CONVERT(varchar(10), getdate(), 101),'ICU', 6
UNION SELECT CONVERT(varchar(10), getdate(), 101),'SOB', 7
UNION SELECT CONVERT(varchar(10), getdate(), 101),'SORU', 8
UNION SELECT CONVERT(varchar(10), getdate(), 101),'HERE', 9
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101), 'XBFA', 1
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XBFB', 2
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XBFC', 3
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XBFD', 4
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XIMA', 5
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XICU', 6
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XSOB', 7
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XSORU', 8
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XHERE', 9

INSERT INTO tUrPicks (mydate, sym, rank)
SELECT CONVERT(varchar(10), getdate(), 101),'BFA', 1
UNION SELECT CONVERT(varchar(10), getdate(), 101),'BFB', 2
UNION SELECT CONVERT(varchar(10), getdate(), 101),'BFC', 3
UNION SELECT CONVERT(varchar(10), getdate(), 101),'BFD', 5
UNION SELECT CONVERT(varchar(10), getdate(), 101),'IMA', 7
UNION SELECT CONVERT(varchar(10), getdate(), 101),'ICU', 6
UNION SELECT CONVERT(varchar(10), getdate(), 101),'SOB', 8
UNION SELECT CONVERT(varchar(10), getdate(), 101),'SORU', 4
UNION SELECT CONVERT(varchar(10), getdate(), 101),'NHERE', 9
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101), 'XBFA', 1
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XBFB', 2
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XBFC', 3
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XBFD', 5
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XIMA', 7
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XICU', 6
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XSOB', 8
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XSORU', 4
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XNHERE', 9

SELECT Coalesce(m.sym, u.sym) AS sym, m.rank AS myRank, u.rank AS urRank
FROM tMyPicks AS m
FULL OUTER JOIN tUrPicks AS u
ON ((u.myDate = m.myDate) AND (u.sym = m.sym))
WHERE (COALESCE(U.mydate, M.myDate) = '2005-01-24') AND
((m.rank <> u.rank) OR (m.sym IS NULL) OR (u.sym IS NULL))
ORDER BY 1

DROP TABLE tMyPicks
DROP TABLE tUrPicks

this results in output that I need, which is:

XBFD 4 5
XHERE 9 NULL
XIMA 5 7
XNHERE NULL 9
XSOB 7 8
XSORU 8 4

As always, thanks for your help!

a time out has occurred while waiting for buffer latch type 4

We are getting this error on SQL Server 2000 ( SP3)
" a time out has occurred while waiting for buffer latch type 4 "
As per Microsoft this may happen when memory is >3GB and awe enabled ,and
this is fixed in SP2
But in my case
SQL Server 2000 -Standard Edition (SP3)
AWE disabled
OS Memory -- 2GB
Service Pack -- SP3
Any one experienced this and solved ? Any help is appreciated .
Thanks
BinuLook at this article.
There are couple of reasons why it can be:
http://support.microsoft.com/default.aspx?scid=kb;en-
us;310834&Product=sql
Regards,
Sergey.
>--Original Message--
>We are getting this error on SQL Server 2000 ( SP3)
>" a time out has occurred while waiting for buffer
latch type 4 "
>As per Microsoft this may happen when memory is >3GB and
awe enabled ,and
>this is fixed in SP2
>But in my case
>SQL Server 2000 -Standard Edition (SP3)
>AWE disabled
>OS Memory -- 2GB
>Service Pack -- SP3
>Any one experienced this and solved ? Any help is
appreciated .
>Thanks
>Binu
>
>.
>

a strange problem

I made a some reports. set their parameterss and passed them while invoking report. At report creation time I check the INTEGRATED SECURITY checkbox so it does not ask for password when pressing ther "invoke report" button. (expectds behaviour).

But when I executed the same exe on some other machine. having similar database as mine. by clikcing the invoke report button, a box comes up asking the following informtion.

Server name:
user :
password :
database :

how to solve it...Open the report and do verify database

A SSIS package

Hi,

I am used Visual Studio SSIS wizard to transfer some data from one database to another with the same table structure. This is the first time I use SSIS. I see two objects created. OLE DB Source extracts some data based on the create date and OLE DB Destination object is a corresponding table.

So query in OLE DB Source:

Select ID,[Desc],[CreateDate] from TableSrc where ],[CreateDate] between ‘1/1/2006’ and ‘1/31/2006’

OLE DB Destination has TableDest as the destination. TableDest has the same structure as TableSrc.

My problem is that when I run the package twice the data will be imported twice. I need to use this package for both new records and updated records in TableSrc . Is there any way I can check if the ID is available in the TableDest, I perform update otherwise perform insert into TableDest.

Thanks,

That is a verry common scenario when loading data. The most popular solution in this forum is tu used a Lookup transform in the data flow against the destination table; no matches in LU transform are treated as errors; so you can configure the error output of the LU transform to 'redirect error'; then the error output is your 'new rows' out put and the no-error output is the 'existing rows' output.:

Somewhere in this thread there is a link to Jamie's blog where that technique is explanied (an other options discussed).

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=779836&SiteID=17

Be sure to understand how Lookup transform uses memory resources as that could play against you if the volume of data is to big or if memory not enough

|||I second Rafael's comments.

A SSIS package

Hi,

I am used Visual Studio SSIS wizard to transfer some data from one database to another with the same table structure. This is the first time I use SSIS. I see two objects created. OLE DB Source extracts some data based on the create date and OLE DB Destination object is a corresponding table.

So query in OLE DB Source:

Select ID,[Desc],[CreateDate] from TableSrc where ],[CreateDate] between '1/1/2006' and '1/31/2006'

OLE DB Destination has TableDest as the destination. TableDest has the same structure as TableSrc.

My problem is that when I run the package twice the data will be imported twice. I need to use this package for both new records and updated records in TableSrc . Is there any way I can check if the ID is available in the TableDest, I perform update otherwise perform insert into TableDest.

Thanks,

I can't find a simple way to do this in SSIS. I'd better do this in T-SQL, usingLinked Servers. Suppose you've set a linked server (namedSourceServer) for the source server on destination server, you can use such query to accomplish INSERT/UPDATE:

UPDATE TableDest
SET [Desc]=src.[Desc], [CreateDate]=src.[CreateDate]
FROM TableDest dest JOIN [SourceServer].SourceDB..TableSrc src
ON dest.ID=src.ID
WHERE?src.[CreateDate] between '1/1/2006' and '1/31/2006'

INSERT INTO TableDest
SELECT * FROM [SourceServer].SourceDB.TableSrc src
WHERE?src.[CreateDate] between '1/1/2006' and '1/31/2006'
AND src.ID NOT IN (SELECT ID FROM TableDest)

a sql datatime question..

according to sql 2k book online
Date and time data from January 1, 1753 through December 31, 9999
what if a couple of records in a huge data file actually have dates
backed to 1500 (and those are actual and valid publication dates), what
should i do? I want to be able to query the data by datetime, and don't
want to use varchar just for a couple of records, but at the same time,
i couldn't bring in the records with a record dated in 1500. is there a
work around?
thank you.=== Steve L === wrote:
> according to sql 2k book online
> Date and time data from January 1, 1753 through December 31, 9999
> what if a couple of records in a huge data file actually have dates
> backed to 1500 (and those are actual and valid publication dates),
> what should i do? I want to be able to query the data by datetime,
> and don't want to use varchar just for a couple of records, but at
> the same time, i couldn't bring in the records with a record dated in
> 1500. is there a work around?
> thank you.
Since you don't want to use a varchar column, how about encoding the dates
in this column by adding a couple thousand years to them before storing
them?
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||The data type datetime does not hold dates earlier than 1753 because of
the major changes that occurred in the calender system (in many
countries) in 1752. Because of that, IMO it would not be a good idea to
try to force it into a datetime column with some kind of workaround.
My advice would be to use a char(8) definition (a suggestion you already
rejected), use the format yyyymmdd when entering dates, and use a
constraint to enforce the format. For example:
CHECK ( MyDate LIKE Replicate('[0-9]',8) -- just digits
AND Floor(CAST(MyDate as int)/10000) BETWEEN 1000 AND 2099 --
year
AND Floor(CAST(MyDate as int)/100)%100 BETWEEN 1 AND 12 --
month
AND CAST(MyDate as int) %100 BETWEEN 1 AND 31 --
day
)
For dates >= 1753, you can safely cast these values to datetime. Also,
this format allows sorting on the column and range selections.
Hope this helps,
Gert-Jan
=== Steve L === wrote:
> according to sql 2k book online
> Date and time data from January 1, 1753 through December 31, 9999
> what if a couple of records in a huge data file actually have dates
> backed to 1500 (and those are actual and valid publication dates), what
> should i do? I want to be able to query the data by datetime, and don't
> want to use varchar just for a couple of records, but at the same time,
> i couldn't bring in the records with a record dated in 1500. is there a
> work around?
> thank you.|||thank you guys..
but that sucks.
i'm using the bulk insert to bring in library of congress data.(they
are huge)
i believe there are only a handful of publication dates are in 1950s
but that failed the process if i declare it as datetime datatype
(which is what i really want). i don't think i can do any
transformation in the bulk insert as suggested.|||Steve,
Basically you are out of luck here, but there is something you can do.
Create a temp table, bcp into it with the char date format and then massage
the date as the format you'd like to see while importing into a permanent
table. Possibilities include the ones mentioned in the previous replies on a
classic approach of having 3 integer fields for year, month and day.
Ilya
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1107196701.961705.64680@.f14g2000cwb.googlegroups.com...
> according to sql 2k book online
> Date and time data from January 1, 1753 through December 31, 9999
> what if a couple of records in a huge data file actually have dates
> backed to 1500 (and those are actual and valid publication dates), what
> should i do? I want to be able to query the data by datetime, and don't
> want to use varchar just for a couple of records, but at the same time,
> i couldn't bring in the records with a record dated in 1500. is there a
> work around?
> thank you.
>

a small SQL help regrading a criteria

i want the ordline.qty * product.prodprice AS ordercost

where ordercost = > 150
but it does not work. Every time i type: where ordercost => 150, it would give me an error.
this is my full query:

select customer.custno, customer.custfirstname + " " + customer.custlastname as custfullname, ordertbl.ordno, ordertbl.orddate, employee.empno, employee.empfirstname + " " + employee.emplastname as empfullname, product.prodno, product.prodname, ordline.qty * product.prodprice AS ordercost from (((ordertbl inner join customer on ordertbl.custno = customer.custno) inner join employee on employee.empno = ordertbl.empno) inner join ordline on ordline.ordno=ordertbl.ordno) inner join product on product.prodno=ordline.prodno where (ordertbl.orddate = datevalue('01/23/2007')) and (making ordercost = > 150) ..............

Thanks for help in advancethis has to be either microsoft access or sql server, it doesn't look very much like ANSI SQL

in any case...

one solution is to wrap the query in another SELECT

select * from ( select customer.custno, customer.custfirstname + " " + customer.custlastname as custfullname, ordertbl.ordno, ordertbl.orddate, employee.empno, employee.empfirstname + " " + employee.emplastname as empfullname, product.prodno, product.prodname, ordline.qty * product.prodprice AS ordercost from (((ordertbl inner join customer on ordertbl.custno = customer.custno) inner join employee on employee.empno = ordertbl.empno) inner join ordline on ordline.ordno=ordertbl.ordno) inner join product on product.prodno=ordline.prodno ) as d where orddate = datevalue('01/23/2007') and ordercost = > 150|||sorry
im not sure where to ask
im using this in sql server and access|||doesn't the star calls out all the rows of the tables?
i'll try|||doesn't the star calls out all the rows of the tables?
i'll tryno, the star "calls out" all the columns

in this case it's all the columns of the derived table, show here in blue --

select * from ( select customer.custno, customer.custfirstname + " " + customer.custlastname as custfullname, ordertbl.ordno, ordertbl.orddate, employee.empno, employee.empfirstname + " " + employee.emplastname as empfullname, product.prodno, product.prodname, ordline.qty * product.prodprice AS ordercost from (((ordertbl inner join customer on ordertbl.custno = customer.custno) inner join employee on employee.empno = ordertbl.empno) inner join ordline on ordline.ordno=ordertbl.ordno) inner join product on product.prodno=ordline.prodno ) as d where orddate = datevalue('01/23/2007') and ordercost = > 150|||hey it works!
why does the * makes it work?|||nm
you have explained.

select * from ( select customer.custno, customer.custfirstname + " " + customer.custlastname as custfullname, ordertbl.ordno, ordertbl.orddate, employee.empno, employee.empfirstname + " " + employee.emplastname as empfullname, product.prodno, product.prodname, ordline.qty * product.prodprice AS ordercost from (((ordertbl inner join customer on ordertbl.custno = customer.custno) inner join employee on employee.empno = ordertbl.empno) inner join ordline on ordline.ordno=ordertbl.ordno) inner join product on product.prodno=ordline.prodno ) where orddate = datevalue('01/23/2007') and ordercost = > 150

u had "as d" as typos =p
__________________|||it works because of the derived table, which uses column names as defined in its SELECT

the expression with the column alias ordercost becomes an actual column in the derived table|||as d was not a typo|||i took the as d out and it works too
and what's as d
sorry for being a noob|||i took the as d out and it works too
and what's as d
This sets "d" as the alias name (actually: table name) for the temporary "table" inside the parentheses.

According to standard SQL, every table (or view or nested table expression) must have a name. By having the "AS d" after the definition, it's as if you created a view, viz:
CREATE VIEW d (custno, custfullname, ordno, orddate, empno,
empfullname, prodno, prodname, ordercost)
AS
SELECT customer.custno,
customer.custfirstname || ' ' || customer.custlastname,
ordertbl.ordno,
ordertbl.orddate,
employee.empno,
employee.empfirstname + " " + employee.emplastname,
product.prodno,
product.prodname,
ordline.qty * product.prodprice
FROM ordertbl inner join customer on ordertbl.custno = customer.custno
inner join employee on employee.empno = ordertbl.empno
inner join ordline on ordline.ordno=ordertbl.ordno
inner join product on product.prodno=ordline.prodno
Now this view can be interrogated:SELECT *
FROM d
WHERE orddate = datevalue('01/23/2007')
and ordercost => 150
Rudy's query is exactly this, except for the fact that no view with the name "d" is ever created, it's just temporarily available for the scope of the current query. Such a "view" is often called a "nested table expression". It's to be written asSELECT d.whatever
FROM (SELECT whatever, ... -- the NTE
) AS d
WHERE ...Note the "d.whatever", where the table name "d" is used. That's why the "AS d" is needed: it's really the name of the NTE !|||thanks peter, i was away from the computer all day, but your answer was much better than mine would've been :)

and i call it a derived table instead of nested table expression|||i call it a derived table instead of nested table expressionNTE is the DB2 terminology.
I'm probably a bit biased...

a simple question regarding AES

ttt.tas@.gmail.com schrieb:

Quote:

Originally Posted by

Thanx Volker fo rteh gr8 help and time :D
>
actually, i'll be using MSSQL for my DB.
the problem is as follow, i want to develop some small exe file that
will read the fields of unencrypted DB and encrypt it field by field.
actually i'll get this DB from a client and he doesn't want me to view
the DB content, its already an exisiting one, so i should develop him
some exe file that he will run on this DB and will encrypt all its
fields, and not the DB as a whole, so i can then take this DB and work
on the encrypted fields instead.
>
this is the whole issue :(

Sounds strange. He basically wants to give you a database where /each/
field is encrypted, i.e. a database full of nonsense?
Why can't he just give you the table structure and let you fill
it with your own test data?
SQLServer surely can export a schema definition?

In any case I suggest you ask in comp.databases.ms-sqlserver. This is
more a database problem than an AES problem. SQLserver has an encryption
API and can do the whole thing (if this is what you really want) at SQL
level. So your client can use a small sql script or transact sql file
to do that.

A further advantage of that approach is that, if something goes wrong,
it was definitely the action of the client and not one of your programs.

I've added comp.databases.ms-sqlserver to the group list so my
posting should show up there too.

As for encryption, I fear there is no easy way to encrypt safely
under the conditions you want. Stream ciphers don't extend the
plaintext but are totally unsafe (i.e. you can easily find out
your clients data) if the stream is reused. If the stream is
not reused, all foreign key relations in your database will break.
So, you'd need to manage the reuse on a per-relation base and this
takes about as much effort as just copying the table structure and
writing a small program to generate test data.

Block ciphers either extend the data or are unsafe.

And both generate binary data which messes up your character and number
columns, bot to mention enumerations. They will also break constraints
if there are any and likely betray to you every field that is NULL.

In short: get the schema definition and write a small program that fills
it with test data.

Lots of Greetings!
Volker
--
For email replies, please substitute the obvious.Volker Hetzer wrote:

Quote:

Originally Posted by

ttt.tas@.gmail.com schrieb:

Quote:

Originally Posted by

Thanx Volker fo rteh gr8 help and time :D

actually, i'll be using MSSQL for my DB.
the problem is as follow, i want to develop some small exe file that
will read the fields of unencrypted DB and encrypt it field by field.
actually i'll get this DB from a client and he doesn't want me to view
the DB content, its already an exisiting one, so i should develop him
some exe file that he will run on this DB and will encrypt all its
fields, and not the DB as a whole, so i can then take this DB and work
on the encrypted fields instead.

What work do you need to do with the encrypted database? For most
purposes I'd say that what you have proposed is impractical and
probably impossible. If you encrypt the database in its entirity:

You won't be able to create or enforce constraints
You won't be able to index it effectively
You won't get any sensible performance metrics
You won't be able to create accurate test cases

In short, you won't really have a database to work with. So if you need
to do any development work I suggest you create some representative
test data for yourself instead.

However, SQL Server 2005 does have encryption built in to the engine,
including support for AES.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Volker Hetzer (firstname.lastname@.ieee.org) writes:

Quote:

Originally Posted by

ttt.tas@.gmail.com schrieb:

Quote:

Originally Posted by

>actually, i'll be using MSSQL for my DB.
>the problem is as follow, i want to develop some small exe file that
>will read the fields of unencrypted DB and encrypt it field by field.
>actually i'll get this DB from a client and he doesn't want me to view
>the DB content, its already an exisiting one, so i should develop him
>some exe file that he will run on this DB and will encrypt all its
>fields, and not the DB as a whole, so i can then take this DB and work
>on the encrypted fields instead.

Actually, we had this sort of a problem with one of our customers, and
we developed a very cheesy low-budget solution. To our defense, I should
add that it was the customer's own idea.

In our case, the problem is that the customer cannot let us into the
database for support cases, if their customer data is visible, due the
regulations on financial secrecy in the country where they are active.

What they do when they need us to access the database, is that they
pull a handle (that is, they run a small application), that copies
all sensitive customer information to a database we do not have access
to, and then they replace this data with a string of question marks.
Once they are done, they copy the real data back.

That could serve as inspiration for ttt.tas's problem. Rather than
encrypting the entire database, just overwrite the sensitive information
with nonsense, and save the real database locally at the client.
Provided that there is a need to merge back at all. If there is no
need to merge back, then you can be more frivolous with destroying
the current information.

Note that depending on the purpose of getting a local copy, the
operation may be more or less successful. If the purpose is to
examine performance problems, replacing a lot of data can change
presumption, resulting in problems in reproducing performance
issues.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog schrieb:

Quote:

Originally Posted by

Volker Hetzer (firstname.lastname@.ieee.org) writes:

Quote:

Originally Posted by

>ttt.tas@.gmail.com schrieb:

Quote:

Originally Posted by

>>actually, i'll be using MSSQL for my DB.
>>the problem is as follow, i want to develop some small exe file that
>>will read the fields of unencrypted DB and encrypt it field by field.
>>actually i'll get this DB from a client and he doesn't want me to view
>>the DB content, its already an exisiting one, so i should develop him
>>some exe file that he will run on this DB and will encrypt all its
>>fields, and not the DB as a whole, so i can then take this DB and work
>>on the encrypted fields instead.

>
Actually, we had this sort of a problem with one of our customers, and
we developed a very cheesy low-budget solution. To our defense, I should
add that it was the customer's own idea.

Out of curiosity, what would the high-budget solution have looked like?

Lots of Greetings!
Volker
--
For email replies, please substitute the obvious.|||Volker Hetzer (firstname.lastname@.ieee.org) writes:

Quote:

Originally Posted by

Erland Sommarskog schrieb:

Quote:

Originally Posted by

>Actually, we had this sort of a problem with one of our customers, and
>we developed a very cheesy low-budget solution. To our defense, I should
>add that it was the customer's own idea.

>
Out of curiosity, what would the high-budget solution have looked like?

The initial idea was to use the new encryption facilities in SQL 2005,
but I do not really like that, since it would require the users to work
with multiple passwords. And the customer wanted to go live with a version
of our product that does not support SQL 2005, so encryption was not an
option at that stage anyway.

Instead my suggestion was to have a second database on a second server.
This database would have all the sensitive information. The few functions
that needs to access it would connect to that database with integrated
security (so the users would not need any extra passwords). If the user
is not authorised to that database, the GUI would just display the dummy
data from the main database. It would fall on the business layer to make
that second connection; it would not be in the stored procedures.

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

Quote:

Originally Posted by

Volker Hetzer (firstname.lastname@.ieee.org) writes:

Quote:

Originally Posted by

>Erland Sommarskog schrieb:

Quote:

Originally Posted by

>>Actually, we had this sort of a problem with one of our customers, and
>>we developed a very cheesy low-budget solution. To our defense, I should
>>add that it was the customer's own idea.

>Out of curiosity, what would the high-budget solution have looked like?

>
The initial idea was to use the new encryption facilities in SQL 2005,
but I do not really like that, since it would require the users to work
with multiple passwords.

Hm. One could have /one/ password for the set of authorized users,
encrypted for each user separately, with the users normal password.
Then the user (or his program anyway) could look up the encrypted
password for the user and decrypt it with the users password.

Lots of Greetings!
Volker
--
For email replies, please substitute the obvious.|||Volker Hetzer (firstname.lastname@.ieee.org) writes:

Quote:

Originally Posted by

Hm. One could have /one/ password for the set of authorized users,
encrypted for each user separately, with the users normal password.
Then the user (or his program anyway) could look up the encrypted
password for the user and decrypt it with the users password.

I'm not sure that works with SQL Server encryption, but I would need to
think both twice and thrice to say for sure. Then again,
encryption/descryption could also be done client-side.

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

Quote:

Originally Posted by

Volker Hetzer (firstname.lastname@.ieee.org) writes:

Quote:

Originally Posted by

>Hm. One could have /one/ password for the set of authorized users,
>encrypted for each user separately, with the users normal password.
>Then the user (or his program anyway) could look up the encrypted
>password for the user and decrypt it with the users password.

>
I'm not sure that works with SQL Server encryption, but I would need to
think both twice and thrice to say for sure. Then again,
encryption/descryption could also be done client-side.

Yes, en-/decryption would be done client-side. The scenario
above just makes sure that someone who hacked himself into
the database without a legitimate password cannot access
the data.
The normal access would go like this:
given a table keys
(
table_name varchar2(32),
column_name varchar2(32),
user_name varchar2(32),
encrypted_key <some binary>
);
Access would be like this:
- select encrypted_key from keys
where
table_name='XXX'
and column_name='YYY'
and user_name='myself';
- client decrypts key with login password
- client has the key to en-/decrypt the columns

Key change presents a problem.

Lots of Greetings!
Volker
--
For email replies, please substitute the obvious.|||Volker Hetzer wrote:
ttt.tas schrieb:

Quote:

Originally Posted by

Quote:

Originally Posted by

>actually, i'll be using MSSQL for my DB.
>the problem is as follow, i want to develop some small exe file that
>will read the fields of unencrypted DB and encrypt it field by field.
>actually i'll get this DB from a client and he doesn't want me to view
>the DB content, its already an exisiting one, so i should develop him
>some exe file that he will run on this DB and will encrypt all its
>fields, and not the DB as a whole, so i can then take this DB and work
>on the encrypted fields instead.

>

I'm not clear on what you're saying.

If you're encrypting the live data to create test data (a one-way
trip of the data), you only need to use a method that takes
printable n-byte field values and hashes them into printable n-byte
values. This is fairly easy to by hashing each n-byte field,
truncating to n bytes, then converting to printable characters.
This assumes that the encrypted data does not need to be
decrypted.

On the other hand, if you want the encrypt the live data, then
process it on your end, then decrypt it back on the user's end,
perhaps your best bet is for the user to create a second database
containing all the live field values in his dB, indexed by unique
random n-byte values (which can be generated in various ways).
He then creates a copy of his live database, substituting the random
values for the live values. You operate on this copy dB, then return
it to him. He then reverses the process, replacing the random field
values with the old live data values. Obviously the replacement
process should only be applied to alphanumeric fields that you
do not intend to modify (e.g., names, addresses, SSNs, credit
card numbers, etc.).

-drt

A severe error occurred on the current command. The results, if any, should be discarded.

Hi,

I am hosting my ASP.NET application on a Host and after some time I get this error
(Don't get it on my development machine):

A severe error occurred on the current command. The results, if any, should be discarded.

And then it says this on the same page:

Exception Details: System.Data.SqlClient.SqlException: A severe error occurred on the current command. The results, if any, should be discarded.

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

[SqlException: A severe error occurred on the current command. The results, if any, should be discarded.]
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +643
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) +9
ASPNetPortal.PortalSettings..ctor(Int32 tabIndex, Int32 tabId)
ASPNetPortal.Global.Application_BeginRequest(Object sender, EventArgs e)
System.Web.SyncEventExecutionStep.Execute() +60
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

I thought this is a problem with max pool size and I did it max pool size = 5000, now application runs ok for some time and then produces this error but some times this comes very soon.

As a solution, I have to copy my dll in bin directory again and application restarts and works properly but then after some time this happenes again.

Please let me know whats the problem.
I checked all of my SqlDataReaders and SqlConnections are closed properly.

Any help would be appreciated.

Thanks.

Rahul.Rahul,

Getting a similar problem. I've narrowed it down to 3 stored procedures I wrote, others work fine. In the win2k server event view you should see a message

Error: 17805, Severity: 18, State: 3
2002-09-05 10:39:41.68 ods Invalid buffer received from client.

I've noticed that the StroProc often hangs when using the 'run stored procedure' function in the Explorer window in the V NET IDE. However the data still gets added. This would suggest the StorProc isn't returning a result to the code in time.

Like you our test server is fine. This runs SQL Server 2000 Developer Edition (SP1)

The production server runs SQL Server 7.0 (SP4)

Do your StroProcs use char or varchar types with a 50+ size or have a large number of parameters?

Regards

Richard|||Hi Richard,

Thanks a lot for your support.
Well! Certainly I am using varchar for 50+ size.

But I think I figured out the problem (still not sure) because since last two days I didn't get this error message, for this success I made some changes to my code.

If you think to discuss these changes would be worth then please let me know.

Thanks a lot again.

Rahul.|||So, did anyone ever figure the answer to this problem? I'm having the same issue, development server works fine (SQL2K), production server craps out (SQL7) with errors "Invalid Buffer received from client"|||If anyone's tracking this thread, here's an update: I moved the database to another production server running SQL2000, and it runs flawlessly. So the root cause is something in the way SQL7 handles SP's from .Net. More updates to come as I find them...|||We are having the same problem (with tables in our .NET Forums database) and found this info on a microsoft newsgroup)
Unfortunately the stricter datatype processing is a side effect of the 031
patch. We're working on a KB article to explain the behavior and scope.
Here is a draft of our work in progress:

KB 827366 – “Error 17805: Invalid Buffer Received from Client? Error
Message in SQL”

-----------------------
--

The information in this article applies to:

- Microsoft .NET Framework 1.0 (Version: 1.0)

- Microsoft .NET Framework 1.1

-----------------------
--

SYMPTOMS

========

When you use the SqlClient .NET Framework classes, the following error
messages may appear in the SQL Server 2000 error log:

Error: 17805, Severity: 20, State: 3

Invalid buffer received from client.

The following corresponding errors may appear in the client .NET
application:

System.Data.SqlClient.SqlException: A severe error occurred on
the current command. The results, if any, should be discarded

-or-

System.Data.SqlClient.SqlException: Procedure or function
spXXXX has too many arguments specified.

Note If you are using the .NET Framework 1.1 you only see the last error
message.

CAUSE

=====

There are three causes for these errors:

- You use SqlClient classes in a Finalize method or C# destructor. Do not
use any managed classes in a Finalize method or C# destructor.

- You do not specify an explicit SQLDbType for the parameters. In this
case, the SqlClient .NET provider tries to select the correct SQLDbType
based on the data that is passed and it will fail.

- If the size of the parameter that is specified explicitly in the .NET
code is more than the maximum allowable size for the data type in the SQL
Server.

- For example: According to SQL Server Books Online, nvarchar is a
Variable-length Unicode character data of n characters. n must be a value
from 1 through 4,000 If you specify a size that is more than 4000 for an
nvarchar parameter, then you will receive the error message that the
"Symptoms" section describes.

The following code also demonstrates how these errors can occur:

Stored Procedure

--------

PROCEDURE spParameterBug @.myText Text AS

Insert Into ParameterBugTable (TextField) Values
(@.myText)

Code

---

static void Main(string[] args)

{

string dummyText=string.Empty;

for (int n=0; n < /*80*/ 3277; n++) // change this to
80 to get the second error above

{

dummyText += "0123456789";

}

// TO DO: Change data source to match your SQL Server:

SqlConnection con= new SqlConnection("data
source=myserver;Initial Catalog=mydb;Integrated Security=SSPI;persist
security info=True;packet size=16384");

SqlCommand cmd = new SqlCommand("SpParameterBug", con);

// Correct invocation:

SqlParameter param =new SqlParameter("@.myText",
SqlDbType.Text);

param.Value = dummyText;

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(param);

con.Open();

try

{

cmd.ExecuteNonQuery();

}

catch (Exception err)

{

Console.WriteLine(err.ToString());

}

// Causes error 17805:

SqlParameter param2 =new SqlParameter("@.myText",
dummyText);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(param2);

try

{

cmd.ExecuteNonQuery();

}

catch (Exception err)

{

Console.WriteLine(err.ToString());

}

Console.ReadLine();

}

RESOLUTION

==========

To resolve these errors, make sure that you do the following:

1. Do not use SqlClient classes in a Finalize method or a C# destructor.

2. Specify the SqlDbType for the SqlParameter so that there is no inferred
type.

3. Specify a parameter size that is within the allowable limits of the data
type.

REFERENCES

==========

For more information about the maximum size for different data types, see
these sections of SQL Books Online:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_
na-nop_9msy.asp: nchar and nvarchar

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_
da-db_7msw.asp: Data Types

Shawn Aebi
Microsoft
This posting is provided "AS IS" with no warranties, and confers no rights.|||Actually here is a link to the thread...
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=IsYbP1AdDHA.2408%40cpmsftngxa06.phx.gbl&rnum=1&prev=/groups%3Fq%3Dsql%2Bserver%2B17805%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26scoring%3Dd%26selm%3DIsYbP1AdDHA.2408%2540cpmsftngxa06.phx.gbl%26rnum%3D1|||i faced the same error, but found that i was executing "return" in the middle of the transaction and thus the bug was fixed by completing the transaction.

A severe error occurred on the current command.

When ever I run a particular report. Even if I set the
connection to don't time out. I keep getting this:
An error has occurred during report processing.
(rsProcessingAborted) Get Online Help Cannot read the next
data row for the data set MOPhoneSkins.
(rsErrorReadingNextDataRow) Get Online Help
A severe error occurred on the current command. The
results, if any, should be discarded.
I don't understand what's going on. Please help me out if
you've had this problem.
Thanks in advance,
BryanThis exception comes from the ADO.NET / the underlying data provider.
Reporting Services just wraps the exception.
There are many possible reasons why this exception can happen. One example:
you specify a stored procedure on server A in your report as data source. At
runtime this stored procedure tries to invoke another stored procedure on
server B through the linked server feature and fails because of missing
privileges to execute stored procedures on server B.
You might want to search ADO related newsgroups (e.g.
news:microsoft.public.dotnet.framework.adonet) or on the internet for the
exception message string "A severe error occurred on the current command.
The results, if any, should be discarded."
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"bmurtha" <anonymous@.discussions.microsoft.com> wrote in message
news:635a01c4826b$677f3d00$a501280a@.phx.gbl...
> When ever I run a particular report. Even if I set the
> connection to don't time out. I keep getting this:
> An error has occurred during report processing.
> (rsProcessingAborted) Get Online Help Cannot read the next
> data row for the data set MOPhoneSkins.
> (rsErrorReadingNextDataRow) Get Online Help
> A severe error occurred on the current command. The
> results, if any, should be discarded.
> I don't understand what's going on. Please help me out if
> you've had this problem.
> Thanks in advance,
> Bryan

A Sample of an MDX query?

Hello,

I have a cube that includes a measure called student count that is pivoted as data below, row called student statuses and column being a time dimension. The one thing left that I would like to accomplish is to create two extra calculated measures called Difference and %Changed using MDX or any other method? Does anyone has some MDX samples or ideas how I could calculate those two columns in blue in my SQL Server Analysis 2005 cube?

Any help is greatly appreciated!

Spring Semester 2006Spring Semester 2005Difference%ChangeUndergraduates Count490045004008.89%Graudates Count100090010011.11%

You can use Business Intelligence Development Studio to add your calculated measures. Start BIDS in Program Files->Microsoft Sql Server 2005->Sql Server Business Intelligence Development Studio and open your database. Double click on the cube and after the cube is opened, click on to the 3rd tab which says "Calculations" to add new calculated members. For more information, check http://msdn2.microsoft.com/en-us/library/ms169748.aspx.

HTH,

Chu

A question on Conversation timer persistence

I'd like to add code to a trigger to calculate the time to fire a message into a queue based on a field changing, and conversation timers seem like the way to go. My first question refers to this line from the BOL:

"Calling BEGIN CONVERSATION TIMER on a conversation before the timer has expired sets the timeout to the new value."

I think that in this trigger, I can simply begin a new conversation if the given field has changed to reset the timer. But intuition tells me that in order to change the timer to a new value, I need to retrieve the existing conversation, correct?

Also, I've read that conversation timers are persistent in that they survive database restarts and shutdowns. But I'm not sure to what extent. After a database restart/shutdown, does the conversation timer "reset" itself to the time interval specified when the conversation was begun or is it able to account for the time the database was down/offline?

Thanks,

Chris

I'm not sure I understand the requirements. Why is that you need to fire a timer as a result of a field change? The usual requirement is to fire a message so that some asynch processing happens later, but not based on a timer. Can you give some more details?

You can have only one timer per conversation. That what the BOL line refers to. You cannot have multiple timers, setting a new timer will erase the old one.

The timers are set as absolute time, not interval. After a database/server restart, if the time of the timer is in the past, then the timer will be fired.

HTH,
~ Remus

|||

Remus,

Thanks for the quick response.

Here's the workflow of the process: A user creates a work order for which they can assign a follow-up time. When this follow-up time arrives, I want to send a message to a Service Broker queue that I have already set up to process the message. If the follow-up time changes, the timer is adjusted to account for the change in time.

The only difference between what I need to do now and what I've already done is sending the message to the queue at a specific time. I imagined a trigger that would fire every time the follow-up time changed so that I could alter the conversation timer. This trigger would begin a new conversation, set a timer, and an activated stored procedure would look for the message type http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer and send a message to my original queue where it will be processed. I see 2 problems with my logic: I am looking for a DialogTimer message type, so the activated stored procedure only knows it is time to do something, but it has no message body that I can use to forward onto the final Service Broker queue. Also, I have no way of finding the conversation I started the last time the trigger fired.

I'm wondering if using a conversation timer is the wrong approach, and if so, what is?

Thanks,

Chris

|||

What you need is a table to associate the conversation which fired the timer with the original work order. When the work order is created, the trigger begin a dialog, sets the timer and then inserts into this table the newly created conversation handle and the work order id.

When the timer fires, the activated procedure receives the message, looks up the work order id in this table (based on the conversation handle the message was RECEIVE on) and does whatever work is required at that moment.

The same table can be also used when updates occur on the follow_up field. Instead of beginning a conversation, the trigger will look up this association table and find the existing conversation.

One thing to note is that timer messages are unlike any message in the sense that they are sent by one conversation endpoint to itself. So the conversation handle on which the timer was set is the same one as on which is going to be received.

I do believe that conversation timers are the right approach. No other approach I can think of is better. Conversation timers are very cheap from a resource point of view, completely contained within the database (this gives lots of advantages related to backup/restore, failover and availability), and offer the possibility to actually luch a procedure.

HTH,
~ Remus

|||

Thanks a lot Remus. A state table was what I came up with as well. I really appreciate being able to come here for valuable, practical advice on how to approach Service Broker issues. Thanks again,

Chris

A QUERY THAT RUN ON DB2 THAT HAVE MORE PERFORMANCE THAN SQL SERVER 2000

The execution time for this query on DB2 v8.0 DBMS one second but I execute it on SQL SERVER 2000 is around 55 second

so how i can incease the performance for SQL server

SELECT ACC_KEY1,ACC_STATUS_LAST FROM PSSIG.CLNT_ACCOUNTS INNER JOIN PSSIG.CLNT_CUSTOMERS ON
PSSIG.CLNT_ACCOUNTS.CSTMR_OID = PSSIG.CLNT_CUSTOMERS.CSTMR_OID
WHERE (PSSIG.CLNT_CUSTOMERS.CSTMR_START_DT >= '1900-1-1 12:00:00') AND
(PSSIG.CLNT_CUSTOMERS.CSTMR_END_DT <= '2106-12-31 12:00:00') AND
(PSSIG.CLNT_ACCOUNTS.ACC_KEY1 >= '0000000000000') AND
(PSSIG.CLNT_ACCOUNTS.ACC_KEY1 <= '9999999999999') AND
(PSSIG.CLNT_ACCOUNTS.ACC_STATUS_LAST = 5 ) AND
ACC_KEY1 > '0' ORDER BY ACC_KEY1

Note 1: value 5 exist in most of rows about ( 999999/1000000 ) from the table rows count
Note 2: the number of rows in each table around 15000000
Note 3: I used the same index structure for both DB2 and SQL server 2000
Note 4: I used some other feature in DB2 that increase the performance but I did not
found the alternative for it in SQL server 2000 :
a- cardinality varies at run time feature
b- include column in index instead of use compound index for
( ACC_KEY1 ,ACC_STATUS_LAST ) columns
Note 5 : Enable reverse scan for index

Um, why are you using strings to store the ACC_KEY1? Numeric fields are much faster.

I would suggest that you drop all your indexes that relate to that query. Then run the Database Engine Tuning Advisor (or whatever its called in SQL 2000) to determine what the right indexes are. Unless you know SQL Server intimately, it can generate better indexes than you can by hand.

Jonathan

|||

thank you for you advice , i use the tuuning wizard but it did not improve the performance

- and acc_key1 could contain a letter so it must be a string

|||

You use the same indexes, but what does those indexes look like?

What is the volume to be returned? Is the expected output close to a million rows? (all the '5's)

How do you measure the time? Do you look at the server for the time it takes to resolve the query, or do you measure at the 'end-point'? (ie if you select... and wait until a million rows has been drawn on the screen, or similar)

/Kenneth

A query runs 1 times slower from a .NET application the from Query

Just a guess.
It might be the delay in creating and opening the connection.
Why don't you log the current time just before calling the SP and after it
and find the time difference. That can narrow down on what the issue is.
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Boaz Ben-Porat" wrote:

> Computer: 3.4 Ghz CPU, 1 GB RAM, 2003 Server
> database : MS SqlServer 2000 Enterprise. ~ 10 GB database file. Largest
> table in the database contains 11,000,000 records.
> Framework: .NET 2.0
> I try to run a query against the database, selecting aggregated data from
> views based on the large table.
> When executed from the Query Analizer, it takes 13 seconds.
> When executed from a .NET application, it takes 140 seconds.
> The database is well tuned (or else the query analizer would go slowly), s
o
> I can't find the reason for this difference.
> Any suggestion ?
> TIA
> Boaz Ben-Porat
> Milestone Systems
>
>Thanks for a quick answer.
The time I refer to is after the connection is opened.
the relevant code:
DbDataReader dr = null;
try
{
// This method opens a connection, if not allready opened
Connect();
// dbCommand is an input parameter of type DbCommand. It contains the SQL
statement
dbCommand.Connection = _connection;
DateTime t1 = DateTime.Now;
dr = dbCommand.ExecuteReader();
DateTime t2 = DateTime.Now;
TimeSpan ts = t2 - t1;
int milli = (int)ts.TotalMilliseconds; // milli contains the execution time
of dbCommand.ExecuteReader();
Boaz Ben-Porat
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:49A213DD-227B-4602-81ED-5ADF4E32687E@.microsoft.com...
> Just a guess.
> It might be the delay in creating and opening the connection.
> Why don't you log the current time just before calling the SP and after it
> and find the time difference. That can narrow down on what the issue is.
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
> "Boaz Ben-Porat" wrote:
>

A point in time backup with SQL Server 2000 Enterprise Manager

I'm having a problem testing a point in time backup. Here
is what I was trying:
1) I created a complete database backup to a file
D:\TEMP.DAT (of a database named
PHCS) (Time - 10:45)
2) I went and made some changes to the PHCS database at
10:49:10
3) I went and made some more changes to the PHCS database
at 10:50:50
4) I backed up the transaction log database (also into
D:\TEMP.DAT, appending).
(Time - 10:52)
5) I went to restore the database backed up at 10:45 and
left it unoperational
6) I went to restore the transaction log created at 10:52
and tried to specify a time
of 10:50:00. I wanted to restore the changes made in step
2, but not step 3.
He restore me the hole last transaction log i selected
an don't stop between step 2 and 3 !!
Many thanks for your help
DanielDid you use STOPAT..?
Here's the example from SQL BOL
-- Restore the database backup.
RESTORE DATABASE MyNwind
FROM MyNwind_1, MyNwind_2
WITH NORECOVERY
GO
RESTORE LOG MyNwind
FROM MyNwind_log1
WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM'
GO
HTH
Ryan Waight, MCDBA, MCSE
"Ischi" <daniel_ischi@.hotmail.com> wrote in message
news:0def01c38d87$06a78890$a101280a@.phx.gbl...
> I'm having a problem testing a point in time backup. Here
> is what I was trying:
> 1) I created a complete database backup to a file
> D:\TEMP.DAT (of a database named
> PHCS) (Time - 10:45)
> 2) I went and made some changes to the PHCS database at
> 10:49:10
> 3) I went and made some more changes to the PHCS database
> at 10:50:50
> 4) I backed up the transaction log database (also into
> D:\TEMP.DAT, appending).
> (Time - 10:52)
> 5) I went to restore the database backed up at 10:45 and
> left it unoperational
> 6) I went to restore the transaction log created at 10:52
> and tried to specify a time
> of 10:50:00. I wanted to restore the changes made in step
> 2, but not step 3.
> He restore me the hole last transaction log i selected
> an don't stop between step 2 and 3 !!
> Many thanks for your help
> Daniel
>|||Hello
No i use the SQL Enterprise Manager not the Query
Analycer. I know the statement in Qury Analycer.
Thaks and best regards.
Daniel
>--Original Message--
>Did you use STOPAT..?
>Here's the example from SQL BOL
>-- Restore the database backup.
>RESTORE DATABASE MyNwind
> FROM MyNwind_1, MyNwind_2
> WITH NORECOVERY
>GO
>RESTORE LOG MyNwind
> FROM MyNwind_log1
> WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM'
>GO
>
>--
>HTH
>Ryan Waight, MCDBA, MCSE
>"Ischi" <daniel_ischi@.hotmail.com> wrote in message
>news:0def01c38d87$06a78890$a101280a@.phx.gbl...
>> I'm having a problem testing a point in time backup.
Here
>> is what I was trying:
>> 1) I created a complete database backup to a file
>> D:\TEMP.DAT (of a database named
>> PHCS) (Time - 10:45)
>> 2) I went and made some changes to the PHCS database at
>> 10:49:10
>> 3) I went and made some more changes to the PHCS
database
>> at 10:50:50
>> 4) I backed up the transaction log database (also into
>> D:\TEMP.DAT, appending).
>> (Time - 10:52)
>> 5) I went to restore the database backed up at 10:45
and
>> left it unoperational
>> 6) I went to restore the transaction log created at
10:52
>> and tried to specify a time
>> of 10:50:00. I wanted to restore the changes made in
step
>> 2, but not step 3.
>> He restore me the hole last transaction log i selected
>> an don't stop between step 2 and 3 !!
>> Many thanks for your help
>> Daniel
>
>.
>|||To do it within Enterprise Manager :-
Right Click DB
All Tasks
Restore DataBase
Tick the Box "Point in Time Restore"
Specify the time you wish to stop at.
--
HTH
Ryan Waight, MCDBA, MCSE
"Ischi" <daniel_ischi@.hotmail.com> wrote in message
news:0cde01c38d90$dcd9bc90$a301280a@.phx.gbl...
> Hello
> No i use the SQL Enterprise Manager not the Query
> Analycer. I know the statement in Qury Analycer.
> Thaks and best regards.
> Daniel
> >--Original Message--
> >Did you use STOPAT..?
> >
> >Here's the example from SQL BOL
> >
> >-- Restore the database backup.
> >RESTORE DATABASE MyNwind
> > FROM MyNwind_1, MyNwind_2
> > WITH NORECOVERY
> >GO
> >RESTORE LOG MyNwind
> > FROM MyNwind_log1
> > WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM'
> >GO
> >
> >
> >
> >--
> >HTH
> >Ryan Waight, MCDBA, MCSE
> >
> >"Ischi" <daniel_ischi@.hotmail.com> wrote in message
> >news:0def01c38d87$06a78890$a101280a@.phx.gbl...
> >> I'm having a problem testing a point in time backup.
> Here
> >> is what I was trying:
> >>
> >> 1) I created a complete database backup to a file
> >> D:\TEMP.DAT (of a database named
> >> PHCS) (Time - 10:45)
> >> 2) I went and made some changes to the PHCS database at
> >> 10:49:10
> >> 3) I went and made some more changes to the PHCS
> database
> >> at 10:50:50
> >> 4) I backed up the transaction log database (also into
> >> D:\TEMP.DAT, appending).
> >> (Time - 10:52)
> >> 5) I went to restore the database backed up at 10:45
> and
> >> left it unoperational
> >> 6) I went to restore the transaction log created at
> 10:52
> >> and tried to specify a time
> >> of 10:50:00. I wanted to restore the changes made in
> step
> >> 2, but not step 3.
> >>
> >> He restore me the hole last transaction log i selected
> >> an don't stop between step 2 and 3 !!
> >>
> >> Many thanks for your help
> >>
> >> Daniel
> >>
> >
> >
> >.
> >

A newbies question

Hi everyone,

I am a newbie with Crystal Report. I am now working on an assignment but unfortunately I don't have much time to look through the Crystal Report books cover to cover to find out the information I need. I am posting my question here to see if anybody can help me out.

The question is: How can I display a number of rows on a report?

Any help would be highly appreciated.You want the number count or ?

Detail is needed...|||Right click on any field which can't be null (use one of your key fields), ->Insert->Summary.
Select 'Count' for 'Calculate this summary', ->OK.
The result will be placed in the RF.

If you need to have the quantity of records in the RH or PH, create a formula @.Count:

Count ({your_table.field})

and place it there.



For more info, read help file on
Count function
Insert Summary Dialog box

A newbie to Reporting Services seeking advice

I have been a long time Crystal Reports user but now due to a new
application, I want to switch over to SQL Server Reporting Services.
My question is whether I can do what I want with Reporting services.
My app is a 2 tier winforms app. The client will query the database through
a VPN. The database is a SQL Server 2005 database. Can my app call reports
located in a directory on the server and display them in a viewer in my app
and then the client either prints to their local machine or saves to file
ex. pdf file? The client does have an IIS server running on the same
machine as the database.
BillIf I understand your question correctly, yes, you can host a server-
based report inside of a client app and also output them to PDF by
using the ReportViewer control.
Start here: http://www.gotreportviewer.com/
On Apr 14, 12:44 am, "BillG" <billgo...@.charter.net> wrote:
> I have been a long time Crystal Reports user but now due to a new
> application, I want to switch over to SQL Server Reporting Services.
> My question is whether I can do what I want with Reporting services.
> My app is a 2 tier winforms app. The client will query the database through
> a VPN. The database is a SQL Server 2005 database. Can my app call reports
> located in a directory on the server and display them in a viewer in my app
> and then the client either prints to their local machine or saves to file
> ex. pdf file? The client does have an IIS server running on the same
> machine as the database.
> Bill

A more complicated case of insertion filtering by more than two fields

Hi,

I posted a problem some hours ago. I found that the solution that l was given by Karolyn was great, but at that time I didn't realize that my problem was a little bit more complicated. I'll rephrase my problem:

I need to insert some registers in a table. These registers have three fileds: col1, col2 and col3. I don't want to insert a register if in the table already exists a row with the col1, col2 and col3 combination of that register. These fields are PK, but I don't want to get errors. The problem is that I'm inserting a field that belongs also to the destination table. How can I filter a "destination" table by two fields in this case?

This the table1:

create table table1(
col1 int not null,
col2 int not null,
col3 int not null,
constraint PK_table1 primary key (col1, col2, col3)
)

Here's my "insert" code:

INSERT INTO table1
SELECT table2.col1, table3.col2, table1.col3
FROM table2, table3
WHERE table2.col1 = table3.col1

The third field in the SELECT now refers also to table1. Witch conditions should I add to avoid repetitions in table1 (avoiding also erroing)

Thanks

FedericoOriginally posted by fmilano

INSERT INTO table1
SELECT table2.col1, table3.col2,table1.col3
FROM table2, table3
WHERE table2.col1 = table3.col1


Doesn't make sense....

A little MDX problem

Hi,

I need some support for a MDX problem.

We have a cube with a measure, are region (with hierachy, but this doesn't matter at all) and two time dimensions (with the usual hierachies). The business problem is now:

You select a date (on any level) from Time1. If you would place the region on rows and Time2 on colums (the days) with nonempty enabled, you would get a number of members back (about 1000 numbers in Time2 per day in Time1). I don't what to see that 1000 numbers, I need a measure which is calulated like this:

- Sort these numbers by value

- Find the number which is as position 99%, so i.e. if you have 1000 numbers (but this number can change), you need the 990th member. So you have to count the members, multiply by .99 and you have the ordinal of the member you need

So: how to do this in MDX? I'm quite struggeling around with counts and sorts, the 1000 are returned quite quickly but when I start to sort them it's getting veeeerrrrryyyy slow...

I would be very happy if someone can help me with that...

Hi Thomas,

There are some problem parameters that I'm not sure of, so I'll assume that:

- Ordering is along a pre-determined level of a hierarchy (not based on query axes)

- Ordering is by a pre-determined measure (again, not dynamically determined)

- Desired position is 99th percentile ascending (ie. 1st percentile descending)

Based on these assumptions, here is an Adventure Works query which returns the [Date] name, value and ordinal of the 99th percentile [Order Count], by Product Category on rows:

>>

With

Member [Measures].[Orders99thMember] as

MemberToStr(Tail(TopCount(NonEmpty([Date].[Calendar].[Date].Members,

{[Measures].[Order Quantity]}) as DS,

Int(DS.Count/100)+1, [Measures].[Order Quantity])).Item(0).Item(0))

Member [Measures].[Orders99thName] as

StrToMember([Measures].[Orders99thMember]).Name

Member [Measures].[Orders99thValue] as

(StrToMember([Measures].[Orders99thMember]),

[Measures].[Order Quantity])

Member [Measures].[Orders99thOrdinal] as

CInt(99 * (NonEmpty([Date].[Calendar].[Date].Members,

{[Measures].[Order Quantity]}).Count)/ 100)

select {[Measures].[Order Quantity],

[Measures].[Orders99thName], [Measures].[Orders99thValue],

[Measures].[Orders99thOrdinal]} on 0,

NonEmpty([Product].[Product Categories].[Category].Members,

{[Measures].[Order Quantity]}) on 1

from [Adventure Works]

Order Qty Orders99thName Orders99thValue Orders99thOrdinal
Accessories 61,931 November 1, 2003 1663 417
Bikes 90,220 February 1, 2003 2654 1082
Clothing 73,598 July 1, 2003 3200 417
Components 49,027 September 1, 2003 4365 38

>>

|||

Deepak,

thanks for you excellent support... But one question: Where do you do the "sort" by order quantity? I do only see that you return the 99th procentile of the set... I don't think your assuptions will help since you can't define that a set is always sorted by a measure... I understand your second assumption that you mean that it's always the order quatity and not sometimes the quantity and sometimes the amount...

Thanks,

|||Sorry, I guess the topcount does the job... Thanks, it's not very fast but it does the job...

A lil confused from BOL

These are 2 different things.
For (A) this is the length of time that transactions are
retained in MSrepl_commands before the cleanup agent can
remove them. If all subscribers have synchronized, the
commands are removed before this period. This is
different if you have anonymous subscribers of course.
For (B) this means that if a subscription doesn't
synchronize within the said period, it is marked as
inactive and must be reinitialized.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
So if (A) time is up.i.e. after 72 hrs , the cleanup agent would anyways
come in and delete all transactions hence making the subscription in a way
inactive and if you look at the publication properties, it has the 72 hrs
instead of the 336 hrs that (B) talks about..
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:0c5b01c4d917$3ba52390$a301280a@.phx.gbl...
> These are 2 different things.
> For (A) this is the length of time that transactions are
> retained in MSrepl_commands before the cleanup agent can
> remove them. If all subscribers have synchronized, the
> commands are removed before this period. This is
> different if you have anonymous subscribers of course.
> For (B) this means that if a subscription doesn't
> synchronize within the said period, it is marked as
> inactive and must be reinitialized.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>