Clueless client who wants to create a table using reserved words

Hello all,
I have a database that supports two different applications.
For arguments sake I'll call them "intelligent app" and "clueless app"

The client that provided me the schema for the clueless app used reserved words as field names.
The words used are:
value, state, and time

I explained to the client that modifying them could prevent current/future issues.

The client is an "oracle" db (and in my mind should know better but ....) and responded back to me with the following:

BEGIN QUOTE
They are not currently reserved and there is no guarantee they will ever be reserved. In fact, the SQL server line eliminates as many words from the reserved list as they add with new releases. There are other words on the
possible future reserved keyword list that I do not wish to avoid either unless forced to some day, like depth, size, class, zone, level and others.
END QUOTE

Now for the assistance request, where can I find the documented ramifications of using reserved words?
I want to have my documentation (and my ducks lined up) when this clients portion of the app fails.

Any and all assistance is greatly appreciated.

T. Mullins'splain dat one again... Whyfor you have to be prepared when their Data Modeler's choices hit the fan?

SQL Server will gleefully tolerate them, as long as they are properly quoted using [], so what the heck do you care? The developers may revolt when their tools act up, but those column names aren't your choices, so they aren't your problems as far as I can see.

-PatP|||Do a search for "Reserved Keywords" in Books OnLine, and you should be presented with the current list of reserved words. One caution, I did come up with tw lists, one for C++ and one for T-SQL.

A quote from this article reads:
Although it is syntactically possible to use SQL Server reserved keywords as identifiers and object names in Transact-SQL scripts, this can be done only using delimited identifiers.

This is pretty much what Pat has said in his post, though.|||Gracias guys !
I knew of the BOL & MSDN info. I was hoping to have examples of apps that have crashed and burned due to poor design. :)
I think I'll wait in the tall grass ......
Regards to all.
tam

Originally posted by MCrowley
Do a search for "Reserved Keywords" in Books OnLine, and you should be presented with the current list of reserved words. One caution, I did come up with tw lists, one for C++ and one for T-SQL.

A quote from this article reads:
Although it is syntactically possible to use SQL Server reserved keywords as identifiers and object names in Transact-SQL scripts, this can be done only using delimited identifiers.

This is pretty much what Pat has said in his post, though.|||Oh, there are fabulous stories that go from the 1970s into the 1990s, but they peter out pretty quickly there. By that point in time, nearly everybody in the compiler/database/toolset community had abandoned the L-R grammer parsers for yacc or its equivalent, which allowed them to quickly and easily make it possible to make a product's entire grammer "quote sensistive". That change allowed the product vendors to make it possible for the user to "un-reserve" words that were otherwise reserved by quoting them somehow.

That change, combined with wider use of code-generators (sometimes by different names, but code generators nonetheless) made it relatively easy to "dodge the bullet" of reserved words.

It can still be a pain in the posterior, but it is no longer a case of Mohammed and the Mountian. Now the developer can work around the reserved-word problem if they choose to do so.

-PatP

Closing Tags

Is there a way to tell SQL Server not to contract closing tags for empty elements? I'm getting a conflict in an app in trying to determine changed data. I create an XML DOM document and write the xml to an xml column in the database.

If I write "<SomeOuterElement><SomeInnerElement></SomeInnerElement></SomeOuterElement>"

the column actually stores: "<SomeOuterElement><SomeInnerElement/></SomeOuterElement>"

