Newbie Question

I have a table called cr. Within that table I have the colums c_id, c_type, first_name, last_name. I need help creating a query that will pull from an excel file that have the same colum names. This excel file is automaticaly generated from one of our sys
tems and the c_id will never change for the record.
So this is what im wanting to do. I need it to look at the xls file and if there are new records in that xls file it will add those to the table or if the records have changed it will update the records within the table based on the c_id. I think this is
feasable but I can't seem to get it to work. And example would be great.
Thanks
Eric
1) DTS package
2) SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\MyExcel.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:254E3DCB-06A0-4D59-9BAB-120B33A59F0B@.microsoft.com...
> I have a table called cr. Within that table I have the colums c_id,
c_type, first_name, last_name. I need help creating a query that will pull
from an excel file that have the same colum names. This excel file is
automaticaly generated from one of our systems and the c_id will never
change for the record.
> So this is what im wanting to do. I need it to look at the xls file and if
there are new records in that xls file it will add those to the table or if
the records have changed it will update the records within the table based
on the c_id. I think this is feasable but I can't seem to get it to work.
And example would be great.
> Thanks
|||and that will insert or update any records?
THanks
"Uri Dimant" wrote:

> Eric
> 1) DTS package
> 2) SELECT *
> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="c:\MyExcel.xls";
> User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
>
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:254E3DCB-06A0-4D59-9BAB-120B33A59F0B@.microsoft.com...
> c_type, first_name, last_name. I need help creating a query that will pull
> from an excel file that have the same colum names. This excel file is
> automaticaly generated from one of our systems and the c_id will never
> change for the record.
> there are new records in that xls file it will add those to the table or if
> the records have changed it will update the records within the table based
> on the c_id. I think this is feasable but I can't seem to get it to work.
> And example would be great.
>
>
sql

Newbie Question

I have a table called cr. Within that table I have the colums c_id, c_type,
first_name, last_name. I need help creating a query that will pull from an e
xcel file that have the same colum names. This excel file is automaticaly ge
nerated from one of our sys
tems and the c_id will never change for the record.
So this is what im wanting to do. I need it to look at the xls file and if t
here are new records in that xls file it will add those to the table or if t
he records have changed it will update the records within the table based on
the c_id. I think this is
feasable but I can't seem to get it to work. And example would be great.
ThanksEric
1) DTS package
2) SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\MyExcel.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:254E3DCB-06A0-4D59-9BAB-120B33A59F0B@.microsoft.com...
> I have a table called cr. Within that table I have the colums c_id,
c_type, first_name, last_name. I need help creating a query that will pull
from an excel file that have the same colum names. This excel file is
automaticaly generated from one of our systems and the c_id will never
change for the record.
> So this is what im wanting to do. I need it to look at the xls file and if
there are new records in that xls file it will add those to the table or if
the records have changed it will update the records within the table based
on the c_id. I think this is feasable but I can't seem to get it to work.
And example would be great.
> Thanks|||and that will insert or update any records?
THanks
"Uri Dimant" wrote:

> Eric
> 1) DTS package
> 2) SELECT *
> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="c:\MyExcel.xls";
> User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
>
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:254E3DCB-06A0-4D59-9BAB-120B33A59F0B@.microsoft.com...
> c_type, first_name, last_name. I need help creating a query that will pull
> from an excel file that have the same colum names. This excel file is
> automaticaly generated from one of our systems and the c_id will never
> change for the record.
> there are new records in that xls file it will add those to the table or i
f
> the records have changed it will update the records within the table based
> on the c_id. I think this is feasable but I can't seem to get it to work.
> And example would be great.
>
>

Newbie Question

I have a table called cr. Within that table I have the colums c_id, c_type, first_name, last_name. I need help creating a query that will pull from an excel file that have the same colum names. This excel file is automaticaly generated from one of our systems and the c_id will never change for the record.
So this is what im wanting to do. I need it to look at the xls file and if there are new records in that xls file it will add those to the table or if the records have changed it will update the records within the table based on the c_id. I think this is feasable but I can't seem to get it to work. And example would be great.
ThanksEric
1) DTS package
2) SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\MyExcel.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:254E3DCB-06A0-4D59-9BAB-120B33A59F0B@.microsoft.com...
> I have a table called cr. Within that table I have the colums c_id,
c_type, first_name, last_name. I need help creating a query that will pull
from an excel file that have the same colum names. This excel file is
automaticaly generated from one of our systems and the c_id will never
change for the record.
> So this is what im wanting to do. I need it to look at the xls file and if
there are new records in that xls file it will add those to the table or if
the records have changed it will update the records within the table based
on the c_id. I think this is feasable but I can't seem to get it to work.
And example would be great.
> Thanks

