With my recent employment at my internship, I've been given the task to start
learning as much about SQL servers and querying(sp?) as possible.
I was wondering if there is anybody out there knows of some good
articles/books that would send me on my way?
I'm not looking for "super" basic stuff but as I've been exposed to
databases in the past but would like to get into more deeper topics. I guess
something along the lines of not a novice but no where near being able to
administer a DB myself.
Thanks,
BenSome good ones.
Microsoft SQL Server Books
http://vyaskn.tripod.com/sqlbooks.htm
AMB
"Ben" wrote:
> With my recent employment at my internship, I've been given the task to start
> learning as much about SQL servers and querying(sp?) as possible.
> I was wondering if there is anybody out there knows of some good
> articles/books that would send me on my way?
> I'm not looking for "super" basic stuff but as I've been exposed to
> databases in the past but would like to get into more deeper topics. I guess
> something along the lines of not a novice but no where near being able to
> administer a DB myself.
> Thanks,
> Ben|||Kalen Delaney's Inside SQL Server is my desktop bible, offers enough info on
almost all facets in depth enough to make it invaluable.
"Ben" wrote:
> With my recent employment at my internship, I've been given the task to start
> learning as much about SQL servers and querying(sp?) as possible.
> I was wondering if there is anybody out there knows of some good
> articles/books that would send me on my way?
> I'm not looking for "super" basic stuff but as I've been exposed to
> databases in the past but would like to get into more deeper topics. I guess
> something along the lines of not a novice but no where near being able to
> administer a DB myself.
> Thanks,
> Ben|||Hi,
SQL Server Books online will be a very good option.
Thanks
Hari
SQL Server MVP
"Ben" <Ben@.discussions.microsoft.com> wrote in message
news:BEA56BB4-BE39-4C1B-89EB-24B2AEE2BC81@.microsoft.com...
> With my recent employment at my internship, I've been given the task to
> start
> learning as much about SQL servers and querying(sp?) as possible.
> I was wondering if there is anybody out there knows of some good
> articles/books that would send me on my way?
> I'm not looking for "super" basic stuff but as I've been exposed to
> databases in the past but would like to get into more deeper topics. I
> guess
> something along the lines of not a novice but no where near being able to
> administer a DB myself.
> Thanks,
> Ben
Showing posts with label learning. Show all posts
Showing posts with label learning. Show all posts
Friday, March 30, 2012
New to SQL
I'm learning SQL and am trying to solve this problem. I have a table of Teams with a TeamID and another table of people with a TeamID col. I am trying to count the number of people on a team and display the team with the most people.
I have used this to choose the number of people on the teams:
SELECT tname, COUNT(tid)
FROM teams t, people p
WHERE t.tid=p.tid
GROUP BY tname;
this displays the team name and the # of members
How can I choose the team with the most members?
Could I do something such as (the syntax may not be correct, but is the overall idea?):
SELECT tname, count(*)
FROM tname
GROUP BY tname
HAVING count(*) >= all
(SELECT tname, COUNT(tid)
FROM teams t, people p
WHERE t.tid=p.tid)Try:
SELECT t.tname, count(*)
FROM teams t, people p
GROUP BY t.tname
HAVING count(*) >= ALL
( SELECT COUNT(*)
FROM people p
GROUP BY p.tid
)|||no that printed out the team names and 23 for each team. 23 is the number of rows in people|||That's because I forgot the join:
SELECT t.tname, count(*)
FROM teams t, people p
WHERE p.tid = t.tid
GROUP BY t.tname
HAVING count(*) >= ALL
( SELECT COUNT(*)
FROM people p
GROUP BY p.tid
)|||thanks that worked... Maybe someone could explain how to think through a query in order to solve it...I have programmed C++ which is a procedural, and SQL is not. I'm having trouble relating a question given in english to how it should be written in SQL|||Yes, it does require a different frame of mind. I'll try to explain how I got to it...
The requirement is "display the team with the most people". To find out the number of people in each team we need to look at the people table:
SELECT p.tid, count(*)
FROM people p
GROUP BY p.tid;
But from those results we only want that group having (hint) the highest count:
SELECT p.tid, count(*)
FROM people p
GROUP BY p.tid
HAVING count(*) >= ALL (<counts by p.tid>;
Now this query serves for <counts by p.tid>:
SELECT COUNT(*)
FROM people p
GROUP BY p.tid;
It will return a list of counts like:
11
7
13
2
So we now have:
SELECT p.tid, count(*)
FROM people p
GROUP BY p.tid
HAVING count(*) >= ALL
( SELECT COUNT(*)
FROM people p
GROUP BY p.tid
);
The final part is more or less cosmetic: show the team name instead of the tid. We do that by joining to the team table in the main query, and then grouping by the team name instead of the tid (since, I assumed, both are unique within the teams table). That gives us the final query:
SELECT t.tname, count(*)
FROM teams t, people p
WHERE p.tid = t.tid
GROUP BY t.tname
HAVING count(*) >= ALL
( SELECT COUNT(*)
FROM people p
GROUP BY p.tid
);
There is more than one way to do it, and it is really a matter of experience and practice to become proficient at solving such problems.|||Thank you. That was explained well.|||Suppose I wanted to see which teams had 4 or more. I tried to change all to 4, which worked, but when I tried to output the names of people, nothing was displayed. I assume it because this column is removed some where in the having clause, but how do I retain that information...once again, thanks for taking the time to write the last reply!|||Hello,
Well you should have had :
SELECT t.tname, count(*)
FROM teams t, people p
WHERE p.tid = t.tid
GROUP BY t.tname
HAVING count(*) >=4;
So you display team names and their number of players for teams having at least 4 players.
Now, I don't understand why you speak of people name. You won't have them with this query.
What do you exactly want ?
Regards,
RBARAER|||Following on from RBARAER's query, you can get all the people's names like this:
SELECT t.tname, p.pname
FROM teams t, people p
WHERE p.tid = t.tid
AND p.tid IN
( SELECT p.tid, count(*)
FROM people p
GROUP BY p.tid
HAVING count(*) >=4
)
ORDER BY t.tname, p.pname;|||I'm sorry, when I said people name, it is actually fname and lname. when i tried the code it gave me an error
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
why does it not let me enter p.lname and p.fname in the outer select?|||NO WAIT...it wasnt the outer select, it was the inner...it does not allow p.tid in the inner select statement. I removed it and it did not produce the correct answer. It returned a group name with 4 members and a gname with 1 member|||Then:
SELECT t.tname, p.fname, p.lname
FROM teams t, people p
WHERE p.tid = t.tid
AND p.tid IN
( SELECT p.tid
FROM people p
GROUP BY p.tid
HAVING count(*) >=4
)
ORDER BY t.tname, p.pname;
If that doesn't work, please post the exact SQL and error code/message.
(I have removed the COUNT(*) that I accidently left in before.)|||perfect!! How does it count the number of members without actually using count?|||oops...sorry, didnt notice it in the having
I have used this to choose the number of people on the teams:
SELECT tname, COUNT(tid)
FROM teams t, people p
WHERE t.tid=p.tid
GROUP BY tname;
this displays the team name and the # of members
How can I choose the team with the most members?
Could I do something such as (the syntax may not be correct, but is the overall idea?):
SELECT tname, count(*)
FROM tname
GROUP BY tname
HAVING count(*) >= all
(SELECT tname, COUNT(tid)
FROM teams t, people p
WHERE t.tid=p.tid)Try:
SELECT t.tname, count(*)
FROM teams t, people p
GROUP BY t.tname
HAVING count(*) >= ALL
( SELECT COUNT(*)
FROM people p
GROUP BY p.tid
)|||no that printed out the team names and 23 for each team. 23 is the number of rows in people|||That's because I forgot the join:
SELECT t.tname, count(*)
FROM teams t, people p
WHERE p.tid = t.tid
GROUP BY t.tname
HAVING count(*) >= ALL
( SELECT COUNT(*)
FROM people p
GROUP BY p.tid
)|||thanks that worked... Maybe someone could explain how to think through a query in order to solve it...I have programmed C++ which is a procedural, and SQL is not. I'm having trouble relating a question given in english to how it should be written in SQL|||Yes, it does require a different frame of mind. I'll try to explain how I got to it...
The requirement is "display the team with the most people". To find out the number of people in each team we need to look at the people table:
SELECT p.tid, count(*)
FROM people p
GROUP BY p.tid;
But from those results we only want that group having (hint) the highest count:
SELECT p.tid, count(*)
FROM people p
GROUP BY p.tid
HAVING count(*) >= ALL (<counts by p.tid>;
Now this query serves for <counts by p.tid>:
SELECT COUNT(*)
FROM people p
GROUP BY p.tid;
It will return a list of counts like:
11
7
13
2
So we now have:
SELECT p.tid, count(*)
FROM people p
GROUP BY p.tid
HAVING count(*) >= ALL
( SELECT COUNT(*)
FROM people p
GROUP BY p.tid
);
The final part is more or less cosmetic: show the team name instead of the tid. We do that by joining to the team table in the main query, and then grouping by the team name instead of the tid (since, I assumed, both are unique within the teams table). That gives us the final query:
SELECT t.tname, count(*)
FROM teams t, people p
WHERE p.tid = t.tid
GROUP BY t.tname
HAVING count(*) >= ALL
( SELECT COUNT(*)
FROM people p
GROUP BY p.tid
);
There is more than one way to do it, and it is really a matter of experience and practice to become proficient at solving such problems.|||Thank you. That was explained well.|||Suppose I wanted to see which teams had 4 or more. I tried to change all to 4, which worked, but when I tried to output the names of people, nothing was displayed. I assume it because this column is removed some where in the having clause, but how do I retain that information...once again, thanks for taking the time to write the last reply!|||Hello,
Well you should have had :
SELECT t.tname, count(*)
FROM teams t, people p
WHERE p.tid = t.tid
GROUP BY t.tname
HAVING count(*) >=4;
So you display team names and their number of players for teams having at least 4 players.
Now, I don't understand why you speak of people name. You won't have them with this query.
What do you exactly want ?
Regards,
RBARAER|||Following on from RBARAER's query, you can get all the people's names like this:
SELECT t.tname, p.pname
FROM teams t, people p
WHERE p.tid = t.tid
AND p.tid IN
( SELECT p.tid, count(*)
FROM people p
GROUP BY p.tid
HAVING count(*) >=4
)
ORDER BY t.tname, p.pname;|||I'm sorry, when I said people name, it is actually fname and lname. when i tried the code it gave me an error
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
why does it not let me enter p.lname and p.fname in the outer select?|||NO WAIT...it wasnt the outer select, it was the inner...it does not allow p.tid in the inner select statement. I removed it and it did not produce the correct answer. It returned a group name with 4 members and a gname with 1 member|||Then:
SELECT t.tname, p.fname, p.lname
FROM teams t, people p
WHERE p.tid = t.tid
AND p.tid IN
( SELECT p.tid
FROM people p
GROUP BY p.tid
HAVING count(*) >=4
)
ORDER BY t.tname, p.pname;
If that doesn't work, please post the exact SQL and error code/message.
(I have removed the COUNT(*) that I accidently left in before.)|||perfect!! How does it count the number of members without actually using count?|||oops...sorry, didnt notice it in the having
Wednesday, March 28, 2012
New to MS SQL
I have a rookie question, I have used Access in the past and I am in the beginning stages of learning MS SQL. Does MS SQL have anything that is equilivant to the "memo" data type in Access so that you can format the data to make notes more readable by honoring the <carriage return> and the <tab> in your free text?Yeah, TEXT datatype (which is not the Access datatype)
Don't use it...
can you live with varchar(8000)|||Thanks for the info... I'll see what happens there.sql
Don't use it...
can you live with varchar(8000)|||Thanks for the info... I'll see what happens there.sql
new to cursors
Can anyone point me to a good resource for learning cursors in MSSQL?
Thanks
DaveDave,
Try www.TechnicalVideos.net. The videos on triggers and Stored
Procedures will help you a lot. There is also a video specifically dealing
with cursors.
Hope this helps,
Chuck Conover
www.TechnicalVideos.net
"Dave Anderson" <anderdw2@.cvn.net> wrote in message
news:ELORb.112$uM2.98@.newsread1.news.pas.earthlink .net...
> Can anyone point me to a good resource for learning cursors in MSSQL?
> Thanks
> Dave|||Remember that you should generally try to avoid using cursors at all. They
are not usually a good solution to a problem in SQL because of their
performance and resource constraints compared to the set-based alternatives.
My view is that the majority of cursors fall into one of three categories:
Procedural administrative tasks (a reasonable use of a cursor); Written by
procedural programmers who don't know SQL; Used to work around a poor data
design (e.g. lack of keys in tables). There are other cases but they are few
and far between in my experience.
--
David Portas
SQL Server MVP
--|||David,
I'm not disagreeing exactly, but I would be interested in your opinion.
Typically I use cursors to load data from an outside source. Since you
can't control what that outside data looks like, I will do the following:
- load data into a temp table
- cursor thru the temp table, verifying data types and generally massaging
the data, if needed
- insert into our production table(s) if the data passes examination (done
in the cursor)
I'm not certain if you'd consider this an administrative task. Some of the
data checking we do would be hard or impossible to do using a set-based
structure, I think.
Appreciate your views.
Best regards,
Chuck
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:JbWdnTdWGp4je4rd4p2dnA@.giganews.com...
> Remember that you should generally try to avoid using cursors at all. They
> are not usually a good solution to a problem in SQL because of their
> performance and resource constraints compared to the set-based
alternatives.
> My view is that the majority of cursors fall into one of three categories:
> Procedural administrative tasks (a reasonable use of a cursor); Written by
> procedural programmers who don't know SQL; Used to work around a poor data
> design (e.g. lack of keys in tables). There are other cases but they are
few
> and far between in my experience.
> --
> David Portas
> SQL Server MVP
> --|||"Chuck Conover" <cconover@.commspeed.net> wrote in
news:1075323791.146752@.news.commspeed.net:
> David,
> I'm not disagreeing exactly, but I would be interested in your
> opinion. Typically I use cursors to load data from an outside
> source. Since you can't control what that outside data looks like,
> I will do the following:
> - load data into a temp table> - cursor thru the temp table,
verifying data types and generally
> massaging the data, if needed
> - insert into our production table(s) if the data passes
> examination (done in the cursor)
Here's what I do:
1) bcp the data to load into a staging table
2) using set-based logic to validate against master tables putting the
'clean' results into a 'good' table and 'bad' data into another
(for later reporting)
3) once all the validation is done, a single insert from the 'good'
table is used to slam all the data into the target table.
--
Pablo Sanchez - Blueoak Database Engineering, Inc
http://www.blueoakdb.com|||As Pablo says, many data cleansing and transformation operations can be
performed using set-based statements, even if you don't have keys in your
source data. For the rest I would use a dedicated ETL tool. DTS is the
component that ships with SQLServer but there are also other packages
specifically designed to automate the type of data prep you are describing
and then load the cleansed data into your database. This approach has lots
of advantages over hand-coded conversions in an RDBMS that isn't really
designed and optimised for the job.
http://pervasive.datajunction.com/djcosmos/
http://www.embarcadero.com/products/dtstudio/index.html
http://www.informatica.com
--
David Portas
SQL Server MVP
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> My view is that the majority of cursors fall into one of three
> categories: Procedural administrative tasks (a reasonable use of a
> cursor); Written by procedural programmers who don't know SQL; Used to
> work around a poor data design (e.g. lack of keys in tables). There are
> other cases but they are few and far between in my experience.
I'll add one more case: you already have a stored procedures that
performs some complex logic, and this procedure accepts its input
in scalar parameters, and you want to apply that logic to entire
set of data.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in
news:Xns947F2060989AYazorman@.127.0.0.1:
> David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> I'll add one more case: you already have a stored procedures that
> performs some complex logic, and this procedure accepts its input
> in scalar parameters, and you want to apply that logic to entire
> set of data.
I'll counter the above with the fact that you're using the wrong
tool for the job. The SP was written to handle single row inserts
therefore if you plan on doing batch inserts, then the SQL should be
written and tuned accordingly. Unless you wish this side of sucky
performance. :)
--
Pablo Sanchez - Blueoak Database Engineering, Inc
http://www.blueoakdb.com|||>> My view is that the majority of cursors fall into one of three
categories:
Procedural administrative tasks (a reasonable use of a cursor);
Written by procedural programmers who don't know SQL; Used to work
around a poor data design (e.g. lack of keys in tables). There are
other cases but they are few and far between in my experience. <<
The only other one that comes to mind is an NP complete problem
(traveling salesman, etc.) where you can use the first near-optimal
answer. Being a set-oriented language, SQL tends to find the **entire
set** of solutions and that can take a **lot** of time. Thank god you
don't run intot hem very often.|||Pablo Sanchez (honeypot@.blueoakdb.com) writes:
> Erland Sommarskog <sommar@.algonet.se> wrote in
> news:Xns947F2060989AYazorman@.127.0.0.1:
>> I'll add one more case: you already have a stored procedures that
>> performs some complex logic, and this procedure accepts its input
>> in scalar parameters, and you want to apply that logic to entire
>> set of data.
> I'll counter the above with the fact that you're using the wrong
> tool for the job. The SP was written to handle single row inserts
> therefore if you plan on doing batch inserts, then the SQL should be
> written and tuned accordingly. Unless you wish this side of sucky
> performance. :)
I'm not talking of simple SPs that inserts a single row into a table.
I'm talking about stored procedures with more than 1500 lines of code,
and which calls plenty of over procedures, activating another 1500 lines
of code. Those lines of code include important business rules, that you
don't want to have duplicated in a scalar version of the procedure and
a table-oriented one. And when many of the calls to the procedure for
busieness reasons are in fact one off, there may be a performance penalty
of the procedure is rewritten to be table-oriented.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Most of our data comes from outside sources in which we have no
control over the quality or accuracy of the data. As a business, we
felt that it was more important to get 99% of the data into our
systems, then deal with those rows that fail afterwards.
Since I was unable to figured out a way to do this as sets (since a
set either all works or all fails together) I have had to cursor
processing all over the place. I also make use of stored procedures
which (as far as I know) cannot be passed in data sets, only scalar
values.|||Another thought (more of a question).
When you need to provide logging (auditing) of each transaction the
easiest way is to run through each row. Do the action, then log it. Go
to next row.
Am I wrong? What are the alternatives?
I provide a real life situation as an example. Derivatives (futures,
options, and future options) are stocks that expire at some date.
After their expiry date, I need to mark the record in our Security
Master table as being inactive. I want a log of each security that was
marked as inactive. If I do not use a cursor, how else can I do it?
The only other way I can think our is do perform the query twice (one
select, then one update) inside a begin / end transaction. The select
statement would give me info to output to a log file and the update
would mark it as inactive...|||Erland Sommarskog <sommar@.algonet.se> wrote in
news:Xns94803655A9EEYazorman@.127.0.0.1:
> I'm not talking of simple SPs that inserts a single row into a
Nope, I didn't assume that you were ... I assumed complicated beasts.
> Those lines of code include important business rules, that you don't
> want to have duplicated in a scalar version of the procedure and a
> table-oriented one.
If the batch processing is important, you do. If it's not something
that requires to be loaded within a time constraint, I completely
agree with you.
> And when many of the calls to the procedure for busieness reasons
> are in fact one off, there may be a performance penalty of the
> procedure is rewritten to be table-oriented.
For example?
--
Pablo Sanchez - Blueoak Database Engineering, Inc
http://www.blueoakdb.com|||> After their expiry date, I need to mark the record in our Security
> Master table as being inactive.
Why? If the expiry date is recorded in your system then you already *know*
whether a stock has expired or not based on the current date and time. An
active / inactive column would just be redundant data. Put the status in a
view if you like - not in a table.
> The only other way I can think our is do perform the query twice (one
> select, then one update)
Yes. But you would need two statements whether you do it in a cursor or not.
It should be quicker without a cursor.
> inside a begin / end transaction. The select
If you really wanted to do it you don't need a transaction but I don't see
the point of the AuditLog unless Stocks are going to change Inactive ->
Active as well as Active -> Inactive. On limited info here's a guess:
INSERT INTO AuditLog (status, col1, col2, ... )
SELECT 'inactive', col1, col2, ...
FROM Stocks
WHERE expirydate <= CURRENT_TIMESTAMP
UPDATE Stocks
SET status = 'inactive'
WHERE status = 'active'
AND EXISTS
(SELECT *
FROM AuditLog
WHERE status = 'inactive'
AND pkcol = Stocks.pkcol)
--
David Portas
SQL Server MVP
--|||> Most of our data comes from outside sources in which we have no
> control over the quality or accuracy of the data. As a business, we
> felt that it was more important to get 99% of the data into our
> systems, then deal with those rows that fail afterwards.
See my reply earlier in this thread. There are tools specifically designed
to solve this problem.
--
David Portas
SQL Server MVP
--|||Pablo Sanchez (honeypot@.blueoakdb.com) writes:
> Erland Sommarskog <sommar@.algonet.se> wrote in
> news:Xns94803655A9EEYazorman@.127.0.0.1:
>> And when many of the calls to the procedure for busieness reasons
>> are in fact one off, there may be a performance penalty of the
>> procedure is rewritten to be table-oriented.
> For example?
We did take to task to rewrite one of our procedures to be table-oriented.
We did this, because this one creates an account transaction, updates
positions, balances and a whole lot more things. The scope for the database
transaction for this may be a singe account transaction, for instance a
simple deposit of money. The scope may also be over 50000 account
transactions, for instance capitalization of interest, or a corporate
action in a major company like Ericsson.
The outcome of this adventure is that we can now rewrite the multi-
transaction updaets to be set-based and be a lot faster than before.
But anything that is still one-by-one due to legacy is now slower,
say one second instead of 200 ms. Instead of having single values
in variables, it is now in 43 table variables, and that is of course
slower.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
>> After their expiry date, I need to mark the record in our Security
>> Master table as being inactive.
> Why? If the expiry date is recorded in your system then you already *know*
> whether a stock has expired or not based on the current date and time. An
> active / inactive column would just be redundant data. Put the status in a
> view if you like - not in a table.
It isn't that easy. I don't know Jason's business, but I know my own.
When an instrument has expired, it should indeed be inactivated, or
deregistered to use the terminology in our system. But you cannot
deregister if there are still are positions or unsettled trades. Even
if the instrument has expired, you may still have to register transactions
in it. For instance, you may not until now discover that you have
registered a trade for the wrong account, and have to cancel and
create a replacement note. So even if the instrument is expired, it
should still be fully valid in transactions - but of course there
should be validation that you don't specify a trade date after
expiration.
Once everything has been cleared up, all trades resulting from expiration
has been registered, and all unused options has been booked out, you
can deregister the instrument.
But there is of course no reason to use a cursor just because of this.
A very simple-minded approach is:
INSERT #temp(id)
SELECT id
FROM instruments
WHERE -- should be deregistered
UPDATE instruments
SET deregdate = getdate()
FROM instruments ins
JOIN #temp t ON ins.id = t.id
INSERT audirlog (...)
SELECT ...
FROM #temp ...
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in
news:Xns94814EAFE04FYazorman@.127.0.0.1:
> The outcome of this adventure is that we can now rewrite the
> multi- transaction updaets to be set-based and be a lot faster
> than before. But anything that is still one-by-one due to legacy
> is now slower, say one second instead of 200 ms. Instead of having
> single values in variables, it is now in 43 table variables, and
> that is of course slower.
[ I don't know if you want to pursue it further so if you don't
respond, I'll assume not. ]
The fact that there's some legacy sounds like that legacy code needs
to be refactored as well. It's only natural that that's what needs
to be done when taking row-at-a-time and converting to set-based.
--
Pablo Sanchez - Blueoak Database Engineering, Inc
http://www.blueoakdb.com|||Pablo Sanchez (honeypot@.blueoakdb.com) writes:
> The fact that there's some legacy sounds like that legacy code needs
> to be refactored as well. It's only natural that that's what needs
> to be done when taking row-at-a-time and converting to set-based.
Needs to is a relative item.
I don't know how much work it took to do rewrite that particular
stored procedure, but I seem to recall that my time estimate was 100
hours. In a company like hours, 100 hours is not something you take out
of thin air.
Some of these jobs running one-by-one have been rewritten, rest assured.
But there is at least one process where a rewrite will take another
100 hours, maybe more.) (This one is however not keeping all calls in one
database transaction.) And I would not expect this to happen to either
we have someone coughing up money for it, or a customer yelling loud
enough about the performance. (The latter is of course much more likely
than the former.)
Anyway, this particular procedure that we rewrote had to be rewritten, for
our system being able to scale. But I have another case, where most calls
are one at a time, and where only one functions make sucessive calls, and
this function is used by one customer only. I'd be cautious before I init
a rewrite here.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for the imput guys. I thought about auditing everything as a
step and then performing the action as another step but I felt it was
somehow "wrong".
Erland summed it up nicely about the inactive status and unprocessed
trades. To add to that, there are other status's (like Halted) that
can be applied to a security. That is another reason for the Status
column.
New to asp
I am in a learning mode here as all my previous work has been with php.
I am trying to make an OLE DB connection.to SQL Server Express on my local
machine using ASP and VBscript. I followed the instructions for making a
NewConnection.udl and double clicked that, filled in the spots and tested
the connection. Everything was OK. When I put it into my asp code, using
that string in the conn.Open statement, I got the error message that it
couldn't open the database that I had specified in the connection string.
(I copied and pasted that string from the .udl file where the test
connection succeeded). This was with Windows Authorization.
I also created a user with the name ASPpage and password asptest. I gave
that user all the privileges using Sql Server Manager Studio Express. When
I changed the string so that it had a user id and a password (SQL Server
authentication), I got the message that the login failed for ASPpage. Note,
also, that I couln not create a connection with that user in Sql Server
Manager Studio Express, but I could make a connection with Windows
authentication.
The failure for the one with the user id is that the user is not associated
with a trusted SQL server connection (even though he has all privileges).
Any suggestions on how to proceed.
Here is the code:
<%
dim q, num, i, acct, coName
set Conn = Server.CreateObject("ADODB.Connection")
q = "SELECT * FROM Company"
Conn.Open "Provider=SQLOLEDB.1;Integrated Security-SSPI;Persist Security
Info=False;Initial Catalog=the_database_name;Data Source=LAPTOP\SQLEXPRESS"
set rs = Conn.Execute(q)
set of = Server.CreateObject("ADODB.Field")
num = rs.Count
for i=0 To num - 1
acct = of("accountNumber")
coName = of("companyName")
Response.Write(acct & " " & coName & "<br>")
next
conn.Close
%>
When I put in to user info, I removed the Integrated Security stuff.
Thanks for any help
Shelly (Sheldon)You look forgot changing Authentication Method from Windows Authentication
to Mixed Authentication (SQL Server and Windows Authentication) from Server
Properties\Security.
--
Ekrem Önsoy
"Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
news:13gfs30mhrjej53@.corp.supernews.com...
>I am in a learning mode here as all my previous work has been with php.
> I am trying to make an OLE DB connection.to SQL Server Express on my local
> machine using ASP and VBscript. I followed the instructions for making a
> NewConnection.udl and double clicked that, filled in the spots and tested
> the connection. Everything was OK. When I put it into my asp code, using
> that string in the conn.Open statement, I got the error message that it
> couldn't open the database that I had specified in the connection string.
> (I copied and pasted that string from the .udl file where the test
> connection succeeded). This was with Windows Authorization.
> I also created a user with the name ASPpage and password asptest. I gave
> that user all the privileges using Sql Server Manager Studio Express.
> When I changed the string so that it had a user id and a password (SQL
> Server authentication), I got the message that the login failed for
> ASPpage. Note, also, that I couln not create a connection with that user
> in Sql Server Manager Studio Express, but I could make a connection with
> Windows authentication.
> The failure for the one with the user id is that the user is not
> associated with a trusted SQL server connection (even though he has all
> privileges).
> Any suggestions on how to proceed.
> Here is the code:
> <%
> dim q, num, i, acct, coName
> set Conn = Server.CreateObject("ADODB.Connection")
> q = "SELECT * FROM Company"
> Conn.Open "Provider=SQLOLEDB.1;Integrated Security-SSPI;Persist Security
> Info=False;Initial Catalog=the_database_name;Data
> Source=LAPTOP\SQLEXPRESS"
> set rs = Conn.Execute(q)
> set of = Server.CreateObject("ADODB.Field")
> num = rs.Count
> for i=0 To num - 1
> acct = of("accountNumber")
> coName = of("companyName")
> Response.Write(acct & " " & coName & "<br>")
> next
> conn.Close
> %>
> When I put in to user info, I removed the Integrated Security stuff.
> Thanks for any help
> Shelly (Sheldon)
>
>|||"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:B0697784-4125-4B7E-833F-AA905D02AB02@.microsoft.com...
> You look forgot changing Authentication Method from Windows Authentication
> to Mixed Authentication (SQL Server and Windows Authentication) from
> Server Properties\Security.
I'm sorry but I don't understand what you are saying. Where do you find
"Server Properties"? When I tried a new connection and switched from
Windows Authentication to SQL Server, it didn't work.
> --
> Ekrem Önsoy
>
> "Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
> news:13gfs30mhrjej53@.corp.supernews.com...
>>I am in a learning mode here as all my previous work has been with php.
>> I am trying to make an OLE DB connection.to SQL Server Express on my
>> local machine using ASP and VBscript. I followed the instructions for
>> making a NewConnection.udl and double clicked that, filled in the spots
>> and tested the connection. Everything was OK. When I put it into my asp
>> code, using that string in the conn.Open statement, I got the error
>> message that it couldn't open the database that I had specified in the
>> connection string. (I copied and pasted that string from the .udl file
>> where the test connection succeeded). This was with Windows
>> Authorization.
>> I also created a user with the name ASPpage and password asptest. I gave
>> that user all the privileges using Sql Server Manager Studio Express.
>> When I changed the string so that it had a user id and a password (SQL
>> Server authentication), I got the message that the login failed for
>> ASPpage. Note, also, that I couln not create a connection with that user
>> in Sql Server Manager Studio Express, but I could make a connection with
>> Windows authentication.
>> The failure for the one with the user id is that the user is not
>> associated with a trusted SQL server connection (even though he has all
>> privileges).
>> Any suggestions on how to proceed.
>> Here is the code:
>> <%
>> dim q, num, i, acct, coName
>> set Conn = Server.CreateObject("ADODB.Connection")
>> q = "SELECT * FROM Company"
>> Conn.Open "Provider=SQLOLEDB.1;Integrated Security-SSPI;Persist Security
>> Info=False;Initial Catalog=the_database_name;Data
>> Source=LAPTOP\SQLEXPRESS"
>> set rs = Conn.Execute(q)
>> set of = Server.CreateObject("ADODB.Field")
>> num = rs.Count
>> for i=0 To num - 1
>> acct = of("accountNumber")
>> coName = of("companyName")
>> Response.Write(acct & " " & coName & "<br>")
>> next
>> conn.Close
>> %>
>> When I put in to user info, I removed the Integrated Security stuff.
>> Thanks for any help
>> Shelly (Sheldon)
>>
>|||You get that setting you right click on the server name goto properties >
seucrity > seclect SQL Server and Windows Authentication as Ekrem said.
But looking at your connection string you have an error ... Maybe a mistype.
But here are two possible connection strings for SQL 2005:
Trusted Connection:
Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated
Security=SSPI;
SQL User Name Connection:
Server=myServerAddress;Database=myDataBase;User
ID=myUsername;Password=myPassword;Trusted_Connection=False;
Also note if you are trying to use integrated security in ASP, the IIS
username IUSR account must have access to SQL server not your account.
Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Shelly" wrote:
> "Ekrem Ã?nsoy" <ekrem@.btegitim.com> wrote in message
> news:B0697784-4125-4B7E-833F-AA905D02AB02@.microsoft.com...
> > You look forgot changing Authentication Method from Windows Authentication
> > to Mixed Authentication (SQL Server and Windows Authentication) from
> > Server Properties\Security.
> I'm sorry but I don't understand what you are saying. Where do you find
> "Server Properties"? When I tried a new connection and switched from
> Windows Authentication to SQL Server, it didn't work.
>
> > --
> > Ekrem Ã?nsoy
> >
> >
> >
> > "Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
> > news:13gfs30mhrjej53@.corp.supernews.com...
> >>I am in a learning mode here as all my previous work has been with php.
> >>
> >> I am trying to make an OLE DB connection.to SQL Server Express on my
> >> local machine using ASP and VBscript. I followed the instructions for
> >> making a NewConnection.udl and double clicked that, filled in the spots
> >> and tested the connection. Everything was OK. When I put it into my asp
> >> code, using that string in the conn.Open statement, I got the error
> >> message that it couldn't open the database that I had specified in the
> >> connection string. (I copied and pasted that string from the .udl file
> >> where the test connection succeeded). This was with Windows
> >> Authorization.
> >>
> >> I also created a user with the name ASPpage and password asptest. I gave
> >> that user all the privileges using Sql Server Manager Studio Express.
> >> When I changed the string so that it had a user id and a password (SQL
> >> Server authentication), I got the message that the login failed for
> >> ASPpage. Note, also, that I couln not create a connection with that user
> >> in Sql Server Manager Studio Express, but I could make a connection with
> >> Windows authentication.
> >>
> >> The failure for the one with the user id is that the user is not
> >> associated with a trusted SQL server connection (even though he has all
> >> privileges).
> >>
> >> Any suggestions on how to proceed.
> >>
> >> Here is the code:
> >>
> >> <%
> >> dim q, num, i, acct, coName
> >> set Conn = Server.CreateObject("ADODB.Connection")
> >> q = "SELECT * FROM Company"
> >> Conn.Open "Provider=SQLOLEDB.1;Integrated Security-SSPI;Persist Security
> >> Info=False;Initial Catalog=the_database_name;Data
> >> Source=LAPTOP\SQLEXPRESS"
> >> set rs = Conn.Execute(q)
> >> set of = Server.CreateObject("ADODB.Field")
> >> num = rs.Count
> >> for i=0 To num - 1
> >> acct = of("accountNumber")
> >> coName = of("companyName")
> >> Response.Write(acct & " " & coName & "<br>")
> >> next
> >> conn.Close
> >> %>
> >>
> >> When I put in to user info, I removed the Integrated Security stuff.
> >>
> >> Thanks for any help
> >>
> >> Shelly (Sheldon)
> >>
> >>
> >>
> >
>
>|||"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:DAC9B2CE-E61A-4AFE-854F-224A4E7566AC@.microsoft.com...
> You get that setting you right click on the server name goto properties >
> seucrity > seclect SQL Server and Windows Authentication as Ekrem said.
Thanks. When I do that in SQL Server Management Studio Express and hit the
"Test" button, I get that the testing failed because the user is not
associated with a trusted SQL Serverver connection. (Error 18452)
> But looking at your connection string you have an error ... Maybe a
> mistype.
> But here are two possible connection strings for SQL 2005:
> Trusted Connection:
> Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated
> Security=SSPI;
What about "Provider"? Also, should I leave out "Persist Security Info"?
> SQL User Name Connection:
> Server=myServerAddress;Database=myDataBase;User
> ID=myUsername;Password=myPassword;Trusted_Connection=False;
What about "Provider"? Also, I can't really test this one further until I
somehow get those credentials working in SQL Server Management Studio
Express.
> Also note if you are trying to use integrated security in ASP, the IIS
> username IUSR account must have access to SQL server not your account.
Could you expand upon this please? Where do I find/set the IIS username?
Thanks.
Shelly
> Thanks!
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "Shelly" wrote:
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:B0697784-4125-4B7E-833F-AA905D02AB02@.microsoft.com...
>> > You look forgot changing Authentication Method from Windows
>> > Authentication
>> > to Mixed Authentication (SQL Server and Windows Authentication) from
>> > Server Properties\Security.
>> I'm sorry but I don't understand what you are saying. Where do you find
>> "Server Properties"? When I tried a new connection and switched from
>> Windows Authentication to SQL Server, it didn't work.
>>
>> > --
>> > Ekrem Önsoy
>> >
>> >
>> >
>> > "Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
>> > news:13gfs30mhrjej53@.corp.supernews.com...
>> >>I am in a learning mode here as all my previous work has been with php.
>> >>
>> >> I am trying to make an OLE DB connection.to SQL Server Express on my
>> >> local machine using ASP and VBscript. I followed the instructions for
>> >> making a NewConnection.udl and double clicked that, filled in the
>> >> spots
>> >> and tested the connection. Everything was OK. When I put it into my
>> >> asp
>> >> code, using that string in the conn.Open statement, I got the error
>> >> message that it couldn't open the database that I had specified in the
>> >> connection string. (I copied and pasted that string from the .udl file
>> >> where the test connection succeeded). This was with Windows
>> >> Authorization.
>> >>
>> >> I also created a user with the name ASPpage and password asptest. I
>> >> gave
>> >> that user all the privileges using Sql Server Manager Studio Express.
>> >> When I changed the string so that it had a user id and a password (SQL
>> >> Server authentication), I got the message that the login failed for
>> >> ASPpage. Note, also, that I couln not create a connection with that
>> >> user
>> >> in Sql Server Manager Studio Express, but I could make a connection
>> >> with
>> >> Windows authentication.
>> >>
>> >> The failure for the one with the user id is that the user is not
>> >> associated with a trusted SQL server connection (even though he has
>> >> all
>> >> privileges).
>> >>
>> >> Any suggestions on how to proceed.
>> >>
>> >> Here is the code:
>> >>
>> >> <%
>> >> dim q, num, i, acct, coName
>> >> set Conn = Server.CreateObject("ADODB.Connection")
>> >> q = "SELECT * FROM Company"
>> >> Conn.Open "Provider=SQLOLEDB.1;Integrated Security-SSPI;Persist
>> >> Security
>> >> Info=False;Initial Catalog=the_database_name;Data
>> >> Source=LAPTOP\SQLEXPRESS"
>> >> set rs = Conn.Execute(q)
>> >> set of = Server.CreateObject("ADODB.Field")
>> >> num = rs.Count
>> >> for i=0 To num - 1
>> >> acct = of("accountNumber")
>> >> coName = of("companyName")
>> >> Response.Write(acct & " " & coName & "<br>")
>> >> next
>> >> conn.Close
>> >> %>
>> >>
>> >> When I put in to user info, I removed the Integrated Security stuff.
>> >>
>> >> Thanks for any help
>> >>
>> >> Shelly (Sheldon)
>> >>
>> >>
>> >>
>> >
>>|||"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:DAC9B2CE-E61A-4AFE-854F-224A4E7566AC@.microsoft.com...
> You get that setting you right click on the server name goto properties >
> seucrity > seclect SQL Server and Windows Authentication as Ekrem said.
> But looking at your connection string you have an error ... Maybe a
> mistype.
> But here are two possible connection strings for SQL 2005:
It was a typo.
> Trusted Connection:
> Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated
> Security=SSPI;
When I tried this string (without Provider, etc.) I got an error message of
"Multiple-step OLE DB operation generated errors. Check each OLE DB status
value, if available. No work was done". This was at the Conn.Open line.
Before I removed the Provider and Persist Security, it said that it failed
to open the_database_name as requested by the login.
Shelly
> SQL User Name Connection:
> Server=myServerAddress;Database=myDataBase;User
> ID=myUsername;Password=myPassword;Trusted_Connection=False;
> Also note if you are trying to use integrated security in ASP, the IIS
> username IUSR account must have access to SQL server not your account.
> Thanks!
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "Shelly" wrote:
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:B0697784-4125-4B7E-833F-AA905D02AB02@.microsoft.com...
>> > You look forgot changing Authentication Method from Windows
>> > Authentication
>> > to Mixed Authentication (SQL Server and Windows Authentication) from
>> > Server Properties\Security.
>> I'm sorry but I don't understand what you are saying. Where do you find
>> "Server Properties"? When I tried a new connection and switched from
>> Windows Authentication to SQL Server, it didn't work.
>>
>> > --
>> > Ekrem Önsoy
>> >
>> >
>> >
>> > "Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
>> > news:13gfs30mhrjej53@.corp.supernews.com...
>> >>I am in a learning mode here as all my previous work has been with php.
>> >>
>> >> I am trying to make an OLE DB connection.to SQL Server Express on my
>> >> local machine using ASP and VBscript. I followed the instructions for
>> >> making a NewConnection.udl and double clicked that, filled in the
>> >> spots
>> >> and tested the connection. Everything was OK. When I put it into my
>> >> asp
>> >> code, using that string in the conn.Open statement, I got the error
>> >> message that it couldn't open the database that I had specified in the
>> >> connection string. (I copied and pasted that string from the .udl file
>> >> where the test connection succeeded). This was with Windows
>> >> Authorization.
>> >>
>> >> I also created a user with the name ASPpage and password asptest. I
>> >> gave
>> >> that user all the privileges using Sql Server Manager Studio Express.
>> >> When I changed the string so that it had a user id and a password (SQL
>> >> Server authentication), I got the message that the login failed for
>> >> ASPpage. Note, also, that I couln not create a connection with that
>> >> user
>> >> in Sql Server Manager Studio Express, but I could make a connection
>> >> with
>> >> Windows authentication.
>> >>
>> >> The failure for the one with the user id is that the user is not
>> >> associated with a trusted SQL server connection (even though he has
>> >> all
>> >> privileges).
>> >>
>> >> Any suggestions on how to proceed.
>> >>
>> >> Here is the code:
>> >>
>> >> <%
>> >> dim q, num, i, acct, coName
>> >> set Conn = Server.CreateObject("ADODB.Connection")
>> >> q = "SELECT * FROM Company"
>> >> Conn.Open "Provider=SQLOLEDB.1;Integrated Security-SSPI;Persist
>> >> Security
>> >> Info=False;Initial Catalog=the_database_name;Data
>> >> Source=LAPTOP\SQLEXPRESS"
>> >> set rs = Conn.Execute(q)
>> >> set of = Server.CreateObject("ADODB.Field")
>> >> num = rs.Count
>> >> for i=0 To num - 1
>> >> acct = of("accountNumber")
>> >> coName = of("companyName")
>> >> Response.Write(acct & " " & coName & "<br>")
>> >> next
>> >> conn.Close
>> >> %>
>> >>
>> >> When I put in to user info, I removed the Integrated Security stuff.
>> >>
>> >> Thanks for any help
>> >>
>> >> Shelly (Sheldon)
>> >>
>> >>
>> >>
>> >
>>|||Shelly,
Please be sure that you have a LOGIN named ASPpage (or whatever) in main
Security node.
After that, be sure of that you have an appropriate USER which is associated
with that LOGIN and has necessary rights to perform its jobs in that
database.
Fire up SSMSE and go to Server Properties and go to Security and change the
Server authentication as "SQL Server and Windows Authentication mode" and
restart your SQL Server service and then try again.
Checklist:
In SQL Server Express, remote connections are disabled by default. So go to
Surface Area Configurations and be sure your instance is enabled for TCP\IP
or whatever protocol you use for connection.
Go to SQL Server Configuration Manager and be sure your Network
Configuration is OK. (TCP\IP or whatever is must be enabled, IP addresses
must be entered etc.)
--
Ekrem Önsoy
"Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
news:13ghjl7nvigltfb@.corp.supernews.com...
> "Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
> news:DAC9B2CE-E61A-4AFE-854F-224A4E7566AC@.microsoft.com...
>> You get that setting you right click on the server name goto properties >
>> seucrity > seclect SQL Server and Windows Authentication as Ekrem said.
>> But looking at your connection string you have an error ... Maybe a
>> mistype.
>> But here are two possible connection strings for SQL 2005:
> It was a typo.
>> Trusted Connection:
>> Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated
>> Security=SSPI;
> When I tried this string (without Provider, etc.) I got an error message
> of "Multiple-step OLE DB operation generated errors. Check each OLE DB
> status value, if available. No work was done". This was at the Conn.Open
> line. Before I removed the Provider and Persist Security, it said that it
> failed to open the_database_name as requested by the login.
> Shelly
>> SQL User Name Connection:
>> Server=myServerAddress;Database=myDataBase;User
>> ID=myUsername;Password=myPassword;Trusted_Connection=False;
>> Also note if you are trying to use integrated security in ASP, the IIS
>> username IUSR account must have access to SQL server not your account.
>> Thanks!
>> --
>> Mohit K. Gupta
>> B.Sc. CS, Minor Japanese
>> MCTS: SQL Server 2005
>>
>> "Shelly" wrote:
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:B0697784-4125-4B7E-833F-AA905D02AB02@.microsoft.com...
>> > You look forgot changing Authentication Method from Windows
>> > Authentication
>> > to Mixed Authentication (SQL Server and Windows Authentication) from
>> > Server Properties\Security.
>> I'm sorry but I don't understand what you are saying. Where do you find
>> "Server Properties"? When I tried a new connection and switched from
>> Windows Authentication to SQL Server, it didn't work.
>>
>> > --
>> > Ekrem Önsoy
>> >
>> >
>> >
>> > "Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
>> > news:13gfs30mhrjej53@.corp.supernews.com...
>> >>I am in a learning mode here as all my previous work has been with
>> >>php.
>> >>
>> >> I am trying to make an OLE DB connection.to SQL Server Express on my
>> >> local machine using ASP and VBscript. I followed the instructions
>> >> for
>> >> making a NewConnection.udl and double clicked that, filled in the
>> >> spots
>> >> and tested the connection. Everything was OK. When I put it into my
>> >> asp
>> >> code, using that string in the conn.Open statement, I got the error
>> >> message that it couldn't open the database that I had specified in
>> >> the
>> >> connection string. (I copied and pasted that string from the .udl
>> >> file
>> >> where the test connection succeeded). This was with Windows
>> >> Authorization.
>> >>
>> >> I also created a user with the name ASPpage and password asptest. I
>> >> gave
>> >> that user all the privileges using Sql Server Manager Studio Express.
>> >> When I changed the string so that it had a user id and a password
>> >> (SQL
>> >> Server authentication), I got the message that the login failed for
>> >> ASPpage. Note, also, that I couln not create a connection with that
>> >> user
>> >> in Sql Server Manager Studio Express, but I could make a connection
>> >> with
>> >> Windows authentication.
>> >>
>> >> The failure for the one with the user id is that the user is not
>> >> associated with a trusted SQL server connection (even though he has
>> >> all
>> >> privileges).
>> >>
>> >> Any suggestions on how to proceed.
>> >>
>> >> Here is the code:
>> >>
>> >> <%
>> >> dim q, num, i, acct, coName
>> >> set Conn = Server.CreateObject("ADODB.Connection")
>> >> q = "SELECT * FROM Company"
>> >> Conn.Open "Provider=SQLOLEDB.1;Integrated Security-SSPI;Persist
>> >> Security
>> >> Info=False;Initial Catalog=the_database_name;Data
>> >> Source=LAPTOP\SQLEXPRESS"
>> >> set rs = Conn.Execute(q)
>> >> set of = Server.CreateObject("ADODB.Field")
>> >> num = rs.Count
>> >> for i=0 To num - 1
>> >> acct = of("accountNumber")
>> >> coName = of("companyName")
>> >> Response.Write(acct & " " & coName & "<br>")
>> >> next
>> >> conn.Close
>> >> %>
>> >>
>> >> When I put in to user info, I removed the Integrated Security stuff.
>> >>
>> >> Thanks for any help
>> >>
>> >> Shelly (Sheldon)
>> >>
>> >>
>> >>
>> >
>>
>|||"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:B7333908-981B-436F-8CB3-4676EC53B93F@.microsoft.com...
> Shelly,
> Please be sure that you have a LOGIN named ASPpage (or whatever) in main
> Security node.
I have one under the Security tab which is under the LAPTOP|SQLEXPRESS in
the object explorer in SSMSE.
> After that, be sure of that you have an appropriate USER which is
> associated with that LOGIN and has necessary rights to perform its jobs in
> that database.
I have a user under Databases\the_database_name\Security\Users in SSMSE. It
has the same rights as the Administrator, including Connect SQL.
> Fire up SSMSE and go to Server Properties and go to Security and change
> the Server authentication as "SQL Server and Windows Authentication mode"
> and restart your SQL Server service and then try again.
Already there at Windows Authentication Mode -- but I restarted anyway.
> Checklist:
> In SQL Server Express, remote connections are disabled by default. So go
> to Surface Area Configurations and be sure your instance is enabled for
> TCP\IP or whatever protocol you use for connection.
I don't know what you mean with "Surface Area Configurations" or where to
find it. I went to the configuration manager and saw to it that shared
memory, named pipes and tcpip were all enabled. I restarted the sql server.
> Go to SQL Server Configuration Manager and be sure your Network
> Configuration is OK. (TCP\IP or whatever is must be enabled, IP addresses
> must be entered etc.)
Done. (Note that everything is taking place on my local laptop, called
LAPTOP, no external connections)
I still get the "Multiple step OLE DB operation generated errors. Check
each OLE DB status value, if available. No work was done".
I don't know where or how to check these values.
My connection line is:
Conn.Open "Integrated Security=SSPI;Initial Catalog=the_database_name;Data
Source=LAPTOP\SQLEXRESS"
Shelly
> "Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
> news:13ghjl7nvigltfb@.corp.supernews.com...
>> "Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
>> news:DAC9B2CE-E61A-4AFE-854F-224A4E7566AC@.microsoft.com...
>> You get that setting you right click on the server name goto properties
>> >
>> seucrity > seclect SQL Server and Windows Authentication as Ekrem said.
>> But looking at your connection string you have an error ... Maybe a
>> mistype.
>> But here are two possible connection strings for SQL 2005:
>> It was a typo.
>>
>> Trusted Connection:
>> Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated
>> Security=SSPI;
>> When I tried this string (without Provider, etc.) I got an error message
>> of "Multiple-step OLE DB operation generated errors. Check each OLE DB
>> status value, if available. No work was done". This was at the
>> Conn.Open line. Before I removed the Provider and Persist Security, it
>> said that it failed to open the_database_name as requested by the login.
>> Shelly
>> SQL User Name Connection:
>> Server=myServerAddress;Database=myDataBase;User
>> ID=myUsername;Password=myPassword;Trusted_Connection=False;
>> Also note if you are trying to use integrated security in ASP, the IIS
>> username IUSR account must have access to SQL server not your account.
>> Thanks!
>> --
>> Mohit K. Gupta
>> B.Sc. CS, Minor Japanese
>> MCTS: SQL Server 2005
>>
>> "Shelly" wrote:
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:B0697784-4125-4B7E-833F-AA905D02AB02@.microsoft.com...
>> > You look forgot changing Authentication Method from Windows
>> > Authentication
>> > to Mixed Authentication (SQL Server and Windows Authentication) from
>> > Server Properties\Security.
>> I'm sorry but I don't understand what you are saying. Where do you
>> find
>> "Server Properties"? When I tried a new connection and switched from
>> Windows Authentication to SQL Server, it didn't work.
>>
>> > --
>> > Ekrem Önsoy
>> >
>> >
>> >
>> > "Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
>> > news:13gfs30mhrjej53@.corp.supernews.com...
>> >>I am in a learning mode here as all my previous work has been with
>> >>php.
>> >>
>> >> I am trying to make an OLE DB connection.to SQL Server Express on my
>> >> local machine using ASP and VBscript. I followed the instructions
>> >> for
>> >> making a NewConnection.udl and double clicked that, filled in the
>> >> spots
>> >> and tested the connection. Everything was OK. When I put it into
>> >> my asp
>> >> code, using that string in the conn.Open statement, I got the error
>> >> message that it couldn't open the database that I had specified in
>> >> the
>> >> connection string. (I copied and pasted that string from the .udl
>> >> file
>> >> where the test connection succeeded). This was with Windows
>> >> Authorization.
>> >>
>> >> I also created a user with the name ASPpage and password asptest. I
>> >> gave
>> >> that user all the privileges using Sql Server Manager Studio
>> >> Express.
>> >> When I changed the string so that it had a user id and a password
>> >> (SQL
>> >> Server authentication), I got the message that the login failed for
>> >> ASPpage. Note, also, that I couln not create a connection with that
>> >> user
>> >> in Sql Server Manager Studio Express, but I could make a connection
>> >> with
>> >> Windows authentication.
>> >>
>> >> The failure for the one with the user id is that the user is not
>> >> associated with a trusted SQL server connection (even though he has
>> >> all
>> >> privileges).
>> >>
>> >> Any suggestions on how to proceed.
>> >>
>> >> Here is the code:
>> >>
>> >> <%
>> >> dim q, num, i, acct, coName
>> >> set Conn = Server.CreateObject("ADODB.Connection")
>> >> q = "SELECT * FROM Company"
>> >> Conn.Open "Provider=SQLOLEDB.1;Integrated Security-SSPI;Persist
>> >> Security
>> >> Info=False;Initial Catalog=the_database_name;Data
>> >> Source=LAPTOP\SQLEXPRESS"
>> >> set rs = Conn.Execute(q)
>> >> set of = Server.CreateObject("ADODB.Field")
>> >> num = rs.Count
>> >> for i=0 To num - 1
>> >> acct = of("accountNumber")
>> >> coName = of("companyName")
>> >> Response.Write(acct & " " & coName & "<br>")
>> >> next
>> >> conn.Close
>> >> %>
>> >>
>> >> When I put in to user info, I removed the Integrated Security stuff.
>> >>
>> >> Thanks for any help
>> >>
>> >> Shelly (Sheldon)
>> >>
>> >>
>> >>
>> >
>>
>>
>|||> Conn.Open "Integrated Security=SSPI;Initial Catalog=the_database_name;Data
> Source=LAPTOP\SQLEXRESS"
Try "Server" instead of "Data Source" and "Database" instead of "Initial
Catalog".
--
Ekrem Önsoy
"Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
news:13gmnt5drgdse27@.corp.supernews.com...
> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
> news:B7333908-981B-436F-8CB3-4676EC53B93F@.microsoft.com...
>> Shelly,
>> Please be sure that you have a LOGIN named ASPpage (or whatever) in main
>> Security node.
> I have one under the Security tab which is under the LAPTOP|SQLEXPRESS in
> the object explorer in SSMSE.
>> After that, be sure of that you have an appropriate USER which is
>> associated with that LOGIN and has necessary rights to perform its jobs
>> in that database.
> I have a user under Databases\the_database_name\Security\Users in SSMSE.
> It has the same rights as the Administrator, including Connect SQL.
>> Fire up SSMSE and go to Server Properties and go to Security and change
>> the Server authentication as "SQL Server and Windows Authentication mode"
>> and restart your SQL Server service and then try again.
> Already there at Windows Authentication Mode -- but I restarted anyway.
>> Checklist:
>> In SQL Server Express, remote connections are disabled by default. So go
>> to Surface Area Configurations and be sure your instance is enabled for
>> TCP\IP or whatever protocol you use for connection.
> I don't know what you mean with "Surface Area Configurations" or where to
> find it. I went to the configuration manager and saw to it that shared
> memory, named pipes and tcpip were all enabled. I restarted the sql
> server.
>> Go to SQL Server Configuration Manager and be sure your Network
>> Configuration is OK. (TCP\IP or whatever is must be enabled, IP addresses
>> must be entered etc.)
> Done. (Note that everything is taking place on my local laptop, called
> LAPTOP, no external connections)
> I still get the "Multiple step OLE DB operation generated errors. Check
> each OLE DB status value, if available. No work was done".
> I don't know where or how to check these values.
> My connection line is:
> Conn.Open "Integrated Security=SSPI;Initial Catalog=the_database_name;Data
> Source=LAPTOP\SQLEXRESS"
> Shelly
>> "Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
>> news:13ghjl7nvigltfb@.corp.supernews.com...
>> "Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
>> news:DAC9B2CE-E61A-4AFE-854F-224A4E7566AC@.microsoft.com...
>> You get that setting you right click on the server name goto properties
>> >
>> seucrity > seclect SQL Server and Windows Authentication as Ekrem said.
>> But looking at your connection string you have an error ... Maybe a
>> mistype.
>> But here are two possible connection strings for SQL 2005:
>> It was a typo.
>>
>> Trusted Connection:
>> Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated
>> Security=SSPI;
>> When I tried this string (without Provider, etc.) I got an error message
>> of "Multiple-step OLE DB operation generated errors. Check each OLE DB
>> status value, if available. No work was done". This was at the
>> Conn.Open line. Before I removed the Provider and Persist Security, it
>> said that it failed to open the_database_name as requested by the login.
>> Shelly
>> SQL User Name Connection:
>> Server=myServerAddress;Database=myDataBase;User
>> ID=myUsername;Password=myPassword;Trusted_Connection=False;
>> Also note if you are trying to use integrated security in ASP, the IIS
>> username IUSR account must have access to SQL server not your account.
>> Thanks!
>> --
>> Mohit K. Gupta
>> B.Sc. CS, Minor Japanese
>> MCTS: SQL Server 2005
>>
>> "Shelly" wrote:
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:B0697784-4125-4B7E-833F-AA905D02AB02@.microsoft.com...
>> > You look forgot changing Authentication Method from Windows
>> > Authentication
>> > to Mixed Authentication (SQL Server and Windows Authentication) from
>> > Server Properties\Security.
>> I'm sorry but I don't understand what you are saying. Where do you
>> find
>> "Server Properties"? When I tried a new connection and switched from
>> Windows Authentication to SQL Server, it didn't work.
>>
>> > --
>> > Ekrem Önsoy
>> >
>> >
>> >
>> > "Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
>> > news:13gfs30mhrjej53@.corp.supernews.com...
>> >>I am in a learning mode here as all my previous work has been with
>> >>php.
>> >>
>> >> I am trying to make an OLE DB connection.to SQL Server Express on
>> >> my
>> >> local machine using ASP and VBscript. I followed the instructions
>> >> for
>> >> making a NewConnection.udl and double clicked that, filled in the
>> >> spots
>> >> and tested the connection. Everything was OK. When I put it into
>> >> my asp
>> >> code, using that string in the conn.Open statement, I got the error
>> >> message that it couldn't open the database that I had specified in
>> >> the
>> >> connection string. (I copied and pasted that string from the .udl
>> >> file
>> >> where the test connection succeeded). This was with Windows
>> >> Authorization.
>> >>
>> >> I also created a user with the name ASPpage and password asptest.
>> >> I gave
>> >> that user all the privileges using Sql Server Manager Studio
>> >> Express.
>> >> When I changed the string so that it had a user id and a password
>> >> (SQL
>> >> Server authentication), I got the message that the login failed for
>> >> ASPpage. Note, also, that I couln not create a connection with
>> >> that user
>> >> in Sql Server Manager Studio Express, but I could make a connection
>> >> with
>> >> Windows authentication.
>> >>
>> >> The failure for the one with the user id is that the user is not
>> >> associated with a trusted SQL server connection (even though he has
>> >> all
>> >> privileges).
>> >>
>> >> Any suggestions on how to proceed.
>> >>
>> >> Here is the code:
>> >>
>> >> <%
>> >> dim q, num, i, acct, coName
>> >> set Conn = Server.CreateObject("ADODB.Connection")
>> >> q = "SELECT * FROM Company"
>> >> Conn.Open "Provider=SQLOLEDB.1;Integrated Security-SSPI;Persist
>> >> Security
>> >> Info=False;Initial Catalog=the_database_name;Data
>> >> Source=LAPTOP\SQLEXPRESS"
>> >> set rs = Conn.Execute(q)
>> >> set of = Server.CreateObject("ADODB.Field")
>> >> num = rs.Count
>> >> for i=0 To num - 1
>> >> acct = of("accountNumber")
>> >> coName = of("companyName")
>> >> Response.Write(acct & " " & coName & "<br>")
>> >> next
>> >> conn.Close
>> >> %>
>> >>
>> >> When I put in to user info, I removed the Integrated Security
>> >> stuff.
>> >>
>> >> Thanks for any help
>> >>
>> >> Shelly (Sheldon)
>> >>
>> >>
>> >>
>> >
>>
>>
>>
>|||"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:58AF86FF-ED77-47B7-97F5-3D1D3B7B7CCE@.microsoft.com...
>> Conn.Open "Integrated Security=SSPI;Initial
>> Catalog=the_database_name;Data Source=LAPTOP\SQLEXRESS"
> Try "Server" instead of "Data Source" and "Database" instead of "Initial
> Catalog".
> --
> Ekrem Önsoy
Same result.
I am trying to make an OLE DB connection.to SQL Server Express on my local
machine using ASP and VBscript. I followed the instructions for making a
NewConnection.udl and double clicked that, filled in the spots and tested
the connection. Everything was OK. When I put it into my asp code, using
that string in the conn.Open statement, I got the error message that it
couldn't open the database that I had specified in the connection string.
(I copied and pasted that string from the .udl file where the test
connection succeeded). This was with Windows Authorization.
I also created a user with the name ASPpage and password asptest. I gave
that user all the privileges using Sql Server Manager Studio Express. When
I changed the string so that it had a user id and a password (SQL Server
authentication), I got the message that the login failed for ASPpage. Note,
also, that I couln not create a connection with that user in Sql Server
Manager Studio Express, but I could make a connection with Windows
authentication.
The failure for the one with the user id is that the user is not associated
with a trusted SQL server connection (even though he has all privileges).
Any suggestions on how to proceed.
Here is the code:
<%
dim q, num, i, acct, coName
set Conn = Server.CreateObject("ADODB.Connection")
q = "SELECT * FROM Company"
Conn.Open "Provider=SQLOLEDB.1;Integrated Security-SSPI;Persist Security
Info=False;Initial Catalog=the_database_name;Data Source=LAPTOP\SQLEXPRESS"
set rs = Conn.Execute(q)
set of = Server.CreateObject("ADODB.Field")
num = rs.Count
for i=0 To num - 1
acct = of("accountNumber")
coName = of("companyName")
Response.Write(acct & " " & coName & "<br>")
next
conn.Close
%>
When I put in to user info, I removed the Integrated Security stuff.
Thanks for any help
Shelly (Sheldon)You look forgot changing Authentication Method from Windows Authentication
to Mixed Authentication (SQL Server and Windows Authentication) from Server
Properties\Security.
--
Ekrem Önsoy
"Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
news:13gfs30mhrjej53@.corp.supernews.com...
>I am in a learning mode here as all my previous work has been with php.
> I am trying to make an OLE DB connection.to SQL Server Express on my local
> machine using ASP and VBscript. I followed the instructions for making a
> NewConnection.udl and double clicked that, filled in the spots and tested
> the connection. Everything was OK. When I put it into my asp code, using
> that string in the conn.Open statement, I got the error message that it
> couldn't open the database that I had specified in the connection string.
> (I copied and pasted that string from the .udl file where the test
> connection succeeded). This was with Windows Authorization.
> I also created a user with the name ASPpage and password asptest. I gave
> that user all the privileges using Sql Server Manager Studio Express.
> When I changed the string so that it had a user id and a password (SQL
> Server authentication), I got the message that the login failed for
> ASPpage. Note, also, that I couln not create a connection with that user
> in Sql Server Manager Studio Express, but I could make a connection with
> Windows authentication.
> The failure for the one with the user id is that the user is not
> associated with a trusted SQL server connection (even though he has all
> privileges).
> Any suggestions on how to proceed.
> Here is the code:
> <%
> dim q, num, i, acct, coName
> set Conn = Server.CreateObject("ADODB.Connection")
> q = "SELECT * FROM Company"
> Conn.Open "Provider=SQLOLEDB.1;Integrated Security-SSPI;Persist Security
> Info=False;Initial Catalog=the_database_name;Data
> Source=LAPTOP\SQLEXPRESS"
> set rs = Conn.Execute(q)
> set of = Server.CreateObject("ADODB.Field")
> num = rs.Count
> for i=0 To num - 1
> acct = of("accountNumber")
> coName = of("companyName")
> Response.Write(acct & " " & coName & "<br>")
> next
> conn.Close
> %>
> When I put in to user info, I removed the Integrated Security stuff.
> Thanks for any help
> Shelly (Sheldon)
>
>|||"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:B0697784-4125-4B7E-833F-AA905D02AB02@.microsoft.com...
> You look forgot changing Authentication Method from Windows Authentication
> to Mixed Authentication (SQL Server and Windows Authentication) from
> Server Properties\Security.
I'm sorry but I don't understand what you are saying. Where do you find
"Server Properties"? When I tried a new connection and switched from
Windows Authentication to SQL Server, it didn't work.
> --
> Ekrem Önsoy
>
> "Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
> news:13gfs30mhrjej53@.corp.supernews.com...
>>I am in a learning mode here as all my previous work has been with php.
>> I am trying to make an OLE DB connection.to SQL Server Express on my
>> local machine using ASP and VBscript. I followed the instructions for
>> making a NewConnection.udl and double clicked that, filled in the spots
>> and tested the connection. Everything was OK. When I put it into my asp
>> code, using that string in the conn.Open statement, I got the error
>> message that it couldn't open the database that I had specified in the
>> connection string. (I copied and pasted that string from the .udl file
>> where the test connection succeeded). This was with Windows
>> Authorization.
>> I also created a user with the name ASPpage and password asptest. I gave
>> that user all the privileges using Sql Server Manager Studio Express.
>> When I changed the string so that it had a user id and a password (SQL
>> Server authentication), I got the message that the login failed for
>> ASPpage. Note, also, that I couln not create a connection with that user
>> in Sql Server Manager Studio Express, but I could make a connection with
>> Windows authentication.
>> The failure for the one with the user id is that the user is not
>> associated with a trusted SQL server connection (even though he has all
>> privileges).
>> Any suggestions on how to proceed.
>> Here is the code:
>> <%
>> dim q, num, i, acct, coName
>> set Conn = Server.CreateObject("ADODB.Connection")
>> q = "SELECT * FROM Company"
>> Conn.Open "Provider=SQLOLEDB.1;Integrated Security-SSPI;Persist Security
>> Info=False;Initial Catalog=the_database_name;Data
>> Source=LAPTOP\SQLEXPRESS"
>> set rs = Conn.Execute(q)
>> set of = Server.CreateObject("ADODB.Field")
>> num = rs.Count
>> for i=0 To num - 1
>> acct = of("accountNumber")
>> coName = of("companyName")
>> Response.Write(acct & " " & coName & "<br>")
>> next
>> conn.Close
>> %>
>> When I put in to user info, I removed the Integrated Security stuff.
>> Thanks for any help
>> Shelly (Sheldon)
>>
>|||You get that setting you right click on the server name goto properties >
seucrity > seclect SQL Server and Windows Authentication as Ekrem said.
But looking at your connection string you have an error ... Maybe a mistype.
But here are two possible connection strings for SQL 2005:
Trusted Connection:
Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated
Security=SSPI;
SQL User Name Connection:
Server=myServerAddress;Database=myDataBase;User
ID=myUsername;Password=myPassword;Trusted_Connection=False;
Also note if you are trying to use integrated security in ASP, the IIS
username IUSR account must have access to SQL server not your account.
Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Shelly" wrote:
> "Ekrem Ã?nsoy" <ekrem@.btegitim.com> wrote in message
> news:B0697784-4125-4B7E-833F-AA905D02AB02@.microsoft.com...
> > You look forgot changing Authentication Method from Windows Authentication
> > to Mixed Authentication (SQL Server and Windows Authentication) from
> > Server Properties\Security.
> I'm sorry but I don't understand what you are saying. Where do you find
> "Server Properties"? When I tried a new connection and switched from
> Windows Authentication to SQL Server, it didn't work.
>
> > --
> > Ekrem Ã?nsoy
> >
> >
> >
> > "Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
> > news:13gfs30mhrjej53@.corp.supernews.com...
> >>I am in a learning mode here as all my previous work has been with php.
> >>
> >> I am trying to make an OLE DB connection.to SQL Server Express on my
> >> local machine using ASP and VBscript. I followed the instructions for
> >> making a NewConnection.udl and double clicked that, filled in the spots
> >> and tested the connection. Everything was OK. When I put it into my asp
> >> code, using that string in the conn.Open statement, I got the error
> >> message that it couldn't open the database that I had specified in the
> >> connection string. (I copied and pasted that string from the .udl file
> >> where the test connection succeeded). This was with Windows
> >> Authorization.
> >>
> >> I also created a user with the name ASPpage and password asptest. I gave
> >> that user all the privileges using Sql Server Manager Studio Express.
> >> When I changed the string so that it had a user id and a password (SQL
> >> Server authentication), I got the message that the login failed for
> >> ASPpage. Note, also, that I couln not create a connection with that user
> >> in Sql Server Manager Studio Express, but I could make a connection with
> >> Windows authentication.
> >>
> >> The failure for the one with the user id is that the user is not
> >> associated with a trusted SQL server connection (even though he has all
> >> privileges).
> >>
> >> Any suggestions on how to proceed.
> >>
> >> Here is the code:
> >>
> >> <%
> >> dim q, num, i, acct, coName
> >> set Conn = Server.CreateObject("ADODB.Connection")
> >> q = "SELECT * FROM Company"
> >> Conn.Open "Provider=SQLOLEDB.1;Integrated Security-SSPI;Persist Security
> >> Info=False;Initial Catalog=the_database_name;Data
> >> Source=LAPTOP\SQLEXPRESS"
> >> set rs = Conn.Execute(q)
> >> set of = Server.CreateObject("ADODB.Field")
> >> num = rs.Count
> >> for i=0 To num - 1
> >> acct = of("accountNumber")
> >> coName = of("companyName")
> >> Response.Write(acct & " " & coName & "<br>")
> >> next
> >> conn.Close
> >> %>
> >>
> >> When I put in to user info, I removed the Integrated Security stuff.
> >>
> >> Thanks for any help
> >>
> >> Shelly (Sheldon)
> >>
> >>
> >>
> >
>
>|||"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:DAC9B2CE-E61A-4AFE-854F-224A4E7566AC@.microsoft.com...
> You get that setting you right click on the server name goto properties >
> seucrity > seclect SQL Server and Windows Authentication as Ekrem said.
Thanks. When I do that in SQL Server Management Studio Express and hit the
"Test" button, I get that the testing failed because the user is not
associated with a trusted SQL Serverver connection. (Error 18452)
> But looking at your connection string you have an error ... Maybe a
> mistype.
> But here are two possible connection strings for SQL 2005:
> Trusted Connection:
> Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated
> Security=SSPI;
What about "Provider"? Also, should I leave out "Persist Security Info"?
> SQL User Name Connection:
> Server=myServerAddress;Database=myDataBase;User
> ID=myUsername;Password=myPassword;Trusted_Connection=False;
What about "Provider"? Also, I can't really test this one further until I
somehow get those credentials working in SQL Server Management Studio
Express.
> Also note if you are trying to use integrated security in ASP, the IIS
> username IUSR account must have access to SQL server not your account.
Could you expand upon this please? Where do I find/set the IIS username?
Thanks.
Shelly
> Thanks!
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "Shelly" wrote:
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:B0697784-4125-4B7E-833F-AA905D02AB02@.microsoft.com...
>> > You look forgot changing Authentication Method from Windows
>> > Authentication
>> > to Mixed Authentication (SQL Server and Windows Authentication) from
>> > Server Properties\Security.
>> I'm sorry but I don't understand what you are saying. Where do you find
>> "Server Properties"? When I tried a new connection and switched from
>> Windows Authentication to SQL Server, it didn't work.
>>
>> > --
>> > Ekrem Önsoy
>> >
>> >
>> >
>> > "Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
>> > news:13gfs30mhrjej53@.corp.supernews.com...
>> >>I am in a learning mode here as all my previous work has been with php.
>> >>
>> >> I am trying to make an OLE DB connection.to SQL Server Express on my
>> >> local machine using ASP and VBscript. I followed the instructions for
>> >> making a NewConnection.udl and double clicked that, filled in the
>> >> spots
>> >> and tested the connection. Everything was OK. When I put it into my
>> >> asp
>> >> code, using that string in the conn.Open statement, I got the error
>> >> message that it couldn't open the database that I had specified in the
>> >> connection string. (I copied and pasted that string from the .udl file
>> >> where the test connection succeeded). This was with Windows
>> >> Authorization.
>> >>
>> >> I also created a user with the name ASPpage and password asptest. I
>> >> gave
>> >> that user all the privileges using Sql Server Manager Studio Express.
>> >> When I changed the string so that it had a user id and a password (SQL
>> >> Server authentication), I got the message that the login failed for
>> >> ASPpage. Note, also, that I couln not create a connection with that
>> >> user
>> >> in Sql Server Manager Studio Express, but I could make a connection
>> >> with
>> >> Windows authentication.
>> >>
>> >> The failure for the one with the user id is that the user is not
>> >> associated with a trusted SQL server connection (even though he has
>> >> all
>> >> privileges).
>> >>
>> >> Any suggestions on how to proceed.
>> >>
>> >> Here is the code:
>> >>
>> >> <%
>> >> dim q, num, i, acct, coName
>> >> set Conn = Server.CreateObject("ADODB.Connection")
>> >> q = "SELECT * FROM Company"
>> >> Conn.Open "Provider=SQLOLEDB.1;Integrated Security-SSPI;Persist
>> >> Security
>> >> Info=False;Initial Catalog=the_database_name;Data
>> >> Source=LAPTOP\SQLEXPRESS"
>> >> set rs = Conn.Execute(q)
>> >> set of = Server.CreateObject("ADODB.Field")
>> >> num = rs.Count
>> >> for i=0 To num - 1
>> >> acct = of("accountNumber")
>> >> coName = of("companyName")
>> >> Response.Write(acct & " " & coName & "<br>")
>> >> next
>> >> conn.Close
>> >> %>
>> >>
>> >> When I put in to user info, I removed the Integrated Security stuff.
>> >>
>> >> Thanks for any help
>> >>
>> >> Shelly (Sheldon)
>> >>
>> >>
>> >>
>> >
>>|||"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:DAC9B2CE-E61A-4AFE-854F-224A4E7566AC@.microsoft.com...
> You get that setting you right click on the server name goto properties >
> seucrity > seclect SQL Server and Windows Authentication as Ekrem said.
> But looking at your connection string you have an error ... Maybe a
> mistype.
> But here are two possible connection strings for SQL 2005:
It was a typo.
> Trusted Connection:
> Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated
> Security=SSPI;
When I tried this string (without Provider, etc.) I got an error message of
"Multiple-step OLE DB operation generated errors. Check each OLE DB status
value, if available. No work was done". This was at the Conn.Open line.
Before I removed the Provider and Persist Security, it said that it failed
to open the_database_name as requested by the login.
Shelly
> SQL User Name Connection:
> Server=myServerAddress;Database=myDataBase;User
> ID=myUsername;Password=myPassword;Trusted_Connection=False;
> Also note if you are trying to use integrated security in ASP, the IIS
> username IUSR account must have access to SQL server not your account.
> Thanks!
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "Shelly" wrote:
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:B0697784-4125-4B7E-833F-AA905D02AB02@.microsoft.com...
>> > You look forgot changing Authentication Method from Windows
>> > Authentication
>> > to Mixed Authentication (SQL Server and Windows Authentication) from
>> > Server Properties\Security.
>> I'm sorry but I don't understand what you are saying. Where do you find
>> "Server Properties"? When I tried a new connection and switched from
>> Windows Authentication to SQL Server, it didn't work.
>>
>> > --
>> > Ekrem Önsoy
>> >
>> >
>> >
>> > "Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
>> > news:13gfs30mhrjej53@.corp.supernews.com...
>> >>I am in a learning mode here as all my previous work has been with php.
>> >>
>> >> I am trying to make an OLE DB connection.to SQL Server Express on my
>> >> local machine using ASP and VBscript. I followed the instructions for
>> >> making a NewConnection.udl and double clicked that, filled in the
>> >> spots
>> >> and tested the connection. Everything was OK. When I put it into my
>> >> asp
>> >> code, using that string in the conn.Open statement, I got the error
>> >> message that it couldn't open the database that I had specified in the
>> >> connection string. (I copied and pasted that string from the .udl file
>> >> where the test connection succeeded). This was with Windows
>> >> Authorization.
>> >>
>> >> I also created a user with the name ASPpage and password asptest. I
>> >> gave
>> >> that user all the privileges using Sql Server Manager Studio Express.
>> >> When I changed the string so that it had a user id and a password (SQL
>> >> Server authentication), I got the message that the login failed for
>> >> ASPpage. Note, also, that I couln not create a connection with that
>> >> user
>> >> in Sql Server Manager Studio Express, but I could make a connection
>> >> with
>> >> Windows authentication.
>> >>
>> >> The failure for the one with the user id is that the user is not
>> >> associated with a trusted SQL server connection (even though he has
>> >> all
>> >> privileges).
>> >>
>> >> Any suggestions on how to proceed.
>> >>
>> >> Here is the code:
>> >>
>> >> <%
>> >> dim q, num, i, acct, coName
>> >> set Conn = Server.CreateObject("ADODB.Connection")
>> >> q = "SELECT * FROM Company"
>> >> Conn.Open "Provider=SQLOLEDB.1;Integrated Security-SSPI;Persist
>> >> Security
>> >> Info=False;Initial Catalog=the_database_name;Data
>> >> Source=LAPTOP\SQLEXPRESS"
>> >> set rs = Conn.Execute(q)
>> >> set of = Server.CreateObject("ADODB.Field")
>> >> num = rs.Count
>> >> for i=0 To num - 1
>> >> acct = of("accountNumber")
>> >> coName = of("companyName")
>> >> Response.Write(acct & " " & coName & "<br>")
>> >> next
>> >> conn.Close
>> >> %>
>> >>
>> >> When I put in to user info, I removed the Integrated Security stuff.
>> >>
>> >> Thanks for any help
>> >>
>> >> Shelly (Sheldon)
>> >>
>> >>
>> >>
>> >
>>|||Shelly,
Please be sure that you have a LOGIN named ASPpage (or whatever) in main
Security node.
After that, be sure of that you have an appropriate USER which is associated
with that LOGIN and has necessary rights to perform its jobs in that
database.
Fire up SSMSE and go to Server Properties and go to Security and change the
Server authentication as "SQL Server and Windows Authentication mode" and
restart your SQL Server service and then try again.
Checklist:
In SQL Server Express, remote connections are disabled by default. So go to
Surface Area Configurations and be sure your instance is enabled for TCP\IP
or whatever protocol you use for connection.
Go to SQL Server Configuration Manager and be sure your Network
Configuration is OK. (TCP\IP or whatever is must be enabled, IP addresses
must be entered etc.)
--
Ekrem Önsoy
"Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
news:13ghjl7nvigltfb@.corp.supernews.com...
> "Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
> news:DAC9B2CE-E61A-4AFE-854F-224A4E7566AC@.microsoft.com...
>> You get that setting you right click on the server name goto properties >
>> seucrity > seclect SQL Server and Windows Authentication as Ekrem said.
>> But looking at your connection string you have an error ... Maybe a
>> mistype.
>> But here are two possible connection strings for SQL 2005:
> It was a typo.
>> Trusted Connection:
>> Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated
>> Security=SSPI;
> When I tried this string (without Provider, etc.) I got an error message
> of "Multiple-step OLE DB operation generated errors. Check each OLE DB
> status value, if available. No work was done". This was at the Conn.Open
> line. Before I removed the Provider and Persist Security, it said that it
> failed to open the_database_name as requested by the login.
> Shelly
>> SQL User Name Connection:
>> Server=myServerAddress;Database=myDataBase;User
>> ID=myUsername;Password=myPassword;Trusted_Connection=False;
>> Also note if you are trying to use integrated security in ASP, the IIS
>> username IUSR account must have access to SQL server not your account.
>> Thanks!
>> --
>> Mohit K. Gupta
>> B.Sc. CS, Minor Japanese
>> MCTS: SQL Server 2005
>>
>> "Shelly" wrote:
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:B0697784-4125-4B7E-833F-AA905D02AB02@.microsoft.com...
>> > You look forgot changing Authentication Method from Windows
>> > Authentication
>> > to Mixed Authentication (SQL Server and Windows Authentication) from
>> > Server Properties\Security.
>> I'm sorry but I don't understand what you are saying. Where do you find
>> "Server Properties"? When I tried a new connection and switched from
>> Windows Authentication to SQL Server, it didn't work.
>>
>> > --
>> > Ekrem Önsoy
>> >
>> >
>> >
>> > "Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
>> > news:13gfs30mhrjej53@.corp.supernews.com...
>> >>I am in a learning mode here as all my previous work has been with
>> >>php.
>> >>
>> >> I am trying to make an OLE DB connection.to SQL Server Express on my
>> >> local machine using ASP and VBscript. I followed the instructions
>> >> for
>> >> making a NewConnection.udl and double clicked that, filled in the
>> >> spots
>> >> and tested the connection. Everything was OK. When I put it into my
>> >> asp
>> >> code, using that string in the conn.Open statement, I got the error
>> >> message that it couldn't open the database that I had specified in
>> >> the
>> >> connection string. (I copied and pasted that string from the .udl
>> >> file
>> >> where the test connection succeeded). This was with Windows
>> >> Authorization.
>> >>
>> >> I also created a user with the name ASPpage and password asptest. I
>> >> gave
>> >> that user all the privileges using Sql Server Manager Studio Express.
>> >> When I changed the string so that it had a user id and a password
>> >> (SQL
>> >> Server authentication), I got the message that the login failed for
>> >> ASPpage. Note, also, that I couln not create a connection with that
>> >> user
>> >> in Sql Server Manager Studio Express, but I could make a connection
>> >> with
>> >> Windows authentication.
>> >>
>> >> The failure for the one with the user id is that the user is not
>> >> associated with a trusted SQL server connection (even though he has
>> >> all
>> >> privileges).
>> >>
>> >> Any suggestions on how to proceed.
>> >>
>> >> Here is the code:
>> >>
>> >> <%
>> >> dim q, num, i, acct, coName
>> >> set Conn = Server.CreateObject("ADODB.Connection")
>> >> q = "SELECT * FROM Company"
>> >> Conn.Open "Provider=SQLOLEDB.1;Integrated Security-SSPI;Persist
>> >> Security
>> >> Info=False;Initial Catalog=the_database_name;Data
>> >> Source=LAPTOP\SQLEXPRESS"
>> >> set rs = Conn.Execute(q)
>> >> set of = Server.CreateObject("ADODB.Field")
>> >> num = rs.Count
>> >> for i=0 To num - 1
>> >> acct = of("accountNumber")
>> >> coName = of("companyName")
>> >> Response.Write(acct & " " & coName & "<br>")
>> >> next
>> >> conn.Close
>> >> %>
>> >>
>> >> When I put in to user info, I removed the Integrated Security stuff.
>> >>
>> >> Thanks for any help
>> >>
>> >> Shelly (Sheldon)
>> >>
>> >>
>> >>
>> >
>>
>|||"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:B7333908-981B-436F-8CB3-4676EC53B93F@.microsoft.com...
> Shelly,
> Please be sure that you have a LOGIN named ASPpage (or whatever) in main
> Security node.
I have one under the Security tab which is under the LAPTOP|SQLEXPRESS in
the object explorer in SSMSE.
> After that, be sure of that you have an appropriate USER which is
> associated with that LOGIN and has necessary rights to perform its jobs in
> that database.
I have a user under Databases\the_database_name\Security\Users in SSMSE. It
has the same rights as the Administrator, including Connect SQL.
> Fire up SSMSE and go to Server Properties and go to Security and change
> the Server authentication as "SQL Server and Windows Authentication mode"
> and restart your SQL Server service and then try again.
Already there at Windows Authentication Mode -- but I restarted anyway.
> Checklist:
> In SQL Server Express, remote connections are disabled by default. So go
> to Surface Area Configurations and be sure your instance is enabled for
> TCP\IP or whatever protocol you use for connection.
I don't know what you mean with "Surface Area Configurations" or where to
find it. I went to the configuration manager and saw to it that shared
memory, named pipes and tcpip were all enabled. I restarted the sql server.
> Go to SQL Server Configuration Manager and be sure your Network
> Configuration is OK. (TCP\IP or whatever is must be enabled, IP addresses
> must be entered etc.)
Done. (Note that everything is taking place on my local laptop, called
LAPTOP, no external connections)
I still get the "Multiple step OLE DB operation generated errors. Check
each OLE DB status value, if available. No work was done".
I don't know where or how to check these values.
My connection line is:
Conn.Open "Integrated Security=SSPI;Initial Catalog=the_database_name;Data
Source=LAPTOP\SQLEXRESS"
Shelly
> "Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
> news:13ghjl7nvigltfb@.corp.supernews.com...
>> "Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
>> news:DAC9B2CE-E61A-4AFE-854F-224A4E7566AC@.microsoft.com...
>> You get that setting you right click on the server name goto properties
>> >
>> seucrity > seclect SQL Server and Windows Authentication as Ekrem said.
>> But looking at your connection string you have an error ... Maybe a
>> mistype.
>> But here are two possible connection strings for SQL 2005:
>> It was a typo.
>>
>> Trusted Connection:
>> Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated
>> Security=SSPI;
>> When I tried this string (without Provider, etc.) I got an error message
>> of "Multiple-step OLE DB operation generated errors. Check each OLE DB
>> status value, if available. No work was done". This was at the
>> Conn.Open line. Before I removed the Provider and Persist Security, it
>> said that it failed to open the_database_name as requested by the login.
>> Shelly
>> SQL User Name Connection:
>> Server=myServerAddress;Database=myDataBase;User
>> ID=myUsername;Password=myPassword;Trusted_Connection=False;
>> Also note if you are trying to use integrated security in ASP, the IIS
>> username IUSR account must have access to SQL server not your account.
>> Thanks!
>> --
>> Mohit K. Gupta
>> B.Sc. CS, Minor Japanese
>> MCTS: SQL Server 2005
>>
>> "Shelly" wrote:
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:B0697784-4125-4B7E-833F-AA905D02AB02@.microsoft.com...
>> > You look forgot changing Authentication Method from Windows
>> > Authentication
>> > to Mixed Authentication (SQL Server and Windows Authentication) from
>> > Server Properties\Security.
>> I'm sorry but I don't understand what you are saying. Where do you
>> find
>> "Server Properties"? When I tried a new connection and switched from
>> Windows Authentication to SQL Server, it didn't work.
>>
>> > --
>> > Ekrem Önsoy
>> >
>> >
>> >
>> > "Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
>> > news:13gfs30mhrjej53@.corp.supernews.com...
>> >>I am in a learning mode here as all my previous work has been with
>> >>php.
>> >>
>> >> I am trying to make an OLE DB connection.to SQL Server Express on my
>> >> local machine using ASP and VBscript. I followed the instructions
>> >> for
>> >> making a NewConnection.udl and double clicked that, filled in the
>> >> spots
>> >> and tested the connection. Everything was OK. When I put it into
>> >> my asp
>> >> code, using that string in the conn.Open statement, I got the error
>> >> message that it couldn't open the database that I had specified in
>> >> the
>> >> connection string. (I copied and pasted that string from the .udl
>> >> file
>> >> where the test connection succeeded). This was with Windows
>> >> Authorization.
>> >>
>> >> I also created a user with the name ASPpage and password asptest. I
>> >> gave
>> >> that user all the privileges using Sql Server Manager Studio
>> >> Express.
>> >> When I changed the string so that it had a user id and a password
>> >> (SQL
>> >> Server authentication), I got the message that the login failed for
>> >> ASPpage. Note, also, that I couln not create a connection with that
>> >> user
>> >> in Sql Server Manager Studio Express, but I could make a connection
>> >> with
>> >> Windows authentication.
>> >>
>> >> The failure for the one with the user id is that the user is not
>> >> associated with a trusted SQL server connection (even though he has
>> >> all
>> >> privileges).
>> >>
>> >> Any suggestions on how to proceed.
>> >>
>> >> Here is the code:
>> >>
>> >> <%
>> >> dim q, num, i, acct, coName
>> >> set Conn = Server.CreateObject("ADODB.Connection")
>> >> q = "SELECT * FROM Company"
>> >> Conn.Open "Provider=SQLOLEDB.1;Integrated Security-SSPI;Persist
>> >> Security
>> >> Info=False;Initial Catalog=the_database_name;Data
>> >> Source=LAPTOP\SQLEXPRESS"
>> >> set rs = Conn.Execute(q)
>> >> set of = Server.CreateObject("ADODB.Field")
>> >> num = rs.Count
>> >> for i=0 To num - 1
>> >> acct = of("accountNumber")
>> >> coName = of("companyName")
>> >> Response.Write(acct & " " & coName & "<br>")
>> >> next
>> >> conn.Close
>> >> %>
>> >>
>> >> When I put in to user info, I removed the Integrated Security stuff.
>> >>
>> >> Thanks for any help
>> >>
>> >> Shelly (Sheldon)
>> >>
>> >>
>> >>
>> >
>>
>>
>|||> Conn.Open "Integrated Security=SSPI;Initial Catalog=the_database_name;Data
> Source=LAPTOP\SQLEXRESS"
Try "Server" instead of "Data Source" and "Database" instead of "Initial
Catalog".
--
Ekrem Önsoy
"Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
news:13gmnt5drgdse27@.corp.supernews.com...
> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
> news:B7333908-981B-436F-8CB3-4676EC53B93F@.microsoft.com...
>> Shelly,
>> Please be sure that you have a LOGIN named ASPpage (or whatever) in main
>> Security node.
> I have one under the Security tab which is under the LAPTOP|SQLEXPRESS in
> the object explorer in SSMSE.
>> After that, be sure of that you have an appropriate USER which is
>> associated with that LOGIN and has necessary rights to perform its jobs
>> in that database.
> I have a user under Databases\the_database_name\Security\Users in SSMSE.
> It has the same rights as the Administrator, including Connect SQL.
>> Fire up SSMSE and go to Server Properties and go to Security and change
>> the Server authentication as "SQL Server and Windows Authentication mode"
>> and restart your SQL Server service and then try again.
> Already there at Windows Authentication Mode -- but I restarted anyway.
>> Checklist:
>> In SQL Server Express, remote connections are disabled by default. So go
>> to Surface Area Configurations and be sure your instance is enabled for
>> TCP\IP or whatever protocol you use for connection.
> I don't know what you mean with "Surface Area Configurations" or where to
> find it. I went to the configuration manager and saw to it that shared
> memory, named pipes and tcpip were all enabled. I restarted the sql
> server.
>> Go to SQL Server Configuration Manager and be sure your Network
>> Configuration is OK. (TCP\IP or whatever is must be enabled, IP addresses
>> must be entered etc.)
> Done. (Note that everything is taking place on my local laptop, called
> LAPTOP, no external connections)
> I still get the "Multiple step OLE DB operation generated errors. Check
> each OLE DB status value, if available. No work was done".
> I don't know where or how to check these values.
> My connection line is:
> Conn.Open "Integrated Security=SSPI;Initial Catalog=the_database_name;Data
> Source=LAPTOP\SQLEXRESS"
> Shelly
>> "Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
>> news:13ghjl7nvigltfb@.corp.supernews.com...
>> "Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
>> news:DAC9B2CE-E61A-4AFE-854F-224A4E7566AC@.microsoft.com...
>> You get that setting you right click on the server name goto properties
>> >
>> seucrity > seclect SQL Server and Windows Authentication as Ekrem said.
>> But looking at your connection string you have an error ... Maybe a
>> mistype.
>> But here are two possible connection strings for SQL 2005:
>> It was a typo.
>>
>> Trusted Connection:
>> Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated
>> Security=SSPI;
>> When I tried this string (without Provider, etc.) I got an error message
>> of "Multiple-step OLE DB operation generated errors. Check each OLE DB
>> status value, if available. No work was done". This was at the
>> Conn.Open line. Before I removed the Provider and Persist Security, it
>> said that it failed to open the_database_name as requested by the login.
>> Shelly
>> SQL User Name Connection:
>> Server=myServerAddress;Database=myDataBase;User
>> ID=myUsername;Password=myPassword;Trusted_Connection=False;
>> Also note if you are trying to use integrated security in ASP, the IIS
>> username IUSR account must have access to SQL server not your account.
>> Thanks!
>> --
>> Mohit K. Gupta
>> B.Sc. CS, Minor Japanese
>> MCTS: SQL Server 2005
>>
>> "Shelly" wrote:
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:B0697784-4125-4B7E-833F-AA905D02AB02@.microsoft.com...
>> > You look forgot changing Authentication Method from Windows
>> > Authentication
>> > to Mixed Authentication (SQL Server and Windows Authentication) from
>> > Server Properties\Security.
>> I'm sorry but I don't understand what you are saying. Where do you
>> find
>> "Server Properties"? When I tried a new connection and switched from
>> Windows Authentication to SQL Server, it didn't work.
>>
>> > --
>> > Ekrem Önsoy
>> >
>> >
>> >
>> > "Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
>> > news:13gfs30mhrjej53@.corp.supernews.com...
>> >>I am in a learning mode here as all my previous work has been with
>> >>php.
>> >>
>> >> I am trying to make an OLE DB connection.to SQL Server Express on
>> >> my
>> >> local machine using ASP and VBscript. I followed the instructions
>> >> for
>> >> making a NewConnection.udl and double clicked that, filled in the
>> >> spots
>> >> and tested the connection. Everything was OK. When I put it into
>> >> my asp
>> >> code, using that string in the conn.Open statement, I got the error
>> >> message that it couldn't open the database that I had specified in
>> >> the
>> >> connection string. (I copied and pasted that string from the .udl
>> >> file
>> >> where the test connection succeeded). This was with Windows
>> >> Authorization.
>> >>
>> >> I also created a user with the name ASPpage and password asptest.
>> >> I gave
>> >> that user all the privileges using Sql Server Manager Studio
>> >> Express.
>> >> When I changed the string so that it had a user id and a password
>> >> (SQL
>> >> Server authentication), I got the message that the login failed for
>> >> ASPpage. Note, also, that I couln not create a connection with
>> >> that user
>> >> in Sql Server Manager Studio Express, but I could make a connection
>> >> with
>> >> Windows authentication.
>> >>
>> >> The failure for the one with the user id is that the user is not
>> >> associated with a trusted SQL server connection (even though he has
>> >> all
>> >> privileges).
>> >>
>> >> Any suggestions on how to proceed.
>> >>
>> >> Here is the code:
>> >>
>> >> <%
>> >> dim q, num, i, acct, coName
>> >> set Conn = Server.CreateObject("ADODB.Connection")
>> >> q = "SELECT * FROM Company"
>> >> Conn.Open "Provider=SQLOLEDB.1;Integrated Security-SSPI;Persist
>> >> Security
>> >> Info=False;Initial Catalog=the_database_name;Data
>> >> Source=LAPTOP\SQLEXPRESS"
>> >> set rs = Conn.Execute(q)
>> >> set of = Server.CreateObject("ADODB.Field")
>> >> num = rs.Count
>> >> for i=0 To num - 1
>> >> acct = of("accountNumber")
>> >> coName = of("companyName")
>> >> Response.Write(acct & " " & coName & "<br>")
>> >> next
>> >> conn.Close
>> >> %>
>> >>
>> >> When I put in to user info, I removed the Integrated Security
>> >> stuff.
>> >>
>> >> Thanks for any help
>> >>
>> >> Shelly (Sheldon)
>> >>
>> >>
>> >>
>> >
>>
>>
>>
>|||"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:58AF86FF-ED77-47B7-97F5-3D1D3B7B7CCE@.microsoft.com...
>> Conn.Open "Integrated Security=SSPI;Initial
>> Catalog=the_database_name;Data Source=LAPTOP\SQLEXRESS"
> Try "Server" instead of "Data Source" and "Database" instead of "Initial
> Catalog".
> --
> Ekrem Önsoy
Same result.
Monday, March 12, 2012
New Member | Need Tutorial
Hi All,
I am a new member of this group. Can any one guide me to get more basic and
intermediate level of learning MS-SQL-Server. I need tutorials, downloads
thank you
GAKHere's a list of various resources:
http://vyaskn.tripod.com/sqlserverres.htm
But don't forget to read Books Online, that gets installed with SQL Server.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
Hi All,
I am a new member of this group. Can any one guide me to get more basic and
intermediate level of learning MS-SQL-Server. I need tutorials, downloads
thank you
GAK|||Here is a link to Books Online
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
and a good beginner book was SQL Server for Dummies. And that is not a
joke. It was all around a good learning tool for begining.
--
Jeff Duncan
MCDBA, MCSE+I
"GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I am a new member of this group. Can any one guide me to get more basic
and
> intermediate level of learning MS-SQL-Server. I need tutorials, downloads
> thank you
> GAK
>|||Start with Books On-Line (shortened to BOL by many posters). I also
recommend Inside SQL Server 2000 by Kalen Delaney. This is a real book that
you will need to purchase, but it should take you a long way towards your
goals. After you work with these for a while, you will likely need in-depth
information on specific areas of SQL server. We (the community) will be
happy to share our opinions on other books and learning resources.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I am a new member of this group. Can any one guide me to get more basic
and
> intermediate level of learning MS-SQL-Server. I need tutorials, downloads
> thank you
> GAK
>|||GAK, there are so many different websites offering information on SQL Server
you will be amazed.
First you need a starting point of learning and there are two distinct paths
(although inter-related)
? Administering Microsoft SQL Server 2000
? Programming Microsoft SQL Server 2000
If you are not a developer then you will almost certainly be looking to
learn the first route. And even if you are then the first route is probably
still a good idea.
I could give you loads of suggestions for books, but quite frankly the best
idea for you would be to pop into a book shop and look through several until
you find one that is going to be the right fit for you.
--
--
Br,
Mark Broadbent
mcdba , mcse+i
============="GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I am a new member of this group. Can any one guide me to get more basic
and
> intermediate level of learning MS-SQL-Server. I need tutorials, downloads
> thank you
> GAK
>|||If your new to SQL Server as an Administrator then I would
recommend the book SQL Server 2000 Step By Step. Its a
really good book for the beginner, I use it in mentoring
new dba's.
J
>--Original Message--
>Hi All,
> I am a new member of this group. Can any one guide me to
get more basic and
>intermediate level of learning MS-SQL-Server. I need
tutorials, downloads
>thank you
>GAK
>
>.
>
I am a new member of this group. Can any one guide me to get more basic and
intermediate level of learning MS-SQL-Server. I need tutorials, downloads
thank you
GAKHere's a list of various resources:
http://vyaskn.tripod.com/sqlserverres.htm
But don't forget to read Books Online, that gets installed with SQL Server.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
Hi All,
I am a new member of this group. Can any one guide me to get more basic and
intermediate level of learning MS-SQL-Server. I need tutorials, downloads
thank you
GAK|||Here is a link to Books Online
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
and a good beginner book was SQL Server for Dummies. And that is not a
joke. It was all around a good learning tool for begining.
--
Jeff Duncan
MCDBA, MCSE+I
"GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I am a new member of this group. Can any one guide me to get more basic
and
> intermediate level of learning MS-SQL-Server. I need tutorials, downloads
> thank you
> GAK
>|||Start with Books On-Line (shortened to BOL by many posters). I also
recommend Inside SQL Server 2000 by Kalen Delaney. This is a real book that
you will need to purchase, but it should take you a long way towards your
goals. After you work with these for a while, you will likely need in-depth
information on specific areas of SQL server. We (the community) will be
happy to share our opinions on other books and learning resources.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I am a new member of this group. Can any one guide me to get more basic
and
> intermediate level of learning MS-SQL-Server. I need tutorials, downloads
> thank you
> GAK
>|||GAK, there are so many different websites offering information on SQL Server
you will be amazed.
First you need a starting point of learning and there are two distinct paths
(although inter-related)
? Administering Microsoft SQL Server 2000
? Programming Microsoft SQL Server 2000
If you are not a developer then you will almost certainly be looking to
learn the first route. And even if you are then the first route is probably
still a good idea.
I could give you loads of suggestions for books, but quite frankly the best
idea for you would be to pop into a book shop and look through several until
you find one that is going to be the right fit for you.
--
--
Br,
Mark Broadbent
mcdba , mcse+i
============="GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I am a new member of this group. Can any one guide me to get more basic
and
> intermediate level of learning MS-SQL-Server. I need tutorials, downloads
> thank you
> GAK
>|||If your new to SQL Server as an Administrator then I would
recommend the book SQL Server 2000 Step By Step. Its a
really good book for the beginner, I use it in mentoring
new dba's.
J
>--Original Message--
>Hi All,
> I am a new member of this group. Can any one guide me to
get more basic and
>intermediate level of learning MS-SQL-Server. I need
tutorials, downloads
>thank you
>GAK
>
>.
>
New Member | Need Tutorial
Hi All,
I am a new member of this group. Can any one guide me to get more basic and
intermediate level of learning MS-SQL-Server. I need tutorials, downloads
thank you
GAK
Here's a list of various resources:
http://vyaskn.tripod.com/sqlserverres.htm
But don't forget to read Books Online, that gets installed with SQL Server.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
Hi All,
I am a new member of this group. Can any one guide me to get more basic and
intermediate level of learning MS-SQL-Server. I need tutorials, downloads
thank you
GAK
|||Here is a link to Books Online
http://www.microsoft.com/sql/techinf...2000/books.asp
and a good beginner book was SQL Server for Dummies. And that is not a
joke. It was all around a good learning tool for begining.
Jeff Duncan
MCDBA, MCSE+I
"GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I am a new member of this group. Can any one guide me to get more basic
and
> intermediate level of learning MS-SQL-Server. I need tutorials, downloads
> thank you
> GAK
>
|||Start with Books On-Line (shortened to BOL by many posters). I also
recommend Inside SQL Server 2000 by Kalen Delaney. This is a real book that
you will need to purchase, but it should take you a long way towards your
goals. After you work with these for a while, you will likely need in-depth
information on specific areas of SQL server. We (the community) will be
happy to share our opinions on other books and learning resources.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I am a new member of this group. Can any one guide me to get more basic
and
> intermediate level of learning MS-SQL-Server. I need tutorials, downloads
> thank you
> GAK
>
|||GAK, there are so many different websites offering information on SQL Server
you will be amazed.
First you need a starting point of learning and there are two distinct paths
(although inter-related)
Administering Microsoft SQL Server 2000
Programming Microsoft SQL Server 2000
If you are not a developer then you will almost certainly be looking to
learn the first route. And even if you are then the first route is probably
still a good idea.
I could give you loads of suggestions for books, but quite frankly the best
idea for you would be to pop into a book shop and look through several until
you find one that is going to be the right fit for you.
--
Br,
Mark Broadbent
mcdba , mcse+i
=============
"GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I am a new member of this group. Can any one guide me to get more basic
and
> intermediate level of learning MS-SQL-Server. I need tutorials, downloads
> thank you
> GAK
>
|||w
"GAK" wrote:
> Hi All,
> I am a new member of this group. Can any one guide me to get more basic and
> intermediate level of learning MS-SQL-Server. I need tutorials, downloads
> thank you
> GAK
>
>
I am a new member of this group. Can any one guide me to get more basic and
intermediate level of learning MS-SQL-Server. I need tutorials, downloads
thank you
GAK
Here's a list of various resources:
http://vyaskn.tripod.com/sqlserverres.htm
But don't forget to read Books Online, that gets installed with SQL Server.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
Hi All,
I am a new member of this group. Can any one guide me to get more basic and
intermediate level of learning MS-SQL-Server. I need tutorials, downloads
thank you
GAK
|||Here is a link to Books Online
http://www.microsoft.com/sql/techinf...2000/books.asp
and a good beginner book was SQL Server for Dummies. And that is not a
joke. It was all around a good learning tool for begining.
Jeff Duncan
MCDBA, MCSE+I
"GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I am a new member of this group. Can any one guide me to get more basic
and
> intermediate level of learning MS-SQL-Server. I need tutorials, downloads
> thank you
> GAK
>
|||Start with Books On-Line (shortened to BOL by many posters). I also
recommend Inside SQL Server 2000 by Kalen Delaney. This is a real book that
you will need to purchase, but it should take you a long way towards your
goals. After you work with these for a while, you will likely need in-depth
information on specific areas of SQL server. We (the community) will be
happy to share our opinions on other books and learning resources.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I am a new member of this group. Can any one guide me to get more basic
and
> intermediate level of learning MS-SQL-Server. I need tutorials, downloads
> thank you
> GAK
>
|||GAK, there are so many different websites offering information on SQL Server
you will be amazed.
First you need a starting point of learning and there are two distinct paths
(although inter-related)
Administering Microsoft SQL Server 2000
Programming Microsoft SQL Server 2000
If you are not a developer then you will almost certainly be looking to
learn the first route. And even if you are then the first route is probably
still a good idea.
I could give you loads of suggestions for books, but quite frankly the best
idea for you would be to pop into a book shop and look through several until
you find one that is going to be the right fit for you.
--
Br,
Mark Broadbent
mcdba , mcse+i
=============
"GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I am a new member of this group. Can any one guide me to get more basic
and
> intermediate level of learning MS-SQL-Server. I need tutorials, downloads
> thank you
> GAK
>
|||w
"GAK" wrote:
> Hi All,
> I am a new member of this group. Can any one guide me to get more basic and
> intermediate level of learning MS-SQL-Server. I need tutorials, downloads
> thank you
> GAK
>
>
New Member | Need Tutorial
Hi All,
I am a new member of this group. Can any one guide me to get more basic and
intermediate level of learning MS-SQL-Server. I need tutorials, downloads
thank you
GAKHere's a list of various resources:
http://vyaskn.tripod.com/sqlserverres.htm
But don't forget to read Books Online, that gets installed with SQL Server.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
Hi All,
I am a new member of this group. Can any one guide me to get more basic and
intermediate level of learning MS-SQL-Server. I need tutorials, downloads
thank you
GAK|||Here is a link to Books Online
http://www.microsoft.com/sql/techin.../2000/books.asp
and a good beginner book was SQL Server for Dummies. And that is not a
joke. It was all around a good learning tool for begining.
Jeff Duncan
MCDBA, MCSE+I
"GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I am a new member of this group. Can any one guide me to get more basic
and
> intermediate level of learning MS-SQL-Server. I need tutorials, downloads
> thank you
> GAK
>|||Start with Books On-Line (shortened to BOL by many posters). I also
recommend Inside SQL Server 2000 by Kalen Delaney. This is a real book that
you will need to purchase, but it should take you a long way towards your
goals. After you work with these for a while, you will likely need in-depth
information on specific areas of SQL server. We (the community) will be
happy to share our opinions on other books and learning resources.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I am a new member of this group. Can any one guide me to get more basic
and
> intermediate level of learning MS-SQL-Server. I need tutorials, downloads
> thank you
> GAK
>|||GAK, there are so many different websites offering information on SQL Server
you will be amazed.
First you need a starting point of learning and there are two distinct paths
(although inter-related)
Administering Microsoft SQL Server 2000
programming Microsoft SQL Server 2000
If you are not a developer then you will almost certainly be looking to
learn the first route. And even if you are then the first route is probably
still a good idea.
I could give you loads of suggestions for books, but quite frankly the best
idea for you would be to pop into a book shop and look through several until
you find one that is going to be the right fit for you.
--
Br,
Mark Broadbent
mcdba , mcse+i
=============
"GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I am a new member of this group. Can any one guide me to get more basic
and
> intermediate level of learning MS-SQL-Server. I need tutorials, downloads
> thank you
> GAK
>|||w
"GAK" wrote:
> Hi All,
> I am a new member of this group. Can any one guide me to get more basic a
nd
> intermediate level of learning MS-SQL-Server. I need tutorials, downloads
> thank you
> GAK
>
>
I am a new member of this group. Can any one guide me to get more basic and
intermediate level of learning MS-SQL-Server. I need tutorials, downloads
thank you
GAKHere's a list of various resources:
http://vyaskn.tripod.com/sqlserverres.htm
But don't forget to read Books Online, that gets installed with SQL Server.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
Hi All,
I am a new member of this group. Can any one guide me to get more basic and
intermediate level of learning MS-SQL-Server. I need tutorials, downloads
thank you
GAK|||Here is a link to Books Online
http://www.microsoft.com/sql/techin.../2000/books.asp
and a good beginner book was SQL Server for Dummies. And that is not a
joke. It was all around a good learning tool for begining.
Jeff Duncan
MCDBA, MCSE+I
"GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I am a new member of this group. Can any one guide me to get more basic
and
> intermediate level of learning MS-SQL-Server. I need tutorials, downloads
> thank you
> GAK
>|||Start with Books On-Line (shortened to BOL by many posters). I also
recommend Inside SQL Server 2000 by Kalen Delaney. This is a real book that
you will need to purchase, but it should take you a long way towards your
goals. After you work with these for a while, you will likely need in-depth
information on specific areas of SQL server. We (the community) will be
happy to share our opinions on other books and learning resources.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I am a new member of this group. Can any one guide me to get more basic
and
> intermediate level of learning MS-SQL-Server. I need tutorials, downloads
> thank you
> GAK
>|||GAK, there are so many different websites offering information on SQL Server
you will be amazed.
First you need a starting point of learning and there are two distinct paths
(although inter-related)
Administering Microsoft SQL Server 2000
programming Microsoft SQL Server 2000
If you are not a developer then you will almost certainly be looking to
learn the first route. And even if you are then the first route is probably
still a good idea.
I could give you loads of suggestions for books, but quite frankly the best
idea for you would be to pop into a book shop and look through several until
you find one that is going to be the right fit for you.
--
Br,
Mark Broadbent
mcdba , mcse+i
=============
"GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I am a new member of this group. Can any one guide me to get more basic
and
> intermediate level of learning MS-SQL-Server. I need tutorials, downloads
> thank you
> GAK
>|||w
"GAK" wrote:
> Hi All,
> I am a new member of this group. Can any one guide me to get more basic a
nd
> intermediate level of learning MS-SQL-Server. I need tutorials, downloads
> thank you
> GAK
>
>
Wednesday, March 7, 2012
new in .Net but learning SqlConnection, SqlCommand and SqlDataReader How in code_behind
Hi.
I want to now a little about CodeBehind how to use it with SqlConnection, SqlCommand and SqlDataReader.
How i use:
Web.Config
<connectionStrings>
<add name="ConnectionStringTest" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\DSNbase.mdb;Persist Security Info=True"providerName="System.Data.OleDb"/>
</connectionStrings>
Default.aspx
<asp:RepeaterID="test"runat="server"DataSourceID="SqlDataSourcetest">
...
..
<asp:SqlDataSourceID="SqlDataSourcetest"runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionStringTest %>"
ProviderName="<%$ ConnectionStrings:ConnectionStringTest.ProviderName %>"
SelectCommand="SELECT [ID], [Name], [Sex], [Born], [Color], [Dog], [Images] FROM [Dogs] WHERE ([Dog] = 'YES') ORDER BY [ID]">
</asp:SqlDataSource>
How can i take my asp:SqlDataSource and put it in my CodeBehind, so it use the ConnectionString from my Web.Config and so i can use my Repeater on my default.aspx site.. !?
Maybe if u got a great link that show "How to do" maybe om MSDN/MSDN2.
You can use a datareader. Heres an example i put together using a stored procedure:
#region StoredProcedure GetDogs
// CREATE PROCEDURE GetDogs()
// AS
// SELECT ID, Name, Sex, Born, Color, Dog, Images FROM Dogs WHERE Dog='YES' ORDER BY ID
// RETURN
#endregion
//This will get the connectionstring from the web.config
SqlConnection objConn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionStringText"].ConnectionString);
SqlCommand objCmd = new SqlCommand("GetDogs", objConn);
objCmd.CommandType = CommandType.StoredProcedure;
objConn.Open();
SqlDataReader objRdr = objCmd.ExecuteReader(CommandBehavior.CloseConnection);
if (objRdr.HasRows)
{
testRepeater.DataSource = objRdr;
testRepeater.DataBind();
}
else { // do alternative if no data is present. }
objRdr.Close();
objConn.Close();
Hi.
How will this look like if im using VB.
|||This should work, but im not 100% sure, Im doing this by hand and not in VS, but its striaght forward and you should be able to see how to change it:
//This will get the connectionstring from the web.config
Dim objConn As SqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionStringText"].ConnectionString)
Dim objCmd As SqlCommand = new SqlCommand("GetDogs", objConn)
objCmd.CommandType = CommandType.StoredProcedure
objConn.Open();
Dim objRdr As SqlDataReader = objCmd.ExecuteReader(CommandBehavior.CloseConnection)
testRepeater.DataSource = objRdr
testRepeater.DataBind()
objRdr.Close()
objConn.Close()
Hope this helps.
Subscribe to:
Posts (Atom)