Which is great for optimization, but the dom produces empty elements with separate closing tags. So when compared as strings, they appear to be different. (I don't have access to the code that's doing the actual comparison. It's a bit of a black box situation.)

So I need SQL Server to leave the XML alone, or I need a way for the DOM to produce the optimized XML. (This is VB. NET)

Any ideas? Thanks.

J

Whether an empty element is serialized as or should not matter as both ways are semantically equivalent, the element is empty both ways.

If you are using the .NET DOM implementation and want to serialize an empty element as then use the property IsEmpty as in this snippet:

Code Snippet

Dim XmlDoc As XmlDocument = New XmlDocument()

Dim Element As XmlElement = XmlDoc.CreateElement("root")

Dim Element1 As XmlElement = XmlDoc.CreateElement("element")

Element1.IsEmpty = True

Element.AppendChild(Element1)

XmlDoc.AppendChild(Element)

XmlDoc.Save(Console.Out)

But note that you get <element /> that way and not <element/> so string comparison might fail again. You will have to implement your own XmlWriter if you need more control over the serialization details. To implement your own XmlWriter you often subclass XmlTextWriter. However such attempts to make applications happy that don't understand XML fully is in my view an attempt to fix things on the wrong end, it should not matter to an application whether an empty element is marked up as <element/> or <element /> or <element></element>.

Closed transaction keeps running...awaiting command

I am debugging an app which blocks many processes in a SQL7 server DB.
The app log writes every transaction "open" and "close".
The weird thing is : when the app logfile says the transaction is
dropped (object closed) the db keeps showing the process "running", in
a sleeping mode, with open_tran in 2 or even 3 and in an awaiting
command status.

We are working on NT4.0, SP6a (all fixes up to date, MDAC 2.7SP1) on
IIS side, an equal box for MTS (same patches and updates) and a SQL
Server 7 (on NT4, same fixes ans SPs) database on another box.

Is this normal? Those sleeping processes are blocking other apps and
everything gets slow and messy...the only solution is to kill those
blocking processes.

Thanks![posted and mailed, please reply in news]

Rittercorp (ritter_cl@.yahoo.com) writes:
> I am debugging an app which blocks many processes in a SQL7 server DB.
> The app log writes every transaction "open" and "close".
> The weird thing is : when the app logfile says the transaction is
> dropped (object closed) the db keeps showing the process "running", in
> a sleeping mode, with open_tran in 2 or even 3 and in an awaiting
> command status.
> We are working on NT4.0, SP6a (all fixes up to date, MDAC 2.7SP1) on
> IIS side, an equal box for MTS (same patches and updates) and a SQL
> Server 7 (on NT4, same fixes ans SPs) database on another box.
> Is this normal? Those sleeping processes are blocking other apps and
> everything gets slow and messy...the only solution is to kill those
> blocking processes.

No, this is not normal. Apparently your application has a problem with
transaction scope. The fact that your application log looks good proves
little. The application log may know about transaction it starts, but
what about transactions that starts in SQL code, for instance in
stored procedures?

There are a couple of gotchas. For instance, if you call a stored procedure
and that stored procedure starts a transaction, but the application
cancels the procedure before the execution completed, for instance
because of the dreaded "Timeout expired", the transaction started by
the SP is *not* rolled back. The same is true, if the procedure
starts a transaction, and then is aborted because of a reference to
a non-existing table.

On http://www.sommarskog.se/sqlutil/aba_lockinfo.html I have a
utility that gives yuu a lot of information about locks in the server,
including a list of which are the locked objects. This might be helpful
for you to understand where you are leaking transactions. (Although it
might be messy to find out. Because once a process has missed a
COMMIT or ROLLBACK, it will accumulate locks from all over the place.)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Client-Server App vs MS Access Data Project

Hello all,

I am looking at building a front end for a SQL Server 2005 database and am looking for an opinion on which way to proceed. My goals are high performance and security. Would it be better to build a desktop client-server application or create a MS Access Data Project to be used as a front end for my database?

ThanksYes, high performance and security comes with SQL Server, not MSAccess.
But client-server app is required when need 2 processes to be running each on a separate machine. Even the database is on a separate machine this doesn't mean a client-server app.
So you need just a desktop application (.Net, VB, Java) with an SQL Server, if it must not be Web accessible.

Client Tools for Backup

Hi all,
I hope this hasn't been answered already...
I have developed a .NET WinForms app which runs on client PCs which talk to
SQL Server2000 on a server. I have coded a backup screen which uses
SQLDMO. This runs fine when I run the app on the server, but it crashes
when I try it on the client PCs. Looks like something to do with an
unregistered
SQLDMO dll.
There's only one client PC which actually requires this functionality. I'm
thinking that installing Client Tools on this PC should take care of things.
I'm wondering if there are any licensing issues.
The company in question is running Small Business Server 2000 and has
the relevant licenses for SQL Server 2000.
Thanks!
John
See if this helps:
http://support.microsoft.com/default...b;EN-US;248241
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"jonsie" <jonsie@.discussions.microsoft.com> wrote in message
news:0F7A3DB9-C07E-4595-9D1A-B19300024332@.microsoft.com...
Hi all,
I hope this hasn't been answered already...
I have developed a .NET WinForms app which runs on client PCs which talk to
SQL Server2000 on a server. I have coded a backup screen which uses
SQLDMO. This runs fine when I run the app on the server, but it crashes
when I try it on the client PCs. Looks like something to do with an
unregistered
SQLDMO dll.
There's only one client PC which actually requires this functionality. I'm
thinking that installing Client Tools on this PC should take care of things.
I'm wondering if there are any licensing issues.
The company in question is running Small Business Server 2000 and has
the relevant licenses for SQL Server 2000.
Thanks!
John
|||hi John,
"jonsie" <jonsie@.discussions.microsoft.com> ha scritto nel messaggio
news:0F7A3DB9-C07E-4595-9D1A-B19300024332@.microsoft.com...
> Hi all,
> I hope this hasn't been answered already...
> I have developed a .NET WinForms app which runs on client PCs which talk
to
> SQL Server2000 on a server. I have coded a backup screen which uses
> SQLDMO. This runs fine when I run the app on the server, but it crashes
> when I try it on the client PCs. Looks like something to do with an
> unregistered
> SQLDMO dll.
> There's only one client PC which actually requires this functionality. I'm
> thinking that installing Client Tools on this PC should take care of
things.
> I'm wondering if there are any licensing issues.
> The company in question is running Small Business Server 2000 and has
> the relevant licenses for SQL Server 2000.
the SQL Server Client Tools are limited and related to SQL Server license...
regarding SQL-DMO component, you can distribute it as it is freely
redistributable..
the related dependencies are:
; not licensed by redist.txt but available after installation of MDAC2.6
...\WINDOWS\SYSTEM\odbcbcp.dll; DestDir: WinSys ; sharedfile
; not licensed by redist.txt but available after installation of MDAC2.6
...\WINDOWS\SYSTEM\sqlwoa.dll ; DestDir: WinSys
; not licensed by redist.txt but available after installation of MDAC2.6
...\WINDOWS\SYSTEM\sqlwid.dll ; DestDir: WinSys
...\Programmi\Microsoft SQL Server\80\Tools\Binn\w95scm.dll; DestDir:
DestinationFolder\Binn
...\WINDOWS\SYSTEM\sqlunirl.dll ; DestDir: WinSys
...\Programmi\Microsoft SQL Server\80\Tools\Binn\sqlresld.dll; DestDir:
DestinationFolder\Binn
...\Programmi\Microsoft SQL Server\80\Tools\Binn\sqlsvc.dll; DestDir:
DestinationFolder\Binn
; not licensed by redist.txt but available after installation of MDAC2.6
...\Programmi\Microsoft SQL Server\80\Tools\Binn\Resources\1033\sqlsvc.RLL;
DestDir: DestinationFolder\Binn\Resources\1033
; not licensed by redist.txt but available after installation of MDAC2.6
...\Programmi\Microsoft SQL Server\80\Tools\Binn\Resources\1033\Sqldmo.rll;
DestDir: DestinationFolder\Binn\Resources\1033
...\Programmi\Microsoft SQL Server\80\Tools\Binn\sqldmo.dll; DestDir:
DestinationFolder\Binn ; file to be registered via regserver
DestinationFolder can either be the installation directory of one instance
of Microsoft SqlServer 2000, like ..\Program Files\Microsoft SQL
Server\80\Tools, even if no istance of SQL Server has been installed, or the
installation directory of your application, but the first one is preferred.
Please do respect the hierarchy \Binn\Resources\1033 (where 1033 specifies
the language), where needed, in order to grant correct functionality of
Ole-Automation objects.
In order to install SQL-DMO components for MSDE 2000, Microsoft Internet
Explorer 5.5 or higher is required.
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Client cannot access SQL 2005 Express Server

I've installed SQL 2005 Express, enabled local and Remote connections but my client app cannot connect to the SQL server. I'm getting access denied or SQL does not exists. Any idea's on how to resolve this issue?

Perhaps these articles will help:

Configuration -Configure SQL Server 2005 to allow remote connections
http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277
http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

Configuration -Connect to SQL Express from "downlevel clients"
http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx

Configuration -Connect to SQL Express and ‘Stay Connected’
http://betav.com/blog/billva/2006/06/getting_and_staying_connected.html

client application bug?

Hello everyone,
I have a client application (Windows app) that logs into a database
via an SQL account. As part of the nature of the application, the
initial login screen of the application has the following fields:
database name: (text field)
sql username: (text field)
sql password: (text field)
database: (drop down)
Filling up the information required on the first 3 fields is
straightforward, but when the drop down is selected to supposedly
choose which database to work on, ocassionally an error comes up
saying that the SQL username does not have access to a particular
database, say DATA1. This happens about 3 in 10 "dropdown selection"
attempts, and will just fix itself after a while or after several
attempts.
The particular SQL login indeed does not have any access to DATA1 (our
example) but based on the data captured in the Profiler, the Windows
application should not have "seen" the DATA1 database. This was the
query as captured:
select db_name()
select name from master..sysdatabases where has_dbaccess(name)=1
Running the above in QA will only list the databases that the
particular SQL account has access to (and the current database), and
does not include DATA1. The above query is executed whenever the drop
down is selected and there are no other queries being made.
Any ideas on this one? I acknowledge that there could be a bug on the
application where it is trying to access the other databases that it
does not have access to...but my question is, how was the application
able to know or enumerate the other databases that the account does
not have access to based on the queries above? (The account can still
do a query to master.dbo.sysdatabases, but such a query has never been
captured.)
Thanks.
AramidCan you verify using Prilfer events like (Audit Login, Audit Logout and
ExistingConnection) to make sure the application is indeed making the
connections as you would expect?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"aramid" <aramid@.hotmail.com> wrote in message
news:2bt371hd68ut7iis3ltm3u2cjbvokcqb3i@.
4ax.com...
Hello everyone,
I have a client application (Windows app) that logs into a database
via an SQL account. As part of the nature of the application, the
initial login screen of the application has the following fields:
database name: (text field)
sql username: (text field)
sql password: (text field)
database: (drop down)
Filling up the information required on the first 3 fields is
straightforward, but when the drop down is selected to supposedly
choose which database to work on, ocassionally an error comes up
saying that the SQL username does not have access to a particular
database, say DATA1. This happens about 3 in 10 "dropdown selection"
attempts, and will just fix itself after a while or after several
attempts.
The particular SQL login indeed does not have any access to DATA1 (our
example) but based on the data captured in the Profiler, the Windows
application should not have "seen" the DATA1 database. This was the
query as captured:
select db_name()
select name from master..sysdatabases where has_dbaccess(name)=1
Running the above in QA will only list the databases that the
particular SQL account has access to (and the current database), and
does not include DATA1. The above query is executed whenever the drop
down is selected and there are no other queries being made.
Any ideas on this one? I acknowledge that there could be a bug on the
application where it is trying to access the other databases that it
does not have access to...but my question is, how was the application
able to know or enumerate the other databases that the account does
not have access to based on the queries above? (The account can still
do a query to master.dbo.sysdatabases, but such a query has never been
captured.)
Thanks.
Aramid

Client app needs to know when data has changed

Hi all,
I am developing a multi user application using Delphi. Currently I am using
a database engine that is terrible. I am considering switching over to SQL
Server. One of the features that I could really use is for the client
applications to be notified when data changed. That is, if one client adds,
deletes or modifies a record, I need the other clients to be notified of
this. Currently, when a client makes a change, I alter a record in a
"changes" table. The other clients poll this table every 10 seconds to
determine if a change has been made. I want to get away from this
poling...
Can SQL Server accomplish this? If yes, which edition?
Thank you
--
Joseph I. Ceasar
CLS Computer SolutionsAn aspect of the new ADO.NET v2.0 is "Query Notifications" and this is fully
implemented by SQLServer-2005 (due to ship w/b 7 Nov 2005). More details on
msdn.microsoft.com/SQL/2005/dataaccess/default.aspx?pull=/library/en-us/dnvs
05/html/querynotification.asp
There is a good CTP (effectively Beta-4) to get early experience on at MS
site msdn.microsoft.com/SQL/2005/default.aspx
HTH
Dick
"Joseph I. Ceasar" wrote:

> Hi all,
> I am developing a multi user application using Delphi. Currently I am usi
ng
> a database engine that is terrible. I am considering switching over to SQ
L
> Server. One of the features that I could really use is for the client
> applications to be notified when data changed. That is, if one client add
s,
> deletes or modifies a record, I need the other clients to be notified of
> this. Currently, when a client makes a change, I alter a record in a
> "changes" table. The other clients poll this table every 10 seconds to
> determine if a change has been made. I want to get away from this
> poling...
> Can SQL Server accomplish this? If yes, which edition?
> Thank you
>
> --
> --
> Joseph I. Ceasar
> CLS Computer Solutions
>
>|||Very interesting.....
I check out that document. It does say that this technique should not be
used for data that changes very frequently. Does anyone have any idea of
what technique to use for data that does change frequently?
"Dick in UK" <Dick in UK@.discussions.microsoft.com> wrote in message
news:F9170DEA-6FC4-4CFB-82FA-7180106BD34D@.microsoft.com...
> An aspect of the new ADO.NET v2.0 is "Query Notifications" and this is
> fully
> implemented by SQLServer-2005 (due to ship w/b 7 Nov 2005). More details
> on
> msdn.microsoft.com/SQL/2005/dataaccess/default.aspx?pull=/library/en-us/dn
vs05/html/querynotification.asp
> There is a good CTP (effectively Beta-4) to get early experience on at MS
> site msdn.microsoft.com/SQL/2005/default.aspx
> HTH
> Dick
> "Joseph I. Ceasar" wrote:
>|||You can't eat the cake and have it. If you want to be notified, it would be
hard to write your own
with lower resource consumption than Query Notifications (based on the smart
technique that QN
uses). If that is to steep for you (too frequent data changes, use a polling
technique. A polling
technique can show stale data, which is why it *can* be less resource intens
ive in a highly changing
environment.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joseph I. Ceasar" <jic@.pipeline.com> wrote in message
news:O%23c7aoDxFHA.2312@.TK2MSFTNGP14.phx.gbl...
> Very interesting.....
> I check out that document. It does say that this technique should not be
used for data that
> changes very frequently. Does anyone have any idea of what technique to u
se for data that does
> change frequently?
> "Dick in UK" <Dick in UK@.discussions.microsoft.com> wrote in message
> news:F9170DEA-6FC4-4CFB-82FA-7180106BD34D@.microsoft.com...
>

Client app needs to know when data has changed

Hi all,
I am developing a multi user application using Delphi. Currently I am using
a database engine that is terrible. I am considering switching over to SQL
Server. One of the features that I could really use is for the client
applications to be notified when data changed. That is, if one client adds,
deletes or modifies a record, I need the other clients to be notified of
this. Currently, when a client makes a change, I alter a record in a
"changes" table. The other clients poll this table every 10 seconds to
determine if a change has been made. I want to get away from this
poling...
--
Joseph I. Ceasar
CLS Computer SolutionsJoseph,
Consider using a trigger?
HTH
Jerry
"Joseph I. Ceasar" <jic@.pipeline.com> wrote in message
news:eKMgHa6wFHA.3644@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I am developing a multi user application using Delphi. Currently I am
> using
> a database engine that is terrible. I am considering switching over to
> SQL
> Server. One of the features that I could really use is for the client
> applications to be notified when data changed. That is, if one client
> adds,
> deletes or modifies a record, I need the other clients to be notified of
> this. Currently, when a client makes a change, I alter a record in a
> "changes" table. The other clients poll this table every 10 seconds to
> determine if a change has been made. I want to get away from this
> poling...
>
> --
> --
> Joseph I. Ceasar
> CLS Computer Solutions
>|||Look into SQL Notification Services. Sounds like this is what you need.
ML|||Do you really need that? It's been my experience that detecting changes
made by other users when a modification is submitted is sufficient.
When the user presses the OK button, a stored procedure is called to commit
the change. If the row to be updated has changed, then the stored procedure
indicates in its return value that it cannot complete the update and why,
and returns the current values in output parameters. In this way you can
inform the user what changed, possibly by whom (of course, in order to do
that you need to record who made the last change within each row). You also
have access to the user's changes so no information is lost in the process.
I load a tool tip with the user's changes when they're different from what's
been returned by the stored procedure. That way, instead of simply
discarding the user's changes, they're available to the user so that he can
decide to either discard his changes, or rekey and apply them. In short,
the user performs the conflict resolution task instead of producing some
exception report and fixing the problem later.
Polling for changes every 10 seconds or registering an event handler in some
middle-tier component does not eliminate the possibility that a change could
be made by another user between polls or while an earlier event is being
processed. The exception handling mechanism described above must still
exist. I think that it would be really annoying, if not dangerous, for a
user filling out a form to be interrupted and/or to have his changes
overridden because some yahoo down the hall updated the same row. Waiting
until the form is filled out, and giving the user the ability to resolve any
conflicts that occur just makes more sense to me.
"Joseph I. Ceasar" <jic@.pipeline.com> wrote in message
news:eKMgHa6wFHA.3644@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I am developing a multi user application using Delphi. Currently I am
> using
> a database engine that is terrible. I am considering switching over to
> SQL
> Server. One of the features that I could really use is for the client
> applications to be notified when data changed. That is, if one client
> adds,
> deletes or modifies a record, I need the other clients to be notified of
> this. Currently, when a client makes a change, I alter a record in a
> "changes" table. The other clients poll this table every 10 seconds to
> determine if a change has been made. I want to get away from this
> poling...
>
> --
> --
> Joseph I. Ceasar
> CLS Computer Solutions
>|||a trigger and . . .
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eJ9Ipd6wFHA.2252@.TK2MSFTNGP09.phx.gbl...
> Joseph,
> Consider using a trigger?
> HTH
> Jerry
> "Joseph I. Ceasar" <jic@.pipeline.com> wrote in message
> news:eKMgHa6wFHA.3644@.TK2MSFTNGP11.phx.gbl...
>|||another component(s). :-) Depends on the situation and the requirments.
"JT" <someone@.microsoft.com> wrote in message
news:uvk6b16wFHA.2652@.TK2MSFTNGP14.phx.gbl...
>a trigger and . . .
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:eJ9Ipd6wFHA.2252@.TK2MSFTNGP09.phx.gbl...
>|||When you say that your database engine is terrible, does this mean that you
are wanting to migrate from Oracle or MySQL to SQL Server?
Also, explain a little more about the application and how it would use the
information that data has been changed by another user. If your client is
using connected ADO recordsets, then perhaps a dynamic cursor?
http://msdn.microsoft.com/library/d...perty_oledb.asp
"Joseph I. Ceasar" <jic@.pipeline.com> wrote in message
news:eKMgHa6wFHA.3644@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I am developing a multi user application using Delphi. Currently I am
> using
> a database engine that is terrible. I am considering switching over to
> SQL
> Server. One of the features that I could really use is for the client
> applications to be notified when data changed. That is, if one client
> adds,
> deletes or modifies a record, I need the other clients to be notified of
> this. Currently, when a client makes a change, I alter a record in a
> "changes" table. The other clients poll this table every 10 seconds to
> determine if a change has been made. I want to get away from this
> poling...
>
> --
> --
> Joseph I. Ceasar
> CLS Computer Solutions
>|||Normally I would agree with this procedure. The specific case that I am
dealing with needs the info "Pushed" to the client. It's a scheduling
application. If a user creates an appointment, I need all the other users
to see that new appointment. Informing a user that a certain time-block is
taken is of no use, since I allow double booking. There are only 2
solutions here. Poling or being notified that something changed.
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:%23pzmrz6wFHA.2072@.TK2MSFTNGP14.phx.gbl...
> Do you really need that? It's been my experience that detecting changes
> made by other users when a modification is submitted is sufficient.
> When the user presses the OK button, a stored procedure is called to
> commit the change. If the row to be updated has changed, then the stored
> procedure indicates in its return value that it cannot complete the update
> and why, and returns the current values in output parameters. In this way
> you can inform the user what changed, possibly by whom (of course, in
> order to do that you need to record who made the last change within each
> row). You also have access to the user's changes so no information is
> lost in the process. I load a tool tip with the user's changes when
> they're different from what's been returned by the stored procedure. That
> way, instead of simply discarding the user's changes, they're available to
> the user so that he can decide to either discard his changes, or rekey and
> apply them. In short, the user performs the conflict resolution task
> instead of producing some exception report and fixing the problem later.
> Polling for changes every 10 seconds or registering an event handler in
> some middle-tier component does not eliminate the possibility that a
> change could be made by another user between polls or while an earlier
> event is being processed. The exception handling mechanism described
> above must still exist. I think that it would be really annoying, if not
> dangerous, for a user filling out a form to be interrupted and/or to have
> his changes overridden because some yahoo down the hall updated the same
> row. Waiting until the form is filled out, and giving the user the
> ability to resolve any conflicts that occur just makes more sense to me.
> "Joseph I. Ceasar" <jic@.pipeline.com> wrote in message
> news:eKMgHa6wFHA.3644@.TK2MSFTNGP11.phx.gbl...
>|||I've never used Notification Services, but that sounds like a place to
start.
"Joseph I. Ceasar" <jic@.pipeline.com> wrote in message
news:%23KrUB86wFHA.3180@.TK2MSFTNGP14.phx.gbl...
> Normally I would agree with this procedure. The specific case that I am
> dealing with needs the info "Pushed" to the client. It's a scheduling
> application. If a user creates an appointment, I need all the other users
> to see that new appointment. Informing a user that a certain time-block
> is taken is of no use, since I allow double booking. There are only 2
> solutions here. Poling or being notified that something changed.
>
> "Brian Selzer" <brian@.selzer-software.com> wrote in message
> news:%23pzmrz6wFHA.2072@.TK2MSFTNGP14.phx.gbl...
>|||SQL Server 2005 will have a feature called "Query Notification" (not the sam
e as Notification
Services). You can be notified immediately if a change is made that might af
fect the rows that has
been returned by your SELECT statement (including new rows that qualifies).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joseph I. Ceasar" <jic@.pipeline.com> wrote in message news:eKMgHa6wFHA.3644@.TK2MSFTNGP11.p
hx.gbl...
> Hi all,
> I am developing a multi user application using Delphi. Currently I am usi
ng
> a database engine that is terrible. I am considering switching over to SQ
L
> Server. One of the features that I could really use is for the client
> applications to be notified when data changed. That is, if one client add
s,
> deletes or modifies a record, I need the other clients to be notified of
> this. Currently, when a client makes a change, I alter a record in a
> "changes" table. The other clients poll this table every 10 seconds to
> determine if a change has been made. I want to get away from this
> poling...
>
> --
> --
> Joseph I. Ceasar
> CLS Computer Solutions
>

ClickOnce Please Help

When having someone run my click once app that is using SQL CE they are getting this message.

The application requires that assembly System.Data.SqlServerCE Version 9.0.242.0 be installed in the global assembly cache (GAC) first

Can someone please let me know what I need to add to prerequisites?

Update on my issue. I have downloaded the development toolkit for CE and have set CE as a prerequisit. I ran click once and then published the application and downloaded it on a machine that does not have click once. The application installed and started. I went to do something that I know uses CE and I get this message Unable to load DLL sqlceme30.DLL.

|||Maybe you should have a look at this: http://msdn2.microsoft.com/en-us/library/bb219481.aspx

ClickOnce for SQL Express Database?

I'm working on an app in Visual Studio 2005 that needs to have installed locally SQL Express to handle its own data.

This app is in a strong upgrading process. I've been using clickonce to publish the app and let my users upgrade automatically (by the way this works amazing ...).

Yet many times changes to the DB are made. Is there a simple way to upgrade their local SQL Express DB like click once does with the app?

Thanks.

hi,

I'm not sure about click once capability to perform the requested task, but even if possible, are you sure you like to do it? that means you would upgrade users database loosing their data modifications... I do think you've better just "upgrade" the database schema and not replace their database...

if this is the case, you can perhaps have a look at syncornization tools like http://www.red-gate.com/products/SQL_Compare/index.htm..

regards

|||

Thanks, the products looks like a very good solution. I've already downloaded it and I'm testing.

Have you used it yourself? Do you know other products that do the same?

|||

hi,

yes, I do use it myself since versions 2...

I know other similar tools are available out there, like http://www.innovartis.co.uk/database_dbghost_home.aspx, but I've always been satisfied by red-gate tools.. they even provides http://www.red-gate.com/products/SQL_Packager/index.htm, base on SQL Compare tool, to just distribute an exe packaged file that, at run time, will perform the metadata updates for you..

but here we are in advertising mode

BTW, I'm not involved in any way with the reported products/company, I'm just a user as you could be..

for further info, a free comparison tool is available at http://www.absistemi.it/sqlCompare.aspx by another italian SQL Server MVP fellow, but I'm not sure it has been updated for SQL Server 2005...

regards

|||Grazie tante ...|||

prego...

(you're welcome)

Clearer version of my earlier question re sp_grantlogin and sp_grantdbaccess

I have amended this post from the one I posted a few hours ago - to make it
clearer (and friendlier!).
I have developed an app for my customer and it assigns different privilege
levels to users based on which domain network group they belong to.
However the only way I have found to be able to grant them access to the sql
server and
database is to connect with a trusted connection having logged in as Domain
Admin. Is that correct?
(I have to go to another site in order to be allowed to do this which is
somewhat inconvenient).
What I would like to able to do but cannot is log into MSDE as sa and run
the following:
use AppDB
exec sp_grantlogin 'TheirDomain\AppManagers'
exec sp_grantdbaccess 'TheirDomain\AppManagers'
exec sp_grantlogin 'TheirDomain\AppReadWrite'
exec sp_grantdbaccess 'TheirDomain\AppReadWrite'
etc.
Can someone let me know if I could achieve the above with a trusted
connection to MSDE as
something less than Domain Admin? If so what network rights would I need?
Many thanks in advance, for your time and expertise!
Clive Elsworth (London UK)
www.EndorphinSoftware.co.uk
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.631 / Virus Database: 404 - Release Date: 17/03/2004
First question is whether the SQL Server install is a default install or if you can add some login first.
If it is a default install, the only logins which exists are the Windows Administrators group and "sa" (which
requires mixed mode - not recommended). However, in order to install SQL Server, you need to be Administrator
anyhow, so why not just run the script which creates the users after install? Am I missing something?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Clive Elsworth" <clive@.takethisbitout.elsworth.dircon.co.uk> wrote in message
news:OrihgJpEEHA.3864@.TK2MSFTNGP12.phx.gbl...
> I have amended this post from the one I posted a few hours ago - to make it
> clearer (and friendlier!).
> I have developed an app for my customer and it assigns different privilege
> levels to users based on which domain network group they belong to.
> However the only way I have found to be able to grant them access to the sql
> server and
> database is to connect with a trusted connection having logged in as Domain
> Admin. Is that correct?
> (I have to go to another site in order to be allowed to do this which is
> somewhat inconvenient).
> What I would like to able to do but cannot is log into MSDE as sa and run
> the following:
> use AppDB
> exec sp_grantlogin 'TheirDomain\AppManagers'
> exec sp_grantdbaccess 'TheirDomain\AppManagers'
> exec sp_grantlogin 'TheirDomain\AppReadWrite'
> exec sp_grantdbaccess 'TheirDomain\AppReadWrite'
> etc.
> Can someone let me know if I could achieve the above with a trusted
> connection to MSDE as
> something less than Domain Admin? If so what network rights would I need?
> Many thanks in advance, for your time and expertise!
> Clive Elsworth (London UK)
>
> --
> www.EndorphinSoftware.co.uk
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.631 / Virus Database: 404 - Release Date: 17/03/2004
>
|||Tibor
Hello again - you and I were in contact about 4 years ago or so - I don't
think I've been to Sweden since then unfortunately. It may have been my SQL
Beautifier that we were discussing then, which after giving away a fair bit
I have not developed since. In any case you were a great help with a trick
to restart SQL Agent when SQL Server restarts (using sp_procoption) which I
have since given away to countless students in classes I have taught.
Anyhow..
I did the install about a month ago as Domain Admin (default install - mixed
mode - not recommended I know, but I really need it) and ran the
sp_grantlogin and sp_grantdbaccess fine, because I was then at the 'Admin'
site where they let me login as Domain Admin. Since then, they wanted a
minor enhancement which meant altering a number of Tables, SPs and Views.
What I usually like to do in this situation is:
1. - Backup the customer's DB and restore it to my laptop
2. - Transfer the customer's data to the newly enhanced DB on my laptop,
which I have tested with the app back in my office
3. - Transfer and Restore that DB to the customer server PC
4. - Grant rights to network groups to the DB now on the customer server PC
In the end I had to do it the other way which was to:
1. - Backup the customer's DB and restore it to my laptop
2. - Apply all the changes to their DB and hope I hadn't missed anything.
3. - Restore it back to their server - the domain groups still existed as DB
users and so no further action was necessary.
Maybe I'll just have to invest in one of those utilities that tells you all
the differences between two DBs - so I can be sure I never miss anything -
but I'd rather not if possible.
It doesn't seem right that sa - that should have total control over a SQL
Server, doesn't have the right to grant DB access to Domain Groups. What do
you think?
Best Regards
Clive
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:exVYrQpEEHA.1032@.TK2MSFTNGP09.phx.gbl...
> First question is whether the SQL Server install is a default install or
if you can add some login first.
> If it is a default install, the only logins which exists are the Windows
Administrators group and "sa" (which
> requires mixed mode - not recommended). However, in order to install SQL
Server, you need to be Administrator
> anyhow, so why not just run the script which creates the users after
install? Am I missing something?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Clive Elsworth" <clive@.takethisbitout.elsworth.dircon.co.uk> wrote in
message
> news:OrihgJpEEHA.3864@.TK2MSFTNGP12.phx.gbl...
it
privilege
sql
Domain
run
need?
>
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.631 / Virus Database: 404 - Release Date: 18/03/2004
|||> Hello again - you and I were in contact about 4 years ago or so <snip>
Ahh, yes. Thanks for reminding me, Clive. :-)

> Maybe I'll just have to invest in one of those utilities that tells you all
> the differences between two DBs - so I can be sure I never miss anything -
> but I'd rather not if possible.
I take it that you have already determined that it is too labor intensive to continuously add onto a script
file while you do changes, so the script file in the end contains the necessary ALTER TABLE commands etc? This
is perfectly doable, but only you can determine whether you consider this hinders your development too much to
be worth it.

> It doesn't seem right that sa - that should have total control over a SQL
> Server, doesn't have the right to grant DB access to Domain Groups. What do
> you think?
Seems I missed this in the beginning of the thread. I thought that your problem is that you don't are
connected as sysadmin. I realize now that you are connected as sa. I didn't know that an SQL Server login as
sa doesn't give you the ability to add Windows Logins from a domain as database users to a database, and if
you read the documentation for sp_grantdbaccess, it only say that you need to be sysadmin (etc). If this is
what you are seeing, then you might want to post a bug report (or rather open a case which might end up in a
bug report being filed).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Clive Elsworth" <clive@.takethisbitout.elsworth.dircon.co.uk> wrote in message
news:%23Zd5q5vEEHA.1376@.TK2MSFTNGP10.phx.gbl...
> Tibor
> Hello again - you and I were in contact about 4 years ago or so - I don't
> think I've been to Sweden since then unfortunately. It may have been my SQL
> Beautifier that we were discussing then, which after giving away a fair bit
> I have not developed since. In any case you were a great help with a trick
> to restart SQL Agent when SQL Server restarts (using sp_procoption) which I
> have since given away to countless students in classes I have taught.
> Anyhow..
> I did the install about a month ago as Domain Admin (default install - mixed
> mode - not recommended I know, but I really need it) and ran the
> sp_grantlogin and sp_grantdbaccess fine, because I was then at the 'Admin'
> site where they let me login as Domain Admin. Since then, they wanted a
> minor enhancement which meant altering a number of Tables, SPs and Views.
> What I usually like to do in this situation is:
> 1. - Backup the customer's DB and restore it to my laptop
> 2. - Transfer the customer's data to the newly enhanced DB on my laptop,
> which I have tested with the app back in my office
> 3. - Transfer and Restore that DB to the customer server PC
> 4. - Grant rights to network groups to the DB now on the customer server PC
> In the end I had to do it the other way which was to:
> 1. - Backup the customer's DB and restore it to my laptop
> 2. - Apply all the changes to their DB and hope I hadn't missed anything.
> 3. - Restore it back to their server - the domain groups still existed as DB
> users and so no further action was necessary.
> Maybe I'll just have to invest in one of those utilities that tells you all
> the differences between two DBs - so I can be sure I never miss anything -
> but I'd rather not if possible.
> It doesn't seem right that sa - that should have total control over a SQL
> Server, doesn't have the right to grant DB access to Domain Groups. What do
> you think?
> Best Regards
> Clive
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:exVYrQpEEHA.1032@.TK2MSFTNGP09.phx.gbl...
> if you can add some login first.
> Administrators group and "sa" (which
> Server, you need to be Administrator
> install? Am I missing something?
> message
> it
> privilege
> sql
> Domain
> run
> need?
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.631 / Virus Database: 404 - Release Date: 18/03/2004
>
|||Tibor
Please see my reply to Greg Low about the sp_grantlogin problem. It's
basically still unresolved - I have more tests to do.

> I take it that you have already determined that it is too labor intensive
to continuously add onto a script
> file while you do changes, so the script file in the end contains the
necessary ALTER TABLE commands etc? This
> is perfectly doable, but only you can determine whether you consider this
hinders your development too much to
> be worth it.
Up to now I have got away without needing to do that because I've been
transferring the client's data into my own 'upgraded and fully tested' db,
before restoring it to their computers.
I was shocked the other day to learn that that was no longer going to be
possible in all cases, although luckily in that case I had kept a note of
all the changes I had made - and so made them all again manually to their
version of the db (briefly restored onto my laptop), which had security all
set up, and so could be restored back to their network without the need for
any additional sp_grantlogins.
Thanks for your help. Hope business is good for you.
Regards
Clive
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eFRGgzZFEHA.2408@.TK2MSFTNGP10.phx.gbl...
> Ahh, yes. Thanks for reminding me, Clive. :-)
>
all
anything -
> I take it that you have already determined that it is too labor intensive
to continuously add onto a script
> file while you do changes, so the script file in the end contains the
necessary ALTER TABLE commands etc? This
> is perfectly doable, but only you can determine whether you consider this
hinders your development too much to
> be worth it.
>
SQL
What do
> Seems I missed this in the beginning of the thread. I thought that your
problem is that you don't are
> connected as sysadmin. I realize now that you are connected as sa. I
didn't know that an SQL Server login as
> sa doesn't give you the ability to add Windows Logins from a domain as
database users to a database, and if
> you read the documentation for sp_grantdbaccess, it only say that you need
to be sysadmin (etc). If this is
> what you are seeing, then you might want to post a bug report (or rather
open a case which might end up in a
> bug report being filed).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Clive Elsworth" <clive@.takethisbitout.elsworth.dircon.co.uk> wrote in
message
> news:%23Zd5q5vEEHA.1376@.TK2MSFTNGP10.phx.gbl...
don't
SQL
bit
trick
which I
mixed
'Admin'
Views.
PC
anything.
as DB
all
anything -
SQL
What do
in
or
Windows
SQL
make
the
which is
and
>
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.648 / Virus Database: 415 - Release Date: 31/03/2004