Newbie needing help creating query

Hi,

I know some SQL but not enough to write the query i'm trying to create and
could do with some help!

I have 2 tables (Product and ProductProgram) that are linked by a common
identified 'ProductID'. Each product has 5 different price levels
(1,2,3,4,5) and these are stored in the ProductProgram table.

The ProductProgram table contains the following columns:

ProductID
Level
Price

The Product table has the following columns:

ProductID
Name
Description

Can anyone show me how to return each product with all 5 of their individual
price levels?SELECT a.ProductID,
a.Name,
a.Description,
p1.Price AS Price1,
p2.Price AS Price2,
p3.Price AS Price3,
p4.Price AS Price4,
p5.Price AS Price5
FROM Product a
LEFT OUTER JOIN ProductProgram p1 ON p1.ProductID=a.ProductID AND
p1.Level=1
LEFT OUTER JOIN ProductProgram p2 ON p2.ProductID=a.ProductID AND
p2.Level=2
LEFT OUTER JOIN ProductProgram p3 ON p3.ProductID=a.ProductID AND
p3.Level=3
LEFT OUTER JOIN ProductProgram p4 ON p4.ProductID=a.ProductID AND
p4.Level=4
LEFT OUTER JOIN ProductProgram p5 ON p5.ProductID=a.ProductID AND
p5.Level=5|||Hi Mark,

Thanks very much! I would never have gotten there on my own!

Mintyman

<markc600@.hotmail.comwrote in message
news:1163429919.872807.120980@.b28g2000cwb.googlegr oups.com...

Quote:

Originally Posted by

SELECT a.ProductID,
a.Name,
a.Description,
p1.Price AS Price1,
p2.Price AS Price2,
p3.Price AS Price3,
p4.Price AS Price4,
p5.Price AS Price5
FROM Product a
LEFT OUTER JOIN ProductProgram p1 ON p1.ProductID=a.ProductID AND
p1.Level=1
LEFT OUTER JOIN ProductProgram p2 ON p2.ProductID=a.ProductID AND
p2.Level=2
LEFT OUTER JOIN ProductProgram p3 ON p3.ProductID=a.ProductID AND
p3.Level=3
LEFT OUTER JOIN ProductProgram p4 ON p4.ProductID=a.ProductID AND
p4.Level=4
LEFT OUTER JOIN ProductProgram p5 ON p5.ProductID=a.ProductID AND
p5.Level=5
>

Newbie help with dynamic SQL

I am creating a stored procedure that will perform a search against a
table. I am passing search parameters to the SP. If the user did not
select a value on the front end, then I am passing NULL into the SP for
that field. So my question is, what is the best practice to only search
on a field if a value is passed for the given field?

This is what I was thinking, but obviously this doesn't work:

@.vchrFieldOne VARCHAR (200) = NULL

SELECT ...
FROM ...
WHERE
0=0

AND
CASE @.vchrFieldOne
WHEN NULL THEN (0 = 0)
ELSE (vchrFieldOne = @.vchrFieldOne)
ENDMine would look something like this:

CREATE PROC pub_info2 @.vchrFieldOne varchar(200) = NULL
AS
If (@.vchrFieldOne is null)
BEGIN
-- select statement where value is not given
END
ELSE
BEGIN
-- select statement where value is given
END

Hope it helps,
Tony Sebion

"Erich93063" <erich93063@.gmail.com> wrote in message
news:1123175211.631764.144360@.z14g2000cwz.googlegr oups.com:

> I am creating a stored procedure that will perform a search against a
> table. I am passing search parameters to the SP. If the user did not
> select a value on the front end, then I am passing NULL into the SP for
> that field. So my question is, what is the best practice to only search
> on a field if a value is passed for the given field?
> This is what I was thinking, but obviously this doesn't work:
> @.vchrFieldOne VARCHAR (200) = NULL
> SELECT ...
> FROM ...
> WHERE
> 0=0
> AND
> CASE @.vchrFieldOne
> WHEN NULL THEN (0 = 0)
> ELSE (vchrFieldOne = @.vchrFieldOne)
> END|||Erich93063 (erich93063@.gmail.com) writes:
> I am creating a stored procedure that will perform a search against a
> table. I am passing search parameters to the SP. If the user did not
> select a value on the front end, then I am passing NULL into the SP for
> that field. So my question is, what is the best practice to only search
> on a field if a value is passed for the given field?
> This is what I was thinking, but obviously this doesn't work:
> @.vchrFieldOne VARCHAR (200) = NULL
> SELECT ...
> FROM ...
> WHERE
> 0=0
> AND
> CASE @.vchrFieldOne
> WHEN NULL THEN (0 = 0)
> ELSE (vchrFieldOne = @.vchrFieldOne)
> END

It doesn't work for several reasons.

1) WHEN NULL - is the same as "WHEN @.charFieldOne = NULL", but in SQL
NULL is never equal to anything, not even another NULL. NULL is a
unknown value, and any comparison with NULL yields the value UNKNOWN.
Correct is WHEN @.vcharFieldOne IS NULL.
2) The return value of a CASE expresssion is always an SQL Server data
type, and there is no boolean data type in T-SQL. Thus you cannot
have "THEN (0 = 9".

The normal way to write this is

AND (vchrFieldOne = @.vchrFieldOne OR @.vchrFieldOne IS NULL)

