Access Control and Security?

Are there any books/article/forum posts out there that document the
best practices for SQL Server security?
For example, what account should create the database?
What account should create the schema?
In a given database should there be a login that is solely responsible
for DDL and a seperate account that only does DML and Queries?
What conventions are people using?
I'm kind of clueless about this and trying to figure out where to
start.
-ThxWhat version of SQL Server are you using? Best practices for SQLS 2000
do not necessarily apply to 2005, where there are expanded options not
available in earlier versions. A good starting point is always SQLS
Books Online, supplemented with a good searh engine :)
-mary
On Wed, 22 Aug 2007 05:58:08 -0700, kilik3000@.gmail.com wrote:
>Are there any books/article/forum posts out there that document the
>best practices for SQL Server security?
>For example, what account should create the database?
>What account should create the schema?
>In a given database should there be a login that is solely responsible
>for DDL and a seperate account that only does DML and Queries?
>What conventions are people using?
>I'm kind of clueless about this and trying to figure out where to
>start.
>-Thx|||On Aug 23, 1:45 pm, "Mary Chipman [MSFT]" <mc...@.online.microsoft.com>
wrote:
> What version of SQL Server are you using? Best practices for SQLS 2000
> do not necessarily apply to 2005, where there are expanded options not
> available in earlier versions. A good starting point is always SQLS
> Books Online, supplemented with a good searh engine :)
> -mary
> On Wed, 22 Aug 2007 05:58:08 -0700, kilik3...@.gmail.com wrote:
> >Are there any books/article/forum posts out there that document the
> >best practices for SQL Server security?
> >For example, what account should create the database?
> >What account should create the schema?
> >In a given database should there be a login that is solely responsible
> >for DDL and a seperate account that only does DML and Queries?
> >What conventions are people using?
> >I'm kind of clueless about this and trying to figure out where to
> >start.
> >-Thx
SQL 2005.
-Thx|||That's a tough one. Basic information is in SQLS Books Online
(http://msdn2.microsoft.com/en-us/library/ms161948.aspx) but it
doesn't directly answer your questions, you have to infer the answers.
Also, your security architecture depends on your needs. DDL has gotten
a lot easier with user-schema separation in 2005 once you understand
it. Erland Sommarskog's web site has some good resources - see Giving
Permissions through Stored Procedures
(http://www.sommarskog.se/grantperm.html).
-mary
On Tue, 28 Aug 2007 20:01:27 -0000, kilik3000@.gmail.com wrote:
>On Aug 23, 1:45 pm, "Mary Chipman [MSFT]" <mc...@.online.microsoft.com>
>wrote:
>> What version of SQL Server are you using? Best practices for SQLS 2000
>> do not necessarily apply to 2005, where there are expanded options not
>> available in earlier versions. A good starting point is always SQLS
>> Books Online, supplemented with a good searh engine :)
>> -mary
>> On Wed, 22 Aug 2007 05:58:08 -0700, kilik3...@.gmail.com wrote:
>> >Are there any books/article/forum posts out there that document the
>> >best practices for SQL Server security?
>> >For example, what account should create the database?
>> >What account should create the schema?
>> >In a given database should there be a login that is solely responsible
>> >for DDL and a seperate account that only does DML and Queries?
>> >What conventions are people using?
>> >I'm kind of clueless about this and trying to figure out where to
>> >start.
>> >-Thx
>SQL 2005.
>-Thx

Access and extended properties

Hello,
Can I read and write the extended properties with Access?

If it’s possible is better create a project (ADP) or a classic MDB?

When I try with this query in the query panel (in a MDB)

SELECT objtype, objname, name, value
FROM fn_listextendedproperty
(NULL, 'schema', 'Person', 'table', default, NULL, NULL)

I have this error message:

The size of a field is too long

If I try with an ADP, when I ask a new query in design view, I have a message for some roblems between the Access version (2003) and SQL Server version (2005) and I can’t save the query.

Tanks.

Hi,

yes - there's no support for SQL Server 2005 in an Access 2003 ADP (you cant save tables, ,,,). If you have this combination it would be better to make a mdb file with odbc connection.... access 2007 supports sql server 2005 more or less...

if you create a mdb you can execute your sql query with ado or pt querys...

regards

audi

Access and extended properties

Hello,
Can I read and write the extended properties with Access?

If it’s possible is better create a project (ADP) or a classic MDB?

When I try with this query in the query panel (in a MDB)

SELECT objtype, objname, name, value
FROM fn_listextendedproperty
(NULL, 'schema', 'Person', 'table', default, NULL, NULL)

I have this error message:

The size of a field is too long

If I try with an ADP, when I ask a new query in design view, I have a message for some roblems between the Access version (2003) and SQL Server version (2005) and I can’t save the query.

Tanks.

Hi,

yes - there's no support for SQL Server 2005 in an Access 2003 ADP (you cant save tables, ,,,). If you have this combination it would be better to make a mdb file with odbc connection.... access 2007 supports sql server 2005 more or less...

if you create a mdb you can execute your sql query with ado or pt querys...

regards

audi

Access ADP - SQL server diagram conflicts!

I've run into an annoyance, if I create a new Access project from an
existing SQL database and open up diagrams created earlier with th
enterprise manager Access XP crashes. Creating new diagrams through Access
seems to work just fine. Anyone else run into this? Thanks all.
JonSounds like an Access bug. I'd ask on those newsgroups. It may be
fixed in newer versions of Access.
--Mary
On Thu, 24 Jun 2004 08:25:04 -0400, "Jon" <jonremovemewest@.msn.com>
wrote:

>I've run into an annoyance, if I create a new Access project from an
>existing SQL database and open up diagrams created earlier with th
>enterprise manager Access XP crashes. Creating new diagrams through Access
>seems to work just fine. Anyone else run into this? Thanks all.
>Jon
>

Access ADP - SQL server diagram conflicts!

I've run into an annoyance, if I create a new Access project from an
existing SQL database and open up diagrams created earlier with th
enterprise manager Access XP crashes. Creating new diagrams through Access
seems to work just fine. Anyone else run into this? Thanks all.
JonSounds like an Access bug. I'd ask on those newsgroups. It may be
fixed in newer versions of Access.
--Mary
On Thu, 24 Jun 2004 08:25:04 -0400, "Jon" <jonremovemewest@.msn.com>
wrote:
>I've run into an annoyance, if I create a new Access project from an
>existing SQL database and open up diagrams created earlier with th
>enterprise manager Access XP crashes. Creating new diagrams through Access
>seems to work just fine. Anyone else run into this? Thanks all.
>Jon
>

Access ADP - SQL server diagram conflicts!

I've run into an annoyance, if I create a new Access project from an
existing SQL database and open up diagrams created earlier with th
enterprise manager Access XP crashes. Creating new diagrams through Access
seems to work just fine. Anyone else run into this? Thanks all.
Jon
Sounds like an Access bug. I'd ask on those newsgroups. It may be
fixed in newer versions of Access.
--Mary
On Thu, 24 Jun 2004 08:25:04 -0400, "Jon" <jonremovemewest@.msn.com>
wrote:

>I've run into an annoyance, if I create a new Access project from an
>existing SQL database and open up diagrams created earlier with th
>enterprise manager Access XP crashes. Creating new diagrams through Access
>seems to work just fine. Anyone else run into this? Thanks all.
>Jon
>
sql

Access a in house sql server from a remote web server

does anybody know how I would create a connection string in asp.net 2.0express to access a database in a different location from the webserver. I have a static IP address on the web server, so I've allowedaccess from the web server through the firewall. I have a user name andpassword, and I've made sure that I have the correct database. Would Iaccess the database using the network IP address and if so, where do Iput it. Thank you to anyone that can give me some insight to thisproblem, I'm very new to asp.net so, about a weeks worth of knowledge,so if you could explain as such I would appreciate it.
Seewww.connectionstrings.com. You put the IP where you normally put the Server name, all else should be the same. That said, hitting a SQL Server on the other side of a firewall may not be the best thing to do.|||thanks
"That said, hitting a SQL Server on the other side of a firewall may not be the best thing to do."
what you recommend then, like I said, I'm pretty new to this. Theproject that I'm working on requires me to create a web form, that ison an outsourced web server, and import the data into our database thatis behind a firewall. Would this be a security issue?

|||Yes, it can be. If SQL Server was perfect, it would not be a problem to leave the SQL port open. SQL Server is not perfect (recall the Slammer virus). So, in the circumstances you describe, perhaps it is your only option. However, I would make very certain that the database server is always patched up to date.sql

Access 2007 Project - SQL Server 2005

Does anyone know whether it is possibe to create an Access 2007 Project - SQL
Server 2005 and have the ability to switch the current database within the
project?
I have multiple customer databases that need updating.
Thanks in advance.
Why don't you read the reply to your yesterday's post in the other group?
"Ed C" <Ed C@.discussions.microsoft.com> wrote in message
news:FA02AA5A-11A7-49C1-8923-E1A9C1CB8830@.microsoft.com...
> Does anyone know whether it is possibe to create an Access 2007 Project -
> SQL
> Server 2005 and have the ability to switch the current database within the
> project?
> I have multiple customer databases that need updating.
> Thanks in advance.
sql

Access 2007 Project - SQL Server 2005

Does anyone know whether it is possibe to create an Access 2007 Project - SQL
Server 2005 and have the ability to switch the current database within the
project?
I have multiple customer databases that need updating.
Thanks in advance.Why don't you read the reply to your yesterday's post in the other group?
"Ed C" <Ed C@.discussions.microsoft.com> wrote in message
news:FA02AA5A-11A7-49C1-8923-E1A9C1CB8830@.microsoft.com...
> Does anyone know whether it is possibe to create an Access 2007 Project -
> SQL
> Server 2005 and have the ability to switch the current database within the
> project?
> I have multiple customer databases that need updating.
> Thanks in advance.

Access 2007 Project - SQL Server 2005

Does anyone know whether it is possibe to create an Access 2007 Project - SQ
L
Server 2005 and have the ability to switch the current database within the
project?
I have multiple customer databases that need updating.
Thanks in advance.Why don't you read the reply to your yesterday's post in the other group?
"Ed C" <Ed C@.discussions.microsoft.com> wrote in message
news:FA02AA5A-11A7-49C1-8923-E1A9C1CB8830@.microsoft.com...
> Does anyone know whether it is possibe to create an Access 2007 Project -
> SQL
> Server 2005 and have the ability to switch the current database within the
> project?
> I have multiple customer databases that need updating.
> Thanks in advance.

Access 2003 iif function

Can someone tell me how i can create an iif function to do the following?
I need to look a field that has an amount in it. if it is 90K or less make i
t
1 if its 180 to 90001 make it 2 etc...
So for every 90K in that amount add 1..
I tried to do a VB code to create a ceiling funciton but Access keeps giving
me an error saying that my function is undefinded? Had a friend run my code
and it worked for him. I did update to the most current service pack and it
still errors out...
Can anyone tell me how I can make either the function work or how to do an
iif function to make it work?
Thank you so much for any help you can give me> Can someone tell me how i can create an iif function to do the following?
> I need to look a field that has an amount in it. if it is 90K or less make
> it
> 1 if its 180 to 90001 make it 2 etc...
SELECT val = CASE WHEN column < 90000 THEN 1
WHEN column < 180000 THEN 2
WHEN column < 270000 THEN 3
ELSE 4 END
FROM table
This is covered here (along with some other things that will help an Access
person moving to SQL Server):
http://www.aspfaq.com/2214|||Please repost this to a Access NG.
HTH
Jerry
"magikgb via webservertalk.com" <u15188@.uwe> wrote in message
news:566081edf8196@.uwe...
> Can someone tell me how i can create an iif function to do the following?
> I need to look a field that has an amount in it. if it is 90K or less make
> it
> 1 if its 180 to 90001 make it 2 etc...
> So for every 90K in that amount add 1..
> I tried to do a VB code to create a ceiling funciton but Access keeps
> giving
> me an error saying that my function is undefinded? Had a friend run my
> code
> and it worked for him. I did update to the most current service pack and
> it
> still errors out...
> Can anyone tell me how I can make either the function work or how to do an
> iif function to make it work?
> Thank you so much for any help you can give me|||ummmm... divide by 90k and add 1? Assuming integers, then:
select 89000 / 90000 + 1 -- = 1
select 90001 / 90000 + 1 -- = 2
select 810001 / 90000 + 1 -- = 10
Payson
magikgb via webservertalk.com wrote:
> Can someone tell me how i can create an iif function to do the following?
> I need to look a field that has an amount in it. if it is 90K or less make
it
> 1 if its 180 to 90001 make it 2 etc...
> So for every 90K in that amount add 1..
> I tried to do a VB code to create a ceiling funciton but Access keeps givi
ng
> me an error saying that my function is undefinded? Had a friend run my cod
e
> and it worked for him. I did update to the most current service pack and i
t
> still errors out...
> Can anyone tell me how I can make either the function work or how to do an
> iif function to make it work?
> Thank you so much for any help you can give me|||Thank you Aaron... I am new to VB is this VB code or just straight SQL?
Aaron Bertrand [SQL Server MVP] wrote:
>SELECT val = CASE WHEN column < 90000 THEN 1
> WHEN column < 180000 THEN 2
> WHEN column < 270000 THEN 3
> ELSE 4 END
>FROM table
>This is covered here (along with some other things that will help an Access
>person moving to SQL Server):
>http://www.aspfaq.com/2214|||This is T-SQL. You are posting to a SQL Server newsgroup. If you are
trying to write VB code or Access SQL, please post to a more appropriate
newsgroup. Then people won't waste their time providing you with useless
solutions...
"magikgb via webservertalk.com" <u15188@.uwe> wrote in message
news:5660b960e4dd5@.uwe...
> Thank you Aaron... I am new to VB is this VB code or just straight SQL?
> Aaron Bertrand [SQL Server MVP] wrote:|||Aaron.. Sorry new to this too...
Aaron Bertrand [SQL Server MVP] wrote:
>This is T-SQL. You are posting to a SQL Server newsgroup. If you are
>trying to write VB code or Access SQL, please post to a more appropriate
>newsgroup. Then people won't waste their time providing you with useless
>solutions...
>
>[quoted text clipped - 16 lines]

Access 2000, SQL Server 2000, Bulk Insert

I am pulling information from a DB2 table through a pass-through query
in Access 2000. I am going to create a comma delimited file that I want
to use Bulk Insert to put into SQL Server 2000.
I have the query to pull the data, all is well. How do I program in VBA
(Acess 2000) to do a bulk insert into a SQL Server table. I have 100000
records that I do this to every day. Bulk Insert seems to be the fastest
way. I will bulk insert the comma delimited file.
I am on a PC, not the SQL Server for the bulk insert. I cannot access
the mainframe DB2 tables from the SQL Server. My PC is the middle man
right now. How do I effectively do the bulk insert.
Does anybody have any experience in doing this, if so, can you post some
code to assist.
Thanks, Lyners
--
Posted via http://dbforums.comOne more thing, I have the code written so that it pulls the data and
loads the records 1 by 1. It takes to long and I keep blowing up the
transaction file. That is why I want to do the bulk insert. 1 record for
the transaction file. (AT least that is what I have read).
Posted via http://dbforums.com

Access <- > SQL Server

Hello!

We have following situation;
network with 100 users, aplication developed in Access, user DB
deployed on SQL Server.

Is it better to create query inside aplication (with code) and then pass
it to SQL Server for execution or is it better to have all these queries
saved like stored procedures and then called from aplication?

Witch queries run faster?

Thank's!

P.S. I know that my english isn't perfect:(!!!!!On Sun, 15 Feb 2004 21:30:11 +0100 in comp.databases.ms-access,
"Jegger" <dzugaj@.net4u.hr> wrote:

>Hello!
> We have following situation;
> network with 100 users, aplication developed in Access, user DB
>deployed on SQL Server.
> Is it better to create query inside aplication (with code) and then pass
>it to SQL Server for execution or is it better to have all these queries
>saved like stored procedures and then called from aplication?
> Witch queries run faster?

Stored procedures are compiled with the execution plan so run faster
than dynamic SQL.

--
A)bort, R)etry, I)nfluence with large hammer.|||On Sun, 15 Feb 2004 21:30:11 +0100, "Jegger" <dzugaj@.net4u.hr> wrote:

