Puzzled by installation error.

I have tried installed several times the SQL Server 2005 Express (Standard or Advanced Services) on my XP SP2 system and all failed.

The error messages are all about the Secure Socket Layer certificate and provider, either cannot find, create valid SSL certificate or the authentication provider is not trust (actually I don't know any of this and where to find them). I am learning at home the SQL Server and not going to do a website. Why I need a SSL? Or if it is really necessary, how do I obtained one?

I installed IIS (supposedly 5.0 since I have XP SP2), is this related? How to install without IIS?

Or do I need a Server OS?

Turn of the SSL on the default website (where Reporting Service will be installed) and you should be able to install the whole thing easily. Afterwards you can turn on SSL again on the default website.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

How to turn off SSL? In SQL Express setup or IIS setting in MMC?

My IIS setting has a default SSL port 440 but I cannot change it.

Puzzled by installation error.

I have tried installed several times the SQL Server 2005 Express (Standard or Advanced Services) on my XP SP2 system and all failed.

The error messages are all about the Secure Socket Layer certificate and provider, either cannot find, create valid SSL certificate or the authentication provider is not trust (actually I don't know any of this and where to find them). I am learning at home the SQL Server and not going to do a website. Why I need a SSL? Or if it is really necessary, how do I obtained one?

I installed IIS (supposedly 5.0 since I have XP SP2), is this related? How to install without IIS?

Or do I need a Server OS?

Turn of the SSL on the default website (where Reporting Service will be installed) and you should be able to install the whole thing easily. Afterwards you can turn on SSL again on the default website.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

How to turn off SSL? In SQL Express setup or IIS setting in MMC?

My IIS setting has a default SSL port 440 but I cannot change it.

Puzzled by duplicates

Hello all!

Have a question:

Is there way in SQL to determine duplicate rows without using count(), aggregate functions, group by or select distinct?

I only have regular select, join and delete features.

Basically, what are all the possible ways to determine duplicates in data like this?

col1|col2
----
s1--j1
s1--j4
s1--j1
s1--j3
s1--j2

I greatly appreciate your response!
Thanks!You need to add a third column, a unique ID column. Then you can do this

select t1.* from t1
join t2 on t1.col1=t2.col2 and t1.col2=t2.col2 and t1.id != t2.id

Check out my brand new SQL tutorial at http://www.bitesizeinc.net/index.php/sql.html

-Chris
http://www.bitesizeinc.net/|||Is there way to do it without a unique ID? Not to be persistent, but my original goal was to do it only with those simple SQL statements.

I just wanted to make sure that I tried every way possible. Maybe there is a way to use cartesian product or whichever, but it's gotta be based on these simple statements.

If it can only be done using unique ID or aggregate functions, it's a good answer as well.

Let me know if I sound confusing. Great site btw, very original!.

Thanks for your devotion.|||Thank you so much for visitting my site...

I think that you're out of options here.

The best way is using GROUP BY.

Otherwise, you could select DISTINCT CONCAT(field1,' ',field2)

Or use the unique ID

I can't think of anyway else...

-Chrissql

Puzzled by Config files

I created a package, created an XML Config file, created a deployment utility and then deployed to server. I scheduled it with agent and I get this error (changed the package name in the error to IMPORT to make it more readable):

Description: The configuration file name "C:\Documents and Settings\chris\My Documents\Visual Studio 2005\Projects\IMPORT\IMPORT\IMPORT.dtsConfig" is not valid. Check the configuration file name. End Warning Warning: 2006-07-12 13:11:39.96 Code: 0x80012059 Source: IMPORT Description: Failed to load at least one of the configuration entries for the package. Check configurations entries and previous warnings to see descriptions of which configuration failed. End Warning Progress: 2006-07-12 13:11:40.56 Source: Import Data Validating: 0% complete End Progress Progress: 2006-07-12 13:11:40.56 Source:... Process Exit Code 1. The step failed.

So basically its trying to point to a config file on my dev workstation. When I created the deploy utility it created a config file in the Deployment directory with the Manifest and the package. I installed from the deployment directory. When installing it asked where I wanted to install and I left it the default of "C:\Program Files\Microsoft SQL Server\90\DTS\Packages\IMPORT" and if I look in that folder the IMPORT.dtsconfig is there.

Ideas of where I went wrong? I want to make sure I understand the whole configuration file notion correctly as I am starting to roll a bunch of packages with config files out to production. I've done the tutorial and thought it was straightforward and made sense. I can fix it by editing the xml of the package and removing the line pointing to my dev box - just want to know what I am doing wrong :)

Nevermind... Found lots of other people with similar issues in diff threads on this forum. This whole config file thing is pretty confusing. When you do the install from the deployment folder it will show all the values from the config file in the deployment directory - but it doesn't use any of them when you actually run the package.

I am really not sure why they put the dtsconfig file in the deployment folder at all - all that does is confuse things. I had been placing the production config file over the one in the deployment folder and assumed it would use it... not so.

Puzzled by concurrent update

I have a puzzle in my mind here. I will thank anyone who can solve my puzzle.

I am not familiar with SQL and its theories behind, so please bear with me if I am asking a stupid newbie question.

My puzzle is generally a problem of generating sequence numbers. The following SQL is only a stripped down version - it fetches the max number, add 1 to it and updates the table with the new number.

DECLARE @.max int

SELECT @.max = MAX(next_number) + 1 from sequence_numbers

UPDATE sequence_numbers SET next_number = @.max WHERE next_number = @.max

Now if user1 gets 100 and user2 also gets 100 and they both try to update the table, what would happen? I fear that the result would be 101.

One of my coworker thinks that adding 'WHERE next_number = @.max' can solve the conflict - user2 will fail. His reasoning is like this:

After user1 updates the table, next_number would be 101 and user2's update will fail because his WHERE criteria is still 100.

But I think user2 still sees the old data (100) and still succeeds and thus both users update the table with number 101.

Thanks.

If you want to use a sequence table, you have to lock this table during the update. I wouldn′t use separate statement though the thing that you described first will happen.

You should either do that within a transaction while locking the table or do it within one statement using locking mechanism and queying this with your update statement:

UPDATE sequence_numbers
SET next_number =
( SELECT MAX(next_number) + 1 from sequence_numbers (TABLOCK) )

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Neo,

One of the cruical components of DBMS(Database Management System) is the concurrency control (CC) subsytem. It ensures that concurrent execution of transactions against DB doesn't violate the consistency of the database. While the transactions execute concurrently in real, the CC component ensures that the database state is AS IF THEY EXECUTED SEQUENTIALLY.
In two words, the server "locks" rows that some transaction modifies in order to prevent others from reading/writing those rows, until the first transaction commits.
If you need serious understanding about how all this works, open books online, and look for concurrency control. There's a lot of material there, from concurrency control basics up to some advanced issues.
About your co-workers explanation: Imagine that the updates are done not in form "where next_number=@.max" but "where row_id=something". By the model proposed by your friend, this update will succeed, because the first update hasn't modified the row_id. In fact it doesn't-the explanation is wrong.

To Jens: I guess that under default serializability level, that is, read commited, second update just cannot read the modified rows, because they will be locked with EXCLUSIVE lock, which blocks any reading transactions. If the first transaction reads data(read lock), the second one reads(read lock), then both will be denied an exclusive access to the rows needed to update(this is a deadlock).

In either case the execution will be serializable, and there is no need for a TABLOCK, there is no need for explicit locking. Please correct me if I am wrong.

|||

I looked thru SQL BOL on Concurrency Control. But reading those technical explanation does not lead me to solve my puzzle. Can anyone explain more to me or direct to some 'newbie' level articles on CC (if such articles exist)? Thanks.

|||

You need to use a locking hint to single thread access to the table. There is a good article here: http://blogs.msdn.com/sqlcat/archive/2006/04/10/572848.aspx from the Microsoft SQL Server Development Customer Advisory Team.

You have the basics, and the rest is covered in that article.

|||

I read the blog, but it does not answer my problem.

Option 1 is only for 'low volume' - how low? Does 'low 'mean that if it is used in higher volum there will be concurrent issues, such as two users get duplicate seq number?

Option 2 is not very pratical.

|||

The tablock was useless, you are right, I was thinking one step further, updating the sequence table (if that would be the requirement) to ensure that nobody else will query meanwhile the sequence table. I saw that this was not a requirement, changed the query and forgot about the tablock.

To the original poster: if you don′t want to reset (pushing the sequence number one step further) you can use the update with the inline select, that should do the trick.

No deadlock will occur if two are using the same update command, because the only locked table will be the updated one. After this is not released the Select statement won′t take place.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

The problem with the first action is that you have to single thread access to the table of sequence numbers in a manner that will be slower than using an identity column. You will not get duplicates, but every user will have to wait.

The biggest problem comes in that when you lock the row to get the max, it has to stay locked until you do the update. But, what if this transaction is part of a greater transaction. Then all processes have to wait until the whole transaction completes because the UPDATE operation causes an exclusive lock that keeps the next user from asking for the max (to avoid dups).

Option 2 beats that by using INSERT operations with identity values that do not block one another and do not require an EXCLUSIVE lock that will block other rows from inserting with the next new value. You can get gaps if transactions are rolled back, but it will work nicely.

The second option can be done with very little work just like the first, though it will look messier.

|||

Thanks for all your replies. They are useful to me.

But how about my original question. Will the two users both succeed in updating (note tha next_number = @.max criteria)?

|||

You can use the TSQL update extension like:

UPDATE sequence_numbers

SET @.max = next_number = next_number + 1

WHERE name = @.blah

The above increments next_number by one and assigns the final result to max in one statement. There is no concurrency issue and the statement itself is serialized due to the exclusive lock on the table or row.

You code doesn't serialize access to getting current maximum number so it will not work.

Puzzle: dateadd workdays only

Hello Louis:
You wrote on Tue, 4 Jan 2005 15:11:01 -0600:
LD> First, why have a table named workDays that contains more than
LD> workdays?
In order to have it filled in not by myself but by HR person, using a nice
dialog with checkboxes.
...
LD> Third, this query seems to work, you can make it into a stored
LD> procedure :)
LD> declare @.f int
LD> declare @.D1 datetime
LD> set @.d1 = '2005-01-01'
LD> set @.f = 6
LD> select *
LD> from workdays
LD> where workday = 1
LD> and date > @.d1
LD> and (select count(*)
LD> from workdays as w
LD> where w.date <= workdays.date
LD> and date > @.d1
LD> and workday = 1) = @.f
the code is OK, but VERY slow. If you populate the table up to, say, 2050
and try it, you will see. Can you make a query that would deliver the result
in a second?
LD> Fourth, what is this for? Why specifically a one-query stored
LD> procedure?
As the subject says, it was a puzzle :-)
VadimOn Tue, 25 Jan 2005 11:38:53 -0600, Vadim Rapp wrote:
Hi Vadim,