However, while this works as far as giving the correct result, the
performance can be unbearable. I have an article on my web site that
discusses a number of alternatives for dynamic searches,
http://www.sommarskog.se/dyn-search.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for the reply. I tried it (At least what I think you meant) but
I get an error (Incorrect syntax near the keyword 'AND' and Incorrect
syntax near the keyword 'END'. Here is what I have:

SELECT ...
FROM...
WHERE 0=0

IF (@.vchrFieldOne IS NULL)

BEGIN
AND 0=0
END
ELSE
BEGIN
AND vchrFieldOne = @.vchrFieldOne
END|||Nice article. Thanks for sharing it.|||YES, I read your article and used your example for dynamic SQL and it
works PERFECTLY. THANKS!!!!!!!!!!!!!

newbie confusion: file vs server

caution: this is not doubt a stupid newbie question...

In creating vs.net 2005 website, I can add a sql database to my project and a mdf file is created. I can create data providers against this file, etc, just as though it were a database in a sql server instance. I can deploy this dbf file to my finished web site.

Also, I can attach to a running instance of sql server 2005 express, and do exactly the same thing.

I can also take my mdf file created in step 1 above, and attach it to a running instance of sql server express.

Now, I have delt with access databases, and sql server 2000 databases, so this dual nature of sql server 2005 express confuses me a little.

Why would I ever need to use a server instance of sql server 2005 when I can use a file based data file in my web apps? Is there an advantage to one or the other?

I had a thought that when using the file based method, I was actually still using the server based stuff, which would explain why the sql server express notification bubble pops up when I debug on the dev machine.

In any case could someone explain the difference and should I install sql server 2005 express on my deployment server?

thanks.

Access is a database SQL Server Express is a SQL Server 2005 edition which is RDBMS(relational database management systems) without a SQL Server instance you don't have a database engine to run your MDF(Microsoft data file) it is just one of at least two files you need to run your database. Run a search for file groups in SQL Server BOL(books online). Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms179316.aspx

|||

In order to use a *.mdf (SQL Server database file), you must have a SQL Server installed and running.

That could be SQL Server 2005 Express.

|||

Thanks for the replies. I have a clearer picture but have some additional questions...

I add a mdf file to my asp.net website. This file requires some version of SQL server, in this case

Express, to provide the RDMS system. Great. So how is it that I can just deploy the app to the web server

and it works without installing SQL server Express on the web server?

Is SQL Server Express built into the ASP.Net 2.0 runtime?

If so, the file must somehow be dynamically attached to this instance of SQL Server Express?

How will this type of deployment be upgraded to a higher version of SQL Server? If I use the "file" based

technique, will my app always use the embeded SQL Server Express and not any fuller version of SQL Server

I may install?

So to migrate to a higher version of SQL Server, will I need to attach the mdf file to SQL Server and change my

connection string to point to the new version of SQL Server?

This is my last little stumbling block to grasping the SQL Server Express concept, thanks for your replies!

|||

Perhaps this will clear some things up for you.

http://msdn2.microsoft.com/en-us/library/bb264564.aspx

|||

yes, perfect. Thanks.

No doubt I over think these things.

While I appreciate the ease of use, I also like knowing what's going on, so the documentation is there, of course, but in such great volume one questions the point of reading massive amounts of documentation on an easy to use feature. ;-) I digress...thanks again.

Newbie Cant connect - Trusted Server issue.

Hello,

I'm creating an application that will rely on a central SQL Server database
for its help file. I'm a beginner with SQL Server really, and I keep
getting the following error;

"Login failed for use 'whateverUser'. Reason: Not associated with a trusted
SQL Server connection"

I can't seem to find a solution in the help files. Any direction would be
greatly appreciated.

Thanks!Jozef (SPAM_O_NATOR@.telus.net) writes:
> I'm creating an application that will rely on a central SQL Server
> database for its help file. I'm a beginner with SQL Server really, and
> I keep getting the following error;
> "Login failed for use 'whateverUser'. Reason: Not associated with a
> trusted SQL Server connection"
> I can't seem to find a solution in the help files. Any direction would be
> greatly appreciated.

You can log into SQL Server in two ways: Windows authentication, and
SQL Server authentication. The former is always available, for SQL Server
authentication to be available, the server must be enabled for this,
which by default it is not.

With Windows authentication, SQL Server accepts your Windows user,
if it has been granted access to SQL Server, and if SQL Server can
somehow validate it. With SQL Server authentication, you provide a
username and password that SQL Server itself validates.

Windows authentication is generally more secure, and also more convient,
since it does not require any extra username/password. However, it does
not always work well, if user and server are not in the same domain -
including when there is no domain at all.

Since you replaced the actual username with 'whateverUser', I don't know
which form of authentication you are trying to use. But if you are not
using Windows authentication, I recommend that you switch to this, by
replacing UserID and Password in the connection string with
Trusted_connection=Yes.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland, that's the most definitive answer I've been able to find.
I'd like to use Windows authentication if I can but this has given me enough
info to be able to play with both.

Thanks again!

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9679C13AF3F52Yazorman@.127.0.0.1...
> Jozef (SPAM_O_NATOR@.telus.net) writes:
>> I'm creating an application that will rely on a central SQL Server
>> database for its help file. I'm a beginner with SQL Server really, and
>> I keep getting the following error;
>>
>> "Login failed for use 'whateverUser'. Reason: Not associated with a
>> trusted SQL Server connection"
>>
>> I can't seem to find a solution in the help files. Any direction would
>> be
>> greatly appreciated.
> You can log into SQL Server in two ways: Windows authentication, and
> SQL Server authentication. The former is always available, for SQL Server
> authentication to be available, the server must be enabled for this,
> which by default it is not.
> With Windows authentication, SQL Server accepts your Windows user,
> if it has been granted access to SQL Server, and if SQL Server can
> somehow validate it. With SQL Server authentication, you provide a
> username and password that SQL Server itself validates.
> Windows authentication is generally more secure, and also more convient,
> since it does not require any extra username/password. However, it does
> not always work well, if user and server are not in the same domain -
> including when there is no domain at all.
> Since you replaced the actual username with 'whateverUser', I don't know
> which form of authentication you are trying to use. But if you are not
> using Windows authentication, I recommend that you switch to this, by
> replacing UserID and Password in the connection string with
> Trusted_connection=Yes.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Newbie Can't connect - Trusted Server issue.

Hello,
I'm creating an application that will rely on a central SQL Server database
for its help file. I'm a beginner with SQL Server really, and I keep
getting the following error;
"Login failed for use 'whateverUser'. Reason: Not associated with a trusted
SQL Server connection"
I can't seem to find a solution in the help files. Any direction would be
greatly appreciated.
Thanks!
Jozef (SPAM_O_NATOR@.telus.net) writes:
> I'm creating an application that will rely on a central SQL Server
> database for its help file. I'm a beginner with SQL Server really, and
> I keep getting the following error;
> "Login failed for use 'whateverUser'. Reason: Not associated with a
> trusted SQL Server connection"
> I can't seem to find a solution in the help files. Any direction would be
> greatly appreciated.
You can log into SQL Server in two ways: Windows authentication, and
SQL Server authentication. The former is always available, for SQL Server
authentication to be available, the server must be enabled for this,
which by default it is not.
With Windows authentication, SQL Server accepts your Windows user,
if it has been granted access to SQL Server, and if SQL Server can
somehow validate it. With SQL Server authentication, you provide a
username and password that SQL Server itself validates.
Windows authentication is generally more secure, and also more convient,
since it does not require any extra username/password. However, it does
not always work well, if user and server are not in the same domain -
including when there is no domain at all.
Since you replaced the actual username with 'whateverUser', I don't know
which form of authentication you are trying to use. But if you are not
using Windows authentication, I recommend that you switch to this, by
replacing UserID and Password in the connection string with
Trusted_connection=Yes.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||You also might want to verify the connection string you are using is
completely correct. http://www.connectionstrings.com has a good collection
of all the various formats to use.
If you're just getting started in programming, you also might want to check
out www.asp.net and www.devx.com. They have some great articles and samples
to get you started, in addition to books, etc.
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"Erland Sommarskog" wrote:

> Jozef (SPAM_O_NATOR@.telus.net) writes:
> You can log into SQL Server in two ways: Windows authentication, and
> SQL Server authentication. The former is always available, for SQL Server
> authentication to be available, the server must be enabled for this,
> which by default it is not.
> With Windows authentication, SQL Server accepts your Windows user,
> if it has been granted access to SQL Server, and if SQL Server can
> somehow validate it. With SQL Server authentication, you provide a
> username and password that SQL Server itself validates.
> Windows authentication is generally more secure, and also more convient,
> since it does not require any extra username/password. However, it does
> not always work well, if user and server are not in the same domain -
> including when there is no domain at all.
> Since you replaced the actual username with 'whateverUser', I don't know
> which form of authentication you are trying to use. But if you are not
> using Windows authentication, I recommend that you switch to this, by
> replacing UserID and Password in the connection string with
> Trusted_connection=Yes.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp
>
|||Thanks Erland, that's the most definitive answer I've been able to find.
I'd like to use Windows authentication if I can but this has given me enough
info to be able to play with both.
Thanks again!
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9679C13AF3F52Yazorman@.127.0.0.1...
> Jozef (SPAM_O_NATOR@.telus.net) writes:
> You can log into SQL Server in two ways: Windows authentication, and
> SQL Server authentication. The former is always available, for SQL Server
> authentication to be available, the server must be enabled for this,
> which by default it is not.
> With Windows authentication, SQL Server accepts your Windows user,
> if it has been granted access to SQL Server, and if SQL Server can
> somehow validate it. With SQL Server authentication, you provide a
> username and password that SQL Server itself validates.
> Windows authentication is generally more secure, and also more convient,
> since it does not require any extra username/password. However, it does
> not always work well, if user and server are not in the same domain -
> including when there is no domain at all.
> Since you replaced the actual username with 'whateverUser', I don't know
> which form of authentication you are trying to use. But if you are not
> using Windows authentication, I recommend that you switch to this, by
> replacing UserID and Password in the connection string with
> Trusted_connection=Yes.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp

Newbie Can't connect - Trusted Server issue.

Hello,
I'm creating an application that will rely on a central SQL Server database
for its help file. I'm a beginner with SQL Server really, and I keep
getting the following error;
"Login failed for use 'whateverUser'. Reason: Not associated with a trusted
SQL Server connection"
I can't seem to find a solution in the help files. Any direction would be
greatly appreciated.
Thanks!Jozef (SPAM_O_NATOR@.telus.net) writes:
> I'm creating an application that will rely on a central SQL Server
> database for its help file. I'm a beginner with SQL Server really, and
> I keep getting the following error;
> "Login failed for use 'whateverUser'. Reason: Not associated with a
> trusted SQL Server connection"
> I can't seem to find a solution in the help files. Any direction would be
> greatly appreciated.
You can log into SQL Server in two ways: Windows authentication, and
SQL Server authentication. The former is always available, for SQL Server
authentication to be available, the server must be enabled for this,
which by default it is not.
With Windows authentication, SQL Server accepts your Windows user,
if it has been granted access to SQL Server, and if SQL Server can
somehow validate it. With SQL Server authentication, you provide a
username and password that SQL Server itself validates.
Windows authentication is generally more secure, and also more convient,
since it does not require any extra username/password. However, it does
not always work well, if user and server are not in the same domain -
including when there is no domain at all.
Since you replaced the actual username with 'whateverUser', I don't know
which form of authentication you are trying to use. But if you are not
using Windows authentication, I recommend that you switch to this, by
replacing UserID and Password in the connection string with
Trusted_connection=Yes.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||You also might want to verify the connection string you are using is
completely correct. http://www.connectionstrings.com has a good collection
of all the various formats to use.
If you're just getting started in programming, you also might want to check
out www.asp.net and www.devx.com. They have some great articles and samples
to get you started, in addition to books, etc.
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"Erland Sommarskog" wrote:

> Jozef (SPAM_O_NATOR@.telus.net) writes:
> You can log into SQL Server in two ways: Windows authentication, and
> SQL Server authentication. The former is always available, for SQL Server
> authentication to be available, the server must be enabled for this,
> which by default it is not.
> With Windows authentication, SQL Server accepts your Windows user,
> if it has been granted access to SQL Server, and if SQL Server can
> somehow validate it. With SQL Server authentication, you provide a
> username and password that SQL Server itself validates.
> Windows authentication is generally more secure, and also more convient,
> since it does not require any extra username/password. However, it does
> not always work well, if user and server are not in the same domain -
> including when there is no domain at all.
> Since you replaced the actual username with 'whateverUser', I don't know
> which form of authentication you are trying to use. But if you are not
> using Windows authentication, I recommend that you switch to this, by
> replacing UserID and Password in the connection string with
> Trusted_connection=Yes.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>|||Thanks Erland, that's the most definitive answer I've been able to find.
I'd like to use Windows authentication if I can but this has given me enough
info to be able to play with both.
Thanks again!
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9679C13AF3F52Yazorman@.127.0.0.1...
> Jozef (SPAM_O_NATOR@.telus.net) writes:
> You can log into SQL Server in two ways: Windows authentication, and
> SQL Server authentication. The former is always available, for SQL Server
> authentication to be available, the server must be enabled for this,
> which by default it is not.
> With Windows authentication, SQL Server accepts your Windows user,
> if it has been granted access to SQL Server, and if SQL Server can
> somehow validate it. With SQL Server authentication, you provide a
> username and password that SQL Server itself validates.
> Windows authentication is generally more secure, and also more convient,
> since it does not require any extra username/password. However, it does
> not always work well, if user and server are not in the same domain -
> including when there is no domain at all.
> Since you replaced the actual username with 'whateverUser', I don't know
> which form of authentication you are trying to use. But if you are not
> using Windows authentication, I recommend that you switch to this, by
> replacing UserID and Password in the connection string with
> Trusted_connection=Yes.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Newbee question about permissions and sp_OACreate

Hi,
I'm creating a trigger that shall monitor a table on inserts and if certain
conditions are met, it should send a mail the administrator.
I do not want to use SQLmail for many reasons so I'm using the extended
procedure sp_OACreate to create an instance of the CDO.Message object
The problem is that only symins can use the extended procedure
sp_OACreate, and of cause I do not want users to be symins.
I think I read or learned somewhere that stored procedures runs under the
security context of the user that created the procedure, so why can't I as
the symin create a my_sendmail procedure that uses the sp_OACreate
extended procedure and grant permission to all users to the my_sendmail
procedure?
Or, is there another way?
Regards,
Jorgen D.First of all, this excedes the purpose of triggers. What has sending mail go
t
to do with ACID? It would be much more efficient if you created a job to
monitor the table and log these special events in a separate table/file and
mail the report/warning.
Second, why is it not possible for you to use SQL Mail? It's far more
efficient than (ab)using sp_OA procedures. And it can be used by any user
without compromising the security.
How about notification services? If mailing these messages was requested by
a manager, you can be pretty sure, he'll start hating it after about a month
.
Either the messages will be too frequent or too infrequent and he'll still
need to keep statistics somewhere, somehow. But I'm just guessing here...
Anyway, seems to me that what you really need is some sort of logical event
logging. This IS the purpose of triggers - propagating changes in one table
to another.
Be careful out there...
ML|||Why do some people answer a question with more questions? (no, thanks for
your input)
My trigger do populate data to other tables (sending work orders to PDA’s,
via merge replication), and when done it’s important that the users
implicated (the ones that gets the work order) gets an mail/sms to inform
them about it (they should react to it immediately)
This mail shall originate form different senders, therefore not SQL-mail
which is based on a single profile (perhaps there is a way around that that
I
didn’t find)
Another reason not to use SQL-mail is in another scenario, that I’m not
going to explain in detail, but the mail should be HTML formatted report
larger than 8000 bytes.
Notification services? Hmmm, I’ll look into that, (as I don’t know what
that
is)
But to get back to my original question….. If I want to use an extended
procedure in my own procedure, how can I set permissions so the ordinary
mortal users can execute that?
Regards,
Jorgen D.
"ML" wrote:

> First of all, this excedes the purpose of triggers. What has sending mail
got
> to do with ACID? It would be much more efficient if you created a job to
> monitor the table and log these special events in a separate table/file an
d
> mail the report/warning.
> Second, why is it not possible for you to use SQL Mail? It's far more
> efficient than (ab)using sp_OA procedures. And it can be used by any user
> without compromising the security.
> How about notification services? If mailing these messages was requested b
y
> a manager, you can be pretty sure, he'll start hating it after about a mon
th.
> Either the messages will be too frequent or too infrequent and he'll still
> need to keep statistics somewhere, somehow. But I'm just guessing here...
> Anyway, seems to me that what you really need is some sort of logical even
t
> logging. This IS the purpose of triggers - propagating changes in one tabl
e
> to another.
> Be careful out there...
>
> ML|||Any user who needs to execute sp_OA procedures must have appropriate
privileges to do so.
I'm sorry, I'm simply curious as to what you're trying to do. You say
certain users must be notified (with a large report, even) of certain change
s
to yor data. And this must be done immediately after those changes were
commited. Why? Do this users then do something? Couldn't that be done
automatically?
It's like in that nuclear plant: "push this button when that light goes on"
- why not just automatically do whatever the button does when conditions are
met which normally tur the light on...?
Instead of sending huge reports you could simply send a link to the report
built by the SQL Reporting Services.
Consider the alternatives before you compromise security.
ML|||Some users are security guards which have to respond to alarms. When they ge
t
notified they should turn on their PDA and connect to the server to get
detailed information of the alarm (address, codes etc.). Other users are
doing emergency road help. So the notification is important to be timely.
They can’t ask the server/system to drive to an incident.
About the reports – They aren’t huge. A single HTML formatted page (tabl
es,
and colours) is larger than 8K which is the limit of SQL-mail, further more
I
can’t get SQL-mail to send in HTML. The reports are requested from the use
rs
PDA to be sent to the customers email. The reports are dynamically created o
n
the server and can’t be sent from the PDA (they don’t have the data)
Conclusion: Since I have to use CDO.Message I have to grant all users public
access to the master database, and then on the sp_OAxxxxx procedures, give
the users EXEC rights. What happened to the ideology “The code of a stored
procedure runs within the security context of the creator”?
"JorgenD" wrote:
> Why do some people answer a question with more questions? (no, thanks for
> your input)
> My trigger do populate data to other tables (sending work orders to PDA’
s,
> via merge replication), and when done it’s important that the users
> implicated (the ones that gets the work order) gets an mail/sms to inform
> them about it (they should react to it immediately)
> This mail shall originate form different senders, therefore not SQL-mail
> which is based on a single profile (perhaps there is a way around that tha
t I
> didn’t find)
> Another reason not to use SQL-mail is in another scenario, that I’m not
> going to explain in detail, but the mail should be HTML formatted report
> larger than 8000 bytes.
> Notification services? Hmmm, I’ll look into that, (as I don’t know wha
t that
> is)
> But to get back to my original question….. If I want to use an extended
> procedure in my own procedure, how can I set permissions so the ordinary
> mortal users can execute that?
> Regards,
> Jorgen D.
>
> "ML" wrote:
>|||You're describing a messaging and reporting system for which web services ar
e
the ideal solution. You have a central data server or a data warehouse and
several clients, that aren't capable of maintaining a 24/7 connection to HQ.
Wouldn't it be more efficient if the clients would only receive short
messages, rather than full reports? Upon the notification they could then
connect to the Report server to see the details of the arising situation and
then decide if they need to download them for off-line use.
That way you can allow access to the same data to the PDA users and to the
desktop users.
You might end up developing a messaging and reporting system using
webservices and smart clients. You could even earn $50 grand!!! :)
(http://www.csdevcompetition.com/)
Giving master database/extended procedure privileges to the Public role is a
bad idea. It is IMHO essential to use a custom role for this - with
explicitly limited privileges.
Also consider the fact that your inserts might slow down due to the needed
complexity of these triggers - that is if the conditions are met frequently.
Of course you'll need a lot of error-handling as well - you don't want to
lock up the server, do you?
ML|||Hi
Look at SQL Server Notification Services to kick of a process where you have
a .NET application send the mail via SMTP.
If you kick off an sp_OA* within a trigger, and it fails, you end up having
a rolled back transaction, possible some e-mails being sent about data that
no longer exists.
If you do not want to use NS, then in your trigger, write a row to another
table indicate that something must be done, and then have SQL Server Agent
process poll the table and send the e-mail.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"ML" wrote:

> You're describing a messaging and reporting system for which web services
are
> the ideal solution. You have a central data server or a data warehouse and
> several clients, that aren't capable of maintaining a 24/7 connection to H
Q.
> Wouldn't it be more efficient if the clients would only receive short
> messages, rather than full reports? Upon the notification they could then
> connect to the Report server to see the details of the arising situation a
nd
> then decide if they need to download them for off-line use.
> That way you can allow access to the same data to the PDA users and to the
> desktop users.
> You might end up developing a messaging and reporting system using
> webservices and smart clients. You could even earn $50 grand!!! :)
> (http://www.csdevcompetition.com/)
>
> Giving master database/extended procedure privileges to the Public role is
a
> bad idea. It is IMHO essential to use a custom role for this - with
> explicitly limited privileges.
> Also consider the fact that your inserts might slow down due to the needed
> complexity of these triggers - that is if the conditions are met frequentl
y.
> Of course you'll need a lot of error-handling as well - you don't want to
> lock up the server, do you?
>
> ML

Newb question regarding cluster load balancing

Hello to all,
I am currently working on creating a Windows 2003 server cluster (3 nodes)
with the intent that it serve our web app which requires multiple SQL
databases. The question I have is this: How can I configure load balancing
for this? It seems that clustering services only allows 1 of the nodes to
access the DB at any given time (which is logical). If this is true, how
will I be able to leverage the processing power of the other two nodes if
they are idle while the node that is hosting the DB does all the work?
Any help would be appreciated.
Michael
You can install multiple instances of SQL Server on a failover cluster. You
cannot natively load-balance the workload between these multiple instances.
They act as independent SQL servers. SQL Clustering is a failover
technology and does not natively load balance.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Michael Weyant" <mweyant@.themarlincompany.com> wrote in message
news:OshvlPrGGHA.312@.TK2MSFTNGP09.phx.gbl...
> Hello to all,
> I am currently working on creating a Windows 2003 server cluster (3 nodes)
> with the intent that it serve our web app which requires multiple SQL
> databases. The question I have is this: How can I configure load balancing
> for this? It seems that clustering services only allows 1 of the nodes to
> access the DB at any given time (which is logical). If this is true, how
> will I be able to leverage the processing power of the other two nodes if
> they are idle while the node that is hosting the DB does all the work?
> Any help would be appreciated.
> Michael
>
|||Thanks Geoff for the information. I thought as much but hoped otherwise.
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:e9dbmVrGGHA.3856@.TK2MSFTNGP12.phx.gbl...
> You can install multiple instances of SQL Server on a failover cluster.
> You cannot natively load-balance the workload between these multiple
> instances. They act as independent SQL servers. SQL Clustering is a
> failover technology and does not natively load balance.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Michael Weyant" <mweyant@.themarlincompany.com> wrote in message
> news:OshvlPrGGHA.312@.TK2MSFTNGP09.phx.gbl...
>