>Hello!
> We have following situation;
> network with 100 users, aplication developed in Access, user DB
>deployed on SQL Server.
> Is it better to create query inside aplication (with code) and then pass
>it to SQL Server for execution or is it better to have all these queries
>saved like stored procedures and then called from aplication?
> Witch queries run faster?
> Thank's!
>P.S. I know that my english isn't perfect:(!!!!!

Since you say this is an application developed in Access, and there are,
apparently, no other applications sharing the back-end, my recommendation is
to implement all the queries in Access except those that you can't make run
optimally that way by simply modifying the query's approach (chaning
subqueries to outer joins, etc.). Most queries of linked tables on SQL Server
will run well as Access queries.

The reason for keeping the queries in Access as much as possible is that it
makes the particular choice of server easier to change. It would be
relatively (though not totally) trivial, for instance, to switch to a
PostgreSQL or Oracle back-end.

There is actually very little speed penalty, in most cases, for executing
Access queries against linked, ODBC tables because Access can generally
convert these into prepared statements that actually act quite a bit like
stored procedures in that they are compiled once by the server when they are
first used, then reused when the same query is executed again. SQL Server is
also clever enough to reuse the same query plan when additional statements are
prepared with identical definitions.|||I make it a rule to: (well, rules)

1) Create parameterized stored procedures whenever possible, which usually mean
every read-only situation. (If you're using ADPs, however, MS has done a heluva
job making param SPs updatable.)