> LD> Fourth, what is this for? Why specifically a one-query stored
> LD> procedure?
>As the subject says, it was a puzzle :-)
Ah - I *love* puzzles!

>the code is OK, but VERY slow. If you populate the table up to, say, 2050
>and try it, you will see. Can you make a query that would deliver the resul
t
>in a second?
Here's a query that is extremely fast if the number of workdays to add is
low. At my system, it can calculate 10 workdays ahead in less than 0.003
seconds, 100 workdays in 0.016 seconds, 1025 workdays in approx. 1 second
and 2000 workdays in under 4 seconds.
The time saving trick is to limit the search to dates in a qualifying
range: the date to be found will always be at least @.f days after the
starting date and never more than 1 and a half time @.f days after the
starting date (to which I add 3 extra days, to make sure I still get an
answer if the date submitted is a friday befor a wend that is
immediately followed by a holiday and @.f is 1). It should be possible to
reduce the range even further - e.g. by setting the lowest possible date
at start date + (1.4 * @.f) minus 1 or 2 to correct for low values of @.f,
but I don't want to do the maths required to get the "best" starting and
ending point.
The query uses a calendar table (http://www.aspfaq.com/show.asp?id=2519);
I did not test to see if a covering index would speed it up further.
select c1.dt
from dbo.Calendar AS c1
inner join dbo.Calendar AS c2
on c2.dt >= @.d1
and c2.dt < c1.dt
and c2.isWday = 1
and c2.isHoliday is null
where c1.isWday = 1
and c1.isHoliday is null
and c1.dt >= dateadd(day, @.f, @.d1)
and c1.dt <= dateadd(day, (@.f * 1.5) + 3, @.d1)
group by c1.dt
having count(*) = @.f
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Wed, 26 Jan 2005 00:25:52 +0100, Hugo Kornelis wrote:
(snip)
Woops - that was an incomplete copy/paste.
Here's the complete script:
declare @.f int
declare @.d1 smalldatetime
set @.d1 = '20050104'
set @.f = 2000
select c1.dt
from dbo.Calendar AS c1
inner join dbo.Calendar AS c2
on c2.dt >= @.d1
and c2.dt < c1.dt
and c2.isWday = 1
and c2.isHoliday is null
where c1.isWday = 1
and c1.isHoliday is null
and c1.dt >= dateadd(day, @.f, @.d1)
and c1.dt <= dateadd(day, (@.f * 1.5) + 3, @.d1)
group by c1.dt
having count(*) = @.f
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello Hugo:
You wrote in conference microsoft.public.sqlserver.programming on Wed, 26
Jan 2005 00:25:52 +0100:
HK> The time saving trick is to limit the search to dates in a qualifying
HK> range:
<snip>
HK> and c1.dt >= dateadd(day, @.f, @.d1)
HK> and c1.dt <= dateadd(day, (@.f * 1.5) + 3, @.d1)
HK> group by c1.dt
Yes, that's what I did as well. I think a sufficiently accurate range is thi
s:
if we have 5 workdays in a w
and we have N holidays in a year,
then it's safe to take the range as
between
@.date1 * 7.0/5.0 + (7-5) and
@.date1 * 7.0/5.0 + (7-5) + N * ( @.F / 365 + 1 )
HK> Ah - I *love* puzzles!
ok... then here's another one:
Find the date of the last Friday without using CASE (and in one query, of
course). If today is Friday, the result should be today's date.
regards,
Vadim Rapp
Vadim Rapp Consulting|||On Tue, 25 Jan 2005 22:30:08 -0600, Vadim Rapp wrote:
(snip)
> HK> Ah - I *love* puzzles!
>ok... then here's another one:
>Find the date of the last Friday without using CASE (and in one query, of
>course). If today is Friday, the result should be today's date.
Hi Vadim,
That's a question I've seen popping up in the newsgroups several times
lately. There are lots of possible solutions, but most of them depend on a
specific setting of SET DATEFIRST, or use a Calendar table.
Here's a solution that is independant of DATEFIRST and that won't need a
Calendar table:
SELECT DATEADD( day,
(DATEDIFF (day, '20000107', CURRENT_TIMESTAMP) / 7) * 7,
'20000107')
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

putting Word docs, etc in DB

We have a project when multiple people in the company will
want to look at Word documents, other text files, maybe
Excel spreadsheets.
Would it be best to put the location of the files in a
database and have our network mappings set up for anyone
in the company can see these files
or
Has anyone put the actual Word document in a SQL Server
(store the binary data in an Image data type) and then
tried using that to bring up the Word document? I think
storing the data from the file into an Image data type
should be fine, but I'm not sure how you'd get Word to
work with the data in the database and display the
contents.
The first option will work but there are some networking
issues. I'm just wondering if a database solution might
work and how you'd do that.
TIA,
Doughttp://www.aspfaq.com/show.asp?id=2149
"Doug" <anonymous@.discussions.microsoft.com> wrote in message
news:5a3e01c4009a$e6919280$a501280a@.phx.gbl...
> We have a project when multiple people in the company will
> want to look at Word documents, other text files, maybe
> Excel spreadsheets.
> Would it be best to put the location of the files in a
> database and have our network mappings set up for anyone
> in the company can see these files
> or
> Has anyone put the actual Word document in a SQL Server
> (store the binary data in an Image data type) and then
> tried using that to bring up the Word document? I think
> storing the data from the file into an Image data type
> should be fine, but I'm not sure how you'd get Word to
> work with the data in the database and display the
> contents.
> The first option will work but there are some networking
> issues. I'm just wondering if a database solution might
> work and how you'd do that.
> TIA,
> Dougsql