2) Create Pass-through queries for any must-have dynamic sql (again,
read-only). Also, PT queries must be used to call those SQL Server SPs!

3) ONLY use JET (Access) queries for those siutations that REQUIRE it, like
updatable BOUND data sources, LinkChild/Master, consumers of VBA custom
functions, etc. (And strive to make your VBA functions into processes that use
SQL Server if you can)

It really boils down to this: Don't use JET unless you ABSOLUTELY MUST. I
haven't gone as far as some JET-haters that use UNBOUND forms w/
insert/update/delete classes, but I'm getting there...

I love Access as a front-end building tool, and a reporting tool. But I can
live without JET.

All of me recent Access/SQL development has been ADPs...no JET! YAYYYY!|||On 15 Feb 2004 23:41:03 GMT, dcmfan@.aol.comSPNOAM (DCM Fan) wrote:

>I make it a rule to: (well, rules)
>1) Create parameterized stored procedures whenever possible, which usually mean
>every read-only situation. (If you're using ADPs, however, MS has done a heluva
>job making param SPs updatable.)
>2) Create Pass-through queries for any must-have dynamic sql (again,
>read-only). Also, PT queries must be used to call those SQL Server SPs!
>3) ONLY use JET (Access) queries for those siutations that REQUIRE it, like
>updatable BOUND data sources, LinkChild/Master, consumers of VBA custom
>functions, etc. (And strive to make your VBA functions into processes that use
>SQL Server if you can)
>It really boils down to this: Don't use JET unless you ABSOLUTELY MUST. I
>haven't gone as far as some JET-haters that use UNBOUND forms w/
>insert/update/delete classes, but I'm getting there...
>I love Access as a front-end building tool, and a reporting tool. But I can
>live without JET.
>All of me recent Access/SQL development has been ADPs...no JET! YAYYYY!

I respect yor opinion, but I disagree. There are cases in which I do agree,
and these are when you will have multiple front-ends to the same data, but
when SQL Server is just the data store for an Access application, the
performance benefit to implenmenting everything server-side is more than
overshadowed by the increased complexity of the front-end, and the loss of
independence from a particular server back-end.

I do favor implementing server-side views and stored procedures as an
optimization when required, but only after a performance problem has been
identified, and it doesn't respond to more minor rearrangments of the queries.

Regarding the use of ADPs fopr client-server apps, I guess that worked for
you, but after I successfully implemented several client-server apps with DAO
and MDBs, I worked on one monster of a project with ADPs, and found it an
ecercise in terrible frustration. Error message handling doesn't work right,
Access is so touchy about the actual implementations of things on the back-end
that many techniques of great use from the server perspective are not usable,
the updatability rules change with every new release of MDAC, so that I've had
to adopt a practice of never trying to bind a form to a query on a join
because I can't trust it (I use hidden combo boxes for FK lookups - how's that
for efficiency!), etc. Frankly, I would never use ADPs again for anything
other than prototypig. I'm at least 5 times more productive using MDBs and
DAO. Of course, if I was using something other than Access as a front-end, I
would nearly always us ADO.

Clearly, your experience differs, but to me ADPs are simply far too unstable
for production systems, and I don't see that they provide any performance
boost, what with having to keep asking the server for schema information
instead of caching it locally in links, constantly digging around behind
procedures and views to update individual tables. You have to jumpt through
several hoops to force Access to respect your intended security and
abstractions with views and procedures, etc. I spend most of my time trying
to get the thing to do what I say instead of guessing what it'll do when it
tries to thnk for me and do what it "knows" I must want.|||Steve, we've been through all this before (pro/con of ADP) in the Access
forums.

Even if everything you say is true about ADPs, and the "common knowledge"
becomes "don't use ADPs, " I still stand by my mantra of "minimize use of JET
if at all possible."

I've never had corruption in 8 years of SQL Server development (It has happened
to others.) I have JET corruption at least once every 6 months. Granted, most
of the time it's caused by faulty hardware, but it scares me that JET is so
"nimble" that way.

Also, I don't know where you get your information on JET query performance, but
in NO WAY is it EVER equal to pass-through performance, except on the smallest
of SELECTS.

I can't begin to tell you how many times Access has either locked up, or I've
gotten impatient at the wait of a mutli-table query designed in the QBE, but
when run right from Query Analyzer returns records in less than 1 second.

Everyone of my combo boxes use Pass-through SELECTS. The performance increase
over JET selects REQUIRES IT, especially when connecting over the internet.
(one-to-one NAT)

Don't get me wrong...I take FULL ADVANTAGE of JET/DAO-based front ends (local
tables, specs, QBE), and I may even come up with an absolute must someday. I
even prefer DAO over ADO, but that's only because I KNOW that I'm more familiar
with it!

My bottom line is this: I'll listen to criticism of ADPs any day (although my
recent ADP projects have been successful, I have lots more exeperince with
MDB), but I can't take it when Access-lovers (I'm one) actually say with a
straight face that JET can perform in any way "as good as" SQL Server views,
SPs, or dynamic SQL.

Just the fact that an extra layer is involved ought to be enough proof...|||
On 16 Feb 2004 05:41:10 GMT, dcmfan@.aol.comSPNOAM (DCM Fan) wrote:

>Steve, we've been through all this before (pro/con of ADP) in the Access
>forums.
>Even if everything you say is true about ADPs, and the "common knowledge"
>becomes "don't use ADPs, " I still stand by my mantra of "minimize use of JET
>if at all possible."
>I've never had corruption in 8 years of SQL Server development (It has happened
>to others.) I have JET corruption at least once every 6 months. Granted, most
>of the time it's caused by faulty hardware, but it scares me that JET is so
>"nimble" that way.

I've had Access corruption, too, but the only times I've had data corruption
were when the back-end was an MDB. That has nothing to do with whether
queries are stored as Access objects or server-side objects when the data is
on the SQL Server.

>Also, I don't know where you get your information on JET query performance, but
>in NO WAY is it EVER equal to pass-through performance, except on the smallest
>of SELECTS.

That's simpy wrong. I've tested it, and I've used the moitoring tools to
watch the SQL. Unless you do something on the Access side that forces it to
do processing locally like joining or filtering on a function, or your query
is just too complex for Access to translate (rare) or if you are missing
indexes on joined fields (and why would you do that on purpose),
Access/DAO/Jet quite happily builds server-side SQL and executes it on the
server.

In the rare cases when I find a query is insisting on processing locally, I
turn part or all of it into a stored procedure or view.

>I can't begin to tell you how many times Access has either locked up, or I've
>gotten impatient at the wait of a mutli-table query designed in the QBE, but
>when run right from Query Analyzer returns records in less than 1 second.

Over time, I have gained an intuitive sense of what queries are dangerous that
way and which are not. It is tru that some time can be wasted with this now
and again.

>Everyone of my combo boxes use Pass-through SELECTS. The performance increase
>over JET selects REQUIRES IT, especially when connecting over the internet.
>(one-to-one NAT)

I have not had the performance issues you describe. It soed seem to help to
make the query DISTINCT, though, so Access will use a snapshot, and not a
Dynaset which would make more than the necessary number of round trips for a
read-only result.

>Don't get me wrong...I take FULL ADVANTAGE of JET/DAO-based front ends (local
>tables, specs, QBE), and I may even come up with an absolute must someday. I
>even prefer DAO over ADO, but that's only because I KNOW that I'm more familiar
>with it!
>My bottom line is this: I'll listen to criticism of ADPs any day (although my
>recent ADP projects have been successful, I have lots more exeperince with
>MDB), but I can't take it when Access-lovers (I'm one) actually say with a
>straight face that JET can perform in any way "as good as" SQL Server views,
>SPs, or dynamic SQL.

Well, I hate to be one of those people (actually, I'm and Access lover/hater,
and I'd take ADO.NET over Jet any day in a C/S project not done in Access),
but what you desccribe has not been my experience. It is true that some
queries need to be translated to run efficiently, but my experience is that
most don't, and in that case, they shouldn't.

I realize we just have to agree to disagree on much of this, but I think it's
important that both of our opinions are fully expressed for the record.

>Just the fact that an extra layer is involved ought to be enough proof...

From what I can tell, ADO/OLEDB is actually a much more complex stack of
layers than DAO/JET/ODBC, and some of what the DAO layer contains is very
beneficial such as the ability to use SQL to join tables from muliple sources,
and the caching of schema data in the front-end rather than looking it up
repeatly from the back-end.

accented characters and ñ's

Hi,
I have a problem in a rss script, when i try to create a folder name that
contains accented charactes or ñ's, the folder is created WITHOUT these
characters.
Example:
rs.CreateFolder("�andú", "/", Nothing)
I run the script and then i go to the report manager and the folder created
is "and"
If i create the folder manually with the report manager it works OK, but i
need the script.
Even when i print this name through the console:
Console.WriteLine("�andú") ' Shows: and
This prints fine in a common vb.net console application, someone knows what
the problem ?
Thanks in advanceThis repros when you save the .RSS file as ANSI but when you save the .RSS
file as Unicode, it works.
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"Diego Belay" <Diego Belay@.discussions.microsoft.com> wrote in message
news:B63EB827-B112-409E-BFF9-7BFE8B43E23C@.microsoft.com...
> Hi,
> I have a problem in a rss script, when i try to create a folder name that
> contains accented charactes or ñ's, the folder is created WITHOUT these
> characters.
> Example:
> rs.CreateFolder("Ñandú", "/", Nothing)
> I run the script and then i go to the report manager and the folder
> created
> is "and"
> If i create the folder manually with the report manager it works OK, but i
> need the script.
> Even when i print this name through the console:
> Console.WriteLine("Ñandú") ' Shows: and
> This prints fine in a common vb.net console application, someone knows
> what
> the problem ?
> Thanks in advance
>|||it works now, thanks!
"Brian Welcker [MS]" wrote:
> This repros when you save the .RSS file as ANSI but when you save the .RSS
> file as Unicode, it works.
>
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Diego Belay" <Diego Belay@.discussions.microsoft.com> wrote in message
> news:B63EB827-B112-409E-BFF9-7BFE8B43E23C@.microsoft.com...
> > Hi,
> > I have a problem in a rss script, when i try to create a folder name that
> > contains accented charactes or ñ's, the folder is created WITHOUT these
> > characters.
> >
> > Example:
> > rs.CreateFolder("�andú", "/", Nothing)
> >
> > I run the script and then i go to the report manager and the folder
> > created
> > is "and"
> > If i create the folder manually with the report manager it works OK, but i
> > need the script.
> >
> > Even when i print this name through the console:
> >
> > Console.WriteLine("�andú") ' Shows: and
> >
> > This prints fine in a common vb.net console application, someone knows
> > what
> > the problem ?
> >
> > Thanks in advance
> >
> >
>
>

About Web service or HTTP ENDPOINT

Hi All,

I met some problems when I walk through a example for Native Http SOAP in SQL Server 2005 for Developers. after I create the HTTP EndPoint in the Management Studio using following code:

USE AdventureWorks
GO

CREATE PROCEDURE EmployeePhoneList
AS
SELECT C.LastName, C.FirstName, C.Phone
FROM Person.Contact AS C
INNER JOIN HumanResources.Employee AS E
ON C.ContactID = E.ContactID
ORDER BY C.LastName, C.FirstName
GO

DROP ENDPOINT HRService;
GO

CREATE ENDPOINT HRService
STATE = STARTED
AS HTTP
(
PATH='/HumanResources',
AUTHENTICATION=(INTEGRATED),
PORTS=(CLEAR)
)
FOR SOAP
(
WEBMETHOD 'EmployeePhoneList'(name='AdventureWorks.dbo.EmployeePhoneList'),
DATABASE='AdventureWorks',
WSDL=DEFAULT
)

then I can see the web service is indeed created by using following cmnd,

SELECT *
FROM sys.endpoints

SELECT *
FROM sys.soap_endpoints;

SELECT *
FROM sys.endpoint_webmethods;

but when I want to use the web service in the VS2005, I can't find it and add the web reference. Any help will be greatly appreciated. and could you please tell me how to check web services in the local computer? Thanks.

Chris

Can you try http://yourmahine/HumanResources?wsd in your browser address bar and see if you can get the wsdl document in xml?

|||

No, I couldn't get the description for the web services, just a "page not found" like error.

In fact, when I run SQL to create HTTP EndPoint, I have to stop the web server first. after the execution of the SQL, I got an internal error from IIS if I restart the server. even though I restart the computer and web server, I still couldn't get the web services description.

I talked with some programmers, none of them use web services directly from sql server2005. and I notice there is few articles about it in the forum. Maybe microsoft just create this funtion for fun?

Anyway, Thanks a lot for you information. Hope get more help from you.

Chris

|||

Chris,

There's a small typo in Nan's post. The url is http://servername/HumanResources?wsdl

His post missed the 'L' at the end. Also, I'm assuming you are using the same user to retrieve the WSDL document as the user who created the endpoint. If not, you will need to grant the user permissions. "GRANT CONNECT on ENDPOINT::<name> to [user]".

HTH
Jimmy

|||

Sorry for the typo. The wsdl url should be http://servername/HumanResources?wsdl. If you can't get the wsdl xml document, please post the error code, 4XX or 5XX.

|||

I'm using IIS 5.1 on WindowsXP SP2. and I'm using the same user account for creation and access the WS.

the error code is HTTP 404 - File not found. Thanks.

Chris

|||

Ah, since you are running on a WinXP SP2 box, please make sure that IIS is not running. SQL 2005's native web serivces uses the OS HTTP kernel mode listener, which is introduced in WinXP SP2 and W2k3, but the IIS 5.1 on WinXP OS does not make use of it. This causes a port contention.

The alternative is to create the endpoint on a different port (ie. port 8080 or 88), such that the request does not end up going to IIS.

Jimmy

|||

I had the exact same problem. I found the answer at the following URL. I have excepted the piece that helped get me on the right path.

http://download.microsoft.com/download/5/0/e/50ec0a69-d69e-4962-b2c9-80bbad125641/ReadmeSQL2005.htm

4.1.18 Remote Connections to a SOAP/HTTP Endpoint May Fail Using Integrated Authentication

Remote connections to a SOAP/HTTP endpoint may fail when the SQL Server service account is a domain account and authentication for the endpoint is set to INTEGRATED, even though local connections succeed. The problem can occur when the Service Principal Name (SPN) is registered to the local system account. You can work around the issue by either registering an SPN for the SQL Server Service account or specifying NTLM authentication for the endpoint. For more information about registering an SPN, see "Registering Kerberos Service Principal Names by Using Http.sys" in SQL Server Books Online.

Then in SQL Books I found the following;

To support mutual authentication under Kerberos, an instance of SQL Server 2005 must associate a Service Principal Name (SPN) with the account it will be running on, such as a local system account or domain user account. The specific details for SPN registration by a specific instance of SQL Server 2005 are determined by the type of service account it has been configured under. If SQL Server 2005 is running under the local system account or network service account, SPNs must be registered under the computer name. If SQL Server 2005 is running under a domain user account, SPNs must be registered under the domain user name.

Using SetSPN.exe

To enable association of an SPN to the account that the instance of SQL Server 2005 is running on, use the Windows SetSPN.exe support tool. The tool adds the SPN for the computer name on which the instance of SQL Server 2005 is running under the Windows domain service user account located in Active Directory. In this scenario, the SetSPN.exe tool can be used to add two SPNs: one for the NetBIOS name and another for the fully qualified DNS name.

For example, if the SetSPN.exe tool is executed from instance of SQL Server 2005 running on MyComputer, the following two SPNs are associated with the account the instance of SQL Server is running under and must be added to directory:

HTTP/MyComputer;

HTTP/MyComputer.fully.qualified.domain.name.com

If an instance of SQL Server is running as a domain user (MyDomain\MySQLAccount) on a computer that is named MySQLHost, the following commands can be used to set the appropriate SPNs:

setspn –A http/MySQLHost MyDomain\MySQLAccount

setspn –A http/MySqlHost.Mydomain.Mycorp.com MyDomain\MySQLAccount

About Web service or HTTP ENDPOINT

Hi All,

I met some problems when I walk through a example for Native Http SOAP in SQL Server 2005 for Developers. after I create the HTTP EndPoint in the Management Studio using following code:

USE AdventureWorks
GO

CREATE PROCEDURE EmployeePhoneList
AS
SELECT C.LastName, C.FirstName, C.Phone
FROM Person.Contact AS C
INNER JOIN HumanResources.Employee AS E
ON C.ContactID = E.ContactID
ORDER BY C.LastName, C.FirstName
GO

DROP ENDPOINT HRService;
GO

CREATE ENDPOINT HRService
STATE = STARTED
AS HTTP
(
PATH='/HumanResources',
AUTHENTICATION=(INTEGRATED),
PORTS=(CLEAR)
)
FOR SOAP
(
WEBMETHOD 'EmployeePhoneList'(name='AdventureWorks.dbo.EmployeePhoneList'),
DATABASE='AdventureWorks',
WSDL=DEFAULT
)

then I can see the web service is indeed created by using following cmnd,

SELECT *
FROM sys.endpoints

SELECT *
FROM sys.soap_endpoints;

SELECT *
FROM sys.endpoint_webmethods;

but when I want to use the web service in the VS2005, I can't find it and add the web reference. Any help will be greatly appreciated. and could you please tell me how to check web services in the local computer? Thanks.

Chris

Can you try http://yourmahine/HumanResources?wsd in your browser address bar and see if you can get the wsdl document in xml?

|||

No, I couldn't get the description for the web services, just a "page not found" like error.

In fact, when I run SQL to create HTTP EndPoint, I have to stop the web server first. after the execution of the SQL, I got an internal error from IIS if I restart the server. even though I restart the computer and web server, I still couldn't get the web services description.

I talked with some programmers, none of them use web services directly from sql server2005. and I notice there is few articles about it in the forum. Maybe microsoft just create this funtion for fun?

Anyway, Thanks a lot for you information. Hope get more help from you.

Chris

|||

Chris,

There's a small typo in Nan's post. The url is http://servername/HumanResources?wsdl

His post missed the 'L' at the end. Also, I'm assuming you are using the same user to retrieve the WSDL document as the user who created the endpoint. If not, you will need to grant the user permissions. "GRANT CONNECT on ENDPOINT::<name> to [user]".

HTH
Jimmy

|||

Sorry for the typo. The wsdl url should be http://servername/HumanResources?wsdl. If you can't get the wsdl xml document, please post the error code, 4XX or 5XX.

|||

I'm using IIS 5.1 on WindowsXP SP2. and I'm using the same user account for creation and access the WS.

the error code is HTTP 404 - File not found. Thanks.

Chris

|||

Ah, since you are running on a WinXP SP2 box, please make sure that IIS is not running. SQL 2005's native web serivces uses the OS HTTP kernel mode listener, which is introduced in WinXP SP2 and W2k3, but the IIS 5.1 on WinXP OS does not make use of it. This causes a port contention.

The alternative is to create the endpoint on a different port (ie. port 8080 or 88), such that the request does not end up going to IIS.

Jimmy

|||

I had the exact same problem. I found the answer at the following URL. I have excepted the piece that helped get me on the right path.

http://download.microsoft.com/download/5/0/e/50ec0a69-d69e-4962-b2c9-80bbad125641/ReadmeSQL2005.htm

4.1.18 Remote Connections to a SOAP/HTTP Endpoint May Fail Using Integrated Authentication

Remote connections to a SOAP/HTTP endpoint may fail when the SQL Server service account is a domain account and authentication for the endpoint is set to INTEGRATED, even though local connections succeed. The problem can occur when the Service Principal Name (SPN) is registered to the local system account. You can work around the issue by either registering an SPN for the SQL Server Service account or specifying NTLM authentication for the endpoint. For more information about registering an SPN, see "Registering Kerberos Service Principal Names by Using Http.sys" in SQL Server Books Online.

Then in SQL Books I found the following;

To support mutual authentication under Kerberos, an instance of SQL Server 2005 must associate a Service Principal Name (SPN) with the account it will be running on, such as a local system account or domain user account. The specific details for SPN registration by a specific instance of SQL Server 2005 are determined by the type of service account it has been configured under. If SQL Server 2005 is running under the local system account or network service account, SPNs must be registered under the computer name. If SQL Server 2005 is running under a domain user account, SPNs must be registered under the domain user name.

Using SetSPN.exe

To enable association of an SPN to the account that the instance of SQL Server 2005 is running on, use the Windows SetSPN.exe support tool. The tool adds the SPN for the computer name on which the instance of SQL Server 2005 is running under the Windows domain service user account located in Active Directory. In this scenario, the SetSPN.exe tool can be used to add two SPNs: one for the NetBIOS name and another for the fully qualified DNS name.

For example, if the SetSPN.exe tool is executed from instance of SQL Server 2005 running on MyComputer, the following two SPNs are associated with the account the instance of SQL Server is running under and must be added to directory:

HTTP/MyComputer;

HTTP/MyComputer.fully.qualified.domain.name.com

If an instance of SQL Server is running as a domain user (MyDomain\MySQLAccount) on a computer that is named MySQLHost, the following commands can be used to set the appropriate SPNs:

setspn –A http/MySQLHost MyDomain\MySQLAccount

setspn –A http/MySqlHost.Mydomain.Mycorp.com MyDomain\MySQLAccount

About VIEWS

Im new in crystal report. Is there a good tutorial on creating VIews.. how to create or modify.. thanksIm new in crystal report. Is there a good tutorial on creating VIews.. how to create or modify.. thanks

to get the details of view you should search database related topics.

About TRAN, CURSOR and @@ERROR

Please take a look at my code below:
CREATE PROCEDURE ...
...
@.userId int,
...
AS
BEGIN TRAN
DECLARE @.confKey varchar(20), @.confValue varchar(20)
DECLARE curs_conf CURSOR LOCAL FAST_FORWARD FOR SELECT [key], value FROM ...
OPEN curs_conf
FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
WHILE @.@.FETCH_STATUS = 0 BEGIN
DELETE FROM tab_user_conf WHERE [user_id] = @.userId AND conf_key = @.confKey
IF @.@.ERROR <> 0 BEGIN
ROLLBACK
CLOSE curs_conf
DEALLOCATE curs_conf
RETURN
END
IF @.confValue <> '' BEGIN
INSERT INTO tab_user_conf ([user_id], conf_key, conf_value) VALUES
(@.userId, @.confKey, @.confValue)
IF @.@.ERROR <> 0 BEGIN
ROLLBACK
CLOSE curs_conf
DEALLOCATE curs_conf
RETURN
END
END
FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
END
CLOSE curs_conf
DEALLOCATE curs_conf
COMMIT
This code works but is the way it catches errors correct?
Do I have to test @.@.ERROR elsewhere?
Is it necessary to close and deallocate the cursor before returning when an
error occurs?
Is it better to rollback before or after closing and deallocating the
cursor?
Thanks for answering my questions.
HenriWhy use a cursor?
Just use one statement for the insert and one for the delete.
"Henri" wrote:
> Please take a look at my code below:
> CREATE PROCEDURE ...
> ....
> @.userId int,
> ....
> AS
> BEGIN TRAN
> DECLARE @.confKey varchar(20), @.confValue varchar(20)
> DECLARE curs_conf CURSOR LOCAL FAST_FORWARD FOR SELECT [key], value FROM ...
> OPEN curs_conf
> FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
> WHILE @.@.FETCH_STATUS = 0 BEGIN
> DELETE FROM tab_user_conf WHERE [user_id] = @.userId AND conf_key = @.confKey
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK
> CLOSE curs_conf
> DEALLOCATE curs_conf
> RETURN
> END
> IF @.confValue <> '' BEGIN
> INSERT INTO tab_user_conf ([user_id], conf_key, conf_value) VALUES
> (@.userId, @.confKey, @.confValue)
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK
> CLOSE curs_conf
> DEALLOCATE curs_conf
> RETURN
> END
> END
> FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
> END
> CLOSE curs_conf
> DEALLOCATE curs_conf
> COMMIT
> This code works but is the way it catches errors correct?
> Do I have to test @.@.ERROR elsewhere?
> Is it necessary to close and deallocate the cursor before returning when an
> error occurs?
> Is it better to rollback before or after closing and deallocating the
> cursor?
> Thanks for answering my questions.
> Henri
>
>|||In a transaction you will usually want to test for @.@.ERROR after every
statement that manipulates data. As Nigel has said however, there is no
obvious reason to use a cursor here at all. I'm not clear just what this
code is supposed to do so if you need more help we'll need some more
information. Refer to: http://www.aspfaq.com/etiquette.asp?id=5006
--
David Portas
SQL Server MVP
--|||Sorry I had removed the part after FROM.
It's
DECLARE curs_conf CURSOR LOCAL FAST_FORWARD FOR SELECT [key], value FROM
myDB.dbo.getKeyValueTable(@.confText)
I'm using a table variable and it can be long to build it.
I'm using a cursor because if I don't I would have to build the same table
variable twice (once to delete the records, once to insert them again if
needed)
As for SQL SERVER documentation, it's not possible to assign a table (SET
@.myTable = funcFillTable(...)) so I don't know any better way than using a
cursor...
Henri
"Nigel Rivett" <sqlnr@.hotmail.com> a écrit dans le message de
news:F1E11350-C8FE-4572-96CC-7467CB3ADBDB@.microsoft.com...
> Why use a cursor?
> Just use one statement for the insert and one for the delete.
> "Henri" wrote:
> > Please take a look at my code below:
> >
> > CREATE PROCEDURE ...
> > ....
> > @.userId int,
> > ....
> >
> > AS
> >
> > BEGIN TRAN
> >
> > DECLARE @.confKey varchar(20), @.confValue varchar(20)
> > DECLARE curs_conf CURSOR LOCAL FAST_FORWARD FOR SELECT [key], value FROM
...
> > OPEN curs_conf
> > FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
> >
> > WHILE @.@.FETCH_STATUS = 0 BEGIN
> >
> > DELETE FROM tab_user_conf WHERE [user_id] = @.userId AND conf_key =@.confKey
> > IF @.@.ERROR <> 0 BEGIN
> > ROLLBACK
> > CLOSE curs_conf
> > DEALLOCATE curs_conf
> > RETURN
> > END
> >
> > IF @.confValue <> '' BEGIN
> > INSERT INTO tab_user_conf ([user_id], conf_key, conf_value) VALUES
> > (@.userId, @.confKey, @.confValue)
> > IF @.@.ERROR <> 0 BEGIN
> > ROLLBACK
> > CLOSE curs_conf
> > DEALLOCATE curs_conf
> > RETURN
> > END
> > END
> >
> > FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
> > END
> >
> > CLOSE curs_conf
> > DEALLOCATE curs_conf
> >
> > COMMIT
> >
> > This code works but is the way it catches errors correct?
> > Do I have to test @.@.ERROR elsewhere?
> > Is it necessary to close and deallocate the cursor before returning when
an
> > error occurs?
> > Is it better to rollback before or after closing and deallocating the
> > cursor?
> >
> > Thanks for answering my questions.
> >
> > Henri
> >
> >
> >
> >
>|||My procedure is getting a key value string like KEY:VALUE;KEY:VALUE;etc.
from client
and a function converts it in a table variable of key value records, that my
cursor uses.
The aim of this procedure is to update an "user configuration" table,
removing, updating and inserting configuration keys and values as specified
by the client.
I thought it would be clever to delete the keys and insert them again when
needed with the updated values. That prevents from testing for each key, if
it is already present in the base, and if it has to be inserted or updated.
As I said to nigel, the cursor is used so that I don't have to build the
key-value table variable twice.
Am I missing something?
Henri
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a écrit dans le
message de news:za2dncKkk8fDBC7cRVn-qA@.giganews.com...
> In a transaction you will usually want to test for @.@.ERROR after every
> statement that manipulates data. As Nigel has said however, there is no
> obvious reason to use a cursor here at all. I'm not clear just what this
> code is supposed to do so if you need more help we'll need some more
> information. Refer to: http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>
>|||This is an example of why careful separation of database and application
layers is important. You shouldn't need to pass delimited lists into the
database. You've introduced a non-relational structure (the delimited list),
created some procedural code around it (the function) which has then forced
you to implement another compromise (the cursor) to work around the
function's perceived limitations!
It seems that what you actually need is an UPDATE followed by an INSERT,
something like the following. Call this for each of your key-value pairs.
Loops and string parsing are much easier and more efficient in client-code.
CREATE PROCEDURE usp_conf_insert_update
(@.key VARCHAR(10), @.value INTEGER)
AS
UPDATE tab_user_conf
SET conf_value = @.value
WHERE conf_key = @.key
IF @.@.ROWCOUNT=0
INSERT INTO tab_user_conf (conf_key, conf_value)
SELECT @.key, @.value
GO
Of course, tab_user_conf looks suspiciously like an unnormalized list rather
than a table... but that's another discussion :-)
Hope this helps.
--
David Portas
SQL Server MVP
--|||>> I thought it would be clever to delete the keys and insert them again when
needed with the updated values.
That's usually not a good idea. Only update data when necessary or you can
get into problems with RI, triggers and tr logs.
You can use the function to insert into a temp table for the data updates.
Then delete any records that are not needed then insert any new ones.
"Henri" wrote:
> My procedure is getting a key value string like KEY:VALUE;KEY:VALUE;etc.
> from client
> and a function converts it in a table variable of key value records, that my
> cursor uses.
> The aim of this procedure is to update an "user configuration" table,
> removing, updating and inserting configuration keys and values as specified
> by the client.
> I thought it would be clever to delete the keys and insert them again when
> needed with the updated values. That prevents from testing for each key, if
> it is already present in the base, and if it has to be inserted or updated.
> As I said to nigel, the cursor is used so that I don't have to build the
> key-value table variable twice.
> Am I missing something?
> Henri
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a écrit dans le
> message de news:za2dncKkk8fDBC7cRVn-qA@.giganews.com...
> > In a transaction you will usually want to test for @.@.ERROR after every
> > statement that manipulates data. As Nigel has said however, there is no
> > obvious reason to use a cursor here at all. I'm not clear just what this
> > code is supposed to do so if you need more help we'll need some more
> > information. Refer to: http://www.aspfaq.com/etiquette.asp?id=5006
> >
> > --
> > David Portas
> > SQL Server MVP
> > --
> >
> >
> >
>
>|||Thanks for your help David
I had always thought that multiple client-server access was slower than 1
access with a string parsed on the server
Is string parsing that slow in SQL SERVER?
Thanks again
Henri
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a écrit dans le
message de news:ibCdnWWyc6JvOi7cRVn-qw@.giganews.com...
> This is an example of why careful separation of database and application
> layers is important. You shouldn't need to pass delimited lists into the
> database. You've introduced a non-relational structure (the delimited
list),
> created some procedural code around it (the function) which has then
forced
> you to implement another compromise (the cursor) to work around the
> function's perceived limitations!
> It seems that what you actually need is an UPDATE followed by an INSERT,
> something like the following. Call this for each of your key-value pairs.
> Loops and string parsing are much easier and more efficient in
client-code.
> CREATE PROCEDURE usp_conf_insert_update
> (@.key VARCHAR(10), @.value INTEGER)
> AS
> UPDATE tab_user_conf
> SET conf_value = @.value
> WHERE conf_key = @.key
> IF @.@.ROWCOUNT=0
> INSERT INTO tab_user_conf (conf_key, conf_value)
> SELECT @.key, @.value
>
> GO
> Of course, tab_user_conf looks suspiciously like an unnormalized list
rather
> than a table... but that's another discussion :-)
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>
>|||So creating a temp table is better that using a table variable or a cursor?
It's sometime difficult to know what is faster and what is not.
Lucky you were here, thanks again :-)
Henri
"Nigel Rivett" <sqlnr@.hotmail.com> a écrit dans le message de
news:B34C01FB-45DE-4A64-9FA1-DE66A293008A@.microsoft.com...
> >> I thought it would be clever to delete the keys and insert them again
when
> needed with the updated values.
> That's usually not a good idea. Only update data when necessary or you can
> get into problems with RI, triggers and tr logs.
> You can use the function to insert into a temp table for the data updates.
> Then delete any records that are not needed then insert any new ones.
> "Henri" wrote:
> > My procedure is getting a key value string like KEY:VALUE;KEY:VALUE;etc.
> > from client
> > and a function converts it in a table variable of key value records,
that my
> > cursor uses.
> >
> > The aim of this procedure is to update an "user configuration" table,
> > removing, updating and inserting configuration keys and values as
specified
> > by the client.
> > I thought it would be clever to delete the keys and insert them again
when
> > needed with the updated values. That prevents from testing for each key,
if
> > it is already present in the base, and if it has to be inserted or
updated.
> >
> > As I said to nigel, the cursor is used so that I don't have to build the
> > key-value table variable twice.
> > Am I missing something?
> >
> > Henri
> >
> >
> > "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a écrit dans le
> > message de news:za2dncKkk8fDBC7cRVn-qA@.giganews.com...
> > > In a transaction you will usually want to test for @.@.ERROR after every
> > > statement that manipulates data. As Nigel has said however, there is
no
> > > obvious reason to use a cursor here at all. I'm not clear just what
this
> > > code is supposed to do so if you need more help we'll need some more
> > > information. Refer to: http://www.aspfaq.com/etiquette.asp?id=5006
> > >
> > > --
> > > David Portas
> > > SQL Server MVP
> > > --
> > >
> > >
> > >
> >
> >
> >
> >
>|||It is certainly better than using a cursor.
"Henri" wrote:
> So creating a temp table is better that using a table variable or a cursor?
> It's sometime difficult to know what is faster and what is not.
> Lucky you were here, thanks again :-)
> Henri
> "Nigel Rivett" <sqlnr@.hotmail.com> a écrit dans le message de
> news:B34C01FB-45DE-4A64-9FA1-DE66A293008A@.microsoft.com...
> > >> I thought it would be clever to delete the keys and insert them again
> when
> > needed with the updated values.
> >
> > That's usually not a good idea. Only update data when necessary or you can
> > get into problems with RI, triggers and tr logs.
> >
> > You can use the function to insert into a temp table for the data updates.
> > Then delete any records that are not needed then insert any new ones.
> >
> > "Henri" wrote:
> >
> > > My procedure is getting a key value string like KEY:VALUE;KEY:VALUE;etc.
> > > from client
> > > and a function converts it in a table variable of key value records,
> that my
> > > cursor uses.
> > >
> > > The aim of this procedure is to update an "user configuration" table,
> > > removing, updating and inserting configuration keys and values as
> specified
> > > by the client.
> > > I thought it would be clever to delete the keys and insert them again
> when
> > > needed with the updated values. That prevents from testing for each key,
> if
> > > it is already present in the base, and if it has to be inserted or
> updated.
> > >
> > > As I said to nigel, the cursor is used so that I don't have to build the
> > > key-value table variable twice.
> > > Am I missing something?
> > >
> > > Henri
> > >
> > >
> > > "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a écrit dans le
> > > message de news:za2dncKkk8fDBC7cRVn-qA@.giganews.com...
> > > > In a transaction you will usually want to test for @.@.ERROR after every
> > > > statement that manipulates data. As Nigel has said however, there is
> no
> > > > obvious reason to use a cursor here at all. I'm not clear just what
> this
> > > > code is supposed to do so if you need more help we'll need some more
> > > > information. Refer to: http://www.aspfaq.com/etiquette.asp?id=5006
> > > >
> > > > --
> > > > David Portas
> > > > SQL Server MVP
> > > > --
> > > >
> > > >
> > > >
> > >
> > >
> > >
> > >
> >
>
>|||Performance is always something you should test out for yourself with your
data - it's rarely possible to generalize. For strings of trivial size the
efficiency of client code vs TSQL is probably negligible but your comments
on the performance of your function suggested this wasn't a negligible
problem. My point was that you should start from the assumptions of correct
design - performance optimization comes later.
--
David Portas
SQL Server MVP
--

about SQL Server Express

i am current developing a project. in visual studio i can create a database file. if i use the database file in my app. does it require SQL Server Express install on a user pc in order to run my app?

thanks

If you can create a DB within Visual Studio, then you already have the SQL Express installed on your PC.|||

i meant when people using my app, will they have to install SQL Server Express?

thanks

|||If this is a web application, then only the web server requires SQL Express. Clients only require the browser. On the other hand, if you're building a Windows application that run locally on each PC, then yes, you do need local installation of SQL Express.

About SQL SERVER 2005(Stored Procedure) & Dot Net 1.1

I create a new stored procedure in SQL SERVER 2005 .It it is stored in " C:\Documents and Settings\mmm\My Documents\SQL Server Management Studio Express\Projects" as (some_name).sql .it takes this links by default . Then I exeecute it with this command

"exec InsertValueIntoTable ;" BUT when i want to execute with clicking the execute button in the then gives an error "Could not find stored procedure 'InsertValueIntoTable'."

Malay Roy wrote:

I create a new stored procedure in SQL SERVER 2005 .It it is stored in " C:\Documents and Settings\mmm\My Documents\SQL Server Management Studio Express\Projects" as (some_name).sql .it takes this links by default . Then I exeecute it with this command

What do you mean by this? The *.sql file is just the script, you must put all the contents in the sql file to SQL_SERVE before you can execute the SP.

|||

Hi,

Please post your question in the following forum.
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=87&SiteID=1

Thank you,
Bhanu.

|||thread moved to the appropriate forum.